# Sequential match **URL:** \ **Category:** Forum\ **Tags:** Adabas-Natural, Natural, Natural-Code-Samples, Natural-on-Windows-Unix\ **Created:** [March 24, 2006, 12:26pm UTC](https://techcommunity.softwareag.com/t/sequential-match/60707 "2006-03-24T12:26:53Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Ralph\_Zbrog](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/ralph_zbrog/32/78_2.png) [@Ralph\_Zbrog](https://techcommunity.softwareag.com/u/Ralph_Zbrog)\ **Post date:** [March 24, 2006, 12:26pm UTC](https://techcommunity.softwareag.com/t/sequential-match/60707/1 "2006-03-24T12:26:53Z")
Natural developers automatically think “READ/FIND” when master and transaction files are involved, but a sequential match may be a better solution, dramatically improving performance. When processing large master and transaction files, the nested FIND is initiated many times. Because there is much more overhead involved in starting a FIND than there is in continuing a READ, READing an entire file is much less expensive than FINDing each individual record. A sequential match replaces the nested FIND with a READ WORK. The WORK file is created with a READ LOGICAL or PHYSICAL, which will benefit from Prefetch or Multi-fetch. If you need to find widows (masters with no transactions), you READ MASTER with a nested FIND TRANSACTION. Then to find orphans (transactions with no master), you READ TRANSACTION with a nested FIND MASTER. The results require two complete passes of the two files. Another benefit of a sequential match is that you can find all widows and orphans in a single pass (by Adabas) of each file. In Natural, a sequential match is implemented with two WORK files, or an ADABAS master with a WORK file for the transactions. It may be necessary to flatten an Adabas file to a WORK file. A Prefetched READ PHYSICAL as an extract, followed by a sort, will perform very well for this. It is necessary that the two files be read in the same sequence, and that there is a 1-to-1 or 1-to-many relationship; a many-to-many relationship is not supported. In a one-to-many relationship, the master is defined as the “one” and the transaction file is the “many.” ZZMATCH.NSP is attached. For testing/validation purposes, it is ready to RUN, as is, on mainframe or Windows. Follow the embedded instructions for customization for real-world applications. A fair bit of the code is for demonstration purposes (e.g. IF #TRACE-xxx …) and can be deleted once you are convinced the program is working as expected. To test under Windows, create a text file called ZZMATCHM.TXT from the following data, and drag it to the same Natural library as ZZMATCH. It will appear as a Resource. ```auto 00000050BOSTON FRANK 202 555 1212 ACCTNGENG 00000060SMITH JOHN 714 555 9876 ENTMNTGER 00000065SMITHERS CAROL 229 555 1212 ENTMNTENG 00000070SMYTHE JON 562 555 6543 ENTMNTGER 00000080GERSTNER LOU 204 555 2345 SALE01FRE 00000090SMITH SAM 415 555 6382 SALE01ENG 00000001CAIN ABLE 800 555 1234 TECH01ENG ``` Create a text file called ZZMATCHT.TXT from the following data, and create another Resource, as above. ```auto SMITH ENTMNT07290000025000 SMYTHE ENTMNT00284000059900 GERSTNER SALE0100185000025000 GERSTNER SALE0102974100000000 GERSTNER SALE0103297001500000 GERSTNER SALE0104126800750000 HENDRIX SALE0101184000100000 SMITH SALE0100039701000000 CAIN TECH0101343000100000 SMITH TECH0127032000085500 THOMPSON TECH0100719000785000 THOMPSON TECH0124900000456700 ZILDJAN TECH1300020000012500 ``` You may need to modify the DEFINE WORK FILE statements to correctly point to the two Resources. For IBM mainframes, here is some JCL you can use to test ZZMATCH. ```auto //jobname JOB (acct),'ZZMATCH SAMPLE',CLASS=a,MSGCLASS=x, // MSGLEVEL=(1,1),NOTIFY=userid,REGION=0M //* (c) 2002 LEN Consulting LLC //MATCH EXEC NATURAL //CMPRINT DD SYSOUT=* //CMWKF01 DD * MSTR --- insert MASTER data here --- /* //CMWKF02 DD * TRAN --- insert TRANSACTION data here --- /* //CMSYNIN DD * LOGON ZZUTIL ZZMATCH FIN /* // ``` [ZZMATCH.zip](https://techcommunity.softwareag.com/uploads/short-url/562XqDuvShdWAhALNXReyPUJrT.zip) (4.49 KB) ---
**Author:** ![Lukas\_Hundemer](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/lukas_hundemer/32/113_2.png) [@Lukas\_Hundemer](https://techcommunity.softwareag.com/u/Lukas_Hundemer)\ **Post date:** [March 24, 2006, 3:57pm UTC](https://techcommunity.softwareag.com/t/sequential-match/60707/2 "2006-03-24T15:57:47Z")
You can do the same in Adabas without using work files. The statements ```auto READ (1) file BY descriptor = start-val STARTING WITH ISN = isn MOVE descriptor TO start-val MOVE *ISN TO isn ... END-READ ``` replaces the ```auto READ WORK n ONCE val ``` The nice thing with the “STARTING WITH ISN” option is that it is not doing what it says 😉 Indeed it starts BEHIND the given ISN and not WITH the given ISN. Thus if you have a master loop reading the master info and a subroutine reading the transactions in parallel, the subroutine must save the current descriptor value and the corresponding ISN. Next time when it is called, the subroutine starts either with the same descriptor value and the next higher ISN (if there are multple records with the same descriptor value) or with the next higher descriptor value (otherwise). I.e. the READ (1) reads the transactions in descriptor sequence but one record by one record. ---
**Author:** ![Ralph\_Zbrog](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/ralph_zbrog/32/78_2.png) [@Ralph\_Zbrog](https://techcommunity.softwareag.com/u/Ralph_Zbrog)\ **Post date:** [March 24, 2006, 9:11pm UTC](https://techcommunity.softwareag.com/t/sequential-match/60707/3 "2006-03-24T21:11:23Z")
The READ will perform just as well as the FIND, but overall the READ WORK will perform much better. If the master has a million records, you will initiate a million READ loops, regardless of the “(1)” notation. Using a READ WORK means that you extracted the million records with a single, Prefetched READ loop. You get the same number of records, but initiating a single loop is much fastrer than initiating a million loops. It’s not the point that you can use a READ (1). The point is that you probably should be using a READ WORK. ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [April 2, 2021, 2:40pm UTC](https://techcommunity.softwareag.com/t/sequential-match/60707/4 "2021-04-02T14:40:31Z")