SQL AND SCHEMA. 1

[pmking]

Hello..

Can somebody please assist me here?

My goal is to have a sql script that will parse a DB2 schema and help identify the following:

NOTE: I HAVE ONE (WORKING SQL SCRIPT) FOR ORACLE AND IT WORKS, BUT WHAT SQL SCRIPTING DIFFERENCES WILL I HAVE TO ACCOUNT FOR, FOR DB2 UDB.

My goal is to have a sql script that will parse a DB2 schema and help identify the following inside a DB2 dB:

------ Distinct Object Types and their Count in the Schema


------ Distinct Column Data Types and their Count in the Schema



------ Tables With No Primary Key or Unique Index in the Schema



------ Tables with no PK or unique index and more than 32 columns



------ Tables Defined with Rowsize > 24k in the Schema



------ Tables With CLOB, BLOB, LONG, or LONG RAW Columns in the Schema



------ Tables With Columns of Special Datatypes in the Schema

------ IOT, Cluster, or Object Tables - in the Schema

------ Types of Constraints on the Tables in the Schema


------ Tables Defined with Triggers in the Schema

Thanks in advance for the assistance..

 

[blom0344]

Well, for starters, the DBA views used to screen the schema data are different (name and contents) between DB2 and Oracle. So, think in terms of a total rewrite of your script..

Ties Blom

 

[pmking]

blom0344,

Thanks for the note.

Do you know where I can read on DBA views to screen for name and contents?

Below is a snipit of the Oracle sql script that I mentioned. So with what is below and the naming conventions in DB2, how would I change, or what syntax would be changed with regard to DB2 and using the script below as somewhat of a template. Basically to find out what I need from a schema. There are certain SELECT options that will get info I need. Such as what is in RED below, is object type in Oracle the same as Object Type in DB2? If not, what is the name I should SELECT on to get the same info? Again, I am so grateful for all the HELP!

PLEASE NOTE ALL ITEMS IN RED BELOW.. To Sum up my question, what would be the DB2 equvelant to the Oracle Select Options I have marked in red below?

----------start of snipit------------------

SELECT '------ Distinct Object Types and their Count in the Schema: '||:b0
FROM dual;
[red]SELECT object_type, count(*) total[/red]
FROM all_objects
WHERE OWNER = :b0
GROUP BY object_type
/


SELECT '------ Distinct Column Data Types and their Count in the Schema: '||:b0
FROM dual;
[red]SELECT data_type, count(*) total[/red]
FROM all_tab_columns
WHERE OWNER = :b0
GROUP BY data_type
/


SELECT '------ Tables With No Primary Key or Unique Index in the Schema: '||:b0
FROM dual;
[red]SELECT distinct(table_name)[/red]
FROM all_tables
WHERE owner = :b0
MINUS
[red](SELECT obj1.name
FROM SYS.user$ user1,
SYS.user$ user2,
SYS.cdef$ cdef,
SYS.con$ con1,
SYS.con$ con2,
SYS.obj$ obj1,
SYS.obj$ obj2
WHERE user1.name = :b0
AND cdef.type# = 2
AND con2.owner# = user2.user#(+)
AND cdef.robj# = obj2.obj#(+)
AND cdef.rcon# = con2.con#(+)
AND obj1.owner# = user1.user#
AND cdef.con# = con1.con#
AND cdef.obj# = obj1.obj#[/red]
UNION
[red]SELECT idx.table_name
FROM all_indexes idx[/red]
WHERE idx.owner = :b0
AND [red]idx.uniqueness[/red] = 'UNIQUE')
/


----------end of snitpit----------------------

 

[blom0344]

you should look in the syscat/sysibm schemas for metadata tables like syscolumns,sysobjects,systables,sysindexes,systriggers,sysreferences.

It is all there, but I am not going to write it all out here..


Ties Blom

 

[pmking]

Can somebody else assist me in getting started with my DB2 sql script. All I need is a starting point.

blom0344--Thanks for the response and suggestion to look in the syscat and sysibm schemas for metadata tables.
I wasn't asking you to write it all for me, just assist with a starting point that went beyond reading a document or manual page..

Thanks to all who assist...

 

[pmking]

blom0344...

THANKS SO MUCH!! I just searched on syscat and was WOW'd by all the info available, I think all the info I need is there. You get a star.

Again thanks so much..