Creating a Loader Specification

Follow the steps below to create a Related Loader specification to load related objects in DB2 UDB, Microsoft SQL Server, Oracle, and Sybase databases.

1. Do one of the following:

• In File-AID Explorer, navigate to the desired repository, right-click Related Loader, and select New.
• From the File menu, select New>Other. The Select a Wizard dialog box appears. Select Compuware>Topaz for Enterprise Data>Related Loader and click Next.
• The New Related Loader Specification dialog box appears.

2. In the Name field, enter a name for the load specification being created.
3. Optionally, in the Description field, enter a description for the load specification being created.
4. Optionally, from the Repository list, select a different repository in which to store the load specification. The Repository field is prepopulated based on the repository from which the new load specification was initiated.
5. In the Load Target section, click Browse. The Target Selection dialog box appears to allow the user to select the target database to load the extracted data.
6. Select one of the following:

• Select the Database radio button to create a

  File-AID/EX

  load specification.
• Select the z/OS radio button to create a

  File-AID/RDX

  load specification.

7. To select the host, do either of the following:

• In the Host field, select the database where the data will be loaded.
• 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.

8. Optionally, if the z/OS radio button was selected, from the SSID list, select the DB2 subsystem of the desired target host.
9. Click OK. The load target is added to the Load Target field on the New Related Loader Specification dialog box.
10. Click Finish.

The Loader - Overview tab appears with the above information populated.

11. In the Extracted Data section, click Browse. The Extracted Data Selection dialog box appears to allow the user to select the type of extract and choose a specification/file.
12. Select the type of extract. The source contains the data and layout information about the source objects. Related Loader uses the contents of the source to generate a list of target objects, column mapping information, and DDLs to create target objects if they do not already exist.

• To select an extract specification from the repository where the load specification is being created, select File-AID/EX Repository Extract. A list of extract specifications appears. (For a

  File-AID/RDX

  load specification, only

  File-AID/RDX

  extracts saved in the

  File-AID/EX

  repository appear.) If desired, type filter text in the type filter text field to filter the list of extract specifications. Select the desired extract specification and click OK. The specification is added to the Overview tab.
• To create a load specification from an extract data file, which contains data from objects and data structures, select File-AID/EX Extract File. An extract data file is created automatically when an extract specification is executed. In the Extract File section, either type the path and name of the extract specification or click Browse to navigate to the extract directory and select the XML file. Click OK. The specification is added to the Overview tab. This option is not available when creating a

  File-AID/RDX

  load specification.
• To create a load specification from a

  File-AID/RDX

  extract, which is a file extracted from MVS and/or DB2 UDB data using the Compuware product

  File-AID/RDX

  , select File-AID/RDX Extract. In the Name field, enter the name of the

  File-AID/RDX

  extract file. In the File-AID Executive section, enter information about the Executive; this section is not available when creating a

  File-AID/RDX

  load specification.. Alternatively, from the Host list, select the host, then enter in the Name field the filter for the extract dataset. Click List and select the desired dataset from the list. Click OK. The specification is added to the Overview tab. 

13. Optionally, to enter credentials to be used when executing a

File-AID/EX

Related Loader 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 Loader specification.
14. Optionally, from the Host list, select the database where the data will be loaded and click OK. Login credentials will be requested if the user is not logged onto the host. The target database and host information is added to the specification and can be viewed on the Loader - Overview tab.
15. Click the Objects tab. The tab appears. This tab displays information about the source and target objects, and allows the user to choose which objects to include in the extract.
16. Select the check boxes for any objects to be included in the load 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
• Target Type
• Target Name
• Source Type

• Source Name

  Important

  In the Target Name column and a message in the Message column indicate that the target object does not exist. Additionally, clicking validates target object existence.

  Important

  To change the target table name, right-click the object and select Change Target Object Name. Depending on the object type, either the Change Table Name or the Change File Name dialog box appears. Enter the new name and click OK.

17. To select the action to be taken on each target, in the Existing Target Action column, right-click an object and select the action (available actions vary by object type and objects can be multi-selected for the same action):

• None: Takes no action.
• Recreate: Recreates the target dataset.
• Clear: Delete the object's existing data before loading the target object. All of the rows in the object will be deleted.
  
  

18. Optionally, select the Replace check box for any objects to replace their duplicate keys during load, or select the check box at the top of the column to do so for all objects. The column displays N/A for objects for which the Replace option does not apply.
19. Click the Field Mappings tab. The Loader - Field Mappings tab appears. This tab displays one source and target object combination at a time and allows the user to modify the field mappings between the source object and the target object.
20. From the Object list, select the target object. The source object fields that are mapped to the selected target object appear in the right pane alongside the target object field, and the list of all source object fields appears in the left pane.
21. Do any of the following to modify the field mappings between the source object and the target object:

• To map a source field from the Metadata tab to a target field, drag it to the appropriate row in the Target Mappings pane or, for a

  File-AID/EX

  load only, to the Expression pane. indicates a mapping that does not include an expression.
• To clear a mapping, in the Target Mappings pane, right-click the mapping and select Clear Mapping or click . Mappings can also be multi-selected and then cleared.

• To reset all mappings to the AutoMap state or to the last saved state, click .
• For a

  File-AID/EX

  load only, to create an expression, use the Expression tab in the left pane, which lists available string functions, date functions, and operators:
• From the Target Mappings pane, select the target field, then drag and drop functions and operators from the Expression tab to the Expression pane. The changes are reflected in the Target Mappings pane. indicates a mapping that includes an expression
• Hover over a function in the left pane to display the definition and syntax for the function.

• Select a row in the Target Mappings pane. The related expression appears in the Expression tab and can be edited. 

  Important

  The Expression tab can be filtered by entering the filter text in the type filter text field. 

  Important

  Red squiggly lines in the Expression pane indicate an expression that is not syntactically correct.

  Important

  To reset all mappings to the default mapping (with no expression), click.

22. Click Save.

23. Click the DDL tab. The Loader - DDL tab appears. This tab allows users to build the appropriate DDL for creating a new target object prior to loading it. DDL stands for Data Definition Language and is used to define the layout/structure of a target object including the column names, types, sizes, and so on.

24. From the Object list, select the target object. The left pane populates with the layout of the target object, including the database schema, the object name, and the ordered list of fields with their types. The right panes, named Create Actions and Post-Create Actions, are editor panes that populate with automatically generated DDL statements. The Create Actions pane typically contains DDL statements that are not dependent on other objects created by the same specification, such as CREATE TABLE or CREATE INDEX statements. The Post-Create Actions pane contains statements that require other objects to already be created before those statements can be executed. A typical example is an ALTER TABLE statement that creates a FOREIGN KEY constraint that references a different object from the same specification.
25. If desired, edit the DDL in the Create Actions and Post-Create Actions panes to reflect how the target object should be created when it doesn't exist. When the specification is executed, the target objects that don't exist are created by executing the provided DDL. When a Loader specification is created, the DDL statements in both editor panes are automatically generated and populated based on either the layout of the existing target object or the layout of the corresponding source object. An advanced user can modify the generated DDL to customize the result of Related Loader execution. For example, additional indexes or constraints can be added to the target objects.

26. Optionally, to export DDLs contained in the specification to a single file, click . The Select DDL Export File dialog box appears. Select or enter a file into which to export the DDLs to and click Save. The DDLs are exported to the specified file name and location.

27. For a File-AID/RDX load only, click the Datasets tab. The Loader - Target Datasets tab appears. This tab allows users to edit the properties of target datasets, which will be used to create datasets. Users can rename any dataset that is part of the target definition, change allocation properties, and change SMS class parameters.
28. In the left pane, select the target dataset for which to edit properties. The Dataset Attributes pane appears, listing properties of the chosen dataset. The list of properties varies by the type of dataset.
29. If desired, edit the properties of the target dataset.

30. Click the Options tab. The Loader - Options tab appears. This tab allows users to specify optional settings that can be used for performance optimization, error handling, and adjusting specific database platform features.
31. Under General Options, if the target database is Oracle or DB2, select a load method. These options are not available for other target database types.

• To use the

  File-AID/EX

  Engine to load data, select SQL Insert. (for File-AID/EX) or Insert (for File-AID/RDX).
• To generate a control file and use the database load utility to load data, select Bulk Load (for File-AID/EX) or Db2 Load Utility (for File-AID/RDX).

32. Select a commit size:

• To allow the

  File-AID/EX

  Execution Server or RDX Engine to decide how best to commit records, select Auto.
• To commit records to the database in batches of the specified size, select Custom and enter a value.

33. For a

File-AID/EX

load only, select the method to clear rows in a table:

• To issue a delete SQL command to delete all records from a table, select Delete.

• To issue a truncate table statement to delete all records, select Truncate. 

  Important

  Truncate, while faster to execute, has database-specific restrictions. In such a scenario, the Delete option is exercised as the fallback option.

34. For a

File-AID/EX

load only, do one of the following to specify when Related Loader should do alters to create foreign keys on new tables:

• To create foreign key constraints before data is loaded to the table, select the Create Constraints Before Loading check box.

• To postpone creation of foreign key constraints until all data is loaded to the table, clear the check box.

  Important

  This check box has no effect when loading into existing tables.

35. Optionally, to define how to handle to a file any rows that cannot be inserted or updated (such as duplicate records), do the following (does not apply to

File-AID/RDX

MVS datasets):

• In the Discard Threshold field, enter the maximum number of rows to be saved to the file. When the threshold has been met, execution of the load specification terminates with an error.
• Indicate whether to save discarded rows by making the appropriate selection for Save Discards.

• In the Discarded Rows Directory field (for

  File-AID/EX

  ) or the Discarded Rows File field (for

  File-AID/RDX

  ), enter the directory/file in which to discard rows. For a

  File-AID/EX

  Load specification, this field is required. For a

  File-AID/RDX

  Load specification, the path is identified in the

  File-AID/RDX

  settings and defaults to <user ID>.DISCARD<date/timestamp> but can be changed. The exact path can be determined from the JCL.

  Important

  By default, the discarded rows are not saved to a file for relational databases, but they are saved to the default directory for

  File-AID/RDX

  Load specifications.

  Important

  For File-AID/RDX MVS datasets, discard options apply only to KSDS datasets. For

  File-AID/RDX

  tables and MVS datasets for which the Load Method is set to Insert, discard options apply to tables and KSDS datasets. For

  File-AID/RDX

  tables and MVS datasets for which the Load Method is set to DB2 Load Utility, discard options apply only to KSDS datasets. For

  File-AID/RDX

  tables for which the Load Method is set to DB2 Load Utility, discard options are disabled.

36. For a

File-AID/EX

load only, select or clear the database-specific check boxes:

• For SQL Server or Sybase, select the Identity Insert On check box to load the identity fields values from the extracted data. Clear this check box to allow the system to generate the identity fields values.
• For Oracle and DB2, select the Lock Tables During Load to lock the specification's tables during the load. This prevents other users and applications from accessing the tables. Use of this option can significantly improve performance of the load process. If the tables being loaded need to be accessible during the load, clear this check box.

37. If Bulk Load was selected (to generate a control file and use the database load utility to load data), do the following:

• For a DB2 database (

  File-AID/EX

  load only):
  • Click Browse next to the Temporary Directory field and navigate to the local directory to which Related Loader will write temporary files during load processing.
• For an Oracle database (

  File-AID/EX

  load only):
  • Click Browse next to the Temporary Directory field and navigate to the local directory to which Related Loader will write temporary files during load processing.
  • In the Error Threshold field, enter the number of errors to be allowed before the load is stopped. To stop a load on the first error, enter 0.
  • Select the Direct Path Load check box to specify that Oracle use the direct path to create data blocks in Oracle database block format and write these blocks directly to the database. Clear the check box to specify that Oracle use the conventional path to use a find array and the SQL INSERT command to load tables.
• For a

  File-AID/RDX

  load only, if Bulk Load was chosen as the method to load data, select whether to enable logging:
  • To enable logging, select Yes.
  • To disable logging, select No and select one of the following:
    • Image Copy: DB2 logging of data will not occur during the load process. DB2 sets a COPY PENDING restriction against the tablespace being loaded. An IMAGECOPY step is generated to remove the COPY PENDING status.
    • Inline Image Copy: (DB2 Release 5.1 and higher only) If the check box was selected to delete existing object data before loading the target object, this option invokes inline image copy. DB2 logging of data will not occur during the load process. The tablespace into which the data is being loaded will not be placed into a copy pending state. If there are multiple objects in a tablespace, inline image copy is not used; instead an IMAGECOPY step is generated to remove the COPY PENDING status.
    • Repair: DB2 logging of data will not occur during the load process. DB2 sets a COPY PENDING restriction against the tablespace being loaded. A REPAIR step is generated to set NOCOPYPEND for the tablespace(s). The execute this utility, the privilege set of the process must include one of the following:
      • REPAIR privilege for the database
      • DNADM or DBCTRL authority for the database
      • SYSCTRL or SYSADM authority

38. Optionally, change the schema for one or more target tables in the load specification.