topic Re: RDB introduction in Operating System - OpenVMS

RDB introduction

Wim Van den Wyngaert — Fri, 12 Feb 2010 11:57:55 GMT

I have knowledge of Oracle and Sybase.

Is there an introduction manual/text that explains RDB compared to Oracle or Sybase ?

I'm thinking of space management, transaction log, locking strategy and escalation, how read concistency is achieved, db (un)loaders, etc.

Wim

Re: RDB introduction

Ian Miller. — Fri, 12 Feb 2010 12:30:49 GMT

Try at www.jcc.com
they have a email list about RDB

http://www.jcc.com/listserver.htm

and provide training on RDB

there are documents available at
http://www.oracle.com/rdb/index.html

Re: RDB introduction

Richard J Maher — Fri, 12 Feb 2010 12:44:36 GMT

Hi Wim,

Probably best just to ask specific questions after explaining where you're coming from. (i.e. Have you just picked up support of an Rdb system? ODBC? OCI, VMS 3GLs? and so on)

As far as space management goes Rdb calls Table Spaces "Storage Areas" and although Rdb supports multi-schema the default is not.

The redo log is called an AIJ (after Image Journal) Before-Images for each transaction are written to a .RUJ file that handles rollback.

Read-consistency is normally achieved through snapshot files 1:Storage-Area and are accessed by specifying a "set trans READ ONLY" transaction.

Locking is complex and very smart with promotion opportunities and largely dependant on optimizer strategy and RESERVING clauses.

RMU is the utility that you need to love for monitoring *backup* and (un)loading.

Full ACID 2PC is available with XA (via DECdtm or Oracle on it's own depending on update order) or TIP

OCI is available as well as ODBC

But as I said bug-subject so ask specifics or get the very good documentaion that regretably is not on-line in HTML format.

If you're using 3GLs then be prepared for the absolute wonder (and completely neglected selling point) of Rdb running in EXEC mode withing the context of a user process!

Cheers Richard Maher

Re: RDB introduction

Wim Van den Wyngaert — Fri, 12 Feb 2010 12:56:37 GMT

Richard,

I'm looking for a job and while there are not so many VMS clients left, I encountered at least 2 that are still using RDB. Good for me : not a lot of sysadmins either.

You can always mail to (first letter of each word of my name as in "author") @telenet.be if you have something that may not be published on the net.

So, when you do a select, is there a locking or a "read-consistency re-construction without lock" ? Or both (as in HP allbase).

Wim

Re: RDB introduction

Jean-François Piéronne — Fri, 12 Feb 2010 13:40:41 GMT

Wim,

How Rdb execute a select depend on the transaction type: read only or a read write.

If you use a read write transaction Rdb lock record to maintains consistency (duration of the lock depend on the isolation level).
If you are in a read only transaction Rdb don't lock any record, it uses snapshot to retrieve the correct record version.
Rdb default isolation level is serializable, very few SGBD do this.

JFP

Re: RDB introduction

labadie_1 — Fri, 12 Feb 2010 13:53:01 GMT

Wim

Rdb has a splendid tool
$ rmu/sh stat 'base'
Check the doc, see all the (numerous) options.

When a customer has bought Rdb, he can use Hot Standby at no additional cost, that is replicate the database on a remote node.

If you wish to be notified when something happens on your Rdb database, see this post
http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=1254399

Re: RDB introduction

Wim Van den Wyngaert — Fri, 12 Feb 2010 16:52:01 GMT

JFP: it's even better. You can configure the behavior per user or on db level. ALTER USER SNAPSHOT allows to enable it always, sometimes or not at all (thus causing locks during selects).

I wonder who was first with the snapshot mechanisme: Oracle or DEC.

And do you also have "snapshot too old" messages in RDB ?

Wim

Re: RDB introduction

Andy Bustamante — Fri, 12 Feb 2010 22:10:19 GMT

See http://www.amazon.com/Rdb-Third-Comprehensive-Lilian-Hobbs/dp/1555581862/ref=sr_1_3?ie=UTF8&s=books&qid=1266012372&sr=8-3 For a third party book.

The Oracle web site has extensive documentaion at http://www.oracle.com/technology/products/rdb/rdb_doc_index.html

Re: RDB introduction

Richard J Maher — Fri, 12 Feb 2010 23:37:12 GMT

Hi Wim,

I've never seen SNAPSHOTTOOOLD from Rdb (you can do a "$search sys$help:r*.doc blah" to check) but doo be aware that .SNP files can just grow and grow if you have long-running read-only transactions and many updates from other read-write transactions. This is one of the reasons you can specify "prestarted transactions are off" to disable long-running (often don't do work or commit for a long time) server behaviour. (Note: That Exclusive table locks do not write to snapshot files and will hang RO transactions and then report an error.)

Rdb is a brilliant product but then Orrible Oracle has a few features (like linktables) that are missing.

Cheers Richard Maher

PS. RMU is the tool you want to use but I believe Rdb databases are now visible to OEM.

Re: RDB introduction

Wim Van den Wyngaert — Sat, 13 Feb 2010 03:50:57 GMT

Richard,

What do you mean with linktables (in Oracle syntax) ?

So the snapsshot too old can't happen because the snapshot will grow until ...
but then what happens if you run out of snapshot space ?

Wim

Re: RDB introduction

Bob Blunt — Sat, 13 Feb 2010 04:56:03 GMT

Wim, I'm not a RDB expert by any means, I've only had to touch RDB as the supporting database for tools like DECplan, EDCS II and some others during a previous contract. I found it easy to learn the basics but it can get very complex in a big hurry.

There's a database-centric set of pages at http://dba.openvms.org, that group might have some other more specific suggestions and there's a RDB DBA seminar scheduled for later this month listed at the main http://www.openvms.org/ pages. Of course you might also check in the RDB-focused information at the Oracle website.

bob

Re: RDB introduction

Richard J Maher — Sat, 13 Feb 2010 05:03:36 GMT

Hi Wim,

> What do you mean with linktables
> (in Oracle syntax) ?

I mean referencing tables in another database through a link table in the local database. The syntax is something linke (I created one yesterday so I should remember better)

create database link myLink connect to me identify mypassword tnsnamesref;

> So the snapsshot too old can't happen
> because the snapshot will grow until ...
> but then what happens if you run out of
> snapshot space ?

Good question. I don't know the answer as I've never seen it happen (even in dev/test) Presumable when the disk runs out of space there will be a cosi-f-kaboom error and something will happen. The philiosophy where I've been is that table-spaces/storage-areas are all able to auto extend (within the limits/percentages specified) An big-uk-bank-oracle site that I was at for some years would pride themselves on having "managed databases" where aouto-extend was forbidden. When I asked what the support DBAs did when they got called at 2:00am 'cos a table space had reached its growth limit, I was told "Well we extend it of course, Duh!" :-)

Set up alarms, device monitoring, OEM, yadda, yadda, yadda.

Now if the AIJ blows up that's a different story! (And a "simple" metadata change resulted in just such a device-full scenario) Which is why they have circular AIJs and emergency overflow bits.

> Wim

Cheers Richard Maher

Re: RDB introduction

Jean-François Piéronne — Sat, 13 Feb 2010 06:43:13 GMT

Wim,

correct, you can disabled snapshot mechanism or use the enabled deferred clause.

But if you disabled snapshot your read write transaction don't give you the same level of isolation and generate a large locking activity.
It's most of the time not a good idea, except if all the application are designed for such behavior. It is generally better to us a exclusive write reserving clause.
But I have used a few times (very few) snapshot enabled deferred on some databases.

Oracle snapshot and Rdb snapshot don't work the same way.
Rdb keep in the snapshot file all versions of any record which can be retrieve by the oldest active transaction. But snapshot file automatically grow, limit is just your disk size...
So except if some exclusive write transaction has modified the table, a read only transaction can't failed (with snap enabled).

Rdb has snapshot since version 1.

JF

Re: RDB introduction

Wim Van den Wyngaert — Sat, 13 Feb 2010 09:25:27 GMT

JPF : not all tables are equal.

An application has critical tables and others that are not. When you do a loading/mass updating at night of a table that's not in use at night, it would be better to take locks instead of using snapshots. But I would implement it at table level instead of user/db. And may be alter the setting in certain jobs.

Wim

Re: RDB introduction

Wim Van den Wyngaert — Sat, 13 Feb 2010 09:27:02 GMT

Sorry, JFP not JPF (have known a Jean Pierre we caleld JP).

Re: RDB introduction

Wim Van den Wyngaert — Sat, 13 Feb 2010 09:32:22 GMT

Richard,

In Sybase you have the system of proxies. WV1 coming from DB A has rights as WV2 in DB B. No need to hardcode your password.

I'll do some reading in
http://download.oracle.com/otn_hosted_doc/rdb/pdf/gdbdd.pdf
where many of the things are explained.

Wim

Re: RDB introduction

labadie_1 — Mon, 15 Feb 2010 09:31:10 GMT

Wim

Book the 3 and 4 of May, and go to
European Oracle Rdb and Oracle Database on OpenVMS Technical Forums, they will be in Bruxelles on those days.

http://www.openvms.org/stories.php?story=10/02/13/8594578

Re: RDB introduction

Wim Van den Wyngaert — Mon, 15 Feb 2010 09:47:19 GMT

Yes but I'll have to know the basics before I go to that. And have free time (when employed).

Wim

Re: RDB introduction

Jean-François Piéronne — Mon, 15 Feb 2010 10:24:42 GMT

Wim,

just to clarify, take look and use snap are not mutually exclusive.
For the moment forgot the disable snap feature and the special batch update transaction.
You take locks on a table/record when you are in a read-write transaction. But the level of your lock depend on you reserving clause (and on the strategy choose by Rdb).
For a table you can specify 2 words, the first one is how you accept others access the table concurrently with you, the second how you access the table:
shared/protected/exclusive read/write

exclusive mean that you want to be alone on the table, in this case and only in this case you don't write to snap.
protected mean you accept reader
shared you accept reader/writer

Rdb escalate an shared lock on a table into a protected if the strategy choose is sequential.

You also specified the isolation level needed:
read committed/repeatable read/serializable
Rdb don't support dirty read mode.

There are, also, others options but outside the scope of this thread.

On Rdb cost of snapshot is "paid" by writer for an eventual reader.

Each time you update a record (table record, index record, system record,...) Rdb use a snap page (if necessary more than one) linked to the live page to write the old version of the record. The documentation contains a full description of the structure of a data/snap page, you can also use the rmu/dump command to view the content of a data/snap page, very useful.

Briefly, a read only transaction read the data page check the record version using the TSN (transaction sequence number), if this version is not the correct one, start to follow the chain of snap page/record.

If you have disabled snap, you have something very similar to "shared read isolation level read committed".

JFP

Re: RDB introduction

Richard J Maher — Mon, 15 Feb 2010 23:05:32 GMT

> in this case and only in this case you
> don't write to snap.

Well there are also BATCH_UPDATE transactions but they're hardly ever used. (Don't write to the RUJ either and can't be rolled back) You can also just "defer" snapshots if there are no writers active on that storage area.

I see one of the Tech Forums this year is in Munich and IIRC the bier gartens start opening in May so I know where I'd rather be!

On the Orrible Oracle front, let me also tip my lid to CONNECT BY PRIOR. Traversing our Org_Unit hierarchy brilliantly at the moment!

I'm sure if Rdb was actively being developed then we'd have such functionality too let alone SQL> SET SESSION AUTHORIZATION USING (PERSONA :ws_integer);

Cheers Richard Maher

PS. Just where are these DBA jobs and how much are they paying :-)