Guru: An Update Conundrum Finally Solved!

January 8, 2018 Ted Holt

Russ writes, “Hey, Ted! I was trying to use a pure SQL solution to adjust some data today. My SQL statement worked fine in quality control, but failed in production. I’ve been wondering if the failure was caused by the database or by me! My problem was to renumber sequence numbers for a customer in a table.”

Russ’s question arrived in my inbox on February 1, 2012. Yes, almost six years ago. At the time, I couldn’t help him. But with the latest technology refreshes from IBM, there is now a way to make the update work properly, and I’m about to show you how.

First, let me explain Russ’s situation. A certain table (physical file) could contain more than one row (record) for each customer. Each row was uniquely identified by customer number and a three-digit packed decimal sequence number. A certain customer, to whom we’ll refer as customer 123, had rows with sequence numbers approaching 999, the limit. Russ needed to reorder the rows, starting at one, to eliminate the gaps and free up more sequence numbers at the end of the series.

Here’s a table we can use to illustrate:

create table mytable                         
  (customer char(3), 
   seq dec(3),
   otherdata char(12));

insert into mytable values                             
('123', 10, 'S'),('123', 11, 'Q'),
('123', 12, 'L'),('123', 14, ' '),
('123', 21, 'R'),('123', 27, 'U'),
('123', 55, 'L'),('456', 10, 'Z'),
('456', 11, '!');

Russ decided to create a temporary table with new sequence numbers. The ROW_NUMBER OLAP function generates a new series of sequence numbers.

declare global temporary table MyFix
  (cust, rownbr, orignbr)
as
  (select customer,
          row_number() over(),
          seq
     from MyTable 
    where customer = '123'
    order by customer, seq)
with data;

Next he updated the production table from the temporary table.

update MyTable as a
    set a.seq = (select rownbr
                   from session.MyFix
                  where a.seq = orignbr)
  where customer = '123';

Voilà! New sequence numbers for 123! The fact that the update worked properly during testing was pure luck. When this statement ran in production, the system threw a duplicate-key error when the update tried to change the sequence number of one row to the value of the sequence number of a row that had not yet been updated.

Until now, DB2 updated the rows in no particular order. Russ needed a way to tell the UPDATE statement to update the selected rows in order by the sequence column, something that was impossible in 2012. Not anymore! The latest TR allows an UPDATE statement to have an ORDER BY clause.

update MyTable as a
    set a.seq = (select rownbr
                   from session.MyFix
                  where a.seq = orignbr)
  where customer = '123'
  order by customer, seq;

Let me tie this up with a couple more salient facts.

(1) You can also use ORDER BY with the DELETE statement. This probably makes no sense unless you pair the ORDER BY with the LIMIT clause, which brings me to salient fact #2.

(2) UPDATE and DELETE now support OFFSET and LIMIT. LIMIT specifies the maximum number of rows to update or delete. This query deletes the two rows that have the lowest sequence number.

delete from mytable
 order by seq
 limit 2;

OFFSET lets you tell DB2 how many rows to skip. This statement deletes up to two rows, beginning with the fourth row in the result set, for customer 123.

delete from mytable
  where customer = '123'
  order by seq
  limit 2
  offset 3;

If the sequence numbers for customer 123 are 10, 11, 12, 14, 21, 27 and 55, the system deletes 14 and 21.

Russ ended up writing an update routine in RPG using record-level access, the same thing I have done in similar situations. Whether I will use RLA again for that task remains to be seen.

RELATED STORIES

SQL Paging With Limit And Offset In DB2 For i

IBM i Technology Refresh – Information for IBM i 7.2

IBM i Technology Refresh – Information for IBM i 7.3