×
 
 
  •  
$helper.renderConfluenceMacro('{bmc-global-announcement:$space.key}')
LOADPLUS for DB2 11.2 ... Command and syntax reference LOADPLUS command options

Supported data type conversions in LOADPLUS

This topic describes the allowable data conversions and default output lengths.

A blank cell in a table indicates that the data type conversion is not allowed.

Numeric output columns

The following table lists the conversions to numeric output that LOADPLUS supports. This table indicates the supported conversions with the following identifiers:

  • X = allowable conversion

  • D = allowable conversion and the default input data type

Important

When you specify FORMAT CSV, LOADPLUS supports only conversions to the following data types:

  • DATE EXTERNAL

  • TIME EXTERNAL

  • TIMESTAMP EXTERNAL


Allowable data type conversions for numeric output columns

Output column

SMALLINT

INTEGER

BIGINT

DECIMAL

FLOAT

DECFLOAT

Input field

SMALLINT

D

X

X

X

X 1

X

INTEGER

X

D

X

X

X 1  

X

INTEGER EXTERNAL

X

X

X

X

X 1  

X

BIGINT

X

X

D

X

X

X

DECIMAL

X

X

X

D

X 1  

X

DECIMAL EXTERNAL

X

X

X

X

X 1  

X

DECIMAL ZONED

X

X

X

X

X 1   

FLOAT

X

X

X

X

D

X

FLOAT EXTERNAL

X

X

X

X

X

X

BINARY

      

VARBINARY

      

CHAR

X

X

X

X

X

X

CHAR BLOBF

      

CHAR CLOBF

      

CHAR DBCLOBF

      

VARCHAR

X

X

X

X

X

 

VARCHAR BLOBF

      

VARCHAR CLOBF

      

VARCHAR DBCLOBF

      

GRAPHIC

      

GRAPHIC EXTERNAL

      

VARGRAPHIC

      

DATE

      

DATE EXTERNAL

      

DATE-format EXTERNAL 2

      

TIME

      

TIME EXTERNAL

      

TIME-format EXTERNAL 2  

      

TIMESTAMP

      

TIMESTAMP EXTERNAL

      

TIMESTAMP-format EXTERNAL 2  

      

TIMESTAMP WITH TIME ZONE

      

TIMESTAMP WITH TIME ZONE EXTERNAL

      

BLOB

      

CLOB

      

DBCLOB

      

XML

      

EXIT

X

X

X

X

X

 

1 This conversion is inexact. The value in the floating-point column might not be identical to the value in the input field.

2 The format number describes the representation of the input date or time value. If you do not specify the format number, LOADPLUS defaults to the format specified in your DSNHDECP module in most cases (ISO, USA, EUR, JIS, or LOCAL). However, for CSV input data, LOADPLUS defaults to ISO format.

The following table describes the default length of values in your input file based on data type. This table uses the following identifiers:

  • Numeric values = required input length

  • X = requires explicit length

  • C = column defines default length

    For DECIMAL, the column also defines precision and scale.

  • V = input value defines the length; two-byte or four-byte length precedes the field value


Default lengths of input values

Output column

SMALLINT

INTEGER

BIGINT

DECIMAL

FLOAT

DECFLOAT

Input field

SMALLINT

2

2

2

2

2

2

INTEGER

4

4

4

4

4

4

INTEGER EXTERNAL

X

X

X

X

X

X

BIGINT

8

8

8

8

8

8

DECIMAL

X

X

X

C

X

X

DECIMAL EXTERNAL

X

X

X

X

X

X

DECIMAL ZONED

X

X

X

C

X

 

FLOAT

X

X

X

X

C

X

FLOAT EXTERNAL

X

X

X

X

X

X

BINARY

      

VARBINARY

      

CHAR

X

X

X

X

X

X

VARCHAR

V

V

V

V

V

 

GRAPHIC

      

GRAPHIC EXTERNAL

      

VARGRAPHIC

      

DATE

      

DATE EXTERNAL

      

DATE-format EXTERNAL

      

TIME

      

TIME EXTERNAL

      

TIME-format EXTERNAL

      

TIMESTAMP

      

TIMESTAMP EXTERNAL

      

TIMESTAMP-format EXTERNAL

      

TIMESTAMP WITH TIME ZONE

      

TIMESTAMP WITH TIME ZONE EXTERNAL

      

BLOB

      

CLOB

      

DBCLOB

      

XML

      

EXIT

X

X

X

X

X

 

Binary, character, and graphic output columns

The following table lists the conversions that LOADPLUS supports to binary, character, and graphic output columns. This table indicates the supported conversions with the following identifiers:

  • X = allowable conversion

  • D = allowable conversion and the default input data type

Important

When you specify FORMAT CSV, LOADPLUS supports only conversions to the following data types:

  • DATE EXTERNAL

  • TIME EXTERNAL

  • TIMESTAMP EXTERNAL


Allowable data type conversions for binary, character, and graphic output columns

Output column

BINARY

VARBINARY

CHARACTER

VARCHAR

GRAPHIC

VARGRAPHIC

Input field

SMALLINT

  

X

X

  

INTEGER

  

X

X

  

INTEGER EXTERNAL

  

X

X

  

BIGINT

  

X

X

  

DECIMAL

  

X

X

  

DECIMAL EXTERNAL

  

X

X

  

DECIMAL ZONED

  

X

X

  

FLOAT

  

X

X

  

FLOAT EXTERNAL

  

X

X

  

BINARY

D

X

    

VARBINARY

X

D

    

CHAR

  

D

X

  

CHAR BLOBF

      

CHAR CLOBF

      

CHAR DBCLOBF

      

VARCHAR

  

X

D

  

VARCHAR BLOBF

      

VARCHAR CLOBF

      

VARCHAR DBCLOBF

      

GRAPHIC

    

D

X

GRAPHIC EXTERNAL

    

X

X

VARGRAPHIC

    

X

D

DATE

  

X

X

  

DATE EXTERNAL

      

DATE-format EXTERNAL 1

  

X

X

  

TIME

  

X

X

  

TIME EXTERNAL

      

TIME-format EXTERNAL 1  

  

X

X

  

TIMESTAMP

  

X

X

  

TIMESTAMP EXTERNAL

      

TIMESTAMP-format EXTERNAL 1  

  

X

X

  

TIMESTAMP WITH TIME ZONE

  

X

X

  

TIMESTAMP WITH TIME ZONE EXTERNAL

      

BLOB

      

CLOB

      

DBCLOB

      

XML

      

EXIT

X

X

X

X

X

X

1 The format number describes the representation of the input date or time value. If you do not specify the format number, LOADPLUS defaults to the format specified in your DSNHDECP module in most cases (ISO, USA, EUR, JIS, or LOCAL). However, for CSV input data, LOADPLUS defaults to ISO format.

The following table describes the default length of values in your input file based on data type. This table uses the following identifiers:

  • Numeric values = required input length

  • X = requires explicit length

  • C = column defines default length

    For DECIMAL, the column also defines precision and scale.

    For GRAPHIC EXTERNAL, this value includes the shift-in and shift-out characters.

  • V = input value defines the length; two-byte or four-byte length precedes the field value

  • T = tables in Supported external DATE TIME and TIMESTAMP formats provide length information.


Default lengths of input values

Output column

BINARY

VARBINARY

CHARACTER

VARCHAR

GRAPHIC

VARGRAPHIC

Input field

SMALLINT

  

2

2

  

INTEGER

  

4

4

  

INTEGER EXTERNAL

  

X

X

  

BIGINT

  

8

8

  

DECIMAL

  

X

X

  

DECIMAL EXTERNAL

  

X

X

  

DECIMAL ZONED

  

X

X

  

FLOAT

  

X

X

  

FLOAT EXTERNAL

  

X

X

  

BINARY

C

C

    

VARBINARY

V

V

    

CHAR

  

C

C

  

VARCHAR

  

V

V

  

GRAPHIC

    

C

C

GRAPHIC EXTERNAL

    

C

C

VARGRAPHIC

    

V

V

DATE

  

4

4

  

DATE EXTERNAL

      

DATE-format EXTERNAL

  

T

T

  

TIME

  

3

3

  

TIME EXTERNAL

      

TIME-format EXTERNAL

  

T

T

  

TIMESTAMP

  

10

10

  

TIMESTAMP EXTERNAL

      

TIMESTAMP-format EXTERNAL

  

T

T

  

TIMESTAMP WITH TIME ZONE

  

33

33

  

TIMESTAMP WITH TIME ZONE EXTERNAL

      

BLOB

      

CLOB

      

DBCLOB

      

XML

      

EXIT

X

X

X

X

X

X

Date, time, and timestamp output columns

The following table lists the conversions that LOADPLUS supports to date, time, and timestamp output columns. This table indicates the supported conversions with the following identifiers:

  • X = allowable conversion

  • D = allowable conversion and the default input data type

Important

When you specify FORMAT CSV, LOADPLUS supports only conversions to the following data types:

  • DATE EXTERNAL

  • TIME EXTERNAL

  • TIMESTAMP EXTERNAL


Allowable data type conversions for date, time, and timestamp output columns

Output column

DATE

TIME

TIMESTAMP

TIMESTAMP WITH TIME ZONE

Input field

SMALLINT

    

INTEGER

X

X

  

INTEGER EXTERNAL

X

X

  

BIGINT

    

DECIMAL

X

X

  

DECIMAL EXTERNAL

X

X

  

DECIMAL ZONED

    

FLOAT

    

FLOAT EXTERNAL

    

BINARY

    

VARBINARY

    

CHAR

X

X

X

X

CHAR BLOBF

    

CHAR CLOBF

    

CHAR DBCLOBF

    

VARCHAR

X

X

X

X

VARCHAR BLOBF

    

VARCHAR CLOBF

    

VARCHAR DBCLOBF

    

GRAPHIC

    

GRAPHIC EXTERNAL

    

VARGRAPHIC

    

DATE

X

   

DATE EXTERNAL

X

   

DATE-format EXTERNAL 1

X

   

TIME

 

X

  

TIME EXTERNAL

 

X

  

TIME-format EXTERNAL 1  

 

X

  

TIMESTAMP

X

X

X

X

TIMESTAMP EXTERNAL

X

X

X

X

TIMESTAMP-format EXTERNAL 1  

X

X

X

 

TIMESTAMP WITH TIME ZONE

X

X

X

X

TIMESTAMP WITH TIME ZONE EXTERNAL

X

X

X

X

BLOB

    

CLOB

    

DBCLOB

    

XML

    

EXIT

X

X

X

 
1 The format number describes the representation of the input date or time value. If you do not specify the format number, LOADPLUS defaults to the format specified in your DSNHDECP module in most cases (ISO, USA, EUR, JIS, or LOCAL). However, for CSV input data, LOADPLUS defaults to ISO format.

The following table describes the default length of values in your input file based on data type. This table uses the following identifiers:

  • Numeric values = required input length

  • X = requires explicit length

  • V = input value defines the length; two-byte or four-byte length precedes the field value

  • L = local DATE and TIME length if your DSNHDECP format is LOCAL. If your DSNHDECP format is ISO, USA, EUR, or JIS, the default length for DATE is 10 and the default length for TIME is 8.

  • T = tables in Supported external DATE TIME and TIMESTAMP formats provide length information.


Default lengths of input values

Output column

DATE

TIME

TIMESTAMP

TIMESTAMP WITH TIME ZONE

Input field

SMALLINT

    

INTEGER

4

4

  

INTEGER EXTERNAL

8

6

  

BIGINT

    

DECIMAL

8

6

  

DECIMAL EXTERNAL

8

6

  

DECIMAL ZONED

8

6

  

FLOAT

    

FLOAT EXTERNAL

    

BINARY

    

VARBINARY

    

CHAR

10

8

26

33

VARCHAR

V

V

V

V

GRAPHIC

    

GRAPHIC EXTERNAL

    

VARGRAPHIC

    

DATE

4

   

DATE EXTERNAL

L

   

DATE-format EXTERNAL

T

   

TIME

 

3

  

TIME EXTERNAL

 

L

  

TIME-format EXTERNAL

 

T

  

TIMESTAMP

10

10

10

10

TIMESTAMP EXTERNAL

26

26

26

26

TIMESTAMP-format EXTERNAL

T

T

T

 

TIMESTAMP WITH TIME ZONE

33

33

33

33

TIMESTAMP WITH TIME ZONE EXTERNAL

33

33

33

33

BLOB

    

CLOB

    

DBCLOB

    

XML

    

EXIT

X

X

X

 

LOB and XML output columns

The following table lists the conversions that LOADPLUS supports to LOB and XML output columns. This table indicates the supported conversions with the following identifiers:

  • X = allowable conversion

  • D = allowable conversion and the default input data type

Important

When you specify FORMAT CSV, LOADPLUS supports only conversions to the following data types:

  • DATE EXTERNAL

  • TIME EXTERNAL

  • TIMESTAMP EXTERNAL


Allowable data type conversions for LOB and XML output columns

Output column

BLOB

CLOB

DBCLOB

XML

Input field

SMALLINT

    

INTEGER

    

INTEGER EXTERNAL

    

BIGINT

    

DECIMAL

    

DECIMAL EXTERNAL

    

DECIMAL ZONED

    

FLOAT

    

FLOAT EXTERNAL

    

BINARY

    

VARBINARY

    

CHAR

    

CHAR BLOBF

X

X 1  X 1  

X

CHAR CLOBF

X 1

X

X 1  

X

CHAR DBCLOBF

X 1  X 1  

X

X 1  

VARCHAR

    

VARCHAR BLOBF

X

X 1  X 1  

X

VARCHAR CLOBF

X 1  

X

X 1  

X

VARCHAR DBCLOBF

X 1  X 1  

X

X 1  

GRAPHIC

    

GRAPHIC EXTERNAL

    

VARGRAPHIC

    

DATE

    

DATE EXTERNAL

    

DATE-format EXTERNAL 2  

    

TIME

    

TIME EXTERNAL

    

TIME-format EXTERNAL 2  

    

TIMESTAMP

    

TIMESTAMP EXTERNAL

    

TIMESTAMP-format EXTERNAL 2

    

TIMESTAMP WITH TIME ZONE

    

TIMESTAMP WITH TIME ZONE EXTERNAL

    

BLOB

X

   

CLOB

 

X

  

DBCLOB

  

X

 

XML

   

X

EXIT

    

1 LOADPLUS invokes DSNUTILB in this case.

2 The format number describes the representation of the input date or time value. If you do not specify the format number, LOADPLUS defaults to the format specified in your DSNHDECP module in most cases (ISO, USA, EUR, JIS, or LOCAL). However, for CSV input data, LOADPLUS defaults to ISO format.

The following table describes the default length of values in your input file based on data type. The V in this table indicates that the input value defines the length and a two-byte or four-byte length precedes the field value.


Default lengths of input values

Output column

BLOB

CLOB

DBCLOB

XML

Input field

SMALLINT

    

INTEGER

    

INTEGER EXTERNAL

    

BIGINT

    

DECIMAL

    

DECIMAL EXTERNAL

    

DECIMAL ZONED

    

FLOAT

    

FLOAT EXTERNAL

    

BINARY

    

VARBINARY

    

CHAR

    

VARCHAR

    

GRAPHIC

    

GRAPHIC EXTERNAL

    

VARGRAPHIC

    

DATE

    

DATE EXTERNAL

    

DATE-format EXTERNAL

    

TIME

    

TIME EXTERNAL

    

TIME-format EXTERNAL

    

TIMESTAMP

    

TIMESTAMP EXTERNAL

    

TIMESTAMP-format EXTERNAL

    

TIMESTAMP WITH TIME ZONE

    

TIMESTAMP WITH TIME ZONE EXTERNAL

    

BLOB

V

   

CLOB

 

V

  

DBCLOB

  

V

 

XML

   

V

EXIT

    
Related topic

LOADPLUS command options

Was this page helpful? Yes No Submitting... Thank you
Last modified by Anagha Evans on Sep 30, 2021
command_reference syntax

Comments

Log in or register to comment.

Supported external DATE TIME and TIMESTAMP formats
Additional input field length information
© Copyright 2013-2023 BMC Software, Inc.
Legal notices

Powered by Atlassian Confluence and Scroll Viewport

© Copyright 2005-2024 BMC Software, Inc. Use of this site signifies your acceptance of BMC’s Terms of Use . BMC, the BMC logo, and other BMC marks are assets of BMC Software, Inc. These trademarks are registered and may be registered in the U.S. and in other countries.