Automatic Informatica Repository Backup

It is a common practice to take regular backup of Informatica repository contents, especially of Dev and Production instances so that any loss or corruption can be confronted later. But this backup activity has always been a manual headache for the administrator. If you are looking on how to get rid of the manual efforts and trying to automate the entire process, here is it. [Update 1: By process, it’s almost similar to my post on OBIEE web catalog backup which can be found here]

Strategy

The idea here is to create a shell/bash script (for Unix based) or a batch file (for Windows machines) in which you specify the path to store the backup repositories and also the username and password to connect to your Informatica domain. Eventually the script you create has to be scheduled to run as per your need. The following section would help you understand how.

The Linux/Unix Way

Write a shell script to connect to the Informatica repository service by specifying your repository name, username and password along with the domain. Let me show a sample.

#!/bin/bash

backupDirectory="/oracle/ETL_Weekly_BKP/";

backupDate=$(date +%F)

backupName=$backupDirectory"InfaRep-Dev-Bkp-"$backupDate".rep";

cd $backupDirectory;

pmrep connect -r DEV_RS -n Administrator -x Admin123 -d domain_infadev

pmrep backup -o $backupName –f

In the above snippet, you may notice the pmrep connect and pmrep backup commands which actually do the job for you. 

Explanation to the attributes follows.

-r             : Specifies that the repository name follows

-n            : To specify the username

-x            : To specify the password

-d            : To specify your domain

-o            : Output file name 

The Windows way

If you are looking to automate this activity in windows, write a batch file instead of a shell as against the previous case with the same functionality. Here is a sample one.

@ECHO OFF

SET backupDirectory=D:\\ETL_Weekly_Backup

SET backupDate=%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%

SET backupName=Infa_Dev_Backup_%backupDate%.rep

cd %backupDirectory%

C:\Informatica\9.5.1\clients\PowerCenterClient\client\bin\pmrep connect -r DEV_RS -n Administrator -x Admin123 -d domain_infadev

C:\Informatica\9.5.1\clients\PowerCenterClient\client\bin\pmrep backup -o $backupName –f

[For description on the pmrep arguments, please refer to the Linux/Unix section]

Auto-Delete Old Backups

As the script will keep creating backups periodically, we would end up having too many backups eating the disk space extensively though we require only one or two latest archives. Again manual intervention is needed to clean them up retaining only the latest ones. To avoid this, you may simply add another line to your script for deleting the old backups based on the desired retention duration.

If you opted for Unix scripting, you may add the following line at the end of your shell file.

 find <Backup Directory> -mtime +<days> -exec rm {} \;

This statement simply finds the files inside the <Backup Directory> whose last modified date is greater than the number of days specified by <days>. For example, if you want your backup to be retained only for 15 days, your script would look like:

find $backupDirectory -mtime +15 -exec rm {} \;

In case you take windows approach, you may add this statement.

forfiles -p <backup directory>  -m *.rep /D -<days> /C "cmd /c del @file"

Example: 

 forfiles -p %backupDirectory%  -m *.rep /D -15 /C "cmd /c del @file"

For more information on ‘forfiles’, you may check here.

Scheduling the Backup Process

With the script being ready for creating backups, the next step is to schedule and set trigger at the desired time and frequency. In Linux/Unix based machines, you can make use of “crontab” to schedule your utility. On a Windows, just a basic task in the “task scheduler” should serve the purpose. The following sections show you how, in case it sounds new to you.

On UNIX based machines

1. After creating the script, it has to be scheduled in the Unix server where the backup you intend to automate.
2. Login to the Unix server as the user with which the backups are to be taken and open the terminal shell.
3. Type the command: crontab –e
4. In the editor that opens, set the desired time, frequency of backup and the backup script as shown below.
5. If you would like to create a backup every Friday at 09:30 AM and the script you created is under /ETL_Weekly_BKP, the crontab entry should be something like this- 

        30 09 * * 5 /ETL_Weekly_BKP/InfaRepositoryBkp.sh

 

     6. Save and close the crontab editor. [To save and quit, use Esc :wq!]

For further reading or understanding on crontab, you may check here.

On Windows

1. Go to Task Scheduler
2. Create a task or basic task
3. Set the trigger with the desired frequency of backup. for example, 30 July, 2013.
4. Set the action to start the created batch file [InfaRepositoryBkp.bat]

There it is! Your Informatica repository will backed up automatically.

Sample Screens

 Windows Task Scheduler showing the scheduled trigger

Windows Task Scheduler with the batch file as the action 

OBIEE Error: [nQSError: 27023] Ambiguous Connection Pool

Problem

With OBIEE, sometimes when you attempt to create a new DB request on a database connection, you would receive the following error. 

error : Odbc driver returned an error (SQLExecDirectW).
error : State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27023] Ambiguous Connection Pool object: "Connection Pool". (HY000)
error : SQL Issued: {call NQSGetQueryColumnInfo('EXECUTE PHYSICAL CONNECTION POOL "Connection Pool"

 But at the same time, if you create an analysis/prompt/filter with tables that belong to the same connection pool, it would work fine.

Cause

 The cause of this error can be understood from the very statement of the error thrown - "Ambiguous Connection Pool". It simply means, you have two [or more] connection pools in the RPD with the same name that you are using to query the database and OBIEE is unable to find out which one to be used.

Solution

To resolve this issue, open your RPD file, rename the problematic Connection pool to something else or simply append ‘1’ to it. Now save the connection pool, create a fresh DB Request with this new name, bingo, there it works!

And if you still wonder why it doesn’t throw an error upon using the tables of the very same ambiguous connection pool, here is the reason. All tables you have in the presentation layer have a link back to the physical tables in the physical layer which will be mapped against its corresponding connection pool. So, even though there is more than one connection pool with same name, this lineage helps BI Server to figure out the right connection pool to be used which is not the case when you create a direct DB request.

Troubleshooting Informatica pmrep and pmcmd issues

Quite often we have people complaining about the errors thrown by Informatica pmrep and pmcmd commands and that they are able to connect to the Informatica repository from the client tools but unable to do so from the terminal or cmd prompt. The solution to this is pretty simple though. All that you need to blame is the environmental variables that have been set [or unset] in the machine.

Problem

Upon calling pmrep and pmcmd from the cmd prompt or terminal, you may end up receiving the following error.

$ pmrep

Could not load program pmrep:

        Dependent module libACE.so could not be loaded.

Could not load module libACE.so.

System error: No such file or directory

$ pmcmd

Could not load program pmcmd:

        Dependent module libpmasrt.a could not be loaded.

Could not load module libpmasrt.a.

System error: No such file or directory

Solution

Unix based machines

1. Check if the LIBPATH variable is set correctly.
The LIBPATH env variable must include “<INFA_HOME>/server/bin” path.
(You may check this by typing echo $LIBPATH in terminal window)

2. If the variable doesn’t include the above said path, set it in ‘.bash_profile’ [or  ‘.profile’ whichever is applicable based on the operating system]

3. Ensure PATH variable includes LIBPATH
The PATH variable should include either LIBPATH or “<INFA_HOME>/server/bin” path.

 

 4. Save and source the file.
       Type “source .bash_profile” [or “. .profile”]

5. Now type pmrep or pmcmd, it has to work.

Windows Machines

1. Check if the LIBPATH variable is set correctly.
The LIBPATH env variable must include “<INFA_HOME>/server/bin” path.

Or echo %LIBPATH% 

2. If the variable doesn’t include the path, set it as shown below.

3. Add “<INFA_HOME>/server/bin” to PATH variable as well if it’s not present already.

4. Now type pmrep or pmcmd and get going!