HP 3000 Manuals HP 3000 Manuals
SYSSQL System Catalog Views
4GL tools on the PC use a special set of views to access system catalog
information on the database server. These server-independent system
catalog views, owned by SYSSQL, are intended to be a common subset of
system catalog views supported by most SQL DBMSs.
The server-independent system catalog views are used by Gupta C/API. For
more information about these special views, refer to the Gupta manual,
SQLBase SQLTalk Language Reference Manual. For information about the
differences between ALLBASE/SQL and the server-independent views, refer
to the chapter "SQL Differences Between ALLBASE/SQL and SQLBase." For
information about ALLBASE/SQL system catalog tables, refer to the
ALLBASE/SQL Database Administration Guide.
The views owned by SYSSQL were installed when you executed the Views
Script from the Scriptor Dialog Box.
Editing the VIEWS.SCP File
If your SQL.INI file contains all the valid DBEnvironments and
ALLBASE/SQL users, you can change just the database code and/or the user
code in the VIEWS.SCP file. These codes will then reference the codes in
the SQL.INI file.
Change the database code from server1 to the new name, and if necessary,
change the sysadm user code to the new name. Then execute the Views
Script from Scriptor as described in the "Installing PC API" chapter.
Executing VIEWS.SCP for Multiple DBEnvironments
Each DBEnvironment must have a set of SYSSQL views. You can make
multiple copies of VIEWS.SCP, and in each copy, change the default code
names sysadm and server1 to new code names that identify the user
installing the views for each DBEnvironment. You must also create a
corresponding entry for each new code name in the SQL.INI file.
For example, suppose you have configured three new DBEnvironments that
you want to make available to PC API users. These DBEnvironments are
named SalesDBE, AcctDBE, and BankDBE. Users other than the one identified
by sysadm will be installing the views for all three new DBEnvironments.
You may want to do the following:
1. Make three copies of VIEWS.SCP:
C:copy views.scp vwsales.scp
C:copy views.scp vwacct.scp
C:copy views.scp vwbank.scp
2. Edit each new .SCP file, changing the default database code
server1 to Sales, Acct, and Bank, respectively.
3. Edit each new .SCP file, changing the default user code sysadm to
dba1, dba2, and dba3, respectively. Or, you can use the same user
code dba1 in all three .SCP files, to designate the same
User.Account for all three sets of views.
4. Add entries corresponding to each new database code (assigned in
step 2) to the SQL.INI file:
mpedbname=Sales,NodeName:SalesDBE.Group.Account,sk
mpedbname=Acct,NodeName:AcctDBE.Group.Account,nw
mpedbname=Bank,NodeName:BankDBE.Group.Account,ip
The last field of each entry in the example above shows how you
can specify different network protocols for each defined
mpedbname.
5. Add an entry corresponding to each new user code (assigned in step
3) to the SQL.INI file:
mpeuser=dba1,User/UserPass.Account/AcctPass
mpeuser=dba2,User/UserPass.Account/AcctPass
mpeuser=dba3,User/UserPass.Account/AcctPass
If you assigned the same User, dba1, in all three .SCP files, you
need to define only one new mpeuser entry, for dba1, here.
The User who installs the views must have DBA authority, because
User grants owner status of the views to SYSSQL and grants public
access authorities to the system catalog views, by default.
6. Bring up the Scriptor Dialog Box under Microsoft Windows.
7. Execute each new view script successively. To start, type
vwsales.scp in the Path/Script Filename Box and press Enter or
click on Execute. Repeat for VWACCT.SCP and VWBANK.SCP.
ISQL and SYSSQL Views
The SYSSQL views can be installed on the database server two ways:
* From the PC client, using the Views Script with Scriptor.
* From the database server, using VIEWS.SQL with Interactive
Structured Query Language (ISQL).
The second method is discussed here.
The PC API product includes scripts called VIEWS.SQL and UNVIEWS.SQL that
you can use to add and remove the SYSSQL views. To use the .SQL scripts
in ISQL, follow these steps:
* Upload the VIEWS.SQL and UNVIEWS.SQL files to the database server
(The extensions to the scripts are then automatically dropped.
These files will be used as command files in ISQL.)
* Type ISQL at the operating system prompt.
* Connect to the DBEnvironment from the ISQL prompt:
isql=> CONNECT TO 'PartsDBE.SomeGrp.SomeAcct';
* Install the SYSSQL views:
isql=> START VIEWS;
Do not use VIEWS.SCP on a DBEnvironment with an earlier version of SYSSQL
views. Refer to the section "Migrating to PC API" in the chapter
"Installing PC API."