Hi all,

I have a database named test on a redhat linux 6.1 enviroment. I need a command to generate the whole DDL of the database. Everything ranging from tables, views, privileges, procedures, triggers, constraints, referential integrity.

Is it possible and if so what will be the command. I know it will use dblook but I have been unable to run it.Also I need to know will this command create DDL of all the tables including the Syscat tables and tablesspaces?

And how will I use it to create the database on the other instance. Will I first manually create a database and then use this ddl or will it automatically create a Database.

Also I need to export all the data of the database. Basically, I need the ddl to create the database in another instance and then use the exported data to populate the newly created database.

I know we can directly export the whole database but I need these perform these 2 steps separately

Use backup/restore if the source and target platforms are compatible:
IBM DB2 9.7 for Linux, UNIX and Windows Information Center

If you cannot use backup and restore, you can use db2look for the DDL and db2move for the data. db2look does not create the database (you need to create that manually). db2look does not create the catalog tables, as these are created when the database is created.

When you use db2look, be sure to specify a different delimiter (see db2look options) such as "@" so that your stored procs will work in the script. It will include the tablespaces if you ask for those, as well as other objects (check the db2look command options).

if i specify another delimiter like % will i have to do anything when i load the sql in another db or will the new db2 recognize the changed delimiter??

I am using db2 -tvf query.sql to load the sql file ?

When I run the file with the delimiter change it gives the following error

SQLN0007N the character % following end is not valid. SQLSTATE=42601

I am using the following command

db2look -d <database> -e -a -l -m -x -td % -o test.sql

and to load

db2 -tvf test.sql

any solution??

This one works for me:
db2look -d <database> -e -a -l -xd -f -td @ > test.sql
the ">" pipes it to output file name instead of using -o

I would not use % since it could be used as part of a Like Statement in the where clasue of some select.

When you run it on the new database, use this:
db2 -td@ -vf test.sql

will this be suitable for transferring a production server from Linux to windows.... and will the transfer be accurate enough to not force any changes to the front end system?

That depends on the DB2 release you run the export on. Some older releases may have some bugs.

You need to modify tablespace definitions in db2look output.