BTEQ supports VARTEXT records by converting each delimited data item in the input record into a VARCHAR data item. An empty data item causes the data item's corresponding field to be nulled.
BTEQ interprets the following as empty data items:
- Two adjacent delimiters
- A delimiter as the very first character of the record
- A delimiter as the very last character of the record
A character other than the default “|” can be specified as the data item delimiter. The following rules for specifying a valid optional character apply:
- No control character other than TAB can be a delimiter.
- Any character that appears in the data cannot be a delimiter.
- Delimiters can only be a single-character sequence.
- For Workstation BTEQ, the delimiter can be a multibyte character for Unicode sessions only.
- For Mainframe BTEQ, the delimiter must always be a single-byte character.
Rules for All Platforms
- The only acceptable data types for VARTEXT records are VARCHAR, VARBYTE, and LONG VARCHAR. Undesirable results occur if other data types are used.
- Because VARTEXT files are opened in text mode, unexpected results might occur if VARTEXT data contains embedded control characters such as null (0x00), linefeed (0x0A) or sub (0x1A). Control characters are accepted for DATA and INDICDATA imports.
- The number of data items in the input record must be equal to the number of fields defined in the USING clause.
- Each trailing delimiter at the end of an input record represents a null value to be generated by BTEQ. The null must have a corresponding field defined in the USING clause. If there are missing USING fields, the import might fail because the number of indicator bit bytes sent is greater than the number the DBS calculates to be needed according to the USING clause. BTEQ relies on the DBS to detect this problem. The failure, typically, is indicated by an 2673 error with a message stating the source parcel length does not match the data defined. There are two alternatives ways to solve this problem so the expected number of indicator bytes matches what BTEQ sends. The first alternative is to not use a trailing delimiter. The second is to add a dummy last column to the USING clause.
- Two consecutive delimiter characters specify that the corresponding field should be nulled. If the record starts with a delimiter, the first field is nulled.
- There should always be one less delimiter than the total number of columns specified in the USING clause.
Rules for Workstation Platform
Each record must end with an end-of-record (line) indicator, appropriate for the workstation and operating system. For example, for a UNIX system, the indicator must be a line feed character. For Windows, the indicator must be a carriage return/line feed pair. In all cases, the end-of-record character, or characters, must be in the bit-format appropriate for the workstation.
Rules for Mainframe Platform
- If a space character is used as delimiter, no empty trailing fields are valid in the record. If one or more fields are null at the end of a record, and the delimiter character is a space, the format of the record is not preserved. The trailing nulls and spaces are truncated, resulting in a DBS error when the record is inserted into a table.
- To make trailing spaces part of the data, end the record with a delimiter.
- When using a Unicode session charset, the delimiter character employed in VARTEXT import Unicode data files must be a Unicode code point equivalent to the delimiter character employed for the associated EBCDIC IMPORT command within the z/OS BTEQ script. There are certain EBCDIC characters that need special consideration to ensure the code points match. For example, if EBCDIC has two vertical bar characters: a solid bar (0x4F) and a broken bar (0x6A).
- The EBCDIC solid bar character maps to 0x008D in UTF-16BE and is called a vertical line.
- The EBCDIC broken bar character maps to 0x007C in UTF-16BE and is called the broken bar.
Use SET REPEATSTOP ON to stop importing data if an error is encountered during processing a VARTEXT record. By default it rejects the record and continues.