How find what ASPX page is causing orphan IBM i jobs

ASNA Visual RPG makes a great partner with Microsoft’s ASP.NET to create superb IBM i websites. For the best performance, these apps should always use DataGate connection pooling and you should always take great care to disconnect every active database connection before a page goes out of scope. If you don’t disconnect a database, the IBM i job is orphaned and doesn’t end right away. The connection pooling article previously mentioned covers orphaned jobs in detail.

This article offers a few tips on finding the cause of orphaned IBM i jobs associated with your AVR ASP.NET web app.

Although this article focuses on the IBM i, the concepts all apply to Web sites driven by ASNA DataGate for SQL Server. The biggest difference there is that you don’t have the WRKACTJOB command to see active connections. However, this Stack Overflow article explains what the SQL Server equalivalent is to WRKACTJOB.

How to tell if you have orphaned jobs

It’s sometimes hard to know if you have an issue with orphaned IBM i jobs. If an AVR ASP.NET website is created without much regard for watching for orphaned jobs during development–then in production they just start sneaking up on you. If you’re using DataGate connection pooling correctly, expect one IBM i job for each block of 20-50 users (as reported by WRKACTJOB). Even with 100 users, you shouldn’t see very many IBM i jobs for your ASP.NET web app. If you have less than 100 users and see more than 10 or 15 jobs or so, you should look deeper into the cause of that many jobs. These numbers may vary widely depending on the type of web app you have and how quickly your users move from page to page. Knowing the exact number of jobs to expect isn’t an exact science–it is not just the number of users but also how heavily users are pounding on the keyboard and showing more pages. It might also help to keep track of the number of jobs at different times of the day for a couple of weeks. This may reveal an issue. Orphan jobs are an insidious problem–that only gets worse as time goes by. For example, consider a Web app with five users. Spurious jobs produced with only five users are annnoying, but with this few users there may not be enough orphan jobs to cause any issues. However, as the number of users grows, so grows the chance that more and more orphan jobs are created. There is a scalability tipping point where this issue goes from annoying to “wow-we need to do something about this.”

What causes orphaned jobs?

Orphaned IBM i jobs are caused by failure to disconnect from a DataGate IBM i connection before a page goes out of scope. Generally, the pattern to ensure a web page doesn’t cause an orphan IBM i job is:

1. Connect to database
2. Open files
3. Do work
4. Close files
5. Disconnect from database

A simple implementation of the pattern is illustrated in the code below: Figure 1. Simple code to ensure IBM i jobs aren’t orphaned. Note the code in Figure 1’s Page_Unload routine. You must explicitly close all files and disconnect the DataGate IBM i connection. Without doing show causes a database leak which results in an orphaned IBM i job. Each time a page is shown that doesn’t correctly perform a database disconnect causes another orphaned job. It doesn’t take much time to cause many orphaned jobs. You also need to beware of any secondary classes that your code behind may be using. The database connections in these classes can also cause database leaks. Read about the singleton DB pattern for more info on how to avoid database leaks with secondary classes.

Using the page-at-a-time test to determine what page is causing orphaned jobs

A good way to determine what page is causing orphaned IBM i jobs is to:

1. Restart the website to ensure you’re starting the test from a cold website.
2. Ensure you are the only user of the web. This test only works with one user using the site. With more than one user, you’ll get spurious test results. Testing the site with only one user is often quite challenging on a production website. It’s best to do this test on a test server, but that isn’t always possible and the next section shows a way to help ensure that there is only one user of the web app during this test–even on a production site.
3. Start a green-screen 5250 session and use WRKUSRJOB to watch for website DataGate jobs. (watch for the same username used in the DataGate database name)
4. Start using the site. As you are using it, press refresh the WRKUSRJOB display after every page. If you are the only user, there should always be one job displayed. If more than one appears for a page, you’ve located the page where the database leak is.

When you find a page that causes a database connection leak, examine its code and add the necessary code to close files and disconnect the database connection. Then run the test again. Other offending pages may lurk! Note: It may also be possible to test for orphan jobs using a unique user profile that you are sure no one else is using. While this could work, performing the testing with no other users active is the best way to get reliable test results.

Ensure only one user using a production application

The previous page-at-a-time test works only when the tester is the only website user during testing. This is a challenging task for a web app deployed to production. Despite your best efforts to get everyone out of the app, there are occasional stragglers that will come along. However, how do you know someone else is using the app? If no one comes along, you can perform the page-at-a-time test on a production site. To ensure the tester is the only user of the website, you can use ASP.NET’s performance counters to display the number of currently active sessions. As long as that number stays at 1, you know the tester is the only website user and the test will be meaningful. If you must do the test on a production server, following these steps to ensure there is only one user of the website. Step 1. On the web server, start the Windows Performance Monitor (Start->Run-Perfmon). Click the “Performance Monitor” entry under the “Monitoring Tools” entry and then click the green plus sign (outlined in red).

Step 2. Use the browser button to find your web server in your network. Then click the “ASP.NET Applications” dropdown arrow (outlined in red).

Step 3. Select “Sessions Active” and “Sessions Total” (use Control/Click to select more than one entry) from the dropdown list displayed. Click the “Add>>” button (outlined in red) and then click “OK.”

Step 4. From the dialog displayed, click the “Change graph type” button (outlined in red) and select “Report.”

Step 5. Watch the report below displayed as you perform the page-at-a-time test described in the previous section. During your tests, it should always show one session active and one session total. If those values change during your production testing, someone else is also using the site and your test results won’t be meaningful. If this occurs, you’ll need to restart the test again when you can be the only user.

This page-by-page stepwise way to watch for database leaks takes a little patience, but it is generally the best way to find leaks.

Using DataGate’s special database logging

Another way to help determine what page is causing orphaned jobs is to use DataGate’s special database logging. This causes some special entries in the IBM i job. To use this logging facility: Step 1. Start Visual Studio as an administrator (locate Visual Studio in the Windows Start Menu, right click it and click the “Start as administrator”). Use View->DataGate Explorer and right-click the Database Name for which you want to use logging to display the dialog shown below:

Step 2. Click the “Advanced” button on the dialog shown above. Scroll to the Text property and set it to “ASNALOGTEXT” (in all uppercase as shown). Click OK. If you’re testing on a production server, restart the website. If you’re testing from within Visual Studio, run the web app.

Step 3. With the web app running, on the IBM i use WRKUSRJOB to work with the website’s IBM i job. Display its job log’s detailed messages as shown in the GIF below:

Step 4. Scroll through job log details as shown in the next two screens. A simplified stack trace is displayed. You should see the Database.Connect and Database.Open and Database.Close as matching pairs. Anytime a Database.Connect and DataGate.Open is displayed without a corresponding Database.Close, a database leak has occurred.

This logging display is helpful, but it can be confusing. It might be helpful to write the job log to a spool file. This requires IBM i admin rights, so check with your IBM i administrator help. Briefly, you need to:

1. Copy an IBM i job description (using CRTDUPOBJ)

2. Change that copied job description so that all of its messages are logged (as shown below in the red box)

3. Assign that IBM i job description to the IBM i user named in your web app’s database name (using CHGUSRPRF). Make a note of the original job description so that you can change it back later.

4. Use WRKSPLF to see the job log created after running the web app.

When you’re done evaluating job logs, change the user’s job description back to what it was. These captured job logs can get very large.

Avoid leaks in the first place!

The best thing to do regarding database leaks and orphan jobs is to avoid them in the first place. It’s easiest to check for them before deploying the web app. As you’re building a web app, be sure to check that your project is only using a single job frequently!