Checking TD Version from UNIX

How would you check what version of teradata is being used in your UNIX env?

You simply have to create a bteq script:

bteq<<EOF

.logon databasename/username,pwd;

.show version

.QUIT

EOF

And run the script.

Or, you can do the same on commandline.

Significance of key phrases

What do you need to do if you what some constraints applied at view level?

I’m pretty sure you know column level constraints (SELECT clause J).

But when you give row level constraints in WHERE clause, do make sure you give the following key phrase:

“WITH CHECK OPTION”

Otherwise, your where clause will be ignored.

On the same note, while creating volatile tables, the key phrase for you to hold the data in until end of session is:

“WITH DATA” and “ON COMMIT PRESERVE ROWS”

Refraining from doing so, will make the data you just inserted disappear!!

Escaping special character '%'

You must be aware of the fact that we use '%' to find a pattern in a string:
Eg: WHERE STRING LIKE '%ABC%'
What would you do if you need to search a string that has '%' in it? In other words, how will you escape the special character '%' while searching?

Here is the syntax:
WHERE STRING LIKE  ('%@%%') ESCAPE '@'

Direct Data load (Flat file to Table)

Direct loading of tables from flat file in SQL Assistant(queryman):

This is quite simple process. But I'm writing it down for the newbees :)
Step1: Create the table in database (it can also be an existing table, with data already in it)
Step2: The sequence of columns in table and flat file should match
Step3: Delimiter used in flat file is usually pipe "|"
Step4: Set queryman to run in Import mode (File -> Import Data)
Step5: Execute sql:
        INSERT INTO DBNAME.TBLNAME
        VALUES(?,?,?,?,?);
According to the number of columns in the table/file, the number of parameters (?) change.
Step6: The "Import file" dialog box will open. Choose the flat file that contains data to be loaded.
Step7: You are done! :-p

Note: Try not to use this method for large number of records. It will consume a lot of time and resources.
I suggest fastload/Multiload for such situations

Skewness

How will you determine which column will be appropriate for Index, that can avoid skewness?

I have given below the syntax. Before assigning a column as Index, make sure you check data in that column is not skewed.

SELECT
HASHAMP(HASHBUCKET(HASHROW(col whose amp distribution that needs to be checked; eg: PI)))
,CAST(COUNT(*) AS DECIMAL(18,0))
FROM
DBNAME.TBLNAME
GROUP BY 1
ORDER BY 1
;

PS: This alone cannot let you determine PI. As, you may sometimes have to decide based on how frequently it is used for Joining purposes etc.

Usage of interval in date time formatting

Sometimes, meddling with datatypes and formats can be frustrating. Especially when there are multiple ways to do one task, but still trying to do in an elegant way.

My client wanted the timestamp column without minutes and seconds.
Eg: '07/25/2008 11:45:23' as '07/25/2008 11:00:00'

Unfortunately, forums and documentation did not help much :(
Many thanks to DBA Girija who helped me get it the 'elegant' way :)

Crude way:
type casting to character, concatenating and converting back to timestamp:

SELECT current_timestamp(0)
, CAST((CAST(CURRENT_TIMESTAMP(0) AS CHAR(10))
||' '||CAST(EXTRACT(HOUR FROM TIME) AS CHAR(2))
||':00:00') AS TIMESTAMP(0));

Elegant way:
usage of interval function and adding it to date. Usage of interval function for this purpose was a bit tricky. Hope this posts helps people facing similar problems

SELECT
current_timestamp(0)
,CAST(current_date AS TIMESTAMP(0)) 
+ CAST(EXTRACT(HOUR FROM current_timestamp)
AS INTERVAL HOUR(2));

Surrogate Key: Part 2

Well, there is another way to do it…
After computing MAX of the column value and use ROW_NUMBER() function. Please note that you can use CSUM(1,1) as well in the same logic.

INSERT INTO DATABASENAME.TGT_TABLENAME
SELECT
MAX_ID + ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY 1) as COL_ID --Partition by is optional. If your values won't repeat, you don't have to use it.

,COLUMN2
,USER
,CURRENT_DATE
FROM
DATABASEANAME.WRK_TABLENAME wrk,
(select max(COL_ID) max_id  from DATABASENAME.TGT_TABLENAME ) max_val
WHERE NOT EXISTS(
SELECT '' FROM
DATABASENAME.TGT_TABLENAME  lkp
WHERE
LKP.COLUMN2= WRK.COLUMN2
);

~Meena

Surrogate key generation

Putting together a few steps to generate surrogate key:

Most of you might've dealt with it already. But, sending it as it might be a quick reference incase of future use
Generating it as such isn’t a big deal, it might get a little tricky when you are trying to insert new values in continuation of already existing surrogate key.

In the Bteq script, you may use a logic that exports current value of the surrogate key column into a data file:
.EXPORT DATA FILE <$DIR/filename>

SELECT MAX(SURR_KEY_COLUMN) (TITLE '')
FROM TGTDB.TGTTBL;

.EXPORT RESET

/****Read in MAXID variable*****/
.IMPORT DATA FILE <$DIR/filename>
USING (MAXID INT)

The above steps will populate MAXID with maximum value of surrogate key.
Now, this variable can be used along with CSUM function to start from where we stopped :)

INSERT INTO TGTDB.TGTTBL
(SURR_KEY_COLUMN
,COLUMN2
)
SELECT
:MAXID + CSUM(1,1)
,T1.COLUMN2
FROM
STGDB.STGTABLE T1
LEFT OUTER JOIN TGTDB.TGTTBL T2
ON T1.COLUMN2 = T2.COLUMN2
WHERE
T2.COLUMN2 IS NULL
;

The above is a typical example in loading target table(such as a lookup table) from staging table; with surrogate keys for the new values of COLUMN2

~Meena

Teradata tip of the day 05/19

A simple tip for today:

Not sure if the stats have been collected well? Is your query performing badly inspite of tuning?
Run the command below and then run EXPLAIN plan on that query:
DIAGNOSTIC HELPSTATS ON FOR SESSION;

You will see suggestions from the optimizer on what columns stats need to be collected, at the end of EXPLAIN plan. See an example below:

     BEGIN RECOMMENDED STATS ->
  8) "COLLECT STATISTICS working.DW_KWDM_PRTNR_MPX_REV_W INDEX (KW ,ROTATION_ID ,PRTNR_CREATIVE_ID ,PRTNR_PRDCT_ID ,FTR_ITEM_ID ,CLICK_DT)".  (HighConf)

  9) "COLLECT STATISTICS working.DW_KWDM_PRTNR_MPX_REV_W COLUMN (TRANS_DT ,CNTRY_ID)".  (HighConf)
 10) "COLLECT STATISTICS working.DW_KWDM_PRTNR_MPX_REV_W COLUMN (CONTENT_MATCH_YN_ID ,TRANS_DT ,CNTRY_ID)".  (HighConf)
     <- END RECOMMENDED STATS

Similarly,

To get a more detailed explain plan, run the command below before executing EXPLAIN:
DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;

~Meena

Teradata tip of the day 05/18

Today's find:
Doing modulus to split digits: This is a simple query, worked well in 'SELECT' clause. But gave out error when Volatile table was created out of it:

A sample of SELECT that ran fine:
SELECT
6789 a
,MOD((a/1000),10) (INT) AS BIT_POS1
, MOD((a/100),10) (INT) AS BIT_POS2
, MOD((a/10),10) (INT) AS BIT_POS3
, MOD(a,10) (INT) AS BIT_POS4;

Volatile table that gave out error:
CREATE VOLATILE TABLE DIGIT_VT AS
(
SELECT
6789 a
,MOD((a/1000),10) (INT) AS BIT_POS1
, MOD((a/100),10) (INT) AS BIT_POS2
, MOD((a/10),10) (INT) AS BIT_POS3
, MOD(a,10) (INT) AS BIT_POS4;
) WITH DATA
PRIMARY INDEX(a)
ON COMMIT PRESERVE ROWS;

Modified format:
CREATE VOLATILE TABLE DIGIT_VT AS
(
SELECT
6789 a
,((a/1000) MOD 10) (INT) AS BIT_POS1
,((a/100) MOD 10) (INT) AS BIT_POS2
,((a/10) MOD 10) (INT) AS BIT_POS3
,(a MOD 10) (INT) AS BIT_POS4
) WITH DATA
PRIMARY INDEX(a)
ON COMMIT PRESERVE ROWS;

Note: Method1 didn’t work when selected from derived table/subquery as well :(

~Meena

Teradata tip of the day

Hi Friends,

I thought of sharing some of the nuances I learn in teradata as a part of my project with you.
Hope this initiative helps :)

While exploring on how to convert hexadecimal value to interger and viceversa, my team was not able to find a direct function on Teradata.

This is the alternative we used (a hack :)) -

SELECT
  1234567890 AS x

 ,SUBSTRING('0123456789ABCDEF' FROM x / (16**7) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**6) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**5) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**4) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**3) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**2) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**1) MOD 16 + 1 FOR 1) ||
  SUBSTRING('0123456789ABCDEF' FROM x / (16**0) MOD 16 + 1 FOR 1) AS Int2HexString

 ,Int2HexString as s

 ,POSITION(SUBSTRING(s FROM 8 FOR 1) IN '123456789ABCDEF') * ((16**0) (INT)) +
  POSITION(SUBSTRING(s FROM 7 FOR 1) IN '123456789ABCDEF') * ((16**1) (INT)) +
  POSITION(SUBSTRING(s FROM 6 FOR 1) IN '123456789ABCDEF') * ((16**2) (INT)) +
  POSITION(SUBSTRING(s FROM 5 FOR 1) IN '123456789ABCDEF') * ((16**3) (INT)) +
  POSITION(SUBSTRING(s FROM 4 FOR 1) IN '123456789ABCDEF') * ((16**4) (INT)) +
  POSITION(SUBSTRING(s FROM 3 FOR 1) IN '123456789ABCDEF') * ((16**5) (INT)) +
  POSITION(SUBSTRING(s FROM 2 FOR 1) IN '123456789ABCDEF') * ((16**6) (INT)) +
  POSITION(SUBSTRING(s FROM 1 FOR 1) IN '123456789ABCDEF') * ((16**7) (INT)) AS HexString2Int
;

Try it out. Also, if you guys know any direct method, let me know.

I keep this initative to a closed group of friends working in DW, so we can share each other stuff we know and broaden our knowledge on TD :)

This chain can be used for learning any new technical info.

~Meena