How to find the Informatica service Processes

Below are some of the process names you should be knowing while working on Informatica issues or checks.

1. java
2. _Adminconsole
3. pmrepagent
4. pmserver
5. pmdtm

1. Java and Adminconsole:

java process is the main process in informatica powercenter which starts the node. If the node is configured as Gateway node, then it will spawn another process which will run admin console.

Apart from node process, there are other java processes also runs if you have configured multiple services such as Metadata Manager Service, Model Repository service, Web Service hub etc,.

2. pmrepagent:

If you want to search for power center repository service process, then search for pmrepagent. If there are multiple processes, you need to check the logs in admin console for the respective PID.

3. pmserver:

pmserver refers to the Integration service process. If the Integration service runs on grid, you will find the process on all the nodes in grid. If it is configured as primary and secondary nodes, then it will be available on only one node.

4. pmdtm:

pmdtm is the process spawned by the workflow which is also called the session task processs. When you see number of pmdtm jobs running on server then all the process are running different sessions.


If you want to get updates on new posts, click the follow button below.

What are the checks to be performed before using pmrep deployment group?

The following are the commands to be run:

1. Login to Host1 and run the following:

cd $INFA_HOME/server/bin

./pmrep connect -r <rep_name> -d <domain_name> -n <username> -x <password> -sdn <Native/LDAP domain>

2. Login to Host2 and run  the following:

cd $INFA_HOME/server/bin

./pmrep connect -r <rep_name> -d <domain_name> -n <username> -x <password> -sdn <Native/LDAP domain> 

If the $INFA_HOME/server/bin is in PATH environment variables then you can the pmrep command from anywhere.

3. Check if the below objects are created if you are using dynamic deployment groups in source repository. 

• Label
• Query

4. Check if you are able to connect source repository from target host where you run the deployment or vice versa.

The username (and password) is the one that you use to connect to the repository through the power center clients (such as repository manager).

The sdn is the security domain which is either Native or the LDAP domain. (enter the same details as the one on the PowerCenter client).

The Connect command uses the following syntax:

pmrep help connect

-r <repository_name> {-d <domain_name> |

{-h <portal_host_name>

-o <portal_port_number>}}

[{ <user_name>

[-s <user_security_domain>] [-x <password> |

-X <password_environment_variable>]} |

-u <connect_without_user_in_kerberos_mode>] [-t <client_resilience>]

To use -X option configure INFA_DEFAULT_DOMAIN_PASSWORD on UNIX:

1. At the command line, type:

pmpasswd <password>

pmpasswd returns the encrypted password.

2. In a UNIX C shell environment, type:

setenv INFA_DEFAULT_DOMAIN_PASSWORD <encrypted password>

    In a UNIX Bourne shell environment, type:

export INFA_DEFAULT_DOMAIN_PASSWORD=<encrypted password> 

Informatica PowerCenter Repository Queries set-2

1: List of Workflows with Integration Service assigned:

SELECT SUBJECT_AREA as FOLDER_NAME, WORKFLOW_NAME, SERVER_NAME as INTEGRATION_SERVICE

FROM REP_WORKFLOWS

WHERE SERVER_NAME IS NOT NULL

Informatica PowerCenter Installation step by step

This article has the the complete steps to install the Informatica Powercenter product. This includes the server installation.

Below are the high-level steps:
1. Download the informatica package/zip file.
2. Unzip/Untar the file.
3. Run the installer.
     a. Configure the Domain
     b. Crete a Node.
     c. Configure the Repository service and Integration service.

Pre- Requirements:

Before you start the Installer, make sure you check the below steps.

1. Database has to be installed.
2. Create schemas. One for Domain contents and another one for Repository contents.
3. Grant the below Roles and Privileges.
     a. Roles - CONNECT, RESOURCE
     b. Privileges - CREATE TABLE, CREATE VIEW, CREATE ANY INDEX
     c. Also unlimited tablespace on the schemas. - GRANT UNLIMITED TABLESPACE ON <schema Name>;

Once the above requirements are verified, you can start the Installation.

Server Installation:

Step 1:

Informatica PowerCenter software can be downloaded from https://edelivery.oracle.com
Log on to https://edelivery.oracle.com and accept the Terms and Conditions to download the zip files.

Search and select the below product:
Oracle Informatica PowerCenter and PowerConnect Adapters.

Click on Continue. Accept the terms and conditions here and you can find the below files. Select which version you need. Here there are 2 versions 951 and 961. Click on Download to download the files.

Step 2:
Once download the below files, Unzip all the files.

Step 3:

Once the unzip done, the files will be available in extracted folders and right click on install.bat and select run the Installer as Administrator.

Step 4:
Select the "Install or Upgrade to Informatica 961 Hotfix 4" and click on Start button.

Step 5:
Select Install Informatica 961 HitFix 4 and click on Next.

Step 6:

Choose the licence file that will come with the downloaded zip file and provide the installation directory. This installation directory will be assigned to environment variable INFA_HOME. Click on Next.

Step 7:

On the next screen you will see the summary of settings for the Installation. Click on Install to begin the binary files installation.

Step 8:

The progress of the installation will be shown in the next window,

Step 9:

Once the installtion is 100% completed, it will configure the installed files. Once that is done, it will move to next screen.
Here you select "Create a Domain".
Check the "Enable Https for Informatica Administrator" and
Port 8443 as default. If you want you can change it to any other available port.
Select "Use a keystore Generated by the Installer"
Click on Next.

Step 10:

In the next screen provide the values for Database connection. This is the database details for the Informatica domain.
Once you provide the DB Type, DB User and password, select JDBC URL and enter the URL like hostname:port.
Once you provide the database details, click on "Test Connection". This should show you the successful message. If it is failing, please check the database details and enter it again.
Click on Next.

Step 11:

Enter any keyword (password) in the next screen for the "Keyword" field and leave the Encryption key directory as default. Click on Next.

Step 12:

In the next screen enter the domain details. (Domain Name, Node host name, Node Name, Node port number, Domain user name - Administrator and Enter the Password for Administrator account. This will the superuser for the Informatica server.
Check the tick box, "Display Advanced port configuration page" if you want to change the default ports and click on Next.

Step 13:

If Advanced Ports selected you will show you the advanced ports page. You can leave the default values and click on Next which will start defining the domain. This will start creating the domain and node.

Step 14:

Once the domain is defined, on the next screen, uncheck the "Run Informatica under a different user account". If you want to run the process under different account you can provide that details here. it is suggested to use the same account and don't select the option.
Click on Next.

Step 15:

Once the above step is completed, on the next window you can find the summary of the Post Installation which means the installation is completed. Use the URL for Informatica Administrator Home page to login into Admin Console.
Username -  Administrator
Password - <password you provided in the step 12.

PowerCenter Client Installation:

Once the server is Installed, you can install the client as well. Please follow the below link for client Installation.

Informatica Client Installation

Login Into Admin Console:

Once the server is installed you can try logging into Informatica Administrator Console. Follow the below link to know how to login into admin console.

Login Into Admin Console

Thank you.

Configure DataDirect ODBC on UNIX with PowerCenter

Do the following to install DataDirect ODBC drivers on UNIX: Install the Informatica Services which include the DataDirect ODBC drivers for UNIX. Set the ODBCHOME environment variable  > ODBCHOME=/opt/Informatica/9.6.1/ODBC7.1; export ODBCHOME Add the $ODBCHOME/bin directory to the PATH environment variable: Using a Bourne shell: $ PATH=${PATH}:$ODBCHOME/bin; export PATH Using a C shell: $ setenv PATH ${PATH}:$ODBCHOME/bin  Add the $ODBCHOME/lib to the library path variable. (OPTIONAL) Copy the odbc.ini file to the location where you would like this file to be used. Typically the Informatica home directory is used. cp $ODBCHOME/odbc.ini $HOME/.odbc.ini Set the ODBCINI environment variable to the location of the .odbc.ini (or odbc.ini) file:  $ ODBCINI=$HOME/.odbc.ini; export ODBCINI OR $ ODBCINI=$ODBCHOME/odbc.ini; export ODBCINI Edit the odbc.ini file (specified by the $ODBCINI environment variable) as follows: Set the InstallDir to the ODBC installation directory ($ODBCHOME). Re-Start the Informatica Node services.

Informatica Power centre “Java Heap Size” memory error issue

Error:

The following out of memory condition (found in the catalina.out) is an indication that the Java heap size needs to be increased:

Exception class: Exception class: java.lang.OutOfMemoryError
java.lang.OutOfMemoryError
Caught an exception in one of the PCSF or PCSF base servlet threads. See below for the brief stack trace

Fix:

1. Login to the server with the user which is used for Informatica server.

2. Goto $INFA_HOME/tomcat/bin

3. Take backup of the file infaservice.sh

4. Change the below value Xmx512m to Xmx1024m or whatever the required number for variable INFA_JAVA_OPTS

"INFA_JAVA_OPTS="${INFA_JAVA_OPTS} -Xmx1024m"

While previously it was :

"INFA_JAVA_OPTS="${INFA_JAVA_OPTS} -Xmx512m".

Re-start the Node services.

How to unlock Informatica Administrator account

You may be thinking that can we UNLOCK superuser account (Administrator) in Informatica without having the current password. The answer is YES we can do it. How to do it?

1. Shutdown all the nodes and services including the Domain.
2. Run the below command to unlock. 

infasetup.sh/bat UnlockUser -un Administrator -np admin123

Full Syntax:
infasetup.sh/bat UnlockUser 
<-UserName|-un> user_name
[<-SecurityDomain|-sdn] security domain]
[<-NewPassword|-np] new_password]

3. Once it runs successfully, start the domain and try to login with new password admin123. 

Migrating the Informatica Domain/Repository to Another Database

Migrating the Domain Configuration Repository to Another Database

1. Shut down all application services in the domain. 

2. Shut down the domain.

3. Back up the domain.

Run the infasetup BackupDomain command to back up the domain configuration to a binary file. By default,

infasetup installs in the server directory.

Use the following syntax:

infasetup.sh BackupDomain -da <database_hostname:database_port> -du <database_user_name> -dp <database_password> -dt <database_type> -ds <database_service_name> -bf <backup_file_name> -dn <domain_name>

4. Create a database schema and a user account in a supported database.

5. Restore the domain configuration backup to the database schema.

Run the infasetup RestoreDomain command to restore the domain configuration in the backup file to the specified database schema.

Use the following syntax:

infasetup.sh RestoreDomain -da <database_hostname:database_port> -du <database_user_name> -dp <database_password> -dt <database_type> -ds <database_service_name> -bf <backup_file_name>

6. Update the database connection information for each gateway node. (If both nodes are setup as gateway, you need to do this step for both nodes.)

Gateway nodes must have a connection to the domain configuration database to retrieve and update domain configuration. Run the infasetup UpdateGatewayNode command on each gateway node to update the database connection information for each gateway node.

Use the following syntax:

infasetup.sh UpdateGatewayNode -da <database_hostname:database_port> -du <database_user_name> –dp <database_password> -dt <database_type> -ds <database_service_name> -dn <domain_name>

7. Start all nodes in the domain. (Start the Informatica Service – infaservice.sh startup)

8. Enable all application services in the domain.

Migrating the PowerCenter Repository to Another Database

1. Back up the repository contents.

select the PowerCenter Repository Service in the Administrator tool, and then select Actions > Repository Contents > Back Up.

2. Disable the service.

3. Create a database schema and a user account in a supported database.

4. Update the database connection properties for the PowerCenter Repository Service, such as the database type, connect string, database user name, and database password.

5. Enable the service in exclusive mode.

6. Restore the repository backup to the database schema. select the PowerCenter Repository Service in the Administrator tool that manages the content that you want to restore, and then select Actions > Repository Contents > Restore.

7. Change the operating mode of the service to normal and restart the service.

Migrating the Data Analyzer Repository to Another Database

1. Back up the repository contents.

select the Reporting Service in the Administrator tool, and then select Actions > Repository Contents > Back Up.

2. Disable the service.

3. Create a database schema and a user account in a supported database.

4. Update the database connection properties for the Reporting Service, such as the database type, connect string, database user name, and database password.

5. Restore the repository backup to the database schema.

select the Reporting Service in the Administrator tool that manages the content that you want to restore, and then select Actions > Repository Contents > Restore.

6. Enable the service.

Informatica Powercenter Repository Queries set-1

Overview:
The below SQL queries are given to Informatica Development or Administration teams to see the metadata from Power center repository database. Please suffix your schema name if required with the table names.

1: To get the source and target connection objects information

SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME,
T.INSTANCE_NAME AS SESSION_NAME, T.TASK_TYPE_NAME,
C.CNX_NAME AS CONNECTION_NAME, V.CONNECTION_SUBTYPE, V.HOST_NAME,
V.USER_NAME, C.INSTANCE_NAME, C.READER_WRITER_TYPE,
C.SESS_EXTN_OBJECT_TYPE
FROM REP_TASK_INST T,
REP_SESS_WIDGET_CNXS C,
REP_WORKFLOWS WF,
V_IME_CONNECTION V
WHERE T.TASK_ID = C.SESSION_ID
AND WF.WORKFLOW_ID = T.WORKFLOW_ID
AND C.CNX_NAME = V.CONNECTION_NAME
--AND WF.SUBJECT_AREA = <FOLDER NAME>

2 : Check the master gateway node

select * from ISP_MASTER_ELECTION;

3: Check which Session has Target Table Truncate Option enabled:

select task_name,'Truncate Target Table' ATTR,
decode(attr_value,1,'Yes','No') Value
from OPB_EXTN_ATTR A, REP_ALL_TASKS B
where A.SESSION_ID=B.TASK_ID and attr_id=9

4: To Find the tracing levels of Powercenter Sessions:

select task_name,
decode (attr_value,
0,'None',
1,'Terse',
2,'Normal',
3,'Verbose Initialisation',
4,'Verbose Data','') Tracing_Level
from
REP_SESS_CONFIG_PARM A,
opb_task B
WHERE a.SESSION_ID=TSK.TASK_ID
and b.TASK_TYPE=68
and attr_id=204
and attr_type=6

5: Find all the Invalid workflows:

select subj_name, task_name
from opb_task a, opb_subject b
where task_type = 71
and is_valid = 0
and a.subj_id = b.subject_id
and UPPER(SUBJ_NAME) like UPPER('<FOLDER_NAME>')

6: List of Users and Groups having Folders Permissions:

SELECT b.subj_name folder_name, c.NAME "USER/GROUP NAME",
DECODE (a.user_type, 1, 'USER', 2, 'GROUP') TYPE,
CASE WHEN ((a.permissions - (a.user_id + 1)) IN (8, 16))THEN 'R--'
WHEN ((a.permissions - (a.user_id + 1)) IN (10, 20))THEN 'R-X'
WHEN ((a.permissions - (a.user_id + 1)) IN (12, 24))THEN 'RW-'
WHEN ((a.permissions - (a.user_id + 1)) IN (14, 28))THEN 'RWX'
ELSE 'NO PERMISSIONS'
END permissions,
CASE WHEN (a.user_id = b.owner_id and c.type = 1 ) THEN 'Y' ELSE 'N' END as Owner
FROM opb_object_access a, opb_subject b, opb_user_group c
WHERE a.object_type = 29
AND a.object_id = b.subj_id
AND a.user_id = c.ID
AND a.user_type = c.TYPE
order by 1,2,3

7: List of all Shared Folders:

SELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT WHERE IS_SHARED <>0 ORDER BY 1,2

8: List of all Repository Folders:

SELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT ORDER BY 1,2

9: List of Folder and their Owners with OS profiles:

SELECT SUBJ_NAME FOLDER_NAME, OS_USER OS_PROFILE, USER_NAME OWNER FROM OPB_SUBJECT A, REP_USERS B
WHERE A.OWNER_ID=B.USER_ID
ORDER BY 1

10: List of Workflows with NO Integration Service assigned:

SELECT SUBJECT_AREA as FOLDER_NAME, WORKFLOW_NAME, SERVER_NAME as INTEGRATION_SERVICE
FROM REP_WORKFLOWS
WHERE SERVER_NAME IS NULL

Informatica Powercenter Client Installation

This article shows you how install the Informatica power center client.

Pre-Requirements:

Unzip the downloaded file from the web. The client will come along with the same zip file in which you download the server package.

For more information how to download and install the informatica server refer the below link.

Informatica Powercenter server Installation

Below steps shows how to install the client.

Step 1:

Run the client installer as Administrator. Right click on install.bat and Run as Administrator.

Step 2:

Once the installer starts, below window will start. Select the option "Install or Upgrade Informatica" and click on Start button.

Step 3:

Select Informatica 961 Hotfix 4 Client option and click on Next.

Step 4:

The installation pre-requisites will be displayed. Verify and click on Next

Step 5:

Select all the client options and click on Next.

Step 6:

Select the directory ($INFA_HOME) where the client has to installed. You can select any folder where you have write access on any filesystem. Once done click on Next

Step 7:

Select the first option (Install Data Transformation Studio and a standalone copy of Eclipse) and clink on Next.

Step 8:

Review the settings and click on Install to proceed with the client installation.

Step 9:

The progress of the installation will be shown in the next window.

Step 10:

Once the above completed, it will show the Post installation summary and click on Done to complete the installation.

Once the installation is complete, you will be able to see the below client tools available in the start menu.

Thank you.

Login to Informatica Admin Console

This Article shows you how to login into Informatica Admin Console once the Informatica server is installed.

Before this we have installed the informatica server on our machine.

Step 1:

Goto start menu and search for "Informatica Administrator Home Page" 

Step 2:

Once the page is opened the home page will look like as shown below. Provide the username and password that you have set at the time of installation and click on Login.

Step 3:

Once you login by default it will open the domain overview screen which will show status of all the services created. In this i have only one Repository Service.

From the above screen, you can create the required services like Repository service, Integration Service etc... And also do all kind of admin activities.

How to Create Repository Service:

Follow the below youtube link to see how to create a Respository Service. 

Informatica PowerCenter Domain Status Checking

There are multiple ways to check the Domain Status.

1. From Command prompt:
1. Run the below command in UNIX/LINUX
   1. $INFA_HOME/isp/bin/infacmd.sh isp ping -dn <Domain_name>
2. Run the below command in Windows
1. change the directory to $INFA_HOME/isp/bin/. If the PATH variables are set to $INFA_HOME/server/bin you can run this command from any where.
1. infacmd.bat/sh ping -dn <Domain_name>
2. to check the admin console service status run the below command.  infacmd.bat/sh ping -dn <Domain_name> -sn _Admin_Console

1. From database:
   1. Connect to Domain database.
   2. Run the sql query : select * from ISP_MASTER_ELECTION; -- this will return you a record with the host and post details.
2. From Browser:
   1. Open the admin console URL. if the URL is opening then the domain is running.