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,

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.

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.

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

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

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.