DB2 Administrative SQL Cookbook: Listing the Columns of a Table in a Comma Separated List

You may also like...

7 Responses

  1. Dave Nance says:

    Since you are covering LISTAGG, thought you might like an XMLAGG as well. The below will take a string and break it up into chunks of a specified length to be inserted to a table as individual rows. The view will show it as one long string. I got tired of apps asking to make a comment string larger. Now they can put whatever size comment they want and I never have to alter a table again.

    drop view comment_tab_v;
    drop table comment_tab;

    create table comment_tab (key int not null, seq int not null, chunk varchar(10), primary key(key, seq));

    commit;

    create view comment_tab_v (key, str) as
    (select key, XMLSERIALIZE(
    XMLAGG( XMLTEXT( chunk))AS CLOB(4096000) ) as str
    from comment_tab
    group by key);

    create trigger comment_tab_v_iir
    instead of insert
    on comment_tab_v
    referencing new as n
    for each row
    insert into comment_tab
    with a(seq, start_pos, len) as (
    select 1, 1, min(length(n.str), 10) from sysibm.sysdummy1
    union all
    select a.seq+1, a.start_pos + a.len, min(length(n.str) – (a.start_pos + a.len) + 1, 10)
    from a
    where min(length(n.str) – (a.start_pos + a.len) + 1, 10) > 0
    )
    select n.key, a.seq, substr(n.str, a.start_pos, a.len) chunk
    from a;

    –#SET TERMINATOR ?

    create trigger comment_tab_v_uir
    instead of update
    on comment_tab_v
    referencing new as n
    old as o
    for each row MODE DB2SQL
    begin atomic
    delete from comment_tab
    where o.key = key;
    insert into comment_tab
    with a(seq, start_pos, len) as (
    select 1, 1, min(length(n.str), 10) from sysibm.sysdummy1
    union all
    select a.seq+1, a.start_pos + a.len, min(length(n.str) – (a.start_pos + a.len) + 1, 10)
    from a
    where min(length(n.str) – (a.start_pos + a.len) + 1, 10) > 0
    )
    select n.key, a.seq, substr(n.str, a.start_pos, a.len) chunk
    from a;

    end?

    –#SET TERMINATOR ;
    create trigger comment_tab_v_dir
    instead of delete
    on comment_tab_v
    referencing old as o
    for each row
    delete from comment_tab
    where o.key = key;

    insert into comment_tab_v values (2, ‘this is a test of the new clob size of 4096000 to see if it works as well as 4000.’);

    update comment_tab_v
    set str = ‘this is an updated test of the new clob size of 4096000 to see if it works as well as 4000. If it does we are looking to create the comment table on LUW and federate to it from the mainframe using a three part name.’
    where key =2;

    –delete from comment_tab_v
    –where key = 2;

    select * from comment_tab_v where key=2;
    select * from comment_tab where key=2;

    • Ember Crooks says:

      As I understand it, XMLAGG became available first. It is probably the better option for strings larger than 32,768 bytes. I see your reasoning for this approach, but I’m not sure it would be my choice. I’d have to test it against other options with a volume of data to understand whether this is better or just taking the hit on administration time for properly sized columns.

  2. Dennis McClure says:

    Doesn’t work on DB2 9.7 FP 7

    G:\db2\db2per\db2_software\BIN\dennis>db2 -mtvf list_col.sql
    select listagg(cast(colname as varchar(10000)), ‘, ‘) within group (order by colno) as columns from syscat.columns where tabschema=’SAPPER’
    and tabname=’VBPA’ with ur
    SQL0440N No authorized routine named “LISTAGG” of type “FUNCTION” having
    compatible arguments was found. SQLSTATE=42884

    G:\db2\db2per\db2_software\BIN\dennis>db2level
    DB21085I This instance or install (instance name, where applicable: “DB2PER”)
    uses “64” bits and DB2 code release “SQL09077” with level identifier
    “08080107”.
    Informational tokens are “DB2 v9.7.700.552”, “s121002”, “IP23369”, and Fix Pack
    “7”.
    Product is installed at “G:\db2\db2per\db2_software” with DB2 Copy Name
    “SAPDB2PER”.

    G:\db2\db2per\db2_software\BIN\dennis>

  3. Luiz da Silva says:

    Hi Ember,

    I recently found myself in this kind of need. Just sharing another approach using recursive common table expression:

    with cte ( schema , table, colno, list_of_columns )
    as
    (
    select rtrim(base.tabschema), rtrim(base.tabname), base.colno, cast(rtrim(base.colname) as clob)
    from syscat.columns base
    where base.tabschema = ‘DB2INST1’ and base.tabname = ‘EMPLOYEE’ and base.colno = 0

    union all

    select rtrim(t1.tabschema), rtrim(t1.tabname), t1.colno, cast(rtrim(t0.list_of_columns) as clob) || ‘, ‘ || cast(rtrim(t1.colname) as clob)
    from cte t0, syscat.columns t1
    where t0.schema = t1.tabschema and t0.table = t1.tabname
    and t1.colno = t0.colno + 1
    )
    select schema, colno, cast(list_of_columns as clob) from cte

    SCHEMA COLNO LIST_OF_COLUMNS
    ——- —– ——————————————————————————————————————
    DB2INST1 0 EMPNO
    DB2INST1 1 EMPNO, FIRSTNME
    DB2INST1 2 EMPNO, FIRSTNME, MIDINIT
    DB2INST1 3 EMPNO, FIRSTNME, MIDINIT, LASTNAME
    DB2INST1 4 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT
    DB2INST1 5 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO
    DB2INST1 6 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE
    DB2INST1 7 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB
    DB2INST1 8 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL
    DB2INST1 9 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX
    DB2INST1 10 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE
    DB2INST1 11 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY
    DB2INST1 12 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS
    DB2INST1 13 EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM

    Regards,

    Luiz

Leave a Reply

Your email address will not be published. Required fields are marked *