Browse Prior Art Database

Accommodating WITH HOLD Cursors in a Transaction- bounded Processing Environment

IP.com Disclosure Number: IPCOM000014001D
Original Publication Date: 2001-Feb-01
Included in the Prior Art Database: 2003-Jun-19
Document File: 5 page(s) / 114K

Publishing Venue

IBM

Abstract

This disclosure shows a method to accommodate WITH HOLD cursors in an environment where the processing units, or database agent, of a relational database are only associated with a specific application for the length of a transaction (i.e. a unit of work). After the end of a transaction, the agent may be re-assigned to a different application which means that any information from the previous application stored with that agent is lost. This causes a problem for those SQL statements whose lifespan exceeds that of a transaction, in particular it causes a problem for open WITH HOLD cursors. For these statements, it is important that the application information and statement information not be lost at the end of a transaction; it needs to be stored in such a way that another, different agent can pick up processing where the first one left off. This problem can be addressed by organizing the application SQL working memory into two distinct areas: an Application Shared SQL Workspace and an Agent Private SQL Workspace. The Application Shared SQL Workspace will contain statements that live beyond the current unit of work while the Agent Private SQL Workspace will contain the others. This approach also requires the introduction of an Application SQL Context that maps which SQL statements are being executed by the application and in which workspace the statements are being executed.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 27% of the total text.

Page 1 of 5

  Accommodating WITH HOLD Cursors in a Transaction- bounded Processing Environment

This disclosure shows a method to accommodate WITH HOLD cursors in an environment where the processing units, or database agent, of a relational database are only associated with a specific application for the length of a transaction (i.e. a unit of work). After the end of a transaction, the agent may be re-assigned to a different application which means that any information from the previous application stored with that agent is lost. This causes a problem for those SQL statements whose lifespan exceeds that of a transaction, in particular it causes a problem for open WITH HOLD cursors. For these statements, it is important that the application information and statement information not be lost at the end of a transaction; it needs to be stored in such a way that another, different agent can pick up processing where the first one left off. This problem can be addressed by organizing the application SQL working memory into two distinct areas: an Application Shared SQL Workspace and an Agent Private SQL Workspace. The Application Shared SQL Workspace will contain statements that live beyond the current unit of work while the Agent Private SQL Workspace will contain the others. This approach also requires the introduction of an Application SQL Context that maps which SQL statements are being executed by the application and in which workspace the statements are being executed.

 Application SQL Context

Agent Private SQL Workspace

Application

Application Shared SQL Workspace

DB2 Agent

An SQL Workspace acts as a "warehouse" of executable forms of SQL statements, called sections, that have been initialized and are ready for use. A workspace also serves as a working area where these same sections can be executed. The Application Shared SQL Workspace is part of the application information that is maintained for each application connected to the database

1

[This page contains 2 pictures or other non-text objects]

Page 2 of 5

and is associated with one and only one application for the life of that application's connection to the database. The Agent Private SQL Workspace is part of the agent information and is associated with one and only agent for the life of that agent. As an agent is transferred from application to application, it brings its private workspace with it and acquires access to the shared workspace of the application it is currently servicing. The contents of an SQL Workspace do not contain any request context information such as package, section entry, or application information. The only items to be found inside an SQL Workspace are sections for both dynamic and static SQL statements that have been initialized and are ready for execution or are in the process of being executed. It is the Application SQL Context which associates individual sections in the workspace to specific request contexts (i.e. packages and section entries) as used by the appli...