Transcript Beginners Guide to OpenEdge SQL

B6: Beginners Guide to OpenEdge® SQL
via ODBC or JDBC
Richard Banville
Technical Fellow
Agenda:
Goal: Make you successful with SQL applications!
 OpenEdge SQL
• Component overview
• Initial connection
 OpenEdge database
• Setup and maintenance
– Control and performance
 OpenEdge SQL specifics
• Tools
• Applications
2
© 2008 Progress Software Corporation
OpenEdge is Open
ABL:
(ABL works with
relational DBs)
.NET
Java
HTML
Open Clients:
Java™
.NET™
Web services
Open Clients
(Non-OpenEdge)
SSL
HTTP
HTTP/S
HTML
OpenEdge
DataServers
OpenEdge
SQL
(works with
OpenEdge RDBMS)
OpenEdge
ABL Clients
Service
Interfaces
3
ODBC
Clients
OpenEdge
ABL Server
“Other”
RDBMS
Oracle®
MSSQL
ODBC
Crystal Reports
WebSphere®
JBOSS / JRun
Java / JDBC apps
J2EE™ / JTA
.NET / ODBC apps
ADO.NET / VB
JDBC
Clients
OpenEdge
SQL Server
OpenEdge
RDBMS
Data is fully interoperable: ABL & SQL
© 2008 Progress Software Corporation
Getting Connected – Client side:
ODBC and JDBC drivers
4
© 2008 Progress Software Corporation
JDBC: Pure Java Driver
Type 4 JDBC driver (10.1a+)
 CLASSPATH
( run ‘sql_env’)
$DLC/java: openedge.jar, util.jar, base.jar
 Class loader (Loading the JDBC driver)
CLASS.FORNAME com.ddtek.jdbc.openedge.OpenEdgeDriver
 URL for JDBC DriverManager.getConnection
jdbc:datadirect:openedge://localhost:6748;databaseName=db1
5
© 2008 Progress Software Corporation
ODBC DSN – single connection
6
© 2008 Progress Software Corporation
ODBC: Multi-Database configuration (10.1B)
7
© 2008 Progress Software Corporation
ODBC DSN - Advanced Tab
 Isolations: defaults have changed between versions
 Fetch Array Size: Max # rows driver fetches from server
 T w/TZ: determines native or varchar storage
 Wide char: varchar: SQL_WVARCHAR vs SQL_VARCHAR
8
© 2008 Progress Software Corporation
Isolation Level Affect on Lock Type
Update
Isolation
Level
Table
Lock
Record
Lock
Table
Lock
Record
Lock
---
---
NoLock
NoLock
IX
Exclusive
IS
Share
Repeatable
Read
IX
Exclusive
IS
Share
Serializable
SIX
Exclusive
Share
None
Read
Uncommitted
Read
Committed
9
Fetch
© 2008 Progress Software Corporation
Connection – server side
10
© 2008 Progress Software Corporation
Default server setup
SQL
Servers
SQL client
Shared Memory
SQL client
SQL & ABL
Broker
ABL client
ABL client
ABL
Servers
11
© 2008 Progress Software Corporation
DB
“Recommended” server setup
SQL client
SQL only
Broker
SQL
Servers
Shared Memory
SQL client
ABL client
ABL client
12
ABL only
Broker
ABL
Servers
© 2008 Progress Software Corporation
DB
Recommended parameters example
Separating ABL and SQL brokers/servers example
Start a primary ABL broker
Start a secondary SQL broker
proserve myDB
-S 6000 -H localhost
-ServerType 4GL
-Mi 1 -Ma 5
-minport 6100
-maxport 6300
-Mpb 4
-Mn 8 -B<n> -L<n> . . .
proserve myDB
-S 5000 -H localhost
-ServerType SQL
-Mi 5 -Ma 10
-minport 5100
-maxport 5300
-Mpb 2 –m3
Secondary broker uses 1 –Mn for itself
13
© 2008 Progress Software Corporation
Security
Authenticate then authorize
Authorization
Authentication
•
•
14
Identify who I am
Validate I am who
I say I am
© 2008 Progress Software Corporation
•
•
Being I am who I
say I am…
What am I allowed
to do
Security
ID and passwords
 Database authentication
• Performed at connection
• SQL
– User ID and password required to authenticate
• ABL
– User ID and password NOT required
15
© 2008 Progress Software Corporation
SQL Authentication (Am I who I say I am?)
ID and passwords scenarios
Case 1: Users have not been created
(no rows in _User table)
• No password validation at connection
– You are not “authenticated”
– You can pretend to be someone you are not
• Unable to do much (not authorized) UNLESS
– You created the database
– You know an authorized user
 Database creator
 Someone who was granted privileges
16
© 2008 Progress Software Corporation
SQL Authentication (Am I who I say I am?)
ID and passwords scenarios…cont’d
Case 2: Users have been created
(rows exist in _User table)
• Password validation at connection
• Valid users defined by a DBA or SA
– Can be created by SQL or ABL
– Make sure existing SQL DBA has userid/pswd
• Invalid login/password error message:
– “Access denied (Authorisation failed). (8933)
17
© 2008 Progress Software Corporation
Authorization – What am I allowed to do?
 SQL follows GRANT security model
• By default, a connected userid is not
authorized to do anything.
• Exceptions:
– the DBA account (full operations)
– the TABLE owner
 DBA controls operation privileges
with GRANT / REVOKE syntax
18
© 2008 Progress Software Corporation
Encountering data access errors
select count(*) from customer;
Access denied (Authorization failed) (7512)

Possible reasons for this:
• No authorization privileges
• Schema scope
20
© 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 ] , …;
Can do anything
21
Can CREATE stuff
© 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 , column , ... ) ] |
REFERENCES [ ( column , column , ... ) ] }
22
© 2008 Progress Software Corporation
Authorization – What can I do?
Example Syntax: GRANT
 For specified Tables or Views - Example
GRANT select ON PUB.Customer
TO richb;
GRANT select ON PUB.Order-line
TO PUBLIC;
COMMIT WORK;
ROLLBACK WORK;
 See PSDN whitepaper on authorization for
additional details.
23
© 2008 Progress Software Corporation
Encountering data access errors
select count(*) from customer;
Access denied (Authorization failed) (7512)
 Possible reasons for this:
• No authorization privileges
• Schema
Schema scope
scope
24
© 2008 Progress Software Corporation
Schema: a logical grouping
In the “SQL world” schema is NOT meta data nor is it “Area 6”
Mysports
database
25
richb schema
PUB schema
Customer table #1
Customer table #4
Order table #2
Contacts table #5
Order-line table #3
Friends table #6
© 2008 Progress Software Corporation
Schemas
What is a default schema?

Users have a default schema attached to their ID
<userid>.<table>
richb.customer

ABL uses one “hidden” schema – ‘PUB’
• Use PUB.customer for access from SQL

Avoiding schema qualification in SQL:
CREATE PUBLIC SYNONYM customer FOR
pub.customer;

26
Can set it as a registry entry in ODBC dsn definition
© 2008 Progress Software Corporation
Solving data access errors
select count(*) from customer;
Access denied (Authorization failed) (7512)
Solution #1:
SELECT count(*) FROM richb.customer;
Solution #2:
SELECT count(*) FROM pub.customer;
Solution #3:
SET SCHEMA ‘pub’;
SELECT count(*) FROM customer;
27
© 2008 Progress Software Corporation
4 Part Naming – Multi-Database Query
Fully Qualified Names – catalog is database name
 Four level naming convention
catalog.schema.table.column-name
28
© 2008 Progress Software Corporation
4 Part Naming – Multi-Database Query
Fully Qualified Names – catalog is database name
 Four level naming convention
catalog.schema.table.column-name
 Example
SELECT Pub.Customer.CustNum,
SportsPrimary.Pub.Customer.Name,
SportsAux1.Pub.Order.OrderNum …
29
© 2008 Progress Software Corporation
4 Part Naming – Multi-Database Query
Fully Qualified Names – catalog is database name
 Four level naming convention
catalog.schema.table.column-name
 Example
SELECT Pub.Customer.CustNum,
SportsPrimary.Pub.Customer.Name,
SportsAux1.Pub.Order.OrderNum …
 ABL has 3 level naming convention
catalog.table.column-name
30
© 2008 Progress Software Corporation
OpenEdge Specifics
SQL is a standard,
but each vendor has it’s own dialect
31
© 2008 Progress Software Corporation
OpenEdge SQL Specifics - Quoting
Non-SQL standard names
 Hyphenated names:
SELECT cust-num FROM PUB.Customer;
Column CUST cannot be found (13865)
 Solution:
quoting (double quotes)
SELECT “cust-num” FROM PUB.Customer;
Most reporting applications will do this automatically.
32
© 2008 Progress Software Corporation
Overstuffed fields - error
 ABL allows more data than column definition
 SQL restricted to _field._sql-width value
SELECT name from PUB.customer;
Column name in table PUB.customer has value
exceeding it’s max length.
 Solution:
Fix _sql-width via SQL “ALTER TABLE or Data Dictionary
ALTER table… ALTER column… SET PRO_SQL_WIDTH <value>;
33
© 2008 Progress Software Corporation
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
34
<table>:
(Table number or all)? all
<area>:
(Area number or all)? all
© 2008 Progress Software Corporation
OpenEdge Specifics - Overstuffed fields
Strategies for managing
 ABL client startup parameter
<progress-client> –checkwidth n
where "n" can be one of the following:
0 — Ignore: Default is to ignore _width value.
1 — WARNING: Store the data and generate a warning.
2 — ERROR: Do not store data and generate an error.
.lg and screen: Width of data is greater than x.customer.Name _width.
35
© 2008 Progress Software Corporation
OpenEdge Specifics – Arrays / Extents

Selecting array columns as a whole
SELECT quarterlySales from PUB.MySales;
Result: semi-colon separated varchar value
102332.67;330002.77;443434.55;333376.50

Selecting array column individually – SQL99 (10.1a)
SELECT quarterlySales[1] from PUB.MySales;
Result: numeric value
102332.67
36
© 2008 Progress Software Corporation
OpenEdge Specifics – Arrays / Extents
Using views to break out array element
CREATE VIEW pubView.QuarterSalesView
(qS1, qS2, qS3, qS4) AS
SELECT quarterlySales[1], quarterlySales[2],
quarterlySales[3], quarterlySales[4]
FROM PUB.MySales;
GRANT select ON pubView.QuarterSalevVIew
TO PUBLIC;
SELECT qS1, qS2, qS3, qS4
FROM pubView.QuarterSalesView;
Result: numeric values
102332.67 330002.77 443434.55 333376.50
37
© 2008 Progress Software Corporation
Query Performance
 Q: What is it going to cost to run
my query?
TIME =
38
© 2008 Progress Software Corporation
?
Query Performance
 Q: What is it going to cost to run
my query?
TIME =
39
© 2008 Progress Software Corporation
Basic Performance: What is the cost?
Database without statistics
100K
Optimizer:
How many rows do I think you have?
50K
10K
rows
40
© 2008 Progress Software Corporation
Basic Performance: Here’s the cost.
Database with Update Statistics
100K
50K
10K
rows
41
© 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;
42
© 2008 Progress Software Corporation
Basic Performance: Viewing Query Plans
Viewing query plan constructed by cost-based optimizer
SET PRO_SERVER LOG [ ON | OFF ]
[ WITH ( { STATEMENT, QUERY_PLAN } ) ];
 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 );
Remembers last 10 queries for a client
44
© 2008 Progress Software Corporation
Query plan: What to look for

Simple single table select
SELECT … FROM pub.customer
WHERE “cust-num” between 1000 and 1100 [ NoExecute ]
table
index
index keys,
predicates
45
SELECT COMMAND.
PROJECT [66] (
|
PROJECT [64] (
|
|
PUB.CUSTOMER. [0](
|
|
|
INDEX SCAN OF (
|
|
|
|
cust-num,
|
|
|
|
|
(PUB.CUSTOMER.cust-num)
between (1000,1100))
© 2008 Progress Software Corporation
Query plan: What to look for

Simple single table select with index hint
SELECT … FROM pub.customer WITH (INDEX (name))
WHERE ”cust-num” between 3000 and 3100 [ NoExecute ]
table
index
index keys,
predicates
46
SELECT COMMAND.
PROJECT [66] (
|
PROJECT [64] (
|
|
PUB.CUSTOMER. [0](
|
|
|
INDEX SCAN OF (
|
|
|
|
cust-num,
|
|
|
|
|
(PUB.CUSTOMER.cust-num)
between (3000,3100))
© 2008 Progress Software Corporation
In Summary
 Initial SQL connection
 Setup and maintenance in
OpenEdge database for
security and performance
 Specifics of OpenEdge with
SQL applications
47
© 2008 Progress Software Corporation
For More Information, go to…
48

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
© 2008 Progress Software Corporation
?
Questions
49
© 2008 Progress Software Corporation
Some Questions Answered



Is there a way to grant a user select privilege for all
tables in one statement?
• There is no way to grant privileges for all tables in one
statement. Other than grant dba ;)
Does the field level extent (array fields) reporting
where members are separated by ";" work for ALL
data type?
• yes
If so, then how is a ";" handled within a character
field? Is it quoted when encountered to differentiate
between an array entry separator?
• An embedded semi-colon in a varchar array will be
preceded by a ~
50
© 2008 Progress Software Corporation
Thank You
51
© 2008 Progress Software Corporation
52
© 2008 Progress Software Corporation