Make multiple DDL changes transactional

Why is it useful?
When complex changes are made in DDL and one of the changes fails, it can get pretty hard to revert the issue.

Who would benefit from it?
Implementing this idea would tremendously help database administrators.

How should it work?

Sample in Db2 v11.5.8.0
db2 "create schema user1"
db2 "create table user1.tab1 (col1 int, col2 int)"

# SQL changes are transactional (works fine)
db2 -td@ "begin insert into user1.tab1 values (1, 1); update user1.tab1 set col1 =2; end@"

# DDL changes are not transactional (DOES NOT WORK - MY IDEA, IMPLEMENT THIS)
db2 -td@ "begin alter table user1.tab1 drop column col2; alter table user1.tab1 add column col2 char(1); end@"

# Above command returns error:
# SQL0104N An unexpected token "alter" was found following "begin ". Expected
# tokens may include: "TRUNCATE". LINE NUMBER=1. SQLSTATE=42601

# But splitting above command into two commands works fine, but they are not transactional.
# If second command fails, first command is not rollbacked.
db2 "alter table user1.tab1 drop column col2"
db2 "alter table user1.tab1 add column col2 char(1)"

Now compare the same to PostgreSQL 15.4 database
psql -c "create schema user1"
psql -c "create table user1.tab1 (col1 int, col2 int)"

# SQL changes are transactional (works fine)
psql -c "begin; insert into user1.tab1 values (1, 1); update user1.tab1 set col1 =2; end"

# DDL changes are also transaction (also works fine!!!)
psql -c "begin; alter table user1.tab1 drop column col2; alter table user1.tab1 add column col2 char(1); end"

# In above command, if "add column" fails, "drop column" is ALSO reverted (rollbacked).

Needed By

Quarter

Post comment

Guest

Reply | Oct 25, 2023

@Ned, I tried your idea on Db2 v11.5.8.0 on Linux and Windows and the same result.

[db2inst1@db2 ~]$ db2 +c "create table MY.T (c1 int)"
DB20000I  The SQL command completed successfully.
[db2inst1@db2 ~]$ db2 +c "create table MY.T2 (c1 badtype)"
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  "BADTYPE" is an undefined name.  SQLSTATE=42704
[db2inst1@db2 ~]$ db2 +c "select * from MY.T"

C1
-----------

  0 record(s) selected.

[db2inst1@db2 ~]$ db2 +c "rollback work"
DB20000I  The SQL command completed successfully.
[db2inst1@db2 ~]$ db2 +c "select * from MY.T"
SQL0204N  "MY.T" is an undefined name.  SQLSTATE=42704

This works fine. But...
[db2inst1@db2 ~]$ db2 "update command options using c off"
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
[db2inst1@db2 ~]$ db2 "create table MY.T (c1 int)"
DB20000I  The SQL command completed successfully.
[db2inst1@db2 ~]$ db2 "create table MY.T2 (c1 badtype)"
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  "BADTYPE" is an undefined name.  SQLSTATE=42704
[db2inst1@db2 ~]$ db2 "select * from MY.T"

C1
-----------

  0 record(s) selected.

[db2inst1@db2 ~]$ db2 "rollback work"
DB20000I  The SQL command completed successfully.
[db2inst1@db2 ~]$ db2 "select * from MY.T"

C1
-----------

  0 record(s) selected.

But this doesn't work (last select should not be successful). The same issue on Linux and Windows.

Now only testing "update command options" command:


[db2inst1@db2 ~]$ db2 "update command options using c off"
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
[db2inst1@db2 ~]$ db2 list command options
 Option  Description                               Current Setting
 ------  ----------------------------------------  ---------------
   -c    Auto-Commit                               ON

And Auto-commit does not changes the setting. Did I perform something wrong or is this a bug?

Admin

Ned Whelan

Reply | Oct 24, 2023

Thank you for submitting this Idea! Upon review, the DDL is under transaction control as demonstrated by this example:

update command options using c off
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

create table MY.T (c1 int)
DB20000I  The SQL command completed successfully.

create table MY.T2 (c1 badtype)
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  "BADTYPE" is an undefined name.  SQLSTATE=42704

select * from MY.T

C1
-----------

  0 record(s) selected.


rollback work
DB20000I  The SQL command completed successfully.

select * from MY.T
SQL0204N  "MY.T" is an undefined name.  SQLSTATE=42704

Note that autocommit is enabled by default in the Db2 CLP. With autocommit enabled, the CLP issues a commit after each statement. The example above disables autocommit, alteratively, it can be disabled via the +c CLP option

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.

Please enter your email address

RELATED IDEAS

Make multiple DDL changes transactional