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.