10 most popular posts for dba-db2.com for 2015

I've just published 10 most popular sqlserver-dba posts for 2015 , so I thought I'd publish 10 most popular posts for dba-db2.com for 2015 I've been meaning to publish the 10 most popular posts for dba-db2.com for 2015 since the start of 2016. But I've had a lot of work and being writing about other topics. Mainly I write dba-db2.com posts to document problems and to help me understand what I'm working on. One of the observations about this list is that I also revisit these posts regularly. I use the posts as reference and also edit based on feedback.... Read more →

DIA8400C A bad page was encountered

Question: The DIA8400C A bad page was encountered message appeared in the db2diag.log file MESSAGE : ZRC=0x86020001=-2046689279=SQLB_BADP "page is bad" DIA8400C A bad page was encountered. Object descriptor, PD_TYPE_SQLB_OBJECT_DESC, 104 bytes Obj: {pool:5;obj:5;type:0} Parent={5;5} How can I investigate further ? Answer: From the information provided , the first thing is to check the database object in question. In the example you’ve presented – It’s the TablespaceID = 5 and TableId = 5 . This query will display the database object db2 "select char(tabname,20), char(tabschema,20) from syscat.tables where tableid=5 and tbspaceid=5" Once you’ve identified the object – there are two utilities... Read more →

How to monitor table reorgs with db2pd -reorgs

The following command can be used to view the reorganization flags for the tables and the indices of the tables: Db2 connect to MYDB db2 reorgchk current statistics on table all This is a basic readout and reports on every table. In practise, I just need to view the recent table reorg stats. Fortunately the db2pd utility returns this information. The basic command is db2pd –reorgs index Read more about db2pd in the db2pd troubleshooting guide - DBA DB2 The command gives you detailed information about table and index reorganisation. Three panels of information appear: Table Reorg Information - Table... Read more →

Delete first 1000 rows in a DB2 table using FETCH FIRST

Question: How can I delete rows in batches ? I’m getting the SQL0964C The transaction log for the database is full error, and don’t have the authority to increase the LOGSECOND parameter? How can I use SQL code to DELETE in batches of 1000? Answer: The FETCH FIRST commands can limit the amount of rows returned. Combining FETCH FIRST with DELETE allows you to manage the batch commit process. Check this example. The SELECT combined with FETCH FIRST statement returns batches of 1000. The DELETE command covers the recordset generated by the SELECT statement DELETE FROM (SELECT * FROM DB1.MY_DB... Read more →

How to check reclaimable space enabled on DB2 tablespace with MON_GET_TABLESPACE

Question: How can I check if a DB2 database tablespace has reclaimable space enabled? A REORG completed and I need to reclaim the space Answer: You can find metrics about DB2 tablespaces using the MON_GET_TABLESPACE table function . Use the reclaimable_space_enabled column . It returns an smallint . If the value returned is 1 – reclaimable space is enabled. If 0 is returned , reclaimables space is not enabled. select substr(TBSP_NAME,1,14),reclaimable_space_enabled from table (MON_GET_TABLESPACE(NULL,-1)) WHERE TBSP_NAME NOT LIKE ('SYS%') Read More on DB2 reorg options Reorg table DB2 DB2 table reorg and table truncate DB2 Used Space and REORG Read more →