Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2013
    Posts
    11

    Unanswered: function that returns json string with all columns of a table

    Hi I have a trigger of this form:
    CREATE TRIGGER TBP701_UPDATE
    AFTER UPDATE ON TBP701
    REFERENCING OLD AS OLD_ROW
    NEW AS NEW_ROW
    FOR EACH ROW
    MODE DB2SQL
    INSERT INTO TBP702 ( ID , IRN , EVENT , OLDVALUES , NEWVALUES , USER , ENTRYTIME ) VALUES ( DEFAULT , NEW_ROW . COLUMN1 , 'BUYBACK_ADD' , '{"column1":"' CONCAT OLD_ROW . COLUMN1 CONCAT '","column2":"' CONCAT OLD_ROW . COLUMN2 CONCAT '","column3":"' CONCAT OLD_ROW . COLUMN3 CONCAT '"}' , '{"column1":"' CONCAT NEW_ROW . COLUMN1 CONCAT '","column2":"' CONCAT NEW_ROW . COLUMN2 CONCAT '","column3":"' CONCAT NEW_ROW . COLUMN3 CONCAT '"}' , USER , CURRENT_TIMESTAMP ) ;

    I need the old and the new values in tbp701 in json format to be inserted in a new table. I need to write such triggers on 100s of tables. So I need a sql function that can take in the
    1. NEW_ROW/OLD_ROW reference and
    2. the table name as parameters

    It should return me the json string with all the columns of that particular table. I can then reuse that function for all my triggers.

    Any help would be very highly appreciated.

    Thanks.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Try the query generator

    You can generate the query using catalog tables (z/os example).
    Than copy result and execute it:

    Code:
    with concat_columns (c_cols, K) as 
    (select Varchar('IFNULL(varchar(' ||  name || '), '''') ', 30000)  , 1
       from sysibm.syscolumns
      where tbname    = 'TBP701'
        and tbcreator  = 'tbcreator'
        and colno        = 1
    union all
    select c_cols || ' || ' || 'IFNULL(varchar(' ||  name || '), '''') ', K + 1    
      from sysibm.syscolumns
          , concat_columns 
      where tbname    = 'TBP701'
        and tbcreator = 'tbcreator'
        and colno     = K + 1
    ) 
    , 
    final_query (f_query) as 
    (
    select
    'select YT.*' || ', ' || c_cols || ' AS C_CLMN'  || ' FROM TBP701 YT '    
    from concat_columns 
       where K = (select max(K) from concat_columns)  
    ) 
    select * from final_query
    Try it. You'll get the result immediately.

    Lenny
    Last edited by Lenny77; 04-15-13 at 19:03.

  3. #3
    Join Date
    Apr 2013
    Posts
    11
    Thanks for the reply.
    When i execute this query I get the following:
    Message: [SQL0206] Column or global variable COLNO not found. Cause . . . . . : COLNO was not found as a column of table *N in *N and was not found as a global variable in *N. If the table is *N, COLNO is not a column of any table or view that can be referenced.

    I must also mention that I am a new-by to db2. So apologies if I am missing the obvious.

    Thanks.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That example was for DB2 z/OS. If you are using DB2 for LUW (Linux/UNIX/Windows) you need to use the syscat catalog views defined in the appendix to SQL Reference Vol I. The columns are somewhat different.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow You can modify the query how you need and want

    Quote Originally Posted by rearden View Post
    Thanks for the reply.
    When i execute this query I get the following:
    Message: [SQL0206] Column or global variable COLNO not found. Cause . . . . . : COLNO was not found as a column of table *N in *N and was not found as a global variable in *N. If the table is *N, COLNO is not a column of any table or view that can be referenced.

    I must also mention that I am a new-by to db2. So apologies if I am missing the obvious.

    Thanks.
    As result of my query you'll have something like following:

    Code:
    select YT.*, 
    IFNULL(varchar(CLIENT_PROF_ID), '')  || IFNULL(varchar(CLIENT_PROF_NAME), '')  || IFNULL(varchar(MODIFIED_DATE), '')  || IFNULL(varchar(CLIENT_PROF_TYP), '')  AS C_CLMN 
    FROM CLIENT_PRF YT
    You can where condition on it, you can add delimiters....


    The result of RUN is RS in 1446 rows. I just shown the method to you.

    Lenny

  6. #6
    Join Date
    Apr 2013
    Posts
    11
    Thanks for the explanation Lenny. However, I am still trying to find the LUW DB2 equivalent of the z/os example that you have given.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by rearden View Post
    Thanks for the explanation Lenny. However, I am still trying to find the LUW DB2 equivalent of the z/os example that you have given.
    You have to use SYSCAT.COLUMNS instead of sysibm.syscolumns.

    You can read about this table here:
    DB2 Universal Database

    Lenny

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Using SYSCAT.COLUMNS <LUW>

    Quote Originally Posted by rearden View Post
    Thanks for the explanation Lenny. However, I am still trying to find the LUW DB2 equivalent of the z/os example that you have given.
    Ok. I have changed the Generator for LUW tables.
    But idea stays the same.

    Code:
    with concat_columns (c_cols, K) as 
    (select CLOB('IFNULL(varchar(' || COLNAME || '), '''') ', 500000)  , 1
       from SYSCAT.COLUMNS 
      where TABNAME    = 'TBP701'
        and COLNO     = 1
    union all
    select c_cols || ' || ' || 'IFNULL(varchar(' || COLNAME || '), '''') ', K + 1 
       from SYSCAT.COLUMNS 
          , concat_columns 
      where TABNAME   = 'TBP701'
        and tbcreator = 'tbcreator'
        and COLNO     = K + 1
    ) 
    , 
    final_query (f_query) as 
    (
    select
    'select YT.*' || ', ' || c_cols || ' AS C_CLMN'  || ' FROM TBP701 YT'  
    from concat_columns 
       where K = (select max(K) from concat_columns)  
    ) 
    select * from final_query
    Please, try it.

    Lenny

  9. #9
    Join Date
    Apr 2013
    Posts
    11
    Thanks Lenny!

Tags for this Thread

Posting Permissions

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