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,
Matthew
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
Matthew,
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.
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.
Matthew,
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.
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.
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.