This article provides a potential way to identify orphan IBM i jobs as a result of Database connection leaks in ASP.NET Web applications. We’ve written before about the perils of orphan IBM i jobs before. This article and video discusses a potential way to identify orphan IBM i jobs and this older article also discusses techniques for avoiding orphan jobs. Este artículo provides some diagnostic help locating the cause of orphan jobs.

Orphan jobs are challenging to identify. In a large system with many users, it’s possible that you have some orphan jobs and don’t know it–and don’t care. However, it’s also possible that the occasional orphan job could cause intermittent, hard-to-find problems.

The short story

If you are familiar with the challenge of finding ASP.NET job leaks that result in orphan jobs, scroll down to the "SQL to the rescue" subheading. Otherwise, put your tray tables in their upright and locked positions, fasten your seat belt, and lean in.

The long story

Simon Hutchinson is an exceedingly clever RPG/SQL programmer and writes a great RPG-related blog at RPGPGM.com. One of Simon’s many specialties is SQL on the IBM i. While reading some of his work the other day I learned that SQL on the IBM i may help find orphan DataGate jobs on the IBM i.

Before we get to that SQL, let’s review a few points first.

SQL to the rescue

Given all that, the challenge to finding potentially orphan jobs is to be able to capture DG8_NET jobs that are active with open files. This is nearly impossible to do with WRKACTJOB. This is where one of Simon’s SQL tricks comes into play.

With your Web application active, and users pounding on it, open a 5250 emulator and enter the STRSQL command. Type in the SQL below and press enter:

This SQL lists all of IBM i jobs named DG8_NET that have opened files. The SQL takes about 10 seconds or so to run. When it’s done it shows DG8_NET jobs with open files:

downloads/images/asna-com-images/sql-open-files.png

More info on ActiveJobInfo https://www.ibm.com/docs/en/i/7.4?topic=services-active-job-info-table-function

The body of the list is empty if there are no DG8_NET jobs with open files. If you do catch an open job with the SQL, run the SQL again to see if you happened onto a job that hasn’t yet closed its files. In the ten seconds or so it takes the SQL to show potentially offending jobs, if a job is shown more than once, it’s probably an orphan job.

The jobs listed aren’t necessarily ASP.NET-related jobs. If you have Visual RPG fat clients, those jobs will be shown, too.

The general rule is, if you’re using DataGate on the IBM i exclusively for ASP.NET Web apps, and the SQL above consistently shows the same job(s) in the list, you ASP.NET Web is probably leaking database connections and causing orphan jobs. If that’s the case, take note of the files opened as a clue as to where to start looking for the leak.

A IBM i job that is active doing work for a Web app (between form load and form unload) is only active for just a few seconds—maybe even milliseconds–before it is returned to the job pool. If the app is coded correctly, (ie closing all files and disconnecting from the DB before any page goes out of scope)  it’s unlikely that running the SQL provided whould ever show any jobs with open files. It could be that it will catch one occasionally at just the right time to show it; but if things are running as expected you won’t see very many and if you do they should do away quickly. However, if you run this SQL say once every 10 seconds, and if you continually see jobs with open files listed (and especially if this list is growing) there is a place in your Web app where it isn’t closing files and disconnecting from the DB before a page goes out of scope.

Remember, too, that using DB connections in secondary classes in an AVR Web app is also likely culprit in creating orphan jobs with open files. See this article for more information.