SETLL

Remember the following when using the SETLL operation:

>> If the SETLL operation is not successful (no records found condition), the file is positioned to the end of the file.

>> When end of file is reached on a file being processed by SETLL, another SETLL can be issued to reposition the file.

>> After a SETLL operation successfully positions the file at a record, you retrieve this record by reading the file. Before you read the file, however, records may be deleted from the file by another job or through another file in your job. Thus, you may not get the record you expected. Even if the %EQUAL built-in function is also set on or the resulting indicator in positions 75 and 76 is set on to indicate you found a matching record, you may not get that record. For information on preventing unexpected modification of your files, see the discussion of allocating objects in the iSeries Information Center Programming topic at URL http://www.ibm.com/eserver/iseries/infocenter.

>> SETLL does not cause the system to access a data record. If you are only interested in verifying that a key actually exists, SETLL with an equal indicator (positions 75-76) or the %EQUAL built-in function is a better performing solution than the CHAIN operation in most cases. Under special cases of a multiple format logical file with sparse keys, CHAIN can be a faster solution than SETLL.

*...1....+....2....+....3....+....4....+....5....+....6....+....7...+....

CL0N01Factor1+++++++Opcode(E)+Factor2+++++++Result++++++++Len++D+HiLoEq....

*

* All the 101 records in ORDFIL are to be printed. The value 101

* has previously been placed in ORDER. The SETLL operation

* positions the file at the first record with the key value 101

* and %EQUAL will return '1'.

C

C ORDER SETLL ORDFIL

C

* The following DO loop processes all the records that have the

* same key value.

C

C IF %EQUAL

C DOU %EOF

C ORDER READE ORDFIL

C IF NOT %EOF

C EXCEPT DETAIL

C ENDIF

C ENDDO

C ENDIF

C

* The READE operation reads the second, third, and fourth 101

* records in the same manner as the first 101 record was read.

* After the fourth 101 record is read, the READE operation is

* attempted. Because the 102 record is not of the same group,

* %EOF will return '1', the EXCEPT operation is bypassed, and

* the DOU loop ends.

Deleting Duplicate Record from PF using STRSQL

Few days ago, i faced a problem where my Pf is populated with duplicate records. For that i found a SQL statement and a SOP :-

1) SQL Statement.

Select * From LIB/MEMBER A
Where RRN(A) >
(Select Min(RRN(B)) From LIB/MEMBER B
Where A.LastName = B.LastName)

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

2) Series of Operation

We can use the simple CPYF (Copy File) command.

1. Create the same file under a different name and define the whole field as key. Also define the key as UNIQUE.
2. CPYF the original file to the new file with ERRLVL(*Nomax).This will copy only the FIRST occurence of duplicate records.
3. After the CPYF finished, you could copy the new file's contents back to the original file with MBROPT(*Replace).
4. After that, the original file WILL NOT contain any duplicate records.

Please Convey your Special Thanks to

Mr Shyam Sunder Reddy, (shyamgssr@gmail.com)