Selecting fixed number of random rows in the results

I have written about using SQL's RAND() to generate random numbers to be included in the returned results, but not to return a fixed number of randomly selected results. In other words I just want five randomly selected rows returned in the results.

In this example I have a file, and yes it is a DDS file, that contains just two fields:

• COMPANY: company number
• VALUE: a three long numeric field

For company number 1 there are twenty records with the sequential values 1 – 20.

The request is for, let's say, five randomly selected records for company number 1 only.

New address for IBM's RPG Cafe

In October IBM's developerWorks website started showing an announcement that it would be closing at the end of 2019. Then last month IBM's Barbara Morris made it known that the contents of the RPG Cafe would be moving to another IBM web site.

Good news! The new RPG Cafe is now online with a very long URL, so I will just say click here.

Having had a quick glance at it, it appears that all of the same information that was available in the old version is also in the new. I have to say I prefer the user interface of the new site.

A big thanks to IBM, and everyone else involved with this, for preserving the RPG Cafe information.

Equivalent of DSPPGM and DSPSRVPGM using SQL part 2

At least once a day I use the Display Program command, DSPPGM, and the Display Service Program command, DSPSRVPGM, to look at the service programs and modules bound to ILE programs and service programs I am analyzing. While the command give me the information I need the latest Technical Refreshes, IBM i 7.4 TR1 and 7.3 TR7, gives me another way to get the same information from two new SQL views:

1. BOUND_MODULE_INFO
2. BOUND_SRVPGM_INFO

In this post I am going to describe how I would use them to replace what I do with DSPPGM and DSPSRVPGM commands, and a few other pieces of information I have found that these Views make it easy for me to get to.

Equivalent of DSPPGM and DSPSRVPGM using SQL part 1

I am sure we are all familiar with the Display Program, DSPPGM, and with the Display Service Program commands, DSPSRVPGM as a way to get all sorts of information about the program or service program entered. My two biggest complaints about these commands are:

1. I can only get the information for one program at a time
2. The only output options offered are display or print, no output to file

With the latest Technology Refreshes, IBM i 7.4 TR1 and 7.3 TR7, there are a number of new Db2 for i, or SQL, Views to give me the same information I was getting from the commands.

In this post I will be describing two of these Views:

Using SQL to write to the job log

Another useful addition to IBM i with the latest round of Technical Refreshes, 7.4 TR1 and 7.3 TR7, is a SQL function that allows you to write directly to the job's job log.

Why would I want to write to the job's job log?

I would use this to write to the job log when something I would expect to happen did not. Or as a record that some part of the program ran, and completed successfully or failed.

The syntax for this function could not be simpler:

CALL SYSTOOLS.LPRINTF(string or variable)

Data Queues using SQL

The latest Technical Refreshes, IBM i 7.4 TR1 and 7.3 TR7, included four additions to SQL that allow us to do things with Data Queues.

In this post I will be describing how I would do things with data queues pre-the latest TRs and post. These are:

1. DATA_QUEUE_INFO view
2. CLEAR_DATA_QUEUE procedure
3. SEND_DATA_QUEUE procedures (there is more than one and I will describe the differences below)
4. RECEIVE_DATA_QUEUE table function

But, before I start describing any of the above I am going to need to have a data queue I can use. Here is a CL program I wrote to create my data queue:

developerWorks data to be moved to another IBM website

Last month I noticed a banner that appeared on the top of all the developerWorks pages notifying us that it was to be closed on December 31 2019.

This has alarmed many IBM i folks who have taken to social media asking what is to become of the valuable information contained within developerWorks?

Yesterday I noticed the following announcement from Barbara Morris, lead developer for the RPG compilers, in the RPG Cafe:

More SQL Table Functions for interrogating IFS

Last week I started writing about the new table functions introduced in IBM i 7.4 TR1 and 7.3 TR7, describing the first of the four table functions that allow me to retrieve information about IFS directories and files. In this post I will describe the other three:

• IFS_OBJECT_LOCK_INFO
• IFS_JOB_INFO
• IFS_OBJECT_REFERENCES_INFO

 

New Performance FAQ released

A new IBM i on Power Performance FAQ was released yesterday, November 21 2019.

While you may not think there will be anything in it for you as you only program in RPG, there is a section for RPG and Cobol native I/O.

This is available as a PDF from IBM's website here.

Using SQL to list directories and files in IFS

Thanks to the folks at RZKH who downloaded and applied the PTFs for IBM i 7.4 TR1 on the first day they became available, what great service!, I have been able to explore some of the new features added to the operating system we know and love. Amongst the additions to Db2 for i, or SQL, is a number of table functions to list information about the directories and files in the Integrated File System, IFS. My original thought was to write one post about them all, but the more I played with the IFS_OBJECT_STATISTICS table function I came to the conclusion that it deserved its own post.

The IFS_OBJECT_STATISTICS table function returns similar information to the RTVDSKINF command. All I want is just some basic information about the directories and files I choose, but there are many more columns returned by the table function. I encourage you to check the link at the bottom of this post which will take you to the appropriate page in IBM's documentation.

In my folder, MyFolder, I have created the following files and subdirectories:

Technology Refresh release day

Today is the day the new Technology Refresh PTFs become available for download for the two most current releases of IBM i 7.4 TR1 and 7.3 TR7.

I wrote about what is included in these TRs last month:

• Db2 for i enhancements
• RPG enhancements

What are the PTF numbers for the TRs?

Fortunately IBM has pages that have all this information, including links to download the PTFs:

Listing Server Authority users

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?