Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 - loading data

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Joshua1066

New User


Joined: 25 Jul 2015
Posts: 1
Location: England

PostPosted: Sun Nov 18, 2018 4:58 am    Post subject: DB2 - loading data
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

Moderator


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

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

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

Active User


Joined: 20 Dec 2014
Posts: 447
Location: USA

PostPosted: Mon Nov 19, 2018 8:13 pm    Post subject: Reply to: DB2 - loading data
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

Senior Member


Joined: 21 Sep 2010
Posts: 2064
Location: NY,USA

PostPosted: Mon Nov 19, 2018 8:34 pm    Post subject:
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
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1621
Location: Oregon

PostPosted: Wed Nov 21, 2018 12:20 am    Post subject:
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.

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

Moderator


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

PostPosted: Thu Nov 22, 2018 9:54 am    Post subject: Reply to: DB2 - loading data
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts To get the count of PDS data sets whi... Vignesh Sid JCL & VSAM 4 Fri Nov 30, 2018 8:38 pm
This topic is locked: you cannot edit posts or make replies. DFSORT output in CSV for data spans i... hiravibk DFSORT/ICETOOL 3 Tue Nov 13, 2018 7:55 pm
No new posts REFORMATTING WITH JFY CLOBBERING OUTP... speermb DFSORT/ICETOOL 12 Thu Nov 08, 2018 7:20 pm
No new posts Migrating AS400 to HIVE; Data type ma... sindhu.jose All Other Mainframe Topics 1 Tue Nov 06, 2018 1:06 pm
No new posts Merge data from record pairs ashokakash DFSORT/ICETOOL 5 Wed Oct 17, 2018 11:38 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us