12B - A Reporting Framework for Plex and 2E

1. 12B - A Reporting Framework for Plex and 2E Mark Murphy STAR Base Consulting, Inc.

2. Bio • Mark Murphy • STAR BASE Consulting, Inc. • Cincinnati, OH • Started with AS/400 and 2E in 1989 • Using Plex since 1997 • Experience in Warehousing, Distribution, Retail, Manufacturing and Business Services markets

3. Overview Scenario: Your company has several locations across the US, and all your sales history is stored in a single file. Corporate wants to be able to generate sales reports with the ability to select the effective dates, sort order, and summary level. The Good News: You can do this easily in Plex or 2E, and the performance can even be good. The Approach: By extracting the data to be reported on into a temporary file, and then printing the report from there you can simplify your code, and improve your performance.

4. Overview (continued)

5. The Components • A Work File • A Data Queue • Four Programs • A Control Program • A Prompt Program • An Extract Program • A Report Program

6. The Work File • Known By 5-10 text fields named Key 1, Key 2, … , Key n • Refers to the file being reported on (e.g. Sales History) • Needs only standard views to allow sorting report in any order requested

7. The Data Queue • Used to communicate from the interactive job to the batch job • Keyed data queue used to allow multiple jobs to use it simultaneously • Helps me deal with long parameter strings CRTDTAQ DTAQ(PARMDQ) MAXLEN(128) FORCE(*YES) SEQ(*KEYED) KEYLEN(12) AUTORCL(*YES) TEXT('Job parameter data queue')

8. The Programs • CL to control the Job – required • RPG to prompt for parameters – optional • RPG to extract records – optional • RPG to print report – required • The report will run in batch, and the CL is self submitting

9. A Quick Refresher • When you submit a job, a request message is placed on the job queue that is processed by a program selected from the routing table. • A request message is a plain text string. • If the command being submitted is a CALL, rather than a custom command, parameters are literals. • Numbers passed as packed(15 5) • Everything else treated as a character string that is at least 25 characters long (only 25 characters guaranteed to be cleared)

10. RTVJOBA TYPE(&INTER) NBR(&JOBN) RTVSYSVAL SYSVAL(QTIME) RTNVAR(&TIME) /* Interactive portion of program */ IF COND(&INTER *EQ '1') THEN(DO) /* *** Insert call to Prompt program */ IF COND(&RTNCDE *EQ ' ') THEN(DO) /* Load long parameter in data queue */ CHGVAR VAR(&KEY) VALUE(&JOBN || &TIME) CHGVAR VAR(&PKEY) VALUE('P' || %SST(&KEY 2 11)) CALL PGM(QSNDDTAQ) PARM(PARMDQ *LIBL &PARMLEN &PARM &KEYLEN &PKEY) The Job Control Program

11. Job Control Program /* Submit the report */ SBMJOB CMD(CALL PGM(******) PARM(&KEY)) MSGLOOP: RCVMSG MSGTYPE(*COMP) RMV(*NO) MSGDTA(&MSGDTA) MSGDTALEN(&MSGDTALEN) + MSGID(&MSGID) MSGF(&MSGF) MSGFLIB(&MSGFLIB) IF COND(&MSGID *EQ ' ') THEN(GOTO CMDLBL(DONE)) IF COND(&MSGID *EQ CPC1221) THEN(DO) SNDPGMMSG MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) MSGDTA(%SST(&MSGDTA 1 &MSGDTALEN)) ENDDO /* Message Received */ GOTO CMDLBL(MSGLOOP) DONE: ENDDO /* Prompt Return Code = *BLANK */ /* Send message if report cancelled by user */ ELSE CMD(DO) /* Report cancelled by user */ SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Report cancelled by user') ENDDO /* Report cancelled by user */

12. The Job Control Program /* End of interactive section */ GOTO CMDLBL(OUT) ENDDO /* Batch portion of program */ /* Get long parameter from data queue */ CHGVAR VAR(&PKEY) VALUE('P' || %SST(&KEY 2 11)) CALL PGM(QRCVDTAQ) PARM(PARMDQ *LIBL &PARMLEN &PARM &WAIT EQ &KEYLEN &PKEY &SNDRLEN &SENDER) /* Build Temporary files in QTEMP */ /* *** Insert call to Extract program */ /* *** Insert call to Print program */ OUT: ENDPGM

13. Prompt Program • This program is a UI function with no database IO function • 2E: Prompt Record • Plex OBASE: Process instance UI • Plex Patterns: UIBASIC\Input

14. Extract Program • A fairly simple program scoped against an appropriate view to make the extract easy • 2E: Retrieve object wrapped in an Execute external function • Plex OBASE: Process some instances • Plex Patterns: ProcessGroup • Writes records to be printed to the Work File

15. Print Program • Built over Work File • Approach • Read record from the Work file • Chain out to get any other data necessary (Work file only has keys) • Print record • 2E: Print file • Plex OBASE: Custom Template based on Print Key Break Report • Plex Patterns: Same as OBASE (no report pattern in this library)

16. The Report Pattern - Plex • Report patterns exist only in OBASE • I prefer Business Entity.Standard functions.Print key break report • BUT, headings do not print properly on page breaks • Create an unscoped copy of the function • Add an extra edit point in the Process overflow subroutine • If you use the Patterns library instead of OBASE • Include OBASE in your model and then make the copy • Create a copy from scratch

17. Resources E-mail: mmurphy@starbaseinc.com Phone: (513) 245-0400 The Edge: edgeusergroup.pugmarks.net Plex Wiki: wiki.plexinfo.net 2E Wiki: wiki.2einfo.net

18. Synopsis SQL support for iSeries development in Plex, and for that matter 2E is limited at best, and designing reports that perform acceptably can be a daunting task when the requirements are complex.  This framework provides a way to easily develop high performance reports for your iSeries without the need for creating a lot of extra access paths to support your reports.