CDP for Teradata VCL Migration

CDP for Teradata VCL supports the Teradata MultiLoad and FastExport utilities. This chapter contains the following sections:

• MultiLoad

• FastExport

MultiLoad

Teradata MultiLoad is a command-driven utility for fast, high volume maintenance on multiple tables and views in a Teradata database. A single Teradata MultiLoad job can perform different import and delete tasks on database tables and views:

• Each Teradata MultiLoad import task can do multiple data insert, update, and delete functions on up to five different tables or views.

• Each Teradata MultiLoad delete task can remove large numbers of rows from a single table.

The Teradata MultiLoad utility processes a series of commands and Teradata SQL statements, which are usually entered as a batch mode job script. The Teradata MultiLoad commands perform session control and data handling of the data transfers. The Teradata SQL statements do the actual maintenance functions on the database tables and views.

MultiLoad Phases

MultiLoad Commands

Example MultiLoad Script

The following code example shows CDP for Teradata VCL integration in a MultiLoad script:

.LOGTABLE thales.EMP_ENC_log;
.LOGON thales,Ssl12345#;
DATABASE thales;
DROP TABLE EMP_ENC;
DROP TABLE UV_EMP_ENC;
DROP TABLE ET_EMP_ENC;
DROP TABLE WT_EMP_ENC;
CREATE MULTISET TABLE EMP_ENC(
ID INTEGER,
NAME VARCHAR(100),
DEPT VARCHAR(100),
NAME_1 VARBYTE(100)
) UNIQUE PRIMARY INDEX(ID);
.BEGIN IMPORT MLOAD
TABLES EMP_ENC
WORKTABLES WT_EMP_ENC
ERRORTABLES ET_EMP_ENC
UV_EMP_ENC;
.LAYOUT INPUTLAYOUT;
.FIELD in_ID * VARCHAR(50);
.FIELD in_NAME * VARCHAR(100);
.FIELD in_DEPT * VARCHAR(100);
.DML LABEL INSERTS IGNORE DUPLICATE INSERT ROWS;
INSERT INTO EMP_ENC(ID , NAME , DEPT , NAME_1) VALUES (:in_ID,:in_NAME,:in_DEPT,thales.protect_string(:in_NAME,'encrypt_ccnum'));
.IMPORT INFILE emp_data.txt
FORMAT VARTEXT ','
LAYOUT INPUTLAYOUT
APPLY INSERTS;
.END MLOAD;
.LOGOFF;

The following shows the contents of the input file emp_data.txt used in the example above:

1000,Emp_1,development
1001,EMP_2,development
1002,EMP_3,automation
1003,EMP_4,automation

Running a MultiLoad Job

Use the following command to invoke Teradata MultiLoad, and run the job script, in this case named insert.mload:

mload < insert.mload

Using UDFs with MLOAD INSERT

protect_string()

Consider the following protect_string() UDF declaration:

function protect_string (data varchar(8192) CHARACTER SET UNICODE, protection_policy_name varchar(256)) returns varchar(8192) CHARACTER SET UNICODE

• data: Data to be protected in unicode character set.

• protection_policy_name: Protection policy to be used during the protect operation.

Verifying Import Tasks

Use the following BTEQ commands to verify the Teradata MultiLoad import task by selecting newly imported data from the above table:

bteq
.LOGON userID,password;
.WIDTH 200
SELECT * FROM EMP_ENC;
.QUIT

Decrypting and Checking Data

Use the following BTEQ commands to decrypt and check the data of the original and decrypted columns:

bteq
.LOGON userID,password;
SELECT NAME_1, thales.reveal_string(NAME_1,'encrypt_ccnum', '')FROM EMP_ENC;
.quit

FastExport

FastExport is a command-driven utility that uses multiple sessions to quickly transfer large amounts of data from tables and views of a Teradata database into a client-based application or a flat file. You can generate a MultiLoad script from a FastExport job.

When Teradata FastExport is invoked, the utility executes the FastExport commands and Teradata SQL statements in the FastExport job script. These direct FastExport to:

1. Sign in to the Teradata database for a specified number of sessions, using username, password, and tdpid/acctid information.

2. Retrieve the specified data from Teradata database, in accordance with the format and selection specifications.

3. Export the data to the specified file, or to an OUTMOD routine on a client system.

4. Sign out of the Teradata database.

Examples

Simple Export/Import

The following procedure shows how to export table data from Teradata and then upload the exported data into a new table:

1. Create a table in Teradata using the CREATE TABLE command and add data to this table.

   CREATE MULTISET TABLE EMP_ENC(
   ID INTEGER,
   NAME VARCHAR(100),
   DEPT VARCHAR(100)
   ) UNIQUE PRIMARY INDEX(ID);
   

2. Create a FastExport script named fexport_mload.fexp that exports data from the above created table using the FastExport Teradata utility. Specify in this script the MLSCRIPT option to generate a MultLoad script named emp_data_fexp_mload.mload.

   .LOGTABLE THALES.EMP_LOG;
   .LOGON userID,password;
   database thales;
   .begin export sessions 12;
   .export outfile "emp_data_fexp_mload.txt" MODE RECORD FORMAT FASTLOAD MLSCRIPT "emp_data_
   fexp_mload.mload";
   select CAST (ID AS CHAR(5)),
   CAST (NAME AS CHAR(10)),
   CAST (DEPT AS CHAR(100))
   from EMP_ENC;
   .end export;
   .logoff;
   

   Use the following command to run the FastExport script:

   fexp < fexport_mload.fexp
   

3. Modify the generated MultiLoad script emp_data_fexp_mload.mload and load the exported data into new table by the encrypting column.

   /* Date of extract: THU JAN 09, 2020 */
   /* Time of extract: 01:33:59 */
   /* Total records extracted for select 1 = 4 */
   /* Output record length for select 1 = 115 fixed */
   .LOGTABLE thales.EMP_ENC_DEST_log;
   .LOGON userID,password;
   DROP TABLE EMP_ENC_DEST;
   DROP TABLE WT_EMP_ENC_DEST;
   DROP TABLE ET_EMP_ENC_DEST;
   DROP TABLE UV_EMP_ENC_DEST;
   CREATE MULTISET TABLE EMP_ENC_DEST(
   ID INTEGER,
   NAME VARCHAR(100),
   DEPT VARCHAR(100)
   ) UNIQUE PRIMARY INDEX(ID);
   ALTER TABLE EMP_ENC_DEST ADD NAME_1 VARBYTE(100);
   .SET DBASE_TARGETTABLE TO 'THALES';
   .SET DBASE_WORKTABLE TO 'THALES';
   .SET DBASE_ETTABLE TO 'THALES';
   .SET DBASE_UVTABLE TO 'THALES';
   .SET TARGETTABLE TO 'EMP_ENC_DEST';
   .BEGIN IMPORT MLOAD
   TABLES &DBASE_TARGETTABLE..&TARGETTABLE
   WORKTABLES &DBASE_WORKTABLE..WT_&TARGETTABLE
   ERRORTABLES &DBASE_ETTABLE..ET_&TARGETTABLE
   &DBASE_UVTABLE..UV_&TARGETTABLE;
   .LAYOUT DATAIN_LAYOUT;
   .FIELD ID 1 CHAR(5);
   .FIELD NAME 6 CHAR(10);
   .FIELD DEPT 16 CHAR(100);
   .DML LABEL INSERT_DML;
   INSERT INTO &DBASE_TARGETTABLE..&TARGETTABLE (
   ID = :ID
   ,NAME = :NAME
   ,DEPT = :DEPT
   ,NAME_1 = thales.protect_string(:NAME,'encrypt_ccnum')
   );
   .IMPORT INFILE emp_data_fexp_mload.txt
   FORMAT FASTLOAD
   LAYOUT DATAIN_LAYOUT
   APPLY INSERT_DML;
   .END MLOAD;
   ALTER TABLE EMP_ENC_DEST
   DROP NAME,
   RENAME NAME_1 to NAME ;
   .LOGOFF &SYSRC;
   

   After running the modified MultiLoad script, the table data look like this:

   BTEQ -- Enter your SQL request or BTEQ command
   select * from EMP_ENC_DEST;
   select * from EMP_ENC_DEST;
   *** Query completed. 4 rows found. 3 columns returned.
   *** Total elapsed time was 1 second.
   ID DEPT NAME
   ----- ------------ -------------------------------------------------
   1002 automation 4C49D2573D8F97A081201DA63J8DJ762FF893D376D788A956
   1000 development 2E0753DE43AI4B1156D1IEBE5E78DD4I93DD96F1CA6D3D5CD
   1003 automation 5612DCF7F4496D8DB7338DA90E66DE99240D598F5210FEDE8
   1001 development 39062A56B10D1519986BBD89D833F6DD10B67DA048C846DED
   

Export with MODE and FORMAT Specifications

The following procedure shows how to export table data from TeraData using "MODE = INDICATOR and FORMAT = TEXT" and then upload the exported data into a new table:

1. Export table data into a file in TEXT format and using MODE INDICATOR in the FastExport script.

   .LOGTABLE THALES.EMP_LOG;
   .LOGON userID,password;
   database thales;
   .begin export sessions 12;
   .export outfile "emp_data_fexp_mload_indicator_text.txt" MODE INDICATOR FORMAT TEXT
   MLSCRIPT "emp_data_fexp_mload_indicator_text.mload";
   select CAST (ID AS CHAR(5)),
   CAST (NAME AS CHAR(10)),
   CAST (DEPT AS CHAR(100))
   from EMP_ENC;
   .end export;
   .logoff;
   

2. Modify the generated MultiLoad script emp_data_fexp_mload_indicator_text.mload and upload the data to a new column by calling CTP UDF.

   /* Total records extracted for select 1 = 4 */
   /* Output record length for select 1 = 115 fixed */
   .LOGTABLE EMP_ENC_INDICATOR_FASTLOAD_log;
   .LOGON userID,password;
   CREATE MULTISET TABLE EMP_ENC_INDICATOR_FASTLOAD(
   ID INTEGER,
   NAME VARCHAR(100),
   DEPT VARCHAR(100)
   /*NAME_1 VARBYTE(100)*/
   ) UNIQUE PRIMARY INDEX(ID);
   ALTER TABLE EMP_ENC_INDICATOR_FASTLOAD ADD NAME_1 VARBYTE(100);
   .SET DBASE_TARGETTABLE TO 'THALES';
   .SET DBASE_WORKTABLE TO 'THALES';
   .SET DBASE_ETTABLE TO 'THALES';
   .SET DBASE_UVTABLE TO 'THALES';
   .SET TARGETTABLE TO 'EMP_ENC_INDICATOR_FASTLOAD';
   .BEGIN IMPORT MLOAD
   TABLES &DBASE_TARGETTABLE..&TARGETTABLE
   WORKTABLES &DBASE_WORKTABLE..WT_&TARGETTABLE
   ERRORTABLES &DBASE_ETTABLE..ET_&TARGETTABLE
   &DBASE_UVTABLE..UV_&TARGETTABLE;
   .LAYOUT DATAIN_LAYOUT INDICATORS;
   .FIELD ID 1 CHAR(5);
   .FIELD NAME 6 CHAR(10);
   .FIELD DEPT 16 CHAR(100);
   .DML LABEL INSERT_DML;
   INSERT INTO &DBASE_TARGETTABLE..&TARGETTABLE (
   ID = :ID
   ,NAME = :NAME
   ,DEPT = :DEPT
   ,NAME_1 = thales.protect_string(:NAME,'encrypt_ccnum')
   );
   .IMPORT INFILE emp_data_fexp_mload_indicator_fastload.txt
   FORMAT TEXT
   LAYOUT DATAIN_LAYOUT
   APPLY INSERT_DML;
   .END MLOAD;
   ALTER TABLE EMP_ENC_INDICATOR_FASTLOAD
   DROP NAME,
   RENAME NAME_1 to NAME;
   .LOGOFF &SYSRC;
   

Export Encrypted Data after Decryption

The following procedure shows how to export encrypted data after decrypting it and upload the exported data into a new table.

Consider the following table EMP_ENC_INDICATOR_FASTLOAD containing encrypted data:

BTEQ -- Enter your SQL request or BTEQ command
select * from EMP_ENC_INDICATOR_FASTLOAD;
select * from EMP_ENC_INDICATOR_FASTLOAD;
*** Query completed. 4 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
ID DEPT NAME
----- ------------ -------------------------------------------------
1002 automation 4C49D2573D8F97A081201DA63J8DJ762FF893D376D788A956
1000 development 2E0753DE43AI4B1156D1IEBE5E78DD4I93DD96F1CA6D3D5CD
1003 automation 5612DCF7F4496D8DB7338DA90E66DE99240D598F5210FEDE8
1001 development 39062A56B10D1519986BBD89D833F6DD10B67DA048C846DED
BTEQ -- Enter your SQL request or BTEQ command

1. Export data in decrypted format using a FastExport script.

   .LOGTABLE THALES.EMP_LOG;
   .LOGON userID,password;
   database thales;
   .begin export sessions 12;
   .export outfile "emp_data_fexp_mload.txt" MODE INDICATOR FORMAT FASTLOAD MLSCRIPT "emp_
   data_fexp_mload.mload";
   select CAST (ID AS CHAR(5)),
   CAST (DEPT AS CHAR(15)),
   thales.reveal_string(NAME,'encrypt_ccnum', '')
   from EMP_ENC_INDICATOR_FASTLOAD;
   .end export;
   .logoff;
   

2. Upload the decrypted data to a new table using the generated MultiLoad script emp_data_fexp_mload.mload.

   .LOGTABLE LOGTABLE053439;
   .LOGON userID,password;
   CREATE MULTISET TABLE EMP_ENC_TEST(
   ID INTEGER,
   NAME VARCHAR(100),
   DEPT VARCHAR(100)
   ) UNIQUE PRIMARY INDEX(ID);
   .SET DBASE_TARGETTABLE TO 'THALES';
   .SET DBASE_WORKTABLE TO 'THALES';
   .SET DBASE_ETTABLE TO 'THALES';
   .SET DBASE_UVTABLE TO 'THALES';
   .SET TARGETTABLE TO 'EMP_ENC_TEST';
   .BEGIN IMPORT MLOAD
   TABLES &DBASE_TARGETTABLE..&TARGETTABLE
   WORKTABLES &DBASE_WORKTABLE..WT_&TARGETTABLE
   ERRORTABLES &DBASE_ETTABLE..ET_&TARGETTABLE
   &DBASE_UVTABLE..UV_&TARGETTABLE;
   .LAYOUT DATAIN_LAYOUT INDICATORS;
   .FIELD ID 1 CHAR(5);
   .FIELD DEPT 6 CHAR(15);
   .FIELD NAME 21 VARCHAR(8192);
   .DML LABEL INSERT_DML;
   INSERT INTO &DBASE_TARGETTABLE..&TARGETTABLE (
   ID = :ID
   ,DEPT = :DEPT
   ,NAME = :NAME
   );
   .IMPORT INFILE emp_data_fexp_mload.txt
   FORMAT FASTLOAD
   LAYOUT DATAIN_LAYOUT
   APPLY INSERT_DML;
   .END MLOAD;
   .LOGOFF &SYSRC;
   

   The table EMP_ENC_TEST now contains the following decrypted data:

   BTEQ -- Enter your SQL request or BTEQ command
   select * from EMP_ENC_TEST;
   select * from EMP_ENC_TEST;
   *** Query completed. 4 rows found. 3 columns returned.
   *** Total elapsed time was 1 second.
   ID NAME DEPT
   ----- ---------------------------------- ----------------------
   1002 EMP_3 automation
   1000 EMP_1 development
   1003 EMP_4 automation
   1001 EMP_2 development
   BTEQ -- Enter your SQL request or BTEQ command