Article

SQL Query plan references _sec-granted-role table on auditing enabled db

Information

 
Article Number000083412
EnvironmentProduct: OpenEdge
Version: 10.1x, 10.2x, 11.x
OS: Windows
Question/Problem Description
Executing a query to get a query plan from an auditing enabled database returns a query plan for the _sec-granted-role table
Executing a query against an auditing enabled database from .NET OpenClient thru the ODBC driver, then calling for a query plan, the plan that is returned to the .NET client references the _sec-granted-role table.

Generating a SQL query plan on an audit enabled database using a C# client (connecting to the database via ODBC) returns the following output
1..SELECT COMMAND.
1..PROJECT [21] (
1..|   PROJECT [20] (
1..|   |   PROJECT [18] (
1..|   |   |   PUB._sec-granted-role. [1](
1..|   |   |   |   INDEX SCAN OF (
1..|   |   |   |   |   _Grantee,
1..|   |   |   |   |   |   (PUB._sec-granted-role._Grantee) = (PARAM1)
1..|   |   |   |   |   |   (PUB._sec-granted-role._Role-name) = (PARAM0))
1..|   |   |   )
1..|   |   , PUB._sec-granted-role._Role-name
1..|   |   )
1..|   , count (*) 
1..|   )
1.., PEXPR1
1..)
Steps to Reproduce
Clarifying Information
Code used to generate the SQL Query plan:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
using System.Diagnostics;

        private void queryData
        {
            String cDSN = "<ODBC DSN name>";
            String cUserID = "<Userid for connection>";
            String cPassword = "<Password>";

            String cConnectionString = @"DSN=" + cDSN +
                   ";UID=" + cUserID + ";PWD=" + cPassword;

            try
            {
                OdbcConnection oConnection = new OdbcConnection(cConnectionString);
                oConnection.Open();

                ShowData(oConnection);
                Console.Read();
            }
            catch (Exception oError)
            {
                MessageBox.Show("Failed to connect to database" + Environment.NewLine + oError.Message);
            }
                
        }

        void ShowData(OdbcConnection poConnection)
        {
            String cSQL = @"select emp.firstname, emp.lastname, dpt.deptcode
                        from pub.employee emp
                        inner join pub.department dpt on emp.deptcode = dpt.deptcode";

            try
            {
                OdbcCommand oCommand = new OdbcCommand(cSQL, poConnection);
                OdbcDataReader oReader = oCommand.ExecuteReader();

                while (oReader.Read())
                {
                }
            }
            catch (Exception oError)
            {
                MessageBox.Show("Failed to read data" + Environment.NewLine + oError.Message);
            }
                        
            ShowPlan(connection);
        }

        void ShowPlan(OdbcConnection poConnection)
        {
            String cSQL = @"SELECT ""_Pnumber"", SUBSTRING(""_Description"",1,80) 
                            FROM pub.""_Sql_Qplan"" WHERE ""_Pnumber"" = (SELECT MAX( ""_Pnumber"" ) 
                            FROM pub.""_Sql_Qplan"" WHERE ""_Ptype"" > 0 )";

            try
            {
                OdbcCommand oCommand = new OdbcCommand(cSQL, poConnection);
                OdbcDataReader oReader = oCommand.ExecuteReader();
                while (oReader.Read())
                {
                    Debug.Print("{0}..{1}", reader[0].ToString(), reader[1].ToString());
                }
            }
            catch (Exception oError)
            {
                MessageBox.Show("Failed to execute query plan" + Environment.NewLine + oError.Message);
            }
        }

The SQL Query plan expected to be generated by the above code follows:
PROJECT [27] (
|   JOIN [9][AUG_NESTED_LOOP-JOIN]
|   |   [RHS-SORTED(-ASC-DUPS)  ](
|   |   PROJECT [22] (
|   |   |   PUB.EMP. [3](
|   |   |   |   TABLE SCAN
|   |   |   )
|   |   , PUB.EMP.FirstName
|   |   , PUB.EMP.LastName
|   |   , PUB.EMP.DeptCode
|   |   , PUB.EMP.rowid

|   |   )
|   ,
|   |   (PEXPR3) = (PEXPR5)
|   |   -- above defines ANL  left side keys <relop> right side keys.
|   ,
|   |   PROJECT [25] (
|   |   |   PUB.DPT. [4](
|   |   |   |   INDEX SCAN OF (
|   |   |   |   |   DeptCode,
|   |   |   |   |   |   (PUB.DPT.DeptCode) = (null))
|   |   |   )
|   |   , PUB.DPT.DeptCode
|   |   , PUB.DPT.rowid
|   |   )
|   )
, PEXPR1
, PEXPR2
, PEXPR5
)
Error Message
Defect/Enhancement Number
Cause
The reason for this lies in the program itself:
  • The OdbcDatareader objects were not closed after its use.
  • For further clarification refer to :
Docs > .NET > .NET API Browser > System.Data.Odbc > OdbcDataReader > Methods > Close > OdbcDataReader.Close Method

 
Resolution
Explicitly call the OdbcDataReader.Close Method when you are finished using the OdbcDataReader to use the associated OdbcConnection for any other purpose.
 
Workaround
Use Query_Plan logging from sql explorer:
proenv>sqlexp -db <dbname> -H <hostname|IP> -S <portnum> -user <userid> -pass <password>

SQLExplorer>set PRO_SERVER LOG ON WITH (QUERY_PLAN);
SQLExplorer>quit;

Once the query in question has executed look for log files named like below with a recent timestamp.  The query plan should have been generated into one of them:
  • SQL_server_<n>_<date>_<time>_A.log
For further information refer to Article 000011403, SQL-92: How to turn SQL statement logging on and off?
 
Notes

 
Attachment 
Last Modified Date12/18/2018 8:46 AM


Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025