MVSFORUMS.com

mayuresh.tendulkar

Hi All,

There is a DB2 table EMPLOYEE, with the following fields

Sr No. 9(03) <---- Primary Key, Autogenerated, starting from 1 incremented by 1 everytime the new record is added
Emp Name X(30)
Emp Address X(50)
...

etc.

Here the requirement is as below:

Say the table has 10000 records with Sr No starting from 1 to 10000. If I delete the record in between, then how can I find out the missing record's Sr No. programmtically?

Theres one way of doing it through a COBOL program as below:

Download the table in a FLAT file and then process it sequentially, Since the Sr No is starting from 1 and will be upto 10000, we can increment it in steps of 1 and compare it with Sr No, Wherever the record is missing, it will be displayed as deleted from table.

The other way of doing it is

2) Through EZTRIEVE also.
3) Through actually processing table in the COBOL program using cursor.

Do we have any other way of doing it?? Probably through DFSORT / ICETOOL???

Regards

Mayuresh Tendulkar

kolusu · Posted: Mon Jan 05, 2004 11:22 am Post subject:

Mayuresh,

There are many ways of doing it. you can find the missing number with a DB2 query itself.you can even find the missing number with sort product.

DB2 Query:

kolusu · Posted: Mon Jan 05, 2004 3:17 pm Post subject:

Mayuresh,

Just for kicks here is an easytrieve code and cobol code which will give the desired results.However I deliberately coded a bug in all the versions(easytrieve as well as cobol). can you find that? Laughing

Easytrieve code:

mayuresh.tendulkar · Posted: Tue Jan 06, 2004 2:28 am Post subject:

Hello Kolusu,

Thanks a million for the ICETOOL code.

I think the bug in the easytrieve & cobol code is:

What if i have deleted the last record from the table?? I will not be able to find out this record with these codes.

RIGHT??

Moreover, the DB2 query you gave was also great.

We could have done it in a different way as below:

Since we know there are 10000 records starting from 1 with the increments of 1.

So we can write a query as:

1)
SUM(Sr No) ----> this will give us the sum of all the record's Sr No. (with 1 record deleted in between)

2)
N* ((N + 1)/2) will give you the sum of all the record's
here N = 10,000

3)
The difference of this 2 will give you the deleted Sr No.

Please let me know if i am wrong.

Laughing

Regards

Mayuresh Tendulkar

kolusu · Posted: Tue Jan 06, 2004 5:29 am Post subject:

mayuresh,

A couple of corrections. In your very first post you said that the Sr_no is defined is 9(03), but your example data you show the maximum value of the SR_NO as 10000 which I don't think will fit in a 9(03).

Your guess about the bug in my code is not true.

I am not really sure of your alternate solution. My first impression of the solution is that might not work . The reason being simple that the difference between 2 sums will always result in a single digit. what happens if there is more than 1 record which is deleted?

Take the following data

mayuresh.tendulkar · Posted: Tue Jan 06, 2004 7:53 am Post subject:

Hello Kolusu,

My mistake, it should have been 9(5).

Ya about the BUG also my first guess was wrong. While giving the answer I did not looked at the data you have supplied.

In the easytrieve code, you have deleted the 2 records with Sr No 6 & 11. So this will not cause any problems till the fields are matching but once the code has found the mismatched record at WS-NO = 6, it will display all the other records to the mismatched condition as we are incrementing the WS-NO counter twice.

kolusu · Posted: Tue Jan 06, 2004 9:01 am Post subject:

Mayuresh,

Thanks for clarifying about the size of the column. Now your second guess is also not accurate.

You said

mayuresh.tendulkar · Posted: Tue Jan 06, 2004 9:13 am Post subject:

Hello Kolusu,

Agreed, I did not gave it a thought.
It was really a refreshing session for me. Thanks a lot.

Regards

Mayuresh Tendulkar

coolman · Posted: Wed Jan 14, 2004 5:07 am Post subject:

Kolusu,
For the DB2 query, if the first row gets deleted, then the query would not capture it.

Cheers,
Coolman
________
digital vaporizer

kolusu · Posted: Wed Jan 14, 2004 2:28 pm Post subject:

coolman,

Yes ofcourse it does not find the missing record if the deleted record happens to be the very first record. hmm I need to fix it.

Here is another version of DFSORT/ICETOOL which will find all the missing seqnum.You need to have DFSORT PTF UQ90053 installed for this job to work. A brief explanation of the job. The first sort operator will sort the file on seqnum descending so that I will get the max seqnum in the table. It create control file for generating the seqnum upto the max seqnum.

The second copy operator will the control file created in the first sort step and generates seqnum file using REPEAT parm,so that the file will have seqnum in serial upto the max number.

Now concatenate both the files(input & generated senum file) and using SELECT with NODUPS to find the missing numbers.

Frank Yaeger · Posted: Thu Jan 15, 2004 10:44 am Post subject:

I just realized that we can change Kolusu's DFSORT/ICETOOL job slightly to replace the SORT with a COPY by using TRAILER1 to create the OUTREC statement. TRAILER1 will pick up the sequence number from the last record. Here's the modified DFSORT/ICETOOL job:

kolusu · Posted: Thu Jan 15, 2004 12:14 pm Post subject:

Frank,

Using copy instead of sort is good idea and it also saves time . Why didn't I think of it Sad

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Frank Yaeger · Posted: Thu Jan 15, 2004 1:08 pm Post subject:

Kolusu,

Well, for that matter, why didn't I think of it when we were discussing this problem offline. Embarassed

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

kolusu · Posted: Thu Jan 15, 2004 3:01 pm Post subject:

Frank,

Aha I remember now why I wanted to sort the data instead of copy.I need the max but not the last record. The last record is not always the max seqnum. what if the data is not sorted on the seqnum?.

for ex take a look at this data :

Frank Yaeger · Posted: Thu Jan 15, 2004 3:38 pm Post subject:

Kolusu,

The first post says