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.

take a break - take the leap!

a short clip of my sky dive- hope it encourages others to take the plunge!

DBS CRASHED OR SESSIONS RESET. RECOVERY IN PROGRESS or WSA E HOST UNREACH

This seems to be a fairly common stumbling block for Teradata users. I understand that most (if not all) applications connecting to Teradata interface using TPA or Trusted Parallel Application. I do not know if there any desktop applications which don't, so here goes a summary of the problem and a probable solution.

You are able to ping the Teradata node. When you try to login using BTEQ or SQL Assistant, you get an error message. For BTEQ the error message is this:

Warning: DBS CRASHED OR SESSIONS RESET. RECOVERY IN PROGRESS

For SQL Assistant, its WSA E HOST UNREACH (see the earlier blog for screenshot)

The simple solution might be start the TPA daemon on the server.

/etc/init.d/tpa start

Here's the full text:

(none):~ # /etc/init.d/tpa stop
PDE stopped for TPA shutdown
(none):~ # netstat -an | grep 1025
(none):~ # bteq

 Teradata BTEQ 12.00.00.00 for LINUX.
 Copyright 1984-2007, NCR Corporation. ALL RIGHTS RESERVED.
 Enter your logon or BTEQ command:
.logon suse/partha

.logon suse/partha
Password:

 *** Warning: DBS CRASHED OR SESSIONS RESET.  RECOVERY IN PROGRESS>
 *** Warning: Exiting because of three BREAKs!

 *** Exiting BTEQ...
 *** RC (return code) = 2
(none):~ # netstat -an | grep 1025
(none):~ # /etc/init.d/tpa start
Teradata Database Initiator service is starting...
Teradata Database Initiator service started successfully.
(none):~ # netstat -an | grep 1025
(none):~ # bteq

 Teradata BTEQ 12.00.00.00 for LINUX.
 Copyright 1984-2007, NCR Corporation. ALL RIGHTS RESERVED.
 Enter your logon or BTEQ command:
.logon suse/partha

.logon suse/partha
Password:


 *** Logon successfully completed.
 *** Teradata Database Release is 12.00.03.12
 *** Teradata Database Version is 12.00.03.14
 *** Transaction Semantics are BTET.
 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 9 seconds.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
select current_date;

select current_date;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 2 seconds.

    Date
--------
11/09/07

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
.quit

.quit
 *** You are now logged off from the DBC.
 *** Exiting BTEQ...
 *** RC (return code) = 0
(none):~ # netstat -an | grep 1025
tcp        0      0 0.0.0.0:1025            0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:42528         127.0.0.1:1025          TIME_WAIT
tcp        0      0 127.0.0.1:42527         127.0.0.1:1025          TIME_WAIT
(none):~ # netstat -an | grep 1025
tcp        0      0 0.0.0.0:1025            0.0.0.0:*               LISTEN
(none):~ #

and here's the screenshot:

don't get caught due to a missing cop1

Most Teradata clients expect to find the Teradata servername in a specialized form in the hosts file. This is /etc/hosts in Linux and <OSDRIVE>\Windows\System32\drivers\etc\hosts in Windows. Turns out that some tools like Teradata SQL Assistant can't locate the server even if you put the full IP address in the ODBC connection. 

 

The client shows an error like this:

0: WSA E HostUnreach: The Teradata server can't currently be reached over this network.

The simplest solution is to add an entry in the hosts file (typical location given above) with the Teradata node name followed by cop1 (or cop2 etc). Here's how it should look now:

# comment
# lines

# here

192.168.93.128   suse susecop1

Note that I have added two entries. While suse is actually the name of the remote server, I have appended a cop1 in the next one for Teradata tools to figure out this is a Teradata server!

Thanks to Jim Chapman for explaining this in this post.

If you are not able to logon using BTEQ or Teradata SQL Assistant, there might be a problem with the Trusted Parallel Application (TPA) daemon. More on that in the next entry.

Phir bhi dil hai hindustani Or Some common mistakes people from the subcontinent make

We Indians tend to have a desi version of most things on life - Indian western culture, Indian western English, Indian western attires and so on. As in others, our Contribution to the English language is immense, so much so, that we often invent new words when we strongly the feel that the word should have existed. For the uninitiated, though, this causes confusion. While I stake no claim on expertise on the langauage here are a few commom mistakes:

Developer (to American/British PM): Few activities are pending

What is implied: A few of the activites are pending

What the PM understands: Almost nothing is pending. A few and few are not same and never will be. Few means close to nothing if at all. The problem is there is no equivalent phrase in the Indian languages so to us few and a few sound the same.

Parallely: No such word exists yet. Till such time lets try to 'do things in parallel'.

Prepone: Existence of postpone doesn't automatically mean there is a prepone. One way might be to 'bring things forward'.

Updation: No, updation of a document doesn't mean anything. Say the document/ policy update will happen in a month.

will add to the list as more words come to my mind.

last but the least our fascination for American words just keeps growing, don't freak out when we pop a darn freaking comment just to make it sound awesome. too good, eh?

The right, left outer join!

Faced an interesting scenario today while working on the correctness of a query. What I didn't know was that in an outer join, the SQL statement 

col(+) = val 

is not quite the same as

col = val
or col is NULL.

In the first case, the RDBMS will pick up all values of that column whether they are NULL or NOT NULL, and display it as NULL if the column value does not match val. The number of rows remains unaffected.

In the second case, however, the RDBMS will pick up only those rows which has the column value = val as well as those rows which are NULL (right table NULL rows which don't match due to the left outer join, OR which are just NULL);
Illustration below:

SQL> create table emp
  2  ( emp_id number,
  3  emp_name varchar2(30),
  4  dept_no number);

Table created.

SQL> create table dept
  2  (dept_id number,
  3  dept_name varchar2(30)
  4  );

Table created.


SQL> insert into emp values(100, 'Adam', 10);
1 row created.

SQL> insert into emp values(200, 'Bud', 20);
1 row created.

SQL> insert into emp values(300, 'Carling', 10);
1 row created.

SQL> insert into emp values(400,'Don', NULL);
1 row created.

SQL> insert into emp values(500, 'Emma',40);
1 row created.

SQL> insert into dept values(10, 'Accounts');
1 row created.

SQL> insert into dept values(20, 'Sales');
1 row created.

SQL> insert into dept values(30, 'IT');
1 row created.

SQL> commit;
Commit complete.

SQL> column emp_name format a8;


SQL> select *
  2  from emp,
  3  dept
  4  where emp.dept_no = dept.dept_no;
where emp.dept_no = dept.dept_id;

    EMP_ID EMP_NAME    DEPT_NO    DEPT_ID DEPT_NAME
---------- -------- ---------- ---------- ------------------------------
       100 Adam             10         10 Accounts
       200 Bud              20         20 Sales
       300 Carling          10         10 Accounts

SQL> select *
  2  from emp,
  3  dept
  4  where emp.dept_no = dept.dept_id(+)
  5  order by emp_id;

    EMP_ID EMP_NAME    DEPT_NO    DEPT_ID DEPT_NAME
---------- -------- ---------- ---------- ------------------------------
       100 Adam             10         10 Accounts
       200 Bud              20         20 Sales
       300 Carling          10         10 Accounts
       400 Don
       500 Emma             40

SQL> select *
  2  from emp,
  3  dept
  4  where emp.dept_no = dept.dept_id(+)
  5  and dept.dept_name (+) = 'Sales'
  6  order by 1;

    EMP_ID EMP_NAME    DEPT_NO    DEPT_ID DEPT_NAME
---------- -------- ---------- ---------- ------------------------------
       100 Adam             10
       200 Bud              20         20 Sales
       300 Carling          10
       400 Don
       500 Emma             40

Note that rows which have dept_name = 'Accounts' are also picked up, but the column value is displayed as NULL since it does not match 'Sales'.
SQL> select *
  2  from emp,
  3  dept
  4  where emp.dept_no = dept.dept_id(+)
  5  and (dept.dept_name = 'Sales'
  6       or
  7       dept.dept_name is null)
  8  order by emp_id;

    EMP_ID EMP_NAME    DEPT_NO    DEPT_ID DEPT_NAME
---------- -------- ---------- ---------- ------------------------------
       200 Bud              20         20 Sales
       400 Don
       500 Emma             40

This is what I was expecting - to filter out non-Sales rows if present.

The same example with ANSI join syntax:

SQL> select *
  2  from emp
  3  left outer join dept
  4  on emp.dept_no = dept.dept_id;

    EMP_ID EMP_NAME    DEPT_NO    DEPT_ID DEPT_NAME
---------- -------- ---------- ---------- ------------------------------
       300 Carling          10         10 Accounts
       100 Adam             10         10 Accounts
       200 Bud              20         20 Sales
       400 Don
       500 Emma             40

SQL> select *
  2  from emp
  3  left outer join dept
  4  on emp.dept_no = dept.dept_id
  5  where dept.dept_name = 'Sales'
  6  order by emp_id;

    EMP_ID EMP_NAME    DEPT_NO    DEPT_ID DEPT_NAME
---------- -------- ---------- ---------- ------------------------------
       200 Bud              20         20 Sales

SQL> select *
  2  from emp
  3  left outer join dept
  4  on emp.dept_no = dept.dept_id
  5  and dept.dept_name = 'Sales'
  6  order by emp_id;

    EMP_ID EMP_NAME    DEPT_NO    DEPT_ID DEPT_NAME
---------- -------- ---------- ---------- ------------------------------
       100 Adam             10
       200 Bud              20         20 Sales
       300 Carling          10
       400 Don
       500 Emma             40

Note that (+) behaviour in the ANSI join when the filter condition is placed in the join condition rather than in the where clause.

SQL> select *
  2  from emp
  3  left outer join dept
  4  on emp.dept_no = dept.dept_id
  5  where (dept_name = 'Sales'
  6        or
  7        dept_name is NULL)
  8  order by emp_id;

    EMP_ID EMP_NAME    DEPT_NO    DEPT_ID DEPT_NAME
---------- -------- ---------- ---------- ------------------------------
       200 Bud              20         20 Sales
       400 Don
       500 Emma             40