|
View previous topic :: View next topic
|
| Author |
Message |
tiger_wen
New User
Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China
|
|
|
|
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 |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Records with same keyx but where? |
|
| Back to top |
|
|
tiger_wen
New User
Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China
|
|
|
|
| Pandora-Box wrote: |
| Records with same keyx but where? |
In SYSREC dsn the second step(UNLOAD from COPY) |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Is KEYX defined as unique? |
|
| Back to top |
|
|
tiger_wen
New User
Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China
|
|
|
|
| Pandora-Box wrote: |
| Is KEYX defined as unique? |
unique index created on column KEYX as my first post shown. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
tiger_wen
New User
Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China
|
|
|
|
| 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
tiger_wen
New User
Joined: 24 Jun 2005
Posts: 22
Location: BeiJing,China
|
|
|
|
| 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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
|
|
