Calling Stored Procedures in DB2 Under z/OS

A stored procedure is one or more SQL statements or supported third-generation languages (3GLs, such as C) statements that are compiled into a single procedure that exists in DB2. Stored procedures might contain static (hardcoded) SQL statements. Static SQL is optimized better for some DBMS operations. In a carefully managed DBMS environment, the programmer and the database administrator can know the exact SQL to be executed.

SAS usually generates SQL dynamically. However, the database administrator can encode static SQL in a stored procedure and therefore restrict SAS users to a tightly controlled interface. When you use a stored procedure call, you must specify a schema.

SAS/ACCESS support for stored procedure includes passing input parameters, retrieving output parameters into SAS macro variables, and retrieving the result set into a SAS table. (Although DB2 stored procedures can return multiple result sets, SAS/ACCESS Interface to DB2 under z/OS can retrieve only a single result set.)

You can call stored procedures only from PROC SQL.

Basic Stored Procedure Call

Use CALL statement syntax to call a stored procedure.

call "schema".stored_proc

The simplest way to call a stored procedure is to use the EXECUTE statement in PROC SQL. In this example, STORED_PROC is executed using a CALL statement. SAS does not capture the result set.

proc sql;
connect to db2;
execute (call "schema".stored_proc);
quit;

Stored Procedure That Returns a Result Set

You can also return the result set to a SAS table. In this example, STORED_PROC is executed using a CALL statement. The result is returned to a SAS table, SasResults.

proc sql;
connect to db2;
create table sasresults as select * from connection to db2 (call "schema".stored_proc);
quit;

Stored Procedure That Passes Parameters

The CALL statement syntax supports the passing of parameters. You can specify input parameters as numeric constants, character constants, or a null value. You can also pass input parameters by using SAS macro variable references. To capture the value of an output parameter, a SAS macro variable reference is required. This example uses a constant (1), an input/output parameter (:INOUT), and an output parameter (:OUT). Not only is the result set returned to the SAS results table, the SAS macro variables INOUT and OUT capture the parameter outputs.

proc sql;
connect to db2;
%let INOUT=2;
create table sasresults as select * from connection to db2
   (call "schema".stored_proc (1,:INOUT,:OUT));
quit;

Stored Procedure That Passes NULL Parameter

In these calls, NULL is passed as the parameter to the DB2 stored procedure.

call proc('');
call proc("")

call proc(.)
call proc(NULL)

%let charparm=;
call proc(:charparm)

 %let numparm=.;
call proc(:numparm)

Only the literal period and the literal NULL work generically for both DB2 character parameters and DB2 numeric parameters. For example, a DB2 numeric parameter would reject "" and %let numparm=.; would not pass a DB2 NULL for a DB2 character parameter. As a literal, a period passes NULL for both numeric and character parameters. However, when it is in a SAS macro variable, it constitutes a NULL only for a DB2 numeric parameter.

You cannot pass NULL parameters by omitting the argument. For example, you cannot use this call to pass three NULL parameters.

call proc(,,)

You could use this call instead.

call proc(NULL,NULL,NULL)

Specifying the Schema for a Stored Procedure

Use standard CALL statement syntax to execute a stored procedure that exists in another schema, as shown in this example.

proc sql;
   connect to db2;
   execute (call otherschema.stored_proc);
quit;

If the schema is in mixed case or lowercase, enclose the schema name in double quotation marks.

proc sql;
   connect to db2;
   execute (call "lowschema".stored_proc);
quit;

Executing Remote Stored Procedures

If the stored procedure exists on a different DB2 instance, specify it with a valid three-part name.

proc sql;
   connect to db2;
   create table sasresults as select * from connection to db2
          (call otherdb2.procschema.prod5 (1, NULL));
quit;

Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.