How to Use

Using the Interactive SQL Service Facility › Submitting SQL Statements › How to Use

How to Use

Use the following steps to submit your SQL statements.

Step 1

When you sign on or select the SET MODE function, CA Datacom Datadictionary displays the Datadictionary Mode Select Panel. Select Option 7 or enter the SET MODE SQL command.

 =>
 =>
 =>
 
 -------------------------------------------------------------------------------
 Datadictionary Mode Select
                                                                            P00M
 Enter desired option number ===> __    (There are 09 options on the menu)
 
  1. DBMAINT   (SET MODE DBM)     DATACOM/DB Structure Maintenance
  2. ENTMAINT  (SET MODE ENTM)    Datadictionary Entity Maintenance
  3. ENTDISPL  (SET MODE ENTD)    Datadictionary Entity Display
  4. AUTHORIZE (SET MODE AUTH)    Datadictionary Authorization Maintenance
  5. FILEMAINT (SET MODE FMM)     File Structure Maintenance
  6. ISF       (SET MODE ISF)     Interactive Service Facility
  7. SQL       (SET MODE SQL)     Interactive SQL Service Facility
  8. IDEAL     (IDEAL)            Transfer to IDEAL application
  9. OFF       (OFF)              End session

Note: If CA Ideal is not installed on your system, the OFF option appears in place of the CA Ideal option on the previous panel.

Also note that when you access the Interactive SQL Service Facility, CA Datacom Datadictionary checks for the existence of the relationship between your PERSON occurrence and a valid SQL AUTHORIZATION occurrence.If a relationship does not exist, CA Datacom Datadictionary presents the Default Authorization Panel. When the relationship exists, the Default Authorization Panel is not presented.

Step 2

The Interactive SQL Service Facility Panel appears after you select this mode. Select Option 1 (SQLMAINT).

=> => => ------------------------------------------------------------------------------- Interactive SQL Service Facility Enter desired option number ===> __ (There are 05 options on the menu) 1. SQLMAINT SQL member maintenance/execution 2. SQLADMIN SQL administrative functions 3. SET MODE Reset Datadictionary processing mode 4. IDEAL Transfer to IDEAL application 5. OFF End session

Note: f CA Ideal is not installed on your system, the OFF option appears in place of the CA Ideal option on the previous panel.

Step 3

CA Datacom Datadictionary displays the SQLMAINT menu panel. Select Option 1 or enter the EDIT SQL command.

=> => => ------------------------------------------------------------------------------- Interactive SQL Service Facility SQLMAINT Enter desired option number ===> __ (There are 05 options on the menu) 1. EDIT Edit/Execute SQL members 2. DISPLAY Display source/output members 3. DELETE Delete source/output members 4. COPY Copy source members 5. END End SQLMAINT processing

Step 4

CA Datacom Datadictionary displays the SQL Member Selection Criteria Fill-in Panel for the selected function. After reading the following list, complete your entries on the panel and press Enter:

 =>
 =>
 =>
 
 -------------------------------------------------------------------------------
 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

source name

(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.

If the member does not exist, a new member is created.
If the member already exists in the list of members created by the current user, the next panel displays the existing source member for modification.
If you leave this field blank, the default name is used.

Valid Entries:: 1- to 8-character member name
Default Value:: $DDSQL

output name

(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 convenience of identification, use the same name for both the source and output members.

If the member already exists in the list of members created by the current user, CA Datacom Datadictionary replaces the previous output member upon execution.
If you leave this field blank, the default name is used.

Valid Entries:: 1- to 8-character member name
Default Value:: $DDSQL

description

(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.

Note: We recommend that you do not use a slash (/) as part of the description's text. The member cannot be retrieved with an EDT margin command if the description contains a slash.

Valid Entries:: 1 to 32 characters
Default Value:: (No default)

Step 5

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:
 -------------------------------------------------------------------------------
 ====== ========================== 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

Member:

The source member name you entered on the SQL Member Selection Criteria Fill-in Panel or the default member name $DDSQL.

Output Line Limit

The maximum number of lines you receive on the output panel. You can move the cursor to this field and change this limit. If your expected output exceeds 1000 lines, you should consider embedding your SQL statement(s) in a host program.

Person:

The PERSON occurrence name associated with the user ID entered on the CA Datacom Datadictionary Sign-on Panel.

Current Authid:

One of the following is displayed:

The authorization ID established for the session with the SET AUTHID function issued with the command or the function in the SQL Administrative option.
The default authorization ID related to the user ID.

Description:

The description you entered on the SQL Member Selection Criteria Fill-in Panel.

numbered line(s)

Use this area for your SQL statements.

If you select an existing source member, the contents of the source member are displayed for you to modify or execute.
If this is a new source member, you receive one numbered blank line. If the statement can be entered on one line, it is not necessary to insert additional lines on the panel.
You can use line commands such as I (insert), R (repeat), or D (delete), to add and delete lines. See Using Line Commands for more information.
CA Datacom Datadictionary allows you to enter your statements in mixed-case letters. If you enter mixed-case letters on a Source Panel, the Output Panel also contains mixed-case letters. However, lowercase letters in an ordinary token are folded to uppercase by the SQL Preprocessor. Lowercase letters in a delimiter token remain lowercase.
You must enter a semicolon after each complete SQL statement.

After placing your SQL statement or statements in the numbered line area on the Source Panel, you can perform the following:

Press PF9 to execute the source member. CA Datacom Datadictionary responds with the Output Panel which shows the results of the SQL processing.
Press PF2 (END) to return to the SQLMAINT menu panel where you can select a function. If you have refreshed the panel by pressing ENTER or a PF key, your entries are saved in the Source Member.
Press the CLEAR key to return to the Interactive SQL Service Facility Panel where you can choose another option. If you have refreshed the panel by pressing ENTER or a PF key, your entries are saved in the Source Member.

Step 6

When you execute the source member, you receive a Output Panel. The following example shows the Output Panel displayed after a CREATE TABLE statement was executed.

 =>
 =>
 =>
 
 --------------------------------------------------------------------------- >>>
 Interactive SQL Service Facility                                       SQLMAINT
 
                                 Output Panel
              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

To exit from the Output Panel, you can perform one of the following.

Press PF2 (END) to obtain an SQLMAINT Panel where you can select another option.
Press PF12 to display the Source Panel.
Press the CLEAR key to return to the Interactive SQL Service Facility Panel.