Purpose:

This document is to clarify what and how the BEGIN_SQL statement is used in a universe.  It is important to note that the parameter is used for a specific purpose and can be very useful when  trying to track long running queries and reports that may be a performance drain on the database.  It can also be used to prevent dead locking or row locking for some databases.  These features are available for a unv universe with Universe Design Tool (UDT) and with a unx universe in Information Design Tool (IDT.)

Overview:

The BEGIN_SQL parameter was designed to provide a mechanism in the universe and connection to set a QUERY_BAND in Teradata and to prefix SQL statements for accounting, prioritization, and workload management in Oracle and DB2. Once set, the value of this parameter is run before the Web Intelligence SQL generation.  This includes the SQL for document generation and for List of Value (LOV) queries. The BEGIN_SQL statement was intended for a SET statement and it is not designed to support every type of SQL statement or query for every type of database.For Example it will not work with Sql Server.

How It Works

CONNECTINIT

It uses the connection to add the QUERY_BAND string.  When a universe connection is created the connectinit parameter is on the last page of the advanced tab. 

BEGIN_SQL

This is a universe parameter that generates a pre-SQL string.  The string is applied during the LOV report SQL generation and during the LOV sql generation.  It is a design constraint that it is not engaged in the Universe layer and is ignored by Desktop Intelligence.  This parameters is supported in Web Intelligence, LiveOffice, Crystal Reports for Enterprise, and QaaWS.

Check the Data Foundation parameters in IDT for this parameter.

SYNTAX

Example for Teradata:

BEGIN_SQL=SET QUERY_BAND='string' for transaction;

 (Note Teradata has specific syntax - see kbase Note 1764353 - Universe Designer LOV Errors with Query Banding Feature

SAMPLE SETTING:

Teradata Syntax example

SET QUERY_BAND = 'App=Businessobjects31; AppUserName = @variable("BOUSER") ; RptName = @Variable("DOCNAME") ; DataProName = @Variable("DPNAME") ; UnivName = @variable("UNVNAME");' for session;

Syntax for most databases is as follows: 

SET QUERY_BAND = 'ApplicationName=YourAppHere; ClientUser=@variable('BOUSER' (mailto:ClientUser=@variable('BOUSER') ) ; ’ FOR TRANSACTION;  (check that a space is after BOUSER and before and after the ';'.

Example of three name-value pairs:BEGIN_SQL=SET QUERY_BAND='UserID=Jones;JobID=980;AppID=TRM' for transaction;

You can also use the @Variable function as the value in the name-value pair, the returned value is enclosed in single quotes:

BEGIN_SQL=SET QUERY_BAND='USER'=@Variable('BOUSER'); Document='@Variable('DPNAME') for transaction;

Feature Points

1. We do not support Teradata Query_Banding.  However we offer a mechanism to pass some settings to Teradata.  We do not guarantee that all Query_Banding settings will work.
2. Quotes.  The statement passed is evaluated with the @variable first.  So the @variable(‘BOUSER’) should be replaced with the username first.
3. It can be set for a transaction or a session.  A transaction is complete with the transaction.  The session will remain in use and an entry is put in the Teradata session table.  It will remain active until the query band is replaced by another.  Best Practice recommendation is for Transaction.
4. As it mentions above.  It will also apply to the query generated for LOV.
5. Since 4.x FP 03 IDT document Query_Band can be used in Crystal Reports for Enterprise.

Best practices

It is recommended to use for transaction, and not for session as the sentence will be sent to the database at each query execution.

Limitations

1. It is supported in Web Intelligence, LiveOffice, and QaaWS. But it is ignored by Desktop Intelligence and Crystal Reports.

         2. Only the listed Report variables can be used with @Variable:

DOCNAME - the name of the document

DPNAME - the name of the Data Provider

DPTYPE - the type of the Data Provider

UNVNAME - the name of the universe• UNVID - the ID of the universe used

         3. This feature Cannot be used to call a stored procedure and it does not support every type of SQL Statement

         4. Multisource Universes only use the Connectinit method.  Other methods are not supported.

         5. Using the @Variable('BOUSER') in the connection Username parameter is not supported. 

See KBA: 2209200 - Unable to use @VARIABLE('BOUSER') as the username for a relational connection in IDT

         6.  Connectinit is Not Supported for JDBC Connections in BI Version 4.2

Known Issues

Teradata Specific:

• Teradata Syntax
• Note 1764353 - Universe Designer LOV Errors with Query Banding Feature

Other Known Issues

• 1773696 - BI4.0: Invalid SET statement in the triggered action (Teradata
• 1794733 - Can't Use @VARIABLE(''BOUSER'') with Teradata in IDT in BI 4.0
• 1737293 - @VARIABLE('DBUSER') Not Passing
• 2666735 - When running the SQL generated by Information Design Tool for query that contains filter on a "timestamp with local timezone" Oracle datatype, the query is not using the appropriate Execution Plan defined on Oracle level and generates a huge cardinality
• 52501075 - List Of Values is not updated in WebIntelligence query when switching the current schema is based on the universe parameter BEGIN_SQL
• 2697112 - Invalid SET statement in the triggered action error message Teradata

Troubleshooting Tips

Capture an end to end trace.  Check the trace for the actual value generated and sent to the Database.  (see below link)

Have the DBA capture Teradata audit of the SQL log.  SQL is logged in a Teradata table of all Sql processed by the database.

If Begin_SQL doesn't work, try the connectinit as a work around.

Check the AutoCommit parameter in the Teradata.sbo file.  This file is on each client and server.  So Web Intelligence uses the server file whereas the universe and client tools use the local client copy. See kb 1282970 Below for instructions.

Related Content:

1861180 - Collecting an end to end trace in BI Platform 4.x - customer instructions and best practice [Video]

How To Set Autocommit in Teradata

For Sql Server Deadlocking or Transaction Isolation Levels see KBA 1344187 - Webi Reports Lock On Database Tables

Related Documents: