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 2. Insert 3 rows $ db2 "insert into test values(1, 'aaa')" $ db2look -d sample -m -t test -o test.ddl $ db2 "insert into test values(4, 'ddd')" $ db2 -tf test_r.ddl |
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 |
7. If we run the test_2.ddl, we may encounter following error complaining a NULL value.
$ db2 -tf test_2.ddl |
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
|
11. Then, run the db2look mimic output which was previously taken with 3 rows old statistics.
$ db2 -tvf test_0.ddl
|
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 |
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
Thank you for reading a bit long topic.
UID
ibm13285741