DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_archive  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_monitor_archive(
3         @montype varchar(30) = NULL
4         , @archivecmd varchar(255) = NULL
5         , @trace int = NULL
6     ) as
7         begin
8             declare @whoami varchar(30)
9                 , @indent varchar(32)
10                , @retval int
11                , @mda_retval int
12                , @found_montype tinyint
13    
14                , @opt_sep char(1)
15                , @charindex tinyint
16                , @archive_str varchar(7)
17                , @option_item varchar(30)
18                , @datestamp datetime
19    
20                , @archive_syntax varchar(40)
21    
22                -- We restrict this to a short string for 125.x due to the
23                -- limited tablename length.
24                --
25                , @arch_prefix_dflt varchar(8)
26                , @arch_prefix_maxlen int
27                , @arch_prefix varchar(30)
28    
29                , @arch_dbname varchar(30)
30                , @baseprocname varchar(100)
31                , @archprocname varchar(60)
32    
33                -- DB status flags
34                , @sel_into_ok int -- status word
35                , @user_proxy_db int
36                , @ha_proxy_db int
37    
38                -- Variables to store status bit settings.
39                , @sel_into_flag int
40                , @proxy_db_flag int
41    
42            select @whoami = object_name(@@procid, db_id('sybsystemprocs'))
43                , @indent = char(10) + space(2 * @@nestlevel)
44                , @archive_str = 'archive'
45    
46                -- This will be the single timestamp used for all data archived
47                -- under this command. This way, this set of data can be all 
48                -- joined in one pass, using this common field.
49                --
50                , @datestamp = getdate()
51    
52                -- Various status flags for database settings.
53                , @sel_into_ok = 4
54                , @user_proxy_db = 1
55                , @ha_proxy_db = 2
56    
57                -- Assume error till we know that it's a valid monitoring type 
58                , @found_montype = 0
59    
60            if (@montype IN ('deadlock'
61                    ))
62            begin
63                select @found_montype = 1
64            end
65    
66            -- This means user asked to archive data for a monitoring type that is
67            -- an illegal monitoring type, or archiving for that type is currently
68            -- not supported. Raise an error and bail.
69            --
70            if (@found_montype = 0)
71            begin
72                select @archive_syntax = "sp_monitor 'help', '"
73                    + @archive_str
74                    + "'"
75                raiserror 19260, @archive_syntax
76                return 1
77            end
78    
79            -- Do routine monitoring specific validation for setup/configuration.
80            --  'archive' is not really a monitoring type, so instead pass-in a
81            -- dummy sproc name as the arg, which will be anyway ignored .
82            --
83            exec @retval = sp_monitor_verify_setup 'sp_monitor_archive'
84    
85            exec @mda_retval = sp_monitor_verify_cfgval @montype
86    
87            -- Bail out if there were any errors.
88            if (@retval != 0)
89                return @retval
90            else if (@mda_retval != 0)
91                return @mda_retval
92    
93            if (@trace IS NULL)
94                exec @trace = sp_monitor_trace_level
95    
96            if (@trace = 1)
97            begin
98                print "%1!---- Start Trace %2! montype='%3!' archivecmd='%4!' datestamp='%5!'"
99                    , @indent, @whoami
100                   , @montype
101                   , @archivecmd
102                   , @datestamp
103           end
104   
105           if (@montype IS NULL)
106           begin
107               raiserror 18464, "'@monitoring_type'"
108               return 1
109           end
110           else if (@archivecmd IS NULL)
111           begin
112               raiserror 18464, "'@archivecmd'"
113               return 1
114           end
115   
116           -- Always archive in the current db of the sproc's execution.
117           select @arch_dbname = db_name()
118   
119           -- Disallow certain key system-dbs for archival uses.
120           if (@arch_dbname IN ('master'
121                       , 'model'
122                       , 'sybsystemprocs'
123                       , 'sybsystemdb'
124                       , 'sybsecurity'
125                   )
126               )
127           begin
128               raiserror 19492, @arch_dbname, "system"
129               return 1
130           end
131   
132           -- Check if archive db allows for select-into and return error
133           -- now, to avoid future errors at run-time.
134           --
135           select @sel_into_flag = (d.status & @sel_into_ok)
136           from master.dbo.sysdatabases d
137           where name = @arch_dbname
138   
139           if (@sel_into_flag != @sel_into_ok)
140           begin
141               exec sp_getmessage 19261, @baseprocname output
142               print @baseprocname, "sp_monitor", @archive_str,
143                   "sp_dboption", "select into"
144   
145               -- Reuse @var to get string name for 17050.
146               exec sp_getmessage 17050, @baseprocname output
147               raiserror 19493, @arch_dbname, @baseprocname
148               return 1
149           end
150   
151           -- check and disallow user- and HA-proxy dbs as an archive db.
152           --
153           select @proxy_db_flag = (d.status3 & (@user_proxy_db | @ha_proxy_db))
154           from master.dbo.sysdatabases d
155           where name = @arch_dbname
156   
157           if (@proxy_db_flag IN (@user_proxy_db, @ha_proxy_db))
158           begin
159               raiserror 19492, @arch_dbname, "proxy"
160               return 1
161           end
162   
163           select @archive_syntax = "'" + @archive_str + " [using prefix=<string>]'"
164           exec @retval = sp_monitor_parse_archive_cmd
165               @archivecmd
166               , @archive_str
167               , @archive_syntax
168               , @arch_prefix output
169               , @trace
170           if (@retval != 0)
171               return @retval
172   
173           -- =================================================================
174           -- Call the monitoring-type specific archival routine
175           -- Currently, we only support archiving for one monitoring type.
176           -- In future, we might consider extending this to archive for 'all'
177           -- monitoring types in one go.
178           --
179           -- Build the procedure's name prefixed by the archive db name, so that
180           -- when we execute the specific archiving sproc, it will run in the
181           -- archive db. (We just have to append the monitoring type for each
182           -- type of monitoring whose data will be archived.)
183           --
184           select @baseprocname = @arch_dbname + ".." + "sp_mon_archive_"
185   
186           if (@montype IN ('deadlock', 'all'))
187           begin
188               select @archprocname = @baseprocname + 'deadlock'
189                   , @found_montype = 1
190   
191               -- The reason why we getdate() here and pass it all the way
192               -- down the stack is that sometime in the future when we
193               -- support archival for other monitoring types (or 'all')
194               -- we would like to keep the archive date across multiple tables
195               -- the same for all data that is being archived in one command.
196               --
197               exec @retval = @archprocname @arch_prefix
198                   , @datestamp
199                   , @trace
200               if (@retval != 0)
201                   return @retval
202           end
203   
204   
205           return @retval
206       end -- }
207   

DEFECTS
 MTYP 4 Assignment type mismatch @montype: varchar(10) = varchar(30) 85
 MTYP 4 Assignment type mismatch @arch_prefix: varchar(8) = varchar(30) 168
 MGTP 3 Grant to public master..sysdatabases  
 MNER 3 No Error Check should check return value of exec 83
 MNER 3 No Error Check should check return value of exec 141
 MNER 3 No Error Check should check return value of exec 146
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Begin-End Pair 7
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 200
 VNRD 3 Variable is not read @found_montype 189
 VUNU 3 Variable is not used @opt_sep 14
 VUNU 3 Variable is not used @charindex 15
 VUNU 3 Variable is not used @option_item 17
 VUNU 3 Variable is not used @arch_prefix_dflt 25
 VUNU 3 Variable is not used @arch_prefix_maxlen 26
 MDYE 2 Dynamic Exec Marker exec @retval 197
 MTR1 2 Metrics: Comments Ratio Comments: 34% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 15dec - 9exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 69 2

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_monitor_trace_level  
calls proc sybsystemprocs..sp_monitor_verify_cfgval  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
      reads table master..syscurconfigs (1)  
      reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_monitor_verify_setup  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
   reads table master..sysobjects (1)  
   calls proc sybsystemprocs..sp_monitor_getcfgnum  
      reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_monitor_parse_archive_cmd  
   calls proc sybsystemprocs..sp_spaceusage_processusing  
      calls proc sybsystemprocs..sp_split_string  
   calls proc sybsystemprocs..sp_split_string  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  

CALLERS
called by proc sybsystemprocs..sp_monitor_deadlock  
   called by proc sybsystemprocs..sp_monitor