Using SQL to look for object locks

This is almost embarrassing. I was asked if there is an easy way to programmatically check if an object is being used by another job. I knew the name of the SQL View, but when I searched this site I could find I mentioned it when IBM i 7.2 TR3 was announced, but I did not write anything more about this View. Today I am making amends of that oversight.

The question was is it possible to know which users are using an object. I could just use the Work Object Lock command, WRKOBJLCK, at the command line:

WRKOBJLCK OBJ(MYLIB/TESTFILE) OBJTYPE(*FILE)

But I needed to be able to retrieve within a program the same information.

Copying source statements from IFS files into programs

I am sure we are all familiar with using a copy compiler directive to copy source code from a "copy book" source member into another. As we can now edit and compile individual source files in the IFS, how can I insert code from those members into the source code of my source file members?

Below I am going to give examples in my three favorite IBM i programming languages:

• RPG
• CL
• SQL

I have three files in an IFS folder, MyFolder, that contain snippets of RPG, CL, and SQL that I want to copy into programs in a source file, DEVSRC, in my library, MYLIB. I also have code snippets in other source members I want to include too. How to do it?

Retrieving the Host's name using SQL view

When I run SQL queries, especially when retrieving system information, it is useful to include the IBM i partition name. I have been using a User Defined Function, UDF, I created to call a CL program to get this information. But looking at my trusty poster of Db2 for i I found the View that gives me the partition's host name and more interesting information.

This View also gives me the IBM i operating system version and release numbers, so I don't have to use the QSS1MRI data area and the DATA_AREA_ table function to retrieve it.

IBM Rational Developer for i Hub website

The next part of the process of moving the contents from IBM's now closed developerWorks to other new IBM sites occurred last week, with the announcement of the new Rational Developer for i Hub.

This is wonderful looking website that has put a wealth of information just a few clicks away from the home page. It appears to have everything that would help an existing RDi user, and for someone new to this tool. I have to say excellent work, and thank you, to all involved with creating this website.

You can reach this new site by clicking on this short URL: https://ibm.biz/rdi_hub

This follows the RPG Cafe's new site, which was opened in December of last year.

Delete many spool files with one command

Addendum: It is easier to do a mass delete of spool files using a SQL procedure, you can read about it here.

---

Have you ever wanted to delete many, many spool files with just one command? You know the scenario someone decided to print every invoice that had ever been generated, and now I have several thousand spool files in an output queue. I use the WRKSPLF command to view all of the spool files in the output queue, and starting deleting them one at a time by putting a '4' next to them. As I do this I am thinking there must be a better way?

Could I just clear the output queue? No, that's not possible as there are many other spool files in the same output queue, belonging to other users, that need to be printed or retained.

Checking authority to a file using SQL

It was another one of those examples of a find when I was looking for something else. This is a SQL scalar function, SQL_CHECK_AUTHORITY, that returns a single value to inform me if I am authorized to use a file or not.

What is a scalar function? A scalar function is passed one or more parameters and returns a single value.

The syntax is very simple:

QSYS2.SQL_CHECK_AUTHORITY(library,file)

It returns one of the following values:

IBM YouTube series on PowerSystems myths

 

The web page that was originally linked to no longer exists, therefore, rendering this page obsolete.

 

Happy New Year, and welcome to 2020

The last of the Christmas presents have been opened, and all of the decorations have been taken down and stored away. At the beginning of a new year I always like to reflect on the old year, and look forwards to what is coming.

2019 was another exciting year for the IBM i community. A new release of the operating system became available to us. Accompanying the new release there were another two Technology Refreshes for IBM i 7.3, and the first TR for the new 7.4. These added all kinds of new and exciting features and functions to our favorite operating system, which I have written about in this web site.

The only way I can gauge what you, the readers of this blog, considered the most interesting posts I wrote last year is by looking at the most read. These were:

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: