OPS6 Beginners Guide to Open Edge SQL via

OPS-6: Beginners Guide to Open. Edge® SQL via ODBC or JDBC Brian Werne Sr. Engineering Manager Open. Edge SQL and Open. Edge Management

Agenda: Goal: Make you successful with SQL applications! § Open. Edge SQL component overview and your initial connection § Setup and maintenance of the Open. Edge database for control and performance § Specifics of the Open. Edge with the SQL based tools and applications 2 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Open. Edge is Open ABL: (ABL works with relational DBs) . NET Java HTML Open Clients: Java™. NET™ Web services Open Clients (Non-Open. Edge) SSL HTTP/S HTML Oracle® MSSQL ODBC 3 Open. Edge SQL (works with Open. Edge RDBMS) Open. Edge ABL Clients Open. Edge ABL Server Open. Edge Data. Servers Crystal Reports Web. Sphere® JBOSS / JRun Java / JDBC apps J 2 EE™ / JTA. NET / ODBC apps ADO. NET / VB ODBC Clients Service Interfaces JDBC Clients Open. Edge SQL Server Open. Edge RDBMS Data is fully interoperable: ABL & SQL © 2008 Progress Software Corporation

Getting Connected – Client side: ODBC and JDBC drivers 4 OPS-6: Beginners Guide to

JDBC driver Type 4 JDBC driver (10. 1 a+) § CLASSPATH ( run ‘sql_env’) $DLC/java: openedge. jar, util. jar, base. jar § Class loader com. ddtek. jdbc. openedge. Open. Edge. Driver § URL jdbc: datadirect: openedge: //localhost: 6748; database. Name=db 1 5 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

ODBC DSN – single connection 6 OPS-6: Beginners Guide to Open. Edge SQL via

ODBC DSN Advanced Tab 7 OPS-6: Beginners Guide to Open. Edge SQL via ODBC

Isolation Level Affect on Lock Type Isolation Level Fetch Table Lock Record Lock --- No. Lock IX Exclusive IS Share Repeatable Read IX Exclusive IS Share Serializable SIX Exclusive Share None Read Uncommitted Read Committed 8 Update OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

ODBC : Multi-Data. Base configuration 9 OPS-6: Beginners Guide to Open. Edge SQL via

Connection – server side 10 OPS-6: Beginners Guide to Open. Edge SQL via ODBC

Default server settings SQL Servers SQL client Shared Memory SQL client SQL & ABL Broker ABL client 11 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC Database ABL Servers © 2008 Progress Software Corporation

“Recommended” server setup SQL client SQL only Broker SQL Servers Shared Memory SQL client ABL client 12 ABL only Broker OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC Database ABL Servers © 2008 Progress Software Corporation

Recommended parameters example Separating ABL and SQL brokers/servers … examples § Example: Start a ABL Primary broker proserve Sports 2000 -S 6000 -H localhost -n 45 -Mn 8 -Mpb 4 -Server. Type 4 GL -Mi 1 -Ma 5 -minport 6100 -maxport 6300 § Example: Start a Secondary SQL broker proserve Sports 2000 -S 5000 -H localhost -m 3 –Mpb 3 -Server. Type SQL –Mi 5 –Ma 5 -minport 5100 -maxport 5300 13 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Security Authentication Who am I? 14 OPS-6: Beginners Guide to Open. Edge SQL via

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 15 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

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 16 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

SQL Authentication (Who am I? ) ID and passwords scenarios…cont’d § Case 2: Users exist in the Open. Edge 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)” 17 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Comparing ABL & SQL Security Systems ABL 18 SQL Security model GRANT Default DBA n/a <db-owner> SYSPROGRESS Default security administrator “*” n/a Default table access “*” <none> Default field access “*” <none> OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Encountering errors Access denied (Authorization failed) (7512) § Possible reason for this: • No authorization privileges • Schema scope 19 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

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 20 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

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

Encountering errors Table/View/Synonym not found (7519) § Possible reasons for this: • Not authorized • Schema scope 22 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

What is a Schema? Mysports database PUB schema Customer table 23 OPS-6: Beginners Guide

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

Schema example SELECT count(*) FROM customer; Table/View/Synonym not found (7519) § Solutions: SELECT count(*) FROM pub. customer; or SET SCHEMA ‘pub’; SELECT count(*) FROM customer; 25 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

4 Part Naming – Multi-Database Query Fully Qualified Names § Four level naming convention catalog. schema. table. column-name § Example SELECT Pub. Customer. Cust. Num, Sports. Primary. Pub. Customer. Name, Sports. Aux 1. Pub. Order. Num … § ABL has 3 level naming convention catalog. table. column-name 26 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Open. Edge Specifics SQL is a standard, but each vendor has it’s own dialect

Open. Edge Specifics - Quoting Non-SQLStandard names § Hyphenated names: SELECT cust-num FROM PUB. Customer; Column CUST cannot be found (13865) § Solution: quoting SELECT “cust-num” FROM PUB. Customer; 28 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

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. 29 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Open. Edge 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=selfservice >1=#threads)? 3 Padding % above current max: 25 30 <table>: (Table number or all)? all <area>: (Area number or all)? all OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Open. Edge Specifics - Overstuffed fields Strategies for managing § ABL client startup parameter -checkwidth <progress-client>. exe –checkwidth n where "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. 31 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Open. Edge Specifics – Arrays / Extents § Selecting array columns as a whole SELECT quarterly. Sales from PUB. My. Sales; Result: semi-colon separated varchar value 102332. 67; 330002. 77; 443434. 55; 333376. 50 § Selecting array column individually – SQL 99 SELECT quarterly. Sales[1] from PUB. My. Sales; Result: numeric value 102332. 67 32 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Open. Edge Specifics – Arrays / Extents Strategies: § Using views to break out array elements CREATE VIEW PUB. Quarter. Sales AS SELECT quarterly. Sales[1], quarterly. Sales[2], quarterly. Sales[3], quarterly. Sales[4] FROM PUB. My. Sales; SELECT * FROM PUB. Quarter. Sales; Result: numeric values 102332. 67 330002. 77 443434. 55 333376. 50 33 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Query Performance § Q: What’s it gonna cost to run my query? TIME =

What is the cost? Database without statistics ABC Corp DB Customers Distributors Parts Suppliers 35 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC Orders Order. Lines Sales. Hist Employees © 2008 Progress Software Corporation

Basic Performance - What is the cost? Database with Update Statistics ABC Corp DB Customers Sales. Hist Order. Lines Distributors Orders Parts Employees Suppliers 36 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

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

Query trees : Defined § relational algebraic tree representation (query tree / execution tree ) Result set Database access 38 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

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 ); 39 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

Query plan – what to look for § Simple single table select • “select … from pub. customer where custnum between 1000 and 1100 [No. Execute]” table index keys, predicates 40 SELECT COMMAND. PROJECT [66] ( | PROJECT [64] ( | | PUB. CUSTOMER. [0]( | | | INDEX SCAN OF ( | | Cust. Num, | | | (PUB. CUSTOMER. Cust. Num) between (1000, 1100)) OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

In Summary § Initial SQL connection § Setup and maintenance in Open. Edge database for security and performance § Specifics of Open. Edge with SQL applications 41 OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation

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

Relevant Exchange Sessions § § § 43 OPS-27: Understanding Record and Table Locking In Open. Edge SQL OPS-10: Moving V 8/V 9 RDBMS to Open. Edge 10 OPS-15: What was Happening with My Database, App. Server, Operating System OPS-18: Data Management and Platforms Roadmap OPS-24: Success with Open. Edge Replication OPS-6: Beginners Guide to Open. Edge SQL via ODBC or JDBC © 2008 Progress Software Corporation