Title: OPS-6: Beginners Guide to OpenEdge® SQL via ODBC or JDBC
1OPS-6 Beginners Guide to OpenEdge SQL via
ODBC or JDBC
Brian Werne
Sr. Engineering Manager OpenEdge SQL and
OpenEdge Management
2Agenda
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
3OpenEdge is Open
Crystal Reports WebSphere JBOSS / JRun Java /
JDBC apps J2EE / JTA .NET / ODBC apps ADO.NET /
VB
ABL
OpenEdge SQL
Open Clients Java .NET Web services
(ABL works with relational DBs)
(works with OpenEdge RDBMS)
.NETJavaHTML
ODBC Clients
JDBC Clients
OpenEdge ABL Clients
ServiceInterfaces
Open Clients(Non-OpenEdge)
SSL HTTP HTTP/S HTML
OracleMSSQLODBC
Data is fully interoperable ABL SQL
4Getting Connected Client side ODBC
and JDBC drivers
5JDBC 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
jdbcdatadirectopenedge//localhost6748database
Namedb1
6ODBC DSN single connection
7ODBC DSN Advanced Tab
8Isolation Level Affect on Lock Type
9ODBC Multi-DataBase configuration
10Connection server side
11Default server settings
SQL ABL Broker
ABL client
ABL client
12Recommended server setup
SQL only Broker
ABL client
ABL client
ABL only Broker
13Recommended 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 -ServerType 4GL -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
14Security
Authorization
Authentication
What am I allowed to do?
15Security 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
16SQL 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
17SQL Authentication (Who am I?)
ID and passwords scenarioscontd
- 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)
18Comparing ABL SQL Security Systems
19Encountering errors
Access denied (Authorization failed) (7512)
- Possible reason for this
- No authorization privileges
- Schema scope
20Authorization 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
21Authorization 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
22Encountering errors
Table/View/Synonym not found (7519)
- Possible reasons for this
- Not authorized
- Schema scope
23What is a Schema?
Mysports database
AuxCat Database
PUB schema
bwerne
PUB
Inventory
Customer table
24Schemas
- 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
25Schema example
SELECT count() FROM customer
SELECT count() FROM customer
Table/View/Synonym not found (7519)
SELECT count() FROM pub.customer
SET SCHEMA pub SELECT count() FROM customer
264 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
27OpenEdge Specifics
- SQL is a standard,
- but each vendor has its own dialect
28OpenEdge 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
29Overstuffed fields - error
- ABL allows more data than column definition
-
SELECT abc from PUB.Ranking
Column abc in table PUB.Ranking has value
exceeding its max length.
30OpenEdge 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
ltconnectgt (0single-user 1self-service
gt1threads)? 3 Padding above current
max 25 lttablegt (Table number or
all)? all ltareagt (Area number or
all)? all
31OpenEdge Specifics - Overstuffed fields
- Strategies for managing
- ABL client startup parameter -checkwidth
ltprogress-clientgt.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.
32OpenEdge Specifics Arrays / Extents
- Selecting array columns as a whole
- Result semi-colon separated varchar value
- 102332.67330002.77443434.55333376.50
- Selecting array column individually SQL99
- Result numeric value
- 102332.67
SELECT quarterlySales from PUB.MySales
SELECT quarterlySales1 from PUB.MySales
33OpenEdge 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
quarterlySales1, quarterlySales2, quarterlySal
es3, quarterlySales4 FROM PUB.MySales
SELECT FROM PUB.QuarterSales
34Query Performance
- Q Whats it gonna cost to run my query?
TIME
35What is the cost?
Database without statistics
ABC Corp DB
Employees
36Basic Performance - What is the cost?
Database with Update Statistics
ABC Corp DB
SalesHist
OrderLines
Customers
Distributors
Orders
Parts
Employees
Suppliers
37Query 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
38Query trees Defined
- relational algebraic tree representation
- (query tree / execution tree )
Result set
Data
Database access
39Basic 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_QplanWHERE "_Pnumber" (SELECT
MAX( "_Pnumber" ) FROM pub."_Sql_Qplan"
WHERE "_Ptype" gt 0 )
40Query 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
SCAN OF ( CustNum,
(PUB.CUSTOMER.CustNum)
between (1000,1100))
index
index keys, predicates
41In Summary
- Initial SQL connection
- Setup and maintenance in OpenEdge database for
security and performance - Specifics of OpenEdge with SQL applications
42For 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
43Relevant 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
44?
Questions
45Thank You
46(No Transcript)