Client Routing API for TimesTen Scaleout

These sections describe the client routing API for TimesTen Scaleout.

• Functionality of the Client Routing API

• Creating a Grid Map and Distribution

• Distribution Key Values

• Getting the Element Location Given a Set of Key Values

• Client Routing API With Functions in Use

• Supported Data Types

• Restrictions

• Failure Modes

Functionality of the Client Routing API

To increase performance, TimesTen Scaleout enables your client application to route connections to an element based on the key value for a hash distribution key.

You provide a key value and TimesTen Scaleout returns an array of element IDs (or the replica set ID) where the database allocated that value. This enables the client application to connect to the element that stores the row with the specified key value, avoiding unnecessary communication between the element storing the row and the one connected to your application.

Note:

This feature is not supported with generic driver managers but is supported with the TimesTen driver manager.

Creating a Grid Map and Distribution

These sections show how to create a grid map and distribution for client routing.

• Functions for the Grid Map and Distribution

• How to Create the Grid Map and Distribution

Functions for the Grid Map and Distribution

There are functions you can use for a grid map and distribution for client routing.

TimesTen Scaleout includes two new objects for a grid map and a grid distribution in the timesten.h file:

• TTGRIDMAP: A grid map is a lookup table that maps the topology of a grid. You create a grid map by calling the ttGridMapCreate function with a valid ODBC connection. The function returns a handle to a TTGRIDMAP object.

  Use the ttGridMapFree function to free a grid map.

  Note:

  • A TTGRIDMAP object is not strongly associated with the HDBC connection. Freeing either object does not free the other.

  • A grid map can be shared among many grid distributions and across application threads. Only one grid map is required per application process per database.

• TTGRIDDIST: A grid distribution is an ordered set of types and values that represent the distribution key columns of a table or tables. For distribution keys composed of multiple columns, the order of the types and values must be the same as for the distribution key columns of the table.

  You create a grid distribution by calling the ttGridDistCreate function with the C type, SQL type, length, scale, and precision of the distribution key columns of a table. The function returns a handle to a TTGRIDDIST object. See Table 2-3.

  Use the ttGridDistFree function to free a grid distribution.

  Note:

  • A TTGRIDDIST object is not associated with a given table. You can use the same TTGRIDDIST object for any table that uses the same types and values in their distribution key columns.

  • A grid distribution cannot be shared across threads. However, multiple grid distributions in different threads can be created using the same grid map.

  Table 2-3 ttGridDistCreate Arguments

  Argument	Type	Description
  hdbc	SQLHDBC	Connection handle
  map	TTGRIDMAP	Grid map handle
  cTypes[]	SQLSMALLINT	Array of C bind types in the same order as the distribution key columns
  sqlTypes[]	SQLSMALLINT	Array of SQL bind types in the same order as the distribution key columns
  precisions[]	SQLULEN	Array of precision values in the same order as the distribution key columns
  scales[]	SQLSMALLINT	Array of scale values in the same order as the distribution key columns
  maxSizes[]	SQLLEN	Array of maximum column size values in the same order as the distribution key columns
  nCols	SQLUSMALLINT	Number of columns in the distribution key
  *dist	TTGRIDDIST	Grid distribution handle (OUT)

  Note:

  The parameters for ttGridDistCreate are similar to those used in a subsequent SQLBindParameter ODBC call.

How to Create the Grid Map and Distribution

This example shows how to work with the grid map and distribution.

1. Create TTGRIDMAP and TTGRIDDIST objects.
2. Call the ttGridMapCreate function to create a grid map using an existing ODBC connection.
3. Call the ttGridDistCreate function to create a grid distribution based on a distribution key composed of two columns.
4. Free the grid distribution and map with the ttGridDistFree and ttGridMapFree functions, respectively.

TTGRIDMAP map;
TTGRIDDIST dist;

ttGridMapCreate(hdbc, &map);

SQLSMALLINT cTypes[] = { SQL_C_LONG, SQL_C_CHAR };
SQLSMALLINT sqlTypes[] = { SQL_INTEGER, SQL_CHAR };
SQLLEN maxSizes[] = { 4, 20 };

ttGridDistCreate(hdbc, map, cTypes, sqlTypes, NULL, NULL, maxSizes, 2, &dist);

...

ttGridDistFree(hdbc, dist);
ttGridMapFree(hdbc, map);

Distribution Key Values

There are methods to set the grid distribution key values for client routing.

• Function for Distribution Key Values

• Setting Distribution Key Values

Function for Distribution Key Values

Use the ttGridDistValueSet function to set the grid distribution key values for client routing.

With the grid map and distribution defined, you set the key values in order to determine the elements in which they are allocated. Call the ttGridDistValueSet function to set the key value for one of the columns in the distribution key. For distribution keys composed of multiple columns, call this function once for every column in the distribution key. Table 2-4 provides a brief summary of the arguments of the ttGridDistValueSet function.

Table 2-4 ttGridDistValueSet Arguments

Argument	Type	Description
hdbc	SQLHDBC	Connection handle
dist	TTGRIDDIST	Grid distribution handle
position	SQLSMALLINT	Position of the column in the distribution key
value	SQLPOINTER	Key value pointer
valueLen	SQLLEN	Length of the key value

Setting Distribution Key Values

This example first calls the ttGridDistClear function to clear any previously defined key values for the distribution key columns. Then, the example calls the ttGridDistValueSet function for every column in the distribution key and sets the key value for each column.

ttGridDistClear(hdbc, dist);

ttGridDistValueSet(hdbc, dist, 1, empId, sizeof(empId));
ttGridDistValueSet(hdbc, dist, 2, "SALES", SQL_NTS);

Getting the Element Location Given a Set of Key Values

Once you set the distribution key values, this section shows that you can call for the location of the key values either by element IDs or replica set ID.

These topics are covered:

• Function for Element IDs

• Getting the Element IDs

• Function for Replica Set ID

• Getting the Replica Set ID

Function for Element IDs

Call the ttGridDistElementGet function to obtain the corresponding element IDs that represent the location of the provided key values. The function returns an array of element IDs. The application is responsible for allocating the return array. The length of the array is based on the value of K-safety of the grid.

For example, in a grid with K-safety set to 2, there must be at least two elements in the array. Table 2-5 provides a brief summary of the arguments of the ttGridDistElementGet function.

Table 2-5 ttGridDistElementGet Arguments

Argument	Type	Description
hdbc	SQLHDBC	Connection handle
dist	TTGRIDDIST	Grid distribution handle
elemIds[]	SQLSMALLINT	Array of element IDs where the key values are allocated (IN/OUT)
elemIdSize	SQLSMALLINT	Value of K-safety

Getting the Element IDs

These examples show how to get the element IDs and how to associate an element ID with a connection string.

This example gets the array of element IDs associated with the current key values (set by the ttGridDistValueSet function) by calling the ttGridDistElementGet function.

SQLSMALLINT elementIds[2];

ttGridDistElementGet(hdbc, dist, elementIds, 2);

Note:

The elementIds array must be of a length equal or greater than the value of K-safety of the grid.

With the location of the set of key values available, your application can use the element IDs to select a connection to one of the elements, prepare a statement, bind values, and execute the statement.

Note:

The connection attempt can be subject to a failover event and the application may not connect to the expected element.

The example that follows shows a query that may help you associate an element ID with a connection string. It assembles a connection string for each element of the database by querying the SYS.V$DISTRIBUTION_CURRENT system view. The connection string includes the TTC_REDIRECT=0 attribute to ensure a connection to the specified element or its replica. If the connection to all replicas fails, then a connection error is returned.

select 'TTC_REDIRECT=0;
TTC_SERVER='||hostexternaladdress||'/'||serverport,mappedelementid
 from SYS.V$DISTRIBUTION_CURRENT;
< TTC_REDIRECT=0;TTC_SERVER=ext-host3.example.com/6625, 1 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host4.example.com/6625, 2 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host5.example.com/6625, 3 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host6.example.com/6625, 4 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host7.example.com/6625, 5 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host8.example.com/6625, 6 >
6 rows found.

Function for Replica Set ID

Call the ttGridDistReplicaGet function to obtain the corresponding replica set ID that represents the location of the provided key values.

Table 2-6 provides a brief summary of the arguments of the ttGridDistReplicaGet function.

Table 2-6 ttGridDistReplicaGet Arguments

Argument	Type	Description
hdbc	SQLHDBC	Connection handle
dist	TTGRIDDIST	Grid distribution handle
*replicaSetId	SQLSMALLINT	Replica set ID where the key values are allocated (OUT)

Getting the Replica Set ID

This example gets the replica set ID associated with the current key values (set by the ttGridDistValueSet function) by calling the ttGridDistReplicaGet function.

SQLSMALLINT replicaSetId;

ttGridDistReplicaGet(hdbc, dist, replicaSetId);

You can use the replica set ID with the SYS.V$DISTRIBUTION_CURRENT system view to look up the communication parameters of the elements in that replica set.

Client Routing API With Functions in Use

This partial example shows the client routing API with most of its objects and functions in use.

#include <timesten.h>

...

TTGRIDMAP map;
TTGRIDDIST dist;

/* Create a grid map using any existing connection. */
ttGridMapCreate(hdbc, &map);

/* The distribution key has two columns: one with TT_INTEGER as data type and
 * one with CHAR(20), in that order. Precision and scale are not necessary. */
SQLSMALLINT cTypes[] = { SQL_C_LONG, SQL_C_CHAR };
SQLSMALLINT sqlTypes[] = { SQL_INTEGER, SQL_CHAR };
SQLLEN maxSizes[] = { 4, 20 };

/* Create grid distribution from the grip map and the specified distribution
 * key column paremeters. */
ttGridDistCreate(hdbc, map, cTypes, sqlTypes, NULL, NULL, maxSizes, 2, &dist);

/* Execution loop. */
while ( ... ) 
{
      SQLSMALLINT elementIds[2];

      /* Clear the existing key values from the distribution map */
      ttGridDistClear(hdbc, dist);

      /* Set the key values for the grid distribution. */
      ttGridDistValueSet(hdbc, dist, 1, key1, sizeof(key1));
      ttGridDistValueSet(hdbc, dist, 2, key2, SQL_NTS);

      /* Get the corresponding element IDs for current key values*/
      ttGridDistElementGet(hdbc, dist, elementIds, 2);

      /* The application uses the element IDs to select a connection to 
       * one of the elements, prepare a statement, bind values, and execute 
       * the statement. */
      ...
}

/* Free the grid distribuion and map. */
ttGridDistFree(hdbc, dist);
ttGridMapFree(hdbc, map);

Supported Data Types

The TTGRIDDIST object is created using the C types and SQL types available from ODBC.

Table 2-7 shows the supported C types and SQL types with their corresponding Database SQL types.

Table 2-7 List of Supported Types

C Types	ODBC SQL Types	Database SQL Types
SQL_C_TINYINT	SQL_TINYINT	TT_TINYINT
SQL_C_SMALLINT	SQL_SMALLINT	TT_SMALLINT
SQL_C_LONG	SQL_INTEGER	TT_INTEGER
SQL_C_BIGINT	SQL_BIGINT	TT_BIGINT
SQL_C_CHAR	SQL_CHAR	CHAR
SQL_C_CHAR	SQL_VARCHAR	VARCHAR, VARCHAR2
SQL_C_WCHAR	SQL_WCHAR	NCHAR
SQL_C_WCHAR	SQL_WVARCHAR	NVARCHAR
SQL_C_SQLT_NUM	SQL_DOUBLE	NUMBER
SQL_C_SQLT_NUM	SQL_DECIMAL	NUMBER(p,s)
SQL_C_SQLT_VNU	SQL_DOUBLE	NUMBER
SQL_C_SQLT_VNU	SQL_DECIMAL	NUMBER(p,s)

The TTGRIDDIST object supports all signed and unsigned data type variants. For example, it supports both SQL_C_SLONG and SQL_C_ULONG.

You can set NULL values by specifying SQL_NULL_DATA for the valueLen parameter of the ttGridDistValueSet function. The NULL value will always map to the same replica set or element IDs.

Restrictions

Client routing has certain restrictions.

• It does not have implicit connection or statement management.

• It does not support date, time, or timestamp data types.

• It does not support explicit type conversion. Applications must specify key values in canonical byte format.

• It does not support character set conversion. It ignores the connection character set.

• Changes in the topology of the grid require that applications free and recreate the grid map.

Failure Modes

The client routing API may return certain errors.

• Incorrect types and values to describe the distribution key columns of the table. In this case, the API will still compute an array of element IDs, but these may not correspond to the real location of the desired key values.

• Unrecognized type codes. If you call the ttGridDistCreate function with unrecognized type codes, the function returns an error.

• Not enough values set for the grid distribution. If you do not provide enough values for the distribution key through the ttGridDistValueSet function, then the ttGridDistElementGet or ttGridDistReplicaGet function would return an error.

• Invalid size of the element IDs array. If you do not provide an array of at least the size of the value of K-safety, the ttGridDistElementGet function would return an error.