Viewing HTTP server information using SQL

I want to give credit to IBM's Db2 for i team for continually adding SQL views, table functions, etc. to information that was not easy to get using APIs or other tools. Another example of their excellent work, introduced with the latest Technology Refreshes, is the view HTTP_SERVER_INFO. Previously this information was only available using the Web Administration for i GUI.

This view, which is located in the library QSYS2, displays information about the Apache server in the HTTP server for IBM i, and will only display data about the enabled and active functions.

As usual with my examples of Db2 for i views and table functions I am not going to list all of the available columns, just the ones I found interesting:

Removing apostrophe in a field using SQL

There are occasions when interfacing data to non-IBM i database where an apostrophe ( ' ) in a field can cause a conversion error. In this post I am going to give some simple examples of how apostrophes can be converted to something else using SQL and RPG.

Let me start with the DDS file I will be using in these examples. The file is called TESTFILE and contains one field:

A R TESTFILER A COMPNAME 35A

The field contains the following three records:

It's been IBM i for longer than AS/400

In a post on LinkedIn yesterday Steve Will, chief architect of the IBM i, shared an interesting milestone in the life of the this operating system.

This operating system had been known as IBM i for a long as it was known as AS/400.

Subtracting days from a "date" when it is a number in a file

The germ for this post came from a question:

How can I subtract 35 days from a date in my files using SQL? The date is a packed number

Most of us who deal with older databases find that the "dates" in the files are not true dates fields, they are numbers masquerading as dates. I have, in a previous post, shown how to change a numeric representation of a date to a date with SQL, this is taking it a step further by converting the date back to a number and updating the file with the new value.

In these examples I will be using the numeric representation of the date in two formats:

How to view pending database commits using SQL

Another addition to Db2 for i in the latest Technology Refresh, IBM i TR2 and 7.3 TR8, was a view called DB_TRANSACTION_INFO. This view lists what the IBM documentation describes as: "returns one row for each commitment definition".

What does that mean? Having played with this View for a while I have found that it just lists all uncommitted database transactions to a file or table that is using commitment control. Database operations to files and tables that do not use commitment control do not appear in this View.

There are far more columns in this View than I am going to use in my examples, so I am going to say to you if you want to know what all of the columns are click on the link to IBM's documentation at the bottom of this post.

Viewing all of the subsystems autostart jobs in one place

If I needed to see which autostart jobs were in my IBM i partitions I would either have to use the Display Subsystem Description command, DSPSBSD, to view the autostart jobs for each subsystem or use an API. Fortunately the latest Technology Refreshes include a Db2 for i View that gives me the information I want.

Before I give examples of using the new View I want to show how to get to the same information using the DSPSBSD command so that you can compare the data from the two.

If I want to see what autostart jobs there are in the QCTL subsystem I would use the following command:

DSPSBSD SBSD(QCTL)

When the Enter key is pressed the following menu is displayed:

Listing SQL Indexes' keys

Most of the time I do not care what the keys are for any SQL Indexes I have built over DDL tables or DDS tables. When I want to extract data from a table or file using SQL I build the statement using the table or physical file, and Db2 of i is smart enough to find the best access paths (logical file or SQL Index) for the statement.

I recently encountered a company where their programmers had been building Indexes. Building Indexes is not a problem, but each programmer had been building their own Indexes without consulting with the other members of the team. The team leader was concerned that they now had many indexes that had the same keys. He asked me if I could help him identify the following:

1. Indexes built over which table or file
2. The keys of those Indexes

Make second line appear and disappear on a subfile

I was asked how to do this by someone who said that no-one in his department knew how to: have a second record appear in a subfile when a function key is pressed, and disappear when the function key was pressed again.

Fortunately this is a simple thing to do with just three keywords in the display file's DDS:

• SFLDROP
• SFLFOLD
• SFLMODE

And there is not much we have to do in the RPG program that uses the subfile.

I have deliberately stripped down the display file and RPG code to what I consider a minimum to, in my opinion, makes it easier to understand.

Let me jump right in and show you the display file I will be using in this example:

Selects with Intersect to find common records in two files

Having written about Selects with Union I thought it was time to mention to Selects with Intersect.

Select statements with the INTERSECT combines the matching results from two tables into one set of results. To reiterate "matching" means that the same value must appear in the given column(s) in both files.

I have two example tables with the same column names, they contain the following data:

Table 1	Table 2
ANGELA BETTY DOROTHY EVELYN GEORGINA	GEORGINA FRANCESCA EVA DOROTHY CAROLINE ANGELA

Writing to the same spool file from multiple programs

The question sounded simple: How can I write to the same spool file from two programs?

I have written before about copying from more than one spool file to create a new one. But this time I will be writing to the same spool file directly from two programs.

Let me start with the DDS for the printer file:

01 A R HEADER 02 A 1'PAGE HEADER' 03 A SKIPB(001) ***************************************************************** 04 A R FORMAT01 05 A 1'PROGRAM 1' 06 A SPACEB(001) 07 A COUNTER 3 0 +2EDTCDE(Z) ***************************************************************** 08 A R FORMAT02 09 A 1'PROGRAM 2' 10 A SPACEB(001) 11 A COUNTER R +2REFFLD(COUNTER *SRC) 12 A EDTCDE(Z) ***************************************************************** 13 A R ENDREPORT 14 A 1'* * * END OF REPORT * * *' 15 A SPACEB(001)