About Encrypted Data In DB2 UDB

[Suresh]

Hi All

I have one column which has data type of varchar only. in this column
i want to store some secured data (I want to store this data by
encrpting using db2 encryption function).But along with this encrypted
data this column contains normal data also..

But in DB2 while storing encrypted data column should be defined FOR
BIT DATA.
In my case how should I go as my column contains both encrypted and
normal data in same column.

Thanks
Suresh

[Serge Rielau]

Suresh wrote:

Hi All

I have one column which has data type of varchar only. in this column
i want to store some secured data (I want to store this data by
encrpting using db2 encryption function).But along with this encrypted
data this column contains normal data also..

But in DB2 while storing encrypted data column should be defined FOR
BIT DATA.
In my case how should I go as my column contains both encrypted and
normal data in same column.

Split your column. The fact that some part of your column has different
requirements is an indicator that you should have different columns to
begin with.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Suresh]

is there any way other than spliting column bcoz i am storing history
data in old value, new value pair in this table and i can not split the
columns.

[tuarek]

Suresh,

Would it be ok for your data security policy, if you use encrypt scalar
function for your secured data? Use encrypt scalar function when
inserting your data.

insert into mytable values encrypt(...)

Regards,

Mehmet

[Suresh]

While using scalar function of encryption on secured data , column data
type should be for bit data, otherwise i cannot use decrypt scalar
function to retrive data. But because of my design limitation i can not
place datatype as for bit data so any other way?

[Serge Rielau]

Suresh wrote:

While using scalar function of encryption on secured data , column data
type should be for bit data, otherwise i cannot use decrypt scalar
function to retrive data. But because of my design limitation i can not
place datatype as for bit data so any other way?

Suresh you can cast yoru string from and to for bit data.
CAST (c1 AS VARCHAR(10) FOR BIT DATA)
CAST (c1bit AS VARCHAR(10) FOR SBCS DATA)
Since this cast does nothing more that strip the codepage and place it
back it's non invasive.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Suresh]

Thanks it worked for me......

[Mehmet Baserdem]

Suresh,

Can you post your encrypt and decrypt statements' final version ? Just
column phrases would be enough.

thanks

Mehmet

[Suresh]

Here i am forwarding u exapmle with column name XYZ of Varchar type
DECRYPT_CHAR(CAST(XYZ AS VARCHAR(200) FOR BIT DATA))

[Cogito-Ergo-Sum]

But in DB2 while storing encrypted data column should be defined FOR
BIT DATA.

That sounds quite a pretty comprehensive statement.

We did not alter our columns when we ecnrypted a table on z/OS. For en/decryption we used the IBM supplied EDITPROC CRYPDSND. IBM Data Encryption for IMS and DB2 databases, V1.1