Improving implementation of procedures and functions

These coding techniques help reduce the processing time of a function or procedure.

The following tips are especially important for functions because a function tends to be called multiple times from many different procedures:

Use the NOT FENCED option so UDFs run in the same thread as the caller.
Use the DETERMINISTIC option on procedures and UDFs that return the same results for identical inputs. This allows the optimizer to cache the results of a function call or order where the function is called in the execution stream to reduce the run time.
Use the NO EXTERNAL ACTION option on UDFs that do not take an action outside the scope of the function. An example of an external action is a function that initiates a different process to fulfill a transaction request.

The use of pipelined table functions can improve performance in some situations.

Avoid the overhead of creating and populating a temporary table by returning the results directly.
Return only a subset of the data, rather than all the rows from a query, with the flexibility of combining SQL routine logic with the PIPE statement.

Coding techniques used for the SQL routine body can have a major impact on the runtime performance of the generated C program. By writing your routine to allow greater use of C code for assignments and comparisons, the overhead of an equivalent SQL statement is avoided. The following tips should help your routine generate more C code and fewer SQL statements.

Declare host variables as NOT NULL when possible. This saves the generated code from having to check and set the null value flags. Do not automatically set all variables to NOT NULL. When you specify NOT NULL, you need to also give a default value. If a variable is always used in the routine, a default value might help. However, if a variable is not always used, having a default value set may cause additional initialization overhead that is not needed. A default value is best for numeric values, where an additional database call to process the assignment of the default value is not needed.
Avoid character and date data types when possible. An example of this is a variable used as a flag with a value of 0, 1, 2, or 3. If this value is declared as a single character variable instead of an integer, it causes calls to the database engine that can be avoided.
Use integer instead of decimal with zero scale, especially when the variable is used as a counter.

Do not use temporary variables. Look at the following example:

IF M_days<=30 THEN
	SET I = M_days-7;
	SET J = 23
	RETURN decimal(M_week_1 + ((M_month_1 - M_week_1)*I)/J,16,7);
END IF

This example can be rewritten without the temporary variables:

IF M_days<=30 THEN
	Return decimal(M-week_1 + ((M_month_1 - M_week_1)* (M_days-7))/23,16,7);
END IF

Combine sequences of complex SET statements into one statement. This applies to statements where C code only cannot be generated because of CCSIDs or data types.
```
SET var1 = function1(var2);
SET var2 = function2();
```
Can be rewritten into one statement:
```
SET var1 = function1(var2), var2 = function2();
```
Simple array element assignments can be implemented in C code when only one assignment is performed in a SET statement.
```
SET array_var[1] = 10, array_var[2] = 20;
```
Should be rewritten as:
```
SET array_var[1] = 10;
SET array_var[2] = 20;
```
Use IF () ELSE IF () ... ELSE ... constructs instead of IF (x AND y) to avoid unnecessary comparisons.

Do as much in SELECT statements as possible:

SELECT A INTO Y FROM B;
	SET Y=Y CONCAT 'X';

Rewrite this example:

SELECT A CONCAT 'X' INTO Y FROM B

Avoid doing character or date comparisons inside of loops when not necessary. In some cases the loop can be rewritten to move a comparison to precede the loop and have the comparison set an integer variable that is used within the loop. This causes the complex expression to be evaluated only one time. An integer comparison within the loop is more efficient since it can be done with generated C code.
Avoid setting variables that might not be used. For example, if a variable is set outside of the an IF statement, be sure that the variable will actually be used in all instances of the IF statement. If not, then set the variable only in the portion of the IF statement that is it actually used.

Replace sections of code with a single SELECT statement when possible. Look at the following code snippet:

SET vnb_decimal = 4;
cdecimal:
			FOR vdec AS cdec CURSOR FOR 
			SELECT nb_decimal
			FROM K$FX_RULES
			WHERE first_currency=Pi_curl AND second_currency=P1_cur2
			DO
				SET vnb_decimal=SMALLINT(cdecimal.nb_decimal);
END FOR cdecimal;

	IF vnb_decimal IS NULL THEN
		SET vnb_decimal=4;
	END IF;
	SET vrate=ROUND(vrate1/vrate2,vnb_decimal);
	RETURN vrate;

This code snippet can be more efficient if rewritten in the following way:

RETURN( SELECT
			CASE
				WHEN MIN(nb_decimal) IS NULL THEN ROUND(Vrate1/Vrate2,4)
			ELSE ROUND(Vrate1/Vrate2,SMALLINT(MIN(nb_decimal)))
			END
			FROM K$FX_RULES
			WHERE first_currency=Pi_curl AND second_currency=Pi_cur2);

C code can only be used for assignments and comparisons of character data if the CCSIDs of both operands are the same, if one of the CCSIDs is 65535, if the CCSID is not UTF-8, and if truncation of character data is not possible. If the CCSID of the variable is not specified, the CCSID is not determined until the procedure is called. In this case, code must be generated to determine and compare the CCSID at runtime. If an alternate collating sequence is specified or if *JOBRUN is specified, C code cannot be generated for character comparisons.
C code can only be used for assignments and comparisons of graphic data if the CCSIDs of both operands are the same, if the CCSID is not UTF-16, and if truncation of graphic data is not possible. If an alternate collating sequence is specified or if *JOBRUN is specified, C code cannot be generated for graphic comparisons.
Use the same data type, length and scale for numeric variables that are used together in assignments. C code can only be generated if truncation is not possible.
```
DECLARE v1, v2 INT;
SET v1 = 100;
SET v1 =  v2;
```
Using identical attributes to set or retrieve array elements may result in the generation of C code for integer, character, varchar, decimal, and numeric types. Character variables that require CCSID processing can require an SQL SET statement to be generated.
Comparisons using array elements are never generated in C. Some comparisons could result in better performance if the element value is assigned to a local variable first that can be used in the comparison.