DB2 - L

  • 1.  CHECK DATA

    Posted Oct 19, 2021 06:17 PM
    I'm trying to clear a CHKP flag on a dependent table with ON DELETE RESTRICT ENFORCED rule.  Table is not a parent; it is only a dependent table.  There are orphaned rows in the dependent table that I would like to move to an exception table.  I get DSNUKINE - INVALID EXCEPTION TABLE D8490CTY.EPPC_ADDRESS FOR D8490CTY.PPC_ADDRESS.   I would like to avoid START...ACCESS(FORCE) command to clear the flag and without coding NOCHECKPEND on the LOAD statement.  

    I don't have an issue when the rule is ON DELETE CASCADE ENFORCED.  What am I missing on the exception table.

    The exception table was created as follows:

    CREATE TABLE dbname.exception_tablename
    LIKE dbname.dependent_tablename
    IN dbname.tablespace;
    COMMIT;
    --
    ALTER TABLE dbname..exception_tablename
    ADD RID CHAR(5);                                                    -- tried CHAR(7), too
    ALTER TABLE dbname..exception_tablename
    ADD TSTAMP TIMESTAMP;
    --

    Control statement for the CHECK UTILITY is:

    CHECK DATA TABLESPACE dbname.tablespace
    SCOPE ALL                                                                   -- tried PENDING, and without SCOPE parameter
    FOR EXCEPTION IN dbname.dependent_tablename
    USE dbname..exception_tablename
    DELETE YES

    ------------------------------
    KarenLachanceTravelers
    ------------------------------


  • 2.  RE: CHECK DATA

    Posted Oct 20, 2021 02:52 AM
    A few quick thoughts: Outstanding DDL on base table? Any LOB columns? Did the exception table ALTERs get actioned so it is not in *REOR ?? Not sure if that last one can cause grief but you never know...

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 3.  RE: CHECK DATA

    Posted Oct 20, 2021 07:17 AM
    I have seen this error before if the base table has an identity column in it. You can get around it by creating the exception table as follows, with the INCLUDING IDENTITY clause:

    CREATE TABLE dbname.exception_tablename
    LIKE dbname.dependent_tablename
    IN dbname
    INCLUDING IDENTITY;

    If no identity column applies, then seeing the base table DDL would help.

    ------------------------------
    ChrisLoudenSocial Security Administration
    ------------------------------



  • 4.  RE: CHECK DATA

    Posted Oct 20, 2021 09:03 AM
    Thanks Chris.  There IS an IDENTITY column in the base table.  The INCLUDING IDENTITY clause failed with DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD INCLUDING. TOKEN <END-OF-STATEMENT> IN EDITPROC VALIDPROC AUDIT OBID DATA WITH WAS
    EXPECTED.

    I altered the exception table to match the IDENTITY column attributes of the base table and the CHECK worked!  Thank you very much.

    ------------------------------
    KarenLachanceTravelers
    ------------------------------



  • 5.  RE: CHECK DATA

    Posted Oct 20, 2021 09:17 AM
    Glad to hear that worked. I checked the exception table creation DDL I had with this particular problem and I think the INCLUDING IDENTITY clause has to come right after the LIKE clause. Doing that may correct the -199 you were getting:

    CREATE TABLE dbname.exception_tablename
    LIKE dbname.dependent_tablename
    INCLUDING IDENTITY

    IN DATABASE dbname;

    ------------------------------
    ChrisLoudenSocial Security Administration
    ------------------------------