Go to main content
1/15
Contents
Title and Copyright Information
Preface
Audience
Related documents
Conventions
Documentation Accessibility
What's New
New features in release 18.1.4.1.0
New features in release 18.1.3.1.0
New features in release 18.1.2.1.0
New features in release 18.1.1.1.0
1
Connecting to a TimesTen Database
Connecting to TimesTen with ODBC and JDBC drivers
Connecting using TimesTen ODBC drivers
Connecting using the TimesTen JDBC driver and driver manager
Specifying Data Source Names to identify TimesTen databases
Overview of user and system DSNs
Defining DSNs for direct or client/server connections
Connection attributes for Data Manager DSNs or Server DSNs
Creating a DSN on Linux and UNIX for TimesTen Classic
Create a user or system odbc.ini file
Using environment variables in database path names
Defining Client and Server DSNs
Resolution path for a DSN
DSN examples for TimesTen Classic
Creating multiple DSNs to a single TimesTen Classic database
Specifying PL/SQL connection attributes in a DSN
Setting up a default DSN in TimesTen Classic
odbc.ini file entry descriptions
ODBC Data Sources
DSN specification
odbc.ini file example
Connecting to a database using a connection string
Using a connection string in TimesTen Scaleout
Using a connection string in TimesTen Classic
2
Managing TimesTen Databases
Opening and closing the database for user connections
Loading and unloading the database from memory
Loading a database into memory for TimesTen Classic
Unloading a database from memory for TimesTen Classic
Specifying a RAM policy
Changes to RAM policy after automatic recovery fails
Preventing an automatic reload of the database after failure
Disconnecting from a database
Disconnecting from a database in TimesTen Scaleout
Disconnecting from a database in TimesTen Classic
Specifying the memory region sizes of a database
Estimating and modifying the memory region sizes for the database
Monitoring PermSize and TempSize attributes
Reducing database size for TimesTen Classic
Receiving out-of-memory warnings
Storage provisioning for TimesTen
Storage provisioning for transaction log files
Bulk copy data using the ttBulkCp utility
Copying data from a TimesTen table to an ASCII file
Copying data from an ASCII file into a TimesTen table
Running ttBulkCp with the -i option
Running ttBulkCp with the -directLoad option on TimesTen Classic
Thread programming with TimesTen
Defragmenting TimesTen databases
Offline defragmentation of a TimesTen Scaleout database
Offline defragmentation of a TimesTen Classic database
Online defragmentation of a TimesTen Classic database
Online defragmentation of TimesTen Classic databases in an active standby pair replication scheme
Online defragmentation of TimesTen Classic databases in a non active standby pair replication scheme
Verifying if your database is a single-instance or distributed database
3
Working with the TimesTen Client and Server
Overview of the TimesTen Client/Server
Restrictions on client/server communication
Communication protocols for Client/Server communication
TCP/IP Communication
Shared memory communication
UNIX domain socket communication
Configuring TimesTen Client and Server
Overview of TimesTen Client/Server configuration
Installing and configuring for client/server connections
Configuring Client/Server of the same TimesTen release
Configuring cross-release TimesTen Client/Server
Defining Server DSNs for TimesTen Server on a Linux or UNIX system
Defining a logical server name
Creating and configuring a logical server name on Windows
Creating and configuring a logical server name on Linux and UNIX
Creating Client DSNs on a TimesTen Client system
Creating and configuring Client DSNs on Windows
Creating and configuring Client DSNs on Linux and UNIX
Using automatic client failover
Managing automatic client failover in TimesTen Scaleout
Managing automatic client failover in TimesTen Classic using an active standby pair
Managing generic automatic client failover in TimesTen Classic
Configuring automatic client failover for TimesTen Classic
Configuring TCP keep-alive parameters
Running the TimesTen Server
Server informational messages
Accessing a remote database on Linux and UNIX
Testing connections
4
Working with the TimesTen Daemon
Starting and stopping the daemon
Installing the setuproot script
Manually starting and stopping the daemon
Shutting down a TimesTen application
Shutting down an application within TimesTen Classic
Shutting down applications within TimesTen Scaleout
Managing TimesTen daemon attributes
Determining the daemon listening address
Listening on IPv6
Managing subdaemons
Configuring a range for shared memory keys
Managing TimesTen Client/Server attributes
Modifying the TimesTen Server attributes
Controlling the TimesTen Server
Prespawning TimesTen Server processes in TimesTen Classic
Specifying multiple connections to the TimesTen Server
Configuring the maximum number of client connections per child server process
Configuring connection distribution among child server processes
Configuring the thread stack size of the child server processes
Using shared memory for Client/Server IPC
Managing the size of the shared memory segment
Changing the size of the shared memory segment
Controlling the TimesTen Server log messages
Error, warning, and informational messages
Critical event logging
5
Globalization Support
Overview of globalization support features
Choosing a database character set
Character sets and languages
Client operating system and application compatibility
Performance and storage implications
Character sets and replication
Length semantics and data storage
Connection character set
Linguistic sorts
Monolingual linguistic sorts
Multilingual linguistic sorts
Case-insensitive and accent-insensitive linguistic sorts
Performing a linguistic sort
Using linguistic indexes
SQL string and character functions
Setting globalization support attributes
Globalization support during migration
6
Using the ttIsql Utility
Batch mode vs. interactive mode
Defining default settings with the TTISQL environment variable
Customizing the ttIsql command prompt
Using the ttIsql online help
Using the ttIsql 'editline' feature for Linux and UNIX only
Emacs binding
vi binding
Using the ttIsql command history
Saving and clearing the ttIsql command history
Using the ttIsql edit command
Changing the default text editor for the ttIsql edit command
Working with character sets
Displaying database structure information
Using the ttIsql describe command
Using the ttIsql cachegroups command
Using the ttIsql dssize command
Using the ttIsql tablesize command
Using the ttIsql monitor command
Listing database objects by object type
Viewing and setting connection attributes
Working with transactions
Working with prepared and parameterized SQL statements
Using, declaring, and setting variables
Declaring and setting bind variables
Automatically creating bind variables for retrieved columns
Creating and executing PL/SQL blocks
Passing data from PL/SQL using OUT parameters
Conditional control with the IF-THEN-ELSE command construct
Loading data from an Oracle database into a TimesTen table
Use ttIsql to create a table and load SQL query results
Use TimesTen built-in procedures to recommend a table and load SQL query results
Cancel a parallel load operation
Viewing and changing query optimizer plans
Using the showplan command
Viewing commands and explain plans from the SQL command cache
View commands in the SQL command cache
Display query plan for statement in SQL command cache
Managing ODBC functions
Canceling ODBC functions
Timing ODBC function calls
Error recovery with WHENEVER SQLERROR
7
Transaction Management
Transaction overview
Transaction implicit commit behavior
Transaction autocommit behavior
TimesTen DDL commit behavior
Ensuring ACID semantics
Transaction atomicity, consistency, and isolation
Transaction consistency and durability
Concurrency control through isolation and locking
Transaction isolation levels
Locking granularities
Setting wait time for acquiring a lock
Checkpoint operations
Purpose of checkpoints
Usage of checkpoint files
Types of checkpoints
Fuzzy or non-blocking checkpoints
Blocking checkpoints
Setting and managing checkpoints
Programmatically performing a checkpoint in TimesTen Classic
Configuring or turning off background checkpointing
Displaying checkpoint history and status
Setting the checkpoint rate
Setting the number of checkpoint file read threads
Transaction logging
Managing transaction log buffers and files
Using NFS-mounted systems for checkpoint and transaction log files
Monitoring accumulation of transaction log files
Log holds by TimesTen components or operations
Purging transaction log files
Monitoring log holds and log file accumulation
Durability options
Durability for TimesTen Scaleout
Guaranteed durability for TimesTen Scaleout
Non-durable distributed transactions for TimesTen Scaleout
Durability for TimesTen Classic
Guaranteed durability for TimesTen Classic
Nondurable transactions for TimesTen Classic
Transaction reclaim operations
About reclaim operations
Configuring the commit buffer for reclaim operations
Recovery with checkpoint and transaction log files
8
Working with Data in a TimesTen Database
Database overview
Database objects
Database users and owners
Understanding tables
Overview of tables
Column overview
Inline and out-of-line columns
Default column values
Table names
Table access
Primary keys, foreign keys and unique indexes
Tables in TimesTen Scaleout
System tables
Working with tables
Creating a table
Dropping a table
Estimating table size
Implementing aging in your tables
Usage-based aging
Time-based aging
Aging and foreign keys
Scheduling when aging starts
Aging and replication
Understanding views
Creating a view
The SELECT query in the CREATE VIEW statement
Dropping a view
Restrictions on views and detail tables
Understanding materialized views
Overview of materialized views
Working with materialized views
Creating a materialized view
Dropping a materialized view
Restrictions on materialized views and detail tables
Performance implications of materialized views
Understanding indexes
Overview of index types
Creating an index
Altering an index
Dropping an index
Estimating index size
Using the Index Advisor to recommend indexes
Prepare for executing the Index Advisor
Capture the data used for generating index recommendations
Retrieve index recommendations and data collection information
Drop data collected for the index advisor and finalize results
Example using Index Advisor built-in procedures
Understanding rows
Inserting rows
Deleting rows
Understanding synonyms
Creating synonyms
Dropping synonyms
Synonyms may cause invalidation or recompilation of SQL queries
Understanding system views
9
The TimesTen Query Optimizer
When optimization occurs
Viewing SQL statements stored in the SQL command cache
Managing performance and troubleshooting commands
Displaying commands stored in the SQL command cache
Viewing SQL query plans
Viewing a query plan from the system PLAN table
Instruct TimesTen to store the plan in the system PLAN table
Reading query plan from the PLAN table
Describing the PLAN table columns
Viewing query plans associated with commands stored in the SQL command cache
Modifying plan generation
Why modify an execution plan?
How hints can influence an execution plan
Use optimizer hints to modify the execution plan
Apply statement level optimizer hints for a SQL statement
Apply transaction level optimizer hints for a transaction
Apply connection level optimizer hints for a TimesTen connection
10
TimesTen Database Performance Tuning
System and database tuning
Provide enough memory
Avoid paging of the memory used by the database
Size your database correctly
Calculate shared memory size for PL/SQL runtime
Set appropriate limit on the number of open files
Configure log buffer and log file size parameters
Avoid connection overhead for TimesTen Classic databases
Load the database into RAM when duplicating
Prevent reloading of the database after automatic recovery fails
Reduce contention
Consider special options for maintenance
Check your driver
Enable tracing only as needed
Use metrics to evaluate performance
Migrate data with character set conversions
Use TimesTen native integer data types if appropriate
Configure the checkpoint files and transaction log files to be on a different physical device
Client/Server tuning
Diagnose Client/Server performance
Work locally when possible
Choose timeout connection attributes for your client
Choose a lock wait timeout interval
Choose the best method of locking
Choose an appropriate lock level
Choose an appropriate isolation level
Use shared memory segment as IPC when client and server are on the same system
Enable prefetch close for read-only transactions
Use a connection handle when calling SQLTransact
Enable multi-threaded mode to handle concurrent connections
SQL tuning
Tune statements and use indexes
Collect and evaluate sampling of execution times for SQL statements
Select the type of index appropriately
Size hash indexes appropriately
Use foreign key constraint appropriately
Compute exact or estimated statistics
Update table statistics for large tables in parallel
Create script to regenerate current table statistics
Control the invalidation of commands in the SQL command cache
Avoid ALTER TABLE
Avoid nested queries
Prepare statements in advance
Avoid unnecessary prepare operations
Store data efficiently with column-based compression of tables
Control read optimization during concurrent write operations
Choose SQL and PL/SQL timeout values
Materialized view tuning
Limit number of join rows
Use indexes on join columns
Avoid unnecessary updates
Avoid changes to the inner table of an outer join
Limit number of columns in a view table
Transaction tuning
Locate checkpoint and transaction log files on separate physical device
Size transactions appropriately
Use durable commits appropriately
Avoid manual checkpoints
Turn off autocommit mode
Avoid transaction roll back
Avoid large DELETE statements
Avoid DELETE FROM statements
Consider using the DELETE FIRST clause
Increase the commit buffer cache size
Recovery tuning
Set RecoveryThreads
Set CkptReadThreads
Scaling for multiple CPUs
Run the demo applications as a prototype
Limit database-intensive connections per CPU
Use read operations when available
Limit prepares, re-prepares and connects
Allow indexes to be rebuilt in parallel during recovery
Use private commands
XLA tuning
Increase transaction log buffer size when using XLA
Prefetch multiple update records
Acknowledge XLA updates
Cache and replication tuning
Index
Scripting on this page enhances content navigation, but does not change the content in any way.