|
Problem IC72326
|
Status: Closed |
SQL1585N, SQLSTATE=54048 MAY OCCUR FOR EXTREMELY LONG SQL STATEMENTS WITH TOO MANY COLUMN EXPRESSIONS INVOLVED. |
| product: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problem description: |
In some cases, the compilation of an extremely long SQL
statement containing too many column expressions and predicates
using parameter marker or host variable comparisons may result
in an SQL1585N, SQLSTATE=54048 error.
When parameter markers or host variables are used with a between
predicate, such as "COL between :hv1 and :hv2" or "COL >= :hv1
and COL <= :hv2", the query rewrite phase will add a check to
ensure that :hv1 <= :hv2. As a result of adding this condition,
you may hit a limitation in the optimizer that results in the
SQL1585N error.
When constants are used instead of the parameter markers or host
variables, we don't hit the limitation in the optimizer.
This limitation may not always result in SQL1585N.
You can identify if this check is added by collecting an EXPLAIN
of the query and using the db2exfmt tool to examine the details.
The following steps describes one method to collect this
information:
1. db2 set current explain mode explain
2. db2 -tvf <file_containing_query>
3. db2 set current explain mode no
4. db2exfmt -d <database_name> -1 -g -o <output_filename>
In the output_filename file, under the "Optimized Statement"
section, the following indicates that this condition was added:
(SELECT Q1.$C0
FROM (VALUES 0) AS Q1
WHERE (:? <= :? SELECTIVITY 1.000000)) AS Q2
and the "Access Plan" will include operators similar to the
following:
0.0111111
NLJOIN
( 4)
1455.04
192.4
/-------+-------\
1 0.0111111
TBSCAN FILTER
( 5) ( 6)
9.563e-05 896.185
0 118.483
| |
1 ...
TABFNC: SYSIBM
GENROW
Q1
The hv1<=hv2 condition is one example where this limitation
applies. This fix addresses the general limitation in the
optimizer where the query contains
SELECT Q1.$C0
FROM (VALUES 0) AS Q1
The fix for this APAR is enabled by setting the
DB2_EXTENDED_OPTIMIZATION registry variable, and there are two
parts to it. It can be enabled using any of the following:
1. To enable both parts:
db2set DB2_EXTENDED_OPTIMIZATION=NLJNORD,NO_HVCHECK_ALL
2. To enable one portion of the change:
db2set DB2_EXTENDED_OPTIMIZATION=NLJNORD
db2set DB2_EXTENDED_OPTIMIZATION=NO_HVCHECK_ALL
Note: if you already have DB2_EXTENDED_OPTIMIZATION set, then
you will need to include it when setting this new keywords; for
example,
db2set DB2_EXTENDED_OPTIMIZATION=YES,NLJNORD,NO_HVCHECK_ALL
The "NLJNORD" value enables the optimizer improvement.
The "NO_HVCHECK_ALL" value disables the injection of the :hv1 <=
:hv2 condition, resulting in the same behavior as when the user
supplies constants instead of host variables or parameter
markers. |
| Problem Summary: |
Problem Description:
SQL1585N, SQLSTATE=54048 MAY OCCUR FOR EXTREMELY LONG SQL
STATEMENTS WITH TOO MANY COLUMN EXPRESSIONS INVOLVED.
Problem Summary:
In some cases, the compilation of an extremely long SQL
statement containing too many column expressions and predicates
using parameter marker or host variable comparisons may result
in an SQL1585N, SQLSTATE=54048 error.
When parameter markers or host variables are used with a between
predicate, such as "COL between :hv1 and :hv2" or "COL >= :hv1
and COL <= :hv2", the query rewrite phase will add a check to
ensure that :hv1 <= :hv2. As a result of adding this condition,
you may hit a limitation in the optimizer that results in the
SQL1585N error.
When constants are used instead of the parameter markers or host
variables, we don't hit the limitation in the optimizer.
This limitation may not always result in SQL1585N.
You can identify if this check is added by collecting an EXPLAIN
of the query and using the db2exfmt tool to examine the details.
The following steps describes one method to collect this
information:
1. db2 set current explain mode explain
2. db2 -tvf <file_containing_query>
3. db2 set current explain mode no
4. db2exfmt -d <database_name> -1 -g -o <output_filename>
In the output_filename file, under the "Optimized Statement"
section, the following indicates that this condition was added:
(SELECT Q1.$C0
FROM (VALUES 0) AS Q1
WHERE (:? <= :? SELECTIVITY 1.000000)) AS Q2
and the "Access Plan" will include operators similar to the
following:
0.0111111
NLJOIN
( 4)
1455.04
192.4
/-------+-------\
1 0.0111111
TBSCAN FILTER
( 5) ( 6)
9.563e-05 896.185
0 118.483
| |
1 ...
TABFNC: SYSIBM
GENROW
Q1
The hv1<=hv2 condition is one example where this limitation
applies. This fix addresses the general limitation in the
optimizer where the query contains
SELECT Q1.$C0
FROM (VALUES 0) AS Q1
The fix for this APAR is enabled by setting the
DB2_EXTENDED_OPTIMIZATION registry variable, and there are two
parts to it. It can be enabled using any of the following:
1. To enable both parts:
db2set DB2_EXTENDED_OPTIMIZATION=NLJNORD,NO_HVCHECK_ALL
2. To enable one portion of the change:
db2set DB2_EXTENDED_OPTIMIZATION=NLJNORD
db2set DB2_EXTENDED_OPTIMIZATION=NO_HVCHECK_ALL
Note: if you already have DB2_EXTENDED_OPTIMIZATION set, then
you will need to include it when setting this new keywords; for
example,
db2set DB2_EXTENDED_OPTIMIZATION=YES,NLJNORD,NO_HVCHECK_ALL
The "NLJNORD" value enables the optimizer improvement.
The "NO_HVCHECK_ALL" value disables the injection of the :hv1 <=
:hv2 condition, resulting in the same behavior as when the user
supplies constants instead of host variables or parameter
markers. |
| Local Fix: |
Users could try replacing the parameter markers or host
variables with constants, or use REOPT ALWAYS.
Users could also try to change the optimization level since it
may cause the optimizer to choose a different plan that may
satisfy the order earlier and thus avoid SQL1585N. |
| available fix packs: |
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
|
| Solution |
Problem is first fixed in Version 9.1 Fix Pack 10 |
| Workaround |
Users could try replacing the parameter markers or host
variables with constants, or use REOPT ALWAYS.
Users could also try to change the optimization level since it
may cause the optimizer to choose a different plan that may
satisfy the order earlier and thus avoid SQL1585N. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 02.11.2010
13.06.2011
13.06.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.1.FP10
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.10
|
 |
