DB2 UDB for zSeries Considerations

Most of the material discussed in this chapter thus far applies to DB2 UDB for zSeries. In this section, few considerations that are specific to zSeries are highlighted.

Condition Handlers

Chapter 4, "Using Flow of Control Statements," mentioned that SQL PL does not require the use of a compound statement to execute more than one statement in a branch of a conditional statement. Thus, if you enclose multiple statements within an IF statement, where the condition is always true, you will in fact be able to code several statements inside a condition handler. Figure 6.27 shows an example of this implementation.

Figure 6.27. An example of Implementation #1 to code several statements within a condition handler in DB2 for zSeries.

 CREATE PROCEDURE simple_error (IN p_midinit CHAR                               ,IN p_empno   CHAR(6) ) WLM ENVIRONMENT <env> LANGUAGE SQL se: BEGIN    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';    DECLARE SQLCODE INT DEFAULT 0;    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION         IF 1=1 THEN                      -- (1)             SET ...;             SET ...;             SELECT * INTO ...;             RETURN ...;         END IF;    UPDATE employee       SET midinit = p_midinit     WHERE empno = p_empno; END se

On Line (1), we use the IF condition statement to allow for multiple statements to be coded within the handler. The condition 1=1 always resolves to true, which means all of these statements are always executed.

Another option is to use the handler to set up a flag variable which can be tested with an IF statement after exiting the handler. Figure 6.28 shows an example of this option.

Figure 6.28. An example of Implementation #2 to code several statements within a condition handler in DB2 for zSeries.

 CREATE PROCEDURE simple_error (IN p_midinit CHAR                               ,IN p_empno CHAR(6) ) WLM ENVIRONMENT <env> LANGUAGE SQL se: BEGIN    DECLARE v_flag SMALLINT DEFAULT 0;    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';    DECLARE SQLCODE INT DEFAULT 0;    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION            SET v_flag = 1;                      --(1)    UPDATE employee       SET midinit = p_midinit     WHERE empno = p_empno;    IF v_flag = 1 THEN                          -- (2)        .  . . ;                                 -- (3)    END IF; END se

On Line (1), we use the condition handler to set the variable v_flag to 1. Later on Line (2), we test if v_flag has a value of 1 and if it does, on Line (3) we perform multiple statements. Later in Figure 6.30, you will see a more complex example which will cover this issue in more detail.

Depending on the type of condition handler and what you are trying to accomplish, you may need to modify the previous example accordingly. Normally you will have to change EXIT handlers to CONTINUE handlers.

Another consideration to keep in mind if you need to set several variables within a condition handler is to use the statements VALUES INTO or SELECT INTO, which allow you to set several variables in one statement.

GET DIAGNOSTICS Statement

The GET DIAGNOSTICS statement has been enhanced considerably in zSeries. Figure 6.29 shows the syntax diagram for the statement.

Figure 6.29. A simplified GET DIAGNOSTICS syntax diagram for zSeries.

 >>-GET-DIAGNOSTICS----+--statement-information--+------------------><                       |--condition-information--|                       '--combined-information---' statement-information     .-,--------------------------------------------------------------.     V                                                                | |----+---host-variable1-------+-=--+--statement-information-item--+--|      '----host-variable1------=--+-DB2_GET_DIAGNOSTICS_DIAGNOSTICS---+' condition-information |-CONDITION---+--+--host-variable2-------+--+------------------------>               '--integer---------------------'     .-,-------------------------------------------------------------.     V                                                               | >----+-----host-variable3----+-=--+--condition-information-item---+-|                                   '--connection-information-item--' combined-information |--+--host-variable4------------+-- = ------------------------------------> >-ALL--+---------------------------------------------------------------+--|        |    .-,--------------------------------------------------------.  |        |   V                                                              |    '---'----+--STATEMENT-------------------------------------------+--+--'             '--+--CONDITION---+--+------------------------------+--'                '--CONNECTION--'  |--+--SQL-variable-name-----+--|                                     '--integer---------------'

The type of information is divided into three parts: statement information, connection information, and condition information. Table 6.3 lists all the keywords for information that can be obtained using the GET DIAGNOSTICS statement.

Table 6.3. Information Available from the zSeries GET DIAGNOSTICS Statement
Information Type	Information Keyword
`STATEMENT`	`DB2_LAST_ROW`
	`DB2_NUMBER_PARAMETER_MARKERS`
	`DB2_NUMBER_RESULT_SETS`
	`DB2_RETURN_STATUS`
	`DB2_SQL_ATTR_CURSOR_HOLD`
	`DB2_SQL_ATTR_CURSOR_ROWSET`
	`DB2_SQL_ATTR_CURSOR_SCROLLABLE`
	`DB2_SQL_ATTR_CURSOR_SENSITIVITY`
	`DB2_SQL_ATTR_CURSOR_TYPE`
	`MORE`
	`NUMBER`
	`ROW_COUNT`
`CONNECTION`	`DB2_AUTHENTICATION_TYPE`
	`DB2_AUTHORIZATION_ID`
	`DB2_CONNECTION_STATE`
	`DB2_CONNECTION_STATUS`
	`DB2_CONNECTION_TYPE`
	`DB2_DDM_SERVER_CLASS_NAME`
	`DB2_PRODUCT_ID`
`CONDITION`	`CATALOG_NAME`
	`CONDITION_NUMBER`
	`CURSOR_NAME`
	`DB2_ERROR_CODE1`
	`DB2_ERROR_CODE2`
	`DB2_ERROR_CODE3`
	`DB2_ERROR_CODE4`
	`DB2_INTERNAL_ERROR_POINTER`
	`DB2_MESSAGE_ID`
	`DB2_MODULE_DETECTING_ERROR`
	`DB2_ORDINAL_TOKEN_n`
	`DB2_REASON_CODE`
	`DB2_RETURNED_SQLCODE`
	`DB2_ROW_NUMBER`
	`DB2_SQLERRD_SET`
	`DB2_SQLERRD1`
	`DB2_SQLERRD2`
	`DB2_SQLERRD3`
	`DB2_SQLERRD4`
	`DB2_SQLERRD5`
	`DB2_SQLERRD6`
	`DB2_TOKEN_COUNT`
	`MESSAGE_TEXT`
	`RETURNED_SQLSTATE`
	`SERVER_NAME`

Some options discussed earlier for LUW also apply except that MESSAGE_TEXT in zSeries returns only the message text, not the SQLCODE or SQLSTATE. For the example shown earlier where MESSAGE_TEXT returned

 SQL0727N  An error occurred during implicit system action type "1".  Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "DB2ADMIN.EMPLOYEE".  SQLSTATE=56098

DB2 for zSeries would only return:

 DB2ADMIN.EMPLOYEE IS AN UNDEFINED NAME

As indicated earlier, the keyword DB2_TOKEN_STRING is not supported in DB2 UDB for zSeries. However, a combination of other different keywords may provide similar information. Figure 6.30 shows an example where an SQL procedure returns the SQLCODE, SQLSTATE, and message text information. This SQL procedure also reflects the way procedure simple_error_token shown in Figure 6.19 would be written in zSeries. Note that this procedure also works for iSeries.

Figure 6.30. The GET DIAGNOSTICS statement in DB2 for zSeries and iSeries.

 CREATE PROCEDURE simple_error_token ( IN p_midinit       CHAR                                      ,IN p_empno         CHAR(6)                                      ,OUT p_sqlcode_out  INT                                      ,OUT p_sqlstate_out CHAR(5)                                      ,OUT p_token_string VARCHAR(100) )     LANGUAGE SQL --  SPECIFIC simple_error_token                      -- applies to iSeries     WLM ENVIRONMENT <env>                            -- applies to zSeries setk: BEGIN     -- Declare variables     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';     DECLARE SQLCODE INT DEFAULT 0;     -- Declare condition handlers     DECLARE EXIT HANDLER FOR SQLEXCEPTION         GET DIAGNOSTICS EXCEPTION 1  p_sqlcode_out  = DB2_RETURNED_SQLCODE,                                      p_sqlstate_out = RETURNED_SQLSTATE,                                      p_token_string = MESSAGE_TEXT;   -- (1)    -- Procedure logic    SET p_token_string = '';    VALUES (SQLCODE, SQLSTATE)      INTO p_sqlcode_out          ,p_sqlstate_out;    UPDATE employee       SET midinit = p_midinit     WHERE empno = p_empno; END setk

On Line (1), you can see that the GET DIAGNOSTICS statement can return several values in one statement. Alternatively, the following options return several groups of values at once:

 GET DIAGNOSTICS p_error_message = ALL STATEMENT; GET DIAGNOSTICS p_error_message = ALL CONNECTION; GET DIAGNOSTICS p_error_message = ALL CONDITION;

For example, the ALL CONDITION keyword may return something like the following:

 CONDITION_NUMBER=1; DB2_RETURNED_SQLCODE=204; RETURNED_SQLSTATE=42704; DB2_REASON_CODE=0; DB2_ROW_NUMBER=00;DB2_ERROR_CODE1=- 500;DB2_ERROR_CODE2=0;DB2_ERROR_CODE3=0; DB2_ERROR_CODE4=-1; DB2_SQLERRD1=- 500;DB2_SQLERRD2=0;DB2_SQLERRD3=0;DB2_SQLERRD4=-1; DB2_SQLERRD5=0; DB2_SQLERRD6=0; DB2_INTERNAL_ERROR_POINTER=- 500;DB2_MODULE_DETECTING_ERROR=DSNXOTL ;MESSAGE_ID=DSN00204E ; SERVER_NAME=MEXICO;DB2_ORDINAL_TOKEN_1 =TS56692.EMPLOYEE;MESSAGE_TEXT=TS56692.EMPLOYEE IS AN UNDEFINED NAME;CURSOR_NAME= ;

For the complete set of keywords that are allowed, refer to the DB2 UDB for z/OS Version 8 SQL Reference Guide.

RETURN Statement

In DB2 UDB for zSeries, a RETURN statement can be coded within a condition handler. Therefore, the stored procedure ret_value shown in Figure 6.21 can be rewritten as shown in Figure 6.31. This also works for iSeries.

Figure 6.31. The RETURN statement within a condition handler in DB2 for zSeries and iSeries.

 CREATE PROCEDURE ret_value ( )     LANGUAGE SQL --  SPECIFIC ret_value                       -- applies to iSeries     WLM ENVIRONMENT <env>                    -- applies to zSeries rv: BEGIN     DECLARE EXIT HANDLER FOR SQLEXCEPTION             RETURN -1;     <body of procedure>; END rv

Figure 6.32 shows one way in which the SQL procedure delete_dept (originally shown in Figure 6.23) can be rewritten in DB2 UDB for zSeries.

Figure 6.32. Rewriting delete_dept SQL procedure in DB2 UDB for zSeries.

 CREATE PROCEDURE delete_dept ( IN p_deptno CHAR(3)                               ,OUT p_message VARCHAR(100) ) LANGUAGE SQL WLM ENVIRONMENT <env> ------------------------------------------------------------------------ -- Procedure Description -- -- Deletes a department, as long as there are no rows with the input --    department number in any child tables (EMPLOYEE and PROJECT). -- -- RETURNS:  1 if successful, but now rows existed for deletion --           0 on successful completion --          -1 on un-successful complete --    SETS: Appropriate message in output parameter 'p_message' ------------------------------------------------------------------------ dd: BEGIN     -- Declare variables     DECLARE SQLCODE     INT     DEFAULT 0;     DECLARE SQLSTATE    CHAR(5) DEFAULT '00000';     DECLARE v_ret_value INT     DEFAULT 0;     DECLARE v_num_rows  INT     DEFAULT 0;     -- Declare conditions     DECLARE c_EMP_child_rows_exist  CONDITION FOR SQLSTATE '99001';     DECLARE c_PROJ_child_rows_exist CONDITION FOR SQLSTATE '99002';     -- Declare handlers     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION                          -- (1)          VALUES ('Unknown error, SQLSTATE: "' ||                   SQLSTATE || '", SQLCODE=' || CHAR(SQLCODE), -1)            INTO p_message, v_ret_value;                                -- (2)     -- Declare handlers for custom conditions     DECLARE EXIT HANDLER FOR c_EMP_child_rows_exist, c_PROJ_child_rows_exist             RETURN -1;                                                 -- (3)     -- Child table: EMPLOYEE     SELECT COUNT(1)       INTO v_num_rows       FROM employee      WHERE workdept = p_deptno;      IF v_ret_value = -1 THEN GOTO BYE; END IF;                        -- (4)      IF v_num_rows <> 0 THEN         SET p_message = 'Cannot delete, child EMPLOYEE rows exist.';         SIGNAL c_EMP_child_rows_exist;      END IF;      --Child table: PROJECT      SELECT COUNT(1)        INTO v_num_rows        FROM project       WHERE deptno = p_deptno;      IF v_ret_value = -1 THEN GOTO BYE; END IF;                        -- (5)     IF v_num_rows <> 0 THEN        SET p_message = 'Cannot delete, child PROJECT rows exist.';          SIGNAL c_PROJ_child_rows_exist;       END IF;       DELETE FROM department           WHERE deptno = p_deptno;       GET DIAGNOSTICS v_num_rows = ROW_COUNT;       IF v_ret_value = -1 THEN GOTO BYE; END IF;                       -- (6)       -- Set the appropriate return message       IF v_num_rows = 0 THEN          SET v_ret_value = 1;          SET p_message = 'No rows exist for deletion of department '          || p_deptno || '.';       ELSE          SET p_message = 'Department ' || p_deptno || ' successfully deleted.';       END IF;       LEAVE dd;                                                        -- (7) BYE:  RETURN -1;                                                       -- (8) END  dd

The following notes correspond to the location numbers shown in Figure 6.32:

Because nested compound statements are not allowed in DB2 for zSeries, only one statement can be coded within a handler; thus, we were unable to set a variable, and at the same time RETURN -1. The IF statement workaround to code several statements within a condition handler is not used in this implementation. The handler was changed from an EXIT handler in the original implementation to a CONTINUE handler, and test conditions where added in Lines (5), (6), and (7). These test conditions would determine if the CONTINUE handler was invoked and if it was, the rest of the instructions would be skipped and a -1 would be returned as indicated by the BYE label.
VALUES INTO and SELECT INTO are handy statements to use within a condition handler because only one statement is allowed inside it, and these statements can set several variables at once.
Note that a RETURN statement can be coded within a condition handler in DB2 for zSeries.
We tested to see if the CONTINUE handler was invoked. If it was, then we should go to the BYE label and return -1.
Same as Line (4).
Same as (4)
This is required so that the BYE label is only reached when the condition handler was invoked, as tested in Lines (4), (5), and (6).
The label BYE should only be reached based on the tests performed in Lines (4), (5), and (6).