Wednesday, May 29, 2024

Getting the message for the SQL code quickly

All of us who use SQL have encountered errors, and when we do a SQL code is returned. This then leaves us trying to find the text associated with the SQL code to understand what had happened.

If this is in a RPG program I always recommend that you use GET DIAGNOTISTICS, which will return all you wanted to know about the message and a whole lot more too. If I am quickly writing a RPG program that will only be used one, or maybe twice, or a CL program and I get a SQL code I need to look it up to understand what happened.

There are two types of SQL codes:

  1. Those less than zero: Errors
  2. Those greater than zero: Warnings

All SQL codes have an equivalent IBM i message id. Basically it is 'SQL' followed by the SQL code. For example, SQL code -423 becomes message id SQL0423.

All message ids' information in IBM i are held in message files. SQL messages have their own message file QSQLMSG in the library QSYS.

If I wanted to look up the message for SQL code -423, message id SQL0423, I could use the Display Message Description command,DSPMSGD:

Monday, May 27, 2024

RPGPGM.COM-unity ribbons

Last week at COMMON Americas PowerUp2024 conference I handed some special ribbons to people. You can see who I did here.

What does this mean? You will have to click on the above link to learn.

Apologies to those people I gave ribbons too that I did not have time to photograph. If you are one of those people it is not too late. Take a photograph of yourself posing with your conference badge and RPGPGM.COM-unity ribbon, like the people in the photographs, and send it to me. I will add you to the page. My email address is RPGPGM.COM@GMAIL.COM

Wednesday, May 22, 2024

Extract number out of character column and use it to update another column

A friend told me about the old database he had to work with. There was one file with a field that contained two pieces of information. Any leading characters, A – Z, meant one thing and the numbers within it meant something else. Unfortunately there could be any number of leading characters, the numbers could be three of four long, and there could be characters at the end. The examples he shared with me were:

  • 'A123B'
  • 'ABC123'
  • 'AB4679'
  • 'BC0123D'

His plan was to add a new numeric field to the file, and update it with the numbers extracted from this original field.

"What would be a simple way to do this with SQL?" he asked.

Wednesday, May 15, 2024

Retrieving file's keys with SQL, having to use DSPFD

In my last post I wrote about how to list a file's key fields using the file QADBKATR to retrieve the key fields' information from.

I mentioned that there may be times that you are not authorized to use the QADBKATR file. If you are not authorized how else can you get the key fields? And present them as I did in the previous example?

I have to fall back and use the Display File Description command, DSPFD, using the Access Path value in the "Type of information" parameter:

DSPFD FILE(MYLIB/TESTFILE) TYPE(*ACCPTH)

Thursday, May 9, 2024

New IBM Power 10 model for AI

On Tuesday, May 7 2024, IBM announced a new model IBM Power 10, S1012, which caught my eye. It is smaller than the other IBM Power rack mounted servers, and it also comes in a cool looking tower version too.

The following statements in the announcement caught my eye:

  • "This 1-socket, half-wide Power10 processor-based system delivers up to 3X more performance per core versus Power S812"
     
  • "delivers the lowest entry price point in the Power port[f]olio to run core workloads for small and medium-sized organizations"
     
  • "provides clients the flexibility to run AI inferencing workloads in remote office and back office (ROBO) locations outside mainstream datacenter facilities"
     

Wednesday, May 8, 2024

Retrieving file's keys with SQL

I was asked how I could present the following information in an "easy to use" manner for all of the "files" in a library:

  • Library name
  • File name, or Table, Index, etc. name
  • Type of file
  • When it was last used
  • File's key fields

The easiest way to get this information is using SQL. The first three pieces of information can be retrieved from the SYSFILES View. The last used information can come from the OBJECT_STATISTICS Table function. Getting the file's key fields is not in any of the usual SQL Views about fields/columns or files/tables.

There is a file that contains the keys for all files: QADBKATR. This is a logical file built over the file QADBKFLD. On all the IBM i partitions I use for testing this posts I found that I am not authorized to the physical file on any, and I am authorized to the logical file on all but one. Therefore, my examples will use QADBKATR.

Tuesday, May 7, 2024

Spring 2024 TRs announced

Today, at a later time than previous announcements, a new set of Technology Refreshes were announced by IBM for the currently supported releases of IBM i. These are IBM i TR4 and IBM i 7.4 TR10.

Doing a quick count of what is new and enhanced I find the following:

  • RPG
    • 2 enhancements
  • Db2 for i (SQL)
    • 17 additions
    • 9 enhancements

You can read all about them on these pages on IBM's websites:

Thursday, May 2, 2024

Converting fetched columns into an array

Coming up with a meaningful title for this post proved harder than normal. I was asked a question about how to fetch data, using a SQL Cursor, and convert some of the fetched columns into an array. The questioner had tried various approaches, but was always receiving an error in the SQL pre-compile step of his program.

The file in question contained 35 fields he was interested in. The record would be for an account, by year and month, 31 balance fields one for every day of the month (yes, I know not all months have 31 days), and finally a total monthly change balance. The questioner wanted to put all of the 31 day fields into one array.

Let me start with my version of his file, that I called TESTFILE:

Wednesday, May 1, 2024

May's presentations

I feel honored to have been asked by Jon Paris and Sunsan Gantner to give two presentations as part of the System i Developer Lunch and Learn series. I will be presenting on Wednesday May 8, about converting spool files to PDF, and on Wednesday May 15, about what is new in CL.

The Lunch and Learn presentations are all online and free to everyone.

There are many interesting speakers covering a good range of subjects. To learn more go here and choose which of the Learn and Learn Series you want to register for.


On Monday May 13 the Central Texas IBM i User Group, CTXiUG, welcomes IBM Champion Marina Schwenk to their online meeting as the guest speaker. She will be talking about using service programs and unit testing.

This meetings are online and free to everyone.

Learn more and register here.


This month is also the COMMON PowerUp in-person conference in Fort Worth, Texas, Monday May 20 - Thursday May 23. I will be giving six presentations this year.

As this is an in-person conference you need to register for the conference here.

Learn more about my presentations on my speaker page here.

If you see me there please introduce yourself and say "Hi".


If you are a member of an IBM i Local User Group, LUG, and you would like me to talk to your group, use the contact form to reach me. We can then arrange a date and subject.

If you are a member of a LUG that is not listed on my LUG page, please contact me and send me your group's details.

Wednesday, April 24, 2024

More about getting information about SQL Views

Several years ago I wrote a post about the Db2 for i (SQL) Views about Views:

  • SYSVIEWS:  Information about Views themselves
  • SYSVIEWDEP:  Information about Views' dependencies

One of the things I talk about during presentations I give to Local User Groups, LUG, is the use of SQL Views and how they make my programming simpler. Therefore, I wanted to revisit these two Views and explain how they help me learn more about the Views in the partitions I use.

Before I can show what kinds of information I can get from SYSVIEWS and SYSVIEWSDEP I am going to need some Views to play with.

I am going to join a DDS file, TESTFILE, and a DDL table, TESTTABLE to create my first View. While it is not essential to know what the file and table look like I am just going to include their source code below.

First TESTFILE: