I am trying to automate re-setting the integrity on all user tables after an APPLY cycle. The problem we have is there are many clients running db2 Personal Edition on laptops. They connect in to synch periodically, which runs a replication capture-apply cycle. Depending on how long since their last synch, ASNLOAD may be invoked which leaves some tables in CHECK PENDING state.

Anyway, I had thought to use the SYSPROC.ADMIN_CMD procedure to execute commands I could dynamically generate. For example:
CALL SYSPROC.ADMIN_CMD('SET INTEGRITY FOR myschema.mytable IMMEDIATE CHECKED')

Then I could make a stored proc that determines which tables are in CHECK PENDING and execute the command on all of them.
SELECT * FROM syscat.tables WHERE OWNERTYPE = 'U' AND TYPE = 'T' AND STATUS = 'C' ORDER BY TABSCHEMA, TABLEID

This same type thing works for RUNSTATS and other commands, but for some reason not for SET INTEGRITY (or I am overlooking something).

Any idea why? Or better, anyone have a way to call SET INTEGRITY dynamically like this?

This is for DB2 UDB 9.7 on Windows by the way..

Thanks.

You don't need ADMIN_CMD to execute SET INTEGRITY, because the latter is an SQL statement, which you can execute dynamically from your SP. You do realize that you must SET INTEGRITY in certain sequence, accounting for table dependencies, don't you?

I guess I am unfamiliar then with how to execute dynamic SQL from a DB2 Stored Proc (native sql). If I have the stmt I want to execute in a variable of type varchar, how to I invoke it? Or am I missing something?

As to the second part, yes I do realize that. In my case I always create the parent tables before child tables in any scripts I execute, so ordering by TABLEID should accomplish keeping the order straight, although I can see that it is possible to hit error cases there. I haven't explored a more precise way to determine the dependency ordering.. If you know a better way, I'd love to hear it.

Thanks for the reply.

looks like I found my answer..
SET toexec = 'SET INTEGRITY FOR ' || tablename || ' IMMEDIATE CHECKED ';
PREPARE stmt FROM toexec;
EXECUTE stmt ;

You can write a recursive query to obtain the relationships or
you can run the set integrity on the tables in a loop till there are no tables in C state...

i prefer to adopt the latter option...

These approaches may not work in all cases, as frequently there are cyclical dependencies between tables. In such circumstances the only option is to specify all dependent tables in a single SET INTEGRITY statement, but the SQL to determine such tables is not trivial. Lenny might be interested in lending a hand, unless he did win his jackpot already...

I suppose you could loop through the results of:
SELECT * FROM syscat.tables WHERE OWNERTYPE = 'U' AND TYPE = 'T' AND STATUS = 'C' ORDER BY TABSCHEMA, TABLEID
or something similar to determine all CHECK PENDING objects, and build up a single statement that includes them all in a comma separated list. Then execute that..
Assuming the limits on the PREPARE and EXECUTE calls are not too small to hold a potentially large statement.
In my case I just have straight foreign key relationships to deal with, and no circular dependencies though.