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