HP 3000 Manuals HP 3000 Manuals
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 commands:
For HP-UX:
isql=> CONNECT TO 'hpsql/sampledb/PartsDBE'; Return
MPE XL:
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.
____________________________________________________________________________________
| |
| |
| 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 |
| |
____________________________________________________________________________________
Figure 4-2. Information on Tables and Views
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.
____________________________________________________________________________________
| |
| |
| 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 |
| |
____________________________________________________________________________________
Figure 4-3. View Definitions in the System Catalog
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:
_____________________________________________________________________________________
| |
| |
| 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 |
| |
_____________________________________________________________________________________
Figure 4-4. Output of the INFO Command
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:
____________________________________________________________________________________
| |
| |
| 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 |
| |
____________________________________________________________________________________
Figure 4-5. System Catalog Information on Indexes
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:
____________________________________________________________________________________
| |
| |
| 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 |
| |
____________________________________________________________________________________
Figure 4-6. Groups in the System Catalog
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:
____________________________________________________________________________________
| |
| |
| 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 |
| |
____________________________________________________________________________________
Figure 4-7. Table Authorities in the System Catalog
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
The query result is shown in the next figure:
____________________________________________________________________________________
| |
| |
| select userid, tablename, owner, colname from system.colauth where tablenam |
| --------------------+--------------------+--------------------+------------ |
| USERID |TABLENAME |OWNER |COLNAME |
| --------------------+--------------------+--------------------+------------ |
| KELLY |INVENTORY |PURCHDB |BINNUMBER |
| KELLY |INVENTORY |PURCHDB |QTYONHAND |
| KELLY |INVENTORY |PURCHDB |LASTCOUNTDAT |
| PETER |INVENTORY |PURCHDB |BINNUMBER |
| PETER |INVENTORY |PURCHDB |QTYONHAND |
| PETER |INVENTORY |PURCHDB |LASTCOUNTDAT |
| --------------------------------------------------------------------------- |
| Number of rows selected is 6 |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e |
| |
____________________________________________________________________________________
Figure 4-8. Column Authorities in the System Catalog