TimesTen quickly loads tables and data in an Oracle database

A very handy data import tool Tttableschemafromoraqueryget and Ttloadfromoracle are added to the TimesTen 11.2.2 release.

You can simply load the table structure and data quickly into the memory library without using the cache group. However, this method can not achieve automatic data refresh, in order to maintain the consistency of data, you must manually to execute the Refresh command.

Introduction to Usage:

1, configure the Tnsnames.ora file, add a connection string.

Once configured, you can test the connectivity to ensure there are no configuration errors.

2, using the TimesTen command to connect

Ttisql "DSN=TT_1122;UID=SCOTT;PWD=TRIGER;ORACLEPWD=TRIGER;ORACLENETSERVICENAME=ORCL"

The red is the TT user name, but it must be guaranteed to be a user of the same name that is present in Oracle. The yellow is the password of the Scott user in TT, the green is the password of Scott in Oracle, ORCL is the name of the connection string configured in the Tnsnames.ora file.

3. Build table Structure

Call Tttableschemafromoraqueryget (' Scott ', ' emp ', ' SELECT * from Scott.emp ');

This command generates a build statement of the EMP table structure that needs to be executed manually in TT.

4. Import data

Call Ttloadfromoracle (' Scott ', ' emp ', ' SELECT * from Scott.emp ');

This loads all eligible data in the query into TT and returns a data after the command succeeds, that is, the total number of data bars that were successfully inserted.

5. Data Refresh

It's good to repeat the command call ttloadfromoracle (' Scott ', ' emp ', ' SELECT * from Scott.emp ');

---------details can be found in the official website

http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21633/ttisql.htm#TTOPR752

-------

Use TimesTen built-in procedures to recommend a table and load SQL query results

While the Createandloadfromoraquery command automatically performs all of the tasks for creating the TimesTen table and lo Ading The result set from Oracle to database into it, the following two built-in procedures separate the same Functionali Ty into the following two steps:

The Tttableschemafromoraqueryget built-in procedure evaluates SQL query and generates the CREATE TABLE SQL statement T Hat can choose to execute. In order to execute this statement, the user should have all required privileges to execute the query on the Oracle Databa Se. This is enables to view the table structure without execution. However, it does require to execute the recommended CREATE TABLE statement yourself.

The ttloadfromoracle built-in procedure executes SQL query on the back-end Oracle database and then loads the result s Et into the TimesTen table. It requires the TimesTen table name where the results are loaded, the Oracle Database SQL SELECT statement to obtain the R equired rows, and the number of parallel threads that you are would like to is used in parallel while loading the table with th is result set.

The call returns a single number indicating the number of rows loaded. Any subsequent calls append the retrieved rows to the table.

Note:see "Tttableschemafromoraqueryget" and "ttloadfromoracle" in the Oracle timesten in-memory Database Reference for Fu ll details on syntax, requirements, restrictions, and required privileges.

The following example connects providing the DSN, user name, password for the user in TimesTen, the password for a user wi Th the same name on the Oracle database, and the Oraclenetservicename for the Oracle database instance. Then, it calls the Tttableschemafromoraqueryget built-in procedure to evaluate the SELECT statement and return a recommend Ed CREATE Table statement for the Employees table. Finally, the example calls the Ttloadfromoracle built-in procedure to load the Employees table with the result set from th e Oracle database. The load is performed in parallel over four threads, and which is the default.

Note:if Autocommit is set to OFF, then the user must either commit or rollback after manually the table.

$ ttisql "Dsn=mydb;uid=oratt;pwd=timesten;  Oraclepwd=oracle;oraclenetservicename=inst1 "Copyright (c) 1996-2013, Oracle.
All rights reserved. Type?
or ' help ' for help, type ' exit ' to quit Ttisql.
Connect "Dsn=mydb;uid=oratt;pwd=timesten;
Oraclepwd=oracle;oraclenetservicename=inst1 "; Connection Successful:dsn=mydb;
Uid=oratt;
Datastore=/timesten/install/info/demodatastore/mydb; Databasecharacterset=us7ascii;
Connectioncharacterset=us7ascii; driver=/timesten/install/lib/libtten.so; permsize=40;
tempsize=32;
Typemode=0;oraclenetservicename=inst1;

(Default setting Autocommit=1)
Command> call Tttableschemafromoraqueryget (' hr ', ' employees ', ' SELECT * from Hr.employees '); < CREATE TABLE "HR". " EMPLOYEES "(" employee_id "number (6,0) NOT NULL," First_Name "VARCHAR2 (Byte)," Last_Name "VARCHAR2 (byte) is not NULL,"  EMAIL "Varchar2" (byte) is not NULL, "Phone_number" VARCHAR2 (Byte), "Hire_date" DATE is not NULL, "job_id" VARCHAR2 (Byte) Not NULL, "SALARY" number (8,2), "commission_pct" NumbeR (2,2), "manager_id" number (6,0), "department_id" number (4,0)) > 1 row found. Command> call Ttloadfromoracle (' hr ', ' EMPLOYEES ', ' SELECT * from HR.
EMPLOYEES ');

< > 1 row found.
Command> SELECT * from Hr.employees;  <, Steven, King, sking, 515.123.4567, 2003-06-17 00:00:00, Ad_pres, 24000, <null>, <null>, > <  Neena, Kochhar, Nkochhar, 515.123.4568, 2005-09-21 00:00:00, AD_VP, 17000, <null>, > < 205, Shelley, Higgins, Shiggins, 515.123.8080, 2002-06-07 00:00:00, Ac_mgr, 12008, <null>, N. > < 206, would

 iam, Gietz, Wgietz, 515.123.8181, 2002-06-07 00:00:00, Ac_account, 8300, &LT;NULL&GT, 205, >-Rows found.

The following example creates a table on the Oracle database where employee_id are a column with a PRIMARY KEY constraints And email is a column with a UNIQUE constraint.

sql> CREATE TABLE Employees    (employee_id number    (6) PRIMARY KEY    , first_name     VARCHAR2    , Last_Name      VARCHAR2 NOT null    , email          VARCHAR2 is not null UNIQUE    , Phone_number   VARCHAR2 (20) c10/>, hire_date      date not null,    job_id         VARCHAR2 (a) not NULL,    salary number         (8,2)    , commission_pct number (2,2)    , manager_id number     (6)    , department_id number  (4)    );

Table created.

Then, the following Tttableschemafromoraqueryget built-in procedure evaluates the SQL query and generates a CREATE TABLE S QL statement. The suggested CREATE TABLE SQL statement the PRIMARY KEY and UNIQUE constraints are not carried Oracle database. nullability constraints are carried over from the Oracle database. This is also applies to the Createandloadfromoraquery command.

Command> call Tttableschemafromoraqueryget (' Oratt ', ' Employees ', ' select * from Oratt.employees ');
< CREATE TABLE "Oratt". EMPLOYEES "( 
" employee_id "number (6,0) not NULL,
" first_name "VARCHAR2 (byte),
" Last_Name "VARCHAR2 (25 byte not null, '
EMAIL ' varchar2 (byte) not null,
' Phone_number ' varchar2 (byte),
' hire_date ' DATE not NU LL,
"job_id" VARCHAR2 (byte) not NULL,
"SALARY" number (8,2),
"commission_pct" number (2,2),
" manager_id "Number (6,0),
" department_id "number (4,0)
 ) >
1 row found.