Oppolzer - Informatik / Blog

If I understand your post right, you are telling us, that both variants (TS scan and
N type index scan - with IN list on CLIENT NO) have abended due to limits
on work file usage.

I don't understand this, because from the original SQL and from
the EXPLAIN output I see no reason for work file usage.

Three questions:

- the work file limits seem to be unreasonably low; time to talk to your DB2 people?

- are you sure that the SQL in production is the same as the one in test,
or did you add an ORDER BY etc., BTW?

- is it possible to get an EXPLAIN output (complete, with ORDER columns)
from production?

Thank you, kind regards

Bernd



Am 05.01.2015 um 02:08 schrieb T.W.:
>
> Looking back at log messages they didn't get any output back because of the 00C reason.
>
> We'll almost have to add a WHERE for the CLIENT_NO to the SQL and break it up into 3 SQL statements.
>
> 00C90305
>
>  Explanation
>  The limit on the space usage of the work file storage by an agent was
>  exceeded. The space usage limit is determined by the zparm keyword MAXTEMPS.
>
>  System action
>  The SQL statement was terminated with an SQLCODE -904 and this reason code.