Wednesday, March 25, 2020

Customizing email address for SNDSMTPEMM

giving each snsmtpemm user their own email address

I was asked is it possible for each person who sends email using the Send SMTP Email command, SNDSMTPEMM, to have their own unique email address. This command is used to send SMTP emails from IBM i. I am not going to go into details on how to use and configure your IBM i, I am going to refer you to the following posts:

When writing this post I had to use a Security Officer, *SECOFR, equivalent user profile to do what I needed to do. I do not have an System Operator, *SYSOPR, equivalent profile, therefore, I do not know if that gives you the necessary changes I will describe below.

Wednesday, March 18, 2020

Check if program is running interactive or batch

interactive or batch using sql rpg

In the past I wrote about a CL program that submit itself to batch, to do that the CL program must be able to determine if it running interactive or batch. The post describes how you can determine that in a CL program. But what about a RPG program or in SQL?

In this post I will give examples in both RPG and SQL how I can determine if the program is interactive or batch. This is not to replace my tried and trusted CL method. It is just an alternative that might prove useful in certain scenarios.


Wednesday, March 11, 2020

Variable length field in DDS file

This all started as a question from a member of another programming team at work. They had been asked to add a new field to an existing file. Most of the time this field would be empty, but it could contain up to 2,000 characters of data in some cases.

"It would be easy with a SQL table," they said "I could just make a new column VARCHAR (variable length character field) and make the default value null. But I don't know how to do that with a DDS field."

By using a variable length character field will mean that the field will not always be 2,000 characters. It will be as long as the data within it. And when there is no data in the field it will take up zero space. What a disk space savings.

Fortunately doing this with a DDS file is as easy as it is with a SQL table using the right keywords when defining the file. And it is just as easy to handle the variable length and null value in a RPG program. I decide to create my example file and the program on an IBM i partition that is running 7.2 just to show that there is nothing from a newer release need to do this.

Wednesday, March 4, 2020

Adding subtotals and a total to a SQL Select

adding subtotals and total to sql select

I have often looked at the results returned to me by a SQL statement and wondered to myself "Wouldn't it be nice if I could add a subtotal to these results".

Yesterday I decided to have a search using my favorite search engine to see if there is an easy way to do this. I could find examples in other flavors of SQL, but not in Db2 for i. Feeling piqued I decided to try one of these examples with one of my SQL Select statements, and was really pleased to find that what was given in the example also worked in Db2 for i too. Thank goodness for SQL interoperability and standardization.

I am not going to give the source code for the SQL DDL table I will be using in these examples. My table, TABLE_OF_THINGS, has three columns:

Monday, March 2, 2020

User Groups month is over

As the end of February has passed so has my IBM i User Groups month. During the month of February I reach out to you, the readers of this website, and ask for help to find new IBM i User Groups websites and social media accounts to add to my user groups page.

This year, with your help, I was able to add the following websites to the list:

  1. Common Slovensko (Slovakia)
  2. Georgia IBM Power Systems User Group Meetup which appears to have replaced the IBM i Tech Community of Atlanta
  3. Delaware Valley Computer User Group which is now found at a new URL

And the following social media accounts too:

Wednesday, February 26, 2020

Checking if Job has error, and sends an email

rpogram to monitor job for error message and send email

The germ for this post came from a question I was asked about if it is possible to monitor a job, and if it errors to send an email. In previous posts I have given examples of the pieces need to do this, and in this post I will just put it all together, like assembling pieces of Lego.

The one perquisite that is needed to make this work is that the name of the Job has to be unique, and always the same. Each time the job is submitted to batch it has to be submitted with the same unique name. In this example the job I want to monitor will always have the name: SIMON_JOB


If the name of this job is not unique then there is no way I can identify it, and I will be able to check its status.

The program is quite simple I just need to do two things:

Wednesday, February 19, 2020

Additions to Object Statistics table function

Changes to the OBJECT_STATISTICS table function

One of my favorite SQL table functions, OBJECT_STATISTICS, was introduced in IBM i 7.2 and it is something I use on a regular basis to identify unused objects that can be deleted. Over the last two releases of IBM i and numerous Technology Refreshes new columns have been added to this table function making it, in my opinion, better. In this post I am going to describe three of these enhancements, and how I can see them helping me.



One of the things I found using OBJECT_STATISTICS was certain object types' Last Used Timestamp was not updated when the object is used. This would fool me into thinking that it was an unused object that could be deleted as it was not being used. Then I would find all of the other objects of the same type were missing the Last Used Timestamp too. I ended up building a complicated select statement, referencing certain object types and conditioning the values in columns, to make an output table I could then use to find obsolete objects.

Wednesday, February 12, 2020

Calculating prime numbers in RPG

calculating prime numbers in rpg

The germ for this post is from a question that was asked in an interview. This person, a self confessed RPG "fresher", had been asked to write a "RPG free" program to calculate the prime numbers between 1 and 11. This request intrigued me, and made me think what kind of program the interviewer probably wanted, and what I would write if I need to use this at work.

Let me start with the basics, the definition of what is a prime number:

Any integer other than 0 or 1 that is not divisible without remainder by any other integers except 1 and the integer itself

Taken from the Merriam Webster dictionary.

What are the prime numbers in the desired range:

Thursday, February 6, 2020

Migration of developerWorks Technology Refresh information

The migration of content from IBM's developerWorks portal, which was sunset at the end of 2019, continued with Scott Forstie, IBM Db2 for i Business Architect, announcing the migration of two new sites:

As usual great information from IBM, and two sites I will be visiting on a regular basis.

Wednesday, February 5, 2020

Object owner information from SQL View

object_ownership sql view rather than wrkobjown

Tucked within the latest round of Technology Refreshes for IBM i for 7.4, TR1,and 7.3, TR7, was the addition of a SQL View, OBJECT_OWNERSHIP. This View returns the same information as the Work Object Owner command, WRKOBJOWN. I do not use WRKOBJOWN frequently, but I do before deleting an user profile to see which objects are owned by the profile, and an intermittent check on the production libraries to make sure that all of the objects are owned by the generic profile created for this purpose.

Alas, I am restricted on the IBM i partition I use to write these posts to only being able to see objects I own. Therefore, my examples will only show these objects.

The WRKOBJOWN is one of the easier commands to use. I can either just type it at a command line and press Enter, or press F4 to prompt to view the command's parameters.