Common DB2 commands

1. Disable DB2
Db2stop or db2stop force.
In the script, write both of them to avoid using the db2stop force command, for example:
Db2stop
Db2stop force
2. Start DB2
Db2start
3. view the local node directory
DB2 list node directory
4. cataloguing a TCP/IP Node
DB2 catalog TCPIP node <node_name> remote 5. node Cataloguing
DB2 catalog TCP node node_name remote server_ip server server_port
6. node deserialization
DB2 uncatalog node node_name
7. Cancel node Cataloguing
DB2 uncatalog node <node_name>
8. Test node attachment
DB2 attach to node_name
9. Database Cataloguing
DB2 catalog dB db_name as db_alias at node node_name
10. Database deserialization
DB2 uncatalog dB db_alias
11. Generate Definition
DB2-tvf name. SQL
12. batch files
Execute a batch file
DB2-TF batch processing file name
(Use and end each command in the file)
Automatic Generation of batch files
Document: temp. SQL
Select 'runstats on table db2. '| tabname |' with distribution and detailed indexes all; 'from syscat. tables where tabschema = 'db2' and type = 'T ';
DB2-TF temp. SQL> runstats. SQL
13. Enter the DB2 command environment
Run db2cmd in "run ".
14. Database Manager parameter configuration information
Obtain the parameters of the current DB2 database manager.
DB2 get dBm cfg
DB2 get dBm CFG | more
Update Database Manager parameters:
DB2 update dBm CFG using para_name para_value
Obtain the snapshot data of the Database Manager.
DB2-V get snapshot for dBm
15. database parameter configuration information
Obtain information about the management and configuration environment of a database in DB2
DB2 get dB CFG for <dB Name>
Or: Execute DB2 get dB CFG after connecting to a database.
DB2 get dB CFG for <dB Name> | more
Update database parameter configuration information
DB2 update dB CFG for test using para_name para_value
16. Set the joint database to available (the default joint database is unavailable)
DB2 update dBm CFG using federated Yes
17. Check the port number.
DB2 get dBm CFG | grep svcename
18. Change the size of DB2 log Space
Note: The following command is used only for DB2 on the developer's own machine to prevent the DB2 database from using hard disk space excessively. If it is a server, the parameter needs to be modified.
DB2 update dB CFG for <dB Name> using logretain off
Logprimary 3 logsecond 2 logfilsiz 25600;
If the page size is 4 kb, the preceding command creates 3 MB log files, occupying MB of hard disk space. 25600 * 4kb = 102400kb.
19. Restore the temporary data to the roll-forward status.
DB2 rollforward database testdb to end of logs and complete noretrieve
20. view the current CLP settings
DB2 list Comand options
21. Update the specific settings of the current CLP session
DB2 update command options using options...
22. Execute operating system commands in CLP
Add "! "As prefix
DB2! Dirc :/
23. Excessive command duration in CLP
Use "/" as the continuation symbol
24. Modify the value of the registration item
Db2set can be modified
For example:
Sets a parameter db2setparameter = value for the current instance.
Set a global parameter db2setparameter = VALUE-G (lower case)
View the list DB2SET-LR (lower case) of all variables that can be set in the config file Registry)
25. Create a DB2 tool Database
DB2 create tools catalog ooools create new database toolsdb
26. How to perform incremental/differential backup
Incremental: The data added between the last full backup and the current backup;
Delta: The data added between the last backup (which may be full backup, Incremental backup, or differential backup) and the current backup;
27. update statistics for all tables
DB2-V connect to db_name
DB2-V "select tbname, nleaf, nlevels, stats_timefrom sysibm. sysindexes"
DB2-V reorgchkupdate statistics on table all
DB2-V "select tbname, nleaf, nlevels, stats_timefrom sysibm. sysindexes"
DB2-V terminate
28. Run statistics on a table
DB2-V runstatson table tab_nameand indexes all
29. Check whether runstats is executed on the database
DB2-V "select tbname, nleaf, nlevels,
Stats_timefrom sysibm. sysindexes"
30. Any user can set the default mode for a specific database connection by setting the current schema special register. The initial default value is the permission ID of the current session user.
Set schema = <schema Name>
This statement can be used interactively or in applications. If the dynamicrules bind option is used to bind a package, this statement does not work. This statement is not under transaction control.
31. view the database monitoring content list
DB2-V get monitor switches
32. Update the monitoring content of a Database
DB2-V update monitor switches using bufferpoolon
33. Getting database snapshots
DB2-V get snapshot for all databases> snap. Out
DB2-V get snapshot for dBm> snap. Out
DB2-V get snapshot for all bufferpools> snap. Out
DB2-V terminate
34. Resetting database snapshots
DB2-V reset monitor all
35. tablespace
Create a tablespace
Rem creates a buffer pool space of 8 K
DB2 connect to gather
DB2 create bufferpool extends abmp immediate size 25000 pagesize 8 K
Rem creates a tablespace: stma
REM must confirm that the path is correct
Rem D:/db2container/stma
DB2 drop tablespace stma
DB2 create
Regular tablespace stma pagesize 8 K
Managed by System
Using ('d:/db2container/stma ') extentsize 8
Overhead 10.5 prefetchsize 8 transferrate 0.14 bufferpool
Stmabmp
Dropped table recovery off
DB2 connect Reset
Example:
Create a tablespace:
DB2 create tablespace exoatbs in database partition group ibmdefaultgroup pagesize 8 K managed by system using ('/home/exoa2/exoainer ') extentsize 32 prefetchsize 16 bufferpool ibmdefault8k overhead 24.10 transferrate 0.90 dropped table recovery off;
Create System tablespace:
Create temporary tablespace exoasystmp in database partition group ibmtempgroup pagesize 8 K managed by system using ('/home/exoa2/exoasystmp ') extentsize 32 prefetchsize 16 bufferpool ibmdefault8k overhead 24.10 transferrate 0.90 dropped table recovery off;
Create temporary tablespace
DB2 create user temporary tablespace stmaspace pagesize 32 K managed by database using (File 'd:/db2_tab/stmaspace. F1 '10000) extentsize 256
Backup tablespace
Backup database yndc tablespace (userspace1) to "D:/Temp" with 2 buffers buffer 1024 parallelism 1 without prompting
List details of all tablespaces
DB2 list tablespaces show detail
36. Buffer Pool
Create buffer: DB2 create bufferpool <buf_name> size <Number of pages> [pagesize 4096] {[not] extended storage}
Create a buffer pool (8 K ):
DB2 create bufferpool ibmdefault8k immediate size 5000 pagesize 8 K;
Modify the buffer: DB2 alter bufferpool <buf_name> size <Number of pages> {[not] extended storage}
Delete a buffer: DB2 drop bufferpool <buf_name>
In the buffer pool, when the npages of syscat. bufferpools is-1, the Database Configuration Parameter bufferpage controls the buffer pool size.
Change the value of npages to-1:
DB2-V connect to db_name
DB2-V select * From syscat. bufferpools
DB2-V alter bufferpoolibmdefaultbp size-1
DB2-V connect Reset
DB2-V terminate
The command to change the Database Configuration Parameter bufferpages is as follows:
DB2-V update dB cfgfor dbnameusing buffpage bigger_value
DB2-V terminate
Calculate buffer pool hit rate
Ideally, the buffer pool hit rate is above 95%. The formula is as follows:
(1-(buffer pool data physical reads + buffer pool index physical reads)/(buffer pool data logical reads + pool index logical reads) * 100%
37. instance operations
Create a DB2 instance
Db2icrt <Instance name>
List the current instance:
DB2 get instance
List all instances
Db2ilist
Delete A DB2 instance
Db2idrop <Instance name>
Set the current DB2 instance
DB2 set db2intance = DB2
38. user operations
Queries the number of tables owned by a user.
DB2 select * From sysibm. Tables Ables where creator = 'user'
Query functions of a user
DB2 select * From sysibm. sysfunctions
Queries the number of views of a user.
DB2 select * From sysibm. sysviews where creator = 'user'
39. Reorganization check
DB2 reorgchk
40. reorganize the table tb1
DB2 reorg table tb1
41. Use operating system commands
DB2! Dir
42. Cast)
DB2 select empno, cast (resume as varchar (370) from emp_resume where resume_format = 'ascii'
43. UDF
To run a DB2 Java stored procedure or UDF, you also need to update the configuration of the DB2 database management program on the server, including the path to install JDK on this machine.
DB2 update dBm CFG using jdk11_path D: sqllibjavajdk
44. Common commands for managing DB2 server instances
Create a DB2 management server instance under DB2 admin create windows or OS/2
DB2 admin drop windows or OS/2 delete a DB2 management server instance
DB2 get admin CFG displays the configuration parameters of the DB2 Management Server
DB2 update admin CFG modify the configuration parameters of the DB2 Management Server
DB2 reset admin CFG sets the configuration parameters of the DB2 Management Server to the default value.
45. Bind a stored procedure
DB2 connect to databasename user DB2 using pwd
DB2 bind C:/dfplus. Bnd
Copy the stored procedure to the C:/sqllib/function directory on the server.
46. Use the DB2 BACKUP command to back up data
First, close all connections to the database and set the database to "silent:
$ DB2 connect to testdb user db2inst1 using thepasswd
$ DB2 quiesce database immediate force connections
$ DB2 connect Reset
Now you can start the backup:
$ DB2 backup database testdb to "? Home/backup "? User db2inst1 using thepasswd
The "silent" Status of the database is removed:
$ DB2 connect to testdb user db2inst1 using thepasswd
$ DB2 unquiesce Database
$ DB2 connect Reset
Note:
1. The preceding command backs up the database testdb to the specified directory/home/backup. Therefore, make sure that the current login user (db2inst1) has read and write permissions on the directory.
If you want to use the root user for backup, edit the file/etc/group and add the root user to the DB2-related groups: db2grp1, db2fgrp1, and dasadm1.
2. The generated backup file name is as follows:
Testdb.0.db2inst1. node).catn).20050131205259.001
47. Use the DB2 restore command to restore
Restore with the same database name:
$ DB2 Restore database testdb from "? Home/backup"