Client Routing API for TimesTen Scaleout
These sections describe the client routing API for TimesTen Scaleout.
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
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 thettGridMapCreate
function with a valid ODBC connection. The function returns a handle to aTTGRIDMAP
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 aTTGRIDDIST
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 sameTTGRIDDIST
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 subsequentSQLBindParameter
ODBC call. -
How to Create the Grid Map and Distribution
This example shows how to work with the grid map and distribution.
- Create
TTGRIDMAP
andTTGRIDDIST
objects. - Call the
ttGridMapCreate
function to create a grid map using an existing ODBC connection. - Call the
ttGridDistCreate
function to create a grid distribution based on a distribution key composed of two columns. - Free the grid distribution and map with the
ttGridDistFree
andttGridMapFree
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
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 |
---|---|---|
|
|
Connection handle |
|
|
Grid distribution handle |
|
|
Position of the column in the distribution key |
|
|
Key value pointer |
|
|
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
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 |
---|---|---|
|
|
Connection handle |
|
|
Grid distribution handle |
|
|
Array of element IDs where the key values are allocated (IN/OUT) |
|
|
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 |
---|---|---|
|
|
Connection handle |
|
|
Grid distribution handle |
|
|
Replica set ID where the key values are allocated ( |
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 |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 thettGridDistElementGet
orttGridDistReplicaGet
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.