Friday, August 7, 2009

How to change the connection pool passwords with each migration?

I have been depending on my DBA to type the passwords in the connection pools every time we have an RPD that needs to be migrated to pre-production or Production. It is not much of an effort but it is just nuisance to bring him to my desk and let him type the password while I turn my head away.

I have been thinking it can be done by UDML and even heard from a colleague who has successfully done it but never really had all that time to try it myself. My colleague, Pradip, gave me some pointers on this and I thank him for that. Today, I got sometime to try it out for myself and I certify that it works best.

I had RPD with pre-production connections and new RPD with development database connections that need to be moved to pre-production. First, I copied the connection pool from my pre-production RPD and pasted in a notepad. Right click on the connection pool and click Copy and then Paste in a notepad. Just a reminder - Copy and Paste menu items in RPD are actually UDML generator and UDML executor respectively.

Once the UDML is pasted in notepad, you will see the password, user name and all the other connection pool properties. You can add all connection pools to notepad, if you have more than one connection pool that needs to be changed with every migration. Once done, run the below command at command line (you should have OracleBI client installed on the machine where you execute the command).

D:\OracleBI\server\Bin>nQUDMLExec.exe -U Administrator -P SADMIN -I D:\UDML_Stg.txt -B "D:\Repository\OracleBIAnalyticsApps_Dev.rpd" -O "D:\Repository\OracleBIAnalyticsApps_Stg.rpd"

Input RPD - D:\Repository\OracleBIAnalyticsApps_Dev.rpd
Output RPD - D:\Repository\OracleBIAnalyticsApps_Stg.rpd

Once the command is fully executed, you should find output RPD with changed connections. You can specify any path you may wish to have the RPD saved.

Contents of D:\UDML_Stg.txt is -
-------------------------------------------------------------------------------
DECLARE CONNECTION POOL "Oracle Data Warehouse"."Oracle Data Warehouse Connection Pool" AS "Oracle Data Warehouse Connection Pool" UPGRADE ID 2327604
DATA SOURCE {VALUEOF(OLAP_DSN)}
TIME OUT 600
MAX CONNECTIONS 100
TYPE 'Default'
USER 'VALUEOF(OLAP_USER)'
PASSWORD 'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54E960C7A19ECF4EFE0BDF057F3860BD2C8F3BEE6EAF9FC34'
SHARED LOGIN
ASYNCHRONOUS
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
HEADER PATH {D:\\OracleBI\\server\\config\\NQSQueryHeader.xml}
TRAILER PATH {D:\\OracleBI\\server\\config\\NQSQueryTrailer.xml}
ISOLATION LEVEL DIRTY READ
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
DESCRIPTION {User name is VALUEOF(OLAP_USER)
Data source name is VALUEOF(OLAP_DSN)}
PRIVILEGES ( READ);
-----------------------------------------------------------------------------

3 comments:

Anonymous said...

Hi Changa,
It is great, As you mentioned I've tried to change the connection pool setting, once I ran the below comment in the comment prompt, I got following results.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\

C:\>OracleBI\server\Bin\nQUDMLExec.exe -U Administrator -P ADMIN -I C:\POOL\UDML_STG.txt -B "C:\POOL\DEV_MERGED.rpd" -O "C:\POOL\UAT.rpd"

---------------C:\POOL\UDML_STG.txt---------------

Complete success!!!

C:\>


But I could not see any changes in my RPD connection pools or UDML_STG.text. I may be understood wrong, could please explain for my understanding once again?

Kvin

Changa Reddy said...

Hi Kvin,

This doesn't change existing RPD or UDML_STG.txt file. You should find a new RPD - "C:\POOL\UAT.rpd" that has the connection pools modified. I hope, you followed the steps to create UDML_STG.txt by copying connection pool values that you want in the target RPD.

Regards,
Changa

Unknown said...

Hi Changa,

It's very useful and thaxs for posting.

I've got a Requirement where in I need to Create Groups and Users from a flat file to RPD using nQUDMLEXEC.exeutility.
I've followed all the steps and executed successfully but where in I could not able to see the Users and Grioups which is there from the Flatfile.
this is a High priority requirement and I've debugged it several ways but of no use.

Please Advise me.

regards
swapna