Find DBSpace for Index
Hi All, is there an sql statement to find out in which dbspace an index was created? Like "select dbsname from systabnames where tabname='FOO'" does for table names? Thanks, Gerd
Well, systabnames.dbsname is NOT the dbspace name but the database containing
the partition. Conversely ALL partitions, whether tables, table fragments,
detached indexes or index fragments have a record in systabnames. The partnum
contains the dbspace number and the built-in function DBINFO() can translate
that to a dbspacename:
SELECT dbsname, tabname, dbinfo('dbspace', partnum)
FROM systabnames
WHERE tabname = 'myindexname';
Art S. Kagel
----- Original Message -----
From: Gerd Dauenhauer <gerd@jazzey.com>
At: 7/ 2 12:10
> Hi All,
> is there an sql statement to find out in which dbspace an index was created?
> Like "select dbsname from systabnames where tabname='FOO'" does for table
names?
>
> Thanks,
> Gerd
--0__=09BBE456DFCDB45F8f9e8a93df938690918c09BBE456DFCDB45F Content-type: multipart/alternative; Boundary="1__=09BBE456DFCDB45F8f9e8a93df938690918c09BBE456DFCDB45F" --1__=09BBE456DFCDB45F8f9e8a93df938690918c09BBE456DFCDB45F Content-type: text/plain; charset=US-ASCII Hi, Try this.. "select dbspace from sysfragments where indexname = "idx"; Thanx, Nita Dembla Software Engineer IBM - Data Management Solutions Work : 913-599-8675 |---------+----------------------------> | | "GERD DAUENHAUER"| | | <gerd@jazzey.com>| | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 07/02/2004 10:33 | | | AM | |---------+----------------------------> >------------------------------------------------------------------------------- -----------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Find DBSpace for Index [3185] | >------------------------------------------------------------------------------- -----------------------------------------------| Hi All, is there an sql statement to find out in which dbspace an index was created? Like "select dbsname from systabnames where tabname='FOO'" does for table names? Thanks, Gerd --1__=09BBE456DFCDB45F8f9e8a93df938690918c09BBE456DFCDB45F Content-type: text/html; charset=US-ASCII Content-Disposition: inline <html><body> <p>Hi,<br> <br> Try this..<br> <br> "select dbspace from sysfragments where indexname = "idx";<br> <br> Thanx,<br> Nita Dembla<br> Software Engineer<br> IBM - Data Management Solutions<br> Work : 913-599-8675<br> <br> <img src="cid:10__=09BBE456DFCDB45F8f9e8a93df938@us.ibm.com" width="16" height="16" alt="Inactive hide details for "GERD DAUENHAUER" <gerd@jazzey.com>">"GERD DAUENHAUER" <gerd@jazzey.com><br> <br> <br> <table V5DOTBL=true width="100%" border="0" cellspacing="0" cellpadding="0"> <tr valign="top"><td width="1%"><img src="cid:20__=09BBE456DFCDB45F8f9e8a93df938@us.ibm.com" border="0" height="1" width="72" alt=""><br> </td><td style="background-image:url(cid:30__=09BBE456DFCDB45F8f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " width="1%"><img src="cid:20__=09BBE456DFCDB45F8f9e8a93df938@us.ibm.com" border="0" height="1" width="225" alt=""><br> <ul> <ul> <ul> <ul><b><font size="2">"GERD DAUENHAUER" <gerd@jazzey.com></font></b><br> <font size="2">Sent by: forum.subscriber@iiug.org</font> <p><font size="2">07/02/2004 10:33 AM</font></ul> </ul> </ul> </ul> </td><td width="100%"><img src="cid:20__=09BBE456DFCDB45F8f9e8a93df938@us.ibm.com" border="0" height="1" width="1" alt=""><br> <font size="1" face="Arial"> </font><br> <font size="2"> To: </font><font size="2">ids@iiug.org</font><br> <font size="2"> cc: </font><br> <font size="2"> Subject: </font><font size="2">Find DBSpace for Index [3185]</font></td></tr> </table> <br> <br> <tt>Hi All,<br> is there an sql statement to find out in which dbspace an index was created?<br> Like "select dbsname from systabnames where tabname='FOO'" does for table names?<br> <br> Thanks,<br> Gerd<br> <br> <br> </tt> </body></html> --1__=09BBE456DFCDB45F8f9e8a93df938690918c09BBE456DFCDB45F-- --0__=09BBE456DFCDB45F8f9e8a93df938690918c09BBE456DFCDB45F Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=09BBE456DFCDB45F8f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=09BBE456DFCDB45F8f9e8a93df938690918c09BBE456DFCDB45F Content-type: image/gif; name="ecblank.gif" Content-Disposition: inline; filename="ecblank.gif" Content-ID: <20__=09BBE456DFCDB45F8f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=09BBE456DFCDB45F8f9e8a93df938690918c09BBE456DFCDB45F Content-type: image/gif; name="pic17587.gif" Content-Disposition: inline; filename="pic17587.gif" Content-ID: <30__=09BBE456DFCDB45F8f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZeZgLVA9YVCpnGagVjV171aRVrYR RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFilKQdI8GA5IcpApeJQt8L09lmgkH LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d5VyKtCKW3jfz4uMKmq3xu4N0nK BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGTKhQMcgQEEAnEjFS98+RnW3smGkZU6ncCWav/4wYOnAI TihRL/4FEwbp28BXMMcoscQCVxlepL4IGDSCyJyVQOu0o7CjmLN50OZlqWmyFy5/6yBBuji0AxFR M00oQAqNIstqI6qKHUsWRAEAvagsmfUEAImyxgbmUpJk3IklNUtJOUAVLoUr1+wqDGTE4zk+T6FG uQb3SizBCwatiiUgCBN8vrz+zFjVyQ8FWkOlg4NQiZMB5QS8QO3mpOaKnL0Z2EKvNMSILEThKhCg zMKPVxYJh23qm9KNW7pArPynMqZDiErsTMqI+LRi3QAgkFUbXpuFKhSYZALd0O5RKa2z9EYKBbpb qxIKsjUPRgD7I2XYV6wyrOw92ykExP8NW4URhknC5dKGE4v4NENQj2jXjmfNgOZDaXb5glRmXQ33 YEWQYNcZFnrYcIQLNzyTFDQNkXIff0ExVlY4srziQk43inZgL4rwxxINMvpFFAz1KOODHiu+4aEw NEjFl5B3JIKWKF3k6I9bfUGp5ZZcdunll5IA4cuHvQQJ5gcsoCWOOUwgltIwAKRxJgbIkJAQZEq0 2YliZnpZZ4BH3CnYOXldOUOfQoYDqF1LFHbXCrO8xmRsfoXDXJ6ChjCAH3QlhJcT6VWE6FCkfCco CgrMFsROrIEX3o2whVjWDjoJccN3LdggSGXLCdLEgHr1lyU3O3QxhgohNKXJCWv8JQr/PDdaqd6w 2rj1inLiGeiCJoDspAoQlYE6QWLSECehcWIYxIQES6zhbn1iImTHEQyqJ4eIxJJoUBc+3CbBuwZE V5cJPPkIjFDdeEabQbd6WgICTxiiz0f5dBKquXF6k4senwEhYGnKEFJeGrxUZy8dB8gmAXI/sPvH ESfCwVt5hTgYiqQqtdRNHQIU1PJ33ZqmzgE90OwLaoJcnMop1WiMmgkPHQRIrwgFuNV90A3doNKT mrKIN07AnGcI9BQjhCBN4RfA1qIZnMqorJCogKfGQnxSCDilTVIA0yl5ciTovgLuBDKFUDE9aQcw 9SA+rjSNf9/M1gxrj6VwDTS0IUSElMzBfsj0NFXR2kwsV1A5IF1grLgLL/r1R40BZEnuBWgmQEyb jqRwSAt6bqMCOFkvKFN2GPPkUzIm/SCF8z8pVzpbjVnMsy0vOr1hw3SaSRUhpY09v0z0J1FnwzPl fmh+xl4WtR0zGu24I4KbMQm3lnVu2oNWxI9W/lcyzA+mCKF4DBikxb/+UWtOGRiFP8qEwAayIgIA Ow== --0__=09BBE456DFCDB45F8f9e8a93df938690918c09BBE456DFCDB45F--
Gerd,
Sorry, it's not SQL, but another simple method for determining an index
dbspace is to run dbschema -ss, as in
dbschema -ss -d dbname -t tabname
Any "in dbspace" or fragmentation scheme will show up in the output.
HTH
>>> "GERD DAUENHAUER" <gerd@jazzey.com> 7/2/2004 8:33:48 AM >>>
Hi All,
is there an sql statement to find out in which dbspace an index was
created?
Like "select dbsname from systabnames where tabname='FOO'" does for
table names?
Thanks,
Gerd