Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Profiling Database

    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

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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)
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Get a catalog poster

    DB2 Version 8 Catalog poster courtesy of IBM

    Or get ERWin and point it at the database
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by gqdesi View Post
    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
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Feb 2009
    Posts
    114
    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
    Last edited by db2dummy1; 11-06-09 at 00:28.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by db2dummy1 View Post
    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.
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Nov 2009
    Posts
    2
    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.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2dummy1 View Post
    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
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •