If you’re dabbling in the client/server world with ODBC—you may have noticed that it can be slow—very slow. If you have this problem and haven’t tried using prestarted jobs, you may want to consider using them. Prestarted jobs can reduce ODBC record retrieval times from over a minute each to under five seconds.

Prestarted jobs have been around for a while. They were designed to speed up the time it takes for remote jobs to access your AS/400, log on, start a program, open files, and initialize the working environment. Those jobs can be Advanced Program-to- Program Communications (APPC) jobs or users on a remote communications link. Prestarted jobs work just as well for ODBC connections.

Unfortunately, prestarted jobs don’t get a lot of attention. This article will cover the basics and point you in the direction of more information. First, though, let’s talk about the relevant parts of an ODBC driver.

Your client workstations probably already have an ODBC driver, and it’s probably already set up properly. Within it, you have imbedded user code or password information so the driver can access the AS/400 without the user seeing an AS/400 sign-on screen. This driver also contains the name of the AS/400 program the driver must work through. To the AS/400, this program name is the driver’s program start request. Normally, when the client makes a request for AS/400 data, authority is established, the request enters the subsystem, and the start request’s program name is started.

The delay happens when the program is starting. Nothing has to be changed on the ODBC; we’re just going to get the AS/400 to save that time by prestarting the program named in the ODBC driver’s program start request.

Let’s return to the AS/400. A prestarted job is part of a subsystem, similar to routing entries. They are called prestarted job entries, or PJEs. PJEs aren’t part of the

subsystem as you create or change it; they are added separately to existing subsystems. CL has a whole set of commands to add, change, remove, start, and end PJEs.

Your first task is to determine which subsystem your ODBC workstations enter the AS/400 on. It’s probably something like QCMN (the communication subsystem), but if you support a large network of workstations, you may have several QCMNs, or you may set up a special one to support ODBC clients.

You add PJEs to the subsystem description with the Add Prestart Jobs (ADDPJE) command (see Figures 1 and 2). Here is its format:

ADDPJE SBSD(subsystem description>) +
PGM() +
JOBD(<*USERPRF or *SBSD or +
) +

USER() +
STRJOBS(*YES) +
NLJOBS(prestarted versions of PGM>) +

THRESHOLD(versions of PGM>) +

ADLJOBS() +
MAXJOBS(one time>) +
MAXUSE(hit (for record retrieval) before + it is terminated + and restarted>) +
WAIT(*YES or *NO) +
POOLID(1 or ) +
CLS( + )

Notice what’s happening here. The command actually creates an optimum environment for n copies of one program to run in. That environment may be the same as the subsystem’s, or it may be unique to the prestarted jobs. This is rather weird because these programs and their environment run within a subsystem and its environment.

Let’s review the command keywords. SBSD is the subsystem description the PJE is being attached to. PGM is the program that will be prestarted. If you don’t know its name, check the documentation from your ODBC driver or call the vendor. These products expect to run through an AS/400-based program, you just need to know what that program name is (and that it is loaded, if you’re not sure).

The USER is a user profile you create for each version of PGM. Try something like CSODBC (Client/Server ODBC). Create it just as you would a group profile—it’s a user profile that is *DISABLED and has an expired password. It may belong to a group, but the profile and its group should have only *USE access to production database files. By the way, when the workstation connects to this program, the user authority that will go through the program to the file objects is the user authority from the user profile signing on at the client. Double-check that profile and your object authorities to make sure the profile doesn’t have any more authority than it should. You don’t want to inadvertently open up too much file access to an ODBC driver that has READ/WRITE capability.

JOBD is the job description. If you know the AS/400, you know job descriptions are liberally sprinkled all around the place. In this case, you can specify a JOBD just for prestarted jobs, or you can refer the system to pick up the JOBD from the user profile (*USRPRF) or from the subsystem description (*SBSD).

The STRJOBS(*YES) parameter tells the system to start the prestarted jobs automatically when the subsystem starts. If you specify *NO or take the default (which is *NO), the prestarted jobs aren’t started until you issue the STRPJE (Start Prestart Job Entries) command. This sounds strange, but you may have a situation where you want to

restrict ODBC high-priority contact to certain hours of the day. If you manually start prestarted jobs, you end them with ENDPJE (End Prestart Job Entries). Most shops just use STRJOBS(*YES).

INLJOBS is the number of versions of PGM the subsystem should start. The default is three, but you can override it to any number up to a 1,000. The number you use will vary, but 10 to 15 is fairly standard. Notice that we will have one PJE in the SBSD for each program. This (and the next three parameters) will determine how many versions of the one program will be running. So, if you want to start up subsystem QCMN with 15 versions of program RCVODBC, you make only one PJE entry for the program and specify INLJOBS(15).

THRESHOLD specifies how many unused prestarted jobs can be on the subsystem before it starts another set. The default is two. If you specified INLJOBS(15), you may want to specify THRESHOLD(5) to indicate that after 10 clients claim versions of the program, the subsystem will prestart another set.

ADLJOBS specifies how many additional jobs the subsystem will prestart after the THRESHOLD value is reached. The default is two again. Following the example, I would use ADLJOBS(5), meaning when THRESHOLD is reached (10 clients are using 10 programs, leaving five unused), the subsystem will prestart another set of five jobs leaving 10 unused.

MAXJOBS denotes the maximum number of prestarted programs that can be in the subsystem at once. Note that this overrides the MAXJOBS of the subsystem description, which should give you an idea of how serious these prestarted jobs are. If you are supporting a large (or potentially large) ODBC network, you may want to use *NOMAX here. Think this through very carefully. Typically, prestarted jobs are there to ensure a group of users better performance. Don’t get into a situation in which you have so many active prestarted jobs that they gobble up your system.

The MAXUSE parameter (default 200) limits how many accesses a program can take before it is terminated (normally) and restarted. This is a tricky one. Your first question may be, why does it terminate after any number? Every record access through the program results in one or more job log entries, depending on your system’s logging level. It’s quite common for a continuous, heavy, ODBC connection to overflow the job log. So you want to periodically terminate the program, flush its log to the job log, and restart fresh. The other side of the equation is that, when you terminate and restart the program, your ODBC client is going to see a serious slowdown. The balance for you to determine is how big you potentially want your job log to be (or do you want to create a job description with no logging?) versus how often you want your client user to see a degradation.

The WAIT parameter tells the system what you want it to do with an ODBC request when there are available versions of the support program. It can wait— WAIT(*YES)—or return to the client (*NO) with an error.

The POOLID is the subsystem (not the system) pool ID that the prestarted programs will run in. The POOLID default on most subsystems is system pool 2 or *BASE. It’s not a bad place to run these jobs, but if you’re having high memory fault rates when ODBC is busy, you may want to specify another subsystem pool ID. You might add a special memory pool to the subsystem description just to support PJEs.

The CLS parameter allows you to define the job class (which contains runtime attributes like priority and timeslice) that the prestarted job runs with. This is a different implementation of class, though. PJEs contain two possible classes: the first one for a certain number of active jobs and the second for all others. This is a good way to give

small numbers of prestarted jobs lots of priority but still protect your system by restricting the resources of higher numbers of ODBC request sessions.

There are other things around the PJE you may be concerned about: Job descriptions. Most shops use *USRPRF or *SBSD. You can also create a special job description for your prestarted jobs.

Users. Make up a unique user profile for these jobs, CSODBC is a good one.

While we’re talking about user profiles, I should note that you may want to create a special message queue (CRTMSGQ) and output queue (CRTOUTQ) to isolate messages or output from the ODBC transactions.

Prestarted jobs take no system resources until they become active. You won’t see them on the WRKACTJOB (Work Active Jobs) or WRKSYSSTS (Work System Status) screens, so you don’t have to worry about resources or performance on prestarted jobs themselves.

Although the conventional way to connect an incoming ODBC call with a prestarted job is simply to have the subsystem match an incoming program start request with the program named in a PJE, there is another way. Use the subsystem’s routing entries to match incoming program requests to the PJEs. Enter routing data on the incoming job description and a corresponding routing entry in the subsystem description. Specify the PJE as the routing entry’s Program to Call parameter, and you’re in business. This may be a way around a situation in which for some reason, the incoming ODBC doesn’t have a program start request.

If you’re frustrated with ODBC calls on the AS/400, try prestarted jobs. You should notice an improvement right away. After you’ve used them for a while, they will be functional enough that you can do some serious tuning with them, so you’ll deliver the maximum performance to the client and take the minimum performance hit on your AS/400.

References

AS/400 CL Reference: ADDxxx through CHGxxx Commands (SC41-3723, CDROM QBKAUP01)

OS/400 Work Management (SC41-3306, CD-ROM QBKALG01)

Figure 1: The Add Prestart Job Entry Screen Figure 2: The Add Prestart Job Entry Screen Part 2

Also Read

The API Corner: Moving Jobs Within and Across Job Queues

The API Corner: What Time Zone Do You Want That Date and Time In?

The API Corner: How Do You Want That Date and Time?