Timesten - ttBulkCp

> Database > (TimesTen|TT) (In-Memory Database|IMDB)

1 - About

ttBulkCp is an utility to import and export data from a Timesten database

Advertising

3 - Syntax

3.1 - Import

TT_DIR/bin/ttbulkcp -i dsn=myDSN mySchema.myTable /path/to/my/dump.dmp

3.2 - Export

TT_DIR/bin/ttbulkcp -o

4 - Dump

4.1 - 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
Advertising

5 - How to

5.1 - Migrate data from Oracle Database to Timesten ?

5.1.1 - 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
Advertising

5.1.2 - 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 sqlfile option
  • then run the text file against the TT database

6 - Support

6.1 - 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.

6.2 - 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'

7 - Documentation / Reference