IBM FileNet P8, Version 5.2.1            

SQL tuning: ID or object property search condition in non Content Platform Engine context

For Oracle, when you run a raw SQL query that directly accesses the object store database, use the Oracle hextoraw() function for any GUID values. ID or object valued properties in Content Platform Engine are implemented as GUID data type columns.

Important: Direct access to the object store database occurs in a non Content Platform Engine context. Directly changing data is not supported under any circumstance. For more information, see Restrictions on direct database access.
For example, if you are querying the database table and columns directly by using database query tools or non-Content Engine API applications, issue a query such as the following one:
SELECT object_id 
FROM DocVersion 
WHERE object_class_id = hextoraw('4AA9266F32C33846AB4C084516BFFDEE') 
Including the hextoraw() function on a GUID value in such a query allows the query execution plan to use the index on the column. GUID values are stored in the database in byte reversed form in Oracle and DB2.
Important: In the context of Content Platform Engine, do not use the rawtohex() function in the following ways:

During query execution, Content Platform Engine properly "binds" any GUID data values that are used in a query by using the JDBC method setBytes(). Setting the bytes for the GUID in this manner ensures that the value is properly matched to any database index on that column. This matching allows the index to be used in a query execution plan.



Last updated: October 2015
p8ppt273.htm

© Copyright IBM Corporation 2015.