1 / 46

OPS-6: Beginners Guide to OpenEdge

Rita
Download Presentation

OPS-6: Beginners Guide to OpenEdge

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. OPS-6: Beginners Guide to OpenEdge® SQL via ODBC or JDBC

    2. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC CLASSPATH ( run ‘sql_env’) Class loader URL JDBC driver Run sql_env V9 – come see me for details Run sql_env V9 – come see me for details

    6. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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

    8. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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

    9. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC ODBC : Multi-DataBase configuration JDBC has same ability, using same means Multiple Databases on same host sports2000.oesql.properties : in database location See PDSN presentation on MDBQ JDBC has same ability, using same means Multiple Databases on same host sports2000.oesql.properties : in database location See PDSN presentation on MDBQ

    10. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Connection – server side

    11. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Default server settings How does this affect you? 4 servers is the default – look at log file for default settings proserve –S port dbname How does this affect you? 4 servers is the default – look at log file for default settings proserve –S port dbname

    12. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC “Recommended” server setup Discuss advantages Discuss advantages

    13. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Recommended parameters example Example: Start a ABL Primary broker Example: Start a Secondary SQL broker -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 1 as this will perform better with fewer servers. 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 1 as this will perform better with fewer servers. 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. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Security Who am I?

    15. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Security Considerations Database authentication SQL Always requires a user ID and password to establish a connection ABL Does not specifically require a user ID and password to establish a connection

    16. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC SQL Authentication (Who am I?) Case 1: Users have not been created (no rows in _User table) Password validation is not enabled No check is performed at connection time No error message at connection time Trusted userTrusted user

    17. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC SQL Authentication (Who am I?) Case 2: Users exist in the OpenEdge RDBMS (rows exist in _User table) Password Validation is enabled Check is performed at connection time Valid users defined by a DBA Error message if login is incorrect / invalid: “Access Denied (8933)” Either case there are 2 default DBAs, SYSPROGRESS and <db-owner>. Either case there are 2 default DBAs, SYSPROGRESS and <db-owner>.

    18. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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 needs 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 needs to set security in the development environment but is securely configured for production sites.

    19. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Encountering errors Possible reason for this: No authorization privileges Schema scope Discuss schema PUB schema Column not found Discuss schema PUB schema Column not found

    20. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Authorization – What can I do? SQL follows GRANT security model By default, a connected userid is not allowed 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?

    21. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Authorization – What can I do? Database wide (system admin or general creation) For specified Tables or Views Where ‘privilege’ is: { SELECT | INSERT | DELETE | INDEX | UPDATE [ ( column , column , ... ) ] | REFERENCES [ ( column , column , ... ) ] } 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. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Encountering errors Possible reasons for this: Not authorized Schema scope Discuss schema PUB schema Column not found Discuss schema PUB schema Column not found

    23. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC What is a Schema? Schema – logical grouping within a databaseSchema – logical grouping within a database

    24. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC What is a default Schema? A user has by default a schema attached to their ID SET SCHEMA { 'string_literal'} OpenEdge ABL uses one schema – ‘PUB’ Another option: Synonyms: Schemas Can set it as a registry entry in ODBC dsn defnition. Also, note that most application will generate full identifiers, including schema.Can set it as a registry entry in ODBC dsn defnition. Also, note that most application will generate full identifiers, including schema.

    25. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Solutions: or Schema example Most reporting application use fully qualified identifiersMost reporting application use fully qualified identifiers

    26. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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)

    27. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC OpenEdge Specifics SQL is a standard, but each vendor has it’s own dialect

    28. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC OpenEdge Specifics - Quoting Hyphenated names: Solution: quoting Most reporting apps will do this for you alreadyMost reporting apps will do this for you already

    29. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Overstuffed fields - error ABL allows more data than column definition 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>;

    30. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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.

    31. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC OpenEdge Specifics - Overstuffed fields Strategies for managing ABL client startup parameter -checkwidth 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.

    32. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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 Result: numeric value 102332.67 Array syntax[] – same as ABL Array syntax[] – same as ABL

    33. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC OpenEdge Specifics – Arrays / Extents Strategies: Using views to break out array elements Result: numeric values 102332.67 330002.77 443434.55 333376.50 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.

    34. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Q: What’s it gonna 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.

    35. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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

    36. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Basic Performance - What is the cost? Big = costly to access a large percentage of data Statistics and cost base optimizer What is it? Why and How to run Update Statistics? Treplace with Bar Graph Big = costly to access a large percentage of data Statistics and cost base optimizer What is it? Why and How to run Update Statistics? Treplace with Bar Graph

    37. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC All Statistics: Table Cardinality, indexes and all columns Statistics - particular table Query Performance: Update Statistics How often? How often?

    38. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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.

    39. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Basic Performance – 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

    40. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Simple single table select “select … from pub.customer where custnum 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.

    41. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC In Summary Initial SQL connection Setup and maintenance in OpenEdge database for security and performance Specifics of OpenEdge with SQL applications

    42. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC 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

    43. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Relevant Exchange Sessions OPS-27: Understanding Record and Table Locking In OpenEdge SQL OPS-10: Moving V8/V9 RDBMS to OpenEdge 10 OPS-15: What was Happening with My Database, AppServer, Operating System OPS-18: Data Management and Platforms Roadmap OPS-24: Success with OpenEdge Replication

    44. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

    45. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC Thank you in many languages Thank you in many languages

    46. OPS-6: Beginners Guide to OpenEdge SQL via ODBC or JDBC

More Related