Browse Prior Art Database

Improving Query Performance For Embedded SQL Program on DBCS System Disclosure Number: IPCOM000073626D
Original Publication Date: 2005-Feb-22
Included in the Prior Art Database: 2005-Feb-22
Document File: 1 page(s) / 44K

Publishing Venue



On iSeries* double byte character set (DBCS) system, the following query can perform poorly when compiled in an embedded SQL program, such as RPG/SQL, COBOL/SQL programs. SELECT * FROM library/FileA WHERE fieldA LIKE :HV1 AND fieldB = :HV2 where HV1 is 'ABC000____ % ' and HV2 is ' ', and FieldA and FieldB are both defined as character type fields in the base file library/FileA which contains 72 million records. Also, there is index IX with key fields: FieldA and FieldB over the base file library/FileA on the system. This disclosure will improve these type of queries performance on DBCS system with no DBCS data in the databases.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 69% of the total text.

Page 1 of 1

Improving Query Performance For Embedded SQL Program on DBCS System

Disclosed is the common situation from the customers, from the customer itself, the data itself is not integrated graphics controller (IGC) data, but the customer coded the program and inserted the data on a single byte character set (SBCS) system, then moved both to a double byte character set (DBCS) system. However, once the program moved to DBCS system, the query performance degraded dramatically.

     This invention is to introduce a data checker engine in the query validation phase before the query goes to the query optimizer on DBCS system,

     If the RPG/SQL or COBOL/SQL program is running on a SBCS system, then we bypass the data checker engine, and directly get into the query optimizer to choose the implementation..

     If the program is running on DBCS system, then we input the user's data into the data checker engine first, ask the data checker engine to scan/check whether the user's data is really IGC data or just the regular character type which matches the field data type. If the data checker engine detects that the user's data is the regular character type, then let the query optimizer just continue to use the existing index and avoid using table scan or creating temporary index as before the database fix packs did. This can improve the customer's query performance significantly. If the data checker engine detects the user's data are really IGC data, then the query optimizer then uses the mappi...