CONVERSION_MODE option

Restricts implicit conversion between binary data types (BINARY, VARBINARY, and LONG BINARY) and other non-binary data types (BIT, TINYINT, SMALLINT, INT, UNSIGNED INT, BIGINT, UNSIGNED BIGINT, CHAR, VARCHAR, and LONG VARCHAR) on various operations.

0, 1

0

Can be set either publicly or temporarily. DBA permissions are not required to set this option.

The default value of 0 maintains implicit conversion behavior prior to version 12.7. Setting CONVERSION_MODE to 1 restricts implicit conversion of binary data types to any other non-binary data type on INSERT, UPDATE, and in queries. The restrict binary conversion mode also applies to LOAD TABLE default values and CHECK constraint. The use of this option prevents implicit data type conversions of encrypted data that would result in semantically meaningless operations.

The CONVERSION_MODE option restrict binary mode value of 1 restricts implicit conversion for the following operations.

For example:

CREATE TABLE t3 (c1 INT,
    csi SMALLINT,
    cvb VARBINARY(2),
    CHECK (csi<cvb));
SET TEMPORARY OPTION CONVERSION_MODE = 1;

The following request:

LOAD TABLE t3(c1 ',', csi ',', cvb ',')
    FROM '/s1/mydata/t3.inp'
    QUOTES OFF ESCAPES OFF
    ROW DELIMITED BY '\n'

fails with the message:

"Invalid data type comparison in predicate
(t3.csi < t3.cvb), [-1001013] ['QFA13']"

For example:

CREATE TABLE t1 (c1 INT PRIMARY KEY,
    cbt BIT NULL,
    cti TINYINT,
    csi SMALLINT,
    cin INTEGER,
    cui UNSIGNED INTEGER,
    cbi BIGINT,
    cub UNSIGNED BIGINT,
    cch CHAR(10),
    cvc VARCHAR(10),
    cbn BINARY(8),
    cvb VARBINARY(8),
    clb LONG BINARY,
    clc LONG VARCHAR);

CREATE TABLE t2 (c1 INT PRIMARY KEY,
    cbt BIT NULL,
    cti TINYINT,
    csi SMALLINT,
    cin INTEGER,
    cui UNSIGNED INTEGER,
    cbi BIGINT,
    cub UNSIGNED BIGINT,
    cch CHAR(10),
    cvc VARCHAR(10),
    cbn BINARY(8),
    cvb VARBINARY(8),
    clb LONG BINARY,
    clc LONG VARCHAR);

CREATE TABLE t4 (c1 INT, cin INT DEFAULT 0x31);

SET TEMPORARY OPTION CONVERSION_MODE = 1;

The following request:

INSERT INTO t1(c1, cvb) SELECT 99, cin FROM T2
WHERE c1=1

fails with the message:

"Unable to convert column 'cvb' to the requested
datatype (varbinary) from datatype (integer).
[-1013043] ['QCA43']"

• UPDATE SET VALUE FROM expression (including constant)

• UPDATE SET VALUE FROM other column

• UPDATE SET VALUE FROM host variable

• JOIN UPDATE SET VALUE FROM column of other table

For example, the following request:

UPDATE t1 SET cbi=cbn WHERE c1=1

fails with the message:

"Unable to implicitly convert column 'cbi' to datatype
(bigint) from datatype (binary). [-1000187] ['QCB87']"

• PUT cursor-name USING … host-variable

• Positioned UPDATE from another column

• Positioned UPDATE from a constant

• Positioned UPDATE from a host variable

1. Comparison Operators When CONVERSION_MODE = 1, the restriction applies to the following operators: =, !=, <, <=, >=, <>, !>, !< BETWEEN … AND IN used in a search condition for the following clauses: WHERE clause HAVING clause CHECK clause ON phrase in a join IF/CASE expression

   For example, the following query:

   SELECT COUNT(*) FROM T1
   WHERE cvb IN (SELECT csi FROM T2)
   

   fails with the message:

   "Invalid data type comparison in predicate 
   (t1.cvb IN (SELECT t1.csi ...)), [-1001013]
   ['QFA13']"
   

2. String Functions When CONVERSION_MODE = 1, the restriction applies to the following string functions: CHAR CHAR_LENGTH DIFFERENCE LCASE LEFT LOWER LTRIM PATINDEX RIGHT RTRIM SIMILAR SORTKEY SOUNDEX SPACE STR TRIM UCASE UPPER

   For example, the following query:

   SELECT ASCII(cvb) FROM t1 WHERE c1=1
   

   fails with the message:

   "Data exception - data type conversion is not
   possible. Argument to ASCII must be string,
   [-1009145] ['QFA2E']"
   

   The following functions allow either a string argument or a binary argument. When CONVERSION_MODE = 1, the restriction applies to mixed type arguments, that is, one argument is string and the other argument is binary.INSERTSTR LOCATE REPLACE STRING STUFF

   For example, the following query:

   SELECT STRING(cvb, cvc) FROM t1 WHERE c1=1
   

   where the column cvb is defined as VARBINARY and the column cvc is defined as VARCHAR, fails with the message:

   "Data exception - data type conversion is not
   possible. Arguments to STRING must be all binary 
   or all string, [-1009145] ['QFA2E']"
   

   The restriction does not apply to the following string functions: BIT_LENGTH BYTE_LENGTH CHARINDEX LENGTH OCTET_LENGTH REPEAT REPLICATE SUBSTRING

3. Arithmetic Operations and Functions When CONVERSION_MODE = 1, the restriction applies to the following operators used in arithmetic operations: +, -, *, /

   The restriction applies to the following bitwise operators used in bitwise expressions: & (AND), | (OR), ^ (XOR)

   The restriction also applies to integer arguments of the following functions: ROUND “TRUNCATE” TRUNCNUM

   For example, the following query:

   SELECT ROUND(4.4, cvb) FROM t1 WHERE C1=1
   

   fails with the message:

   "Data exception - data type conversion is not
   possible. Second Argument to ROUND cannot be
   converted into an integer, [-1009145] ['QFA2E']"
   

4. Integer Argument to Various Functions When CONVERSION_MODE = 1, the restriction applies to integer argument of the following functions: ARGN SUBSTRING DATEADD YMD

   For example, the following query:

   SELECT ARGN(cvb, csi, cti) FROM t1 WHERE c1=1
   

   fails with the message:

   "Data exception - data type conversion is not
   possible. First Argument to ARGN cannot be converted
   to an integer, [-1009145] ['QFA2E']"
   

5. Analytical Functions, Aggregate Functions, and Numeric Functions When CONVERSION_MODE = 1, no further restriction applies to analytical functions, aggregate functions, and numeric functions that require numeric expressions as arguments.