DB2 - Problem description
Problem IT31173 | Status: Closed |
AFTER UPGRADING TO V10.5 FP8 OR LATER, PROCEDURE THAT PERFORMS TRUNCATE TO CGTT MAY FAIL WITH SQL0204N. | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
In v10.5 FP8 or later, DEFAULT_SCHEMA is not used and the session authorization ID and CURRENT SCHEMA is used for unqualified reference in procedure that performs TRUNCATE to CGTT. Therefore, procedure fails with SQL0204N after upgrading to v105fp8 or later even if the procedure was able to run in previous versions Here is a repro of the problem (note that you need to create a database with DB2_COMPATIBILITY_VECTOR=ORA to enable PL/SQL syntax): set schema test DB20000I The SQL command completed successfully. create user temporary tablespace usertemp DB20000I The SQL command completed successfully. create global temporary table cgtt_tab (c1 int) in usertemp DB20000I The SQL command completed successfully. create table reg_tab(c1 int) DB20000I The SQL command completed successfully. create or replace procedure reg_dummy (myout out int) is begin truncate table reg_tab; myout := 1; end DB20000I The SQL command completed successfully. create or replace procedure cgtt_dummy (myout out int) is begin truncate table cgtt_tab; myout := 1; end DB20000I The SQL command completed successfully. call test.reg_dummy(?) Value of output parameters -------------------------- Parameter Name : MYOUT Parameter Value : 1 Return Status = 0 call test.cgtt_dummy(?) Value of output parameters -------------------------- Parameter Name : MYOUT Parameter Value : 1 Return Status = 0 set schema test2 DB20000I The SQL command completed successfully. call test.reg_dummy(?) Value of output parameters -------------------------- Parameter Name : MYOUT Parameter Value : 1 Return Status = 0 call test.cgtt_dummy(?) SQL0204N "TEST2.CGTT_TAB" is an undefined name. SQLSTATE=42704 In v105fp7, the final call statement works as we use the schema of the procedure (not the current schema which is 'TEST2') to resolve unqualified table names for static SQL. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 10.5 Fix Pack 11 or higher * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 05.12.2019 25.02.2020 25.02.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |