Browse Prior Art Database

Optimizing SQL Cursor Operations to a Remote DBMS Using a Single Process Server

IP.com Disclosure Number: IPCOM000101308D
Original Publication Date: 1990-Jul-01
Included in the Prior Art Database: 2005-Mar-16
Document File: 2 page(s) / 78K

Publishing Venue

IBM

Related People

Crus, RA: AUTHOR [+5]

Abstract

In a Distributed Database environment supporting SQL requests, an application communicating with a local instance of the Database Management System (DBMS) (the "requester"), may require access to data managed by a remote DBMS (the "server"). For SQL cursor operations, communication between the requester and the server may be optimized by increasing the amount of information maintained for each cursor and by maintaining cursor state information at both the requester and the server. Initialization/termination processing at the server is optimized by utilizing a single surrogate process and an associated queue at the server.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 52% of the total text.

Optimizing SQL Cursor Operations to a Remote DBMS Using a Single Process Server

       In a Distributed Database environment supporting SQL
requests, an application communicating with a local instance of the
Database Management System (DBMS) (the "requester"), may require
access to data managed by a remote DBMS (the "server").  For SQL
cursor operations, communication between the requester and the server
may be optimized by increasing the amount of information maintained
for each cursor and by maintaining cursor state information at both
the requester and the server.  Initialization/termination processing
at the server is optimized by utilizing a single surrogate process
and an associated queue at the server.

      When the server recognizes that the last row has been
processed, processing is optimized by implicitly closing the cursor
and including an end of data response in the data transmitted back to
the requester.  Therefore, no additional transmission to the server
is required in order to close the cursor.

      Data retrieval via a cursor that does not allow any form of
update operation is optimized by accumulating the rows retrieved from
the database into blocks at the server and then transmitting the
blocks of rows back to the requester.  This optimization, called
blocked fetch, operates in two different fashions described in the
following paragraphs.

      In the first, called "continuous block fetch", a communication
path called a "conversation" is dedicated to a single cursor.  The
server fills a block, sends the block to the requester, and then
begins the process of retrieving data from the database and filling a
block again.  This technique is an iterative one that is repeated
until all of the data has been retrieved and sent to the requester.
At the requester, SQL FETCH operations using the cursor are sa...