Looking for DB2 code to do what Oracle was able to do 2

[DeepDiverMom]

I must generate a management report (today) from data gathered from each database server in the company. We have a preponderance of Oracle servers (50+) against which I have successfully run the following code. I must also gather similar statistics for our DB2, Sybase, SQL Server, and MySQL instances. I am having difficulty finding appropriate "across-the-db-server" data dictionary views for these remaining four vendors' db server environments that I was able to access for Oracle. I believe it is my relative lack of familiarity with these other vendors' db engines that is standing in my way. Could anyone please propose SQL code for the DB2 SQL environment that can achieve what I did in Oracle?

My specific need is for a single line of output (per db server instance/invocation) that displays
a) Host (machine) name upon which this db server runs,
b) DB Instance name (if applicable/available),
c) DB version for this instance,
d) Number of DB logins/users for this db instance,
e) Total number of tables+indexes across the entire db instance (i.e., across all schemas), and finally
f) Total amount of space that all tables and indexes consume collectively on this database instance.

Note: I have used '^' to separate values to facilitate reading results into an Excel spreadsheet as delimited values.)

Here is the code that I used successfully against each of our Oracle instances.

select	host_name||'^'||instance_name||'^'||version||'^'||
	users||'^'||cnt||'^'||storage_space
from	 v$instance
	,(select count(*) cnt,sum(bytes) storage_space from dba_segments)
	,(select count(*) users from dba_users)
/

durango^durprod^9.2.0.4^107^23894^5878298816

My colleagues here tell me that the non-Oracle databases do not have the same across-the-db-instance-query capabilities that Oracle has. I would hate to find out that they are right. Can anyone please help me prove them all wrong with a working piece of DB2 SQL code? If the "nay-sayers" are correct, please advise me of that sad fact, as well.

There is certainly appropriate gratitude at the end of this for anyone who can help me resolve this issue.

Thanks,

Becca

 

[juliane26]

Hi Becca,

well, you wont be able to get this for DB2 like that, and I guess you use the Oracle Management Server (I am not an Oracle specialist so much, but as I heard this is like a single administration instance for many database instances)?
DB2 has a system catalog per database and not per instance.
Still, here might be some helpful answers (to be executed from command line).
With a suitable shell script your output can be generated as well.

a)hostname
b) db2ilist
c) db2licm -l (licence and version) or db2level (fixpak and build information) (I am not sure what you exactly need)
Only from V8 onwards it is possible to have different fixpak levels for multiple instances, before that was not possible. Check out, whether you are using that option at all. On Windows that is not possible at all, all have to have the same level.

d) db2 list applications
e) unfortunately for each db:
>>db2 connect to dbname
>>db2 select count(*) from syscat.tables
>>db2 select count(*) from syscat.indexes

f) I am not sure here, you just need the total space??
>> db2 connect to dbname
>> db2 list tablespaces show detail
you can see also storage information. Sum up total pages multiplied by page size.

Hmmm, coming to think of it I think there were some rumours about UDTFs delivering also this kind of information from Version 8 onwards enabling you using SQL. Still you will have to connect to each database, that cannot be avoided.


Juliane

 

[blom0344]

Becca,

Look into the following free DB2 tool:

http://chuzhoi_files.tripod.com

It gives you some alternatives to using the command line statements ...

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[DeepDiverMom]

Thank you both, Juliane and Ties, for your contributions. Your responses were helpful. They confirmed DB2's capabilities and limitations insofar as my particular need is concerned. For that, I am very grateful...for that you each deserve a Purple Star.

Becca