DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropthreshold  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_dropthreshold"      17903
6     **
7     ** 17871, "There is no segment named '%1!'."
8     ** 17875, "Only the DBO of database '%1!' or a user with System Administrator
9     **	   (SA) authorization may add, delete, or modify thresholds in that
10    **	   database."
11    ** 17903, "Table '%1!' does not exist in database '%2!' -- cannot drop
12    **	  thresholds."
13    ** 17904, "Segment '%1!' does not have a threshold at '%2!' pages."
14    ** 17905, "You may not drop the log's last-chance threshold."
15    ** 17906, "Dropping threshold for segment '%1!' at '%2!' pages."
16    ** 17289, "Set your curwrite to the hurdle of current database."
17    */
18    
19    create or replace procedure sp_dropthreshold
20        @dbname varchar(255), /* current database name */
21        @segname varchar(255), /* segment name		*/
22        @free_space unsigned int /* threshold level	*/
23    as
24    
25        declare @segno int,
26            @status smallint,
27            @status1 smallint,
28            @msg varchar(1024),
29            @dummy int,
30            @nullarg char(1),
31            @gp_enabled int
32    
33        select @dummy = 0
34        select @status1 = 1
35    
36        /*
37        ** If we are under a user defined xact, disallow this since we may
38        ** leave the dbtable's threshold cache out-of-sync if the end user
39        ** rollbacks its xact.
40        */
41        if @@trancount > 0
42        begin
43            /*
44            ** 17260, "Can't run %1! from within a transaction."
45            */
46            raiserror 17260, "sp_dropthreshold"
47            return (1)
48        end
49    
50        set transaction isolation level 1
51        set chained off
52    
53        /*
54        ** Make sure we are in the right database
55        */
56        if (@dbname != db_name())
57        begin
58            /*
59            ** 18031, "You are in the wrong database.  Say 'USE %1!', then run
60            **         this procedure again."
61            */
62            raiserror 18031, @dbname
63            return (5)
64        end
65    
66        /*
67        ** If granular permissions is not enabled make sure the user (is the DBO) 
68        ** or (has "sa_role").  If granular permissions is enabled make certain the 
69        ** user has 'manage database' permission.
70        */
71    
72        execute @status = sp_aux_checkroleperm "dbo",
73            "manage database", @dbname, @gp_enabled output
74    
75        if (@status != 0)
76        begin
77            /*
78            ** Send apropriate audit records.
79            */
80            if (@gp_enabled = 0)
81            begin
82                execute @status1 = sp_aux_checkroleperm "sa_role",
83                    @nullarg, @nullarg, @gp_enabled output
84                if (@status1 != 0)
85                begin
86                    /* To Audit. */
87                    select @dummy = proc_role("sa_role")
88                    /*
89                    ** 17875, "Only the DBO of database %1! or a 
90                    ** user with System Administrator (SA) 
91                    ** authorization may add, delete, or modify 
92                    ** thresholds in that database.
93                    */
94                    raiserror 17875, @dbname
95                    return (1)
96                end
97            end
98            else
99            begin
100               select @dummy = proc_auditperm("manage database",
101                       @status, @dbname)
102               return (1)
103           end
104       end
105   
106       /* For Auditing */
107       if (@gp_enabled = 0)
108       begin
109           if (@status1 = 0)
110           begin
111               if (proc_role("sa_role") = 0)
112                   return 1
113           end
114       end
115       else
116       begin
117           select @dummy = proc_auditperm("manage database", @status,
118                   @dbname)
119       end
120   
121       /*
122       ** Make sure the segment name is valid.
123       */
124       select @segno = segment from syssegments where name = @segname
125       if @segno is NULL
126       begin
127           /*
128           ** 17871, "There is no segment named '%1!'."
129           */
130           raiserror 17871, @segname
131           return (2)
132       end
133   
134       /*
135       ** Make sure the threshold exists
136       */
137       if (select free_space from systhresholds
138               where segment = @segno
139                   and free_space = @free_space)
140           is null
141       begin
142           /*
143           ** 17904, "Segment '%1!' does not have a threshold at '%2!' pages."
144           */
145           raiserror 17904, @segname, @free_space
146           select segment_name = @segname, free_space
147           from systhresholds
148           where segment = @segno
149   
150           return (3)
151       end
152   
153       /* Make sure this is not the last-chance threshold for syslogs */
154       if @segname = 'logsegment' and
155               (select status from systhresholds
156               where segment = @segno
157                   and free_space = @free_space) & 1 = 1
158       begin
159           /*
160           ** 17905, "You may not drop the log's last-chance threshold."
161           */
162           raiserror 17905
163           return (4)
164       end
165   
166       /*
167       ** We have done all the tests we can.  Drop the threshold.
168       */
169   
170       /*
171       ** 17906, "Dropping threshold for segment '%1!' at '%2!' pages."
172       */
173       exec sp_getmessage 17906, @msg output
174       print @msg, @segname, @free_space
175   
176       begin transaction delete_threshold
177       delete systhresholds
178       where segment = @segno
179           and free_space = @free_space
180   
181       if @@error != 0
182       begin
183           rollback transaction
184           /*
185           ** 17907, "Delete of systhresholds row failed."
186           */
187           raiserror 17907
188           return (1)
189       end
190       /*
191       ** Last, rebuild the database threshold table
192       */
193       dbcc dbrepair(@dbname, "newthreshold", @segname)
194   
195   
196       if @@error != 0
197       begin
198           rollback transaction
199           /*
200           ** 17878, "Rebuild of the database threshold table failed."
201           */
202           raiserror 17878
203           return (1)
204       end
205   
206       commit transaction
207   
208       return (0)
209   


exec sp_procxmode 'sp_dropthreshold', 'AnyMode'
go

Grant Execute on sp_dropthreshold to public
go
RESULT SETS
sp_dropthreshold_rset_001

DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: uint vs int 139
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 148
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 QTYP 4 Comparison type mismatch Comparison type mismatch: uint vs int 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 178
 QTYP 4 Comparison type mismatch Comparison type mismatch: uint vs int 179
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOI 4 Table with no index sybsystemprocs..systhresholds sybsystemprocs..systhresholds
 MGTP 3 Grant to public sybsystemprocs..sp_dropthreshold  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MGTP 3 Grant to public sybsystemprocs..systhresholds  
 MNER 3 No Error Check should check return value of exec 173
 MUCO 3 Useless Code Useless Brackets 47
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 208
 QAFM 3 Var Assignment from potentially many rows 124
 QCRS 3 Conditional Result Set 146
 QISO 3 Set isolation level 50
 VNRD 3 Variable is not read @dummy 117
 MRST 2 Result Set Marker 146
 MSUB 2 Subquery Marker 137
 MSUB 2 Subquery Marker 155
 MTR1 2 Metrics: Comments Ratio Comments: 47% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 17dec - 11exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 80 19

DATA PROPAGATION detailed
ColumnWritten To
@segnamesp_dropthreshold_rset_001.segment_name

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table sybsystemprocs..syssegments  
writes table sybsystemprocs..sp_dropthreshold_rset_001 
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
read_writes table sybsystemprocs..systhresholds