Storing Encrypted data in DDS-defined PF
Greetings, Experts!
On an AS/400, I need to add a column to an existing DDS-defined physical file to hold an encrypted credit-card number. Unfortunately, I cannot change this one into SQL-defined.
If this was an SQL-defined table, I'd simply add the "FOR BIT DATA" qualifier to the column definition so it could hold the encrypted data.
e.g.
encryptedCreditCard char(32) for bit data
But, since this table is DDS-defined, how do I add the "FOR BIT DATA" portion to the column? I've searched the manuals, but I must be missing it.
Thanks in advance!
DaveSlash
A R RSTUFF
A ENCCCARD 32 COLHDG('ENC CREDIT CARD #')
daveslash,
No way to add "for bit data" other than SQL. You could try using ALTER TABLE to add the column, but if SQL isn't allowed, then altering the table with SQL probably is a no-no too.
Perhaps datatype "5" will do the trick for you - Binary Character? I'm not sure if it is 100% compatible. Also, IBM recommends using BInary WITH BIT DATA rather than character.
Other alternatives to consider:
Character with CCSID (65535)
"B" - Binary
"H" - Hexadecimal (like character with no CCSID associated, so never gets translated).
Create an extension table for the credit card info with SQL, and create a join logical.
The big difference is the options for conversion to other types, default rules, and padding rules.
- Gary Patterson
No way to add "for bit data" other than SQL. You could try using ALTER TABLE to add the column, but if SQL isn't allowed, then altering the table with SQL probably is a no-no too.
Perhaps datatype "5" will do the trick for you - Binary Character? I'm not sure if it is 100% compatible. Also, IBM recommends using BInary WITH BIT DATA rather than character.
Other alternatives to consider:
Character with CCSID (65535)
"B" - Binary
"H" - Hexadecimal (like character with no CCSID associated, so never gets translated).
Create an extension table for the credit card info with SQL, and create a join logical.
The big difference is the options for conversion to other types, default rules, and padding rules.
- Gary Patterson
daveslash:
I'm not at all sure that FOR BIT DATA is required -- IF all it is going to do is "hold an encrypted credit-card number." But I suspect that it will do more than that; I suspect that you actually want to use SQL to manipulate the column as encrypted data. So...
You can use ALTER TABLE against a DDS PF. However, at least through V6R1, you shouldn't be able to specify BInary FOR BIT DATA. BINARY and BIT are incompatible on all systems I try.
And the reasons you can't regenerate the file as SQL DDL...?
Tom
I'm not at all sure that FOR BIT DATA is required -- IF all it is going to do is "hold an encrypted credit-card number." But I suspect that it will do more than that; I suspect that you actually want to use SQL to manipulate the column as encrypted data. So...
You can use ALTER TABLE against a DDS PF. However, at least through V6R1, you shouldn't be able to specify BInary FOR BIT DATA. BINARY and BIT are incompatible on all systems I try.
And the reasons you can't regenerate the file as SQL DDL...?
Tom
Oops, my typo. Should have read something like: "Using BINARY for bit-oriented data rather than CHARACTER FOR BIT DATA". Declaring "FOR BIT DATA" just suppresses CCSID conversion, as far as I know, which renders it meaningless on BINARY columns. I'm having trouble finding where I read that recommendation, too, so you may not want to make it words to live by...
Just FYI: I created a DDS PF with "H" data type (SQL sees it as CHARACTER with CCSID 66535), and was able to use the column with ENCRYPT_RC2 and DECRYPT_BIT under V5R3. Then I created an SQL view with DECRYPT_BIT embedded, and it worked beautifully, requiring me to SET ENCRYPTION PASSWORD in the STRSQL session before being able to view records using the view (I didn't hardcode the password).
Big fun. I hadn't had a chance to play with encrypted columns yet in DB2/400. Nice to see the major functionality wirks with DDS and SQL DDL-described files. More and more features are SQL only, and a lot of shops are still standardized on DDS only.
Tom: that leads me to my bet for the "no SQL DDL" requirement .... shop standards. daveslash?
- Gary Patterson
Just FYI: I created a DDS PF with "H" data type (SQL sees it as CHARACTER with CCSID 66535), and was able to use the column with ENCRYPT_RC2 and DECRYPT_BIT under V5R3. Then I created an SQL view with DECRYPT_BIT embedded, and it worked beautifully, requiring me to SET ENCRYPTION PASSWORD in the STRSQL session before being able to view records using the view (I didn't hardcode the password).
Big fun. I hadn't had a chance to play with encrypted columns yet in DB2/400. Nice to see the major functionality wirks with DDS and SQL DDL-described files. More and more features are SQL only, and a lot of shops are still standardized on DDS only.
Tom: that leads me to my bet for the "no SQL DDL" requirement .... shop standards. daveslash?
- Gary Patterson
you can use MI to DES encrypt the data. Let me dig out my copy of leif's MI guide. it's been a while...
you can find it here
http://www.leif.org/as400/
ShalomC
you can find it here
http://www.leif.org/as400/
ShalomC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
> tliotta:
> I suspect that you actually want to use SQL to manipulate the column as encrypted data.
Precisely. The problem arises not when storing the data, but rather when selecting it back. Without the "FOR BIT DATA" clause, I get a SQL0171 error when decrypting it. (SQL0171 = Argument 01 of function DECRYPT_CHAR not valid.)
> And the reasons you can't regenerate the file as SQL DDL...?
This will be a new column in an existing DDS-defined table from our legacy, green-screen system. Company policy states we can create tables using SQL for new development, but we must retain DDS for existing tables.
daveslater:
Once again, you have hit the nail directly on the head. You are amazing. I hadn't thought about using a CAST to "FOR BIT DATA", but that works beautifully.
Thanks!
DaveSlash
You're on the wrong side of the question. I expect YOU to answer the AS/400 questions. :)
I'm not sure that you can. Adding this to a physical file definition would require the the file be rebuilt to match the new definition. I'm not aware of the AS/400 version of DB2 doing this.
Kent