1 / 11

Revive your Basic Commands

Revive your Basic Commands. 03.22.13 http://db2commerce.com. Out With the old, in With the New. List Applications – the Old. $ db2 list applications Auth Id Application Appl. Application Id DB # of

cherryw
Download Presentation

Revive your Basic Commands

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Revive your Basic Commands 03.22.13 http://db2commerce.com Out With the old, in With the New.

  2. List Applications – the Old $ db2 list applications Auth Id Application Appl. Application Id DB # of Name Handle Name Agents -------- -------------- ---------- -------------------------------- -------- ----- WSCOMUSR db2jcc_applica 62651 10.12.2.4.38674.130317084750 WC039D01 1 WSCOMUSR db2jcc_applica 62650 10.12.2.4.38600.130317084530 WC039D01 1 WSCOMUSR db2jcc_applica 62735 10.12.2.4.39095.130317120000 WC039D01 1 WSCOMUSR db2jcc_applica 62886 10.12.2.4.39861.130317180000 WC039D01 1 WSCOMUSR db2jcc_applica 62912 10.12.2.4.39989.130317190000 WC039D01 1 WSCOMUSR db2jcc_applica 62655 10.12.2.4.38679.130317084754 WC039D01 1 WSCOMUSR db2jcc_applica 62653 10.12.2.4.38676.130317084752 WC039D01 1 WSCOMUSR db2jcc_applica 62652 10.12.2.4.38675.130317084751 WC039D01 1

  3. 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 APP_NAME APP_HANDLE APPLICATION_ID STATUS STATUS_SEC CLIENT_NNAME ---------- ---------------- ----------- ------------------------- ---------- ----------- --------------- WSCOMUSR db2jcc_applicati 62651 10.12.2.4.38674.130317084 UOWWAIT 30 srvr-dev-app01 WSCOMUSR db2taskd 62644 *LOCAL.DB2.130317084524 UOWWAIT 54 srvr-dev-app01 WSCOMUSR db2jcc_applicati 62650 10.12.2.4.38600.130317084 UOWWAIT 30 srvr-dev-app01 WSCOMUSR db2stmm 62643 *LOCAL.DB2.130317084523 UOWWAIT 54 srvr-dev-app01 WSCOMUSR db2jcc_applicati 62735 10.12.2.4.39095.130317120 UOWWAIT 30 srvr-dev-app01 WSCOMUSR db2jcc_applicati 62886 10.12.2.4.39861.130317180 UOWWAIT 30 srvr-dev-app01 WSCOMUSR db2jcc_applicati 62912 10.12.2.4.39989.130317190 UOWWAIT 30 srvr-dev-app01 WSCOMUSR db2jcc_applicati 62655 10.12.2.4.38679.130317084 UOWWAIT 30 srvr-dev-app01 DB2INST1 db2bp 62930 *LOCAL.db2inst1.130317193 UOWEXEC 0 srvr-dev-db01 WSCOMUSR db2fw0 62647 *LOCAL.DB2.130317084527 UOWWAIT 6 srvr-dev-app01 WSCOMUSR db2jcc_applicati 62653 10.12.2.4.38676.130317084 UOWWAIT 37 srvr-dev-app01 WSCOMUSR db2lused 62646 *LOCAL.DB2.130317084526 UOWWAIT 52 srvr-dev-app01 WSCOMUSR db2jcc_applicati 62652 10.12.2.4.38675.130317084 UOWWAIT 28 srvr-dev-app01 WSCOMUSR db2wlmd 62645 *LOCAL.DB2.130317084525 CONNECTED 6 srvr-dev-app01 14 record(s) selected.

  4. 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.

  5. List Applications Considerations

  6. List Tablespaces – Old Way ]$ db2 list tablespaces Tablespaces for Current Database Tablespace ID = 0 Name = SYSCATSPACE Type = Database managed space Contents = All permanent data. Regular table space. State = 0x0000 Detailed explanation: Normal Tablespace ID = 1 Name = TEMPSPACE1 Type = System managed space Contents = System Temporary data State = 0x0000 Detailed explanation: Normal Tablespace ID = 2 Name = USERSPACE1 Type = Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal

  7. List Tablespaces Show Detail – Old Way $ db2 list tablespaces show detail Tablespaces for Current Database Tablespace ID = 0 Name = SYSCATSPACE Type = Database managed space Contents = All permanent data. Regular table space. State = 0x0000 Detailed explanation: Normal Total pages = 98304 Useable pages = 98300 Used pages = 81484 Free pages = 16816 High water mark (pages) = 93028 Page size (bytes) = 4096 Extent size (pages) = 4 Prefetch size (pages) = 24 Number of containers = 1

  8. 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 CONTENT_TYPE STATE SIZE_MB NUM_CONTS PCT_USED ------------ ------- ----------- ---------- ------------ -------- ------------ ----------- --------------- SYSCATSPACE 0 4096 DMS ANY NORMAL 318.29 1 82.89 TEMPSPACE1 1 4096 SMS SYSTEMP NORMAL 0.00 1 -1.00 USERSPACE1 2 4096 DMS LARGE NORMAL 25108.25 1 79.98 TAB8K 3 8192 DMS ANY NORMAL 238.25 1 9.54 TAB16K 4 16384 DMS ANY NORMAL 930.00 1 90.86 TEMPSYS8K 5 8192 SMS SYSTEMP NORMAL 0.00 1 -1.00 TEMPSYS16K 6 16384 SMS SYSTEMP NORMAL 0.01 1 -1.00 TEMPSYS32K 7 32768 SMS SYSTEMP NORMAL 0.03 1 -1.00 USERTEMP32K 8 32768 SMS USRTEMP NORMAL 2.12 1 -1.00 DBA32K 9 32768 DMS LARGE NORMAL 51.00 1 2.07 SYSTOOLSPACE 10 4096 DMS LARGE NORMAL 0.07 1 0.24 11 record(s) selected.

  9. Finding What an Application is Currently Executing There were two approaches the “old” way. • take an application snapshot and look at the “last executed” SQL • Notice “last” and not “current” • Run a statement event monitor • Lot of information • Potential performance impact

  10. Vote for Ember! Blog: http://db2commerce.com Twitter: @ember_crooks LinkedIn: http://www.linkedin.com/pub/ember-crooks/8/a9b/25a/ E-mail: ember.crooks@gmail.com Useful links on this topic: DB2 Info Center entry on ALTER TABLE: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.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.luw.sql.rtn.doc/doc/r0055069.html

More Related