Use option 1 (EDIT) on the SQLMAINT Panel to create, modify, or execute a member containing SQL statements. CA Datacom Datadictionary displays a SQL Member Selection Criteria Fill-in Panel as in the following example: The panel displays the EDIT option you requested on the left side of the panel, and provides fields for you to enter the names of the source and output members and a description.
You can also use the EDT SQL command in the command area in the Interactive SQL Service Facility. For details about entering this command, see EDIT SQL Command.
=> => => ------------------------------------------------------------------------------- Interactive SQL Service Facility SQLMAINT SQL Member Selection Criteria Fill-in S01F EDIT SQL / ________ , ________ , ________________________________ / EDT (source) (output) (description) (name ) (name ) NOTE: If no source/output member is entered the default is $DDSQL. PF1=HELP PF2=END PF3=SPLIT PF4=PROCESS PF5=TOP PF6=BOTTOM PF7=BACKWARD PF8=FORWARD PF9=EXECUTE PF10=LEFT PF11=RIGHT PF12=ALTERNATE
Complete your specifications on the SQL Member Selection Criteria Fill-in Panel according to the following descriptions:
(Optional) Enter the name of the member that contains the SQL statements. Use alphanumeric characters in the name. It cannot contain special characters or embedded blanks.
When the member name you enter already exists for the current user, the next panel displays the existing source member for modification. If the member does not exist, a new member is created. If you leave this field blank, the default name is used.
1 to 8 characters
$DDSQL
(Optional) Enter the name of the member that contains the results of executing the source member. Use alphanumeric characters in the name. It cannot contain special characters or embedded blanks.
For identification convenience, use the same name for both the source and output members. If the output member name you enter already exists, CA Datacom Datadictionary replaces the previous output member upon execution. If you leave this field blank, the default name is used.
1 to 8 characters
$DDSQL
(Optional) You can enter up to 32 characters and embedded blanks to describe your source members. The same description is displayed with the output member.
1 to 32 characters
(No default)
Source Panel
After you complete the SQL Member Selection Criteria Fill-in Panel, and press Enter, CA Datacom Datadictionary displays the Source Panel.
=> => => ------------------------------------------------------------------------------- Interactive SQL Service Facility SQLMAINT Source Panel S01S EDIT Member: $DDSQL Output Line Limit: 01000 Person: JONES Current Authid: JONES Description: CREATE TABLE DEPTTBL ------------------------------------------------------------------------------- ====== ========================== T O P ======================================= 000001 ====== ======================= B O T T O M ==================================== PF1=HELP PF2=END PF3=SPLIT PF4=PROCESS PF5=TOP PF6=BOTTOM PF7=BACKWARD PF8=FORWARD PF9=EXECUTE PF10=LEFT PF11=RIGHT PF12=ALTERNATE
Information you provided on the SQL Member Selection Criteria Fill-in Panel displays in the top portion of this panel. In this example, a CREATE TABLE SQL statement is being executed. See the following explanation of the fields that appear on this panel.
The source member name you entered on the SQL Member Selection Criteria Fill-in Panel or the default member name $DDSQL.
(Optional) The maximum number of lines you receive on the output panel. Even though a portion of the output member information is displayed, the entire statement is executed.
If you want the output to display more than 1000 lines, we recommend that you enter the SQL statements in an application program, or see the person responsible for administering CA Datacom/DB at your site.
Up to 1000
1000
The PERSON occurrence associated with the user ID entered on the CA Datacom Datadictionary Signon Panel.
One of the following is displayed:
The description you entered on the SQL Member Selection Criteria Fill-in Panel.
If this is a new source member, you receive one numbered blank line where you enter your SQL statement(s). See the instructions in Submitting SQL Statements to enter the SQL statements you can execute in the Interactive SQL Service Facility. End each statement with a semicolon.
Use line commands to insert the number of text lines needed to enter the statement(s). See Using Line Commands.
After completing the statement, press Enter. CA Datacom Datadictionary displays the SQL statement as it was entered.
=> => => ------------------------------------------------------------------------------- Interactive SQL Service Facility SQLMAINT Source Panel S01S EDIT Member: $DDSQL Output Line Limit: 01000 Person: JONES Current Authid: JONES Description: CREATE TABLE DEPTTBL ------------------------------------------------------------------------------- ====== ========================== T O P ======================================= 000001 CREATE TABLE DEPTTBL 000002 (DEPTNO CHAR(2) NOT NULL, 000003 DEPTNAME CHAR(24) NOT NULL); ====== ======================= B O T T O M ==================================== PF1=HELP PF2=END PF3=SPLIT PF4=PROCESS PF5=TOP PF6=BOTTOM PF7=BACKWARD PF8=FORWARD PF9=EXECUTE PF10=LEFT PF11=RIGHT PF12=ALTERNATE
At this point in processing, you can perform one of the following functions:
Output Panel
When you execute the statement, you receive the Output Panel. The following example is output for the CREATE TABLE statement for the DEPTTBL table.
=> => => --------------------------------------------------------------------------- >>> Interactive SQL Service Facility SQLMAINT Output Panel S01O EDIT Member= $DDSQL Description: CREATE A SCHEMA ------------------------------------------------------------------------------- ====== ========================== T O P ======================================= 000001 (OUTPUT CREATED FROM SOURCE MEMBER $DDSQL ) 000002 CREATE TABLE DEPTTBL 000003 (DEPTNO CHAR(2) NOT NULL, 000004 DEPTNAME CHAR(24) NOT NULL); 000005 --------+--------+--------+--------+--------+--------+--------+--------+ 000006 STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 000007 --------+--------+--------+--------+--------+--------+--------+--------+ 000008 SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72 000009 NUMBER OF INPUT RECORDS READ IS 0003 PF1=HELP PF2=END PF3=SPLIT PF4=PROCESS PF5=TOP PF6=BOTTOM PF7=BACKWARD PF8=FORWARD PF9=EXECUTE PF10=LEFT PF11=RIGHT PF12=ALTERNATE
When the display is longer than the screen, you can use the scroll commands or PF keys to display the additional information. Another example panel showing an error message and return code follows the description of the fields on this panel.
Note: Certain SQL statements, such as SELECT, return information that extends beyond the right column of a display. Use the scroll commands or PF keys to display this information. However, the returned data is truncated when the information on a line exceeds 228 bytes. The source and output members are stored in the Virtual Library System (VLS) and the VLS has a record limit of 240 bytes, of which 12 bytes are used internally.
The top portion of the panel displays the member name and description you entered on the Source Panel. For this example, the text portion of the panel displays the contents of the output member the following segments.
Line numbers referenced are only valid as illustration for this example, the output varies with each SQL statement you execute.
Line 1
The source member that created the output is displayed.
Lines 2-4
The SQL statement(s) as entered and the result(s) of the executing statement(s) are displayed.
Line 6
A message stating the success or failure of the executed statement is displayed. An SQLCODE of 0 indicates the statement execution was successful. When you execute a SELECT statement, an SQLCODE of 100 also indicates successful execution. SQL return codes are listed in the CA Datacom/DB Message Reference Guide.
Remaining Lines
Additional support information is included: the number of columns CA Datacom Datadictionary uses to process the SQL statements, the number of input records read, the number of SQL statements processed.
If the returned data exceeds the 228-byte per line limit, the support information includes: RECORD LENGTH EXCEEDS MAXIMUM, DATA MAY BE TRUNCATED.
Error Messages Displayed
When an error in processing is encountered, more information is displayed containing the appropriate return codes, and whether the error was encountered in processing CA Datacom Datadictionary or CA Datacom/DB.
=> => => --------------------------------------------------------------------------- >>> Interactive SQL Service Facility SQLMAINT Output Panel S01O EDIT Member= CSCHEMA Description: CREATE A SCHEMA ------------------------------------------------------------------------------- ====== ========================== T O P ======================================= 000001 (OUTPUT CREATED FROM SOURCE MEMBER CSCHEMA ) 000002 CREATE SCHEMA AUTHORIZATION JONES; 000003 ---------+---------+---------+---------+---------+---------+---------+-- 000004 ERROR OCCURRED EXECUTING A PLAN -0118(MAAE) (QEXEI) 000005 ---------+---------+---------+---------+---------+---------+---------+-- ====== ======================= B O T T O M ==================================== PF1=HELP PF2=END PF3=SPLIT PF4=PROCESS PF5=TOP PF6=BOTTOM PF7=BACKWARD PF8=FORWARD PF9=EXECUTE PF10=LEFT PF11=RIGHT PF12=ALTERNATE
In this example, the -0118 SQL return code indicates that an error was detected by the CA Datacom Datadictionary Service Facility (DSF) while attempting to process the statement. The MAAE return code indicates with M that the return code is from a module of CA Datacom Datadictionary and with AAE (a DSF return code) that the AUTHORIZATION occurrence already exists in CA Datacom Datadictionary.
Error messages appear in the following general format:
Indicates an error was encountered during execution of the statement.
where:
Indicates where the error occurred. In the previous example, an error occurred executing the plan.
The nnnnn string indicates that an error occurred during CA Datacom/DB or CA Datacom Datadictionary execution. The -0118 in the previous example, indicates an error occurred in executing CA Datacom Datadictionary. A return code of -0117 indicates an error occurred in executing CA Datacom/DB.
The (xyyy) represents the return codes. The x indicates the type of CA Datacom Datadictionary return code and the yyy represents the CA Datacom Datadictionary Service Facility (DSF) return code.
See SQL Error Handling for information.
These characters represent an internal SQL code.
Return codes for SQL and other related CA Datacom products are listed in the CA Datacom/DB Message Reference Guide.
The output member can be up to 228 columns wide. To view the columns beyond the displayable screen area, use the SCROLL commands or PF11 to scroll right and PF10 to scroll left. See SCROLL Command for more instructions.
To exit from the Output Panel, you can do one of the following:
You can execute the statements in the source member again, or use the member to create other source members. Output members can be displayed at a later time or deleted. See the following sections for displaying, copying, and deleting source members, and for displaying and deleting output members.
|
Copyright © 2014 CA.
All rights reserved.
|
|