- The IBM Datastage job executes dynamically built SQL in SAP ASE 16.0 which is accessed via IBM ODBC driver 7.16.
- This job runs slow sometimes:
- When the job runs slow, sp_who shows its process (spid) executes the 'fetch cursor' command and each loop takes about 1-60 seconds.
- When the job runs fast, the 'fetch cursor' command is not seen in sp_who.
- The SQL in question is a SELECT joining 4 tables using INNER JOINs:
SELECT TOP 1
p.col1_pk = r.col1_pk
AND r.col_id = 123
AND '2019-12-16' BETWEEN p.from_date AND p.thru_date
p.col1_pk = id.col1_pk
AND id.col_type = 'MYID'
AND id.col_value = '12345678'
- The query plan collected when the query runs slow shows:
- forward index scans are used for all 4 tables
- I/O Size 4 Kbytes is used for index leaf for all 4 indexes
- I/O Size 4 Kbytes is used for data pages for 3 tables (a covering index is used for mydb..t3, so data pages are not read)
Note: This is a 4K-page ASE server.
- Enabling dynamic SQL plan pinning (setting "dynamic SQL plan pinning" to 1) does not help.
- SAP Adaptive Server Enterprise (ASE) 16.0 SP02 PL04 HF1
- IBM ODBC driver 7.16
statistics, covering, index, literal, parameterization, literal_autoparam, autoparam , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem
About this pageThis is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).
Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.