topic Re: VARCHAR2 converts to NVARCHAR2 during load in Data Management Questions

VARCHAR2 converts to NVARCHAR2 during load

RobertRenner — Fri, 08 Apr 2011 18:09:49 GMT

I have this pesky little problem with my SDE instances (running ArcSDE 9.3 with Oracle 11g). Everytime I load a table or feature class into my geodatabase from a shapefile, an sde export file, or from another file or enterprise geodatabase, all of my varchar2 columns automatically are converted to NVARCHAR2 on the load. I asked Vince Angelo about it and he suggested toggling the UNICODE_STRING DBTUNE parameter in DEFAULTS to FALSE. I didn't have this parameter in my DEFAULTS keyword so I added it an set it to FALSE, restarted SDE, but this did not resolve my issue. Any thoughts?

Re: VARCHAR2 converts to NVARCHAR2 during load

VinceAngelo — Fri, 08 Apr 2011 18:38:15 GMT

An 'sdeimport' *also* maps SE_STRING_TYPE to NVARCHAR2? That's bizarre.

How is your character set defined? As SYSTEM:

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

and/or

SELECT * FROM NLS_DATABASE_PARAMETERS;

Did you change the UNICODE_STRING in the dbtune.sde file or in SDE.DBTUNE?
(Or both, by running 'sdedbtune -o import'?)

- V

Re: VARCHAR2 converts to NVARCHAR2 during load

RobertRenner — Fri, 08 Apr 2011 19:50:44 GMT

An 'sdeimport' *also* maps SE_STRING_TYPE to NVARCHAR2? That's bizarre.

How is your character set defined? As SYSTEM:

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

and/or

SELECT * FROM NLS_DATABASE_PARAMETERS;

Did you change the UNICODE_STRING in the dbtune.sde file or in SDE.DBTUNE?
(Or both, by running 'sdedbtune -o import'?)

- V

I just ran some sdeimports from my dev to test environment and the column defs are holding where they should be. My problem was from legacy to dev. I'll check on the NLS CHARACTERSET in a bit and revisit this thread. In the meantime, thanks for your help.

Re: VARCHAR2 converts to NVARCHAR2 during load

RobertRenner — Fri, 08 Apr 2011 20:26:09 GMT

Did you change the UNICODE_STRING in the dbtune.sde file or in SDE.DBTUNE?
(Or both, by running 'sdedbtune -o import'?)

- V

I used sdedbtune -o insert, not import. Should I have run import as well?

Re: VARCHAR2 converts to NVARCHAR2 during load

VinceAngelo — Fri, 08 Apr 2011 21:48:48 GMT

The 'insert' mode overrides the last file's contents. 'import' mode would clobber
any previous 'inserts' with file contents, so it's rare that you want to do both.

I'm very conservative in my DBTUNE management:

+ I *never* edit the %SDEHOME%\etc\dbtune.sde file, leaving it as installed.

+ At each new major release I always copy the dbtune.sde to dbtune.{instance_name}
and recreate the keywords I added to the previous release (taking note of any added
parameters not reflected in the old build). [For SP updates I just move (or hard-link
on Unix) the previous release's file.]

+ I only use 'sdedbtune -o import' to load the entire dbtune.{instance_name} file,
so I know the database content is accurately reflected in a single ASCII file.

+ I only use 'sdedbtune -o export' if the SDEHOME has been corrupted (because I prefer
to maintain documentation and formatting) in my dbtune files -- and then I just use the
export data to reassemble (and re-import) dbtune.{instance_name}.

- V