DatabaseProcApplicationCreatedLinks
sybsystemprocssp_shmdumpdisp  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** SP_SHMDUMPDISP
4     **
5     ** This stored procedure is used to display the current setting for
6     ** shared memory dump conditions. It is called by the sp_shmdumpconfig
7     ** stored procedure when a user executes sp_shmdumpconfig to display
8     ** the current settings.
9     **
10    ** This stored procedure calls the sp_shmdumpsize stored procedure 
11    ** in order to get size estimates for the shared memory dump files
12    ** that will be generated for each condition.
13    **
14    ** History:
15    ** 19sept96	pdorfman	Initial coding
16    ** 07apr97	pdorfman	Created separate stored procedure
17    ** 21jan02	dwein		Added support for message type
18    ** 19apr04	pdorfman	Skip dump count for default condition
19    */
20    
21    create or replace procedure sp_shmdumpdisp
22        /* -------------- Stored Procedure Parameters ----------------------- */
23        @type varchar(20) /* action requested by user   */
24    as
25    
26        /* ----------------- Declare Local Variables ------------------------ */
27        declare @not_status int, /* notification return status */
28            @maxconds int, /* max # dump conditions      */
29            @curcount int, /* current # dump cond's      */
30            @header_printed int, /* display header printed     */
31            @print_str varchar(1024), /* for printing error msgs    */
32            @cache_size int, /* total data cache memory    */
33            @proc_size int, /* size of procedure cache    */
34            @memory int, /* server total memory        */
35            @shm_size int, /* non-cache memory           */
36            @proc_size_str char(10), /* size of procedure cache    */
37            @cache_size_str char(10), /* server total memory        */
38            @shm_size_str char(10), /* non-cache memory           */
39            @memory_str char(10), /* server total memory        */
40            @position int, /* position of first blank    */
41            @windows int, /* indicates windows platform */
42            @rptline varchar(512),
43            @compression_level int /* dump compression level     */
44    
45    
46    
47        /*
48        ** The following variables are treated as constants within this
49        ** stored procedure. They are set below.
50        */
51        declare @DUMPCOND_CLASS int, /* sysattributes class        */
52            @OBJTYPE char(2), /* sysattributes object typ   */
53            @CFG_PRIMARY int, /* primary record id          */
54            @CFG_FILENAME int, /* file name record id        */
55            @CFG_DIRNAME int, /* directory name record id   */
56            @CFG_MAXDUMPS int, /* maxdumps record id         */
57            @CFG_PAGECACHE int, /* page cache record id       */
58            @CFG_PROCCACHE int, /* proc cache record id       */
59            @CFG_UNUSED int, /* unused memory record id    */
60            @CFG_HALT int, /* halt option record id	*/
61            @ATTR_ADD int, /* add notification value     */
62            @ATTR_CHANGE int, /* change notification val    */
63            @ATTR_DROP int, /* drop notification value    */
64            @ATTR_FETCH int, /* fetch notification value   */
65            @CFG_INCLUDE int, /* include memory type        */
66            @CFG_OMIT int, /* omit memory type           */
67            @CFG_DEFAULT int, /* use default mem setting    */
68            @CFG_ATTRIBUTE int, /* attrib val for config options */
69            @CFG_ERRORLOG int, /* include errorlog record id */
70            @CFG_MERGE int, /* merge pll dump record id   */
71            @CFG_CLUSTER int, /* cluster dump behavior      */
72            @CFG_CORE int, /* produce core dump */
73            @CFG_CSMD int /* produce csmd */
74    
75        /*
76        ** Declare variables and cursors used to display current settings
77        */
78        declare @cond_name char(10),
79            @cond_value int,
80            @cond_cvalue char(20),
81            @cond_attrib int,
82            @rectype int,
83            @int_val int,
84            @char_val varchar(255),
85            @cmaxdumps char(7),
86            @dmaxdumps char(7),
87            @cpagecache varchar(20),
88            @dpagecache varchar(20),
89            @cproccache varchar(20),
90            @dproccache varchar(20),
91            @cunused varchar(20),
92            @dunused varchar(20),
93            @chalt varchar(20),
94            @dhalt varchar(20),
95            @cdirname varchar(255),
96            @ddirname varchar(255),
97            @cfilename varchar(30),
98            @dfilename varchar(30),
99            @defaults_found int,
100           @dumpsize int,
101           @cdumpsize varchar(10),
102           @compdumpsize varchar(10),
103           @order_number int,
104           @dumpcount char(3),
105           @cfgval int,
106           @cluster char(7),
107           @dcluster char(7),
108           @current_mode varchar(10),
109           @dumptype varchar(10),
110           @coreset int,
111           @csmdset int
112   
113       /*
114       ** Create temporary lookup table referenced by the c_conditions
115       ** cursor (below).  Table creation is required here in order to 
116       ** declare the cursor.
117       */
118       create table #attname(attrib int, name char(10))
119   
120       /*
121       ** Create temporary conditions table referenced by the c_display
122       ** cursor (below).  Table creation is required here in order to
123       ** declare the cursor
124       */
125       create table #conditions
126       (order_num int,
127           Type char(10),
128           Value char(20) null,
129           Dumptype char(10),
130           Maxdumps char(7),
131           Dump_Count char(3) null,
132           Page_Cache char(7),
133           Proc_Cache char(7),
134           Unused_Space char(7),
135           Halt char(7),
136           Cluster char(7),
137           Est_File_Size varchar(10),
138           Est_Comp_Size varchar(10),
139           Filename varchar(30),
140           Directory varchar(255))
141   
142       /*
143       ** Cursor to return all dump condition rows, ordered by object type
144       ** (attribute)
145       */
146       declare c_conditions cursor
147       for select an.name, sa.attribute, sa.object
148       from master.dbo.sysattributes sa, #attname an
149       where sa.class = @DUMPCOND_CLASS
150           and sa.object_type = @OBJTYPE
151           and sa.attribute = an.attrib
152           and sa.object_info1 = @CFG_PRIMARY
153       order by sa.attribute
154   
155       /*
156       ** Cursor to return all settings for a given dump condition
157       */
158       declare c_settings cursor
159       for select sa.object_info1, sa.int_value, sa.char_value
160       from master.dbo.sysattributes sa
161       where sa.class = @DUMPCOND_CLASS
162           and sa.object_type = @OBJTYPE
163           and sa.attribute = @cond_attrib
164           and sa.object = @cond_value
165           and sa.object_info1 != @CFG_PRIMARY
166   
167       /*
168       ** Cursor to read the #conditions table and display the contents.
169       */
170       declare c_display cursor
171       for select Type, Value, Dumptype, Maxdumps, Dump_Count,
172           Page_Cache, Proc_Cache, Unused_Space,
173           Halt, Cluster, Est_File_Size, Est_Comp_Size, Filename, Directory
174       from #conditions
175       order by order_num
176   
177       /* ----------------- Setup and Validation ------------------------ */
178       set nocount on
179   
180       /*
181       **  Common Definition Section: Note: any changes made to the following
182       **  values must also be made in shmdumpdrop and shmdumpconfig
183       */
184   
185       /*
186       ** Class ID and type defined in utils/attrib.lst
187       */
188       select @DUMPCOND_CLASS = 7
189       select @OBJTYPE = "DC"
190   
191       /*
192       ** The following constants define record types for the dump condition
193       ** class in the sysattributes table.The values set here must be the 
194       ** same as those defined in utils/cfgdump.c.
195       */
196       select @CFG_PRIMARY = 1
197       select @CFG_FILENAME = 2
198       select @CFG_DIRNAME = 3
199       select @CFG_MAXDUMPS = 4
200       select @CFG_PAGECACHE = 5
201       select @CFG_PROCCACHE = 6
202       select @CFG_UNUSED = 7
203       select @CFG_HALT = 8
204       select @CFG_CLUSTER = 9
205       select @CFG_CORE = 10
206       select @CFG_CSMD = 11
207   
208       /*
209       ** The following constants define record types for the dump condition
210       ** class in the sysattributes table for the config records. The values
211       ** set here must be the same as those defined in utils/shmdump.c.
212       */
213       select @CFG_ATTRIBUTE = 0
214       select @CFG_ERRORLOG = 1
215       select @CFG_MERGE = 2
216   
217       /*
218       ** The following constants are used for setting memory modes and other
219       ** options.  These values must correspond to those used in utils/cfgdump.c.
220       */
221       select @CFG_DEFAULT = 0 /* Use the default value */
222       select @CFG_INCLUDE = 1 /* Include memory / Option On */
223       select @CFG_OMIT = 2 /* Omit memory / Option off */
224   
225       /*
226       ** The following must correspond to values in sysattr.h
227       */
228       select @ATTR_ADD = 1
229       select @ATTR_CHANGE = 2
230       select @ATTR_DROP = 3
231       select @ATTR_FETCH = 4
232   
233       /*
234       ** Determine if we are running on the windows platform
235       */
236       if (select charindex("Windows", @@version)) > 0
237       begin
238           select @windows = 1
239       end
240       else
241       begin
242           select @windows = 0
243       end
244   
245       /*
246       ** Obtain the dump file compression level for use later on
247       */
248       select @compression_level = value
249       from master.dbo.sysconfigures
250       where name = 'memory dump compression level'
251   
252       /*
253       ** End Common Definition Section
254       */
255   
256       /* ----------------- Identify and Perform the Command ----------- */
257   
258       select @defaults_found = 0
259       select @header_printed = 0
260   
261       /*
262       ** Create table #attname (attrib int, name char(25))
263       ** NOTE: The atribute values must correspond to those defined
264       ** in utils/attrib.lst.
265       */
266       insert #attname values (1, 'Error   ')
267       insert #attname values (2, 'Signal  ')
268       insert #attname values (3, 'Severity')
269       insert #attname values (4, 'Module  ')
270       insert #attname values (5, 'Defaults')
271       insert #attname values (6, 'Timeslice')
272       insert #attname values (7, 'Panic')
273       insert #attname values (8, 'Message')
274       insert #attname values (9, 'Dbcc')
275       insert #attname values (10, 'Codepoint')
276   
277       /*
278       ** Open curson to select all dump conditions
279       */
280       open c_conditions
281   
282       fetch c_conditions into @cond_name, @cond_attrib, @cond_value
283   
284       if (@@sqlstatus = 1)
285       begin
286           /*
287           ** 18511, "Error reading sysattributes table"
288           */
289           raiserror 18511
290           return 1
291       end
292   
293       select @dumptype = "csmd"
294       select @cmaxdumps = "Default"
295       select @cpagecache = "Default"
296       select @cproccache = "Default"
297       select @cunused = "Default"
298       select @chalt = "Default"
299       select @cfilename = "Default File Name"
300       select @cdirname = "Default Directory"
301       select @dumpcount = ""
302       select @cluster = "Default"
303   
304       while (@@sqlstatus = 0)
305       begin
306           /*
307           ** Fetch all special settings for the current
308           ** dump condition
309           */
310           open c_settings
311   
312           fetch c_settings into @rectype, @int_val, @char_val
313   
314           /*
315           ** There is no dump count for the Defaults condition
316           */
317           if (@cond_name != "Defaults")
318           begin
319               select @dumpcount = convert(char(3),
320                   shmdumpcount(rtrim(@cond_name),
321                       @cond_value))
322           end
323           else
324           begin
325               select @dumpcount = "  0"
326           end
327   
328           while (@@sqlstatus = 0)
329           begin
330               if (@rectype = @CFG_MAXDUMPS)
331               begin
332                   select @cmaxdumps = convert(char(7), @int_val)
333               end
334               else if (@rectype = @CFG_PAGECACHE)
335               begin
336                   if (@int_val = 1)
337                       select @cpagecache = 'Include'
338                   else
339                       select @cpagecache = 'Omit   '
340               end
341               else if (@rectype = @CFG_PROCCACHE)
342               begin
343                   if (@int_val = 1)
344                       select @cproccache = 'Include'
345                   else
346                       select @cproccache = 'Omit   '
347               end
348               else if (@rectype = @CFG_UNUSED)
349               begin
350                   if (@int_val = 1)
351                       select @cunused = 'Include'
352                   else
353                       select @cunused = 'Omit   '
354               end
355               else if (@rectype = @CFG_CORE)
356               begin
357                   select @coreset = @int_val
358               end
359               else if (@rectype = @CFG_CSMD)
360               begin
361                   select @csmdset = @int_val
362               end
363               else if (@rectype = @CFG_HALT)
364               begin
365                   if (@int_val = 1)
366                       select @chalt = 'Halt   '
367                   else
368                       select @chalt = 'No Halt'
369               end
370               else if (@rectype = @CFG_DIRNAME)
371               begin
372                   select @cdirname = @char_val
373               end
374               else if (@rectype = @CFG_FILENAME)
375               begin
376                   select @cfilename = @char_val
377               end
378               else if (@rectype = @CFG_CLUSTER)
379               begin
380                   if (@int_val = 1)
381                       select @cluster = 'All'
382                   else
383                       select @cluster = 'Local  '
384               end
385               else
386               begin
387                   /*
388                   ** 18512, "WARNING: Unknown dump condition record type found: %1!"
389                   */
390                   raiserror 18512, @rectype
391               end
392   
393               fetch c_settings
394               into @rectype, @int_val, @char_val
395           end /* while (@@sqlstatus = 0) */
396   
397           /*
398           ** Close cursor on settings for current condition
399           */
400           close c_settings
401   
402           select @cond_cvalue = convert(char(20), @cond_value)
403   
404           /*
405           ** work out text for dump type
406           */
407           if (@coreset = @CFG_INCLUDE and @csmdset = @CFG_OMIT)
408               select @dumptype = "core"
409           else if (@coreset = @CFG_OMIT and @csmdset = @CFG_INCLUDE)
410               select @dumptype = "csmd"
411           else if (@coreset = @CFG_INCLUDE and @csmdset = @CFG_INCLUDE)
412               select @dumptype = "csmd, core"
413           else
414               select @dumptype = "csmd"
415   
416           /*
417           ** Handle the system default settings separately.
418           */
419           if (@cond_attrib = 5)
420           begin
421   display_defaults:
422               select @defaults_found = 1
423   
424               if (@header_printed = 1)
425               begin
426                   print " "
427               end
428   
429               /*
430               ** If the following values are not set by
431               ** records in the sysattributes table, report
432               ** the built in system defaults that we know will
433               ** be enforced at dump time (csmd_dump_condition)
434               ** using values hard-coded in the software.
435               */
436               if (@cmaxdumps = 'Default')
437               begin
438                   select @cmaxdumps = '1      '
439               end
440               select @dmaxdumps = @cmaxdumps
441   
442               if (@cpagecache = 'Default')
443               begin
444                   select @cpagecache = 'Omit   '
445               end
446               select @dpagecache = @cpagecache
447   
448               if (@cproccache = 'Default')
449               begin
450                   select @cproccache = 'Include'
451               end
452               select @dproccache = @cproccache
453   
454               if (@cunused = 'Default')
455               begin
456                   select @cunused = 'Omit   '
457               end
458               select @dunused = @cunused
459   
460               if (@chalt = 'Default')
461               begin
462                   select @chalt = 'Halt   '
463               end
464               select @dhalt = @chalt
465   
466               if (@cfilename = 'Default File Name')
467               begin
468                   select @cfilename = 'Generated File Name'
469               end
470               select @dfilename = @cfilename
471   
472               if (@cdirname = 'Default Directory')
473               begin
474                   select @cdirname = '$SYBASE'
475               end
476               select @ddirname = @cdirname
477   
478               if (@cluster = 'Default')
479               begin
480                   select @cluster = 'Local  '
481               end
482               select @dcluster = @cluster
483   
484               select @dumpcount = "---"
485   
486               /*
487               ** Make sure that default settings display last in
488               ** the output.
489               */
490               select @order_number = 9999
491           end
492           else /* (@cond_attrib = 5) */
493           begin
494               /*
495               ** Group dump conditions by condition type
496               */
497               select @order_number = @cond_attrib
498           end /* (@cond_attrib = 5) */
499   
500           /*
501           ** The value column is not applicable to the
502           ** system defaults, timeslice or panic
503           */
504           if (@cond_attrib in (5, 6, 7, 9))
505           begin
506               select @cond_cvalue = '---  '
507           end
508   
509   
510   
511           /*
512           ** Calculate the estimated dump file size
513           */
514           exec sp_shmdumpsize @cpagecache, @cproccache, @dumpsize output
515   
516   
517   
518           /*
519           ** Convert @dumpsize to varchar and append the units specification to
520           ** the string
521           */
522           select @cdumpsize = (convert(varchar(10), @dumpsize) + " " + "MB")
523   
524           /*
525           ** If dump file compression is enabled, calculate an estimate of
526           ** the compressed dump file size.  We do this based on a rough
527           ** rule of thumb that compressed files are about 1/4 the size of
528           ** the uncompressed file.  This is based on experience and not on
529           ** the current server's characteristics.
530           */
531           if (@compression_level > 0)
532           begin
533               select @compdumpsize = (convert(varchar(10), @dumpsize / 4) + " " + "MB")
534           end
535           else
536           begin
537               select @compdumpsize = @cdumpsize
538           end
539   
540           /*
541           ** Insert data for curent condition into worktable for 
542           ** display later.
543           */
544           insert #conditions
545           values (@order_number,
546               @cond_name,
547               @cond_cvalue,
548               @dumptype,
549               @cmaxdumps,
550               @dumpcount,
551               @cpagecache,
552               @cproccache,
553               @cunused,
554               @chalt,
555               @cluster,
556               @cdumpsize,
557               @compdumpsize,
558               @cfilename,
559               @cdirname)
560   
561           /*
562           ** Fetch next dump condition
563           */
564           fetch c_conditions into @cond_name, @cond_attrib, @cond_value
565   
566           /*
567           ** Set to default values
568           */
569           select @dumptype = "csmd"
570           select @cmaxdumps = "Default"
571           select @cpagecache = "Default"
572           select @cproccache = "Default"
573           select @cunused = "Default"
574           select @chalt = "Default"
575           select @cfilename = "Default File Name"
576           select @cdirname = "Default Directory"
577           select @dumpcount = ""
578           select @cluster = "Default"
579       end /* while (@@sqlstatus = 0) */
580   
581       /*
582       ** If we didn't find a defaults record, go back and display default
583       ** values anyway. This is as bit of a kludge, but it will work as long
584       ** as the goto statement is executed before the close cursor
585       ** statement that follows.
586       */
587       if (@defaults_found = 0)
588       begin
589           /*
590           ** There is no entry for the default settings in the
591           ** sysattributes table.  Display the system defaults 
592           ** that are defined in the server.
593           */
594           select @cond_name = 'Defaults'
595           select @cmaxdumps = '1      '
596           if (@windows = 1)
597           begin
598               select @cdirname = "%%SYBASE%%"
599           end
600           else
601           begin
602               select @cdirname = "$SYBASE"
603           end
604           select @cond_attrib = 5
605           goto display_defaults
606       end
607   
608       /*
609       ** Close cursor on dump conditions
610       */
611       close c_conditions
612   
613       /*
614       ** Now actually display the settings
615       */
616   
617       /*
618       ** Display a list of configured dump conditions
619       */
620       print ""
621       print "Configured Shared Memory Dump Conditions"
622       print "----------------------------------------"
623       print ""
624   
625       open c_display
626   
627       fetch c_display into
628           @cond_name,
629           @cond_cvalue,
630           @dumptype,
631           @cmaxdumps,
632           @dumpcount,
633           @cpagecache,
634           @cproccache,
635           @cunused,
636           @chalt,
637           @cluster,
638           @cdumpsize,
639           @compdumpsize,
640           @cfilename,
641           @cdirname
642   
643       while (@@sqlstatus = 0)
644       begin
645           if (@windows = 1 and @cond_name = "Signal")
646           begin
647               select @rptline = space(2) + "Exception" + space(1) +
648                   "0x" + inttohex(convert(int, @cond_cvalue))
649           end
650           else
651           begin
652               select @rptline = space(2) + @cond_name + space(1) + @cond_cvalue
653           end
654           print @rptline
655   
656           if (@cond_name = "Message")
657           begin
658               select @print_str = space(4) +
659                   "Message Text:" + space(11) +
660                   str_replace(ssel_message(convert(int,
661                           @cond_cvalue)), "%", "%%")
662               print @print_str
663           end
664           else if (@cond_name = "Error")
665           begin
666               if (@@langid = 0)
667               begin
668                   select @print_str = space(4) + "Error Text"
669                       + space(14) +
670                       str_replace(description, "%", "%%")
671                   from master.dbo.sysmessages where
672                       error = convert(int, @cond_cvalue)
673                       and langid is NULL
674               end
675               else
676               begin
677                   select @print_str = space(4) + "Error Text"
678                       + space(14) +
679                       str_replace(description, "%", "%%")
680                   from master.dbo.sysmessages where
681                       error = convert(int, @cond_cvalue)
682                       and langid = @@langid
683               end
684               print @print_str
685           end
686   
687           select @rptline = space(4) + "Type:" + space(19) + @dumptype
688           print @rptline
689   
690           select @rptline = space(4) + "Maximum Dumps:" +
691               space(10) + @cmaxdumps
692           if (@cond_name != "Defaults" and @cmaxdumps = "Default")
693               select @rptline = @rptline + " (" + rtrim(@dmaxdumps) + ")"
694           print @rptline
695   
696           if (@cond_name != "Defaults")
697           begin
698               select @rptline = space(4) + "Dumps since boot:" +
699                   space(7) + @dumpcount
700               print @rptline
701           end
702   
703           select @rptline = space(4) + "Halt Engines:" +
704               space(11) + @chalt
705           if (@cond_name != "Defaults" and @chalt = "Default")
706               select @rptline = @rptline + " (" + rtrim(@dhalt) + ")"
707           print @rptline
708   
709           select @rptline = space(4) + "Cluster:" +
710               space(16) + @cluster
711           if (@cond_name != "Defaults" and @cluster = "Default")
712               select @rptline = @rptline + " (" + rtrim(@dcluster) + ")"
713           print @rptline
714   
715           select @rptline = space(4) + "Page Cache:" +
716               space(13) + @cpagecache
717           if (@cond_name != "Defaults" and @cpagecache = "Default")
718               select @rptline = @rptline + " (" + rtrim(@dpagecache) + ")"
719           print @rptline
720   
721           select @rptline = space(4) + "Procedure Cache:" +
722               space(8) + @cproccache
723           if (@cond_name != "Defaults" and @cproccache = "Default")
724               select @rptline = @rptline + " (" + rtrim(@dproccache) + ")"
725           print @rptline
726   
727           select @rptline = space(4) + "Unused Space:" +
728               space(11) + @cunused
729           if (@cond_name != "Defaults" and @cunused = "Default")
730               select @rptline = @rptline + " (" + rtrim(@dunused) + ")"
731           print @rptline
732   
733           select @rptline = space(4) + "Dump Directory:" +
734               space(9) + @cdirname
735           if (@cond_name != "Defaults" and @cdirname = "Default Directory")
736               select @rptline = @rptline + " (" + rtrim(@ddirname) + ")"
737           print @rptline
738   
739           select @rptline = space(4) + "Dump File Name:" +
740               space(9) + @cfilename
741           if (@cond_name != "Defaults" and @cfilename = "Default File Name")
742               select @rptline = @rptline + " (" + rtrim(@dfilename) + ")"
743           print @rptline
744   
745           if (@compression_level > 0)
746           begin
747               select @rptline = space(4) + "Estimated Compressed File Size:" +
748                   space(4) + @compdumpsize
749               print @rptline
750               select @rptline = space(4) + "Estimated Uncompressed File Size:" +
751                   space(4) + @cdumpsize
752           end
753           else
754           begin
755               select @rptline = space(4) + "Estimated csmd Size:" +
756                   space(4) + @cdumpsize
757           end
758   
759           print @rptline
760   
761           print ""
762   
763           fetch c_display into
764               @cond_name,
765               @cond_cvalue,
766               @dumptype,
767               @cmaxdumps,
768               @dumpcount,
769               @cpagecache,
770               @cproccache,
771               @cunused,
772               @chalt,
773               @cluster,
774               @cdumpsize,
775               @compdumpsize,
776               @cfilename,
777               @cdirname
778       end
779   
780       close c_display
781   
782       /*
783       ** Get configured maximum number of dump conditions
784       */
785       select @maxconds = value
786       from master.dbo.sysconfigures
787       where name = 'maximum dump conditions'
788   
789       /*
790       ** Subtract 1 so that we don't count the defaults record
791       */
792       select @curcount = (count(*) - 1)
793       from #conditions
794   
795       /*
796       ** 18513, "Current number of conditions: %1!"
797       */
798       exec sp_getmessage 18513, @print_str output
799       print @print_str, @curcount
800       /*
801       ** 18514, "Maximum number of conditions: %1!"
802       */
803       exec sp_getmessage 18514, @print_str output
804       print @print_str, @maxconds
805   
806       /*
807       ** Display CSMD-related configuration options.
808       */
809       print ""
810       print "Configurable Shared Memory Dump Configuration Settings"
811       print "------------------------------------------------------"
812   
813       select @cfgval = value
814       from master.dbo.sysconfigures
815       where name = 'dump on conditions'
816   
817       /*
818       ** 19399, "Dump on conditions: %1!"
819       */
820       exec sp_getmessage 19399, @print_str output
821       print @print_str, @cfgval
822   
823       if (@cfgval = 0)
824       begin
825           /*
826           ** 19400, "You must run 'sp_configure 'dump on conditions',1' to
827           **		enable the shared memory dump facility."
828           */
829           exec sp_getmessage 19400, @print_str output
830           print ""
831           print @print_str
832           print ""
833       end
834   
835       select @cfgval = value
836       from master.dbo.sysconfigures
837       where name = 'number of dump threads'
838   
839       /*
840       ** 19398, "Number of dump threads: %1!"
841       */
842       exec sp_getmessage 19398, @print_str output
843       print @print_str, @cfgval
844   
845       /*
846       ** Determine include errorlog setting.  Set the @cfgval variable
847       ** to the default setting in the event there is no configuration row in
848       ** sysattributes.  This must be the same default as in utils/shmdump.c.
849       */
850       select @cfgval = 1
851       select @cfgval = int_value
852       from master.dbo.sysattributes
853       where class = @DUMPCOND_CLASS
854           and object_type = @OBJTYPE
855           and attribute = @CFG_ATTRIBUTE
856           and object = @CFG_ERRORLOG
857   
858       /*
859       ** 19396, "Include errorlog in dump file: %1!"
860       */
861       exec sp_getmessage 19396, @print_str output
862       print @print_str, @cfgval
863   
864       /*
865       ** Determine merge files setting.  Set the @cfgval variable
866       ** to the default setting in the event there is no configuration row in
867       ** sysattributes.  This must be the same default as in utils/shmdump.c.
868       */
869       select @cfgval = 1
870       select @cfgval = int_value
871       from master.dbo.sysattributes
872       where class = @DUMPCOND_CLASS
873           and object_type = @OBJTYPE
874           and attribute = @CFG_ATTRIBUTE
875           and object = @CFG_MERGE
876   
877       /*
878       ** 19397, "Merge parallel files after dump: %1!"
879       */
880       exec sp_getmessage 19397, @print_str output
881       print @print_str, @cfgval
882   
883       /*
884       ** 17160, "Shared memory dump file compression level: %1"
885       */
886       exec sp_getmessage 17160, @print_str output
887       print @print_str, @compression_level
888   
889       /*
890       ** Calculate server memory usage
891       */
892   
893       select @cache_size = 0
894       select @proc_size = 0
895   
896   
897   
898       /*
899       ** Obtain the current total memory allocated to the server. 104 is the
900       ** configuration # for total logical memory.
901       */
902       select @memory = convert(int, comment)
903       from master.dbo.syscurconfigs
904       where config = 104
905   
906       select @memory = (@memory / 1024) + 1
907   
908       select @memory_str = convert(char(7), @memory)
909       select @position = charindex(" ", @memory_str)
910       select @memory_str = space(7 - @position) + rtrim(@memory_str) + " MB"
911   
912       /*
913       ** 19 is the config # for named caches.
914       ** This value is in kilobytes.
915       ** For SDC server, ther are two kinds of data caches:
916       ** 1. Global cache
917       **    For this kind of cache, the instanceid in sysconfigures will be NULL.
918       ** 2. Private cache for an instance
919       **    For this kind of cache, the instanceid in sysconfigures will be the
920       **    instanceid of the instance on which the cache is configured.
921       ** So, for sysconfigures.instanceid, it might have below values:
922       **    (1) NULL
923       **        This means the cache is a global cache.
924       **    (2) instance id
925       **        This mean the cache is for instance.
926       **
927       ** In this procedure, we referred the implementation in sp_helpcache.
928       ** First, we will insert all the caches including the global cache and
929       ** private cache for instances into a temporary table. Then, we will
930       ** scan every rows in this temporaty table. If a row's instanceid is NULL,
931       ** this means it is a global cache. In this case, we need to check if there
932       ** are private cache with the same name for the instance. If there is such
933       ** a cache, we need to skip this row to avoid duplicated calculation.
934       */
935   
936       select @cache_size = sum(cu.value)
937       from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu
938       where co.config = 19
939           and co.config = cu.config
940           and co.name = cu.comment
941   
942   
943       select @cache_size_str = convert(char(7), (@cache_size / 1024) + 1)
944       select @position = charindex(" ", @cache_size_str)
945       select @cache_size_str = space(7 - @position) + rtrim(@cache_size_str) + " MB"
946   
947       /*
948       ** Config # 146 is the procedure cache size value.
949       ** Config # 414 is the statement cache size value.
950       ** Since statement cache is added to the configured procedure cache
951       ** size, these two values must be combined to calculate the total
952       ** procedure cache size.
953       */
954       select @proc_size = sum(convert(int, comment))
955       from master.dbo.syscurconfigs
956       where config in (146, 414)
957   
958       select @proc_size_str = convert(char(7), (@proc_size / 1024) + 1)
959       select @position = charindex(" ", @proc_size_str)
960       select @proc_size_str = space(7 - @position) + rtrim(@proc_size_str) + " MB"
961   
962       select @shm_size_str = convert(char(7), @memory - (((@cache_size / 1024)) + (@proc_size / 1024)))
963       select @position = charindex(" ", @shm_size_str)
964       select @shm_size_str = space(7 - @position) + rtrim(@shm_size_str) + " MB"
965   
966   
967   
968       print ""
969       print "Server Memory Allocation"
970       print "Procedure Cache  Data Caches  Server Memory  Total Memory"
971       print "---------------  -----------  -------------  ------------"
972       print "      %1!   %2!     %3!    %4!",
973           @proc_size_str, @cache_size_str, @shm_size_str, @memory_str
974   
975   
976   
977   
978       if (@type = 'debug')
979       begin
980           print " "
981           /*
982           ** Printing Debug Information:
983           */
984           exec sp_getmessage 18515, @print_str output
985           print @print_str
986           print " "
987           select @not_status = attrib_notify(@DUMPCOND_CLASS, 1, @OBJTYPE,
988                   1, NULL,
989                   NULL, NULL, NULL, NULL,
990                   NULL, NULL, NULL, NULL,
991                   @ATTR_FETCH)
992       end
993   
994       /*
995       ** Indicate success
996       */
997       return 0
998   
999   /*
1000  ** End sp_shmdumpdisp
1001  */
1002  


exec sp_procxmode 'sp_shmdumpdisp', 'AnyMode'
go

Grant Execute on sp_shmdumpdisp to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 301
 MEST 4 Empty String will be replaced by Single Space 577
 MEST 4 Empty String will be replaced by Single Space 620
 MEST 4 Empty String will be replaced by Single Space 623
 MEST 4 Empty String will be replaced by Single Space 761
 MEST 4 Empty String will be replaced by Single Space 809
 MEST 4 Empty String will be replaced by Single Space 830
 MEST 4 Empty String will be replaced by Single Space 832
 MEST 4 Empty String will be replaced by Single Space 968
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @cfilename: varchar(30) = varchar(255) 376
 MTYP 4 Assignment type mismatch Page_Cache: char(7) = varchar(20) 551
 MTYP 4 Assignment type mismatch Proc_Cache: char(7) = varchar(20) 552
 MTYP 4 Assignment type mismatch Unused_Space: char(7) = varchar(20) 553
 MTYP 4 Assignment type mismatch Halt: char(7) = varchar(20) 554
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {attribute}
Uncovered: [object, object_info2, object_info3, object_cinfo]
151
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 149
 QTYP 4 Comparison type mismatch smallint = int 149
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 151
 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 163
 QTYP 4 Comparison type mismatch smallint = int 163
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 682
 QTYP 4 Comparison type mismatch smallint = int 682
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 853
 QTYP 4 Comparison type mismatch smallint = int 853
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 855
 QTYP 4 Comparison type mismatch smallint = int 855
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 872
 QTYP 4 Comparison type mismatch smallint = int 872
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 874
 QTYP 4 Comparison type mismatch smallint = int 874
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 904
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 938
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 VRUN 4 Variable is read and not initialized @DUMPCOND_CLASS 149
 VRUN 4 Variable is read and not initialized @OBJTYPE 150
 VRUN 4 Variable is read and not initialized @CFG_PRIMARY 152
 VRUN 4 Variable is read and not initialized @cond_attrib 163
 VRUN 4 Variable is read and not initialized @cond_value 164
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_shmdumpdisp  
 MNER 3 No Error Check should check @@error after insert 266
 MNER 3 No Error Check should check @@error after insert 267
 MNER 3 No Error Check should check @@error after insert 268
 MNER 3 No Error Check should check @@error after insert 269
 MNER 3 No Error Check should check @@error after insert 270
 MNER 3 No Error Check should check @@error after insert 271
 MNER 3 No Error Check should check @@error after insert 272
 MNER 3 No Error Check should check @@error after insert 273
 MNER 3 No Error Check should check @@error after insert 274
 MNER 3 No Error Check should check @@error after insert 275
 MNER 3 No Error Check should check return value of exec 514
 MNER 3 No Error Check should check @@error after insert 544
 MNER 3 No Error Check should check return value of exec 798
 MNER 3 No Error Check should check return value of exec 803
 MNER 3 No Error Check should check return value of exec 820
 MNER 3 No Error Check should check return value of exec 829
 MNER 3 No Error Check should check return value of exec 842
 MNER 3 No Error Check should check return value of exec 861
 MNER 3 No Error Check should check return value of exec 880
 MNER 3 No Error Check should check return value of exec 886
 MNER 3 No Error Check should check return value of exec 984
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 334
 MUCO 3 Useless Code Useless Brackets 336
 MUCO 3 Useless Code Useless Brackets 341
 MUCO 3 Useless Code Useless Brackets 343
 MUCO 3 Useless Code Useless Brackets 348
 MUCO 3 Useless Code Useless Brackets 350
 MUCO 3 Useless Code Useless Brackets 355
 MUCO 3 Useless Code Useless Brackets 359
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 365
 MUCO 3 Useless Code Useless Brackets 370
 MUCO 3 Useless Code Useless Brackets 374
 MUCO 3 Useless Code Useless Brackets 378
 MUCO 3 Useless Code Useless Brackets 380
 MUCO 3 Useless Code Useless Brackets 407
 MUCO 3 Useless Code Useless Brackets 409
 MUCO 3 Useless Code Useless Brackets 411
 MUCO 3 Useless Code Useless Brackets 419
 MUCO 3 Useless Code Useless Brackets 424
 MUCO 3 Useless Code Useless Brackets 436
 MUCO 3 Useless Code Useless Brackets 442
 MUCO 3 Useless Code Useless Brackets 448
 MUCO 3 Useless Code Useless Brackets 454
 MUCO 3 Useless Code Useless Brackets 460
 MUCO 3 Useless Code Useless Brackets 466
 MUCO 3 Useless Code Useless Brackets 472
 MUCO 3 Useless Code Useless Brackets 478
 MUCO 3 Useless Code Useless Brackets 504
 MUCO 3 Useless Code Useless Brackets 531
 MUCO 3 Useless Code Useless Brackets 587
 MUCO 3 Useless Code Useless Brackets 596
 MUCO 3 Useless Code Useless Brackets 643
 MUCO 3 Useless Code Useless Brackets 645
 MUCO 3 Useless Code Useless Brackets 656
 MUCO 3 Useless Code Useless Brackets 664
 MUCO 3 Useless Code Useless Brackets 666
 MUCO 3 Useless Code Useless Brackets 692
 MUCO 3 Useless Code Useless Brackets 696
 MUCO 3 Useless Code Useless Brackets 705
 MUCO 3 Useless Code Useless Brackets 711
 MUCO 3 Useless Code Useless Brackets 717
 MUCO 3 Useless Code Useless Brackets 723
 MUCO 3 Useless Code Useless Brackets 729
 MUCO 3 Useless Code Useless Brackets 735
 MUCO 3 Useless Code Useless Brackets 741
 MUCO 3 Useless Code Useless Brackets 745
 MUCO 3 Useless Code Useless Brackets 823
 MUCO 3 Useless Code Useless Brackets 906
 MUCO 3 Useless Code Useless Brackets 943
 MUCO 3 Useless Code Useless Brackets 958
 MUCO 3 Useless Code Useless Brackets 962
 MUCO 3 Useless Code Useless Brackets 978
 MUIN 3 Column created using implicit nullability 118
 MUIN 3 Column created using implicit nullability 125
 QAFM 3 Var Assignment from potentially many rows 248
 QAFM 3 Var Assignment from potentially many rows 668
 QAFM 3 Var Assignment from potentially many rows 677
 QAFM 3 Var Assignment from potentially many rows 785
 QAFM 3 Var Assignment from potentially many rows 813
 QAFM 3 Var Assignment from potentially many rows 835
 QAFM 3 Var Assignment from potentially many rows 851
 QAFM 3 Var Assignment from potentially many rows 870
 QAFM 3 Var Assignment from potentially many rows 902
 QNAJ 3 Not using ANSI Inner Join 148
 QNAJ 3 Not using ANSI Inner Join 937
 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}
161
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
250
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
672
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
681
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
787
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
815
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
837
 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}
853
 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}
872
 VNRD 3 Variable is not read @CFG_PRIMARY 196
 VNRD 3 Variable is not read @CFG_DEFAULT 221
 VNRD 3 Variable is not read @ATTR_ADD 228
 VNRD 3 Variable is not read @ATTR_CHANGE 229
 VNRD 3 Variable is not read @ATTR_DROP 230
 VNRD 3 Variable is not read @cond_attrib 604
 VNRD 3 Variable is not read @not_status 987
 VUNU 3 Variable is not used @shm_size 35
 VUNU 3 Variable is not used @current_mode 108
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 147
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 171
 CUPD 2 Updatable Cursor Marker (updatable by default) 159
 MTR1 2 Metrics: Comments Ratio Comments: 33% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 84 = 84dec - 2exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 423 21
 PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 936

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysattributes (1)  
reads table master..sysconfigures (1)  
reads table master..syscurconfigs (1)  
read_writes table tempdb..#conditions (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
read_writes table tempdb..#attname (1) 
calls proc sybsystemprocs..sp_shmdumpsize  
   reads table master..sysconfigures (1)  
   reads table master..sysattributes (1)  
   reads table master..syscurconfigs (1)  
reads table master..sysmessages (1)  

CALLERS
called by proc sybsystemprocs..sp_shmdumpconfig