PowerCenter can load data into the following targets:
- Relational. Oracle, Sybase ASE, Sybase IQ, Informix, IBM DB2, Microsoft SQL Server, and Teradata.
- File. Fixed and delimited flat file and XML.
- Application. You can purchase additional PowerCenter Connect products to load data into business sources such as Hyperion Essbase, IBM MQSeries, IBM DB2 OLAP Server, JMS, Microsoft Message Queue, mySAP, PeopleSoft EPM, SAP BW, SAS, Siebel, TIBCO, and webMethods.
- Mainframe. You can purchase PowerExchange to load data into mainframe databases such as IBM DB2 OS/390, IBM DB2 OS/400, and VSAM.
- Other. Microsoft Access and external web services.
You can load data into targets using ODBC or native drivers, FTP, or external loaders.
A target is design with the target designer.
Target definitions define the structure of tables in the target database, or the structure of file targets the integration service creates when you run a session. If you add a relational target definition to the repository that does not exist in a database, you need to create target table. You do this by generating and executing the necessary SQL code within the target designer. Menu > Targets > Generate/Execute SQL
Bulk Load Oracle Guidelines
Bulk Load is a property of a task for a target table.
When bulk loading to Oracle, the Integration Service invokes the SQL*Loader.
Use the following guidelines when bulk loading to Oracle:
- Do not define CHECK constraints in the database.
- Do not define primary and foreign keys in the database. However, you can define primary and foreign keys for the target definitions in the Designer.
- To bulk load into indexed tables, choose non-parallel mode and disable the Enable Parallel Mode option.
Note that when you disable parallel mode, you cannot load multiple target instances, partitions, or sessions into the same table. To bulk load in parallel mode, you must drop indexes and constraints in the target tables before running a bulk load session. After the session completes, you can rebuild them. If you use bulk loading with the session on a regular basis, use pre- and post-session SQL to drop and rebuild indexes and key constraints.
- When you use the LONG datatype, verify it is the last column in the table.
- Specify the Table Name Prefix for the target when you use Oracle client 9i. If you do not specify the table name prefix, the Integration Service uses the database login as the prefix.
Pre and post-session SQL are property of the session task on a Source Qualify transformation or target table. The Integration Service runs:
- pre-session SQL commands before it reads the source.
- post-session SQL commands after it writes to the target.
Documentation / Reference
- PowerCenter Workflow Basic Guide - page 88 Chapter 7 Bulk Loading