Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: db2look comand

  1. #1
    Join Date
    May 2009
    Posts
    8

    Unanswered: db2look comand

    Hi everyone

    I hope someone can help me, I need to create a database with all tables and tablespace that currently has been created in other server I use:
    db2 create db test
    and from the other server extract the ddl using:
    db2look -d DB -e -l -x -a > DB.ddl
    and run it with db2 -tvf DB.ddl > DB.out
    but there are two schemas with tables that I don´t have =( and are 254 tables

    how I can get a complete ddl do create the database with all tables and schemas ?

    Thanks for the help

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    when you say you do not have 2 schemas, you mean it did not created them? db2look should have them.

    Why not restore it from the backup?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    May 2009
    Posts
    8
    yes it seams that the db2look comand does not extract the ddl for those tables
    which will be the corect commands to use since the this bd is on a production server and I need to create it identical in a development box ?

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    are you sure that those are tables? I have never heard or seen db2look missing a table.

    If you run "select type from syscat.tables where tabschema = '' and tabname = ''" what do you get in the type?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    May 2009
    Posts
    8
    I get:
    V
    V
    V
    312 record(s) selected
    from these comand

    db2 "select type from syscat.tables where tabschema = 'XBRIOBV'"

    XBRIOBV is one of the schemas I dont have in the database

  6. #6
    Join Date
    Jan 2009
    Posts
    8
    hi

    i hope this will help u..


    please try db2look command with -z option and specify the required schema you want to create ddl for the schema.

    db2look -d DB -e -l -x -z <schemanname> > DB.ddl

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by unitec

    XBRIOBV is one of the schemas I dont have in the database
    First of all, could you please tell us how you determine that a schema is missing?
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by unitec
    I get:
    V
    V
    V
    312 record(s) selected
    from these comand

    db2 "select type from syscat.tables where tabschema = 'XBRIOBV'"

    XBRIOBV is one of the schemas I dont have in the database
    In case you haven't found out already V stands for a VIEW. Those are not tables. Even so they should have showed up in your output based on the command that you showed earlier.

    The only way to remove views from the output is to use option "-noview" which does not look like you did.

    Are you sure you are in the right database?

    As Nick said, How do you know that you are not getting that schema?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I just reread your first post. You are not seeing that schema after you try to run DDL from another server on your new server.

    Have you looked in your DB.out file to see if there were errors while creating those views?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    May 2009
    Posts
    8
    hi

    I use the comand

    $ db2 connect to DIWSTDB
    and then

    $ db2 list tables for all
    where I get
    1923 record(s) selected
    Whit
    Table/View Schema Type Creation time
    ------------------------------- --------------- ----- --------------------------
    ASSAY_FOUNDRY_BOOKINFO ASSAY V 2008-02-23-12

    on the old box
    What mean the Type "V" and "T" ?
    Then I did the same on the new box where I want the database, I checked that output and I found that XBRIOBV is not in here =(

    Regards!

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The type 'V' indicates a view. The schema in your listing shows as 'ASSAY'; why do you expect it to be 'XBRIOBV' in on the new system?
    Last edited by n_i; 05-20-09 at 10:00.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Go to your original DB and run this command:

    Select substr(tabschema,1,20) as tabschema, type, count(*) from syscat.tables where tabschema = 'XBRIOBV' group by tabschema, type

    then run the same command in your new DB and give us a complete output that shows exactly what command you ran and what you got back.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  13. #13
    Join Date
    May 2009
    Posts
    8
    Yes that is just part of the ouput and 'XBRIOBV' is an schema that I do not have in the new system and the ouput from the ddl I run db2look -d DB -e -l -x -a > DB.ddl all is successful
    Please help!

  14. #14
    Join Date
    May 2009
    Posts
    8
    Hi all
    I checked the db2look -d DB -e -l -x -a > DB.ddl ouput and found that with it I get the ddl from views that I dont have so it seams that one of them were not complete susscessfully so I run the ddl again and works naw I have tose view in my database.

    Best Regards,
    =)

  15. #15
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    That is why in post 9 I asked you to look at your OUT file. We could have been on a 5th drink already
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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