Links

Tags

Recent tweets

Multiple-row feature to improve database access performance

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 intfrom 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.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 GETdynarray 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{

GETDIAGNOSTICS :numerrors = NUMBER;

};

retsqlstate char(5);

for (i intfrom 1 to numerrors)

Execute #SQL{

GETDIAGNOSTICS 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.