Related Extract with Disguise for zOS DB2

BMC AMI Test Drive 
BMC Compuware Test Data Privacy – Related Extract with Disguise zOS Db2

This tutorial will introduce you to the BMC AMI DevX Data Studio Related Extract to create a relationally intact subset of data while using Disguise Rules to change and protect sensitive data.

Revised: 2024/03/14 00:00 


Getting Started

Instructions:

  • This guide contains many screenshots to provide a visual reference
  • Please note each place that you must enter your own specific ID or number
  • You must complete each step before proceeding to the next to successfully follow the test drive script

If, at any point during your experience, your host connection times out, you may need to log back in to the TestDrive host connection.

image-2023-4-10_9-27-14.png

If at any time during the execution of this script the Common Enterprise Services Login popup is shown, enter your test drive ID and password under User ID and Password, check the Save credentials box and then depress the ENTER key or click OK.


BMC AMI DevX Data Studio

The Data Explorer View is a part of BMC AMI DevX Data Studio which simplifies the complex task of managing and preparing data for testing. This enables developers and test engineers to perform data-related tasks without writing programs or scripts, coding SQL, or using multiple utilities. They can easily create specifications to extract related sets of data from one environment and load them into another. Extracting just the relevant data needed for testing is done using easy-to-use selection criteria or starting key values. The extracted data will remain relationally intact based on the referential integrity (RI) or application relationships (AR). Data Privacy rules can be applied at the time of extract to protect sensitive data. Data can then be loaded into the environment of choice by supplying the necessary target information in a Related Load job.

In this exercise you will:

  • Create a Related Extract specification
    • Extract all related data using Referential Integrity
    • Create Application Relationships
    • Create Selection Criteria to extract only specific data
    • Disguise Data during Extract


 Create a Related Extract Specification


Do This
  • Open the DevX Data Studio perspective in Workbench for Eclipse.

image-2024-3-14_11-9-38.png

Do This
  • Select the Data Explorer tab in the top left.

image-2023-4-4_13-17-19.png

Do This
  • Double click the FAEXRepos Repository in the Data Explorer tab on the left pane or right-click and select Open.

image-2023-4-4_13-18-23.png


Do This
  • Right-click on Related Extract and select New.

image-2023-4-4_13-20-11.png

In this example you will extract data from multiple z/OS DB2 tables. Note that you can use this same process against DB2, DB2 z/OS, Oracle, Sybase, and SQL Server.


Do This
  • Name the related extract job DB2_RELATED_EXTRACT.
  • Click the Browse button beside Driving Object to select a table as a starting point within the database.

image-2023-4-4_13-21-21.png


Do This
  • For Type select Db2 for z/OS and select Host TestDrive.
  • Click on the SSID drop down menu. 
  • You will be prompted to enter your Test Drive ID (CWEZ###) and password. 
  • Select the DBCC schema for the SSID.

image-2023-4-16_15-54-7.png


Do This
  • Enter your CWEZ### ID for the schema name.
  • Click List.
  • Select the ORDER_TABLE.
  • Click OK.

image-2023-4-16_15-55-3.png


Do This

This will populate the Driving Object field and show the Host connection.


  • Click Finish.

image-2023-4-16_15-55-59.png


Do This
  • Check the box Use referential integrity and Click the Discover Relationships button below it.
  • Type in the Extract file location a dataset name of "CWEZ###.EXTRACT.DB2" using your Test Drive ID.
  • Click the Objects tab at the bottom of this section.

image-2023-4-16_15-58-5.png


You now see all tables related to the ORDER_TABLE through referential integrity and will be included in the extract.

Do This
  • Click the Relationships tab at the bottom of this section.

image-2023-4-16_16-0-52.png

The Relationships view shows the objects as parents and dependents and the type of relationship. In this example all objects are related through RI (referential integrity).

Do This
  • You can adjust the columns as needed for readability.
  • Click the Visualization tab at the bottom of this section.

image-2023-4-16_16-2-17.png

The Visualization view shows the objects graphically. Solid lines indicate the tables are related through RI (referential integrity) which is maintained in the database.

Do This
  • Right click on any table and select properties to view for that object.
  • Click the Data Privacy tab at the bottom of this section.

image-2023-4-16_16-4-42.png


Do This
  • Click the Browse button beside Project Name.

image-2023-4-16_16-5-56.png


Do This
  • Select the TDPRepos repository from the drop down.
  • Highlight the project TEST DRIVE DISGUISE PROJECT and click OK.

image-2023-4-16_16-6-57.png

worddav115d3fb5ef7c35c3003e09b0e6e90992.png

For distributed databases you have an additional tab for Keys. You do not see this in your current view.

Extracts may be driven by a key file to subset the data and get precise information. Key files may also be created from an extract to drive another extract, possibly in a different database. This functionality will be coming soon for z/OS DB2.


Do This
  • Click the Options tab at the bottom of this section.

image-2023-4-16_16-8-14.png

Extract Options include limits, discovery options, and more advance database options.

Do This
  • To execute this Related Extract click the Blue Arrow Execute button in the top right.

image-2023-4-16_16-9-11.png

 

Do This

Processing details will show up in the Console tab.

  • Double click this tab to maximize the view.
  • Scroll through the report to see the number of rows extracted from each table and the data privacy applied to each eligible column.

image2022-1-10_16-26-53.png


Do This
  •  Scroll down in the Console to see how the data privacy rules were applied to the different columns in the tables.

image2022-1-10_16-28-9.png


Do This
  • Double click on the Console tab to minimize this view.
  • Close the Related Extract job by clicking on the X in the tab.

image-2023-4-16_16-13-58.png

 

Do This
  • Save your changes.

image-2023-4-16_16-14-44.png

You have now successfully created a Related Extract job and extracted rows from multiple tables related through Referential Integrity and applied disguise rules to replace sensitive data in columns containing Names, Phone Numbers, Social Security Numbers, Credit Card Numbers, Dates, and Email addresses.


Application Relationships

Application Relationships are used to related objects when there is no RI maintained within the database or between certain tables. This allows the Related Extract to extract only related rows from these tables in the same manner as it would with RI. These Application Relationships are maintained within the product and make no changes to the database.

Do This

To create an Application Relationship (AR) between tables: 

  • Right click on Application Relationship, and select New.

image-2023-4-16_16-15-42.png


Do This

In this example, the Order table is programmatically related to the Customer table but there is no RI in the database. 

  • Type CUSTORD in the name field. 
  • Give it a description for future reference. 
  • Click Add Objects to select these two tables.

image-2023-4-16_16-17-3.png


Do This
  • Select Db2 z/OS for the Type.
  • Select the DBCC SSID and type in CWEZ### using your Test Drive ID for the Schema name.
  • Click List.
  • Select the CUSTOMER_TABLE and click Add.

image-2023-4-16_16-18-4.png


Do This
  • Highlight the ORDER_TABLE and click Add and Close.

image-2023-4-16_16-18-51.png


Do This
  • Click Finish.

image-2023-4-16_16-19-35.png


Do This
  • Click on the Objects tab along the bottom.

image-2023-4-16_16-20-31.png


Do This
  • Highlight the first table in the list.
  • Use the middle icon top right to Create New Relationship.

image-2023-4-16_16-21-25.png


Do This
  • Type CUSTORD in the Relationship Name field.
  • Use the drop down to select the ORDER_TABLE as the dependent on the right.

image-2023-4-16_16-22-30.png

To create the Application Relationship we will relate the Customer Number column in the Customer table to the Cust Num column in the Order table. 

Do This
  • Click on Customer Number on the left under Customer Table and click on Cust Num on the right under Order Table.
  • Click on the Add Association icon to create relationship.

image-2023-4-16_16-23-30.png

Note: Multiple tables can be related through AR. If there were more tables selected, the Create button would allow additional relationships to be created. Also, tables can be related through more than one column.

Do This
  • Verify that the columns are now listed under Associations at the bottom.
  • Click Create and Close.

image-2023-4-16_16-25-38.png


Do This
  • Click on the Relationships tab along the bottom to see the newly created Relationship.

image-2023-4-16_16-26-28.png

Here you can see the tables related to each other and you can scroll down to see the columns at the bottom that make up this relationship.  Icons on the right allow you to add other objects to this relationship, create a new relationship, or edit or remove this one.  Icons at the bottom right allow adding, editing or removing associations between these two objects.

image-2023-4-16_16-27-44.png



Do This
  • Click on the Visualization tab. 

This shows a graphical representation of the objects and the relationships.

image-2023-4-16_16-29-15.png


Do This
  • Click the X in the tab at the top to close the newly created Application Relationship. 
  • Click save.

image-2023-4-16_16-30-0.png


This Application Relationship can now be incorporated into a Related Extract job. This type of relationship will be treated in the same manner as an RI relationship during the extract process. The parent row extracted will pass along the key to the dependent table based on the column used to relate the tables and any rows there that match will also be extracted.


Selection Criteria

Selection Criteria allows specific data to be extracted based on data values. In this example all rows from the Order table where the Order Type is equal to Purchase will be selected. Then during the extract all related rows will be extracted from the other tables.

Do This
  • In the Data Explorer tab right click on Selection Criteria and select New.

image-2023-4-16_16-31-1.png


Do This

Give the Selection Criteria a meaningful name. 

  • Type "Purchases" in the name field.
  • Click Browse to select a table.

image-2023-4-16_16-31-48.png


Do This
  • Select Db2 z/OS for the Type.
  • Select the DBCC SSID and type in CWEZ### using your Test Drive ID for the Schema name.
  • Click List.
  • Select the ORDER_TABLE and click OK.

image-2023-4-16_16-32-55.png


Do This
  • Click Finish.

image-2023-4-16_16-33-36.png


Do This
  • Click the Condition tab along the bottom of this section.

image-2023-4-16_16-34-11.png


Do This
  • Click on the Add a new condition icon on the top right.

image2022-1-10_16-57-6.png


Do This
  • Click on the <Select field> under the Field Name column.

image2022-1-10_16-58-50.png


Do This
  • Use the dropdown menu and select the ORD_TYPE column.

image2022-1-10_16-59-50.png


Do This
  • Click in the space under Condition Type to activate the dropdown menu. 
  • Select Equal to from the list.

image2022-1-10_17-0-56.png


Do This
  • Click in the space under the Value column and select the box with "…".

image2022-1-10_17-1-42.png


Do This
  • Type PURCHASE for our selection value.
  • Click OK.

image-2023-4-16_16-36-59.png


Do This
  • Click the X in the tab to close the Selection Criteria.

image2022-1-10_17-3-44.png


Do This
  • Click Save to save the Selection Criteria.

image-2023-4-16_16-38-1.png


Execute Extract with AR and Selection Criteria

For this exercise we will open the Related Extract job created earlier. This time we will be adding in additional tables through the use of Applications Relationships. We will also be limiting the number of rows extracted by using Selection Criteria.


Do This
  • In the Data Explorer tab, expand Related Extract.
  • Right click the existing DB2_RELATED_EXTRACT job.
  • Select Open.

image-2023-4-16_16-39-5.png


Do This
  • Check the box to Use application relationships.
  • Click on the Select Relationship button.

image-2023-4-16_16-39-49.png


Do This
  • Select the CUSTORD relationship previously created and click OK.

image-2023-4-16_16-40-31.png


Do This
  • Click the Discover Relationships button to refresh the RI relationships and to now include the additional AR relationships.

image-2023-4-16_16-41-15.png


Do This
  • Click the Objects tab along the bottom to see all objects now included in the Extract.

image-2023-4-16_16-42-3.png

Note you now see the Customer table because of the AR created to the Order table. There is also an additional table, Contact table.

 

Do This
  • Click on the Relationships tab along the bottom.

image-2023-4-16_16-43-21.png

Note the Customer table is related to the Order table with a Relationship Type of AR. Once the Customer table is related, it will include in the extract the Contact table since they are related through RI.


Do This
  • Click on the Visualization tab.

image-2023-4-16_16-45-22.png

 You can graphically understand how the objects are related to each other. Note the dashed line marking the Application Relationship. 


Do This
  • Click again on the Objects tab.
  • Select the Order table and right click.
  • Hover over Selection Criteria and choose Select.

image-2023-4-16_16-46-31.png

For that table you can apply selection criteria, determine process type, and see properties. This can be activated and applied to any table, not just the driving table for more complex criteria to get just the data needed.


Do This
  • Select the Selection Criteria previously created and click OK.

image-2023-4-16_16-47-22.png


Do This
  • Click on the blue arrow icon top right to execute.

image-2023-4-16_16-48-3.png

This will extract all rows from the Order table where Order Type is equal to Purchase. Then it will extract the related rows of data from the other tables related through Referential Integrity, including the Customer table now related through an Application Relationship. Since we had previously selected a Data Privacy Project any sensitive data will also be disguised.


Do This
  • Double click the Console tab to maximize.
  • Check the number of Rows Extracted.

image-2023-4-16_16-49-42.png

Note that we are now only selecting 17 rows from the Order table instead of the 35 rows from the previous execution with no selection criteria.

Listed here are all the data items that were disguised. It shows for each table which columns had a disguise rule applied and how many rows were changed. Scroll through to understand how the rules were applied according to the Source Data Identifiers in the Project.


Do This
  • Scroll down through the report in the Console. 
  • Once done click on the X in the tab to close the Console.

worddav613ce07c9ab48f44225b518e5b879784.png

 

Do This
  • Click on the X in the tab to close the Related Extract job.
  • You may save your changes.

image-2023-4-16_16-50-39.png

You have now successfully extracted related data using Referential Integrity and Application Relationships while applying Selection Criteria to subset that data based on specific data values and Disguised the data so that it is now compliant with existing privacy regulations.  This data is now ready to be loaded into a testing or development environment.

This completes the Related Extract with Disguise section.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*