In a previous post I wrote about the different types of numbers that could be used in RPG. Alas, we have been spoiled with RPG as there are less types to choose from in when defining numbers fields in DDS files and columns (SQL) DDL tables.
DDS files
DDS files come in different forms, and they all have their own rules as to the types of numbers are valid. I am only going cover the major types, which in my opinion are:
- Physical files
- Display files
- Printer files
Physical files
Having been spoiled with the plethora of number types available in RPG we are only allowed a few in DDS for physical files:
Number type | Data type |
Maximum size |
Packed | P | Up to 63 |
Signed (zoned) | S | Up to 63 |
Binary | B | Up to 18 |
Float | F | Up to 9 for single precision Up to 17 for double precision |
So what does that look like in DDS:
01 A R TESTFILER 02 A PACKED 63P 0 03 A SIGNED R S REFFLD(PACKED *SRC) 04 A BINARY 18B 0 05 A FLOAT1 9F FLTPCN(*SINGLE) 06 A FLOAT2 17F FLTPCN(*DOUBLE) |
In the real world no one needs a numeric field that is 63 long. This file would be more typical:
01 A R TESTFILER 02 A PACKED 7P 2 03 A CHAR1 1A 04 A SIGNED R S REFFLD(PACKED) 05 A CHAR2 R REFFLD(CHAR1 *SRC) 06 A BINARY 4B 0 07 A CHAR3 R REFFLD(CHAR1 *SRC) 08 A FLOAT1 9F FLTPCN(*SINGLE) |
The fields CHAR1, CHAR2, and CHAR3 I am using as separators so that I can easily find the start and end of each number field.
When I compile the file and look at the file's layout I see:
Field name | Number type |
Beginning position |
Ending position |
Length |
PACKED | P | 1 | 4 | 7,2 |
CHAR1 | A | 5 | 5 | 1 |
SIGNED | S | 6 | 12 | 7,2 |
CHAR2 | A | 13 | 13 | 1 |
BINARY | B | 14 | 15 | 4,0 |
CHAR3 | A | 16 | 16 | 1 |
FLOAT1 | F | 17 | 20 | 4 |
I am not going to bother to show the program I used to write one record to this file. I am going to show what that record looks like:
PACKED CHAR1 PACKED CHAR1 BINARY CHAR1 FLOAT1 1,234.00 * 1,234.00 * 1,234 * 123460000.E-004 |
If I use the Display Physical file, DSPPFM, command and press F10 I can see the hexadecimal in the record.
* . . . + . . . . 1 . . . . + . . . . 2 0123400F 5CF0F1F2 F3F4F0F0 5C04D25C 4640E800 |
It is not that I find that confusing, I just have to remember that each byte is made up of two characters next to one another. Personally I find it easier to understand to format the hexadecimal like this:
---- + ----1-- - -+ - ---2 0240 5 FFFFFFF 5 0D 5 44E0 130F C 0123400 C 42 C 6080 |
I have formatted a space between each field above to make it easier to see where each field starts and ends. The hex '5C' is the asterisk character I am using as a field separator.
This clearly shows the advantage of using a packed field. The seven packed numeric takes up just four bytes, rather than the seven the signed/zoned field does.
04D2 is the hexadecimal equivalent of the decimal 1,234.
I cannot figure out how the float field has been packed into the bytes. Who cares the beauty of the IBM i is I do not have to bother with how the number is stored, all I need to care about is the value contained within.
Display files
Signed numeric fields are the only type of number that are allowed in display files.
01 A DSPSIZ(24 80 *DS3) 02 A R SCREEN 03 A SIGNED 10S 0 2 10 |
Printer files
Signed fields, of course, and I was surprised to find that both types of float fields are supported by printer files.
01 A R PRDETAIL 02 A SIGNED 10S 0 2 10 03 A FLOAT1 9F 4 10FLTPCN(*SINGLE) 04 A FLOAT2 17F 5 10FLTPCN(*DOUBLE) |
(SQL) DDL tables
Db2 for i Data Definition Language, DDL, has the following types of numbers:
- DECIMAL - equivalent of packed in DDS
- NUMERIC - equivalent of signed in DDS
- REAL - equivalent of floating point single precision in DDS
- DOUBLE - equivalent of floating point double precision in DDS
- INTEGER
- SMALLINT - small integer
- BIGINT - large integer
When I am defining a table I do not have give column sizes, when created the table will use the number types default size.
01 CREATE TABLE QTEMP.TESTTABLE ( 02 DECIMAL DECIMAL, 03 NUMERIC NUMERIC, 04 SMALLINT SMALLINT, 05 INTEGER INTEGER, 06 BIGINT BIGINT, 07 DOUBLE DOUBLE, 08 REAL REAL) ; |
Number type |
Length | Column size |
Decimal | 5,0 | 3 |
Numeric | 5,0 | 5 |
Small integer | 4,0 | 2 ^{1} |
Integer | 9,0 | 4 ^{1} |
Big integer | 18,0 | 8 ^{1} |
Real | 4 | 4 |
Double | 8 | 8 |
^{1} Stored as binary.
The only number types I can define a size for is the decimal and the numeric. The integer and float types have to be used "as is".
Determining the maximum and minimum values of the various column types is easy. I use *HIVAL and *LOVAL in a RPG program:
01 **free 02 dcl-ds Data extname('QTEMP/TESTTABLE') ; 03 end-ds ; 04 clear Data ; 05 DECIMAL = *hival ; 06 NUMERIC = *hival ; 07 SMALLINT = *hival ; 08 INTEGER = *hival ; 09 BIGINT = *hival ; 10 DOUBLE = *hival ; 11 REAL = *hival ; 12 exec sql INSERT INTO QTEMP.TESTTABLE VALUES(:Data) ; 13 clear Data ; 14 DECIMAL = *loval ; 15 NUMERIC = *loval ; 16 SMALLINT = *loval ; 17 INTEGER = *loval ; 18 BIGINT = *loval ; 19 DOUBLE = *loval ; 20 REAL = *loval ; 21 exec sql INSERT INTO QTEMP.TESTTABLE VALUES(:Data) ; 22 *inlr = *on ; |
Line 1: All my code is free, that is totally free RPG.
Lines 2 and 3: This is the definition of a data structure I will be using to insert data into my DDL table. Rather than define the data structure's subfields I am using the table itself to define this as an externally described data structure.
Line 4: I am initializing the data structure using the clear operation code.
Line 5 – 11: Moving the RPG special value *HIVAL to the data structure subfields. As I did not use the QUALIFIED keyword when I defined the data structure I do not have to qualify the subfields.
Line 12: I am using the data structure to insert a row into the table.
Lines 13 – 21: The same as above, but for *LOVAL.
The results are:
Column | Maximum value |
Minimum value |
DECIMAL | 9 x 10^{63} | -9 x 10^{63} |
NUMERIC | 9 x 10^{63} | -9 x 10^{63} |
SMALLINT | 9,999 | -9,999 |
INTEGER | 999,999,999 | -999,999,999 |
BIGINT | 9,223,372,036,854,775,807 | -9,223,372,036,854,775,808 |
DOUBLE | 1.797693134862316E+308 | -1.797693134862316E+308 |
REAL | 3.4028235E+038 | -3.4028235E+038 |
As I showed in the example program, above, it is possible to use the DDL columns as variables in a RPG program. What is interesting is how the SQL precompiler defines the SQL integers to equivalent RPG number types. I have copied this from the compile listing:
2 dcl-ds Data extname('QTEMP/TESTTABLE') ; 3 end-ds ; *-------------------------------------------------- * Data structure . . : DATA * External format . : TESTTABLE : QTEMP/TESTTABLE *-------------------------------------------------- <---------------------- Source Specifications ------ 4=D DECIMAL 63P 0 5=D NUMERIC 63S 0 6=D SMALLINT 4B 0 7=D INTEGER 9B 0 8=D BIGINT 20I 0 9=D DOUBLE 8F 0=D REAL 4F |
The SMALLINT and INTEGER have been converted to binary, rather than the nearest equivalent size for an integer variable in RPG: 3 and 10 bytes. This explains why the SMALLINT and INTEGER columns have the same ranges as a RPG binary variable, but the BIGINT has the same range as the RPG 20 long integer variable.
This article was written for IBM i 7.3, and should work for earlier releases too.
Thank you so much! I happen to be working on a project that was using this, and it answered all of my questions!
ReplyDeleteI'm not an RPG guy much, but I think you have some mistakes in this. The following works:
ReplyDeleteCREATE TABLE T ( A SMALLINT, B INT)
INSERT INTO T VALUES(32767, 2147483647)
INSERT INTO T VALUES(-32768, -2147483648)
Also, there is the Decimal Float type:
CREATE TABLE T (A DECFLOAT(16), B DECFLOAT(34))
Decimal float is like floating point, but instead of binary digits, there are decimal digits. Interestingly a value of 4.00 in Decimal float is distinct from 4.0, because the precision specified is different. There are ways to compare them and determine that they are scientifically distinct due to being measurements with different specified precision and other ways to compare them mathematically to see that they have the same 'raw' value. They also don't have imprecision issues for decimal calulations, so that the value of 1/10 (0.1) can be specified exactly for example.
I was going to mention Decfloat. For those that might be confused, consider if I tell you my truck is 14 feet long, vs. telling you it is 14.00 feet long. In the second case, you know more about the length, as I would be justified in saying it is 14 feet long when in fact it is 14.01 feet if I used a more precise measuring tool. That's why DECFLOAT has a different binary representation for 14 vs. 14.00 (vs. 14.0)
DeleteIf the EXTBININT(*YES) keyword is used on the control spec, then the SMALLINT and INTEGER types defined in a file will be treated as integers (5I and 10I) in the program instead of binary decimal types (4B and 9B).
ReplyDelete