Load Ingest Data
Results 1 to 3 of 3

Thread: Retrieving unicode strings in a TPT export

  1. #1
    deboopi is offline Junior Member
    Join Date
    Jul 2014
    Posts
    4
    Rep Power
    0

    Retrieving unicode strings in a TPT export

    I'm using Attunity connectors 1.2 in a SSIS 2008R2 environment.

    When retrieving unicode strings with TPT export I've got no problems when the access mode is Table name.

    But when I switch from Table Name to SQL command as Teradata Source I always get a

    TPT Export error encountered during Initiate phase. TPTAPI_INFRA: API306: Error: conflicting data length for column (2) - Process Type. Source column'sdata length is (20) Target column's data length is (30).

    I know this has to do with UTF8 - UTF16 character sets but while the Table name source works the sql command does not.
    Is there a workaround because sql commands are necessary to retrieve my data.
    If the column to be retrieved has a character set LATIN, there's no problem but when I switch to unicode I get the error.

    Who can help ma out?

  2. #2
    sav
    sav is offline Junior Member
    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    Retrieving unicode strings in a TPT export

    Quote Originally Posted by deboopi View Post
    I'm using Attunity connectors 1.2 in a SSIS 2008R2 environment.

    Who can help ma out?
    Hi! I do not know the answer, but Attunity have real problems with UNICODE in SQL-command TPT Export mode.

    I have 2 users (sysdba, dev99_tst) in Teradata 13.10 with the same collation(MULTINATIONAL) and default character set(UNICODE).
    I've made a table with 4 columns and 2 rows and trying to retrieve data from it.
    If I specify sysdba user in Teradata connection all goes fine, if I specify dev99_tst package fails.
    Right after credentionals change "Teradata Source" marks invalid and tries to automaticaly fix itself.
    It makes output columns types DT_STR instead of DT_WSTR as it was with sysdba. That's incorect because the charset is UNICODE.
    If I try to execute the package it fails with error TPT12108: Output Schema does not match data from SELECT statement.
    The TPT export log shows the only difference with the size of "cast_field".
    Under sysdba Attunity counts it as 408, and under dev99_tst only 272.
    Both connection strings are equal and uses CHARSET=UTF8.
    I've found two reciepts: to cast this field, to translate it to LATIN.
    In first case it fails with the same error. In the second case it not fail but looses regional characters (the result is "?????????????").


    Dear Attunity developers, help us to solve the problem!

    deboopi! Try to translate it like I've done it. If you have only english characters it may help you.


    CREATE MULTISET TABLE dev99.TST ,NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
    (
    TST_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
    TST_VARCHAR VARCHAR(100) CHARACTER SET UNICODE CASESPECIFIC NOT NULL,
    TST_INT INTEGER NOT NULL,
    TST_FLOAT FLOAT)
    PRIMARY INDEX ( TST_DATE );

    select ''|| '001B'XC||coalesce(TST_VARCHAR,'')|| '001B'XC||coalesce(TST_INT,'')|| '001B'XC||coalesce(TST_FLOAT,'') as cast_field from dev99.TST
    select cast(''|| '001B'XC||coalesce(TST_VARCHAR,'')|| '001B'XC||coalesce(TST_INT,'')|| '001B'XC||coalesce(TST_FLOAT,'') as varchar(4000)) as cast_field from dev99.TST
    select TRANSLATE(''|| '001B'XC||coalesce(TST_VARCHAR,'')|| '001B'XC||coalesce(TST_INT,'')|| '001B'XC||coalesce(TST_FLOAT,'') USING UNICODE_TO_LATIN WITH ERROR)as cast_field from dev99.TST

    CREATE USER "DEV99_TST" FROM "SYSDBA"
    AS PERM = 0
    PASSWORD = ***
    ACCOUNT = ('')
    STARTUP = ''
    NO FALLBACK
    NO BEFORE JOURNAL
    NO AFTER JOURNAL
    COLLATION = MULTINATIONAL
    DEFAULT CHARACTER SET UNICODE
    DATEFORM=INTEGERDATE
    TIME ZONE=NULL
    ;
    grant all on DEV99.TST to DEV99_TST;

  3. #3
    deboopi is offline Junior Member
    Join Date
    Jul 2014
    Posts
    4
    Rep Power
    0
    Quote Originally Posted by sav View Post
    Hi! I do not know the answer, but Attunity have real problems with UNICODE in SQL-command TPT Export mode.

    I have 2 users (sysdba, dev99_tst) in Teradata 13.10 with the same collation(MULTINATIONAL) and default character set(UNICODE).
    I've made a table with 4 columns and 2 rows and trying to retrieve data from it.
    If I specify sysdba user in Teradata connection all goes fine, if I specify dev99_tst package fails.
    Right after credentionals change "Teradata Source" marks invalid and tries to automaticaly fix itself.
    It makes output columns types DT_STR instead of DT_WSTR as it was with sysdba. That's incorect because the charset is UNICODE.
    If I try to execute the package it fails with error TPT12108: Output Schema does not match data from SELECT statement.
    The TPT export log shows the only difference with the size of "cast_field".
    Under sysdba Attunity counts it as 408, and under dev99_tst only 272.
    Both connection strings are equal and uses CHARSET=UTF8.
    I've found two reciepts: to cast this field, to translate it to LATIN.
    In first case it fails with the same error. In the second case it not fail but looses regional characters (the result is "?????????????").


    Dear Attunity developers, help us to solve the problem!

    deboopi! Try to translate it like I've done it. If you have only english characters it may help you.


    CREATE MULTISET TABLE dev99.TST ,NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
    (
    TST_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
    TST_VARCHAR VARCHAR(100) CHARACTER SET UNICODE CASESPECIFIC NOT NULL,
    TST_INT INTEGER NOT NULL,
    TST_FLOAT FLOAT)
    PRIMARY INDEX ( TST_DATE );

    select ''|| '001B'XC||coalesce(TST_VARCHAR,'')|| '001B'XC||coalesce(TST_INT,'')|| '001B'XC||coalesce(TST_FLOAT,'') as cast_field from dev99.TST
    select cast(''|| '001B'XC||coalesce(TST_VARCHAR,'')|| '001B'XC||coalesce(TST_INT,'')|| '001B'XC||coalesce(TST_FLOAT,'') as varchar(4000)) as cast_field from dev99.TST
    select TRANSLATE(''|| '001B'XC||coalesce(TST_VARCHAR,'')|| '001B'XC||coalesce(TST_INT,'')|| '001B'XC||coalesce(TST_FLOAT,'') USING UNICODE_TO_LATIN WITH ERROR)as cast_field from dev99.TST

    CREATE USER "DEV99_TST" FROM "SYSDBA"
    AS PERM = 0
    PASSWORD = ***
    ACCOUNT = ('')
    STARTUP = ''
    NO FALLBACK
    NO BEFORE JOURNAL
    NO AFTER JOURNAL
    COLLATION = MULTINATIONAL
    DEFAULT CHARACTER SET UNICODE
    DATEFORM=INTEGERDATE
    TIME ZONE=NULL
    ;
    grant all on DEV99.TST to DEV99_TST;
    Well using translate with a LATIN character set works but when unicode characters are in the result set, the problem of retrieving them still exists.
    The best solution I can come up with right now is to use views instead of sql statements and then I can retrieve unicode characters without problems.
    But this requires the use of views.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •