Help with ENCRYPT and DECRYPT_CHAR functions

[Wes Gyure]

Included in Fix Pack 3 for DB2 7.1 are two new functions that encrypt
and decrypt fields based on a password.

I need help using these functions. I tried the simple SQL statement
documented
in the ReleaseNotes and SQL Reference Guide, however the documentation
is
wrong and the SQL statements don't work.

I need help figuring out the decrypt_char(? ? ? ) function. The
documentation says
that the following will work for my table:

table xtable:
column name varchar(50)

insert into xtable (name) values encrypt('some_name', 'password', '');
// this works great

then to retreive information;

select decrypt_char(name, 'password') from xtable;

I receive this error every time ---
SQL0171N The data type, length or value of argument "1" of routine
"SYSIBM.DECRYPT" is incorrect. SQLSTATE=42815

Help!!

[Liu Liu]

Hi,

The result of the ENCRYPT function is a for bit data, so you need to
define your column as varchar(50) for bit data.(Notes that if your original
data is 50 bytes, it will be padded to the next 8 bytes boundary (i.e. 56
bytes), so it's better to define the column as varchar(56) for bit data)
Hope that helps.

Liu

"Wes Gyure" <Wes....@tivoli.com> wrote in message
news:3B0AC52E...@tivoli.com...

[Dirk Wollscheid]

I wanted to try that to, but for some reason I don't get the encrypt
functions in the catalogs ("select * from syscat.functions where funcname
like '%CRY%'" returns nothing). I did a "db2updv7 -d sample". Is there any
way of getting the level for a database or tracing this?

The other thing that could have gone wrong is that I had V7 FP2 on my system
and then updgraded to FP3 and then to V7.2. Could this be the problem?

Thx, Dirk

[Liu Liu]

Dirk,

ENCRYPT is a built-in function (in SYSIBM), so it will not show up in
the catalog. Please check sysibm.sysversions. It will tell you the version
of the database.

Liu

"Dirk Wollscheid" <wol...@us.ibm.com> wrote in message
news:9eglev$33e$1...@stlnews.stl.ibm.com...

[Wes Gyure]

Liu,
Thanks for the help, but I'm not maxing out the field. I made the field 50
characters because I know that I will only enter something that is 25
characters. Is there a way to delimeter a column as for bit data or just as
long as it is a multiple of 8.
I did create a table with one column (column1 varchar(56)) and run the commands

to encrypt the word ('test') in the column. That works fine. Then I issue the
command
select decrypt_char(column1, 'mypassword') from mytable
I still receive the SQL1071N error :

SQL0171N The data type, length or value of argument "1" of routine
> "SYSIBM.DECRYPT" is incorrect. SQLSTATE=42815

Have you had this function working yet?

Wes

[Liu Liu]

Wes,

You do need to define the column as varchar for bit data. The length
doesn't really matter as long as you don't max it out (you will get a
different error if you do.) Try this:

create table mytable (column1 varchar(56) for bit data);
insert into mytable values (encrypt('test', 'mypassword'));
select decrypt_char(column1, 'mypassword) from mytable;

Liu

"Wes Gyure" <Wes....@tivoli.com> wrote in message

news:3B0C12FF...@tivoli.com...