Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: A Load that Deletes

    I am having a hard time finding information regarding the Load command and the critieria that it uses to know that it has to do a delete.

    I know that the load did do a delete, but I dont know why.

    Number of rows read = 292518
    Number of rows skipped = 0
    Number of rows loaded = 292518
    Number of rows rejected = 0
    Number of rows deleted = 77264
    Number of rows committed = 292518

    Can anyone point me to the correct documentation so that I can get a better understanding of what is going on?

    Regards
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    I found this online

    "INSERT and DELETE privilege on the table when the load utility is invoked in REPLACE mode"

    this is my code
    Code:
    load from $IXF_LOCATION of ixf replace into CENSTG.STG_RKEM_MOVE_TBL nonrecoverable
    From what I can see online it looks like i have a primary key violation on the table that is causing the deletes. Am I thinking correctly?
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Not necessarily a primary key - any unique constraint violation will cause LOAD to delete duplicate rows after the build phase.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    ...Just like an Index...which I have just deleted and am reloading now. I will let you know the results.
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You used the REPLACE option. This means that existing rows are deleted.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Other constraint violations also cause rows to be deleted. For example, if you have a column with "CHECK ( col IN ('A', 'B', 'C') )", then DB2 must delete the row if the value in such a column is 'D'. Otherwise, you would have inconsistencies in the database.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I would think that in the case of violated check or referential constraints, no deletes would happen during the load; instead, the table would be put into the check pending state upon the load completion, and it would be the administrator's task to deal with that.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Knut, IIRC, during the delete phase of a load command, only unique key violations are deleted.

    After load, SET INTEGRITY has to be run on a table that has check constraints and referential constraints. By specifying an exception table, the constraint violating rows are deleted from the main table and placed in the exception table. If no exception table is specified, the table continues in the check pending status.

    Please correct me if I am wrong.

    Thanks


    Sathyaram


    Quote Originally Posted by stolze
    Other constraint violations also cause rows to be deleted. For example, if you have a column with "CHECK ( col IN ('A', 'B', 'C') )", then DB2 must delete the row if the value in such a column is 'D'. Otherwise, you would have inconsistencies in the database.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You're right and I'm wrong (again). SET INTEGRITY has to be used to bring a table out of check pending state by verifying RI/check constrainsts. Unique constraints are handled during index rebuild in load already.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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