Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6

    Answered: appended space after query ouput

    10.5.5 on Linux
    running this query produces the correct o/p but a truckload of spaces are appended. I can't work out why the spaces are appended - I know I can clean them with SED but I don't believe they should be there in the first place. Can anyone give me a hint?

    select 'alter table '||rtrim(a.tabschema)||'.'||rtrim(a.tabname)||' add constraint '||rtrim(a.tabname)||'_PK primary key ('||listagg(rtrim(b.colname),',') within group (order by colseq)||') ;'
    from syscat.indexes a
    inner join
    syscat.indexcoluse b
    on a.indschema=b.indschema
    and a.indname=b.indname
    where a.tabschema='SYNCSOFT'
    and a.uniquerule='P'
    and a.tabname not like 'EXPLAIN%'
    and a.tabname not like 'ADVISE%'
    group by tabschema,tabname ;


    o/p file displayed in hex
    0000000: 616c 7465 7220 7461 626c 6520 5359 4e43 alter table SYNC
    0000010: 534f 4654 2e41 4343 4f55 4e54 5f48 4541 SOFT.ACCOUNT_HEA
    0000020: 4445 5220 6164 6420 636f 6e73 7472 6169 DER add constrai
    0000030: 6e74 2041 4343 4f55 4e54 5f48 4541 4445 nt ACCOUNT_HEADE
    0000040: 525f 504b 2070 7269 6d61 7279 206b 6579 R_PK primary key
    0000050: 2028 504b 5f49 4429 203b 2020 2020 2020 (PK_ID) ;
    0000060: 2020 2020 2020 2020 2020 2020 2020 2020
    0000070: 2020 2020 2020 2020 2020 2020 2020 2020
    0000080: 2020 2020 2020 2020 2020 2020 2020 2020
    ........
    and many more until next statement
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  2. Best Answer
    Posted by gvphubli

    "I also write query for generating script, what I usually do is for eg if I am referencing tabname from syscat.tables..I enclose tabname column like this

    trim(cast(tabname as varchar(50))) - this will get rid of all the unwanted spaces."


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    This is the way how DB2 CLP works. It pads the character fields to the maximum possible length of each field to line up the column headers & rows.
    Use the following instead:

    export to myfile.txt of del modified by nochardel
    select ...
    Regards,
    Mark.

  4. #3
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1
    I also write query for generating script, what I usually do is for eg if I am referencing tabname from syscat.tables..I enclose tabname column like this

    trim(cast(tabname as varchar(50))) - this will get rid of all the unwanted spaces.
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

  5. #4
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    thanks guys. After all the years of just using rtrim I'd not been caught out. So now I've learned another thing!
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Posting Permissions

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