[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] ______________________________________________________________________________