Home

What is DBTOOL?

000020119
Product: Progress
Version: 9.1D06, 9.1E
Product: OpenEdge
Version: 10.x, 11.x
OS: All Supported Operating Systems
Other: Database, DBTOOL
What is DBTOOL?
How to identify problems with the SQL Width for a database?
How to identify problems with the SQL Width when using DataServers ProToXXX
How to change the SQL width outside the Data Dictionary?
How to change SQL Width from command line?
How to change MAX Width from command line?
How to address OpenEdge SQL-92 error 210012?
SQL-Width is the current SQL Width for the field listed. Max-Width represents the size of the longest piece of data in the field. If 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"

DBTOOL is a character mode diagnostic tool that identifies and reports on possible record format, index space, SQL width or date issues and fixes SQL Width and index space violations.
 
This tool was first included in Progress 9.1D06 Service Pack, 9.1E and later and is included in the DLC/bin executables.
More menu items and reporting granularity were added in major and minor releases.
 
To access the DBTOOL menus, from a PROENV shell run :  
 
$   dbtool <dbname>
 
The following options menu are presented by DBTOOL (OE11.4).
 
DATABASE TOOLS MENU
-------------------
 
1. SQL Width & Date Scan w/Report Option
2. SQL Width Scan w/Fix Option
3. Record Validation
4. Record Version Validation
5. Read  or Validate database block(s)
6. Record fixup
7. Schema Validation
9. Enable/Disable File Logging
10. Index Space Validation
11. Index Space Fixup
12. Schema Fixup
 
Q. Quit
 
Where:
Option  1: Will report on SQL width or date violations
Option  2: Will find width violations over the specified percentage of the current maximum width, and increase the SQL width when found. (Padding % introduced in OpenEdge 10.x)
Option  3: Will compare the physical storage of the record to the schema definition and reports discrepancies.
Option  4: Will validate record schema versioning before and after the records are updated in memory by the tool
Option  5: Will validate the information in database block headers depending on the level of validation chosen
Option  6: Will scan records for indications of possible corruption. (This option was added in Service Pack 10.0B04)
Option  7: Will identify errors in schema records for word indexes (Added in 10.1A as  _StorageObject Record Validation)
Option  9: Will enable or disable redirection of the tool output to a file named dbtool.out as opposed to the screen.
Option 10: Will check and report on index space allocation errors
Option 11: Will fix errors discovered with Index Space Validation
Option 12: Fixes rare types of schema corruption.
 
After the required 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 self-service 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 selection appear.
  1. 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 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.
  1. Depending on the 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.
  1. 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 prompt 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.
  1. 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 
References to Other Documentation:

OpenEdge Data Management: Database Administration -> Reference -> Other Database Administration Utilities -> DBTOOL Utility

Progress Articles):

000073671, How to speed up DBTOOL sql-width fix  
000061006, DBTOOL: Read or Validate Database Blocks Validation levels  
000014223, How to create an input file with ABL to run a DBTOOL SQL Width Scan w/Fix Option for all Tables of a database from a script.   
000045840, How to create an input file with ABL to run a DBTOOL Record Validation report for each Table in a database?   
 
9/26/2018 8:36 AM