All Forums General
Abhishek_RC 12 posts Joined 09/14
17 Mar 2015
Reverse engineer rules from dbqlruletbl rows

Hello,

 

We have an existing query logging parameters set for every account in one of the environments (DBC.DBQLRuleTbl). However, we want to make a small change for a specific account_string then revert it back. Is there any way or any sql what will help generate backup of all dbql parmaters set in DBC.DBQLRuleTbl/ DBC.DBQLRulesV. So that when we want to revert back, we will just execute those scripts and things will be back to old settings ?

 

So basically I need a query to reverse engineer the begin query logging statements from the rows in the dbqlruletbl.

 

Thanks

Abhishek

dnoeth 4628 posts Joined 11/04
17 Mar 2015

Hi Abhishek,
I wrote this to get the DBQL commands from DBQLRuleTbl in TD14.10/15, will need some modification for earlier releases:

SELECT
   'BEGIN QUERY LOGGING'
   || CASE WHEN WithFlags = ' WITH' THEN '' ELSE WithFlags END
   || ModeFlag
   || CASE WHEN LimitCount > 0 THEN ' LIMIT' || COALESCE(LimitSQLText, LimitSummary, LimitThreshold) ELSE '' END
   || CASE WHEN LimitCount > 1 THEN ' AND'   || COALESCE(LimitThreshold, LimitSummary, LimitSQLText) ELSE '' END
   || ' ON '
   || CASE WHEN APPLNAME <> '' THEN 'ApplName=''' ||TRIM(APPLNAME) || '''' ELSE TRIM(db.DatabaseName) END
   || CASE WHEN AccountString <> ''  THEN ' ACCOUNT=' || '''' || TRIM(AccountString) || '''' ELSE '' END
   || ';' AS DBQL_SQL
FROM
 (
   SELECT
--      ExtraField12 AS DBQLOptions,                    -- uncomment for TD14.10
--      CAST(ExtraField10 AS BYTEINT) AS AlgMode,       -- uncomment for TD14.10
--      ExtraField9 AS LockDelay,                       -- uncomment for TD14.10
--      ExtraField5 AS ObjectUsage,                     -- uncomment for TD14.10
      CASE
         WHEN SummaryFlag = 'T'
         THEN ' SUMMARY='
              || TRIM(SummaryVal1 (FORMAT 'Z(4)9')) || ','
              || TRIM(SummaryVal2 (FORMAT 'Z(4)9')) || ','
              || TRIM(SummaryVal3 (FORMAT 'Z(4)9')) || ' '
              || CASE
                    WHEN TypeOfCriterion= 0 THEN 'ElapsedSec'
                    WHEN TypeOfCriterion= 1 THEN 'CPUTime'
                    WHEN TypeOfCriterion= 2 THEN 'IOCount'
                    WHEN TypeOfCriterion= 3 THEN 'CPUTimeNorm'
                    WHEN TypeOfCriterion= 4 THEN 'ElapsedTime'
                    ELSE ''
                 END
      END AS LimitSummary
     ,CASE
         WHEN ThresholdFlag = 'T'
         THEN ' THRESHOLD='
              || TRIM(SummaryVal1 (FORMAT 'Z(4)9')) || ' '
              || CASE
                   WHEN TypeOfCriterion= 0 THEN 'ElapsedSec'
                   WHEN TypeOfCriterion= 1 THEN 'CPUTime'
                   WHEN TypeOfCriterion= 2 THEN 'IOCount'
                   WHEN TypeOfCriterion= 3 THEN 'CPUTimeNorm'
                   WHEN TypeOfCriterion= 4 THEN 'ElapsedTime'
                 END
      END AS LimitThreshold
     ,CASE WHEN TextSizeLimit <> 200 THEN ' SQLTEXT=' || TRIM(TextSizeLimit (FORMAT 'Z(4)9')) END AS LimitSQLText
     ,CASE WHEN LimitSummary   <> '' THEN 1 ELSE 0 END +
      CASE WHEN LimitThreshold <> '' THEN 1 ELSE 0 END +
      CASE WHEN LimitSQLText   <> '' THEN 1 ELSE 0 END AS LimitCount
     ,' WITH'
      || TRIM(TRAILING ',' FROM
                                CASE WHEN ExplainFlag            = 'T' THEN ' Explain,'                 ELSE '' END ||
                                CASE WHEN LockDelay              >  0  THEN ' LOCK=' || TRIM(LockDelay) ELSE '' END ||
                                CASE WHEN TypeOfCriterion        =  20 THEN ' None,'                    ELSE '' END ||
                                CASE WHEN ObjFlag                = 'T' THEN ' Objects,'                 ELSE '' END ||
                                CASE WHEN ParamFlag              = 'T' THEN ' ParamInfo,'               ELSE '' END || -- comment out for TD14.10
                                CASE WHEN SqlFlag                = 'T' THEN ' SQL,'                     ELSE '' END ||
                                CASE WHEN GETBIT(DBQLOptions,11) =  1  THEN ' Detailed'                 ELSE '' END ||
                                CASE WHEN StatsUsageFlag         = 'T' THEN ' StatsUsage,'              ELSE '' END ||
                                CASE WHEN StepFlag               = 'T' THEN ' StepInfo,'                ELSE '' END ||
                                CASE WHEN ObjectUsage            = 'T' THEN ' UseCount,'                ELSE '' END ||
                                CASE WHEN UtilityInfoFlag        = 'T' THEN ' UtilityInfo,'             ELSE '' END || -- comment out for TD14.10
                                CASE WHEN GETBIT(DBQLOptions,10) =  1  THEN ' Verbose'                  ELSE '' END ||
                                CASE WHEN XMLPlanFlag            = 'T' THEN ' XMLPlan,'                 ELSE '' END)  AS WithFlags
     ,COALESCE( ' Mode='||TRIM(AlgMode), '')  AS ModeFlag
     ,APPLNAME
     ,AccountString
     ,UserId
   FROM DBC.DBQLRuleTbl
) AS dt
JOIN DBC.DBase AS db
  ON UserId = db.DatabaseId
ORDER BY
   CASE WHEN UserId = '00000000'xb THEN 0 ELSE 1 END
  ,DatabaseName
  ,AccountString
  ,APPLNAME
;

 

Dieter

Abhishek_RC 12 posts Joined 09/14
18 Mar 2015

Awesome, Thanks a lot Dieter, for your help.
Yes I am also using TD 14.10.03.04.
But it looks like I had to comment out below columns as it did not exist in DBC.DBQLRule table :
Does that look fine ?
+

LockDelay

ParamFlag

ObjectUsage

UtilityInfoFlag

DBQLOptions

AlgMode

+

 

SELECT

   'BEGIN QUERY LOGGING'

   || CASE WHEN WithFlags = ' WITH' THEN '' ELSE WithFlags END

--   || ModeFlag

   || CASE WHEN LimitCount > 0 THEN ' LIMIT' || COALESCE(LimitSQLText, LimitSummary, LimitThreshold) ELSE '' END

   || CASE WHEN LimitCount > 1 THEN ' AND'   || COALESCE(LimitThreshold, LimitSummary, LimitSQLText) ELSE '' END

   || ' ON '

   || CASE WHEN APPLNAME <> '' THEN 'ApplName=''' ||TRIM(APPLNAME) || '''' ELSE TRIM(db.DatabaseName) END

   || CASE WHEN AccountString <> ''  THEN ' ACCOUNT=' || '''' || TRIM(AccountString) || '''' ELSE '' END

   || ';' AS DBQL_SQL

FROM

 (

   SELECT

--      ExtraField12 AS DBQLOptions,                    -- uncomment for TD14.10

--      CAST(ExtraField10 AS BYTEINT) AS AlgMode,       -- uncomment for TD14.10

--      ExtraField9 AS LockDelay,                       -- uncomment for TD14.10

--      ExtraField5 AS ObjectUsage,                     -- uncomment for TD14.10

      CASE

         WHEN SummaryFlag = 'T'

         THEN ' SUMMARY='

              || TRIM(SummaryVal1 (FORMAT 'Z(4)9')) || ','

              || TRIM(SummaryVal2 (FORMAT 'Z(4)9')) || ','

              || TRIM(SummaryVal3 (FORMAT 'Z(4)9')) || ' '

              || CASE

                    WHEN TypeOfCriterion= 0 THEN 'ElapsedSec'

                    WHEN TypeOfCriterion= 1 THEN 'CPUTime'

                    WHEN TypeOfCriterion= 2 THEN 'IOCount'

                    WHEN TypeOfCriterion= 3 THEN 'CPUTimeNorm'

                    WHEN TypeOfCriterion= 4 THEN 'ElapsedTime'

                    ELSE ''

                 END

      END AS LimitSummary

     ,CASE

         WHEN ThresholdFlag = 'T'

         THEN ' THRESHOLD='

              || TRIM(SummaryVal1 (FORMAT 'Z(4)9')) || ' '

              || CASE

                   WHEN TypeOfCriterion= 0 THEN 'ElapsedSec'

                   WHEN TypeOfCriterion= 1 THEN 'CPUTime'

                   WHEN TypeOfCriterion= 2 THEN 'IOCount'

                   WHEN TypeOfCriterion= 3 THEN 'CPUTimeNorm'

                   WHEN TypeOfCriterion= 4 THEN 'ElapsedTime'

                 END

      END AS LimitThreshold

     ,CASE WHEN TextSizeLimit <> 200 THEN ' SQLTEXT=' || TRIM(TextSizeLimit (FORMAT 'Z(4)9')) END AS LimitSQLText

     ,CASE WHEN LimitSummary   <> '' THEN 1 ELSE 0 END +

      CASE WHEN LimitThreshold <> '' THEN 1 ELSE 0 END +

      CASE WHEN LimitSQLText   <> '' THEN 1 ELSE 0 END AS LimitCount

     ,' WITH'

      || TRIM(TRAILING ',' FROM

                                CASE WHEN ExplainFlag            = 'T' THEN ' Explain,'                 ELSE '' END ||

                  --              CASE WHEN LockDelay              >  0  THEN ' LOCK=' || TRIM(LockDelay) ELSE '' END ||

                                CASE WHEN TypeOfCriterion        =  20 THEN ' None,'                    ELSE '' END ||

                                CASE WHEN ObjFlag                = 'T' THEN ' Objects,'                 ELSE '' END ||

                     --           CASE WHEN ParamFlag              = 'T' THEN ' ParamInfo,'               ELSE '' END || -- comment out for TD14.10

                                CASE WHEN SqlFlag                = 'T' THEN ' SQL,'                     ELSE '' END ||

                          --      CASE WHEN GETBIT(DBQLOptions,11) =  1  THEN ' Detailed'                 ELSE '' END ||

                                CASE WHEN StatsUsageFlag         = 'T' THEN ' StatsUsage,'              ELSE '' END ||

                                CASE WHEN StepFlag               = 'T' THEN ' StepInfo,'                ELSE '' END ||

          --                      CASE WHEN ObjectUsage            = 'T' THEN ' UseCount,'                ELSE '' END ||

                     --           CASE WHEN UtilityInfoFlag        = 'T' THEN ' UtilityInfo,'             ELSE '' END || -- comment out for TD14.10

                   --             CASE WHEN GETBIT(DBQLOptions,10) =  1  THEN ' Verbose'                  ELSE '' END ||

                                CASE WHEN XMLPlanFlag            = 'T' THEN ' XMLPlan,'                 ELSE '' END)  AS WithFlags

   --  ,COALESCE( ' Mode='||TRIM(AlgMode), '')  AS ModeFlag

     ,APPLNAME

     ,AccountString

     ,UserId

   FROM DBC.DBQLRuleTbl

) AS dt

JOIN DBC.DBase AS db

  ON UserId = db.DatabaseId

ORDER BY

   CASE WHEN UserId = '00000000'xb THEN 0 ELSE 1 END

  ,DatabaseName

  ,AccountString

  ,APPLNAME

;

Abhishek_RC 12 posts Joined 09/14
18 Mar 2015

Hi Dieter,
Apologies, please disregard my last post, it seems i misunderstood . I got it.Its working fine.
 
Thank you so much.
 
Abhishek

You must sign in to leave a comment.