Check your PE related stuff

 In 5.2 - you should be using any of the below URL to check your PE related stuff -

http://server:port/peengine/IOR/admin/help
http://server:port/peengine/IOR/ping?cp=myCP&farm=true&dbinfo=true
http://server:port/peengine/IOR/ping?systeminfo=true
http://server:port/peengine/IOR/ping?cp=myCP&java_dump=true
http://server:port/peengine/admin/async
http://server:port/peengine/admin/cm/audit
http://server:port/peengine/admin/cm/stats
http://server:port/peengine/admin/cm/task?name=*?tion=info
http://server:port/peengine/admin/api

Sweep filter conditions

IBM FileNet P8, Version 5.2            

---

A filter condition narrows the scope of a sweep to include only objects that meet specific criteria. The criteria is expressed in terms of properties and values of the objects that are targeted by the sweep. The syntax is a subset of the SQL syntax for the WHERE clause.

Tip: By default, a sweep retrieves a database row before it evaluates the filter conditions for that row. For information about overriding this default behavior, see Optimizing sweep performance by changing when filter conditions are evaluated.

Here are some examples of filter conditions:

Goal	Filter condition example
All superseded documents	VersionStatus = 4
All documents that were created at least a year ago	DateCreated < NOW() - TimeSpan(365, 'Days')
All content in a specific storage area	StorageArea = OBJECT('{5E2BE09A-F4B1-49E2-A229-77FE32E5FEF1}')
Complex logical expression	VersionStatus = 4 AND DateCreated < NOW() - TimeSpan(365, 'Days') AND ContentSize > (1024 * 1024 * 500)

Cloning IBM FileNet Content Engine data from one IBM FileNet P8 domain to another

Question

You have two version 4.5 FileNet P8 domains, one is for your test environment and the other is for your production environment. Your production environment has been up and running for a while and you want to clone the Content Engine data from the production environment back to the test environment. What is the best way to accomplish this cloning process?

Answer

This technical notice presents a procedure for cloning Content Engine data from one version 4.5.0 or 4.5.1 FileNet P8 domain to another FileNet P8 domain. To clone the Content Engine data means to copy the Global Configuration Database (GCD) and object stores from one FileNet P8 domain to another.


Important: This procedure does not describe how to clone Process Engine data.

Once the cloning procedure is completed, the test environment will have the same object store data (metadata and content), as the production environment. The two FileNet P8 domains do not need to have the same physical system layout. For example, the production FileNet P8 domain can have clustered FileNet P8 components, while the test FileNet P8 domain can have nonclustered FileNet P8 components. This procedure can apply to all supported application server, database, and directory server types.

Prerequisites

• To accomplish this cloning procedure you must be very familiar with FileNet P8 installation and system administration.
  
  
• The production and test FileNet P8 domains must be running on the same version of FileNet P8 software and the same fix pack level.
  
  
• The production and test FileNet P8 domains must use the same directory service (LDAP) domain for authentication.
  
  
• The data source names for the GCD must be the same in the production and test FileNet P8 domains.

Additional Notes

The cloning procedure is designed to minimize the downtime on the production FileNet P8 domain. However, for parts of the procedure the test and production FileNet P8 domains will be shut down. Therefore, do this procedure during off hours, when shutting down the FileNet P8 domains will have minimal impact on users.

If your production FileNet P8 domain is configured with a Content Search Engine component, it is a best practice to complete the procedure in this technical notice first, and then create new content search indexes (collections) in the test FileNet P8 domain and reindex the content.

If you are unfamiliar with any of the steps in this procedure, contact the IBM Enterprise Content Management (ECM) Client Support team for assistance.

The technical notice “Migrating Production Data to Test in the Same Authentication Domain” located at http://www-01.ibm.com/support/docview.wss?uid=swg21326721 describes a similar procedure to the one in this technical notice but applies only to IBM FileNet P8 3.5 environments.


Procedure

This procedure has three parts:

1. Backing up Content Engine data from the production FileNet P8 domain
   
   
2. Configuring the test FileNet P8 domain
   
   
3. Restoring backed-up Content Engine data to the test FileNet P8 domain

I. Backing up Content Engine data from the production FileNet P8 domain

Important: Do all of the steps in this part of the procedure on the production FileNet P8 domain only.

To shut down all of the FileNet P8 components, then back up the Content Engine data, and finally restart the components:

1. Verify that the following Content Engine activities have completed:
   
   - Content Engine transactions (including all automatically launched indexing jobs)
   
   - In-progress event actions (that is, the QueueItem table is empty)
   
   - Publishing requests
   
   Contact the ECM Client Support team if you are unsure of how to verify the items in this step. Failure to properly shut down the production FileNet P8 domain (including all services and activities) will cause problems in the early stages of starting the FileNet P8 software on the test FileNet P8 domain.
   
   In particular, queued work will be launched as Content Engine services start, but the work might not start until the migration completes, which will result in flooding the event logs with misleading error messages.
   
   
2. Shut down the production FileNet P8 domain in the following order:
   
   a. Shut down the Workplace or Workplace XT application servers.
   
   b. Stop all other client activity, such as custom FileNet P8 applications.
   
   c. Shut down the Process Engine servers.
   
   d. Shut down the Content Engine application servers, including the
   administrative application server.
   
   
3. Make a backup copy of the bootstrapped version of the Content Engine EAR file, which later will be copied to the test FileNet P8 domain. This file is typically in the following directory: ce_install_path/tools/configure/profiles/profile_name/EAR.
   
   
4. Complete the following steps to back up the Content Engine data:
   
   a. Use your own chosen tools to back up the Content Engine file storage areas, which eventually will be copied to the test FileNet P8 domain.
   
   b. Use database vendor tools to export the GCD database and the databases for the object stores from the FileNet P8 production database. For an Oracle database, export everything owned by the GCD table space owner and each object store table space owner.
   
   For details on backup and restore procedures, refer to the IBM FileNet P8 4.5 online help topic: System Administration > Content Engine Administration > Content Engine overview > Features > Backup and restore.
   
   
5. Restart the components of the production FileNet P8 domain in the following order:

a. Restart the Content Engine application servers.

b. Restart the Process Engine servers.

c. Restart the Workplace or Workplace XT application servers.

d. Restart all other client activity, such as custom FileNet P8 applications.

II. Configuring the test FileNet P8 domain

Important: Do all of the steps in this part of the procedure on the test FileNet P8 domain only.

To shut down all of the FileNet P8 components and create the database tables:

1. Shut down the components of the test FileNet P8 domain in the following order:
   
   a. Shut down the Workplace or Workplace XT application servers.
   
   b. Stop all other client activity, such as custom FileNet P8 applications.
   
   c. Shut down the Process Engine servers.
   
   d. Shut down the Content Engine application servers, including the administrative application server.
   
   
2. Complete the following steps on the database server that is used for the test FileNet P8 domain:
   
   a. Create a new GCD database (schema).
   
   b. Create a new object store database (schema) for each object store that you are copying from the production FileNet P8 domain.
   
   It is a best practice to give each object store table the same name as the corresponding table in the database server for the production FileNet P8 domain.
   
   
3. Complete the following steps depending on whether you have data sources already defined for the GCD and object stores:
   
   - If you already have data sources defined for the GCD and object stores in the test FileNet P8 domain that match the data source names in the production FileNet P8 domain, use the administrative tool on your application server where Content Engine is installed to update the user name and password to access the database, database name, and TCP/IP port, for each data source to be used in the test FileNet P8 domain.
   
   - If any object store that you are copying from the production FileNet P8 domain is not already defined in the test FileNet P8 domain (that is, its data sources do not yet exist in the test FileNet P8 domain), use the Configuration Manager to create data sources for the new object store.
   
   
4. For each file storage area in the production FileNet P8 domain, create a new corresponding file storage area directory in the test FileNet P8 domain.
   
   It is a best practice for the path to each new file storage area in the test FileNet P8 domain to be the same as the path to the corresponding file storage area in the production FileNet P8 domain.
   
   
5. Replace the Content Engine EAR file on the Content Engine server in the test FileNet P8 domain by completing the following steps:
   
   a. Copy the bootstrapped version of the Content Engine EAR file from the production FileNet P8 domain to the location of the Content Engine EAR file on the test FileNet P8 domain.
   
   b. Use the administrative tool on your application server in the test FileNet P8 domain to undeploy the Content Engine EAR file. Then, restart the application server.
   
   c. In the Configuration Manager use the Deploy Application Task option to redeploy the Content Engine EAR file.
   
   

III. Restoring backed-up Content Engine data to the test FileNet P8 domain

To restore the backed-up production data into the test FileNet P8 domain:

1. Shut down the application server where Content Engine Server is deployed in the test FileNet P8 domain, including the administrative server.
   
   
2. Use database vendor tools to import the exported production FileNet P8 domain GCD database and object store databases into the new databases (schemas) in the test environment. For Oracle databases, you can use a command such as Oracle import with fromuser and touser parameters to import into the table space with a new name for the test environment table spaces.
   
   
3. Use your own chosen tools to restore the backed-up file storage areas from the production FileNet P8 domain to the test FileNet P8 domain.
   
   
4. Start the Content Engine application servers in the test FileNet P8 domain, including the administrative server.
   
   
5. If the path to the storage area in the test FileNet P8 domain differs from that in the production FileNet P8 domain, use the Move Storage Area wizard to update the storage area path location in the GCD.
   
   
   For details, refer to the IBM FileNet P8 4.5 online help topic System Administration > Content Engine Administration > Content Engine Wizards > Move a storage area.
   
   
6. Use FileNet Enterprise Manager to verify that all the object stores that you copied from the production FileNet P8 domain are accessible in the test FileNet P8 domain:
   
   a. Select a document from each object store and view it.
   
   b. Create new Process Engine isolated regions and connection points to connect to the Process Engine in the test FileNet P8 domain.
   
   Tip: For additional verification that the data sources for the production and test FileNet P8 domains are not crossed, create a new folder within one of the object stores in the test FileNet P8 domain, and then verify that the new folder does not exist within the same object store in the production FileNet P8 domain.
   
   
7. Start the other FileNet P8 components in the test FileNet P8 domain: Workplace or Workplace XT, custom applications, and Process Engine.
   
   
8. If you are using the Content Search Engine component, create new content search indexes (collections) in the test FileNet P8 domain and reindex the content.

IBM FileNet P8, Version 5.2            

---

Creating sweeps

There are two fundamental types of sweeps: once-only and ongoing. But because there are two subtypes of ongoing sweeps, there are three named sweep types: job, policy, and queue.

About this task

Subtype	Type	Description
Job	Once-only	A job sweep processes the objects in a database table in one iteration.
Policy	Ongoing	A policy sweep processes the objects in a database table in an ongoing manner. In terms of implementation, a policy sweep consists of two main components: a policy object and a sweep object. For more information, see Opening a policy sweep.
Queue	Ongoing	A queue sweep processes the rows in a queue table in an ongoing manner. A queue sweep is a special type of sweep because it removes table rows as they are processed during an iteration.

• Deleting objects with sweeps
  You can delete objects by creating a policy sweep.
• Updating object retention with sweeps
  You can update object retention by creating either a job sweep or a policy sweep.
• Moving content with sweeps
  Use a job or policy sweep to move content for large numbers of documents and annotations from one storage area to another.
• Generating thumbnails
  A thumbnail is a small bitmap image of the first page of a content element. Using thumbnails allows users to quickly verify content before downloading or checking out documents.

Working with Queries - Constructing a SQL Statement

IBM FileNet P8, Version 5.2            

---

Working with Queries

Constructing a SQL Statement

The SearchSQL class provides helper methods to construct the SQL statement, or you can pass an existing SQL statement in to a SearchSQL instance. The helper methods are supplied for assistance in building SQL statements, and cannot provide the level of specification you can achieve with an independently constructed statement.

Note: You cannot combine these two manners of construction. The SQL statement must be specified in its entirety using either the SearchSQL helper methods or an independently constructed statement. However, for the purposes of development, you can use the helper methods to build the SQL statement, then use the SearchSQL.toString method to get the SQL statement string and further refine the SQL statement manually before passing it to SearchSQL.setQueryString.

Using the SearchSQL Methods

The general sequence for constructing a SQL statement using the SearchSQL methods is as follows:

Java™ Example

// Create the SearchSQL object.
SearchSQL sqlObject = new SearchSQL();

// (Optional) Specify the maximum number of records to be returned. This 

defaults to the
// value of ServerCacheConfiguration.NonPagedQueryMaxSize, if unspecified.
sqlObject.setMaxRecords(150);

// Specify the SELECT list using the setSelectList method.
String select = "r.Title, s.Title";
sqlObject.setSelectList(select);

// Specify the FROM clause using the setFromClauseInitialValue method.
// Symbolic name of class.
String myClassName1 = "Requirement";
// Alias name.
String myAlias1 = "r";
// Indicates whether subclasses are included.
boolean subclassesToo = false;
sqlObject.setFromClauseInitialValue(myClassName1, myAlias1, subclassesToo);

// For joins, specify an additional FROM clause using the 

setFromClauseAdditionalJoin 
// method. Symbolic name of class.
String myClassName2 = "Specification";
// Alias name.
String myAlias2 = "s";
// The property on the class specified for the setFromClauseInitialValue 
// method. This is one constituent in the underlying ON clause. The other 

constituent is 
// prop2.
String prop1 = "s.ApplicationName";
// The property on the class specified for this method (myClassName2). 

This is the other 
// constituent of the underlying ON clause for the join.
String prop2 = "r.ApplicationName";
// Indicates whether subclasses are included.
subclassesToo = false;
sqlObject.setFromClauseAdditionalJoin(
    JoinOperator.INNER, myClassName2, myAlias2, prop1, 
    JoinComparison.EQUAL, prop2, subclassesToo);

// Specify the WHERE clause using the setWhereClause method.
String whereClause = "r.Title LIKE '%P8%'";
sqlObject.setWhereClause(whereClause);

// Specify the ORDER BY clause using the setOrderByClause method.
String orderClause = "r.Title";
sqlObject.setOrderByClause(orderClause);

// Check the SQL statement constructed.
System.out.println("SQL: " + sqlObject.toString());

// Create a SearchScope instance and test the SQL statement.
SearchScope searchScope = new SearchScope(os);
// Uses fetchRows to test the SQL statement.
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, 

new Boolean(true));
  

C# Example

// Create the SearchSQL object.
SearchSQL sqlObject = new SearchSQL();

// (Optional) Specify the maximum number of records to be returned. 

This defaults to the
// value of ServerCacheConfiguration.NonPagedQueryMaxSize, if unspecified.
sqlObject.SetMaxRecords(150);

// Specify the SELECT list using the setSelectList method.
string select = "r.Title, s.Title";
sqlObject.SetSelectList(select);

// Specify the FROM clause using the setFromClauseInitialValue method.
// Symbolic name of class.
string myClassName1 = "Requirement";
// Alias name.
string myAlias1 = "r";
// Indicates whether subclasses are included.
bool subclassesToo = false;
sqlObject.SetFromClauseInitialValue(myClassName1, myAlias1, subclassesToo);

// For joins, specify an additional FROM clause using the 

setFromClauseAdditionalJoin 
// method. Symbolic name of class.
string myClassName2 = "Specification";
// Alias name.
string myAlias2 = "s";
// The property on the class specified for the setFromClauseInitialValue 
// method. This is one constituent in the underlying ON clause. The other 

constituent is 
// prop2.
string prop1 = "s.ApplicationName";
// The property on the class specified for this method (myClassName2). 

This is the other 
// constituent of the underlying ON clause for the join.
string prop2 = "r.ApplicationName";
// Indicates whether subclasses are included.
subclassesToo = false;
sqlObject.SetFromClauseAdditionalJoin(
    JoinOperator.INNER, myClassName2, myAlias2, prop1, 
    JoinComparison.EQUAL, prop2, subclassesToo);

// Specify the WHERE clause using the setWhereClause method.
string whereClause = "r.Title LIKE '%P8%'";
sqlObject.SetWhereClause(whereClause);

// Specify the ORDER BY clause using the setOrderByClause method.
string orderClause = "r.Title";
sqlObject.SetOrderByClause(orderClause);

// Check the SQL statement constructed.
System.Console.WriteLine("SQL: " + sqlObject.ToString());

// Create a SearchScope instance and test the SQL statement.
SearchScope searchScope = new SearchScope(os);
// Uses fetchRows to test the SQL statement.
IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);

Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.

The SearchSQL instance (sqlObject) can then be specified in the SearchScope parameter list to execute the search. See SQL Syntax Reference for detailed information about SQL statement syntax.

Using an Independently Constructed Statement

A SQL statement that conforms to IBM® FileNet® standards can be passed to SearchSQL in string format. You can use either the constructor SearchSQL(String), or the setQueryString method.

Java Example

// The SQL statement "SELECT DocumentTitle Id FROM Document WHERE DocumentTitle 

LIKE 
// '%Acct%'" retrieves documents by ID (GUID) that have a document 
// title containing the character pattern "Acct".
// You could store this statement in a String variable and pass it to the 
// setQueryString method, similar to this:
String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle 

LIKE '%Acct%'";
SearchSQL sqlObject = new SearchSQL();
sqlObject.setQueryString(mySQLString);

// Alternatively, you could use the SearchSQL(String) constructor:
// String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE 

DocumentTitle 
// LIKE '%Acct%'";
// SearchSQL sqlObject = new SearchSQL(mySQLString);
    
// The SearchSQL instance (sqlObject) can then be specified in the 
// SearchScope parameter list to execute the search. Uses fetchRows to test the 

SQL 
// statement.
SearchScope searchScope = new SearchScope(os);
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, new 

Boolean(true));
  

C# Example

// The SQL statement "SELECT DocumentTitle Id FROM Document WHERE DocumentTitle 

LIKE 
// '%Acct%'" retrieves documents by ID (GUID) that have a document 
// title containing the character pattern "Acct".
// You could store this statement in a String variable and pass it to the 
// SetQueryString method, similar to this:
String mySQLString = "SELECT DocumentTitle, Id FROM Document WHERE DocumentTitle 

LIKE '%Acct%'";
SearchSQL sqlObject = new SearchSQL();
sqlObject.SetQueryString(mySQLString);

// The SearchSQL instance (sqlObject) can then be specified in the 
// SearchScope parameter list to execute the search. Uses fetchRows to test the 

SQL 
// statement.
SearchScope searchScope = new SearchScope(os);
IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);

Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.

See SQL Syntax Reference for detailed information about SQL statement syntax.

Searching for Content

You can include content searches (full-text searches) in queries constructed using either the SearchSQL helper methods or independently constructed SQL statements. The CONTAINS function performs the content search functions. CONTAINS is used for explicit or fuzzy full-text queries, and can search for content in all CBR-enabled properties on the object, or a single CBR-enabled property.

See Content Searches and CBR Queries for more information.

Content Searches using the SearchSQL Methods

You can specify a content search by using the SearchSQL helper method, setContainsRestriction. The following example uses setContainsRestriction to search for the specified content in all IsCBREnabled properties of the Document class:

Note: The setContainsRestriction helper method does not provide the same level of functionality as the CONTAINS function can in an independently constructed SQL statement. The setContainsRestriction method does not accept a property parameter to constrain the full-text search to a specific property.

Java Example

 SearchSQL sqlObject = new SearchSQL();
            
String myClassName = "Document";
sqlObject.setSelectList("DocumentTitle");
sqlObject.setFromClauseInitialValue(myClassName, null, false);

String containsExpression = "'FileNet'";
sqlObject.setContainsRestriction(myClassName, containsExpression);

// Displays the SQL statement.
System.out.println("SQL: " + sqlObject.toString());
    
// Executes the content search.
SearchScope searchScope = new SearchScope(os);            
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, 

new Boolean(true)); 

C# Example

SearchSQL sqlObject = new SearchSQL();

string myClassName = "Document";
sqlObject.SetSelectList("DocumentTitle");
sqlObject.SetFromClauseInitialValue(myClassName, null, false);

string containsExpression = "'FileNet'";
sqlObject.SetContainsRestriction(myClassName, containsExpression);

// Displays the SQL statement.
System.Console.WriteLine("SQL: " + sqlObject.ToString());

// Executes the content search.
SearchScope searchScope = new SearchScope(os);            
IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);

Refer to the Searching for Database Rows example code for an illustration of iterating through the RepositoryRowSet.

Content Searches using Independently Constructed Statements

To specify a content search in an independently constructed SQL statement, add the CONTAINS function within the WHERE clause. Only one CONTAINS function can be used in a single SQL statement.

The following examples illustrate the different ways these content searches can be constructed:

Single Property Search

In this example, only content in the AccountName property will be searched (assuming IsCBREnabled is true for this property).

Java Example

String mySQLString = "SELECT DocumentTitle Id FROM Document d "
    + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject "
    + "WHERE CONTAINS(Name, 'FileNet')"; 
        
SearchSQL sqlObject = new SearchSQL(mySQLString);
    
// Executes the content search.
SearchScope searchScope = new SearchScope(os);            
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, 

new Boolean(true));
    

C# Example

String mySQLString = "SELECT DocumentTitle Id FROM Document d "
    + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject "
    + "WHERE CONTAINS(Name, 'FileNet')";

SearchSQL sqlObject = new SearchSQL(mySQLString);

// Executes the content search.
SearchScope searchScope = new SearchScope(os);            
IRepositoryRowSet rowSet = searchScope.FetchRows(sqlObject, null, null, true);

All Properties Search

In this example, the content of all properties on the object will be searched for a semantic correlation to "company" (assuming IsCBREnabled is true for all of these properties).

Java Example

String mySQLString = "SELECT DocumentTitle Id FROM Document d " 
    + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject " 
    + "WHERE CONTAINS(*, 'company')";
        
SearchSQL sqlObject = new SearchSQL(mySQLString);
    
 // Executes the content search.
SearchScope searchScope = new SearchScope(os);            
RepositoryRowSet rowSet = searchScope.fetchRows(sqlObject, null, null, 

new Boolean(true));

C# Example

String mySQLString = "SELECT DocumentTitle Id FROM Document d " 
    + "INNER JOIN ContentSearch cs ON d.This = cs.QueriedObject " 
    + "WHERE CONTAINS(*, 'company')";
        
SearchSQL sqlObject = new SearchSQL(mySQLString);
    
// Executes the content search.
SearchScope searchScope = new SearchScope(os);            
IRepositoryRowSet rowSet =  searchScope.FetchRows(sqlObject, null, null, true);
    

Searching Multiple Repositories

Any of the SearchScope methods can be used to search multiple repositories. The general sequence for specifying multiple repositories is as follows:

Java Example

// Create the ObjectStore array required as the initial SearchScope 
// parameter. (Assumes you have the object store objects.)
ObjectStore[] osArray = new ObjectStore[]{os1,os2};

// Create the SearchScope instance using the constructor for multiple object 
// stores, and specifying the merge mode.
SearchScope searchMultiple = new SearchScope(osArray, MergeMode.INTERSECTION);
  

C# Example

// Create the ObjectStore array required as the initial SearchScope 
// parameter. (Assumes you have the object store objects.)
IObjectStore[] osArray = new IObjectStore[]{os1,os2};

SearchScope searchMultiple = new SearchScope(osArray, MergeMode.INTERSECTION);

See Merge Mode for more information about merge mode settings.

Searching for Objects

Use the SearchScope.fetchObjects method to search for objects in a repository. The fetchObjects method returns a collection of IndependentObject objects. The general sequence for performing a query for objects is as follows:

Java Example

// Create a SearchSQL instance and specify the SQL statement (using the 
// helper methods).
SearchSQL sqlObject = new SearchSQL();
sqlObject.setSelectList("d.DocumentTitle, d.Id");
sqlObject.setMaxRecords(20);
sqlObject.setFromClauseInitialValue("Document", "d", false);  

// Check the SQL statement.  
System.out.println("SQL: " + sqlObject.toString()); 

// Create a SearchScope instance. (Assumes you have the object store 
// object.)
SearchScope search = new SearchScope(os);

// Set the page size (Long) to use for a page of query result data. 

This value is passed 
// in the pageSize parameter. If null, this defaults to the value of 
// ServerCacheConfiguration.QueryPageDefaultSize.
Integer myPageSize = new Integer(100);

// Specify a property filter to use for the filter parameter, if needed. 
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.setMaxRecursion(myFilterLevel);
myFilter.addIncludeType(new FilterElement(null, null, null, 

FilteredPropertyType.ANY, null)); 

// Set the (Boolean) value for the continuable parameter. This indicates 
// whether to iterate requests for subsequent pages of result data when the

 end of the 
// first page of results is reached. If null or false, only a single page of 

results is 
// returned.
Boolean continuable = new Boolean(true);

// Execute the fetchObjects method using the specified parameters.
IndependentObjectSet myObjects = search.fetchObjects(sqlObject, myPageSize,

 myFilter, continuable);
 

C# Example

// Create a SearchSQL instance and specify the SQL statement (using the 
// helper methods).
SearchSQL sqlObject = new SearchSQL();
sqlObject.SetSelectList("d.DocumentTitle, d.Id");
sqlObject.SetMaxRecords(20);
sqlObject.SetFromClauseInitialValue("Document", "d", false);

// Check the SQL statement.  
System.Console.WriteLine("SQL: " + sqlObject.ToString());

// Create a SearchScope instance. (Assumes you have the object store 
// object.)
SearchScope search = new SearchScope(os);

// Set the page size (Long) to use for a page of query result data. 

This value is passed 
// in the pageSize parameter. If null, this defaults to the value of 
// ServerCacheConfiguration.QueryPageDefaultSize.
int myPageSize = 100;

// Specify a property filter to use for the filter parameter, if needed. 
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.SetMaxRecursion(myFilterLevel);
myFilter.AddIncludeType(new FilterElement(null, null, null,

 FilteredPropertyType.ANY, null));

// Set the (boolean) value for the continuable parameter. This indicates 
// whether to iterate requests for subsequent pages of result data when the 

end of the 
// first page of results is reached. If null or false, only a single page of

 results is 
// returned.
bool continuable = true;

// Execute the fetchObjects method using the specified parameters.
IIndependentObjectSet myObjects = search.FetchObjects(sqlObject, myPageSize,

 myFilter, continuable);
    

See Constructing a SQL Statement for the general sequence and examples.

See Searching Multiple Repositories for the sequence and examples when searching multiple object stores.

Searching for Database Rows

Use the SearchScope.fetchRows method to search rows in the Content Engine database. The fetchRows method returns a collection of RepositoryRow objects. The general sequence for performing a query for database rows is as follows:

Java Example

// Create a SearchSQL instance and specify the SQL statement (using the 

helper methods).
SearchSQL sqlObject = new SearchSQL();
sqlObject.setSelectList("d.DocumentTitle, d.Id");
sqlObject.setMaxRecords(20);
sqlObject.setFromClauseInitialValue("Document", "d", false);  
    
// Check the SQL statement.  
System.out.println("SQL: " + sqlObject.toString()); 

// Create a SearchScope instance. (Assumes you have the object store object.)
SearchScope search = new SearchScope(os);

// Set the page size (Long) to use for a page of query result data. This value

 is passed 
// in the pageSize parameter. If null, this defaults to the value of 
// ServerCacheConfiguration.QueryPageDefaultSize.
Integer myPageSize = new Integer(100);

// Specify a property filter to use for the filter parameter, if needed. 
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.setMaxRecursion(myFilterLevel);
myFilter.addIncludeType(new FilterElement(null, null, null, 

FilteredPropertyType.ANY, null)); 

// Set the (Boolean) value for the continuable parameter. This indicates 
// whether to iterate requests for subsequent pages of result data.
Boolean continuable = new Boolean(true);

// Execute the fetchRows method using the specified parameters.
RepositoryRowSet myRows = search.fetchRows(sqlObject, myPageSize, myFilter, 

continuable);

// You can then iterate through the collection of rows to access the properties.
int rowCount = 0;
Iterator iter = myRows.iterator();
while (iter.hasNext()) {
    RepositoryRow row = (RepositoryRow) iter.next();
            
    String docTitle = row.getProperties().get("DocumentTitle").getStringValue();
    Id docId = row.getProperties().get("Id").getIdValue();
            
    rowCount++;
    System.out.print(" row " + rowCount + ":");
    System.out.print(" Id=" + docId.toString());
    if (docTitle != null) {
        System.out.print(" DocumentTitle=" + docTitle);
    }
    System.out.println();                            
} 

C# Example

// Create a SearchSQL instance and specify the SQL statement (using the helper

 methods).
SearchSQL sqlObject = new SearchSQL();
sqlObject.SetSelectList("d.DocumentTitle, d.Id");
sqlObject.SetMaxRecords(20);
sqlObject.SetFromClauseInitialValue("Document", "d", false);        

// Check the SQL statement.  
System.Console.WriteLine("SQL: " + sqlObject.ToString());

// Create a SearchScope instance. (Assumes you have the object store object.)
SearchScope search = new SearchScope(os);

// Set the page size (Long) to use for a page of query result data. This value 

is passed 
// in the pageSize parameter. If null, this defaults to the value of 
// ServerCacheConfiguration.QueryPageDefaultSize.
int myPageSize = 100;

// Specify a property filter to use for the filter parameter, if needed. 
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.SetMaxRecursion(myFilterLevel);
myFilter.AddIncludeType(new FilterElement(null, null, null, 

FilteredPropertyType.ANY, null));

// Set the (boolean) value for the continuable parameter. This indicates 
// whether to iterate requests for subsequent pages of result data.
bool continuable = true;

// Execute the fetchRows method using the specified parameters.
IRepositoryRowSet myRows = search.FetchRows(sqlObject, myPageSize, myFilter, 

continuable);

// You can then iterate through the collection of rows to access the properties.
int rowCount = 0;
foreach(IRepositoryRow row in myRows)
{
    string docTitle = row.Properties.GetProperty("DocumentTitle").GetStringValue();
    Id docId = row.Properties.GetProperty("Id").GetIdValue();
    
    rowCount++;
    System.Console.WriteLine(" row " + rowCount + ":");
    System.Console.WriteLine(" Id=" + docId.ToString());
    if (docTitle != null)
    {
        System.Console.WriteLine(" DocumentTitle=" + docTitle);
    }
    System.Console.WriteLine();                        
}
    

See Constructing a SQL Statement for the general sequence and examples.

See Searching Multiple Repositories for the sequence and examples.

Searching for Metadata

Use the SearchScope.fetchSearchableClassDescriptions method to search for metadata. The fetchSearchableClassDescriptions method returns a collection of ClassDescription objects. There is no SearchSQL parameter for this method. The general sequence for performing a query for metadata is as follows:

Java Example

// Create a SearchScope instance. (Assumes you have the object store 
// object.)
SearchScope search = new SearchScope(os);

// Specify the names of the classes containing the metadata you want to find.

 This must 
// be a String array of the identifiers (symbolic names, display names, 

or object IDs).
String[] myClassNames = new String[]{"Document", "Annotation"};

// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.setMaxRecursion(myFilterLevel);
myFilter.addIncludeType(new FilterElement(null, null, null, 

FilteredPropertyType.ANY, null));

// Execute the fetchSearchableClassDescriptions method using the specified 
// parameters.
ClassDescriptionSet myMetadata = 

search.fetchSearchableClassDescriptions(myClassNames, myFilter);
       

C# Example

// Create a SearchScope instance. (Assumes you have the object store 
// object.)
SearchScope search = new SearchScope(os);

// Specify the names of the classes containing the metadata you want to

 find. This must 
// be a String array of the identifiers (symbolic names, display names, 

or object IDs).
string[] myClassNames = new string[] { "Document", "Annotation" };

// Specify a property filter to use for the filter parameter, if needed.
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
int myFilterLevel = 1;
myFilter.SetMaxRecursion(myFilterLevel);
myFilter.AddIncludeType(new FilterElement(null, null, null, 

FilteredPropertyType.ANY, null));

// Execute the fetchSearchableClassDescriptions method using the specified 
// parameters.
IClassDescriptionSet myMetadata = search.FetchSearchableClassDescriptions

(myClassNames, myFilter);
    

See Searching Multiple Repositories for the sequence and examples.

Searching for Objects Using a Stored Search

To use a stored search to query for objects in a repository, call the SearchScope.fetchObjects method having StoredSearch, rather than SearchSQL as the initial argument in its signature. The fetchObjects method returns a collection of IndependentObject objects. The example executes a stored search existing in the repository.

Java Example

// Create a SearchScope instance. (Assumes you have the object store 
// object.)
SearchScope search = new SearchScope(os);

// Get the StoredSearch object.
StoredSearch ss=Factory.StoredSearch.fetchInstance(os, new 

(Id( "{9FEC3C69-57B2-4E29-872A-0EE452881555}"), null);

// Set the search parameters. Search template parameters only need to be

 provided if the persisted stored search needs to be modified at runtime.
SearchTemplateParameters searchParams = new SearchTemplateParameters();
searchParams.setContent(null);
searchParams.setMaximumRecords(50);

SearchTemplateWhereProperty whereProp = new SearchTemplateWhereProperty();
whereProp.setLiteral("application/vnd.oasis.opendocument.text");
whereProp.setItemId("35");
ArrayList alWhereProps = new ArrayList();
alWhereProps.add(whereProp);
searchParams.setWhereProperties(alWhereProps);

SearchTemplateSelectProperty selectProp = new SearchTemplateSelectProperty();
selectProp.setSymbolicName ("DocumentTitle"); 
selectProp.setItemId("1");
selectProp.setSortLevel(0));
selectProp.setSortOrder(SortOrder.DESCENDING);
ArrayList alSelectProps = new ArrayList();
alSelectProps.add(selectProp);
searchParams.setSelectProperties(alSelectProps);

// Set the page size (Long) to use for a page of query result data. 

This value is passed 
// in the pageSize parameter. If null, this defaults to the value of 
// ServerCacheConfiguration.QueryPageDefaultSize.
Integer myPageSize = new Integer(100);

// Specify a property filter to use for the filter parameter, if needed. 
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
myFilter.setMaxRecursion(1);
myFilter.addIncludeType(new FilterElement(null, null, null, 

FilteredPropertyType.SINGLETON_STRING, null)); 
        
// Set the (Boolean) value for the continuable parameter. This indicates 
// whether to iterate requests for subsequent pages of result data when the

 end of the 
// first page of results is reached. If null or false, only a single page of 

results is 
// returned.
Boolean continuable = Boolean.TRUE;

// Execute the fetchObjects method using the specified parameters.
IndependentObjectSet myObjects = search.fetchObjects(ss, "document", 

searchParams, myPageSize, myFilter, continuable);

// You can then iterate through the collection of objects to access the properties.
Iterator iterObjects = myObjects.iterator();
while (iterObjects.hasNext()) 
{
    IndependentObject object = (IndependentObject) iterObjects.next();
    Properties props = object.getProperties();
    Iterator iterProps = props.iterator();
    while (iterProps.hasNext() )
    {
        Property prop = (Property)iterProps.next();
        System.out.print("\nProperty: " + prop.getPropertyName() );
        if ( prop.getObjectValue() != null )
            System.out.print("  Value: " + prop.getObjectValue().toString() );
                  
        if (prop.getPropertyName().equalsIgnoreCase("FoldersFiledIn"))
        {
            if ( prop.getObjectValue() != null )
            {
                FolderSet fs = (FolderSet)prop.getIndependentObjectSetValue();
                Iterator iterFs = fs.iterator();
                while (iterFs.hasNext())
                {
                    Folder folder = (Folder)iterFs.next();
                    System.out.print("\r\tFolder Name: " + folder.get_FolderName() +
                        "   Folder Path: " + folder.get_PathName());
                }
            }
        }
    }
}
System.out.println("\nFinished searchForObjects");
       

C# Example

// Create a SearchScope instance. (Assumes you have the object store object.)
SearchScope search = new SearchScope(os);

// Get the StoredSearch object.
IStoredSearch ss = Factory.StoredSearch.FetchInstance

(os, new Id( "{9FEC3C69-57B2-4E29-872A-0EE452881555}"), null);

// Set the search parameters. Search template parameters only need to 

be provided if the persisted stored search needs to be modified at runtime.
SearchTemplateParameters searchParams = new SearchTemplateParameters();
searchParams.Content=null;
searchParams.MaximumRecords=25;

SearchTemplateWhereProperty whereProp = new SearchTemplateWhereProperty();
whereProp.Literal="application/vnd.oasis.opendocument.spreadsheet";
whereProp.ItemId="35";
IList<SearchTemplateWhereProperty> ListWhereProps = 

new List<SearchTemplateWhereProperty>();
ListWhereProps.Add(whereProp);
searchParams.WhereProperties = ListWhereProps;

SearchTemplateSelectProperty selectProp = new SearchTemplateSelectProperty();
selectProp.SymbolicName = "DocumentTitle";
selectProp.ItemId="1";
selectProp.SortLevel=0;
selectProp.SortOrder=SortOrder.NONE;
IList<SearchTemplateSelectProperty> ListSelectProps = new List<SearchTemplateSelectProperty>();
ListSelectProps.Add(selectProp);
searchParams.SelectProperties = ListSelectProps;

// Set the page size (Long) to use for a page of query result data. 

This value is passed 
// in the pageSize parameter. If null, this defaults to the value of 
// ServerCacheConfiguration.QueryPageDefaultSize.
int myPageSize = 100;

// Specify a property filter to use for the filter parameter, if needed. 
// This can be null if you are not filtering properties.
PropertyFilter myFilter = new PropertyFilter();
myFilter.SetMaxRecursion(1);
myFilter.AddIncludeType(new FilterElement(null, null, null,

 FilteredPropertyType.SINGLETON_STRING, null));

// Set the (boolean) value for the continuable parameter. This indicates 
// whether to iterate requests for subsequent pages of result data when the 

end of the 
// first page of results is reached. If null or false, only a single page of 

results is 
// returned.
bool continuable = true;

// Execute the fetchObjects method using the specified parameters.
IIndependentObjectSet myObjects = search.FetchObjects(ss, "document", searchParams myPageSize, myFilter, continuable);

// You can then iterate through the collection of rows to access the properties.
foreach (IIndependentObject obj in myObjects)
{
    IProperties props = obj.Properties;
    foreach (IProperty prop in props)
    {
        System.Console.Write("\nProperty: " + prop.GetPropertyName());
        if ( prop.GetObjectValue() != null )
            System.Console.Write("  Value: " + prop.GetObjectValue().ToString());
                  
        if (prop.GetPropertyName().Equals("FoldersFiledIn"))
        {
            if ( prop.GetObjectValue() != null)
            {
                IFolderSet fs = (IFolderSet)prop.GetIndependentObjectSetValue();
                                                
                foreach (IFolder folder in fs)
                {
                    System.Console.WriteLine("\n\tFolder Name: " 

+ folder.FolderName +
                        "   Folder Path: " + folder.PathName);
                }
            }
        }
    }
}
    

Querying Partitions

IBM Content Search Services support partitioning by date to address high-volume indexing of date-sensitive documents, such as e-mail messages archived in a Content Engine repository. IBM Content Search Services also supports partitioning documents that have specific values of certain string properties, as well as partitioning documents by both date and string. By using partitioning, you can reduce the number of IBM Content Search Services indexes that need to be searched, thus reducing query retrieval time.

Querying Using Date Partitions

To search IBM Content Search Services index partitions using date partitioning, the WHERE clause of a query must contain conditions on the date/time partitioning property; otherwise, all indexes or collections will be searched. For example, assume that a class has a custom property called "receivedDate" on which to partition documents, and the object store on which the class resides is configured to use "receivedDate" as the partitioned property, then the following query statement invokes a search on partitioned indexes or collections.

SELECT … FROM Document D INNER JOIN ContentSearch CS ON
        D.This=CS.QueriedObject WHERE CONTAINS(*,'dog') AND
        D.receivedDate >= 2008-10-26 AND D.receivedDate < 2009-03-25
  

The above query narrows the search to those indexes or collections with date ranges that overlap the specified range of values for the receivedDate property. If no indexes or collections cover the specified range of dates, then the query would return zero results.

For the search to be optimized, you must specify the conditions referencing the partitioning property (receivedDate in the above example) following the CONTAINS clause; otherwise, the parsing of the query can result in a search that scans the entire database, rather than the CBR index data identified in the CONTAINS clause.

Querying Using String Partitions

To search IBM Content Search Services index partitions, the WHERE clause of a query must contain conditions on the string property; otherwise, all indexes will be searched. For example, assume that a class has a custom property called "tenant" on which to partition documents, and the object store on which the class resides is configured to use "tenant" as the partitioned property, then the following query statement invokes a search on partitioned indexes.

SELECT ... FROM Document D INNER JOIN ContentSearch CS ON
        D.This=CS.QueriedObject WHERE CONTAINS(*,'dog') AND
        D.tenant='IBM'
  

Best Practices for Searches

Using Administration Console

Use the Administration Console for Content Platform Engine's Search tool to construct your query or to quickly validate that your query works as intended. Be sure to include the object reference "this" in the SELECT clause when attempting to execute any query examples (included in this documentation). For example, this query:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' 
    

must be entered into Query Builder in this form:

    SELECT d.this, d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' 

For more information on searching with Administration Console for Content Platform Engine, see Finding objects with queries.

Querying a DB2® Database

If DB2 table overflow support is enabled on an object store, queries against the DB2 database can fail under certain conditions. For more information, see DB2 Table Overflow Support: Query Impact.

Limit Rows Returned

Setting the values of certain properties can prevent a user from allocating excessive memory when making requests to the Content Engine server — a situation that could cause the server to become slow due to memory limitations.

Limit the number of rows returned from your query to a usable number, either by specifying your query's row selection criteria to achieve that result, or by deciding on that number in advance by setting the following ServerCacheConfiguration properties: QueryPageMaxSize, QueryPageDefaultSize, and NonPagedQueryMaxSize.

For queries that use the SQL option of COUNT_LIMIT to request a search result count, limit the number of rows that the Content Engine counts with the following ServerCacheConfiguration properties: QueryCountDefaultSize and QueryCountMaxSize.

In addition, the ObjectStore interface provides properties that limit the maximum amount of time that a query can consume. The DefaultQueryTimeLimit and the MaxQueryTimeLimit properties impose time limits on client-to-server query RPCs, and the QueryDatabaseTimeout property limits query execution at the database level.

Avoid Non-Indexed Ordering and Searching

Avoid referencing any non-indexed column in a JOIN, WHERE, or ORDER BY clause. For example, optimize the following query by creating an index on Document.DocumentTitle (database column DocVersion.xxx_documenttitle):

    SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' 

Note: The Document class maps to the DocVersion table.

Also, avoid WHERE clauses with the LIKE operator when the searched-for column value does not permit the effective use of indexes. For example:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle LIKE '%abc' 

In this example, even if an index exists for the DocumentTitle column, the query cannot use it due to the leading wildcard ("%") in the searched-for column value. Code your query to return a relatively small number of rows by placing a sufficient number of characters before the wildcard. In search dialogs, allow end users to perform "Starting with" searches (such as for "abc%") but not "Contains" searches (such as for "%abc%").

Queries that do not make effective use of indexes can potentially cause the database to lock the table. Other users can then be blocked from updating the table for the duration of the query, causing checkins to time out, and other such problems.

Avoid Non-Function-Indexed Case Insensitive Comparisons (Oracle/DB2)

Avoid any column value comparison resulting from a JOIN, WHERE, or ORDER BY clause for any non-indexed column, or for any column belonging to an index, that does not directly or indirectly use the LOWER function. You should follow this guideline in these circumstances:

• You are working with an object store that uses Oracle or DB2 as the database engine.
• You have forced case insensitive searches by setting the ObjectStore.ForceCaseInsensitiveSearch property to true, and the Oracle or DB2 database has not been natively configured for case insensitivity (and so setting ForceCaseInsensitiveSearch to true has a practical effect).

For DB2, generate a column by applying the LOWER function to a pre-existing column, and then create an index on the generated column. For Oracle, create a function based index. For example, the index LOWER(DocVersion.xxx_documenttitle) makes the following queries more efficient:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' 
    SELECT d.Id, d.DocumentTitle, d.Creator FROM Document 

d ORDER BY d.DocumentTitle 
    

Note: The Document class maps to the DocVersion table.

Avoid Non-Indexed Property Searching

Ensure that at least one WHERE condition property is selective (it restricts the rows returned) and is indexed. For example, optimize the following query by creating an index on Document.DocumentTitle:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc' 
    

Note: The Document class maps to the DocVersion table.

Also, avoid WHERE clauses with the LIKE operator when the searched-for column value does not permit the effective use of indexes. For example:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle LIKE '%abc' 

In this example, even if an index exists for the DocumentTitle column, the query cannot use the index due to the leading wildcard ("%") in the searched-for column value. Code your query to return a relatively small number of rows by placing a sufficient number of characters before the wildcard. In search dialogs, allow end users to perform "Starting with" searches (such as for "abc%") but not "Contains" searches (such as for "%abc%").

Queries that do not make effective use of indexes can cause the database to escalate locks to the page and table level. Other users can then be blocked from updating the table for the duration of the query, causing checkins to time out and other related problems.

Avoid Unnecessary Object Type Searches

Avoid unnecessarily searching for subclass types by adding the EXCLUDESUBCLASSES operator to your query (a query has an implicit INCLUDESUBCLASSES operator by default).

For example, as a result of the implicit INCLUDESUBCLASSES, the following query can return objects belonging to a Document subclass, in addition to those belonging to the Document class:

    SELECT d.Id FROM Document d WHERE DocumentTitle = 'MyDoc' 

Presuming only objects from the Document class are needed, instead of writing the query as:

    SELECT d.Id FROM Document d WHERE DocumentTitle = 'MyDoc' AND ISCLASS(d, 

Document) 

use the EXCLUDESUBCLASSES operator in this manner:

    SELECT d.Id FROM Document d WITH EXCLUDESUBCLASSES WHERE DocumentTitle =

 'MyDoc' 

See SQL Syntax Reference for more information about the EXCLUDESUBCLASSES and INCLUDESUBCLASSES operators.

Avoid Unnecessary Column Returns

Avoid returning all of the columns in a table when only some are needed. For example, instead of:

  
    SELECT d.* FROM Document d WHERE d.DocumentTitle = 'MyDoc'

specify the needed columns, as in this example:

    SELECT d.Id FROM Document d WHERE d.DocumentTitle = 'MyDoc'

This minimizes the amount of data transmitted over the network. Also, when all of the columns in the SELECT clause are indexed, the data for the query might be retrieved directly from the index, making the physical row lookup unnecessary.

Avoid Complex Table Linkages

Avoid referencing three or more tables in a query. More tables can degrade query performance, and complicate performance tuning efforts.

Avoid Unnecessary Result Row Ordering

Avoid an unnecessary ORDER BY clause. Explicit row ordering may be unnecessary when you can rely on your query returning its results in a particular order as a side effect of an indexed search. For example, the ORDER BY clause in the following query may be unnecessary since a composite index exists for the Container table on (parent_container_id, name):

    SELECT f.FolderName FROM Folder f WHERE f.Parent = Object('/sub1/sub1a') 
        AND f.IsHiddenContainer = false ORDER BY f.FolderName 

Note: The Folder class maps to the Container table.

With a non-continuable search, it is best to remove either the ORDER BY, or ORDER BY f.Parent, F.FolderName clause on the existing columns in the composite index.

With a continuable search, it is best to use the ORDER BY f.Parent, F.FolderName clause; otherwise, when the ORDER BY Id is automatically appended to the search, a sort operation will be necessary.

As another example, rather than basing a search on the indexed property OrderDate:

    select D.OrderDate, D.OrderNumber from Document d where d.OrderDate < ? 

ORDER BY D.OrderNumber

If the search results do not require a sort on OrderNumber, search on OrderDate instead:

    select D.OrderDate, D.OrderNumber from Document d where d.OrderDate < ? 

ORDER BY D.OrderDate

By using OrderDate for the ORDER BY clause:

• You avoid a sort step in the query, because the query will be using the OrderDate index.
• The database query optimizer will tend to use the index on OrderDate.
• With continuable searches, when there is a composite index on "OrderDate, Id" the ORDER BY D.OrderDate clause is required to ensure the SORT operation is removed. The Content Engine automatically appends an ORDER BY Id to the search.

Avoid Property Searches using the LIKE operator and Force Case-Insensitive Search (DB2 LUW)

Enabling Force Case-Insensitive Search (FCIS) for an object store causes Content Engine queries to be issued with the LOWER operator on string columns and values in the WHERE clause. In order for the search to be fast, an index must be created on a generated column which uses the LOWER function. For instructions on indexing properties when using FCIS, see Creating database indexes for case-insensitive search.

Note, however, that for a DB2 LUW database, searches will not use an index if the LIKE operator is used and FCIS is enabled. This is a limitation of DB2 LUW. DB2 cannot rewrite a query condition that uses LOWER to access the generated index for that column. This limitation results in slow searches when using the LIKE operator and FCIS.

For example, this Content Engine SQL cannot use a generated column index on DB2 LUW:

WHERE myProperty LIKE 'abc%'

Therefore, whenever possible, avoid using LIKE on DB2 LUW with FCIS, for single-value property searches and especially for multi- valued property searches.

Regardless of the database type and the FCIS setting on the object store, keep in mind that poorly constructed property searches result in poor retrieval performance. For example:

• Avoid property searches that cannot leverage column indexes.
  A database index cannot be used in a query using the LIKE operator with a wildcard at the front, for example:
  

  WHERE myListProperty LIKE '%abc%'

  A much faster search avoids LIKE, for example:
  

  WHERE 'abc' IN myListProperty

  When using the IBM FileNet Workplace XT or IBM Content Navigator client application, which provides predefined lists of SQL operators, avoid the CONTAINS operator or the LIKE operator with a wildcard at the front. Instead, use the INCLUDES or INCLUDE ALL operator.
  For more information, see Avoid Non-Indexed Property Searching and Avoid Non-Indexed Ordering and Searching.
• For multiple list property conditions separated by the OR operator, use the INTERSECTS operator. See Use INTERSECTS Operator for queries with multivalued properties.

Avoid Subqueries (Oracle)

Avoid using subqueries and operators that indirectly generate subqueries whenever you are querying an object store that uses Oracle as the database engine. Potential subquery related issues exist with the Oracle optimizer. Specifically, use an INNER JOIN instead of a subquery (however, see also the guideline on avoid complex table linkages). For example, rewrite the potentially slow query:

    SELECT d.Id FROM Document d WHERE d.Id IN (SELECT b.Bp8ObjectGuid FROM Bp8Attachment b)

in this functionally equivalent form:

    SELECT d.Id FROM Document d INNER JOIN Bp8Attachment b ON d.Id = b.Bp8ObjectGuid 

Also, because the Content Engine uses a subquery to implement the INFOLDER operator, avoid using that operator. For example, rewrite the query:

    SELECT f.FolderName FROM Folder f WHERE f.this INFOLDER '/sub1/sub1a' AND f.IsHiddenContainer = false 

in this manner:

    SELECT f.FolderName FROM Folder f WHERE f.Parent = OBJECT('/sub1/sub1a') AND f.IsHiddenContainer = false 

Also, rewrite a query like this:

    SELECT d.id FROM Document d WHERE d.This INFOLDER '/sub1/sub1a' 

in this more efficient form:

    SELECT d.Id FROM Document d INNER JOIN ReferentialContainmentRelationship r ON d.This = r.Head 
        WHERE r.Tail = OBJECT('/sub1/sub1a') 

See SQL Syntax Reference for more information about the INFOLDER operator.

Use INTERSECTS Operator for queries with multivalued properties

Avoid queries that contain two or more multivalued property conditions in an OR clause. Such queries can result in query timeouts or unacceptable query performance because it can be difficult for the database engine to create an optimal query plan.

For example, do not use a query like the following:

SELECT d.Id FROM Document WHERE 'value1' IN ListPropertyString OR 'value2' IN ListPropertyString OR 'value3' IN ListPropertyString

Instead, use the INTERSECTS operator, as follows. It greatly improves performance.

SELECT d.Id FROM Document WHERE ListPropertyString INTERSECTS ('value1', 'value2', 'value3')

For more information, see INTERSECTS Operator.

Avoid search delays caused by security filtering

Avoid searches with large result sets that filter documents on security to a small number of rows.

Content Engine applies security and removes a user's access to objects after the objects are retrieved from the database engine. When a search finds a large number of rows (more than a few thousand) in the database, the total time to retrieve all of the rows (the database-to-Content Engine round trip time) can be considerable. If the search is continuable and there is little security filtering, the first page can be returned quickly, but if security filtering greatly limits the rows, many rows will have to be retrieved in order to fill the page.

To avoid this situation, design your application such that the search criteria includes a limiting property or other criteria (such as belonging to a particular folder) on which to filter. As an example, you could create a custom property, such as myRole, and add the property as additional criteria to your query in the form of "WHERE myProperty = 'myRole'". You could also choose to restrict the document search to a specific folder, such as "WHERE ... INFOLDER 'myFolder'".

Query Syntax

See the SQL Syntax Reference documentation for information on how to construct SQL statements. SQL statements need to follow the IBM FileNet standard, which generally conforms to SQL-92, with extensions for IBM FileNet specific constructs.

Filenet Upgrade Errors (Problems that can occur when you are updating from Filenet 4.0 to 5.2)

Filenet Upgrade Errors (Problems that can occur when you are upgrading from Filenet 4.0 to 5.2)

Coming soon...

Case Analyzer Upgrade errors (Problems that can occur when you are updating from Filenet 4.0 to 5.2)

Case Analyzer Upgrade errors (Problems that can occur when you are updating from Filenet 4.0 to 5.2)

Coming soon...