A very important consideration for AVR for .NET ASP.NET websites is managing IBM i jobs. This article discusses how to ensure that an AVR for .NET website doesn’t create more IBM i jobs than necessary. This is very important. Without fully understanding the relationship between a DclDB object and its corresponding IBM i job, it’s easy to write a web app that creates many unnecessary IBM i jobs.

This article shows how to avoid unnecessary IBM i jobs by using the singleton DB pattern. Using that pattern, you establish a single IBM i job in the top-level parent page and then make that connection available to any class instances the parent page uses.

During any one user’s use of your AVR web app, there should only be one IBM i job in play for that user. You can easily test this during development. Start your web app in Visual Studio and use the IBM i’s WRKUSRJOB to see the IBM i jobs for the user specified in the DataGate database name. If you ever see more than one job while you are the single user of the web app, you’ve got an IBM i job leak that should be plugged!

This article assumes familiarity with DataGate connection pooling and the concept of orphan IBM i jobs. See this article for more detail on those topics . This article also provides related help finding the cause of orphaned, or unnecessary, IBM i jobs.

Figure 1a below shows a simple code-behind for an ASPX page. This code-behind declares a database object named DGDB1 and correctly connects and disconnects that database object before the page goes out of scope. The code-behind has one file named File1 and it also instances a secondary class named MyClass (instanced as myc ). You don’t see the code-behind doing anything but opening and closing File1 ; in the real world, there were be other chunks of code that do something with that file. This code-behind’s Page_Load is also a bit unrealistic. These abstractions are used to keep the code short.

Close *ALL closes all files opened in the class where the Close *All is used; it doesn’t close files opened in other classes. Every class must issue its own Close *All .

Figure 1a. A code-behind fragment instancing and using a method in a secondary class.

The MyClass that Figure 1a uses is shown below in Figure 1b.

Figure 1b. The secondary class used by Figure 1a.

The class in Figure 1b declares its own database object named DGDB2 and it connects DGDB2, open files, does work, closes files, and then disconnects DGDB2. While this follows ASNA best practices to avoid orphan IBM i jobs, it results in an unnecessary job. Why?

The code-behind has declared and connected one database connection, which results in an IBM i job, and the code in MyClass (Figure 1b) is doing the same thing. Each time you connect to a database object (a  DclDB object) an IBM i job is created (or pulled from a pooled job). Figure 1c below illustrates this.

Figure 1c. An IBM i job schematic for the code in Figures 1a and 1b.

Checking WRKUSRJOB while the DoWork routine in the MyClass code is running would confirm two IBM i jobs running.

Figure 1d. Using WRKUSRJOB to confirm that two jobs are associated with Figure 1a and 1b’s code.

It could be argued that this second job isn’t that big of a deal. With DataGate’s effective connection pooling, and with a small number of classes, that’s probably correct. While the scenario shown here does create an extra job, that job gets pooled–which makes it quickly available the next time the web app needs a job. However, for large AVR websites, you often have many secondary classes with many jobs. It only takes a little effort to channel all secondary class database activity through the same job as the code-behind is using.

Avoid unnecessary jobs with the Singleton DB Pattern

Use the Singleton DB Pattern to limit any one code-behind and all of its secondary classes to using one IBM i job. Don’t let this somewhat highbrow name be offputting. Naming the solution to this multiple-job challenge simply provides a name for the solution–and provides a vocabulary we can use while discussing the issue ( like all software patterns do ). Implementing the Singleton DB Pattern is less off-putting than its tuxedo-wearing name.

The enabler for the Singleton DB Pattern is .NET’s ability to pass an object by reference–in this case the database object. That is, the code-behind will pass, by reference, its database object to any secondary classes. These classes then will use that parent’s database connection (and its underlying IBM i job). Let’s take a closer look.

AVR for .NET does default to passing parameters by value. However, in .NET (as with Java, JavaScript, and many other languages), reference type values (for example, those variables that reference a class instance) are passed by reference. This may seem a little confusing if you’re new to .NET. The value of a reference types is effectively a pointer in memory to referenced object. So when a reference type gets passed by value you are essentially passing the memory address of its referenced object. In this article’s case, the memory address of a database object is being passed around. Read more about value types and reference types here.

The revised code-behind that implements the Singleton DB Pattern is shown below in Figure 2a.

Figure 2a. A code-behind fragment instancing and using a method in a secondary class.

This code in Figure 2a is nearly identical to the previous code-behind with one important change: It passes its DGDB1 database connection to MyClass's constructor, MyClass , with this line of code.

The revised code for MyClass is shown below in Figure 2b. There are a few more changes in this code. While they are minor changes, they cause a dramatic improvement in job management.

Figure 2b. The secondary class used by Figure 1a.

In the code above in Figure 2b, you’ll notice that MyClass's constructor is now explicitly defined. It receives one parameter: an AVR database object. The real magic here is the next line:

The constructor gets called before anything else occurs in MyClass ; this ensures that the very first thing that happens in MyClass is that its database object ( DGDB2 ) is now referencing the database object passed in the constructor ( DGDB1 ). This is effectively a runtime swap of the database object in MyClass . After this assignment, the DGDB1 and DBDG2 variables are referencing the exact same object in memory at runtime. (Note that at compile time, DBDG2 itself is implicitly connected in MyClass to resolve any declared files.)

Because the value of reference types is a memory address, DGDB2 = DGDB1 is making both variables point to the class instance referenced by DGDB1 .

You’ll also notice this line immediately after the database assignment:

This ensures that if the code-behind didn’t connect DGDB1 that it gets connected for MyClass's use. To further illustrate that DGDB1 and DGDB2 are now referencing the same database object in memory, this line could also be:

Because at this point both variables are referencing the exact same object. Let that soak in. It is a critical point. It’s very important to do the connect after the database assignment ( DBDG2 = DBDG1 ). Otherwise, you’ll cause an orphan IBM i job.

AVR for .NET doesn’t raise an error if Connect is issued against an already-connected database object. You can safely issue the Connect operation without regard for whether the database object is already connected.

You’ll also notice that there isn’t a

in MyClass . Remember that MyClass is effectively borrowing the code-behind’s database connection. The Singleton DB Pattern grants the secondary class permission to use that database connection, but secondary classes do not have permission to disconnect database connections. That’s the parent’s job (in the case, the code behind in Figure 2a).

The Singleton DB Pattern imposes a strict assumption that when object A passes a database connection to object B that object B will not make any changes to the state of the database connection. It’s like loaning your car to a friend. She can use it, but under no circumstances can she change it! Don’t bring it back with scratched paint and Taco Cabana trash in it!

Secondary classes should always have a

to close all files opened in the secondary class before the secondary class goes out of scope. There is a convention in .NET that says if a class has a Close method, that method should be called before that class goes out of scope. For this reason, you’ll often see a method like this in classes that implement the Singleton DB Pattern if the code doesn’t inherently close the files (as Figure 2b does).

If a class (any class, not just those obeying the Singleton DB Pattern) has a Close method it is the programmer’s responsibility to call that method before the class goes out of scope.

Figure 2c below shows the code-behind and the secondary MyClass in action and how they both share the same IBM i job.

Figure 2c. An IBM i job schematic for the code in Figures 2a and 2b.

If you were to use WRKUSRJOB while MyClass is doing some work, you would now see only one job, as shown below in Figure 2d.

Figure 2d. Using WRKUSRJOB to confirm that only one job is associated with Figure 2a and 2b’s code.

Notes on the Singleton DB Pattern

As a general rule, it’s best to only use Shared(*Yes) in web apps for nothing but pure functions or constants.


The Singleton DB Pattern is very effective in limiting IBM i jobs in complex AVR web apps. Use it as you’re building (or adding to) AVR web apps. Also be sure to check frequently during the development stage to ensure your app is always using only one IBM i job.