Something just occurred to me.  The SYSTEM DBEFILESET is populated with a bunch
of things, amongst which you will find the views for the various IMAGE/SQL
tables created during the ATTACH command.

What you could do is the following:

Using ISQL, connect to the DBE and then do:

SELECT * FROM SYSTEM.TABLE WHERE DBEFILESET = 'SYSTEM';

Scan the list to see where the owner will be the IMAGE/SQL database name and
you will see all these views.  You could delete all the views dealing with
automatic masters and then selectively delete the views for users that would
never access through ISQL or SQL.

You see, when you attach a database, all datasets in that database create a
view for each and every password level, in the SYSTEM DBEFILESET.  The vast
majority of these views are useless and should be purged.  Please remember they
will come back when you next detach and attach!

Kind regards,

Denys. . .

Denys Beauchemin
HICOMP
(800) 323-8863  (281) 288-7438         Fax: (281) 355-6879
denys at hicomp.com                             www.hicomp.com


-----Original Message-----
From:   EBEN YONG [SMTP:[log in to unmask]]
Sent:   Monday, March 12, 2001 8:29 PM
To:     [log in to unmask]
Subject:        ImageSQL and DBERR 2502

This question goes out to the ImageSQL gurus.  Notice below the DBERR 2502
when the SQL statement is attempted:

isql=> CONNECT TO 'HLTHDBE';
isql=> select a.prov#, count(a.ccn#), b.firstname, b.lastname
> from online.drug_claim_load a, health.provider b
> where b.prov# = a.prov#
> group by a.prov#, b.firstname, b.lastname;
MIXED space exhausted in DBEFileSet SYSTEM.  (DBERR 2502)
isql=>

>From the ALLBASE/SQL Message Manual:
*-----------
2502

MESSAGE   ! space exhausted in DBEFileSet !. (DBERR 2502)
CAUSE   This message can have several meanings as follows:

All the space is exhausted in a DBEFileset if no expandable DBEFiles are
available.

All fixed-length DBEFiles in the DBEFileSet have run out of space, and all
expandable DBEFiles have been fully expanded.
The first parameter is the setting for the DBEFile's TYPE attribute. Valid
settings are INDEX, TABLE, and MIXED.

ACTION   Add new DBEFiles of the appropriate type to the DBEFileSet. If
space has been exhausted in the System.DBEFileSet during a query, try adding
more tempspace and more contiguous physical disk space.
*-----------

I have attempted to accomplish the recommended action.  From SQLGEN, here
are the statements I have used in an attempt to add DBEfiles to my DB
Environment:

*-----------
/* This file was created with a user language environment of */
/*    NATIVE-3000                                            */


CREATE DBEFILESET HLTHMFS;

CREATE DBEFILESET IMAGESQL;

CREATE DBEFILE HLTHMIX2
   WITH PAGES = 10000,
   NAME = 'HLTHMIX2',
   TYPE = MIXED;

ADD DBEFILE HLTHMIX2 TO DBEFILESET HLTHMFS;

CREATE DBEFILE HLTHMIX3
   WITH PAGES = 100000,
   NAME = 'HLTHMIX3',
   TYPE = MIXED;

ADD DBEFILE HLTHMIX3 TO DBEFILESET HLTHMFS;

CREATE DBEFILE HLTHMIX1
   WITH PAGES = 3000,
   NAME = 'HLTHMIX1',
   TYPE = MIXED;

ADD DBEFILE HLTHMIX1 TO DBEFILESET IMAGESQL;

COMMIT WORK;
*-----------

We have noticed that the error pertains to running out of SYSTEM space.  But
ISQL will not allow me to add a mixed type dbefile to the system dbefileset
(IMAGESQL) of more than 3000 pages.

In any event, if anyone can shed some light on this issue, I would really
appreciate it.  Thanks.

Eben Yong
Health Plan of San Mateo