Wednesday, November 13, 2019

Listing Server Authority users

list users with server authority created using addsvraute

When I wrote about securing DDM files I bemoaned the lack of a WRKSVRAUTE command. While ago I stumbled across a Db2 for i View that will display a list of all the users I with DDM server authority.

This view has one of the longest names I have used to date: DRDA_AUTHENTICATION_ENTRY_INFO, and it contains the following four columns:

Long column name Short column name Description
AUTORIZATION_NAME USER_NAME User profile
SERVER_NAME SRVR_NAME Target server for the authentication entry
SERVER_AUTHORIZATION_NAME SRVR_USER User profile on the target server
PASSWORD_STORED PW_STORED Is the password stored in the authentication entry?

Wednesday, November 6, 2019

Example of why using SQL can be better than RPG

using sql instead of rpg chain operation to cope with a change in the file key

Here is another example of using SQL can make your life easier, coping with a change of the key in file without having to change the program.

At work we are gradually moving all of the US based IBM i partitions from a PowerSystems model 720 to a brand new model 900. I always thought PowerSystems model 900s could only run IBM i 7.3 and 7.4, it turns out that it will also run IBM i 7.2 but a whole load of PTFs have to be loaded and applied when moving from an older model (that was probably not up to date with PTFs too). The moves have been pretty uneventful, apart a change that makes a process I wrote many years ago fail.

The file QA1ALG in library QUSRBRM has changed, a new timestamp field has been added and that is used to replace the existing date and time fields in the file's key:

Tuesday, November 5, 2019

The future for IBM i looks beyond 2030

ibm chart shows future of ibm i goes beyond 2031

The post I wrote last month about the developerWorks closing at the end of this year. This caused a flurry of messages asking me if this meant that this was the end of the "AS400" too.

This is NOT a signal that the end of IBM i is nigh. I have been at various user group meetings where folks from IBM have told us that they have plans for this operating system going many years into the future. They also share an image showing past and the planned future releases of IBM i. After a good deal of "googling" I found this image that was updated after the launch of IBM i 7.4 .

Wednesday, October 30, 2019

Too many spool files: Which outqs, users, and the oldest

spool file reporting using output_queue_entires sql view

At work we monitor the percentage of ASP used to make sure we do not cross the threshold into "critical storage". Recently the amount of ASP has be slowly climbing, and I was part of a trio asked to determine what could be done to reduce the percentage. One looked for unapplied PTFs, another for exceedingly large files, and I was told to look at "everything else".

I know that one long neglected aspect, that is often overlooked, are the number, size, and age of the spool files on this IBM i partition. There are some spool files that need to keep, certain financial period end reports, but others, compile listing, job logs, do not.

First thing is to determine the size of the problem. Fortunately there is a SQL View that allows me to do this easily, OUTPUT_QUEUE_ENTRIES. I can just run the following statement and have the total number of spool files and total size returned to me:

Wednesday, October 23, 2019

More tips for using Temporal tables

db2 for i temporal tables audit user, recovering deleted row, how to delete tables

I first wrote about using Temporal tables when they were introduced as part of IBM i 7.3, back in 2016. I thought they would become widely used, as they provide a way to look at the same data as it was at different times in the past.

Having had three years to "play" with Temporal tables, I have learned many things I can do with them, that I did not know when I first wrote about them. I thought this would be a good opportunity to revisit them, and to share some of the extra tips and tricks I have learned.

One thing to remember, and I always forget, is that all of the tables used must be journaled. For the easiest way to do this refer to my original post about Temporal tables.

For a Temporal table I need a table that will be the "live" table. I have deliberately kept this example very simple, so the data does not get in the way of the important columns needed for a Temporal table. My example table will only contain two "data" columns, the rest are used by the Temporal table process. This is the example table I will be using:

Tuesday, October 22, 2019

The end of developerWorks is nigh

I noticed at the top of all the IBM developerWorks pages I visit there is the following message:

The developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its content (wikis, blogs, forums, files, activities) will no longer be available.

Wednesday, October 16, 2019

Finding the machine number and retrieving data from the job log

machine type of the power systems server you are using

The following question was posed on Twitter last week:

For the life of me I can't figure how to get an IBM i machine type programmatically. Suggestions?

While I was looking in all the places I could think of ex-IBM-er Dawn May provided an answer I could not better:

Thursday, October 10, 2019

Additions to RPG in new TRs

rpg enhancements to ibm i 7.4 tr1 and 7.3 tr7

On Tuesday I wrote about the new additions to Db2 for i in the latest Technology Refreshes, IBM i 7.2 TR1 and 7.3 TR7. Today I bring you the additions to the RPG programming language.

Barbara Morris, lead developer for the RPG compilers at IBM, published a blog in the RPG Cafe part of the IBM Community website. In this blog she introduces us to the three additions coming with these TRs:

Wednesday, October 9, 2019

Putting multiple SQL Selects together to make one row

multiple sql select statements within 1 select statement to make a single row

I was working with one of the Tech Support guys at work putting together bits of information into a one row of data that would allow him to display information on any IBM i partition. This is not something I had done before, and after a little while "playing" I had a statement that put bits of information from different sources together into one row.

I am sure we are familiar with using a Select to get information from a table/file, or perhaps join tables/files together, to get the results. But they are always related, with columns/fields that can be used to link the tables/files together. In this scenario I am running several independent Select statements, each producing one row of results, I want to combine into one. The information want is:

  1. Partition name
  2. Date and time
  3. Default CCSID, from system value
  4. Number of spool files in QEZJOBLOG output queue
  5. Model number
  6. Release of IBM i

Tuesday, October 8, 2019

Db2 enhancements in IBM i 7.4 TR1 and 7.3 TR7

ibmi 7.4 tr1 and 7.3 tr7

It is a good job I checked the pages for the new Technology Refreshes today as they now contain data, and I can find announcement documents too. But there has been no fanfare on platforms like Twitter that I would have expected.

It would appear that the two TRs contain the same Db2 for i enhancements. Just having a quick look I can see several I am very interested in trying:

  • Views to see which modules and service programs and bound to programs
  • Db2 services for data queues
  • Db2 services for the IFS
  • A way to use SQL to write directly to the job log

In other words lots of good stuff to play with and write about in the future.