Table Design

kishore erukulapati

Table Design
Hi DBAS
There are 10 COBOL programs which insert file-names along with few other
details into a table .There is one CONCAT Program which reads this table
concatenates based on some flag and writes the output file-names into the
same table . Some other PROCESS program will again read this table and
processes the rows which were inserted by CONCAT program .
All the above 10 Programs can run concurrently with CONCAT program
What will be the best design for this table ?

We thought of having current time stamp and the current program as the
primary key for this table . ( To avoid -803 when 2 programs are trying to
insert into the above table at the same time )
We plan to have row-locking to avoid -911 .

To distinguish between the input rows to CONCAT program / PROCESS
Program we have another column with next_program . Status column to
distingush between Processed and Unprocessed rows .
We plan to have non-unique index on these 2 columns .

What will be the best table design for the above Scenario ?

Thanks
Kishore
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Joseph Burns

Re: Table Design
(in response to kishore erukulapati)
Kishore,

I am not sure I understand exactly what this table is desinged to
do, but using row-locking to avoid -911 on inserts will probably
not help.

On an insert, if DB2 sees that the page is in use, then it will
not give a -911, instead it goes through an algorithm and picks
another page. The biggest exception to this rule is in the case
of a -803 (duplicate row).

For a -803, DB2 will wait to see if a process commits
or rolls back before doing anything. And you can get a -911 in
this case but even row locking won't save you because on a -803
you are talking about the same row anyway.

If you are considering row-locking to prevent -911 for reader
and a writer type of contention then that may be helpful.

Hope this helps,
Joe Burns
DB2 Database Services

All opinions are my own, and not those of my employer.







kishore erukulapati <[login to unmask email]> on 12/23/99 12:21:50 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Joseph Burns/ISG/CORP/Highmark)
Subject: Table Design




Hi DBAS
There are 10 COBOL programs which insert file-names along with few other
details into a table .There is one CONCAT Program which reads this table
concatenates based on some flag and writes the output file-names into the
same table . Some other PROCESS program will again read this table and
processes the rows which were inserted by CONCAT program .
All the above 10 Programs can run concurrently with CONCAT program
What will be the best design for this table ?

We thought of having current time stamp and the current program as the
primary key for this table . ( To avoid -803 when 2 programs are trying to
insert into the above table at the same time )
We plan to have row-locking to avoid -911 .

To distinguish between the input rows to CONCAT program / PROCESS
Program we have another column with next_program . Status column to
distingush between Processed and Unprocessed rows .
We plan to have non-unique index on these 2 columns .

What will be the best table design for the above Scenario ?

Thanks
Kishore
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com