BTRV Edition User's Guide

Type Mapping Table

To define an XDD file requires defining the data type of each column of the original data file. The following table describes the accepted XDD data types:

Type to be used in the XDD field specification	Type description
BT_Numeric	ASCII string representing a signed number with trailing sign, A convention (NUMERIC)
BT_NumSA	ASCII string representing a signed number with trailing sign, M convention (NUMERICSA)
BT_NumSTS	ASCII string representing a signed number with trailing separate sign (NUMERICSTS)
BT_NumSLS	ASCII string representing a signed number with leading separate sign (NUMERICSLS)
BT_NumSLB	ASCII string representing a signed number with leading separate sign, B convention (NUMERICSTB)
BT_NumSTB	ASCII string representing a signed number with trailing separate sign, B convention (NUMERICSLB)
BT_Char	Fixed-length ASCII string (CHAR)
BT_Float	Float or Double values (FLOAT)
BT_Decimal	Packed string representing a signed number in A convention. (DECIMAL)
BT_Money	Packed string representing a signed number in A convention (MONEY)
BT_AutoIncrement	Integer number represented in native O/S binary signed format (AUTOINCREMENT)
BT_Bit	1 or more bits (in case of sequences of BT_BIT) in 1-byte. We do not support having sequences of two or more BT_BIT fields.
BT_Currency	8-byte signed quantity, sorted and stored in Intel binary integer format (CURRENCY)
BT_BinaryBuffer	Binary buffer (CHAR)
BT_Date	4-byte value, day in the first byte, the month in the second byte, and the year in a two-byte word following the month. The year is expected to be set to the integer representation of the entire year (DATE)
BT_Integer	Integer number represented in native O/S binary signed format (INTEGER)
BT_UnsignedBinary	Integer number represented in native O/S binary unsigned format (UNSIGNED BINARY)
BT_Time	4- byte value. Hundredths of a second, second, minute, and hour values are each stored in 1-byte binary format (TIME)
BT_LString	Regular STRING type, except that the first byte of the string contains the binary representation of the string's length (LSTRING)
BT_ZString	A C string with the same characteristics as a regular string type except that it is terminated by a binary 0 (ZSTRING)

See also <schema> table element.

In This Section Variable-length fields mapped into LONGVAR* SQL field

Variable-length fields mapped into LONGVAR* SQL field

Variable length fields map into SQL LONGVAR* fields.

The XDD structure allows the following elements and attributes for variable length field support.

1. dbtype values:
   • BLOB: Indicates a variable-length binary object with length depending on a field value.
   • CLOB: Indicates a variable-length text object with length depending on a field value.
2. <field> attribute sizefield used in conjunction with dbtype BLOB or CLOB and having size = "0"

For an XDD field mapped into a LONG VARCHAR or LONG VARBINARY, the following conditions must be met:

1. The field definition must have:

   size="0"

   sizefield="X" where "X" is a valid field containing the number of bytes (we suggest this field to be hidden, but this is not mandatory)

   dbtype="clob" or dbtype="blob"

2. At maximum, one and only one field mapped to a BLOB or CLOB type.
3. It must be the last field in the record buffer.

If one (or more) of the above condition is not met, error CTDBRET_CALLBACK_11 ("Unsupported clob/blob definition") is returned.

Troubleshooting Data Conversion Errors

The fact that the XDD initially does not contain any error handling information immediately exposes data conversion errors to a SQL user. This provides the way to begin troubleshooting data conversion errors and to identify the proper settings to specify in your XDD file.

Visually Check Data with FairCom DB SQL Explorer

FairCom DB SQL Explorer and FairCom DB Explorer include a button to simplify identifying "bad" records.

To check for bad records in FairCom DB SQL Explorer or FairCom DB Explorer:

1. Select a sqlized table, such as custmast in the image above.
2. Click the Table Records tab.
3. A button labeled Check Bad Records appears at the right of the row of buttons (the image above shows the Java version of FairCom DB Explorer where the buttons are at the top of the tab; the .NET version, called FairCom DB SQL Explorer, shows this row of buttons at the bottom of the tab).
4. Click the button to execute a SQL query to find records that did not sqlize properly.

Check using a SQL Query

You can use the procedures in this section to identify data-conversion errors and use that information to fine-tune your XDD files.

If a query fails, it is possible the failure is due to a problem with SQL data conversion. Troubleshooting this type of error is quite easy with the following steps:

1. Identify the table (in case of a complex query) on which the conversion error occurs by running the following SQL statement on each table involved in the query:

   SELECT * FROM <table>

   If none of the queries fail, the original query failure is not due to a conversion problem.

2. Run the following SQL statement to select only the records that do not properly convert:

   SELECT * FROM <table> ctoption(badrec)

   ctoption(badrec) is a FairCom DB extension to SQL indicating the query should return only records having conversion errors and expose values that do not properly convert as NULL.

3. Look for NULL values returned from the query in step 2. These are the fields that do not properly convert. The remaining record values should be sufficient to identify the record that requires investigation.

CTSQLCBK.FCS Log

The ctoption(badrec) command generates a log file, ctsqlcbk.fcs, in the FairCom DB SQL Server directory that can be used to determine the exact conversion error and the data causing it. This file lists all the fields that caused a conversion error exposed in SQL along with the value of the data that could not be converted. Note that the log contains information for the fields that result in propagating the conversion error to SQL. It does not log conversion errors that result in a SQL value because they were already handled successfully following the settings in the XDD.

Each log entry is made of three lines:

1. The first line is similar to the following:

   Convert error XXXX on field[YYYY]

   Where XXXX is the error code indicating the cause of the conversion error, YYYY is the field on which the conversion error occurred.

2. The second line contains a message which gives internal information for FairCom technicians to identify where the error occurs in the code, as well as a message explaining the problem.
3. The third line is a hexadecimal dump of the field content.

For example:

Convert error 4028 on field[FIELD1]

{ctdbStringFormatToDateTime} on 0000000000 failed

00000000

Convert error 4028 on field[FIELD2]

{ctdbStringFormatToDate} on 00000000 failed

3030303030303030

Convert error 4118 on field[FIELD3]

[NormalizeCobolStr] ascii value is not a digit

2020202020202020

Convert error 4118 on field[FIELD4]

[NormalizeCobolStr] ascii value is not a digit

2020202020202020