In my last article I wrote: “IBM i Services allows a modern system administrator to move away from having to use 5250 emulation, not because you should, but because using IBM i services is quicker in getting the job done. In my next episode of a screen a story I am going to demonstrate that using Navigator for i.”. So this month it is time to fulfil that promise.

A call came in from a user who reported slow performance using ODBC to query a file. That is what happened in real life which was the inspiration for this article.

In 5250 emulation there are two ways to find out which job has slow performance. The first one is starting from the file and run the command WRKOBJLCK or using the same command, but this time against the user profile. So “WRKOBJLCK OBJ(<LIBRARY>/<FILE>) OBJTYPE(*FILE)” or WRKOBJLCK OBJ(<USERPROFILE>) OBJTYPE(*USRPRF). Depending on the activity on your system one or the other is the fastest way to find the job. Finding the SQL statement being executed is not always as simple as it looks because today a lot of SQL statements are being generated under the covers. 

It is here where Navigator for i has the advantage because it allows you to combine searching for the user  and the file in one go.

Assuming that the SQL statement is run against file QCUSTCDT in library QIWS, a file provided by IBM and available on nearly every IBM i LPAR.

The actual SQL statement run is “select * from QIWS.QCUSTCDT where STATE<>’NY’”.  With only a handful of record this is not really a candidate for demonstrating slow performance but I hope you don’t mind me using this file.

When we start Navigator for i (Nav4i), we open the panel Active Jobs. Depending if we have set up Subsystem Routing for ODBC we might decide to first have a look there. For the “Details” level we need to find the information we are looking for which requires us to select the “All” level when looking at the Active Jobs. We enter our filter strings as shown below:

When we press Enter after typing the last character of our search string, the following result is shown:

As you can see we have now found our job executing the SQL statement. It is here where the advanced functionality of Nav4i comes into play.

When we right click on the a panel is shown and when we select “Details”, another panel is shown. It there where we can see the option “SQL Details”.

When selecting that option the SQL script is shown, again in a new panel with a tab for the SQL Properties. Good performance start with up-to-date software and here we can check the interface level. If the Interface name reads “IBM i Access for Windows” there is work to be done.

We now have the actual SQL statement as it was used by the job with the slow performance. We now need to use IBM i Access Client Solution (ACS) “SQL  Performance Center”. Again no 5250 emulation, but as they say the right tool for the right job.

For those of you who want to learn about the more advanced way, you can even avoid using Navigator for i, read on.

Remember the screen “Active Jobs -All “ where the strings “Rudi” and “qcustcdt” were entered. That panel has an SQL button. If you press that button, the screen shown below will pop up:

Pressing the button “Run SQL” will result in ACS “Run SQL Scripts” starting. If instead of that you get this:

If you do as instructed here, the next time you press “Run SQL” you will see:

When you have IBM i Access Client Solutions 1.1.9.2 of higher installed by making some minor changes you can turn this SQL statement in more flexible one. When running it afterwards it will ask you in a prompt window for the values to be used to search for.

It will look like this:

Navigator for i is one of the biggest IBM i Services advocates. I hope you now know why it is time to enter the world of IBM i Services. Navigator for i makes that step easier and will take you by the hand on your journey, in the same way as it did in this article.

Bon voyage 🙂