Increment Row Number

[gregsimpson]

I posted the following on the DB2 board but they have not been able to help. Anyone over here any ideas please.

Thanks
Greg

I have inherited the following table on OS/390 DB2 version 5. Unfortunately DB2 Version5 on OS/390 doesn't have the nicer features such as rowid and sequence.

The table looks as below

Customer Bacs_Amt Bacs _Date Bacs_Name Bacs_Row_Num

000000001 29.30 29/02/2002 Pension 0
000000001 29.30 29/02/2002 Pension 0
000000002 31.00 02/02/2002 Sky Tv 0
000000002 11.00 02/02/2002 TV Licence 0

As you will see from the table there is nothing to make the rows unique. It is for instance possible to pay your wifes pension and your own pension to the same provider, on the same day for the same amount. I am writing a front end application against this table and need to be able to guarantee each row is unique.

To this end I have altered the table by adding the Bacs_Row_Num colum(Integrer). By incrementing this value I can guarantee uniqueness on the table.

Does anyone know of a way of writing some SQL to increment the Bacs_Row_Num column or must I write a program. I don't mind if it increments from 1 to 9999999... or goes back to one and starts incrementing once again whenever customer_id changes. I am simply trying to avoid having to write a program.

Desired results are therefore along the lines of

Customer Bacs_Amt Bacs _Date Bacs_Name Bacs_Row_Num

000000001 29.30 29/02/2002 Pension 1
000000001 29.30 29/02/2002 Pension 2
000000002 31.00 02/02/2002 Sky Tv 1
000000002 11.00 02/02/2002 TV Licence 2

or
Customer Bacs_Amt Bacs _Date Bacs_Name Bacs_Row_Num

000000001 29.30 29/02/2002 Pension 1
000000001 29.30 29/02/2002 Pension 2
000000002 31.00 02/02/2002 Sky Tv 3
000000002 11.00 02/02/2002 TV Licence 4


Thanks
Greg





Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



Nick23 (Programmer) Sep 16, 2002
what about writing a trigger? does v5 supports triggers?


Mark this post as a helpful/expert post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



MarcLodge (Programmer) Sep 17, 2002
Greg,
Are you talking initial load of new field? I'm assuming so as I guess the application would need to be amended to update the new column.
Marc
Ps. Awful table by the way!!!!


Mark this post as a helpful/expert post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



gregsimpson (Programmer) Sep 22, 2002
Marc,

as I said

Does anyone know of a way of writing some SQL to increment the Bacs_Row_Num column or must I write a program. I don't mind if it increments from 1 to 9999999... or goes back to one and starts incrementing once again whenever customer_id changes. I am simply trying to avoid having to write a program.

I have altered the table therefore the new column exists. I was just wondering if anyone was clever enough to give me some SQL which I could use to incrementally and uniquely amend the values in the row number column.

Although I only recently inherited this table I still feel the need to defend it. Unless you know the business and technical reasons behind why something was created as it is, I don't think it is fair or useful for you to comment with "Awfull table ...... Suffice to say however that I was more interested in an SQL solution for the incremental problem than anything else.

Cheers
Greg





Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



MarcLodge (Programmer) Sep 23, 2002
Greg,
I'm still not sure what you are looking for, hence my last post attempting to clarify things. Where do you require this piece of SQL to go? In the table definition? In a program? In a separate application? Your post does not make it clear.

My comment about the table being awful was a throwaway line, meant to show a certain amount of sympathy for your plight. Sorry it seems to have caused you concern.

Marc


Mark this post as a helpful/expert post!


Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!



gregsimpson (Programmer) Sep 26, 2002
Marc,

the table is loaded. All I want to do is increment the value in the BACS_ROW_NUM column to give each row a unique id.
I was simply wondering whether someone is able to knock up a peice of SQL I could submit through SPUFI to do this. This was simply to avoid having to write a program.

Are you able to show me how to do this?

Thanks
Greg

 

[angiole]

I remember having a similar situation, with an inherited table.

Like you I added an XX_ROW number column.

What I did was write a cursor to select all the ROWID values (speaking Oracle here, I'm inclined to think DB2 has a hidden/pseudo column that physically identifies the rows of a table as well), then for each ROWID a counter variable is incremented and an update step sets the XX_ROW value.

I know you're looking for a simple 1 step SQL to do this, I'm not sure it's possible.

The cursor/procedure idea is only marginally longer to write, but allows more control.

Hope this helps...

Cheers AA 8~)

 

[PruSQLer]

Ugh, pretty tough to do that with SQL when you have duplicates. I had a similar table and used MS Access to solve the problem. I created a CSV text file, imported it to Access and let it generate a sequential Primary Key for me. Then I just loaded the subsequent data into the original table. Of course, you need to be able to create a delimited text file to do that. Once you have the unique values, you can use a correlated sub query to generate sequential IDs. That's the best I can come up with.

My table was about 60,000 rows. I also use DB2 but having RapidSQL makes it easy to create the delimited file. Somehow I doubt SPUFI is as accomodating.

 

[r937]

can you not just declare Bacs_Row_Num as an identity column?

"Adding an identity column: When you add an identity column to a table that is not empty, DB2 places the table space that contains the table in the REORG pending state. When the REORG utility is subsequently run, DB2 generates the values for the identity column in all existing rows and then removes the REORG pending status. These values are guaranteed to be unique, and their order is system-determined."

http://publib.boulder.ibm.com/cgi-b...10?SHELF=DSNSHHA2&DT=20010718164132#SPTAIDENC

rudy

http://rudy.ca/

 

[stoggers]

Hi,

I think that you will find that this works in DB2...

SELECT CUSTOMER,BACS,BACS_DATE,BACS_NAME,
ROW_NUMBER() OVER (PARTITION BY CUSTOMER ORDER BY BACS_DATE)
FROM TEST1 ORDER BY CUSTOMER,BACS_DATE

It needs a bit of tweaking to exactly match your requirements.

Regards,

Stoggers.

 

[stoggers]

Hi,

I forgot to add that you may have problems in converting the query to an update statement. This is because there isn't a way of uniquely identifying a row in the current table to link it to a row returned from the select statement.

It may be possible to do this using ROWID's but I am not too sure.

It may be simpler to create a copy of the table (CREATE new table LIKE oldtable) and INSERT from oldtable into newtable.

You will then have to take care of permissions, binds, indexes, constraints etc on the new table.

Regards,

Stoggers.

 

[MarcLodge]

Hi all,
I don't think Stoggers' solution is going to work. Greg stated at the beginning that he was working with DB2 version 5, and I'm fairly certain that a number of elements of the SELECT are not available in V5. As Stoggers rightly pointed out, incorporating this into an update query would be very difficult as you would be trying to update a table based upon values in a sub-select in the same table, which DB2 V5 can't do.

R937's solution will not work either (I think) as the link is to a manual for UDB2 V7. I don't think that V5 has the identity column concept.

I came across:
CREATE TABLE EMP_UPDATE
(UNIQUE_ID CHAR(13) FOR BIT DATA,
EMPNO CHAR(6),
TEXT VARCHAR(1000))

INSERT INTO EMP_UPDATE
VALUES (GENERATE_UNIQUE(), '000020', 'Update entry...'),
(GENERATE_UNIQUE(), '000050', 'Update entry...')

But that also doesn't work (I think) in V5.

It's an interesting problem that I thought could be solved using SQL, and possibly QMF, but after a fair deal of thought I can't come up with an answer and think Greg's probably already written the program.

But if anybody knows different......