Copy SQL Stored Procedures | Teradata Data Mover - 17.10 - About Copying SQL Stored Procedures - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.10
Release Date
June 2021
Content Type
User Guide
Publication ID
B035-4101-061K
Language
English (United States)
A SQL stored procedure is a set of SQL statements with an assigned name that is stored in the database in compiled form so that it can be shared by a number of programs. An example SQL stored procedure can be created with the following SQL statements:
CREATE PROCEDURE SP_deleteTestTable ()
BEGIN 
DELETE FROM test;
END;
After the stored procedure is created, its definition can be retrieved by executing a show procedure query:
SHOW PROCEDURE SP_deleteTestTable;

Copying SQL Stored Procedures Using Data Mover

Data Mover copies SQL stored procedures as follows:
  • Data Mover retrieves the SQL stored procedure creation DDL by executing a show procedure dbname.spname SQL statement.
  • Data Mover does not parse a create procedure DDL; it executes the exact same DDL on the target system. It does not relocate or rename stored procedure objects.
  • If the procedure name in the procedure definition is not fully qualified, the procedure is created in the database having the same name as the source procedure database.
  • If the table names in the procedure definition are not fully qualified, those tables must exist in the database having the same name as the source procedure database to avoid an error.
  • If the object being copied does not exist on the target database, it is created on the target database. If the object does exist on the target database, the object is overwritten – unless the overwrite_existing_objects property is set to false. In that case, a create time error is generated.
  • If a procedure is part of a full database copy, the procedure is copied with the database using DSA. It is not copied separately.
When copying a SQL stored procedure, the underlying table for the procedure must already exist on the target system, or be copied with the job. Depending on the stored procedure creation SQL, a JDBC error may occur when creating a stored procedure without the underlying table.