SDM Implementation Session modification to SQL Server

Document ID:KM03807632
Creation Date:27-Apr-2021
Modified Date:27-Apr-2021
- Micro Focus Products:
  structured data manager

Summary

This is not in documentation and guide or comment inside sql_tuning file aren't enough.

Question

 Why this topic needs to be documented and exposed?

On documentation there is nothing about this.

 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 read even there are uncommitted transactions.
o He needs BF send alter session to SQL Server and make proper action allowing the BF do not block the read tables in a select statement.

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>.*.*.*.*.SESSION_VAR1=TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
o Run the BF under environment you specified on before clause.
o You will see log file saying:
Extract data from database to backend directly thread 0 : root : () Hints map table :{*={*={*={*={SESSION_VAR1=TRANSACTION ISOLATION LEVEL READ UNCOMMITTED}}}}}

o You will see SqlServer Trace file in xml saying:
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</Column>
      <Column id="10" name="ApplicationName">Microsoft JDBC Driver for SQL Server</Column>
      <Column id="11" name="LoginName">obt_if</Column>
      <Column id="12" name="SPID">