Manually collecting and refreshing statistics for a DDL table

This is how to manually update the statistics used by the SYSCOLUMNSTAT View for a SQL DDL table, TESTABLE.

If the video is too small for you to see what is going on, click on the YouTube link in the video, or click here.

 

IBM documentation describing how to do this here.

 

Using ACS version 1.1.9.1

More useful information added to SYSCOLUMNSTAT View about variable length columns

As part of the recent IBM i Technology refreshes, 7.5 TR1 and 7.4 TR7, three new columns were added to the SYSCOLUMNSTAT View. These are only of interest for variable length columns. While two of the columns I found easy to understand the third took awhile for me to work out what its contents mean.

These new columns are:

• MAXIMUM_COLUMN_LENGTH:  For variable length columns this is the maximum length of the column. For fixed width columns this is the column length.

Converting numbers and characters into timestamps

I am sure we have all worked with ERP Applications that still store their dates and times in numeric or character fields. I was asked what would need to be done to convert these into a timestamp in a SQL Select statement. Personally, I do like using timestamps rather than individual date and time columns, or fields. Therefore, I consider this a good question.

I have a SQL DDL Table with a mixture of "date" and "time" columns within it:

• DATE_NBR8:  A numeric representation of a date, eight long, and in ISO format (YYYYMMDD).
• DATE_NBR7:  A numeric representation of a date, seven long, in CYMD format (CYYMMDD).
• DATE_CHAR:  A character representation of a date, eight long, in ISO format with no separator characters.
• DATE_DATE:  What I would call a "true date", date data type.
• TIME_NBR:  A numeric representation of a time, six long.
• TIME_CHAR:  A character representation of a time, six long, with no separator characters.
• TIME_TIME:  A "true time", time data type.

Find the most recent rows for a repeating sets of data with SQL

This post is based upon something I needed to provide to my superiors at my work. We have a history file that contains many thousands of repeating sets records (yes, I know it is time to purge the old data), I was asked to "create a SQL statement" that would return the most recent records for each key. It will make more sense when I show the data I am going to be using here.

In these examples the file is named TESTFILE, and contains three columns of data. I can use the following SQL statement to list the contents of this file:

01 SELECT * FROM TESTFILE

The results are:

Getting the hours, minutes, and seconds from a difference in timestamps with SQL

I have timestamp columns in a Db2 Table that I need to give the difference in hours, minutes, and seconds. Preferably I would put these into separate columns that I can then use in various ways in subsequent jobs and programs.

All you really needs to know about the Table I will be using is that it is called TESTTABLE, and the timestamp columns I need to calculate the difference between are:

1. FROM_TIMESTAMP
2. THRU_TIMESTAMP

I can easily determine the difference between the two with the following SQL statement:

Improved SQL built in function for calculating difference between timestamps

I had written about the Db2 built in function TIMESTAMPDIFF, which is used to calculate the difference between two timestamps and return the difference in various different units of time. In the latest Technology Refreshes, IBM i 7.5 TR1 and IBM i 7.4 TR7, a new and improved built in function, TIMESTAMPDIFF_BIG, was introduced.

TIMESTAMPDIFF had issues in being able to correctly return the difference in microseconds. IBM's documentation says that the new TIMESTAMPDIFF_BIG will do that correctly. The syntax of the two built in functions, BiF, is the same:

Copying data with an Identity column from one table to another and not change the value

It has always been a problem with copying data from one SQL DDL table to another if the table contains an identity column. Identity columns are controlled by the database itself. When a new row is inserted into the table the database assigns the Identity column in the new row the next sequence number. They are a great way to ensure that the table has a unique key, and they can be shared with other files as an external key to link back to the original table.

The issue has always been that if I copy data from one table to another with an Identity column of the same name the database will assign the inserted rows new identity values using the sequence of the copied to table. This has been a great frustration to us all, and has led to many forsaking Identity columns altogether.

How to change someone's PDM defaults

I have been asked this question twice this week:

Is there a way to change everyone's PDM settings without them having to into their own settings and making the change themselves?

Both people want to change the "file" that contains these setting either with a SQL statement or they would write a program to do so.

I do not know where the PDM settings are stored. I assume they are in a file, or possibly even files. As the data is IBM's data I would be very wary to change it as I have no idea of what the consequences are if I was to make a mistake.

Easy way to validate if user has a certain group profile

This is another example of something I found by accident. Checking IBM's documentation I can find that this is found in IBM i releases 7.5 – 7.1, it might even be in earlier releases but IBM's online documentation only goes back to IBM i 7.1.

VERIFY_GROUP_FOR_USER is a SQL function that allows me to verify if the user of the current job is a member of any group profiles.

I could use the Display User Profile command, DSPUSRPRF, to display which groups and supplemental groups an user profile has. What is the fun in that when I can get the same information using SQL?

Finding out if I am authorized to a function usage identifier

Function Usage Identifiers are a way to be authorized to a particular function, without having the authority changed on my user profile. I have found it is a lot easier to persuade the sys admins to grant me authority to the Function Usage Identifier for something, than it is to have my user profile changed.

In this post I am going to show ways you can find the Function Usage Identifiers, see which ones someone is authorized to, and finally have a way to check in a program whether or not you are authorized to one of the functions. Fortunately all of this can be achieved simply using SQL. What I will be using are:

Using SQL to get the first of the month for a date

I have used the LAST_DAY scalar function to return last day of the month many times. I was interested to see in IBM i 7.5 TR1 and IBM i 7.4 TR7 that a new matching scalar function was added, FIRST_DAY.

As the name suggests FIRST_DAY will return the fist day of the month of the date passed to the scalar function. For example:

01 VALUES FIRST_DAY(CURRENT_DATE) || ' thru ' || LAST_DAY(CURRENT_DATE)

I have used FIRST_DAY and LAST_DAY to return the first and last days of the month for the current date. Rather than have this information in two columns I have concatenated these together to produce one result. I know the SQL purist are going to criticize me as I have used the double pipe symbols ( || ) rather than CONCAT. They will, rightly, point out that this using the double pipe will not work on other Db2 platforms. As I will only ever run this code in an IBM i partition, I am not concerned by this lack of cross-platform compatibility.

February 2023 Power announcement

On Valentine's day, February 14, Ken King, General Manager for IBM Power severs, published a blog describing the latest IBM Power announcements.

Not a lot about IBM i, but I am always interested to learn what is going on with the IBM Power server family of hardware and the operating systems it hosts.

You can read his blog, titled "Fueling Digital Transformation with IBM Power", here.