Consider the following INSERT statement:
       
            INSERT INTO <target-table>
                (SELECT * FROM <source-table> WHERE ...);

The earlier implementation did not allow the source table to be the same as the target table. Any table occurrence in the SELECT clause of the INSERT clause cannot have the target table. The server returns error -360 if it detects such a case.

This feature relaxes the above restriction by allowing the use of target tables in the SELECT clause of the INSERT statement.

If one of the tables in the SELECT clause is the target table, then newly inserted rows into the target table by the INSERT statement are NOT used for evaluation of the select or any of the nested subquery of the INSERT statement.

The effect of the above statement is the same as the following statements executed in a transaction.

           SELECT * FROM <source-table> WHERE ... INTO TEMP <temp-tab>;
           INSERT INTO <target-table> SELECT * FROM <temp-tab>;
           DROP TABLE <temp-tab>;

           INSERT INTO <target-table> EXECUTE PROCEDURE <someproc>();

If procedure <someproc> scans or updates <target-table>,  then the database server returns error -360.

The behavior of the UPDATE and DELETE statements has not changed where the target table is used in their select subqueries. In this case, the database server returns error -360.

This applies to the stdev() function in a SELECT statement that has a group by clause for a single row group.

The stdev() function has changed from calculating 'sample deviation' to calculating 'population deviation.' The difference between the two is in the final divide by the value '/N' in the expression. For sample deviation, we would divide by 'N-1' and for population deviation, which we are now calculating, we use 'N'. Users will find that our calculation of the standard deviation is different from earlier server versions, the difference being (N-1)/N and, of course, the special case when N is 1.

Use the following formula to calculate the population deviation:

            (sum((X[k])^2) - (sum(X[k]))^2/N) / N

By definition, the population deviation for a population of 1 is 0. If you wish, you can omit such cases through the appropriate query construction; for example, "having count(*) > 1."

C.  Interpretation of two digit years within objects

This section does not apply if this is a first time installation of Informix, or if two digit years are not used in the expressions of the following objects:

        fragmentation expressions, stored procedures, triggers, check constraints, and user defined routines.

Note that not all of these features may be supported on this version of the product.

This release of 7.31.UD1 introduces a change in when date literals with two digit years within expressions of objects are evaluated according to the settings of relevant environment variables, such as, and not limited to, DBCENTURY. Previous to this release, two digit year dates in the expressions of the objects were interpreted by Informix according to the environment variable settings which prevailed at runtime time of the object.  However, starting with this release, the date literal will always be interpreted using the environment variable settings prevailing at the creation time or the time of last modification of the object with which the date literal is associated.  The settings of environment variable at runtime of the object will not be used.

This applies only to date strings having two digit years in the expression of the objects mentioned above; i.e. it does not apply if four digit years are used in the objects.

The following two steps are required to take advantage of this change introduced in this software:

        1.   Upgrade the Informix server to this release

        2.   Redefine all objects that use two digit year expressions.  For fragmentation expression, redefining means detaching and reattaching
        the expression.  For all other objects, the object must be dropped from the database and recreated.

Only after the objects are redefined using this new server, the date literals in the expressions within objects will be interpreted according to the environment variable settings at the time the object was created or last modified.  The reference date used for this interpretation is the creation date or the last modification date of the object and not the current date when a query is run.

If the objects are not redefined using this new server, the behavior of the object will remain the same as prior to the upgrade. However, since any new objects created after the software upgrade will behave differently from those created prior to the upgrade, administration of the database may become difficult because the database will have a mix and match of new and old behavior of objects in the database (with respect to when a two digit years within expressions of objects are evaluated). Therefore, it is recommended that the two upgrade steps above be followed.

Lastly, in order to avoid any possibility of misinterpreting two digit years within the objects, it is recommended that this opportunity be used to change the use of two digit years to four digit years instead, if possible.

This section does not apply if this is a first time installation of Informix, or if date literals are not present in the following objects within the database: fragmentation expressions, stored procedures, triggers, check constraints, and user defined routines. Note that not all of these objects may be supported for this version of the product.

In the rare case that the setting of DBDATE prevailing at creation time or time of last modification of the object differs from the one that is in effect at the run time of the object, you may either get a runtime error from the server or get erroneous results due to incorrect interpretation of the date literal.

In order to maintain consistency, starting with objects created or modified using this release, the date literals within expressions of objects will be evaluated according to the setting of DBDATE prevailing at creation time or time of last modification of the object. The settings of environment variable at runtime of the object will not be used to evaluate the date literal within the objects. However, the prevailing setting at runtime of the query will still be in effect for date related data processed within the query.
        
If your operating environment is such that the objects were created using one set of assumptions regarding the DBDATE setting and the runtime environment uses a different setting, you may encounter some problems. It is recommended that the usage of the database be modified so that the settings of DBDATE at creation, modification, and run time are consistent throughout.

The new behaviour is :

        IF PDQPRIORITY environment variable is set in user(onpload) environment
        THEN
           let server use it to do unload
        ELSE
           IF it was set in the engine startup environment
           THEN
              let server use it do unload
           ELSE
              let server use value of 100 to do unload.

Note : HPL will not allow unloading to multiple devices if PDQPRIORITY is zero or when the PDQ is turned off at the server side either by setting MAX_PDQPRIORITY to zero in ONCONFIG or through 'onmode -D' command. In such cases an error message will be added to the onpload log file.

"Cannot  unload  to  multiple devices with PDQ turned off, either at client or server side."

For more information on PDQPRIORITY environment variable, see the "Informix Guide to SQL: Reference."

For more information about reverting from Informix Dynamic Server Version 7.31 to an older database server, see the Informix Migration Guide.

The following table lists selected capacity limits and system defaults for this release of Informix Dynamic Server.

System-Level Parameters	Maximum Capacity per Computer System
Informix Dynamic Server systems per computer (Dependent on available system resources)	255
Maximum number of accessible remote sites	Machine specific
Table-Level Parameters (based on 2K page size)	Maximum Capacity           per Table
Data rows per fragment	4,277,659,295
Data pages per fragment	16,775,134
Data bytes per fragment  (excludes Smart Large Objects (BLOB, CLOB) and Simple Large Objects (BYTE, TEXT) created in Blobspaces)	33,818,671,136
Binary Large Object BLOB/CLOB pages.	2**31
Binary Large Objects TEXT/BYTE bytes	2**31
Row length	32,767
Number of columns	32K
Indexes  (roughly double this number for 4k page size)	77
Columns per index	16
Bytes per index	255
Access Capabilities	Maximum Capacity per Dynamic System
Maximum databases per Informix Dynamic Server system	21 million
Maximum tables per Informix Dynamic Server system	477,102,080
Maximum active users per Informix Dynamic Server (minus the minimum number of system threads)	32K user threads
Maximum active users per database and table (also limited by the number of available locks, a tunable parameter)	32K user threads
Maximum number of open tables per Informix Dynamic Server system	Dynamic allocation
Maximum number of open tables per user and join	Dynamic allocation
Maximum locks per Informix Dynamic Server system and database	Dynamic allocation
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 chunk size	2 gigabytes
Maximum number of 2K pages per chunk	1 million
Maximum number of open BLOBs (applies only to TEXT and BYTE data types)	20
Maximum number of B-tree levels	20
Maximum amount of decision support memory	Machine specific
Informix Dynamic Server System Defaults
Table lock mode	Page
Initial extent size	8 pages
Next extent size	8 pages
Read-only isolation level (with database transactions)	Committed Read
Read-only isolation level (ANSI-compliant database)	Repeatable Read
ON-Monitor Statistics
Number of displayed user threads	1000
Number of displayed chunks	1000
Number of displayed dbspaces	1000
Number of displayed databases	1000
Number of displayed logical logs	1000