DSXchange

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

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.

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

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

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!!

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!

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

What characterset is each instance using?

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

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

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

As suspected.