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 information, see the Server Administration
for UNIX, Windows, OpenVMS, IBMÂ i, and z/OS manual
or the Web Console online help.
x
Procedure: How to Create Synonyms
-
In the
Data Management Console, right-click an application directory and
select New, and then Synonym.
The Create Synonym dialog box opens as shown in the following
image, showing the adapters that you have configured.
-
Expand
a configured adapter folder, select a connection, and click OK.
The Select Synonym Candidates dialog box opens, as shown
in the following image.
Note: The
contents of this dialog box will depend on the adapter selected.
-
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.
-
Optionally,
restrict candidates based on object type by selecting/deselecting
the corresponding check boxes. The object types will vary depending
on the adapter.
-
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, inserting 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; %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, inserting 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;
%ABC% to select tables or views whose names contain the letters
ABC at the beginning, middle, or end.
-
Click Next.
The Create Synonym dialog box opens, as shown in the following
image, showing all objects that meet the specified criteria.
-
From
the Select Application drop-down list, select
a directory. baseapp is the default value.
-
If you
have tables with identical table names, 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.
If all tables and views have unique names, leave the prefix
and suffix fields blank.
-
To specify
that this synonym should overwrite any earlier synonym with the
same fully qualified name, select the Overwrite existing synonyms check
box.
-
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 are dynamic, it may not be beneficial to choose
this setting.
-
If you
are creating a synonym for a table that has a foreign key to another
table, select the With foreign keys check
box to provide access to both tables in the synonym and Access File.
-
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.
-
To create
a synonym with a different name from a table name, select its corresponding
check box and enter the desired synonym name in the Default Synonym Name
column.
-
Select
the Customize data type mappings check box
to access additional settings, as shown in the following image.
-
From
the Longchar as drop-down menu, you can select:
ALPHA, TEXT, or BLOB. TEXT is the default value. Use ALPHA for DataMigrator.
You can also change the scale and precision settings.
-
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.
-
The Default
Synonym Name column displays the name that will be assigned
to the synonym. To assign a different name, replace the displayed
value.
-
Click Create
Synonym.
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, as shown in the
following image.
x
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. For information on creating
the flow, see How to Use a Relational Stored Procedure as a Data Source.
We
are using Oracle as an example for this procedure.
-
In the
navigation pane, right click a configured relational Oracle connection
and select Create Synonym.
The Select Synonym Candidates (Step 1) dialog box opens,
as shown in the following image.
-
Select
Stored Procedure from the drop-down menu
and the Filter by owner/schema and object name check box,
as shown in the following image. Enter an Owner/schema or Object
name or both and click Next.
The
Select Synonym Candidates (Step 2) dialog box opens.
-
Select
the radio button for a stored procedure as shown in the following image
and click Next.
The
Select Synonym Candidates (Step 3) dialog box opens.
-
Select
check boxes for the parameters and supply their values. Click Create
Synonym.
Note: Values entered are used only when creating
the synonym and are not saved.
A
confirmation message appears.
-
Click Close.
The synonym now appears in the navigation pane listed under
the application directory it was created in, as shown in the following
image.
x
Procedure: How to Test Synonyms
-
In the
Data Management Console, right-click a server name and select Connect.
-
Expand
the Application Directories folder, and expand
the application directory where you created your synonyms.
Note: If the synonyms are not listed, select the
synonyms button from the toolbar above the navigation pane.
-
Right-click
the synonym you wish to test and select Sample Data.
Sample data appears in the workspace, looking similar to the that shown
in the following image:
x
Procedure: How to Create Sample Procedures and Data
Sample procedures and data are needed for Tutorial: Creating Data and Process Flows With DataMigrator and for the exercises in Advanced DataMigrator Tips and Techniques.
-
From
the DMC, right-click Application Directories in
the navigation pane and select Create Samples,
as shown in the following image.
The
Create Sample Procedures and Data dialog box opens, as shown in
the following image.
-
From
the Sample procedures and data for drop-down
menu, choose DataMigrator - General, DataMigrator
- Iterator, DataMigrator - File Listener, DataMigrator
- Star Schema, or Star Schema with Stress
Test Data, as shown in the following image.
Note: The Star
Schema with Stress Test Data option creates Star Schema
tables and generates data for reporting. It allows you to specify
the number of rows generated for each dimension table, a start date,
and the number of rows loaded in the fact table.
-
From
the Select DBMS drop-down menu as shown
in the following image, choose a DBMS type, and click Create.
FOCUS/FDS will be the only choice if you do not have other adapters
configured.
When
the creation process is complete, a confirmation message appears,
as shown in the following image. The message will depend on the
type of samples you create, for example, DataMigrator - General,
DataMigrator - Iterator, DataMigrator - File Listener, DataMigrator
- Star Schema, Star Schema with Stress Test Data.
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 dat.
-
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 Managing
Metadata page of the Web Console, click the Application
Directories link in the navigation pane to see the Configure
Application Path page.
x
Procedure: How to Create Sample Procedures and Data for Star Schema
Star Schema sample procedures are needed
for Loading a Star Schema.
-
From the
DMC, right-click Application Directories in
the navigation pane and select Create Samples,
as shown in the following image.
The
Create Sample Procedures and Data dialog box opens, as shown in
the following image. Your display will depend on the specific adapters
you have configured.
-
Select DataMigrator
- Star Schema from the Sample procedures and data for
drop-down menu.
-
From the
Select DBMS drop down menu, select a relational database that you
have configured.
-
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.
x
Procedure: How to Create Sample Procedures and Data for File Listener
From
the DMC:
-
Right-click on Application Directories in
the navigation pane and select Create Samples.
The Create Sample Procedures and Data dialog box opens.
Your display will depend on the specific adapters you have configured.
-
Select DataMigrator - File Listener from
the Sample procedures and data drop-down menu.
-
From the Select DBMS drop down menu, select a relational database that
you have configured.
-
Click Create to create the sample procedures and
data for file listener.
x
Procedure: How to Create Sample Procedures and Data for Iteration
Additional sample procedures and data are
needed for Iterating Through Multiple Tables. These instructions should be followed
after you have loaded the sample data and procedures for DataMigrator.
-
From the DMC, right-click Application Directories in
the navigation pane and select Create Samples.
The Create Sample Procedures and Data dialog box opens.
Your display will depend on the specific adapters you have configured.
-
Select DataMigrator - Iterator from
the Sample procedures and data drop-down menu.
-
From the Select DBMS drop down menu, select a relational database that
you have configured.
-
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.
-
Load the tables just created. Expand the ibisamp directory.
Find the dmplntpf flow in the list, right-click it and
select Submit.