Troubleshooting scenario 8: Why Content Manager OnDemand does not support query result modifier SQL clauses

Symptoms

Several commands and APIs are available that allow a user to submit raw SQL for querying the Content Manager OnDemand application group data tables. However, because the submitted SQL is not parsed or validated, certain query result modifiers are not supported. These query result modifiers include but are not limited to:
  • ORDER BY
  • READ ONLY
  • FETCH FIRST
  • OPTIMIZE FOR
  • SORT ASC/DESC
  • GROUP BY

Causes

This happened because of the overall design of the Content Manager OnDemand application group. OnDemand uses database segments, multiple tables, and even multiple queries in order to increase the performance of queries and lessen the workload of the database. SQL queries against the application group data tables are first limited to just the tables that meet the database segment constraints. This is accomplished by using a start and stop date for each data table. That list of tables is used to generate a full-select with UNION ALL SQL statement. This generated SQL string is limited by some restraints, such as the maximum length of the generated SQL statement and the number of tables that are searched in a single query. These generated SQL statements create some highly complex queries that are repeated in multiple select statements while searching over a large range of data. Adding search result modifiers, such as ORDER BY, compounds the workload on the database.

Resolving the problem

In an attempt to reduce the work to the clients, the Content Manager OnDemand folder definitions support actions such as sorting, grouping, and limiting the number of rows. This approach frees the database from having to perform complex query modifications that require a large amount of time to optimize and large amounts of memory to perform.

The following is an example using the arsdoc command, where you submit an SQL query like ACCTNO LIKE ‘1001% and specify a segment date like 12/01/2004 to 12/31/2004 to Content Manager OnDemand: 
arsdoc query -u user -p whatever -f “Credit Card Statements” 
-i “ACCTNO LIKE ‘1001” -s “12/01/2004,12/31/2004”
The Content Manager OnDemand server uses the database segment values to determine what tables need to be searched. This might yield only a single table to search and the generated SQL looks like: 
SELECT * FROM root.BAA20 WHERE ACCTNO LIKE ‘1001
In this case, you can append a modifier such as ORDER BY to your submitted SQL. The generated SQL is simply appended to the end of a single SELECT/WHERE statement.
In another example, extend the database segment date range to 01/01/2004-12/31/2004: 
arsdoc query -u user -p whatever -f “Credit Card Statements”
 -i “ACCTNO LIKE ‘1001” -s “01/01/2003,12/31/2004”
The number of tables being searched increase, possibly by two tables, and the generated SQL looks like: 
SELECT * FROM BAA18 WHERE ACCTNO LIKE ‘1001 UNION ALL 
SELECT root.BAA19.*, ‘BAA19' FROM root.BAA19 
WHERE ACCTNO LIKE ‘1001 UNION ALL 
SELECT root.BAA20.*,'BAA20' 
FROM root.BAA20 
WHERE ACCTNO LIKE ‘1001
This example indicates that using a modifier like ORDER BY cannot be appended directly onto each WHERE clause. It results in invalid SQL syntax because it is included as part of each WHERE clause. Valid SQL appends it to the end of the entire full-select statement. Because Content Manager OnDemand does not parse the query SQL that is submitted, it is not able to append the ORDER BY modifier to the end of the full-select statement.
To take this example a step further, change the database segment range to 01/01/1990-12/31/2004: 
arsdoc query -u user -p whatever -f “Credit Card Statements” -i 
“ACCTNO LIKE ‘1001” -s “01/01/1990,12/31/2004”
The number of tables might increase to a hundred or more, and the resulting SQL is too long or span too many tables. In this case, OnDemand generates multiple full-select queries to accomplish the task, and cannot generate valid SQL across the queries and have it perform an ORDER BY for the entire result set.

In this case, Content Manager OnDemand depends on the client to modify the results. While using APIs or commands like ARSDOC to submit SQL queries directly to Content Manager OnDemand, use just the fields being queried and always supply a segment date range. Without a segment date range supplied, all tables in the application group are searched. After the results are returned, you can modify them accordingly.