1. Please help support our sponsors by considering their products and services.
    Our sponsors enable us to maintain high-speed Internet connection and fast webservers.
    They support this free information and knowledge exchange forum service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions

[HOWTO] GROUP_CONCAT on DB2

Discussion in 'Scripting' started by Rigido, Nov 13, 2017.

  1. Rigido

    Rigido ADSM.ORG Senior Member

    Joined:
    Apr 21, 2006
    Messages:
    106
    Likes Received:
    5
    Occupation:
    AIX, TSM and Storage specialist
    Location:
    Rome, Italy
    Hi,
    I'm trying to do a script to extract all info about schedules, something like Q SCHED + Q ASSOC.
    The problem is that the inner query from the associations table could return more than one row and DB2 gives error. I read that MySQL has the function group_concat, is there something similar on DB2 or does exist any way to concatenate values?

    Thanks.
     
  2.  
  3. Rigido

    Rigido ADSM.ORG Senior Member

    Joined:
    Apr 21, 2006
    Messages:
    106
    Likes Received:
    5
    Occupation:
    AIX, TSM and Storage specialist
    Location:
    Rome, Italy
    Found it!
    It is a script to generate a comma separated output to import into a spreadsheet (dsmadmc -id=admin -password=XXX -dataonly=y -display=table -comma "run qsched" > QSCHED.TXT).
    Maybe it could be useful for someone, here it is:
    Code:
    select -
    rpad(SCHEDULE_NAME,30) as SCHEDULE_NAME, -
    rpad(DESCRIPTION,100) as DESCRIPTION, -
    rpad(ACTION, 15) as ACTION, -
    rpad(OPTIONS,40) as OPTIONS, -
    rpad(OBJECTS,200) as OBJECTS, -
    STARTTIME as STARTTIME, -
    rpad(PERIOD,2)||' '||rpad(PERUNITS, 5) as PERIOD, -
    rpad(DAYOFWEEK,50) as DAYOFWEEK, -
    '"'||(select listagg(NODE_NAME, ',') from ASSOCIATIONS A where A.SCHEDULE_NAME=C.SCHEDULE_NAME)||'"' as ASSOCIATED -
    from CLIENT_SCHEDULES C
    Bye
     
    marclant likes this.

Share This Page