This topic describes how to use the SingleStore Connector for Informatica PowerCenter to connect Informatica PowerCenter to SingleStore.

The certification matrix below shows the latest versions tested for SingleStore DB and Informatica PowerCenter:

Prerequisites

​ The following prerequisites need to be satisfied before making the connection: ​

1. You have a working SingleStore cluster. ​
2. You have downloaded the configuration .zip from here: https://informatica-connector-releases.s3.eu-central-1.amazonaws.com/SingleStorePowerCenter1.0.0.zip ​ ​

Installing the Connector

Windows Server

1. Unzip the PowerCenter configuration .zip file into an empty directory on the Windows server. The directory should contain the following:

   /client
   /server
   installClient.bat
   installServer.bat
   README.txt
   

2. Run the installServer.bat script, supplying it with the path to your Informatica Server directory. For example:

   cmd> installServer.bat C:\Informatica\10.4.0
   

   The script copies the connector’s .jar files, plugin .xml file, and third party libraries into the relevant server directories and registers the plugin.

3. Go to your Informatica admin console -> PowerCenter Repository Service (PCRS) and set the Operating Mode to “Exclusive”.

4. After the PCRS is restarted, register the plugin using the pmrep command (pmrep is located in the server\bin directory):

   cmd> pmrep.exe registerplugin -i $INFA_SERVER\server\bin\Plugin\SingleStorePlugin.xml
   

   Example:

   cmd> pmrep.exe registerplugin -i C:\Informatica\10.4.0\server\bin\Plugin\SingleStorePlugin.xml
   

5. Now that the server installation is complete, set the PowerCenter Repository Service Operation Mode back to “Normal”.

Windows Client

To install and configure the windows client:

1. Run the installClient.bat script, supplying it with the path to your Informatica clients directory. For example:

   cmd> installClient.bat C:\Informatica\client\10.4.0\clients
   

   The script copies the connector’s .jar files, registry files, and third party libraries into the relevant client directories and adds the registry entries.

2. Verify that PowerCenterClient received the registry files (SingleStore.reg and SingleStore_64.reg). Go to the $INFA_CLIENT\PowerCenterClient\client\bin directory and create the registry entry by double-clicking on the .reg file that corresponds to your OS architecture. SingleStore.reg for 32-bit OS and SingleStore_64.reg for 64-bit OS.

   Example of the full-path:

   C:\Informatica\client\10.4.0\clients\PowerCenterClient\client\bin\SingleStore_64.reg
   

Using the Connector

The connector can be used in both read and write scenarios.

To load the connector as a source, go to the Informatica PowerCenter Source Analyzer, click “Sources” and then click “Create SingleStore Source”.

To load the connector as a target, go to the Informatica PowerCenter Target Designer, click “Targets” and then click “Create SingleStore Target”.

With either scenario, you are taken to a connection window. Fill out the connection details and choose the appropriate tables definitions to load into Informatica.

The mappings and workflows are created in the same way as for any other sources and targets.

Source Join/Filter Example

To join multiple sources in the designer, connect those sources with a single Application Source Qualifier and specify the join conditions.

In the Application Source Qualifier go to Edit Conditions, click Edit Join Conditions, and specify the Join Condition.

Note: Only the Native Expression option is supported – that is the SQL syntax which is recognized by SingleStore. The Join Condition set in the PowerCenter Designer can be overridden for each session by setting a session attribute “User Defined Join” in the Workflow Manager for the corresponding workflow.

You can also specify the Filter Condition for the Application Source Qualifier -> Edit Conditions. Click “Edit Filter Conditions” and specify the Filter Condition.

Again, as in the Join case, only the Native Expression option is supported. The Filter Condition set in the PowerCenter Designer can be overridden for each session by setting a session attribute “Source Filter” in the Workflow Manager for the corresponding workflow.

Target Bulk Insert Example

The connector provides the ability to write data in the bulk mode. To enable the bulk insert mode, check the session attribute “Bulk Insert”.

Avoiding JVM Memory limits

When working with larger tables (100s of MB to TB), PowerCenter can hit JVM memory issues. To avoid potential out-of-memory issues:

1. Go to the Administrator console -> PowerCenter Integration Service -> “Processes” Tab, and increase the Java SDK Minimum Memory and Java SDK Maximum Memory.

Depending on your workloads, values like 1024M or 2048M may be reasonable.

Note: Informatica PowerCenter does not recognize Mb or Gb, it expects the memory number to be formatted like this: 1024M (rather than 1024Mb).

Was this Article Helpful?

Thanks For Your Feedback!