Automating Report Transmission
by Mark Beckstrom
Reader Mark Beckstrom needed to get his 400 to send the results of a database query automatically. His tip tells us how he did it.
We had a need to generate a daily report by AS/400 Query, and E-mail the results to a distant location. We also wanted to automate this process and let the 400 perform the task without intervention.
The problem was to take the results of the query and attach it to an e-mail message.
The solution was a CL program that:
- Runs the query. Note: to avoid "losing" the spooled file, the output should be held (not sent to a live printer).
- Copies the resultant Spool File to a named file.
- Converts this named file to ASCII
- Sends the converted file as an attachment to E-mail.
Here is a simple CL program that accomplished this task.
0001.00 PGM 0002.00 RUNQRY QRY(QRYLIB/QUERY) RCDSLT(*NO) 0003.00 CPYSPLF FILE(QPQUPRFIL) TOFILE(QRYLIB/NAMEDFILE) 0004.00 CPYTOPCD FROMFILE(QRYLIB/NAMEDFILE) TOFLR(WTCMAIN) + 0005.00 TODOC(ASCII.TXT) REPLACE(*YES) 0006.00 SNDDST TYPE(*DOC) + 0007.00 TOINTNET((firstname.lastname@example.org)) + 0008.00 DSTD('Automatic E-mail report') MSG('This + 0009.00 is a test file sent by CL program + 0010.00 QRYLIB/MAILFILE') DOC(ASCII.TXT) FLR(WTCMAIN)+ 0011.00 ENDPGM
The compiled program can then be scheduled to run daily, weekly, etc using the WRKJOBSCDE command.
Any program that produces a spooled output can of course replace the RUNQRY command.
We run this program under OS/400 V4R2, and our computer is configured for e-mail support.
Thanks, Mark. We'll be sending along a Search400 logo shirt as a thank-you for our tip.
Mark Beckstrom serves as the AS400 and MAPICS Administrator for Welding Technology Corporation.