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.
Best Answer
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.
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.