Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

duplicated when unload ts from full image copy ds

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

New User


Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China

PostPosted: Tue May 21, 2013 7:13 am    Post subject: duplicated when unload ts from full image copy ds
Reply with quote

duplicated when unload ts from full image copy with shrlevel reference option.

We are using DB2 v9,and recently we are encountering a kind of issue about the COPY/UNLOAD utility.

Backgroup:
We have a table TABLEX with column KEYX and DATAX), and we also create an unique index
on column KEYX.

Operations:
1. full image copy:
// EXEC DSNUPROC,PARM='DSNB,XXXX'
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
TEMPLATE COPYDASD DSN …
COPY TABLESPACE DBX.TSX COPYDDN(COPYDASD) FULL YES SHRLEVEL CHANGE
/
2.UNLOAD from copy
//UNLD EXEC DSNUPROC
//SYSREC DD DSN=…  we unload the records required here!
//SYSPRINT DD DSN=…
//SYSPUNCH DD DSN=…
//IMAGCOPY DD DSN=…
//SYSIN DD *
UNLOAD TABLESPACE DBX.TSX FROMCOPYDDN IMAGCOPY FROM TABLE SHX.TABLEX HEADER NONE WHERE …

Issues:
After step 1 and step 2 done, we found that the are records with the same KEYX value exists in SYSREC.

Basic analysis:
According to the utility guide and reference(SC18-9855-13):
When you specify SHRLEVEL CHANGE during COPY utility, uncommitted data might be copied.

Is the uncommitted data cause this issue?
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue May 21, 2013 7:57 am    Post subject:
Reply with quote

Records with same keyx but where?
Back to top
View user's profile Send private message
tiger_wen

New User


Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China

PostPosted: Tue May 21, 2013 8:06 am    Post subject:
Reply with quote

Pandora-Box wrote:
Records with same keyx but where?


In SYSREC dsn the second step(UNLOAD from COPY)
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue May 21, 2013 8:14 am    Post subject:
Reply with quote

Is KEYX defined as unique?
Back to top
View user's profile Send private message
tiger_wen

New User


Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China

PostPosted: Tue May 21, 2013 8:19 am    Post subject:
Reply with quote

Pandora-Box wrote:
Is KEYX defined as unique?


unique index created on column KEYX as my first post shown.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue May 21, 2013 11:49 am    Post subject:
Reply with quote

SHARELEVEL CHANGE allows the uncommited data to be copied as happened in your case

You might need to check on the advice below

DB2 Manual wrote:

SHRLEVEL
Indicates whether other programs can access or update the table space or index while COPY is running.
REFERENCE
Allows read-only access by other programs.
CHANGE
Allows other programs to change the table space or index space.
When you specify SHRLEVEL CHANGE, uncommitted data might be copied.

When SHRLEVEL CHANGE with FLASHCOPY CONSISTENT is specified, the COPY utility uses DB2 shadow processing to backout uncommitted work to make the FlashCopy image copy consistent without any availability outage to applications. Application updates are allowed throughout the creation of the FlashCopy image copy and the creation of the sequential image copies.

Recommendation: Except when creating FlashCopy image copies or traditional image copies with SHRLEVEL CHANGE and FLASHCOPY CONSISTENT specified, do not use image copies that are made with SHRLEVEL CHANGE when you run RECOVER TOCOPY.



Also is there a way through to restructure the schedule of execution of the full image copy jobs and also there by you could change the SHRLEVEL
Back to top
View user's profile Send private message
tiger_wen

New User


Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China

PostPosted: Wed May 22, 2013 5:26 am    Post subject:
Reply with quote

Pandora-Box wrote:
SHARELEVEL CHANGE allows the uncommited data to be copied as happened in your case

You might need to check on the advice below

DB2 Manual wrote:

SHRLEVEL
Indicates whether other programs can access or update the table space or index while COPY is running.
REFERENCE
Allows read-only access by other programs.
CHANGE
Allows other programs to change the table space or index space.
When you specify SHRLEVEL CHANGE, uncommitted data might be copied.

When SHRLEVEL CHANGE with FLASHCOPY CONSISTENT is specified, the COPY utility uses DB2 shadow processing to backout uncommitted work to make the FlashCopy image copy consistent without any availability outage to applications. Application updates are allowed throughout the creation of the FlashCopy image copy and the creation of the sequential image copies.

Recommendation: Except when creating FlashCopy image copies or traditional image copies with SHRLEVEL CHANGE and FLASHCOPY CONSISTENT specified, do not use image copies that are made with SHRLEVEL CHANGE when you run RECOVER TOCOPY.



Also is there a way through to restructure the schedule of execution of the full image copy jobs and also there by you could change the SHRLEVEL


Thanks for your time on this,I think I had already posted the related information in my first thread.Thanks.

What I want to know is:
Did the uncommitted data cause my issue.

Thanks again for your help:)
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Wed May 22, 2013 6:12 am    Post subject: Reply to: duplicated when unload ts from full image copy ds
Reply with quote

Well, you're sitting there with the data. Pull all the duplicate KEYXs out (ICETOOL SELECT should get you there). Find out what the duplication is. If you can relate the duplicates to uncommitted data, then your answer is Yes.
Back to top
View user's profile Send private message
tiger_wen

New User


Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China

PostPosted: Wed May 22, 2013 7:16 am    Post subject: Re: Reply to: duplicated when unload ts from full image copy
Reply with quote

Bill Woodger wrote:
Well, you're sitting there with the data. Pull all the duplicate KEYXs out (ICETOOL SELECT should get you there). Find out what the duplication is. If you can relate the duplicates to uncommitted data, then your answer is Yes.


I just tried,the problem can not be produced.

The utility guide and reference said:
When you specify SHRLEVEL CHANGE, uncommitted data might be copied.

Anyone can tell under what sort of condition will the uncommitted data be copied?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed May 22, 2013 9:43 am    Post subject:
Reply with quote

Using SHRLEVEL CHANGE means you are allowing some process to modify the table when the image copy is executing so it is more likely that uncommited data gets copied to your copy dataset

What you could do is schedule your copy job such a way that it doesnt execute parallel to the processes which modify this table
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed May 22, 2013 7:24 pm    Post subject:
Reply with quote

Hi All,

what does this recommendation mean?



Quote:

Recommendation: Except when creating FlashCopy image copies or traditional image copies with SHRLEVEL CHANGE and FLASHCOPY CONSISTENT specified, do not use image copies that are made with SHRLEVEL CHANGE when you run RECOVER TOCOPY.


So if we are taking image copies using SHRLEVEL CHANGE, that what kind of recovery is suggested? what would happen if we run RECOVER TOCOPY, will it not handle any uncommitted records captured in the Image copy with SHRLEVEL CHANGE?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed May 22, 2013 9:00 pm    Post subject:
Reply with quote

Hello,

It is a rather dangerous practice to backup data while it is being updated.

IBM has a phrase that convers this and many other "things". . .
"The result may be unpredictable"

I don't know your organization, but places i support want NO doubt about backuip content.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu May 23, 2013 12:12 pm    Post subject:
Reply with quote

Hi tiger_wen,

You unloaded data might have different values for 1/more columns than what's in the table, since you have unloaded from a fuzzy copy. But, it will not contain duplicates as it has unique index.

Are you deleting the unload dataset before running the unload, if not you might have appended data running unload multiple times.

Thanks,
Sushanth
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 syncsort: copy lines after the keyword shreya19 SYNCSORT 3 Fri Dec 02, 2016 9:47 am
No new posts Single COPY CICS TS datasets and when... Kyle Carroll CICS 2 Tue Oct 11, 2016 9:47 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us