DB2 FOR LUW / DB2FORLUW / 970 - DB2

Under the following sample scenario: 
- Table table1 contains sufficiently high number of integer 
values 
stored in a column of char/varchar data type of more than 
adequate size. 
 
SELECT null FROM table1 S LEFT JOIN table1 A ON RID ( A ) = S.v 
 
results in SQL0901N with stack trace resembling the following: 
 
sqloDumpEDU 
sqldDumpContext 
sqldDumpContext 
sqlrr_dump_ffdc 
sqlzeDumpFFDC 
sqlzeSqlCode 
sqlnn_erds 
sqlnn_erds 
sqlng_process_parse_tree_node 
sqlng_process_BF_node 
sqlng_walk_BF_chain 
sqlng_build_thread 
sqlng_build_TA_op 
sqlng_build_TA_op 
sqlng_process_scan_op 
 
The following statement results in SQL0100W and incorrect number 
of rows returned: 
 
SELECT null FROM table1 S LEFT JOIN table1 A ON RID ( A ) = CAST 
(S.v AS BIGINT)

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of RID function in predicate evaluation.               * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* SQL0901N or SQL0100W with incorrect number of results may be * 
* encountered during certain usage of the RID function in      * 
* predicate evaluation, as outlined in this APAR.              * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Install code fix associated with this APAR, or switch to     * 
* using the RID_BIT function in predicate evaluation.          * 
*                                                              * 
* To temporarily fix the SQL0100W issue with incorrect number  * 
* of rows returned (temporarily meaning for the current amount * 
* of rows in the base table), you can also increase the page   * 
* size of the tablespace, recreate the table and data and      * 
* rerun the problem SQL statement (note that this will NOT     * 
* permanently solve the problem, and eventually incorrect      * 
* results would be returned for the RID(A) = CAST(...)         * 
* statement again).                                            * 
****************************************************************

Install this APAR or switch to using the RID_BIT function in the 
 
predicate evaluation.

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
 

Install code fix associated with this APAR, or switch to using 
the RID_BIT function in predicate evaluation.

Switch to using the RID_BIT function in predicate evaluation, or 
to temporarily fix just the SQL0100W issue with incorrect number 
of rows returned (temporarily meaning for the current amount of 
rows in the base table), you can also increase the page size of 
the tablespace, recreate the table and data and rerun the 
problem SQL statement (note that this will NOT permanently solve 
the problem, and eventually incorrect results would be returned 
for the RID(A) = CAST(...) statement again).

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

02.02.2011
08.12.2011
08.12.2011