DB2 fix : ADM4500W A package cache overflow condition has occurred.

Question: During some troubleshooting , I noticed these messages in the db2diag.log file and instance.nfy files. “There is no error but this indicates that the package cache has exceeded the configured maximum size. If this condition persists, you should perform additional monitoring to determine if you need to change the PCKCACHESZ DB configuration parameter. You could also set it to AUTOMATIC.” I checked the database configurations via : db2 get db cfg | grep ‘PCKCACHESZ’ Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8) How can I get rid of these messages? Do they point to deeper problem? Answer: First thing to... Read more →

SQL0286N A table space could not be found with a page size of at least 32768

Question: I got this error on MYDB on MYSERVER: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0286N A table space could not be found with a page size of at least "32768" that authorization ID "DB2USER" is authorized to use. SQLSTATE=42727 Answer: You must be attempting to execute a CREATE TABLE statement where the requirement is for a pagesize greater that the tablespace pagesizes available. You could verify this by connecting to the database and running the LIST TABLESPACES SHOW DETAIL command. If... Read more →

ADM11001E DB2 did not create a memory segment for running fenced routines

Question:We are carrying out an application installation. The installation is failing with the following message in the .nfy file . ADM11001E DB2 did not create a memory segment for running fenced routines. This was specified by the use of DB2_FMP_COMM_HEAPSZ registry variable. How can I bypass these errors to allow the installation to finish? Answer: I’ve seen this problem plenty of times where a DBA has set the DB2 registry value : DB2_FMP_COMM_HEAPSZ=0. On many occasions , it is to force fenced routines such as the DB2 Health Monitor :Monitor health of instance and databases to not run. It is... 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 →

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 →