DBTOOL was first included in Progress 9.1D06 Service Pack as a character mode application which amongst others allows problems with the SQL Width and record format errors to be identified and adjusted. For further information refer to Article:
In order to solve the problem permanently, the SQL-WIDTH of character fields that are supposed to handle large amounts of text (especially with editor widgets on the UI) need to be reviewed, bearing in mind that there is a 32K bytes field limit on the ABL character type and a 32000 byte limit on a record.
OpenEdge 11.5.1 onward, OpenEdge SQL allows database administrators (DBAs) to authorize truncation of data, so that the selected data fits the defined column size and the part of the value larger than the defined size of the column is truncated with truncate by:
As an alternative to DBTool (especially for OpenEdge versions for which DBTool and
-SQLTruncateTooLarge & -SQLWidthUpdate are not available, the following can be used:The ABL sample
FieldSizeReport.p code below can be used as an alternative to DBTOOL, which scans the entire database and check to see whether data contained in character fields exceeds the SQL width by comparing the length of character fields with
_field._width
- The code is based upon 3 loops (table, record and character fields) and a dynamic query. With static queries, it is possible to get rid of the third loop and have better performance with less CPU usage (make a program generator, need the right to compile).
- The code example below does not check the Raw fields. The length of such a field could be obtained with the 4GL GET-SIZE() function.
- The code was initially tested in version 9.1C and optimized against speed with the use of array variables (faster than using lists or handling an additional temp-table loop). You can expect the following scan rates with 600MHz machines, and a high CPU usage:
- ~4GBytes per hour on UNIX/Linux
- ~2GBytes per hour on Windows
- This code sample is provided as-is and should be verified first against a test environment before production. To date, this code can be compiled and run against any Progress 9.1D, 9.1E, OpenEdge 10.x, 11,x database.
EXAMPLE#1: If the program is run against a database with 3 tables and no SQL-WIDTH problem, the output of the program is:
# FieldSizeReport.txt
15:55:58 Table: Customer
15:57:22 Table: Invoice
15:57:22 Table: ItemEXAMPLE#2: If the program is run against a database with SQL-WIDTH problem, the output of the program is:
# FieldSizeReport.txt
15:55:58 Table: Customer
Field-Name Width MaxSize MaxRowid Extent
Address 10 34 0x0000000000000236 0
Address2 10 19 0x0000000000000060 0
City 10 16 0x0000000000000159 0
Comments 100 29995 0x000000000001fae1 0
Contact 20 22 0x00000000000001ed 0
Name 8 30 0x000000000000012a 0One advantage of using this code example over DBTOOL Option
1. SQL Width & Date Scan w/Report, is that before adjusting the current SQL-WIDTH, the record field can first be dumped to a text file to check the field content using the reported
MaxRowid. Analysing the field content may for example show unnecessary leading or trailing spaces or invalid content that can be used to fix other fields with the same data entries.
Example: Dump the largest record field exceeding the current SQL-Width.
DEFINE VARIABLE cRowId AS CHARACTER NO-UNDO.
OUTPUT TO exportfield.txt.
FIND Customer WHERE ROWID(Customer) = TO-ROWID("0x000000000001fae1") NO-LOCK.
cRowId = STRING(ROWID(Customer)).
PUT UNFORM recid(Customer) "; " SUBSTRING(cRowId, 13, LENGTH(cRowId)) "; " comment.
OUTPUT CLOSE.
Example: Methods to remove trailing spaces:1. Create a SQL view using LTRIM / RTRIM
a. To more easily view leading/trailing spaces, prefix and suffix the result. For example:
SELECT rowid, '--' + "Comment" + '--'
FROM "PUB"."Customer"
WHERE LENGTH(RTRIM("Name")) <> LENGTH("Name") ;
b. Update fields with LTRIM / RTRIM to strip out leading/trailing spaces:
UPDATE "PUB"."Customer" SET "Comment" = LTRIM("Comment");
COMMIT;
UPDATE "PUB"."Customer" SET "Comment" = RTRIM("Comment");
COMMIT;
2. Using ABL to strip white space characteters, refer to the example provided in Article
Example: FieldSizeReport.p as an alternative to DBTOOL SQL Width & Date Scan w/Report Option
/* FieldSizeReport.p as an alternative to DBTOOL SQL Width & Date Scan w/Report Option */
DEFINE VARIABLE FieldList AS CHARACTER NO-UNDO. /*optimize the Dynamic query*/
DEFINE VARIABLE qh AS HANDLE NO-UNDO. /*Query handle*/
DEFINE VARIABLE bh AS HANDLE NO-UNDO. /*buffer handle*/
DEFINE VARIABLE s AS INTEGER NO-UNDO. /*size*/
DEFINE VARIABLE i AS INTEGER NO-UNDO. /*to manage the extents*/
DEFINE VARIABLE k AS INTEGER NO-UNDO. /*to manage character field 'number'*/
DEFINE VARIABLE mk AS INTEGER NO-UNDO. /*max k*/
&SCOPED-DEFINE MaxExt 500 /*increase if needed*/
/* fastest way is to use arrays (faster than a temp-table defined LIKE _field)*/
DEFINE VARIABLE an AS CHARACTER EXTENT {&MaxExt} NO-UNDO. /*names*/
DEFINE VARIABLE abfh AS HANDLE EXTENT {&MaxExt} NO-UNDO. /*buffer field handle*/
DEFINE VARIABLE aex AS INTEGER EXTENT {&MaxExt} NO-UNDO. /*extents*/
DEFINE VARIABLE aw AS INTEGER EXTENT {&MaxExt} NO-UNDO. /*sql width*/
DEFINE VARIABLE ams AS INTEGER EXTENT {&MaxExt} NO-UNDO. /*max size*/
DEFINE VARIABLE ar AS ROWID EXTENT {&MaxExt} NO-UNDO. /*rowid of max size*/
DEFINE STREAM scr. /*screen*/
DEFINE STREAM rep. /*report*/
OUTPUT STREAM scr TO TERMINAL.
OUTPUT STREAM rep TO FieldSizeReport.txt.
PUT STREAM rep " FieldSizeReport.txt started the " TODAY " at "
STRING(TIME,"hh:mm:ss").
PUT STREAM rep
"~n Given info are: ~tField-Name~tWidth~tMaxSize~tMaxRowid~tExtent".
FOR EACH _file NO-LOCK:
/* Shall we keep it or skip it? */
IF (_file._file-name BEGINS "_"
AND LOOKUP(_file._file-name,"_file,_field,_index") = 0)
OR _file._file-name BEGINS "SYS"
OR _file._owner <> "PUB" THEN NEXT.
PUT STREAM rep UNFORMATTED "~n~n" STRING(TIME,'hh:mm:ss') "
Table: " _file._file-Name.
PAUSE 0.
DISPLAY STREAM scr STRING(TIME,'hh:mm:ss') + " Table: "
+ _file._file-Name FORMAT "X(77)" WITH THREE-D.
/* copy Schema and prepare FieldList */
DELETE OBJECT qh NO-ERROR.
DELETE OBJECT bh NO-ERROR.
FieldList = "".
CREATE BUFFER bh FOR TABLE _file._file-name BUFFER-NAME "yo".
k = 0.
FOR EACH _field OF _file NO-LOCK
WHERE _field._Data-Type = "CHARACTER":
ASSIGN
k = k + 1
an[k] = _field._field-name
abfh[k] = bh:BUFFER-FIELD(_field._field-name)
aex[k] = _field._extent
aw[k] = _field._width
FieldList = (IF FieldList = "" THEN "" ELSE FieldList + " ")
+ _field._field-name.
END.
IF FieldList = "" THEN NEXT. /*No char field, go to next one*/
/* Sweep the records, detect and store the max sizes and rowids*/
ASSIGN
ams = 0 /*reset the size array*/
mk = k. /*keep max value of k*/
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("FOR EACH yo FIELDS (" + FieldList + ") NO-LOCK").
qh:QUERY-OPEN().
DO WHILE TRUE: /*record loop*/
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
DO k = 1 TO mk: /*field loop*/
IF aex[k] = 0 THEN DO:
s = LENGTH(abfh[k]:BUFFER-VALUE).
IF s > ams[k] THEN ASSIGN
ams[k] = s
ar[k] = bh:ROWID.
END.
ELSE DO i = 1 TO aex[k]: /*extent loop if needed*/
s = LENGTH(abfh[k]:BUFFER-VALUE(i)).
IF s > ams[k] THEN ASSIGN
ams[k] = s
ar[k] = bh:ROWID.
END.
END.
END.
/* Report the fields where size > SqlWidth in schema */
DO k = 1 TO mk:
IF ams[k] > aw[k] THEN
PUT STREAM rep UNFORMATTED "~n~t" an[k] "~t" aw[k] "~t"
ams[k] "~t" STRING(ar[k]) "~t" aex[k].
END.
END.
PUT STREAM rep "~n~n report finished the " TODAY " at " STRING(TIME,"hh:mm:ss").
OUTPUT STREAM rep CLOSE.
OUTPUT STREAM scr CLOSE.
Example: FieldSizeFix.p as an alternative to DBTOOL SQL Width Scan w/Fix Option
- The following code will detect the fields with large amount of data and will adjust SQL-WIDTH accordingly.
- USE IT AT YOUR OWN RISK.
/* FieldSizeFix.p as an alternative to DBTOOL SQL Width Scan w/Fix Option */
/* Detect fields with a large amount of data and will adjust SQL-WIDTH accordingly. */
FORM
_file-name LABEL "Processing File"
_field-name
WITH FRAME xx SIDE-LABELS 1 COL WIDTH 78.
DEFINE TEMP-TABLE sqlw
FIELD fileName AS CHARACTER FORMAT "x(24)"
FIELD fieldName AS CHARACTER FORMAT "x(24)"
FIELD sz AS INTEGER
INDEX ix IS PRIMARY UNIQUE fileName fieldName.
RUN FIND_fiedls.
FOR EACH sqlw NO-LOCK :
DISPLAY sqlw WITH TITLE "Change SQL-WIDTH for this fields" .
END.
DEFINE VARIABLE vlOK AS LOGICAL.
IF CAN-FIND( FIRST sqlw NO-LOCK ) THEN
MESSAGE "Do you wish to continue?" VIEW-AS ALERT-BOX BUTTONS YES-NO UPDATE vlok.
ELSE
MESSAGE "Dataserver schema was validated." VIEW-AS ALERT-BOX.
IF vlok THEN
DO TRANSACTION:
FOR EACH _file NO-LOCK WHERE NOT _hidden,
EACH _field OF _file WHERE _data-type = "CHARACTER",
EACH sqlw NO-LOCK WHERE FILENAME = _file-name AND fieldName = _field-name:
ASSIGN _width = sz + 8.
END.
END.
PROCEDURE FIND_fiedls:
DEFINE VARIABLE vFieldList AS CHARACTER NO-UNDO.
DEFINE VARIABLE vSizeList AS CHARACTER NO-UNDO.
DEFINE VARIABLE vFieldName AS INTEGER NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
FOR EACH _file WHERE NOT _hidden NO-LOCK:
ASSIGN vFieldList = ''
vSizeList = ''.
FOR EACH _field OF _file NO-LOCK WHERE _data-type = "CHARACTER":
ASSIGN vFieldList = vFieldList + ',' + _field-name
vSizeList = vSizeList + ',' + STRING(INTEGER(_width - 2)).
END.
IF vFieldList = '' THEN
NEXT.
ASSIGN vFieldList = SUBSTRING(vFieldList,2)
vSizeList = SUBSTRING(vSizeList,2).
CREATE BUFFER hBuffer FOR TABLE _file-name.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hBuffer).
hQuery:QUERY-PREPARE("FOR EACH " + _file-name + " NO-LOCK ").
hQuery:QUERY-OPEN().
DISPLAY _file-name WITH FRAME xx .
IF hQuery:GET-FIRST() THEN
REPEAT:
hBuffer = hQuery:GET-BUFFER-HANDLE(1).
DO vFieldName = 1 TO NUM-ENTRIES(vFieldList):
ASSIGN hField = hBuffer:BUFFER-FIELD(ENTRY(vFieldName,vFieldList)).
IF LENGTH(hField:BUFFER-VALUE) > INTEGER(ENTRY(vFieldName,vSizeList)) THEN
DO:
FIND sqlw WHERE FILENAME = _file-name AND fieldName = ENTRY(vFieldName,vFieldList) NO-ERROR.
IF NOT AVAILABLE(sqlw) THEN
DO:
CREATE sqlw.
ASSIGN FILENAME = _file-name
fieldName = ENTRY(vFieldName,vFieldList).
END.
IF sz < LENGTH(hField:BUFFER-VALUE) THEN
ASSIGN sz = LENGTH(hField:BUFFER-VALUE).
END.
END.
hQuery:GET-NEXT().
IF hQuery:QUERY-OFF-END THEN
LEAVE.
END.
hQuery:QUERY-CLOSE.
DELETE OBJECT hQuery.
DELETE OBJECT hBuffer.
END.
END PROCEDURE.
