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çais (France)Italiano (Italy)Português (Brasil)日本語Русский (Russia)中文 (简体) (China)中文 (繁體, 台灣) (Taiwan)ar-AR
  • Login

OpenEdge® Data Management: SQL Development

Manage Open Edge SQL and ABL database connections

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
Table of Contents
  • 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 default areas
        • Configure default areas
        • Display default areas
        • Create objects in default areas
      • 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
  • Dynamic data masking in OpenEdge SQL server
  • 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

Manage Open Edge SQL and ABL database connections

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
Print
  • Last Updated: January 17, 2024
  • 1 minute read
    • OpenEdge
    • Version 12.8
    • Documentation
Contents
  • Determine database server requirements
  • Start SQL and ABL brokers
TitleResults for “How to create a CRG?”Also Available inAlert