Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] DB2 Stored Procedure
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] DB2 Stored Procedure
 Login/Join
 
Gold member
posted
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
 
Posts: 85 | Registered: December 20, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 85 | Registered: December 20, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 85 | Registered: December 20, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 394 | Location: Chennai | Registered: December 02, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 394 | Location: Chennai | Registered: December 02, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 394 | Location: Chennai | Registered: December 02, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] DB2 Stored Procedure

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.