Storing Encrypted data in DDS-defined PF

Posted on 2009-04-08
Last Modified: 2013-12-06

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

Question by:daveslash
LVL 45

Expert Comment

Comment Utility

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.

LVL 34

Expert Comment

by:Gary Patterson
Comment Utility

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
LVL 27

Expert Comment

Comment Utility

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
LVL 32

Expert Comment

Comment Utility
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

LVL 14

Accepted Solution

daveslater earned 500 total points
Comment Utility
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                                                                                              
LVL 18

Author Comment

Comment Utility

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


Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now