Clarity

Hi All,

We would like to implement failover feature in JASPERSOFT to connect PPM_JASPERSOFT schema so the DB connect string format needs to be changed.

From: jdbc:oracle:thin:@DBSERVER:1521/SERVICENAME

To: jdbc:oracle:thin:@(DESCRIPTION=

(FAILOVER=ON)(LOAD_BALNCE=OFF)

(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

(HOST=DBSERVER)

(PORT=1521))(ADDRESS=(PROTOCOL=TCP)

(HOST=DBSERVER_FAILOVER)

(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=SERVICENAME)))

I know we can change it in the \Tomcat\webapps\reportservice\META-INF\content.xml

Please suggest any other place we need to change

Thanks

Giri

Hi Giri,

That would be one way of doing it but its not supported as you are directly making a change in the product. However I would request you to raise an idea so that we can have it in the product like the fail over mechanism is there in CA PPM.

Regards

Suman Pramanik

Thanks Suman for your response,

For NIKU & PPM_DWH schemas we can change the JDBC URL from CSA, but we don't have similar page to make changes for PPM_JASPERSOFT schema.

I raised a support ticket too.

Thanks

Giri

That's correct and that's the reason I asked to raise an Idea so that we can have it in the product for future. Changing the file system is not supported.

I will create an Idea. But I need to implement this failover connect string ASAP

Thanks

Giri

I am sorry I am not completely sure if changing the context.xml will help, if you can overcome this please post the resolution. While going through some forums I found this

Clustering / Load Balancing In Jasper Reports Server | DEFTeam Blog

Cluster Design Process | Jaspersoft Community

Regards

Suman

Hi Giri,

If you use Oracle RAC with no SCAN listener, then please do the following:

0. Backup your $JSTomcat\webapps\reportservice\META-INF\context.xml file.
1. Open the file $JSTomcat\webapps\reportservice\META-INF\context.xml with text editor
2. Modify the line for jdbc/jasperserver to specify the JDBC URL you need.
3. Save and restart the Jaspersoft Tomcat service

This should work for you.

For Oracle RAC servers with SCAN listener this is not required and the JDBC URL will remain containing one server only. The SCAN server will handle the failover behind the scenes.

Hope this helps.

Please let me know how it goes.

Kind Regards

Nika Hadzhikidi
CA Technologies
Principal Support Engineer

Thanks Nika,

We have tested this in DEV Environment. For Jaspersoft it worked fine.

We got problem with Data warehouse part..

DBA has given the format as

jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=OFF)(ADDRESS_LIST=(PROTOCOL=TCP)(HOST=DBSERVER)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=DBSERVER_FAILOVER)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SERVICENAME)))

I have 2 questions:

1. If I try to copy the same in NSA-->Database * Data Warehouse tabs it is in JDBC URL - It didn't work. I changed it as below. Is it ok?

jdbc:clarity:oracle://DBSERVER:1521;serviceName=SERVICENAME;BatchPerformanceWorkaround=true;InsensitiveResultSetBufferSize=0;

ServerTpe=dedicated;supportLinks=true;AlternateServers=(DBSERVER_FAILOVER:1521);FailoverMode=Select;ConnectionRetryCount=20;ConnectionRetryDelay=15;LoadBalancing=false

2. After DBA has switched to second server(DBSERVER_FAILOVER) - DB Link didn't work.

How to resolve this...

I asked DBA to create the DB Link manually and leaving the JDBC URL in Data Warehouse tab as it was earlier

Will it be ok?

Please suggest on these two items.

Thanks

Giri

Hi Giri,

Glad to hear it worked and the Jaspersoft JDBC is now corrected and working.

The JDBC URL in Clarity CSA is not generated by the same rules as in Jaspersoft context.xml. Different Data Direct driver syntax is used. So in case you want to configure your Datawarehouse JDBC, please just follow the rules stated in the Clarity PPM Installation Guide :
https://docops.ca.com/ca-ppm/14-3/administrating/ca-ppm-system-administration-csa/manage-clustering

Here is the example you should follow:

jdbc:clarity:oracle://server1:1521;ServiceName=serviceTNS;BatchPerformanceWorkaround=true;InsensitiveResultSetBufferSize=0;ServerType=dedicated;supportLinks=true;AlternateServers=(server2:1521;server3:1521);LoadBalancing=true

Normally you should not have an issue with your db link on this configuration. I would recommend you to try:

1. Configure the DWH database with Oracle RAC URL and failover server URL.
2. Save and restart all services
3. Go to CSA and recreate the DB link (by specifying a different db link name, and clicking on Save)

See if that works. Alternatively you could also manually delete and recreate the link with the help of your DBA. Please ensure that you specify the full dblink name (in case it has a domain) in CSA.

Please let me know how it goes.

Kind Regards

Nika Hadzhikidi
CA Technologies
Principal Support Engineer

Thanks Nika for the response.

We have tested the failover and worked successfully.

Below are the changes:

1. I used the format same as you mentioned except LoadBalancing=false - as my DBA's format is having LOAD_BALANCE=OFF.

2. While creating the DB Link from CSA, it is taking only Primary Server - I had to ask DBA to recreate the DB Link.

3. For some reason, Jaspersoft was showing available in the CSA but it was throwing error - Could not login to https://servername:8443/reportservice with username ppmjasperadmin. I regenerated keystore files and copied into Jaspersoft Server to resolve the error.

Thanks

Giri