Steps on Ubuntu to move db2 table to a different tablespace

Start the instance as root

• /etc/init.d/db2exc start

Become db2inst1 user

• su db2inst1

Get into command line mode

• db2

Run the following commands

db2 => connect to dbtest
db2 => list tables
db2 => create table test01 (a integer not null, b varchar(10), primary key(a))
db2 => create table test02 (x integer not null, y integer not null, z varchar(10), primary key(x,y))
db2 => create view v_test01 as select * from test01
db2 => alter table test02 add constraint test02_fk1 foreign key (x) references test01 (a) on delete restrict
db2 => alter table test02 alter foreign key test02_fk1 not enforced

To extract DDL use

• db2look -d dbtest -e

db2look syntax

Syntax: db2look -d DBname [-e] [-xs] [-xdir Path] [-u Creator] [-z Schema]
                          [-t Tname1 Tname2...TnameN] [-tw Tname] [-h]
                          [-o Fname] [-a] [-m] [-c] [-r] [-l] [-x] [-xd] [-f]
                          [-fd] [-td x] [-noview] [-i userID] [-w password]
                          [-v Vname1 Vname2 ... VnameN] [-dp] [-ct]
                          [-wrapper WrapperName] [-server ServerName] [-nofed]
                          [-wlm] [-ap] [-mod] [-cor]

                          [-wrapper WrapperName] [-server ServerName][-fedonly] [-nofed]

        db2look [-h]

        -d: Database Name: This must be specified

        -e: Extract DDL file needed to duplicate database
       -xs: Export XSR objects and generate a script containing DDL statements
     -xdir: Path name: the directory in which XSR objects will be placed
        -u: Creator ID: If -u and -a are both not specified then $USER will be used
        -z: Schema name: If -z and -a are both specified then -z will be ignored
        -t: Generate statistics for the specified tables
       -tw: Generate DDLs for tables whose names match the pattern criteria (wildcard characters) of the table name
       -ap: Generate AUDIT USING Statements
      -wlm: Generate WLM specific DDL Statements
      -mod: Generate DDL statements for Module
      -cor: Generate DDL with CREATE OR REPLACE clause
        -h: More detailed help message
        -o: Redirects the output to the given file name
        -a: Generate statistics for all creators
        -m: Run the db2look utility in mimic mode
            -c: Do not generate COMMIT statements for mimic
            -r: Do not generate RUNSTATS statements for mimic
        -l: Generate Database Layout: Database partition groups, Bufferpools and Tablespaces
        -x: Generate Authorization statements DDL excluding the original definer of the object
       -xd: Generate Authorization statements DDL including the original definer of the object
        -f: Extract configuration parameters and environment variables
       -td: Specifies x to be statement delimiter (default is semicolon(;))
        -i: User ID to log on to the server where the database resides
        -w: Password to log on to the server where the database resides
   -noview: Do not generate CREATE VIEW ddl statements
  -wrapper: Generates DDLs for federated objects that apply to this wrapper
   -server: Generates DDLs for federated objects that apply to this server
    -FEDONLY: Only created Federated DDL Statements
    -nofed: Do not generate Federated DDL
       -fd: Generates db2fopt statements for opt_buffpage and opt_sortheap along with other cfg and env parameters.
        -v: Generate DDL for view only, this option is ignored when -t is specified
       -dp: Generate DROP statement before CREATE statement
       -ct: Generate DDL Statements by object creation time