Tables and Arrays

ARRAY –

The array is a collection of elements having the same data type and length.

In RPG, we use ‘DIM’ keyword to define array.

There are 3 types of arrays in RPGLE –

• Run time array
• Compile time array
• Pre-runtime array

1. Run time array –

In Run time array, values will be filled to array during the run time only.

If any value is already assigned to array index, it can also be changed.

Fixed format example –

Line 1: Run time array name arr1 is defined with dimension 15 and length 10 having character data type for each element in array.

Line 2: A variable name index is defined with length (2,0) having zoned (numeric) data type.

Line 4: Assigning value to 1st index of array.

Line 5: Assigning value to 2nd index of array.

Line 6: Assigning index variable with value 3.

Line 7: Assigning value to 3rd index of array with the use of variable name index.

Free format example –

2. Compile time array –

In compile time array, values will be filled to array while compiling the program.
Values in compile time array cannot be changed during run time, values will be static in this.

Fix format example –

Line 1: Compile time array arr1 is defined with dimension 5 and length 20 having character data type.

The Keyword CTDATA is used to represent compile time array.
The keyword PERRCD is used to represent number of element values in each row.
Total number of elements (dim) = PERRCD elements * Number of rows.

Line 11: ‘**’ should be at position 1, after this we can give any readable name like (CTDATA arr1)
Line 12: Compile time array value for 1st index, as PERRCD is ‘1’ so this complete line will be assigned to 1st index.
Line 13: Compile time array value for 2nd index.
Line 14,15,16: Compile time array for 3rd, 4th, 5th index.
Once the program is compiled, all the values will be filled to array name arr1.

Line 5,6,7,8: Array values are used directly as array is filled while compile time.

Free format example:

3. Pre-runtime array –

The compile time array has some restrictions to change the values of array, if we want to change array value then we will need to make changes in program and recompile the program.

In pre-runtime array, we maintain the array elements in a separate file, if we want to change array element then we can change values in file, there is no need to recompile the program.

As array elements will be filled from the separate file so it will be filled while calling the program.

A flat file is a physical file that has a record length and no DDS (Data Description Specification).
Below is the command to create a flat file:

CRTPF FILE(YASHDEV/FLATF1) RCDLEN(20)

RCDLEN means, each record in file can be of length 20.

Below are the values added to the file for test data:

Fix format example:

Line 1: flat file FLATF1 is defined with file type as ‘I’ (input).
File Designation as ‘T’ to indicate an array or table file.
File Format as ‘F’ to indicate a program-described file.
Record Length as ’20’ to use length ’20’ for array element length.

Line 2: Pre-runtime array arr1 is defined with dimension 10 and length 20 having character data type.
Keyword FROMFILE is used to represent array to fill values from FLATF1 file.
Keyword PERRCD is used to represent the number of element values in each record of file.

While calling this program, it will fill the array from file FLATF1, and we can use the array directly.

Line 6,7,8,9: Array values are used directly without any array assignment.

arr(1) will be having value ‘Test value 1’ as per 1st record in file FLATF1 and PERRCD keyword with value ‘1’ for one element in one record.

Note: File Designation ‘T’ is not supported in fully free RPG format.

Tables –

In IBMi, tables are files which contain data in structural format.

There are 2 type of files –

• Physical files
• Logical files

1. Physical files –

Physical files include instructions on how to provide or receive data from a program in addition to the actual data that is kept on the system. They have one or more members and only one record format. Database files may contain externally or program-described records.

Physical files type is ‘*FILE’ and attribute is ‘PF’.

There can be multiple columns and keys in a physical file.

Creating a dds source of physical file: –

• We can create a physical file member dds by command ‘STRSEU’ and press F4 for prompt, below screen will be displayed to give details.
  Source file – Source physical file name where we need to create a physical file.

  Library – Library name in which source file exists.
  Source member – Physical file member name.
  Source type – It should be ‘PF’ for physical file.

  Option – It can be blank for default. There are multiple option values –

  2=Edit a member
  5=Browse a member
  6=Print Member

  Text description – It can be blank for default value. Also, we can give any text for our readability purposes.

Below is the dds source for an example physical file EMPPF-

Line 1: This is a file level keyword ‘UNIQUE’, which is to allow only unique records in this file as per defined key.

Keyword entries are optional in physical file, we can use as per our requirements.

There are 5 columns (EMPNO, EMPNAME, EMPGENDER, EMPEMAIL, EMPDEPT) defined in this physical file example.

There are ‘COLHDG’ keywords at field level, which is useful for readable name of columns.

There is a key EMPNO defined on line 8, which is useful to read the records from this physical file. We can give multiple keys in a physical file as per our requirements.

There are multiple levels for keyword entry –

1. File level entries
2. Record format level entries
3. Field level entries
4. Key field level entries

File level entries – file level entries work on entire file.

Below are the file level entries –

UNIQUE – It indicates that duplicate key values are not allowed

FIFO – It arranges duplicate key values in first-in, first-out order.

LIFO – It arranges duplicate key values in last-in, first-out order.

FCFO – It arranges duplicate key values in first-changed, first-out order

Record format level entries – It work for the defined record format.

Below are the record format level entries –

FORMAT – it shares field descriptions with an existing record format.

Below is the format of this keyword –

FORMAT(LIBNAME/FILENAME)

TEXT – It provides a description of the record or field.

Below is the format of this keyword –

TEXT(‘record format description’)

EDTCDE – It specifies an edit code (for reference function only).

EDTWRD – It provides an edit word (for reference function only).

REFFLD – It copies the field description from the referenced field.

REFSHIFT – It specifies a keyboard shift (for reference function only).

TEXT – It provides a description of the record or field.

TIMEFMT – It specifies the format of a TIME field.

TIMESEP – It specifies the separator used in the formatted TIME field.

VALUES – It provides a list of valid values (for reference function only).

VARLEN – It defines the field as a variable-length field.

Key field level entries – It work for the keys defined in physical file.

Below are the key field level entries –

DESCEND – It arranges records from the highest to the lowest key field value.

SIGNED – It arranges records using the sign portion of the key value.

ABSVAL – It arranges records using the absolute value of the key value.

UNSIGNED – It arranges records without using the sign portion of the key value.

ZONE – It arranges records using only the zone portion of the key value.

NOALTSEQ – It indicates to ignore any alternative collating sequence.

DIGIT – It arranges records using only the digit portion of the key value.

Below is the example (EMPPF1) of using REFFLD keyword –

Here, EMPNO field of EMPPF file is referenced to EMPNBR.

EMPNBR has the same data type and length as EMPNO field of EMPPF file.

We can ignore giving filename to define each field by using REF keyword at file level.

Below is the example of using REF keyword –

Creation of physical file object (Compile physical file)-

‘CRTPF’ is the command to compile physical file member. Type ‘CRTPF’ on command line and press F4 for prompt –

File – Object name for physical file.

Library – Object library in which physical file to be created.

Source file – Source file name in which physical file member is present.

Library – Library name in which source file is present.

Source member – Physical file member name.

Record length – It is used for flat file, it should be blank to compile physical file member.

Or we can use below command to create an object for physical file –

CRTPF FILE(&OBJLIB/&OBJNAME)
SRCFILE(&SRCLIB/&SRCFILE)
SRCMBR(&SRCMBR)

&OBJLIB – Object library in which physical file to be created.

&OBJNAME – Object name for physical file.

&SRCLIB – Library name in which source file is present.

&SRCFILE – Source file name in which physical file member is present.

&SRCMBR – Physical file member name.

CHGPF Command – The Change Physical File command changes the attributes of a physical file and all members of physical file. The changed attributes are used for all members subsequently added to the file unless other values are specified or default for the add operation.

Change Physical File Member (CHGPFM) command is used to change the attributes of a specific member.

Below is the CHGPF command –

CHGPF FILE(&OBJLIB/&OBJNAME)
SRCFILE(&SRCLIB/&SRCFILE)
SRCMBR(&SRCMBR)

&OBJLIB – Object library in which physical file to be created.

&OBJNAME – Object name for physical file.

&SRCLIB – Library name in which source file is present.

&SRCFILE – Source file name in which physical file member is present.

&SRCMBR – Physical file member name.

Other commands –

DSPFD – The Display File Description (DSPFD) command shows one or more types of information retrieved from the file descriptions of one or more database and/or device files.

Below is the DSPFD command to see all details of a physical file object –

DSPFD FILE(&FILELIB/&FILENAME)

&FILELIB – Physical file object library

&FILENAME – Physical file object name

DSPFFD – The Display File Field Description (DSPFFD) command shows, prints, or places in a database file field-level information for one or more files in a specific library or all the libraries to which the user has access.

Below is the DSPFFD command to see all details of a physical file fields –

DSPFFD FILE(&FILELIB/&FILENAME)

DSPDBR – The Display Database Relations (DSPDBR) command provides relational information about database files.

Below is the DSPFFD command to see all details of a physical file database relations –

DSPDBR FILE(&FILELIB/&FILENAME)

Logical files –

In AS/400, logical files are used to provide alternate views of physical files by specifying a different record sequence, selecting specific records, or reordering fields. Here’s a brief overview of the content for logical files:

Record Format Definitions:

Define the record format(s) that the logical file will use. These formats are typically based on the physical file’s record format but can include selected fields or reorganized data.

Key Field Definitions:

Specify key fields for the logical file. These fields determine the order of records in the logical file. The keys can include fields from one or more record formats.

File Type and Attributes:

Indicate the type of logical file (e.g., keyed or arrival sequence) and set attributes such as whether it’s updateable or read-only.

Select/OMIT Conditions:

Define conditions to selectively include or exclude records from the logical file based on specific criteria. This enhances data retrieval efficiency.

Join Logical Files:

If necessary, define join logical files that combine records from multiple physical files based on specified key relationships.

Access Paths:

Specify access paths for the logical file, which can include single-level or multi-level indexes. This helps optimize data retrieval operations.

Override Capabilities:

Utilize override capabilities to customize the behavior of the logical file, such as field renaming, data type conversion, or default values.

Example dds source for non-join logical file –

Line 1: we have used same record format name of physical file EMPPF.
PFILE is the keyword which indicates the logical file is based on EMPPF physical file.
Line 2,3: These are 2 fields which we select from physical file.
For no fields defined in logical file show all the fields.
Line 4: There is 1 key (EMPNO) defined for this logical file.
Line 5: This is definition of omit criteria, this logical file will omit the data in which EMPNO is greater than 30000.
Line 6: This is definition of select criteria, this logical file will select the data in which EMPNO is greater than 20000.

Join-Logical files –

In AS/400, join logical files are used to combine records from multiple physical files based on specified key relationships.

Here’s a basic overview of how you can create join logical files to join EMPPF and EMPPF3:

Below is the dds source for EMPPF physical file –

Below is the dds source for EMPPF3 physical file –

Below is the example dds source for EMPLF1 join logical file.

Line 1: There is a record format name defined EMPLFR and JFILE keyword is defined to join EMPPF and EMPPF3 physical files.
Line 2: There is a JOIN keyword which indicates the sequence of files.
Line 3: There is a JFLD keyword which indicates the fields of both files to join.
Line 4,5: There are EMPNO and EMPNAME fields are from EMPPF physical file.
Line 6,7: There are EMPADDR and EMPMOBNO fields are from EMPPF3 physical file.

Below is the command to compile logical file –

CRTLF FILE(&FILELIB/&FILENAME)

&FILELIB – Logical file object library

&FILENAME – Logical file object name

SQL Equivalent global temporary tables –

Global temporary tables are created in QTEMP library which is different for each session.

It will be used for current application process and cannot be shared with other application processes.

It can be used in SQLRPGLE program or executable interactively.

It can be used to eliminate the use of array or array ds in our application program by creating global temporary tables, writing data at run time and use as per program flow.

Below is the statement of global temporary table –

DECLARE GLOBAL TEMPORARY TABLE TEMP_EMP
(EMPNBR    CHAR(6)   NOT NULL,
EMPSAL    DECIMAL(9, 2),
EMPBONUS  DECIMAL(9, 2),
EMPDEPT   CHAR(10))
ON COMMIT PRESERVE ROWS
NOT LOGGED
RCDFMT TEMP_EMPR;

In above SQL statement, a temporary table will be created in QTEMP library when declare global temporary table statement will be executed.

DECLARE GLOBAL TEMPORARY TABLE is the syntax to create TEMP_EMP table in QTEMP library.

There are 4 columns (EMPNBR, EMPSAL, EMPBONUS, EMPDEPT) defined.

ON COMMIT PRESERVE ROWS indicates that all rows of the table are preserved.

NOT LOGGED indicates that there will be no logs when changes are made to this table.

There is a record format TEMP_EMPR defined using RCDFMT.

We can declare a temporary table by using another table as below.

DECLARE GLOBAL TEMPORARY TABLE TEMP_EMP_1
LIKE LIBNAME/FILENAME
ON COMMIT PRESERVE ROWS
NOT LOGGED
RCDFMT TEMP_EMPR1;

In above SQL statement, we have used LIKE keyword which indicates that this table has all the fields of FILENAME file which is present in LIBNAME library.

Below is the example of creating a global temporary table TEMP_EMP_2 having 3 columns (EMPNBR, EMPNAME, EMPSAL) from FILENAME file which is present in LIBNAME library and data for EMPNBR greater than 1000.

DECLARE GLOBAL TEMPORARY TABLE TEMP_EMP_2 AS
(SELECT EMPNBR, EMPNAME, EMPSAL
FROM LIBNAME/FILENAME
WHERE EMPNBR > 1000)
WITH DATA
LIKE LIBNAME/FILENAME
ON COMMIT PRESERVE ROWS
NOT LOGGED
RCDFMT TEMP_EMPR2;