Re: Read triggers and SQL CQE or SQE

[Glenn Gundermann <glenn.gundermann@xxxxxxxxx>]

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