Conference orarep::nomahs::dec_data_distributor

Don,

ENQLM in authorize is limited to 32K. You can increase this if you use 
the create process system service to create a process, but only to 64K.

Is there any way they can do the purge in an exclusive transaction, as I 
would suspect that record locks are your problem. Alternately, they could 
try and run the replication with no other users on the database and this 
should mean that Adjustable Locking Granularity (ALG) will minimise the 
number of record locks actually held. Make sure that ALG is enabled for the 
database (you can see this in RMU/DUMP/HEAD).

G'day,

Mark.

    RUN/ENQUEUE_LIMIT=n is the easiest way to get a quota between 32k and
    and 64k.
    
    Peter

    Thanks Mark and Peter.  Will post results here later.
    
    Don

    Last night the customer ran the same transfer, nothing's changed, and
    this time RDB$CHANGES gets purged.
    
    Sounds to me like some kind of system lock resource problem.
    
    Anyone got any ideas on what could be happening here?
    
    Thanks,
    Don

Well ENQLM is used for records, pages and buffers.

What is the NUMBER OF BUFFERS setting, also is RDM$BIND_BUFFERS defined?
Is ADJUSTABLE LOCK GRANULARITY enabled or disabled?
Was the purge running when there was a lot of active updaters to the
RDB$CHANGES table?  Maybe contention forced an escalation of the locks.

Ian

Don,

My first guess is that there wwas one or more other users on the database 
when the original purge was taking place that forced the purge to take out 
lots of record locks. If there are no other users on the DB then Rdb 
minimises the number of record locks actually held using the ALG mechanism.

RE: Your question in mail regarding exclusive transactions.

If you can do a massive update like this in an exclusive transaction, then 
record locks are not used, and therefore the total number of locks should 
be significantly reduced. I don't know of any way to do this unless you 
guesput a switch in the product to allow it.

G'day,

Mark.

    
    Mark,
    
    How exactly is guesputing done?  They left that part out of the last
    Rdb training class I took.  :-)

Jean-Claude,

>    How exactly is guesputing done?  They left that part out of the last
>    Rdb training class I took.  :-)

Actually it's not an Rdb feature. It's an engineering term to describe how 
they decide where to place new code changes into the product. :-)

G'day,

Mark.

    Thanks Mark and Ian.  Will follow up on your ideas.  I'm sure that
    there was db activity during the purge.  Customer has said that this is
    a 24x7 production db.
    
    Thanks again,
    Don

    I've got some more info from the customer.  ALG is disabled. The global
    buffer count is 3000 with each user getting 60.  RDM$BIND_BUFFERS is
    not defined.  Yes, there's a very good chance that contention could be
    a problem. 
    
    The replication transfer starts around 11:00PM.  If it's not finished
    by 2:00AM then source updates coming from 5 Rdb databases start
    occuring.  It's very possible that these transactions could be
    happening while DDD is trying to purge RDB$CHANGES.
    
    I received a purging algorithm from Claude so that this customer can
    write their own application and then use RUN/ENQLM= if they run into
    this problem again.  Thanks Claude!
    
    I'll post the algorithm in the next reply in case anyone ever needs it.
    
    Thanks,
    Don 

Algorithm for deleting rows from the RDB$CHANGES table of a source database.

1.  Determine the Cutoff Point

Examine the rows in the RDB$TRANSFERS table.  For example,

    RDB$TRANSFER_NAME	RDB$VINTAGE_TSER

    Portland		9760
    Seattle		9823

In this example there are two rows, one for each REPLICATION transfer run
against this source database.  Transfers run on different schedules, so
they may not all have processed the same number of transactions stored in
the RDB$CHANGES table.  The TSER column in this table indicates how far each
transaction has gotten.  TSER is short for Transaction SERialization number.
Transactions are marked with serial numbers so we can tell in which order the
transactions were committed.

Choose the lowest of the values in the TSER column from this table.  Any
transaction in the RDB$CHANGES table which has a TSER lower than that number
is sure to have been processed by all transfers and can safely be deleted
from RDB$CHANGES.

    NB	Delete all transactions with a TSER value L-O-W-E-R than the cutoff
        number.  We leave the last processed transaction lying around so
        that a sanity check can be performed when the transfer is next run.
        Never delete all the rows from the RDB$CHANGES table if the transfers
        are expected to continue to run as normal.

2.  Delete rows from RDB$CHANGES

It's not as simple as DELETE FROM RDB$CHANGES WHERE RDB$TRANSACTION_TSER < n.
The reason is because not all rows in RDB$CHANGES have a TSER value.  Bear
with me.

Each source transaction results in one or more rows being written to the
RDB$CHANGES table.  For a long transaction, that may mean several rows.
(Rows in RDB$CHANGES do not go one-for-one for rows inserted, updated or
deleted from the main tables).  The rows are written to RDB$CHANGES as they
are produced.  Since the TSER number for a given transaction is assigned at
COMMIT time, the TSER value appears only in the last row of the transaction.

Read the rows from RDB$CHANGES, sorted by ascending DDAL$TRANSACTION_TID
and by descending DDAL$TRANSACTION_SEQUENCE.  All rows associated with a
given transaction have the same TID number.  Read the following values from
the row:  RDB$TRANSACTION_TID, RDB$TRANSACTION_TSER, and the database key of
the row.  Sorting from highest to lowest sequence number means that for a
given transaction, you'll see the last row first.  The last row contains the
transaction's TSER value.  When you see a new transaction (a change in TID
value), you will have just gotten the TSER value for that transaction.
Compare it with the cutoff number.  If it is lower than the cutoff number,
you want to delete all rows that have that same TID value.  Delete the rows
by passing back the database keys.

    
    Correction to note 259.11:
    
    About halfway down the screen is a paragraph in which the following
    sentence appears:
    
    	"The TSER column in this table indicates how far each
    	transaction has gotten."
    
    Replace the word transaction with the word transfer.

Don,

With ALG disabled you will lock each row individually regardless of user 
activity. The only reason that would make sense for this transfer to have 
succeeded the second time would be if another transfer executed and purged 
some changes before the second run.

G'day,

Mark.

One has to wonder why anyone would want *extra* locking activity (do they have
CPU to burn?)

Ian

re: .13, .14

  Hi!  I'm basically the customer Don's been talking about ... at least I'm the
one that the real customer is using to work on some of their DB requirements.
I've been working with the customer for several years on several projects.  The
replication process being one of the projects, as was analyzing/improving their 
production databases performance.

  It's been a couple of years since the source DB's ALG was disabled ... so 
I'm going on what's left of my memory ;^).  I'm also trying to find my notes 
from back then ... I know it's in one of these boxes!

  I believe the reason we disabled ALG was due to performance and lock 
contention issues we were encountering when the 5 regional processors updated
the source database.  We have a very short window (from 1:00 am to 3:00 am)
to transfer the day's information to the source DB so we have to run the
transfers simultaneously.  We have 3 main tables that were in constant 
contention when ALG was enabled.  Disabling ALG seemed to eliviate the locking
problems.

  I know we've also done some other "transfer scheduling" work so enabling
ALG may be an option again.  I'll have to check.

  And yes we are running two replication transfers.  One for moving the lookup
table data, the other for the production table data.  The lookup table xfer
runs once a day, while the other xfer runs twice a day.  This seems to solve
the purge enqueue quota problem (most of the time).  

I did get the error while doing an interactive query on the RDB$CHANGES table
yesterday.  I had to exit SQL and re-execute the query to retrieve the data.
I didn't look to see what the DB activity was.

  Thanks to all for your inputs and ideas.

Paul