It's easy to find all SCD tables through

    syscat.TABLES t 
    syscat.COLUMNS s
    on t.tabname = s.tabname 
    s.COLNAME = 'EFFECTIVE_DTE' and t.type='T'
    with ur;

problem is how to further apply SQLs on the resut tables sets and check if each contain data for specific date ? say:

select effective_dte, count(*) from TABLE1 group by TABLE1 where ...

is it possible to futher apply the queries to all result tables


You should be able to create a stored procedure that executes your query to get the table names. Then you loop over the results and execute your effective date query for each table individually.

You might even be able to do this without wrapping it into an stored procedure.

  • how, that is the question for not wrapping it into SP – zinking Nov 21 '13 at 1:17
  • I don't see any methods without using of dynamic SQLs to convert Table name string to Table refs in SQL so far. – zinking Jan 15 '14 at 6:39
  • Check out the DB2 SQL Cookbook (mysite.verizon.net/Graeme_Birchall/id1.html). Chapter "Running SQL Within SQL" would be the solution you might be looking for. It uses a java scalar function. – Peter Schuetze Jan 15 '14 at 13:38

As Pointed by @PeterSchuetze, In later versions of DB2 you might be able to use return_VARCHAR to inline DYNAMIC SQL directly in SQL

I tried in DB2 9.7, not working thought

--#COMMENT ----------------------------------------------
--#COMMENT ---                                        ---
--#COMMENT ---    List tables that have a department  ---
--#COMMENT ---    column (that is suitably indexed),  ---
--#COMMENT ---    and that have at least one row for  ---
--#COMMENT ---    the 'A00' department.               ---
--#COMMENT ---                                        ---
--#COMMENT ----------------------------------------------
SELECT   CHAR(tab.tabname,15)   AS tabname
        ,CHAR(col.colname,10)   AS colname
            ' SELECT ''Y'''  ||
            ' FROM '  || tab.tabschema  || '.' || tab.tabname ||
            ' WHERE ' || col.colname    || ' = ''A00''' ||
            ' FETCH FIRST 1 ROWS ONLY ' ||
            ' OPTIMIZE FOR 1 ROW ' ||
            ' WITH UR' 
         ),'N'),1) AS has_dept
FROM     syscat.columns col
        ,syscat.tables  tab
WHERE    col.tabschema  =  USER
  AND    col.colname   IN ('DEPTNO','WORKDEPT')
  AND    col.tabschema  =  tab.tabschema
  AND    col.tabname    =  tab.tabname
  AND    tab.type       =  'T'
  AND    col.colname   IN
        (SELECT SUBSTR(idx.colnames,2,LENGTH(col.colname))
         FROM   syscat.indexes idx
         WHERE  tab.tabschema = idx.tabschema
           AND  tab.tabname   = idx.tabname)    
  • which fix pack of 9.7? because some things may not work in one but work in another. – Chris Aldrich Jan 16 '14 at 13:41
  • SERVICE_LEVEL(DB2 v9.7.0.6) FIXPACK_NUM(6) – zinking Jan 17 '14 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.