• Admin-Scout-small-Banner
           
    CURSOR Admin-Scout
    get the ultimate tool for Informix
    pfeil  
invispix 10x10
invispix 10x10
Latest versionsfixlist
14.10.xC8 FixList
12.10.xC15.X5 FixList
11.70.xC9.XB FixList
11.50.xC9.X2 FixList
11.10.xC3.W5 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

Informix - Problem description

Problem IT37479 Status: Closed

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

product:
INFORMIX SERVER / 5725A3900 / E10 - 
Problem 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

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  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((: 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((: 129
        (select c.col1
          from c, ch
          where (and (and (= c.jcol1, ch.jcol1), (=
ch.const_col1, "SOMECONST")), (>= ( to_date, col_dt,
"%m/%d/%Y"), proc_date)))) )  as rtab_26991
      where (: -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
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.                        *
****************************************************************
Local Fix:
Solution
Workaround
****************************************************************
* USERS AFFECTED:                                              *
* Users of Informix Server prior to 14.10.xC7.                 *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Informix Server 14.10.xC7.                        *
****************************************************************
Comment
Fixed in Informix Server 14.10.xC7.
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
01.07.2021
01.11.2021
01.11.2021
Problem solved at the following versions (IBM BugInfos)
Problem solved according to the fixlist(s) of the following version(s)
invispix 10x10
invispix 10x10
invispix 10x10
Technical InfoTechnical Info

Technical Info

DocumentationDocumentation

Documentation

IBM NewsletterIBM Newsletter

IBM Newsletter

Current BugsCurrent Bugs

Current Bugs

Bug ResearchBug Research

Bug Research

Bug FixlistsBug Fixlists

Bug Fixlists

Release NotesRelease Notes

Release Notes

Machine NotesMachine Notes

Machine Notes

Release NewsRelease News

Release News

Product LifecycleProduct Lifecycle

Lifecycle

Media DownloadMedia Download

Media Download

invispix 10x10
Service und SupportService und Support

Service and Support

Informix MonitoringInformix Monitoring

Informix Monitoring

Admin-Scout for InformixAdmin-Scout for Informix

Admin-Scout for Informix

Informix News & InfosInformix News & Infos

Informix News & Infos