=================================
RELEASE NOTES
FOR
INFORMIX 8.11.UE1 PRODUCT RELEASE
DATE: 15 Apr 1997
=================================
TABLE OF CONTENTS
I. OVERVIEW OF RELEASE NOTES
II. PRODUCTS CONTAINED IN THE 8.1 INFORMIX-OnLine XPS, INFORMIX-Command
Center, CONNECTIVITY, & EMBEDDED PRODUCTS RELEASE
III. NEW-PRODUCT HIGHLIGHTS
IV. INFORMIX-ONLINE 7.1 FEATURES NOT CURRENTLY SUPPORTED IN ONLINE XPS
V. LIMITS IN INFORMIX-ONLINE XPS
VI. CHANGES TO EXISTING FUNCTIONALITY
VII. PRODUCT DEFICIENCIES
VIII. OPEN BUG
IX. FIXED BUGS
X. UPGRADE FROM PREVIOUS RELEASES
XI. BACKWARD COMPATIBILITY
XII. RELEASE NOTES, DOC NOTES, AND BUG LISTS FOR ACCOMPANYING PRODUCTS
I.OVERVIEW OF RELEASE NOTES
The purpose of these release notes is to make the user aware of
changes in products that might affect existing applications.
The release notes contain information on changes from previous
versions, known problems, and workarounds.
The release notes document is not intended to be all-inclusive;
it is a tool to assist you in the update process. Please consult
Informix product manuals for additional information on product
features and for clarification of product behavior.
II.PRODUCTS CONTAINED IN THE 8.1 INFORMIX-OnLine XPS, INFORMIX-Command
Center (ICC), CONNECTIVITY, & EMBEDDED PRODUCTS RELEASE
The following products are contained in the 8.11 INFORMIX-OnLine XPS
servers and connectivity products release:
Product Name Generic Name
------------ ------------
INFORMIX-OnLine XPS Database Server Product
INFORMIX-Command Center (ICC) Central Administrative Desktop
INFORMIX-ON-Bar Backup and Restore Utilities
INFORMIX-ESQL/C Structured Query Language API
INFORMIX-ESQL/C-Runtime Structured Query Language API
INFORMIX-CLI Call-level Interface for ODBC
III.NEW-PRODUCT HIGHLIGHTS
A. New Restrictions in OnLine XPS:
The following restrictions have been introduced in the 8.11
INFORMIX-OnLine XPS servers, connectivity, and embedded
products release:
No Reversion to Previous Releases of OnLine XPS
-----------------------------------------------
Because of changes to Configuration Management structures in
XPS 8.11, reversion from XPS 8.11 to XPS 8.10 is NOT POSSIBLE.
New Configuration Parameter: LOG_BACKUP_MODE
--------------------------------------------
To support ON-Bar, the 8.11 release contains the new
ONCONFIG parameter LOG_BACKUP_MODE. It is critical that you
add the following line to your ONCONFIG file if you do not
want the Server attempting to back up logs:
LOG_BACKUP_MODE NONE
Clustered Indexes and Nonlogging Tables
---------------------------------------
As of XPS 8.11, creating a cluster index on a STANDARD
table is not supported. This was inadvertently allowed in
previous versions of XPS. With the current (8.11 and later)
implementation of clustered indexes, the method of
reorganizing the data is nonlogging, so the table cannot be
restored during full recovery. Therefore, clustered indexes
should not be allowed on a STANDARD table.
Creating cluster indexes on STATIC and OPERATIONAL tables
is supported. If you require a clustered index on a
STANDARD table, alter the table to OPERATIONAL, create the
cluster index, perform a level-0 archive of the dbspaces
involved, and then alter the table back to type STANDARD.
Configuration Parameters Eliminated
-----------------------------------
The following configuration parameters are no longer
supported and should be deleted from the ONCONFIG file to
eliminate warnings:
LTAPEDEV
BAR_MAX_BACKUP
BAR_NB_XPORT_COUNT
BAR_XFER_BUF_SIZE
The ON-Bar configuration parameters are documented in the
INFORMIX OnLine XPS Backup and Restore Guide.
B. New Features for OnLine XPS:
The following major features have been introduced in the 8.11
INFORMIX-OnLine XPS servers, connectivity, and embedded products
release:
> CASE statements
> Event alarms (using new onevd UNIX daemon)
> Hash joins
> Nonlogging tables
replace nonlogging databases
> Parallelized functions:
- aggregation
- deletes
- index builds
- joins
- loads
- logging
- scans
- sorts
- unloads
- updates
> Joined-row updates
Ability to use a join to select desired rows for update
as well as ability to use columns from joining tables
for values in the set clause.
> Sampled and local table qualifiers
Instead of scanning all rows, OnLine XPS can use either
a sample of the table or only the local fragments.
> Hash fragmentation
OnLine XPS can distribute rows using a system-defined
hash function.
> Improved aggregation and grouping
Algorithm changes improve performance and skew handling.
> Improved sort parallelization
Algorithm changes improve parallelization.
> Improved expression evaluation
Algorithm & implementation changes improve performance.
> Pload/XPS
Replaces onload, onunload, dbload, dbunload
Binary data (IBM binary integers and packed decimal)
Named pipes
> SQL92 CASE expression
OnLine XPS supports this expression.
> Subquery unnesting
The database server recognizes that some subqueries
can be processed more efficiently by "unnesting" the
subquery and executing the query similar to a join.
The restrictions on which queries are unnested are
described under Product Limitations.
> Dbslices
A new method of simplifying dbspace allocation and
management.
> xctl
A control program layer on top of OnLine and OS
utilities to simplify management of coservers in
OnLine XPS.
> onutil
An interactive command interpreter to manage OnLine XPS
storage objects and performs administration operations.
> Singleton insert/update/delete from secondary coservers.
> External tables
Parallel load and unload
Pload/XPS uses SQL syntax to load, unload and
filter data to and from external files. Refer to
the Pload/XPS chapter of the "OnLine XPS Feature
Enhancements Guide" for details.
Exception file captures conversion failures.
> Connections to secondary coservers.
All supported DML statements work through
connections made on any coserver. DDL statements are
only supported on the primary coserver (coserver 1)
except for the creation of TEMP tables,
which can be issued from any coserver.
New onstat Options for OnLine XPS:
onstat -g bus
This option displays information about backups and restores
that have been queued using onbar.
onstat -g bus_sm
This option displays information about storage managers and
registered onbar worker processes.
C. New Features in Informix-Command Center
INFORMIX-Command Center (ICC) is a central administrative desktop
that provides single-view control over OnLine XPS servers, coservers,
and storage. ICC runs on Windows NT and allows the OnLine XPS
administrator to configure, control, and monitor OnLine XPS running
on UNIX. This release of ICC includes the following features:
* The ICC Main Window
The ICC Main window is a graphical interface that displays the
status of the physical resources of all the OnLine XPS database
servers, and automates common database server administration
functions.
* The New Database Server Wizard
The New Database Server Wizard helps you configure and initialize
an OnLine XPS database server for the first time.
* The Logical Log Monitor
The Logical Log Monitor tracks the rate at which logical logs fill
on multiple coservers and displays the status of the logical logs.
* The Event Monitor
The Event Monitor reports events that are flagged by the OnLine XPS
database server and indicates whether events are critical.
For more information about these features, refer to the
INFORMIX-Command Center User Guide for INFORMIX-OnLine XPS.
D. New Features in ON-Bar
This version of ON-Bar provides backup and recovery utilities
for the OnLine XPS database server. Refer to the Backup and
Restore Guide for INFORMIX-OnLine XPS, and the doc-notes file,
BAR_DOC8.1 for details about this version of the ON-Bar
utility.
E. New Features in INFORMIX-CLI
This release of OnLine XPS supports INFORMIX-CLI. Refer to the
INFORMIX-CLI Installation Guide and INFORMIX-CLI Programmer's
manual for details.
IV.INFORMIX-ONLINE 7.1 FEATURES NOT CURRENTLY SUPPORTED IN ONLINE XPS
OnLine XPS supports functionality provided by INFORMIX-OnLine
Dynamic Server with the following exceptions:
> ALTER FRAGMENT, ALTER TABLE
Workaround: Use Pload/XPS to unload the table. Recreate it
and then reload it.
> ANSI flagger updates
> Auditing or the configuration file, adtcfg, used to
> store ADTMODE, ADTERR, ADTPATH, and ADTSIZE from ONCONFIG
> Blobs
> Constraints: OnLine XPS does not check deferred or referential
constraints.
> Cursors specifying WITH HOLD
> Cursor stability isolation mode (level is automatically
upgraded to committed read)
> Dataskip
> DB-Access enhancements described in Chapter 6 of the "Guide to 7.1
Feature Enhancements"
> Deferred constraints not checked
> Detached indexes
> Distributed database capability (formerly called ISTAR)
> Dynamic allocation of user threads, transactions, tblspaces,
chunks, and dbspaces
> Enhancements to the SQLhosts file in OnLine 7.1 (wildcards,
explicit addresses, options field)
> Environment variables that OnLine 7.1 adds (INFORMIXOPCACHE,
INFORMIXSQLHOSTS, NODEFDAC) or changes (OPTCOMPIND, PSORT_NPROCS)
> Fragment-level privileges (grant and revoke)
> INFORMIXTMP directory
> INFORMIX-STAR (distributed database capability)
> Native-Language Support and the NCHAR and NVARCHAR data types
> Nonlogging databases (replaced by nonlogging tables)
> on* utilities (refer to section IV)
> ONCONFIG parameters new to OnLine 7.1 (LBU_PRESERVE, ONDBSPDOWN,
OPTCACHEMAX)
> Outer joins that have a join ordering specified in the FROM clause
of the query--for example, SELECT * FROM T1, OUTER(T1,T3)
> PDQ
> Records longer than a data page with clustered indexes. If
clustered indexes are being used then the maximum record-
size is approximately 100 bytes less than the pagesize.
> Referential constraints not supported
> Rename database
> Roles (creating, setting, dropping, granting, revoking)
> Sample database (stores7)
> Segments freed automatically when unused
> SQL utility changes in OnLine 7.1 (in dbexport, dbload, dbschema)
> SQLWARN array updates
> Stored procedures
> Subqueries of the following types:
- subqueries inside OR operators
- correlated subqueries that have a disjunction 'OR'
in their WHERE clause
- correlated subqueries inside NOT operators
- queries that produce 'complex outer join expressions' when
unnested
- subqueries inside of view definitions
- subqueries using views
- subqueries combined with views in the same query
- subqueries in the SET clause of an update
- subqueries inside of a CASE expression or a GROUP BY clause
- subqueries that contain GROUP BY clauses
- independent subqueries inside the ALL clause when
the operator involved is anything except '!=' (for
example, SELECT a FROM T1 WHERE a <op> ALL (SELECT
b FROM T2); only works when <op> is '!=')
- multivalued independent subqueries that are not part
of an EXISTS or NOT EXISTS predicate and have a
disjunction OR in the WHERE clause
- view definitions that contain subqueries
> System catalog tables that OnLine 7.1 adds (sysfragauth,
sysobjstate, sysroleauth, sysviolations) or changes
(syscoldepend, sysconstraints, sysusers)
> Triggers
> Unique constraints and unique indexes
Supported only for unfragmented tables or on columns that
compose the fragmentation key.
> Unique indexes
> UPDATE STATISTICS HIGH
Workaround: On large tables, UPDATE STATISTICS MEDIUM gives
good results.
> View definitions containing unions
> Views combined with subqueries in the same query
> View restrictions
> XA transaction support
> X/OPEN 4 compliance
V.LIMITS IN INFORMIX-ONLINE XPS
Maximum Capacity
System-Level Parameters Per Computer System
-----------------------------------------------------------------
INFORMIX-OnLine Dynamic Server systems per computer...........255
(Dependent on available system resources)
Maximum number of accessible remote sites ......... not supported
Maximum number of Dbspaces ....................................8K
Maximum Capacity
Table-Level Parameters (based on 4K page size) per Table
-----------------------------------------------------------------
Data rows per fragment............................. 4,277,659,295
Data pages per fragment............................... 16,775,134
Data bytes per fragment........................... 67,637,342,272
(excludes Binary Large Objects, BLOBs)
Binary Large Object bytes...........................not supported
Row length...................................Page Size - 64 bytes
Number of columns............................................ 32K
Indexes...................................................... 154
Columns per index............................................. 16
Bytes per index.............................................. 255
Maximum Capacity
Access Capabilities per OnLine System
-----------------------------------------------------------------
Maximum databases per OnLine system................... 21 million
Maximum tables per OnLine system..................... 477,102,080
Maximum active users per OnLine system........... 32K userthreads
(minus the minimum number of system threads)
Maximum active users per database and table...... 32K userthreads
(Also limited by number of available locks, a tunable parameter)
Maximum number of open tables per OnLine system........... 32,000
Maximum number of open tables per user and join........... 32,000
Maximum locks per OnLine system and database........... 8 million
Maximum number of page cleaners.............................. 128
Maximum number of recursive synonym mappings.................. 16
Maximum number of tables locked by user....................... 32
Maximum number of cursors per user.............. machine specific
Maximum number of pages per chunk.......................1 million
Maximum number of open BLOBs........................not supported
Maximum number of B-tree levels............................... 20
INFORMIX-OnLine Extended Parallel Server System Defaults
-----------------------------------------------------------------
Table lock mode............................................. Page
Initial extent size...................................... 4 Pages
Next extent size......................................... 4 Pages
Read-only isolation level......................... Committed Read
(w/ database transactions)
Read-only isolation level........................ Repeatable Read
(MODE ANSI database)
VI.CHANGES TO EXISTING FUNCTIONALITY
CONFIGSIZE
==========
New in this release, the CONFIGSIZE configuration parameter can
be used to specify the amount of rootdbs space allocated for
storage of Configuration Management (CM) data on CM coservers.
In this release, only coserver 1 is a CM coserver.
The CM data that in prior releases had been written to the
xcfg_<DBSE>.<SERVERNUM> file in the INFORMIXDIR/etc directory
is now kept in a reserved area in the rootdbs. Using the
rootdbs to store the data allows the CM subsystem to guarantee
data consistency in the event of failure--a guarantee that is
not possible with the xcfg file. Although OnLine XPS does not
rely on the contents of the xcfg file, it continues to write
data to the file periodically so that utilities that rely on
its contents need not be changed.
EVENT ALARMS
============
In OnLine 7.1 and prior releases, event alarms executed the
script specified in the ALARMPROGRAM onconfig parameter. In
OnLine XPS, event alarms instead send a message to the new
event daemon onevd.
Event alarm classes have been reorganized and simplified. Refer
to the Documentation Notes for details.
ALTER TABLE
===========
Two statements now available are:
ALTER TABLE tab LOCK MODE() and ALTER TABLE tab TYPE ()
In OnLine XPS 8.11, ALTER TABLE cannot be explicitly rolled
back and therefore cannot be executed within a user-initiated
transaction. A user who wishes to undo the operation needs to
alter the table back to its original state explicitly.
RENAME TABLE
============
You can rename tables (from the primary coserver only) with no
semantic changes.
DBSPACETEMP
===========
In this release, the DBSPACETEMP configuration parameter applies to all
nodes.
SELECT INTO TEMP/SCRATCH
========================
This release provides automatic support for fragmented
temporary tables across nodes when those tables are explicitly
created using the SELECT INTO TEMP SQL statement.
The database server creates explicit temporary tables according
to the following criteria:
o If the query used to populate the temp table produces no rows,
the database server creates an empty, nonfragmented table.
o If the rows produced by the query do not exceed 8 kilobytes, the
temporary table resides in only one dbspace.
o If the rows exceed 8 kilobytes, the database server creates
multiple fragments and populates them using a round-robin
fragmentation scheme.
Known Problems with Select into Temp
o The row inserted into the temp table must fit on a single page.
The error "242: Could not open database table ()" is generated
if the row is too long.
o On each node, the number of dbspaces used for temp purposes
(specified by DBSPACETEMP) need to be greater than or equal to
the number of CPU VPs configured.
EXTERNAL TABLES
================
High performance, parallel loads and unloads are provided by
defining the datafiles to be loaded or unloaded as external
tables and allowing users to load or unload the data with
INSERT ... SELECT statements.
New features as of 8.0:
- FIXED POSITION text files (ASCII or EBCDIC) have new converters:
New converters for PACKED and ZONED decimals.
New converters for big-endian integers (as stored by IBM)
- Check constraints, defaults and not nulls defined on external tables
can be used to cleanse data during loads.
- Escape characters in delimited ASCII files are supported.
- The Pload logfile contains summary information about the
Pload jobs and lists reject files that are created.
- Completion statistics for loads and unloads are available.
- Runtime statistics are provided via onstat commands.
- DBSCHEMA writes out external table definitions.
- Better diagnostics for rejections are provided in PG1
including writing out the record number for the rejected record
rather than the offset.
OTHER PLOAD/XPS FEATURES
========================
- Higher performance loads/unloads for text files using
repartitioned conversions.
- CREATE EXTERNAL TABLE
This statement describes the external data file.
- SELECT...INTO EXTERNAL
This statement can be used to select data into an external
table and save the description of the data for future loads
and unload. This can only be used for delimited text files
or for files written in the Informix internal data representation.
This statement cannot be used for fixed-format files.
- INSERT INTO <internal table> SELECT...FROM <external table>
<WHERE clause>
This statement loads data from external files to internal tables.
- INSERT INTO <external table> SELECT...FROM <internal tables>
<WHERE clause>
This statement unloads data into external files from internal
tables.
- SELECT <select-list> FROM <external table> <WHERE clause>
This is a true SELECT that does not require an INSERT clause.
Most types of SELECT statement can be done on the external data.
The following restrictions apply:
no joins
no subqueries
- Both the INSERT table and the SELECT table can be external tables.
Inserts into external tables are not appended to the end of
the external file. The file is truncated to zero length then
the rows are inserted. Appending to external tables is
currently not supported.
- Supports ORDER BY for all unload statements.
- Supports FIXED POSITION text files (ASCII or EBCDIC).
- Supports DELIMITED text files (ASCII or EBCDIC).
- Supports Informix representation data files (for the fastest
load/unload).
- Supports column mapping, expressions, and projections on external
tables for loads.
- Supports filtering of records for loading using WHERE clauses for
loads.
- Supports loads using DEFAULTS defined as system functions.
The following Pload/XPS features are not in this release:
- DBDATE and DBMONEY defined for the external table.
Use environmental variables to change these values.
- Violations tables
- Pseudotables for runtime statistics
- Sample program for tape handling
Three new system catalog tables contain information about
external tables: sysexternal, sysextdfiles, and sysextcols.
Queries on these catalogs can be done using the tabid as stored
in systables with tabtype = 'E'.
Known Problems with External Tables
- When you unload a float or small float to a fixed-position
file, the number is truncated to fit the field if the
internal precision of the float is greater than the fixed
field supports. For more control over the formatted result of
a float, use the SQL functions ROUND() or TRUNC() to define
output precision.
- When a row is rejected because the final field delimiter
before the record delimiter is missing (in DELIMITED files),
the subsequent record is sometimes also rejected. This
problem arises because the record delimiter for the first
record can be skipped, and so the rejected row includes all
the first record and the following record.
LOGGING AND NONLOGGING TABLES
=============================
With the addition of fragmentation and detached indexes, it has
become impossible to guarantee index integrity if the engine
fails during an insert, update or delete. For these reasons,
OnLine XPS does not support nonlogging databases.
With the current focus on data warehousing, the need to have a
mix of logged and unlogged tables within the same database has
become apparent. To satisfy the needs of data warehousing,
clarify the logging and recovery requirements of tables, and
replace the already decommissioned nonlogging databases, OnLine
XPS supports four types of permanent tables (RAW, STATIC,
OPERATIONAL and STANDARD) and two types of temporary tables
(TEMP and SCRATCH).
COSERVER VERSIONING
===================
OnLine XPS assumes that all coservers are executing the same
version of oninit. For example, you cannot run an OnLine XPS
8.11 coserver within the same instance of OnLine XPS as an
8.00PF1 coserver. When you install a new release, install it on
ALL participating nodes.
STACK TRACES
============
The system stack trace facility has been enhanced to print both the name
and the hex address of each routine.
ROWIDS
======
Rowids are obsolete in OnLine XPS. Users must identify rows using column
values in a row.
NONLOGGING DATABASES
====================
OnLine XPS does not support nonlogging databases. The default
logging mode is unbuffered logging. The database can contain
logging and nonlogging temporary and permanent tables in any
combination.
ONCONFIG Configuration File
===========================
To support coservers, OnLine XPS 8.11 requires and supports
more ONCONFIG parameters than OnLine 7,x and earlier. Refer to
the "INFORMIX-OnLine XPS Feature Enhancements Guide" for
details on how to set up the ONCONFIG file. The
INFORMIXDIR/etc directory contains the sample file
onconfig.xps.
To support ON-Bar, the 8.11 release contains the new ONCONFIG
parameter LOG_BACKUP_MODE. It is critical that you add
LOG_BACKUP_MODE NONE
to the ONCONFIG file if you do not want the Server attempting
to back up logs.
LOG_BACKUP_MODE <x> This replaces the current LTAPEDEV mechanism
for determining how RSAM handles full logs.
NONE Logs are not backed up but are still reused.
Equivalent to LTAPEDEV /dev/null mode.
ONLINE UTILITIES
================
oncheck
-------
The oncheck utility is not supported in OnLine XPS 8.11. Onutil provides
the functionality of the oncheck -cd, -pd, -pt and -pT options. See the
onutil section of the "INFORMIX-OnLine XPS Feature Enhancements Guide"
for a description of these statements.
onload & onunload
-----------------
These utilities are replaced by using EXTERNAL tables with the
Informix (internal data representation) format.
onparams
--------
Informix recommends that you use onutil in place of onparams.
The client must connect to coserver 1 in this release.
-p option
The option to change the size and/or location of the physical log
is not supported in this release of OnLine XPS. To alter the
physical log you must stop the INFORMIX-OnLine server, edit the
relevant ONCONFIG parameters, and bring the server back to on-line or
quiescent mode. To change the size of the physical log, change the
PHYSFILE configuration parameter. To change the location of the
physical log, change the PYSDBS configuration parameter.
Specifying a Coserver (-X flag)
The -X flag allows you to specify a coserver name or coserver
number. If you do not specify the -X flag, the coserver number
defaults to 1.
onspaces
--------
Informix recommends that you use onutil in place of onspaces.
Onspaces operations for blobspaces are not supported in this
release of OnLine XPS.
OnLine XPS does not support changing the mirror of the root
dbspace using onspaces. To change the the mirror of the root
dbspace, stop the INFORMIX-OnLine server, change the MIRROR,
MIRRORPATH and MIRROROFFSET configuration parameters to the
desired values, and bring the server back to on-line or
quiescent mode.
Specifying a Coserver (-X flag)
The -X flag allows you to specify a coserver name or coserver
number. If you do not specify the -X flag, the coserver number
defaults to 1. You can specify a coserver when you create a
dbspace using onspaces. The coserver you specify will be the
managing coserver for the new dbspace.
onmode
------
You can execute onmode on individual coservers, or use xctl onmode
to apply changes to all coservers.
When you invoke onmode by itself, it operates on the coserver specified
in the INFORMIXSERVER environment variable. Generally this means the
INFORMIXSERVER value must be set to <servername>.<coserverid>
When you invoke onmode (or any other utility) within an xctl command,
xctl automatically sets the INFORMIXSERVER environment variable on
each coserver.
onstat
------
You can execute onstat on individual coservers, or use xctl onstat
to check the status of all coservers.
When you invoke onstat by itself it displays status information
for the coserver specified in the INFORMIXSERVER environment variable.
Generally this means the INFORMIXSERVER value must be set to
<servername>.<coserverid>
When you use xctl onstat, xctl automatically sets INFORMIXSERVER for you.
NEW onstat OPTIONS IN ONLINE XPS
--------------------------------
onstat -g xqp <plan_id>
Prints the query plan for the given plan ID. You must
issue this command from the connection coserver. This
is the same plan as the one listed in the XMP Query
Plan section of the sqexplain.out file when you run a
command with SET EXPLAIN ON.
onstat -g xqs <plan_id>
Prints the current query statistics for the given plan
ID. You must issue this command from the connection
coserver.This is the same plan as the one listed in the
XMP Query Statistics section of the sqexplain.out file
when you run a command with SET EXPLAIN ON. The only
difference is that statistics from the command include
numbers for the currently active query segments. This
allows you to detect skew in an active query--in
particular, during a long-running hash join.
Option OnLine XPS Subsystem or Type of Information
-------------------------------------------------------------------
onstat -g rgm Resource Grant Manager
onstat -g stk displays call stacks for individual threads
onstat -g xmp displays information about the active parts
of all running queries
onutil
------
Onutil includes a RESET BACKUP command, which enables recording
new logical logs, logging status changes, new chunks, and new
mirrors for a cogroup as if a level-0 backup had been performed.
(This is often referred to as a "simulated level-0 backup.")
onutil syntax:
ALTER COGROUP cogroup-name RESET BACKUP;
IMPORTANT: The reset backup command does not produce an actual backup.
You cannot use reset backup for recovery purposes.
LOGSLICES
=========
Onutil supports "logslices". A logslice is a set of logs
created across a dbslice. Logslice operations simplify the
process of adding (or deleting) logs on an OnLine XPS server.
Please see the "INFORMIX-OnLine XPS Feature Enhancement Guide"
for details on how to create and manage logslices.
Procedure for adding new logs.
------------------------------
To add a new logical log to the OnLine XPS server perform the
following steps using onutil:
1. Bring to quiescent mode the coservers where new logs are to be
added.
2. Add a logical log using the CREATE LOGICAL LOG or
CREATE LOGICAL LOGSLICE command. This will give a log (or
logslice) with a status of A (added) in the output of onstat -d.
3. Enable the log (giving it a status of F for free) using the
RESET BACKUP command described above.
xctl
----
The xctl command uses the NODE parameter in each COSERVER
section of the OnLine XPS configuration file to execute
commands on one or more coservers.
You can use the -C flag to simplify server startup. An OnLine XPS
server can be initialized by using:
xctl -C oninit -iy
it is roughly equivalent to giving the command:
xctl -b -X= oninit -iy -X=
WARNING: Use of the "iy" option destroys any/all data previously
stored in this OnLine instance.
The names of the -b (and -C) output files are now of the form
stdout.<DBSERVERNAME>.<COSERVERID>
in the working directory; both stderr and stdout go to the
same file. DBSERVERNAME is from the onconfig file.
3) As a last step in environment variable handling, all variables
set for the "xctl" but not on the remote host's "xboot" are also
copied. This should eliminate the need to use "xctl -e...".
4) If initial communication between xctl and any xboot client stalls
for more than 20 seconds, it is assumed that the client node has
crashed and xctl goes on to the next host. An error message
("xctl: unable to connect") is printed and xctl exits with a
nonzero result code.
5) Header lines for input and output have been expanded to identify
the command, host, coserver id, and time.
6) Running xctl without any options prints a brief "man" page.
7) xctl now uses it's own TCP/IP based communication between
xctl and xboot. This should be invisible to users.
xload & xunload
--------------------
Xload and xunload have been replaced by Pload/XPS functionality
(external tables).
PDQPRIORITY IN ONLINE XPS
=========================
OnLine XPS does not require that you set PDQPRIORITY to execute
a query; however, such queries are granted minimal memory
allocations. To obtain larger memory grants, set PDQPRIORITY
to a value greater than 2 (the default). If you set PDQPRIORITY
to zero, queries are admitted without waiting for queries with
PDQPRIORITY greater than zero. For more information, refer to
the "SET PDQPRIORITY" section in Chapter 7 and the "Monitoring
Parallel Database Queries" section in Chapter 5 of the
"INFORMIX-OnLine XPS Feature Enhancements Guide."
VII.PRODUCT DEFICIENCIES
====================
USING 8.11 ONLINE XPS SYNTAX IN ESQL/C
======================================
The embedded products have not yet been modified to parse the new
syntax. The means that to use the new syntax in an ESQL/C program
you must use the prepare/execute model or the EXECUTE IMMEDIATE syntax.
NOT SUPPORTED
EXEC SQL create raw table t (a int) fragment by hash(a)
in dbslice;
SUPPORTED
EXEC SQL execute immediate
"create raw table t (a int) fragment by hash(a)
in dbslice";
or
EXEC SQL prepare insprep from
"create raw table t (a int) fragment by hash(a)
in dbslice";
EXEC SQL execute insprep;
ERROR MESSAGES AND NUMBERS INCOMPLETE
=====================================
The numbers allocated for new XPS functionality have not been
merged with baseline code numbers and may conflict with some
7.x error numbers. In addition there are many error situations
that use the same number but with a different text error
message parameter. In the future it is likely that some of
these error conditions will be modified to generate their own
unique number. In the meantime the error message text is
essential to a correct diagnosis of the problem.
Queries without GROUP BY or WHERE clauses that contain
aggregation on indexed columns. For example:
create index ix on tab(indexed_col);
select max(indexed_col) from tab;
Workarounds
a) use a "dummy" where clause:
select max(indexed_col) from tab
where 1 = 1;
b) use a valid WHERE clause
c) use a group by clause
select col2, max(indexed_col) from tab
[ where ... ]
group by col2
OUTER JOIN queries involving more than two tables
no workaround.
OUTER JOIN queries where the join relationship is
is not the equality join. For example:
select ... from t1, outer t2 where t1.c < t2.c ;
VIII.OPEN BUGS
BUG # DESCRIPTION
--------------------------------------------------------------------
63263 DROP DATABASE IN SESSION 2 IMMEDIATELY FOLLOWING SESSION 1
WHICH ACCESSED THE SAME DATABASE FAILS
59187 SHARE DATA DICTIONARY CACHES DO NOT AGE OUT. THEY PERSIST
FOR EVER.
58269 A QUERY WITH MANY COLUMNS DISTINCT CLAUSE USES EXCESSIVE
MEMORY DURING PARSE PHASE AND CAN FAIL WITH -208. ALSO CAN
FAIL WITH MANY IN CLAUSES.
57321 WRONG SQLCODE ERROR -211 DISPLAYED WHEN CONNECTING FROM 2ND
COSERVER AND SELECTING FROM A TABLE
64257 KILL A SESSION DOING SELECT INTO TEMP AND YOU CAN GET THE
ERROR -408 INSTEAD OF THE EXPECTED -1803.
64800 ASSERT FAILED: INTERNAL ERROR - BUS ERROR WHEN TRYING TO
TURN MIRRORING OFF OF A DBSPACE WHILE ITS CHUNKS ARE STILL
BEING MIRRORED
64872 STARTING MIRRORING USING COMMAND "ONSPACES -M <DBSPACE> -F
<FILENAME> DUMPS CORE WHEN NUMBER OF LINES IN THE FILENAME
IS > 99
64731 ONCE YOU START MIRRORING MULTIPLE CHUNKS USING COMMAND
"ONSPACES -M <> -F <>, THERE IS NO WAY YOU CAN ABORT
MIRRORING
53727 EXECUTING A QUERY "TOO EARLY" AFTER COSERVER 1 IS IN ONLINE
MODE CAN CAUSE COSERVER 2 TO PANIC.
48887 FOR CERTAIN VALUES OF INFORMIXSERVER, ONLY ONE OF EITHER
DBACCESS OR ONSTAT WILL WORK, NOT BOTH.
65420 ONLOG UTILITY DOESN'T PROTECT AGAINST SIGQUIT OR A 2ND
SIGINT PROPERLY, ALSO THE ONLOG BACK-END THREAD DOESN'T
DETECT THE UTILITIES DEATH.
62207 PLOAD/XPS:WRONG MESSAGE DISPLAYED WHEN SELECTING INTO
EXTERNAL TABLE
62398 HASH FRAGMENTION ON SERIAL COLS, IF EXPLICIT DATA VALUES
LOADED, RESULTS IN POOR DISTRIBUTION; OVER RANGE OF
1-100000 ROWS ALL GO TO COSERVER 1.
58397 ON 2-COSERVER SYSTEM, DBSCHEMA -SS FAILS WHEN RUN FROM THE
2ND COSERVER
66110 LISTENER THREAD HANGS PREVENTING FURTHER CONNECTIONS TO THE
ENGINE. XPS VERSION OF ONLINE BUGS B31146 AND B35580.
61402 27000: CANNOT SUPPORT MULTIPLE CONNECTIONS OVER SHARED
MEMORY. ERROR, WHEN START DBACCESS, IF CONNECT. REDIRECTOR
IS ENABLE (C=1)
60685 ASSERT FAILED: INTERNAL ERROR - BUS ERROR WHILE ADDING SHM
VIRTUAL PROCESSOR (XPS WAS UP WITH NETTYPE TLITCP)
IX.FIXED BUGS
BUG # DESCRIPTION
---------------------------------------------------------------------
55596 UNABLE TO CREATE PLOAD REJECT FILE FOR SOME SPECIAL CASES
56438 INSERT .. SELECT ... ALLOWS "ORDER BY" WHICH SHOULD NOT BE
ALLOWED.
56477 DATABASE LEVEL REVOKE STATEMENTS DISPLAYS ERROR MESSAGE
580:CANNOT REVOKE PERMISSION . 111: ISAM ERROR: NO RECORD
FOUND
56635 DATA DICTIONARY CORRUPTION AS A RESULT OF "ALTER TABLES"
FROM "RAW" TO "OPERATIONAL" PERFORMED ON A LARGE NUMBER OF
TABLES
56950 8.0.PG1 (ICL ONLY) - DBACCESS WRITES TO EXTERNAL TABLE USING
PIPE HANG WHEN KAIO IS ACTIVATED.
57243 XPS 8.00.PG1 SP2 SYSMASTER:SYSTABNAMES DBSNAME CHANGES AFTER
RENAME TABLE
57825 WHEN UNLOADING TO MULTIPLE PIPES ON A SINGLE COSERVER, A
CRASH CAN OCCUR WHEN MT_WAKEUP IS CALLED BY MORE THAN ONE
FIFO VP.
57884 DBSCHEMA -SS ON TABLE NOT FRAGMENTED ACROSSED DBSLICE
PRODUCES "IN <DBSPACE>" CLAUSE FOR INDEXES WHICH IS NOT
SUPPORTED BY THE ENGINE.
57930 CORE IS DUMPED WHEN INFORMIXSERVER SET TO GROUP NAME AND
CONNECTION REDIRECTOR IS ENABLED (C=1)
57954 PTHDRPAGE:PTALLOC:BAD PARTN PAGE AFTER INTERRUPT OF
CONCURRENT SELECT INTO SCRATCH (TEMP) TABLE.
57967 SET EXPLAIN ON CAUSES AN INTERNAL BUS ERROR TO OCCUR.
58249 AIO GLOBAL FILE STRUCTURE NEEDS TO HAVE SOME FIELDS RESET
BEFORE REUSE
58334 HASH JOIN BUILD ON TABLE WITH MANY NULLS DOESN'T YIELD OFTEN
ENOUGH. WITH 50000+ NULLS IN THE HASH TABLE THE YIELDS
OCCURED EVERY 2.5 MINUTES.
58395 XCTL -C ONINIT [-VY|-IY] WOULD NOT GENERATE
STDOUT.SVRNAME.SVRNUMBER FILE
58468 "ONSTAT -G LSC" NEXT_PPAGE VALUE ON XPS IS INCORRECT
58486 CAN UNLOAD A TABLE IN EBCDIC FORMAT THAT CONTAINS NULLS,
BUT CANNOT LOAD BACK INTO A DATABASE
59753 CAN'T BRING UP SEQUENT 8.10.UD1. FAILS WITH UNABLE TO READ
XPS ROOTDBS PAGE ZERO (22) IF USING RAW CHUNKS. FAILS THE
2ND TIME IT IS BROUGHT UP.
59912 DEADLOCK BETWEEN THE CM_MULTI MUTEX AND A
RSLK_OBJ_CM_FRAGTABID LOCK WHEN SET LOCK TO WAIT <INFINITE>
IS USED.
60108 DBACCESS, DATABASE -> INFO -> DBSPACE RETURNS ERROR
60382 "SELECT FIRST 1 .." SYNTAX GETS -284 ERROR IF MORE THAN 1
ROW SATISFIES SELECT CRITERIA
60614 ENGINE CRASH AFTER INTERRUPTING DBACCESS DATABASE CONNECT.
60619 QUERY WITH SUB-SELECT WHICH JOIN ON SAME TABLE CAN HANG
SERVER.
61148 IN XPS, AFTER DROPPING INDIVIDUAL LOGS IN A LOGSLICE, IT'S
IMPOSSIBLE TO DROP THE LOGSLICE ITSELF.
61611 -23725 PLOAD INTERNAL ERROR IN (FILE, LINE#)=(EXITXLR.C,2108).
61989 XPS: STATIC TABLES DO NOT PERMIT DIRTY READS IF LOCKED BY
OTHER USER IN EXCLUSIVE MODE
62260 BUS ERROR DURING ADD_TO_FREELIST() WHILE DOING
SQTCB_CLEANUP().
62503 ERR'S -242 COULD NOT OPEN DATABASE TABLE, -106 NON-EXCLUSIVE
ACCESS ON SELECT INTO EXTERNAL WHERE OUTER TABLE IS SPECIFIED
TWICE.
62774 XPS 8.10.UD1 SEQUENT: CMI_READ_PAGEZERO() DOESN`T HANDLE EINTR
FROM OPEN/LSEEK CALLS
63351 XPS ERROR GOTTEN: "ASSERT FAILED: CHECK_ALLOC_BLOCK MEMORY
BLOCK POSSIBLY FREED TWICE"
63596 UPDATE STATISTICS LOW/MEDIUM CAN CAN CAUSE BUS ERROR OR MEMORY
BLOCK TRAILER CORRUPTION.
63642 RECEIVE ERROR 788 ON SQL STATEMENT. ALSO CELL_NUM WAS NOT
CASTE AS A CHARACTER TYPE AS IT SHOULD HAVE BEEN.
63645 IN 8.11.UB4.X1, RUNNING TABLE INFO AS OTHER-THAN INFORMIX,
LIST WILL NOT BE COMPLETE. ONLY APPRX 2460 CHARACTERES ARE
DISPLAYED IN SCREEN LISTING.
64925 PLOAD LOG FILE AND SQEXPLAIN.OUT FILE SOMETIMES GET CREATED
AS ROOT.
65309 VALUES IN SYSMASTER TABLES DON'T DISPLAY CORRECTLY AFTER
MIGRATING TO 8.11.UC1B4; IF YOU DROP/RECREATE SYSMASTER DB,
PROBLEM DISAPPEARS
65848 ENGINE HANG/NON-YIELDING HJOIN THREAD; EVENTUAL ENGINE CRASH
WHEN PROCESSING OVERFLOW.
65984 QUERY WITH FIXED DECIMAL DATATYPE USING BETWEEN CLAUSE WITH
A LOWER OR UPPER BOUNDARY WHOSE SCALE IS > THAN THE COLUMN'S
SCALE RETURNS WRONG RESLT
66231 QUERY GIVES WRONG RESULTS FOR IN PREDICATE
66473 INSERT SELECT FROM SMALLINT TO INT GET GARBAGE
68193 NODE HUNG; DEADLOCK BETWEEN MDM_SVC THREAD HOLDING DFM_CB
WHILE WAITING ON XMF_REQT AND THE XMF THREAD WAITING ON
DFM_CB IN DFM_CTL_CALLB.
X.UPGRADE FROM PREVIOUS RELEASES
The 8.11 release requires a complete reload from any version of
OnLine Dynamic Server or OnLine XPS earlier than 8.00PG1.
Because of changes to Configuration Management buffers in XPS
8.11 (described later in this section), reversion from XPS 8.11
to XPS 8.10 is not possible.
The fundamental reason data must be reloaded into OnLine XPS is
to increase the number of dbspaces and chunks from the current
limit of 2K to 64K. Not being able to have more than 2K
dbspaces limits the size of databases to the 2- to 3-terabyte
range. Informix expects that customers will soon require larger
databases. Increasing the number of dbspaces beyond 2K requires
page structure changes which, unfortunately, forces a reload.
Informix realizes that a reload is a major inconvenience for
customers. To limit the number of customers affected by the
reload, Informix made this change in the 8.00PG1 release.
8.00PG1 has an upper limit of 8K dbspaces and chunks, but these
changes allow for future limits of 64K dbspaces and chunks
without any future reloads.
The basic steps for the reload consist of:
1) saving the description of the database schema using dbschema
2) unloading the data
3) initializing the database
4) building the dbspaces and dbslices for the new database
5) creating the tables and database elements
6) loading in the data that had been saved
A utility (dbxpsldr.ec, found in INFORMIXDIR/demo/xmp) for
facilitating the reload is available on the 8.11 tape and on
the ESM web page along with a README.dbxpsldr.
This utility is run on the old database to create three output
SQL scripts:
1) x_<dbname>.sql--creates external table definitions for all
internal user tables in the database <dbname>. These are used
by Pload/XPS to unload and load the data.
2) u_<dbname>.sql--an unload script that unloads all the tables
3) l_<dbname>.sql--a load script that provides steps for running
a high performance load for all the tables
CM Safewrite Conversion
-----------------------
When you start an OnLine XPS server of release 8.11 (or later)
against a database that was last saved using either Release
8.00 (PG1 or later) or 8.10, the server converts the
Configuration Manager data in the safewrite areas in chunk 1 of
the root dbspace to a slightly different format. No manual
action is needed or possible for the conversion, and once the
Configuration Manager data is written back to the safewrite
areas, it is no longer possible to restart successfully with a
pre-8.11 Server.
The new format uses a bit more safewrite disk space for each
kind of object tracked, and, because the disk space used by
these areas was already preallocated, the count of each kind of
object that the Configuration Manager can save may be reduced
by a small number. This is the case only for databases
initialized with a pre-8.11 Server; all newly initialized
databases use a larger preallocation size. Furthermore, if the
safewrite areas are later made larger by increasing the
CONFIGSIZE, the new safewrite areas are will be large enough to
hold the number of objects indicated in the 8.00 Release
Notes.
For each kind of object, if the conversion reduces the count
that can be saved, an message indicating the previous and new
limits is written in the online log. If there is no message
about a given kind of object, the maximum number of objects of
that kind that can be saved remains unchanged from the number
indicated in the 8.00 Release Notes.
To make sure that Configuration Manager data is saved to the
safewrite areas correctly and correctly read back during
startup, new sanity checks verify the contents of each object
as it is transferred into and out of safewrite buffers. To
disable these checks, set the UNIX environment variable
CM_CHECK_INVALID to N prior to starting the server.
For example, from a C-shell, execute the following command:
setenv CM_CHECK_INVALID N
>From a Bourne or Korn shell, execute the following command:
CM_CHECK_INVALID=N ; export CM_CHECK_INVALID
Consult Informix support personnel before you turn off these checks.
Updating CM Critical Media Status
---------------------------------
With the 8.11-UC1 release, the Configuration Manager tracks
whether each DBSpace is Critical Media and provides that status
to other subsystems. To provide compatibility with existing
instances, the Configuration Manager performs a process that
results in it acquiring Critical Media status about all
DBSpaces. The Configuration Manager starts the update process
the first time Coserver 1 is run under the new version.
The bulk of the process runs asynchronously, allowing
individual Coservers to come on-line before the entire process
is complete. The process involves Coserver 1 retrieving
Critical Media status from each of the configured Coservers and
updating it in Configuration Manager data areas, including
saving it permanently in the Configuration Manager safewrite
area. Should one or more Coservers be off-line during that
initial startup, the update process for them is automatically
continued once they are started, even if that is after the
entire XPS Server has been shutdown and restarted. The update
process is completed immediately for those coservers that are
started with coserver 1.
Any attempt by other XPS subsystems to access the Configuration
Manager's Critical Media status for a given Coserver prior to
the update process for that coserver completing will result in
an error returned to the subsystem.
The following is an example of what is added to the online log
for each Coserver whose Configuration Manager Critical Media
Status is updated:
[01] 16:04:08 Updating CM Critical Media Status for Coserver 2
[01] 16:04:08 Critical DBspaces in Coserver 2 are:
[01] 16:04:08 DBspace Number: 2 Name: rootdbs_2
[01] 16:04:08 DBspace Number: 7 Name: dbsl.5
[01] 16:04:08 DBspace Number: 8 Name: dbsl.6
[01] 16:04:08 DBspace Number: 9 Name: dbsl.7
[01] 16:04:08 DBspace Number: 10 Name: dbsl.8
[01] 16:04:08 Update for Coserver 2 is Completed.
Note that all messages are added by Coserver 1.
Table Type Changes
------------------
In addition to the new syntax CREATE SCRATCH TABLE t1(), the
original syntax CREATE TEMP TABLE t1 () WITH NO LOG is still
supported.
Also in addition to the new syntax SELECT...INTO SCRATCH t1,
the original syntax SELECT...INTO TEMP t1 WITH NO LOG is still
supported.
The semantics of TEMP TABLE WITH NO LOG are the same as for
SCRATCH tables. See the section on table types for more details
on SCRATCH tables.
Light appends and express-mode loads are no longer supported
for STANDARD tables.
External Table Changes
----------------------
OnLine XPS does not support express-mode loads for STANDARD tables.
The REJECTDIR keyword has been changed to REJECTFILE and supports
%c and %n macros for runtime filename expansion.
ONCONFIG File Changes
---------------------
Dynamic configuration parameters for userthreads, transactions,
tblspaces, chunks and dbspaces are now supported. The following
configuration parameters are now ignored: USERTHREADS,
TRANSACTIONS, TBLSPACES, CHUNKS and DBSPACES.
The onconfig parameter DS_TOTAL_MEMORY maximum value has been
increased to 4 gigabytes. This allows a maximum of 2 gigabytes
for any single iterator.
Header Size Change
------------------
The overhead for each page has increased from 28 bytes to 60
bytes. This will affect the number of rows or index entries per
page.
Changes in Default Extent Sizes
-------------------------------
The initial and next extents sizes for tables have been changed
from 8 pages to 4 pages.
Changes in Number of Dbspaces and Chunks
----------------------------------------
The maximum number of dbspaces has been increased from 2047 to 8K.
The maximum number of chunks is the same as for dbspaces.
Changes in System Catalog Tables
--------------------------------
The sysfragments system catalog has an additional column named
hydbpos. This column contains a fixed value of zero. It is
included in the composite key for the catalog.
XI.BACKWARD COMPATIBILITY
Because of changes to Configuration Management buffers in XPS 8.11,
reversion from XPS 8.11 to XPS 8.10 is not possible.
XII.RELEASE NOTES, DOC NOTES, AND BUG LISTS FOR ACCOMPANYING PRODUCTS
For the location of INFORMIX-CLI release notes and
documentation notes, refer to the Introduction to the
INFORMIX-CLI Programmer's Manual. The release notes contain
lists of open and fixed bugs.
For a list of INFORMIX-Command Center bugs, refer to the README
file in the "Informix Administration Program Group" on the NT
workstation.
=================================