1 / 46

OPS-6: Beginners Guide to OpenEdge ® SQL via ODBC or JDBC

OPS-6: Beginners Guide to OpenEdge ® SQL via ODBC or JDBC. Brian Werne. Sr. Engineering Manager OpenEdge SQL and OpenEdge Management. Agenda:. Goal: Make you successful with SQL applications!. OpenEdge SQL component overview and your initial connection

vala
Download Presentation

OPS-6: Beginners Guide to OpenEdge ® SQL via ODBC or JDBC

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. OPS-6: Beginners Guide to OpenEdge® SQL via ODBC or JDBC Brian Werne Sr. Engineering Manager OpenEdge SQL and OpenEdge Management

  2. Agenda: Goal: Make you successful with SQL applications! • OpenEdge SQL component overview and your initial connection • Setup and maintenance of the OpenEdge database for control and performance • Specifics of the OpenEdge with the SQL based tools and applications OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  3. OpenEdgeABL Server OpenEdgeSQL Server OpenEdgeRDBMS OpenEdgeDataServers OpenEdge is Open Crystal Reports WebSphere® JBOSS / JRun Java / JDBC apps J2EE™ / JTA .NET / ODBC apps ADO.NET / VB ABL: OpenEdge SQL (ABL works with relational DBs) Open Clients: Java™ .NET™ Web services (works with OpenEdge RDBMS) .NETJavaHTML ODBC Clients JDBC Clients ServiceInterfaces OpenEdge ABL Clients Open Clients(Non-OpenEdge) SSL HTTP HTTP/S HTML Oracle®MSSQLODBC Data is fully interoperable: ABL & SQL

  4. Getting Connected – Client side: ODBC and JDBC drivers OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  5. JDBC driver Type 4 JDBC driver (10.1a+) • CLASSPATH ( run ‘sql_env’) • Class loader • URL • $DLC/java: openedge.jar, util.jar, base.jar • com.ddtek.jdbc.openedge.OpenEdgeDriver jdbc:datadirect:openedge://localhost:6748;databaseName=db1 OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  6. ODBC DSN – single connection OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  7. ODBC DSN Advanced Tab OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  8. Isolation Level Affect on Lock Type OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  9. ODBC : Multi-DataBase configuration OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  10. Connection – server side OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  11. SQL Servers Shared Memory SQL client SQL client Database ABL Servers Default server settings SQL & ABL Broker ABL client ABL client OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  12. SQL Servers Shared Memory SQL client SQL client Database ABL Servers “Recommended” server setup SQL only Broker ABL client ABL client ABL only Broker OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  13. Recommended parameters example Separating ABL and SQL brokers/servers … examples • Example: Start a ABL Primary broker • Example: Start a Secondary SQL broker proserve Sports2000 -S 6000 -H localhost -n 45 -Mn 8 -Mpb 4 -ServerType4GL -Mi 1 -Ma 5 -minport 6100 -maxport 6300 proserve Sports2000 -S 5000 -H localhost -m3 –Mpb 3 -ServerType SQL –Mi 5 –Ma 5 -minport 5100 -maxport 5300 OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  14. Security Who am I? Authorization Authentication What am I allowed to do? OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  15. Security Considerations ID and passwords Database authentication • SQL • Always requires a user ID and password to establish a connection • ABL • Does not specifically require a user ID and password to establish a connection OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  16. SQL Authentication (Who am I?) ID and passwords scenarios • Case 1: Users have not been created (no rows in _User table) • Password validation is not enabled • No check is performed at connection time • No error message at connection time OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  17. SQL Authentication (Who am I?) ID and passwords scenarios…cont’d • Case 2: Users exist in the OpenEdge RDBMS (rows exist in _User table) • Password Validation is enabled • Check is performed at connection time • Valid users defined by a DBA • Error message if login is incorrect / invalid: • “Access Denied (8933)” OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  18. Comparing ABL & SQL Security Systems OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  19. Encountering errors • Possible reason for this: • No authorization privileges • Schema scope Access denied (Authorization failed) (7512) OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  20. Authorization – What can I do? • SQL follows GRANT security model By default, a connected userid is not allowed to do anything. Exceptions: - the DBA account (full operations) - the TABLE owner • DBA controls operation privileges with GRANT / REVOKE syntax OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  21. Authorization – What can I do? Privileges – Syntax: GRANT (2 types) • Database wide (system admin or general creation) • For specified Tables or Views • Where ‘privilege’ is: { SELECT | INSERT | DELETE | INDEX | UPDATE [ ( column , column , ... ) ] | REFERENCES [ ( column , column , ... ) ] } GRANT { DBA, RESOURCE } TO user_name [, user_name ] , …; GRANT { privilege [, privilege ], … | ALL } ON table_name TO { user_name [, user_name ] , … | PUBLIC } [ WITH GRANT OPTION ]; OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  22. Encountering errors • Possible reasons for this: • Not authorized • Schema scope Table/View/Synonym not found (7519) OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  23. What is a Schema? Mysports database AuxCat Database PUB schema bwerne PUB Inventory Customer table OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  24. Schemas • What is a default Schema? • A user has by default a schema attached to their ID SET SCHEMA { 'string_literal'} • OpenEdge ABL uses one schema – ‘PUB’ • Another option: Synonyms: • SET SCHEMA ‘pub’ • CREATE PUBLIC SYNONYM customer FOR pub.customer; OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  25. Schema example • SELECT count(*) FROM customer; • SELECT count(*) FROM customer; • Solutions: or Table/View/Synonym not found (7519) • SELECT count(*) FROM pub.customer; • SET SCHEMA ‘pub’; • SELECT count(*) FROM customer; OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  26. 4 Part Naming – Multi-Database Query Fully Qualified Names • Four level naming convention • Example • ABL has 3 level naming convention catalog.schema.table.column-name SELECT Pub.Customer.CustNum, SportsPrimary.Pub.Customer.Name, SportsAux1.Pub.Order.OrderNum … catalog.table.column-name OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  27. OpenEdge Specifics SQL is a standard, but each vendor has it’s own dialect OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  28. OpenEdge Specifics - Quoting Non-SQLStandard names • Hyphenated names: • Solution: quoting • SELECT cust-num FROM PUB.Customer; Column CUST cannot be found (13865) • SELECT “cust-num” FROM PUB.Customer; OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  29. Overstuffed fields - error • ABL allows more data than column definition • SELECT abc from PUB.Ranking; Column abc in table PUB.Ranking has value exceeding it’s max length. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  30. OpenEdge Specifics - Overstuffed fields Strategies for managing: • Dbtool : percentage option ($DLC/bin/dbtool) 1. SQL Width & Date Scan w/Report Option 2. SQL Width Scan w/Fix Option Choice: 2 <connect>: (0=single-user 1=self-service >1=#threads)? 3 Padding % above current max: 25 <table>: (Table number or all)? all <area>: (Area number or all)? all OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  31. OpenEdge Specifics - Overstuffed fields Strategies for managing • ABL client startup parameter -checkwidth <progress-client>.exe –checkwidth nwhere "n" can be one of the following: 0 — Ignore _width value. Default. 1 — Store the data and generate a warning. 2 — Do not store data and generate an error. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  32. OpenEdge Specifics – Arrays / Extents • Selecting array columns as a whole Result: semi-colon separated varchar value 102332.67;330002.77;443434.55;333376.50 • Selecting array column individually – SQL99 Result: numeric value 102332.67 • SELECT quarterlySales from PUB.MySales; • SELECT quarterlySales[1] from PUB.MySales; OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  33. OpenEdge Specifics – Arrays / Extents Strategies: • Using views to break out array elements Result: numeric values 102332.67 330002.77 443434.55 333376.50 • CREATE VIEW PUB.QuarterSales AS SELECT quarterlySales[1], quarterlySales[2], • quarterlySales[3], quarterlySales[4] FROM PUB.MySales; • SELECT * FROM PUB.QuarterSales; OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  34. Query Performance • Q: What’s it gonna cost to run my query? TIME = OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  35. Customers Orders Distributors OrderLines Parts SalesHist Suppliers What is the cost? Database without statistics ABC Corp DB Employees OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  36. Basic Performance - What is the cost? Database with Update Statistics ABC Corp DB SalesHist OrderLines Customers Distributors Orders Parts Employees Suppliers OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  37. Query Performance: Update Statistics UPDATE STATISTICS syntax • All Statistics: Table Cardinality, indexes and all columns • Statistics - particular table • UPDATE TABLE STATISTICS AND INDEX STATISTICS AND [ALL] COLUMN STATISTICS; • UPDATE TABLE STATISTICS AND INDEX STATISTICS AND [ALL] COLUMN STATISTICS • FOR pub.customer; OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  38. Query trees : Defined • relational algebraic tree representation (query tree / execution tree ) Result set Data Database access OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  39. Basic Performance – Query Plans Viewing query plan constructed by cost-based optimizer • Query Plans Located in VST _SQL_QPLAN SELECT SUBSTRING("_Description",1,80) FROM pub."_Sql_Qplan“WHERE "_Pnumber" = (SELECT MAX( "_Pnumber" ) FROM pub."_Sql_Qplan" WHERE "_Ptype" > 0 ); OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  40. table Query plan – what to look for • Simple single table select • “select … from pub.customer where custnum between 1000 and 1100 [NoExecute]” SELECT COMMAND. PROJECT [66] ( | PROJECT [64] ( | | PUB.CUSTOMER. [0]( | | | INDEX SCANOF ( | | | | CustNum, | | | | | (PUB.CUSTOMER.CustNum) between (1000,1100)) index index keys, predicates OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  41. In Summary • Initial SQL connection • Setup and maintenance in OpenEdge database for security and performance • Specifics of OpenEdge with SQL applications OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  42. For More Information, go to… • PSDN • Developing Performance-Oriented ODBC/JDBC OpenEdge Applications • OpenEdge SQL: Authorization Explained • OpenEdge SQL in a 10.1B Multi-Database Environment • OpenEdge® Database Run-time Security Revealed • OpenEdge Technical Support - KBases • Basic Guide to Defining Progress SQL Database Permissions & Security • Progress eLearning Community • Using OpenEdge SQL • Documentation • 10.1C OpenEdge Data Management: SQL Development   • 10.1C OpenEdge Data Management: SQL Reference OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  43. Relevant Exchange Sessions • OPS-27: Understanding Record and Table Locking In OpenEdge SQL • OPS-10: Moving V8/V9 RDBMS to OpenEdge 10 • OPS-15: What was Happening with My Database, AppServer, Operating System • OPS-18: Data Management and Platforms Roadmap • OPS-24: Success with OpenEdge Replication OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  44. ? Questions OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  45. Thank You OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

  46. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

More Related