Autocommit does not work with truncate table...

[jsiebrand]

By default with sqlalchemy has autocommit enabled. However, this is not working with the ibm_db_sa interface when a "truncate table command is executed"

Test case:

sqlalchemy.version
'1.3.23'

#Create the engine
engine = create_engine(sqla_url, pool_size=10, max_overflow=20)
conn = engine.connect()

#Create the table if it does not exist and truncate to ensure that it is empty
print("Creating table if not exists")
conn.execute("create table if not exists johannes.mbike2(manufacturer varchar(50), model varchar(50), year int)")
db2pd("-db sample -wlock -lock")

Creating table if not exists

RETURN CODE None
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:09:08 -- Date 2021-08-30-14.32.05.398276

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:09:08 -- Date 2021-08-30-14.32.05.398290

Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID

---

print("Truncating table")
conn.execute("truncate table johannes.mbike2 immediate")
db2pd("-db sample -wlock -lock")

---

Truncating table

RETURN CODE None
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:09:14 -- Date 2021-08-30-14.32.11.443112

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:09:14 -- Date 2021-08-30-14.32.11.443126

Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
0x00007F83EAC22980 3 00000500033D000060E95EFBC3 CatCacheLock ..S G 3 2 0 0x00000000 0x40000000 0
0x00007F83EAC22B80 3 0000050003003D000000000052 RowLock ..X G 3 2 0 0x00200000 0x40000000 0
0x00007F83EAB6BF00 3 0200000000000000000000006D ExtentMoveLock ..S G 3 1 0 0x00200100 0x40000000 0
0x00007F83EAC22C00 3 010000000100000001004055D6 VarLock ..S G 3 1 0 0x00000000 0x40000000 0
0x00007F83EAC22480 3 00000500000000000000000054 TableLock .IX G 3 4 0 0x00202000 0x40000000 0
0x00007F83EABC8180 3 02001100000000000000000054 TableLock ..Z G 3 1 0 0x00202000 0x40000000 0

From this there is a row lock left in the SYSIBM.SYSTABLES table:
select substr(tabschema,1,6) as schema, substr(tabname,1,10) as table, tbspaceid, tableid
from syscat.tables where tableid = 5 and tbspaceid =0

SCHEMA TABLE TBSPACEID TABLEID

---

SYSIBM SYSTABLES 0 5

1 record(s) selected.

and an exclusive lock on the base table:
db2 "select substr(tabschema,1,9) as schema, substr(tabname,1,10) as table, tbspaceid, tableid from syscat.tables where tableid =17 and tbspaceid =2"

SCHEMA TABLE TBSPACEID TABLEID

---

JOHANNES MBIKE2 2 17

However, when autocommit is explicitly declared for the engine as a work around, then the truncate table command does not hold any locks:

print("Truncating table")

engine = create_engine(sqla_url, pool_size=10, max_overflow=20)
conn = engine.connect().execution_options(autocommit=True)
conn.execute("truncate table johannes.mbike2 immediate")
db2pd("-db sample -wlock -lock")

Truncating table
RETURN CODE None
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:19:56 -- Date 2021-08-30-14.42.53.906285

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 01:19:56 -- Date 2021-08-30-14.42.53.906298

Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID

[imavo]

Your symptom has nothing to do with sqlalchemy, or autocommit, or ibm_db_sa, or ibm_db, or python.

Instead, Db2-LUW is working as designed , specifically for the statementTRUNCATE ...IMMEDIATE. The IMMEDIATE clause causes Db2-LUW to process the statement immediately and it cannot be undone and is therefore not impacted by the autocommit setting.

Study the documentation for more details.
https://www.ibm.com/docs/en/db2/11.5?topic=statements-truncate

This ticket should be closed.