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.)
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
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.
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.
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
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;
- 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.
- Quotes. The statement passed is evaluated with the @variable first. So the @variable(‘BOUSER’) should be replaced with the username first.
- 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.
- As it mentions above. It will also apply to the query generated for LOV.
- Since 4.x FP 03 IDT document Query_Band can be used in Crystal Reports for Enterprise.
It is recommended to use for transaction, and not for session as the sentence will be sent to the database at each query execution.
- 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.
6. Connectinit is Not Supported for JDBC Connections in BI Version 4.2
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
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.
For Sql Server Deadlocking or Transaction Isolation Levels see KBA 1344187 - Webi Reports Lock On Database Tables