DatabaseProcApplicationCreatedLinks
sybsystemprocssp_filter_metrics  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_filter_metrics"
6     **
7     ** 19417, "predicate '%1!' is an invalid filter condition."
8     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
9     ** 19345, "No qpmetrics group with group id = '%1!' exists in this database."
10    */
11    
12    /*
13    ** sp_filter_metrics delete qpmetrics records from SYSQUERYMETRICS
14    ** based on the inputed predicate and gid (sa only).
15    */
16    
17    create or replace procedure sp_filter_metrics
18        @pgid varchar(30) = NULL,
19        @pred varchar(600) = NULL
20    as
21        declare @gid int
22        declare @filter_cmd varchar(1000)
23        declare @dummy int
24        declare @nullarg varchar(1)
25        declare @gp_enabled int
26        declare @status int
27        declare @status1 int
28        declare @dbname varchar(255)
29    
30        /* Initialize */
31        select @status1 = 1
32    
33        set nocount on
34    
35        /* Don't start a transaction for the user, he does not expect it. */
36        if @@trancount = 0
37        begin
38            set chained off
39        end
40    
41        /* Don't hold long READ locks, the user might not appreciate it. */
42        set transaction isolation level 1
43    
44        select @dbname = db_name()
45        /*
46        **  Only the Database Owner (DBO) or
47        **  Accounts with sa_role can execute it.
48        **  If the user has sa_role, it's uid will
49        **  be DBO uid (1). If granular permissions is enabled then
50        **  users with 'manage database' or 'monitor qp performance' permission
51        **  can execute it.
52        */
53        select @nullarg = NULL
54    
55        execute @status = sp_aux_checkroleperm "dbo",
56            "manage database", @dbname, @gp_enabled output
57        if (@status != 0)
58        begin
59            if (@gp_enabled = 0)
60            begin
61                execute @status1 = sp_aux_checkroleperm sa_role,
62                    @nullarg, @nullarg, @gp_enabled output
63                if (@status1 != 0)
64                begin
65                    /* 
66                    ** 17230, "You must be the System Administrator
67                    ** (SA) or the Database Owner (dbo) to execute 
68                    ** this procedure." 
69                    */
70                    raiserror 17230
71                    return (1)
72                end
73            end
74            else
75            begin
76                execute @status1 = sp_aux_checkroleperm @nullarg,
77                    "monitor qp performance", @nullarg,
78                    @gp_enabled output
79                if (@status1 != 0)
80                begin
81                    /* 
82                    ** Call proc_perm here to do auditing and error
83                    **  message. 
84                    */
85                    select @dummy = proc_auditperm(
86                            "manage database", @status, @dbname)
87                    select @dummy =
88                        proc_auditperm("monitor qp performance",
89                            @status1)
90                    return (1)
91                end
92            end
93        end
94    
95        /* 
96        ** Send apropriate audit records, already determined user has 
97        ** one of the roles or the permission. 
98        */
99        if (@gp_enabled = 0)
100       begin
101           if (@status1 = 0)
102               select @dummy = proc_role("sa_role")
103       end
104       else
105       begin
106           if (@status = 0)
107               select @dummy = proc_auditperm("manage database",
108                       @status, @dbname)
109           if (@status1 = 0)
110               select @dummy = proc_auditperm("monitor qp performance",
111                       @status1)
112       end
113   
114       select @gid = convert(integer, @pgid)
115   
116       if @gid <= 0 or not exists (select 1 from sysqueryplans where gid = - @gid)
117       begin
118           /* 19345, "No qpmetrics group with group id = '%1!' exists in this database." */
119           raiserror 19345, @gid
120           return 2
121       end
122       else
123       begin
124           /* Now do the pruning based on the filtering condition */
125           select @filter_cmd =
126               " delete sysqueryplans from sysqueryplans, sysquerymetrics where "
127           select @filter_cmd = @filter_cmd + "sysqueryplans.gid = - @gid and "
128           select @filter_cmd = @filter_cmd + "sysqueryplans.id = sysquerymetrics.id and "
129           select @filter_cmd = @filter_cmd + "( " + @pred + " )"
130           exec (@filter_cmd)
131   
132           /* 19417, "predicate '%1!' is an invalid filter condition" */
133           if (@@error != 0)
134           begin
135               raiserror 19417, @pred
136               return 3
137           end
138       end
139   
140       return 0
141   


exec sp_procxmode 'sp_filter_metrics', 'AnyMode'
go

Grant Execute on sp_filter_metrics to public
go
DEFECTS
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_aux_checkroleperm: parameter # 1 61
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {gid}
116
 MGTP 3 Grant to public sybsystemprocs..sp_filter_metrics  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 133
 QISO 3 Set isolation level 42
 VNRD 3 Variable is not read @dummy 110
 MDYS 2 Dynamic SQL Marker 130
 MSUB 2 Subquery Marker 116
 MTR1 2 Metrics: Comments Ratio Comments: 39% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 13dec - 5exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 62 17

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysqueryplans  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  

CALLERS
called by proc sybsystemprocs..sp_metrics