Professional Web Applications Themes

set integrity with temporary exception tables? - IBM DB2

hallo! UDB V7.1 and V8.1.3 windows i have a foreign key constraint between two tables. i do the following: 1. export parent table 2. load it on target 3. export child table 4. load it on target now the tables are in check pending state. now i use "set integrity for parent, child immediate checked" to bring it back from check pending state. in some cases (e.g. when a new parent row and a child row which references this new parent row are created between step 1 and 3) it is possible that foreign key constraints are violated and set ...

  1. #1

    Default set integrity with temporary exception tables?

    hallo!

    UDB V7.1 and V8.1.3 windows

    i have a foreign key constraint between two tables.

    i do the following:
    1. export parent table
    2. load it on target
    3. export child table
    4. load it on target

    now the tables are in check pending state.

    now i use "set integrity for parent, child immediate checked"
    to bring it back from check pending state.

    in some cases (e.g. when a new parent row and a child row which
    references this new parent row are created between step 1 and 3) it is
    possible that foreign key constraints are violated and set integrity
    returns with SQL3603N.

    i know, that it is possible to bring the tables back to normal state if
    i use
    "set integrity for parent, child immediate checked for exception in
    parent use parentexcp, in child use childexcp"

    my questions:
    is it possible to ignore the rows which violates the constraints without
    using exception tables?

    is there a other way without exception tables?

    why is it not possible to create the exception tables as temporary
    tables (declare global temporary table parentexcp like parent no
    logging)? when i try this i get an error SQL0204N "PARENTEXCP" from set
    integrity!

    thanks
    andreas

    alederer Guest

  2. #2

    Default Re: set integrity with temporary exception tables?

    Did you qualify the temp table with SESSION?
    I'm not aware of a limitation of SET INTEGRITY to use persistent exception
    tables only.
    The -204 supports the assumtion that the table was simply not found rather
    than not acceptable.

    Cheers
    Serge


    Serge Rielau Guest

  3. #3

    Default Re: set integrity with temporary exception tables?

    Serge Rielau wrote:
    > Did you qualify the temp table with SESSION?
    yes, see below...
    > I'm not aware of a limitation of SET INTEGRITY to use persistent exception
    > tables only.
    > The -204 supports the assumtion that the table was simply not found rather
    > than not acceptable.
    here a protokoll of what i do:

    the table s1p is the parent, son is the child table

    db2 load from e:\emptyload.s1p of ixf replace into s1p

    db2 declare global temporary table TMPS1P like S1P not logged
    db2 select count(*) from SESSION.TMPS1P

    1
    -----------
    0

    1 Satz/Sätze ausgewählt.

    db2 declare global temporary table TMPSON like SON not logged

    db2 select count(*) from SESSION.TMPSON

    1
    -----------
    0

    1 Satz/Sätze ausgewählt.

    db2 SET INTEGRITY FOR S1P,SON IMMEDIATE CHECKED FOR EXCEPTION IN S1P USE
    SESSION.TMPS1P, IN SON USE SESSION.TMPSON

    DB21034E Der Befehl wurde als SQL-Anweisung verarbeitet, da es sich um
    keinen
    g³ltigen Befehl des Befehlszeilenprozessors handelte. Wõhrend der
    SQL-Verarbeitung wurde Folgendes ausgegeben:
    SQL0204N "TMPS1P" ist ein nicht definierter Name. SQLSTATE=42704

    so it seems that it is not possible to use temporary tables as exception
    tables!?

    thanks
    andreas

    alederer Guest

  4. #4

    Default Re: set integrity with temporary exception tables?

    IMHO that's a bug. I passed thsi post on to the affected developers, but if
    you want a fix you'll need to call support.

    Cheers
    Serge


    Serge Rielau Guest

Similar Threads

  1. Temporary tables and disk activity
    By Phil Endecott in forum PostgreSQL / PGSQL
    Replies: 11
    Last Post: December 13th, 10:24 PM
  2. MySql temporary tables
    By Andu in forum PHP Development
    Replies: 1
    Last Post: September 16th, 03:31 AM
  3. Temporary Tables
    By Phil Jackson in forum IBM DB2
    Replies: 2
    Last Post: August 22nd, 05:05 AM
  4. global temporary tables
    By Blair Adamache in forum IBM DB2
    Replies: 0
    Last Post: July 31st, 07:44 PM
  5. Temporary tables visible to all sessions
    By Konstantin Kudin in forum Oracle Server
    Replies: 3
    Last Post: July 10th, 11:28 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139