OPS5: DataServer Best Practices

1
OPS-5 DataServer Best Practices

• Revised Revisited

David Moloney
Software Architect
2
Under Development

• This talk includes information about potential
  future products and/or product enhancements.
• What I am going to say reflects our current
  thinking, but the information contained herein is
  preliminary and subject to change. Any future
  products we ultimately deliver may be materially
  different from what is described here.

3
Agenda
The path to successful DataServer development
deployment

• DataServers Best Practices

• Why ?
• When to use ?
• What are they ?

• Why ?
• When to use ?
• What are they ?

4
DataServers WHY ?

• Has a customer, your partners or your company
  ever asked you
• Does your OpenEdge Application run against MS
  SQL Server ?
• Oracle ?
• Some other data source ?

5
DataServers WHEN ?

• When yes on the WHY question would be
  compelling
• Yes is compelling when theres a
  willingness to
• Invest mind share in the foreign data source
• Re-examine parts of your OpenEdge application
• Compromise performance for conformance
• in light of a compelling business case,
  corporate decision or market opportunity.

6
What is a DataServer ?

• A redirection layer for database requests in an
  OpenEdge client or agent.
• OpenEdge database request translation
• Interface layer communication with a foreign data
  source

7
What is a DataServer ?
OpenEdge Database
8
What is a DataServer ?
MS SQL Server
ODBC
9
What is a DataServer ?
MS SQL Server
ODBC
Oracle SQL Server
OCI
10
What is a DataServer ?
MS SQL Server
ODBC
DataServer
Oracle SQL Server
OCI
11
What is a DataServer ?
MS SQL Server
DataServer for MSS
ODBC
(process)
Oracle SQL Server
DataServer for Oracle
OCI
(process)
12
What is a DataServer ?
DataServer
OpenEdge Database
OpenEdge Database
Schema Holder
13
What is a DataServer ?
MS SQL Server
ODBC
DataServer
SQL
ABL
Oracle SQL Server
OpenEdge Database
OCI
Schema Holder
14
Agenda
The path to successful DataServer development
deployment

• DataServers Best Practices

• Why v
• When to use v
• What are they v

• Why ?
• When to use ?
• What are they ?

15
WHY Use DataServer Best Practices ?

• Were twice as likely to avoid pain than to
  pursue gain
• Why arent DataServers just painless load and
  go ?

16
WHY Use DataServer Best Practices ?

• AppreciateYou are replacing the strengths of
  your OpenEdge Database with those of another data
  source
• Deference is given to the foreign data source on
• Set-based SQL design
• Cursor Consistency
• Security
• Transaction Control
• Lock Management
• Query Optimization Indexing
• Constraints

17
WHY Use DataServer Best Practices ?

• AppreciateYou are replacing the features of
  your OpenEdge Database with those of another data
  source
• Context resides in the foreign data source for
• Data Types Fixed versus variable length strings
• Sequences, Triggers, Constraint implementations
• Schema, Naming, Qualifying, Networking
  Conventions
• NULLs versus UNKNOWNs
• PL/SQL or T-SQL versus ABL

18
WHY Use DataServer Best Practices ?

• Because you need a map !

19
WHEN To Use DataServer Best Practices ?

• Always !
• The Flavors of pain relief
• Minimize loss of performance
• Maximize conformance
• Reduce time to market

20
WHEN To Use DataServer Best Practices ?

• Conformance Example
• Picking an Index

FOR EACH customerEND. FOR EACH employee
USE-INDEX departmentEND.FOR EACH invoice BY
zip-codeEND.
21
WHEN To Use DataServer Best Practices ?

• Performance Example
• Who should do the join ?

FOR EACH customer, EACH order OF customer
QUERY-TUNING (NO-JOIN-BY-SQLDB)
DISPLAY customer.name customer.cust-num
customer.postal-code order.order-date. END.
22
WHEN To Use DataServer Best Practices ?

• Performance Example
• What will be joined ?

FOR EACH customer, EACH order OF customer
WHERE LENGTH(RIGHT-TRIM(customer.
name)) gt 5 DISPLAY customer.name
customer.cust-num
customer.postal-code order.order-date. END.
23
WHEN To Use DataServer Best Practices ?

• Performance Example
• Where is the join done ?

FOR EACH customer, FIRST order OUTER-JOIN OF
customer WHERE order.order-num lt 50 DISPLAY
customer.name customer.cust-num
customer.postal-code order.order-date. END.
24
WHAT are the DataServer Best Practices ?
The Best Practices White Paper

• Steps for Successful DataServer Development and
  Deployment
• http//www.psdn.com/library/entry.jspa?externalID
  1320
• (Located on PSDN)
• DataServer Guides
• Chapter(s) on Programming Considerations

25
WHAT are the DataServer Best Practices ?
In the Best Practices White Paper

• STEP ONE Eliminate DBMS differences
• STEP TWO Optimize the Application, the
  DataServer, and the DBMS.

26
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• First get it to work
• Compile incompatibilities are minimal
• SETUSERID
• COUNT-OF
• CONTAINS
• RECID
• If ROWID index is not a unique integer

27
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Then there are the nuances
• FIND cust WHERE name BEGINS SI.
• FOR EACH cust WHERE name lt CHR(126).
• FOR EACH cust WHERE name ltgt Bob

28
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping Availability

DEFINE BUFFER xcust FOR cust.CREATE cust.ASSIGN
cust-num 111.FIND xcust WHERE cust.cust-num
111.
29
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping Availability

DEFINE BUFFER xcust FOR cust.CREATE cust.ASSIGN
cust-num 111.VALIDATE cust.FIND xcust WHERE
cust.cust-num 111.
30
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping capturing validation

FOR FIRST cust EXCLUSIVE-LOCK ASSIGN name
FILL(a,35) NO-ERROR. VALIDATE NO-ERROR. IF
error-statuserror THEN DO ltsome error
processinggt UNDO, LEAVE. END.END.
31
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping Transactions

DEF VAR num AS INT INIT 103.DO TRANSACTION
FIND cust WHERE cust num
EXCLUSIVE-LOCK. ASSIGN name
Bob.END.FIND cust WHERE cust-num
num.DISPLAY name.
32
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping Transactions

DO TRANSACTION FIND FIRST cust
EXCLUSIVE-LOCK.END.RELEASE cust.DO
TRANSACTION FIND FIRST cust
EXCLUSIVE-LOCK.END.
33
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping Transactions

REPEAT FIND FIRST cust. ASSIGN cust-num
1. do-blk DO ON ERROR UNDO do-blk, RETRY
do-blk FIND state WHERE st.st cust.st.
SET state. END.END. DISPLAY state.
34
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping Transactions

REPEAT FIND FIRST cust. ASSIGN cust-num
1. do-blk DO ON ERROR UNDO do-blk, RETRY
do-blk FIND state WHERE st.st cust.st.
SET state. VALIDATE state.
END.END.DISPLAY state.
35
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping Visibility

FOR FIRST cust NO-LOCK BY cust-numEND.IF
AVAILABLE cust THEN MESSAGE cust found
VIEW-AS ALERT-BOX.
36
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping Locking

FOR FIRST cust FOR FIRST cust NO-LOCK FOR FIRST
cust SHARE-LOCK FOR FIRST cust EXCLUSIVE-LOCK
37
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Record Scoping Cursor Positioning

FIND cust WHERE cust-num 123. lt Do some other
stuff gt FIND cust WHERE name Bob.
38
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences
Best Practice

• Just code around certain nuances

GLOBAL-DEFINE DB-TYPE ORACLEIF NOT DEFINED
(MSSQLS) THEN IF DBTYPE(dbname)
PROGRESS THEN RUN OpenEdge-database-code EL
SE RUN DataServer-foreign-interface-code
39
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences
Best Practice

• Be explicit about
• Scope of your recordsTiming of your write
  operations

VALIDATE RELEASE RECID
40
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences
Best Practice

• Code knowing that for DataServers
• Transactions narrow record scoping
• Transactions release locks and should release
  buffers

DO TRANSACTIONASSIGN cust.name
Bob. END. RELEASE cust.
41
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences
Best Practice

• Avoid SHARE-LOCK
• Adopt a pessimistic locking model
• NO-LOCK
• EXCLUSIVE-LOCK

FOR EACH cust EXCLUSIVE-LOCK ASSIGN name
Bob.END.
42
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences
Best Practice

• DataServers only reposition FIND statement
  cursors.Set-based result statements are
  contained.
• FOR EACH
• QUERY
• DYNAMIC FIND
• RELEASE set-based buffers that should be out of
  scope after these,

FOR EACH cust DISPLAY cust.END. RELEASE
cust.
43
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences
Do you see a theme emerging ?
44
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Consider the difference

FIND customer. versus FOR EACH customer.
45
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• Consider the difference

FIND customer USE-INDEX cust-num. versus FOR
EACH customer BY cust-num.
46
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences
Best Practice

• Code with a set-based mind set !
• FOR EACH
• QUERY
• DYNAMIC FIND
• INDEXED-REPOSITION
• Avoid OLTP-biased FIND statements

47
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences
Know Thy Data Source
48
WHAT are the DataServer Best Practices ?
STEP ONE Eliminate DBMS differences

• STEP TWO Optimize the Application, the
  DataServer, and the DBMS.
• On PSDN, see Performance Study at the end of
  DB-16 In Any Case, the Devils in the
  DataServer Details
• http//www.psdn.com/library/entry.jspa?externalID
  3355

49
WHAT are the DataServer Best Practices ?
STEP TWO Optimize Application, DataServer, and
DBMS.
The Importance of ROWID

• ROWID optimizes OpenEdge database access
• DataServers map ROWID to a unique key
• Uniqueness in DataServers enables
• Locking
• Deletions Updates
• FINDs, Queries Browsers
• All cursor positioning such as INDEXED-REPOSITION
• RECID/ROWID functions
• random record access in a non-unique set

50
WHAT are the DataServer Best Practices ?
STEP TWO Optimize Application, DataServer, and
DBMS.
Performance Ground Zero

• Set-based design enhances ROWID reconciliation
• FOR EACH
• QUERY
• DYNAMIC FIND
• INDEXED-REPOSITION

51
WHAT are the DataServer Best Practices ?
STEP TWO Optimize Application, DataServer, and
DBMS.
Best Practice - Performance Top Ten

• 1. Eliminate FIND statements

FIND FIRST cust
FOR FIRST custEND.
FIND LAST cust
FOR LAST custEND.
52
WHAT are the DataServer Best Practices ?
STEP TWO Optimize Application, DataServer, and
DBMS.
FIND FIRST Customer NO-LOCK NO-ERROR. IF
AVAILABLE Customer THEN Cnt 1. REPEAT FIND
NEXT Customer NO-ERROR. IF NOT AVAILABLE
(Customer) THEN LEAVE. Cnt Cnt
1. END. OPEN QUERY q FOR EACH Customer
NO-LOCK. REPEAT GET NEXT q. IF NOT
AVAILABLE Customer THEN LEAVE. Cnt Cnt
1. END. CLOSE QUERY q.
53
WHAT are the DataServer Best Practices ?
STEP TWO Optimize Application, DataServer, and
DBMS.
Best Practice - Performance Top Ten

• 2 Stored Procedures
• 3 Avoid SHARE-LOCK, Consider NO-LOCK
• 4 Tune your database, especially indexes
• 5 Resolve queries/joins on the server
• 6 Use field lists and/or views
• 7 Use QUERY-TUNING where appropriate
• 8 Use Dsrv connect options
• 9 Client startup options

54
In Summary
STEP TWO Optimize Application, DataServer, and
DBMS.
Best Practice - Performance Top Ten

• 10. STEP ONE Eliminate DBMS differences

STEP ONE resonates with
STEP TWO
55
For More Information, go to
Redevelopment Tools

• Basic Tools
• Manual Refactoring (and 10.1C unsupported switch
  -clw) ???
• Performance logs
• Progress Debugger
• Compile XREF
• Progress Profiler Tool (unsupported but ships
  w/OpenEdge)
• Other Resources
• Proparse http//www.joanju.com
• Prolint-http http//www.prolint.org
• ProRefactor http//www.prorefactor.org
• PSDN
• OpenEdge DataServers category (Best Practices
  paper, Performance Study)
• Forum OpenEdge RDBMS Data Management

56
Relevant Exchange Sessions

• OPS-25 Unicode the DataServer
• Wednesday, June 11th
• 130 230
• Grand Ballroom L-M

57
?
Questions
58
Thank You
59
(No Transcript)