IC66695: SET EXPLAIN REPEATS THE UDR STATISTICS INFORMATION FOR EACH ROW RETURNED BY THE QUERY

APAR status

• Closed as program error.

Error description

• Set explain repeats the UDR statistics information for each row
  returned by the query.  The only thing that changes is the
  statistics throughout the repetitions.  It could show the UDR
  statistics just once in the explain output.
  
  The following test case shows the actual behavior :
  
  create database d1;
  
  create table t1 (c1 int);
  insert into t1 values (1);
  
  create table t2 (c1 int);
  insert into t2 values (1);
  insert into t2 values (2);
  insert into t2 values (3);
  
  create function f1(p1 int) returning int;
    define v1 int;
    select c1 into v1 from t1 where c1 = p1;
    return v1;
  end function;
  
  set explain on;
  
  select *, f1(1) from t2;
  
  sqexplain.out  shows :
  
  QUERY: (OPTIMIZATION TIMESTAMP: 02-25-2010 11:30:03)
  ------
  select *, f1(1) from t2
  
  Estimated Cost: 2
  Estimated # of Rows Returned: 1
  
    1) informix.t2: SEQUENTIAL SCAN
  
  UDRs in query:
  --------------
      UDR id  :   386
      UDR name:   f1
  
  ----------
  Procedure: informix.f1
  Statement id: 0
  
  Query statistics:
  -----------------
  
    Table map :
    ----------------------------
    Internal name     Table name
    ----------------------------
    t1     t1
  
    type     table  rows_prod  est_rows  rows_scan  time
  est_cost
  
  ----------------------------------------------------------------
  ---
  
    scan     t1     1          1         1          00:00.00   2
  
  
  
  ----------
  Procedure: informix.f1
  Statement id: 0
  
  Query statistics:
  -----------------
  
    Table map :
    ----------------------------
    Internal name     Table name
    ----------------------------
    t1     t1
  
    type     table  rows_prod  est_rows  rows_scan  time
  est_cost
  
  ----------------------------------------------------------------
  ---
  
    scan     t1     2          1         2          00:00.00   2
  
  
  
  ----------
  Procedure: informix.f1
  Statement id: 0
  
  Query statistics:
  -----------------
  
    Table map :
    ----------------------------
    Internal name     Table name
    ----------------------------
    t1     t1
  
    type     table  rows_prod  est_rows  rows_scan  time
  est_cost
  
  ----------------------------------------------------------------
  ---
  
    scan     t1     3          1         3          00:00.00   2
  
  
  
  Query statistics:
  -----------------
  
    Table map :
    ----------------------------
    Internal name     Table name
    ----------------------------
    t1     t2
  
    type     table  rows_prod  est_rows  rows_scan  time
  est_cost
  
  ----------------------------------------------------------------
  ---
  
    scan     t1     3          1         3          00:00.00   2
  
  
  Note : Additionally, it repeats exactly the same data for the
  last row.
  
  Set Explain of only the UDR shows:
  
  set explain on;
  execute function f1(1);
  
  (expression)
  
    1
  
  1 row(s) retrieved.
  
  sqexplain.out
  =============
  
  ----------
  Procedure: informix.f1
  Statement id: 0
  
  Query statistics:
  -----------------
  
    Table map :
    ----------------------------
    Internal name     Table name
    ----------------------------
    t1       t1
  
    type     table  rows_prod  est_rows  rows_scan  time
  est_cost
  
  ----------------------------------------------------------------
  ---
    scan     t1      1          1         1         00:00.00     2
  
  
  If the t2 table returns 2 million rows, the sqexplain.out will
  be about 1GB in size.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users has UDRs in the query and has EXPLAIN_STAT set to 1    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Query stat printed out for each row returned from the query. *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.50.xC7 and above.                              *
  ****************************************************************
  

Problem conclusion

• Fixed in 11.50.xC7 to only print query stat for UDR at the end
  of the stmt execution.
  

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

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP

• RB15 PSY

     UP