9 Replies Latest reply: Aug 22, 2012 9:10 AM by 460076 RSS

    Connect OWB 11.2  with AS/400 , iSeries DB2

    860203
      Hi all.

      I'm trying to connect the OWB with a DB2 data that resides in a AS/400 machine.

      I'm using the heterogeneous services. and the ODBC drivers, but I cant make the OWB get connect to the AS/400.

      My DB is 64 bits, and I downloaded the iSeriesAcces odbc drivers from IBM. (64 bits too).

      I followed all the instructions from the Oracle Documentation, but when i try to test the connection with the sqlplus after create the DBLINK, i recieved this log from the gateway trace:

      GATEWAY TRACE ERROR
      --------------------------

      Oracle Corporation --- TUESDAY JUN 21 2011 09:29:10.905

      Version 11.2.0.1.0

      Entered hgolofns at 2011/06/21-09:29:10
      libname=/opt/ibm/iSeriesAccess/lib64/libcwbodbc.so, funcname=SQLGetFunctions
      peflerr=6521, libname=/opt/ibm/iSeriesAccess/lib64/libcwbodbc.so, funcname=SQLGetFunctions
      hoaerr:28500
      Exiting hgolofns at 2011/06/21-09:29:10
      Failed to load ODBC library symbol: /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so(SQLGetFunctions)
      Exiting hgolofn, rc=28500 at 2011/06/21-09:29:10
      Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:337 FUNCTION:hgoinit() ID:Loading ODBC aray of function ptrs
      Entered hgoexit
      HS Gateway: NULL connection context at exit
      Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:108 FUNCTION:hgoexit() ID:Connection context


      Here are my tnsnames.ora , listener.ora and gateway configuration file:

      TNSNAMES.ORA
      -----------------------------
      IDB2 =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LXBI.refisal.com.co)(PORT = 1521))
      (CONNECT_DATA =
      (SID=IDB2_SID))
      (HS=OK))

      LISTENER.ORA
      ---------------------------
      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = LXBI.refisal.com.co)(PORT = 1521))
      )
      )

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = IDB2_SID)
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1 )
      (PROGRAM = dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/opt/ibm/iSeriesAccess/lib64:/u01/app/oracle/product/11.2.0/db_1/lib)
      )
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
      )
      )


      initIDB2_SID.ora
      ---------------------------------
      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO =iSeriesDB4allUser
      HS_FDS_SHAREABLE_NAME =/opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
      HS_FDS_TRACE_LEVEL =4
      HS_FDS_SUPPORT_STATISTICS=FALSE
      #
      # ODBC specific environment variables
      #
      set ODBCINI = /etc/odbc.ini


      Besides the ODBC gateway connection, is there another way to connect the OWB to AS/400 iSeries DB2 ?

      Thanks. in advance.
        • 1. Re: Connect OWB 11.2  with AS/400 , iSeries DB2
          David Allan-Oracle
          There is also the native connectivity using JDBC - using the code template mappings.

          Cheers
          David
          • 2. Re: Connect OWB 11.2  with AS/400 , iSeries DB2
            860203
            Hi searching in the forums I found this thread and specially this post

            [http://forums.oracle.com/forums/thread.jspa?messageID=9413097#9413097]

            But I dont understand when he said that I need to "create a platform using platform assistant"

            Anyone knows what it means ??

            Thanks,,
            • 3. Re: Connect OWB 11.2  with AS/400 , iSeries DB2
              860203
              Hi David ,

              I'm trying to get this work, I follow the instructions copying the jdbc open drivers to the ext folder.


              In the warehouse builder I choose a DB2 type location , and I edited the advanced settings to set the driver like as400 jdbc.

              When I test the connection, the test is successful, but when I try to import a table nothing appears , I cant see any tables.

              I test the connection too, using your tlc script, but I only see the schemas, no tables

              I don't know if I need to create a custom platform to get connect with a AS/400 iSeries DB2 Database.

              Any help , will be appreciated

              Thanks
              • 4. Re: Connect OWB 11.2  with AS/400 , iSeries DB2
                David Allan-Oracle
                Hi

                When you execute the script and it lists the schemas, have you this schema name defiend in your OWB location?

                Cheers
                David
                • 5. Re: Connect OWB 11.2  with AS/400 , iSeries DB2
                  860203
                  HI David
                  Yes, I have defined a Schema name that appears in the script output.

                  The location settings are the next:

                  User : myUser ( this is the user from the AS/400 machine )
                  Password :
                  Host: AS/400 ip Machine
                  Port: 23
                  Database Name: Refisal1
                  Schema: BPCSF ( this name appear in the output list from the script)

                  And in the advanced settings :

                  Class Controler : com.ibm.as400.access.AS400JDBCDriver
                  URL: jdbc:as400://192.168.10.1:23/REFISAL1
                  URL TEMPLATE: : jdbc:as400://Host:Port/Database Name
                  Catalog : Refisal1

                  These are the settings for the OWB location.

                  One thing is that in the settings a Version appears , and there is only 8.1 , 8.2 , 9.1 . nothing to match the AS/400 Version.

                  Thanks
                  • 6. Re: Connect OWB 11.2  with AS/400 , iSeries DB2
                    David Allan-Oracle
                    If you need to use Catalog the jdbc tester script doesn't help as it stands. You need to check teh catalog name to see if it is matching what is projected from the JDBC driver.

                    Within the JDBC helper add in the line after the 'set meta_schema...' line
                    set meta_catalog [$rsms getColumnName 2]

                    and then the following two lines after the 'puts "Schema: ...' line
                    set catalog_name [$rss getString $meta_catalog]
                    puts "Catalog: $catalog_name"

                    Then save and run. It is probably that the catalog does not match what you have entered, below you have Refisal1for the catalog value.

                    Cheers
                    David
                    • 7. Re: Connect OWB 11.2  with AS/400 , iSeries DB2
                      860203
                      HI

                      I keep trying to get this to work, Now I'm going to resume what I have been doing:

                      1.) This is my new initIDB2_SID.ora I set the next parameters


                      HS_FDS_CONNECT_INFO =iSeriesDB4allUser
                      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so <--------------- important change
                      HS_FDS_TRACE_LEVEL =4
                      HS_FDS_SUPPORT_STATISTICS=FALSE
                      HS_LANGUAGE=HS_LANGUAGE=american_america.we8mswin1252 <---------------- important change


                      Whit these parameters now I can get data from the AS/400 creating a DBLINK

                      In sqldeveloper I can get the data from any table, but the data come with blanks at the end, and after 50 rows come 50 rows without data, and the next 50 with data, so on.

                      In OWB I create a location from the public dblink, now I can see the tables, but when I try to select the table to import it, I get an error:

                      ORA-00604: se ha producido un error a nivel 1 de SQL recursivo
                      ORA-28500: la conexión de ORACLE a un sistema no Oracle ha devuelto este mensaje:
                      ORA-06512: en "SYS.HS$_DDTF_SQLTABPRIKEYS", línea 68

                      And some tables names are with "square character" like undefiened character □

                      I think i need to set other parameter in my hs file, but I dont Know what....

                      Thanks if any can helpme

                      Edited by: Andres on 06-jul-2011 16:00
                      • 8. Re: Connect OWB 11.2  with AS/400 , iSeries DB2
                        460076
                        Hi David,

                        I'm looking for a solution to this problem. I follow your instructions and try with many kind of drivers, but I still I can't see the tables structure in the OWB.


                        I'd appreciate your help.

                        Regards.

                        Edited by: Nandex on 21-08-2012 07:10 PM

                        Edited by: Nandex on 21-08-2012 07:11 PM
                        • 9. Re: Connect OWB 11.2  with AS/400 , iSeries DB2
                          460076
                          Hi David,

                          We are trying to get a connection to db2 on a as400. What ever we try jtopen etc we fail.

                          We can make a connection to the DB2 database but we cannot see any table at all.

                          I used your tcl script as the sample but I just can see the schemas but not tables .
                          OMB+> source g:/Data_work/IBM_jdbc/jdbc_info_1.tcl
                          Schema: XXX
                          Schema: XXX
                          Schema: XXX
                          Schema: XXX
                          Schema: XXX
                          Schema: XXX
                          Schema: XXX
                          OMB+>
                          set driver "com.ibm.as400.access.AS400JDBCDriver"
                          set connect "jdbc:as400://XX.XX.X.XX;naming=sql;errors=full;date format=iso"
                          set username "XXX"
                          set pass "XXX"
                          I also tried with this driver DSClients--jdbc_sqlj-9.7.0.1 IBM Data Server Driver for JDBC and SQLJ V9.7 Fix Pack 1
                          I copy this two files db2jcc.jar db2jcc4.jar and keep the files db2jcc_license_cu.jar, db2jcc_license_cisuz.jar
                          I change your tcl script
                          set driver "com.ibm.db2.jcc.DB2Driver"
                          set connect "jdbc://73.37.0.42;naming=sql;errors=full;date format=iso"
                          set username "XXX"
                          set pass "XXX"

                          C:\owb11g\owb\bin\win32>OMBPlus.bat c:\jdbc_info_db2.tcl
                          java.sql.SQLException: No suitable driver

                          But I get this error, I and tried with the Design Center, and I can connect but I can't see the tables.
                          Thanks for your help.