Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    82

    Unanswered: db2 Database ddl and data export

    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

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Use backup/restore if the source and target platforms are compatible:
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2012
    Posts
    82
    Quote Originally Posted by Marcus_A View Post
    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 ?

  5. #5
    Join Date
    Jan 2012
    Posts
    82
    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??

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2012
    Posts
    82
    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?

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by junaid377 View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You need to modify tablespace definitions in db2look output.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •