Showing posts with label Informatica. Show all posts
Showing posts with label Informatica. Show all posts

Wednesday, 25 April 2018

4/25/2018 02:27:00 pm

Informatica High- availability of Domain

Terminology

  • Gatewa​y Node: A node configured as a gateway can become the master of the domain.
  • Master Gateway Node: A gateway node that is currently acting as master.
  • Worker Node: A node marked as a worker node will not become the master of the domain but report to the current master.
  • Master Election: A routine run by nodes to find the current master or unanimously elect a master.
  • Domain database: Database schema where domain metadata is stored. This database acts as an arbiter during master election process.
  • Database heartbeat: A heartbeat (update query) run periodically by the master node on the domain database to broadcast its liveness
  • Node heartbeat: A heartbeat message sent by all non-master nodes to master node updating its liveness 

Design

The availability of the Informatica domain is based on the availability of an elected master node. When the first gateway node is started, it runs the master election routine and becomes the master node. When the next gateway nodes are started, they discover the first node as the master (as a part of master election) and register with it. These nodes periodically send a (node) heartbeat to the master node to express its aliveness.

When the master node terminates, the other live gateway nodes detect the unavailability of the master node by the failed Node heartbeat. At once, they re-run the master election (master re-election) to elect a new master. After a new master is identified, the remaining nodes registers with the new master.

Clients may not be able to connect to Informatica Services temporarily when there is no elected master in the domain.
Note
​Worker nodes do not provide domain High-availability. During startup, it attempts to connect to every gateway node in the domain to identify the current master and registers on identifying the new master.

Database Heartbeat

The master node runs a heartbeat update query on the domain database periodically to persist its aliveness. This is critical for other gateway nodes to be aware of a live master node.
All Gateway nodes perform this heartbeat during the master election routine. This heartbeat informs any electing node of other participating nodes.
This heartbeat is used by master node as a test on availability of domain database.
Database heartbeat periodicity is controlled by the domain-level custom property MasterDBRefreshInterval. The default (and minimum) value is 8 seconds.

Database heartbeat timeout

When master node terminates unexpectedly, the other gateway nodes wait for a timeout before attempting to become the new master. This timeout is 4 x MasterDBRefreshInterval (32 seconds by default). In case of a temporary glitch causing delay/error in the master’s heartbeat, the higher timeout value increases tolerance and does not trigger spurious re-election.

When the master node fails to update the database within the timeout, it gives up the master role and terminates itself. This ensures consistent behavior with other nodes that could be electing master (especially in situations when master node loses network connectivity)

Note

In a domain with single-gateway node (where domain HA & master election does not apply), the heartbeat timeout is 12 x MasterDBRefreshInterval. Here, this heartbeat is only used as a test for availability of domain.

Node heartbeat

The master node has to know which nodes are alive to maintain service(s) status and availability. All non-master nodes update the master node periodically to express its liveness.

Node heartbeat is controlled by node command line option (configured via INFA_JAVA_OPTS environment variable)    “-Dinfa.masterUpdateTimeInterval” and defaults to 15000 milliseconds.
​Note

This has to be configured to the same value on all nodes (gateways and workers alike).

Node heartbeat timeouts

Master node behavior              

If the master does not receive heartbeat from a node within than 6xNodeHeartBeat seconds (90 seconds default), it will mark the node status (as well as services running on that node) as inactive/dead and attempt to start services on other live nodes (as configured).

In the event of a non-master node termination (unexpected), it will take 6xNodeHeartBeat seconds before marking the node as dead.

Non-Master node behavior

If a non-master node failed to send its heartbeat to the master (error/timeout), then it assumes that the master node is not available and re-runs master election. This no-wait model ensures that the other gateway nodes do not waste time before re-electing a master.

In case of a temporary glitch causing the heartbeat message to error/time out, the node will discover the same old master node to be alive and continue reporting to it. 

Co​mmon Database heartbeat failures

As explained in the previous section, the master node terminates itself if it fails to run heartbeat query within the timeout. This is observed in node.log as follows:

This occurs in the following situations:

Error in accessing domain database

When domain database is not available (planned or unplanned) for longer than the timeout period, this will cause errors during heartbeat in the node and errors will be logged in node.log & exceptions.log.

Errors (SQLException) will be logged in node.log and exceptions.log before the termination message.

Network/communication errors

Network errors such as Connection refused (when database server is not accessible), No route to host (when the Informatica host loses network connectivity), Connection timed out (when a TCP timeout occurs) etc. will be logged in node.log and exceptions.log

Timeouts

When the heartbeat thread in the master node did not update the database within the timeout, the node will terminate.
A fatal message will be logged in node.log when the query was not run within the timeout.

Following situations can cause the timeout:
  • ​Network issues
    Network at either ends (Informatica host or Database host), or any network element in-between can cause timeout errors.
  • Resource  crunch (and/or) Node process starvation
    Saturated utilization of system resource on the Informatica host (such as CPU, memory, disk, network) can cause starvation in node process causing heartbeat threads to timeout.
  • Java Garbage collection pauses
    The Informatica node is a java process, where Java’s Garbage collection threads might suspend the application involuntarily for a longer time than the timeout.

Common Node heartbeat failures

By design, node heartbeat is interpreted differently by master node and non-master nodes. Master node gets to know the status of all other nodes in the domain so that it can ensure service availability. Non-master nodes piggyback on node heartbeat to be aware of the availability of master gateway node.
Similarly, failure of a heartbeat message (by non-master nodes) & heartbeat timeout error (by master node) trigger different routines. Below are common situations:

Node heartbeat timeout failure

When the master node fails to get a heartbeat from a non-master node within the timeout, it marks the node as inactive and starts failing over services that were running on the non-master to other available nodes (if applicable). The state of the non-master node process (if it was alive or dead) does not matter, as if it is not able to update the master, it is as good as dead.
  • ​Logs on the non-master node will help identify if the node was terminated for some reason.
  • Logs on master node and domain logs, tells the story of heartbeat failure.
  • In case of the non-master node was alive yet marked as inactive, infa9dump on the non-master node & master node helps identify if there’s any blocked communication between the 2 nodes

Node heartbeat failure

When a single heartbeat message from non-master node is not delivered on time, the non-master questions the availability of the master and starts master re-election (in case of gateway node) or searches for a new master (worker node). However, it continues to run the services running on the node during this period.

In case of a temporary network glitch, the heartbeat failure will not have any impact on domain/services.
Node.log shows an error as follows:

ERROR [Domain Monitor] [DOM_10025] The node cannot send heartbeat messages to the master gateway node. 

Troubleshooting

Heartbeat failure/timeout can happen due to difference causes such as Informatica configuration or related issues, and system-level causes such as resource utilization, environment, and network issues. 
Following are some common steps to isolate the issue to system-level causes.

Troubleshooting Informatica application related issues

InfaLogs

Analyzing logs from different components/nodes helps put parts into the full root-cause story. Collecting InfaLogs including domain and service logs from all nodes in the domain help understand the complete situation.
Note

Logs from an unaffected node also help in understanding the pattern of affected vs. unaffected parts of the domain

JDBC Spy logging can be enabled to debug queries executed and relevant errors/delays for database heartbeat failures (however, this does not help in database connectivity issues)

InfaDump

InfaDump on the node process collects diagnostics such as thread & heap dump that helps perform deeper analysis. This is useful to debug situations when threads are unexpectedly blocked/stuck when performing heartbeat causing timeouts/failures though processes are still running.

Java GC logging

Enabling Java’s GC log (command-line option) for the node process will help identify if GC pause is the cause of the issue. Typically, high GC pause is due to invalid configuration of –Xmx for the node process. The default value of 512m is the minimum for a node, but necessary size could be higher based on number of services & users in the domain.

In rare situations, this can be due to memory leak in the node process. Collecting infa9ump on the node process will help isolate memory leaks

Troubleshooting system & network

System resource crunch (and/or) process starvation

Monitoring system resources at granular-level and identifying resource-saturation & process-starvation during the window of timeout will help identify as the cause of the heartbeat failure. Including all resource utilization, and load-average will show any anomalies. In the case of virtualized environment, it will be critical to monitor resources granted to the guest and not just the provisioned resources. For instance, metrics such as CPU ready%, memory balloon in VMWare, help identify starvation caused by virtualization.

Network errors

Network errors such as dropped packets or delayed transfers at either ends of communication (Master Host ÃŸÃ  Non-master host, Master host ÃŸÃ Database host), or any network element in-between such as switches, routers, firewalls, virtualization layer can cause timeout. This can be due to network congestion, broken hardware, configuration issues etc.

Comparing TCP packet dumps captured at both ends of communication will help isolate packet delivery.
Note
A filter with IP addresses of host and port number (database’s port in case of database heartbeat failure, and node’s Service Manager port in case of node heartbeat failure) can reduce the size of captured packets

Tuning

The default configuration of heartbeat intervals & timeouts in Informatica are expected to function in robust environments. However, depending on system load & related configuration, there could be a lot of unexpected heartbeat failures (followed by unexpected service unavailability/failover). The heartbeat intervals can be increased to higher values to be more tolerant to temporary failures.

Note

Increasing heartbeat intervals has a side-effect of delaying failover when a real failure happens. For example, master re-election will take longer when a master node terminates unexpectedly on increasing database heartbeat interval

Following is a list of tunables relevant to Informatica heartbeats:
34
By
at 34 comments:
Labels:
4/25/2018 03:38:00 am

How to stabilize Informatica domain in a non optimal environment

There are four types of settings that influence performance, and can help stabilize a domain:
  1. Resilience: Settings that influence the keeping alive of processes during (temporary) network failures
  2. Timeouts: Values that govern if or when a connection is lost
  3. Intervals: Values that govern how often to check whether a process or service is alive
  4. Performance




Resilience

Resilience is the ability of PowerCenter service clients to tolerate temporary network failures until the timeout period expires or you resolve the system failure. Clients that are resilient to a temporary failure can maintain connection to a service for the duration of the timeout.

There are two variables associated with resilience:

Limit on Resilience Timeouts: 180 (default). The amount of time a service waits for a client to connect or reconnect to the service. This limit can override the client resilience timeouts configured for a connecting client. This is available for the domain and Application Services.

Resilience Timeout: 30 (Domain), and 180 (Repository and Integration Services). The amount of time a client attempts to connect or reconnect to a service. A limit on resilience timeouts can override the timeout.
In general, it is advisable to keep all limits at the same level, to keep the interdependency even.

Timeouts
Connection Timeout: 40000 (server/tomcat/conf/server.xml). The number of milliseconds the Connector (https) waits, after accepting a connection, for the request URI line to be presented. This parameter is a general tomcat server parameter and is to do with how long the server leaves a connection between the browser and server open after a response is complete.
Repository Service: Database Properties: DatabaseConnectionTimeout: 180 Period of time that the Repository Service attempts to establish or reestablish a connection to the database system. Default is 180 seconds.

Refresh intervals
Domain: Properties: Custom Properties: MasterDBRefreshInterval: 8 (effective 28 seconds with factor 3.5). This is the interval the domain master gateway uses to write its entry in the domain database, table PCSF_MASTER_ELECTION, determining it is alive. If this update fails four consecutive times, the master gateway node shuts down. If there are multiple gateway nodes in the domain, a new master gateway will be elected, assuming the domain database is alive.
/server/tomcat/bin/infaservice.sh -Dinfa.masterUpdateTimeInterval=15000.
The domain service marks a node inactive if the master gateway node does not gets a notification from the node within six times 15 seconds. When the CPU load on the machine is high, the worker node cannot send the notification in time. The master gateway node will mark the node as inactive and all the services on the node will not be available, despite the node is actually up and running. Change the master gateway timeout by adding the following system property to INFA_JAVA_OPTS: setenv INFA_JAVA_OPTS="${INFA_JAVA_OPTS} -Dinfa.masterUpdateTimeInterval=X"
Repository Service: Properties: Advanced Properties: HeartBeatInterval: 60 Interval at which the Repository Service verifies its connections with clients of the service.

Performance
Repository Service: Properties: Database properties: Optimize Database Schema: No. Enables optimization of repository database schema when you create repository contents or back up and restore an IBM DB2 or Microsoft SQL Server repository. When you enable this option, the Repository Service creates repository tables using Varchar(2000) columns instead of CLOB columns  wherever possible. Using Varchar columns improves repository performance because it reduces disk input and output and because the database buffer cache can cache Varchar columns.

To use this option, the repository database must meet the following page size requirements:

IBM DB2: Database page size 4 KB or greater. At least one temporary tablespace with page size 16 KB or greater.
Microsoft SQL Server: Database page size 8 KB or greater.
3
By
at 3 comments:
Labels:

Wednesday, 28 March 2018

3/28/2018 03:03:00 am

Informatica How to Series (Useful Tips)



In this post we will give you few useful how to tips

How to merge two target files in same structure  session/mapping 
#########################################################################
  1. Make sure that at the session level both the targets are having the same name.
  2. Choose Merge Type as sequential merge for both targets
  3. For second target (as per  Target load plan at the designer ), select button "Append if Exists".
#########################################################################

How to Validate validate all mappings in a particular folder  
#########################################################################
 Please refer to below link for details
How to validate all-mappings in folder

#########################################################################

How to create target file name with time stamp
#########################################################################
 There are there option to achieve this
  1. Rename the file in post session command using unix script
  2. Create a workflow variable with timestamp 
  3. Using Output type as command in session properties
I will explain three methods in detail soon
#########################################################################
1
By
at 1 comment:
Labels: ,

Tuesday, 27 March 2018

3/27/2018 02:30:00 pm

Strange !!! Mapping Variable Value Taking Persistent value from Repository


Today we are going to discuss behavior of mapping variables persistent values.Most of experienced informatica developers have used the concept of assigning mapping variable value to workflow variable using post session variable assignment feature.Passing mapping variables  is quite handy in many scenarios where you want to perform some action on basis of value passed from mapping i.e. you might to send an email or start another session.

 Normally it is done in below way 
  1. Define a Mapping variable 
  2. Assign value to variable using setvar functions
  3. Pass value to workflow parameter is using postsession variable assignementAll is good till we encounter error VAR_27049 : Mapping variable name: [$$MAP_VAR], persisted value: [9], run instance name []. 

 Most of us are confused about this issue and try different way to overcome this by assigning value on parameter file etc.
Mapping variables are different from normal programming language variables as they have below features
  1. Mapping variables have an aggregation set, which is either Maximum or Minimum. (See Informatica Designer >Mappings > Parameters and Variables).
  2. Mapping variables are assigned to a variety of values during the session run; the final value of the variable is either the maximum or the minimum of the values. 
  3. In the related mapping, if the aggregation is set to Max, the final value is the maximum value. That means that if the value given in the parameter file is lower than the value already in the repository, it will be ignored. 
Below example will make it more clear
Example
Consider that in the we are using max type of variable , the value is *1000* for the VAR1 mapping variable. 
**********************************************************************************************************
2018-02-23 18:02:59 : INFO : (6615 | DIRECTOR) : (IS | myinteg_IS) : node01 : TM_6962 : The variable [$$WFVAR1] will be assigned from [$$VAR1] 
2018-02-23 18:02:59 : INFO : (6615 | DIRECTOR) : (IS | myinteg_IS) : node01 : VAR_27048 : Persisting mapping variable values to the repository. 
2018-02-23 18:02:59 : INFO : (6615 | DIRECTOR) : (IS | myinteg_IS) : node01 : VAR_27049 : Mapping variable name: [$$VAR1], persisted value: [1000], run instance name []. 
**********************************************************************************************************

If mapping variable comes out greater than 1000 in mapping then it will work fine.But if Value  comes less than 1000, it keeps the persistent value and only gets reset if the Parameter file value is Reset via Workflow Manager. 
You can reset mapping variable in the Workflow session gets "reset" (right-click the Session task and select View Persistent Values. Click Reset Values to delete existing variable values),This is a manual step and not feasible with automated runs.
Solution :Apparently there is no direct solution to avoid this,you might need to design your mapping in such a way that it gets value more than value saved in repository (or less in case of aggregation type defined as minimum),You can try to prefix it with running number







26
By
at 26 comments:
Labels: ,

Thursday, 10 December 2015

12/10/2015 08:23:00 am

SQL Override Pros and Cons

This is one of most frequently asked interview question for a informatica developer "what are the pros and cons of using SQL Overide ? " 


Pros 
  1. We can perform complex join conditions and filter conditions easily and the same is directly pushed to the database level without Informatica having to write the queries.
  2. We can override queries to make them use indexes and to get better execution plan.We can also use hints in SQL query
  3. There are few things which can be achieved easily in sql query using advanced sql feature like Top/Pivot/CTE etc.To achieve same in informatica you have to write sql logic
  4. In few cases when we are migrating from some legacy system to Informatica we can directly use old SQL queries in sql override which will save lot of efforts

Cons

  1. The no of columns connected in the downstream transformation  should match with the datatype and length with the overridden column name even if it does not necessarily need the same column names
  2. We lose the ability to perform source side push down optimization the moment we perform an override
  3. By writing complex queries on the override, we complicate the code which is against the fundamental rules of using ETL packages 



4
By
at 4 comments:
Labels: , ,
12/10/2015 02:44:00 am

Control M Character in File --Everything explained

This is the problem which every IT Person faces when dealing with files across platforms.If you haven't faced it I can guarantee you will face it one day.



There is lot of information on internet about this but it has not been clearly explained.I will try to put all information together about Control M character at one place.

First of all it is important to understand

"Line Endings" in different operating systems 

Line Ending means when you are at end of line and how Enter Key is interpreted in different operating systems.

All major operating system interpret Enter character differently

OS Line Ending
Windows CR/LF
Unix LF
Mac CR/LF

CR = \r = Carriage Return
LF = \n = Line Feed
Why Do Control M Character Occur

When we  open a text file that was created under Windows. The text characters in the first line are all displayed correctly. At the end of the line, we find a CR. This means nothing special to Unix so the unix attempts to display the character. CR is a non-printable character. Under Unix many of these non-printable characters are mapped to control characters when displayed. In the case of CR, its displayable equivalent is Control-M. This is displayed in most editors as ^M.
The next character is a LF. Unix is fine with it as it is the standard line end character. The editor therefore moves to the start of the next line on the display and starts to process the next line.

How to view Control M Character in Unix

Below will show all  tabs, vertical tabs, carriage returns, linefeeds and whatnot using the slash notation.
od -c Yourfile.txt

If you only want to see control M Character

cat -v filename.txt
Possible Solutions

  1. Remove character in VI Editor Open the VI Editor and do :1,$/^M//g .^M character can be produced by  holding down the control key whilst pressing M at the same time
  2. Different Unix Command : There are various commands availaible to remove control m character    1)  tr -d '\r' < infile.txt > outfile.txt 2) dos2unix < DOSfile.txt > Unixfile.txt
  3. Fix during FTP : Control M character will not appears if files is transferred using ASCII Mode
If you need to process such file in informatica you can call above commands in pre session to remove control M character

 I Hope you are clear about why Control M character occur and possible solutions




3
By
at 3 comments:
Labels: ,

Thursday, 20 March 2014

3/20/2014 03:38:00 pm

SQL Transformation -Practical Usage


The SQL transformation processes SQL queries midstream in a pipeline. You can insert, delete, update, and retrieve rows from a database. You can pass the database connection information to the SQL transformation as input data at run time. The transformation processes external SQL scripts or SQL queries that you create in an SQL editor. The SQL transformation processes the query and returns rows and database errors.

Mode
Script mode : The SQL transformation runs ANSI SQL scripts that are externally located. You pass a script name to the transformation with each input row. The SQL transformation outputs one row for each input row.
Query mode : The SQL transformation executes a query that you define in a query editor. You can pass strings or parameters to the query to define dynamic queries or change the selection parameters. You can output multiple rows when the query has a SELECT statement.

Main Properties

Passive or active transformation : The SQL transformation is an active transformation by default. You can configure it as a passive transformation when you create the transformation.
Database type: The type of database the SQL transformation connects to.
Connection type. Pass database connection information to the SQL transformation or use a connection object.

There is lot of theoretical document about this transformation about this on internet and help files I will try to show how we can use it in different practical scenarios


  1. Remove duplicate records
  2. Dynamic Query in SQL Transformation
  3. Capture Database errors using SQL Transformation
  4. SQL Transformation with Non-Equi Join
  5. Dynamic DB Connections using SQL Transformation
  6. One to Many Records SQLTx
  7. NonEql Join MultMatch Lkup
  8. Dynamic Split Data


Remove duplicate records

We can remove duplicate records by using SQL Transformation by using below method.
First use aggregator transformation to count of duplicate rows then use same variable in SQL transformation using parameter substituting
Use below in SQL transformation after creating sql transformation in query mode
delete test_dup where PRODUCT_ID=~PRODUCT_ID~ and SUBPRODUCT_ID=~SUBPRODUCT_ID~ and rownum <~count_dup~
TEST_DUP is table and we want to find duplicate data on basis of product id and sub product id.
We have used string substitution feature of SQL transformation here i.e. use of  ~




Dynamic Query in SQL Transformation :

In most of datawarehouse projects we encountered error ORA-02149 Specified partition does not exist as sometime we don’t have partition created for new data or data comes with unexpected value which does not have corresponding partition.
Though it can be overcome by using interval partitioning feature of oracle but that creates partition name with name like SYS*
We can use SQL Transformation to achieve this.
Suppose we have sales table created with list partitioning

create table sales (
        product_id      number,
        trans_amt       number,
        sales_dt        date,
        state_code      varchar2(2)
)
partition by list (state_code)
(
partition ct    values ('CT'),
partition ca    values ('CA')
);
Suppose we get every day data in sales file which get loaded into sales table but if data comes with other value than CT or CA for state code then load will fail with error error ORA-02149 Specified partition does not exist
We can create a SQL Transformation to achieve this



It can be achieve in below steps
Source is sales file and create a look up on all_tab_partitions view





Call this unconnected lookups from expression using below syntax
Chk_partition_exist = :lkp.LKP_ALL_TAB_PARTITIONS('SALES',state_code)

create a filter transformation with iif(isnull(Chk_partition_exist),TRUE,FALSE) so that data for   which partition does not exist will pass

Create SQL transformation like below




If  input data comes with new values of state code then it will automatically create partitions so that we will never see error ORA-02149 Specified partition does not exist again.


We will continue to cover more practical usages of SQL transformation in next article.
27
By
at 27 comments:
Labels: