b6: beginners guide to openedge ® sql via odbc or jdbc richard banville technical fellow
TRANSCRIPT
B6: Beginners Guide to OpenEdge® SQL via ODBC or JDBC
Richard BanvilleTechnical Fellow
© 2008 Progress Software Corporation2
Agenda:
OpenEdge SQL• Component overview
• Initial connection
OpenEdge database• Setup and maintenance
– Control and performance
OpenEdge SQL specifics• Tools
• Applications
Goal: Make you successful with SQL applications!
© 2008 Progress Software Corporation3
OpenEdge is Open
SSL HTTP
HTTP/S HTML
.NETJava
HTML Open Clients
(Non-OpenEdge)OpenEdge
ABL ClientsODBCClients
ServiceInterfaces
OpenEdgeDataServer
s
OpenEdgeABL Server
OpenEdgeSQL Server
JDBCClients
Oracle®
MSSQLODBC
ABL: Open Clients:Java™
.NET™
Web services
(ABL works with relational DBs)
OpenEdge SQL
Crystal ReportsWebSphere®
JBOSS / JRunJava / JDBC appsJ2EE™ / JTA.NET / ODBC appsADO.NET / VB
Data is fully interoperable: ABL & SQL
(works with OpenEdge RDBMS)
OpenEdgeRDBMS
“Other”RDBMS
© 2008 Progress Software Corporation4
Getting Connected – Client side: ODBC and JDBC drivers
© 2008 Progress Software Corporation5
CLASSPATH ( run ‘sql_env’)
Class loader (Loading the JDBC driver)
URL for JDBC DriverManager.getConnection
JDBC: Pure Java Driver
Type 4 JDBC driver (10.1a+)
jdbc:datadirect:openedge://localhost:6748;databaseName=db1
$DLC/java: openedge.jar, util.jar, base.jar
CLASS.FORNAME com.ddtek.jdbc.openedge.OpenEdgeDriver
© 2008 Progress Software Corporation6
ODBC DSN – single connection
© 2008 Progress Software Corporation7
ODBC: Multi-Database configuration (10.1B)
© 2008 Progress Software Corporation8
ODBC DSN - Advanced Tab
Isolations: defaults have changed between versions
T w/TZ: determines native or varchar storage
Wide char: varchar: SQL_WVARCHAR vs SQL_VARCHAR
Fetch Array Size: Max # rows driver fetches from server
© 2008 Progress Software Corporation9
NoneShareExclusiveSIXSerializable
ShareISExclusiveIXRepeatable
Read
ShareISExclusiveIX
Read Committed
NoLockNoLock------Read
Uncommitted
Record Lock
Table Lock
Record Lock
Table Lock
Isolation Level
FetchUpdate
Isolation Level Affect on Lock Type
© 2008 Progress Software Corporation10
Connection – server side
© 2008 Progress Software Corporation11
Default server setup
SQL Servers
SQL clientSQL client
ABL clientABL client
SQL clientSQL client
ABL clientABL client
Sh
ared M
emo
ry
ABL Servers
DBSQL & ABL
Broker
© 2008 Progress Software Corporation12
“Recommended” server setup
SQL Servers
SQL clientSQL client
ABL clientABL client ABL onlyBroker
SQL onlyBroker
SQL clientSQL client
ABL clientABL client
Sh
ared M
emo
ry
ABL Servers
DB
© 2008 Progress Software Corporation13
Recommended parameters example
Separating ABL and SQL brokers/servers example
proserve myDB -S 6000 -H localhost -ServerType 4GL -Mi 1 -Ma 5 -minport 6100 -maxport 6300 -Mpb 4 -Mn 8 -B<n> -L<n> . . .
Start a primary ABL broker Start a secondary SQL broker
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
© 2008 Progress Software Corporation14
Security
Authentication
Authenticate then authorize
• Identify who I am
• Validate I am who I say I am
Authorization
• Being I am who I say I am…
• What am I allowed to do
© 2008 Progress Software Corporation15
Database authentication• Performed at connection
• SQL– User ID and password required to authenticate
• ABL– User ID and password NOT required
Security
ID and passwords
© 2008 Progress Software Corporation16
SQL Authentication (Am I who I say I am?)
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
ID and passwords scenarios
© 2008 Progress Software Corporation17
SQL Authentication (Am I who I say I am?)
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)
ID and passwords scenarios…cont’d
© 2008 Progress Software Corporation18
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
© 2008 Progress Software Corporation20
Encountering data access errors
Possible reasons for this:• No authorization privileges
• Schema scope
Access denied (Authorization failed) (7512)
select count(*) from customer;
© 2008 Progress Software Corporation21
Authorization – What can I do?
Database wide (system admin or general creation)GRANT { DBA, RESOURCE } TO user_name [, user_name ] , …;
Privileges syntax: GRANT (2 types)
Can do anything Can CREATE stuff
© 2008 Progress Software Corporation22
Authorization – What can I do?
Database wide (system admin or general creation)GRANT { DBA, RESOURCE } TO user_name [, user_name ] , …;
GRANT { privilege [, privilege ], … | ALL } ON table_name TO { user_name [, user_name ], … | PUBLIC } [ WITH GRANT OPTION ];
Privileges syntax: GRANT (2 types)
For specified Tables or Views
– Where ‘privilege’ is:
{ SELECT | INSERT | DELETE | INDEX | UPDATE [ ( column , column , ... ) ] | REFERENCES [ ( column , column , ... ) ] }
© 2008 Progress Software Corporation23
Authorization – What can I do?
For specified Tables or Views - Example
See PSDN whitepaper on authorization for additional details.
Example Syntax: GRANT
GRANT select ON PUB.Customer TO richb;
GRANT select ON PUB.Order-line TO PUBLIC;
COMMIT WORK;ROLLBACK WORK;
© 2008 Progress Software Corporation24
Encountering data access errors
Possible reasons for this:• No authorization privileges
• Schema scope
Access denied (Authorization failed) (7512)
select count(*) from customer;
Schema scope
© 2008 Progress Software Corporation25
Schema: a logical grouping
PUB schemaCustomer table #1
Mysports database
In the “SQL world” schema is NOT meta data nor is it “Area 6”
Order table #2
Order-line table #3
richb schema
Customer table #4
Contacts table #5
Friends table #6
© 2008 Progress Software Corporation26
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:
Can set it as a registry entry in ODBC dsn definition
Schemas
CREATE PUBLIC SYNONYM customer FOR pub.customer;
What is a default schema?
© 2008 Progress Software Corporation27
Solving data access errors
Access denied (Authorization failed) (7512)
select count(*) from customer;
SELECT count(*) FROM pub.customer;
SET SCHEMA ‘pub’;SELECT count(*) FROM customer;
Solution #1:
SELECT count(*) FROM richb.customer;
Solution #2:
Solution #3:
© 2008 Progress Software Corporation28
Four level naming convention
4 Part Naming – Multi-Database Query
Fully Qualified Names – catalog is database name
catalog.schema.table.column-name
© 2008 Progress Software Corporation29
Four level naming convention
Example
4 Part Naming – Multi-Database Query
Fully Qualified Names – catalog is database name
catalog.schema.table.column-name
SELECT Pub.Customer.CustNum,
SportsPrimary.Pub.Customer.Name,
SportsAux1.Pub.Order.OrderNum …
© 2008 Progress Software Corporation30
Four level naming convention
Example
ABL has 3 level naming convention
4 Part Naming – Multi-Database Query
Fully Qualified Names – catalog is database name
catalog.schema.table.column-name
SELECT Pub.Customer.CustNum,
SportsPrimary.Pub.Customer.Name,
SportsAux1.Pub.Order.OrderNum …
catalog.table.column-name
© 2008 Progress Software Corporation31
OpenEdge Specifics
SQL is a standard,
but each vendor has it’s own dialect
© 2008 Progress Software Corporation32
OpenEdge SQL Specifics - Quoting
Hyphenated names:
Non-SQL standard names
SELECT cust-num FROM PUB.Customer;
SELECT “cust-num” FROM PUB.Customer;
Column CUST cannot be found (13865)
Solution: quoting (double quotes)
Most reporting applications will do this automatically.
© 2008 Progress Software Corporation33
Overstuffed fields - error
ABL allows more data than column definition SQL restricted to _field._sql-width value
Column name in table PUB.customer has value
exceeding it’s max length.
SELECT name from PUB.customer;
Solution:
ALTER table… ALTER column… SET PRO_SQL_WIDTH <value>;
Fix _sql-width via SQL “ALTER TABLE or Data Dictionary
© 2008 Progress Software Corporation34
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
© 2008 Progress Software Corporation35
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.
© 2008 Progress Software Corporation36
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 (10.1a)
Result: numeric value
102332.67
SELECT quarterlySales from PUB.MySales;
SELECT quarterlySales[1] from PUB.MySales;
© 2008 Progress Software Corporation37
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;
Result: numeric values
102332.67 330002.77 443434.55 333376.50
SELECT qS1, qS2, qS3, qS4 FROM pubView.QuarterSalesView;
GRANT select ON pubView.QuarterSalevVIew TO PUBLIC;
© 2008 Progress Software Corporation38
Q: What is it going to cost to run my query?
TIME = ?
Query Performance
© 2008 Progress Software Corporation39
Q: What is it going to cost to run my query?
TIME =
Query Performance
© 2008 Progress Software Corporation40
Basic Performance: What is the cost?
Database without statistics
10K
rows
50K
100K
SalesH
ist
Parts
OrderLines
Orders
Custom
er
Optimizer:
How many rows do I think you have?
© 2008 Progress Software Corporation41
Basic Performance: Here’s the cost.
Database with Update Statistics
SalesH
ist
Parts
OrderLines
Orders
10K
rows
50K
100K
Custom
er
© 2008 Progress Software Corporation42
All Statistics: Table Cardinality, indexes and all columns
Query Performance: Update Statistics
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND [ALL] COLUMN STATISTICS;
UPDATE STATISTICS syntax
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND [ALL] COLUMN STATISTICS FOR pub.customer;
Statistics - particular table
© 2008 Progress Software Corporation44
Basic Performance: Viewing Query Plans
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 );
Viewing query plan constructed by cost-based optimizer
Remembers last 10 queries for a client
SET PRO_SERVER LOG [ ON | OFF ] [ WITH ( { STATEMENT, QUERY_PLAN } ) ];
© 2008 Progress Software Corporation45
Simple single table select
SELECT … FROM pub.customer WHERE “cust-num” between 1000 and 1100 [ NoExecute ]
Query plan: What to look for
table
index
index keys, predicates
SELECT COMMAND. PROJECT [66] (| PROJECT [64] ( | | PUB.CUSTOMER. [0](| | | INDEX SCAN OF ( | | | | cust-num, | | | | | (PUB.CUSTOMER.cust-num) between (1000,1100))
© 2008 Progress Software Corporation46
Simple single table select
SELECT … FROM pub.customer WHERE ”cust-num” between 3000 and 3100 [ NoExecute ]
Query plan: What to look for
table
index
index keys, predicates
SELECT COMMAND. PROJECT [66] (| PROJECT [64] ( | | PUB.CUSTOMER. [0](| | | INDEX SCAN OF ( | | | | cust-num, | | | | | (PUB.CUSTOMER.cust-num) between (3000,3100))
WITH (INDEX (name))
with index hint
© 2008 Progress Software Corporation47
In Summary
Initial SQL connection
Setup and maintenance in OpenEdge database for security and performance
Specifics of OpenEdge with SQL applications
© 2008 Progress Software Corporation48
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
© 2008 Progress Software Corporation49
Questions?
© 2008 Progress Software Corporation50
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 ~
© 2008 Progress Software Corporation51
Thank You
© 2008 Progress Software Corporation52