DatabaseProcApplicationCreatedLinks
sybsystemprocssp_deviceattr  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_deviceattr"         17482
5     **
6     ** 17260, "Can't run %1! from within a transaction." 
7     ** 17471, "No such device exists -- run sp_helpdevice to list the DataServer devices."
8     ** 19104, "'%1!' attribute of device '%2!' turned '%3!'. Restart Adaptive 
9     **	   Server for the change to take effect."
10    ** 18807, "Changing attribute for master or HA master_companion device is not allowed"
11    ** 18683, "The device %1! is not a disk device."
12    ** 18684, "Usage: sp_deviceattr device_name, %1!, { true | false }"
13    ** 18685, "The attribute %1! is already turned %2!"
14    ** 19389, "The attributes %1! and %2! can not be both set to ON for the same device."
15    ** 19608, "No such parameter: '%1!'"
16    ** 19641, "Warning: '%1!', is a file system device.
17    **          A system failure may cause data loss if the dsync is set to false."
18    ** 19642, "Warning: Sybase recommends not to use '%1!' as a database device,
19    **          because it is a block device and may cause data loss in case of system failure."
20    ** 19975, "You cannot set attribute %1! for %2! device '%3!'."
21    */
22    
23    create or replace procedure sp_deviceattr
24    (
25        @logicalname varchar(30), /* logical name of the device */
26        @optname varchar(10), /* name of the parameter: "dsync", "directio" */
27        @optvalue varchar(10) /* the value of the dsync/directio flag: true or false */
28    )
29    as
30        declare @status int /* status bit for the device */
31        declare @status1 int /* status bitmap for the device */
32        declare @status2 int /* status2 bitmap for the device */
33        declare @whichstat tinyint /* indicates which status field to store */
34        declare @disk_device int /* disk device identifier */
35        declare @optbitvalue int /* the dsync/directio flag */
36        declare @msg varchar(1024) /* temp buffer for messages */
37        declare @chg_attr_value char(3) /* denotes if the attribute is on or off */
38        declare @directio_support int /* direct io support on current platform */
39        declare @phyname varchar(100) /* device physical path */
40        declare @devicetype int /* temp buffer for to store type of device */
41        declare @platform varchar(30) /* temp buffer to store platform name*/
42        declare @nullarg char(1)
43        declare @dummy int
44        declare @gp_enabled int
45    
46    
47        /*
48        **  If we're in a transaction, disallow this since it might make recovery
49        **  impossible.
50        */
51        if @@trancount > 0
52        begin
53            /* 17260, "Can't run %1! from within a transaction." */
54            raiserror 17260, "sp_deviceattr"
55            return (1)
56        end
57    
58        else
59        begin
60            set chained off
61        end
62    
63        set transaction isolation level 1
64    
65        /* 
66        ** If granular permissions is not enabled then sa_role is required.
67        ** If granular permissions is enabled then the permission 'manage disk' is
68        ** required.  proc_role and proc_auditperm will also do auditing
69        ** if required. Both will also print error message if required.
70        */
71    
72        select @nullarg = NULL
73        execute @status = sp_aux_checkroleperm "sa_role", "manage disk",
74            @nullarg, @gp_enabled output
75    
76        /* For Auditing */
77        if (@gp_enabled = 0)
78        begin
79            if (proc_role("sa_role") = 0)
80                return (1)
81        end
82        else
83        begin
84            select @dummy = proc_auditperm("manage disk", @status)
85        end
86    
87        if (@status != 0)
88            return (1)
89    
90        /*
91        ** Disallow the user to change the status of the master or the HA
92        ** master_companion device. The use of @@cmpstate here is to distinguish
93        ** a master_companion device from a HA failover and a non-HA user
94        ** created one. It is possible for user to create a device named
95        ** "master_companion" in a non-HA ASE.
96        */
97        if ((@logicalname = "master")
98                or ((@logicalname = "master_companion") and (@@cmpstate in (4, 12))))
99        begin
100           /*
101           ** 18807, "Changing attribute for master or HA master_companion
102           ** device is not allowed."
103           */
104           raiserror 18807
105           return (1)
106       end
107   
108       /*  Check and make sure that the device actually exists. */
109       if not exists (select *
110               from master.dbo.sysdevices
111               where name = @logicalname)
112       begin
113           /*
114           ** 17471, "No such device exists -- run sp_helpdevice to list
115           ** the DataServer devices." 
116           */
117           raiserror 17471
118           return (1)
119       end
120   
121       select @phyname = phyname, @status1 = status, @status2 = status2
122       from master.dbo.sysdevices
123       where name = @logicalname
124   
125       /* get device type; 1 - character special, 2 - block special, 3 - file system */
126       select @devicetype = getdevicetype(@phyname)
127   
128       /* get platform info from spt_values table */
129       select @platform = name from master.dbo.spt_values
130       where type = 'E' and number = 1
131   
132       /* warn block device usage on all platforms except hp , nt and linux*/
133       if (@devicetype = 2 and @platform not in ("hp9000/800", "nt386", "linux"))
134       begin
135           exec sp_getmessage 19642, @msg output
136           print @msg, @phyname
137       end
138   
139       /* InitIalize status bits. */
140       select @disk_device = 2
141   
142       /* Check that the name of the device is a disk device. */
143       if ((@status1 & @disk_device) != @disk_device)
144       begin
145   
146           /* 18683, "The device %1! is not a disk device." */
147           raiserror 18683, @logicalname
148           return (1)
149       end
150   
151       /* Check that there is no syntax error in the command line */
152   
153       if lower(@optname) not in ("dsync", "directio") or lower(@optvalue) not in
154           ("true", "false")
155       begin
156           /* 18684, "Usage: sp_deviceattr device_name, %1!, { true | false }" */
157           raiserror 18684, "dsync | directio"
158           return (1)
159   
160       end
161   
162       if (lower(@optname) = "dsync")
163       begin
164           if ((lower(@optvalue) = "true") and (@devicetype = 1))
165           begin
166               /*
167               ** 19975, "You cannot set attribute %1! for %2! device '%3!'."  
168               */
169               raiserror 19975, "dsync", "raw", @logicalname
170               return (1)
171           end
172           if ((lower(@optvalue) = "true") and ((@status2 & 1) = 1))
173           begin
174               /*
175               ** 19389, "The attributes %1! and %2! can not 
176               ** be both set to ON for the same device."
177               */
178               raiserror 19389, @optname, "directio"
179               return (1)
180           end
181           select @whichstat = 1
182           select @status = @status1
183           select @optbitvalue = 16384
184   
185           /*
186           ** In device.h
187           ** DSK_DSYNC_SEEN is 0x10000 which is 65536 in decimal
188           **
189           ** 'whichstat' above is set to 1 directly indicating that DSYNC
190           **  has been set to 'true' or 'false' using sp_deviceattr 
191           **
192           ** So now we have to first check if DSYNC_SEEN is already set,
193           ** probably during creation of the device, if not,
194           ** then we set it here, as we are explicitly setting DSYNC here 
195           ** We've used status2 from the devstat structure to store DSYNC_SEEN
196           ** hence we check and update that immediately.
197           */
198   
199           if ((@status2 & 65536) != 65536)
200           begin
201               select @status2 = (@status2 | 65536)
202               update master.dbo.sysdevices set status2 = @status2 where name = @logicalname
203           end
204       end
205       else if (lower(@optname) = "directio")
206       begin
207           select @directio_support = low from master.dbo.spt_values
208           where name = "directio" and type = "io"
209           /* check for directio support on current platform */
210           if (@directio_support = 0)
211           begin
212               /*
213               ** 19608, "No such parameter: '%1!'"  
214               */
215               raiserror 19608, "directio"
216               return (1)
217           end
218           if ((lower(@optvalue) = "true") and (@devicetype = 1))
219           begin
220               /*
221               ** 19975, "You cannot set attribute %1! for %2! device '%3!'."
222               */
223               raiserror 19975, "directio", "raw", @logicalname
224               return (1)
225           end
226           if ((lower(@optvalue) = "true") and ((@status1 & 16384) = 16384))
227           begin
228               /*
229               ** 19389, "The attributes %1! and %2! can not 
230               ** be both set to ON for the same device."
231               */
232               raiserror 19389, @optname, "dsync"
233               return (1)
234           end
235           select @whichstat = 2
236           select @status = @status2
237           select @optbitvalue = 1
238       end
239   
240       if (lower(@optvalue) = "true")
241   
242       begin
243           if ((@status & @optbitvalue) = @optbitvalue)
244           begin
245               /* 18685, "The attribute %1! is already turned %2!" */
246               raiserror 18685, @optname, "on"
247               return (1)
248           end
249           else
250           begin
251               /* The user wants to turn on the  o_dsync flag */
252               select @status = (@status | @optbitvalue)
253           end
254   
255           select @chg_attr_value = "on"
256       end
257       else if (lower(@optvalue) = "false")
258       begin
259           if ((@status & @optbitvalue) != @optbitvalue)
260           begin
261               /* 18685, "The attribute %1! is already turned %2!" */
262               raiserror 18685, @optname, "off"
263               return (1)
264           end
265           else
266           begin
267               /* The user wants to turn off the o_dsync flag */
268               select @status = (@status & ~ @optbitvalue)
269               /* warn the user , if he is using file system device with dsync off */
270               if ((@devicetype = 3) and (lower(@optname) = "dsync"))
271               begin
272                   exec sp_getmessage 19641, @msg output
273                   print @msg, @phyname
274               end
275           end
276   
277           select @chg_attr_value = "off"
278       end
279   
280       /* Validation passed, now update the status in sysdevices table. */
281       if (@whichstat = 1)
282       begin
283           update master.dbo.sysdevices set status = @status where name = @logicalname
284       end
285       else if (@whichstat = 2)
286       begin
287           update master.dbo.sysdevices set status2 = @status where name = @logicalname
288       end
289   
290       /*
291       ** 19104, "'%1!' attribute of device '%2!' turned '%3!'. Restart Adaptive 
292       **	   Server for the change to take effect."
293       */
294       exec sp_getmessage 19104, @msg output
295       print @msg, @optname, @logicalname, @chg_attr_value
296   
297       return (0)
298   


exec sp_procxmode 'sp_deviceattr', 'AnyMode'
go

Grant Execute on sp_deviceattr to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @phyname: varchar(100) = varchar(127) 121
 MTYP 4 Assignment type mismatch @platform: varchar(30) = varchar(255) 129
 MTYP 4 Assignment type mismatch status: smallint = int 283
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
208
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdevices  
 MGTP 3 Grant to public sybsystemprocs..sp_deviceattr  
 MNER 3 No Error Check should check return value of exec 135
 MNER 3 No Error Check should check @@error after update 202
 MNER 3 No Error Check should check return value of exec 272
 MNER 3 No Error Check should check @@error after update 283
 MNER 3 No Error Check should check @@error after update 287
 MNER 3 No Error Check should check return value of exec 294
 MUCO 3 Useless Code Useless Brackets in create proc 24
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 172
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 224
 MUCO 3 Useless Code Useless Brackets 226
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 243
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 281
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 297
 MUOT 3 Updates outside transaction 287
 QAFM 3 Var Assignment from potentially many rows 129
 QAFM 3 Var Assignment from potentially many rows 207
 QISO 3 Set isolation level 63
 VNRD 3 Variable is not read @dummy 84
 MSUB 2 Subquery Marker 109
 MTR1 2 Metrics: Comments Ratio Comments: 51% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 24 = 36dec - 14exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 146 23

DEPENDENCIES
PROCS AND TABLES USED
reads table master..spt_values (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)  
read_writes table master..sysdevices (1)