Joined: 26 Nov 2002 Posts: 12399 Topics: 75 Location: San Jose
Posted: Thu Jan 15, 2004 4:00 pm Post subject:
Frank,
Yes they are incremented SEQUENTIALLY in the DB2 table. But When you unload the DB2 table to a flat file , you are not guaranteed that you will get the seqnum in order unless they use an ORDER by clause while unloading the data.
Would it make a big difference if we use max on the trailer parm?
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Thu Jan 15, 2004 5:01 pm Post subject:
Quote:
Would it make a big difference if we use max on the trailer parm?
MAX would work fine and would cover the more general case. _________________ 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
Joined: 15 Dec 2002 Posts: 637 Topics: 43 Location: Bengaluru, INDIA
Posted: Sun Jan 18, 2004 10:09 am Post subject:
Kolusu,
Will your code (the latest ICETOOL code in this thread) work if the last record is deleted?
Assume, that the highest serial number is currently 12345 and (for sake of simplicity) there are as many rows in the table. The previous serial number as per Mayuresh's description would be 12344. If 12345 is deleted from the database, then the unloaded file will have 12344 records with the highest serial number as 12344.
Your code would now generate the control card for CTL2CNTL as:
When CTL2CNTL is used for NULL, then there will be 12344 rows with sequence numbers running from 1 to 12344 sequentially. If 12345 is the only deleted record from the erstwhile table of 12345 rows, then the output file will be empty!
When the maximum and some other record(s) is deleted, then the output will have that "some other record(s)" and will miss out the highest serial number. This is because, the CTL2CNTL would be created till the max - 1 serial number.
I do not think any "out of database" solution will help. This is because, by looking at the unload file of 12344 records one cannot guess if the table actually had 12344 records or it had 12345 records with 12345 deleted.
The best thing would be to take a "before" and "after" unloads and then use a simple SELECT with NODUPS in ICETOOL. That would be an all-encompassing solution.[/u] _________________ ALL opinions are welcome.
Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Joined: 26 Nov 2002 Posts: 12399 Topics: 75 Location: San Jose
Posted: Mon Jan 19, 2004 9:42 am Post subject:
Cogito,
You are right about that the job will not the get last record. But if you read the post clearly , the aim is find the missing seqnum. Even though you can term the last record as a missing number , but it is not going to affect any of the process. i.e you can always insert a new record with MAX(SEQNUM) + 1 . But if a seqnum is deleted in the middle then you cannot really find it unless you have before and after copies.
Let us take this scenario.you take a backup of the table at 6 am and the user inserts a new record with max seqnum(say 12345) and later on at 3 pm you decide to delete the row and there is no further activity on the table. Now you take a backup at 6 pm. Now your output will be empty as the backup and new unload will have the same no: of rows.
In my opinion I don't think the max record deletion is a problem at all.
Joined: 15 Dec 2002 Posts: 637 Topics: 43 Location: Bengaluru, INDIA
Posted: Mon Jan 19, 2004 10:08 am Post subject:
Kolusu,
Insert followed by a delete will not cause a problem. But, if there were only delete then, backup and new unload will be different. It is for a such scenario, I suggested a SELECT...NODUPS for the backup+new unload files.
Of course, insert+delete of a sequence number does not mean that, that sequence number is missing. So, it is not a problem. But, only delete (for a max or min or middle number) will cause differences between backup and new unload.
Maybe Mayuresh needs to come back for this. _________________ ALL opinions are welcome.
Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
All times are GMT - 5 Hours Goto page Previous1, 2
Page 2 of 2
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
