Your suggested change has been received. Thank you.

close

Suggest A Change

https://thales.na.market.dpondemand.io/docs/dpod/services/kmo….

Thales Logo

All

  • All
back

Teradata MultiLoad and FastExport

FastExport

search

Please Note:

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 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 Teradata database.

Simple Export/Import

The following procedure shows how to export table data from Teradata and 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.encrypt_cbc(: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 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.encrypt_cbc(: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.decrypt_cbc(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

On this page