Native SQL for DB2 Common Server (SAP Library

Native SQL for DB2 Common Server

Below are the conversion tables for ABAP variables and data types in the DB2 Common Server database.

● You should, where possible, use the combinations of ABAP variable types and database data types in bold type.

● For illegal type combinations, the ABAP error procedure is given, along with the SQL error code of any resulting ABAP short dump.

● The behavior of any type combinations not listed here is undefined.

Note: The following documentation refers to the current database release DB2 UDB Version 5.0. SAP cannot accept any responsibility for consistency in other releases. Consistency will only be ensured if the database manufacturer (IBM) itself guarantees that the type conversions will be backwards-compatible. These tables are not to be regarded as a set of guaranteed conversion rules.

The following abbreviations are used:

● ok: The interface can perform the required conversion.

● [info] : The conversion is performed. There are database warnings, but no SQL error. However, the data is truncated at the right [rtrunc], left [trunc], rounded [round], or undefined [undef].

● <error code>: The failure of the conversion is marked by the corresponding SQL error code from the database. This is included in the ABAP short dump.

Inserts/Updates of a Variable Value with ABAP Type

· Character C(n) Numeric Text N(n)

· Packed number P(m,d)

Selects on a Column Value with Database Type

· char

· varchar

· longvarchar

· decimal

· numeric

· float

· double

· smallint

· integer

· date

· time

· timestamp

Insert/Update

ABAP Data Type: Character C

DB Data Type	*Test Case [Notes]*	Result
char	ABAP data value width > DB column width	22001
char	ABAP data value width <= DB column width	ok
varchar	ABAP data value width > DB column width	22001
varchar	ABAP data value width <= DB column width	ok
decimal	ABAP data value is non-numeric	22005
	Loss of significant places in conversion	22003
	Loss of non-significant places	22003
	No loss in conversion	ok
numeric	ABAP data value is non-numeric	22005
	Loss of significant places in conversion	22003
	Loss of non-significant places	22003
	No loss in conversion	ok
smallint	ABAP data value is non-numeric	22005
	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
integer	ABAP data value is non-numeric	22005
	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
float	ABAP data value is non-numeric	22005
	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
double	ABAP data value is non-numeric	22005
	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
date	ABAP data value does not have a valid DB date format	22007
date	ABAP data value has a valid DB date format	ok
time	ABAP data value does not have a valid DB time format	22007
time	ABAP data value has a valid DB time format	ok
timestamp	ABAP data value does not have a valid DB time stamp format	22007
timestamp	ABAP data value has a valid DB time stamp format	ok

ABAP Data Type: Numeric N

DB Data Type	*Test Case [Notes]*	Result
char	ABAP data value width > DB column width	22001
char	ABAP data value width <= DB column width	ok
varchar	ABAP data value width > DB column width	22001
varchar	ABAP data value width <= DB column width	ok
decimal	Loss of places in conversion	22003
decimal	No loss in conversion	ok
numeric	Loss of places in conversion	22003
numeric	No loss in conversion	ok
smallint	Loss of places in conversion	22003
smallint	No loss in conversion	ok
integer	Loss of places in conversion	22003
integer	No loss in conversion	ok
float	Loss of places in conversion	22003
float	No loss in conversion	ok
double	Loss of places in conversion	22003
double	No loss in conversion	ok
date		22007
time		22007
timestamp		22007

ABAP Data Type: Packed Number P

DB Data Type	*Test Case [Notes]*	Result
decimal	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
numeric	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
smallint	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
integer	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
float	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
double	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok

ABAP Data Type: Integer I

DB Data Type	*Test Case [Notes]*	Result
char	Loss of places in conversion	22003
char	No loss in conversion	ok
varchar	Loss of places in conversion	22003
varchar	No loss in conversion	ok
decimal	Loss of places in conversion	22003
decimal	No loss in conversion	ok
numeric	Loss of places in conversion	22003
numeric	No loss in conversion	ok
smallint	Loss of places in conversion	22003
smallint	No loss in conversion	ok
integer	Loss of places in conversion	22003
integer	No loss in conversion	ok
float	Loss of places in conversion	22003
float	No loss in conversion	ok
double	Loss of places in conversion	22003
double	No loss in conversion	ok
date		22007
time		22007
timestamp		22007

ABAP Data Type: Float F

DB Data Type	*Test Case [Notes]*	Result
char	Loss of significant places in conversion	22003
	Loss of non-significant places	22003
	No loss in conversion	ok
varchar	Loss of significant places in conversion	22003
	Loss of non-significant places	22003
	No loss in conversion	ok
decimal	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
numeric	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
smallint	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
integer	Loss of significant places in conversion	22003
	Loss of non-significant places	[trunc]
	No loss in conversion	ok
float	Loss of significant places in conversion	22003
float	No loss in conversion	ok
double	Loss of significant places in conversion	22003
double	No loss in conversion	ok
date		22007
time		22007
timestamp		22007

ABAP Data Type: Date D

DB Data Type	*Test Case [Notes]*	Result
char (8)	ABAP data value width (8) > DB column width	22001
char (8)	ABAP data value width (8) <= DB column width	ok
varchar	ABAP data value width (8) > DB column width	22001
varchar	ABAP data value width (8) <= DB column width	ok
decimal	Loss of places in conversion	22003
decimal	No loss in conversion	ok
numeric	Loss of places in conversion	22003
numeric	No loss in conversion	ok
smallint	[Data is always lost]	22003
integer		ok
float		ok
double		ok
date		22007
time		22007
timestamp		22007

ABAP Data Type: Time T

DB Data Type	*Test Case [Notes]*	Result
char(6)	ABAP data value width (6) > DB column width	22001
char(6)	ABAP data value width (6) <= DB column width	ok
varchar	ABAP data value width (6) > DB column width	22001
varchar	ABAP data value width (6) <= DB column width	ok
decimal	Loss of places in conversion	22003
decimal	No loss in conversion	ok
numeric	Loss of places in conversion	22003
numeric	No loss in conversion	ok
smallint	[Data is always lost]	22003
integer		ok
float		ok
double		ok
date		22007
time		22007
timestamp		22007

ABAP Data Type: Hexadecimal X

DB Data Type	*Test Case [Notes]*	Result
char	ABAP data value width > DB column width	22001
	ABAP data value width = DB column width	ok
	ABAP data value width < DB column width	[undef]
varchar	ABAP data value width > DB column width	22001
	ABAP data value width = DB column width	ok
	ABAP data value width < DB column width	[undef]

Select

Database Column Type: char, varchar, or longvarchar

ABAP Data Type	*Test Case [Notes]*	Result
Character C	DB data value width > ABAP field width	[rtrunc]
	DB data value width < ABAP field width [left-justified, padded with trailing blanks]	ok
	DB data value width = ABAP field width	ok
Numeric N	DB data value is non-numeric	[undef]
	DB data value width > ABAP field width [simulated SQL error]	22sim
	DB data value width < ABAP field width [right-justified, padded with leading zeros]	ok
	DB data value width = ABAP field width	ok
Packed P	[simulated SQL error]	22sim
Integer I	DB data value is non-numeric	22005
	Loss of significant places in conversion	22003
	No loss in conversion	ok
Float F	DB data value is non-numeric	22005
	Loss of significant places in conversion	22003
	Loss of non-significant places in conversion	[round]
	No loss in conversion	ok
Date D	Behaves like ABAP type C(8)	-
Time T	Behaves like ABAP type C(6)	-
Hexa X	DB data value width > ABAP field width	[rtrunc]
	DB data value width < ABAP field width [left-justified, remaining length undefined]	ok
	DB data value width = ABAP field width	ok

Database Column Type: decimal or numeric

ABAP Data Type	*Test Case [Notes]*	Result
Character C	[simulated SQL error]	22sim
Numeric N	[simulated SQL error]	22sim
Packed P	Loss of significant places in conversion	22003
	Loss of non-significant places in conversion	[round]
	No loss in conversion	ok
Integer I	Loss of significant places in conversion	22003
	Loss of non-significant places in conversion	[round]
	No loss in conversion	Ok
Float F	Loss of significant places in conversion	22003
	Loss of non-significant places in conversion	[round]
	No loss in conversion	ok
Date D	[simulated SQL error]	22sim
Time T	[simulated SQL error]	22sim
Hexa X		[undef]

Database Column Type: float or double

ABAP Data Type	*Test Case [Notes]*	Result
Character C	Loss of significant places in conversion	22003
	Loss of non-significant places in conversion	[round]
	No loss in conversion	ok
Numeric N	[simulated SQL error]	22sim
Packed P	[simulated SQL error]	22sim
Integer I	Loss of significant places in conversion	22003
	Loss of non-significant places in conversion	[round]
	No loss in conversion	ok
Float F	Loss of significant places in conversion	22003
	Loss of non-significant places in conversion	[round]
	No loss in conversion	ok
Date D	[simulated SQL error]	22sim
Time T	[simulated SQL error]	22sim
Hexa X		[undef]

Database Column Type: integer or smallint

ABAP Data Type	*Test Case [Notes]*	Result
Character C	Loss of significant places in conversion	22003
Character C	No loss in conversion	ok
Numeric N	[simulated SQL error]	22sim
Packed P	[simulated SQL error]	22sim
Integer I	[No loss in conversion]	ok
Float F	[No loss in conversion]	ok
Date D	[simulated SQL error]	22sim
Time T	[simulated SQL error]	22sim
Hexa X		[undef]

Database Column Type: date, time or timestamp

ABAP Data Type	*Test Case [Notes]*	Result
Character C	DB data value width > ABAP field width	22003
	DB data value width < ABAP field width [left-justified, padded with trailing blanks]	ok
	DB data value width = ABAP field width	ok
Numeric N	[simulated SQL error]	22sim
Packed P	[simulated SQL error]	22sim
Integer I	[restricted data type attribute violation]	07006
Float F	[restricted data type attribute violation]	07006
Date D	[simulated SQL error]	22sim
Time T	[simulated SQL error]	22sim
Hexa X	[restricted data type attribute violation]	07006