DB2 FOR LUW / DB2FORLUW / 970 - DB2

When trying to execute an INSERT statement to a table having 
auto-generated and bit string columns, it is noticed that it 
takes a long time to complete. 
 
If capture stacks during the insert statement compilation, the 
following stack repeats. db2trc -perfcount shows 
sqlnr_check_redundant_prd() takes long time. 
 
UDT_colequiv_exp 
sqlnr_check_redundant_prd 
sqlnr_constpu_action 
sqlnr_comp 
sqlnr_seq 
sqlnr_rcc 
sqlnr_perqun_action 
sqlnr_comp 
sqlnr_priority 
sqlnr_rcc 
sqlnr_allqun_action 
sqlnr_comp 
sqlnr_seq 
sqlnr_rcc 
sqlnr_start_action 
sqlnr_comp 
sqlnr_seq 
sqlnr_rcc 
sqlnr_exe 
 
This happens if the following conditions are met: 
 
  o It is up to DB2 to generate a value for generated column 
  o Client is trying to insert any value to binary column 
  o Database collation is not IDENTITY 
 
For example: 
 
create table N ( 
    i int generated by default as identity (starting from 0, 
increment by 1), 
    c char(1) for bit data); 
 
insert into N (c) values (x'00');

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 V9.7 users.                                              * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When trying to execute an INSERT statement to a table having * 
* auto-generated and bit string columns, it is noticed that it * 
* takes a long time to complete.                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* If the insert statement can be modified, explicitly specify  * 
* a                                                            * 
* value for the generated column. For example,                 * 
* insert into N (i, c) values (1, x'00');                      * 
*                                                              * 
* If the insert statement cannot be modified, create a         * 
* database                                                     * 
* using IDENTITY collation and move data over to the new       * 
* database.                                                    * 
****************************************************************

If the insert statement can be modified, explicitly specify a 
value for the generated column. For example, 
insert into N (i, c) values (1, x'00'); 
 
If the insert statement cannot be modified, create a database 
using IDENTITY collation and move data over to the new database.

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem has been fixed in V9.7FP5.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

10.08.2011
08.02.2012
08.02.2012

9.7.FP5