Hi Tom,
I have 3 questions :

1. Any Documentation link for Option c from above.(type4 (thin) jdbc (all java) drivers).

2. Is there a Oracle Transparent Gateway to connect to a DB2 Database running on UNIX/Windows box.Most of the documents I found mention of a Oracle Tranparent Gateway to access DB2 on Mainframes. Please do give a link if there are any such documents, or do I have to use Generic Connectivity to connect to DB2 running on UNIX/Windows box.

3. What is the significance of the view hs_fds_class? On querying this view I get the following information :
SQL> select * from hs_fds_class;

FDS_CLASS_NAME  FDS_CLASS_COMMENTS             FDS_CLASS_ID
--------------- ------------------------------ ------------
BITE            Built-In Test Environment                 1


Thanks 

it is like loading "your developed java code".


2) as far as I know, you would use the ODBC gateway provided with the heterogenous services to access that yes, in fact, you would probably use that for most databases.


3) it is just metadata

Hi Tom,
---------------
<TOM>
2) as far as I know, you would use the ODBC gateway provided with the
heterogenous services to access that yes, in fact, you would probably use that
for most databases.
</TOM>
----------------

Does it mean that it is advantageous to use Generic Connectivity than to use Oracle Transparent Gateway?

Thanks

Hi Tom,

Thanks to this thread and the documentation pointed out by you, I was able to sucesssfully connect to DB2 Database from Oracle. 

Is there a way to call/execute a DB2 stored procedure from Oracle. In DB2 from the Command Line Processor I can execute the procedure as :
db2 => call test(1000)

  Return Status = 0

But when I try the same from SQLPLUS prompt 
===============================
Method 1
=================================
SQL> call test@phonedb(1000);
call test@phonedb(1000)
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name
===============================
Method 2
============================
SQL> exec test@phonedb(1000);
BEGIN test@phonedb(1000); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TEST@PHONEDB' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

================
Method 3
==================
SQL> begin
  2  DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@phonedb('call test(1000)');
  3  end;
  4  /
DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@phonedb('call test(1000)');
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00221: 'EXECUTE_IMMEDIATE' is not a procedure or is undefined
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

=================
phonedb is the Database Link I have created for the DB2 Database
================
Can you Please tell me where I am goin wrong?

Thanks 

Thanks , it worked.

I have been trying to use DBMS_HS_PASSTHROUGH to call a sybase proc that returns paramaters and I cant get it to work. I think the parameters are returned via a ref cursor but I cannot get the code to compile with the extra parameter. Is there some setup required, both systems are using Sunos. My code is like

DBMS_HS_PASSTHROUGH.parse@CRVS.WORLD(v_handle, 'exec pGetField "FF001782","ff","BORR" ');

the fourth param is an out param.

Am I doing something wrong?

Dave

I have a HS connection with DB2, I followed this steps:

1) I created the System DSN connection and I test it with Java (ABM's is working).
2) I configured tnsnames.ora, listener.ora sqlnet.ora and inithsBD.ora like this:

tnsnames.ora:
BD_TRANSFERENCIA.WORLD =
(description =
(address = (protocol=tcp)(host=BDSERVER)(port=1521))
(connect_data = (sid=BD_TRANSFERENCIA))
(hs=ok)
)
listener.ora:
(SID_DESC =
(GLOBAL_DBNAME = BD_TRANSFERENCIA)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = BD_TRANSFERENCIA)
(program = hsodbc)
)

sqlnet.ora:
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD

initBD_TRANSFERENCIA.ora:
HS_FDS_CONNECT_INFO = BD_TRANSFERENCIA
HS_FDS_TRACE_LEVEL = 0

3) I restarted tnslistener

4) I created the DB_LINK (with an user that can realize ABM's)

5) I tested SELECT sentence and it works.

6) I tried to INSERT simple values (TYPES are corrects)
INSERT INTO TABLE1@BD_TRANSFERENCIA.WORLD
(CAMPO1,CAMPO2,CAMPO3,CAMPO4)
VALUES
('F2',2,'30',2005)
/

and Oracle response:

18:24:54 ORA-28500: la conexión de ORACLE a un sistema no Oracle ha devuelto este mensaje:
18:24:54 [Generic Connectivity Using ODBC][IBM][Controlador ODBC de Client Access Express (32 bits)][DB2/400 SQL]SQL7008 - TABLE1 de CONTOR no válido para la operación. (SQL State: S1000; SQL Code: -7008)

Is necesary other parameter for DB2 HS conections?

Thanks in advance.

Michael P.

I tried to do an INSERT with HS connection and Oracle response:

18:24:54 ORA-28500: connection to non-Oracle system response:
18:24:54 [Generic Connectivity Using ODBC][IBM][ODBC controller for Client
Access Express (32 bits)][DB2/400 SQL]SQL7008 - TABLE1 de CONTOR not valid operation. (SQL State: S1000; SQL Code: -7008)

The error says that my DSN connection don't have permission to do INSERT, but I tested the DSN connection with Java (same user connection) and I did INSERT without problems.

What can i do?

Thanks for your help.

Michael P.




Is necesary other parameter for DB2 HS conections?

Thanks in advance.

Michael P.


Followup:
can you translate the message - and does the user you are connecting as have the
privilege to do that

Is HSODBC solution viable in terms of performance? Can you share your views on the following

- performance
- security

Hi, i have this problem

SQL> exec pkg_procedimientos_java.pr_prueba_getfolio;
BEGIN pkg_procedimientos_java.pr_prueba_getfolio; END;

*
ERROR at line 1:
ORA-29515: exit called from Java code with status -2
ORA-06512: at "MANHATTAN_CTRL.PKG_PROCEDIMIENTOS_JAVA", line 3
ORA-06512: at "MANHATTAN_CTRL.PKG_PROCEDIMIENTOS_JAVA", line 20
ORA-06512: at line 1


the code of PKG_PROCEDIMIENTOS_JAVA IS


 as

PROCEDURE PR_RETURN_FOLIO(pi_ip IN VARCHAR2,
                          pi_servidor IN VARCHAR2,
                          pi_usuario IN VARCHAR2,
                          pi_password IN VARCHAR2,
                          pi_biblioteca IN VARCHAR2,
                          pi_folio IN OUT VARCHAR2,
                          pi_error_code IN OUT VARCHAR2,
                          pi_error_desc IN OUT VARCHAR2) AS LANGUAGE JAVA NAME 
    'getFolio.getFolio( java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String[], java.lang.String[], java.lang.String[])' ;
    
PROCEDURE PR_PRUEBA_GETFOLIO
IS
   vl_folio VARCHAR2(4000);
   vl_error_code VARCHAR2(4000);
   vl_error_desc VARCHAR2(4000);
BEGIN
--NULL;
   PR_RETURN_FOLIO('150.230.84.40','RETRET', 'ODBCMANMIG', 'KRETRET','MMRETRETM' ,vl_folio,vl_error_code,vl_error_desc);
   DBMS_OUTPUT.PUT_LINE('FOLIO:'||vl_folio);
   DBMS_OUTPUT.PUT_LINE('ERROR_CODE:'||vl_error_code||' ');
   DBMS_OUTPUT.PUT_LINE('ERROR_DESC:'||vl_error_desc||' ');
END;
    
end;




AND THE JAVA CODE IS

import java.sql.*;
//import COM.ibm.db2.jdbc.app.*; // DB2 UDB JDBC classes
//import COM.ibm.db2.app.*; // StoredProc and associated classes
import COM.ibm.db2.jcc.*;
/**
 *
 * @author aenriquez
 */
public class getFolio {
        private static String urlDB2;  
        private static String usr, pwd, lib;
        
        public static void getFolio(String ip, String srv, String user, String pass, String library, String[] folio, String[] codErr, String[] msgErr){
            usr = user;
            pwd = pass;
            lib = library;
            urlDB2 = "jdbc:db2://"+ ip +"/"+ srv; 
            try {
                codErr[0] = "0";
                msgErr[0] = "";
                folio[0] = putFolio();                
            }catch (SQLException e){
                codErr[0] = "1";
                msgErr[0] = e.getMessage();
            }
        }
           
        private static String putFolio() throws SQLException{
        String valRtn = null;
            
            CCnDB2 myCn = new CCnDB2(urlDB2, usr, pwd);
//            valRtn = myCn.execQry("select distinct inumbr from "+lib+".invbal where inumbr = 10107");
            valRtn = myCn.execQry("call "+lib+".SP_MANHFOS(5005)");
            myCn.CloseCn400();
            return valRtn;
        }
/*        public static void main(String[] args) {
        String ip = args[0];        
        String srv = args[1];        
        String usr = args[2];
        String pwd = args[3];
        String lib = args[4];
            
        String[] nfolio = new String[1];        
        String[] codErr = new String[1];        
        String[] msgErr = new String[1];        
        
            getFolio oFolio = new getFolio(ip, srv, usr, pwd, lib, nfolio, codErr, msgErr);
            args[5] = nfolio[0];
            args[6] = codErr[0];
            args[7] = msgErr[0];
    }           */
}
class CCnDB2 {
    private static Connection db2cn = null;
    private String outMsg, urlDB2;
    private String usr;
    private String pwd;        
  
    public CCnDB2(String p_urlDB2, String p_usr, String p_pwd){   
        urlDB2 = p_urlDB2;
        usr = p_usr;
        pwd = p_pwd;
    }   
     
    private void conectaDB2() throws SQLException{
        try{
         // Crear la conexión utilizando el Controlador JDBC de DB2 Universal
            Class.forName("com.ibm.db2.jcc.DB2Driver");
            db2cn = DriverManager.getConnection (urlDB2, usr, pwd);
            db2cn.setReadOnly(true);            
            db2cn.setAutoCommit(false);
            if(db2cn == null||db2cn.isClosed()) {
                outMsg += "Conexión NO establecida IP:"+urlDB2+"\n";
            }                
            else {
                outMsg += "Conexión a CD establecida ";                
            }            
        }catch(ClassNotFoundException e){            
            System.err.println(e.getMessage());
            System.exit(-2);
        }           
    }
       
    public String execQry(String stmt) throws SQLException  {
        String valRtn = null;
        if(db2cn==null||db2cn.isClosed()) conectaDB2();
        Statement stm = db2cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rst = null;
        
        String sqlNative = db2cn.nativeSQL(stmt);
        rst = stm.executeQuery(sqlNative);
        rst.next();
        valRtn = rst.getString(1);
        rst.close();
        stm.close();
        stm = null;     // alex
        return valRtn;
    }
    
    public void CloseCn400(){
        try {
            if(this.db2cn != null){
               db2cn.close();
            }
        }
        catch (SQLException ex){
            if(outMsg == null) System.out.println(ex.getMessage());
            else {
                outMsg += ex.getMessage()+"\n";
            }
        }
        catch (Exception ex){
            if(outMsg == null) System.out.println(ex.getMessage());
            else {
                outMsg += ex.getMessage()+"\n";
            }
        }
    }
}    


WHAT IS THE PROBLEM? 

Hi Tom,

Is it possible to export data from postgre DB to Oracle DB ? My requirement is to read data from postgre DB and dump/use this data in Oracle DB.
Any pointers on DB link creation OR export/import utility will be highly appreciated.

Cheers,

Dheeraj

"Would I build a production system whereby I'd be hitting this gateway 1,000's of times per second?
probably not."

I understand the above comment but Would you build a Production system that does a few calls to a non-oracle source (maybe a few hundred calls/day that bring back a few thousand rows each time - may be as high as 100,000 rows)? I understand that bringing back 100k rows may not be super fast but that is okay - lag of a few minutes is acceptable. The idea is to bring these rows into Oracle and load a table and run some analysis within Oracle.

Tom,
I wasn't clear in the posting - the question below was for you asking to clarify a statement earlier in this thread.
----------------------------------------------------------

"Would I build a production system whereby I'd be hitting this gateway 1,000's of times per second?

probably not."

I understand the above comment but Would you build a Production system that does a few calls to a
non-oracle source (maybe a few hundred calls/day that bring back a few thousand rows each time -
may be as high as 100,000 rows)? I understand that bringing back 100k rows may not be super fast
but that is okay - lag of a few minutes is acceptable. The idea is to bring these rows into Oracle
and load a table and run some analysis within Oracle.

Not sure what you mean by "if I didn't have an end user application waiting for it - may be. we call that replication" - I don't understand what you are trying to say.

Hi Tom, I spent too many hours trying to found an example for connecting to DB2 via Oracle. Now that I could do it I want to share the procedure with all your readers.

1. Copy inithsodbc.ora to initQA_DB2.ora in $ORACLE_HOME\hs\admin

2. Add the following lines to listener.ora in Oracle server ($ORACLE_HOME\NETWORK\ADMIN)

(SID_DESC =
(SID_NAME = QA_DB2)
(ORACLE_HOME = path for your $ORACLE_HOME)
(PROGRAM = hsodbc)
)

3. Add the following lines to tnsnames.ora in Oracle server ($ORACLE_HOME\NETWORK\ADMIN)

DB2= (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=localhost)
(PORT=1521)
)
(CONNECT_DATA = (SERVICE_NAME=QA_DB2)
)
(HS = OK)
)

4. Execute CATalog script for Heterogeneous Services in Oracle server ($ORACLE_HOME\RDBMS\ADMIN\caths.sql). I think this is optional if you won't use transparent gateway.

5. Create database link

drop public DATABASE LINK dwh_qa_db2;

CREATE public DATABASE LINK dwh_qa_db2 CONNECT TO "cmorlan" IDENTIFIED BY "lonelyranger" USING 'DB2';

6. Test the connection

SELECT *
FROM some_table@dwh_qa_db2

I have Oracle 10g on Windows 64 bit (Itanimum) . I am looking for transparent gateway for this version. Please let me know , where I can found this .

( In OTN , I can find only 32 bit version)

Will the transparent gateway for 11g 64 bit be compatible with 10g version ?


Thanks in Advance