error importing SAFe SAFe 3.0 Reports 6.0.1, "Allocated Capacity by Strategic Theme" fails with "an SQL exeception occurred when validating the SQL generated from the reprot (see the log file for the query string causing the exception)
Hi,
I am trying to import SAFe_3.0_Reports__6.0.1___2016-03-09_.zip using the latest instructions linked in this forum post https://jazz.net/forum/questions/203527/error-importing-the-safe-reports-into-jrs
A CCM project area was created using SAFe 3.0 Process (Program). Program Epic, Feature, and PI Objective work items were created. There is no Portfolio Epic work item type. DCC jobs were run and the JRS data warehouse data source was refreshed.
This is CCM 6.0.1 iFix003 for JRS. This error has been reproduced in 2 different environments, one with DB2 and one with SQL Server 2012
The following error is thrown in JRS showing all but one report imported, "Allocated Capacity by Strategic Theme" fails:
The rs.log is slightly different in SQL Server than DB2 but the query it calls out is the same:
SQL Server:
2016-03-25 19:43:53,932 [ WebContainer : 6] ERROR ibm.team.integration.reporting.model.utils.DbUtils - CRRGW5628E An com.microsoft.sqlserver.jdbc.SQLServerException error occurred when validating the input SQL string, caused by A processing error "Invalid column name 'VAL'." occurred..
com.microsoft.sqlserver.jdbc.SQLServerException: A processing error "Invalid column name 'VAL'." occurred.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
2016-03-25 19:43:53,932 [ WebContainer : 6] ERROR ibm.team.integration.reporting.model.utils.DbUtils - The query that caused the exception is:
SELECT DISTINCT T1.NAME AS URL1_title,
T1.URL AS URL1,
VAL,
VAL1,
VAL2,
VAL3,
T2.FULL_NAME
FROM RIDW.VW_REQUIREMENT T1
LEFT OUTER JOIN RIDW.VW_RESOURCE T2
ON T2.RESOURCE_ID=T1.OWNER_ID
WHERE T1.PROJECT_ID = 6 AND
( T1.REQUIREMENT_TYPE = '[SAFe] Strategic Theme'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUIREMENT_ID <> -1 AND T1.REQUIREMENT_ID IS NOT NULL)
2016-03-25 19:43:53,932 [ WebContainer : 6] ERROR n.reporting.model.exportimport.ImportActionHandler - Import failed for file Allocated_Capacity_by_Strategic_Theme.ttl An SQL exception occurred when validating the SQL generated from the report (see the log file for the query string causing the exception).
2016-03-25 19:44:08,885 [ WebContainer : 6] ERROR gration.reporting.model.beans.ReportDefinitionBean - (ReportDefinitionBean.java:873)-> http://jazz.net/ns/reporting/sparqlreporting#reportQueryName: Allocated Capacity by Strategic Theme
2016-03-25 19:44:08,885 [ WebContainer : 6] ERROR n.reporting.model.exportimport.ImportActionHandler - Cannot find query for report in file Allocated_Capacity_by_Strategic_Theme.ttl -> http://jazz.net/ns/reporting/sparqlreporting#reportQueryName: Allocated Capacity by Strategic Theme
DB2:
2016-03-31 00:36:43,554 [ Default Executor-thread-9266] ERROR ibm.team.integration.reporting.model.utils.DbUtils - CRRGW5628E An com.ibm.db2.jcc.am.SqlSyntaxErrorException error occurred when validating the input SQL string, caused by DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=VAL, DRIVER=4.14.121.
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=VAL, DRIVER=4.14.121
at com.ibm.db2.jcc.am.ed.a(ed.java:677)
2016-03-31 00:36:43,554 [ Default Executor-thread-9266] ERROR ibm.team.integration.reporting.model.utils.DbUtils - The query that caused the exception is:
SELECT DISTINCT T1.NAME AS URL1_title,
T1.URL AS URL1,
VAL,
VAL1,
VAL2,
VAL3,
T2.FULL_NAME
FROM RIDW.VW_REQUIREMENT T1
LEFT OUTER JOIN RIDW.VW_RESOURCE T2
ON T2.RESOURCE_ID=T1.OWNER_ID
WHERE T1.PROJECT_ID = 6 AND
( T1.REQUIREMENT_TYPE = '[SAFe] Strategic Theme'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUIREMENT_ID <> -1 AND T1.REQUIREMENT_ID IS NOT NULL)
2016-03-31 00:36:43,564 [ Default Executor-thread-9266] ERROR n.reporting.model.exportimport.ImportActionHandler - Import failed for file Allocated_Capacity_by_Strategic_Theme.ttl An SQL exception occurred when validating the SQL generated from the report (see the log file for the query string causing the exception).
2016-03-31 00:38:06,976 [ Default Executor-thread-9266] ERROR gration.reporting.model.beans.ReportDefinitionBean - (ReportDefinitionBean.java:873)-> http://jazz.net/ns/reporting/sparqlreporting#reportQueryName: Allocated Capacity by Strategic Theme
2016-03-31 00:38:06,981 [ Default Executor-thread-9266] ERROR n.reporting.model.exportimport.ImportActionHandler - Cannot find query for report in file Allocated_Capacity_by_Strategic_Theme.ttl -> http://jazz.net/ns/reporting/sparqlreporting#reportQueryName: Allocated Capacity by Strategic Theme
Any suggestions on how to resolve this?
Thanks,
Brett
I am trying to import SAFe_3.0_Reports__6.0.1___2016-03-09_.zip using the latest instructions linked in this forum post https://jazz.net/forum/questions/203527/error-importing-the-safe-reports-into-jrs
A CCM project area was created using SAFe 3.0 Process (Program). Program Epic, Feature, and PI Objective work items were created. There is no Portfolio Epic work item type. DCC jobs were run and the JRS data warehouse data source was refreshed.
This is CCM 6.0.1 iFix003 for JRS. This error has been reproduced in 2 different environments, one with DB2 and one with SQL Server 2012
The following error is thrown in JRS showing all but one report imported, "Allocated Capacity by Strategic Theme" fails:
The rs.log is slightly different in SQL Server than DB2 but the query it calls out is the same:
SQL Server:
2016-03-25 19:43:53,932 [ WebContainer : 6] ERROR ibm.team.integration.reporting.model.utils.DbUtils - CRRGW5628E An com.microsoft.sqlserver.jdbc.SQLServerException error occurred when validating the input SQL string, caused by A processing error "Invalid column name 'VAL'." occurred..
com.microsoft.sqlserver.jdbc.SQLServerException: A processing error "Invalid column name 'VAL'." occurred.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
2016-03-25 19:43:53,932 [ WebContainer : 6] ERROR ibm.team.integration.reporting.model.utils.DbUtils - The query that caused the exception is:
SELECT DISTINCT T1.NAME AS URL1_title,
T1.URL AS URL1,
VAL,
VAL1,
VAL2,
VAL3,
T2.FULL_NAME
FROM RIDW.VW_REQUIREMENT T1
LEFT OUTER JOIN RIDW.VW_RESOURCE T2
ON T2.RESOURCE_ID=T1.OWNER_ID
WHERE T1.PROJECT_ID = 6 AND
( T1.REQUIREMENT_TYPE = '[SAFe] Strategic Theme'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUIREMENT_ID <> -1 AND T1.REQUIREMENT_ID IS NOT NULL)
2016-03-25 19:43:53,932 [ WebContainer : 6] ERROR n.reporting.model.exportimport.ImportActionHandler - Import failed for file Allocated_Capacity_by_Strategic_Theme.ttl An SQL exception occurred when validating the SQL generated from the report (see the log file for the query string causing the exception).
2016-03-25 19:44:08,885 [ WebContainer : 6] ERROR gration.reporting.model.beans.ReportDefinitionBean - (ReportDefinitionBean.java:873)-> http://jazz.net/ns/reporting/sparqlreporting#reportQueryName: Allocated Capacity by Strategic Theme
2016-03-25 19:44:08,885 [ WebContainer : 6] ERROR n.reporting.model.exportimport.ImportActionHandler - Cannot find query for report in file Allocated_Capacity_by_Strategic_Theme.ttl -> http://jazz.net/ns/reporting/sparqlreporting#reportQueryName: Allocated Capacity by Strategic Theme
DB2:
2016-03-31 00:36:43,554 [ Default Executor-thread-9266] ERROR ibm.team.integration.reporting.model.utils.DbUtils - CRRGW5628E An com.ibm.db2.jcc.am.SqlSyntaxErrorException error occurred when validating the input SQL string, caused by DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=VAL, DRIVER=4.14.121.
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=VAL, DRIVER=4.14.121
at com.ibm.db2.jcc.am.ed.a(ed.java:677)
2016-03-31 00:36:43,554 [ Default Executor-thread-9266] ERROR ibm.team.integration.reporting.model.utils.DbUtils - The query that caused the exception is:
SELECT DISTINCT T1.NAME AS URL1_title,
T1.URL AS URL1,
VAL,
VAL1,
VAL2,
VAL3,
T2.FULL_NAME
FROM RIDW.VW_REQUIREMENT T1
LEFT OUTER JOIN RIDW.VW_RESOURCE T2
ON T2.RESOURCE_ID=T1.OWNER_ID
WHERE T1.PROJECT_ID = 6 AND
( T1.REQUIREMENT_TYPE = '[SAFe] Strategic Theme'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUIREMENT_ID <> -1 AND T1.REQUIREMENT_ID IS NOT NULL)
2016-03-31 00:36:43,564 [ Default Executor-thread-9266] ERROR n.reporting.model.exportimport.ImportActionHandler - Import failed for file Allocated_Capacity_by_Strategic_Theme.ttl An SQL exception occurred when validating the SQL generated from the report (see the log file for the query string causing the exception).
2016-03-31 00:38:06,976 [ Default Executor-thread-9266] ERROR gration.reporting.model.beans.ReportDefinitionBean - (ReportDefinitionBean.java:873)-> http://jazz.net/ns/reporting/sparqlreporting#reportQueryName: Allocated Capacity by Strategic Theme
2016-03-31 00:38:06,981 [ Default Executor-thread-9266] ERROR n.reporting.model.exportimport.ImportActionHandler - Cannot find query for report in file Allocated_Capacity_by_Strategic_Theme.ttl -> http://jazz.net/ns/reporting/sparqlreporting#reportQueryName: Allocated Capacity by Strategic Theme
Any suggestions on how to resolve this?
Thanks,
Brett
Accepted answer
The problem is that you have not created an RDNG SAFe project area. The report that is causing the error includes RDNG artifacts. If you do not have a SAFe RDNG project area, that report will not be imported successfully. That's okay, the other reports are fine and will work. I will update the SAFe Report Guidelines document to clarify this. If you did not follow all instructions in that document, you will run into issues such as this.
4 other answers
The issue is that you have not created an RDNG SAFe-based project area. The report that fails to import is an RDNG artifact report. You do not have that project area, so you do not have the artifacts and therefore the report cannot be imported. This is clearly documented in the SAFe Report Guidelines.
In general, report import failures are isolated -- the reports that do import successfully will work fine. If you do not have or want RDNG, you can ignore this error. I will update the SAFe Report Guidelines document Troubleshooting section to clarify this.
In general, report import failures are isolated -- the reports that do import successfully will work fine. If you do not have or want RDNG, you can ignore this error. I will update the SAFe Report Guidelines document Troubleshooting section to clarify this.
Agreed! This is correct, we needed to get the CCM area up and running first, We did the import of reports at that juncture as part of Post initialization tasks for the CCM area. Couple of days later we did the creation of the other application PA's (RDNG and RQM) and wrapped them around an LPA since there is no LPA template for SaFe. We now all 3 applications. If we decide there is value in the report that error's is there a way to import it?
You can always import the reports again so try it now that you have the RDNG project area. As long as you've followed the instructions in the guidelines document, you should have no errors at this point. The reports that you have already imported will be re-imported (unless you opt to not overwrite, which will result in duplicate reports). It sounds like the post-initialization task needs to clarify that the reports should not be imported until the CLM LPA (or application project areas separately) are created.
Agreed! This is correct, we needed to get the CCM area up and running first, We did the import of reports at that juncture as part of Post initialization tasks for the CCM area. Couple of days later we did the creation of the other application PA's (RDNG and RQM) and wrapped them around an LPA since there is no LPA template for SaFe. We now all 3 applications. If we decide there is value in the report that error's is there a way to import it?
Comments
Brett Bohnn
Mar 31 '16, 12:47 p.m.I am wondering if the query that causes the exception explains root cause. It is select from RIDW.VW_REQUIREMENT and I don't have an RM project area with data.
SELECT DISTINCT T1.NAME AS URL1_title,
T1.URL AS URL1,
VAL,
VAL1,
VAL2,
VAL3,
T2.FULL_NAME
FROM RIDW.VW_REQUIREMENT T1
LEFT OUTER JOIN RIDW.VW_RESOURCE T2
ON T2.RESOURCE_ID=T1.OWNER_ID
WHERE T1.PROJECT_ID = 6 AND
( T1.REQUIREMENT_TYPE = '[SAFe] Strategic Theme'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUIREMENT_ID <> -1 AND T1.REQUIREMENT_ID IS NOT NULL)