Unable to resolve WVARCHAR to DB2 table

MT · Post by MT » Wed Jul 09, 2014 10:22 am

Hi tbtcust

what is the column length in DataStage and in DB2?

I suppose your database hast been created with UTF-8 and this means that a single character can be stored in 1,2,3 or even 4 bytes.
Be aware that DB2 length specifications at the moment are in bytes (and not in characters). This can lead to your mentioned warnings.

tbtcust · Post by **tbtcust** » Wed Jul 09, 2014 12:06 pm

Hello MT, thanks for the reply

Some columns are as small as 1 and as large as 80. The database code set is UTF-8.

I understand what you wrote. I would still expect the implicit conversion to account for this.

Based on what you wrote, this message will never go away. True?

MT · Post by MT » Wed Jul 09, 2014 12:12 pm

Hi tbtcust

Some columns are as small as 1 and as large as 80. The database code set is UTF-8.

OK - but is the specified length in DataStage the same as in DB2?
For example if you have a length of 10 in DataStage - try using 40 in DB2 ( as a single character could be stored in up to 4 bytes using UTF-8 ).

tbtcust · Post by **tbtcust** » Thu Jul 10, 2014 6:18 am

Hello MT,

Yes - "but is the specified length in DataStage the same as in DB2?"

I adjusted the database table column as suggested and received the same warning. I even made column a len of 100 an still the same message.

chulett · Post by **chulett** » Thu Jul 10, 2014 6:43 am

So, are you having any of the issues it is warning you of - data loss, corruption, data truncation or unexpected errors? It's just a warning after all, so could always be demoted via a message handler if everything seems to be working fine.

tbtcust · Post by **tbtcust** » Thu Jul 10, 2014 7:10 am

Hello chulett,

Agreed. There is a larger issue, where I am losing records at insert time and DB2 connector is not rolling back. I will ask this in a follow-on post, once this issue is resolved and if the issue continues.

chulett · Post by **chulett** » Thu Jul 10, 2014 7:52 am

Ok... but didn't really answer my question. Does resolving this require solving any of the issues noted or is this just about you wanting to remove the warnings?

tbtcust · Post by **tbtcust** » Thu Jul 10, 2014 8:24 am

Apologies chulett,

Right now, I think this issue is causing an issue with records being dropped at insert time (a second issue). Right now I don't know for sure if they are related or not. If we can resolve this one, then I'll know for sure that they are related or they are two separate issues.

chulett · Post by **chulett** » Thu Jul 10, 2014 9:58 am

Suggestion: drop this issue for now and start a discussion on the "dropped at insert time" problem. Come back here if resolving that one doesn't solve this one at the same time.

tbtcust · Post by **tbtcust** » Thu Jul 10, 2014 12:56 pm

Will do. Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Jul 10, 2014 4:26 pm

Do you have a reject link on the DB2 Connector?

MT · Post by MT » Fri Jul 11, 2014 1:58 am

Hi tbtcust,

reading from a file into a DB2 UTF-8 database should be possible without warnings.

Having UTF-8 for the database and as NLS in the DataStage job
you could use the DB2CODEPAGE set to 1208. Make this a job parameter and do not user the extended unicode for the column and it will work without warnings.
At least it does in my environment.

Otherwise - as suggested above - demote it to informational in the director

tbtcust · Post by **tbtcust** » Fri Jul 11, 2014 3:32 am

Hello ray, I do have a reject link. Records never seem to land there. High-level, as a test I have the commit set to 2000, load three records. The first and third are fine the second has an invalid date. DataStage throws a warned on the second record, did not load that record, nothing landed in the reject file, the failure did not cause the previously inserted record to roll back, and lastly loaded the third record.

Hello MT, I'm not familiar with 1208 so I looked it up. I have done what you mentioned. The warnings did go away. This DB instance will store multi-languages. With this solution it seems we loss that multi-language support. True?

Also, as a test I left everything as-is and removed extended unicode. and there are no warnings as well.

tbtcust · Post by **tbtcust** » Fri Jul 11, 2014 5:46 am

Apologies MT, having a Homer moment.

I asked you "With this solution it seems we loss that multi-language support. True?"

My original tests was in English. I reran both test using Spanish language, which included inserts and export from the DB. The results were perfect.

So it seems the solution is to always removed from extended "Unicode" and DataStage will take care of the rest.

MT · Post by MT » Fri Jul 11, 2014 5:53 am

Hi tbtcust

1208 is UTF-8 and this environment variable sets it for DB2.

I would not say that it is always the solution, but in many cases it is.
We have other jobs working well with Unicode extended attribute and it seems it "depends" on combinations used.

Many things can only be found out by trial & error. To be honest - I hate those

DSXchange

Unable to resolve WVARCHAR to DB2 table

Re: Unable to resolve WVARCHAR to DB2 table