Build a Teradata PT Database Connection Object - Parallel Transporter

Build a Teradata PT Database Connection Object

In order to establish a connection with the Teradata Database, users must create an object that will manage all of that connection’s important attributes. In Teradata PT, this object is called a Connection object.

There are two steps involved in building a Connection object:

1 Create a Connection Object

The Connection object is defined as a C++ class. A Connection object can be created by calling the Connection class constructor.

using namespace teradata::client::API;

Connection* conn = new Connection();

2 Add Parameters

The Connection object is used to house parameters for a database connection. There are three types of parameters:

a attributes

b schemas

c DML Groups

Attributes

There are various types of options that can be set using attributes. Each driver has a set of required attributes and optional attributes. Depending on the attribute, there can be multiple values for that attribute. These are called array attributes. The following is an example of adding attributes and array attributes:

conn->AddAttribute(TD_MAX_SESSIONS,4);

conn->AddArrayAttribute(TD_WORK_TABLE, 2,"testa_wt","testb_wt", NULL);

Set the value(s) for an attribute in a single call to either the AddAttribute function or the AddArrayAttribute function. Once an attribute has been set, the value(s) of the attribute cannot be changed and additional values cannot be added to it.

Set all attributes prior to calling the Initiate function. After a Connection object has been initiated, no additional attributes may be set.

Schemas

A schema object defines the format of the data being used in the load and export tasks. A schema consists of one or more columns, and the format of each column’s data. The schema lets the driver know what data to expect.

Unlike an attribute, a schema is defined as an external C++ class. When creating a schema object using the class constructor, the schema must be designated as an input or output schema. This means the task will involve either loading, updating data, or exporting data. The following is an example of creating a schema object:

Schema * schema = new Schema("input");

Columns are added to the schema object one at a time using the TD_DataType constants to designate the data type of the column. The following are examples of adding a column to a schema object:

schema->AddColumn("Associate_Name",TD_CHAR,25);

schema->AddColumn("Salary",TD_FLOAT,8);

Figure 3 depicts the coding lines for a two‑table select statement and how the data is brought together in the answer set.

Figure 3: Schema Definition for a Multiple Table Select Statement

When all columns have been added to the schema object, the schema object is then passed to the Connection class. The following is an example of adding a schema object to a Connection object:

conn->AddSchema(schema);

Working with Schemas

When working with schemas, note the following:

Every driver requires a schema except when using either the Update driver's Delete Task with no variable substitution or the Export driver’s dynamic schema feature.

A Connection object may have only one schema.

The column names defined in the schema do not have to match the actual names of the columns in the target table(s) as they can be renamed in the SQL statements used for the job. The column names in the schema must match the names used in the job’s SQL statements.

The column data types defined in the schema do not have to match the actual data types of the columns in the target table(s) since they can be cast into other data types in the SQL statements used. The column data type in the schema must, however, match the data types used in the job’s SQL statements.

The order of the columns defined in the schema does not have to match the actual order of the columns in the target table(s) as the order can be changed in the SQL statements used. The order of the columns in the schema must, however, match the order of the columns in the job’s SQL statements.

When loading multiple tables using the Update or Stream driver, the input schema defined for the job is a superset of the columns in all of the tables being loaded. One or more of the columns in the schema must correlate to the columns being loaded in each of the target tables.

VARCHAR and VARBYTE data with a length less than or equal to the defined length in the SCHEMA object is allowed.

Examples of Schema Definitions

If the Export driver is being used to export data from Table 1 which has columns A, B, and C, and the SELECT statement is:

SELECT A FROM T	able 1

then the output schema is defined as one column with a data type matching column A’s definition in Table 1. If column A is defined as a variable character with a length of 50, then the column in the output schema is defined as TD_VARCHAR with a length of 50.

Schema* schema = new Schema("output");

schema->AddColumn("A", TD_VARCHAR, 50);

Here’s another example using the same table. If the select statement is changed to:

SELECT A(CHAR(100)) FROM Table 1

then the column in the output schema is defined as TD_CHAR with a length of 100.

Schema* schema = new Schema("output");

schema->AddColumn("A", TD_CHAR, 100);

DML Groups

DMLGroup objects are used to store sets of DML statements and DML options which can later be applied when loading or updating data. A DMLGroup is defined as an external C++ class.

The following is an example of creating a DMLGroup object:

DMLGroup * dmlGr = new DMLGroup();

Each DML statement is stored as a character string within the DMLGroup object. DML statements can be added one at a time to the DMLGroup object using the AddStatement function. More than one DML statements can be added to the same DMLGroup object but each DML statement must be added in a separate call to the AddStatement function. The following is an example of adding a statement to a DMLGroup object:

dmlGr->AddStatement("INSERT INTO test1( :Associate_Id, :Salary );");

Along with DML statements, a series of DML options can be set for a DMLGroup object using the TD_DMLOption parameters. The following is an example of setting DML options for a DMLGroup object:

dmlGr->AddDMLOption(MARK_DUPLICATE_ROWS);

A DMLgroup is added to the Connection class by using the AddDMLGroup function. The AddDMLGroup function takes two parameters:

A pointer to the DML group being added

a pointer to a TD_Index object through which the function will return the index reference for the DML group. This index is used later with the UseDMLGroups function.

The following is an example of adding a DML group to the Connection class:

TD_Index index;

TD_StatusCode returnValue = conn->AddDMLGroup(dmlGr, &index);