Query timeout due to use of ZIIP processors ?

[marco_mica]

We have users running a planning transaction SAPLRSBOLAP_BICS_CONSUMER from EP to BW.

The query generated by this transaction times out after 600 seconds. When we look at the data about the query in DB2 -> statement Cache, we see Elapsed time = 197.5 sec, CPU time = 0.5 sec, 'other wait time' = 197 sec. Using RMF we see a huge amount of activity on the ZIIP processors while the query is running.

If I submit the identical query from SPUFI directly on the same DB2 subsystem, it completes succesfully within 2 sec.

Any suggestion on why the query would be executed differently when submitted from SAP than when running from TSO ?

[marco_mica]

RUNSTATS with INDEX ALL KEYCARD turned out to be the solution to the problem. After doing this, the performance of the query from SAP is better than the one from spufi. Now runs in 0.2 sec vs. 18 minutes.

[thomas_vogt]

KEYCARD option is always used when RUNSTATS is triggered from SAP (DB13, BW).

Regards,

Thomas

[marco_mica]

What we see it that Db2 seems to pick a different access path when receiving the query from SAP compared to directly executing the query from SPUFI. We have opened a note in the mean time to get support in order to help resolve the issue. (- message https://service.sap.com/sap/support/message/E/002007974700004918832008 with SAP).

[thomas_vogt]

Seems to be a problem of parallel processing since SPUFI is using SJ but not the call from SAP. In SAP SET CURRENT DEGREE = ANY is performed before issueing the SQL statement.

I have contacted the ticket processor.

Regards,

Thomas

[thomas_vogt]

Actually both processor types are identical. Both statements might use different access paths for any reason, but it's just a guesss without knowing any details on the statement since we never had similar problems.

Regards,

Thomas