DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_tranlog  31 Aug 14Defects Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_TRANLOG
4     **
5     **	The driver procedure for the getting space usage information for entity
6     **	type - "tranlog" (transaction log). Called by sp_spaceusage.
7     **
8     **	Parameters:
9     **		@actionword	- The action to be performed. 
10    **		@unit		- Unit for displaying page counts, if any.
11    **		@archive_db	- Name of the database where the archive table
12    **				  resides or will be created.
13    **		@archive_prefix - Prefix for the archive table name, if any.
14    **		@select_list	- Comma separated list of columns to project
15    **				  the output.
16    **		@where_clause	- A WHERE clause, if any, to filter the output.
17    **		@order_by_clause- An ORDER BY clause, to order the output.
18    **		@from_date	- Date, if any, from when on to look for data 
19    **				  in the archive table to generate report.
20    **		@to_date	- Date, if any, till when to look for data in 
21    **				  the archive table to generate report.
22    **	
23    **	Returns
24    **		0 - if all goes well
25    **		6 - archive table not found
26    **	    other - error while execution 
27    {
28    */
29    create procedure sp_spaceusage_tranlog
30    (
31        @actionword varchar(20)
32        , @unit varchar(6) = NULL
33        , @archive_db varchar(30) = NULL
34        , @archive_prefix varchar(237) = NULL
35        , @select_list varchar(1536) = NULL
36        , @where_clause varchar(1536) = NULL
37        , @order_by_clause varchar(768) = NULL
38        , @from_date varchar(30) = NULL
39        , @to_date varchar(30) = NULL
40    
41    )
42    as
43        begin -- { 	
44    
45            declare
46                @returnStatus int
47                , @archivetabname varchar(320)
48                , @defaultarchivetabname varchar(30)
49    
50                -- String constants for the action 
51                , @action_display varchar(8)
52                , @action_report varchar(7)
53                , @action_archive varchar(8)
54    
55            select
56                @archivetabname = NULL
57                , @defaultarchivetabname = "spaceusage_tranlog"
58    
59                , @action_display = "display"
60                , @action_report = "report"
61                , @action_archive = "archive"
62    
63            if @actionword in (@action_report, @action_archive)
64            begin -- This is report/archive mode
65    
66                -- Verify that the archive database does exist.
67                if not exists (select 1
68                        from master.dbo.sysdatabases
69                        where name like @archive_db)
70                begin
71                    raiserror 18322, @archive_db
72                    return (6)
73                end
74    
75                select @archivetabname = @archive_db + "." + user_name() + "."
76                    + case @archive_prefix
77                        when NULL
78                        then @defaultarchivetabname
79                        else @archive_prefix
80                            + @defaultarchivetabname
81                    end
82    
83                -- Verify the archive table does exist in the database for the
84                -- report action.
85                -- 
86                if @actionword = @action_report
87                    and object_id(@archivetabname) is NULL
88                begin
89                    declare @tmpname varchar(290)
90    
91                    select @tmpname = user_name() + "."
92                        + case @archive_prefix
93                            when NULL
94                            then @defaultarchivetabname
95                            else @archive_prefix
96                                + @defaultarchivetabname
97                        end
98    
99                    raiserror 17870, @tmpname, @archive_db, NULL
100                   return (6)
101               end
102           end
103   
104           /* 
105           ** Create and initialize the temporary #syslogsinfo table based on 
106           ** the action specified. For 'display'/'archive' action, the table is 
107           ** inserted with a single row for the current state of the syslgos using
108           ** the lct_admin() built-ins. For 'report' action, the table is
109           ** populated by selecting into it the relavant data from the archive 
110           ** table.
111           **
112           ** NOTE: Any change done to this table needs a change in the
113           ** corresponding #syslogsinfo table created at the beginning of the
114           ** file (at line #15). And the inserts that are done in the procedure
115           ** sp_spaceusage_tranlog_init (at lines #116, 209).
116           */
117           create table #syslogsinfo
118           (
119               VersionNum int NOT NULL
120               , ESDNum int NOT NULL
121               , EBFNum int NOT NULL
122               , ArchiveDateTime datetime NOT NULL
123               , ServerName varchar(255) NULL
124               , MaxPageSize int NOT NULL
125               , DBName varchar(30) NOT NULL
126               , OwnerName varchar(30) NOT NULL
127               , TableName varchar(255) NOT NULL
128               , Id int NOT NULL
129               , NumRows int NULL
130               , IsMLD bit NOT NULL
131               , IsLogFull bit NOT NULL
132               , LCTPageCount int NULL
133               , TotalPageCount int NULL
134               , RsvdPageCount int NULL
135               , CLRPageCount int NULL
136               , UsedPageCount int NULL
137               , FreePageCount int NULL
138               , PctFreePages decimal(5, 2) NULL
139               , PctUsedPages decimal(5, 2) NULL
140               , LCTPages float NULL
141               , TotalPages float NULL
142               , RsvdPages float NULL
143               , CLRPages float NULL
144               , UsedPages float NULL
145               , FreePages float NULL
146           )
147   
148           if (@@error != 0)
149               return (@@error)
150   
151           exec @returnStatus = sp_spaceusage_tranlog_init @actionword
152               , @archivetabname
153               , @from_date
154               , @to_date
155   
156           if @returnStatus != 0
157               return (@returnStatus)
158   
159           /*
160           ** Depending on the unit specifier, convert the page counts into the
161           ** appropriate data type before display.
162           **
163           ** NOTE: Although, archive mode ideally would only archive the value
164           ** in pages, it may have a WHERE clause on these computed columns for
165           ** which it requires these converted values.
166           */
167           exec @returnStatus = sp_spaceusage_cnvtounit @unit, "#syslogsinfo"
168   
169           if @returnStatus != 0
170               return (@returnStatus)
171   
172           /* 
173           ** Now, display/archive the information collected and computed to the 
174           ** user based on the action. For display/report the user expects to see
175           ** the output on screen. And for archive action the user expects the 
176           ** output to be appended to the archive table.
177           */
178           if @actionword in (@action_display, @action_report)
179           begin -- {
180   
181               exec @returnStatus = sp_spaceusage_tranlog_genoutput
182                   @actionword
183                   , @unit
184                   , @select_list
185                   , @where_clause
186                   , @order_by_clause
187   
188               if @returnStatus != 0
189                   return (@returnStatus)
190   
191           end -- }
192           else if @actionword = @action_archive
193           begin -- {
194   
195               exec @returnStatus = sp_spaceusage_tranlog_archdata
196                   @archivetabname
197                   , @where_clause
198   
199               if @returnStatus != 0
200                   return (@returnStatus)
201   
202           end -- }
203   
204           drop table #syslogsinfo
205   
206       end -- }	-- }
207   


exec sp_procxmode 'sp_spaceusage_tranlog', 'AnyMode'
go

Grant Execute on sp_spaceusage_tranlog to public
go
DEFECTS
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_tranlog  
 MUCO 3 Useless Code Useless Brackets in create proc 30
 MUCO 3 Useless Code Useless Begin-End Pair 43
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 200
 MSUB 2 Subquery Marker 67
 MTR1 2 Metrics: Comments Ratio Comments: 41% 29
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 11dec - 5exi + 2 29
 MTR3 2 Metrics: Query Complexity Complexity: 45 29

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_spaceusage_tranlog_archdata  
   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)  
   calls proc sybsystemprocs..sp_exec_SQL  
calls proc sybsystemprocs..sp_spaceusage_tranlog_init  
   reads table master..sysusages (1)  
   calls proc sybsystemprocs..sp_versioncrack  
      calls proc sybsystemprocs..sp_split_string  
   read_writes table tempdb..#syslogsinfo (1) 
   reads table master..sysdatabases (1)  
   calls proc sybsystemprocs..sp_exec_SQL  
writes table tempdb..#syslogsinfo (1) 
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_spaceusage_tranlog_genoutput  
   calls proc sybsystemprocs..sp_autoformat  
      calls proc sybsystemprocs..sp_namecrack  
      reads table master..syscolumns (1)  
      read_writes table tempdb..#colinfo_af (1) 
      reads table tempdb..syscolumns (1)  
      reads table master..systypes (1)  
      calls proc sybsystemprocs..sp_autoformat  
      reads table tempdb..systypes (1)  
   calls proc sybsystemprocs..sp_getmessage  
   reads table tempdb..#syslogsinfo (1) 
calls proc sybsystemprocs..sp_spaceusage_cnvtounit  
   calls proc sybsystemprocs..sp_exec_SQL  

CALLERS
called by proc sybsystemprocs..sp_spaceusage