Determining which objects have changed since last save

There is an old library, I am going to call it OLDLIB1, that is still in everyone's library list. I needed to determine if there are files in this library that are still changed. I have used the word "changed" rather than "used" as they mean two different things. A file is used when it is opened in a program, the data within might not be changed. A file is changed when a record is added to the file, modified, or deleted. At present the entire library is saved using the SAVLIB command once a week. But if files are still being used we need to do a more often backup.

I can get to this information using the Display Object Description command, DSPOBJD, but with that I have to build an output file and then search the output file for the information I need.

Fortunately the SQL View SYSTABLESTAT contains the information too in the following columns:

Using SQL to perform a domain lookup

We all develop our own algorithms to validate email addresses. There must be an "@" sign in the string, there must be a period after that, etc. Most of these I have seen fail due to the domain name part of the email address. There are now Top Level Domains, TLD, (the letters that come after the "dot") that are longer than three characters. I know of many non-IBM i applications that use a DNS lookup to determine if the domain is active, although this will not guarantee that the domain has email.

As part of the IBM i 7.5 and 7.4 TR6 release is a simple way to do a DNS lookup using SQL.

Before I get started I need to state the obvious: What I am going to show you here will only work if your IBM i partition can connect to the internet. If it cannot then you will be unable to duplicate what I describe here.

The DNS lookup is performed by a Db2 for i Table Function, DNS_LOOKUP, which is found in the library QSYS2. Two parameters can be passed to the Table function:

Using SQL to check the user password rules

I am sure when we sign on to a new partition and are prompted to change our user profile's password we spend several minutes trying to find a password we like that matches the partition's password rules.

As part of the new release IBM i 7.5 and 7.4 TR6 comes a new SQL table function that allows me to validate passwords to the partition's rules. SQL table function CHECK_PASSWORD uses all of the password system values in this hierarchy:

• QPWDRQDDIF:  Required Difference in Passwords
• Either:
  • QPWDRULES:  Password Rules
• Or:
  • QPWDMINLEN:  Minimum Length of Passwords
  • QPWDMAXLEN:  Maximum Length of Passwords
  • QPWDLMTAJC:  Restriction of Consecutive Digits for Passwords
  • QPWDLMTCHR:  Restricted Characters for Passwords
  • QPWDLMTREP:  Restriction of Repeated Characters for Passwords
  • QPWDRQDDGT:  Requirement for Numeric Character in Passwords

Creating a reusable SQL View for a spool file

For the last few years I have been asked more often for a download, that can be loaded into Microsoft Excel, rather than paper report. Custom reports can be easily modified to include a "download file" option. Reports from within the ERP is more complicated. If I modify the ERP's report then I become responsible for it. Most ERP vendors will not provide support for any object I have modified. And then if an updated version of the object becomes available from the ERP, I would have to add my changes again. The solution I use is to generate the report, then "slice and dice" the spool file into a "download file".

I could copy the spool file into a physical file and then "slice and dice" using a RPG program. I don't have to do that anymore. I can build a SQL View using the SPOOLED_FILLE_DATA table function for my report, and then use standard SQL to "slice and dice" the contents into the columns I desire.

Using value from the spool file as the IFS file name

I was asked how it was possible to use a string within a spool file as the file name when it was copied to the IFS as a PDF. I have written in separate posts the parts that would be needed to achieve this, and this post will pull it all together.

In my scenario I have spool files of invoices. I want to copy these to create individual PDFs in an IFS folder.

I created a simple RPG program to create a couple of "invoice" spool files. These invoices were generated using the QSYSPRT printer file, and the user data of INVOICE. They both had the same layout, the only difference being the invoice number itself. For example:

+ + + Invoice : 81719 + + +

SQL view listing Binding Directory information

All I wanted was a quick way to check which Binding Directories contain a certain service program. Included in IBM i 7.5 and 7.4 TR6 is a SQL View that can provide me with the information I desire.

If I wanted to see which Service Programs and Modules are contained within a Binding Directory I would use the Work With Binding Directory Entries command, WRKBNDDIRE:

WRKBNDDIRE BNDDIR(MYLIB/TEST)

Which shows the following:

Making the source code line longer for RUNSQLSTM

I always place the code I use to create DDL tables, indexes, views, etc. in a source member. It has always frustrated me that the code could not go beyond the 80th column, without the compiling failing. As part of IBM i 7.5 and 7.4 TR6 a new special value is allowed in the Run SQL Statement command, RUNSQLSTM.

In the RUNSQLSTM command there is a parameter for the source margins, MARGINS, that allows me to give the position of the right margin of the code within the member. The default is 80, but now there is a keyword I can use in its place: *SRCFILE. This "tells" the compiler to use the entire length of the source member, rather than the first 80 characters only.

If I made a SQL statement that is greater than 80 characters in a source member, like this:

Find the number of times an SQL Index had been used

When you are building SQL DDL Indexes to improve performance it is also imperative to find Indexes that are not being used. Having identified those a decision can be made on whether to delete these unused indexes, or not.

Finding this information introduced me to a SQL View I had not used before: SYSTABLEINDEXSTAT

SYSTABLEINDEXSTAT contains the columns I want, the number of times the index has been used and the date it was last used. Alas, the view SYSINDEXES does not contain that information.

I always recommend that, on the partition you use, you run the following statement at least once to see all of the information that is available to you:

SELECT * FROM QSYS2.SYSTABLEINDEXSTAT LIMIT 10 ;

Using something better than DSPDBR

I am sure we have all used the Display Database Relations command, DSPDBR, to establish any file's or table's dependent objects. We can also use a SQL Table Function to give us more (better) columns of data than the DSPSBR command does.

But before I start giving examples of using this Table function I need something to work with. Let me start with a very simple DDL table:

01 CREATE TABLE MYLIB.TABLE1 02 (FIRST VARCHAR(20))

I do not need to insert data into the table.

Next I will create an Index over my Table:

Getting information about MTI using SQL

Maintained Temporary Indexes, MTI, are SQL Indexes that have been created automatically by the Db2 optimizer, without any outside intervention. The reason an MTI is created is there is no suitable existing Index to meet the requirements of a SQL query. MTI is just like any other SQL Index in my IBM i systems. MTI are temporary, as is suggested by their name, and are deleted when the partition is IPL-ed. A MTI might not be recreated the first time the SQL query is executed, it may take several times before the Db2 optimizer decides it is advantageous to create it. Therefore, to optimize your systems it is a good idea to review the Index Advisor on a regular basis, to determine if there are Indexes that should be created to alleviate the need for a MTI.

This begs the question: How can I see what MTI currently exist on my partition?

Fortunately there is a way using the MTI_INFO Table function.

The Table function has two optional parameters:

1. Table schema
2. Table name

A better way to find which file an Alias was built for

Earlier this year I described a way I could retrieve the schema/library and table/file that a SQL Alias had been built over. over the last few months I have discovered several other ways that will allow me to easily get to that information, and even the member too.

I tend to use SQL Alias for coping with SQL's inability to easily handle multi member files. I could use the Override Database command, OVRDBF, too but I prefer the ease of using the Alias as I can create, use, and then delete the alias all within the same program.

Before I start showing examples, I am going to need to create an Alias, ALIAS_3. Here I am going to create an Alias that will based on the source file DEVSRC, in my library MYLIB, and the source member TESTRPG:

CREATE OR REPLACE ALIAS MYLIB.ALIAS_3 FOR MYLIB.DEVSRC (TESTRPG)

Copying multiple spool files into one PDF

The task came in: I needed to take many spool files from an output queue and convert them into just one PDF. I have written about converting individual spool files into PDF, but now there was the need for the to be only one PDF.

My solution would need to:

1. Merge all of the spool files into one spool file
2. Convert the one spool file into a PDF in the IFS

Both of these I have done separately on many occasions; this was the first time to do them together.

I cannot use the spool files I used for this task, but I have two spool files in my personal output queue: