Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problem with SQLOUT when calling DB2 Stored Procedure

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Problem with SQLOUT when calling DB2 Stored Procedure
 Login/Join
 
Ryan N
Member
posted
When running the following code:

ENGINE DB2 SET DEFAULT_CONNECTION DTCLNT1
SQL DB2 PREPARE SQLOUT FOR
CALL DTS.PERSISTENCY_SCORE(
DATE('2011-01-01'),
DATE('2011-12-31'),
DATE('2013-06-30'),
'M',
'FIRM_ID',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL);
END
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS RESULTS
END

I get:
0 ERROR AT OR NEAR LINE 23 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

Executing the exact same command directly against the database returns rows. I have used this exact same syntax with a Select query against the same database with no problems.

I'm a WF newbie but have done my due diligence on this topic and not found anything helpful, any help/suggestions are appreciated.

thanks!
RN

This message has been edited. Last edited by: Ryan N,


WF: WebFocus 8.0.02
InfoAssist, Dev Studio, Magnify, Portal/Dashboards, Mobile Favs
Data: DB2
OS: Windows
Output: Multiple
 
Posts: 23 | Location: KC, MO | Registered: August 05, 2013Report This Post
susannah
Expert
posted Hide Post
kc, what's all that 'NULL,' stuff?
humor me...take out all that null stuff,
put a -RUN after your extract's END statement
[caveat: I'm not in 8, i'm oracle, not db2]
SQL DB2 PREPARE SQLOUT FOR
CALL DTS.PERSISTENCY_SCORE(
DATE('2011-01-01'),
DATE('2011-12-31'),
DATE('2013-06-30'),
'M',
'FIRM_ID');
END
-RUN
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS RESULTS
END



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Ryan N
Member
posted Hide Post
Thanks for the suggestion, each NULL is a parameter for the stored procedure, there will be various combinations of the parameters provided when it's called so those have to be there as place holders to ensure that the right values get to the right parameters. I tried anyway and got the same message. Also tried adding -RUN before the TABLE FILE to no avail.


WF: WebFocus 8.0.02
InfoAssist, Dev Studio, Magnify, Portal/Dashboards, Mobile Favs
Data: DB2
OS: Windows
Output: Multiple
 
Posts: 23 | Location: KC, MO | Registered: August 05, 2013Report This Post
augmental
Member
posted Hide Post
I do not get the FOC205 error when using the following but I do receive EDA no data instead of the expected resultset. If this CALL to the SP is done with a utility such as Toad then the expected resultset is returned. There is something in WebFOCUS that cannot traverse / interpret the SP.

SQL DB2 SET SERVER DTCLNT1 ;
FILEDEF PRDATA DISK C:\TEMP\PRDATA.FTM
SQL DB2 PREPARE SQLOUT FOR
SQL DB2 EX DTS.PERSISTENCY_SCORE
'2011-01-01',
'2011-12-31',
'2013-06-30',
'M',
'FIRM_ID',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS PRDATA
END
-RUN

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: May 09, 2013Report This Post
RSquared
Guru
posted Hide Post
I think that you may need to HOLD the output to SQLOUT. See following

/*
TABLE ON TABLE HOLD AS SQLOUT
/*


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
augmental
Member
posted Hide Post
Ryan,

This will get a resultset, I just tried it:

SET SQLENGINE = DB2
SQL DB2 SET SCHEMA DTS ;
SQL DB2 SET SERVER DTCLNT1 ;
SQL DB2 EX DTS.PERSISTENCY_SCORE
'2011-01-01',
'2011-12-31',
'2013-06-30',
'M',
'FIRM_ID',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL;
TABLE FILE SQLOUT
PRINT *
END
-RUN


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: May 09, 2013Report This Post
Ryan N
Member
posted Hide Post
Ultimately the statement
SQL DB2 SET SERVER DTCLNT1 ;
had to be before
SQL DB2 SET SCHEMA DTS ;

Once I switched those two around it worked. We also ended up coding the schema into the stored procedure so that the set schema command was not required at all.


WF: WebFocus 8.0.02
InfoAssist, Dev Studio, Magnify, Portal/Dashboards, Mobile Favs
Data: DB2
OS: Windows
Output: Multiple
 
Posts: 23 | Location: KC, MO | Registered: August 05, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Problem with SQLOUT when calling DB2 Stored Procedure

Copyright © 1996-2020 Information Builders