469,929 Members | 1,811 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,929 developers. It's quick & easy.

querying from sysibm or syscat tables

Can I get the equivalent information for the following commands
using SYSCAT or SYSIBM tables.

db2 list tablespace containers for 14 show detail

db2 list history backup since 20061018 for dbname

I would like to use perl DBI to get the relevant information.

Oct 24 '06 #1
8 14279
dc********@aim.com wrote:
Can I get the equivalent information for the following commands
using SYSCAT or SYSIBM tables.

db2 list tablespace containers for 14 show detail

db2 list history backup since 20061018 for dbname

I would like to use perl DBI to get the relevant information.
There are administrative functions and views to get the information.
E.g.:
http://publib.boulder.ibm.com/infoce...c/r0022351.htm
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 24 '06 #2

Serge Rielau wrote:
dc********@aim.com wrote:
Can I get the equivalent information for the following commands
using SYSCAT or SYSIBM tables.

db2 list tablespace containers for 14 show detail

db2 list history backup since 20061018 for dbname

I would like to use perl DBI to get the relevant information.
There are administrative functions and views to get the information.
E.g.:
http://publib.boulder.ibm.com/infoce...c/r0022351.htm
this is for ver 9.

we are using 8.2.3

I found out the sql to get tablespace containers information

select * from
table(sysproc.snapshot_container('dbname',dbpartit ionnum))

Looks like there is no query to get db2 list history backup since
20061018 for dbname

also what is sql query to get information of

db2 list db directory

I want to avoid use "db2 " commands inside. Would prefer direct perl
DBI calls.

Oct 24 '06 #3
dc********@aim.com wrote:
db2 list db directory
I think you'll need to write a C UDF for that:
http://publib.boulder.ibm.com/infoce...c/r0001492.htm

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 24 '06 #4

dc********@aim.com wrote:
db2 list history backup since 20061018 for dbname
found one for this.

select * from table(admin_list_hist()) where operation = 'B';

will give the same information as list history backup

Oct 24 '06 #5
dc********@aim.com wrote:
Can I get the equivalent information for the following commands
using SYSCAT or SYSIBM tables.
The question doesn't even arise. Since the SYSCAT views are documented and
the SYSIBM tables are not (with the exception of SYSIBM.SYSDUMMY1), you
should never rely on the SYSIBM tables. Those may change at any time (and
they do change once in a while). If you rely on them and your application
breaks, its your problem.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Oct 25 '06 #6
Looks like ADMIN_LIST_HIST table function requires something to be
installed.
On some of the servers when I run the query against that table table
function
I get the following error:-

SQL0440N No authorized routine named "ADMIN_LIST_HIST" of type
"FUNCTION"
having compatible arguments was found. SQLSTATE=42884

The same query works fine on other servers. The version of Db2 is same.
8.2.3
However all failed boxes were 64bit Db2.

What needs to be done?

Oct 25 '06 #7
Ian
dc********@aim.com wrote:
Looks like ADMIN_LIST_HIST table function requires something to be
installed.
On some of the servers when I run the query against that table table
function
I get the following error:-

SQL0440N No authorized routine named "ADMIN_LIST_HIST" of type
"FUNCTION"
having compatible arguments was found. SQLSTATE=42884

The same query works fine on other servers. The version of Db2 is same.
8.2.3
However all failed boxes were 64bit Db2.

What needs to be done?
You probably didn't execute the 'db2updv8' command after applying a
fixpack.

db2updv8 -d SAMPLE (or whatever the name of your database is).

Oct 25 '06 #8
Ian wrote:
You probably didn't execute the 'db2updv8' command after applying a
fixpack.

db2updv8 -d SAMPLE (or whatever the name of your database is).
yes that was the reason.

thanks.

Oct 25 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Patrick Finnegan | last post: by
7 posts views Thread by Robert Stearns | last post: by
9 posts views Thread by Raquel | last post: by
reply views Thread by deangc | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.