|
Problem IC78033
|
Status: Closed |
INSERT TAKES A LONG TIME TO COMPILE WHEN INSERTING INTO A TABLE WITH AUTO-GENERATED and BIT STRING COLUMNS |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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.
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'); |
| Problem Summary: |
****************************************************************
* 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. *
**************************************************************** |
| Local Fix: |
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. |
| available fix packs: |
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
|
| Solution |
Problem has been fixed in V9.7FP5. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 10.08.2011
08.02.2012
08.02.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
