RH Linux - DB2 8.1 FP 14.

We have moved our prod databases from one Linux server to the other and enable archive loging (It was circular in the old server). Then, I setup an Automatic maintenance for the 2 databases with the following settings:

Online Maintenance Window: 02:00 AM to 04:00 AM. Every Sunday.
Activities: Defragment Data (REORG)
Optimize Data Access(RUNSTATS)

I also have nightly backup that is not in the Automatic Maintenace but as separate task which runs 11:00 PM every night.

My Archive loging settings were (Disk space page):

Number of Primary Log files: 10
Number of Secondary Log files: 60
Size of each Log file: 2048

This morning we had problems updating a table from WebSphere application throwing the error:

"COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL0290N Table space access is not allowed. SQLSTATE=55039"

What could be the problem ? Thanks.............

You did take a backup of your database after changing the logging settings?

You may also want to have a look at the db2diag output. It should contain some more details on why the access was denied.

Also, did you already read through the explanation for SQL0290? http://publib.boulder.ibm.com/infoce...oc/sql0290.htm

Thanks for the quick reply. Yes I did take a look at the explanation..........Below is some entries from the db2diag.log file.........The one I noticed is:

AutoStats: [IBM][CLI Driver][DB2/LINUX] SQL0204N "SYSTOOLS.HMON_ATM_INFO" is an undefined name. SQLSTATE=42704

Although, I setup the Automatic Maintenance from 2:00 AM to 4:00 AM, was it still doing the "Automatic Runstats" at 8:45 AM ?

Please see attached for the db2diag.log file.

You did set the DIAGLEVEL to 4 before capturing the db2diag output? And the db2diag output is for the timeframe where the SQL0290 occured? I'm asking because there is nothing about 290 in your file.

It is because this error was received from WebSphere application running against the database trying to update a table which is not in the db2diag.log file. The only other error in the db2diag.log file is what is below which we know about (Files did not existed at the time the task ran).


2008-02-25-04.00.15.971878-300 I890953G501 LEVEL: Error
PID : 22919 TID : 3005430272 PROC : db2agent (DRSSFSP1)
INSTANCE: db2inst1 NODE : 000 DB : DRSSFSP1
APPHDL : 0-191 APPID: *LOCAL.db2inst1.080225090105
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 140 bytes
LOADID: 22919.2008-02-25-04.00.15.182859.0 (2;235)
Error Opening data file , -2029060079, (nil), Detected in file:sqluMonitoring.C, Line:707

2008-02-25-04.00.15.972122-300 I891455G496 LEVEL: Error
PID : 22919 TID : 3005430272 PROC : db2agent (DRSSFSP1)
INSTANCE: db2inst1 NODE : 000 DB : DRSSFSP1
APPHDL : 0-191 APPID: *LOCAL.db2inst1.080225090105
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 135 bytes
LOADID: 22919.2008-02-25-04.00.15.182859.0 (2;235)
/home/db2inst1/nightly_values.dat , 0, (nil), Detected in file:sqluMonitoring.C, Line:708

You didn't answer my question about the DIAGLEVEL setting...

I am sorry................No it is set to 3 (I guess it is default)............This is a production server and there is no way of reproducing the error.............I guess I should listen to some others who suggested that auto-maintenance is a bad idea on a production box but development or test servers.

Thanks.

Why can't you reproduce the problem?

I would be very, very careful heeding comments of the "others". Auto-maintenance is not a bad idea for production systems at all. Development servers usually don't need it at all, and test servers should have the same or very similar configuration as the production server. The question is if you tested this feature accordingly in your test environment to make sure you don't run into any surprises. Based on your descriptions above, me answer would be "no".

As for the entries in the db2diag output, my best bet would be a "user error". Maybe you had some tasks where the maintenance was not scheduled for the interval you told us; maybe your system clock is not correct; maybe something else. If you want to get to the bottom of this, we can either discuss this here (but not intermixed with another problem) or you call IBM support.

It was just a comment or two I read somwhere on the internet (don't remember where) about having
the Automatic Maintenance is not a good idea for production (I am not trying to start an argument
as I am quite new to DB2 myself)

I can not reproduce the problem because it locks the client from entering data to tables. As a result,
the company gets charged for it.

I can not reproduce it off-production hours because I don't have access to nor I know WebSphere.

I setup the Automatic Maintenace from the GUI. If you look at the first page of the GUI
(Introduction), there is a note on the page that says:

Remember! Maintenance activities do not occur at the scheduled times.
Maintenance will only occur when DB2 determines that it is required and a maintenence
window is available.

I probably misunderstood this. I thought it would do the maintenance within the
hours I specified on "Timing" page under the "Online Maintenance Window" which was 2:00 AM to 4:00 AM on Sunday mornings.

When I look at the db2diag.log file, it seems like it was still doing the REORG at 8:45 AM (Please correct me
if I am wrong). I get to this conclusion form the message:

......................
....................................
FUNCTION: DB2 UDB, Automatic Table Maintenance, db2HmonEvalReorg, probe:10
START : Automatic reorg evaluation has started on database CASBPST1


When it is doing this, it locked the user-tablespace not allowing updates to occur.

I thought I limit the Automaintenance (REORG-RUNSTATS) to 2:00 AM to 4:00 am ONLY !!!!


This is my conclusion. Then Again, I may be wrong. I am not experienced with DB2 as much as you guys.


Thanks............

I think you were right expecting the maintenance activities to _start_ within the specified time window. However, it does not guarantee that it _ends_ withn that window. If for any reason an activity (presumably REORGCHK in your case) cannot complete before the end of the window I don't believe DAS will terminate it; the activity will proceed until it completes.

I guess you should try to figure out why is it that REORGCHK takes so long. For example, what is the lock timeout setting in your database? By default it is set to -1 (forever); if REORGCHK hits a table that is locked by the application for whatever reason it may very well just sit there and wait until the end of time.

Have a look here for a more detailed explanation on maintenance windows: http://publib.boulder.ibm.com/infoce...c/c0021760.htm

Have you checked that you don't have an offline maintenance window? Also, the 4:00 end time cannot be enforced if the maintenance task will take longer. So you should first bring your database to a well-maintained state, then figure out how much weekly maintenance is necessary and then schedule accordingly.

Yes, lock timeout is default (-1). I don't have offline maintenance window. I only scheduled the online maintenance. The interesting issue is that I setup this as brand-new on last sunday at 2:30 PM. So, if I set the maintenance window to be 2:00 AM to 4:00 AM for sunday, the maintenance window was Already gone. So, it should wait until the next sunday (3/2/2008) to run for the first time. Am I wrong ?

Thanks for all your feedbacks.............