2 Replies Latest reply: Apr 9, 2013 12:41 PM by dbomp RSS

    Getting an "Integer" logical type to stick

    dbomp
      I created my identity-style primary keys in the logical model as logical type "Integer". On the relational model it's still "Integer", and for Oracle it created them as "number(38,0)" (or as SQL Developer tells me, "number(*,0)"). This is a good result, and I like it. My trouble is how to compare my model to a created database without it reporting that the data types are different and wanting to recreate the tables.

      Maybe it's a problem with how I'm doing the comparison, so I'll spell out my steps:

      1) File->Import->Data Dictionary
      2) I pick my connection, and [Next >].
      3) I pick my schema, click "Swap Target Model" to on, and leave the "Import To" as my relational model and "Oracle Database 11g" as the database. [Next >]
      4) My tables are already clicked, so I leave them. [Next >]
      5) It reports that I have changes in my tables, and there are three in the list:
      a) The "Data Type" for the column in question in the model is "INTEGER", and the table is "NUMBER". It's checkmarked as a difference.
      b) The "Data Type Kind" for the model is "Logical Type(Integer)", and the table is "Logical Type(NUMERIC)". (This one isn't checkmarked because I turned off the "Compare Option" for "Use 'Data Type Kind' Property".)
      c) Under "Indexes", it wants "To Drop" my primary key (index). That's odd, because it still lists the primary key under "PK and UK Constraints". It's not really a problem and I just ignore it, but I wonder if it's related to the data type issue.

      If I do "DDL Preview", it's (leaving out some of the storage and logging options):
      ALTER TABLE CAM.ADDRESSES DROP CONSTRAINT ADDRESSES_PK CASCADE ;
      
      DROP INDEX CAM.ADDRESSES_PK
      ;
      DROP TRIGGER CAM.ADDRESSES_ROW_ID_TRG 
      ;
      ALTER TABLE CAM.ADDRESSES RENAME TO bcp_ADDRESSES 
      ;
      CREATE TABLE CAM.ADDRESSES 
          ( 
           ROW_ID INTEGER  NOT NULL , 
      [...]
          );
      INSERT INTO CAM.ADDRESSES 
          (ROW_ID ,[...] )
      SELECT 
          ROW_ID , [...]
      FROM 
          bcp_ADDRESSES 
      ;
      CREATE UNIQUE INDEX CAM.ADDRESSES_PK ON CAM.ADDRESSES 
          ( 
           ROW_ID ASC 
          ) [...]
      ;
      
      ALTER TABLE CAM.ADDRESSES 
          ADD CONSTRAINT ADDRESSES_PK PRIMARY KEY ( ROW_ID  ) 
          USING INDEX CAM.ADDRESSES_PK ;
      So it looks like it'd create the column as an "INTEGER" again, so on the next attempt I'd be back where I started.

      In the "Types Administration" I tried changing the logical type mapping to "number(38,0)" and then "number(38)", but that didn't help. I didn't write down how it reported the difference -- I think it was that "number(38)" was different from "number". If I changed the type mapping to "number", then it stopped reporting the data type as a difference -- but then it wanted to create the columns as "number", which has a scale, and I'd rather not have one.

      Any suggestions on how to get it to leave this alone?

      (Version 3.3.0.747. I think that when I was running 3.1.4, it just wanted to
      alter column addresses modify (row_id integer)
      , which was annoying but I could just delete all of those lines. Now that it wants to recreate the table altogether, it's harder to work around. But maybe the table recreation is due to the index business, which it didn't bother me about before.)
        • 1. Re: Getting an "Integer" logical type to stick
          Philip Stoyanov-Oracle
          Hi,

          thanks for feedback.
          Now that it wants to recreate the table altogether
          Storage properties are tracked also in DM 3.3.0.747 - change in data type can lead to "recreate table" sequence if you explicitly set it for that table in "Data type conversion" tab. Some changes in storage properties (there is a "Storage properties" tab you can check it)
          can lead to such behavior. You can check "Tabular view" tab (next to "Tree view" tab) - there is a " Generate report " button and you can generate report about changes - the easiest way to see them all.
          About Integer data type - it won't report difference if logical type integer is mapped to Integer data type or to Number(38). We'll improve it to cover mapping to Number as well.

          Philip
          • 2. Re: Getting an "Integer" logical type to stick
            dbomp
            Thanks for the quick reply and about the storage properties. I'll check into that.
            About Integer data type - it won't report difference if logical type integer is mapped to Integer data type or to Number(38). We'll improve it to cover mapping to Number as well.
            I just did some experiments and learned something about Oracle today. If you create a column as type "INTEGER" and look at the table in SQL Developer (3.0.04)'s Schema Browser's "Columns" tab, it says that the column is of type "NUMBER(38,0)", but it isn't. The USER_TAB_COLUMNS view says that the DATA_PRECISION is null, not 38, with DATA_SCALE 0. That explains why the Modeler sees the model's "INTEGER" or "NUMBER(38)" is different from the database column's precision-less data type. If the database column is explicitly created as a "NUMBER(38,0)", and USER_TAB_COLUMNS.DATA_PRECISION is really 38, the Modeler reports that as type "NUMBER(38)", which is sees as the same as the model's "INTEGER". Very interesting.