IBM i Run SQL Scripts – Query Data, Format an Email, and Send It

Today we will look at a process to check a file, located within the IFS, and identify if it was changed within the last 24 hours, compile an email, format the email, and send it. I set this up as a .sql file stored on the IFS to be run at a set time by a scheduled job. The scheduled job calls the .sql file using RUNSQLSTM. This was a fun process to put together and it is one that I continue to add on to and develop. In order for this process to work I had to engage the system function of retrieving the directory info using RTVDIRINF.

Let me give credit where it is due. Because of the great IBM i Community I was able to find various components needed for this.

Email from SQL (Scott Forstie) – found here
Use HTML to Format Email (Simon Hutchinson) – found here
Convert Timestamp (Ted Holt) – found here

Here is the process I followed when setting this up:

I knew I needed to capture the file data within a directory so that I could review it daily for this process to work. I looked to RTVDSKINF to handle this for me. You can run it against a single directory and depending on how many files are in the directory this could take a minute or hours. One challenge I encountered with RTVDSKINF was that the filenames were randomly generated and I needed them to stay consistent. I solved this by specifying the prefix in the command INFFILEPFX(QAEZDFP). (I ran line 5 manually several times to verify that the files would be generated and generated consistently with the same names before running this SQL). This generated two files QAEZDFPO (objects) and QAEZDFPD (directories).

CL: CHGJOB CCSID(37); --change the job to run with with the correct access

CL: DLTF FILE(QAEZDFPO); --file that holds objects
CL: DLTF FILE(QAEZDFPD); --file that holds directories
CL: RTVDIRINF DIR('/FOLDER') INFFILEPFX(QAEZDFP); --the folder to retrieve info on and the prefix on the file names

In order to start clean every time the process ran I delete the two files created by RTVDSKINF (lines 3 and 4 above).
Next I defined variables to hold the results of my query so that I could have more control over the data.

DECLARE var_count INTEGER;
DECLARE var_size CHAR(15);
DECLARE var_bytes CHAR(15);
DECLARE var_file CHAR(15);
DECLARE var_date CHAR(30);
DECLARE var_cmdstmt VARCHAR(2000);
DECLARE var_subject CHAR(50);
DECLARE var_customer CHAR(50);
DECLARE var_task CHAR(50);
DECLARE var_result CHAR(50);
DECLARE var_email CHAR(50);
DECLARE var_disclaimer CHAR(180);

I ran the query to identify if the results would show that a specific file, starting with GEN, was modified within the last 24 hours. I started at directory index 2 because 1 would have been root /. Since the file started with GEN I asked SQL to review the field where it was like ‘Gen%’ to use a wildcard in my search.

SELECT COUNT(*) --collect a count of the results
        INTO var_count --place the results into a variable
        FROM QAEZDFPO --file to search
        WHERE QEZDIRIDX = 2 --start at directory index 2
              AND QEZOBJNAM LIKE 'GEN%' --wildcard search for filename
              AND QEZCHGTIMA >= CURRENT TIMESTAMP - 1 DAY; --take the date field and compare it to today's date minus 1 day

If the results were greater than 1, then more than one filename was present. Send an email noting that so we can adjust the query. (this was not expected behavior. We only wanted to see one filename that started with GEN)
If the results = 1, collect the specific data on the GEN file and have it email me the results
If neither of those was true, send an email identifying that the specific file had not been updated

IF var_count > 1 THEN
... --email with more than one file present
ELSEIF var_count = 1 THEN
... --collect data and email the specific info on the file
ELSE
... --email that the file was not updated
ENDIF;

Before sending the email I populated my variables with custom data and data from the RTVDSKINF tables.
- I wanted to format the size to GigaBytes (GB) and I found the formula to convert bytes to GB was to divide by 1073741824: DEC(DEC(QEZDTASIZE)/1073741824,19,2) the field is of the type BIGINT so I had to convert it to a decimal before the division to decimal conversion would produce the results I wanted (ex. 20969754785 bytes to 19.52GB).
- In addition to that I also wanted to format the timestamp so that it would be more pleasant to read. Thanks to Ted Holt I found VARCHAR_FORMAT for this exact purpose. There are various ways you can structure this but I chose Month-Day-Year Hours(24 hour format):Minutes:Seconds VARCHAR_FORMAT(QEZCHGTIMA,’MM-DD-YYYY HH24:MI:SS’)
- The last thing I wanted to do was to add spacing and color formatting to the body of the email. I found Simon’s article talking about using HTML when sending an SMTP Email and this was a perfect fit. This allowed me to have line breaks and change the font color to red using <br> and <font color=”red”> respectively.

SELECT QEZOBJNAM, --collect the file name
               QEZDTASIZE, --collect the file size in raw format
               dec(dec(QEZDTASIZE)/1073741824,19,2), --collect the file size in GB format
               VARCHAR_FORMAT(QEZCHGTIMA,'MM-DD-YYYY HH24:MI:SS') --collect the date in user friendly format
            INTO var_file,
                 var_bytes,
                 var_size,
                 var_date
            FROM QAEZDFPO
            WHERE QEZDIRIDX = 2
                  AND QEZOBJNAM LIKE 'GEN%'
                  AND QEZCHGTIMA >= CURRENT TIMESTAMP - 1 DAY;
        SET var_cmdstmt = 'SNDSMTPEMM RCP((''' concat var_email concat''' *pri)) 
        			SUBJECT((''' CONCAT var_subject CONCAT ''')) NOTE(('' Filename: ' CONCAT TRIM(CHAR(var_file)) 
					CONCAT '<br> Updated on: ' CONCAT TRIM(CHAR(var_date)) CONCAT '<br> Total size: '
                    CONCAT TRIM(CHAR(var_size)) CONCAT 'GB or ' CONCAT TRIM(CHAR(var_bytes)) CONCAT
                    ' bytes <br><br><font color="red">' CONCAT var_disclaimer CONCAT '</font>'')) CONTENT((*HTML)) ';

Finally I sent the email by calling the string I generated to execute.

CALL qsys2.qcmdexc(var_cmdstmt); --execute the command stored in the character string

Here is the complete code:

CL: CHGJOB CCSID(37);

CL: DLTF FILE(QAEZDFPO);
CL: DLTF FILE(QAEZDFPD);
CL: RTVDIRINF DIR('/FOLDER') INFFILEPFX(QAEZDFP);

BEGIN
    DECLARE var_count INTEGER;
    DECLARE var_size CHAR(15);
    DECLARE var_bytes CHAR(15);
    DECLARE var_file CHAR(15);
    DECLARE var_date CHAR(30);
    DECLARE var_cmdstmt VARCHAR(2000);
    DECLARE var_subject CHAR(50);
    DECLARE var_customer CHAR(50);
    DECLARE var_task CHAR(50);
    DECLARE var_result CHAR(50);
    DECLARE var_email CHAR(50);
    DECLARE var_disclaimer CHAR(180);
    SELECT COUNT(*)
        INTO var_count
        FROM QAEZDFPO
        WHERE QEZDIRIDX = 2
              AND QEZOBJNAM LIKE 'GEN%'
              AND QEZCHGTIMA >= CURRENT TIMESTAMP - 1 DAY;
    SET var_email = 'test@test.com';
    SET var_customer = 'Test';
    SET var_task = 'Backup Transfer:';
    SET var_result = 'file(s) updated.';
    SET var_subject = TRIM(var_customer) CONCAT ' ' CONCAT TRIM(var_task) CONCAT ' ' CONCAT
                TRIM(var_count) CONCAT ' ' CONCAT TRIM(var_result);
    SET var_disclaimer = 'DISCLAIMER: %Enter your disclaimer here%.';
    IF var_count > 1 THEN
        SET var_cmdstmt = 'SNDSMTPEMM RCP((''' concat var_email concat''' *pri)) SUBJECT((''' CONCAT
                    var_subject CONCAT ''')) NOTE((''Results greater than one.'')) ';
    ELSEIF var_count = 1 THEN
        SELECT QEZOBJNAM,
               QEZDTASIZE,
               dec(dec(QEZDTASIZE)/1073741824,19,2),
               VARCHAR_FORMAT(QEZCHGTIMA,'MM-DD-YYYY HH24:MI:SS')
            INTO var_file,
                 var_bytes,
                 var_size,
                 var_date
            FROM QAEZDFPO
            WHERE QEZDIRIDX = 2
                  AND QEZOBJNAM LIKE 'GEN%'
                  AND QEZCHGTIMA >= CURRENT TIMESTAMP - 1 DAY;
        SET var_cmdstmt = 'SNDSMTPEMM RCP((''' concat var_email concat''' *pri)) SUBJECT((''' CONCAT
                    var_subject CONCAT ''')) NOTE(('' Filename: ' CONCAT TRIM(CHAR(var_file)) CONCAT
                    '<br> Updated on: ' CONCAT TRIM(CHAR(var_date)) CONCAT '<br> Total size: '
                    CONCAT TRIM(CHAR(var_size)) CONCAT 'GB or ' CONCAT TRIM(CHAR(var_bytes)) CONCAT
                    ' bytes <br><br><font color="red">' CONCAT var_disclaimer CONCAT '</font>'')) CONTENT((*HTML)) ';
    ELSE SET var_cmdstmt = 'SNDSMTPEMM RCP((''' concat var_email concat''' *pri)) SUBJECT(('''
                CONCAT var_subject CONCAT
                ''')) NOTE((''Check the system for errors.'')) ';
    END IF;
    CALL qsys2.qcmdexc(var_cmdstmt);
END;

This is a sample of the email this produces when the GEN file was successfully updated:

As always please let me know if you have any questions, thoughts, or critiques. Have a great day!

********
EDIT
********
I made some enhancements to this after the initial post.

Enhancements Include:

Send to multiple recipients: To, CC, BCC
Exclude Days of the Week: using dayname(%date field name%)
Prevent Emails from sending if criteria is not met

CL: CHGJOB CCSID(37);

CL: DLTF FILE(QAEZDFPO);
CL: DLTF FILE(QAEZDFPD);
CL: RTVDIRINF DIR('/FOLDER') INFFILEPFX(QAEZDFP);

BEGIN
    DECLARE var_count INTEGER;
    DECLARE var_day CHAR(10);
    DECLARE var_size CHAR(15);
    DECLARE var_bytes CHAR(15);
    DECLARE var_file CHAR(15);
    DECLARE var_date CHAR(30);
    DECLARE var_cmdstmt VARCHAR(2000);
    DECLARE var_subject CHAR(50);
    DECLARE var_customer CHAR(50);
    DECLARE var_task CHAR(50);
    DECLARE var_result CHAR(50);
    DECLARE var_email CHAR(50);
    DECLARE var_cc CHAR(50);
    DECLARE var_cc2 CHAR(50);
    DECLARE var_bcc CHAR(50);
    DECLARE var_disclaimer CHAR(180);
    DECLARE var_recipients CHAR(200);
    SELECT dayname(QEZCHGTIMA), COUNT(*)
        INTO var_day, var_count
        FROM QAEZDFPO
        WHERE QEZDIRIDX = 2
              AND QEZOBJNAM LIKE 'GEN%'
              AND QEZCHGTIMA >= CURRENT TIMESTAMP - 1 DAY
              group by dayname(QEZCHGTIMA);
    SET var_email = 'to@email.com';
    SET var_cc = 'cc@email.com';
    SET var_cc2 = 'cc2@email.com';
    SET var_bcc = 'bcc@email.com';
    SET var_customer = 'Test';
    SET var_task = 'Backup Transfer:';
    SET var_result = 'file(s) updated.';
    SET var_subject = TRIM(var_customer) CONCAT ' ' CONCAT TRIM(var_task) CONCAT ' ' CONCAT
                TRIM(var_count) CONCAT ' ' CONCAT TRIM(var_result);
    SET var_disclaimer = 'DISCLAIMER: %Enter your disclaimer here%.';
    --More than one file found
    IF var_count > 1 THEN
        SET var_cmdstmt = 'SNDSMTPEMM RCP((''' concat var_email concat''') (''' concat var_cc concat''' *cc) (''' concat 
                    var_cc2 concat''' *cc) (''' concat var_bcc concat''' *bcc)) SUBJECT((''' CONCAT
                    var_subject CONCAT ''')) NOTE((''' concat var_count concat ' files were uploaded'')) ';
    --One file found -> provide details
    ELSEIF var_count = 1 THEN
        SELECT QEZOBJNAM,
               QEZDTASIZE,
               dec(dec(QEZDTASIZE)/1073741824,19,2),
               VARCHAR_FORMAT(QEZCHGTIMA,'MM-DD-YYYY HH24:MI:SS')
            INTO var_file,
                 var_bytes,
                 var_size,
                 var_date
            FROM QAEZDFPO
            WHERE QEZDIRIDX = 2
                  AND QEZOBJNAM LIKE 'GEN%'
                  AND QEZCHGTIMA >= CURRENT TIMESTAMP - 1 DAY;
        SET var_cmdstmt = 'SNDSMTPEMM RCP((''' concat var_email concat''') (''' concat var_cc concat''' *cc) (''' concat 
                    var_cc2 concat''' *cc) (''' concat var_bcc concat''' *bcc)) SUBJECT((''' CONCAT
                    var_subject CONCAT ''')) NOTE(('' Filename: ' CONCAT TRIM(CHAR(var_file)) CONCAT
                    '<br> Updated on: ' CONCAT TRIM(CHAR(var_date)) CONCAT '<br> Total size: '
                    CONCAT TRIM(CHAR(var_size)) CONCAT 'GB or ' CONCAT TRIM(CHAR(var_bytes)) CONCAT
                    ' bytes <br><br><font color="red">' CONCAT var_disclaimer CONCAT '</font>'')) CONTENT((*HTML)) ';
    --Exclude days that the file is known to not be available -> email if outside those days that the file is missing
    ELSEIF var_day not in ('Saturday','Sunday') THEN 
        SET var_cmdstmt = 'SNDSMTPEMM RCP((''' concat var_email concat''') (''' concat var_cc concat''' *cc) (''' concat 
                var_cc2 concat''' *cc) (''' concat var_bcc concat''' *bcc)) SUBJECT(('''
                CONCAT var_subject CONCAT
                ''')) NOTE((''Check the FTP process on the host system for errors.'')) ';
    END IF;
    --Check to make sure the statement command is not empty
    IF var_cmdstmt <> '' THEN
        CALL qsys2.qcmdexc(var_cmdstmt);
    END IF;
END;

Here is the complete code:

Share this:

Related