Course ID 00086596
HP product number U5512S
Course format ILT
Course length 5 days
Skill level Expert
Delivery languages English
Related certifications
In preparation for these exams
Selected items from this course are included in these exams:
Register for this course.
Find this course in the Training calendar and click the “Register” link in the last column to begin your registration.

Course description

This course covers the skills needed to design, implement, and build an embedded SQL/MX program using the host programming languages, C and COBOL85. Classroom discussions and hands on lab exercises enable you to apply these programming techniques to your programming tasks. Become familiar with the available tools and functions in the SQL/MX product and the operating system, affecting the program's execution and run time environment. This course is 60 percent lecture and 40 percent hands on labs using HP servers.

Who should attend

Software developers who are responsible for programming or supporting embedded SQL database applications.

Prerequisites

Before attending this course, you should have the following:
• NonStop SQL/MX Basics (U4184S) or NonStop SQL/MX
• Database for SQL/MP Users (U4185S) and
COBOL or C Programming experience
• Experience with OSS (shell) environment is beneficial
For complete prerequisites and requirements to achieve any of the related certifications or upgrade paths, see the certification description on the HP ExpertOne website.

Topics

  • Module 1 - SQL Program Development
    • Embedded SQL program benefits
    • Format and structural differences between traditional and embedded SQL programs
  • Module 2 - Embedded SQL/MX Program Structure
    • Basic program formats
    • Syntax and rules for embedding SQL statements
    • Function and rules for using host variables
    • Types of NonStop SQL/MX statements and directives
    • Referencing SQL objects in an embedded SQL/MX program
    • Function and rules for declaring an SQL declare section
    • Labs: embedded SQL/MX COBOL and C program structures
  • Module 3 - Compiling Embedded SQL/MX Programs in the OSS and Guardian Environments
    • Naming methods used to access SQL/MX and SQL/MP objects
    • Module directive
    • Constructing embedded statements
    • Declaring host variables
    • SQL/MX COBOL and C/C++ preprocessor
    • Compiling COBOL and C/C++ annotated source files
    • Compiling embedded executable program files
    • SQL/MX programs in the OSS or Guardian environments
      Labs: compiling embedded SQL/MX COBOL or C programs in the OSS environment
  • Module 4 - Preprocessing an Embedded SQL/MX Source File on a Windows Workstation
    • Methods for preprocessing embedded SQL/MX COBOL or C source files
    • Labs: preprocessing embedded SQL/MX COBOL or C source files
  • Module 5 - SQL/MX Module Management
    • Module management concepts and methods
    • Targeting for different systems using a script file
    • Versioning of an application using a script file
    • Grouping multiple source files using a script file
    • Labs: module management for embedded SQL/MX COBOL or C programs
  • Module 6 - SQL/MX Data Types and Host Variables Declarations
    • Host variables and SQL identifiers
    • Host program and SQL/MX identifiers naming rules
    • Host variable declaration and usage
    • SQL/MX data types
    • Declaring indicator variables for NULL values
    • Data conversion for C, COBOL, and SQL/MX data types
    • INVOKE preprocessor directive
    • Labs: SQL/MX data types and COBOL or C host variables declarations
  • Module 7 - Data Retrieval and Modification Using Character and Numeric Data Types
    • Data retrieval and modification statements
    • INSERT statement
    • Blank padding
    • UPDATE statement
    • DELETE statement
    • Single row SELECT statement
    • Labs: embedding DML statements in C or COBOL programs using character and numeric data types
  • Module 8 - Data Retrieval and Modification Using DATETIME and INTERVAL Data Types
    • DATETIME and INTERVAL data types
    • DATETIME and INTERVAL host variables
    • Inserting and updating DATETIME and INTERVAL data
    • Single row SELECT statement for DATETIME and INTERVAL data types
    • DATETIME functions
    • Labs: embedding SQL/MX DML statements in C or COBOL programs using DATETIME and INTERVAL data types
  • Module 9 - Error Handling and Status Reporting
    • SQLSTATE and SQLCODE variables
    • SQL/MX extensions to SQLSTATE values
    • Declaring and checking SQLSTATE or SQLCODE
    • SET TRANSACTION statement
    • GET DIAGNOSTICS and GET DIAGNOSTICS EXCEPTION statements
    • WHENEVER statement
    • Labs: error checking and status reporting in embedded SQL/MX COBOL or C programs
  • Module 10 - Database Integrity and Transaction Control
    • Internal and external threats to database integrity
    • Protecting database integrity against threats
    • Maintaining entity integrity and referential integrity
      Transaction control statements
  • Module 11 - Locking and Concurrency
    • Database concurrent access issues
    • Locking strategies
    • Implementing locking strategies into programming constructs
    • Solving deadlock situations
    • Labs: access options and isolation levels in embedded SQL/MX C or COBOL programs
  • Module 12 - Data Retrieval and Modification Using Cursors
    • SQL cursor concept
    • Steps for using a static SQL cursor
    • Using cursors with DATETIME and INTERVAL data types
    • Cursor considerations
    • Labs: using static cursors in embedded SQL/MX C or COBOL programs
  • Module 13 - SQL/MX Rowsets
    • Accessing multiple rows using rowsets
    • Rowsets used as input or output host variables
    • Declaring host variable arrays as rowsets
    • Using rowset arrays
    • Inserting, updating, and deleting rows
    • Using the rowset index identifier
    • Specifying rowset-derived tables
    • Rowset considerations
    • Labs: using rowsets in embedded SQL/MX C or COBOL programs
  • Module 14 - SQL/MX Publish and Subscribe
    • Publish and subscribe services
    • Transactional dequeuing
    • Subscribers
    • STREAM access and restrictions
    • Embedded DELETE and UPDATE
    • Partitioned queues
    • Labs: using publish and subscribe features in embedded SQL/MX C or COBOL programs
  • Module 15 - SQL/MX Compound Statements
    • Compound statement concept
    • Assignment statement
    • IF statement
  • Module 16 - Managing SQL/MX Applications
    • Table and view name references
    • Compile time name resolution
    • Late name resolution
    • Similarity checking and check criteria
    • Automatic recompilation
    • Moving a program with compiled modules
    • Moving a program and recompiling to generate module files
    • Securing SQL modules
  • Module 17 - SQL/MX Programming Techniques
    • NULLs, nullable columns, and host variables
    • Using LIKE predicates
    • Determining existence
    • Maintaining referential integrity
    • Long-running transactions
    • Three-image-update technique
    • Labs: SQL programming techniques for C or COBOL programs
  • Onsite-delivery equipment requirements
    • HP NonStop server
    • NonStop Kernel Operating System G06.18 or later with SQL/MX
    • Projecting equipment for the instructor's laptop computer to display PowerPoint slides

Objectives

After completing this course, you should be able to do the following:
  • Identify and use SQL/MX directives and statements in embedded SQL/MX COBOL or C programs
  • Preprocess and compile SQL/MX embedded programs from the OSS environment
  • Use the SQL/MX module management features to manage SQL/MX module files
  • Declare and use character, numeric, DATETIME, and INTERVAL data type host variables in embedded SQL/MX data manipulation statements
  • Embed SQL/MX DML statements in COBOL or C programs
  • Use SQL/MX transaction statements to define transactions
  • Use SQL/MX access options and isolation levels to define locking strategies
  • Declare and use static cursors and rowsets in embedded SQL/MX COBOL or C programs
  • Use the publish and subscribe features in embedded SQL/MX COBOL or C programs

How to register

View the HP ExpertOne Global Training Calendar to register for the trainings offering that best meets your needs.

Policies, fees and cancellations

Course fees may vary and are established and collected by the training center delivering the course. Cancellation fees may apply. Contact your HP Authorized Training Partner for their respective policies.