If you are migrating between major releases, such as moving from a 16.xx release to a 17.xx release, the database restoration process automatically recompiles SQL procedures if the corresponding source code has been saved in the database.
To prevent unauthorized modification, replication, or distribution of the source code, procedures provided by Teradata and third-party vendors generally are not saved with their source code. These procedures cannot be recompiled during migrations or upgrades.
The pre-migration preparation script generates a file, sp_nospllist.txt, which lists SQL stored procedures that were not saved with their source code. These procedures must be either modified prior to the migration or upgrade, or must be recreated after the migration or upgrade.
-
View the sp_nospllist.txt report.
This report shows the stored procedures that do not have their source code stored in the database.The report shows stored procedure names in both ASCII text and hexint forms.
-
Recreate each listed stored procedure in one of the following ways:
- Procedures that are exclusive to your site, and that do not include source code, should be modified using ALTER PROCEDURE with the WITH SPL clause to store their source code in the database. This allows these procedures to be recompiled during the migration or upgrade. For more information on ALTER PROCEDURE, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- Teradata-provided SQL stored procedures that do not have source code are recreated during the upgrade/migration process during the execution of DIP scripts. For more information on the DIP utility, see Teradata Vantage™ - Database Utilities , B035-1102 .
- Stored procedures provided by third party vendors usually do not include source code. These require re-installation of a software package, and possibly assistance from the vendor.