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 192008
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
Some of the processes that depend on shared table instances , can experience a dramatic differences in performance, in case all of the instances available are being used, or remaining locked by some orphan or failed process. One way to resolve this problem is to go online and check if tables should be “released”. Here is a set of SQLs that would allow you to do the same thing and depending on you requirements – allow to automate this process, which could prove critical during the times of high usage:
-- Shared Temp Table instance control tableSELECT * FROM PS_TEMP_TBL_ASGNMWHERE TEMP_TBL_NAME = 'table_name'WITH UR -- Get tables that were locked by processes that ran 200 instance numbers beforeSELECT * FROM PS_TEMP_TBL_ASGNMWHERE in_use_sw = 'Y' AND process_instance < (SELECT max (prcsinstance) - 200 FROM psfssys.psprcsrqst WHERE runstatus = '9')with ur -- RELEASE ALL TEMP TABLE LOCKS -- FROM PROCESS INSTANCES THAT ARE LESS THAN 200 THAN THE LAST SUCCESSFUL ONEUPDATE ps_temp_tbl_asgnm SET in_use_sw = 'N', PROCESS_INSTANCE = 0WHERE in_use_sw = 'Y' AND process_instance < (SELECT max (prcsinstance) - 200 FROM psfssys.psprcsrqst WHERE runstatus = '9')
Let me know if you have any questions
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