IBM FileNet P8, Version 5.2.1            

Database SQL

In the SQL for the queries that you submit to Content Platform Engine, you can reference Content Platform Engine properties and classes in the following clauses: SELECT, FROM, WHERE, ORDER BY, and JOIN. Content Platform Engine parses the SQL in your submitted query into SQL that is known as database SQL. This parsing occurs in the following manner: classes are mapped to tables, properties are mapped to columns, and system columns are added to the SELECT, WHERE, and ORDER BY clauses as necessary.

The following table shows an example of how Content Platform Engine translates your query SQL into database SQL.
Table 1. Query SQL to database SQL translation example
Content Platform Engine SQL Corresponding database SQL
SELECT Id, Creator, DocumentTitle 
FROM DocSubClass 
WHERE SiteLocation = 'site123' 
ORDER BY DocumentTitle 
SELECT object_id, object_class_id, security_id, creator, ..., u1708_documenttitle 
FROM DocVersion 
WHERE object_class_id IN (?,?) 
AND home_id Is Null 
AND u7E24_sitelocation = ? 
ORDER BY u1708_documenttitle, object_id
The following aspects of database SQL might require some explanation:
Table 2. Database SQL
Item Comment
Bind markers The "?" symbols that Content Platform Engine adds to the database SQL are bind markers. Because of bind markers, a query execution plan does not have to be recompiled for each execution of the same search with different search values. Bind markers are required for fast performance in a high concurrency system. These markers or markers such as the following ones can be seen in a FileNet® P8 Server trace or database trace: ":1", ":2", and so on.
Property column prefixes The prefix on property columns in the database takes the form of uxy_, in which xy is a set of four digits. In the previous example, for instance, the following prefixes are shown: u1708_ and u7E24_. The uxy_ prefix varies from system to system and from property to property; it designates a user property column as opposed to a system column. For more information, see Custom table and Column Names.
WHERE clause: object_class_id IN (?,?) The object_class_id WHERE clause is always added to a search on a subclass to restrict the search to that class. If the WITH INCLUDESUBCLASSES clause (the default) is specified as part of the Content Platform Engine SQL, the search is restricted to the class and subclasses. In the case of this query, the Document subclass is "DocSubClass".
WHERE clause: home_id Is Null The home_id clause is added to restrict the search to objects that are not associated with cross-object store references.
ORDER BY clause: u1708_documenttitle, object_id The ORDER BY object_id is added by the Content Platform Engine for any continuable query.


Last updated: October 2015
p8ppt306.htm

© Copyright IBM Corporation 2015.