Loop through the list of tables and check for a value in a field (DB2)

In DB2, I can get a list of tables with the following sql statement:

select tabname from syscat.tables where `tabschema = 'DBO'

Assuming that开发者_如何转开发 each table has a field named a1, how can I loop through the tables and check for a value in that field in every table?

There are two general ways. One would be to write a program that processes each file to check that column. The program could use embedded SQL to retrieve the count of the chosen value from each table. Or you could create a stored proc that accepts a table and schema name as inputs and sets an output value as essentially a boolean indicator of whether or not that table had the chosen value.

Potentially, you could perhaps create an outer proc to loop through the list of tables. And for each table it would call the inner proc that tests presence of the value.

This is a test proc that I used to verify the basic principle. It checks a column for APFILE='ACCPTH'. It returns either (1) or (0) depending on whether any row has that value or not.

--  Generate SQL 
--  Version:                    V6R1M0 080215 
--  Generated on:               03/22/14 02:59:07 
--  Relational Database:        TISI 
--  Standards Option:           DB2 for i 
DROP SPECIFIC PROCEDURE SQLEXAMPLE.CHKFLDVAL ; 

SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","mylib" ; 

CREATE PROCEDURE SQLEXAMPLE.CHKFLDVAL ( 
    IN TABLENAME VARCHAR(128) , 
    IN SCHEMANAME VARCHAR(128) , 
    OUT VALFOUND SMALLINT ) 
    LANGUAGE SQL 
    SPECIFIC SQLEXAMPLE.CHKFLDVAL 
    NOT DETERMINISTIC 
    READS SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    CLOSQLCSR = *ENDMOD , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DLYPRP = *NO , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    RDBCNNMTH = *RUW , 
    SRTSEQ = *HEX   

    P1 : BEGIN

 DECLARE STMTSQL VARCHAR ( 256 ) ;
 DECLARE RTNRESULT SMALLINT ;

 SET STMTSQL = 'VALUES (select CASE WHEN count(*) = 0 THEN 0 ELSE 1 END as chkVal from ' CONCAT SCHEMANAME CONCAT '.' CONCAT TABLENAME CONCAT ' where APFILE=''ACCPTH'' group by APFILE) INTO ?' ;

 PREPARE STMT_NAME FROM STMTSQL ;
 EXECUTE STMT_NAME USING RTNRESULT ;

 SET VALFOUND = RTNRESULT ;

 END P1  ; 

COMMENT ON SPECIFIC PROCEDURE SQLEXAMPLE.CHKFLDVAL 
    IS 'Check field value in some table' ;

If I call it with a different TableName or SchemaName parameter value, I can get different values returned in rtnResult.

SQL is all that's actually needed. It's not a particularly good thing for SQL to do.

You cannot do this using just SQL statements. You will have to do a bit of scripting or programming of some sort to create new queries based on the table names you find and run them.