Timesten - ttBulkCp
About
ttBulkCp is an utility to import and export data from a Timesten database
Articles Related
Syntax
Import
TT_DIR/bin/ttbulkcp -i dsn=myDSN mySchema.myTable /path/to/my/dump.dmp
Export
TT_DIR/bin/ttbulkcp -o
Dump
Attribute
Attribute lines are used for setting file attributes, which control the formatting of the datafile.
Attribute lines begin with the ten-character sequence ##ttBulkCp. The format of an attribute line is:
##ttBulkCp[:attribute=value]...
Attribute lines can appear anywhere in the datafile.
Example:
- The following header line set the date format to a custom format:
##ttBulkCp:DFORMAT='Mon DD, YYYY'
- The following header line sets the field separator character to $ and disables quoting of character strings:
##ttBulkCp:FSEP=$:QUOTES=0
How to
Migrate data from Oracle Database to Timesten ?
With SQL Developer
With SQL Developer, you have an option to export to the ttbulkcp format.
You get a dump file such as this one:
##ttBulkCp:FSEP=,:QUOTES=1:TSFORMAT=Oracle
##ttBulkCp:CHARACTERSET=WE8MSWIN1252
##ttBulkCp:NCHARENCODING=ASCII
#Generated at : 2012-08-28 15:08:46.241
#end
# 1. COLUMN_NAME_1
# 2. COLUMN_NAME_2
# 3. COLUMN_NAME_3
# 4. COLUMN_NAME_4
# 5. COLUMN_NAME_5
# 6. COLUMN_NAME_6
# 7. COLUMN_NAME_7
# 8. COLUMN_NAME_8
# 9. COLUMN_NAME_9
# 10. COLUMN_NAME_10
# 11. COLUMN_NAME_11
# 12. COLUMN_NAME_12
# 13. COLUMN_NAME_13
# 14. COLUMN_NAME_14
# 15. COLUMN_NAME_15
# 16. COLUMN_NAME_16
# 17. COLUMN_NAME_17
# 18. COLUMN_NAME_18
# 19. COLUMN_NAME_19
# 20. COLUMN_NAME_20
# 21. COLUMN_NAME_21
# 22. COLUMN_NAME_22
# 23. COLUMN_NAME_23
# 24. COLUMN_NAME_24
# 25. COLUMN_NAME_25
1,3113,10,5,334990,256665,20157,31633,,342520,22076,25606,1644591,4440,,,,,21881,2250,1994,2805,1456,74,4514
1,3181,16,3,334714,257073,12029,28078,,342363,13576,19075,1634336,4405,,,,,22722,1769,2474,2326,1977,82,4487
1,3283,27,7,276048,211939,27281,37168,,283422,33496,35691,1330135,3627,,,,,18612,1885,1417,2479,818,15,3642
1,2700,23,5,335487,258241,41688,51751,,342043,43677,46136,1634091,4441,,,,,22619,2348,1664,2975,1074,59,4500
1,1958,12,5,334903,,25860,37675,,339610,30043,32307,1612305,4430,,,,,,2129,2002,3076,1126,25,4455
1,2167,7,4,342989,,50827,60011,,349988,46152,48062,1661716,4430,,,,,,2326,1652,3047,1040,153,4583
1,1726,22,4,331812,,28311,39947,,331653,25864,28353,1575562,4292,,,,,,1996,1971,2799,1285,52,4344
1,1206,20,2,339821,,22131,34605,,342182,22648,24673,1594781,4379,,,,,,2253,1873,3129,1092,27,4406
.........................
.........................
Then you can import the file with the following ttBulkCp command:
ttBulkCp -i -e mytbl.err -m 0 "DSN=myDataSourceName;UID=UserId;Pwd=UserPassword" TheTargetTable TheDumpFile.dump
where:
- -i is the import option (and not export)
- -e defines the error file mytbl.err
- -m defines the number of error before stopping the import (0 means never stop)
- “DSN=myDataSourceName;UID=UserId;Pwd=UserPassword” is the connection string where DSN is the data source name, UID the user id and PWD the user password
- TheTargetTable defines the table where the data is loaded
- TheDumpFile.dump design the dump file name
With the m option, an error is so reported:
SA_TIME_DA000087D2_DATA_TABLE.dump:
0 rows inserted
8431 rows not inserted due to errors
8431 rows total
Command Line
Not tested
A possible method is to:
- export your Oracle DB using Data Pump (expdp)
- transform the binary file to text file using impdp with the Oracle Database - Data Pump - (Export|Import) Utilities option
- then run the text file against the TT database
Support
ttBulkCp: Error received in file
ttBulkCp:
ttBulkCp: Error received in file /u01/stage/08_AirlineDemo/airlines/TT/SA_TT_05M.dmp, line 1682720 --
S1000: [TimesTen][TimesTen 11.2.2.2.0 ODBC Driver][TimesTen]
TT0994: Data store connection terminated. Please reconnect. -- file "sqlAPI.c", lineno 5591,
procedure "sb_sqlExecBatch()" (TimesTen error code = 994).
Verify that you have enough size in your partition.
date values must be in YYYY-MM-DD format
When loading a file, you can receive this error:
##ttBulkCp:CHARACTERSET=AL32UTF8
###
### Errors/warnings from file SA_TIME_DA000087D2_DATA_TABLE.dump:
###
# While processing row 1 (global row 1) in file SA_TIME_DA000087D2_DATA_TABLE.dump, line 37:
### ERROR at character 139:
### While reading value for column 24 (DEP_DATE00008780) of type DATE:
### Invalid date value -- date values must be in YYYY-MM-DD format.
8796,17043,25383,33755,5,0,229,76,1025,1,"1989 HY1","1989 / 01","1989 Q1","1989 Week 01","1989",19890101,19890101,19890101,1,1,1,1,1989,05-01-1989 00:00:00,19890105,2,10,4,41,1989,32513
# *
# (row was not inserted)
As you see the export made by SQL Developer doesn't match the standard ODBC date format.
The original header of the dump file was:
##ttBulkCp:FSEP=,:QUOTES=1:TSFORMAT=Oracle
You just need to add or replace the timestamp attribute format by a date format. For instance:
##ttBulkCp:FSEP=,:QUOTES=1:DFORMAT='DD-MM-YYYY*HH24:MI:SS'