Take the 2-minute tour ×
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. It's 100% free, no registration required.

It's easy to find all SCD tables through

select 
    * 
from 
    syscat.TABLES t 
    join
    syscat.COLUMNS s
    on t.tabname = s.tabname 
where 
    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
add comment

2 Answers

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 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 at 13:38
add comment

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 ---                                        ---
--#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
        ,CHAR(COALESCE(return_VARCHAR(
            ' 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)    
FOR FETCH ONLY
WITH UR;
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 at 13:41
    
SERVICE_LEVEL(DB2 v9.7.0.6) FIXPACK_NUM(6) –  zinking Jan 17 at 1:26
add comment

Your Answer

 
discard

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.