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

About This Presentation
Title:

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

Description:

OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Brian Werne Sr. Engineering Manager OpenEdge SQL and OpenEdge Management Agenda: OpenEdge SQL component ... – PowerPoint PPT presentation

Number of Views:116
Avg rating:3.0/5.0
Slides: 47
Provided by: psdnComak
Category:
Tags: jdbc | odbc | ops | openedge | sql | adonet | beginners | guide | via

less

Transcript and Presenter's Notes

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


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

3
OpenEdge 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
4
Getting Connected Client side ODBC
and JDBC drivers
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
jdbcdatadirectopenedge//localhost6748database
Namedb1
6
ODBC DSN single connection
7
ODBC DSN Advanced Tab
8
Isolation Level Affect on Lock Type
9
ODBC Multi-DataBase configuration
10
Connection server side
11
Default server settings
SQL ABL Broker
ABL client
ABL client
12
Recommended server setup
SQL only Broker
ABL client
ABL client
ABL only Broker
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 -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
14
Security
Authorization
Authentication
What am I allowed to do?
  • Who am I?

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

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

17
SQL 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)

18
Comparing ABL SQL Security Systems
19
Encountering errors
Access denied (Authorization failed) (7512)
  • Possible reason for this
  • No authorization privileges
  • Schema scope

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

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
22
Encountering errors
Table/View/Synonym not found (7519)
  • Possible reasons for this
  • Not authorized
  • Schema scope

23
What is a Schema?
Mysports database
AuxCat Database
PUB schema
bwerne
PUB
Inventory
Customer table
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
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

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
27
OpenEdge Specifics
  • SQL is a standard,
  • but each vendor has its own dialect

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
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 its max length.
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
ltconnectgt (0single-user 1self-service
gt1threads)? 3 Padding above current
max 25 lttablegt (Table number or
all)? all ltareagt (Area number or
all)? all
31
OpenEdge 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.
32
OpenEdge 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
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
quarterlySales1, quarterlySales2, quarterlySal
es3, quarterlySales4 FROM PUB.MySales
SELECT FROM PUB.QuarterSales
34
Query Performance
  • Q Whats it gonna cost to run my query?

TIME
35
What is the cost?
Database without statistics
ABC Corp DB
Employees
36
Basic Performance - What is the cost?
Database with Update Statistics
ABC Corp DB
SalesHist
OrderLines
Customers
Distributors
Orders
Parts
Employees
Suppliers
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
38
Query trees Defined
  • relational algebraic tree representation
  • (query tree / execution tree )

Result set
Data
Database access
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_QplanWHERE "_Pnumber" (SELECT
MAX( "_Pnumber" ) FROM pub."_Sql_Qplan"
WHERE "_Ptype" gt 0 )
40
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
SCAN OF ( CustNum,
(PUB.CUSTOMER.CustNum)
between (1000,1100))
index
index keys, predicates
41
In Summary
  • Initial SQL connection
  • Setup and maintenance in OpenEdge database for
    security and performance
  • Specifics of OpenEdge with SQL applications

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

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

44
?
Questions
45
Thank You
46
(No Transcript)
Write a Comment
User Comments (0)
About PowerShow.com