Create or Replace Target Tables
In the Developer tool, you can generate a DDL script for one or more relational data objects in the Model repository, and run the DDL script to create or replace tables in the target database. If a target already exists in that database, you can drop the target and re-create it.
You can create or replace target table at design-time or at runtime. To create or replace tables at design-time, you must create and execute the DDL script before you run a mapping. You can configure the write transformation in a mapping to create or replace a relational target at run time.
The Developer tool generates a database-specific version of the DDL script for all supported connection types. If you select a JDBC or ODBC target, the Developer tool generates the ANSI SQL-92 generic data type format. You can generate DDL scripts for the following database types:
- •IBM DB2
- •Greenplum
- •Hive
- •JDBC
- •Microsoft SQL Server
- •Netezza
- •ODBC
- •Oracle
- •Teradata
Note: You cannot generate a DDL script if you use an OLE DB SQL Server connection.
Rules and Guidelines to Create or Replace Target Tables
Consider the following rules and guidelines when you generate and execute a DDL script:
- •Avoid selecting multiple objects with the same source table name. When you select multiple objects with the same source table name, the DDL code fails. If you select the Drop Table and the Create Table options for three objects with the same source table name, the DDL code succeeds for the first Drop Table and Create Table commands, and fails for the subsequent commands.
- •Before you run a mapping, verify the data types and manually update them, if required. The char and byte semantics in Oracle are ignored when you generate the DDL script. When you create a table that contains char and varchar2 columns in an Oracle database, you can define the storage for char and byte semantics. When you import the metadata for the Oracle table into the Developer tool, the char and byte semantics are ignored. If you generate the DDL script for the Oracle table, the Developer tool defines the data type as Char.
- •If you generate DDL for ODBC connections, the Developer tool creates the DDL script in ANSI SQL-92 generic data type format. The ANSI SQL-92 format might not run on all databases as the target database might not support the data type or data length.
- •If you create a DDL script to migrate database tables from Greenplum to Netezza, you can incorporate only 16000 characters in the NVARCHAR column because the NVARCHAR data type supports only up to 16000 characters in a Netezza database.
- •When you generate the DDL script, the Developer tool identifies the best data type match between the databases that the original connection and the new connection point to. The precision and scale for data types vary between databases. In an Oracle database, the default precision and scale for the Timestamp data type is (29, 9). When you generate the DDL script from Oracle to Microsoft SQL Server, the precision and scale for the Timestamp data type reduce to (26, 6). When you generate the DDL script from Oracle to DB2, the precision and scale for the Timestamp data type reduce to (27, 7).
Generating and Executing DDL at Design-Time
Before you generate and run the DDL script, ensure that the user has the appropriate read and write permissions to access the target database.
1. In the Object Explorer view of the Developer tool, select the relational data object for which you want to create a table in the target database. If you want to create multiple tables, hold down the Shift and Ctrl keys to select multiple data objects.
2. Right-click a selected data object and select Generate and Execute DDL.
The Generate and Execute DDL dialog box appears.
3. In the Object Selection area, select the Target Database Type for which you want to generate DDL.
4. In the DDL Generation area, you can select the following options:
- - Drop Table. Drop a table in the database before you create it. Select this option to replace a table.
- - Create Table. Creates a table on the target database.
- - Primary Key. Creates primary keys based on the selected data objects.
- - Foreign Key. Creates foreign keys based on the selected data objects.
- - Copy to Clipboard. Copies the DDL script to the clipboard.
- - Save As. Saves the DDL script to a file.
5. In the DDL Generation area, click Generate DDL.
The DDL generated for the selected data objects appears in the DDL Generation area.
Warning: If you edit the DDL script generated in the DDL Generation area, you might encounter errors when you run the DDL script.
6. In the DDL Execution area, click Browse and choose a target database connection.
The Choose Connection dialog box lists the target database connections based on the target database type that you choose in the Object Selection area. For example, if you choose Oracle as the target database type, the Developer tool displays Oracle connections.
7. Click Execute DDL.
8. Click Close.
Generating and Executing DDL at Runtime
You can configure the Write transformation in a mapping to create or replace a relational target at run time.
The Data Integration Service generates and executes the DDL script at run time. For more information, see the Informatica Developer Mapping Guide.
DDL Generation Errors
When you generate and run a DDL script, you might encounter an error. The error can occur due to one of the following reasons:
- •If the target database does not support the data type that you selected.
- •If you select a physical data object that contains a cyclic dependency.
- •If you select a physical data object that has no columns.
