In my last article A screen a story – What does this button do? The SQL button was used to retrieve SQL statements which I then tweaked to get the job done. The purpose of it all was to build an SQL script to automate changing the object owner of a printer, object type *DEVD.

In this article I am going to explain the SQL statements used and what is needed to create an SQL procedure to execute it.

If you are new to SQL procedures you can start getting acquainted with them by starting IBM i Access Client Solutions (ACS) Schemas and doing some reverse engineering. The library SYSTOOLS contains some procedures provided by IBM so, if this library/schema is not already installed, we are going to add it as shown below: Left click on schemas then the include icon on the far right of the window. In the Enter schema name field type systools then click “Add”

After pressing the “Add” and the “OK” button, we are able to open the Schema “SYSTOOLS” and have a look at the procedures:

To demonstrate that reverse engineering works we just select a random procedure:

Please be aware that every ACS option with “…” at the end opens a dialog window in which we can make changes before the action is performed. 

When selecting the option “Generate SQL…” a new dialog window allowing us to press the “Generate” button is shown. It is not unlikely that you have to scroll down in that window before the “Generate” button appears on the display. Now we can see what the procedure looks like:

Using this as a framework we created our own procedure to be used for changing the object owner of a printer.

I like to use the library QRPLOBJ, cleared at every IPL, to avoid having to clean up my mess after testing.  The first thing to do is add QRPLOBJ to the SET PATH statement.

The create or replace procedure statement is obvious to use, especially when you are in the testing phase.
The procedure is expecting two parameters: the current object owner and the new object owner. The procedure itself starts with the “Begin” statement and declaring all the variables used:

If you are familiar with CL programming you can compare the SQLEXCEPTION with
“MONMSG MSGID(CPF0000)” a sort of catch all error routine. In our procedure we use e-mail to tell us that something has gone wrong.

The body of the procedure starts with collecting all the printers owned by the current object owner value passed to the procedure.

The “FETCH FIRST 1 ROW ONLY” can be removed once you are happy with the test results, after testing with only the first record.

Depending on your business operating hours you may need to check if the printer is started and if it is start end the writer.

Please note that the option “*CNTRLD” is used to stop the printer. However, when the printer has a job status of “MSGW” using this option will not work. In that case you will need to use the option “*IMMED” instead. This could be done by adding an extra statement like:

And also add something like:

The rest of what needs to be done is pretty straight forward:

Using the LPRINTF procedure is very helpful because all the commands are placed in the variable “FULLCMD” so using LRPINTF allows you to place the commands in your job log as a comment. When a typo is made in a command the job log will tell you.

In my code I use the command DLYJOB DLY(60), which is a bit too much to allow for a printer to stop when using the option “*CNTRLD”. Bringing it down to 10 seconds, proved to be a long enough time for printers to stop. If you add the piece of code to also handle printers with a status of MSGW it might not be needed at all. As always, the proof of the pudding is in the eating.

2. When you want to test this new procedure, you have the option to use Content Assist available in Run SQL:script.
   When using that you will end up with this:  
   
   “Call QRPLOBJ.CHANGE_DEVD_OWNER_2(P_FROMUSER => ‘<Current_Owner>’, P_TOUSER => ‘<New_Owner>’)”

So enjoy your meal with a first bite into using SQL procedures on IBM i in changing the object owner of a printer.