目录
前言
由于最近一直在忙着做DB2数据库方面的性能测试，总是遇到各种奇葩问题，然后各种百度，后来一想，还不如把常用命令都记下来，方便后续用的时候节省时间。
对了，公司的DB2是安装在AIX操作系统上的。
查看系统RUNSTATS状态
| select tabname,colnames,create_time,stats_time,nleaf,nlevels from syscat.indexes where tabname = 'XXXXX' and tabschema = 'XXXXXX';
select tabname,alter_time,stats_time,last_regen_time from syscat.tables where tabname = 'XXXXX' and tabschema = 'XXXXXX'; |
查找需要RUNSTATS的表和索引
| ①select tabname,INDNAME from syscat.indexes where stats_time is null; ②select tabname from syscat.tables where stats_time is null; 为了捕捉表及其索引的统计信息，使用如下命令： runstats on table schema.tabname with distribution and detailed indexes all; |
db2dialog
查询某个时间点之后的db2dialog日志信息：
| db2diag -time 2021-04-09-11.00.00.000000 |
模糊查询规则：
-g: 搜索符合搜索一系列“<列标志>=<列值>”条件的诊断日志记录，条件中间使用逗号分开。搜索区分大小写。
-gi: 功能等同于-g,搜索不区分大小写。
-gv: 搜索不符合一系列“<列标志>=<列值>”条件的诊断日志记录，条件中间使用逗号分开。搜索区分大小写。
-gvi:功能等同于-gv,搜索不区分大小写。
= 全字精确匹配查询
:= 部分匹配模糊查询
!= 查找不符合全字精确匹配查询条件的记录
!:= 查找不符合部分匹配模糊查询条件的记录
^= 选择查找列中以后面的查找条件开头的记录
!^= 选择查找列中不以后面的查找条件开头的记录
| db2diag -time 2021-04-09-11.00.00.000000 -gi MESSAGE:=indexes db2diag -time 2021-04-09-11.00.00.000000 -gi MESSAGE:=XXXX db2diag -time 2021-04-09-11.00.00.000000 -gi MESSAGE:=syscat.indexes db2diag -time 2021-04-09-11.00.00.000000 -gi MESSAGE:=syscat |
监控执行时间最长的SQL语句
| select * from sysibmadm.TOP_DYNAMIC_SQL order by AVERAGE_EXECUTION_TIME_S desc fetch first 5 rows only; |
db2expln
| db2expln -d picpodb -q "SQL语句" -t |
查看DB2特定参数值
| db2 get db cfg for 数据库名称 | grep RUNSTATS db2 update db cfg for 数据库名称 using AUTO_RUNSTATS OFF |
查看DB2特定表空间信息
| 首先，select * from syscat.tables where 查询条件，从这里面找到该表所在的表空间
然后，db2 list tablespaces show detail | grep -p USERSPACE1 |
重启DB2数据库
| 1.查看是否有活动的链接 db2 list applications for db 数据库名字
2.关闭连接 db2 force application all
3.再执行一次步骤1中的命令，查看链接是否全部关闭。
4.执行停止实例命令 db2stop
5.执行实例启动命令 db2start |
快速删除大量数据
| 新建null.del文件，让后放到特定目录下，然后用load - from去加载
db2 “LOAD FROM /xxx/null.del of del REPLACE INTO 表名” |
db2batch
说明
| *** Invalid or unsupported long command line option *** For option "-help"
* Type 'db2batch -h' for help. [db2inst1@DB1:/home/db2inst1]#db2batch -h
Usage:
db2batch [-d dbname] [-a userid[/password]] [-f sql_file] [-m parameters_file] [-r result_file[,summary_file]] [-v on||off] [-s on||off] [-q on||off||del] [-g on||off] [-l stmt_delimiter] [-t col_delimiter] [-w col_width] [-c on||off] [-iso rr||rs||cs||ur] [-car [cc||wfo] [-cli [cache_size]] [-o [option value] [...]] [-time on||off] [-i short||long||complete] [-msw [switch on||off||hold] [...]] [-mss [snapshot] [...]] [-z output_file[,summary_file]] [-h] [-u] [-?]
('db2batch -h' for extended help)
Where: (All options and values are case insensitive)
-d Name of database to connect to. Default is to use the name set in $DB2DBDFT. -a Authentication host user id with optional password. Default is to not specify a user id or password. -f Input file containing SQL statements. Default is to use stdin. -m Input file containing parameter values to bind to SQL statement parameter markers before executing a statement. Default is to not bind parameters. -r Output file to contain the query results. If 'summary_file' is specified, it will contain the summary table. Default is to stdout. (screen) -v Verbose mode. Reports extra information during processing. Default is off. -s Summary table. Provides a summary of elapsed times with arithmetic and geometric means, complete with the number of query rows fetched and output. Default is on. -q Query output only. The output options are: off - Output information and headings with query results. on - Output only query results in non-delimited format. del - Output only query results in delimited format. Default is off. -g Group block output. When 'on', takes one snapshot for the entire block and reports only block timing in the summary table. When 'off', takes a snapshot and reports summary table timing for each statement executed in the block. Default is on. -l SQL Statement Delimiter. Changes SQL statement separator. Default is ';'. -t Single character output SQL column separator. Specify -t TAB for tab or -t SPACE for space. Default is SPACE for '-q on' and ',' for '-q del'. -w Maximum Result Set Column Width. Truncates all data to this width for displaying, unless data cannot be truncated. The width may be increased in order to eliminate the warning CLI0002W and thus get more accurate Fetch time. Default is 32768, ranging from 0 to 2G. -c Automatically commit after each SQL statement. Default is on. -iso Isolation Level. rr - Repeatable Read (ODBC Serializable) rs - Read Stability (ODBC Repeatable Read) cs - Cursor Stability (ODBC Read Committed) ur - Uncommitted Read (ODBC Read Uncommitted) Default is rr. -car Concurrent Access Resolution cc - Currently Commited wfo - Wait for Outcome -cli CLI Execution. This option exists for backwards compatibility with versions where the default execution mode was not CLI. The optional cache size argument is also ignored. -o Control option and value pairs. The possible options are: r <rows output> (Maximum query result rows shown) Default is -1. (All) f <rows fetched> (Maximum query result rows fetched) Default is -1. (All) p <performance detail> (A detail level between 0 and 5) 0 - No timing or monitoring snapshots. 1 - Timing information. 2 - 1 and agent_id snapshot. 3 - 2 and db2, dbase snapshots. 4 - 3 and dbase_applinfo snapshot. 5 - 4 and dbase_tablespaces, dbase_tables, dbase_bufferpools snapshots. Default is 1. o <query optimization class> (A class of 0-3, 5, 7, or 9) Default is -1. (Server default) e <explain mode> no - Execute query but do not explain. explain - Explain query but do not execute. yes - Explain and execute the query. Default is no. s <error stop> no - Continue when encountering non-critical errors yes - Stop when encountering errors Default is no. -time SQL statement timing. Default is on. -i Elapsed time interval measurement. short - elapsed time to run the statement. long - elapsed time to run the statement including overhead between statements. complete - elapsed time to run the statement where the prepare, execute, and fetch times are reported separately. Default is short. -msw Monitor Switches. Each switch specified should be followed by either 'on', 'off', or 'hold' to turn on, turn off, or keep the switch's previous state respectively. The possible switches are: uow, statement, table, bufferpool, lock, sort, timestamp The special switch 'all' sets all of the above switches. By default all switches are set to 'hold'. -mss Monitor Snapshots. List all monitoring snapshots that should be taken after each statement or block is executed. More than one snapshot can be taken at a time, with information from all snapshots combined into one large snapshot before printing. The possible snapshots are: applinfo_all, dbase_applinfo, dcs_applinfo_all, db2, dbase, dbase_all, dcs_dbase, dcs_dbase_all, dbase_remote, dbase_remote_all, agent_id, dbase_appls, appl_all, dcs_appl_all, dcs_appl_handle, dcs_dbase_appls, dbase_appls_remote, appl_remote_all, dbase_tables, appl_locks_agent_id, dbase_locks, dbase_tablespaces, bufferpools_all, dbase_bufferpools, dynamic_sql The special snapshot 'all' takes all of the above snapshots. Any snapshots involving an appl ID are not supported in favour of their agent ID (appl handle) equivalents. Default is to not take any monitoring snapshots. -z Redirect all output to a output file. It is similar to the -r option, but includes any messages or error codes with the output. If 'summary_file' is specified, it will contain the summary table. Default is to stdout. -h Display this help screen. -u Alias for -h. -? Alias for -h.
SQL Input File Format:
SELECT * FROM TABLE; (all SQL statements must end with a delimiter) HELP; (display this help message and db2batch notes) QUIT; (stop executing any more SQL statements) -- comment (comments are ignored) --#COMMENT comment (comment appears in the output) --#BGBLK [repeat_count] (start of a block of queries) --#EOBLK (end of a block of queries) --#PARAM <value> [...] (values for a parameter in next SQL statement) --#SET <option> <value> (see following table)
----------------------------------------------------------------- option value default description ----------------------------------------------------------------- ROWS_FETCH -1 to n -1 -1 fetches all the rows ROWS_OUT -1 to n -1 -1 shows all fetched rows PERF_DETAIL 0 to 5 1 Performance detail level TIMING on/off on Print timing information SNAPSHOT string none Monitoring snapshots to take DELIMITER string ; SQL statement delimiter PAUSE n/a n/a Stop and wait for user input TIMESTAMP n/a n/a Output a timestamp SLEEP 1 to n n/a Sleep for x seconds ERROR_STOP no/yes no Stop when encountering errors
Note:
Blocks of queries can be looped over multiple times by specifying a repeat count when defining the block. The statement(s) in the block will only be prepared the first time.
If a parameters file is used, then each line specifies the parameter values for a given statement and a given iteration of a block. If instead #PARAM directives are used, multiple values and even parameter ranges are specified in advance for each parameter of each statement, and on each iteration of the block a random value is chosen from the specified sets for each parameter. #PARAM directives and a parameters file cannot be mixed.
Parameter Value Format:
-36.6 'DB2' X'0AB2' G'...' NULL 12 'batch' x'32ef' N'...' null +1.345E-6 'db2 batch' X'afD4' g'...' Null
Each parameter is defined like a SQL constant, and is separated from other parameters by whitespace. Non-delimited text represents a number, plain delimited (') text represents a single byte character string, x/X prefixed delimited (') text represents a binary string encoded as pairs of hex digits, g/G/n/N prefixed delimited (') text represents a graphic string composed of double byte characters, and NULL (case insensitive) represents a null value.
Parameter Input File Format:
Line X lists the set of parameters to supply to the Xth SQL statement that is executed in the input file. If blocks of statements are not repeated, then this corresponds to the Xth SQL statement that is listed in the input file. A blank line represents no parameters for the corresponding SQL statement. The number of parameters and their types must agree with the number of parameters and the types expected by the SQL statement.
Parameter Directive Format:
--#PARAM [single | start:end | start:step:end] [...]
Each parameter directive specifies a set of parameter values from which one random value is selected for each execution of the query. Sets are composed of both single parameter values and parameter value ranges. Parameter value ranges are specified by placing a colon (':') between two valid parameter values, with whitespace being an optional separator. A third parameter value can be placed between the start and end values to be used as a step size which overrides the default. Each parameter range is the equivalent of specifying the single values of 'start, start+step, start+2*step, ... start+n*step' where n is chosen such that 'start+n*step <= end' but 'start+(n+1)*step > end'. While parameter directives can be used to specify sets of values for any type of parameter (even NULL), ranges are only supported on numerical parameter values (integers and decimal numbers).
General Notes:
1. All SQL statements MUST be terminated by a delimiter (default ';') set by the --#SET DELIMITER command. This delimiter can be 1 or 2 characters. 2. SQL statement length is limited only by available memory and the interface used. Statements can break over multiple lines, but multiple statements are not allowed on a single line. 3. Input file line length is limited only be available memory. 4. db2batch issues its own connect and connect reset. 5. PAUSE & SLEEP are timed when in long timing mode. 6. Explain tables must be created before explain options can be used. 7. All command line options and SQL file statements are case insensitive with respects to db2batch. |
示例
| db2batch -d odb -f /home/db2inst1/test.sql -r /home/db2inst1/testResult -o r 0
db2batch -d odb -f /home/db2inst1/test.sql -r /home/db2inst1/testResult -o r 0 p 4 e yes |
注意
当在db2batch中开启explain时，需要提前创建db2inst1.explain表，而这个表结构DB2已经自带了：
首先找到这个表结构文件：find / -name EXPLAIN.DDL
其次，找到之后执行该文件：db2 -tvf /opt/IBM/db2/V9.7/misc/EXPLAIN.DDL
参考资料
https://www.ibm.com/docs/zh/db2/9.7?topic=commands-db2batch-benchmark-tool
db2expln
说明
|
[db2inst1@DB1:/home/db2inst1]#db2expln -help
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool
SQL and XQUERY Explain Tool describes the access plan selection for static SQL statements in the packages stored in the DB2 Universal Database system catalogs. Given a database name, package name, package creator, and section number, SQL and XQUERY Explain Tool will interpret and describe the information in these catalogs.
The syntax is:
.-----------. V | >>--db2expln----<option>--+--><
where <option> and <parameter> are taken from the list below. Each <option> may appear only once, and they may be specified in any order.
Connection Options: -database <db> = Connect to the database named <db>. -d <db>
-user <name> <pw> = Connect as user <name> with password <pw>. -u <name> <pw>
A database name must be specified.
Output Options: -terminal = Send output to the terminal. -t
-output <file> = Write output to the file named <file>. -o <file>
Either terminal or file output must be specified.
Help Options: -help = Display this help text. -h -?
Package Options: -schema <pattern> = The package creator must match <pattern>. -c <pattern>
-package <pattern> = The package name must match <pattern>. -p <pattern>
-version <pattern> = The package version must match <pattern>. If not specified, then the package with the version '' (the empty string) will be explained.
-section <number> = The section number is <number>. Use 0 (zero) for -s <number> all sections in the package.
-escape <charater> = Use <character> as the escape character when -e <character> matching patterns.
-noupper = Do not upper case creator, package and version -l before matching.
The creator and package information must be specified unless dynamic SQL is being explained. If the section information is not specified, then all sections will be displayed.
The <pattern> for creator, package, and version is in LIKE predicate form, which allows the percent sign (%%) and underscore (_) as pattern matching characters. This allows multiple packages to be explained with one invocation of db2expln. The escape character can be used to force the %% and _ characters to be treated literally. (See the SQL Reference for more information on the LIKE predicate.) If multiple packages may be matched, the section number is automatically set to 0 (all sections).
Dynamic Statement Options: -statement <statement> = The dynamic statement <statement> will be -q <sql> explained.
-stmtfile <file> = The dynamic statements contained in the file -f <file> <file> will be explained. <File> must exist at the client.
-cache <anchID>,<stmtUID>,<envID>,<varID> = Retrieve the statement identified by the given IDs from the dynamic SQL cache. (The IDs can be obtained by running db2pd with the -dynamic option.
-noenv = By default, each statement that affects the compile environment will be invoked after it has been explained. This option prevents the execution of these statements.
Explain Options: -setup <file> = The SQL statements in <file> will be invoked before any sections or statements are explained. The SQL statements in <file> will not be explained. Errors in the setup script are reported but ignored.
-terminator <character> = Each SQL statement for -setup, -statement, and -z <character> -stmtfile ends at <character>. If this option is not specified, then each statement is assumed to be one line long
-graph = Reconstruct the original optimizer plan graph (as -g presented by Visual Explain). Note that the reconstructed graph may not exactly match the original plan.
-opids = Show the operator ID numbers. -i
-nostats = Do not show compiler statistics in the graph.
Event Monitor Options: -actevm <name> = The name of the activities event monitor whose activitystmt logical grouping contains the section environments to be explained. -actevm must be specified if -appid, -uowid, -actid or -actid2 are to be specified. -appid <ID name> = The application identifier uniquely identifying the application that issued the activities whose section environments are to be explained. -uowid <number> = The unit of work ID whose section environments are to be explained. The unit of work ID is unique only within a given application. -actid <number> = The activity ID whose section environments are to be explained. The activity ID is only unique within a given unit of work. -actid2 <number> = The activity secondary ID whose section environments are to be explained. This defaults to zero if not specified.
The specific options available may vary by database server. Use "db2expln -help -database <db>" to get the options available for a specific server.
(3/-) [db2inst1@PICPDB1:/home/db2inst1]# |
示例
| db2expln -d odb -q "select * from orginfo" -o /home/db2inst1/db2expln
db2expln -d picpodb -q "select * from orginfo" -g -t
db2expln -d odb -q "select * from orginfo" -g -o /home/db2inst1/db2expln
|
导出表数据
| db2 "export to /home/db2inst1/表名20210413.del of del select 列名 from 表名" |
导出表结构
| db2look -d odb -e -t 表名 -o /home/db2inst1/表名.sql |
GET SNAPSHOT
说明
| [db2inst1@PICPDB1:/home/db2inst1]#db2 ? GET SNAPSHOT GET SNAPSHOT FOR { DATABASE MANAGER | ALL [DCS] DATABASES | ALL [DCS] APPLICATIONS | ALL BUFFERPOOLS | [DCS] APPLICATION {APPLID appl-id | AGENTID appl-handle} | FCM FOR ALL DBPARTITIONNUMS | LOCKS FOR APPLICATION {APPLID appl-id | AGENTID appl-handle } | { ALL | [DCS] DATABASE | [DCS] APPLICATIONS | TABLES | TABLESPACES | LOCKS | BUFFERPOOLS | DYNAMIC SQL [write to file] } ON database-alias } [AT DBPARTITIONNUM db-partition-number | GLOBAL]
NOTE: From the operating system prompt, prefix commands with 'db2'. Special characters MAY require an escape sequence (\), for example: db2 \? change database db2 ? change database xxx comment with \"text\" |
REORGCHK
说明
| [db2inst1@PICPDB1:/home/db2inst1]#db2 ? REORGCHK REORGCHK [{UPDATE | CURRENT} STATISTICS] [ON {TABLE {USER | SYSTEM | ALL | schema-name.table-name} | SCHEMA schema-name}]
NOTE: From the operating system prompt, prefix commands with 'db2'. Special characters MAY require an escape sequence (\), for example: db2 \? change database db2 ? change database xxx comment with \"text\" [db2inst1@PICPDB1:/home/db2inst1]# |
举例
db2 reorgchk CURRENT STATISTICS on table XXX.XXX
db2 reorgchk update STATISTICS on table XXX.XXX
rebind
说明
| [db2inst1@PICPDB1:/home/db2inst1]#db2 ? rebind REBIND [PACKAGE] package-name [VERSION version-id] [APREUSE {YES | NO}] [RESOLVE {ANY | CONSERVATIVE}] [REOPT {NONE | ONCE | ALWAYS}] [FUNCPATH schema-name [ {,schema-name} ... ]]
NOTE: From the operating system prompt, prefix commands with 'db2'. Special characters MAY require an escape sequence (\), for example: db2 \? change database db2 ? change database xxx comment with \"text\" |
举例
select * from syscat.packages where valid = 'X';
db2 rebind package P6550240
reorg
说明
| [db2inst1@PICPDB1:/home/db2inst1]#db2 ? reorg REORG { TABLE table-name Table-Clause | { INDEXES ALL FOR TABLE table-name | INDEX index-name [FOR TABLE table-name] } Index-Clause | TABLE mdc-table-name RECLAIM EXTENTS ONLY Reclaim-Extents-Clause} [Table-Partition-Clause] [On-DbPartitionNum-Clause]
Table-Clause: [INDEX index-name] [[ALLOW {READ | NO} ACCESS] [USE tablespace-name] [INDEXSCAN] [LONGLOBDATA [USE long-tablespace-name]] [KEEPDICTIONARY | RESETDICTIONARY]] | [INPLACE [ [ALLOW {WRITE | READ} ACCESS] [NOTRUNCATE TABLE] [START | RESUME] | {STOP | PAUSE} ]]
Index-Clause: [ALLOW {READ | NO | WRITE} ACCESS] [{CLEANUP ONLY [ALL | PAGES] | CONVERT}]
Reclaim-Extents-Clause: [ALLOW {WRITE | READ | NO} ACCESS]
Table-Partition-Clause: ON DATA PARTITION partition-name
On-DbPartitionNum-Clause: ON {{DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number [TO db-partition-number] , ... ) | ALL DBPARTITIONNUMS [EXCEPT {DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number [TO db-partition-number] , ...)]}
NOTE: From the operating system prompt, prefix commands with 'db2'. Special characters MAY require an escape sequence (\), for example: db2 \? change database db2 ? change database xxx comment with \"text\" |
举例
db2 reorg indexes all for table xxxx.xxxx
AIX-查看磁盘空间
df -g
AIX-查看目录下各个文件夹大小
du -g
AIX-查看特定文件大小
ls -l 文件名
文件大小单位是B，字节
3996
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
