Teradata User-Installed-Files (UIF) and the Database Search Path

Using the User’ STARTUP String

CREATE USER SAMPLE_USER FROM DBC
AS PASSWORD = ********
,PERM=0
STARTUP = 'SET SESSION SEARCHUIFDBPATH = MYPROJ;'
;
MODIFY SAMPLE_USER AS 
STARTUP = ‘SET SESSION SEARCHUIFDBPATH = MYPROJ;’

NOTE:
There are LIMITATIONS to this approach, and they are made clear in the
Teradata Documentation:

“Purpose of the STARTUP String”

Custom Tweak, Use this at Your Own Risk

Question 1: If the UIF/script runs on the node, which directory on the Linux filesystem holds our Script?

find /var/opt/teradata/tdtemp/uiflib
/var/opt/teradata/tdtemp/uiflib
/var/opt/teradata/tdtemp/uiflib/tdbs_1032
/var/opt/teradata/tdtemp/uiflib/tdbs_1032/sha256.py
/var/opt/teradata/tdtemp/uiflib/uifrev.log
/var/opt/teradata/tdtemp/uiflib/scriptlog
#!/usr/bin/pythonimport sys
import hashlib
for rec in sys.stdin:
rec = rec.strip().split("\t")
if rec:
print("\t".join([rec[0],hashlib.sha256(rec[0]).hexdigest()]))

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 SEARCHUIFDBPATH

SET SESSION SEARCHUIFDBPATH = MYPROJ
;
SEL
SCR_OPER.RAW_EMAIL
,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
;
SCRIPT_COMMAND('./MYPROJ/sha256.py')
SCRIPT_COMMAND('/var/opt/teradata/tdtemp/uiflib/tdbs_1032/sha256.py')
SEL 
SCR_OPER.RAW_EMAIL
,SCR_OPER.HASHED_EMAIL
FROM 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
;
SET SESSION SEARCHUIFDBPATH = MYPROJ;

Question 3: Ok, this works, BUT, how on earth was the tdbs_1032 subdirectory derived in the absolute path to the UIF, i.e
/var/opt/teradata/tdtemp/uiflib/tdbs_1032

SELECT UIF.DATABASEID,
DB.DATABASENAMEI,
UIF.UIFFileName
FROM DBC.UIF_INFO UIF
INNER JOIN DBC.DBASE DB
ON UIF.DatabaseId = DB.DatabaseId
WHERE DATABASENAME='MYPROJ'
AND UIFFILENAME='sha256.py'
;
DatabaseId  DatabaseNameI UIFFileName
00–00–08–04 MYPROJ sha256.py
DatabaseId BYTE(4) NOT NULL
A    B    C    D
00 00 08 04
B    A    D    C
00 00 04 08
/var/opt/teradata/tdtemp/uiflib/tdbs_1032                         
sel 
DATABASENAME
,UIFFileName
--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
,'/var/opt/teradata/tdtemp/uiflib/'||'tdbs_'
||trim(MID_ENDIAN_INT)||'/'
||trim(UIFFileName)
AS UIF_Absol_PATH

FROM DBC.DBASE DB
INNER JOIN DBC.UIF_Info UIF
ON DB.DatabaseId=UIF.DatabaseId
WHERE DATABASENAMEI='MYPROJ'
and UIFFileName='sha256.py'

Take-Home Points

--

--

Learner. Interests include Cloud and Devops technologies.

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store