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.

Is this proof iNext is IBM i 7.5?

IBM's own Support Roadmap shows a new release coming this year, which they have given the marvelously mysterious name: iNext. The only unknown is would this new release be 7.5 or 8.1 .

I have heard whispers from within the IBM i community that the new release would be 7.5 . Although no-one could offer any hard proof that it would be.

I received an email from AG (full name withheld to protect his anonymity), that he had found a file in IBM's manual downloads site for QMGTOOLS a file with the name: qmgtool750.savf

SQL scalar function to add quote characters around your strings

I was working on an interface between two applications. I needed to provide to the receiving application data that would be delimited with the quote character ( " ) and of varying length. The data was coming from an older application with DDS files and fixed width fields.

In my first version I used the following to provide a person's name in the desired format:

01 SELECT '"' || RTRIM(LAST_NAME) || ', ' || RTRIM(FIRST_NAME) || '"' 02 FROM PERSON

Checking for database connections between partitions

In Db2 for i a "database" is the equivalent of a partition in IBM i "speak". Many of us work with multiple partitions, and there are programs that fetch and send data between them. The two most common ways you can send and receive data between partitions is using a DDM file and the SQL three part name.

Over my three decades of programming I have used both many times, although in the past few years I have used the SQL three part name more often. To go from just fetching a few records from a file, to copying whole files. But how can I tell if a particular job is using these kind of connections to fetch or send data?

Fortunately there is a SQL table function, ACTIVE_DB_CONNECTIONS, that allows me to see those connections.

Using SQL to copy data between partitions

I work in a multi-partition environment and frequently use the three part name in a SQL statement to "pull" data from one partition onto another. I was recently asked if I could give some more examples of it in action.

The three part names describes a method where I can get information from another SQL database. With IBM i that translates to another partition. Using IBM i terms the format of the three part name is:

partition.library.file

Used in its simplest it could just be:

Local User Group 2022 update

We are one week into Local User Group month, I want to thank everyone who contacted me with information about Local User Groups, LUG.

Most confirmed the details of existing groups.

But I did receive the bad news that the Vermont Midrange User Group closed its doors in 2020.

On a brighter note Common Switzerland, German language site, fixed their redirect so their site is active again.

Please keep sending in information about LUG from anywhere in the world by using the Contact Form, on the right.

Still no news from Asia. This surprises me with the prevalence of Power servers running IBM i in India and Japan.

Confirmed so far:

• Closed
  • Vermont Midrange User Group, USA
• Rediscovered
  • Common Switzerland (German)

Finding which job submitted a batch job

An occasional question I get is there a way to determine which job submitted a particular batch job. Fortunately by using a SQL table function I can retrieve that information.

To be able to get to retrieve this information the batch job must be active, running, if it not what is described here will not work.

Before I starting showing SQL I need a program that will submit a job to batch.

Local User Groups 2022

Every February I have what I like to call Local User Group Month. During this month I maintain the list of user groups I have on this web site. The list can be found by clicking on the "IBM i User Groups" button on the top of this, or any other, page.

Why do I do this? It is because I believe that taking part in an IBM i user group benefits all of us. We can learn from one another, and share our experience too.

I call upon all of you readers to help me too. If you know of any Local User Group anywhere in the world that are not on the list, groups that have shut down, or groups that have changed their website address please contact me using the Contact form on the right.

Just going through my list from last year I found that...