HP NonStop SQL/MP Programming Manual for COBOL Abstract This manual documents the programming interface to HP NonStop™ SQL/MP for COBOL. It is intended for application programmers who are embedding SQL statements and directives in COBOL programs. Product Version NonStop SQL/MP G06 and H01 Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs, H06.03 and all subsequent H-series RVUs, G06.20 and all subsequent G-series RVUs, and D46.
Document History Part Number Product Version Published 429326-002 NonStop SQL/MP G06 May 2003 429326-003 NonStop SQL/MP G06 December 2003 429326-004 NonStop SQL/MP G06 December 2004 529758-001 NonStop SQL/MP G06 April 2005 529758-002 NonStop SQL/MP G06 and H01 August 2010 529758-003 NonStop SQL/MP G06 and H01 August 2012
Legal Notices © Copyright 2012 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
HP NonStop SQL/MP Programming Manual for COBOL Index Examples Figures Tables Legal Notices What’s New in This Manual xi Manual Information xi New and Changed Information xi About This Manual xiii Who Should Read This Guide xiii Related Manuals xiii Notation Conventions xvi HP Encourages Your Comments xix 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) SQLSADISPLAY 5-27 Superseded Procedures 5-30 SQLCADISPLAY 5-30 SQLCATOBUFFER 5-34 6.
6. Explicit Program Compilation (continued) Contents 6. Explicit Program Compilation (continued) HP COBOL Compiler 6-45 SQL Compiler 6-45 SQL Program File 6-45 7.
. Dynamic SQL Operations Contents 10.
. Character Processing Rules (CPRL) Procedures (continued) Contents 11. Character Processing Rules (CPRL) Procedures (continued) CPRL_GETNEXTINSEQUENCE_ 11-16 CPRL_GETNUMTABLE_ 11-17 CPRL_GETSPECIALTABLE_ 11-18 CPRL_GETUPSHIFTTABLE_ 11-19 CPRL_INFO_ 11-19 CPRL_READOBJECT_ 11-21 CPRL_UPSHIFT_ 11-22 A. SQL/MP Sample Database B.
E. Writing Pathway Servers (continued) Contents E. Writing Pathway Servers (continued) CALL Model: SQL Subprograms UPDATE Subprogram E-8 INSERT Subprogram E-10 E-8 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.
Figures Contents Figures Figure i. Figure 2-1. Figure 2-2. Figure 4-1. Figure 6-1. Figure 6-2. Figure 6-3. Figure 6-4. Figure 8-1. Figure 10-1. Figure 10-2. Figure A-1.
Contents HP NonStop SQL/MP Programming Manual for COBOL—529758-003 x
What’s New in This Manual Manual Information HP NonStop SQL/MP Programming Manual for COBOL Abstract This manual documents the programming interface to HP NonStop™ SQL/MP for COBOL. It is intended for application programmers who are embedding SQL statements and directives in COBOL programs. Product Version NonStop SQL/MP G06 and H01 Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs, H06.03 and all subsequent H-series RVUs, G06.
What’s New in This Manual Changes to the 529758-001 Manual Changes to the 529758-002 manual: Added applicability note for SQL integer data types on page 2-5 and page 2-8. Modified the ALTER TABLE and ALTER INDEX information under Causes of SQL Error 8204 (Lost Open Error) on page 4-2. Changes to the 529758-001 Manual Changed the manual title from HP NonStop SQL/MP Programming Manual for COBOL85 to HP NonStop SQL/MP Programming Manual for COBOL.
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. The HP COBOL compiler means both the COBOL85 compiler and the NMCOBOL compiler. 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 i. 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 ii. 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 iii. 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.
Change Bar 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.
HP Encourages Your Comments About This Manual margin of changed portions of text, figures, tables, examples, and so on. Change bars highlight new or revised information. For example: The message types specified in the REPORT clause are different in the HP COBOL environment and the Common Run-Time Environment (CRE). The CRE has many new message types and some new message type codes for old message types. In the CRE, the message type SYSTEM includes all messages except LOGICAL-CLOSE and LOGICAL-OPEN.
About This Manual HP Encourages Your Comments HP NonStop SQL/MP Programming Manual for COBOL—529758-002 xx
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.
Development of a COBOL Program Introduction Development of a COBOL Program You can embed static or dynamic SQL statements in a COBOL source file. You embed a static SQL statement as an actual SQL statement and run the SQL compiler to explicitly compile the statement before you run the program. To embed a dynamic SQL statement, code a placeholder variable for the statement, and then construct, SQL compile, and execute the statement at run time.
SQL/MP Statements and Directives Introduction 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 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 Use class MAP DEFINEs to specify SQL objects—tables, views, indexes, and collations—and class CATALOG DEFINEs to specify SQL catalogs). 2. Run an HP COBOL compiler (COBOL85 or the NMCOBOL compiler), specifying a source file containing embedded SQL statements as input. 3.
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 For more information, see Running the HP COBOL Compilers on page 6-12. 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. The COBOL85 for NonStop Systems Manual contains detailed information on using COBOL in the OSS environment.
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-3. 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 Place a Declare Section in the Data Division. You can specify more than one Declare Section in a program, if necessary, but you cannot nest Declare Sections. Do not place a Declare Section within a COBOL record description. The first item after the BEGIN DECLARE SECTION directive must have level 01. The only directives you can specify in a Declare Section are the COBOL compiler SOURCE directive and the SQL INVOKE directive.
Using Corresponding SQL and COBOL Data Types Host Variables Table 2-1. Corresponding SQL and COBOL Data Types (page 1 of 2) SQL/MP Data Type COBOL Data Type Fixed-Length Character Data Type CHARACTER (l) PIC X(l). PIC X(l) Fixed-Length Character Data Type With CHARACTER SET Clause CHARACTER (l) CHARACTER SET charset PIC X(l) CHARACTER SET charset 01 column-name CHARACTER SET charset PIC X(l).
Using Corresponding SQL and COBOL Data Types Host Variables Table 2-1. Corresponding SQL and COBOL Data Types (page 2 of 2) SQL/MP Data Type COBOL Data Type NUMERIC (5 to 9,s) UNSIGNED PIC NUMERIC (10 to 18,s) SIGNED PIC S9(18-s)V9(s) COMP. DECIMAL (l,s) SIGN IS LEADING PIC S9(l-s)V9(s) DISPLAY SIGN IS LEADING. DECIMAL (l,s) UNSIGNED PIC 9(l-s)V9(s) DISPLAY. PIC 9(l-s)V9(s) COMP Same as NUMERIC. 9(9-s)V9(s) COMP. Numeric Data Types PIC 9(l-s)V9(s) Same as DECIMAL.
Using Corresponding SQL and COBOL Data Types Host Variables Note. To retrieve floating-point columns, you must declare all the required host variables with corresponding data types supported by COBOL. (Floating-point columns will be handled during data type conversion.) For example, you must declare numeric host variables, like REAL, FLOAT, and DOUBLE PRECISION, as a numeric data type supported by COBOL.
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 HP COBOL 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): 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): 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 COBOL Data Description Clauses Host Variables Using COBOL Data Description Clauses The next table summarizes the COBOL data description clauses and their interpretation by SQL/MP when they are used in host variable declarations. SQL/MP does not support the COBOL special names option DECIMAL POINT IS COMMA. 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.
Using Date-Time and INTERVAL Data Types Host Variables TIME Represents a time and is equivalent to DATETIME HOUR TO SECOND TIMESTAMP Represents a date and time and is equivalent to DATETIME YEAR TO FRACTION(6) INTERVAL Represents a duration of time as a year-month or day-time interval HP NonStop SQL/MP Programming Manual for COBOL—529758-003 2-10
Using Indicator Variables for Null Values Host Variables Declare date-time values as character data types and then use the TYPE AS clause to direct SQL/MP to interpret the value in the host variable as a date-time or INTERVAL value. Sample TYPE AS clauses are: 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.
Testing for a Null Value Host Variables 02 RETIRE-IND PIC S9(4) COMP. EXEC SQL END DECLARE SECTION END-EXEC. ... PROCEDURE DIVISION. ... 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.
Retrieving Rows With Null Values Host Variables ... 0150-SELECT. EXEC SQL FETCH GET-PRODNUM INTO :PRODNUM, :DATE-SHIPPED INDICATOR :SHIP-IND 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. ...
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 COBOL-clause is a COBOL clause such as VALUE or USAGE. For a description of the COBOL 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.
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. Topics include: Embedding SQL Statements Finding Information on page 3-3 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 Precede an SQL statement with a COBOL section or paragraph name. 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.
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 HP NonStop SQL/MP Programming Manual for COBOL—529758-003 3-8 Finding Information
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. Topics include: Opening and Closing Tables and Views on page 4-2 Single-Row SELECT Statement on page 4-4 INSERT Statement on page 4-6 UPDATE Statement on page 4-8 DELETE Statement on page 4-10 Using SQL Cursors on page 4-12 Table 4-1 provides some guidelines for using these statements. Table 4-1.
Data Retrieval and Modification Opening and Closing Tables and Views Note. Using a cursor can sometimes degrade a program’s performance. A cursor operation requires three statements (OPEN, FETCH, and CLOSE), which increase the messages between the file system and disk process. Therefore, consider not using a cursor if a single-row SELECT statement is sufficient.
Data Retrieval and Modification Recovering From SQL Error 8204 ALTER INDEX with LOCKLENGTH ALTER VIEW with RENAME 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 not yet opened the cursor. 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 SQL/MP scans the database to find the first row indicated by CUSTNUM and then returns this row to the program. Because CUSTNUM is not a primary key, SQL/MP 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.
Using IN SHARE MODE or IN EXCLUSIVE MODE Data Retrieval and Modification EMPLOYEE.DEPTNUM INTO :EMPLOYEE.FIRST-NAME, :EMPLOYEE.LAST-NAME, :EMPLOYEE.DEPTNUM 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. ...
Data Retrieval and Modification Inserting a Timestamp 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. ...
Updating a Single Row Data Retrieval and Modification SQL/MP returns these values to SQLCODE after an UPDATE statement. SQLCODE Value Description The UPDATE statement was successful. 0 100 No rows were found on a search condition. < 0 An error occurred; SQLCODE contains the error number. > 0 (not 100) A warning occurred; SQLCODE contains the first warning number. The UPDATE statement updates rows in sequence.
Data Retrieval and Modification Updating Columns With Null Values than HOSTVAR-MIN-SALARY. A user enters the values for HOSTVAR-INC and HOSTVAR-MIN-SALARY. EXEC SQL UPDATE PERSNL.EMPLOYEE SET SALARY = SALARY * :HOSTVAR-INC WHERE SALARY < :HOSTVAR-MIN-SALARY END-EXEC. This example updates all rows in the EMPLOYEE. DEPTNUM column that contain the value in HOSTVAR-OLD-DEPTNUM.
Deleting a Single Row Data Retrieval and Modification or views in subqueries of the search condition. For information about process access, see Required Access Authority on page 7-1. SQL/MP returns these values to SQLCODE after a DELETE statement. SQLCODE Value Description The DELETE statement was successful. 0 100 No rows were found on a search condition. < 0 An error occurred; SQLCODE contains the error number. > 0 (not 100) A warning occurred; SQLCODE contains the first warning number.
Using SQL Cursors Data Retrieval and Modification Using SQL Cursors An SQL cursor is a named pointer that a host language program (C, COBOL, Pascal, or TAL) can use to access a set of rows in a table or view, one row at a time. Using a cursor, a program can process rows in the same way it might process records in a sequential file.
Data Retrieval and Modification Steps for Using a Cursor Steps for Using a Cursor These steps are shown in Figure 4-1 on page 4-12. Each step is described in detail on subsequent pages in this section. 1. Declare any host variables you plan to use with the cursor. 2. Name and define the cursor by using a DECLARE CURSOR statement. Follow the conventions for an SQL identifier for the cursor name.
Cursor Position Data Retrieval and Modification A program can use a cursor whose declaration does not specify FOR UPDATE to locate rows in a table to delete. SQL/MP tests the table only for read access when the OPEN statement executes. However, because a DELETE operation requires write access, SQL/MP checks for write access when you execute the DELETE statement. A program contending for data access with other users can specify the IN EXCLUSIVE MODE clause in the associated SELECT statement.
Data Retrieval and Modification Virtual Sequential Block Buffering (VSBB) FOR UPDATE clause. In this case, your cursor points to the actual data and has cursor stability. Virtual Sequential Block Buffering (VSBB) The SQL/MP optimizer often uses Virtual Sequential Block Buffering (VSBB) as an access path strategy. Conflicting UPDATE, DELETE, or INSERT statements can invalidate a cursor’s buffering for a table.
Data Retrieval and Modification OPEN Statement CLOSE statements, that refer to the cursor. The DECLARE CURSOR statement must also be within the scope of the statements that refer to the cursor. The DECLARE CURSOR statement does not affect the values in the SQLCA and SQLSA data structures. 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).
FETCH Statement Data Retrieval and Modification If a host variable contains old values from the previous execution of the program, a subsequent FETCH statement uses these old values as the starting point to retrieve data. Therefore, the FETCH does not begin at the expected location in the result table. The host variables must also be declared within the scope of the OPEN statement.
Data Retrieval and Modification FETCH Statement the DECLARE CURSOR, OPEN, INSERT, DELETE, UPDATE, and CLOSE statements that refer to the cursor. SQL/MP resets values in an SQLSA structure immediately before a FETCH statement executes. If you use an SQLSA value elsewhere in your program, save the value in a variable immediately after the FETCH statement executes.
Data Retrieval and Modification Multirow SELECT Statement * Process the retrieved values in the host variables. ... 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 * Process row values returned to host variables. ... EXEC SQL CLOSE GET-NAME-ADDRESS END-EXEC. 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.
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 Element Description Records Used The number of records actually used by the statement Disc Reads The number of disk reads caused by accessing this table Message Count The number of messages sent to execute operations on this table Message Bytes The number of message bytes sent to access this table Lock WE A flag indicating either that lock waits occurred (W) or that lock escalations occurred (E) for the table Example 5-4 shows the information SQLSADISPLAY
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 an HP COBOL program containing embedded SQL statements and directives in the Guardian, OSS, and PC host environments using TNS and TNS/R compilation tools.
TNS Mode Compilation Explicit Program Compilation TNS Mode Compilation A TNS-compiled program uses TNS process and memory architecture and consists of TNS object code (TNS instructions). Compiling an HP COBOL program in TNS mode enables you to execute the program on a TNS system. You can also execute TNS programs on TNS/R systems and boost the execution speed on those systems by generating accelerated object code after compilation.
Explicit Program Compilation TNS Mode Compilation To compile a COBOL program that contains embedded SQL statements and directives for execution on a TNS (or TNS/R) system: 1. Add any required class MAP or class CATALOG DEFINEs. 2. Run the COBOL85 compiler and specify a source file as input. Your compilation unit must include an SQLCODE variable declaration (either declared explicitly or implicitly with the INCLUDE SQLCA directive).
Native Mode Compilation for TNS/R Systems Explicit Program Compilation Native Mode Compilation for TNS/R Systems A natively compiled program for a TNS/R system uses native TNS/R process and memory architecture and consists of native object code (RISC instructions). Compiling an HP COBOL program in native TNS/R mode enables you to execute the program on a TNS/R system only. Figure 6-2 shows the steps you follow to explicitly SQL compile a COBOL program in TNS/R native mode. Figure 6-2.
Explicit Program Compilation Preparing for Compilation To compile a COBOL program that contains embedded SQL statements and directives for execution on a TNS/R system: 1. Add any required class MAP or class CATALOG DEFINEs. 2. Run the NMCOBOL compiler and specify a source file as input. Your compilation unit must include an SQLCODE variable declaration (either declared explicitly or implicitly with the INCLUDE SQLCA directive). You must specify the SQL compiler directive on the compiler command line. 3.
Requirements for Compiling a COBOL Program Explicit Program Compilation Requirements for Compiling a COBOL Program Before compiling the program, verify that the source code contains the required elements for compilation.
SQL Compiler Directive Explicit Program Compilation 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 debugger (for TNS/R) on a COBOL object file. However, the current source line indicated by the Inspect debugger depends on how you produced the object file.
Explicit Program Compilation SQL Compiler Directive Section location table (SLT) index number, which maps a single SQL statement to a table in the RTDU Source file name and number Source file line number The SQL map is sorted first by RTDU name and then by SLT index number. You can use this map to correlate MEASURE output with the SQL statements. The SQLMAP option also directs the compiler to include the HOSV version in the compiler listing.
Explicit Program Compilation Copying Source Code Into a Compilation Unit Copying Source Code Into a Compilation Unit To copy the COBOL source code from a separate file into a compilation unit, use one of these options: COBOL SOURCE directive COBOL COPY statement You cannot use SQL statements in a file you are copying with the COPY statement. For information about the COPY statement or SOURCE directive, see the COBOL85 for NonStop Systems Manual.
Setting DEFINEs Explicit Program Compilation 22> ADD DEFINE =collate1, CLASS MAP, FILE collate1 ... Note. For information on adding DEFINEs in the OSS environment, see Using DEFINEs in the OSS Environment on page 6-10. If you specify a DEFINE name in an SQL statement that is not in your current set of DEFINEs, the SQL compiler issues a warning message and leaves the statement uncompiled in the object file. When you run your program, the SQL executor automatically tries to recompile the SQL statement.
Using PARAM Commands Explicit Program Compilation Class MAP DEFINEs specified in INVOKE directives Class MAP or class CATALOG DEFINEs specified in SQL statements If you specify a class CATALOG DEFINE for the SQLCOMP CATALOG option when you run the SQL compiler using the cobol or nmcobol utility, add the DEFINE before you issue the cobol or nmcobol command.
Running the HP COBOL Compilers Explicit Program Compilation Running the HP COBOL Compilers The type of HP COBOL compiler that you can use to compile an embedded SQL/MP program depends on your operating environment and platform. Table 6-1 lists the HP COBOL compilers, their compilation mode, and the environment and server on which you can run the compilers. Table 6-1.
Explicit Program Compilation Running HP COBOL Compilers in the Guardian Environment Running HP COBOL Compilers in the Guardian Environment To run an HP COBOL compiler in the Guardian environment, see: Running the COBOL85 Compiler in the Guardian Environment Running the NMCOBOL Compiler in the Guardian Environment on page 6-14 Running the COBOL85 Compiler in the Guardian Environment To run the COBOL85 compiler in the Guardian environment, enter the COBOL85 command at the TACL prompt or from a TACL O
Explicit Program Compilation Running HP COBOL Compilers in the Guardian Environment the program. For example, this command directs the compiler to declare SQL data structures in the Extended-Storage Section: COBOL85 /IN MYSRC/ MYPROG; SQL; SQLMEM EXT For more information about the compiler directives, see the COBOL85 for NonStop Systems Manual.
Explicit Program Compilation Running HP COBOL Compilers in the Guardian Environment By default, the compiler generates an object file, RUNUNIT, qualified by the default system, volume, and subvolume names. To name the object file, specify a target file on the command line. For example, this command generates an object file, MYPROG: NMCOBOL /IN MYSRC/ MYPROG; SQL For natively compiled programs, the SQL directive is not accepted in the source code.
Explicit Program Compilation Running HP COBOL Compilers in the OSS Environment Running HP COBOL Compilers in the OSS Environment To run an HP COBOL compiler in the OSS environment, see: Changing Default Path Names and Disk Volume in the OSS Environment Running the cobol Utility in the OSS Environment on page 6-17 Running the nmcobol Utility in the OSS Environment on page 6-19 Changing Default Path Names and Disk Volume in the OSS Environment Table 6-3 lists the default path names of the programs
Running HP COBOL Compilers in the OSS Environment Explicit Program Compilation To change one or more of the defaults before executing the cobol or nmcobol command, use the export command. The effect of the export command lasts until you explicitly change the value of the export command. To execute a cobol or nmcobol command with a specified set of environment variables, use the OSS env function with the environment variables listed in Table 6-3.
Explicit Program Compilation Running HP COBOL Compilers in the OSS Environment To compile, bind, and SQL compile an embedded SQL/MP program, use this type of command: cobol -o /usr/mydir/myprog -L /nonnative/usr/lib -Wcobol="SQL" -Wsql="catalog \$vol.subvol" mysrc.cbl The -Wcobol flag directs the cobol utility to pass a string of compiler directives to the COBOL85 compiler. In this case, the SQL directive tells the COBOL85 compiler to expect embedded SQL in the source file.
Running HP COBOL Compilers in the OSS Environment Explicit Program Compilation To invoke the Binder program to bind object files into a program file and then invoke the SQL/MP compiler to SQL compile the program file, use this command: cobol -o myprog -L /nonnative/usr/lib -Wbind="set heap_max 64" -Wsql x.o y.o z.o The -Wbind flag directs the cobol utility to pass arguments to the Binder. In the previous example, the Binder sets the maximum heap size to 64 pages.
Explicit Program Compilation Running HP COBOL Compilers in the OSS Environment To compile, link, and SQL compile an embedded SQL/MP program, use this command: nmcobol -o /usr/mydir/myprog -Wsql="WHENEVERLIST" -Wsqlcomp="catalog \$vol.subvol" mysrc.cbl The -Wsql flag tells the NMCOBOL compiler to expect embedded SQL in the source file and passes optional SQL directive options, such as WHENEVERLIST, to the NMCOBOL compiler.
Explicit Program Compilation -Wsqlconnect = mode The -Wnld or -Wnld_obey flag directs the nmcobol utility to pass arguments to the nld utility. In the previous example, the nld utility sets the maximum heap size to 64 pages. For more information, see the nld and noft Manual. Note. Run the linker before SQL compiling the program. -Wsqlconnect This option instructs the compiler about which security mode must be used while communicating with the NSK host.
Explicit Program Compilation Running the Native COBOL Cross Compilers in a PC Host Environment HP_NSK_CONNECT_MODE This environment variable is introduced in H06.25/J06.07 RVU and can be set to any of the following values: legacy secure_quiet secure_warn secure_err If the environment variable is set to any of the previous values, these values are used by the compiler to set the connection mode. If the environment variable is set to any other value, the compiler returns an error.
Explicit Program Compilation Binding and Linking Binding and Linking Binding TNS object files or linking native object files involves validating and resolving references to other programs or routines and collecting and modifying code and data blocks from one or more object files to produce a single object file. The Binder or linker is a tool that you can use to read, link, modify, and build executable object files.
Explicit Program Compilation The nld or ld Utility @SELECT LIST * OFF @BUILD progfile @EXIT Caution. The Binder STRIP command without the SYMBOLS or AXCEL option removes the Binder table from an object file. Without the Binder table, the SQL compiler cannot compile the program file, and the SQL executor cannot execute it. For more information on the Binder program, see the Binder Manual. For more information on binding HP COBOL programs, see the COBOL85 for NonStop Systems Manual.
Acceleration of TNS HP COBOL Programs Explicit Program Compilation Acceleration of TNS HP COBOL Programs The process of acceleration applies only to object files created in TNS mode by the COBOL85 compiler (or by the cobol utility in the OSS environment of a TNS/R system). Natively compiled programs cannot be accelerated. Accelerated object code improves the execution speed of TNS programs on TNS/R systems. To accelerate a TNS object file, use The Accelerator on page 6-24.
Explicit Program Compilation The Accelerator The Accelerator The Accelerator enables you to optimize TNS programs to run faster on TNS/R systems. A TNS object file that has been accelerated for a TNS/R system has the original TNS code plus the logically equivalent optimized RISC instructions.
Explicit Program Compilation SQL Compiler Functions SQL Compiler Functions Resolves names and expands SQL object names, including DEFINE names, using the current default volume and the current catalog, and then stores the DEFINE names in the SQL object file. Performs type checking for COBOL and SQL data types. Expands views.
Running the SQL Compiler in the Guardian Environment Explicit Program Compilation Running the SQL Compiler in the Guardian Environment To run the SQL compiler in the Guardian environment, enter the SQLCOMP command at the TACL prompt or from a TACL OBEY command file using this syntax: SQLCOMP / IN object-file [ , OUT [ list-file ] ] [ , run-option] [ , run-option ]... / [ compiler-option [ , compiler-option ]...
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment 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): [\node.]file \node is an optional node (system) name. \file is one of these Guardian names: [$volume-name.][subvolume-name.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment 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. CURRENTDEFINES | STOREDDEFINES specifies the set of DEFINEs used to interpret DEFINE names in the SQL statements in the program file. CURRENTDEFINES selects the current set of DEFINEs for compiling the program. CURRENTDEFINES is the default.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment FORCE | NOFORCE controls how errors affect SQL compilation. FORCE directs the SQL compiler to produce a valid, executable object file regardless of syntax errors. The SQL compiler writes the SQL source statements to the program file so that the statements can automatically be recompiled if executed at run time. Use the FORCE option to debug a program if you do not need to execute the SQL statements that generate errors.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment 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 executor 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 DEFINEs (if NORECOMPILE is not specified). The SQL executor does not attempt to execute any plans in the program without recompiling them. 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.
Explicit Program Compilation Running the SQL Compiler in the Guardian Environment COMPILE determines which SQL statements are compiled during an explicit SQL compilation. You can direct the SQL compiler to use the similarity check to determine if a statement’s execution plan from a previous compilation is operable. The SQL compiler then recompiles only the statements that fail the similarity check. Other SQL statements retain their existing plans.
Explicit Program Compilation Running the SQL Compiler in the OSS Environment If the program has not been previously compiled or if the program does not contain similarity information, the COMPILE INVALID PLANS option directs the SQL compiler to compile all SQL statements in the program. INOPERABLE PLANS directs the SQL compiler to explicitly compile these SQL statements: Statements with inoperable plans (invalid plans that fail the similarity check). Uncompiled statements with empty sections.
Explicit Program Compilation SQL Compiler Messages Be the local owner of the table or a remote owner with purge access to the table (or be the local super ID user) For information about process access, see Required Access Authority on page 7-1. In these examples, the first statement updates the statistics for all columns in the ORDERS table. The second statement updates the statistics columns in the primary key or clustering key or in any indexes for the table ODETAIL.
Explicit Program Compilation SQL Compiler Messages statement does not match the number of host variables, the compiler returns a warning message and assumes that you do not want to use either the extra columns or the extra host variables. Unavailable statistics. The SQL compiler does not have the necessary statistics for a table or view to optimize an execution plan. The compiler then uses statistics in the catalog to determine an optimized execution plan.
SQL Program File Format Explicit Program Compilation 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 (TNS programs) or linker (native programs), a file generated by the Accelerator (TNS programs only), or a file previously compiled by the SQL compiler. Figure 6-4 shows the format of an SQL program file. For an SQL program file that has accelerated object code, see Figure 6-3 on page 6-24. Figure 6-4.
SQL Compiler Listings Explicit Program Compilation 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 includes 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.
Using the EXPLAIN Utility Explicit Program Compilation Example 6-1. Sample SQL Compiler Listing of a COBOL Program (page 2 of 2) SQL - SLT Index 121 = 4, Run-Unit = NEWPART COMMIT WORK SQL - SLT Index = 5, Run-Unit = NEWPART 137 ROLLBACK WORK BINDER - OBJECT FILE BINDER - T9621D30 - (17JUL95) Copyright Tandem Computers Incorporated 1982-1995 SYSTEM \NEWYORK Object file \NEWYORK.$SQL.SQLPGMS.
Using the EXPLAIN Utility Explicit Program Compilation current default catalog. The SQL compiler uses the current set of DEFINEs and writes the output to the spooler location $S.#EXPLAIN: SQLCOMP / IN SQLPROG, OUT $S.#EXPLAIN / CATALOG $DISK2.
Using CONTROL Directives Explicit Program Compilation FILE ... guardian-name ... In the next example, the SQL compiler writes an execution plan and DEFINEs to the spooler location $S.#EXPLAIN. The OBEYFORM option directs the compiler to write the DEFINEs in OBEY command file format to the file named SETDEFS for subsequent execution. The catalog name is not included in the SQLCOMP command because it is stored in the program file.
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.
Using Compatible Components Explicit Program Compilation Using Compatible Components Before you compile an SQL program file, you might need to determine the versions of the HP COBOL compiler, SQL compiler (SQLCOMP), and all SQL program files to ensure that all components and files are compatible. HP COBOL Compiler The host SQL version (HSV) identifies the SQL version of the HP COBOL compiler.
Explicit Program Compilation SQL Program File This subsection describes the HOSV and its relationship to the HP COBOL compiler and SQL compiler. For more information about the PFV and PCV, see the SQL/MP Version Management Guide. The HP COBOL compiler generates the HOSV and stores the value in the object file. If multiple object files are bound together in a single target object file, the HOSV of the target object file is the newest (maximum) HOSV of the individual object files.
Explicit Program Compilation HP NonStop SQL/MP Programming Manual for COBOL—529758-003 6-48 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.
Using DEFINEs Program Execution temporarily gain a controlled subset of another user’s privileges. For more information about PROGID programs, see the Security Management Guide. Using DEFINEs Before running an SQL program file, you can specify DEFINE, PARAM, or ASSIGN commands. This subsection describes DEFINEs. For information about PARAM and ASSIGN commands, see the TACL Reference Manual.
Using the CREATEPROCESS Routine Program Execution program-file is the name of the SQL program file. For an explicit RUN command, TACL qualifies a partially qualified file name by using the =_DEFAULTS DEFINE. For an implicit RUN command, TACL searches for program-file in the TACL #PMSEARCHLIST variable. run-option is a RUN command run option as described in the TACL Reference Manual. param-set is one or more parameters to pass to the new process.
Program Execution Using the CLU_PROCESS_CREATE_ Routine Example 7-1. COBOL CREATEPROCESS Routine IDENTIFICATION DIVISION PROGRAM-ID. RUNSQL. ... ENVIRONMENT DIVISION. CONFIGURATION SECTION. ... SPECIAL-NAMES. FILE $SYSTEM.SYSTEM.COBOLLIB IS COBOL-LIBRARY. ... DATA DIVISION. WORKING-STORAGE SECTION. 01 SQL-PROGRAM-FILE PIC X(36). 01 CREATE-OPTION PIC 9999 COMP. 01 PRIMARY-CPU PIC S9999 COMP. 01 RETURN-STATUS PIC S9999 COMP. ... PROCEDURE DIVISION. ...
Running a Program in the OSS Environment Program Execution Running a Program in the OSS Environment After successfully compiling your COBOL program with the cobol or the nmcobol 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. If the current directory is in your search path, you can run your program by typing the name of the executable object file and pressing the Return key.
Interactive Commands Program Execution The information about D-series nodes also applies to G-series nodes. 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. If necessary, you can force the program to run at a low PIN interactively from a TACL process or programmatically from an application process.
Pathway Environment Program Execution 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. The HIGHPIN object-file attribute in the SQL program file is ON. A high PIN is available when the server runs.
Program Execution Determining Compatibility With the SQL Executor 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. To embed a static GET VERSION OF PROGRAM statement in a COBOL program, you must include the INTO clause with a host variable.
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).
Causes of Automatic Recompilation Program Invalidation and Automatic SQL 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: 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.
Preventing Automatic Recompilations Program Invalidation and Automatic SQL Recompilation 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.
Preventing Automatic Recompilations Program Invalidation and Automatic SQL Recompilation 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: 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 COBOL—529758-003 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.
Error and Status Reporting Generating Structures With Different Versions { SQLCA | SQLSA | SQLDA } VERSION are keywords that specify the SQLCA, SQLSA, or SQLDA structure, respectively. 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.
Sharing Structures Error and Status Reporting For more information about versions of NonStop SQL/MP, see the SQL/MP Version Management Guide. 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.
Error and Status Reporting Returning Error and Warning Information 04 TABLE-NAME PIC X(24). 04 RECORDS-ACCESSED PIC S9(9) COMP. 04 RECORDS-USED PIC S9(9) COMP. 04 DISC-READS PIC S9(9) COMP. 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.
Error and Status Reporting Checking the SQLCODE Identifier Using the SQLCODE Data Item The HP COBOL compiler does not automatically generate an SQLCA structure, which includes the SQLCODE data item. You must declare an SQLCODE identifier either explicitly as a PIC S9(4) COMP data item or implicitly with an INCLUDE SQLCA directive. You cannot specify SQLCODE both explicitly as a data item and implicitly with an INCLUDE SQLCA directive.
Error and Status Reporting Using the WHENEVER Directive PERFORM SQL-WARNING-8000 ... Using the SQLCODEX Data Item You can use level-88 items with an SQLCODE data item, such as: 1. Substitute an SQLCODEX data item for the SQLCODE data item. 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.
Using the WHENEVER Directive Error and Status Reporting This table indicates the HP COBOL compiler pseudocode that checks SQLCODE and the order in which the checks are made: Order Condition Compiler Pseudocode 1 NOT FOUND IF SQLCODE = 100 THEN action-specification 2 SQLERROR IF SQLCODE < 0 THEN action-specification 3 SQLWARNING IF SQLCODE > 0 AND SQLCODE NOT = 100 THEN action-specification action-specification is one of: PERFORM :host-identifier ; GOTO :host-identifier ; GO TO :host-identifier
Error and Status Reporting Using the WHENEVER Directive If another program is called within the error handling code, the position of the called program in the listing order determines the WHENEVER directive in effect. The context of the calling program has no effect. The listing order includes files copied into the program through a SOURCE directive. If a copied file contains a WHENEVER directive, that directive is in effect following the SOURCE directive.
Error and Status Reporting Using the WHENEVER Directive Example 9-1. Enabling and Disabling the WHENEVER Directive PROCEDURE-DIVISION. 0010-SET-UP. EXEC SQL WHENEVER SQLERROR PERFORM :9900-SQL-ERROR-HANDLER END-EXEC. ... 9900-SQL-ERROR-HANDLER. * Disable SQLERROR handling to prevent looping. EXEC SQL WHENEVER SQLERROR END-EXEC. ... EXEC SQL INSERT INTO ERRLOG ( ..., ERRORS_SQL, ...) VALUES ( ..., :SQLCODE-NUM, ...) END-EXEC. ... ... EXEC SQL INSERT INTO ERRLOG ( ..., ERRORS_SQL, ...) VALUES ( ...
Error and Status Reporting Using the WHENEVER Directive Using an Aggregate Function All aggregate functions except COUNT return a null value when operating on an empty set. If a host variable receives the null value as the result of an aggregate function, you must specify an indicator variable and test the result of the indicator variable. Otherwise, SQL/MP returns a “no indicator variable provided” condition instead of a “no rows found” condition.
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 HP COBOL 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: 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 SECURE "NNNN"
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.
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. The program 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.
Dynamic SQL Operations Using Dynamic SQL Developing a Dynamic SQL Application on page 10-9 Constructing a Server that Interfaces With Pathway on page 10-35 Sample Dynamic SQL Program on page 10-37 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.
Using Dynamic SQL Dynamic SQL Operations Figure 10-1. Static and Dynamic SQL Programs * COBOL program with static SQL statement. ... EXEC SQL INSERT INTO EMP VALUES ('BROWN', 6400)" ENDEXEC ... 1 2 3 * COBOL program with dynamic SQL statement. ... * Declare host variables: EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 STATEMENT-BUFFER PIC x(256) ... EXEC SQL END DECLARE SECTION END-EXEC. ... DISPLAY "Enter SQL statement to be executed". ACCEPT STATEMENT-VALUE OF STATEMENT-BUFFER. ...
Dynamic SQL Operations Uses for Dynamic SQL SQL Operation Static SQL Operation Dynamic SQL Operation Generate an application plan SQLCOMP Run time Store the application plan SQLCOMP Not applicable Execute the statement Run time Run time Uses for Dynamic SQL Dynamic SQL can be very useful if an application requires: Flexibility to construct SQL statements at run time (for example, an interactive interface similar to SQLCI but designed for an inexperienced user).
Dynamic SQL Operations Determining When to Use Dynamic SQL table name, column names, data types, and options entered by a user and stored in local variables. The program user sees only a series of prompts, such as ENTER THE TABLE NAME, ENTER THE FIRST COLUMN NAME, and so forth. Determining When to Use Dynamic SQL If you do not know the whole text of an SQL statement at development time but there are only a few alternatives, you might want to program the alternatives into your application.
Dynamic SQL Operations Using Parameters When you prepare a statement, the database environment generates an access plan and a description of the result set. Preparation is useful if you want to execute a statement multiple times with the previously generated access plan to minimize processing overhead. The EXECUTE statement executes a previously prepared dynamic SQL statement. You can use EXECUTE for any DDL, DML, or DCL statement except SELECT. (Use a cursor to process a SELECT statement.
Dynamic SQL Operations Using Parameters Input parameters function in much the same way as host variables in embedded SQL. An input parameter can appear in an SQL expression wherever a constant can appear. You specify input parameters in a statement as either a question mark (?) or a question mark plus a name (?VAL). Before execution, you assign values to the place held by each parameter. Unlike static SQL, dynamic parameters do not require length or data type definition before program compilation.
Dynamic SQL Operations Using Parameters value in the first host variable for both occurrences of parameter ?A. The value in the second host variable is used for parameter ?B, but the value in HOST-VAR3 is unused. For example, in this statement, SQL/MP uses the value in HOST-VAR1 for both occurrences of parameter ?A and the value in HOST-VAR2 for parameter ?B. The value in HOST-VAR3 is ignored. EXEC SQL EXECUTE EXECUTE-STATEMENT USING USING :HOST-VAR1, :HOST-VAR2, :HOST-VAR3 END-EXEC. Caution.
Dynamic SQL Operations Developing a Dynamic SQL Application * STORE THE VALUE IN AN INPUT BUFFER * POINTED TO BY IN-SQLDA. * EXECUTE THE STATEMENT USING EACH SUCCESSIVE VALUE. EXEC SQL EXECUTE S1 USING DESCRIPTOR :IN-SQLDA END-EXEC. * END OF LOOP Using Indicator Parameters A program uses an indicator parameter to indicate that a null value was entered for a parameter.
Dynamic SQL Operations Declaring a Host Variable 4. If you plan to execute the statement more than once, or if your statement includes input or output parameters, prepare the SQL statement to compile the statement dynamically and assign it a statement name. 5. Determine whether there are parameters in the SQL statement by examining the SQLSA. Then use the DESCRIBE INPUT and DESCRIBE statements as needed. 6.
Dynamic SQL Operations Defining Storage for Input and Output Parameters The SQLSA stores information about the statement. (In contrast, the SQLDA stores information about parameters.) Defining Storage for Input and Output Parameters This subsection describes how to allocate storage for parameters. The discussion starts with a description of the SQLDA structure and associated buffers.
Dynamic SQL Operations Defining Storage for Input and Output Parameters Table 10-2. SQLDA Structure Fields (page 1 of 2) Field Name Description EYE-CATCHER An identifying field that a program must initialize. SQL/MP does not return a value to EYE-CATCHER. NUM-ENTRIES The number of input or output parameters the SQLDA structure can accommodate. SQLVAR Group item that describes input parameters or database columns.
Dynamic SQL Operations Defining Storage for Input and Output Parameters Table 10-2. SQLDA Structure Fields (page 2 of 2) Field Name Description NULL-INFO For input parameters, NULL-INFO contains a negative integer if the parameter permits null values. For output parameters, NULL-INFO contains a negative integer if the parameter can return a null value. VAR-PTR The extended address of the actual data (value of input parameter or database column). SQL/MP does not return VAL-PTR.
Dynamic SQL Operations Defining Storage for Input and Output Parameters Table 10-3.
Dynamic SQL Operations Defining Storage for Input and Output Parameters Table 10-3. SQLDA DATA-TYPE Values (page 3 of 3) Value Type of Data 210 Minute to Fraction (INTERVAL) 211 Hour to Fraction (INTERVAL) 212 Day to Fraction (INTERVAL) 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 release-option specifies the version of the SQLDA structure generated by the HP COBOL compiler. RELEASE1 specifies SQL/MP version 1, and RELEASE2 specifies SQL/MP version 2. Note. Although the HP COBOL compiler supports the RELEASE1 and RELEASE2 options, HP might not support these options in a future PVU.
Dynamic SQL Operations Defining Storage for Input and Output Parameters COLLATION-BUFFER, which supports collations with a maximum length of 512 bytes each The INCLUDE STRUCTURES and INCLUDE SQLDA directives in the COBOL program are: DATA DIVISION. EXEC SQL INCLUDE STRUCTURES SQLDA VERSION 315 END-EXEC. ... EXEC SQL INCLUDE SQLDA (SQLDAX, 20, NAMES-BUFFER, 30, CPRULES COLLATION-BUFFER, 512) END-EXEC. The HP COBOL compiler generates this SQLDA structure: 01 SQLDAX.
Dynamic SQL Operations 03 PDECIMAL 03 PLARGINT 03 PSMLINT Preparing the SQL Statement REDEFINES P REDEFINES P REDEFINES P PIC S9(9)V9(3) DISPLAY. PIC 9(18) COMP. PIC 9(4) COMP. Declare buffers in working storage. Use redefines to store differing data types. 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.
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. The HP COBOL compiler 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, this sequence was described in Prompting the User for Input Values on page 10-24: 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. Display the name with the 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 an HP 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, which 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, which 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 COBOL—529758-003 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 COBOL—529758-003 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 Topics include: SQL/MP Internal Structures Resizing Segments on page B-2 Avoiding Name Conflicts on page B-2 Using the SQLMEM Directive on page B-4 Estimating Memory Requirements on page B-5 Memory Requirements
Memory Considerations Resizing Segments perform this function, and you do not need to allocate space in your data stack or your Extended-Storage Section for these data structures. Resizing Segments The SQL executor and any utilities that use the SQLINALL data structure use Guardian procedures for resizing an existing extended segment. Therefore, you do not need to be concerned about the size of the extended data segments that NonStop SQL/MP uses to store the SQLINALL data structure.
Memory Considerations Avoiding Name Conflicts If the compiler cannot determine the procedure name, PROCEDURE-ID is replaced by this declaration, and the executor provides the name later. 10 BVARS-PTR 10 FILLER PIC S9(9) COMP VALUE -999999. PIC X(27). SQLVARS Structure If the SQL statement contains input or output host variables, the HP COBOL compiler creates an SQLVARSnI structure for each input variable and an SQLVARSnO structure for each output variable: 05 SQLVARSn{I|O}.
Memory Considerations Using the SQLMEM Directive SQLBVARS Structure If the SLT-INDEX item of SQLINn is -1, the HP COBOL 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: 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. The HP COBOL compiler 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. Topics include: Using a Local Partition Using TACL DEFINEs on page C-2 Using Current Statistics on page C-2 Local autonomy in the NonStop SQL/MP network-distributed database 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.
Maximizing Local Autonomy Using TACL DEFINEs This example shows the concept of maximizing local autonomy. The PARTS table is a partitioned table that resides on the \NEWYORK and \PARIS nodes: \NEWYORK The first partition contains all rows in which PARTS.PARTNUM (the primary key) is less than 5000. \PARIS The second partition contains all rows in which PARTS.PARTNUM is 5000 or greater. An index on the PARTDESC column of table PARTS, is named IXPART.
Maximizing Local Autonomy Skipping Unavailable Partitions your query does not try to retrieve any rows from the unavailable partition. Executing the UPDATE STATISTICS statement can eliminate both these problems. Skipping Unavailable Partitions Use the SKIP UNAVAILABLE PARTITION option of the CONTROL TABLE directive to cause SQL/MP to skip a partition that is not available and to open the next available partition that satisfies the search condition of a query.
Maximizing Local Autonomy Skipping Unavailable Partitions HP NonStop SQL/MP Programming Manual for COBOL—529758-003 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.
Converting COBOL Programs Generating SQLDA Structures INCLUDE STRUCTURES directive. For more information, see Section 9, Error and Status Reporting. Use the SQLCAGETINFOLIST procedure to return information from the SQLCA structure. Do not access this structure directly. HP reserves the right to change it in future PVUs.
Converting COBOL Programs Generating a Version 2 SQLDA Structure 1. If necessary, remove the RELEASE2 option from the SQL compiler directive or the INCLUDE SQLDA directive. The HP COBOL compiler returns an error if you specify the RELEASE2 option and the INCLUDE STRUCTURES directive. 2. If you specified the RELEASE2 option in an INCLUDE SQLDA directive, remove any -R2 suffixes you appended to SQLDA field names. 3.
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. 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). SQL/MP does not return VAL-PTR.
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. 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 9(4). X(18). S999 COMP. XXX. 9(6)V99 COMP. EXEC SQL END DECLARE SECTION EXEC SQL INCLUDE SQLCA 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 COBOL—529758-003 E-12
Index A Accelerator compiling process 1-5 effect on SQL validity 8-2 OSS environment 6-18 OSS environment variable 6-16 to optimize object code 6-14, 6-24 Access authority 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 UPDATE statement 4-8 for an UPDATE STATISTICS statement 6-35 to run an SQL program file 7-1 to run the SQL compiler 6-25 Access path determined
Index C 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 CLU_PROCESS_CREATE_ routine 7-4 COBOL compiler directives placement 3-2 SOURCE 3-7, 6-9 SQL 3-7, 6-7 SQLMEM 3-7 COBOL COPY sta
Index D 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-13 CPRL error codes 11-2 CPRL procedures CPRL_AREALPHAS_ 11-4 CPRL_ARENUMERICS_ 11-5 CPRL_ARE_ 11-3 CPRL_COMPARE1ENCODED_ 11-6 CPRL_COMPAREOBJECTS_ 11-8 CPRL_COMPARE_ 11-7 CPRL_DECODE_ 11-9 CPRL_DOWNSHIFT_ 11-10 CPRL_ENCODE_ 11-11 CPRL_GETALPHATABLE_ 11-12 CPRL_GETCHARCLASSTABLE_ 11-13 CPRL_GETDOWNSHIFTTABLE_ 11-1
Index D Data structures, internal SQL B-1 Data types, SQL and COBOL correspondence 2-2 DATA-LEN, SQLDA structure field 10-12 checking 10-21 DATA-TYPE, SQLDA structure field 10-12 checking 10-21 DATEFORMAT clause, INVOKE directive 2-18 Date-time data type 2-9, 2-18 Debugging FORCE option 6-36 RUND command 7-2 DECIMAL POINT IS COMMA option, COBOL 2-8 DECLARE CURSOR statement description 4-15 example 4-13 guidelines 4-15 placement 4-15 Declare Section declaring host variables 1-2, 2-1 INVOKE directive 2-13 s
Index E Dynamic SQL (continued) processing SELECT statements 10-30 programming techniques 10-5 prompting user for input 10-24 sample program 10-37 SQLSA statistics 9-21 SQL/MP system procedures 5-1 statements, description 3-6, 10-1 steps for developing an application 10-9 steps for use 10-2 uses 10-4 D-series node, running a program at low PIN 7-5 E Embedded NonStop SQL/MP conformance to ISO/ANSI standards 1-8 Embedded sign, decimal data type 2-17 Embedded SQL statements advantages 1-1 compiling a progra
Index F EXCLUSIVE MODE, SELECT statement 4-6 EXECUTE IMMEDIATE, dynamic SQL statement 3-6 EXECUTE, dynamic SQL statement 3-6 Execution statistics, SQL/MP system procedures 5-1 EXPLAIN DEFINES report 6-41 EXPLAIN PLAN report 6-40 EXPLAIN utility 6-29, 6-40 EYE-CATCHER SQLCA structure field 9-13 SQLDA structure field 10-12 SQLSA structure field 9-23 F FETCH statement audited tables and views 4-18 cursor operations 4-13 description 4-17 SQLCODE values 4-17 File format, SQL program 6-38 File position, sequen
Index I I IN EXCLUSIVE MODE, SELECT statement 4-6, 4-14 IN SHARE MODE, SELECT statement 4-6 INCLUDE SQLCA directive description 3-3 example 9-5 syntax 9-12 INCLUDE SQLCODEX directive 3-4 INCLUDE SQLDA directive considerations 10-16 description 3-4 syntax 10-15 INCLUDE SQLSA directive description 3-4 syntax 9-22 INCLUDE STRUCTURES directive embedded 3-3 syntax 9-1 versions D-1 Indicator parameters dynamic SQL 10-9 names buffer 10-24 Indicator variable description 2-1 null value 2-10 specifying 2-6 with agg
Index L L ld utility guidelines 6-23 OSS environment 6-20 -strip option 6-23 Level-88 items, INCLUDE SQLCODEX 9-6 Linker guidelines 6-21 SQL program file format 6-38 -strip option 6-23 Load time, SQL 8-6 LOAD, SQL utility 4-2 Local partition, maximizing local autonomy C-2 LOCK TABLE, DCL statement 3-6 Loops, avoiding with WHENEVER 9-9 Lost Open error causes 4-2 recovery 4-3 M Memory guidelines B-6 requirements B-5 MESSAGES, SQLSA structure field 9-24 MESSAGE-BYTES, SQLSA structure field 9-24 Multiple row
Index O Null value (continued) with INVOKE directive 2-13 NULL-INFO, SQLDA structure field 10-13 NUM-ENTRIES, SQLDA structure field 10-12 NUM-ERRORS, SQLCA structure field 9-13 NUM-ERR-ENTRIES, SQLCA structure field 9-13 NUM-TABLES, SQLSA structure field 9-23 Output parameters, handling null values 10-35 Output variable, description 2-1 OUTPUT-COLLATIONS-LEN, SQLSA structure field 9-24 OUTPUT-NAMES-LEN, SQLSA structure field 9-24 OUTPUT-NUM, SQLSA structure field 9-24 O Parallel execution plans, and si
Index R 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-24 Primary key, SELECT statement 4-5 Procedure Division, statement placement 3-3 PROCEDURE-ID, SQLCA structure field 9-13 process access ID (PAID) 7-1 PROGID attribute 7-1 Program catalog version (PCV) 6-45 Program file, TACL RUN command 7-3 Program format version (PFV) 6-45, 7-7 Program invalidation, causes
Index S Similarity check at run time 8-9 collations 8-15 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 SOURCE directive COBOL compiler directive 3-7 SQL communications area (SQLCA) See SQLCA structure SQL compiler binding and linking 6-22 DEFINEs 6-9 description 6-25 determining version 6-45 EXPLAI
Index S SQLCA structure (continued) returning information 9-12 SQLCAGETINFOLIST procedure 5-17 SQLCA_DISPLAY2_ procedure 1-4 SQLCADISPLAY procedure syntax 5-30 SQLCAFSCODE procedure syntax 5-17, 9-12 SQLCAGETINFOLIST procedure description 5-1 syntax 5-18, 9-12 SQLCATOBUFFER procedure syntax 5-34 SQLCA-RESERVED, SQLCA structure field 9-13 SQLCA_BUFFER2_ procedure considerations 5-16 with an error table 5-15 SQLCA_DISPLAY2_ procedure considerations 5-7 description 5-1 example 1-4 syntax 5-4 system procedure
Index S SQLMAP option, SQLCOMP command 9-2 SQLMEM, COBOL 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-18 fields 9-23 guidelines 9-22 SQLSADISPLAY procedure syntax 5-27 SQLSA-RESERVED, SQLSA structure field 9-24 SQLVAR, SQLDA structure field 10-12 SQL, COBOL compiler directive 3-7, 6-7 SQL-ERROR, SQLCA structure field 9-14 SQL-STATEMENT-TYPE, SQLSA structure field 9-24 SQL/MP directives BEGIN DECLARE SEC
Index T Storage allocation, parameters in dynamic SQL 10-11 STOREDDEFINES option, SQLCOMP command 6-10 STRIP command 6-22 SUBSYSTEM-ID, SQLCA structure field 9-14 SUFFIX clause, INVOKE directive 2-21 SUPPRESS-DISPLAY, SQLCA structure field 9-14 SYNC clause, host variable declaration 2-9 SYNTAX-ERR-LOC, SQLCA structure field 9-13 SYSKEY (system-defined primary key) 2-17 System procedures description 1-4, 5-1 SQLADDR 5-3 SQLCAFSCODE 5-17 SQLCAGETINFOLIST 5-17 SQLCA_DISPLAY2_ 5-4 SQLCA_TOBUFFER2_ 5-11 SQLGET
Index V USAGE clause, host variable declaration 2-9 USER-LINE-NUMBER, SQLCA structure field 9-13 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 clause, host variable declaration 2-9 VARCHAR data type description 2-25 with INVOKE directive 2-17 Variables, environment, OSS 6-16 VAR-PTR, SQLDA structure field 10-13 Version information displaying with SQL GETOBJECTVERSION 5-25 displaying with SQLGETSYSTEMV
Index Special Characters HP NonStop SQL/MP Programming Manual for COBOL—529758-003 Index-16
