• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1641
  • Last Modified:

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.

  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!

A          R RSTUFF
A            ENCCCARD      32          COLHDG('ENC CREDIT CARD #')

Open in new window

Dave Ford
Dave Ford
1 Solution
Kent OlsenData Warehouse Architect / DBACommented:

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.

Gary PattersonVP Technology / Senior Consultant Commented:

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

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...?

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Gary PattersonVP Technology / Senior Consultant Commented:
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
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

here are two sql routines that do it very well - I have put them into a service program

 ** standard decryption string                                  
D StringDS        ds                                            
D  String                      128    

PfnDecryptNoHint  B                   Export                              
DfnDecryptNoHint  Pi           128                                        
D DecryptStr                   128    Const                                
      exec sql                                                            
      set :String =                                                        
      DECRYPT_char(Cast(:DecryptStr as char(128) for bit data) ,          
      'password') ;                                                    
  Return String ;                                                          
P                 e  

PfnEncryptNoHint  B                   Export                
DfnEncryptNoHint  Pi           128                          
D Input                        128    Const                
DOutput           s            128                          
      string = Input ;                                      
      exec sql                                              
      set :Output = ENCRYPT_RC2(trim(:String) ,            
                        'password') ;                    
      Return Output ;                                      
P                 e                                                                                              
Dave FordSoftware Developer / Database AdministratorAuthor Commented:

> 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.


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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now