Data Integration

 View Only

  • 1.  Datastage varchar unicode issue

    Posted Sun July 10, 2022 07:59 AM
    Hİ everyone,

    i need your help,  my goal is  move the data to db2 from oracle but when I move columns with type varchar2 it gives me the below error,

    While reading data for column <COLUMN_NAME>, the connector received Oracle error code ORA-1406. (CC_OraStatement::logArrayReturnCodes, file CC_OraStatement.cpp, line 6,964)

    if i set  unicode  the data type extended in transformer, the problem  is solved  but i, don't want to always define column in  database connector. i have multiple table to move and i want create dynamic job with rcp,  is there anything to  way to make varchar extended set unicode as default in transformer or different solution

    My enviroment info 
    -Datastage 11.1.7
                               Engine  OS->Linux
                                                 Locale-> 
                                                              LANG=en_US.UTF-8
                                                              LC_CTYPE="en_US.UTF-8"
                                                              LC_NUMERIC="en_US.UTF-8"
                                                              LC_TIME="en_US.UTF-8"
                                                              LC_COLLATE="en_US.UTF-8"
                                                              LC_MONETARY="en_US.UTF-8"
                                                              LC_MESSAGES="en_US.UTF-8"
                                                              LC_PAPER="en_US.UTF-8"
                                                              LC_NAME="en_US.UTF-8"
                                                              LC_ADDRESS="en_US.UTF-8"
                                                              LC_TELEPHONE="en_US.UTF-8"
                                                              LC_MEASUREMENT="en_US.UTF-8"
                                                              LC_IDENTIFICATION="en_US.UTF-8"
                                                              LC_ALL=

    -DB2 
                                                             Database territory = TR
                                                             Database code page = 1208
                                                             Database code set = UTF-8
                                                             Database country/region code = 90
                                                             Database collating sequence = IDENTITY
                                                             Alternate collating sequence (ALT_COLLATE) =
                                                             Number compatibility = ON
                                                             Varchar2 compatibility = ON
                                                             Date compatibility = OFF 

    -Oracle 
                                NLS_RDBMS_VERSION 19.0.0.0.0
                                NLS_NCHAR_CONV_EXCP FALSE
                                NLS_LENGTH_SEMANTICS BYTE
                                NLS_COMP BINARY
                                NLS_DUAL_CURRENCY $
                                NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
                                NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
                                NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
                                NLS_TIME_FORMAT HH.MI.SSXFF AM
                                NLS_SORT BINARY
                                NLS_DATE_LANGUAGE AMERICAN
                                NLS_DATE_FORMAT DD-MON-RR
                                NLS_CALENDAR GREGORIAN
                                NLS_NUMERIC_CHARACTERS .,
                                NLS_NCHAR_CHARACTERSET AL16UTF16
                                NLS_CHARACTERSET WE8ISO8859P9
                                NLS_ISO_CURRENCY AMERICA
                                NLS_CURRENCY $
                                NLS_TERRITORY AMERICA
                                NLS_LANGUAGE AMERICAN


    Best Regards

    ------------------------------
    Şükrü EREN
    ------------------------------

    #DataIntegration


  • 2.  RE: Datastage varchar unicode issue

    IBM Champion
    Posted Tue July 12, 2022 02:52 AM
    Hello Şükrü,

    this is an old and cumbersome problem with reading UTF-8 data with RCP. DataStage when reading data with RCP from a database connector will define the columns as string and afaik there is no way to force it to read it as ustring when doing a select * from table. What you can do is to read e.g. instead of varchar (DB2) or varchar2 (oracle) as vargraphic (db2) or nvarchar (oracle, as far as I remember, it has been a while that I worked with oracle) by doing a cast in your select statement, then datastage will create the metadata as ustring. Also you can set an environment variable in the datastage administrator for the database NLS setting, which would be $DB2CODEPAGE for db2 and $NLS_LANG for oracle.

    I hope this helps.

    KR Ralf

    ------------------------------
    Ralf Martin
    Principal Consultant
    Infologistix GmbH
    Bregenz
    ------------------------------

    Original Message