I have a DB2 table where one of the columns is encrypted using the two parameter variant of ENCRYPT (so the password is supplied in the SQL itself). Unfortunately due to an ETL issue the column got populated by data with two different encryption keys. I want to fix this, by running some kind of script, but unfortunately I cannot do a mass update as DECRYPT_CHAR will throw an exception on the first row where it couldn't use my encryption key.

Is there a way to write a SQL script that would go through all of the rows and re-encode the rows which use the wrong encryption keys without throwing an exception? I'd prefer plain SQL if possible.


Yes there is. You could do this with SQL PL to create a stored procedure to do this. Or if you prefer other script languages you could use KSH, BSH, or even Perl.

The point being to catch the errors or exceptions (a little easier to do with SQL PL IMHO) and then you can react to them the way you wish.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.