Getting Started With Progress SQL-92

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

2. 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

3. 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

4. Client Server Overview SQL Servers Shared Memory SQL client SQL client Database 4GL Servers SQL & 4GL Broker 4GL client 4GL client

5. 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

6. 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)

7. 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

8. 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

9. 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]

10. 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

11. 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]

12. 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

13. Client-server Configurations Recommended SQL Servers Shared Memory SQL client SQL client Database 4GL Servers SQL only Broker 4GL client 4GL client 4GL only Broker

14. SQL Server Architecture Network RPC Messages Local Transaction Manager Comm. Manager SQL Statement Manager Statement Parser Authorization Manager Optimizer Execution Manager View Manager Cost Manager Statistics Manager Schema Manager Transactional Relational Storage Manager

15. 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

16. 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

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

18. 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

19. 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

20. 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

21. 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

22. 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

23. 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

24. 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

25. 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

26. When Do We Check Privileges? Network RPC Messages Local Transaction Manager Comm. Manager SQL Statement Manager Statement Parser Authorization Manager Optimizer Execution Manager View Manager Cost Manager Statistics Manager Schema Manager Transactional Relational Storage Manager

27. 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

28. 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

29. 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)”

30. 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”

31. 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

32. 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

33. 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

34. 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

35. 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

36. 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

37. 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

38. SQL92 Isolation Levels • 4 levels that affect the session wide record locking behavior • Uncommitted read • Committed read • Repeatable read (default) • Serializable

39. SQL92 Isolation Levels

40. 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

41. 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

42. Progress Implementation

43. 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)

44. 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

45. 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

46. 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

47. When Does Optimization Take Place? Network RPC Messages Local Transaction Manager Comm. Manager SQL Statement Manager Statement Parser Authorization Manager Optimizer Execution Manager View Manager Cost Manager Statistics Manager Schema Manager Transactional Relational Storage Manager

48. 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

49. 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

50. 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 ?