Hello friends,

I am here to get some suggestions on improving the webfocus report performance.

Right now, I am reporting out of mainframe database. The actual master files are present on mainframe database. We are creating synonyms of these master files on developer studio and using these master files for reporting.
I need only a few say 10 fields for my report but there are 30-35 fields present in the actual file. I feel that these unnecessary fields are hitting the report performance.

So, is there any way that I can just extract the fields that I want in my report and report out of it so that my report performance is speeded up?

Thanks for any assistance.

Thanks
Tejovathi

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

how are you extracting the data. cannot you just use a TABLE request and print the 10 fields you are looking for. this will pass the request for only those 10 fields.

The overwhelming likelihood is that any performance hit you're encountering is due to the number of rows, not the number of fields. You can make a master file that only connects to the fields you're interested in, or you can perform a select that only pulls the fields you need, but I don't think you're going to see a change in performance.

Check that the table in question is indexed, check that the index isn't badly fragmented, check that the initial select only pulls records you need and that it performs any aggregation on the server where the data resides. That is where you'll get your big returns on investment.

J.

TexasStingray,

Yes..I am just using TABLE FILE xxxx and printing the 10 fields I want in my report. But I see that the report takes 5 mins to give the output even when the recordlimit = 30.

John_Edwards,

Yes..I agree with you that the performance hit I am facing right now is due to the number of rows because the table I am reporting from has too many rows.

Below captioned is the first TABLE FILE request I am using to report out of table OSC.

TABLE FILE OSC
SUM CNT.CUSTACCT AS 'CUST1211'
BY SACTYRMO
ON SACTYRMO SKIP-LINE COLUMN-TOTAL
BY SACTCODE NOPRINT
ACROSS CTY
ROW-TOTAL
WHERE SACTCODE EQ '0303';
WHERE SACTREAS EQ '001';
WHERE SACTYRMO EQ '2012/11';
IF RECORDLIMIT EQ 70
ON TABLE HOLD AS F1211
END
-RUN
-EXIT

OSC uses a master file named OSC.mas in which I don't see any field indexed.

Thanks
Tejovathi

Look at the table on its native DBMS. If the Index is there the DBMS will use it and your run times will decrease.

J.

The native DBMS used is IDMS. The OSC.mas file has no indexes in its native IDMS at all.

We have a synonym created on webfocus developer studio for the OSC.mas file that is in the native IDMS. So, whatever indices present in the OSC.mas of IDMS will be carried forward to the OSC.mas in WF developer studio.

Is there any way that we can create indicies in OSC.mas file after it is transfered to WF dev studio? Or is it that the indices should be created only in the native IDMS?

Thanks
Tejovathi

The issue here may be because you are running the FEX in Dev Studio with a local synonym and this will be evalulated and a sub-request will then be passed to the mainframe server.

My guess is that the RECORDLIMIT is being evaluated at Dev Studio and not being passed to the mainframe.

How many records are in the MF data source ?

The best way to resolve this may be to remote execute the code, and PCHOLD the results back to Dev Studio.

Waz,

What does remote executing the code mean? How do I go about doing that?

Thanks
Tejovathi

Do you mean to say that I should use PCHOLD instead of HOLD in the above TABLE FILE OSC request?

Thanks
Tejovathi

If I understand your environment you are running a fex locally, and requesting data from a data source on the mainframe via a synonym that points to a master that is on the mainframe.

When you execute code locally the code is evaluated locally and any synonyms that point to a remote data source will have subquery generated and sent to that source.

In this case instead of running the fex locally you can run the FOCUS code on the mainframe.

There are two ways.

1. The fex exists on the mainframe
2. Ship the code to the Mainframe and execute it.

These rely on a WebFOCUS server on the mainframe.

The syntax is as follows:

1. Execute a fex on another WebFOCUs server.

REMOTE DEST = {wfserver name}

REMOTE EX {fex name}


2. Ship the code and execute it.

REMOTE DEST = {wfserver name}

-REMOTE BEGIN
{FOCUS Code}
-REMOTE END

You may also have to supply user and password if the connection is secure and not trusted.
REMOTE USER {user}
REMOTE PASS {password}

The {wfserver name} will be the same as the one you used to create the master file synonyms from the Mainframe.

Forgot to mention, with RMEOTE, the code is run on the Maintframe, so PCHOLD is needed to return the results to the calling PC.

Waz,

I used the -REMOTE commands as you mentioned but got the error below when I executed the code.

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------


NOVIX = 03
DATE 1
DATE1 = 201303
(FOC4340) BIND RUN-UNIT ERROR : 1473
0 ERROR AT OR NEAR LINE 41 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: TST_REMT
BYPASSING TO END OF COMMAND

The code I used is as follows:
(I created a fex file with the below code in WF developer studio and executed it from developer studio.)

SET ASNAMES = ON, HOLDLIST = PRINTONLY
-SET &T2DATE = &YYMD;
-SET &MNID = EDIT (&T2DATE,'$$$$99$$');
-SET &MN_NM = DECODE &MNID (01 'JAN' 02 'FEB' 03 'MAR' 04 'APR'
- 05 'MAY' 06 'JUN' 07 'JUL' 08 'AUG'
- 09 'SEP' 10 'OCT' 11 'NOV' 12 'DEC');
-SET &NOVIX = DECODE &MN_NM (JAN '01' FEB '02' MAR '03' APR '04'
- MAY '05' JUN '06' JUL '07' AUG '08'
- SEP '09' OCT '10' NOV '11' DEC '12');
-TYPE NOVIX = &NOVIX
-SET &DATE1 = EDIT (&T2DATE,'999999');
-TYPE DATE 1
-TYPE DATE1 = &DATE1


-REMOTE BEGIN

DEFINE FILE OSC
ADATE/A8YYMD WITH CUSTACCT = '20' | SACTDATE;
NEWDATE/YYMD = ADATE;
SACTYRMO/YYM = NEWDATE;
END
TABLE FILE OSC
PRINT
CUSTACCT
SACTYRMO
SACTCODE
SACTREAS
CTY
ON TABLE PCHOLD AS TST_REMT
END
-RUN

-REMOTE END

TABLE FILE TST_REMT
PRINT *
ON TABLE PCHOLD FORMAT HTML
END

-RUN
-EXIT

Please let me know if I need to do any corrections to my code.

Thanks
Tejovathi

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

It looks to me like your running your code and creating your HOLD File in the REMOTE instance and then trying to access the hold file from the local instance.

A couple of suggestions.

Move the -RUN to outside the -REMOTE commands, they don't get shipped to the other server.

There seems to be an error with the run on the remote server.

Can you run this code on the MF server ?

I suspect that the remote environment isn't set up correctly.