Problem with binary load

[Former Member]

Hi,

I need to update the data in a non-prod table from my prod data.  I've extrat the data to a binary file.  When I try to load the data, I receive the following error:

Cannot convert 21490200 to a numeric(18)...

The two tables are identical, same OS, IQ 15.4 on both servers.

Extract script used:

SET TEMPORARY OPTION Temp_Extract_Name1 ='/location_1/tableA.bin';

SET TEMPORARY OPTION Temp_Extract_Name2 ='';

SET TEMPORARY OPTION Temp_Extract_Binary ='on';

SET TEMPORARY OPTION Temp_Extract_Swap ='off';

SET TEMPORARY OPTION Temp_extract_Size1 = '1073741824';

SELECT * FROM tableA;

Load script:

set temporary option disable_ri_check = 'on';
set temporary option quoted_identifier = 'on';
set temporary option escape_character = 'on';
set temporary option minimize_storage = 'on';

LOAD table tableA (
id_exec_trav              BINARY WITH NULL BYTE,
date_due                  BINARY WITH NULL BYTE,
date_debut_interv                  BINARY WITH NULL BYTE,
heure_debut_interv                  BINARY WITH NULL BYTE,
date_fin_interv                  BINARY WITH NULL BYTE,
heure_fin_interv                  BINARY WITH NULL BYTE,
date_debut_rdv                  BINARY WITH NULL BYTE,
heure_debut_rdv                  BINARY WITH NULL BYTE,
heure_fin_rdv                  BINARY WITH NULL BYTE,
plage_horaire_rdv                  BINARY WITH NULL BYTE,
ind_rendez_vous                  BINARY WITH NULL BYTE,
ind_echeance                  BINARY WITH NULL BYTE,
ind_rendez_vous_manque                  BINARY WITH NULL BYTE,
ind_echeance_manque                  BINARY WITH NULL BYTE,
row_timestamp                  BINARY WITH NULL BYTE
)
from '/location1/tableA.bin'

QUOTES OFF
STRIP OFF
ESCAPES OFF
FORMAT 'binary'
byte order low
preview on
notify 100000

with checkpoint on;
commit;
rollback;

set temporary option escape_character = 'off';
set temporary option quoted_identifier = 'off';
set temporary option disable_ri_check = 'off';
set temporary option minimize_storage = 'off';

Any idea?

Julie

[Former Member]

I just realized that the problem is with the extract, not the load.  I had to remove the primary key and the NULL constraint to be able to load the data.

[raymond_lackey]

good catch, I was just going through my notes and realized that was the answer.

[Former Member]

It's exactly what I tried two minutes ago.  I used an unsigned bigint and it worked fine.  But in reality, I must keep the numeric type due to our data architecture...

[Former Member]

Thanks Ray!

I tried to drop the table and recreate it without any indexes or primary key, making it was recreated in IQ_Main (good catch!).  It doens't work.

[raymond_lackey]

hmmmm, a mystery...can you change the datatypes on source and destination to integer?

[raymond_lackey]

Hi Julie,

First make sure the table is created in IQ_Main, not IQ_System_Main. Then try dropping primary key and / or a HG index on the column in question. I have had this problem before on occasion, in some cases I had to drop and recreate the table.