Skip to content

Instantly share code, notes, and snippets.

@forstie
Created April 26, 2021 11:41
Show Gist options
  • Save forstie/4db8cb3be4048be93475d816c15cff4d to your computer and use it in GitHub Desktop.
Save forstie/4db8cb3be4048be93475d816c15cff4d to your computer and use it in GitHub Desktop.
SQL DML includes the WITH NC clause to avoid having the data change participate in the transaction. SQL DDL does not include the WITH NC clause, but the savvy SQL user can leverage an AUTONOMOUS procedure to achieve the same behavior.
-- Assumption... the connection is setup to use COMMIT(*CHG)
-- =========================================================================
--
-- By default, SQL Data Definition Language (DDL) cannot use WITH NC
--
-- =========================================================================
CREATE TABLE QTEMP.T035 (FLD1 CHARACTER (9) CCSID 37 NOT NULL DEFAULT '',
FLD2 CHARACTER (30) CCSID 37 NOT NULL DEFAULT '',
FLD3 DECIMAL (3,0) NOT NULL DEFAULT 0,
FLD4 DECIMAL (3,0) NOT NULL DEFAULT 0,
FLD5 CHARACTER (10) CCSID 37 NOT NULL DEFAULT '',
FLD6 SMALLINT NOT NULL DEFAULT 0,
FLD7 SMALLINT NOT NULL DEFAULT 0,
FLD8 SMALLINT NOT NULL DEFAULT 0,
FLD9 SMALLINT NOT NULL DEFAULT 0,
FLD10 SMALLINT NOT NULL DEFAULT 0,
FLD11 SMALLINT NOT NULL DEFAULT 0,
FLD12 CHARACTER (2) CCSID 37 NOT NULL DEFAULT '',
FLD13 CHARACTER (2) CCSID 37 NOT NULL DEFAULT '',
FLD14 SMALLINT NOT NULL DEFAULT 0,
fld15 clob(1k) ccsid 37);
select count(*) as "pending_transaction?"
from qsys2.db_transaction_info
where JOB_NAME = qsys2.job_name and
(local_record_changes_pending = 'YES' or
local_object_changes_pending = 'YES');
-- 1 returned, there's a pending transaction
select *
from table(qsys2.job_lock_info('*'))
where lock_state like '*EXCL%'
order by object_type;
-- 3 rows returned... the QTEMP/T035 *FILE is part of the transaction
-- =====================================================================================================
--
-- If an Autonomous Procedure is used, SQL Data Definition Language (DDL) is implemented without commit
--
-- =====================================================================================================
create or replace procedure coolstuff.SQL_DDL_with_nc(p_stmt varchar(10000) for sbcs data )
autonomous
set option commit = *none, usrprf=*user, dynusrprf=*user
begin
execute immediate p_stmt;
end;
commit;
stop;
-- Grab a fresh connection...
call coolstuff.SQL_DDL_with_nc(
'CREATE TABLE QTEMP.T035 (FLD1 CHARACTER (9) CCSID 37 NOT NULL DEFAULT '''',
FLD2 CHARACTER (30) CCSID 37 NOT NULL DEFAULT '''',
FLD3 DECIMAL (3,0) NOT NULL DEFAULT 0,
FLD4 DECIMAL (3,0) NOT NULL DEFAULT 0,
FLD5 CHARACTER (10) CCSID 37 NOT NULL DEFAULT '''',
FLD6 SMALLINT NOT NULL DEFAULT 0,
FLD7 SMALLINT NOT NULL DEFAULT 0,
FLD8 SMALLINT NOT NULL DEFAULT 0,
FLD9 SMALLINT NOT NULL DEFAULT 0,
FLD10 SMALLINT NOT NULL DEFAULT 0,
FLD11 SMALLINT NOT NULL DEFAULT 0,
FLD12 CHARACTER (2) CCSID 37 NOT NULL DEFAULT '''',
FLD13 CHARACTER (2) CCSID 37 NOT NULL DEFAULT '''',
FLD14 SMALLINT NOT NULL DEFAULT 0,
fld15 clob(1k) ccsid 37)');
select count(*) as "pending_transaction?"
from qsys2.db_transaction_info
where JOB_NAME = qsys2.job_name and
(local_record_changes_pending = 'YES' or
local_object_changes_pending = 'YES');
-- 0 returned, there's no pending transaction
select *
from table(qsys2.job_lock_info('*'))
where lock_state like '*EXCL%'
order by object_type;
-- 0 rows returned... the QTEMP/T035 *FILE was created WITH NC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment