Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: concatenate output in execute select.

    Hi colleagues,

    I need the help to somebody in the followind.

    I execute this:
    select count(*) from schema.table

    The output is this.
    select count(*) from schema.table
    3955

    I need the followind output
    select count(*) from schema.table 3955

    Thank you for you help for advanced.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    This is not standard DB2

    When you execute the statement, use x and v options and then edit your output using
    sed/awk/perl etc

    An example with sed

    Code:
    db2inst1@ip:~> cat /tmp/sql109
    select count(*) from syscat.tables ;
    
    db2inst1@ip:~> db2 -tvxf /tmp/sql109 | sed '$!N;s/\n/ /'
    select count(*) from syscat.tables          446
    HTH
    ==
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    hi colleague,

    cat tablas_tablespace_TSMAESTRAS1
    select count(*) from PP1.A0IMPRTD with UR;
    select count(*) from PP1.PROPAGACIONES with UR;
    select count(*) from PP1.A0SVCRTD with UR;
    select count(*) from PP1.HIT_RATIO with UR;
    select count(*) from PP1.A0IMGRTD with UR;
    select count(*) from PP1.A0RUTATD with UR;
    select count(*) from PP1.A0UNSSTD with UR;
    select count(*) from PP1.OPTIMA_SERVICIOS with UR;
    select count(*) from PP1.A0CONVTD with UR;
    select count(*) from PP1.A0MENSTD with UR;
    select count(*) from PP1.A0TITRTD with UR;
    select count(*) from PP1.A0TPARTD with UR;
    select count(*) from PP1.P60_INI with UR;
    select count(*) from PP1.P60_FIN with UR;

    when i execute this command
    db2 -tvxf tablas_tablespace_TSMAESTRAS1 | sed '$!N;s/\n/ /'

    the output is this.

    select count(*) from PP1.A0IMPRTD with UR 76
    select count(*) from PP1.PROPAGACIONES with UR
    60002
    select count(*) from PP1.A0SVCRTD with UR 25
    select count(*) from PP1.HIT_RATIO with UR
    22996
    select count(*) from PP1.A0IMGRTD with UR 225
    select count(*) from PP1.A0RUTATD with UR
    331594
    select count(*) from PP1.A0UNSSTD with UR 13124
    select count(*) from PP1.OPTIMA_SERVICIOS with UR
    5171
    select count(*) from PP1.A0CONVTD with UR 10997
    select count(*) from PP1.A0MENSTD with UR
    7219
    select count(*) from PP1.A0TITRTD with UR 6
    select count(*) from PP1.A0TPARTD with UR
    4
    select count(*) from PP1.P60_INI with UR 0
    select count(*) from PP1.P60_FIN with UR
    0

    Somebody can explain me this command.
    db2 -tvxf tablas_tablespace_TSMAESTRAS1 |sed '$!N;s/\n/ /'

    Thank you for advanced.

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    in a nut shal it removes new line and thus combining them into one. Very neat trick. I have never used it this way before.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Please, somebody can explain me this commands.
    db2 -tvxf tablas_tablespace_TSMAESTRAS1 | sed '$!N;s/\n/ /'

    Thank you for you help.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what is not clear
    db2 -tvxf is a standard command to invoke a sql input file : see doc for detail
    LIST COMMAND OPTIONS
    pipe ... sed is a standard unix/linux command : see details in doc
    Sed - An Introduction and Tutorial
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hi colleagues,
    Ineed somebody explain me this command sed '$!N;s/\n/ /' for i undertand y obtain my output.

    Thank you for advanced.

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    sed '$!N;s/\n/ /'
    even a unix novice like me can understand this : looking at the syntax samples
    \n --> new line to replace by blanco
    this is a question for another forum or needs some reading..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by georgipa View Post
    Hi colleagues,
    Ineed somebody explain me this command sed '$!N;s/\n/ /' for i undertand y obtain my output.

    Thank you for advanced.
    You need to get into sed for that .. You can find dozens of very useful sed scripts here :

    http://sed.sourceforge.net/sed1line.txt
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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