Data Warehouse Job Fail

[Anon Anon]

Dear All,

 

Currently we are on Clarity14.3 and trying to install Jaspersoft alongwith Data Warehouse.

We are able to set up dblink from database side using cmn_dblink_sp.

 

.ppm_dwh.cmn_dblink_sp 'PPMDBLINK','DB Server Host Name,Port' , 'Clarity Database Name' ,'ClarityDB Login', 'ClarityDBLogin Password'

We validated this dblink by executing below query which is giving correct output.

 

select  COUNT(1) from PPMDBLINK.ClarityDev.niku.srm_resources

 

We have created separate login for data warehouse with ppm_dwh.

When we are trying to configure from CSA, we are facing error regarding connectivity between database and data warehouse.It seems that CSA is ignoring the custom link which we have created in previous step using cmn_dblink_sp.

 

We checked NSA logs and found below error.

ERROR 2016-04-25 21:19:55,727 [http-nio-8090-exec-5] handlers.DWHDBLinkConnectionHandler (clarity:sa:3X/F9v1IsPbfAZWV263rUeBjBbTmctOTz4TYj/bjUiU0:nsa.properties.datawarehouse.save) Connection with the database could not be established with Database link

com.niku.union.persistence.PersistenceException:

SQL error code: 1326

Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Named Pipes Provider: Could not open a connection to SQL Server [1326].

 

ODBC is running fine from CSA server to DBserver.

Has anybody come across this error while configuring Data Warehousing.

 

Kind Regards,

Abhijit

 

 

 

 

 

 

 

 

Attachment(s)

NSA Screenshot.docx   59 KB 1 version

[Suman Pramanik]

Hi Abhijit,

 

Try creating the DB link at the database and then input the same in CSA as a work around.

 

Regards

Suman Pramanik

[Anon Anon]

Thanks Suman for the response.

We are already trying the same way. We have created DBlink from Database side only and we are mentioning that in NSA.

Still it is giving error regarding connectivity between DB and Data Warehouse.

Why application is not using the custom dblink as we have set the flag true in CSA.

 

Kind Regards,

Abhijit

[Suman Pramanik]

If you are using a DB link created from Database there is a flag in CSA which needs to be checked.

 

Custom Database Link

( Leave the field unchecked to create a database link. Check the value if you have already created a database link in the database. )

If its still throwing an error, looks at the CSA log as well as admin log.

 

Regards

Suman Pramanik

[Anon Anon]

Hello Suman,

Below is the screenshot of Data Warehouse configuration in the NSA. Can you please check if the parameters are correct.

PPM_DWH is the login created for data warehouse.

 

 

 

ERROR 2016-04-26 08:28:39,378 [http-nio-8090-exec-2]
handlers.DWHDBLinkConnectionHandler
(clarity:sa:8ZUTgs9cbIEd0+SrOa3cGsioMde6UeOBPWzuzikQis00:nsa.properties.datawarehouse.save)
Connection with the database could not be established with Database link

1. com.niku.union.persistence.PersistenceException:

SQL error code: 1326

Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Named
Pipes Provider: Could not open a connection to SQL Server [1326].

Executed:

SELECT count(1) dblinkvalid
from [PPMDBLINK].ClarityDev.niku.DWH_SETTINGS

Derived from statement:

<?xml version="1.0" encoding="UTF-8"?>

<statement
sortColumnPath="/data/header/sortInfo/@sortColumn"
sortDirectionPath="/data/header/sortInfo/@sortDirection"
slicePath="/data/header/pagination/sliceInfo/@slice"
sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize"
inputSource="map"
xmlns="http://schemas.niku.com/2002/pmd">

  <sql
dbVendor="all">

  
<text>@dynamic@</text>

    <param
direction="IN" dynamicReplacement="@dynamic@"
expressionListDelimiter=","/>

  </sql>

</statement>

 

Not sure why we are seeing network error as Clarity Database and Data Warehouse DB both are on the same SQL Server and Clarity App is able to talk with Clarity Database.

Admin Log is as below :-

4/25/16 7:51 PM (niku) execute: remote = false

4/25/16 7:51 PM (niku) execute: hosts.xml out of date

4/25/16 7:51 PM (niku) execute: Config Mgr setup

4/25/16 7:51 PM (niku) execute: read hosts.xml

4/25/16 7:52 PM (niku) execute: remote = false

4/25/16 7:52 PM (niku) execute: hosts.xml out of date

4/25/16 7:52 PM (niku) execute: Config Mgr setup

4/25/16 7:52 PM (niku) execute: read hosts.xml

4/25/16 7:58 PM (niku) execute: remote = false

4/25/16 7:58 PM (niku) execute: hosts.xml out of date

4/25/16 7:58 PM (niku) execute: Config Mgr setup

4/25/16 7:58 PM (niku) execute: read hosts.xml

4/25/16 7:58 PM (niku) execute: remote = false

4/25/16 7:58 PM (niku) execute: hosts.xml out of date

4/25/16 7:58 PM (niku) execute: Config Mgr setup

4/25/16 7:58 PM (niku) execute: read hosts.xml

4/25/16 7:58 PM (niku) Deploying service(s)...

4/25/16 7:58 PM (niku) Initialized...

4/25/16 7:58 PM (niku) Executing ant script

4/25/16 7:58 PM (niku) Executed ant script

4/25/16 7:58 PM (niku) Updating service: beacon

4/25/16 7:58 PM (niku) Updating windows service: CA Clarity PPM Beacon

4/25/16 7:58 PM (niku) Updated windows service: CA Clarity PPM Beacon

4/25/16 7:58 PM (niku) Done.

4/25/16 7:58 PM (niku) execute: remote = false

4/25/16 7:58 PM (niku) execute: hosts.xml out of date

4/25/16 7:58 PM (niku) execute: Config Mgr setup

4/25/16 7:58 PM (niku) execute: read hosts.xml

4/25/16 7:58 PM (niku) Deploying service(s)...

4/25/16 7:58 PM (niku) Initialized...

4/25/16 7:58 PM (niku) Executing ant script

4/25/16 7:59 PM (niku) Executed ant script

4/25/16 7:59 PM (niku) Updating service: nsa

4/25/16 7:59 PM (niku) Updating windows service: CA Clarity PPM System Admin Server

4/25/16 7:59 PM (niku) Updated windows service: CA Clarity PPM System Admin Server

4/25/16 7:59 PM (niku) Done.

[Anon Anon]

In addition to above mentioned stuff, one more thing noticed.

 

Once we run the configuration from CSA, DBLINK stops working.  If we execute query from database using dblink we see below error.

 

OLE DB provider "SQLNCLI11" for linked server "ppmdblink" returned message "Login timeout expired".

 

OLE DB provider "SQLNCLI11" for linked server "ppmdblink" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

 

Msg 1326, Level 16, State 1, Line 0

 

Named Pipes Provider: Could not open a connection to SQL Server [1326].

 

 

Whereas it was running fine initially as soon as we created dblink using stored procedure. It was returning output to below query.

 

SELECT  COUNT(1) from ppmdblink.ClarityDev.niku.srm_resources

 

This query was returning output before we configure the NSA or try to save DBLINK in NSA.

 

[Suman Pramanik]

Hi Abhijit,

 

You can try to delete the db link, create the same and then try to update the same in CSA if that is not done.

 

Regards

Suman Pramanik

[Anon Anon]

Thanks Suman.

We tried  but facing the same issue.

[Anon Anon]

Thanks Suman and Nika for the response.

We have both the database Clarity and Data Warehouse on the same SQL server.

Clarity is working fine and we are seeing this issue only with Data Warehouse.

I went through another link Data Warehouse Database Link Config Query  where the same issue was faced by CMCN1982.

In that link it is mentioned that issue got resolved after correct configuration on NSA.

Any hint in that direction will be helpful.

[Nika Hadzhikidi]

Hi Abhijit,

 

Here is your error message:

 

Named Pipes Provider: Could not open a connection to SQL Server [1326].

 

This is not a Clarity PPM, but a database MSSQL error. Please work with your DBA to resolve it. Here are some tips you could you use to start troubleshooting this:
https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-errors/

If you still have trouble I'd suggest your DBA raises a Support issue with Microsoft SQL server support.

 

Hope this helps -Nika

[Anon Anon]

Hello Suman/Nika,

Somehow we moved ahead and now facing the timeout issue with Load Data Warehouse access rights job.

Below is the error screenshot.

 

Any suggestion to get out of this issue.

Kind Regards,

Abhijit

[Nika Hadzhikidi]

Hi Abhijit

 

Great to hear you were able to resolve the MSSQL issue.

This is caused by two points:

 

1. First, the setting in CSA should be increased.
a. Please go to CSA - Server - Data Warehouse
b. Set the ETL timeout to 1200 instead of 600
c. Save and repeat for all services in cluster

2. Secondly, we have a known Clarity PPM defect, CLRT-77362 that causes this job to be slower on MSSQL database. This is fixed in 14.3.0.6, please apply this patch as well and try again.

 

I saw that you opened a Support issue : I am now going to be the owner and work directly with you to get this sorted out.

 

Thanks -Nika