Skip to main contentSkip to search
Powered by Zoomin Software. For more details please contactZoomin
Progress DocumentationProgress Documentation
Progress Documentation
  • Home
  • Home
  • EnglishČeštinaDeutsch (Germany)Español (Spain)FrançaisItaliano (Italy)Português (Brasil)日本語Русский (Russia)中文 (简体) (China)中文 (繁體, 台灣) (Taiwan)ar-AR
  • Login

OpenEdge® Data Management: SQL Development

Data control language and transaction behavior

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
Table of Contents
  • Preface
    • Purpose
  • Introduction
    • An overview of OpenEdge SQL
    • OpenEdge SQL client/server architecture
      • Multi-threaded architecture
    • Examples of syntax diagrams (SQL)
      • Long SQL syntax descriptions split across lines
    • Online Database changes using SQL
      • Rename field using SQL
      • Drop a trigger
      • SQL commands to modify schema
  • JDBC Client
    • Introduction to the JDBC client
      • JDBC architecture
      • Internet Protocol support
    • Set environment variables
      • Set environment variables in a character environment
      • Set environment variables in Windows
    • Connect to an OpenEdge database with a JDBC driver
      • Connect using SQL Explorer
        • Securely specifying a password in SQL Explorer
      • Connect from a Java application using a URL
        • Database connection examples
      • Connect from a Java application using a data source
      • Enable encryption
        • EncryptionMethod
        • ValidateServerCertificate
        • HostNameInCertificate
        • TrustStore and TrustStorePassword
      • JDBC connection parameters
  • ODBC Client
    • Overview of ODBC
      • ODBC architecture
    • Configure data sources
      • Internet Protocol support
      • Configure Windows clients
        • Add a new data source
      • Enable encryption
      • Configure UNIX clients
        • Set environment variables
        • Configure data sources on a UNIX environment
      • Definitions of ODBC.INI tags
      • Test your ODBC connection on UNIX
        • Solaris and AIX
        • Linux
  • SQL Log Files
    • Troubleshoot on the SQL Server
      • Troubleshoot database connection problems
      • Troubleshoot a SQL Crash
      • Troubleshoot using Pro Server log file
      • Troubleshoot establishing a connection to the database
      • Log level details
  • Data control language and security
    • Work with database security
      • Compare OpenEdge SQL and ABL security
        • OpenEdge SQL security
        • ABL security
      • Compare authentication and authorization
        • Authentication
        • Authorization
    • Create users
      • Create database administrators
      • Create users in the _User table
      • SQL Only users
        • Create an SQL only user
        • Alter a user to an SQL only user
    • Grant privileges
      • Privilege basics
      • GRANT statement
        • Database privileges
        • Table-specific privileges
        • Grant public access
    • Verify privileges
    • Revoke privileges
  • OpenEdge SQL data definition language
    • Use Data Definition Language statements
      • Work with tables
        • CREATE TABLE
        • ALTER TABLE
        • ALTER TABLE RENAME COLUMN
        • ALTER TABLE RENAME INDEX
        • ALTER TABLE DROP COLUMN
        • DROP TABLE
      • Work with indexes
        • CREATE INDEX
        • DROP INDEX
      • Work with views
        • CREATE VIEW
        • DROP VIEW
      • Work with sequences
        • Use CURRVAL and NEXTVAL in a statement
        • DROP SEQUENCE
        • ALTER SEQUENCE
      • Work with domains
        • CREATE DOMAIN
        • DROP DOMAIN
      • Work with tenants
        • CREATE TENANT
        • CREATE SUPER-TENANT
        • ALTER TENANT
        • DROP TENANT
        • SHOW TENANT
      • Work with groups
        • CREATE GROUP
        • DROP GROUP
        • SHOW GROUP
        • ALTER GROUP
      • Work with partitions
        • SHOW PARTITION
    • Maintain 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
      • Handle cycles in referential integrity
        • Create tables in cycles
        • Insert rows in a cycle
    • Work with SQL utilities
      • Use the SQLDUMP utility
        • Dump Multi-tenant tables
        • Dump partitioned tables
      • Use the SQLLOAD utility
        • Load Multi-tenant tables
        • Load partitioned tables
      • Use the SQLSCHEMA utility
        • Schema definition for multi-tenant tables
  • Change Data Capture
    • Use SQL to query CDC data
    • Use Change Data Capture
      • Application Guidelines
    • Use cases
      • Find changed data references during minimal Change tracking
      • Find change data references in a time range with minimal Change tracking
      • Find change data references for a table row with minimal Change tracking
      • Get change data values for a source table
      • Get change data values for a source table within a time range
      • Get change data values for a source table row
      • Get change data values driven by a source table row
      • Get Before and After values in change data
      • Propagate change details to the Data Warehouse, and using CDC_get_changed_columns()
      • Look for changes to a particular column in a source table
      • Find source tables with Changes
      • List source tables with Change volume
      • Consume and manage CDC change data
    • CDC Table Security
    • Scalar Functions
      • CDC_get_changed_columns
      • CDC_is_column_changed
    • Enable Encryption on CDC Source Table
  • OpenEdge SQL data manipulation language
    • Use Data Manipulation Language statements
      • SELECT
      • INSERT
      • UPDATE
      • DELETE
    • Use indexes
      • Index system catalog tables
    • Work with join operations
      • Use inner joins
        • Employ a table alias
      • Use outer joins
        • Left outer joins
        • Right outer joins
    • Use scalar subqueries
  • OpenEdge SQL and Advanced Business Language Interoperability
    • Manage Open Edge SQL and ABL database connections
      • Determine database server requirements
      • Start SQL and ABL brokers
        • Establish an encrypted connection
    • Establish user accounts and assign privileges
    • ABL and OpenEdge SQL interaction in an OpenEdge application
      • Compare ABL and OpenEdge SQL
        • ABL
        • OpenEdge SQL
      • Understand OpenEdge SQL database structure
      • Compare OpenEdge SQL and ABL database objects
      • Name objects for OpenEdge SQL and ABL databases
        • Name conventions for ABL objects
        • Name conventions for OpenEdge SQL identifiers
        • Conventional identifiers
        • Delimited identifiers
      • SQL column widths and data type compatibility
        • Use the -checkwidth startup parameter
        • Use the DBTool utility
      • Work with triggers
      • Work with locking behavior and isolation levels
  • Data control language and transaction behavior
    • Work with transaction control
      • COMMIT statement
      • ROLLBACK statement
    • Transactions and isolation levels
      • Dirty read
      • Nonrepeatable read
      • Phantom read
      • Setting isolation levels
    • Understand transactions and locking
      • Lock modes
      • How lock levels and lock modes interact
      • Understand lock acquisition
        • Information schema locks
        • Table and record locks
    • Enhance performance with locking hints
      • The READPAST locking hint
    • Monitor locking and database performance
    • Authorized data truncation
      • Logging
      • Write to database log file
      • Log instances
      • SQL utility update to prevent data loss
      • Limitations of ADT
    • Autonomous schema update
      • ASU on replication target DB
  • User defined functions
    • Work with User Defined Functions
      • Create a UDF
      • Drop a UDF
      • Grant Execute permissions for a UDF
      • Revoke Execute permissions for a UDF
      • UDF Example
    • Semantics and Limitations
    • Permissions to create a UDF
  • Perform multi-database queries
    • Multi-database query overview
      • The process of multi-database queries
        • 1. Define the databases
        • 2. Ensure permissions to access databases to be queried
        • 3. Connect to the databases
        • 4. Perform the query
        • 5. Disconnect
      • Work with catalogs in multi-database queries
        • Work with default catalogs
        • Work with catalogs and synonyms
        • Work with catalogs and stored procedures
        • Grant permissions to perform multi-database queries
        • Limitations of the OpenEdge SQL multi-database environment
    • Connect to multiple databases
      • Connect to multiple databases using SQL commands
      • Specify a database default catalog
        • Use the SET CATALOG statement
      • Determine catalog availability
        • Use the SHOW CATALOGS statement
      • Use the CONNECT AS CATALOG statement
      • Disconnect from catalogs
      • Use the DISCONNECT CATALOG statement
      • Use properties files to enable multiple database connections
        • Create a properties file
    • An example of a multi-database query
      • Connect to an auxiliary database
      • Perform a multi-database query
      • Disconnect an auxiliary database
  • Configure JVM and specify the parameters
  • Work with JTA transactions
    • JTA's role in J2EE
    • Understand JTA architecture
    • Understand 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
    • Plan for JTA transaction support
      • JTA transactions and database resource planning
      • Enable JTA support
      • Disable JTA support
      • Monitor JTA transactions
      • Resolve JTA transactions
  • Stored procedures and triggers
    • Set up OpenEdge SQL for stored procedures, user defined functions and triggers
    • Basics of Java stored procedures
      • Advantages of stored procedures
      • How OpenEdge SQL interacts with Java
        • Create stored procedures
        • Call stored procedures
        • Use stored procedures
    • Stored procedure fundamentals
      • Java snippet
        • Structure of stored procedures
    • Write stored procedures
      • Invoke stored procedures
        • From ODBC
        • From JDBC
      • Modify and delete stored procedures
      • Stored procedure security
      • Use the OpenEdge SQL Java classes
        • Pass values to SQL statements
        • setParam method: pass input values to SQL statements
        • getValue method: pass values from SQL result sets to variables
        • Pass 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
        • Retrieve data: the SQLCursor class
        • Return a procedure result set to applications: the RESULT clause and DhSQLResultSet
        • Handle null values
        • Set SQL statement input parameters and procedure result set fields to null
        • Assign null values from SQL result sets: the SQLCursor.wasNULL method
        • Handle errors
        • Call stored procedures from other stored procedures
        • Stored procedure parameter requirements and usage
        • INOUT and OUT parameters when one Java stored procedure calls another
    • Work with triggers
      • Create triggers
      • Drop a Trigger
      • 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
      • Assign null values from SQL OLDROW and NEWROW statement objects: the OLDROW.isNULL method and NEWROW.isNULL
  • Autonomous update statistics
    • Run the AUS service
    • View the AUS service configuration parameters
    • Monitor AUS Server activities
  • Optimize query performance
    • Understand optimization
      • How the query optimizer works
        • Represent 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
          • Consider Equi-Constant Predicates
        • Push restrict operations close to the data origin
        • Use indexes for restrictions
        • Choose the best index
        • Predicate expressions
        • Generate candidate indexes
        • Select an index
        • Provide index hints
        • Join optimization
        • Determine join order among adjacent join nodes
        • Choose the join algorithm
        • Augmented nested loop join
        • Merge join
        • Nested loop join
        • Sort optimization
        • Eliminate redundant sorts
        • Convert table scans to index bracket scans
        • Indexes to evaluate MAX/MIN functions
        • Index bracket scan optimization
    • Inspect what the optimizer produces
      • The _Sql_Qplan virtual system table
    • Affect what the optimizer produces
      • Work with the UPDATE STATISTICS command
      • SQL use of index statistics
      • Update index statistics
Table of Contents

Data control language and transaction behavior

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
  • Last Updated: March 30, 2020
  • 1 minute read
    • OpenEdge
    • Version 12.2
    • Documentation

To properly maintain a database, it is important to understand the concepts of transactions, locks, and their proper management using OpenEdge SQL, as described in the following sections.

Contents
  • Work with transaction control
  • Transactions and isolation levels
  • Understand transactions and locking
  • Enhance performance with locking hints
  • Monitor locking and database performance
  • Authorized data truncation
  • Autonomous schema update
TitleResults for “How to create a CRG?”Also Available inAlert