The latest updated version of IBM's "IBM i on Power – Performance FAQ" was published at the beginning of this month, May 1.
While it includes the usual system information, in the latter half it contains advice for:
To view the guide, which is a PDF, click here.
In my opinion identity columns are one of the cool things in Db2 for i. Basically an identity column is a column in a table that is updated by Db2 with the next sequential value. Depending upon how I code the identity column I can guarantee that they will never repeat, and provide a great unique key that can be used as a foreign key in another table. Most of time I do not have to bother with what the identity column's value as I know it will be unique and the only way I can reuse numbers is to reset the identity column.
I have to admit at times I am curious as to what the last value used in a table's identity column. I stumbled across a SQL function that will return to me the last used identity column for the table. But before I describe how to use this function I need a table and some data within it to show how to use the function.
My table is simple, it just contains two columns:
I wrote before how to SQL Insert a row into a table with the columns' defaults. Included within the last Technology Refreshes for IBM i 7.4 TR1 and 7.3 TR7 was an enhancement to the SQL Insert statement that would add a new row to the table with the columns' defaults without having to say "DEFAULT" for each column.
Before I show the enhanced Insert statement I need a table to insert the row into:
01 CREATE OR REPLACE TABLE MYLIB.TESTTABLE ( 02 ID_COLUMN SMALLINT GENERATED ALWAYS AS IDENTITY, 03 COLUMN1A VARCHAR(10) DEFAULT 'COL 1', 04 COLUMN1B VARCHAR(10), 05 COLUMN2A DECIMAL(3,0) DEFAULT -1, 06 COLUMN2B DECIMAL(3,0), 07 COLUMN3A DATE DEFAULT '01/01/1900', 08 COLUMN3B DATE, 09 COLUMN4A TIMESTAMP DEFAULT '0001-01-01-00.00.00.000000', 10 COLUMN4B TIMESTAMP 11 ) |
After last night's publication of the enhancements in the forthcoming Technical Refreshes, IBM i 7.4 TR2 and 7.3 TR2, this morning the announcement documents have been published.
From this we learn that the projected availability date for the PTFs for these TRs will be May 15, 2020.
The Db2 enhancements for the new Technical Refreshes of IBM i were published tonight, 7.3 TR8 and 7.4 TR2.
I have only had the opportunity for a quick glance at the pages for the two TRs and I find that there are some additions that have been made to 7.4, and not to 7.3:
The same additions to the RPG language were made for both versions. These are:
My original plan was to answer a question about the best way to retrieve the data from the system reply list on a particular IBM i partition. Before I wrote that post I asked my colleagues whether they knew about reply lists and had they used them? From their reactions I think I need to discuss what the system reply list is, and include the answer to my question within this post.
What the system reply list allows me to create a list of entries that will handle inquiry messages. On my travels I seen a few sites that use this feature, while most do not.
Reply lists have been around forever, all the way back to the days of the AS400. From what I remembered while creating the examples for this post I don't think anything has changed since I first played with them.
This all started with a conversation with a colleague. He asked about how he could change multiple RPG's numbered indicators without having to hard code the indicators. As the indicators are held in an array he was asking couldn't he just change a number of elements in one statement, rather than the individual indicators? He gave this example of fixed format RPG which changes indicators 10 - 13.
C MOVEA '1001' *IN(10) |
When we place a debug breakpoint after this line we could see that indicators 10 – 13 contained the values he desired:
> EV *IN *IN(10) = '1' *IN(11) = '0' *IN(12) = '0' *IN(13) = '1' |
Before I go any further I want to give my opinion about numbered indicators. I am not against indicators. I use them all the time in my work, and you have seen many examples of me using indicators in posts in this blog. But these are named indicators, not the numbered indicators those of us old enough to have programmed in RPG2 or RPG3 had to use. In RPG4 there is no excuse to use numbered indicators. They are confusing as a numbered indicator gives me no idea as to its function. Do you know what *IN80 is used for? If I use a named indicator instead then everyone knows its purpose.
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.
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.
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.
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:
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:
And the following social media accounts too: