DatabaseProcApplicationCreatedLinks
sybsystemprocssp_mon_archive_data  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_mon_archive_data(
3         @archivename varchar(40)
4         , @selstmt varchar(1800)
5         , @fromclause varchar(30)
6         , @whereclause varchar(255) = NULL
7         , @uniqueindex_name varchar(30) = NULL
8         , @datestamp datetime
9         , @created int output
10        , @trace int
11        , @unique_col1 varchar(30) = NULL
12        , @unique_col2 varchar(30) = NULL
13        , @unique_col3 varchar(30) = NULL
14        , @unique_col4 varchar(30) = NULL
15        , @unique_col5 varchar(30) = NULL
16    ) as
17        begin
18            declare @whoami varchar(30)
19                , @indent varchar(32)
20                , @sqlstmt varchar(1800)
21                , @msg varchar(80)
22                , @do_freeze_mda tinyint
23                , @retval int
24                , @mda_retval int
25                , @tmp_retval int
26                , @num_rows int
27                , @crt_newarchive tinyint
28    
29            select @whoami = 'sp_mon_archive_data'
30                , @indent = char(10) + space(2 * @@nestlevel)
31    
32            if (@trace = 1)
33            begin
34                print "%1!---- Start Trace %2!: ----"
35                    , @indent, @whoami
36            end
37            if (object_id(@archivename) IS NULL)
38            begin -- {
39                -- Create a new table using SELECT INTO.
40                --
41                select @sqlstmt = @selstmt
42                    + " INTO " + @archivename
43                    + ' '
44                    + @fromclause
45                    + ' '
46                    + @whereclause
47    
48                    , @crt_newarchive = 1
49                    , @do_freeze_mda = 1
50            end -- }
51            else
52            begin -- {
53    
54                -- Re-create an INSERT...SELECT SQL statement adding WHERE
55                -- clauses for duplicate elimination, using the columns
56                -- specified for uniqueness.
57                --
58                select @sqlstmt = 'INSERT ' + @archivename
59                    + ' '
60                    + @selstmt
61                    + ' '
62                    + @fromclause
63                    + ' '
64                    + @whereclause
65    
66                    -- Archive already exists. Just insert into it.
67                    , @crt_newarchive = 0
68    
69                    -- We need to freeze MDA for insert-select, also.
70                    --
71                    , @do_freeze_mda = 1
72    
73            end -- }
74    
75            -- If we are creating a new archive, here is where the SQL
76            -- to extract data from monDeadLock will get executed. Freeze
77            -- it, and unfreeze it after the SQL executes.
78            --
79            if (@do_freeze_mda = 1)
80            begin
81                select @mda_retval = mdaconfig('freeze', 'begin')
82            end
83    
84            select @num_rows = 0
85            exec @retval = sp_exec_SQL @sqlstmt
86                , "sp_mon_archive_data-archive"
87                , @num_rows output
88                , @trace
89            if (@do_freeze_mda = 1)
90            begin
91                select @mda_retval = mdaconfig('freeze', 'end')
92            end
93    
94            -- The data was correctly archived either via SELECT-INTO or
95            -- INSERT-SELECT. Report on that.
96            --
97            if (@retval = 0)
98            begin
99                -- Reset archive name to be full name, incl dbname.
100               -- [Reuse variable for message reporting.]
101               --
102               select @sqlstmt = db_name() + ".dbo." + @archivename
103               if (@crt_newarchive = 1)
104               begin
105                   exec sp_getmessage 19489, @msg output
106                   print @msg, @sqlstmt
107               end
108               exec sp_getmessage 19490, @msg output
109               print @msg, @num_rows, @sqlstmt, @datestamp
110   
111           end
112   
113           -- Start building the CREATE INDEX sql and then exec it. Unique
114           -- index used for duplicate elimination during future inserts.
115           --
116           if ((@retval = 0) and (@crt_newarchive = 1))
117           begin
118               select @sqlstmt = "CREATE UNIQUE INDEX "
119                   + @uniqueindex_name
120                   + " ON "
121                   + @archivename
122                   + "("
123   
124               if (@unique_col1 IS NOT NULL)
125               begin
126                   select @sqlstmt = @sqlstmt
127                       + @unique_col1
128               end
129   
130               if (@unique_col2 IS NOT NULL)
131               begin
132                   select @sqlstmt = @sqlstmt
133                       + ","
134                       + @unique_col2
135               end
136   
137               if (@unique_col3 IS NOT NULL)
138               begin
139                   select @sqlstmt = @sqlstmt
140                       + ","
141                       + @unique_col3
142               end
143   
144               if (@unique_col4 IS NOT NULL)
145               begin
146                   select @sqlstmt = @sqlstmt
147                       + ","
148                       + @unique_col4
149               end
150   
151               if (@unique_col5 IS NOT NULL)
152               begin
153                   select @sqlstmt = @sqlstmt
154                       + ","
155                       + @unique_col5
156               end
157               select @sqlstmt = @sqlstmt
158                   + ")"
159                   + " WITH IGNORE_DUP_KEY"
160   
161               exec @retval = sp_exec_SQL @sqlstmt
162                   , 'archive_data-crt-unique-index'
163                   , NULL, @trace
164           end
165   
166           -- Deem successful creation of table only if index was also
167           -- successfully built.
168           --
169           if (@retval = 1)
170               select @created = 1
171   
172           if (@trace = 1)
173           begin
174               print "%1!---- End Trace %2! (retval=%3!) ----"
175                   , @indent, @whoami, @retval
176           end
177   
178           return @retval
179       end -- }
180   

DEFECTS
 MTYP 4 Assignment type mismatch @trace: tinyint = int 88
 MTYP 4 Assignment type mismatch @trace: tinyint = int 163
 MNER 3 No Error Check should check return value of exec 85
 MNER 3 No Error Check should check return value of exec 105
 MNER 3 No Error Check should check return value of exec 108
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Begin-End Pair 17
 MUCO 3 Useless Code Useless Brackets 32
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 172
 VNRD 3 Variable is not read @mda_retval 91
 VUNU 3 Variable is not used @tmp_retval 25
 MTR1 2 Metrics: Comments Ratio Comments: 21% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 16dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 55 2

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_exec_SQL  
   writes table sybsystemprocs..sp_exec_SQL_rset_001 
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  

CALLERS
called by proc sybsystemprocs..sp_mon_archive_monTable  
   called by proc sybsystemprocs..sp_mon_archive_deadlock