question on codepage file type modifier of export command

Harishkumar .Pathangay

question on codepage file type modifier of export command

Hi,
The codepage file type modifier of export command will result in data loss because of double conversion when used across different code pages. How is that option useful then?
Let me give a sample scenario.
I have a db in unicode 8 code page. I use windows client to connect to database and export data with codepage file type modifier as utf-8.
The db server will send data in unicode code page, which will be converted to application code page [windows code page]. so this can result in subsitution character placement because utf-8 is wider set than windows code page.
Since we have also specified codepage file type modifier as utf-8 in export command, the output del file will be created as utf-8 code page, which means again there is a conversion happening from windows code page to utf-8.
this second conversion is not proper because of substitution characters placement during first conversion in previous step.
How come then  codepage file type modifier is efficient? Should i make sure such a double conversion will not result in data loss?

why cannot the export utility convert the code page from server code page to codepage as in file type modifier with by passing application code page as intermediate step?

thanks,
harish pathangay

Nadir Doctor

question on codepage file type modifier of export command
(in response to Harishkumar .Pathangay)
Hi Harish,

This is due to support for different versions of granularity:

DB2CODEPAGE - instance level

codepage - database configuration parameter specified at creation time and
cannot be altered thereafter [only option is if it needs change to drop and
recreate using new codepage]

export, import, ingest, load utilities - application level


Best Regards,
Nadir



On Wed, Aug 2, 2017 at 3:03 PM, Harishkumar .Pathangay <[login to unmask email]
> wrote:

> Hi,
> The codepage file type modifier of export command will result in data loss
> because of double conversion when used across different code pages. How is
> that option useful then?
> Let me give a sample scenario.
> I have a db in unicode 8 code page. I use windows client to connect to
> database and export data with codepage file type modifier as utf-8.
> The db server will send data in unicode code page, which will be converted
> to application code page [windows code page]. so this can result in
> subsitution character placement because utf-8 is wider set than windows
> code page.
> Since we have also specified codepage file type modifier as utf-8 in
> export command, the output del file will be created as utf-8 code page,
> which means again there is a conversion happening from windows code page to
> utf-8.
> this second conversion is not proper because of substitution characters
> placement during first conversion in previous step.
> How come then codepage file type modifier is efficient? Should i make
> sure such a double conversion will not result in data loss?
>
> why cannot the export utility convert the code page from server code page
> to codepage as in file type modifier with by passing application code page
> as intermediate step?
>
> thanks,
> harish pathangay
>
> -----End Original Message-----
>

Prashant Shettar

question on codepage file type modifier of export command
(in response to Nadir Doctor)
When you specify code page modifier in the export command , export utility
converts characters from database code page to the one mentioned in the
export command and stores data with that codepage. I am not sure whether
database does double code page conversion , as you explained. There should
not be any data loss. If any then it's defect.

You can import data to another table (mentioning the code page )and see
whether you have right data in the database. Check on the server.

This option is helpful when you are moving data between different
applications having different code page. For me this option was helpful
when I was migrating non-unicode database to unicode. For some reason, I
could not use load through Cursor.

Regards
Prashant Shettar










On Thu, Aug 3, 2017 at 7:40 AM, Nadir Doctor <[login to unmask email]> wrote:

> Hi Harish,
>
> This is due to support for different versions of granularity:
>
> DB2CODEPAGE - instance level
>
> codepage - database configuration parameter specified at creation time and
> cannot be altered thereafter [only option is if it needs change to drop and
> recreate using new codepage]
>
> export, import, ingest, load utilities - application level
>
>
> Best Regards,
> Nadir
>
>
>
> On Wed, Aug 2, 2017 at 3:03 PM, Harishkumar .Pathangay <
> [login to unmask email]> wrote:
>
>> Hi,
>> The codepage file type modifier of export command will result in data
>> loss because of double conversion when used across different code pages.
>> How is that option useful then?
>> Let me give a sample scenario.
>> I have a db in unicode 8 code page. I use windows client to connect to
>> database and export data with codepage file type modifier as utf-8.
>> The db server will send data in unicode code page, which will be
>> converted to application code page [windows code page]. so this can result
>> in subsitution character placement because utf-8 is wider set than windows
>> code page.
>> Since we have also specified codepage file type modifier as utf-8 in
>> export command, the output del file will be created as utf-8 code page,
>> which means again there is a conversion happening from windows code page to
>> utf-8.
>> this second conversion is not proper because of substitution characters
>> placement during first conversion in previous step.
>> How come then codepage file type modifier is efficient? Should i make
>> sure such a double conversion will not result in data loss?
>>
>> why cannot the export utility convert the code page from server code page
>> to codepage as in file type modifier with by passing application code page
>> as intermediate step?
>>
>> thanks,
>> harish pathangay
>>
>> -----End Original Message-----
>

Harishkumar .Pathangay

RE: question on codepage file type modifier of export command
(in response to Prashant Shettar)

Hi Nadir,

I am talking about code page conversion happening twice when export utility.

Hi Prashant,

Exactly, that is the point I am raising in the question. I got confused because of following words from KC:

Please note KC Words: It is saying export utility will convert data from application code page to file type modifier code page.

codepage = x is an ASCII character string. The value is interpreted as the code page of the data in the output data set. Converts character data to this code page from the application code page during the export operation

As per above words, Export will convert from Database Code Page to Application Code Page and again Convert from Application Code Page to Code Page File Type Modifier. This is Redundant Code Page conversion.

Is it not?

The Document clearly deviates from behavior and what you are mentioning too and what I observe too. It is not clear and just a source of confusion.  

Thanks,

Harish Pathangay

Harishkumar .Pathangay

RE: question on codepage file type modifier of export command
(in response to Harishkumar .Pathangay)

Hi, Did a test case.

I am getting Error, but I am expecting messages to be vice versa.


db2 export to unisys-1270.del of del modified by codepage=1270 select * from unisys
SQL2754N Codepage "1270" cannot be converted to codepage "1252".

DB Code Page is 1208, App Code Page 1252 [Export is Run from Windows Client], File Type Code Page Modifier 1270.

I am expecting message to be Codepage "1252" cannot be converted to codepage "1270".

Why it is saying other way? that is where I am getting confused.

thanks,

harish pathangay

Nadir Doctor

question on codepage file type modifier of export command
(in response to Harishkumar .Pathangay)
Hi Harish,

This may aid in your better understanding -

http://www-01.ibm.com/support/docview.wss?uid=swg21984868
-- note the safer preference to use ixf format instead of del in middle of
document and example towards end of document respectively


Raul's article also provides a good discussion and with mainframe
interaction -

https://www.ibm.com/developerworks/data/library/techarticle/dm-0506chong/index.html


Best Regards,
Nadir



On Sat, Aug 5, 2017 at 4:59 AM, Harishkumar .Pathangay <[login to unmask email]
> wrote:

> Hi, Did a test case.
>
> I am getting Error, but I am expecting messages to be vice versa.
>
>
> db2 export to unisys-1270.del of del modified by codepage=1270 select *
> from unisys
> SQL2754N Codepage "1270" cannot be converted to codepage "1252".
>
> DB Code Page is 1208, App Code Page 1252 [Export is Run from Windows
> Client], File Type Code Page Modifier 1270.
>
> I am expecting message to be Codepage "1252" cannot be converted to
> codepage "1270".
>
> Why it is saying other way? that is where I am getting confused.
>
> thanks,
>
> harish pathangay
>
> -----End Original Message-----
>

Harishkumar .Pathangay

question on codepage file type modifier of exportcommand
(in response to Nadir Doctor)
I have read all those docs.

Document is telling one thing. But Command is behaving other way.
Why? That is the question I am asking.

SQL2754N Codepage "1270" cannot be converted to codepage "1252".
But as per that Document you sent, I should get Error Message Text as below.
SQL2754N Codepage "1252" cannot be converted to codepage "1270".

Why it is behaving like that?

Thanks,
Harish Pathangay

Sent from Mail for Windows 10

From: Nadir Doctor
Sent: 05 August 2017 18:13
To: [login to unmask email]
Subject: [DB2-L] - RE: question on codepage file type modifier of exportcommand

Hi Harish,

This may aid in your better understanding -

http://www-01.ibm.com/support/docview.wss?uid=swg21984868
-- note the safer preference to use ixf format instead of del in middle of document and example towards end of document respectively


Raul's article also provides a good discussion and with mainframe interaction -

https://www.ibm.com/developerworks/data/library/techarticle/dm-0506chong/index.html



Best Regards,
Nadir
 


On Sat, Aug 5, 2017 at 4:59 AM, Harishkumar .Pathangay <[login to unmask email]> wrote:
Hi, Did a test case.
I am getting Error, but I am expecting messages to be vice versa.

db2 export to unisys-1270.del of del modified by codepage=1270 select * from unisys
SQL2754N Codepage "1270" cannot be converted to codepage "1252".
DB Code Page is 1208, App Code Page 1252 [Export is Run from Windows Client], File Type Code Page Modifier 1270.
I am expecting message to be Codepage "1252" cannot be converted to codepage "1270".
Why it is saying other way? that is where I am getting confused.
thanks,
harish pathangay


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2




Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Attachments

  • 3D9976CE4562444A92DF5CCE6968CBC4.png (<1k)
  • C166959EC4754C2EA98F5C688DAD30B1.png (<1k)