DatabaseProcApplicationCreatedLinks
sybsystemprocssp_post_xpload  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_post_xpload"
4     **
5     ** 17260, "Can't run %1! from within a transaction."
6     ** 18600, "%1!: Illegal option. The only legal option is 'force'."
7     */
8     create or replace procedure sp_post_xpload
9         @forceopt varchar(30) = NULL
10    as
11        declare @objid int
12        declare @dbname varchar(30)
13        declare @tablename varchar(255)
14        declare @msg varchar(400)
15        declare @numtable int
16        declare @tablecount int
17        declare @sqltext varchar(400)
18        declare @dummy int
19        declare @nullarg char(1)
20        declare @gp_enabled int
21        declare @status1 int
22        declare @status2 int
23    
24        select @status1 = 1
25        select @status2 = 1
26    
27    
28    
29        /*
30        ** dbcc reindex is not allowed in a transaction.
31        */
32        if @@trancount > 0
33        begin
34            /*
35            ** 17260, "Can't run %1! from within a transaction."
36            */
37            raiserror 17260, "sp_post_xpload"
38            return (1)
39        end
40    
41        if @forceopt is not null
42        begin
43            if @forceopt != "force"
44            begin
45                /* 
46                ** 18600, "%1!: Illegal option. 
47                ** The only legal option is 'force'." 
48                */
49                raiserror 18600, sp_post_xpload
50                return (1)
51            end
52        end
53    
54        set chained off
55    
56        set transaction isolation level 1
57    
58        /*
59        **  If granular permissions is not enabled, 
60        **  Accounts with sa_role role can execute it.
61        **  If granular permissions is enabled then users with 'load database' or
62        **  'own database' permission can execute it.
63        */
64        select @dbname = db_name()
65        select @nullarg = NULL
66        execute @status1 = sp_aux_checkroleperm "sa_role", "load database",
67            @dbname, @gp_enabled output
68    
69        if (@gp_enabled = 0)
70        begin
71            /* 
72            ** proc_role() will raise permission errors
73            ** and send audit records to the audit trail.
74            */
75            if (proc_role("sa_role") = 0)
76                return (1)
77        end
78        else
79        begin
80            if (@status1 != 0)
81            begin
82                execute @status2 = sp_aux_checkroleperm @nullarg,
83                    "own database", @dbname, @gp_enabled output
84    
85                if (@status2 != 0)
86                begin
87                    select @dummy = proc_auditperm("load database",
88                            @status1, @dbname)
89                    select @dummy = proc_auditperm("own database",
90                            @status2, @dbname)
91                    return 1
92                end
93            end
94            if (@status1 = 0)
95                select @dummy = proc_auditperm("load database",
96                        @status1, @dbname)
97            if (@status2 = 0)
98                select @dummy = proc_auditperm("own database",
99                        @status2, @dbname)
100       end
101   
102       /*
103       ** Check if this database was loaded from across platform.
104       */
105       set flushmessage on
106       select @dbname = db_name()
107       if not exists (select 1 from master.dbo.sysdatabases
108               where name = @dbname
109                   and (status3 & 524288) != 0)
110       begin
111           select @msg = "Error: Current database "
112               + @dbname
113               + " is not a database that was loaded from a byte-swapped architecture."
114           print @msg
115           return (1)
116       end
117   
118       /*
119       ** Re-partition hash partition tables.
120       */
121   
122       /* Get the number of hash partition tables */
123       select @numtable = count(*)
124       from dbo.sysindexes, dbo.sysobjects
125       where sysobjects.type = 'U'
126           and sysindexes.partitiontype = 2
127           and sysobjects.id = sysindexes.id
128           and (sysindexes.indid = 0 or sysindexes.indid = 1)
129           and sysobjects.sysstat2 & 1024 = 0
130   
131       if (@numtable != 0)
132       begin
133           select @tablecount = 0
134           declare sysobjects_hash_cursor insensitive cursor for
135           select sysobjects.name
136           from dbo.sysindexes(index csysindexes),
137               dbo.sysobjects(index csysobjects)
138           where type = 'U'
139               and sysindexes.partitiontype = 2
140               and sysobjects.id = sysindexes.id
141               and (sysindexes.indid = 0 or sysindexes.indid = 1)
142               and sysobjects.sysstat2 & 1024 = 0
143           open sysobjects_hash_cursor
144           fetch sysobjects_hash_cursor into @tablename
145           while (@@sqlstatus != 2 and @@sqlstatus != 1)
146           begin
147               select @tablecount = @tablecount + 1
148               select @msg = "sp_post_xpload: Repartitioning hash partition table " + @tablename + " ("
149                   + convert(varchar, @tablecount) + "/"
150                   + convert(varchar, @numtable) + ")"
151               print @msg
152               select @sqltext = "reorg rebuild " + @tablename
153               exec (@sqltext)
154               if (@@error != 0)
155               begin
156                   select @msg = "sp_post_xpload: Failed to repartition hash partition table " + @tablename
157                   print @msg
158               end
159               fetch sysobjects_hash_cursor into @tablename
160           end
161       end
162   
163       /*
164       ** Rebuild global clustered index on rr partition tables to regenerate
165       ** partition condition, if the partition key is unichar or univarchar.
166       */
167   
168       /* Get the number of such rr partition tables */
169       select @numtable = count(*)
170       from sysobjects o, sysindexes i1
171       where o.id = i1.id
172           and i1.indid != 0 and i1.indid != 255 and i1.status3 & 1 = 1
173           and o.type = 'U' and o.sysstat2 & 1024 = 0
174           and i1.id in (select i2.id
175               from sysindexes i2, syspartitionkeys p, syscolumns c
176               where i2.id = p.id and p.id = c.id
177                   and i2.indid = p.indid
178                   and i2.indid != 255 and i2.status3 & 1 = 1
179                   and p.colid = c.colid
180                   and c.type in (135, 155))
181       if (@numtable != 0)
182       begin
183           select @tablecount = 0
184           declare sysobjects_rrind_cursor cursor for
185           select o.name
186           from sysobjects o(index csysobjects),
187               sysindexes i1(index csysindexes)
188           where o.id = i1.id
189               and i1.indid != 0 and i1.indid != 255 and i1.status3 & 1 = 1
190               and o.type = 'U' and o.sysstat2 & 1024 = 0
191               and i1.id in (select i2.id
192                   from sysindexes i2(index csysindexes),
193                       syspartitionkeys p(index csyspartitionkeys),
194                       syscolumns c(index csyscolumns)
195                   where i2.id = p.id and p.id = c.id
196                       and i2.indid = p.indid
197                       and i2.indid != 255 and i2.status3 & 1 = 1
198                       and p.colid = c.colid
199                       and c.type in (135, 155))
200           open sysobjects_rrind_cursor
201           fetch sysobjects_rrind_cursor into @tablename
202           while (@@sqlstatus != 2 and @@sqlstatus != 1)
203           begin
204               select @tablecount = @tablecount + 1
205               select @msg = "sp_post_xpload: Rebuilding clustered index on table " + @tablename + " ("
206                   + convert(varchar, @tablecount) + "/"
207                   + convert(varchar, @numtable) + ")"
208               print @msg
209               /* Force rebuild of indexes including APL clustered index */
210               dbcc reindex(@tablename, 24)
211               fetch sysobjects_rrind_cursor into @tablename
212           end
213       end
214   
215       /*
216       ** Rebuild indexes on user tables.
217       */
218   
219       select @numtable = count(*)
220       from dbo.sysobjects
221       where type = 'U'
222   
223       select @tablecount = 0
224   
225       declare sysobjects_cursor cursor for
226       select id, name
227       from dbo.sysobjects(index ncsysobjects)
228       where type = 'U'
229   
230       open sysobjects_cursor
231   
232       fetch sysobjects_cursor into @objid, @tablename
233   
234       while (@@sqlstatus != 2 and @@sqlstatus != 1)
235       begin
236           select @tablecount = @tablecount + 1
237           select @msg = "sp_post_xpload: Processing table " + @tablename + " ("
238               + convert(varchar, @tablecount) + "/"
239               + convert(varchar, @numtable) + ")"
240   
241           print @msg
242           if exists (select id
243                   from dbo.sysindexes
244                   where id = @objid
245                       and indid > 1 and indid != 255)
246           begin
247               if @forceopt = "force"
248                   /* Force rebuild index except APL clustered index.*/
249                   dbcc reindex(@objid, 30)
250               else
251                   /*	
252                   ** We don't need to check a APL table which has
253                   ** only clustered index.
254                   ** Need to check and rebuild non-clustered index on 
255                   ** APL or clusterd/non-cluster index on DOL.
256                   */
257                   dbcc reindex(@objid, 14)
258           end
259   
260           fetch sysobjects_cursor into @objid, @tablename
261       end
262   
263       close sysobjects_cursor
264   
265       deallocate cursor sysobjects_cursor
266   
267       checkpoint
268   
269       return (0)
270   


exec sp_procxmode 'sp_post_xpload', 'AnyMode'
go

Grant Execute on sp_post_xpload to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 174
 QJWI 5 Join or Sarg Without Index 191
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
126
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
139
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
172
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
178
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
189
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
197
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 126
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 128
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 139
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 141
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 172
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 178
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 189
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 197
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 245
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause sysobjects_cursor 226
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 127
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 127
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 140
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 140
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 171
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 171
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 174
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 176
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 176
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitionkeys.id: Warning message on syspartitionkeys 176
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 188
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 188
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 191
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 195
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 195
 MAW1 3 Warning message on %name% sybsystemprocs..syspartitionkeys.id: Warning message on syspartitionkeys 195
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 226
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 242
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 244
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 8
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_post_xpload  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syspartitionkeys  
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 97
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 269
 QFID 3 Force index sybsystemprocs..sysindexes 136
 QFID 3 Force index sybsystemprocs..sysobjects 137
 QFID 3 Force index sybsystemprocs..sysobjects 186
 QFID 3 Force index sybsystemprocs..sysindexes 187
 QFID 3 Force index sybsystemprocs..sysindexes 192
 QFID 3 Force index sybsystemprocs..syspartitionkeys 193
 QFID 3 Force index sybsystemprocs..syscolumns 194
 QFID 3 Force index sybsystemprocs..sysobjects 227
 QISO 3 Set isolation level 56
 QNAJ 3 Not using ANSI Inner Join 124
 QNAJ 3 Not using ANSI Inner Join 136
 QNAJ 3 Not using ANSI Inner Join 170
 QNAJ 3 Not using ANSI Inner Join 175
 QNAJ 3 Not using ANSI Inner Join 186
 QNAJ 3 Not using ANSI Inner Join 192
 QNUA 3 Should use Alias: Table sybsystemprocs..sysindexes 124
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 124
 QNUA 3 Should use Alias: Table sybsystemprocs..sysindexes 136
 QNUA 3 Should use Alias: Table sybsystemprocs..sysobjects 137
 QNUA 3 Should use Alias: Column type should use alias sysobjects 138
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
Uncovered: [number]
176
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syspartitionkeys.csyspartitionkeys unique clustered
(id, indid, colid)
Intersection: {colid, id}
Uncovered: [indid]
176
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid, id}
Uncovered: [number]
195
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syspartitionkeys.csyspartitionkeys unique clustered
(id, indid, colid)
Intersection: {colid, id}
Uncovered: [indid]
195
 VNRD 3 Variable is not read @gp_enabled 83
 VNRD 3 Variable is not read @dummy 98
 CRDO 2 Read Only Cursor Marker (has subqueries) 185
 CUPD 2 Updatable Cursor Marker (updatable by default) 135
 CUPD 2 Updatable Cursor Marker (updatable by default) 226
 MDYS 2 Dynamic SQL Marker 153
 MSUB 2 Subquery Marker 107
 MSUB 2 Subquery Marker 174
 MSUB 2 Subquery Marker 191
 MSUB 2 Subquery Marker 242
 MTR1 2 Metrics: Comments Ratio Comments: 19% 8
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 55 = 59dec - 6exi + 2 8
 MTR3 2 Metrics: Query Complexity Complexity: 178 8
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 123
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 135
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitionkeys} 0 174
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, p=sybsystemprocs..syspartitionkeys} 0 191

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syspartitionkeys  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table sybsystemprocs..sysindexes