how to implement multiple sequences on a single table 2

[misterstick]

i'd like to be able to have an arbitrary number of
sequences against a single table.

i realise that general algorithm questions like this aren't
really what this forum is about, but i thought someone
might have come across this problem before, or could find
or have found a solution somewhere.

some background.

we use a batching algorithm to import transactions.

users create a batch of posting records, update the batch
and the process validates and updates the data from the
postings (external to the system) into transactions
(internal) adding it into all necessary summary tables at
the same time.

each batch of postings is described in a header record
called, surprisingly BATCH_HEAD. the BATCH_HEAD records
have an arbitrary number as their unique identifier
BATCH_NO. for auditing purposes these BATCH_NOs must be
sequential, there must be no gaps.

in days gone by when we had direct files (CISAM, etc) there
was no problem with this, just store the last number in a
single record key file, and lock and update it as necessary.

in oracle, we've has to use sequences because locking
doesn't work. this works well enough: the write of a
BATCH_HEAD record is done close enough to the number
getting pulled off the sequence that missing numbers are so
far not an issue.

time passes, specs change.

the system has been updated so that it is multi-company.
the number of companies is arbitrary and user-defined.

the BATCH_HEAD table is now keyed by company and batch_no.
the batch numbers must still be sequential and gapless, but
now within company: company 1 gets its own set of batches
within the table numbered 1 to m, and if company 2 exists
it does too, numbered 1 to n.

in CISAM the logic doesn't change much, you just add a
record to the key table for each company and retrieve
records by company. the lock, read, update, unlock
processing works just as well.

in oracle, however, things aren't so simple. i can think of
several ways to do this. each, however, requires a giant
mess of PL/SQL.

although excited by the prospect of writing densely
impenetrable logic i'd really rather try and keep things as
simple as possible.

anybody have any ideas?

many thanks,


mr s. <

 

[misterstick]

If X-SAM is so good, and they've only had it for 20 years, why change?

there was ksam, and mpe died. most recently there was cisam, but the rs/6000s we were using have come to the end of their support contracts. moving to windows is a pretty popular thing to do, but dsam hasn't been any where near good enough to use in a large system.

also, as you don't need me to tell you, relational is just better.

and they suggest SQL Server instead

now that would be a case of unwelcome interference. but, luckily, even the dumbest user can grasp that changing back-ends is not something undertaken lightly.

i'm glad that you brought this up, though, since any move to a non-sequence version of this code would be a step forward if we did get a prospect who was sold on sql server.

as i said, platform neutrality is my goal.


mr s. <

 

[ChrisHunt]

Just suppose, for a moment, that you're an ordinary design...

Before anybody queries this, I meant ordinary person - heaven knows where that "design" came from.

Must learn to re-read posts before posting them...

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Thargy]

Chris,

that's a fair point about the numbers of things. However, this means that the punters are being shown the primary key values of something from the database, i.e. monotonically increasing contiguous (or otherwise) sequential integers. Since sequence generated numbers must be meaningless (becaue there's no way they can be used otherwise) why are people being shown meaningless numbers?

If you really have to number things, for showing to end-users, just select all successful transactions from my_table where punter_interested = true, and select rownum at the same time. That will definitely give you a count starting at 1, going up without breaks, and will work every time. The punters will be none the wiser and hey-ho, everybody's happy.

Would this be a suitable way of delivering what's wanted?

Regards

Tharg

Grinding away at things Oracular

 

[ChrisHunt]

Sometimes, Tharg, numbers have to be visible - invoice numbers, order numbers, ticket numbers. In such cases, even if the computer system will work just as well with non-contiguous numbers, it can be worth a little work to suppress possible gaps.

You're right that the vast majority of primary key values will (or should) never be seen by end-users, or not paid a lot of attention if they are. A regular sequence number is fine for these cases. But there are a small minority of cases where gap suppression can be worthwhile.

Oh, and I've just thought of another reason why users sometimes don't like gaps: workload reporting. Suppose you want to know how many records you created today? Easy: it's (primary key of last record of the day)-(primary key of first record of the day)+1. Of course, the proper way to do it is to query the database to count the records. But often the end-user isn't able to query the database, or to persuade the powers-that-be to write a report to do it. Sure, it sucks, but that's the real world for you!

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Thargy]

Chris,

I agree - and I don't . This whole thing (IMHO) stems from bad design up front. If gap-less audit trails and workload monitoring are requirements, then they should be designed in up front. Fortunately, I believe that both these birds can be killed with one stone.

Workload monitoring can not be achieved by monitoring the contents of one table (unless the application is trivially simple). A typical business transaction might make entries or alter data in numerous tables, as a result of just 1 business action. What the business wants is business audit, not a list of entries in a table. What the auditor wants is business audit, not a not a list of entries in a table. If the system provides business audit, including failed actions, business actions where mistakes were made and abandoned, and any and all other types of failure, then this list will have no gaps in the numbering.

Once the auditors get a few hundred records showing that a task was abandoned by the user (e.g. they shut down their web browser) they'll soon ask for just those actions that went through, and be glad of the gaps, which they will then know contain irrelevant dross. It will suffice to demonstrate that your audit is complete, and all will be satisfied. Workload can also be meaningfully monitored by business transactions.

How about that then?

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

You probably know already that I agree, John (Tharg).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[misterstick]

as far as bad design goes, this system was first spec'ed out in about 1978. it should be overhauled but them as pays the wages don't work like that. it ain't broke, after all.

the only problem with it is the short term design decisions from hardware which were based mostly on time or rather cost.

is there anyone who really plans their code to be entirely platform and langauge neutral?

what percentage increase in costs would your bosses allow now to defray possible migration costs later?

we're working with zero.


mr s. <

 

[misterstick]

ok, so i used "defray" to mean "reduce" when it actually just means "pay".

i apologise.

i r idiot.



mr s. <

 

[Thargy]

stick,

a 1978 vintage specimen, I believe that was a good year for client/server apps, but I was still at school then, so I can't be sure.

As to the platform neutrality thing, well, the illustrious Mr Kyte has inserted numerous nails in its coffin. Platform neutrality is an unobtainable myth, and those who vainly seek it must expect to expend loads of dosh.

Do your customers really have that much spare cash? In my admittedly limited experience, the punters are usually tighter than a duck's rear end. The truly informed (or just tight) customers will rightly refuse to pay so much as a groat for platform independence. I can't help but agree with them on this one. The only question in my mind is "are they doing this for sound technical reasons or just plain lucky?"

Regards

T

Grinding away at things Oracular

 

[jimirvine]

Platform neutrality is an unobtainable myth, and those who vainly seek it must expect to expend loads of dosh.

Hear! Hear!

 

[misterstick]

hmm.

[ol]
[li]powerhouse is platform independent and cross database[/li]
[li]oracle is platform independent[/li]
[/ol]

and i would have a problem writing a mostly platform independent app using the first to point at the second because...

mr s. <

 

[Thargy]

Stick,

I was meaning an application which is database platform independent, not Oracle's OS independence.

Is powerhouse a front-end dev tool, like say powerbuilder?

I know that there are such tools, and that they can query any mainstream db with the correct plug-in, and/or version, but that's not what I was alluding to.

I meant those that think that you can have the same front end, middle n tiers, and then swap out the db without making a shred of difference.

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

I meant those that think that you can have the same front end, middle n tiers, and then swap out the db without making a shred of difference.

Actually, I've seen a company whose development platform achieves just that: TenFold Corporation (They have just been acquired by a Texas firm.)

I've actually seen real, live development occur on their platform, first using MySQL, then next the developer specified that he wanted the application ported to SQL Server and to Oracle. The front-end application looked no different whether the back end was on MySQL, SQL Server, or Oracle. (They also port to DB2 and EnterpriseDB.)

The demonstration that I saw also included translation of the front-end rendering from English, into Spanish, French, and Japanese!

It was pretty amazing!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Dave,

ten fold really do have their act together then.

How do they manage the situation where Oracle's read consistent view gives different results (when two interleaved transactions are occurring) with those db's that don't give a read-consistent view?

Is there something in the application to guarantee consistency across all db engines, regardless of how they handle things differently internally? If there is, then ten fold have done a uniquely outstanding job (so far as I know), and must have employed gurus from each db technology to do so.

I am impressed if they've aced these issues.

T

Grinding away at things Oracular

 

[SantaMufasa]

To address the read-consistency issue you mention, TenFold's development engine employs the locking schemes for the specific platform that ensure the highest quality consistency for that platform.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Santa,

AFAIK ten fold are unique in having that level of insight, nous, and technical knowledge to appreciate the subtleties of each DBMS.

So few even realise that there is an issue, let alone handle it fully.

Hats off to ten fold.

Regards

T

Grinding away at things Oracular