[IQUG] IQ16.0 Binary Extract & Load

Cathy.Pearce at Euroclear.com Cathy.Pearce at Euroclear.com
Thu Jul 21 04:10:33 MST 2016


Thanks Richard and Mark

I agree, that works :-)

Cheers,
Cathy



From:   "Mumy, Mark" <mark.mumy at sap.com>
To:     "Mei, Richard" <Richard.Mei at bmo.com>, "Cathy.Pearce at Euroclear.com" 
<Cathy.Pearce at Euroclear.com>, "iqug at dssolutions.com" 
<iqug at dssolutions.com>
Date:   20/07/2016 17:19
Subject:        Re: [IQUG] IQ16.0 Binary Extract & Load
Sender address : mark.mumy at sap.com (External)



Thanks Richard.  Used that on my repro and it cleared it up.
 
 
Mark Mumy
Enterprise Architect, Customer Innovation and Enterprise Platform Team 
SAP
M +1 347 820-2136, E mark.mumy at sap.com
Join me online: http://scn.sap.com/people/markmumy/blog
 
Please consider the impact on the environment before printing this e-mail.
 
 
 
From: Richard Mei <Richard.Mei at bmo.com>
Date: Wednesday, July 20, 2016 at 11:17
To: Mark Mumy <mark.mumy at sap.com>, "Cathy.Pearce at Euroclear.com" 
<Cathy.Pearce at Euroclear.com>, IQ Group <iqug at dssolutions.com>
Subject: RE: [IQUG] IQ16.0 Binary Extract & Load
 
We had the same issue with our IQ16 SP11. It’s a known issue and is fixed 
in future versions. It only happens to varchar columns.
 
2324233 - SAP IQ 16: varchar of binary extraction will output the wrong 
data
 
Resolution
This problem (CR 799266) will be fixed in a future version.
The workaround is to disable the optimization before the binary extract 
using DML_Options14=8.
 
ex: set temporary option DML_Options14 = 8;
it is link
https://launchpad.support.sap.com/#/notes/2324233/E
 
 
-------------------
Richard Mei | Senior Manager, Data Services | Customer Data Management | 
BMTT, 416-927-5784 | Website: CAD/MBA Datamart
 
From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of 
Mumy, Mark
Sent: July-20-16 11:51 AM
To: Cathy.Pearce at Euroclear.com; iqug at dssolutions.com
Subject: Re: [IQUG] IQ16.0 Binary Extract & Load
 
Why do you have a delimiter in your binary load?  Try removing that to see 
if it clears it up.
 
FROM '/IQWork/Staging/O_ESS_DIM.ESS_JOBS.dat' 
FORMAT BINARY 
DELIMITED BY '\x09'
 
 
Not related, but concerning.  You have the option 
temp_extract_null_as_empty set to ON.  That means that you’ve now lost the 
ability to distringuish between a NULL string (no data) and a string that 
is empty (data).
 
What character(s) are between the SWAP and DC1 in the 15.4 file?  Can you 
run ‘od –xc’ on the files and sent that output?  Just those three lines 
should suffice.  The extraction should be identical between versions, so 
it is confusing as to why the files have different data.
 
 
Mark Mumy
Enterprise Architect, Customer Innovation and Enterprise Platform Team 
SAP
M +1 347 820-2136, E mark.mumy at sap.com
Join me online: http://scn.sap.com/people/markmumy/blog
 
Please consider the impact on the environment before printing this e-mail.
 
 
 
From: "iqug-bounces at iqug.org" <iqug-bounces at iqug.org> on behalf of "
Cathy.Pearce at Euroclear.com" <Cathy.Pearce at Euroclear.com>
Date: Wednesday, July 20, 2016 at 10:39
To: IQ Group <iqug at dssolutions.com>
Subject: [IQUG] IQ16.0 Binary Extract & Load
 
We're facing an issue  in IQ16.0 SP11 with the export/import in binary 
format while it works fine in ASCII 
(we use this method to backup a smaller table to flatfile or copy to a 
Developer system) 

We don't think the issue is coming from our scripting, as my colleague 
managed to reproduce it with basic extract and load table statements. 
We are extracting and loading into the same database, just different 
owner/schema 

Table structure is: 
TABLE "O_ESS_DIM16"."ESS_JOBS" ( 
        "ID" INTEGER NOT NULL DEFAULT AUTOINCREMENT, 
        "MF_DATE" VARCHAR(5) NOT NULL, 
        "THE_DATE" DATE NOT NULL, 
        "ENVIRONEMENT" VARCHAR(4) NOT NULL, 
        "SWAP_TYPE" VARCHAR(8) NOT NULL, 
        "DC_FROM" VARCHAR(4) NULL, 
        "DC_TO" VARCHAR(4) NOT NULL, 
        "LUP_TS" TIMESTAMP NOT NULL, 
        PRIMARY KEY ( "ID" ASC ) 
) 
ASCII

set temporary option temp_extract_column_delimiter = '\x09'; 
set temporary option temp_extract_null_as_empty = 'on'; 
set temporary option temp_extract_directory = '/IQWork/Staging'; 
set temporary option temp_extract_name1 = 'O_ESS_DIM.ESS_JOBS.dat'; 
set temporary option temp_extract_name2 = ''; 
set temporary option temp_extract_binary = 'off'; 
set temporary option temp_extract_swap = 'off'; 
select * from O_ESS_DIM.ESS_JOBS; 
set temporary option temp_extract_name1 = ''; 

--ASCII 
TRUNCATE TABLE O_ESS_DIM16.ESS_JOBS; 
SET TEMPORARY OPTION IDENTITY_INSERT = 'O_ESS_DIM16.ESS_JOBS'; 
LOAD TABLE O_ESS_DIM16.ESS_JOBS 
(ID,MF_DATE,THE_DATE,ENVIRONEMENT,SWAP_TYPE,DC_FROM,DC_TO,LUP_TS) 
FROM '/IQWork/Staging/O_ESS_DIM.ESS_JOBS.dat' 
FORMAT ASCII 
DELIMITED BY '        ' 
QUOTES OFF   
ESCAPES OFF 



=> OK 
Binary

set temporary option temp_extract_column_delimiter = '\x09'; 
set temporary option temp_extract_null_as_empty = 'on'; 
set temporary option temp_extract_directory = '/IQWork/Staging'; 
set temporary option temp_extract_name1 = 'O_ESS_DIM.ESS_JOBS.dat'; 
set temporary option temp_extract_name2 = ''; 
set temporary option temp_extract_binary = 'on'; 
set temporary option temp_extract_swap = 'off'; 
select * from O_ESS_DIM.ESS_JOBS; 
set temporary option temp_extract_name1 = ''; 

--BINARY 
TRUNCATE TABLE O_ESS_DIM16.ESS_JOBS; 
SET TEMPORARY OPTION IDENTITY_INSERT = 'O_ESS_DIM16.ESS_JOBS'; 
LOAD TABLE O_ESS_DIM16.ESS_JOBS ("ID" BINARY WITH NULL BYTE,"MF_DATE" 
BINARY WITH NULL BYTE,"THE_DATE" BINARY WITH NULL BYTE,"ENVIRONEMENT" 
BINARY WITH NULL BYTE,"SWAP_TYPE" BINARY WITH NULL BYTE,"DC_FROM" BINARY 
WITH NULL BYTE,"DC_TO" BINARY WITH NULL BYTE,"LUP_TS" BINARY WITH NULL 
BYTE) 
FROM '/IQWork/Staging/O_ESS_DIM.ESS_JOBS.dat' 
FORMAT BINARY 
DELIMITED BY '\x09' 
QUOTES OFF   
ESCAPES OFF 



=> In column 'SWAP_TYPE', there's strange characters 
=> In columns 'DC_FROM' and 'DC_TO', there's a trailing 'P' 

It works fine in our 15.4 systems 

Interestingly:  this is how the first few records look in the Binary 
files:  So looks to be the extract that's different 
IQ15.4 
^A12217^K7lPRODSWAP    DC1 DC2 á³QxlM-^KM-^@ 
^B12245^K7M-^HPRODSWAP    DC2 DC1 á³Q^W¾Ê 
^C12294^K7¹PRODSWAP    DC1 DC2 á³Q^A6÷M-^@ 

IQ16.0 
^A12217^K7lPRODSWAP3-DC1PDC2Pá³QxlM-^KM-^@ 
^B12245^K7M-^HPRODSWAPM-^KM-^@DC2PDC1Pá³Q^W¾Ê 
^C12294^K7¹PRODSWAPÊDC1PDC2Pá³Q^A6÷M-^@ 


Any idea what's changed or we've missed? 

Thanks, 
Cathy Pearce 

This e-mail message, including any attachments transmitted with it, is 
CONFIDENTIAL and may contain legally privileged information. This message 
is 
intended solely for the use of the individual or entity to whom it is 
addressed. If 
you have received this message in error, please notify us immediately and 
delete 
it from your system. Please visit our website to read the full disclaimer 
http://www.euroclear.com/site/public/disclaimer" and for Euroclear Group 
company 
information 
https://www.euroclear.com/en/about/our-structure/Corporate-details.html
 



This e-mail message, including any attachments transmitted with it, is 
CONFIDENTIAL and may contain legally privileged information. This message is 
intended solely for the use of the individual or entity to whom it is addressed. If 
you have received this message in error, please notify us immediately and delete 
it from your system. Please visit our website to read the full disclaimer 
http://www.euroclear.com/site/public/disclaimer" and for Euroclear Group company 
information https://www.euroclear.com/en/about/our-structure/Corporate-details.html
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20160721/92653e8a/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/gif
Size: 15737 bytes
Desc: not available
URL: <http://iqug.org/pipermail/iqug/attachments/20160721/92653e8a/attachment-0002.gif>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/gif
Size: 16590 bytes
Desc: not available
URL: <http://iqug.org/pipermail/iqug/attachments/20160721/92653e8a/attachment-0003.gif>


More information about the IQUG mailing list