DB2 Statement details

DB2 Statement details 1. Data Operation Language (DML: select, delete, insert, update)

<1> query the database directory: db2 list db directory <2> query database tables db2 list tables current user db2 list tables for all tables db2 list tables for schema schemaname tables in the specified mode <3> display table structure db2 describe table tablename <4> insert data db2 insert into tablename (field name, field name ...) values (values corresponding to Field Names) db2 insert into tablename1 (Field 1, Field 2, Field 3 ...) select Field 1, Field 2, Field 3... from tablename2 + query condition <5> change table or view data db2 update tablename/viewname set field name 1 = '', Field 2 = '',... + query condition <6> delete data db2 delete from tablename where + condition <7> import data db2 "import from E: \ name.txt of del insert into tableName" db2 "import from E: \ name. ixf of ixf commitcount 5000 insert/create/replace into tableName "db2" load client from D: \ xx.txt of del insert/replace into tabName "(No logs need to be written, but the table must exist before insertion; cannot create table) db2 "load client from D: \ xx.txt of del restart/terminate into tabName" when the imported data is forcibly interrupted due to a problem, this table is locked. You can use this command to unlock <8> export data db2 "export to E: \ name.txt of del select * from tableName" db2 "export to E: \ name.txt of del modified by nochardel select * from tableName "(export data without semicolons) export table structure and data db2" export to E: \ name. ixf of ixf modified by nochardel select * from tableName "db2" export to E: \ name. ixf of ixf modified by nochardel select * from tableName fetch first (number + UNM) rows only "(fixed number) export the table structure db2look-d dbName-e-t tableName-o D: \ xxx. SQL (path) -I userName-w password db2look-d dbName-z tabSchema-e-c-I userName-w password-o + path name export stored procedure structure db2 "export to xxx. SQL of del select text from syscat. procedures where procname = 'Upper case Stored Procedure name' "<9> query table Status db2 load query table + tableName <10> query the data volume of the current table (when data is stored in the database) db2 select count (1) from tab with ur <11> modify the current table name and schema name db2 rename table tab1 to tab2

 

2. Data Definition Language (DDL: create, alter)

<1> Create or delete an instance db2icrt instance_name/db2idrop-f instance_name linux: db2icrt-u user_id instance_name <2> create a view, table, and schema db2 create view/table/schema create the schema of a specified user db2 create schema schName AUTHORIZATION userName db2 create schema AUTHORIZATION userName (not specified mode name, the Mode name is implicitly the userName) defines the table db2 create table tableName with default values (column1 data type, column2 data type default 'default ') create table clone_tablename like tablename db2 create table clone_tablename as (select * from tablename) definition only create materialized query table (MQT) create table new_table_name as (select * from table_name) data initially deferred refresh deferred; refresh table new_table_name;

 

Note: A materialized table is similar to a Query and does not actually form a table. The type is displayed as Query. But it can be used as a table. Create a table and specify its index tablespace

Db2 create table (...) in userspace1 INDEX in userspace2 (userspace1 is the table space, and userspace2 is the table INDEX space)

 

<3> Create a view

Db2 create view viewname as select field name 1, field name 2... from table where + condition with check option: each row inserted or updated through a view must comply with the view definition, such as: create view emp_view2 (empno, empname, deptno) as (select id, name, dept from employee where dept = 10) with check option when this view is used to update data or insert new values, with check option limits the input values of the dept Column

 

<4> modify a table (column, primary key, unique constraint, check constraint)

1) ADD the new COLUMN alter table tablename add column columnname data type 2) ADD constraints 3) modify the table field alter table tablename alter columnname set data type 4) add primary key alter table tablename add primary key (c1, c2) <5> drop schema schName <CASCADE | RESTRICT> CASCADE) indicates that all database objects in this mode are deleted simultaneously in the delete mode. RESTRICT indicates that deleting is restricted when database objects are defined in this mode; if no database object exists, the <6> reorganizing the table and its indexes reorg the table data reorg table tableName index indexName (based on the index) and reorg indexes can be deleted. All for table tableName <7> re-collect the table and its index statistics runstats on table tableName for indexes all (re-collect the table information used before running the batch to improve efficiency) <8> the IDENTITY column generated always as identity (start with 1, increment by 1) of the DB2 auto-increment primary key method specifies a field as a self-incrementing field and places it after the data type.

 

SEQUENCE object (SEQUENCE) 3. Data Control Language (DCL: grant, revoke)

Grant the table privileges to the user grant select, update, delete on table tableName to user userName with grant option grant the package privileges to the same group grant control on package packageName on group groupName with grant option