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)) ;

Printing information about SQL RPG programs

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) ;

ACS improves by 0.0.0.2

 

The original contents of this page have become obsolete, go to this page for up-to-date information.

 

List rows in IFS files that contain a certain 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

World backup day

A day like today is a good reminder of the importance of backups. I am sure we have all had to restore data from a backup many times in our lives in IT.

Do you know if your company's backup are valid? It always surprises me how many company's purchase a new device for backups, tape drive or VTL, and never try to do a test restore. Trust me you want to make sure you know how to restore data from the device before your really need to.

Not really part of backups but make sure you only retain backups for the time they might be needed, this varies by industry and would imagine by country too. Once the backup has passed the retention date destroy it. I have been through an e-discovery process, it became an almost full-time job for a month to find every backup that was on premise, off site, and even in the IT members cars and homes. It gets expensive too when you are asked to restore data from tapes you no longer have a tape drive for, and you have to contract with a third party to do it on your behalf. It is so much easier to tell the auditors that, in agreement with your company's legal department and the written company policy, all backups are destroyed after the designated period of time.

SQL indexes suggested from the job log

I have written before about how Db2 for i will suggest indexes, using the SYSIXADV view or Navigator for i, that will improve the performance of your SQL programs, etc. There are times I need to know if any indexes are advised when a program has run just once.

Fortunately this information is written to the job log, when the statement is executed in the program.

Before I demonstrate that I need a SQL DDL table I can use:

CREATE OR REPLACE TABLE MYLIB.TABLE1 (FIRST VARCHAR(20), SECOND VARCHAR(20)) ON REPLACE DELETE ROWS

Notice that the table does not have a key.

Changes to the RFE process

This morning I received an email from IBM informing that there are changes coming to the RFE, Request For Enhancement, process.

IBM uses the RFE process to drive a lot of the enhancements they add to new releases and Technology Refreshes to all of their products, including IBM i. If you have an idea you think would be a valid improvement to IBM i you would a submit an RFE. Other people can vote on the RFE if they agree it would be a worthwhile enhancement.

I am not going to repeat what IBM has already published on their Community web site. What I am going to do is to give you this link to it here.

If you have a bookmark in your browser to the RFE old web page you will need to delete your existing one and create a new one as it is now found at a different URL.

I encourage you all to submit RFEs if you have any ideas, and vote on others. If you don't IBM will never know that wonderful idea you just came up with.

Encrypting data with SQL

December 14, 2023 Update:
256 byte enhanced version of AES encryption is available.

---

 

• Advanced Encryption Standard, AES
• RC2
• Triple DES, TDES

There are two ways we can do this, set an encryption password within a program and use that, or give the password with every insert or update. My preference is the first scenario.

Before I start encrypting data, I need a DDL table in which to put it all:

01 CREATE OR REPLACE TABLE MYLIB.TABLE1 02 (UNENCRYPTED VARCHAR(10), 03 TYPE_AES VARCHAR(128) FOR BIT DATA, 04 TYPE_RC2 VARCHAR(128) FOR BIT DATA, 05 TYPE_TDES VARCHAR(128) FOR BIT DATA, 06 PRIMARY KEY(UNENCRYPTED)) 07 ON REPLACE DELETE ROWS ;

SQL has a constant for pi

Today is 3/14 in *MDY format, it is called pi day in recognition of the mathematical constant of the same name. Therefore, I thought that this would be the ideal occasion to write about the SQL scalar function PI().

Many years ago when I was at school, I was taught that pi is the fraction ²²/₇. This is not exactly true. The ancient Greek mathematician Archimedes stated that pi, or π in Greek, is a number be approximated as being between the fractions ²²³/₇₁ and ²²/₇. It is not exactly ²²/₇, but that is considered the most popular approximation.

SQL does better than any approximation. If I use the scalar function PI() I am given the true value of pi to 15 decimal places.

I say PI() as the function is not passed any parameters. Then why should it as pi is a constant.

How does PI() compare to the approximations? As I am using ACS's Run SQL Scripts I would just use the following statement:

Media library information with SQL

A recent upgrade to a new virtual tape library, VTL, product introduced me to the concepts of a tape library and to a new command Work with Media Library Status, WRKMLBSTS. The system admins came to me asked if there was a way I could get to the same information as is displayed by WRKMLBSTS.

They had become frustrated with the command as it did not offer the ability to output its results to an outfile or a spool file, that they would then copy to a file.

"Is there something in SQL you can work your magic with?" they asked.

A quick search of IBM's documentation portal gave me what was needed, a view called MEDIA_LIBRARY_INFO.

Finding open cursors within a job

It is possible to leave a SQL cursor when you exit a program.

Personally I prevent a cursor being left open by use of the SET OPTION SQL statement in my RPG program:

exec sql SET OPTION CLOSQLCSR=*ENDMOD ;

Or when I create the object I make sure that the Close SQL Cursor parameter, CLOSQLCSR, is set to *ENDMOD:

CRTSQLRPGI OBJ(MYLIB/MY_PROGRAM) SRCFILE(MYLIB/DEVSRC) CLOSQLCSR(*ENDMOD)

Not everyone is as vigilant or as cautious as I am. If a program ends abnormally, or the cursor is not closed with the SQL close statement:

End of Local User Group Month

Today being the last day of February it is also the last day of Local User Group 2022 month.

I am please to say that last week I found a LUG based in Singapore that covers all of the operating systems that run on IBM Power: AIX, IBM i, and Linux.

ASEAN Power (AIX, IBM i, Linux) Group

They have been added to the list of user groups on the User Groups page.

Even though Local User Group month has ended I am still interested in keeping the list of user groups up to date. If you learn of a new group, or one that has closed, please contact me with their details.

A thank you goes out to everyone who contacted me about their groups, or groups they know of.