DB2 list of frequently used commands
1. To establish a 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 to the database
connect to sample1 user db2admin using 8301206
3. To 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. The establishment of table
create table zjt_tables as
(Select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from tables) definition only;
create table zjt_views as
(Select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from views) definition only;
5. Insert Record
insert into zjt_tables select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from tables;
insert into zjt_views select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from views;
6. To establish the view
create view V_zjt_tables as select tabschema, tabname from zjt_tables;
7. To establish the 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. To establish uniqueness of the index
CREATE UNIQUE INDEX I_ztables_tabname
[Size = 3] ON zjt_tables (tabname);
9. See Table
select tabname from tables
where tabname = 'ZJT_TABLES';
10. See column
select SUBSTR (COLNAME, 1,20) as column names, TYPENAME as type, LENGTH as the length of
from columns
where tabname = 'ZJT_TABLES';
11. See table structure
db2 describe table user1.department
db2 describe select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp from user.tables
12. View the index 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 indname = 'I_ZTABLES_TABNAME';
15. See store 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. To re-connect
connect reset
18. Interrupt the database connection
disconnect db2_gcb
19. View application
LIST APPLICATION;
20. Kill application
FORCE APPLICATION (0);
db2 force applications all (to force all applications disconnect from the database)
21. Lock table
lock table test in exclusive mode
22. Sharing
lock table test in share mode
23. Shows all the tables the current user
list tables
24. List all the system table
list tables for system
25. Display the currently active database
list active databases
26. See command options
list command options
27. The system database directory
LIST DATABASE DIRECTORY
28. Tablespace
list tablespaces
29. Table space containers
LIST TABLESPACE CONTAINERS FOR
Example: LIST TABLESPACE CONTAINERS FOR 1
30. Displays the user access to the database
GET AUTHORIZATIONS
31. Start instance
DB2START
32. Stop instance
db2stop
33. Table or view privileges
grant select, delete, insert, update on tables to user
grant all on tables to user WITH GRANT OPTION
34. Package privileges
GRANT EXECUTE
ON PACKAGE PACKAGE-name
TO PUBLIC
35. Mode privilege
GRANT CREATEIN ON SCHEMA SCHEMA-name TO USER
36. Database Privileges
grant connect, createtab, dbadm on database to user
37. Index privileges
grant control on index index-name to user
38. Information to help (? XXXnnnnn)
Cases:? SQL30081
39. SQL help (note the syntax of SQL statements)
help statement
For example, help SELECT
40. SQLSTATE help (note SQL state and class code)
? Sqlstate or? Class-code
41. Change and the “Management server” password-related
db2admin setid username password
42. Create SAMPLE database
db2sampl
db2sampl F: (specify the installation disk)
43. Use the operating system command
! Dir
44. Convert data types (cast)
SELECT EMPNO, CAST (RESUME AS VARCHAR (370))
FROM EMP_RESUME
WHERE RESUME_FORMAT = 'ascii'
45. UDF
To run DB2 Java stored procedure or UDF, also need to update the DB2 database management server configuration to include the JDK is installed in the path of the machine
db2 update dbm cfg using JDK11_PATH d: sqllibjavajdk
TERMINATE
update dbm cfg using SPM_NAME sample
46. Check the DB2 database manager configuration
db2 get dbm cfg
47. Search all authorization names with privileges
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE' FROM SYSCAT.TABAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE' FROM SYSCAT.PACKAGEAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX' FROM SYSCAT.INDEXAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN' FROM SYSCAT.COLAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA' FROM SYSCAT.SCHEMAAUTH
UNION
SELECT DISTINCT GRANTEE, GRANTEETYPE, '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 table structure
alter table yhdab ALTER kh SET DATA TYPE varchar (13);
alter table yhdab ALTER ID SET DATA TYPE varchar (13);
alter table lst_bsi alter bsi_money set data type int;
insert into yhdab values
('20000300001 ', '123456', 'User01', '20000300001 '),
('20000300002 ', '123456', 'User02', '20000300002 ');
49. Business Type Description
insert into ywlbb values
('User01', 'business applications'),
('User02', 'business Undo'),
('User03', 'cost of query'),
('User04', 'cost of self-pay'),
('User05', 'cost of pre-existing'),
('User06', 'password changes'),
('User07', 'Print invoice'),
('Gl01', 'change the user basic information'),
('Gl02', 'change the payment information'),
('Gl03', 'date statistical functions'),
('Gl04', 'strike a balance function'),
('Gl05', 'reconciliation function'),
('Gl06', 'billing function'),
('Gl07', 'General Statistics')
Recommended links:
The set EAM2008 into Yunnan Lancang Lead
View open ports: Let Trojan True Features
How to improve the agency's initiative
Cleverly hide an important part of the worksheet so that no leakage
You before the boss look like a duck in a variety of Lotus
faulting Module msvcr80 dll version 8.0 50727.1433
Qualcomm CEO: WiMax 3G will compete in the face of setbacks
National Social Security card can break the ice in the Yangtze River Delta region



