Oracle memory database TimesTen trial notes (aix5.3)

Recently, the company intends to use the memory database technology in the next generation of billing systems. Therefore, as one of the company's designers, I posted TimesTen on Oracle, installed on our test server aix5. This short article is mainly a note for my operations on the database. The next article will send an example of a C program and my test results, the TimesTen cache of Oracle product may be used in the future, and some notes will be sent in succession, hoping to help those who need it.

1. Create an ODBC. ini file in the login directory.
[ODBC data sources]
Mytimesten = TimesTen 6.0 driver

[Demodatastore]
Datastore =/home/logs/timesten6/demodatastore
Durablecommits = 0
Permsize = 16

2. Connect to the data source and create a table
[31 machine]/home/workshop> ttisql

Copyright (c) 1996-2006, Oracle. All rights reserved.
Type? Or "help" for help, type "exit" to quit ttisql.
All commands must end with a semicolon character.

Command> connect "DSN = demodatastore ";
Im002: data source name not found and no default driver specified
The command failed.
Command> connect "DSN = demodatastore ";
Connection successful: DSN = demodatastore; uid = Hangzhou; datastore =/home/logs/timesten6/demodatastore; permsize = 16;
(Default setting autocommit = 1)

Command> Create Table customer
> (Cust_number integer not null primary key,
> First_name char (12) not null,
> Last_name char (12) not null,
> Address varchar (100) not null );

Command> describe customer;

Table metadata. Customer:
Columns:
* Cust_number integer not null
First_name char (12) not null
Last_name char (12) not null
Address varchar (100) inline not null

1 Table found.
(Primary key columns are indicated *)

Command> Create Table ref_products
> (Prod_number char (10) Not null primary key,
> Prod_name varchar (100) not null,
> Price decimal (6, 2) not null );

Command> Create Table orders
> (Order_number integer not null,
> Cust_number integer not null,
> Prod_name char (10) Not null,
> Order_date date not null );

Command> host ttschema demodatastore;
Create Table rule. Customer (
Cust_number integer not null,
First_name char (12) not null,
Last_name char (12) not null,
Address varchar (100) inline not null,
Primary Key (cust_number ));

Create Table orders. Orders (
Order_number integer not null,
Cust_number integer not null,
Prod_name char (10) Not null,
Order_date date not null );

Create Table rule. ref_products (
Prod_number char (10) Not null,
Prod_name varchar (100) inline not null,
Price decimal (6, 2) not null,
Primary Key (prod_number ));

Command> exit
Disconnecting...
Done.

3. import data through files
[31 machine]/home/logs/timesten6/demodatastore> ls-Al
Total 32
Drwxrwxrwx 2 unzip DBA 256 Oct 24 ./
Drwxr-XR-x 3 sort DBA 4096 Oct 24 ../
-R -- 1 Jun DBA 1565 Oct 24 customer. dat
-R -- 1 Jun DBA 1591 Oct 24 Orders. dat
-R -- 1 Jun DBA 967 Oct 24 ref_products.dat
[31 machine]/home/logs/timesten6/demodatastore> pwd
/Home/logs/timesten6/demodatastore
[31 machine]/home/logs/timesten6/demodatastore> CAT customer. dat
3700, "Peter", "Burchard", "882 Osborne Avenue, Boston, MA 02122"
1121, "Saul", "Mendoza", "721 stardust Street, Mountain View, CA 94043"
1278, "Mary", "Behr", "2233 Emerson Road, Vancouver, WA 98663"
1868, "Paul", "tu", "308 Bowman court, Palo Alto, CA 94309"
3645, "John", "Silva", "3329 taffy Lane, Atlanta, Ga 30314"
1935, "Sandra", "Lao", "115 Spangler Avenue, San Jose, CA 95112"
1002, "Marco", "Mueller", "40 East 5th Avenue, New York, NY 10009"
2364, "Karen", "Johnson", "3971 Hill Road, Chicago, il 60608"
2655, "Linda", "Garcia", "7599 Clark road, Denver, CO 80210"
1077, "Gautam", "mudunuri", "16 Welsley Avenue, Fremont, CA 94555"
3864, "Ruth", "Silver", "88 West 65th Street, New York, NY 10009"
1010, "Fatima", "Borba", "6868 Bascom Avenue, San Jose, CA 95128"
2300, "Pavel", "Popov", "233 Loredo Street, Dallas, TX 75210"
1001, "Steven", "McPhee", "72 Vine Street, San Jose, CA 95125"
3525, "Anthony", "Bianchi", "122 Fuller Avenue, Patchogue, NY 11772"
2826, "Mary", "Anderson", "6363 Bjorn Road, minneapois, Mn 55417"
2435, "Juanita", "Dawes", "733 Valdosta Avenue, Baton Rouge, la 70816"
1224, "Abdul", "Aziz", "6793 bird Avenue, San Jose, CA 95126"
3611, "Catherine", "McKenzie", "54 East 21st Avenue, New York, NY 10009"
1900, "Patricia", "Levesque", "658 Aristotle Road, Palo Alto, CA 94305"
3290, "Paula", "Rossi", "21 West 54th Street, New York, NY 10009"
1665, "David", "Singh", "4001 West hedding, San Jose, CA 95216"
3098, "Cynthia", "Stewart", "333 East Palm Street, Miami, FL 33150"
1133, "Kerri", "Haas", "68 East San Fernando, San Jose, CA 95113"
2555, "Bo", "Smith", "124 North 1st Street, Dallas, TX 75210"
[31 machine]/home/logs/timesten6/demodatastore> ttbulkcp-I-d warn DSN = demodatastore metadata. Customer customer. dat

Customer. DAT:
25 rows inserted
25 rows total

[31 machine]/home/logs/timesten6/demodatastore> ttbulkcp-I-d warn DSN = demodatastore limits. Orders orders. dat

Orders. DAT:
43 rows inserted
43 rows total

[31 machine]/home/logs/timesten6/demodatastore> ttbulkcp-I-d warn DSN = demodatastore metadata. ref_products ref_products.dat

Ref_products.dat:
15 rows inserted
15 rows total

4. query imported data
[31 machine]/home/logs/timesten6/demodatastore> ttisql

Copyright (c) 1996-2006, Oracle. All rights reserved.
Type? Or "help" for help, type "exit" to quit ttisql.
All commands must end with a semicolon character.

Command> connect "DSN = demodatastore ";
Connection successful: DSN = demodatastore; uid = Hangzhou; datastore =/home/logs/timesten6/demodatastore; permsize = 16;
(Default setting autocommit = 1)

Command> select * from customer;
<3700, Peter, Burchard, 882 Osborne Avenue, Boston, MA 02122>
<1121, Saul, Mendoza, 721 stardust Street, Mountain View, CA 94043>
<1278, Mary, Behr, 2233 Emerson Road, Vancouver, WA 98663>
<1868, Paul, tu, 308 Bowman court, Palo Alto, CA 94309>
<3645, John, Silva, 3329 taffy Lane, Atlanta, Ga 30314>
<1935, Sandra, Lao, 115 Spangler Avenue, San Jose, CA 95112>
<1002, Marco, Mueller, 40 East 5th Avenue, New York, NY 10009>
<2364, Karen, Johnson, 3971 Hill Road, Chicago, il 60608>
<2655, Linda, Garcia, 7599 Clark road, Denver, CO 80210>
<1077, Gautam, mudunuri, 16 Welsley Avenue, Fremont, CA 94555>
<3864, Ruth, silver, 88 West 65th Street, New York, NY 10009>
<1010, Fatima, Borba, 6868 Bascom Avenue, San Jose, CA 95128>
<2300, Pavel, Popov, 233 Loredo Street, Dallas, TX 75210>
<1001, Steven, McPhee, 72 Vine Street, San Jose, CA 95125>
<3525, Anthony, Bianchi, 122 Fuller Avenue, Patchogue, NY 11772>
<2826, Mary, Anderson, 6363 Bjorn Road, minneapois, Mn 55417>
<2435, Juanita, Dawes, 733 Valdosta Avenue, Baton Rouge, la 70816>
<1224, Abdul, Aziz, 6793 bird Avenue, San Jose, CA 95126>
<3611, Catherine, McKenzie, 54 East 21st Avenue, New York, NY 10009>
<1900, Patricia, Levesque, 658 Aristotle Road, Palo Alto, CA 94305>
<3290, Paula, Rossi, 21 West 54th Street, New York, NY 10009>
<1665, David, Singh, 4001 West hedding, San Jose, CA 95216>
<3098, Cynthia, Stewart, 333 East Palm Street, Miami, FL 33150>
<1133, Kerri, Haas, 68 East San Fernando, San Jose, CA 95113>
<2555, Bo, Smith, 124 North 1st Street, Dallas, TX 75210>
25 rows found.

5. SQL operations
Command> insert into customer
> Values (1365, 'phpine', 'rogers', '1970 Augustine drive, Santa Clara, CA 100 ');
1 row inserted.

Command> select * from customer where cust_number = 1365;
<1365, Joseph phine, Rogers, 2100 Augustine drive, Santa Clara, CA 95054>
1 row found.

6. Back up the database
[31 machine]/home/logs/timesten6> mkdir backup

[31 machine]/home/logs/timesten6> ls
Demodatastore/demodatastore. ds1 demodatastore. res0 demodatastore. RES2
Demodatastore. ds0 demodatastore. log0 demodatastore. RES1 backup/

[31 machine]/home/logs/timesten6> ttbackup-DIR/home/logs/timesten6/backup "DSN = demodatastore ";
Backup started...
Backup complete

[31 machine]/home/logs/timesten6> Cd backup

[31 machine]/home/logs/timesten6/backup> ls
Demodatastore.0.bac demodatastore.0.bac0 demodatastore. Sta

[31 machine]/home/logs/timesten6/backup> LL
Total 29592
-RW ------- 1 Jun DBA 14549816 Oct 24 demodatastore.0.bac
-RW ------- 1 Jun DBA 589824 Oct 24 demodatastore.0.bac0
-RW ------- 1 Jun DBA 688 Oct 24 demodatastore. Sta

7. Delete and restore a database
[31 machine]/home/logs/timesten6/backup> ttstatus
TimesTen status report as of Tue Oct 24 17:47:25 2006

Daemon PID 782504 port 16001 instance timesten6
TimesTen server PID 774300 started on port 16003
No TimesTen webserver running

------------------------------------------------------------------------
Data Store/home/logs/timesten6/demodatastore
There are no connections to the Data Store
Replication policy: Manual
Cache Agent Policy: Manual
------------------------------------------------------------------------
End of report

[31 machine]/home/logs/timesten6/backup> ttdestroy/home/logs/timesten6/demodatastore

[31 machine]/home/logs/timesten6/backup> ttstatus
TimesTen status report as of Tue Oct 24 17:51:59 2006

Daemon PID 782504 port 16001 instance timesten6
TimesTen server PID 774300 started on port 16003
No TimesTen webserver running

------------------------------------------------------------------------
End of report

[31 machine]/home/logs/timesten6/demodatastore> ttrestore-DIR/home/logs/timesten6/backup "DSN = demodatastore ";
Restore started...
Restore complete

[31 machine]/home/logs/timesten6> ttstatus
TimesTen status report as of Tue Oct 24 17:53:46 2006

Daemon PID 782504 port 16001 instance timesten6
TimesTen server PID 774300 started on port 16003
No TimesTen webserver running

------------------------------------------------------------------------
Data Store/home/logs/timesten6/demodatastore
There are no connections to the Data Store
Replication policy: Manual
Cache Agent Policy: Manual
------------------------------------------------------------------------
End of report