I have a column in DB2 that is encrypted. Other than using a pass-thru SQL, I have not been able to figure out how to decrypt this column. I assume you would specify the encryption key on the fieldname in the metadata but I have not been able to find any information on this.

Any help would be appreciated.

Thanks,
John

This message has been edited. Last edited by: jrh,

It sounds like it would come thru if you had a different level of access, one which decrypts during a read. I've encountered something like that a while (years) ago. What's your DBAs have to say about that (decrypt on read access)?

Doug,

Thanks for your response.

Decrypting the column on DB2's side is not a problem. If I do a pass-thru SQL and give it the encryption key in the select statement, it decrypts fine. However, in this case I have to have the encryption key. We do not want the developers to have this key. My plan was to have the encryption key in the metadata and then I could encrypt the .mas file. Maybe my assumption is not valid. Is it possible to specify an encryption key in the metadata.

Thanks,
John

Beginning with version 7.7.03, a new function was added allowing for the insertion of native SQL expressions into a DB2 query. The function is 'SQL'.

Here is an example of how it is used in the select:

DEFINE FILE QUOTE_NIN_INFO
TSN/A124 = SQL.DECRYPT_CHAR(QNIN_SSN, 'ssn_encrypt' );
END

Here is how it might be used in a WHERE statement:

WHERE SQL.DECRYPT_CHAR(QNIN_SSN, 'ssn_encrypt' ) LE '2'

Thanks,
John