topic Calling a DB2 procedure has OUT parameter using SAS in SAS Procedures

Calling a DB2 procedure has OUT parameter using SAS

deleted_user — Wed, 14 Jan 2009 10:45:41 GMT

Hi,

The book "SAS/ACCESS® 9.1.3 Supplement for DB2 under z/OS (SAS/ACCESS for Relational Databases" is mentioned as below to call a stored procedure which has out parameters

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

By referring the above sample I tried to call the stored procedure SASSP0004 but it throws an error as shown below

148 proc sql;
149 connect to db2(user=Iteration1 password=XXXXXXXXXX
150 database=pbsdev);
151 create table sasresults as select * from connection to db2
152 (call SASSP0004(:SYS_OUT,0,2852700031)) ;
ERROR: CLI prepare error: [IBM][CLI Driver][DB2/NT] SQL0312N The host variable "SYS_OUT" is used in a dynamic SQL statement, a
view definition, or a trigger definition. SQLSTATE=42618
SQL statement: call SASSP0004(:SYS_OUT,0,2852700031).

Procedure Body:
CREATE PROCEDURE SASSP0004 (
OUT SYS_OUT varchar(5),
IN DELIVERY_ID_ST DECIMAL(10,0),
IN DELIVERY_ID_EN DECIMAL(10,0) )
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR with return FOR SELECT * FROM db2table where delv_id between DELIVERY_ID_ST and DELIVERY_ID_EN;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SELECT 'SYS' into SYS_OUT FROM SYSIBM.SYSDUMMY1;
OPEN C1;
END;

Kindly revert with the fix that should be done to the above PROC SQL code.

Re: Calling a DB2 procedure has OUT parameter using SAS

sbb — Wed, 14 Jan 2009 14:13:34 GMT

Not sure if it applies to your problem specifically, but there is a SAS HOTFIX E9D213 which may address your error. Also, the SAS support website http://support.sas.com/ contains useful supplemental reference material and SAS-hosted DOCUMENTATION. Using the SEARCH facility, I found a topic-related technical document (see link below) which also may be useful.

Scott Barry
SBBWorks, Inc.

SAS/ACCESS® 9.1.x Interface to DB2 on the Mainframe
Using the LIBNAME Engine

http://support.sas.com/resources/papers/accessdb2.pdf

Re: Calling a DB2 procedure has OUT parameter using SAS

deleted_user — Thu, 15 Jan 2009 05:21:39 GMT

Thanks for your promptive response.

Infact the doc that you enclosed in your post is mentioned as in the doc that I mentioned previously.

I belive the above code works well in Z/OS environment

Can anyone provide the sample PROC SQL code to call a DB2 procedure that has out parameter in windows environment? Message was edited by: AnjiReddy1729