Teradata check current user

To check currently login as user :

sel user

閱讀內容...

隱藏內容...

SQL Performance tuning in Teradata

為DB進行collect statistics時,除了根據過往的經驗外,在Teradata中其實是可以讓個engine 給點提議的

要讓engine 為你提供要collect的選擇,首先需要執行以下statement

DIAGNOSTIC HELPSTATS ON FOR SESSION;

之後再EXPLAIN 一下要行的sql

EXPLAIN SEL * FROM tableA a INNER JOIN tableB b ON a.ref_cd = b.ref_cd 
LEFT OUTER JOIN tableC c ON a.serv_cd = c.serv_cd
LEFT OUTER JOIN tableD d ON a.row_id = d.row_id AND CAST('20141101' AS DATE FORMAT 'yyyymmdd') BETWEEN d.start_date AND d.end_date
WHERE event_date = CAST('20141101' AS DATE FORMAT 'yyyymmdd')

在explain 完個execution plan 後的地方就會出現到Engine 為你提議要collect statistics 的地方

 10) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 1 minute and 12 seconds. 
     BEGIN RECOMMENDED STATS ->
 11) "COLLECT STATISTICS tableA COLUMN EVENT_DATE". 
     (HighConf)
 12) "COLLECT STATISTICS tableA COLUMN ROW_ID". 
     (HighConf)
 13) "COLLECT STATISTICS tableA COLUMN SERV_CD". 
     (HighConf)
 14) "COLLECT STATISTICS tableA COLUMN REF_CD". 
     (HighConf)
     <-END RECOMMENDED STATS

根據Engine 的提議執行完COLLECT STATISTICS後,再EXPLAIN個plan一次,就會發現預計的時間快了不少呢!

10) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 21.37 seconds.

閱讀內容...

隱藏內容...

Remove leading zero in teradata

在TD中要移除leading zero 原來是幾簡單的

TRIM(LEADING '0' FROM Fields)

閱讀內容...

隱藏內容...

unprintable character at files

最近,因為公司的收購行動下,要和一些新的部門合作,但真的不明白為何它們的檔案中總是有一些unprintable character,這些character最大的問題就係它導致Teradata的mload不能正常地運作=.=!,因為是檔案是用上delimiter的關係,這些怪獸導致TD不能正確地解讀一行data,當然最簡單的方法是叫他們處理掉那些怪獸

不過在等他們處理時,又不妨自己找Google大神問一下路,最後總算找到有用的方法

下面的command能在Unix的環境下把一些非ASCII的東西移除,而且我在cygwin下運作時,一個六百多MB的檔案也很快就cleansing 完呢!所以都算幾有用

 tr -cd '\11\12\15\40-\176' < err-file > clean-file

http://alvinalexander.com/blog/post/linux-unix/how-remove-non-printable-ascii-characters-file-unix

閱讀內容...

隱藏內容...

teradata escape special character in like statement

由於_ 及 % 在like statement 中是有特別的作用, 所以如果要在尋覓中指定它們的出現,我地是需要把放一個escape char 在它們之前的,而在TD入面這一個escape char 是可以自行定義的, 方法如下.

WHERE STRING LIKE '%#_%' ESCAPE '#'

其中'#'就是定義的escape char

閱讀內容...

隱藏內容...

Release mload in teradata

有時當teradata fail 了一些load job,佢就會lock左你的table而不會自動release,當然你可以等,等teradata自己把這個lock了的table release出來,但是這樣被動的等根本不是一個辨法,這時你就可以用到release mload 這指令

release mload xxxx

不過有時,這指令是會出現以下錯誤的
2572: MLoad table xxxx can not be released

係呢個時侯就可以加上in apply 強行把這個table release出來

release mload xxxx in apply

閱讀內容...

隱藏內容...

teradata error code

Return Code = 04 ( BTEQ returns a return code of 04 for the following Teradata error codes )
2580 - Mload not active on table %TVMID.
2667 - Statistics cannot be collected on an empty table.
3534 - Index already exists.
3666 - This view has too many columns to store or retrieve comments.
3737 - Name is longer than 30 characters.
3747- No start-up string defined for this user.
3803 - Table “%VSTR” already exists.
3804 - View “%VSTR” already exists.
3805 - Macro “%VSTR” already exists.

Return Code = 08 ( BTEQ returns a return code of 04 for the following Teradata error codes )

CLI0530 -Character set name or code unknown.
2123- A segment could not be read successfully.
2538 -A disk read error occurred in the tables area.
2541- End of hash code range reached.
2632- All AMPs own sessions for this Fast/MultiLoad
2639 - Too many simultaneous transactions.
2641 %DBID.%TVMID was restructured. Resubmit.
2644 No more room in database %DBID.
2654 Operation not allowed: %DBID.%TVMID is being restored.
2805 Maximum row length exceeded in %TVMID.
2809 Invalid recovery sequence detected.
2815 Apparent invalid restart of a restore.
2818 Invalid lock to dump table without after image journaling.
2825 No record of the last request was found after Teradata Database restart.
2826 Request completed but all output was lost due to Teradata Database restart.
2827 Request was aborted by user or due to statement error.
2828 Request was rolled back during system recovery.
2830 Unique secondary index must be dropped before restoring table.
2835 A unique index has been invalidated. Resubmit request.
2837 Table being fast loaded; no data dumped.
2838 Table is unhashed; no data dumped.
2840 Data rows discarded due to inconsistent hash codes.
2843 No more room in data base.
2866 Table was recovery aborted; no data dumped.
2868 This permanent journal table is damaged; no data dumped.
2920 Delete journal and AMP down without dual.
2921 No saved subtable for journal %DBID.%TVMID.
2926 No more room in %DBID.%TVMID.
3001 Session is already logged on.
3111 The dispatcher has timed out the transaction.
3116 Response buffer size is insufficient to hold one record.
3119 Continue request submitted but no response to return.
3120 The request is aborted because of a Teradata Database recovery.
3523 %FSTR does not have %VSTR access to %DBID.%TVMID.
3524 %FSTR does not have %VSTR access to data base %DBID.
3566 Data base does not have a PERMANENT journal.
3596 RESTORE Teradata Database invalid if table, view or macro exists outside of
Teradata Database.
3598 Concurrent change conflict on data base; try again.
3603 Concurrent change conflict on table; try again.
3613 Dump/restore, no hashed nonfallback tables found.
3656 Journal table specified no longer exists.
3658 ROLLBACK/ROLLFORWARD table specifications are invalid.
3705 Teradata SQL request is longer than the Simulator maximum.
3802 Database “%VSTR” does not exist.
3807 Table/view “%VSTR” does not exist.
3824 Macro “%VSTR” does not exist.
3873 “%VSTR” is not a journal table.
3877 NO FALLBACK specified and the table is FALLBACK.
3897 Request aborted due to Teradata Database restart. Resubmit.
3916 Requested information not in dictionary.
5495 Stored Procedure %VSTR does not exist.

Return Code = 12 ( BTEQ returns a return code of 04 for the following Teradata error codes )
CLI0001 Parameter list invalid or missing.
CLI0002 Invalid number of parameters received.
CLI0003 Error validating HSIRCB.
CLI0004 Error validating HSICB.
CLI0005 Error validating HSISPB.
CLI0006 Invalid destination HSICB detected.
CLI0007 Invalid destination RCB detected.
CLI0008 DBCFRC unable to free RCB/HSICB control blocks because they are not
contiguous in storage.
CLI0009 Invalid DBCAREA pointer or id.
CLI0010 ECB already waiting.
2971 The AMP lock table has overflowed.
2972 No table header exists for table.

http://teradatadevelopers.blogspot.com/

閱讀內容...

隱藏內容...