SDM how to unlock tables running BFs on SqlServer with Hint

  • KM03807631
  • 27-Apr-2021
  • 27-Apr-2021

Summary

Documentation is not saying exactly how to use a hint in SQL Server configure sql_tuning.properties file and requisits.

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.