DatabaseProcApplicationCreatedLinks
sybsystemprocssp_opt_querystats_main  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** SP_OPT_QUERYSTATS_MAIN
4     **
5     ** This is the main stored procedure for the sp_opt_querystats utility.
6     ** This stored procedure handles verification of the conditions for
7     ** executing the optimizer diagnostic utility are present and then
8     ** creates and executes the Job Scheduler job to generate the diagnostic
9     ** information.  Once the job has completed, this stored procedure
10    ** retrieves the results from the Job Scheduler database and returns
11    ** then to the caller.
12    **
13    ** History:
14    **    4/28/2011   pdorfman   written
15    */
16    create or replace procedure sp_opt_querystats_main
17        @query varchar(16384),
18        @input_params varchar(2048) = "all",
19        @target_database varchar(30) = null,
20        @user_name varchar(30) = null
21    as
22        declare @job_name varchar(80)
23        declare @option_string varchar(4096)
24        declare @server_name varchar(256)
25        declare @jobid int
26        declare @ret int
27        declare @first_row int
28        declare @last_row int
29        declare @offset int
30        declare @options int
31        declare @begin int
32        declare @length int
33        declare @current_parameter varchar(50)
34        declare @current_option int
35        declare @input_length int
36        declare @position int
37        declare @command varchar(1024)
38        declare @optgoal_count int
39        declare @return_code int
40        declare @use_db_cmd varchar(50)
41        declare @exit_code int
42        declare @max_job_output int
43        declare @set_user_cmd varchar(60)
44    
45        declare @statio_string varchar(30)
46        declare @stattime_string varchar(30)
47        declare @showplan_string varchar(30)
48        declare @missingstats_string varchar(30)
49        declare @resource_string varchar(30)
50        declare @plancost_string varchar(30)
51        declare @option_show_string varchar(30)
52        declare @exec_string varchar(30)
53        declare @option_show_long_string varchar(30)
54        declare @allrows_mix_string varchar(30)
55        declare @allrows_oltp_string varchar(30)
56        declare @allrows_dss_string varchar(30)
57        declare @diagmode_string varchar(30)
58        declare @option_all_string varchar(30)
59        declare @option_allexec_string varchar(30)
60        declare @showdata_string varchar(30)
61        declare @switches_string varchar(30)
62        declare @quoted_identifier_string varchar(30)
63    
64        /*
65        ** The following variable definitions and assignments must correspond
66        ** exactly to those in sp_opt_querystats_job
67        */
68        declare @query_evaluating_const int
69        declare @query_pending_const int
70        declare @query_finished_const int
71    
72        declare @statio_const int
73        declare @stattime_const int
74        declare @showplan_const int
75        declare @missingstats_const int
76        declare @resource_const int
77        declare @plancost_const int
78        declare @option_show_const int
79        declare @noexec_const int
80        declare @option_show_long_const int
81        declare @allrows_mix_const int
82        declare @allrows_oltp_const int
83        declare @allrows_dss_const int
84        declare @diagmode_const int
85        declare @nodata_const int
86        declare @switches_const int
87        declare @quoted_identifier_const int
88    
89        declare @line varchar(2000)
90        declare @rest varchar(2000)
91        declare @outtext varchar(4000) -- Large enough to handle 2 x job_output
92        declare @rowbuf varchar(2000)
93        declare @seq_no int
94        declare @patindex int
95        declare @row_number int
96        declare @newline char(1)
97    
98        select @newline = char(10) -- Constant for newline character
99    
100       select @statio_const = 1
101       select @stattime_const = 2
102       select @showplan_const = 4
103       select @missingstats_const = 8
104       select @resource_const = 16
105       select @plancost_const = 32
106       select @option_show_const = 64
107       select @noexec_const = 128
108       select @option_show_long_const = 256
109       select @allrows_mix_const = 512
110       select @allrows_oltp_const = 1024
111       select @allrows_dss_const = 2048
112       select @diagmode_const = 4096
113       select @nodata_const = 8192
114       select @switches_const = 16384
115       select @quoted_identifier_const = 32768
116   
117       select @query_pending_const = 0
118       select @query_evaluating_const = 1
119       select @query_finished_const = 2
120       /*
121       ** End common constant definitions
122       */
123   
124       select @statio_string = "statio"
125       select @stattime_string = "stattime"
126       select @showplan_string = "showplan"
127       select @missingstats_string = "missingstats"
128       select @resource_string = "resource"
129       select @plancost_string = "plancost"
130       select @option_show_string = "option_show"
131       select @exec_string = "exec"
132       select @option_show_long_string = "option_show_long"
133       select @allrows_mix_string = "allrows_mix"
134       select @allrows_oltp_string = "allrows_oltp"
135       select @allrows_dss_string = "allrows_dss"
136       select @diagmode_string = "diagmode"
137       select @option_all_string = "all"
138       select @option_allexec_string = "allexec"
139       select @showdata_string = "showdata"
140       select @switches_string = "switches"
141       select @quoted_identifier_string = "quoted_identifier"
142   
143       select @options = 0
144       select @return_code = 0
145   
146       if (@query = "help")
147       begin
148           print "sp_opt_querystats stored procedure"
149           print ""
150           print "Reports information about the optimization of a query."
151           print ""
152           print '   Usage: sp_opt_querystats "<query text>" | help [, "<diagnostic options>" | null [, <database name> [, <user name>]]]'
153           print ""
154           print "   Where:"
155           print ""
156           print "    <query text> (required) is the text of the query to be analyzed."
157           print ""
158           print "    <diagnostic options>  (optional) is a comma-separated list containing one or more of the following strings:"
159           print ""
160           print "     - statio"
161           print "     - stattime"
162           print "     - showplan"
163           print "     - missingstats"
164           print "     - resource"
165           print "     - plancost"
166           print "     - showdata"
167           print "     - exec"
168           print "     - option_show_long"
169           print "     - option_show"
170           print "     - allrows_mix"
171           print "     - allrows_oltp"
172           print "     - allrows_dss"
173           print "     - diagmode"
174           print "     - switches"
175           print "     - quoted_identifier"
176           print "     - all"
177           print "     - allexec"
178           print ""
179           print "    Note that the values within the following lists are mutually exclusive. You can only specify one of these:"
180           print "       {allrows_mix, allrows_oltp, allrows_dss}"
181           print "       {option_show, option_show_long}"
182           print "       {all, allexec}"
183           print ""
184           print "   <database name> (optional) can be used to specify the name of the database in which the query should be executed.  This is used when the table names in the query are not fully qualified.  Note that all unqualified tables must be in a single database that is specified by this parameter."
185           print ""
186           print "   <user name> (optional) can be used to specify the name of the user under which the query should be executed. The user must be a valid user in the database specified by the database name parameter."
187           print ""
188           print "   Note that the database name parameter cannot be NULL if user name is specified."
189           print ""
190           print "For example:"
191           print ""
192           print '  sp_opt_querystats "select * from pubs2.dbo.authors"'
193           print "or"
194           print '	 sp_opt_querystats "select * from pubs2.dbo.authors", "showplan,statio,option_show,plancost"'
195           print "or"
196           print '  sp_opt_querystats "select * from authors", "all", pubs2'
197   
198           return (0)
199       end
200   
201       /*
202       ** Verify that the user has sufficient permissions to
203       ** execute DBCC commands used by the job
204       */
205       if (proc_role("sa_role") < 1)
206       begin
207           return - 1
208       end
209   
210       /*
211       ** Verify that the user has the required Job Scheduler role
212       */
213       if ((charindex("js_admin_role", show_role()) = 0)
214               and (charindex("js_user_role", show_role()) = 0))
215       begin
216           /*
217           ** "ERROR: You must have either 'js_user_role' or 'js_admin_role' to run
218           **  this stored procedure."
219           */
220           raiserror 17094
221           return - 1
222       end
223   
224       /*
225       ** Verify that the Job Scheduler is enabled.  Note that this does not
226       ** guarantee that the Job Scheduler is actually running.  See the next check.
227       */
228       if not exists (select 1 from master.dbo.syscurconfigs cc, master.dbo.sysconfigures sc
229               where cc.config = sc.config and cc.value = 1
230                   and sc.comment = 'enable job scheduler')
231       begin
232           /*
233           ** "ERROR: The Job Scheduler is not enabled on this server. The Job
234           **  Scheduler must be enabled to execute this stored procedure. "
235           */
236           raiserror 17095
237           return - 1
238       end
239   
240       /*
241       ** Verify that the Job Scheduler is running
242       */
243       if not exists (select 1 from master.dbo.sysprocesses where cmd = "JOB SCHEDULER")
244       begin
245           /*
246           ** "ERROR: The Job Scheduler is not running. Contact a system administrator
247           **  to resolve this problem."
248           */
249           raiserror 17009
250           return - 1
251       end
252   
253       /*
254       ** Parse the input parameters first and set the options bit mask
255       */
256       if (@input_params is null)
257       begin
258           select @input_params = @option_all_string
259       end
260   
261       /*
262       ** Enable nodata and noexec by default.  These will
263       ** be disabled if the user specifies exec or showdata options.
264       */
265       select @options = @nodata_const + @noexec_const
266   
267       select @input_params = ltrim(rtrim(@input_params))
268       select @input_length = char_length(@input_params)
269       select @length = @input_length
270       select @optgoal_count = 0
271       select @begin = 1
272   
273       while (@length > 0)
274       begin
275           select @position = charindex(",", substring(@input_params, @begin,
276                       (@input_length - @begin)))
277   
278           if (@position != 0)
279           begin
280               select @length = @position - 1
281               select @current_parameter = rtrim(ltrim(substring(@input_params, @begin,
282                               @length)))
283           end
284           else
285           begin
286               select @length = @input_length - @begin + 1
287               select @current_parameter = rtrim(ltrim(substring(@input_params, @begin,
288                               @length)))
289           end
290   
291           if (@current_parameter = @option_all_string
292                   or @current_parameter = @option_allexec_string)
293           begin
294               select @options = @options
295                   | @statio_const
296                   | @stattime_const
297                   | @showplan_const
298                   | @missingstats_const
299                   | @resource_const
300                   | @plancost_const
301                   | @option_show_long_const
302                   | @switches_const
303   
304               if (@current_parameter = @option_allexec_string)
305               begin
306                   select @options = @options ^ @noexec_const
307               end
308           end
309           else if (@current_parameter = @statio_string)
310               select @options = @options | @statio_const
311           else if (@current_parameter = @stattime_string)
312               select @options = @options | @stattime_const
313           else if (@current_parameter = @showplan_string)
314               select @options = @options | @showplan_const
315           else if (@current_parameter = @missingstats_string)
316               select @options = @options | @missingstats_const
317           else if (@current_parameter = @resource_string)
318               select @options = @options | @resource_const
319           else if (@current_parameter = @plancost_string)
320               select @options = @options | @plancost_const
321           else if (@current_parameter = @option_show_string)
322               select @options = @options | @option_show_const
323           else if (@current_parameter = @switches_string)
324               select @options = @options | @switches_const
325           else if (@current_parameter = @quoted_identifier_string)
326               select @options = @options | @quoted_identifier_const
327           else if (@current_parameter = @exec_string)
328               -- Disable the default noexec option
329               select @options = @options ^ @noexec_const
330           else if (@current_parameter = @option_show_long_string)
331               select @options = @options | @option_show_long_const
332           else if (@current_parameter = @showdata_string)
333               -- Disable the default nodata option
334               select @options = @options ^ @nodata_const
335           else if (@current_parameter = @allrows_mix_string)
336           begin
337               select @options = @options | @allrows_mix_const
338               select @optgoal_count = @optgoal_count + 1
339           end
340           else if (@current_parameter = @allrows_oltp_string)
341           begin
342               select @options = @options | @allrows_oltp_const
343               select @optgoal_count = @optgoal_count + 1
344           end
345           else if (@current_parameter = @allrows_dss_string)
346           begin
347               select @options = @options | @allrows_dss_const
348               select @optgoal_count = @optgoal_count + 1
349           end
350           else if (@current_parameter = @diagmode_string)
351               select @options = @options | @diagmode_const
352           else
353           begin
354               /*
355               ** "ERROR: Invalid option parameter: '%1!'", @current_parameter
356               */
357               raiserror 17013, @current_parameter
358               return - 1
359           end
360   
361           select @begin = @begin + @length + 1
362   
363           if (@begin > @input_length)
364           begin
365               break
366           end
367       end
368   
369       if ((@options & @option_show_const != 0)
370               and (@options & @option_show_long_const != 0))
371       begin
372           /*
373           ** "ERROR: Both '%1!' and '%2!' were specified. You can only use one of
374           **  these options at a time.",
375           */
376           raiserror 17014, @option_show_string, @option_show_long_string
377           return - 1
378       end
379   
380       if (@optgoal_count > 1)
381       begin
382           /*
383           ** "ERROR: You have specified more than one optmizer goal. Only one
384           ** optimizer goal can be specified at a time."
385           */
386           raiserror 17049
387           return - 1
388       end
389   
390       /*
391       ** Create the job that will be used to execute this query
392       */
393       if (@@servername is null)
394       begin
395           /*
396           ** "ERROR: The @@servername variable is null.  The server name must be set."
397           */
398           raiserror 17071
399           return - 1
400       end
401   
402       select @server_name = @@servername
403   
404       /*
405       ** Create a unique job name and then create the job definition
406       */
407   
408       select @job_name = "opt_qstat_" +
409           convert(varchar(4), datepart(yy, getdate())) +
410           convert(varchar(2), datepart(mm, getdate())) +
411           convert(varchar(2), datepart(dd, getdate())) +
412           convert(varchar(2), datepart(hh, getdate())) +
413           convert(varchar(2), datepart(mi, getdate())) +
414           convert(varchar(2), datepart(ss, getdate())) +
415           convert(varchar(4), @@spid)
416   
417       /*
418       ** Only create a use database command if the target_database is not null
419       */
420       if (@target_database is not null)
421       begin
422           if not exists (select 1 from master.dbo.sysdatabases
423                   where name = @target_database)
424           begin
425   
426               /*
427               ** ERROR: Database '%1!' does not exist on this server.", @target_database
428               */
429               raiserror 17097, @target_database
430               select @return_code = - 1
431               return - 1
432           end
433   
434           select @use_db_cmd = "use " + @target_database + " go "
435       end
436       else
437       begin
438           select @use_db_cmd = ""
439       end
440   
441       if (@user_name is not null)
442       begin
443           if (@target_database is null)
444           begin
445               /*
446               ** ERROR: The user name can only be specified if the database name is
447               ** also specified.
448               */
449               raiserror 17099
450               return - 1
451           end
452   
453           select @set_user_cmd = ' setuser "' + @user_name + '" go '
454       end
455       else
456       begin
457           select @set_user_cmd = " "
458       end
459   
460       select @option_string = "jcmd=select jobid = @js_sjobid into #odu1 go "
461           + @use_db_cmd
462           + @set_user_cmd -- must be executed after the user database command
463           + " declare @jid int select @jid = jobid from #odu1 exec sp_opt_querystats_job @jid,server="
464           + @server_name
465           + ",sjproperties=shared_run"
466   
467       exec @jobid = sybmgmtdb.dbo.sp_sjobcreate @name = @job_name, @option = @option_string
468   
469       if (@jobid < 0)
470       begin
471           /*
472           ** "ERROR: Failed to create job '%1!' with option '%2!' with error code: %3!"
473           */
474           raiserror 17073, @job_name, @option_string, @jobid
475           return - 1
476       end
477   
478       /*
479       ** Determine whether the work table already exists.  If it does
480       ** not, create it now.
481       */
482       if not exists (select 1 from tempdb.dbo.sysobjects where name = @job_name)
483       begin
484           select @command =
485               "create table tempdb.." + @job_name + " " +
486               "(
487   	  status unsigned int, -- 0 = not executed; 1 = executing; 2 = executed
488   	  jobid int,
489   	  options unsigned int,
490   	  query text
491   	)"
492   
493           exec (@command)
494       end
495   
496       if not exists (select 1 from tempdb.dbo.sysobjects where name = @job_name)
497       begin
498           /*
499           ** "ERROR: Creation of work table tempdb..%1! failed", @job_name
500           */
501           raiserror 17072, @job_name
502           return - 1
503       end
504   
505       /*
506       * Insert the query into the work table now that we have the jobid
507       */
508       select @command = "insert tempdb.." + @job_name + " " +
509           "values (@query_pending_const, @jobid, @options, @query)"
510       exec (@command)
511   
512       /*
513       ** Call the Job Scheduler to execute the analysis of the query
514       */
515       exec @ret = sybmgmtdb.dbo.sp_sjobcontrol @name = @job_name, @option = "run_now"
516   
517       if (@ret < 0)
518       begin
519           /*
520           ** "ERROR: An error occurred while executing the job.  Job name: '%1!',
521           **  jobid: %2!.  Error: %3!",
522           */
523           raiserror 17074, @job_name, @jobid, @ret
524           select @return_code = - 1
525           goto cleanup
526       end
527   
528       /*
529       ** Wait until the job is registered in the js_history table
530       */
531       while not exists (select jsh_state from sybmgmtdb.dbo.js_history
532               where jsh_sjobid = @jobid)
533       begin
534           waitfor delay "00:00:01"
535       end
536   
537       /*
538       ** Do not continue until the JS Agent completes handling job output
539       */
540       while exists (select jsh_state from sybmgmtdb.dbo.js_history
541               where jsh_sjobid = @jobid
542                   and jsh_state in ("W", "Q", "B", "R1", "R2"))
543       begin
544           waitfor delay "00:00:01"
545       end
546   
547       /*
548       ** Wait for the JS Task to notify the JS Agent that processing is complete.
549       ** A delay is built into the JS and this procedure needs to wait until
550       ** the JS Task and Agent are synchronized before proceeding.
551       */
552       waitfor delay "00:00:05"
553   
554       /*
555       ** Check the exit code for the job and raise an error if it was
556       ** not successful.
557       */
558       select @exit_code = h.jsh_exit_code
559       from sybmgmtdb.dbo.js_history h
560       where h.jsh_sjobid = @jobid
561   
562       if (@exit_code != 0)
563       begin
564           /*
565           ** ERROR: The job (jobid '%1!') failed with exit code '%2!'. Verify that 
566           ** the current user has a valid externlogin defined for this server and 
567           ** review the Job Scheduler agent log for any errors.
568           */
569           raiserror 17098, @jobid, @exit_code
570   
571           select @return_code = - 1
572           goto cleanup2
573       end
574   
575       /*
576       ** Extract the output from the job from the Job Scheduler history 
577       ** table and return it to the caller.
578       */
579       select seq_no = o.jsout_seqno,
580           job_output = convert(varchar(1800), rtrim(o.jsout_text))
581       into #temp_history_stage
582       from sybmgmtdb.dbo.js_output o,
583           sybmgmtdb.dbo.js_history h,
584           sybmgmtdb.dbo.js_scheduledjobs j
585       where j.sjob_id = h.jsh_sjobid
586           and h.jsh_sjobid = @jobid
587           and j.sjob_name = @job_name
588           and o.jsout_exid = h.jsh_exid
589       order by o.jsout_seqno asc
590   
591       /*
592       ** Check to see whether there was any job output
593       ** This must immediately follow the select into #temp_history
594       */
595       if (@@rowcount = 0)
596       begin
597           /*
598           ** "ERROR: No output found for job (jobid: '%1!'). The job may have failed
599           **  or the Job Scheduler may not be running. Contact a system administrator
600           **  to verify that the Job Scheduler is running and review the Job Scheduler
601           **  Agent log for errors.", 
602           */
603           raiserror 17076, @jobid
604           select @return_code = - 1
605           goto cleanup
606       end
607   
608       /*
609       ** Create the #temp_history table
610       */
611       select * into #temp_history from #temp_history_stage where 1 = 2
612   
613       /*
614       ** Now divide the job output into separate rows and join lines that were
615       ** split between multiple rows in the js_output table. Lines may have been
616       ** split because part of the line fell at the end of the 901 character
617       ** jsout_text column.  After this processing, each line in the job output
618       ** will be in a separate row in the #temp_history table.
619       */
620   
621       declare jshist_cur cursor for
622       select seq_no, rtrim(job_output)
623       from #temp_history_stage
624       order by seq_no asc
625   
626       open jshist_cur
627       fetch jshist_cur into @seq_no, @outtext
628   
629       select @row_number = 0
630   
631       while @@sqlstatus = 0
632       begin
633           --Attach any remainder from last line processed
634           if @rest is not null
635           begin
636               select @outtext = @rest + @outtext
637               select @rest = null
638           end
639   
640           --Split current line at newline characters
641           while len(@outtext) > 0
642           begin
643               set @patindex = charindex(@newline, @outtext)
644               if @patindex > 0
645               begin
646                   select @line = substring(@outtext, 1, @patindex - 1)
647                   select @outtext = substring(@outtext, len(@line + @newline) + 1, len(@outtext))
648                   if (len(@line) > 0)
649                   begin
650                       select @row_number = @row_number + 1
651                       insert #temp_history values (@row_number, @line)
652                   end
653               end
654               else
655               begin
656                   --Save the rest of this line and prefix to next line
657                   select @rest = substring(@outtext, 1, len(@outtext) - len(@newline) + 1)
658                   select @outtext = NULL
659               end
660           end
661           fetch jshist_cur into @seq_no, @outtext
662       end
663   
664       -- Save the last line
665       if @rest is not null
666       begin
667           select @row_number = @row_number + 1
668           insert #temp_history values (@row_number, @rest)
669       end
670   
671       close jshist_cur
672       deallocate jshist_cur
673       drop table #temp_history_stage
674   
675       /*
676       ** Locate the rows containing the begin and end markers and delete
677       ** all rows before and after these rows.  They only contain progress
678       ** messages.
679       */
680       select @first_row = seq_no
681       from #temp_history
682       where job_output like "%BEGIN QUERY ANALYSIS%"
683   
684       select @last_row = seq_no
685       from #temp_history
686       where job_output like "%END QUERY ANALYSIS%"
687   
688       /*
689       ** If the diagnostic results are not found report an error and display the
690       ** job output in case this contains useful information to determine why
691       ** the job did not execute as expected.
692       */
693       if (@first_row is null or @last_row is null)
694       begin
695           select @max_job_output = cc.value
696           from master.dbo.syscurconfigs cc, master.dbo.sysconfigures sc
697           where cc.config = sc.config
698               and sc.comment = 'maximum job output'
699   
700           /*
701           ** ERROR: The optimizer diagnostics were incomplete.  This may be because 
702           ** the value of the 'maximum job output' configuration parameter (%1!) is 
703           ** smaller than the size of the diagnostic output. Consider increasing the 
704           ** value of 'maximum job output'.
705           */
706           raiserror 17077, @max_job_output
707   
708           select @return_code = - 1
709   
710           if (@options & @diagmode_const != 0)
711           begin
712               goto diagnostic_mode
713           end
714   
715           goto cleanup
716       end
717       else if (@options & @diagmode_const = 0)
718       begin
719           delete #temp_history
720           where seq_no < @first_row
721               or seq_no > @last_row
722   
723           /*
724           ** Update the rows containing the begin an end markers to
725           ** remove the text that lies before the beginning and
726           ** after the end markers.
727           */
728   
729           select @offset = patindex("%BEGIN QUERY ANALYSIS%", job_output)
730           from #temp_history
731           where seq_no = @first_row
732   
733           select @offset = @offset + len("BEGIN QUERY ANALYSIS")
734   
735           update #temp_history
736           set job_output = "[ BEGIN QUERY ANALYSIS ]" + @newline +
737               substring(job_output, (@offset + 2), (1800 - @offset - 2))
738           where seq_no = @first_row
739   
740   
741           select @offset = patindex("%END QUERY ANALYSIS%", job_output)
742           from #temp_history
743           where seq_no = @last_row
744   
745           select @offset = @offset - 2
746   
747           update #temp_history
748           set job_output = substring(job_output, 1, (@offset - 1)) +
749               @newline + "[ END QUERY ANALYSIS ]"
750           where seq_no = @last_row
751       end
752   
753       /*
754       ** Now that we have found the beginning and end of the diagnostic output
755       ** consolidate as many lines as possible into a single row in the 
756       ** history table before displaying the results.
757       */
758   
759       declare temp_hist_curs cursor for
760       select seq_no, job_output
761       from #temp_history
762       order by seq_no asc
763   
764       create table #temp_history2(seq_no int, job_output varchar(1900))
765   
766       open temp_hist_curs
767   
768       select @row_number = 0
769       select @rowbuf = ""
770       fetch temp_hist_curs into @seq_no, @outtext
771   
772       while @@sqlstatus = 0
773       begin
774           if (charindex(@newline, @rowbuf) = 0)
775           begin
776               select @rowbuf = @rowbuf + @newline
777           end
778   
779           /*
780           ** See whether the row buffer can hold the current line plus newline char
781           */
782           if (len(@rowbuf) + len(@outtext) < 1900)
783           begin
784               select @rowbuf = @rowbuf + @outtext + @newline
785           end
786           else
787           begin
788               /*
789               ** Insert the row buffer into the history table when it's full
790               ** and save the current line in @rowbuf
791               */
792               select @row_number = @row_number + 1
793               insert #temp_history2 values (@row_number, @rowbuf)
794   
795               if (@@error != 0)
796               begin
797                   break
798               end
799               select @rowbuf = @outtext
800           end
801           fetch temp_hist_curs into @seq_no, @outtext
802       end
803   
804       /*
805       ** Insert final row into history table if row buffer is not empty
806       */
807       if (len(@rowbuf) > 0)
808       begin
809           select @row_number = @row_number + 1
810           insert #temp_history2 values (@row_number, @rowbuf)
811       end
812   
813       close temp_hist_curs
814       deallocate temp_hist_curs
815   
816   diagnostic_mode:
817   
818       /*
819       ** Now return the output to the caller
820       */
821       select rtrim(job_output)
822       from #temp_history2
823       order by seq_no asc
824   
825   cleanup:
826   
827       drop table #temp_history
828   
829   cleanup2:
830   
831       /*
832       ** Drop the output after we have displayed it
833       */
834   
835       exec @ret = sybmgmtdb.dbo.sp_sjobhistory @name = @job_name, @option = "drop"
836   
837       if (@ret != 0)
838       begin
839           /*
840           ** "ERROR: The call to sp_sjobhistory to drop job history '%1!' failed
841           ** with error code %2!",
842           */
843           raiserror 17078, @job_name, @ret
844           select @return_code = - 1
845       end
846   
847       exec @ret = sybmgmtdb.dbo.sp_sjobdrop @name = @job_name, @option = "all"
848   
849       if (@ret != 0)
850       begin
851           /*
852           **  "ERROR: call to sp_sjobdrop to drop job '%1!' failed with error code %2!"
853           */
854           raiserror 17096, @job_name, @ret
855           select @return_code = - 1
856       end
857   
858       select @command = "drop table tempdb.." + @job_name
859       exec (@command)
860   
861       return @return_code
862   


exec sp_procxmode 'sp_opt_querystats_main', 'AnyMode'
go

Grant Execute on sp_opt_querystats_main to public
go
RESULT SETS
sp_opt_querystats_main_rset_001

DEFECTS
 PERR 6 Parsing Error Could not find definition for table sybmgmtdb..js_history 531
 PERR 6 Parsing Error Could not find definition for table sybmgmtdb..js_history 540
 PERR 6 Parsing Error Could not find definition for table sybmgmtdb..js_history 559
 PERR 6 Parsing Error Could not find definition for table sybmgmtdb..js_output 582
 PERR 6 Parsing Error Could not find COLUMN job_output in query's tables 682
 PERR 6 Parsing Error Could not find COLUMN job_output in query's tables 686
 PERR 6 Parsing Error Could not find COLUMN seq_no in query's tables 720
 PERR 6 Parsing Error Could not find COLUMN seq_no in query's tables 731
 PERR 6 Parsing Error Could not find COLUMN seq_no in query's tables 738
 PERR 6 Parsing Error Could not find COLUMN seq_no in query's tables 743
 PERR 6 Parsing Error Could not find COLUMN seq_no in query's tables 750
 PERR 6 Parsing Error Could not find COLUMN job_output in the query's tables 760
 PERR 6 Parsing Error Could not find COLUMN seq_no in the query's tables 760
 MBRV 4 Bad return value [-1-99] return - 1 207
 MBRV 4 Bad return value [-1-99] return - 1 221
 MBRV 4 Bad return value [-1-99] return - 1 237
 MBRV 4 Bad return value [-1-99] return - 1 250
 MBRV 4 Bad return value [-1-99] return - 1 358
 MBRV 4 Bad return value [-1-99] return - 1 377
 MBRV 4 Bad return value [-1-99] return - 1 387
 MBRV 4 Bad return value [-1-99] return - 1 399
 MBRV 4 Bad return value [-1-99] return - 1 431
 MBRV 4 Bad return value [-1-99] return - 1 450
 MBRV 4 Bad return value [-1-99] return - 1 475
 MBRV 4 Bad return value [-1-99] return - 1 502
 MEST 4 Empty String will be replaced by Single Space 149
 MEST 4 Empty String will be replaced by Single Space 151
 MEST 4 Empty String will be replaced by Single Space 153
 MEST 4 Empty String will be replaced by Single Space 155
 MEST 4 Empty String will be replaced by Single Space 157
 MEST 4 Empty String will be replaced by Single Space 159
 MEST 4 Empty String will be replaced by Single Space 178
 MEST 4 Empty String will be replaced by Single Space 183
 MEST 4 Empty String will be replaced by Single Space 185
 MEST 4 Empty String will be replaced by Single Space 187
 MEST 4 Empty String will be replaced by Single Space 189
 MEST 4 Empty String will be replaced by Single Space 191
 MEST 4 Empty String will be replaced by Single Space 438
 MEST 4 Empty String will be replaced by Single Space 769
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch job_output: varchar(1900) = varchar(2000) 793
 MTYP 4 Assignment type mismatch @rowbuf: varchar(2000) = varchar(4000) 799
 MTYP 4 Assignment type mismatch job_output: varchar(1900) = varchar(2000) 810
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 482
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 496
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 532
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 541
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 560
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 586
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(80) 587
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 720
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 721
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 731
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 738
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 743
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 750
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 VRUN 4 Variable is read and not initialized @rest 634
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jshist_cur 622
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause temp_hist_curs 760
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 16
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysprocesses  
 MGTP 3 Grant to public sybsystemprocs..sp_opt_querystats_main  
 MGTP 3 Grant to public tempdb..sysobjects  
 MNER 3 No Error Check should check @@error after select into 579
 MNER 3 No Error Check should check @@error after select into 611
 MNER 3 No Error Check should check @@error after insert 651
 MNER 3 No Error Check should check @@error after insert 668
 MNER 3 No Error Check should check @@error after delete 719
 MNER 3 No Error Check should check @@error after update 735
 MNER 3 No Error Check should check @@error after update 747
 MNER 3 No Error Check should check @@error after insert 810
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 256
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 278
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 311
 MUCO 3 Useless Code Useless Brackets 313
 MUCO 3 Useless Code Useless Brackets 315
 MUCO 3 Useless Code Useless Brackets 317
 MUCO 3 Useless Code Useless Brackets 319
 MUCO 3 Useless Code Useless Brackets 321
 MUCO 3 Useless Code Useless Brackets 323
 MUCO 3 Useless Code Useless Brackets 325
 MUCO 3 Useless Code Useless Brackets 327
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 332
 MUCO 3 Useless Code Useless Brackets 335
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 345
 MUCO 3 Useless Code Useless Brackets 350
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 369
 MUCO 3 Useless Code Useless Brackets 380
 MUCO 3 Useless Code Useless Brackets 393
 MUCO 3 Useless Code Useless Brackets 420
 MUCO 3 Useless Code Useless Brackets 441
 MUCO 3 Useless Code Useless Brackets 443
 MUCO 3 Useless Code Useless Brackets 469
 MUCO 3 Useless Code Useless Brackets 517
 MUCO 3 Useless Code Useless Brackets 562
 MUCO 3 Useless Code Useless Brackets 595
 MUCO 3 Useless Code Useless Brackets 648
 MUCO 3 Useless Code Useless Brackets 693
 MUCO 3 Useless Code Useless Brackets 710
 MUCO 3 Useless Code Useless Brackets 717
 MUCO 3 Useless Code Useless Brackets 774
 MUCO 3 Useless Code Useless Brackets 782
 MUCO 3 Useless Code Useless Brackets 795
 MUCO 3 Useless Code Useless Brackets 807
 MUCO 3 Useless Code Useless Brackets 837
 MUCO 3 Useless Code Useless Brackets 849
 MUIN 3 Column created using implicit nullability 764
 MZMB 3 Zombie: use of non-existent object master..sp_sjobcreate 467
 MZMB 3 Zombie: use of non-existent object master..sp_sjobcontrol 515
 MZMB 3 Zombie: use of non-existent object sybmgmtdb..js_history 531
 MZMB 3 Zombie: use of non-existent object sybmgmtdb..js_history 540
 MZMB 3 Zombie: use of non-existent object sybmgmtdb..js_history 559
 MZMB 3 Zombie: use of non-existent object sybmgmtdb..js_output 582
 MZMB 3 Zombie: use of non-existent object sybmgmtdb..js_history 583
 MZMB 3 Zombie: use of non-existent object sybmgmtdb..js_scheduledjobs 584
 MZMB 3 Zombie: use of non-existent object master..sp_sjobhistory 835
 MZMB 3 Zombie: use of non-existent object master..sp_sjobdrop 847
 QAFM 3 Var Assignment from potentially many rows 695
 QNAJ 3 Not using ANSI Inner Join 228
 QNAJ 3 Not using ANSI Inner Join 582
 QNAJ 3 Not using ANSI Inner Join 696
 QNAM 3 Select expression has no name rtrim(job_output) 821
 QPNC 3 No column in condition 611
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
482
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
496
 VNRD 3 Variable is not read @query_pending_const 117
 VNRD 3 Variable is not read @query_evaluating_const 118
 VNRD 3 Variable is not read @query_finished_const 119
 VUNU 3 Variable is not used @current_option 34
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 622
 CRDO 2 Read Only Cursor Marker (has an 'order by' clause) 760
 MDYS 2 Dynamic SQL Marker 493
 MDYS 2 Dynamic SQL Marker 510
 MDYS 2 Dynamic SQL Marker 859
 MRST 2 Result Set Marker 821
 MSTA 2 Use of Star Marker 611
 MSUB 2 Subquery Marker 228
 MSUB 2 Subquery Marker 243
 MSUB 2 Subquery Marker 422
 MSUB 2 Subquery Marker 482
 MSUB 2 Subquery Marker 496
 MTR1 2 Metrics: Comments Ratio Comments: 26% 16
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 58 = 70dec - 14exi + 2 16
 MTR3 2 Metrics: Query Complexity Complexity: 487 16
 PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 228
 PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 695

DEPENDENCIES
PROCS AND TABLES USED
reads table tempdb..sysobjects (1)  
reads table master..sysconfigures (1)  
writes table sybsystemprocs..sp_opt_querystats_main_rset_001 
reads table master..sysdatabases (1)  
reads table master..syscurconfigs (1)  
read_writes table tempdb..#temp_history2 (1) 
read_writes table tempdb..#temp_history (1) 
read_writes table tempdb..#temp_history_stage (1) 
reads table master..sysprocesses (1)  

CALLERS
called by proc sybsystemprocs..sp_opt_querystats