IBM Support

75 ways to demystify DB2: # 39: One popular reason of SQL1227 error when using db2look mimic output

Technical Blog Post


Abstract

75 ways to demystify DB2: # 39: One popular reason of SQL1227 error when using db2look mimic output

Body

I have been often asked about the reason of SQL1227N error when other DB2 DBA mates tried to update the catalog statistics for a table.

Most of you run the runstats for tables in a regular basis. And in very rare cases, running runstats results in unexpected performance output.
For some business system which is sensitive to just 0.1 second slowness, it would be blown up as a critical problem.
So some of our DBA warriors was planning to find the way of recovering the statistics before running runstats.

Generally speaking, it's not suggested to update the statistics manually unless it is on a testing system.

<Avoiding manual updates to the catalog statistics>
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0055097.html 

If we hit any query performance issue after runstats in their production systems, it's better to try to find out the root cause ,then take action accordingly, rather than updating the statistics manually. 

Nonetheless, I'm writing this blog to share the reason behind the SQL1227N error when we try to tweak the catalog with the db2look mimic output which was taken before runstats.
And I'm going to describe a probable workaround. So read this as just the reference to know why the error happens.

( Again, I would like to bring the bottom line that db2look mimic option was not designed for this usage to recover statistics after runstats.
This feature can be useful when mimicking a production database on a test system in order to examine query access plans.)


The most popular situation of SQL1227N is when we try to update the catalog from lower cardinality db2look output into higher cardinality current statistics.

For example,

1. We have a statistics 'A' currently.
2. As time goes by, more rows are inserted into a table.
3. Take db2look mimic 'A' before runstats. 
4. Running the runstats
5. Then recover previous statistics by running db2look mimic 'A'.

However, general fact is our data is mostly increasing so that we can get the error with this attempt.
For your straightforward look, let me simply reproduce this situation.

<Problem cases reproduction>

 

1. Creating test table and index
$ db2 "create table test (i int, c char(10))" 
$ db2 "create index itest on test(i)" 

2. Insert 3 rows

$ db2 "insert into test values(1, 'aaa')" 
$ db2 "insert into test values(2, 'bbb')" 
$ db2 "insert into test values(3, 'ccc')" 

3. Run the runstats and get the db2look mimic.
    Now we have the statistics with 3 rows.

$ db2 runstats on table $USER.test and indexes all 

$ db2look -d sample -m -t test -o test.ddl 
$ db2look -d sample -r -m -t test -o test_r.ddl 

4. Insert 2 more rows

$ db2 "insert into test values(4, 'ddd')" 
$ db2 "insert into test values(5, 'eee')" 

5. Then runstats.
    Now we have the statistics with 5 rows.
$ db2 runstats on table $USER.test and indexes all 

6. Trying manual update to recover the statistic of 3 rows using db2look mimic output.
   Then we meet the SQL1227N error twice.

$ db2 -tf test_r.ddl
....
SQL1227N  The catalog statistic "3" for column "CARD" is out of range for its 
target column, has an invalid format, or is inconsistent in relation to some 
other statistic. Reason Code = "6".  SQLSTATE=23521 
....
SQL1227N  The catalog statistic "3" for column "INDCARD" is out of range for 
its target column, has an invalid format, or is inconsistent in relation to 
some other statistic. Reason Code = "8".  SQLSTATE=23521 
....

 


How does it look like ? But it's not a defect at all.
This attempt is a kind of violation related to a dependency between catalog tables inside DB2.

Following is one of the way to revise the db2look output for running without the error.

NOTE. This is not the official way and may not give relief for all error cases.
            Just read this as the reference to understand the background. ( Test steps and opinions are my own. )

So let's do the reproduction in a different way.
The main idea is that updating the statistic manually after resetting current statistics.


<Workaround>
 

1. Creating test table and index
$ db2 "create table test (i int, c char(10))" 
$ db2 "create index itest on test(i)" 

2. Insert 3 rows

$ db2 "insert into test values(1, 'aaa')" 
$ db2 "insert into test values(2, 'bbb')" 
$ db2 "insert into test values(3, 'ccc')" 

3. Run the runstats and get the db2look mimic with -r option.
    Now we have the statistics with 3 rows.

$ db2 runstats on table $USER.test and indexes all 

$ db2look -d sample -r -m -t test -o test_0.ddl


4. Insert 2 more rows and runstats.
    Now we have the statistics with 5 rows.

$ db2 "insert into test values(4, 'ddd')" 
$ db2 "insert into test values(5, 'eee')" 
$ db2 runstats on table $USER.test and indexes all 
 

5. At this stage, get db2look mimic without -r option
 
$ db2look -d sample -m -t test -o test_1.ddl

 
6. copy test_1.ddl to test_2.ddl and delete SQLs except for following 2 sql
 
$ cat test_2.ddl

CONNECT TO SAMPLE;
 

UPDATE SYSSTAT.INDEXES
 
SET NLEAF=-1,
 
NLEVELS=-1,
 
FIRSTKEYCARD=-1,
 
FIRST2KEYCARD=-1,
 
FIRST3KEYCARD=-1,
 
FIRST4KEYCARD=-1,
 
FULLKEYCARD=-1,
 
CLUSTERFACTOR=-1,
 
CLUSTERRATIO=-1,
 
SEQUENTIAL_PAGES=-1,
 
PAGE_FETCH_PAIRS='',
 
DENSITY=-1,
 
AVERAGE_SEQUENCE_GAP=-1,
 
AVERAGE_SEQUENCE_FETCH_GAP=-1,
 
AVERAGE_SEQUENCE_PAGES=-1,
 
AVERAGE_SEQUENCE_FETCH_PAGES=-1,
 
AVERAGE_RANDOM_PAGES=-1,
 
AVERAGE_RANDOM_FETCH_PAGES=-1,
 
NUMRIDS=-1,
 
NUMRIDS_DELETED=-1,
 
NUM_EMPTY_LEAFS=-1,
 
INDCARD=-1
 
WHERE TABNAME = 'TEST' AND TABSCHEMA = 'LJSI97 ';
 

 
UPDATE SYSSTAT.COLUMNS
 
SET COLCARD=-1,
 
NUMNULLS=-1
 
WHERE TABNAME = 'TEST' AND TABSCHEMA = 'LJSI97 ';

 

7. If we run the test_2.ddl, we may encounter following error complaining a NULL value.
 

$ db2 -tf test_2.ddl

......
 
UPDATE SYSSTAT.INDEXES SET NLEAF=-1, NLEVELS=-1, FIRSTKEYCARD=-1, 
FIRST2KEYCARD=-1, FIRST3KEYCARD=-1, FIRST4KEYCARD=-1, FULLKEYCARD=-1, 
CLUSTERFACTOR=-1, CLUSTERRATIO=-1, SEQUENTIAL_PAGES=-1, 
PAGE_FETCH_PAIRS='', DENSITY=-1, AVERAGE_SEQUENCE_GAP=-1, 
AVERAGE_SEQUENCE_FETCH_GAP=-1, AVERAGE_SEQUENCE_PAGES=-1, 
AVERAGE_SEQUENCE_FETCH_PAGES=-1, AVERAGE_RANDOM_PAGES=-1, 
AVERAGE_RANDOM_FETCH_PAGES=-1, NUMRIDS=-1, NUMRIDS_DELETED=-1, 
NUM_EMPTY_LEAFS=-1, INDCARD=-1 WHERE TABNAME = 'TEST' AND TABSCHEMA = 
'LJSI97 '
 
DB21034E The command was processed as an SQL statement because it was 
not a
 
valid Command Line Processor command. During SQL processing it returned:

 
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=0,
 
TABLEID=7, COLNO=18" is not allowed. SQLSTATE=23502

 

8. We can check the relevant column COLNO=18 is "PAGE_FETCH_PAIRS" by running following SQL,
    and see that SQL0407N error was triggered because it is NOT NULL column.
 

SELECT C.TABSCHEMA, C.TABNAME,
 
   C.COLNAME
 
FROM SYSCAT.TABLES AS T,
 
    SYSCAT.COLUMNS AS C
 
WHERE T.TBSPACEID = 0
 
AND T.TABLEID = 7
 
AND C.COLNO = 18
 
AND C.TABSCHEMA = T.TABSCHEMA
 
AND C.TABNAME = T.TABNAME;

 

 
9. Copy test_2.ddl to test_3.ddl and edit like below
 
-- insert space value for not to be NULL
 
PAGE_FETCH_PAIRS='' ==> PAGE_FETCH_PAIRS = ' '
 

 
10. Execute test_3.ddl, then the statistics for the 'test' table will be reset.
 

$ db2 -tvf test_3.ddl
 
CONNECT TO SAMPLE
 

 
Database Connection Information
 

 
Database server    = DB2/AIX64 9.7.5
 
SQL authorization ID = LJSI97
 
Local database alias = SAMPLE
 

 

 
UPDATE SYSSTAT.INDEXES SET NLEAF=-1, NLEVELS=-1, FIRSTKEYCARD=-1, 
FIRST2KEYCARD=-1, FIRST3KEYCARD=-1, FIRST4KEYCARD=-1, FULLKEYCARD=-1, 
CLUSTERFACTOR=-1, CLUSTERRATIO=-1, SEQUENTIAL_PAGES=-1, 
PAGE_FETCH_PAIRS=' ', DENSITY=-1, AVERAGE_SEQUENCE_GAP=-1, 
AVERAGE_SEQUENCE_FETCH_GAP=-1, AVERAGE_SEQUENCE_PAGES=-1, 
AVERAGE_SEQUENCE_FETCH_PAGES=-1, AVERAGE_RANDOM_PAGES=-1, 
AVERAGE_RANDOM_FETCH_PAGES=-1, NUMRIDS=-1, NUMRIDS_DELETED=-1, 
NUM_EMPTY_LEAFS=-1, INDCARD=-1 WHERE TABNAME = 'TEST' AND TABSCHEMA = 
'LJSI97 '
 
DB20000I The SQL command completed successfully.
 

 
UPDATE SYSSTAT.COLUMNS SET COLCARD=-1, NUMNULLS=-1 WHERE TABNAME = 
'TEST' AND TABSCHEMA = 'LJSI97 '
 
DB20000I The SQL command completed successfully.
 

 
COMMIT WORK
 
DB20000I The SQL command completed successfully.

 

11. Then, run the db2look mimic output which was previously taken with 3 rows old statistics.
 

$ db2 -tvf test_0.ddl
 
CONNECT TO SAMPLE
 

 
Database Connection Information
 

 
Database server     = DB2/AIX64 9.7.5
 
SQL authorization ID = LJSI97
 
Local database alias = SAMPLE
 

 

 
UPDATE SYSSTAT.TABLES SET CARD=3, NPAGES=1, FPAGES=1, OVERFLOW=0, 
ACTIVE_BLOCKS=0 WHERE TABNAME = 'TEST' AND TABSCHEMA = 'LJSI97 '
 
DB20000I The SQL command completed successfully.
 

 
UPDATE SYSSTAT.COLUMNS SET COLCARD=3, NUMNULLS=0, SUB_COUNT=-1, 
SUB_DELIM_LENGTH=-1, AVGCOLLENCHAR=-1, AVGCOLLEN=5 WHERE COLNAME = 'I' 
AND TABNAME = 'TEST' AND TABSCHEMA = 'LJSI97 '
 
DB20000I The SQL command completed successfully.
 

 
UPDATE SYSSTAT.COLUMNS SET COLCARD=3, NUMNULLS=0, SUB_COUNT=-1, 
SUB_DELIM_LENGTH=-1, AVGCOLLENCHAR=-1, AVGCOLLEN=11 WHERE COLNAME = 'C' 
AND TABNAME = 'TEST' AND TABSCHEMA = 'LJSI97 '
 
DB20000I The SQL command completed successfully.
 

 
COMMIT WORK
 
DB20000I The SQL command completed successfully.
 

 
UPDATE SYSSTAT.INDEXES SET NLEAF=1, NLEVELS=1, FIRSTKEYCARD=3, 
FIRST2KEYCARD=-1, FIRST3KEYCARD=-1, FIRST4KEYCARD=-1, FULLKEYCARD=3, 
CLUSTERFACTOR=-1.000000, CLUSTERRATIO=100, SEQUENTIAL_PAGES=0, 
DENSITY=0, AVERAGE_SEQUENCE_GAP=0.000000, 
AVERAGE_SEQUENCE_FETCH_GAP=-1.000000, AVERAGE_SEQUENCE_PAGES=0.000000, 
AVERAGE_SEQUENCE_FETCH_PAGES=-1.000000, AVERAGE_RANDOM_PAGES=1.000000, 
AVERAGE_RANDOM_FETCH_PAGES=-1.000000, NUMRIDS=3, NUMRIDS_DELETED=0, 
NUM_EMPTY_LEAFS=0, INDCARD=3 WHERE INDNAME = 'ITEST' AND INDSCHEMA = 
'LJSI97 ' AND TABNAME = 'TEST' AND TABSCHEMA = 'LJSI97 '
 
DB20000I The SQL command completed successfully.
 

 
COMMIT WORK
 
DB20000I The SQL command completed successfully.
 

 
UPDATE SYSSTAT.FUNCTIONS SET ios_per_invoc= -1.0, insts_per_invoc= -1.0,
ios_per_argbyte= -1.0, insts_per_argbyte= -1.0, percent_argbytes= -1, 
initial_ios= -1.0, initial_insts= -1.0, cardinality= -1.0
 
DB20000I The SQL command completed successfully.
 

 
UPDATE SYSSTAT.FUNCTIONS SET ios_per_invoc=-1.000000, 
insts_per_invoc=-1.000000, ios_per_argbyte=-1.000000, 
insts_per_argbyte=-1.000000, percent_argbytes=-1, initial_ios=-1.000000,
initial_insts=-1.000000, cardinality=1 WHERE funcname = 
'EXPLAIN_GET_MSG' AND funcschema = 'SYSPROC ' AND specificname = 
'EXPLAIN_GET_MSG'
 
DB20000I The SQL command completed successfully.
 

 
UPDATE SYSSTAT.FUNCTIONS SET ios_per_invoc=-1.000000, 
insts_per_invoc=-1.000000, ios_per_argbyte=-1.000000, 
insts_per_argbyte=-1.000000, percent_argbytes=-1, initial_ios=-1.000000,
initial_insts=-1.000000, cardinality=1 WHERE funcname = 
'EXPLAIN_GET_MSG2' AND funcschema = 'SYSPROC ' AND specificname = 
'EXPLAIN_GET_MSG2'
 
DB20000I The SQL command completed successfully.
 

 
COMMIT WORK
 
DB20000I The SQL command completed successfully.

 

12. Then, let's see if optimizer refers the updated statistics by db2exrmt. 
 

 
$ db2 set current explain mode explain
 
$ db2 "select * from test"
 
SQL0217W The statement was not executed as only Explain information 
requests
 
are being processed. SQLSTATE=01604
 
$ db2 set current explain mode no
 
DB20000I The SQL command completed successfully.
 
$ db2exfmt -d sample -1 -g TIC -o test_exfmt.out
 

From test_exfmt.out file, we could see that DB2 referred the previous statistic with 3 rows.

 

$ cat test_exfmt.out
 

 
...snippet....
 

 
Objects Used in Access Plan:
 
---------------------------
 

 
       Schema: LJSI97
 
       Name:  TEST
 
       Type:  Table
 
                       Time of creation: 
       2012-07-10-17.01.04.023477
 
                       Last statistics update: 
       2012-07-10-17.11.27.585256
 
                       Number of columns:             2
 
                       Number of rows:                3
 
                       Width of rows:                 22
 
.... Snippet .....

 

 


Hope this help to understand the background of SQL1277N error case if you experienced the same error before.

And refer the following manual page to see more about updating catalog statistics and restriction.


Mimicking databases using db2look

* General rules for updating catalog statistics manually

* Rules for updating table and nickname statistics manually

 

Thank you for reading a bit long topic.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm13285741