Sunday 14 June 2015

How to connect to amazon redshift using Informatica power center?

How to connect to  amazon redshift using Informatica power center?


You can connect to amazon redshift from the drivers available on Informatica cloud. However, if you want to connect Informatica power center 9.1 and prior version  to redshift, using Postgres odbc drivers are an option. You can use DataDirect 6.1 PostgreSQL Wire Protocol and later versions for this purpose.

Step1: Install the postgres driver and create an entry in .odbc.ini


Some of the parameters you might have to set in your odbc (.odbc.ini) connection is below: Check you driver for the complete settings:

[PostgreSQL]
Driver=/informatica/server91/ODBC6.1/lib/DWpsql25.so
Description=DataDirect 6.1 PostgreSQL Wire Protocol
Database=mydatabasename
0HostName=myreshifthost.cqa0adasda-east-1.redshift.amazonaws.comLogonID=myuser_name
Password=mypassword
PortNumber=yourportnumber
TrustStore=/export/redshift-ssl-ca-cert.pem
PacketSize=32


You can contact Informatica or read the driver documentation for the full settings.

2) Create a relational odbc connection from Informatica workflow manager as shown below: 



Wednesday 10 June 2015

How to enforce primary key constraint in Informatica when loading to netezza?

Does netezza enforce primary key constraint?


Neteza does not enforce primary key constraint. Application that loads data to netezza has to enforce it.

How to enforce primary key constraint in Informatica when loading to netezza?


If Informatica is loading data to netezza you can enforce primary key constrain by setting the primary key constraint in the target definition of Informatica mappings. This allows it to enforce primary key constrain in the current load but does not work if there is data already in the netezza table. For example, if record1 and record2 are duplicate in the current load then Informatica detects it when loading to netezza table. However, if records2 already exists in netezza table, and you are now loading only record1 then Informatica cannot find out about record2.

In this circumtance you have to put additional logic in informatica such as a lookup or filter condition to check for existing records in netezza target table. Hope this helps!!




Monday 8 June 2015

How to install UDF functions on Netezza?


How to install an user defined function (UDF) in netezza?


Lets says you have got hold of the UDF function from netezza and want to install it then the below steps would help. In the example below, reverse function is installed on netezza server.


How to install UDF functions on Netezza?


 In the folder where you have placed the tgz file execute the below instruction.

[netezzahost]$ gunzip reverse.tgz
[netezzahost]$ tar -xvf reverse.tar

[netezzahost]$ cd reverse

[netezzahost]$ ./install <databasename>

CREATE FUNCTION
Created udf
Done


After this step you might have to execute grant command to provide permission on the function to the users.

For example:

grant execute on reverse to <mygroup>;


Login to database and test the function:

select reverse('mydog');
 REVERSE
---------
 godmy
(1 row)

How to connect to microsoft sqlserver from Informatica using odbc driver ?

How to connect to sqlserver from Informatica using odbc driver?

Step1: set up the .odbc. ini file with the below entry. You will have to enter the proper path for your driver and information for your database.

[MYSQLSERVER]
Driver=/data/informatica/ODBC6.1/lib/DWmsss25.so
Description=DataDirect SQL Server Wire Protocol
Database=mydatabase
LogonId=mysuerid
Password=mydatabase
Address=machinename,1433
QuotedId=No
AnsiNPW=No

Step 2: set up the relational odbc connection from your workflow manager as shown in screenshot below.