Assume that for very important reason we have to keep some data as base64 encoded data. This data is very often sent as a part of pure xml file and we strongly believe that keeping this data in this format and thus avoiding converting them every time we improve efficiency of our mission critical application.
It is quite easy in Oracle PL/SQL query language to convert this data to human readable format by using utl_raw and UTL_ENCODE functions.
SELECT CUST_ID, utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) FROM TEST1.CUST_ENCODED_TABLE;
SELECT CUST_ID, utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) FROM TEST1.CUST_ENCODED_TABLE WHERE utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) LIKE '%25%';
But after migrating to DB2 there is a little surprise - there is nothing like UTL_RAW and UTL_ENCODE functions and we have a lot of data in base64 encoded format. How to resolve this issue without costly redesigning our application and converting data once again ?
Solution idea
The solution is very simple - UTFs.
Example
Assume that we have created the following table:
CREATE TABLE TEST.CUST_ENCODED_TABLE
( "CUST_ID" VARCHAR2(45),
"CUST_ADDRESS" VARCHAR2(200)
);
CUST_ADDRESS contains customer address in base64 encoded format.
Now populate our table with sample data:
DECLARE
i NUMBER := 1;
CUST_ID VARCHAR(100);
CUST_ADDRESS VARCHAR(200);
CUST_ENCODED VARCHAR(200);
SEQ NUMBER;
BEGIN
LOOP
i := i+1;
SEQ := LTrim(TO_CHAR(i,'999'));
CUST_ID := 'ID'||SEQ;
CUST_ADDRESS := 'CITY'||SEQ||' '||'ZIP'||SEQ||' '||'ADDRESS'||SEQ;
CUST_ENCODED := UTL_RAW.cast_to_varchar2(UTL_ENCODE.BASE64_ENCODE(utl_raw.cast_to_raw(CUST_ADDRESS)));
dbms_output.put_line(CUST_ID || ':' || CUST_ADDRESS || ':' || CUST_ENCODED);
INSERT INTO TEST1.CUST_ENCODED_TABLE VALUES(CUST_ID,CUST_ENCODED);
EXIT WHEN i>100;
END LOOP;
END;
Query examples
SELECT CUST_ID,utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) FROM TEST1.CUST_ENCODED_TABLE;Migrate to DB2
SELECT CUST_ID, utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) FROM TEST1.CUST_ENCODED_TABLE WHERE utl_raw.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(utl_raw.cast_to_raw(CUST_ADDRESS))) LIKE '%25%';
Now let's migrate our table to db2. You can use IBM Migration Toolkit or accomplish it any other way.
After migration run a query - data are encoded as expected.
db2 "select * from TEST.CUST_ENCODED_TABLE"
..........
ID87 Q0lUWTg3IFpJUDg3IEFERFJFU1M4Nw==
ID88 Q0lUWTg4IFpJUDg4IEFERFJFU1M4OA==
..........
Solution - UDF
Create and compile following simple java class.
import java.io.IOException;And compile using javac provided together with DB 9.7.
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;
import COM.ibm.db2.app.UDF;
public class UTL_ENCODE extends COM.ibm.db2.app.UDF {
public static String BASE64_ENCODE(String in) {
BASE64Encoder encoder = new BASE64Encoder();
return encoder.encode(in.getBytes());
}
public static String BASE64_DECODE (String in) throws IOException {
BASE64Decoder decoder = new BASE64Decoder();
byte[] b = decoder.decodeBuffer(in);
return new String(b);
}
}
/opt/ibm/db2/V9.7/java/jdk32/bin/javac UTL_ENCODE.java
Then copy output UTL_ENCODE.class to the /home/db2inst1/sqllib/function directory.
Next step is to create SQL functions.
CREATE OR REPLACE FUNCTION UTL_ENCODE.BASE64_DECODE(VARCHAR(200))Tip: After changing anything in the java code don't forget to run
RETURNS varchar(200) EXTERNAL NAME 'UTL_ENCODE!BASE64_DECODE'
FENCED
PARAMETER STYLE JAVA
LANGUAGE JAVA
NO EXTERNAL ACTION ;
db2 "CALL SQLJ.REFRESH_CLASSES()"after copying .class file to functions directory. Otherwise DB2 engine will be using the old version of the function.
Don't forget to grant execute right for just created function.
db2 grant execute on FUNCTION UTL_ENCODE.BASE64_DECODE to /user/Final
db2 "SELECT CUST_ID,UTL_ENCODE.BASE64_DECODE(CUST_ADDRESS) from TEST1.CUST_ENCODED_TABLE"
ID87 CITY87 ZIP87 ADDRESS87
ID88 CITY88 ZIP88 ADDRESS88
SELECT CUST_ID,UTL_ENCODE.BASE64_DECODE(CUST_ADDRESS) FROM TEST1.CUST_ENCODED_TABLE WHERE UTL_ENCODE.BASE64_DECODE(CUST_ADDRESS) LIKE '%25%';
CUST_ID
--------------------------------------------- -----------
ID25 CITY25 ZIP25 ADDRESS25
