Category Archives: SysAdmin

Housekeeping AS400 File PART 1 – Clear Data

Housekeeping is an important part of a system or data. With housekeeping, we expected to maintain system stability and reliability.

The housekeeping method that will be discussed first is to clear files or to clear the entire contents of the record. For this section, if we have several files that can be done by deleting all the data in the file, then the following methods can be used:

Using SQL statement

DELETE FROM LIBA/FILEA

Using Command CLRPFM

If the file is large enough, it is advisable to submit a job with SBMJOB. This command will be executed if there is no locking in the file.

Using Swap Empty File

Prepare a blank file using the CRTDUPOBJ command to another library / temporary library. In the example below, duplicate FILEA from LIBA to LIBB.

Ensure the following conditions:

  • If the file does not contain multiple logical files, duplicate the logical files to a temporary library. To check the logical file in the file, use the DSPDBR command
  • If files in the source library are journaled, the files in the temporary libraries are journalized using the STRJRNPF command

Swap files using the MOVOBJ command, with steps, Move File FILE along with logical files (if any) in LIBA to LIBC (second temporary library), then move FILEA along with logical files (if any) in LIBB to LIBA (empty files are moved to source library).

Of the three methods above in the clear file, I prefer to use the swap object method. But it also depends on how long your application downtime can be accepted. Doing a study first is highly recommended.

Removing Deleted Record on AS400 Physical File

When we delete a record using SQL, the record does not actually disappear immediately from the table / file. There are several ways to delete a deleted record in a file, including:

Using the RGZPFM command, with exclusive lock. This command is suitable for processing files that are not locked continuously.

Pastikan tidak ada locking pada File yang akan di reorganize.

RGZPFM with Share Update, is used if the file to be reorganized has a continuous locks and requires downtime to release the locks.

Copy File (CPYF) Command

AS400 table is a *FILE object, the CPYF command is used to copy all or part of the table structure in the database to another table. The following is the copy file command that I usually use:

Member Option > MBROPT

# MBROPT(*ADD) - Copy all record source files to the target file
# MBROPT(*REPLACE) - Copy all the record source files to the target file starting with delete member files first

Create File > CRTFILE

# CRTFILE(*YES) - Create a new file in the target library
# CRTFILE(*NO) - If file is already available in the target library

Copy From – To Record Number > FROMRCD – TORCD

# FROMRCD (*START/<rrn>) - *START if you want to copy the first record from the source file, or <rrn> relative record number if you want from a specific record number
# TORCD (*END/<rrn>) - *END if you want to copy until the last record from the source file, or <rrn> relative record number if you want to reach a certain record number

Record Format Field Mapping – FMTOPT

This parameter is used if the source file format is different from the target file format
# FMTOPT (*NOCHK) - Copy files based on the length of the existing records
# FMTOPT (*MAP *DROP) - Copy files based on existing field names (*MAP) and ignore non-existent fields (*DROP)

Examples:

# CPYF FROMFILE(SRCLIB/SRCFILE) TOFILE(TGTLIB/TGTFILE) MBROPT(*ADD) CRTFILE(*YES)
# CPYF FROMFILE(SRCLIB/SRCFILE) TOFILE(TGTLIB/TGTFILE) MBROPT(*ADD)
# CPYF FROMFILE(SRCLIB/SRCFILE) TOFILE(TGTLIB/TGTFILE) MBROPT(*REPLACE)
# CPYF FROMFILE(SRCLIB/SRCFILE) TOFILE(TGTLIB/TGTFILE) MBROPT(*ADD) FROMRCD(*START) TORCD(*END)
# CPYF FROMFILE(SRCLIB/SRCFILE) TOFILE(TGTLIB/TGTFILE) MBROPT(*ADD) FROMRCD(10) TORCD(*END)
# CPYF FROMFILE(SRCLIB/SRCFILE) TOFILE(TGTLIB/TGTFILE) MBROPT(*ADD) FMTOPT(*NOCHK) 
# CPYF FROMFILE(SRCLIB/SRCFILE) TOFILE(TGTLIB/TGTFILE) MBROPT(*ADD) FMTOPT(*MAP *DROP) 
                                  

Find WWN (Worldwide port Name) on AS400 Fiber Card

Here’s how you can find out the WWN of a Fiber Card.

From command line, type WRKHDWRSC *STG

On the Work With Hardware Status page, type option 7 = Display resource detail on DCXX that you want to check.

On the Display Resource Details page, press page down until you find the Word wide port name parameter. Congratulations, WWN has been successfully obtained.

AS400 Trace Connection

Trace Connection (TRCCNN) is used to find out information through a network address (IP or domain).
Here is an example of using Trace Connection on the AS400:

TRCCNN SET(ON) TRCTYPE(IP) TRCTBL(TRCCNN) SIZE(500 MB) TCPDTA(TCP () () *N ‘n.n.n.n’)
…change n.n.n.n with the TCP / IP address (if what we want to trace is the IP) of the remote system that we want. In the TCP / IP data section, enter the specific IP and port you want (local or remote).

The above command is only to turn on Trace Connection, to turn it off you can use the command:

TRCCNN SET(OFF) TRCTBL(TRCCNN) OUTPUT(STMF) TOSTMF(‘/tmp/AnyFileName.pcap’ *YES)
… Replace /tmp/AnyFileName.pcap with the desired IFS path and with a .pcap file format.

To read the results of the trace connection, we can use 3rd party applications such as Wireshark and others.

For more details, see the following IBM web: