Declare the BINARY Data Type Using DDS

Published: October 18, 2006

by Michael Sansoterra

We've all heard by now that defining tables using DDS is becoming outdated and that developers need to use SQL in order to access the relatively new data types, including DATALINK and Large Objects (BLOB, CLOB, and DBCLOB).

I recently ran into a situation where I wanted to change a field in an existing table to use the new (as of V5R3) BINARY data type for encrypting credit card data. (Recall that the BINARY data type is similar to character data tagged with a CCSID of 65535. However, data in a BINARY column will never be translated whereas the character data tagged with CCSID 65535 may still be translated depending on the environment settings.) Because it is a "new" SQL data type, I figured the BINARY data type wasn't available in DDS.

However, because of issues with the customer's source management software, I still wanted to re-define the table using the existing DDS rather than change the table definition and related indexes to SQL. It was at this time that I did a little snooping and found that the BINARY data type is definable using DDS. The trick to getting this to work is by specifying a data type of '5' in the data type column of the field definition. Here is an example:

R DATAFILER                                                 
  CHARDATA     200A         TEXT('EQUIV TO SQL CHAR(200)')  
  BINARYDATA   2005         TEXT('EQUIV TO SQL BINARY(200)')

To create a VARBINARY field instead of BINARY, just add the VARLEN keyword to the field definition.

The main stumbling block here is that this information is documented in the DDS manual but is not documented in the SEU help on V5R3 or V5R4. After checking the SEU help I almost gave up! I constantly need to remind myself to check multiple sources before giving up on a problem.

Incidentally, as far as converting an existing field, a character field can be converted to a binary field using the CHGPF command as long as the character field is tagged with CCSID 65535. If the character field is not tagged with CCSID 65535, you'll have to change the table definition twice: once to change the CCSID of the column to 65535 and then again in order to change the field type from character to binary.

In case you're like me and assumed that BINARY isn't definable with DDS, remember to use data type '5'. While it is helpful to know SQL for using the new data types, for legacy files it is handy to be able to use binary columns in existing DDS definitions.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.