cancel
Showing results for 
Search instead for 
Did you mean: 

Data value was changed with binary format extract/load

former_member232292
Participant
0 Kudos

Dear experts,

Here's a problem I just found while using binary extract/load.

Steps:

1. create table test1(id int,str varchar(20),flag int);

2. insert test1 values(0,'haha',0);commit;

3. select * into test2 from test1 where 1=2;

4. -- the extract part --

set temporary option temp_extract_name1='test1_bin.dat';
set temporary option temp_extract_directory='/iq16/rs/charset';
set temporary option isql_show_multiple_result_sets='on' ;
set temporary OPTION ISQL_PRINT_RESULT_SET = 'ALL';
set temporary option temp_extract_quotes = 'off';
set temporary option temp_extract_quotes_all = 'off';
set temporary option temp_extract_binary = 'ON';
set temporary option dml_options14=8;
set temporary option temp_extract_column_delimiter='\x0F';
select * from test1;
set temporary option temp_extract_name1='';

5. -- the load table part --

load table test2 (id,str,flag) using file '/iq16/rs/charset/test1_bin.dat' delimited by '\x0F' quotes off escapes off format binary;

Then -- we can see -- the value was changed --

(DBA)> select * from test2;
id str flag
--------------------------------------------
0 haha 32

(1 rows)

Execution time: 0.015 seconds

And if there's more rows contained on source table -- it will be even worse -- the following rows will be change more and more until it's full of illegal characters. like --

before extract --

(DBA)> select * from test1;
id str flag
--------------------------------------------
0 haha 0
1 hehe 1

(2 rows)
after extract/load --
(DBA)> select * from test2;
id str flag
--------------------------------------------
0 haha 32
16777216 hehe 538976288

(2 rows)

I don't know if it's bug. Please kind help -- my IQ's version is --

(DBA)> select @@version;
@@version
--------------------------------------------------------------------------------------------------------------------------------
SAP IQ/16.0.110.2722/10940/P/sp11.19/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2018-03-12 02:46:27

Thanks

Regards

Eisen

Accepted Solutions (1)

Accepted Solutions (1)

claude
Explorer
0 Kudos

Hello Eisen,

I also did your repro with the SAP IQ version "SAP IQ/16.1.020.528/10528/P/sp02/MS/Windows 2012 R2/64bit/2017-07-14 14:45:44"

I've a same behavior, a result like this:

id,str,flag
0,'haha',32
0,'haha',32
0,'haha',32
0,'haha',32
256,'haha',538976288
2105376,'hehe',538976288
16777216,'hehe',538976288

Please can you open an incident then I'll take it for a further investigation with the Dev.

Just let me know for the incident number.

Regards,
Claude

Answers (2)

Answers (2)

former_member232292
Participant
0 Kudos

Dear Claude,

Thank you very much... It's my fault -- I forget the "binary with null byte" and just reuse the load statement from ascii loading.

Thanks again

Regards

Eisen

former_member232292
Participant
0 Kudos

Dear Claude,

Thank you very much. The incident is -- 1880389257

Please have a look. Thanks

Regards

Eisen