IT33796: VIEW NOT RESPECTING SUBQUERY THEREFORE PRODUCING TOO MANY ROWS WHICH LEADS TO LONGER RUNTIME OF THE QUERY

APAR status

• Closed as program error.

Error description

• A view used in a select with a subquery does not apply subquery
  when being created and therefore producing to many rows
  
  Example:
  
  select
  ...
  where test_view.serial_vk in (select my_serial_1 from my_tab_1
  where ....)
  
  subquery is applied when using the view (which is slow)
  
  3) (Temp Table For View): SEQUENTIAL SCAN
  
  Filters:
  Table Scan Filters: (Temp Table For View).serial_vk = ANY
  <subquery>
  
  
  subquery is applied when creating the view (which is fast)
  
    1) informix.my_tab_2: INDEX PATH
  
      (1) Index Name: informix.my_ind_2
          Index Keys: my_serial_2 (Key-Only) (Serial, fragments:
  ALL)
          Lower Index Filter: informix.my_tab_2.my_serial = ANY
  <subquery>
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 12.10.xC15 and 14.10.xC5.  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Informix Server 12.10.xC15 (when available) or    *
  * 14.10.xC5.                                                   *
  ****************************************************************
  This fix removes a previous restriction with SQL_DEF_CTRL bit
  0x40000000, by default it's disabled.
  
  steps to set SQL_DEF_CTRL
  1) get the current SQL_DEF_CTL value
      onstat -g cfg SQL_DEF_CTRL
  name                      current value
  SQL_DEF_CTRL              0x44004b0
  
  2) the new value to enable that bit
  SQL_DEF_CTRL  |  0x40000000 =  0x444004b0
  
  3) set with onmode or in ONCONFIG
  onmode -wf SQL_DEF_CTRL=0x444004b0
  

Problem conclusion

• Fixed in Informix Server 12.10.xC15 and 14.10.xC5.
  

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