Note that JCR requires at least READ_COMMITED isolation level and other RDBMS configurations can cause some side-effects and issues. So, make sure proper isolation level is configured on database server side.
RDBMS reindexing feature uses queries based on LIMIT and OFFSET clauses which are disabled by default. However, you can enable them by executing the following.
$ db2set DB2_COMPATIBILITY_VECTOR=MYS $ db2stop $ db2start
Statistics are 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
If you do not want to enable the LIMIT/OFFSET clauses, you can still use "db2-mys" as dialect. However, remember that the indexing is 120 times slower.
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.
ANALYZE TABLE JCR_SITEM, JCR_SVALUE
When using the RDBMS re-indexing, you need to set
enable_seqscan" to "
off" or "
to at least "50".
Though the PostgreSQL server performs query optimization automatically, you can manually call the 'ANALYZE' command to collect statistics which can affect on the performance.
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.
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.
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.
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