SQL Procedure Language: Differences across platforms
Implementing SQL stored procedures on distributed platforms and z/OS
SQL PL has became very popular for the development of stored procedures for DB2 on different platforms. While the language is basically the same for DB2 for iSeries, DB2 for Linux, UNIX and Windows (referred to as the distributed platforms), or DB2 for z/OS, some differences exist among different operating system implementations and DB2 releases.
This article outlines the differences among the SQL PL implementations on different platforms and in various releases of DB2. It provides help for SQL procedure developers in writing portable database applications, or porting their database applications from one platform to another.
Please note, that if you are running Linux on z/OS, you are dealing with DB2 on Linux, so all the rules applicable to DB2 for Linux, UNIX and Windows apply.
This article is not a substitute for product documentation. Please refer to SQL Reference for Cross Platform Development for more information.
CREATE PROCEDURE statement
The syntax for this statement is very similar between platforms, as follows:
CREATE PROCEDURE procname
( IN/OUT/INOUT parameter_name DB2_data_type )
specific_name list_of_options SQL_body
Here are some of the CREATE PROCEDURE implementation differences for the various platforms.
- While on distributed platforms stored procedures can be overloaded (for example, you can overload procedure abc(p1,p2) with procedure abc(p1, p2, p3)), on z/OS platform, stored procedure overloading is not permitted, so if you are designing an application that needs to be implemented on both platforms, it is best to avoid overloading.
- While all platforms support all native DB2 data types, DB2 v8 on z/OS supports
TABLE LIKE__table-name__AS LOCATOR, thus permitting parameter specification in a transition table.
- When a stored procedure is invoked, the actual values in the transition table are not passed to the stored procedure. A single value is passed instead. This single value is a locator to the transition table, which the procedure uses to access the transition table columns. A procedure with a table parameter can only be invoked from the triggered action of a trigger.
- While LANGUAGE SQL is a mandatory clause for SQL Procedures for DB2 on z/OS, it is now optional for UDB v8 on distributed, but my recommendation would be to always specify this clause for platform-independent procedures.
- Specific name is recommended (but is optional) for UDB and not supported for DB2 on z/OS. Instead, DB2 on z/OS supports an optional external name, as you'll see below.
Now compare the other options. The following options are supported on both platforms. They are optional and can be specified in any order:
- DYNAMIC RESULT SETS 0 / integer -- Specifies the maximum number of query result sets that the stored procedure can return. The default is DYNAMIC RESULT SETS 0, which indicates that there are no result sets. The value must be between 0 and 32767
- NOT DETERMINISTIC / DETERMINISTIC -- Specifies whether the stored procedure returns the same results for each invocation with the same IN and INOUT arguments. DB2 does not verify that the stored procedure code is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC.
- MODIFIES SQL DATA / READS SQL DATA / CONTAINS SQL -- Specifies the classification of SQL statements that the procedure can execute. The default is MODIFIES SQL DATA.
- CALLED ON NULL INPUT -- Specifies that the procedure is to be called even if any or all argument values are null, which means that the procedure must be coded to test for null argument values. The procedure can return null or non-null values. CALLED ON NULL INPUT is the default.
The following options are supported for DB2 on z/OS only, they are optional and can be specified in any given order. If you want your procedures to work in both environments you may comment out those for distributed implementation, but use them for DB2 for z/OS:
- NO COLLID / COLLID collection ID -- Identifies the package collection that is to be used when the stored procedure is executed. This is the package collection into which the DBRM that is associated with the stored procedure is bound. NO COLLID is the default.
- WLM ENVIROMENT name -- Identifies the WLM (workload manager) environment in which the stored procedure is to run when the DB2 stored procedure address space is WLM-established. The name of the WLM environment is an SQL identifier. If you do not specify WLM ENVIRONMENT, the stored procedure runs in the default WLM-established stored procedure address space specified at installation time.
- PROGRAM TYPE MAIN / SUB -- Specifies whether the stored procedure runs as a main routine or a subroutine. MAIN is the default for SQL procedures.
- EXTERNAL NAME string / identifier -- Specifies the name of the z/OS load module for the program that runs when the procedure name is specified in an SQL CALL statement. The value must conform to the naming conventions for z/OS load modules: the value must be less than or equal to 8 bytes, and it must conform to the rules for an ordinary identifier with the exception that it must not contain an underscore.
As an example, the following stored procedure can be built on both platforms without any changes:
CREATE PROCEDURE UPDATE_SALARY_1 (IN EMPLOYEE_NUMBER CHAR(10), IN RATE DECIMAL(6,2)) LANGUAGE SQL MODIFIES SQL DATA BEGIN UPDATE EMP SET SALARY = SALARY * RATE WHERE EMPNO = EMPLOYEE_NUMBER; END
The second example shows a DB2 SQL stored procedure for z/OS. The procedure runs in a WLM environment called PARTSA and uses package collection PROC_COL when executed. SQL work is committed on return to the caller.
CREATE PROCEDURE UPDATE_SALARY_1 (IN EMPLOYEE_NUMBER CHAR(10), IN RATE DECIMAL(6,2) ) LANGUAGE SQL MODIFIES SQL DATA COLLID PROC_COL WLM ENVIRONMENT PARTSA DETERMINISTIC COMMIT ON RETURN YES BEGIN UPDATE EMP SET SALARY = SALARY * RATE WHERE EMPNO = EMPLOYEE_NUMBER; END
The options for CREATE PROCEDURE statement for DB2 on z/OS are not limited to the options described above. Other specific z/OS options would only be used due to a need to exploit a unique capability of DB2 on z/OS. You can find more information in the DB2 UDB for z/OS V8 SQL Reference (SC18-7426-01).
Declaration of variables
There are no differences in the declarations of local variables, cursors, temporary tables, but on both platforms the order of declaration is important. Cursors always need to be declared in the beginning of the block. The order is as follows:
- Local variables declaration
- Cursor declaration
- Temporary tables declaration
- Condition declaration
- SQL-control statements
While there are no differences in syntax or semantics between UDB on distributed platforms and DB2 v8 for z/OS, you should note that DB2 v7 for z/OS does not support full select in assignment statement. So the statement:
SET sal = (SELECT salary from emp where empno =’000010’)
-- is not supported in DB v7.
DB2 v8 on z/OS supports all statements that are supported on UDB on the distributed platforms, except for the FOR statement (or FOR LOOP).
The following statements are not supported on DB2 for z/OS v7:
For both databases you can declare error handlers that will determine what action are taken if an SQL statement fails with a particular error code (SQLCODE or SQLSTATE).
The general syntax is common:
DECLARE action HANDLER FOR specific or general condition values SQL-procedure statement.
While on DB2 on z/OS action can be only CONTINUE or EXIT, DB2 on distributed platforms supports UNDO.
Also, DB2 on z/OS requires that you specify HANDLER for each condition, while DB2 on distributed platforms supports one HANDLER for multiple conditions. In addition, DB2 on z/OS does not support nested compound statements, so putting multiple SQL statements in BEGIN..END block is not supported. For example, the following handler declaration from DB2 on distributed:
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN SET v_sqlcode = SQLCODE; SET v_msg = ‘ERROR/WARNING’; END;
would need to be changed for DB2 on z/OS as follows:
DECLARE EXIT HANDLER FOR SQLEXCEPTION IF (1=1) THEN SET v_sqlcode = SQLCODE; SET v_msg = ‘ERROR’; END IF;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING H1: LOOP SET v_sqlcode = SQLCODE; SET v_errmsg = 'WARNING'; LEAVE H1; END LOOP H1;
GET DIAGNOSTICS statement
The GET DIAGNOSTICS statement provides diagnostic information about the last SQL statement (other than a GET DIAGNOSTICS statement itself) that was executed. While serving the same purpose and sharing almost the same syntax, this statement is quite different between platform and releases.
Start with DB2 on z/OS v7.
DB2 on z/OS only supports return of the number of the rows that were processed by an SQL statement:
GET DIAGNOSTICS v_rowcount = ROW_COUNT;
DB2 on distributed platforms, along with the number of the rows, can return the status (return code) from the calling procedure, and a native DB2 error message that corresponds to the particular SQLCODE/SQLSTATE. So the syntax for this statement on distributed platforms is:
GET DIAGNOSTICS v_rowcount = ROW_COUNT; GET DIAGNOSTICS v_retcode = RETURN_STATUS / DB2_RETURN_STATUS; GET DIAGNOSTICS EXCEPTION 1 v_errmsg = MESSAGE_TEXT / DB2_TOKEN_STRING;
Please note that, while in the previous version of DB2 on distributed platforms, only keywords RETURN_STATUS and MESSAGE_TEXT were supported, version 8 gives you the flexibility to also use DB2_RETURN_STATUS and DB2_TOKEN_STRING.
GET DIAGNOSTICS statement is not limited to the information described above.
GET DIAGNOSTICS statement supports retrieval of almost any fields of SQLCA area, populated by the database for each SQL statement. For the full syntax of this statement please reference to DB2 UDB for z/OS V8 SQL Reference (SC18-7426-00).
This article describes differences between implementation of DB2 stored procedures written in SQL Procedural Language between distributed platform (Linux, UNIX, Windows) and z/OS.
Thanks to Tom Miller from Silicon Valley Laboratory for reviewing the draft of this article and making constructive recommendations.
- DB2 UDB for Linux, UNIX and Windows SQL Reference Volume 1
- DB2 UDB for Linux, UNIX and Windows SQL Reference Volume 2
- DB2 UDB for OS/390 and z/OS V7 Application Programming and SQL Guide, SC26-9933-03
- DB2 UDB for OS/390 and z/OS V7 SQL Reference, SC26-9944-03
- DB2 UDB for z/OS V8 Application Programming and SQL Guide, SC18-7415-01
- DB2 UDB for z/OS V8 SQL Reference, SC18-7426-01
- SQL Reference for Cross-Platform Development