Enterprise Manager Java App: Automatic Database Analysis
In BBj 13.0 and higher, the Enterprise Manager Java app has been superseded by a new browser Enterprise Manager and Eclipse plug-in. See Enterprise Manager - Database: Query Analysis.
Overview
In BBj 10.0 and higher, automatic database analysis is a powerful feature that removes the necessity of a BBjServices administrator to explicitly run a database analysis operation on their database. Database (or table) analysis is a feature where the BBj SQL engine analyzes the tables in the database to generate information used for determining the best possible way to optimize SQL queries run against that database. Without this information, the SQL engine must make a generic “guess” as to how to optimize a query and may or may not choose the best option. SQL query performance is highly dependent on the availability of this information.
How It Works
The BBj database engine examines tables each time they are accessed to determine if an analysis is warranted. The following criteria is currently used to determine if a table should be analyzed or reanalyzed:
- The table has not yet been analyzed
- Since
the last time the table was analyzed,
- the record count has increased by more that 50%
- one or more indices were created or dropped from the table (also includes keys added or removed from the file using non-database operations or tools such as SQL or the Enterprise Manager)
If any of these criteria are met, a table analysis job starts on that table and enters it into the analysis queue where it starts as soon as there is an available time slot for it to run. The analysis engine intentionally limits the number of simultaneous analysis processes so that it does not interfere with the running of applications or queries on that server. To view the progress of analysis jobs and the order of queued tables, simply click on the “Table Analysis Queue” item in the navigator on the left side of the Enterprise Manager application window.
How it Defaults
An important consideration is whether to leave automatic table analysis enabled for a database or if it should be disabled and analyzed manually. In most cases, automatic analysis should simply be enabled, which is the default setting on setup of a new database in the Enterprise Manager. However, if your application has the occasion to remove data files or tables that are part of the database, it may be necessary to disable automatic table analysis since an analysis operation will prevent a file currently being analyzed from being removed.
An alternative to disabling this powerful, convenient feature is to use the BBj Admin API to programmatically ensure that a database does not have any table analysis processes running that would prevent manipulation of tables in that database. This can be done with only a few lines of BBj code and prevent anyone from an application or administrative tool from starting an analysis operation on that database until the application is finished performing its specific task. The following code sample demonstrates how to perform this task. When table analysis is disabled, no analysis jobs will be started on the specified database, and any running or queued jobs on that database will be terminated and removed from the queue:
rd_dbserver$ = "localhost"
rd_dbport = 2002
rd_dbssl = 0
rd_user$ = "admin"
rd_password$ = "admin123"
adminBase! = com.basis.api.admin.BBjAdminFactory.getBBjAdmin(
: java.net.InetAddress.getByName(rd_dbserver$),
: rd_dbport,
: rd_dbssl,
: rd_user$,
: rd_password$)
REM Disable table analysis on the ChileCompany database
adminBase!.setTableAnalysisEnabled("ChileCompany", 0)
REM Do some application operations
REM Enable table analysis again on the ChileCompany database
adminBase!.setTableAnalysisEnabled("ChileCompany", 1)