Re: Run-Away query stops system -- MIDRANGE-L

I would run WRKSYSACT. It is very difficult to know what the SQL jobs are
and why they are running. Would help a lot if you could display the SQL
statement being run by a server job.

On Mon, Nov 12, 2018 at 6:30 PM Don Brown <DBrown@xxxxxxxxxx> wrote:

Yesterday the IBMi became totally unresponsive. It took 20 or 30 attempts
to get a console connection to the system

This is a single system using lan console (No HMC) with V7R3 and very
current on PTF's

The cause of the problem was later identified that a user running a very
bad query. This was discovered when performance started to go south again
and one of the IT people walked around all the departments and found a user
re-running the query that did not finish when the system failed!!

The application has a user tool for creating adhoc queries.
When the user log on there is a batch job created in in a user subsystem.
When the query runs a QZDASOINIT jobs provides the database connection with
user profile QUSER in QUSRWRK

I checked everything I could to see what was chewing up resources and
nothing identified this job.

I believe the joining of tables in this query resulted in many new indexes
being required over files with millions of records.

I would like to identify what could have been done to identify the
responsible job - any suggestions ?

The system CPU usage from wrkactjob was < 10%
Disk % busy was high between 30 - 50 %
System storage was fine at 62%
No relevant messages were on the system operator queue or history log
Displaying the history log resulted in CPD2537 Not all messages logged -
Also pointing to the high disk activity
Wrksysact did not show any abnormal jobs or any high I/O
The full version of system performance tools is not available on this
system.

How could I have identified this job using the available tools ?

While I am still doing a lot of reading I believe there is a way to govern
a query that if it uses more than a preset resource or exceeds a run
duration the system can cancel the query - Has anyone implemented this or
have any relevant links to how this is implemented ?

Appreciate any suggestion or advice.

Don Brown

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com