Wednesday, October 28, 2020

Getting the day of the week in a CL program using SQL

getting day of week in a cl program

The germ for this post came from a Facebook post I saw. The question was posed: Is it possible to retrieve the day of the week in a CL program? Multiple people suggested using a CEE API. Then came a reply that really interested me. This person said he had built a SQL View for this purpose.

It struck me as a creative approach to this scenario. SQL has a global variable CURRENT_DATE which contains, as the name suggests, the current date:

SELECT CURRENT_DATE
  FROM SYSIBM.SYSDUMMY1

00001
----------
2020-10-28

What kind of information would I want to know about today's date?

Wednesday, October 21, 2020

Using SQL to extract packed decimal from a flat file

sql interpret to extract packed decimal from strings

I have a two "flat" files that contain packed numeric fields. The first is a "header" that contains the file date, the number of records and the total of the amounts in the "detail" file. The "detail" file contains account information that I don't really care about. I need to validate the count from the "header" file to the data in the "detail" file.

When I look at the file using the Display Physical File Member command, DSPPFM, I can see the packed fields. They are where the funny characters are:

DSPPFM FLATHDR

*...+....1....+....2....+....3
2020-10-01??????Ãí_

DSPPFM FLATDTL

*...+....1....+....2....+....3....+....4....+....5
First     ??One  ?????¬*Testing
Second    ??Two  ???Ì°?*Testing

Wednesday, October 14, 2020

Which libraries did Jane create?

get the libraries owned by a specific user profile

I was asked how can I identify the libraries a person, in this example we will call her Jane, created? The person who asked the question was a self-confessed “newbie operator" who had been given some information by his employer's programmers, but doubted it was the most efficient way to get the data.

If I need to find out who created one library I would use the Display Object Description command, DSPOBJD.

DSPOBJD OBJ(MYLIB) OBJTYPE(*LIB) DETAIL(*FULL)

The following screen would be shown, and I would just look at the “Created by user" field:

Wednesday, October 7, 2020

Using multiple SQL Sequences to update different record types

sql sequence to update file with unique sequence number for each record

There is a file, ACCTFILE, with a unique key of two fields:

  1. SEQUENCE:  A unique sequence number
  2. ACCTYPE:  Account type code
01 A                                      UNIQUE
02 A          R ACCTFILER
03 A            SEQUENCE       6P 0
04 A            ACCTYPE        2A
05 A          K SEQUENCE
06 A          K ACCTYPE

The Sequence is incremented every time a record is written to the file, i.e. there is only one record with each Sequence number. The Sequence is now in the 800,000s, and the concern is what will happen when happen when 999,999 + 1 happens?

This is when they asked me for advice.

Tuesday, October 6, 2020

7.4 TR3 and 7.3 TR9 announced

new technology refreshes 7.4 tr3 and 7.3 tr9 announced

The autumnal announcements have been made for new Technology Refreshes for the currently supported versions of the IBM i operating system:

  • IBM i 7.4 TR3
  • IBM i 7.3 TR9

The TRs include the same updates for Db2 for i (SQL), RPG, and open source.

Saturday, October 3, 2020

New functionality added to this site

Have you noticed that there is a new option on the menu at the top of this page?

I have added a new page that contains a way to search this blog using a Google custom search engine.

Click on the link at the top of any page in this web site, and go search for the information you are looking for.

Happy searching!

Wednesday, September 30, 2020

Create or replace for SQL Tables

create or replace table on replace values

In previous posts I have given examples of using the CREATE OR REPLACE for all kinds of SQL objects: Views, Indexes, Functions, Triggers, Sequences, etc. One situation I have not mentioned is using this option when creating a Table. I know it sounds a bit scary to replace an existing Table that contains data. If I am going to do it I want to have the option to preserve the data or to delete it.

Creating a new table is as easy as:

01  CREATE TABLE MYLIB.TESTING_TABLE
02  FOR SYSTEM NAME "TESTTABLE"
03  (
04    FIRST_COLUMN FOR "FIRST" VARCHAR(10) NOT NULL,
05    IDENTITY_COLUMN FOR "ID_COL" BIGINT
06      GENERATED ALWAYS AS IDENTITY
07  ) ;

08  INSERT INTO MYLIB.TESTTABLE (FIRST_COLUMN)
09  VALUES('FIRST'),('SECOND'),('THIRD') ;

Wednesday, September 23, 2020

Retrieving commands' defaults, CLOB-ing it

using clob with qcdrcmdd api

Last week I wrote about how to use an API to retrieve a command's defaults. I encountered a problem when the string returned by the API would be blank. I did not know what was causing this. Fortunately two people added comments to the post explaining the issue: if the string returned is longer than the size of the result variable blank is returned. I want to thank both of them for letting me know what was causing this problem.

Therefore, if I increased the size of the subfield in the API's result data structure I see the results for those commands I did not see before.

This caused another problem, as I could no longer use the field in the DDS file I had defined before. The maximum size of an alphanumeric field is 32,766 bytes, which proved too small for some of the commands I tested with. My first thought was to use a (SQL) DDL table instead, but the maximum size of a character column is one byte smaller than for the DDS alphanumeric field, 32,765. As these XML strings can be extremely long I will need to insert these into a CLOB, Character Large Object, column as this can contain up to 2,147,483,647 bytes, way more than I could ever need.

My SQL DDL table looks like:

Wednesday, September 16, 2020

Retrieving commands' defaults

qcdrcmdd api to get command definition

I wanted to retrieve the defaults for a number of IBM commands, so I could compare them to those in another IBM i partition. I found an API, QCDRCMDD, would give me a XML string of the command's definition, that I could then write to a file. This file could then be transferred to the other partition and used when comparing the two sets of commands' information.

The output file, OUTFILE, would contain one field, for the XML string, but would need to big enough to contain the longest possible XML string. My guess is that a field of 10,000 characters would be long enough to cover most commands.

01  A          R OUTFILER
02  A            FIELD      10000A

Wednesday, September 9, 2020

Using Navigator for i for SQL index suggestions

using sql index advisor to create indexes

I have been playing with the new release of Access Client Solutions, ACS 1.1.8.5, and have been impressed with it. I have found Navigator for i a bit faster so thought it was time to revisit one of the coolest features included in it, the Db2 for i index advisor.

I have written about using the index advisor using the old Client Access Operation Navigator before. The ACS version of the index advisor is so much easier to use I wanted to encourage you to look at it.

When you access a table or file using SQL the Db2 engine determines the best access path to use to extract the data requested. If there is not a suitable existing access path it will build a temporary one. When it does, it makes an addition to the index advisor table with the suggested key, or if there is already a row it increments the "times used" counter. I can use the index advisor to see which indexes are suggested. If there is a suggested index that has been used many, many times then I will build it to make the SQL faster, as it no longer has to build the temporary access path.

Wednesday, September 2, 2020

Prestart jobs' information using SQL

sql to view prestart jobs

What are prestart jobs? They are batch jobs that run to allow programs on remote systems to communicate with the IBM i partition. They use prestart job entries that are found in the subsystem descriptions of the subsystems in which they run.

Previously the only way you could list the information about prestart jobs was to use the Display Subsystem Description command, DSPSBSD, one subsystem at a time, or the Display Active Prestart Job command, DSPACTPJ, one job at a time. The latest Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, introduced the following to Db2:

  • PRESTART_JOB_INFO View
  • PRESTART_JOB_STATISTICS Table function

Both of these are found in the QSYS2 library.

Thursday, August 27, 2020

New Db2 for i poster

Scott Forstie, Db2 for i Business Architect, has updated his Db2 for i poster to include everything from the latest Technology Refreshes, and made it available for download at ibm.biz/Db2foriPoster.

Thank you Scott for doing this.

In the future if that link stops working, as ones for previous posters have, you can download it from here.

Wednesday, August 26, 2020

Difference in Run SQL Scripts

acs run sql scripts content assist

I have had the latest version of Access Client Solutions, ACS, 1.1.8.5 for just over a week. I use "Run SQL Scripts" so much I leave it open all day. It is my favorite part of ACS, as I often start with a simple SQL statement, and then copy that to make it ever more complex until I have the final form of the statement I want to use. Something that is hard to do using the STRSQL interface.

There are several ways to start "Run SQL Scripts". The most common way is from the 5250 emulation display. I will find the option to start it in the "Actions" menu.


Tuesday, August 25, 2020

Wednesday, August 19, 2020

Using multiple data structures to contain the results of a Fetch

single row fetch into multiple data structures in rpg

The germ for this post came from a question from Bob. It is not the question that I wanted to share or the answer I came up with, but the solution he came up with. Bob was presented with an old program with a SQL Cursor definition statement that joined several files together. Within the Cursor definition the original programmer had listed all of the fields in the files, and in the Fetch statement he/she had listed all the fields from the files again.

The question he asked me was this possible to convert from a Cursor Fetching one row at a time to a multiple row Fetch.

In the following examples I am not going to use the files Bob used, but these test files:

Tuesday, August 18, 2020

IBM video: The Legacy and Future of IBM POWER with IBM POWER10

IBM reveals the next generation of IBM POWER10 processor

IBM press release here.

Interview with IBM Cognitive Systems General Manager Stephen Leonard here.

Monday, August 17, 2020

Time to give RPG a new name

help make rpg for i a reality

I was talking to some friends about totally free RPG and I soon got tired having to save "totally free RPG" all the time. Then it struck me this language needs a new name!

Do not worry I am proposing we drop the name "RPG", but the time is right to give it something to show that this is a new version of the programming language. When the AS/400 was launched, 32 years ago, RPGIII from the System/38 was renamed RPG/400. 25 years ago ILE/RPG was brought forth giving us a new paradigm to programming.

Five years ago "totally free RPG" was introduced. It is not ILE/RPG. There are no columns and I can start typing in the first column of the source member, and can continue the way to the last column too. It was something new and different from its antecedents. This is a new version of the programming language and, in my opinion, it deserves a new name.

Friday, August 14, 2020

New version of ACS: 1.1.8.5

acs version 1.1.8.5 download

A new version of IBM's Access Client Solutions, ACS, has been released. ACS includes:

  • 5250 emulator
  • Printer emulator
  • Navigator for i
  • Run SQL scripts
  • File transfers
  • And a lot more

And it is free to download from IBM's website.

If you are using a free IBM i service, like RZKH's PUB400.com, then this is what you should use as it gives you a lot more than the other 5250 emulators.

Wednesday, August 12, 2020

Using table function rather than view for object privilege information

object

One of the new arrivals with the latest rounds of Technology Refreshes was a table function to retrieve the authority for a particular object. "Wait!" I hear you regular readers say, "Wasn't there already a SQL View added to show that information?" And you are correct in IBM i 7.3 TR2 the OBJECT_PRIVILEGES View was released.

With a Table function I pass it a set of parameters and only the results for those values are returned. If I use a View then the entire View has to be searched for the rows matching the selection criteria. By using a Table function, rather than a View, I can get the information I want quicker. I will demonstrate this in the examples below.

The OBJECT_PRIVILEGES Table function and View have the same columns. The only annoying difference is the name of the authorized user profile in the View is AUTHORIZATION_NAME, while in the Table function it is AUTHORIZATION_USER.

In these example I am only interested in determining if user profile is authorized to use a file, TESTFILE. The profiles authority is held in the column OBJECT_AUTHORITY. Therefore, if I want to see who is authorized to use the file TESTFILE in the library MYLIB I would need to pass the Table function the following:

Tuesday, August 11, 2020

Removing deleted records faster than RGZPFM

cpyf quicker than rgzpfm

The subject of this post is not new, but I thought I would share this as this is the quickest way I know to get rid of millions of deleted records taking up space in your files. The last time I used this method was with a file that contained 1 million "active" and 11 million deleted records. The application owner of this file had a fixed amount of time to remove the deleted records in their weekly maintenance "window". Having performed tests using RGZPFM she found that it took longer than the allowed, and came to me for ideas.

RGZPFM FILE(BIGFILE)

The part of this process that many people forget is all the access paths are reorganized too. In this case there were a plethora of logical files built over this file, I forget exactly how many but too many for my liking.

What was my suggested alternative?

Thursday, August 6, 2020

Viewing IFS object's authority using SQL

using sql to view authority of files and folders in ifs

Included with the latest Technology Refreshes is a new table function which allows me to view objects' privileges of the folders and files in the IFS. Object privileges/authorities are something that I am asked for during audits, therefore, I was really interested to learn what information I can get from it.

The only gotcha is as a table function IFS_OBJECT_PRIVILEGES returns the results for one set of objects, rather give me the ability to get information for all objects as I would using a view.

As I have done in many of my previous examples I am not going to show you all the columns returned by this table function, just the ones I find interesting. There is a link at the bottom of this post to IBM's documentation for this table function which includes a full list and description for all of the columns.

The columns I am going to work with are:

Wednesday, August 5, 2020

SELECT with EXCEPT clause to find differences between records in two files

using except clause to join 2 select statements together to see differences

I saw this mentioned in a Facebook discussion where someone said he was using what I will describe below to determine the difference between the records in two files. I had not heard of this method so I wanted to try it out for myself and determine whether it is something I could use in the future. Two SQL Select statements joined with an EXCEPT clause. But before I show examples of that I need data.

I am going to:

  • Create a file
  • Add data to it
  • "Clone" the file to make an exact copying, including the data
  • Change the data in the "clone"
  • Compare the two files

In these examples I am just going to use a file with one field. The same principles work with one field as it would with a hundred fields. I decided to K.I.S.S. and just have one field in my file. The layout for the file is:

Thursday, July 30, 2020

Viewing HTTP server information using SQL

view data about apache http server on ibm i

I want to give credit to IBM's Db2 for i team for continually adding SQL views, table functions, etc. to information that was not easy to get using APIs or other tools. Another example of their excellent work, introduced with the latest Technology Refreshes, is the view HTTP_SERVER_INFO. Previously this information was only available using the Web Administration for i GUI.

This view, which is located in the library QSYS2, displays information about the Apache server in the HTTP server for IBM i, and will only display data about the enabled and active functions.

As usual with my examples of Db2 for i views and table functions I am not going to list all of the available columns, just the ones I found interesting:

Wednesday, July 29, 2020

Removing apostrophe in a field using SQL

replace 1 character with another using rpg and sql

There are occasions when interfacing data to non-IBM i database where an apostrophe ( ' ) in a field can cause a conversion error. In this post I am going to give some simple examples of how apostrophes can be converted to something else using SQL and RPG.

Let me start with the DDS file I will be using in these examples. The file is called TESTFILE and contains one field:

A          R TESTFILER
A            COMPNAME      35A

The field contains the following three records:

Saturday, July 25, 2020

It's been IBM i for longer than AS/400

In a post on LinkedIn yesterday Steve Will, chief architect of the IBM i, shared an interesting milestone in the life of the this operating system.

This operating system had been known as IBM i for a long as it was known as AS/400.

Wednesday, July 22, 2020

Subtracting days from a "date" when it is a number in a file

substract 35 days from a date when it is a packed numeric field

The germ for this post came from a question:

How can I subtract 35 days from a date in my files using SQL? The date is a packed number

Most of us who deal with older databases find that the "dates" in the files are not true dates fields, they are numbers masquerading as dates. I have, in a previous post, shown how to change a numeric representation of a date to a date with SQL, this is taking it a step further by converting the date back to a number and updating the file with the new value.

In these examples I will be using the numeric representation of the date in two formats:

Tuesday, July 21, 2020

How to view pending database commits using SQL

finding uncommited transactions using DB_TRANSACTION_INFO view

Another addition to Db2 for i in the latest Technology Refresh, IBM i TR2 and 7.3 TR8, was a view called DB_TRANSACTION_INFO. This view lists what the IBM documentation describes as: "returns one row for each commitment definition".

What does that mean? Having played with this View for a while I have found that it just lists all uncommitted database transactions to a file or table that is using commitment control. Database operations to files and tables that do not use commitment control do not appear in this View.

There are far more columns in this View than I am going to use in my examples, so I am going to say to you if you want to know what all of the columns are click on the link to IBM's documentation at the bottom of this post.

Thursday, July 16, 2020

Viewing all of the subsystems autostart jobs in one place

view subsystem autostart jobs using sql

If I needed to see which autostart jobs were in my IBM i partitions I would either have to use the Display Subsystem Description command, DSPSBSD, to view the autostart jobs for each subsystem or use an API. Fortunately the latest Technology Refreshes include a Db2 for i View that gives me the information I want.

Before I give examples of using the new View I want to show how to get to the same information using the DSPSBSD command so that you can compare the data from the two.

If I want to see what autostart jobs there are in the QCTL subsystem I would use the following command:

DSPSBSD SBSD(QCTL)

When the Enter key is pressed the following menu is displayed:

Wednesday, July 15, 2020

Listing SQL Indexes' keys

using sysindexes and syskeys to make a list of index keys

Most of the time I do not care what the keys are for any SQL Indexes I have built over DDL tables or DDS tables. When I want to extract data from a table or file using SQL I build the statement using the table or physical file, and Db2 of i is smart enough to find the best access paths (logical file or SQL Index) for the statement.

I recently encountered a company where their programmers had been building Indexes. Building Indexes is not a problem, but each programmer had been building their own Indexes without consulting with the other members of the team. The team leader was concerned that they now had many indexes that had the same keys. He asked me if I could help him identify the following:

  1. Indexes built over which table or file
  2. The keys of those Indexes

Wednesday, July 8, 2020

Make second line appear and disappear on a subfile

subfile that expands to a 2nd line and contracts

I was asked how to do this by someone who said that no-one in his department knew how to: have a second record appear in a subfile when a function key is pressed, and disappear when the function key was pressed again.

Fortunately this is a simple thing to do with just three keywords in the display file's DDS:

  • SFLDROP
  • SFLFOLD
  • SFLMODE

And there is not much we have to do in the RPG program that uses the subfile.

I have deliberately stripped down the display file and RPG code to what I consider a minimum to, in my opinion, makes it easier to understand.

Let me jump right in and show you the display file I will be using in this example:

Tuesday, July 7, 2020

Selects with Intersect to find common records in two files

sql selects with intersect to find common data

Having written about Selects with Union I thought it was time to mention to Selects with Intersect.

Select statements with the INTERSECT combines the matching results from two tables into one set of results. To reiterate "matching" means that the same value must appear in the given column(s) in both files.

I have two example tables with the same column names, they contain the following data:

Table 1 Table 2
ANGELA
BETTY
DOROTHY
EVELYN
GEORGINA
GEORGINA
FRANCESCA
EVA
DOROTHY
CAROLINE
ANGELA

Wednesday, July 1, 2020

Writing to the same spool file from multiple programs

output from 2 programs to same spool file

The question sounded simple: How can I write to the same spool file from two programs?

I have written before about copying from more than one spool file to create a new one. But this time I will be writing to the same spool file directly from two programs.

Let me start with the DDS for the printer file:


01 A          R HEADER
02 A                                     1'PAGE HEADER'
03 A                                      SKIPB(001)
    *****************************************************************
04 A          R FORMAT01
05 A                                     1'PROGRAM 1'
06 A                                      SPACEB(001)
07 A            COUNTER        3  0     +2EDTCDE(Z)
    *****************************************************************
08 A          R FORMAT02
09 A                                     1'PROGRAM 2'
10 A                                      SPACEB(001)
11 A            COUNTER   R             +2REFFLD(COUNTER *SRC)
12 A                                      EDTCDE(Z)
    *****************************************************************
13 A          R ENDREPORT                                            
14 A                                     1'* * * END OF REPORT * * *'
15 A                                      SPACEB(001)

Tuesday, June 30, 2020

Subsystem status using SQL

check status of subsystem using new sql view

Last year I wrote about a way to check if a subsystem was active or not. The statement combined two table functions and could only check if a subsystem was active, if it was not returned in the results it was assumed to be inactive. Included in the latest round of Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, is a new SQL View all about subsystems, now I can get the same information just from one View, including the status of the subsystem.

The information I am interested from the View SUBSYSTEM_INFO is:

  • Subsystem name and the library it resides in
  • Status: active, inactive, etc
  • Number of jobs currently running in the subsystem
  • Maximum number of jobs that could run in the subsystem
  • Job that is monitoring the subsystem

Thursday, June 25, 2020

Searching for reserved characters in SQL

search strings for sql special characters

SQL like all other programming languages has a number of characters that are reserved for certain uses. What happens if you need to search for one of those characters in a table or file? In this post I am going to give you two ways you can do this.

To show how this works I have a test file with five records in it:

FIELD1
----------
HERE'S IT
100% REAL
APPLE
BEAR
CAT

What I want to do is…

  1. Find the record with the apostrophe ( ' ) in it
  2. Find the record with the percent sign ( % ) in it

Wednesday, June 24, 2020

Using SQL to validate packed fields

validate packed number fields with sql

When I heard of these three table functions being introduced as part of the latest round of Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, I was really interested to know what they did as their names, "Validate data", really piqued my interested. These table functions will validate packed numeric fields at, what I like to call, three levels:

  1. VALIDATE_DATA:  validates at the member level
  2. VALIDATE_DATA_FILE:  validates at the file level
  3. VALIDATE_DATA_LIBRARY:  validates at the library level

These three exist in the SYSTOOLS library.

It is nigh impossible to put bad data into date, time, and timestamp fields in files. But it is not too hard to put bad data into a packed numeric field. While rare, I am seeing this more as data is taken from other types of databases and applications, and loaded into files in IBM i.

Wednesday, June 17, 2020

Control progression of cursor in display file

control cursor movement on dspf

The question was how to control the way the cursor moves from field to field on a display. The display had two columns of fields and the questioner wanted the cursor to progress down the first column's fields before moving to the top of the second column.

My example display file contains two records format. The first I will be using to demonstrate the default cursor progression, and the second how I can move down the first column's fields before moving over to the second column.

The DDS code for the first record format looks like:

A          R SCREEN0
A            Z001           3   B  2  3
A            Z002           3   B  2 10
A            Z003           3   B  3  3
A            Z004           3   B  3 10
A            Z005           3   B  4  3
A            Z006           3   B  4 10

Tuesday, June 16, 2020

Deleting spool files using SQL

deleting old spool files using sql procedure

Many of the IBM i partitions I have worked upon have had an issue of old spool files occupying valuable storage space. Since IBM i 7.2 there has been a way for me to analyze the spool files, using SQL, and make decisions on which would be deleted. The process of doing the deleting had to be entrusted to a program containing CL commands.

The latest round of Technology Refreshes, 7.4 TR2 and 7.3 TR8, gave me a way I can delete old spool files using a SQL procedure, DELETE_OLD_SPOOLED_FILES

Warning:  Use this SQL procedure carefully. If you do not you run the risk of deleting all spool files from your partition.

DELETE_OLD_SPOOLED_FILES has five parameters:

Wednesday, June 10, 2020

Happy 7th birthday RPGPGM.COM

Another year has passed and this blog has reached its seventh anniversary. We are living in interesting times with IBM i, and over the years I have been fortunate to write about many things, that in my opinion, have made significant improvements to programming with this operating system. As well as a whole lot of cool additions to Db2 for i (SQL). I have learn a lot from what I have written about, and I am sure that I write better code than I use to.

The past year has been an exciting time, not only for this blog but for me too. I would include the following as my personal high points, in no particular order:

Tuesday, June 9, 2020

Making a hash of records for comparisons

sql hash_row to allow comparison of records in files

Another addition to Db2 for i as part of the latest Technology Refreshes for IBM i 7.4 TR2 only, is the ability to create a hash value for a record from a file using the HASH_ROW SQL built in function. I am not going to describe what hash values are, if you are interested there is a Wikipedia article describing it. I am just going to say that hash-ing a string produces hexadecimal value of the data.

Previously I had been able to hash individual fields, or columns, using the various standards of hash. As HASH_ROW uses SHA512 I can do the same for a field using HASH_SHA512:

01  SELECT HASH_SHA512('A') AS "Hash",
02         LENGTH(TRIM(HASH_SHA512('A'))) AS "Length"
03    FROM SYSIBM.SYSDUMMY1
04  UNION
05  SELECT HASH_SHA512('ABCDEFGHIJKLMNOPQRSTUVWXYZ'),
06         LENGTH(TRIM(HASH_SHA512('ABCDEFGHIJKLMNOPQRSTUVWXYZ')))
07    FROM SYSIBM.SYSDUMMY1

Wednesday, June 3, 2020

Using SQL to compare files for differences

sql table function compare_files to compare layout and data

Having looked at, and played, with a number of the new additions to Db2 for i in the latest new Technology Refresh this is my favorite and in my opinion the most useful: table function COMPARE_FILE.

COMPARE_FILE gives me the ability to identify differences between physical files, source files, and DDL (SQL) tables. I can compare two files layout and the data within them with this table function, and have a results row returned for each difference. Wow, both of those with the same table function!

Alas, it is only available in the Technology Refresh for 7.4 only, IBM i 7.4 TR2. It is not available in IBM i 7.3 TR8.

COMPARE_FILES has a number of parameters:

Tuesday, June 2, 2020

New SQL table function to display a job's locks

retrieving job lock information using sql

Before the latest round of new Technology Refreshes, 7.4 TR2 and 7.3 TR8, the only ways I knew to get Job Lock information was either by using an API or the Display Job command, DSPJOB. These new TRs introduced a new table function, JOB_LOCK_INFO, that allows me to see the same information with just a simple SQL statement.

JOB_LOCK_INFO has three parameters:

  1. Job name: This is the only required parameter. This can either be the full job name, or for information about the current job an asterisk ( * ) can be used
  2. Internal objects only: Optional. NO, which is the default, shows only external objects. YES shows internal objects, internal space objects, and external objects
  3. Ignore error: Optional. NO when an error is encountered it is returned. YES, which is the default, only a warning is returned

Thursday, May 28, 2020

Define a new Data Structure from one that is nested

using nested data structures in likeds

In the past I have written about nesting data structures, a way to contain data structure(s) within another data structure. And there are times I have wanted to define a new data structure to be identical to another using the LIKEDS keyword.

01  dcl-ds One qualified ;
02    OneSF1 char(1) ;
03    dcl-ds Two ;
04      TwoSF char(5) ;
05    end-ds ;
06    OneSF2 char(1) ;
07  end-ds ;

08  dcl-ds Test1DS likeds(One) ;

Wednesday, May 27, 2020

Improvements to RPG's timestamps

improvements to rpg timestamp

Ever since timestamps were introduction to RPG I have always felt aggrieved that they never contained the entire timestamp. Timestamps have six decimal places following the seconds and only the first three positions, milliseconds, were filled. The last three positions were always zero. Occasionally I have found in timestamp fields in two records with same value. I would unable to determine which record is older, without using other fields or the file's relative record number.

The latest round of new Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, brought improvements to the way RPG does timestamps.

Some of what I am going to describe here may have been available in earlier releases or TRs.

Tuesday, May 26, 2020

Key data structure big improvement

change to RPG %kds

I do not use the Keyed data structure, but I can appreciate this change that occurred as part of the latest new Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, was significant.

Before the latest TRs the second parameter of the %KDS built function had to be a number to denote how many of the key fields were to be used. The change in the new TRs now allows for that second parameter to also allow a variable.

The old way would look something like this:

01    if (NbrKeyFlds = 1) ;
02      chain %kds(FileKeys:1) TESTFILER ;
03    elseif (NbrKeyFlds = 2) ;
04      chain %kds(FileKeys:2) TESTFILER ;
06    endif ;

Wednesday, May 20, 2020

Retrieving information about a library using SQL

new table function library_info to get library data using sql

As part of the new Technology Refreshes, 7.4 TR2 and 7.3 TR8, came a table function to return information about a single library. I have to admit that I don't really care about some of the information returned in the results, but there are three columns I am going to find very useful.

The new table function LIBRARY_INFO is found in the library QSYS2. It combines data elements from the RTVLIBD and DSPOBJD commands. When calling this table function there are two parameters, one mandatory and the other is optional:

  1. Library name:  No surprise that this is the mandatory parameter
  2. Ignore errors?  What to do when an error is encountered. This is the optional parameter. Valid values are: YES and NO

In these examples I am not going to use the second parameter.

Right, let's jump right in and see what information we can retrieve about one of my libraries:

Friday, May 15, 2020

Today is 7.4 TR2 and 7.3 TR8 day

today 7.4 tr2 and 7.3 tr8 ptf are available

Today is the day the PTFs for the latest Technology Refreshes are available for download, IBM i 7.4 TR2 and 7.3 TR8.

The relevant PTFs are:

You can find links to what are in these Technology Refreshes here.

I am now going to generate a support ticket asking RZKH to load these TRs on their partitions. When they have I can start sharing how to use these enhancements.

Wednesday, May 13, 2020

Discovering the database's limits

using sql to see the maximum sizes of the ibm i database

Every so often I stumble across something in Db2 for i that I may not have a practical use for, but I find interesting. The SQL table SQL_SIZING is a good example of this. It contains one row for each limit of the IBM i Db2 database manager.

The table contains just four columns:

  1. SIZING_ID:  The ANSI, and ISO, number that defines the database sizing id
  2. SIZING_NAME:  The ANSI, and ISO, database sizing id name
  3. SUPPORTED_VALUE:  Sizing limit (value). If null the sizing limit is not applicable for IBM i
  4. COMMENTS:  Even though the sizing name is big enough to contain an adequate description of the sizing id this column gives a more detailed description

Wednesday, May 6, 2020

Adding RPG compiler parameters into the SQL Option statement

sql option compileopt in sql rpgle program

This feature was brought to my attention by someone who was converting a fixed format SQLRPGLE program to modern all free RPG code, and wondered the purpose of this code snippet.

01  C/EXEC SQL
02  C+  SET OPTION COMMIT = *NONE,
03  C+             COMPILEOPT = 'DBGVIEW(*ALL)'
04  C/END-EXEC

She understood the purpose of line 2. It was line 3 she had not seen before. Neither had I, and was intrigued as to what its purpose was and how else could it be used.

Tuesday, May 5, 2020

IBM performance FAQ update

The latest updated version of IBM's "IBM i on Power – Performance FAQ" was published at the beginning of this month, May 1.

While it includes the usual system information, in the latter half it contains advice for:

  • Db2 for i
  • RPG
  • Cobol
  • C
  • Java

To view the guide, which is a PDF, click here.