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

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