Results 1 to 15 of 15
  1. #1
    Join Date
    May 2009
    Posts
    10

    Unanswered: db2look to simulate prod failed

    I am using db2look to get a bunch of related table statistics from a production databaes, and update a test database using the generated statements. Both the production database and test database don't have detailed index statistics.

    During this process, only one update statement failed, which is to update SYSSTAT.TABLES for a specific table X. It failed with the message:
    The catalog statistis "963" for column "CARD" is out of its target column,has an invalid format or is inconsistent in relation to some other statistics. (23521, -1227)

    This is strange, becaues:
    1) The update statements for other tables and their columns statistis succeeded. (i.e. the statements that update SYSSTAT.TABLES and SYSSTAT.COLUMNS)
    2) The update statements for table X's column statistis (SYSSTAT.COLUMNS) succeeded. So I know that "CARD" value 963 of the table is greater than any column's COLCARD.

    I suspect the relationship between "CARD" and "NPAGE", "FPAGE" might be off. Another posibility is that the some configuration of prod and test is different. However, i don't know how to confirm this. Could you please help?


    I am using db2 8.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by wondering
    I am using db2 8.
    Out of service. But I would apply FP17 if you haven't already.
    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
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If by any chance your production database is partitioned and the test one is not, keep in mind that statistics are collected per partition.

    If this is not the case, I would first try making sure that the statistics in production is fully up to date.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2009
    Posts
    10
    Thanks, the production database is fully up to date, and it is not partitioned (neither is the test database).

    There should be a relationship between CARD/NPAGES/FPAGES, such as a page can contain # of records, so CARD records should at least occupy #NPAGES, etc? I am just guessing.

  5. #5
    Join Date
    May 2009
    Posts
    10
    Anyone can help me on this?

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Do you have a reason code for -1227 (sql1227n)?

    SQL1227N The catalog statistic "<value>" for column
    "<column>" is out of range for its target column, has
    an invalid format, or is inconsistent in relation to
    some other statistic. Reason Code = "<code>"


    Can you execute the update statement from the command line to see if it gives you the RC.

  7. #7
    Join Date
    May 2009
    Posts
    10
    I am discovering something insteresting here. The prod database's statistics is updated with runstats command without "WITH DISTRIBUTION ON COLUMNS" clause, this can be proved by running the blow command on the prod database:

    select * from SYSSTAT.COLUMNS where TABNAME = '***' AND TABSCHEMA = '***'
    (*** is a placehold for real values, don't want to disclose my company's information :-))

    This sql returns empty.

    The db2look however returns update statements to update SYSSTAT.COLUMNS with valid values.

    So it seems, db2look executes runstats on the prod database but doesn't write them into the prod tables.

    But this doens't make sense, after all, i want to simulate the prod database.

    1) is there a way to tell db2look to not do more than it should?
    2) is there a way to delete information from SYSSTAT.COLUMNS?

    Thank you for your help.

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    select * from SYSSTAT.COLUMNS

    should not return empty if table is there. Regardless how stats are collected.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    May 2009
    Posts
    10
    But how can I explain the empty result? I fired the sql statement via CLP, it shows "0 record(s) selected."

    If I select from SYSCAT.COLUMNS, the result is not empty.

    Could you expand on this?

    Thanks.

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    WOW buddy. you are confusing me. does select * from SYSCAT.COLUMNS gives you data or not. Show the command and its output.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    May 2009
    Posts
    10
    select * from SYSCAT.COLUMNS where TABNAME = 'XXXX' AND TABSCHEMA = 'XXXX'

    The result set is not empty.

    select * from SYSSTAT.COLUMNS where TABNAME = 'XXXX' AND TABSCHEMA = 'XXXX'
    The result set is empty, even after I have executed RUNSTATS ON TABLE "XXXX"."XXXX"
    WITH DISTRIBUTION ON ALL COLUMNS
    on this table, the result set is still empty!

    But the db2look on this table gives update statements, such as:
    UPDATE SYSSTAT.COLUMNS
    SET COLCARD=45056,
    NUMNULLS=0,
    SUB_COUNT=-1,
    SUB_DELIM_LENGTH=-1,
    AVGCOLLENCHAR=-1,
    HIGH2KEY='338960',
    LOW2KEY='10987',
    AVGCOLLEN=4
    WHERE COLNAME = 'XXXX' AND TABNAME = 'XXXX' AND TABSCHEMA = 'XXXX';

    So it seems that the db2look command first does a runstats command on the table but without writing the statistics to the related system tables and output them.

    Have I made myself clear? Please continue to help me. Thank you very much.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    db2look does not update statistics. I'd suggest you take a closer look at how you spell 'XXXX' and 'XXXX' in each of the statements.
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    As Nick said db2look does nothing to change stats. Check your name.

    btw. I just realized that you were talking about two diff schemas SYSCAT and SYSSTAT. but that does not changes anything. SYSSTAT should have info on the table and its columns regardles if you have indexes or has collected statistics.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    May 2009
    Posts
    10
    I run the sql "select * from SYSSTAT.COLUMNS" and grepped the result, there is no record for the XXXX table!

    SYSSTAT should contain the records even for an empty table. I've proved that by creating an empty table and search for it in the SYSSTAT.COLUMNS.

    Who can solve the mystery?

  15. #15
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Are you sure that your XXXX is a table??? Please run

    select type from syscat.tables where tabschema = 'XXX' and tabname = 'XXXX'

    and show the output.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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