Re: Read triggers and SQL CQE or SQE

Hi Peter,

I would invest some time to research and change the programs to use SQE.
There is a finite list of scenarios that dictate which query engine will be
used depending on the version of OS you are on.

You might find setting IGNORE_DERIVED_INDEX to *YES in the QUSRSYS/QAQQINI
file improves performance if you are querying select-omit LFs.

Some interesting articles:

http://www.ibm.com/developerworks/data/library/techarticle/dm-1001maxsqewithdds/

http://www-01.ibm.com/support/docview.wss?uid=nas8N1013988

http://www-01.ibm.com/support/docview.wss?uid=nas8N1018857



Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Cell: (416) 317-3144


On 3 December 2014 at 19:22, Peter Connell <Peter.Connell@xxxxxxxxxx> wrote:

Hi,
We have a read trigger based audit system to record who, when and why
certain files were accessed because of legal requirements.
It has been running for many years but is now causing issues with jobs
that process a large number of transactions using embedded SQL where each
transaction may launch several nested SQL requests that read files that
have a read trigger attached.

The problem appears to be that SQL decides not to use the more efficient
SQE method in favour or CQE which does things like creating a copy for a
table scan. This is making some jobs horrendously slow.
Redesigning the audit system to not use read triggers is beyond
consideration. Unless there is some workaround that can force the use of
SQE then we are faced with rewriting the SQL function points as native RPG
where they access files that have read triggers.

Does anyone have any suggestions.

Peter