Module 1 — SQL Program Development

• Identify the format and structural differences between a traditional and an embedded SQL application program.

Module 2 - Embedded SQL/MX Program Structure

• Identify the basic formats and structures of an embedded SQL program.
• Explain the function and syntax for declaring and using host variables in a program.
• Identify the different types of HPE NonStop SQL/MX statements and directives
• Describe the methods used to reference SQL object in an embedded SQL/MX program

Module 4: Performance considerations in query design

• Describing the naming methods used to access SQL/MX and SQL/MP objects.
• Describe the module directive
• Describe how to construct embedded statements.
• Describe how to declare host variables.
• Preprocess COBOL and C/C++ sources files embedded with HPE NonStop SQL/MX statements and directives on the HPE NonStop server.
• Use the nmcobol or ecobol utilities to compile COBOL annotated source files.
• Use the c89 utility to compile C/C++ annotated source files.
• Use the SQL/MX module compiler (mxUserModuleCompiler) to compile embedded executable program files.
• Run SQL/MX programs in the OSS environment or Guardian environment.

Module 4 – Preprocessing an Embedded SQL/MX Source File on a Windows Workstation

Module 5 - SQL/MX Module Management

• Describe the module management concepts of targeting, versioning, and grouping.
• Generate an application targeted for different systems (targeting) using a script file.
• Generate different versions of an application (versioning) using a script file.
• Develop an application consisting of multiple source files using a script file, which requires:
• Compiling and linking multiple object files into an executable file.
• SQL/MX compiling multiple module definition files to create module files with the same group name.

Module 6 - SQL/MX Data Types and Host Variables Declarations

• Name the different HPE NonStop SQL/MX data types and map the SQL data types to the host-language data types.
• Given a COBOL85 or C program source file, a database schema, a list of SQL objects, and database access specifications, perform the following:

  • Declare host variables in the source files.
  • Specify the host variables in the embedded SQL statements.
  • Use the INVOKE directive to generate a record structure for a SQL table in your host program.
  • Compile the source file and generate an executable program and its module file.

Module 7 - Data Retrieval and Modification Using Character and Numeric Data Types

• Embed HPE NonStop SQL/MX data manipulation statements into a host-language source file (C or COBOL85), build an executable application, and validate the program’s compliance.

Module 8 - Data Retrieval and Modification Using DATETIME and INTERVAL Data Types

• Embed HPE NonStop SQL/MX data manipulation statements using DATETIME and INTERVAL data types into a host-language source file (C or COBOL), build an executable application, and validate the programs compliance.

Module 9 - Error Handling and Status Reporting

• Describe the SQLSTATE variable, the SQLCODE variable, and the diagnostic area attributes used in error checking.
• Perform error checking by declaring and checking, the SQLSTATE variable.
• Perform error checking by declaring and checking, the SQLCODE variable.
• Use the GET DIAGNOSTIC directive to obtain detailed information from the diagnostic area about warnings and errors.
• Perform error checking using WHENEVER declaratives to control processing of warnings and errors.

Module 10 - Database Integrity and Transaction Control

• Discuss the types of internal and external threats against database integrity.
• Describe how transaction control can protect the database from these threats.
• Describe the techniques for maintaining entity integrity and referential integrity.
• Code embedded SQL statements for transaction control in an application program.

Module 11 - Locking and Concurrency

• Describe the problems associated with database concurrent access.
• Identify the different locking strategies to solve concurrency problems.
• Implement locking strategies into programming constructs.
• Describe the mechanism to solve a deadlock situation.

Module 12 - Data Retrieval and Modification Using Cursors

• Describe the concept of accessing multiple rows using a cursor.
• Describe the HPE NonStop SQL/MX statements (DECLARE CURSOR, OPEN, FETCH, and CLOSE)

  • used to implement a static cursor in a SQL/MX program.

Module 13 - SQL/MX Rowsets

• Describe the concept of accessing multiple rows using rowsets.
• Implement rowsets in HPE NonStop SQL/MX statements to retrieve, insert, update, and delete rows of data.

Module 14 - SQL/MX Publish and Subscribe

• Describe the concepts of publish/subscribe services and transactional queuing.
• Create applications using publish/subscribe services and transaction queuing.

Module 15 - SQL/MX Compound Statements

• Describe the ANSI SQL Persistent Stored Module (SQL/ PSM) statements supported by HPE NonStop SQL/MX:

  • Compound Statements: BEGIN and END.
  • Assignment (SET) statement.
  • IF statement.

Module 16 - Managing SQL/MX Applications

• Describe table name references used in a program and the effects of changing the table name references.
• Describe compile-time name resolution.
• Describe late-name resolution.
• Describe automatic recompilation and how to enable or disable it.
• Describe the methods for moving HPE NonStop SQL/ MX applications.

Module 17 - SQL/MX Programming Techniques

• Describe the programming techniques to handle NULL columns, to detect data existence, and to use pattern matching and row positioning with alphanumeric key values.
• Describe specific programming techniques for TS/MP (Pathway) servers and long-running transactions in offline (large batch) applications.

Lab Introduction

Module 2 Lab 1 — Embedded SQL/MX COBOL Program Structure

Module 2 Lab 2 — Embedded SQL/MX C Program Structure

Module 3 Lab 3.1A — Compiling Embedded SQL/MX COBOL Programs in the OSS Environment Using ANSI Names

Module 3 Lab 3.1B — Compiling Embedded SQL/MX COBOL Programs in the OSS Environment Using MP ALIAS

Module 3 Lab 3.1C — Compiling Embedded SQL/MX COBOL Programs in the OSS Environment Using DEFINEs

Module 3 Lab 3.1D — Compiling Embedded SQL/MX COBOL Programs in the OSS Environment Using NSK Names

Module 3 Lab 3.2A — Compiling Embedded SQL/MX C Programs in the OSS Environment Using ANSI Names

Module 3 Lab 3.2B — Compiling Embedded SQL/MX C Programs in the OSS Environment Using MP ALIAS

Module 3 Lab 3.2C — Compiling Embedded SQL/MX C Programs in the OSS Environment Using DEFINEs

Module 3 Lab 3.2D — Compiling Embedded SQL/MX C Programs in the OSS Environment Using NSK Names

Module 3 Lab 3 — Validating an Embedded SQL/MX Program

Module 4 Lab 1 — Preprocessing Embedded SQL/MX COBOL Source Files on a Windows NT/2000/XP Workstation

Module 4 Lab 2 — Preprocessing Embedded SQL/MX C Source Files on a Windows NT/2000/XP Workstation

Module 5 Lab 1 — Module Management for Embedded SQL/MX COBOL Programs

Module 5 Lab 2 — Module Management for Embedded SQL/MX C Programs

Module 6 Lab 1 — SQLMX Data Types and COBOL Host Variables Declarations

Module 6 Lab 2 — SQLMX Data Types and C Host Variables Declarations

Module 7 Lab 1 — Embedding SQL/MX DML Statements in a COBOL Program Using Character and Numeric Data Types

Module 7 Lab 2 — Embedding SQL/MX DML Statements in a C Program Using Character and Numeric Data Types

Module 8 Lab 1 — Embedding DML Statements in a COBOL Program Using DATETIME and INTERVAL Data Types

Module 8 Lab 2 — Embedding DML Statements in a C Program Using DATETIME and INTERVAL Data Types

Module 9 Lab1 — Error Checking and Status Reporting in an Embedded SQL/MX COBOL Program

Module 9 Lab 2 — Error Checking and Status Reporting in an Embedded SQL/MX C Program

Module 11 Lab 1 — Access Options and Isolation Levels in an Embedded SQL/MX COBOL Program

Module 11 Lab 2 — Access Options and Isolation Levels in an Embedded SQL/MX C Program

Module 11 Lab 3 — Optional Lab: SQL/MX Lock Lab

Module 12 Lab 1 — Using Static Cursors in an Embedded SQL/MX COBOL Program

Module 12 Lab 2 — Using Static Cursors in an Embedded SQL/MX C Program

Module 13 Lab 1 — Using ROWSETs in an Embedded SQL/MX COBOL Program

Module 13 Lab 2 — Using ROWSETs in an Embedded SQL/MX C Program

Module 14 Lab 1 — Using Publish and Subscribe Features in an Embedded SQL/MX COBOL Program

Module 14 Lab 2 — Using Publish and Subscribe Features in an Embedded SQL/MX C Program

Module 17 Lab 1 — SQL Programming Techniques (COBOL)

Module 17 Lab 2 — SQL Programming Techniques (C)