IT37479: USING TRACE ON IN A STORED PROCEDURE WITH SQL STATEMENT HAVING EXCEPT CLAUSE CAN CAUSE SEGV ASSERTIONS

APAR status

• Closed as program error.

Error description

• If you have a stored procedure where TRACE has been enabled and
  certain kinds of SQL statements may trigger assertion failures.
  
  Here is stack trace for the asserting thread:
  
  afstack
  afhandler
  affail_interface
  mt_ex_throw_sig
  afsig_handler
  <signal frame>
  elist_trace
  elist_trace
  slist_trace.part.6
  elist_trace
  elist_trace
  elist_trace
  slist_trace.part.6
  qblock_trace
  runproc
  udrlm_spl_execute
  udrlm_exec_routine
  udr_execute
  ip_fetch
  getrow
  fetchrow
  exfetch
  sql_nfetch
  sq_nfetch
  sqmain
  spawn_thread
  th_init_initgls
  startup
  
  In the af file, in the onstat -g ses <session id> portion, you
  should see that the onstat -g ses command reports "changing data
  structure forced command termination", which indicates that the
  onstat command also encountered a SEGV when it was attempting to
  report the current SQL being run in the stored procedure, like
  this:
  
  /product/1410FC6_IBM/bin/onstat -g ses 40:
  
  IBM Informix Dynamic Server Version 14.10.FC6DE -- On-Line -- Up
  00:00:41 -- 175748 Kbytes
  2021-06-29 11:25:05
  
  session           effective
  #RSAM    total      used       dynamic
  id       user     user      tty      pid      hostname
  threads  memory     memory     explain
  40       user1  -         38       16291    host1 1        94208
  90768      off
  ...
  Current SQL statement (2) :
    execute procedure proc1(?,?)
  
  Host variables :
     address            type       flags value
     -----------------------------------------
     0x000000004db3c330 CHAR       0x000 06/01/20
     0x000000004db3c3c0 INT        0x000 8
  
  Stored procedure stack :
  
     context            proc-counter       opcode name
  
  ----------------------------------------------------------------
  --
     0x000000004db37858 0x4d81ae68+0x0030 CURSOR     db1:proc1
  
  Current SQL statement (2) in procedure db1:proc1
     proc-counter 0x4d81ae68 opcode CURSOR
  
  select *
    from  table((<unknown op>: 129
      (select c.col1
        from c , r
        where (and (= c.filtcol1, proc_variable), (= c.col1,
  r.col1)))) )  as ltab_19414
    where (not (exists
      (select *
        from  table((<unknown op>: 129
          (select c.col1
            from c, ch
            where (and (and (= c.jcol1, ch.jcol1), (=
  ch.const_col1, "SOMECONST")), (>= (<procedure> to_date, col_dt,
  "%m/%d/%Y"), proc_date)))) )  as rtab_26991
        where (<unknown op>: -515 Changing data structure forced
  command termination.
  
  The above SQL is a transformation of what happens when the SQL
  statement uses an EXCEPT clause, so in the SPL code,  SQL
  statement looks like this:
  
  foreach
              SELECT c.col1 into procvar2 FROM  c, r
              WHERE c.filtcol1  = proc_variable
              AND c.col1 = r.col1
              EXCEPT
              SELECT {+INDEX(ch, idx1)} c.col1 FROM c, ch
              WHERE c.jcol1 = ch.jcol1
              AND ch.const_col1 = 'SOMECONST'
              AND TO_DATE(col_dt, "%m/%d/%Y") >= proc_date
  
  end foreach
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 14.10.xC7.                 *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Informix Server 14.10.xC7.                        *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 14.10.xC7.
  

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