Handling GUID's (UniqueIdentifiers) in DB2 using VB.NET

Continuing the saga of DB2, I am now trying to handle GUID's/UniqueIdentifiers from the Enterprise Library. First thing I had to get a handle on is that DB2 UDB has no datatype similar to a GUID. In Microsoft SQL they are represented as UniqueIdentifiers and used quite extensively. Oracle represents them as RAW(16). When I ran the conversion on my database using the IBM Migration Toolkit (MTK), the GUID's in my database were converted to CHARACTER(16) FOR BIT DATA. So basically, a container to hold 16 bytes worth of data. It takes 2 hexadecimal characters to represent 1 byte so 32 hexadecimal characters is 16 bytes or 128-bits.

The problem is how to supply a 32 character GUID and get DB2 to accept it. On the SQL Command line, it looks like this:

[code lang='sql']INSERT INTO mytable (RecordID,Name) VALUES (cast(x'EC553531235042008E09C1A69114C2CC' as char(16) for bit data),'Hello');[/code]

Now the magic is in the cast function, in DB2, cast allows you to cast from one data type to the other sort of like ctype in VB.NET. The other key is the preceding x to tell DB2 that I want it to understand this is a hexadecimal value being passed. I found that little bit in Google but for the life of me, can't find it in the DB2 documentation anywhere.
On updates it looks like:

[code lang='sql']update mytable set Name='Hello World' WHERE hex(RecordID) ='EC553531235042008E09C1A69114C1CC'[/code]

On to selects, command line looks like:
[code lang='sql']update mytable set Name='Hi There' WHERE hex(RecordID) ='EC553531235042008E09C1A69114C2CC'[/code]

The last bit I found was in actually using the Enterprise Library Data Access Application Blocks to get at DB2. The first trick is to recompile the data dll once you have installed the DB2 Enterprise Library Add-Ins. Here is a guide to the recompile . Once all of this is done, you can add the DB2 provider in Enterprise Library Configuration. I use all stored procedures for handling my database interface. I had to update most of my stored procedures to handle the new GUID formats in DB2. Here is an example:

[code lang='sql']
CREATE PROCEDURE MyTableAddSingle (v_RecordID CHAR(16) FOR BIT DATA,
v_Name VARCHAR(100) )
LANGUAGE SQL

BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE l_error CHAR(5) DEFAULT '00000';

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET l_error = '00000';

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING

BEGIN

SET l_error = SQLSTATE;

IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN

RESIGNAL;

END IF;

END;

INSERT INTO MyTable (RecordID,Name) VALUES (v_RecordID,v_Name);
COMMIT;

END
[/code]

Note that most of this was autogenerated by IBM MTK, just the insert statement is original. Now onto the VB.NET side of things.

[code lang='vb']
Dim db As Database
db = DatabaseFactory.CreateDatabase("DB2_instance_name")
Dim dbCmdWrap As DBCommandWrapper = db.GetStoredProcCommandWrapper("MyTableAddSingle)
dbCmdWrap.AddInParameter("RecordID", DbType.Binary, Guid.NewGuid.ToByteArray)
dbCmdWrap.AddInParameter("Name", DbType.String, "Hi There")
db.ExecuteNonQuery(dbCmdWrap)
[/code]

I thought it was interesting that I didn't have to supply the ParameterName as V_RecordID, just RecordID worked. This was mostly trial and error. I tried a bunch of different combinations. DbType.Guid throws an unhandled exception, DbType.String dies out because we can't cast it internal to the stored procedure. I have a lot more to convert but thought I would share 8-days worth of research with you. I must note that I opened a support ticket with IBM Trial Support and they gave me a lot of hints on getting this done. Only on my last questions to them was I told to RTFM :).

Please feel free to post a comment if you have a better way to do this or more explination to add.