Inline SQL PL is described as “inline” because the logic is expanded into and executed with the SQL statements that reference them. In this appendix, we look at statements and elements supported in inline SQL PL. For thorough discussions and examples of how it can be used, refer to Chapter 9, “User-Defined Functions and Triggers.”

From the syntax diagram presented in Figure B.1, you can see that the dynamic compound statement must be wrapped inside a BEGIN ATOMIC ... END block. This ensures that either all or none of the statements inside the block will be committed to the database. Optionally, a label can be used to name the atomic block.

dynamic-compound-statement

>>-+-------------+--BEGIN ATOMIC-------------------------------->
   '-label:------'

>--+-----------------------------------------+------------------>
   | .-------------------------------------. |
   | V                                     | |
   '---+-| SQL-variable-declaration |-+--;-+-'
       '-| condition-declaration |----'


   .-,--------------------------.
   V                            |
>----SQL-procedure-statement--;-+--END--+-------+--------------><
                                        '-label-'

SQL-variable-declaration:


           .-,-----------------.
            V                   |
|--DECLARE----SQL-variable-name-+--data-type-------------------->


   .-DEFAULT NULL------------.
>--+-------------------------+----------------------------------|
   '-DEFAULT--default-values-'


condition-declaration:


|--DECLARE--condition-name--CONDITION--FOR---------------------->

               .-VALUE-.
   .-SQLSTATE--+-------+-.
>--+---------------------+--string-constant---------------------|

In the declaration section, only SQL variable and condition declarations are supported. This means that you cannot declare cursors and condition handlers. You may ask what good is it to declare conditions without the ability to declare handlers. In inline SQL PL, a condition will be useful when you want to raise an error with a SIGNAL statement using a named condition.

As for the SQL procedure statements supported in inline SQL PL, there are few restrictions. Refer to Figure B.2 for a complete list of supported SQL procedure statements.

CALL                                  IF
CASE                                  ITERATE
DECLARE <variable>                    LEAVE
DECLARE <condition>                   RETURN
FOR                                   SET
GET DIAGNOSTICS                       SIGNAL
GOTO                                  WHILE

Most of them are fairly straightforward, but a few statements warrant some discussion. The CALL statement enables the ability to CALL stored procedures from within triggers, SQL UDFs, SQL methods, and stand-alone code. This significantly extends the power of these objects and increases the reusability of stored procedures. For example, you can now indirectly handle conditions in an SQL UDF by calling a stored procedure that contains error-handling logic.

Because cursor declaration is not supported in inline SQL PL, cursor manipulations such as positioned updates and deletes are not possible. Rather than using cursors explicitly, you can alternatively use the FOR statement to loop through the result set returned from the specified SELECT statement. Here is an example extracted from Chapter 4, “Using Flow of Control Statements,” that uses a FOR loop to iterate through all the rows returned from the SELECT statement. This method gives you the same result as declaring, opening, and fetching a cursor. In fact, a read-only cursor is declared under the cover when the following example is executed:

FOR v_row AS
    SELECT firstnme, midinit, lastname
      FROM employee
    DO
        SET v_fullname = v_row.lastname || ', ' ||
                         v_row.firstnme || ' ' || v_row.midinit;
        INSERT INTO tname VALUES (v_fullname);
END FOR

The SET statement support in inline SQL PL is slightly different from what it can do inside an SQL procedure. As you may already know, the SET statement is used to assign a value to a variable such as this:

SET v1 = 3;

You can also assign a result value from a SELECT statement to a variable:

SET v_salary = (SELECT salary FROM employee WHERE empno='000010'),

Note that the SELECT statement must return only a single row; otherwise, the statement will raise an error. These statements are both valid in SQL procedures and inline SQL PL. In an SQL procedure, if you want to assign values to one or more variables, you must use the SELECT ... INTO ... statement—for example,

SELECT salary, bonus INTO v_salary, v_bonus FROM employee WHERE empno='000010';

However, in inline SQL PL, the SELECT ... INTO ... statement is not supported. You use the following SET statement to reach the same result instead. Remember that the SELECT statement shown previously must return only one row.

SET v_salary, v_bonus = (SELECT salary, bonus FROM employee WHERE empno='000010'),

To summarize when to use which statement, refer to Table B.1.

SET v1 = (SELECT c1 
FROM t1
          FETCH 1 ROW ONLY)

           SET v1, v2 = (SELECT c1, c2
              FROM t1
              FETCH 1 ROW ONLY)

SELECT c1, c2
INTO v1, v2
FROM t1
FETCH 1 ROW ONLY

Besides showing the list of supported statements allowed in inline SQL PL, Figure B.4 calls out the unsupported statements.

ALLOCATE CURSOR                  LOOP
ASSOCIATE LOCATORS               REPEAT
DECLARE <cursor>                 RESIGNAL
DECLARE ... HANDLER              COMMIT
PREPARE                          ROLLBACK
EXECUTE
EXECUTE IMMEDIATE

Looking at Figure B.4, you already know the story about cursors and handlers. Therefore, any statements related to cursors and handlers are not supported.

Because the statements are dynamic, it is rational that the support of the PREPARE, EXECUTE, and EXECUTE IMMEDIATE statements are not needed. As for the LOOP and REPEAT statements, you can use the WHILE loop to implement the same logic. RESIGNAL is not supported because it can only be used within a condition handler for which it is not allowed in inline SQL PL.

Recall that a dynamic compound statement must be atomic so that all or none of the member statements commit successfully. Therefore, it does not make sense to commit or roll back any particular statement inside the block.

DB2 does support stand-alone codes, also known as SQL PL scripting. It is very straightforward to develop DB2 stand-alone codes because you have just learned about DB2 UDFs and triggers. DB2 stand-alone codes, which are the same as UDFs and triggers, are supported by DB2 inline SQL PL. Figure B.5 shows an example of the DB2 stand-alone code. The example requires a supporting table duplicate_empno, which is defined as the following:

CREATE TABLE duplicate_empno(empno CHAR(6))

BEGIN ATOMIC                                               -- (1)
  DECLARE v_empno CHAR(6);
  DECLARE v_prev_empno CHAR(6);

  FOR c_emp AS                                             -- (2)
    SELECT empno FROM employee ORDER BY empno

  DO
    SET v_empno = empno;

    IF (v_empno = v_prev_empno) THEN                       -- (3)
       INSERT INTO duplicate_empno VALUES (v_empno);
    END IF;

    SET v_prev_empno = v_empno;
  END FOR;
END                                                        -- (4)
@

The example in Figure B.5 is a utility script that can be used to quickly find out the duplicate employee number in the employee table. Assume that the data in the department table is found to be corrupted. For some reason, there are few cases of employees with the same employee ID. The code is used to quickly find the bad employee IDs for further investigation.

A piece of stand-alone code must be encased within the BEGIN ATOMIC and END clause on Lines (1) and (4). An implicit cursor is used with a FOR loop on Line (2). The algorithm to find the duplicate IDs is to sort the IDs first and then compare each ID with the previous ID. All IDs matching their previous IDs are captured on Line (3) for further investigation.

The ’@’ character at the last line is the statement terminator. To execute the code, you need to save it to a file, say find_dup.db2. Then, execute the following at the command prompt:

db2 -td@ -f find_dup.db2

For more information on the statement terminator and on how to execute statements in files, refer to Appendix A, “Getting Started with DB2.”

Please note that there are no duplicate employee numbers in the employee table. You need to manually insert a few rows of bad data in order to capture the duplicate employee numbers.

After knowing the differences between inline SQL PL and SQL PL, you are probably wondering when to choose dynamic compound statements as opposed to SQL procedures and vice versa.

First of all, both are compound statements if the SQL procedure contains more than one statement in the procedure body. Using compound statements usually improves performance because statements are grouped in one execution block. This minimizes network flow by sending only one request to DB2 for a set of statements versus one request for each statement.

At SQL procedure creation time, the procedure is compiled and a package is created. The package contains the execution path of how data will be accessed (also known as the data access path). In other words, the optimizer evaluates the best data access path when the procedure is being created. Depending on the query optimization class, the compile time or procedure creation time varies. On the other hand, dynamic compound statements are dynamic in nature, and data access plans are generated at execution time.

Other than understanding when dynamic compound statements and SQL procedures are being compiled, you should also take notice of the complexity of the logic you are implementing. If the logic is complex, we recommend you use SQL procedures because they support the comprehensive SQL PL with which you can easily implement efficient logic.

On other hand, if the logic is simple and the number of statements is relatively small, consider using inline SQL PL instead. With the power of SQL PL, SQL procedures can definitely handle simple logic but use of stored procedures incurs some overhead. In cases when only one or two SQL statements are wrapped inside a stored procedure, there might not be any performance gain. Sometimes it might actually negatively impact performance by using such a stored procedure. In such a case, you can probably obtain better performance results by using inline SQLs.

When inline SQLs are being executed, they are “inlined” into the calling SQL statements. These statements will be expanded to also contain the logic of the compound statements. Therefore, it is always a good practice to keep the inline SQL simple and short so that statement compilation and optimization can be performed efficiently.

Atomicity is also another area to think about. As highlighted in the previous section, commit and rollback are not allowed in dynamic compound statements because they must be atomic. If you need to implement transaction controls inside the compound statements, SQL procedures will be your only choice.

This appendix introduced you to a subset of SQL PL called inline SQL PL, which is only used by DB2 UDB for Linux, UNIX, and Windows. It is used in dynamic compound SQL statements that can be executed as stand-alone code. Dynamic compound SQL statements can also be used in triggers, SQL UDFs, and SQL methods. Because inline SQL PL does not contain all the SQL PL elements, supported as well as unsupported SQL statements were highlighted. Even though only simple logic is allowed in inline SQL PL, you can extend the power of stored procedures by calling them from within dynamic compound SQL statements.

The appendix explained that utilizing compound statements helps to improve performance. With dynamic compound statements and SQL stored procedures, there are few things to consider when choosing one over the other. First, it is crucial to understand when the data access plan is generated. Secondly, determine whether the complexity of the logic can be handled by dynamic compound statements. If so, pay attention to the size of the SQL statements that are going to be embedded. Use stored procedures if it is large and complex.

A simple example of how to use inline SQL PL in stand-alone code was demonstrated here. For more practical examples of the language usage in UDFs and triggers, Chapter 9, “User-Defined Functions and Triggers,” is a must-read.