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] Dynamic Hold File Name

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Dynamic Hold File Name
 Login/Join
 
Member
posted
Hi all,

I'm trying to create a large set of reports that all use the same basic set of data. Because of this, to increase performance, I'd like to store the base data set in a hold file so that I only hit the data source one time as the users switch back and forth between reports.

All of these reports are filtered by a start date and end date that the user selects, so I am creating a HOLD file based on those date values.


Everything else works pretty much as I expect when I run the code the first time, the Hold and master file are generated and I see the data.
File doesn't exist
'RETCODE = ' | -1
15.15.00 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
15.15.00 BR (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
15.15.00 AE SELECT T1."TRADE_DATE",T1."FIRM_BRANCH_ID",T1."LOGON_ID",
15.15.00 AE T1."ACCT_ID",T1."PRODUCT_CAT",T1."COMMODITY_CODE",
15.15.00 AE T1."CONTRACT_MONTH",T1."CONTRACT_YEAR",T1."STRIKE",
15.15.00 AE T1."DAY_LONG",T1."DAY_SHORT",T1."DAY_LONG_TRADE_COUNT",
15.15.00 AE T1."DAY_SHORT_TRADE_COUNT" FROM TSTNYSE1.AGGREGATE_ACCT T1
15.15.00 AE WHERE (T1."TRADE_DATE" BETWEEN TO_DATE('01-03-2010 00:00:00',
15.15.00 AE 'DD-MM-YYYY HH24:MI:SS') AND TO_DATE('01-03-2010 00:00:00',
15.15.00 AE 'DD-MM-YYYY HH24:MI:SS'));
1
0 NUMBER OF RECORDS IN TABLE= 1294 LINES= 1294
0
0 NUMBER OF RECORDS IN TABLE= 1294 LINES= 1294



The second time I run the report, I always get a blank result.
'RETCODE = ' | 0
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

However, I can see the hold file and know it has data. There seems to be an issue finding the file if it exists and after I verify its existence.

The complete code is posted below, thank you for your assistance
 
APP HOLD TPMS
-RUN

-SET &HOLDFILENAME = 'AGGACCTHOLD' | STRIP(10,'&START_DATE.EVAL','/','A8') | STRIP(10,'&END_DATE.EVAL','/','A8');
-SET &FULLHOLD = 'D:\ibi\64bit\apps\tpms\' | '&HOLDFILENAME.EVAL' | '.ftm';
-*-SET &FULLHOLD = 'D:\ibi\64bit\apps\tpms\' | '&HOLDFILENAME.EVAL';

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON

-DOS STATE &FULLHOLD
-*-DOS STATE 'D:\ibi\64bit\apps\tpms\aggaccthold.ftm'
-TYPE 'RETCODE = ' | &RETCODE
-RUN
-IF &RETCODE EQ 0 GOTO NOHOLDNEEDED;

TABLE FILE AGGREGATE_ACCT
PRINT
     FIRM_BRANCH_ID
     LOGON_ID
     ACCT_ID
     PRODUCT_CAT
     COMMODITY_CODE
     CONTRACT_MONTH
     CONTRACT_YEAR
     STRIKE
     DAY_LONG
     DAY_SHORT
     DAY_LONG_TRADE_COUNT
     DAY_SHORT_TRADE_COUNT
WHERE ( TRADE_DATE GE DT(&START_DATE) ) AND ( TRADE_DATE LE DT(&END_DATE) );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS &HOLDFILENAME FORMAT ALPHA
ON TABLE SET HTMLCSS OFF
END

-NOHOLDNEEDED
TABLE FILE '&HOLDFILENAME.EVAL'
-*TABLE FILE AGGACCTHOLD
PRINT
     FIRM_BRANCH_ID
     LOGON_ID
     ACCT_ID
     PRODUCT_CAT
     COMMODITY_CODE
     CONTRACT_MONTH
     CONTRACT_YEAR
     STRIKE
     DAY_LONG
     DAY_SHORT
     DAY_LONG_TRADE_COUNT
     DAY_SHORT_TRADE_COUNT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
     INCLUDE = nfa,
$
ENDSTYLE
END
 

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


Web Focus Version 7.6.8
Win XP 2002
 
Posts: 11 | Registered: June 25, 2009Report This Post
Expert
posted Hide Post
Add a FILEDEF or APP FI statement after the -NOHOLDNEEDED label. When you come into the program the 2nd time, since WebFOCUS is stateless, it doesn't know what the name of the hold file is or where it is located. You have to tell it. You've got the amper variable with the filename in it so it should be fairly easy to do.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
no quotes needed around the filename in the NOHOLDNEEDED section.

TABLE FILE &HOLDFILENAME.EVAL

You might also try a -SET &ECHO=ALL; or a -? & to show you exactly how the parameter values for the filename are being interpreted and used.

And you defintely DO need the FILEDEF as Ginny mentions. I would just put it at the top so it's always set whether or not it's a first request or a subsequent request.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
You should also look at the FOCCACHE option -

WebFOCUS/iWay New Features > Server Enhancements > Core Server and Client Features Foccache: Persistent Directory for Caching Files

quote:
When you want to be able to reuse data within the same browser session, you can store the data in the form of a HOLD, SAVE, or SAVEB file in the foccache directory, which is automatically created when the connection to the server is established. This becomes the first directory in the application path.

As long as the browser session remains active, the stored files remain in the foccache directory and can be referenced in requests using standard two part names. For example, the first request below creates a HOLD file in foccache, which is referenced by the second request


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, 2005Report This Post
Virtuoso
posted Hide Post
As Francis suggests, using the persistent directory FOCCACHE may be the best solution. The FOCCACHE directory is automatically appended to the front of the APP path when the user session begins. I believe the default hold period for files stored in FOCCACHE is 180 minutes - or when the user closes the browser session. The initial TABLEF FILE creates the hold file and master in FOCCACHE and subsequent requests will find it there. The APP QUERY/TABLEF FILE FOCAPPQ step tests for the existence of the hold file master in FOCCACHE. I included the LOCASE function because file names are stored in lower case in my UNIX environment.

-SET &HOLDFILENAME = 'AGGACCTHOLD' | STRIP(10,'&START_DATE.EVAL','/','A8') | STRIP(10,'&END_DATE.EVAL','/','A8');
-SET &TESTNAME = LOCASE(&HOLDFILENAME.LENGTH,&HOLDFILENAME,'A&HOLDFILENAME.LENGTH');
-*
APP QUERY FOCCACHE HOLD
-*
TABLEF FILE FOCAPPQ
 PRINT DATE
 WHERE FILENAME EQ '&TESTNAME' || '.mas';
 ON TABLE SAVE
END
-*
-RUN
-IF &LINES GT 0 GOTO NOHOLDNEEDED ;
-*
TABLEF FILE AGGREGATE_ACCT
PRINT
     FIRM_BRANCH_ID
     LOGON_ID
     ACCT_ID
     PRODUCT_CAT
     COMMODITY_CODE
     CONTRACT_MONTH
     CONTRACT_YEAR
     STRIKE
     DAY_LONG
     DAY_SHORT
     DAY_LONG_TRADE_COUNT
     DAY_SHORT_TRADE_COUNT
WHERE ( TRADE_DATE GE DT(&START_DATE) ) AND ( TRADE_DATE LE DT(&END_DATE) );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS FOCCACHE/&HOLDFILENAME FORMAT ALPHA
ON TABLE SET HTMLCSS OFF
END
-*
-NOHOLDNEEDED
-*
TABLE FILE &HOLDFILENAME
PRINT
     FIRM_BRANCH_ID
     LOGON_ID
     ACCT_ID
     PRODUCT_CAT
     COMMODITY_CODE
     CONTRACT_MONTH
     CONTRACT_YEAR
     STRIKE
     DAY_LONG
     DAY_SHORT
     DAY_LONG_TRADE_COUNT
     DAY_SHORT_TRADE_COUNT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
     INCLUDE = nfa,
$
ENDSTYLE
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
Thank you all for your responses.

I was able to add the line:

FILEDEF &HOLDFILENAME DISK &FULLHOLD

after -NOHOLDNEEDED and I am able to find the correct hold file.


Web Focus Version 7.6.8
Win XP 2002
 
Posts: 11 | Registered: June 25, 2009Report 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] Dynamic Hold File Name

Copyright © 1996-2020 Information Builders