Creating an Extract Specification
Follow the steps below to create an extract specification to extract the appropriate data from the source selected.
Icons appear on various tabs to indicate the type of resource:
Icon | Description |
|---|---|
| A file |
| A file with unsupported fields |
| A driving object for a file |
| A driving object with unsupported fields for a file |
| A table |
| A table with unsupported fields |
| A driving object for a table |
| A driving object with unsupported fields for a table |
To create an extract specification
- Do one of the following:
- In Data Explorer, navigate to the desired repository, right-click Related Extract, and select New.
From the File menu, select New > Other. The Select a Wizard dialog box appears. Select BMC > DevX
> Related Extract and click Next.
- The New Related Extract Specification dialog box appears.
- In the Name field, enter a name for the extract specification being created.
- Optionally, in the Description field, enter a description for the extract specification being created.
- Optionally, from the Repository list, select a different repository in which to store the extract specification. The Repository field is prepopulated based on the repository from which the new extract specification was initiated.
- In the Driving Object section, click Browse. The Resource selection dialog box appears to allow the user to locate the desired driving object from which to start the extraction process. All relationships for this extract are derived from this object.
- Select one of the following:
- Select the Database radio button to create a
extract specification.
- Select the Db2 for z/OS radio button to create a
extract specification.
- Select the Dataset radio button to create a
extract specification.
- Select the Database radio button to create a
- To select the host, do either of the following:
- In the Host field, select the host connection from the list.
- Click Configure to add a new host connection. The Host Connections page of the Preferences dialog box appears. Complete this dialog box according to the instructions in the Host Explorer Connections topic in the Host Explorer User Guide.
- If the Db2 for z/OS radio button was selected, from the SSID list, select the DB2 subsystem that contains the desired schema and objects.
- If the Database radio button or the Db2 for z/OS radio button was selected, in the Schemas box's Name field, enter a schema name and click List. A list of matching schemas appears. Refer to the Filters topic in the Host Explorer User Guide for information about filtering schema names.
From the resulting list, select the appropriate schema, and then use the Tables box to select or search for the desired object. The search is case-sensitive.
- If the Dataset radio button was selected, do the following to select the desired dataset and a layout for that dataset.
- In the Name field, enter filter text and press Enter or click List to display the datasets matching the filter.
- In the Record Layout Information section, click Browse and navigate to the location of the layout dataset (member) and select it. The Level 01 Name dropdown list is populated with the first 01 level from the layout selected. Click OK.
- Click OK. The selected host and object are added to the Driving object field on the New Related Extract Specification dialog box.
Click Finish. The Extract - Overview tab appears with the above information populated.
- Optionally, for a
extract only, to enter credentials to be used when executing a Related Extract specification, click
. The Execution Credentials dialog box appears. Enter the user ID and password and, optionally, select the Use Trusted Connection check box (for a Microsoft SQL Server or DB2 target database) or Use OS Authentication check box (for an Oracle target database) to use a trusted connection to authenticate the data connection against the requesting user's user ID. Then click OK. These credentials will be required when executing a Related Extract specification. - Optionally, select either or both of the following check boxes to determine how relationships between objects will be discovered during the discovery process:
- Select the Use referential integrity check box to use referential integrity (RI) to discover relationships during the discovery process. RI is based on foreign key to primary key or unique relationships. RI ensures that key values are consistent. The database preserves the defined relationships between objects when records are added or deleted. It prevents users from adding records to a related object when there is no associated record in the primary object, from changing values in a primary object that would result in orphaned records in a related object, and from deleting records from a primary object when there are matching related records. If this check box is not selected, no RI relationships will be discovered; only the driving object will be discovered.
- To choose the application relationship to be used in the specification, do the following:
- Select the Use application relationships check box.
- Click Select Relationships. The Select Application Relationship dialog box appears.
Select from the table the application relationship (AR) specification to use to discover relationships during the discovery process. Application relationships ensure that the key values are consistent.
Click OK. The name of the selected specification appears next to the Select Relationships button.
- Click Discover Relationships to find all of the relationships (dependent and parent objects) for this driving object. The Relationships tab and the Objects tab populate. Depending on the source type, size, and the object hierarchy in the source, discovering all relationships can take a while.
- In the Extract file location field, do one of the following:
- For a
extract, either enter the base directory name or click Browse to navigate to the location in which to store the files created when the extract specification is executed. These files contain the data extracted from each object. In the directory entered, a subdirectory is created under the owner of the Related Extract specification. Within that directory, a directory is created with the specification name.
- For a
extract, either enter the mainframe dataset or click Browse to open the Extract Location dialog box and navigate to mainframe dataset in which to store the extracted data from each object. These files contain the data extracted from each object.
- For a
- For a
extract only, optionally, click Discovery Options to go to the Options tab where more options are available.
Click the Objects tab. The Extract - Objects tab appears. This tab displays the driving object, its parents, and its direct and indirect dependents, and allows the user to choose which objects to include in the extract.
- Optionally, select one or more Extract Scope Navigation Settings check boxes to modify the way relationships are chased in the extract specification:
- Direct: Selecting this check box causes all objects that are direct descendents of the driving object or are related to a direct descendent of the driving object to be chased.
- Parent: Selecting this check box causes all parent objects of objects included in the extract to be chased.
- Sibling: Selecting this check box causes data to be retrieved from parent to dependent where the original path had the dependent chasing the parent. For example, after chasing the related parent rows, the chase goes back to the dependent object to retrieve any "sibling" rows that were not previously extracted. Selecting this check box requires that the Parent check box be selected.
- Indirect: Selecting this check box causes indirect objects to be included in the extract. Indirect objects are objects that are descendents of parent objects that are not already reached as a direct descendent of the driving object. Selecting this check box requires that the Parent check box be selected.
Optionally, enter text into the <type filter text> field to filter the tab to show only the rows that contain the typed text.
- Optionally, from the Filter list, select one of the following to filter the Objects table:
- All - Includes all matching checked, unchecked, or excluded items in the objec
- Included - Includes only the items with the check box selected and that are not grayed out in the objec
- Excluded by User - Shows only the objects that are not selected and are not excluded by scop
- Excluded by Scope - Shows only the unreachable (grayed out) item
- Optionally, click Column Chooser to select which columns appear on the Objects tab.
Select the check boxes for any objects to be included in the extract or select the
check box at the top of the column to include all objects . The following columns provide information about the objects :- ID: A numerical identification of the object
- Type: The database type
- Object name: The object's name
- Selection Criteria: The name of the selection criteria specification assigned to this object
- Message: An area for informational messages, such as "Excluded by Scope" or "Excluded by User"
- Optionally, to set up additional options for processing objects, do any of the following:
- Apply Selection Criteria to an object.
- Select the processing type to be used to extract rows from the objects.
- Satisfy AR/RI in addition to Selection Criteria (for
extracts only).
Click the Relationships tab. The Extract - Relationships tab appears. This tab displays the relationships between the objects selected on the Objects tab to be included in the extract scope.
Optionally, select one or more Extract Scope Navigation Settings check boxes to modify the way relationships are chased in the extract specification (described above).
Select the check boxes for any relationships to be shown on the Relationships tab or select the
check box at the top of the column to include all relationships. Including or excluding a relationship between a parent and its dependent object can, depending on the relationship chase, cause other relationships and objects to become unreachable. The following columns provide information about the relationships:- ID: A numerical identification of the object
- Parent Type: The parent database type
- Parent Name: The name of the parent object
- Dependent Type: The dependent type
- Dependent Name: The name of the dependent object
- Relationship Type: Indicates whether the relationship is referential integrity or application relationship
- Message: An area for informational messages, such as "Excluded by Scope" or "Excluded by User"
Click the Visualization tab. The visualization diagram allows users to observe the objects and relationships that can be included in the extract specification.
- Click the Data Privacy tab. The Extract - Data Privacy tab appears. This tab allows users to choose the Data Privacy project (created in the File-AID Data Privacy plug-in for ) that contains the dynamic privacy rules to be used in this specification. It also lets users select an execution parameters override file that overrides variable values and translation object names (
extracts only); enable/disable application of dynamic privacy rules when the specification is run; set logging options for privatization (
extracts only), retain disguised extract files (
extracts only), and enter a prefix for audit file names (
extracts only).
Next to the Project name field, click Browse to navigate to the Data Privacy project that contains the dynamic privacy rules to be included in the specification. The Select Dynamic Privacy Project dialog box appears. From the Repository list, select the repository that contains the project. The box populates with the projects in that repository. To filter the list of projects, enter the filter text in the type filter text field. Select the desired project and click OK. The project information is added to the Extract Data Privacy Options section of the Extract - Data Privacy tab.
- Optionally, for
extracts only, select the Retain pre-disguised files check box to retain the file that are created when the
Engine first extracts data without data privacy applied. If this check box is not selected, these files will be deleted.
For
extracts only, to override variables, a translate table name, or a list variables, next to the Execution Parameters Overrides File field, click Browse. The Select Execution Parameter File dialog box appears. Navigate to the overrides file and click OK. The path and file name of the override file is added to the Extract Data Privacy Options section of the Extract - Data Privacy tab.
- Optionally, select the Disable Rules at Execution check box to prevent the dynamic privacy rules from running when the specification is run. By default, the rules will run.
- Use the Audit Options section of the Extract - Data Privacy tab to set logging options for the data privacy rule:
- Select the Enable Auditing check box to create an audit log, or clear the check box to prevent the log from being created. When a log is created, it contains the name of the object, which fields have data privacy applied, and the number of records audited.
- For
extracts only, select the Log Original Data check box to write values to the log file before disguising them, or clear the check box to not log the original data to the log file. This check box is disabled until the Enable Auditing check box is selected.
- For
extracts only, in the Max Records to Audit field, enter the total number of records to audit. This check box is disabled until the Enable Auditing check box is selected.
- For
extracts only, in the Audit Every Nth Record field, enter the sampling interval to be used, such as every 3rd record. This check box is disabled until the Enable Auditing check box is selected. By default, every record will be audited.
- For
extracts only, optionally, in the Audit File Prefix field, specify the prefix for the audit trail datasets created when the specification is executed. If no prefix is entered, the USERID will be used as the prefix.
- For Standalone Execution Servers running on Windows: Audit logs are written to \ProgramData\Compuware\FAEX\PrivacyLogs.
- For Local Execution Servers running on Windows: Audit logs are written to <Topaz_workspace_dir>\.com.compuware.fileaid.ex\PrivacyLogs.
- For Execution Servers running on Unix/Linux: Audit logs are written to a PrivacyLogs subfolder under the execution server's installation location.
- For a
extract only, click the Keys tab. The Extract - Keys tab appears. This tab is used to add, edit, remove, include, exclude, and define the layout for key files, and to map the key columns.
- Do any of the following:
- Add a key fil
- Remove a key fil
- Exclude a key fil
- Define the layout for a key fil
Map key column
- Click the Options tab. The Extract - Options tab appears. This tab allows users to set additional options for the extract specification.
- In the Maximum Rows to Extract field, enter the total number of rows the specification should extract. Valid values are blank (<No Limit>, which is the default and extracts all rows) or an integer (from 1 to 2,147,483,647). The time it takes the extract to complete is directly affected by this number. The maximum rows to extract is not a hard limit. However, when the limit is reached, the extraction process starts to resolve all orphan child records, such that their parent records are also extracted. This may result in more records than the specified maximum count, but the integrity of the data extracted is preserved.
- When you select Generate Delete Statements, a file is created and is stored at the location that you mentioned in Extract file location. This file lists SQL statements relevant to extracted records and their relationships. These are the records that are extracted during the execution of related extract specification. A database administrator can use these SQL statements to write a script to delete extracted records.
- For
extracts only, in the Number of Cycles to Extract field, enter the number of extract cycles after which the extract process should stop. Leave the field blank for no limit.
- For
extracts only, use the Discovery Options section to select what schemas to include in the extract specification. For any schemas included in the extract specification, if the schema of an object on the DBMS is one of the selected IDs and the object is related directly or indirectly to the driving object, the object will be included in the extract specification.
- Select Include all schemas to include all available schemas in the extract specification.
- Select Limit discovery to these schemas to choose specific schemas to include in the extract specification. Click Add to open the Select Schemas dialog box, which lists the available schemas in the database of the driving object. Select the check boxes for any schemas to be included and clear the check box for schemas to be excluded, then click OK. The schemas to be included are added to the Discovery Options box on the Extract - Options tab. To remove schemas, select each and click Remove.
- For
extracts only, use the Advanced Options section to configure the Related Extract engine for Microsoft SQL Server and Oracle databases if one is being used.
41. Select the Use DBMS for faster extraction check box to use the Database Server-Driven Engine. Alternatively, clear this check box to use the Execution Server-Driven Engine. When the check box is cleared, the remaining options are disabled. Refer to Related-Extract-Engines for information on the Database Server-Driven Engine and the Execution Server-Driven Engine.
42. Under Data Preparation Threads, select the appropriate radio button to specify the number of data preparation tasks that can be executed in parallel:
- Select Auto to allow the execution server to determine the ideal number of threads. This option provides acceptable behavior for most extracts that do not involve extensive data or relationships.
- Select Server Setting to direct the execution server to use the value for Extract_Max_Db_Threads in the engine.properties file (located by default at \ProgramData\Compuware\FAEX\Cfg. Refer to for guidance
- Select Custom and enter the number of data preparations tasks to allow to be executed in parallel in the field. Enter an asterisk to allow unlimited tasks. Refer to for guidance If the same extract specification will be executed repeatedly, experiment with the values and then use the Custom setting to specify the best value found.
43. Under Data Processing Threads, executed in parallel:
- SelectAuto to allow the execution server to determine the ideal number of threads. This option provides acceptable behavior for most extracts that do not involve extensive data or relationships.
- Select Server Setting to direct the execution server to use the value for Extract_Max_Threads in the engine.properties file (located by default at \ProgramData\Compuware\FAEX\Cfg. Refer to for guidance
- Select Custom and enter the number of data processing tasks to allow to be executed in parallel in the field. Enter an asterisk to allow unlimited tasks. Refer to for guidance If the same extract specification will be executed repeatedly, experiment with the values and then use the Custom setting to specify the best value found.
- In the Database/Tablespace to use for temporary tables field, enter the name of the tablespace (for Oracle) or database (for SQL Server) to use for temporary tables created as part of the execution of data preparation task By default, the tablespace/database that is used will be the same as the tablespace/database being extracted against. Leave this field empty for the default behavior.
- From the Select the schema to use for temporary tables drop-down list, select where any temporary tables should be created as part of the execution of data preparation task By default, the schema that owns the selected driving table hosts these tables. The selected schema should give the user running the extract permissions to create and drop tables and should provide sufficient space for the extract process. BMC recommends allocating space that is at least twice the total size of all tables participating in the extract, or multiple extracts that may be simultaneously executed against same database
- If you selected either Server Setting radio button, edit the enginproperties file accordingly.
44. For
extracts only, use the Db2 zOS Options section to configure
execution options.
45. Next to Capture DDL, select one or more check boxes:
- Select Views to specify that the DDL for DB2 View objects should be saved in the Extract file in order for
to create DB2 views for the Load process.
- Select Aliases to specify that the DDL for DB2 Alias objects should be saved in the Extract file in order for
to create DB2 Alias objects for the Load process.
- Select Synonyms to specify that the DDL for DB2 Synonym objects should be saved in the Extract file in order for
to create DB2 Synonym objects for the Load process.
- Select or clear the Convert Unicode data to EBCDIC check box. Select the check box to indicate that DB2 data extracted from objects with Unicode encoding should be written to the extract file in EBCDIC format. Clear the check box to indicate that the DB2 data should be written to the extract file in Unicode format. Selecting this check box will the maintain EBCDIC encoding utilizing IBM z/OS Conversion Services.
- Select or clear the Use Uncommitted Reads check box. Select it to specify that
should extract data from Db2 objects that include uncommitted reads. Clear the check box to not extract this data. Whether the value entered here is used in the extract screens depends on the setting of the UNCOMMITTED_READ_DEFAULT parameter of the Db2 Environment PARMLIB member. Refer to UNCOMMITTED_READ_DEFAULT in the File-AID Advanced Configuration Guide for more information.
- Select or clear the Extract Hidden Columns check box. Select it to specify that
should extract data from Db2 columns that are defined as IMPLICITLY HIDDEN. Clear the check box to not extract this data.
- Do one of the following:
- Click
. - From the File menu. select Save.
- Click
- Select Views to specify that the DDL for DB2 View objects should be saved in the Extract file in order for
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*