The template assistant lets you create a new template or use an existing template to migrate data from one database to another.
Select File | New | Template. Alternatively, right-click Templates in the Navigator and select New | Template.
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.
The migration type should be DB to IQ.
To use multiple engines for execution, select “Allow execution on multiple engines.”
If you want to use multiple writers for loading data to the IQ database, select “Use IQ Multiplex”. Select this option if more than one table is being migrated to the IQ database.
To support multiplex execution, you must install the
Sybase SQL Server 11 ODBC driver on the same machine as ETL Development
and ETL Server.
To enable loading bulk load data into the IQ database from files located on remote host machines, select “Use IQ Client Side Load.”
To lock the target table in Exclusive mode and prevent it from being updated by concurrent transactions, select “Use IQ Lock Table”. If selected, no other transaction can execute queries or perform any updates against the locked table. Use IQ Lock Table 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.
To enable transactionality for the generated job or projects, select Transactional. Data is committed at the end of the write operation for a successful execution, and rolled back for an unsuccessful execution.
If the Transactional option is selected, all the data
source and data sink components that support transactionality, are
created with their Transactional property enabled.
Click Next.
Enter connection details for the source database and select the tables to transfer. See “Database connection settings”.
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 one of these icons:
Exclude all objects from transfer to exclude all tables.
Include all objects in transfer 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”.
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 Preserve schema/owner 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 “Use Pipes” is selected, paths are automatically set. If Use Pipes is not selected, manually provide the values ended by the path delimiter. For example, C:\ETLStage\.
See “DB Bulk Load Sybase IQ properties list”.
The Use Pipes option and the Stage server field are
not available if you selected “Use IQ Client Side Load” 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.
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 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 new destination table based on the selected source attributes, select Create Table, or right-click the option and select Activate. To re-create an existing table, select Drop Table.
Execution options – these options affect the execution on project level.
Select Truncate 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 multiproject 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 alternatively
perform all tasks described here, by right-clicking a stored template
in the Navigator.
Save template – store the template in the repository. Storing allows you to reuse the collected data for similar jobs.
Build projects and jobs – create one project for each source table, and a migration job that controls the execution of all the projects.
Create the destination data model – 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 – available only if Build projects and jobs is selected. If you select this option, the generated job is executed after migration template data is processed.
Select at least the Save template or Build Projects
and jobs options to not lose collected data.
Click Finish.
Before you can execute the generated job, either register
engines or open the job and deactivate the MultiEngine 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 