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.

How to create an input file from a 4GL session to run DBTOOL to do an SQL Width Scan w/Fix Option for all Tables of a database from a script.

Oct 23, 2013
Knowledge

Title

URL Name

P159769

Article Number

000140171

Environment

Product: Progress Version: 9.1E Product: OpenEdge Version: 10.x, 11.x OS: All supported platforms Other: DBTOOL

Question/Problem Description

How to run DBTOOL SQL Width Scan w/Fix Option for all database tables from a script.
How to run DBTOOL SQL Width & Date Scan w/Report Option for all Tables of a database.

Steps to Reproduce

Clarifying Information

Error Message

Defect Number

Enhancement Number

Cause

Resolution

Before running this program be sure there is enough disk space on the system, as there will be one file generated for each table in the database.

The size of the file is dependent upon the logging level chosen and the number of records in the table.

The program example below uses the recommended least verbose logging level.

The following line of code from the program listing sets up the DBTOOL run time parameters in the input.txt file to run the SQL Width Scan w/Fix Option.

PUT UNFORMATTED "9~n2~n1~n20~n" + STRING (_file-number) + "~n0".

Legend for the parameters for the above line are:

9 = Enable file logging.

~n = carriage return.

2 = SQL Width Scan w/Fix Option.

~n = carriage return.

1 = Connection Type (0 = single-user, 1 = Self-service)

~n = carriage return.

20 = (Enter the desired Padding % above the Current Max)

~n= carriage return.

_file-number = is the table number supplied by the "FOR EACH" loop.

~n = carriage return.

0 = Logging Level (0,1,2, and 3) 0 is the least verbose, 3 is most verbose and reserved for development inspection.

To run the SQL Width & Date Scan w/Report for all Tables of a database, modify the run time parameters:

PUT UNFORMATTED "9~n1~n1~n" + STRING (_file-number) + "~n0".

The following line of code from the PROGRAM LISTING BELOW, must be edited to reflect the name of the database instead of the Sports2000 demo 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.

For example: dbtool_customer.

/* create dbtool menu input file */

FOR EACH _file NO-LOCK WHERE _Tbl-Type = "T":
    OUTPUT TO Value("input.txt").
    /* SQL Width Scan wFix Option. */
        PUT UNFORMATTED "9~n2~n1~n20~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.

Example of the input file created by the above script:

Example output for a single table:

Total records read: 0

SQLWidth errors found: 0, Date errors found: 0

SQLWidth errors fixed: 0

Workaround

Notes

Progress Articles:

What is DBTOOL?
How to create an input file with ABL to run a DBTOOL Record Validation report for each Table in a database?

Keyword Phrase

Last Modified Date

11/20/2020 7:34 AM

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.

Files(0)

Was this article helpful?

Was this article helpful?

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.

Information

Related Articles