SQL Procedure Language: Differences across platforms

Implementing SQL stored procedures on distributed platforms and z/OS

Examine the differences between an implementation of IBM® DB2® Universal Database™ (UDB) stored procedures written in SQL Procedural Language (SQL PL) on distributed platforms (Linux™, UNIX,®, Windows®) and an implementation of IBM DB2 Universal Database (UDB) stored procedures written in SQL PL for z/OS®.

Marina Greenstein (greenstm@us.ibm.com), Certified I/T Specialist, IBM, Software Group

Marina Greenstein photoMarina Greenstein is a Certified Technical Consultant with the DB2 Migration Team. She joined IBM in 1995 and is currently responsible for helping customers migrate from competitive DBMS to DB2 UDB. She authored multiple articles and presented migration methodology and various database migration topics at numerous DB2 Technical conferences and at SHARE.



09 December 2004

Introduction

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:

  1. Local variables declaration
  2. Cursor declaration
  3. Temporary tables declaration
  4. Condition declaration
  5. SQL-control statements

Assignment statement

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.


SQL-control-statement

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:

  • RESIGNAL
  • SIGNAL
  • RETURN

Error handling

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;

or:

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).


Summary

This article describes differences between implementation of DB2 stored procedures written in SQL Procedural Language between distributed platform (Linux, UNIX, Windows) and z/OS.


Acknowledgements

Thanks to Tom Miller from Silicon Valley Laboratory for reviewing the draft of this article and making constructive recommendations.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=32175
ArticleTitle=SQL Procedure Language: Differences across platforms
publish-date=12092004