1 / 33

OpenEdge DataServers How to Connect to Oracle and SQL Server Tips and Techniques

OpenEdge DataServers How to Connect to Oracle and SQL Server Tips and Techniques. Simon Epps. Solutions Engineer, Progress OpenEdge. Agenda Slide. Introduction Today’s Best Practices Tomorrow’s Best Practices Summary Questions and Answers. DataServer Technology Introduction. -. -. -.

terry
Download Presentation

OpenEdge DataServers How to Connect to Oracle and SQL Server Tips and Techniques

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. OpenEdge DataServers How to Connect to Oracle and SQL Server Tips and Techniques Simon Epps Solutions Engineer, Progress OpenEdge

  2. Agenda Slide • Introduction • Today’s Best Practices • Tomorrow’s Best Practices • Summary • Questions and Answers

  3. DataServer Technology Introduction - - - - - - - - - - - - • Respond to Business Drivers • Integration & Corporate Standards • Progress ABL functionality and behavior against non-OpenEdge databases • OLTP processing • Native RDBMS connectivity and functionality • Unix and Windows Clients (GUI, CHUI, UBroker) OpenEdge Database OpenEdge Client DataServer Schema Holder Oracle SQL Server

  4. DataServer Components • A world without the DataServer Technology OpenEdge Client OpenEdge Database ABL

  5. DataServer Components • The DataServer technology • Embedded in to the OpenEdge Client • GUI • CHUI • Unified Brokers OpenEdge Client OpenEdge Database ABL DataServer Oracle SQL Server SQL

  6. DataServer Components INT VARCHAR - - INT CHAR - - - - - - • Schema Holder • Meta data mapping between OpenEdge and SQL • No application data is stored OpenEdge Client OpenEdge Database ABL DataServer Oracle SQL Server SQL

  7. Adopting the OpenEdge DataServer WARNING - Expectations must be set correctly • Not all DBMS’s are created equal • Performance • Scalability • Simulation of ABL features not always possible • No SHARE-LOCK • No WORD INDEXING • Simulation of ABL features not easy with SQL • FIND FIRST takes multiple SQL calls • Must set goals and expectations early with frequent validation checks

  8. Terminology & Technology Sync-up INT VARCHAR - - - - INT CHAR - - - - - - - - OpenEdge Client OpenEdge Database ABL FOR EACH Customer: DISPLAY name. END. DataServer Broker DataServer SQL Oracle SQL Server SQL ODBC OCI

  9. DataServer Technology SQL Versus Progress ABL Same Functionality Same Performance Same Ease of Use Transact SQL Progress ABL DECLARE @STARTDATE DATETIME DECLARE @CustName CHAR, @CustCredit INT DECLARE @C1 INT SELECT @STARTDATE = GETDATE() DECLARE C1 CURSOR FOR SELECT creditlimit FROM customer OPEN C1 FETCH NEXT FROM C1 WHILE (@@fetch_status <> -1) BEGIN BEGIN TRAN UPDATE customer SET CreditLimit = 44333 WHERE CURRENT OF C1 IF @@ERROR <> 0 ROLLBACK TRAN ELSE COMMIT TRAN FETCH NEXT FROM C1 END CLOSE C1 DEALLOCATE C1 SELECT DATEDIFF(MS, @STARTDATE, GETDATE()) as ETIME GO DEF VAR tstart AS INT NO-UNDO. DEF VAR MyRandom AS INT NO-UNDO. MyRandom = RANDOM(10,2000). tstart = ETIME. FOR EACH customer EXCLUSIVE-LOCK: creditlimit = MyRandom. END. DISPLAY ETIME - tstart. ü ü û

  10. Today’s Best Practices • Use the OpenEdge RDBMS where possible • Make it Function Not all ABL functionality can be emulated via SQL • Source scanning utility, Profiler, Data Width utility • Make it Perform (Optional) SQL emulation of ABL can be complex and costly • Use Results Set friendly ABL commands • Progress ABL row set orientated • SQL results set orientated • Leverage RDBMS Stored Procedures • Data Access & • Business logic

  11. Tomorrow’s Best Practices • Use the OpenEdge RDBMS where possible • Make it Function Not all ABL functionality can be emulated via SQL • Source scanning utility, Profiler, Data Width utility • Make it Perform (Optional) SQL emulation of ABL can be complex and costly • Use Results Set friendly ABL commands • Progress ABL row set orientated • SQL results set orientated • Leverage RDBMS Stored Procedures • Data Access & • Business logic Make it easier to stay within the Progress ABL Better ABL integration

  12. Best PracticesWhy focus on RDBMS Stored Procedures? • Benefits • Offload work from client to RDBMS • General RDBMS Best Practices • Akin to OpenEdge Application Server • Considerations • Integrating returned Data • SQL knowledge needed • Source code can become RDBMS dependant

  13. Tomorrow’s Best Practices (today) Leverage the OpenEdge Reference Architecture • Data Access Layer – Black Box Data: • Keep Business Logic in the ABL • Use Stored Procedures for Data access Users Enterprise Services Presentation Layer/s Integration Layer/s ABL Business Logic Business Servicing Layers ProDataSet Data Access Layers Managed Data Stores Unmanaged Data Stores OpenEdge Database Oracle SQL Server

  14. RDBMS Stored ProceduresCurrent techniques CLIENT RDBMS OUTPUT FORMAT “SELECT * FROM Customer” SQL EXEC STORED PROCEDURE DataServer DataServer ABL SELECT * FROM Customer

  15. RDBMS Stored ProceduresCurrent techniques CLIENT RDBMS OUTPUT FORMAT PROC-TEXT-BUFFER “SELECT * FROM Customer” SQL EXEC STORED PROCEDURE DataServer DataServer 4GL FOR EACH PROC-TEXT-BUFFER: DISPLAY PROC-TEXT-BUFFER. “A & A Athletic & Rec 1990 ...” “A & J Sports Inc 1231 …” “A A Collegiate Pennant Co 1303 ...” SELECT * FROM Customer

  16. RDBMS Stored ProceduresCurrent techniques CLIENT RDBMS OUTPUT FORMAT PROC-TEXT-BUFFER SQL EXEC STORED PROCEDURE DataServer ABL PROC-TEXT-BUFFER USER DEFINED STORED PROCEDURE In/Out/Rtn PARAMS Input/Output/ReturnPARAMETERS RDBMS BUFFER ABL BUFFER

  17. RDBMS Stored ProceduresCurrent techniques Level of Integration Work CLIENT RDBMS OUTPUT FORMAT CLIENT RDBMS OUTPUT FORMAT RDBMS ABL LOW HIGH PROC-TEXT-BUFFER PROC-TEXT-BUFFER SQL EXEC STORED PROCEDURE SQL EXEC STORED PROCEDURE DataServer DataServer ABL PROC-TEXT-BUFFER MEDIUM HIGH ABL PROC-TEXT-BUFFER USER DEFINED STORED PROCEDURE USER DEFINED STORED PROCEDURE In/Out/Rtn PARAMS MEDIUM MEDIUM In/Out/Rtn PARAMS RDBMS BUFFER Input/Output/ReturnPARAMETERS RDBMS BUFFER ABL BUFFER ABL BUFFER HIGH LOW

  18. RDBMS Stored Procedures Send-SQL & PROC-TEXT Buffer DEFINE VAR SP-Handle AS INT. RUN STORED-PROC send-sql-statement SP-Handle = PROC-HANDLE ("select * from customer"). FOR EACH PROC-TEXT-BUFFER WHERE PROC-HANDLE = SP-Handle. DISPLAY PROC-TEXT-BUFFER. END. CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = SP-Handle. SQL EXEC RDBMS Stored Procedure SQL Statement ABL Buffer SINGLE CHAR COLUMN

  19. RDBMS Stored Procedures User Defined and BUFFERS DEFINE VAR SP-Handle AS INT. RUN STORED-PROC My-RDBMS-Procedure SP-Handle = PROC-HANDLE. FOR EACH PROC-TEXT-BUFFER WHERE PROC-HANDLE = SP-Handle. DISPLAY PROC-TEXT-BUFFER. FOR EACH My-RDBMS-VIEW WHERE PROC-HANDLE = SP-Handle. DISPLAY My-RDBMS-VIEW. END. User Defined RDBMS Stored Procedure 4GL Buffer SINGLE CHAR COLUMN RDBMS BUFFER & 4GL BUFFER

  20. RDBMS Stored ProceduresNew techniques Level of Integration Work CLIENT RDBMS OUTPUT FORMAT RDBMS ABL LOW HIGH PROC-TEXT-BUFFER SQL EXEC STORED PROCEDURE SQL EXEC STORED PROCEDURE LOW LOW ABL TEMPTABLE DataServer ABL 4GL MEDIUM HIGH PROC-TEXT-BUFFER USER DEFINED STORED PROCEDURE MEDIUM MEDIUM In/Out/Rtn PARAMS RDBMS BUFFER ABL BUFFER HIGH LOW

  21. Tomorrow’s Best PracticesNew for OpenEdge 10.1A • Automatic ABL TempTable population via Stored Procedures DEFINE VAR ttHandle AS HANDLE EXTENT 1. RUN STORED-PROC <RDBMS Stored Procedure> load-result-into ttHandle. • Accepts Results set(s) from any Stored Procedure • SQL and TempTable columns must match • Available in V9.1E & R10.0B (Technical Preview)

  22. Tomorrow’s Best PracticesStored Procedures & TempTables DEFINE VAR ttHandle AS HANDLE EXTENT 1 NO-UNDO. DEFINE TEMP-TABLE ttCust LIKE Sports.Customer FIELD tRecid AS INT FIELD tRECID_ident AS INT. ttHandle[1] = TEMP-TABLE ttCust:HANDLE. RUN STORED-PROC send-sql-statement load-result-into ttHandle ("select * from customer"). FOR EACH ttCust: DISPLAY name custnum address creditlimit. END.

  23. Tomorrow’s Best PracticesTempTable integration • Dynamic TempTable support DEFINE VARIABLE hBrowse AS HANDLE NO-UNDO. DEFINE VARIABLE ttHandle AS HANDLE NO-UNDO. CREATE TEMP-TABLE ttHandle. RUN STORED-PROC send-sql-statement load-result-into ttHandle ("select * from customer"). hBrowse = ttHandle:DEFAULT-BUFFER-HANDLE. CREATE QUERY q. q:SET-BUFFERS(hBrowse). q:QUERY-PREPARE("for each " + ttHandle:name). q:QUERY-OPEN.

  24. Tomorrow’s Best PracticesTempTable integration • Multiple Result Sets • SQL Server = Send-SQL-Statement STORED PROCEDURE • Oracle = User Defined STORED PROCEDURE CREATE TEMP-TABLE tt1. CREATE TEMP-TABLE tt2. DEF VAR ttHandle AS HANDLE EXTENT 2. ttHandle[1] = tt1. ttHandle[2] = tt2. RUN STORED-PROC send-sql-statement load-result-into ttHandle ("select * from customerselect * from order").

  25. Tomorrow’s Best PracticesTempTable integration (1 of 2) • ProDataSet - BEFORE-FILL Method DEFINE DATASET dsCustOrd FOR ttCust, ttOrder DATA-RELATION dsCustOrd FOR ttCust, ttOrder RELATION-FIELDS(custnum, custnum). DEFINE VAR phDataSet AS HANDLE NO-UNDO. phDataSet = DATASET dsCustOrd:HANDLE. phDataSet:SET-CALLBACK-PROCEDURE ("BEFORE-FILL", "preDataSetFill", THIS-PROCEDURE). (1) (2)

  26. Tomorrow’s Best PracticesTempTable integration (2 of 2) PROCEDURE PreDataSetFill: DEF VAR ttHandle AS HANDLE EXTENT 2. ttHandle[1] = TEMP-TABLE ttCust:HANDLE. ttHandle[2] = TEMP-TABLE ttOrder:HANDLE. IF DBTYPE(NUM-DBS) <> "PROGRESS" THEN DO: BUFFER ttCust:handle:FILL-MODE = "NO-FILL". RUN STORED-PROC send-sql-statement load-result-into ttHandle ("select * from customer select * from order"). END. ELSE DO: /* normal OpenEdge FILL procedure */ QUERY qCust:QUERY-PREPARE("for each customer"). END. END PROCEDURE. /* preDataSetFill */ (3) (4) (5) (6)

  27. Tomorrow’s Best PracticesTempTable integration 0x0039001600010000574845524520282850524f47524553535f5245434944203d2000000130004000400000000000000000000000129292000 • ROWID • Progress = 116 unique character value • Dependant on RECID & TableName DEFINE TEMP-TABLE ttCust LIKE Sports.Customer FIELD tRecid AS INT FIELD tRECID_ident AS INT. RUN STORED-PROC send-sql-statement load-result-into ttHandle ("select * from customer"). FOR EACH ttCust: DISPLAY tRecid. END. 1 2 3 4 5 6 ….

  28. Tomorrow’s Best PracticesTempTable integration DEFINE TEMP-TABLE ttCust LIKE Sports.Customer FIELD tRecid AS ROWID FIELD tRECID_ident AS INT. DEFINE VAR phDataSet AS HANDLE NO-UNDO. BUFFER ttCust:handle:ATTACH-DATA-SOURCE(DATA- SOURCE dsCust:HANDLE,?,?,?). RUN STORED-PROC send-sql-statement load-result-into ttHandle ("select * from customer"). FOR EACH ttCust: DISPLAY tRecid. END. • ROWID requires ProDataSet association 0x00390016000100923…… 0x00390016000100923…… 0x00390016000100923…… 0x00390016000100923…… 0x00390016000100923…… 0x00390016000100923…… 0x00390016000100923…… …

  29. ProDataSet Demonstration - - - - - - - - - ABL Business Logic Methods: Dataset:Before-fill Buffer:Before-fill Before-row-fill Row-Add Row-Delete …. ProDataSet • Theory into Practice …. • Performance optimized at a Data Access • Single ‘view’ into multiple Data Sources Oracle SQL Server OpenEdge Database

  30. Agenda Slide • Introduction • Today’s Best Practices • Tomorrow’s Best Practices • Summary • Questions and Answers

  31. In Summary • ‘Out of Box’ functionality and performance may be all you need • Improved Progress ABL and RDBMS Stored Procedure integration • No longer a ‘check box’ technology. Develop, deploy and integrate with confidence

  32. Questions?

More Related