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
onconfigparameters added for new features (the
onconfigparameters are used to configure the database server)
- Any new syscatalog changes (the system catalog tables have all the information about database objects)
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
- Fragment by list and interval
- Fragment-level statistics
- Storage provisioning
- Trusted context
- Mapped user
- Selective row-level audit (SRLA)
- Creating a foreign key constraint with index disabled
- Automatic allocation of secondary partition header pages for extending the extent map
- Blade manager extensions
- New tasks added to Sysadmin database
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.
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
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
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;
STATCHANGE configuration parameter is a
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.
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.
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.
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.
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.
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.
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
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.
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.
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
You can create a table
WITH AUDIT clause or
ADD|DROP AUDIT clause.
The command used in Listing 13 displays the generated audit trail.
Listing 13. Utility to check audit trail
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.
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.
- IBM Informix 11.70 Information Center: Learn more about the latest version of Informix.
- Informix page on developerWorks: Learn more about Informix.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Informix Dynamic Server Express Edition: Download and evaluate Informix Dynamic Server Express Edition.
- Informix trials and demos: Download a trial or demo version of Informix.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the My developerWorks community; with your personal profile and custom home page, you can tailor developerWorks to your interests and interact with other developerWorks users.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.