PowerWire.eu

Independent IBM i, AIX and Linux news and tech tips for Europe and beyond

  • Home
  • News
  • Technical Articles
    • IBM i
    • AIX
    • Linux
    • VIOS
  • Subscribe
  • About Us
  • Contact Us
  • Advertise with PowerWire.eu

A screen a story – What does this button do? Part II

January 10, 2023 by Rudi van Helvoirt Leave a Comment

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.

  1. 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.

Filed Under: IBM i, Technical Articles Tagged With: IBM i, IBM Power Systems, Power Systems

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Free monthly newsletter signup

News

A screen a story – Not our problem anymore

Last week I ran into an issue, which did teach me something new about the command WRKPRB. After checking a … [Read More...]

Raspberry Pi and Machine Learning

In my last article, I demonstrated how I used Machine Learning on the Raspberry Pi to determine if a … [Read More...]

Shield Advanced Solutions – new Messaging and Monitoring solutions

September 2022 – Toronto As Shield Advanced Solutions celebrates the significant milestone, 25 years of … [Read More...]

More articles from this section

Quick Links

  • Advertise
  • Subscribe

Follow Us…

  • Email
  • LinkedIn
  • Twitter

Search This Website

Copyright © 2023 · Cue Communications · All Rights Reserved

Log in