Wednesday, September 28, 2022

Using SQL to get information about Journal Receivers

Earlier this year I wrote about using the QjoRtvJrnReceiverInformation API to get information about journal receivers. I noticed that in a recent batch of PTFs a new SQL View, JOURNAL_RECEIVER_INFO, which provides all of the information I am interested in. It is easier to get the information I desire from the SQL View than the API, so why would I continue to use the API?

The View JOURNAL_RECEIVER_INFO is found in the QSYS2 library. I recommend that you run the following statement to see all of the columns and information it contains:

SELECT * FROM QSYS2.JOURNAL_RECEIVER_INFO LIMIT 10 ;

Tuesday, September 27, 2022

End of support for IBM i 7.3 announced

I was guessing that this was going to happen either this or next month, and it has. In an announcement dated September 27, 2022, IBM revealed that they will discontinue support for release IBM i 7.3 on September 30, 2023.

7.3 was released on April 15, 2016. With it going off support on September 30, 2023, it means that it will have been an active release for seven years and five and a half months.

You can read the full announcement, including the other product coming to end-of-life, here.

If you have a Power8 or higher and you are on IBM i 7.3 you need to start finalizing your plans to update your operating system to either 7.4 or 7.5.

Wednesday, September 21, 2022

Determining which objects have changed since last save

There is an old library, I am going to call it OLDLIB1, that is still in everyone's library list. I needed to determine if there are files in this library that are still changed. I have used the word "changed" rather than "used" as they mean two different things. A file is used when it is opened in a program, the data within might not be changed. A file is changed when a record is added to the file, modified, or deleted. At present the entire library is saved using the SAVLIB command once a week. But if files are still being used we need to do a more often backup.

I can get to this information using the Display Object Description command, DSPOBJD, but with that I have to build an output file and then search the output file for the information I need.

Fortunately the SQL View SYSTABLESTAT contains the information too in the following columns:

Tuesday, September 20, 2022

Using SQL to perform a domain lookup

We all develop our own algorithms to validate email addresses. There must be an "@" sign in the string, there must be a period after that, etc. Most of these I have seen fail due to the domain name part of the email address. There are now Top Level Domains, TLD, (the letters that come after the "dot") that are longer than three characters. I know of many non-IBM i applications that use a DNS lookup to determine if the domain is active, although this will not guarantee that the domain has email.

As part of the IBM i 7.5 and 7.4 TR6 release is a simple way to do a DNS lookup using SQL.

Before I get started I need to state the obvious: What I am going to show you here will only work if your IBM i partition can connect to the internet. If it cannot then you will be unable to duplicate what I describe here.

The DNS lookup is performed by a Db2 for i Table Function, DNS_LOOKUP, which is found in the library QSYS2. Two parameters can be passed to the Table function:

Thursday, September 15, 2022

Using SQL to check the user password rules

I am sure when we sign on to a new partition and are prompted to change our user profile's password we spend several minutes trying to find a password we like that matches the partition's password rules.

As part of the new release IBM i 7.5 and 7.4 TR6 comes a new SQL table function that allows me to validate passwords to the partition's rules. SQL table function CHECK_PASSWORD uses all of the password system values in this hierarchy:

  • QPWDRQDDIF:  Required Difference in Passwords
  • Either:
    • QPWDRULES:  Password Rules
  • Or:
    • QPWDMINLEN:  Minimum Length of Passwords
    • QPWDMAXLEN:  Maximum Length of Passwords
    • QPWDLMTAJC:  Restriction of Consecutive Digits for Passwords
    • QPWDLMTCHR:  Restricted Characters for Passwords
    • QPWDLMTREP:  Restriction of Repeated Characters for Passwords
    • QPWDRQDDGT:  Requirement for Numeric Character in Passwords

Wednesday, September 14, 2022

Creating a reusable SQL View for a spool file

For the last few years I have been asked more often for a download, that can be loaded into Microsoft Excel, rather than paper report. Custom reports can be easily modified to include a "download file" option. Reports from within the ERP is more complicated. If I modify the ERP's report then I become responsible for it. Most ERP vendors will not provide support for any object I have modified. And then if an updated version of the object becomes available from the ERP, I would have to add my changes again. The solution I use is to generate the report, then "slice and dice" the spool file into a "download file".

I could copy the spool file into a physical file and then "slice and dice" using a RPG program. I don't have to do that anymore. I can build a SQL View using the SPOOLED_FILLE_DATA table function for my report, and then use standard SQL to "slice and dice" the contents into the columns I desire.

Wednesday, September 7, 2022

Using value from the spool file as the IFS file name

I was asked how it was possible to use a string within a spool file as the file name when it was copied to the IFS as a PDF. I have written in separate posts the parts that would be needed to achieve this, and this post will pull it all together.

In my scenario I have spool files of invoices. I want to copy these to create individual PDFs in an IFS folder.

I created a simple RPG program to create a couple of "invoice" spool files. These invoices were generated using the QSYSPRT printer file, and the user data of INVOICE. They both had the same layout, the only difference being the invoice number itself. For example:

 +
 +
 +
 Invoice : 81719
 +
 +
 +

Tuesday, September 6, 2022

SQL view listing Binding Directory information

All I wanted was a quick way to check which Binding Directories contain a certain service program. Included in IBM i 7.5 and 7.4 TR6 is a SQL View that can provide me with the information I desire.

If I wanted to see which Service Programs and Modules are contained within a Binding Directory I would use the Work With Binding Directory Entries command, WRKBNDDIRE:

WRKBNDDIRE BNDDIR(MYLIB/TEST)

Which shows the following:

Thursday, September 1, 2022

Making the source code line longer for RUNSQLSTM

I always place the code I use to create DDL tables, indexes, views, etc. in a source member. It has always frustrated me that the code could not go beyond the 80th column, without the compiling failing. As part of IBM i 7.5 and 7.4 TR6 a new special value is allowed in the Run SQL Statement command, RUNSQLSTM.

In the RUNSQLSTM command there is a parameter for the source margins, MARGINS, that allows me to give the position of the right margin of the code within the member. The default is 80, but now there is a keyword I can use in its place: *SRCFILE. This "tells" the compiler to use the entire length of the source member, rather than the first 80 characters only.

If I made a SQL statement that is greater than 80 characters in a source member, like this:

Wednesday, August 31, 2022

Find the number of times an SQL Index had been used

SYSTABLEINDEXSTAT times index used

When you are building SQL DDL Indexes to improve performance it is also imperative to find Indexes that are not being used. Having identified those a decision can be made on whether to delete these unused indexes, or not.

Finding this information introduced me to a SQL View I had not used before: SYSTABLEINDEXSTAT

SYSTABLEINDEXSTAT contains the columns I want, the number of times the index has been used and the date it was last used. Alas, the view SYSINDEXES does not contain that information.

I always recommend that, on the partition you use, you run the following statement at least once to see all of the information that is available to you:

SELECT * FROM QSYS2.SYSTABLEINDEXSTAT
 LIMIT 10 ;

Wednesday, August 24, 2022

Using something better than DSPDBR

RELATED_OBJECTS better than DSPDBR

I am sure we have all used the Display Database Relations command, DSPDBR, to establish any file's or table's dependent objects. We can also use a SQL Table Function to give us more (better) columns of data than the DSPSBR command does.

But before I start giving examples of using this Table function I need something to work with. Let me start with a very simple DDL table:

01  CREATE TABLE MYLIB.TABLE1
02  (FIRST VARCHAR(20))

I do not need to insert data into the table.

Next I will create an Index over my Table:

Wednesday, August 17, 2022

Getting information about MTI using SQL

MTI information via SQL

Maintained Temporary Indexes, MTI, are SQL Indexes that have been created automatically by the Db2 optimizer, without any outside intervention. The reason an MTI is created is there is no suitable existing Index to meet the requirements of a SQL query. MTI is just like any other SQL Index in my IBM i systems. MTI are temporary, as is suggested by their name, and are deleted when the partition is IPL-ed. A MTI might not be recreated the first time the SQL query is executed, it may take several times before the Db2 optimizer decides it is advantageous to create it. Therefore, to optimize your systems it is a good idea to review the Index Advisor on a regular basis, to determine if there are Indexes that should be created to alleviate the need for a MTI.

This begs the question: How can I see what MTI currently exist on my partition?

Fortunately there is a way using the MTI_INFO Table function.

The Table function has two optional parameters:

  1. Table schema
  2. Table name

Wednesday, August 10, 2022

A better way to find which file an Alias was built for

find alias llibrary file member

Earlier this year I described a way I could retrieve the schema/library and table/file that a SQL Alias had been built over. over the last few months I have discovered several other ways that will allow me to easily get to that information, and even the member too.

I tend to use SQL Alias for coping with SQL's inability to easily handle multi member files. I could use the Override Database command, OVRDBF, too but I prefer the ease of using the Alias as I can create, use, and then delete the alias all within the same program.

Before I start showing examples, I am going to need to create an Alias, ALIAS_3. Here I am going to create an Alias that will based on the source file DEVSRC, in my library MYLIB, and the source member TESTRPG:

CREATE OR REPLACE ALIAS MYLIB.ALIAS_3 FOR MYLIB.DEVSRC (TESTRPG)

Wednesday, August 3, 2022

Copying multiple spool files into one PDF

The task came in: I needed to take many spool files from an output queue and convert them into just one PDF. I have written about converting individual spool files into PDF, but now there was the need for the to be only one PDF.

My solution would need to:

  1. Merge all of the spool files into one spool file
  2. Convert the one spool file into a PDF in the IFS

Both of these I have done separately on many occasions; this was the first time to do them together.

I cannot use the spool files I used for this task, but I have two spool files in my personal output queue:

Tuesday, July 26, 2022

Which files are used by a Query?

query files used

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:

Wednesday, July 20, 2022

Access System Directory information via SQL

system directory entries

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.

Monday, July 18, 2022

Merlin comes to 7.3

ibm merlin ibmi 7.3When 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.

Wednesday, July 13, 2022

Find how to update the long comment column

sql comment on

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

Tuesday, July 12, 2022

New Power10 Servers announced

new power10 servers

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.

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

Wednesday, July 6, 2022

New SQL Table Function for Activation Group information

activation group ACTIVATION_GROUP_INFO

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.

Wednesday, June 29, 2022

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

stop table delete with restrict on drop

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

Tuesday, June 28, 2022

Moving content of data structure into an array

data strucutre subfields in 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:

Wednesday, June 22, 2022

Removing duplicate characters from a string

rpg scan replace %scanrpl

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.

Tuesday, June 21, 2022

Extra parameters added to Call command to prevent decimal data error

new parameters added to call command

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:

Friday, June 17, 2022

More fun with the Boolean data type

using unknown instead of null in sql

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:

Wednesday, June 15, 2022

New data type for SQL - Boolean

sql boolean data type

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:

Friday, June 10, 2022

9 years and counting!

Wow! It was nine years ago I wrote the first post of this blog. Here I am almost 800 posts later and I am still writing. Fortunately, IBM has "cooperated" and has released a lot of really interesting and exciting things in this time, giving me lots of material to write about.

This where I pick my favorite five posts of the past 12 months, which is always a struggle as there is always so many things to consider:

Wednesday, June 8, 2022

New RPG op-code to send messages to job log

rpg snd-msg %msg %target

Another addition to the RPG programming language with IBM i 7.5 and 7.4 TR6 is an operation code that allows me to write to the current job's job log.

I have been using the SQL procedure LPRINTF to write to the job log since 2019. Now I have the ability to do so with native RPG.

The new operation code is called Send Message, SND-MSG, and is accompanied by two new optional Built in Functions, BiF: %MSG and %TARGET.

Just how simple it is to write to job log I will demonstrate below. Let me start with my first example program's source code:

01  **free
02  dcl-s Text varchar(50) inz('Second message') ;

03  snd-msg 'First message' ;

04  snd-msg Text ;

05  snd-msg *INFO 'Third message' ;

Wednesday, June 1, 2022

New SQL built in function allows for validating data types

sql try_cast cast

I am sure many of us have found that when we try and cast, in SQL, from one data type to another the “casting" will fail. This is especially true when moving character representations of numbers or dates to a decimal or date type. With the new release IBM i 7.5 and 7.4 TR6 there is a new SQL built in function that allows for the testing of a cast before it is performed.

Before I start explaining this new built in function let me define the table I will be using, TESTTABLE:

CREATE TABLE MYLIB.TESTTABLE
(COLUMN1 CHAR(10),
 COLUMN2 CHAR(10)) ;

It has two columns I will be using in this post. For now, I am only interested in the contents of the column COLUMN1, which I can see using the following SQL statement:

Monday, May 30, 2022

Updated IBM i roadmap for 2022

With IBM i 7.5 being released IBM has updated its IBM i Roadmap image:


Click on image to see larger version

I find interesting:

Wednesday, May 25, 2022

Start testing the new date rule

1970 rule window dates

Since the beginning of time on OS/400, the beloved ancestor of IBM i, six long dates have always used the "1940 rule" to determine the century.

If Year >= 40 then Century = 19
If Year < 40 then Century = 20

2039 is not that far in the future, only 17 years away, IBM has developed a replacement rule, "1970 rule".

If Year >= 70 then Century = 19
If Year < 70 then Century = 20

As part of the IBM i 7.5 announcement IBM has said that the new rule will be implemented in a future release or Technology Refresh.

IBM is giving a foretaste of the new rule in 7.5. I used the word "foretaste" as the "1940 rule" has not been replaced, but the new rule can be implemented on top of it, if you so desire. The rule can either be applied to your entire system, which I do NOT recommend, or it can be implemented just to the current job.

Tuesday, May 24, 2022

TR PTFs for 7.4 and 7.3 available today

ibmi v7r4 tr6 v7r3 tr12 ptf

The PTFS for the two new Technology Refreshes:

  • IBM i 7.4 TR6
  • IBM i 7.3 TR12

Are now able for download as PTFs from IBM Support website.

Start planning to download these into a test partition, and get ready for the testing process to validate your applications with these TRs. Don't forget the database (SQL) and RPG PTFs come separately.

<whispers> It might be time to get the latest CUM PTFs to ensure you get all the available PTFs for your release </whispers>

Wednesday, May 18, 2022

New way in RPG to monitor for errors

on-excp in monitor group

In RPG we have been able to monitor for errors, using a Monitor group, for years. Until IBM i 7.5 and 7.4 TR6 we could only monitor for program and file status codes. I have always preferred CL’s MONMSG that allowed me to monitor by message id instead.

As part of the new release, or new TR for 7.4, there is a new RPG operation code, ON-EXCP, that can be used within a Monitor group to monitor for a specific error message or messages. The syntax is very similar to that of the ON-ERROR operation code:

monitor ;
  Something
on-excp 'CPF1234' : 'RNX1234' : 'RNQ1234' ;
  Do something else to mitigate the error
endmon ;

I am going to give some examples below so you can understand how ON-EXCP works. Here is my RPG program’s source code:

Tuesday, May 17, 2022

New TR for IBM i 7.3

ibmi v7r3 technology refresh tr12

I am certain in all the videos of the announcements for IBM i 7.5 and 7.4 TR6 we were told there would be no more Technology Refreshes, TR, for 7.3.

Well, it appears that there is to be another Technology Refresh for 7.3, TR12. I found it on the Technology Refresh information page for 7.3 here.

It is available on the same day, May 24, that 7.4 TR6 is.

What is available in this TR for 7.3?

Wednesday, May 11, 2022

Making a list of journal receivers with the detach date

journal receiver information from api

I wanted to write a program that would delete old journal receivers, ones detached from the journal and more than a certain number of days old (to be determined later). I can see the detach date when I use the Display Journal Receiver Attributes command, DSPJRNRCVA, but I can only do this for one receiver at a time, and the command only allows for display or print. Looking at my chart of SQL Views and Table functions I found that is not one for Journal Receivers. Alas, this leaves me having to use an API to get to this information.

The information I desire is:

  • Journal name and library
  • Journal receiver name and library
  • Attach date (timestamp) of the receiver
  • Detach date (timestamp) for the receiver

There is an API that will return to me the information. It comes with the long name: QjoRtvJrnReceiverInformation. Before I go into the example of using this API let me get started with how I made a list of all the receivers in a library. Here I can use a SQL Table function, OBJECT_STATISTICS. I use the following statement to display a list of all the receivers in a library.

SELECT OBJNAME,OBJLIB
  FROM TABLE(QSYS2.OBJECT_STATISTICS(<library name>,'*JRNRCV','*ALLSIMPLE'))

Tuesday, May 3, 2022

New version 7.5 announced

ibmi 7.5 and 7.4 tr6

Most of us knew it was coming, so there is no surprise that a new release for IBM i has been announced. The new release, 7.5, accompanies a Technology Refresh update for version 7.4 TR6. As only two versions of the IBM i are supported with TRs it does also mean the end of TRs for version 7.3. If you are using a partition with 7.3, and you can upgrade, this should be the time to consider doing so to at least 7.4.

IBM i 7.5 will only run on Power 9 and 10 servers.

Many of the new features of 7.5 will not be supported by 7.4 TR6. I will make note here of which are additions or changes that apply to 7.5 only.

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

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

Monday, April 4, 2022

When divine intervention is needed

There are times in everyone's work life when things go from bad to worse, especially when you are trying to fix something in the middle of the night.

If you feel that divine inspiration is needed you are in luck as there is a patron saint of computers and the internet: Saint Isidore of Seville.

In 1997 the head of the Catholic church, John Paul II, designated St Isidore of Seville to this role. You can learn more about him here. Today, April 4, is his saint's day.

If you are asking for help make sure you are asking St Isidore of Seville, as there is another St Isidore who is the patron saint of farmers.

Thursday, March 31, 2022

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.

Wednesday, March 30, 2022

SQL indexes suggested from the job log

sql ddl indexes advised by debug

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.

Thursday, March 24, 2022

Changes to the RFE process

new 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.

Wednesday, March 23, 2022

Encrypting data with SQL

encrpyt columns with sql

Db2 for i provides us the ability to encrypt data in the database using three of the more popular methods:

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 ;

Monday, March 14, 2022

SQL has a constant for pi

using pi in sql

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 22/7. 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 223/71 and 22/7. It is not exactly 22/7, 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:

Wednesday, March 9, 2022

Media library information with SQL

media library information and status

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.

Wednesday, March 2, 2022

Finding open cursors within a job

looking for open sql cursors

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: