DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setlockpromote_ptn  14 déc. 14Defects Propagation Dependencies

1     
2     /* Stored procedure for adding or modifying partition lock promotion attribute. 
3     ** Database attributes can only be modified when using Master.
4     */
5     
6     /*
7     ** Messages for "sp_setlockpromote_ptn"
8     **
9     ** 17260, "Can't run %1! from within a transaction."
10    ** 17421, "No such database -- run sp_helpdb to list databases."
11    ** 17460, "Object must be in the current database."
12    ** 18076, "Could not set curwrite to object level. Set your maxwrite label correctly."
13    ** 18090, "The target object does not exist."
14    ** 18158, "At least one of the parameters 'new_lwm', 'new_hwm' or 'new_pct' must be non-NULL to execute this procedure."
15    ** 18159, "You must be in the 'master' database to add, change or drop lock promotion attribute for a user database."
16    ** 18160, "Please specify a non-NULL value for %1, since it has not been set previously with a non-NULL value."
17    ** 18161, "Object name parameter must be NULL for Server-wide lock promotion attributes. Using NULL instead of - %1."
18    ** 18162, "'%1!' is a not a user table.  '%1' can be used only on user tables."
19    ** 18163, "The 'lock promotion LWM' value %1 cannot be greater than the 'lock promotion HWM' value %2."
20    ** 18164, "Invalid value specified for 'scope' parameter. Valid values are 'SERVER','DATABASE' or 'TABLE'."
21    ** 18165, "The 'lock promotion!' attributes of %1 '%2' have been changed. The new values are %3."
22    ** 18338, "Invalid obj_type parameter: '%1!'. Please specify 'PAGE' or 'ROW'."
23    */
24    
25    create or replace procedure sp_setlockpromote_ptn
26        @obj_type varchar(10), /* page or row */
27        @scope varchar(10), /* table, database or server */
28        @objname varchar(767) = NULL, /* table or database name */
29        @new_lwm int = NULL, /* Lock promotion lwm value */
30        @new_hwm int = NULL, /* Lock promotion hwm value */
31        @new_pct int = NULL /* Lock promotion pct value */
32    as
33    
34        declare @attrib_objid int /* object id of the table/db */
35        declare @object_type varchar(2) /* object type of the table/db */
36        declare @attrib int /* attrib type of the optname */
37        declare @use_char_value varchar(42) /* Lock promotion attribute's char_value */
38        declare @msg varchar(1024) /* message buffer */
39        declare @action int /* ADD or CHANGE attribute */
40        declare @ret int /* Return value */
41        declare @tab_type smallint /* User table ? */
42        declare @use_lwm int
43        declare @use_hwm int
44        declare @use_pct int
45        declare @update_lwm int
46        declare @update_hwm int
47        declare @nullarg char(1)
48        declare @dummy int
49        declare @status int
50        declare @gp_enabled int
51        declare @serverclass int
52    
53        select @use_lwm = NULL
54        select @use_hwm = NULL
55        select @use_pct = NULL
56    
57        /* If we're in a transaction, disallow this */
58        if @@trancount > 0
59        begin
60            /*
61            ** 17260, "Can't run %1! from within a transaction."
62            */
63            raiserror 17260, "sp_setlockpromote_ptn"
64            return (1)
65        end
66        else
67        begin
68            set chained off
69        end
70    
71        set transaction isolation level 1
72    
73        if upper(@obj_type) not in ("PAGE", "ROW")
74        begin
75            /*
76            ** 18338, "Invalid obj_type parameter: '%1!'. Please specify 'PAGE' or 'ROW'."
77            */
78            raiserror 18338, @obj_type
79            return (1)
80        end
81    
82        /*
83        ** If object name is given in the format "DBname.TABname", then 
84        ** this stored procedure should be executed from the DB context.
85        */
86        if @objname like "%.%" and
87            substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
88        begin
89            /*
90            ** 17460, "Object must be in the current database."
91            */
92            raiserror 17460
93            return (1)
94        end
95    
96        if (upper(@scope) != "DATABASE" and upper(@scope) != "SERVER"
97                and upper(@scope) != "TABLE")
98        begin
99            /*
100           ** 18164, "Invalid value specified for 'scope' parameter 
101           ** Valid values are 'SERVER','DATBASE' or 'TABLE'."
102           */
103           raiserror 18164, @objname
104           return (1)
105       end
106   
107       if upper(@obj_type) = "PAGE"
108           select @attrib = 0
109       else
110           select @attrib = 1
111   
112       /* 
113       ** If granular permissions is not enabled then sa_role is required.
114       ** If granular permissions is enabled permission 'manage lock promotion
115       ** threshold' is required.  proc_role and proc_auditperm will also do auditing
116       ** if required. Both will also print error message if required.
117       */
118   
119       select @nullarg = NULL
120       execute @status = sp_aux_checkroleperm "sa_role",
121           "manage lock promotion threshold", @nullarg,
122           @gp_enabled output
123   
124       /* For Auditing */
125       if (@gp_enabled = 0)
126       begin
127           if (proc_role("sa_role") = 0)
128               return (1)
129       end
130       else
131       begin
132           select @dummy = proc_auditperm("manage lock promotion threshold",
133                   @status)
134       end
135   
136       if (@status != 0)
137           return (1)
138   
139       if (upper(@scope) = "SERVER")
140       begin
141           /* 
142           ** If we're configuring lock promotion for a database, 
143           ** make sure we're currently in master. 
144           */
145           if @objname is not NULL
146           begin
147               /*
148               ** 18161, "Object name parameter must be NULL for Server-wide 
149               ** lock promotion attributes. Using NULL instead of - %1."
150               */
151               raiserror 18161, @objname
152           end
153   
154           select @serverclass = 1
155   
156           select @object_type = 'S '
157   
158           select @use_lwm = object_info1, @use_hwm = object_info2,
159               @use_pct = object_info3
160           from master..sysattributes where class = 47
161               and attribute = @attrib
162               and object_type = @object_type
163   
164           if @@rowcount != 0
165           begin
166               select @action = 2 /* ATTR_CHANGE */
167           end
168           else
169           begin
170               select @action = 1 /* ATTR_ADD */
171           end
172       end
173   
174       if (upper(@scope) = "DATABASE")
175       begin
176           /* 
177           ** If we're configuring lock promotion for a database, 
178           ** make sure we're currently in master. 
179           */
180   
181           select @attrib_objid = db_id()
182           if @attrib_objid != 1
183           begin
184               /*
185               ** 18159, "You must be in the 'master' database to add, change 
186               ** or drop lock promotion attribute for a user database."
187               */
188               raiserror 18159
189               return 1
190   
191           end
192   
193           /* Translate dbname to dbid. */
194           select @attrib_objid = 0
195           select @attrib_objid = dbid from master.dbo.sysdatabases
196           where name = @objname
197   
198           if @attrib_objid = 0
199           begin
200               /*
201               ** 17421, "No such database -- run sp_helpdb to list databases."
202               */
203               raiserror 17421
204               return 1
205           end
206   
207           select @object_type = 'D '
208       end
209   
210       if (upper(@scope) = "TABLE")
211       begin
212           /* Translate objname to objid. */
213           select @attrib_objid = 0
214           select @attrib_objid = id, @tab_type = sysstat & 7
215           from sysobjects where id = object_id(@objname)
216   
217           if @attrib_objid = 0
218           begin
219               /* 
220               ** 18090, "The target object does not exist."
221               */
222               raiserror 18090
223               return 1
224   
225           end
226   
227           if @tab_type != 3
228           begin
229               /*
230               ** 18162, "'%1!' is a not a user table. '%2' can be 
231               ** used only on user tables."
232               */
233               raiserror 18162, @objname, "sp_setlockpromote_ptn"
234               return (1)
235           end
236   
237           select @object_type = 'T '
238       end
239   
240       if (@serverclass != 1)
241       begin
242           select @use_lwm = object_info1, @use_hwm = object_info2,
243               @use_pct = object_info3
244           from sysattributes where class = 46
245               and attribute = @attrib
246               and object_type = @object_type
247               and object = @attrib_objid
248   
249           if (@@rowcount != 0)
250           begin
251               select @action = 2 /* ATTR_CHANGE */
252           end
253           else
254           begin
255               select @action = 1 /* ATTR_ADD */
256   
257           end
258       end
259   
260       /*
261       ** Validate that if a new attribute value is not specified, then an old one 
262       ** exists for each of LWM, HWM and PCT.
263       */
264   
265       if @new_lwm is not NULL
266           select @use_lwm = @new_lwm
267       if @use_lwm is NULL
268       begin
269           /*
270           ** 18160, "Please specify a non-NULL value for %1, 
271           ** since it has not been set previously with a non-NULL value."
272           */
273           raiserror 18160, "new_lwm"
274           return 1
275       end
276   
277       if @new_hwm is not NULL
278           select @use_hwm = @new_hwm
279       if @use_hwm is NULL
280       begin
281           /*
282           ** 18160, "Please specify a non-NULL value for %1, 
283           ** since it has not been set previously with a non-NULL value."
284           */
285           raiserror 18160, "new_hwm"
286           return 1
287       end
288   
289       if @new_pct is not NULL
290           select @use_pct = @new_pct
291       if @use_pct is NULL
292       begin
293           /*
294           ** 18160, "Please specify a non-NULL value for %1, 
295           ** since it has not been set previously with a non-NULL value."
296           */
297           raiserror 18160, "new_pct"
298           return 1
299       end
300   
301       /* Validate that LWM is less than or equal to HWM. */
302   
303       if @use_lwm > @use_hwm
304       begin
305           /*
306           ** 18163, "The 'lock promotion LWM' value %1 cannot 
307           ** be greater than the 'lock promotion HWM' value %2."
308           **
309           */
310           raiserror 18163, @use_lwm, @use_hwm
311           return 1
312       end
313   
314       /*
315       ** Assemble the char_value field for sysattributes. This is needed for
316       ** sp_help and sp_helpdb to display all 3 of LWM, HWM and PCT, even though
317       ** we use an umbrella attribute "row lock promote", or "page lock promote".
318       */
319   
320       select @use_char_value = "PCT = " + convert(varchar(3), @use_pct) +
321           ", LWM = " + convert(varchar(10), @use_lwm) +
322           ", HWM = " + convert(varchar(10), @use_hwm)
323   
324       begin transaction
325   
326       /* Validate boundary conditions for the attributes. */
327       if (@serverclass = 1)
328       begin
329           select @ret = attrib_valid(47, @attrib, @object_type,
330                   NULL, @use_lwm, @use_hwm, @use_pct, NULL,
331                   NULL, NULL, NULL, NULL, NULL, @action)
332       end
333       else
334       begin
335           select @ret = attrib_valid(46, @attrib, @object_type,
336                   @attrib_objid, @use_lwm, @use_hwm, @use_pct, NULL,
337                   NULL, NULL, NULL, NULL, NULL, @action)
338       end
339   
340       if @ret = 0 /* Validation routine returned false. */
341       begin
342           rollback tran
343           return (1)
344       end
345   
346       if (@action = 2) /* ATTR_CHANGE */
347       begin
348           if (@serverclass = 1)
349           begin
350               update master..sysattributes
351               set char_value = @use_char_value,
352                   object_info1 = @use_lwm,
353                   object_info2 = @use_hwm,
354                   object_info3 = @use_pct
355               where class = 47
356                   and attribute = @attrib
357                   and object_type = @object_type
358           end
359           else
360           begin
361               update sysattributes
362               set char_value = @use_char_value,
363                   object_info1 = @use_lwm,
364                   object_info2 = @use_hwm,
365                   object_info3 = @use_pct
366               where class = 46
367                   and attribute = @attrib
368                   and object_type = @object_type
369                   and object = @attrib_objid
370           end
371   
372           /*
373           ** If there was an error, @@error will be non-zero
374           */
375           if @@error != 0
376           begin
377               if @@trancount != 0
378                   rollback transaction
379               return (1)
380           end
381       end
382       else /* ADD attribute */
383       begin
384           if (@serverclass = 1)
385           begin
386               insert into master..sysattributes(class, attribute, object_type,
387                   object_info1, object_info2,
388                   object_info3, char_value)
389               values (47, @attrib, @object_type,
390                   @new_lwm, @new_hwm,
391                   @new_pct, @use_char_value)
392           end
393           else
394           begin
395               insert into sysattributes(class, attribute, object_type, object,
396                   object_info1, object_info2,
397                   object_info3, char_value)
398               values (46, @attrib, @object_type, @attrib_objid,
399                   @new_lwm, @new_hwm,
400                   @new_pct, @use_char_value)
401           end
402   
403           /*
404           ** If there was an error, @@error will be non-zero
405           */
406           if @@error != 0
407           begin
408               if @@trancount != 0
409                   rollback transaction
410               return (1)
411           end
412       end
413   
414   
415       /* Notify */
416       if (@serverclass = 1)
417       begin
418           select @ret = attrib_notify(47, @attrib, @object_type,
419                   NULL, @use_lwm, @use_hwm, @use_pct, NULL,
420                   NULL, NULL, NULL, NULL, NULL, @action)
421       end
422       else
423       begin
424           select @ret = attrib_notify(46, @attrib, @object_type,
425                   @attrib_objid, @use_lwm, @use_hwm, @use_pct, NULL,
426                   NULL, NULL, NULL, NULL, NULL, @action)
427       end
428   
429       if @ret = 0 /* Unable to notify ? */
430       begin
431           rollback tran
432           return (1)
433       end
434   
435       commit transaction
436   
437       /*
438       ** 18165, "The %4! lock promotion attributes of %1, '%2' 
439       ** have been changed. The new values are %3."
440       */
441       exec sp_getmessage 18165, @msg output
442       print @msg, @scope, @objname, @use_char_value, @obj_type
443   
444       return (0)
445   


exec sp_procxmode 'sp_setlockpromote_ptn', 'AnyMode'
go

Grant Execute on sp_setlockpromote_ptn to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch attribute: smallint = int 389
 MTYP 4 Assignment type mismatch attribute: smallint = int 398
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 160
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 161
 QTYP 4 Comparison type mismatch smallint = int 161
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 244
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 245
 QTYP 4 Comparison type mismatch smallint = int 245
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 355
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 356
 QTYP 4 Comparison type mismatch smallint = int 356
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 366
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 367
 QTYP 4 Comparison type mismatch smallint = int 367
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 214
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 215
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_setlockpromote_ptn  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check @@error after update 350
 MNER 3 No Error Check should check @@error after update 361
 MNER 3 No Error Check should check @@error after insert 386
 MNER 3 No Error Check should check @@error after insert 395
 MNER 3 No Error Check should check return value of exec 441
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 249
 MUCO 3 Useless Code Useless Brackets 327
 MUCO 3 Useless Code Useless Brackets 343
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 348
 MUCO 3 Useless Code Useless Brackets 379
 MUCO 3 Useless Code Useless Brackets 384
 MUCO 3 Useless Code Useless Brackets 410
 MUCO 3 Useless Code Useless Brackets 416
 MUCO 3 Useless Code Useless Brackets 432
 MUCO 3 Useless Code Useless Brackets 444
 MUPK 3 Update column which is part of a PK or unique index object_info1 351
 MUPK 3 Update column which is part of a PK or unique index object_info1 362
 QAFM 3 Var Assignment from potentially many rows 158
 QAFM 3 Var Assignment from potentially many rows 242
 QISO 3 Set isolation level 71
 QIWC 3 Insert with not all columns specified missing 8 columns out of 15 386
 QIWC 3 Insert with not all columns specified missing 7 columns out of 15 395
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, attribute}
160
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
244
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, attribute}
355
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
366
 VNRD 3 Variable is not read @dummy 132
 VUNU 3 Variable is not used @update_lwm 45
 VUNU 3 Variable is not used @update_hwm 46
 MTR1 2 Metrics: Comments Ratio Comments: 41% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 30 = 46dec - 18exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 197 25

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
read_writes table master..sysattributes (1)  
reads table master..sysdatabases (1)  
read_writes table sybsystemprocs..sysattributes  
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  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  

CALLERS
called by proc sybsystemprocs..sp_setrowlockpromote_ptn  
called by proc sybsystemprocs..sp_setpglockpromote_ptn