Retrieving information about a library using SQL

As part of the new Technology Refreshes, 7.4 TR2 and 7.3 TR8, came a table function to return information about a single library. I have to admit that I don't really care about some of the information returned in the results, but there are three columns I am going to find very useful.

The new table function LIBRARY_INFO is found in the library QSYS2. It combines data elements from the RTVLIBD and DSPOBJD commands. When calling this table function there are two parameters, one mandatory and the other is optional:

1. Library name:  No surprise that this is the mandatory parameter
2. Ignore errors?  What to do when an error is encountered. This is the optional parameter. Valid values are: YES and NO

In these examples I am not going to use the second parameter.

Right, let's jump right in and see what information we can retrieve about one of my libraries:

Today is 7.4 TR2 and 7.3 TR8 day

Today is the day the PTFs for the latest Technology Refreshes are available for download, IBM i 7.4 TR2 and 7.3 TR8.

The relevant PTFs are:

• Db2 for i
• RPG

You can find links to what are in these Technology Refreshes here.

I am now going to generate a support ticket asking RZKH to load these TRs on their partitions. When they have I can start sharing how to use these enhancements.

Discovering the database's limits

Every so often I stumble across something in Db2 for i that I may not have a practical use for, but I find interesting. The SQL table SQL_SIZING is a good example of this. It contains one row for each limit of the IBM i Db2 database manager.

The table contains just four columns:

1. SIZING_ID:  The ANSI, and ISO, number that defines the database sizing id
2. SIZING_NAME:  The ANSI, and ISO, database sizing id name
3. SUPPORTED_VALUE:  Sizing limit (value). If null the sizing limit is not applicable for IBM i
4. COMMENTS:  Even though the sizing name is big enough to contain an adequate description of the sizing id this column gives a more detailed description

Adding RPG compiler parameters into the SQL Option statement

This feature was brought to my attention by someone who was converting a fixed format SQLRPGLE program to modern all free RPG code, and wondered the purpose of this code snippet.

01 C/EXEC SQL 02 C+ SET OPTION COMMIT = *NONE, 03 C+ COMPILEOPT = 'DBGVIEW(*ALL)' 04 C/END-EXEC

She understood the purpose of line 2. It was line 3 she had not seen before. Neither had I, and was intrigued as to what its purpose was and how else could it be used.

IBM performance FAQ update

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:

• Db2 for i
• RPG
• Cobol
• C
• Java

To view the guide, which is a PDF, click here.

View the most recent Identity column value

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:

1. Identity column
2. Another column that will contain some data

Inserting one row with every columns' default value

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 )

IBM i 7.4 TR2 and 7.3 TR8 announced

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.

• IBM i 7.4 TR2
• Announcement document
• IBM i 7.3 TR8
• Announcement document

Db2 enhancements in IBM i 7.4 TR2 and 7.3 TR8

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:

• Functional enhancement: Weakly typed distinct types
• Functional enhancement: HASH_ROW built-in function
• Service: QSYS2.COMPARE_FILE

The same additions to the RPG language were made for both versions. These are:

System Reply List and using SQL to view the list

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.