DatabaseProcApplicationCreatedLinks
sybsystemprocssp_deviceattr  31 Aug 14Defects 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 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    
43        /*
44        **  If we're in a transaction, disallow this since it might make recovery
45        **  impossible.
46        */
47        if @@trancount > 0
48        begin
49            /* 17260, "Can't run %1! from within a transaction." */
50            raiserror 17260, "sp_deviceattr"
51            return (1)
52        end
53    
54        else
55        begin
56            set chained off
57        end
58    
59        set transaction isolation level 1
60    
61        /*
62        ** check if user has sa role, proc_role will also do auditing
63        ** if required. proc_role will also print error message if required.
64        */
65    
66        if (proc_role("sa_role") = 0)
67            return (1)
68    
69        /*
70        ** Disallow the user to change the status of the master or the HA
71        ** master_companion device. The use of @@cmpstate here is to distinguish
72        ** a master_companion device from a HA failover and a non-HA user
73        ** created one. It is possible for user to create a device named
74        ** "master_companion" in a non-HA ASE.
75        */
76        if ((@logicalname = "master")
77                or ((@logicalname = "master_companion") and (@@cmpstate in (4, 12))))
78        begin
79            /*
80            ** 18807, "Changing attribute for master or HA master_companion
81            ** device is not allowed."
82            */
83            raiserror 18807
84            return (1)
85        end
86    
87        /*  Check and make sure that the device actually exists. */
88        if not exists (select *
89                from master.dbo.sysdevices
90                where name = @logicalname)
91        begin
92            /*
93            ** 17471, "No such device exists -- run sp_helpdevice to list
94            ** the DataServer devices." 
95            */
96            raiserror 17471
97            return (1)
98        end
99    
100       select @phyname = phyname, @status1 = status, @status2 = status2
101       from master.dbo.sysdevices
102       where name = @logicalname
103   
104       /* get device type; 1 - character special, 2 - block special, 3 - file system */
105       select @devicetype = getdevicetype(@phyname)
106   
107       /* get platform info from spt_values table */
108       select @platform = name from master.dbo.spt_values
109       where type = 'E' and number = 1
110   
111       /* warn block device usage on all platforms except hp , nt and linux*/
112       if (@devicetype = 2 and @platform not in ("hp9000/800", "nt386", "linux"))
113       begin
114           exec sp_getmessage 19642, @msg output
115           print @msg, @phyname
116       end
117   
118       /* InitIalize status bits. */
119       select @disk_device = 2
120   
121       /* Check that the name of the device is a disk device. */
122       if ((@status1 & @disk_device) != @disk_device)
123       begin
124   
125           /* 18683, "The device %1! is not a disk device." */
126           raiserror 18683, @logicalname
127           return (1)
128       end
129   
130       /* Check that there is no syntax error in the command line */
131   
132       if lower(@optname) not in ("dsync", "directio") or lower(@optvalue) not in
133           ("true", "false")
134       begin
135           /* 18684, "Usage: sp_deviceattr device_name, %1!, { true | false }" */
136           raiserror 18684, "dsync | directio"
137           return (1)
138   
139       end
140   
141       if (lower(@optname) = "dsync")
142       begin
143           if ((lower(@optvalue) = "true") and (@devicetype = 1))
144           begin
145               /*
146               ** 19975, "You cannot set attribute %1! for %2! device '%3!'."  
147               */
148               raiserror 19975, "dsync", "raw", @logicalname
149               return (1)
150           end
151           if ((lower(@optvalue) = "true") and ((@status2 & 1) = 1))
152           begin
153               /*
154               ** 19389, "The attributes %1! and %2! can not 
155               ** be both set to ON for the same device."
156               */
157               raiserror 19389, @optname, "directio"
158               return (1)
159           end
160           select @whichstat = 1
161           select @status = @status1
162           select @optbitvalue = 16384
163       end
164       else if (lower(@optname) = "directio")
165       begin
166           select @directio_support = low from master.dbo.spt_values
167           where name = "directio" and type = "io"
168           /* check for directio support on current platform */
169           if (@directio_support = 0)
170           begin
171               /*
172               ** 19608, "No such parameter: '%1!'"  
173               */
174               raiserror 19608, "directio"
175               return (1)
176           end
177           if ((lower(@optvalue) = "true") and (@devicetype = 1))
178           begin
179               /*
180               ** 19975, "You cannot set attribute %1! for %2! device '%3!'."
181               */
182               raiserror 19975, "directio", "raw", @logicalname
183               return (1)
184           end
185           if ((lower(@optvalue) = "true") and ((@status1 & 16384) = 16384))
186           begin
187               /*
188               ** 19389, "The attributes %1! and %2! can not 
189               ** be both set to ON for the same device."
190               */
191               raiserror 19389, @optname, "dsync"
192               return (1)
193           end
194           select @whichstat = 2
195           select @status = @status2
196           select @optbitvalue = 1
197       end
198   
199       if (lower(@optvalue) = "true")
200   
201       begin
202           if ((@status & @optbitvalue) = @optbitvalue)
203           begin
204               /* 18685, "The attribute %1! is already turned %2!" */
205               raiserror 18685, @optname, "on"
206               return (1)
207           end
208           else
209           begin
210               /* The user wants to turn on the  o_dsync flag */
211               select @status = (@status | @optbitvalue)
212           end
213   
214           select @chg_attr_value = "on"
215       end
216       else if (lower(@optvalue) = "false")
217       begin
218           if ((@status & @optbitvalue) != @optbitvalue)
219           begin
220               /* 18685, "The attribute %1! is already turned %2!" */
221               raiserror 18685, @optname, "off"
222               return (1)
223           end
224           else
225           begin
226               /* The user wants to turn off the o_dsync flag */
227               select @status = (@status & ~ @optbitvalue)
228               /* warn the user , if he is using file system device with dsync off */
229               if ((@devicetype = 3) and (lower(@optname) = "dsync"))
230               begin
231                   exec sp_getmessage 19641, @msg output
232                   print @msg, @phyname
233               end
234           end
235   
236           select @chg_attr_value = "off"
237       end
238   
239       /* Validation passed, now update the status in sysdevices table. */
240       if (@whichstat = 1)
241       begin
242           update master.dbo.sysdevices set status = @status where name = @logicalname
243       end
244       else if (@whichstat = 2)
245       begin
246           update master.dbo.sysdevices set status2 = @status where name = @logicalname
247       end
248   
249       /*
250       ** 19104, "'%1!' attribute of device '%2!' turned '%3!'. Restart Adaptive 
251       **	   Server for the change to take effect."
252       */
253       exec sp_getmessage 19104, @msg output
254       print @msg, @optname, @logicalname, @chg_attr_value
255   
256       return (0)
257   


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) 100
 MTYP 4 Assignment type mismatch status: smallint = int 242
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
167
 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 114
 MNER 3 No Error Check should check return value of exec 231
 MNER 3 No Error Check should check @@error after update 242
 MNER 3 No Error Check should check @@error after update 246
 MNER 3 No Error Check should check return value of exec 253
 MUCO 3 Useless Code Useless Brackets in create proc 24
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 206
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 244
 MUCO 3 Useless Code Useless Brackets 256
 MUOT 3 Updates outside transaction 246
 QAFM 3 Var Assignment from potentially many rows 108
 QAFM 3 Var Assignment from potentially many rows 166
 QISO 3 Set isolation level 59
 MSUB 2 Subquery Marker 88
 MTR1 2 Metrics: Comments Ratio Comments: 49% 23
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 21 = 32dec - 13exi + 2 23
 MTR3 2 Metrics: Query Complexity Complexity: 129 23

DEPENDENCIES
PROCS AND TABLES USED
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
read_writes table master..sysdevices (1)