Equivalent SQL and REXX data types

All REXX data is string data. Therefore, when a REXX program assigns input data to a column, Db2 converts the data from a string type to the column type. When a REXX program assigns column data to an output variable, Db2 converts the data from the column type to a string type.

When you assign input data to a Db2 table column, you can either let Db2 determine the type that your input data represents, or you can use an SQLDA to tell Db2 the intended type of the input data.

When a REXX program assigns data to a column, it can either let Db2 determine the data type or use an SQLDA to specify the intended data type. If the program lets Db2 assign a data type for the input data, Db2 bases its choice on the input string format.

The following table shows the SQL data types that Db2 assigns to input data and the corresponding formats for that data. The two SQLTYPE values that are listed for each data type are the value for a column that does not accept null values and the value for a column that accepts null values.

Table 1. SQL input data types and REXX data formats
SQL data type assigned by Db2	SQLTYPE for data type	REXX input data format
INTEGER	496/497	A string of numerics that does not contain a decimal point or exponent identifier. The first character can be a plus (+) or minus (-) sign. The number that is represented must be between -2147483648 and 2147483647, inclusive.
BIGINT	492/493	A string of numbers that does not contain a decimal point or an exponent identifier. The first character can be a plus (+) or minus (-) sign. The number that is represented must be between -9223372036854775808 and -2147483648, inclusive, or between 2147483648 and 9223372036854775807.
DECIMAL(p,s)	484/485	One of the following formats: A string of numerics that contains a decimal point but no exponent identifier. p represents the precision and s represents the scale of the decimal number that the string represents. The first character can be a plus (+) or minus (-) sign. A string of numerics that does not contain a decimal point or an exponent identifier. The first character can be a plus (+) or minus (-) sign. The number that is represented is less than -9223372036854775808 or greater than 9223372036854775807.
FLOAT	480/481	A string that represents a number in scientific notation. The string consists of a series of numerics followed by an exponent identifier (an E or e followed by an optional plus (+) or minus (-) sign and a series of numerics). The string can begin with a plus (+) or minus (-) sign.
VARCHAR(n)	448/449	One of the following formats: A string of length n, enclosed in single or double quotation marks. The character X or x, followed by a string enclosed in single or double quotation marks. The string within the quotation marks has a length of 2*n bytes and is the hexadecimal representation of a string of n characters. A string of length n that does not have a numeric or graphic format, and does not satisfy either of the previous conditions.
VARGRAPHIC(n)	464/465	One of the following formats: The character G, g, N, or n, followed by a string enclosed in single or double quotation marks. The string within the quotation marks begins with a shift-out character (X'0E') and ends with a shift-in character (X'0F'). Between the shift-out character and shift-in character are n double-byte characters. The characters GX, Gx, gX, or gx, followed by a string enclosed in single or double quotation marks. The string within the quotation marks has a length of 4*n bytes and is the hexadecimal representation of a string of n double-byte characters.

For example, when Db2 executes the following statements to update the MIDINIT column of the EMP table, Db2 must determine a data type for HVMIDINIT:

SQLSTMT="UPDATE EMP" ,
  "SET MIDINIT = ?"  ,
  "WHERE EMPNO = '000200'"
"EXECSQL PREPARE S100 FROM :SQLSTMT"
HVMIDINIT='H'
"EXECSQL EXECUTE S100 USING" ,
   ":HVMIDINIT"

Because the data that is assigned to HVMIDINIT has a format that fits a character data type, Db2 REXX Language Support assigns a VARCHAR type to the input data.

If you do not assign a value to a host variable before you assign the host variable to a column, Db2 returns an error code.