May's presentations

I feel honored to have been asked by Jon Paris and Sunsan Gantner to give two presentations as part of the System i Developer Lunch and Learn series. I will be presenting on Wednesday May 8, about converting spool files to PDF, and on Wednesday May 15, about what is new in CL.

The Lunch and Learn presentations are all online and free to everyone.

There are many interesting speakers covering a good range of subjects. To learn more go here and choose which of the Learn and Learn Series you want to register for.

On Monday May 13 the Central Texas IBM i User Group, CTXiUG, welcomes IBM Champion Marina Schwenk to their online meeting as the guest speaker. She will be talking about using service programs and unit testing.

This meetings are online and free to everyone.

Learn more and register here.

This month is also the COMMON PowerUp in-person conference in Fort Worth, Texas, Monday May 20 - Thursday May 23. I will be giving six presentations this year.

As this is an in-person conference you need to register for the conference here.

Learn more about my presentations on my speaker page here.

If you see me there please introduce yourself and say "Hi".

If you are a member of an IBM i Local User Group, LUG, and you would like me to talk to your group, use the contact form to reach me. We can then arrange a date and subject.

If you are a member of a LUG that is not listed on my LUG page, please contact me and send me your group's details.

More about getting information about SQL Views

Several years ago I wrote a post about the Db2 for i (SQL) Views about Views:

• SYSVIEWS:  Information about Views themselves
• SYSVIEWDEP:  Information about Views' dependencies

One of the things I talk about during presentations I give to Local User Groups, LUG, is the use of SQL Views and how they make my programming simpler. Therefore, I wanted to revisit these two Views and explain how they help me learn more about the Views in the partitions I use.

Before I can show what kinds of information I can get from SYSVIEWS and SYSVIEWSDEP I am going to need some Views to play with.

I am going to join a DDS file, TESTFILE, and a DDL table, TESTTABLE to create my first View. While it is not essential to know what the file and table look like I am just going to include their source code below.

First TESTFILE:

Generate spreadsheet with SQL in batch job

When I wrote about the Db2 for i, or SQL, scalar function to create a Microsoft Excel spreadsheet in the IFS my example program ran interactively. I recently received a message asking the following: "good stuff even though I started to use it, it has some limitations so far: doesn't work with QTEMP files. Am I right?"

I submitted my example program to batch and found that the expected spreadsheet was not generated. Looking in the job's job log I could see several messages related to files I created in QTEMP not being found. It would appear that the Java called by the GENERATE_SPREADSHEET submits a job to batch with the type BATCHI.

If I should not use files in QTEMP then I need a Standard Output file, STDOUT, in another library. I chose to create it in QGPL, but it could be in any library that is in the users' library list. As STDOUT is a source file I create it with the following:

CRTSRCPF FILE(QGPL/STDOUT) RCDLEN(266) + TEXT('Standard output file')

End to free format directive

What I call totally free RPG and what IBM calls modern RPG has been around for nine years. This type of RPG always needs the **FREE directive starting in the first position of the source line. And what follows is the free form RPG, where the source line can start in any position.

...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 01 **free 02 dcl-s Statement1 varchar(100) inz('Entered in free format RPG') ;

Marc Raynor informed me of another directive that "ends" the free format RPG, **END-FREE. After that directive all valid RPG must be entered in the sixth position for fixed format code, and later for the not completely free format code.

Having been told this I had to try it:

ACS 1.1.9.5 out now

I want to thank Shaker Steven for bringing to my attention this morning that the Spring 2024 update for Access Client Solutions, ACS, is now available. When I opened my ACS the following window popped up informing me the new update was available.

Using Select Into with Execute Immediate

The idea for this post came from a question I was asked about using the Execute Immediate SQL statement. The questioner asked why he was unable to execute a Select Into statement using Execute Immediate. His RPG source code compiled, but when called the program would error with a SQL code of -312, or message id SQL0312:

Variable RESULT not defined or not usable for reason code 8. 8 -- The host variable is specified in dynamic SQL. Host variables are not valid in dynamic SQL. Parameter markers must be used instead.

I found a solution for him. I could resist playing with what I found and created a far more complicated scenario that I am sharing with you in this post.

Patron saint of IT's day

In the western Christian calendar today is a special day for everyone working in information Technology, as it is Saint Isidore of Seville saint's day.

St Isidore of Seville is special to the IT community as in 1997 he was made patron saint of all things related to computers and IT by Pope John Paul II. You can learn more about Isidore here.

Happy St Isidore of Seville day, and let's hope for a little divine intervention so all of our IT projects in the next year complete successfully, on time, and on budget.

"Looking at his example it looks so easy. Gratias tibi RPGPGM.COM!"

Send email with SQL in batch job

When I wrote about the Db2 for i, or SQL, scalar function to create a Microsoft Excel spreadsheet in the IFS my example program ran interactively. I recently received a message asking the following: "good stuff even though I started to use it, it has some limitations so far: doesn't work with QTEMP files. Am I right?"

I submitted my example program to batch and found that the expected spreadsheet was not generated. Looking in the job's job log I could see several messages related to files I created in QTEMP not being found. It would appear that the Java called by the GENERATE_SPREADSHEET submits a job to batch with the type BATCHI.

If I should not use files in QTEMP then I need a Standard Output file, STDOUT, in another library. I chose to create it in QGPL, but it could be in any library that is in the users' library list. As STDOUT is a source file I create it with the following:

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.