July 10, 2006
DB2 stores information about database objects in what it calls the System Catalog. There are a number of views in the system catalog you can use to obtain metadata about your database such as columns, key relationships, schemas, stored procs, etc.
Today, I was working on a report where I wanted to display statistics about a number of staging files we use in our applications. I wanted to list things like number of records, number of columns, column names, etc. Things like the number of columns were easy to do just by selecting count(*) from the source table. To get the number of columns, though, I didn't want to use a hack like selecting a single record and getting the column list from that. What I wanted to do was query the system catalog. Unlike SQL Server, Oracle, and just about every other DB platform out there, documentation for DB2/400 is rather scarce.
After a lot of googling, I found a DB2/400 reference that had the information on the system catalog I needed. For those interested, the full documentation set can be found here. As IBM documentation has a tendency to disappear, here's the SYSIBM.SQLCOLUMNS library/view I used to query column information from the system catalog:
SELECT count(*) as totalRows
WHERE TABLE_NAME = 'yourTable'
If you want to get a list of all columns, along with a lot of additional info about each column, just do a SELECT * instead of the count.
There are several other useful SQL views you can use:
SQLCOLPRIVILEGES, SQLFOREIGNKEYS, SQLPRIMARYKEYS, SQLPROCEDURECOLS, SQLPROCEDURES, SQLSCHEMAS, SQLSPECIALCOLUMNS, SQLSTATISTICS, SQLTABLEPRIVILEGES, SQLTABLES, SQLTYPEINFO, SQLUDTS