DB2 FOR LUW / DB2FORLUW / A50 - DB2

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.

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 10.5 Fix Pack 11 or higher                    *
****************************************************************

not known / see Local fix

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

05.12.2019
25.02.2020
25.02.2020