2018 -- Two compelling images

As we prepare to step into the new year, I am reminded of two of the most compelling images from 2018. Entirely by coincidence, these were both taken on the same day.

A person picking up the garbage

About the person: The person happens to be my (and several other's) first lead/mentor. As it happens, he is now a top shot in a leading tech company at a level so high many would not reach in our lifetimes (I know I wont). A list of his inventions can be found here. I make it a point to visit him and his family every time I visit Bay area, to ensure I don't forget how to think.

About the image: This was taken at a Durga puja venue, where hundreds of people visit daily and eat out. Such venues can quickly become messy if the garbage is not picked up regularly. Realizing the cleaning crew were caught up elsewhere, he decided to take matters into his own hands (literally) and started cleaning out the overflowing garbage. No one told him to, or expect him to, except his sense of duty and humility. Those that have had the honor to have worked alongside him would know this is his normal style, not the exception. My takeaway: When you're truly great, you don't hesitate to bend over and get stuff done. Think you're great? Be humble!

Modern women facing each other

About the image: An elderly couple is seen silently offering prayers in front of the Durga idol. One couldn't help but notice an Acura car key/fob attached to her saree anchal. My guess is she was doing the driving that day. She didn't appear self conscious or inconvenienced by her attire; rather she carried herself with dignity and grace that commanded attention and respect. My takeaway: When you're really smart, any attire works.

Looking forward to many eye openers learnings in 2019!

Generating a sequential Surrogate Key in Teradata

This has to the commonest problem of all in the Data Warehousing World. And people suggest different ways to tackle this based on their comfort level. Here I show an approach to create a sequentially increasing surrogate key without the use of variables or CSUM.

CSUM is a deprecated feature and Teradata strongly discourages its use. This can be found in page 339 of the Teradata manual here

/*Create Source Table*/

create table src_tbl

(pk int,

dta varchar(10)

);

/*Insert rows in source */

insert into src_tbl(101, 'Anne');

insert into src_tbl(102, 'Betty');

insert into src_tbl(103, 'Cherrie');

/*Create Target Table*/

create table tgt_tbl

(sk int,

dta varchar(10)

);

/*Insert a row to simulate some data already present in target*/

insert into tgt_tbl (1, 'Anne');

/*Check Values are inserted */

select * from tgt_tbl;

select * from src_tbl;

/*Generate new values for source rows that are to be inserted */

sel (sel max(sk) from tgt_tbl) + row_number() over(order by src.dta) tgt_sk --<--- Surrogate Key Generated

, src.dta

from src_tbl src

left join tgt_tbl tgt

on src.dta = tgt.dta

where tgt.dta is null;

The Result is shown below

tgt_sk	dta
2	Betty
3	Cherrie

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.