Wednesday, November 23, 2022

Improvement to GENERATE_PDF

The GENERATE_PDF scalar function has been a wonderful addition to Db2 for i providing a simple way to convert a spool file to a PDF in the IFS.

My only complaint about it has been that I have to pass the spool file number to it, there was no '*LAST' option for the last spool file with that name. Fortunately with IBM i 7.5 and 7.4 TR6 came an enhancement to GENERATEPDF that allows '*LAST'.

Using ACS's "Run SQL Scripts" I could just do the following:

Tuesday, November 22, 2022

End of maintenance for Power8 servers

With IBM Power10 and Power9 being marketed it should not come as a surprise that IBM announced the end of their maintenance support for Power8 servers.

Power8 chips were announced in 2013, and became available in Power servers in June 2014.

If your employer is currently using a Power8 server, or older, you should show your superiors this announcement, linked below. This shows that the Power8 models may have different End of Service dates, but all of dates are in 2024. And start planning to move to a more recent Power server.

Announcement 922-117
Services withdrawal: Declaration of plan to discontinue lease, rental, and maintenance services for select POWER machines

If you do not know what your server is you can check using the method described here.

Wednesday, November 16, 2022

Using SQL QCMDEXC to simplify cleanup

Several months ago I publishing a post about finding the detached journal receivers in a partition. I mentioned if I want to delete those receivers I would create an output table, and then read that in a CL program and use the Delete Journal Receiver command, DLTJRNRCV, to delete the receivers one at a time.

Shortly after I received an email from Sue Romano, who is member of the Db2 for i development team, giving me a simpler alternative. Her examples used the QCMDEXC scalar function.

The QCMDEXC scalar function allows me to execute a CL command every time a row is returned in the results of a SQL statement.

Tuesday, November 15, 2022

Java errors with ACS

I want to start this to say that this is NOT an issued caused by the new version of ACS.

I use Microsoft Windows 11 on the computer I write these posts upon. Last week I applied the latest round of Window updates to my computer. After that whenever I opened any part of ACS I was presented with a Java error window, and then ACS would open.

Friday, November 11, 2022

ACS 1.1.9.1 now available

The fall release of IBM's Access Client Solution, ACS, is now available.

IMHO the quickest way to down-load the install zip file is via the ACS software installed on your personal computer.

  • Open your ACS window
  • Select "Help" on the menu at the top of the menu
  • Select "Check for updates" in the drop-down menu

Thursday, November 10, 2022

Virtual tour of Power9 and 10 servers

IBM has created a series of virtual tours of their Power9 and 10 servers.

Although I do not consider myself a "hardware guy", I did find the information interesting. I hope you do too.

IBM: Virtual tour of Power 9 and 10 family

Wednesday, November 9, 2022

Useful new options added to generate SQL source for object

I have already written about these two SQL procedures:

Both can retrieve the SQL statement(s) from a SQL object. With the release of IBM i 7.5 and 7.4 TR6 new parameters and values have been added to them both. I have to admit I do use GENERATE_SQL far more often than the other, therefore, the examples I give will be using this SQL Procedure.

Most of time I use GENERATE_SQL I am retrieving the DDL source from Tables, Indexes, Views, and Physical files. In this example I am going to use a different type of SQL object, a variable.

Wednesday, November 2, 2022

Checking my authority using a SQL Scalar Function

There are times I discover little things in IBM i that I wish that had been available before. An example in the new SQL enhancements that came as part of IBM i 7.5 and 7.4 TR6 release and refresh. It is a SQL Scalar Function that allows me to check the user's special authority, as a member group profile, or acquired by adopted authority.

I have written many programs in the past where the user needed a particular special authority, for example *SECADM, and if they do not have authority when they try to execute the command it errors. The snippet below, from a RPG program, is an example of this:

Thursday, October 27, 2022

Retrieving the CPU usage information via SQL

One of the many additions to the last release and Technology Refresh, IBM i 7.5 and 7.4 TR6, was a SQL View that returns one row of results of the usage of the CPU.

To most people it may not sound the most interesting information, but I have been asked if there was a way to get to this information so it could be written to an outfile, by a couple of people. Previously the only way I knew how to show the information was the Work with System Activity command, WRKSYSACT.

                           Work with System Activity                   DEV750
                                                             DD/DD/DD  TT:TT:TT
 Automatic refresh in seconds  . . . . . . . . . . . . . . . . . . .     5
 Job/Task CPU filter . . . . . . . . . . . . . . . . . . . . . . . .    .10
 Elapsed time . . . . . . :   00:00:02    Overall CPU util . . . . :    80.1
 Overall SQL CPU util . . . :    30.4
 Average CPU rate . . . . . :   101.2
 Current processing capacity:     1.00

Wednesday, October 26, 2022

SQL Views to help get information about Temporal Tables

When I ask people about their use of recent additions to the IBM i operating system my question about Temporal Tables are generally met by question "What are those?"

I am not going to explain what they are and how to use them in this post, as I wrote about them when they were introduced as part of IBM i 7.3 . You learn about them in the post I wrote when they introduced as part of 7.3, you can read about them here.

What I am going to explain in this post are two SQL Views that show you information about Temporal Tables:

Wednesday, October 19, 2022

Changing data within User Space with SQL

As part of the IBM i 7.5 and 7.4 TR6 release and refresh we have been given two new SQL Procedures that allow us to change data within an User Space. The first allows me to change the data within the User Space in character format, the other as binary.

Before I start showing these new Procedures I need a User Space. I can create this with SQL using the CREATE_USER_SPACE Procedure:

CALL QSYS2.CREATE_USER_SPACE('MYUSRSPC','MYLIB',131072,'YES',
                             '','*USE','YES')

Tuesday, October 18, 2022

Creating a list of commands and their command processing programs

The questions was is there a way to list the command processing programs for all of the commands in a library without having to look at each one individually with the Display Command command, DSPCMD. I decided to take this a small step further and want to have the name of the Validity Checking Program too.

Having looked at all of the lists I know of SQL Views, Table Functions, etc. I could not find one for commands. Alas, all of the CL commands only output to screen or spool file. It meant I had to use an API, QCDRCMDI. While writing this example I did have issues with QCDRCMDI. After over four hours the only way I found I can call the API it is call it from a separate program. More about that later.

As a result I have two programs:

Thursday, October 13, 2022

Fall 2022 Technology Refreshes announced

The one day on my vacation I was beyond the reach of the internet, IBM announced their latest Technology Refreshes for IBM i 7.5 and 7.4 . As 7.3 is nearing the end of support there will be no more TR for it.

What caught my eye in these TRs?

As usual the Db2 for i has produced many new things for us to use. The first thing that caught my eye is the new REMOTE_TABLE, will I be able to access table functions on remote partitions using the three-part name? I cannot wait to try this.

Wednesday, October 12, 2022

Using SQL to check for level check, reuse deleted, and file size

There are three things about data physical files I always like to keep track of:

  1. Level check status
  2. Will file reuse deleted records
  3. What is the maximum number of records the file can contain

For years I have used the Display File Description command, DSPFD, to create a lists of the files in a library, or libraries, and then read that file to check the fields for that information. As I can execute CL commands in ACS's Run SQL Scripts I can do the following:

01  CL:DSPFD FILE(MYLIB/*ALL) TYPE(*ATR) OUTPUT(*OUTFILE) 
               FILEATR(*PF) OUTFILE(QTEMP/DSPFD_ATR) ;

02  SELECT PHLIB,PHFILE,PHDTAT,PHLVLC,PHRCDC,PHRUSE
      FROM QTEMP/DSPFD_ATR ;

Wednesday, October 5, 2022

Do not SQL insert if there is already a row present with the same key

The need was understandable. Every time I made a change to a file I need to insert the file's library into another table. I only needed the library name in the table once. And I did not want my statement to error.

The table is every simple, just one column for the library name:

01  CREATE TABLE MYLIB.TESTTABLE
02  (LIBRARY CHAR(10),
03   PRIMARY KEY(LIBRARY)) ;

Inserting the first record is not a problem:

Tuesday, October 4, 2022

Create and change User Spaces' attributes with SQL

In IBM i 7.5 and 7.4 TR6 the Db2 for i team introduced two new SQL Procedures for creating and changing the attributes of User Spaces, rather than using APIs.

I am going to show examples of these below.

 

Create User space

Thursday, September 29, 2022

SQL equivalent of WRKSPLF, faster way of getting information about my spool files?

One of the first commands we all learn with IBM i is the Work with Spool Files command, WRKSPLF. With IBM i releases 7.5 and 7.4 TR6 comes a new SQL Table Function that is the equivalent of WRKSPLF.

The new Table Function, SPOOLED_FILE_INFO is found in library QSYS2, has parameters that match those of the WRKSPLF command. The syntax for the Table Function is:

SELECT * FROM TABLE(QSYS2.SPOOLED_FILE_INFO(
  USER_NAME => '*CURRENT',
  STARTING_TIMESTAMP => CURRENT_TIMESTAMP - 6 MONTHS,
  ENDING_TIMESTAMP => CURRENT_TIMESTAMP - 2 DAYS,
  STATUS => '*READY *HELD', 
  JOB_NAME => '*',
  OUTPUT_QUEUE => '*LIBL/MYOUTQ',
  USER_DATA => 'SQL',
  FORM_TYPE => '*STD',
  SYSTEM_NAME => 'DEV750'))

These parameters and their WRKSPLF equivalents are:

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: