Manually Creating a Target Definition
You can manually create a target definition rather than importing it or creating it from a source definition. To manually create a target definition:
1. In the Target Designer, click Targets > Create.
2. Enter a name for the target and select the target type. If you create a relational definition, follow database-specific naming conventions.
3. Click Create.
An empty definition appears in the work space. It may be covered by the dialog box. The new target definition also appears within the Navigator window.
4. If you want to create another target definition, enter a new target name and target type and click Create. Repeat this step for each target you want to create.
5. Click Done when you finish creating target definitions.
6. Configure the target definition.
The new target definition is saved to the repository. You can now use the target definition in a mapping. You can also create a target table in the target database based on relational target definitions.
Note: You cannot manually create a target definition for XML files.
Maintaining Relational Target Definitions:
You can maintain relational target definitions in the following ways:
* Reimport the target definition. Reimport a target definition rather than edit it if the target changes significantly.
* Define primary key-foreign key relationships. Define primary key-foreign key relationships between relational target tables.
* Edit the target definition. Edit a target definition to add comments or key relationships, or update it to reflect a changed target.
After you create a relational target definition, you can edit it using the following target definition tabs:
* Table tab. Edit properties such as constraints for relational targets and flat file properties for flat file targets.
* Columns tab. Edit column information such as datatype and precision.
* Indexes tab. Add index information for relational target definitions.
* Metadata Extensions tab. Extend the metadata stored in the repository by associating information with repository objects, such as target definitions.
When you change a target definition, the Designer propagates the changes to any mapping using that target. Some changes to target definitions can invalidate mappings.
When you add a new column to a target in the Target Designer, all mappings using the target definition remain valid. However, when you add a new column and change some of its properties, the Designer invalidates
mappings using the target definition.
You can change the following properties for a newly added target column without invalidating a mapping:
If the changes invalidate the mapping, validate the mapping and any session using the mapping. You can validate objects from the Query Results or View Dependencies window or from the Repository Navigator. You can validate multiple objects from these locations without opening them in the workspace. If you cannot validate the mapping or session from one of these locations, open the object in the workspace and edit it.
Reimporting a Relational Target Definition:
If a target table changes, such as when you change a column datatype, you can edit the definition or you can reimport the target definition. When you reimport the target, you can either replace the existing target definition or
rename the new target definition to avoid a naming conflict with the existing target definition.
To reimport a target definition:
1. In the Target Designer, follow the same steps to import the target definition, and select the target to import. The Designer notifies you that a target definition with that name already exists in the repository. If you have
multiple tables to import and replace, select Apply To All Tables.
2. Click Rename, Replace, Skip, or Compare.
3. If you click Rename, enter the name of the target definition and click OK.
4. If you have a relational target definition and click Replace, specify whether you want to retain primary key foreign key information and target descriptions.
Creating a Primary Key-Foreign Key Relationship:
To create a relationship between two relational tables, choose the Link Column mode from the Layout menu. Drag from the Foreign Key column of one table to the Primary Key column of another table. The Designer may prompt you to remove the existing link to the default primary key table. Editing Table Options
You can edit the following options on the Table tab of the target definition:
* Business names. Add a more descriptive name to the table using the Rename button.
* Constraints. SQL statements for table-level referential integrity constraints. Applies to relational targets only.
* Creation options. SQL statements for table storage options. Applies to relational targets only.
Description. Add a comment or link to business documentation. These are displayed in Repository Manager for the target table.
Adding comments or business documentation links to targets is an easy way to document the purpose of a target. You can add or modify comments to any existing target. You can enter up to 2,000 bytes/K in the description, where K is the maximum number of bytes a character contains in the selected repository code page.
For example, if the repository code page is a Japanese code page where K=2, each description and comment field can contain up to 1,000 characters.
* Keywords. Keep track of target definitions with key words. As development and maintenance work continues, the number of targets increases. While all of these targets may appear in the same folder, they may all serve different purposes. Keywords can help you find related targets. Keywords can include developer names, mappings, or the associated schema. Use keywords to perform searches in the Repository Manager.
* Database type. Define the target type, either a relational database or a flat file. You can change a relationaltarget definition to a flat file target definition and vice versa. When you change the target definition type, you
lose some metadata when you save the changes to the repository. When you change the target definition type from relational to flat file, you lose indexes information, constraints information, and creation options information. The Workflow Manager invalidates all sessions using the target.
When you change the target definition type from flat file to relational, you lose all flat file property information. If you change the target definition back to a flat file, the Designer uses default values for the flat file properties.
The Workflow Manager invalidates all sessions using the target.
Note: If you change the target type from flat file to relational, the Workflow Manager invalidates all sessions that use the target. However, you can change a target type from relational to flat file without invalidating
sessions that use the target.
* Flat file information. When the database type is flat file, you can define the flat file properties by clicking the
To add options to a relational target definition:
1. In the Target Designer, double-click the title bar of the target definition. The Edit Tables dialog box appears.
2. Click the Rename button to edit the target name and the business name.
3. To change the target type, choose a different database in the Database Type field. To change the target type to a flat file target, choose flat file.
4. Edit the following properties for relational target definitions
* To add a constraint, type the SQL statement in the Constraints field.
* To add a creation option, type the SQL statement in the Creation Options field.
5. To add a description, type a description in the Description field.
6. To add keywords, click Edit Keywords. The Edit Keywords dialog box appears.
7. Use the buttons to create and move keywords.
8. Click OK.
You can edit the following information in the Columns tab of the target definition:
* Column name. The column names in the target. When editing a relational target definition, edit the column name if you are manually creating the relational target definition or if the actual target column name changed.
* Datatype. The datatypes that display in the target definition depend on the target type of the target definition.
* Precision and scale. When designing or importing relational targets, consider the precision and scale of values in each column. Precision is the maximum number of significant digits for numeric datatypes, or the
maximum number of characters for string datatypes. Precision includes scale. Scale is the maximum number of digits after the decimal point for numeric values. Therefore, the value 11.47 has a precision of 4 and a scale of
2. The string Informatica has a precision (or length) of 11.
All datatypes for relational targets have a maximum precision. For example, the Integer datatype has a maximum precision of 10 digits. Some numeric datatypes have a similar limit on the scale or do not allow you
to set the scale beyond 0. Integers, for example, have a scale of 0, since by definition they never include decimal values.
You can change the precision and scale for some datatypes to values that differ from the values defined in the database. However, changing the precision or scale can cause numeric overflow on numeric columns, truncation on character columns, or insertion of zeroes on datetime columns when the Integration Service writes to the target column.
* Not null. Choose whether you want to allow null data in the target.
* Key type. Select Primary, Foreign, Primary-Foreign, or Not a Key. Applies to relational targets only.
* Business name. Optionally, you can add business names to each target column.
To edit the columns of a relational target definition:
1. In the Target Designer, double-click the title bar of a target definition.
2. Select the Columns tab.
3. Configure the options of the target definition as described above.
4. If you are creating a target definition and you want to add columns, select a column and click Add.
5. Enter the name, datatype, and other characteristics of the column. Repeat these steps for each column you want to add to the table.
6. If you want to move a column, use the Up and Down buttons, or drag it within the scrolling list.
7. Click OK.
Since indexes speed queries against tables, adding indexes to the target database is an important part of target table design. You can add index information to relational target definitions. Queries to the data warehouse
determine which columns you should index. If you define indexes, select the option to create indexes when you create target tables.
To create indexes for a target table:
1. In the Target Designer, double-click the title bar of a relational target definition.
2. Select the Indexes tab.
3. To add an index, click the Add button in the Indexes section.
4. Enter a name for the index and press Enter.
5. To add a column to the index, click the Add button in the Columns section. Select a column name and click
6. Repeat steps 3 to 5 for each column you want to assign.
7. Click OK.
Important: When you generate and execute the DDL to create the target table, choose to create an index.
Creating a Target Table:
After you add a relational target definition to the repository, you can instruct the Designer to generate and execute the SQL code to create the target in a relational database. You cannot create a table in a relational database from an XML target definition or a flat file target definition.
The Designer generates the SQL script using characters in the UCS-2.
If the target already exists in that database, you can drop it and re-create it. The Designer writes the SQL code to an .SQL text file, so you can review and edit the DDL commands by opening this file.
To generate and execute the SQL code:
1. In the Target Designer, select the relational target definition you want to create in the database. If you want to create multiple tables, select all relevant table definitions.
2. Click Targets > Generate/Execute SQL.
Click Connect and select the database where the target table should be created. Click OK to make the connection.
Enter a file name and location for the SQL script you are about to generate and any options you want to include in the SQL DDL code. This text file exists on the local file system, not in the repository.
Depending on the Generation options you select, the SQL script will contain all of the CREATE and DROP commands that match the selections. For example, if you created a target definition with primary keys, choose
to generate the SQL with primary keys.
3. Click Generate SQL File if you want to create the SQL script, or Generate and Execute if you want to create the file, and then immediately run it.
When you click Generate SQL file, the SQL generated for the selected table definitions is stored in the file you selected. If the file already exists, a dialog box appears prompting you to overwrite the existing file. The
progress of copying the generated SQL file appears in the Output window in the Designer.
After the file has been generated, you can click Edit SQL File, which opens a text editor so you can modify the SQL statements. When the Designer generates the SQL file for the target database, it encloses all table and
field names containing the slash character in double quotes.
You can click Execute SQL File to create the tables. When you click Generate and Execute, the SQL generated for the selected table definitions is stored in the file you selected and immediately executed.
Note: As long as the Designer is open, it locks the SQL file you last opened and modified. If you want to unlock the file so that you can view it in a different application, open a different SQL file in the Designer, or
exit the Designer.
4. Click Close.
When you close this dialog box, the Designer maintains an open connection to the target database. If you reopen the dialog box, you do not need to reconnect to the target database.
SQL DDL Commands in the Designer When the Designer generates the SQL code, it uses generic SQL, not the platform-specific version of the DDL code. The Designer passes these instructions to the ODBC Driver Manager, which converts the standard version of SQL into platform-specific commands. Do not try to run these SQL files through a different utility or use the syntax as an example of the native DDL syntax.
Dropping and Re-creating Indexes:
After you insert significant amounts of data into a target, you normally need to drop and re-create indexes on that table to optimize query speed. You can drop and re-create indexes by either of the following methods:
¨ Using pre- and post-session SQL commands. The preferred method for dropping and re-creating indexes is to define a pre-session SQL statement in the Pre SQL property that drops indexes before loading data to the
target. Use the Post SQL property to re-create the indexes after loading data to the target. Define pre- and postsession SQL for relational targets in the mapping target properties or on the Mappings tab in the session
* Using the Designer. The same dialog box you use to generate and execute DDL code for table creation can drop and re-create indexes. Every time you run a workflow that modifies the target table, launch the Designer
and use this feature when you use this method.
* Stored procedures. You can also use stored procedures to drop and re-create indexes.
If you modify a relational target definition, use the Designer to drop and re-create the corresponding target table. Note: When you drop a target table, the Designer deletes the table from the database. If you want to keep the
target data, back it up before you drop the table.
To re-create the target table:
1. In the Target Designer, modify the relational target definition and select it.
2. Click Targets > Generate/Execute SQL.
In the dialog box, connect to the appropriate target database. Select the DROP options checked for the table and any indexes on the table.
3. Click Generate and Execute.
The Designer drops and re-creates the table, including any indexes assigned to it.