Hi All,
I tried searching the forums, but couldn't find any results. I'm new to DB2 (mostly work in SQL Server 2005), but I need some help. I would like to profile a database to obtain the following information:
- Table Name
- Column Name
- Data Type
- Number of records
- Number of Values (no NULLS)
- Number of Unique values
- Min Value
- Max Value
- Min Length
- Max Length

I saw that in SYSIBM.SYSCOLUMNS, I can obtain Table Name, Column Name, and Data Type but I was hoping there might be a sys table that holds the other information. If not, how would I create a cursor? I have something that worked in Sybase SQL, but doesn't port over to DB2 syntax.

Thanks in advance for all the help.
- jay

name - columns and type can be retrieved from systables - syscolumns
info about the data - only by executing some sql like
select count(*) : nbr of rows
select count(distinct... for unique values....
max(xx) min(xx)

Most of this information is available in syscat.columns, sysstat.columns and sysstat.coldist.

But this information is not 'current', but reflects the status of the data when RUNSTATS was last run.

DB2 Information Centre for your version of the product will give description of the columns in each of the above views.

Cheers
Sathyaram

Get a catalog poster

DB2 Version 8 Catalog poster courtesy of IBM

Or get ERWin and point it at the database

Cheers Brett

@op , the link is for zOS db2 v8. The catalog tables/views do change .. So make sure you are using the right version ... If you are not sure what the column value means, you have to look at the documentation

HTH

Sathyaram

If you are using DB2 for LUW, then you should not use the SYSIBM tables. Use the SYSCAT views instead. The are documented in the appendix of SQL Reference Vol 1.

Use SYSIBM tables in good health. SYSCAT views are for those who moved to DB2 from "Query Manager", OS/2, Windows, etc. and have not the capacity to remember SYSIBM names. When you grow up and migrate to DB2 on z/OS, you will not find any SYSCAT there. So get ahead of the game and learn SYSIBM names.

P.S. Where is Marcus today? I miss him

The point is that the SYSIBM tables in DB2 LUW are not documented and are not an official interface (except for SYSIBM.SYSDUMMY1). So if you build on that and DB2 changes something in those tables (which can happen at any time, even at run-time as much as the documentation is concerned), it is your fault and not DB2's. Therefore: use the SYSCAT view on DB2 LUW and use the SYSIBM tables on DB2 z/OS. In short: only use APIs that are documented.

Thanks for all the responses. I'm using DB2 UDB via DBArtisan 8.0.1

What I forgot to mention earlier is that I want to output the counts/max/min in a table.

Ex. ID | TBCreator| TableName | FieldName | DataType | RecordCount| ValueCount | UniqueCount | Max | Min

I created a table that currently has: ID (Auto-number), TBCreator, TableName, FieldName, DataType. I'm assuming that I could run a LOOP statement to cycle through the ID field and perform the necessary counts/max/min on each table/column by concatenating TBCreator and TableName and using that to call the table.

I am here.

The SYSCAT views are documented as the Catalog Views in the appendix of the SQL Reference Vol 1, whereas the SYSIBM tables are not documented. That alone is sufficient reason why you should use the catalog views instead of tables on DB2 LUW. Obviously, that is not the case for DB2 z/OS, where the SYSIBM tables are documented as the Catalog Tables.

Oh, excuse me! I forgot that you don't read documentation. Never Mind.