b6: beginners guide to openedge

1. B6: Beginners Guide to OpenEdge® SQL via ODBC or JDBC

2. Familiar with ABL General theme- compare to ABL Familiar with ABL General theme- compare to ABL

3. What this is, what this is notWhat this is, what this is not

4. Getting Connected – Client side: ODBC and JDBC drivers Provided in: SQL Client Access License RDBMS Licenses Progress Download Center for OpenEdge (SQL Client Access - free) ODBC Driver - wire protocol driver : 10.1a+ JDBC Driver - Type 4 (pure Java) : 10.1a+ Provided in: SQL Client Access License RDBMS Licenses Progress Download Center for OpenEdge (SQL Client Access - free) ODBC Driver - wire protocol driver : 10.1a+ JDBC Driver - Type 4 (pure Java) : 10.1a+

5. CLASSPATH ( run ‘sql_env’) Class loader (Loading the JDBC driver) URL for JDBC DriverManager.getConnection JDBC: Pure Java Driver Run sql_env sqlexp -db database-name -S port | service-name -H host -user userid –password password sqlexp –url jdbc:datadirect:openedge://host:port;databaseName=db_name; servicename=service_name -user userid -password password 1. It must load the JDBC driver. 2. It must connect to the JDBC driver. To load the JDBC driver, call the Class.forName method in the JDBC application. The Class.forName method takes the fully qualified class name of the JDBC driver as its argument. The fully qualified class name for the JDBC driver is com.ddtek.jdbc.openedge.OpenEdgeDriver. This class name is case-sensitive and must be typed exactly as shown. The Class.forName method also registers the specified JDBC driver with the Driver Manager class so that the driver is available for connections. Now that the JDBC driver has been loaded and registered, the DriverManager.getConnection method must be called to establish a connection to the database. The getConnection method takes three arguments: • A string containing a URL. The Driver Manager uses this URL to find a driver that can connect to the database represented by the given URL. Once the driver is found, the URL is used by the Driver class to establish the connection to the database.Run sql_env sqlexp -db database-name -S port | service-name -H host -user userid –password password sqlexp –url jdbc:datadirect:openedge://host:port;databaseName=db_name; servicename=service_name -user userid -password password 1. It must load the JDBC driver. 2. It must connect to the JDBC driver. To load the JDBC driver, call the Class.forName method in the JDBC application. The Class.forName method takes the fully qualified class name of the JDBC driver as its argument. The fully qualified class name for the JDBC driver is com.ddtek.jdbc.openedge.OpenEdgeDriver. This class name is case-sensitive and must be typed exactly as shown. The Class.forName method also registers the specified JDBC driver with the Driver Manager class so that the driver is available for connections. Now that the JDBC driver has been loaded and registered, the DriverManager.getConnection method must be called to establish a connection to the database. The getConnection method takes three arguments: • A string containing a URL. The Driver Manager uses this URL to find a driver that can connect to the database represented by the given URL. Once the driver is found, the URL is used by the Driver class to establish the connection to the database.

6. ODBC DSN – single connection Unix- uses a .odbc.ini file - come see me after Unix- uses a .odbc.ini file - come see me after

7. ODBC: Multi-Database configuration (10.1B) msdbq:config1 specifies a property file JDBC has same ability, using same means Multiple Databases on same host sports2000.oesql.properties : in database location See PDSN presentation on MDBQ Unix- uses a .odbc.ini file - come see me after msdbq:config1 specifies a property file JDBC has same ability, using same means Multiple Databases on same host sports2000.oesql.properties : in database location See PDSN presentation on MDBQ Unix- uses a .odbc.ini file - come see me after

8. ODBC DSN - Advanced Tab “TimeStamp with Timezone” support – what happens when unchecked Suggest using READ COMMITTED – suggest always setting this explicitly - check your default ( varies by release) REPEATABLE_READ will give you closest behavior to ABL, but I think you want READ_COMMITTED or READ_UNCOMMITTED Unicode support - Wide Character changes which types are used in your describe phase Fetch Array Size is not the same as –Mm in ABL “TimeStamp with Timezone” support – what happens when unchecked Suggest using READ COMMITTED – suggest always setting this explicitly - check your default ( varies by release) REPEATABLE_READ will give you closest behavior to ABL, but I think you want READ_COMMITTED or READ_UNCOMMITTED Unicode support - Wide Character changes which types are used in your describe phase Fetch Array Size is not the same as –Mm in ABL

9. Isolation Level Affect on Lock Type Go through behavior of each isolation level Need to talk about dirty reads somewhere Go through behavior of each isolation level Need to talk about dirty reads somewhere

10. Connection – server side

11. Default server setup Discuss advantages Discuss advantages

12. “Recommended” server setup Discuss advantages Discuss advantages

13. Recommended parameters example -Mi minimum number of clients per server -Ma - maximum number of clients per server -ServerTtype specifies that only clients of that type may connect to servers spawned by that broker and is case sensitive. -Mpb is to restrict the number of servers that can be spawned by that broker. There is a slight performance gain by segregating the port ranges. There is a savings of 6 network type system calls. The secondary broker uses 1 one of the -Mn (Specify the maximum number of remote client servers that a broker can start) to ID itself. I suggest using a -Mi of MORE THAN 1 for SQL. Since SQL is threaded it performs better as threads are cheaper than processesd. Some groups of parameters define ‘database access and shared resources’ - DB server performance parameters - DB server internationalization parameters - DB server statistics parameters They are common to all brokers and servers running on a single physical database They are set by the first broker started on the database One group of parameters defines ‘database broker resources’ - The sever network parameters They define the 'client access resources' shared by all servers spawned from the broker In this group you can also find some specific parameters linked to the Admin. Service and java class path for SQL-92 The DB server type parameters allow to start either: An Auto Server (-m1 – This is used internally by PROGRESS) A single DB server (-m2) A secondary login broker (-m3) Helps to separate administration task for SQL-92 and 4GL. NOTE: for a list of the parameters that are in each group, please check the manual called "progress startup command and parameter reference" section 3.4 progress database server parameters -Mi minimum number of clients per server -Ma - maximum number of clients per server -ServerTtype specifies that only clients of that type may connect to servers spawned by that broker and is case sensitive. -Mpb is to restrict the number of servers that can be spawned by that broker. There is a slight performance gain by segregating the port ranges. There is a savings of 6 network type system calls. The secondary broker uses 1 one of the -Mn (Specify the maximum number of remote client servers that a broker can start) to ID itself. I suggest using a -Mi of MORE THAN 1 for SQL. Since SQL is threaded it performs better as threads are cheaper than processesd. Some groups of parameters define ‘database access and shared resources’ - DB server performance parameters - DB server internationalization parameters - DB server statistics parameters They are common to all brokers and servers running on a single physical database They are set by the first broker started on the database One group of parameters defines ‘database broker resources’ - The sever network parameters They define the 'client access resources' shared by all servers spawned from the broker In this group you can also find some specific parameters linked to the Admin. Service and java class path for SQL-92 The DB server type parameters allow to start either: An Auto Server (-m1 – This is used internally by PROGRESS) A single DB server (-m2) A secondary login broker (-m3) Helps to separate administration task for SQL-92 and 4GL. NOTE: for a list of the parameters that are in each group, please check the manual called "progress startup command and parameter reference" section 3.4 progress database server parameters

14. Security Identify who I am Validate I am who I say I am

15. Database authentication Performed at connection SQL User ID and password required to authenticate ABL User ID and password NOT required Security

16. 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 Trusted userTrusted user

17. 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) Either case there are 2 default DBAs, SYSPROGRESS and <db-owner>. Either case there are 2 default DBAs, SYSPROGRESS and <db-owner>.

18. 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 Table owner – unless DBA revokes DBA account can be <db-owner> or SYSPROGRESS by default , recommend changing this. Make sure to compare to ABL Remember the previous error. How come we don’t return: not authorized? Table owner – unless DBA revokes DBA account can be <db-owner> or SYSPROGRESS by default , recommend changing this. Make sure to compare to ABL Remember the previous error. How come we don’t return: not authorized?

19. Comparing ABL & SQL Security Systems If we compare the two client’s security systems, you will notice some interesting information. I’ll let you read through the details later, but the highlights are: To many people’s surprise, the ABL core’s authorization implementation employs the GRANT model the same as SQL. Implementation wise in the ABL, no database connection user-id has access unless explicitly granted it. The second is that SQL employs the traditional definition of DBA, which the ABL does not. However, the ABL security system has a defined Security Administrator who is limited to managing user accounts and the data those user accounts can access. The primary difference between ABL and SQL is that the ABL’s defaults are made for a development environment where PUBLIC access is granted to everyone to the entire database. SQL employs a more production site approach in that it denies access to all until it is granted. ABL developers need set any security, but they need to configure security for the production sites. SQL developers need to set security in the development environment but is securely configured for production sites. If we compare the two client’s security systems, you will notice some interesting information. I’ll let you read through the details later, but the highlights are: To many people’s surprise, the ABL core’s authorization implementation employs the GRANT model the same as SQL. Implementation wise in the ABL, no database connection user-id has access unless explicitly granted it. The second is that SQL employs the traditional definition of DBA, which the ABL does not. However, the ABL security system has a defined Security Administrator who is limited to managing user accounts and the data those user accounts can access. The primary difference between ABL and SQL is that the ABL’s defaults are made for a development environment where PUBLIC access is granted to everyone to the entire database. SQL employs a more production site approach in that it denies access to all until it is granted. ABL developers need set any security, but they need to configure security for the production sites. SQL developers need to set security in the development environment but is securely configured for production sites.

20. Encountering data access errors Possible reasons for this: No authorization privileges Schema scope Discuss schema PUB schema Column not found Discuss schema PUB schema Column not found

21. Authorization – What can I do? Database wide (system admin or general creation) RESOURCE Allows the specified users to issue CREATE statements. DBA Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges. TO username [ , username ] , ... Grants the specified privileges on the table or view to the specified list of users. SELECT Allows the specified users to read data from the table or view. INSERT Allows the specified users to add new rows to the table or view. DELETE Allows the specified users to delete rows from the table or view. INDEX Allows the specified users to create an index on the table or view. UPDATE [ ( column , column , ... ) ] Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named. REFERENCES [ ( column , column , ... ) ] Allows the specified users to refer to the table from other tables’ constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named. CREATE USER ‘name’,’pass’RESOURCE Allows the specified users to issue CREATE statements. DBA Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges. TO username [ , username ] , ... Grants the specified privileges on the table or view to the specified list of users. SELECT Allows the specified users to read data from the table or view. INSERT Allows the specified users to add new rows to the table or view. DELETE Allows the specified users to delete rows from the table or view. INDEX Allows the specified users to create an index on the table or view. UPDATE [ ( column , column , ... ) ] Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named. REFERENCES [ ( column , column , ... ) ] Allows the specified users to refer to the table from other tables’ constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named. CREATE USER ‘name’,’pass’

22. Authorization – What can I do? Database wide (system admin or general creation) RESOURCE Allows the specified users to issue CREATE statements. DBA Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges. TO username [ , username ] , ... Grants the specified privileges on the table or view to the specified list of users. SELECT Allows the specified users to read data from the table or view. INSERT Allows the specified users to add new rows to the table or view. DELETE Allows the specified users to delete rows from the table or view. INDEX Allows the specified users to create an index on the table or view. UPDATE [ ( column , column , ... ) ] Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named. REFERENCES [ ( column , column , ... ) ] Allows the specified users to refer to the table from other tables’ constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named. CREATE USER ‘name’,’pass’RESOURCE Allows the specified users to issue CREATE statements. DBA Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges. TO username [ , username ] , ... Grants the specified privileges on the table or view to the specified list of users. SELECT Allows the specified users to read data from the table or view. INSERT Allows the specified users to add new rows to the table or view. DELETE Allows the specified users to delete rows from the table or view. INDEX Allows the specified users to create an index on the table or view. UPDATE [ ( column , column , ... ) ] Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named. REFERENCES [ ( column , column , ... ) ] Allows the specified users to refer to the table from other tables’ constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named. CREATE USER ‘name’,’pass’

23. Authorization – What can I do? For specified Tables or Views - Example See PSDN whitepaper on authorization for additional details. RESOURCE Allows the specified users to issue CREATE statements. DBA Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges. TO username [ , username ] , ... Grants the specified privileges on the table or view to the specified list of users. SELECT Allows the specified users to read data from the table or view. INSERT Allows the specified users to add new rows to the table or view. DELETE Allows the specified users to delete rows from the table or view. INDEX Allows the specified users to create an index on the table or view. UPDATE [ ( column , column , ... ) ] Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named. REFERENCES [ ( column , column , ... ) ] Allows the specified users to refer to the table from other tables’ constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named. CREATE USER ‘name’,’pass’RESOURCE Allows the specified users to issue CREATE statements. DBA Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges. TO username [ , username ] , ... Grants the specified privileges on the table or view to the specified list of users. SELECT Allows the specified users to read data from the table or view. INSERT Allows the specified users to add new rows to the table or view. DELETE Allows the specified users to delete rows from the table or view. INDEX Allows the specified users to create an index on the table or view. UPDATE [ ( column , column , ... ) ] Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named. REFERENCES [ ( column , column , ... ) ] Allows the specified users to refer to the table from other tables’ constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named. CREATE USER ‘name’,’pass’

24. Encountering data access errors Possible reasons for this: No authorization privileges Schema scope Discuss schema PUB schema Column not found Discuss schema PUB schema Column not found

25. Schema: a logical grouping Schema – logical grouping within a databaseSchema – logical grouping within a database

26. 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 PUBLIC indicates available to all Can set it as a registry entry in ODBC dsn defnition. Also, note that most application will generate full identifiers, including schema.PUBLIC indicates available to all Can set it as a registry entry in ODBC dsn defnition. Also, note that most application will generate full identifiers, including schema.

27. Solving data access errors Discuss schema PUB schema Column not found Discuss schema PUB schema Column not found

28. Four level naming convention 4 Part Naming – Multi-Database Query When connected to auxiliary databases, use the catalog name with OpenEdge SQL query commands to qualify the database to which the query applies. The Primary database is automatically assigned a catalog name the same as the database name. OpenEdge Releases prior to 10.1B support 2 and 3 level names, for schema, tables (and stored procedures) and columns respectively, when referenced. Release 10.1B extends this naming to support 3 and 4 level naming (as defined in the SQL standard) with the addition of the catalog name naming-level. Names, according to the standard SQL syntax, are composed of 1, 2, 3, or 4 components, as described below: Catalog – a catalog is a named collection of schemas. In OpenEdge, a catalog logically corresponds to an Open Edge database. Schema – a named collection of table and other database objects. Tables Columns Tables have up to 3 components or levels: catalog.schema.tablename. Columns have up to 4 components or levels: catalog.schema.table.column-name. Similarly, a stored procedure can be referred to as “catalog.schema.proc-name”. This naming system is supported by many tools, such as Crystal, by competitors such as Microsoft SQL Server, and by related standards such as ODBC and JDBC. Catalog names cannot be used on DDL statements, such as ALTER or CREATE. Catalog names cannot be used with sequences. Synonyms: A public synonym, used without a qualifier, must exist in the set of synonyms defined for the primary database. A public synonym may be qualified with a catalog name, with or without a schema name, in which case the synonym must exist in the set of synonyms defined for the specified catalog. A private synonym must always be qualified by at least the schema name of the schema where that synonym exists. A private synonym, used with only a schema qualifier, must exist in the set of synonyms defined for the primary database. Stored procedures: A stored procedure in an auxiliary catalog can be called by explicitly qualifying the procedure name by the catalog name. The SQL statements executed from within a stored procedure are interpreted in light of the multiple catalogs active. Consider a stored procedure “order_parts” stored in an auxiliary database. Tables referenced from statements in “order_parts”, if they are not qualified by a catalog name, will refer to tables in the default catalog (the Primary database) and NOT the auxiliary database. This may not be what was intended. To avoid such problems, fully qualify table names in stored procedures. call mysports.pub.order_parts(cItemCode) When connected to auxiliary databases, use the catalog name with OpenEdge SQL query commands to qualify the database to which the query applies. The Primary database is automatically assigned a catalog name the same as the database name. OpenEdge Releases prior to 10.1B support 2 and 3 level names, for schema, tables (and stored procedures) and columns respectively, when referenced. Release 10.1B extends this naming to support 3 and 4 level naming (as defined in the SQL standard) with the addition of the catalog name naming-level. Names, according to the standard SQL syntax, are composed of 1, 2, 3, or 4 components, as described below: Catalog – a catalog is a named collection of schemas. In OpenEdge, a catalog logically corresponds to an Open Edge database. Schema – a named collection of table and other database objects. Tables Columns Tables have up to 3 components or levels: catalog.schema.tablename. Columns have up to 4 components or levels: catalog.schema.table.column-name. Similarly, a stored procedure can be referred to as “catalog.schema.proc-name”. This naming system is supported by many tools, such as Crystal, by competitors such as Microsoft SQL Server, and by related standards such as ODBC and JDBC. Catalog names cannot be used on DDL statements, such as ALTER or CREATE. Catalog names cannot be used with sequences. Synonyms: A public synonym, used without a qualifier, must exist in the set of synonyms defined for the primary database. A public synonym may be qualified with a catalog name, with or without a schema name, in which case the synonym must exist in the set of synonyms defined for the specified catalog. A private synonym must always be qualified by at least the schema name of the schema where that synonym exists. A private synonym, used with only a schema qualifier, must exist in the set of synonyms defined for the primary database. Stored procedures: A stored procedure in an auxiliary catalog can be called by explicitly qualifying the procedure name by the catalog name. The SQL statements executed from within a stored procedure are interpreted in light of the multiple catalogs active. Consider a stored procedure “order_parts” stored in an auxiliary database. Tables referenced from statements in “order_parts”, if they are not qualified by a catalog name, will refer to tables in the default catalog (the Primary database) and NOT the auxiliary database. This may not be what was intended. To avoid such problems, fully qualify table names in stored procedures. call mysports.pub.order_parts(cItemCode)

29. Four level naming convention Example 4 Part Naming – Multi-Database Query When connected to auxiliary databases, use the catalog name with OpenEdge SQL query commands to qualify the database to which the query applies. The Primary database is automatically assigned a catalog name the same as the database name. OpenEdge Releases prior to 10.1B support 2 and 3 level names, for schema, tables (and stored procedures) and columns respectively, when referenced. Release 10.1B extends this naming to support 3 and 4 level naming (as defined in the SQL standard) with the addition of the catalog name naming-level. Names, according to the standard SQL syntax, are composed of 1, 2, 3, or 4 components, as described below: Catalog – a catalog is a named collection of schemas. In OpenEdge, a catalog logically corresponds to an Open Edge database. Schema – a named collection of table and other database objects. Tables Columns Tables have up to 3 components or levels: catalog.schema.tablename. Columns have up to 4 components or levels: catalog.schema.table.column-name. Similarly, a stored procedure can be referred to as “catalog.schema.proc-name”. This naming system is supported by many tools, such as Crystal, by competitors such as Microsoft SQL Server, and by related standards such as ODBC and JDBC. Catalog names cannot be used on DDL statements, such as ALTER or CREATE. Catalog names cannot be used with sequences. Synonyms: A public synonym, used without a qualifier, must exist in the set of synonyms defined for the primary database. A public synonym may be qualified with a catalog name, with or without a schema name, in which case the synonym must exist in the set of synonyms defined for the specified catalog. A private synonym must always be qualified by at least the schema name of the schema where that synonym exists. A private synonym, used with only a schema qualifier, must exist in the set of synonyms defined for the primary database. Stored procedures: A stored procedure in an auxiliary catalog can be called by explicitly qualifying the procedure name by the catalog name. The SQL statements executed from within a stored procedure are interpreted in light of the multiple catalogs active. Consider a stored procedure “order_parts” stored in an auxiliary database. Tables referenced from statements in “order_parts”, if they are not qualified by a catalog name, will refer to tables in the default catalog (the Primary database) and NOT the auxiliary database. This may not be what was intended. To avoid such problems, fully qualify table names in stored procedures. call mysports.pub.order_parts(cItemCode) When connected to auxiliary databases, use the catalog name with OpenEdge SQL query commands to qualify the database to which the query applies. The Primary database is automatically assigned a catalog name the same as the database name. OpenEdge Releases prior to 10.1B support 2 and 3 level names, for schema, tables (and stored procedures) and columns respectively, when referenced. Release 10.1B extends this naming to support 3 and 4 level naming (as defined in the SQL standard) with the addition of the catalog name naming-level. Names, according to the standard SQL syntax, are composed of 1, 2, 3, or 4 components, as described below: Catalog – a catalog is a named collection of schemas. In OpenEdge, a catalog logically corresponds to an Open Edge database. Schema – a named collection of table and other database objects. Tables Columns Tables have up to 3 components or levels: catalog.schema.tablename. Columns have up to 4 components or levels: catalog.schema.table.column-name. Similarly, a stored procedure can be referred to as “catalog.schema.proc-name”. This naming system is supported by many tools, such as Crystal, by competitors such as Microsoft SQL Server, and by related standards such as ODBC and JDBC. Catalog names cannot be used on DDL statements, such as ALTER or CREATE. Catalog names cannot be used with sequences. Synonyms: A public synonym, used without a qualifier, must exist in the set of synonyms defined for the primary database. A public synonym may be qualified with a catalog name, with or without a schema name, in which case the synonym must exist in the set of synonyms defined for the specified catalog. A private synonym must always be qualified by at least the schema name of the schema where that synonym exists. A private synonym, used with only a schema qualifier, must exist in the set of synonyms defined for the primary database. Stored procedures: A stored procedure in an auxiliary catalog can be called by explicitly qualifying the procedure name by the catalog name. The SQL statements executed from within a stored procedure are interpreted in light of the multiple catalogs active. Consider a stored procedure “order_parts” stored in an auxiliary database. Tables referenced from statements in “order_parts”, if they are not qualified by a catalog name, will refer to tables in the default catalog (the Primary database) and NOT the auxiliary database. This may not be what was intended. To avoid such problems, fully qualify table names in stored procedures. call mysports.pub.order_parts(cItemCode)

30. Four level naming convention Example ABL has 3 level naming convention 4 Part Naming – Multi-Database Query When connected to auxiliary databases, use the catalog name with OpenEdge SQL query commands to qualify the database to which the query applies. The Primary database is automatically assigned a catalog name the same as the database name. OpenEdge Releases prior to 10.1B support 2 and 3 level names, for schema, tables (and stored procedures) and columns respectively, when referenced. Release 10.1B extends this naming to support 3 and 4 level naming (as defined in the SQL standard) with the addition of the catalog name naming-level. Names, according to the standard SQL syntax, are composed of 1, 2, 3, or 4 components, as described below: Catalog – a catalog is a named collection of schemas. In OpenEdge, a catalog logically corresponds to an Open Edge database. Schema – a named collection of table and other database objects. Tables Columns Tables have up to 3 components or levels: catalog.schema.tablename. Columns have up to 4 components or levels: catalog.schema.table.column-name. Similarly, a stored procedure can be referred to as “catalog.schema.proc-name”. This naming system is supported by many tools, such as Crystal, by competitors such as Microsoft SQL Server, and by related standards such as ODBC and JDBC. Catalog names cannot be used on DDL statements, such as ALTER or CREATE. Catalog names cannot be used with sequences. Synonyms: A public synonym, used without a qualifier, must exist in the set of synonyms defined for the primary database. A public synonym may be qualified with a catalog name, with or without a schema name, in which case the synonym must exist in the set of synonyms defined for the specified catalog. A private synonym must always be qualified by at least the schema name of the schema where that synonym exists. A private synonym, used with only a schema qualifier, must exist in the set of synonyms defined for the primary database. Stored procedures: A stored procedure in an auxiliary catalog can be called by explicitly qualifying the procedure name by the catalog name. The SQL statements executed from within a stored procedure are interpreted in light of the multiple catalogs active. Consider a stored procedure “order_parts” stored in an auxiliary database. Tables referenced from statements in “order_parts”, if they are not qualified by a catalog name, will refer to tables in the default catalog (the Primary database) and NOT the auxiliary database. This may not be what was intended. To avoid such problems, fully qualify table names in stored procedures. call mysports.pub.order_parts(cItemCode) When connected to auxiliary databases, use the catalog name with OpenEdge SQL query commands to qualify the database to which the query applies. The Primary database is automatically assigned a catalog name the same as the database name. OpenEdge Releases prior to 10.1B support 2 and 3 level names, for schema, tables (and stored procedures) and columns respectively, when referenced. Release 10.1B extends this naming to support 3 and 4 level naming (as defined in the SQL standard) with the addition of the catalog name naming-level. Names, according to the standard SQL syntax, are composed of 1, 2, 3, or 4 components, as described below: Catalog – a catalog is a named collection of schemas. In OpenEdge, a catalog logically corresponds to an Open Edge database. Schema – a named collection of table and other database objects. Tables Columns Tables have up to 3 components or levels: catalog.schema.tablename. Columns have up to 4 components or levels: catalog.schema.table.column-name. Similarly, a stored procedure can be referred to as “catalog.schema.proc-name”. This naming system is supported by many tools, such as Crystal, by competitors such as Microsoft SQL Server, and by related standards such as ODBC and JDBC. Catalog names cannot be used on DDL statements, such as ALTER or CREATE. Catalog names cannot be used with sequences. Synonyms: A public synonym, used without a qualifier, must exist in the set of synonyms defined for the primary database. A public synonym may be qualified with a catalog name, with or without a schema name, in which case the synonym must exist in the set of synonyms defined for the specified catalog. A private synonym must always be qualified by at least the schema name of the schema where that synonym exists. A private synonym, used with only a schema qualifier, must exist in the set of synonyms defined for the primary database. Stored procedures: A stored procedure in an auxiliary catalog can be called by explicitly qualifying the procedure name by the catalog name. The SQL statements executed from within a stored procedure are interpreted in light of the multiple catalogs active. Consider a stored procedure “order_parts” stored in an auxiliary database. Tables referenced from statements in “order_parts”, if they are not qualified by a catalog name, will refer to tables in the default catalog (the Primary database) and NOT the auxiliary database. This may not be what was intended. To avoid such problems, fully qualify table names in stored procedures. call mysports.pub.order_parts(cItemCode)

31. OpenEdge Specifics SQL is a standard, but each vendor has it’s own dialect

32. OpenEdge SQL Specifics - Quoting Hyphenated names: Most reporting apps will do this for you alreadyMost reporting apps will do this for you already

33. Overstuffed fields - error ABL allows more data than column definition SQL restricted to _field._sql-width value ALTER TABLE <table name> ALTER COLUMN <column name> SET PRO_SQL_WIDTH  <value>;or Connect to the Database via the Progress Data Dictionary and choose menu - Options - SQL Properties - Adjust Field Width.ALTER TABLE <table name> ALTER COLUMN <column name> SET PRO_SQL_WIDTH  <value>;

34. OpenEdge Specifics - Overstuffed fields Strategies for managing: Dbtool : percentage option ($DLC/bin/dbtool) Performance impact – more memory allocation on client and server side for result sets Tool is online. Storage wise it’s the same, because everything is packed format. 25% the first time, Probably don’t want 25% each time after that. Using the DBTool utility The DBTool utility allows Progress users to identify when the size of column data in the database exceeds the Data Dictionary definition and therefore the SQLWidth value. The DBTool utility addresses this situation because it allows for the fast updating of Data Dictionary SQLWidth definitions. The following error message is reported to a SQL application when the SQLWidth for a column exceeds the Data Dictionary SQLWidth definition: Column column in table table has value exceeding its max length or precision (7864) The syntax for DBTool is: Syntax dbtool dbname To access DBTool from the command line: Type dbtool and the database name and press Enter. The DBTool option menu appears: Select an option from the menu and press Enter. Table 7–2 describes the options available in the DBTool option menu. Table 7–2: DBTool option menu Option Description 1 Finds the maximum field sizes and reports them. 2 Finds the maximum field sizes and updates their widths. 3 Validates the schema versioning of the records after the records are updated by DBTool. 4 Validates the schema versioning before and after the records are updated in DBTool. 5 Validates db keys while scanning database blocks. 9 Enables or disables file logging. Q Quits the DBTool utility. For more information on the DBTool utility, see OpenEdge Data Management: Database Administration. Performance impact – more memory allocation on client and server side for result sets Tool is online. Storage wise it’s the same, because everything is packed format. 25% the first time, Probably don’t want 25% each time after that. Using the DBTool utility The DBTool utility allows Progress users to identify when the size of column data in the database exceeds the Data Dictionary definition and therefore the SQLWidth value. The DBTool utility addresses this situation because it allows for the fast updating of Data Dictionary SQLWidth definitions. The following error message is reported to a SQL application when the SQLWidth for a column exceeds the Data Dictionary SQLWidth definition: Column column in table table has value exceeding its max length or precision (7864) The syntax for DBTool is: Syntax dbtool dbname To access DBTool from the command line: Type dbtool and the database name and press Enter. The DBTool option menu appears: Select an option from the menu and press Enter. Table 7–2 describes the options available in the DBTool option menu. Table 7–2: DBTool option menu Option Description 1 Finds the maximum field sizes and reports them. 2 Finds the maximum field sizes and updates their widths. 3 Validates the schema versioning of the records after the records are updated by DBTool. 4 Validates the schema versioning before and after the records are updated in DBTool. 5 Validates db keys while scanning database blocks. 9 Enables or disables file logging. Q Quits the DBTool utility. For more information on the DBTool utility, see OpenEdge Data Management: Database Administration.

35. OpenEdge Specifics - Overstuffed fields Strategies for managing ABL client startup parameter Using the -checkwidth startup parameter OpenEdge 4GL programmers can override Data Dictionary column definitions in 4GL programs. Therefore, a SQL application cannot read a record if a column contains data greater than the SQLWidth value defined in the Data Dictionary. An attempt to retrieve a column that exceeds the SQLWidth definition generates an error message, and the attempt to access the record fails. Use the -checkwidth startup parameter to specify whether Progress compares CHARACTER, DECIMAL, and RAW field data against the metaschema _width field value before updating a database record. The _width field value specifies the maximum width of the data allowed in a field. The syntax for the -checkwidth startup parameter is: Syntax -checkwidth n The -checkwidth startup parameter can be employed in the following modes: 0 — Ignore the _width field value and store the data. This is the default mode. 1 — Store the data and generate a warning message if the data exceeds the size specified in the _width field. 2 — Do not store data that exceeds the size specified in the _width field and generate an error. Specify this mode if you want the Progress 4GL to behave like SQL. For more information on the -checkwidth startup parameter, see OpenEdge Deployment: Startup Command and Parameter Reference. Using the -checkwidth startup parameter OpenEdge 4GL programmers can override Data Dictionary column definitions in 4GL programs. Therefore, a SQL application cannot read a record if a column contains data greater than the SQLWidth value defined in the Data Dictionary. An attempt to retrieve a column that exceeds the SQLWidth definition generates an error message, and the attempt to access the record fails. Use the -checkwidth startup parameter to specify whether Progress compares CHARACTER, DECIMAL, and RAW field data against the metaschema _width field value before updating a database record. The _width field value specifies the maximum width of the data allowed in a field. The syntax for the -checkwidth startup parameter is: Syntax -checkwidth n The -checkwidth startup parameter can be employed in the following modes: 0 — Ignore the _width field value and store the data. This is the default mode. 1 — Store the data and generate a warning message if the data exceeds the size specified in the _width field. 2 — Do not store data that exceeds the size specified in the _width field and generate an error. Specify this mode if you want the Progress 4GL to behave like SQL. For more information on the -checkwidth startup parameter, see OpenEdge Deployment: Startup Command and Parameter Reference.

36. 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 Array syntax[] – same as ABL No ODBC support? No jdbc support? Array syntax[] – same as ABL No ODBC support? No jdbc support?

37. OpenEdge Specifics – Arrays / Extents Using views to break out array element See whitepaper on PSDN Needed for Crystal to present Arrays as individual elements.See whitepaper on PSDN Needed for Crystal to present Arrays as individual elements.

38. Q: What is it going to cost to run my query? Query Performance The OpenEdge SQL Engine contains a query optimizer that analyzes SQL queries and produces a plan for how SQL should best execute the query. The plan contains information such as which tables to access, in what order, and with which indexes. To produce a good query plan, the optimizer analyzes the query and considers many methods for each query execution step. The OpenEdge SQL Engine contains a query optimizer that analyzes SQL queries and produces a plan for how SQL should best execute the query. The plan contains information such as which tables to access, in what order, and with which indexes. To produce a good query plan, the optimizer analyzes the query and considers many methods for each query execution step.

39. Q: What is it going to cost to run my query? Query Performance The OpenEdge SQL Engine contains a query optimizer that analyzes SQL queries and produces a plan for how SQL should best execute the query. The plan contains information such as which tables to access, in what order, and with which indexes. To produce a good query plan, the optimizer analyzes the query and considers many methods for each query execution step. The OpenEdge SQL Engine contains a query optimizer that analyzes SQL queries and produces a plan for how SQL should best execute the query. The plan contains information such as which tables to access, in what order, and with which indexes. To produce a good query plan, the optimizer analyzes the query and considers many methods for each query execution step.

40. Basic Performance: What is the cost? No pre-selected index. At runtime, it decides based on data profile, which index or not is best to use based on “cost”. Optimizer makes decisions based on the statistcis data profile. replace with BAR Graph No pre-selected index. At runtime, it decides based on data profile, which index or not is best to use based on “cost”. Optimizer makes decisions based on the statistcis data profile. replace with BAR Graph

41. Basic Performance: Here’s the cost. No pre-selected index. At runtime, it decides based on data profile, which index or not is best to use based on “cost”. Optimizer makes decisions based on the statistcis data profile. replace with BAR Graph No pre-selected index. At runtime, it decides based on data profile, which index or not is best to use based on “cost”. Optimizer makes decisions based on the statistcis data profile. replace with BAR Graph

42. All Statistics: Table Cardinality, indexes and all columns Query Performance: Update Statistics How often? 20% data change – either adding new data or changing existing data How often? 20% data change – either adding new data or changing existing data

43. Query trees : Defined relational algebraic tree representation (query tree / execution tree ) What are query trees What’s in a query – breakdown of a statement ( Define project, restrict, join) What do query trees look like Inverted tree, ‘tree in New England’ The query processor makes extensive use of the relational algebraic tree representation to model and manipulate SQL queries. These trees can be thought of as a series of pipes, valves, and other components through which data flows, entering through the bottom from one or more tables, and leaving through the top as a result set. At various points within the tree, the data are operated on as needed to produce the desired result. Each operation is represented as a node in the tree. Nodes may also have one or more expressions associated with them to specify columns, conditions, and calculations associated with the operation. Some of the operators that may be present in the tree are: Restrictions reduce the number of output rows by eliminating those that fail to satisfy some condition applied to the input. Restrict operators appear in the tree from predicates (WHERE clauses). Projections reduce the number of output columns by eliminating columns not present in a project list. Projection operators appear in the tree from SELECT statements. Joins combine two or more input tables into a single output table that contains some combination of rows from the inputs. Joins appear in the tree from the use of FROM clauses. Sorts change the ordering of rows in an input table to produce an output table in the desired order. Leaf nodes of the tree are always references to database tables. What are query trees What’s in a query – breakdown of a statement ( Define project, restrict, join) What do query trees look like Inverted tree, ‘tree in New England’ The query processor makes extensive use of the relational algebraic tree representation to model and manipulate SQL queries. These trees can be thought of as a series of pipes, valves, and other components through which data flows, entering through the bottom from one or more tables, and leaving through the top as a result set. At various points within the tree, the data are operated on as needed to produce the desired result. Each operation is represented as a node in the tree. Nodes may also have one or more expressions associated with them to specify columns, conditions, and calculations associated with the operation. Some of the operators that may be present in the tree are: Restrictions reduce the number of output rows by eliminating those that fail to satisfy some condition applied to the input. Restrict operators appear in the tree from predicates (WHERE clauses). Projections reduce the number of output columns by eliminating columns not present in a project list. Projection operators appear in the tree from SELECT statements. Joins combine two or more input tables into a single output table that contains some combination of rows from the inputs. Joins appear in the tree from the use of FROM clauses. Sorts change the ordering of rows in an input table to produce an output table in the desired order. Leaf nodes of the tree are always references to database tables.

44. Basic Performance: Viewing Query Plans Query Plans Located in VST “_Sql_Qplan” Create a view to make your life simpler… Select * from my_qplan; Add application query first hard coded VST , not browseable can also do online query plan tracing for whole server- using logging mechanism Create a view to make your life simpler… Select * from my_qplan; Add application query first hard coded VST , not browseable can also do online query plan tracing for whole server- using logging mechanism

45. Simple single table select SELECT … FROM pub.customer WHERE “cust-num” between 1000 and 1100 [ NoExecute ] Query plan: What to look for Simple indentation to show tree form. Find and consider the highlights shown. Ignore the Project operations, and other less important operations.Simple indentation to show tree form. Find and consider the highlights shown. Ignore the Project operations, and other less important operations.

46. Simple single table select SELECT … FROM pub.customer WHERE ”cust-num” between 3000 and 3100 [ NoExecute ] Query plan: What to look for Simple indentation to show tree form. Find and consider the highlights shown. Ignore the Project operations, and other less important operations.Simple indentation to show tree form. Find and consider the highlights shown. Ignore the Project operations, and other less important operations.

47. In Summary Initial SQL connection Setup and maintenance in OpenEdge database for security and performance Specifics of OpenEdge with SQL applications

48. 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 TS Knowledge base Security – Mike Jacob’s talk TS Knowledge base Security – Mike Jacob’s talk

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

51. Thank you in many languages Thank you in many languages