Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79

    Red face Unanswered: Import Table DDL & Indexes

    I'm using DB2 UDB v8 on Linux to Import table DDL & indexes from mainframe.

    I export in IXF format and import using the CREATE statement, but only the tables and data are created - No Indexes......

    Can anyone see what I'm doing wrong?

    CONNECT TO <mainframe>;
    EXPORT TO /home ...etc... /LIST.IXF OF IXF
    MESSAGES /home ...etc.../LIST.msg
    SELECT * FROM <qualifier>.<table name>;
    CONNECT RESET;
    TERMINATE;

    CONNECT TO <Linux>;
    IMPORT FROM /home ...etc.../LIST.IXF OF IXF
    COMMITCOUNT 10000
    MESSAGES /home ...etc .../LIST-IMP.msg
    CREATE INTO <qualifier>.<table name>
    IN <table space>;
    COMMIT WORK;
    CONNECT RESET;
    TERMINATE;

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am fairly certain the Export/Import using IXF only gets the data and the layout of the table, No Indexes, foreign keys, triggers, etc.

    To get these, you would need to run db2look with the -e option,
    then edit out the objects you do not want. then run that ddl.

    Andy

  3. #3
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    The manual states the CREATE option of Import will create the indexes - but I guess I'm doing something wrong:
    CREATE
    Creates the table definition and row contents. If the data was exported from a DB2 table, sub-table, or hierarchy, indexes are created.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are correct, the IMPORT will create the indexes (I never really tried that before). I tried the following command:

    IMPORT FROM d:\myfile.ixf OF IXF MODIFIED BY indexschema=DB2INST1 MESSAGES d:\mess.out CREATE INTO DB2INST1.mytable ;

    and it did create the indexes. Now I created the IXF file from DB2 v7.2
    on linux and created from it on another DB2 v7.2 linux server.

    You can try this, let me know if it works...

    Andy

    Originally posted by Rick-dba
    The manual states the CREATE option of Import will create the indexes - but I guess I'm doing something wrong:
    CREATE
    Creates the table definition and row contents. If the data was exported from a DB2 table, sub-table, or hierarchy, indexes are created.

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Indexes are only created with import command if the primary key exist on source table. So the primary key rule creates the unique index on table.

    To create the whole DDL statements including table definition, foreign keys, indexes, etc then use db2look command.


    Sintax
    db2look -d database -i userid -w password -o output_DDL_file -e -x -z schema -t table_name

    Legend
    database - source database
    userid - source userid
    password - source password
    output_DDL_file - path and file name of generated DDLs
    schema - source table schema name
    table_name - source table name without schema name

    You can also try "db2look ?" for more detailed help.

    Hope this helps,
    Grofaty

  6. #6
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    db2look -d database -i userid -w password -o output_DDL_file -e -x -z schema -t table_name

    Will this statement to work against a remote os/390 db2 v6 data source?

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    db2look is DB2 UDB command.

    I don't know about db2 os/390 v6, but I suggest you to try this out. If it works it is worth the effort.

    Hope this helps,
    Grofaty

    Originally posted by Rick-dba
    db2look -d database -i userid -w password -o output_DDL_file -e -x -z schema -t table_name

    Will this statement to work against a remote os/390 db2 v6 data source?

Posting Permissions

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