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:

Wednesday, April 17, 2024

Generate spreadsheet with SQL in batch job

When I wrote about the Db2 for i, or SQL, scalar function to create a Microsoft Excel spreadsheet in the IFS my example program ran interactively. I recently received a message asking the following: "good stuff even though I started to use it, it has some limitations so far: doesn't work with QTEMP files. Am I right?"

I submitted my example program to batch and found that the expected spreadsheet was not generated. Looking in the job's job log I could see several messages related to files I created in QTEMP not being found. It would appear that the Java called by the GENERATE_SPREADSHEET submits a job to batch with the type BATCHI.

If I should not use files in QTEMP then I need a Standard Output file, STDOUT, in another library. I chose to create it in QGPL, but it could be in any library that is in the users' library list. As STDOUT is a source file I create it with the following:

  CRTSRCPF FILE(QGPL/STDOUT) RCDLEN(266) +
             TEXT('Standard output file')

Monday, April 15, 2024

End to free format directive

What I call totally free RPG and what IBM calls modern RPG has been around for nine years. This type of RPG always needs the **FREE directive starting in the first position of the source line. And what follows is the free form RPG, where the source line can start in any position.

     ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
01  **free
02  dcl-s Statement1 varchar(100) inz('Entered in free format RPG') ;

Marc Raynor informed me of another directive that "ends" the free format RPG, **END-FREE. After that directive all valid RPG must be entered in the sixth position for fixed format code, and later for the not completely free format code.

Having been told this I had to try it: