Wednesday, June 27, 2018

Different types of numbers in files and tables

different types of numbers that can be used when defining dds files and sql 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

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 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.

4 comments:

  1. Thank you so much! I happen to be working on a project that was using this, and it answered all of my questions!

    ReplyDelete
  2. I'm not an RPG guy much, but I think you have some mistakes in this. The following works:

    CREATE 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.

    ReplyDelete
    Replies
    1. 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)

      Delete
  3. If 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

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.