Which files are used by a Query?

This is a question I am asked enough for me to want to write this so I can direct the askers here. The question is:

How can I discover the files used by all the Query/400 objects in a library?

The Query/400 object is called a Query Definition, and is the following object type: *QRYDFN

To generate a list of the files they use is a two-step process.

The first process is to create an output file that contains the data. I use the Display Program Reference command, DSPPGMREF, to generate the output file. If I wanted to list all the files used by all of the Query definitions in the library MYLIB I would use the following:

Access System Directory information via SQL

I was asked for a list of all the enabled user profiles that are enrolled in the system directory on a partition. I know I could use the Display Directory Entry command, DSPDIRE, to create an outfile of the information:

DSPDIRE OUTPUT(*OUTFILE) OUTFILE(QTEMP/WOKFILE) DETAIL(*FULL) OUTFILFMT(*TYPE1)

Surely I would only want to retrieve the information for enabled profiles, or perhaps have a way to validate if a single user profile is enrolled in the system directory.

After some poking around I found a number of files in the library QUSRSYS that would give me the information I wanted. As the data is in files I can use SQL to get data and format the results in a way I would want, in real time.

Merlin comes to 7.3

When the announcement for IBM i 7.5 was made we were introduced to Merlin. This was made available for IBM i 7.4 TR6 too.

I do not remember there being any mention of Merlin being made available for earlier releases. It appears that I missed something as earlier this month IBM announced that Merlin would be available for IBM i 7.3 too.

For more information you will find the announcement here.

Personally I think this is a great move by IBM to broaden the number of their customers who can use Merlin, as there still are a lot of companies still running IBM i 7.3 on their partitions.

Find how to update the long comment column

Those of you who have used the SYSCOLUMNS and SYSTABLES views must have noticed that there is a column called LONG_COMMENT. On the partitions I work on this column is usually null. What is purpose of this column? And how is it updated?

A quick search in IBM's documentation introduced me to the COMMENT ON SQL statement. Before I give examples of how to use it, I am going to need a SQL DDL table to use:

01 CREATE OR REPLACE TABLE MYLIB.FIRST_TABLE 02 FOR SYSTEM NAME "TABLE1" 03 (FIRST_COLUMN FOR "FIRST" VARCHAR(20), 04 SECOND_COLUMN FOR "SECOND" VARCHAR(20)) 05 ON REPLACE DELETE ROWS ; 06 LABEL ON COLUMN FIRST_TABLE ( 07 FIRST_COLUMN IS 'First col', 08 SECOND_COLUMN IS 'Column heading' 09 ) ; 10 LABEL ON COLUMN FIRST_TABLE ( 11 FIRST_COLUMN TEXT IS 'First column', 12 SECOND_COLUMN TEXT IS 'Column text' 13 ) ; 14 LABEL ON TABLE FIRST_TABLE IS 'This is the first table' ;

New Power10 Servers announced

September last year IBM announced the first of the Power servers that included the Power10 chips, the E1080.

Today more Power10 based servers have been announced. All of these are smaller than the "flagship" E1080, but still pack considerable power (pun intended).

Click on image to see larger version

I am sure all of my regular readers know that I am "software" guy, not "hardware". Rather than embarrass myself, by not explaining the new servers features properly, I am giving you the links I have found about these new servers.

• IBM Newsroom: IBM Expands Power10 Server Family to Help Clients Respond Faster to Rapidly Changing Business Demands
• Redbook for Power10 servers
• Power10 documentation
• Individual server specifications
  • S1014 server
  • S1022 server
  • S1024 server

A big disappointment is that the newly announced E1050 will not run the IBM i operating system.

New SQL Table Function for Activation Group information

New release IBM i 7.5 and the corresponding Technology Refresh for 7.4, TR6, has given us a SQL Table Function that allows us to see information about the active activation groups within a job. In the past I have been able to get this information using the Work With Job command, WRKJOB:

WRKJOB JOB('*') OPTION(*ACTGRP)

The output options are limited to display ( * ) or printed output ( *PRINT ). For years I have taken printed output from commands and broken them apart into files either using SQL or RPG. It is nicer to have a View or Table Function where I do not have to do this anymore.

Change to Create Table statement to stop accidental deletion of the Table

It is always a danger that someone will accidentally drop (delete) the wrong file, not maliciously just accidentally.

01 CREATE TABLE MYLIB.TABLE1 02 (COLUMN1 CHAR(1)) ; 03 DROP TABLE MYLIB.TABLE1 ; Statement ran successfully

Within the new release of IBM i 7.5 is an addition to the Create Table statement that can prevent this. This does appear to be only in 7.5, I cannot find a mention of this in the documentation for IBM i 7.4 TR6.

This addition to the Create Table statement is: WITH RESTRICT ON DROP

Moving content of data structure into an array

I have been asked this same question a few times during the last couple of weeks: What is easiest way to move data from a data structure's subfields into an array?

Asking some other people I know how they would do it, several of them did not know of this simple way.

Here is the start of my code:

Removing duplicate characters from a string

I was asked how I would remove duplicate characters from a string just using RPG. I has been some time since I came up with an all RPG solution, so I accepted the challenge.

The string of data is in a file, TESTFILE, in the field F1. The sample string I used in F1 was:

SELECT F1 FROM TESTFILE ; F1 -------------------- abcdefghabcdijkl

I have more than one of these characters in the string: a, b, c, and d. And only one of these: f, g, h, i, j, k, and l. My goal is for a single character of all of these.

Extra parameters added to Call command to prevent decimal data error

Anyone who has programmed on IBM i has experienced this frustration. I want to call a CL program from a command line. I type:

CALL CL_PGM ('A' 12)

And I am presented with the following error:

MCH1202 received by procedure CL_PGM. (C D I R)

For the beginner this is cryptic. When I drill down into the job log I can see what really happened:

More fun with the Boolean data type

IBM i 7.5 introduces a synonym for NULL, UNKNOWN. Having performed a few tests, I can confirm I can use it just as I would NULL.

For example I can use it when inserting data into a SQL Table. The first column in this Table is a Boolean data type, the second is character.

INSERT INTO QTEMP.TESTTABLE VALUES('true','1'),('false','2'),(DEFAULT,'3'), ('true','4'),('false','5'),('true',UNKNOWN)

I have used a multiple rows insert to insert six rows with one Insert statement. Notice how I have used UNKNOWN in the data for the last row to be inserted.

I check the contents of this file with the following SQL statement:

New data type for SQL - Boolean

A new data type has been added to Db2 for i's Data Definition Language, DDL, as part of IBM i 7.5, but was not added to IBM i 7.4 TR6. A Boolean data type.

Being Boolean it should only contain two values, but this can contain three possible values:

1. True
2. False
3. Null – when this contains no data

I love this. For many, many years I have been creating "Boolean" like columns or fields to contain a sorta kinda true/false logic. But as the columns/fields were either character or numeric they could contain any valid value of that data type. Now I can have a column to denote things like:

• Item is in-stock
• Item is in a backlog status
• Certain information has been provided or accompanies whatever

You get the idea.

A Boolean column can be defined in a SQL table as simply as: