Query Bands can be defined at the Connection level by either specifying the Query Band attribute in the Connection String or by using the TdConnection.ChangeQueryBand method. To define Query Bands when opening a connection, the Query Band attribute is specified in the connection string. After a connection has been opened, the TdConnection.ChangeQueryBand method can be called to add, modify, or remove Query Bands from the connection.

A Query Band defined at the connection level remains set until the connection has been closed (TdConnection.Close) or is removed by the application.

Query Bands defined at the Connection level are also visible during a transaction.

Defining Query Bands using the Query Band Connection String Attribute

By using the QueryBand Connection String Attribute, Query Bands are defined when TdConnection.Open is called. The following is a snippet of code that uses the QueryBand Connection String Attribute:

String conStr = "Query Band='ProxyUser=puser1;Action=delete;';Data Source=tdat1;User Id=user1;Password=pass1";

TdConnection conn = TdConnection(conStr);
conn.Open()

The Query Band value must be enclosed in double or single quotes. This will enable the provider to associate all the key=value pairs with the Query Band attribute.

Adding, Modifying, and Deleting Query Bands

After a connection has been opened, Query Bands can be added, modified, or deleted from the connection. This is done by retrieving an instance of TdQueryBand from the TdConnection.QueryBand property. By using the TdQueryBand instance returned from this property an application is able to add, modify, delete, or retrieve existing Query Bands that have been defined on the connection to the Advanced SQL Engine.

Any changes made to the TdQueryBand instance returned from the TdConnection.QueryBand property do not have any affect on the connection until TdConnection.ChangeQueryBand has been called. This method is used to apply the changes made to the connection.

The following is an example of code that uses TdQueryBand to add, modify, and remove Query Bands:

public void QueryBandExample()
{
   TdConnectionStringBuilder connBuilder = new TdConnectionStringBuilder();
   connBuilder.DataSource = "tdat1";
   connBuilder.UserId = "user1";
   connBuilder.Password = "pass1";

   //Setting up two Query Bands that will be defined when the connection is opened
   connBuilder.QueryBand = "ProxyUser=puser1;Application=app1;";

   TdConnection conn = TdConnection(connBuilder.ConnectionString);
   conn.Open();

   // retrieve query bands from connection
   TdQueryBand qb = conn.QueryBand;

   // New Query Bands can be added using the Indexer
   qb["Action"] = "Update";

   // Or by using the Add method
   qb.Add("JobId", "312");

   // A existing Query Band can be modified by using the indexer
   qb["ProxyUser"] = "user2";

   // A existing Query Band can also be removed
   qb["Application"] = null;

   // Applying the changes
   conn.ChangeQueryBand(qb);
   //
   // PERFORM TASKS
   //
   conn.Close();
}

Connection Level Query Band Guidelines

The following are guidelines related to Connection Level Query Bands:

1. Query Bands that were defined using the Query Band Connection String Attribute are not visible from the TdConnection.QueryBand property until TdConnection.Open is called.
2. After a connection has been opened, new Query Bands can be defined or existing ones can be modified/removed by calling the TdConnection.ChangeQueryBand method.
3. After Query Bands have been added or modified, the TdConnection.ChangeQueryBand must be called and passed the instance of TdQueryBand that contains the modified Query Bands.
4. If TdConnection.ChangeQueryBand is called when connected to a Teradata Database release prior to 12.0, a TdException will be thrown indicating that the SQL Engine does not support Query Bands.
5. Trusted Sessions (ProxyUser and ProxyRole) are only supported when connected to a Teradata Database 13.0 or later. If ProxyUser and/or ProxyRole are specified when connected to a Teradata Database 12.0, a TdException will be thrown indicating that Trusted Sessions are not supported by the SQL Engine.
6. When connection pooling is enabled, the connection string is used to determine what pool is used to retrieve a connection from when an application calls TdConnection.Open. The Query Band attribute is ignored when the provider determines which pool will be used to retrieve the connection.
7. Application specific (custom) Query Bands can be defined in the QueryBand connection string attribute or the TdQueryBand object.
8. After TdConnection.Close is called, the provider removes all Query Bands from the connection before it is returned to the connection pool. The Query Bands defined in the connection string will be re-applied to the connection when it is retrieved from the pool by TdConnection.Open.
9. When the Teradata provider's capabilities to manage Query Bands are used by an application, it is important that the application does not submit "SET QUERY_BAND" statements to the SQL Engine.

Example of Connection Level Query Bands

The following is an example of setting Connection Level Query Bands:

Public void QueryBandDemo()
{
   TdConnectionStringBuilder sb = new TdConnectionStringBuilder();

   // Setting the connection string using the connection string builder.
   sb.DataSource = "teradata1";
   sb.UserId = "user1";
   sb.Password = "pass1";
   // The ApplicationName is a reserved Query Band name used for logging, auditing, and
   // workload management.
   // Custom1 is a customer specific Query Band
   sb.QueryBand = "ApplicationName = App1;Custom1 = cust1;"

   TdConnection tc = new TdConnection(sb.ToString());
   tc.Open();

   // If Query Bands have been defined in the connection string, it is important that
   // an instance of TdQueryBand be retrieved from TdConnection.QueryBand. The
   // instance that is returned will have all the existing Query Bands set to their
   // specified values.

   TdQueryBand cqb = tc.QueryBand;

   // There are reserved Query Band names that can be set by an application.
   // The reserved names will be represented as properties in the TdQueryBand class.
   // They could also have been defined in the connection string.
   cqb.Action = "Update";

   // A reserved Query Band name can also be specified using the Add method.
   cqb.Add("Group", "Manufacturing");

   // Custom key=value pairs can also be defined
   cqb["SpecialName"] = "AppValue";

   // Query Bands can be modified after connecting to Teradata.

   // Removing the custom Query Band.
   cqb ["Custom1"] = null;

   // Removing the definition of a reserved Query Band
   // A Query Band can also be removed by specifying it's default value
   cqb ["Action"] = String.Empty;

   // Modifying the ProxyUser Reserved Query Band by using it's property
   cqb.ProxyUser = "newuser";

   // Adding a custom Query Band
   cqb.Add("SpecialCategory", "AppValue");

   // After any Query Band has been modified, it needs to be applied to the connection
   tc.ChangeQueryBand (cqb);

   //
   // Application performs it required tasks
   //

   tc.Close();
}

See Also

Connection Pools: Connection Pooling and Query Bands