View previous topic :: View next topic
|
Author |
Message |
Joshua1066
New User
Joined: 25 Jul 2015 Posts: 1 Location: England
|
|
|
|
Hello - we have a DB2 system that comprises of about 100 DB2 tables in systems test. Many of the tables have RI associated with them. Overall it holds about 5000 rows so not very big. I have been asked to reload a new set of data. Before doing this I decided to backup the exiting 100 tables to QSAM files and then reload them using LOAD REPLACE. I expected no issues, but alas, it failed with both RI issues and duplicate key issues. To get around the RI issues I loaded with ENFORCE NO mainly because I didn't know the parent/child relationship and then took the tables out of check pending via check data. However, I still have lots of duplicate key issues. How can it be that if I did a simple copy/reload, that I have such issues with duplicate keys ? Is it possible that over the last few years, some of the tables have been altered from 'allow duplicates' to 'not allow duplicates' ? Its one of those tasks that I thought would be simple to suddenly becoming a headache. Any thoughts ? |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Joshua1066,
Welcome to the forums.
Quote: |
I have been asked to reload a new set of data |
Just curious, where do you get this new data from? You mentioned backup and reload. I assume you are not loading back the same data, are you? |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
May be that you unloaded the same data in same file 2 times? So, I would first make sure that I don't have duplicate data in input.
You may also want to check the existing data for any duplicates in concerned table(s).
. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Also, are you sure of doing LOAD REPLACE and not LOAD RESUME assuming you are reloading the same unloaded data and not the one from other sources, if its all new set of data then the data quality needs to be checked against RI? |
|
Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
|
|
|
|
Is it possible that you had a SHRLEVEL of CHANGE for the UNLOAD. So if the unload was done during update,delete,insert to the table, it might create duplicates or missing rows or incorrect values.
Quote: |
SHRLEVEL
Specifies whether other processes can access or update the table space or partitions while the data is being unloaded.
UNLOAD ignores the SHRLEVEL specification when the source object is an image copy data set.
The default value is SHRLEVEL CHANGE ISOLATION CS.
CHANGE
Specifies that rows can be read, inserted, updated, and deleted from the table space or partition while the data is being unloaded.
ISOLATION
Specifies the isolation level with SHRLEVEL CHANGE.
CS
Indicates that the UNLOAD utility is to read rows in cursor stability mode. With CS, the UNLOAD utility assumes CURRENTDATA(NO).
UR
Indicates that uncommitted rows, if they exist, are to be unloaded. The unload operation is performed with minimal interference from the other DB2 operations that are applied to the objects from which the data is being unloaded.
SKIP LOCKED DATA
Specifies that the UNLOAD utility is to skip rows on which incompatible locks are held by other transactions. This option applies to a row level or page level lock.
REFERENCE
Specifies that during the unload operation, rows of the tables can be read, but cannot be inserted, updated, nor deleted by other DB2 threads.
When you specify SHRLEVEL REFERENCE, the UNLOAD utility drains writers on the table space from which the data is to be unloaded. When data is unloaded from multiple partitions, the drain lock is obtained for all of the selected partitions in the UTILINIT phase. |
|
|
Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
|
|
|
|
Just another Thought...
I hope that you dont have Identity column defined on these table . If Identity column are part of these table I suggest to use IDENTITYOVERRIDE parameter with enforce no followed By check Data because I have seen in Shop using IDENTITY column as Primary key and later use it as Fkey in child tables. |
|
Back to top |
|
|
|