Details About Informatica Stored Procedure Properties

Stored Procedure is nothing but collection of SQL/PLSQL statements which can be executed in database.

Once Stored procedure was created, then we can call it in informatica by using Stored Procedure transformation.

• Stored Procedure is a Passive Transformation.
• It can be used as both connected and unconnected mode.

 Stored Procedure Type:

we can set the stored type as below

• Normal
  If stored procedure type is normal means, it is a connected procedure and it will expect input from other transformations and return the out put to other transformations or will be called in other transformations.

• Source Pre Load
  If we set the stored procedure type as source pre load, integration service will execute the procedure in source database before ETL load starts. In other words we can say before executing Source Qualifier, procedure will be executed without returning any value.
   

• Source Post Load
  It is also work like as Source pre load but after Source qualifer query execution, informatica integration service will execute the procedure in source data base.
  

• Target Pre Load
  if we choose stored procedure type as sTarget pre load, integration service will execute procedure in target database before the target load is about to start. Work like as Target Pre SQL.
• Target Post Load
  If strored procedure type is Target post load, informatica integration service will execute the procedure in target database after data load completed into target.

Different Type of Lookup Caches in Informatica

Cache: Cache is nothing but collection of data stored in a place.

There are mainly 4 types of lookup caches in informatica.

• Static Lookup Cache
• Dynamic Lookup Cache
• Persistent Lookup Cache
• Shared lookup Cache 

Static Lookup Cache:

When session execution started, static lookup cache will be created by integration service. Once the static lookup cache was build, then integration service will query the data from lookup cache instead of database.If the lookup condition is true then static lookup cache will return the data otherwise it will return null.

The main advantage is performance, because if once the cache is build then informatica never try to hit the database to fetch the data from lookup table. So execution time will reduce.

As well as the main disadvantage is, after that cache build any changes happened in lookup table those changes will not be reflected in static cache.In this case we need to rebuild the cache.

Once the session execution completed static lookup cache will automatically destroyed.

Dynamic Lookup cache:    

When we choose dynamic lookup cache option in session level properties, the existing cache will be updated when the lookup table updated. That means any inserts or updates happened on target table, same changes will reflect in dynamic lookup cache.

In other words simply we can say, dynamic lookup cache will always be in synchronization with lookup table.

Dynamic cache will also be destroyed when the session execution completed.

Persistent Lookup Cache: 

If we select persistent cache in session level properties, infromatica integration server will create the cache file for the lookup table, so it can be used in next time. So this cache can be reused by providing the cache file name in session level properties. Here integration service first will check the cache file existence, if file is already existed then it will use the file otherwise it will create a new file.

It will be very helpful when lookup table data is static.

Integration service won't delete the cache file even after successful session execution.


Shared lookup Cache: 


We can share the persistent cache to other lookups by providing the cache name and cache location. 

How to kill the session in Oracle

As we know, while  performing any DDL and DML operations on oracle table, oracle database puts the lock on corresponding table, after successful query execution it will automatically release the lock on that table.

If we interrupt the query execution, the lock still remains on the table. So we can't perform any DML and DDL operation on the same table.

So before we are going to perform any DML or DDL operations, we should kill the session to unlock the table. Below is the procedure to kill session/unlock table.

first identify the object id for your table by using following query.   
SELECT * FROM dba_objects WHERE object_name='EMP';

in my case OBJECT_ID is 83603.

Now check the lock, if exist.

SELECT sid FROM v$lock WHERE id1=83603;

in my case sid value is 3341

if the above query didn't return any value means, there is no lock on that table.

SELECT sid, serial# FROM v$session WHERE sid=3341;

here sid=3341 and serial#=226371


Now we need to kill the session by using above values.

ALTER SYSTEM KILL SESSION '3341,226371' ;

once the session was killed, we can perform any type of valid operation on oracle table.