[teiid-issues] [JBoss JIRA] (TEIID-5858) Orcale collation issue with Teiid 9.1.1

[Steven Hawkins (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5858?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13820533#comment-13820533 ] 

Steven Hawkins commented on TEIID-5858:
---------------------------------------

Setting the java system property should be sufficient.  In later versions you can also use an environment variable.

You can also use EmbeddedConfiguration.getProperties to see/set property values.

> Orcale collation issue with Teiid 9.1.1
> ---------------------------------------
>
>                 Key: TEIID-5858
>                 URL: https://issues.jboss.org/browse/TEIID-5858
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 9.1.1
>            Reporter: Ivan Chan
>            Assignee: Steven Hawkins
>            Priority: Major
>
> One of customers run into this following error:
>     org.teiid.core.TeiidComponentException: TEIID31202 Detected that an already sorted set of values was not in the expected order (typically UTF-16 / UCS-2). Please check the translator settings to ensure character columns used for joining are sorted as expected.
> And they were trying to join multiple oracle databases together. And they are running Teiid 9.1.1
> It seems like I need to change the collation. And I tried to change those values, but it didn't seem to do anything.
> org.teiid.requireTeiidCollation
> org.teiid.collationLocale
> org.teiid.assumeMatchingCollation
> Here is how we create the tables in Oracle:
> CREATE TABLE "DWH_MSFL"."MW_CATEGORY" CREATE TABLE "DWH_MSFL"."MW_CATEGORY"  ( "CAT_ID" NUMBER(*,0),  "CAT_TITLE" VARCHAR2(255 CHAR),  "CAT_TITLE_ORIGIN" VARCHAR2(255 CHAR),  "CAT_PAGES" NUMBER(*,0),  "CAT_SUBCATS" NUMBER(*,0),  "CAT_FILES" NUMBER(*,0) ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DWH_MSFL" ;
>  
>  
> --------------------------
>  
> CREATE TABLE "DWH_MSFL"."MW_CATEGORYLINKS" CREATE TABLE "DWH_MSFL"."MW_CATEGORYLINKS"     ( "CL_FROM" NUMBER(*,0),   "CL_TO" VARCHAR2(255 CHAR),   "CL_SORTKEY" VARCHAR2(230 CHAR),   "CL_TIMESTAMP" TIMESTAMP (0),   "CL_SORTKEY_PREFIX" VARCHAR2(255 CHAR),   "CL_COLLATION" VARCHAR2(32 CHAR),   "CL_TYPE" VARCHAR2(10 CHAR)    ) SEGMENT CREATION IMMEDIATE    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255   NOCOMPRESS NOLOGGING   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "DWH_MSFL" ;
> Notwithstanding, can't the issue somehow be related to  categorylinks>category relation or Column-Level Collation (CI_COLLATION)which is set to Uppercase(see insert to categorylinks table in DWH_MSFL.sql). We have an issue exactly with a category and title column(CAT_TITLE)



--
This message was sent by Atlassian Jira
(v7.13.8#713008)