Reverse engineer rules from dbqlruletbl rows

General
Enthusiast

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

Tags (1)
3 REPLIES
Senior Apprentice

Re: Reverse engineer rules from dbqlruletbl rows

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
;
Enthusiast

Re: Reverse engineer rules from dbqlruletbl rows

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

;

Enthusiast

Re: Reverse engineer rules from dbqlruletbl rows

Hi Dieter,

Apologies, please disregard my last post, it seems i misunderstood . I got it.Its working fine.

Thank you so much.

Abhishek