Use the SQLVIEW command to generate an SQL description using the Schema Administrator command interface.
SQLVIEW Command
── SQLVIEW ─┬─ DATABASE <database title> ────────────────┬─────────────►
└─ LOGICAL DATABASE <logical database title ─┘
┌◄────────────────────────────────┐
►─┴─┬──────────────────────────────┬┴───────────────────────────────────┤
├─ <1> ─ : ─ <access control> ─┤
├─ <1> ─ : ─ <owner override> ─┤
├─ <1> ─ : ─ OVERRIDE ─────────┤
└─ <1> ─ : ─ GRANTALL ─────────┘<database title>
──┬──────────────────────┬─<database name>─┬───────────────────┬───────┤ ├─ ( ──<usercode>── ) ─┤ └─ ON ──<pack name>─┘ └─ * ──────────────────┘
── <logical database name> OF ─┬─ <database name> ──┬──────────────────┤
└─ <database title> ─┘<access control>
── ACCESSCONTROL ── = ─┬─ INQUIRYONLY ─┬─┬───────────────┬────────────►
└─ UPDATEOK ────┘ └─ ,STATISTICS ─┘
►─┬───────────────┬────────────────────────────────────────────────────┤
└─ ,LOCKEDFILE ─┘<owner override>
── OWNER ── = ─── <owner usercode> ───────────────────────────────────┤
Explanation
|
Syntax Element |
Description |
||
|---|---|---|---|
|
SQLVIEW DATABASE or SQLVIEW LOGICAL DATABASE |
Designates the Schema Administrator SQLVIEW command. |
||
|
<database title> |
Describes the name and location of the Enterprise Database Server database for which you want an SQL mapping. If you do not specify a usercode and pack name, standard file search rules are applied. |
||
|
Describes the name and location of the Enterprise Database Server logical database for which you want an SQL mapping. If you do not specify a usercode or a pack name, standard file-search rules are applied. |
|||
|
<access control> |
Defines the degree of access to the database using the SQL mappings. |
||
|
<owner override> |
Defines the owner of the SQLVIEW database. The owner is the only usercode that has initial permissions to access the database using MCPSQL. The owner must then grant permissions to other users before they are able to access data. If you do not specify a usercode, the SQLVIEW owner defaults to the usercode of the user running the Schema Administrator SQLVIEW command. For more information on the SQLVIEW database owner, see Permissions—Assigning Access Rights to Database Structures. |
||
|
<database name> |
Specifies the name of the Enterprise Database Server database for which you want an SQL mapping. |
||
|
<usercode> |
Indicates the usercode under which the Enterprise Database Server control file is stored. |
||
|
* |
Indicates that the Enterprise Database Server control file is stored without a usercode. |
||
|
<pack name> |
Indicates the pack name on which the Enterprise Database Server control file is stored. |
||
|
ACCESSCONTROL |
Defines the degree of access to the database. ACCESSCONTROL can be defined as either INQUIRYONLY or UPDATEOK. The default is INQUIRYONLY. |
||
|
INQUIRYONLY |
Allows users to retrieve information from the database but not to modify information in the database. |
||
|
UPDATEOK |
Allows users to retrieve and modify information in the database. |
||
|
STATISTICS |
Computes population statistics for all structures for use in query optimization. During a SQLVIEW, if the STATISTICS option is included in the SQLVIEW command, MCPSQL accumulates population information for each table. If the RECORDCOUNT option is set for the Enterprise Database Server database, MCPSQL uses the population information already maintained by the Enterprise Database Server. Conversely, if RECORDCOUNT option is not set, MCPSQL acquires population information by counting and saving the number of records in every table during a SQLVIEW. The Enterprise Database Server continues to acquire and maintain population information for every table during a SQLVIEW, however, setting the RECORDCOUNT option provides the most accurate results. If the STATISTICS option is set, the population information can be used to optimize queries that merge data from multiple datasets; this can be useful when indexes (SETs) can not be used for optimization. Though the STATISTICS option with the RECORDCOUNT option set provides the most accurate population count, the count acquired using only the STATISTICS option is still useful to optimize queries when SETs can not be used, as only a count estimation is required.
For more information on the RECORDCOUNT option, see the Enterprise Database Server for ClearPath MCP Data and Structure Definition Language (DASDL) Programming Reference Manual. |
||
|
LOCKEDFILE |
Applies structure lockout while the population is being computed. |
||
|
OVERRIDE |
Allows the SQLVIEW to run without requiring operator intervention. |
||
|
GRANTALL |
Performs the following permissions command: GRANT ALL ON * TO PUBLIC This command allows any user to query or update the database. Only the owner can grant or revoke further permissions. |
Examples Using the Command Interface
You initiate the Schema Administrator SQLVIEW command by using either a CANDE RUN statement or a WFL job. The following text provides examples of each method.
Using a CANDE RUN Statement
The following example shows how to run the Schema Administrator using a CANDE RUN statement. The name of the Enterprise Database Server database is ORGDB; it is located on the pack DBPACK. Access control is defined as UPDATEOK. The example also names a CARD file and a DASDL file. The DASDL file is file-equated to the title of the description file.
RUN *SYSTEM/MCPSQL/ADMIN
("SQLVIEW DATABASE ORGDB ON DBPACK:
ACCESSCONTROL = UPDATEOK");
FILE CARD = SCHEMA/SECURE/ORGDB;
FILE DASDL = DESCRIPTION/ORGDB ON DESCPACK;
FILE DDLRESULTS = RESULT/DMSVIEW/ORGDBUsing a WFL Job
The following example shows how to run the Schema Administrator using a WFL job. In this example, the database ORGDB under the usercode SAMPLE is being viewed. Because the ACCESSCONTROL syntax element is omitted, the default value is INQUIRYONLY; users querying the viewed database have inquiry access only. Users cannot update the database. This example names the file SCHEMA/SECURE/ORGDB as the CARD file.
BEGIN JOB SQLVIEW/ORGDB;
TASK T;RUN *SYSTEM/MCPSQL/ADMIN
("SQLVIEW DATABASE (SAMPLE)ORGDB") [T];
FILE CARD (TITLE = SCHEMA/SECURE/ORGDB);
IF T(VALUE) LSS 0 THEN
ABORT "Errors detected";
END JOB