Teradata User-Installed-Files (UIF) and the Database Search Path
In a previous article, I gave an overview of UIF features and the process required to setup, install and test a basic python UIF (
MYPROJ.sha256) for generating a sha256 hash.
This article aims to expand on the initial learnings to cover off on:
- Methods of invoking UIFs without having to specifically declare the UIF database search path for each session, i.e.
SET SESSION SEARCHUIFDBPATH = <databasename>;
Using the User’ STARTUP String
When a Teradata Database user is created, we have an option of specifying a
STARTUP string, which can include statements to be executed each time the user invokes a session.
The following “Create User” statement for creating userid
SAMPLE_USER includes a
STARTUP parameter, which sets the UIF database search path for each session login.
CREATE USER SAMPLE_USER FROM DBC
AS PASSWORD = ********
STARTUP = 'SET SESSION SEARCHUIFDBPATH = MYPROJ;'
SAMPLE_USER can also opt to modify this parameter for themselves using:
MODIFY SAMPLE_USER AS
STARTUP = ‘SET SESSION SEARCHUIFDBPATH = MYPROJ;’
There are LIMITATIONS to this approach, and they are made clear in the Teradata Documentation:
“Purpose of the STARTUP String”
“The length of a startup string is limited to 255 characters…….”
“The startup string is processed when:
- You log on to Teradata Database through
- You establish a
JDBCconnection using the
Teradata JDBC Driverand have specified the connection parameter
Teradata JDBC Driver, and
CLIv2support startup strings.”
Custom Tweak, Use this at Your Own Risk
This option that I’m about to describe has not been documented (as far as I’m aware). The analysis was based on a “hunches” and curiosity.
I’ll explain the thought process that led to conclusion/result using the UIF script (
MYPROJ.sha256) that was created in my previous article. By all means, if you are reading this, and are able to provide more insight, feel free to do so by leaving a note.
Question 1: If the UIF/script runs on the node, which directory on the Linux filesystem holds our Script?
The Teradata documentation tells us that in order to troubleshoot a UIF script execution, we can examine the script log file on the node/s, located at
/var/opt/teradata/tdtemp/uiflib, and that this directory also holds our UIFs.
Let’s logon to the Node and find out some details about the child objects of this path.
From a Terminal, we run run the following to get a recursive list of all files and sub-directories:
So, from the above, directory
/var/opt/teradata/tdtemp/uiflib/ is the parent for UIF related objects. If we take a closer look, we see that the sha256 UIF has been stored as
Let’s make sure that this file is the sha256 python code we installed onto the database as a UIF by listing its contents via
import hashlibfor rec in sys.stdin:
rec = rec.strip().split("\t")
This is definitely the code for the sha256 UIF we installed onto the database. A
show file will confirm this:
So, the answer to the subject question, our UIF has been stored on the node at:
Question 2: Now that we know the absolute path of our UIF Script on the Node, can we use this to forgo having to set the
What we’re trying to prove is that if we run the UIF from
sql assistant and use the absolute path instead of the relative path, then our UIF should execute without having to issue a
SET SESSION SEARCHUIFDBPATH = <databasename>;
Let’s test this theory.
The existing code that we used in the previous article to run our UIF is as follows:
SET SESSION SEARCHUIFDBPATH = MYPROJ
,SCR_OPER.HASHED_EMAIL FROM SCRIPT (ON (sel email_addr FROM myproj.emails)
SCRIPT_COMMAND('./MYPROJ/sha256.py') RETURNS ('RAW_EMAIL VARCHAR(55)' ,' HASHED_EMAIL VARCHAR(100)')
) AS SCR_OPER
We need to change the relative path in this code from:
So, our new code becomes:
,SCR_OPER.HASHED_EMAILFROM SCRIPT (ON (sel email_addr FROM myproj.emails)
SCRIPT_COMMAND('/var/opt/teradata/tdtemp/uiflib/tdbs_1032/sha256.py')RETURNS ('RAW_EMAIL VARCHAR(55)' ,' HASHED_EMAIL VARCHAR(100)')
) AS SCR_OPER
Now we can try running this code WITHOUT issuing a:
SET SESSION SEARCHUIFDBPATH = MYPROJ;
Log off any active sessions and log back in again. Now run the new code and check the output:
Wow! It runs successfully without the need to set the
Question 3: Ok, this works, BUT, how on earth was the
tdbs_1032subdirectory derived in the absolute path to the UIF, i.e
I did try digging around in the doco to find some info on how this subdirectory is created but ran out of luck.
So, I took a “brute force” style thinking approach.
I started out by assuming that
tdbs_1032 maps in some way to the database where the UIF was installed to (
tdbs_ appears to be a naming convention, most likely derived from the term “Teradata Database System”.
1032 is cryptic, but, my theory is that this number has been generated using the database dictionary tables field/fields. I’m going to work through the assumption that the
1032 is derived in some way from
dbc.dbase.databaseid for the database where the UIF has been installed (
By running the following query:
FROM DBC.UIF_INFO UIF
INNER JOIN DBC.DBASE DB
ON UIF.DatabaseId = DB.DatabaseId
we get the following output:
DatabaseId DatabaseNameI UIFFileName
00–00–08–04 MYPROJ sha256.py
By looking at the column definition in the DDL for
dbc.dbase.databaseid, we can see that it’s defined as:
DatabaseId BYTE(4) NOT NULL
So, the value of
databaseid from the previous query is
00–00–08–04, which is BYTE data represented in HEX, i.e.
I need a quick way of converting this HEX value to decimal/integer using different “
Head over to https://www.scadacore.com/tools/programming-calculators/online-hex-converter/ and paste in the HEX value of our
00–00–08–04 and click on “
You should see the following output:
By looking at the conversion above, it’s becoming a bit more obvious.
DatabaseIdis being converted to a 32 bit integer, BUT, before this happens, the byte orders have changed.
2052 value highlighted in the two leftmost boxes was derived using Big-Endian format of the
ABDC refers to the order of the bytes.
DatabaseIdin Big-Endian is represented as (hex).
A B C D
00 00 08 04
This converts to integer value of
DatabaseIdbytes are treated as having Mid-Endian Byte order, then we have:
B A D C
00 00 04 08
This hex value converts to integer value of
1032, which is the same value as seen in the directory path to the UIF script, i.e.
Key UIF Details
By running the query below, we can obtain the key details for our UIF (
--Convert Hex Bytes to Chars and LEFT PAD with 00xb
,CAST(LPAD(FROM_BYTES('00'xb||UIF.databaseid, 'BASE16') ,8,'0') AS CHAR(8)) as DatabaseID_Bytes2HexChar--Extract each bytes so we can re-order them
,SUBSTR(DatabaseID_Bytes2HexChar, 1 for 2) as A
,SUBSTR(DatabaseID_Bytes2HexChar, 3 for 2) as B
,SUBSTR(DatabaseID_Bytes2HexChar, 5 for 2) as C
,SUBSTR(DatabaseID_Bytes2HexChar, 7 for 2) as D--Big Endian Byte order
,(A||B||C||D) AS BIG_ENDIAN_HEX_CHARS--Order Bytes to Mid-Endian format
,(B||A||D||C) AS MID_ENDIAN_HEX_CHARS--Convert the Hex Mid-Endian representation Integer
,TO_NUMBER(MID_ENDIAN_HEX_CHARS, 'xxxxxxxx' ) AS MID_ENDIAN_INT--Our UIF Absolute Path
FROM DBC.DBASE DB
INNER JOIN DBC.UIF_Info UIF
Below are the key take-home points re the
If you are using
BTEQ, CLIv2 or Teradata' JDBC Driver, then:
- You modify, or set the User’s
STARTUPat creation, to include a statement for setting the database UIF search path
- For JDBC connections, in addition to setting the user’
STARTUPstring, you will also need to set the driver option
If you are using
ODBC/.Net or any other other connection type including the ones listed above, you can use the “
Custom Tweak” described in this article.
At a high level, the “Custom Tweak” method uses the absolute path to the UIF, which is derived as follows:
- Edit the SQL mentioned in “Key UIF Details” to filter for the required UIF in question
- The query will return several columns with one of them being
- Use the value returned for
UIF_Absol_PATHin the UIF’s
SCRIPT_COMMANDto forgo having to set the