If a limit clause is included in the Query Processor configuration file, then each time an SQL query is run, the database operations performed during that query are counted. If the number of operations exceeds the stated limit, an error occurs. A database operation is equivalent to one retrieval of a row in an SQL table, regardless of whether the retrieved entity is selected.

For example, consider the following query:

SELECT NAME FROM PERSON WHERE AGE = 60

If this query reads 1000 entities and selects 10, then the operation count is 1000. Because of the high-performance method of executing a linear search, the check for exceeding the operations limit is performed when a record is selected or at 5-second intervals, whichever occurs first. Depending upon the system load, the operation count can exceed the query limit by a considerable number before the query is stopped.

For update queries, the retrievals are counted, not the updates.

For example, consider the following query:

UPDATE STUDENT (ADVISOR = 1234) WHERE STUDENT_NO > 1106

If this query reads 500 entities and modifies 6, then the operation count is 500.

In a nested query, the retrievals at all levels are counted. An example of this query would be as follows:

SELECT * FROM CUSTOMER WHERE CUSTOMER.SALESMAN ID IN (SELECT ID
  FROM SALESMEN WHERE REGION = 'EAST')

If this query reads 10 rows from SALESMEN for each of 60 customers and selects 15 customers, then the operation count is 600.

When the Limit Is Set and Tested

The query limit for the current program is obtained when the open query driver call is made. If a limit is changed while a program is running, the new value does not take effect until the program is terminated and started up again.

When a retrieval or an update operation is executed that causes the limit to be exceeded, the following error message appears:

You have exceeded the database operations limit <nnn> requested
by your administrator.

Effect of Query Limit Error

If a query limit error occurs on a retrieval call that is being repeated to obtain all the selected data, then all the data returned to the application by the earlier calls is available to the application. If the query limit error occurs on an update statement, no update is performed.

The following sample Query Processor configuration file illustrates the uses of the limit clause:

RELEASE 160 (PACK = SYS160)
     SL MCPSQLADMIN = (GEORGE)SYSTEM/MCPSQL/ADMIN ON USER1
USER
     FRED,
     GEORGE,
     MARY     LIMIT = 8000     160
PROGRAM
     (FRED)OBJECT/REPORTPROG ON USER1,
     (MARY)OBJECT/CLIPROG ON USER2
     LIMIT UNLIMITED
DEFAULT     LIMIT = 1000  RELEASE = 160

With the preceding Query Processor configuration file, queries are limited to 1000 operations each. However, the following exceptions to this limit are defined: