zOS v7 OBID on Create Table sqlcode -736 - post #2

Brian Bear

zOS v7 OBID on Create Table sqlcode -736 - post #2
Hi Listers.

This was rejected as a dupe, so trying to send again with some minor
changes to the subject and the content. Sorry if you're reading this
again.




In trying to create a table specifying OBID 142. SQLCODE = -736, ERROR:
INVALID OBID 140 SPECIFIED results.

CREATE TABLE CHARM.TDIST23_PUSH_XREFB
LIKE CHARM.TDIST23_PUSH_XREF
IN DDIST01.SDIST123
OBID 140


Listing all the OBIDs, PSIDs and ISOBIDs from SYSTABLESPACE, SYSTABLES,
and SYSINDEXES shows no object using 142.

I have tried STOP/START the database and even recycled the entire
subsystem hoping it was merely the DBD still referring to the OBID. **
No good.

The SQLCODE -736 reads: ...select a different OBID for non-ROSHARE READ
database tables. TELL ME IT AIN'T SO!!

Previous postings indicate reorging the tablespace containing a previously
dropped table... Does anyone know how to identify the tablespace
that contained the dropped table ?

Any other suggestions?

Thanks in advance.

*****************************************************************************
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you
received this message in error, please contact the sender and delete
the material from any computer.
*****************************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sally Mir

Re: zOS v7 OBID on Create Table sqlcode -736 - post #2
(in response to Brian Bear)
There's one more place you could check for existing OBIDs:
SYSIBM.SYSCHECKS. Table check constraints are assigned OBIDs, so you might
be lucky enough to find that the 142 is being used by one of these.

If not, you could always do formatted dumps of the space map pages of
segmented tablespaces in the particular database you're working with and
look for the value in the SEGOBID field (don't forget to translate it to
hex). For non-segmented tablespaces you could dump the data pages and look
for your value in the PGSOBD field. Of course, you don't need to bother
looking in partitioned tablespaces, because you couldn't have a dropped
table lurking in one since you have to drop the tablespace in order to drop
a table.

For all that work, I'd just reorg everything in the database and be done
with it! :) Don't forget to do MODIFY RECOVERY jobs too, because the OBID
still cannot be reused if there is an image copy containing the dropped
table that could be used to recover back to it.


Sally A. Mir, AVP
IBM Certified Database Administrator
DB2 Universal Database V8.1 for Z/OS
(336) 773-4011



Brian Bear
<[login to unmask email]
ING.COM> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> [DB2-L] zOS v7 OBID on Create Table
sqlcode -736 - post #2

12/21/2005 03:17
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>







Hi Listers.

This was rejected as a dupe, so trying to send again with some minor
changes to the subject and the content. Sorry if you're reading this
again.




In trying to create a table specifying OBID 142. SQLCODE = -736, ERROR:
INVALID OBID 140 SPECIFIED results.

CREATE TABLE CHARM.TDIST23_PUSH_XREFB
LIKE CHARM.TDIST23_PUSH_XREF
IN DDIST01.SDIST123
OBID 140


Listing all the OBIDs, PSIDs and ISOBIDs from SYSTABLESPACE, SYSTABLES, and
SYSINDEXES shows no object using 142.

I have tried STOP/START the database and even recycled the entire subsystem
hoping it was merely the DBD still referring to the OBID. ** No good.

The SQLCODE -736 reads: ...select a different OBID for non-ROSHARE READ
database tables. TELL ME IT AIN'T SO!!

Previous postings indicate reorging the tablespace containing a previously
dropped table... Does anyone know how to identify the tablespace
that contained the dropped table ?

Any other suggestions?

Thanks in advance.
*****************************************************************************

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you
received this message in error, please contact the sender and delete
the material from any computer.
*****************************************************************************
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
ForwardSourceID:NT000457DE

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: zOS v7 OBID on Create Table sqlcode -736 - post #2
(in response to Sally Mir)
It looks here like you're trying to create an object with OBID 140, not OBID
142.

--Phil


On 12/21/05, Brian Bear <[login to unmask email]> wrote:
>
>
> Hi Listers.
>
> This was rejected as a dupe, so trying to send again with some minor
> changes to the subject and the content. Sorry if you're reading this again.
>
>
>
>
> In trying to create a table specifying OBID 142. SQLCODE = -736, ERROR:
> INVALID OBID 140 SPECIFIED results.
>
> CREATE TABLE CHARM.TDIST23_PUSH_XREFB
> LIKE CHARM.TDIST23_PUSH_XREF
> IN DDIST01.SDIST123
> OBID 140
>
>
> Listing all the OBIDs, PSIDs and ISOBIDs from SYSTABLESPACE, SYSTABLES,
> and SYSINDEXES shows no object using 142.
>
> I have tried STOP/START the database and even recycled the entire
> subsystem hoping it was merely the DBD still referring to the OBID. ** No
> good.
>
> The SQLCODE -736 reads: ...select a different OBID for non-ROSHARE READ
> database tables. TELL ME IT AIN'T SO!!
>
> Previous postings indicate reorging the tablespace containing a previously
> dropped table... Does anyone know how to identify the tablespace
> that contained the dropped table ?
>
> Any other suggestions?
>
> Thanks in advance.



--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm