Sequence number for a Batch

[tojigneshshah]

Hi,

I have a situation where i have multiple batch and the each batch are
sequence numbers. For each batch, the number should start with 1.
For example:

Col.no1 Col.no2
------ -------
batch1 1
batch1 2
batch1 3

batch 2 1
batch 2 2

batch 3 1

batch4 1
..... .....

Is there a easy way to do it in DB2 while creating a table?

Identity column in DB2 simply creates numbers in sequence.

Please advise
Jignesh

[Serge Rielau]

to***********@gmail.com wrote:

Hi,

I have a situation where i have multiple batch and the each batch are
sequence numbers. For each batch, the number should start with 1.
For example:

Col.no1 Col.no2
------ -------
batch1 1
batch1 2
batch1 3

batch 2 1
batch 2 2

batch 3 1

batch4 1
..... .....

Is there a easy way to do it in DB2 while creating a table?

Identity column in DB2 simply creates numbers in sequence.

Please advise
Jignesh

Are your batches finite? You could create a sequence/batch and write a
function doing dynamic SQL to use the appropriate sequence.

Otherwise you could use thr ROW_NUMBER() OVER(PARTITION BY
<batchcolumn>) OLAP function possibly offset by the max number for the
batch before the insert.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

Will the batches ever have items removed? If they are, will they need
reordering?

You may need a FUNCTION to reorder everything for you. If gaps in the
sequence to not matter, a SEQEUNCE can be used.

B.

[tojigneshshah]

Hi Serge,

Nopes, my batch are infinite. I created a sequence but it just
increments the sequence number even after a having a new batch.

Please advise.
Jignesh

Are your batches finite? You could create a sequence/batch and write a
function doing dynamic SQL to use the appropriate sequence.

Otherwise you could use thr ROW_NUMBER() OVER(PARTITION BY
<batchcolumn>) OLAP function possibly offset by the max number for the
batch before the insert.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[tojigneshshah]

Can someone show some example of inserting records with sequence number
in a batch?

regards
Jignesh
to***********@gmail.com wrote:

Hi Serge,

Nopes, my batch are infinite. I created a sequence but it just
increments the sequence number even after a having a new batch.

Please advise.
Jignesh

Are your batches finite? You could create a sequence/batch and write a
function doing dynamic SQL to use the appropriate sequence.

Otherwise you could use thr ROW_NUMBER() OVER(PARTITION BY
<batchcolumn>) OLAP function possibly offset by the max number for the
batch before the insert.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

to***********@gmail.com wrote:

Can someone show some example of inserting records with sequence number
in a batch?

How does your batch processing look like. Can you paraphrase with pseudo
code?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[tojigneshshah]

Serge,

Here's the description of table.

Table: receipt_check

c1: receipt_id char(11) not null ,
c2: receipt_seq int not null generated by default as identity (start
with 1, increment with 1, .........),
c3: receipt_dt timestamp,
c4: receipt_user_nm varchar(35)

Records in the table receipt_check looks something like below:

c1 c2
--- -----
111-22-3333 1
111-22-3333 2
111-33-4444 3
111-33-4444 4
111-33-4444 5
222-44-5555 6
I want to create a new column called c5 (for grouping c1 and c2). And
the table/records should display something like below:
c1 c2
c5 (new column)
--- -----
--------------------
111-22-3333 1
1
111-22-3333 2
2
111-33-4444 3
1
111-33-4444 4
2
111-33-4444 5
3
222-44-5555 6
1
Any help is greatly appreciated.

regards
Jignesh

[Serge Rielau]

to***********@gmail.com wrote:

Serge,

Here's the description of table.

Table: receipt_check

c1: receipt_id char(11) not null ,
c2: receipt_seq int not null generated by default as identity (start
with 1, increment with 1, .........),
c3: receipt_dt timestamp,
c4: receipt_user_nm varchar(35)

Records in the table receipt_check looks something like below:

c1 c2
--- -----
111-22-3333 1
111-22-3333 2
111-33-4444 3
111-33-4444 4
111-33-4444 5
222-44-5555 6
I want to create a new column called c5 (for grouping c1 and c2). And
the table/records should display something like below:
c1 c2
c5 (new column)
--- -----
--------------------
111-22-3333 1
1
111-22-3333 2
2
111-33-4444 3
1
111-33-4444 4
2
111-33-4444 5
3
222-44-5555 6
1

OK, this will populate your new column:
UPDATE (SELECT c5, row_number(partition by c1 order by c2) as rn
FROM receipt_check) AS U
SET c5 = rn;

To maintain it (I assume you only do INSERT....) you will need:
CREATE TRIGGER trg1 BEFORE INSERT ON receipt_check
REFERENCING NEW AS n FOR EACH ROW
SET n.c5 = (SELECT COUNT(1) + 1 FROM receipt_check WHERE n.c1 = c1)

assuming single row insert. For multi row insert you will need to
replace +1 with the scratchpad counter function in sqllib/samples/c

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[tojigneshshah]

Hi Serge,

I am little confused so let me display the entire scenario one more
time.

Here's real scenario.

I have table called 'validation' in one database. And the columns are
as follows:

receipt_id char(11) not null,
receipt_seq integer not null,
payee_num char(5),
payment_amt decimal(16),
email_addr varchar(35),
payee_addr1 varchar(35),
payee_addr2 varchar(35),
payee_addr3 varchar(30)

The table validation already has data and it looks something like this:

receipt_id receipt_seq payee_num
------------- --------------- -------------
001100AB 1 12341
001100AB 1 23232
001100AB 1 65432
001100BA 1 23424
001100BA 1 64352
001100BB 1 23627
001100BB 1 90201
002130CC 1 89292
002530DA 1 67782

Now, i wanted to load the data from table validation into a new table
called validation_new with new column (receipt_num) in the new table
but in another database.

The new column (receipt_num) in a new table (validation_new) will look
like this:

receipt_id char(11) not null,
receipt_seq integer not null,
receipt_num integer not null,
payee_num char(5),
payment_amt decimal(16),
email_addr varchar(35),
payee_addr1 varchar(35),
payee_addr2 varchar(35),
payee_addr3 varchar(30)

And the record should look something like this:

receipt_id receipt_seq receipt_num payee_num
------------- --------------- --------------- -------------
001100AB 1 1 12341
001100AB 1 2 23232
001100AB 1 3 65432
001100BA 1 1 23424
001100BA 1 2 64352
001100BB 1 1 23627
001100BB 1 2 90201
002130CC 1 1 89292
002530DA 1 1 67782
So, how to load the data from the flat file with new receipt_num?

Any help would be of greatly appreciated.
Thanks
Jignesh

Serge Rielau wrote:

to***********@gmail.com wrote:

Serge,

Here's the description of table.

Table: receipt_check

c1: receipt_id char(11) not null ,
c2: receipt_seq int not null generated by default as identity (start
with 1, increment with 1, .........),
c3: receipt_dt timestamp,
c4: receipt_user_nm varchar(35)

Records in the table receipt_check looks something like below:

c1 c2
--- -----
111-22-3333 1
111-22-3333 2
111-33-4444 3
111-33-4444 4
111-33-4444 5
222-44-5555 6
I want to create a new column called c5 (for grouping c1 and c2). And
the table/records should display something like below:
c1 c2
c5 (new column)
--- -----
--------------------
111-22-3333 1
1
111-22-3333 2
2
111-33-4444 3
1
111-33-4444 4
2
111-33-4444 5
3
222-44-5555 6
1

OK, this will populate your new column:
UPDATE (SELECT c5, row_number(partition by c1 order by c2) as rn
FROM receipt_check) AS U
SET c5 = rn;

To maintain it (I assume you only do INSERT....) you will need:
CREATE TRIGGER trg1 BEFORE INSERT ON receipt_check
REFERENCING NEW AS n FOR EACH ROW
SET n.c5 = (SELECT COUNT(1) + 1 FROM receipt_check WHERE n.c1 = c1)

assuming single row insert. For multi row insert you will need to
replace +1 with the scratchpad counter function in sqllib/samples/c

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Simply import into the new table and set the column to a default value.
Then run the UPDATE statement I provided to populate the column.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[tojigneshshah]

Hi Serge,

Appreciate all your kind replies. It looks simple, but still i m facing
some problem while importing data.

From the previous mail, I have created a new column with 'default 0'. While i import from flat file (of del), the first row is getting
inserted, while other records are rejected by violating primary key
constraints and getting SQL0803N.

Any idea wat is causing this?

thanks
Jignesh

Serge Rielau wrote: Simply import into the new table and set the column to a default value.
Then run the UPDATE statement I provided to populate the column.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

to***********@gmail.com wrote:

Hi Serge,

Appreciate all your kind replies. It looks simple, but still i m facing
some problem while importing data.

From the previous mail, I have created a new column with 'default 0'.

While i import from flat file (of del), the first row is getting
inserted, while other records are rejected by violating primary key
constraints and getting SQL0803N.

Any idea wat is causing this?

I assume your new PK includes teh new column?
Of course that won't work until teh update is doen.
So, leave the PK away, import, update, then ALTER TABLE ADD PRIMARY KEY.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[tojigneshshah]

altast, it work the way i want.

Here's what i did.

1.Created a temp table.
2.loaded the data into the temp table.
3. exported the data from the temp table with rownumber () over ...
4. created new table with the new table. (without the PK)
5. loaded the data into the new table with the selected column (leaving
all not null columns)
6. updated the new column (receipt_num) with the update with partition
by and order by.
7. created pk.

Thanks for your help.
Regards
Jignesh