Give DB2 new functionality with the ability to CAST from binary data.

Whether it be a hash value, raw data from another system, or something else, every once in a while, I find myself manipulating binary data within an application. However, in SQL, it's a royal pain to convert binary data to another built-in data type.

DB2 for i supports the casting of many built-in data types to binary. However, it does not support the reverse scenario of changing binary data to a built-in type. The casting of binary data to a built-in data type such as an integer will earn your application an SQL0461 error: "CAST from BINARY to INTEGER not supported." The good news is that user-defined functions can aid in the casting of binary data to another data type.

(The DB2 for i cast specification that lists the supported casting of the various built-in data types can be found here within the DB2 for i SQL Reference.)

Since, as of i5/OS 7.1, there is little intrinsic support for casting binary data to a built-in type, this is a programming task we'll need to tackle. A scalar user-defined function is the appropriate vehicle that will enable SQL to receive binary data and translate it to the appropriate built-in data type. Shown here is the use of a hypothetical user-defined function called Bin2Int that receives a 4-byte binary input and returns a standard 4-byte integer:

DECLARE @IntegerVariable INTEGER;

SET @IntegerVariable=Bin2Int(BINARY(X'00000010'));

So how do we write this function? There are two basic options available when writing scalar functions: SQL and external. An SQL function is written entirely in the SQL procedural language, and an external function is written in a high-level language such as RPG, COBOL, or Java. Because the SQL language doesn't have great support for interpreting and manipulating binary data (the original problem we started with), this is probably not the best option.

In contrast, RPG is a great language that can be used to convert binary data to a built-in numeric type. For example, using an RPG data structure, four bytes of contiguous data (assumed to be binary) can easily be interpreted as a 32-bit integer:

DdsConvert        DS

D BinData                        4

D IntData                       10I 0 Overlay(BinData)

As a result, the functions to convert binary data to a built-in type will be written as external function in RPG. How will DB2 know to use the RPG logic to do the conversion? The CREATE FUNCTION statement will instruct DB2 how to use the RPG code when the function is used. The attached RPG ILE service program DB2BINARYR (shown in full at the end of this article) contains several subprocedures that DB2 can use for casting binary data to another built-in type.

The subprocedures within the service program and a description of their function are listed here:

• BIN2INT—Convert 4-byte binary to INTEGER data type

• BIN2SMALL—Convert 2-byte binary to SMALLINT data type

• BIN2BIGINT—Convert 8-byte binary to BIG INTEGER data type

• BIN2REAL—Convert 4-byte binary to REAL data type

• BIN2DOUBLE—Convert 8-byte binary to DOUBLE data type

• BIN2NUMERIC—Convert 15-byte binary to NUMERIC(15,5)

• BIN2DECIMAL—Convert 8-byte binary to DECIMAL(15,5)

• BIN2CHAR—Convert varying-length binary to VARCHAR

Each of these subprocedures has a corresponding CREATE FUNCTION statement in the RPG source. Each of these CREATE FUNCTION SQL statements will need to be executed using System i Navigator or STRSQL in order to register the subprocedures for use by SQL.

In case you're unfamiliar with using RPG code with SQL, here is a sample CREATE FUNCTION statement:

CREATE FUNCTION xxxx/BIN2INT

(BINDATA BINARY(4))

RETURNS INTEGER

LANGUAGE RPGLE

PARAMETER STYLE GENERAL

DETERMINISTIC

NO SQL

RETURNS NULL ON NULL INPUT

EXTERNAL NAME 'xxxx/DB2BINARYR(BIN2INT)'

NOT FENCED

This CREATE FUNCTION is used to register function code with DB2; it tells DB2 about the parameters passed to the function and identifies what the function will return when it's completed. It also gives additional information, such as the language of the function and the location of the program (or service program). When SQL attempts to process a statement that invokes the BIN2INT scalar function, DB2 uses the information from this CREATE FUNCTION statement to locate and invoke the RPG code in service program DB2BINARY2 subprocedure BIN2INT.

To find out more about using CREATE FUNCTION, including the keywords, refer to the CREATE FUNCTION section of the DB2 for i SQL Reference manual.

If your eyes can read it, here is a sample of how the functions operate and what they'll return (using hex constants for illustrative purposes):

SELECT BIN2REAL(BINARY(X'41BC51EC')),           /* 2.354 */  

       BIN2DOUBLE(BINARY(x'40378A3D70A3D70A')), /* 2.354 */  

       BIN2INT(BINARY(x'00000100')),            /*   256 */  

       BIN2SMALL(BINARY(x'7FFF')),              /* 32767 */  

       BIN2BIGINT(BINARY(x'000000000000001F')), /*    31 */  

       BIN2DECIMAL(BINARY(x'000000000512345F')),/* 5.12345 */

       BIN2NUMERIC(BINARY(x'F0F0F0F0F0F0F0F0F0F5F1F2F3F4F5')),

                                                /* 5.12345 */

       CHAR(BIN2CHAR(BINARY(x'C1C2C3')),3),     /* ABC */    

       CHAR(BIN2CHAR_1208(BINARY(x'414243')),3) /* ABC */    

  FROM SYSIBM/SYSDUMMY1                                      

All of these functions dealing with numeric data are "strongly" typed, meaning that they expect an input of the appropriate data type and length. These rules can be relaxed (say, passing only three bytes of an integer value instead of four) at the expense of more coding.

The BIN2NUMERIC and BIN2DECIMAL functions arbitrarily convert only numeric data defined with a precision of fifteen and a scale of five (15,5). Technically, the scale is not carried in the binary representation of these formats, so these functions can in fact convert any binary representation of numeric or decimal data with a precision of 15. However, the converted binary value will always be returned from the function with a scale of 5.

The BIN2CHAR function works with only single-byte character data and assumes that the binary data represents character data from the job's default Coded Character Set Identifier (CCSID). So if the SQL job's default CCSID is 37 (U.S. EBCDIC), SQL will simply assume that the binary data represents CCSID 37.

Additional variations of this function can be made by changing the CCSID of the VARCHAR return parameter. In this example, a new function is created that will interpret the binary data as CCSID 1208 (UTF-8).

CREATE FUNCTION xxxx/BIN2CHAR_1208

(BINDATA VARBINARY(16384))

RETURNS VARCHAR(16384) CCSID 1208

LANGUAGE RPGLE

PARAMETER STYLE GENERAL

DETERMINISTIC

NO SQL

RETURNS NULL ON NULL INPUT             

EXTERNAL NAME 'xxxx/DB2BINARYR(BIN2CHAR)'

NOT FENCED

This function will assume that the binary data is stored as 8-bit Unicode. Since the subprocedure BIN2CHAR doesn't really do anything, the character data translation relies on some hocus-pocus magic that operates automatically when data is passed between RPG and DB2. To perform a true text conversion from binary data to any supported character set, the BIN2CHAR character conversion function would need to implement the technique shown in Converting Between Character Sets.

External user-defined functions provide the perfect way to add functionality to DB2 for i. Working with binary data in SQL no longer has to be a burdensome chore.

CODE

//
// Compile Instructions:
// CRTRPGMOD MODULE(DB2BINARYR)
// CRTSRVPGM SRVPGM(DEV/DB2BINARYR)
// EXPORT(*ALL)
//
HNoMain

DBIN2INT PR 10I 0
D parmData 4 Const

DBIN2SMALL PR 5I 0
D parmData 2 Const

DBIN2BIGINT PR 20I 0
D parmData 8 Const

DBIN2REAL PR 4F
D parmData 4 Const

DBIN2DOUBLE PR 8F
D parmData 8 Const

DBIN2DECIMAL PR 15P 5
D parmData 8 Const

DBIN2NUMERIC PR 15S 5
D parmData 15 Const

DBIN2CHAR PR 16384 Varying
D parmData 16384 Varying Const
//
// CREATE FUNCTION DEV/BIN2INT
// (BINDATA BINARY(4))
// RETURNS INTEGER
// LANGUAGE RPGLE
// PARAMETER STYLE GENERAL
// DETERMINISTIC
// NO SQL
// RETURNS NULL ON NULL INPUT
// EXTERNAL NAME 'DEV/DB2BINARYR(BIN2INT)'
// NOT FENCED
//
// CREATE FUNCTION DEV/BIN2SMALL
// (BINDATA BINARY(2))
// RETURNS INTEGER
// LANGUAGE RPGLE
// PARAMETER STYLE GENERAL
// DETERMINISTIC
// NO SQL
// RETURNS NULL ON NULL INPUT
// EXTERNAL NAME 'DEV/DB2BINARYR(BIN2SMALL)'
// NOT FENCED
//
//
// CREATE FUNCTION DEV/BIN2BIGINT
// (BINDATA BINARY(8))
// RETURNS BIGINT
// LANGUAGE RPGLE
// PARAMETER STYLE GENERAL
// DETERMINISTIC
// NO SQL
// RETURNS NULL ON NULL INPUT
// EXTERNAL NAME 'DEV/DB2BINARYR(BIN2BIGINT)'
// NOT FENCED
//
// CREATE FUNCTION DEV/BIN2REAL
// (BINDATA BINARY(4))
// RETURNS REAL
// LANGUAGE RPGLE
// PARAMETER STYLE GENERAL
// DETERMINISTIC
// NO SQL
// RETURNS NULL ON NULL INPUT
// EXTERNAL NAME 'DEV/DB2BINARYR(BIN2REAL)'
// NOT FENCED
//
// CREATE FUNCTION DEV/BIN2DOUBLE
// (BINDATA BINARY(8))
// RETURNS REAL
// LANGUAGE RPGLE
// PARAMETER STYLE GENERAL
// DETERMINISTIC
// NO SQL
// RETURNS NULL ON NULL INPUT
// EXTERNAL NAME 'DEV/DB2BINARYR(BIN2DOUBLE)'
// NOT FENCED
//
// CREATE FUNCTION DEV/BIN2DECIMAL
// (BINDATA BINARY(8))
// RETURNS DECIMAL(15,5)
// LANGUAGE RPGLE
// PARAMETER STYLE GENERAL
// DETERMINISTIC
// NO SQL
// RETURNS NULL ON NULL INPUT
// EXTERNAL NAME 'DEV/DB2BINARYR(BIN2DECIMAL)'
// NOT FENCED
//
// CREATE FUNCTION DEV/BIN2NUMERIC
// (BINDATA BINARY(15))
// RETURNS NUMERIC(15,5)
// LANGUAGE RPGLE
// PARAMETER STYLE GENERAL
// DETERMINISTIC
// NO SQL
// RETURNS NULL ON NULL INPUT
// EXTERNAL NAME 'DEV/DB2BINARYR(BIN2NUMERIC)'
// NOT FENCED
//
//
//
// CREATE FUNCTION DEV/BIN2CHAR
// (BINDATA VARBINARY(16384))
// RETURNS VARCHAR(16384)
// LANGUAGE RPGLE
// PARAMETER STYLE GENERAL
// DETERMINISTIC
// NO SQL
// RETURNS NULL ON NULL INPUT
// EXTERNAL NAME 'DEV/DB2BINARYR(BIN2CHAR)'
// NOT FENCED
//
// Variations of the BIN2CHAR function can be made for specific
// CCSIDs:
//
// CREATE FUNCTION DEV/BIN2CHAR_1208
// (BINDATA VARBINARY(16384))
// RETURNS VARCHAR(16384) CCSID 1208
// LANGUAGE RPGLE
// PARAMETER STYLE GENERAL
// DETERMINISTIC
// NO SQL
// RETURNS NULL ON NULL INPUT
// EXTERNAL NAME 'DEV/DB2BINARYR(BIN2CHAR)'
// NOT FENCED
//
PBIN2INT B Export
DBIN2INT PI 10I 0
D parmData 4 Const

DdsConvert DS
D BinData 4
D IntData 10I 0 Overlay(BinData)

/Free
BinData=parmData;
Return IntData;
/End-Free

PBIN2INT E

PBIN2SMALL B Export
DBIN2SMALL PI 5I 0
D parmData 2 Const

DdsConvert DS
D BinData 2
D IntData 5I 0 Overlay(BinData)

/Free
BinData=parmData;
Return IntData;
/End-Free

PBIN2SMALL E

PBIN2BIGINT B Export
DBIN2BIGINT PI 20I 0
D parmData 8 Const

DdsConvert DS
D BinData 8
D IntData 20I 0 Overlay(BinData)

/Free
BinData=parmData;
Return IntData;
/End-Free

PBIN2BIGINT E

PBIN2REAL B Export
DBIN2REAL PI 4F
D parmData 4 Const

DdsConvert DS
D BinData 4
D FltData 4F Overlay(BinData)

/Free
BinData=parmData;
Return FltData;
/End-Free

PBIN2REAL E

PBIN2DOUBLE B Export
DBIN2DOUBLE PI 8F
D parmData 8 Const

DdsConvert DS
D BinData 8
D FltData 8F Overlay(BinData)

/Free
BinData=parmData;
Return FltData;
/End-Free

PBIN2DOUBLE E

PBIN2DECIMAL B Export
DBIN2DECIMAL PI 15P 5
D parmData 8 Const

DdsConvert DS
D BinData 8
D DecData 15P 5 Overlay(BinData)

/Free
BinData=parmData;
Return DecData;
/End-Free

PBIN2DECIMAL E

PBIN2NUMERIC B Export
DBIN2NUMERIC PI 15S 5
D parmData 15 Const

DdsConvert DS
D BinData 15
D NumData 15S 5 Overlay(BinData)

/Free
BinData=parmData;
Return NumData;
/End-Free

PBIN2NUMERIC E

PBIN2CHAR B Export
DBIN2CHAR PI 16384 Varying
D parmData 16384 Varying Const

/Free
Return parmData;
/End-Free

PBIN2CHAR E

as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1