DB2(常用工具)具体实用

题目：1、熟练使用db2look工具导出数据库结构2、使用db2pd监控表空间、锁的使用情况3、使用db2mtrk 检查数据库内存的分配情况4、练习使用db2top工具5、使用db2batch测试SQL语句的性能解答：1、熟练使用db2look工具导出数据库结构[myinst@ye ~]$ db2look -d mydb3 -l -e -o mydb3.dll-- No userid was specified, db2look tries to use Environment variable USER-- USER is: MYINST-- Creating DDL for table(s)-- Output is sent to file: mydb3.dll-- Binding package automatically ...-- Bind is successful-- Binding package automatically ...-- Bind is successful2、使用db2pd监控表空间、锁的使用情况#db2pd监控表空间[myinst@ye ~]$ db2pd -db mydb3 -tablespaceDatabase Member 0 -- Database MYDB3 -- Active -- Up 0 days 00:29:31 -- Date2015-08-24-11.36.10.344000Tablespace Configuration:Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name0x00007F9AC71E0080 0 DMS Regular 32768 4 Yes 4 1 1 Off 1 0 3 Yes SYSCATSPACE0x00007F9AC71ED220 1 SMS SysTmp 32768 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE10x00007F9AC71FA3C0 2 DMS Large 32768 32 Yes 32 1 1 Off 1 0 31 Yes USERSPACE10x00007F9AC7207560 3 DMS Large 32768 4 Yes 4 1 1 Off 1 0 3 Yes SYSTOOLSPACE0x00007F9AC7214700 4 DMS Large 32768 32 Yes 32 2 2 Off 1 0 31 Yes MYSPACE3Tablespace Statistics:Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWMMax HWM State MinRecTime NQuiescers PathsDropped TrackmodState0x00007F9AC71E0080 0 7168 7164 6880 0 284 68806880 0x00000000 0 0 No n/a0x00007F9AC71ED220 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a0x00007F9AC71FA3C0 2 1024 992 96 0 896 9696 0x00000000 0 0 No n/a0x00007F9AC7207560 3 1024 1020 80 0 940 8080 0x00000000 0 0 No n/a0x00007F9AC7214700 4 1024 992 352 0 640 352352 0x00000000 1439886641 0 No n/aTablespace Autoresize Statistics:Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF0x00007F9AC71E0080 0 Yes Yes 33554432 -1 No None None No0x00007F9AC71ED220 1 Yes No 0 0 No 0 None No0x00007F9AC71FA3C0 2 Yes Yes 33554432 -1 No None None No0x00007F9AC7207560 3 Yes Yes 33554432 -1 No None None No0x00007F9AC7214700 4 Yes Yes 33554432 -1 No None None NoTablespace Storage Statistics:Address Id DataTag Rebalance SGID SourceSGID0x00007F9AC71E0080 0 0 No 0 -0x00007F9AC71ED220 1 0 No 0 -0x00007F9AC71FA3C0 2 -1 No 0 -0x00007F9AC7207560 3 -1 No 0 -0x00007F9AC7214700 4 -1 No 0 -Containers:Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSetContainer0x00007F9AC1270580 0 0 File 7168 7164 0 0/www/db2/db2test/myinst/NODE0000/MYDB3/T0000000/C0000000.CAT0x00007F9AC1270C60 1 0 Path 1 1 0 0/www/db2/db2test/myinst/NODE0000/MYDB3/T0000001/C0000000.TMP0x00007F9AC1268A20 2 0 File 1024 992 0 0/www/db2/db2test/myinst/NODE0000/MYDB3/T0000002/C0000000.LRG0x00007F9AC1269A00 3 0 File 1024 1020 0 0/www/db2/db2test/myinst/NODE0000/MYDB3/T0000003/C0000000.LRG0x00007F9AC126AA00 4 0 File 1024 992 0 0/www/db2/db2test/myinst/NODE0000/MYDB3/T0000004/C0000000.LRG#db2pd监控锁的使用情况[myinst@ye ~]$ db2pd -db mydb3 -lockDatabase Member 0 -- Database MYDB3 -- Active -- Up 0 days 00:23:26 -- Date2015-08-24-11.30.05.926325Locks:Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID3、使用db2mtrk 检查数据库内存的分配情况#显示数据库的内存分配情况的详细信息[myinst@ye ~]$ db2mtrk -d -vTracking Memory on: 2015/08/24 at 12:12:36Memory for database: MYDB3Backup/Restore/Util Heap is of size 262144 bytesPackage Cache is of size 2293760 bytesOther Memory is of size 196608 bytesCatalog Cache Heap is of size 720896 bytesBuffer Pool Heap (2) is of size 34078720 bytesBuffer Pool Heap (1) is of size 75300864 bytesBuffer Pool Heap (System 32k buffer pool) is of size 1835008 bytesBuffer Pool Heap (System 16k buffer pool) is of size 1572864 bytesBuffer Pool Heap (System 8k buffer pool) is of size 1441792 bytesBuffer Pool Heap (System 4k buffer pool) is of size 1376256 bytesShared Sort Heap is of size 1310720 bytesLock Manager Heap is of size 53936128 bytesDatabase Heap is of size 107085824 bytesApplication Heap (15) is of size 65536 bytesApplication Heap (14) is of size 65536 bytesApplication Heap (13) is of size 65536 bytesApplication Heap (12) is of size 196608 bytesApplication Heap (11) is of size 65536 bytesApplication Heap (10) is of size 65536 bytesApplication Heap (9) is of size 131072 bytesApplication Heap (8) is of size 131072 bytesApplications Shared Heap is of size 917504 bytesTotal: 283115520 bytes#显示数据库的内存使用情况[myinst@ye ~]$ db2mtrk -dTracking Memory on: 2015/08/24 at 12:14:16Memory for database: MYDB3utilh pckcacheh other catcacheh bph (2) bph (1) 256.0K 2.2M 192.0K 704.0K 32.5M 71.8Mbph (S32K) bph (S16K) bph (S8K) bph (S4K) shsorth lockh 1.8M 1.5M 1.4M 1.3M 1.2M 51.4Mdbh apph (15) apph (14) apph (13) apph (12) apph (11) 102.1M 64.0K 64.0K 64.0K 192.0K 64.0Kapph (10) apph (9) apph (8) appshrh64.0K 128.0K 128.0K 896.0K#显示当前实例的内存使用情况[myinst@ye ~]$ db2mtrk -iTracking Memory on: 2015/08/24 at 12:15:10Memory for instanceother fcmbp monh51.2M 832.0K 512.0K#显示当前实例的内存使用的详细信息[myinst@ye ~]$ db2mtrk -i -vTracking Memory on: 2015/08/24 at 12:15:18Memory for instanceOther Memory is of size 53739520 bytesFCMBP Heap is of size 851968 bytesDatabase Monitor Heap is of size 524288 bytesTotal: 55115776 bytes4、练习使用db2top工具[myinst@ye ~]$ db2top -d mydb3按快捷键“d”，转至数据库屏幕按快捷键“D”，转至动态SQL 屏幕按快捷键“u”,显示活动的实用程序，并且跨数据库分区将它们聚集起来按快捷键“l”,转至会话屏幕按快捷键“t”,转至表空间屏幕5、使用db2batch测试SQL语句的性能[myinst@ye ~]$ cat sample.sqlselect * from mydb3.emp;[myinst@ye ~]$ db2batch -d mydb3 -f sample.sql* Timestamp: Mon Aug 24 2015 13:53:24 CST---------------------------------------------* SQL Statement Number 1:select * from mydb3.emp;** CLI error in preparing the SQL statement:(-204): [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "MYDB3.EMP" is an undefined name. SQLSTATE=42704* Elapsed Time is: 0.000000 seconds* Summary Table:Type Number Repetitions Total Time (s) Min Time (s) Max Time (s) Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------Statement 1 1 0.000000 0.000000 0.000000 0.000000 0.000000 0 0* Total Entries: 1* Total Time: 0.000000 seconds* Minimum Time: 0.000000 seconds* Maximum Time: 0.000000 seconds* Arithmetic Mean Time: 0.000000 seconds* Geometric Mean Time: 0.000000 seconds---------------------------------------------* Timestamp: Mon Aug 24 2015 13:53:24 CST#获得更多信息[myinst@ye ~]$ db2batch -d mydb3 -f sample.sql -o r 0 p 2* Timestamp: Mon Aug 24 2015 13:54:30 CST---------------------------------------------* SQL Statement Number 1:select * from mydb3.emp;** CLI error in preparing the SQL statement:(-204): [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "MYDB3.EMP" is an undefined name. SQLSTATE=42704* Elapsed Time is: 0.000000 secondsMonitoring InformationInstance name = myinstNode name =Node type = Enterprise Server Edition with local and remote clientsSnapshot timestamp = 08/24/2015 13:54:30.306349Application SnapshotApplication handle = 229Application status = UOW WaitingStatus change time = 08/24/2015 13:54:30.301560Application code page = 1208Application country/region code = 1DUOW correlation token = *LOCAL.myinst.150824055430 Application name = db2batchApplication ID = *LOCAL.myinst.150824055430Sequence number = 00002TP Monitor client user ID =TP Monitor client workstation name = TP Monitor client application name = DB2BATCHTP Monitor client accounting string =CONNECT Authorization ID = MYINSTClient login ID = myinstConfiguration NNAME of client = Client database manager product ID = SQL10055Process ID of client application = 7007Platform of client application = LINUXAMD64Communication protocol of client = Local ClientDatabase name = MYDB3Database path = /www/db2/db2test/myinst/NODE0000/SQL00001/MEMBER0000/Client database alias = MYDB3Input database alias =The highest authority level granted =Direct DBADM authorityDirect SECADM authorityIndirect SYSADM authorityIndirect CREATETAB authorityIndirect BINDADD authorityIndirect CONNECT authorityIndirect IMPLICIT_SCHEMA authorityCoordinator member number = 0Coordinator agent process or thread ID = 56Agents associated with the application = 1Connection request start timestamp = 08/24/2015 13:54:30.292632Connect request completion timestamp = 08/24/2015 13:54:30.293070Application idle time = 0Inbound communication address = *LOCAL.myinstLast reset timestamp = 08/24/2015 13:54:30.300279Agents stolen = 0Agents waiting on locks = 0Maximum associated agents = 1Priority at which application agents work = 0Priority type = DynamicLocks held by application = 0Lock waits since connect = 0Time application waited on locks (ms) = 0 Deadlocks detected = 0 Lock escalations = 0 Exclusive lock escalations = 0 Number of Lock Timeouts since connected = 0 Total time UOW waited on locks (ms) = 0Total sorts = 0 Total sort time (ms) = 0 Total sort overflows = 0Buffer pool data logical reads = 0 Buffer pool data physical reads = 0 Buffer pool temporary data logical reads = 0 Buffer pool temporary data physical reads = 0 Buffer pool data writes = 0 Buffer pool index logical reads = 1 Buffer pool index physical reads = 0 Buffer pool temporary index logical reads = 0 Buffer pool temporary index physical reads = 0 Buffer pool index writes = 0 Buffer pool xda logical reads = 0 Buffer pool xda physical reads = 0 Buffer pool temporary xda logical reads = 0 Buffer pool temporary xda physical reads = 0 Buffer pool xda writes = 0 Total buffer pool read time (milliseconds) = 0Total buffer pool write time (milliseconds)= 0Time waited for prefetch (ms) = 0 Unread prefetch pages = 0 Direct reads = 0 Direct writes = 0 Direct read requests = 0 Direct write requests = 0 Direct reads elapsed time (ms) = 0 Direct write elapsed time (ms) = 0Number of SQL requests since last commit = 0 Commit statements = 1 Rollback statements = 0 Dynamic SQL statements attempted = 0 Static SQL statements attempted = 1 Failed statement operations = 0 Select SQL statements executed = 0Xquery statements executed = 0Update/Insert/Delete statements executed = 0DDL statements executed = 0Internal automatic rebinds = 0Internal rows deleted = 0Internal rows inserted = 0Internal rows updated = 0Internal commits = 0Internal rollbacks = 0Internal rollbacks due to deadlock = 0Binds/precompiles attempted = 0Rows deleted = 0Rows inserted = 0Rows updated = 0Rows selected = 0Rows read = 0Rows written = 0UOW log space used (Bytes) = 0Previous UOW completion timestamp = 08/24/2015 13:54:30.293070 Elapsed time of last completed uow (sec.ms)= 0.005268UOW start timestamp = 08/24/2015 13:54:30.296285 UOW stop timestamp = 08/24/2015 13:54:30.301553 UOW completion status = Committed - Commit StatementOpen remote cursors = 0Open remote cursors with blocking = 0Rejected Block Remote Cursor requests = 0Accepted Block Remote Cursor requests = 1Open local cursors = 0Open local cursors with blocking = 0Total User CPU Time used by agent (s) = 0.001037Total System CPU Time used by agent (s) = 0.000000Host execution elapsed time = 0.000845Package cache lookups = 1Package cache inserts = 0Application section lookups = 1Application section inserts = 0Catalog cache lookups = 1Catalog cache inserts = 0Catalog cache overflows = 0Catalog cache heap full = 0Catalog cache high water mark = 0Number of hash joins = 0Number of hash loops = 0Number of hash join overflows = 0Number of small hash join overflows = 0Statement type = Static SQL Statement Statement = Static CommitSection number = 0Application creator =Package name =Consistency Token =Cursor name =Statement member number = 0Statement start timestamp = 08/24/2015 13:54:30.301498 Statement stop timestamp = 08/24/2015 13:54:30.301553 Elapsed time of last completed stmt(sec.ms)= 0.000055Total Statement user CPU time = 0.000052Total Statement system CPU time = 0.000000SQL compiler cost estimate in timerons = 0SQL compiler cardinality estimate = 0Degree of parallelism requested = 1Number of agents working on statement = 1Number of subagents created for statement = 1Statement sorts = 0Total sort time = 0Sort overflows = 0Rows read = 0Rows written = 0Internal rows deleted = 0Internal rows updated = 0Internal rows inserted = 0Rows fetched = 0Buffer pool data logical reads = 0Buffer pool data physical reads = 0Buffer pool temporary data logical reads = 0Buffer pool temporary data physical reads = 0Buffer pool index logical reads = 0Buffer pool index physical reads = 0Buffer pool temporary index physical reads = 0Buffer pool xda logical reads = 0Buffer pool xda physical reads = 0Buffer pool temporary xda logical reads = 0Buffer pool temporary xda physical reads = 0Blocking cursor = NOAgent process/thread ID = 56Memory usage for application:Node number = 0Memory Pool Type = Other MemoryCurrent size (bytes) = 196608High water mark (bytes) = 327680Configured size (bytes) = 1868365824* Summary Table:Type Number Repetitions Total Time (s) Min Time (s) Max Time (s) Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------Statement 1 1 0.000000 0.000000 0.000000 0.000000 0.000000 0 0* Total Entries: 1* Total Time: 0.000000 seconds* Minimum Time: 0.000000 seconds* Maximum Time: 0.000000 seconds* Arithmetic Mean Time: 0.000000 seconds* Geometric Mean Time: 0.000000 seconds---------------------------------------------* Timestamp: Mon Aug 24 2015 13:54:30 CST。