Scribd Logo
Upload

Welcome to Scribd!

  • 2K views

    My New Moving Vehicle DB2MOVE "With More POWER and Less Gas"

    Uploaded by

    Renu Sharma
    The document discusses the db2move command which allows for bulk data movement between databases. It provides use cases for when to use db2move such as for backups, application deployment, or moving a single schema. The syntax, actions including export, import, load, and copy are covered. Details are given on copy options, tips for improving performance with parallel loads, and restarting failed copy operations.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    My New Moving Vehicle DB2MOVE "With More POWER and Less Gas"

    Uploaded by

    Renu Sharma
    2K views10 pages
    The document discusses the db2move command which allows for bulk data movement between databases. It provides use cases for when to use db2move such as for backups, application deployment, or moving a single schema. The syntax, actions including export, import, load, and copy are covered. Details are given on copy options, tips for improving performance with parallel loads, and restarting failed copy operations.

    Original Description:

    db2move presentation

    Original Title

    DB2sgottalentFinalsI.istmarchdb2movewithmorepowerlessgas

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses the db2move command which allows for bulk data movement between databases. It provides use cases for when to use db2move such as for backups, application deployment, or moving a single schema. The syntax, actions including export, import, load, and copy are covered. Details are given on copy options, tips for improving performance with parallel loads, and restarting failed copy operations.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    2K views10 pages

    My New Moving Vehicle DB2MOVE "With More POWER and Less Gas"

    Uploaded by

    Renu Sharma
    The document discusses the db2move command which allows for bulk data movement between databases. It provides use cases for when to use db2move such as for backups, application deployment, or moving a single schema. The syntax, actions including export, import, load, and copy are covered. Details are given on copy options, tips for improving performance with parallel loads, and restarting failed copy operations.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 10

    My new moving vehicle DB2MOVE With more POWER and less gas

    Db2udb LUW version 10.x onwards DB2s got Talent Finals

    3/19/2013

    DB2's Got Talent Finals

    By Renu Sharma of ACE DBA CONSULTING Inc

    When to use db2move


    OS independent backup Duplicate the schema Database size is small DBAs are too busy to take backup for me Shop has not got online backup Data of single schema is required Application deployment had to be done yesterday Restoring earlier backup image will take time Moving Bulk Data without intervention and without creating individual export and import/load scripts

    3/19/2013

    DB2's Got Talent Finals

    By Renu Sharma of ACE DBA CONSULTING Inc

    What is db2move and Its Authorizations


    Bulk data movement tool Mode 1. Export , Import or Load 2. Copy Mode Format 1. PC/IXF Same authorization as these APIs 1. Export 2. Import 3. Load
    3/19/2013 DB2's Got Talent Finals By Renu Sharma of ACE DBA CONSULTING Inc 3

    db2move Command Syntax


    db2move dbname --action Export Import Load Copy options
    -tc--table-definers -tn--table-names -sn--schema-names -ts--tablespace-names -io--import-option -lo--load-option -co--copy-option -l--lobpaths -u--userid -p--password --aw

    Source

    Target

    3/19/2013

    DB2's Got Talent Finals

    By Renu Sharma of ACE DBA CONSULTING Inc

    db2move Actions
    EXPORT
    1. Exports data depending on filteration criteria 2. No options means - exports all the tables 3. Internal information is stored in db2move.lst file

    IMPORT / LOAD
    1. 2. 3. Imports/Loads all tables of db2move.lst Uses -io option for IMPORT Uses -lo option for Load

    COPY
    1. Duplicates the schema 2. SYSTOOLSPACE table space has to exist
    3/19/2013 DB2's Got Talent Finals By Renu Sharma of ACE DBA CONSULTING Inc 5

    Db2move with copy options -co


    Mode
    1. 2. 3. 1.
    1. 2. 3. 4. 5.

    DDL_AND_LOAD : Default DDL_ONLY LOAD_ONLY Target database has to be local


    SCHEMA_MAP : Maps source schema to target schema NONRECOVERABLE : Tablespace will not put in backup pending state TABLESPACE_MAP : Maps source tablespace to target tablespace PARALLEL : Load spreads over number of threads OWNER : Change owner at target database

    Mandatory Options Optional Options

    3/19/2013

    DB2's Got Talent Finals

    By Renu Sharma of ACE DBA CONSULTING Inc

    db2move Trick or Treat oh no its db2move Tips and Tricks


    Delete or comment the files in db2move.lst if you dont want to load all tables Create individual load scripts for GENERATED ALWAYS identity columns Use Parallel parameter from 0 -16 Threads Timings of db2move with Parallel 16 threads Load without Parallel Load With 16 Threads real 403m6.468s real 369m58.965s user 0m0.315s user 0m0.303s sys 0m0.144s sys 0m0.077s Almost 10 % load time improvement with parallel option
    3/19/2013 DB2's Got Talent Finals By Renu Sharma of ACE DBA CONSULTING Inc 7

    Restarting db2move with copy option


    Error Types :
    1. DDL : Recreate Objects 2. Load : Reload data

    Error Type Files :


    1. 2. 3. 4. COPYSCHEMA_timestamp.ERR COPYSCHEMA_timestamp.MSG LOADTABLE.timestamp.MSG LOADTABLE.timestamp.ERR

    Check Load Error States, e.g., Load Pending :


    Use Load Query table tablename or List utilities show details

    Restart LOAD
    Run load from /dev/null of ixf into table name or run load Terminate
    Remove the error files and restart Load
    3/19/2013 DB2's Got Talent Finals By Renu Sharma of ACE DBA CONSULTING Inc 8

    Restrictions and Performance Parameters For db2move


    Tables having GENERATED ALWAYS identity column cannot be imported /loaded LOAD command has to be run locally where the database and the data file resides Loading data into tables containing XML columns is only supported for the LOAD , Import but not for the COPY action

    db2move performance parameters


    1. 2. 3. 4. 5.
    3/19/2013

    sortheap util_heap_sz logfilsiz logprimary parameters Adding pages to bufferpool sizes


    DB2's Got Talent Finals By Renu Sharma of ACE DBA CONSULTING Inc 9

    Links and Resources


    The db2move command now supports parallel processing http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db 2.luw.wn.doc%2Fdoc%2Fc0059711.html db2move - Database movement tool command http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db 2.luw.wn.doc%2Fdoc%2Fc0059711.html Restarting a failed copy schema operation http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db 2.luw.wn.doc%2Fdoc%2Fc0059711.html My contact and my blog info Renu Sharma ACE DBA Consulting Inc www.db2dbas.com renusharma@db2dbas.com, renusharma@aol.com 732-342-7630, 732-995-9963,732-374-3601
    3/19/2013 DB2's Got Talent Finals By Renu Sharma of ACE DBA CONSULTING Inc 10

    You might also like