Cognos Analytics

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

Hi Jose,

I have not updated the JDBCDriver. But my 11.1.4 Audit Package is working without any issue. It is related to the java version. I am using java version 1.7 and Ojdbc7.jar.

Please check your java version.
Thanks.,
Ram


------------------------------
Ramanujam Rajagopal
------------------------------

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

Hi Guys,
Cognos Analytics ships with JAVA 8 (1.8), which is what is also tested. While JAVA 7 (1.7) may work we wouldn't recommend it. 

Unless there is a compelling reason you should consider moving to JAVA 8 and ensure your JDBC drivers are compliant with the JAVA version.

https://www.ibm.com/software/reports/compatibility/clarity-reports/report/html/prereqsForProduct?deliverableId=E32E9AD000BE11EABCF401BE73544226
Cheers,
Trevor

------------------------------
TREVOR COMEAU
------------------------------

Original Message:
Sent: Tue January 28, 2020 09:25 AM
From: Ramanujam Rajagopal
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Hi Jose,

I have not updated the JDBCDriver. But my 11.1.4 Audit Package is working without any issue. It is related to the java version. I am using java version 1.7 and Ojdbc7.jar.

Please check your java version.
Thanks.,
Ram


------------------------------
Ramanujam Rajagopal

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

Hi Trevor, thanks for your reply, just to confirm, I am using MS SQL for the CM database and Java 8, and the JDBC drivers for Java 8, so as far as I know shoudl work properly.


Thanks again.

Regards.

------------------------------
Jose Galvan
------------------------------

Original Message:
Sent: Tue January 28, 2020 10:02 AM
From: TREVOR COMEAU
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Hi Guys,
Cognos Analytics ships with JAVA 8 (1.8), which is what is also tested. While JAVA 7 (1.7) may work we wouldn't recommend it. 

Unless there is a compelling reason you should consider moving to JAVA 8 and ensure your JDBC drivers are compliant with the JAVA version.

https://www.ibm.com/software/reports/compatibility/clarity-reports/report/html/prereqsForProduct?deliverableId=E32E9AD000BE11EABCF401BE73544226
Cheers,
Trevor

------------------------------
TREVOR COMEAU

Original Message:
Sent: Tue January 28, 2020 09:25 AM
From: Ramanujam Rajagopal
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Hi Jose,

I have not updated the JDBCDriver. But my 11.1.4 Audit Package is working without any issue. It is related to the java version. I am using java version 1.7 and Ojdbc7.jar.

Please check your java version.
Thanks.,
Ram


------------------------------
Ramanujam Rajagopal

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

When you mention 7.4, did you mean the Microsoft SQL Server JDBC driver?

7.4.0 was released Aug 2019 followed by a hotfix 7.4.1 about a week later.

It has a significant defect we raised to the SQL Server JDBC driver team as we described in a technote.

https://www.ibm.com/support/pages/node/1073792

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

Sorry for not specifying I was not aware of that and yes I have the latest that are 7.4.1 (directly from the official site).

Thanks

------------------------------
Jose Galvan
------------------------------

Original Message:
Sent: Tue January 28, 2020 09:05 PM
From: NIGEL CAMPBELL
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

When you mention 7.4, did you mean the Microsoft SQL Server JDBC driver?

7.4.0 was released Aug 2019 followed by a hotfix 7.4.1 about a week later.

It has a significant defect we raised to the SQL Server JDBC driver team as we described in a technote.

https://www.ibm.com/support/pages/node/1073792

------------------------------
NIGEL CAMPBELL

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

Hi Jose,

I had the same issue when I was testing out Cognos 11.1.3 when planning my upgrade from 10.2.2. The issue is for some reason Cognos is not able to query the COGIPF_USERLOGON table inside the Audit database. Here was my solution:

1. Make sure you are using the latest Audit package included during your Cognos install (will ensure any updates from IBM are there)
2. Copy the Framework Manager project file for the Audit package (install_path\analytics\samples\Audit_samples\Audit) to a location to edit (ex: D:\Framework Manager Projects)
3. Using Framework Manger open the Audit Package
4. Edit the following query subjects definitions
   1. Audit > Audit > Database View >
      1. COGIPF_USERLOGON
      2. COGIPF_USERLOGON_FAILURES
      3. COGIPF_USERLOGOFF
      4. COGIPF_AGENTBUILD
      5. COGIPF_AGENTRUN
      6. COGIPF_MOBILESERVICE
      7. COGIPF_MOBILESERVICE_LOGOFF
   2. For each of the above query subjects update the following:
      1. Update the SQL Type from Cognos to Native. This is updated by opening the query subject, selecting the Query Information tab, Options (bottom right), and then SQL Settings tab. Press OK.
      2. On the SQL tab update the SQL query:
         1. From: from [Audit].COGIPF_USERLOGON 
         2. To: from [Audit].dbo.COGIPF_USERLOGON (assuming it is using the default schema)
      3. After making the change, go back to the Query Tab and press Test Sample to ensure data is now being returned.

I opened a support ticket with IBM and this was there solution. I did find it odd that only one table has issues being read by Cognos and not do not. I am running 11.1.5 currently and this issue still exists in the Audit package.

------------------------------
Christopher Mandragos
BI Developer/System Engineer
Innova Systems International, LLC
Alexandria VA
------------------------------

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

Thanks!!! I opened a ticket also with IBM, no answer yet. Also a coworker found that changing the model from DQM to CQM the reports works again (because thst way don0't use the JDBC) but I loss the dashboard capabilities.

I will check your steps later today to see how that works for me, and also thanks for confirm the issue still remains in Cognos 11.1.5.


Regards.

------------------------------
Jose Galvan
------------------------------

Original Message:
Sent: Wed January 29, 2020 09:09 AM
From: Christopher Mandragos
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Hi Jose,

I had the same issue when I was testing out Cognos 11.1.3 when planning my upgrade from 10.2.2. The issue is for some reason Cognos is not able to query the COGIPF_USERLOGON table inside the Audit database. Here was my solution:

1. Make sure you are using the latest Audit package included during your Cognos install (will ensure any updates from IBM are there)
2. Copy the Framework Manager project file for the Audit package (install_path\analytics\samples\Audit_samples\Audit) to a location to edit (ex: D:\Framework Manager Projects)
3. Using Framework Manger open the Audit Package
4. Edit the following query subjects definitions
   1. Audit > Audit > Database View >
      1. COGIPF_USERLOGON
      2. COGIPF_USERLOGON_FAILURES
      3. COGIPF_USERLOGOFF
      4. COGIPF_AGENTBUILD
      5. COGIPF_AGENTRUN
      6. COGIPF_MOBILESERVICE
      7. COGIPF_MOBILESERVICE_LOGOFF
   2. For each of the above query subjects update the following:
      1. Update the SQL Type from Cognos to Native. This is updated by opening the query subject, selecting the Query Information tab, Options (bottom right), and then SQL Settings tab. Press OK.
      2. On the SQL tab update the SQL query:
         1. From: from [Audit].COGIPF_USERLOGON 
         2. To: from [Audit].dbo.COGIPF_USERLOGON (assuming it is using the default schema)
      3. After making the change, go back to the Query Tab and press Test Sample to ensure data is now being returned.

I opened a support ticket with IBM and this was there solution. I did find it odd that only one table has issues being read by Cognos and not do not. I am running 11.1.5 currently and this issue still exists in the Audit package.

------------------------------
Christopher Mandragos
BI Developer/System Engineer
Innova Systems International, LLC
Alexandria VA

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

a

------------------------------
------------------------------
Christopher Mandragos
------------------------------
------------------------------

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

As update, today IBM answer my issue, it is a open issue in the development of the JDBC driver, and issue that came with version 7.4.1, and appear the fix is WIP.

"Cognos Analytics retrieves metadata via the DatabaseMetadata interface implemented by a JDBC driver. No column metadata is retrieved when the object name includes an underscore.

 

This version of the JDBC driver has an issue

 

The JDBC DatabaseMetadata interface methods accept values used to scope the metadata which should be retrieved. Several methods support pattern matching characters where JDBC uses the underscore character to match any single character and percentage as a wildcard.

 

When Cognos Analytics calls a method such as DatabaseMetadata.getColumns, it will pass the name of a database object (i.e. table). Should the name include either JDBC meta character, Cognos Analytics will escape those characters with an escape character.

 

Microsoft's 7.4.1 JDBC driver incorrectly handles parameter values where characters in a name include an escaped character

https://github.com/microsoft/mssql-jdbc/issues/1139

 

Reference  in

https://www.ibm.com/support/pages/node/1073792

"

------------------------------
Jose Galvan
------------------------------

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson

In case you missed my previous response, we documented the Microsoft JDBC driver (last Sept) here.
https://www.ibm.com/support/pages/node/1073792

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Thu January 30, 2020 05:13 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

As update, today IBM answer my issue, it is a open issue in the development of the JDBC driver, and issue that came with version 7.4.1, and appear the fix is WIP.

"Cognos Analytics retrieves metadata via the DatabaseMetadata interface implemented by a JDBC driver. No column metadata is retrieved when the object name includes an underscore.

 

This version of the JDBC driver has an issue

 

The JDBC DatabaseMetadata interface methods accept values used to scope the metadata which should be retrieved. Several methods support pattern matching characters where JDBC uses the underscore character to match any single character and percentage as a wildcard.

 

When Cognos Analytics calls a method such as DatabaseMetadata.getColumns, it will pass the name of a database object (i.e. table). Should the name include either JDBC meta character, Cognos Analytics will escape those characters with an escape character.

 

Microsoft's 7.4.1 JDBC driver incorrectly handles parameter values where characters in a name include an escaped character

https://github.com/microsoft/mssql-jdbc/issues/1139

 

Reference  in

https://www.ibm.com/support/pages/node/1073792

"

------------------------------
Jose Galvan

Original Message:
Sent: Mon January 27, 2020 02:37 PM
From: Jose Galvan
Subject: XQE-PLN-0012 error on Audit Package in 11.1.4

Good Afternoon. 

I have several reports from the Audit Package (installed samples from 11.1.2). Started to run correctly in 11.1.2. Around 2 months ago we updated to 11.1.4 Cognos (no update to the package) and reports ran still ok.

Some days ago I updated the JDBC Drivers (from 7.2 to 7.4 latest) and next day all reports using the audit package started to fail with error:

RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0012 The column 'COGIPF_LOGON_OPERATION' could not be found in the model.

If I repoint the report to the package from Cognos 10, the report works again.

If I rollback the drivers to 7.2 and reboot the servers, the reportsrun again.

Someone else have experienced this problem? just checking before entering an IBM Ticket.

------------------------------
Jose Galvan
------------------------------
#CognosAnalyticswithWatson