Send email with SQL

We have been able to send email from our IBM i partitions for several releases, using IBM commands and various third party tools. I use the Send SMTP Email command, SNDSMTPEMM, that comes within the IBM i operating system. Within the latest round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, comes another way, with SQL.

A new scalar function was added to the SYSTOOLS library called SEND_EMAIL. It has four parameters:

1. TO_EMAIL:  Recipients email address. Mandatory
2. SUBJECT:  Email's subject line. Mandatory
3. BODY:  Email's body. Mandatory
4. ATTACHMENT: IFS path name of the file to be sent as an attachment. Optional, if omitted no attachment is sent

For example:

01 VALUES SYSTOOLS.SEND_EMAIL 02 (TO_EMAIL => 'simon@email.com', 03 SUBJECT => 'Subject line', 04 BODY => 'Email sent using SYSTOOLS.SEND_EMAIL')

SEND_EMAIL returns a return code value. If statement is successful '1' is returned. If unsuccessful '-1' is returned.

When the above statement was run the following was returned:

00001 ------ 1

As the email address, subject, and body parameters are mandatory I cannot omit them. I also cannot pass a blank email address, see line 2 below:

01 VALUES SYSTOOLS.SEND_EMAIL 02 (TO_EMAIL => '', 03 SUBJECT => 'Subject line', 04 BODY => 'Email sent using SYSTOOLS.SEND_EMAIL')

The above statement returns a SQL code of -438, which is the same as the SQL0438 message.

But I can have blank subject and body parameters:

01 VALUES SYSTOOLS.SEND_EMAIL 02 (TO_EMAIL => 'simon@email.com', 03 SUBJECT => '', 04 BODY => '')

The scalar function returns a successful return code.

00001 ------ 1

I can look in my job's job log, using the JOBLOG_INFO SQL Table function, to see whether my statements were successful:

01 SELECT MESSAGE_ID AS "Msg id", 02 MESSAGE_TYPE AS "Type", 03 MESSAGE_SUBTYPE "Subtype", 04 MESSAGE_TIMESTAMP AS "Timestamp", 05 MESSAGE_TEXT AS "Text" 06 FROM TABLE(QSYS2.JOBLOG_INFO('*')) 07 WHERE MESSAGE_TEXT LIKE 'Send E-mail%'

Lines 1 – 5: The columns I want returned in my results.

Line 6: As the parameter passed to JOBLOG_INFO is '*' this means the current job.

Line 7: I only want rows returned where the message text starts with "Send E-mail".

My job log entries for the above statements returns:

Msg id Type Subtype Timestamp ------- ---------- ----------------- -------------------------- TCP5091 COMPLETION <NULL> 2024-03-09 13:02:08.526958 TCP5092 ESCAPE EXCEPTION HANDLED 2024-03-09 13:03:10.067946 TCP5091 COMPLETION <NULL> 2024-03-09 13:05:29.868575 Text ----------------------- Send E-mail Successful. Send E-mail Failed. Send E-mail Successful.

The last row was my first statement, which was successful. The middle row was the one with the blank email address, which failed. And the top one was with the blank subject and body, which was successful.

And what about attachments? As I said above those can be sent too.

01 VALUES SYSTOOLS.SEND_EMAIL 02 (TO_EMAIL => 'simon@email.com', 03 SUBJECT => 'Email with attachment 1', 04 BODY => 'Email sent with attachment', 05 ATTACHMENT => '/home/MyFolder/test.csv')

I receive a success return code:

00001 ------ 1

What about if I use path that is not valid?

01 VALUES SYSTOOLS.SEND_EMAIL 02 (TO_EMAIL => 'simon@email.com', 03 SUBJECT => 'Email with attachment 2', 04 BODY => 'Email sent using SYSTOOLS.SEND_EMAIL', 05 ATTACHMENT => '/bad_directory_name/somefile.txt')

As the folder "bad_directory_name" does not exist the SQL code -438, SQL0438, is returned.

When I rerun the JOBLOG_INFO statement I can see one successful send, the first statement, and one failed, the second statement:

Msg id Type Subtype Timestamp ------- ---------- --------------- -------------------------- TCP5091 COMPLETION <NULL> 2024-03-09 13:21:30.812608 TCP5092 ESCAPE EXCEPTION HANDLED 2024-03-09 13:22:29.049959 Text ----------------------- Send E-mail Successful. Send E-mail Failed.

How might I use this is a real world scenario. I have an unknown number of CSV files in my IFS folder and I want to email some of them to myself. I can see how many there are by using the IFS_OBJECT_STATISTICS Table function:

01 SELECT PATH_NAME 02 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 03 START_PATH_NAME => '/home/MyFolder', 04 SUBTREE_DIRECTORIES => 'NO', 05 OBJECT_TYPE_LIST => '*STMF', 06 IGNORE_ERRORS => 'YES')) 07 WHERE PATH_NAME LIKE '%/values_%' 08 ORDER BY PATH_NAME DESC

Line 1: I am only interested in the path name of the files.

Line 5: I only want results returned from the Table function that are stream files.

Line 7: And only for those files that contain this string.

There are three files that meet the criteria:

PATH_NAME ------------------------------------ /home/MyFolder/values_2024-03-10.csv /home/MyFolder/values_2024-03-09.csv /home/MyFolder/values_2024-03-08.csv

When I add the SEND_EMAIL Table function to the above statement when IFS_OBJECT_STATISTICS returns a row the file in the path name will be emailed to my email address:

01 SELECT PATH_NAME, 02 SYSTOOLS.SEND_EMAIL('simon@email.com','Values report', 03 'Values report is attached',PATH_NAME) 04 AS "Success" 05 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS( 06 START_PATH_NAME => '/home/MyFolder', 07 SUBTREE_DIRECTORIES => 'NO', 08 OBJECT_TYPE_LIST => '*STMF', 09 IGNORE_ERRORS => 'YES')) 10 WHERE PATH_NAME LIKE '%/values_%' 11 ORDER BY PATH_NAME DESC

Lines 2 – 4: This is the SEND_EMAIL scalar function. I have not bother with the parameter names as, IMHO, they would make this statement too long. Notice that the last parameter, line 3, is the path for the file to attach which is the path name from the IFS_OBJECT_STATISTICS.

When the above is run the following is displayed as the results:

PATH_NAME Success ------------------------------------ -------- /home/MyFolder/values_2024-03-10.csv 1 /home/MyFolder/values_2024-03-09.csv 1 /home/MyFolder/values_2024-03-08.csv 1

I can confirm if these emails were successfully sent by checking JOBLOG_INFO using the statement I did before:

Msg id Type Subtype Timestamp ------- ---------- --------------- -------------------------- TCP5091 COMPLETION <NULL> 2024-02-09 13:29:50.076756 TCP5091 COMPLETION <NULL> 2024-02-09 13:29:50.128666 TCP5091 COMPLETION <NULL> 2024-02-09 13:29:50.215508 Text ----------------------- Send E-mail Successful. Send E-mail Successful. Send E-mail Successful.

Therefore, I have successfully three emails, each with a different file attached.

 

You can learn more about the SEND_EMAIL scalar function from the IBM website here.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.