2946987 - IBM Datastage job runs slow sometimes - SAP ASE 16.0

• 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
...
FROM
   mydb..t1 p
   INNER JOIN
      mydb..t2 r
      ON
         p.col1_pk = r.col1_pk
         AND r.col_id = 123
         AND '2019-12-16' BETWEEN p.from_date AND p.thru_date
INNER JOIN
   mydb..t3 id
   ON
       p.col1_pk = id.col1_pk
       AND id.col_type = 'MYID'
       AND id.col_value = '12345678'
INNER JOIN
   mydb..t4 a
   ON
      a.col2_pk=p.col2_pk

• 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