Re: dbspace of non-fragmented tables

Well, there is a problem, just backwards of how I stated it yesterday. I hate it when that happens. Gabor is right - as written below, it does not matter whether the non-sysmaster database is logged or not. I had had problems in the past when trying it the other way, i.e. with sysmaster being the current database and joining to an unlogged database like: database sysmaster; select t.tabname, d.name from database:systables t, sysdbspaces d where t.tabname = "table name" and d.dbsnum = trunc(t.partnum / 1048576); I just checked again, and in 7.23.UC6 on HP-UX 10.20 at least, the above returns "568: Cannot reference an external database without logging." Technically, according to the full error message, either all databases within a transaction must use logging or all must not. I just verified that by trying a join from an unlogged database to a logged database, which returns "569: Cannot reference an external database with logging." Thus, sysmaster is a semi-exception in that you can join TO it from an unlogged database, but you can not join to an unlogged database FROM sysmaster. Confusing, isn't it? > In article <7g7ts9$s8l$1@news.xmission.com>, > Mark Collins <mcollins@us.dhl.com> wrote: > snip... > > If you want the dbspace name, you have to join with sysdbspaces: > > > > select t.tabname, d.name > > from systables t, > > sysmaster:sysdbspaces d > > where t.tabname = "table name" > > and d.dbsnum = trunc(t.partnum / 1048576);> > > > To do this join, your database must be logged. > > > > Mark Collins > > mcollins@us.dhl.com > > > > The problem lies in how easily and dangerously we forget that > > manipulating things is not the same as understanding them. > > > > > > It doesn't matter whether the database is logged or not. Even > though sysmaster shows up as "U" (unbuffered logging), it's not > a "real" database and may be joined with non-logging ones as well. > > -- > Gabor Heppes > IBM Global Services > gaborh@au1.ibm.com > Mark Collins mcollins@us.dhl.com The problem lies in how easily and dangerously we forget that manipulating things is not the same as understanding them.