DB2 implemented multiple-row fetch/insert in V8, this new feature can fetch or insert a batch of rows with one time of interaction with DB2, which can improve the performance of retrieving or inserting multiple rows significantly.
In RBD version 9, we implemented the multiple-row feature which can generated COBOL codes and Java codes to utilize the features provided by database to improve performance.
This blog introduces the new EGL syntax and typical usage of the multiple-row feature.
1.Multiple-Row implementation prior EGL v9
Prior to EGL v9, multiple-row fetch in EGL was supported like below:
employees employeeRecord [0]{RowSetSize = 10}; get employees; for ( i int from 1 to employees.getSize() ) SysLib.writeStdout( employees[i].fName ); End |
The get employees; did times of fetch to retrieve all rows in table Employee, and each fetch operation retrieved 10 rows.
Multiple-row insert was supported like below:
employees employeeRecord [0]{RowSetSize = 10}; //The omitted codes below appends 50 elements to employees array. … add employees; |
The add employees; did 5 times of insert to table Employee, and each insert operation inserted 10 rows.
The drawbacks of the implementation are:
- It’s not suitable to process big result set, because the GET statement will retrieve all rows into a dynamic array at one time. For example, if there are 1000 rows conformed to the SQL, then the 1000 rows will be retrieved into the dynamic array at one time, this needs many memory. Much worse case is that after the first row is processed, no more rows need to be processed, that means 999 rows are retrieved for nothing
- Can not retrieve diagnostic data if there are some errors in the fetch.
2.Multiple-Row implementation in EGL v9
2.1.Open Statement
The Open statement supports dynamic array of SQLRecord as the target. If the target record of the Open statement is a dynamic SQLRecord array, that means it will use multiple-row fetch in the following Get Position statement.
Changes from previous support:
- Clause in the Open statement supports dynamic array variable.
- The Open statement supports annotation “rowsetsize”.
For example:
Case 1:
employees Employee[0]{rowsetsize=10}; Open resultset1 for employees; |
Case 2:
employees Employee[0]{rowsetsize=10}; Open resultset1 with #sql{ select eID, uName, PASSWORD, fName, lName, office, sex, EMail from EMPLOYEETEST } for employees; |
Case 3:
Open resultset1 {@rowsetsize=10} with #sql{ select eID, uName, PASSWORD, fName, lName, office, sex, EMail from EMPLOYEETEST }; //the rowset size is 10. |
Case 4:
employees Employee[0]{rowsetsize=10}; Open resultset1 {@rowsetsize=20} with #sql{ select eID, uName, PASSWORD, fName, lName, office, sex, EMail from EMPLOYEETEST } for employees; //the rowset size is 20, instead of 10. |
2.2.Get PositionOption Statement
The Get postionOption statement supports dynamic SQLRecord array as the target. The Get postionOption statement empties the dynamic array, and populates it with new row set. The INTO clause can be dynamic array variables or static array variables.
Changes from previous support:
- The Get PositionOption Statement supports dynamic array variable as the target.
- The Get PositionOption Statement supports dynamic array variables or static array variables as the targets of INTO clause.
Case 1:
employees Employee[0]{rowsetsize=10}; Open resultset1 scroll with #sql{ select eID, uName, PASSWORD, fName, lName, office, sex, EMail from EMPLOYEETEST } for employees; Get Next employees; //this empty array employees, and populate the next 10
// rows to array employees, up to 10 rows. If there are //only 3 rows in the returned result set, only 3 rows are //populated into array employees, and employees.getSize() //returns 3. Get Previous employees; Get First employees; Get Last employees; Get absolute(3) employees; Get relative(3) employees; |
Case 2: Dynamic array variables as the target of INTO statement
employees Employee[0]{rowsetsize=10}; Open resultset1 scroll with #sql{ select eID, uName from EMPLOYEETEST } for employees; eIDs INT[0]{}; uNames CHAR(20)[10]{}; Get Next employees into eIDs, uNames; //this empty array eIDs, and populate
//the next 10 rows to array eIDs, up to 10 rows. If there //are only 3 rows in the returned result set, only 3 rows //are populated into array eIDs, and eIDs.getSize() //returns 3. DynamicEmployee DynamicEmployee; Get Next employees into DynamicEmployee; Record DynamicEmployee eIDs INT[10]; uNames CHAR(20)[10]; end |
Case 3: Static array variables as the target of INTO statement
employees Employee[0]{rowsetsize=10}; Open resultset1 scroll with #sql{ select eID, uName from EMPLOYEETEST } for employees; staticEmployee StaticEmployee; Get Next employees into staticEmployee.eIDs, staticEmployee.uNames; // Get Next employees into staticEmployee; Record StaticEmployee 1 eIDs INT[10]; 1 uNames CHAR(20)[10]; end |
For case 3, if rowsetsize is greater than the length of static array (occurred field), when the Get Next statement is trying to fill value to an element which exceeds the length of the static array, an IndexOutOfBoundException message would be displayed.
2.3.Delete Statement
Positioned Delete statement contains two kinds of operations:
- Delete the specified row in the dynamic array.
- Delete all rows in the dynamic array.
Changes from previous support:
- The Delete statement supports dynamic SQLRecord array variables as the target.
- The Delete statement supports array element as the target.
Case 1: The dynamic array is populated by Get PositionOption Statement (ex. Get Next)
employees Employee[0]{rowsetsize=10}; Open resultset1 forUpdate with #sql{ select eID, uName, PASSWORD, fName, lName, office, sex, EMail from EMPLOYEETEST } for employees; Get Next employees; Delete employees[2]; //this deletes the second row in the row set using
//“WHERE CURRENT OF CS1” i int = 2; Delete employees[i]; //the index could be a variable Delete employees[2] from resultset1; //this should have better performance Delete employees; //this deletes the whole row set using
//“WHERE CURRENT OF CS1” Delete employees from resultset1; //this should have better performance |
2.4.Replace Statement
The Replace Statement contains two kinds of operations:
- Update the specified row in the dynamic array.
- Update the whole dynamic array.
Case 1: The dynamic array is populated by Get PositionOption Statement (ex. Get Next)
employees Employee[0]{rowsetsize=10}; Open resultset1 forUpdate with #sql{ select eID, uName, PASSWORD, fName, lName, office, sex, EMail from EMPLOYEETEST } for employees; Get Next employees; employees[2].uName = “test”; Replace employees[2]; //this updates the second row in the row set newName CHAR(20) = “test1”; Replace employees[2] #with SQL{ update EMPLOYEETEST
set uName = :newName }; //this updates the second row in //the row set i int = 2; Replace employees[i]; //The index can be a variable. Replace employees[2] from resultset1; //this should have better performance uName CHAR(10) = “test”; Replace employees #with SQL{ update EMPLOYEETEST
set uName = :uName }; //this updates the whole row set
//with uName = “test”. Explicated SQL must
//exist, otherwise there should be
//validation error. Replace employees #with SQL{ update EMPLOYEETEST
set uName = :uName } from resultset1; //this should have better
//performance |
2.5.Add Statement
The Add statement supports {@Atomic} and {@Atomic{no}}, the default value is ATOMIC.
Case 1: ATOMIC and NOTATOMIC to Add statement
employees Employee[0]{rowsetsize=10}; //fill employees with many rows here Add {@Atomic} employees; //this insert the rows into table with ATOMIC Add {@Atomic{no}} employees; //this insert the rows into table with NOTATOMIC Add employees; //this insert the rows into table with ATOMIC |
2.6.RowsetSize and MaxSize
RowsetSize controls the number of rows retrieved in one time of multiple-row fetch, or the number of rows inserted in one time of multiple-row insert.
MaxSize controls the maximum elements the dynamic array can contain. When appending a new element, and the size of the array is greater than the MaxSize of the array, a RuntimeException message is displayed. This does NOT affect the GET dynarray type operation.
See below cases:
Case 1:
employees Employee[0]{rowsetsize=10, maxsize=10}; Open resultset1 scroll with #sql{ select eID, uName, PASSWORD, fName, lName, office, sex, EMail from EMPLOYEETEST } for employees; Employees.getMaxsize(); //this returns 10 Get Next employees; //this empty array employees, and populate the next 10
// rows to array employees, up to 10 rows. If there are //only 3 rows in the returned result set, only 3 rows are //populated into array employees, and employees.getSize() //returns 3. |
Case 2:
employees Employee[0]{rowsetsize=10, maxsize=15}; Open resultset1 scroll with #sql{ select eID, uName, PASSWORD, fName, lName, office, sex, EMail from EMPLOYEETEST } for employees; Employees.getMaxsize(); //this returns 15 Get Next employees; //this empty array employees, and populate the next 10
// rows to array employees, up to 10 rows. If there are //only 3 rows in the returned result set, only 3 rows are //populated into array employees, and employees.getSize() //returns 3. |
Case 3:
employees Employee[0]{rowsetsize=10, maxsize=5}; Open resultset1 scroll with #sql{ select eID, uName, PASSWORD, fName, lName, office, sex, EMail from EMPLOYEETEST } for employees; Employees.getMaxsize(); //this returns 5 Get Next employees; //this empty array employees, and populate the next 10
// rows to array employees, but because the maxsize is 5, //and can not contains more than 5 rows, so a //RuntimeException is thrown. User need correct it by change maxsize to 10. |
3.Get Diagnostics Design
You can code get diagnostics statements in Execute #SQL{…} as is shown in below example:
numerrors int; Execute #SQL{ GET DIAGNOSTICS :numerrors = NUMBER; }; retsqlstate char(5); for (i int from 1 to numerrors) Execute #SQL{ GET DIAGNOSTICS CONDITION :i :retsqlstate = RETURNED_SQLSTATE; }; sysLib.writeStdout( "SQLSTATE :" + retsqlstate ); end |
Because GET DIAGNOSTICS can not be executed in Java, Get Diagnostics is not supported in Debugger.