Rebind all of the packages (including the one associated with the SQL procedure).Run the REBIND command against the package.USE TestDB GO CREATE PROCEDURE TestSniffingRecompile CustomerCategoryID CHAR(1) WITH RECOMPILE AS SELECT C.CustomerName, C.LastBuyDate FROM dbo. where the package's name is P2355101 and its schema is SCHEMA1. As an example, below is the sample code to create our previous stored procedure with the recompile option. The user who runs the stored procedure is not the owner of the. Stored Procedure is executed without Fully Qualified Name. Additional Scenarios that lead to compile locks. The solution in this case is to reduce or to eliminate the recompiles. Stored Procedure is executed without Fully Qualified Name. Stored procedure recompilation is one explanation for compile locks on a stored procedure or trigger. You can force recompilation of plans by running sprecompile against an object most common choices are a table or a stored procedure. Stored procedure recompilation is one explanation for compile locks on a stored procedure or trigger. ROUTINESCHEMA ROUTINENAME PKGSHEMA PKGNAME Using the sprecompile System Stored Procedure This sometimes comes in handy if you need to intervene during a production incident if you’ve got a bad plan being used repeatedly by incoming sessions. Output from the above query will be similar to the following: Determine the package that is associated with the SQL procedure.įor example, run the following query to get the corresponding package name and its schema for the SQL procedure SCHEMA1.PROC1:ĭb2 "select b.routineschema, b.routinename, a.bschema pkgshema, a.bname pkgname from syscat.routinedep a, syscat.routines b where a.routinename=b.specificname and a.routineschema=b.routineschema and b.routinename='PROC1'and b.routineschema='SCHEMA1'".Determine the package associated with the SQL procedure and rebind it manually.Use the built-in system stored procedure SYSPROC.REBIND_ROUTINE_PACKAGE to rebind the package associated with the SQL procedure.įor example, to rebind the SQL procedure SCHEMA1.PROC1, run:ĭb2 "CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'SCHEMA1.PROC1', 'ANY')".One of the following methods can be used to achieve the same objective: Dropping and recreating the SQL procedure can be inconvenient and requires access to source code.
0 Comments
Leave a Reply. |