Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Chennai India
    Posts
    2

    Question Unanswered: How to update tables present in different databases using SQL's

    We have 2 databases DBx and DBy.

    Currently we have configured a table from DBy using federated
    objects(user mappings & nicknames)
    which can be queried directly from DBx.

    We are updating the table of DBy from DBx using set passthru option as enclosed below

    SET var_sql = 'SET PASSTHRU DBy';
    EXECUTE IMMEDIATE var_sql;

    SET var_sql = 'INSERT INTO CNTL.BATCH_LOG(COMMENT_TXT) VALUES(' || CHR(39)
    || 'Error from MMTEST3' || CHR(39) || ')';
    EXECUTE IMMEDIATE var_sql;

    SET var_sql = 'SET PASSTHRU RESET';
    EXECUTE IMMEDIATE var_sql;

    COMMIT


    But this option works using stored procedures alone, is there anyway that we can update the table of dby using SQLS


    thanks in advance
    Rangarjan

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to update tables present in different databases using SQL's

    Currently, Federated databases are Read Only ... You cannot perform insert or update ...

    HTH

    Cheers

    Sathyaram

    Originally posted by Ranga_aryan
    We have 2 databases DBx and DBy.

    Currently we have configured a table from DBy using federated
    objects(user mappings & nicknames)
    which can be queried directly from DBx.

    We are updating the table of DBy from DBx using set passthru option as enclosed below

    SET var_sql = 'SET PASSTHRU DBy';
    EXECUTE IMMEDIATE var_sql;

    SET var_sql = 'INSERT INTO CNTL.BATCH_LOG(COMMENT_TXT) VALUES(' || CHR(39)
    || 'Error from MMTEST3' || CHR(39) || ')';
    EXECUTE IMMEDIATE var_sql;

    SET var_sql = 'SET PASSTHRU RESET';
    EXECUTE IMMEDIATE var_sql;

    COMMIT


    But this option works using stored procedures alone, is there anyway that we can update the table of dby using SQLS


    thanks in advance
    Rangarjan

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605

    Re: How to update tables present in different databases using SQL's

    Hi,

    In DB2 version 7.2 is only available select from federated database to remote database. In DB2 version 8.1 is also available select, insert, update and delete from federated database to remote database

    Grofaty

    Originally posted by sathyaram_s
    Currently, Federated databases are Read Only ... You cannot perform insert or update ...

    HTH

    Cheers

    Sathyaram

Posting Permissions

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