HP NonStop SQL/MP Programming Manual for COBOL85 Abstract This manual documents the programming interface to HP NonStop™ SQL/MP for COBOL85 and NMCOBOL. It is intended for application programmers who are embedding SQL statements and directives in COBOL and NMCOBOL programs. Product Version NonStop SQL/MP G06 Supported Release Version Updates (RVUs) This publication supports D46.00 and all subsequent D-series RVUs and G06.
Document History Part Number Product Version Published 427072-001 NonStop SQL/MP G06 November 2000 429326-001 NonStop SQL/MP G06 March 2001 429326-002 NonStop SQL/MP G06 May 2003 429326-003 NonStop SQL/MP G06 December 2003 429326-004 NonStop SQL/MP G06 December 2004
HP NonStop SQL/MP Programming Manual for COBOL85 Index Examples What’s New in This Manual xi Manual Information xi New and Changed Information About This Manual xiii Who Should Read This Guide Related Manuals xiii Notation Conventions xvi Figures Tables xi xiii 1.
2. Host Variables (continued) Contents 2.
4. Data Retrieval and Modification (continued) Contents 4.
5. SQL/MP System Procedures (continued) Contents 5. SQL/MP System Procedures (continued) SQLCATOBUFFER 5-34 6.
. Program Execution Contents 7. Program Execution Required Access Authority 7-1 Using DEFINEs 7-1 Entering the TACL RUN Command 7-2 Using the CREATEPROCESS Routine 7-3 Running a Program in the OSS Environment 7-4 Running a Program at a Low PIN on a D-Series Node 7-4 Interactive Commands 7-5 Programmatic Commands 7-5 Pathway Environment 7-6 Determining Compatibility With the SQL Executor 7-6 8.
10. Dynamic SQL Operations (continued) Contents 10.
11. Character Processing Rules (CPRL) Procedures (continued) Contents 11. Character Processing Rules (CPRL) Procedures (continued) CPRL_READOBJECT_ 11-21 CPRL_UPSHIFT_ 11-22 A. SQL/MP Sample Database B. Memory Considerations SQL/MP Internal Structures B-1 Resizing Segments B-1 Avoiding Name Conflicts B-2 Using the SQLMEM Directive B-4 Estimating Memory Requirements B-5 Memory Requirements B-5 Guidelines for Memory Use B-6 C.
Index Contents Index Examples Example 1-1. Example 1-2. Example 2-1. Example 2-2. Example 2-3. Example 2-4. Example 4-1. Example 4-2. Example 4-3. Example 5-1. Example 5-2. Example 5-3. Example 5-4. Example 6-1. Example 7-1. Example 9-1. Example 9-2. Example 9-3. Example 10-1. Example 10-2. Example 10-3. Example 10-4. Example A-1. Example D-1. Example D-2. Example E-1. Example E-2. Example E-3. Example E-4.
Figures (continued) Contents Figures (continued) Figure 6-1. Figure 6-2. Figure 8-1. Figure 10-1. Figure 10-2. Figure A-1. Compiling a COBOL Program 6-2 SQL Program File Format 6-35 Timestamp Check 8-8 Static and Dynamic SQL Programs 10-2 Names Buffer Structure 10-24 SQL/MP Sample Database Relations A-2 Tables Table 1. Table 2. Table 3. Table 4. Table 1-1. Table 2-1. Table 3-1. Table 3-2. Table 4-1. Table 5-1. Table 5-2. Table 6-1. Table 6-2. Table 6-3. Table 9-1. Table 9-2. Table 10-1. Table 10-2.
Contents HP NonStop SQL/MP Programming Manual for COBOL85 —429326-004 x
What’s New in This Manual Manual Information HP NonStop SQL/MP Programming Manual for COBOL85 Abstract This manual documents the programming interface to HP NonStop™ SQL/MP for COBOL85 and NMCOBOL. It is intended for application programmers who are embedding SQL statements and directives in COBOL and NMCOBOL programs. Product Version NonStop SQL/MP G06 Supported Release Version Updates (RVUs) This publication supports D46.00 and all subsequent D-series RVUs and G06.
What’s New in This Manual New and Changed Information HP NonStop SQL/MP Programming Manual for COBOL85 —429326-004 xii
About This Manual This manual describes the NonStop SQL/MP programmatic interface for HP COBOL for NonStop systems. Using this interface, a COBOL program can access a NonStop SQL/MP database by using embedded SQL statements and directives. Who Should Read This Guide This manual is intended for application programmers who are embedding SQL/MP statements and directives in a COBOL program.
Related Manuals About This Manual Table 1. NonStop SQL/MP Library (page 2 of 2) SQL/MP Installation and Management Guide Describes how to plan, install, create, and manage a NonStop SQL/MP database and SQL programs. SQL/MP Report Writer Guide Describes how to use report writer commands and SQLCI options to design and produce reports. SQL/MP Programming Manual for C Describes the NonStop SQL/MP programmatic interface for C, COBOL, Pascal, and TAL applications.
Related Manuals About This Manual In addition to the NonStop SQL/MP library, program development, Guardian, and HP NonStop Open System Services (OSS) manuals described in these tables can be useful to a COBOL programmer. Table 2. Program Development Manuals Manual Description COBOL85 for NonStop Systems Manual Describes the HP implementation of COBOL, including the statement syntax, run-time library, program execution environment, HP extensions, and how to use HP COBOL.
Notation Conventions About This Manual Table 3. Guardian Manuals Manual Description Guardian Application Conversion Guide Describes how to convert C, COBOL, Pascal, TAL, and TACL applications to use the extended features of the NonStop OS. Guardian Procedure Calls Reference Manual Describes the syntax for Guardian procedure calls. Guardian Programmer’s Guide Describes how to use Guardian procedure calls from an application to access operating system services.
General Syntax Notation About This Manual italic computer type. Italic computer type letters within text indicate C and Open System Services (OSS) variable items that you supply. Items not enclosed in brackets are required. For example: pathname [ ] Brackets. Brackets enclose optional syntax items. For example: TERM [\system-name.]$terminal-name INT[ERRUPTS] A group of items enclosed in brackets is a list from which you can choose one item or none.
General Syntax Notation About This Manual Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example: "[" repetition-constant-list "]" Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: CALL STEPMOM ( process-id ) ; If there is no space between two items, spaces are not permitted.
1 Introduction NonStop SQL/MP is the HP relational database management system (RDBMS) that uses SQL to define and manipulate data in an SQL/MP database. You can run SQL statements interactively by using the SQL/MP conversational interface (SQLCI) or programmatically by embedding SQL statements and directives in a host-language program written in COBOL, C, Pascal, or TAL. This manual describes the programmatic interface to SQL/MP for COBOL programs.
Host Variables Introduction You declare host variables in a Declare Section in the Data Division. A Declare Section is delimited by the BEGIN DECLARE SECTION and END DECLARE SECTION directives. In this example, FILENUMBER and MESSAGE are host variables: DATA DIVISION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 FILENUMBER PIC 9(6) DISPLAY. 01 MESSAGE PIC X(200). ... EXEC SQL END DECLARE SECTION END-EXEC.
SQL/MP Statements and Directives Introduction SQL/MP Statements and Directives Table 1-1 lists the SQL/MP statements and directives you can embed in a COBOL program. Table 1-1.
SQL/MP System Procedures Introduction You code an SQL statement or directive by preceding it with EXEC SQL and then terminating it with END-EXEC. Example 1-1 shows static SQL statements embedded in a COBOL program: Example 1-1. Static SQL Statements in a COBOL Program DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 IN-PARTS-REC. 02 IN-PARTNUM PIC 9(4) COMP. 02 IN-PRICE PIC S9(8)V99 COMP. 02 IN-PARTDESC PIC X(18). EXEC SQL END DECLARE SECTION END-EXEC. ...
Error and Status Reporting Introduction contain embedded SQL statements. You must perform only one extra step for an SQL program: you compile the embedded SQL statement by using the SQL compiler. 1. Add any required class MAP or class CATALOG DEFINEs (you can use class MAP DEFINEs to specify SQL objects—tables, views, indexes, and collations—and class CATALOG DEFINEs to specify SQL catalogs). 2. Run the COBOL85 or NMCOBOL compiler, specifying a source file containing embedded SQL statements as input.
Dynamic SQL Operations Introduction Dynamic SQL Operations With static SQL operations, you code the actual SQL statement in the COBOL source file. However, with dynamic SQL statements, a program can construct, compile, and execute an SQL statement at run time. You code a host variable as a placeholder for the dynamic SQL statement, which is usually unknown or incomplete until run time. A dynamic SQL statement requires some input, often from a user at a terminal, to construct the final statement.
SQL/MP Version Management Introduction SQL/MP Version Management Each product version update (PVU) of SQL/MP has an associated version number. The initial PVUs were version 1 (C10 and C20) and version 2 (C30). Version 300 of SQL/MP began using a three-digit version number to allow for software product revision (SPRs). A new version number is always greater than the previous number, but the new number might not follow a constant increment.
Introduction Effect on Conformance to ISO/ANSI Standards invoke the COBOL85 compiler, Binder program, Accelerator, and SQL compiler. (The cobol utility is described in Section 6, Explicit Program Compilation) Most features of the COBOL language and library are available in the OSS environment, and most of them operate as they do in the Guardian environment. Differences in the two environments as they relate to the COBOL interface to SQL/MP are discussed throughout this manual.
2 Host Variables A host variable is a data item you can use in both COBOL and NonStop SQL/MP statements to provide communication between these two types of statements. A host variable appears as a COBOL name and can be any COBOL data item declared in a Declare Section that has a corresponding SQL data type as shown in Table 2-1, Corresponding SQL and COBOL Data Types, on page 2-2. For static SQL operations, a host variable can be an input or output variable (or both in some cases) in SQL statements.
Coding Host Variable Names Host Variables Coding Host Variable Names Use COBOL naming conventions for host variable names. A COBOL name can contain from 1 to 30 alphanumeric characters, including letters, digits, and hyphens (-). The first or last letter cannot be a hyphen. Letters can be uppercase, lowercase, or a combination of both. HP COBOL names must contain at least one letter or hyphen.
Using Corresponding SQL and COBOL Data Types Host Variables Table 2-1. Corresponding SQL and COBOL Data Types (page 2 of 3) SQL/MP Data Type COBOL Data Type Fixed-Length Character Data Type With NATIONAL CHARACTER Clause NATIONAL CHARACTER (l) 01 column-name CHARACTER SET def-charset PIC X(l). Variable-Length Character Data Type VARCHAR(l) 02 column-name. 03 LEN PIC S9(4) COMP. 03 VAL PIC X(l).
Using Corresponding SQL and COBOL Data Types Host Variables Table 2-1. Corresponding SQL and COBOL Data Types (page 3 of 3) SQL/MP Data Type COBOL Data Type Numeric Data Types PIC 9(l-s)V9(s) Same as DECIMAL. SMALLINT SIGNED PIC S9(4) COMP. SMALLINT UNSIGNED PIC INTEGER SIGNED PIC S9(9) COMP. INTEGER UNSIGNED PIC LARGEINT SIGNED PIC S9(18) COMP. FLOAT (1 to 22 bits) Not supported. REAL Not supported. FLOAT (23 to 54 bits) Not supported. DOUBLE PRECISION Not supported. 9(4) COMP.
Using Corresponding SQL and COBOL Data Types Host Variables Data Conversion SQL/MP performs the conversion between SQL and COBOL data types as follows: • • When a host variable serves as an input variable (supplies a value to the database), SQL/MP first converts the value that the variable contains to a compatible SQL data type and then uses the value in the SQL operation.
Host Variables Specifying Host Variables in SQL Statements Specifying Host Variables in SQL Statements Use this syntax to specify a host variable in an SQL statement. You must precede the host variable name with a colon (:). The colon causes the COBOL85 compiler to handle the name as a host variable.
Using the COBOL PICTURE Clause Host Variables Using the COBOL PICTURE Clause If you use the PICTURE clause to declare COBOL record descriptions as host variables, the clause must conform to both COBOL syntax rules and SQL/MP limitations. Fixed-Length Character Data Use the PICTURE clause to declare a host variable for fixed-length character data (CHAR data type) as follows: PICTURE X (length) [ USAGE IS DISPLAY ] The length value must be a positive integer and not greater than 4096.
Numeric Data Host Variables Numeric Data Use the PICTURE clause to declare a host variable for numeric data (NUMERIC, DECIMAL, SMALLINT, LARGEINT, and INTEGER data types) as follows: PICTURE [S] { 9(integer) [ V [ 9(scale) ] ] } { V9(scale) } [ [ USAGE IS ] [ [ [ { { { { DISPLAY COMPUTATIONAL COMP BINARY } } } } ] ] ] ] If you specify COMPUTATIONAL, COMP, or BINARY, the value is stored as a binary integer with an implied decimal point.
Using Date-Time and INTERVAL Data Types Host Variables Using Date-Time and INTERVAL Data Types COBOL Description SQL/MP Host Variable Interpretation BLANK The clause is ignored. data-name Any data name is allowed, including an SQL reserved word. Specific hyphenation rules apply. FILLER The clause is ignored. JUSTIFIED The clause is not allowed; however, it can appear in an entry already being ignored, such as REDEFINES. level number Any number is allowed.
Using Indicator Variables for Null Values Host Variables • • • • • TYPE AS DATETIME YEAR TO HOUR TYPE AS DATE TYPE AS TIME TYPE AS TIMESTAMP TYPE AS INTERVAL YEAR You can insert or retrieve date-time values in any of three formats, independently of the SQL column definition. For example, you can specify formats such as 06/15/1996, 1996-06-15, or 15.06.1996. You must declare the host variable size to be consistent with the format you will use.
Testing for a Null Value Host Variables ... MOVE NULL-EMPNUM TO EMPNUM. MOVE -1 TO RETIRE-IND. EXEC SQL INSERT INTO =RETIREES VALUES (:EMPNUM,:RETIRE-DATE INDICATOR :RETIRE-IND) END-EXEC. ... The next example uses the NULL keyword instead of an indicator variable to insert the null value: MOVE NULL-EMPNUM TO EMPNUM. ... EXEC SQL INSERT INTO =RETIREES VALUES (:EMPNUM, NULL) END-EXEC. Testing for a Null Value To test for a null value, a program tests the indicator variable associated with a host variable.
Retrieving Rows With Null Values Host Variables END-EXEC. * NonStop SQL/MP sets SHIP-IND to -1 if the column * contained a null value in the selected row. IF SHIP-IND = -1 THEN MOVE "NULL" TO VALUE-DISPLAY ELSE MOVE DATE-SHIPPED TO VALUE-DISPLAY. IF SQLCODE = 0 DISPLAY PRODNUM " " VALUE-DISPLAY. ... Retrieving Rows With Null Values You use an indicator variable to insert null values into a database or to test for a null value after you retrieve a row.
Host Variables Creating Host Variables Using the INVOKE Directive Creating Host Variables Using the INVOKE Directive The INVOKE directive creates host variables that correspond to columns in an SQL table or view. Each host variable is a COBOL data item with the same name as the respective column in the table or view. If a column allows null values, INVOKE also generates an indicator variable for the column.
COBOL Record Descriptions Host Variables COBOL Record Descriptions The next examples show the correspondence between columns of various SQL data types and the COBOL record description generated by the INVOKE directive. Example 2-1 shows the CREATE TABLE statements that generate the SQL tables. Example 2-1. CREATE TABLE Statements (page 1 of 2) CREATE TABLE \NEWYORK.$DISK1.SQL.
COBOL Record Descriptions Host Variables Example 2-1. CREATE TABLE Statements (page 2 of 2) TYPE_COB_PICX1_NULL PIC X(10) CHARACTER SET ISO88591 TYPE_COB_PICX2 PIC X(10) CHARACTER SET KANJI , NOT NULL, TYPE_COB_PICX2_NULL PIC X(10) CHARACTER SET KANJI ) CATALOG $SQL.SQLCAT ; These INVOKE directives are coded in a COBOL source file: EXEC SQL BEGIN DECLARE SECTION; EXEC SQL INVOKE \NEWYORK.$DISK1.SQL.TYPECOB1 AS TYPES-REC1 END-EXEC. EXEC SQL INVOKE \NEWYORK.$DISK1.SQL.TYPECOB2 AS TYPES-REC2 END-EXEC.
Host Variables COBOL Record Descriptions Example 2-2. COBOL Record Descriptions Generated by the INVOKE Directive (page 2 of 2) * * Record Definition for table \NEWYORK.$DISK1.SQL..TYPECOB2 Definition current at 15:55:38 - 10/10/94 01 TYPES-REC2. 02 TYPE-CHAR1 CHARACTER SET "ISO88591" PIC X(10). 02 TYPE-CHAR1-NULL-I PIC S9(4) COMP. 02 TYPE-CHAR1-NULL CHARACTER SET "ISO88591" PIC X(10). 02 TYPE-CHAR2 CHARACTER SET "KANJI" PIC X(10). 02 TYPE-CHAR2-NULL-I PIC S9(4) COMP.
Embedded Sign in a Decimal Data Type Host Variables Column or Data Type Description of Change Underscore (_) within a name Converts underscores to hyphens (–). For example, the column name CITY_STREET becomes CITY-STREET. Underscore (_) at the end of a name Truncates the underscore so that the resulting COBOL name does not end in a hyphen. For example, the column name HOME_ becomes HOME. Column with VARCHAR data type Creates a group item with two elementary data items.
Date-Time and INTERVAL Data Types Host Variables does not generate a SYSKEY host variable. (INVOKE does not generate a column for the SYSKEY of a table because SYSKEY cannot be specified in a CREATE TABLE statement.) For example, suppose that you create a table by using this statement: CREATE TABLE TYPESTAB (COLUMN-A INT, COLUMN-B INT) INVOKE generates host variables for columns COLUMN-A and COLUMN-B but does not generate a host variable for the SYSKEY automatically associated with the table.
Date-Time and INTERVAL Data Types Host Variables Figure 2-1. DATE Representation Year 1 9 5 Separator − 2 Month 0 5 Separator − Day 2 8 VST013.vsd INVOKE generates this record description in the COBOL format: 01 EMPLOYEE 02 NAME 02 BIRTH-DATE PIC X(18) PIC X(10) VALUE SPACES. VALUE SPACES.
Date-Time and INTERVAL Data Types Host Variables Example 2-3. Creating Valid DATETIME and INTERVAL Data Types ?INSPECT ?SYMBOLS IDENTIFICATION DIVISION. PROGRAM-ID. COBEXT. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. TANDEM/16. OBJECT-COMPUTER. TANDEM/16. * DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 OUTPUT-VAL PIC X(30). 01 INPUT-VAL PIC X(30) VALUE SPACES. 01 TEMP-STMT-TEXT PIC X(200) VALUE SPACES. EXEC SQL END DECLARE SECTION END-EXEC.
Using Indicator Variables With the INVOKE Directive Host Variables Using Indicator Variables With the INVOKE Directive The INVOKE directive automatically generates a two-byte indicator variable (data type short) for each host variable that corresponds to a column that allows a null value. The name of the indicator variable is the same name as the corresponding column plus a prefix, if you specify one, and a suffix. If you do not specify a prefix or suffix, INVOKE appends the default suffix -I to the name.
Using Indicator Variables With the INVOKE Directive Host Variables INDICATOR is the indicator variable. The SQL data type of an indicator variable is SMALLINT. The corresponding COBOL data type is PIC S9(4) COMP. VALUE is the host variable corresponding to the column value. This example uses the NULL STRUCTURE clause, which causes columns that can contain null values to be declared as group items. This INVOKE directive contains a NULL STRUCTURE clause: EXEC SQL INVOKE BTABLE NULL STRUCTURE END-EXEC.
Using INVOKE With SQLCI Host Variables Example 2-4. Using Host and Indicator Variable Names EXEC SQL BEGIN DECLARE SECTION END-EXEC. EXEC SQL INVOKE ODETAIL AS ORDER-DETAIL-RECORD SUFFIX -I END-EXEC. *Record Description ***************************** *01 ORDER-DETAIL-RECORD. * 02 ORDERNUM PIC 9(6) COMP. * 02 PARTNUM PIC 9(4) COMP. * 02 UNIT-PRICE-I PIC S9(4) COMP. * 02 UNIT-PRICE PIC S9(6)V9(2) COMP. * 02 QTY-ORDERED-I PIC S9(4) COMP. * 02 QTY-ORDERED PIC 9(5) COMP. EXEC SQL END DECLARE SECTION END-EXEC. .
Associating a Character Set With a Host Variable Host Variables Using INVOKE with SQLCI provides less program independence than embedding INVOKE in your program, because you must re-create the host variable declarations if the referenced table changes. However, if necessary, you can edit the host variables before copying them into your program’s compilation unit. Associating a Character Set With a Host Variable By default, SQL/MP associates a single-byte unknown character set with a host variable.
Treatment in COBOL Statements Host Variables COBOL85-clause is a COBOL85 clause such as VALUE or USAGE. For a description of the COBOL85 clauses, see the COBOL85 for NonStop Systems Manual. Treatment in COBOL Statements A COBOL statement treats a host variable declared with the CHARACTER SET clause as if the host variable had been declared without the clause.
Host Variables VARCHAR Data Type HP NonStop SQL/MP Programming Manual for COBOL85 —429326-004 2- 26
3 SQL/MP Statements and Directives This section describes NonStop SQL/MP statements and directives you can embed in a COBOL program. For a detailed description, including the syntax, of all SQL statements and directives, see the SQL/MP Reference Manual. Embedding SQL Statements Use this syntax to embed an SQL statement or directive in a COBOL source file: EXEC SQL sql-statement-or-directive END-EXEC.
SQL/MP Statements and Directives • If you place COBOL and SQL statements on the same line, these restrictions apply: ° ° • • • • Placing SQL Statements and Directives The COBOL statement cannot be a COPY or REPLACE statement. A COBOL statement must follow the embedded SQL statement terminator; it cannot precede an embedded SQL statement on a line. Use either SQL or COBOL comments within SQL statements and directives. An SQL comment begins with a double hyphen (--) and ends with the end of the line.
Finding Information SQL/MP Statements and Directives Data Division You can use these statements and directives in the Data Division: • • • • • • BEGIN DECLARE SECTION and END DECLARE SECTION directives DECLARE CURSOR statement INVOKE directive INCLUDE STRUCTURES directive INCLUDE SQLCA, INCLUDE SQLDA, and INCLUDE SQLSA directives INCLUDE SQLCODEX directive Procedure Division You can use these statements and directives in the Procedure Division: • • • • • • Data Control Language (DCL) statements Data
Finding Information SQL/MP Statements and Directives Table 3-1. NonStop SQL/MP Statements and Directives (page 2 of 5) Statement or Directive Manual* Description INCLUDE SQLCODEX SQLRM, COBPM Enables declaring level-88 items to check for specified conditions. INCLUDE SQLDA SQLRM, COBPM Generates the SQLDA structure to receive information about input and output variables for dynamic SQL statements.
Finding Information SQL/MP Statements and Directives Table 3-1. NonStop SQL/MP Statements and Directives (page 3 of 5) Statement or Directive Manual* Description Data Definition Language (DDL) Statements HELP TEXT SQLRM Specifies help text for a column of a table or view. UPDATE STATISTICS SQLRM Updates information about the contents of a table and its indexes. Data Manipulation Language (DML) Statements CLOSE SQLRM, COBPM Terminates a cursor. DECLARE CURSOR SQLRM, COBPM Defines a cursor.
Finding Information SQL/MP Statements and Directives Table 3-1. NonStop SQL/MP Statements and Directives (page 4 of 5) Statement or Directive Manual* Description Data Control Language (DCL) Statements FREE RESOURCES SQLRM Closes cursors and releases locks held by the program. LOCK TABLE SQLRM Locks a table or underlying tables of a view and associated indexes. UNLOCK TABLE SQLRM Releases locks held on nonaudited tables and views.
Finding Information SQL/MP Statements and Directives Table 3-1. NonStop SQL/MP Statements and Directives (page 5 of 5) Statement or Directive Manual* Description BEGIN WORK SQLRM Starts a TMF transaction. COMMIT WORK SQLRM Commits all database changes made during the current TMF transaction and frees resources. ROLLBACK WORK SQLRM Backs out the current TMF transaction and frees resources.
SQL/MP Statements and Directives Finding Information HP NonStop SQL/MP Programming Manual for COBOL85 —429326-004 3 -8
4 Data Retrieval and Modification This section describes how to access data in a NonStop SQL/MP database by using the Data Manipulation Language (DML) statements in a COBOL program. Table 4-1 provides some guidelines for using these statements. Table 4-1. SQL/MP Statements for Data Retrieval and Modification NonStop SQL/MP Statement Description Single-Row SELECT statement Retrieves a single row of data from a table or protection view and places the specified column values in host variables.
Data Retrieval and Modification Opening and Closing Tables and Views Opening and Closing Tables and Views SQL/MP automatically opens and closes tables and views during the execution of DDL statements, DML statements, and SQL utility operations such as a LOAD or COPY. SQL/MP opens a table or view when a host-language program executes the first SQL statement that refers to the table or view and then closes the table or view when the program that opened it stops.
Data Retrieval and Modification • • • • Recovering From SQL Error 8204 CREATE CONSTRAINT and CREATE INDEX DROP CONSTRAINT, DROP INDEX, DROP TABLE, or DROP VIEW (protection view only) UPDATE STATISTICS COPY, LOAD, PURGEDATA, or RESTORE utility operation (A disk or network line that goes down and then comes back up can also cause the system to terminate a program’s open.) 3. The program tries to execute another SQL statement for the table or view. 4.
Single-Row SELECT Statement Data Retrieval and Modification • The program has opened the cursor, but the OPEN CURSOR statement did not require any input host variables, and the first FETCH statement has not yet been executed. However, if the problem occurs on a FETCH statement, the SQL executor closes the cursor and returns error -8204. The program must then close and reopen the cursor before executing a subsequent FETCH statement.
Data Retrieval and Modification Using a Primary Key Value to Select Data also reads the remainder of the table to verify that the row returned is the only qualifying row. If it is not, SQL/MP returns an error. Example 4-1. Using a Column Value to Select Data EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 CUSTOMER. 02 CUSTNUM PIC 9(4) DISPLAY. 02 CUSTNAME PIC X(18). 02 STREET PIC X(22). 02 CITY PIC X(14). 02 STATE PIC X(12). 02 POSTCODE PIC X(10). 01 FIND-THIS-CUSTOMER PIC 9(4) VALUE 0.
Using IN SHARE MODE or IN EXCLUSIVE MODE Data Retrieval and Modification FROM PERSNL.EMPLOYEE WHERE EMPLOYEE.EMPNUM = :FIND-THIS-EMPLOYEE END-EXEC. Using IN SHARE MODE or IN EXCLUSIVE MODE If you include the keyword IN in the EXCLUSIVE MODE or SHARE MODE option after a host variable, the statement generates a syntax error.
Data Retrieval and Modification Inserting a Single Row Inserting a Single Row This INSERT statement inserts a row (JOBCODE and JOBDESC columns) into the JOB table: EXEC SQL BEGIN DECLARE SECTION; * Declare host variables HV-JOBCODE and HV-JOBDESC. ... EXEC SQL END DECLARE SECTION; ... PROCEDURE DIVISION. ... * Move values to HV-JOBCODE and HV-JOBDESC. ... EXEC SQL INSERT INTO PERSNL.JOB (JOBCODE, JOBDESC) VALUES (:HV-JOBCODE, :HV-JOBDESC) END-EXEC. ...
Inserting a Timestamp Data Retrieval and Modification Inserting a Timestamp This example inserts a timestamp value into COLUMNA of TABLET. The COLUMNA definition specifies the data type TIMESTAMP DEFAULT CURRENT. This example uses the JULIANTIMESTAMP and CONVERTTIMESTAMP system procedures and the SQL CONVERTTIMESTAMP function. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 DATETIME PIC S9(18) COMP. EXEC SQL END DECLARE SECTION END-EXEC. PROCEDURE DIVISION. MAIN-DRIVER. ...
Data Retrieval and Modification Updating a Single Row return the contents of the SQLCA structure, use the SQLCA_DISPLAY2_ or SQLCA_TOBUFFER2_ procedure. For more information, see Section 5, SQL/MP System Procedures, and Section 9, Error and Status Reporting. Updating a Single Row This example updates a single row of the ORDERS table that contains information about the order number specified by UPDATE-ORDERNUM. In a typical application, a user enters the values for UPDATE-DATE and UPDATE-ORDERNUM.
Updating Columns With Null Values Data Retrieval and Modification specified by HOSTVAR-NEW-DEPTNUM. A user enters the values for HOSTVAROLD-DEPTNUM and HOSTVAR-NEW-DEPTNUM. EXEC SQL UPDATE PERSNL.EMPLOYEE SET DEPTNUM = :HOSTVAR-NEW-DEPTNUM WHERE DEPTNUM = :HOSTVAR-OLD-DEPTNUM END-EXEC. Updating Columns With Null Values This example updates the specified SALARY column in the EMPLOYEE table to a null value using an indicator variable. The SET-TO-NULLS host variable specifies the row to update.
Data Retrieval and Modification Deleting a Single Row After a successful DELETE operation, the SQLCA structure contains the number of rows deleted. If an error occurs on a DELETE operation, the SQLCA contains the approximate number of rows deleted. To return the contents of the SQLCA, use SQLCA_DISPLAY2_ or SQLCA_TOBUFFER2_ procedure. For more information, see Section 5, SQL/MP System Procedures, and Section 9, Error and Status Reporting.
Steps for Using a Cursor Data Retrieval and Modification Figure 4-1. Using a Static SQL Cursor in a COBOL Program 5 DATA DIVISION. ••• EXEC SQL BEGIN DECLARE SECTION END-EXEC. * Declare host variable(s). ••• EXEC SQL END DECLARE SECTION END-EXEC. ••• EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT COLUMN1, COLUMN2, COLUMN n FROM =TABLE WHERE COLUMN1 = :HOSTVAR-FIND-ROW END-EXEC. ••• PROCEDURE DIVISION. ••• * Initialize the host variable(s). MOVE INITIAL-VALUE TO HOSTVAR-FIND-ROW.
Data Retrieval and Modification Process Access ID (PAID) Requirements 4. Open the cursor by using an OPEN statement. The OPEN statement determines the result table and sorts the table if the SELECT statement includes the ORDER BY clause. For audited tables or views, the OPEN statement also associates the cursor with a TMF transaction. 5. Retrieve the column values from a row using the FETCH statement.
Cursor Position Data Retrieval and Modification Cursor Position The cursor position is similar to the record position in a sequential file. The SQL statements that affect the cursor position in a program are: SQL Statement Cursor Position or Action OPEN Positions the cursor before the first row. FETCH Positions the cursor at the retrieved row (or the current position). DELETE Positions the cursor between rows.
Data Retrieval and Modification • • DECLARE CURSOR Statement A stand-alone UPDATE or DELETE statement on the same table (directly or through a view) by the same process An UPDATE...WHERE CURRENT or DELETE...WHERE CURRENT statement using a different cursor to access the same table (directly or through a view) by the same process For example, a loop containing both a FETCH statement and a stand-alone UPDATE or DELETE statement on the same table invalidates the cursor’s buffer on every loop iteration.
Data Retrieval and Modification OPEN Statement Example 4-2 declares a cursor named LIST-BY-PARTNUM: Example 4-2. Declaring a Cursor EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PARTS. 02 PARTNUM PIC 9(4) DISPLAY. 02 PARTDESC PIC X(18). 02 PRICE PIC S9(16)V9(2) COMP. 02 QTY-AVAILABLE PIC S9(9) COMP. ... EXEC SQL END DECLARE SECTION END-EXEC. ...
FETCH Statement Data Retrieval and Modification Some additional considerations for the OPEN statement are as follows: • • • • • You must code an OPEN statement within the scope of all other SQL statements (including the DECLARE CURSOR, FETCH, INSERT, DELETE, UPDATE, and CLOSE statements) that use the cursor. The OPEN statement must execute before any FETCH statements for the cursor. For audited tables and views, the OPEN statement must execute within a TMF transaction.
Data Retrieval and Modification FETCH Statement For audited tables and views, the FETCH statement must execute within the same TMF transaction as the OPEN statement for the cursor. This FETCH statement retrieves information from the PARTS table: EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 PARTS. 02 PARTNUM PIC 9(4) DISPLAY. 02 PARTDESC PIC X(18). 02 PRICE PIC S9(16)V9(2) COMP. 02 QTY-AVAILABLE PIC S9(9) COMP. ... EXEC SQL END DECLARE SECTION END-EXEC.
Data Retrieval and Modification Multirow SELECT Statement Multirow SELECT Statement When used with a cursor, a SELECT statement can return multiple rows from a table or protection view, one row at a time. A cursor uses a FETCH statement to retrieve each row and store the selected column values in host variables. The program can then process the values (for example, list or save them in an array).
Data Retrieval and Modification UPDATE Statement UPDATE Statement When used with a cursor, an UPDATE statement updates rows, one row at a time, in a table or protection view. To identify the set of rows to update (or test), specify the FOR UPDATE OF clause in the associated SELECT statement. Before you update each row, you can test one or more column values. If you decide to update the row, specify the WHERE CURRENT OF clause in the UPDATE statement.
UPDATE Statement Data Retrieval and Modification Example 4-3. Using the UPDATE statement BEGIN DECLARE SECTION. 01 NEW-PARTS. 02 STARTING-PARTNUM 02 NEW-PARTDESC 02 NEW-PRICE 02 NEW-QTY ... END DECLARE SECTION. PIC PIC PIC PIC 9(4). X(18). S9(16)V9(2) COMP. S9(9) COMP. EXEC SQL DECLARE GET-BY-PARTNUM CURSOR FOR SELECT PARTNUM, PARTDESC, PRICE, QTY-AVAILABLE FROM SALES.PARTS WHERE ( PARTNUM >= :STARTING-PARTNUM ) STABLE ACCESS FOR UPDATE OF PARTDESC, PRICE, QTY-AVAILABLE END-EXEC. ... ...
Multirow DELETE Statement Data Retrieval and Modification Multirow DELETE Statement When used with a cursor, a DELETE statement deletes multiple rows one row at a time from a table or protection view. You identify the set of rows to delete (or test) in the associated SELECT statement. Before you delete a row, you can test one or more column values, and then, if you decide to delete the row, specify the WHERE CURRENT OF clause in the DELETE statement.
Data Retrieval and Modification CLOSE Statement CLOSE Statement The CLOSE statement closes an open SQL cursor. After the CLOSE statement executes, the result table established by the OPEN statement no longer exists. To use the cursor again, you must reopen it using an OPEN statement. A program does not require special authorization to execute a CLOSE statement.
Data Retrieval and Modification Using Foreign Cursors These statements open, fetch, and close a foreign cursor named LIST-BY-PARTNUM which is declared in the procedure 3000-UPDATE-INVENTORY: ** While EOF=false OPEN 3000-UPDATE-INVENTORY.LIST-BY-PARTNUM USING DESCRIPTOR input-sqlda FETCH 3000-UPDATE-INVENTORY.LIST-BY-PARTNUM USING DESCRIPTOR output-sqlda. CLOSE 3000-UPDATE-INVENTORY.LIST-BY-PARTNUM.
5 SQL/MP System Procedures Table 5-1 describes the NonStop SQL/MP system procedures a COBOL program can call to return various SQL information. These procedures are described alphabetically on subsequent pages in this section. Table 5-1.
COBOLEXT File SQL/MP System Procedures COBOLEXT File To call the SQL/MP system procedures, which are written in TAL, use the COBOL ENTER TAL statement. The COBOLEXT file contains source declarations for these procedures (as well as for other system procedures). You might need to check with your system administrator to make sure the COBOLEXT file for the procedures you use in your program are available on your system.
SQL/MP System Procedures • SQLADDR From TACL or SQLCI, enter an ADD DEFINE (or ALTER DEFINE) command. Do not include a backslash (\) or a space before the node name. For example, this command adds a new DEFINE for the $SQL.MSG.FRENCH message file on the \PARIS node: ADD DEFINE =_SQL_MSG_PARIS,CLASS MAP,FILE $SQL.MSG.FRENCH For the _SQL_MSG_system DEFINE to be in effect for an SQLCI session, you must add or change the DEFINE before you start the SQLCI session.
SQLCA_DISPLAY2_ SQL/MP System Procedures SQLCA_DISPLAY2_ The SQLCA_DISPLAY2_ procedure displays the error or warning messages that SQL/MP returns to the SQLCA structure. SQLCA_DISPLAY2_ writes the information to a file or to a terminal.
SQLCA_DISPLAY2_ SQL/MP System Procedures output-record-length optional input PIC S9(4) COMP is the length in bytes of records to be written to the output file; the length must be an integer value from 60 to 600. The default length is 79 bytes. sql-msg-file-number optional input/output PIC S9(4) COMP is the file number of the SQL message file (SQLMSG is the default file). If you specify -1 as the input value, the system opens the message file and returns the resulting file number.
SQLCA_DISPLAY2_ SQL/MP System Procedures statistics optional input PIC X controls the display of statistics: Y Display row and cost statistics if the value returned to the SQLCA in the ROW or COST field is greater than or equal to 0. N Do not display statistics. R Display row statistics only. C Display cost statistics only. The default is Y.
SQL/MP System Procedures suffix Using SQLCA_DISPLAY2_ With an Error Table optional input PIC X(length) is a string to be appended to each output line. The default is a null string. suffix-length optional input PIC S9(4) COMP is the length of the suffix string for each output line. This length must be an integer value from 1 to 15. If you include suffix, suffix-length is required.
SQL/MP System Procedures • Additional Considerations for SQLCA_DISPLAY2_ The SQLCA can contain a maximum of seven errors and 180 bytes of text of the actual parameters returned to the program. Information that exceeds these limits is lost. SQLCA_DISPLAY2_ displays a warning message that indicates when information is lost.
Additional Considerations for SQLCA_DISPLAY2_ SQL/MP System Procedures Example 5-1. Error Processing Using SQLCA_DISPLAY2_ WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. EXEC SQL WHENEVER NOT FOUND PERFORM :8000-NOT-FOUND END-EXEC. EXEC SQL WHENEVER SQLWARNING PERFORM :9900-SQL-WARN END-EXEC. EXEC SQL WHENEVER SQLERROR PERFORM :9999-SQL-ERROR END-EXEC. DO-BEGIN. PERFORM READ-MESSAGE.
SQL/MP System Procedures Additional Considerations for SQLCA_DISPLAY2_ Example 5-2 shows the routines in the file COPYCODE. Example 5-2. Error Routines in Copy Library ?SECTION REQUEST-NOT-FOUND, TANDEM 8000-NOT-FOUND. MOVE "REQUESTED ROW NOT FOUND" TO ADVISORY-LINE OF REPLY. MOVE 9998 TO REPLY-CODE. ?SECTION SQL-WARNING, TANDEM 9900-SQL-WARN. MOVE -1 to SQL-MSG-FILE-NO. MOVE "N" to STATS. MOVE "N" to ERR-LOC.
SQL/MP System Procedures Generating Meaningful Messages As shown in Example 5-2 on page 5-10, the messages sent to the HOMETERM contain neither the internal location where the error was encountered nor the statistics and cost of the SQL statement. Usually, this information is not important and can be omitted to reduce the number of error lines. Consider an SQL constraint that is violated during the update, causing this message to be sent to HOMETERM: SQLCA display of SQL statement at SAMPLE.#5141.
SQLCA_TOBUFFER2_ SQL/MP System Procedures This procedure is similar to the SQLCA_DISPLAY2_ procedure, which writes error information to a file or terminal. ENTER TAL "SQLCA_TOBUFFER2_" USING sqlca, output-buffer, output-buffer-length, [ first-record-number, [ output-records, [ more, [ output-record-length, [ sql-msg-file-number, [ errors, [ warnings, [ statistics, [ caller-error-loc [ internal-error-loc, [ prefix, [ prefix-length, [ suffix, [ suffix-length sqlca required ] ] ] ] ] ] ] ] ] ] ] ] ] ].
SQLCA_TOBUFFER2_ SQL/MP System Procedures The default is 1. To obtain more than one error record, you must increment the value in firstrecord-number. output-records optional output PIC S9(4) COMP is the number of records (lines) written by SQLCA_TOBUFFER2_ to output-buffer. more optional output PIC X is a flag that indicates whether all the desired lines fit into the output-buffer: Y There were additional records; the buffer overflowed. N There were no additional records.
SQLCA_TOBUFFER2_ SQL/MP System Procedures errors optional input PIC X controls the writing of error messages to the buffer: Y Write all errors. N Write only the first error. B Write all errors but suppress this prefix: ERROR from subsystem [nn]: The default is Y. warnings optional input PIC X controls the writing of warning messages to the buffer: Y Write all warning messages. N Do not write any warning messages.
SQL/MP System Procedures Using SQLCA_TOBUFFER2_ With an Error Table The default is Y. internal-error-loc optional input PIC X controls the writing of the system-code location where the first error in the SQLCA occurred: Y Write the location. N Suppress the information. The default is Y. prefix optional input PIC X(length) is a string to precede each output line. The default is three asterisks and a space (*** ).
SQL/MP System Procedures Additional Considerations for SQLCA_BUFFER2_ space for four errors is sufficient. If you set the line length to 80 characters, four errors require a buffer of 960 characters. If you use SQLCA_DISPLAY2_ to write to an SQL error table, make the same changes to the parameter defaults. When you create the table to receive the error information, specify the text columns as multiples of 80 but less than 255 characters each.
SQLCAFSCODE SQL/MP System Procedures SQLMSG-BUFFER, 375, OMITTED, OMITTED, OMITTED, 75, SQLMSG-FILENUM. ... SQLCAFSCODE The SQLCAFSCODE procedure returns either the first or the last error in the SQLCA structure that was set by the file system, disk process, or operating system. If there was no such error, SQLCAFSCODE returns 0. If the SQLCA is full when an error occurs, the error is lost. ENTER TAL "SQLCAFSCODE" USING sqlca, [ first-flag ] GIVING error-info.
SQLCAGETINFOLIST SQL/MP System Procedures The information in the SQLCA structure can originate from these subsystems or system components: • • • • • • SQL/MP NonStop OS File system Disk process (DP2) FastSort program (SORTPROG process) Sequential I/O (SIO) procedures Note. The SQLCAGETINFOLIST procedure returns error numbers as positive values and warning numbers as negative values. A program might need to switch the sign before processing the error or warning.
SQLCAGETINFOLIST SQL/MP System Procedures result-max required input PIC S9(4) COMP is the maximum size, in bytes, of the result table. error-index optional input PIC S9(4) COMP is the index of the SQLCA error entry you want to see. The SQLCA structure has a fixed set of fields (item codes 1 through 21) for errors and warnings. In addition, SQLCA has a table of records (item codes 22 through 29), with each record describing one error or warning.
SQLCAGETINFOLIST SQL/MP System Procedures call-error output PIC S9(4) COMP is a variable you declare for the GIVING parameter to store the SQL error code that indicates the results of the call. SQLCAGETINFOLIST procedure error codes are: Error Code Description 8510 A required parameter is missing. 8511 The program specified an invalid item code. 8512 The program specified an invalid SQLCA structure.
SQLCAGETINFOLIST SQL/MP System Procedures Item Code Size (Bytes) Description 10 2 Syntax error location; if there was no syntax error, SQL returns -1. 11 2 Maximum length, in bytes, of the system procedure that sets the first error or warning. 12 2 Actual length, in bytes, of the system procedure that sets the first error or warning. 13 (in item code 12) Location of the system procedure that sets the first error or warning. 14 2 Maximum length, in bytes, of the parameter buffer.
SQLCAGETINFOLIST SQL/MP System Procedures Item Code Size (Bytes) Description 27 2 Sequence in which the error or warning was set. 28 2 Size of the buffer that contains parameters. Each string is delimited by a zero. 29 (in item code 28) Buffer that contains parameters, delimited by a zero. Each parameter begins on an even word boundary and is preceded by 2 bytes.
SQLCAGETINFOLIST SQL/MP System Procedures Example 5-3. Calling the SQLCAGETINFOLIST procedure (page 1 of 2) WORKING-STORAGE SECTION. ... * Declare a buffer to hold the error information: 01 ERRORS-AND-WARNINGS. 02 NAME-LEN PIC S9(4) COMP. 02 NUM-ERRS PIC S9(4) COMP. 02 NAME PIC X(32). 02 ERR-CODE PIC S9(4) COMP. * Include the SQLCA declaration: EXEC SQL INCLUDE SQLCA END-EXEC. * Declare a variable to hold the size of the buffer, to * be calculated using INSPECT..TALLYING: 01 ERR-WARN-SIZE PIC S9(4) COMP.
SQLGETCATALOGVERSION SQL/MP System Procedures Example 5-3. Calling the SQLCAGETINFOLIST procedure (page 2 of 2) * * * * * Call SQLCAGETINFOLIST. The third parameter is the number of codes you are supplying in item-list. The seventh parameter will cause the program to truncate all names to 32 characters. The ERROR-ITEM parameter receives information about the fifth entry in the errors array.
SQLGETOBJECTVERSION SQL/MP System Procedures sql-version output PIC S9(4) COMP is the version of the catalog. For information about versions of SQL/MP, see the SQL/MP Version Management Guide. error-info output PIC S9(4) COMP indicates the results of the SQLGETCATALOGVERSION call. If the call is successful, error-info is 0. If an error occurs, error-info contains the operating system or SQL error number. For a description of SQL errors, see the SQL/MP Messages Manual. Note.
SQLGETSYSTEMVERSION SQL/MP System Procedures error-info output PIC S9(4) COMP indicates the results of the SQLGETOBJECTVERSION call. If the call is successful, error-info is 0. If an error occurs, error-info contains the operating system or SQL error number. For a description of SQL errors, see the SQL/MP Messages Manual. Note. Although version 315 SQL/MP software supports the SQLGETOBJECTVERSION procedure, HP might not support this procedure in a future PVU.
SQLSADISPLAY SQL/MP System Procedures error-info output PIC S9(4) COMP indicates the results of the call. The procedure returns zero after a successful operation; otherwise, it returns a nonzero value to indicate an error or warning. For a description of SQL errors, see the SQL/MP Messages Manual. Note. Although version 315 SQL/MP software supports the SQLGETSYSTEMVERSION procedure, HP might not support this procedure in a future PVU.
SQLSADISPLAY SQL/MP System Procedures detail-params optional input determines whether sequential I/O (SIO) or Enscribe I/O is used for writing to the output file. A COBOL program usually omits detail-params and uses Enscribe I/O (the default). The parameter detail-params points to a structure with this TAL declaration: STRUCT detail^params; BEGIN sio STRING; out^fcb^1 INT .EXT; out^fcb^2 INT .EXT; END; sio specifies whether sequential I/O is used: Y Use SIO; ignore output-file-number.
SQLSADISPLAY SQL/MP System Procedures The elements of the SQLSADISPLAY procedure display are: Element Description \system.$vol.subvol.
Superseded Procedures SQL/MP System Procedures Superseded Procedures The SQLCADISPLAY and SQLCATOBUFFER procedures have been superseded by the SQLCA_DISPLAY2_ and SQLCA_TOBUFFER2_ procedures, respectively. The data type of parameters in the SQLCA_DISPLAY2_ and SQLCA_TOBUFFER2_ make those procedures easier to use in COBOL programs. These procedures are included in this manual for compatibility with earlier PVUs.
SQLCADISPLAY SQL/MP System Procedures output-record-length optional input PIC S9(4) COMP is the length in bytes of records to be written to the output file; the length must be an integer value from 60 to 600. The default is 79 bytes. sql-msg-file-number optional input/output PIC S9(4) COMP is the file number of the SQL message file (SQLMSG is the default file). If you specify -1 as an input value, the system opens the message file and returns the resulting file number.
SQLCADISPLAY SQL/MP System Procedures The default is Y. statistics optional input PIC S9(4) COMP controls the display of statistics: Y Display row and cost statistics if the value returned to the SQLCA in the ROW or COST field is greater than or equal to 0. N Do not display statistics. R Display row statistics only. C Display cost statistics only. The default is Y.
SQLCADISPLAY SQL/MP System Procedures prefix-length optional input PIC S9(4) COMP is the length of the prefix string for each output line. This length must be an integer value from 1 to 15. If you include prefix, prefix-length is required. suffix optional input PIC X(length) is a string to be appended to each output line. The default is a null string. suffix-length optional input PIC S9(4) COMP is the length of the suffix string for each output line.
SQLCATOBUFFER SQL/MP System Procedures Example This SQLCADISPLAY statement uses all default values: ENTER TAL “SQLCADISPLAY” USING SQLCA. SQLCATOBUFFER The SQLCATOBUFFER procedure writes to a buffer the error or warning messages returned by SQL/MP. The buffer is a record area declared in the Working-Storage or Extended-Storage Section of the program.
SQLCATOBUFFER SQL/MP System Procedures output-buffer required input/output PIC X(length) is the record name to which SQLCATOBUFFER writes the error information. output-buffer-length required input PIC S9(4) is the length of output-buffer in bytes. This length must be • • An integer value from output-record-length through 600 A multiple of output-record-length The minimum length recommended is 300 bytes.
SQLCATOBUFFER SQL/MP System Procedures The procedure pads each line with spaces and adds the suffix and prefix strings if the ENTER statement specifies them. sql-msg-file-number optional input/output PIC S9(4) COMP is the file number of the SQL message file (SQLMSG is the default file). If you specify -1 as the input value, the system opens the message file and returns the resulting file number. If you specify a value other than -1, the system uses that value as the file number of the message file.
SQLCATOBUFFER SQL/MP System Procedures statistics optional input PIC S9(4) COMP controls the writing of statistics to the buffer: Y Write row and cost statistics if the value returned to the SQLCA in the ROW or COST field is greater than or equal to 0. N Do not write statistics. R Write row statistics only. C Write cost statistics only. The default is Y.
SQLCATOBUFFER SQL/MP System Procedures suffix optional input PIC X(length) is a string to be appended to each output line. The default is a null string. suffix-length optional input PIC S9(4) COMP is the length of the suffix string for each output line. This length must be an integer value from 1 to 15. If you include suffix, suffix-length is required. This example uses a 75-character output record and declares a buffer SQLMSG-BUFFER as 375 characters.
6 Explicit Program Compilation This section describes the explicit compilation of a COBOL program containing embedded SQL statements and directives in the Guardian and OSS environments using TNS/R compilation tools. The Compilation Method Following are the steps for compiling a COBOL program that contains embedded SQL statements and directives. The steps are shown in Figure 6-1 on page 6-2 and described in detail on subsequent pages in this section. 1. Add any required class MAP or class CATALOG DEFINEs.
The Compilation Method Explicit Program Compilation Figure 6-1 shows the steps you follow to explicitly SQL compile a COBOL program. Figure 6-1. Compiling a COBOL Program COBOL Source File With Embedded SQL Statements 1 Add any required DEFINEs. Source FIle 2 Run the compiler. COBOL85 Compiler NMCOBOL Compiler Run the Binder program (if necessary) Binder Process 3 4 Run the compiler.
Requirements for Compiling a COBOL Program Explicit Program Compilation For a detailed description of directives ignored by the NMCOBOL compiler, see the COBOL85 for NonStop Systems Manual. The requirements for compiling a COBOL program are discussed next. Requirements for Compiling a COBOL Program Feature or Option COBOL Compiler Requirements SQL directive Required SQLMEM directive Optional SQLCODE identifier Required for each program and nested program.
Explicit Program Compilation Requirements for Compiling a COBOL Program Feature or Option COBOL Compiler Requirements INVOKE and INCLUDE directives Replaces INVOKE and INCLUDE directives with COBOL data declarations that correspond to the SQL structures being invoked or included. Inspect debugger You can use the Inspect interactive symbolic debugger on any COBOL object file. However, the current source line indicated by the Inspect debugger depends on how you produced the object file.
Explicit Program Compilation Using DEFINEs in the Guardian Environment Using DEFINEs in the Guardian Environment You can use DEFINE names in an SQL program to specify the names of SQL catalogs and objects (tables, views, indexes, partitions, and collations). You must set all DEFINE names used in SQL statements before SQL load time (the time when an SQL program executes its first statement) unless your program uses execution-time name resolution.
Using DEFINEs in the OSS Environment Explicit Program Compilation description of the STOREDDEFINES option, see SQL Compiler Options on page 6-26.
Explicit Program Compilation Using PARAM Commands Using PARAM Commands None of the PARAM commands are required. The COBOL85 and SQL compilers accept these command interpreter PARAM commands: • • • • • PARAM BINSERV PARAM SAMECPU PARAM SWAPVOL on page 6-8 PARAM SYMBOL-BLOCKS on page 6-8 PARAM SYMSERV on page 6-9 To use a PARAM command, you must enter it before you give the command to run the compiler. Use this syntax to enter a PARAM command.
Using PARAM Commands Explicit Program Compilation dummy-value is a nonzero value. It does not specify the CPU in which the processes are to run; the CPU run option controls that. PARAM SWAPVOL The PARAM SWAPVOL command specifies the volume on which the compiler and the Binserv and Symserv processes will create temporary files (if possible): PARAM SWAPVOL $volume $volume is the name of the volume on which the temporary files are to be created and must exist on the system on which the compiler resides.
Explicit Program Compilation Copying Source Code into a Compilation Unit PARAM SYMSERV The PARAM SYMSERV command specifies which Symserv process you want to use: PARAM SYMSERV guardian-name Follow these guidelines for guardian-name: • • If guardian-name designates a system other than the system on which the compiler is running, the SYMSERV parameter is ignored. If guardian-name does not include a volume or subvolume name, the compiler uses your current default values.
Processes Involved in Compilation Explicit Program Compilation Processes Involved in Compilation The COBOL85 compiler consists of one process, COBOL85. Two other processes, Symserv and Binserv, are present throughout the compilation. Symserv collects symbol table information, puts it into structures in a temporary file, and gives the temporary file to Binserv. Binserv writes the information from the temporary file into the object file. Binserv also performs binding.
Explicit Program Compilation Running the COBOL85 or NMCOBOL Compiler in the Guardian Environment WHENEVERLIST directs the compiler to write active WHENEVER options to the listing file after each SQL statement is processed. The default is not to write the WHENEVER options. RELEASE1 or RELEASE2 specifies the version of the SQL/MP features in the program (including the SQL data structures) and the version of SQL/MP software on which the program file can run as follows: RELEASE1 specifies version 1 features.
Explicit Program Compilation Running the COBOL85 or NMCOBOL Compiler in the Guardian Environment list-file is the destination file to which the compiler directs the listing; list-file can be a disk file, process (including a spooler collector), or I/O device name (including a terminal, magnetic tape unit, or printer) as follows: [\node.]file \node is an optional node (system) name. file is one of these Guardian names: [$volume-name.][subvolume-name.
Running the COBOL85 Compiler in the OSS Environment Explicit Program Compilation compiler-directive is a compiler directive as described in the COBOL85 for NonStop Systems Manual. To terminate a compilation in the Guardian environment, use the Break key to return to the Command Interpreter and stop the process using its process identification number. Running the COBOL85 Compiler in the OSS Environment The OSS utility cobol generates COBOL programs that run in the OSS environment.
Explicit Program Compilation Running the COBOL85 Compiler in the OSS Environment cbl cob is a suffix identifying file.cbl or file.cob as a COBOL source program to be compiled and (optionally) bound. a is a suffix identifying file.a as a library of object files (typically produced by the ar utility) to be passed directly to the Binder. o is a suffix identifying file.o as an object file produced by a previous COBOL compilation to be passed directly to the Binder. so is a suffix identifying file.
Explicit Program Compilation Running the COBOL85 Compiler in the OSS Environment Table 6-1. OSS cobol Utility Flags (page 2 of 3) -O [optlevel] Directs the cobol utility to set the optimization level to optlevel, which must be 0, 1, or 2. -O 0 is equivalent to the compiler directive OPTIMIZE 0. The resulting code is not accelerated. -O 1 is equivalent to the compiler directive OPTIMIZE 1. The resulting code is not accelerated. -O 2 is equivalent to the compiler directive OPTIMIZE 2.
Explicit Program Compilation Running the COBOL85 Compiler in the OSS Environment Table 6-1. OSS cobol Utility Flags (page 3 of 3) -Wrunlib=pathname Directs the cobol utility to make pathname the user library to be used at run time (pathname must be a Guardian file). -Wsql[="arguments"] Directs the cobol utility to invoke the SQL compiler, passing it arguments (For a description of the arguments that can be passed to the SQL compiler, see SQL Compiler Options on page 6-26).
Explicit Program Compilation Running the NMCOBOL Compiler in the OSS Environment Changing Default Pathnames and Disk Volume in the OSS Environment Table 6-2 shows the default pathnames of the programs that the cobol and nmcobol commands invoke and the default disk volume on which these programs create temporary files. To change one or more of the defaults before executing the cobol or nmcobol command, use the export command.
Running the NMCOBOL Compiler in the OSS Environment Explicit Program Compilation The command syntax for running the nmcobol compiler in the OSS environment follows. The nmcobol utility is case-sensitive. Bracketed items are optional. Put spaces between flags and their parameters but do not put spaces on either side of equal signs. For detailed information on the nmcobol utility, see the Open System Services Shell and Utilities Reference Manual. nmcobol [ flag [ flag ]... ] operand ...
Explicit Program Compilation Running the NMCOBOL Compiler in the OSS Environment srl is a suffix identifying file.srl as a shared run-time library (SRL) produced by the nld utility. The nld utility uses the SRL to resolve external references. You cannot use file.srl with the -WBstatic flag. -library is a sequence of characters whose effect depends on the binding mode. If static binding is selected (-WBstatic flag), the library liblibrary.a is searched.
Explicit Program Compilation Running the NMCOBOL Compiler in the OSS Environment Table 6-3. OSS nmcobol Utility Flags (page 2 of 4) -s Directs the nmcobol utility to produce object files or an executable file from which symbolic and other information not required for proper execution has been removed. If both -g and -s are present, keep symbolic information in the object files but remove it from the executable file. Do not use -Wsql and -s in the same invocation of the nmcobol utility.
Explicit Program Compilation Running the NMCOBOL Compiler in the OSS Environment Table 6-3. OSS nmcobol Utility Flags (page 3 of 4) -W[no]map Directs the nmcobol utility to enable [disable] the generation of identifier maps for the compiler listing. This flag works only if the -Wnosuppress flag is specified. The default is -Wnomap. -Wnld[="arguments"] Directs the nmcobol utility to pass arguments (a string of nld utility commands) to the nld utility.
Binding and Linking Explicit Program Compilation Table 6-3. OSS nmcobol Utility Flags (page 4 of 4) -Wsyntax Directs the nmcobol utility to check the syntax of the source program, but not generate any code. -Wusage Directs the nmcobol utility to display information on how to run the cobol utility. No compilation system components are run. -Wv Directs the nmcobol utility to echo to the standard error file the command line as each component of the compilation system is run.
Explicit Program Compilation The nld Utility Note. In an OSS environment, the Binder is invoked by the cobol utility through the -Wbind flag. For more information, see Running the COBOL85 Compiler in the OSS Environment on page 6-13, or the Open System Services Shell and Utilities Reference Manual. • • SQL compile only the final bound object; you are not required to separately SQL compile each object of a multiple-module program.
Explicit Program Compilation Required Access Authority during compilation to generate a report on the execution plans for SQL DML statements and the DEFINEs used by the program. Caution. You can use the Accelerator to optimize object code running on a TNS/R system. The Accelerator, however, invalidates SQL program files. Therefore, run the Accelerator before you explicitly SQL compile the program to avoid having to run the SQL compiler again. For more information, see the Accelerator Manual.
Running the SQL Compiler in the Guardian Environment Explicit Program Compilation • • Generates a listing of the SQL statements in the program file, including any warning or error messages that occurred. Sets the SQL SENSITIVE and SQL VALID flags in the program file label if the compilation is successful.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment You must run the SQL compiler on the same system where object-file exists. If you do not specify a system or volume name, the SQL compiler uses your current default values. list-file is the destination to which the SQL compiler directs the listing; list-file can be a disk file name, process name (including a spooler collector), or a device name (including a terminal, magnetic tape unit, or line printer) as follows: [\node.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment If the program was previously SQL compiled and recorded in a different catalog, the catalog-name overrides the catalog name stored in the program file. The program is dropped from the previous catalog and recorded in catalog-name. If you omit the CATALOG clause, the SQL compiler uses the current default catalog. If you have not defined a default catalog, the SQL compiler uses your current default subvolume.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment DEFINES [ file-name ] [ , OBEYFORM ] generates a listing of the TACL DEFINEs that the SQL compiler used to compile the SQL statements. (The SQL compiler uses these DEFINEs to recompile the program if the STOREDDEFINES option is specified.) file-name is the destination to which the DEFINE listing is written in addition to the compiler listing. See list-file for a description.
Explicit Program Compilation • Running the SQL Compiler in the Guardian Environment An access path (index) is unavailable. RECOMPILE is the default. If the program uses the similarity check, automatic recompilation might not occur. For more information, see Section 8, Program Invalidation and Automatic SQL Recompilation. NORECOMPILE directs the SQL compiler not to automatically recompile the program.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment OFF directs the SQL compiler to explicitly SQL compile the program and perform all SQL compiler functions. OFF is the default. NOREGISTER directs the SQL compiler to compile a program without registering the program in a catalog. To use the NOREGISTER option, you must have an SQL/MP software version of 310 (or later). ON directs the SQL compiler to explicitly compile the program but not to register it in a catalog.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment CHECK INVALID PROGRAM is the default. INVALID PLANS specifies that the SQL executor should automatically recompile an SQL statement if either of these conditions occur (and NORECOMPILE is not specified): • • The statement is invalid. The statement references a DEFINE at SQL load time that has changed since the last explicit SQL compilation.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment To support the COMPILE PROGRAM STORE SIMILARITY INFO, COMPILE INVALID PLANS, or COMPILE INOPERABLE PLANS option, an SQL catalog must have a catalog version of 310 (or later). If you specify the COMPILE PROGRAM STORE SIMILARITY INFO, COMPILE INVALID PLANS, or COMPILE INOPERABLE PLANS option (which stores similarity information in the program file), the SQL compiler sets the program’s PFV and PCV to 310.
Explicit Program Compilation Running the SQL Compiler in the OSS Environment SQL object. (The SQL compiler also generates empty sections for CONTROL directives and DDL statements.) Other SQL statements retain their existing execution plans. The COMPILE INOPERABLE PLANS option stores similarity information in the program file and updates the program’s name map and usages in the USAGES tables.
Explicit Program Compilation SQL Compiler Messages Error Conditions An error condition results from an invalid reference to an SQL object in an SQL statement. Examples of invalid references are an incorrect column name or an incompatible data type. If an error occurs, the SQL compiler generates a listing, but it does not record the program file in the catalog and does not validate it for execution. You can force an SQL compilation regardless of errors by specifying the SQLCOMP FORCE option.
SQL Program File Format Explicit Program Compilation refers to a table; the table, of course, does not exist when the program is explicitly SQL compiled.) • Unresolved DEFINEs. An SQL statement references a nonexistent DEFINE. SQL Program File Format The input program file to the SQL compiler can be a COBOL object file, a file generated by the Binder program, a file generated by the Accelerator, or a file previously compiled by the SQL compiler. Figure 6-2 shows the format of an SQL program file.
Explicit Program Compilation SQL Compiler Listings SQL Compiler Listings The SQL compiler writes all SQL statements in the program file to the listing (or OUT) file. If an error or warning occurs, the compiler include a message after the statement that caused the problem. For DML statements, the compiler also includes the estimated cost of processing the statement, which is a positive integer indicating the relative cost.
SQL Compiler Listings Explicit Program Compilation Example 6-1. Sample SQL Compiler Listing (page 1 of 2) SQL - Source File = \NEWYORK.$SQL.SQLPGMS.
Explicit Program Compilation Using the EXPLAIN Utility Example 6-1. Sample SQL Compiler Listing (page 2 of 2) SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL ************************************************** - Summary of SQL Compiling Number of SQL Statements = 6 Number of SQL Errors = 0 Number of SQL Warnings = 0 Number of other Errors = 0 Compile Time = 00:00:00.249 Elapsed Time = 00:00:22.915 Program file is \NEWYORK.$SQL.SQLPGMS.
Using the EXPLAIN Utility Explicit Program Compilation EXPLAIN DEFINES Report The EXPLAIN DEFINES report shows the mapping of DEFINE names used in SQL statements with this information: • • The default volume and catalog used by the program (obtained from the =_DEFAULTS DEFINE) Each DEFINE name and its associated Guardian name The EXPLAIN utility can generate the EXPLAIN DEFINES report in either of these formats: OBEY command file format EXPLAIN generates the ADD DEFINE commands that add the DEFINEs.
Explicit Program Compilation Using CONTROL Directives The catalog name is not included in the SQLCOMP command because it is stored in the program file. The NOOBJECT option suppresses the generation of a new object file, so the SQL compiler does not register the program file in a catalog. SQLCOMP /IN SQLPROG,OUT $S.#EXPLAIN/ NOOBJECT, EXPLAIN PLAN DEFINES SETDEFS, OBEYFORM For more information about the EXPLAIN utility, including detailed examples and reports, see the SQL/MP Query Guide.
Explicit Program Compilation Static SQL Statements Static SQL Statements Follow these guidelines when you use CONTROL directives with static SQL statements: • A CONTROL directive affects subsequent static DML statements in listing order, regardless of the execution order, until either of these conditions occur: ° ° Another CONTROL directive resets the CONTROL options. The program encounters the end of the run-time data unit (RTDU) that contains the CONTROL directive.
Explicit Program Compilation Dynamic SQL Statements This example varies the wait time for cursors that access the PARTS table. The default wait time (60 seconds) applies only to the first cursor (CURSOR1): PROCEDURE DIVISION. ... 200-DEFAULT-WAIT. EXEC SQL DECLARE CURSOR CURSOR1 FOR SELECT PARTNUM, PARTDESC, PRICE FROM SALES.PARTS WHERE (PARTNUM > :MIN-PARTNUM AND PARTNUM < :MAX-PARTNUM) ORDER BY PARTNUM END-EXEC. ... ... 500-SHORT-WAIT. EXEC SQL CONTROL TABLE SALES.PARTS TIMEOUT .1 SECOND END-EXEC.
Explicit Program Compilation • Using Compatible Components A dynamic CONTROL directive affects only dynamic SQL statements prepared after the CONTROL directive in execution order, except as described in this note. Note.
SQL Program File Explicit Program Compilation SQL Program File An SQL program file has these versions: Version Description Host object SQL version (HOSV) The version of the COBOL85 compiler used to compile the program. Generated by the COBOL85 compiler and therefore is the same as the host SQL version (HSV) of the compiler. Program format version (PFV) The version of the SQL compiler used to compile the program and the oldest version of an SQL executor that can execute the program.
Explicit Program Compilation SQL Program File You can also execute a dynamic GET VERSION OF PROGRAM statement using the PREPARE and EXECUTE statements as shown in this example: MOVE "GET HOST OBJECT VERSION OF PROGRAM SQLPROG" TO HV-TEXT. EXEC SQL PREPARE DYNAMIC-STATEMENT FROM :HV-TEXT END-EXEC. EXEC SQL EXECUTE DYNAMIC-STATEMENT RETURNING :HV-HOSV END-EXEC. You cannot, however, use the GET VERSION OF PROGRAM statement with the EXECUTE IMMEDIATE statement.
Explicit Program Compilation HP NonStop SQL/MP Programming Manual for COBOL85 —429326-004 6- 46 SQL Program File
7 Program Execution This section describes the execution of a COBOL program containing embedded SQL statements and directives in the OSS environment. The section provides details about the required access permissions, the TACL DEFINES used, and the steps to run the TACL RUN command. It further explains how to run a program at low PIN and how to determine compatibility with the SQL executor.
Entering the TACL RUN Command Program Execution Entering the TACL RUN Command To execute an SQL program file from a TACL process, use the TACL RUN (or RUND to invoke the INSPECT program) command. You can enter a RUN command either explicitly or implicitly by using this syntax: [ RUN[D] ] program-file [ / [ , run-option ]... / ] [ param-set ] RUN executes the program file without invoking the Inspect debugger. RUND executes the program file under the control of the Inspect symbolic debugger.
Using the CREATEPROCESS Routine Program Execution Using the CREATEPROCESS Routine To execute an SQL program file from a COBOL program, use the COBOL CREATEPROCESS routine. The CREATEPROCESS routine starts a new process by using the parameters you supply and (optionally) sends process-creation messages altered by COBOL saved message utility (SMU) routines to the new process. To call the CREATEPROCESS routine, use the COBOL ENTER statement; however, do not include the TAL keyword after the ENTER keyword.
Running a Program in the OSS Environment Program Execution For more information about the CREATEPROCESS routine, see the COBOL85 for NonStop Systems Manual. Running a Program in the OSS Environment After successfully compiling your COBOL program with the cobol utility in the OSS environment, you have an executable object file. Its name is either a.out (by default) or the name you gave it with the -o flag.
Interactive Commands Program Execution A D-series PIN has these divisions: • • • A low PIN ranges from 0 through 254. A high PIN ranges from 256 through 65,535 (or the maximum number). PIN 255 is reserved. If an SQL program was written (or converted) to run at a high PIN, you usually want the program to run at a high PIN because more high PINs are available, and it frees the low PINs for processes that cannot run at a high PIN.
Pathway Environment Program Execution SQL program file by using the Binder CHANGE command as previously described under Interactive Commands on page 7-5. Pathway Environment In a Pathway environment, an SQL program running as a server process can run at an available high PIN if these conditions are met: • • • • The SQL program was written (or converted) to run at a high PIN. The HIGHPIN server attribute for the SQL program in the Pathway configuration file is ON.
Program Execution Determining Compatibility With the SQL Executor To determine the PFV of an SQL program, use a FUP INFO or SQLCI FILEINFO command with the DETAIL option. For programs registered in version 300 (or later) catalogs, you can also query the PROGRAMS.PROGRAMFORMATVERSION column. However, for version 300 (or later) SQL/MP software, HP recommends that you use the GET VERSION OF PROGRAM statement with the FORMAT option. You can enter this statement from SQLCI or embedded in a COBOL program.
Program Execution Determining Compatibility With the SQL Executor HP NonStop SQL/MP Programming Manual for COBOL85 —429326-004 7 -8
8 Program Invalidation and Automatic SQL Recompilation This section describes the causes of program invalidation and automatic SQL recompilation and preventive steps you can take in either case. Program Invalidation An SQL program file can be valid or invalid. A valid program can run without SQL recompilation using its current execution plans.
Program Invalidation and Automatic SQL Recompilation Causes of Program Invalidation Causes of Program Invalidation Program invalidation is caused by certain operations performed on the program file and by DDL operations that alter an SQL object that the program references.
Program Invalidation and Automatic SQL Recompilation • • • Causes of Program Invalidation Changing a collation, which includes dropping and then re-creating the collation, renaming a collation, or changing a DEFINE that points to a collation Executing an UPDATE STATISTICS statement with the RECOMPILE option for a table (RECOMPILE is the default option) Restoring a table, including an underlying table of a protection or shorthand view, by using the RESTORE program with the SQLCOMPILE OFF option specified
Program Invalidation and Automatic SQL Recompilation File-Label and Catalog Inconsistencies File-Label and Catalog Inconsistencies Because NonStop SQL/MP records SQL validity in both the program’s file label and in the PROGRAMS catalog table, inconsistencies can occur. An invalid program file is sometimes recorded as valid in the catalog, or a valid program file is recorded as invalid in the catalog. Consider these situations: • A program file is not accessible to the SQL catalog manager.
Program Invalidation and Automatic SQL Recompilation • • • • • • Automatic SQL Recompilation ALTER TABLE statement to move or split partitions (including a simple move, one-way split, or two-way split) ALTER TABLE...DROP PARTITION statement ALTER INDEX...DROP PARTITION statement (if the similarity check is enabled for the base table) ALTER INDEX statement to move or split index partitions CREATE INDEX statement UPDATE STATISTICS...
Program Invalidation and Automatic SQL Recompilation • • • • Causes of Automatic Recompilation Uses the current description of the database to determine the most efficient access path for each referenced database object Maximizes database availability and node autonomy by generating a new execution plan at run time Allows a program to reference database objects that did not exist during explicit SQL compilation Allows a program to use a new set of DEFINEs to specify a different database (for example, a d
Program Invalidation and Automatic SQL Recompilation Causes of Automatic Recompilation (For a dynamic SQL statement, the SQL compiler uses the current set of DEFINEs when the PREPARE or EXECUTE IMMEDIATE statement executes.) Failed Timestamp Check The SQL executor performs the timestamp check for each SQL object referenced in an SQL statement at table open time (the first time the table is opened).
Program Invalidation and Automatic SQL Recompilation Causes of Automatic Recompilation Figure 8-1. Timestamp Check Processor (CPU) Memory PROG Execution Plans ... SELECT ... FROM TAB ... TAB Redefinition Timestamp ... TAB File Label Redefinition Timestamp ... TAB Data PROG (object code) SQL Executor Object Code ... Timestamp Check NonStop SQL/MP Database SQL recompilation rewrites copy of PROG execution plan in memory only.
Program Invalidation and Automatic SQL Recompilation Run-Time Recompilation Errors Run-Time Recompilation Errors If an automatic SQL recompilation is successful, the SQL statement executes; however, if the recompilation fails, the SQL executor returns compilation errors or warnings as follows: • • Recompilation of a single statement. The SQL executor returns error information to the SQLCODE variable and the SQLCA structure (if declared). Recompilation of an entire program.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations Specifying the CHECK INOPERABLE PLANS Option To direct the SQL executor to use the similarity check for a program, specify the CHECK INOPERABLE PLANS option when you explicitly compile the program as shown in the next example: SQLCOMP /IN sqlprog,OUT $s.#sqlist/ CHECK INOPERABLE PLANS For the complete syntax of the CHECK option, see Section 6, Explicit Program Compilation.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations table-name or view-name is the Guardian name or DEFINE name of the table or protection view. The name cannot be a shorthand view. For the ALTER TABLE statement with the SIMILARITY CHECK clause, table-name cannot be an SQL catalog table. SIMILARITY CHECK ENABLE | DISABLE enables or disables the similarity check for the specified table or protection view. DISABLE is the default.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations INOPERABLE PLANS option; otherwise, the similarity check fails and automatic recompilation occurs. RUN-TIME-TABLE can be the same table as COMPILE-TIME-TABLE, a modified version of COMPILE-TIME-TABLE, or a different table altogether. Note. The similarity check does not support parallel execution plans. Tables are not considered similar if they are specified in a query that uses a parallel execution plan.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations asterisk (*), RUN-TIME-TABLE must have the same number of columns as COMPILE-TIME-TABLE. For more information, see the next subsection. Similarity Rules for Protection Views The similarity check does not support shorthand views; the similarity rules for protection views are as follows: • • A protection view is never similar to a table or other SQL object.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations SELECT columna FROM table1 WHERE EXISTS (SELECT [DISTINCT] * FROM table2) TABLE1 = Pass TABLE2 = Fail INSERT INTO table1 (SELECT [DISTINCT] * FROM table2) TABLE1 = Fail TABLE2 = Fail SELECT table1.*,table2.x FROM table1,table2 TABLE1 = Fail, TABLE2 = Pass An SQL statement uses unqualified column names and the additional columns make one of the column names used in the statement ambiguous.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations Collations You do not have to explicitly enable the similarity check for a collation, because collations always have the check implicitly enabled. Two collations are similar only if they are equal. SQL/MP uses the CPRL_COMPAREOBJECTS_ procedure to compare the two collations. Consequently, two tables that contain character columns associated with collations are similar only if the collations are equal.
Program Invalidation and Automatic SQL Recompilation Preventing Automatic Recompilations HP NonStop SQL/MP Programming Manual for COBOL85 —429326-004 8- 16
9 Error and Status Reporting This section provides information about error and status reporting after the execution of an SQL statement or directive in a COBOL program. For information about the SQL descriptor area (SQLDA), see Section 10, Dynamic SQL Operations. Using the INCLUDE STRUCTURES Directive The INCLUDE STRUCTURES directive specifies the version of SQL structures that the COBOL85 compiler generates.
Error and Status Reporting Generating Structures With Different Versions version is the version number of the generated data structures; version can be 1, 2, 300, or later. { SQLCA | SQLSA } [ EXTERNAL ] specifies that the structures are declared as external, making it possible to share them among subprograms of the main program. As a result, the SQLCA object or the SQLSA object can be referenced by any subprogram in the main program that describes the object.
Sharing Structures Error and Status Reporting Sharing Structures Sharing a single SQLCA and SQLSA structure among subprograms of a host program saves a large amount of memory space. The SQLCA structure is 430 bytes. The pre-R330 SQLSA structure is 838 bytes, and the R330 SQLSA structure is 1790 bytes. A program with many subprograms that contain embedded SQL can consume enormous amounts of memory space for the multiple structures alone.
Error and Status Reporting Returning Error and Warning Information 04 MESSAGES PIC S9(9) COMP. 04 MESSAGE-BYTES PIC S9(9) COMP. 04 WAITS PIC S9(4) COMP. 04 ESCALATIONS PIC S9(4) COMP. 04 SQLSA-RESERVED PIC X(4). 02 PREPARE REDEFINES DML. 03 INPUT-NUM PIC 9(4) COMP. 03 INPUT-NAMES-LEN PIC 9(4) COMP. 03 OUTPUT-NUM PIC 9(4) COMP. 03 OUTPUT-NAMES-LEN PIC 9(4) COMP. 03 NAME-MAP-LEN PIC 9(4) COMP. 03 SQL-STATEMENT-TYPE PIC 9(4) COMP. 88 SQL-STATEMENT-SELECT VALUE 1. 88 SQL-STATEMENT-INSERT VALUE 2.
Error and Status Reporting Checking the SQLCODE Identifier This example uses the INCLUDE SQLCA directive to implicitly declare the SQLCODE data item: WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 IN-PARTS-REC. 10 IN-PARTNUM PIC 9(4). 10 IN-PRICE PIC 9(6)V99. 10 IN-PARTDESC PIC X(18). EXEC SQL END DECLARE SECTION END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. ... This example checks SQLCODE only for errors and warnings: PROCEDURE DIVISION. ... 300-INSERT-DATA. MOVE 4120 TO IN-PARTNUM.
Using the WHENEVER Directive Error and Status Reporting To do this, include an INVOKE SQLCODEX statement in each program or nested program in which you want an SQLCODEX data item. 2. Attach level-88 items to the SQLCODEX data item. 3. Declare the SQLCA structure. However, this approach is not recommended, because SQLCODEX is not part of the ANSI Database—Embedded SQL Standard.
Error and Status Reporting Using the WHENEVER Directive When more than one WHENEVER condition applies to an SQL statement, SQL/MP processes the conditions in order of precedence. For example, an SQL error and an SQL warning can occur for the same statement, but the error condition has a higher precedence and is processed first. These WHENEVER directives check for the error, warning, and not-found conditions: EXEC SQL WHENEVER NOT FOUND PERFORM :ROW-NOT-FOUND-7000 END-EXEC.
Error and Status Reporting Using the WHENEVER Directive Enabling and Disabling WHENEVER Checking You can enable and disable the WHENEVER directive for different parts of your program. For example, you might want to handle SQL errors by checking SQLCODE after an SQL statement instead of using WHENEVER SQLERROR. Example 9-1 shows how to enable and disable the WHENEVER directive. Example 9-1. Enabling and Disabling the WHENEVER Directive PROCEDURE-DIVISION. 0010-SET-UP.
Error and Status Reporting Using the WHENEVER Directive To avoid these situations, disable the appropriate WHENEVER directive for the part of your program that handles the condition. Example 9-2 on page 9-10 enables and disables the WHENEVER directive. Using an Aggregate Function All aggregate functions except COUNT return a null value when operating on an empty set.
Using the WHENEVER Directive Error and Status Reporting Example 9-2. Using the WHENEVER Directive WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 IN-PARTS-REC. 10 IN-PARTNUM PIC 9(4). 10 IN-PRICE PIC 9(8)V99. 10 IN-PARTDESC PIC X(18). EXEC SQL END DECLARE SECTION END-EXEC. 01 WARNING-SUM PIC S9(4) COMP. EXEC SQL INCLUDE SQLCA END-EXEC. ... PROCEDURE DIVISION. 100-OPENING. EXEC SQL WHENEVER SQLWARNING PERFORM :910-WARNINGS END-EXEC.
Returning Information From the SQLCA Error and Status Reporting Returning Information From the SQLCA SQL/MP returns run-time information, including errors and warnings, for the most recently executed SQL statement to the SQL communication area (SQLCA). The SQLCA structure can contain up to seven error or warning codes (in any combination) that might be returned by a single SQL statement or directive. Declaring the SQLCA Structure The COBOL85 compiler does not automatically generate an SQLCA structure.
Returning Information From the SQLCA Error and Status Reporting Table 9-1. SQLCA Structure Fields (page 1 of 3) Field Name Description EYE-CATCHER Identification field, always set by the system to CA. VERSION-ID Current version of the SQLCA; subsequent SQL/MP software PVUs can change this value. NUM-ERR-ENTRIES Maximum number of error entries that the ERRORS-ALL group item can hold. The number is 7. PARAMS-BUFFER-LEN Byte length of the PARAMS-BUFFER item. The maximum length is 180 bytes.
Returning Information From the SQLCA Error and Status Reporting Table 9-1. SQLCA Structure Fields (page 2 of 3) Field Name Description SQL-ERROR Repeating group for error information. There are 7 occurrences, each of which returns information for a single error or warning. ERRCODE Error and warning messages are documented in the SQL/MP Messages Manual.
Returning Information From the SQLCA Error and Status Reporting Table 9-1. SQLCA Structure Fields (page 3 of 3) Field Name Description FILLER Filler item. PARAMS-BUFFER Information about warnings and errors. PARAMSBUFFER-LEN is the length in bytes. You can use the SQLCA_DISPLAY2_ procedure to read information returned to this buffer. Each parameter is stored as a string of ASCII printable characters terminated by a byte containing binary 0. SRC-NAME-BUFFER Name of the program source file.
Error and Status Reporting Returning Information From the SQLCA This constraint in this example examines two columns within the same row and ensures that the employee termination date is equal to or greater than the date of hire: EXEC SQL CREATE CONSTRAINT VALIDATE_CONST ON EMPLOYEE CHECK TERM_DATE >= HIRE_DATE END-EXEC.
Error and Status Reporting Returning Information From the SQLCA Terminal Using HOMETERM is not always advisable because more error messages could be generated than HOMETERM can handle. For example, an incorrect table name in a DEFINE could make the table unavailable to a Pathway system. This error would cause every server program to receive an SQL error each time it referred to that table. Also, consider that other components of the system, such as TMF, might be using HOMETERM.
Error and Status Reporting Returning Information From the SQLCA Suppose that TABLEX and its index XTABLX are created as follows: CREATE TABLE TABLEX ( ERRDATE NUMERIC (6) NO DEFAULT ERRTIME NUMERIC (6) NO DEFAULT TERMID NUMERIC (6) NO DEFAULT SQLCODE NUMERIC (4) NO DEFAULT FSCODE NUMERIC (4) SYSTEM DEFAULT TEXT1 CHARACTER (240) SYSTEM DEFAULT TEXT2 CHARACTER (240) SYSTEM DEFAULT TEXT3 CHARACTER (240) SYSTEM DEFAULT TEXT4 CHARACTER (240) SYSTEM DEFAULT PRIMARY KEY ( ERRDATE ASC, ERRTIME ASC ) ) NO AUDIT SE
Returning Information From the SQLCA Error and Status Reporting Each Monday, for example, you could use DUP to copy TABLEX to a TABLEX1, and then clear TABLEX before activating the Pathway system. The errors found this week and last week are available. Only a minimum amount of program code is needed to support this facility. Selective Error Reporting SQL/MP returns an error condition for many different errors. Some of these are true errors, others are not.
Error and Status Reporting Returning Information From the SQLCA Summary of Error Processing Recommendations This list summarizes the recommendations described in this subsection for processing errors. • • • • Use WHENEVER directives to test for SQL errors, warnings, and other conditions. Each WHENEVER directive should refer to a common error handling routine. Develop common error handling routines that can be saved in a source library and copied with a COBOL COPY command or an COBOL SOURCE directive.
Error and Status Reporting • • • • • • Returning Performance and Statistics Information If you route errors directly to HOMETERM by using the SQLCA_DISPLAY2_ routine, save the errors in an SQL error table also. This table can be used as an easily retrievable record of errors. Archive the error table. Using the SQL utilities, make a copy of the table and purge the current data. Retain the copy for future reference. The error table might also be useful for reporting errors to your service provider.
Error and Status Reporting Returning Performance and Statistics Information The SQLSA structure is undefined after the execution of a DSL, DDL, DCL, or transaction control statement. Use this syntax for the INCLUDE SQLSA directive to declare the SQLSA in the Data Division of your program (but not in a Declare Section): EXEC SQL INCLUDE SQLSA END-EXEC.
Returning Performance and Statistics Information Error and Status Reporting Example 9-3. SQLSA Structure 01 SQLSA. 02 EYE-CATCHER PIC X(2) VALUE "SA". 02 VERSION PIC S9(4) COMP VALUE 0. 02 DML. 03 NUM-TABLES PIC 9(4) COMP VALUE 0. 03 STATS OCCURS 16 TIMES. 04 TABLE-NAME PIC X(24) VALUE SPACES. 04 RECORDS-ACCESSED PIC S9(9) COMP VALUE 0. 04 RECORDS-USED PIC S9(9) COMP VALUE 0. 04 DISC-READS PIC S9(9) COMP VALUE 0. 04 MESSAGES PIC S9(9) COMP VALUE 0. 04 MESSAGE-BYTES PIC S9(9) COMP VALUE 0.
Returning Performance and Statistics Information Error and Status Reporting Table 9-2. SQLSA Structure Fields (page 2 of 2) Field Name Description RECORDS-USED Number of records altered or returned. DISC-READS Number of disk reads and writes. MESSAGES Number of messages sent to the disk process. MESSAGE-BYTES Number of bytes sent in all the messages sent to the disk process. WAITS Number of lock waits or timeouts. ESCALATIONS Number of times record locks are escalated to file locks.
Error and Status Reporting Returning Performance and Statistics Information HP NonStop SQL/MP Programming Manual for COBOL85 —429326-004 9- 24
10 Dynamic SQL Operations Dynamic SQL is useful if you do not know all or part of an SQL statement before run time. In this case, you cannot program the statement into your application; it must be constructed at run time. For example, you might want to process SQL statements from a user or accept a statement generated by an application on a personal computer. When you use dynamic SQL, you can construct or obtain SQL statements at run time and then compile them and execute them.
Using Dynamic SQL Dynamic SQL Operations Using Dynamic SQL Figure 10-1 shows part of a COBOL program (top) that contains an embedded static SQL statement and a part of another COBOL program (bottom) that accepts a request from a user to execute a dynamic SQL statement. The steps used for the dynamic SQL statements are: 1. 2. 3. 4. Define a character string host variable to hold the dynamic SQL statement. Display a prompt to the user, requesting the SQL statement.
Dynamic SQL Operations Uses for Dynamic SQL Dynamic SQL can be less efficient than static SQL because more work is deferred until run time.
Dynamic SQL Operations Determining When to Use Dynamic SQL statement. The statement can contain input parameters. If it does, the program can prompt the user for the parameter values. You can also write a program for direct user input so that the user does not have to understand SQL syntax.
Dynamic SQL Operations • Using Parameters Use the PREPARE and EXECUTE statements to prepare a statement for execution, save the information about the statement, and then execute the statement as often as needed with these considerations: ° The PREPARE statement associates a statement name with the SQL statement specified by an SQL identifier and dynamically compiles an SQL/MP statement.
Dynamic SQL Operations Using Parameters The SQL statement is compiled without the actual input values, which are substituted for the parameter when the SQL statement executes. For the syntax for a parameter, see the SQL/MP Reference Manual. Input Parameters A dynamic SQL statement can contain input parameters, which allow your program to construct SQL statements at run time.
Dynamic SQL Operations Using Parameters A PREPARE statement prepares the statement in the host variable named UPDATE-STATEMENT: EXEC SQL PREPARE EXECUTE-STATEMENT FROM :UPDATE-STATEMENT END-EXEC. To supply values for the UPDATE statement at run time, the program uses the two host variables HOST-VAR1 and HOST-VAR2: EXEC SQL EXECUTE EXECUTE-STATEMENT USING :HOST-VAR1, :HOST-VAR2 END-EXEC. The value in HOST-VAR1 is used for both instances of parameter ?A. The value in HOST-VAR2 is used for parameter ?B.
Dynamic SQL Operations Developing a Dynamic SQL Application name of the parameter in NAMESBUF, the input names buffer. The prepared statement is named S1. EXEC SQL PREPARE S1 FROM :INTEXT END-EXEC. EXEC SQL DESCRIBE INPUT S1 INTO :IN-SQLDA NAMES INTO :NAMESBUF END-EXEC. 5. The program enters a loop and prompts the user to supply values for successive execution of the statement: * BEGINNING OF LOOP * PROMPT THE USER FOR A VALUE USING THE * PARAMETER NAME FROM THE NAMES BUFFER. ...
Dynamic SQL Operations Developing a Dynamic SQL Application This subsection includes information about how to support parameters and null values in dynamic programs; the topics might not all apply to your application. In general, the steps in a dynamic SQL application are: 1. Declare a host variable for the SQL statement to be submitted. 2. Declare the SQLCA and SQLSA data structures. 3. If you plan to support input or output parameters in the SQL statement, perform these steps: a.
Dynamic SQL Operations Declaring a Host Variable Declaring a Host Variable In a Declare Section in the Data Division, declare a host variable to serve as the buffer or “container” for the SQL statement: EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 STATEMENT-BUFFER PIC X(256) VALUE SPACES. EXEC SQL END DECLARE SECTION END-EXEC.
Dynamic SQL Operations Defining Storage for Input and Output Parameters more than one SQLDA; in some cases, you can reuse the same structure for different SQL statements. If there are input parameters in the dynamic SQL statement, you must have an input SQLDA to describe them. Similarly, if your program handles dynamic SELECT operations, declare an SQLDA to describe the output results (SELECT columns).
Dynamic SQL Operations Defining Storage for Input and Output Parameters Table 10-2. SQLDA Structure Fields (page 2 of 3) Field Name Description DATA-LEN The DATA-LEN value depends on the data type as follows: Fixed-length character The number of bytes in the string. Variable-length character The maximum number of bytes in the string. Decimal numeric Bits 0:7 contain the decimal scale. Bits 8:15 contain the byte length of the item. Binary numeric Bits 0:7 contain the decimal scale.
Dynamic SQL Operations Defining Storage for Input and Output Parameters Table 10-2. SQLDA Structure Fields (page 3 of 3) Field Name Description VAR-PTR The extended address of the actual data (value of input parameter or database column). SQL/MP does not return VAL-PTR; your program must initialize it to point to the input and output data buffer. IND-PTR The address of a flag that indicates whether a parameter or column is actually null.
Dynamic SQL Operations Defining Storage for Input and Output Parameters Table 10-3.
Dynamic SQL Operations Defining Storage for Input and Output Parameters Declaring the SQLDA Structure, Names Buffer, and Collation Buffer To declare an SQLDA structure and associated buffers, use the INCLUDE SQLDA directive in the Data Division of your program (but not in a Declare Section).
Dynamic SQL Operations Defining Storage for Input and Output Parameters CPRULES is a keyword that is required if you specify a collation buffer. collation-buffer is a host variable that is a COBOL record name of the collation buffer. The DESCRIBE statement includes the COLLATIONS INTO clause, which directs SQL/MP to return collations to collation-buffer. For more information, see Calculating the Lengths of the Names and Collation Buffers on page 10-20.
Dynamic SQL Operations Defining Storage for Input and Output Parameters The COBOL85 compiler generates this SQLDA structure: 01 SQLDAX. 05 EYE-CATCHER 05 NUM-ENTRIES 05 SQLVAR 10 DATA-TYPE 10 DATA-LEN 10 PRECISION 10 NULL-INFO 10 VAR-PTR 10 IND-PTR 10 CPRL-PTR 10 RESERVED PIC X(2) VALUE "D1". PIC S9(4) COMP VALUE 20. OCCURS 20 TIMES. PIC S9(4) COMP VALUE 0. NATIVE-2. PIC S9(4) COMP VALUE 0. PIC S9(4) COMP VALUE 0. PIC S9(9) COMP VALUE -999999. PIC S9(9) COMP VALUE -999999. PIC S9(9) COMP VALUE -999999.
Dynamic SQL Operations Preparing the SQL Statement Preparing the SQL Statement Before preparing the statement, specify WHENEVER directives for error handling in the Procedure Division: EXEC SQL WHENEVER SQLERROR PERFORM :100-HANDLE-ERROR END-EXEC. EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC. If you are obtaining the SQL statement from a user, read the statement. In the Procedure Division, prepare the SQL statement.
Dynamic SQL Operations Checking for Parameters Table 10-4. SQLSA Contents after a Prepare Operation (page 2 of 2) SQLSA Field Description OUTPUT-NAMES-LEN Length of the buffer required to contain the names of the output parameters. SQL-STATEMENT-TYPE Type of statement being prepared. Use this information to decide what type of statement was entered.
Dynamic SQL Operations Handling Parameters Calculating the Lengths of the Names and Collation Buffers Use the names buffer to store the names of your input parameters (after a DESCRIBE INPUT operation) or the names of selected columns (after a DESCRIBE operation). SQL/MP returns a name to the names buffer as a VARCHAR item. A column name is qualified with the table name. COBOL85 processes the name as a group item defined as: 02 02 LEN VAL PIC 9(4) COMP. PIC X(value-of-len).
Dynamic SQL Operations Handling Parameters from INPUT-NUM or OUTPUT-NUM, respectively. On each iteration, follow these steps: 1. Check the DATA-TYPE field. If necessary, adjust the data type and reset DATALEN and PRECISION accordingly. For an example of this, see Example 10-1 on page 10-25. 2. Allocate an amount of memory equal to DATA-LEN for the parameter. 3. Initialize EYE-CATCHER, NUM-ENTRIES, VAR-PTR, and IND-PTR in the SQLDA structure. 4.
Dynamic SQL Operations Handling Parameters The same considerations apply if your program must handle precision for date-time, INTERVAL, FLOAT, or binary numeric values. The precision information is in the PRECISION field of the SQLVAR entry. This example checks for scale in an output value and sets the scale information to 3 for numeric and decimal items. You would follow a similar procedure to handle a scaled input parameter value, using the input SQLDA instead of the output SQLDA.
Dynamic SQL Operations Handling Parameters reference the DATA-TYPE, DATA-LEN, and VAR-PTR fields in the SQLDA. In addition, this program must initialize these fields: • • • EYE-CATCHER to point to the value D1 (for a version 2 or 300 SQLDA) or DA (for a version 1 SQLDA). VAR-PTR to point to the input or output data buffer. Because COBOL programs cannot generate addresses, you must call a TAL procedure, SQLADDR, to accomplish this task.
Dynamic SQL Operations • Prompting the User for Input Values IND-PTR points to a flag in Working-Storage that indicates whether the parameter actually was null. If the parameter or output variable is not null, you use the location referenced by VAR-PTR to indicate the value. If your program processes indicator parameters, IND-PTR points to the indicator parameter associated with that input parameter in the names buffer after DESCRIBE INPUT executes.
Dynamic SQL Operations Prompting the User for Input Values Example 10-1. Evaluating Input Parameter Values (page 1 of 2) DATA DIVISION. * Loop counter: 01 INDEX PIC S9(4) COMP. * Define storage for all possible data types. The VAR-PTR * field in the input SQLDA points to this storage. 01 PARAM-REC. 02 PARAMS OCCURS 20 TIMES. 03 P PIC X(60). 03 PCHAR REDEFINES P PIC X(60). 03 PVARCHAR REDEFINES P. 04 LEN PIC S9(4) COMP. 04 VAL PIC X(58). 03 PNUMERIC REDEFINES P PIC S9(15)V9(3) COMP.
Dynamic SQL Operations Prompting the User for Input Values Example 10-1.
Dynamic SQL Operations Prompting the User for Input Values You can use VAR-PTR to read the names from the names buffer only if you access the names buffer immediately following DESCRIBE INPUT or DESCRIBE. After you have set VAR-PTR to point to the data, you can no longer use VAR-PTR to access the names buffer and must loop through the names buffer to get the names.
Dynamic SQL Operations Prompting the User for Input Values Example 10-2 prompts for input. Example 10-2. Prompting for Input DATA DIVISION. ... 01 NAME 01 NAME-IX PIC X(30). PIC S9(4) COMP. * This variable will store the 2-byte length field: 01 NAMESIZEX PIC X(2). * This variable redefines the 2-byte length field * as an integer so you can perform arithmetic to advance * through the buffer: 01 NAMESIZE REDEFINES NAMESIZEX PIC S9(4) COMP. PROCEDURE DIVISION. ...
Dynamic SQL Operations Performing the Database Request After DESCRIBE INPUT executes and for each input parameter described in an SQLVAR array in the input SQLDA, SQL/MP sets NULL-INFO to -1 if the input parameter in the prepared statement could have a null value (that is, if the prepared statement included a null indicator parameter). If the user specifies a null value for the parameter, set the Working-Storage location referenced by IND-PTR to -1.
Dynamic SQL Operations Performing the Database Request Processing SELECT Statements To determine if a statement is a SELECT statement, check SQL-STATEMENT-TYPE OF SQLSA to see if it equals 1. If so, perform these steps: 1. Declare a cursor to handle the SELECT statement: EXEC SQL DECLARE C1 CURSOR FOR S1 END-EXEC. For more information about dynamic cursors, see Using Dynamic SQL Cursors, following. 2. Begin a TMF transaction: EXEC SQL BEGIN WORK END-EXEC.
Dynamic SQL Operations Performing the Database Request subsection describes some guidelines for the use of cursors.
Dynamic SQL Operations Displaying Output You also use the USING DESCRIPTOR clause with the FETCH statement to write column values to an output buffer specified in the program’s variable declarations. The output SQLDA describes a list of memory locations into which FETCH copies the data. Using Cursors With an UPDATE WHERE CURRENT Clause To use UPDATE WHERE CURRENT with a static SQL cursor, specify a FOR UPDATE OF clause with a column list in the DECLARE CURSOR statement.
Dynamic SQL Operations Displaying Output For input, the sequence was described in Prompting the User for Input Values on page 10-24 as follows: 1. 2. 3. 4. Get the length of the parameter name. Advance to the name. Display the name and ask for a value for that name. Interpret the value entered according to data type. To display output, one possible sequence is: 1. 2. 3. 4. Get the length of the column name. Advance to the name. Interpret the data type of the column value.
Dynamic SQL Operations Displaying Output Example 10-3. Displaying Output Column Values DATA DIVISION. * Define storage for all possible data types for output columns: 01 COLUMNS-REC. 02 COLUMN OCCURS 20 TIMES. 03 C PIC X(60). 03 RCHAR REDEFINES C PIC X(60). 03 RINT REDEFINES C PIC 9(9) COMP. 03 RVARCHAR REDEFINES C. 04 LEN PIC S9(4) COMP. 04 VAL PIC X(30) . 03 RNUMERIC REDEFINES C PIC S9(15)V9(3) COMP. 03 RDECIMAL REDEFINES C PIC S9(9)V9(3) DISPLAY. 03 RLARGINT REDEFINES C PIC 9(18) COMP.
Dynamic SQL Operations Constructing a Server that Interfaces With Pathway Handling Null Results If the value returned is null, SQL/MP checks NULL-INFO and moves a -1 into the location pointed to by IND-PTR. (Errors are returned if the value is null but NULL-INFO is 0 or if IND-PTR is an invalid address.) Your program must check NULL-INFO to determine whether the value returned could be null.
Dynamic SQL Operations Constructing a Reply Message includes. As each value is read, you concatenate the corresponding text to form the statement. Example Suppose that an application screen describes a personnel record. If any column does not have a value, the user can enter N. LIST-MSG is the name of the request message you defined.
Dynamic SQL Operations Sample Dynamic SQL Program Sample Dynamic SQL Program Example 10-4 shows a COBOL program that constructs a set of SELECT statements, prepares them, and retrieves the associated data. Example 10-4. Sample Dynamic SQL Program (page 1 of 2) ?SQL ?INSPECT ?SYMBOLS IDENTIFICATION DIVISION. PROGRAM-ID. COBEXT. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. TANDEM/16. OBJECT-COMPUTER. TANDEM/16. * DATA DIVISION. WORKING-STORAGE SECTION. 01 CURSORS.
Dynamic SQL Operations Sample Dynamic SQL Program Example 10-4. Sample Dynamic SQL Program (page 2 of 2) PROCEDURE DIVISION. 1000-DRIVER. PERFORM 3000-SPECIFY-ERROR-HANDLING. PERFORM 3100-PROCESS-QUERIES. STOP RUN. 3000-SPECIFY-ERROR-HANDLING. EXEC SQL WHENEVER SQLERROR PERFORM :6000-HANDLE-ERROR END-EXEC. 3100-PROCESS-QUERIES.
11 Character Processing Rules (CPRL) Procedures This section describes CPRL procedures that a COBOL program can call to process these collation objects: • • SQL collation—An SQL/MP object with file code 941 generated by the CREATE COLLATION statement Collation object—A Guardian file with file code 199 generated by the NLCP collation compiler ($system.system.NLCPCOMP) Table 11-1 summarizes the CPRL procedures. These procedures are alphabetically described in detail on subsequent pages in this section.
Character Processing Rules (CPRL) Procedures COBOLEXT File Table 11-1.
Character Processing Rules (CPRL) Procedures CPRL_ARE_ CPRL_ARE_ The CPRL_ARE_ procedure determines if all characters in a string are in the character class defined by the specified CPRL. You can also call CPRL_ARE_ to scan a string for the first character not in a specific character class. ENTER TAL "CPRL_ARE_" USING classname, classnamelength, inputstring, inputstringlength, exceptcharaddr, cprladdr GIVING errorcode.
Character Processing Rules (CPRL) Procedures • • CPRL_AREALPHAS_ If -6 is returned, the first character in istring not in the specified character class was found; exceptcharaddr is set to the address of this character. For other error codes, exceptcharaddr is set to an invalid address. cprladdr pic S9(9) input is a pointer to the SQL collation or collation object.
Character Processing Rules (CPRL) Procedures • CPRL_ARENUMERICS_ For other error codes, exceptcharaddr is set to an invalid address. cprladdr pic S9(9) input is a pointer to the SQL collation or collation object. CPRL_ARENUMERICS_ The CPRL_ARENUMERICS_ procedure determines if all characters in a string are numeric according to the specified SQL collation or collation object. You can also use CPRL_ARENUMERICS_ to scan for the first nonnumeric character in a string.
Character Processing Rules (CPRL) Procedures cprladdr CPRL_COMPARE1ENCODED_ pic S9(9) input is a pointer to the SQL collation or collation object. CPRL_COMPARE1ENCODED_ The CPRL_COMPARE1ENCODED_ procedure compares two strings according to an SQL collation or collation object. The first string is assumed to be in encoded form, and the second is assumed to be in original (not encoded) form. For strings of unequal length, the procedure logically pads the shorter string with blanks.
Character Processing Rules (CPRL) Procedures result CPRL_COMPARE_ pic S9(4) output indicates the result of the comparison: -1 The first operand is less than the second 0 The operands collate equally 1 The first operand is greater than the second For error codes other than 0 (zero), result is meaningless. cprladdr pic S9(9) input is a pointer to the SQL collation or collation object.
Character Processing Rules (CPRL) Procedures string2 CPRL_COMPAREOBJECTS_ pic X(string2length) input is an array containing the second string to be compared. string2length pic S9(4) input is the length in bytes of string2. result pic S9(4) output indicates the result of the comparison, if the error code is 0 (zero): -1 string1 is less than string2. 0 The strings collate equally. 1 string1 is greater than string2.
Character Processing Rules (CPRL) Procedures CPRL_DECODE_ CPRL_DECODE_ The CPRL_DECODE_ procedure decodes a string that has been encoded by the CPRL_ENCODE_ procedure. If the same (or equivalent) SQL collation is used for both CPRL_ENCODE_ and CPRL_DECODE_, the decoded string will be equal to the original string with respect to that SQL collation. Because encoding is not generally a one-to-one function, the decoded string might not be identical to the original string.
Character Processing Rules (CPRL) Procedures decodedstringlength CPRL_DOWNSHIFT_ pic S9(4) output is the number of bytes of encodedstring that were decoded. CPRL_DECODE_ pads the remainder of decodedstring with blanks up to decodedstringmaxlength. cprladdr pic S9(9) input is a pointer to the SQL collation or collation object. CPRL_DOWNSHIFT_ The CPRL_DOWNSHIFT_ procedure downshifts a character string according to the downshift rules in a specified SQL collation or collation object.
Character Processing Rules (CPRL) Procedures shiftedstringmaxlength CPRL_ENCODE_ pic S9(4) input specifies the maximum length of shiftedstring; it must be greater than equal to inputstring. shiftedstringlength pic S9(4) output specifies the length of the downshifted string returned in shiftedstring. cprladdr pic S9(9) input is a pointer to the SQL collation or collation object.
Character Processing Rules (CPRL) Procedures encodedstringmaxlength CPRL_GETALPHATABLE_ pic S9(4) input specifies the maximum length of encodedstring. encodedstringlength pic S9(4) output is the number of bytes of decodedstring that were encoded. CPRL_ENCODE_ pads the remainder of decodedstring with encoded blanks up to decodedstringmaxlength. cprladdr pic S9(9) input is a pointer to the SQL collation or collation object.
Character Processing Rules (CPRL) Procedures CPRL_GETCHARCLASSTABLE_ CPRL_GETCHARCLASSTABLE_ The CPRL_GETCHARCLASSTABLE_ procedure extracts character class information from an SQL collation or collation object for a user-specified character class. ENTER TAL "CPRL_GETCHARCLASSTABLE_" USING array, cprladdr, classname, classnamelength, GIVING errorcode. The CPRL_GETCHARCLASSTABLE_ procedure returns these error codes: Code Description The operation was successful.
Character Processing Rules (CPRL) Procedures CPRL_GETDOWNSHIFTTABLE_ CPRL_GETDOWNSHIFTTABLE_ The CPRL_GETDOWNSHIFTTABLE_ procedure extracts downshift information from an SQL collation or collation object. ENTER TAL "CPRL_GETDOWNSHIFTTABLE_" USING array, cprladdr GIVING errorcode. The CPRL_GETDOWNSHIFTTABLE_ procedure returns these error codes: Code Description The operation was successful. 0 –2 The SQL collation or collation object is invalid.
Character Processing Rules (CPRL) Procedures CPRL_GETLAST_ The CPRL_GETFIRST_ procedure returns these error codes: Code Description The operation was successful. 0 –2 The SQL collation or collation object is invalid. –4 The version of the SQL collation or collation object is not supported. firststring pic X(firststringmaxlength) output is an array in which CPRL_GETFIRST_ returns the first string. firststringmaxlength pic S9(4) input is the maximum length of firststring.
Character Processing Rules (CPRL) Procedures laststringmaxlength CPRL_GETNEXTINSEQUENCE_ pic S9(4) input specifies the maximum length of laststring. laststringlength pic S9(4) output specifies the number of bytes of laststring that were scanned. (When CPRL_GETFLAST_ is successful, laststringlength and laststringmaxlength will be equal.) cprladdr pic S9(9) input is a pointer to the SQL collation or collation object.
Character Processing Rules (CPRL) Procedures nextstring CPRL_GETNUMTABLE_ pic X(nextstringmaxlength) output is an array in which CPRL_GETNEXTINSEQUENCE_ returns the next string. Overlapping inputstring and nextstring causes unpredictable results. nextstringmaxlength pic S9(4) input specifies the maximum length of nextstring. The returned value is padded with blanks as necessary to fill nextstring for this length. In most cases, set nextstring to the same value as inputstring.
Character Processing Rules (CPRL) Procedures array CPRL_GETSPECIALTABLE_ pic X(256) output is a 256-byte array specified by the user. If the call is successful, CPRL_GETNUMTABLE_ sets each byte in array as follows: 1 The corresponding character code in the SQL collation or collation object is numeric. 0 The corresponding character code in the SQL collation or collation object is not numeric. If the call is unsuccessful, array is not modified.
Character Processing Rules (CPRL) Procedures cprladdr CPRL_GETUPSHIFTTABLE_ pic S9(9) input is a pointer to the SQL collation or collation object. CPRL_GETUPSHIFTTABLE_ The CPRL_GETUPSHIFTTABLE_ procedure extracts upshift information from an SQL collation or collation object. ENTER TAL "CPRL_GETUPSHIFTTABLE_" USING array, cprladdr GIVING errorcode. The CPRL_GETUPSHIFTTABLE_ procedure returns these error codes: Code Description The operation was successful.
Character Processing Rules (CPRL) Procedures CPRL_INFO_ The CPRL_INFO_ procedure returns these error codes: Code Description The operation was successful. 0 –2 The SQL collation or collation object is invalid. –4 The version of the SQL collation or collation object is not supported. The user-specified buffer is not large enough to receive the returned string. –20 cprladdr pic S9(9) input is a pointer to the SQL collation or collation object.
Character Processing Rules (CPRL) Procedures CPRL_READOBJECT_ CPRL_READOBJECT_ The CPRL_READOBJECT_ procedure reads a collation object from a Guardian disk file (file code 199) into a user-specified buffer. CPRL_READOBJECT_ does not read SQL collations (file code 941) generated by a CREATE COLLATION statement. ENTER TAL "CPRL_READOBJECT_" USING buffer bufferlength objectlength filename filenamelength cprladdr GIVING errorcode.
Character Processing Rules (CPRL) Procedures filename CPRL_UPSHIFT_ pic X(filenamelength) input is the Guardian file name in external format containing the collation object. The file code for filename must be 199. filenamelength pic S9(4) input is the length in bytes of filename. cprladdr pic S9(9) output is the address of the collation object if 0 (zero) is returned; otherwise, cprladdr is set to an invalid address.
Character Processing Rules (CPRL) Procedures shiftedstringmaxlength CPRL_UPSHIFT_ pic S9(4) input specifies the maximum length of shiftedstring; it must be greater than or equal to inputstringlength. shiftedstringlength pic S9(4) output specifies the length of the upshifted string returned in shiftedstring. cprladdr pic S9(9) is a pointer to the SQL collation or collation object.
Character Processing Rules (CPRL) Procedures HP NonStop SQL/MP Programming Manual for COBOL85—429326-004 11 -24 CPRL_UPSHIFT_
A SQL/MP Sample Database This appendix describes the NonStop SQL/MP sample database and sample application that are included on the product site update tape (SUT). Many examples in this manual (as well as other SQL/MP manuals) refer to the sample database. You can create your own copy of the sample database and access it using SQLCI commands or by embedding SQL statement in a host-language program. The sample database includes the PERSNL, SALES, and INVENT subvolumes.
SQL/MP Sample Database Figure A-1 shows the names of columns and tables and the relations between the tables in the sample database. Figure A-1.
SQL/MP Sample Database Example A-1 shows the COPYLIB file containing the record descriptions of the sample database tables. This file was generated using INVOKE directives executed from SQLCI. For example, this INVOKE directive generates the DEPT table: INVOKE PERSNL.DEPT FORMAT COBOL85 TO COPYLIB (DEPT) ; For more information about SQLCI, see the SQL/MP Reference Manual.
SQL/MP Sample Database Example A-1. COPYLIB File for Sample Database (page 2 of 3) ***************************************************** * Sales (SALES) * ***************************************************** ?SECTION CUSTOMER * Record Definition for \SYS1.$VOL1.SALES.CUSTOMER * Definition current at 17:09:10 - 10/10/94 01 CUSTOMER. 02 CUSTNUM PIC 9(4) COMP. 02 CUSTNAME PIC X(18). 02 STREET PIC X(22). 02 CITY PIC X(14). 02 STATE PIC X(12). 02 POSTCODE PIC X(10). 02 CREDIT PIC X(2).
SQL/MP Sample Database Example A-1. COPYLIB File for Sample Database (page 3 of 3) ***************************************************** * Inventory (INVENT) * ***************************************************** ?SECTION SUPPLIER * Record Definition for \SYS1.$VOL1.INVENT.SUPPLIER * Definition current at 17:09:14 - 10/10/94 01 SUPPLIER. 02 SUPPNUM PIC 9(4) COMP. 02 SUPPNAME PIC X(18). 02 STREET PIC X(22). 02 CITY PIC X(14). 02 STATE PIC X(12). ?SECTION PARTSUPP * Record Definition for \SYS1.$VOL1.INVENT.
SQL/MP Sample Database HP NonStop SQL/MP Programming Manual for COBOL85—429326-004 A- 6
B Memory Considerations This appendix describes the SQL internal data structures generated in a COBOL program, including this information: • • Using the SQLMEM directive to control the placement of the SQL internal data structures Estimating the memory required by a COBOL program SQL/MP Internal Structures The COBOL85 compilers generate the SQLINALL internal data structure to maintain information about the SQL statements and host variables used in the program.
Memory Considerations Avoiding Name Conflicts of the same program could show different memory sizes. This difference reflects the dynamic memory management provided by SQL/MP. The COBOL85 compilers initialize all the pointers in the SQLINALL structure in the SQL-INIT section, which the compilers writs at the end of the source program. To initialize the pointers, the compiles insert a call to the SQLADDR procedure.
Memory Considerations Avoiding Name Conflicts SQLVARS Structure If the SQL statement contains input or output host variables, the COBOL85 compilers create an SQLVARSnI structure for each input variable and an SQLVARSnO structure for each output variable as follows: 05 SQLVARSn{I|O}. 10 EYE-CATCHER 10 NUM-ENTRIES 10 VARSn. 15 DATA-TYPE 15 DATA-LEN 15 PRECISION 15 NULL-INFO 15 VAR-PTR 15 IND-PTR 15 CPRL-PTR 15 RESERVED PIC X(2) VALUE "D1". PIC S9(4) COMP VALUE num-entries.
Memory Considerations Using the SQLMEM Directive SQLBVARS Structure If the SLT-INDEX item of SQLINn is -1, the COBOL85 compilers creates an SQLBVARS entry. This entry occurs when a qualified cursor or statement name has a qualifying program name other than the current program name. The SQLBVARS entry follows the declaration of its corresponding SQLIN as follows: 05 SQLBVARSn. 10 EYE-CATCHER 10 NUM-ENTRIES 10 BVARS1. 15 DATA-TYPE 15 DATA-LEN 15 RESERVED0 15 VAR-PTR 15 IND-PTR 10 BVARS2.
Memory Considerations Estimating Memory Requirements Note. COBOL85 does not perform checkpointing on data in an extended data segment. Therefore, to ensure data integrity, HP recommends that you use the NonStop Transaction Management Facility (TMF). Estimating Memory Requirements A program that uses embedded SQL statements and directives to access an SQL/MP database uses more memory than a program that accesses an Enscribe database.
Memory Considerations ALTER BEGIN WORK CLOSE COMMENT CREATE DELETE DESCRIBE DESCRIBE INPUT Guidelines for Memory Use DROP END WORK EXECUTE EXECUTE IMMEDIATE FETCH FREE RESOURCES GET VERSION HELP TEXT INSERT LOCK TABLE OPEN RELEASE ROLLBACK WORK SELECT UNLOCK TABLE UPDATE UPDATE STATISTICS Do not count these SQL statements and directives: ° ° ° ° ° BEGIN DECLARE SECTION and END DECLARE SECTION CONTROL EXECUTOR, CONTROL QUERY, and CONTROL TABLE DECLARE CURSOR INVOKE WHENEVER Guidelines for Memory Use
C Maximizing Local Autonomy This appendix describes about the local autonomy in the NonStop SQL/MP network-distributed database, which ensures that a program can access data on the local node, regardless of the availability of SQL objects on remote nodes. In some cases, the design of NonStop SQL/MP allows for local autonomy. For example, if a DDL change alters a table on \NODEA when \NODEB is unavailable, an SQL program file on \NODEB that uses the altered \NODEA table is not marked as invalid.
Maximizing Local Autonomy Using TACL DEFINEs A program declares an SQL cursor as follows: EXEC SQL DECLARE GET_PART_CURSOR CURSOR FOR SELECT PARTNUM, PARTDESC, PRICE, QTY_AVAILABLE FROM =PARTS WHERE PARTS.PARTNUM < 5000 AND PARTS.PARTDESC = "V8 DISK OPTION" END-EXEC. The program running on \NEWYORK uses a DEFINE to associate the PARTS table with the first partition located at \NEWYORK: SET DEFINE CLASS MAP ADD DEFINE =parts, FILE \NEWYORK.$VOL1.SALES.
Maximizing Local Autonomy Skipping Unavailable Partitions applies to static or dynamic SQL statements that refer to partitioned tables and partitioned indexes of the tables.
Maximizing Local Autonomy Skipping Unavailable Partitions HP NonStop SQL/MP Programming Manual for COBOL85—429326-004 C- 4
D Converting COBOL Programs This appendix describes how a COBOL program developed for NonStop SQL/MP version 1 or version 2 software can execute on SQL/MP version 300 (or later) software without changes to its embedded SQL statements or directives. However, to use new SQL features, you must modify and recompile the program. Generating SQL Data Structures The SQLCA, SQLSA, and SQLDA data structures can change in new PVUs of SQL/MP.
Converting COBOL Programs Generating SQLDA Structures Generating SQLDA Structures If your existing COBOL program generates SQLDA structures and you are converting it to run on version 300 (or later) SQL/MP software, you might need one or more of these combinations of SQLDA structures: • • • A version 300 (or later) SQLDA structure A version 1 or version 2 SQLDA structure A version 300 (or later) SQLDA structure and a version 1 or version 2 SQLDA structure Generating a Version 315 SQLDA Structure To con
Converting COBOL Programs Generating a Version 2 SQLDA Structure 3. If you are converting a version 1 SQLDA structure, initialize the NULL-INFO and IND-PTR fields. (Your program should already initialize these fields for a version 2 SQLDA structure.) 4. Add an INCLUDE STRUCTURES directive with the ALL VERSION 2 option: EXEC SQL INCLUDE STRUCTURES ALL VERSION 2 END-EXEC. Or specify only the SQLDA VERSION 2 option: EXEC SQL INCLUDE STRUCTURES SQLDA VERSION 2 END-EXEC.
Converting COBOL Programs Generating a Version 2 SQLDA Structure Table D-2. Version 2 SQLDA Structure Fields (page 2 of 3) Field Name Description DATA-LEN DATA-LEN depends on the data type as follows: Fixed-length character The number of bytes in the string. Variable-length character The maximum number of bytes in the string. Decimal numeric Bits 0:7 contain the decimal scale. Bits 8:15 contain the byte length of the item. Binary numeric Bits 0:7 contain the decimal scale.
Converting COBOL Programs Generating a Version 1 SQLDA Structure Table D-2. Version 2 SQLDA Structure Fields (page 3 of 3) Field Name Description NULL-INFO For input parameters, NULL-INFO contains a negative integer if the column permits null values. For output columns, NULL-INFO contains a negative integer if the row returned is null. VAR-PTR The extended address of the actual data (value of input parameter or column).
Converting COBOL Programs Generating a Version 1 SQLDA Structure Example D-2 shows a version 1 SQLDA structure. Example D-2. Version 1 SQLDA Structure 01 sqlda-name. 05 EYE–CATCHER 05 NUM–ENTRIES 05 SQLVAR [ –R1 ] 10 DATA–TYPE 10 DATA–LEN 10 RESERVED–0 10 VAR–PTR 10 RESERVED 01 names-buffer-name PIC X(2) VALUE "DA". PIC S9(4) COMP. OCCURS sqlvar-count TIMES. PIC S9(4) COMP. NATIVE–2. PIC S9(4) COMP. PIC S9(9) COMP. PIC S9(9) COMP. PIC X( length ).
Converting COBOL Programs Using a Combination of SQLDA Structures Table D-3. Version 1 SQLDA Structure Fields (page 2 of 2) Field Name Description DATA-TYPE The data type of the parameter or output variable. DATA-LEN The DATA-LEN value depends on the data type as follows: Fixed-length character The number of bytes in the string. Variable-length character The maximum number of bytes in the string. Decimal numeric Bits 0:7 contain the decimal scale. Bits 8:15 contain the byte length of the item.
Converting COBOL Programs Planning for Future PVUs Planning for Future PVUs If you are converting a COBOL program developed for SQL/MP version 1 or version 2 software to use version 300 (or later) features and to run on SQL/MP version 300 (or later) software, consider making these changes in your program for compatibility with future SQL/MP PVUs.
E Writing Pathway Servers Writing a Pathway application that accesses a NonStop SQL/MP database is similar to writing an application that accesses an Enscribe database. In either case, you manage terminal requests and displays in the requester portion of the application and perform database manipulation in the servers. When coding a Pathway application that accesses an SQL/MP database, you embed the SQL DML statements in the server code. Most Pathway servers use subroutines.
Writing Pathway Servers PERFORM Model The sample code for the PERFORM model server is shown in Example E-1. Example E-1. PERFORM Model (page 1 of 3) IDENTIFICATION DIVISION. PROGRAM-ID. perform-model-sql. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. Tandem NonStop. OBJECT-COMPUTER. Tandem NonStop. INPUT-OUTPUT SECTION. FILE-CONTROL. SELECT msg-in ASSIGN TO $RECEIVE FILE STATUS IS receive-file-status. SELECT msg-out ASSIGN TO $RECEIVE FILE STATUS IS receive-file-status. DATA DIVISION.
Writing Pathway Servers PERFORM Model Example E-1. PERFORM Model (page 2 of 3) WORKING-STORAGE SECTION. 01 receive-file-status. 02 stat-1 PIC 9. 88 close-from-requester VALUE 1. 02 stat-2 PIC 9. EXEC SQL BEGIN DECLARE SECTION END-EXEC. * The definition of PARTS-RECORD should be an INVOKE directive. 01 parts-record. 02 partnum 02 partname 02 inventory 02 location 02 price PIC PIC PIC PIC PIC EXEC SQL END DECLARE SECTION EXEC SQL INCLUDE SQLCA 9(4). X(18). S999 COMP. XXX. 9(6)V99 COMP. END-EXEC.
Writing Pathway Servers CALL Model: SQL Main Program Example E-1. PERFORM Model (page 3 of 3) update-parts. MOVE partnum OF parts-info TO partnum OF parts-record. MOVE inventory OF parts-info TO inventory OF parts-record. EXEC SQL UPDATE $mkt.sample.parts SET inventory = :parts-record.inventory WHERE partnum = :parts-record.partnum END-EXEC. insert-parts.
Writing Pathway Servers CALL Model: SQL Main Program The reply message fields REPLY-CODE and ERROR-CODE are set as follows: Reply Message REPLY-CODE ERROR-CODE Operation successful 0 N.A. Error, operation not performed 9999 Positive SQLCODE value (transformed from original negative value) Warning, but operation successful 9998 Positive SQLCODE value Invalid ENTRY-TYPE 9997 N.A. CALL error occurred 9996 N.A.
Writing Pathway Servers CALL Model: SQL Main Program Example E-2. CALL Model Main Program (page 2 of 3) DATA DIVISION. FILE SECTION. FD msg-in LABEL RECORDS ARE OMITTED. * The definition of ENTRY-MSG should be a COPY statement. 01 entry-msg. 02 pw-header. 04 reply-code PIC S9(4) COMP. 04 application-code PIC XX. 04 function-code PIC XX. 04 trans-code PIC 99. 04 term-id PIC X(15). 04 log-request PIC X. 02 entry-type PIC X. 02 parts-info. 04 partnum PIC 9(4). 04 partname PIC X(18).
Writing Pathway Servers CALL Model: SQL Main Program Example E-2. CALL Model Main Program (page 3 of 3) PROCEDURE DIVISION. MAIN-SECTION SECTION. a-init. OPEN INPUT msg-in. OPEN OUTPUT msg-out SYNCDEPTH 1. PERFORM b-trans UNTIL close-from-requester. STOP RUN. b-trans. MOVE SPACES to entry-reply, entry-msg. MOVE ZERO to reply-code OF entry-reply. READ msg-in AT END STOP RUN END-READ. MOVE pw-header OF msg-in TO pw-header OF msg-out.
Writing Pathway Servers CALL Model: SQL Subprograms CALL Model: SQL Subprograms Following are the two subprograms that are called to perform SQL operations on the PARTS table.The reply message fields REPLY-CODE and ERROR-CODE are set as follows: Reply Message REPLY-CODE ERROR-CODE Operation successful 0 N.A.
Writing Pathway Servers UPDATE Subprogram Example E-3. SQL UPDATE Subprogram (page 2 of 2) * The definition of these LINKAGE parameters should be COPY * statements. 01 reply-code PIC S9(4) COMP. 01 error-code PIC S9(4) COMP. PROCEDURE DIVISION USING parts-params, reply-code, error-code. MAIN-SECTION SECTION. 00-whenever. EXEC SQL WHENEVER NOT FOUND PERFORM :sql-notfnd EXEC. EXEC SQL WHENEVER SQLERROR PERFORM :sql-error EXEC. EXEC SQL WHENEVER SQLWARNING PERFORM :sql-warning EXEC.
Writing Pathway Servers INSERT Subprogram INSERT Subprogram The SQL subprogram INSERT-PARTS that inserts new parts into the PARTS table is illustrated in Example E-4. Example E-4. SQL INSERT Subprogram (page 1 of 2) IDENTIFICATION DIVISION. PROGRAM-ID. insert-parts. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. Tandem NonStop. OBJECT-COMPUTER. Tandem NonStop. DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC.
Writing Pathway Servers INSERT Subprogram Example E-4. SQL INSERT Subprogram (page 2 of 2) PROCEDURE DIVISION USING parts-params, reply-code, error-code. MAIN-SECTION SECTION. 00-whenever. EXEC SQL WHENEVER NOT FOUND CONTINUE EXEC. EXEC SQL WHENEVER SQLERROR PERFORM :sql-error EXEC. EXEC SQL WHENEVER SQLWARNING PERFORM :sql-warning EXEC. start-program. MOVE partnum record. MOVE partname record. MOVE inventory record. MOVE location record. MOVE price record.
Writing Pathway Servers INSERT Subprogram HP NonStop SQL/MP Programming Manual for COBOL85—429326-004 E -12
Index A Accelerator compiling process 1-5 effect on SQL validity 8-2 OSS environment 6-15 OSS environment variable 6-17 to optimize object code 6-24 Access authority for a cursor 4-13 for a DELETE statement multirow 4-22 single-row 4-10 for a FETCH statement 4-17 for a SELECT statement multirow 4-19 single-row 4-4 for an INSERT statement 4-6 for an INVOKE directive 2-13 for an OPEN statement 4-16 for an UPDATE statement 4-8 for an UPDATE STATISTICS statement 6-33 to run an SQL program file 7-1 to run the SQ
Index C BLANK clause, host variable declaration 2-9 Buffer invalidation, cursor operations 4-14 BUILD command, Binder program 6-23 C Case sensitivity, SQL identifiers 4-15 CAST function 2-5 Catalog version CHECK options 8-10 SQLGETCATALOGVERSION 5-24 Character processing rules (CPRL) procedures 11-1 CHARACTER SET clause, host variable declaration 2-24 Character set, associating with a host variable 2-24 CHECK INOPERABLE PLANS, SQLCOMP option 8-10 CLOSE statement cursor operations 4-13 description 4-23 CO
Index D Constraints considerations 9-15 creating 9-14 CONTROL directives description 6-40 with dynamic SQL statements 6-42 with static SQL statements 6-41 CONTROL EXECUTOR, DCL statement 3-5 CONTROL QUERY, DCL statement 3-5 CONTROL TABLE directive description 3-5 OPEN ALL option 4-2 CONVERTTIMESTAMP, system procedure and SQL function 4-8 COPY COBOL statement 6-9 SQL utility 4-2 COST, SQLCA structure field 9-12 CPRL error codes 11-2 CPRL procedures CPRL_AREALPHAS_ 11-4 CPRL_ARENUMERICS_ 11-5 CPRL_ARE_ 11-3
Index D Data conversion between SQL and COBOL data types 2-4 dynamic SQL statements 2-5 Data Declaration directives 3-3, 3-4 Data Definition Language (DDL) statements 3-4 Data description clauses, COBOL 2-8 Data Division Declare Section 2-1 statement placement 3-3 Data Manipulation Language (DML) statements 3-5 Data retrieval statements 4-1 Data Status Language (DSL) statements 3-6 Data structures, internal SQL B-1 Data types, SQL and COBOL correspondence 2-2 DATA-LEN, SQLDA structure field 10-12 checking
Index E Dynamic SQL declaring host variables 10-10 declaring SQLCA and SQLSA data structures 10-10 defining storage for parameters 10-10 description 1-6 handling parameters 10-20 indicator parameters 10-8 input parameters 10-6 interface with Pathway 10-35 output parameters 10-6 parameter list 10-6 parameters in a loop 10-7 PREPARE statement 10-18 processing database requests 10-4 processing nonSELECT statements 10-29 processing SELECT statements 10-30 programming techniques 10-4 prompting user for input 1
Index F Errors and warnings (continued) SQLCAFSCODE procedure 5-17 SQLCATOBUFFER procedure 5-34 SQLCA_DISPLAY2_ procedure 2-5, 5-4 SQLCA_TOBUFFER2_ procedure 5-11 SQLCODE variable 1-5 SQLMSG file 5-2 SQL/MP system procedures 5-1 WHENEVER directive 9-6 Errors table 9-16 ERRORS-ALL, SQLCA structure field 9-12 Error-Checking directive, WHENEVER 3-6 ERROR-LOCATION, SQLCA structure field 9-12 ESCALATIONS, SQLSA structure field 9-23 EXCLUSIVE MODE, SELECT statement 4-6 EXECUTE IMMEDIATE, dynamic SQL statement 3
Index I Host variables (continued) associating with a character set 2-24 COBOL statements 2-25 creating with INVOKE directive 2-13 declaring 1-2, 2-1 description 1-1, 2-1 initializing 4-16 naming conventions 2-2 syntax for specifying 2-6 use of colon when specifying 1-2, 2-6 HOSV (host object SQL version) 6-44 I IN EXCLUSIVE MODE, SELECT statement 4-6, 4-13 IN SHARE MODE, SELECT statement 4-6 INCLUDE SQLCA directive description 3-3 example 9-4 syntax 9-11 INCLUDE SQLCODEX directive 3-4 INCLUDE SQLDA dire
Index J INVOKE directive (continued) PREFIX clause 2-21 SUFFIX clause 2-21 through SQLCI 2-23 INVOKE SQLCODEX statement 9-6 J JULIANTIMESTAMP system procedure 4-8 JUSTIFIED clause, host variable declaration 2-9 L Level-88 items, INCLUDE SQLCODEX 9-5 Load time, SQL 8-6 LOAD, SQL utility 4-2 Local partition, maximizing local autonomy C-1 LOCK TABLE, DCL statement 3-6 Loops, avoiding with WHENEVER 9-8 Lost Open error causes 4-2 recovery 4-3 M Memory guidelines B-6 requirements B-5 MESSAGES, SQLSA structur
Index O NUM-ERRORS, SQLCA structure field 9-12 NUM-ERR-ENTRIES, SQLCA structure field 9-12 NUM-TABLES, SQLSA structure field 9-22 OUTPUT-COLLATIONS-LEN, SQLSA structure field 9-23 OUTPUT-NAMES-LEN, SQLSA structure field 9-23 OUTPUT-NUM, SQLSA structure field 9-23 O P OBEY file format, EXPLAIN report 6-39 Object file, TACL RUN command 7-2 OCCURS clause, host variable declaration 2-9 OPEN ALL option, CONTROL TABLE directive 4-2 OPEN statement considerations 4-17 cursor operations 4-13, 4-16 description
Index R PICTURE clause (continued) host variable declaration 2-9 numeric data 2-8 variable-length character data 2-7 PIN (process identification number) 7-4 PRECISION, SQLDA structure field checking for character set ID 10-22 description 10-12 PREFIX clause, INVOKE directive 2-21 PREPARE, dynamic SQL statement 3-6 PREPARE, SQLSA structure field 9-23 Primary key, SELECT statement 4-5 Procedure Division, statement placement 3-3 PROCEDURE-ID, SQLCA structure field 9-12 Process Access ID (PAID) See access aut
Index S SELECT statement (continued) with a cursor 4-1, 4-19 SENSITIVE flag, program file label 8-1 SHARE MODE, SELECT statement 4-6 SHOW DEFMODE command 6-5 SIGN clause, host variable declaration 2-9 Similarity check at run time 8-9 collations 8-14 COMPILE-TIME-TABLE 8-11 description 8-9 enabling for tables and protection views 8-10 rules for protection views 8-13 rules for tables 8-11 RUN-TIME-TABLE 8-11 syntax 8-10 SIMILARITYCHECK column, TABLES table 8-11 SIMILARITYINFO column, PROGRAMS table 8-10 SOU
Index S SQLCA structure (continued) description 1-5 example 9-4 fields 9-12 information returned 5-4 number of rows deleted 4-11 number of rows inserted 4-6 number of rows updated 4-8 returning information 9-11 SQLCAGETINFOLIST procedure 5-17 SQLCA_DISPLAY2_ procedure 1-4 SQLCADISPLAY procedure description 5-1 syntax 5-30 SQLCAFSCODE procedure description 5-1 syntax 5-17, 9-11 SQLCAGETINFOLIST procedure description 5-1 syntax 5-18, 9-11 SQLCATOBUFFER procedure description 5-1 syntax 5-34 SQLCA-RESERVED, S
Index S SQLGETCATALOGVERSION system procedure description 5-1 syntax 5-24 SQLGETOBJECTVERSION system procedure description 5-1 syntax 5-25 SQLGETSYSTEMVERSION system procedure description 5-1 syntax 5-26 SQLINALL structures B-1 SQLMAP option, SQLCOMP command 9-2 SQLMEM, COBOL85 compiler directive 3-7, B-4 SQLMSG file description 5-2 file number 5-31, 5-36 with SQLCA_DISPLAY2_ procedure 5-36 SQLSA structure FETCH statement 4-17 fields 9-22 guidelines 9-21 SQLSADISPLAY procedure description 5-1 syntax 5-27
Index T Statements embedding 3-1 format 3-1 Static SQL statements, COBOL program 1-4 Statistics SQL compilation 6-33 SQLCADISPLAY procedure 5-32 SQLCAGETINFOLIST procedure 5-17 SQLSA 9-20 SQLSADISPLAY procedure 5-27 updating with UPDATE STATISTICS 6-33 STATS, SQLSA structure field 9-22 Storage allocation, parameters in dynamic SQL 10-10 STOREDDEFINES option, SQLCOMP command 6-5 SUBSYSTEM-ID, SQLCA structure field 9-13 SUFFIX clause, INVOKE directive 2-21 SUPPRESS-DISPLAY, SQLCA structure field 9-13 SWAPVO
Index V UPDATE statement description 3-5, 4-8 indicator variables 2-10 multiple rows 4-9 null value 4-10 single-row 4-9 SQLCODE values 4-8 with a cursor 4-1, 4-20 without a cursor 4-1 UPDATE STATISTICS statement 3-5, 6-33 UPDATE WHERE CURRENT clause, with cursors 10-32 USAGE clause, host variable declaration 2-9 USER-LINE-NUMBER, SQLCA structure field 9-12 USING DESCRIPTOR clause, with cursors 10-31 V VALID flag, program file label 8-1 Valid SQL program 8-1 Validation functions, of SQL compiler 8-1 VALUE
Index Special Characters Special Characters $SYSTEM.SYSTEM.
