When working in the IT business we all appreciate it if we are being helped by the operating system. For that purpose in the New Navigator for I IBM created the SQL button. Should we all start to use the name Navigator for i or keep it short, like I do, and use the term “Nav4i”?
What you call it doesn’t really matter, what matters is knowing how to use it.
Below you see an image of the button I would like to talk about:
This SQL button will tell you which SQL statement was used to get the data presented from IBM i. Please be aware that in rare cases this button is greyed out, not everything presented is retrieved using SQL. For example, when looking at the TCP/IP server on your LPAR, that is one of them.
When pressing the SQL button of the screen show above, an extra screen will pop up and show you the actual SQL statement used:
When pressing the button “Run SQL” you might get a window telling you what you need to do.
When starting IBM i Access Client Solutions (ACS) under the Tools menu you will find the option “Navigator Requests”:
When selecting that option, you will have the option to “Start Listening” by pressing the “Start” button:
Resulting in:
So when going back to Nav4i, pressing the “Run SQL” button again will result in an ACS Run SQL script being opened with an active connection to that system:
The idea to write this article came from this button and was based on call I received from a customer. The request they had was to automate the process of changing the object owner for all *DEVD objects created by a system administrator who had left the company. As the user owned over 100 printers and these printers were always in use, it included some extra work they did not look forward to. Especially as it this had to be done manually.
Doing this automatically had some challenges. Stopping the printer, vary the printer off, changing the owner, vary the printer back on again and finally starting the printer afterwards. Of course only for any printers which were running.
In order to get the information I went to Navi4 and selected “Users and Groups” and “Users”. Selected a random user and took the option to view the Library Objects only:
When doing that I did not care about the data, all I need is the SQL statement behind it:
After making some changes, I ended up with the statement shown below:
After that I needed to know if the *DEVD was locked by a job, meaning that the printer was started. For that I went to the screen shown below and pressed the “SQL” button:
Again, I made some changes, with this as a result:
Next month I will show you how to put this knowledge to good use so please stay tuned”
With these two SQL statements I have all what I need to create a SQL procedure. In my article for next month I will show you how this is all glued together. By then the customer will probably had the opportunity to test the procedure. So what I will show you has already been set to good use.
