Does anybody have syntax on how to make a pass-through sql query to execute a db2 stored procedure (and get results from it) ? I am declaring a cursor in my procedure and trying to get results into a webfocus report.

tia,

This message has been edited. Last edited by: Kerry,

Take a look at the "iWay Adapter Administration for UNIX, Windows, OpenVMS, OS/400, OS/390, and z/OS, Version 5 Release 3.3" manual - "CHAPTER 8 - Using the Adapter for DB2", section "Calling a DB2 Stored Procedure Using SQL Passthru".

There's a section in the v7.6.5 documentation titled "Reporting Against a DB2 Stored Procedure" that describes "Generating a Synonym for a Stored Procedure" that is not in the v5.3.3 documentation. I'm not sure this means you cannot create synonyms (or meta-data) in v5.3 but it's worth a try.

This documentation suggests you can call a stored procedure using SQL Passthru or create meta-data and then use TABLE to retrieve data.

Looks like the entire instruction "SQL DB2 EX P1" had to be in one line.
Otherwise it gives "Cannot parse can't open P1.fex. It may be corrupted".

Now the procedure is getting called (I did a dummy insert). I have to
figure out how to display the results. The following gave an error.

Stored Procedure:

create procedure p1()
language sql
begin
declare c1 cursor with return to client for
select * from tt;
insert into tt values ('t','t','t',10);
open c1;
end
@

Fex:

SQL DB2 EX P1
;

TABLE ON TABLE HOLD AS HT1
END
-RUN

TABLE FILE HT1
PRINT *
END
-RUN

Error:

 0 ERROR AT OR NEAR LINE      1  IN PROCEDURE SQLOUT  FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: @@@@0001
 BYPASSING TO END OF COMMAND
 0 ERROR AT OR NEAR LINE     17  IN PROCEDURE test    FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HT1
 BYPASSING TO END OF COMMAND

Never mind. It works. The Fex had to be :

SQL DB2 EX P1
;

TABLE FILE SQLOUT
PRINT *
END
-RUN

Francis, tx for pointing out the manual.

quote:

SQL DB2 EX P1
;

I tried exactly the same thing. But gave me the error

(FOC1400) SQLCODE IS -104 (HEX: FFFFFF98) XOPEN: 42000
L (FOC1405) SQL PREPARE ERROR.

What could have been the probelem ?

My Proc...

create procedure p1()

language sql

begin

declare c1 cursor with return to client for

SELECT * FROM FMTSTDTAF1.FPRR_BRANDCODE;

open c1;

end


My Fex...

SQL DB2 EX P1
;

It's a SQL error, have you looked at the error message information on IBM's site?

DB2 Version 9.1 for z/OS > Troubleshooting for DB2 > DB2 codes > SQL codes > SQL error codes > -104

But the Procedure is executing correctly. No issues at DB2 end. Still am getting this error while execcuting from Fex.

I had a look at the Error Code description. But DB2 doesnt throw any error while executing the proc from my FEX throws me the above error..

I read the following stuff in the trouble shooting section of Webfocus Server console.

DB2 stored procedures on Z/OS

To use an embedded sql procedure thru our db2 adapter the stored procedure must be compiled and linked into the db2 database. DSNHSQL is the jcl procedure for preparing SQL procedures in batch.

Sample of a embedded sql stored procedure:

CREATE PROCEDURE EDAQA.PROC1 ( )
RESULT SETS 1
COLLID PROC0281
WLM ENVIRONMENT DBY1WLM
LANGUAGE SQL
-----------------------------------------------------------------
-- SQL Stored Procedure
-----------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE CURSOR1 CURSOR WITH RETURN FOR
SELECT
EDAQA.NF29005.SSN5 AS SSN5,
EDAQA.NF29005.LAST_NAME5 AS LAST_NAME5,
EDAQA.NF29005.FIRST_NAME5 AS FIRST_NAME5,
EDAQA.NF29005.BIRTHDATE5 AS BIRTHDATE5,
EDAQA.NF29005.SEX5 AS SEX5

FROM
EDAQA.NF29005;
-- Cursor left open for client application
OPEN CURSOR1;
END P1


What does this mean exactly? Is it not enough if we create and compile a Stored procedure in usual way as we do.


/*DSNHSQL is the jcl procedure for preparing SQL procedures in batch.*/

I could not get on this part as well.