Support MC Press - Visit Our Sponsors

Forums Sponsor

Popular Forums

POPULAR FORUMS Rpg sql insert into qtemp table (2019 views) What is better, free code or traditional code ? (1747 views) cant ping workstation from system i (1416 views) was 7 nd server not starting (1249 views) SQL Error 802 Type 6 - Numeric Handling for Joins? (569 views) ODF Release April 28, 2012 (494 views)

Forums

 

   Search Sponsor

POPULAR SEARCHES

Search

Integers fields are a fantastic way to store relatively large numbers in a small space, but getting RPG to play nice isn't always easy.

There are times when RPG simply leaps forward into the future and times when it needs a little cajoling. The whole concept of ILE was a quantum leap. Integer support, a little less so.

Integer and Binary

The biggest problem is the fact that RPG has long had support for a field type similar to the integer, called a binary field. The main difference has to do with the way the maximum value is defined. For integer fields, it is the binary maximum based on the number of bytes in the field: a two-byte unsigned field can range from 0 to 65535 (the signed version goes from -32768 to 32767), and they never have decimal precision. Meanwhile, you specify binary (type B) fields with digits and precision like any other numeric field type in RPG, and in fact when the field is brought in from disk, the default is to actually represent it internally in the RPG program as a packed field. So basically, this just allows the data to be stored in fewer bytes: a decimal value that can hold up to four digits of information requires three packed decimal bytes. The same binary field takes only two bytes but at the cost of converting back and forth between packed and binary every time you access the database.

I won't go into a long discussion, especially since binary fields are deprecated; you really should never use them. Instead, you should always use integer fields, and in fact when you use DDL to define your files, you will see some strange throwbacks to the olden days of binary fields.

What Happens with DDL

When using DDL to create files, you don't even have the option of creating a binary field, at least not the way RPG understands. Binary fields in DDL mean something completely different: large strings of hexadecimal data.

Instead, if you're creating numeric binary fields in DDL, you use one of three data types: INTEGER, BIGINT, or SMALLINT. As the names imply, the primary difference between these field types is size. SMALLINT is two bytes, INTEGER is four bytes, BIGINT is eight bytes.

 CREATE TABLE JDPTST/BININTDDL

   (ANINT INTEGER NOT NULL WITH DEFAULT,  

    ABIGINT BIGINT NOT NULL WITH DEFAULT,

    ASMALLINT SMALLINT NOT NULL WITH DEFAULT)       

I then use this file in a program:

 h DEBUG                                     

 fbinintddl if   e           k disk   

  /free                                

   read binintddl;           

   *inlr = *on;                      

  /end-free                              

Note: I use DEBUG to make sure all fields are compiled, even if they're not used. Otherwise, the compiler ignores them. Also, the program as defined wouldn't compile anyway, because by default a file defined with SQL has a record format whose name is the same as the file. Either you would have to rename the format in the RPG program using the RENAME keyword on the F-spec, or you could assign the record format name using the RCDFMT keyword in the CREATE TABLE command, as outlined in a recent TechTip by Kent Milligan.

Anyway, if you then look at the compile listing, you would see that the fields are defined as follows:

 ABIGINT           P(20,0)                 7D

 ANINT             P(9,0)                  6D

 ASMALLINT         P(4,0)                  8D

Note first off that they are packed. This can be an issue, especially when passing data to other languages in data structures (an issue I'll address in more detail in a moment). But another issue is the size of the field. Note that SMALLINT is defined as four positions, even though it can hold values up to 32767 (or 65535 for an unsigned value). RPG limits the size to four digits, because it tries to figure out the largest number of digits that can be held with all nines. Since 9999 can fit in the SMALLINT type but 99999 cannot, RPG conservatively sets the size to four digits.

What happens, though, is that if you try to assign a value higher than 9999, even a value like 10000, which can easily fit in the field, RPG fails with an RNQ0103 error: target too small to hold result. To get around this problem, use the EXTBININT(*YES) keyword on the H-spec:

 h DEBUG EXTBININT(*YES)                                                    

Compiled this way, the field sizes change:

 ABIGINT           P(20,0)                 7D 

 ANINT             P(10,0)                 6D 

 ASMALLINT         P(5,0)                  8D 

Now you can actually update the fields to contain their maximum values. In fact, you have to be careful not to put values in that are too large. You can set the ASMALLINT value to 99999, but if you attempt to write or update the database with that value, you'll trigger the RNQ0103 error. Also note that the field ABIGINT doesn't change. For some reason, the conservative redefinition technique (which should have resulted in a field of 19 digits, not 20) does not apply to 64-bit numbers. They are always redefined to the appropriate maximum 20-digit field.

Communicating with Other Languages

So on to the last piece of the puzzle. Over the years, I've found the best way to pass data between languages is by using data structures. There are pluses and minuses to the technique, but in general I much prefer the idea of sending one or two data structures with predefined layouts. Please note that I am not talking about passing data between machines, especially over the Internet. In those cases, it's far easier to debug data that's sent textually, using a standard format such as XML or JavaScript Object Notation (JSON), and the amount of data passed as opposed to things like latency and so on makes saving bytes less of an issue.

But when I'm talking about large quantities of small packets of data, such as between layers in a multi-tiered application, I find the data structure technique to be invaluable. In general, the linkage between tiers is just a couple of buffers, and as the requirements change, I just change the conversion code, not the linkage details. Doing this can eliminate some headaches, such as hitting the maximum number of parameters.

Since IBM has been very good about providing support for packed decimal fields in Java (even though Java itself lags behind pretty badly), I've used packed decimal for many years. However, as more and more SQL enters the fray and integer values (record numbers, ID fields, timestamps) abound, I recently decided to start using integer numbers in my DDL and thus in my RPG programs. And this is where another legacy issue with RPG comes into play.

Note that even with the EXTBININT(*YES) keyword specified, my fields were being defined as packed fields. I create a message data structure like so:

 d message         ds                                

 d   MSG_SMALLINT                      like(ASMALLINT)

 d   MSG_INT                           like(ANINT)   

 d   MSG_BIGINT                        like(ABIGINT) 

And then look at the definition:

 MESSAGE           DS(20) 

 MSG_BIGINT        P(20,0)

 MSG_INT           P(10,0)

 MSG_SMALLINT      P(5,0) 

As you can see, the data structure is 20 characters long. This makes sense for packed fields; a P(20,0) field takes 11 bytes, P(10,0) takes 6, and the P(5,0) takes 3. But the program on the other side will be expecting binary data with fields of sizes 8, 4, and 2 respectively, and a total size of 14: You will get all kinds of numeric conversion errors with this setup.

This is because by default RPG will always convert fields from database files to packed, and we saw that in the listings. The only way around this is to force the database definitions to be honored, which you do by specifying an externally described data structure. Add this line before the message DS definition:

 d dsbid         e ds                  extname(BININTDDL)

Now the database fields will no longer be converted to packed, and you can see that in the cross-reference:

 ABIGINT           I(20,0)

 ANINT             I(10,0)

 ASMALLINT         I(5,0)

And thanks to that change, magic occurs with the LIKE defines in the message data structure as well:

 MESSAGE           DS(14)

 MSG_BIGINT        I(20,0)

 MSG_INT           I(10,0)

 MSG_SMALLINT      I(5,0)

Note that the fields are now defined as integer (type I) fields, and the data structure is now only 14 characters long, as it should be. You can now define this data structure in another language like EGL or Java using integer fields, and the two programs will communicate correctly.

Many thanks to Barbara Morris for patiently explaining to me the issues involved. If you remember to use both of these techniques--the EXTBININT(*YES) and the externally described data structure--you will be able to better communicate binary numeric data between your tiers.