JR30182: WHEN SETTING SPARSE LOOKUP OPTION, NVARCHAR COLUMN IS WRONGLY DEFINED AS STRING NOT USTIRNG IN OSHSCRPT.OSH

APAR status

• Closed as program error.

Error description

• When setting sparse lookup option, NVarChar column is wrongly
  defined as string not ustirng in OshScrpt.osh.
  As a result, the sparse lookup operation fails w/ warning
  messages like below:
  
   Invalid character(s) ([x82]) found converting string (code
  point(s): [x82][xA0][x82][xA2][x82])
   from codepage Shift_JIS to Unicode, substituting.
  [nls/converter.C:1011]
  
  SQL> desc AXA_LOOKUP_TEST
   NAME       NULL?    TYPE
   ---------- -------- ------------
   ID         NOT NULL NUMBER
   NAME       NOT NULL VARCHAR2(10)
   KANJI_NAME NOT NULL NVARCHAR2(5)
  
  When editing RT_SC99/OshScript.osh manually like below, the job
  works fine:
  
  ## Operator
  odbclookup
  ...
    KANJI_NAME:not_nullable string[max=5]=KANJI_NAME;)]
  'Lookup_4:DSLink7.v'
    |
    V
    KANJI_NAME:not_nullable ustring[max=5]=KANJI_NAME;)]
  'Lookup_4:DSLink7.v'
  

Local fix

Problem summary

• ****************************************************************
  USERS AFFECTED:
  Using NVarChar with sparse lookup
  ****************************************************************
  PROBLEM DESCRIPTION:
  When setting sparse lookup option, NVarChar column is wrongly
  defined as string not ustirng in OshScrpt.osh.
  As a result, the sparse lookup operation fails w/ warning
  messages.
  ****************************************************************
  RECOMMENDATION:
  Apply patch
  This change is included in 8.0.1 Fix Pack 2.
  This change is included in 8.1 FP1
  ****************************************************************
  

Problem conclusion

• Issue identified in oshgen and fixed.
  I should point out the difference you will see in osh
  generationas it might not be quite what you expect. You will see
  2  different versions depending on whether you have any
  strings in your lookup source, or whether it contains all
  ustrings.
    If it is all ustrings, then you will see the
  change you were    suggesting above.
  
     ## Operator
    odbclookup
          ...
    KANJI_NAME:not_nullable
  string[max=5]=KANJI_NAME;)]       'Lookup_4:DSLink7.v'
             |
            V
           KANJI_NAME:not_nullable
  ustring[max=5]=KANJI_NAME;)]       'Lookup_4:DSLink7.v'
  
  
         However, if there are
  strings in your lookup schema (as was in  your test dsx),you
  will see something different since the      dblookup operator
  will only ever produce strings or ustrings,   not a mixture. In
  this case a -use_strings option is added whichtells the operator
  to only output strings, and the we use the   modify adapter to
  map those strings to ustrings using the map   name defined on
  the stage.
              ## Operator
             odbclookup
            ...
           -use_strings
          ....
  
  KANJI_NAME:not_nullable string[max=5]=KANJI_NAME;)]
  'Lookup_4:DSLink7.v'
   |
  V
  KANJI_NAME:not_nullable
  ustring[max=5]=ustring_from_string[STAGE_MAP_NAME](KANJI_NAME);)
  ] 'Lookup_4:DSLink7.v'
  
  Also, from the note above, I should point out that there isn't
   necessarily a direct correlation between ustring and NVARCHAR.
  This depends on the characterset defined on the database, and
  when running in NLS mode, string should be thought of more like
  an array of bytes, rather than chars (which a ustring is),
   i.e. unless the database varchar has a single byte
  characterset, character manipulation functions won't work.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Modules/Macros

• SERVER
  

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R801 PSY

     UP