Wednesday 25 March 2015

Difference between Lookup and Joiner Transformation in Informatica

Difference between Lookup and joiner transformation in Informatica


Look up transformation :
a) Look up transformation can be used on a single table or a query to search for records that match incoming records. Matching condition can be specified in the lookup transformation. The result returned can be multiple columns or single column.
b) Lookup transformation can be unconnected or connected transformation. Unconnected transformation can return only single value.
c) Lookup transformation can be static or active. Dynamic lookups are active transformation.
d) Lookup transformation be used with more than one relational operator such as > , =, etc.

 

Joiner transformation :

a) Joiner transformation is used to usually to join data coming from two separate tables or source qualifiers.
b) The join can be left outer join, inner join, right outer join, etc.
c) The joiner returns all the results that match the join condition.
d) The master records in the joiner transformation is cached. The detail records are not cached. Hence, joiner transformation is active transformation.


When do you use joiner or lookup transformation?


a) If  the table size is not too large then preferable to use lookup.
b) If result from a single matching records needs to be returned then use a lookup. If there is a query that needs to be used in a lookup to find the result for lookup then good to use a lookup.
c) If you are doing lookup on a table that is updated in the session then use a lookup. Joiners are active so not preferable.
d) If look up table data does not change then the table can be made persistent and used in the lookup which gives even better performance.
e) If data from two different source qualifiers need to be joined then use a joiner.
f) If data from two different databases are read and need to be joined with a outer or inner join then use joiner transformation.

Thursday 19 March 2015

Creating DATABASE, GROUPS, USERS , GRANTING PERMISSIONS in Netezza

Creating DATABASE, GROUPS, USERS , GRANTING PERMISSIONS in Netezza

Some of the sample netezza commands to create database, user groups , users, add user to a group, grant permissions to netezza objects is below:


Creating a netezza database, group, and adding user group permissions to that database


\c SYSTEM
CREATE DATABASE <databasename>;

CREATE GROUP <groupname> WITH USER <username1>, <username2>;
GRANT LIST ON
<databasename> TO <groupname> WITH GRANT OPTION;GRANT  Create External Table, Create Sequence, Create Synonym, Create Table, Create Temp Table, Create View TO <groupname> ;

GRANT backup, create sequence, create synonym, create table, create external table, create temp table, create view, restore  TO <groupname> ;

To create a user group in netezza database:


Syntax: CREATE GROUP <groupname>

Example: CREATE GROUP  MYTESTGROUP1;

To alter the settings of the group:


ALTER GROUP "<groupname>" WITH ROWSETLIMIT 0 SESSIONTIMEOUT 0 QUERYTIMEOUT 50 DEFPRIORITY High MAXPRIORITY High;

To create a user and assign to a netezza database user group:


CREATE USER  <username>  WITH PASSWORD '??' IN GROUP "<groupname>";

If user already exists or after you have added a user then you can use the command below to add that user to a group:


ALTER GROUP "<groupname>" ADD USER "<username>";

To provide permission on netezza objects such as tables, views, database etc to a user group or a user use the syntax below:


GRANT List ON "<databasename>" TO "groupname";

GRANT List, Select, ON "<tablename>" TO "groupname";


GRANT delete,genstats,groom,insert,list,select,truncate,update ON <tablename> TO <groupname>;
 

To copy permissions from one table to the other in Netezza:


ALTER TABLE <target_table_name> set privileges to  <target table name>;

To change ownership of a netezza table:

alter table <tablename> owner to <newownername>

The above command change the schema name associated with the table

Monday 16 March 2015

Ways to Sort and aggregate data in Informatica

Sorting  and aggregating data in  Informatica

Sorting In Informatica:

In the Source qualifier of Informatica mapping you can sort the data by manually overiding the SQL and adding order by clause to the SQL. Also, group by clause can be added to the source qualifier.

Select col1, col2, col3 from table1 order by col1, col2



The other way to sort data in the source qualifier of Informatica mapping is to use the Number of sorted ports field in the properties tab of the source qualifier (as shown in screenshot). You can set it to any number of ports you want to sort it on and it will sort the data based on the order the ports are in the source qualifier. If you set the number of sorted ports to 2, then it going to sort in ascending order of port1 and port2 which is eqvilent to using order 1,2 clause in the sql.