Prefetch/Multi Fetch

, , , ,
1

Hi,

Our Natural/ADABAS environment is setup as follows on the mainframe:

  1. One ADABAS database which contains all our Natural Environments (MPM108)
  2. Multiple ADABAS databases (Dev, Test, UAT, Production) each in there own MPM. Production is MPM102

All our Natural programs run from MPM108 and read data from the required database based on the database the DDM is cataloged against.

I am trying to use Multi Fetch on some batch sequential read unload programs against the production database (MPM102) but when I run using the PREFETCH=YES on MPM108 the parameters only apply to MPM108.

My question is, how would I get PREFETCH to be enabled on MPM102 for a Natural program that is running from MPM108 against MPM102 other than turning it on for the entire MPM102 nucleus?

Thanks

Derrick

2

When you want to multi-fetch for just some programs then use the MULTI-FETCH clause
on the READs for these specific programs.

3

Hi Wolfgang,

That is what I have done, but PREFETCH=YES has to be turned on for it to work properly. My problems is that the Natural program is running from MPM108 so turning on PREFETCH there has no impact on the retrieval of the data from MPM102.

4

PREFETCH works at the database level. That’s why you turn it on in your JCL. By default it applies to all sequential READs in your Natural session.

//NAT    EXEC NATBT,...
//DDCARD DD ...
ADARUN PREFETCH=YES
ADARUN PREFSBL=
etc
/*

Statement-level Multi-fetch is handled by Natural.

READ MULTI-FETCH 10
       file BY descriptor

If you are using statement-level Multi-fetch, don’t specify PREFETCH.

5

I am using statement-level MULTI-FETCH but not with BY descriptor.

So in this case PREFETCH will not help me? My jobs seem to be taking a very long time to run, for example, i have one unload which runs for over 12 hours to unload a file with 12.5 million records. The only processing other than reading and writing the output to work files is to process any PE’s and MU’s and write those to separate work files.

Any ideas on how to speed this up?

6

PREFETCH was the first implementation of read-ahead processing for Adabas. The newer version was called MULTI-FETCH. Both were available for a long while, but PREFETCH has been (or soon will be) deprecated.

Multi-fetch is invoked via the PREFETCH ADARUN parameter (same keyword for upward compatibility) or via the MULTI-FETCH parameter of Natural’s READ statement. All three variants of READ (logical, physical, ISN) are supported.

You should expect a dramatic performance improvement using READ MULTI-FETCH 10. Limit your READ to 100,000 records and run the program with and without multi-fetch to verify this.

READ (100000) MULTI-FETCH 10 file
...
READ (100000) file

Performance can be degraded by

  • poor array processing (i.e. your MUs and PEs)
  • large views
  • long field lists in WRITE WORK

to name just a few.

7

Just to add to what Ralph wrote re MUs and PEs. Could you post some of the code that is being used to read the data, and to write to the separate work files? There are some simple “mistakes” that can multiply runtimes by pretty large multiples.

If you could show the views that include the MUs and PEs, it may be that you can make considerable reduction in the 12 hour time you cited.

8

Hi,

I am attaching the source for the program, it does process 11 PE’s / MU’s and creates 12 output files.

Thanks

Derrick
P1030.txt (95.7 KB)

9
  1. The biggest issue is the GET statements. Remove them. Each is duplicating much of the work of the READ.
  • Combine all the fields from VE-FILE into a single view.
  • For each MU and PE, determine the maximum count, either as defined by the application or via a small Natural program.
  • Replace all occurrences of (I:I) with (max) where “max” is the maximum-count for the specific MU or PE

This will return all occurrences of all arrays with a single database access (the READ). With a MF factor of 100, you’ll execute only 125,000 Adabas commands.

Your current code executes 125,000 READs. Presuming only 1 occurrence for each MU/PE, you execute 11 GETs for each - an additional 137.5 million Adabas commands. If each PE/MU has 10 occurrences, you would invoke 1.375 billion GETs.

LOCAL
1 I (I4)
1 J (I4)                                                                                                                       
1 #AGV (N3)  CONST <1>    /* set to max occ for the app
1 #PCV (N3)  CONST <1>    /* set to max occ for the app
1 #PDV (N3)  CONST <1>    /* set to max occ for the app
...
1 VE-FILEV VIEW OF VE-FILE
  2 VEAA-VAC-NO
...
  2 VERC-FUTURE-USE
* VE-FILE-AGV VIEW OF VE-FILE     /* removed
  2 VEAG-MVID-PLUS (#AGV)
...
* VE-FILE-PCV VIEW OF VE-FILE    /* removed
  2 VECA-PART (#PCV)
  2 VECB-PART-DATB-EST (#PCV)
  2 VECC-PART-PRIME-FLAG #PCV)
...
* VE-FILE-PDV VIEW OF VE-FILE   /* removed
  2 VEDA-PLATE-ISSUE (#PDV)
  2 VEDC-PLATE-ITEM-TYPE (#PDV)
  2 VEDD-PLATE-NO (#PDV)
...
  1. Where possible, write fields directly from the view to the work file. This is not COBOL, so you don’t need an intermediate work area.
  • Remove as many MOVE and MOVE EDITED statements as you can.
  • MOVE and MOVE EDITED statements should be limited to NECESSARY format translations. (Such as *ISN, if you don’t like packed decimal in your work files.)
  • Remove the xxx-DATA definitions. Or at least limit the field lists only to support the “necessary format translations.”
  1. Define indices (e.g. I and J) as format I4.

This will give you a much faster (and smaller) program.