Sequence Number

[gregsimpson]

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

 

[Nick23]

what about writing a trigger? does v5 supports triggers?

 

[MarcLodge]

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!!!!

 

[gregsimpson]

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

 

[MarcLodge]

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

 

[gregsimpson]

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

 

[MarcLodge]

Greg,
I've had a think about this, and I don't think you can do it. What you effectively want to do is to:
UPDATE BACSTAB
SET BACS_ROW_NUM = BACS_ROW_NUM + incrementing variable

I can't think of a way to manipulating the variable from within SQL/SPUFI. I did think of setting all values of the column to 1, then using QMF (as you're on a mainframe) and in the forms USAGE field use CSUM. This gives you a cumulative sum of each row of the report. Unfortunately, it does not manipulate the data in the fashion that you want. Looks great in the report but I couldn't see a way to get it into the table.

Sorry to not be of help. Let me know if anybody manages to come up with anything.

:-(

Marc

 

[stoggers]

Hi,

See my solution in:

thread220-368708