- 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 ONE Support launchpad (Login required).
Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.