Data Integration

 View Only
Expand all | Collapse all

IIDR: step by step process to add 1000 plus tables to the subscription

  • 1.  IIDR: step by step process to add 1000 plus tables to the subscription

    Posted Mon October 14, 2024 05:58 PM

    Hello,

    I need to create an IIDR Subscription and add more than 1000 tables to the Sub. I got the following script, however, i don't have script to get the table names from the .txt file and fill into the following command. IIDR import is not working as i don't know how to create xml file. Please let me know the process to add 1000+ tables (both source and target) to the subscription

    Thanks

    CHCCLP ADD SUBSCRIPTION <subscription_name> FOR DATASOURCES SOURCE <source_db_name> TARGET <target_db_name>;

    -- Step 4: Bulk Add Tables (Replace with table names or automate this with a script)
    -- Loop to add 2000 tables
    -- Example for 3 tables, extend for 2000
    CHCCLP ADD TABLE MAPPING IN SUBSCRIPTION <subscription_name> FOR SCHEMA <source_schema> TABLE <table_1> TO TARGET TABLE <target_schema>.<target_table_1>;
    CHCCLP ADD TABLE MAPPING IN SUBSCRIPTION <subscription_name> FOR SCHEMA <source_schema> TABLE <table_2> TO TARGET TABLE <target_schema>.<target_table_2>;
    CHCCLP ADD TABLE MAPPING IN SUBSCRIPTION <subscription_name> FOR SCHEMA <source_schema> TABLE <table_3> TO TARGET TABLE <target_schema>.<target_table_3>;



    ------------------------------
    Muhammad Khan
    ------------------------------


  • 2.  RE: IIDR: step by step process to add 1000 plus tables to the subscription

    Posted Tue October 15, 2024 01:24 AM

    Hi Muhammad,

    I don't know much about Subscription, but if you have a relational database as a source or target, then you could query the data dictionary/database catalog of that database to generate the script.

    Example for DB2:

    SELECT
    'CHCCLP ADD TABLE MAPPING IN SUBSCRIPTION SUBSCRIPTION_' || NAME || ' FOR SCHEMA ' || CREATOR || ' TABLE ' || NAME || ' TO TARGET TABLE ' || CREATOR || '.' || NAME || ';'
    FROM SYSIBM.SYSTABLES
    WHERE 1=1
    --AND CREATOR in ()
    --AND TABLENAME in/like ()
    --ADD a WHERE condition here to filter your tables
    ;

    This assumes that schemas and tablenames are the same in source and target, if not you could connect the tables via federation (if it is DB2) and query both data dictionaries. Or you export the information of your data dictionary and generate the script with a shell script.



    ------------------------------
    Ralf Martin
    Principal Consultant
    infologistix GmbH
    Bregenz
    ------------------------------

    Original Message


  • 3.  RE: IIDR: step by step process to add 1000 plus tables to the subscription

    Posted Sat October 19, 2024 10:20 PM
    Edited by Lorraine Rizzuto 8 days ago

    Great question, Muhammad! Ralf's suggestion using the data dictionary is a great start. You can also explore tools like IIDR Design Studio for a more visual approach to building subscriptions. For a more hands-on approach, you might find a video tutorial helpful. A quick search for such as how to use Bunn coffee maker on platforms like YouTube can provide step-by-step guidance.



    ------------------------------
    Carla Price
    ------------------------------

    Original Message