Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2011
    Posts
    6

    Unanswered: connect two databases from two different server

    hi i need help, i have to create queries from two databases from two different server. Is it possible in db2?.
    I can do that in mysql
    MySQL :: MySQL 5.0 Reference Manual :: 13.7.2 How to Use FEDERATED Tables

    CREATE TABLE test_table (
    id INT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL DEFAULT '',
    other INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    INDEX name (name),
    INDEX other_key (other)
    )
    ENGINE=MyISAM
    DEFAULT CHARSET=latin1;


    FEDERATED table on the local server for accessing the remote table:

    CREATE TABLE federated_table (
    id INT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL DEFAULT '',
    other INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    INDEX name (name),
    INDEX other_key (other)
    )
    ENGINE=FEDERATED
    DEFAULT CHARSET=latin1
    CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

    that allows it to create queries between the two servers like:
    INSERT INTO ventas_virtual.test_table (
    test_table.name , test_table.other
    )
    (select federated_table.name,federated_table.other from ventas.federated_table )
    any idea??

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First of all, please migrate "CREATE TABLE test_table" into DB2.
    After that, please publish your DB2 version/release and platform OS.

    Then we can try to access it from other database/instance/server of DB2.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

  4. #4
    Join Date
    Dec 2011
    Posts
    6

    Version and operative system

    Quote Originally Posted by tonkuma View Post
    I'm now experimenting with two servers db2 express version 9.5 on Windows, but the idea is to work with two AS/400 servers.

    with Federated I'll be able to perform actions to insert, update and delete from one server to another?

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    first check if federation is also available at i-series
    iSeries Information Center
    not all db2 edition have the same function/features
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Aug 2011
    Posts
    46
    db2 Connect server is part of the iseries installation, and it supports homogeneous federation function. if you want to access
    multiple DB2 for iseries/OS databases or
    DB2 for iseries and say DB2 for z/OS or
    DB2 for iseries to DB2 for UNIX/Linux/Windows server
    in the same SQL statement you can do that with DB2 Connect.

  7. #7
    Join Date
    Dec 2011
    Posts
    6

    response

    Quote Originally Posted by amitrai4 View Post
    db2 Connect server is part of the iseries installation, and it supports homogeneous federation function. if you want to access
    multiple DB2 for iseries/OS databases or
    DB2 for iseries and say DB2 for z/OS or
    DB2 for iseries to DB2 for UNIX/Linux/Windows server
    in the same SQL statement you can do that with DB2 Connect.
    where is DB2 Connect option that you say, I dont found in my server?.

    I was able to create queries and stored procedures between two different database servers using DB2 Express-C 9.5 on windows with federated database like said Tonkuma, I used the wizard bd2 control center and with a table nickname I can refer to another database. But my iSeries/Servers don't have control center Any Idea?
    Last edited by acremex; 12-21-11 at 20:27.

  8. #8
    Join Date
    Aug 2011
    Posts
    46
    Quote Originally Posted by acremex View Post
    where is DB2 Connect option that you say, I dont found in my server?.

    I was able to create queries and stored procedures between two different database servers using DB2 Express-C 9.5 on windows with federated database like said Tonkuma, I used the wizard bd2 control center and with a table nickname I can refer to another database. But my iSeries/Servers don't have control center Any Idea?
    You can use iseries navigator.

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2 connect and federation server are different products and have different usage
    connect : connect to ebcdic servers (z-i..)
    federation : connect to different db (sql/s ora..)
    I could not find any reference to federation server in i-series db2
    another possibility : install db2 on another server (not i) with federation and access this db with db2 connect from i-series
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I found another entry from forum with statement from IBM

    Frank, DB2 Connect servers contain homogeneous federation function i.e. it
    is included as part of DB2 Connect for iSeries (yes, we have a product like
    that), DB2 Connect Application Server Edition, DB2 Connect Unlimited
    Edition and DB2 Connect Enterprise Edition. So, if what you need to do is
    access multiple DB2 for i5/OS databases or DB2 for i5/OS and say DB2 for
    z/OS or DB2 fro AIX server in the same SQL statement you can do this with
    DB2 Connect. You do not need to purchase WebSphere Information Integrator.

    Leon Katsnelson

    so in i-series it is included with db2 connect and no need for fed.server as indicated..
    IDUG : Forums : iSeries (OS/400) DB2 Federation
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    Dec 2011
    Posts
    6

    Java stored procedure to remote server. Is it possible?

    I'm trying to make Remote Procedure This is my code to create procedure.
    Code:
    CREATE PROCEDURE SIZE (OUT TAM INTEGER)
       EXTERNAL NAME 'DIRECTE.SIZE'
       LANGUAGE JAVA PARAMETER STYLE JAVA;
    Java Class
    Code:
    public class DIRECTE {
     
       public static void SIZE (int[]wrkcrsize){
        Connection conexion;
            int i=100; 
           try {
           // Cargar el Controlador JDBC de DB2 de tipo 2 con DriverManager
                Properties connectProperties = new Properties();
              
                connectProperties.put("user", "remoteuser");
                connectProperties.put("password", "remotepass");
                connectProperties.put("databaseName","remotebd");
                connectProperties.put("serverName","hostremote");
                connectProperties.put("portNumber","50000");
    
                Class.forName("com.ibm.db2.jcc.DB2Driver");
    //Next line causes an error            
    conexion = DriverManager.getConnection("jdbc:default:connection",connectProperties);
            
                conexion.setAutoCommit(false);
            
                ResultSet Registros = null;
           
                String sql1 = "SELECT COUNT(*) "
                    + "FROM somsoc";
           
                Statement SentenciaSQL = conexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                Registros = SentenciaSQL.executeQuery(sql1);
                Registros.next();
                wrkcrsize[0] = Registros.getInt(1);
            SentenciaSQL.close();
            conexion.commit();
            conexion.close();
            } catch (ClassNotFoundException e) {
                System.out.println("Class not found");
                System.out.println(e);
         
            } catch (SQLException e) {
                System.out.println(e.getErrorCode());
                System.out.println(e);
              
            }  
       }
    if I use local data everything works perfect, when I test the java class and netbeans o cmd with java "classgenerate.class" that work...

Posting Permissions

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