One of my colleagues have introduced me to this utility and we had our DBAs install it on the system, and voila – you can see DB2 in works: it’s running snapshots online, you can see bufferpools usage, your top processes and much much more. Here is a link to a IBM page that has a detailed description, screenshots:
http://www.ibm.com/developerworks/data/library/techarticle/dm-0812wang/
Feb 182009
Recently I had to use escape characters in DB2 queries and after looking through documentation here is a simple way to do this. You can define you escape character using
{escape '\'}
right after the statement your are trying to use it, here is a sample statement:
select oprid, oprdefndesc
from psoprdefn
where oprid like '%\_%' {escape '\'}
Mar 192008
Need to delete millions of rows from a table fast? Here is how you can do this:
1: ALTER TABLE table_name ACTIVATE NOT LOGGED INITIALLY; --disable logging
2: DELETE FROM table_name WHERE ....;
3: COMMIT; --Commits and enable logging
Mar 122008
Here are some very useful utility SQLs that will allow you to understand your space usage, system and other useful stuff:
DB2 Space Usage & Status
1: -- to find tablespace status, type , usage run following SQL
2: select substr(tablespace_name,1,20) Tablespace_name,
3: case t.TBSPACETYPE
4: when 'S' then 'System Managed'
5: when 'D' then 'Database managed'
6: end as TBSManaged,
7: (used_pages/total_pages ) * 100 usedPCT,
8: s.total_pages Total_pages,
9: s.used_pages Used_pages,
10: (s.total_pages - s.USABLE_PAGES ) Overhead_pgs,
11: s.NUM_CONTAINERS No_of_containers,
12: s.free_pages Free_pages,
13: case t.DATATYPE
14: when 'A' then ' ALL typ perm '
15: when 'L' then ' Large '
16: when 'T' then ' System Temp'
17: when 'U' then ' User Temp '
18: end as TBStype,
19: case s.TABLESPACE_STATE
20: when 0 then ' Normal '
21: when 1 then ' Quiesced: SHARE '
22: when 2 then ' Quiesced: UPDATE '
23: when 4 then ' Quiesced: EXCLUSIVE '
24: when 8 then ' Load pending '
25: when 16 then ' Delete pending '
26: when 32 then ' Backup pending '
27: when 64 then ' Roll forward in progress '
28: when 128 then ' Roll forward pending '
29: when 256 then ' Restore pending '
30: when 512 then ' Disable pending '
31: when 1024 then ' Reorg in progress '
32: when 2048 then ' Backup in progress '
33: when 4096 then ' Storage must be defined '
34: when 8192 then ' Restore in progress '
35: when 16384 then ' Offline and not accessible '
36: when 32768 then ' Drop pending '
37: when 33554432 then ' Storage may be defined '
38: when 67108864 then ' Storage Definition is in (final) state '
39: when 134217728 then ' Storage Definition was changed prior to rollforward '
40: when 268435456 then ' DMS rebalancer is active '
41: when 536870912 then ' TBS deletion in progress '
42: when 1073741824 then ' TBS creation in progress ' end TABLESPACE_STATE
43: from table(snapshot_tbs_cfg(' ',-1)) as s, sysibm.systablespaces t where s.tablespace_name = t.tbspace ;
DB2 Unused Space
1: -- GETTING WASTED SPACE
2: select TBSPACE, ((80 - usedPCT) * total_pages/100)*4/1000 SPACE_WASTED
3: FROM
4: ( select substr(tablespace_name,1,20) TBSPACE,
5: total_pages,
6: used_pages,
7: free_pages,
8: (used_pages*100/total_pages ) usedPCT
9: from table(snapshot_tbs_cfg(' ',-1)) as tbs )
10: AS TABLE_SPACE
11: WHERE usedPCT < 60
Mar 042008
Here are a few more nice DB2 scripts that would save you time:
Get all tables in Table Space
-- Get all tables in particular tablespaceselect SUBSTR(TABNAME,1,25) "TABNAME", SUBSTR(TABSCHEMA,1,15) "SCHEMA", NPAGES, CARD from syscat.tables where tbspace = '?'
Generate Grants for all tables in Table Space
-- Genereating GRANTS SELECT 'grant select on ' || tabname || ' to PSFDEV' || ';' FROM syscat.tables WHERE tabname NOT IN (SELECT tabname FROM syscat.tabauth WHERE grantee = 'PSFDEV') AND tabschema = 'PSFSSYS' AND (type = 'T' OR type = 'V')
Oracle PeopleTools Blog
Recent Comments