TSM v6 sql output displays in columns not rows

[tsm_dude]

TSM v6 standard SQL output;

tsm: TSM6>select volume_name, access, status, pct_utilized, pct_reclaim from volumes where devclass_name like '%LTO%'

VOLUME_NAME: HR0001L3
ACCESS: OFFSITE
STATUS: FULL
PCT_UTILIZED: 78.1
PCT_RECLAIM: 24.8

VOLUME_NAME: HR0002L3
ACCESS: OFFSITE
STATUS: FULL
PCT_UTILIZED: 75.2
PCT_RECLAIM: 26.8

VOLUME_NAME: HR0003L3
ACCESS: READWRITE
STATUS: FULL
PCT_UTILIZED: 100.0
PCT_RECLAIM: 0.0

VOLUME_NAME: HR0004L3
ACCESS: READWRITE
STATUS: FULL
PCT_UTILIZED: 100.0
PCT_RECLAIM: 0.0

VOLUME_NAME: HR0005L3
ACCESS: READWRITE
STATUS: FULL
PCT_UTILIZED: 69.4
PCT_RECLAIM: 32.7


TSM v5 standard sql output;

tsm: TSM5>select volume_name, access, status, pct_utilized, pct_reclaim from volumes where devclass_name like '%LTO%'

VOLUME_NAME ACCESS STATUS PCT_UTILIZED PCT_RECLAIM
------------------ ------------------ ------------------ ------------ -----------
G00002L4 READWRITE FULL 65.0 35.9
G00003L4 UNAVAILABLE FULL 100.0 0.0
G00007L4 READWRITE FULL 86.4 13.7
G00024L4 OFFSITE FULL 60.0 39.9
G00025L4 OFFSITE FULL 60.7 39.2
G00026L4 OFFSITE FULL 22.9 77.7
G00031L4 READONLY FULL 100.0 0.0
G00032L4 OFFSITE FULL 87.7 12.3
G00034L4 OFFSITE FULL 100.0 0.0
G00039L4 OFFSITE FULL 70.2 29.7
G00041L4 OFFSITE FULL 45.9 55.2
G00043L4 READWRITE FULL 100.0 0.0
G00044L4 OFFSITE FULL 30.4 70.6
G00045L4 OFFSITE FULL 39.1 61.2



Anybody know how to make TSM6 output display same as TSM5 ? IS there a column/row setting
Thanks

 

[Ale]

Hi,
The only solution that I am using right now is the dsmadmc -tab
I think it is a kind of bug or something, maybe Db2 thing, not sure.

 

[Harry_Redl]

Hi,

there is a way - not pretty ....

select cast(volume_name as char(8)) as Volume, cast(access as char(10)) as Access,cast(status as char(10)) as Status, cast(pct_utilized as decimal(4,2)) as PctUtil, cast(pct_reclaim as decimal(4,2)) as PctRecl from volumes where devclass_name like '%LTO%'

You got the idea .... window width must be large enough so the output fits in ...

Harry

EDIT: I had to modify it - the numbers have to be as "decimal" not "char"

 

[dyna]

Is there any update to this issue? An easier way than casting everything?

Thanks

 

[DaJuRan]

Hi Dyna,

Essentially any method to shorten the columns width so that they all fit accross the console display is what has to be done here, so that casting method indicated or could use similarly a substr method or even try shortening the column headings in the case of the pct_utilized and pct_reclamable as the headings are much large then the possible values of the field.

select substr(volume_name,1,10) as vname,substr(access,1,10) as access,substr(status,1,10) as status, pct_utilized as "util%", pct_reclaim as "rec%" from volumes where devclass_name like '%LTO%'

DaJ

 

[okZaphod]

Simple...

Is there any update to this issue? An easier way than casting everything?

Thanks

Why would you not just use the simplest version, especially if loading into excel vs trying to format all the columns to fit a display...
in dsmadmc
select -comma * from nodes
or
select -tab * from nodes

works for me.

(sorry - Hadn't logged into adsm.org in a while)

 

[GregE]

Testing v6.3 here, working to upgrade from 5.5. I'm running this:

select -tab volume_name from volumes
and result is...
VOLUME_NAME: 001023L4
VOLUME_NAME: 001024L4
VOLUME_NAME: 001025L4
VOLUME_NAME: 001026L4

There is ample window width to put these in rows so that is not the issue. Any idea why?

 

[GregE]

hmm, had to use DaJuRan's method.
select substr(volume_name,1,10) from volumes

But I don't understand why.

 

[TiborB]

But I don't understand why.

Every column in db2 table has defined lenght. F.e STGPOOL_NAME is 31 characters long. And all 31 character are to be printed regardless of efective use of those characters. The result is the row would be longer than the row on the console so the output must be formated in 'list format'

If you are curious of effective length of a column try something like this:

select max(length(stgpool_name)) from stgpools

EDIT: fixed 'terminilogy'

 

[GregE]

I don't understand what you are telling me.

select max(length(volume_name)) from volumes
....is 40

If my console is wider than 40 characters, why doesn't it show me these volumes a single column?

VOLUME_NAME
------------------
001001L4
001005L4
001013L4
001021L4
001022L4

 

[TiborB]

Well it took some time for me to grasp what you mean

there are two things: length of colum defined in database:

select length from syscat.columns where COLNAME='VOLUME_NAME' and TABNAME='VOLUMES'

LENGTH
------------
256

and size of longest item in column VOLUME_NAME (this is what you got)

 

[eddytrio]

try running

dsmadmc -tab

the only solution till now.

 

[GregE]

try running

dsmadmc -tab

I did that earlier, but I still get field name on every line.

select -tab volume_name from volumes
and result is...
VOLUME_NAME: 001023L4
VOLUME_NAME: 001024L4
VOLUME_NAME: 001025L4
VOLUME_NAME: 001026L4

 

[TiborB]

select cast(VOLUME_NAME as char(20 )) as volume from volumes

VOLUME
---------------------
00050AL3
00060AL3
00080AL3
00100AL3

 

[eddytrio]

I did that earlier, but I still get field name on every line.

select -tab volume_name from volumes
and result is...
VOLUME_NAME: 001023L4
VOLUME_NAME: 001024L4
VOLUME_NAME: 001025L4
VOLUME_NAME: 001026L4

its not only running the specific command its about starting the dsmadmc with -tab option. This would let your all sql queries come with desired table output.

 

[GregE]

Ahh ok, didn't realize that. Thank you.