Transcript E2: Getting Started With SQL

Getting Started With
Progress SQL-92
Sébastien Haefelé,
SQL92 Product Advocate
The Progress Company
Goals




Get basic guidelines to start and configure a
SQL92 servers
Properly manage privileges in your SQL92
database
Understand record locking behavior, versus
transaction isolation levels with the SQL92
engine
Increase performances while accessing the
SQL92 database
PUG September 2002, Oslo, Norway
2
© 2002, Progress Software Corporation
Agenda

SQL92 servers administration
– Client-server architecture
– Startup parameters
– Best practices to start SQL92 DB servers:


Before 9.1d
With 9.1d
– SQL server architecture




Database management
SQL92 and transactions
SQL92 and performances
Some Successes
PUG September 2002, Oslo, Norway
3
© 2002, Progress Software Corporation
Client Server Overview
SQL
Servers
SQL client
Shared Memory
SQL client
SQL & 4GL
Broker
Database
4GL client
4GL client
PUG September 2002, Oslo, Norway
4GL
Servers
4
© 2002, Progress Software Corporation
Servers Startup Parameters

DB server performance parameters
– -B, -L, -n, -spin, etc.

DB server internationalization parameters :
– -cpcase, -cpcoll, -cpstream, etc.

DB server statistics parameters :
– -usercount, -baseindex, etc.

DB server “network” parameters :
– -N, -S and -H, -Ma, -Mn, -Mi, -Mpb, etc.

DB server type parameters :
– -m1, -m2, -m3
PUG September 2002, Oslo, Norway
5
© 2002, Progress Software Corporation
New SQL92 Server Features
Available With 9.1d


SQL92 server in 9.1d is multi-threaded
New parameters coming in 9.1d
– Minimum clients per SQL92 server
– Maximum clients per SQL92 server
– Type of server a broker can start:-ServerType
(Values are: 4GL, SQL or both)
PUG September 2002, Oslo, Norway
6
© 2002, Progress Software Corporation
Best Practices to Start SQL92
DB Servers

Goal:
– Avoid networking resource issues linked to




-n
-minport and –maxport
Etc.
To achieve this goal:
– Separate SQL servers from 4GL servers

Get an SQL only broker
PUG September 2002, Oslo, Norway
7
© 2002, Progress Software Corporation
Best Practices to Start SQL92
DB Servers

Hypotheses:
– 30 4GL connections:


10 self service
20 client-server
– 15 SQL connections

Warning:
– In 9.1a, b and c, each SQL92 server can
handle only one client at a time

To better manage network resources: start 2
brokers. One for SQL and one for 4GL
clients
PUG September 2002, Oslo, Norway
8
© 2002, Progress Software Corporation
Best Practices to Start SQL92
DB Servers

Example of formula for 9.1a, b and c
– Total number of clients: 45 with 19 servers +
1 secondary login broker
– For the SQL broker:



Total number of SQL servers: 15
Broker service number: 5000
Server port range: [5100-5300]
– For the 4GL broker:




Total number of 4GL servers: 4
Max number of 4GL clients per server: 5
Broker service number: 6000
Server port range: [6100-6300]
PUG September 2002, Oslo, Norway
9
© 2002, Progress Software Corporation
Implementing the Example
With Proserve

Example of commands for 9.1a, b and c
– Start a 4GL broker (primary broker)

Proserve <db name> -S 6000 -H <host name>
-n 45 -Mn 20 -Mpb 4 -Ma 5 -minport 6100
-maxport 6300
– Start an SQL broker as secondary broker

Proserve <db name> -S 5000 -H <host name>
-m3 -Mpb 15 -Ma 1 -minport 5100
-maxport 5300
– To force a broker to spawn only a specific
type of servers refer to KBase # 20570
PUG September 2002, Oslo, Norway
10
© 2002, Progress Software Corporation
Best Practices to Start SQL92
DB Servers

Example of formula for 9.1d
– Total number of clients: 45 with 7 servers + 1
secondary login broker
– For the SQL broker:




Total number of SQL servers: 3
Max number of SQL clients per server: 5
Broker service number: 5000
Server port range: [5100-5300]
– For the 4GL broker:




Total number of 4GL servers: 4
Max number of 4GL clients per server: 5
Broker service number: 6000
Server port range: [6100-6300]
PUG September 2002, Oslo, Norway
11
© 2002, Progress Software Corporation
Implementing the Example
With Proserve

Example of commands for 9.1d
– Start a 4GL broker (Primary broker)

Proserve <db name> -S 6000 -H <host name>
-n 45 -Mn 8 -Mpb 4 -ServerType 4GL
-Ma 5 -minport 6100 -maxport 6300
– Start an SQL broker as secondary broker


Proserve <db name> -S 5000 -H <host name>
-m3 -Mpb 3 -ServerType SQL -Ma 5
-minport 5100 -maxport 5300
The primary Broker should always be the
one with the highest value for -Ma
PUG September 2002, Oslo, Norway
12
© 2002, Progress Software Corporation
Client-server Configurations
Recommended
SQL client
SQL
Servers
SQL only
Broker
Shared Memory
SQL client
4GL client
4GL client
PUG September 2002, Oslo, Norway
4GL only
Broker
Database
4GL
Servers
13
© 2002, Progress Software Corporation
SQL Server Architecture
Network RPC Messages
Local Transaction
Manager
Comm. Manager
SQL Statement Manager
Statement
Parser
Authorization
Manager
Optimizer
View
Manager
Execution
Manager
Cost
Manager
Statistics
Manager
Schema Manager
Transactional Relational Storage Manager
PUG September 2002, Oslo, Norway
14
© 2002, Progress Software Corporation
Agenda


SQL92 servers administration
Database management
– Database notions that differs from the 4GL
– Schema management
– Data definition language limits: ALTER
TABLE
– Data definition language: CREATE USER
– Privileges management



SQL92 and transactions
SQL92 and performances
Some Successes
PUG September 2002, Oslo, Norway
15
© 2002, Progress Software Corporation
Database Notions That Differ
From the 4GL

SQL important key words that differs from
4GL key words:
– Catalog: 4GL schema
– Schema: no equivalent notions
– Columns: 4GL fields
PUG September 2002, Oslo, Norway
16
© 2002, Progress Software Corporation
Schema Management


A schema is an entity that helps define
groups of tables with logical or functional
links. Tables located in a schema usually
share information pertaining to a specific
set of operations
This means in the Progress implementation
that
– A schema is not linked to a user
– A user has by default a schema attached to
him which is named after the user ID (it is his
own work space)
PUG September 2002, Oslo, Norway
17
© 2002, Progress Software Corporation
Schema Management

While developing / using an application, you
may need to move from one schema to an
other:
– Use the SQL ‘set schema’ statement

Keep in mind that you can not create / alter /
drop schema
– They are implicit
– Represent a logical way of grouping tables
PUG September 2002, Oslo, Norway
18
© 2002, Progress Software Corporation
Data Definition Language
Limits: ALTER TABLE

Within a schema you have to perform
database administration tasks:
– In 9.1a,b,c,d you can only create and drop
tables
– In the next Progress version, phase 1 of
ALTER TABLE is implemented:

Renaming table
PUG September 2002, Oslo, Norway
19
© 2002, Progress Software Corporation
Data Definition Language
Limits: ALTER TABLE

Best practices to “mimic” ALTER TABLE for
tables located in the ‘pub’ schema
– Use the 4GL dictionary

Limits:
– Can not define primary and candidate keys
– Can not define foreign keys

Allows easy modification of the table
definition
PUG September 2002, Oslo, Norway
20
© 2002, Progress Software Corporation
Data Definition Language
Limits: ALTER TABLE

Best practices to “mimic” ALTER TABLE for
tables not located in the ‘pub’ schema
– CREATE TABLE sql92.State (
State CHAR(4) NOT NULL PRIMARY KEY,
Region VARCHAR(4))
AS SELECT state, '' FROM pub.State;

Limits:
– Difficult operation when foreign keys are
defined on the altered table

Allows easy modification of the table
definition
PUG September 2002, Oslo, Norway
21
© 2002, Progress Software Corporation
Data Definition Language:
CREATE USER

IMPORTANT to remember:
– A SQL database is a closed system

Always requires a user ID and password to
establish a connection
– A 4GL database is an open system

Does not specifically require a user ID and
password to establish a connection
PUG September 2002, Oslo, Norway
22
© 2002, Progress Software Corporation
Data Definition Language:
CREATE USER



The ‘user’ table is shared between the SQL
and 4GL environment
SQL has no notions of ‘BLANK USER’
This means
– Creating a first user via SQL is equivalent to
creating a user from the progress data
administration
– The user list maintained via SQL (alter / drop
users) is updated for the 4GL as well
PUG September 2002, Oslo, Norway
23
© 2002, Progress Software Corporation
Data Definition Language:
CREATE USER

When no users are created:
– 4GL will not display a login dialogue-box
– SQL will ALWAYS require a user ID and
password, but they can be anything

When users are created:
– 4GL will display a login dialogue-box but still
allow blank connections (if not disabled)
– SQL will ALWAYS require a VALID login
PUG September 2002, Oslo, Norway
24
© 2002, Progress Software Corporation
Best Practice for User
Creation

Depends on your environment:
– SQL only connections:


Create users to allow user id and password
validation from the SQL interface
Manage SQL privileges accordingly
– SQL and 4GL connections:



Create users if security is used from the 4GL
as well
Do not create users if 4GL does not use
security
Manage SQL privileges accordingly
PUG September 2002, Oslo, Norway
25
© 2002, Progress Software Corporation
When Do We Check
Privileges?
Network RPC Messages
Local Transaction
Manager
Comm. Manager
SQL Statement Manager
Statement
Parser
Authorization
Manager
Optimizer
View
Manager
Execution
Manager
Cost
Manager
Statistics
Manager
Schema Manager
Transactional Relational Storage Manager
PUG September 2002, Oslo, Norway
26
© 2002, Progress Software Corporation
Privileges Management

In SQL, privileges are:
– Checked each time an SQL request is
processed
– Applicable to different database “objects”
– Associated to users or groups of users
– Designed to permit or prevent specific
actions or group of actions on database
“objects”

SQL92 privileges differ from 4GL privileges
PUG September 2002, Oslo, Norway
27
© 2002, Progress Software Corporation
Two Main Types of Privileges

On the whole database
– Give / restrict system administration
privileges (DBA)
– Give / restrict general creation privileges on
a database (resource)

On tables, views & procedures
– Give / restrict specific operations like:





Alter an object definition (table, view…)
Delete, insert, select and update records
Execute stored procedures
Granting your own privileges
Define constraints to an existing table
PUG September 2002, Oslo, Norway
28
© 2002, Progress Software Corporation
Privileges Are

Granted to / Revoked from
– Everybody (database “object” is made
public)
– A specific user

Stored in
– Sysprogress.SYSDBAUTH
– Sysprogress.SYSTABAUTH
– Sysprogress.SYSCOLAUTH

The cause of error: “Access Denied
(Authorization failed)(7512)”
PUG September 2002, Oslo, Norway
29
© 2002, Progress Software Corporation
The Syntax

Two key statements to manage SQL-92
privileges:
– GRANT:

Example GRANT { RESOURCE, DBA } TO
username [ , username ] , ... ;
– REVOKE:


Example: REVOKE { RESOURCE , DBA }
FROM { username [ , username ] , ... };
For the exact syntax check your “SQL-92
guide and reference manual”
PUG September 2002, Oslo, Norway
30
© 2002, Progress Software Corporation
Best Practices in Privileges
Management


A DBA is the only person to have full
privileges on a database
The first DBA is the database creator:
– Do not use ‘SYSPROGRESS’ as DBA
– Check the sysprogress.sysdbauth table to
find out WHO the DBA is (if you forgot its ID)

No users other then DBA and the creator
have privileges on new tables, views…
– Privileges to other users need to be granted

There is no need to create users to give
them privileges
PUG September 2002, Oslo, Norway
31
© 2002, Progress Software Corporation
Best Practices in Privileges
Management

Before assigning privileges ask yourself:
– Should this user have DBA / resources
privileges?
– Is this object selectable by everybody?
(GRANT SELECT ON table TO PUBLIC)
– Is this object updateable by everybody?
(GRANT UPDATE ON table TO PUBLIC)
– Is this object insert able by everybody?
(GRANT INSERT ON table TO PUBLIC)
– Etc
PUG September 2002, Oslo, Norway
32
© 2002, Progress Software Corporation
Agenda



SQL92 servers administration
Database management
SQL92 and transactions






Processing phenomena in a transactional
environment
SQL92 isolation levels
Progress implementation
Best practices
SQL92 and performances
Some Successes
PUG September 2002, Oslo, Norway
33
© 2002, Progress Software Corporation
SQL92 and Transactions

Transaction control is explicit in SQL


Commit work
Rollback work
But


Record locking is implicit in SQL
Record locking behavior differs for each
SQL transaction isolation level
PUG September 2002, Oslo, Norway
34
© 2002, Progress Software Corporation
Processing Phenomena in a
Transactional Environment

Dirty read
– Scenario:

User 1 executes:
– INSERT INTO pub.State (state, state_name, region)
values (‘AB', 'Abcdefghij’, ‘ABCD');

User 2 executes: select * from pub.State
– User 2 sees: state ‘AB’


User 1 executes: rollback work
User 2 has seen data that did not really exist!
– Occurs when one user is updating / inserting
a record while an other user is reading it, but
that work is not committed to the database
PUG September 2002, Oslo, Norway
35
© 2002, Progress Software Corporation
Processing Phenomena in a
Transactional Environment

Non repeatable read
– Scenario:


User 1 executes: select * from pub.State
User 2 executes:
– Update pub.State set state_name = 'hello world'
where state = ‘AK’;
Commit work;

User 1 re-executes: select * from pub.State
– User 1 has updated records in his result set !
– Occurs when one user is repeating a read
operation on the same records but has
updated values
PUG September 2002, Oslo, Norway
36
© 2002, Progress Software Corporation
Processing Phenomena in a
Transactional Environment

Phantom
– Scenario:


User 1 executes: select * from pub.State
User 2 executes:
– INSERT INTO pub.State (state, state_name, region)
values (‘AB', 'Abcdefghij’, ‘ABCD');
Commit work;

User 1 re-executes: select * from pub.State
– User 1 has new records in his result set !
– Occurs when one user is repeating a read
operation on the same records but has new
records in his result set
PUG September 2002, Oslo, Norway
37
© 2002, Progress Software Corporation
SQL92 Isolation Levels

4 levels that affect the session wide record
locking behavior
–
–
–
–
Uncommitted read
Committed read
Repeatable read (default)
Serializable
PUG September 2002, Oslo, Norway
38
© 2002, Progress Software Corporation
SQL92 Isolation Levels
Dirty Read
Non
Repeatable
Read
Phantom
Read
Allowed
Allowed
Allowed
Prevented
Allowed
Allowed
Repeatable
Read
Prevented
Prevented
Allowed
Serializable
Prevented
Prevented
Prevented
Uncommitted
Read
Committed
Read
PUG September 2002, Oslo, Norway
39
© 2002, Progress Software Corporation
SQL92 Isolation Levels

Uncommitted read
– Allows all processing phenomena to occur
– No record and schema locks
– Equivalent to a 4GL read-only session

Committed read
– Prevents dirty reads but allows all other
phenomena
– Shared record locks on select (locks are
immediately released)
– Exclusive otherwise
PUG September 2002, Oslo, Norway
40
© 2002, Progress Software Corporation
SQL92 Isolation Levels

Repeatable read
– Allows phantoms but prevents all other
phenomena
– Shared record locks on select, exclusive
otherwise (locks are held until transaction
end)
– Roughly equivalent to a 4GL session

Serializable
– Prevents all phenomena
– Shared table locks on select, shared intent
exclusive table locks otherwise
PUG September 2002, Oslo, Norway
41
© 2002, Progress Software Corporation
Progress Implementation
Isolation
Level
Uncommitted
Read
Committed
Read
Repeatable
Read
Serializable
PUG September 2002, Oslo, Norway
Select
Table
Record
Lock
Lock
Update
Table
Record
Lock
Lock
None
Operation Operation
Not
Not
Allowed
Allowed
Intent
Share
Share
Intent
Exclusive
Exclusive
Intent
Share
Share
Intent
Exclusive
Exclusive
None
Share
Intent
Exclusive
Exclusive
None
Share
42
© 2002, Progress Software Corporation
Progress Implementation

SQL92 needs
– A locking mechanism different from 4GL
– Locks are set at different levels:




Catalog
Table(s)
Record(s)
4GL uses
– Locks mainly at record level (default)
– SQL92 locking behavior when required (For
instance when using the data dictionary)
PUG September 2002, Oslo, Norway
43
© 2002, Progress Software Corporation
SQL92 Isolation Levels

Higher isolation level means:
– Better data consistency (more record locks)
– Lower performances

Lower isolation level means:
– Lower data consistency (less record locks)
– Better performances
PUG September 2002, Oslo, Norway
44
© 2002, Progress Software Corporation
Best Practices in Choosing
Transaction Isolation Levels

In a multi-user environment
– Un-committed read should be used for
reporting applications that do not rely on
data accuracy (ex: statistical information at
the end of a month)
– Committed read or repeatable read should be
used in most cases
– Serializable should be used for transactional
applications that do require maximum data
consistency

In single user environment use only
committed read
PUG September 2002, Oslo, Norway
45
© 2002, Progress Software Corporation
Agenda




SQL92 servers administration
Database management
SQL92 and transactions
SQL92 and performances
–
–
–
–

When does optimization take place?
Manual query optimization
Automated query optimization
Best practices to keep good performances
Some Successes
PUG September 2002, Oslo, Norway
46
© 2002, Progress Software Corporation
When Does Optimization Take
Place?
Network RPC Messages
Local Transaction
Manager
Comm. Manager
SQL Statement Manager
Statement
Parser
Authorization
Manager
Optimizer
View
Manager
Execution
Manager
Cost
Manager
Statistics
Manager
Schema Manager
Transactional Relational Storage Manager
PUG September 2002, Oslo, Norway
47
© 2002, Progress Software Corporation
Automated Query
Optimization

Optimization goal is to:
– Sort the table list, from minimum number of
records to highest number of records to
access

Based on the information provided in the
query
– Access table with the best index

Optimization is complex
PUG September 2002, Oslo, Norway
48
© 2002, Progress Software Corporation
Automated Query
Optimization

To achieve these goals, the optimizer will
mainly use statistics based on:
– Table cardinality
– Value distribution in columns

In 9.1d there are new statistics to help
decide:
– Which index to use
PUG September 2002, Oslo, Norway
49
© 2002, Progress Software Corporation
Automated Query
Optimization

Statistics are maintained with the SQL
statement ‘update statistics’

In 9.1a, b and c:
– It can be executed per table
– Has to be executed when no other user
updates the database

And 9.1d ?
PUG September 2002, Oslo, Norway
50
© 2002, Progress Software Corporation
Automated Query
Optimization

Starting with 9.1d:
– Update statistics can be executed to build:

All statistics:
– The complete database
– On a specific table

Specific statistics on a specific table:
–
–
–
–
For each indexed column
All columns in a table
All indexes defined on this table
Only table cardinality
– Runs on-line regardless of other users
updating the database
PUG September 2002, Oslo, Norway
51
© 2002, Progress Software Corporation
Some Numbers …

Results for a mix of 19 customer queries,
Windows client, Solaris Server (always
compared to 9.1A considered as the base
line)
Release Total Query
Time
9.1A 1.00
Relative
Performance
1.00 x
9.1B 0.75
1.32 x
9.1C 0.56
1.79 x
9.1D 0.11
9.29 x
PUG September 2002, Oslo, Norway
52
© 2002, Progress Software Corporation
Best Practices to Keep Good
Performances

Monitor on a regular basis
– Table access to find if indexes are missing
– Table heavily modified with lots of
'transactions‘ (create / update / delete
records)

Maintain when required
– Statistics for indexes on a table
– Statistics for indexed columns in a table
– Statistics for table cardinality
PUG September 2002, Oslo, Norway
53
© 2002, Progress Software Corporation
Best Practices to Keep Good
Performances

When to execute ‘update statistics’
– In 9.1a, b or c (due to locking behavior)


When the database is not used or when there
is low activity
Execute the update statistics table per table
– In 9.1d


Anytime
Execute the update statistics:
– Per table
 For all indexes in that table
 For all indexed column in that table
PUG September 2002, Oslo, Norway
54
© 2002, Progress Software Corporation
Agenda





SQL92 servers administration
Database management
SQL92 and transactions
SQL92 and performances
Some Successes
– J2EE
– JDBC
– JSP
PUG September 2002, Oslo, Norway
55
© 2002, Progress Software Corporation
Some Successes

J2EE architecture already in use:
– Server WebSphere – JRun - JBoss
– Progress 9.1C and D on AIX

JDBC architecture already in use:
– Plain java application with JDK 1.3.1
– Progress 9.1C and D on all supported OS

JSP and Java Servlets applications already
in use:
– Apache + Tomcat
– Progress 9.1C and 9.1D
PUG September 2002, Oslo, Norway
56
© 2002, Progress Software Corporation
Questions
PUG September 2002, Oslo, Norway
57
© 2002, Progress Software Corporation