A Documentation defect has been raised to clarify the "Max Width", "Array Width" and " Max Array Width" values reported by DBTOOL which are helpful in DataServer environments which rely on _width of _field records for array char/raw/decimal fields. This Article clarifies these fields by presenting an example.
DBTOOL ArrayWidth vs Max WidthComparing "Max ArrayWidth" vs "ArrayWidth" sizes can be used as guidelines when configuring foreign data sources to accept data from an OpenEdge database.
To size columns for DataServers / foreign data sources to accept OpenEdge data, DBTOOL Verbose level 4 provides alternate size calculations for array fields.
Only Verbose 4 of DBTOOL Option 1 SQL Width & Date Scan w/Report, will report any max array width smaller than M*N:
- M is the size of the array element who's size is the largest;
- N is the dimension of the array
Verbose 0-3 of DBTOOL Option 2 SQL Width Scan w/Fix Option, the max array width is updated silently whereas Verbose 4 also reports it.
Example: mytbl1 has a Character type field #2 with 4 array elements:
- Record 1: # bytes in Array Field#2 for Record #1: {30, 30, 30, 30}
- Record 2: # bytes in Array Field#2 for Record #2: { ?, ?, ?, 40}
Current max fieldLen for mytbl1 (10):
Fld# SQLWidth Max Width ArrayWidth Max ArrayWidth ERROR NAME
---- -------- --------- ---------- -------------- -------- ----
Before padding:
2: 72 128 168 168 ***### f1
DBTOOL re-run after, with 20% padding:
2: 153 128 201 168 f1
1. Fld#:
a. field number
b. Field types with that do not have sql width issues and are not date date/time fields are not reported.
2. SQLWidth:
a. The _Field._Width value.
b. It will have "Date" for date fields.
- Date fields are fixed size which do not have issues with sqlwidth but are included to report date formatting issues.
c. Option "2. SQL Width Scan w/Fix Option" updates the _Width field as appropriate
i. The padding % is added on top of the "Max Width" found.
ii. In the example, a 20% padding == ((30 + 30 + 30 + 30) + (2 * 4)) * 1.20 = 153
3. Max Width:
a. The actual maximum length of this field found by scanning the table's record length
b. non-array fields == maximum length of the field found plus 2 bytes overhead.
c. array fields == maximum length of the array field found determined by the sum of the array elements plus 2 bytes overhead.
d. In the example, since the overall length of {30, 30, 30, 30} is larger than
{ ?, ?, ?, 40} the Max Width value reported == (30 + 30 + 30 + 30) + (2*4) = 128
4. ArrayWidth: (For use with DataServers and Foreign Data Sources)
- "ArrayWidth" is based on maximum data length of all extent data.
a. ArrayWidth is a calculated value based on the maximum array **element** length found throughout all the records scanned for that table.
b. ArrayWidth == (maximum array element length +2) * (#elements)
c. When a sqlwidth update is performed, the ArrayWidth == ArrayWidth * padding, at the time the sql width was padded unless the current max element calculation described in 4b exceeds the old calculated value with the padding: MAX("saved ArrayWidth with padding" vs "current max via 4b calculation")
d. In the example, the maximum element length found during scanning the table is 40 bytes,
the calculation == (40 + 2) * 4 = 168
e. 20% padding == ((40 + 2) * 4) * 1.20 = 201 but is only applied if the actual "Max Width" exceeds the "SQLWidth".
f. Users may want to use this value when configuring their foreign data source to accept data from an OpenEdge database for array char/raw/decimal fields.
5. Max ArrayWidth: (For use with DataServers and Foreign Data Sources)
a. This is the same as ArrayWidth, but does not include the padding in the calculation
b. Which is why this value can therefore be smaller than ArrayWidth
c. Users may want to use this value when configuring their foreign data source to accept data from an OpenEdge database for array char/raw/decimal fields.
6. ERROR:
a. <blank>: non-array field has no error
b. ### : array field and it has no errors (Verbose level 4 only), no longer applies after a padding has been added to the field or to array date fields
c. *** : field has errors
d. ***### : array field has width error (Verbose level 4 only)
e. %%%% : date field has formatting error
7. NAME: field name from _Field._Field-Name
NOTES:
1. MAX-WIDTH can be specified in the .df file to specify the max with of the column.
2. Padding % is applied to all fields of the record that have width issues and is applied using the "Max Width" value.
In the example for the new _field._Width value: 128 * 1.20 =153 .
3. "Max ArrayWidth" vs "ArrayWidth" are used when configuring foreign data sources to accept data from an OpenEdge database. Rather than a maximum size of the existing field, the reported value is calculated assuming all elements require sizing each element of the array to the maximum single element size for the records scanned.
4. After a padding of an errant _field._width for an array field, the new "ArrayWidth" value is stored in _field._fld-res1[3]. It is retrieved and reported on subsequent sql width check operations as opposed to using the calculated value.
When fields are populated, as opposed to empty:
- _field._fld-res1[3] == (max element size * array dimension).
- The DataServer considers the larger value between - schema width (_Field._width) and DBTOOL adjusted value (_Field._fld-res1[3])
if there are no data in any of the extents of the field:
- The value in _Field._fld-res1[3] is smaller than _Field._width.
Running DBTOOL with a new array field, the field field _fld-res1[3] is initialized/ updated against data:
find _field "<arrayfieldname>".
IF _FIELD._Extent >=1 THEN
DISP _Field-name _width _Fld-Misc3[1] FORMAT "zzzzzzzz9" _fld-res1[3] FORMAT "zzzzzzzz9".
