Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557

    Unanswered: getting LTRIM and RTRIM to work

    My success rate to getting those to work is only .5%

    what is a magic?

    db2 "select rtrim(INDSCHEMA), rtrim(INDNAME), rtrim(COLNAME) from syscat.INDEXCOLUSE"
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  2. #2
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    does not work for me either. It works in concatenations though:

    db2 "select rtrim(INDSCHEMA)||‘.’||rtrim(INDNAME)||’ ’||rtrim(COLNAME) from syscat.INDEXCOLUSE"
    Last edited by MarkhamDBA; 05-14-09 at 15:23.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Cool, that just might work for what I am looking to do with it.

    Bella, can you shine a light on it why it works the way it works, and maybe you can ask your IBM friends to get it to work
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I assume you problem is the display in the CLP still showing too long. The reason is that the column has a maximum length and that is what DB2 uses to return the column lengths. Doing rtrim has no visual effect. It would work in an application where the strings had trailing blanks.

    Andy

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Yes Andy,

    I am using telnet and it will stay there. I guess it does not have to win a pageant contest
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can shorten up the fields this way, but it may cause some truncation:


    db2 "select varchar(INDSCHEMA,30), varchar(INDNAME,30), varchar(COLNAME,30) from syscat.INDEXCOLUSE"


    Andy

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    truncation is the problem I can not afford. I am going with SUBSTR and giving plenty room to make sure that nothing will get cut.

    Thank you.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Пума,

    The number of dashes in the column heading can't be trimed. CLP has no idea what the next column is going to be like. Since we don't know the length of the next column, we need to consider the maximum length so that the data is column-aligned properly. substr gives CLP a clear indication that the output will have a constant column length so it's able to align the output properly.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Cougar8000
    db2 "select rtrim(INDSCHEMA), rtrim(INDNAME), rtrim(COLNAME) from syscat.INDEXCOLUSE"
    If it's a display problem, maybe the following will work?
    Code:
    db2 -x "select rtrim(INDSCHEMA), rtrim(INDNAME), rtrim(COLNAME) from syscat.INDEXCOLUSE"
    (For me, on Linux, it works both with and without "-x".)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    It is a display issue and no, -x did not work for me. Thank you.

    Bella,

    огромное спасибо
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    you're welcome, Oлег

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The -x doesn't help, of course, because the maximum length of the column doesn't change. And since DB2 tries to come up with a tabular output and because it doesn't know the longest of all values in the result, it has to stick with the maximum length.

    Since you cannot tolerate truncation, you cannot use SUBSTR() because that would truncate the values if they are too long - and you wouldn't even get a warning on that.

    What I would do in your case is this:
    - ignore the issue - why do some spaces bother you?
    - use some scripting language like Perl and its connectivity modules for DB2 and fetch the results regularly
    - use "sed" or some other tool to remove multiple spaces:
    Code:
    db2 -x "..." | sed -e 's/  *//g'
    Also note that if you have LOBs in your output, those will be truncated at 4K or 8K (I don't know which). The reason is simply that the DB2 won't be so stupid as trying to put 2GB to the console, which just takes a few hours to write all the dashes and the values - and no one could really do anything with this anyway.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Knut,

    I hear what you are saying as far as SUBSTR goes. I just refuse to accept that someone would generate a schema name longer then 15 char and tab or ind name longer then 50.

    I was just trying to get the layout in a way that it would be easy to look at it, even so I would be doing further changes to it.

    Your method did crossed my mind I was just trying to avoid it for some reason and get db2 to do it all. As it stands right now I will use your method if there is no royalty

    Danke.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    If you really want a "nice looking" tabular output, but with a minimum of useless spaces, you could use perl or sed to replace the inter-column spaces into TABs and load the resulting tab-separated file into Excel (or OpenOffice of course).
    Or maybe even better, convert it to an HTML table and look at it with your favourite browser:
    Code:
    db2 -x "select '<tr><td>'.rtrim(INDSCHEMA)||'</td><td>'||rtrim(INDNAME)
         ||'</td><td>'||rtrim(COLNAME)||'</td></tr>' from syscat.INDEXCOLUSE" \
    | perl -p -e "BEGIN{print '<html><body><table border=1>';}s/\s//g;
                  END{print '</table></body></html>';}" > indexcoluse.html
    Last edited by Peter.Vanroose; 05-15-09 at 21:14.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Cougar8000
    Knut,

    I hear what you are saying as far as SUBSTR goes. I just refuse to accept that someone would generate a schema name longer then 15 char and tab or ind name longer then 50.
    I don't like such very restrictive limitations. I consider self-explaining and well-chosen names extremely important for maintenance and the times where disk space was not available and that's why you had to use short, cryptic identifiers have long passed.

    Also, I think you are on very thin ice with your assumption refusal - especially since you also used the word "generate"...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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