IC80494: UNCLEAR OUTPUT OF ONSTAT -G SQL WHEN TEMPORARY TABLES ARE DROPPED AND CREATED WITHIN A STORED PROCEDURE

APAR status

• Closed as program error.

Error description

• When you drop and create temporary tables within a stored
  procedure, the onstat -g sql shows unclear output in the list of
  temporary tables.
  
  Example for the stored procedure:
  
  create procedure xx()
     drop table t1;
     select first 1 * from sysdefaults into temp t1;
     drop table t1;
     select first 1 * from sysprocedures into temp t1;
  end procedure;
  
  After you execute this stored procedure twice the onstat -g sql
  shows:
  
  Sess       SQL            Current            Iso Lock       SQL
  ISAM F.E.
  Id         Stmt type      Database           Lvl Mode       ERR
  ERR  Vers  Explain
  47         -              test               NL  Not Wait   0
  0    9.24  Off
  
  Last parsed SQL statement :
    execute procedure xx()
  
  User-created Temp tables :
    partnum  tabname            rowsize
    200043   t1                 5274
    200043   t1                 264
    200043   t1                 5274
  
  When you drop the stored procedure and recreate it within the
  session before you execute it again, the onstat -g sql shows:
  
  Sess       SQL            Current            Iso Lock       SQL
  ISAM F.E.
  Id         Stmt type      Database           Lvl Mode       ERR
  ERR  Vers  Explain
  51         -              test               NL  Not Wait   0
  0    9.24  Off
  
  Last parsed SQL statement :
    execute procedure xx()
  
  User-created Temp tables :
    partnum  tabname            rowsize
    200043   t1                 5274
    200043   t1                 264
    200043   t1                 5274
    200043   t1                 264
    200043   t1                 500
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * IDS-11.50.xC9                                                *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Unclear output of onstat -g sql when temporary tables are    *
  * dropped and created within a stored procedure                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS-11.50.xC10.                                   *
  ****************************************************************
  

Problem conclusion

• Fix is done for IDS-11.50.xC10 and future releases.
  

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

• RB50 PSN

     UP