on 07-04-2018 8:09 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear Claude,
Thank you very much. The incident is -- 1880389257
Please have a look. Thanks
Regards
Eisen
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.