![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Other DB2 data movement utilities
db2move is a data movement tool that can be used to move large numbers
of tables between DB2 databases. Supported actions in the command are
EXPORT, IMPORT,
LOAD,
and COPY. The behavior of actions
EXPORT, IMPORT, and
LOAD is exactly the same as described
previously. The only action you probably are not familiar with is
COPY. It duplicates tables in a schema or
schemas into a target database. The syntax of db2move is as simple
as:
db2move database_name
action
options
|
A list of user tables is extracted from the system catalog tables, and each table is exported in PC/IXF format. The PC/IXF files can then be imported or loaded into another DB2 database.
Here are some examples. This command imports all tables in the sample
database in REPLACE mode with the specified
user ID and password:
db2move sample IMPORT -io REPLACE -u
userid -p password.
And this command loads all tables under the schemas db2admin and
db2user in REPLACE mode:
db2move
sample LOAD -sn db2admin,db2user -lo REPLACE.
Refer to the Command Reference to get a complete listing and
descriptions of all the options. However,
COPY warrants a discussion. With
COPY, you specify one or more schemas with
the -sn option. Only tables with exactly
the same schema names specified in the -sn
option will be copied (via export). If multiple schema names are
specified, use commas to separate them and no blanks are allowed.
Refer to the example below.
db2move sample COPY -sn db2inst1,prodschema -co TARGET_DB acctdb USER peter
USING petepasswd DDL_AND_LOAD
|
The above db2move command copies supported objects under the db2inst1
and prodschema schemas. The -co option that
follows makes the command more interesting.
TARGET_DB specifies the target database,
which the schemas are going to be copied — acctdb in this case.
This option is mandatory when COPY is
specified. In addition, the target database must be different from the
source database. You may provide the user and password with the
USER and USING
options when connecting to the target database.
By default, supported objects from the source schema will be created, and tables will be populated in the target database. This is the behavior of the DDL_AND_LOAD mode. Two other modes are available: DDL_ONLY and LOAD_ONLY. As the names imply, DDL_ONLY only creates all the supported objects from the source schema and LOAD_ONLY loads all specified tables from the source to the target database. Note that tables must already exist in the target database when this option is used.
Sometimes you may want to rename the schema when copying the objects to
the target database. The SCHEMA_MAP option
can be used for this purpose. You simply provide one or more pairs of
schema mappings like this:
SCHEMA_MAP
((source_schema1,target_schema1),(source_schema2,target_schema2)).
Extra attention is recommended when SCHEMA_MAP is used. Only the schema
of the object itself is renamed, qualified objects inside the object
body remains unchanged. For example:
CREATE VIEW FOO.v1 AS
'SELECT c1 FROM FOO.T1'.
Schema rename from FOO to BAR will result in
CREATE
VIEW BAR.v1 AS 'SELECT c1 FROM FOO.T1'.
BAR.v1 created in the target database might fail if FOO.T1 is not
defined.
A similar mapping idea also applies to tablespaces. For example, you
want the copied tables to be stored in a different tablespace name
from the source database. The db2move command is extended to let you
specify tablespace name mappings. Consider the following option:
TABLESPACE_MAP ((TS1,TS2),(TS2,TS3),SYS_ANY).
The above tablespace name mapping indicates that source TS1 is mapped
to target TS2, source TS2 is mapped to target TS3. The
SYS_ANY indicates that the remaining
tablespaces will use tablespaces chosen by the database manager based
on the tablespace selection algorithm. Let's put the pieces together
in an example.
Listing 37. db2move utility example
db2move sample COPY -sn db2inst1,prodschema
-co TARGET_DB acctdb USER peter USING petepasswd LOAD_ONLY
SCHEMA_MAP ((db2inst1,db2inst2),(prodschema,devschema))
TABLESPACE_MAP SYS_ANY
NONRECOVERABLE
|
This command copies supported objects in the db2inst1 and prodschema from the SAMPLE database to the ACCTDB database. The authorization ID peter and the associated password is used to connect to ACCTDB. The target tables already exist in ACCTDB and the tables will be repopulated. All objects under the db2inst1 and prodschema schemas are now under db2inst2 and devschema, respectively. Instead of using the tablespace name defined in the SAMPLE database, the default tablespace in ACCTDB will be used instead.
The NONRECOVERABLE option allows the user to
use the tablespaces that were loaded into immediately after the copy
completed. Backups of the tablespaces are not required, but highly
recommended at the earlier convenient time.
ADMIN_COPY_SCHEMA is used to copy a specific
schema and all objects in it. The new target schema objects will be
created using the same object names as the objects in the source
schema, but with the target schema qualifier.
ADMIN_COPY_SCHEMA can be used to copy
tables with or without the data of the original tables.
This procedure invokes the LOAD command for
loading the data into the target schema and, hence, it has three modes
of operations:
DDL— Create empty copies of all supported objects from the source schema.COPY— Create empty copies of all objects from the source schema, then load each target schema table with data. Load is done in NONRECOVERABLE mode. A backup must be taken after callingADMIN_COPY_SCHEMA; otherwise, the copied tables will be inaccessible following recovery.COPYNO— Create empty copies of all objects from the source schema, then load each target schema table with data. Load is done in COPYNO mode.
Example
In this example, the SOURCE_SCHEMA objects residing in table spaces
SOURCETS1 and SOURCETS2 will be moved to the target schema and copied
to the target TARGET_SCHEMA to tablespaces TARGETTS1 and TARGETTS2,
respectively. SYS_ANY indicates that the
remaining tablespaces will use tablespaces chosen by the database
manager based on the tablespace selection algorithm.
CALL SYSPROC.ADMIN_COPY_SCHEMA('SOURCE_SCHEMA', 'TARGET_SCHEMA',
'COPY', NULL, 'SOURCETS1 , SOURCETS2', 'TARGETTS1, TARGETTS2,
SYS_ANY', 'ERRORSCHEMA', 'ERRORNAME')
|
db2look is a handy tool that can be invoked from the command prompt. It can:
- Extract database definition language (DDL) statements from database objects.
- Generate
UPDATEstatements to update database manager and database configuration parameters. - Generate db2set commands to set the DB2 profile registries.
- Extract and generate database statistical reports.
- Generate
UPDATEstatements to replicate statistics on database objects.
Utilities like LOAD require the existence of a target table. You can
use db2look to extract the table's DDL, run it against the target
database, then invoke the LOAD operation.
db2look is very easy to use, as the following examples illustrates.
This command generates DDL statements for all objects created by peter
from the database department, and the output is stored in
alltables.sql.
db2look -d department -u peter -e -o alltables.sql
|
The next command generates:
- DDL for all objects in the database department (specified by
options
-d, -a, and-e). UPDATEstatements to replicate the statistics on all tables and indices in the database (specified by option-m).- GRANT authorization statements (specified by option
-x). UPDATEstatements for the database manager and database configuration parameters, and db2set commands for the profile registries (specified by option-f).
db2look -d department -a -e -m -x -f -o db2look.sql
|
The db2look is also capable of generating commands to register XML
schemas. The following example generates the necessary
REGISTER
XMLSCHEMA and
COMPLETE XMLSCHEMA commands (specified by
option -xs) for objects with schema name
db2inst1. The output db2look.sql will be created under /home/db2inst1,
which is specified in the -xdir option.
db2look -d department -z db2inst1 -xs -xdir /home/db2inst1 -o db2look.sql
|
Benchmarking is a process of evaluating the application in various aspects, such as database response time, CPU and memory usage. Benchmark tests are based on a repeatable environment so the same test runs under the same conditions. Results collected from the tests can then be evaluated and compared.
db2batch is a benchmarking tool that takes a set of SQL and/or XQuery statements, dynamically prepares, and describes the statements, and returns an answer set. Depending on the options used in the db2batch command, the answer set might return elapsed time of execution of the statements, database manager snapshots on memory usage, such as bufferpool and cache information.
You can specify the statements you want to run benchmark on in a flat
file or standard input. A number of control options can be set in the
input file. They are specified with this syntax:
--#SET
control_option value. Here is an
example of an input file with control options. For a complete listing
of control options, please refer to the Information Center.
Listing 38. db2batch example
-- db2batch.sql
-- ------------
--#SET PERF_DETAIL 3
--#SET ROWS_OUT 5
-- This query lists employees, the name of their department
-- and the number of activities to which they are assigned for
-- employees who are assigned to more than one activity less than
-- full-time.
--#COMMENT Query 1
select lastname, firstnme,
deptname, count(*) as num_act
from employee, department, emp_act
where employee.workdept = department.deptno and
employee.empno = emp_act.empno and
emp_act.emptime < 1
group by lastname, firstnme, deptname
having count(*) > 2;
--#SET PERF_DETAIL 1
--#SET ROWS_OUT 5
--#COMMENT Query 2
select lastname, firstnme,
deptname, count(*) as num_act
from employee, department, emp_act
where employee.workdept = department.deptno and
employee.empno = emp_act.empno and
emp_act.emptime < 1
group by lastname, firstnme, deptname
having count(*) <= 2;
|
- Option
PERF_DETAIL 3means that performance detail on elapsed time, a snapshot for the database manager, the database, and the application will be returned. - Option
ROWS_OUT 5means that only five rows are to be fetched from the result set regardless of the actual number of rows returned for the query. COMMENT Query1simply gives the statement a name: Query1.
The following command invokes the benchmark tool against the SAMPLE
database with the input file db2batch.sql:
db2batch -d sample -f
db2batch.sql .
This command will return the result set of both queries limited to five rows, elapsed time, and CPU time of the queries. Database manager, database, and application snapshots are also returned. Since the output is quite large, we are only showing the summary of the db2batch command here.
Listing 39. db2batch summary table
* Summary Table:
Type Number Repetitions Total Time (s) Min Time (s) ...
--------- ----------- ----------- -------------- --------------
Statement 1 1 0.052655 0.052655 ...
Statement 2 1 0.004518 0.004518 ...
...Max Time (s) Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
-------------- --------------- -------------- -------------- -------------
... 0.052655 0.052655 0.052655 5 5
... 0.004518 0.004518 0.004518 8 5
* Total Entries: 2
* Total Time: 0.057173 seconds
* Minimum Time: 0.004518 seconds
* Maximum Time: 0.052655 seconds
* Arithmetic Mean Time: 0.028587 seconds
* Geometric Mean Time: 0.015424 seconds
|
The db2batch command supports many options. We are just listing a few here for you to get an idea the power of the tool:
-m parameter_filespecifies an input file with parameter values to bind to the SQL statement parameter markers.-r result_filespecifies an output file to contain the result of the command.-i short|long|completespecifies what is being measured for the elapsed time intervals.shortmeasures the elapsed time to run each statement.longmeasures the elapsed time to run each statement including overhead between statements.completemeasures the elapsed time to run each statement where the prepare, execute, and fetch times are reported separately.-isospecifies the isolation level used for the statement. By default, db2batch uses the Repeatable Read isolation level.
Table of contents
- Before you start
- Data movement utilities
- DB2
EXPORTutility - DB2
IMPORTutility - DB2
LOADutility - DB2
INGESTutility IMPORT, LOADandINGESTcomparison- Data movement utilities and hidden columns
- ADMIN_MOVE_TABLE procedure
- Other DB2 data movement utilities
- DB2 maintenance utilities
- DB2 Advisors
- Conclusion
- Resources
- About the author
