Federated Server

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Praveen_db2
    #1

    Federated Server

    Hi All
    DB2 8.1.3,Windows
    I have 2 databases in a single instance, say DB_1 and DB_2.I need to query
    a table from DB_1 in DB_2.I am try to use a nickname for it.But nickname
    creation is not possible until federated server is made.Please tell me how
    to create a federated server.Moreover , is there any thing extra which I
    need to buy for this?When I gave this command to create a server
    CREATE SERVER "TEST1"
    TYPE DB2/UDB
    VERSION 8.1
    WRAPPER DRDA
    AUTHORIZATION "SAM_USER" PASSWORD "SAM_PWD"
    OPTIONS
    (NODE 'TEST1'
    ,DBNAME 'TEST1'
    );
    It says
    SQL0204N "DRDA" is an undefined name. SQLSTATE=42704
    Do I need to buy something extra for this?
    Please advise.

    Praveen

    Tags: None
    • Knut Stolze
      #2
      Re: Federated Server

      Praveen_db2 wrote:
      Hi All
      DB2 8.1.3,Windows
      I have 2 databases in a single instance, say DB_1 and DB_2.I need to query
      a table from DB_1 in DB_2.I am try to use a nickname for it.But nickname
      creation is not possible until federated server is made.Please tell me how
      to create a federated server.Moreover , is there any thing extra which I
      need to buy for this?When I gave this command to create a server
      CREATE SERVER "TEST1"
      TYPE DB2/UDB
      VERSION 8.1
      WRAPPER DRDA
      AUTHORIZATION "SAM_USER" PASSWORD "SAM_PWD"
      OPTIONS
      (NODE 'TEST1'
      ,DBNAME 'TEST1'
      );
      It says
      SQL0204N "DRDA" is an undefined name. SQLSTATE=42704
      Do I need to buy something extra for this?
      Please advise.
      You have to perform the following steps:

      (1) Register a wrapper.
      A wrapper is nothing else than a library that implements the glue code
      between the DB2 engine and the remote data source. In your case, the
      remote data source happens to be the same machine/DB2 instance.
      DB2 comes with wrappers for all DB2 and Informix data sources. I'll
      have to purchase WebSphere Information Integrator for other systems.
      (2) Register a server.
      A server identifies a remote data source. This is a single database in
      DB2 terminology. In your case, we're talking about 'DB_1' (or 'TEST1').
      (3) Register a user mapping.
      The user connected to the federated server (where you want to create
      the nickname) may not be allowed to access the remote data source.
      Thus, you have to define the credentials that shall be used when
      accessing the data source. (And those credentials are specific for each
      user connecting the federated server.)
      (4) Register a nickname.
      A nickname is just a pointer referring to a table at the remote data
      source.

      Here is an example that should work for you, assuming that RMT_DB is the
      name of the remote database:

      CREATE WRAPPER drda;

      CREATE SERVER srv
      TYPE DB2/UDB
      VERSION '8'
      WRAPPER drda
      AUTHORIZATION "stolze" PASSWORD "passwd"
      OPTIONS (DBNAME 'rmt_db');

      CREATE USER MAPPING FOR USER SERVER srv
      OPTIONS ( REMOTE_AUTHID 'stolze', REMOTE_PASSWORD 'passwd' );

      CREATE NICKNAME n FOR srv.syscat.view s;

      SELECT * FROM n;

      --
      Knut Stolze
      DB2 Information Integration Development
      IBM Germany

        Comment

        • Praveen_db2
          #3
          Re: Federated Server

          Thanx a ton Knut

            Comment

            • The Maxx
              #4
              Re: Federated Server

              Plus you should set the FEDERATED database manager parameter to YES.
              (May be that was to obvious, but I didn't see it in the previous post.

              Regards,
              Max Cortez

              Knut Stolze wrote:
              Praveen_db2 wrote:
              >
              Hi All
              DB2 8.1.3,Windows
              I have 2 databases in a single instance, say DB_1 and DB_2.I need to query
              a table from DB_1 in DB_2.I am try to use a nickname for it.But nickname
              creation is not possible until federated server is made.Please tell me how
              to create a federated server.Moreover , is there any thing extra which I
              need to buy for this?When I gave this command to create a server
              CREATE SERVER "TEST1"
              TYPE DB2/UDB
              VERSION 8.1
              WRAPPER DRDA
              AUTHORIZATION "SAM_USER" PASSWORD "SAM_PWD"
              OPTIONS
              (NODE 'TEST1'
              ,DBNAME 'TEST1'
              );
              It says
              SQL0204N "DRDA" is an undefined name. SQLSTATE=42704
              Do I need to buy something extra for this?
              Please advise.
              >
              You have to perform the following steps:
              >
              (1) Register a wrapper.
              A wrapper is nothing else than a library that implements the glue code
              between the DB2 engine and the remote data source. In your case, the
              remote data source happens to be the same machine/DB2 instance.
              DB2 comes with wrappers for all DB2 and Informix data sources. I'll
              have to purchase WebSphere Information Integrator for other systems.
              (2) Register a server.
              A server identifies a remote data source. This is a single database in
              DB2 terminology. In your case, we're talking about 'DB_1' (or 'TEST1').
              (3) Register a user mapping.
              The user connected to the federated server (where you want to create
              the nickname) may not be allowed to access the remote data source.
              Thus, you have to define the credentials that shall be used when
              accessing the data source. (And those credentials are specific for each
              user connecting the federated server.)
              (4) Register a nickname.
              A nickname is just a pointer referring to a table at the remote data
              source.
              >
              Here is an example that should work for you, assuming that RMT_DB is the
              name of the remote database:
              >
              CREATE WRAPPER drda;
              >
              CREATE SERVER srv
              TYPE DB2/UDB
              VERSION '8'
              WRAPPER drda
              AUTHORIZATION "stolze" PASSWORD "passwd"
              OPTIONS (DBNAME 'rmt_db');
              >
              CREATE USER MAPPING FOR USER SERVER srv
              OPTIONS ( REMOTE_AUTHID 'stolze', REMOTE_PASSWORD 'passwd' );
              >
              CREATE NICKNAME n FOR srv.syscat.view s;
              >
              SELECT * FROM n;
              >
              --
              Knut Stolze
              DB2 Information Integration Development
              IBM Germany

                Comment

                • Knut Stolze
                  #5
                  Re: Federated Server

                  The Maxx wrote:
                  Plus you should set the FEDERATED database manager parameter to YES.
                  (May be that was to obvious, but I didn't see it in the previous post.)
                  You're right! I always forget that until I hit the respective error message
                  the first time. ;-)

                  --
                  Knut Stolze
                  DB2 Information Integration Development
                  IBM Germany

                    Comment

                    Previous template Next
                    Working...