Limitations of the Lifecycle Query Engine relational store

The following are the Lifecycle Query Engine relational store limitations:
  • Lifecycle Query Engine relational store works with only Oracle and Db2® on Windows or Linux®. Installation of Lifecycle Query Engine with Lifecycle Query Engine relational store on Derby database is not supported. You can no longer deploy all-in-one Derby demos if you want to try the Lifecycle Query Engine relational store because Apache Derby does not support the With or Select clause that is used by Lifecycle Query Engine relational store queries.
  • You cannot move the data from Lifecycle Query Engine Apache Jena TDB into Lifecycle Query Engine relational store. Administrators need to reindex the application data for the Lifecycle Query Engine relational store from the same data sources that they are indexing for Lifecycle Query Engine Apache.
  • Server rename, the behavior that exists with the Lifecycle Query Engine Jena solution, is not available in Jazz® Reporting Service 7.0.3.
  • Initial Lifecycle Query Engine rs indexing is expected to be slower compared with Lifecycle Query Engine Jena.
  • The statistics of the Lifecycle Query Engine relational store tables must be updated before you reindex or remove the data sources.
  • Custom attributes run slower than the predefined attributes. The intention is to allow customers to extend and modify the relational store in the future, but this will not be part of the new user experience.
  • The order of the query results might be different between SPARQL and SQL:
    • Reports sorting on string properties like title might deliver different ordering. SQL databases are typically configured to using case-sensitive collation in which lowercase letters are ordered before uppercase letters, whereas for SPARQL the reverse is true.
    • Reports on IBM® Engineering Test Management artifacts that are sorted by ID are likely to have different ordering. Engineering Test Management publishes oslc:shortId values that are xsd:integer instead of the xsd:string type that is required by OSLC Core. SPARQL sorts such literals by numeric value.
    • The ordering of null values is likely to be different between SQL and SPARQL.
  • Resultant differences in ordering where the query results reach the query limit might result in the truncated result set containing different results between SPARQL and SQL. The default query limit is 3000.
    • When Resource Description Framework (RDF) of an artifact has both a dcterms:title and rdfs:label, and the strings differ, the results between SPARQL and SQL might be different. The Lifecycle Query Engine relational store maps both properties to the same column, so Jazz Reporting Service cannot support querying or reporting on both properties as different values.
    • When an artifact has multiple dcterms:title values that use multiple language-tagged literals, the results between SPARQL and SQL are likely to be different. SPARQL reports all the values. SQL only reports the plain literal or English language-tagged string.
    • When an artifact has a string or XMLLiteral property whose length exceeds 400 characters, the result of querying on that property might yield different results between SPARQL and SQL. This is because for the Lifecycle Query Engine relational store, the queryable column for that property is a VARCHAR(400), so only the first 400 characters can be queried. SPARQL does not have that restriction.
    • For SQL, the dateTimes might yield strings that differ due to rounding of times (represented by using dateTime SQL type) up or down to the nearest 1/300th second. For example, a dateTime of 2021-04-19T23:59:59.999 might be shown as 2021-04-20T00:00:00.00 because that time value is rounded up and this results in a different date.
    • When running Compare query results containing a number of days, date results might be different by +- 1 as each vendor calculates the date differences differently.
    • When querying on an Lifecycle Query Engine data source that is not scoped by a configuration that includes versioned artifacts from opted-in project areas, SPARQL produces the Cartesian product of property values across graphs. For example, consider that you have requirement1-version1 with title V1 title and description V1 description, and requirement1-version2 with title V2Title and description V2 description. A report on requirements that use the Lifecycle Query Engine data source that has columns for title and description includes the following 4 rows:
      • V1Title, V1Description
      • V1Title, V2Description
      • V2Title, V1Description
      • V2Title, V2Description

    • The Lifecycle Query Engine relational store does not report the Cartesian product and only reports 2 rows for that requirement, which is an improvement.

      You can achieve the same effect in SPARQL by manually editing the query to include GRAPH ?g {...} constructs, but this might significantly impact query performance.

      The result of this is that you cannot compare query results between SPARQL and SQL when a report is on an Lifecycle Query Engine data source that is not scoped by a configuration and is not scoped to compatible or non-opted-in project areas.

  • The Compare query results feature do not produce the expected result if you change the filter before running Compare query results. Therefore , to validate SPARQL vs SQL on a report level, make sure you run a report with the changed filter first, and then run the Compare query results.
  • SPARQL and SQL historic metric jobs might not run at the exact time, so the metrics that are collected at different times might not match exactly when using compare query results.
  • Report authors must revisit reports with custom expressions to see whether they work. In most cases, SPARQL needs to be replaced with SQL statements. See Manually converting SPARQL to SQL.
  • The Lifecycle Query Engine relational store schema or the process for the database schema evolution over time is not documented. Until this is not completed, the recommendation is to avoid using advanced SQL with Lifecycle Query Engine rs 7.0.3.
  • Estimate, Corrected Estimate, and Time spent attributes in custom expressions are automatically divided by 60000 to convert values in milliseconds to minutes. For SPARQL, they are not converted and remain as milliseconds. If you have custom expressions that perform explicit division to convert the values to minutes or hours, they need to be revised and updated for SQL.
  • Artifacts that have a blank node as their subject and include that URL as a column in the report might result in reports whose results cannot be compared against SPARQL. If the URL column is used for sorting, it results in different ordering compared with SPARQL. The issue is that SPARQL manufactures blank nodes at query time, whereas pseudo-URLs are created for blank nodes for the Lifecycle Query Engine relational store at indexing time. As of now there is no way to correlate the two.
  • When comparing query results, filedAgainst values are missing for SPARQL. SPARQL handles such queries by showing the filed against as blank. SQL shows filedAgainst that uses the URL. SQL is working as expected as this is per design to show their URLs. SQL does this for all enumerations. SPARQL does not show filedAgainst values for the hierarchical ones. The following example shows the filed against URL: https://jazz.net/jazz/resource/itemOid/com.ibm.team.workitem.Category/_aw5r4HBgEeqdgt6zVZd8Ug. If you paste in Chrome, it shows the filed against as archived.
  • The Rhapsody® Tag URLs for SQL use a different syntax when compared to SPARQL. It is advisable not to do reporting on Rhapsody Tag URLs as these URLs are internal and are not navigable.
  • When a report result exceeds the row count limit, it is suggested that you sort the results by the same column so that equivalent comparisons can be performed.

Cases in which report comparisons are not useful or cannot be compared

  • Reports against the Lifecycle Query Engine data source that include versioned artifacts might not compare correctly. You can use project scoping to limit the artifacts to project areas that are not opted-in for configuration management.
  • Drill down in reports currently cannot be query results that are compared. The drill down must be executed through the Report Builder results user interface.