TimesTen import from oracle utility
The other day, someone wanted to place some oracle tables into TimesTen DB for some testing, this is a great quick way:
1) Make sure you have a connection to your Oracle database in the TimesTen tnsfile, so I have the following in my tnsnames.ora in the following directory (/u01/app/oracle/product/TimesTen/tt1122/network/admin/samples) :
MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX) (PORT=1521))
(CONNECT_DATA =
(SERVICE_NAME = live)
)
)
2) Set your environment
TNS_ADMIN=/u01/app/oracle/product/TimesTen/tt1122/network/admin/samples
Export TNS_ADMIN
cd /u01/app/oracle/product/TimesTen/tt1122/ttoracle_home/instantclient_11_2
./sqlplus read/only@mydb
3) Create a directory to place the files that will be generated for the import, and cd to the directory.
4) Run the following command:
ttImportFromOracle –oraConn user/oracle@myoracle –tables my_small_tab –typeMap 2,1 –prefix tm1
myoracle = ip:port/service
prefix = prefix for files generated.
5) Now we shall add the MYDB to the TimesTen settings:
cd /u01/app/oracle/product/TimesTen/tt1122/info
vi sys.odbc.ini
Add the following to your Times Ten Store ours is called TT_AGGR_STORE
[TT_AGGR_STORE]
Driver=/u01/app/oracle/product/fmw/../TimesTen/tt1122/lib/libtten.so
DataStore=/u01/app/oracle/product/fmw/../aggregate_store/tt_aggr_store
LogDir=/u01/app/oracle/product/fmw/../aggregate_store/logs
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
LogFileSize=1024
LogBufMB=1024
LogBufParallelism=16
Preallocate=0
PermSize=400000
TempSize=200000
MemoryLock=4
CkptFrequency=30
CkptLogVolume=0
CkptRate=20
PrivateCommands=1
RecoveryThreads=40
oraclenetservicename=MYDB
6) Restart TimesTen
cd /sbin
./sysctl -p
ttdaemonadmin -stop
ttdaemonadmin -start
7) In the example below, we are doing the table siebel.wc-KPI_F_041174
[oracle@CIMAlyticsSrv1 TT_INSTALLERS]$ ./ttImportFromOracle -oraConn read/only@8x.2xx.1xx.1x:1521/live -tables siebel.wc_KPI_F_041174 -typeMap 2,1 -prefix ncmp1
Beginning processing
Resolving any tablename wildcards
Eliminating any duplicate tables
Getting metadata from source
Generating database user list
Assigning TimesTen datatypes
Analyzing source tables
Analyzing table 'SIEBEL.WC_KPI_F_041174' ...
Estimating table sizes
Evaluating parallel data load
Generating output files
Finished processing
8) The following files will be generated:
ncmp1CreateUsers.sql
ncmp1CreateTables.sql
ncmp1LoadData.sql
ncmp1CreateIndexes.sql
ncmp1UpdateStats.sql
9) Connect to your TimesTen DB and the run the above sql files in the order above:
[oracle@CIMAlyticsSrv1 bin]$ ttisql -connstr "dsn=TT_AGGR_STORE;uid=user;pwd=password;oraclepwd=opassword"
**the opassword is the password to your oracle instance
Command> @/home/oracle/TT_INSTALLERS/ncmp1CreateUsers.sql
…
..