|
Problem IC90202
|
Status: Closed |
WHEN DDL ON AN UNDEFINED OBJECT IS EXECUTED, THE TRANSACTION IS OPENED EVEN THOUGH THE DDL ITSELF FAILS WITH SQL0204N. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When DDL on an undefined object is executed, the transaction is
opened even though the DDL itself fails with SQL0204N. It's
because DB2 logs a record indicating that DDL has just started
and then checks if the object exists.
> db2 list applications
Auth Id Application Appl. Application Id
DB # of
Name Handle
Name Agents
-------- -------------- ----------
--------------------------------------------------------------
-------- -----
DB2INST7 db2bp 9049 *LOCAL.db2inst7.130214004413
HADRDB 1
> db2pd -d hadrdb -trans | egrep 'Address| 9049 '
Address AppHandl [nod-index] TranHdl Locks
State Tflag Tflag2 Firstlsn Lastlsn
SpaceReserved LogSpace TID AxRegCnt GXID
ClientUserID ClientWrkstnName
ClientApplName ClientAccntng
0x070000004007F900 9049 [000-09049] 18 0
READ 0x00000000 0x00000000 0x0000000000000000
0x0000000000000000 0 0
0x0000000112FD 1 0 n/a
n/a n/a
n/a
> db2 +c "drop table notab"
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0204N "DB2INST7.NOTAB" is an undefined name. SQLSTATE=42704
> db2pd -d hadrdb -trans | egrep 'Address| 9049 '
Address AppHandl [nod-index] TranHdl Locks
State Tflag Tflag2 Firstlsn Lastlsn
SpaceReserved LogSpace TID AxRegCnt GXID
ClientUserID ClientWrkstnName
ClientApplName ClientAccntng
0x070000004007F900 9049 [000-09049] 18 2
WRITE 0x00000000 0x00000000 0x0000000005457214
0x0000000000000000 74 486
0x0000000112FD 1 0 n/a
n/a n/a
n/a
This could lead a system to 'Transaction Log Full' situation if
it's run with AUTOCOMMIT OFF and never committed. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users on HADR with ROS *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to V97FP9 *
**************************************************************** |
| Local Fix: |
Run COMMIT after DDL. |
| available fix packs: |
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
|
| Solution |
Fixed in V97FP9 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 13.02.2013
05.05.2014
05.05.2014 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP9
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.9
|
 |
| 9.7.0.9
|
|
