Introduction

IBM InfoSphere Optim provides a solution to streamline test data management, making it easier and faster to create right-sized test databases that accurately reflect end-to-end business processes. The solution can be extended with the Data Privacy Solution, which delivers powerful data transformation capabilities to mask confidential corporate data so that you can use it safely for application testing. These transformations include everything from simple data masking to pre-packaged transformation algorithms for complex data elements like credit card numbers, e-mail addresses, and national identifiers.

In an environment of data sensitivity, there is a need to mask data not only in traditional relational databases, but also in flat positional and flat delimited files that could be part of a testing scenario. Flat positional files are the same as flat delimited files except that the carriage return is not explicit and there is no delimiter between columns.

If you send files to vendors, off-shore testers, or even internal testers, the data in those files could be subject to regulations. This article shows you how to use the InfoSphere Optim Test Data Management with the Data Privacy Option and the InfoSphere Federation Server to mask data appropriately. You will learn to map the flat file data to tables using Optim Connect Studio, which are then defined as ODBC data sources and made accessible to InfoSphere Federation Server using the ODBC wrapper, as shown in Figure 1.

(View a larger version of Figure 1.)

The high-level steps described in this article include:

1. Validate that you have the proper prerequisites. You need InfoSphere Optim Test Data Management with Data Privacy Option at a minimum of Version 7.3.1. The Optim Connect software needs to be at Version 5.3.2 or later, and the InfoSphere Federation Server should be at Version 9.5 or later. You also need an Optim generic license, which is included with the product.
2. Define the flat delimited file that needs masking (delim1.txt) and the subsequent output file (delim2.txt) so Optim can access them using Optim Connect. Also define the flat positional file that needs masking (posit1.txt) and the subsequent output file (posit2.txt) so they can be accessed by using Optim Connect. Optim Connect Studio is the tool used to define them.
3. Define the files as ODBC data sources so that InfoSphere Federation Server can access them.
4. Create appropriate definitions (ODBC wrapper, nicknames, and user mappings) for the data sources in InfoSphere Federation Server.
5. Extract the data, define the masking algorithm, and insert with masking into the new output file (delim2.txt, posit2.txt). The masking in this case uses one of the simpler functions within Optim (substring), but the entire set of masking functionality is available for these files just as it is with relational tables.

The instructions in this article are based on the assumption that Optim 7.3.1, Optim Connect 5.3.2, and InfoSphere Federated Server 9.5 or later have been successfully installed on a Windows® platform.

Back to top

Validate prerequisites

Optim Test Data Management and Privacy needs to be at Version 7.3.1. Optim Connect needs to be at Version 5.3.1. Optim Connect must be at this level to be able to mask positional flat files. InfoSphere Federation Server needs to be at Version 9.5 or later. Complete the following steps to validate your software levels.

1. Start Optim, and click Help > About Optim, as shown in Figure 2.
   
   Figure 2. Optim version level validation
   
   
   

   The version and level are displayed, as shown in Figure 3.

   
   
   Figure 3. Optim version level
   
   
   
2. Start Optim Connect Studio, and click Help > About Optim Connect Studio, as shown in Figure 4.
   
   Figure 4. Verify Optim Connect version level
   
   
   

   The version and level are displayed, as shown in Figure 5.

   
   
   Figure 5. Optim Connect version level
   
   
   

   Optim Connect must be at this level to work with positional flat files.

3. To check the level of InfoSphere Federation server, enter the command db2licm –l from a DOS prompt, which returns the result shown in Listing 1.
   
   Listing 1. Results of server level check
   

   Product name: "InfoSphere Federation Server" License type: "CPU" Expiry date: "Permanent" Product identifier: "wsfs" Version information: "9.5"

   
   

Back to top

Define the delimited flat file and the positional flat file to Optim using Optim Connect Studio file-table mapping

There are many cases where you might need to mask flat file data. To take advantage of the extensive algorithms available in Optim Data Privacy Solution, the file must be accessed as if it were a relational table with columns. You can then select the columns to mask within the Optim tool and take advantage of the functions and algorithms within Optim. To mask flat files, you need two files: one for input to Optim and one for output from Optim as masked data.

Follow these steps to define the flat file that needs masking (DELIM1.txt) and the output file (DELIM2.txt) using Optim Connect Studio.

1. Create a flat file for this example that includes two rows, as shown in Listing 2.
   
   Listing 2. Example flat file
   

   DAN,222,PRINCETON TIM,333,OPTIM

   
   
2. Define a positional flat file that needs masking POSIT1.txt and an output file POSIT2.txt, using Optim Connect Studio. The flat positional file is shown in Listing 3.
   
   Listing 3. Example flat positional file
   

   AAAAAAA222BBBBBBBBBB CCCCCCC333DDDDDDDDDD

   
   
3. Open Optim Connect Studio, and begin the process of defining the flat file definition and the positional flat file definition. The default view is Configuration, but if it doesn't appear, click the menu option Window > Show view > Configuration, as shown in Figure 6.
   
   Figure 6. Optim Connect configuration screen
   
   
   
4. Expand localhost, then Bindings, and then NAV.
5. Right-click Data Sources, and click New Data Source, as shown in Figure 7.
   
   Figure 7. Start creating a new data source
   
   
   
6. To add a delimited type file to the data sources, enter DELIM in the name field, highlight Delimited Text Files, and click Next, as shown in Figure 8.
   
   Figure 8. Create new data sources
   
   
   
7. Enter the file system where the files are located, and click Finish, as shown in Figure 9.
   
   Figure 9. Fill in file location
   
   
   
8. Expand the DELIM data source, right-click Show Metadata View, and expand DELIM, as shown in Figure 10.
   
   Figure 10. Create table
   
   
   

Create a new table

Follow these steps to create a new table.

1. Right-click the table, and click New table, as shown in Figure 11.
   
   Figure 11. New table wizard
   
   
   
2. Enter the table name (LIM1), and click Finish.
3. On the right pane, enter the data file location (don't use the browse button), change the organization to sequential, and press CTRL+S to save, as shown in Figure 12. Check to make sure your changes are saved.
   
   Figure 12. Table parameter wizard
   
   
   
4. Click the Columns tab at the bottom.
5. Click Insert in the upper right-hand corner. The Insert Column wizard appears, as shown in Figure 13.
   
   Figure 13. Table column wizard
   
   
   
6. Fill in the first column name that you want for the table (NAME), and click OK.
7. Pull down the data type, and scroll until you get to the type you want (varstring) as a data type for the first column, which contains DAN, as shown in Figure 14.
   
   Figure 14. Table attribute wizard
   
   
   
8. Enter the size 10 in the next column. Repeat this for all the columns in your file, using the Insert button with Insert after as the location of each new column. The final table and attributes are shown in Figure 15.
   
   Figure 15. Results of table attribute wizard
   
   
   
9. Press CTRL+S, or click the X next to *LIM1 to close the screen and to save the definition. To display the XML, right-click the table name, and click Open as XML.The XML representation is shown in Listing 4.
   
   Listing 4. XML representation of table attribute
   

   <table datasource="DELIM" description="TEST" delimited="," fileName="C:\Flatfiles\delim1.txt" name="LIM1" organization="sequential" quoteChar="0"> <fields> <field datatype="varstring" name="NAME" size="10"/> <field datatype="varstring" name="IDNUM" offset="12" size="10"/> <field datatype="varstring" name="WORK" size="10"/> </fields> <keys/> </table>

   
   
10. Make sure that the delimited=”,” shows up in the XML. If it is not there, add it and save the file.
11. Repeat the steps to create a new table for a second table called LIM2. Check to make sure the location and sequential attributes are saved.

Validate the new tables

Follow these steps to validate the new tables.

1. Validate that you defined the table on the flat file correctly by right-clicking LIM1, choosing Test, and clicking Next and Finish. If the table is defined correctly, you will see a sampling of the table data, as shown in Figure 16. You can also use the query tool to select the data in the file as if it were a relational table.
   
   Figure 16. Table validate (test) wizard
   
   
   
2. Repeat the validation for LIM2.
3. Close all the delimited file definitions.

Create a definition for the positional flat files

Follow these steps to create a definition for the positional flat files.

1. Click the Configuration tab on the left, and right-click Data Sources.
2. Click New Data Source, and fill in a name (POSIT1).
3. Highlight Flat files, and click Next, as shown in Figure 17.
   
   Figure 17. Data source wizard
   
   
   
4. Enter the location, and click Finish, as shown in Figure 18.
   
   Figure 18. New positional flat file
   
   
   
5. Repeat the steps to create a definition for the positional flat files for a second data source named POSIT2.

Complete the data table

Follow these steps to complete the data table.

1. Right-click POSIT1, and click Show Metadata View,as shown in Figure 19.
   
   Figure 19. Optim Connect Studio
   
   
   
2. Click to expand POSIT1, and right-click on Table.
3. Click New Table, enter POSIT1 for the table name, and click Finish.
4. Enter the complete data file location and name (don’t browse; type the location).
5. Change the organization to sequential, and press CTRL+S, as shown in Figure 20.
   
   Figure 20. Table definition
   
   
   
6. Click the Columns tab on the bottom of the pane.
7. Add the column names and characteristics: NAME string 7, IDNUM string 3, WORK string 10, and CRLF string 2, as shown in Figure 21. Do not change any other characteristics.
   
   Figure 21. Add columns
   
   
   
8. Click the X next to POSIT1 to save.
9. To ensure that the file and location are saved correctly, right-click POSIT1, click Open as XML, and check to see that the entire file name and location is there, as shown in Listing 5. If it is not, edit the XML, and make the changes.
   
   Listing 5. XML representation of POSIT1 datasource
   

   <table datasource="POSIT1" fileName="C:\flatfiles\posit1.txt" name="POSIT1" organization="sequential"> <fields> <field datatype="string" name="NAME" size="7"/> <field datatype="string" name="IDNUM" size="3"/> <field datatype="string" name="WORK" size="10"/> <field datatype="string" name="CRLF" size="2"/> </fields> <keys/> </table>

   
   
10. Repeat the steps to complete the data table for a POSIT2 table definition. The resulting metadata view should look like Figure 22.
    
    Figure 22. Final metadata view output
    
    
    

Use nav_util to test the connection

Test the connection to the primary data source with nav_util before proceeding. Make sure you can select from the primary LIM1 and secondary table. LIM2 is empty, but you should be able to see that it exists. The following steps describe how to use the nav_util tool to test.

1. Go to C:\IBM Optim\Connect\Server\bin (or wherever you have it installed).
2. Enter nav_util execute DELIM. The resulting screen is shown in Figure 23.
   
   Figure 23. List tables output
   
   
   
3. Enter select * from LIM1; and the resulting screen is shown in Figure 24.
   
   Figure 24. Select LIM1 output
   
   
   
4. Repeat the steps to test the connection for the positional files.

Back to top

Define ODBC sources

Follow these steps to define the ODBC data sources so that the InfoSphere Federation Server can access them.

1. Go to Start > Control Panel > Administrative Tools > Data Sources (ODBC).
2. Click System DSN, and click Add, as shown in Figure 25.
   
   Figure 25. ODBC Data Source Administrator
   
   
   
3. Highlight Optim Connect Driver for OBDC 2.5, and click Finish, as shown in Figure 26.
   
   Figure 26. Optim Connect Driver
   
   
   

   The Optim Connect Driver for ODBC 2.5 setup wizard appears, as shown in Figure 27.

   
   
   Figure 27. Optim Connect DSN properties
   
   
   
4. Enter the name on the first screen (DELIM), leave the server location blank, and click Next.
5. Pull down the User profile field, and click NAV.
6. Enter nav (lower case) in the password field, and check Save password (unsafe), as shown in Figure 28.
   
   Figure 28. Optim Connect 2.5 setup
   
   
   
7. Test the login, and click Next.
8. For the binding, leave it as NAV. Pull down the Default datasource (catalog) field, and select your data source (DELIM).
9. Check Single datasource (for schema), test the datasource, and click Next, as shown in Figure 29.
   
   Figure 29. Optim Connect binding properties
   
   
   
10. Take all the defaults for the tracing and debugging, click Next, and note the entries, as shown in Figure 30.
    
    Figure 30. Final entry for data source DELIM
    
    
    
11. Click Finish.
12. Repeat the steps to define ODBC sources for both POSIT1 and POSIT2, keeping in mind that you grouped the two files with DELIM and separated the two files for POSIT1 and POSIT2 to show an alternative way of defining the files.

Back to top

Define the data sources to InfoSphere Federation Server

Now you will define the two delimited files and two positional files to InfoSphere Federation Server. (This article assumes that the InfoSphere Federation Server is installed and working before proceeding with the next steps.) You'll then associate those files with the ODBC wrapper so that InfoSphere Federation Server will use ODBC to extract from and insert to those files. It is very important to be consistent and use the same AUTHID throughout the federated definitions and the Optim definitions. This article uses db2work for all connections in the federated hub and for all Optim directory and aliases.

Follow these steps to define the data sources to InfoSphere Federation Server.

1. From the DB2 Control Center, expand your database and your federated objects, right-click Federated Database Objects, and click Create Wrapper, as shown in Figure 31.
   
   Figure 31. Control Center base
   
   
   
2. Choose the ODBC as the data source for the wrapper, and enter the name for your wrapper (DELIM), as shown in Figure 32.
   
   Figure 32. Create wrapper
   
   
   
3. Click the Settings tab.
4. Change the value of DB2_FENCED to Y, and click OK to save, as shown in Figure 33.
   
   Figure 33. Wrapper settings
   
   
   
5. Expand the DELIM wrapper, right-click Server Definitions, and click Create, as shown in Figure 34.
   
   Figure 34. Create server definitions
   
   
   
6. Click Discover, as shown in Figure 35.
   
   Figure 35. Create server definitions
   
   
   
7. De-select all the check boxes for the objects currently selected, and select the check box for the data source you want (DELIM), as shown in Figure 36.
   
   Figure 36. Server selection
   
   
   
8. Click the Properties button, and click the Settings tab.
9. Be sure the check boxes for PUSHDOWN and DB2_MAXIMAL_PUSHDOWN are selected as the server, and change those values of N to Y, as shown in Figure 37.
   
   Figure 37. Server settings
   
   
   

Map the local user IDs with the NAV user ID

Follow these steps to map the local user IDs with the NAV user ID used in Optim Connect Studio.

1. From the Object View, right-click User Mapping, and click Create, as shown in Figure 38.
   
   Figure 38. Create user mapping
   
   
   
2. Click the user you want for the local login (DB2WORK), and move it to the second pane by clicking on the right arrow (>) button, as shown in Figure 39.
   
   Figure 39. User definition
   
   
   
3. Click the Settings tab, and enter the user ID and password that you will use for Optim Connect. For the example, the user ID and the password are both nav, as shown in Figure 40.
   
   Figure 40. User settings
   
   
   
4. Click OK to finish.

Create a nickname

Follow these steps to create a nickname. Note that you can instead skip ahead to create nicknames using an alternative method.

1. Right-click Nicknames, and select Create, as shown in Figure 41.
   
   Figure 41. Create nicknames
   
   
   
2. Click Add, enter the remote schema (public) and remote table name (DELIM1), and click OK, as shown in Figure 42.
   
   Figure 42. Add first nickname
   
   
   
3. Repeat the steps to create a nickname for DELIM2. The resulting screen is shown in Figure 43.
   
   Figure 43. Add second nickname
   
   
   

Create nicknames using an alternative method

You could instead use discovery to find and create nicknames. Follow these steps to create nicknames using discovery.

1. Right-click Nicknames, and click Create.
2. Click Discover on the right side.
3. Enter the user ID (nav) and password (nav), as shown in Figure 44.
   
   Figure 44. Discover nicknames
   
   
   
4. Click OK.
5. Check the nicknames you want to define, and click OK, as shown in Figure 45.
   
   Figure 45. Select nicknames
   
   
   

Define data sources for POSIT1 and POSIT2

Repeat the steps to define the data sources to InfoSphere Federation Server for both POSIT1 and POSIT2.

Back to top

Extract and de-identify data

This section describes how to use IBM Optim to define an extract (using an access definition file), then extract the data from DELIM1 and the insert from the extract file into DELIM2 using the masking algorithm you define. You will also do the same for POSIT1 and POSIT2 (non-delimited flat files).

1. Start Optim.
2. Select Options > Product, as shown in Figure 46.
   
   Figure 46. Start Optim
   
   
   
3. Enter your Optim password (optim), and select OK, as shown in Figure 47.
   
   Figure 47. Optim password
   
   
   
4. Click Database.
5. Select the check box for Allow OPTIM PK/Rels on DB Views (Allow Primary Keys and Relationships on Database Views) to enable Optim to access views in the database, and click OK, as shown in Figure 48.
   
   Figure 48. Optim database options
   
   
   

Complete the extract process

Follow these steps to complete the extract process.

1. To start the extract process, select File > New > Extract, as shown in Figure 49.
   
   Figure 49. Start extract
   
   
   
2. Enter a description, which is useful for remembering what this is used for, in case you want to do it again.
3. Enter a name for the new extract file (DELIM1.XF), and select the Named radio button for the access definition.
4. Enter an access definition name (DELIM1.AD), and click the icon for the access definition editor, which is located two icons to the right of the running man icon, as shown in Figure 50.
   
   Figure 50. Extract Request Editor
   
   
   
5. Click the Start Table browse button, as shown in the middle of Figure 51.
   
   Figure 51. Browse start table
   
   
   
6. Highlight your primary delimited file table (LIM1), click Select, close the access definition, and save the file, as shown in Figure 52.
   
   Figure 52. Select start table
   
   
   
7. Click the running man icon to extract the data.
8. Click Proceed if the NO RUNSTATS message screen appears. You should see a screen similar to Figure 53 that has the statistics from the table.
   
   Figure 53. Statistics report
   
   
   
9. Close the extract window, and save the extract name as DELIM1.

Complete the insert with masking

Follow these steps to do the insert with masking.

1. Select File > New > Insert, and enter the following fields of the insert, as shown in Figure 54.
   • Description is Insert DELIM2
   • Source File is DELIM1.XF (the source file is what you just extracted)
   • Control File is DELIM1.cf
   • Table Map Name is DB2WORK.DELIM2 (the table map is new, and you will be editing it next)
   
   
   Figure 54. Insert Request Editor
   
   
   
2. Edit the table map by clicking the icon for the table map editor (two to the right of the running man icon), as shown in Figure 54. The table map is used to define how you will mask the columns. You can pick from the base functions within Optim (SUBSTRING, for example), or you can use the algorithms for more complex masking of credit card numbers.
3. Enter the qualifier (FEDERATE.DB2WORK) and a new column map in the table map.
4. Enter the Column Map ID (DELIM2) and a column map name under Column Map or LOCAL (DELIM2).
5. Right-click the column map, and click Open Column Map, as shown in Figure 55. Make sure you change the destination table to your new table (LIM2).
   
   Figure 55. Table Map Editor
   
   
   
6. Mask column 3 by entering SUBSTR(WORK1,3) in the Source Column field that contains WORK, as shown in Figure 56.
   
   Figure 56. Mask column
   
   
   
7. Close and save the column editor, and close the table map editor.
8. Click the running man icon to do the insert to the second delimited file, as shown in Figure 57.
   
   Figure 57. Run insert request
   
   
   

   The statistics screen appears, detailing what was inserted, as shown in Figure 58.

   
   
   Figure 58. Insert statistics
   
   
   
9. Select from the second delimited file, or browse for the file, to see if the masking has occurred. Note the truncation due to the substring in the column map, as shown in Listing 6.
   
   Listing 6. Check for masking
   

   C:\flatfiles>type delim2.txt DAN,222,PRI

   
   

Back to top

Conclusion

High-quality test data based on production data is an important component of a test strategy. However, the consequences of using private data for testing purposes, even inadvertently, can be significant. Optim Test Data Management with Data Privacy Option provides an excellent solution to produce right-sized test databases that reflect business objects and can be de-identified using a variety of algorithms.

This article shows how you can extend those capabilities to flat files by using InfoSphere Federation Server to map flat files to tables so that any private data in those files can be extracted and de-identified using the powerful capabilities of the Optim Test Data Management and Data Privacy Solution.

Back to top

Appendix

The nav_util program in the directory of c:\ibm optim\connect\server\bin is a great diagnostic tool to use if you run into errors during the definition of the tables. Some helpful functions include the following:

• Nav_util check license
• Nav_util execute delim

Once you are in the tool, there are many different utilities to check your definitions and test.

NavSQL>
> describe <ds_name:>table_name <full> <index> to describe a table or a view
> describe <ds_name:>@procedure_name to describe a procedure
> help
> list catalogs <catalog-mask>
> list columns <table-mask> <column-mask> <case-sensitive>
> list procedure_col <procedure-mask> <column-mask> <case-sensitive>
> list procedures <procedure-mask>
> list statistics <table-mask>
> list synonyms <synonym-mask>
> list tables <table-mask>
> list views <view-mask>
> native_describe <ds_name:>table_name <full> <ndex> to get a native description 
   of a table or a view
> query_describe sql_statement to describe an sql statement
> rollback
> any select, call or update statement
> show datatype datatype_name
> show driver driver_name
> tdp-default ds_name
> explain ['<file_name>'] <sql statement>

NavSQL >    

Back to top

Acknowledgments

A special thanks to Tim Smith for technical guidance.

Resources

Learn

• Watch the demo A day in the life of a DBA, including a section on using Optim Test Data Management with the Data Privacy Option.
  
  
• See the Optim Test Data Management product page to learn more about Optim Test Data Management.
  
  
• Peek at the Optim Data Privacy product page to learn more about Optim Data Privacy.
  
  
• Join the Integrated Data Management community space to find information and conversation about Integrated Data Management products, technologies, and best practices.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Follow developerWorks on Twitter.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Follow the Optim LUW forum to share innovative ideas and experiences with others who have implemented any of the solutions in the Optim LUW portfolio.
  
  
• Participate in the Optim z/OS forum to share innovative ideas and experiences with others who have implemented any of the solutions in the Optim z/OS portfolio.
  
  
• Dive into the Integrated Data Management experts blog to find insights, advice, hints and tips, and lessons-learned on topics on everything from data modeling and database application development to database administration, performance monitoring, and more.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Dan Callahan is a Senior Certified IT Specialist in Information Management with a focus on DB2 and has worked on all products and platforms that run DB2 and integrate with DB2. He joined IBM in 1987 as a systems programmer/database analyst for MVS and now focuses on DB2 for Linux, UNIX, and Windows and any product that interfaces with DB2. He also has given classes in China and Germany on the various aspects of the Information Management software portfolio.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Validate prerequisites
• Define the delimited flat file and the positional flat file to Optim using Optim Connect Studio file-table mapping
• Define ODBC sources
• Define the data sources to InfoSphere Federation Server
• Extract and de-identify data
• Conclusion
• Appendix
• Acknowledgments
• Resources
• About the author
• Comments

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers