Preface
Purpose
Audience
Organization
Using this manual
References to ABL compiler and run-time features
References to ABL data types
Before you begin
Typographical conventions
Examples of syntax descriptions
Long syntax descriptions split across lines
Complex syntax descriptions with both required and optional elements
OpenEdge messages
Obtaining more information about OpenEdge messages
Introduction
MS SQL Server DataServer overview
DataServer components
DataServer for MS SQL Server logic
Schema holder
Security
OpenEdge security
MS SQL Server database security
Additional security considerations
DataServer utilities
DataServer demonstration databases
DataServer configurations
The local, self-service, DataServer configuration
The remote, client/server, DataServer configuration
Configuring distributed DataServer applications using ProBroker
Configuring DataServer applications with a self-service OpenEdge AppServer or Pacific AppServer
Configuring distributed DataServer applications using the Unified Broker Framework
A closer look at the OpenEdge Management or OpenEdge Explorer interface
Using the mergeprop utility
Typical configuration scenarios
SSL-enabled DataServer
SSL enabled DataServer for MS SQL
Distributed DataServer configuration with SSL for MSS
Software requirements
Guidelines for using the DataServer
Some functionality highlights
Using the DataServer for MS SQL Server for the first time
Migrating an OpenEdge database to an MS SQL Server data source
Documentation resources
Initial Programming Considerations
Database design issues
OpenEdge database objects and MS SQL Server database objects
Naming conventions
MS SQL Server DataServer data source limits
Code pages
Client code page
Code page, collation, and case conflicts
Support for Unicode
Configuration requirements
Data type support
Indexes and sorting
USE-INDEX and BY clause considerations
Dummy indexes for sort order
Unique indexes
Large key entry support
Case-sensitivity
Interaction of code page, collation, and case sensitivity
Initial value
MS SQL Server data source views
Triggers
OpenEdge sequences
Native Sequence generator
Support for 64-bit sequences
Data types
Working with unsupported data types
Working with non-updatable data types
Processing considerations for 32-bit and 64-bit data types
Processing activities that require additional action
Determining your application needs
DataServer for MS SQL Server support for datetime data types
Working with MS SQL Server and ABL datetime data types
Legacy mapping based on MAP to MSS DateTime:
Performing data type conversions
Enabling Datetime data types using the Data Administration tool
Update/Add Table definitions utility
Schema Migration > OpenEdge DB to MS SQL Server
Verify Table Definitions and Adjust Schema utilities
Generate delta.sql OpenEdge to MS SQL Server utility
Dump as Create Table Statement
Default and Special Datetime Default Values
Using Datetime Data Types with Stored Procedures
Datetime index components
Using datetime data types in a WHERE clause
Support for OpenEdge ABL CLOB data type
ABL-to-MS SQL Server data type mapping
Support for OpenEdge ABL BLOB data type
ABL-to-MS SQL Server data type mapping
User-defined data types
Arrays
Unknown value (?)
Zero-length character strings
_BLANKDEFAULT
Record creation
Data source record locking
Share locks
Exclusive locks
Handling lock timeouts
Additional record locking details
NO-LOCK
Locking impact on queries
Locking examples
The NO-WAIT option
Transactions
Transaction scoping and buffer management
Error handling
Overflow checking
Cursors
Forward and backward scrolling
Impact of MAX-ROWS
Block cursors
Considering when to use block cursors and firehose block cursors
Additional details about block cursor behavior
Block cursors on versus off
Block versus lookahead cursors
ABL issues
ROWID function
RECID function
DEFINE BROWSE statement
Field lists
FIND statements
FIND NEXT statement
FIND PREV and FIND LAST statements
Compiling OpenEdge procedures
r-code
Relaxing CRC Validation Rules for pre-compiled r-code
Adding a New Field
Changing the Precision of a Character Column
FOR statements
Features and language differences
RDBMS stored procedures
RDBMS Stored Procedure Details
Overview
Defining native stored procedures to ABL
RDBMS stored procedure basics
Run Stored-Procedure details
Complete syntax reference for Run Stored-Procedure
Run Stored-Procedure statement
LOAD-RESULT-INTO phrase
PROC-STATUS phrase
PROC-HANDLE phrase
NO-ERROR option
PARAM phrase
Close Stored-Procedure statement
Run Stored-Proc statement execution without the LOAD-RESULT-INTO phrase
Run Stored-Proc statement execution with the LOAD-RESULT-INTO phrase
Run Stored-Proc statement execution using the send-sql-statement option
Without the Load-Result-Into option
With the Load-Result-Into option
Data output and retrieval options
Return codes
Values of output parameters defined when creating a procedure
Results retrieved from a database
Using the OpenEdge-supplied proc-text-buffer for row results
Defining a special view on the MS SQL Server data source to use as a buffer for row results
Loading results into a temp-table
Interfacing with RDBMS stored procedures
Retrieving return codes
Retrieving output parameter values
Retrieving result sets using proc-text-buffer
Technique to use proc-text-buffer
Assessing result sets obtained from the proc-text-buffer technique
Defining a view to use as a buffer
Technique to define a view to use as a buffer
Assessing result sets obtained by defining a view as buffer technique
Loading result sets into temp-tables
Getting started
Employing additional enhancements
Creating a temp-table layout plan
Using a temp-table handle with an unprepared dynamic temp-table
Details about a dynamic temp-table in an unprepared state
Details about a dynamic temp-table in a prepared state
Additional temp-table examples
Handling errors
Trapping errors when using Load-Result-Into
Error messages related to using the Load-Result-Into phrase
ROWID Support
Understanding the ROWID implementation
ROWID characteristics
ROWID: Standard ABL behavior
Using ROWID with RUN STORED-PROCEDURE and LOAD-RESULT-INTO
Additional ProDataSet support
Additional Features to Enhance DataServer Performance
Enhancements overview
Connection pooling
Main components
Considerations when using ODBC connection pooling and DataServer connection management
ODBC Connection Pooling
DataServer connection management
Enabling connection management
Disabling connection management
Connections exclusively for stored procedures
Impact on commits in stored procedures
Firehose, Firehose block, and Fast Forward-Only Cursors
Firehose cursors
Firehose block cursors
Firehose and Firehose block exclusions
Fast Forward-Only cursors
Monitoring cursor and connection use
Monitoring connections with qt_debug
Cursor downgrades
Statement cache
OpenEdge query types
Query tuning
Caching records
Join by SQLDB
Writing queries for performance
Modifying the run-time schema check
Skip schema verification
Replacing FIND FIRST for performance
Configuring the DataServer
DataServer components
DataServer configuration prerequisites
Configuring an ODBC driver and registering the data source
Configuring the ODBC driver
Registering your data source
Configuring a local DataServer
Configuring a remote DataServer
Configuring with OpenEdge Management or OpenEdge Explorer
Connecting to a MS SQL Server DataServer broker using the NameServer
Configuring from the command line
The ubroker.properties file
Establish ssl protocol in a distributed DataServer configuration using ubroker.properties file
Editing the ubroker.properties file
Validating ubroker.properties content
Configuring multiple brokers
Using the NameServer
Configuring a self-service DataServer
Configuring the OpenEdge AppServer agent as a self-service DataServer session
Configuring the Pacific AppServer agent as a self-service DataServer session
Creating a schema holder
Before you begin
General schema holder creation tasks
Authorization and Authentication
Database authentication during foreign database connect
Permissions for creating or updating a schema holder
Permissions for connecting a schema holder
Application-specific permissions
Preparing to create the schema holder
Creating a new empty OpenEdge database
Creating a schema holder
Maintaining a schema holder
Updating a deployed schema holder
Typical configuration for a remote client to connect to a remote DataServer
Configuring an ODBC data source
Starting a broker
Starting the schema holder to accept remote client connections
Connecting remote clients to the MS SQL Server DataServer broker
Configuring a remote AppServer
Connecting the DataServer
Starting a local DataServer
Starting a remote DataServer
Starting and stopping a broker process from OpenEdge Management/OpenEdge Explorer and connecting a client
Performing from the Windows host
Performing on the client
Starting and stopping a broker process using MSSMAN
Starting and stopping a broker process from the command line
Starting the Windows client process
Starting the UNIX client process
Connection guidelines
Connections and security in Windows
Connecting a schema holder
Connecting a schema holder at startup
Using a local DataServer configuration
Using a remote DataServer configuration
Unsupported connection parameters
Special connection issues
Binding with connection and startup parameters
Query tuning with connection and startup parameters
Analyzing application execution with Enhanced Logger
LOG-MANAGER system handle attributes and methods
DSLOG-MANAGER system handle attributes and methods
Log entry types
Logging levels
Setting the DataServer logging levels and log entry types
Aggregate logging using performance and interface log entry types at run-time
Server Context Log File Naming
Creating a unique log file per connected client
Enabling ABL to SQL Correlation in Logging
Local schema caching
Connection failures and OpenEdge responses
Connection troubleshooting
Managing connections to an MS SQL Server database
Accessing the DataServer log
Naming the log file
Setting the log file location
Handling log file creation errors
Restrictions on dynamic changes to log file
Using the $UNIQUE_DSLOG environment variable
Accessing the log file using the host machine
The DataServer Tutorial
Demonstration databases for DataServer for MS SQL Server
Preparing to create demonstration databases
DataServer utilities
Prime to ROWID identification
ROWID sub-section of MS SQL Server Conversion
Best index selection
Recommendation for choosing best clustered index
Creating a schema holder
Updating a schema holder
Verifying a schema holder
Verifying a table
Changing connection information in a schema holder
Changing a code page in a schema holder
Deleting a schema holder
Managing server attributes
Overview of foreign database constraint definitions
Constraint definition names
Defining constraint definitions in Data Administration
Defining a primary key constraint definition
Defining a unique key constraint definition
Defining a foreign key constraint definition
Defining a check constraint definition
Defining a default constraint definition
Modifying an existing constraint definition
Activating or deactivating a class of existing constraint definitions
Deleting contstraint definitions
Deleting one existing constraint definition
Deleting all existing constraint definitions
Generating constraint definitions from ROWID
Migrating an OpenEdge database to MS SQL Server
Preparing a data source for the utility
Migrating defined constraint definitions to your foreign data source
Running the OpenEdge DB to MS SQL Server utility
Running the conversion in batch mode
Column width
Adjust schema utility
Delta df to MS SQL Server Incremental Schema Migration utility
Running the Incremental Schema Migration utility interactively
Running the Incremental Schema Migration utility silently (batch mode)
Adjusting field widths during migration
Updating the MS SQL Server database
Modifying a schema holder
Modifying table-level information
Modifying field-level information
Defining the ROWID
Selecting an index to support ROWID
Dumping and Loading Schema Holder Information
Independent OpenEdge MS SQL Server Schema Pull
Running the Independent schema pull utility in batch mode
Adding extended ABL support
Modifying tables to support arrays
Using triggers to enable PROGRESS_RECID support of the ROWID function
Using computed columns to enable PROGRESS_RECID support of the ROWID function
Migrating RECID-trigger mechanism to RECID computed column mechanism
Troubleshooting
Tuning your environment with the -Dsrv startup parameter
ODBC options
DataServer options
Using MS SQL Server and DataServer options
Transaction Management: the AUTOCOMMIT option
Query Result Order: the PRGRS_ALWAYS_INDEX option
Concurrent query execution: the PRGRS_CACHE_CONN option
Connection problems: the PRGRS_CONNECT option
Key-buffer size: the PRGRS_IDBUF option
Locking error messages: the PRGRS_LOCK_ERRORS option
Large rows: the PRGRS_MINBUF option
SQL statements cache: the PRGRS_PREPCACHE option
Concurrent procedure results: the PRGRS_PROC_TRAN option
Cursor characteristics: the PRGRS_STABLE_CURS option
Wait time for asynchronous connections: the PRGRS_WAIT_DELAY option
Preserve cursors: the -Dsrv MSS_PRESERVE_CURS,1 option
RECID/ROWID format: the variable length option
Old format RECID Buffer size: the ZPRGRS_RECID_BUF_SIZE,nnn Option
SVUB,x: the Unified Broker Server Option
Logging options
Using the block cursor switches
Block cursors: the -Dsrv PRGRS_BLOCK_CURS,1 option
Block cursor allocation by table: the -Dsrv PRGRS_TABLE_BLOCKSIZE,nnn option
Total Block cursor allocation: the -Dsrv PRGRS_MAX_BLOCKSIZE,nnn option
Impact of block cursors on cache size: the -Dsrv QT_CACHE_SIZE,nnn option
Adjusting values
ODBC driver problems
Migration Issues
Creating a schema holder
Modifying your application
Modifying your insert trigger
Server Related Command Line Utilities and Startup Parameters
OpenEdge Management or OpenEdge Explorer command line utilities for the DataServer
MSSCONFIG utility
MSSMAN utility
NSCONFIG utility
NSMAN utility
Other command line utilities for the DataServer
PROBRKR command
PROSHUT command
DataServer startup parameters
Data Type Details
Data type conversion details
Using qt_debug to Analyze Performance
OpenEdge Data Management DataServer for Microsoft® SQL Server
Using qt_debug to Analyze Performance