|
Problem IC61578
|
Status: Closed |
BINDING NULL VALUE TO HOST VARIABLES MAY CAUSE -901 ERROR WITH MESSAGE "UNKNOWN TYPE ID 140". |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Binding null value to host variables may cause SQL0901N error
with message "Unknown type id 140".
The problem may happen when all of the following conditions are
met:
1. The database manager configuration parameter 'FEDERATED' is
set to 'YES'.
2. running a query on a nickname and host variable is used in
where-clause
3. host variable is bound with a null value in a scenario like
the following:
"colname=:hostvariable :ind" and the value of ind is negative
(indicating the value being bound is null)
4. the application is bound with REOPT ALWAYS options
The problem is the above scenario will cause the federation
compiler internally handle a query like: "where colname = NULL"
which will trigger error from federation compiler. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Federation users. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See the error description. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to v97fp1. *
**************************************************************** |
| Local Fix: |
Any of the following workarounds can bypass the problem:
1. Change predicate to something like " :vc :ind IS NOT NULL AND
COLUMN1 IS NOT NULL AND COLUMN1 = :vc ".
2. For applications using static embedded SQL, specify option
"REOPT ONCE" or "REOPT NONE" instead of "REOPT ALWAYS" while
binding the applications with the bind command. This can avoid
compiler from compiling the query with specific host variable
values, including NULL.
3. Simply remove the host variable indicator, and handle the
NULL case before execution of the query in the application code. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
|
| Solution |
This problem is first fixed in version 9.7 fixpack 1. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 19.06.2009
15.12.2009
15.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
