Understanding Informix 11.70 features with upgrade impact

IBM® Informix®, Version 11.70.xC1 has an abundance of new features, and this article aims to help you achieve a smooth upgrade to Informix 11.70.xC1 and efficiently use these new features. In this article, explore a list of the features that have upgrade impact, and get a brief overview of each feature, with information about how to use the features after upgrade, information about reversion messages (in case you want to revert to your older version), and information about any new onconfig parameters and system catalog changes that were added for the new features.

Neelima Doguparthy (ndogupa@us.ibm.com), Software Engineer, IBM

Neelima Doguparthy photoNeelima Doguparthy is a software engineer at IBM, San Jose, USA. She has been with IBM Informix since 2007. As a test engineer, she worked on conversion/reversion testing, integration testing, and installation testing of Informix.



11 November 2010

Also available in Chinese

Introduction

IBM Informix, Version 11.70.xC1 is a relational database server that allows you to upgrade (convert) from an earlier version of the server (for example, from Versions 7.31, 9.40, 10.00, 11.10, or 11.50 to 11.70). Many new features have upgrade impact, and some new features are enhancements to existing features.

There are a few things that you should know about the features during the process of upgrading, especially if you think you might want to move back to older versions. This article covers the following key points:

  • Short description of the features
  • Examples for each of the features
  • Reversion messages seen when reverting to the old version
  • Any new onconfig parameters added for new features (the onconfig parameters are used to configure the database server)
  • Any new syscatalog changes (the system catalog tables have all the information about database objects)

Prerequisites

This article explains the conversion impact from 11.50.xC7 to Version 11.70.xC1 and provides information for reverting back to Version 11.50.xC7. This document assumes the following is already installed:

  • Informix 11.50.xC7
  • Informix CSDK 3.50.xC7
  • Informix 11.70.xC1
  • Informix CSDK 3.70.xC1

Much of the discussion that follows in this article also applies to releases other than 11.50.xC7.


Features description with examples

The following features are described in this section:

Forest of trees (FOT) index

A FOT index is an index that is similar to a B-Tree index, but an FOT index has multiple root nodes and fewer levels. (B-Tree indexes have only one root node and more levels.) You can think of levels in a FOT index as smaller subtrees (buckets). To retrieve an item from a subtree, you compute a hash value from the column you chose when creating the index. This hash value is then mapped to a bucket for storage or for getting a value of the row.

After successfully from converting Informix 11.50.xC7 to 11.70.xC1, you can use the following command to a create FOT:

Listing 1. SQL syntax for the FOT feature
create index fot_indx on tab_1(col1) hash on (col1) with 1000 buckets;

The command in Listing 1 creates a FOT index on tab_1 table on col1 with 1000 buckets (subtrees). You can create a FOT on a new table created after conversion or on an existing table.

Reversion impact: When you try to revert back to an older version, the following message is displayed, and reversion is cancelled:

Listing 2. Reversion messages
onmode -b 11.50.xC6
Database xdb: Drop FOT index fot_indx before reversion.

Drop the FOT index using the following command:

Listing 3. SQL syntax to drop FOT
drop index  fot_indx;

After dropping the FOT index, the reversion will be successful.

System catalog changes: A FOT index creates a new entry in the existing sysindices table.

Fragment by list and interval

List fragmentation fragments data based on a list of discrete values. This type of fragmentation can help in the logical segregation of data. It is useful when a table has a finite set of values for the fragment key (or partitioning key) and queries on the table have an equality predicate on the fragment key.

After successfully converting from Informix 11.50.xC7 to 11.70.xC1, you can use the following command to create a table with list fragmentation:

Listing 4. SQL syntax for the fragment by list
CREATE TABLE tab_list
(id SERIAL, fname CHAR(32), lname CHAR(32), state CHAR(2), phone CHAR(12)) 
FRAGMENT BY LIST (state)
  PARTITION p0 VALUES ("MA", "NH") IN dbsace0,
  PARTITION p1 VALUES ("AZ", "OR") IN dbspace1,
  PARTITION p2 VALUES ("KY", "OH") IN dbspace2,
  PARTITION p3 VALUES (NULL) IN dbspace3,
  PARTITION p4 REMAINDER IN dbspace3;

The command in Listing 4 creates a table called tab_list that fragments data based on column state using list fragmentation. You can also change the fragmentation scheme of an existing table to list using the ALTER FRAGMENT command.

Interval fragmentation fragments data based on an interval value. This type of fragmentation allows the DBA to define a set of initial fragments and an interval value. The DBA does not have to create all the possible fragments for a table up front. The server automatically creates fragments based on the specified interval value when rows are inserted that do not fit in the initial fragments.

After successfully converting from version 11.50.xC7 to 11.70.xC1, you can use the following command to create table with interval fragmentation:

Listing 5. SQL syntax for the fragment by interval
CREATE TABLE tab_interval (id INTEGER, name CHAR(32), dept CHAR(2), hiredate DATE) 
FRAGMENT BY RANGE (id) 
INTERVAL (100) STORE IN (dbspace1, dbspace2, dbspace3)
 PARTITION p0 VALUES IS NULL IN dbspace0,
 PARTITION p1 VALUES < 250 IN dbspace1,
 PARTITION p2 VALUES < 500 IN dbspace2;
 
INSERT INTO tab_interval (id) VALUES (601);

The command in Listing 5 creates a table called tab_interval that fragments data on column id using interval fragmentation. The table has three initial fragments: p0, p1, and p2. When rows are inserted with values of id columns >= 500, the server creates interval fragments based on the specified interval value of 100 in one of the dbspaces specified in the STORE IN clause. The above insert will result in the creation of an interval fragment with fragment expression >= 600 and < 700. You can also change the fragmentation scheme of an existing table to interval using the ALTER FRAGMENT command. Both list and interval fragmentation can be used to fragment indexes.

Reversion impact: If you have tables fragmented by list or interval, you cannot revert to 11.50.xC7 unless those tables are dropped or the fragmentation scheme of those tables are modified to make them non-fragmented. When you try to revert back to an older version, the following message is displayed, and reversion is cancelled:

Listing 6. Reversion messages
onmode -b 11.50.xC6
Database xdb:Table xuser.tab_list:Drop or alter the fragmentation strategy for Table
before reversion
Database xdb:Table xuser.tab_interval:Drop or alter the fragmentation strategy for Table
before reversion

After the tables are dropped or altered to make them non-fragmented, the reversion will be successful.

Listing 7. Drop the tables for list and interval fragmentation
drop tab_list;
drop tab_interval;

System catalog changes:

  • A new version column is added to sysfragments system catalog table to keep track of changes to statistics information.
  • In addition to a row for each table fragment, the sysfragments system catalog table will have rows for the following information pertaining to list and interval fragmented table or index: Fragment key (or partitioning key), interval value, and dbspace list for interval fragments.
  • List fragmentation is represented by 'L,' and interval fragmentation is represented by 'N' in the strategy column of sysfragments system catalog table.

Fragment-level statistics

This feature helps you to store data distributions per fragment. It allows you to define a data change threshold, and statistics are refreshed only when the data change threshold is reached. Run the update statistics command to create or refresh statistics.

After successfully converting from version 11.50.xC7 to 11.70.xC1, ensure you have a valid smartblob space defined for the SYSSBSPACENAME onconfig parameter, and ensure the space is allocated. Then use the following command to create fragment level statistics:

Listing 8. SQL syntax for the fragment-level statistics
CREATE TABLE  tab_frag_stat
  (
    s_suppkey integer not null,
    s_name char(25),
    s_address varchar(40),
    s_nationkey integer,
    s_phone char(15),
    s_acctbal decimal(12,2),
    s_comment varchar(101)
  )
fragment by expression s_suppkey < 11 in dbspace2,
s_suppkey >= 11 and s_suppkey < 21 in dbspace3,
remainder in dbspace10;

ALTER TABLE tab_frag_stat STATCHANGE AUTO,STATLEVEL FRAGMENT;
UPDATE STATISTICS HIGH FOR TABLE tab_frag_stat;

The STATCHANGE configuration parameter is a new ONCONFIG parameter that is used to define the threshold. The integer value of the configuration parameter ranges between 0 to 100 or AUTO; the default value is 10.

The STATLEVEL property is the level used to specify if the distributions must be created at table level or fragment level. The default value is AUTO. You can also set STATLEVEL TABLE or STATLEVEL FRAGMENT. When you specify STATLEVEL FRAGMENT, the distributions are created per fragment.

The command in Listing 8 creates a fragmented table called tab_frag_stat. After you insert rows and if any of the fragments reach the threshold when update statistics is run, the server creates the distribution for that particular fragment. The default threshold is 10%. Fragment-level statistics can be used for new tables created after conversion, or they can be used for existing tables.

Reversion impact: When you try to revert to an older version, the server takes care of dropping the catalog changes; there are no reversion messages. Any fragment-level statistics are dropped, and all tables will use table-level statistics in the older version.

System catalog changes:

  • A new system catalog table, sysfragdist, is introduced for this feature. This table stores the distributions per fragment.
  • New columns ustbuildduration and ustlowts were added to the sysindices table.
  • New columns statlevel and statchange were added to the systables table.
  • A new column ustbuildduration was added to sysdistrib table.

New ONCONFIG parameters:

  • AUTO_STAT_MODE: Has a default value of 1, which enables the automatic update statistics operation (fragment-level statistics is ON). The value 0 disables automatic update statistics operation (original server behavior).
  • STATCHANGE: Has a default value of 10, which defines the percentage of threshold changes. Integer values for the configuration parameter range from 0 to 100.

Storage provisioning

This feature allows you to automatically allocate a storage space when there is a depletion of storage space. If you enable this feature, you no longer need DBA intervention to monitor the space when the application might encounter the problem of depletion of storage space. The server automatically takes care of adding the space from the storage pool that you specify. A storage pool can be raw devices, cooked files, or directories. A storage space can be a dbspace, sbspace, blobspace, temporary dbspace, or temporary sbspace. When there is any depletion of storage space, existing chunks can be automatically extended, or new chunks can be automatically added from the storage pool to that particular storage space.

After converting successfully from version 11.50.xC7 to 11.70.xC1, you can use the SQL administration API commands to implement storage provisioning. For example, to add a directory called /work/dbspaces/pool1 to the storage pool, specify the following:

Listing 9. Example to add a directory to storage pool
EXECUTE FUNCTION task("storagepool add", "/work/dbspaces/pool1", 0, 0, 1000, 1);

Reversion impact: The storagepool table is automatically dropped during reversion. There are no reversion messages for this feature.

System database changes:

  • A new storagepool table is added to sysadmin database.
  • A new column, is_extendable, is added to the sysmaster:syschunks table.
  • New columns, create_size and extend_size, are added to sysmaster:sysdbstab table.

New ONCONFIG parameters:

  • SP_AUTOEXPAND: The default value is 1, which means enable automatic space expansion; 0 means disable automatic space expansion.
  • SP_WAITTIME: The default value is 30, and the range is 0 to 2147483647.
  • SP_THRESHOLD: The default value is 0. The server interprets1 to 50 (including decimals) as a threshold percentage. The server interprets 1000 or above as the number of kilobytes. Values between 50 to 1000 are invalid.

Trusted context

A trusted context is a database object defining the set of properties for a connection. When the properties are met, a trusted connection is allowed. The connection is made by a specific user and made from a trusted client. DBSECADM (Database Security Administrator ) has the permissions to create, delete, or modify trusted context.

After converting successfully from version11.50.xC7 to 11.70.xC1, you can use the following command to create trusted context:

Listing 10. SQL syntax for trusted context
CREATE TRUSTED CONTEXT TCX1 BASED UPON CONNECTION USING SYSTEM AUTHID usr3 
ATTRIBUTES (ADDRESS '9.99.999.999')
WITH USE FOR usr2 WITHOUT AUTHENTICATION,usr1 WITH AUTHENTICATION ENABLE;

The command in Listing 10 creates a trusted context called TCX1. The address is the IP address, and when usr3 requests a trusted connection, the request is accepted, because usr3 satisfies all the connection attributes. You can switch the user to usr2 without providing authentication information, because usr2 is defined as a user of TCX1 without authentication. However, you cannot switch to usr1 without authentication, because usr1 is defined as a user of TCX1 with authentication. In order to connect to trusted context, you must have a client, such as ESQLC, JCC, or JDBC.

Reversion impact: The server takes care of dropping the system catalog changes, and the reversion goes through without any reversion messages.

System databases changes:

  • New tables systrustedcontext, systcxattributes, and systcxusers are added to sysusers database.
  • New columns succ6, succ7, succ8, fail6, fail7,and fail8 are added to the sysmaster:sysaudit table.

Mapped user

Currently on UNIX or MS Windows, all the users of the server are also known to the operating system. Using the mapped user feature, you can externally authenticate a user by mapping the user to an appropriate user and group privileges, irrespective of whether a user has an operating systems account. A user who is mapped this way is known as a mapped user. A DBSA can map a user to an existing operating systems user, or the DBSA can map the user to a default set of properties.

The authentication can be done using SSO or PAM. For more information on PAM authentication, take a look at the information on Pluggable Authentication Modules (PAM) in the IBM Informix Security Guide and in some of the client guides.

After converting successfully from version 11.50.xC7 to 11.70.xC1, you can use the following command to create a mapped user:

Listing 11. SQL syntax for mapped user
onmode -wf USERMAPPING=BASIC
GRANT ACCESS TO user usr1 WITH PROPERTIES user idsuser;

In order to use this feature, the USERMAPPING configuration parameter can be set to basic or admin. After authenticating usr1 through PAM, the grant command (used in Listing 11) maps usr1 to idsuser (the user with the operating systems account), granting the properties of idsuser to usr1.

Reversion impact: The server drops all of the catalog changes before reversion; the reversion is successful without any reversion messages.

System database changes: New tables sysusermap, syssurrogategroups, and syssurrogateusers are added to the sysusers database.

New ONCONFIG parameters:

  • USERMAPPING: The default value is OFF, which means only users having operating system accounts can connect to Informix. A value of BASIC means that users without operating systems account can connect to Informix, but not as privileged users. A value of ADMIN means that users without operating system accounts can connect to the server as privileged users.

Selective row-level audit (SRLA)

This feature is an enhancement of existing audit behavior. Using this feature, you can enable row-level auditing. This improves the performance of the database server. In order to use the SRLA feature, you must configure the adtcfg file by setting the new ADTROWS configuration parameter to 1. You must also be a DBSSO.

After converting successfully from Informix 11.50.xC7 to 11.70.xC1, you can use the following command to create SRLA:

Listing 12. SQL syntax for SRLA
onaudit -c 
onaudit -R 1
onaudit -a -u usr1 -e INRW
ALTER table tab_audit ADD AUDIT;

You can check the audit configuration using onaudit -c. The onaudit -R 1 command is used to enable SRLA. The onaudit -a -u _default -e INRW command creates the user audit mask usr1, and the granularity is set to Insert Row. You can also set the granularity to Delete Row (DLRW), Read Row (RDRW), or Update Row (UPRW). The above ALTER command enables SRLA for the table tab_audit.

You can create a table WITH AUDIT clause or alter table ADD|DROP AUDIT clause.

The command used in Listing 13 displays the generated audit trail.

Listing 13. Utility to check audit trail
onshowaudit

Reversion impact: When you try to revert to an older version, the following message is displayed, and reversion is cancelled.

Listing 14. Reversion messages
onmode -b 11.50.xC6
Database xdb: Drop the audit property from the table tab_audit before reversion.

Use the command in Listing 15 to drop the SRLA feature:

Listing 15. SQL syntax to drop SRLA
ALTER table tab_audit DROP AUDIT;

After you drop SRLA, the reversion will be successful.

Creating a foreign key constraint with index disabled

New syntax for disabling an index for foreign key constraint was added to use with the ALTER statement. When creating a foreign key, Informix automatically creates a B-Tree index on the child table when creating a foreign key. In some situations, the index is not used for any lookups, there are no updates/deletes from the parent table, and no queries that need this index on the child table. In this scenario, the B-tree on the child table is simply not needed and incurs unwanted overhead if the child table is inserted, updated, or deleted.

After converting successfully from version 11.50.xC7 to 11.70.xC1, you can use the following commands to create a foreign key constraint with index disabled:

Listing 16. SQL syntax for foreign key constraint with index disabled
CREATE table tab_parent (col1 int, col2 int) EXTENT SIZE 16;
CREATE unique index indx_parent_col1 on tab_parent(col1);
ALTER table parent add constraint primary key(col1) constraint cons_parent_col1;
CREATE table tab_child(c1 int, c3 varchar(32))  EXTENT SIZE 16;
CREATE index indx_child_c1 on child(c1);

ALTER table child add constraint
(foreign key(x1) references parent (col1) constraint cons_child_c1 index disabled);

The commands in Listing 16 create a parent table, called tab_ parent, with the primary key constraint cons_parent_col1. The table has a unique index, indx_parent_col1. The commands also create child table, called tab_child table with a unique index, indx_chile_c1. The ALTER command creates the foreign key constraint, cons_child_c1, with the indx_child_c1 index disabled. During the foreign key enforcement, the server uses the sequential scan, instead of foreign key index. The foreign key index will be disabled, and the sysobjstate catalog table will reflect that state. This feature is very useful for data warehouse applications.

Reversion impact: When you try to revert back to an older version, the following message is displayed, and reversion is cancelled:

Listing 17. Reversion messages
onmode -b 11.50.xC6
Database xdb: Table xuser.child: Drop and recreate this referential constraint 
cons_child_x1 before reversion.

To drop the foreign key constraint with index disabled and recreate this referential constraint, issue the following command:

Listing 18. SQL syntax to drop and recreate the feature
ALTER table child drop constraint cons_child_c1;
ALTER table child add constraint (foreign key(x1) references parent (col1) 
constraint cons_child_c1);

After the foreign key constraint with index disabled is dropped and recreated, the reversion completes successfully.

Automatic allocation of secondary partition header pages for extending the extent map

Prior to 11.70xC1, if the partition page header has a large number of extent entries in it, conversion would fail, if the server was not able to fit all the extent entries into a single partition header page. Starting with version 11.70.xC1, if you are converting from a lower version of Informix, and if a partition header page has a large number of extents entries, Informix will internally allocate secondary partition header pages (if required) to extend the extent map, so that conversion will be successful. If more extents are allocated after converting to 11.70.xC1, and if you try to revert back to an older version, the reversion may fail, if the server is not able to fit all extent entries into a single partition header page. In order to successfully revert, you will be required to run defragmenter on such partitions. You can find more information on Defragment in the IBM Informix 11.70 Information Center (see Resources").

Blade manager extensions

After conversion, when a UDT, UDR, or INDEX from a database extension (DataBlade Module) is used, the database extension is automatically registered.

Listing 19. Example where datablade module is automatically registered
CREATE table tab1 (col1 integer, col2 node);

The above command creates a table called tab1, and it will automatically register the node datablade.

Reversion impact: When you try to revert back to an older version, the following message is displayed, and reversion is cancelled.

Listing 20. Reversion messages
onmode -b 11.50.xC6
Database xdb: Must drop spl/udr (sysbldsqltextin) before attempting reversion.

There will be more reversion messages apart from the above-mentioned reversion message.

Drop the extensions:

Listing 21. SQL syntax to drop the extensions
drop tab1;
execute function sysbldprepare('Node.*', 'drop');
unprep database name.

After dropping the extensions, the reversion will be successful.

New tasks added to Sysadmin database

New tasks (idle_user_timeout,auto_crsd, check_for_ipa,auto_tune_cpu_vps, and bad_index_alert) were added to the ph_task table. For the Blade Manager extensions mentioned above, other new tasks (autoreg exe, autoreg vp, autoreg migrate-console, and autoreg migrate test) tasks were added to the ph_task table. For the storage provisioning feature mentioned above, the mon_low_storage task is added to the ph_task table.

Reversion impact: All the new tasks will be dropped by the server before reversion; there are no reversion messages.


Conclusion

This article has provided information that should help you plan how to implement all the new Version 11.70 features that have potential upgrade and reversion impact. After learning about this upgrade impact, the system catalog changes, new ONCONFIG parameters, and reversion impact, you are now ready to take full advantage of all the new features in Version 11.70.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=577226
ArticleTitle=Understanding Informix 11.70 features with upgrade impact
publish-date=11112010