•  Preface
  • Purpose
  • Audience
  • Organization
  • Typographical conventions
  •  Examples of syntax diagrams (SQL)
    • Long SQL syntax descriptions split across lines
•  Introduction
  • An overview of OpenEdge SQL
  •  OpenEdge SQL client/server architecture
    • Multi-threaded architecture
•  JDBC Client
  •  Introduction to the JDBC client
    • JDBC architecture
    • JDBC components
    • JDBC API support
    • Internet Protocol Support
  •  Setting environment variables
    • Setting environment variables in a character environment
    • Setting environment variables in Windows
  •  Connecting to an OpenEdge database with a JDBC driver
    • Connecting using SQL Explorer
    •  Connecting from a Java application using a URL
      • Database connection examples
    • Connecting from a Java application using a data source
    •  Enabling encryption
      • EncryptionMethod
      • ValidateServerCertificate
      • HostNameInCertificate
      • TrustStore and TrustStorePassword
    • JDBC connection parameters
•  ODBC Client
  •  Overview of ODBC
    • ODBC architecture
  •  Configuring data sources
    • Internet Protocol support
    •  Configuring Windows clients
      • Adding a new data source
    • Enabling encryption
    •  Configuring UNIX clients
      • Setting environment variables
      • Configuring data sources on a UNIX environment
    • Definitions of ODBC.INI tags
    •  Testing your ODBC connection on UNIX
      • Solaris and AIX
      • HPUX
      • Linux
•  Troubleshooting on the server using the Pro Server log file
  • Troubleshooting database connection problems
  • Troubleshooting using Pro Server log file
  • Troubleshooting establishing a connection to the database
  • Logging level details
•  Data Control Language and Security
  •  Working with database security
    •  Comparing OpenEdge SQL and ABL security
      • OpenEdge SQL security
      • ABL security
    •  Comparing authentication and authorization
      • Authentication
      • Authorization
  •  Creating users
    • Creating database administrators
    • Creating users in the _User table
    •  SQL Only Users
      • Create an SQL only user
      • Alter a user to an SQL only user
  •  Granting privileges
    • Privilege basics
    •  GRANT statement
      • Database-wide privileges
      • Table-specific privileges
      • Granting public access
  • Verifying privileges
  • Revoking privileges
•  OpenEdge SQL Data Definition Language
  •  Using Data Definition Language statements
    •  Working with tables
      • CREATE TABLE
      • ALTER TABLE
      • DROP TABLE
    •  Working with indexes
      • CREATE INDEX
      • DROP INDEX
      • Enabling large database index keys
    •  Working with views
      • CREATE VIEW
      • DROP VIEW
    •  Working with sequences
      • Enabling 64-bit sequences
      • Using CURRVAL and NEXTVAL in a statement
      • DROP SEQUENCE
      • ALTER SEQUENCE
      • Working with existing 32-bit sequences
    •  Working with domains
      • CREATE DOMAIN
      • DROP DOMAIN
    •  Working with tenants
      • CREATE TENANT
      • CREATE SUPER-TENANT
      • ALTER TENANT
      • DROP TENANT
      • SHOW TENANT
    •  Working with groups
      • CREATE GROUP
      • DROP GROUP
      • SHOW GROUP
      • ALTER GROUP
    •  Working with partitions
      • SHOW PARTITION
  •  Maintaining data integrity
    • Need for integrity constraints
    •  Types of integrity constraints
      • Check constraints
      • Column-level check constraints
      • Table-level check constraints
      • Primary keys
      • Candidate keys
    •  Referential constraints
      • Foreign key constraint
    •  Handling cycles in referential integrity
      • Creating tables in cycles
      • Inserting rows in a cycle
  •  Working with SQL utilities
    •  Using the SQLDUMP utility
      • Dumping Multi-tenant tables
      • Dumping partitioned tables
    •  Using the SQLLOAD utility
      • Loading Multi-tenant tables
      • Loading partitioned tables
    •  Using the SQLSCHEMA utility
      • Schema definition for multi-tenant tables
•  Change Data Capture
  • Using SQL to query CDC data
  •  Using Change Data Capture
    • Application Guidelines
  •  Use cases
    • Finding changed data references during minimal Change tracking
    • Finding change data references in a time range with minimal Change tracking
    • Finding change data references for a table row with minimal Change tracking
    • Getting change data values for a source table
    • Getting change data values for a source table within a time range
    • Getting change data values for a source table row
    • Getting change data values driven by a source table row
    • Getting Before and After values in change data
    • Propagating change details to the Data Warehouse, and using CDC_get_changed_columns()
    • Looking for changes to a particular column in a source table
    • Finding source tables with Changes
    • Listing source tables with Change volume
    • Consuming and managing CDC change data
  • CDC Table Security
  •  Scalar Functions
    • CDC_get_changed_columns
    • CDC_is_column_changed
  • Enabling Encryption on CDC Source Table
•  OpenEdge SQL Data Manipulation Language
  •  Using Data Manipulation Language statements
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
  •  Using indexes
    • Index system catalog tables
  •  Working with join operations
    •  Using inner joins
      • Employing a table alias
    •  Using outer joins
      • Left outer joins
      • Right outer joins
  • Using scalar subqueries
•  OpenEdge SQL and Advanced Business Language Interoperability
  •  Managing Open Edge SQL and ABL database connections
    • Determining database server requirements
    •  Starting SQL and ABL brokers
      • Establishing an encrypted connection
  •  Establishing user accounts and assigning privileges
    • Using authentication
    • Assigning privileges
  •  ABL and OpenEdge SQL interaction in an OpenEdge application
    •  Comparing ABL and OpenEdge SQL
      • ABL
      • OpenEdge SQL
    • Understanding OpenEdge SQL database structure
    •  Comparing OpenEdge SQL and ABL database objects
      • Rows and columns
      • Tables
      • Schemas
    •  Naming objects for OpenEdge SQL and ABL databases
      • Naming conventions for ABL objects
      • Naming conventions for OpenEdge SQL identifiers
      • Conventional identifiers
      • Delimited identifiers
    • Working with data type compatibility
    •  Working with SQL column widths
      • Using the -checkwidth startup parameter
      • Using the DBTool utility
    • Working with triggers
    • Working with locking behavior and isolation levels
•  Data Control Language and Transaction Behavior
  •  Working with transaction control
    • COMMIT statement
    • ROLLBACK statement
  •  Transactions and isolation levels
    • Dirty read
    • Nonrepeatable read
    • Phantom read
    • Setting isolation levels
  •  Understanding transactions and locking
    • Lock modes
    • How lock levels and lock modes interact
    •  Understanding lock acquisition
      • Information schema locks
      • Table and record locks
  •  Enhancing performance with locking hints
    • The READPAST locking hint
  • Monitoring locking and database performance
  • Online schema changes
  •  Authorized Data Truncation
    • Logging
    • Writing to database log file
    • Logging instances
    • SQL Utility update to prevent data loss
  • Autonomous Schema Update
•  User Defined Functions
  •  Working with User Defined Functions
    • Creating a UDF
    • Dropping a UDF
    • Granting Execute permissions for a UDF
    • Revoking Execute permissions for a UDF
    • UDF Example
  • Semantics and Limitations
  • Permissions to create a UDF
•  Performing Multi-database Queries
  •  Multi-database query overview
    •  The process of multi-database queries
      • 1. Defining the databases
      • 2. Ensuring permissions to access databases to be queried
      • 3. Connecting to the databases
      • 4. Performing the query
      • 5. Disconnecting
    •  Working with catalogs in multi-database queries
      • Working with default catalogs
      • Working with catalogs and synonyms
      • Working with catalogs and stored procedures
      • Granting permissions to perform multi-database queries
      • Limitations of the OpenEdge SQL multi-database environment
  •  Connecting to multiple databases
    • Connecting to multiple databases using SQL commands
    •  Specifying a database default catalog
      • Using the SET CATALOG statement
    •  Determining catalog availability
      • Using the SHOW CATALOGS statement
    • Using the CONNECT AS CATALOG statement
    • Disconnecting from catalogs
    • Using the DISCONNECT CATALOG statement
    •  Using properties files to enable multiple database connections
      • Creating a properties file
  •  An example of a multi-database query
    • Connecting to an auxiliary database
    • Performing a multi-database query
    • Disconnecting an auxiliary database
•  Configuring JVM
  • Specifying JVM parameters in a properties file
•  Working with JTA Transactions
  • JTA's role in J2EE
  • Understanding JTA architecture
  •  Understanding application interfaces
    • XADataSource
    • XAConnection
    • XAResource
    • XAResource methods
  •  JTA and the distributed transaction process
    • JTA transactions and two-phase commit protocol
    • JTA transactions and conventional transactions
    • JTA transactions and crash recovery
    • JTA transactions and OpenEdge Replication
  •  Planning for JTA transaction support
    • JTA transactions and database resource planning
    • Enabling JTA support
    • Disabling JTA support
    • Monitoring JTA transactions
    • Resolving JTA transactions
•  Stored Procedures and Triggers
  •  Setting up OpenEdge SQL for stored procedures and triggers
    • Enabling stored procedures on 64-bit platform databases
  •  Basics of Java stored procedures
    • Advantages of stored procedures
    •  How OpenEdge SQL interacts with Java
      • Creating stored procedures
      • Calling stored procedures
      • Using stored procedures
  •  Stored procedure fundamentals
    •  Java snippet
      • Structure of stored procedures
  •  Writing stored procedures
    •  Invoking stored procedures
      • From ODBC
      • From JDBC
    • Modifying and deleting stored procedures
    • Stored procedure security
    •  Using the OpenEdge SQL Java classes
      • Passing values to SQL statements
      • setParam method: pass input values to SQL statements
      • getValue method: pass values from SQL result sets to variables
      • Passing values to and from stored procedures: input and output parameters
      • Implicit data type conversion between SQL and Java types
      • Executing an SQL statement
      • Immediate execution
      • Prepared execution
      • Retrieving data: the SQLCursor class
      • Returning a procedure result set to applications: the RESULT clause and DhSQLResultSet
      • Handling null values
      • Setting SQL statement input parameters and procedure result set fields to null
      • Assigning null values from SQL result sets: the SQLCursor.wasNULL method
      • Handling errors
      • Calling stored procedures from other stored procedures
      • Stored procedure parameter requirements and usage
      • INOUT and OUT parameters when one Java stored procedure calls another
  •  Working with triggers
    • Creating triggers
    • Structure of triggers
    • Triggers, stored procedures, and constraints
    • Typical uses for triggers
    • OLDROW and NEWROW objects: passing values to triggers
    • getValue method for NEWROW and OLDROW
    • Assigning null values from SQL OLDROW and NEWROW statement objects: the OLDROW.isNULL method and NEWROW.isNULL
•  Optimizing Query Performance
  •  Understanding optimization
    •  How the query optimizer works
      • Representing the statement as a query tree
    •  The statement parser
      • Quantified predicates and other subqueries
      • Views
    •  Optimizer phases
      • Early evaluation of constant expressions
      •  GROUP BY optimization
        • Re-order GROUP BY columns
        • Considering Equi-Constant Predicates
      • Pushing restrict operations close to the data origin
      • Using indexes for restrictions
      • Choosing the best index
      • Predicate expressions
      • Generating candidate indexes
      • Selecting an index
      • Providing index hints
      • Join optimization
      • Determining join order among adjacent join nodes
      • Choosing the join algorithm
      • Augmented nested loop join
      • Merge join
      • Nested loop join
      • Sort optimization
      • Eliminating redundant sorts
      • Converting table scans to index bracket scans
      • Indexes to evaluate MAX/MIN functions
      • Index bracket scan optimization
  •  Inspecting what the optimizer produces
    • The _Sql_Qplan virtual system table
  •  Affecting what the optimizer produces
    • Working with the UPDATE STATISTICS command
    • SQL use of index statistics
    • Updating index statistics