Reading files (AS/400 IBMi)

 You can read the contents of other files (physical and logical) directly in the transformation to retrieve external data. To do so, you would use two steps, declaration and use.

You can declare a file in the declaration window (3).

The syntax for declaring a file is as follows:

FILE alias LIKE library/File   

The alias is used to reference the file buffer and its fields.

When you exit the declaration window, a new buffer named F.alias is added to the list (1).

For example, if you enter:

/* Declaration to access a file
FILE CUSTOMER LIKE ADVENTURE/DIMCUST

The F.CUSTOMER buffer is added. It contains the following fields:

• F.CUSTOMER.@EOF@
• F.CUSTOMER.CUSTKEY
• all other files’ fields

The fact that the ADVENTURE/DIMCUST file name is entered against the LIKE clause does not mean that it is the file read, but only that the file read will have the same structure.

Each time a file is read, you would specify the file name as a constant or in a variable. In this way, the name of the file (library/file and possibly member) may depend on the contents of a field. 

The Talend Change Data Capture engine for AS/400 IBMi handles file opening for up to 1000 files.

You can check in the field list that a column shows which are the fields that comprise the reading key and in which order. 

If the physical does not have a key, you can read a logic that does have one.

There are numerous read instructions so as to cover all the capabilities of IMBi.

READfirst b.Field1/Constant ON alias 

This reads the first record of the file in the F.alias buffer.

Example: 

/* Declaration to access a file
FILE CATEG LIKE RAFALE/CATEG   
 
/* Reads the first record of the RAFALE/CATEG file
READfirst ‘RAFALE/CATEG’ ON CATEG
/* Copies the contents of the DESCRIPTION field in the CATEG file to the O.CATEGDESC field
O.CATEGDESC = F.CATEG.DESCRIPTION

This reads the first record in the RAFALE/CATEG file, retrieves the content of the DESCRIPTION field in this record, and enters it in the CATEGDESC field in the output buffer.

READlast b.Field1/Constant ON alias

This reads the last record of the file in the F.alias buffer.

Example:

/* Declaration to access a file
FILE CATEG LIKE RAFALE/CATEG   
 
/* Reads the last record of the RAFALE/CATEG file
READlast ‘RAFALE/CATEG’ ON CATEG
/* Copies the contents of the DESCRIPTION field in the CATEG file to the O.CATEGDESC field
O.CATEGDESC = F.CATEG.DESCRIPTION

This reads the last record in the RAFALE/CATEG file, retrieves the content of the DESCRIPTION field in this record, and enters it in the CATEGDESC field in the output buffer.

READnext b.Field1/Constant ON alias

This reads the record following the current record of the file in the F.alias buffer.

Example:

/* Declaration to access a file
FILE CATEG LIKE RAFALE/CATEG   
 
/* Reads the next record of the RAFALE/CATEG file
READnext ‘RAFALE/CATEG’ ON CATEG
/* Copies the contents of the DESCRIPTION field in the CATEG file to the O.CATEGDESC field
O.CATEGDESC = F.CATEG.DESCRIPTION

This reads the next record in the RAFALE/CATEG file, retrieves the content of the DESCRIPTION field in this record, and enters it in the CATEGDESC field in the output buffer.

READprevious b.Field1/Constant ON alias

 This reads the record preceding the current record of the file in the F.alias buffer.

Example:

/* Declaration to access a file
FILE CATEG LIKE RAFALE/CATEG   
 
/* Reads the previous record of the RAFALE/CATEG file
READprevious ‘RAFALE/CATEG’ ON CATEG
/* Copies the contents of the DESCRIPTION field in the CATEG file to the O.CATEGDESC field
O.CATEGDESC = F.CATEG.DESCRIPTION

This reads the previous record in the RAFALE/CATEG file, retrieves the content of the DESCRIPTION field in this record, and enters it in the CATEGDESC field in the output buffer.

READkey b.Field1/Constant ON alias WITHKEY EQ    TO b.Field2/Constant
                                                  GE       
                                                  GT       
                                                  LE       
                                                  LT       
                                                  NEXTEQ   
                                                  NEWUNIQ  
                                                  PREVEQ   
                                                  PREVUNIQ                                        

This reads the record matching the key criterion for the file in the F.alias buffer.

The following key criteria can be used:

The key may be partial. If, for example, the key for the AS/400 IBMi file you want to read is KEYFIELD1, KEYFIELD2, and KEYFIELD3, you can use only KEYFIELD1, or KEYFIELD1 followed by the first three characters in KEYFIELD2.

If the key of the AS/400 IBMi file is composed of several fields, and you want to search based on the entire key, you can, for example, define a full-length field with sub-descriptions using the BASED clause for the DCL instruction. 

Example:

FILE ORDER LIKE OMBIL_SRC/ORDERL01   
 
/* The key of the ORDERL01 file is STE(2 alpha), CODCLI(10 Num), NUMCDE(9 PACKED)
DCL W.CDE_KEY CHAR 17
  DCL W.CDE_KEY.STE CHAR 2 BASED CDE_KEY POS 1
  DCL W.CDE_KE.CUSTCODE ZONED 10,0 BASED CDE_KEY POS 3
  DCL W.CDE_KEY.NUMCDE PACKED 9,0 BASED CDE_KEY POS 13
 
/* Loads the read key
W.CDE_KEY.STE = S.JRN_MBR
W.CDE_KEY.CODECLI = I.CODECLI
W.CDE_KEY.NUMCDE = I.NUMCDE
 
/* Reads the record with key in the OMBIL_PRD/ORDERL01 file
READkey ‘OMBIL_PRD/ORDERL01’ ON CATEG WITHKEY EQ TO W.CDE_KEY
O.NAME = F.ORDER.NAME

This reads the record in the OMBIL_PRD/ORDERL01 file (note that the name of the library is not the same as in the FILE declaration) with the company's composite key.