Archives of the TeradataForum

Message Posted: Wed, 18 Dec 2013 @ 21:05:56 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Would I be right in saying that with QUEUE Tables, you can't SELECT AND CONSUME with a WHERE clause ?

I was kind of expecting little restriction in this area, otherwise I'd have to have a separate Queue Table for each Target Table - not a problem as such, I was just expecting a bit more flexibility.

Also, are there any 'gotchas' that you guys know of before I recommend we use them for a mixed workload application ?

Ah, in case you're wondering what mixed workload application we're thinking of applying it to, I'm proposing its use for an application where we're providing :

i) Select with Locking Row Access on a Summary Table

ii) An ETL Batch Bteq job, which regularly write rows to that Table, every two hours

iii) In frequent online updates of data onto that Summary Table from Users

My thinking is that, if I allowed concurrent Update access to the same Table from both Online and Batch, there would be TABLE level locking contention.

What I see as a High Level Design is the writing of the online Inserts and Updates to a QUEUE Table, followed by an Asynchronous Select And Consume against that Queue Table, writing to the Summary Table.

At least this way, Users can write the data, and this can then be written to the Summary Table asap, without directly holding up the User.

Good idea ? Bad idea ?

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

	<Prev	Next>		<<First	<Prev	Next>	Last>>