This discussion is archived
7 Replies Latest reply: Oct 6, 2011 8:17 AM by kgronau RSS

Copy to Oracle function: copy a DB2 table

892817 Newbie
Currently Being Moderated
Hallo,

I need to copy a single DB2 table to Oracle. I'm usign SQL Developer version 3.0. I downloaded and configured JDBC UDB Db2 driver version 9.7 (according to DB2 dba, this is the right version). I can connect to DB2 Instance, i can execute queries but SQL Developer does not show any table in the "Connections" view under the DB2 instance connection icon. A similar problem happened to me in the past with Oracle: I resolved that issue asking the dba to give grants to meta data info tables.
With db2 we tried to access the db using an administrator user, with all the grants available, but still sql-developer gives not meta information at all (tables, keys, indexes etc) on this db2 connection.
Could you please give me an idea on how to fix this issue?

Thanks a lot

Simone
  • 1. Re: Copy to Oracle function: copy a DB2 table
    kgronau Guru
    Currently Being Moderated
    Simone,
    on which platform does your DB2 database running? Is it residing on Unix or Windows, on an AS400 or on a z/OS mainframe?

    Edited by: kgronau on Oct 6, 2011 10:58 AM
  • 2. Re: Copy to Oracle function: copy a DB2 table
    892817 Newbie
    Currently Being Moderated
    It's running under Z/Os
  • 3. Re: Copy to Oracle function: copy a DB2 table
    kgronau Guru
    Currently Being Moderated
    That's the problem..

    An Oracle database is an Oracle database regardless on which platform it is running. But that's not true for IBM. A DB2 on a mainframe is completely different then a DB2 database on an iSeries or on Unix and Windows. There are for example major differences in the system catalog. SQL Developer currently supports only DB2 database on Unix and Windows see the readme http://www.oracle.com/technetwork/developer-tools/sql-developer/rel3-ea-relnotes-189445.html stating at the section Third Party Databases: * SQL Developer supports IBM DB2 UDB LUW...

    When SQL Dev connects it queries the system catalog of the foreign database to get the information about the objects you have access to.

    The catalog on a DB2 database running on a mainframe is different from the catalog oo a DB2 LUW database and thus SQL Developer doesn't show you any objects that you can access and that's also the reason why you won't be able to copy the table to the oracle database.

    So when you link a mainframe DB2 database into SQL Developer you'll only be able to execute some basic statements in the SQL Worksheet - but you won't ever be able to use the migration or any other advance feature which requires to query data from the system catalog.
  • 4. Re: Copy to Oracle function: copy a DB2 table
    892817 Newbie
    Currently Being Moderated
    Thanks a lot....your reply is absolutely clear. Thanks...hope that my dba will copy this table under a db2 instance on unix or windows.

    Best regards

    Simone
  • 5. Re: Copy to Oracle function: copy a DB2 table
    892817 Newbie
    Currently Being Moderated
    My dba did a copy (export-import) from db2 z/OS to db2 Windows. He gave me an account with rights to call catalog tables (meta) and still SQL Developer does not show tables list.
  • 6. Re: Copy to Oracle function: copy a DB2 table
    892817 Newbie
    Currently Being Moderated
    My dba did a copy (export-import) from db2 z/OS to db2 Windows. He gave me an account with rights to call catalog tables (meta) and still SQL Developer does not show tables list.
  • 7. Re: Copy to Oracle function: copy a DB2 table
    kgronau Guru
    Currently Being Moderated
    Which DB2 version are you using on the Windows box?
    Can your DBA trace the DB2 database to see that you really don't miss any privilege or might you be able to test with db2admin account - just to see that the objects of a DB2 database appear in the object browser?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points