First, common commands
Set up the database DB2_GCB
Create Database DB2_GCB ON G: Alias DB2_GCB
USING CODESET GBK TERRITORY CN Collate Using System DFT_EXTENT_SZ 32
2. Connect the database
Connect to Sample1 User DB2Admin Using 8301206
3. Establish an alias
Create Alias DB2Admin.tables for sysstat.tables;
Create Alias DB2Admin.views for syscat.views
Create alias db2admin.columns for syscat.columns;
Create alias guest.columns for syscat.columns;
4. Establish a table
Create Table ZJT_TABLES AS
Definition Only;
Create Table ZJT_Views AS
Select * from views) definition.
5. Insert record
INSERT INTO ZJT_TABLES SELECT *.
INSERT INTO ZJT_VIEWS SELECT *.
6. Create a view
Create View v_zjt_tables asselect tabschema, tabname from zjt_tables;
7. Create a trigger
Create Trigger ZJT_TABLES_DEL
After delete on zjt_tables
Referencing Old As O
For Each Row Mode DB2SQL
INSERT INTO ZJT_TABLES1 VALUES (Substr (O. Tabschema, 1, 8), Substr (O. TabName, 1, 10))
8. Establish a unique index
Create Unique Index I_ZTables_TABNAME
On ZJT_TABLES (TabName);
9. View the table
Select Tabname from Tables
WHERE TABNAME = 'zjt_tables';
10. View column
SELECT SUBSTR (ColName, 1, 20) AS column name, Typename As type, Length AS length
From columns
WHERE TABNAME = 'zjt_tables';
11. View the table structure
DB2 DESCRIBE TABLE USER1.DEPARTMENT
DB2 DESCRIBE SELECT * from User.tables
12. View the index of the table
DB2 DESCRIBE INDEXES for Table User1.Department
13. View view
Select ViewName from views
WHERE VIEWNAME = 'v_zjt_tables;
14. View index
Select Indname from Indexes
WHERE indeame = 'i_ztables_tabname';
15. View the storage process
SELECT SUBSTR (Procschema, 1, 15), Substr (procname, 1,15)
From syscat.procedures;
16. Type Conversion (CAST)
IP DataType: VARCHAR
SELECT CAST (IP as INTEGER) 50 from log_comm_failed
17. Reconnect
Connect Reset
18. Interrupt Database Connection
Disconnect DB2_GCB19. View Application
List application;
20. Kill Application
Force Application (0);
DB2 Force Applications All (forced all applications from database disconnection)
21. Lock Table
Lock Table Test in Exclusive Mode
22. Sharing
Lock Table Test in Share Mode
23. Show all current users
List tables
24. List all system tables
List table for system
25. Show current activity database
List Active Databases
26. View Command Options
List command Options
27. System database directory
List Database Directory
28. Table space
List tablespaces
29. Table space container
List tablepace Containers for
Example: List TableSpace Containers for 1
30. Display access rights for user databases
Get Authorizations
31. Starting instance
DB2Start
32. Stop instance
DB2STOP
33. Table or view privilege
Grant SELECT, DELETE, INSERT, UPDATE ON TABLES TO USER
Grant All on Tables To User with Grant Option
34. Program package
Grant Execute
On Package Package-Name
TO PUBLIC
35. Mode privilege
Grant Createin on Schema Schema-Name To User
36. Database privilege
Grant Connect, CreateTab, DBADM on Database to User
37. Index privilege
Grant Control On Index Index-Name To User
38. Information help (? Xxxnnnn)
SQL30081
39. SQL Help (Description Syntax of the SQL statement)
Help Statement
For example, Help SELECT
40. SQLSTATE Help (Description SQL status and category code)
? SQLSTATE or? Class-Code
41. Change passwords related to "Management Server"
DB2ADMIN SETID UserName Password
42. Create a Sample Database
DB2SAMPL
DB2SAMPL F: (specified installation disk)
43. Using the operating system command
DIR! DIR
44. Conversion Data Type (CAST)
Select Empno, Cast (Resume As Varchar (370))
From EMP_RESUME
Where resume_format = 'ascii'
45. UDF
To run the DB2 Java stored procedure or UDF, you also need to update the DB2 database management program configuration on the server to include the path to install JDK on the machine.
DB2 UPDATE DBM CFG Using JDK11_PATH D: / SQLLIB / JAVA / JDK
Terminate
Update DBM CFG Using SPM_Name Sample
46. Check the DB2 Database Manager configuration
DB2 GET DBM CFG
47. Retrieve all authorized names with privileges
Select Distinct Grantee, Grantetype, 'Database' from syscat.dbauth
Union
Select Distinct Grantee, GranteType, 'Table' from syscat.tabauthunion
Select Distinct Grantee, GranteType, 'Package' from syscat.pageauth
Union
Select Distinct Grantee, Grantetype, 'INDEX' from syscat.indexauth
Union
Select Distinct Grantee, GranteType, 'Column' from syscat.colauth
Union
Select Distinct Grantee, Grantetype, 'Schema' from syscat.schemaauth
Union
Select Distinct Grantee, GranteType, 'Server' from syscat.passthruauth
ORDER by Grantee, Granteetype, 3
Create Table YHDAB
(id varchar (10),
Password varchar (10),
YWLX VARCHAR (10),
KH varchar (10));
Create Table Ywlbb
(YWLBBH VARCHAR (8),
YWMC VARCHAR (60))
48. Modify the table structure
Alter Table Yhdab Alter Kh Set Data Type Varchar (13);
Alter Table Yhdab Alter ID Data Type Varchar (13);
ALTER TABLE LST_BSI ALTER BSI_MONEY SET DATA TYPE INT;
Insert Into Yhdab Values
('20000300001', '123456', 'USER01', '2000030000001'),
('20000300002', '123456', 'USER02', '2000000002');
49. Business Type Description
Insert Into Ywlbb Values
('User01', 'Business Application ",
('USER02', 'Business Cancel'),
('USER03', 'cost query'),
('USER04', 'cost is self-receiving'),
('USER05', 'cost preservation'),
('USER06', 'password modification "),
('USER07', 'invoice print'),
('GL01', 'Change User Basic Information'),
('GL02', 'Change Payment Information'),
('GL03', 'Day Statistics "),
('GL04', 'Billing Function'),
('GL05', 'Subsidist "),
('GL06', 'Billing Function'),
('GL07', 'Comprehensive Statistics')
2. Directory View Description
Description Directory View
Check constraints syscat.checks
Syscat.columns
Check the columns of constraints Syscat.colchecks
Columns used in keywordscat.KeyColuse
Data Type Syscat.DataTypes Function Parameters or Functions Syscat.FuncParms
Reference constraints syscat.references
Mode syscat.schemata
Table constraints syscat.tabconst
Table syscat.tables
Trigger Syscat.Triggers
User definition function syscat.functions
View syscat.views
String type
Binary large object (BLOB) string.
Character Large Object (CLOB) string, its character sequence can be a single-byte character or multibyte character, or a combination of both.
Double-byte character large object (dbclob) string, its character sequence is a double-character character.
Database pattern
The first specification form: There is a value in each row in the table and each column, never a set of values.
The second specification: The fact that each column in the keyword is not provided depends on the fact that the entire keyword is provided.
The third specification: Each non-key column provides facts with other non-keyword columns and depends only on the keyword.
The fourth specification: There is no row containing two or more independent factors related to an entity.
V. Data type
Data type type characteristic example or range
CHAR (15) The maximum length of 254 'sunny day'
VARCHAR (15) grows the maximum length of 4000 'sunny day'
Smallint digital length is 2 bytes of character accuracy is 5 digits to -32768 to 32767
The integer number is 4 bytes of length in 10 digits to -2147483648 to 2147483647
REAL digital single precision floating point 32-bit approximation range is -3.402E 38 to -1.175E-37 or 1.175E-37 to -3.402E 38 or zero
Double Digital Double Precision Floating Point 64-bit approximation range is -1.79769E 308 to -2.225E-307 or 2.225E-307 to 1.79769E 308 or zero
Decimal (5, 2) number accuracy is 5 decimal bits 2 ranges to -10 ** 31 1 to 10 ** 31-1
Date Date Time 3 Part Value 1991-10-27
Time Date Time 3 Part Value 13.30.05
TimeStamp Date Time Seven Values 1991-10-27-13.30.05.000000
Six. Column function
The column function calculates a set of values in the column to obtain a single result value. The following is an example of some column functions.
AVG
Returns the value of a group removed in this group
Count
Returns a number of rows or values of the bank or the number of values
Max
Returns the maximum value in a set of values
MIN
Returns the minimum value in a set of values
Seven. Scalar function
The scalar function performs a value to return another value. The following is some examples of scalar functions provided by the DB2 universal database.
ABS
The absolute value of the number of returns
HEX
Hexadecimal representation of the return value
Length
Returns the number of bytes in the argument (returns the number of double-word characters for the graphic string.)
Year
Extract the year part of the date time
