Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: need way to automatically SET INTEGRITY on all tables

    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.
    Last edited by craigmc; 02-22-10 at 15:12.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    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.

  4. #4
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    looks like I found my answer..
    SET toexec = 'SET INTEGRITY FOR ' || tablename || ' IMMEDIATE CHECKED ';
    PREPARE stmt FROM toexec;
    EXECUTE stmt ;

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sathyaram_s View Post
    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...
    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...
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •