Db2look and Db2move detailed

Simple examples of db2look and Db2move

---building a library
Create database db_name on filesystem_location using codeset Utf-8 territory CN

---delete a library
DB2 Drop DB db_name

(If the database is in use, you need to execute the following command first)
DB2 Force application All

---export table structure
db2look-d db_name-u user_name-e-o file_to_export.sql

---import table structure
DB2-TVF File_to_import.sql

---export table data
Db2move db_name Export

---importing table data
Db2move db_name import-u username-p Password

---replace table data
Db2move db_name load-u username-p Password

(after load, some tables may need to execute the following command to work properly)
Set integrity for table_name immediate checked

Explain Db2look and Db2move parameters and how to use them in detail

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]

Db2look [-h]

-D: Database name: This must specify

-E: Extracting the DDL files needed to replicate the database

-XS: Exporting XSR objects and generating scripts that contain DDL statements

-xdir: Pathname: The directory that will be used to place the XSR object

-U: Create program ID: if both-u and-A are not specified, the $USER will be used

-Z: Mode name: If both-Z and-A are specified, the-Z is ignored

-T: Generate statistics for the specified table

-TW: DDL is generated for tables that match the schema condition (wildcard) of the name with the table name

-H: More detailed help message

-o: Redirect output to the given file name

-A: Generate statistics for all creation programs

-M: Running the Db2look utility in analog mode

-C: Do not generate a mock COMMIT statement

-r: Do not generate a simulated RUNSTATS statement

-L: Generate Database layout: Database partition group, buffer pool, and table space.

-X: The "Authorization" statement of the original definition program that generated the Exclude object DDL

-XD: Generate an "authorization" statement DDL that includes the original definition program of the object

-F: Extracting configuration parameters and environment variables

-TD: Specifies x as the statement delimiter (the default delimiter is a semicolon (;))

-I: User ID used when logging on to the server where the database resides

-W: the password used to log on to the server where the database resides

-noview: Do not generate create VIEW DDL statement

-wrapper: Generating DDL for federated objects that apply to this wrapper

-server: Generating DDL for federated objects that apply to this server

-nofed: Do not generate federated DDL

-FD: Generates DB2FOPT statements for Opt_buffpage and opt_sortheap and other configuration and environment parameters.

-V: DDL is generated for the view only, this option is ignored when-T is specified

-DP: Generate a DROP statement before the CREATE statement

-CT: Generating DDL Statements by object creation time

Db2move command

Db2move <database-name> <action> [<option> <value>]

First, you must specify the database name (the database in which you want to move the table) and the actions to be performed (export and import or load). Then specify an option to define the scope of the operation. For example, you can limit an action to a specific table (-TN), table space (-ts), table creator (-TC), or schema name (-SN). A subset of the creator of the specified table, table space, or table is only valid for the export operation. If you specify multiple values, you must separate them with commas, and spaces are not allowed between value list items. You can specify a maximum of 10 items.

Alternatively, you can specify the-TF option by using a file name as a parameter that lists the name of the table to export, where each row can only list one full table name. You can also specify the following:

-io import-option

Specifies a mode that the import tool for DB2 can run. Valid options are: CREATE, INSERT, Insert_update, REPLACE, and Replace_create. The default value is Replace_create. For more information about these patterns, see the product documentation for DB2, which can be obtained from DB2 technical support.

-lo load-option

Specifies a pattern that the DB2 load tool can run. Valid options are: INSERT and REPLACE. The default value is INSERT. For more information about these patterns, see the product documentation for DB2, which can be obtained from DB2 technical support.

-L Lobpaths

Specifies the location of the LOB file to create or find. You must specify one or more absolute pathname names. If more than one absolute path is specified, it must be separated by commas, and no spaces are allowed between the values. The default value is the current directory.

-U userid
Specifies a user ID that can be used to log on to the remote system using this user ID.

-p password

Specifies the password to authenticate the user, which needs to be logged on to the remote system with a valid user ID and password.

Instance

1. Export to a file for SQL statements
db2look-d cqyancao-e-o db.sql-i db2user-w PSW
Database name to out file name User name password

Example: db2look-d department-u walid-e-o db2look.sql

--This will generate DDL statements for all tables and federated objects created by user WALID
--The Db2look output is sent to a file named Db2look.sql

Example: db2look-d department-z myscm1-e-o db2look.sql

--This will generate DDL statements for all tables with schema name MYSCM1
--The DDL for all federated objects created $USER will also be generated.
--The Db2look output is sent to a file named Db2look.sql

Example: db2look-d department-u walid-m-o db2look.sql

--This generates an UPDATE statement to capture statistics about the tables/aliases created by the user WALID
--The Db2look output is sent to a file named Db2look.sql

Example: db2look-d department-u walid-e-wrapper w1-o db2look.sql

--This will generate DDL statements for all tables created by user WALID
--will also generate DDL for all federated objects created by the user WALID for the wrapper W1
--The Db2look output is sent to a file named Db2look.sql

Example: db2look-d department-u walid-e-server s1-o db2look.sql

--This will generate DDL statements for all tables created by user WALID
--will also generate DDL for all federated objects created by the user WALID for server S1
--The Db2look output is sent to a file named Db2look.sql

2. Export the database using the Db2move command
Export the initialization data from another database, first into the exported directory, such as: D:\db,
The command is: db2move dbname export–u username–p password. Note: dbname is the original database name, username is the user name password password.

Restore command d:\db> db2move dbname import-u username-p Password

3. Import single table using Db2move export
Export Db2move dbname export-tn yc_news-u db2user-p Password
Import db2move dbname import-u db2user-p Password

4. Other related
DB2 take the first 10 records
For example:
DB2 = SELECT * FROM TableName fetch first and rows only

DB2 modifying field Lengths
DB2 ALTER TABLE Db2admin.config ALTER CVALUE set datatype varchar (255)
For example:
DB2 = ALTER TABLE news ALTER AUTHOR SET data type varchar (250)
DB2 = Describe table news

DB2 adding field methods
ALTER TABLE TABLE_NAME
Add Column column_name datatype

Db2look and Db2move detailed