The data processing staff of a certain AS/400 installation had an extra-strength headache. Every time they ran many of the daily, weekly, monthly, quarterly, semi-annual, and yearly queries and Open Query File (OPNQRYF) CL programs, they had to change the hard-coded dates in the record selection criteria. The data processing staff maintained a list of the jobs to change and when to change them.
Finally, they eliminated this massive manual effort in a simple way. We think you'll find what they did intriguing and, more importantly, useful.
The data processing staff eliminated this unnecessary work by creating a one-record file of various date fields representing days, weeks, and months back as well as ahead, based on the current system date. They created a join in the queries and OPNQRYF CL programs that had hard-coded date selection by including the one-record dates file. They changed the record selection criteria to select the records based on the appropriate fields from the dates file, rather than on literals. Users could run queries throughout the day, based on the current values of the dates file.
If the query was to select all the previous day's orders, they used the field containing that day's date in the record selection section. If the query was a weekly invoicing query and needed to select all records for one week back, then the date range test contained the date fields representing seven days back and the previous day's date. The OS/400 Job Scheduler ran each job at a specified time each day, week, month, and so on.
The staff submitted the current day's jobs to the nightly job queue (a single-threaded queue) before midnight. At 00:00:01 (one second past midnight) the system job scheduler submitted a CL program that updated the dates file to that same job queue. Jobs that needed dates for the new day were submitted after 00:00:01, again to the same job queue. Submitting all nightly jobs through a single-threaded job queue at the same priority ensured that the last job for the prior day completed before the CL program updated the dates file.
Joining to the one-record file was easy for the data processing staff. They added the dates file to the list of files in the OPNQRYF command or query but didn't specify how to match it to any of the other files. When a file has no join criteria, the system joins all the records of that file to the records from the other file or files. In relational database terminology, this is called a Cartesian product. In this case, there is only one record in the dates file, but that one record is joined to every record in the other files.
1 illustrates how to join the dates file (DTF002PF) to another file (DSALES) using OPNQRYF. The JFLD (join fields) parameter is not coded, so OPNQRYF joins all records in the first file to every record in the second file. In 2, three files are being processed. There is a JFLD parameter because two files other than the dates file are joined by common item number, but there is no mention of the dates file in this parameter.
Figure 1 illustrates how to join the dates file (DTF002PF) to another file (DSALES) using OPNQRYF. The JFLD (join fields) parameter is not coded, so OPNQRYF joins all records in the first file to every record in the second file. In Figure 2, three files are being processed. There is a JFLD parameter because two files other than the dates file are joined by common item number, but there is no mention of the dates file in this parameter.
By giving the other files access to the dates in the one-record file, it's easy to build record selection criteria that you don't have to change before running the job. To select records for the previous day's date, you compare a date field from some data file to the field containing the previous day's date in the one-record dates file, as shown in 1. XACTDT is a transaction date field in a file of sales figures. B1D is the field in the dates file that has the previous day's date.
By giving the other files access to the dates in the one-record file, it's easy to build record selection criteria that you don't have to change before running the job. To select records for the previous day's date, you compare a date field from some data file to the field containing the previous day's date in the one-record dates file, as shown in Figure 1. XACTDT is a transaction date field in a file of sales figures. B1D is the field in the dates file that has the previous day's date.
The dates file has many dates to handle different situations. The OPNQRYF statement in 2 might be scheduled to run every Saturday, and the selected records could be used to create a summary of the week's sales. It selects the records in which the transaction dates are between the values in fields B5D (back 5 days, or the previous Monday) and B1D (back 1 day, or the previous Friday).
The dates file has many dates to handle different situations. The OPNQRYF statement in Figure 2 might be scheduled to run every Saturday, and the selected records could be used to create a summary of the week's sales. It selects the records in which the transaction dates are between the values in fields B5D (back 5 days, or the previous Monday) and B1D (back 1 day, or the previous Friday).
We used OPNQRYF to illustrate how to use the one-record dates file, but this technique works with other relational tools as well. If you want to join the dates file to one other file with Query/400, put the special value *ALL in first field entry of the Specify How to Join Files display. If you want to join the dates file to two or more other files, you don't need the *ALL value. Instead, you join the other files but specify no join criteria for the dates file.
Maybe you'd like to put this power to work for you. You can download the code (either from MC's Web site at www.as400.com/mc/prog or from MC-BBS), compile it, and add it to your daily routine.
3 contains the DDS for the one-record dates file, DTF002PF. This is the file you join to other files. The first three fields-BGNMON, BGNYR, and TODAY-store the first date of the current month, the first date of the current year, and the current date, respectively. The other field names begin with B for back and A for ahead, followed by a number and a letter (D for days, W for weeks, M for months) to indicate length of time. You should have no trouble figuring out the name of a field you need. You may even want to add other fields, such as other "back" and "ahead" fields, first day of quarter, or first day of week. All dates are stored as six-digit zoned numeric fields in YYMMDD format, but you can easily modify this job to accommodate dates of other formats.
Figure 3 contains the DDS for the one-record dates file, DTF002PF. This is the file you join to other files. The first three fields-BGNMON, BGNYR, and TODAY-store the first date of the current month, the first date of the current year, and the current date, respectively. The other field names begin with B for back and A for ahead, followed by a number and a letter (D for days, W for weeks, M for months) to indicate length of time. You should have no trouble figuring out the name of a field you need. You may even want to add other fields, such as other "back" and "ahead" fields, first day of quarter, or first day of week. All dates are stored as six-digit zoned numeric fields in YYMMDD format, but you can easily modify this job to accommodate dates of other formats.
Program DTF001RG, in 4, builds the dates file. Since RPG III does not have date arithmetic, it relies on the power of OPNQRYF to build the back and ahead fields. The calculations OPNQRYF needs to generate the dates are stored in compile time array MF. Upon each iteration of the DO loop, the RPG program calls CL program DTF002CL (see 5). DTF002CL does what RPG III won't do: calculate a date. DTF002CL accepts the date calculation passed to it and calculates the date. Instead of passing the date back to the caller through a parameter, it loads the date into file DTF001PF (see 6) in YYYYMMDD format, so the caller can retrieve it.
Program DTF001RG, in Figure 4, builds the dates file. Since RPG III does not have date arithmetic, it relies on the power of OPNQRYF to build the back and ahead fields. The calculations OPNQRYF needs to generate the dates are stored in compile time array MF. Upon each iteration of the DO loop, the RPG program calls CL program DTF002CL (see Figure 5). DTF002CL does what RPG III won't do: calculate a date. DTF002CL accepts the date calculation passed to it and calculates the date. Instead of passing the date back to the caller through a parameter, it loads the date into file DTF001PF (see Figure 6) in YYYYMMDD format, so the caller can retrieve it.
When DTF002CL ends, the RPG program opens physical file DTF001PF, retrieves the calculated date, and moves it, right-aligned, into the corresponding element of array OD. Array OD is really the file DTF002PF remapped as an array through an externally described data structure. When all dates have been calculated, DTF001RG writes one record into DTF002PF.
You'll find a CL program to drive the job in 7. This is the program you would have the job scheduler call every day at just past midnight. Program DTF001CL first builds a one-record blank file in QTEMP called DTF000PF. This file is read by the OPNQRYF command in DTF002CL, even though OPNQRYF doesn't use its data. The purpose of this file is to force OPNQRYF to generate exactly one output record, which is copied to file DTF001PF. Next, DTF001CL clears out the existing one-record dates file and calls the RPG program to regenerate it.
You'll find a CL program to drive the job in Figure 7. This is the program you would have the job scheduler call every day at just past midnight. Program DTF001CL first builds a one-record blank file in QTEMP called DTF000PF. This file is read by the OPNQRYF command in DTF002CL, even though OPNQRYF doesn't use its data. The purpose of this file is to force OPNQRYF to generate exactly one output record, which is copied to file DTF001PF. Next, DTF001CL clears out the existing one-record dates file and calls the RPG program to regenerate it.
To run this job in your shop, compile the DDS for the physical files, then compile the programs, and, last, add an entry to the job scheduler to CALL DTF001CL at one second past midnight. Once this job is in daily operation, you can modify jobs to use file DTF002PF and schedule them appropriately.
The people who developed this job were very successful with it, but that is not to say that things never went wrong. Occasionally, users lost reports or needed to regenerate reports for a previous date. The best way the programmers found to handle such requests was to copy the query or CL program that had to be rerun into a temporary library, modify the copy with hard-coded dates, execute it, and discard it.
You may want to have critical jobs double-check that the dates file was updated as it should have been. Such programs should read the record in the dates file and compare field TODAY to the system date. If the dates are the same, the program can proceed normally.
Things can also get out of kilter if jobs come out of the night job queue in the wrong order. Make sure that the jobs for the new day don't go into the job queue at a higher priority than the job that updates the dates file.
Now, you have a nifty program to help with the operations in your shop. It's not the only way to solve this problem, of course, since OPNQRYF, Query/400, and RPG IV (ILE RPG) handle date arithmetic. However, using the Cartesian product is a good method because it removes the date arithmetic from your queries. You can also use the Cartesian product technique to pass other information, such as a report title, into a report.
There's a moral to this story. The gurus of the manufacturing world constantly remind us that it is foolish to improve a process that we can eliminate. Instead of making it easier to change hard-coded dates in recurring jobs, the data processing staff in this story eliminated the need to do so. Have you been improving tasks that you should eliminate? It's worth thinking about.
Rich Grega, CDP, is the information services supervisor for CorTec, a manufacturer of reenforced fiberglass panels in Washington Court House, Ohio. He has 29 years of experience in data processing.
Ted Holt is an associate technical editor for Midrange Computing. He can be reached by E-mail at
Flexible Date Selection with OPNQRYF
Figure 1: Joining the Dates File to One Other File
OPNQRYF FILE((DSALES) (DTF002PF)) + FORMAT(DSALES) + QRYSLT('XACTDT *EQ B1D') + KEYFLD((STORE#) (ITEM#))
Flexible Date Selection with OPNQRYF
Figure 2: Joining the Dates File to Two Other Files
OPNQRYF FILE((DSALES) (ITEMS) (DTF002PF)) + FORMAT(DTF101PF) + QRYSLT('XACTDT *EQ %RANGE(B5D B1D)') + KEYFLD((STORE#) (ITEM#)) + JFLD((1/ITEM# 2/ITEM#)) + MAPFLD((ITEM# '1/ITEM#'))
Flexible Date Selection with OPNQRYF
Figure 3: DDS for Dates File DTF002PF
*=============================================================== * To compile: * * CRTPF FILE(XXX/DTF002PF) SRCFILE(XXX/QDDSSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+ .. 4 ...+... 5 ...+... 6 ...+... 7 A R RC TEXT('Today''s dates ') A BGNMON 6S 0 COLHDG('1st day' 'of month') A BGNYR 6S 0 COLHDG('1st day' 'of year') A TODAY 6S 0 COLHDG('Current' 'date') A B1D 6S 0 COLHDG('Back' '1' 'day') A B2D 6S 0 COLHDG('Back' '2' 'days') A B3D 6S 0 COLHDG('Back' '3' 'days') A B4D 6S 0 COLHDG('Back' '4' 'days') A B5D 6S 0 COLHDG('Back' '5' 'days') A B6D 6S 0 COLHDG('Back' '6' 'days') A B1W 6S 0 COLHDG('Back' '1' 'week') A B2W 6S 0 COLHDG('Back' '2' 'weeks') A B3W 6S 0 COLHDG('Back' '3' 'weeks') A B4W 6S 0 COLHDG('Back' '4' 'weeks') A B1M 6S 0 COLHDG('Back' '1' 'month') A B2M 6S 0 COLHDG('Back' '2' 'months') A B3M 6S 0 COLHDG('Back' '3' 'months') A A1D 6S 0 COLHDG('Ahead' '1' 'day') A A2D 6S 0 COLHDG('Ahead' '2' 'days') A A3D 6S 0 COLHDG('Ahead' '3' 'days') A A4D 6S 0 COLHDG('Ahead' '4' 'days') A A5D 6S 0 COLHDG('Ahead' '5' 'days') A A6D 6S 0 COLHDG('Ahead' '6' 'days') A A1W 6S 0 COLHDG('Ahead' '1' 'week') A A2W 6S 0 COLHDG('Ahead' '2' 'weeks') A A3W 6S 0 COLHDG('Ahead' '3' 'weeks') A A4W 6S 0 COLHDG('Ahead' '4' 'weeks') A A1M 6S 0 COLHDG('Ahead' '1' 'months') A A2M 6S 0 COLHDG('Ahead' '2' 'months') A A3M 6S 0 COLHDG('Ahead' '3' 'months') *. 1 ...+... 2 ...+... 3 ...+ .. 4 ...+... 5 ...+... 6 ...+... 7
Flexible Date Selection with OPNQRYF
Figure 4: RPG Program DTF001RG
*=============================================================== * To compile: * * CRTRPGPGM PGM(XXX/DTF001RG) SRCFILE(XXX/QRPGSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 FDTF001PFIF E DISK UC FDTF002PFO E DISK E MF 2 29 40 E OD 29 6 0 * Date file as an array I E DSDTF002PF I 1 174 OD I X'00000000' C HEX00 I X'00000001' C HEX01 C MOVE '0' PSSR 1 * C 3 DO 29 I 30 C CALL 'DTF002CL' C PARM MF,I MAPFLD 40 C OPEN DTF001PF C READ DTF001PF 91 C MOVE DATE1 OD,I C CLOSEDTF001PF C ENDDO * C MOVE OD,3 OD,1 C MOVE '01' OD,1 C MOVE OD,3 OD,2 C MOVE '0101' OD,2 * C WRITERC C MOVE *ON *INLR *=============================================================== * Exception error handling routine *=============================================================== C *PSSR BEGSR * C PSSR IFEQ '0' C MOVE '1' PSSR * C CALL 'QMHMOVPM' C PARM *BLANKS MSGKEY 4 C PARM '*DIAG' MSGTYP 10 C PARM HEX01 TYPCT 4 C PARM '*' STKSTR 10 C PARM HEX01 STKCT 4 C PARM HEX00 ERRDS 4 * C CALL 'QMHRSNEM' C PARM MSGKEY C PARM ERRDS * C ENDIF * C ENDSR'*CANCL' *=============================================================== ** MF *not used* *not used* %CURDATE %CURDATE - %DURDAY(1) %CURDATE - %DURDAY(2) %CURDATE - %DURDAY(3) %CURDATE - %DURDAY(4) %CURDATE - %DURDAY(5) %CURDATE - %DURDAY(6) %CURDATE - %DURDAY(7) %CURDATE - %DURDAY(14) %CURDATE - %DURDAY(21) %CURDATE - %DURDAY(28) %CURDATE - %DURMONTH(1) %CURDATE - %DURMONTH(2) %CURDATE - %DURMONTH(3) %CURDATE + %DURDAY(1) %CURDATE + %DURDAY(2) %CURDATE + %DURDAY(3) %CURDATE + %DURDAY(4) %CURDATE + %DURDAY(5) %CURDATE + %DURDAY(6) %CURDATE + %DURDAY(7) %CURDATE + %DURDAY(14) %CURDATE + %DURDAY(21) %CURDATE + %DURDAY(28) %CURDATE + %DURMONTH(1) %CURDATE + %DURMONTH(2) %CURDATE + %DURMONTH(3)
Flexible Date Selection with OPNQRYF
Figure 5: CL Program DTF002CL
/*==================================================================*/ /* To compile: */ /* */ /* CRTCLPGM PGM(XXX/DTF002CL) SRCFILE(XXX/QCLSRC) */ /* */ /*==================================================================*/ DTF002CL: + PGM PARM(&MAPEXPR) /* Parameters */ DCL &MAPDEF *CHAR 60 DCL &MAPEXPR *CHAR 40 /* Work variables */ DCL &ABEND *LGL VALUE('0') /* Message forwarding variables */ DCL &ERRDS *CHAR 4 VALUE(X'00000000') DCL &MSGKEY *CHAR 4 VALUE(' ') DCL &MSGTYPECT *CHAR 4 VALUE(X'00000001') DCL &MSGTYPE *CHAR 10 VALUE('*DIAG ') DCL &STKCT *CHAR 4 VALUE(X'00000001') DCL &STKSTRPOS *CHAR 10 VALUE('*') MONMSG CPF0000 EXEC(GOTO ABEND) CHGVAR VAR(&MAPDEF) + VALUE('%CHAR((' *CAT &MAPEXPR *TCAT ') "ISO")') OPNQRYF FILE((QTEMP/DTF000PF)) + FORMAT(DTF001PF) + MAPFLD((WORK &MAPDEF) + (DATE1 '%SST(WORK 1 4) *CAT %SST(WORK 6 2) + *CAT %SST(WORK 9 2)')) CPYFRMQRYF FROMOPNID(DTF000PF) TOFILE(DTF001PF) + MBROPT(*REPLACE) FMTOPT(*NOCHK) CLOF OPNID(DTF000PF) RETURN ABEND: /* Resend diagnostic & escape messages to caller */ IF (&ABEND) THEN(RETURN) CHGVAR VAR(&ABEND) VALUE('1') CALL PGM(QMHMOVPM) PARM(&MSGKEY &MSGTYPE &MSGTYPECT + &STKSTRPOS &STKCT &ERRDS) CALL PGM(QMHRSNEM) PARM(&MSGKEY &ERRDS) ENDPGM
Flexible Date Selection with OPNQRYF
Figure 6: DDS for File DTF001PF
*=============================================================== * To compile: * * CRTPF FILE(XXX/DTF001PF) SRCFILE(XXX/QDDSSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+ .. 4 ...+... 5 ...+... 6 ...+... 7 .. A R DTF001RC A DATE1 8
Flexible Date Selection with OPNQRYF
Figure 7: CL Program DTF001CL
/*==================================================================*/ /* To compile: */ /* */ /* CRTCLPGM PGM(XXX/DTF001CL) SRCFILE(XXX/QCLSRC) */ /* */ /*==================================================================*/ DTF001CL: + PGM /* Work variables */ DCL &ABEND *LGL VALUE('0') /* Message forwarding variables */ DCL &ERRDS *CHAR 4 VALUE(X'00000000') DCL &MSGKEY *CHAR 4 VALUE(' ') DCL &MSGTYPECT *CHAR 4 VALUE(X'00000001') DCL &MSGTYPE *CHAR 10 VALUE('*DIAG ') DCL &STKCT *CHAR 4 VALUE(X'00000001') DCL &STKSTRPOS *CHAR 10 VALUE('*') MONMSG CPF0000 EXEC(GOTO ABEND) /* Build file with 1 blank record in QTEMP */ CRTPF FILE(QTEMP/DTF000PF) RCDLEN(1) INZPFM FILE(QTEMP/DTF000PF) TOTRCDS(1) /* Rebuild the dates file */ CLRPFM FILE(DTF002PF) CALL PGM(DTF001RG) RETURN ABEND: /* Resend diagnostic & escape messages to caller */ IF (&ABEND) THEN(RETURN) CHGVAR VAR(&ABEND) VALUE('1') CALL PGM(QMHMOVPM) PARM(&MSGKEY &MSGTYPE &MSGTYPECT + &STKSTRPOS &STKCT &ERRDS) CALL PGM(QMHRSNEM) PARM(&MSGKEY &ERRDS) ENDPGM
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online