Query Bands in the .Net Data Provider for Teradata 13.01.00.00

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

This blog discusses how Query Bands have been implemented in the .Net Data Provider for Teradata 13.01.00.00.

I have placed the Guidelines first because I know many of you are already familiar with Query Bands, and are primarily interested in the guidelines on using Query Bands with the .Net Data Provider for Teradata. If you would like further details refer to the other sections.

Here is a summary of each of the sections:

Guidelines/Tips

Recommendations when using Query Bands with the .Net Data Provider for Teradata

Query Band Overview

A brief overview of the purpose of Query Bands.

Connection Level

Discussion of Connection Level Query Bands.

Transaction Level

Discussion of Transaction Level Query Bands.

Trusted Sessions

Discussion of Trusted Session and how it is set up using Query Bands.

Connection Pooling and Query Bands

How Query Bands affect the provider's management of connection pools.

Guidelines/Tips

  1. When Query Bands are to be modified at the Connection or Transaction levels, the QueryBand property should be called. Any changes that are to be made should be performed on the TdQueryBand instance returned from the QueryBand property.
  2. The ChangeQueryBand() method must be called to apply the changes made in the TdQueryBand instance to the Connection (TdConnection) or Transaction (TdTransaction).
  3. Query Bands are supported by the Teradata Database 12.0 and later. If an application is connected to a Teradata Database earlier than 12.0, and the method ChangeQueryBand is called a TdException will get thrown.
  4. Use the properties TdQueryBand.ProxyUser and TdQueryBand.ProxyRole to set up a Trusted Session.
  5. Trusted Sessions are supported by the Teradata Database 13.0 and later. If an application is connected to a Teradata Database earlier than 13.0 and ProxyUser /ProxyRole have been set, a TdException will get thrown when the ChangeQueryBand method is called.
  6. A Proxy User must be granted CONNECT THROUGH privileges through a trusted user. A trusted user must be the name of a permanent user who is already defined in the Teradata Database, but cannot be user DBC. Refer to the Teradata Manual, SQL Data Control Language for more information.
  7. Do not call TdConnection.ChangeQueryBand when a transaction is open. When this method is called it will succeed. However, further commands will not execute successfully . A TdException will get thrown indicating that only an end transaction statement can follow a DDL. This occurs because the ChangeQueryBand() method executes the DDL statement "SET QUERY_BAND … FOR SESSION". The Teradata Database only allows an end transaction statement to be executed after it has processed a DDL statement.
  8. The Query Band connection string attribute is ignored when the provider associates a connection string to a connection pool.

Query Band Overview

Query Band support has been added to the .Net Data Provider For Teradata beginning with TDNETDP 13.01.00.00.

There are several reasons to use of Query Bands. A few of these reason are:

  • Enhancement of Security (Trusted Session)
  • Better Job Control and Workload Management
  • More information for Audits

There are several articles that have been posted that discuss the benefits and use of Query Bands.

If an application needed to use Query Bands prior to the 13.01 release, the SET QUERY_BAND statement had to be executed. It was also the application's responsibility to manage the Query Bands. Now, the provider is able to manage Query Bands on behalf of an application.

There is a new class, TdQueryBand, that is used to manage Query Bands. Applications also have the capability of defining Query Bands on the connection string using the QueryBand connection string attribute. Query Bands can be defined at either the connection or transaction levels.

This is how Query Bands are defined in a connection string:

User Id=tuser;Password=tpass;Data Source=td1;QueryBand='Action=update;ProxyUser=puser;ProxyRole=prole;';

There is a set of reserved Query Band keys that are used by debugging, logging, workload management, and other tools to monitor the use of the Teradata Database. These reserved keys are implemented as properties in the TdQueryBand class. The name of a few of these keys/properties are: Action, ApplicationName, ProxyUser, ProxyRole. To view the full list of Query Band keys refer to the Teradata Manual: SQL Data Definition Language.

The value for any of the properties/keys can be set several different ways:

TdQueryBand qb = new TdQueryBand();

qb.Action = "update";
qb["ProxyUser"] = "terauser";
qb.Add("Deadline", DateTimeOffset.Now);

Application specific Query Bands can be defined by using either the indexer or the Add method of the TdQueryBand class.

qb.Add("CustomQB1", "custom1");
qb["CustomQB2"] = "custom2";

TdQueryBand also supports the removal of a Query Band. 

qb.Action = String.Empty;
qb["ProxyUser"] = String.Empty;
qb.Remove("Deadline");

Connection Level Query Bands

Query Bands are defined at the Connection level by either defining them using the Query Band connection string attribute or by using the TdConnection.ChangeQueryBand() method.

To retrieve the Query Bands that have been defined at the connection level, the property TdConnection.QueryBand is called. This method will retrieve all the Query Bands associated with the connection, and return the definitions in a TdQueryBand instance.

After changes have been made to the Query Bands, the method TdConnection.ChangeQueryBand() must be called so that the changes can be applied to the Teradata Database. When this method is called, the provider will perform the following:

  1. Remove all connection level Query Bands.
  2. Apply the Query Bands contained in the TdQueryBand instance that is passed in as a parameter.

IMPORTANT: Because all defined Query Bands are first removed it is important to retrieve them using the property TdConnection.QueryBand when Query Bands are to be added, deleted, or modified. Any changes that are to be made to Query Bands should be made to the TdQueryBand instance returned from this property.

Query Bands that have been defined at the Connection level exist until the connection has been closed (TdConnection.Close), or have been removed (TdQueryBand.Clear) from the connection.

This is an example of adding and removing Query Bands:

static void QueryBandExample1()
{
TdConnectionStringBuilder sb = new TdConnectionStringBuilder();

sb.UserId = "terauser";
sb.Password = "terapass";
sb.DataSource = "tera1";
sb.QueryBand = new TdQueryBand("Action=Update;ProxyUser=proxy1;ProxyRole=role1;");

TdConnection conn = new TdConnection(sb.ConnectionString);
conn.Open();

//Retrieving Query Bands that have been defined.
TdQueryBand qb = conn.QueryBand;

//Modifying the current Query Bands
qb.Action = "delete"; //Modifying a Query Band
qb["customQb1"] = "custom1"; //Query Band will be added
qb.ProxyUser = String.Empty; //ProxyUser will be removed
qb.Remove("ProxyRole"); // ProxyRole will be removed

//Applying changes to Teradata Database
conn.ChangeQueryBand(qb);

conn.Close();
}

Transaction Level Query Bands

Transaction level Query Band are defined when either the TdConnection.BeginTransaction() method is called and a TdQueryBand instance is passed in as a parameter, or when the TdTransaction.ChangeQueryBand() method is called.

Modifying Query Bands at the Transaction level follow the same pattern as in the Connection Level. For example, to retrieve the Query Bands that have been defined in the transaction the property TdTransaction.QueryBand is called. All the modifications to the existing Query Bands should be performed on the TdQueryBand instance returned from this property. Once all the changes have been made, TdTransaction.ChangeQueryBand() is called to apply the changes to the transaction.

TdTransaction.ChangeQueryBand() performs same tasks as TdConnection.ChangeQueryBand(). When TdTransaction.ChangeQueryBand() is called all Query Bands at the Transaction level are removed, and the Query Bands defined in the TdQueryBand instance passed in as a parameter are applied.

IMPORTANT: If Query Bands exist in the transaction, it is important that TdTransaction.QueryBand is first called, and that any changes are performed on the TdQueryBand instance returned by this property.  Query Bands defined at the Connection level are inherited by the Transaction. If a Query Band has been defined at both the Transaction and Connection levels, the one at the Transaction level has precedence.

Query Bands that are defined at the Transaction level only exist while the transaction is open. Query Bands can be defined when opening a transaction (TdConnection.BeginTransaction()) or during an opened transaction (TdTransaction.ChangeQueryBand()).

This is an example of retrieving Query Bands, then modifying and applying the changes to the Transaction:

static void QueryBandExample2()
{
TdConnection conn = new TdConnection(
"User Id=tuser;Password=tpass;Data Source=tera1;QueryBand=Action=Update;ProxyUser=proxy1;ProxyRole=role1;");
conn.Open();

TdQueryBand tranQb = new TdQueryBand("AppCustom1=custom1;Action=delete;");

TdTransaction trans = conn.BeginTransaction(tranQb);

TdQueryBand definedQb = trans.QueryBand;

//The same Query Bands are defined in definedQb as in tranQb

//Modifying Query Bands
definedQb["AppCustom2"] = "custom2"; //Adding app. Specific Query Band
definedQb.Group= "gp1"; //Defining value for a reserved Query Band key

//Applying changes made to Query Bands
TdTransaction.ChangeQueryBand(definedQb);

trans.Commit();

conn.Close();
}

Trusted Sessions

A Trusted session is only supported by Teradata Database 13.0 or greater.

What is a Trusted Session? A Trusted Session is implemented using Query Bands. A Trusted Session enables a user of an application to access a Teradata Database by proxy through an established connection. An application will still need to log into the Teradata Database using a trusted user. However, each user of an application can be assigned an identifier used as a proxy. This proxy user is granted CONNECT THROUGH privileges to the trusted user. The proxy user is also assigned to roles which define a set of privileges. This is a brief explanation. For more information refer to the Teradata Manual SQL Data Definition Language: Detailed Topics.

Many application developers will probably find the Trusted Session capability the most important feature that is provided by Query Bands.

To specify a proxy user the ProxyUser reserved Query Band key is used. The roles assigned to a proxy user is specified using the ProxyRole key.

TdQueryBand has properties for both these keys called ProxyUser and ProxyRole. The following demonstrates how to set up a trusted session:

static TdConnection TrustedSessionExample(String proxyUser, String proxyRole)
{
TdQueryBand qb = new TdQueryBand();
qb.ProxyUser = proxyUser;
qb.ProxyRole = proxyRole;

TdConnectionStringBuilder builder = new TdConnectionStringBuilder();
builder.QueryBand = qb;
builder.DataSource = "tdat1";
builder.UserId = "tdatuser";
builder.Password = "tdatpass";

TdConnection conn = new TdConnection(builder.ConnectionString);

conn.Open();

return (conn);
}

Connection Pooling and Query Bands

The Teradata Provider enables Connection Pooling by default.

The provider can manage several Connection Pools, and each pool is identified by the Connection String.  For example a different connection pool will be accessed by the provider when TdConnection.Open is called for each of the four different connection strings :

Data Source=tdat1; User Id=tuser; Password=tpass; Connection Pooling=true;

Data Source=tdat1; User Id=tuser; Password=tpass; Connection Pooling=true;

User Id=tuser; Data Source=tdat1; Password=tpass; Connection Pooling=true

Password=tpass; User Id=tuser; Connection Pooling=true;Data Source=tdat1;

The provider will create a new pool in the case where the Connection String has not been associated with a connection pool.

When an application is using Query Bands, the Query Band values specified in the Connection String may need to be changed each time TdConnection.Open() is called. This is especially true when Trusted Sessions are being used. It would be a waste of resources if the provider were to create a new connection pool each time the Query Band values were changed by an application.

To prevent a new connection pool from being created, the Query Band connection string attribute is ignored when the provider associates a Connection String to a pool. For example, the following connection strings are associated to the same pool:

Data Source=tdat1; User Id=tuser; Password=tpass; Query Band='ProxyUser=joe;ProxyRole=admin;';

Data Source=tdat1; User Id=tuser; Password=tpass; Query Band='ProxyUser=sam;ProxyRole=client;';

Data Source=tdat1; User Id=tuser; Password=tpass; Query Band='ProxyRole=client;ProxyUser=sue;Action=upd;';

Data Source=tdat1; User Id=tuser; Query Band='ProxyRole=john;ProxyUser=client;Action=update;' ; Password=tpass;