The InnovationQ application will be updated on Sunday, May 31st from 10am-noon ET. You may experience brief service interruptions during that time.
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



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);