PH28725: BIG SQL QUERY MAY FAIL WITH SQL1229N WHEN ONE OR MORE WORKER NODES IS DOWN

APAR status

• Closed as fixed if next.

Error description

• A Big SQL query may fail with SQL1229N if one or more nodes are
  down. In Big SQL, multi-node
  subsections should be runtime partitioned. In a rare scenario,
  the query's access plan may
  include a subsection that is multi-node, but not flagged as
  runtime partitioned.  That subsection
  will run on a fixed set of nodes, instead of a set of nodes
  dynamically determined by the
  scheduler at runtime. As a result, the query cannot complete if
  one node fails, returning sqlcode
  -1229.
  
  This issue may be seen if the query is accessing complicated
  (i.e. nested) views.  If you run
  EXPLAIN and db2exfmt against the query, in the optimized
  statement you might see an
  outer join with a VALUES expression; for example:
  
  RIGHT OUTER JOIN
  (SELECT
     NULL,
     NULL,
     NULL
   FROM
     (VALUES
     ) AS Q2
  
  and in the access plan, you will see the above outer join that
  the partitioning specifies
  "Replicated Data on all nodes"
  
  i.e
   Input Streams:
   -------------
           1) From Object SYSIBM.GENROW
                   Estimated number of rows:       0
                   Partition Map ID:                       1
                   Partitioning:                   (REPL )
                                                   Replicated Data
  on all nodes
                   Number of columns:              0
                   Subquery predicate ID:          Not Applicable
                   Partition Column Names:
                   ----------------------
                   +NONE
  

Local fix

• 1) Ensure all nodes are up and running.
  2) If a node will not stay up, remove the problematic node from
  the cluster.
  3) Try to run the query with a different optimization level, to
  generate a
  different access plan.
  i.e SET CURRENT QUERY OPTIMIZATION 3
  

Problem summary

• Please see problem description.
  

Problem conclusion

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

Applicable component levels