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