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.