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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 45

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
#Citrix #POC #XenDesktop #vCenter #VMware #ESX
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.

730 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