SQL Select for DB2

[Andrew21210]

A long time ago (before my time) someone ran an SQL Select on our 5.5 TSM server to get the attached table information.

My question is: Is there any type of SQL select for a 6.3 server (DB2) that will get me the same type of table? I've looked all over the place and I can't find anyone who has written one, which makes me think that it can't be done...

 

[LED888]

To get a list of all tables available for querying in the database
TSMDB1 enter the following command:
select tabname from syscat.tables where tabschema=’TSMDB1’ and type=’V’

Good Luck,
Sias

 

[Andrew21210]

To get a list of all tables available for querying in the database
TSMDB1 enter the following command:
select tabname from syscat.tables where tabschema=’TSMDB1’ and type=’V’

Good Luck,
Sias

Where am I running this from? The Server command line?

 

[chad_small]

You have to run it from a UNIX or DOS command line.

DB2 connect TSMDB1

$ db2 connect to TSMDB1
$ db2 "select tabname from syscat.tables where tabschema='TSMDB1' and type='V' "

results are 154 tables in TSM 7.1

 

[Andrew21210]

You have to run it from a UNIX or DOS command line.

DB2 connect TSMDB1

$ db2 connect to TSMDB1
$ db2 "select tabname from syscat.tables where tabschema='TSMDB1' and type='V' "

results are 154 tables in TSM 7.1

Thanks Chad. That gives me the table names. Any idea how I get the objects for each table name (similar to the file I uploaded)? My problem is that I don't know who ran the original select on the 5.5 server and how they did it. Perhaps it was several selects and then they just dumped everything into a spreadsheet.

 

[chad_small]

This should do it.

$ db2 "select tabname, colname, colno, TYPENAME, LENGTH from syscat.columns where tabschema='TSMDB1' "

 

[Andrew21210]

This should do it.

$ db2 "select tabname, colname, colno, TYPENAME, LENGTH from syscat.columns where tabschema='TSMDB1' "

Thanks Chad. That's exactly what I needed!