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,
WebFOCUS 5.3.3 MRE - Solaris - Sun Web Server - Weblogic
March 02, 2009, 09:06 AM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 04, 2009, 12:14 PM
Govind Jujare
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
WebFOCUS 5.3.3 MRE - Solaris - Sun Web Server - Weblogic
March 04, 2009, 12:18 PM
Govind Jujare
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.
WebFOCUS 5.3.3 MRE - Solaris - Sun Web Server - Weblogic
November 12, 2010, 06:48 AM
Ramkumar - Webfous
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 ;
Thanks,
Ramkumar. WebFOCUS/Tableau Webfocus 8 / 7.7.02 Unix, Windows HTML/PDF/EXCEL/AHTML/XML/HTML5
November 12, 2010, 11:29 AM
Francis Mariani
It's a SQL error, have you looked at the error message information on IBM's site?
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 15, 2010, 12:25 AM
Ramkumar - Webfous
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..
Thanks,
Ramkumar. WebFOCUS/Tableau Webfocus 8 / 7.7.02 Unix, Windows HTML/PDF/EXCEL/AHTML/XML/HTML5
December 03, 2010, 06:40 AM
Ramkumar - Webfous
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.
Thanks,
Ramkumar. WebFOCUS/Tableau Webfocus 8 / 7.7.02 Unix, Windows HTML/PDF/EXCEL/AHTML/XML/HTML5