DatabaseProcApplicationCreatedLinks
sybsystemprocssp_optgoal  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** Messages for sp_optgoal
5     **
6     ** 17118 "The length of the user defined goal name must be smaller than 12."
7     ** 17149 "User defined optimizer goal with such name does not exist. Please check the goal name."
8     ** 17150 "Incorrect action. Possible actions are : "show","save","delete".
9     ** 17151 "System defined optimizer goal cannot be modified."
10    **
11    */
12    
13    /*
14    ** This procedure manipulates user defined goals.
15    ** It takes two parameters:
16    ** 	- goal name
17    **	- action
18    ** Goal name is a string up to 11 characters defined by the user.
19    ** Action is one of :
20    **	"save" - the goal is saved to the system sysattributes
21    **	"show" - the content og the goal is diplayed
22    **	"delete" - deletes a goal
23    **
24    */
25    create or replace procedure sp_optgoal
26        @name varchar(30) = null,
27        @option varchar(30) = NULL
28    as
29        declare @bitmap varbinary(30), @a int, @msg varchar(1024), @sysoption int
30        declare @nullarg varchar(1)
31        declare @status int
32        declare @dummy int
33        declare @gp_enabled int
34    
35        select @nullarg = NULL
36    
37    
38        /* print all user defined goals avilable */
39        if (@name is NULL)
40        begin
41            select 'user defined goal' = object_cinfo from master..sysattributes
42            where class = 50 and object_type = 'qp'
43    
44            return 0
45        end
46    
47        select @option = rtrim(lower(@option))
48        /* set default action */
49        if @option is NULL
50            select @option = 'show'
51    
52        /* only allowed */
53        if (@option not in ('save', 'show', 'delete'))
54        begin
55            exec sp_getmessage 17150, @msg output
56            raiserror 17150
57            return 1
58        end
59    
60        /* permissions check */
61        if (@option in ('save', 'delete'))
62        begin
63            /*
64            ** If granular permissions is not enabled then sa_role is 
65            ** required. If granular permissions is enabled then the 
66            ** permission 'manage opt goal' is required.  proc_role and 
67            ** proc_auditperm will also do auditing if required. Both will 
68            ** also print error message if required.
69            */
70    
71            execute @status = sp_aux_checkroleperm "sa_role",
72                "manage opt goal", @nullarg, @gp_enabled output
73    
74            if (@status != 0)
75            begin
76                if (@gp_enabled = 0)
77                begin
78                    /* 
79                    ** The user does not 
80                    ** have SA role audit this as a failed sa 
81                    ** command execution.
82                    */
83                    select @dummy = proc_role("sa_role")
84                    return (1)
85                end
86                else
87                begin
88                    select @dummy =
89                        proc_auditperm("manage opt goal",
90                            @status)
91                    return 1
92                end
93            end
94        end
95    
96        select @sysoption = 0
97        if @name in ('allrows_mix', 'allrows_dss', 'allrows_oltp')
98        begin
99            select @sysoption = 1
100       end
101   
102       if (@option = 'show')
103       begin
104   
105           select @a = count(*) from master..sysattributes where class = 50
106               and object_type = 'qp'
107               and object_cinfo = @name
108   
109           if (@a < 1)
110           begin
111               if (@sysoption = 0)
112               begin
113                   exec sp_getmessage 17149, @msg output
114                   raiserror 17149
115                   return 1
116               end
117           end
118   
119           set plan optgoal @name
120   
121           select @bitmap = @@optoptions
122   
123   
124           select name from master..sysoptions
125           where spid = @@spid and currentsetting = '1'
126               and category like 'Query T%'
127               and scope & 32 = 32
128       end
129   
130       if (@option = 'save')
131       begin
132   
133           if (@sysoption = 1)
134           begin
135               exec sp_getmessage 17151, @msg output
136               raiserror 17151
137               return 1
138           end
139   
140           if (datalength(@name) > 11)
141           begin
142               exec sp_getmessage 17118, @msg output
143               raiserror 17118
144               return 1
145           end
146           select @bitmap = @@optoptions
147   
148           delete master..sysattributes where class = 50
149               and object_type = 'qp'
150               and object_cinfo = @name
151   
152   
153           insert master..sysattributes(class, attribute, object_type, object, object_cinfo, char_value)
154           select 50, 0, 'qp', 1, @name, @bitmap
155   
156           select @a = schema_inc(- 1, - 1)
157   
158       end
159   
160       if (@option = 'delete')
161       begin
162   
163           if (@sysoption = 1)
164           begin
165               exec sp_getmessage 17151, @msg output
166               raiserror 17151
167               return 1
168           end
169   
170           select @a = count(*) from master..sysattributes where class = 50
171               and object_type = 'qp'
172               and object_cinfo = @name
173   
174           if (@a < 1)
175           begin
176               exec sp_getmessage 17149, @msg output
177               raiserror 17149
178               return 1
179           end
180   
181           delete master..sysattributes where class = 50
182               and object_type = 'qp'
183               and object_cinfo = @name
184       end
185   
186   
187       return 0
188   


exec sp_procxmode 'sp_optgoal', 'AnyMode'
go

Grant Execute on sp_optgoal to public
go
RESULT SETS
sp_optgoal_rset_002
sp_optgoal_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 42
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 105
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 125
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 148
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 170
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 181
 TNOI 4 Table with no index master..sysoptions master..sysoptions
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysoptions  
 MGTP 3 Grant to public sybsystemprocs..sp_optgoal  
 MNER 3 No Error Check should check return value of exec 55
 MNER 3 No Error Check should check return value of exec 113
 MNER 3 No Error Check should check return value of exec 135
 MNER 3 No Error Check should check return value of exec 142
 MNER 3 No Error Check should check @@error after delete 148
 MNER 3 No Error Check should check @@error after insert 153
 MNER 3 No Error Check should check return value of exec 165
 MNER 3 No Error Check should check return value of exec 176
 MNER 3 No Error Check should check @@error after delete 181
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 174
 MUOT 3 Updates outside transaction 181
 QCRS 3 Conditional Result Set 41
 QCRS 3 Conditional Result Set 124
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 153
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type}
42
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, object_cinfo}
105
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, object_cinfo}
148
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, object_cinfo}
170
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, object_cinfo}
181
 VNRD 3 Variable is not read @dummy 88
 VNRD 3 Variable is not read @msg 176
 MRST 2 Result Set Marker 41
 MRST 2 Result Set Marker 124
 MTR1 2 Metrics: Comments Ratio Comments: 29% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 21dec - 10exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 104 25

DATA PROPAGATION detailed
ColumnWritten To
@namesysattributes.object_cinfo   sp_optgoal_rset_002.name sp_passwordpolicy_rset_001.value sp_rjs_retrieve_rset_001.js_server

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_optgoal_rset_002 
writes table sybsystemprocs..sp_optgoal_rset_001 
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
reads table master..sysoptions (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table master..sysattributes (1)