IBM Mainframe Forum Index
Log In
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register

DB2 - loading data

IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message

New User

Joined: 25 Jul 2015
Posts: 1
Location: England

PostPosted: Sun Nov 18, 2018 4:58 am
Reply with quote

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
View user's profile Send private message
Arun Raj


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Mon Nov 19, 2018 7:56 pm
Reply with quote


Welcome to the forums.

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
View user's profile Send private message

Active User

Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Mon Nov 19, 2018 8:13 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2576
Location: NYC,USA

PostPosted: Mon Nov 19, 2018 8:34 pm
Reply with quote

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
View user's profile Send private message

Global Moderator

Joined: 28 Aug 2007
Posts: 1689
Location: Tiruppur, India

PostPosted: Wed Nov 21, 2018 12:20 am
Reply with quote

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.

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.


Specifies that rows can be read, inserted, updated, and deleted from the table space or partition while the data is being unloaded.
Specifies the isolation level with SHRLEVEL CHANGE.
Indicates that the UNLOAD utility is to read rows in cursor stability mode. With CS, the UNLOAD utility assumes CURRENTDATA(NO).
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.
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.
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
View user's profile Send private message


Joined: 14 Oct 2005
Posts: 1206
Location: Bangalore,India

PostPosted: Thu Nov 22, 2018 9:54 am
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2


Similar Topics
Topic Forum Replies
No new posts compare two file to find position/dat... SYNCSORT 2
No new posts Secondary index error while loading d... IMS DB/DC 2
No new posts SKIP LOCKED DATA in UPDATE statement DB2 9
No new posts How to find which file contains my se... DFSORT/ICETOOL 6
No new posts Changing Data Type SYNCSORT 4
Search our Forums:

Back to Top