DatabaseProcApplicationCreatedLinks
sybsystemprocssp_copy_all_qplans  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_copy_all_qplans"
6     **
7     ** 18639, "There is no query plans group named '%1!' in this database."
8     ** 18656, "Unrecoverable error while copying the query plan %1!. Keeping all plans copied so far and interrupting the copy."
9     ** 18657, "Recoverable error while copying the query plan %1!. Skipping this plan and continuing the copy."
10    ** 18658, "Error while fetching the next query plan to copy. Keeping all plans copied so far and interrupting the copy."
11    */
12    
13    /*
14    ** sp_copy_all_qplans copies all plans in a source group to 
15    ** a destination group. As there could be many of them, rather 
16    ** than having an ACID behavior, the SP attempts to do as much 
17    ** as possible. 
18    ** Note, no learning APs are copied.
19    */
20    
21    create or replace procedure sp_copy_all_qplans
22        @src_group varchar(30),
23        @dest_group varchar(30)
24    as
25        declare
26            @uid int,
27            @src_gid int,
28            @src_id int,
29            @ret int,
30            @rows int,
31            @dummy int,
32            @nullarg char(1),
33            @gp_enabled int,
34            @status int,
35            @dbname varchar(255)
36    
37        set nocount on
38    
39        select @dbname = db_name()
40    
41        /* Don't start a transaction for the user, he does not expect it. */
42        if @@trancount = 0
43        begin
44            set chained off
45        end
46    
47        /* Don't hold long READ locks, the user might not appreciate it. */
48        set transaction isolation level 1
49    
50    
51        /*
52        **  If granular permissions is not enabled
53        **  only the Database Owner (DBO) or
54        **  Accounts with SA role can execute it.
55        **  If the user has SA role, it's uid will
56        **  be DBO uid (1). If granular permissions is enabled then
57        **  users with 'manage abstract plans' permission can execute it.
58        */
59    
60        select @nullarg = NULL
61    
62        execute @status = sp_aux_checkroleperm "dbo",
63            "manage abstract plans", @dbname, @gp_enabled output
64    
65        /* 
66        ** If @status = 0, user is dbo, has sa_role or if granular permissions
67        ** is enabled has 'manage abstract plans' permission.  Therefore the
68        ** user can copy all query plans in the group.
69        */
70        if (@status = 0)
71            select @uid = NULL
72        else
73            select @uid = user_id()
74    
75        exec sp_aux_get_qpgroup @src_group, @src_gid out
76    
77        if @src_gid is null
78        begin
79            /* 18639, "There is no query plans group named '%1!' in this database." */
80            raiserror 18639, @src_group
81    
82            return 1
83        end
84    
85        /* cursor to scroll through the source APs */
86        declare src_ids cursor
87        for
88        select distinct id
89        from sysqueryplans
90        where
91            uid = isnull(@uid, uid)
92            and gid = @src_gid
93            and type = 100
94    
95        open src_ids
96        fetch src_ids into @src_id
97    
98        while @@sqlstatus = 0
99        begin
100           /* insert each source AP in the destination group */
101           exec @ret = sp_copy_qplan @src_id, @dest_group
102   
103           /*
104           ** @ret == 1 meant no destination group. 
105           ** When there's an @@error, probably we can't 
106           ** go on...
107           */
108           if @ret = 1 or @@error != 0
109           begin
110               /* 18656, "Unrecoverable error while copying the query plan %1!. Keeping all plans copied so far and interrupting the copy." */
111               raiserror 18656, @src_id
112   
113               return 1
114           end
115   
116           /*
117           ** This plan is illegal (key collision, etc)
118           ** but try to continue. 
119           */
120           if @ret != 0
121           begin
122               /* 18657, "Recoverable error while copying the query plan %1!. Skipping this plan and continuing the copy." */
123               raiserror 18657, @src_id
124           end
125   
126           fetch src_ids into @src_id
127       end
128   
129       select @rows = @@rowcount
130   
131       if @@sqlstatus = 1
132       begin
133           /* 18658, "Error while fetching the next query plan to copy. Keeping all plans copied so far and interrupting the copy." */
134           raiserror 18658
135   
136           return 1
137       end
138   
139       return 0
140   


exec sp_procxmode 'sp_copy_all_qplans', 'AnyMode'
go

Grant Execute on sp_copy_all_qplans to public
go
DEFECTS
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered
(uid, gid, hashkey, id, type, sequence)
Intersection: {gid, type}
92
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 93
 MAW1 3 Warning message on %name% sybsystemprocs..sysqueryplans.id: Warning message on sysqueryplans 88
 MGTP 3 Grant to public sybsystemprocs..sp_copy_all_qplans  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check return value of exec 75
 MUCO 3 Useless Code Useless Brackets 70
 QGWO 3 Group by/Distinct/Union without order by 88
 QISO 3 Set isolation level 48
 VNRD 3 Variable is not read @nullarg 60
 VNRD 3 Variable is not read @gp_enabled 63
 VNRD 3 Variable is not read @rows 129
 VUNU 3 Variable is not used @dummy 31
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 88
 MTR1 2 Metrics: Comments Ratio Comments: 58% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 11dec - 4exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 44 21

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
   reads table sybsystemprocs..sysattributes  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_copy_qplan  
   read_writes table tempdb..#t (1) 
   read_writes table sybsystemprocs..sysqueryplans  
   calls proc sybsystemprocs..sp_aux_checkroleperm  
   calls proc sybsystemprocs..sp_cmp_qplans  
      calls proc sybsystemprocs..sp_aux_checkroleperm  
      calls proc sybsystemprocs..sp_getmessage  
         reads table sybsystemprocs..sysusermessages  
         reads table master..syslanguages (1)  
         reads table master..sysmessages (1)  
         calls proc sybsystemprocs..sp_validlang  
            reads table master..syslanguages (1)  
      reads table sybsystemprocs..sysqueryplans  
   calls proc sybsystemprocs..sp_aux_get_qpgroup  
reads table sybsystemprocs..sysqueryplans