Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: EXPORT IMPORT or Load From Cursor.

    Hi Guys,

    I need to extend one column of a table over page size of tablespace.

    So I will create a new tablespace with bigger pagesize amd move the table to new one.

    Exporting data and loading data will take about 1 hr an 20 minutes as I estimated. Which is not preffered by the company.

    so I need to reduce time (downltime).

    I never used LOAD FROM CURSOR, but I am sure it is double as faster.

    Can some one advise me any better way to do it fast.

    DB2 V8 fp 12 in WIN 2k3 --- Table size 28GB


    Thanks in adv
    DBFinder

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    DECLARE MYCUR CURSOR FOR SELECT col1, col2 FROM tabschema.tabname WITH UR;

    LOAD FROM MYCUR OF CURSOR MESSAGES message_file.msg INSERT INTO tabschema.tabname_new NONRECOVERABLE;

    RUNSTATS ON TABLE tabschema.tabname_new WITH DISTRIBUTION AND DETAILED INDEXES ALL;
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Is something possible for use as online. Without taking offtime.

    I know I can create similar table and when data will be synchronized I can stop for a second and drop old table.


    And . . . I donot know further. From here on.


    Can I rename the table after dropping old one. ?? How ?? What about triggers and constarints ??


    Thanks

    DBFinder

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DBFinder
    Is something possible for use as online. Without taking offtime.

    I know I can create similar table and when data will be synchronized I can stop for a second and drop old table.


    And . . . I donot know further. From here on.


    Can I rename the table after dropping old one. ?? How ?? What about triggers and constarints ??
    You cannot guarantee that the data in the old and new tables are identical, unless you lock the source table before starting the load, which will prevent any updates to the source.

    To rename the target table you use, surprisingly, the RENAME statement. After dropping the original table you will have to re-create all indexes, constraints, and triggers to reference the new table.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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