![]() Rebind all of the packages (including the one associated with the SQL procedure). While your stored procedure is running, you can simply recompile it.Could it be caused by the parameter sniffingMaybe you can try adding OPTION(RECOMPILE) after the query statement in the stored procedureand see if it works. Run the REBIND command against the package. The following is my guess about this issue based on your description and the stored procedure you provided.where the package's name is P2355101 and its schema is SCHEMA1. ROUTINESCHEMA ROUTINENAME PKGSHEMA PKGNAME 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'".You could use management studio or your source control to generate a concatenated create script of all procedures into a single file and then run that. Explicit recompilation eliminates the need for implicit run-time. You can force recompilation of plans by running sprecompile against an object most common choices are a table or a stored procedure. Also, you could use sprecompile: EXEC sprecompile YourChangedTable but that only marks associated stored procedures for recompile when they are run the next time. Use the ALTER PROCEDURE statement to explicitly recompile a standalone stored procedure. Determine the package associated with the SQL procedure and rebind it manually. 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.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. |