DB2 tablespaces can become unavailable for various reasons and when they do go off-line the problem may not be immediately visible. For example tablespaces in "backup pending mode" can be readable but not updateable.
For example:
------------------------------ Command Entered ------------------------------ update sch1.table1set ind = 'N' where provider = 'ABC'
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0290N Table space access is not allowed. SQLSTATE=55039
This looks like a permissions problem but it's actually an issue with the physical tablespace. We can enumerate the tablespace details with the "db2 list tablespaces". In this case the hex code for the "state" command indicates backup pending.
Table
Tablespace ID = 91 Name = SW380 Type = System managed space Contents = Any data State = 0x0020 Detailed explanation: Backup pending
This procedure returns a list of tablespaces where state is abnormal.
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | #!d:\\Tcl\\bin\\tclsh84
######################################
# check db2 tablespaces
######################################
proc checkTablespace {userid password database} {
package require textutil
if {[catch {exec db2 connect to $database user $userid using $password} r] == 0} {
set contiue true
} else {
return -code 1 [list $r]
}
set tableSpaces [exec db2 list tablespaces]
set listed [textutil::splitx $tableSpaces Tablespace]
foreach list $listed {
set r [lsearch -exact $list State]
if {$r == -1} {
set continue true
} else {
if {[lindex $list [expr {$r + 2}]] == "0x0000"} {
set status ok
} else {
lappend result_list $list
set status notOK
}
}
}
exec db2 terminate
if {$status == "ok"} {
return
} else {
return -code 1 $result_list
}
}
|
