oracle 記憶體資料庫 TimesTen試用筆記（aix5.3）

最近公司打算在下一代計費系統裡面使用記憶體資料庫的技術，所以作為公司的設計人員之一的我，在oracle網上下了個TimesTen，安裝在我們的測試伺服器上面aix5，這篇短文主要是我操作資料庫的一個筆記，下一篇打算髮一個c程式的例子和我測試的結果，後續可能還會使用TimesTen cache of oracle這個產品，陸續也會發一下這方面的筆記上來，希望對有這方面需求的朋友有所協助。

1 建立.odbc.ini檔案在登陸目錄下
[ODBC Data Sources]
mytimesten=TimesTen 6.0 Driver

[DemoDataStore]
DataStore=/home/fee/TimesTen6/DemoDataStore
DurableCommits=0
PermSize=16

2 串連資料來源，並建立表
[31 machine]/home/fee>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=fee;DataStore=/home/fee/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 FEE.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 with *)

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 FEE.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 FEE.ORDERS (
        ORDER_NUMBER INTEGER not null,
        CUST_NUMBER  INTEGER not null,
        PROD_NAME    CHAR(10) not null,
        ORDER_DATE   DATE not null);

create table FEE.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 通過檔案匯入資料
[31 machine]/home/fee/TimesTen6/DemoDataStore>ls -al
total 32
drwxrwxrwx   2 fee      dba             256 Oct 24 17:25 ./
drwxr-xr-x   3 fee      dba            4096 Oct 24 17:25 ../
-r--r--r--   1 fee      dba            1565 Oct 24 17:24 customer.dat
-r--r--r--   1 fee      dba            1591 Oct 24 17:24 orders.dat
-r--r--r--   1 fee      dba             967 Oct 24 17:24 ref_products.dat
[31 machine]/home/fee/TimesTen6/DemoDataStore>pwd
/home/fee/TimesTen6/DemoDataStore
[31 machine]/home/fee/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, Minneapolis, MN 55417"
2435,"Juanita","Dawes","733 Valdosta Avenue, Baton Rouge, LA 70816"
1224,"Abdul","Aziz","6793 Bird Avenue, San Jose, CA 95126"
3611,"Katherine","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/fee/TimesTen6/DemoDataStore>ttBulkCp -i -d warn DSN=DemoDataStore fee.customer customer.dat

customer.dat:
    25 rows inserted
    25 rows total

[31 machine]/home/fee/TimesTen6/DemoDataStore>ttBulkCp -i -d warn DSN=DemoDataStore fee.orders orders.dat

orders.dat:
    43 rows inserted
    43 rows total

[31 machine]/home/fee/TimesTen6/DemoDataStore>ttBulkCp -i -d warn DSN=DemoDataStore fee.ref_products ref_products.dat

ref_products.dat:
    15 rows inserted
    15 rows total
   
4 查詢匯入的資料   
[31 machine]/home/fee/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=fee;DataStore=/home/fee/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, Minneapolis, MN 55417 >
< 2435, Juanita     , Dawes       , 733 Valdosta Avenue, Baton Rouge, LA 70816 >
< 1224, Abdul       , Aziz        , 6793 Bird Avenue, San Jose, CA 95126 >
< 3611, Katherine   , 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操作
Command> insert into customer
> values(1365,'Josephine','Rogers','2100 Augustine Drive, Santa Clara, CA 95054');
1 row inserted.

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

6 備份資料庫
[31 machine]/home/fee/TimesTen6>mkdir backup

[31 machine]/home/fee/TimesTen6>ls
DemoDataStore/       DemoDataStore.ds1    DemoDataStore.res0   DemoDataStore.res2
DemoDataStore.ds0    DemoDataStore.log0   DemoDataStore.res1   backup/

[31 machine]/home/fee/TimesTen6>ttBackup -dir /home/fee/TimesTen6/backup "DSN=DemoDataStore";
Backup started ...
Backup complete

[31 machine]/home/fee/TimesTen6>cd backup

[31 machine]/home/fee/TimesTen6/backup>ls
DemoDataStore.0.bac    DemoDataStore.0.bac0   DemoDataStore.sta

[31 machine]/home/fee/TimesTen6/backup>ll
total 29592
-rw-------   1 fee      dba        14549816 Oct 24 17:44 DemoDataStore.0.bac
-rw-------   1 fee      dba          589824 Oct 24 17:44 DemoDataStore.0.bac0
-rw-------   1 fee      dba             688 Oct 24 17:44 DemoDataStore.sta

7 刪除和恢複資料庫
[31 machine]/home/fee/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/fee/TimesTen6/DemoDataStore
There are no connections to the data store
Replication policy  : Manual
Cache agent policy : Manual
------------------------------------------------------------------------
End of report

[31 machine]/home/fee/TimesTen6/backup>ttDestroy /home/fee/TimesTen6/DemoDataStore

[31 machine]/home/fee/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/fee/TimesTen6/DemoDataStore>ttRestore -dir /home/fee/TimesTen6/backup "DSN=DemoDataStore";
Restore started ...
Restore complete

[31 machine]/home/fee/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/fee/TimesTen6/DemoDataStore
There are no connections to the data store
Replication policy  : Manual
Cache agent policy : Manual
------------------------------------------------------------------------
End of report