Awful SQL output with v6

[Rigido]

Hi,
for my everyday checks I use some scripts from the command line, something like:

select NODE_NAME,PLATFORM_NAME,DOMAIN_NAME,TCP_NAME,TCP_ADDRESS,(CURRENT_DATE - date(LASTACC_TIME)) as LAST_ACC from NODES where NODE_NAME like upper('%$1%')

With TSM v5 it's easy to have a "nice looking" output, it's sufficient to have a well sized window and you'll have something like:

tsm: TSMU04>run qip test

NODE_NAME             PLATFORM_NAME       DOMAIN_NAME           TCP_NAME              TCP_ADDRESS           LAST_ACC
------------------    ----------------    ------------------    ------------------    ------------------    --------
TESTSQL               TDP MSSQL Win64     PD_SQL                TIDBSTIM02WTST        10.248.164.82              665

Well, with v6 you'll have:

    NODE_NAME: IPTSVIL
PLATFORM_NAME: WinNT
  DOMAIN_NAME: PD_BA
     TCP_NAME: IPTSVIL
  TCP_ADDRESS: 10.233.163.104
     LAST_ACC: 0

It looks like v5 will trim unnecessary blanks and v6 will not, I even tried to cast all columns as varchar but it looks like some columns aren't cast as I wanted.
Is there any better (and faster) way to have the same output on v6 as v5? Keep in mind that I'm talking about scripts that I use on the admin command line and not as external (so no dataonly etc.).

Thanks.

EDIT: While writing I said that looks like v6 doesn't "trim" so I even tried to ltrim(rtrim(COLUMN)) but it didn't work.

 

[zsoltesz]

Hi Rigido,

Instead of TRIM, you can use the RPAD scalar function. For example: Select RPAD(node_name,20) as node, RPAD(platform_name,20) as platform from nodes.

Zoli

 

[Rigido]

Hi Rigido,

Instead of TRIM, you can use the RPAD scalar function. For example: Select RPAD(node_name,20) as node, RPAD(platform_name,20) as platform from nodes.

Zoli

Thank you Zoli, rpad function is perfect, but I think the problem is somewhere else...
If the output is <=78 it works

tsm: TSMU07>select rpad(NODE_NAME,[b][u]18[/u][/b]),rpad(PLATFORM_NAME,15),rpad(DOMAIN_NAME,10),rpad(TCP_NAME,15),rpad(TCP_ADDRESS,15),rpad((CURRENT_DATE - date(LASTACC_TIME)),5) as LAST from NODES where NODE_NAME like upper('%svil%')

Unnamed[1]          Unnamed[2]       Unnamed[3]  Unnamed[4]                      Unnamed[5]                      LAST
------------------- ---------------- ----------- ------------------------------- ------------------------------- ------
IPTSVIL             WinNT            PD_BA       IPTSVIL                         10.233.163.104                  0

If the output is >78 it doesn't work, no matter how large is your window (mine is 120columns)

tsm: TSMU07>select rpad(NODE_NAME,[b][u]19[/u][/b]),rpad(PLATFORM_NAME,15),rpad(DOMAIN_NAME,10),rpad(TCP_NAME,15),rpad(TCP_ADDRESS,15),rpad((CURRENT_DATE - date(LASTACC_TIME)),5) as LAST from NODES where NODE_NAME like upper('%svil%')

Unnamed[1]: IPTSVIL
Unnamed[2]: WinNT
Unnamed[3]: PD_BA
Unnamed[4]: IPTSVIL
Unnamed[5]: 10.233.163.104
      LAST: 0

Does someone know if there is a setting somewhere in DB2/TSM?

Grazie.

 

[zsoltesz]

Please read this article from IBM: http://www-01.ibm.com/support/docview.wss?uid=swg21502715&myns=swgtiv&mynp=OCSSGSG7&mync=R

Zoli

 

[Rigido]

Thank you again Zoli but that article doesn't explain where it will get the "single line" width.
As I wrote before, my window is 120 columns wide and every columns of the select is "rpadded" to keep total length under control, but if you want a table output the total length MUST be under 79 chars.
And you know what?
Take a look at the 78 chars output and count how many chars are EFFECTIVELY there...119! In fact colum 4 and 5 are wider than rpad value of 15.
I think I'm going to open an APAR at IBM.

 

[Rigido]

Ok, it is not so simple...still investigating.
Does someone know why 15 chars "dpadded" columns as TCP_NAME and TCP_ADDRESS are shown as a 30 chars dpadded column?
Just to be sure:

tsm: TSMU07>select max(length(TCP_NAME)) from nodes

  Unnamed[1]
------------
          15

 

[Geirr]

Hi

Sorry to "reopen" this old thread, but I would like to know if you found a solution to this. I'm dealing with the same issues and would like to have the cleaner output in v. 6.

/G

 

[chad_small]

My only solution was to do as you did, or to dump the file to a delimited file and use PERL to format a better report.

 

[Rigido]

Hi all,
I'm sorry i lost this thread and did update it.
I didn't find why tcp_address column is shown that way, I thought of a double-byte code page or similar but I'm not a DB2 admin and I don't even know where to start.
The solution I found was to uset he LEFT function to cut the output.

 

[DanGiles]

Hi all,
I'm sorry i lost this thread and did update it.
I didn't find why tcp_address column is shown that way, I thought of a double-byte code page or similar but I'm not a DB2 admin and I don't even know where to start.
The solution I found was to uset he LEFT function to cut the output.

I've always just used the cast function. E.G.

select cast((schedule_name) as char(23)) as "Schedule Name", cast((node_name) as char(30)) as "Node Name",cast((actual_start) as char(10)) as "Start Day", substr(char(actual_start),12,5) as "Stime", cast((completed) as char(10)) as "End Day", substr(char(completed),12,5) as "Etime", cast((result) as char(5)) as "Result" from events where status='Completed' and node_name='$1' order by "Start Day"