University of Connecticut Computer Center
UCC DDD Letter #71
Index UConnWeb UCC DDD Letters

Resourse Limit Facility Implementation

Date: December 19, 1997

Reformatted: December 19, 1997

NON-TECHNICAL SUMMARY
We now offer the ability to access DB2 data with a variety of tools, including FOCUS applications and the data warehouse clients. With this flexibility comes the possibility that remotely-submitted, resource-intensive queries can seriously impact the overall performance of the central system. Therefore, to ensure the availability and stability of the OS/390 environment, we are implementing a Resource Limit Facility (RLF) that will terminate long-running queries which exceed a resource consumption threshold.

TECHNICAL DETAIL
On Tuesday, January 6th, the Resource Limit Facility will be implemented for some DB2 queries. The purpose of the Resource Limit Facility is to prevent user queries, particularly run-away queries, from severely impacting other users and applications running on the OS/390 operating system*.

Initially, resource limits will be implemented for distributed DB2 users**. This group currently includes FOCUS users accessing application data stored in DB2 from the VM operating system, and BRIO/QUERY users accessing Data Warehouse data in DB2.

When the Resource Limit Facility is implemented, any DB2 query from a distributed user that exceeds 4 minutes of DB2 CPUTIME will end immediately with a -905 error code. There is no direct correlation between the elapsed time of a query and the DB2 CPUTIME but, in general, the DB2 CPUTIME is a subset of elapsed time.

When a query ends because resource limits were exceeded, the exact error message seen by the user varies depending on the user's environment, but will always include the -905 SQLCODE and/or 57014 SQLSTATE. For example, the error message received by a FOCUS user on VM looks like this: 

   (FOC1400)  SQLCODE IS  : -905/FFFFFC77
   (FOC1495)  EXECUTION ENDED DUE TO RESOURCE LIMIT VIOLATION
   (FOC1407)  SQL FETCH CURSOR ERROR.  : TABLES

The error message for a BRIO/QUERY user looks like this: 

   SQLSTATE: 57014. Error Msg: (IBM)(CLI Driver)(DB2)
   SQL0969N  There is no message text corresponding to SQL
   error -905. in the message file on this workstation.

An analysis of our performance data shows that virtually all queries will complete successfully within the allotted time. In the few cases where queries exceeded the limit in the recent past, we determined that the query was incorrectly coded.

If you experience a Resource Limit error, you should first check the query to be sure it is correct. If it appears to be coded correctly, you may request help by calling the Computer Center Help Desk (486-HELP/4357). The Help Desk can direct you to an experienced programmer who can help you improve your query performance or determine if an exception to the RLF rule is warranted.

  • * (OS/390 is the operating system where all CICS and DB2 applications and batch work execute. It was formerly known as MVS.)
  • ** (Distributed DB2 users are those users accessing DB2 data from other than an OS/390 or CICS platform).

    A copy of this communication will be viewable on the Computer Center Policies web page at the following address: http://vm.uconn.edu/~wwwucc/uccpols.html.

    Go to Top
    Page last updated on 12/19/97.
    Mail comments to: wwwucc@uconnvm.uconn.edu