Improved code generation for SQL PL procedures, functions, & triggers

You are in: IBM i Technology Updates > Db2 for i - Technology Updates >  Db2 for i Performance Enhancements > Improved code generation for SQL PL procedures, functions, & triggers

What's new with IBM i 7.3 SF99703 Level 11 and IBM i 7.2 SF99702 Level 23:

• See section 2 (IBM i 7.2 and higher)

The article "Improving SQL procedure performance" includes a good description of many cases where SQL statements can be effectively in-lined during CREATE PROCEDURE (SQL), CREATE FUNCTION (SQL),  and CREATE TRIGGER.  The complete article can be downloaded using the Improving SQL Procedure Performance link on this page: Tips and Coding Examples.

Appendix A in that document enumerates the instances where in-lining should occur.

The article does not include many recent enhancements for in-lining capability.

Section 1:

Performance enhancements for SQL PL routines (procedures, functions, and triggers):

1. Generate direct C code for SET assignments of literal to a GRAPHIC/VARGRAPHIC variable of CCSID 13488.
2. Generate direct C code for IF statements where a GRAPHIC/VARGRAPHIC variable of CCSID 13488 is compared to a literal.  This only applies when SRTSEQ(*HEX) is used.
3. Generate internal work variables and functions such that they are generated local to the ILE C module rather than exported. 

Section 2:

This enhancement is applicable to LANGUAGE SQL procedures, functions, and triggers.

1) When assigning an SQL variable of type GRAPHIC or VARGRAPHIC to an SQL variable of type GRAPHIC or VARGRAPHIC, the assignment may be accomplished directly within the generated ILE C code.

This improvement applies to the following usage of the SQL SET variable statement:

SET v1 = v2;

where v1 is of type GRAPHIC or VARGRAPHIC, v2 is of type GRAPHIC or VARGRAPHIC, the CCSIDs of v1 and v2 are equal, and the CCSID is not 1200.

2) When assigning a SUBSTRING of an SQL variable of type GRAPHIC or VARGRAPHIC to an SQL variable of type GRAPHIC or VARGRAPHIC, the SUBSTRING and assignment may be accomplished directly within the generated ILE C code.

This improvement applies to the following usage of the SQL SET variable statement:

SET v1 = SUBSTRING(v2, 1, <literalOne>);

where v1 is of type GRAPHIC or VARGRAPHIC, v2 is of type GRAPHIC or VARGRAPHIC, the CCSIDs of v1 and v2 are equal, the CCSID is not 1200, and <literalOne> is a literal with a non negative value which is less than or equal to the length of v2 and less than or equal to the length of v1.

3) When using an IN or NOT IN predicate within a simple IF statement to compare one or more character-string constants or Unicode graphic-string constants to an SQL variable of type GRAPHIC or VARGRAPHIC marked with CCSID 13488, the comparison may be accomplished directly within the generated ILE C code.

This improvement applies to the following usage of the SQL IF statement:

IF v1 IN (character-string constant, ...) THEN
IF v1 IN (Unicode graphic-string constant, ...) THEN
IF v1 NOT IN (character-string constant, ...) THEN
IF v1 NOT IN (Unicode graphic-string constant, ...) THEN

where v1 is of type GRAPHIC or VARGRAPHIC, the CCSID of v1 is 13488, the constant is 256 characters or less in length, and there are at most three constants within the IN or NOT IN predicate.

4) Using the SQL IF statement within a LANGUAGE SQL trigger to check multiple trigger event predicates may be accomplished directly within the generated ILE C code.

This improvement applies to the following usage of the SQL IF statement:

IF <event-predicate-one> <op> <event-predicate-two> THEN

where <event-predicate-one> and <event-predicate-two> are DELETING, INSERTING, UPDATING, NOT DELETING, NOT INSERTING, or NOT UPDATING, and <op> is AND or OR.

To achieve the improved code generation, SQL procedures, functions, and triggers must be recreated after this PTF is applied.