Wednesday, April 27, 2022

Placing SQL functions into the SQL Insert statement

To write the posts for this website I need to have examples DDL tables. I often use a Table whose first column contains the row number and a second contains some kind of random number.

While I was illustrating how to fill one of these tables it struck me that there was an easier way to do this, having all the logic in the Insert statement itself.

This is the source code for the Table I commonly use:

01  CREATE TABLE MYLIB.FIRST_TABLE
02    FOR SYSTEM NAME "TABLE1"
03  (FIRST_COLUMN FOR COLUMN "FIRST" VARCHAR(10),
04   SECOND_COLUMN FOR COLUMN "SECOND" SMALLINT,
05   UNIQUE (FIRST_COLUMN)) ;

Wednesday, April 20, 2022

Printing information about SQL RPG programs

prtsqlinf

I am using the word "program" in a generic way here. In reality what I am writing in this post can also be used for SQL packages (object type *SQLPKG) and service programs too.

What I wanted to know was which SQL statements executed within a program, and the tables and indexes these statements used. Fortunately, there is a command for that: PRTSQLINF, Print SQL Information. Before I show what this command can do, I need to have a table and program I can use to create the information that the command will return.

First I need a table. I know I will not win awards for originality of this table's name; I have called it TESTTABLE.

01  CREATE TABLE MYLIB.TESTTABLE
02  (FIRST_COLUMN FOR COLUMN "COL1" CHAR(10),
03   SECOND_COLUMN FOR COLUMN "COL2" INT) ;

Wednesday, April 13, 2022

ACS improves by 0.0.0.2

Update November 11, 2022: New version is currently available for download here


A couple of times a year IBM gives us a new release of Access Client Solutions, ACS. It would appear that Scott Forstie, Db2 for i Business Architect, has a similar sense of humor as mine, as I was amused by his announcement of the new release:

IBM i Access Client Solutions just improved by 0.0.0.2!

The previous version was 1.1.8.8 and now we have the brand new 1.1.9.0!

You can check which release you currently have from the "Access Client Solutions" window. Click on "Help":

Wednesday, April 6, 2022

List rows in IFS files that contain a certain string

listing all rows in ifs files containing a string

The question was an interesting one: How is it possible, in SQL, to list all of the files in the IFS that contain a certain string?

There are a number of SQL table functions that allow me to get to information about the files in IFS folders, and others to "read" their contents. This is a good excuse to combine the two.

I am going to place three files in my personal IFS folder, MyFolder, then search those for the desired string. I created three DDL tables in my library, MYLIB. OK, I created one table and then cloned it two times to make the three tables. I populated them with just a few rows of data. As the tables are identical, I can easily combine the results from the three in one SQL statement using the UNION clause:

SELECT 'TABLE1',A.* FROM TABLE1 A
UNION
SELECT 'TABLE2',B.* FROM TABLE2 B
UNION
SELECT 'TABLE3',C.* FROM TABLE3 C 
ORDER BY 1,2