0

I'm using ancient Sybase database servers. In those servers, I can do a offline backup where I can backup a set of device files, for databases that are created on that device files. The mapping from database name to device name is many-to-many. For example:

dbname devicename
------ ----------
db1    device1
db1    device2
db2    device2
db4    device3
db5    device2
db6    device3

How can I find the smallest sets of device names which contain a set of databases?

In the simple example above:

device1,device2 contains db1, db2, db5
device3         contains db4, db6

Later edit: I was asked to post the sql query to generate the sample data listed above. Here it is (from a Sybase server):

select dbid, vdevno from master..sysusages where vdevno>0

This will generate data like:

dbid   vdevno     
------ -----------
 31514           1
 31513           2
     2           3
     2           4
     6           4
...snip...
Ben Slade
  • 478
  • 5
  • 11
  • How did you arrive at the first 2 column table shown above? is it SQL? if so, please place that into your question. (**not** as an image please) – Paul Maxwell May 23 '23 at 01:56
  • It's not an image. I just typed it in, formatted as code to get fixed width spacing. It should be selectable/copyable. – Ben Slade May 23 '23 at 14:15
  • I know it is text. What I was hoping to learn is did you run a sql query to get that data. If yes, paste that query into the question (as text) – Paul Maxwell May 23 '23 at 18:13

1 Answers1

0

I think this might similar to something called Set-based Attribute Reduction (eg. used in data mining), but my case is much simpler.

I think the easiest way to do it is go find all the vdevno's for a dbid, then find all the other dbid's that use that first set of vdevno's. For example (using Sybase temp tables starting with "#"):

Pick the first user database with dbid=7 (as an example)

Find all the vdevno's for dbid=7

> select dbid,vdevno from sysusages where dbid=7;
  dbid   vdevno     
  ------ -----------
       7           5
       7           6
       7           5

Insert in them into a #tmp table:

> select dbid,vdevno into #tmp from sysusages where dbid=7;

Then find all the other dbid's that use vdevnos used by dbid 7 (stored in #tmp)

> select distinct dbid,vdevno from sysusages 
> where dbid not in (select dbid from #tmp) 
> and vdevno in (select vdevno from #tmp);
 dbid   vdevno
 ------ -----------
      4           5
      6           5
  31515           5
  31517           5
      9           5
     18           5
     12           5
     26           5
     31           5
     32           5
     34           5
     35           5
     36           5
      5           6
  31516           6
  31517           6
      8           6

And insert those other dbid's into the #tmp table too:

> insert into #tmp
> select distinct dbid,vdevno from sysusages 
> where dbid not in (select dbid from #tmp) 
> and vdevno in (select vdevno from #tmp);

Then see if there are any dbid's that are not in #tmp, but have vdevno's in #tmp:

> select count(*) as row_cnt from sysusages 
> where dbid not in (select dbid from #tmp) 
> and vdevno in (select vdevno from #tmp);
      row_cnt    
  -----------
            0

No more, so now #tmp contains a complete list of all databases (and their vdevno's) that share vdevno's with dbid=7

Next, you need to see if there are other dbid's not included in #tmp (not included in the #tmp pool of dbid's sharing vdevno's). If so, you need to restart the process for those other dbid's using a new #tmp table. There are quite a few for me:

> select distinct dbid from sysusages where dbid not in (select dbid from #tmp) where dbid>7;
 dbid
 ------
     10
     13
     15
     16
     17
     22
     23
     24
     25
     28
     29
     30
  31513
  31514

So I would restart the process with dbid=10 and a new #tmp1 table:

> select dbid,vdevno from sysusages where dbid=10;
 dbid   vdevno
 ------ -----------
     10           8
     10           7
     10           8
     10           9
     10          46
     10          46

> select dbid,vdevno into #tmp1 from sysusages where dbid=10;

> insert into #tmp1
> select distinct dbid,vdevno from sysusages 
> where dbid not in (select dbid from #tmp1) 
> and vdevno in (select vdevno from #tmp1);

And repeat the above insert query until no new rows are inserted. Then #tmp1 contains a new pool of dbid's that share vdevno's

Ben Slade
  • 478
  • 5
  • 11