Now possible to build SQL Alias over last member

The SQL Create Alias statement was enhanced in the latest round of Technology Refreshes, IBM i 7.5 TR4 and IBM i 7.4 TR10. Previously when you created a SQL Alias you had to give a specific member in the DDS Physical file. Now I can use *LAST to denote the most recent member in the Physical file, and what is really cool is if I add another member to the file the Alias will now use that member!

This will be clearer when I show my examples.

First I need a DDS physical file. I called this one MULTIMBR, and it is found in my library MYLIB.

A R RMULTIMBR A FIELD 100A

I compiled the above DDS Physical file with the following command:

01 CRTPF FILE(MYLIB/MULTIMBR) SRCFILE(MYLIB/DEVSRC) MAXMBRS(*NOMAX)

List all object types using SQL

Have you ever wondered how many different types of objects there in IBM i? And what the types of objects are?

The latest Technology Refreshes give us a SQL View that contains that information, and we can query it to answer those questions.

The SQL View is called SYSTEM_OBJECT_TYPES, and it is found in the QSYS2 library. It has three columns:

1. OBJECT_TYPE:  Object type
2. TEXT_DESCRIPTION:  Description of what the object type is
3. CATEGORY:  There are two categories:
   IFS - object is in the IFS
   LIBRARY - object resides in a library (not an IFS object)

New functionality added to RPG's SND-MSG op code

Two years ago the Send Message, SND-MSG, operation code was added to RPG. In the latest round of Technology Refreshes a number of new options have been added to SND-MSG and the Target, %TARGET, built in function.

To oversimplify it SND_MSG consists to three parts:

1. Type of message to send
2. Message text or variable
3. The target for the message, which is the %TARGET built in function, BiF. This is optional

For example:

SND-MSG *INFO 'Message text goes here' %TARGET(*SELF)

Deleting old device definitions

Last month I wrote about a way to list all of the unused virtual devices in your partition. In response to the article I received a question: "Is there a way to then delete these devices, other than manually going through? One system I just did has over 5,000 devices...."

The answer, of course, is yes there is. Before I give my final result let me go through the steps I did to create a SQL script to delete the unused virtual devices.

I can identify the virtual devices using the Object Statistics SQL Table function. I do not want to repeat myself. For the details read my previous article.

I decide to create a Common Table Expression, CTE, as it was just "neater". In the first part of the statement I would create a virtual table of all the eligible virtual devices, and in the second I would delete them. To start with I just want to make sure I gather the data I expected, therefore, my first statement did not do any deleting.

Constant keyword added to RPG definitions

As part of the latest Technology Refreshes, IBM i 7.5 TR4 and 7.4 TR10, comes an addition to the variable and data structure definitions. It is now possible to use the constant keyword as part of those definitions to protect them from being changed.

Why would I want to do that?

There are times I want to use a variable, rather than a constant. It might be to contain a date. I would not want this to be changed, either on purpose or by accident.

Below is an example:

01 **free 02 dcl-s StartDate date inz(*sys) const ; 03 dsply StartDate ; 04 StartDate = d'2024-07-04' ;

July's presentations

This moth I have two events I will be talking at.

I will be the speaker of the July meeting of the Central Texas IBM i User Group, CTXiUG. This meeting is on Tuesday July 9 at 6:30 PM (CT. I will be talking about various ways I have used SQL on IBM i to keep partitions clean of old objects, etc.

You can register at the CTXiUG website here.

Later this month I will be attending and presenting at one of my favorite conference's, OCEAN user group's TechCon24. The conference is a four-day affair. The first day, Wednesday July 24, is online. I am presenting in person on the second and third days, Thursday July 24 and Friday July 25. On Saturday, July 27, is their day of workshops.

You can learn more about the best user group conference in the western USA at their TechCon24 website here. You can register for the event from the same page.

I hope to see you there. And if you are attending and you see me, 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.

Create an Excel spreadsheet from a CL program

It has been possible to create a Microsoft compatible Excel spreadsheet using SQL since the last Technology Refreshes. In my example I demonstrated how you could do this within a RPG program.

A few months have passed and I have been asked how to do the same within a CL program. With a minor change I will show it is possible.

Let me start by showing the code for this program:

RPGPGM.COM-unity ribbons in the UK

Last week I was at i-UG's i-Power2024 conference, in the United Kingdom. This gave me another opportunity to hand out some RPGPGM.COM-unity ribbons.

What is the RPGPGM.COM-unity? You will have to click on this link to learn what it means, and see who I gave ribbons to.

Happy birthday to IBM i

Today marks the 36th anniversary of the launch of the AS/400 server and the OS/400 operating system on June 21, 1988.

Over this time the server has evolved, with the change from CISC to RISC chips and the improvements in chip technology, to become the IBM Power server. The operating system has been rewritten several times to include the latest evolving technologies to become IBM i, which is not the only operating system that can run on a IBM Power server.

What we have today is not AS/400 and OS/400, even though everything we could with them we can do today with the modern server and operating system.

I think I did a good job describing this history last year, for the 35th anniversary. If you are interested in learning more what AS/400 was, and what it has become, read the story here.

Happy birthday IBM Power and IBM i! May you have many more!

Using SQL table function to lookup SQL codes

It has always been a bit of a bind to look up what a SQL codes means, to be able to discover what error or warning Db2 for i is giving me for a SQL statement I have executed. There is a message file, QSQLMSG, where I can convert the SQL code into a message id and then look in the message file for the message texts:

SQL code -501 = Message id SQL0501 DSPMSGD RANGE(SQL0501) MSGF(QSQLMSG)

With the technology refreshes for IBM i 7.5 TR3 and 7.4 TR9 comes a table function where I can pass it the SQL code, and the results include all the information I desire:

• Message id
• Message text
• Second level message text

New Technology Refreshes available today

Today the PTFs for the latest Technology Refreshes, IBM i 7.5 TR4 and 7.4 TR10, become available.

There are three sets of PTFs you need to download:

• General PTFs for the Technology Refresh
• Database PTFs (SQL)
• RPG PTFs

The numbers for these PTFs are different for the two releases. These are the numbers I have found so far:

	IBM i 7.5 TR4	IBM i 7.4 TR10
General TR PTFs
Database PTFs	SF99950   7	SF99704   28
RPG PTFs	5770WDS SJ00650 TGTRLS(V7R4M0): 5770WDS SJ00653	5770WDS SJ00635

I cannot wait to start trying all the new features and functions. When I do, I will write about them on this website.

Eleven years and still going

Another year has passed, and this blog is now eleven years old. It is still a labor of love, and I want to continue to bring you the news about IBM i and IBM Power, and the best examples of using IBM i. I am grateful to all of you for visiting this site, and especially to you who have reached out to me with their feedback and encouragement.

On the personal side I was grateful to be recognized by IBM as an IBM Champion for Power for the fourth year.

List unused virtual devices

This was a first: I was asked by the auditors for a list of virtual devices that had not been used in the past six months. In all my years of dealing with auditors this was the first time I had been asked this question.

It is not a big deal to get this information. All virtual devices are just device descriptions, object type '*DEVD', with the object attribute of 'DSPVRT'. As they are objects I can get this information from the SQL Table function OBJECT_STATISTICS.

What columns of data am I interested in?

• Object's attribute
• Object's name
• The date the object was created
• The date the object was last used
• Object's description

My SQL statement to retrieve the data the auditors are interested in would be:

Withdrawal of support for some development tools

In an announcement made last month, May 2024, IBM announced the withdrawal of a number of their products and tools for IBM power and IBM i, effective April 30, 2025.

Included are the following IBM Rational Development Studio for i Application Development Toolset tools:

• Report Layout Utility (RLU)
• Screen Design Aid (SDA)
• File Compare and Merge Utility (FCMU)
• Advanced Printer Function (APF)
• Character Generator Utility (CGU)
• Data File Utility (DFU)

You can read the announcement here.

I have reached out to an IBM Business Partner and asked them does this mean that these tools will still work, just they we will no longer get support for issues with them. When I receive a reply from them I will post it here.

I don't think that IBM will stop these tools from working. SEU has been off support for years, but you can still use it (if you must).

Getting the message for the SQL code quickly

All of us who use SQL have encountered errors, and when we do a SQL code is returned. This then leaves us trying to find the text associated with the SQL code to understand what had happened.

If this is in a RPG program I always recommend that you use GET DIAGNOTISTICS, which will return all you wanted to know about the message and a whole lot more too. If I am quickly writing a RPG program that will only be used one, or maybe twice, or a CL program and I get a SQL code I need to look it up to understand what happened.

There are two types of SQL codes:

1. Those less than zero: Errors
2. Those greater than zero: Warnings

All SQL codes have an equivalent IBM i message id. Basically it is 'SQL' followed by the SQL code. For example, SQL code -423 becomes message id SQL0423.

All message ids' information in IBM i are held in message files. SQL messages have their own message file QSQLMSG in the library QSYS.

If I wanted to look up the message for SQL code -423, message id SQL0423, I could use the Display Message Description command,DSPMSGD:

RPGPGM.COM-unity ribbons

Last week at COMMON Americas PowerUp2024 conference I handed some special ribbons to people. You can see who I did here.

What does this mean? You will have to click on the above link to learn.

Apologies to those people I gave ribbons too that I did not have time to photograph. If you are one of those people it is not too late. Take a photograph of yourself posing with your conference badge and RPGPGM.COM-unity ribbon, like the people in the photographs, and send it to me. I will add you to the page. My email address is RPGPGM.COM@GMAIL.COM

Extract number out of character column and use it to update another column

A friend told me about the old database he had to work with. There was one file with a field that contained two pieces of information. Any leading characters, A – Z, meant one thing and the numbers within it meant something else. Unfortunately there could be any number of leading characters, the numbers could be three of four long, and there could be characters at the end. The examples he shared with me were:

• 'A123B'
• 'ABC123'
• 'AB4679'
• 'BC0123D'

His plan was to add a new numeric field to the file, and update it with the numbers extracted from this original field.

"What would be a simple way to do this with SQL?" he asked.

Retrieving file's keys with SQL, having to use DSPFD

In my last post I wrote about how to list a file's key fields using the file QADBKATR to retrieve the key fields' information from.

I mentioned that there may be times that you are not authorized to use the QADBKATR file. If you are not authorized how else can you get the key fields? And present them as I did in the previous example?

I have to fall back and use the Display File Description command, DSPFD, using the Access Path value in the "Type of information" parameter:

DSPFD FILE(MYLIB/TESTFILE) TYPE(*ACCPTH)

New IBM Power 10 model for AI

On Tuesday, May 7 2024, IBM announced a new model IBM Power 10, S1012, which caught my eye. It is smaller than the other IBM Power rack mounted servers, and it also comes in a cool looking tower version too.

The following statements in the announcement caught my eye:

• "This 1-socket, half-wide Power10 processor-based system delivers up to 3X more performance per core versus Power S812"
   
• "delivers the lowest entry price point in the Power port[f]olio to run core workloads for small and medium-sized organizations"
   
• "provides clients the flexibility to run AI inferencing workloads in remote office and back office (ROBO) locations outside mainstream datacenter facilities"
   

Retrieving file's keys with SQL

I was asked how I could present the following information in an "easy to use" manner for all of the "files" in a library:

• Library name
• File name, or Table, Index, etc. name
• Type of file
• When it was last used
• File's key fields

The easiest way to get this information is using SQL. The first three pieces of information can be retrieved from the SYSFILES View. The last used information can come from the OBJECT_STATISTICS Table function. Getting the file's key fields is not in any of the usual SQL Views about fields/columns or files/tables.

There is a file that contains the keys for all files: QADBKATR. This is a logical file built over the file QADBKFLD. On all the IBM i partitions I use for testing this posts I found that I am not authorized to the physical file on any, and I am authorized to the logical file on all but one. Therefore, my examples will use QADBKATR.

Spring 2024 TRs announced

Today, at a later time than previous announcements, a new set of Technology Refreshes were announced by IBM for the currently supported releases of IBM i. These are IBM i TR4 and IBM i 7.4 TR10.

Doing a quick count of what is new and enhanced I find the following:

• RPG
  • 2 enhancements
• Db2 for i (SQL)
  • 17 additions
  • 9 enhancements

You can read all about them on these pages on IBM's websites:

Converting fetched columns into an array

Coming up with a meaningful title for this post proved harder than normal. I was asked a question about how to fetch data, using a SQL Cursor, and convert some of the fetched columns into an array. The questioner had tried various approaches, but was always receiving an error in the SQL pre-compile step of his program.

The file in question contained 35 fields he was interested in. The record would be for an account, by year and month, 31 balance fields one for every day of the month (yes, I know not all months have 31 days), and finally a total monthly change balance. The questioner wanted to put all of the 31 day fields into one array.

Let me start with my version of his file, that I called TESTFILE:

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.

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:

Calculating the difference between two dates in SQL with the result in days

I have written before about how to calculate the difference between two dates using SQL. The returned result is given in years, months, and days. But there are some occasions I need the result in days. For example I need a list of customers who have not paid in over 90 days.

Let me jump into my first example. Here I used a DDS file to contain a "date" field, like many ERP databases the date is held in a numeric field:

01 A R TESTFILER 02 A CUSTNBR 10A 03 A ENTERDTE 8P 0

To calculate how many days difference there is from the date I wrote this post, I first need to convert the field ENTERDTE from a packed numeric to a "true" date, and then I can perform the calculation using it. I am showing a very simple method of doing this. You might find this will not work in your situation. If any of the numeric fields in the file contain a numeric value that is not a valid date, zero for example, then this example would fail with an error.

Retrieving PTF cover letters using SQL

In preparation for forthcoming annual audits I was asked if there was a way to be download the PTF cover letters for all the PTFs that had been applied in 2023 in a format that could be searched?

In previous years all of the PTF cover letters were printed out and retained. If anyone had a question about whether a PTF was applied to a certain feature the printed letters were manually searched. This could take a long time.

Fortunately the last Technology Refreshes. IBM i 7.5 TR3 and 7.4 TR9, comes with a something I can use. Within them is a new Db2 for i, SQL, table function to View PTF cover letters, PTF_COVER_LETTER. If I can view a cover letter I can also insert it into a table. Which is a lot better than using the output from Display PTF Cover Letter command, DSPPTFCVR.

Using SQL to ping remote servers

Included as part of the recent Technology Refreshes, IBM i TR3 and 7.4 TR9, comes a new Db2 for i, or SQL, table function that allows me to ping a remote server and get the result in one row.

The PING table function has a number of parameters you can use with it:

1. REMOTE_SYSTEM:  This is where I would use the name of the remote server. There is a default value of *INTNETADR, which notifies the table function I am wanting to ping with the IP address.
2. REMOTE_IP_ADDRESS:  The remote server's IP address. If REMOTE_SYSTEM is not *INTNETADR then this parameter is ignored.
3. ADDRESS_VERSION_FORMAT:  Which IP address format to use. Allowed values are *IP4, *IP6, and *CALC which is the default.
4. NUMBER_OF_PACKETS_TO_SEND:  Number of packets to send. Allowed range is 1-999, default is 5.
5. PACKET_LENGTH_TO_SEND:  Length of the packet to send. Allowed range is 8 – 65500, 256 is the default.
6. WAIT_TIME:  Number of seconds to wait for the return packet. Allowed range is 1 – 120, default is 1.
7. LOCAL_IP_ADDRESS:  Local internet address that the outbound packets use. Can be an IP4 or IP6 address, or default which is *ANY.

New View for listing jobs within job queues

Included as part of the recent Technology Refreshes, IBM i TR3 and 7.4 TR9, comes a new Db2 for i, or SQL, View that will return all the jobs that are submitted to job queues.

I have written in the past about how to find a job that has been submitted to batch. This new View makes it much easier to find this information.

I have a couple of examples of the kind of information I want to find about submitted jobs. But before I talk about those I recommend that you run the following statement to see what information is returned. As there is, IMHO, a lot of useful information in the columns of this View.

Renaming IFS files using SQL

In the past if I needed to rename files in the IFS I would either use the Rename Object command, RNM, or used the 'Integrated File System' tool in ACS.

The latest round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, brings us a Db2 for i, or SQL, scalar function that allows me to rename files in the IFS, IFS_RENAME. The scalar function has three parameters:

1. FROM_OBJECT:  The path of the file to be renamed.
2. TO_OBJECT:  The path of what the file will be called.
3. REPLACE:  If there is a file with the To Object name should it be replaced? 'YES' and 'NO' are the allowed values. If none is given the default is 'NO'.

SELF improvement

Having asked at several conferences I seem to be the only person using SELF to capture SQL errors. SELF, or SQL Error Logging Facility, was introduced in IBM i 7.5 TR1 and 7.4 TR7, as a way to capture SQL errors and insert them into a table that I could use to diagnose and remedy those errors. The major drawback of this tool was that I would have to list all of the SQL codes I was interested in and insert them into a SQL Global Variable every time I wanted to use SELF.

This was remedied as part of the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, now I could use the special values of '*ERROR', '*WARN', or '*ALL'. Rather than list all of the SQL codes I could just use one of these to capture all errors, all warnings, or all errors and warnings. Which makes my life a whole lot easier.

Getting statistics for jobs that have ended

One of the new additions to Db2 for i, or SQL, in the latest Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, is a new Table function ENDED_JOB_INFO. This Table function exists in the SYSTOOLS library. ENDED_JOB_INFO allows me to retrieve some information about jobs that have, as the name suggests, ended.

ENDED_JOB_INFO has two parameters:

1. START_TIME:  The start of the range of jobs to find. If a job starts on or after this time it will be included in the results. If no value is given the default is the current timestamp less one day.
2. END_TIME:  End of the range. If no value is given the default is '9999-12-30-00.00.00.000000'.

SQL repeat scalar function

The idea for this post came from Jonathan Heinz who brought to my attention the REPEAT SQL scalar function in a comment to a post. I have to admit I had not heard of it so this post is examples of how I played with it to become familiar with its use.

Being a scalar function it can be used within a Select, Set, or Values statement. Its syntax is:

REPEAT('characters-to-repeat', number-of-times-to-repeat)

I started playing with it in ACS's Run SQL Scripts, using a Values statement:

Enumeration of constants comes to RPG

Enumeration definitions have been added to RPG as part of the Fall Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9. This allows me to define a list, or group, of constants in one definition structure. Personally I would only include related constants in one enumeration group, but I could include all of my constants, both related and unrelated, in one group.

The definition looks very similar to how to code a data structure definition.

01 dcl-enum EnumerationName ; 02 Name1 'Value 1' ; 03 Name2 'Value 2' ; 04 end-enum ;

Creating Excel spreadsheets with SQL

One of the more eye-catching additions to Db2 for i in the latest round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, was a SQL scalar function that would create a Microsoft Excel compatible spreadsheet in the IFS.

For years we have been able to create Common Separated Variable length file, CSV, by copying data to the IFS. This can be opened using Microsoft Excel and then saved as an Excel compatible spreadsheet. This is the first time an easy-to-use method has been provided, as part of native IBM i, to allow us to create an Excel in the IFS from data in the Db2 database.

The new scalar function is called GENERATE_SPREADSHEET and is found in the library SYSTOOLS. It has a number of parameters that need to be passed to it:

Check if license product installed

I was performing some testing on a different IBM i partition where I wanted to take a spool file and generate a PDF in my IFS folder. I received an error saying that "Transformation services not loaded". Before I move my testing to another partition I wanted an easy way to check is "IBM Transformation Services for i" is installed.

I could check using the Work License Information, WRKLICINF, as the message suggested. But I my profile does not have sufficient authority to use it.

Fortunately there is an alternative I can use. I need to thank IBM Db2 for i chief architect, Scott Forstie, for bringing to my attention a SQL View that provides me with an easy to way to accomplish what I want.

Happy New Year 2024

Another year has passed and we all look forward into the New Year, 2024, with excitement thinking of all the good things it can bring to us all.

Before I put the old year out of mind, what did it bring us in the IBM i world?

• After seven years the end of regular support of IBM i 7.3
• Ten years after its announcement the end of service for IBM Power8 servers
• Two new Technology Refreshes for 7.4 and 7.5

What did you find the most interesting last year? These were the top ten popular posts from last year: