Reversion requirements and limitations

Ensure that all in-place ALTER operations are complete. If the reversion process does not complete successfully because of in-place ALTER operations, the reversion process lists all the tables that have outstanding in-place alter operations. You must resolve outstanding in-place alter operations on each of the tables in the list before you can revert to the older database server. For more information, see Resolve outstanding in-place alter operations.

SELECT tenant_dbsname FROM tenant;

database sysadmin;
execute function task("tenant drop", "mydb");

select idxname from sysindices 
where substring_index(substring_index((indexkeys::lvarchar),'<',-1),'>',1) in 
(select procid from sysprocedures where paramtypes::lvarchar like 'bson' or 
paramtypes::lvarchar like 'bson,%' or paramtypes::lvarchar like '%,bson,%' or 
paramtypes::lvarchar like '%,bson') and 
amid = (select am_id from sysams where am_name = 'rtree'); 

select idxname from sysindices 
where substring_index(substring_index((indexkeys::lvarchar),'[',-1),']',1) 
in (select opclassid from sysopclasses 
where opclassname in ('bts_json_ops','bts_bson_ops','bts_longlvarchar_ops')); 

select fieldname from sysattrtypes 
where type = 40 and extended_id in ( 
  select extended_id from sysxtdtypes 
  where name in ( 
    select substring_index(substring_index((description::lvarchar),'(',-1),')',1) 
    from sysxtddesc 
    where extended_id in ( 
      select extended_id from sysxtdtypes 
      where type = ( select type from sysxtdtypes 
                     where name = 'timeseries') 
))); 

SELECT id FROM tsinstancetable WHERE bitand(flags, '16') = 16;

Then delete the rows with packed elements from the time series table.

 SELECT * FROM tsinstancetable WHERE id > 2147483647;

If the server does not have the space for allocating extents for new external and empty tables, you must drop the objects before reverting. Similarly, before you revert to version 11.50.xC5 or earlier versions, you must drop all external tables. The SYSEXTERNAL, SYSEXTCOLS, and SYSEXTDFILES system catalog tables will be dropped automatically during reversion.

SELECT a.tabname FROM systables a, systabamdata b
 WHERE LENGTH(b.am_param) > 256 AND a.tabid = b.tabid; 

• A SELECT statement that returns qualifying rows of the original table to a temporary table
• A DELETE or INSERT statement that modifies the original table by inserting or deleting rows that match rows in the temporary table

ANSI joins in distributed queries: Distributed queries that use ANSI-compliant LEFT OUTER syntax for specifying joined tables and nested loop joins run more efficiently in Version 10.00.UC4 than in earlier releases. This occurs through sending the query to each participating database server for operations on local tables of those servers. If you revert from Version 10.00.UC4 or later to an earlier release that does not support this implementation of the ANSI-compliant syntax, such queries might show reduced performance, because the database server instance from which the query originates will perform the joins locally.

The INDEX_SJ and AVOID_INDEX_SJ optimizer directives: When queries explicitly use the new INDEX_SJ and AVOID_INDEX_SJ optimizer directives, these directives have no effect when the query runs. You must run UPDATE STATISTICS on stored procedures to force re-compilation of stored procedures.

In addition, reversion removes the effect of these directives in SAVE EXTERNAL DIRECTIVES statements and on output from the SET EXPLAIN statement. If you revert to a version of the database server that supports the sysdirectives system catalog table, but does not support the AVOID_INDEX_SJ or INDEX_SJ directives, user informix must delete any active row of sysdirectives that includes AVOID_INDEX_SJ or INDEX_SJ in the directives column.