Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 228 / 536

Converting data on insertion
208
Column width issues
Adaptive Server IQ assumes the width of the input data is the same as the
destination column width and reads the input file accordingly. If they are not
the same width, Adaptive Server IQ may read too few or too many bytes of the
input file for that column. The result is that the read for that column may be
incorrect, and the reads for subsequent columns in the input file will be
incorrect, because they will not start at the correct position in the input file.
For example, if
input_column1
is 15 bytes wide and
destination_column1
is 10
bytes wide, and you do not specify the
ASCII
conversion option, Adaptive
Server IQ assumes the input column is only 10 bytes wide. This is fine for
destination_column1
, because the input data is truncated to 10 bytes in any case.
But it also means that Adaptive Server IQ assumes that the next column in the
input file starts at byte 11, which is still in the middle of the first column,
instead of at byte 16, which is the correct starting position of the next column.
Conversely, if
input_column1
is 10 bytes wide and
destination_column1
is 15
bytes wide, and you do not specify the
ASCII
conversion option, Adaptive
Server IQ assumes the input column is 15 bytes wide. This means that
Adaptive Server IQ reads all of
input_column1
plus 5 bytes into the next column
in the input file and inserts this value into
destination_column1
. So, the value
inserts into
destination_column1
and all subsequent columns are incorrect.
To prevent such problems, use the
ASCII
conversion option. With this option,
Adaptive Server IQ provides several ways to specify the fixed or variable
width of an input column. Your input data can contain fixed width input
columns with a specific size in bytes, variable width input columns with
column delimiters, and variable width input columns defined by binary prefix
bytes.
Using the ASCII conversion option
Use the
ASCII
conversion option to either:
• Convert ASCII input data to binary and specify the width of the input
column so data can be read in correctly for that column, or
• Insert ASCII data into an
ASCII
data type column when the width of the
input column is different from the width of the destination column. This
option lets you specify how much of the input data it should read for each
column.
You can use this option with any of the Adaptive Server IQ data types, with 1,
2, or 4 prefix bytes, and with a column delimiter.