Informix giving me lessons from database implementation

I wrote about my problems with Informix IDS several days ago.

That problem is sovled now:

“The number of rows in the table is small enough to cause the database server to perform a sequential scan, and an index scan on a table with lock mode configured to row.”

You can setup IDS to use indexes all time which gets you off my problem. See Informix suport page for further info.

The infromation about locking previous and next record in index was outdated. Informix does not make this since v7.

I will bethink pretty much locking problems with other databases.
We still have problems with deleting data from tables using SQL.

Solution: OPTCOMPIND parameter

OPTCOMPIND is an environment variable or a parameter in the Informix configuration file. The optimizer uses its value to determine its choice of the data access method. It has one of three values (0, 1 and 2), which indicate the following:

  • If the value is set to 0, then the optimizer chooses index scans over table scans if appropriate indexes exist, without even considering the estimated costs.
  • If the value is set to 1, then the optimizer behaves as it does for value 0 if the transaction isolation mode is not Repeatable Read. If the transaction isolation mode is Repeatable Read, then the optimizer would base its choice purely on the estimated costs.
  • If value is set to 2, the optimizer would uses estimated costs to determine an execution plan regardless of the transaction isolation mode.

You may either set OPTCOMPIND either as an environment variable or as a parameter in the configuration file, but setting it as a parameter will take precedence on execution.

Link: Tuning Informix SQL

Leave a Reply

Your email address will not be published. Required fields are marked *