Select from table with different magic numbers.

Paul A Redhead

Select from table with different magic numbers.
Folks,
I just thought I'd communicate a couple of things that recently changed my
understanding of a couple of DB2 concepts. It may be that I'm the only one
surprised by them, and if so then I hope I can get a couple of explanatory
comments.

Situation was that there were 2 'temporary' development environments, say DEV1
and DEV2. The DEV1 environment had been completely backed up using a DFDSS DUMP
to tape at some earlier stage. I was then requested to retrieve the data from
this for 6 tables and use that data to populate those tables in DEV2. I know
there are lots of ways to skin a cat, however I thought I'd back up the DEV1
tables, do a DFDSS RESTORE of those 6 tables from the dump back over the DEV1
tablespaces/indexes, do a REPAIR LEVELID, use SQL to move the data to DEV2 (only
small tables, all < 50 rows), then put the DEV1 data back. I did the DFDSS
RESTORE of the 6 tablespaces/indexes (yes, STOP DB to externalize pages first
etc...) and the return code from the RESTORE was 0. I REPAIRed them, got a 0. I
then did a SELECT * from the table using QMF and got no rows returned.
Everything appeared fine, just a normal empty table, no negative return code.
The user was adamant that there should have been data there. I did a DSN1PRNT of
page 2, and lo and behold there were in fact rows there...nicely formatted,
everything looked OK. It was then I found out that the table had been dropped
and recreated (SAME definitions) since the DUMP had been taken. DSN1PRNT of page
0 and page 2 revealed the magic numbers (DBID, PSID, OBID) were different to
those in the catalog.

Question 1. I would have expected a negative return code from the SQL if the
numbers were different...specifically a -904 ...? Any ideas ? Is this what is
meant to happen ?

OK, I then thought I'd just use DSN1COPY with OBIDXLAT and RESET to copy them to
the DEV2 tables. When I tried this I got the message

DSN1970I DSN1TRAN DATA PAGE CONTAINING OBID 0155 HAS NO MATCHING VALUE IN
THE SYSXLAT FILE

Now the OBID in the dataset that had been overlayed was 202. The formatted
DSN1PRNT clearly showed this for all records. The value of 0155 was the value
for the new incarnation of the table and was the value currently stored in the
DB2 CATALOG for it.

Question 2. I didn't think DSN1COPY accessed the DB2 CATALOG at all. I checked
my monitor for past threads and there was none. Being a STAND ALONE utility I
didn't expect to find one. I can only image that it directly accessed the
CATALOG datasets ? Where did it get this value ?

To get past this I did a DFDSS RESTORE of these same tablespaces from the same
backup tape and used a RENAME UNCONDITIONAL to put them to another high level
qualifier (my own USERID) so the name was NOT that of a VSAM cluster for an
existing table. I then SUCCESSFULLY used the same DSN1COPY jobs to write to the
DEV2 tablespaces, did a REPAIR LEVELID and RECOVERED the indexes and all was OK.


Any comments ? Is this Working As Designed ?

Thanks,
Paul.




*************************************************************
Opinions contained in this e-mail do not necessarily reflect
the opinions of the Queensland Department of Main Roads, or
of Queensland Transport. If you have received this electronic
mail message in error, please immediately notify the sender
and delete the message from your computer.



Keith MARTIN

Re: Select from table with different magic numbers.
(in response to Paul A Redhead)
Paul,

1. DB2 worked as designed. DB2 would have looked for spacemap pages with
the new OBID, found none and returned no rows. (if anyone wants to put a
more technical explanation out there please do). I have assumed that you
had not done a reorg and a modify catalog to remove all image copies prior
to the reorg.

2. DSN1Copy doesn't access the catalog. At a guess DB2 had updated a
spacemap and inserted the new tables OBID which DSN1Copy detected and
(rightly) complained about. Given that you were able to run the same
DSN1Copy job on untouched restored datasets this is the most likely outcome.
If you still have the datasets that you recovered to and then selected from,
rerun your DSN1Print and check for the new tables OBID. It will be there
somewhere.

Keith Martin
Application Specialist Services
Database Management
+617 383 55246
mailto:[login to unmask email]

The views expressed herein are the views of the writer and may not
necessarily be the views of SUNCORP-METWAY.


> -----Original Message-----
> From: Paul A Redhead [SMTP:[login to unmask email]
> Sent: Friday, December 17, 1999 3:33 PM
> To: [login to unmask email]
> Subject: Select from table with different magic numbers.
>
> Folks,
> I just thought I'd communicate a couple of things that recently
> changed my
> understanding of a couple of DB2 concepts. It may be that I'm the only one
> surprised by them, and if so then I hope I can get a couple of explanatory
> comments.
>
> Situation was that there were 2 'temporary' development environments, say
> DEV1
> and DEV2. The DEV1 environment had been completely backed up using a
> DFDSS DUMP
> to tape at some earlier stage. I was then requested to retrieve the data
> from
> this for 6 tables and use that data to populate those tables in DEV2. I
> know
> there are lots of ways to skin a cat, however I thought I'd back up the
> DEV1
> tables, do a DFDSS RESTORE of those 6 tables from the dump back over the
> DEV1
> tablespaces/indexes, do a REPAIR LEVELID, use SQL to move the data to DEV2
> (only
> small tables, all < 50 rows), then put the DEV1 data back. I did the
> DFDSS
> RESTORE of the 6 tablespaces/indexes (yes, STOP DB to externalize pages
> first
> etc...) and the return code from the RESTORE was 0. I REPAIRed them, got a
> 0. I
> then did a SELECT * from the table using QMF and got no rows returned.
> Everything appeared fine, just a normal empty table, no negative return
> code.
> The user was adamant that there should have been data there. I did a
> DSN1PRNT of
> page 2, and lo and behold there were in fact rows there...nicely
> formatted,
> everything looked OK. It was then I found out that the table had been
> dropped
> and recreated (SAME definitions) since the DUMP had been taken. DSN1PRNT
> of page
> 0 and page 2 revealed the magic numbers (DBID, PSID, OBID) were different
> to
> those in the catalog.
>
> Question 1. I would have expected a negative return code from the SQL if
> the
> numbers were different...specifically a -904 ...? Any ideas ? Is this what
> is
> meant to happen ?
>
> OK, I then thought I'd just use DSN1COPY with OBIDXLAT and RESET to copy
> them to
> the DEV2 tables. When I tried this I got the message
>
> DSN1970I DSN1TRAN DATA PAGE CONTAINING OBID 0155 HAS NO MATCHING
> VALUE IN
> THE SYSXLAT FILE
>
> Now the OBID in the dataset that had been overlayed was 202. The
> formatted
> DSN1PRNT clearly showed this for all records. The value of 0155 was the
> value
> for the new incarnation of the table and was the value currently stored in
> the
> DB2 CATALOG for it.
>
> Question 2. I didn't think DSN1COPY accessed the DB2 CATALOG at all. I
> checked
> my monitor for past threads and there was none. Being a STAND ALONE
> utility I
> didn't expect to find one. I can only image that it directly accessed the
> CATALOG datasets ? Where did it get this value ?
>
> To get past this I did a DFDSS RESTORE of these same tablespaces from the
> same
> backup tape and used a RENAME UNCONDITIONAL to put them to another high
> level
> qualifier (my own USERID) so the name was NOT that of a VSAM cluster for
> an
> existing table. I then SUCCESSFULLY used the same DSN1COPY jobs to write
> to the
> DEV2 tablespaces, did a REPAIR LEVELID and RECOVERED the indexes and all
> was OK.
>
>
> Any comments ? Is this Working As Designed ?
>
> Thanks,
> Paul.
>
>
>
>
> *************************************************************
> Opinions contained in this e-mail do not necessarily reflect
> the opinions of the Queensland Department of Main Roads, or
> of Queensland Transport. If you have received this electronic
> mail message in error, please immediately notify the sender
> and delete the message from your computer.
>
>
>
>
>

------------------------------------------------------------

The contents of this message are the views of the Author and
do not necessarily reflect the views of SUNCORP METWAY LTD.

http://www.suncorpmetway.com.au



Paul A Redhead

Re: Select from table with different magic numbers.
(in response to Keith MARTIN)
Keith,
Thanks for your reply. I like your idea about the SELECT from the table.
The only problem I have with it (and I should have stated this more clearly in
the original post) is that ALL THREE magic numbers were different. The database
had been dropped and recreated. The DBID, PSID and OBID were ALL different. I
would have expected DB2 to detect this. Maybe it just uses the DBD to
'overlay' and looks for OBID values related to that table. If this is the case
then I accept your explanation. The tablespace IS segmented.

I must admit that I still have a problem with the DSN1COPY part of things.
No UPDATE activity of any sort was performed to the table....not that any could
have been (I would hope). I still have the formatted DSN1PRNT of the only
data page, and there is definitely only the one OBID value. I have had one of
the other DBA's check this independently, but then I guess we could be having a
group delusion...seems like it some days.

Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on 17/12/99
15:53 ---------------------------


"MARTIN, Keith" <[login to unmask email]> on 17/12/99 15:48:48

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: Re: Select from table with different magic numbers.




Paul,

1. DB2 worked as designed. DB2 would have looked for spacemap pages with
the new OBID, found none and returned no rows. (if anyone wants to put a
more technical explanation out there please do). I have assumed that you
had not done a reorg and a modify catalog to remove all image copies prior
to the reorg.

2. DSN1Copy doesn't access the catalog. At a guess DB2 had updated a
spacemap and inserted the new tables OBID which DSN1Copy detected and
(rightly) complained about. Given that you were able to run the same
DSN1Copy job on untouched restored datasets this is the most likely outcome.
If you still have the datasets that you recovered to and then selected from,
rerun your DSN1Print and check for the new tables OBID. It will be there
somewhere.

Keith Martin
Application Specialist Services
Database Management
+617 383 55246
mailto:[login to unmask email]

The views expressed herein are the views of the writer and may not
necessarily be the views of SUNCORP-METWAY.


> -----Original Message-----
> From: Paul A Redhead [SMTP:[login to unmask email]
> Sent: Friday, December 17, 1999 3:33 PM
> To: [login to unmask email]
> Subject: Select from table with different magic numbers.
>
> Folks,
> I just thought I'd communicate a couple of things that recently
> changed my
> understanding of a couple of DB2 concepts. It may be that I'm the only one
> surprised by them, and if so then I hope I can get a couple of explanatory
> comments.
>
> Situation was that there were 2 'temporary' development environments, say
> DEV1
> and DEV2. The DEV1 environment had been completely backed up using a
> DFDSS DUMP
> to tape at some earlier stage. I was then requested to retrieve the data
> from
> this for 6 tables and use that data to populate those tables in DEV2. I
> know
> there are lots of ways to skin a cat, however I thought I'd back up the
> DEV1
> tables, do a DFDSS RESTORE of those 6 tables from the dump back over the
> DEV1
> tablespaces/indexes, do a REPAIR LEVELID, use SQL to move the data to DEV2
> (only
> small tables, all < 50 rows), then put the DEV1 data back. I did the
> DFDSS
> RESTORE of the 6 tablespaces/indexes (yes, STOP DB to externalize pages
> first
> etc...) and the return code from the RESTORE was 0. I REPAIRed them, got a
> 0. I
> then did a SELECT * from the table using QMF and got no rows returned.
> Everything appeared fine, just a normal empty table, no negative return
> code.
> The user was adamant that there should have been data there. I did a
> DSN1PRNT of
> page 2, and lo and behold there were in fact rows there...nicely
> formatted,
> everything looked OK. It was then I found out that the table had been
> dropped
> and recreated (SAME definitions) since the DUMP had been taken. DSN1PRNT
> of page
> 0 and page 2 revealed the magic numbers (DBID, PSID, OBID) were different
> to
> those in the catalog.
>
> Question 1. I would have expected a negative return code from the SQL if
> the
> numbers were different...specifically a -904 ...? Any ideas ? Is this what
> is
> meant to happen ?
>
> OK, I then thought I'd just use DSN1COPY with OBIDXLAT and RESET to copy
> them to
> the DEV2 tables. When I tried this I got the message
>
> DSN1970I DSN1TRAN DATA PAGE CONTAINING OBID 0155 HAS NO MATCHING
> VALUE IN
> THE SYSXLAT FILE
>
> Now the OBID in the dataset that had been overlayed was 202. The
> formatted
> DSN1PRNT clearly showed this for all records. The value of 0155 was the
> value
> for the new incarnation of the table and was the value currently stored in
> the
> DB2 CATALOG for it.
>
> Question 2. I didn't think DSN1COPY accessed the DB2 CATALOG at all. I
> checked
> my monitor for past threads and there was none. Being a STAND ALONE
> utility I
> didn't expect to find one. I can only image that it directly accessed the
> CATALOG datasets ? Where did it get this value ?
>
> To get past this I did a DFDSS RESTORE of these same tablespaces from the
> same
> backup tape and used a RENAME UNCONDITIONAL to put them to another high
> level
> qualifier (my own USERID) so the name was NOT that of a VSAM cluster for
> an
> existing table. I then SUCCESSFULLY used the same DSN1COPY jobs to write
> to the
> DEV2 tablespaces, did a REPAIR LEVELID and RECOVERED the indexes and all
> was OK.
>
>
> Any comments ? Is this Working As Designed ?
>
> Thanks,
> Paul.
>
>
>
>
> *************************************************************
> Opinions contained in this e-mail do not necessarily reflect
> the opinions of the Queensland Department of Main Roads, or
> of Queensland Transport. If you have received this electronic
> mail message in error, please immediately notify the sender
> and delete the message from your computer.
>
>
>
>
>

------------------------------------------------------------

The contents of this message are the views of the Author and
do not necessarily reflect the views of SUNCORP METWAY LTD.

http://www.suncorpmetway.com.au








Paul A Redhead

Re: Select from table with different magic numbers.
(in response to Paul A Redhead)
Keith,
As you suggested in (2.) I went back and did a DSN1PRNT of the spacemap
page and DID find a segment with a listing for the OBID of 155 that was in the
CATALOG. I don't understand how this got there. There was no update performed
to this table. There were no data rows associated with it...but it was there,
and maybe DSN1COPY insists on a conversion for it. Could you explain this
mechanism ? I'm stumped, but would be extremely grateful for an answer.

Thanks very much,
Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on 17/12/99
16:56 ---------------------------


"MARTIN, Keith" <[login to unmask email]> on 17/12/99 15:48:48

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: Re: Select from table with different magic numbers.




Paul,

1. DB2 worked as designed. DB2 would have looked for spacemap pages with
the new OBID, found none and returned no rows. (if anyone wants to put a
more technical explanation out there please do). I have assumed that you
had not done a reorg and a modify catalog to remove all image copies prior
to the reorg.

2. DSN1Copy doesn't access the catalog. At a guess DB2 had updated a
spacemap and inserted the new tables OBID which DSN1Copy detected and
(rightly) complained about. Given that you were able to run the same
DSN1Copy job on untouched restored datasets this is the most likely outcome.
If you still have the datasets that you recovered to and then selected from,
rerun your DSN1Print and check for the new tables OBID. It will be there
somewhere.

Keith Martin
Application Specialist Services
Database Management
+617 383 55246
mailto:[login to unmask email]

The views expressed herein are the views of the writer and may not
necessarily be the views of SUNCORP-METWAY.


> -----Original Message-----
> From: Paul A Redhead [SMTP:[login to unmask email]
> Sent: Friday, December 17, 1999 3:33 PM
> To: [login to unmask email]
> Subject: Select from table with different magic numbers.
>
> Folks,
> I just thought I'd communicate a couple of things that recently
> changed my
> understanding of a couple of DB2 concepts. It may be that I'm the only one
> surprised by them, and if so then I hope I can get a couple of explanatory
> comments.
>
> Situation was that there were 2 'temporary' development environments, say
> DEV1
> and DEV2. The DEV1 environment had been completely backed up using a
> DFDSS DUMP
> to tape at some earlier stage. I was then requested to retrieve the data
> from
> this for 6 tables and use that data to populate those tables in DEV2. I
> know
> there are lots of ways to skin a cat, however I thought I'd back up the
> DEV1
> tables, do a DFDSS RESTORE of those 6 tables from the dump back over the
> DEV1
> tablespaces/indexes, do a REPAIR LEVELID, use SQL to move the data to DEV2
> (only
> small tables, all < 50 rows), then put the DEV1 data back. I did the
> DFDSS
> RESTORE of the 6 tablespaces/indexes (yes, STOP DB to externalize pages
> first
> etc...) and the return code from the RESTORE was 0. I REPAIRed them, got a
> 0. I
> then did a SELECT * from the table using QMF and got no rows returned.
> Everything appeared fine, just a normal empty table, no negative return
> code.
> The user was adamant that there should have been data there. I did a
> DSN1PRNT of
> page 2, and lo and behold there were in fact rows there...nicely
> formatted,
> everything looked OK. It was then I found out that the table had been
> dropped
> and recreated (SAME definitions) since the DUMP had been taken. DSN1PRNT
> of page
> 0 and page 2 revealed the magic numbers (DBID, PSID, OBID) were different
> to
> those in the catalog.
>
> Question 1. I would have expected a negative return code from the SQL if
> the
> numbers were different...specifically a -904 ...? Any ideas ? Is this what
> is
> meant to happen ?
>
> OK, I then thought I'd just use DSN1COPY with OBIDXLAT and RESET to copy
> them to
> the DEV2 tables. When I tried this I got the message
>
> DSN1970I DSN1TRAN DATA PAGE CONTAINING OBID 0155 HAS NO MATCHING
> VALUE IN
> THE SYSXLAT FILE
>
> Now the OBID in the dataset that had been overlayed was 202. The
> formatted
> DSN1PRNT clearly showed this for all records. The value of 0155 was the
> value
> for the new incarnation of the table and was the value currently stored in
> the
> DB2 CATALOG for it.
>
> Question 2. I didn't think DSN1COPY accessed the DB2 CATALOG at all. I
> checked
> my monitor for past threads and there was none. Being a STAND ALONE
> utility I
> didn't expect to find one. I can only image that it directly accessed the
> CATALOG datasets ? Where did it get this value ?
>
> To get past this I did a DFDSS RESTORE of these same tablespaces from the
> same
> backup tape and used a RENAME UNCONDITIONAL to put them to another high
> level
> qualifier (my own USERID) so the name was NOT that of a VSAM cluster for
> an
> existing table. I then SUCCESSFULLY used the same DSN1COPY jobs to write
> to the
> DEV2 tablespaces, did a REPAIR LEVELID and RECOVERED the indexes and all
> was OK.
>
>
> Any comments ? Is this Working As Designed ?
>
> Thanks,
> Paul.
>
>
>
>
> *************************************************************
> Opinions contained in this e-mail do not necessarily reflect
> the opinions of the Queensland Department of Main Roads, or
> of Queensland Transport. If you have received this electronic
> mail message in error, please immediately notify the sender
> and delete the message from your computer.
>
>
>
>
>

------------------------------------------------------------

The contents of this message are the views of the Author and
do not necessarily reflect the views of SUNCORP METWAY LTD.

http://www.suncorpmetway.com.au








Linda Ball

Re: Select from table with different magic numbers.
(in response to Paul A Redhead)
I'm a little lost about all the steps you took here, but this fact may help
you: when a table is created in a segmented table space, the first anchor
segment is reserved. Could that be how you got the segment in your space
map?




Opinions are, of course, my own and not BMC Software's official position!
--------------------------------------------
Linda Ball
DB2 Corporate Architect
OS/390 Product Development
Direct Phone: (512) 340-6722
Fax: (512) 340-6646
Toll Free: (800) 841-2031
[login to unmask email]

-----Original Message-----
From: Paul A Redhead [mailto:[login to unmask email]
Sent: Friday, December 17, 1999 12:59 AM
To: [login to unmask email]
Subject: Re: Select from table with different magic numbers.


Keith,
As you suggested in (2.) I went back and did a DSN1PRNT of the spacemap
page and DID find a segment with a listing for the OBID of 155 that was in
the
CATALOG. I don't understand how this got there. There was no update
performed
to this table. There were no data rows associated with it...but it was
there,
and maybe DSN1COPY insists on a conversion for it. Could you explain this
mechanism ? I'm stumped, but would be extremely grateful for an answer.

Thanks very much,
Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on
17/12/99
16:56 ---------------------------


"MARTIN, Keith" <[login to unmask email]> on 17/12/99 15:48:48

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: Re: Select from table with different magic numbers.




Paul,

1. DB2 worked as designed. DB2 would have looked for spacemap pages with
the new OBID, found none and returned no rows. (if anyone wants to put a
more technical explanation out there please do). I have assumed that you
had not done a reorg and a modify catalog to remove all image copies prior
to the reorg.

2. DSN1Copy doesn't access the catalog. At a guess DB2 had updated a
spacemap and inserted the new tables OBID which DSN1Copy detected and
(rightly) complained about. Given that you were able to run the same
DSN1Copy job on untouched restored datasets this is the most likely outcome.
If you still have the datasets that you recovered to and then selected from,
rerun your DSN1Print and check for the new tables OBID. It will be there
somewhere.

Keith Martin
Application Specialist Services
Database Management
+617 383 55246
mailto:[login to unmask email]

The views expressed herein are the views of the writer and may not
necessarily be the views of SUNCORP-METWAY.


> -----Original Message-----
> From: Paul A Redhead [SMTP:[login to unmask email]
> Sent: Friday, December 17, 1999 3:33 PM
> To: [login to unmask email]
> Subject: Select from table with different magic numbers.
>
> Folks,
> I just thought I'd communicate a couple of things that recently
> changed my
> understanding of a couple of DB2 concepts. It may be that I'm the only one
> surprised by them, and if so then I hope I can get a couple of explanatory
> comments.
>
> Situation was that there were 2 'temporary' development environments, say
> DEV1
> and DEV2. The DEV1 environment had been completely backed up using a
> DFDSS DUMP
> to tape at some earlier stage. I was then requested to retrieve the data
> from
> this for 6 tables and use that data to populate those tables in DEV2. I
> know
> there are lots of ways to skin a cat, however I thought I'd back up the
> DEV1
> tables, do a DFDSS RESTORE of those 6 tables from the dump back over the
> DEV1
> tablespaces/indexes, do a REPAIR LEVELID, use SQL to move the data to DEV2
> (only
> small tables, all < 50 rows), then put the DEV1 data back. I did the
> DFDSS
> RESTORE of the 6 tablespaces/indexes (yes, STOP DB to externalize pages
> first
> etc...) and the return code from the RESTORE was 0. I REPAIRed them, got a
> 0. I
> then did a SELECT * from the table using QMF and got no rows returned.
> Everything appeared fine, just a normal empty table, no negative return
> code.
> The user was adamant that there should have been data there. I did a
> DSN1PRNT of
> page 2, and lo and behold there were in fact rows there...nicely
> formatted,
> everything looked OK. It was then I found out that the table had been
> dropped
> and recreated (SAME definitions) since the DUMP had been taken. DSN1PRNT
> of page
> 0 and page 2 revealed the magic numbers (DBID, PSID, OBID) were different
> to
> those in the catalog.
>
> Question 1. I would have expected a negative return code from the SQL if
> the
> numbers were different...specifically a -904 ...? Any ideas ? Is this what
> is
> meant to happen ?
>
> OK, I then thought I'd just use DSN1COPY with OBIDXLAT and RESET to copy
> them to
> the DEV2 tables. When I tried this I got the message
>
> DSN1970I DSN1TRAN DATA PAGE CONTAINING OBID 0155 HAS NO MATCHING
> VALUE IN
> THE SYSXLAT FILE
>
> Now the OBID in the dataset that had been overlayed was 202. The
> formatted
> DSN1PRNT clearly showed this for all records. The value of 0155 was the
> value
> for the new incarnation of the table and was the value currently stored in
> the
> DB2 CATALOG for it.
>
> Question 2. I didn't think DSN1COPY accessed the DB2 CATALOG at all. I
> checked
> my monitor for past threads and there was none. Being a STAND ALONE
> utility I
> didn't expect to find one. I can only image that it directly accessed the
> CATALOG datasets ? Where did it get this value ?
>
> To get past this I did a DFDSS RESTORE of these same tablespaces from the
> same
> backup tape and used a RENAME UNCONDITIONAL to put them to another high
> level
> qualifier (my own USERID) so the name was NOT that of a VSAM cluster for
> an
> existing table. I then SUCCESSFULLY used the same DSN1COPY jobs to write
> to the
> DEV2 tablespaces, did a REPAIR LEVELID and RECOVERED the indexes and all
> was OK.
>
>
> Any comments ? Is this Working As Designed ?
>
> Thanks,
> Paul.
>
>
>
>
> *************************************************************
> Opinions contained in this e-mail do not necessarily reflect
> the opinions of the Queensland Department of Main Roads, or
> of Queensland Transport. If you have received this electronic
> mail message in error, please immediately notify the sender
> and delete the message from your computer.
>
>
>
>
>

------------------------------------------------------------

The contents of this message are the views of the Author and
do not necessarily reflect the views of SUNCORP METWAY LTD.

http://www.suncorpmetway.com.au













Paul A Redhead

Re: Select from table with different magic numbers.
(in response to Linda Ball)
Linda,
With the new revelation regarding the segment entry in the spacemap page my
question as to how I saw what I saw has been resolved. My question is now about
the mechanism. I'll break my question down into the following and hope someone
can explain the mechanism.

1. Create database, tablespace, table. Lets say DBID=1, PSID=1,OBID=1 (for
example).
2. DFDSS DUMP this.
3. Drop and recreate the database using the same DDL.
4. New Database, tablespace, table numbers are DBID=2, PSID=2, OBID=2.
5. STOP tablespace, DFDSS RESTORE tablespace and indexspace datasets.
6. REPAIR LEVELID on tablespace, START tablespace.
7. SELECT * from table. No error SQLCODE, just no rows returned.

8. DSN1PRNT page 2 to find segment entries for OBID=1 (first segment entry)
AND (!) a segment entry for OBID=2 .

Got no rows returned because DB2 went to the spacemap page with the OBID
for the current table from the catalog (OBID=2) and found no entries.
Somewhere between step 5 and step 7 DB2 actually updated the spacemap page with
a segment entry for the table in the catalog that had previously had no segment
entry. NO checking of DBID/PSID could have been done. This is what confused
me... I wrongly assumed I should have seen a -904 .

Thanks,
Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on 20/12/99
09:31 ---------------------------


"Ball, Linda" <[login to unmask email]> on 18/12/99 02:38:00

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: Re: Select from table with different magic numbers.




I'm a little lost about all the steps you took here, but this fact may help
you: when a table is created in a segmented table space, the first anchor
segment is reserved. Could that be how you got the segment in your space
map?




Opinions are, of course, my own and not BMC Software's official position!
--------------------------------------------
Linda Ball
DB2 Corporate Architect
OS/390 Product Development
Direct Phone: (512) 340-6722
Fax: (512) 340-6646
Toll Free: (800) 841-2031
[login to unmask email]

-----Original Message-----
From: Paul A Redhead [mailto:[login to unmask email]
Sent: Friday, December 17, 1999 12:59 AM
To: [login to unmask email]
Subject: Re: Select from table with different magic numbers.


Keith,
As you suggested in (2.) I went back and did a DSN1PRNT of the spacemap
page and DID find a segment with a listing for the OBID of 155 that was in
the
CATALOG. I don't understand how this got there. There was no update
performed
to this table. There were no data rows associated with it...but it was
there,
and maybe DSN1COPY insists on a conversion for it. Could you explain this
mechanism ? I'm stumped, but would be extremely grateful for an answer.

Thanks very much,
Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on
17/12/99
16:56 ---------------------------


"MARTIN, Keith" <[login to unmask email]> on 17/12/99 15:48:48

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: Re: Select from table with different magic numbers.




Paul,

1. DB2 worked as designed. DB2 would have looked for spacemap pages with
the new OBID, found none and returned no rows. (if anyone wants to put a
more technical explanation out there please do). I have assumed that you
had not done a reorg and a modify catalog to remove all image copies prior
to the reorg.

2. DSN1Copy doesn't access the catalog. At a guess DB2 had updated a
spacemap and inserted the new tables OBID which DSN1Copy detected and
(rightly) complained about. Given that you were able to run the same
DSN1Copy job on untouched restored datasets this is the most likely outcome.
If you still have the datasets that you recovered to and then selected from,
rerun your DSN1Print and check for the new tables OBID. It will be there
somewhere.

Keith Martin
Application Specialist Services
Database Management
+617 383 55246
mailto:[login to unmask email]

The views expressed herein are the views of the writer and may not
necessarily be the views of SUNCORP-METWAY.


> -----Original Message-----
> From: Paul A Redhead [SMTP:[login to unmask email]
> Sent: Friday, December 17, 1999 3:33 PM
> To: [login to unmask email]
> Subject: Select from table with different magic numbers.
>
> Folks,
> I just thought I'd communicate a couple of things that recently
> changed my
> understanding of a couple of DB2 concepts. It may be that I'm the only one
> surprised by them, and if so then I hope I can get a couple of explanatory
> comments.
>
> Situation was that there were 2 'temporary' development environments, say
> DEV1
> and DEV2. The DEV1 environment had been completely backed up using a
> DFDSS DUMP
> to tape at some earlier stage. I was then requested to retrieve the data
> from
> this for 6 tables and use that data to populate those tables in DEV2. I
> know
> there are lots of ways to skin a cat, however I thought I'd back up the
> DEV1
> tables, do a DFDSS RESTORE of those 6 tables from the dump back over the
> DEV1
> tablespaces/indexes, do a REPAIR LEVELID, use SQL to move the data to DEV2
> (only
> small tables, all < 50 rows), then put the DEV1 data back. I did the
> DFDSS
> RESTORE of the 6 tablespaces/indexes (yes, STOP DB to externalize pages
> first
> etc...) and the return code from the RESTORE was 0. I REPAIRed them, got a
> 0. I
> then did a SELECT * from the table using QMF and got no rows returned.
> Everything appeared fine, just a normal empty table, no negative return
> code.
> The user was adamant that there should have been data there. I did a
> DSN1PRNT of
> page 2, and lo and behold there were in fact rows there...nicely
> formatted,
> everything looked OK. It was then I found out that the table had been
> dropped
> and recreated (SAME definitions) since the DUMP had been taken. DSN1PRNT
> of page
> 0 and page 2 revealed the magic numbers (DBID, PSID, OBID) were different
> to
> those in the catalog.
>
> Question 1. I would have expected a negative return code from the SQL if
> the
> numbers were different...specifically a -904 ...? Any ideas ? Is this what
> is
> meant to happen ?
>
> OK, I then thought I'd just use DSN1COPY with OBIDXLAT and RESET to copy
> them to
> the DEV2 tables. When I tried this I got the message
>
> DSN1970I DSN1TRAN DATA PAGE CONTAINING OBID 0155 HAS NO MATCHING
> VALUE IN
> THE SYSXLAT FILE
>
> Now the OBID in the dataset that had been overlayed was 202. The
> formatted
> DSN1PRNT clearly showed this for all records. The value of 0155 was the
> value
> for the new incarnation of the table and was the value currently stored in
> the
> DB2 CATALOG for it.
>
> Question 2. I didn't think DSN1COPY accessed the DB2 CATALOG at all. I
> checked
> my monitor for past threads and there was none. Being a STAND ALONE
> utility I
> didn't expect to find one. I can only image that it directly accessed the
> CATALOG datasets ? Where did it get this value ?
>
> To get past this I did a DFDSS RESTORE of these same tablespaces from the
> same
> backup tape and used a RENAME UNCONDITIONAL to put them to another high
> level
> qualifier (my own USERID) so the name was NOT that of a VSAM cluster for
> an
> existing table. I then SUCCESSFULLY used the same DSN1COPY jobs to write
> to the
> DEV2 tablespaces, did a REPAIR LEVELID and RECOVERED the indexes and all
> was OK.
>
>
> Any comments ? Is this Working As Designed ?
>
> Thanks,
> Paul.
>
>
>
>
> *************************************************************
> Opinions contained in this e-mail do not necessarily reflect
> the opinions of the Queensland Department of Main Roads, or
> of Queensland Transport. If you have received this electronic
> mail message in error, please immediately notify the sender
> and delete the message from your computer.
>
>
>
>
>

------------------------------------------------------------

The contents of this message are the views of the Author and
do not necessarily reflect the views of SUNCORP METWAY LTD.

http://www.suncorpmetway.com.au


















Linda Ball

Re: Select from table with different magic numbers.
(in response to Paul A Redhead)
I have an idea that the code in DB2 which repairs missing or inaccurate
segmented chains actually put an entry into the space map as an anchor. We
will have to prove that with a testing. The SELECT * could have triggered
that code. We will verify this. But that sure looks like what happened to
you. Thanks for making the light bulb go off for me. I hate mysteries!

Opinions are, of course, my own and not BMC Software's official position!
--------------------------------------------
Linda Ball
DB2 Corporate Architect
OS/390 Product Development
Direct Phone: (512) 340-6722
Fax: (512) 340-6646
Toll Free: (800) 841-2031
[login to unmask email]

-----Original Message-----
From: Paul A Redhead [mailto:[login to unmask email]
Sent: Sunday, December 19, 1999 5:45 PM
To: [login to unmask email]
Subject: Re: Select from table with different magic numbers.


Linda,
With the new revelation regarding the segment entry in the spacemap
page my
question as to how I saw what I saw has been resolved. My question is now
about
the mechanism. I'll break my question down into the following and hope
someone
can explain the mechanism.

1. Create database, tablespace, table. Lets say DBID=1, PSID=1,OBID=1 (for
example).
2. DFDSS DUMP this.
3. Drop and recreate the database using the same DDL.
4. New Database, tablespace, table numbers are DBID=2, PSID=2, OBID=2.
5. STOP tablespace, DFDSS RESTORE tablespace and indexspace datasets.
6. REPAIR LEVELID on tablespace, START tablespace.
7. SELECT * from table. No error SQLCODE, just no rows returned.

8. DSN1PRNT page 2 to find segment entries for OBID=1 (first segment entry)
AND (!) a segment entry for OBID=2 .

Got no rows returned because DB2 went to the spacemap page with the
OBID
for the current table from the catalog (OBID=2) and found no entries.
Somewhere between step 5 and step 7 DB2 actually updated the spacemap page
with
a segment entry for the table in the catalog that had previously had no
segment
entry. NO checking of DBID/PSID could have been done. This is what confused
me... I wrongly assumed I should have seen a -904 .

Thanks,
Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on
20/12/99
09:31 ---------------------------


"Ball, Linda" <[login to unmask email]> on 18/12/99 02:38:00

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: Re: Select from table with different magic numbers.




I'm a little lost about all the steps you took here, but this fact may help
you: when a table is created in a segmented table space, the first anchor
segment is reserved. Could that be how you got the segment in your space
map?




Opinions are, of course, my own and not BMC Software's official position!
--------------------------------------------
Linda Ball
DB2 Corporate Architect
OS/390 Product Development
Direct Phone: (512) 340-6722
Fax: (512) 340-6646
Toll Free: (800) 841-2031
[login to unmask email]

-----Original Message-----
From: Paul A Redhead [mailto:[login to unmask email]
Sent: Friday, December 17, 1999 12:59 AM
To: [login to unmask email]
Subject: Re: Select from table with different magic numbers.


Keith,
As you suggested in (2.) I went back and did a DSN1PRNT of the spacemap
page and DID find a segment with a listing for the OBID of 155 that was in
the
CATALOG. I don't understand how this got there. There was no update
performed
to this table. There were no data rows associated with it...but it was
there,
and maybe DSN1COPY insists on a conversion for it. Could you explain this
mechanism ? I'm stumped, but would be extremely grateful for an answer.

Thanks very much,
Paul.

---------------------- Forwarded by Paul A Redhead/CorpServ/qdot/au on
17/12/99
16:56 ---------------------------


"MARTIN, Keith" <[login to unmask email]> on 17/12/99 15:48:48

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Paul A Redhead/CorpServ/qdot/au)
Subject: Re: Select from table with different magic numbers.




Paul,

1. DB2 worked as designed. DB2 would have looked for spacemap pages with
the new OBID, found none and returned no rows. (if anyone wants to put a
more technical explanation out there please do). I have assumed that you
had not done a reorg and a modify catalog to remove all image copies prior
to the reorg.

2. DSN1Copy doesn't access the catalog. At a guess DB2 had updated a
spacemap and inserted the new tables OBID which DSN1Copy detected and
(rightly) complained about. Given that you were able to run the same
DSN1Copy job on untouched restored datasets this is the most likely outcome.
If you still have the datasets that you recovered to and then selected from,
rerun your DSN1Print and check for the new tables OBID. It will be there
somewhere.

Keith Martin
Application Specialist Services
Database Management
+617 383 55246
mailto:[login to unmask email]

The views expressed herein are the views of the writer and may not
necessarily be the views of SUNCORP-METWAY.


> -----Original Message-----
> From: Paul A Redhead [SMTP:[login to unmask email]
> Sent: Friday, December 17, 1999 3:33 PM
> To: [login to unmask email]
> Subject: Select from table with different magic numbers.
>
> Folks,
> I just thought I'd communicate a couple of things that recently
> changed my
> understanding of a couple of DB2 concepts. It may be that I'm the only one
> surprised by them, and if so then I hope I can get a couple of explanatory
> comments.
>
> Situation was that there were 2 'temporary' development environments, say
> DEV1
> and DEV2. The DEV1 environment had been completely backed up using a
> DFDSS DUMP
> to tape at some earlier stage. I was then requested to retrieve the data
> from
> this for 6 tables and use that data to populate those tables in DEV2. I
> know
> there are lots of ways to skin a cat, however I thought I'd back up the
> DEV1
> tables, do a DFDSS RESTORE of those 6 tables from the dump back over the
> DEV1
> tablespaces/indexes, do a REPAIR LEVELID, use SQL to move the data to DEV2
> (only
> small tables, all < 50 rows), then put the DEV1 data back. I did the
> DFDSS
> RESTORE of the 6 tablespaces/indexes (yes, STOP DB to externalize pages
> first
> etc...) and the return code from the RESTORE was 0. I REPAIRed them, got a
> 0. I
> then did a SELECT * from the table using QMF and got no rows returned.
> Everything appeared fine, just a normal empty table, no negative return
> code.
> The user was adamant that there should have been data there. I did a
> DSN1PRNT of
> page 2, and lo and behold there were in fact rows there...nicely
> formatted,
> everything looked OK. It was then I found out that the table had been
> dropped
> and recreated (SAME definitions) since the DUMP had been taken. DSN1PRNT
> of page
> 0 and page 2 revealed the magic numbers (DBID, PSID, OBID) were different
> to
> those in the catalog.
>
> Question 1. I would have expected a negative return code from the SQL if
> the
> numbers were different...specifically a -904 ...? Any ideas ? Is this what
> is
> meant to happen ?
>
> OK, I then thought I'd just use DSN1COPY with OBIDXLAT and RESET to copy
> them to
> the DEV2 tables. When I tried this I got the message
>
> DSN1970I DSN1TRAN DATA PAGE CONTAINING OBID 0155 HAS NO MATCHING
> VALUE IN
> THE SYSXLAT FILE
>
> Now the OBID in the dataset that had been overlayed was 202. The
> formatted
> DSN1PRNT clearly showed this for all records. The value of 0155 was the
> value
> for the new incarnation of the table and was the value currently stored in
> the
> DB2 CATALOG for it.
>
> Question 2. I didn't think DSN1COPY accessed the DB2 CATALOG at all. I
> checked
> my monitor for past threads and there was none. Being a STAND ALONE
> utility I
> didn't expect to find one. I can only image that it directly accessed the
> CATALOG datasets ? Where did it get this value ?
>
> To get past this I did a DFDSS RESTORE of these same tablespaces from the
> same
> backup tape and used a RENAME UNCONDITIONAL to put them to another high
> level
> qualifier (my own USERID) so the name was NOT that of a VSAM cluster for
> an
> existing table. I then SUCCESSFULLY used the same DSN1COPY jobs to write
> to the
> DEV2 tablespaces, did a REPAIR LEVELID and RECOVERED the indexes and all
> was OK.
>
>
> Any comments ? Is this Working As Designed ?
>
> Thanks,
> Paul.
>
>
>
>
> *************************************************************
> Opinions contained in this e-mail do not necessarily reflect
> the opinions of the Queensland Department of Main Roads, or
> of Queensland Transport. If you have received this electronic
> mail message in error, please immediately notify the sender
> and delete the message from your computer.
>
>
>
>
>

------------------------------------------------------------

The contents of this message are the views of the Author and
do not necessarily reflect the views of SUNCORP METWAY LTD.

http://www.suncorpmetway.com.au