LI74148: DB2 INSTANCE MAY ABEND EXECUTING A QUERY PREFIXED WITH THE '.DB2SERVICE' KEYWORD.

DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• A query prefixed with the DB2 support '.db2service' keyword may
  abend the instance if any of the tables used in the query has a
  dependent statistical view defined.
  
  An example of such a scenario follows:
  
  -- Table DDL
  CREATE TABLE T1 (C1 INT);
  CREATE TABLE T2 (C1 INT);
  
  -- View DDL
  CREATE VIEW STATVIEW AS (SELECT T1.C1 AS C1, T2.C1 AS C2 FROM
  T1, T2 WHERE T1.C1 = T2.C1);
  
  -- Convert view into a statistical view
  ALTER VIEW STATVIEW ENABLE QUERY OPTIMIZATION;
  
  -- Query that will abend the instance
  .db2service SELECT T1.C1 AS C1, T2.C1 AS C2 FROM T1, T2 WHERE
  T1.C1 = T2.C1;
  
  The stack trace from such an abend will look something like:
  sqlnq_mchFailList::findElem
  sqlnq_mchFailList::addElem
  sqlnq_qur::add_matchable_MQT_SV
  sqlnq_som::compute_matchinfo_opt
  sqlnr_compute_matchinfo
  sqlnr_optprep
  sqlnr_optprep_action
  sqlnr_comp
  sqlnr_seq
  sqlnr_rcc
  sqlnr_exe
  sqlnx_menuproc
  sqlnx_drv
  sqlnn_cmpl
  ...
  
  This problem will be first fixed in DB2 Version 9.1 Fix Pack 8
  and DB2 Version 9.5 Fix Pack 5.
  

Local fix

• No feasible workaround exists aside from:
  1) Not using the '.db2service' keyword
  2) Disabling any dependent statistical views of tables used in
  the query for query optimization. For the aforementioned query,
  this may be done like so:
  
  -- Revert view from a statistical view to a normal view
  ALTER VIEW STATVIEW DISABLE QUERY OPTIMIZATION;
  

Problem summary

• Problem Description:
  
  DB2 INSTANCE MAY ABEND EXECUTING A QUERY PREFIXED WITH THE
  '.DB2SERVICE' KEYWORD.
  Problem Summary:
  A query prefixed with the DB2 support '.db2service' keyword may
  abend the instance if any of the tables used in the query has a
  dependent statistical view defined.
  
  An example of such a scenario follows:
  
  -- Table DDL
  CREATE TABLE T1 (C1 INT);
  CREATE TABLE T2 (C1 INT);
  
  -- View DDL
  CREATE VIEW STATVIEW AS (SELECT T1.C1 AS C1, T2.C1 AS C2 FROM
  T1, T2 WHERE T1.C1 = T2.C1);
  
  -- Convert view into a statistical view
  ALTER VIEW STATVIEW ENABLE QUERY OPTIMIZATION;
  
  -- Query that will abend the instance
  .db2service SELECT T1.C1 AS C1, T2.C1 AS C2 FROM T1, T2 WHERE
  T1.C1 = T2.C1;
  
  The stack trace from such an abend will look something like:
  sqlnq_mchFailList::findElem
  sqlnq_mchFailList::addElem
  sqlnq_qur::add_matchable_MQT_SV
  sqlnq_som::compute_matchinfo_opt
  sqlnr_compute_matchinfo
  sqlnr_optprep
  sqlnr_optprep_action
  sqlnr_comp
  sqlnr_seq
  sqlnr_rcc
  sqlnr_exe
  sqlnx_menuproc
  sqlnx_drv
  sqlnn_cmpl
  ...
  
  This problem will be first fixed in DB2 Version 9.1 Fix Pack 8
  and DB2 Version 9.5 Fix Pack 5.
  
  Users Affected:
  All
  

Problem conclusion

• Problem was first fixed in Version 9.1 Fix Pack 8
  

Temporary fix

• No feasible workaround exists aside from:
  1) Not using the '.db2service' keyword
  2) Disabling any dependent statistical views of tables used in
  the query for query optimization. For the aforementioned query,
  this may be done like so:
  
  -- Revert view from a statistical view to a normal view
  ALTER VIEW STATVIEW DISABLE QUERY OPTIMIZATION;
  

Comments

APAR Information

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

  LI74147

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

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R910 PSY

     UP