Lowercase to Uppercase

There had been few occasions to change the inputs from a command to be converted all upper case for CPP. This is especially true when CL and Query management is used. The query source are set of SQL statements, in which the field values are case sensitive. So when, name 'Subbu' is compared with 'SUBBU', they do not match.

Like always there is a great helper API for thus purpose. It is QCLSCAN. Please refer to the following url for more information on this API.

publib.boulder.ibm.com/iseries/v5r1/ic2924/info/apis/qclscan.htm

The following source when used, converts the inputs parameter passed to all upper case and return the upper case value in the same parameter.

Please use when required.

PGM PARM(&USRIN)
DCL VAR(&USRIN) TYPE(*CHAR) LEN(10)
DCL VAR(&CHARLEN) TYPE(*DEC) LEN(3 0) VALUE(10)
DCL VAR(&STRPOS) TYPE(*DEC) LEN(3 0) VALUE(1)
DCL VAR(&PATTERN) TYPE(*CHAR) LEN(10)
DCL VAR(&PATTERNLEN) TYPE(*DEC) LEN(3 0)
DCL VAR(&TRAS) TYPE(*CHAR) LEN(1) VALUE('1')
DCL VAR(&TRIMTRBL) TYPE(*CHAR) LEN(1) VALUE('1')
DCL VAR(&WILD) TYPE(*CHAR) LEN(1) VALUE('1')
DCL VAR(&OUTPUT) TYPE(*DEC) LEN(3 0)
CALL PGM(QCLSCAN) PARM(&USRIN &CHARLEN &STRPOS +
&PATTERN &PATTERNLEN &TRAS &TRIMTRBL +
&WILD &OUTPUT)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('The +
output is' *BCAT &USRIN)
ENDPGM: ENDPGM

Thank you,
For more help reach me on iseriesconsultant@rediffmail.com

Purpose
Data migration is one of the major and crucial activities for many of core AS400 systems. This is performed by application support team with the active involvement of infrastructure support team. The infrastructure support team must perform the backup before the data migration and the restoration in case the things does not act as it must after the data migration (roll back).
The purpose of this document is to suggest an automatic way of performing the roll back activity after an unsuccessful data migration.

Assumption
• The Responsibility of roll back rests with infrastructure team. (Though it may not be the case always)
• The backup of user libraries or at least the required impacted libraries has been performed prior to data migration.
• Tape is in possession of infrastructure support team and is loaded in the tape drive. (TAP03 is assumed for this coding)• Required authorities to restore the objects, the access to library and access to this program are available
• RESTORE, is the name of library to which the objects are restored.

Note
• TAP03, the tape drive is mentioned in the source code only as an example. Please change it according to your needs.
• AS/400, iSeries and System i, all refer to the same system, as far as the practicality, for this document is concerned.
• The coding is performed on V5R2 system, and any release after this, may have better code handling in place.

Important Terms
• Physical Files – These are database files on AS400 that contain data. Physical file may have more than one member. Every member is a table with rows and columns. A physical file may have logical files.
• Logical files – these files are views for Physical file. They do not contain data, but point to physical file containing data. Logical file must have at least one physical file attached to it.
Constraints
• Logical file cannot be copied to a library without the corresponding Physical file being copied first.• Physical files cannot be deleted until all the associated logical files are deleted.

Program logic
• Information on all the files in the library to be rolled back is dumped to an out file.
• The file is used as a reference to delete the logical (first) and physical (second) files from the library.
• Restore all the files pertaining to library to library ‘RESTORE’.
• Copy the physical files first and then the corresponding logical files to destination library.
• Clear the ‘RESTORE’ library.
• Repeat the same for any further libraries required.

Caution
• Ensure that, there is enough space on the system to have the files restored to ‘RESTORE’ library.
• QINTER subsystem must be down, and you are logged at console as security officer or a powerful profile. Else with users logged in it will create havoc and roll back will be hindered.

Code
The code provided here, as mentioned earlier, is developed on V5R2 system, the release higher to this will have more features to handle. Please have this code as reference and tailor it to your needs.
Program name: DLTRSTFIL This is to core program to be called with the library name as the parameter. The program 1. Deletes the logical file and then the physical files. 2. Restores the files to 'RESTORE' library.3. Moves the files to destination library. (Passed as parameter)4. Clears the 'RESTORE' library. 5. Prints the job log.
PGM PARM(&LIBNAM) DCL VAR(&LIBNAM) TYPE(*CHAR) LEN(10) DCLF FILE(RBKLIB/APPFILES) CLRPFM FILE(RBKLIB/APPFILES) MBR(APPFILES) DSPOBJD OBJ(&LIBNAM/*ALL) OBJTYPE(*FILE) + OUTPUT(*OUTFILE) OUTFILE(RBKLIB/APPFILES) RCVF: RCVF MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(DLTF)) IF COND(&ODOBAT *EQ PF) THEN(DO) CALL PGM(RBKLIB/DLTLF) PARM(&ODOBNM &LIBNAM) DLTF FILE(&LIBNAM/&ODOBNM) ENDDO GOTO CMDLBL(RCVF) DLTF: SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('The + entire deletion opertion is complete') RSTOBJ OBJ(*ALL) SAVLIB(&LIBNAM) DEV(TAPO3) + OBJTYPE(*FILE) RSTLIB(RESTORE) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('The + restoration is complete') CALL PGM(RBKLIB/MOVFILES) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('The + move operation is complete') CLRLIB LIB(RESTORE) SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('The + library restore is cleared.') DSPJOBLOG OUTPUT(*PRINT) ENDPGM

Program name: DLTLF

The program
1. Deletes the logical files and then pass the control to DLTRSTFIL program to delete the corresponding physical file.
2. Handles the exception when no logical files are associated with the physical file.

PGM PARM(&PFNAM &LIBNAM) DCL VAR(&PFNAM) TYPE(*CHAR) LEN(10) DCL VAR(&LIBNAM) TYPE(*CHAR) LEN(10) DCLF FILE(RBKLIB/TESTF) CLRPFM FILE(RBKLIB/TESTF) MBR(TESTF) DSPDBR FILE(&LIBNAM/&PFNAM) OUTPUT(*OUTFILE) + OUTFILE(RBKLIB/TESTF) RCVF: RCVF MONMSG MSGID(CPF0864 CPD0079 CPD0078) EXEC(GOTO + CMDLBL(DLTLF)) IF COND(&WHREFI *EQ ' ') THEN(GOTO + CMDLBL(DLTLF2)) DLTF FILE(&WHRELI/&WHREFI) GOTO CMDLBL(RCVF) DLTLF2: SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('There + are no logicals to this physical file') GOTO CMDLBL(ENDPGM) DLTLF: SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('The + logical files are deleted.') ENDPGM: ENDPGM

Program name: MOVFILES
This program,
1. Move the physical file to the destination library (passed as parameter)
2. Makes the call to program MOVLF to move the Logical files to destination library.

PGM PARM(&LIBNAM) DCL VAR(&LIBNAM) TYPE(*CHAR) LEN(10) DCLF FILE(RBKLIB/APPFILES) CLRPFM FILE(RBKLIB/APPFILES) MBR(APPFILES) DSPOBJD OBJ(RESTORE/*ALL) OBJTYPE(*FILE) + OUTPUT(*OUTFILE) OUTFILE(RBKLIB/APPFILES) RCVF: RCVF MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(ENDPGM)) IF COND(&ODOBAT *EQ PF) THEN(DO) MOVOBJ OBJ(RESTORE/&ODOBNM) OBJTYPE(*FILE) + TOLIB(&LIBNAM) CALL PGM(RBKLIB/MOVLF) PARM(&ODOBNM &LIBNAM) ENDDO GOTO CMDLBL(RCVF) ENDPGM: SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('The + move operation complete. The entire + operation is completed for library' *BCAT &LIBNAM) ENDPGM

Program name: MOVLF

This program moves the Logical files of the corresponding Physical file to destination library.

PGM PARM(&PFNAM &LIBNAM) DCL VAR(&PFNAM) TYPE(*CHAR) LEN(10) DCL VAR(&LIBNAM) TYPE(*CHAR) LEN(10) DCLF FILE(RBKLIB/TESTF) CLRPFM FILE(RBKLIB/TESTF) MBR(TESTF) DSPDBR FILE(&LIBNAM/&PFNAM) OUTPUT(*OUTFILE) + OUTFILE(RBKLIB/TESTF) RCVF: RCVF MONMSG MSGID(CPF0864 CPD0079 CPD0078) EXEC(GOTO + CMDLBL(MOVLF)) IF COND(&WHREFI *EQ ' ') THEN(GOTO + CMDLBL(MOVLF2)) MOVOBJ OBJ(RESTORE/&WHREFI) OBJTYPE(*FILE) + TOLIB(&LIBNAM) GOTO CMDLBL(RCVF) MOVLF2: SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('There + are no logicals to this physical file') GOTO CMDLBL(ENDPGM) MOVLF: SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('The + logical files are moved') ENDPGM: ENDPGM

Conclusion

The document provides one of the automated and relatively simple method to deal with the roll back situation after a unsuccessful data migration. Please feel free to change the code according to your requirements.