Summary
Question
SDM how to unlock tables running BFs on SqlServer with Hint
Why this topic needs to be documented and exposed?
On documentation this matter is not clear and no easy way to understand it.
What is the scope of this knowledge?
This applies only if customer is using SDM 7.6x version and up.
What customer wants to do?
o Using BF in D2F he wants to work in tables with transactions but not locking the table to avoid db table blocks.
Answer
Pre-Requisites:
o You must to create a Environment Intrusive, with D2F feature just no specifying Database to Database capacity in WC/Environment creation.
o This is instead of create a D2F as usual environment creation.
How to Fix:
o You have to modify sql_tuning.properties file located in <obt_home>/config.
o Use session hint add the command like you see below:
<your environment>.*.*.*.*.SUFFIX_HINT=WITH (NOLOCK
o Run the BF under environment you specified on before clause.
o You will see log file saying, example from SDM demo schema Demarc tables:
DEBUG : Extract data from database to backend directly thread 0 : com.hp.lw2.sql : () Statement for ORDER_LINE:
SELECT "ORDER_LINE"."ORDERLINEID" "ORDERLINEID",
"ORDER_LINE"."ORDERID" "ORDERID",
"ORDER_LINE"."PRODUCTID" "PRODUCTID",
"ORDER_LINE"."QUANTITY" "QUANTITY",
"ORDER_LINE"."PRICE" "PRICE",
"ORDER_LINE"."DISCOUNT" "DISCOUNT",
"ORDER_LINE"."NOTE" "NOTE",
"ORDER_LINE"."ORDER_YEAR" "ORDER_YEAR"
FROM "Demarc"."dbo"."ORDER_LINE" "ORDER_LINE" WITH (NOLOCK)
o With the facts you are now sure it works.