All Forums Third Party Software
Tnewbee 215 posts Joined 05/10
15 Apr 2014
Bi tool cannot read from different databases in teradata?

We have some BI queries which run on Teradata. Now these queries create a temp table in the base tables database and then read from another transaction table again in the base tables daabase. If any other job is updating this transaction table then we face a deadlock because of the read lock already on the table. 
Now Can the BI queries not create temp table in one database and read from view databse, instead of base dB? The BI folks claim that they cannot pass 2 database names, and the batch id just uses whatever is the default database. They also cannot create volatile or GTT. 
Any idea on how to handle this? 

ulrich 816 posts Joined 09/09
16 Apr 2014

Hm, not so easy to answer. I am not sure that I understand how the dead lock occurs.
Questions: is the read lock nessesary for you? Would lock row for access be OK?
I would strongly avoid the creation of tmp tables for BI queries. Are these create per session? VT & GTT are usually better choice here. Why can't you use these?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Adeel Chaudhry 773 posts Joined 04/08
16 Apr 2014

Its more related to BI tool, but as a usual practice you can expose tables to BI with a view layer .... which resides in a single DB hence they can access it.
And you can define those views using LOCKING ROW FOR ACCESS .... should resolve the issue.

-- If you are stuck at something .... consider it an opportunity to think anew.

Tnewbee 215 posts Joined 05/10
17 Apr 2014

So do you mean create a view layer in base tables table database, thought we have a separate view database ?  Is this the limitation that of BI tool that they cannot create a temp table in one database and then read from another database in the same connection pool? 

Adeel Chaudhry 773 posts Joined 04/08
21 Apr 2014

Its you choice to have views inn same or different DBs .... what i wrote above is that usually its 1 DB for tables .... and another seperate 1 for all the views.
Regarding BI limitation, you should check with that BI tool's experts or related forums.

-- If you are stuck at something .... consider it an opportunity to think anew.

VandeBergB 182 posts Joined 09/06
21 Apr 2014

Don't commingle views and tables in the base table db.  If you do that you'll create an administrative mess for the dba's.
Create a separate BI db that contains nothing but views, one to one for the base table accesses and business/semantic views for the view objects.  if you need to duplicate business logic, simply but the fully defined equivalent of a "select * from" the business view in the BI db.  Don't recreate any view logic, just alias the bv's in the BI db.
Give the BI db enough space to build the required temp space and  point everything at the BI db.

Some drink from the fountain of knowledge, others just gargle.

You must sign in to leave a comment.