Using The Process Scheduler To Send Email & FTP Files

Using The Process Scheduler To Send Email & FTP Files Bradley Smith Computer services University of Central Florida Session number: 8013

Agenda • Changes made to process scheduler to allow for email of log, trace and output files to be emailed back to user • Changes made to process scheduler to allow files either to be sent or retrieved from remote sites via ftp • Implementation/Testing

Objectives • Explain why logging into your database server is a bad idea to retrieve log/trace and output files • Show how programmers need not know UNIX commands to get SQR’s to send/receive files • Show how easy these mods are to place into your environment

Introduction • Environment • Sun E5500, Oracle Database, Student Admin 7.6, Peopletools 7.59 • Have used with any 7.5X tools, and should work with 7.0 tools • Programmers • Little or no knowledge of UNIX • Functional users • Even less knowledge of UNIX

Requirements • Email must deal with MIME attachments • Output files, trace files must follow a filenaming convention, (i.e. BRADSMITH_12345.lis) • CCLTRGEN.SQR required a minor change because as delivered it uses the same file for each run instead of creating a new file each time. • let $FileName = 'CC' || $Letter_Cd || '_' || $PRCS_PROCESS_INSTANCE || '.ltr '

Benefits • Supports using preferred email address from db if available or sends to OPRID@definedhost • Frees up local client machine from processing • Large SQR’s run faster on the server • If COBOL tracing is turned on, users don’t need to involve the system administrator to send that output to them.

Benefits • Output/Log files are saved (until deleted), so one can go back and find the information after the run has completed • Since the server is running the program, sqr.log file that is typically written when run on the client isn’t lost, and the client can submit the same program to run (such as ccltrgen.sqr), with different runcontrol id’s, before waiting for the previous one to finish • CCLTRGEN.SQR uses the sqr.log to put error/problem messages about emplid’s when creating the datafile

Security Concerns • If users were to log into the server directly, all output/log files would be available • Access login/passwords are stored on the server while the process is running

How Is This Done • Modify • prcs_sqr - added code at exit points to send mail message • prcs_bat - added code at exit points to send mail message • prcs_stf - added code to allow each database to have it’s own output directory • Added • prcs_sqr_mail - new program that holds bulk of new sqr related commands • prcs_bat_mail - new program that holds bulk of new COBOL related commands

How Is This Done: prcs_stf • In the routine check_and_build_output() add code to the bottom of the function • This allows each database instance to have an output directory.

How Is This Done: prcs_sqr • Code to call external function to gather the following • Operator’s ID • Database Name • Process Instance Number • Code around all parts of the shell script that exit, to send mail message

How Is This Done: prcs_bat • Similar type function added to program as in prcs_sqr • Capture OPRID • Capture database instance • Add code that sends email message if COBOL program exits

How Is This Done: prcs_*_mail • Function to send email messages • Find log file • Find any output file following standard • Find log file for process • Find trace file if any for process

Requirements • With former mainframe programmers we wanted an easy way for them to test interface programs for sending/receiving data to outside systems and to have those same programs be moved into production, without modification

Notes • Current works only with SQR’s • Keep output filenames all lower case • Since the current working directory when the SQR’s starts up is the output directory, there is no need to use pathnames in the input/output files of the SQR’s • FTP process will place/get files in the current working directory

Benefits • Easy for people to use • No special calls • No “hard coding” things into SQR’s • If source/destination of data change, no program changes • Importing synchronizing, i.e. waiting for remote files to show, in case the remote site is slow in generating data files • Handles both Process & Job Definitions

FTP Control Setup Panel FTP Control ID, to be used on the +S/+G option when in the Process Scheduler Request Window. This value is uppercase, but is case insensitive when used with the +S/+G option. Number of times it will try on locate command. Sleep time between each try. This is the where the remote site file is gotten from or sent to. This area here, because of the scroll bar, will allow you to send/get more than one file to/from the remote system. So if your SQR generates/needs more than one file, you can have multiple entries here. Same as SQR input/output filename. Case sensitive, and should be exactly the same. Also no directories should be here. It should just be a filename.

FTP Importing Synchronizing • The Locate and Delete feature • How it works. The FTP Control ID table is the key to the whole thing. You will place an entry for each remote file you are looking for in the FTP Control Id table

Usage FTP Control ID goes here. The value is case insensitive, as well as the +S/+G (ie could be +sTESTFTP or +gTESTFTP). This must match the FTP Control Id, because if it doesn’t it may run the wrong FTP Control entry, or not run one at all Should always say PSUNX, and have the Server radio button checked Process names associated to this panel group will show here. We usually make the FTP control ID and the SQR name the same

Modifications To prcs_sqr • Add code to process command line arguments to set values for sending or receiving files. • 3 New programs • prcs_ftp_get - shell script to get files • prcs_ftp_send - shell script to send files • prcs_ftp_locate - shell script for locate & delete

Implementation • Download files • Place prcs_* files in the temp directory on server • prcs_sqr_mail, prcs_bat_mail - changes • DEFAULT_EMAIL_HOST • COBOL_TRC - check directory path • prcs_stf,prcs_sqr,and prcs_bat • Use UNIX diff to compare my changes to your own site

Implementation • prcs_ftp_get,prcs_ftp_send and prcs_ftp_locate • Inspect for any changes that may affect your site • When complete, move new version and test • CF_FTP.DAT file into AUD database and migrate like a patch to your test database • Review upgrade tab to determine which objects you may want/not want to upgrade • Check tablespace used by records and adjust for your own site, and create new ftp control tables • Security to menus

Implementation • Go -> PeopleTools -> Process Scheduler -> Use -> Process Types Add string to end of Parameter List - Don’t forget to start it off with a space so that prcs_bat will pick it up

Implementation • Remember that if you place the programs only on the server, the next tools upgrade/server transfer will overwrite the files • For this reason I place the files also on the file server {psver}\src\cbl\unix • prcs_sqr has a mod in it just for ccltrgen, you may want to remove or keep this change

Implementation • Recommend against passing variable via process definition, use job run control panels instead • Shell scripts can get confused over process instance number • Doesn’t affect running of the program, just emailing output files back

Testing • Email • Run dddaudit.sqr/sysaudit.sqr via Utilities • Run ptptedit via Process Scheduler Request - Multiple Process Sample • FTP • Create FTP Control ID for sending and receiving • Place file in server output directory, run any SQR with the +S option • Place file on remote site, run any SQR with the +G option, check for existence of file

Did I Mention The Web Site? http://pegasus.cc.ucf.edu/~brad Questions?

Using The Process Scheduler To Send Email & FTP Files