Each time an SQL statement is prepared, the server estimates the cost of the statement. The application can then decide whether to continue with the execution of the statement.
This estimate can be obtained from the SQLCA ( SQLERRD(4) ), and used by the application directly or the SQL_DB2ESTIMATE connect option can be set to a threshold value. If the estimated cost of any statement exceeds the threshold, DB2 CLI displays a dialog box, with a warning and a prompt to continue or cancel the execution of the statement.
The suggested threshold value is 60000, although in general the application should allow the end user to set the threshold value.
| Note: | The estimate is only an estimate of the total resources used by the server to execute the statement, it does not indicate the time required to execute the statement. |
An estimate of the number of rows in the result is also available from the SQLCA ( SQLERRD(3) ), and could also be used by the application to restrict large queries.
| Note: | The accuracy of the information returned in the SQLERRD(3) and SQLERRD(4) fields is dependent on many factors such as the use of parameter markers and expressions within the statement. The main factor which can be controlled is the accuracy of the database statistics. That is, when the statistics were last updated, (for example, for DB2 Universal Database, the last time the RUNSTATS command was run.) |