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,

Matthew RoessnerSenior Systems ProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carlos IjalbaIT Systems DirectorCommented:
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.
Gary PattersonVP Technology / Senior Consultant Commented:

There is really no such thing as a "stagnant" job.  

Are you just talking about QZDASOINIT jobs, specifically, or do you have other problem jobs?   It is perfectly normal to have a number of these jobs started and in a running state for extended periods, depending on your prestart job configuration.  

QZDASOINIT jobs are used to service remote database requests.  The system is generally configured to start a number of these jobs, and to maintain an adequate pool of running QZDASOINIT jobs to service incoming remote database requests.  These jobs are also configured to service a certain number of SQL requests before shutting down and starting a "fresh" job.

Arbitrarily shutting them down can cause remote jobs to fail, can impact the performance of remote programs that depend on these jobs, and can impact the overall performance of your system.

QZDASOINIT job behavior can be customized - number of jobs to prestart, number of requests to service before shutting down, etc.

Happy to provide more info.  

What OS version are you running?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
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?
Carlos IjalbaIT Systems DirectorCommented:

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.
Gary PattersonVP Technology / Senior Consultant Commented:

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.