Hi,

I am using Mainframes DB2 UDB V7.1. We want to develop a SQL stored procedure, the syntax for which is given below:

CREATE PROCEDURE TSTPROC2
(
IN PROV_ID_IN INTEGER,
OUT PROV_ID INTEGER,
OUT LST_NM CHAR(40),
OUT FST_NM CHAR(20),
OUT EFF_DT DATE
)
LANGUAGE SQL
COLLID NOCOLLID
NO WLM ENVIRONMENT
BEGIN
DECLARE SQLCODE INT DEFAULT 0;
SELECT PROV_ID, LST_NM, FST_NM, EFF_DT
FROM PROV WHERE PROV_ID = PROV_ID_IN;
END

We are able to create an entry in the SYSIBM.SYSROUTINES by executing the above statement in the SPUFI.

But I have the following Queries:

1) Do we need to compile the SQL stored procedure to execute it...???

If we are supposed to compile it, please give the details of the compilation. The manual says if we compile with the program DSNHPC with the HOST(SQL) option, it converts the SQL statements in to equivalent 'C' statements. Then we need to compile & link-edit the generated 'C' statements.

In mainframes, I am not aware of the JCL to compile the 'C' program.

2) When we are trying to invoke the stored procedure from the COGNOS, IMPROPTU tool it is throwing the error message as "Data types not compatiable".

Any information on this regard will be greatly helpful.


Thanks,
Manoj Kumar

I would try to execute the SP via SPUFI first.

Hi Marcus,

Could you please let me know how to execute a SP via SPUFI.

When I tried to execute in the spufi as
"CALL TEST_PROC (IN , OUT)" it is showing an error message as
"SQLCODE = -084, ERROR: UNACCEPTABLE SQL STATEMENT"

Also could you please let me know whether we need to compile the SQL stored procedure to execute it...???


Thanks,
Manoj