Statistics is collected automatically starting from DB2 Version 9, however it is needed to launch statistics collection manually during the very first start, otherwise it could be very long. You need to run the following ' RUNSTATS' command for JCR_SITEM (or JCR_MITEM) and JCR_SVALUE (or JCR_MVALUE) tables.

RUNSTATS ON TABLE <scheme>.<table> WITH DISTRIBUTION AND INDEXES ALL

To prevent any consistency issues, ensure that InnoDB is configured as the default MySQL engine (instead of MyISAM by default) before launching your application for the very first time. Otherwise, when the application creates the tables, MyISAM will be used as the MySQL engine which is not transactional and does not support integrity constraints. Even if later you switch to InnoDB using an alter table, all the integrity constraints would be missing as they would have been removed tables at the time of the table creation.

MyISAM is not supported due to its lack of transaction support and integrity check. Use it only if you do not expect any support and if performances in read accesses are more important than the consistency in your usecase. Therefore, the mysql-myisam and mysql-myisam-utf8 dialects are only dedicated to the community.

MySQL relies on collected statistics for keeping track of data distribution in tables and for optimizing join statements, but you can manually call 'ANALYZE' to update statistics if needed.

For example:

ANALYZE TABLE JCR_SITEM, JCR_SVALUE

When using the RDBMS re-indexing, you need to set "enable_seqscan" to "off" or "default_statistics_target" to at least "50".

Though the PostgreSQL/PostgrePlus server performs query optimization automatically, you can manually call the 'ANALYZE' command to collect statistics which can affect the performance.

For example:

ANALYZE JCR_SITEM
ANALYZE JCR_SVALUE

For a version prior to 9.1, the standard_conforming_strings parameter is enabled, you need to use "pgsql-scs" as dialect.

One more mandatory JCR requirement for underlying databases is a case sensitive collation. Microsoft SQL Server both 2005 and 2008 customers must configure their server with collation corresponding to personal needs and requirements, but obligatorily case sensitive. Refer here for more information on selecting SQL Server Collation.

MS SQL DB server's optimizer automatically processes queries to increase performance. Optimization is based on statistical data which is collected automatically, but you can manually call Transact-SQL by the 'UPDATE STATISTICS' command which in very few situations may increase performance.

For example:

UPDATE STATISTICS JCR_SITEM
UPDATE STATISTICS JCR_SVALUE

Sybase DB Server optimizer automatically processes queries to increase performance. Optimization is based on statistical data which is collected automatically, but you can manually call Transact-SQL by the 'update statistics' command which may increase performance in very few situations.

For example:

update statistics JCR_SITEM
update statistics JCR_SVALUE

Oracle DB automatically collects statistics to optimize performance of queries, but you can manually call the 'ANALYZE' command to start collecting statistics immediately which may improve performance.

For example:

ANALYZE INDEX JCR_PK_SITEM COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SITEM_PARENT_FK COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SITEM_PARENT COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SITEM_PARENT_NAME COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SITEM_PARENT_ID COMPUTE STATISTICS
ANALYZE INDEX JCR_PK_SVALUE COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SVALUE_PROPERTY COMPUTE STATISTICS
ANALYZE INDEX JCR_PK_SREF COMPUTE STATISTICS
ANALYZE INDEX JCR_IDX_SREF_PROPERTY COMPUTE STATISTICS
ANALYZE INDEX JCR_PK_SCONTAINER COMPUTE STATISTICS