DatabaseProcApplicationCreatedLinks
sybsystemprocssp_bindcache  14 déc. 14Defects Propagation Dependencies

1     
2     /* Stored procedure for binding entites to named caches.  Entity must
3     ** be the name of a database, table or index.
4     **
5     ** Databases can only be bound when using Master.
6     ** The Master database cannot be bound to a named cache, but individual
7     ** tables in Master can.
8     */
9     
10    /*
11    ** Messages for "sp_bindcache"	18086
12    **
13    **  17260, "Can't run %1! from within a transaction."
14    **  18086, "Specified named cache does not exist."
15    **  18087, "You must be in Master to bind or unbind a database."
16    **  18089, "The Master database cannot be bound to or unbound from
17    **	    a named cache."
18    **  18092, "Only logs may be bound to this cache."
19    **  18098, "Individual tables in a temporary database cannot be bound to or 
20    **          unbound from named caches.  However, all of a temporary database
21    **	    may be bound or unbound."
22    **  18156, "Usage: sp_unbindcache dbname [, [ownername.]tablename
23    **	    [, indexname | `text only']]"
24    **  18170, "Usage: sp_bindcache cachename, dbname [, [ownername.]tablename
25    **	    [, indexname | `text only']]"
26    **  18175, "Specified named cache does not exist. The SQL Server must be
27    **	    rebooted to activate the named cahce."
28    **  18076, "Could not set curwrite to object level. Set your maxwrite label correctly."
29    **  19120, "Cache binding changes for 'tempdb' are not dynamic. Adaptive Server
30    **	    must be restarted for the change to take effect."
31    **  19686, "Warning! Binding a local temporary database to a local named cache
32    **	    that doesn't exist on the instance that owns the temporary 
33    **	    database."
34    **  19776, "Cache binding changes for local system temporary databases are not 
35    **	    dynamic. The owner instance must be rebooted for the change to take
36    **	    effect."
37    **  19777, "Cache binding change for local temporary database '%1!' failed due 
38    **	    to owner instance '%2!' status change. Please try again after the 
39    **	    owner instance change completes."
40    **  19828, "Cache binding failed for database '%1!'"
41    **  19974, "Individual object or database cannot be bound to or unbound from
42    **	   cache, '%1!', which is an in-memory storage cache."
43    **  19979, "Cannot run '%1!' for in-memory database '%2!'."
44    **  19991, "You cannot bind an in-memory database, or individual objects in an 
45    **	   in-memory database, to cache '%1!', which is a regular named cache. 
46    */
47    create or replace procedure sp_bindcache
48        @cachename varchar(255),
49        @dbname varchar(30),
50        @tablename varchar(512) = NULL,
51        @indexname varchar(255) = NULL
52    as
53    
54        declare @dbid int
55        declare @ownerid int
56        declare @objid int
57        declare @indid int
58        declare @entitytype char(1)
59        declare @aiobject int
60        declare @status int
61        declare @separator int
62        declare @mask int
63        declare @cachecount int
64        declare @class smallint
65        declare @attribute smallint
66        declare @action smallint
67        declare @objtype char(1)
68        declare @sysattrtype char(2)
69        declare @tempdb_mask int /* all tempdb status bits */
70        declare @local_tempdb_mask int /* all local tempdb status bits */
71        declare @localsystdbbit int /* local system tempdb status3 bit */
72        declare @tdb_instanceid int /* local tempdb instanceid */
73        declare @tdb_instancename varchar(255)
74        declare @sqlbuf varchar(255)
75        declare @islocalsystdb int /* It is a local system tempdb */
76        declare @is_others_ltdb int /* It is a local tempdb on remote instance */
77        declare @dbstat int
78        declare @dbstat3 int
79        declare @msg varchar(1024)
80        declare @is_inmemcache int
81        declare @nullarg char(1)
82        declare @dummy int
83        declare @gp_enabled int
84    
85    
86    
87    
88        /*
89        **  Don't allow this in a transaction because we can't undo what
90        **  the bind_cache() builtin has done.
91        */
92        if @@trancount > 0
93        begin
94            /*
95            ** 17260, "Can't run %1! from within a transaction."
96            */
97            raiserror 17260, "sp_bindcache"
98            return (1)
99        end
100       else
101       begin
102           set transaction isolation level 1
103           set chained off
104       end
105   
106       /* check if user has sa role, proc_role will also do auditing
107       ** if required. proc_role will also print error message if required.
108       */
109       select @nullarg = NULL
110       execute @status = sp_aux_checkroleperm "sa_role",
111           "manage data cache", @nullarg, @gp_enabled output
112   
113       /* For Auditing */
114       if (@gp_enabled = 0)
115       begin
116           if (proc_role("sa_role") = 0)
117               return 1
118       end
119       else
120       begin
121           select @dummy = proc_auditperm("manage data cache", @status)
122       end
123   
124       if (@status != 0)
125           return (1)
126   
127   
128       /* Make sure the target cache exists. */
129       if (@cachename is not NULL)
130       begin
131           select @status = 0
132           select @is_inmemcache = 0
133   
134           /* Don't get deleted entries. We may have valid entry */
135   
136           select @status = status from master.dbo.sysconfigures
137           where name = @cachename and parent = 19 and config = 19
138               and status != 16384
139           /* if the named cache does not exist */
140           if (@status = 0)
141           begin
142               /*  18086, "Specified named cache does not exist." */
143               raiserror 18086
144               return 1
145           end
146           /* if the named cache is not active */
147           if (@status & 32) != 32
148           begin
149               /*  
150               ** 18175, "Specified named cache does not exist. The SQL Server must be
151               ** rebooted to activate the named cache."
152               */
153               raiserror 18175
154               return 1
155           end
156   
157           /* if the named cache is an in-memory storage cache */
158           if (@status & 65536 = 65536)
159           begin
160               select @is_inmemcache = 1
161               raiserror 19974, @cachename
162               return 1
163           end
164       end
165   
166       if @dbname is NULL
167       begin
168           /*
169           ** Dbname is required for both sp_bindcache and sp_unbindcache.
170           */
171           if @cachename is NULL
172           begin
173               /*
174               ** Raise the sp_unbindcache usage message.
175               */
176               raiserror 18156
177               return 1
178           end
179           else
180           begin
181               /*
182               ** Raise the sp_bindcache usage message.
183               */
184               raiserror 18170
185               return 1
186           end
187       end
188   
189       /*
190       ** Retrieve the id's required to identify the object the cache is being
191       ** bound to.
192       */
193       select @status = 0
194       select @dbid = NULL
195       select @ownerid = NULL
196       select @objid = NULL
197       select @indid = NULL
198       select @entitytype = NULL
199       exec @status = sp_aux_parse_entity @dbname, @tablename, @indexname,
200           @dbid output, @ownerid output,
201           @objid output, @indid output,
202           @entitytype output
203   
204       if @status = 2
205       begin
206           /*
207           ** A syntax error was encountered.  Raise the appropriate usage
208           ** message.
209           */
210           if @cachename is NULL
211           begin
212               /*
213               ** Raise the sp_unbindcache usage message.
214               */
215               raiserror 18156
216               return 1
217           end
218           else
219           begin
220               /*
221               ** Raise the sp_bindcache usage message.
222               */
223               raiserror 18170
224               return 1
225           end
226       end
227       else if @status != 0
228       begin
229           return @status
230       end
231   
232       /* Do not allow sp_bindcache/sp_unbindcache to be run on an archive database. */
233       if exists (select * from master.dbo.sysdatabases
234               where dbid = @dbid
235                   and (status3 & 4194304) = 4194304)
236       begin
237           /* Cannot run '%1!' on an archive database." */
238           if @cachename = NULL
239               raiserror 19424, "sp_unbindcache"
240           else
241               raiserror 19424, "sp_bindcache"
242           return (1)
243       end
244   
245       /* Do not allow sp_unbindcache to be run on an in-memory database. */
246       if exists (select * from master.dbo.sysdatabases
247               where dbid = @dbid
248                   and (status4 & 4096 = 4096))
249       begin
250           if @cachename = NULL
251           begin
252               raiserror 19979, "sp_unbindcache", @dbname
253               return (1)
254           end
255           else if @is_inmemcache = 0
256           begin
257               /* Inmemory database or any object in inmemory database
258               ** cannot be bound to normal named cache.
259               */
260               raiserror 19991, @cachename
261               return (1)
262           end
263       end
264   
265       select @is_others_ltdb = 0
266       select @islocalsystdb = 0
267   
268       select @dbstat = status, @dbstat3 = status3
269   
270       from master.dbo.sysdatabases
271       where dbid = @dbid
272   
273       /*
274       ** If we are [un]binding a database...
275       */
276       if @entitytype = 'D'
277       begin
278           /* 
279           ** Make sure we are currently in the master database.
280           ** If not display an error message and exit.
281           */
282           if db_id() != 1
283           begin
284               raiserror 18087
285               return 1
286           end
287           /* 
288           ** If the cache type is "logonly", make sure we are binding only
289           ** syslogs to it.  Binding a database to a "logonly" cache is illegal.
290           */
291           select @status = 0
292           select @status = status
293           from master.dbo.sysconfigures
294           where name = @cachename and parent = 19 and config = 19
295           select @mask = @status & 4
296           if @mask > 0
297           begin
298               /*  
299               **	18092, "Only logs may be bound to this cache."
300               */
301               raiserror 18092
302               return 1
303           end
304   
305   
306   
307           /* Master itself cannot be bound */
308           if @dbid = 1
309           begin
310               raiserror 18089
311               return 1
312           end
313   
314           select @aiobject = @dbid
315       end
316       else
317       begin
318           select @tempdb_mask = number
319           from master.dbo.spt_values
320           where type = "D3" and name = "TEMPDB STATUS MASK"
321   
322           /* Individual tables in temporary databases cannot be bound. */
323           if ((@dbid = 2) OR (@dbstat3 & @tempdb_mask) != 0)
324           begin
325               raiserror 18098
326               return 1
327           end
328   
329           select @aiobject = @objid
330   
331           /* if the log-only bit is set, make sure we're binding the log. */
332           select @status = 0
333           select @status = status from master.dbo.sysconfigures
334           where name = @cachename and parent = 19 and config = 19
335           select @mask = @status & 4
336           if @mask > 0
337           begin
338               if (@objid != 8)
339               begin
340                   raiserror 18092
341                   return 1
342               end
343           end
344       end
345   
346       /*
347       ** Map entity type to attribute type.  Note that the attribute manager
348       ** needs a two-byte string...
349       */
350       select @sysattrtype = @entitytype + " "
351   
352       /* Figure out if we need to insert, update or delete from sysattributes. */
353       begin tran bindcache
354       if @cachename is not NULL
355       BEGIN
356           if exists (select * from sysattributes where
357                       class = 3 AND
358                       attribute = 0 AND
359                       object_type = @sysattrtype AND
360                       object = @aiobject AND
361                       object_info1 = @indid)
362           BEGIN
363               select @action = 2 /* ATTR_CHANGE */
364           END
365           ELSE
366           BEGIN
367               select @action = 1 /* ATTR_ADD */
368           END
369       END
370       ELSE
371       BEGIN
372           select @action = 3 /* ATTR_DROP */
373       END
374   
375       /*
376       **  Call the notification routine before modifying sysattributes.  This
377       **  is necessary because cm_bind_cache() needs to look up the OLD binding
378       **  for the entity.
379       */
380       select @status = 0
381       select @class = 3
382       select @attribute = 0
383   
384       /*
385       ** For system 'tempdb' all (un)bindings are delayed untill the
386       ** next reboot. The changes on the systemtables are done anyway.
387       **
388       ** In SDC, same goes with local system tempdb. However,
389       ** This restriction on dynamic binding doesn't apply to the global
390       ** system 'tempdb' since it is not used for worktable or #temp tables
391       ** unless in configuration mode.
392       */
393       if @@clustermode = "shared disk cluster"
394       begin
395           select @localsystdbbit = number
396           from master.dbo.spt_values
397           where type = "D3" and name = "local system temp db"
398           if ((@dbstat3 & @localsystdbbit) = @localsystdbbit)
399           begin
400               select @islocalsystdb = 1
401           end
402       end
403   
404       if ((@dbid != 2 or (@@clustermode = "shared disk cluster" and
405                       sdc_intempdbconfig() = 0))
406               and @islocalsystdb = 0
407               and @is_others_ltdb = 0)
408       BEGIN
409           select @status = attrib_notify(@class, @attribute, @sysattrtype, @aiobject,
410                   @indid, NULL, NULL, NULL, NULL, @cachename, NULL,
411                   NULL, NULL, @action)
412   
413           if @status = 1
414           BEGIN
415               rollback tran bindcache
416               raiserror 19828, @dbname
417               return @status
418           END
419       END
420   
421       if @action = 1
422       BEGIN
423           insert into sysattributes
424           (class, attribute, object_type, object,
425               object_info1, char_value, int_value)
426           VALUES
427           (3, /* BUFFER MANAGER */
428               0, /* CACHE BINDING */
429               @sysattrtype,
430               @aiobject,
431               @indid,
432               @cachename,
433               1 /* Binding is valid */
434           )
435       END
436   
437       if @action = 2
438       BEGIN
439           update sysattributes
440           set char_value = @cachename,
441               int_value = 1 /* binding is valid */
442           where class = 3 AND
443               attribute = 0 AND
444               object_type = @sysattrtype AND
445               object = @aiobject AND
446               object_info1 = @indid
447       END
448   
449       if @action = 3
450       BEGIN
451           delete from sysattributes
452           where class = 3 AND
453               attribute = 0 AND
454               object_type = @sysattrtype AND
455               object = @aiobject AND
456               object_info1 = @indid
457       END
458   
459       /*
460       ** If we come here for bindings of local tempdb owned by remote instance,
461       ** we have assumed that the local tempdb is not recovered and we can
462       ** safely update master..sysattributes only. Verify that the local tempdb
463       ** is still not recovered, otherwise, rollback the transaction.
464       */
465       if (@@clustermode = "shared disk cluster" and @is_others_ltdb = 1)
466       begin
467           select @dbstat = status
468           from master.dbo.sysdatabases
469           where dbid = @dbid
470           if ((@dbstat & 64) = 0)
471           begin
472               /*
473               ** 19777, "Cache binding change for local tempdb '%1!' 
474               ** failed due to owning instance '%2!' status change. 
475               ** Please try again after the owning instance change
476               ** completes."
477               */
478               raiserror 19777, @dbname, @tdb_instancename
479               rollback tran bindcache
480               return 1
481           end
482       end
483       commit tran bindcache
484   
485       if (@dbid = 2 and (@@clustermode != "shared disk cluster" or
486                   sdc_intempdbconfig() = 1))
487       BEGIN
488           /*
489           ** 19120, "Cache binding changes for 'tempdb' are not dynamic.
490           ** Adaptive Server must be restarted for the change to take effect."
491           **
492           ** This message should be printed as a warning/informational message
493           ** and not user error but it is not changed to avoid any impact to 
494           ** user applications.
495           */
496           raiserror 19120
497       END
498   
499       if (@islocalsystdb = 1)
500       begin
501           /*
502           ** 19776, "Cache binding changes for local system temporary databases 
503           ** are not dynamic. The owner instance must be rebooted for the change 
504           ** to take effect."
505           */
506           exec sp_getmessage 19776, @msg output
507           print @msg
508       end
509   
510       return @status
511   
512   


exec sp_procxmode 'sp_bindcache', 'AnyMode'
go

Grant Execute on sp_bindcache to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
320
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
397
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 137
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 234
 QTYP 4 Comparison type mismatch smallint = int 234
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 247
 QTYP 4 Comparison type mismatch smallint = int 247
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 271
 QTYP 4 Comparison type mismatch smallint = int 271
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 294
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 334
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 357
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 358
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 442
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 443
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 452
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 453
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 469
 QTYP 4 Comparison type mismatch smallint = int 469
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 VRUN 4 Variable is read and not initialized @tdb_instancename 478
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_bindcache  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MNAC 3 Not using ANSI 'is null' 238
 MNAC 3 Not using ANSI 'is null' 250
 MNER 3 No Error Check should check @@error after insert 423
 MNER 3 No Error Check should check @@error after update 439
 MNER 3 No Error Check should check @@error after delete 451
 MNER 3 No Error Check should check return value of exec 506
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 253
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 323
 MUCO 3 Useless Code Useless Brackets 338
 MUCO 3 Useless Code Useless Brackets 398
 MUCO 3 Useless Code Useless Brackets 404
 MUCO 3 Useless Code Useless Brackets 465
 MUCO 3 Useless Code Useless Brackets 470
 MUCO 3 Useless Code Useless Brackets 485
 MUCO 3 Useless Code Useless Brackets 499
 QAFM 3 Var Assignment from potentially many rows 318
 QAFM 3 Var Assignment from potentially many rows 395
 QISO 3 Set isolation level 102
 QIWC 3 Insert with not all columns specified missing 8 columns out of 15 424
 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_info1, object_type, object, attribute, class}
357
 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_info1, object_type, object, attribute, class}
442
 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_info1, object_type, object, attribute, class}
452
 VNRD 3 Variable is not read @dummy 121
 VNRD 3 Variable is not read @ownerid 200
 VUNU 3 Variable is not used @separator 61
 VUNU 3 Variable is not used @cachecount 63
 VUNU 3 Variable is not used @objtype 67
 VUNU 3 Variable is not used @local_tempdb_mask 70
 VUNU 3 Variable is not used @tdb_instanceid 72
 VUNU 3 Variable is not used @sqlbuf 74
 MSUB 2 Subquery Marker 233
 MSUB 2 Subquery Marker 246
 MSUB 2 Subquery Marker 356
 MTR1 2 Metrics: Comments Ratio Comments: 48% 47
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 43 = 62dec - 21exi + 2 47
 MTR3 2 Metrics: Query Complexity Complexity: 253 47

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..sysdatabases (1)  
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
read_writes table sybsystemprocs..sysattributes  
reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_aux_parse_entity  
   reads table sybsystemprocs..sysindexes  
   reads table sybsystemprocs..sysobjects  

CALLERS
called by proc sybsystemprocs..sp_unbindcache