The template assistant lets you create a new template or use an existing template to migrate data from one database to another.
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.
To use multiple writers for loading data to the IQ database, select Use IQ Multiplex. Select this option to migrate more than one table to the IQ database.
To support multiplex execution, you must install the Sybase SQL Server 11 ODBC driver on the same machine as IQ InfoPrimer Development and IQ InfoPrimer 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 datasource and data sink components that support transactionality are created with their Transactional property enabled.
Click Next.
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.
Click Next.
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.
The same schema or owner must exist in the destination database.
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\.
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.
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.
Source and destination table names are generally the same. To use different names, enter a new name into the Destination field or select an existing 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.
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.
While processing the data, you can view the current state and progress.