Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

Method and system to implement modified COMMIT SQL statement to perform COMMIT of a range of SQL operations in Logical Unit of Work (LUW)

IP.com Disclosure Number: IPCOM000199961D
Publication Date: 2010-Sep-22
Document File: 5 page(s) / 134K

Publishing Venue

The IP.com Prior Art Database

Abstract

Disclosed is a system and method to COMMIT or SAVE the range of work done in an LUW(Logical Unit of Work) by adding more to the SQL COMMIT statement. This allows user to SAVE part of the LUW. Hence it will reduce the number of COMMIT and ROLLBACK statements both of which are huge performance overhead and also gives the developer more flexibility while coding the application to meet the business need.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 47% of the total text.

Page 1 of 5

Method and system to implement modified COMMIT SQL statement to perform COMMIT of a range of SQL operations in Logical Unit of Work (LUW)

Disclosed is a system to COMMIT or SAVE the range of work in an LUW. With the existing database architecture the SQL COMMIT statement performs on the LUW(Logical Unit of Work) level and commits all the statements that constitute the LUW. A part of LUW cannot be committed with the existing database technology and to do this USER have to execute the opposite or reverse SQL statement.

Consider the below situations to understand the existing problem:

Case 1: Considering an application executing below SQL statements on the database, START
EXEC SQL CONNECT TO TEMPDB

EXEC SQL INSERT1

EXEC SQL UPDATE1
SAVEPOINT1

EXEC SQL INSERT2

EXEC SQL UPDATE2
SAVEPOINT2
:
:
EXEC SQL INSERT30
EXEC SQL UPDATE30
SAVEPOINT30

IF UPDATE30 fails then user has to ROLLBACK everything except INSERT15, UPDATE15 to INSERT20 to UPDATE20 to maintain the application business logic.

With the existing database facilities user has to embed pre-defined SAVEPOINT at the required place in the program. If user does not know till where SQL statement needs to ROLLBACK or the point till where the ROLLBACK needs to be done, then there is no such facility to dynamically determine in the current system.

Even with the SAVEPOINT embedded in application, user can ROLLBACK till the SAVEPOINTs. But user can not SAVE the SQL statements in between two SAVEPOINTs.

Case 2: Consider using interactive SQL facility to execute the below SQL statements: INSERT1
INSERT2
INSERT3
:

1

Page 2 of 5

INSERT20

UPDATE1
UPDATE2
UPDATE3
:
UPDATE20

If the user realizes that all the UPDATEs are not valid, What will be the next step? ROLLBACK the entire WORK and re-execute the 20 INSERT statements again. Where as USER cannot SAVE the range of SQL statements in a LUW. To address this problem, there is a need to COMMIT a range of SQL statements in a LUW to reduce the re-execution of statements.

Existing solutions:

Current database architecture has the concept of SQL SAVEPOINT. SQL SAVEPOINT statement is used to set a savepoint within a transaction. User should know the point till where the ROLLBACK has to be done in a LUW. When the point till where the ROLLBACK has to be done is not known then user cannot use the SAVEPOINT facility.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/do c/r0003271.htm

Drawback of existing solutions:
1) If the user do not know in advance till what point there is a need for ROLLBACK then SAVEPOINT cannot be used, i.e SAVEPOINTs should be predefined in an application.
2) SQL SAVEPOINT can ROLLBACK to defined savepoints but user cannot SAVE the SQL statements after the defined SAVEPOINTs.

The core idea of our invention:

Disclosed system and method is mainly intended to COMMIT or SAVE the range of work done in an LUW(Logical Unit of Work). To add more to the SQL COMMIT statement,which allows user to SAVE part of the LUW. Hence this...