DB2 data types

Gallium Data supports all the data types supported by DB2/LUW.

However, Gallium Data has not yet been tested with DB2/z and DB2/i, therefore the types that are specific to these systems, such as timestamp with time zone, timestamp without time zone, and rowid, are not yet supported.

All numbers

All number values (smallint, integer, bigint, decimal, numeric, decfloat, real, double, float) are exposed as JavaScript numbers.

if (context.row.colDecFloat == 42.5) {

    context.row.colDecFloat = 44.001;

}

JavaScript numbers have some limitations, so when dealing with unusually large or precise numbers, you have the option to retrieve these numbers as Java objects, thereby preserving the exact value.

You can retrieve a value as a Java BigDecimal object for values of type decimal, numeric, decfloat, real, double, float.

You can retrieve a value as a Java BigInteger object for values of type smallint, integer, bigint.

For instance, if your DB2 database has a column of type BIGINT, which is a 63-bit signed integer, JavaScript will be unable to properly handle values greater than 2^52: values greater than that will be rounded, thus leading to loss of precision.

In this example, if the value is 987,654,321,987,654,321, the following code will cause some loss of precision:

context.row.numberofmolecules += 3;  // Result: 987,654,321,987,654,300

Similar loss of precision will happen if the number is a decimal number, and the precision exceeds what JavaScript is capable of.

When dealing with such numbers, you can avoid any loss of precision by retrieving the value as an object rather than a number:

let nbrMolecules = context.row.getValue("numberofmolecules");

Assuming the column is of type BIGINT in DB2, this would return a Java object of type BigInteger, which can then be used without any loss of precision:

let BigInteger = Java.type("java.math.BigInteger");

let nbrMolecules = context.row.getJavaValue("numberofmolecules");

let newMols = noMolecules.add(new BigInteger("3"));

context.row.numberofmolecules = newMols;  // Correct result, no precision loss

All strings

All string values (char, varchar, clob, graphic, vargraphic, dbclob, nchar, nvarchar, nclob) are exposed as JavaScript strings.

let value = context.row.colVarchar;

value += ' as of ' + new Date();

context.row.colVarchar = value;

In some cases, you may need to specify the encoding of the text, in which case you can use:

let varcharValue = context.row.getString("colVarchar", "ISO-8859-9");

context.row.setString("colVarchar", "MyGelecek sefer görüşürüz", "ISO-8859-9");

See the character encodings page for a list of all the encodings supported.

array

Arrays are fully supported, and are exposed as JavaScript arrays. In DB2, arrays are only supported in stored procedures and triggers, but not as column types.

If we assume that we have an invocation of a stored procedure with the second parameter being an array of strings, then we could have the following code in a prepared statement filter:

let secondParam = context.packet.values[1]; // JavaScript arrays are zero-based

if (secondParam[3] === 'Jones') {

    secondParam[3] = 'Smith';

}

binary and varbinary

Values of the type binary and varbinary are exposed as JavaScript arrays of bytes.

if (context.row.colBinary[2] === 12) {

    context.row.colBinary[2] = 99;

}

blob

DB2 blobs are exposed as byte arrays.

context.row.colBlob = [1,2,3,4,5];

boolean

Booleans are exposed as JavaScript booleans.

if (context.row.colBool1) {

    context.row.colBool2 = true;

}

date

Values of the type date are exposed to JavaScript as strings in the format  YYYY-MM-DD. 

Valid values go from 0001-01-01 to 9999-12-31. 

let d = context.row.colDate; // Value: "2023-06-25"

context.row.colDate = '2023-12-31';

You may find it useful to retrieve the value as a Java LocalDate object if you need to manipulate it:

let d = context.row.getJavaValue("colDate");

context.row.colDate = d.plusDays(50);

It is possible to set the value of a date using a JavaScript Date object:

context.row.colDate = new Date("1999-12-31");

time

Values of type time are exposed as strings with format "HH:MM:SS" (24-hour based).

log.debug("Value of colTime is: " + context.row.colTime); // "23:59:59"

context.row.colTime = "12:34:56";

You may find it useful to retrieve the value as a Java LocalTime object if you need to manipulate it:

let t = context.row.getJavaValue("colTime");

context.row.colTime = t.minusMinutes(75);

timestamp

Values of the type timestamp are exposed as strings in the format YYYY-MM-DDThh:mm:ss.SSSSSS

The number of decimals for fractional seconds depends on the precision in the database, and can be anywhere from 0 to 9. If more decimals are provided than fits in the data type, the extra decimals are silently truncated.

let d = context.row.colTimestamp; // Value: "2023-06-25T12:34:56.789012"

context.row.colDate = '1999-12-31T23:59:59.999';

If you need to manipulate a timestamp in non-trivial ways, you may find it convenient to retrieve the value as a Java LocalDateTime object:

let dt = context.row.getJavaValue("colTimestamp");

context.row.colTimestamp = dt.plusDays(30);

It is also possible to set the value of a timestamp using a JavaScript Date object:

context.row.colDateTime = new Date("1999-12-31T12:34:56.789");

However, JavaScript Date objects cannot handle precision smaller than a millisecond.

Note: because Gallium Data has not yet been tested with DB2/z and DB2/i, the behavior of the types timestamp with time zone and timestamp without time zone is undefined.

User-defined types (UDTs)

User-defined types are mapped to standard types either by the query, or by transform functions defined for the types.

xml

Values of type xml are exposed as strings.

log.debug("Value of colXml is: " + context.row.colXml);  // Logs the XML

context.row.colXml = '<test attrib="foo">value</test>';