Monthly Archives: May 2014

Running scripts in teradata

Let us assume, we have a data warehouse in teradata. Let us suppose that this data warehouse is loaded with a set of scripts. In principle it could be done. The question then arises how the scripts are run. An example could clarify this. The example looks like:

.logon oTDD001.s2.ms.****.com/TOM.VAN-MAANEN, pau26688
.export report file=C:\Users\TOM.VAN-MAANEN\phi.txt
.set retlimit 20
SELECT	'"'||trim(Ident)||'";"'|| trim(Serial)||'";"'|| trim(Node)||'"' FROM	SAN_D_FAAPOC_01.TestUnicode;
.export reset;
.quit

The first line of this script creates a connection to a Teradata machine. The second line allocates a file that will be used to write data to. The fourth line is standard example where records from a table are exported. The last lines finishes the export and finishes the export from a table.
This scripts is called by a piped command where the script is sent to the bteq:

C:\Users\TOM.VAN-MAANEN>bteq < bteq.txt
BTEQ 13.10.00.01 Thu May 22 11:28:08 2014

+---------+---------+---------+---------+---------+---------+---------+----
.logon oTDD001.s2.ms.unilever.com/TOM.VAN-MAANEN,

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

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
.export report file=C:\Users\TOM.VAN-MAANEN\phi.txt
 *** To reset export, type .EXPORT RESET
+---------+---------+---------+---------+---------+---------+---------+----
.set retlimit 20
+---------+---------+---------+---------+---------+---------+---------+----
insert into SAN_D_FAAPOC_01.TestUnicode(ident,node,serial)
        values('Test├╣─ÖSummary','Test├╣─ÖSummary',' 19');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
SELECT '"'||trim(Ident)||'";"'|| trim(Serial)||'";"'|| trim(Node)||'"'
FROM SAN_D_FAAPOC_01.TestUnicode;

 *** Query completed. 16 rows found. One column returned.
 *** Total elapsed time was 1 second.


+---------+---------+---------+---------+---------+---------+---------+----
.export reset;
 *** Output returned to console.
+---------+---------+---------+---------+---------+---------+---------+----
.quit
 *** You are now logged off from the DBC.
 *** Exiting BTEQ...
 *** RC (return code) = 0

C:\Users\TOM.VAN-MAANEN>

PIN code

A few days ago, I got the suggestion to protect a transaction with a PIN number. But how does that mechanism work? It looks as if this mechanism is used to describe several situations. One situation (the simplest) looks a bit like a password. On the client side, a user enters a PIN code. This PIN code is translated into another value that is sent to the server. For the moment, one may think of a multiplication of the PIN code by the daynumber. This product is sent to the server. The server undertakes a similar process. It multiplies a stored PIN code with the daynumber. If this result equals the number that is received, the entered PIN code is assumed to be entered by the authorised user. After such verification the user may proceed to the required transaction.
Whenever the network traffic is monitored, the monitor only reveals the calculated code. Suppose the PINcode is multiplied by the daynumber, someone sees a number like 337518. If someone monitors the network with the idea to hack the protected site, such information is worthless. The hacker doesn’t know the algoritm (multiplicating the pincode by the daynumber), he can’t know what PIN code to file out. He might try to use the number 337518 as an attempted PINcode but his attempt will be rejected. From the traffic, he doesn’t get the PINcode that he needs to use.