Wednesday, November 30, 2022

Workload Group shortcut added to Active Job Info

One of the SQL table functions I use the most is the ACTIVE_JOB_INFO, which is used to list all of the active jobs in any partition.

In IBM i TR4 and 7.3 TR10 a new parameter, DETAILED_INFO, was added to the Table Function. This controlled the number of columns that were returned in the results. When it was introduced there were the following options

-- Default
SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) ;

-- All columns returned
SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) ;

-- QTEMP info returned only
SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'QTEMP')) ;

-- Minimum info
SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'NONE')) ;

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

Update April 17, 2023: New version is currently available for download here


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: