DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dumpoptimize  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** This procedure configures the amount of data dumped by the Backup Server
5     ** during the 'dump database' command. It sends bs_optimize RPC to the local
6     ** Backup Server, passing all the parameters to the stored procedure as it is.
7     ** The parsing of all the options is done by the Backup Server. 
8     **
9     ** This can be issued only by users with SA role or oper_role or by the 
10    ** Database owner.
11    */
12    
13    /*
14    ** Messages for "sp_dumpoptimize"
15    **
16    ** 17260, "Can't run %1! from within a transaction."
17    ** 19958, "Backup Server policy is set to '%1!'."
18    */
19    
20    create or replace procedure sp_dumpoptimize
21        @opt1 varchar(30) = NULL, /* First optimization parameter */
22        @opt2 varchar(30) = NULL /* Second optimization parameter */
23    as
24    
25        declare @bs_name varchar(255),
26            @bs_policy varchar(255),
27            @msg varchar(1024),
28            @dummy int,
29            @nullarg char(1),
30            @gp_enabled int,
31            @dbname varchar(255),
32            @status1 int,
33            @status2 int,
34            @status3 int,
35            @idx int,
36            @val int,
37            @opt varchar(30),
38            @optval varchar(30)
39        declare @equal int
40        declare @value int
41    
42        set nocount on
43    
44        select @status1 = 1, @status2 = 1, @status3 = 1, @dbname = db_name()
45    
46        if @@trancount > 0
47        begin
48            /* 17260, "Can't run %1! from within a transaction." */
49            raiserror 17260, "sp_dumpoptimize"
50            return (1)
51        end
52    
53        /*
54        **  If granular permissions is not enabled, either dbo or 
55        **  accounts with sa_role or oper_role role can execute it.
56        **  If granular permissions is enabled then users with 
57        ** 'dump database' permission can execute it.
58        */
59        select @nullarg = NULL
60        execute @status1 = sp_aux_checkroleperm "dbo", "dump database",
61            @dbname, @gp_enabled output
62    
63        if (@gp_enabled = 0)
64        begin
65            if (@status1 != 0)
66            begin
67                execute @status2 = sp_aux_checkroleperm "sa_role",
68                    @nullarg, @nullarg, @gp_enabled output
69    
70                if (@status2 != 0)
71                begin
72                    execute @status3 = sp_aux_checkroleperm "oper_role",
73                        @nullarg, @nullarg, @gp_enabled output
74    
75                    if (@status3 != 0)
76                    begin
77                        /* 
78                        ** proc_role() will raise permission errors
79                        ** and send audit records to the audit trail.
80                        */
81                        select @dummy = proc_role("sa_role")
82                        select @dummy = proc_role("oper_role")
83                        return (1)
84                    end
85                end
86            end
87            if (@status2 = 0)
88                select @dummy = proc_role("sa_role")
89    
90            if (@status3 = 0)
91                select @dummy = proc_role("oper_role")
92        end
93        else
94        begin
95            select @dummy = proc_auditperm("dump database", @status1, @dbname)
96    
97            if (@status1 != 0)
98                return (1)
99        end
100   
101       /*
102       ** For parallel_scan option call to dbcc tune will set dump_pscan_cnt value.
103       ** This value can later be used during dump to verify number of available
104       ** ase connections.
105       */
106       if lower(substring(@opt1, 1, 13)) = "parallel_scan"
107       begin
108           select @equal = charindex("=", @opt1)
109           select @value = (convert(int, ltrim(rtrim(substring(@opt1, @equal + 1, len(@opt1) - @equal)))))
110           dbcc tune("dump_pscan_cnt", @value)
111       end
112   
113       select @bs_policy = srvnetname from master..sysservers where srvname = "SYB_BACKUP"
114   
115       /*
116       ** If the Multiple Backup server feature is enabled with Backup server policy
117       ** as $dedicated or $roundrobin, execute bs_optimize for all the active
118       ** Backup servers within cluster.
119       */
120       if ((lower(@bs_policy) = "$dedicated") or (lower(@bs_policy) = "$roundrobin"))
121       begin
122           /* 19958, "Backup Server policy is set to '%1!'." */
123           exec sp_getmessage 19958, @msg output
124           print @msg, @bs_policy
125   
126           declare instancenames cursor for
127           select srvname from master..sysservers where srvstatus2 = 4
128   
129           open instancenames
130   
131           fetch instancenames into @bs_name
132   
133           while (@@sqlstatus = 0)
134           begin
135               select @bs_name = @bs_name + "_BS"
136   
137               /* 19959, "Attempting to execute RPC for Backup Server '%1!'." */
138               exec sp_getmessage 19959, @msg output
139               print @msg, @bs_name
140   
141               exec (@bs_name + '...bs_optimize @opt1, @opt2')
142               fetch instancenames into @bs_name
143           end
144           close instancenames
145       end
146       else
147       begin
148           select @opt = @opt1, @val = - 1, @optval = ""
149           if (@opt1 = @opt2)
150           begin
151               select @opt2 = NULL
152           end
153   
154           while ((@val < 0) and (@opt is not NULL))
155           begin
156               select @idx = charindex('=', @opt)
157   
158               if @idx != 0 and substring(@opt, 1, @idx - 1)
159                   = "allocation_threshold"
160               begin
161                   select @optval = right (@opt, char_length(@opt) - @idx)
162                   if lower(ltrim(@optval)) = "default"
163                       select @val = 0
164                   else if isnumeric(@optval) = 1
165                       select @val = abs(convert(int, @optval))
166                   else
167                       break
168               end
169               else if (@opt = @opt1)
170                   select @opt = @opt2
171               else
172                   break
173           end
174   
175           if (@val between 0 and 100)
176           begin
177               dbcc tune("dump_allocation_threshold", @val, @optval)
178           end
179   
180           /* Issue the bs_optimize RPC to the local Backup Server */
181           exec SYB_BACKUP...bs_optimize @opt1, @opt2
182   
183       end
184       return (0)
185   
186   
187   


exec sp_procxmode 'sp_dumpoptimize', 'AnyMode'
go

Grant Execute on sp_dumpoptimize to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 148
 QTYP 4 Comparison type mismatch Comparison type mismatch: uint vs int 127
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause instancenames 127
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 20
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_dumpoptimize  
 MNER 3 No Error Check should check return value of exec 123
 MNER 3 No Error Check should check return value of exec 138
 MNER 3 No Error Check should check return value of exec 181
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 184
 VNRD 3 Variable is not read @gp_enabled 73
 VNRD 3 Variable is not read @dummy 95
 CUPD 2 Updatable Cursor Marker (updatable by default) 127
 MDYS 2 Dynamic SQL Marker 141
 MTR1 2 Metrics: Comments Ratio Comments: 33% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 21 = 23dec - 4exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 89 20

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysservers (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)