When calling certain SQL procedures in DB2 Version 10.1 you may
encounter an unexpected SQL error:
In DB2 Version 10.1.0.2 a SQL1224N error may occur. Under DB2
Versions 10.1.0.0 or 10.1.0.1, you may see the following
unexpected error instead:
SQL0471N Invocation of routine "MYSCHEMA.MYPROC" failed due to
reason "1". SQLSTATE=55023
The procedure call may also trap in the insertTraceRecord/
topLevelCleanup functions.
For the errors or the trap to occur, the called SQL procedure
must satisfy the following conditions:
- The procedure must have originally been created under DB2
Version 8.2, then migrated to Version 9.1, 9.5, or 9.7, before
being
migrated to Version 10.1.
- The procedure contains at least one WITH RETURN cursor that is
not used in the routine and not even opened. In other words,
the
procedure declares the cursor, but does not use it in any other
statements. |
Drop and recreate the failing procedure in DB2 Version 10.1.
In order to identify additional routines that may be affected by
this problem, please run one of the following queries:
On little-endian platforms (e.g., Windows, Linux AMD64):
select R.routineschema, R.routinemodulename,
R.routinename, R.specificname
from syscat.routines R, sysibm.syscodeproperties CP
where R.implementation like 'db2pvm%'
and CP.lib_id = R.lib_id
and bitand(ascii(cast(substr(CP.sql_compiled_code, 53, 1)
as char(1) for bit data)), 4) <> 0
On big-endian platforms (e.g., AIX):
select R.routineschema, R.routinemodulename,
R.routinename, R.specificname
from syscat.routines R, sysibm.syscodeproperties CP
where R.implementation like 'db2pvm%'
and CP.lib_id = R.lib_id
and bitand(ascii(cast(substr(CP.sql_compiled_code, 56, 1)
as char(1) for bit data)), 4) <> 0
Drop and recreate each identified routine. |
