This discussion is archived
9 Replies Latest reply: Sep 9, 2013 12:42 AM by ChrisJenkins RSS

TT5121 error - Non standard type mapping?

AndréLopes Newbie
Currently Being Moderated

Hi all,

 

I'm creating cache groups on the TT database, but for some tables I'm getting the following error:

 

TT5121: Non standard type mapping for column [Schema].[Table].[Column], cache operations are restricted.

 

I'm creating the tables on TT using the Oracle tables DDL, so I quite don't understand where's the problem. I found another thread here on OTN with a similar problem, but it didn't help me. Both TT and Oracle have the UTF8 Character Set.

 

Can anyone help?

 

Thanks,

André

  • 1. Re: TT5121 error - Non standard type mapping?
    ChrisJenkins Guru
    Currently Being Moderated

    Can you please post the actual DDL highlighting one of the problem columns. It's hard to make any useful comment based only on an error message...

     

    Chris

  • 2. Re: TT5121 error - Non standard type mapping?
    AndréLopes Newbie
    Currently Being Moderated

    Hi Chris,

     

    The DDL for the 11g source table:

     

    CREATE TABLE DMTCMT_AT.DIM_ADDRESS

    (

      ADDRESS_ID                      NUMBER(19)    NOT NULL,

      ADDRESS_CODE                    VARCHAR2(40 CHAR),

      ADDRESS_TYPE                    VARCHAR2(32 CHAR),

      STREETNAME                      VARCHAR2(64 CHAR),

      HOUSE_NUMBER                    VARCHAR2(16 CHAR),

      HOUSE_NUMBER_EXTENSION          VARCHAR2(8 CHAR),

      HOUSE_NAME                      VARCHAR2(64 CHAR),

      FLAT_NUMBER                     VARCHAR2(16 CHAR),

      FLOOR_NUMBER                    VARCHAR2(16 CHAR),

      STAIR_CASE                      VARCHAR2(16 CHAR),

      ZIP_CODE                        VARCHAR2(8 CHAR),

      ZIP_CODE_WITHOUT_EXTENSION      VARCHAR2(8 CHAR),

      ZIP_CODE_EXTENSION              VARCHAR2(8 CHAR),

      DISTRICT_NAME                   VARCHAR2(64 CHAR),

      CITY_NAME                       VARCHAR2(64 CHAR),

      COMMUNITY_NAME                  VARCHAR2(64 CHAR),

      REGION_NAME                     VARCHAR2(64 CHAR),

      COUNTRY_NAME                    VARCHAR2(64 CHAR),

      PO_BOX_NUMBER                   VARCHAR2(16 CHAR),

      PO_BOX_TYPE                     VARCHAR2(16 CHAR),

      X_COORDINATE                    VARCHAR2(16 CHAR),

      Y_COORDINATE                    VARCHAR2(16 CHAR),

      IS_COMPLETE                     VARCHAR2(1 CHAR),

      DSL_FRANCHISE_ID                NUMBER(19),

      CABLE_FRANCHISE_ID              NUMBER(19),

      OBJECT_SCD1_WID                 NUMBER(19),

      OBJECT_WID                      NUMBER,

      EXTERNAL_ID                     VARCHAR2(255 BYTE),

      VALID_FOR_START_DATE_TIME       DATE,

      VALID_FOR_END_DATE_TIME         DATE,

      META_VALID_FOR_START_DATE_TIME  DATE,

      META_VALID_FOR_END_DATE_TIME    DATE,

      META_DATASOURCE_ID              NUMBER(19),

      META_CREATION_DATE              DATE,

      META_IS_CURRENT                 VARCHAR2(1 BYTE),

      META_IS_VALID                   VARCHAR2(1 BYTE),

      META_IS_ACTIVE                  VARCHAR2(1 BYTE),

      META_BUSINESS_UNIT_ID           NUMBER(19),

      META_COUNTRY_CODE               VARCHAR2(3 BYTE) NOT NULL,

      META_PROCESS_ID                 NUMBER(19),

      META_MODIFICATION_DATE          DATE,

      MD5_CRC                         VARCHAR2(32 BYTE),

      IS_SELF_INSTALL                 VARCHAR2(1 BYTE),

      SELF_INSTALL_DATE               DATE,

      BUSINESS_FRANCHISE_ID           NUMBER(20)

    )

    TABLESPACE CDMDWHPE

    PCTUSED    0

    PCTFREE    10

    INITRANS   1

    MAXTRANS   255

    STORAGE    (

                INITIAL          64K

                NEXT             1M

                MINEXTENTS       1

                MAXEXTENTS       UNLIMITED

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               )

    LOGGING

    NOCOMPRESS

    NOCACHE

    NOPARALLEL

    MONITORING

    ENABLE ROW MOVEMENT;

     

    The DDL for the TT cache group:

     

    CREATE READONLY CACHE GROUP DIM_ADDRESS_AT FROM DMTCMT_AT.DIM_ADDRESS

    ( ADDRESS_ID                      NUMBER(19)    NOT NULL,

      ADDRESS_CODE                    VARCHAR2(40 CHAR),

      ADDRESS_TYPE                    VARCHAR2(32 CHAR),

      STREETNAME                      VARCHAR2(64 CHAR),

      HOUSE_NUMBER                    VARCHAR2(16 CHAR),

      HOUSE_NUMBER_EXTENSION          VARCHAR2(8 CHAR),

      HOUSE_NAME                      VARCHAR2(64 CHAR),

      FLAT_NUMBER                     VARCHAR2(16 CHAR),

      FLOOR_NUMBER                    VARCHAR2(16 CHAR),

      STAIR_CASE                      VARCHAR2(16 CHAR),

      ZIP_CODE                        VARCHAR2(8 CHAR),

      ZIP_CODE_WITHOUT_EXTENSION      VARCHAR2(8 CHAR),

      ZIP_CODE_EXTENSION              VARCHAR2(8 CHAR),

      DISTRICT_NAME                   VARCHAR2(64 CHAR),

      CITY_NAME                       VARCHAR2(64 CHAR),

      COMMUNITY_NAME                  VARCHAR2(64 CHAR),

      REGION_NAME                     VARCHAR2(64 CHAR),

      COUNTRY_NAME                    VARCHAR2(64 CHAR),

      PO_BOX_NUMBER                   VARCHAR2(16 CHAR),

      PO_BOX_TYPE                     VARCHAR2(16 CHAR),

      X_COORDINATE                    VARCHAR2(16 CHAR),

      Y_COORDINATE                    VARCHAR2(16 CHAR),

      IS_COMPLETE                     VARCHAR2(1 CHAR),

      DSL_FRANCHISE_ID                NUMBER(19),

      CABLE_FRANCHISE_ID              NUMBER(19),

      OBJECT_SCD1_WID                 NUMBER(19),

      OBJECT_WID                      NUMBER,

      EXTERNAL_ID                     VARCHAR2(255 BYTE),

      VALID_FOR_START_DATE_TIME       DATE,

      VALID_FOR_END_DATE_TIME         DATE,

      META_VALID_FOR_START_DATE_TIME  DATE,

      META_VALID_FOR_END_DATE_TIME    DATE,

      META_DATASOURCE_ID              NUMBER(19),

      META_CREATION_DATE              DATE,

      META_IS_CURRENT                 VARCHAR2(1 BYTE),

      META_IS_VALID                   VARCHAR2(1 BYTE),

      META_IS_ACTIVE                  VARCHAR2(1 BYTE),

      META_BUSINESS_UNIT_ID           NUMBER(19),

      META_COUNTRY_CODE               VARCHAR2(3 BYTE) NOT NULL,

      META_PROCESS_ID                 NUMBER(19),

      META_MODIFICATION_DATE          DATE,

      MD5_CRC                         VARCHAR2(32 BYTE),

      IS_SELF_INSTALL                 VARCHAR2(1 BYTE),

      SELF_INSTALL_DATE               DATE,

      BUSINESS_FRANCHISE_ID           NUMBER(20)

      PRIMARY KEY ("OBJECT_WID"));

     

    Thanks,

    André

  • 3. Re: TT5121 error - Non standard type mapping?
    AndréLopes Newbie
    Currently Being Moderated

    The error is on the ADDRESS_CODE column.

  • 4. Re: TT5121 error - Non standard type mapping?
    ChrisJenkins Guru
    Currently Being Moderated

    I can't see why you would get that error on that column. Maybe you are re-directign the output and thsi is causing the erro to appear out of place?

     

    What TimesTen version is this (ttVersion)?

     

    Anyway, when i run this using TimesTen 11.2.2.5.0 against Oracle DB 11.2.0.2 I get the followign errors (only) reported after the end of the create cache group statement:

     

    5120: No matching unique index with not null columns, unique key constraint with not null columns, or primary key constraint on table SCOTT.DIM_ADDRESS, cache operations are restricted

    5168: Restricted cache groups are deprecated

    5126: A system managed cache group cannot contain non-standard column type mapping

     

    Pretty clear what the problem(s) are...

     

    1.   In Oracle, the column OBJECT_WID must be declared as NOT NULL

     

    2.   In Oracle, OBJECT_WID must be declared as the tables primary key (or at the very least you must create a UNIQUE INDEX on this column)

     

    3.   In the cache group definition you must declare OBJECT_WID as NOT NULL

     

    When I fix those problems it works just fine. Can you try the same please.

     

    Chris

  • 5. Re: TT5121 error - Non standard type mapping?
    AndréLopes Newbie
    Currently Being Moderated

    I'm using the TT version 11.2.2.5.0 against the Oracle 11.2.0.3.

     

    I have the index+primary key, I didn't gave you the full create table statement. It's the following:

     

    CREATE TABLE DMTCMT_AT.DIM_ADDRESS

    (

      ADDRESS_ID                      NUMBER(19)    NOT NULL,

      ADDRESS_CODE                    VARCHAR2(40 CHAR),

      ADDRESS_TYPE                    VARCHAR2(32 CHAR),

      STREETNAME                      VARCHAR2(64 CHAR),

      HOUSE_NUMBER                    VARCHAR2(16 CHAR),

      HOUSE_NUMBER_EXTENSION          VARCHAR2(8 CHAR),

      HOUSE_NAME                      VARCHAR2(64 CHAR),

      FLAT_NUMBER                     VARCHAR2(16 CHAR),

      FLOOR_NUMBER                    VARCHAR2(16 CHAR),

      STAIR_CASE                      VARCHAR2(16 CHAR),

      ZIP_CODE                        VARCHAR2(8 CHAR),

      ZIP_CODE_WITHOUT_EXTENSION      VARCHAR2(8 CHAR),

      ZIP_CODE_EXTENSION              VARCHAR2(8 CHAR),

      DISTRICT_NAME                   VARCHAR2(64 CHAR),

      CITY_NAME                       VARCHAR2(64 CHAR),

      COMMUNITY_NAME                  VARCHAR2(64 CHAR),

      REGION_NAME                     VARCHAR2(64 CHAR),

      COUNTRY_NAME                    VARCHAR2(64 CHAR),

      PO_BOX_NUMBER                   VARCHAR2(16 CHAR),

      PO_BOX_TYPE                     VARCHAR2(16 CHAR),

      X_COORDINATE                    VARCHAR2(16 CHAR),

      Y_COORDINATE                    VARCHAR2(16 CHAR),

      IS_COMPLETE                     VARCHAR2(1 CHAR),

      DSL_FRANCHISE_ID                NUMBER(19),

      CABLE_FRANCHISE_ID              NUMBER(19),

      OBJECT_SCD1_WID                 NUMBER(19),

      OBJECT_WID                      NUMBER,

      EXTERNAL_ID                     VARCHAR2(255 BYTE),

      VALID_FOR_START_DATE_TIME       DATE,

      VALID_FOR_END_DATE_TIME         DATE,

      META_VALID_FOR_START_DATE_TIME  DATE,

      META_VALID_FOR_END_DATE_TIME    DATE,

      META_DATASOURCE_ID              NUMBER(19),

      META_CREATION_DATE              DATE,

      META_IS_CURRENT                 VARCHAR2(1 BYTE),

      META_IS_VALID                   VARCHAR2(1 BYTE),

      META_IS_ACTIVE                  VARCHAR2(1 BYTE),

      META_BUSINESS_UNIT_ID           NUMBER(19),

      META_COUNTRY_CODE               VARCHAR2(3 BYTE) NOT NULL,

      META_PROCESS_ID                 NUMBER(19),

      META_MODIFICATION_DATE          DATE,

      MD5_CRC                         VARCHAR2(32 BYTE),

      IS_SELF_INSTALL                 VARCHAR2(1 BYTE),

      SELF_INSTALL_DATE               DATE,

      BUSINESS_FRANCHISE_ID           NUMBER(20)

    )

    TABLESPACE CDMDWHPE

    PCTUSED    0

    PCTFREE    10

    INITRANS   1

    MAXTRANS   255

    STORAGE    (

                INITIAL          64K

                NEXT             1M

                MINEXTENTS       1

                MAXEXTENTS       UNLIMITED

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               )

    LOGGING

    NOCOMPRESS

    NOCACHE

    NOPARALLEL

    MONITORING

    ENABLE ROW MOVEMENT;

     

    CREATE UNIQUE INDEX DMTCMT_AT.DIM_ADDRESS_PK ON DMTCMT_AT.DIM_ADDRESS

    (OBJECT_WID)

    LOGGING

    TABLESPACE CDMDWHPE

    PCTFREE    10

    INITRANS   2

    MAXTRANS   255

    STORAGE    (

                INITIAL          64K

                NEXT             1M

                MINEXTENTS       1

                MAXEXTENTS       UNLIMITED

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               )

    NOPARALLEL;

     

    ALTER TABLE DMTCMT_AT.DIM_ADDRESS ADD (

      CONSTRAINT DIM_ADDRESS_PK

      PRIMARY KEY

      (OBJECT_WID)

      USING INDEX DMTCMT_AT.DIM_ADDRESS_PK);

     

    GRANT SELECT ON DMTCMT_AT.DIM_ADDRESS TO CACHEADM;

     

    The problem is that this happening to other several tables and I used the 11g tables DDL as source for the cache groups create statement. I'm not understanding what can be causing this....

     

    The goal here is to load information from two separate schemas in 11g (DMTCMT_IE and DMTCMT_AT) into TT and then create a view on another TT schema (DMTCMT_PE) that UNIONs ALL both tables. Both Data Marts share the exact same structure and I was able to create the caching group for DMTCMT_IE.DIM_ADDRESS but not for DMTCMT_AT.DIM_ADDRESS.

     

    Thanks,

    André

  • 6. Re: TT5121 error - Non standard type mapping?
    ChrisJenkins Guru
    Currently Being Moderated

    Hi Andre,

     

    As I stated in my previous reply, all columns that are part of the table's primary key must be declared as NOT NULL in Oracle. Simply having them as the primary key is not sufficient. TimesTen needs to be able to uniquely identify every row in a cached table and if the primary key column(s) in Oracle allow NULLs then this is not possible.

     

    Chris

  • 7. Re: TT5121 error - Non standard type mapping?
    AndréLopes Newbie
    Currently Being Moderated

    Set the primary key not null? You need to explicitly add that to the create table statement? That's really strange, because when you do the alter table to add the primary key, it automatically sets that field to not null, and that's the current status (just checked, and the column is not null).

     

    On top of that, I have a VM on my laptop with TT and 11g, and if I use the DDL that I showed you, it works perfectly...

     

    Thanks,

    André

  • 8. Re: TT5121 error - Non standard type mapping?
    ChrisJenkins Guru
    Currently Being Moderated

    Okay, I just verified your results with adding the primary key via ALTER TABLE. So something strange is occurring on your problem setup.

     

    I need to do some investigation; I will get back to you. We may need you to capture some diagnostics / information from your problem setup.

     

    Chris

  • 9. Re: TT5121 error - Non standard type mapping?
    ChrisJenkins Guru
    Currently Being Moderated

    Can you please do the following and post the results.

     

    Connect to the TimesTen database using ttIsql. Please use the same userid (UID), password (PWD) and Oracle password (OraclePWD) that you have been using previously.

     

    Issue the following commands within ttIsql:

     

    spool colinfo1.txt

    autocommit 0;

    passthrough 3;

     

    select

      atb.column_name,

      nvl(decode(data_type,

                 'NUMBER', 1,

                 'VARCHAR2', 2,

                 'LONG', 3,

                 'RAW', 4,

                 'LONG RAW', 5,

                 'CHAR', 6,

                 'DATE', 7,

                 'TIMESTAMP(0)', 8,

                 'TIMESTAMP(1)', 8,

                 'TIMESTAMP(2)', 8,

                 'TIMESTAMP(3)', 8,

                 'TIMESTAMP(4)', 8,

                 'TIMESTAMP(5)', 8,

                 'TIMESTAMP(6)', 8,

                 'TIMESTAMP(7)', 8,

                 'TIMESTAMP(8)', 8,

                 'TIMESTAMP(9)', 8,

                 'FLOAT', 9,

                 'NCHAR', 10,

                 'NVARCHAR2', 11,

                 'BINARY_DOUBLE', 12,

                 'BINARY_FLOAT', 13,

                 'CLOB', 14,

                 'BLOB', 15,

                 'NCLOB', 16,

                 -1)

          , -1) datatype,

      nvl(data_length, -1) datalen,

      nvl(data_precision,

          decode(data_type,

                 'NUMBER',  39 ,

                 'FLOAT',  39 ,

                 -1)) as prec,

      nvl(data_scale,

          decode(data_type,

                 'NUMBER',  128 ,

                 'FLOAT',  128 ,

                 -1)) as scale,

      decode(nullable, 'Y', 1, 0) isnullable,

      decode(nvl(char_used, 'B'), 'C', 1, 0) charsemantics,

      nvl(char_length, -1) charlength

    from sys.all_tab_columns atb

    where atb.table_name = 'DIM_ADDRESS'

    and atb.owner = 'DMTCMT_AT'

    order by 1;

     

    spool off

     

    repeat for the other table (that works) using a different spool filename (colinfo2.txt) and replacing DMTCMT_AT in the query with DMTCMT_IE.

     

    Post the contents of the two files indicating which is which.

     

    Thanks,

     

    Chris

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points