DatabaseProcApplicationCreatedLinks
sybsystemprocssp_reportstats  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/reportstats */
4     
5     /*
6     **  This stored procedure prints out Chargeback Accounting Statistics and
7     **  percentages.
8     */
9     
10    /*
11    ** Messages for "sp_reportstats"        17935
12    **
13    ** 17231, "No login with the specified name exists."
14    **
15    ** NOTE: We need to LOCALIZE the column names of the SELECTs below!!
16    **
17    */
18    
19    create or replace procedure sp_reportstats
20        @loginame varchar(255) = NULL /* name of login to report */
21    as
22    
23        declare @cnt int,
24            @cputot float,
25            @iotot float
26    
27        declare @nullarg char(1)
28        declare @dummy int
29        declare @status int
30        declare @gp_enabled int
31    
32    
33    
34    
35        if @@trancount = 0
36        begin
37            set chained off
38        end
39    
40        set transaction isolation level 1
41    
42    
43        /* 
44        ** If granular permissions is not enabled then sa_role is required.
45        ** If granular permissions is enabled then the permission 'manage server' is 
46        ** required.
47        ** proc_role and proc_auditperm will also do auditing
48        ** if required. Both will also print error message if required.
49        */
50        select @nullarg = NULL
51        execute @status = sp_aux_checkroleperm "sa_role",
52            "manage server", @nullarg, @gp_enabled output
53    
54        /* For Auditing */
55        if (@gp_enabled = 0)
56        begin
57            if (proc_role("sa_role") = 0)
58                return 1
59        end
60        else
61        begin
62            select @dummy = proc_auditperm("manage server", @status)
63        end
64    
65        if (@status != 0)
66            return 1
67    
68        begin transaction
69    
70        /*
71        **  Get totals for all logins except sa.
72        **  If the total is zero all users have
73        **  used zero time so far. Change totals
74        **  to random value (1) and go on. 
75        **  % usage calculations will appropriately
76        **  result in zero this way.
77        */
78        select @cputot = sum(convert(float, totcpu)),
79            @iotot = sum(convert(float, totio))
80        from master.dbo.syslogins holdlock
81    
82        if @cputot = 0
83            select @cputot = 1
84    
85        if @iotot = 0
86            select @iotot = 1
87    
88        if @loginame is not NULL
89        begin
90    
91            if not exists (select *
92                    from master.dbo.syslogins
93                    where name like @loginame
94                        and ((status & 512) != 512)) /* not LOGIN PROFILE */
95            begin
96                /* 17231, "No login with the specified name exists." */
97                raiserror 17231
98                rollback transaction
99                return (1)
100           end
101   
102           /*
103           **  Print out the totals for this login, and compare against 
104           **  overall totals.
105           */
106           select "Name" = name, "Since" = convert(char(11), accdate),
107               "CPU" = totcpu,
108               "Percent CPU" = convert(varchar(9), convert(numeric(14, 4),
109               round(convert(numeric(20, 4),
110                   totcpu) / @cputot * 100, 5)))
111               + "%",
112               "I/O" = totio,
113               "Percent I/O" = convert(varchar(9), convert(numeric(14, 4),
114               round(convert(numeric(20, 4),
115                   totio) / @iotot * 100, 5)))
116               + "%"
117           from master.dbo.syslogins where name like @loginame
118               and ((status & 512) != 512) /* not LOGIN PROFILE */
119   
120       end
121       else
122       begin
123   
124           /*
125           **  Print out the totals for all logins except sa, and compare against 
126           **  overall totals.
127           */
128           select "Name" = name, "Since" = convert(char(11), accdate),
129               "CPU" = totcpu,
130               "Percent CPU" = convert(varchar(9), convert(numeric(14, 4),
131               round(convert(numeric(20, 4),
132                   totcpu) / @cputot * 100, 5)))
133               + "%",
134               "I/O" = totio,
135               "Percent I/O" = convert(varchar(9), convert(numeric(14, 4),
136               round(convert(numeric(20, 4),
137                   totio) / @iotot * 100, 5)))
138               + "%"
139           from master.dbo.syslogins
140           where ((status & 512) != 512) /* not LOGIN PROFILE */
141       end
142   
143       /*
144       **  Print out overall totals.
145       */
146       print ""
147       select "Total CPU" = convert(varchar(15), @cputot), "Total I/O" = convert(varchar(15), @iotot)
148       commit transaction
149       return (0)
150   
151   


exec sp_procxmode 'sp_reportstats', 'AnyMode'
go

Grant Execute on sp_reportstats to public
go
RESULT SETS
sp_reportstats_rset_003
sp_reportstats_rset_002
sp_reportstats_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 146
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public sybsystemprocs..sp_reportstats  
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 149
 QCRS 3 Conditional Result Set 106
 QCRS 3 Conditional Result Set 128
 QISO 3 Set isolation level 40
 VNRD 3 Variable is not read @dummy 62
 VUNU 3 Variable is not used @cnt 23
 MRST 2 Result Set Marker 106
 MRST 2 Result Set Marker 128
 MRST 2 Result Set Marker 147
 MSUB 2 Subquery Marker 91
 MTR1 2 Metrics: Comments Ratio Comments: 35% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 10dec - 3exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 51 19

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_reportstats_rset_003 
reads table master..syslogins (1)  
writes table sybsystemprocs..sp_reportstats_rset_001 
writes table sybsystemprocs..sp_reportstats_rset_002 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  

CALLERS
called by proc sybsystemprocs..sp_clearstats