Different types of numbers in files and tables

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:

1. Physical files
2. Display files
3. 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

¹ 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 1063	-9 x 1063
NUMERIC	9 x 1063	-9 x 1063
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.