[log in to unmask] (Pankaj Arora) wrote:
> > >The foll error comes when creating a table in ALLBASE
> > >
> > > DBEFILESET has no DBE files (DBERR 2501)
> > > Table space on DBEFILESET xyz is not granted for xxxx
> > >
> > >This error comes irrespective of the fact that DBE files are created
> > >and added to DBEFILESET. As a result the tables are not getting
> > >created.
In G.0, changes were made to ALLBASE to improve preprocessing concurrency
and give the DBA greater control over space management. If a machine is
licensed for IMAGE/SQL, but is not licensed for ALLBASE/SQL, then
- users are allowed to store sections (which are essentially compiled forms
of SQL queries) in any DBEFileSet.
- users are only allowed to create tables in the IMAGESQL DBEFileSet.
IMAGE/SQL is a "restricted version" of ALLBASE/SQL. The restriction is that
users are only allowed to create tables in the IMAGESQL DBEFileSet, and the
maximum capacity of the IMAGESQL DBEFileSet is 3000 (4KB) pages. IMAGE/SQL
provides SQL connectivity to IMAGE data, and allows you to create some table
data in ALLBASE/SQL. When you purchase the ALLBASE/SQL license, you obtain
the "unrestricted version" of ALLBASE:
- there is no maximum capacity on any DBEFileSet.
- users are allowed to create tables in any DBEFileSet that the DBA allows
them to.
Pankaj, I believe that the cause of the problem you encountered is that on a
machine licensed for IMAGE/SQL, you tried to create a table in a DBEFileSet
other than IMAGESQL. I duplicated the problem using the following commands
(I first used IMSQL to create musicdbe):
isql=> create dbefileset userfset;
isql=> create dbefile file1 with pages=3, name = 'file1';
isql=> add dbefile file1 to dbefileset userfset;
isql=> commit work;
isql=> create table mytable (c1 integer) in userfset;
TABLESPACE on DBEFileSet USERFSET is not granted for [log in to unmask]
Default DBEFileSet IMAGESQL used. (DBWARN 2098)
The table was created (look at SYSTEM.TABLE), but it was created in the
IMAGESQL DBEFileSet. If a user tries to create a table in a DBEFileSet that
they don't have TABLESPACE authority for, the table is created but it is
created in the default DBEFileSet, and a warning is generated.
I agree that DBWARN 2098 is a bit confusing if you don't understand what
is going on underneath, but the explanation in the message manual is pretty
good.
Here are some more details about G.0:
*) The GRANT/REVOKE commands were enhanced so that the DBA could grant
TABLESPACE or SECTIONSPACE authority to a particular user or group:
GRANT TABLESPACE on DBEFILESET userfset to joe@sales;
TABLESPACE grants authority to store tables in the DBEFileset.
SECTIONSPACE grants authority to store sections in the DBEFileset.
Prior to this change, a user who had been granted RESOURCE authority
was able to create tables in any DBEFileSet. In G.0, a user can only
create tables in the DBEFileSets for which he or she has been granted
TABLESPACE authority. This allows the DBA to control how much space
a particular user or group can use.
If a machine is licensed for IMAGE/SQL, users are only allowed to
create tables in the IMAGESQL DBEFileSet. If the DBA tries to
grant tablespace on another dbefileset, then an error is returned:
Grant TABLESPACE on USERFSET DBEFILESET is not allowed. (DBERR 3501)
*) SYSTEM.SPACEAUTH lists all users and groups for which TABLESPACE or
SECTIONSPACE authority has been granted.
If a machine is licensed for IMAGE/SQL, the default entries for
SYSTEM.SPACEAUTH look like:
isql=> select * from system.spaceauth;
--------------------+--------------------+----------+------------
USERID |DBEFILESET |TABLESPACE|SECTIONSPACE
--------------------+--------------------+----------+------------
PUBLIC |IMAGESQL |Y |Y
PUBLIC |SYSTEM |N |Y
Translation: users are only allowed to create tables in the IMAGESQL
DBEFileSet, but they can create sections in any DBEFileSet that the
DBA allows them to.
In order to create tables in a DBEFileSet other than IMAGESQL, you need
to purchase the license for ALLBASE/SQL. After you have the license,
the DBA needs to GRANT TABLESPACE on the appropriate DBEFileSets to
the appropriate users.
*) The SET DEFAULT DBEFILESET statement is used to set the default
DBEFileSet for PUBLIC. Remember, if a user tries to create a table in a
DBEFileSet that they don't have TABLESPACE authority for, the table is
created but it is created in the default DBEFileSet.
*) SYSTEM.SPACEDEFAULT contains the default TABLESPACE and SECTIONSPACE
DBEFileSets.
isql=> select * from system.spacedefault;
--------------------+--------------------+---------
USERID |DBEFILESET |SPACETYPE
--------------------+--------------------+---------
PUBLIC |IMAGESQL | 2 /* TABLESPACE */
PUBLIC |SYSTEM | 1 /* SECTIONSPACE */
For IMAGE/SQL, the default DBEFileSet for tables is IMAGESQL and
the default DBEFileSet for sections is SYSTEM. The DBA can change
the default DBEFileSet for sections, but cannot change the default
DBEFileSet for tables unless the license for ALLBASE/SQL has been
obtained.
Hope this helps,
Leslie-Anne
P.S. It is true (unfortunately) that a DBE created on an IMAGE/SQL machine
will have the IMAGESQL DBEFileSet, but no DBEFiles. A CREATE TABLE
statement will succeed (because only the system catalog is affected,
i.e. a row is inserted into system.table, etc.), but errors will be
returned when you try to insert data into the table (because there
are no files to store the data on).
The most recent versions of the ODBCVIEW script has logic that will
add a DBEFile to the IMAGESQL DBEFileSet if it does not exist. If you
don't use the ODBCVIEW script, you need to add at least one DBEFile to
the IMAGESQL DBEFileSet if you want to store data in SQL tables.
______________________________________________________________________________
Leslie-Anne Bain
Hewlett-Packard Company Voice: 408/447-0666
19447 Pruneridge Ave, MS 47UX FAX: 408/447-7902
Cupertino, CA 95014-9913 E-Mail: [log in to unmask]
______________________________________________________________________________
|
