Informatica Repository Query to Find All the Source and Target connections in Your Respository

The below Informatica repository query can be used to get the Source Connections, Target connections of all the instances used in the informatica.

Apart from that we have details of the Folder, mapping name ,session name , and the connection name as shown in Informatica.

SELECT DISTINCT 

C.SUBJECT_AREA FOLDER_NAME, 
C.MAPPING_NAME MAPPING_NAME, 
C.SESSION_NAME SESSION_NAME, 
E.READER_WRITER_TYPE, 
E.INSTANCE_NAME, 
E.CNX_NAME, 

CASE 
WHEN E.WIDGET_TYPE = 2 
THEN 'TARGET CONNECTION' 
ELSE 
CASE 
WHEN E.WIDGET_TYPE IN (1, 3, 56, 45, 55, 84) 
THEN 
'SOURCE CONNECTION' 
ELSE 
NULL 
END 
END CNX_TYPE 

FROM INFA_REPO.REP_VERSION_PROPS A, 
     INFA_REPO.REP_USERS B, 
     INFA_REPO.REP_LOAD_SESSIONS C, 
     INFA_REPO.REP_REPOSIT_INFO D, 
     INFA_REPO.REP_SESS_WIDGET_CNXS E 

WHERE ( A.USER_ID = B.USER_ID 
AND C.SESSION_ID <> D.REPOSITORY_ID 
AND C.SESSION_ID = A.OBJECT_ID 
AND C.SUBJECT_ID = A.SUBJECT_ID 
AND A.OBJECT_TYPE = 68 
AND C.SESSION_ID = E.SESSION_ID 
AND C.SESSION_VERSION_NUMBER = 
E.SESSION_VERSION_NUMBER)

Informatica Session Components Tab: Pre-session and Post-session Command

Pre-Session Command:

We can define Operating System Commands or  programs to be executed before the data loading process (Session Process) starts. Operating System commands or programs can be defined as Reusable Commands (defined in the form of   Command Tasks) or Non-reusable commands (defined with this property directly). Some of the uses of commands or programs can be:

• To enable/disable database users before data loading
• Make backup/copy of target tables so that old data can be restored in case of data loading fails
• To intimate users via Emails about the Data Loading success so that they can start analysis

Informatica Session: Sources and Targets Properties and Connection

Source Connection

Define source data connection for each source qualifiers. If the base tables for source qualifiers are from different databases then we should define any of the databases as connection database for all source qualifiers. We need to grant SELECT privilege on all the tables from different databases to the Connection Database. This task is done at Database level.

Target Connection

Select Relational Writer if the target is a Table else select File Writer if the target is a Text  File i.e. Flat File .If File Writer is selected then use 'Set file properties' button to define file structure like: Delimiter, Text Qualifier, etc and to give extra character instead of nulls in flat files.

Properties Page

If Relational Writer is selected then set the following properties:

• Target load type: Normal or Bulk
• Insert, if this option is Unchecked then target table can not receive New Records.
• Delete, if this option is Unchecked then target table does not allow deletion of records.

Informatica Session:Properties Tab

Below are the properties defined under "Informatica Session Properties tab".

• Enter session log file name (any name) 

• Define session log file directory using $PMSessionLogDir system variable OR define the complete folder path (/runbatch/session/test_session.log). 

Types of Tasks in Informatica

There are different types of tasks in Informatica Workflow manager which we use while running a workflow.I have listed it below.

Tasks	Description
Assignment	Used to assign a value to a workflow variable
Command	Used to run a shell command during the workflow
Control	Used to stop or abort the workflow
Decision	Tells a condition to evaluate
Email	Used to send email during the workflow
Event-Raise	Notifies the Event-Wait task that an event has occurred
Event-Wait	It waits for the event to completed in order to start the next task
Session	Used to run the mapping created in Designer buy linking to session
Timer	It waits for a already timed event to start

Session Parameters in Informatica

Session Parameter

Session parameters, like mapping parameters, represent values you might want to change between sessions, such as a database connection or source file. Use session parameters in the session properties, and then define the parameters in a parameter file. You can specify the parameter file for the session to use in the session properties. You can also specify it when you use pmcmd to start the session.The Workflow Manager provides one built-in session parameter, $PMSessionLogFile.With $PMSessionLogFile, you can change the name of the session log generated for the session.The Workflow Manager also allows you to create user-defined session parameters.

Naming Conventions for User-Defined Session Parameters
Parameter Type	Naming Convention
Database Connection	$DBConnectionName
Source File	$InputFileName
Target File	$OutputFileName
Lookup File	$LookupFileName
Reject File	$BadFileName

Use session parameters to make sessions more flexible. For example, you have the same type of transactional data written to two different databases, and you use the database connections TransDB1 and TransDB2 to connect to the databases. 
You want to use the same mapping for both tables. Instead of creating two sessions for the same mapping, you can create a database connection parameter, $DBConnectionSource, and use it as the source database connection for the session. When you create a parameter file for the session, you set $DBConnectionSource to TransDB1 and run the session. After the session completes, you set $DBConnectionSource to TransDB2 and run the session again.

You might use several session parameters together to make session management easier. For example, you might use source file and database connection parameters to configure a session to read data from different source files and write the results to different target databases. You can then use reject file parameters to write the session reject files to the target machine. You can use the session log parameter, $PMSessionLogFile, to write to different session logs in the target machine, as well.

When you use session parameters, you must define the parameters in the parameter file. Session parameters do not have default values. When the PowerCenter Server cannot find a value for a session parameter, it fails to initialize the session.