Affects Version/s: None
Security Level: Public (Public: Anyone can view)
KAI Review Status:Not Required
KTI Review Status:Not Required
Forwarding on this change from Poonam Bhargava <email@example.com> from the KFS team at IU. Please see the attached patch file which implements this change.
Here is how it works currently:
- Creates a backup table for the given table - "create table <table_name>_bak as select * from <table_name>"
- Encrypts the given column in the original table
- Drops the backup table in the end
- In case of an exception, it rolls back the transaction and restores the original table from the backup table
The problems we are noticing:
- The process is very slow for huge tables, e.g. that have over a million rows.
- If there is an exception (say network disconnection etc.), it rolls back everything, thus requiring every row to be processed again.
Changed Approach in the customization:
Create a back up table and add an encrypt indicator column to it.
Perform encryption on the backup table.
- Retrieve n rows at a time, encrypt them and commit them as a batch.
- Update encrypt_ind to Y when a row has been encrypted.
- In case of an error, rollback updates on this batch of n rows.
- Back up table is dropped only if the whole process is successful - i.e. the whole backup table has been successfully encrypted and the original table has been refreshed from the backup table.
- Process is much faster. Processing time has reduced from ~10 hours to ~2.2 hours, for IU reference tables.
- An exception doesn't rollback everything. Since the back up table is dropped only if the whole process is successful, the process can be resumed from the last successful commit.
This is an impacting change. Please let me know if you have any questions or concerns.