Wed, Apr
5 New Articles

Kick Start ODBC with Prestarted Jobs

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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() +
) +

USER() +
NLJOBS(prestarted versions of PGM>) +

THRESHOLD(versions of PGM>) +

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.




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

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


Kick_Start_ODBC_with_Prestarted_Jobs05-00.jpg 450x304


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


Kick_Start_ODBC_with_Prestarted_Jobs05-01.jpg 450x304




Support MC Press Online

$0.00 Raised:

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: