IT27612: PDQ QUERY EXECUTED USING NESTED LOOP JOIN ON TIME SERIES VIRTUALTABLE MAY CONSUME EXCESSIVE RESOURCES

APAR status

• Closed as program error.

Error description

• Running a query with PDQ enabled and whose execution plan
  involves a nested loop join with a Time Series virtual table
  whose base table is fragmented results in a large number of
  sessions being created with each having a single thread named
  ts_vtam_pdq. The majority of these sessions look to perform some
  work and then remain idle until the query execution has
  completed.
  
  The sqexplain.out includes:
  
  SELECT
  aq_data_v.timestamp, aq_data.station_id,
    SUM(aq_data_v.pred_value),
    SUM(aq_data_v.obs_value),
    SUM(aq_data_v.inter_obs_value)
  FROM aq_data_v aq_data_v
  INNER JOIN aq_data aq_data
    ON aq_data_v.location_type = aq_data.location_type
    AND aq_data_v.station_id = aq_data.station_id
    AND aq_data_v.subdomain_id = aq_data.subdomain_id
    AND aq_data_v.sensor_parameter_code =
  aq_data.sensor_parameter_code
  WHERE aq_data_v.timestamp
    BETWEEN '2018-05-13 00:00:00' and '2018-05-13 23:59:59'
  GROUP BY aq_data_v.timestamp, aq_data.station_id
  
    1) informix.aq_data: SEQUENTIAL SCAN  (Parallel, fragments:
  ALL)
  
    2) informix.aq_data_v: VTI SCAN  (Parallel, fragments: ALL)
  
          VTI Filters:
  (informix.equal(informix.aq_data_v.location_type,informix.a
  q_data.location_type ) AND
  informix.equal(informix.aq_data_v.station_id,informix
  .aq_data.station_id ) AND
  informix.equal(informix.aq_data_v.subdomain_id,informi
  x.aq_data.subdomain_id ) AND
  informix.equal(informix.aq_data_v.sensor_parameter_
  code,informix.aq_data.sensor_parameter_code ) AND
  informix.lessthanorequal(infor
  mix.aq_data_v.timestamp,datetime(2018-05-13 23:59:59.00000) year
  to fraction(5)
  ) AND
  informix.greaterthanorequal(informix.aq_data_v.timestamp,datetim
  e(2018-05-
  13 00:00:00.00000) year to fraction(5) ))
  NESTED LOOP JOIN
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of IDS prior to 12.10.xC13.                            *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * PDQ query executed using nested loop join on Time Series     *
  * virtual table may consume excessive resources.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  

Problem conclusion

• Fixed in IDS 12.10.xC13.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels