Load Ingest Data
Results 1 to 9 of 9

Thread: SQL Server 2012 SP1 CTP4 - Attunity CDC v 1.1

  1. #1
    alvinr is offline Junior Member
    Join Date
    Mar 2012
    Posts
    12
    Rep Power
    0

    SQL Server 2012 SP1 CTP4 - Attunity CDC v 1.1

    I got this version from microsoft sql server 2012 SP1 CTP4 feature download site. Everything seems to work but when I do an update in Oracle, I am not getting the primary key column come in. I can see the changes, I can see the __$operation as a 3 and 4 but the primary key column is null. I would think that's an issue since I don't know which row to update. Also this table in Oracle is partitioned. I had submitted another issue about partitioned tables a few months ago but that issue seems to be fixed in this version.

  2. #2
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi,

    It sounds like you didn't run the supplemental logging scripts. To get or run the supplemental logging scripts, open the CDC instance properties window, Scripts tab (Include all tables/capture instances option).

    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    alvinr is offline Junior Member
    Join Date
    Mar 2012
    Posts
    12
    Rep Power
    0
    Sample Table inOracle

    CREATE TABLE DOC_TABLE"
    (
    "CMPL_DOC_ID" NUMBER, -----Primary Key
    "FOLDER_ID" NUMBER,
    "DEALER_ID" VARCHAR2(20 BYTE),
    "TEMPLATE_ID" NUMBER(10,0),
    "TRANSACTION_ID" VARCHAR2(50 BYTE),
    "TRANSACTION_ID_TYPE" VARCHAR2(10 BYTE),
    "CMPL_DOC_STATUS" VARCHAR2(1 BYTE),
    "DOC_STATUS" VARCHAR2(20 BYTE),
    "DOC_LOCATION" VARCHAR2(1 BYTE),
    "PROCESSING_CODE" VARCHAR2(20 BYTE),
    "CREATED_DATE" DATE DEFAULT sysdate
    )
    When a change happens on any column, I am geting just that column changed data. I would expect at least the primary key would come over with it. Without the pri. key, the changed row has no value since I don't know which column to update. Insert works, update has an issue.
    Also this table is partitioned on CREATED_DATE

    Ron

  4. #4
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Again, you don't have supplemental logging enabled. You should run the supplemental logging scripts.


    Regards,


    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  5. #5
    alvinr is offline Junior Member
    Join Date
    Mar 2012
    Posts
    12
    Rep Power
    0

    Here is my Log Script...I don't think I am missing anything.

    EXECUTE IMMEDIATE ('ALTER TABLE "X"."DOC_TABLE" LOGGING');
    DECLARE GroupExists NUMBER;
    BEGIN
    SELECT COUNT(*) INTO GroupExists FROM DBA_LOG_GROUPS WHERE LOG_GROUP_NAME = 'MS_DOC_TABLE508971eb_00' AND OWNER = 'XTRA' AND TABLE_NAME = 'DOC_TABLE' ;
    IF GroupExists = 1 THEN
    EXECUTE IMMEDIATE ('ALTER TABLE "X"."DOC_TABLE" DROP SUPPLEMENTAL LOG GROUP "MS_DOC_TABLE508971eb_00"');
    END IF;
    DECLARE GroupExists NUMBER;
    BEGIN
    SELECT COUNT(*) INTO GroupExists FROM DBA_LOG_GROUPS WHERE LOG_GROUP_NAME = 'MS_DOC_TABLE508971eb_01' AND OWNER = 'XTRA' AND TABLE_NAME = 'DOC_TABLE' ;
    IF GroupExists = 1 THEN
    EXECUTE IMMEDIATE ('ALTER TABLE "X"."DOC_TABLE" DROP SUPPLEMENTAL LOG GROUP "MS_DOC_TABLE508971eb_01"');
    END IF;
    EXECUTE IMMEDIATE ('ALTER TABLE "X"."DOC_TABLE" ADD SUPPLEMENTAL LOG GROUP "MS_DOC_TABLE508971eb_00" ("CMPL_DOC_ID","FOLDER_ID","DEALER_ID","TEMPLATE_I D","TRANSACTION_ID",
    "TRANSACTION_ID_TYPE","CMPL_DOC_STATUS","DOC_STATU S","DOC_LOCATION","PROCESSING_CODE","CREATED_DATE" ) ALWAYS');
    END;

    We can even do a gotomeeting and I can show you what's going on.
    Last edited by alvinr; 11-05-2012 at 04:15 PM.

  6. #6
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi Ron,

    Please run the following two queries and let me know the results:

    SELECT supplemental_log_data_min, SUPPLEMENTAL_LOG_DATA_PK FROM v$database;

    select c.column_name, g.always, c.log_group_name
    from ALL_LOG_GROUP_COLUMNS c, ALL_LOG_GROUPS g
    where
    c.owner = g.owner and c.log_group_name=g.log_group_name and c.table_name=g.table_name
    and c.owner='X'
    and c.table_name = 'DOC_TABLE';


    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  7. #7
    alvinr is offline Junior Member
    Join Date
    Mar 2012
    Posts
    12
    Rep Power
    0
    Quote Originally Posted by alvinr View Post
    Sample Table inOracle

    CREATE TABLE DOC_TABLE"
    (
    "CMPL_DOC_ID" NUMBER, -----Primary Key
    "FOLDER_ID" NUMBER,
    "DEALER_ID" VARCHAR2(20 BYTE),
    "TEMPLATE_ID" NUMBER(10,0),
    "TRANSACTION_ID" VARCHAR2(50 BYTE),
    "TRANSACTION_ID_TYPE" VARCHAR2(10 BYTE),
    "CMPL_DOC_STATUS" VARCHAR2(1 BYTE),
    "DOC_STATUS" VARCHAR2(20 BYTE),
    "DOC_LOCATION" VARCHAR2(1 BYTE),
    "PROCESSING_CODE" VARCHAR2(20 BYTE),
    "CREATED_DATE" DATE DEFAULT sysdate
    )
    When a change happens on any column, I am geting just that column changed data. I would expect at least the primary key would come over with it. Without the pri. key, the changed row has no value since I don't know which column to update. Insert works, update has an issue.
    Also this table is partitioned on CREATED_DATE

    Ron
    Query1
    SELECT supplemental_log_data_min, SUPPLEMENTAL_LOG_DATA_PK FROM v$database;
    --->Output NO NO


    Query2

    "COLUMN_NAME" "ALWAYS" "LOG_GROUP_NAME" "OWNER" "TABLE_NAME"
    "CMPL_DOC_ID" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "FOLDER_ID" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "DEALER_ID" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "TEMPLATE_ID" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "TRANSACTION_ID" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "TRANSACTION_ID_TYPE" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "CMPL_DOC_STATUS" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "DOC_STATUS" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "DOC_LOCATION" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "PROCESSING_CODE" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    "CREATED_DATE" "ALWAYS" "MS_CMPL_DOC_INDEX508971eb_00" "X" "DOC_TABLE"
    Last edited by alvinr; 11-06-2012 at 05:02 PM.

  8. #8
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi Ron,

    Database level supplemental logging is not enabled on your source database. To addd minimal database level supplemental logging you can run:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  9. #9
    alvinr is offline Junior Member
    Join Date
    Mar 2012
    Posts
    12
    Rep Power
    0
    Quote Originally Posted by Gadi.Farhat View Post
    Hi Ron,

    Database level supplemental logging is not enabled on your source database. To addd minimal database level supplemental logging you can run:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


    Regards,

    Gadi
    ---Thanks. That worked...I was missing the alter database.

Posting Permissions

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