1 / 71

DB-16: In Any Case, the Devil’s in the DataServer Details

DB-16: In Any Case, the Devil’s in the DataServer Details . That which we persist in doing becomes easier, not that the task itself has become easier, but that our ability to perform it has improved. Ralph Waldo Emerson (1803 - 1882). David Moloney. Principal, Progress OpenEdge.

kevyn
Download Presentation

DB-16: In Any Case, the Devil’s in the DataServer Details

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB-16: In Any Case, the Devil’s in the DataServer Details That which we persist in doing becomes easier, not that the task itself has become easier, but that our ability to perform it has improved. Ralph Waldo Emerson (1803 - 1882) David Moloney Principal, Progress OpenEdge

  2. Agenda • A Story • Terminology & Technology Sync-up • The Devils in the Details • Case Studies • ABL & DataServer • ABL & the RDBMS • DataServer & RDBMS • DataServer & API Access Drivers • DataServer Performance • Summary & Questions DB-16: In Any Case, the Devil’s in the DataServer Details

  3. A Story Once Upon A Time … Mary and the three DataServers DB-16: In Any Case, the Devil’s in the DataServer Details

  4. Mary meets Compiler For Each Display Cust ABL DB-16: In Any Case, the Devil’s in the DataServer Details

  5. Client meets Server OpenEdge™ Database DB-16: In Any Case, the Devil’s in the DataServer Details

  6. Where’s the love ? Where’s my pie ? IBM DB2 Oracle Microsoft SQL Server DB-16: In Any Case, the Devil’s in the DataServer Details

  7. For Each What ? Habla “For Each” ? For Each Display Cust ABL DB-16: In Any Case, the Devil’s in the DataServer Details

  8. Hmmm…. For Each Display Cust ABL DB-16: In Any Case, the Devil’s in the DataServer Details

  9. I’ve got Bingo ! For Each Display Cust ABL DB-16: In Any Case, the Devil’s in the DataServer Details

  10. Trusted friends ODBC ODBC Driver Manager Driver Driver Driver OE DB Data Source Data Source Data Source DB2 Informix Sybase Address OCI OCI API For Each Display Cust ABL SQL*Net Data Source Oracle DB-16: In Any Case, the Devil’s in the DataServer Details

  11. Mary makes a house call Hey Man … It’s all about the “For Each” OE DB For Each Display Cust ABL Address DB-16: In Any Case, the Devil’s in the DataServer Details

  12. Bravo ! ABL Address DB-16: In Any Case, the Devil’s in the DataServer Details

  13. The End DB-16: In Any Case, the Devil’s in the DataServer Details

  14. That was then … OE DB For Each Display Cust ABL Address DB-16: In Any Case, the Devil’s in the DataServer Details

  15. Terminology & Technology Sync-up OpenEdge Database MS SQL Server Oracle SQL Server Address DB-16: In Any Case, the Devil’s in the DataServer Details

  16. - - - - Terminology & Technology Sync-up OpenEdge Database MS SQL Server Oracle SQL Server Address DataServer Broker SQL DB-16: In Any Case, the Devil’s in the DataServer Details

  17. - - - - Terminology & Technology Sync-up OpenEdge Database MS SQL Server Oracle SQL Server Item_no Item-no Address DataServer Broker SQL DB-16: In Any Case, the Devil’s in the DataServer Details

  18. - - - - Terminology & Technology Sync-up OpenEdge Database MS SQL Server Oracle SQL Server Item_no Item-no Address DataServer Broker SQL DB-16: In Any Case, the Devil’s in the DataServer Details

  19. - - - - Terminology & Technology Sync-up OpenEdge Database MS SQL Server Oracle SQL Server Item_no Item-no Address DataServer Broker SQL DB-16: In Any Case, the Devil’s in the DataServer Details

  20. - - - - Sync-up: 4-Tier Perspective Tier 1 Tier 4 Tier 2 OpenEdge Database MS SQL Server Oracle SQL Server Tier 3 Address DataServer Broker SQL DB-16: In Any Case, the Devil’s in the DataServer Details

  21. Sync-up: Self-Service Client 3-Tier Perspective Tier 3 Tier 2 OpenEdge Database MS SQL Server Oracle SQL Server Address Tier 1 DB-16: In Any Case, the Devil’s in the DataServer Details

  22. DB2 A - B - C - X - - Y - Z - - - 1 2 3 Schema PUSH SQL Server OpenEdge Schema PULL Sync-up: Application-centric Application Centric - Just another Oracle OCI or ODBC Client Oracle SELECT UPPERCASE (name) FROM Customer SELECT TO UPPER(name) FROM Customer SELECT TO UPPER(name) FROM Customer OpenEdge SELECT UPPER(name) FROM Customer FOR EACH Customer DataServer OpenEdge Client DB-16: In Any Case, the Devil’s in the DataServer Details

  23. - - - - Sync-up: Client-Server Perspective API Server Tier OpenEdge Client Tier MS SQL Server Oracle SQL Server OpenEdge Database API Client Tier DataServer Broker OpenEdge Server Tier Address SQL DB-16: In Any Case, the Devil’s in the DataServer Details

  24. Sync-up: Component Perspective Database Component Language Component MS SQL Server Oracle SQL Server OpenEdge Database Access Component DB-16: In Any Case, the Devil’s in the DataServer Details

  25. Sync-up: DataServer Component Layers OpenEdge Client Component & Dictionary Progress ABL Dataserver Layer Dataserver Layer Dataserver Client OpenEdge Server Component Dataserver Server Access Component ODBC OCI Database Component MS SQL Srvr Oracle DB-16: In Any Case, the Devil’s in the DataServer Details

  26. Business Components Data Sources OpenEdge Reference ArchitectureFor Service Oriented Business Applications Design for longevity, flexibility, and competitive advantage Enterprise Services Presentation OpenEdge Component Common Infrastructure Access Component Data Access Database Component DB-16: In Any Case, the Devil’s in the DataServer Details

  27. Case Study: Components ABL Dictionary Schema DataServer API Foreign Data Source DB-16: In Any Case, the Devil’s in the DataServer Details

  28. Case Study: Goals • Appreciate intricacies amongst components • Demonstrate the somewhat “arbitrary” diversity DataServer issues • Create opportunities to discuss important aspects of the DataServer architecture. • Promote the interest of a problem-solver audience DB-16: In Any Case, the Devil’s in the DataServer Details

  29. Case Study: Prerequisite & Disclaimer • DataServer deference given to the database • On Security • On Transaction Control • On Lock Management • On Cursor Consistency DB-16: In Any Case, the Devil’s in the DataServer Details

  30. The Devils in the Details: Case Study #1 Database Dictionary + ABL + I ran a pro-to-<dataserver> migration and compiled some code against the schema holder it built. Everything compiled just fine. Then I built a separate schema holder, pulled definitions from the very same SQL database, and connected to it. But, against this schema holder, the compiled code fails. Why would it matter how we create the schema holder as long as the structure of the SQL database remains exactly the same ! DB-16: In Any Case, the Devil’s in the DataServer Details

  31. - - - - DataServer Migration OpenEdge Database PUSH Item-no MS SQL Server Oracle SQL Server OpenEdge Database Item_no Item_no Item-no Pull DataServer Broker SQL DB-16: In Any Case, the Devil’s in the DataServer Details

  32. The Devils in the Details: Case Study #1 (cont.) Why don’t my schema holder “pull” definitions match the OpenEdge Database they were derived from ? The script that built the SQL Database came directly from OpenEdge Database ! DB-16: In Any Case, the Devil’s in the DataServer Details

  33. - - - - DataServer Pushed & Pulled Independently OpenEdge Database PUSH Item-no MS SQL Server Oracle SQL Server OpenEdge Database Item_no Item_no Item_no Pull DataServer Broker SQL DB-16: In Any Case, the Devil’s in the DataServer Details

  34. - - - - Pull & Adjust Schema OpenEdge Database Item-no MS SQL Server Oracle SQL Server Adjust Schema OpenEdge Database Item_no Item_no Item-no Pull DataServer Broker SQL DB-16: In Any Case, the Devil’s in the DataServer Details

  35. The Devils in the Details: Case Study #2 Database DataServer Dictionary + + ABL + • We deployed a new schema holder to our customers and their production applications started crashing with no warning. We didn’t make any code changes but if we do, the application still crashes, only with a different error. What would cause the DataServer’s inconsistent behavior and how can we get an indication of the problem ? DB-16: In Any Case, the Devil’s in the DataServer Details

  36. The Devils in the Details: Case Study #3 DataServer Dictionary + ABL + • I compiled my application against an OpenEdge Database. Then, I did the same against my Schema Holder. Why did the DataServer r-code grow larger than my OpenEdge database r-code ? DB-16: In Any Case, the Devil’s in the DataServer Details

  37. The Devils in the Details: Case Study #4 Database Dictionary + ABL + • During a migration, I’m getting truncation errors loading data into my SQL Server database . I elected the “load data” option. The schema copied fine but migrating data from my OpenEdge database to a DataServer schema holder fails. Why would the migration create definitions too small for my data ? DB-16: In Any Case, the Devil’s in the DataServer Details

  38. The Devils in the Details: Case Study #5 Database Dictionary + ABL + • I’m migrating an OpenEdge database to a DataServer schema. When I elect to load data, I get NULL-constraint and unique-constraint violations. DB-16: In Any Case, the Devil’s in the DataServer Details

  39. The Devils in the Details: Case Study #6 (Intro.) DataServer Database + + ABL • Record Write & AvailabilityDEFINE BUFFER xcust FOR cust.CREATE cust.cust-num = 111.FIND xcust WHERE xcust.cust-num = 111. DB-16: In Any Case, the Devil’s in the DataServer Details

  40. The Devils in the Details: Case Study #6 (Intro.) DataServer Database + + ABL • Record Write & AvailabilityDEFINE BUFFER xcust FOR cust.CREATE cust.cust-num = 111.VALIDATE cust. /* or RELEASE cust. */ FIND xcust WHERE xcust.cust-num = 111. DB-16: In Any Case, the Devil’s in the DataServer Details

  41. The Devils in the Details: Case Study #6 DataServer Database + + ABL • I’m getting a STOP condition trying to assign a column value after a FIND statement. The application exits after the ASSIGN. How can I be sure this isn’t corrupting my database ? DO TRANSACTION:FIND FIRST cust.ASSIGN name = FILL(“a”,35) NO-ERROR. END. DB-16: In Any Case, the Devil’s in the DataServer Details

  42. The Devils in the Details: Case Study #6 (cont.) DataServer Database + + ABL • But, I’m still getting the error condition DO TRANSACTION:FIND FIRST cust.ASSIGN name = FILL(“a”,35) NO-ERROR.VALIDATE cust. END. DB-16: In Any Case, the Devil’s in the DataServer Details

  43. The Devils in the Details: Case Study #6 (cont.) DataServer Database + + ABL DO TRANSACTION:FIND FIRST cust.ASSIGN name = FILL(“a”,35) /* NO-ERROR */.VALIDATE cust NO-ERROR.IF error-status:error THEN DO: <some error processing> UNDO, LEAVE.END. END. DB-16: In Any Case, the Devil’s in the DataServer Details

  44. The Devils in the Details: Case Study #6 (cont.) DataServer Database + + ABL • But, I’m still getting the error condition DO TRANSACTION ON ERROR UNDO,LEAVE:FIND FIRST cust.ASSIGN name = FILL(“a”,35) NO-ERROR.VALIDATE cust. CATCH Progress.Lang.AppError ae: MESSAGE "Inside AppError Catch". IF ae:GetMessage(1) <> ? THEN MESSAGE ae:GetMessage(1) ae:GetMessageNum(1). ELSE MESSAGE "ReturnError" ae:returnvalue view-as alert-box. DELETE OBJECT ae. END CATCH. CATCH Progress.Lang.ProError pe: MESSAGE "Inside ProError Catch". REPEAT i = 1 TO pe:NumMessages: PUT UNFORMATTED " Error Number: " pe:GetMessageNum(i) FORMAT ">>>>>9" SKIP "Message: " pe:GetMessage(i) FORMAT "x(73)" SKIP. END. DELETE OBJECT pe. END CATCH. END. DB-16: In Any Case, the Devil’s in the DataServer Details

  45. The Devils in the Details: Case Study #6 (cont.) DataServer Database + + ABL • Warning: DO TRANSACTION:FIND FIRST cust EXCLUSIVE-LOCK.ASSIGN name = FILL(“a”,35) NO-ERROR.VALIDATE cust.CONTENTION EXPOSURE ! END. DB-16: In Any Case, the Devil’s in the DataServer Details

  46. The Devils in the Details: Case Study #7 DataServer Database + + ABL • I have two clients running simultaneously.Both create records and lock records exclusively on the same table. Why are they getting “table is use by another user” errors ?DO TRANSACTION: FIND FIRST cust EXCLUSIVE-LOCK. IF AVAILABLE cust THEN ASSIGN name = “Bob”. REPEAT: FIND NEXT cust EXCLUSIVE-LOCK. IF NOT AVAILABLE cust THEN LEAVE. Cnt = Cnt + 1. ASSIGN name = “Bob” + STRING(cnt). END.END. DB-16: In Any Case, the Devil’s in the DataServer Details

  47. The Devils in the Details: Case Study #7 (cont.) DataServer Database + + ABL DO TRANSACTION: FIND FIRST cust EXCLUSIVE-LOCK. IF AVAILABLE cust THEN ASSIGN name = “Bob”. REPEAT:DO TRANSACTION: FIND NEXT cust EXCLUSIVE-LOCK. IF NOT AVAILABLE cust THEN LEAVE. Cnt = Cnt + 1. ASSIGN name = “Bob” + STRING(cnt). END.END. END.END. DB-16: In Any Case, the Devil’s in the DataServer Details

  48. The Devils in the Details: Case Study #7 (cont.) DataServer Database + + ABL DO TRANSACTION:FIND FIRST cust EXCLUSIVE-LOCK. IF AVAILABLE cust THEN ASSIGN name = “Bob”.END.REPEAT:DO TRANSACTION: FIND NEXT cust EXCLUSIVE-LOCK. IF NOT AVAILABLE cust THEN LEAVE. Cnt = Cnt + 1. ASSIGN name = “Bob” + STRING(cnt). END. END.END. DB-16: In Any Case, the Devil’s in the DataServer Details

  49. The Devils in the Details: Case Study #7 (cont.) DataServer Database + + ABL • Another example regarding cursor consistencyDEFINE VARIABLE num AS INT INITIAL 103.DO TRANSACTION: FIND cust WHERE cust = num EXCLUSIVE-LOCK. ASSIGN name = “Bob”.END.FIND cust WHERE cust-num = num.DISPLAY name. DB-16: In Any Case, the Devil’s in the DataServer Details

  50. The Devils in the Details: Case Study #8 DataServer + ABL • Parenthesis around names in the WHERE clause of our join is [slow/fast], generates multiple queries and sends index hints with the queries. • For each customer, each order WHERE (order.custnum) = (cust.custnum): Oracle documentation says joins won’t pass index hints on a server join. If we just remove the parenthesis: • For each customer, each order WHERE order.custnum = cust.custnum: We only get one query, [better/worse] performance and no hints. DB-16: In Any Case, the Devil’s in the DataServer Details

More Related