Conference orarep::nomahs::rdb_60

    There is no interface to Rdb that will return the TSN.

~    Reason is, a report is not including particular timestamped records
~    occasionally, and I can prove why using TSNs.  But I need to be able
~    to display the TSN of one of the transactions in order to do so.

Why not tell us more about the problem?  Maybe we can suggest an alternative
method.

Ian

re:  <<< Note 5001.2 by NOVA::SMITHI "Don't understate or underestimate Rdb!" >>>

>~    Reason is, a report is not including particular timestamped records
>~    occasionally, and I can prove why using TSNs.  But I need to be able
>~    to display the TSN of one of the transactions in order to do so.
>
>Why not tell us more about the problem?  Maybe we can suggest an alternative
>method.

OK, here's the process and problem:

    - procedure starts a read only transaction in interactive SQL

    - report data is accessed via a single SQL statement consisting of several 
      unioned selects; among the records accessed are those containing a
      date/time_record_loaded column "DT_LOAD"  which was assigned using 
      default CURRENT_TIMESTAMP when inserted by a loader.

    - In the first part of the union, a select gets CURRENT_TIMESTAMP for the	
      report header "date and time of report" display.

    - when verifying the report data against a raw data dump, it *appears*
      that the report misses some row(s) that it should have caught because
      they have DT_LOAD values which are less than CURRENT_TIMESTAMP on the
      report header date/time by 'n' hundredths of a second.

      One example is a report with header date/time of "27-JAN-1997 23:52:24.71"
      Raw data extract shows a record with DT_LOAD if  "27-JAN-1997 23:52:24.49"
      that was *not* included in the report.
      
      Another instance shows a report header of "27-JAN-1997 23:22:26.12" and
      missing row from raw data w/ datetime of  "27-JAN-1997 23:22:26.10".


    So while the reports header shows report-run-time N, it is possible to miss
    some records with a slightly less timestamp, N-H, where H is some 
    hundredths of a second.  At first glance, it looks like records were
    missing from the query output.

    I figure that the reports read only SQL starts before the transactions that
    write the "missing" rows above, and that therefore, the TSNs of the new
    records will be greater than the viewable cutoff TSN for the snapper and
    thus, cannot be read by the snapper.   But I'd like to prove it.  
    I can get the TSN of the "missing" rows from a page dump, but I don't 
    know the TSN of the report run, thus my note.

    Even though the report SQL query is running with an outline, it still 
    takes about 25 seconds to start returning data, so it's very reasonable
    for the loader to be adding records sometime after it starts.

    I think a key question is when does CURRENT_TIMESTAMP get assigned a value
    in a select ?   Particularly in this select ?  When the output stream 
    starts, or when the first leg of the union runs ?  

    ALso, non- readonly transactions are not an option.

    /Ken

    Is it useful for you to do an RMU/DUMP/USER on the db while the report
    process is running?  That way you will get the TSNs of all the users at
    that point in time.

I've got two ideas for you.  First, use RMU/DUMP/USER to get a snapshot
of the users in a database.  this output includes the TSN for each user.
Second, create a local variable to store the time tamp before you
start the query and use this timestamp in the query.  Then you'll be
certain of what time you were using.

something along the lines of

	SQL> att 'fi mf_personnel';
	SQL> declare :dt date vms;
	SQL> begin set :dt = current_timestamp; end;
	SQL> select :dt, count (*)  from EMPLOYEES where BIRTHDAY < :dt;
	 DT
	  6-FEB-1997 11:03:51.22           100
	1 row selected
	SQL> exit;

First of all I don't see anything wrong with this...

If a transaction is running *BUT* has not yet committed then any rows it has
inserted will not be visible to the READ ONLY transaction.  Therefore, it is
possible that there are rows with timestamps which preceed the start of the
report that don't get read.  They simply were not committed.

One thing you can do is force all writes from the loader to commit before
producing the report (rather like a quiet point) and then allowing the loader
to resume.  In this way the READ ONLY transaction will read all the commited
transactions.  I know ways to do this if you are interested (it doesn't work
too well if you have transaction timeout enabled, or if you are concerned
about stall times).

~	SQL> att 'fi mf_personnel';
~	SQL> declare :dt date vms;
~	SQL> begin set :dt = current_timestamp; end;
~	SQL> select :dt, count (*)  from EMPLOYEES where BIRTHDAY < :dt;
~	 DT
~	  6-FEB-1997 11:03:51.22           100
~	1 row selected
~	SQL> exit;

Well you *could* do that.  However, CURRENT_TIMESTAMP is fetched once per
statement, and is fetched before the query starts.  So the two timestamps in
this example will be pretty much the same.

Ian

>Well you *could* do that.  However, CURRENT_TIMESTAMP is fetched once per
>statement, and is fetched before the query starts.  So the two timestamps in
>this example will be pretty much the same.

	I concur.  This would be a way to demonstrate for sure what
was going on in terms of timestamps.

re: <<< Note 5001.4 by HOTRDB::PMEAD "Paul, [email protected], 719-577-8032" >>>

>    Is it useful for you to do an RMU/DUMP/USER on the db while the report
>    process is running?  That way you will get the TSNs of all the users at
>    that point in time.

    Well, the data that we'd want to get is in there.  As a last alternative
    we've discussed spawning a rmu/dump/users after the transaction starts,
    then parsing the output to get the TSN of the transaction, but I was 
    hoping to get at it in a less roundabout way.  

    Figured there'd be a debug flag for this.

    /Ken

> First of all I don't see anything wrong with this...

Same here, just trying to prove the behavior, and the only path I see is
via TSN's. 

>One thing you can do is force all writes from the loader to commit before
>producing the report (rather like a quiet point) and then allowing the loader
>to resume.  In this way the READ ONLY transaction will read all the commited
>transactions.  I know ways to do this if you are interested (it doesn't work
>too well if you have transaction timeout enabled, or if you are concerned
>about stall times).

We have no transaction timeout, and stalls *may* be tolerable.  What's the
technique ?  Almost sounds like deferred snaps would be an alternative.

/Ken

~~      <<< Note 5001.9 by ORAREP::AWECIM::HANNAN "Beyond description..." >>>

~~We have no transaction timeout, and stalls *may* be tolerable.  What's the
~~technique ?  Almost sounds like deferred snaps would be an alternative.

If the stalls are tolerable, a technique similar to what we use might work 
:)  We use the QUIET lock to control this behavior.  Before a loader starts 
a transaction, they have to check the mode of some lock resource and see if 
a transaction can be started.  The reporter asks for the same lock, and 
when they obtain it, it indicates that there are no active loaders.  You 
shouldn't use Rdb's lock, but you can certainly use the technique.

attach the database (ATTACH #1)

Now before the READ ONLY transaction starts

    - attach to the database (ATTACH #2)

    - start a READ WRITE transaction and reserve the table for PROTECTED READ
    with WAIT

	(this will stall until all current writers are committed)

    - now in the original attach (ATTAH #1) start a READ ONLY transaction

	(no writers can be started yet, they will be queued waiting for
	the PROTECTED READ transaction)

    - once the READ ONLY is started, perform a ROLLBACK and DISCONNECT from
    ATTACH #2

continue as before...

If transactions against the table are startewd with NOWAIT, or timeout then it
will not be completely transparent.

Ian

re: -1

Seems like it would be difficult to syncronize the rollback of the PR
transaction in attach #2 with the start of the RO transaction in attach #1.
These would have to be different processes that communicate somehow. 

In any case, I'm not looking to improve or change the way the report is
currently being generated, just trying to fully explain the "missing" rows
issue.

/Ken

~Seems like it would be difficult to syncronize the rollback of the PR
~transaction in attach #2 with the start of the RO transaction in attach #1.
~These would have to be different processes that communicate somehow. 

No this is very easy and can be done in one process... (my SQL isn't complete
but I hope you get the idea)

CONNECT AS 'A' ... ALIAS DB1
CONNECT AS 'B' ... ALIAS DB2
SET TRANSACTION READ WRITE WAIT RESERVING T FOR PROTECTED READ;
CONNECT TO 'A';
SET TRANSACTION READ ONLY;
CONNECT TO 'B';
ROLLBACK;
DISCONNECT 'B';
CONNECT TO 'A';

...etc...

I know one application which uses this, and by the way I didn't invent this
construct.

Ian

I see what you were saying...  I thought you meant sync'ing the rollback 
of the PR transaction with the start of the RO *select* execution...

I really don't see that this technique will buy me much though. It's still
possible for a write to get in there and commit before the select produces
the output for timestamp.

/Ken

~I really don't see that this technique will buy me much though. It's still
~possible for a write to get in there and commit before the select produces
~the output for timestamp.

No it isn't.  No writer can get in until you ROLLBACK the PROTECTED READ
transaction.  So do what you want before rolling back the READ WRITE
transaction...

Ian