DatabaseProcApplicationCreatedLinks
sybsystemprocssp_showexeclass  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** Messages for "sp_showexeclass"
6     **
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18277, "Execution class '%1!' is not a valid class"
9     */
10    
11    create or replace procedure sp_showexeclass
12        @execlassname varchar(255) = NULL /* Class name */
13    
14    as
15    
16        declare @attrib_id int,
17            @engine int,
18            @term int,
19            @object_type varchar(2),
20            @eng_gr varchar(30),
21            @anyengine varchar(30),
22            @lastengine varchar(30),
23            @priority varchar(10),
24            @ts_str varchar(10),
25            @eng_str varchar(255),
26            @eng_num varchar(10),
27            @eng_list varchar(255),
28            @len int,
29            @upcase_str varchar(255),
30            @instanceid int, /* instance id */
31            @i smallint, /* loop index to iterate sysengines */
32            @max_instance smallint /* max instance number for cluster */
33    
34        select @instanceid = NULL
35        IF (@@kernelmode = 'process')
36        BEGIN
37            select @anyengine = 'ANYENGINE'
38            select @lastengine = 'LASTONLINE'
39        END
40        ELSE
41        BEGIN
42            select @anyengine = 'syb_default_pool'
43            select @lastengine = 'syb_default_pool'
44        END
45    
46        /*
47        **  IF we're in a transaction, disallow this since it might make recovery
48        **  impossible.
49        */
50        IF @@trancount > 0
51        BEGIN
52            /*
53            ** 17260, "Can't run %1! from within a transaction."
54            */
55            raiserror 17260, "sp_showexeclass"
56            return (1)
57        END
58        else
59        BEGIN
60            /* Use TSQL mode of unchained transactions */
61            set chained off
62        END
63    
64        /* Dont do any "Dirty Reads" */
65        set transaction isolation level 1
66    
67        /* first convert any system defined class name to upper case */
68        IF (@execlassname is not NULL)
69        BEGIN
70            select @upcase_str = upper(@execlassname)
71            IF ((@upcase_str = "EC1") OR (@upcase_str = "EC2") OR
72                    (@upcase_str = "EC3"))
73                select @execlassname = @upcase_str
74        END
75    
76        /* check that class value is ok */
77        IF ((@execlassname is not NULL) AND (@execlassname != 'EC1') AND
78                (@execlassname != 'EC2') AND
79                (@execlassname != 'EC3'))
80        BEGIN
81            IF not exists (select * from master..sysattributes where
82                        class = 6 AND
83                        attribute = 2 AND
84                        object_cinfo = @execlassname)
85    
86            BEGIN
87                /*
88                ** 18277, "Execution class '%1!' is not a valid class"
89                */
90                raiserror 18277, @execlassname
91                return (1)
92            END
93        END
94    
95        /* 
96        ** Now hook up with Sysattributes Table...
97        ** Verify that the attributes passed to sysattributes is correct.
98        ** Errors will be displayed from the builtin functions. Note that for
99        ** stored procedures, the sysattributes table in the current database is
100       ** used. For other objects, the sysattributes table in master is used.
101       */
102   
103       select @attrib_id = 2
104       select @object_type = "UC"
105   
106       CREATE table #shecl_desc
107       (classname varchar(30),
108           priority varchar(10),
109           engine_group varchar(30),
110           engines varchar(30) null)
111   
112       /* Collect the attribute values for 'EC1' Class */
113       IF ((@execlassname = "EC1") OR (@execlassname is NULL))
114       BEGIN
115           select @priority = 'HIGH'
116           select @eng_gr = @anyengine
117           select @eng_str = 'ALL'
118           insert into #shecl_desc
119           values ("EC1", @priority, @eng_gr, @eng_str)
120   
121       END
122   
123       /* Collect the attribute values for 'EC2' Class */
124       IF ((@execlassname = "EC2") OR (@execlassname is NULL))
125       BEGIN
126           select @priority = 'MEDIUM'
127           select @eng_gr = @anyengine
128           select @eng_str = 'ALL'
129           insert into #shecl_desc
130           values ("EC2", @priority, @eng_gr, @eng_str)
131   
132       END
133   
134       /* Collect the attribute values for 'EC3' Class */
135       IF ((@execlassname = "EC3") OR (@execlassname is NULL))
136       BEGIN
137           select @priority = 'LOW'
138           select @eng_gr = @lastengine
139           if (@@system_view != "cluster")
140           BEGIN
141               IF (@@kernelmode = 'process')
142                   select @engine = (select max(engine) from master..sysengines
143                           where (status = 'online'))
144               ELSE
145                   select @engine = NULL
146               select @eng_str = convert(char(5), @engine)
147               insert into #shecl_desc
148               values ("EC3", @priority, @eng_gr, @eng_str)
149           END
150   
151       END
152   
153       /* Collect attribute values for specified user defined class or all classes */
154       IF ((@execlassname is NULL) OR (@execlassname NOT IN ("EC1", "EC2", "EC3")))
155       BEGIN
156           DECLARE class_info cursor for
157           (select object_cinfo, char_value, object_info3 from
158               master..sysattributes where
159               (class = 6 AND
160                   attribute = @attrib_id AND
161                   object_type = @object_type))
162   
163           DECLARE @class_str varchar(255),
164               @classname varchar(30)
165   
166           OPEN class_info
167   
168           FETCH class_info into @classname, @class_str, @instanceid
169           WHILE (@@sqlstatus != 2)
170           BEGIN
171               IF ((@execlassname is NULL) OR
172                       (@execlassname = @classname))
173               BEGIN
174                   /*
175                   ** Parse the string to get the task priority
176                   ** and engine group name for this class.
177                   ** The class string is in the format 
178                   **  :  : enginegroup
179                   ** with 10/10/30 length fields respectively,
180                   ** in addition to the delimitors.
181                   */
182                   select @term = charindex(":", @class_str)
183   
184                   select @priority =
185                       substring(@class_str, 1, (@term - 1))
186                   select @ts_str =
187                       substring(@class_str, (@term + 1), 10)
188                   select @eng_gr =
189                       substring(@class_str, (@term + 12), 30)
190   
191                   IF (@@kernelmode = 'process')
192                   BEGIN
193                       IF (@eng_gr = 'ANYENGINE')
194                           select @eng_list = 'ALL'
195                       ELSE IF (@eng_gr = 'LASTONLINE')
196                       BEGIN
197                           /*
198                           ** For SDC, output format
199                           **   "engine#  instance  
200                           */
201                           select @engine = (select max(engine)
202                                   from master..sysengines
203   
204                                   where (status = 'online'))
205   
206                           select @eng_list = convert(char(5),
207                               @engine)
208   
209                       END
210                       ELSE
211                       BEGIN
212                           /*
213                           ** Get the list of engines from the 
214                           ** group name. The engine list is in
215                           ** the format engine # : engine # : ..
216                           ** For SMP or SDC with instance scope
217                           ** only local bound engine group
218                           ** For SDC with cluster scope
219                           ** local and remote engine group 
220                           */
221                           select @eng_str = (select char_value from
222                                       master..sysattributes
223                                   where (class = 6 AND
224                                           attribute = 1 AND
225                                           object_type = 'EG' AND
226   
227                                           object_cinfo = @eng_gr))
228   
229                           /* format the list of engines */
230                           select @len = 0
231                           select @term = charindex(":", @eng_str)
232   
233                           IF (@term != 0)
234                           BEGIN
235                               select @eng_str = rtrim(@eng_str)
236                               while (@len <
237                                       char_length(@eng_str))
238                               BEGIN
239                                   select @eng_num =
240                                       substring(@eng_str,
241                                           (@len + 1),
242                                           (@term - 1))
243                                   IF (@len = 0)
244                                       select
245                                           @eng_list =
246                                           ltrim(@eng_num)
247                                   ELSE
248                                       select
249                                           @eng_list =
250                                           @eng_list +
251                                           "," +
252                                           ltrim(@eng_num)
253                                   select @len = @len +
254                                       @term
255                               END
256                           END
257                           ELSE
258                           BEGIN
259                               select @eng_list = @eng_str
260                           END
261   
262                       END
263                   END
264                   ELSE
265                       select @eng_list = NULL
266   
267                   /* Insert all the collected values */
268                   insert into #shecl_desc
269                   values (@classname, @priority, @eng_gr,
270                       @eng_list)
271               END
272               FETCH class_info into @classname, @class_str,
273                   @instanceid
274           END
275   
276           CLOSE class_info
277           deallocate cursor class_info
278   
279       END
280   
281       /* Display values and cleanup */
282       IF (@@kernelmode = 'process')
283           /* Adaptive Server has expanded all '*' elements in the following statement */ select #shecl_desc.classname, #shecl_desc.priority, #shecl_desc.engine_group, #shecl_desc.engines from #shecl_desc
284       ELSE
285           select classname, priority, engine_group as "threadpool" from #shecl_desc
286   
287       drop table #shecl_desc
288       return (0)
289   


exec sp_procxmode 'sp_showexeclass', 'AnyMode'
go

Grant Execute on sp_showexeclass to public
go
RESULT SETS
sp_showexeclass_rset_002
sp_showexeclass_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch engines: varchar(30) = varchar(255) 119
 MTYP 4 Assignment type mismatch engines: varchar(30) = varchar(255) 130
 MTYP 4 Assignment type mismatch engines: varchar(30) = varchar(255) 148
 MTYP 4 Assignment type mismatch engines: varchar(30) = varchar(255) 270
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 82
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 83
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 159
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 160
 QTYP 4 Comparison type mismatch smallint = int 160
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 223
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 224
 TNOI 4 Table with no index master..sysengines master..sysengines
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause class_info 157
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysengines  
 MGTP 3 Grant to public sybsystemprocs..sp_showexeclass  
 MNER 3 No Error Check should check @@error after insert 118
 MNER 3 No Error Check should check @@error after insert 129
 MNER 3 No Error Check should check @@error after insert 147
 MNER 3 No Error Check should check @@error after insert 268
 MUCO 3 Useless Code Useless Brackets 35
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 169
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 204
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 236
 MUCO 3 Useless Code Useless Brackets 243
 MUCO 3 Useless Code Useless Brackets 282
 MUCO 3 Useless Code Useless Brackets 288
 MUIN 3 Column created using implicit nullability 106
 QCRS 3 Conditional Result Set 283
 QCRS 3 Conditional Result Set 285
 QISO 3 Set isolation level 65
 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, attribute, object_cinfo}
82
 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, attribute}
159
 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: {object_type, object_cinfo, attribute, class}
223
 VUNU 3 Variable is not used @i 31
 VUNU 3 Variable is not used @max_instance 32
 CUPD 2 Updatable Cursor Marker (updatable by default) 157
 MRST 2 Result Set Marker 283
 MRST 2 Result Set Marker 285
 MSUB 2 Subquery Marker 81
 MSUB 2 Subquery Marker 142
 MSUB 2 Subquery Marker 201
 MSUB 2 Subquery Marker 221
 MTR1 2 Metrics: Comments Ratio Comments: 27% 11
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 38 = 39dec - 3exi + 2 11
 MTR3 2 Metrics: Query Complexity Complexity: 137 11

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_showexeclass_rset_002 
writes table sybsystemprocs..sp_showexeclass_rset_001 
reads table master..sysengines (1)  
reads table master..sysattributes (1)  
read_writes table tempdb..#shecl_desc (1)