Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer
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 ( 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)    
share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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