DB2 notes-Common commands

DB2 notes-Common commands visual database management software can use DbVisualizer, suitable for multiple databases. 1. a tablespace is actually a way to organize data files. Oracle organizes data through the tablespace database object. Before inserting data into the Oracle database, you must first create a tablespace and then insert the data into an object in the tablespace. The best way to explain databases, tablespaces, data files, tables, and data files is to imagine a cabinet filled with stuff. The database is actually a Cabinet, the drawer in the cabinet is a tablespace, the folder in the drawer is a data file, the paper in the folder is a table, and the information written on the paper is data. 2. Run the command line DB2 1) initialize the DB2 environment Win + R (in windows) and enter the BIN directory of the DB2 installation directory. Run the command db2cmd to enter the DB2 CLP window. Note: In this mode, the command statement does not have ";" 2) then enter the command db2, and then you can enter the SQL statement 3 and database version: db2 level 4. view all databases db2 list db directory 5. connect to database sample connect to sample user userName using password 6. Create database command: by default, the Database name of the use case created by db2sampl is sampl. to specify the database name, use db2sampl-name databaseName 7 to import and export data between different databases (1) db2look Link: how to use db2look sample: eq: db2look-d employee-e-generate DDL for all tables, views, etc for the e Mployee databaseand displays on screen db2look-d employee-e-o k. SQL-generate DDL for all tables, views, etc for the employee databaseand sends the output to k. SQL file Copy k. SQL file to the target maching and run the query with thefollowing command db2-tvf k. SQL (2) db2move Link: how to use db2move 8. export and import data 1) DB2 command line export database full database table structure ① Win + R enter the BIN directory of DB2 installation directory, run DB2CMD to enter the DB2 CLP window. Command: DB2CMD ② create a data folder command: MKDIR data description: export the SQL statement of the full table structure of the database to the data directory ③ enter the data directory command: command: DB2LOOK-D DATABASE_NAME-E-A-I USER_NAME-w password-O DB_DLL. SQL description: DATABASE_NAME-Database Name USER_NAME-login Database User Name PASSWORD-login Database User PASSWORD DB_DLL. SQL-database full table SQL script file 2) DB2 command line export database full database data ① Execute Command, export data command: DB2MOVE DATABASE_NAME EXPORT-u USER_NAME-p PASSWORD Description: DATABASE_NAME-Database Name USER_NAME-Logon database username PA SSWORD-Logon Database User Password DB_DLL. SQL-database full table SQL script file prompt:-u,-p must be in lowercase 3) DB2 command line Export Database single table data ① Execute Command, command for exporting single table data: DB2 export to [path (eg .. d:/TABLE_NAME.IXF)] of ixf select [field (eg .. * orcol1, col2 ,...... Coln)] FROM TABLE_NAME; Description: exported file format A: DEL (delimited ASCII format); B: WSF (worksheet format); C: IXF (integrated exchange format, PC version) 4) DB2 command line to IMPORT table data ① Execute Command to IMPORT table data command: DB2 import form [path (eg .. d:/TABLE_NAME.IXF)] of ixf insert into TABLE_NAME; DB2LOAD FROM [path (eg .. d:/TABLE_NAME.IXF)] of ixf insert into TABLE_NAME; DB2LOAD FROM [path (eg .. d:/TABLE_NAME.IXF)] of ixf replace into TABLE_NAME; // Delete the existing record DB2LOAD FROM [path (eg .. d:/TABLE_NAME.IXF)] of ixf restart into TABLE_NAME; // when the load fails, run the command again and export the result and error message DB2LOAD FROM [path (eg .. d:/TABLE_NAME.IXF)] of ixf restart into TABLE_NAME; // import DB2LOAD FROM [path (eg .. d:/TABLE_NAME.IXF)] of ixf modified by identieyigorn insert to TABLE_NAME; CHECK suspension when data is loaded: setintegrity for TABLE1 check immediate unchecked; description: 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.