|
Problem IC65586
|
Status: Closed |
SET SESSION AUTHORIZATION/SET SESSION_USER with string constant inside SQL routine causes trap |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When you use SET SESSION AUTHORIZATION or SET SESSION_USER with
a
string constant inside of an SQL routine, compiling the routine
will cause your instance to terminate abnormally.
For example:
CREATE PROCEDURE SP_TEST
LANGUAGE SQL
BEGIN
SET SESSION_USER = 'UNIUSR'; -- will cause a trap when
compiled
END
CREATE PROCEDURE SP_TEST
LANGUAGE SQL
BEGIN
SET SESSION_USER = UNIUSR; -- will compile successfully
END
At db2diag.log, you will see a similar messages as:
---------------------------
2010-01-13-13.41.51.639516+540 I26466G1291 LEVEL: Severe
PID : 15456 TID : 2950687632 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-7 APPID:
*LOCAL.db2inst1.100113044121
AUTHID : DB2INST1
EDUID : 19 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary
logging func, probe:0
MESSAGE : RDS UCINTFC: pStatementText =
DATA #1 : Hexdump, 168 bytes
0xAD86ABA0 : 4352 4541 5445 2050 524F 4345 4455 5245 CREATE
PROCEDURE
0xAD86ABB0 : 2053 505F 5445 5354 0A4C 414E 4755 4147
SP_TEST.LANGUAG
0xAD86ABC0 : 4520 5351 4C0A 4245 4749 4E0A 2053 4554 E
SQL.BEGIN. SET
0xAD86ABD0 : 2053 4553 5349 4F4E 5F55 5345 5220 3D20
SESSION_USER =
0xAD86ABE0 : 2755 4E49 5553 5227 3B20 2D2D 2054 6865
'UNIUSR'; -- The
0xAD86ABF0 : 2069 6E73 7461 6E63 6520 6973 2064 6F77
instance is dow
0xAD86AC00 : 6E65 642E 0A2D 2D20 5345 5420 5345 5353 ned..--
SET SESS
0xAD86AC10 : 494F 4E5F 5553 4552 203D 2055 4E49 5553 ION_USER
= UNIUS
0xAD86AC20 : 523B 2020 202D 2D20 5468 6520 696E 7374 R; --
The inst
0xAD86AC30 : 616E 6365 2069 7320 6E6F 7420 646F 776E ance is
not down
0xAD86AC40 : 6564 2E0A 454E 440A ed..END.
---------------------------
And the associated trap file, in this case
15456.19.000.trap.txt,
could contain below stack trace:
-----------------------
<snip>
<POFDisassembly>
_ZNK9sqlnq_pid6myqncpEv + 0x0006
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xB4696D1E : 8B426483F8127507
</POFDisassembly>
<StackTrace>
--Frame--- ------Function + Offset------
0xAFDF3C54 _ZNK9sqlnq_pid6myqncpEv + 0x0006
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xAFDF3F30 _Z18sqlnq_set_registerPP8stknode_i10actiontypePhP3loc
+ 0x1e46
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xAFDF3F84 _Z12sqlnp_smactnP8sqlnp_cbi + 0x087c
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xAFDF3FDC _Z12sqlnp_parserP8sqlnp_cb + 0x0707
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xAFDF40F8 _Z10sqlnp_mainP12sqlnq_stringsPP9sqlnq_qur + 0x03fd
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xAFDF560C
_Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sq
lrr_cmpl_enviiPP9sqlnq_qur
+ 0x09bb
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xAFDF5638
_Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sq
lrr_cmpl_env
+ 0x0024
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xAFDF6290
_Z17sqlra_compile_varP8sqlrr_cbP14sqlra_cmpl_envPhitiiiiiP14SQLP
_LOCK_INFOP16sqlra_cached_varPiPy
+ 0x0605
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xAFDF6C34
_Z14sqlra_find_varP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idj
jPhjthP14sqlra_cmpl_env15sqlra_fill_modePiiS8_iiiS8_P14SQLP_LOCK
_INFOPP16sqlra_cached_varS8_Pyb
+ 0x03ae
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
0xAFDF7358 _Z13sqlra_get_varP8sqlrr_cbiibPbS1_ + 0x05db
(/home/db2inst1/sqllib/lib32/libdb2e.so.1)
<snip>
----------------------- |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 UDB Version 9.7. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error description field for more information. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 FixPack 2. *
**************************************************************** |
| Local Fix: |
Basically none but removing the single quotes may be a
workaround. |
| available fix packs: |
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 6 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 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 |
Problem was first fixed in DB2 UDB Version 9.7 FixPack 2. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC67532
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 14.01.2010
16.06.2010
16.06.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
