Helen,

Are you talking about the TIB_DataPump component?

If the data is substantial in size it may be a much faster route to use a
text file, compress it, send it over the wire, decompress it, load it in.

Jason Wharton

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <IBObjects@yahoogroups.com>
Sent: Friday, January 15, 2010 1:42 PM
Subject: Re: [IBO] Moving data from one database to another using
Tib_import/export. Surrogate key?


> At 05:43 AM 16/01/2010, you wrote:
>>Hi,
>>
>>I am going to be writing code to schedule an export from one database,
>>transfer the data via ftp/http/file copy to another system and then have
>>that second system import the data.
>>
>>The data I am moving is all from a single table, I want to do something
>>like :
>>
>>1 - export all data from table A where ADDED_TIMESTAMP>LAST_EXPORT_DATE to
>>a CSV
>>2 - my code uploads to destination
>>3 - Target system uses TIB_IMPORT to load the CSV into the table.
>
> You don't need to go through these hoops. TIB_Import enables you to do
> the export and import in one operation, in a coss-database transaction,
> without converting data to CSV and back again.
>
>>All seems ok to me except that the table in question has a surrogate key
>>provided from a generator.
>>
>>Is there a way to say to the TIB_IMPORT on the central target system :
>>
>>"Load all the data from this CSV but don't load the TABLE_ID column,
>>instead get a value for it from Generator X".
>
> Supposing you don't go your proposed route but instead use TIB_Import as
> designed:
>
> Source query:
>
> select <column-list-excluding-ID-field> from srctable where....
>
> Destination statement:
>
> insert into desttable (<column-list-excluding-ID-field>)
> values (<params-list-excluding-ID-field>)
>
> Of course, make sure that you have written a safe BI trigger for the
> TABLE_ID column in the destination table, viz.,
> ...as
> if (new.table_id is null) then
> new.table_id = gen_id (gen_table_id, 1);
>
> you wrote:
>
>>There also will be many systems uploading files to the central target
>>system in the manner described.
>
> Caveat here : if the idea is to use an export-import job on a regular
> basis as a multi-user replication system, this is a fairly unsound
> approach to that task.
>
> Helen