Getting Started With Progress SQL92 Sbastien Haefel SQL

Getting Started With Progress SQL-92 Sébastien Haefelé, SQL 92 Product Advocate The Progress Company

Goals n n Get basic guidelines to start and configure a SQL 92 servers Properly manage privileges in your SQL 92 database Understand record locking behavior, versus transaction isolation levels with the SQL 92 engine Increase performances while accessing the SQL 92 database PUG September 2002, Oslo, Norway 2 © 2002, Progress Software Corporation

Agenda n SQL 92 servers administration – Client-server architecture – Startup parameters – Best practices to start SQL 92 DB servers: n n Before 9. 1 d With 9. 1 d – SQL server architecture n n Database management SQL 92 and transactions SQL 92 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 & 4

Servers Startup Parameters n DB server performance parameters – -B, -L, -n, -spin, etc. n DB server internationalization parameters : – -cpcase, -cpcoll, -cpstream, etc. n DB server statistics parameters : – -usercount, -baseindex, etc. n DB server “network” parameters : – -N, -S and -H, -Ma, -Mn, -Mi, -Mpb, etc. n DB server type parameters : – -m 1, -m 2, -m 3 PUG September 2002, Oslo, Norway 5 © 2002, Progress Software Corporation

New SQL 92 Server Features Available With 9. 1 d n n SQL 92 server in 9. 1 d is multi-threaded New parameters coming in 9. 1 d – Minimum clients per SQL 92 server – Maximum clients per SQL 92 server – Type of server a broker can start: -Server. Type (Values are: 4 GL, SQL or both) PUG September 2002, Oslo, Norway 6 © 2002, Progress Software Corporation

Best Practices to Start SQL 92 DB Servers n Goal: – Avoid networking resource issues linked to n n -n -minport and –maxport Etc. To achieve this goal: – Separate SQL servers from 4 GL servers n Get an SQL only broker PUG September 2002, Oslo, Norway 7 © 2002, Progress Software Corporation

Best Practices to Start SQL 92 DB Servers n Hypotheses: – 30 4 GL connections: n n 10 self service 20 client-server – 15 SQL connections n Warning: – In 9. 1 a, b and c, each SQL 92 server can handle only one client at a time n To better manage network resources: start 2 brokers. One for SQL and one for 4 GL clients PUG September 2002, Oslo, Norway 8 © 2002, Progress Software Corporation

Best Practices to Start SQL 92 DB Servers n Example of formula for 9. 1 a, b and c – Total number of clients: 45 with 19 servers + 1 secondary login broker – For the SQL broker: n n n Total number of SQL servers: 15 Broker service number: 5000 Server port range: [5100 -5300] – For the 4 GL broker: n n Total number of 4 GL servers: 4 Max number of 4 GL 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 n Example of commands for 9. 1 a, b and c – Start a 4 GL broker (primary broker) n 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 n Proserve <db name> -S 5000 -H <host name> -m 3 -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 SQL 92 DB Servers n Example of formula for 9. 1 d – Total number of clients: 45 with 7 servers + 1 secondary login broker – For the SQL broker: n n 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 4 GL broker: n n Total number of 4 GL servers: 4 Max number of 4 GL 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 n Example of commands for 9. 1 d – Start a 4 GL broker (Primary broker) n Proserve <db name> -S 6000 -H <host name> -n 45 -Mn 8 -Mpb 4 -Server. Type 4 GL -Ma 5 -minport 6100 -maxport 6300 – Start an SQL broker as secondary broker n n Proserve <db name> -S 5000 -H <host name> -m 3 -Mpb 3 -Server. Type 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 4 GL client PUG September 2002, Oslo, Norway 4 GL only Broker Database 4 GL Servers 13 © 2002, Progress Software Corporation

SQL Server Architecture Network RPC Messages Local Transaction Manager Comm. Manager SQL Statement Manager Statement Parser Authorization Manager Execution Manager Optimizer View Manager Cost Manager Statistics Manager Schema Manager Transactional Relational Storage Manager PUG September 2002, Oslo, Norway 14 © 2002, Progress Software Corporation

Agenda n n SQL 92 servers administration Database management – Database notions that differs from the 4 GL – Schema management – Data definition language limits: ALTER TABLE – Data definition language: CREATE USER – Privileges management n n n SQL 92 and transactions SQL 92 and performances Some Successes PUG September 2002, Oslo, Norway 15 © 2002, Progress Software Corporation

Database Notions That Differ From the 4 GL n SQL important key words that differs from 4 GL key words: – Catalog: 4 GL schema – Schema: no equivalent notions – Columns: 4 GL fields PUG September 2002, Oslo, Norway 16 © 2002, Progress Software Corporation

Schema Management n n 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 n While developing / using an application, you may need to move from one schema to an other: – Use the SQL ‘set schema’ statement n 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 n Within a schema you have to perform database administration tasks: – In 9. 1 a, b, c, d you can only create and drop tables – In the next Progress version, phase 1 of ALTER TABLE is implemented: n Renaming table PUG September 2002, Oslo, Norway 19 © 2002, Progress Software Corporation

Data Definition Language Limits: ALTER TABLE n Best practices to “mimic” ALTER TABLE for tables located in the ‘pub’ schema – Use the 4 GL dictionary n Limits: – Can not define primary and candidate keys – Can not define foreign keys n Allows easy modification of the table definition PUG September 2002, Oslo, Norway 20 © 2002, Progress Software Corporation

Data Definition Language Limits: ALTER TABLE n Best practices to “mimic” ALTER TABLE for tables not located in the ‘pub’ schema – CREATE TABLE sql 92. State ( State CHAR(4) NOT NULL PRIMARY KEY, Region VARCHAR(4)) AS SELECT state, '' FROM pub. State; n Limits: – Difficult operation when foreign keys are defined on the altered table n Allows easy modification of the table definition PUG September 2002, Oslo, Norway 21 © 2002, Progress Software Corporation

Data Definition Language: CREATE USER n IMPORTANT to remember: – A SQL database is a closed system n Always requires a user ID and password to establish a connection – A 4 GL database is an open system n 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 n n n The ‘user’ table is shared between the SQL and 4 GL 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 4 GL as well PUG September 2002, Oslo, Norway 23 © 2002, Progress Software Corporation

Data Definition Language: CREATE USER n When no users are created: – 4 GL will not display a login dialogue-box – SQL will ALWAYS require a user ID and password, but they can be anything n When users are created: – 4 GL 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 n Depends on your environment: – SQL only connections: n n Create users to allow user id and password validation from the SQL interface Manage SQL privileges accordingly – SQL and 4 GL connections: n n n Create users if security is used from the 4 GL as well Do not create users if 4 GL 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 Execution Manager Optimizer View Manager Cost Manager Statistics Manager Schema Manager Transactional Relational Storage Manager PUG September 2002, Oslo, Norway 26 © 2002, Progress Software Corporation

Privileges Management n 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” n SQL 92 privileges differ from 4 GL privileges PUG September 2002, Oslo, Norway 27 © 2002, Progress Software Corporation

Two Main Types of Privileges n On the whole database – Give / restrict system administration privileges (DBA) – Give / restrict general creation privileges on a database (resource) n On tables, views & procedures – Give / restrict specific operations like: n n n 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 n Granted to / Revoked from – Everybody (database “object” is made public) – A specific user n Stored in – Sysprogress. SYSDBAUTH – Sysprogress. SYSTABAUTH – Sysprogress. SYSCOLAUTH n The cause of error: “Access Denied (Authorization failed)(7512)” PUG September 2002, Oslo, Norway 29 © 2002, Progress Software Corporation

The Syntax n Two key statements to manage SQL-92 privileges: – GRANT: n Example GRANT { RESOURCE, DBA } TO username [ , username ] , . . . ; – REVOKE: n n 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 n n 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) n No users other then DBA and the creator have privileges on new tables, views… – Privileges to other users need to be granted n 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 n 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 n n n SQL 92 servers administration Database management SQL 92 and transactions n n n Processing phenomena in a transactional environment SQL 92 isolation levels Progress implementation Best practices SQL 92 and performances Some Successes PUG September 2002, Oslo, Norway 33 © 2002, Progress Software Corporation

SQL 92 and Transactions n Transaction control is explicit in SQL n n Commit work Rollback work But n n 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 n Dirty read – Scenario: n User 1 executes: – INSERT INTO pub. State (state, state_name, region) values (‘AB', 'Abcdefghij’, ‘ABCD'); n User 2 executes: select * from pub. State – User 2 sees: state ‘AB’ n n 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 n Non repeatable read – Scenario: n n User 1 executes: select * from pub. State User 2 executes: – Update pub. State set state_name = 'hello world' where state = ‘AK’; Commit work; n 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 n Phantom – Scenario: n n User 1 executes: select * from pub. State User 2 executes: – INSERT INTO pub. State (state, state_name, region) values (‘AB', 'Abcdefghij’, ‘ABCD'); Commit work; n 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

SQL 92 Isolation Levels n 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

SQL 92 Isolation Levels Dirty Read Non Repeatable Read Phantom Read Uncommitted Read Allowed Committed Read Prevented Allowed Prevented Repeatable Read Serializable PUG September 2002, Oslo, Norway 39 © 2002, Progress Software Corporation

SQL 92 Isolation Levels n Uncommitted read – Allows all processing phenomena to occur – No record and schema locks – Equivalent to a 4 GL read-only session n 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

SQL 92 Isolation Levels n 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 4 GL session n 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 Select Table Record Lock None Operation Not Allowed Intent Share Intent Exclusive None Share Intent Exclusive Uncommitted Read None Committed Read Repeatable Read Serializable PUG September 2002, Oslo, Norway Update Table Record Lock Share 42 © 2002, Progress Software Corporation

Progress Implementation n SQL 92 needs – A locking mechanism different from 4 GL – Locks are set at different levels: n n Catalog Table(s) Record(s) 4 GL uses – Locks mainly at record level (default) – SQL 92 locking behavior when required (For instance when using the data dictionary) PUG September 2002, Oslo, Norway 43 © 2002, Progress Software Corporation

SQL 92 Isolation Levels n Higher isolation level means: – Better data consistency (more record locks) – Lower performances n 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 n 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 n In single user environment use only committed read PUG September 2002, Oslo, Norway 45 © 2002, Progress Software Corporation

Agenda n n SQL 92 servers administration Database management SQL 92 and transactions SQL 92 and performances – When does optimization take place? – Manual query optimization – Automated query optimization – Best practices to keep good performances n 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 Execution Manager Optimizer View Manager Cost Manager Statistics Manager Schema Manager Transactional Relational Storage Manager PUG September 2002, Oslo, Norway 47 © 2002, Progress Software Corporation

Automated Query Optimization n Optimization goal is to: – Sort the table list, from minimum number of records to highest number of records to access n Based on the information provided in the query – Access table with the best index n Optimization is complex PUG September 2002, Oslo, Norway 48 © 2002, Progress Software Corporation

Automated Query Optimization n To achieve these goals, the optimizer will mainly use statistics based on: – Table cardinality – Value distribution in columns n In 9. 1 d there are new statistics to help decide: – Which index to use PUG September 2002, Oslo, Norway 49 © 2002, Progress Software Corporation

Automated Query Optimization n Statistics are maintained with the SQL statement ‘update statistics’ n In 9. 1 a, b and c: – It can be executed per table – Has to be executed when no other user updates the database n And 9. 1 d ? PUG September 2002, Oslo, Norway 50 © 2002, Progress Software Corporation

Automated Query Optimization n Starting with 9. 1 d: – Update statistics can be executed to build: n All statistics: – The complete database – On a specific table n 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 … n Results for a mix of 19 customer queries, Windows client, Solaris Server (always compared to 9. 1 A considered as the base line) Release Total Query Time Relative Performance 9. 1 A 1. 00 x 9. 1 B 0. 75 1. 32 x 9. 1 C 0. 56 1. 79 x 9. 1 D 0. 11 9. 29 x PUG September 2002, Oslo, Norway 52 © 2002, Progress Software Corporation

Best Practices to Keep Good Performances n Monitor on a regular basis – Table access to find if indexes are missing – Table heavily modified with lots of 'transactions‘ (create / update / delete records) n 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 n When to execute ‘update statistics’ – In 9. 1 a, b or c (due to locking behavior) n n When the database is not used or when there is low activity Execute the update statistics table per table – In 9. 1 d n n Anytime Execute the update statistics: – Per table n For all indexes in that table n For all indexed column in that table PUG September 2002, Oslo, Norway 54 © 2002, Progress Software Corporation

Agenda n n n SQL 92 servers administration Database management SQL 92 and transactions SQL 92 and performances Some Successes – J 2 EE – JDBC – JSP PUG September 2002, Oslo, Norway 55 © 2002, Progress Software Corporation

Some Successes n J 2 EE architecture already in use: – Server Web. Sphere – JRun - JBoss – Progress 9. 1 C and D on AIX n JDBC architecture already in use: – Plain java application with JDK 1. 3. 1 – Progress 9. 1 C and D on all supported OS n JSP and Java Servlets applications already in use: – Apache + Tomcat – Progress 9. 1 C and 9. 1 D PUG September 2002, Oslo, Norway 56 © 2002, Progress Software Corporation