Go Premium for a chance to win a PS4. Enter to Win


Storing Encrypted data in DDS-defined PF

Posted on 2009-04-08
Medium Priority
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:Dave Ford
LVL 46

Expert Comment

by:Kent Olsen
ID: 24102329

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 35

Expert Comment

by:Gary Patterson
ID: 24102568

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

ID: 24103160

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

LVL 35

Expert Comment

by:Gary Patterson
ID: 24104907
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 33

Expert Comment

ID: 24108268
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 2000 total points
ID: 24149950
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

by:Dave Ford
ID: 24150273

> 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 Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

877 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