Field declaration

You would declare a field in the declaration window (3), regardless of the buffer to which it belongs.

Note: The order in which you declare the fields determines the field order in the buffer.

The syntax for declaring a field is as follows:

AS/400 IBMi source

DCL I.field CHAR length[,decimals] [BASED field POS position] …
          O       ZONED
          B       PACKED
          S       BIN
          W       FLOAT
          V       NCHAR
          X       VARCHAR
                  SCRIPT
      
        …  [DESC ’description’] [NULL Yes/No/Rtrim/Same] [DATEFMT format] …
      
        …  [KEYORDER OrderNumber] [AGGREGATE Yes/No] [FIELDOPT Insert/Update/Never/ Insert/Update]

Field types

Type	Description
CHAR	Alphanumeric field that may be 1 to 32,765 characters in length (each buffer is limited to 32,765 characters).
ZONED	Numeric field stored in zoned format in the memory (1 digit per byte), the floating comma is not stored in the memory. The sign is combined with the last digit.
PACKED	Numeric field stored in a packed BCD in the memory (1 digit per half-byte), the floating comma is not stored in the memory. The sign is stored in the last right half byte.
BIN	Numeric field that accepts: , 4 or 8-byte integer values.
FLOAT	Numeric field that accepts: - or 8-byte floating comma.
NCHAR	Alphanumeric field in Unicode UCS2 format commonly known as ASCII.
VARCHAR	Alphanumeric type. In an AS/400 IBMi target, if the access mode is not SQL, the varchar is processed as a char. When a varchar field contains less than the maximum allowed, the value is delimited to a binary zero.
SCRIPT	Allows an instruction to be sent (sysdate, for ex.) or a SQL sub-query. For an AS/400 IBMi target it will only be taken into account if the access mode is a SQL type.

The storage length of NCHAR fields (Unicode UCS2) is doubled. A 5-character NCHAR field would occupy 10 bytes on the disk.

On an AS/400 IBMi target, the varchar and script fields are processed as a char field if the access mode is not SQL.

Floats can be used as is and output to an AS/400 IBMi or to an NT target.

In the transformation module, floats can be used as any numeric field and assigned to ZONED and PACKED fields. AS/400 IBMi makes the conversion and rounds the result to the precision defined for the output field.

Note: As a reminder, a ZONED or PACKED field can contain up to 31 significant digits that you can distribute between the integer part and the decimal part according to your requirements.

Note: Floats are numerics stored as mantissa and exponent values and are therefore approximations of the initial value (magnitude stored in powers of 2). Consequently, depending on how the output is expressed, the rounding would be more or less refined.

Examples:

If you send a float as to an NT float, you obtain:

Value entered: -987654321.123456789

Value stored on the AS/400 IBMi: -9,8765432112345684E+008

Value received by the NT target or by an AS/400 IBMi in SQL mode: 9,8765430E+08

Value entered: 32.22

Value stored on the AS/400 IBMi: 3,2219999999999999E+001

Value received by the NT target or by an AS/400 IBMi in SQL mode: 3,2219999E+01

If you convert the float to a ZONED (or PACKED) number of 31,15 (31 digits INCLUDING 15 decimals):

Value entered: -987654321.123456789

Value stored on the AS/400 IBMi: -9,8765432112345684E+008

Value received by the NT target or by an AS/400 IBMi in SQL mode: 0000000987654321.123456835746765

Non-AS/400 IBMi source

DCL I.field CHAR length[,decimals] [BASED field POS position] …
          O       NUMERIC
          B       IMAGE
          S       RAW
          W       SCRIPT
          V       TEXT
          X
      
        …  [DESC ’description’] [NULL Yes/No/Rtrim/Same] [DATEFMT format] …
      
        …  [KEYORDER OrderNumber] [AGGREGATE Yes/No] [AGREGATE Yes/No] [FIELDOPT Insert/Update/Never/ Insert/Update]

Field types

Type	Description
CHAR	Alphanumeric field that may be 1 to 32765 characters in length (each buffer is limited to 32,765 characters). A char field is processed as a varchar by default and complemented or not by blanks, depending on the value of NULL option. On an AS/400 IBMi target, a zone will only be processed as VARCHAR if the access mode is SQL.
ZONED	Numeric field stored in zoned format in the memory (1 digit per byte), the floating comma is not stored in the memory. The sign is combined with the last digit.
IMAGE	Define a varbinary field
RAW	Same than IMAGE
SCRIPT	Used to send a SQL statement (sysdate for example) or a sub-query. On an AS/400 IBMi target, the target file must be processed with SQL access mode.
TEXT	Define a longvarchar field

BASED

A BASED field is a field redefinition whose first byte is specified by the POS parameter for the field defined after the BASED parameter.

Example:

Representation of Buffer I in the memory, for example.

Suppose that you want to define:

A fieldA that covers fields 3 and 4, so that they can be read in one operation
A fieldB that is used to read an extract of field 5

For this, the following declarations are necessary:

DCL I.ZONEA ZONED 16,0 BASED ZONE3 POS 1

DCL I.ZONEB CHAR 4 BASED ZONE5 POS 3

Note that the field types A and B are not in any way connected with the fields 3, 4 and 5.

The BASED statement is used in certain cases, to avoid defining an intermediate field in a work buffer (V or W) and using assignment instructions to load it.

Note: The field name after the BASED statement must not be prefixed by the buffer name, as the "based" field and the base field are INVARIABLY in the same buffer.

DESC

This parameter is used for the same purpose as the Description field in the Fields tab in the table properties. It is used to specify a description associated with the field.

Example:

DCL O.CODDFV CHAR 1 DESC 'Quote/bill/credit note code'

NULL

This parameter is used for the same purpose as the Null field in the Fields tab in the table properties.

It specifies whether a null value must be sent for an empty field to an SQL database or an AS/400 IBMi in SQL mode, based on these rules:

Null	Type	Rule
Yes	Alpha	The blanks to the right are deleted, and if the field is empty, then the Null value is sent.
No	Alpha	The blanks to the right are not deleted.
Rtrim	Alpha	The blanks to the right are deleted, and if the field is empty, " " is sent.
Same	Alpha	No processing, empty are empty, null are null and varchar are varchar
Yes	Numeric	If the value contained in the field is 0, then Null is sent.
No	Numeric	If the value contained in the field is 0, then 0 is sent.
Same	Numeric	No processing, null are null, 0 are 0
Rtrim	Numeric	Same result as for No.
Yes	Date	If the value contained in the field is 0 or blank, then Null is sent.
No	Date	If the value contained in the field is 0 or blank, then Null is sent.
Same	Date	Sent data are exactly what was received or calculated. Null are Null, date are date
Rtrim	Date	Same result as for No.

Note: SQL databases usually do not accept that a field belonging to a primary key (PRIMARY KEY CONSTRAINT) contains a null value.

Example:

DCL O.CODDFV CHAR 1 DESC 'Quote/bill/credit note' NULL No

DATEFMT

This parameter is used for the same purpose as the Date field in the Fields tab in the table properties.

It specifies whether the field contains a date, whether it must be used for an SQL date-type field, and its storage format.

For example, if the source field contains a date stored in alpha or numeric format, with four digits for the year, two digits for the month and two digits for the date, you select the YYYYMMDD format.

In the formats, C stands for the century (0=19, 1=20), Y for the year, M for the month, D for the date, HHMM stands for the hour using two characters followed by the minutes using two characters, and HHMMSS stands for the hour using two characters followed by the minutes using two characters, and seconds using two characters.

In case of using two characters (YY format) to indicate the year, the resulting year for the 4-character format would be 20YY if YY<40, and 19YY if YY>40.

If you want to apply a rule other than the standard rules, you must use the transformation module.

Example:

 DCL O.DATPAI PACKED 8,0 DATEFMT YYYYMMDD NULL Yes

For an AS/400 IBMi source transmitting to an NT target, if the source date contains 0001-01-01, the date is considered as null and the NULL value is assigned to the date on the target.

For a non-AS/400 IBMi source (Oracle, SQL Server, ODBC) transmitting to an AS/400 IBMi target, if a date has the Null value, the value 0001-01-01 is assigned.

KEYORDER

This parameter is used for the same purpose as the Key order field in the Fields tab in the table properties.

It specifies whether the field is part of the key used to access the record on the target table and its order number in the key composition.

Example:

DCL O.CODDFV CHAR 1 DESC 'Quote/bill code' KEYORDER 1

FIELDOPTION

DCL O.DATADD PACKED 8,0 DATEFMT YYYYMMDD NULL Yes FIELDOPTION Insert

This shows when the field/column is displayed :

Insert/update

The field/column is used when insert and updates are performed.
Insert

The field/column is only used when inserts are done.
Update

The field/column is used only when updates are performed.
Never

The field/column is never used but it is sent to the target to be used in, for ex. the transformation.

AGGREGATE

This parameter specifies that the field/column will be managed as an aggregated column.

Example:

You want to use the FACENT table (billing header) to manage an aggregate for customers, with the number of bills and total amount billed, for each customer.

You must define the aggregate grouping criterion as the aggregate key (the customer code CUST_CODE in the above example).

The aggregated fields are CA and NUMBER, so you enter Yes for these AGGREGATE fields.

Note: For online aggregation to work, the access type must be SQL.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here

Field declaration

AS/400 IBMi source

Non-AS/400 IBMi source

BASED

DESC

NULL

DATEFMT

KEYORDER

FIELDOPTION

AGGREGATE

In this section

Did this page help you?