| Informix Online Documentation |
Date: 05/19/00
Version: 6.0.3
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.
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 |
Red Brick Decision Server V6.0.3 contains the following new features:
Red Brick Decision Server V6.0.2 contains the following new features:
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:
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 following SET commands would achieve the same result:
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).
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.
Informix Red Brick Decision Server V6.0.3 and associated products are certified to run on the following Windows 2000 platforms:
Red Brick Decision Server V6.0.3 allows TARGET indexes on VARCHAR columns. In V6.0.2, such indexes were disallowed.
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.
To use versioning on Compaq Alpha machines, you must apply the following patch:
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.
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:
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.
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.
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.
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.
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-BackTrackSQL-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.
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
Read the following sections before installing V6.0.3 over a previous release of the Red Brick server.
Installation ChangesThe 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)
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.
To install Red Brick Decision Server V6.0 on a UNIX computer, you need at least 145 megabytes of free disk space.
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.
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:
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.
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.
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.
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.
This section describes known issues in Red Brick Decision Server V6.0 and is organized as follows:
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 LoadsFor 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 SpaceWhen 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 STARjoinThe 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.
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 SpacesA 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.
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 EXPLAINWhen 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.
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.
"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."
"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:
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:
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.
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 StructureWhen 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 StatisticsWhen you specify the VERBOSE option, the CHECK TABLE statement generates the following segment statistics in the output file:
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.
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_STORAGEThe 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 ColumnThe 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_SEGMENTSThe 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:
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 OutputThe 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:
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
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.
To facilitate the creation of hierarchies, all columns for all tables are declared NOT NULL in the CREATE TABLE statements for the Aroma database.
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.
The reference can be:
The ALTER SEGMENT...OPTICAL command marks an existing segment as stored on a near-line, or optical, storage device. 
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:
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-188To 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.
SyntaxThe following diagram shows how to construct a SET CHECK REPORT FILE PERMISSIONS statement:
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:
where 'directory_name' specifies the full path of directory where the summary files will be placed.
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 onData 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:
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."
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.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 FormatFormat 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 fieldtypesVARLEn 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 ReadingLike 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 LengthSince 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 NewlineSimilar 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 ExtractionAfter 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 RecommendationsThe 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:
CREATE TABLE statement to create a market table:
The input file market.txt (newline between records):
Load data in VARIABLE format:
1. Read by Newline
2. Read by Specified Length
Data after loading:
| MKTKEY | HQ_CITY | HQ_STATE | DISTRICT | REGION |
|---|---|---|---|---|
| 1 | Atlanta | GA | Atlanta | South |
| 2 | Miami | FL | Atlanta | South |
| 3 | New Orleans | LA | New Orleans | South |
| 4 | Houston | TX | New Orleans | South |
| 5 | New York | NY | New York | North |
The following messages have been either added or changed in V6.0.3.
