Examining PartsDBE

In this section, you will examine the objects that were created within PartsDBE--tables, views, indexes, and authority structure. Information about all these objects is in the system catalog, which is automatically created by ALLBASE/SQL as the DBEnvironment is configured.

Run ISQL, then CONNECT to PartsDBE. (If you are using HP-UX, first change back to the directory from which you ran the script to create PartsDBE. You must have write permission in the directory from which you CONNECT.) Use one of the following CONNECT statements:

For HP-UX:

   isql=> CONNECT TO 'hpsql/sampledb/PartsDBE'; Return

MPE/iX:

   isql=> CONNECT TO 'PartsDBE'; Return

Now examine the system catalog by creating queries on the system views.

Examining the Tables and Views

Use the following query exactly as shown to look at all the tables and views created by the setup script:

   isql=> SELECT NAME, OWNER, Return

   > DBEFILESET, TYPE Return

   > FROM SYSTEM.TABLE Return

   > WHERE OWNER <> 'SYSTEM'; Return

The result table is shown below.

Figure 6-2 Information on Tables and Views

   select name, owner, dbefileset, type from system.table where owner <> 'SYST

   --------------------+--------------------+--------------------+------      

   NAME                |OWNER               |DBEFILESET          |TYPE        

   --------------------+--------------------+--------------------+------      

   SUPPLYBATCHES       |MANUFDB             |WAREHFS             |     0

   TESTDATA            |MANUFDB             |WAREHFS             |     0

   PARTS               |PURCHDB             |WAREHFS             |     0

   INVENTORY           |PURCHDB             |WAREHFS             |     0

   SUPPLYPRICE         |PURCHDB             |PURCHFS             |     0

   VENDORS             |PURCHDB             |PURCHFS             |     0

   ORDERS              |PURCHDB             |ORDERFS             |     0

   ORDERITEMS          |PURCHDB             |ORDERFS             |     0

   PARTINFO            |PURCHDB             |SYSTEM              |     1

   VENDORSTATISTICS    |PURCHDB             |SYSTEM              |     1

   MEMBERS             |RECDB               |RECFS               |     0

   CLUBS               |RECDB               |RECFS               |     0

   EVENTS              |RECDB               |RECFS               |     0

   ---------------------------------------------------------------------------

   Number of rows selected is 13

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e

Each table is identified by the NAME column. The OWNER column specifies the database to which the table belongs. If a table does not belong to you (that is, if you are not the database owner), you must prefix the table name with its owner name whenever you refer to it.

The DBEFILESET column contains the name of the DBEFileSet an entry has been associated with, and the TYPE column indicates whether the entry is a table or a view. Entries with type 0 are tables, and entries with type 1 are views. Note that all views are automatically associated with the SYSTEM DBEFileSet.

View Definitions

You can see the view definitions by issuing the following query exactly as shown:

   isql=> SELECT VIEWNAME, SELECTSTRING Return

   > FROM SYSTEM.VIEWDEF WHERE Return

   > OWNER = 'PURCHDB'; Return

The query result is shown in the next figure.

Figure 6-3 View Definitions in the System Catalog

   select viewname,selectstring from system.viewdef where owner = 'PURCHDB';  

   --------------------+------------------------------------------------------

   VIEWNAME            |SELECTSTRING                                          

   --------------------+------------------------------------------------------

   PARTINFO            | SELECT PurchDB.SupplyPrice.PartNumber, PurchDB.Parts.

   PARTINFO            |PurchDB.SupplyPrice.VendorNumber, PurchDB.Vendors.Vend

   PARTINFO            |PurchDB.Supplyprice.VendPartNumber,

   PARTINFO            |PurchDB.SupplyPrice.UnitPrice, PurchDB.SupplyPrice.Dis

   PARTINFO            |FROM PurchDB.Parts, PurchDB.SupplyPrice, PurchDB.Vendo

   PARTINFO            |PurchDB.SupplyPrice.PartNumber = PurchDB.Parts.PartNum

   PARTINFO            |PurchDB.SupplyPrice.VendorNumber = PurchDB.Vendors.Ven

   VENDORSTATISTICS    | SELECT PurchDB.Vendors.VendorNumber, PurchDB.Vendors.

   VENDORSTATISTICS    |, OrderDate, OrderQty, OrderQty * PurchasePrice FROM

   VENDORSTATISTICS    |PurchDB.Vendors, PurchDB.Orders, PurchDB.OrderItems WH

   VENDORSTATISTICS    |PurchDB.Vendors.VendorNumber = PurchDB.Orders.VendorNu

   VENDORSTATISTICS    |PurchDB.Orders.OrderNumber = PurchDB.OrderItems.OrderN

   ---------------------------------------------------------------------------

   Number of rows selected is 12

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e

Scroll to the right to examine the complete select string for each view definition.

Using the INFO Command

You can see individual table descriptions by using the INFO command, which returns the column definition of a table. Use the following command for the Vendors table:

   isql=> INFO PURCHDB.VENDORS; Return

The output from this ISQL command is shown below:

Figure 6-4 Output of the INFO Command

   isql=> info purchdb.vendors;



   Column Name          Data Type (length)     Nulls Allowed   Language

   ----------------------------------------------------------------------------

   VENDORNUMBER         Integer                NO            

   VENDORNAME           Char (   30)           NO              n-computer

   CONTACTNAME          Char (   30)           YES             n-computer

   PHONENUMBER          Char (   15)           YES             n-computer

   VENDORSTREET         Char (   30)           NO              n-computer

   VENDORCITY           Char (   20)           NO              n-computer

   VENDORSTATE          Char (    2)           NO              n-computer

   VENDORZIPCODE        Char (   10)           NO              n-computer

   VENDORREMARKS        VarChar (   60)        YES             n-computer

The Column Name column lists the names of all the columns in the table. The Data Type column shows the specific data type for each column and its size (in parentheses). The third column, Nulls Allowed, indicates whether or not NULL values are permitted in the column, and the Language column indicates which language is applicable for the column if it is a character type.

Examining Indexes

The following query shows the indexes on tables in PartsDBE:

   isql=> SELECT INDEXNAME, TABLENAME, Return

   > UNIQUE, CLUSTER FROM SYSTEM.INDEX; Return

The query result is shown in the next figure:

Figure 6-5 System Catalog Information on Indexes

   select indexname,tablename,unique,cluster from system.index;               

   --------------------+--------------------+------+-------                   

   INDEXNAME           |TABLENAME           |UNIQUE|CLUSTER                   

   --------------------+--------------------+------+-------                   

   PARTNUMINDEX        |PARTS               |     1|      0

   PARTTONUMINDEX      |SUPPLYPRICE         |     0|      1

   PARTTOVENDINDEX     |SUPPLYPRICE         |     0|      0

   VENDPARTINDEX       |SUPPLYPRICE         |     1|      0

   VENDORNUMINDEX      |VENDORS             |     1|      0

   ORDERNUMINDEX       |ORDERS              |     1|      1

   ORDERVENDINDEX      |ORDERS              |     0|      0

   ORDERITEMINDEX      |ORDERITEMS          |     0|      1

   INVPARTNUMINDEX     |INVENTORY           |     1|      0

   ---------------------------------------------------------------------------

   Number of rows selected is 9

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e

The UNIQUE and CLUSTER columns show what kind of index was created: PartNumIndex is a unique index; PartToNumIndex is a clustering index; OrderNumIndex is both unique and clustering; and OrderVendIndex is neither unique nor clustering.

Examining the Authority Structure

An authority structure consists of many elements. Some of these elements are shown below:

Group definitions.
Table authorizations for select, insert, update, and delete operations on tables.
Column authorizations for permission to update specific columns.

Groups

Use the following query to examine the authorization groups in PartsDBE and their members:

   isql=> SELECT * FROM SYSTEM.GROUP; Return

The query result is shown below:

Figure 6-6 Groups in the System Catalog

   select * from system.group;                                                

   --------------------+--------------------+--------------------+----------- 

   USERID              |GROUPID             |OWNER               |NMEMBERS    

   --------------------+--------------------+--------------------+----------- 

   PURCHMANAGERS       |PURCHMANAGERS       |PETER               |          3

   MARGY               |PURCHMANAGERS       |PETER               |          0

   RON                 |PURCHMANAGERS       |PETER               |          0

   SHARON              |PURCHMANAGERS       |PETER               |          0

   PURCHDBMAINT        |PURCHDBMAINT        |PETER               |          3

   ANNIE               |PURCHDBMAINT        |PETER               |          0

   DOUG                |PURCHDBMAINT        |PETER               |          0

   DAVID               |PURCHDBMAINT        |PETER               |          0

   PURCHASING          |PURCHASING          |PETER               |          5

   AJ                  |PURCHASING          |PETER               |          0

   JORGE               |PURCHASING          |PETER               |          0

   RAGAA               |PURCHASING          |PETER               |          0

   GREG                |PURCHASING          |PETER               |          0

   KAREN               |PURCHASING          |PETER               |          0

   RECEIVING           |RECEIVING           |PETER               |          3

   AL                  |RECEIVING           |PETER               |          0

   ---------------------------------------------------------------------------

   First 16 rows have been selected.

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e

For each group, the members are listed. Note that the group and the member names are listed in the USERID column, and the number of members appears in each row where the group name appears as a USERID. The OWNER column shows the owner of the authorization group.

Table Authorities

Use the following query exactly as shown to examine the authorizations on the PurchDB.Inventory table:

   isql=> SELECT USERID, SELECT, INSERT, Return

   > UPDATE, DELETE, ALTER, INDEX Return

   > FROM SYSTEM.TABAUTH WHERE Return

   > NAME = 'INVENTORY'; Return

The query result is shown below:

Figure 6-7 Table Authorities in the System Catalog

   select userid, select, insert, update,delete, alter, index from system.taba

   --------------------+------+------+------+------+-----+-----               

   USERID              |SELECT|INSERT|UPDATE|DELETE|ALTER|INDEX               

   --------------------+------+------+------+------+-----+-----               

   PURCHMANAGERS       |Y     |N     |N     |N     |N    |N

   PURCHDBMAINT        |Y     |Y     |Y     |Y     |Y    |Y

   PURCHASING          |Y     |Y     |Y     |Y     |N    |N

   WAREHOUSE           |Y     |Y     |Y     |Y     |N    |N

   KELLY               |N     |N     |C     |N     |N    |N

   PETER               |N     |N     |C     |N     |N    |N

   DBEUSERS            |Y     |Y     |Y     |Y     |N    |N

   ---------------------------------------------------------------------------

   Number of rows selected is 7

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e

Each row contains a USERID, which is the name of a user or group, and an entry for each type of authority. A Y in a column indicates that the USERID has that authority, an N indicates the USERID does not have that authority.

Column Authorizations

A special kind of authorization is the permission to update specific columns in a table. These permissions are shown in the SYSTEM.COLAUTH view in the system catalog.

Use the following query exactly as shown to display the column authorizations defined for the PurchDB.Inventory table:

   isql=> SELECT USERID, TABLENAME, Return

   > OWNER, COLNAME FROM Return

   > SYSTEM.COLAUTH WHERE Return

   > TABLENAME = 'INVENTORY'; Return