morganjk
ADSM.ORG Member
I recently created a select statement that will give me info similar to a "q session". I figured out how to convert the data_sent and data_received columns. What I can't find is how to right align these columns
Here's a sample of the current output:
SESSION # STATE DATA SENT DATA RECVD SESS TYPE
----------- --------- ----------- ----------- ----------
759632 Run 207.4 MB 52.5 KB Admin
761840 Run 15.4 MB 217 B Admin
776005 Run 103.5 KB 346.2 GB Node
786930 IdleW 109.3 KB 8.6 GB Node
795628 IdleW 376.0 MB 24.4 KB Node
797109 Run 2.5 MB 205 B Admin
798535 IdleW 4.6 GB 186.3 KB Node
823243 Run 143 B 231 B Admin
What I would like to see
SESSION # STATE DATA SENT DATA RECVD SESS TYPE
----------- --------- ----------- ----------- ----------
759632 Run 207.4 MB 52.5 KB Admin
761840 Run 15.4 MB 217 B Admin
776005 Run 103.5 KB 346.2 GB Node
786930 IdleW 109.3 KB 8.6 GB Node
795628 IdleW 376.0 MB 24.4 KB Node
797109 Run 2.5 MB 205 B Admin
798535 IdleW 4.6 GB 186.3 KB Node
823243 Run 143 B 231 B Admin
Select statement used.
select cast((SESSION_ID) as char(10)) as "SESSION #", -
cast((STATE) as char(8)) as "STATE", -
CASE -
WHEN BYTES_SENT>1099511627776 THEN CAST(DEC(BYTES_SENT)/1024/1024/1024/1024 AS DEC(5,1))||' TB' -
WHEN BYTES_SENT>1073741824 THEN CAST(DEC(BYTES_SENT)/1024/1024/1024 AS DEC(5,1))||' GB' -
WHEN BYTES_SENT>1048576 THEN CAST(DEC(BYTES_SENT)/1024/1024 AS DEC(5,1))||' MB' -
WHEN BYTES_SENT>1024 THEN CAST(DEC(BYTES_SENT)/1024 AS DEC(5,1))||' KB' -
ELSE -
CAST(BYTES_SENT AS DEC(5,0))||' B' -
END AS "DATA SENT", -
CASE -
WHEN BYTES_RECEIVED>1099511627776 THEN CAST(DEC(BYTES_RECEIVED)/1024/1024/1024/1024 AS DEC(5,1))||' TB' -
WHEN BYTES_RECEIVED>1073741824 THEN CAST(DEC(BYTES_RECEIVED)/1024/1024/1024 AS DEC(5,1))||' GB' -
WHEN BYTES_RECEIVED>1048576 THEN CAST(DEC(BYTES_RECEIVED)/1024/1024 AS DEC(5,1))||' MB' -
WHEN BYTES_RECEIVED>1024 THEN CAST(DEC(BYTES_RECEIVED)/1024 AS DEC(5,1))||' KB' -
ELSE -
CAST(BYTES_RECEIVED AS DEC(5,0))||' B' -
END AS "DATA RECVD", -
cast((SESSION_TYPE) as char(7)) as "SESS TYPE" -
from SESSIONS order by SESSION_ID
Here's a sample of the current output:
SESSION # STATE DATA SENT DATA RECVD SESS TYPE
----------- --------- ----------- ----------- ----------
759632 Run 207.4 MB 52.5 KB Admin
761840 Run 15.4 MB 217 B Admin
776005 Run 103.5 KB 346.2 GB Node
786930 IdleW 109.3 KB 8.6 GB Node
795628 IdleW 376.0 MB 24.4 KB Node
797109 Run 2.5 MB 205 B Admin
798535 IdleW 4.6 GB 186.3 KB Node
823243 Run 143 B 231 B Admin
What I would like to see
SESSION # STATE DATA SENT DATA RECVD SESS TYPE
----------- --------- ----------- ----------- ----------
759632 Run 207.4 MB 52.5 KB Admin
761840 Run 15.4 MB 217 B Admin
776005 Run 103.5 KB 346.2 GB Node
786930 IdleW 109.3 KB 8.6 GB Node
795628 IdleW 376.0 MB 24.4 KB Node
797109 Run 2.5 MB 205 B Admin
798535 IdleW 4.6 GB 186.3 KB Node
823243 Run 143 B 231 B Admin
Select statement used.
select cast((SESSION_ID) as char(10)) as "SESSION #", -
cast((STATE) as char(8)) as "STATE", -
CASE -
WHEN BYTES_SENT>1099511627776 THEN CAST(DEC(BYTES_SENT)/1024/1024/1024/1024 AS DEC(5,1))||' TB' -
WHEN BYTES_SENT>1073741824 THEN CAST(DEC(BYTES_SENT)/1024/1024/1024 AS DEC(5,1))||' GB' -
WHEN BYTES_SENT>1048576 THEN CAST(DEC(BYTES_SENT)/1024/1024 AS DEC(5,1))||' MB' -
WHEN BYTES_SENT>1024 THEN CAST(DEC(BYTES_SENT)/1024 AS DEC(5,1))||' KB' -
ELSE -
CAST(BYTES_SENT AS DEC(5,0))||' B' -
END AS "DATA SENT", -
CASE -
WHEN BYTES_RECEIVED>1099511627776 THEN CAST(DEC(BYTES_RECEIVED)/1024/1024/1024/1024 AS DEC(5,1))||' TB' -
WHEN BYTES_RECEIVED>1073741824 THEN CAST(DEC(BYTES_RECEIVED)/1024/1024/1024 AS DEC(5,1))||' GB' -
WHEN BYTES_RECEIVED>1048576 THEN CAST(DEC(BYTES_RECEIVED)/1024/1024 AS DEC(5,1))||' MB' -
WHEN BYTES_RECEIVED>1024 THEN CAST(DEC(BYTES_RECEIVED)/1024 AS DEC(5,1))||' KB' -
ELSE -
CAST(BYTES_RECEIVED AS DEC(5,0))||' B' -
END AS "DATA RECVD", -
cast((SESSION_TYPE) as char(7)) as "SESS TYPE" -
from SESSIONS order by SESSION_ID