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:

Using three part name when the user profile or passwords are different

This post is to take care of an oversight that was brought to my attention. I have written several posts about using the SQL three part name, all of the examples assumed that the user name and password were the same on all of the partitions. What happens if they are not? This post will explain what to do.

For these examples I am using three partitions:

1. DEV740: This partition is running IBM i 7.4, and is the "local" partition, the one I am performing all the calls from
2. DEV730: Runs IBM i 7.3. When the connection was configured on DEV740 to DEV730 the serial number was used, therefore, DEV730 is known as G102A08R.
3. DEV720: This is the oldest partition with IBM i 7.2. My using it proves that the three part name has been around for longer than just the last few releases

I am not going to do anything too fancy for these examples. I have a file, called WHERE_AM_I, on all of these partitions. It has just one field, FLD001 that contains a unique message in each file. All I am going to do is to access these files, and show the contents on DEV740.

Capturing SQL results into a data area

The idea for this post came from a Facebook thread. The question was: "Does anyone know of a way to insert the result of an SQL SELECT into a CL Variable? I can make an SQL select into a table and read (RCVF) the file of course... But can it be done in a more "direct" way?" One of the replies to the question piqued my interest.

Before I start showing examples of code I needed a SQL statement that anyone with a recent release of IBM i could execute in their partitions. I came up with retrieving the record length for the SYSDUMMY1 table. I find the easiest way to get that information is to use the SYSFILES SQL View.

01 SELECT MAXIMUM_RECORD_LENGTH 02 FROM QSYS2.SYSFILES 03 WHERE SYSTEM_TABLE_SCHEMA = 'SYSIBM' 04 AND SYSTEM_TABLE_NAME = 'SYSDUMMY1'

Changes to the SQL View for System Values

There are times when IBM updates a SQL View to add information to it that makes more useful and easier to use than it was before. In the last round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, came the addition of columns to the View that we can use to retrieve information about system values. The added rows doubled the number of columns in the View, by all means it previously had just a few rows.

The SYSTEM_VALUE_INFO View was introduced as part of IBM i 7.2 and 7.1. I have primarily used it for comparing the system values in different partitions that is an audit requirement. I could get the information from multiple partitions and display it in the same "report". The "report" is a Microsoft Excel spreadsheet.

The original version of the View contained just three columns:

1. System value name
2. Numeric value, contains system value if it is a number
3. Character value, contains systems value if it is character data

I combined the numeric and character values into one column for my "report". But I was missing one piece of information I knew would be really useful: what was the default value of the system value when the partition was created? These two are new columns in the changed View. All of the new columns are: