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

How to Submit SQL Statements

To submit SQL statements in the Interactive SQL Service Facility:

1. Place your SQL statements in the variable-line area of the Source Panel (examples are shown in the following sections). See Basic Language Elements for information on how to write SQL statements.
   • You can specify a name for the source member that is created when you execute your statements, or use a default source member.
   • You must place a semicolon after each complete SQL statement you enter.
   • You can use line commands to insert, delete, copy and move lines in the Source Panel.

   The source member is saved by CA Datacom Datadictionary in a Virtual Library System (VLS) member. The name of the VLS library is specified in the System Resource Table parameter DDOLSQL= (in the DDSYSTBL macro). See the CA Datacom/DB Database and System Administration Guide.

   You can optionally display, copy, modify, execute, and delete your source members through menu and panel selections or command line commands.

   Note: You can put comment lines in your source member. Two dashes (--) indicate that the line is a comment. These two dashes must be in column one and two on the input line. Comment lines are included in the final tally of records within the member using the statement "number of records read is...," but they are not regarded as SQL statements. Blank lines can be used as separator lines. As with comment lines, blank lines are counted in the final record count but ignored as statements.

2. Submit the source member for processing with the EXECUTE command (PF9 key).
   • The Interactive SQL Service Facility prepares and executes the statements.
   • If successful, the CA Datacom Datadictionary is updated with the appropriate definitions in PROD status. Table objects are cataloged to the Directory and are ready to populate with data.
   • If multiple SQL commands are entered in a single source member and an SQL processing error (indicated by an SQL return code in the format -nnn) is encountered, a ROLLBACK WORK command is issued by the Interactive SQL Service Facility.

   Note: A COMMIT WORK command is implied, in CICS, by a transaction boundary (that is to say, from EXECUTE to display of results is a single transaction). A ROLLBACK WORK is implied, in all environments, by a negative SQL return code (in the format -nnn) on any SQL statement in the member being executed. The COMMIT WORK and ROLLBACK WORK commands can be placed in a source member to be executed, if necessary.

3. CA Datacom Datadictionary places the results of processing the source member in an output member which is displayed on an Output Panel. The output member is saved in the same Virtual Library System (VLS) member as the source member.
   • The Output Panel indicates the status of the statement execution.
   • If the execution is not successful, CA Datacom Datadictionary displays return codes on this panel. Return codes for SQL and other related CA Datacom products are listed in the CA Datacom/DB Message Reference Guide.
   • You can display and delete the output members.