Is there a way in db2 to take a backup of the database structure (Metadata) without actual data?

My purpose is to pull structure (tables, views, indexes, triggers, procedures and functions) from an existing DB into a new DB.

The tables can be blank.

  • 3
    Check db2look – ypercubeᵀᴹ Dec 29 '15 at 8:56
  • @YperSillyCubeᵀᴹ has the answer. You should put that as an answer so rrrohanb can mark it as such. – Chris Aldrich Dec 29 '15 at 13:36
  • @ChrisAldrich Please feel free to add an answer. I have never used this and feel a bit reluctant to post something I only found by searching the Net. – ypercubeᵀᴹ Dec 29 '15 at 13:44

Since YperSillyCube(TM) insists, I will add his answer. db2look is the tool that comes with DB2 to extract DDL from the database. You can extract things like tables, constraints, indexes, triggers, stored procedures, etc. As well as security statements, federation settings, buffer pool and tablespaces, and some database settings.

I highly recommend using it. I used to use it all the time when I worked with DB2. Very handy. Especially when combined with tools like Beyond Compare to compare DDLs between environments, etc.

The IBM Knowledge Center link is good documentation to read up on. Ember Crooks has posted two great articles on db2look here and here.

Personally, I tended to use the following options the most with the command.

db2look -d <databasename> -a -l -e -x -o <file to extract to> -td"@"

This allows me to grab several objects besides just the default, the tablespaces and bufferpools, some security settings and then make sure I set the @ sign as the statement terminator in case I have triggers, functions, or stored procedures.

You can then import those into a new database using the following db2 -td@ -vf <filename> and then either redirect the results out or pipe to tee as I like to do on Unix. Do note that the character you use as the statement terminator in the db2look command needs to match the statement terminator supplied when importing the file.

  • say i extract it to a file ABC.sql can you guide me to Deploy the same to a New DB TEST? – rrrohanb Dec 29 '15 at 14:10
  • 1
    db2 -td@ -vf ABC.sql > results.txt or if you have Unix, I personally like to use db2 -td@ -vf ABC.sql | tee results.txt – Chris Aldrich Dec 29 '15 at 15:22

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.