Copy PPT - Aetna - db2commerce.com
Download
Report
Transcript Copy PPT - Aetna - db2commerce.com
Revive your Basic Commands
Out With the old, in With the New.
03.22.13
http://db2commerce.com
CONFIDENTIAL; © 2012 ROSETTA. All rights reserved.
List Applications – the Old
$ db2 list applications
Auth Id
-------WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
Application
Name
-------------db2jcc_applica
db2jcc_applica
db2jcc_applica
db2jcc_applica
db2jcc_applica
db2jcc_applica
db2jcc_applica
db2jcc_applica
Appl.
Handle
---------62651
62650
62735
62886
62912
62655
62653
62652
Application Id
DB
# of
Name
Agents
-------------------------------- -------- ----10.12.2.4.38674.130317084750
WC039D01 1
10.12.2.4.38600.130317084530
WC039D01 1
10.12.2.4.39095.130317120000
WC039D01 1
10.12.2.4.39861.130317180000
WC039D01 1
10.12.2.4.39989.130317190000
WC039D01 1
10.12.2.4.38679.130317084754
WC039D01 1
10.12.2.4.38676.130317084752
WC039D01 1
10.12.2.4.38675.130317084751
WC039D01 1
2
Using SQL to List Applications
select
substr(A.AUTHID,1,10) as AUTH_ID,
substr(A.APPL_NAME,1,16) as app_name,
integer(A.AGENT_ID) as APP_HANDLE,
substr(A.APPL_ID,1,25) as APPLICATION_ID,
substr(A.APPL_STATUS,1,10) as status,
second(current timestamp-A.STATUS_CHANGE_TIME) as STATUS_SEC,
substr(A.CLIENT_NNAME,1,15) as CLIENT_NNAME
from SYSIBMADM.APPLICATIONS A with ur;
AUTH_ID
---------WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
DB2INST1
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
WSCOMUSR
APP_NAME
APP_HANDLE
---------------- ----------db2jcc_applicati
62651
db2taskd
62644
db2jcc_applicati
62650
db2stmm
62643
db2jcc_applicati
62735
db2jcc_applicati
62886
db2jcc_applicati
62912
db2jcc_applicati
62655
db2bp
62930
db2fw0
62647
db2jcc_applicati
62653
db2lused
62646
db2jcc_applicati
62652
db2wlmd
62645
APPLICATION_ID
------------------------10.12.2.4.38674.130317084
*LOCAL.DB2.130317084524
10.12.2.4.38600.130317084
*LOCAL.DB2.130317084523
10.12.2.4.39095.130317120
10.12.2.4.39861.130317180
10.12.2.4.39989.130317190
10.12.2.4.38679.130317084
*LOCAL.db2inst1.130317193
*LOCAL.DB2.130317084527
10.12.2.4.38676.130317084
*LOCAL.DB2.130317084526
10.12.2.4.38675.130317084
*LOCAL.DB2.130317084525
STATUS
STATUS_SEC CLIENT_NNAME
---------- ----------- --------------UOWWAIT
30 srvr-dev-app01
UOWWAIT
54 srvr-dev-app01
UOWWAIT
30 srvr-dev-app01
UOWWAIT
54 srvr-dev-app01
UOWWAIT
30 srvr-dev-app01
UOWWAIT
30 srvr-dev-app01
UOWWAIT
30 srvr-dev-app01
UOWWAIT
30 srvr-dev-app01
UOWEXEC
0 srvr-dev-db01
UOWWAIT
6 srvr-dev-app01
UOWWAIT
37 srvr-dev-app01
UOWWAIT
52 srvr-dev-app01
UOWWAIT
28 srvr-dev-app01
CONNECTED
6 srvr-dev-app01
14 record(s) selected.
3
Using SQL to Analyze Applications
select
substr(client_nname,1,25) as client_nname,
count(*) as count
from SYSIBMADM.APPLICATIONS
where APPL_ID not like '*LOCAL%'
group by client_nname with ur;
CLIENT_NNAME
COUNT
------------------------- ----------435796-svp00comm01r.
2
435707-svp00comm02r.
3
435717-svp00comm03r.
2
435727-svp00comm04r.
2
435737-svp00comm05r.
3
435747-svp00comm06r.
1
6 record(s) selected.
LOGO
4
List Applications Considerations
List Applications/List
Applications Show
Detail/Application Snapshot
SYSIBMADM.
APPLICATIONS
No database
connection required
Requires database
connection
All Databases for an
instance
Only information on
one database
Multiple things to get
the same information
Must use SQL to
access
5
List Tablespaces – Old Way
]$ db2 list tablespaces
Tablespaces for Current Database
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
Normal
=
=
=
=
=
0
SYSCATSPACE
Database managed space
All permanent data. Regular table space.
0x0000
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
Normal
=
=
=
=
=
1
TEMPSPACE1
System managed space
System Temporary data
0x0000
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
Normal
=
=
=
=
=
2
USERSPACE1
Database managed space
All permanent data. Large table space.
0x0000
6
List Tablespaces Show Detail – Old Way
$ db2 list tablespaces show detail
Tablespaces for Current Database
Tablespace ID
Name
Type
Contents
space.
State
Detailed explanation:
Normal
Total pages
Useable pages
Used pages
Free pages
High water mark (pages)
Page size (bytes)
Extent size (pages)
Prefetch size (pages)
Number of containers
=
=
=
=
0
SYSCATSPACE
Database managed space
All permanent data. Regular table
= 0x0000
=
=
=
=
=
=
=
=
=
98304
98300
81484
16816
93028
4096
4
24
1
7
Using SQL to List Tablespaces
select
substr(TBSP_NAME,1,12) as Name,
SMALLINT(TBSP_ID) as TBSP_ID,
INTEGER(TBSP_PAGE_SIZE) as PAGE_SIZE,
TBSP_TYPE as TYPE,
TBSP_CONTENT_TYPE as CONTENT_TYPE,
substr(TBSP_STATE,1,8) as STATE,
decimal(float(TBSP_USED_PAGES*TBSP_PAGE_SIZE)/1024/1024,10,2) as SIZE_MB,
(select count(1) from table(mon_get_container('',-2))as C where C.TBSP_ID = T.TBSP_ID) as num_conts,
case when TBSP_TYPE = 'DMS'
THEN DECIMAL(FLOAT(TBSP_USED_PAGES)/FLOAT(TBSP_USABLE_PAGES)*100,5,2)
ELSE -1
END as PCT_USED
from table(mon_get_tablespace('',-2)) as T with ur
NAME
TBSP_ID PAGE_SIZE
TYPE
------------ ------- ----------- ---------SYSCATSPACE
0
4096 DMS
TEMPSPACE1
1
4096 SMS
USERSPACE1
2
4096 DMS
TAB8K
3
8192 DMS
TAB16K
4
16384 DMS
TEMPSYS8K
5
8192 SMS
TEMPSYS16K
6
16384 SMS
TEMPSYS32K
7
32768 SMS
USERTEMP32K
8
32768 SMS
DBA32K
9
32768 DMS
SYSTOOLSPACE
10
4096 DMS
CONTENT_TYPE
-----------ANY
SYSTEMP
LARGE
ANY
ANY
SYSTEMP
SYSTEMP
SYSTEMP
USRTEMP
LARGE
LARGE
STATE
SIZE_MB
NUM_CONTS
PCT_USED
-------- ------------ ----------- --------------NORMAL
318.29
1
82.89
NORMAL
0.00
1
-1.00
NORMAL
25108.25
1
79.98
NORMAL
238.25
1
9.54
NORMAL
930.00
1
90.86
NORMAL
0.00
1
-1.00
NORMAL
0.01
1
-1.00
NORMAL
0.03
1
-1.00
NORMAL
2.12
1
-1.00
NORMAL
51.00
1
2.07
NORMAL
0.07
1
0.24
11 record(s) selected.
8
Finding What an Application is Currently
Executing
There were two approaches the “old” way.
1.
take an application snapshot and look at the “last executed” SQL
Notice “last” and not “current”
2. Run a statement event monitor
Lot of information
Potential performance impact
9
10
Vote for Ember!
Blog: http://db2commerce.com
Twitter: @ember_crooks
LinkedIn: http://www.linkedin.com/pub/ember-crooks/8/a9b/25a/
E-mail: [email protected]
Useful links on this topic:
DB2 Info Center entry on ALTER TABLE:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.lu
w.sql.ref.doc/doc/r0000888.html
DB2 Info Center entry on ADMIN_MOVE_TABLE:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.lu
w.sql.rtn.doc/doc/r0055069.html
11