ADMINISTRATION 

1. What is Load Manager?

The load Manager performs the following tasks

•  Manages session and batch scheduling.
•  Locks the session and read session properties.
•  Reads the parameter file.
•  Expand the server and session variables and parameters.
•  Verify permissions and privileges
•  Validate source and target code pages.
•  Create the session log file.
•  Create the Data Transformation Manager which executes the session.

2. What is DTM process? How many threads it creates to process data, explain each thread in brief?

After the load manager performs validations for the session, it creates the DTM process. The DTM process is the second process associated with the session run. The primary purpose of the DTM process is to create and manage threads that carry out the session tasks. The DTM allocates process memory for the session and divide it into buffers. This is also known as buffer memory. It creates the main thread, which is called the master thread. The master thread creates and manages all other threads. If we partition a session, the DTM creates a set of threads for each partition to allow concurrent processing. When Informatica server writes messages to the session log it includes
thread type and thread ID. Following are the types of threads that DTM creates:

•  MASTER THREAD – Main thread of the DTM process. Creates and manages all other threads.
•  MAPPING THREAD – One Thread to Each Session. Fetches Session and Mapping Information.
•  Pre and Post Session Thread – One Thread Each To Perform Pre and Post Session Operations.
•  READER THREAD – One Thread for Each Partition for Each Source Pipeline.
•  WRITER THREAD – One Thread for Each Partition If Target Exist in the Source pipeline Write to the Target.
•  TRANSFORMATION THREAD – One or More Transformation Thread For Each Partition.

3. Can you create a folder within designer?

Not possible

4. How do you take care of security using a repository manager?

•  Using repository privileges, folder permission and locking.
•  Repository privileges(Session operator, Use designer, Browse repository, Create session and batches,
• Administer repository, administer server, super user)
•  Folder permission(owner, groups, users)
•  Locking(Read, Write, Execute, Fetch, Save)

5. What are the different uses of a repository manager?

Repository manager used to create repository which contains metadata the Informatica uses to transform data from source to target. And also it use to create informatica users and folders and copy, backup and restore the repository
6. What are 2 modes of data movement in Informatica Server?

The data movement mode depends on whether Informatica Server should process single byte or multi-byte character data. This mode selection can affect the enforcement of code page relationships and code page validation in the Informatica Client and Server.

•  Unicode – IS allows 2 bytes for each character and uses additional byte for each non-ascii character
  (such as Japanese characters)
•  ASCII – IS holds all data in a single byte The IS data movement mode can be changed in the Informatica Server configuration parameters. This comes into effect once you restart the Informatica Server.

7. What is Code Page used for?

A code page contains the encoding to specify characters in a set of one or more languages. An encoding is the assignment of a number to a character in the character set. Code Page is used to identify characters that might be in different languages. If you are importing Japanese data into mapping, then u must select the Japanese code page for the source data.

8. What is Code Page Compatibility?

Compatibility between code pages is used for accurate data movement when the Informatica Sever runs in the Unicode data movement mode. If the code pages are identical, then there will not be any data loss. One code page can be a subset or superset of another. For accurate data movement, the target code page must be a superset of the source code page.
Superset – A code page is a superset of another code page when it contains the character encoded in the other code page. It also contains additional characters not contained in the other code page.
Subset – A code page is a subset of another code page when all characters in the code page are encoded in the other code page.
9. What is default block buffer size?
: 64K
10.What is default LM shared memory size?
: 2MB
11.Define Server Concepts with respect to memory buffers

The Informatica server used three system resources – CPU, Shared Memory & Buffer
Memory

Informatica server uses shared memory, buffer memory and cache memory for session information and to move data between session threads.
LM Shared Memory – Load Manager uses both process and shared memory. The LM keeps the information server list of sessions and batches, and the schedule queue in process memory. Once a session starts, the LM uses shared memory to store session details for the duration of the session run or session schedule. This shared memory appears as the configurable parameter (LMSharedMemory) and the server allots 2,000,000 bytes as default. This allows you to schedule or run approximately 10 sessions at one time.
DTM Buffer Memory – The DTM process allocates buffer memory to the session based on the DTM buffer poll size settings, in session properties. By default, it allocates 12,000,000 bytes of memory to the session.
DTM divides memory into buffer blocks as configured in the buffer block size settings. (Default: 64,000 bytesper block)

13.What are the two programs that communicate with the Informatica Server?

Informatica provides Server Manager and pmcmd programs to communicate with the Informatica Server:
Server Manager – A client application used to create and manage sessions and batches, and to monitor and stop the Informatica Server. You can use information provided through the Server Manager to troubleshoot sessions and improve session performance.
pmcmd – A command-line program that allows you to start and stop sessions and batches, stop the Informatica Server, and verify if the Informatica Server is running.

COMMAND LINE ARGUMENTS 

14. What is pmcmd commands?

pmcmd is a command line program to communicate with the Informatica server. This does not replace the server manager, since there are many tasks that you can perform only with server Manager.
These are some operations that you can do using PMCMD – Start, Stop and abort the session
15. What is pmrep commands?

You can use pmrep to create or delete repository users and groups. You can also use pmrep to modify repository privileges assigned to users and groups.
16. How do we start & stop session from pmcmd command line?

Use the following syntax to ping the Informatica Server on a UNIX system:
pmcmd ping [{user_name | %user_env_var} {password | %password_env_var}]
[hostname:]portno
Use the following syntax to start a session or batch on a UNIX system:
pmcmd start {user_name | %user_env_var} {password | %password_env_var}
[hostname:]portno [folder_name:]{session_name | batch_name}
[:pf=param_file] session_flag wait_flag
Use the following syntax to stop a session or batch on a UNIX system:
pmcmd stop {user_name | %user_env_var} {password | %password_env_var}
[hostname:]portno[folder_name:]{session_name | batch_name} session_flag
Use the following syntax to stop the Informatica Server on a UNIX system:
pmcmd stopserver {user_name | %user_env_var} {password | %password_
env_var} [hostname:]portno

Metadata Repository

17. Is there any metadata query to find the list of Informatica folder name, workflow names which are migrated in a particular Quarter?

The below SQL will give you the list of folders, workflows and their last saved date.
SELECT W.SUBJECT_AREA FOLDER_NAME, W.WORKFLOW_NAME, W.WORKFLOW_LAST_SAVED
FROM REP_WORKFLOWS W
ORDER BY TO_DATE (W.WORKFLOW_LAST_SAVED, ‘MM/DD/YYYY HH24:MI:SS’) DESC
18. How can I run Metadata Queries in Informatica PowerCenter?

Informatica metadata is stored in some database repository. This can be the same database where we have our source/ staging / target tables or it may be a completely different database (that is the case in general).
We can execute User defined queries metadata queries only on this database.
We may need to ask Informatica administrator about the database login credentials. We need to have a read access username/password for the database. After that we can connect to the database and run the metadata queries.
19. Write a metadata query to identify the sessions having truncate option enabled

select
task_name,
‘Truncate Target Table’ ATTR,
decode(attr_value,1,’Yes’,’No’) Value
from OPB_EXTN_ATTR OEA,
REP_ALL_TASKS RAT
where
OEA.SESSION_ID=rat.TASK_ID
and attr_id=9

20. Where can I find a history / metrics of the load sessions that have occurred in
Informatica?

The tables which house this information are OPB_LOAD_SESSION, OPB_SESSION_LOG, and OPB_SESS_TARG_LOG. OPB_LOAD_SESSION contains the single session entries, OPB_SESSION_LOG contains a historical log of all session runs that have taken place. OPB_SESS_TARG_LOG keeps track of the errors, and the target tables which have been loaded. Keep in mind these tables are tied together by Session_ID. If a session is deleted from OPB_LOAD_SESSION, it’s history is not necessarily deleted from OPB_SESSION_LOG,
nor from OPB_SESS_TARG_LOG. Unfortunately – this leaves un-identified session ID’s in these tables. However, when you can join them together, you can get the start and complete times from each session.

21. How to extract the workflow monitor record information from Informatica metadata repository?

SELECT DISTINCT
FOLDER_NAME, WORKFLOW_NAME, SESSION_NAME,
START_DATE, START_TIME, END_DATE, END_TIME, DURATION “DURATION IN
DD:HH:MI:SS”,
SOURCE_ROWS, TARGET_ROWS, REJECTED_ROWS, REJECTED_STATUS, STATUS,
FAILED_REASON
FROM
( SELECT
t.SUBJECT_AREA FOLDER_NAME, t.WORKFLOW_NAME, t.SESSION_NAME,
DECODE(t.RUN_STATUS_CODE, 2,NULL, TO_CHAR(t.ACTUAL_START,’DD-MON-YYYY’))
START_DATE,
DECODE(t.RUN_STATUS_CODE, 2,NULL, TO_CHAR(t.ACTUAL_START,’HH24:MI:SS
AM’)) START_TIME,
DECODE(t.RUN_STATUS_CODE, 2,NULL, TO_CHAR(t.SESSION_TIMESTAMP,’DD-MONYYYY’))
END_DATE,
DECODE(t.RUN_STATUS_CODE, 2,NULL, TO_CHAR(t.SESSION_TIMESTAMP,’HH24:MI:SS
PM’)) END_TIME,
DECODE(t.RUN_STATUS_CODE, 2,NULL, TRUNC((((86400*(SESSION_TIMESTAMPACTUAL_
START))/60)/60)/24)||’:’
|| (TRUNC(((86400*(SESSION_TIMESTAMP-ACTUAL_START))/60)/60) –
24*(TRUNC((((86400*(SESSION_TIMESTAMP-ACTUAL_START))/60)/60)/24)))||’:’
|| (TRUNC((86400*(SESSION_TIMESTAMP-ACTUAL_START))/60) –
60*(TRUNC(((86400*(SESSION_TIMESTAMP-ACTUAL_START))/60)/60))) ||’:’
|| (TRUNC(86400*(SESSION_TIMESTAMP-ACTUAL_START)) –
60*(TRUNC((86400*(SESSION_TIMESTAMP-ACTUAL_START))/60)))) DURATION ,
DECODE(t.RUN_STATUS_CODE, 2,NULL, t.SUCCESSFUL_SOURCE_ROWS) SOURCE_ROWS ,
DECODE(t.RUN_STATUS_CODE, 2,NULL, t.SUCCESSFUL_ROWS) TARGET_ROWS,
DECODE(t.RUN_STATUS_CODE, 2,NULL, t.FAILED_ROWS) REJECTED_ROWS,
DECODE(t.RUN_STATUS_CODE, 2,NULL,CASE WHEN t.SUCCESSFUL_SOURCE_ROWS <>
t.SUCCESSFUL_ROWS THEN ‘VALIDATE THE MISMATCH’ END) REJECTED_STATUS

DECODE(t.RUN_STATUS_CODE, 1,’Succeeded’, 2,’Disabled’, 3,’Failed’,
4,’Stopped’, 5,’Aborted’, 6,’Running’, 7,’Suspending’, 8,’Suspended’,
9,’Stopping’, 10,’Aborting’, 11,’Waiting’, 15,’Terminated’) AS STATUS,
REPLACE(REPLACE(t.FIRST_ERROR_MSG,CHR(10),’ ‘),’No errors encountered.’,”)
AS FAILED_REASON,
RANK() OVER (PARTITION BY session_name ORDER BY t.SESSION_TIMESTAMP DESC)
rnk
FROM REP_SESS_LOG t WHERE t.SUBJECT_AREA='<<informatica_folder_name>>’
) sess_run
WHERE sess_run.rnk = 1
ORDER BY START_DATE, START_TIME
Don’t forget to put the informatica folder name in the SUBJECT_AREA filter above. Also we might need to
make some other small adjustments above to better suit your purpose / informatica version.

REPOSITORY MANAGER

22 What is a Repository Manager?

23.What Type Of Repositories Can Be Created Using Informatica Repository Manager?

Informaica PowerCenter includeds following type of repositories :

Standalone Repository : A repository that functions individually and this is unrelated to any other repositories.

Global Repository : This is a centralized repository in a domain. This repository can contain shared objects across the repositories in a domain. The objects are shared through global shortcuts.

Local Repository : Local repository is within a domain and it’s not a global repository. Local repository can connect to a global repository using global shortcuts and can use objects in it’s shared folders.

Versioned Repository : This can either be local or global repository but it allows version control for the repository. A versioned repository can store multiple copies, or versions of an object. This features allows to efficiently develop, test and deploy metadata in the production environment

 Repository Manager

24.How to implement Security Measures using Repository manager?

 There are 3 ways to implement security measures.

They are:

• Folder Permission within owner, groups, and users.
• Locking (Read, Write, Retrieve, Save and Execute).
• Repository Privileges viz.
  • Browse Repository.
  • Use Workflow Manager(To create session and batches and set its properties).
  • Workflow Operator(To execute Session and batches).
  • Use Designer, Admin Repository(Allows any user to create and manage repository).
  • Admin User(Allows the user to create repository server and set its properties).
  • Super User(All the privileges are granted to the user).

25. Describe the steps for export and import?

•  Open the folder which contains the mapping.
•  Check Out the mapping to be exported.
•  Click Repository–>Export Objects and save it in your local drive.
•  Open the folder in which you want to export the mapping.
•  Click Repository–>Import Objects and select mapping xml file and Click import.
•  Once the mapping is imported to the new folder just save it and Check In.

26. What are the various methods of code migration or which is the best way of deployment?

The best way is, arguably, the XML export and import, as it is very easy.
But again it all depends upon the requirement; if we want to migrate some workflows with dependent objects at once shot, then the suggested way is XML export and import.
If you need to migrate only some small objects (say some designer or workflow manager objects) then we can go for copying through Repository Manager or through Designer(for Designer objects) or through Workflow manager (for Workflow manager objects) itself. But for this we have to be connected to both the repositories
while coping.
Sometime we may need to migrate entire project and want to have a complete log of deployment, then we can go for creating Deployment Group using Deployment Wizard.
We might use pmrep to automate exporting objects on a daily or weekly basis. To use this command, we must create a Control File with all the specifications that the Copy Wizard requires. The control file is an XML file defined by the depcntl.dtd file. A deployment control file is an XML file that you use with the DeployFolder and Deploy Deployment Group pmrep commands to deploy a folder or deployment group.
We can create a deployment control file manually to provide parameters for deployment, or you can create a deployment control file with the Copy Wizard. If you create the deployment control file manually, it must conform to the depcntl.dtd file that is installed with the PowerCenter Client. You include the location of the depcntl.dtd file in the deployment control file.
One good thing is we can roll back a deployment to purge the deployed versions from the target repository or folder. When we roll back a deployment, you roll back all the objects in a deployment group that we deployed at a specific date and time. We cannot roll back part of a deployment.
In the PowerCenter Client, we can export repository objects to an XML file and then import repository objects
from the XML file. Use the following client applications to export and import repository objects:

•  Repository Manager: You can export and import both Designer and Workflow Manager Objects.
•  Designer: You can export and import Designer objects.
•  Workflow Manager: You can export and import Workflow Manager objects.

pmrep: You can export and import both Designer and Workflow Manager objects. You might use
pmrep to automate exporting objects on a daily or weekly basis.
27. What are the various options for ETL code migration

There are couples of Options Available for Code migration.
If you have a Versioned Repository, as the first step Check in all the Workflows and dependent objects. Now we have Couple of different ways to achieve the migration.
Option 1. Now you can export the Workflow from Repository Manager using the Export Object Option to export as XML and then import into QA using Repository Manager Import Object Option.
Option 2. You can keep your Dev and QA is in the same Repo, you can just do the Drag and Drop option. For this Open Both Dev and QA Folders in Repository Manager and Just Drag the Objects from Dev to QA.
Option 3. You can Create a Deployment Group using Repository Manager and attach all the Workflows you need to migrate in the Deployment group and This Deployment group can be migrated Option 4. You have the Option to Migrate the Entire Folder As well
when we can Use these Options Option 1. We can use this Option when the number of Workflows to migrate is few. If you do not have Informatica Versioned Repository, These Exported XML can be used to keep your Versions.
Option 2. When you have less number of Workflows to Migrate you can use this option.
Option 3. Large number of Objects migrated together. It will keep the list of Objects migrated as a group and in case of a rollback is required it is easy in this approach.
Option 4. Mostly used when you migrate a Project for the first time to QA with a large number of workflows .
28. What is labeling in Informatica?

we can see label concept in many places like in our mail box. Some time we do group some of our mails to different level. Like marking some mails to personal level.
In Informatica, Label is a global object that you can associate with any versioned object or group of versioned objects in a repository. You may want to apply labels to versioned objects to achieve the following results:
– Track versioned objects during development.
– Improve query results.
– Associate groups of objects for deployment

 
– Associate groups of objects for import and export.
For example, you might apply a label to sources, targets, mappings, and sessions associated with a workflow so that you can deploy the workflow to another repository without breaking any dependency.
You can apply the label to multiple versions of an object. Or you can specify that you can apply the label to one version of the object.
You can create and modify labels in the Label Browser. From the Repository Manager, click Versioning > Labels  to browse for a label.
Informatica Version control is nothing but a team based development methodology where we create copies of the actual objects to tract the modification using check in and checkout options.
5. Suppose having Informatica Version Control in place, can we revert back an object to a
state of two previous version.
Answer:

•  From the Version History of the Object, open the required version of the Object in Workspace.
•  Next export the xml metadata of the Object.
•  Next Check out the Object.
•  Then import the metadata exported earlier.
•  Save and Check In the Object.

29. What do we mean by Team based development in Informatica?

Team based development is nothing but version control for the metadata objects.
If we have the team-based development option, we can enable version control for the repository. A versioned repository stores multiple versions of an object. Each version is a separate object with unique properties.
A PowerCenter version control feature allows us to efficiently develop, test, and deploy metadata into production.
During development, we can perform the following change management tasks to create and manage multiple versions of objects in the repository:

•  Check out and check in versioned objects.
•  Compare objects.
• Track changes to an object.
•  Delete or purge a version.
•  Use global objects such as queries, deployment groups, and labels to group versioned objects.

ADMINISTRATION 

1. WHAT IS LOAD MANAGER? 
2. WHAT IS DTM PROCESS? HOW MANY THREADS IT CREATES TO PROCESS DATA, EXPLAIN EACH THREAD IN BRIEF? 
3. CAN YOU CREATE A FOLDER WITHIN DESIGNER? 
4. HOW DO YOU TAKE CARE OF SECURITY USING A REPOSITORY MANAGER? 
5. WHAT ARE THE DIFFERENT USES OF A REPOSITORY MANAGER? 
6. WHAT ARE 2 MODES OF DATA MOVEMENT IN INFORMATICA SERVER? 
7. WHAT IS CODE PAGE USED FOR? 
8. WHAT IS CODE PAGE COMPATIBILITY? 
9. WHAT IS DEFAULT BLOCK BUFFER SIZE? 
10. WHAT IS DEFAULT LM SHARED MEMORY SIZE? 
11. DEFINE SERVER CONCEPTS WITH RESPECT TO MEMORY BUFFERS 
12. WHAT ARE THE TWO PROGRAMS THAT COMMUNICATE WITH THE INFORMATICA SERVER? 

COMMAND LINE ARGUMENTS

1. WHAT IS PMCMD COMMANDS? 
2. WHAT IS PMREP COMMANDS? 
3. HOW DO WE START & STOP SESSION FROM PMCMD COMMAND LINE?

Metadata Repository

1. IS THERE ANY METADATA QUERY TO FIND THE LIST OF INFORMATICA FOLDER NAME, WORKFLOW NAMES WHICH ARE MIGRATED IN
A PARTICULAR QUARTER? 
3. WRITE A METADATA QUERY TO IDENTIFY THE SESSIONS HAVING TRUNCATE OPTION ENABLED 
4. WHERE CAN I FIND A HISTORY / METRICS OF THE LOAD SESSIONS THAT HAVE OCCURRED IN INFORMATICA?
5. HOW TO EXTRACT THE WORKFLOW MONITOR RECORD INFORMATION FROM INFORMATICA METADATA REPOSITORY?

. REPOSITORY MANAGER

1. DESCRIBE THE STEPS FOR EXPORT AND IMPORT? 
2. WHAT ARE THE VARIOUS METHODS OF CODE MIGRATION OR WHICH IS THE BEST WAY OF DEPLOYMENT? 
3. WHAT ARE THE VARIOUS OPTIONS FOR ETL CODE MIGRATION 
4. WHAT IS LABELING IN INFORMATICA? 
5. SUPPOSE HAVING INFORMATICA VERSION CONTROL IN PLACE, CAN WE REVERT BACK AN OBJECT TO A STATE OF TWO PREVIOUS
VERSION. 
6. WHAT DO WE MEAN BY TEAM BASED DEVELOPMENT IN INFORMATICA?