A partitioned table is unavailable for select after DSN1COPY

Eugene Zeev

A partitioned table is unavailable for select after DSN1COPY

Hi community members.
With DB2 V11 DSN1COPY utility I copied a table with some hundreds partitions from production to a development environment. After that REPAIR utility detects versions difference. It ends with RC=8 and message DSNU694I REPAIR CATALOG CANNOT CONTINUE BECAUSE NOT ENOUGH INFORMATION IS AVAILABLE. The table is available for RUNSTATS and REORG utilities, but unavailable for SELECT. Is it possible to fix the problem without any action on source (production)?

Philip Sevetson

A partitioned table is unavailable for select after DSN1COPY
(in response to Eugene Zeev)
Eugene,

Tl:dr; I think you have to do the REORG to clean up your internal markers.

***

It sounds – and I admit I’m guessing here – like the version information in the copied data doesn’t have any match to the catalog and DBD information for the tablespace. If you can do a REORG, you should do that – if DB2 will agree to unload the data to a sequential SYSREC during the REORG, that will remove all DBD information associated with the data and the RELOAD step will set the current version of the test tablespace when putting the data in pages.

For the record, I’ve never _not_ done a REORG on a tablespace after populating it with DSN1COPY… except for one company where they quite painstakingly created all prod and test objects in _exactly the same order for all objects_, which led to everything being assigned the same DBID, OBID, and version information. In that circumstance, and that only, DSN1COPY will not cause internal marker problems in the target tablespace.

--Phil Sevetson

From: Eugene Zeev [mailto:[login to unmask email]
Sent: Monday, April 09, 2018 4:57 AM
To: [login to unmask email]
Subject: [DB2-L] - A partitioned table is unavailable for select after DSN1COPY


Hi community members.
With DB2 V11 DSN1COPY utility I copied a table with some hundreds partitions from production to a development environment. After that REPAIR utility detects versions difference. It ends with RC=8 and message DSNU694I REPAIR CATALOG CANNOT CONTINUE BECAUSE NOT ENOUGH INFORMATION IS AVAILABLE. The table is available for RUNSTATS and REORG utilities, but unavailable for SELECT. Is it possible to fix the problem without any action on source (production)?

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: A partitioned table is unavailable for select after DSN1COPY
(in response to Eugene Zeev)

In Reply to Eugene Zeev:

Hi community members.
With DB2 V11 DSN1COPY utility I copied a table with some hundreds partitions from production to a development environment. After that REPAIR utility detects versions difference. It ends with RC=8 and message DSNU694I REPAIR CATALOG CANNOT CONTINUE BECAUSE NOT ENOUGH INFORMATION IS AVAILABLE. The table is available for RUNSTATS and REORG utilities, but unavailable for SELECT. Is it possible to fix the problem without any action on source (production)?

Eugene,
 
You don't mention if you used the Objectid translate for DBID, PSID, OBID. I have never tried this myself and don't have experience with REPAIR, so I don't know what info it needs. I have typically used Unload and Load to logically copy data from one subsystem to another. This can avoid the need for an outage from putting the tables into Read Only and Quiesced to externalise all changes from Bufferpool. Does not require the understanding of internals so much too (what needs to be repaired and info needed).

I could imagine problems if the source table had rows with multiple versions, so that some rows did not match target table definition.

Maybe REORG would work as Phil suggested (no idea), maybe not due to version info missing. REORG is a bit like an Unload (Format Internal) and Load, so saves very little. I think logical transfer of the data circumvents many issues.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd 

Srinivas Adupa

RE: A partitioned table is unavailable for select after DSN1COPY
(in response to Michael Hannan)

Hey Eugene,

Is it possible to post the full DDL of Db2 objects to the forum, preferably joblog also. 

This would help us dig into the problem statement clearly. 

A question: Are you copying all the partitions of source tablespace? or only some partitions? 

If all partitions, then its okay. But if its the some partitions, then you might be missing some other steps which need to be done. I am suspecting that, you are trying to copy limited partitions from source tablespace by creating the target tablespace with only those limited partitions. This is wrong as per DSN1COPY. 

Hope this helps. 

Best Regards /Srinivas Adupa

Philip Sevetson

A partitioned table is unavailable for select after DSN1COPY
(in response to Michael Hannan)
Also, what Michael said. My mind is clearly going. I completely forgot about OBIDXLAT, and yes, that usually clears up DSN1COPY issues if there isn’t an incompatible table or tablespace property involved.

--Phil ( :: goes off muttering to himself about losing his mind with age :: )


From: Michael Hannan [mailto:[login to unmask email]
Sent: Monday, April 09, 2018 11:28 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: A partitioned table is unavailable for select after DSN1COPY


In Reply to Eugene Zeev:

Hi community members.
With DB2 V11 DSN1COPY utility I copied a table with some hundreds partitions from production to a development environment. After that REPAIR utility detects versions difference. It ends with RC=8 and message DSNU694I REPAIR CATALOG CANNOT CONTINUE BECAUSE NOT ENOUGH INFORMATION IS AVAILABLE. The table is available for RUNSTATS and REORG utilities, but unavailable for SELECT. Is it possible to fix the problem without any action on source (production)?

Eugene,

You don't mention if you used the Objectid translate for DBID, PSID, OBID. I have never tried this myself and don't have experience with REPAIR, so I don't know what info it needs. I have typically used Unload and Load to logically copy data from one subsystem to another. This can avoid the need for an outage from putting the tables into Read Only and Quiesced to externalise all changes from Bufferpool. Does not require the understanding of internals so much too (what needs to be repaired and info needed).

I could imagine problems if the source table had rows with multiple versions, so that some rows did not match target table definition.

Maybe REORG would work as Phil suggested (no idea), maybe not due to version info missing. REORG is a bit like an Unload (Format Internal) and Load, so saves very little. I think logical transfer of the data circumvents many issues.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Myron Miller

A partitioned table is unavailable for select after DSN1COPY
(in response to Michael Hannan)
I haven't done hundreds of partitions, the most i've done is about 5-10 partitions. But we do it both intersystem and intrasystem, literally hundreds of tables with OBID, DBID, PSID specified. (about 1000+ tables daily and weekly and another 1000+ on request). UNLOAD/RELOAD is fine but when you've got literally thousands of tables to do and a minimal timeframe to get them done, unload/reload won't work. It's just way way way too slow. We have only a 20-25 minute window to get this done and UNLOAD/RELOAD can't finish in that window. Usually takes several hours to accomplish it with UNLOAD/RELOAD whereas DSN1COPY works in minutes. Sometimes errors with it, but not enough to be unmanageable. Try doing over 1000 tables with sizes from 500k rows to 100 million rows in a short time. We break it up into about 6 chunks, each of about 150+ tables and the largest time one is about 25 minutes. Most are less than 15 minutes.


That said, it's not unusual to get various types of errors, the most common is version differences between source and target. We also get the situation where DB2 gets confused or the data is confused. We have TIMESTAMP specified as 6 length in the catalog but it thinks the length is 0 in the data. When this happens, i have to run a reorg on the target. Runstats does not necessarily work, generally it is one way that i tell that something didn't work on the DSN1COPY when runstats fails.


My test scenario when it fails and i get one of my common failure codes is first REPAIR, then reorg. Once in a while, i need to reorg the source and then manually copy the data again to get things reset correctly. But that is rare.


But remember, the tables must be identical in structure. THAT IS ABSOLUTELY CRITICAL. If not, that's a guaranteed 00C90101 eventually. Then version and timestamp default settings are the common causes of problems with DSN1COPY. Repair usually fixes the version issues but timestamp defaults are almost always fixed by reorg. Once in a great while, the source must be reorg;d if versions can't be totally resolved, then recopy can be done.


We average way less than 1 error per week on these over 4 systems with each set over 1000+ tables being copied. And this is both intersystem and intrasystem copies. So considering the time saved, it's worth it doing the DSN1COPY process.


Thanks Myron W. Miller


________________________________
From: Michael Hannan <[login to unmask email]>
Sent: Monday, April 9, 2018 11:27 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: A partitioned table is unavailable for select after DSN1COPY


In Reply to Eugene Zeev:

Hi community members.
With DB2 V11 DSN1COPY utility I copied a table with some hundreds partitions from production to a development environment. After that REPAIR utility detects versions difference. It ends with RC=8 and message DSNU694I REPAIR CATALOG CANNOT CONTINUE BECAUSE NOT ENOUGH INFORMATION IS AVAILABLE. The table is available for RUNSTATS and REORG utilities, but unavailable for SELECT. Is it possible to fix the problem without any action on source (production)?

Eugene,

You don't mention if you used the Objectid translate for DBID, PSID, OBID. I have never tried this myself and don't have experience with REPAIR, so I don't know what info it needs. I have typically used Unload and Load to logically copy data from one subsystem to another. This can avoid the need for an outage from putting the tables into Read Only and Quiesced to externalise all changes from Bufferpool. Does not require the understanding of internals so much too (what needs to be repaired and info needed).

I could imagine problems if the source table had rows with multiple versions, so that some rows did not match target table definition.

Maybe REORG would work as Phil suggested (no idea), maybe not due to version info missing. REORG is a bit like an Unload (Format Internal) and Load, so saves very little. I think logical transfer of the data circumvents many issues.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

Eugene Zeev

RE: A partitioned table is unavailable for select after DSN1COPY
(in response to Myron Miller)

Hi community members.
Thanks for all replies. I will answer on some questions. Running reorg for a table with hundreds partitions is very resource consuming, we try to avoid it. The target is a new environment, so all definitions are equal to source (production) and all versions are 0. For all objects DBID, PSID, OBID translation was done correctly. It is problematic to post DDL by security reasons. I copied from backup all partitions with their partitioned indexes. The backup was done with share level reference. In production some partitions are in AREO status.
This workaround was found. The table was created in the target without some last columns. After that ALTER ADD COLUMN commands were performed. At the result the version number of the target table is equal to the source. All partitions without AREO status were copied by DSN1COPY. It is enough for this new environment. Now it is possible to perform SELECT for the target without REPAIR. However REPAIR with TEST option ends not OK. Hope after upgrading to V12 we will not have such problems.
Again many thanks for help.

Phil Grainger

A partitioned table is unavailable for select after DSN1COPY
(in response to Myron Miller)
AND don't forget to correctly specify the NUMBER of partitions to dsn1copy - otherwise it gets might confused about which bits of a RID are page numbers and which are partition numbers

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Myron Miller [mailto:[login to unmask email]
Sent: 10 April 2018 15:05
To: [login to unmask email]
Subject: [DB2-L] - RE: A partitioned table is unavailable for select after DSN1COPY


I haven't done hundreds of partitions, the most i've done is about 5-10 partitions. But we do it both intersystem and intrasystem, literally hundreds of tables with OBID, DBID, PSID specified. (about 1000+ tables daily and weekly and another 1000+ on request). UNLOAD/RELOAD is fine but when you've got literally thousands of tables to do and a minimal timeframe to get them done, unload/reload won't work. It's just way way way too slow. We have only a 20-25 minute window to get this done and UNLOAD/RELOAD can't finish in that window. Usually takes several hours to accomplish it with UNLOAD/RELOAD whereas DSN1COPY works in minutes. Sometimes errors with it, but not enough to be unmanageable. Try doing over 1000 tables with sizes from 500k rows to 100 million rows in a short time. We break it up into about 6 chunks, each of about 150+ tables and the largest time one is about 25 minutes. Most are less than 15 minutes.



That said, it's not unusual to get various types of errors, the most common is version differences between source and target. We also get the situation where DB2 gets confused or the data is confused. We have TIMESTAMP specified as 6 length in the catalog but it thinks the length is 0 in the data. When this happens, i have to run a reorg on the target. Runstats does not necessarily work, generally it is one way that i tell that something didn't work on the DSN1COPY when runstats fails.



My test scenario when it fails and i get one of my common failure codes is first REPAIR, then reorg. Once in a while, i need to reorg the source and then manually copy the data again to get things reset correctly. But that is rare.



But remember, the tables must be identical in structure. THAT IS ABSOLUTELY CRITICAL. If not, that's a guaranteed 00C90101 eventually. Then version and timestamp default settings are the common causes of problems with DSN1COPY. Repair usually fixes the version issues but timestamp defaults are almost always fixed by reorg. Once in a great while, the source must be reorg;d if versions can't be totally resolved, then recopy can be done.



We average way less than 1 error per week on these over 4 systems with each set over 1000+ tables being copied. And this is both intersystem and intrasystem copies. So considering the time saved, it's worth it doing the DSN1COPY process.


Thanks Myron W. Miller

________________________________
From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, April 9, 2018 11:27 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: A partitioned table is unavailable for select after DSN1COPY


In Reply to Eugene Zeev:

Hi community members.
With DB2 V11 DSN1COPY utility I copied a table with some hundreds partitions from production to a development environment. After that REPAIR utility detects versions difference. It ends with RC=8 and message DSNU694I REPAIR CATALOG CANNOT CONTINUE BECAUSE NOT ENOUGH INFORMATION IS AVAILABLE. The table is available for RUNSTATS and REORG utilities, but unavailable for SELECT. Is it possible to fix the problem without any action on source (production)?

Eugene,

You don't mention if you used the Objectid translate for DBID, PSID, OBID. I have never tried this myself and don't have experience with REPAIR, so I don't know what info it needs. I have typically used Unload and Load to logically copy data from one subsystem to another. This can avoid the need for an outage from putting the tables into Read Only and Quiesced to externalise all changes from Bufferpool. Does not require the understanding of internals so much too (what needs to be repaired and info needed).

I could imagine problems if the source table had rows with multiple versions, so that some rows did not match target table definition.

Maybe REORG would work as Phil suggested (no idea), maybe not due to version info missing. REORG is a bit like an Unload (Format Internal) and Load, so saves very little. I think logical transfer of the data circumvents many issues.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)

Michael Hannan

RE: A partitioned table is unavailable for select after DSN1COPY
(in response to Myron Miller)

In Reply to Myron Miller:

I haven't done hundreds of partitions, the most i've done is about 5-10 partitions. But we do it both intersystem and intrasystem, literally hundreds of tables with OBID, DBID, PSID specified. (about 1000+ tables daily and weekly and another 1000+ on request). 

Just curious, if you DSN1COPY just 5 to 10 partitions of a table, what do you do with the indexes? If index is not partitioned, that seems more tricky. REBUILD would defeat the time savings?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Srinivas Adupa

A partitioned table is unavailable for select after DSN1COPY
(in response to Michael Hannan)
Obviously YES.
REBUILD INDEX should be performed as the index is NPI. Savings because of DSN1COPY might be trimmed because of REBUILD.

Best Regards /Srini

From: Michael Hannan [mailto:[login to unmask email]
Sent: Wednesday, April 11, 2018 9:05 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: A partitioned table is unavailable for select after DSN1COPY

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

In Reply to Myron Miller:
I haven't done hundreds of partitions, the most i've done is about 5-10 partitions. But we do it both intersystem and intrasystem, literally hundreds of tables with OBID, DBID, PSID specified. (about 1000+ tables daily and weekly and another 1000+ on request).

Just curious, if you DSN1COPY just 5 to 10 partitions of a table, what do you do with the indexes? If index is not partitioned, that seems more tricky. REBUILD would defeat the time savings?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

Myron Miller

A partitioned table is unavailable for select after DSN1COPY
(in response to Michael Hannan)
we copy the indexes as well. one job copies the data and another copies the indexes. Rarely have to do rebuild index.


Thanks Myron W. Miller


________________________________
From: Michael Hannan <[login to unmask email]>
Sent: Tuesday, April 10, 2018 11:34 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: A partitioned table is unavailable for select after DSN1COPY


In Reply to Myron Miller:

I haven't done hundreds of partitions, the most i've done is about 5-10 partitions. But we do it both intersystem and intrasystem, literally hundreds of tables with OBID, DBID, PSID specified. (about 1000+ tables daily and weekly and another 1000+ on request).

Just curious, if you DSN1COPY just 5 to 10 partitions of a table, what do you do with the indexes? If index is not partitioned, that seems more tricky. REBUILD would defeat the time savings?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

Srinivas Adupa

A partitioned table is unavailable for select after DSN1COPY
(in response to Myron Miller)
Hey Miller,
In Eugene's case, they are not copying all the partitions of TS (but some). And they've some NPI's too.
You can do DSN1COPY on indexes also only when you are copying all the partitions (ultimately whole data of TS) - but not partial parts. Only solution that I would think is to do REBUILD in this case.

Best Regards /Srini

From: Myron Miller [mailto:[login to unmask email]
Sent: Wednesday, April 11, 2018 6:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: A partitioned table is unavailable for select after DSN1COPY

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

we copy the indexes as well. one job copies the data and another copies the indexes. Rarely have to do rebuild index.


Thanks Myron W. Miller

________________________________
From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tuesday, April 10, 2018 11:34 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: A partitioned table is unavailable for select after DSN1COPY


In Reply to Myron Miller:
I haven't done hundreds of partitions, the most i've done is about 5-10 partitions. But we do it both intersystem and intrasystem, literally hundreds of tables with OBID, DBID, PSID specified. (about 1000+ tables daily and weekly and another 1000+ on request).

Just curious, if you DSN1COPY just 5 to 10 partitions of a table, what do you do with the indexes? If index is not partitioned, that seems more tricky. REBUILD would defeat the time savings?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

Srinivas Adupa

A partitioned table is unavailable for select after DSN1COPY
(in response to Myron Miller)
Then you are already in safer hands. REBUILD is definitely required mainly
when copying partial data. Your current situation comes under that. Because
of NPIs.

On Wed, 11 Apr 2018, 6:00 pm Myron Miller, <[login to unmask email]> wrote:

> we copy the indexes as well. one job copies the data and another copies
> the indexes. Rarely have to do rebuild index.
>
>
> Thanks Myron W. Miller
>
>
> ------------------------------
> *From:* Michael Hannan <[login to unmask email]>
> *Sent:* Tuesday, April 10, 2018 11:34 PM
>
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: A partitioned table is unavailable for select
> after DSN1COPY
>
>
> In Reply to Myron Miller:
>
> I haven't done hundreds of partitions, the most i've done is about 5-10
> partitions. But we do it both intersystem and intrasystem, literally
> hundreds of tables with OBID, DBID, PSID specified. (about 1000+ tables
> daily and weekly and another 1000+ on request).
>
> Just curious, if you DSN1COPY just 5 to 10 partitions of a table, what do
> you do with the indexes? If index is not partitioned, that seems more
> tricky. REBUILD would defeat the time savings?
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
> -----End Original Message-----
>

Eugene Zeev

RE: A partitioned table is unavailable for select after DSN1COPY
(in response to Phil Grainger)

Phil,
Thanks for pointing out to the importance of parameter NUMPATS. Should it be used in case of copying DPSI?