ABL INTEGER data-type range is from: -(2^31) to ((2^31) - 1)
In OpenEdge 10.1A and earlier:
All intermediate calculations are carried out in 32-bit arithmetic.
Code that contains an arithmetic calculation where the intermediate result cannot be stored in a 32-bit integer, yields an incorrect result even though the final result does not exceed the -2147483648 to +2147483647 range of INTEGER data. For example, 2,000,000,000 * 100 / 100 gives an incorrect result, because the intermediate result of 200 billion cannot be stored in a 32-bit integer, even though the result does not exceed 2 billion.
ABL INT64 data-type range is from: -(2^63) to ((2^63) - 1)
In OpenEdge 10.1B and later:
- The INT64 data type was introduced. This data type stores values outside the range of the Progress INTEGER data type from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.
- INT64 may be used with database fields, temp-table fields, program variables, parameters, and user defined functions' return values.
- All intermediate calculations are carried out in 64-bit arithmetic. For example, 2,000,000,000 * 100 / 100 yields the correct result whether the target field is INT or INT64. And although 2,000,000,000 * 100 does not cause an overflow, the result must be assigned to an INT64 data type otherwise a run-time error will result.
How to find the current highest RECID values:
When a Storage Area has breached the 32-bit limit, recid() overflow will result in the application. Determining where the recid() values are in production environments is a useful first step in assisting with deployment planning. The following Article outlines methods that can be employed:
How to change the data type of a database field to INT64:
An existing field, program variable, or parameter defined with the INTEGER data type can be expanded to be 64-bit capable by changing the data type from INT to INT64. Existing database fields can be changed programmatically or through the Data Dictionary while the database is online as follows:
Option 1. Data Dictionary tool, using "-> int64".
Option 2. Data Administration tool, using Load definitions (.df)
UPDATE FIELD "CustNum" OF "Customer" AS int64
Option 3. ABL code.
All fields of a table defined as INT data type can be changed it to INT64. Refer to Article
Additional Considerations for 64-bit values in the current application architecture (recid, integer and sequences):
1. Indexes do not need to be rebuilt.
After changing a field's data type from INT to INT64, it is not necessary to dump and load the data or rebuild indexes if the field is an index key. To revert the datatype change, a dump and load is necessary.
2. 64-bit Sequences
If the database was created with a Progress OpenEdge release older than 10.1B, and if the database contains sequences, it is also necessary to enable 64-bit sequences using the PROUTIL ENABLESEQ64 qualifier. If the database was created in OpenEdge 10.1B or later, the sequences are already 64-bit-enabled and no further action is required.
3. The Data Dictionary only allows the INTEGER data type to be changed.
The tools that maintain the Database Schema do not allow the Data Type of a field to be changed.
The only exception came with the introduction of 64 bit integers in OpenEdge 10.1B, where in the case of an INTEGER field there is a provision to change the integer field to and only to int64. The INTEGER data type can be changed without adding a new field of the required data type because the storage format for INTEGER and INT64 is the same.
A typical example in this light is the RECID Data Type which stores 64 bit values. When the record is created with a recid value in the 64-bit range and the field is part of an index key, the routine that creates the index key for the datatype recid creates the index key correctly. Prior to OpenEdge 11.7.2, 11.6.4 the routine used by index utilities, (idxcheck, idxfix, idxbuild, idxactivate) processes RECID datatype fields as an INTEGER field when validating or building keys. This results in false negatives when index verification is used and index corruption when index build utilities are used. Changing these fields to the INT64 datatype would negate the issue. Refer to Articles
4. Prior to OpenEdge 11.7.0; 11.6.4, when recids straddle the 32/64-bit boundary, multi-level indexes are corrupted when the index grows larger than can be referenced by a 32-bit variable. Refer to Article
5. Application code review
a. Since OpenEdge 11, the
-noint64 startup option no longer prevents reference to int64 variables, fields, or expressions, but it will still change the data type of long integer constants to be DECIMAL as opposed to INT64. For further information refer to Article
How does the -noint64 client session startup parameter work? b. When the data type of a database field is changed, this effects the Cyclic Redundancy Check (CRC) of the database table that contains the field. Any r-code derived from ABL code which references the table must be recompiled.
c. Any references to the converted INTEGER data type database field can still overflow until code that has static references to the associated table is recompiled. Failing to do so may still allow the code to run, but r-code will still assume the field is an INTEGER type. This will lead to error "
Value <value> too large to fit in INTEGER datatype. (13682)" or "
Value <value> too large to fit in INTEGER. Line <line> in <procedure> (15747)" when trying to read larger INT64 values from the database.
d. Track specific assignments. Apart from database fields, temp-table fields, program variables, parameters and user defined functions' return values need to be changed to INT64. To this end open-source tools such as
ProLint could be useful.
http://www.oehive.org/prolint/download e. Once a Storage Area has breached the 32-bit limit, a database area re-organisation is needed to prevent recid() overflow before related application code can be fixed and deployed to production sites. This can be achieved with tablemove or dump and load to move tables out of the area into new areas to reset the recids.
- Tables that have more than 2 billion rows (including fragmented records) or Tables that are already in their own Type II Storage area with a high-water mark above the 32-bit boundary will need to be split before moving to new areas.
- Use TABANALYS reports to find the average record sizes for each table to determine the best RPB (recbit) in the new areas, so that rowids won't be wasted for the area. When the total recids is kept to a minimum, this provides more time to fix the related application code.
- Once tables have been moved, ensure that the recid() overflow will not re-occur within the timeframe that the application code is fixed.
- The 32-bit INTEGER boundary is essentially the same as the Type I Storage area maxarea limit on recid values. The methods described in the following Article can be considered:
Performance differences between INT64 and INTEGER and which is recommended?
The performance difference between INT64 and INTEGER depends on several factors.
1. Data Size and Range
2. Performance Considerations
- Speed: On modern 64-bit systems, INT64 operations are typically just as fast as INTEGER because the CPU natively supports 64-bit arithmetic.
- Memory Usage: INT64 uses twice the memory of INTEGER (8 bytes vs. 4 bytes), which could impact performance if dealing with large arrays or datasets.
- Compatibility: Some older versions of Progress or integrations might not fully support INT64, so INTEGER could be safer in those cases.
3. Use Case:
- Use INT64 when there is a need to store large numbers (e.g., timestamps, counters, IDs).
- Use INTEGER when there is confidence that the values will stay within the 32-bit range, and memory optimization is desired.
Use INTEGER (32-bit) when:
If numeric values never exceed ±2,147,483,647, then INTEGER is generally the better choice for performance:
- Lower memory footprint: 4 bytes vs. 8 bytes for INT64.
- Faster memory access: Smaller data types are more cache-friendly.
- Less disk I/O: If stored in temp-tables or passed to the database, smaller types reduce I/O overhead.
- Better performance in loops and sorting: Especially when dealing with large arrays or temp-tables.
Use INT64 only when:
- Expect values to exceed the INTEGER range.
- Working with large IDs, timestamps, or counters that require 64-bit precision.
- Integrating with external systems or databases that use 64-bit integers.
