| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_showexeclass | | 31 Aug 14 | Defects 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 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 **: 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: enginegroup 179 ** with 10/10/30 length fields respectively, 180 ** in addition to the delimitors. 181 */ 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: 37 = 38dec - 3exi + 2 | 11 |
| MTR3 2 Metrics: Query Complexity Complexity: 137 | 11 |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysattributes (1)  reads table master..sysengines (1) read_writes table tempdb..#shecl_desc (1) |