Issue when Importing Metadata from oracle table..

jagadam · Post by **jagadam** » Tue Nov 19, 2013 8:24 am

Hi,

When I am trying to import the metadata from the oracle table, the varchar field lengths are increasing by four times when compared to length in the actual oracle table.

Version of datastage used 8.7
unix running on 4 nodes(so i doubt that its multiplying the length by 4..but dont know why)
importing it through plug in metadata option.

Any information in this regard will be helpful.

Thanks
NJ

chulett · Post by **chulett** » Tue Nov 19, 2013 8:30 am

No doubt an NLS issue. Use the parallel (command line?) metadata importer rather than the plugin. I don't recall its name off the top of my head, however.

jagadam · Post by **jagadam** » Tue Nov 19, 2013 10:03 am

Hi Chulett,

I imported using orchestrate method and it imported with the right length. But why its giving wrong length when trying to import with plugin metadata method. what would be NLS issue and how can we fix this. thanks!!

Thanks
NJ

ray.wurlod · Post by **ray.wurlod** » Tue Nov 19, 2013 4:14 pm

You can't "fix" it. The OCI reports multiple bytes per character when metadata queries are executed.

jagadam · Post by **jagadam** » Tue Nov 19, 2013 4:59 pm

So, is it an issue with version 8.7 or with any version..? because we didn't have this issue in version 8.1. Thanks!!

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Tue Nov 19, 2013 5:08 pm

I suspect NLS wasn't installed or the project had it disabled at 8.1. Hence the difference.

Have your admin check the NLS settings on the old 8.1 project and compare it to the 8.7 project if you still have access.

Note: Enabling / Disabling NLS can have significant impacts way above what you are talking about, so proceed with caution!

jagadam · Post by **jagadam** » Wed Nov 20, 2013 9:15 pm

Thanks for the responses. But, when i tried to connect to other oracle database instance(version 10.2)using plug-in metadata option it is importing it with the correct length. But, when i tried to connect to an oracle instance(version 11.2) with plug-in metadata option, Its giving incorrect length. If its an NLS issue then i think it should behave same way for both instances...

Thanks
NJ

chulett · Post by **chulett** » Wed Nov 20, 2013 10:51 pm

What characterset is each instance using?

ray.wurlod · Post by **ray.wurlod** » Thu Nov 21, 2013 2:53 pm

It's not entirely an NLS issue, it also relates to the way that the different OCI versions report metadata.

jagadam · Post by **jagadam** » Thu Nov 21, 2013 5:21 pm

Hi,

These are the NLS charactersets that are being used by different versions.

For version 10.2 : WE8ISO8859P1(NLS_CHARACTERSET)

For Version 11.2 : AL32UTF8 (NLS_CHARACTERSET)

Thanks
NJ

Mike · Post by **Mike** » Thu Nov 21, 2013 9:24 pm

Seems like a characters vs bytes difference... you went from a fixed width characterset in your version 10.2 (1 character = 1 byte) to a variable width characterset in your version 11.2 (1 character = 1-4 bytes).

Seems like the metadata import may be assuming a worst case scenario of every character requiring 4 bytes... hence the size multiplication by 4.

Mike

chulett · Post by **chulett** » Thu Nov 21, 2013 9:48 pm

As suspected.