DataMigrator/Reporting Server > Setting Up DataMigrator > Creating Metadata

Creating Metadata

How to:

Synonyms constitute the metadata that DataMigrator requires to define unique names (or aliases) for each table or view that is accessible from the server. A synonym must be created for every table that DataMigrator accesses.

Once you have created the metadata (synonym), it is a good idea to test it to make sure that the data is available for DM processing.

You can also create and test metadata from the Managing Metadata page of the Web Console. For more information, see the Server Administration manual or the Web Console online help.

Procedure: How to Create Synonyms for Database Tables

  1. In the Data Management Console, right-click an application directory, point to New, and then click Synonym (Create or Update).

    The Select adapter to configureconnection dialog box opens, showing the adapters that you have configured.

  2. Expand a configured adapter folder, select a connection, and click OK.

    The Select Synonym CandidatesAvailable Objects dialog box opens, as shown in the following image.

    Select Synonym Candidates dialog box

    Note: The contents of this dialog box will depend on the adapter selected.

  3. To use the database specified when the connection was added, leave Default Database as is, or select a different database from the drop-down menu. This option is only available for Informix, Microsoft SQL Server, and Sybase ASE adapters.
  4. Optionally, restrict candidates based on object type by selecting or clearing the corresponding check boxes. The object types will vary depending on the adapter.
  5. Optionally, filter the possible candidates by name and owner. Selecting this option adds Owner and Table Name parameters to the dialog box.
    • For the Owner parameter, type a string for filtering the owner IDs and insert the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter ABC% to select tables or views whose owner IDs begin with the letters ABC, %ABC to select tables or views whose owner IDs end with the letters ABC, or %ABC% to select tables or views whose owner IDs contain the letters ABC at the beginning, middle, or end.
    • For the Table Name parameter, type a string for filtering the table or view names and insert the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter ABC% to select all tables or views whose names begin with the letters ABC, %ABC to select tables or views whose names end with the letters ABC, or %ABC% to select tables or views whose names contain the letters ABC at the beginning, middle, or end.
  6. Click Next.

    The Create Synonym dialog box opens, as shown in the following image, showing all objects that meet the specified criteria.

    Create Synonym dialog box
  7. If you have tables with identical table names, you can assign a prefix or a suffix to distinguish them. For example, if you have identically named human resources and payroll tables (most likely with different owners), assign the prefix HR to distinguish the synonyms for the human resources tables. Note that the resulting synonym name cannot exceed 64 characters. Alternately, you can specify different application directories for different schemas or owners.

    When synonyms already exist, you can select the Update or SelectUpdate Base Synonyms option. If you select UpdateUpdate Base Synonyms from the Update or Create MetadataAction drop-down menu, you have the option of selecting which attributes to update. For more information, see How to Update a Synonym.

  8. To specify that this synonym should overwrite any earlier synonym with the same fully qualified name, select the Overwrite existing synonyms check box.
  9. To reflect the current cardinality (number of rows or tuples) in the table during metadata creation, select the Cardinality check box. The use of cardinality is for equi-joins. The order of retrieval is based on the size (cardinality) of the table. Smaller tables are read first.

    Note: If the cardinality of the tables to be used in the application is dynamic, it may not be beneficial to choose this setting.

  10. To specify that the synonym should not contain column information, select the Dynamic columns check box.

    If this option is selected, column data is retrieved dynamically from the data source at the time of the request.

  11. ExpandSelect the Customize data type mappings sectioncheckbox to access additional settings, as shown in the following image.
    Customize data type mappings
  12. Optionally, change the scale and precision settings.
  13. From the Decompose Date fields into components drop-down menu, select OFF - Turn off This option must be off when using DataMigrator.
  14. Complete your table or view selection:

    To select all tables or views in the list, select the check box to the left of the Default Synonym Name column heading.

    To select specific tables or views, select the corresponding check boxes.

  15. The Default Synonym Name column displays the name that will be assigned to the synonym. To assign a different name, replace the displayed value.
  16. Click NextCreate Base Synonyms.

    Synonyms are created and added under the specified application directory.

    The status field of the Create Synonym window shows that the synonyms were created successfully.

  17. Click Close.

Procedure: How to Create a Synonym for a Relational Stored Procedure

If you create a synonym for a relational stored procedure, you can use that stored procedure as a data source in a flow. When you create the synonym, you will be prompted for values for the parameters of the procedure.

We are using Oracle as an example for this procedure.

  1. In the navigation pane, right-click a configured relational Oracle connection and click Create or Update Synonym.

    The Select Synonym Candidates (Step 1) dialog box opens.

  2. Select Stored Procedure from the drop-down menu and the Filter by owner/schema and object name check box. Enter an Owner/schema or Object name or both and click Next.

    The Select Synonym Candidates (Step 2) dialog box opens.

  3. Select the radio button for a stored procedure and click Next.

    The Select Synonym Candidates (Step 3) dialog box opens.

  4. Select check boxes for the parameters and supply a value for testing. Click Create SynonymCreate Base Synonyms.

    Note: Values entered are used only when creating the synonym and are not saved.

    A confirmation message appears.

  5. Click Close.

    The synonym now appears in the navigation pane listed under the application directory it was created in.

Procedure: How to Update a Synonym

To update a synonym from the Select Synonym CandidatesAvailable Objects dialog box:

  1. Select UpdateUpdate Base Synonyms from the Update or Create MetadataAction drop-down menu.
  2. Select a synonym from the list and click NextUpdate Base Synonyms.

    The Update Synonyms dialog box opens, as shown in the following image.

    Update Synonyms dialog box
  3. Select the attributes that you want to override those from the existing synonym and click Next.

    The selected synonym will now be updated

Procedure: How to Test Synonyms for Tables or Files

  1. In the Data Management Console, right-click a server name and click Connect.
  2. Expand the Application Directories folder, and expand the application directory where you created your synonyms.

    Note: If the synonyms are not listed, on the Home tab, from the Filter group, click Synonyms.

  3. Right-click the synonym you wish to test and click Sample Data. Sample data appears in the workspace, and looks similar to the following image.
    Sample data

Procedure: How to Create Sample Procedures and Data

  1. From the DMC, right-click Application Directories in the navigation pane, point to New, and click Tutorials.

    The Create Tutorial Framework dialog box opens, as shown in the following image.

    Create Tutorial Framework dialog box
  2. From the Tutorial drop-down menu, select one of the following options:
    • WebFOCUS - Retail Demo
    • WebFOCUS - State Population Demo
    • WebFOCUS - Custom SQL Security Provider
    • DataMigrator - General
    • DataMigrator - Iterator
    • DataMigrator - File Listener
    • DataMigrator - Star Schema
    • Create Legacy Sample Tables and Files

    Note: The WebFOCUS and Legacy sample tables and files are not used with DataMigrator.

    The Retail Demo option loads tables with a pre-determined amount of data designed for WebFOCUS reports.

  3. From the Select DBMS drop-down menu, choose a DBMS type, and click Create. DATREC, a binary file format, will be the only choice if you do not have other adapters configured.

    For the Prefix for SQL Tables field, use the default value unless you have a reason to avoid tables starting with the indicated default. If so, type a different value.

    When the creation process is complete, a confirmation message appears. The message will depend on the type of samples you create, for example, DataMigrator - General, DataMigrator - Iterator, DataMigrator - File Listener, or DataMigrator - Star Schema.

    The message also displays in the Console Log.

    The procedures and synonyms appear in the ibisamp application directory, which is created and added to the server search path during installation. For FOCUS, the sample data is also stored in ibisamp. For other adapters, it is stored in the database of the configured adapter. The source data for the file listener flow is stored in ibisamp in four files with names starting with dmord and an extension of data.

  4. Ensure that the ibisamp application directory is in the server path. It will be in the path unless you delete it.
    • In the DMC, examine the navigation tree in the server you are using.
    • On the Applications tab of the Web Console, click the Application Directories link in the navigation pane to see the application path. For more information, see How to Configure the Application Path.

Procedure: How to Create Column Names in Lower Case

Note: By default, column names are created in the database with UPPER CASE column names. This is problematic for databases, such as PostgreSQL and those based on it such as Greenplum, where column names are expected to be in lower case.

  1. In the DMC, expand the Adapters folder, followed by the Configured Adapters folder.
  2. Right-click the folder name of the adapter you wish to configure and click Change Settings.

    The Change Settings dialog box opens.

  3. In the Metadata section, from the ALIAS_CASE drop-down menu, select LOWER - Enforce lower case.
  4. Click Save.

This setting is also used by DataMigrator when creating new tables.

Procedure: How to Create Sample Procedures and Data for Star Schema

  1. From the DMC, right-click Application Directories in the navigation pane, point to New, and then click Tutorials.

    The Create Tutorial Framework dialog box opens. Your display will depend on the specific adapters you have configured.

  2. Select DataMigrator - Star Schema from the Tutorial drop-down menu.
  3. From the Select DBMS drop-down menu, select a relational database that you have configured.

    For the Prefix for SQL Tables field, use the default value unless you have a reason to avoid tables starting with the indicated default. If so, type a different value.

  4. Click Create. The following status message should appear:

    Star Schema sample procedures created for database

    where:

    database

    indicates the database type you have selected.

The procedures and synonyms appear in the ibisamp application directory, which is created and added to the server search path during installation.

Procedure: How to Create Sample Procedures and Data for File Listener

From the DMC:

  1. Right-click Application Directories in the navigation pane, select New, and then click Tutorials.

    The Create Tutorial Framework dialog box opens. Your display will depend on the specific adapters you have configured.

  2. Select DataMigrator - File Listener from the Tutorial drop-down menu.
  3. From the Select DBMS drop-down menu, select a relational database that you have configured.

    For the Prefix for SQL Tables field, use the default value unless you have a reason to avoid tables starting with the indicated default. If so, type a different value.

  4. Click Create to create the sample procedures and data for file listener.

Procedure: How to Create Sample Procedures and Data for Iteration

  1. From the DMC, right-click Application Directories in the navigation pane, point to New, and then click Tutorials.

    The Create Tutorial Framework dialog box opens. Your display will depend on the specific adapters you have configured.

  2. Select DataMigrator - Iterator from the Tutorial drop-down menu.
  3. From the Select DBMS drop-down menu, select a relational database that you have configured.

    For the Prefix for SQL Tables field, use the default value unless you have a reason to avoid tables starting with the indicated default. If so, type a different value.

  4. Click Create. The following status message should appear:

    Sample procedures created for database

    where

    database

    Indicates the database type you have selected.

    The procedures and synonyms appear in the ibisamp application directory, which is created and added to the server search path during installation.

  5. Load the created tables and expand the ibisamp directory.

    Right-click the dmplntpf flow and click Submit.

WebFOCUS

Feedback