Hi Friends,

I have got a requirement where in I need to retrieve only current year's data from a table which is having morethan 15 years of data.

When I just query that table, it takes a very long(15 mins and above) to run and finally timesout.

May I know if it is possible to just have current year's data in a temporary table and keep refreshing it on a regular basis and use that file to query current year's data rather than hitting the entire table's data?

Appreciate any assistance.

Thanks
Teju

have your dba's create you a materialized view of current year, and perhaps prior year, and refresh it ever night. let them worry about it.
or ...
APP HOLD myapp
TABLE FILE yourfile
IF YEAR IS &DATEYY
PRINT fields
BY datefield
ON TABLE HOLD AS thisyear FORMAT FOCUS INDEX DATEFIELD
END
APP HOLD
.. and put this job into report caster and run it every night. Where are you located?

yes. you can create a report caster schedule to load the current year data from the master table in to a webfocus temporary table on daily basis and use the temporary table in your report.

Have you checked to see if the table is properly indexed, so retrieval of a specified year is efficient ?

First, isn't WHERE usually recommended over IF for selection? Second, I have found that constructing a WHERE clause using the date format matching the table's date format greatly increases the chance that the selection is done on the data server (SQL in our case) rather than inside of Focus. The data server tends to be more effiricient at this. Third, we have found that sometimes the statistics on the table need to be regenerated more frequesntly. This is a DBA task that makes the indexes work better.

Susannah,

Since I am using the IDMS tables for reporting,
is it that my IDMS admin would be able to help me out in creating materialized view of current year in IDMS?

Since I have never used APP HOLD myapp before, I would like to know what should "myapp" refer to in "APP HOLD myapp"? Or is it just a Keyword?

Thanks in advance
Teju

Rakesh,

Do you mean to suggest the same solution as Susannah mentioned above? Or do you mean to say that I just create a HOLD file with current year's data and schedule it in Report Caster on a daily basis? If yes, willn't the schedule take a long time to run and affect the server's performance?

If none of the above is not what you mean, can you please detail.

Thanks in advance
Teju

Waz & Kevin,

I am using the IDMS tables for reporting and they were already created years ago and they are not indexed.

When I asked the IDMS admin to add the indices to the already existing tables, he said that creating indicies on already existing tables cannot be done and will impact the already existing reports.

In short, the IDMS admin is not ready to create indices in already existing masters.

If possible, please advice me some alternate way which doesnt involve admin.

Thanks
Teju

Thanks
Teju

Susannah,

Also, is it the hold file name that I should use in my original report coding?

I am located in Maryland.

Thanks
Teju

APP HOLD resets the hold directory to your agent
APP HOLD someapp sets the hold directory to your app. but you want to turn that off as soon as your file and its master have been parked in your app; that's what the APP HOLD command does...turns it off.

nope
whenever there is a constant, use IF
IF has no overhead
WHERE has a bucketload of overhead

whenever you want to write a report, you'll TABLE FILE against whatever extract file you've created .
be sure you call it something very identifiable.

Susannah,

As suggested by you, I just created a fex file and scheduled it to run now.

Will see how it works and would get back to you if I have any more questions and need any help regarding this.

Thanks
Teju

I have been touting the advantage/efficiency of IF over WHERE for years and glad to see Susannah that you addressed this question!

Of course, WHERE is necessary in certain conditions where the IF does not work.


Vivian

Hi Susannah,

I just happened to run the schedule and after 3 hours of running, it gave the below message in email format

BTP1010 Schedule Executed Due To NEXTRUNTIME at cob-wfocusp-01.wssc.ad.root:8200
BTP1020 Starting task: billspassthru
BTP1020 Task type: MR Standard Report
BTP1020 Task domain: wsscprod/wsscprod.htm
BTP1020 Retrieving MR report: app/passthru
BTP1020 Connecting to server WSSCPROD with execution id webfocp
BTP1020 Executing focexec.
BTP1020 No report to create.
BTP1020 OUTPUT FILE NOT ALLOCATED: ACCVAL
BTP1020 Task finished.
BTP1010 No report to distribute.


The code I have used in my fex file is as follows:

APP HOLD MMIS
DEFINE FILE CSBILLCH
TYPEBILL/A1=EDIT(I2586_BILL_TYPE_CODE,'9$$');
BILL/A3=DECODE TYPEBILL(1 'ACT' 2 'EST' 3 'CUS' 4 'ADJ' 5 'PAY'
6 'IDC' 7 'MIS' 8 'REF' 9 'FRS');
YY/A2 = EDIT(I2582_BILL_GEN_DATE,'99$$$$');
MM/A2 = EDIT(I2582_BILL_GEN_DATE,'$$99$$');
NY/I3 = EDIT(YY);
NM/I3 = EDIT(MM);
CRAPFY/I3 = IF NM GE 07 THEN NY + 1 ELSE NY;
FY/A2 = EDIT(CRAPFY,'$99');
END
TABLE FILE CSBILLCH
COUNT I2511_CODE_ID_CSACCT/I9 AS 'ACCT1'
BY FY
BY MM
BY I2582_BILL_GEN_DATE
IF I2586_BILL_TYPE_CODE NE 5$$
-*ACROSS BILL AS ''
IF FY EQ 14
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS ACCVAL FORMAT FOCUS INDEX I2582_BILL_GEN_DATE
END

APP HOLD


Please advice on what's wrong with my code.

Thanks
Teju

try running something very small, that has only 10 or so lines in it
IF RECORDLIMIT IS 10
and get your mechanics down first.

What ever path you use, I think you need to understand the IDMS table and what is indexed.

Perhaps there are other ways to get this data out utilising the struncture that is currently there.

Perhaps you could also consider non FOCUS methods to extract the data.


Also, I think your admin is a bit lazy.

quote:

ON TABLE HOLD AS ACCVAL FORMAT FOCUS INDEX I2582_BILL_GEN_DATE

When indexing a field in format FOCUS, there is a restriction on the length of the name (12 characters IIRC).

Either give the date field a shorter name, or use FORMAT XFOCUS.

From your IF statements your only selections are on a wildcard and a defined field, this is going to impact the speed at which your data is returned.

Can you change your IF statements to use the underlying fields instead?

I'm assuming this line of code is to exclude all I2586_BILL_TYPE_CODE that start with a 5:

IF I2586_BILL_TYPE_CODE NE 5$$

have you tried

WHERE (I2586_BILL_TYPE_CODE LT '500') OR (I2586_BILL_TYPE_CODE GE '600');

to see if it runs any faster?

Similarly with your FY selection, can you use the database date field instead?

I think it is good to know how your TABLE-request is translated to SQL.
With all the EDIT's in the DEFINE I wonder if this will be translated to efficient SQL.

Put these statements just before your TABLE request. The request will not be
executed; just a SQL-statement will be generated.


SET TRACEOFF = ALL
SET TRACEUSER = ON
SET TRACEWRAP = 132
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET XRETRIEVAL = OFF


Then I will use IF more than WHERE going forward. I trust the experts from FocalPoint more than the documentation. The documentation that says (note the Tip): Release 7.7.03 > Reporting Language > Creating Reports With WebFOCUS Language > Selecting Records for Your Report
Selecting Records Using IF Phrases
How to: Select Records Using the IF Phrase

The IF phrase selects records to be included in a report, and offers a subset of the functionality of WHERE. For a list of supported IF operators, see Using Operators in Record Selection Tests.

Tip: Unless you specifically require IF syntax (for example, to support legacy applications), we recommend using WHERE.

and we would recommend just the opposite.
IF works when comparing a field to a constant
WHERE is needed for anything more complicated eg field to field, field to expression, testing against computed field values, etc

susannah --

From what I have observed, the TABLE parser in effect converts WHERE to IF when that is possible
-- i.e., it consistently treats

   WHERE field <op> constant;

and

   IF field <op> constant

identically (in terms of generated SQL, and internal processing for local files).

Do you have a counter-example?

Sussanah,

The RC job ran well but when I queried the hold file ACCVAL as below, I got the error message

0 ERROR AT OR NEAR LINE 8 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC036) NO DATA FOUND FOR THE FOCUS FILE NAMED: ACCVAL


TABLE FILE ACCVAL
PRINT
*
WHERE RECORDLIMIT EQ 10
END
-EXIT


I see the accval.mas file created with SUFFIX=XFOC. But I would like the master file to be created with the SUFFIX=IDMSR. Please let me know how to go about it?

Thanks in advance

Teju

Hi FreSte,

I have already tried turning trace on 2 months back and was warned by my admin not to use it any more because it consumes lot of memory and is hitting the performance of the server.

But turning traces on do help a lot.

Thanks
Teju

Hi Tewy,

I haven't tried using WHERE but I would try doing that now. I never thought that there is such huge difference between WHERE and IF.

Anyways thanks a lot for your valuble suggestion.

Thanks
Teju

Waz,

Can you please suggest any non-FOCUS method in which I can extract the data?

Thanks
Teju

You will definitely want to use tracing from time to time. However you will want to only turn it on as you are using it. There is a lot of data that can accumulate over time when tracing is on. You can also clean up the trace files after you get what you need.

Sheesh... All those years I was using IF and then I took some WF courses from IBI and they recommended WHERE except in defines and computes.... grrrrrrrrrrr

constants in both examples.
you're missing the point