;*********************************************************************** ;Licensed Materials - Property of IBM ; ;BI and PM: UDA ; ;(C) Copyright IBM Corp. 2005, 2020 ; ;U.S. Government Users Restricted Rights - Use, duplication, or disclosure ;by GSA ADP Schedule Contract with IBM Corp. ; ;Unless specifically authorized by IBM, you may not modify any part of this ;file. Where modification is authorized, you must reproduce any copyright ;notices contained in this file and specifically identify which ;modifications have been made by your organization. YOU ARE SOLELY ;RESPONSIBLE FOR DETERMINING THE APPROPRIATENESS OF ANY MODIFICATIONS TO ;THIS FILE AND ASSUME ALL RISKS ASSOCIATED WITH THE USE AND DISTRIBUTION ;OF THE MODIFIED FILE. IBM will not provide support relating to ;unauthorized changes you make to this file. ;*********************************************************************** ; $Header: //uda/main/prod/dmd/sgi/odbc35/teradata/cogdmod_td.ini#14 $ ; ; Module: ; cogdmod_td.ini ; ; Purpose: ; This module contains the Teradata SQL information. ; ; Notes: ; Do NOT modify this file. Doing so could result in unknown behavior ; by the ODBC gateway, possibly resulting in application aborts. ; ; ; XCW: This version is cloned from v58 of cogdmod_teradata.ini file. Any ; change of cogdmod_teradata.ini file after that version must be ; propagated into here ; ; ************************************************************************ [Builtin Functions] ; If the second parameter ( size) is not specified, it defaults to ; one. It can cause "Rigth truncation of character data" error. c:cast_char(%1bcxdinst {,%2n})=CAST(%1 AS CHAR{(%2)}); d:cast_date(%1cdt)=CAST(%1 AS DATE); n:cast_decimal(%1cin {,%2n,%3n})=CAST(%1 AS DECIMAL{(%2,%3)}); n:cast_double_precision(%1cn)=CAST(%1 AS DOUBLE PRECISION); n:cast_float(%1cn)=CAST(%1 AS FLOAT); n:cast_integer(%1cin)=CAST(%1 AS INTEGER); c:cast_numberToString(%1bcdinst)=CAST(%1 AS CHAR(254)); n:cast_numeric(%1cin {,%2n,%3n})=CAST(%1 AS NUMERIC{(%2,%3)}); n:cast_real(%1cn)=CAST(%1 AS REAL); n:cast_smallint(%1cin)=CAST(%1 AS SMALLINT); ; for Teradata, the second parameter ( size ) is mandatory c:cast_varchar(%1bcxdinst {,%2n})=CAST(%1 AS VARCHAR(%2)); t:localtime({%1n})=CURRENT_TIME; s:localtimestamp({%1n})=CURRENT_TIMESTAMP; ; ; Business Functions for Date Calculations ; d:_add_days(%1d,%2n)=( %1 + cast( ( %2 ) as INTERVAL DAY(4) ) ); s:_add_days(%1s,%2n)=( %1 + cast( ( %2 ) as INTERVAL DAY(4) ) ); d:_add_months(%1d,%2n)=ADD_MONTHS( %1, %2 ); s:_add_months(%1s,%2n)=ADD_MONTHS( %1, %2 ); d:_add_years(%1d,%2n)=ADD_MONTHS( %1, ( (%2) * 12 ) ); s:_add_years(%1s,%2n)=ADD_MONTHS( %1, ( (%2) * 12 ) ); n:_days_between(%1d,%2d)=(%1 - %2); ; overload this function for timestamp parameters to take into account the TIME part of the timestamps that can impact the result (more or less 1 day) n:_days_between(%1s,%2s)=case when ((%1 - CAST(CAST(%1 AS DATE) AS TIMESTAMP) ) HOUR TO SECOND < (%2 - CAST(CAST(%2 AS DATE) AS TIMESTAMP) ) HOUR TO SECOND) and (CAST (%1 AS DATE) - CAST (%2 AS DATE) > 0) then CAST (%1 AS DATE) - CAST (%2 AS DATE) - 1 when ((%1 - CAST(CAST(%1 AS DATE) AS TIMESTAMP) ) HOUR TO SECOND > (%2 - CAST(CAST(%2 AS DATE) AS TIMESTAMP) ) HOUR TO SECOND) and (CAST (%1 AS DATE) - CAST (%2 AS DATE) < 0) then CAST (%1 AS DATE) - CAST (%2 AS DATE) + 1 else CAST (%1 AS DATE) - CAST (%2 AS DATE) end; ;ODBC driver version greater than 3.6.0.1 is suitable for the actual _months_between definition n:_months_between(%1ds,%2ds)=((EXTRACT( YEAR from %1 ) - EXTRACT( YEAR from %2 )) * 12 + EXTRACT( MONTH from %1 ) - EXTRACT( MONTH from %2 ) + CASE WHEN cast(%1 as DATE ) > cast( %2 as DATE ) THEN CASE WHEN EXTRACT( DAY from %1 ) < EXTRACT( DAY FROM %2 ) AND EXTRACT( DAY from LAST_DAY(%1) ) <> EXTRACT( DAY from %1 ) THEN -1 ELSE 0 END WHEN cast(%1 as DATE ) < cast( %2 as DATE ) THEN CASE WHEN EXTRACT( DAY from %1 ) > EXTRACT( DAY from %2 ) AND EXTRACT( DAY from LAST_DAY(%2) ) <> EXTRACT( DAY from %2 ) THEN 1 ELSE 0 END ELSE 0 END); n:_years_between(%1ds,%2ds)=((EXTRACT( YEAR from %1 ) - EXTRACT( YEAR from %2 )) * 12 + EXTRACT( MONTH from %1 ) - EXTRACT( MONTH from %2 ) + CASE WHEN cast(%1 as DATE ) > cast( %2 as DATE ) THEN CASE WHEN EXTRACT( DAY from %1 ) < EXTRACT( DAY FROM %2 ) AND EXTRACT( DAY from LAST_DAY(%1) ) <> EXTRACT( DAY from %1 ) THEN -1 ELSE 0 END WHEN cast(%1 as DATE ) < cast( %2 as DATE ) THEN CASE WHEN EXTRACT( DAY from %1 ) > EXTRACT( DAY from %2 ) AND EXTRACT( DAY from LAST_DAY(%2) ) <> EXTRACT( DAY from %2 ) THEN 1 ELSE 0 END ELSE 0 END)/12; n:_days_to_end_of_month(%1ds)=(EXTRACT( DAY FROM (ADD_MONTHS(%1 - CAST( EXTRACT( DAY FROM %1 ) AS INTERVAL DAY) + INTERVAL '1' DAY, 1) - INTERVAL '1' DAY)) - EXTRACT(DAY FROM %1)); n:_age(%1ds)=; d:_first_of_month(%1d)=(%1 - CAST( EXTRACT( DAY FROM %1 ) AS INTERVAL DAY ) + INTERVAL '1' DAY); s:_first_of_month(%1s)=(%1 - CAST( EXTRACT( DAY FROM %1 ) AS INTERVAL DAY ) + INTERVAL '1' DAY); d:_last_of_month(%1d)=(ADD_MONTHS( ( %1 - CAST( EXTRACT( DAY FROM %1 ) AS INTERVAL DAY ) + INTERVAL '1' DAY ), 1 ) - INTERVAL '1' DAY); s:_last_of_month(%1s)=(ADD_MONTHS( ( %1 - CAST( EXTRACT( DAY FROM %1 ) AS INTERVAL DAY ) + INTERVAL '1' DAY ), 1 ) - INTERVAL '1' DAY); s:_make_timestamp(%1n,%2n,%3n)=; n:_ymdint_between(%1ds,%2ds)=; n:_day_of_year(%1ds)= CAST( ( CAST (%1 AS DATE) + 1 - CAST( ( CAST( EXTRACT( YEAR FROM (CAST ( %1 AS DATE) )) AS CHAR(4)) || '-01-01' ) AS DATE) ) AS INTEGER); n:_round(%1n, %2n)=CASE WHEN (%1) < 0 THEN (CAST( ( (%1) * ( 10 ** (%2) ) - 0.5 ) AS BIGINT ) / ( 10 ** (%2) )) ELSE (CAST( ( (%1) * ( 10 ** (%2) ) + 0.5 ) AS BIGINT ) / ( 10 ** (%2) )) END; n:_week_of_year(%1ds)=; n:_day_of_week(%1ds, %2n)=; ; ; Additional Business Functions for Date Calculations ; t:_add_hours(%1t,%2n)=( %1 + cast( ( %2 ) as INTERVAL HOUR(4) ) ); s:_add_hours(%1s,%2n)=( %1 + cast( ( %2 ) as INTERVAL HOUR(4) ) ); t:_add_minutes(%1t,%2n)=( %1 + cast( ( %2 ) as INTERVAL MINUTE(4) ) ); s:_add_minutes(%1s,%2n)=( %1 + cast( ( %2 ) as INTERVAL MINUTE(4) ) ); t:_add_seconds(%1t,%2n)=( %1 + cast( ( %2 ) as INTERVAL SECOND(4,0) ) ); s:_add_seconds(%1s,%2n)=( %1 + cast( ( %2 ) as INTERVAL SECOND(4,0) ) ); n:_hours_between(%1ds,%2ds)=; n:_minutes_between(%1ds,%2ds)=; n:_seconds_between(%1ds,%2ds)=; ; ; Teradata functions equivalent to EE functions ; bcxdinst:nullif(%1dbcxdinst, %2dbcxdinst)=(NULLIF(%1, %2)); n:round(%1n, %2n)=CASE WHEN (%1) < 0 THEN (CAST( ( (%1) * ( 10 ** (%2) ) - 0.5 ) AS BIGINT ) / ( 10 ** (%2) )) ELSE (CAST( ( (%1) * ( 10 ** (%2) ) + 0.5 ) AS BIGINT ) / ( 10 ** (%2) )) END; n:power(%1n,%2n)=((%1) ** (%2)); n:position(%1cx,%2cx)=INDEX(%2,%1); n:random(%1n,%2n)=RANDOM(%1,%2); ; ; New CAST entry for NCHAR and NVARCHAR ; Added for Trakker #607309 c:coguda#cast_nchar(%1bcxdinst {,%2n})=CAST(%1 AS CHAR(%2)); c:coguda#cast_nvarchar(%1bcxdinst {,%2n})=CAST(%1 AS VARCHAR(%2)); i:coguda#cast_interval(%1)=CAST(%1 AS INTERVAL DAY(4) TO SECOND); i:coguda#cast_interval_year(%1)=CAST(%1 AS INTERVAL YEAR(4)); i:coguda#cast_interval_year_to_month(%1)=CAST(%1 AS INTERVAL YEAR(4) TO MONTH); i:coguda#cast_interval_month(%1)=CAST(%1 AS INTERVAL MONTH(4)); i:coguda#cast_interval_day(%1)=CAST(%1 AS INTERVAL DAY(4)); i:coguda#cast_interval_day_to_hour(%1)=CAST(%1 AS INTERVAL DAY(4) TO HOUR); i:coguda#cast_interval_day_to_minute(%1)=CAST(%1 AS INTERVAL DAY(4) TO MINUTE); i:coguda#cast_interval_day_to_second(%1)=CAST(%1 AS INTERVAL DAY(4) TO SECOND); i:coguda#cast_interval_hour(%1)=CAST(%1 AS INTERVAL HOUR(4)); i:coguda#cast_interval_hour_to_minute(%1)=CAST(%1 AS INTERVAL HOUR(4) TO MINUTE); i:coguda#cast_interval_hour_to_second(%1)=CAST(%1 AS INTERVAL HOUR(4) TO SECOND); i:coguda#cast_interval_minute(%1)=CAST(%1 AS INTERVAL MINUTE(4)); i:coguda#cast_interval_minute_to_second(%1)=CAST(%1 AS INTERVAL MINUTE(4) TO SECOND); i:coguda#cast_interval_second(%1)=CAST(%1 AS INTERVAL SECOND(4)); [Database Functions] ;The scalar functions should be used with escape sequences c:concat(%1,%2)=\{fn CONCAT(%1, %2)\}; n:mod(%1,%2)=\{fn MOD(%1, %2)\}; [Exceptions Aggregates] Avg="avg" Count="count" Count_Star="count(*)" Max="max" Min="min" Stddev_Pop="stddev_pop" Stddev_Samp="stddev_samp" Sum="sum" Var_Pop="var_pop" Var_Samp="var_samp" [Exceptions OLAP Functions] Olap_Avg="avg" Olap_Count="count" Olap_Count_Star="count(*)" Olap_Max="max" Olap_Min="min" Olap_Sum="sum" Olap_Rank="rank" Olap_Percent_Rank="percent_rank" Olap_Row_Number="row_number" [Exceptions Clauses] From="from" Group_By="group by" Having="having" Where="where" ;V2R6 supports the WITH clause, however it supports only one ;common table expression per WITH clause. With= With_Recursive= [Exceptions Commands] Call="call " Delete="delete " Insert="insert " Select="select " Update="update " Max_Literal_Len="30502" [Exceptions Delimiters] Catalog_Delimiter="\"" Schema_Delimiter="\"" Table_Delimiter="\"" Column_Delimiter="\"" Procedure_Delimiter="\"" Wholename_Delimiter="" [Exceptions General] Aggregate_In_Expr=T Aggregate_Value_Expr=T Boolean_Comparison=F Correlated_Subqueries=T Count_Blob=F Count_Non_Distinct=T Count_Value_Expr=T Count_Literal=T Cross_Product=T Distinct_Aggregates=T Distinct_Aggr_In_Case=F Group_By_Alias=T Group_By_Expr=T Group_By_Ordinal=T Group_By_Sorted=F Is_Null_Value_Expr=T Is_Null_Value_Parm=T Like_Value_Expr=T Multiple_Distinct=T Nested_Case=T ; Null=T Not_In_Subquery=T Nulls_Sort_Last=F Order_By_Alias=F ;Union requires order by ordinal Order_By_Name=F Substring_On_Expr=T Subqueries=T Subquery_Column_Alias=T Subquery_In_Case=F Olap_Distinct=F Olap_Null_Order=F Distinct_To_GroupBy=F Parameter_In_Select_List=F Expression_In_In=F [Exceptions General DATABASE:TERADATA VERSION:12.00] Distinct_To_GroupBy=T [Exceptions Joins] Cross=T Cross_Syntax=" CROSS JOIN " Full_Non_Equi_Join=T Full_Outer=T Full_Post="" Full_Pre="" Full_Syntax=" FULL OUTER JOIN " Inner=T Inner_Col_Post="" Inner_Col_Pre="" Inner_Syntax=" INNER JOIN " Inner_Tbl_Restrict=F Left_Eql=" LEFT OUTER JOIN " Left_Nested=T Left_Outer=T Left_Post="" Left_Pre="" Nested_Outer=T Non_Equi_Joins=T On_Condition_Between_Predicate=T On_Condition_In_Predicate=T On_Condition_IsNull_Predicate=T On_Condition_Like_Predicate=T On_Condition_Not_Predicate=T On_Condition_Or_Predicate=T On_Condition_Set_Functions=F On_Condition_Subqueries=T One_Outer=F Optnl_Tbl_Filter=T Optnl_Tbl_Join_Filter=T Optnl_Tbl_Join_Restrict=F Outer_Tbl_List=F Optnl_Tbl_Restrict=F Outer_Syntax=T Right_Eql=" RIGHT OUTER JOIN " Right_Nested=T Right_Outer=T Right_Post="" Right_Pre="" [Exceptions Literals] Alt_Date_Literal_Assignment=F Alt_Date_Literal_Between=F Alt_Date_Literal_Comparison=F Alt_Date_Literal_In=F Alt_Date_Literal_Insert_Value=F Alt_Date_Literal_Str= Alt_Interval_Literal_Str= Alt_Time_Literal_Str= Alt_Timestamp_Literal_Str= Boolean_Literal=F Date_Add_Function_Str= Date_Format_Str="YYYY MM DD" Date_Literal=C Date_Literal_Str="DATE '%s-%s-%s'" Date_Sub_Function_Str= False_Literal_Str= Interval_Format_Str="DDDD HH MM SS FFFFFF" Interval_Literal=T Interval_Literal_Str= IntervalYM_Format_Str="YYYY MM" IntervalYM_Literal=T IntervalYM_Literal_Str= Interval_Sign_Before_Quote=T Time_Format_Str="HH MM SS FFFFFF" Time_Literal=C Time_Literal_Str="TIME '%s:%s:%s.%s'" TimeTZ_Format_Str= TimeTZ_Literal=F TimeTZ_Literal_Str= Timestamp_Format_Str="YYYY MM DD HH MM SS FFFFFF" Timestamp_Literal=C Timestamp_Literal_Str="TIMESTAMP '%s-%s-%s %s:%s:%s.%s'" TimestampTZ_Format_Str= TimestampTZ_Literal=F TimestampTZ_Literal_Str= True_Literal_Str= Unknown_Literal_Str= Zero_Date_Literal_Str= Zero_Timestamp_Literal_Str= NChar_Literal=T [Exceptions Misc] Case_Parentheses=T [Exceptions Operators] Add="+" And="and" Div="/" Eql="=" Geq=">=" Grt=">" Leq="<=" Les="<" Mul="*" ;Neg="-" Neq="<>" Not="not" Or="or" Sub="-" [Exceptions Predicates] All="all" [Exceptions Separators] Catalog_Separator="." Schema_Separator="." Table_Separator="." [Exceptions Set Operators] Distinct="distinct" Except=" except " Except_All=T Intersect="intersect" Intersect_All=T ;Order_By= Union="union" Union_All=T [Exceptions Tables] Joined=T Derived=T UniqueName_Prefix="coguda" [Exceptions Value Expressions] Case=T Cast=T Coalesce=T Extract=T Octet_Length=T Nullif=C Substring=T Upper=T StrCat=T Char_Length=T Position=C Current_Date=T Current_Time=F Current_Timestamp=F LocalTime=C LocalTimestamp=C Abs=T Exp=T Ln=T Mod=T Power=C Sqrt=T Ceiling=F Lower=T Floor=F Trim=T [Exceptions Blob Expressions] Blob_In_Substring=T Blob_In_Trim=T Blob_In_StrCat=T [Operator Addition] Date= Time= Timestamp= Interval= [Operator Subtraction] Date= Time= Timestamp= Interval= [Operator Comparison] NV=CH,OK:NC,OK:VC,OK NC=NV,OK:CH,OK:VC,OK VC=NV,OK:NC,OK CH=NV,OK:NC,OK [Misc] ; Default processing mode. Processing Mode=Database Only ;Use the wild card (i.e. %) as the table name to retrieve all the schemas. Wild_Card_As_Tablename= ; Row Limit in Teradata is achieved by adding ; the following line: ; TOP n ; Right after the first SELECT or SELECT DISTINCT ; [Directives Session] Query_Row_Limit_Pos=Append_Distinct Query_Row_Limit_Text=" TOP %d " ; ; Operations supported and results of the supported operation. ; The format is; ; [Operator ] ; =,{:,} ; ; where ; ; LHS = data type of left hand side of operation ; RHS = data type of right hand side of operation ; RES = result data type of operation ; ; LHS, RHS and RES values may be one of: ; ; Name Abbreviation DMS Data Type ; CHAR CH sqlChar ; VARCHAR VC sqlVarChar ; LVARCHAR LC sqlLongVarChar ; NCHAR NC sqlNChar ; NVARCHAR NV sqlNVarChar ; BINARY BN sqlBinary ; VARBINARY VB sqlVarBinary ; BOOLEAN BO sqlBoolean ; SMALLINT SM sqlSmallInt ; INTEGER IT sqlInteger ; QUAD QD sqlQuad ; DECIMAL DM sqlDecimal ; NUMERIC NU sqlNumeric ; FLOAT FL sqlFloat ; DOUBLE DB sqlDouble ; DATE DT sqlDate ; TIME TM sqlTime ; TIMESTAMP TS sqlDateTime ; INTERVAL IV sqlInterval ; TIME_TZ TT sqlTimeTZ ; TIMESTAMP_TZ TZ sqlTimestampTZ ; INTERVAL_YM IY sqlIntervalYM ; ALL DATATYPES AD ------------- ; ---- NA sqlUnknownType ; [Operator Cast DATABASE:TERADATA] AD=AD,AD AD=TT,NA:TZ,NA TM=TM,NA TS=TM,NA TT=TM,NA TZ=TS,NA DB=IV,NA:IY,NA FL=IV,NA:IY,NA [DATABASE:TERADATA VERSION:16] ;This section is used by Teradata ANSI ODBC driver on Unix ;for IANNAAppCodePage that is specified in the odbc.ini file. [I18N Encoding Mapping DATABASE:TERADATA] ;ascii 3=US-ASCII ;No euro updates 4=iso-8859-1 ;Central Europe 5=iso-8859-2 ;Maltese Esperanto 6=iso-8859-3 ;Baltic 7=iso-8859-4 ;Cyrillic 8=iso-8859-5 ;Arabic 9=iso-8859-6 ;ISO Greek (w/o euro update) 10=iso-8859-7 ;hebrew 11=iso-8859-8 ;Turkish 12=iso-8859-9 ;UTF-8 106=utf-8 ;Latin9, with Euro updates 111=iso-8859-15 ;Japanese MS_Kanji 17=Shift_JIS ;Korean, same as cp1363 36=korean ;HP Latin1 2004=roman8 ;PRC Chinese 2025=GB2312 114=gb18030 ;Taiwan Chinese 2026=Big5 ;EBCDIC Germany 2030=CP273 ;windows-1252 2252=cp1252 ;Thai Industry Standard Institute 2259=TIS-620 ; ;The following is the standard way to retrieve the database character set when establishing connections to Teradata. ;The character set information is used for some useful optimizations. ;If the character set is not known, the product will still work fine, thus the statement can be commented out. ;The statement could be replaced with one that gives the same answers. If the string "%s" is present, it will be replaced with the user name before execution. ;If the replacement statement throws an error, the error will be returned to the user and the connection will fail. ;If the replacement statement returns no rows, this is interpreted as unknown character set, and no error is returned to the user. ; [DATABASE:TERADATA] ;line below is to read subsections [DATABASE:TERADATA], and [DATABASE:TERADATA VERSION:16.20] DBINFO_SUBSECTIONS="DBNAM,DBMIN" Interval Workaround="T" SupportUnicode="T" CHARACTER_SET_SQL="SELECT CurrentCollation, LogonSource FROM DBC.SessionInfoX WHERE SessionNo = SESSION AND UserName = '%s' ORDER BY LogonSequenceNo desc, LogonDate desc, LogonTime desc" ; This entry is used to specify whether the prepared statement will be closed ; when reaching the end of data, the connection occupied can be used by other ; statement. CLOSE_HSTMT_AT_EOD="T" ; When set to T, the sub connections held by the request will be free when release request. RELEASE_SUBCONNECTION="T" ;Teradata supports only one asynchronous active statement per connection SQL_MAX_CONCURRENT_ACTIVITIES="1" ;When set to T, disable the command "Help Column", and disable the command to get the database collation. disable_Help_Column = "F" ;If plus sign appears in the numeric string, ;[Teradata][ODBC Teradata Driver] Numeric value out of range Unsigned Positive Numbers="T" [DRIVER:TDATAODBC_SB32.DLL] ;select 1 from T where QC_INTEGER = cast(? as integer) ;[Teradata][ODBC Teradata Driver] (80) The database cannot determine the type and size of parameter, returning 0. ;RECJ6ZHU7 SQL_API_SQLDESCRIBEPARAM="FALSE" [DRIVER:TDATAODBC_SB32.SO] SQL_API_SQLDESCRIBEPARAM="FALSE" [DRIVER:TDATAODBC_SB64.DLL] SQL_API_SQLDESCRIBEPARAM="FALSE" [DRIVER:TDATAODBC_SB64.SO] SQL_API_SQLDESCRIBEPARAM="FALSE" [DRIVER:TDATA32.DLL] SQL_API_SQLDESCRIBEPARAM="FALSE" [DRIVER:TDATA.SO] SQL_API_SQLDESCRIBEPARAM="FALSE"