DB2 12 for z/OS - Two Highly Requested SQL Enhancements

DB2 12 for z/OS - Two Highly Requested SQL Enhancements

Meg Bernal, Chris Crone, IBM SVL Lab

DB2 12 introduces a number of new SQL and SQL PL related enhancements.  Two of the SQL related enhancements have been requested by many customers are Piece-wise DELETE, and the ability to MERGE two tables.  In the first of this two-part article, we will discuss the new DELETE syntax that enables customers to delete large numbers of rows from a table without severely impacting the system.  

Piece-wise delete modification

Database Administrators (DBAs) may receive a request from the business to delete data from a table (or view).  The delete request has the potential of affecting many rows that qualify the search condition.  The deletion of large amounts of rows may cause locking issues with other applications or processes.   To delete the data, DBAs requested an SQL syntax change to an existing SQL statement in lieu of having to write an application program.  In many customer shops, deploying an application would require the DBA to follow a change control process which, at times, can be cumbersome and time-consuming.  

To mitigate the effect of locking large number of rows, or having lock escalation occur, DB2 12 has been enhanced to allow the FETCH FIRST n ROWS ONLY clause to be specified on a searched DELETE statement.

Let’s say a DBA receives a request to clean up a history table by removing all entries that are older than five years.  To mitigate the number of rows locked at any point in time, a COMMIT is issued after a pre-determined number of fetches, such that you would stay below the lock escalation number and therefore still have the table available for other activity. 

Prior to DB2 12, a DBA might write an autonomous transaction to purge the history table from rows having a timestamp older than five years.  The autonomous transaction would perform the following:

  1. Declare a cursor with the WITH HOLD option to fetch rows
  2. Open the cursor
  3. Perform a loop to:
    1. Fetch from the table
    2. Perform a positioned delete to delete the row
    3. After N number of rows, commit the data thereby freeing up locks

 An autonomous transaction might be an attractive option because the autonomous transaction can issue COMMIT.  While other stored procedures can COMMIT ON RETURN, only an autonomous SQL Procedure can issue COMMIT inside the procedure body.  Additionally, a DBA would deploy the autonomous transaction by going through the change control process once.  After the initial deployment of the autonomous transaction, data could be deleted from different tables simply by calling the autonomous transaction with a different SELECT statement.

 In the positioned delete example that follows, the following four (4) parameters are passed into the autonomous transaction:

  1. P_SELECT_STMT - A SELECT statement that contains a predicate that qualifies rows having timestamps older than five years
  2. P_TABLE_NAME - The table name of the table having its rows deleted
  3. P_NUMER_OF_LOOPS – The total number of rows that have timestamps older than five (5) years
  4. P_COMMIT_INTERVAL – The number of fetches to perform prior to issuing the COMMIT statement

 The figure below demonstrates the positioned delete solution.

CREATE PROCEDURE PURGE_ROWS_SP(IN P_SELECT_STMT VARCHAR(1000),
IN P_TABLE_NAME VARCHAR(128),
IN P_NUMBER_OF_LOOPS INTEGER,
IN P_COMMIT_INTERVAL INTEGER)
LANGUAGE SQL
AUTONOMOUS
P1: BEGIN

DECLARE v_DYNAMIC VARCHAR(1000);
DECLARE v_SQL VARCHAR(1000);
DECLARE v_DELETE VARCHAR(1000);
DECLARE v_counter integer DEFAULT 0;
DECLARE v_temp integer DEFAULT 0;
DECLARE v_at_end SMALLINT DEFAULT 0;
DECLARE v_target VARCHAR(100);

DECLARE not_found CONDITION for SQLSTATE '02000';
DECLARE C1_CUR CURSOR WITH HOLD FOR v_DYNAMIC;
DECLARE CONTINUE HANDLER for not_found SET v_at_end = 1;

SET v_SQL = P_SELECT_STMT || ' FOR UPDATE ';
SET v_DELETE =
'DELETE FROM '|| P_TABLE_NAME || ' WHERE CURRENT OF C1_CUR ';

PREPARE v_DYNAMIC FROM v_SQL;

OPEN C1_CUR;

fetch_loop: LOOP

FETCH C1_CUR INTO v_target;
IF v_at_end <> 0 THEN
LEAVE fetch_loop;
END IF;

EXECUTE IMMEDIATE v_DELETE;

SET v_temp = MOD(v_counter,P_COMMIT_INTERVAL);

IF v_temp = 0 THEN
COMMIT;
END IF;

SET v_counter = v_counter + 1;
IF v_counter >= P_NUMBER_OF_LOOPS THEN
LEAVE fetch_loop;
END IF;

END LOOP fetch_loop;

CLOSE C1_CUR;

END P1!

Starting in DB2 12, the application developer may use the piece-wise delete syntax to delete N number of rows and issue a commit to free locks.  The stored procedure is much simpler to code in DB2 12 as there is no longer a need to declare a cursor.   In the piece-wise delete solution, the four (4) parameters passed into the autonomous transaction remain the same except the P_SELECT_STATMENT now contains the predicate that qualifies rows having timestamps older than five years.

In the simplified solution for DB2 12, the autonomous transaction would only need to perform the following:

  1. Perform a loop to:
    1. Perform a piece-wise delete to delete N number of rows
    2. Commit the data thereby freeing up locks

 The figure below demonstrates the piece-wise delete solution.  Notice the FETCH FIRST ROWS ONLY clause attached to the searched delete statement.

CREATE PROCEDURE FFNR_PURGE_ROWS_SP(IN P_SELECT_STMT VARCHAR(1000),
IN P_TABLE_NAME VARCHAR(128),
IN P_NUMBER_OF_LOOPS INTEGER,
IN P_COMMIT_INTERVAL INTEGER)
LANGUAGE SQL
AUTONOMOUS
P1: BEGIN

DECLARE v_DELETE VARCHAR(1000);
DECLARE v_counter integer DEFAULT 0;

SET v_DELETE =
'DELETE FROM '|| P_TABLE_NAME || '
WHERE ' || P_SELECT_STMT
|| ' FETCH FIRST ' || P_COMMIT_INTERVAL ||' ROWS ONLY';

delete_loop: LOOP

EXECUTE IMMEDIATE v_DELETE;
COMMIT;

SET v_counter = v_counter + P_COMMIT_INTERVAL;
IF v_counter >= P_NUMBER_OF_LOOPS THEN
LEAVE delete_loop;
END IF;

END LOOP delete_loop;

END P1!

 

As you can see, DB2 12 greatly simplifies data maintenance when deleting rows are required.  By leveraging DB2 11’s AUTONOMOUS transaction support, it is relatively simple to DELETE data while avoiding concurrency issues with other applications that may need access to the table. 

 

In part two of this posting, we will explore another often-requested enhancement to the MERGE statement.

 

 

Recent Stories
Db2 11.1 Enhancements Since GA

DB2 12 for z/OS - Two Highly Requested SQL Enhancements

PBR RPN explained