Revive your Basic Commands Out With the old
Revive your Basic Commands Out With the old, in With the New. 03. 22. 13 http: //db 2 commerce. com CONFIDENTIAL; © 2012 ROSETTA. All rights reserved.
List Applications – the Old $ db 2 list applications Auth Id -------WSCOMUSR WSCOMUSR Application Name -------db 2 jcc_applica db 2 jcc_applica Appl. Handle -----62651 62650 62735 62886 62912 62655 62653 62652 Application Id DB # of Name Agents ---------------- ----10. 12. 2. 4. 38674. 130317084750 WC 039 D 01 1 10. 12. 2. 4. 38600. 130317084530 WC 039 D 01 1 10. 12. 2. 4. 39095. 130317120000 WC 039 D 01 1 10. 12. 2. 4. 39861. 130317180000 WC 039 D 01 1 10. 12. 2. 4. 39989. 130317190000 WC 039 D 01 1 10. 12. 2. 4. 38679. 130317084754 WC 039 D 01 1 10. 12. 2. 4. 38676. 130317084752 WC 039 D 01 1 10. 12. 2. 4. 38675. 130317084751 WC 039 D 01 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 DB 2 INST 1 WSCOMUSR WSCOMUSR APP_NAME APP_HANDLE --------db 2 jcc_applicati 62651 db 2 taskd 62644 db 2 jcc_applicati 62650 db 2 stmm 62643 db 2 jcc_applicati 62735 db 2 jcc_applicati 62886 db 2 jcc_applicati 62912 db 2 jcc_applicati 62655 db 2 bp 62930 db 2 fw 0 62647 db 2 jcc_applicati 62653 db 2 lused 62646 db 2 jcc_applicati 62652 db 2 wlmd 62645 APPLICATION_ID ------------10. 12. 2. 4. 38674. 130317084 *LOCAL. DB 2. 130317084524 10. 12. 2. 4. 38600. 130317084 *LOCAL. DB 2. 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. db 2 inst 1. 130317193 *LOCAL. DB 2. 130317084527 10. 12. 2. 4. 38676. 130317084 *LOCAL. DB 2. 130317084526 10. 12. 2. 4. 38675. 130317084 *LOCAL. DB 2. 130317084525 STATUS_SEC CLIENT_NNAME -----------UOWWAIT 30 srvr-dev-app 01 UOWWAIT 54 srvr -dev-app 01 UOWWAIT 30 srvr-dev-app 01 UOWEXEC 0 srvr -dev-db 01 UOWWAIT 6 srvr -dev-app 01 UOWWAIT 37 srvr-dev-app 01 UOWWAIT 52 srvr -dev-app 01 UOWWAIT 28 srvr-dev-app 01 CONNECTED 6 srvr -dev-app 01 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 -svp 00 comm 01 r. 2 435707 -svp 00 comm 02 r. 3 435717 -svp 00 comm 03 r. 2 435727 -svp 00 comm 04 r. 2 435737 -svp 00 comm 05 r. 3 435747 -svp 00 comm 06 r. 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 ]$ db 2 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. 0 x 0000 Tablespace ID Name Type Contents State Detailed explanation: Normal = = = 1 TEMPSPACE 1 System managed space System Temporary data 0 x 0000 Tablespace ID Name Type Contents State Detailed explanation: Normal = = = 2 USERSPACE 1 Database managed space All permanent data. Large table space. 0 x 0000 6
List Tablespaces Show Detail – Old Way $ db 2 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 = 0 x 0000 = = = = = 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, 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 TEMPSPACE 1 1 4096 SMS USERSPACE 1 2 4096 DMS TAB 8 K 3 8192 DMS TAB 16 K 4 16384 DMS TEMPSYS 8 K 5 8192 SMS TEMPSYS 16 K 6 16384 SMS TEMPSYS 32 K 7 32768 SMS USERTEMP 32 K 8 32768 SMS DBA 32 K 9 32768 DMS SYSTOOLSPACE 10 4096 DMS CONTENT_TYPE ------ANY SYSTEMP LARGE ANY SYSTEMP USRTEMP 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: //db 2 commerce. com Twitter: @ember_crooks Linked. In: http: //www. linkedin. com/pub/ember-crooks/8/a 9 b/25 a/ E-mail: ember. crooks@gmail. com Useful links on this topic: DB 2 Info Center entry on ALTER TABLE: http: //publib. boulder. ibm. com/infocenter/db 2 luw/v 9 r 7/topic/com. ibm. db 2. lu w. sql. ref. doc/r 0000888. html DB 2 Info Center entry on ADMIN_MOVE_TABLE: http: //publib. boulder. ibm. com/infocenter/db 2 luw/v 9 r 7/topic/com. ibm. db 2. lu w. sql. rtn. doc/r 0055069. html 11
- Slides: 11