Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Automating Report Transmission

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:

  1. Runs the query. Note: to avoid "losing" the spooled file, the output should be held (not sent to a live printer).
  2. Copies the resultant Spool File to a named file.
  3. Converts this named file to ASCII
  4. 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)                      
0005.00                           TODOC(ASCII.TXT) REPLACE(*YES) 
0006.00              SNDDST     TYPE(*DOC) +                  
0007.00                           TOINTNET((mbeckstrom@weltronic.com)) +  
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.

Dig Deeper on iSeries system performance and monitoring

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.