5.5.3 Fixed-size data format

For an input data file in fixed-size data format, specify the input data in characters or in the format stored in the database. When using a constructor function to generate the data to be stored in an abstract data type, code the input parameter either in character string data or in the data type of the function argument.

Numeric data in character string format and character string data in character string format are stored in columns of the data types listed in the table below.

Table 5-30 Numeric data and character string data in character string format

Numeric data in character string formatCharacter string data in character string format
  • INTEGER
  • SMALLINT
  • DECIMAL
  • FLOAT
  • SMALLFLT
  • DATE
  • INTERVAL YEAR TO DAY
  • TIME
  • INTERVAL HOUR TO SECOND
  • TIMESTAMP
  • CHAR
  • VARCHAR
  • MCHAR
  • MVARCHAR
  • NCHAR
  • NVARCHAR
Organization of this subsection
(1) Specification format
(2) Rules
(3) Input data types unique to the fixed-size data format
(4) Example

(1) Specification format

For details about how to specify input data in characters, see 5.5.1 DAT format. For details about how to specify input data in the format stored in the database, see 5.5.2 Binary format.

(2) Rules

Table 5-32 Data storage when the input data is specified in character string data format (fixed-size data format)

Data type of table column or parameter data type of constructor functionSpecification of column-name statement and option statementData specified in the input data fileStorage data
INTEGER, SMALLINT, DECIMAL, FLOAT, and SMALLFLTtype=char(3)1[Figure]1 for each data type
[Figure]1
[Figure]1[Figure]
+[Figure]1Input data error
1[Figure]1
type=char(3)allspace operand specified in the option statement[Figure]0 for each data type
allspace operand omitted from the option statement[Figure]Input data error
CHAR(7) and MCHAR(7)type=char(7) or type=char(7),mode=textABC[Figure]ABC[Figure]
"ABC"[Figure]
"ABC[Figure]"
""ABC"""ABC"[Figure]
[Figure][Figure]
""[Figure]
[Figure]"[Figure]A[Figure]"[Figure][Figure]"[Figure]A[Figure]"[Figure]
type=char(7),mode=binABC[Figure]ABC[Figure]
"ABC"[Figure]"ABC"[Figure]
"ABC[Figure]""ABC[Figure]"
""ABC""""ABC""
[Figure][Figure]
""[Figure]""[Figure]
[Figure]"[Figure]A[Figure]"[Figure][Figure]"[Figure]A[Figure]"[Figure]
NCHAR(3)type=char(6)[Figure][Figure][Figure][Figure][Figure][Figure]
"[Figure]"[Figure]"[Figure]"[Figure]
"[Figure][Figure]"[Figure][Figure][Figure]
""[Figure]"""[Figure]"[Figure]
[Figure][Figure][Figure][Figure][Figure][Figure]
""[Figure][Figure]""[Figure][Figure]
VARCHAR(7) and MVARCHAR(7)type=char(7) or type=char(7),enclose_del=yesABC[Figure]ABC (3 bytes)
"ABC"[Figure]
"ABC[Figure]"ABC[Figure] (5 bytes)
""ABC"""ABC" (5 bytes)
[Figure][Figure](1 byte)
""[Figure](0 byte)
[Figure]"[Figure]A[Figure]"[Figure][Figure]"[Figure]A[Figure]"
type=char(7),enclose_del=noABC[Figure]ABC (3 bytes)
"ABC"[Figure]"ABC" (5 bytes)
"ABC[Figure]""ABC[Figure]" (7 bytes)
""ABC""""ABC"" (7 bytes)
[Figure][Figure](1 byte)
""[Figure]"" (2 bytes)
[Figure]"[Figure]A[Figure]"[Figure][Figure]"[Figure]A[Figure]"[Figure]
NVARCHAR(3)type=char(6)[Figure][Figure][Figure][Figure][Figure][Figure](6 bytes)
"[Figure]"[Figure]"[Figure]"[Figure] (6 bytes)
"[Figure][Figure]"[Figure][Figure](4 bytes)
""[Figure]"""[Figure]" (4 bytes)
[Figure][Figure][Figure][Figure][Figure][Figure](6 bytes)
""[Figure][Figure]""[Figure][Figure] (6 bytes)
DATEtype=char(12)2003-12-31[Figure]2003-12-31
[Figure]2003-12-31
[Figure]2003-12-31[Figure]
INTERVAL YEAR TO DAYtype=char(11)00010101​.[Figure]+00010101​.
[Figure]00010101​.
[Figure]00010101​.[Figure]
TIMEtype=char(10)08:45:00[Figure]08:45:00
[Figure]08:45:00
[Figure]08:45:00[Figure]
INTERVAL HOUR TO SECONDtype=char(9)010101.[Figure]+010101.
[Figure]010101.
[Figure]010101.[Figure]
TIMESTAMPtype=char(21)1991-01-01_11:22:33[Figure]1991-01-01_11:22:33
[Figure]1991-01-01_11:22:33
[Figure]1991-01-01_11:22:33[Figure]
BLOBtype=char(13)/tmp/file01[Figure]Data in /tmp/file01
[Figure]/tmp/file01Error
[Figure]//tmp/file01[Figure]
BINARY(7)type=char(7)[Figure][Figure]
Legend:
[Figure]: Single-byte space (0x20)
[Figure]: Double-byte space

(3) Input data types unique to the fixed-size data format

An input data file in the fixed-size data format is created using the same data format as for the table's column data type or using character strings that can be converted to the column data type. For the input data that is stored in columns of DECIMAL type, you can use the ADEC (ASCII CODE DECIMAL) and BDEC (BINARY DECIMAL) types.

(a) ADEC type (ASCII CODE DECIMAL)

The ADEC type is used to specify numeric data in character string format. The data does not contain decimal points. Instead, the location of the decimal point is specified in the column structure information file. The following shows the format of the ADEC type:

[Figure]

Rules
  • The following characters are permitted:
    +, -, space (0x20), tab (0x09), null character (0x00), 0 to 9
    Note that if the data contains at least 1 byte of space, tab, or null character, it is treated as an integer without the decimal point. Therefore, if you create data whose decimal point is to be determined based on the precision and the decimal places specified in the type operand, you must pad the data as appropriate with 0s.
  • Input data consisting of only a sign (+ or -) results in an input data error.
  • Input data consisting of only spaces is permitted only when the allspace operand is specified in the option statement. In such a case, 0 is stored. If the allspace operand is not specified, an input data error results.
  • The input data cannot contain decimal points. You use the type operand in the column structure information file to specify the precision and decimal places. The relationship between precision (m) and decimal places (n) is as follows:
    1 [Figure] m[Figure] 38; 0 [Figure] n[Figure] 38; m[Figure] n
  • If the input data contains a sign (+ or -), you must specify for the precision (m) the number of digits including the 1-byte sign.
  • The precision and decimal places specified in the type operand must be appropriate for converting data to the DECIMAL type for the storage column (no digit cancellation occurs). If the data cannot be converted, a control statement error results.
Example of data storage
The table below shows an example of storing the following data:
  • Column definition for the storage table: DECIMAL(10,4)
  • Input data: ADEC type
  • Specification in the column structure information file: type=adec(10,4)

    Table 5-33 Example of storing data specified as the ADEC type in DECIMAL(10,4)

    Data specified in the input data fileData stored in DECIMAL(10,4)Description
    1[Figure]+000001.0000If the data contains even 1 byte of space, the precision and decimal places specified in the type operand are ignored and the data is treated as a value with no decimal point.
    [Figure]1+000001.0000
    [Figure]1[Figure]+000001.0000
    [Figure]10000+010000.0000
    000001[Figure]+000001.0000
    1000000000​+100000.0000If the data consists of only 0 to 9, the location of decimal point is determined by the precision and decimal places specified in the type operand.
    0000000001​+000000.0001
    0000010000​+000001.0000
    +000010000​+000001.0000If the data consists of 0 to 9 and the sign part, the location of decimal point is determined from the end of the data.
    -000010000​-000001.0000
Legend:
[Figure]: Space (0x20)
(b) BDEC type (BINARY DECIMAL)

The BDEC type is used to specify binary data represented in a binary number as a decimal number. The location of the decimal point is determined based on the table's column data type and the number of digits in the decimal number. The first bit of data of the BDEC type is treated as the sign. The following shows the format of the BDEC type:

[Figure]

Rules
  • You must specify the length of the binary data in the type operand in the column structure information file.
  • The number of digits (m) for the BDEC type is as follows:
    In 32-bit mode: 1 to 4 bytes
    In 64-bit mode: 1 to 8 bytes
  • The actual values that can be specified for the BDEC type are as follows:
    In 32-bit mode: -2147483648 to 2147483647
    In 64-bit mode: -9223372036854775808 to 9223372036854775807
  • The actual values that can be specified in a DECIMAL column are as follows:
    In 32-bit mode: Up to 10 digits for precision
    In 64-bit mode: Up to 19 digits for precision
Example of data storage
The table below shows an example of storing the following data:
  • Input data: 4-byte binary number
  • Specification in the column structure information file: type=bdec(4)

    Table 5-34 Example of storing data specified as the BDEC type in a DECIMAL column

    Data specified in the input data fileColumn definition of the storageData to be stored
    0xff 0xff 0xfc 0xf7DECIMAL(10,0)-0000000777​
    DECIMAL(10,2)-00000007​.77
    DECIMAL(10,10)-0.0000000777​
As shown in the example, the same value can be stored as different values depending on the DECIMAL column's precision and decimal places.

(4) Example

The figure below shows an example of coding data in the fixed-size data format.

Figure 5-17 Example of data in fixed-size data format

[Figure]