DB2 database usage

1. Use db2cmd to open the db2 command line
 
2. db2look-d db_name-e-a-x-I db_username-w db_passwd-0 file_name. SQL # export the database structure
 
3. db2move db_name export-u db_username-p db_passwd # export Database Data
 
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; # Set permissions
 
6. db2 connect to db_name user username using db_passwd # connect to the database
 
7. db2-tvf 'file _ name' #-f reads the input file-t termination statement character-v returns the current command. If each statement in the file ends with a carriage return, use-vf, and add-t to indicate that no statement ends ";".
 
8. DB2 database backup
 
Cmd ---> db2cmd ---> db2
Db stop force prevents application (db2stop force should be used)
Db start (db2start)
// Connect to wjm_mis user wyn using wyn)
Backup db wjm_cms to c :\
9. import data in the new db2
 
Create Database: db2 create db database name
 
Import table structure operation: db2-tvf file name. SQL
 
Import data operation: db2move new database name load
 
Run the db2look command to obtain the DDL script of the database object.
DB2's db2look command is interpreted as follows:
Db2look 8.2
Db2look: Generate 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 be specified
 
-E: extract the DDL files required for database replication.
This option will generate scripts containing DDL statements
You can run this script on another database to recreate the database object.
This option can be used with the-m Option
-U: ID of the program to be created: If-u and-a are not specified, $ USER is used.
If the-a option is specified, the-u option is ignored.
-Z: Mode name: if both-z and-a are specified,-z is ignored.
The Mode name of the union is ignored.
-T: Generate Statistics for the specified table
You can specify a maximum of 30 tables.
-Tw: generates DDL statements for tables whose names match the table's schema condition (wildcard ).
If the-tw option is specified, the-t option is ignored.
-V: Only DDL is generated for the view. This option is ignored when-t is specified.
-H: More Detailed Help messages
-O: redirects the output to the specified file name.
If the-o option is not specified, the output is forwarded to stdout.
-A: generate statistics for all created programs
If this option is specified, the-u option is ignored.
-M: run the db2look utility in simulated mode.
This option generates a script containing the SQL UPDATE statement.
These SQL UPDATE statements capture all statistics
You can run this script on another database to copy the initial one.
If the-m option is specified, the-p,-g, and-s options are ignored.
-C: do not generate a simulated COMMIT statement
This option is ignored unless-m or-e is specified.
CONNECT and connect reset statements will not be generated
COMMIT is omitted. After the script is executed, it must be implemented explicitly.
-R: do not generate a simulated RUNSTATS statement.
The default value is RUNSTATS. This option is valid only when-m is specified.
-L: generate the database layout: database partition group, buffer pool, and tablespace.
-X: If this option is specified, the db2look utility generates the authorization DDL
For existing authorized privileges, excluding the object's original definer
-Xd: If this option is specified, the db2look utility generates the authorization DDL
For existing authorized privileges, including the object's original definer
-F: Extracts configuration parameters and environment variables.
If this option is specified, the-wrapper and-server options are ignored.
-Fd: generates the db2fopt statement for opt_buffpage, opt_sortheap, and other configuration and environment parameters.
-Td: Specify x as the statement delimiter (the default Delimiter is semicolon (;))
It should be used with the-e Option (if the trigger or SQL routine exists)
-P: in plaintext format
-S: generate a 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: displays index page access pairs using graphs.
Gnuplot must be installed and <psfig. tex> must be in your LaTeX input path
The <filename. ps> file will also be generated along with the LaTeX File
-I: the user ID used to log 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 the create view ddl statement.
-Wrapper: Generate DDL for the Union object that applies to this wrapper
The generated object may contain the following items:
Package, server, user ing, nickname, type ing,
Function templates, function ing, and index specifications
-Server: Generate DDL statements for the associated objects on this server.
The generated object may contain the following items:
Package, server, user ing, nickname, type ing,
Function templates, function ing, 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 to get filename. dvi
 
Example: db2look-d DEPARTMENT-u walid-e-o db2look. SQL
 
-- This generates DDL statements for all tables and associated 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 the schema name MYSCM1
-- DDL of all associated objects created by $ USER is also 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
-- DDL will also be generated for all Union objects created by user WALID of the package 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
-- DDL for all associated objects created by user WALID of server S1 will also be generated
-- The db2look output is sent to a file named db2look. SQL.
 
 
 
 
 
 
 
 
 
 
Method 1
 
In the object View window of the control center, select the data table of the table structure to be exported, press Ctrl or Shift to select multiple data tables, right click, and choose-> Generate DDL.
 
Method 2
 
◆ Step 1: Open the command line tool of DB2, create a new folder data under the BIN folder of the DB2 installation directory, and enter the directory.
 
Create this directory: mkdir data
Go to the directory: cd data
 
◆ Step 2: 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 in the folder you just created.
 
◆ Step 3: export data. The command line is as follows:
Db2move databasename export-u username-p password
 
 
 
So far, data export has ended.
 
2. Export table data
 
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 del select [field (example: * or col1, col2, col3)] from TABLE1;
 
Import table data
 
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 (for example: D: "TABLE1.ixf)] of ixf replace into TABLE1; // delete an existing record before loading data.
 
Load from [path (for example: D: "TABLE1.ixf)] of ixf restart into TABLE1; // when loading fails, re-execute and record export results and error messages
 
Import from [path (example: D: "TABLE1.ixf)] of ixf savecount 1000 messages [path (example: D:" msg.txt)] insert into TABLE1; // where, savecount indicates that every 1000 operations are completed and recorded once.
 
Import data with auto-increment fields:
 
Load from [path (example: D: "TABLE1.ixf)] of ixf modified by identityignore insert into TABLE1; // Add modified by identityignore.
 
Check suspension occurs when data is loaded:
 
Set integrity for TABLE1 check immediate unchecked;
 
The command is only valid for the tables whose data passes the constraints check. If the execution cannot be lifted, it is necessary to check the data integrity, whether it does not meet the constraints, and try to refresh the data, and then perform the load operation.
 
In addition, for load and import, the literal difference is: load and import, but still do not understand the difference between the two.
 
But load is obviously better than import in terms of performance (load requires more permissions)
 
 
For example:
1. export data: db2 export to t_sys_class.ixf of ixf select * from ams. t_sys_class
2. Import:
Db2 load from t_sys_class.ixf of ixf modified by identityignore insert into docsdb. t_sys_class