The template assistant lets you create a new template or use an existing template for migrating data from one database to another.
Select File | New | Template. Alternatively, right-click Templates in the Navigator and select this option.
Enter the migration details:
Provide a name for the template. The name is used for the template object and, further qualified, for the generated transformation objects.
Specify the migration type.
The Migration Type available by default is DB to IQ.
Select the Allow execution on multiple engines option, to use multiple engines for execution.
Select the Use IQ Multiplex option to support multiplex execution by using multiple writers for loading data to IQ. Select this option if more than one table is being migrated to the IQ database.
To support multiplex execution, you must install the
Adaptive Server® Anywhere (ASA) 11 ODBC driver on the same
machine as ETL Development and ETL Server.
Select the Use IQ Client Side Load option to enable loading bulk load data into the IQ database from files located on remote host machines.
Select the Use IQ Lock Table option to lock the target table in Exclusive mode and prevent it from being updated by concurrent transactions. If selected, no other transaction can execute queries or perform any updates against the locked table. The Use IQ Lock Table option also queues multiple projects that load the same table in Sybase IQ.
If you select this option, you must also specify the maximum blocking time that the project should wait, before acquiring the lock.
Click Next.
Enter connection details for the source database and select the tables to transfer. See “Database connection settings”, for information about the database connection parameters to specify.
The database connection properties are the same as for
the DB components.
Click Logon to view the list of available tables for the specified database. By default, each table is selected for transfer. Unselect the tables you do not want to transfer. You can also choose one or more table rows, right-click and select Exclude. To include a table for transfer, right-click and select Transfer.
Alternatively, you can click the:
Exclude all objects from transfer icon to exclude all tables.
Include all objects in transfer icon to include all tables.
To view additional information about a table, choose the table row, right-click and select:
Browse – to view table data.
Count – to view the record count of the selected table. To view the record count for all tables, click Count All.
Click Next.
Enter database connection properties for the destination database. See “Database connection settings”, for more information about database connection parameters you must specify.
Click Logon to view the list of available tables. To view the table data or the record count of the selected table, right-click and select Browse or Count. To view the record count of all tables, click Count All.
Click Next.
Specify transfer settings for tables to be transferred.
Select the Preserve schema/owner option to retain the schema or owner information of the source table.
The same schema or owner must exist in the destination database.
Enter stage properties.
In the Stage and Stage Server fields, specify the path for the load stage properties of the DB Bulk Load IQ component. If the Use Pipes option is selected, paths are set automatically. If Use Pipes is not selected, manually provide the values ended by the path delimiter. For example, C:\ETLStage\.
See to “DB Bulk Load Sybase IQ properties list” for a detailed description of these properties.
The Use Pipes option and the the Stage server field
is disabled if you have selected the Use IQ Client Side Load option
in the migration details window in Step 2.
Select source attributes.
By default, all attributes of a table are selected for transfer. To change the attribute selection, click the icon in the Columns field.
In the Select Attribute window, unselect the attributes to exclude from transfer. You can also select one or more attribute rows, right-click, and choose Exclude to unselect the attributes.
Select destination tables.
It is assumed that source and destination table names are the same. To use different names, enter a new name into the Destination field or select an existing table.
Select the additional options to perform appropriate actions for each table.
Data model options – before the transfer starts, verify that the destination tables exist. The data model options can help you set up the destination data model. They do not affect execution, but affect the data model when it is created from the template.
To create a non-existing destination table based on the selected source attributes, select the Create Table option, or right-click the option and select Activate. To re-create an existing table, select the Drop Table option.
Execution options – these options affect the execution on project level.
Select the Truncate option to remove all records from the destination table before loading. This option corresponds to the Truncate Table property of the target component.
The failure of a Critical project causes the job to stop execution and signal failure. The Critical option and the Ignore Errors option correspond to the properties of the multi-project job component.
The Ignore Errors setting does not affect the projects generated through this template.
Select the tasks you want to perform on the collected data.
Except for saving the template, you can perform all
tasks described here, also by right-clicking a stored template in
the Navigator.
Save template – If you select this option, the template is stored in the repository. Storing allows you to reuse the collected data for similar jobs.
Build projects and jobs – Select this option to create one project for each source table, and a migration job that controls the execution of all the projects.
Create the destination data model – Select this option to set up the destination data model according to the data model options you entered. Click Advanced to enter SQL commands, which are executed before the destination tables are created.
Execute job – The Execute job option is activated only if the Build projects and jobs option is selected. If you select this option, the generated job is executed after migration template data is processed.
Click Finish.
Be sure to select at least the Save template or Build
Projects and jobs options if you do not want the collected data
to be lost.
Before you can execute the generated job, either register
engines or open the job and deactivate Multi Engine Execution option.
See “Using multiple engines to reduce job execution time”.
While processing the data, you can view the current state and progress.
Building a migration template 