SAP Knowledge Base Article - Preview

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

Symptom

  • 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.


Read more...

Environment

  • SAP Adaptive Server Enterprise (ASE) 16.0 SP02 PL04 HF1
  • IBM ODBC driver 7.16 

Product

SAP Adaptive Server Enterprise 16.0

Keywords

statistics, covering, index,  literal, parameterization, literal_autoparam, autoparam , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem

About this page

This 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.