Blog Content/Topics List

The information provided applies to Informix Server and IWA Version 12.10.FC1 and newer.

The trickle feed functionality to incorporate data inserted to fact tables continuously into an existing data mart is based on triggers: Insert triggers are used for fact tables to collect new data records and transfer them into the appropriate data mart. This method causes little overhead and works on individual fact tables. When enabling or disabling trickle feed for a data mart, the respective triggers on the fact tables must be created or dropped. Typical situations are when a data mart needs to be refreshed or fully re-loaded, operations that are recommended on a regular basis.

Drawback of this trigger based method is, that creating or dropping the triggers are data definition operations that require exclusive access to the tables. For the Informix server exclusive table access means that no other session may access the table concurrently, effectively excluding even dirty read or merely waiting for a table lock by other sessions. In practise this may be difficult to achieve, because even having the table locked exclusively does not prevent other sessions from dirty read or waiting to acquire a lock on this same table. As a result, enabling or disabling trickle feed may fail due to missing exclusive table access for the underlying trigger operations.

A possible solution to this potential problem is the use of undocumented NONEXCLTRIG. NONEXCLTRIG is both, a configuration variable to be set in the onconfig file of the Informix server instance, as well as an environment variable to be set in the clients environment from where the connection to the database is initiated. This is how it is used:

1. Set the configuration parameter NONEXCLTRIG in the onconfig file of the Informix server instance to one of three values:

   0 :

   Default behavior, same as if not present. Creating and dropping a trigger always requires exclusive table access.

   1 :

   Whether exclusive table access is needed for creating or dropping a trigger depends on the setting of environment variable NONEXCLTRIG in the clients environment.

   2 :

   Creating or dropping a trigger does not require exclusive table access, regardless of the clients environment setting.

   Restart the Informix server instance for the new configuration setting to take effect.

2. If the configuration parameter NONEXCLTRIG is set to value 1, then set the environment variable NONEXCLTRIG in the clients environment before connecting to the database and executing create or drop trigger statements. For example in the Bourne or Korn Shell by:

   NONEXCLTRIG=1
export NONEXCLTRIG

It is important to understand the implications of using NONEXCLTRIG:

• Supported:
  INSERT CURSORS will reload the modified trigger definition on the next PUT operation.
  UPDATE / DELETE WHERE CURRENT will reload the trigger definition prior to execute.

• Limitation:
  EXECUTE of other statements prepared before the trigger change will give an error.
  SELECT cursors will not reload trigger definitions until closed and reopened.

  The Informix server does not attempt to check (e.g. by detecting locks held by the transaction) whether the current transaction has made any updates to the table when the next DML operation detects the change in the table definition. Thereby two operations in the same transaction may have different trigger definitions for the table.

To minimize undesired effects of the limitation described above, the following is recommended when using NONEXCLTRIG is necessary:

• Set the configuration parameter NONEXCLTRIG to value 1 in the onconfig file.
• Set the environment variable NONEXCLTRIG only for the session setting up or removing trickle feed.

In order to ensure consistency of the data in the data mart and avoid errors due to trigger changes:

• Execute the periodic data mart refresh and the subsequent setting up of trickle feed for this data mart in the same database session,
• and in this session lock in share mode all the tables being part of the data mart (as a minimum lock the fact tables) before starting the data mart refresh. Then release these locks only after having set up trickle feed.

This way will avoid missing data in the data mart that is inserted into fact tables between the start of the data mart refresh and the setting up of trickle feed.

Blog Content/Topics List