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?
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?
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.
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?
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.
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.