DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dump_history  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for "sp_dump_history"
5     **
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 17152, "The dump history file version is missing or invalid."
8     ** 18255, "%1! cannot be NULL."
9     ** 19980, "You are not authorized to execute this stored procedure.
10    ** Only the System Administrator (SA) or a user with oper_role
11    ** authorization can execute this stored procedure."
12    **
13    */
14    
15    create or replace procedure sp_dump_history
16        @operation varchar(10) = 'list', /* Operation to be performed */
17        @until_time varchar(30) = NULL, /* until time date time */
18        @name varchar(255) = NULL, /* DB name or file for config type */
19        @dump_type varchar(30) = NULL, /* Database dump type */
20        @status varchar(10) = NULL, /* dump record status */
21        @file varchar(256) = NULL /* optional file name. 
22    					** Only valid with 'list'.
23    					*/
24    as
25        begin
26    
27            declare @retcode int, /* return code */
28                @fname varchar(256), /* dump history file name */
29                @cfgsql varchar(256), /* SQL query string */
30                @dmpsql varchar(512),
31                @altsql varchar(512),
32                @wheresql varchar(256),
33                @execsql varchar(1024),
34                @tabname varchar(128), /* Temp table name */
35                @dmptab varchar(128), /* Temp table name */
36                @cfgtab varchar(128), /* Temp table name */
37                @alttab varchar(128),
38                @suffix varchar(30),
39                @crttab varchar(512), /* Create table string */
40                @usage varchar(312),
41                @order varchar(60),
42                @dirname varchar(255),
43                @basename varchar(255),
44                @until datetime,
45                @rectype int,
46                @rowcount int,
47                @filelen int,
48                @slash int,
49                @backslash int,
50                @nullarg char(1),
51                @status1 int,
52                @status2 int,
53                @gp_enabled int,
54                @dummy int,
55                @filever varchar(255), /* Version in history file */
56                @versql varchar(255), /* Version select sql */
57                @hist_version_str varchar(3), /* History file version */
58                @syb varchar(128), /* $SYBASE */
59                @fabs1 varchar(1), /* First char of file name */
60                @fabs2 int /* Index of : in filename */
61    
62            set nocount on
63    
64            /* Assume failure by default */
65            select @retcode = 1
66    
67            /* Set history file version required */
68            select @hist_version_str = '1.0'
69    
70            if (@operation = 'help')
71            begin
72                select @retcode = 0
73                goto print_usage
74            end
75    
76            if @@trancount > 0
77            begin
78                /* 17260, 'Can't run %1! from within a transaction.' */
79                raiserror 17260, 'sp_dump_history'
80                return 1
81            end
82    
83            /*
84            ** Verify that the user has sufficient permissions to
85            ** update the dump configuration
86            **
87            ** When GP is not enabled, only the accounts with either 
88            ** SA role or oper_role can execute this procedure. 
89            ** When GP is enabled, only the accounts with MANAGE DUMP CONFIGURATION 
90            ** privilege can execute this procedure.
91            */
92            select @nullarg = NULL
93            select @status1 = 1
94            select @status2 = 1
95    
96            /* sp_aux_checkroleperm will not raise error */
97            execute @status1 = sp_aux_checkroleperm "sa_role",
98                "manage dump configuration", @nullarg, @gp_enabled output
99    
100           if (@gp_enabled = 0)
101           begin
102               if (@status1 != 0)
103               begin
104                   execute @status2 = sp_aux_checkroleperm "oper_role",
105                       @nullarg, @nullarg, @gp_enabled output
106   
107                   if (@status2 != 0)
108                   begin
109                       /* 
110                       ** proc_role() will raise permission errors
111                       ** and send audit records to the audit trail.
112                       */
113                       select @dummy = proc_role("sa_role")
114                       select @dummy = proc_role("oper_role")
115                       return 1
116                   end
117               end
118   
119               /* send audit record when succeed */
120               if (@status1 = 0)
121               begin
122                   select @dummy = proc_role("sa_role")
123               end
124   
125               if (@status2 = 0)
126               begin
127                   select @dummy = proc_role("oper_role")
128               end
129           end
130           else
131           begin
132               select @dummy = proc_auditperm("manage dump configuration",
133                       @status1)
134               if (@status1 != 0)
135                   return 1
136           end
137   
138           /* Use list as default operation */
139           if (@operation is NULL)
140           begin
141               select @operation = 'list'
142           end
143   
144           if not @operation in ('purge', 'list', 'listfiles')
145           begin
146               goto print_usage
147           end
148   
149           if (@operation = 'purge')
150           begin
151               /* Call a server built-in to perform purge operation. */
152               select @retcode = purge_dump_history(@name, @dump_type,
153                       @until_time, @status)
154               return @retcode
155           end
156   
157           /* Get dump history file name */
158           select @fname = value2
159           from master.dbo.sysconfigures
160           where name = 'dump history filename'
161   
162           if (@fname = '' or @fname is NULL)
163           begin
164               /* Nothing to list. Return. */
165               return 0
166           end
167   
168           /* Add the $SYBASE path if fname is not absolute path. */
169           select @fabs1 = substring(@fname, 1, 1)
170           select @fabs2 = charindex(':', @fname)
171           if not (@fabs1 = '/' or @fabs1 = '\' or @fabs2 > 0)
172           begin
173               select @syb = @@sybase_krg
174               select @fname = @syb + '/' + @fname
175           end
176   
177           /* Create a unique sufix. */
178           select @suffix = rtrim(convert(char, @@spid))
179               + ltrim(str(datepart(ms, getdate())))
180   
181           if (@operation = 'listfiles')
182           begin
183               select @filelen = char_length(@fname)
184               select @slash = charindex('/', reverse(@fname))
185               select @backslash = charindex('\', reverse(@fname))
186               if ((@slash = 0) or (@slash > @backslash and @backslash != 0))
187                   select @slash = @backslash
188   
189               select @dirname = substring(@fname,
190                       1, @filelen - @slash),
191                   @basename = substring(@fname,
192                       @filelen - @slash + 2, @slash)
193   
194               select @tabname = 'tempdb..dir_' + @suffix
195               select @crttab = 'create proxy_table ' + @tabname
196                   + ' external directory at ''' + @dirname + ''''
197               exec (@crttab)
198               if (@@error != 0)
199               begin
200                   return @retcode
201               end
202   
203               select @wheresql = 'where filename = ''' + @basename
204                   + ''' or filename like ''' +
205                   @basename + '.[0-9][0-9][0-9]'''
206               exec sp_autoformat @tabname, 'filename, ctime', @wheresql
207               if @@error != 0
208                   select @retcode = 0
209   
210               select @execsql = 'if object_id(@tabname) is not null '
211                   + 'drop table ' + @tabname
212               exec (@execsql)
213               if @@error = 0
214                   select @retcode = 0
215               return @retcode
216           end
217   
218           /* A different version of the file is requested. */
219           if (@file is not NULL)
220           begin
221               if @file like '[0-9][0-9][0-9]'
222                   select @fname = @fname + '.' + @file
223               else
224                   select @fname = @file
225           end
226   
227           /* Create temp table name */
228           select @tabname = 'tempdb..dumphist' + @suffix,
229               @dmptab = 'tempdb..dmptab' + @suffix,
230               @cfgtab = 'tempdb..cfgtab' + @suffix,
231               @alttab = 'tempdb..alttab' + @suffix
232   
233           /* Create a proxy table on external dump history file. */
234           select @crttab = 'create table ' + @tabname + '('
235               + 'rec_type int, '
236               + 'dbid int, '
237               + 'name varchar(255), '
238               + 'num_stripes int, '
239               + 'prev_seq_date datetime, '
240               + 'cur_seq_date	datetime, '
241               + 'dump_date datetime, '
242               + 'stripe_name varchar(255), '
243               + 'hilpgno int, '
244               + 'server_name varchar(30), '
245               + 'passwd int, '
246               + 'cmp_lvl int, '
247               + 'status int '
248               + ') external file at '
249               + '''' + @fname + ''' column delimiter ''|'' '
250   
251           /* Init the sql string to select all records of dump type. */
252           select @dmpsql =
253               ' select Dump_Type ='
254               + ' case rec_type'
255               + ' when 2 then ''DATABASE'' '
256               + ' when 3 then ''TRAN'' '
257               + ' when 5 then ''CUMULATIVE'''
258               + ' when 6 then ''DELTA'''
259               + ' else ''UNKNOWN'' end,'
260               + ' Dbid = dbid,'
261               + ' Database_name = convert(sysname, name),'
262               + ' Stripes = num_stripes,'
263               + ' Dump_date = convert(varchar(26), dump_date, 109),'
264               + ' File = stripe_name, '
265               + ' Server_name = server_name,'
266               + ' Compression_lvl = cmp_lvl, '
267               + ' Password = case passwd when 1 then ''yes'' else ''no'' end'
268               + ' into ' + @dmptab
269               + ' from ' + @tabname
270   
271           select @cfgsql =
272               ' select Source_File = name,'
273               + ' Date = convert(varchar(26), dump_date, 109),'
274               + ' File = stripe_name '
275               + ' into ' + @cfgtab
276               + ' from ' + @tabname
277   
278           select @altsql =
279               ' select Date = convert(varchar(26), dump_date, 109),'
280               + ' Device = stripe_name, '
281               + ' Segment = case num_stripes when 4 then ''LOG'' else ''DATA'' end,'
282               + ' Action = case status & 8 when 8 then ''EXTEND'' else ''SHRINK'' end,'
283               + ' Start = case hilpgno when 4294967295 then 0 else hilpgno end,'
284               + ' Pages = cmp_lvl'
285               + ' into ' + @alttab
286               + ' from ' + @tabname
287   
288           if (@dump_type is not NULL)
289           begin
290               select @rectype =
291                   case upper(@dump_type)
292                       when 'DATABASE' then 2
293                       when 'TRAN' then 3
294                       when 'TRANSACTION' then 3
295                       when 'CONFIG' then 4
296                       when 'CONFIGURATION' then 4
297                       when 'CUM' then 5
298                       when 'CUMULATIVE' then 5
299                       when 'DELTA' then 6
300                       when 'ALTERDB' then 7
301                       when 'ALL' then 0
302                       else NULL
303                   end
304   
305               if @rectype is NULL
306               begin
307                   goto print_usage
308               end
309   
310               if @rectype != 0
311               begin
312                   select @wheresql = ' where rec_type = '
313                       + convert(char, @rectype)
314               end
315           end
316           else
317           begin
318               select @rectype = 0
319               select @wheresql = ' where 1 = 1'
320           end
321   
322           if (@name is not NULL)
323           begin
324               select @wheresql = @wheresql + ' and name = '''
325                   + @name + ''''
326           end
327   
328           if (@until_time is not NULL)
329           begin
330               select @until = convert(datetime, @until_time)
331               if (@@error != 0)
332               begin
333                   goto print_usage
334               end
335               select @wheresql = @wheresql + ' and dump_date <= '
336                   + '''' + @until_time + ''''
337           end
338   
339           if (@status is not NULL)
340           begin
341               if @status = 'success'
342                   select @wheresql = @wheresql + ' and status & 1 != 0'
343               else if @status = 'fail'
344                   select @wheresql = @wheresql + ' and status & 2 != 0'
345               else if @status = 'deleted'
346                   select @wheresql = @wheresql + ' and status & 4 != 0'
347               else
348                   goto print_usage
349           end
350   
351           select @order = ' order by name, dump_date'
352   
353           /* Execute Create Table */
354           exec (@crttab)
355           if (@@error != 0)
356           begin
357               goto cleanup
358           end
359   
360           /* Check file version */
361           select @versql = 'select @filever = name from '
362               + @tabname + ' where rec_type = 0'
363   
364           select @filever = '0.0'
365           exec (@versql)
366           if (@@error != 0)
367           begin
368               goto cleanup
369           end
370   
371           if (@filever != @hist_version_str)
372           begin
373               raiserror 17152
374               return 1
375           end
376   
377           /*
378           ** Perform select operation to list out contents of dump history
379           ** file based on the user supplied input.
380           */
381           if @rectype in (0, 2, 3, 5, 6)
382           begin
383               select @execsql = @dmpsql + @wheresql +
384                   ' and rec_type in (2, 3, 5, 6) ' + @order
385               exec (@execsql)
386               if (@@error != 0)
387               begin
388                   goto cleanup
389               end
390           end
391   
392           if @rectype in (0, 4)
393           begin
394               select @execsql = @cfgsql + @wheresql +
395                   ' and rec_type = 4 ' + @order
396               exec (@execsql)
397               if (@@error != 0)
398               begin
399                   goto cleanup
400               end
401           end
402   
403           if @rectype in (0, 7)
404           begin
405               select @execsql = @altsql + @wheresql +
406                   ' and rec_type = 7 ' + @order
407               exec (@execsql)
408               if (@@error != 0)
409               begin
410                   goto cleanup
411               end
412           end
413   
414           if object_id(@dmptab) is not null
415           begin
416               select @execsql = 'select @rowcount = count(*) from ' + @dmptab
417               print "DUMP"
418               print "----"
419               exec (@execsql)
420               if (@rowcount > 0)
421               begin
422                   exec sp_autoformat @dmptab
423               end
424               print ""
425           end
426   
427           if object_id(@cfgtab) is not null
428           begin
429               select @execsql = 'select @rowcount = count(*) from ' + @cfgtab
430               print "CONFIG"
431               print "------"
432               exec (@execsql)
433               if (@rowcount > 0)
434               begin
435                   exec sp_autoformat @cfgtab
436               end
437               print ""
438           end
439   
440           if object_id(@alttab) is not null
441           begin
442               select @execsql = 'select @rowcount = count(*) from ' + @alttab
443               print "ALTER DATABASE"
444               print "--------------"
445               exec (@execsql)
446               if (@rowcount > 0)
447               begin
448                   exec sp_autoformat @alttab
449               end
450           end
451   cleanup:
452           /* Drop temporary tables. */
453           select @execsql =
454               ' if object_id(@tabname) is not null drop table ' + @tabname
455               + ' if object_id(@dmptab)  is not null drop table ' + @dmptab
456               + ' if object_id(@cfgtab)  is not null drop table ' + @cfgtab
457               + ' if object_id(@alttab)  is not null drop table ' + @alttab
458           exec (@execsql)
459           if (@@error = 0)
460           begin
461               select @retcode = 0
462           end
463   
464           return @retcode
465   
466   print_usage:
467           select @usage = 'sp_dump_history '
468               + '[  @operation = {''list'' | ''purge'' '
469               + '| ''listfiles'' | ''help''}]'
470               + '[, @until_time = ''date''] '
471               + '[, @name = ''<database or file name>'']'
472               + '[, @dump_type = {''DATABASE'' | ''TRAN[SACTION]'' '
473               + '| ''CONFIG[URATION]'' | ''CUM[ULATIVE]'' '
474               + '| ''ALTERDB''}]'
475               + '[, @status = {''success'' | ''fail'' | ''deleted''}]'
476               + '[, @file = ''<filename>'']'
477   
478           print @usage
479           return @retcode
480       end
481   
482   


exec sp_procxmode 'sp_dump_history', 'AnyMode'
go

Grant Execute on sp_dump_history to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 162
 MEST 4 Empty String will be replaced by Single Space 424
 MEST 4 Empty String will be replaced by Single Space 437
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @syb: varchar(128) = int 173
 VRUN 4 Variable is read and not initialized @rowcount 420
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 15
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public sybsystemprocs..sp_dump_history  
 MNER 3 No Error Check should check return value of exec 206
 MNER 3 No Error Check should check return value of exec 422
 MNER 3 No Error Check should check return value of exec 435
 MNER 3 No Error Check should check return value of exec 448
 MUCO 3 Useless Code Useless Begin-End Pair 25
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 322
 MUCO 3 Useless Code Useless Brackets 328
 MUCO 3 Useless Code Useless Brackets 331
 MUCO 3 Useless Code Useless Brackets 339
 MUCO 3 Useless Code Useless Brackets 355
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 371
 MUCO 3 Useless Code Useless Brackets 386
 MUCO 3 Useless Code Useless Brackets 397
 MUCO 3 Useless Code Useless Brackets 408
 MUCO 3 Useless Code Useless Brackets 420
 MUCO 3 Useless Code Useless Brackets 433
 MUCO 3 Useless Code Useless Brackets 446
 MUCO 3 Useless Code Useless Brackets 459
 QAFM 3 Var Assignment from potentially many rows 158
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
160
 VNRD 3 Variable is not read @gp_enabled 105
 VNRD 3 Variable is not read @dummy 132
 VNRD 3 Variable is not read @until 330
 MDYS 2 Dynamic SQL Marker 197
 MDYS 2 Dynamic SQL Marker 212
 MDYS 2 Dynamic SQL Marker 354
 MDYS 2 Dynamic SQL Marker 365
 MDYS 2 Dynamic SQL Marker 385
 MDYS 2 Dynamic SQL Marker 396
 MDYS 2 Dynamic SQL Marker 407
 MDYS 2 Dynamic SQL Marker 419
 MDYS 2 Dynamic SQL Marker 432
 MDYS 2 Dynamic SQL Marker 445
 MDYS 2 Dynamic SQL Marker 458
 MTR1 2 Metrics: Comments Ratio Comments: 17% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 45 = 52dec - 9exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 211 15

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_005