What is DBTOOL?
DBTOOL is a character mode diagnostic tool reports on possible Record Format, Index Space, SQL width or Date issues that are identified and optionally fixes SQL Width and index space violations.
The DBTOOL utility was first included in Progress 9.1D06 Service Pack, 9.1E and later and is included in the DLC/bin executable's. More menu items and reporting granularity were added in future major and minor releases.
How to use DBTOOL ?
To access the DBTOOL menus online or offline, from a PROENV shell run :
$ dbtool <dbname>
DATABASE TOOLS MENU
1. SQL Width & Date Scan w/Report Option - To report on SQL width or date violations
2. SQL Width Scan w/Fix Option - To find width violations over the specified percentage of the current maximum width, and increase the SQL Width when found. (The Padding % was introduced in OpenEdge 10.x)
- DBTOOL Options 1 and 2 are the most common use-case in application environments where ABL and SQL co-exist
- When the Max Width of a field exceeds the SQL Width then this can cause problems when executing SQL queries, typically "Column in table has value exceeding its max length or precision" or "Overflow error (7485)" in combination with an undersized -SQLStmtCache.
- In deployment situations, DBTOOL will assure the exported schema MAX-WIDTH is correctly sized with respect to sql width display for the current production field data sizes.
- In OpenEdge 11.6, Autonomous Schema Update (-SQLWidthUpdate) was added as a database feature to resolve the SQL width problem by updating the SQL column width in schema automatically when Authorized Data Truncation (-SQLTruncateTooLarge) occurs for data in a CHARACTER | VARCHAR column. For further information refer to Article Where to enable the -SQLTruncateTooLarge and -SQLWidthUpdate features
3. Record Validation - To compare the physical storage of the record to the schema definition and reports discrepancies.
4. Record Version Validation - To validate record schema versioning before and after the records are updated in memory by the tool
5. Read or Validate Database Blocks - To validate the information in data block headers depending on the level of validation chosen. Refer to Article DBTOOL: Read or Validate Database Blocks Validation levels
6. Record Fixup - To scan records for indications of possible corruption. (This option was added in OpenEdge 10.0B04 Service Pack). Refer to Article DBTOOL Option 3 - gives warning about the first record fragment is < 10 bytes
7. Schema Validation - To identify errors in schema records for word indexes (Added in 10.1A as _StorageObject Record Validation)
8. Disable Object Locking Protection -
9. Enable/Disable File Logging - To enable or disable redirection output to a file named dbtool.out as opposed to the screen.
10. Index Space Validation - To check and report on index space allocation errors
11. Index Space Fixup - Fixes errors discovered with Index Space Validation
12. Schema Fixup - Fixes rare types of schema corruption.
After the required DBTOOL Option is selected the following prompt appears:
<connect>:(0=single-user 1=self-service >1=#threads)?
-
If the database is offline, select 0 to run the tool single-user,
-
If the database is online, enter a value "n" to run shared-memory multi-threaded, where n = number of CPU's (i.e. one thread per CPU)
After a valid connection code is entered, prompts specific to the functionality of the selected DBTOOL Option appear.
- When prompted: enter a specific table number or all tables for the option to scan:
<table>: (Table number or all)?
The specific table number can for example be found through the following 4GL Query:
FOR EACH _file WHERE _file-number > 0 AND NOT _file-name BEGINS "SYS" NO-LOCK:
DISPLAY _FILE._file-name _file-number.
END.
- Depending on the DBTOOL Option selected, a prompt for the RECID may appear:
<recid>: (recid or all)?
Unless a specific recid is known from error message analysis or integrity reporting, scan all recid's.
- When prompted for the Storage Area number:
<area>: (Area number or all)?
The Storage Area Number will be for the Storage Area that houses the table that the recid(s) in the previous prompts belong to.
Given the table number for example, the related Storage Area number can be found through the following 4GL query:
FIND _Storageobject WHERE _Storageobject._object-type = 1 AND
_Storageobject._object-number = <tablenumber>.
DISPLAY _Storageobject._area-number.
- When a prompt for the verbosity level appears:
<display>: (verbose level 0-3)?
Use verbosity levels above 1 only if explicitly instructed by Progress Technical Support. The varying output content and file sizes based on the verbose level used against a single table are detailed in Article:
