The case of the rapidly expanding TEMPSPACE1 tablespace

An application install was forcing the TEMPSPACE1 to fill very quickly and constantly getting to the system disk is full error. The query was crashing as disk full limits were stopping the query to finish successfully.The db2diag log entries were referencing ADM6017E The following table space is full. Table space name TEMPSPACE1. Disk is full A sql trace captured the query running during this activity. Within the query there was a scalar function.I used the query on How to list UDF in DB2 and returned the routine body. An examination of the routine revealed some useful details. The routine accepted... Read more →

ADM6017E The following table space is full. Table space name TEMPSPACE1. Disk is full

Question: During an application installation this error appeared on the db2diag log ADM6017E The following table space is full. Table space name: "TEMPSPACE1". The underlying file system is full There is very little flexibility about changing the application code and also very limited documentation – limiting my understanding of the amount of TEMPSPACE required. Is there a quick way to workaround this problem? Answer: There are workarounds to the disk is full error when requiring extra temp tablespace particular problem. Without knowing the details , I suspect it’s some sort of function hitting the data which is causing excessive requirement... Read more →

Difference db2diag log and nfy administration notification log

Question: What’s the difference between the db2diag log and the DB2 instance nfy administration log file? Answer: In summary: The Administration notification log (nfy ) file reports on a significant event for a DB2 instance. The db2diag log presents diagnostic details about errors. When troubleshooting a db2 error the db2diag log files has detailed information, The level of detail is reliant on the DIAGLEVEL applied to the instance The level of information in the nfy file usually is accompanied by the DB2 error code. Typical activity recorded is storage issue, indexing activities and DB2 table errors. For a full list... Read more →

How to search db2diag tool for a specific timeframe

The backup administrator was troubleshooting a DB2 backup failure with the vendor and requested the db2diag output for a specific timeframe . The steps I took to extract the information from the db2diag tool were: Log on to the DB2 LUW instance with a suitable logon account. Go to the path of the db2diag log. Normally the db2diag log location is sqllib/db2dump but you can also check the db2 dbm cfg command. Look for the DIAGPATH parameter value. If you’re on Linux you can use the grep command - db2 get dbm cfg | grep DIAGPATH Once you have moved... Read more →

Restore a DB2 LUW database without a DB2 backup copy from filesystem backup

Question: A developer corrupted some data and requested a restore to a previous recovery point. A standard request. When checking the backup system , there were no backup copies available. The backups had been failing for the last couple of nights and the monitoring alerts were not working. I checked around the backup history file , in case any ad-hoc backups had completed, and there none available. As a last resort I checked if there had been a flat file backup , and amazingly there was a flat file backup of all the files on the server. This is unusual... Read more →