Welcome, guest | Sign In | My Account | Store | Cart

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.

Tcl, 42 lines
 1
 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
    }

}
Created by Patrick Finnegan on Tue, 1 Jul 2003 (MIT)
Tcl recipes (162)
Patrick Finnegan's recipes (56)

Required Modules

  • (none specified)

Other Information and Tasks