Guru: I’m A Number, You’re A Number, Everybody’s A Number

September 9, 2019 Ted Holt

Do you, like Bob Seger, sometimes feel that you are nothing more than a number? Me too. That’s because to many people, that’s exactly what we are. And if there’s one thing that computers are good at, it’s assigning numbers — to orders, to accounts, to invoices, to transactions, and of course, to people. Since we have to make the computer assign numbers, we may as well learn the modern way to do it.

In my earliest days of programming, I would store the last assigned of a series of numbers in a data file. (The S/34 and S/36 had data files, not a database.) When I began working with the S/38, I learned to store the last assigned number in a data area. My programs would retrieve the last assigned number, add 1 to it, and update the file or data area.

There’s nothing wrong with that method, in my opinion, but I think it’s wiser to let the database do as much work as possible so that my applications have to do less. DB2 for i has two mechanisms for assigning serial numbers — identity columns and sequence objects. Comparing and contrasting the two would take me off the subject, so let me just say that I prefer to use an identity column when possible, and that’s the approach I take in the following example.

Let’s assume a typical header-detail table configuration:

create or replace table bvvhdr
 ( OrderID   dec (9)  primary key
                      generated always as identity,
   EntryDate date     not null with default,
   DueDate   date     not null with default,
   CustomerID dec(7)  not null with default
 );

create or replace table bvvdtl
 ( OrderID   dec (9),
   Line      dec (3),
   Item      char(6) not null with default,
   Quantity  dec (3) not null with default,

   primary key (OrderID, Line),

   constraint bvvdtl_fk_orderID
      foreign key (OrderID)
      references bvvhdr (OrderID)
      on delete cascade
 );

The ORDERID column is the generated key value in the header. That is, the database manager generates a new order number every time we insert a new row into the BVVHDR table.

insert into bvvhdr (entrydate, duedate, customerid)
   values ('2019-09-09', '2019-10-01', 1)

But that’s not the way I usually use INSERT in production jobs. I usually write RPG programs that insert entire data structures, so let’s see some code like that.

ctl-opt nomain
        option(*srcstmt: *nodebugio);

/copy copybooks,bvv

dcl-s  Nx   int(10);

dcl-proc  CreateSale   export;

   dcl-pi CreateSale;
      ioHeaderData             likeds(Header_t);
      ioDetailData             likeds(Detail_t)   dim(10);
      inDetailCount            int(10)            const;
   end-pi;

   dcl-ds  DetailData          likeds(Detail_t) based(pDD);

   exec sql
      insert into bvvhdr
         overriding user value
         values(:ioHeaderData);

   // retrieve the generated key
   exec sql
      values Identity_val_local()
        into :ioHeaderData.OrderID;

   for Nx = 1 to inDetailCount;
      pDD = %addr(ioDetailData (Nx));
      DetailData . OrderID = ioHeaderData.OrderID;
      DetailData . Line    = Nx;
      exec sql
         insert into bvvdtl
            values (:DetailData);
   endfor;
   return;

end-proc  CreateSale;

For completeness, here’s the copybook to which it refers.

**free                                                                  
dcl-ds  Header_t ext extname('BVVHDR') alias qualified template end-ds; 
dcl-ds  Detail_t ext extname('BVVDTL') alias qualified template end-ds; 
                                                                        
   dcl-pr CreateSale;                                                   
      ioHeaderData             likeds(Header_t);                        
      ioDetailData             likeds(Detail_t)   dim(10);              
      inDetailCount            int(10)            const;                
   end-pr;                                                              

Let’s break it down.

   exec sql
      insert into bvvhdr
         overriding user value
         values(:ioHeaderData);

The insert refers to a data structure, ioHeaderData, which came into the subprocedure as a parameter. This data structure includes all the fields in the header record, including the order ID. I had to include OVERRIDING USER VALUE to tell the system to ignore the ORDERID field in the data structure.

The system assigns an order number to the new order. How can I retrieve the generated order ID so that I can assign it to the details? There are two methods—use the IDENTITY_VAL_LOCAL function or use a table reference (SELECT FROM INSERT). I call the former “Old Reliable”, but the SQL reference manual says this:

Alternative to IDENTITY_VAL_LOCAL:

It is recommended that a SELECT FROM INSERT be used to obtain the assigned value for an identity column. See table-reference for more information.

These are weasel words. For one thing, the sentence is in passive voice, which is something which is avoided by me. Who recommends it? They don’t say.

Second, there’s no explanation. Why does this unknown person (or these unknown persons) recommend it?

Maybe I’ll write about that method one of these days, but for this example, I’ll stick with Old Reliable.

   // retrieve the generated key
   exec sql
      values Identity_val_local()
        into :ioHeaderData.OrderID;

Now the header data structure has been updated with the newly assigned order number, which we can copy into the ORDERID column of the detail data structure.

   dcl-ds  DetailData          likeds(Detail_t) based(pDD);

   for Nx = 1 to inDetailCount;
      pDD = %addr(ioDetailData (Nx));
      DetailData . OrderID = ioHeaderData.OrderID;
      DetailData . Line    = Nx;
      exec sql
         insert into bvvdtl
            values (:DetailData);
   endfor;

The header and the details are tied together nicely and neatly.

Updating presents a slight challenge. If you try to change the order ID of a header record, the system will respond with nasty message SQL0798 (Value cannot be specified for GENERATED ALWAYS column ORDERID). However, that does not mean that the generated column value cannot be changed. This is confusing, so first let me show you how to make it work correctly, then I’ll add a little more information.

Here’s a short program that retrieves a row, changes a column, and then writes the data structure back to the database.

**free
ctl-opt bnddir('THOLT')
        option(*srcstmt: *nodebugio);

/copy copybooks,bvv

 dcl-ds HeaderData   likeds(Header_t)  inz;
 dcl-s  OID          int(10);

   exec sql set option commit=*none;

   OID = 1;

   exec sql
      select * into :HeaderData
        from bvvhdr
       where orderID = :OID;

   HeaderData.CustomerID = 2;

   exec sql
      update bvvhdr
         overriding system value
         set row = :HeaderData
       where OrderID = :OID;
   return;

Notice the UPDATE, specifically the part that says OVERRIDING SYSTEM VALUE. This tells the system not to increment the ORDERID. “Wow!” I hear you say. “You mean you have to tell the system that!?” Believe it or not, you do. If I specify OVERRIDING USER VALUE, as I did with the INSERT, the system will update the order number with the next available number. And if you omit the OVERRIDING option, the program won’t compile.

There you have the basics: how to define a serial number in the database, how to retrieve it so it can be written into other tables, and how not to increment it when you shouldn’t. For more information, see the links below.

RELATED RESOURCES

IDENTITY_VAL_LOCAL

Identity columns

INSERT

UPDATE

Comparison of identity columns and sequences