Wednesday, February 21, 2024

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.

Tuesday, February 20, 2024

March CTXiUG meeting

In two weeks, Tuesday March 5 at 6:30 PM (CT), is the next meeting of the Central Texas IBM i User Group, CTXiUG. The meeting is online, open to all, and free. All you have to do is register.

The guest presenter for this meeting is new IBM Power Champion Gregory Simmons, who will be talking about procedures in RPG.

To register for this meeting, and more details go to ctxiug.blogspot.com

I look forward to seeing you there.

Wednesday, February 14, 2024

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.

Wednesday, February 7, 2024

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.

Wednesday, January 31, 2024

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'.

Wednesday, January 24, 2024

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.

Thursday, January 18, 2024

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'.

Wednesday, January 17, 2024

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:

Wednesday, January 10, 2024

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 ;

Tuesday, January 9, 2024

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:

Wednesday, January 3, 2024

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.

Monday, January 1, 2024

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?

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