Avatar of Matthew Roessner
Matthew Roessner

asked on 

iSeries - Cancel Stagnant Jobs

I am wondering if anyone has a program or script they are running for the IBM i that will look for stagnant jobs (a certain job name, ie: QZDASOINIT) that have been running for x days (ie. a job named QZDASOINIT that has been running for more than 5 days).

We have had some issues in the past with stagnant jobs taking up resources on our servers.  I wanted to create a program to search out for these "stagnant" jobs and remove them.

Thanks in advance,

IBM System i

Avatar of undefined
Last Comment
Gary Patterson, CISSP
Avatar of Carlos Ijalba
Carlos Ijalba
Flag of Spain image

This should be relatively easy to do with a script under QSH, as with CL it will be a bit more convoluted to do.

But, won't it be better to look at the joblogs of this jobs and find out where they are getting frozed? more than likely a problem with LOCKS.

I have a simmilar script for ksh on AIX wich should be fairly straightforward to convert to qsh, if you are interested on Qshell.
Avatar of Gary Patterson, CISSP
Gary Patterson, CISSP
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Matthew Roessner
Matthew Roessner


I was specifically talking about the QZDASOINIT jobs specifically.  We have had a problem a few times with MANY of these jobs being out in QUSRWRK. They end up sucking a large amount of processor (none individually - but all of them together). This eventually causes the system to get bogged down and then other jobs start backing up. Users don't see their completing like normal so they re-submit them....essentially snowballing the problem.

If we end all the QZDASOINIT jobs for a particular user (associated with a website that connects to the server) - everything starts running normally again and the system catches back up very quickly.

Maybe I just need to tweak the QZDASOINIT job properties?
Avatar of Carlos Ijalba
Carlos Ijalba
Flag of Spain image


In your case if it's just a matter of QZDASOINIT jobs, and as this jobs are related to DB2 performance, I just wouldn't kill them, better off open a case with IBM, since I have seen before problems with this jobs consuming too much and the problem was caused by expensive SQL statements, which I think you can analize with Database Monitor from iNavigator.

Carlos is probably on the right track.  

You really should not be managing QZDASOINIT jobs directly.  This is a SYMPTOM of an application problem, not a CAUSE.

If the jobs are using CPU, then they are active, and are doing that in response to user requests.  This really sounds like more of an application issue or possibly a query tuning issue.

When a remote application connects to a QZDASOINIT job, there are a set of  SQL query parameters assigned.  One of these rules is QUERY_TIME_LIMIT, which determines if a particular SQL statement is allowed to run, based on the optimizer's estimate of runtime.  For example, if you set the QUERY_TIME_LIMIT to 60 seconds, and the optimizer estimates that a given query is going to run for 61 seconds, the QZDASOINIT job would return an error to the application requesting the long-running query.  

Unfortunately, some poorly-designed applications aren't set up to handle these sort of errors, and may crash.  Also, some applications don't offer a simple way to configure QUERY_TIME_LIMIT, though it is usually possible to work-around that problem with proper system setup.

I do IBM i performance consulting for a living, and I've run into this problem and had to fix it many times.

The key to solving this is to determine what applications are generating the expensive, long-running requests, and to then determine how to fix the underlying problem:

Modify the applications involved,
Train users to run less expensive queries,
Enforce QUERY_TIME_LIMIT for these jobs,
Optimize the SQL,
Build needed indexes, and/or
Purge large tables

IBM provides lots of tools to help with this analysis - especially IBM i DB2 SQL Performance Monitor, the SQL Plan Cache, and the Index Advisor.  In the hands of a knowledgeable IBM i DBA/ performance analyst, these tools will help you isolate the problem and may offer quick solutions.
IBM System i
IBM System i

IBM i, formerly known as AS/400, is a proprietary, midrange, object-based operating and application system. It includes a relational database, a menu-driven interface, support for multiple users, block-oriented terminal support and printers, and supports security, communications, and web-based application servers including Websphere, Apache and Tomcat.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo