Clarity

Expand all | Collapse all

Data Warehouse Database Link Config Query

Suman Pramanik

Suman PramanikApr 10, 2015 10:11 AM

Suman Pramanik

Suman PramanikApr 10, 2015 10:50 AM

  • 1.  Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 08:16 AM

    Hi guys,

     

    I have imported the base dwh image and restored to database ppm_dwh.

    When I try to set up the dblink I get an error:

     

    "Failed to update properties on the Jaspersoft server. Could not login to http://server:8080/reportservice with username ppmjasperadmin"

     

    Data Warehouse.PNG

     

    Can anyone please advise what I need to do? Thanks.



  • 2.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 10, 2015 08:32 AM

    Hi CMCN1982,

     

    The dataware house db link and Jaspersoft error is different. Can you please confirm that you have given the below rights

     

    MSSQL:

    GRANT ALTER ANY LOGIN TO PPM_DWH

    GRANT ALTER ANY LINKED SERVER TO PPM_DWH

     

    Common procedures can be used to create DBLINK for both Microsoft SQL and Oracle from CA Clarity PPM. The procedures can be called directly from the Data Warehouse schema if necessary.

     

    MSSQL:

    CMN_DBLINK_SP @P_DB_LINK_NAME VARCHAR(30),

    @P_CLARITY_DB_HOST_NAME VARCHAR(30),

    @P_CLARITY_SCHEMA_NAME VARCHAR(30),

    @P_CLARITY_USER VARCHAR(30),

    @P_CLARITY_PASSWD VARCHAR(30)

     

    Example: Microsoft SQL DBLINK Creation

    CMN_DBLINK_SP 'PPMDBLINK','<hostname>','NIKU','NIKU','NIKU'

     

    Troubleshooting steps

     

    MSSQL

    Follow these steps:

    1. Verify that the following privileges are granted to the PPM_DWH schema:

    GRANT ALTER ANY LOGIN TO PPM_DWH

    GRANT ALTER ANY LINKED SERVER TO PPM_DWH

    2. To verify that PPMDBLINK is working, run the following query from the PPM_DWH schema:

    SELECT count(1) FROM PPMDBLINK.niku.niku.srm_resources

    If the link works, data is returned.

     

    Regards

    Suman Pramanik



  • 3.  Re: Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 08:51 AM

    Thank you Suman,

     

    I granted the ALTER ANY LOGIN and ALTER ANY LINKED SERVER rights to PPM_DWH

     

    CMN_DBLINK_SP @P_DB_LINK_NAME VARCHAR(30),

    @P_CLARITY_DB_HOST_NAME VARCHAR(30),

    @P_CLARITY_SCHEMA_NAME VARCHAR(30),

    @P_CLARITY_USER VARCHAR(30),

    @P_CLARITY_PASSWD VARCHAR(30)

     

    returns an error:

    Must declare the scalar variable "@P_DB_LINK_NAME"

     

    and

     

    CMN_DBLINK_SP 'PPMDBLINK','<hostname>','NIKU','NIKU','NIKU'


    returns:

    Could not find stored procedure 'CMN_DBLINK_SP'


    Can you please advise?




  • 4.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 10, 2015 10:11 AM

    Did the import worked fine?

     

    Regards

    Suman



  • 5.  Re: Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 10:46 AM

    I deleted my first attempt and started again.

     

    The import and restore of ppm_dwh has completed successfully.

    Now I am at the setup login name stage.

     

    "Set up a valid login name for use by CA Clarity PPM. This login name and password are the values that you specify in CA Clarity System Administration to log in to the database. The login name and password can be any combination. Set the Microsoft SQL Server Database Schema name as niku. This name must be the Default Schema for the login user you created in the Microsoft SQL Server."

     

    So Im going to Security -> Logins -> New Login

    Login name: ppm_dwh

    SQL Server Authentication

     

    chose password.

     

    default database: should this be ppm_dwh or master?

     

    Where do I set the sql server database schema as niku>



  • 6.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 10, 2015 10:50 AM

    It should be DWH


    Regards

    Suman



  • 7.  Re: Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 10:59 AM

    Ok, and where do I "set the Microsoft SQL Server Database Schema name as niku. This name must be the Default Schema for the login user you created in the Microsoft SQL Server." ?



  • 8.  Re: Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 11:24 AM

    I should probably add that I have a separate server from my Clarity db.



  • 9.  Re: Data Warehouse Database Link Config Query

    Posted Apr 13, 2015 12:00 PM

    Hi guys,

     

    has anyone else run into this problem?

     

    I have granted all the necessary access to ppm_dwh

    but when I try to set up my ppmdblink I cannot get past this:

     

    CMN_DBLINK_SP @P_DB_LINK_NAME VARCHAR(30),

    @P_CLARITY_DB_HOST_NAME VARCHAR(30),

    @P_CLARITY_SCHEMA_NAME VARCHAR(30),

    @P_CLARITY_USER VARCHAR(30),

    @P_CLARITY_PASSWD VARCHAR(30)

     

    Msg 137, Level 15, State 2, Line 1

    Must declare the scalar variable "@P_DB_LINK_NAME".



  • 10.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 13, 2015 02:37 PM

    Hi cmcn1982,

     

    This query will not run because none of the variables are declared, this is normal. In fact, this is just an example of the query and the parameters you have to supply prior to running it in the Installation Guide. If you look further, here is the example:

     

    Example: Microsoft SQL DBLINK Creation

    CMN_DBLINK_SP 'PPMDBLINK','<hostname>','NIKU','NIKU','NIKU'

     

     

    So you have just to supply the correct parameters for it as per the example and run it on your DWH database,  and this should work fine. Keep in mind hostname is db hostname for Clarity, schema, user and password are also for Clarity, and your schema might be different from niku.

     

     

    Once the link creates fine, run this query to check if the link is working (provided your schema name is niku, otherwise please replace the first niku reference):

    SELECT count(1) FROM PPMDBLINK.niku.niku.srm_resources

     

    Please let me know how it goes.

     

    Kind Regards

     

    Nika Hadzhikidi

    CA Technologies

    Principal Support Engineer



  • 11.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 06:29 AM

    Thank you Nika.

     

    So if I understand correctly my command will look like this:

     

    CMN_DBLINK_SP 'PPMDBLINK','MyServerName','ppm_dwh','ppm_dwh','MyPassword'

     

    when I run this however I get:

     

    "Could not find stored procedure 'CMN_DBLINK_SP'"



  • 12.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 10:02 AM

    When I do a search for all stored procedures in the ppm_dwh database however, the cmn_dblink_sp stored procedure is listed.

     

    cmn_dblink_sp.PNG



  • 13.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 10:13 AM

    I suspect your Microsoft SQL Server installation does not have the correct server level collation; it is meant to be a case-insensitive collation setting (we in fact only support one collation, SQL_Latin1_General_CP1_CI_AS):  CA PPM 14.2 On Premise Installation Guide (restricted content): Configure SQL Server

     

    When you have a case sensitive server-level collation, it applies to the 'identifiers' in the database too (e.g. function names, stored procedures, and even table/column names, and some cross-database scoped variables), it isn't just a data setting.



  • 14.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 10:35 AM

    Thank you Nick.

     

    I checked the server collation and it appears to be ok:

     

    Server Collation.png

     

     

    Is there anything else I should be looking for?



  • 15.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 14, 2015 10:43 AM

    Hi CMCN1982,

     

    Not exactly, it's actually the Clarity database you would specify, example:

     

    CMN_DBLINK_SP 'PPMDBLINK','MyServerName','niku','niku','MyPassword'

     

    You basically have to create the link on ppm_dwh database to niku database. Since your database collation appears to be correct, could you please check you run it on the correct database. You have to run this statement on ppm_dwh, connected as ppm_dwh.

     

     

    Kind Regards

     

    Nika Hadzhikidi

    CA Technologies

    Principal Support Engineer



  • 16.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 11:01 AM

    Thank you for your patience Nika!

     

    I think I have it now.

     

    I have a separate server (I'll call it new_server here) from my database server (old_server)

     

    So in my command should I be pointing at my old_server or my new_server ?

    And I guess it will be the niku password on my db server I should use?



  • 17.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 14, 2015 11:28 AM

    Hi CMCN1982,

     

    Let's be more precise and say we need to link new database (DWH)  to the existing database Niku (Clarity). So you should use Clarity server hostname, schema, user and password as parameters, and execute the command on DWH database whilst connected with ppm_dwh user.

     

    Kind Regards

     

    Nika Hadzhikidi

    CA Technologies

    Principal Support Engineer



  • 18.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 11:50 AM

    Success! Almost!

     

    Ok so I think I have my dwh communicating with my db now.

     

    I ran the script to test the link and got results back:

    results.PNG

    In the NSA, when I try to save my parameters the status will not change to 'Available' however. Even though it would appear that my dwh and db are communicating?

     

    My port is 1433 and is definitely open.

    I believe my parameters are correct also.

     

    Vendor: SQL

    Login Name: I set this as ppm_dwh, the account is unlocked and password is ok.

     

    Hostname: I tried both my data warehouse server name and its IP here.

     

    Database name: Should this be the name of my Clarity db or my data warehouse db (ppm_dwh)

     

    Service name: ?



  • 19.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 11:58 AM

    I think I might need to set up an ODBC connection between my dwh server and my Clarity db server? Whatever I call this is what will be populated in the service name above?



  • 20.  Re: Data Warehouse Database Link Config Query
    Best Answer

    Posted Apr 14, 2015 12:39 PM

    I'm glad it's not the collation problem, firstly.

     

    An ODBC connection shouldn't be needed, if you check the contents of the cmn_dblink_sp then the procedure and parameters it uses to create the link is being done specifically with the 'sqlncli' provider (SQL Native Interface), it's not using an ODBC driven provider.

     

    So the parameters are (in order):

    1. A personal 'name' or reference for your link (e.g. 'PPMDBLINK')

    2. The hostname of the db server where your main 'niku' schema for Clarity resides (this has to be a hostname that your DWH database server recognises and can resolve).

    3. Clarity schema name of the db server from step 2 (which will be 'niku').

    4. The username / login of the db server from step 3

    5. The password for logging in as the user in step 4.



  • 21.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 05:59 AM

    Thank you so much for all of your assistance guys.

    In my NSA I now see my DHW Status as available and my PPMDBLINK appears to be working.



  • 22.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 06:15 AM

    May have spoken too soon!

     

    OK - so I was able to run CMN_DBLINK_SP 'PPMDBLINK','ClarityDbServer','NIKU','NIKU','password'

    against the ppm_dwh database whilst logged in as the ppm_dwh login user

     

    This completed successfully.

    However when I tried to verify using - SELECT count(1) FROM SRM_RESOURCES@PPMDBLINK

     

    I get the following: -

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'SRM_RESOURCES@PPMDBLINK'.

     

    This is what's confusing though - when I went to my NSA and populated the parameters as Nick suggested my status has actually changed to 'Available'.

    But, when I save I still get a connection type error - NSA-0132: Database link could not be created successfully. Please verify the Database and Data Warehouse connection details.

     

    So it would appear that my dwh and db are not communicating properly just yet.

     

     



  • 23.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 07:16 AM

    OK, some further progress.

     

    I created the DBLink OK, and now when I run the verification check I no longer get an error, "

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'SRM_RESOURCES@PPMDBLINK'."

     

    When I log into my NSA and look at the Data Warehouse Tab it says the status is 'Available'

     

    Again though, when I chose Save it tells me that - NSA-0132: Database link could not be created successfully. Please verify the Database and Data Warehouse connection details.

     

    I'm unsure as to whether the DBLink is set up correctly or not!



  • 24.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 09:59 AM

    How about: select count(1) from niku.SRM_RESOURCES@PPMDBLINK

     

    I haven't given this a try myself yet, but just suspecting since the dwh database user's default schema isn't going to be 'niku', that you may have to qualify it explicitly.



  • 25.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 15, 2015 10:41 AM

    Hi CMCN1982,

     

    It looks like now your link is set up properly, since the results are returned. It might be something else that causes the message. Please check the following permissions have been granted to ppm_dwh user:


    GRANT VIEW SERVER STATE to PPM_DWH
    GRANT ALTER ANY LOGIN TO PPM_DWH
    GRANT ALTER ANY LINKED SERVER TO PPM_DWH

     

    Then please retry and let me know if it works. If it does not, I see you have opened a Support case : we can set up a webex to resolve this further on a call.

     

     

    Kind Regards

     

    Nika Hadzhikidi
    CA Technologies
    Principal Support Engineer



  • 26.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 10:45 AM

    Thank you Nika.

     

    The ppm_dwh user definitely has those permissions applied. It's strange as I say because data is being returned, but when I choose Save in NSA it's telling me that the link could not be created successfully.

     

    Can we please set up a webex session? I feel that it is probably just some small issue that could be resolved quicker over a phone call. Thank you!



  • 27.  Re: Data Warehouse Database Link Config Query

    Posted Apr 16, 2015 08:58 AM

    Thanks to Nika for helping me to resolve this!

     

    DWH is now showing as Available and when I choose Save there are no error messages.

    Some of my parameters were incorrect in NSA and once we corrected those everything was ok.

     

    Thanks again to all for your help with this one,



  • 28.  Re: Data Warehouse Database Link Config Query

    Posted Aug 31, 2015 11:35 AM

    Nika,

     

    I am having the exact same issue and have performed everything in this thread.  Still to no avail.  What were the settings that needed adjusting in the NSA?



  • 29.  Re: Data Warehouse Database Link Config Query

    Posted Aug 31, 2015 11:47 AM

    I had the same problem creating the DBLink from CSA.

     

    As workaround I created the DBLINK directly on the database with the scripts from the installation manual.

     

    After that, just use the same DBLINK name previously created.

     

    Hope it helps.



  • 30.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Aug 31, 2015 11:57 AM

    Hi :

     

    if DWH database shows as not Available in CSA, this will be due to the database connection, not the dblink. In this case, ensure you enter the correct parameters to connect to the database. Here is an example screenshot:

     

    You have to specify the user login ppm_dwh, check if that's what you are entering.

     

    Kind Regards



  • 31.  Re: Data Warehouse Database Link Config Query

    Posted Aug 31, 2015 12:03 PM

    I am showing available in both the Reporting and Data Warehouse environments.

     

    The log file says can’t find CMN_DBLINK_SP.  I have validated it is there and ran the command as PPM_DWH just like it says in this thread.  It says it completes successfully, but I can’t get any results when I use the select count (1) command.



  • 32.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Aug 31, 2015 12:57 PM

    Okay let's see : so if you run this query on the DWH database:

    SELECT count(1) FROM PPMDBLINK.niku.niku.srm_resources

     

    then you have no results? Or do you have any error message? Please note that the first 'niku' is your actual database name, so it can vary if you set up something else.

     

    Kind Regards



  • 33.  Re: Data Warehouse Database Link Config Query

    Posted Aug 31, 2015 01:12 PM

    Received this error:

     

    OLE DB provider “SQLNCLI10 for linked server “PPMDBLINK” returned message “Login timeout”

    OLE DB provider “SQLNCLI10 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.”

    Msg 53, Level 15, State 1, Line 0

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



  • 34.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Aug 31, 2015 01:21 PM

    It seems that it's either an issue with the database connection or the link you specified.

     

    What is the exact command you used to create the link? Try to use :

    CMN_DBLINK_SP 'PPMDBLINK','MyServerName','niku','niku','MyPassword'

    You have to run this statement on ppm_dwh, connected as ppm_dwh. Ensure you specify the correct server name.

     

    Re-running the command is fine, the stored procedure is going to drop and recreate the existing link. Please could you try and then re-run the SQL query above.



  • 35.  Re: Data Warehouse Database Link Config Query

    Posted Aug 31, 2015 01:36 PM

    Progress,

     

    I ran the command as you had it, it did not work.

     

    I made sure I used PPM_DWH and had to add the “ppm_dwh.” in front of the command to get it to run.

     

    I am now not getting an error and getting results when I run the select command.  However, I am still getting the error from the NSA that it can’t find the CMN_DBLINK_SP stored procedure.



  • 36.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Aug 31, 2015 02:05 PM

    Good. Please could you check what is the exact error in your nsa-ca.log and let me know? It points to a permission issue but you were able to successfully run the SP, that's why we have to check what will be the exact error message in there.



  • 37.  Re: Data Warehouse Database Link Config Query

    Posted Aug 31, 2015 02:15 PM

    Error message: [SQLServer JDBC Driver][SQLServer] Could not find stored procedure ‘CMN_DBLINK_SP’

     

    I can see the call for it and it is using the right parameters that I just used:

     

    Schema=niku, username=niku, hostname TPADWCSQL001, password=, clarityDBLinkName=CLARITYDBLINK



  • 38.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Aug 31, 2015 04:16 PM

    It should be okay to access it if it worked for you to run it as above... Did you create it as PPMDBLINK or CLARITYDBLINK? The reference should be the same in NSA as the one you used in the command. So if you left it as PPMDBLINK, you should indicate it in NSA as Database link name.



  • 39.  Re: Data Warehouse Database Link Config Query

    Posted Aug 04, 2015 09:55 PM

    Hi Nika,

     

    Is Linked Server the only option we have to connect the dwh and Clarity databases? Our company opposes the use of Linked Servers due to performance and security concerns. Are there other supported ways that we can configure Jaspersoft and Clarity without using Linked Server?



  • 40.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Aug 05, 2015 11:10 AM

    Hi Kate,


    Unfortunately this is not possible, because the link will actually help to retrieve information from PPM database to DWH. So without the link neither the OOTB reports or Datawarehouse jobs will actually work.
    I would recommend you submit the use case to your company so they make an exception. The link should be available only to the DWH user to use.

    Hope this helps.

     

    Kind Regards
    Nika Hadzhikidi

    CA Technologies

    Principal Support Engineer



  • 41.  Re: Data Warehouse Database Link Config Query

    Posted Oct 16, 2015 11:36 AM

    I'm having issues with creating the PPMDBLINK. Within the CSA the data warehouse is showing as available but I get an error when clicking Save.

     

    The SQL executed fine to create the DBLINK, so I'm guessing that isn't the issue.

     

    When executing the select query I recieve an error that the niku user cannot login. The PPM_DWH has all the necessary access rights. Any ideas?



  • 42.  Re: Data Warehouse Database Link Config Query

    Posted Oct 30, 2017 01:33 AM

    The issue has remained unresolved for me after reading this post.

    First of all, if anyone can summarise or if CA publishes a TEC doc, it would be very helpful.

     

     

    The database link could be created by two ways.

    1> From CSA Dataware house Tab

    -OR-

    2> Using SQL Server Studio using the stored procedure.

     

    NIKU, NIKU has been used few times while defining the link and while running the SELECT statement.

    MS SQL Server database has certain terminology, like server, instance, service, schema, database, owner, user, login, port number, etc.

    The fields on screen on CSA - Data warehouse tab does not appear to be consistent with the documentation CA PPM System Administration Guide Connection details on Page 29.

     

    Appreciate if more information is shared by those who uses non-default port number.

     

    Thanks



  • 43.  Re: Data Warehouse Database Link Config Query

    Posted Oct 30, 2017 03:05 AM

    Hi Mayank,

    Can you please go through Data Warehouse Job Fail , where discussions have been made on 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 2, Level 16, State 1, Line 3
    Named Pipes Provider: Could not open a connection to SQL Server [2].

     

     

    Regards,
    Srikanth G



  • 44.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Oct 30, 2017 03:10 AM

    Hi Mayank,

     

    Would appreciate if you can log a new thread, once a thread is mark as answered there are chances to miss out the thread. We have a documentation site called Docops" CA PPM - CA PPM - 15.3 - CA Technologies Documentation "

     

    See this link has a detail to configure DB link "https://docops.ca.com/ca-ppm/15-3/en/installing-and-upgrading/install-ca-ppm/install-and-configure-the-data-warehouse "

     

    Regards

    Suman Pramanik 



  • 45.  Re: Data Warehouse Database Link Config Query

    Posted Oct 31, 2017 01:36 AM

    Got resolved as follows:

     

    Using SQL server studio, logged into ppm_dwh database as ppm_dwh user.

    Ran following command in SQL Query window:

     

     

    CMN_DBLINK_SP 'PPMDBLINK', 'VM0001\sqlservice', 'Clarity_143','Clarity','******'

     

    ppmdblink can be any name of the link

    VM0001 is the host name where SQL Server runs

    sqlservice is the SQL service on which the database was created.

    Clarity_143 is the database name

    Clarity is the user that logs into the Clarity_143 database with a password ******.

     

     

    The link got created successfully.

     


    Ran SELECT count(1) FROM PPMDBLINK.Clarity_143.niku.srm_resources.

    niku is the schema of Clarity_143 database objects.

     

    Hope this helps.