Informix
Informix Online Documentation

RELEASE NOTES FOR

Informix Red Brick Decision Server for UNIX Platforms

Date: 05/19/00

Version: 6.0.3


Table of Contents

I. Important Release Information
II. Customer Support
III. New Features
A. Multiple PSUs per Default Segment
B. New Configuration Option--READER_PRIORITY
IV. Significant Operational Considerations
A. Support for Windows 2000 Computers
B. TARGET Indexes on VARCHAR Columns
C. TCP/IP Ports
D. Versioned Databases on Compaq Alpha Machines
E. Kernel Parameters
F. SQL-BackTrack on 64-bit Operating Systems
G. Red Brick ODBClib SDK for Silicon Graphics IRIX Platforms
H. HP Patch Required for HP-UX 11 64-bit Operating Systems
V. Installation and Upgrade Notes
A. For the First Time
B. With an Existing Warehouse
C. Migration Requirements for Red Brick Decision Server
D. Important Notes on 6.0 Installation and Upgrade Procedure
E. EBCDIC Code Pages
F. Disk Space Requirements
G. Third-Party Tools
H. License Keys for All Products
VI. Fixed Issues
A. Database Administration Issues
B. Administrator Tool Issues
C. Query-Related Issues
VII. Known Issues
A. Database Administration Issues
B. Query-Related Issues
C. Client Tool Issues
VIII. Documentation Notes
A. Installation and Configuration Guide for UNIX Platforms
B. Administrator's Guide
C. ODBC Connectivity Guide
D. SQL-BackTrack User's Guide
E. SQL Reference Guide
F. Table Management Utility Reference Guide
G. Tech Note on Loading Data in VARIABLE Format
H. Messages and Codes Reference Guide


I. Important Release Information

Informix Red Brick Decision Server Version 6.0.3 (V6.0.3) is a new release that contains new features and maintenance fixes. Before you use V6.0.3, read the following sections:

Informix Red Brick Decision Server 6.0.3 is the latest version of the product formerly named Red Brick Warehouse. Throughout these release notes, "V6.0.3" refers to Red Brick Decision Server Version 6.0.3 release, "V5.1.8" refers to Red Brick Warehouse Version 5.1.8 release, and so on.


II. Customer Support

If you have technical questions about a Red Brick Decision Server database but cannot find the answer in the appropriate document, contact Informix Customer Support as follows:

Telephone 1-800-274-8184 or 1-913-492-2086
(7 A.M. to 7 P.M. CST, Monday through Friday)

Internet access http://www.informix.com/techinfo

For nontechnical questions about a Red Brick Decision Server database, contact Informix Customer Support as follows:

Telephone +1-800-274-8184
(7 A.M. to 7 P.M. CST, Monday through Friday)

Internet access http://www.informix.com/services


III. New Features

Red Brick Decision Server V6.0.3 contains the following new features:

Red Brick Decision Server V6.0.2 contains the following new features:

A. Multiple PSUs per Default Segment

In some customer environments, power users are allowed to create transient tables (not temporary tables, but real user tables that exist for a short period of time) and use them to store intermediate query data and/or data extracted from production data warehouses. Often these tables are created with default segments because the DBA wants to keep control over space usage and does not want to give these users authority to create named segments.

When the amount of data inserted into these tables exceeds the 2GB limit on default segments, the power users need to wait for the DBA to add a named segment to their table for more storage. The requisite wait for the DBA has a negative impact on both project turnaround time and the database administration group. This enhancement provides power users with more freedom in their storage usage when new tables are created but still allows the DBA to keep control over the file system space.

The 2GB limit on default segments is relaxed so that more than 2GB of data can be inserted into a table without the DBA's intervention. Originally, a 2GB maximum size was set because some file systems limit file sizes to 2GB. Theoretically, on some other systems, the maximum size can be set to larger than 2GB. Although PSUs larger than 2GB are not supported for this release, default segments larger than 2GB are supported through multiple PSUs.

DBAs can specify the file system and the amount of space for use by the default table or index through either SET commands or options in the rbw.config file. One or more PSUs are allocated for the specified amount of space when the CREATE TABLE statement is executed. This method does not slow down any loads to dynamically allocate another PSU. It also gives the DBA a greater degree of control over the space used and can prevent a user from monopolizing the file system.

This feature provides two new configuration options:

The DEFAULT_SEGMENT_SIZE parameter specifies the amount of space for use by the default table or index. If this option is not set, the default table size is limited to 2GB. If no unit (K, M, G) is specified for value, the default is K.

The DEFAULT_PSU_EXTENDSIZE parameter specifies the extend size of PSUs in the default table or index. The default value is 16K. If no unit (K, M, G) is specified for value, the default is K.

This option can improve performance for large tables or indexes when it is properly set to some large value. However, if it is set too large, file system space can be wasted. In particular, if many small tables are to be created, using some small value (for example, 16K) may save file system space.

When you execute the CREATE TABLE statement, you get the following results:

Example

The DBA could set the DEFAULT_SEGMENT_SIZE to 5GB and DEFAULT_PSU_EXTENDSIZE to 256KB with the following two lines in the rbw.config file:

Three PSUs would be allocated to a single default segment:The graphic shows three PSUs and their sizes. The first two have a maximum size of 2GB and the third has a maximum size of 1GB.

The following SET commands would achieve the same result:

B. New Configuration Option--READER_PRIORITY

The following new option can be set in the rbw.config file:

OPTION READER_PRIORITY ON | OFF

This option is set to ON by default, which preserves the locking behavior supported in previous releases. Queries requesting read locks on tables have priority over operations that need to write to the database. When queries are executed, they must acquire a database-level lock on the system tables; this lock is not released until the individual tables are locked. Although this behavior is appropriate for most decision-support applications, heavy query loads can cause any concurrent DDL operations to be significantly delayed because each new request for a database read lock is given higher priority than requests for database write locks.

Setting READER_PRIORITY to OFF disables query-priority locking in general and enables schema locks to be acquired at the table level, instead of relying on a database-level lock on the system tables. As soon as the schema locks are acquired, the database lock can be released. For applications that require activities such as DDL operations and loads to occur while the database is online, this approach to locking reduces contention in two ways:

Conversely, this approach can delay the locking of tables required for query execution. Unless you routinely encounter problems with delayed DDL operations that must be performed while the database is online, Informix suggests that you leave this option set to ON.

When the READER_PRIORITY option is set to OFF, the DST_LOCKS table and the showlocks utility reflect two new types of lock: RS (read schema) and WS (write schema).


IV. Significant Operational Considerations

Before you use Red Brick Decision Server V6.0, read the platform-specific information for your operating system in the appendices of the Installation and Configuration Guide. Also note the following operational considerations and changes.

A. Support for Windows 2000 Computers

Informix Red Brick Decision Server V6.0.3 and associated products are certified to run on the following Windows 2000 platforms:

B. TARGET Indexes on VARCHAR Columns

Red Brick Decision Server V6.0.3 allows TARGET indexes on VARCHAR columns. In V6.0.2, such indexes were disallowed.

C. TCP/IP Ports

The server uses two port numbers for each SERVER entry in the rbw.config file: the specified port number and a port number one greater than the specified port. (For example, if port 5050 is specified, the server will use ports 5050 and 5051.) If the second port number is in use, the server cannot start correctly. Before you upgrade to V6.0, verify that both the specified port and the one immediately following it are available and have not been assigned to other applications.

D. Versioned Databases on Compaq Alpha Machines

To use versioning on Compaq Alpha machines, you must apply the following patch:

UNIX Release Os Patch Comment
Tru64 UNIX

OSF425-411

Tru64 UNIX 4.0d

OSF425-405328-3

Patch included in patch kit 3 as patch 345.03 Kernel fixes.

Tru64 UNIX 4.0e

OSF435-160

Patch included in patch kit 2 as patch 161.00 Kernel fixes.

E. Kernel Parameters

Red Brick Warehouse V5.1.5 and later releases make greater use of IPC system resources (shared memory and semaphores). Be sure to compare your kernel parameters with the recommendations in the appendices of the Installation and Configuration Guide, and if necessary, rebuild the kernel before you install V5.1.5 or a later version.

Data Size and Stack Size (for AIX and Digital UNIX Platforms Only)

For Red Brick Decision Server V6.0, Informix recommends higher data size and stack size settings for the AIX and Digital UNIX operating systems. Refer to the appropriate kernel parameters sections of the Installation and Configuration Guide.

F. SQL-BackTrack on 64-bit Operating Systems

If you are running Red Brick Decision Server on a 64-bit UNIX operating system and want to use SQL-BackTrack for backups, you must ensure that your environment meets the following conditions:

G. Red Brick ODBClib SDK for Silicon Graphics IRIX Platforms

On Silicon Graphics IRIX platforms, Informix now provides two sets of 32-bit ODBClib libraries contained in two separate directories: libo32 for -32 libraries and lib32 for -n32 libraries. If you link an application to a 32-bit library, be sure to use the correct library, based on the compile flag settings for the application.

H. HP Patch Required for HP-UX 11 64-bit Operating Systems

Customers running 64-bit code on the HP-UX 11 should install HP operating system patch PHKL_12354. Unless you install this patch, you might encounter occasional problems with a bus error.


V. Installation and Upgrade Notes

The following sections contain notes on installing and licensing the products you have purchased, and upgrading existing databases. After you have read these notes, refer to the Installation and Configuration Guide for standard installation and upgrade procedures.

A. For the First Time

If you are a new user, follow the procedure for a first-time installation described in the Installation and Configuration Guide. This procedure is designed for sites not currently using Red Brick Decision Server.

B. With an Existing Warehouse

If you are currently using an earlier version of the Red Brick server, read the information in the Installation and Configuration Guide under "Running Two Releases of Red Brick Decision Server." This section describes how to allow users to continue working with the current database and software while you install the new version in a separate directory. You can then test the new version to see how the changes will affect your warehouse implementation before you upgrade your production databases.

Informix recommends this procedure whenever the changes between releases are substantial or if an upgrade is required. A database cannot be used with an earlier release after it has been upgraded to a newer one.

SQL-BackTrack

SQL-BackTrack 2.0.1 for Red Brick Decision Server V6.0 is incompatible with Red Brick Warehouse 5.1.x. Informix recommends that you perform a full backup of your database immediately before and after you upgrade to V6.0.

C. Migration Requirements for Red Brick Decision Server

For information on migrating databases from V5.x to V6.x, refer to the Informix Red Brick Migration Guide, available online at:

http://www.informix.com/answers/english/prbrick60.htm

D. Important Notes on 6.0 Installation and Upgrade Procedure

Read the following sections before installing V6.0.3 over a previous release of the Red Brick server.

Installation Changes

The V6.0.3 release requires an upgrade of existing V5.x databases to accommodate changes in the system catalog brought about by the introduction of the VARCHAR datatype. You can upgrade any V5.0.3 or later database directly to V6.0.3.

Two new prompts have been added to the installation script for V6.0.3 to ensure that database upgrades run smoothly after the installation is complete. The first prompt is as follows:

The default answer is yes. If you answer yes, the following prompt is displayed:

If you answer no, the installation will be cancelled.

Upgrade Procedure (5.x to 6.0.x)
  1. Connect to the 5.x database with the 5.x server, turn off versioning, and drop the version log (if the database was set up for versioning).
  2. Take a full backup of the 5.x database.
  3. Install the 6.0 server.
  4. Add the database name to the new rbw.config file.
  5. Change your environment variables to point to the new installation.
  6. Connect to the 5.x database with the 6.0 server and upgrade the database by running the following TMU command:
  7. where db_name is the logical name of the database and filename is a control file that contains:
  8. Take a full backup of the upgraded 6.0 database.

E. EBCDIC Code Pages

Although some EBCDIC code pages are listed in Appendix B of the Installation and Configuration Guide, you cannot create a database with an EBCDIC code page, such as IBM285. Loading EBCDIC data with the TMU is supported, as documented in Chapter 3 of the Table Management Utility Reference Guide.

F. Disk Space Requirements

To install Red Brick Decision Server V6.0 on a UNIX computer, you need at least 145 megabytes of free disk space.

G. Third-Party Tools

Before installing Red Brick Decision Server V6.0 and upgrading your database to the new release, you should contact the vendors of products and tools used with Red Brick Decision Server and verify their compatibility.

H. License Keys for All Products

You can install all Informix Red Brick products from the installation media; however, you cannot use any product without a license key.

You will be asked to provide license keys for each licensed product during the installation process. You can provide the license keys at that time or enter them later by using the rb_setup script on UNIX platforms or the Red Brick Setup program on Windows platforms.

Note the following changes in license key requirements for Version 6.0:


VI. Fixed Issues

This section lists and describes issues reported by customers that have been fixed in Informix Red Brick Decision Server V6.0.3. The prefix CSL followed by a number indicates the Customer Support Log number assigned to each issue.

A. Database Administration Issues

The implementation of unique multi-column indexes was not clearly documented. See the "SQL Reference Guide" documentation notes.

A new OPTION parameter was requested to provide a default directory for the summary files produced by the CHECK TABLE and CHECK INDEX commands. See the "SQL Reference Guide" documentation notes. In addition, the locations and names of the summary files are now reported as part of the standard output from the RISQL session, and more descriptive messages are reported when these commands are executed.

The ALTER TABLE ADD COLUMN command sometimes failed with a 132 error because the source data segments were not checked for damage or block count inconsistencies before the command was executed.

New format tables did not reuse deleted rows, regardless of whether the new rows were inserted via the RISQL Entry Tool or the Table Management Utility.

The ALTER TABLE ADD COLUMN...IN_PLACE command corrupted tables in both 5.1 and 6.0 databases.

B. Administrator Tool Issues

Various operations submitted through the Administrator tool failed with a 132 error when the database entry in the rbw.config file was incorrect. This scenario now produces an appropriate error message.

C. Query-Related Issues

In some STARjoin queries that included unconstrained tables in the FROM clause, the optimal STAR index was not chosen.

The server doubled the result set for queries with multiple WHERE clause constraints qualifying the same data (redundant constraints) when the constraints were separated with an OR operator. This problem has been corrected.

Certain queries with constraints expressed as IN lists failed to use the B-TREE index on the constrained column.


VII. Known Issues

This section describes known issues in Red Brick Decision Server V6.0 and is organized as follows:

A. Database Administration Issues

Processing Double-Byte Space Characters

East Asian languages, such as Chinese and Japanese, support a double-byte space character (for example, 0xa1a1 in Chinese). Using this double-byte space, as opposed to its single-byte ASCII counterpart (0x20), can cause unpredictable results with character-string comparisons, TRIM function processing, datetime string processing, and so on. Where possible, substitute double-byte spaces within strings with single-byte spaces, and trim any trailing double-byte spaces from the input file before you load the data.

Double-Byte Characters in Datetime Input Data for TMU Loads

For datetime fields, the TMU does not recognize Traditional and Simplified Chinese double-byte characters, except for the double-byte designations of "a.m." and "p.m." If you are loading Chinese data into a date or time field, define the appropriate mask to suppress the double-byte data. Within date and time masks, each underscore character represents a single byte that you intend to mask out; therefore, you must specify the exact number of underscores to match the number of bytes you want to suppress. For example, a double-byte representation of a year value requires two underscores in the mask.

For detailed information about defining date and time masks, refer to Chapter 3 of the Table Management Utility Reference Guide.

ALTER TABLE Statement Might Run Out of Space

When VARCHAR columns are involved, it is not possible to calculate in advance the exact space requirements of an ALTER TABLE statement. Informix recommends that you always have a backup of a table before altering it, in case the statement fails because it runs out of space.

Enabling Parallelism for a STARjoin

The ROWS_PER_JOIN_TASK parameter enables parallelism for the STARjoin. If the number of join tasks is less than 1, then parallelism is not enabled for both the fetch and join phases. For more details, refer to "Enabling Parallelism for a STARjoin" on page 11-19 of the V6.0 Administrator's Guide.

B. Query-Related Issues

JDBC Date String Results

Due to a Java bug, the JDBC driver will not return an error for invalid date strings in getDate, getTimestamp, or getTime commands. For example, getDate (col ('9999-12-33')) returns the date "10000-01-02" and getDate(col('1999-02-29')), a date that does not exist, returns the date "1999-03-01."

LIKE Constraints on VARCHAR Columns with Trailing Spaces

A query with a HAVING clause that contains a LIKE constraint on a VARCHAR column can return unexpected results when trailing spaces are present. For example, consider the following table and query:

According to the SQL standard, this query should return no rows or the row ('a ', 2). The Red Brick server, however, always returns the row ('a ', 1) because the HAVING predicate is evaluated before the result set is grouped.

C. Client Tool Issues

Administrator Tool

You cannot use the current Administrator tool (V2.0) with prior releases of the Red Brick server. Likewise, you cannot use prior versions of the Administrator tool (V1.0.x) with Red Brick Decision Server 6.0. If you attempt to use incompatible versions of the Administrator tool and Red Brick Decision Server, an error message is displayed.

Graphical EXPLAIN

When you choose the graphical option to display EXPLAIN statement output, make sure that the ASYNCHRONOUS box is not checked. Checking the ASYNCHRONOUS box results in an application error.


VIII. Documentation Notes

The Red Brick Decision Server document set was updated for V6.0.2. An Answers OnLine CD containing these manuals in electronic format is provided with your product. You can also view these manuals on the Informix Web site: www.informix.com.

This section contains information that was too late for inclusion in the published manuals or introduced for V6.0.3.

A. Installation and Configuration Guide for UNIX Platforms

On page D-4, cut the following text from the load_processes description:

"If not specified, use 0."

Replace it with the following statement:

"If there are no parallel load or parallel REORG operations, use 0. Otherwise use 3+(3*num_cpus) +max_nuniq_idx."

On pages G-3, H-3, and I-4, cut the following text from the load_processes description:

"If no parallel loads, use 0. Otherwise, use: 3 + MAX (1, num_cpus/2) + max_nuniq_idx."

Replace it with the following statement:

"If there are no parallel load or parallel REORG operations, use 0. Otherwise use 3+(3*num_cpus) +max_nuniq_idx."

B. Administrator's Guide

1. Change the bullet on page 6-13, "Versioning operations with SET LATEST VERSION ON," to read, "Versioning operations with SET USE LATEST REVISION ON."

2. Replace TEMP with temporary in the second bullet on page 6-13.

3. At the top of page 6-14, delete the section that begins:

"Operations that will not work with frozen versions, include the following..."

Replace it with the following text:

To use the following statements on a versioning database in frozen version mode, first override frozen versioning with the SET USE LATEST REVISION ON command. For more information about turning frozen versions on or off, refer to "Controlling Frozen Versions" on page 6-18.

The following operations are allowed in frozen version mode:

The following operations are not allowed in frozen version mode:

4. Under "Freezing the Database" on page 6-18, delete the following sentence:

For the revision number of the database being accessed by the current RISQL session, see the READ_REVISION column of the DST_COMMANDS table.

Replace it with:

To find the revision number of the database that your RISQL command is accessing, type:

5. Replace the third paragraph under "Daemon Processes" on page 9-50 with the following paragraph:

If the administration daemon goes down for some reason but the warehouse daemon is still running, restart the administration daemon separately by running the rbwadmd executable from the /redbrick/bin directory. To stop the administration daemon, use the ALTER SYSTEM TERMINATE ADMIN DAEMON command.

6. Replace the section "Obtaining Information on Tables and Indexes" on page 9-13 with the following sections:

Obtaining Information on Tables and Indexes

You can check for and optionally repair damage to table storage data structure and obtain segment statistics (including row counts) for tables with the CHECK TABLE statement. You can check an index for corruption and obtain configuration and size information with the CHECK INDEX statement.

Checking and Repairing Table Structure

When you specify the FIX option, the CHECK TABLE statement marks as damaged any damaged segment that it finds. After you have repaired the segment, use the alter segment...verify statement to verify that the segment has been repaired and mark it as undamaged. You must complete this step before you use the CHECK TABLE statement on the table again.

CHECK TABLE Segment Statistics

When you specify the VERBOSE option, the CHECK TABLE statement generates the following segment statistics in the output file:

(1 of 2)

CHECK TABLE Segment Statistics Field Description
active rows

Number of rows that reside in the segment.

deleted rows

Number of rows deleted from the segment.

total blocks

Number of 8-kilobyte blocks in the segment.

free blocks

Number of blocks that currently do not contain data rows.

storage reclen

Length (in bytes) the server expects each record to be, based on the fill factor of VARCHAR columns. The typical row size formula in "Example 1: Effect of the VARCHAR Fill Factor on Number of Rows Per Block" on page 10-30 calculates this value.

average reclen

Actual average size (in bytes) of records (rows) in the segment. The average row size differs from the storage reclen value when the row contains one or more VARCHAR columns.

indirect rows

Number of rows that reside on a different page than where they originally were loaded or inserted. Indirect rows occur when not enough space exists on the page to store a longer row that resulted from an update of a VARCHAR column.

max reclen

Length (in bytes) of the row when all VARCHAR columns contain the longest possible string.

longest rec

Actual longest row length (in bytes) that exists in the segment.

min reclen

Length (in bytes) of the row when all VARCHAR columns are null or empty.

shortest rec

Actual shortest row length (in bytes) that exists in the segment.

freespace

Number of bytes of unused space in the allocated blocks in a segment. The Example: Freespace Segment Statistics in CHECK TABLE Output section of these release notes illustrates this value.

rows/block

Number of row numbers the server allocates per block based on the fill factor of VARCHAR columns. "How the Server Uses the VARCHAR Fill Factor" on page 10-28 shows the formula to calculate this value.

unused row IDs

Number of row numbers currently not used by actual rows.

unusable freespace

Number of bytes of free space in blocks where all assigned row numbers are used and the amount of free space exceeds the size of a typical row. Nonzero values appear in this field when the fill factor is higher than the actual rowsize as "Example 2: VARCHAR Fill Factor Too High" on page 10-31 illustrates.

Example: Segment Statistics in CHECK TABLE with VERBOSE Option

This example shows the CHECK TABLE statement with the VERBOSE option for the Sales table from the Aroma database.

Because the Sales table consists of two segments, the CHECK TABLE statement produces two output files. The name of each output file for the previous CHECK TABLE statement starts with the table and segment numbers as the following filename shows:

Figure 1 shows the contents of the output files that the CHECK TABLE statement with the VERBOSE option produces for the Sales table.

Figure 1:
Contents of Two Output Files from CHECK TABLE with VERBOSE Option

In this example, the CHECK TABLE output shows equal values for the following segment statistics because no VARCHAR columns exist in the table: storage reclen, average reclen, max reclen, longest rec, min reclen, shortest rec

The value in the rows/block field is the same as the rows per block that the database server calculates with the following formula:

The value in the freespace field is the number of unused bytes in existing allocated blocks. For more information, refer to the Freespace Field section of these release notes.

For a description of how to use the CHECK TABLE segment statistics to determine the effectiveness of the VARCHAR column fill factor, refer to "Using CHECK TABLE with the VERBOSE Option" on page 10-34.

7. Replace the sections "USED Column" and "TOTALFREE Column" on page 9-16 with the following sections.

USED Column

The USED column in the RBW_STORAGE system table indicates how much of the PSU has been allocated so far-the largest amount of space the PSU has ever occupied. This number also provides the lower limit on the new MAXSIZE in the CHANGE MAXSIZE option for the ALTER SEGMENT statement.

The USED value is not necessarily the amount of space used in the PSU because some of the USED space might actually be on an internal free-storage list. To see how much free space is available in the used blocks, use the CHECK TABLE statement with the VERBOSE option, as the Freespace Field section of these release notes describes.

Example: MAXSIZE and USED Columns in RBW_STORAGE

The following example shows the values of the MAXSIZE and USED columns in the RBW_STORAGE system table for the Sales table from the Aroma database:

The USED column shows that the first PSU in segments DAILY_DATA1 and DAILY_DATA2 have reached the MAXSIZE value of 1024 kilobytes. A second PSU was allocated to each segment.

TOTALFREE Column

The TOTALFREE column in the RBW_SEGMENTS system table contains the amount of free space available to the segment, whether the segment is associated with an index or a table. This value assumes that the file system contains sufficient space to allow the segment to grow to its maximum size.

For tables, if no rows have been deleted from a table, the difference between MAXSIZE and USED space in RBW_STORAGE equals TOTALFREE space in RBW_SEGMENTS for the segment(s) associated with that table. The Example: TOTALFREE Column in RBW_SEGMENTS section of these release notes illustrates this situation.

Red Brick Decision Server reuses space by row, which means that when a row is deleted from a table, the next row added to the table is stored in the location of the last deleted row. Therefore, after several rows residing in a given segment have been deleted from a table, that segment contains free space where those rows used to be stored. The value for TOTALFREE measures only the space that has not yet been used, not the space freed by deleting rows. If you have deleted large numbers of rows from your table, you might have more free space than the value of TOTALFREE indicates.

To see how much free space is available in the used blocks, use the CHECK TABLE statement with the VERBOSE option, as the Freespace Field section of these release notes describes.

Example: TOTALFREE Column in RBW_SEGMENTS

The following query obtains the TOTALFREE column in the RBW_SEGMENTS system table for the Sales table from the Aroma database:

The Example: MAXSIZE and USED Columns in RBW_STORAGE section of these release notes shows these values for the Sales table from the Aroma database. The following equation shows that no rows have been deleted:

8. Add the following "Freespace Field" section to "Monitoring Growth of Tables and Indexes" on page 9-13.

Freespace Field

The freespace field in the output of the CHECK TABLE statement with the VERBOSE option displays the number of bytes in existing allocated blocks in the table that are not used. To obtain a more accurate value of unused space within the segment, add this freespace value with the TOTALFREE column in the RBW_SEGMENTS system table.

Example: Freespace Segment Statistics in CHECK TABLE Output

The following example shows the values of the freespace field in the CHECK TABLE output and of the TOTALFREE column in the RBW_SEGMENTS system table for the Sales table from the Aroma database:

In this example, the freespace field value is 741 bytes for the first segment. The following equations show that this freespace value is the number of unused bytes in the allocated space:

The Example: TOTALFREE Column in RBW_SEGMENTS section of these release notes shows that the TOTALFREE value for the Sales table in the Aroma database is 992 kilobytes. Use the following equation to calculate the total unused space in the first segment of the Sales table:

9. Add the following two entries to the table on p. B-13.

OPTION CHECK_REPORT_FILE_PERMISSIONS

Specifies file permissions on the reports generated by the CHECK TABLE and CHECK INDEX statements. When set to SERVER_OWNER, the redbrick user has read/write permission; when set to SERVER_GROUP (on UNIX only), the group has read-only permission; when set to ALL, all users have read-only permission. Possible values: SERVER_OWNER, SERVER_GROUP (UNIX), ALL. Default: SERVER_OWNER

OPTION GRANT_TEMP_RESOURCE_TO_ALL

Grants authorization to create temporary tables. Possible values: ON, OFF. Default: ON

10. Add the following bullet to the list of database limits on p. 1-22.

Queries can contain a maximum row size of approximately 8 kilobytes in intermediate and final result tables. This limit is equivalent to the maximum size of a row in a table.

11. Add the following sentence to the CREATE TABLE Statements section on p. A-9.

To facilitate the creation of hierarchies, all columns for all tables are declared NOT NULL in the CREATE TABLE statements for the Aroma database.

C. ODBC Connectivity Guide

Add the following sentence to the bottom of p. 1-5:

If you use Red Brick Decision Server Version 6.0 with Red Brick ODBC Driver Versions 4.00.x, 5.00.x, or 6.00.x, you cannot use the VARCHAR data type. Versions 4.01.x, 5.01.x, and 6.01.x support the VARCHAR data type.

D. SQL-BackTrack User's Guide

Replace the syntax given for the rb_btrecover program on pages 6-8 and E-3 with the following syntax.

E. SQL Reference Guide

Replace the definition of order_reference for the RESET BY subclause on page 7-53:

The reference can be:

Add the following command description to page 8-30:

The ALTER SEGMENT...OPTICAL command marks an existing segment as stored on a near-line, or optical, storage device. The diagram shows the following syntax: ALTER SEGMENT segment_name OF TABLE table_name or OF INDEX index_name OPTICAL ON or OFF.

Setting a segment to OPTICAL ON sets the Optical column in the RBW_SEGMENTS system table to Y, allowing you to control access to the segment with the SET OPTICAL AVAILABILITY commands documented on page 9-33. If the segment is set to OFF, the segment is not recognized as optically stored and the availability commands have no effect.

If you are using HP OmniStorage 2.2 software and the operating system fails to recognize that a segment is stored optically, you can use the ALTER SEGMENT...OPTICAL ON command to force or reset the segment's Optical column to Y. This might be necessary after running any of the following commands on an optical segment:

Replace the description of the FIX option on p. 8-91 with the following paragraph:

Specifies that the check table operation should fix any problems it encounters. If any repairs are made, you must then run the ALTER SEGMENT...VERIFY statement to check that the segment has been repaired, followed by another Check Table...FIX statement. If the FIX option is specified, Check Table will only run serially.

Replace the description under "UNIQUE" on page 8-137 with the following text:

Declares that duplicate values are not allowed in the column. Declaring a column UNIQUE does not enforce uniqueness on the column; to enforce uniqueness, you must also build a B-TREE index on the column. To enforce uniqueness over a combination of columns, declare all of the columns as UNIQUE, then create a single index (B-TREE or STAR) over those columns. The columns do not have to be primary key columns.

Consider the following CREATE TABLE statement:

The following statement:

creates a unique B-TREE index. However, the following statement:

creates a non-unique B-TREE index.

Note: If only the combination of columns is unique and no single column by itself is unique, an index must not be defined on the single column; if it is, uniqueness will be enforced on that single column.

Add the following paragraph to the output_file_specification for EXPORT on p. 8-188

To pipe the output of the EXPORT statement to a TMU LOAD DATA operation, you must specify the -d argument for the rb_tmu executable. For example, the following EXPORT statement pipes its output to a TMU operation that loads a table in the Aroma database:

For the full syntax of the rb_tmu and rb_ptmu executables, refer to Chapter 2 of the Table Management Utility Reference Guide.

Add the SET CHECK REPORT FILE PERMISSIONS command to Chapter 9.

The SET CHECK REPORT FILE PERMISSIONS command controls access to output files created by the CHECK TABLE and CHECK INDEX commands. This command overrides the OPTION CHECK_REPORT_FILE_PERMISSIONS parameter in the rbw.config file.

Syntax

The following diagram shows how to construct a SET CHECK REPORT FILE PERMISSIONS statement:

The diagram shows the following syntax: SET CHECK REPORT FILE PERMISSIONS followed by SERVER_OWNER, SERVER_GROUP, or ALL.

SERVER_OWNER

By default, the output from the CHECK TABLE and CHECK INDEX commands is readable only by the redbrick user.

SERVER_GROUP

Gives read-only permission to the specified group of UNIX users. This setting is valid only on UNIX platforms.

ALL

Gives read-only permission to all users.

Add the SET CHECK_TABLE_INDEX_DIRECTORY command to Chapter 9.

The SET CHECK_TABLE_INDEX_DIRECTORY command specifies a default directory for the summary files when the directory path is not specified in the CHECK TABLE or the CHECK INDEX command. This command overrides the OPTION CHECK_TABLE_INDEX_DIRECTORY parameter in the rbw.config file. If a directory is specified in the CHECK INDEX or CHECK TABLE command, it overrides the SET command and OPTION parameter.

The following diagram shows how to construct a SET CHECK_TABLE_INDEX_DIRECTORY statement:

The diagram shows the following syntax: SET CHECK_TABLE_INDEX_DIRECTORY followed by the directory_name inside single quotes.

where 'directory_name' specifies the full path of directory where the summary files will be placed.

F. Table Management Utility Reference Guide

Remove the restriction "PTMU Only" from the Memory Map Limit section on p. 2-35.

The TMU setting for memory mapping indexes applies to both TMU and PTMU operations.

Replace "No FORMAT keyword" with "No FORMAT clause" in metavariable table on p. 3-30. Replace "FIXED" metavariable with "No FORMAT keyword" in metavariable table on p. 3-31.

There is no keyword called FIXED.

Add the following to the SUBSTR metavariable definition in the Character Fieldtype section on
p. 3-85:

Data loaded in the table column can be unpredictable if the substring start position is not 0, or the substring length is greater than the table column length.

Make the following changes to the "Variable-Format Records" section on p. 3-110 and 3-111:
  1. Delete the sentence "The TMU determines the record length from the defined size of RECORDLEN in the FORMAT clause of the LOAD DATA statement according to the following rules."
  2. Replace it with "The TMU determines the fixed-length part of the record from the defined size of FIXEDLEN in the FORMAT clause of the LOAD DATA statement according to the following rules."

  3. In the bulleted section, change all four occurrences of "RECORDLEN" to "FIXEDLEN".
On p. 4-23:

The last line of the market.txt example is not aligned. Delete the first "0" from "000...5".

Replace the column name "Character Set" with "Code Page" in the table in Appendix C. Replace the column name "Sort" with "Collation Order" in the table in Appendix C.

G. Tech Note on Loading Data in VARIABLE Format

The VARCHAR datatype is a new feature in this release.

The existing TMU input formats SEPARATED and FIXED can be used to load data into a table with VARCHAR columns. To save storage on the input file and speed up the input reading, a more compact TMU input file format called VARIABLE format has been added. The TMU supports both loading from and unloading to files in this new format.

This section intends to give an overview of the new format. The accurate syntax can be found in the Table Management Utility Reference Guide.

1. Input Record Format

Format VARIABLE is a modified version of format FIXED. In addition to existing TMU fieldtypes, two new TMU fieldtypes, VARLEN and VARLEN EXTERNAL, are added. An input record for VARIABLE format consists of a fixed-length part and a variable-length part. Every input record has the same length for the fixed part and may have a different length for the variable part. For a variable-length TMU field column, the length of the column is in the fixed part and the real data of the column is attached in the variable part.

The fixed part is structured the same as format FIXED. The variable part consists of multiple variable-length character data sections. The length of a data section is described by the corresponding VARLEN or VARLEN EXTERNAL field in the fixed part. The first data section starts at the end of the fixed part. The subsequent data sections' starting offsets are defined by the total length of the preceding data sections.

2. VARLEN and VARLEN EXTERNAL TMU fieldtypes

VARLEn and VARLEN EXTERNAL TMU fieldtypes are only allowed in VARIABLE format. Both fieldtypes themselves are in fixed length and their positions can be described using POSITION keywords and/or field length specifications. The output column for the VARLEN or VARLEN EXTERNAL field can be a real column in either VARCHAR or CHARACTER datatype or a pseudocolumn.

VARLEN is the binary form of the length for the data section in the variable part. If the field length is larger than 2, only the first two bytes are used. Similarly, VARLEN EXTERNAL is the ASCII/EBCDIC external form of the data length.

After the data section is extracted from the variable part, operations to the VARLEN and VARLEN EXTERNAL fields are actually performed on the character data section. TRIM, LTRIM, and RTRIM modifiers, as well as SUBSTR, are allowed and performed on the data section. When used together with a pseudocolumn, the character data fills the pseudocolumn in the exact length of the data section.

When multiple VARLEN or VARLEN EXTERNAL fields are present in the TMU control file, the order of data sections that appear in the variable part should be in the same order as the length field in the fixed part. However, the order in which VARLEN or VARLEN EXTERNAL fields appear in the control file is not relevant. In the case of two VARLEN or VARLEN EXTERNAL fields with the same starting position, the length of the two fields should be the same. That means they refer to the same data section in the variable part. If the length is different, the TMU gives an error and stops.

3. Input Record Reading

Like FIXED format, the TMU can perform two kinds of input record reading based on the FORMAT clause in the TMU control file: read by specified length and read by newline.

Read by Specified Length

Since the input record is in variable length, the RECORDLEN keyword no longer applies. Instead, the keyword FIXEDLEN should be used to indicate the total length for the fixed part of the record. Another keyword, INTRA RECORD SKIP, is added to tell the TMU to skip a certain number of bytes between input records. It is mainly for skipping newlines '\n' or ' \n' between records.

For each input record, the TMU reads the fixed part in the specified length. The TMU converts each VARLEN or VARLEN EXTERNAL field into a number and adds the numbers together as the total length of the variable part. The TMU then reads the variable part according to that length. If INTRA RECORD SKIP is present, the TMU bypasses the specified bytes before moving on to the next input record.

If the length characters in VARLEN and VARLEN EXTERNAL fields are not legal digits, the TMU stops and gives an error message. If the length characters are in legal digits but do not reflect the real length of the data section, the TMU tries to detect it. However, it is possible that such length errors can cause the TMU to read in the following records in the wrong boundary.

Read by Newline

Similar to FIXED format, if the FIXEDLEN keyword is not present, the TMU reads the input record line by line. No binary data is allowed in the input record. The TMU does not parse the VARLEN fields in the reading process.

As every record is delimited by the newline, the length error in a particular record will not spread over to the following records. Only the problematic record is discarded.

Data Section Extraction

After the TMU reads in the input record, the TMU converts it into an internal row. In the appearance order of VARLEN and VARLEN EXTERNAL fields in the fixed part, TMU calculates the offset and length of each data section in the variable part. The data sections will then be put in the corresponding output column of the internal row.

4. Observations and Recommendations

The VARIABLE format is more compact than the FIXED format and also preserves significant trailing spaces. On the other hand, the data input file is more complicated. Here are some observations and recommendations for using this format:

5. Example:

CREATE TABLE statement to create a market table:

The input file market.txt (newline between records):

00000000001

000007 GA

000007 South

AtlantaAtlanta

00000000002

000005 FL

000007 South

MiamiAtlanta

00000000003

000011 LA

000011 South

New OrleansNew Orleans

00000000004

000007 TX

000011 South

HoustonNew Orleans

00000000005

000008 NY

000008 North

New YorkNew York

Load data in VARIABLE format:

1. Read by Newline

2. Read by Specified Length

Data after loading:

H. Messages and Codes Reference Guide

The following messages have been either added or changed in V6.0.3.

RBL378F Alphanumeric literal 'string' exceeds 1024 bytes.
Cause: The load process terminated.
Response: Correct the LOAD DATA statement.
RBS734E The column 'column_name' does not identify a foreign key.
Cause: The user attempted to create a STAR index with a column that has not been defined as a foreign key.
Response: If the column is part of a multi-column foreign key, use the foreign key constraint name rather than the column name. If the foreign key constraint does not exist you can create it with an ALTER TABLE ADD CONSTRAINT statement.
RBR826E Output file system is full. Cannot continue execution.
Cause: The file system where the output file is placed is full. Either output or error message cannot be written.
Response: Change the output file to a different file system or remove some unnecessary files from the current file system.
RBA654E Data cannot be inserted into the table because the maximum number of rows per segment has been reached string.
Cause: The number of rows in the segment into which this row is to be inserted is equal to the table's MAXROWS PER SEGMENT. No more rows can be inserted into the table until the MAXROWS PER SEGMENT value is changed. If the row contains VARCHAR columns, the actual number of rows in the table may be less than MAXROWS PER SEGMENT. If there is a large difference between the number of rows and MAXROWS PER SEGMENT, the FILLFACTORs for VARCHAR columns have been set wrongly.
Response: ALTER the MAXROWS PER SEGMENT value for the table. If there is a big difference between the number of rows in the segment and MAXROWS PER SEGMENT, refer to the discussion on setting FILLFACTOR for VARCHAR columns in the Warehouse Administrator's Guide.
RBS1649E Segment segment of index index is not directly associated with the table table.
Cause: The index segment is not defined on the specified table.
Response: Correct command and re-submit.
RBL1811E SUBSTR start position must be greater than zero.
Cause: The SUBSTR function in the TMU takes two positive integers as arguments. The first argument indicates the starting position and the second indicates the maximum length of the string.
Response: Correct the error and submit the command again.
RBL1812E SUBSTR length argument must be non-negative.
Cause: The SUBSTR function in the TMU takes two positive integers as arguments. The first argument indicates the starting position and the second indicates the maximum length of the string.
Response: Correct the error and submit the command again.
RBS1974E The precomputed view filename is invalid.
Cause: The precomputed view became invalid before execution but must be valid for Vista query rewrite.
Response: Make sure the precomputed view is valid and submit the statement again.
RBS7518E Current PSU EXTENDSIZE value (number KB) exceeds the PSU MAXSIZE (number).
Cause: The current EXTENDSIZE of the PSU is greater than its MAXSIZE.
Response: Set the EXTENDSIZE of the PSU smaller than the current PSU MAXSIZE.
RBS7519E Invalid unit string in string. Valid units are k (Kilobytes), m (Megabytes) and g (Gigabytes).
Response: Change the unit in the rbw.config file or by using a SET command.
RBS7520E Default segment size is outside the valid range of 16K and 512G.
Response: Change the default segment size in the rbw.config file or by using a SET command.
RBS7521E Default PSU extend size is outside the valid range of 0 and 2G.
Response: Change the value in the rbw.config file or by using a SET command.



Copyright 2000, Informix Software, Inc. All rights reserved