1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

Awful SQL output with v6

Discussion in 'Scripting' started by Rigido, Nov 22, 2011.

  1. Rigido

    Rigido New Member

    Joined:
    Apr 21, 2006
    Messages:
    67
    Likes Received:
    1
    Occupation:
    AIX, TSM and Storage specialist
    Location:
    Rome, Italy
    Hi,
    for my everyday checks I use some scripts from the command line, something like:
    Code:
    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:
    Code:
    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:
    Code:
        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.
     
    Last edited: Nov 22, 2011
  2.  
  3. zsoltesz

    zsoltesz New Member

    Joined:
    Feb 26, 2008
    Messages:
    107
    Likes Received:
    4
    Location:
    Hungary
    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 likes this.
  4. Rigido

    Rigido New Member

    Joined:
    Apr 21, 2006
    Messages:
    67
    Likes Received:
    1
    Occupation:
    AIX, TSM and Storage specialist
    Location:
    Rome, Italy
    Thank you Zoli, rpad function is perfect, but I think the problem is somewhere else...
    If the output is <=78 it works
    Code:
    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)
    Code:
    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.
     
  5. zsoltesz

    zsoltesz New Member

    Joined:
    Feb 26, 2008
    Messages:
    107
    Likes Received:
    4
    Location:
    Hungary
  6. Rigido

    Rigido New Member

    Joined:
    Apr 21, 2006
    Messages:
    67
    Likes Received:
    1
    Occupation:
    AIX, TSM and Storage specialist
    Location:
    Rome, Italy
    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.
     
  7. Rigido

    Rigido New Member

    Joined:
    Apr 21, 2006
    Messages:
    67
    Likes Received:
    1
    Occupation:
    AIX, TSM and Storage specialist
    Location:
    Rome, Italy
    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:
    Code:
    tsm: TSMU07>select max(length(TCP_NAME)) from nodes
    
      Unnamed[1]
    ------------
              15
     
    Last edited: Nov 23, 2011
  8. Geirr

    Geirr New Member

    Joined:
    Sep 13, 2002
    Messages:
    27
    Likes Received:
    0
    Occupation:
    TSM Administrator
    Location:
    DK
    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
     
  9. chad_small

    chad_small Moderator

    Joined:
    Dec 17, 2002
    Messages:
    2,205
    Likes Received:
    44
    Occupation:
    AIX/SAN/TSM
    Location:
    Gilbert, AZ
    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.
     
  10. Rigido

    Rigido New Member

    Joined:
    Apr 21, 2006
    Messages:
    67
    Likes Received:
    1
    Occupation:
    AIX, TSM and Storage specialist
    Location:
    Rome, Italy
    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.
     
  11. DanGiles

    DanGiles Senior Member

    Joined:
    Oct 25, 2002
    Messages:
    566
    Likes Received:
    10
    Occupation:
    Sr. Storage Admin
    Location:
    Toronto, Ont. Canada
    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"
     

Share This Page