Wednesday, February 28, 2024

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.

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.

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.