Results 1 to 10 of 10
  1. #1
    Member Rigido's Avatar
    Join Date
    Apr 2006
    Location
    Rome, Italy
    Posts
    56
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default Awful SQL output with v6

    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 by Rigido; 11-22-2011 at 03:34 AM.

  2. #2
    Member zsoltesz's Avatar
    Join Date
    Feb 2008
    Location
    Hungary
    Posts
    101
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Default

    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

  3. The Following User Says Thank You to zsoltesz For This Useful Post:

    Rigido (11-22-2011)

  4. #3
    Member Rigido's Avatar
    Join Date
    Apr 2006
    Location
    Rome, Italy
    Posts
    56
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by zsoltesz View Post
    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
    Code:
    tsm: TSMU07>select rpad(NODE_NAME,18),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,19),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. #4
    Member zsoltesz's Avatar
    Join Date
    Feb 2008
    Location
    Hungary
    Posts
    101
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Default

    Please read this article from IBM: http://www-01.ibm.com/support/docvie...CSSGSG7&mync=R

    Zoli

  6. #5
    Member Rigido's Avatar
    Join Date
    Apr 2006
    Location
    Rome, Italy
    Posts
    56
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    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. #6
    Member Rigido's Avatar
    Join Date
    Apr 2006
    Location
    Rome, Italy
    Posts
    56
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    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 by Rigido; 11-23-2011 at 04:00 AM.

  8. #7
    Member
    Join Date
    Sep 2002
    Location
    DK
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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. #8
    Moderator chad_small's Avatar
    Join Date
    Dec 2002
    Location
    Gilbert, AZ
    Posts
    2,191
    Thanks
    1
    Thanked 24 Times in 23 Posts

    Default

    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.
    Chad Small
    IBM Certified Deployment Professional
    chadsmal@gmail.com
    http://www.tsmadmin.com

  10. #9
    Member Rigido's Avatar
    Join Date
    Apr 2006
    Location
    Rome, Italy
    Posts
    56
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    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. #10
    Senior Member DanGiles's Avatar
    Join Date
    Oct 2002
    Location
    Toronto, Ont. Canada
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by Rigido View Post
    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"
    Daniel R. Giles
    Sileg Consulting Inc.
    416.402.9744 dan.giles@sileg.com
    http://www.linkedin.com/in/sileg

Similar Threads

  1. tsm sql query output format
    By bxtr97 in forum Scripting
    Replies: 5
    Last Post: 11-29-2010, 06:18 PM
  2. SQL Select output to file
    By scottgassTSM in forum Scripting
    Replies: 4
    Last Post: 05-09-2008, 05:42 AM
  3. Simple SQL query into single-line output?
    By kzw2zx in forum Scripting
    Replies: 10
    Last Post: 02-13-2008, 03:21 PM
  4. sql select and blank line in output?
    By Stephan in forum Scripting
    Replies: 0
    Last Post: 01-17-2007, 01:24 PM
  5. Export Server Scripts/SQL query output
    By vogant in forum Scripting
    Replies: 1
    Last Post: 09-04-2006, 10:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •