The following line of code from the program listing below sets up the DBTOOL run time parameters in the "input.txt" file to run the Record Validation Option. It can be modified to fit requirements to use different DBTOOL Options and different input values.
PUT UNFORMATTED "9~n3~n4~nall~n" + STRING (_file-number) + "~n0".
A legend for the parameters in the above line are:
9 = Enable file logging.
~n = carriage return.
3 = Record Validation Option.
~n = carriage return.
4 = Connection Type (0=single-user 1=self-service >1=#threads)
~n = carriage return.
all = rowid
~n = carriage return.
_file-number = The table number supplied by the "FOR EACH" loop.
~n = carriage return.
0 = Verbose Logging Level (0,1,2, and 3) 0 is the least verbose, 3 is most verbose and reserved for development inspection.
The following line of code must be edited to reflect the name of the database instead of the Sports2000 database:
OS-COMMAND SILENT VALUE ("dbtool sports2000 < input.txt").
The program generates output file logs using the format: "dbtool_ + <_file-name>",
Where: _file-name is the name of the database table.
Example: dbtool_customer.
Before running this code ensure there is enough disk space on the system, as there will be one output file generated for each table in the database.
The size of the file is dependent on:
- The verbose logging level chosen. This code example uses the recommended least verbose logging level which results in relatively small output files if no errors are detected (65 - 113 bytes). See the example output below for how the verbose level affects the size of the output file
- The number of records in the table and
- The number and type of errors detected.
This code outlines the simplest example of how to use ABL code to generate the required input and run DBTOOL online. DBTOOL does a sequential scan one Area at a time so choosing one table at a time and repeating for each table is
not performant. Performance could be improved by modifying the query to choose "all tables" in each area then starting multiple DBTOOL sessions, each running on a different Area. At the time of writting, DBTOOL does not allow selection of multiple distinct tables or areas. Another method is described in the code example provided in Article
How to detect fields with problematic SQL-WIDTH with 4GL/ABL
The script is formatted for Windows batch scripts, this can be modified to suit individual OS scripting requirements.
/* To run DBTOOL Record Validation Table by Table */
FOR EACH _file NO-LOCK WHERE _Tbl-Type = "T":
OUTPUT TO Value("input.txt").
/* 3. Record Validation Option. verbose level 0 multi-user*/
PUT UNFORMATTED "9~n3~n4~nall~n" + STRING (_file-number) + "~n0".
OUTPUT CLOSE.
OS-COMMAND SILENT VALUE ("dbtool sports2000 < input.txt").
OS-RENAME VALUE ("dbtool.out") value ("dbtool_" + _file-name).
END.
/* To create a .bat script to run DBTOOL Record Validation Table by Table */
DEFINE STREAM data1.
DEFINE STREAM data2.
OUTPUT STREAM data2 TO "dbtoolscript.bat".
FOR EACH _file NO-LOCK WHERE _Tbl-Type = "T":
OUTPUT STREAM data1 TO value ("dbtoolin_" + _file-name).
/* 3. Record Validation Option. verbose level 0 multi-user*/
PUT STREAM data1 UNFORMATTED "9~n3~n4~nall~n" + STRING (_file-number) + "~n0".
OUTPUT STREAM data1 CLOSE.
PUT STREAM data2 UNFORMATTED
"@echo off" SKIP
"ECHO %DATE% %TIME% TABLE:" + _file-name " STARTING ..." SKIP
"CALL dbtool sports2000 < dbtoolin_" + _file-name + " > NUL " SKIP
"ren dbtool.out dbtoolout_" + _file-name SKIP
"ECHO %DATE% %TIME% TABLE:" + _file-name " done!!" SKIP
.
END.
OUTPUT STREAM data2 CLOSE.
Example of the input file created:
9
3
4
all
2
0
Example output to provide a preview of the varying output content and file sizes based on the verbose level used against a single table run without errors:
Verbose logging 0 size 67 bytes
Total records read: 9418
Record format errors found: 0
Verbose logging 1 size 433,205 bytes
...
No error(s) found during validation of 10811.
No error(s) found during validation of 10816.
Total records read: 9418
Record format errors found: 0
Verbose logging 2 size 800,505 bytes
...
Rec size: 129 (0x81) Num Frags: 1
No error(s) found during validation of 10810.
Rec size: 133 (0x85) Num Frags: 1
No error(s) found during validation of 10811.
Total records read: 9418
Record format errors found: 0
Verbose logging 3 size 12,566,070 bytes
...
Rec size: 133 (0x85) Num Frags: 1
Offset # Info Ent Len
0x 0 1: skip tbl: 0x 2 0000: e700 0200 73
0x 5 1: vector : 0x b 0000: fa00 0b00 0102 0113 fdfd fdfd fdff
0x 13 2: : 0x 3 0000: 0300 ade3
0x 17 3: : 0x 18 0000: 1865 6164 4520 5151 5151 5151 5151 2070
0010: 7070 7070 7070 7070 70
0x 30 4: : 0x 0 0000: 00
0x 31 5: : 0x 0 0000: 00
0x 32 6: : 0x 0 0000: 00
0x 33 7: : 0x 0 0000: 00
0x 34 8: : 0x c 0000: 0c74 6865 7265 616e 6462 6163 6b
0x 41 9: : 0x 0 0000: 00
0x 42 10: : 0x 0 0000: 00
0x 43 11: : 0x 7 0000: 0752 5252 5252 3939
0x 4b 12: : 0x 21 0000: 2139 3943 4343 4343 4343 4343 2033 336c
0010: 6c6c 6c6c 6c6c 6c6c 6c6c 6c6c 6c6c 6c6c
0020: 6c6c
0x 6d 13: : 0x 3 0000: 0380 1500
0x 71 14: : 0x 0 0000: 00
0x 72 15: : 0x 5 0000: 054e 6574 3330
0x 78 16: : 0x 0 0000: 00
0x 79 17: : 0x 9 0000: 0939 3933 3320 3838 384f
0x 83 18: : 0x 0 0000: 00
0x 84 19: : 0x 0 0000: 00
No error(s) found during validation of 10811.
Page Reader Thread 1 done. rtc: 0
Total records read: 9418
Record format errors found: 0
