Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Unanswered: Count of records not matching with card in partitioned database

    Scenario: In a DB2 V8.1 logically partitioned database (4 partitions) on Windows.

    The count of the table is as follows:
    D:\>db2 "select count(*) from admin.state with ur"

    1
    -----------
    1450000

    1 record(s) selected.


    D:\>

    However, the card from syscat.tables is as follows:

    D:\>db2 "select substr(tabname, 1, 30), substr(tabschema,1,30), card, npages, fpages from syscat.tables where tabname like 'STATE%' with ur"

    1 2 CARD NPAGES FPAGES
    ------------------------------ ------------------------------ -------------------- ----------- -----------
    STATE ADMIN 40 8 8
    STATEMENTS SYSCAT -1 -1 -1

    2 record(s) selected.


    D:\>

    The db2nodes.cfg contents is as found below:

    0 pcidl PCIDL 0
    1 pcidl PCIDL 1
    2 pcidl PCIDL 2
    3 pcidl PCIDL 3



    How do 1450000 & 40 relate? Why do they not match? Am I missing something in a partitioned database environment?

    Thanks in advance.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The stats in the DB2 catalog are only updated after you run runstats.

    If the stats are -1, that means that runstats has never been run since the table was created.
    Last edited by Marcus_A; 07-19-06 at 04:51.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Ofcourse, runstats is done, as shown below:

    C:\>db2 "select stats_time from syscat.tables where tabname = 'STATE' with ur"

    STATS_TIME
    --------------------------
    2006-07-19-14.24.27.828000

    1 record(s) selected.


    C:\

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    In the db2 manuals, about RUNSTATS command :

    "The command collects statistics for a table on the database partition from which it is invoked. If the table does not exist on that database partition, the first database partition in the database partition group is selected.
    "
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Thanks for the reply.

    I have 1 coordinator node and 3 logical partitions on 1 single box.

    The partitioning key is on 'bill'. The group by on this key is as follows:
    Code:
    D:\>db2 "select bill, count(*) from admin.state group by bill with ur"
    
    BILL   2
    ----------- -----------
           8746          10
           7383        2830
           1918           5
           6530       40235
            200         960
            682      219305
           1910        2715
           2052     1182795
           9843        1145
    
      9 record(s) selected.
    
    D:\>
    I do not see how 40 is the card value for the above breakup on bill attribute.

    Thanks in advance.

Posting Permissions

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