Dismiss
The IQ application will be unavailable on Sunday, November 24th, starting at 9:00am ET while we make system improvements. Access will be restored as quickly as possible.
Browse Prior Art Database

Method and apparatus to allow execution of Multi Row SQLs over DRDA without modifying SQLs in existing application programs

IP.com Disclosure Number: IPCOM000173286D
Original Publication Date: 2008-Jul-29
Included in the Prior Art Database: 2008-Jul-29

Publishing Venue

IBM

Abstract

Multi Row SQL statements were introduced to reduce the overhead of sending similar (homogenous) SQL statements one at a time to the server and receive the response. Now Database clients could buffer homogenous SQLs and send them one shot to the database server for processing and receive a single response for the entire chunk of SQL that was processed. Homogenous SQLs can be defined as SQL that modify the same table and the same subset of columns in that table , only differing in the values of the columns that they act on. 1. The client Application sends the SQLs to the Resource Adapter 2. The Resource adapter packages this SQL into a DRDA datastream and buffers it without sending it to the server. 3. Steps 1 and 2 are repeated, until all homogenous SQLs in the application are exhausted. 4. The Buffers are then sent at one shot to the server. 4. Server processes these buffers, and returns the SQLCA for the entire Homogenous SQL chunk , to the RA 5. The resource adapter sends back the SQLCA to the Application Program. The problem with this approach : Application programs had to be modified to allow the new multiple row SQLs in them . The new SQL format included the number of rows to be processed by the SQL statement . For example : In the old SQL version , an Insert SQL statement would look like : EXEC SQL INSERT INTO TABLE1 VALUES (:hv1,:hv2); The Multi Row version had to know the number of rows that need to be processed before hand, and thus introduced the new format : EXEC SQL INSERT INTO TABLE1 VALUES (:hv1,:hv2) FOR :xyz ROWS; (This statement belongs to DB2 for z/OS) Where :xyz is a host variable containing the actual number of Rows this statement intends to process. This means, that customers who want to avail the advantages of Multi Row Insert , will have to rewrite and rebuild their entire application suite. A) DB2 for z/OS has a solution for Multi Row Inserts where their insert statment intends to avail the advantage of Multi-Row SQL speed, looks like : INSERT INTO TABLE VALUES (:h1,:h2,h3) FOR :xyz ROWS Where, :xyz is the number of Rows that this insert statement intends the server to process. B) DB2 for Linux,Unix and Windows , Oracle & MySQL have a solution for Multi Row Inserts where the Insert statement that needs to avail the advantages of Multi Row SQL speed looks like : INSERT INTO TABLE VALUES (:h1,:h2,:h3),(:h4,:h5,h6),(:h7,:h8,:h9);