2

Is there any way to programatically copy across schema (i.e. tables,functions,views) etc from one database over to another one. Assuming both are running same versions of DB2.

I found a few utilities like i) db2move -- i could not find a way to access it via jdbc. So is ruled out. ii) admin_copy_schema -- copies across schemas in same database. iii)db2look -- again a utility. no way to call it from jdbc.

Even if we find a way to run those utilites db2move or db2look produces files which then have to be copied over to the target machine - making it highly complicated to automate the whole thing.

Is there a simple way to clone over a database through jdbn

1

JDBC is not really going to answer your problem directly. It is a generic programming API for working with any database. So it can't possibly know about utilities that are specific to an implementation, be it DB2, Oracle, MySQL, etc.

I would look at using one of the utilities you mentioned above. That is their purpose. They assist with moving databases and/or data across environments. (There is also the restore redirect option as well, which is good if you are doing a backup/restore option).

If you insist on doing this through application code, then you will have to pass DDL through the JDBC API's (which dependent on your driver and your permissions, may or may not work). Or you would have to try to execute the DB2 utilities from "command line" through you program (look at the Runtime class to do this).

Again, I would keep this out of the application (perhaps you can share more of why you want to do this? That would help with answering your question). If you insist on doing it inside your application, you may also want to check out Stack Overflow, as you may get answers from a "programmatic" perspective.

2
  • I would add that you could do this with JDBC, but you'd have to write your own tool to generate DDL from the database by querying the system catalog tables. This tool would have to be updated regularly as IBM adds features to DB2 – which is exactly why it's better to let IBM (or other database vendor) provide the tool for you. Feb 14 '12 at 20:06
  • I might do this myself, porting definitions from a SQL Server schema to DB2 for i. On IBM i there are features in DB2 not found on other platforms. None of the tools I've found address them, or do so very poorly. It may be extra work, but I can tweak it to handle those features as suits my needs. If we were to install new version of either DBMS, within my 9 month window, it wouldn't matter if new features were added unless we choose to use them. (highly unlikely in this case)
    – WarrenT
    Dec 10 '14 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.