5 Managing a Database

Create a database definition

A database definition contains the description of a database. It defines the database name, as well as the attributes of the database. Once a database definition is added to the current version of the model, it can be used to create a database. Each database has one or more connectables associated with it. Connectables specify how applications connect to the database. Connectables are discussed in "Connecting to a database".

vi /mydir/database1.dbdef

DataStore=/disk1/databases/database1
LogDir=/disk2/logs
DatabaseCharacterSet=AL32UTF8
Durability=0
PermSize=32768
TempSize=4096
LogBufMB=1024
Connections=2048

% ttGridAdmin dbdefCreate /mydir/database1.dbdef
Database Definition database1 created.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

Create a database based on the database definition

In TimesTen Scaleout, user data is distributed to a set of elements that form a database. Each data instance in the current version of the model contains one element of every user database in the grid.

You can create a database based on the attributes stored in a database definition. On database creation, every data instance creates an element of the database and loads it into memory.

The process of creating an element of the database on every data instance is asynchronous. The daemon of each data instance performs the operations necessary to create and load the element into memory independently, as soon as it realizes that there is a new database flagged for creation.

The ttGridAdmin dbCreate command creates a database based on a database definition.

Create the database1 database based on the database1 database definition.

% ttGridAdmin dbCreate database1
Database database1 creation started

Wait until all data instances report that they have loaded their element of the database into memory before proceeding with the definition of the distribution map. You can verify the status of the database creation process with the ttGridAdmin dbStatus command as shown in Example 5-2.

% ttGridAdmin dbStatus database1 -element
Database database1 element level status as of Wed Jan 10 14:34:08 PST 2018
 
Host  Instance  Elem Status Date/Time of Event  Message 
----- --------- ---- ------ ------------------- ------- 
host3 instance1    1 loaded 2018-01-10 14:33:23         
host4 instance1    2 loaded 2018-01-10 14:33:21         
host5 instance1    3 loaded 2018-01-10 14:33:23         
host6 instance1    4 loaded 2018-01-10 14:33:23         
host7 instance1    5 loaded 2018-01-10 14:33:23         
host8 instance1    6 loaded 2018-01-10 14:33:23         

For more information on the ttGridAdmin dbCreate or ttGridAdmin dbStatus command, see "Create a database (dbCreate)" or "Monitor the status of a database (dbStatus)", respectively, in the Oracle TimesTen In-Memory Database Reference.

Define the distribution map of the database

TimesTen Scaleout allows for elastic scalability. You can increase or reduce the number of elements in your database according to your business needs. When you add new data instances to a grid, TimesTen Scaleout does not automatically re-distribute the data stored in the database across the elements of the new or remaining instances. The way the data is distributed in TimesTen Scaleout is defined by the data space group associated to each host in the grid and the elements of the data instances defined in the distribution map of the database.

The ttGridAdmin dbDistribute command with the -add option adds the element of a data instance to the distribution map of a database. Using all as the parameter for the -add option adds the elements of all the available data instances in the grid. The all parameter is typically used for the initial definition of the distribution map of a new database.

Add all the elements of the available data instances in the grid1 grid to the distribution map of the database1 database.

% ttGridAdmin dbDistribute database1 -add all -apply
Distribution map updated

For more information on the ttGridAdmin dbDistribute command, see "Set or modify the distribution scheme of a database (dbDistribute)" in the Oracle TimesTen In-Memory Database Reference.

Open the database for user connections

For an application to be able to connect to a database, the database needs to be open for user connections. As with the database creation process, the process of opening elements is asynchronous. The daemon of every data instance performs the operations necessary to open its element as soon as it realizes that the database is flagged for opening.

The ttGridAdmin dbOpen command opens a database for user connections.

Open the database1 database for user connections.

% ttGridAdmin dbOpen database1
Database database1 open started

You can verify the status of the database opening process with the ttGridAdmin dbStatus command as shown in Example 5-3.

% ttGridAdmin dbStatus database1 -element
Database database1 element level status as of Wed Jan 10 14:34:43 PST 2018
 
Host  Instance  Elem Status Date/Time of Event  Message 
----- --------- ---- ------ ------------------- ------- 
host3 instance1    1 opened 2018-01-10 14:34:43         
host4 instance1    2 opened 2018-01-10 14:34:43         
host5 instance1    3 opened 2018-01-10 14:34:42         
host6 instance1    4 opened 2018-01-10 14:34:42         
host7 instance1    5 opened 2018-01-10 14:34:42         
host8 instance1    6 opened 2018-01-10 14:34:42         

For more information on the ttGridAdmin dbOpen or ttGridAdmin dbStatus command, see "Open a database (dbOpen)" or "Monitor the status of a database (dbStatus)", respectively, in the Oracle TimesTen In-Memory Database Reference.

Create a connectable

TimesTen Scaleout creates a direct connectable by default for every database definition added to the grid, this connectable enables applications to create direct connections to the database from any data instance in the distribution map of the database. TimesTen Scaleout uses the name assigned to the database definition to name the connectable. You need to create a client/server connectable to establish client/server connections to a database.

The tasks to create a connectable are:

• Creating a connectable file

• Creating a connectable based on the connectable file

ConnectionCharacterSet=AL32UTF8
UID=terry

% ttGridAdmin connectableCreate -dbdef database1 -cs /mydir/database1CS.connect
Connectable database1CS created.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

Connect to a database using ODBC and JDBC drivers

Applications can use the ODBC direct driver, the ODBC client driver, or an ODBC driver manager to connect to a database. See "Connecting to TimesTen with ODBC and JDBC drivers" in the Oracle TimesTen In-Memory Database Operations Guide for more information.

The following topics discuss how to use those DSNs to establish direct and client connections to a database:

• Establishing direct connections from a data instance

• Establishing client connections from a TimesTen Client

• Redirecting client connections

% ttIsql -connStr "DSN=database1"

Copyright (c) 1996, 2016, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=database1";
Connection successful: DSN=database1;UID=pat;DataStore=/disk1/databases/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;LogDir=/disk2/logs;
PermSize=32768;TempSize=4096;TypeMode=0;
(Default setting AutoCommit=1)
Command> 

% ttGridAdmin gridClientExport /mydir/sys.odbc.ini

[ODBC Data Sources]
database1CS=TimesTen 18.1.4 Client Driver
 
[database1CS]
TTC_SERVER_DSN=DATABASE1
# External address/port info for host3.instance1
TTC_SERVER1=host3.example.com/6625
# External address/port info for host4.instance1
TTC_SERVER2=host4.example.com/6625
# External address/port info for host5.instance1
TTC_SERVER3=host5.example.com/6625
# External address/port info for host6.instance1
TTC_SERVER4=host6.example.com/6625
# External address/port info for host7.instance1
TTC_SERVER5=host7.example.com/6625
# External address/port info for host8.instance1
TTC_SERVER6=host8.example.com/6625
ConnectionCharacterSet=AL32UTF8
UID=terry

% ttIsqlCS -connStr "DSN=database1CS"

Copyright (c) 1996, 2016, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=database1CS;UID=terry;
Enter password for 'terry': 
Connection successful: DSN=database1CS;TTC_SERVER=host3.example.com;
TTC_SERVER_DSN=DATABASE1;UID=terry;DATASTORE=/disk1/databases/database1;
DATABASECHARACTERSET=AL32UTF8;CONNECTIONCHARACTERSET=AL32UTF8;
PERMSIZE=32768;TEMPSIZE=4096;TYPEMODE=0;
(Default setting AutoCommit=1)
Command> 

C:\>ttInstallDSN -f C:\Users\terry\Downloads\sys.odbc.ini
 
Found the following DSNs in available 'C:\Users\terry\Downloads\sys.odbc.ini'.
0 : database1CS
[ Please select the DSN to be imported: ]
0
Adding DSN 'database1CS'.

C:\>ttIsql -connStr "DSN=database1CS"
 
Copyright (c) 1996, 2016, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=database1CS;UID=terry;
Enter password for 'terry': 
Connection successful: DSN=database1CS;TTC_SERVER=host3.example.com;
TTC_SERVER_DSN=DATABASE1;UID=terry;DATASTORE=/disk1/databases/database1;
DATABASECHARACTERSET=AL32UTF8;CONNECTIONCHARACTERSET=AL32UTF8;
PERMSIZE=256;TEMPSIZE=128;TYPEMODE=0;
(Default setting AutoCommit=1)
Command> 

% ttIsqlCS -connStr "TTC_SERVER=host3.example.com;TTC_SERVER_DSN=database1;
 TCP_Port=6625;UID=terry"

Verify if your database is a distributed database

If you want to verify that the database you are connected to is indeed a distributed database (TimesTen Scaleout) and not a single-instance database (TimesTen Classic), call for the value of the ttGridEnable attribute with the ttConfiguration built-in procedure. The built-in procedure returns ttGridEnable=1 for databases in a grid.

Command> CALL ttConfiguration('ttGridEnable');
< TTGridEnable, 1 >
1 row found.
Command>

For more information on the ttConfiguration built-in procedure, see "ttConfiguration" in the Oracle TimesTen In-Memory Database Reference.

Hash

The hash distribution scheme distributes data based on the hash of the primary key or a set of user-specified columns. The hash key determines in which replica set a row should be stored. Any given row in the table is stored in only one replica set. If the table does not have a primary key or a user-specified distribution column, TimesTen Scaleout distributes the data based on the hash of a hidden column that TimesTen Scaleout adds for this purpose. This distribution scheme is adaptive to topology changes and uses consistent hashing. In other words, a row with an specific value in the hash key columns will always be allocated on the same replica set, provided that the topology does not change. If the topology changes, the location of the row may change when the data is re-distributed.

Create the customers table that uses a DISTRIBUTE BY HASH clause, which distributes data based on the hash of the cust_id primary key column.

CREATE TABLE customers (
    cust_id            NUMBER(10,0) NOT NULL PRIMARY KEY,
    first_name         VARCHAR2(30) NOT NULL,
    last_name          VARCHAR2(30) NOT NULL,
    addr1              VARCHAR2(64),
    addr2              VARCHAR2(64),
    zipcode            VARCHAR2(5),
    member_since       DATE NOT NULL
) DISTRIBUTE BY HASH;

Figure 5-1 shows the data distribution for the customers table in the database1 database, as configured in "Creating a database". TimesTen Scaleout distributes the data to each element based on the hash of the cust_id column.

Figure 5-1 Table distributed by hash

Description of ''Figure 5-1 Table distributed by hash''

For more information on the hash distribution scheme, see "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

Reference

The reference distribution scheme distributes the data of a child table based on the location of the corresponding parent row of a foreign key constraint. This distribution scheme optimizes the performance of joins by distributing related data on a single element. When you join the parent and child tables, TimesTen Scaleout does not need to access different elements because all of the data is stored on the same element. The parent table can be distributed by hash or reference, which allows for a multi-tiered reference distribution.

Create the customers parent table that uses a DISTRIBUTE BY HASH clause that distributes data based on the hash of the cust_id primary key column. Then, create the accounts child table that uses a DISTRIBUTE BY REFERENCE clause that distributes the data in the accounts table based on the location of the corresponding value of the referenced column, customers(cust_id), in the fk_customer foreign key.

CREATE TABLE customers (
    cust_id            NUMBER(10,0) NOT NULL PRIMARY KEY,
    first_name         VARCHAR2(30) NOT NULL,
    last_name          VARCHAR2(30) NOT NULL,
    addr1              VARCHAR2(64),
    addr2              VARCHAR2(64),
    zipcode            VARCHAR2(5),
    member_since       DATE NOT NULL
) DISTRIBUTE BY HASH;
 
CREATE TABLE accounts (
    account_id         NUMBER(10,0) NOT NULL PRIMARY KEY,
    phone              VARCHAR2(15) NOT NULL,
    account_type       CHAR(1) NOT NULL,
    status             NUMBER(2) NOT NULL,
    current_balance    NUMBER(10,2) NOT NULL,
    prev_balance       NUMBER(10,2) NOT NULL,
    date_created       DATE NOT NULL,
    cust_id            NUMBER(10,0) NOT NULL,
    CONSTRAINT fk_customer
        FOREIGN KEY (cust_id)
            REFERENCES customers(cust_id)
) DISTRIBUTE BY REFERENCE (fk_customer);

Figure 5-2 shows the data distribution for the customers table in the database1 database, as configured in "Creating a database". TimesTen Scaleout distributes the data in the customers table to each replica set based on the hash of the cust_id primary key column. The figure also shows the data distribution for the accounts table, which is based on the location of the corresponding value of the referenced column, cutomers(cust_id), in the fk_customer foreign key.

Figure 5-2 Table distributed by reference

Description of ''Figure 5-2 Table distributed by reference''

For more information on the reference distribution scheme, see "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

Duplicate

The duplicate distribution scheme distributes identical copies of the data of a table to all the elements of a database. This distribution scheme optimizes the performance of reads and joins against the table by ensuring that all data access is local. However, inserts and updates are more resource intensive than other distribution schemes.

Create the account_type table that uses a DUPLICATE clause that distributes the data to all the elements of a database.

CREATE TABLE account_type (
    type            CHAR(1) NOT NULL PRIMARY KEY,
    description     VARCHAR2(100) NOT NULL
) DUPLICATE;

Figure 5-3 shows the data distribution for the account_type table in the database1 database, as configured in "Creating a database". TimesTen Scaleout creates a copy of the data on all the elements of the database.

Figure 5-3 Table distributed by duplicate

Description of ''Figure 5-3 Table distributed by duplicate''

For more information on the duplicate distribution scheme, see "CREATE TABLE" in the Oracle TimesTen In-Memory Database SQL Reference.

Materialized views as a secondary form of distribution

Materialized views enable you to create a secondary form of distribution for a table and can be useful in scenarios such as:

• If you have a table with a primary key and a unique column and you distribute the table by hash based on the primary key column, TimesTen Scaleout would need to connect to every element of the database to verify the uniqueness of the values inserted or updated in the unique column. In this case, the hash distribution scheme cannot guarantee that duplicate values are located in the same element. If you additionally create a materialized view of the table that is distributed by hash based on the unique column, TimesTen Scaleout would be able to verify the uniqueness of the values in the unique column more efficiently since the location of the row in the materialized view would be determined by the value in the unique column. It is also recommended to create an index on the materialized view for the unique column to get optimum query performance. See Example 5-6 for an example of this scenario.

• If you have a table with two independent groups of columns that are commonly joined in queries, consider distributing the table by hash based on one of the groups of columns. This will optimize the queries against this group of columns. Then, to optimize queries against the second groups of columns, create a materialized view of the table that is distributed by hash based on the second group of columns. See Example 5-7 for an example of this scenario.

Consider the following when using materialized views in TimesTen Scaleout:

• Distribution is limited to the hash distribution scheme only.

• The hash key column must be explicitly specified, even if you intend to use the primary key as the hash key.

• The SQL optimizer may re-write a query against a base table to use an available materialized view instead if it detects that it may improve the execution time of the query.

CREATE TABLE customers (
    cust_id            NUMBER(10,0) NOT NULL PRIMARY KEY,
    first_name         VARCHAR2(30) NOT NULL,
    last_name          VARCHAR2(30) NOT NULL,
    addr1              VARCHAR2(64),
    addr2              VARCHAR2(64),
    zipcode            VARCHAR2(5),
    member_since       DATE NOT NULL
) DISTRIBUTE BY HASH;
 
CREATE TABLE accounts (
    account_id         NUMBER(10,0) NOT NULL PRIMARY KEY,
    phone              VARCHAR2(16) NOT NULL UNIQUE,
    account_type       CHAR(1) NOT NULL,
    status             NUMBER(2,0) NOT NULL,
    current_balance    NUMBER(10,2) NOT NULL,
    prev_balance       NUMBER(10,2) NOT NULL,
    date_created       DATE NOT NULL,
    cust_id            NUMBER(10,0) NOT NULL,
    CONSTRAINT fk_customer
        FOREIGN KEY (cust_id)
            REFERENCES customers(cust_id)
) DISTRIBUTE BY REFERENCE (fk_customer);
 
CREATE MATERIALIZED VIEW phone_mv 
    DISTRIBUTE BY HASH (phone) 
    AS SELECT phone FROM accounts;

CREATE TABLE customers (
    cust_id            NUMBER(10,0) NOT NULL PRIMARY KEY,
    first_name         VARCHAR2(30) NOT NULL,
    last_name          VARCHAR2(30) NOT NULL,
    addr1              VARCHAR2(64),
    addr2              VARCHAR2(64),
    zipcode            VARCHAR2(5),
    account_id         NUMBER(10,0),
    member_since       DATE NOT NULL
) DISTRIBUTE BY HASH;
 
CREATE TABLE accounts (
    account_id         NUMBER(10,0) NOT NULL PRIMARY KEY,
    phone              VARCHAR2(15) NOT NULL,
    account_type       CHAR(1) NOT NULL,
    status             NUMBER(2,0) NOT NULL,
    current_balance    NUMBER(10,2) NOT NULL,
    prev_balance       NUMBER(10,2) NOT NULL,
    date_created       DATE NOT NULL,
    cust_id            NUMBER(10,0) NOT NULL UNIQUE
) DISTRIBUTE BY HASH;

CREATE TABLE call_records (
    call_id            NUMBER(10,0) NOT NULL PRIMARY KEY,
    caller             NUMBER(10,0) NOT NULL,
    receiver           NUMBER(10,0) NOT NULL,
    call_time          TIMESTAMP NOT NULL,
    code               INT NOT NULL
) DISTRIBUTE BY HASH;

SELECT accounts.account_id, customers.cust_id, call_records.code
    FROM accounts, customers, call_records
    WHERE customers.cust_id = call_records.caller
        AND call_records.code = ?
        AND customers.account_id = accounts.account_id;

CREATE MATERIALIZED VIEW customers_calls_mv 
    DISTRIBUTE BY HASH (caller) 
    AS SELECT caller, code FROM call_records;

CREATE MATERIALIZED VIEW customers_account_mv 
    DISTRIBUTE BY HASH (account_id) 
    AS SELECT account_id FROM customers;

For more information on materialized views, see "CREATE MATERIALIZED VIEW" in the Oracle TimesTen In-Memory Database SQL Reference.

Populating a table with the ttBulkCp utility

The ttBulkCp utility with the -i option enables you to load data from a file. This option uses standard INSERT SQL statements to load data into a specific table of a database. The ttBulkCp utility inserts each row into its corresponding element based on the distribution scheme of the table.

These sections describe the options for loading data into a database while using the ttBulkCp utility.

• Populate a table from a single location

• Populate a table from several locations

% ttBulkCp -i -connStr "DSN=database1;UID=terry" customers
 /mydir/customers_data.dmp
Enter password for 'terry':

/mydir/customers_data.dmp:
    1000 rows inserted
    1000 rows total

% ttGridAdmin dbStatus database1 -replicaset
Database database1 Replica Set status as of Thu Jan 11 13:17:29 PST 2018
 
RS DS Elem Host  Instance  Status Date/Time of Event  Message 
-- -- ---- ----- --------- ------ ------------------- ------- 
 1  1    1 host3 instance1 opened 2018-01-10 14:34:43         
    2    2 host4 instance1 opened 2018-01-10 14:34:43         
 2  1    3 host5 instance1 opened 2018-01-10 14:34:42         
    2    4 host6 instance1 opened 2018-01-10 14:34:42         
 3  1    5 host7 instance1 opened 2018-01-10 14:34:42         
    2    6 host8 instance1 opened 2018-01-10 14:34:42

% ttBulkCp -i -localOnly -connStr "DSN=database1;UID=terry" customers
 /mydir/customers_data.dmp
Enter password for 'terry':

/mydir/customers_data.dmp:
    339 rows inserted
    661 rows not inserted (ignored)
    1000 rows total

% ttBulkCp -i -localOnly -connStr "DSN=database1;UID=terry" customers
 /mydir/customers_data.dmp
Enter password for 'terry':

/mydir/customers_data.dmp:
    327 rows inserted
    673 rows not inserted (ignored)
    1000 rows total

% ttBulkCp -i -localOnly -connStr "DSN=database1;UID=terry" customers
 /mydir/customers_data.dmp
Enter password for 'terry':

/mydir/customers_data.dmp:
    334 rows inserted
    666 rows not inserted (ignored)
    1000 rows total

Populating a table with the ttLoadFromOracle built-in procedure

The ttLoadFromOracle built-in procedure enables you to load data from an Oracle database.

These sections describe how to load data from a Oracle database into a database while using the ttLoadFromOracle built-in procedure.

• Enable communication to an Oracle database

• Populate a table from several locations

% ttGridAdmin SQLNetImport /mydir/sqlnet.ora
SQLNet configuration file /mydir/sqlnet.ora imported

% ttGridAdmin TNSNamesImport /mydir/tnsnames.ora
TNSNames configuration file /mydir/tnsnames.ora imported

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each Instance......................OK
...
ttGridAdmin modelApply complete

Command> call ttLoadFromOracle('terry', 'customers', 'SELECT * FROM
 terry.customers');
< 1000 >
1 row found.

% ttGridAdmin dbStatus database1 -replicaset
Database database1 Replica Set status as of Thu Jan 11 13:17:29 PST 2018
 
RS DS Elem Host  Instance  Status Date/Time of Event  Message 
-- -- ---- ----- --------- ------ ------------------- ------- 
 1  1    1 host3 instance1 opened 2018-01-10 14:34:43         
    2    2 host4 instance1 opened 2018-01-10 14:34:43         
 2  1    3 host5 instance1 opened 2018-01-10 14:34:42         
    2    4 host6 instance1 opened 2018-01-10 14:34:42         
 3  1    5 host7 instance1 opened 2018-01-10 14:34:42         
    2    6 host8 instance1 opened 2018-01-10 14:34:42

Command> call ttLoadFromOracle('terry', 'customers', 'SELECT * FROM
 terry.customers', 4, 'localOnly=Y');
< 339 >
1 row found.

Command> call ttLoadFromOracle('terry', 'customers', 'SELECT * FROM
 terry.customers', 4, 'localOnly=Y');
< 327 >
1 row found.

Command> call ttLoadFromOracle('terry', 'customers', 'SELECT * FROM
 terry.customers', 4, 'localOnly=Y');
< 334 >
1 row found.

Modify the connection attributes in a database definition

To modify a database definition is to modify the assigned value of the connection attributes that a database definition supports. The types of connection attributes that a database definition supports and that can be modified after database creation are:

• First connection attributes

• PL/SQL first connection attributes

• TimesTen Server connection attributes

TimesTen Scaleout assigns the default value to any supported attribute not explicitly specified in the database definition. Attributes with the default value assigned can be modified by including the attribute in the database definition. Once you add or modify the attributes defined in a database definition and apply the changes to current version of the model, TimesTen Scaleout overwrites the configuration files of every data instance with the new attributes in the DSN associated with the database definition.

To modify the values assigned to the attributes supported by a database definition, perform these tasks:

1. If you don't have access to the file that you used to create (or modify) the database definition, export the contents of the database1 database definition to a file.

   % ttGridAdmin dbdefExport database1 /mydir/database1.dbdef
   

   Example 5-13 shows the contents of the exported file.

   Example 5-13 Exported database definition file

   # DbDef GUID ED157D81-D915-490B-AC80-353234E8516E Exported 2016-06-14 14:52:32
   [database1]
   Connections=2048
   DatabaseCharacterSet=AL32UTF8
   DataStore=/disk1/databases/database1
   Durability=0
   LogBufMB=1024
   LogDir=/disk2/logs
   PermSize=32768
   TempSize=4096
   

2. Modify the value of the PermSize attribute from 32768 and 49152 in the exported database definition file, as shown in Example 5-14.

   Example 5-14 Modified database definition file

   # DbDef GUID ED157D81-D915-490B-AC80-353234E8516E Exported 2016-06-14 14:52:32
   [database1]
   Connections=2048
   DatabaseCharacterSet=AL32UTF8
   DataStore=/disk1/databases/database1
   Durability=0
   LogBufMB=1024
   LogDir=/disk2/logs
   PermSize=49152
   TempSize=4096
   

3. Import the contents of the modified database definition file into the database1 database definition.

   % ttGridAdmin dbdefModify /mydir/database1.dbdef
   Database Definition DATABASE1 modified.
   

4. Apply the changes to the database1 database definition to the current version of the model.

   % ttGridAdmin modelApply
   ...
   Updating grid state...................................................OK
   Pushing new configuration files to each Instance......................OK
   ...
   ttGridAdmin modelApply complete
   

5. Unload the database1 database as shown in "Unloading a database from memory".

6. Restart the database1 database as shown in "Reloading a database into memory" to bring the changes you made to the database1 database definition into effect.

For a complete description of all the connection attributes, see "Connection Attributes" in the Oracle TimesTen In-Memory Database Reference.

For more information on the ttGridAdmin dbdefExport, ttGridAdmin dbdefModify, or ttGridAdmin modelApply command, see "Export a database definition (dbdefExport)", "Modify a database definition (dbdefModify)", or "Apply the latest version of the model (modelApply)", respectively, in the Oracle TimesTen In-Memory Database Reference.

Modify the connection attributes in a connectable

To modify a connectable is to modify the assigned value of the connection attributes that a connectable supports. The types of connection attributes that a connectable supports are:

• General connection attributes

• NLS general connection attributes

• PL/SQL connection attributes

• TimesTen Client connection attributes

TimesTen Scaleout assigns the default value to any supported attribute not explicitly specified in the connectable. Attributes with the default value assigned can be modified by including the attribute in the connectable. Once you add or modify the attributes defined in a connectable and apply the changes to current version of the model, TimesTen Scaleout overwrites the configuration files of every data instance with the new attributes in the DSN associated with the connectable.

To modify the values assigned to the attributes supported by a connectable, perform these tasks:

1. If you don't have access to the file that you used to create (or modify) the connectable, export the contents of the database1CS connectable to a file.

   % ttGridAdmin connectableExport database1CS /mydir/database1CS.connect
   

   Example 5-15 shows the contents of the exported file.

   Example 5-15 Exported connectable file

   # Connectable GUID E3175374-EC83-4826-A78C-8E3D21A0EFF6 Exported 2016-06-07
    12:40:47
   [database1CS]
   ConnectionCharacterSet=AL32UTF8
   UID=terry
   

2. Modify the value of the SQLQueryTimeout connection attribute to 300 in the exported connectable file, as shown in Example 5-16.

   Example 5-16 Modified connectable file

   # Connectable GUID E3175374-EC83-4826-A78C-8E3D21A0EFF6 Exported 2016-06-07
    12:40:47
   [database1CS]
   ConnectionCharacterSet=AL32UTF8
   UID=terry
   SQLQueryTimeout=300
   

3. Import the contents of the modified connectable file into the database1CS connectable.

   % ttGridAdmin connectableModify /mydir/database1CS.connect
   Connectable DATABASE1CS modified.
   

4. Apply the changes to the database1CS connectable to the current version of the model.

   % ttGridAdmin modelApply
   ...
   Updating grid state...................................................OK
   Pushing new configuration files to each Instance......................OK
   ...
   ttGridAdmin modelApply complete
   

For a complete description of all the connection attributes, see "Connection Attributes" in the Oracle TimesTen In-Memory Database Reference.

For more information on the ttGridAdmin connectableExport, ttGridAdmin connectableModify, or ttGridAdmin modelApply command, see "Export a connectable (connectableExport)", "Modify a connectable (connectableModify)", or "Apply the latest version of the model (modelApply)", respectively, in the Oracle TimesTen In-Memory Database Reference.