DB2 Export Table Structure statements

1. Open the DB2 command line using Db2cmd

2.db2look-d db_name-e-a-x-i db_username-w db_passwd-0 file_name.sql #导出数据库结构

3.db2move db_name export-u db_username-p db_passwd #导出数据库数据

4. Create database db_name on ' directory_name ' using codeset utf-8 territory US collate using System; # IBM-EUCJP--An encoding format

5.create schema sch_name authorization sch_passwd; #设置权限

6.DB2 Connect to db_name user username using db_passwd #连接数据库

7.DB2-TVF ' file_name ' #-F read input file-T terminating statement character-v Loopback current command If each statement in the file is directly terminated with a carriage return, use-VF, plus-T to indicate that no statement is ";" End

8.DB2 Database Backup

CMD--->db2cmd--->db2

DB Stop force prevents someone from applying (should db2stop force)

DB Start (should Db2start)

Connect to Wjm_mis User Wyn using Wyn (after reconnection, indicates in use, cannot be backed up)

Backup db wjm_cms to c \

9. Import data in new DB2

Create DATABASE: DB2 Create DB Library name

Import Table structure operation: DB2-TVF file name. sql

Import data operation: Db2move new library name load

We use the Db2look command to get the DDL script for the database object.
DB2 's Db2look command is interpreted as follows:
Db2look Version 8.2
Db2look: Generating DDL to recreate objects defined in the database
Syntax: db2look-d DBname [-E] [-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]
[-wrapper Wrappername] [-server ServerName] [-nofed]

db2look-d DBname [-u Creator] [-S] [-g] [-a] [-t Tname1 Tname2 ... Tnamen]
[-P] [-O Fname] [-I UserID] [-W Password]
Db2look [-h]

-D: Database name: This must specify

-E: Extracting the DDL files needed to replicate the database
This option generates a script that contains a DDL statement
You can run this script against another database to recreate the database objects
This option can be used with the-m option
-U: Create program ID: IF-u and-A are not specified, the $USER will be used
If the-a option is specified, the-u option is ignored
-Z: Mode name: If both-Z and-A are specified, the-Z is ignored
The schema name of the federated part is ignored
-T: Generate statistics for the specified table
The maximum number of tables that can be specified is 30
-TW: DDL is generated for tables that match the schema condition (wildcard) of the name with the table name
The-t option is ignored when the-TW option is specified
-V: DDL is generated for the view only, this option is ignored when-T is specified
-H: More detailed help message
-o: Redirect output to the given file name
If the-o option is not specified, the output goes to the stdout
-A: Generate statistics for all creation programs
If this option is specified, the-u option is ignored
-M: Running the Db2look utility in analog mode
This option generates a script that contains the SQL UPDATE statement
These SQL UPDATE statements capture all statistical information
You can run this script against another database to replicate the initial
The-p,-G, and-s options are ignored when the-m option is specified
-C: Do not generate a mock COMMIT statement
This option is ignored unless-m or-e is specified
Connect and connect RESET statements will not be generated
A COMMIT was omitted. After you execute the script, you need to explicitly implement it.
-r: Do not generate a simulated RUNSTATS statement
The default value is RUNSTATS. This option is only valid if-M is specified
-L: Generate Database layout: Database partition group, buffer pool, and table space.
-X: If this option is specified, the Db2look utility generates an authorization DDL
The original definition of the object is not included for existing authorized privileges
-xd: If this option is specified, the Db2look utility generates an authorization DDL
For existing authorized privileges, including the original definition of the object
-F: Extracting configuration parameters and environment variables
If this option is specified, the-wrapper and-server options are ignored
-FD: Generates DB2FOPT statements for Opt_buffpage and opt_sortheap as well as other configuration and environment parameters.
-TD: Specifies x as the statement delimiter (the default delimiter is a semicolon (;))
Should be used with the-e option (if a trigger or SQL routine exists)
-P: Using clear Text Format
-S: Generate PostScript file
This option will generate a PostScript file for you
When this option is set, all latex and TMP PS files are removed
Required (non-IBM) software: LaTeX and Dvips
Note: The file Psfig.tex must be in the LaTeX input path
-G: Use graphics to display index page access pairs
Gnuplot must be installed, and <psfig.tex> must be in your LaTeX input path
A <filename.ps> file will also be generated with the LaTeX file
-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
The resulting object may contain the following:
wrappers, servers, user mappings, nicknames, type mappings,
function templates, function mappings, and index specifications
-server: Generating DDL for federated objects that apply to this server
The resulting object may contain the following:
wrappers, servers, user mappings, nicknames, type mappings,
function templates, function mappings, and index specifications
-nofed: Do not generate federated DDL
If this option is specified, the-wrapper and-server options are ignored

Latex layout: Latex Filename.tex for Filename.dvi

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 table/nickname 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

Method One

In the Object view window of the control center, select the data table to which you want to export the table structure, press CTRL or SHIFT to select multiple, right-click, and generate DDL.

Method Two

First step: Open the DB2 command-line tool, create a new folder under the Bin folder of the DB2 installation directory, data, and enter the directory.

Create this directory: mkdir data

Enter this directory: CD data

The second step: export the table structure, the command line is as follows:

db2look-d dbname-e-a-x-i username-w password-o ddlfile.sql

After successful execution, you will find the SQL file under the folder you just created.

Step three: Export the data with the following command line:

db2move databasename export-u username-p Password

This concludes the export data.

2 exporting data from a table

Export to [path (example: D: "TABLE1.IXF)] of IXF SELECT [Field (example: * or COL1,COL2,COL3)] from TABLE1;

Export to [path (example: D: "Table1.del)] of the Del Select [Field (example: * or COL1,COL2,COL3)] from TABLE1;

Import data for a table

Import from [Path (example: D: "TABLE1.IXF)] of ixf insert into TABLE1;

Load from [path (example: D: "TABLE1.IXF)] of ixf insert into TABLE1;

Load from [path (example: D: "TABLE1.IXF)] of ixf replace into TABLE1; Delete existing records before loading data

Load from [path (example: D: "TABLE1.IXF)] of ixf restart into TABLE1; When the mount fails, re-executes and logs the export results and error messages

Import from [Path (example: D: "TABLE1.IXF)] of IXF savecount messages [Path (example: D:" Msg.txt)] insert into table1;//where savecount Indicates that every 1000 operations are completed and logged once.

Data import with self-growing fields:

Load from [path (example: D: "TABLE1.IXF)] of IXF modified by Identityignore INSERT into table1;//add modified by Identityignore.

Check pending when loading data is lifted:

SET INTEGRITY for TABLE1 CHECK IMMEDIATE unchecked;

The command only works on the table where the data is checked by the constraint, and if execution cannot be lifted, it is necessary to check the integrity of the data, whether the constraints are not met, and try to reorganize the data before performing the mount operation.

In addition, the literal difference between load and import is: loading and importing, but still not understanding the difference between the two.

Just performance on load is obviously better than import. (load requires more permissions)

Data reference: http://www.knowsky.com/399738.html

DB2 Export Table Structure statements