FastExport Example

Here’s the input script (contents of fastExport01.sql, which is used as the input file below)

/* --------------------------------------*/
/* @(#) FASTEXPORT SCRIPT                */
/* @(#) Version 0.1                      */
/* @(#) Created by Partha                */
/* --------------------------------------*/

/* Setup the Fast Export Parameters */

.LOGTABLE partha.fexplog01;

.LOGON suse/partha,partha;

    

/* CREATE LOGTABLE AND LOGON */

.BEGIN EXPORT

     SESSIONS 1;

    

.EXPORT OUTFILE fexpOut01.txt

MODE RECORD FORMAT TEXT;

    

SELECT *

FROM bipm_competency_results_cmprsd;

/* Finish the Export Job and Write to File */

.END EXPORT;

.LOGOFF;

And here’s the script output in Windows cmd shell:

F:\Dropbox>fexp < fastExport01.sql

     ========================================================================

     =                                                                      =

     =          FastExport Utility    Release FEXP.13.10.00.003             =

     =          Platform WIN32                                              =

     =                                                                      =

     ========================================================================

     =                                                                      =

     =     Copyright 1990-2010 Teradata Corporation. ALL RIGHTS RESERVED.   =

     =                                                                      =

     ========================================================================

**** 14:24:46 UTY2411 Processing start date: MON OCT 17, 2011

     ========================================================================

     =                                                                      =

     =          Logon/Connection                                            =

     =                                                                      =

     ========================================================================

0001 /* ---------------------------------------------------------------*/

     /* @(#) FASTEXPORT SCRIPT

        */

     /* @(#) Version 1.1                                                 */

     /* @(#) Created by Partha

         */

     /* ---------------------------------------------------------------*/

     /* Setup the Fast Export Parameters */

     .LOGTABLE partha.fexplog01;

0002 .LOGON suse/partha,;

**** 14:24:56 UTY8400 Teradata Database Release: 12.00.03.12

**** 14:24:56 UTY8400 Teradata Database Version: 12.00.03.14

**** 14:24:56 UTY8400 Default character set: ASCII

**** 14:24:56 UTY8400 Current RDBMS has UDT support

**** 14:24:56 UTY8400 Current RDBMS has Large Decimal support

**** 14:24:56 UTY8400 Current RDBMS doesn't have FEXP w/o Spooling support

**** 14:24:56 UTY8400 Maximum supported buffer size: 1M

**** 14:24:56 UTY8400 Data Encryption supported by RDBMS server

**** 14:24:57 UTY6211 A successful connect was made to the RDBMS.

**** 14:24:57 UTY6217 Logtable 'partha.fexplog01' has been created.

     ========================================================================

     =                                                                      =

     =          Processing Control Statements                               =

     =                                                                      =

     ========================================================================

0003

     /* CREATE LOGTABLE AND LOGON */

     .BEGIN EXPORT

        SESSIONS 1;

0004

     .EXPORT OUTFILE fexpOut01.txt

     MODE RECORD FORMAT TEXT;

0005

     SELECT *

     FROM bipm_competency_results_cmprsd;

0006 /* Finish the Export Job and Write to File */

     .END EXPORT;

     ========================================================================

     =                                                                      =

     =          FastExport Initial Phase                                    =

     =                                                                      =

     ========================================================================

**** 14:24:57 UTY8700 Options in effect for this FastExport task:

     .       Sessions:    1 session(s).

     .       Mode:        RECORD

     .       Blocksize:   64330 bytes.

     .       Outlimit:    No limit in effect.

**** 14:24:57 UTY8715 FastExport is submitting the following request:

     Select NULL from partha.fexplog01 where (LogType = 220) and (Seq = 1) and

     (FExptSeq = 0);

**** 14:24:57 UTY8705 EXPORT session(s) requested: 1.

**** 14:24:57 UTY8706 EXPORT session(s) connected: 1.

**** 14:24:57 UTY8715 FastExport is submitting the following request:

     BT;BEGIN FASTEXPORT;

**** 14:24:57 UTY8715 FastExport is submitting the following request:

     SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER), MiscInt3 (INTEGER),FExptSeq

     (INTEGER), FExptCkpt (VARBYTE(1024)) from partha.fexplog01 WHERE (LogType =

     210) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq) from

     partha.fexplog01 where (LogType = 210) and (Seq = 1)));

**** 14:24:57 UTY8715 FastExport is submitting the following request:

     SELECT MiscInt1 (INTEGER), MiscInt2 (INTEGER),  MiscInt3 (INTEGER),FExptSeq

     (INTEGER), FExptCkpt (VARBYTE(1024)) from partha.fexplog01 WHERE (LogType =

     212) and (Seq = 1) and (FExptSeq IN (SELECT MAX(FExptSeq) from

     partha.fexplog01 where (LogType = 212) and (Seq = 1)));

**** 14:24:57 UTY8715 FastExport is submitting the following request:

     SELECT *

     FROM bipm_competency_results_cmprsd;

**** 14:24:57 UTY8724 Select request submitted to the RDBMS.

**** 14:24:58 UTY8725 Select execution completed. 2 data blocks generated.

**** 14:24:58 UTY8715 FastExport is submitting the following request:

     INS partha.fexplog01 (LogType, Seq) VALUES (220, 1)

**** 14:24:58 UTY8715 FastExport is submitting the following request:

     END FASTEXPORT;ET;

**** 14:24:58 UTY8710 Processing complete for this FastExport task.

     ========================================================================

     =                                                                      =

     =          FastExport Task Complete                                    =

     =                                                                      =

     ========================================================================

**** 14:24:58 UTY8722 629 total records written to output file.

0007 .LOGOFF;

     ========================================================================

     =                                                                      =

     =          Logoff/Disconnect                                           =

     =                                                                      =

     ========================================================================

**** 14:24:58 UTY6216 The restart log table has been dropped.

**** 14:24:58 UTY6212 A successful disconnect was made from the RDBMS.

**** 14:24:58 UTY2410 Total processor time used = '0.171601 Seconds'

     .       Start : 14:24:46 - MON OCT 17, 2011

     .       End   : 14:24:58 - MON OCT 17, 2011

     .       Highest return code encountered = '0'.

A Question of Inalienable Rights

In Teradata, rights on an object are automatically assigned to two entities – the creator of the object and the owner database of the object. The rights of the creator are explicit, i.e. the system explicitly adds 11 or so rows in the AccessRights (DBC.UserRights view) table when the insert statement is issued.

SELECT *

FROM dbc.userrights

WHERE tablename = 'TestTable';

 *** Query completed. No rows found. 

CREATE TABLE TestTable

(TableId INT);

 *** Table has been created. 

SELECT *

FROM dbc.userRights

WHERE tableName = 'TestTable';

 *** Query completed. 11 rows found. 8 columns returned.

DatabaseName                   TableName                      ColumnName

------------------------------ ------------------------------ -------------

partha                         TestTable                      All

partha                         TestTable                      All

partha                         TestTable                      All

partha                         TestTable                      All

partha                         TestTable                      All

partha                         TestTable                      All

partha                         TestTable                      All

partha                         TestTable                      All

partha                         TestTable                      All

partha                         TestTable                      All

partha                         TestTable                      All

REVOKE ALL

ON TestTable

FROM partha;

 *** Revoke accepted.

All explicit rights removed!

 

SELECT *

FROM dbc.userRights

WHERE tableName = 'TestTable';

 *** Query completed. No rows found. 

INSERT INTO TestTable(1001);

 *** Insert completed. One row added.

 *** Total elapsed time was 1 second.

Possible due to implicit ownership rights.

SELECT *

FROM TestTable;

 *** Query completed. One row found. One column returned. 

    TableId

-----------

       1001

As is evident even if the explicit rights are removed from the user, the rights of ownership are inalienable.