1-TO CREATE A USER ACCOUNT

Connect to a database as a
Connect to a database as a
user who has DBA privileges
user who has DBA privileges

DB Structure View >> Security Security tab becomes active in the DB Details view

Security tab >> Add user to Create user dialog appears

database 

Maximum Characters: 32, No blanks

Type a user ID
Note: Once a user is created, you can log in to DB Navigator only with a defined user ID

Type a password and confirm Maximum Characters: 32, No blanks

it(Optional) Note: The password is case-sensitive; user IDs are not case-sensitive

Choose: Grant DBA Privilege Note: If the first account does not have DBA privileges, you cannot add a DBA account or any additional user accounts
or Grant Resource Privilege

Click Create SQL Script generated, executed and committed

The new user appears in the

list in the Security tab and is
added to the _User table
 MAINTAIN A USER ACCOUNT
View Chan Delet Gran Chan
ing ging ing ting ging
●
Deleting OpenEdge database
●
Viewing OpenEdge ●
Changing OpenEdge user accounts
●
Granting privileges to ●
Changing privileges for
database user account database user account
●
Connect to a database as a OpenEdge database user OpenEdge database user
user who has DBA privileges accounts accounts
information passwords
●
DBA Structure View >> ●
Connect to a database as a
●
Connect to a database as a user
●
DBA Structure View >> ●
Connect to a database as a who has DBA privileges
Security Node. Security tab user who has DBA privileges Security Node. Security tab user who has DBA
appears in the DB Details
●
DBA Structure View >> Security
appears in the DB Details ●
DBA Structure View >> privileges Node. Security tab appears in
View
View Security Node. Security tab ●
DBA Structure View >> the DB Details View
●
Security Tab >> Select one or
●
DB Details view row:  appears in the DB Details Security ●
Security Tab >> Select user
more user accounts(SHIFT account >> Click the DBA Rights
Authenticated user(Type: View key: Selecting range of users, Node. Security tab
Internal)  or SQL privilege ●
Security Tab >> User or the Resource Rights cell >>
CTRL key: Selecting appears in the DB Details Choose Yes or No in Combo Box
●
DB Details view displays the Account (Type: Internal) >> individual rows) >> Drop User View ●
Note: You cannot change
following information: User Change user password >> >> Confirm >> OK ●
Security Tab >> Add SQL privileges on the account that
ID, Name, Account Type, New Password >> Confirm ●
Note: Deleted accounts list: Privilege >> Same as you use to log in to the
DBA Rights, Account Rights Password >> OK Select Show Inactive creating user account database
Privileges
 2-TROUBLESHOOTING THE DATABASE CONNECTION
Connections View >> Connection profiles >> Database Connections
●
Ensure the string in the Name field is unique and indicates the correct driver.
●
Ensure the Driver Class Name field (at the bottom) is correct. The Progress OpenEdge JDBC Driver (DataDirect) class name is  com.ddtek.jdbc.openedge.OpenEdgeDriver
●
Click the Extra Class Path tab. Verify that the following pathnames appear:  
●
install_dir\oeide\architect\eclipse\plugins\com.progress.dbnavigator_2.1.0\
drivers\openedge.jarinstall_dir\oeide\architect\eclipse\plugins\com.progress.dbnavigator_2.1.0\
drivers\base.jarinstall_dir\oeide\architect\eclipse\plugins\com.progress.dbnavigator_2.1.0\drivers\util.jar
●
Note: The install_dir path refers to the complete path of your product installation. You must provide this complete pathname to configure the driver properly

Right-click, and choose Edit Connection Profile

Check the URL in the Connection Profile dialog box

Close the Edit Connection Profile dialog once you confirm that the URL is correct

Choose Windows > Preferences > Progress Databases > DB Navigator > JDBC Drivers to open the JDBC Drivers preferences page

Select the driver you want to configure from the list, and then choose Edit. The Edit Driver dialog appears

In the Edit Driver dialog:

If a required driver is not listed in the Extra Class Path list, click Add

Choose the required driver; repeat for each additional driver required

Use Delete to remove any incorrect pathnames

 3-How To Maintain Database Errors 
• One common data-entry error is attempting to add a duplicate record; that is, trying to create a record using a unique
key that already exists in the database. Suppose, for example, that a user tries to add a customer with custnum =
1 (where custnum is a unique key), but a customer record with this custnum value already exists. The attempt fails and
ABL generates an error.

• When this type of error occurs, ABL tries to resolve it by working back through the procedure, looking at each block
header until it finds the closest block with the error-handling property, and then undoing and retrying the block.
However, because the DataServer is accessing a non-OpenEdge data source, OpenEdge cannot detect duplicate-key
errors until the end of a transaction block. Therefore, if an error occurs in a subtransaction, ABL cannot detect it until
the end of the entire transaction block and must perform default error handling for the entire transaction block.

• Suppose that the user enters an existing state while Progress is processing the DO block. When this duplicate-key
entry occurs for an OpenEdge database, Progress returns control to the DO block, displays a message that the record
exists, and reprompts the user for a state abbreviation.However, with the DataServer, if a duplicate key entry occurs in
the DO block, Progress returns control to the REPEAT block rather than the DO block. As a result, the procedure
reprompts the user for a customer number after the inner transaction completes
• The following example illustrates ABL and DataServer error handling:
rep-blk:
REPEAT:
  PROMPT-FOR customer.custnum. /* User input */
  FIND customer USING customer.custnum NO-ERROR.
  IF AVAILABLE customer THEN
    UPDATE customer.custnum customer.name customer.state. /* User input */
  do-blk:
  DO ON ERROR UNDO do-blk, RETRY do-blk:
    FIND state WHERE st.state = customer.state.
    DISPLAY state.
    SET state. /* User input */
  END.
END.

• If you use NO–ERROR to do your own error handling, you must account for the fact that an ODBC data source creates
or updates a record later than Progress does. For example, the following code does not trap data-source errors,
because the requests to perform the operations have not yet been sent to the data source:

CREATE customer NO-ERROR.

ASSIGN customer.custnum = 45 NO-ERROR.
ASSIGN customer.name = "Smith" NO-ERROR.
 • The validate statement causes the dataserver to send requests to your ODBC data source, so
incorporate it into your error-handling technique, as in the following example:

DEFINE VARIABLE ix AS INTEGER NO-UNDO.

DO TRANSACTION:
CREATE customer NO-ERROR.
  ASSIGN customer.custnum = 45 NO-ERROR.
VALIDATE customer.
IF ERROR-STATUS:ERROR THEN DO:
MESSAGE "error: number of messages = " ERROR-STATUS:NUM-MESSAGES.
    DO ix = 1 TO ERROR-STATUS:NUM-MESSAGES:
MESSAGE "error" ERROR-STATUS:GET-NUMBER(ix)
        ERROR-STATUS:GET-MESSAGE (ix).
END.
UNDO, LEAVE.
END.
ASSIGN customer.name = "Smith" NO-ERROR.
VALIDATE customer.
IF ERROR-STATUS:ERROR THEN . . .
END.

• This code returns data-source errors after the validate statement.

 4-How To Connect To Database
• Establishes a connection to a database. Optionally, the CONNECT statement can also specify a name
for the connection and a username and password for authentication.

Syntax

CONNECT TO connect_string[ AS connection_name][ USER

username][ USING password] ;

Parameters
connect_string
{ DEFAULT |db_name|db_type:T:host_name:port_num:db_name }

•  Arguments to connect must be either string literals enclosed in quotation marks or

character‑string host variables.
The connect_string can be a simple database name or a complete connect_string. A complete connect string has
the components shown in the following table:

Connect string Description

db_type Type of database. The only currently supported
database type is progress
T T directs the SQL engine to use the TCP/IP protocol
host_name Name of the system where the database resides
port_num Port number to use for the connection
db_name Name of the database

• Connection_name:
The name of the connection as either a character literal or host variable. If the CONNECT statement omits a
connection name, the default is the name of the database. Connection names must be unique.

• Username :
User name for authentication of the connection. SQL verifies the user name against a corresponding password
before it connects to the database. The value of the DH_USER environment variable specifies the default user
name. If DH_USER is not set, the value of the user environment variable specifies the default user name.

• Password :
Password for authentication of the connection. SQL verifies the password against a corresponding user name
before it connects to the database.
The value of the DH_PASSWD environment variable determines the default password.
 Example : The following example illustrates the CONNECT statement:

CONNECT TO "salesdb" AS "sales_conn";

CONNECT TO "progress:T:localhost:custdb" AS "cust_conn";
CONNECT TO DEFAULT;

• The first statement shown connects to the salesdb database on the local system.  

• The second statement connects to the custdb database on the local system.

• The last statement connects to the environment-defined database by default.
 5-Query tuning
How you structure a query determines how efficiently you access a database. Using your MS SQL Server data source efficiently
enhances the performance of DataServer applications. The standard approach to enhancement is using selection criteria to
refine access to data, but you can further optimize the DataServer's execution of a query by specifying the OpenEdge QUERY-
TUNING phrase.
 YOU CAN INCLUDE THE QUERY-TUNNING PHRASE IN THESE OPEN-EDGE STATEMENTS:

• FOR EACH
FOR EACH table QUERY-TUNING(query-tuning-optionquery-tuning-option...)
• OPEN QUERY
OPEN QUERY query QUERY-TUNING(query-tuning-optionquery-tuning-option...)
• DO PRESELECT
DO PRESELECT table QUERY-TUNING(query-tuning-optionquery-tuning-option...)
• REPEAT PRESELECT
REPEAT PRESELECT table QUERY-TUNING(query-tuning-optionquery-tuning-option...)
 Query-tuning options
Option Description
ARRAY-MESSAGE Specifies whether the DataServer sends
NO-ARRAY-MESSAGE multiple result rows in a single logical
network message, thereby reducing
network traffic.
Default: ARRAY-MESSAGE, if the query
uses a lookahead cursor.

CACHE-SIZE integer Specifies the size in bytes of the cache

used by lookahead cursors.
Maximum: None.
Default: 30000.

DEBUG EXTENDED Specifies whether the DataServer should

DEBUG SQL print to the dataserv.lg file the debugging
NO-DEBUG information that it generates for a query.
Default: NO-DEBUG.
 Query-tuning options
Option Description
JOIN-BY-SQLDB Specifies whether the DataServer allows its
NO-JOIN-BY-SQLDB data source to perform a join.
Default: JOIN-BY-SQLDB

LOOKAHEAD Specifies whether the DataServer uses

NO-LOOKAHEAD lookahead or standard cursors.
Default: LOOKAHEAD, when statements
use NO-LOCK or when statements
use SHARE-
LOCK with TXN_ISOLATION level set to 1
(read uncommitted.)

SEPARATE-CONNECTION Specifies whether each cursor should use a

NO-SEPARATE-CONNECTION separate database connection.
Default: NO-SEPARATE-
CONNECTION except in certain cases.
 Query-tuning options
Option Description
NO-QUERY-ORDER-ADDED Specifies that OpenEdge should not choose
an index in the absence of a USE-
INDEX or BY clause in the query request.

NO-QUERY-UNIQUE-ADDED Specifies that OpenEdge should omit the

record identifier from the end of the query's
generated ORDER BY clause when trying to
obtain record uniqueness from a selected
non-unique index

FIREHOSE-CURSOR Specifies at the query level that the

NO-FIREHOSE-CURSOR firehose cursor type should be considered
to satisfy the query when the NO-
LOCK phrase is used.

SELECT-ON-JOIN When performing an outer join operation,

JOIN-ON-SELECT SELECT-ON-JOIN specifies that any search
condition separate from the join predicate
be bracketed after the join is performed.
Default: JOIN-ON-SELECT.
 6-Query tuning
The following example shows how to use the QUERY-TUNING phrase
to enhance performance. It includes a join, JOIN-BY-SQLDB.

FOR EACH customer, EACH order OF customer WHERE order.ordernum GT 20

   BY customer.custnum QUERY-TUNING (JOIN-BY-SQLDB)

• Note: The DataServer does not issue errors or warnings if it does not

apply the QUERY-TUNING options that you specify.
 HOW TO IDENTIFY SLOW SQL

• Generate an Actual Execution Plan.

• Monitor Resource Usage.
• Use the Database Engine Tuning Advisor.
• Find Slow Queries With SQL DMVs.
• Query Reporting via APM Solutions.
• SQL Server Extended Events.
 7-Identify
Database Performance
Bottleneck
 Parameters affecting the database
performance.
Applications running on your system should not compete with the database for system resources.
Because system and application performance can vary greatly depending on the configuration
The following factors can affect server performance:
      CPU usage
      Disk I/O relating to database, before-image, and after-image I/O
      Memory usage
      Database and index fragmentation

CPU Usage: To use your system to its full potential, the CPU should be busy most of the time. An idle CPU
or unproductive CPU processing can indicate a bottleneck. Use operating system utilities to monitor CPU
usage.
• If performance is inadequate and your CPU is idle, the CPU might be waiting for another resource.
Identify the bottleneck and eliminate it so that the CPU can process work efficiently. Use PROMON to
monitor database activity.

• Disk I/O : Because reading and writing data to disk is a relatively slow operation, disk I/O is a common
database performance bottleneck. The database engine performs three primary types of I/O operations.
Database I/O
Before-image I/O
After-image I/O (relevant only if after-imaging is enabled)
 Reduce Disk I/O bottleneck

The best way to reduce disk I/O bottlenecks is to spread I/O across several
physical disks, allowing multiple disk accesses to occur concurrently. You can
extend files across many disk volumes or file systems.
 What is Before-image and how to reduce
bottleneck.
• Before-imaging is always enabled to let the database engine recover transactions
if the system fails.
• This mechanism is extremely important for database reliability, but it creates a
significant amount of I/O that can affect performance. In addition, before-image
I/O is usually the first and most likely cause of I/O bottlenecks.
• The engine must always record a change in the BI file before it can record a change
in the database and after-image files. If BI activity creates an I/O bottleneck, all
other database activities are affected.
 What is Before-image and how to reduce
bottleneck.

You can reduce the I/O impact of before-imaging by:

Moving the BI file to its own disk
Providing more BI buffer
Increasing the BI cluster size
Increasing the BI block size
 What is After imaging and reduce bottlenecks

After-imaging is an optional recovery mechanism that lets you recover data

and transactions if a disk fails. AI files must be kept on separate disks from the
database and BI files, so after-imaging I/O activity does not contribute to I/O
activity on the disks where BI and database files are stored. However, after-
imaging creates a significant amount of I/O that can affect performance. 
 What is After imaging and reduce bottlenecks

Reduce the I/O impact of after-imaging by:

Using an after-image writer (AIW) on systems with an Enterprise database
license
Raising the AI block size
 Memory Usage

Many of the techniques for improving server performance involve using

memory to avoid disk I/O whenever possible. In general, you spend memory to
improve performance. However, if the amount of memory on your system is
limited, you can overload memory resources, causing your system to page.
Paging can affect performance more severely than a reasonable amount of
disk I/O. You must determine the point where memory use and disk I/O is
balanced to provide optimal performance. In other words, you must budget
the amount of memory you can afford to spend to improve performance.
 Database fragmentation

Over time, as records are deleted from a database and new records are added,
gaps can occur on the disk where the data is stored. This fragmentation can
cause inefficient disk space usage and poor performance with sequential
reads. You can eliminate fragmentation by dumping and reloading the
database. 
 Indexing

The optimal degree of index block utilization depends on the type of database access
performed.
Retrieval-intensive applications generally perform better when the index blocks are close
to full since the database engine has to access fewer blocks to retrieve a record.
The larger the index, the greater the potential for improving performance by compacting
the index.
Update-intensive applications, on the other hand, perform better with loosely packed
indexes because there is room to insert new keys without having to allocate more blocks.
 Indexing

 Index analysis provides the utilization information you require to make

decisions. Choose a balance between tightly packed indexes and under-
utilized indexes, depending on your data and applications. 
8-Backup and Restore
Table from Backup Set
 Backup database

For backing up the database we will use the PROBKUP utility.

There are two types of backups
1. Incremental backup
2. Full backup
Consider a database name “devel.db”
Following commands will illustrate the methods of backing up the database by both the methods:
 1.verify that the database is not in use
“proutil devel -C BUSY ”
 Backup database

2.Shut down devel.db.
“proshut devel”
3. Run PROBKUP -estimate to determine how much media is necessary for the
backup, since this is the first time you are making a backup of the database
“probkup devel a:\devback -com -red 5 -scan -estimate”
“probkup devel incremental /dev/null -estimate”
 Backup database

4. Enter the following command to perform database backup

full
“probkup devel a:\devback -vs 708 -verbose -com -red 5 -scan”
Incremental
“probkup devel a:\devback -vs 708 -verbose -com -red 5 -scan”
 Backup database

To perform online full backup skip step 2-4 as online backup does not require to
shut down the database.
Full
“probkup online devel a:\devback -verbose -com -red 5 ”
Incremental
“probkup online devel incremental /dev/rrm/0m -vs 35
    -bf 20 -verbose -io 1 -com -red 5”
 Backup database

“/dev/rrm/0m”
Specifies the output destination is a tape drive, /dev/rrm/0m.

“a:\devback”
Specifies the output destination is a file, \devback, on the a: drive.

“online”
Specifies that the backup is an online backup.
 Restore Database

The full/ incremental Database restoration in openEdge progress is done by PROREST Utility.

“prorest dbnamedevice-name{-list | -vp | -vf}”
dbname
Specifies the name of the database where you want to restore the backups.
device-name
Identifies the directory pathname of the input device or standard file from which you are
restoring the data
 Restore Database
-list
Provides a description of all application data storage areas contained within a
database backup. Use the information to create a new structure description
file and database so you can restore the backup. For additional information,
see Obtaining storage area descriptions using PROREST.
-vp
Specifies that the restore utility reads the backup volumes and computes and
compares the backup block cyclical redundancy checks (CRCs) with those in
the block headers.
 Restore Database

To recover any data from a bad block, you must have specified a redundancy
factor when you performed the database backup. See Error-correction blocks
on page 117 for more information about error-correction blocks and data
recovery.
-vf
Specifies that the restore utility compares the backup to the database block-
for-block. Do not compare the backup to a database that is in use.
 9-How to use Web Console for add database for monitoring

Step 1: Starting the Database Administration Console in OpenEdge Management or OpenEdge Explorer
Step 2: Accessing the Database Administration Console

Step 3: Adding a database connection

Step 4: Enabling the database for multi-tenancy

Step 5: Creating a super-tenant

Step 6: Creating a domain for the super-tenant

Step 7: Adding a user for the super-tenant

Step 8: Setting up the super-tenant's database login user credentials

Step 9: Creating a regular tenant, its domain, and its users

Step 10: Enabling tables for multi-tenancy
 10-How to create database backup job for schedule

Step 1 : In OpenEdge Management, click Jobs > New > Job from a Template.

Step 2: In the Database Maintenance area of the Create Custom Job page,click:
           The OnlineBackup link to create an online backup job.
           The OfflineBackup link to create an offline backup job.

Step 3:  Specify a name for the job in the Name field
Step 4: Optionally, provide a description for the job in the
Description field.
Step 5:  From the Database list, select the database users want to
back up.

Step 6: In the Backup device section, specify the backup medium.

           If users select Tape, specify the absolute path for the tape device.
           If users select Disk, specify the location for the backup in the Directory field and the name of the backup in
the File name field. 
 If users want to overwrite an existing backup at the specified location, select the Overwrite file option.

 Step 7: If users want to perform an incremental backup, select the

Incremental option.
Step 8: Optionally, specify values for various backup parameters such as volume size (-vs),
redundancy factor (red), estimate size (-estimate), 
compression (-com), and no recovery (-norecover).
 

Step 9 : Optionally, select the Debug log file option to generate a debug log file.
           Once the job is run, the debug log file becomes available through the debug log file link. The
file provides diagnostic details that help users debug the job.

Step 10: Click Save to save the backup job.

 11-How to configure high availability Database Cluster
Use this checklist to complete a new installation where the metadata repository
database system and services tier (IBM® WebSphere® Application Server) are set
up in a clustered topology. The engine tier is set up in an active-passive high
availability configuration.
Complete the following steps:
•Plan the topology
•Prepare the database cluster
•Prepare the IBM InfoSphere® Information Server databases within the cluster
•Prepare the WebSphere Application Server cluster
•Run the InfoSphere Information Server installation program to install InfoSphere Information Server software for
the services tier and cause it to communicate with the database cluster
•Configure WebSphere Application Server to communicate with the database cluster
•Install and configure high-availability cluster management software on the engine tier servers
•Install and configure the engine tier
•Install and configure the client tier
•Test the installation
•Configure the cluster
•Perform postinstallation configuration tasks
 12-How to switchover/failover database in cluster
• You must define and modify the environment variables described for your operating system
in the following sections for Failover Clusters to operate correctly.
AIX HPUX
• Set PSC_CLUSTER_PATH to the install directory for OpenEdge • Set PSC_CLUSTER_PATH to the install directory for OpenEdge
• Set JREHOME to the Java run-time environment install • Modify PATH to include $PSC_CLUSTER_PATH/bin
directory • Modify SHLIB_PATH to
• Modify PATH to include $PSC_CLUSTER_PATH/bin include $PSC_CLUSTER_PATH/lib and $PSC_CLUSTER_PATH/
• Modify LIBPATH to bin
include $PSC_CLUSTER_PATH/lib, $PSC_CLUSTER_PATH/bin,  • Set DLC to the install directory for OpenEdge
$JREHOME/lib, $JREHOME/jre/bin, and $JREHOME/jre/classic
• Set DLC to the install directory for OpenEdge

SUN Solaris Sparc Windows

• Set PSC_CLUSTER_PATH to the install directory for • Set PSC_CLUSTER_PATH to the install directory for OpenEdge
OpenEdge • DLC/bin/procluster.bak
• Modify PATH to include $PSC_CLUSTER_PATH/bin If the installation of additional products overwrites
the procluster.bat file, the procluster.bak file is saved.
• Modify LD_IBRARY_PATH to If procluster.bat is overwritten, the following error message appears:
include $PSC_CLUSTER_PATH/lib and $PSC_CLUSTER
_PATH/bin
• Set DLC to the install directory for OpenEdge
'pscluster' is not recognized as
an internal or external command,
operable program or batch file
 13-How to monitor database cluster

Tools for monitoring performance

This section describes several tools that you can use to monitor the performance of an
OpenEdge database. It includes information about:

• PROMON utility
• Virtual system tables