Transcript
Loading Bulk Data into Sybase IQ 15.1 from PowerCenter
© 2011 Informatica Corporation. No part of this document may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording or otherwise) without prior consent of Informatica Corporation.
Abstract The PowerCenter Integration Service can load bulk data to Sybase IQ targets. This article describes how to configure the Sybase IQ 15.1 external loader connection and use the connection details in a session.
Supported Versions
PowerCenter 9.1.0
Table of Contents Overview .......................................................................................................................................... 2 Prerequisites .................................................................................................................................... 2 Connection String in Sybase IQ External Loader ............................................................................ 3 External Loader Executable Attribute .............................................................................................. 3 Output File Directory ........................................................................................................................ 3 Connecting to the Sybase IQ 15.1 Server ....................................................................................... 3 Step 1. Map Sybase IQ Datafile Directory to the PowerCenter Integration Service.................... 4 Step 2. Create a Session with a Sybase IQ External Loader Connection ................................... 4 Step 3. Configure Sybase Target Properties ............................................................................... 4 Rules and Guidelines for Sybase IQ External Loaders ................................................................... 5
Overview You use an external loader connection type for Sybase IQ in PowerCenter. External loaders increase load performance by loading data directly from a file or pipe instead of using SQL commands to insert data. The Sybase IQ external loader generates data output files and then calls an application external to PowerCenter to connect to Sybase IQ and load the data from the files. You can configure the Sybase IQ external loader to load data to Sybase IQ 15.1.
Prerequisites A Sybase IQ server is running on a Windows machine and a PowerCenter Integration Service is running on another windows machine on the same LAN. To verify PowerCenter can connect to the Sybase IQ target, complete the following steps: 1. Ensure that Sybase IQ 15.x server is running. 2. Install the Sybase IQ 15.x client on the machine where the PowerCenter Integration Service runs. Ensure you add the Sybase IQ server details into the interfaces file of the Sybase IQ client installation. 3. Designate a folder on the Sybase server machine to contain the control files and the data files. Map this folder as a network drive on the machine where the PowerCenter Integration Service runs.
2
4. Ensure the following environment variables are set in the machine where the PowerCenter Integration Service runs. set set set set set
SYBASE=C:\Sybase_IQ151 SYBROOT= C:\Sybase_IQ151 SYBASE_JRE6_32= C:\Sybase_IQ151\shared\JRE-6_0_10_32BIT IQDIR15= C:\Sybase_IQ151\IQ-15_1 PATH=%IQDIR15%/bin32;%PATH%
5. Ensure that the mapped network drive or the mounted drive is accessible before bringing up the Informatica services on the system hosting the PowerCenter Integration Service. 6. Restart the PowerCenter Integration Service.
Connection String in Sybase IQ External Loader In Sybase IQ 15.1, you must enter the connection string in the revised format. The connection string includes a valid Sybase IQ user ID and password, and the Sybase IQ engine name. The session might fail if you use quotes in the connect string. The revised connection string in Sybase IQ External Loader contains the following attributes: uid=user ID; pwd=password; eng=Sybase IQ database server name
The revised connection string format includes values as described in the following table: Property
Description
uid
Database user ID.
pwd
Database password.
eng
Sybase IQ database server name.
For example, enter the connection string as follows: uid=dba;pwd=sql;eng=iqsn_2303
External Loader Executable Attribute PowerCenter uses the Sybase IQ dbisql utility to execute a bulk copy into Sybase IQ. In Sybase IQ 15.1, while configuring the Sybase connection properties for the external loader executable, enter the host name and port number. For example, enter the External Loader Executable attribute as follows: dbisql -host inbmogli -port 2638
If you use an executable file with a different name, you must update the External Loader Executable field. If the external loader executable file directory is not in the system path, you must enter the file path and file name in the External Loader Executable field.
Output File Directory In the target properties in the session, enter the output file directory to include the Sybase IQ server data file directory that is mapped as a network drive on to the machine where the PowerCenter Integration Service runs. The output file directory defines the location where the Informatica PowerCenter Integration Service writes the temporary output files. The Sybase IQ External Loader uses the output file for loading into Sybase IQ 15.1. The PowerCenter Integration Service overwrites the output file each time it runs the workflow.
Connecting to the Sybase IQ 15.1 Server To connect to the Sybase IQ 15.1 server, complete the following tasks: 1. Map Sybase IQ datafile directory to the PowerCenter Integration Service.
3
2. Create a session with a Sybase IQ external loader connection. 3. Configure Sybase target properties.
Step 1. Map Sybase IQ Datafile Directory to the PowerCenter Integration Service 1. Open Windows Explorer. 2. Click Tools > Map Network Drive. 3. Browse to the system directory for Sybase IQ data file. This maps the data file directory of the Sybase IQ server on the machine where the PowerCenter Integration Service runs.
Step 2. Create a Session with a Sybase IQ External Loader Connection 1. 2. 3. 4.
In the Workflow Manager, create a session for the Sybase mapping. Choose the reader connection corresponding to the source type. Select Sybase IQ External Loader as the target connection type. Enter the connection string as mentioned in the following table: Property
Description
uid
Database user ID.
pwd
Database password.
eng
Sybase IQ database server name.
For example, enter the connection string as follows: uid=dba;pwd=sql;eng=iqsn_2303
5. Enter the following values under the attributes as mentioned in the following table: Property
Description
Server Datafile Directory
Enter the Sybase IQ server path where the PowerCenter Integration Service loads the data files. The source connection writes loader files into the directory and the target external loader connection then loads the files into Sybase IQ.
External Loader Executable
Enter the Sybase IQ dbisql utility. PowerCenter uses the Sybase IQ dbisql utility to execute a bulk copy into Sybase IQ.
Under the External Loader Executable attribute, enter the host name and port number. For example, enter the External Loader Executable attribute as follows: dbisql -host inbmogli
-port 2638
Step 3. Configure Sybase Target Properties 1. Edit the Sybase session. 2. Under the Targets property, enter the output file directory. This is the IQ server data file directory that is mapped as a network drive on to the machine where the PowerCenter Integration Service runs. 3. Ensure the values entered for target file directory and bad file directory for the flat file target is a mapped network drive location if the Sybase IQ server is on Windows. Verify that the flat file target is a mounted location if the Sybase IQ server is on UNIX so that the server data file directory is accessible to the PowerCenter Integration Service.
4
Rules and Guidelines for Sybase IQ External Loaders Use the following rules and guidelines when you use external loaders to load to Sybase IQ: Ensure that target tables do not violate primary key constraints. Configure a Sybase IQ user with read or write access before you use a Sybase IQ external loader. Target flat files for a Sybase IQ external loader can be fixed-width or delimited. The Sybase IQ external loader cannot perform update or delete operations on targets. For a session with multiple partitions, use the round-robin partition type to route data to a single target file. If the PowerCenter Integration Service and Sybase IQ server are on different machines, map or mount a drive from a machine hosting the Sybase IQ server to the machine hosting the PowerCenter Integration Service.
Author Anju Andrews Senior Software Engineer, QA
5
