Hello Snorri,
I think you will have to use a temporary table for the load of the data.
Here a small test that works:
set encryption password "Kalu knows the solution";
unload to /tmp/kalu_test
select customer_num, lname, fname, encrypt_aes(fname)
from customer;
drop table if exists customer_enc;
create temp table customer_enc(
customer_num int,
lname varchar(128),
fname varchar(128),
fname_enc varchar(128)
) with no log;
drop table if exists customer2;
create table customer2(
customer_num int,
lname varchar(128),
fname varchar(128)
);
load from /tmp/kalu_test
insert into customer_enc;
insert into customer2
select customer_num, lname, decrypt_char(fname_enc,"Kalu knows the solution") from customer_enc;
select * from customer2;
------------------------------
Gerd Kaluzinski
------------------------------
Original Message:
Sent: Tue March 23, 2021 06:00 PM
From: Snorri Bergmann
Subject: Simple column encryption
We export data from our DB regularly and now we need to encrypt some columns. Something like
unload to data.unl select col1, col2, crypt(col3), col4 ....
This doesn't have to be super secure and the columns we encrypt (char and int) are rather short so we'd like to keep the encrypted string as short as possible. The function must also be NOT VARIANT (always returns the same output for the same input). Has anyone done something similar?
We are using 14.10
Thanks in advance,
-Snorri
------------------------------
Snorri Bergmann
------------------------------
#Informix