Re: Opinions on approach, please...



"Frederico Fonseca" <real-email-in-msg-spam@xxxxxxxxx> wrote in message
news:emrl34hci2k3ulss7lqcglo6sb3u1h2b8e@xxxxxxxxxx
On Mon, 26 May 2008 15:00:37 GMT, "William M. Klein"
<wmklein@xxxxxxxxxxxxxxxxx> wrote:

<all previous parts of the thread are snipped>

Pete,
I the reason that I mentioned "locks" is that I am familiar with
applications
that INTENTIONALLY used "locks" (of one sort or another) to serialize
specific
steps. They didn't use them to "protect" from data corruption. Rather
they
would use a "control" record to be "locked" when one
user/part-of-the-application/whatever was doing something to insure that
other
parts/users didn't "start" a new process.

Micro Focus and the '02 Standard have "wait" syntax (within READ
processing -
and as a separate statement). I couldn't find this in my Fujitsu dox, so
this
may not be a problem there. (They do have file status codes for
accessing
locked records).

As I say, this is not a question of 'accidentally" having two processing
attempting to update the same record; rather it is the use of specific
records
(or files) to serialize processing.
This is possible in some, if not all RDBMS. In case of DB2 for example
one would declare a select WITH RR (or RS) USE AND KEEP UPDATE (or
exclusive) LOCKS.

While it seems simple, it can have a negative effect if the table
space is applying page lock lvl (most common) and not row level lock.
It all comes down to database design taking in consideration what the
application(s) that access same database do, and is never easy to get
it right.


***

I also wanted to explain (a bit) about the "multiple positioning" point
that I
tried to make earlier. I don't know if this is used with non-IBM
mainframe
COBOLs - or even if it is still used (much) on IBM mainframes.

The techniques was where you had two (or more) File-Control entries -
pointing
to the same physical (VSAM) file - one via its "primary" key and one via
an
alternate key (or possibly both via separate alternate keys). Using START
and
READ NEXT processing, you could then process "simultaneously" through the
same
physical file in two different sequences. I could be mistaken, but I
don't
think that SQL can (easily) do this via two "simultaneous" cursors for the
same
database. However, if this isn't something that you have run into, then
it may
well be a non-issue with the types of applications that you would want to
convert.
It is perfectly possible to do it. bad performance most times unless
the cursor specifies a small enough number of records.
e.g. if table_a has following indexes.
ix_1
company
department
client
invoice_number
invoice_line

ix_2
company
department
product_code
invoice_number

It is perfectly possible to declare 2 cursors. one for each index (or
even for same index) and have both open at same time, and scrool in
each one as needed.

Regarding COBOL and updates.
Several cursors can be open at the same time on same table.
Problem arises when a update occurs to that same table.
With standard COBOL indexed files, if any process added a records PAST
the sequence the current process is reading, then the current process
would pick it up.
With SQL this depends on the isolation lvl used when defining the
SELECT.
Most common is CS - cursor stability. This one will NOT read rows have
not yet been commited by other processes. But it will also prevent
other processes from updating the current row of the open cursor.
Only type of isolation lvl that gives exactly the same behaviour as
COBOL is UR - uncomitted read. But this should really not be used by
update programs, as you may get dirty data on the database. Should
only be used by inquiry programs where content of the data is not that
important.

This may or not be important on Pete case. Without knowing exactly how
their current application processes its files, and what type of file
structure they have it is impossible to know if they will find this
type of problem. or even others.


Your point on isolation is something I did consider. I'm pretty certain it
won't be a problem. Here's why I think it will be OK....

1. If the MOST system duplicates exactly the current ISAM operations, there
should be no problem. That's why cursors will NOT be opened for UPDATE and
all updating will be done by singletons. You are absolutely correct that
updates made to records further down the sequence will not be picked up by
the RDBMS, because it uses a result set that won't be refreshed.

2. If we do encounter problems with certain processes, I'll consider adding
UR cursors. MOST knows exactly what kind of indexed access is being done, so
it can make a decision as to which kind of cursor to use. I honestly don't
think it will come to that.

Thanks for your post, Frederico.

Pete.
--
"I used to write COBOL...now I can do anything."


.

Relevant Pages

  • Re: Multiple problems with a running job
    ... SQL Server MVP ... > database that I know is unchanging), making it impossible for me to run ... I'm declaring the cursors as LOCAL STATIC. ... > to prevent locks. ...
    (microsoft.public.sqlserver.programming)
  • Re: Opinions on approach, please...
    ... I the reason that I mentioned "locks" is that I am familiar with applications ... It all comes down to database design taking in consideration what the ... It is perfectly possible to declare 2 cursors. ... With standard COBOL indexed files, if any process added a records PAST ...
    (comp.lang.cobol)
  • Re: Cursor in a trigger
    ... All the locks that your transaction has taken ... out are held for the durtion of the trigger, and cursors are slow, so the ... > Is it a good thing to put a Cursor in a trigger? ...
    (microsoft.public.sqlserver.programming)
  • Multiple problems with a running job
    ... I've got a stored procedure that essentially consists of about a half dozen ... database that I know is unchanging), making it impossible for me to run ... I'm declaring the cursors as LOCAL STATIC. ... to prevent locks. ...
    (microsoft.public.sqlserver.programming)
  • Re: Decouple SQL queries from class in OOP design
    ... > when mixing SQL queries directly with the client classes. ... > Some of my database tables are not stable during the development ... That is done for a reason. ... or leaks cursors this is the way to catch it. ...
    (comp.object)