Finding DB2 objects in questionable status...

SUBSCRIBE DB2-L Anonymous

Finding DB2 objects in questionable status...
Are there SYSIBM catalog/directory tables I can interrogate to get a list of
database objects and their status (tables, indexes, SPs, triggers, etc) that
are not in a normal, useable state? I know I can retrieve COPY PENDING
tables from SYSTABLESPACE STATUS but there are so many different
states an object can be in that may not be desirable.

Is there an IBM-supplied query that is run at upgrade or migration time that
lists objects in questionable status? I would like to do this without running -
DISPLAY DATABASE commands. Thanks in advance.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: Finding DB2 objects in questionable status...
(in response to SUBSCRIBE DB2-L Anonymous)
Unfortunately IBM don't externalize many of the "questionable statuses" so a
-DIS DATABASE is the only guaranteed way to find the oddities (like RECP,
AREORP, RBDP etc.)

If you want to do this from SQL, you could write a table UDF that does the
-DIS DATABASE and returns the result

Then you ought to be able to do

SELECT * FROM myudf_disdatabase(dbname,tsname) where you pass the database
and tablespace names as parameters

Phil Grainger

Grainger Database Solutions Ltd


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David
Sent: 20 January 2010 15:30
To: [login to unmask email]
Subject: [DB2-L] Finding DB2 objects in questionable status...

Are there SYSIBM catalog/directory tables I can interrogate to get a list of

database objects and their status (tables, indexes, SPs, triggers, etc) that

are not in a normal, useable state? I know I can retrieve COPY PENDING
tables from SYSTABLESPACE STATUS but there are so many different
states an object can be in that may not be desirable.

Is there an IBM-supplied query that is run at upgrade or migration time that

lists objects in questionable status? I would like to do this without
running -
DISPLAY DATABASE commands. Thanks in advance.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every
level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Martin Hubel

Re: Finding DB2 objects in questionable status...
(in response to Phil Grainger)
To use SQL, you can use the IBM supplied procedure SYSPROC.ADMIN_COMMAND_DB2, if you installed it.

From the DB2 command line connected to z/OS, I used:

C:\>db2 "call sysproc.admin_command_db2('-dis db(*) sp(*) restrict limit(*)', 34,DB,null,?,?,?,?,?,?,?,?)"

The inputs are Command, Length, DB for parse type, null for member. The 8 '?' refer to output values.

You could also run this from a select on the z/OS side.

hth--Martin

>> Unfortunately IBM don't externalize many of the "questionable statuses" so
>> a
>> -DIS DATABASE is the only guaranteed way to find the oddities (like RECP,
>> AREORP, RBDP etc.)

>> If you want to do this from SQL, you could write a table UDF that does the
>> -DIS DATABASE and returns the result

>> Then you ought to be able to do

>> SELECT * FROM myudf_disdatabase(dbname,tsname) where you pass the database
>> and tablespace names as parameters

>> Phil Grainger

>> Grainger Database Solutions Ltd


>> -----Original Message-----
>> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David
>> Sent: 20 January 2010 15:30
>> To: [login to unmask email]
>> Subject: [DB2-L] Finding DB2 objects in questionable status...

>> Are there SYSIBM catalog/directory tables I can interrogate to get a list
>> of

>> database objects and their status (tables, indexes, SPs, triggers, etc)
>> that

>> are not in a normal, useable state? I know I can retrieve COPY PENDING
>> tables from SYSTABLESPACE STATUS but there are so many different
>> states an object can be in that may not be desirable.

>> Is there an IBM-supplied query that is run at upgrade or migration time
>> that

>> lists objects in questionable status? I would like to do this without
>> running -
>> DISPLAY DATABASE commands. Thanks in advance.

>> _____________________________________________________________________

>> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
>> http://IDUG.ORG/NA *
>> _____________________________________________________________________

>> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
>> presentations!
>> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
>> Warehouses, - among
>> many more categories of help waiting for you!
>> Whether you are an old hand or a DB2 newbie, we have presentations for
>> every
>> level.
>> _____________________________________________________________________

>> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
>> the home of IDUG's DB2-L

>> _____________________________________________________________________

>> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
>> http://IDUG.ORG/NA *
>> _____________________________________________________________________

>> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
>> presentations!
>> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
>> Warehouses, - among
>> many more categories of help waiting for you!
>> Whether you are an old hand or a DB2 newbie, we have presentations for
>> every level.
>> _____________________________________________________________________

>> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
>> the home of IDUG's DB2-L






====================
Martin Hubel
MHC Inc.
[login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

Fight the Right Fires - Let us show you how
====================


The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Marcel van Tunen

Re: Finding DB2 objects in questionable status...
(in response to Martin Hubel)
How about using DSNACCOX with these settings:

Querytype Restrict
Objecttype ALL
Checklevel 8

Met vriendelijke groet,
Marcel van Tunen

-----Original Message-----
From: Phil Grainger [mailto:[login to unmask email]
Sent: Wednesday, January 20, 2010 5:59 PM
Subject: Re: Finding DB2 objects in questionable status...

Unfortunately IBM don't externalize many of the "questionable statuses"
so a -DIS DATABASE is the only guaranteed way to find the oddities (like
RECP, AREORP, RBDP etc.)

If you want to do this from SQL, you could write a table UDF that does
the -DIS DATABASE and returns the result

Then you ought to be able to do

SELECT * FROM myudf_disdatabase(dbname,tsname) where you pass the
database and tablespace names as parameters

Phil Grainger

Grainger Database Solutions Ltd


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of David
Sent: 20 January 2010 15:30
To: [login to unmask email]
Subject: [DB2-L] Finding DB2 objects in questionable status...

Are there SYSIBM catalog/directory tables I can interrogate to get a
list of

database objects and their status (tables, indexes, SPs, triggers, etc)
that

are not in a normal, useable state? I know I can retrieve COPY PENDING
tables from SYSTABLESPACE STATUS but there are so many different states
an object can be in that may not be desirable.

Is there an IBM-supplied query that is run at upgrade or migration time
that

lists objects in questionable status? I would like to do this without
running - DISPLAY DATABASE commands. Thanks in advance.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free
technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free
technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

**********************************************************************
This transmission is confidential and must not be used or disclosed by anyone other than the intended recipient.
Neither Tata Steel Europe Limited nor any of its subsidiaries can accept any responsibility for any use or misuse of the transmission by anyone.

For address and company registration details of certain entities within the Corus group of companies, please visit
http://www.corusgroup.com/entities
**********************************************************************

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

[login to unmask email]

Re: Finding DB2 objects in questionable status...
(in response to Marcel van Tunen)
Take look at _www.recoverknowledge.com_ (http://www.recoverknowledge.com)
and you will find a tool that will give you everything you ever wanted to
know about questionable status. The ones that are mentions are all on one
screen.

Ed.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
** If you are going to attend only one conference this year, this is it!


** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L