;*********************************************************************** ;Licensed Materials - Property of IBM ; ;BI and PM: UDA ; ;(C) Copyright IBM Corp. 2005, 2019 ; ;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: $ ; ; Module: ; cogdmod_ve.ini ; ; Purpose: ; This module contains the Vertica gateway information. ; ; Notes: ; Do NOT modify this file. Doing so could result in unknown behavior ; by the Vertica gateway, possibly resulting in application aborts. ; ; ************************************************************************ [DATABASE:VERTICA DATABASE] Disable_Subquery_With_Parameters=T Keyword Alias=" as \"%s\" " ; Vertica support for parameter markers is very limited and specific so not considered supported SQL_API_SQLDESCRIBEPARAM="FALSE" [Exceptions Commands DATABASE:VERTICA DATABASE] Select="select" Insert= Update= Delete= Call= Select_Star="T" Max_Stmt_Len="" [Exceptions Clauses DATABASE:VERTICA DATABASE] From="from" Where="where" Group_By="Group By" Having="having" Window= With= With_Recursive= [Exceptions Tables DATABASE:VERTICA DATABASE] Derived=T Joined=T Simple_And_Joined=T Tbl_Ref_Limit="" SubSelect_Limit="" Table_Constructor=F UniqueName_Prefix="coguda" [Exceptions Tables DATABASE:VERTICA DATABASE VERSION:03] Joined=F [Exceptions Joins DATABASE:VERTICA DATABASE] Inner=T Left_Outer=T Right_Outer=T Full_Outer=T Cross=T On_Condition_Or_Predicate=T Nested_Outer=T Inner_Tbl_Restrict=F Optnl_Tbl_Restrict=F Non_Equi_Joins=T One_Outer=F Natural=T Left_Nested=T Right_Nested=T Two_Sided_Join_Restrict=F Optnl_Tbl_Join_Restrict=F Optnl_Tbl_Join_Filter=T Optnl_Tbl_Filter=T Prsrv_Tbl_Join_Filter=T Prsrv_Tbl_Filter=T On_Condition_Set_Functions=F On_Condition_Subqueries=F Full_Non_Equi_Join=T On_Condition_In_Predicate=T On_Condition_Not_Predicate=T On_Condition_Like_Predicate=T On_Condition_Between_Predicate=T On_Condition_IsNull_Predicate=T Non_Join_Expr=T Non_Join_Non_Equi_Expr=T Only_Eql_With_And=F On_Condition_Functions=T Two_Sided_Join_Restrict_Filter=F Nested_Inner=T Product_Join=T Outer_Non_Equi_Join=T On_Condition_No_Expr=T Outer_Syntax=T Left_Pre= ;Left_Eql=" LEFT OUTER JOIN " Left_Post= Right_Pre= ;Right_Eql=" RIGHT OUTER JOIN " Right_Post= ;Full_Syntax=" FULL OUTER JOIN " Full_Pre= Full_Post= ;Inner_Syntax=" INNER JOIN " Inner_Col_Pre= Inner_Col_Post= ;Cross_Syntax=" CROSS JOIN " ;Union_Syntax=" UNION " ;Natural_Syntax=" NATURAL JOIN " [Exceptions Joins DATABASE:VERTICA DATABASE VERSION:03] ; Some 3.0.x ODBC drivers incorrectly claim FOJ is available. Vertica defect VER-9676 Full_Outer=F Optnl_Tbl_Restrict=T Left_Nested=F Right_Nested=F Optnl_Tbl_Join_Restrict=T On_Condition_Set_Functions=T Full_Non_Equi_Join=F Only_Eql_With_And=T Two_Sided_Join_Restrict_Filter=T [Exceptions Predicates DATABASE:VERTICA DATABASE] Between="between" In="in" Like="like" Is_Null="is null" Any= All= Exists="exists" NotLike="not like" [Exceptions Aggregates DATABASE:VERTICA DATABASE] Max="max" Min="min" Sum="sum" Avg="avg" Count="count" Count_Star="count(*)" Stddev="stddev" Stddev_Pop="stddev_pop" Stddev_Samp="stddev_samp" Variance="variance" Var_Pop="var_pop" Var_Samp="var_samp" Grouping= Rank= Dense_Rank= Percent_Rank= Cume_Dist= Percentile_Cont= Percentile_Disc= First_Value= Last_Value= Percentile=F [Exceptions OLAP Functions DATABASE:VERTICA DATABASE] Olap_Min="min" Olap_Max="max" Olap_Sum="sum" Olap_Avg="avg" Olap_Count="count" Olap_Count_Star="count(*)" Olap_Stddev= Olap_Stddev_Pop="stddev_pop" Olap_Stddev_Samp= Olap_Variance= Olap_Var_Pop="var_pop" Olap_Var_Samp= Olap_Rank="rank" Olap_Dense_Rank="dense_rank" Olap_Percent_Rank="percent_rank" Olap_Cume_Dist="cume_dist" Olap_Percentile_Cont="percentile_cont" Olap_Percentile_Disc="percentile_disc" Olap_First_Value="first_value" Olap_Last_Value="last_value" Olap_Row_Number="row_number" Olap_Ratio_To_Report= Olap_Ntile="ntile" ; Olap_Count_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" ; Olap_Sum_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" Olap_Last_Value_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" [Exceptions OLAP Functions DATABASE:VERTICA DATABASE VERSION:03] Olap_Min= Olap_Max= Olap_Sum= Olap_Avg= Olap_Count= Olap_Count_Star= Olap_Stddev_Pop= Olap_Var_Pop= Olap_Percent_Rank= Olap_Cume_Dist= Olap_Percentile_Cont= Olap_Percentile_Disc= Olap_Last_Value= Olap_Ntile= ; Olap_Last_Value_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" [Exceptions Set Operators DATABASE:VERTICA DATABASE] Union="union" Union_All=T Intersect= Intersect_All=F Except= Except_All=F Distinct="distinct" Order_By="order by" [Exceptions Operators DATABASE:VERTICA DATABASE] Add="+" Sub="-" Mul="*" Div="/" Eql="=" Neq="<>" Grt=">" Geq=">=" Les="<" Leq="<=" Neg="-" And="and" Or="or" Not="not" Assign="=" ; Note: Vertica 3.0 documents that string functions do not handle multibyte UTF-8 sequences correctly. ; They treat each byte as a character. [Exceptions Value Expressions DATABASE:VERTICA DATABASE] Case_Simple=T Case_Searched=T Coalesce=T Nullif=T Grouping_Sets=F Rollup=F Cube=F ; Position. In some edge cases Vertica does not align to the SQL standard Position=T Char_Length=F Octet_Length=F Substring=T Upper=T Lower=T StrCat=T Cast=C Abs=T Floor=T Ceiling=C ; Ln. In some edge cases Vertica does not align to the SQL standard and follows IEEE ln=T Exp=T ; Ln. In some edge cases Vertica does not align to the SQL standard and follows IEEE Mod=T ; Sqrt. In some edge cases Vertica does not align to the SQL standard and follows IEEE Sqrt=T ; Power. In some edge cases Vertica does not align to the SQL standard and follows IEEE Power=T Current_Date=T ;Unable to get timezone type via ODBC so disabled Current_Time=F Current_TimeStamp=F LocalTime=T LocalTimestamp=T ; Aspects of Extract can be mapped via entries in the built in section Extract=C Extract_Year=coguda#extract_year Extract_Month=coguda#extract_month Extract_Day=coguda#extract_day Extract_Hours=coguda#extract_hour Extract_Minutes=coguda#extract_minute Extract_Seconds=coguda#extract_second Trim=T Row_Value_Constructors=T Olap_Window_Order_By=T [Exceptions General DATABASE:VERTICA DATABASE] Aggregate_In_Expr=T Aggregate_Value_Expr=T Correlated_Subqueries=T Cross_Product=T Distinct_Aggregates=T Distinct_Aggr_In_Case=T Group_By_Expr=T Group_By_Ordinal=T Group_By_Alias=T Multiple_Distinct=T Count_Non_Distinct=T Count_Value_Expr=T Count_Literal=T Is_Null_Value_Expr=T Is_Null_Value_Parm=T Like_Value_Expr=T Subquery_In_Having=T Subquery_In_Group_By=F Subquery_In_Case=F Count_Blob=T Expression_In_In=T Null_Order=F Olap_Null_Order=T Olap_Distinct=F Nested_Olap_Functions=F Empty_String_Is_Null=F Order_By_In_Derived_Table=T Interval_As_VarChar=F Boolean_Comparison=T Derived_Column_List=T With_Column_List=F Aggr_With_Interval=T Nested_Case=T Not_In_Subquery=T Nulls_Sort_Last=F ; Note: Vertica sorts nulls first or last depending on the data type. Order_By_Alias=T Order_By_Name=T Substring_On_Expr=T Subqueries=T Subquery_Column_Alias=T Parameter_In_Select_List=T Remove_Parameter_Cast=T Subquery_With_Parameters=T Like_With_Parameters=T Like_With_Escape=T Comments=T Integer_Division_Truncation=T ; Vertica has various limitations with respect to parameter markers Parameter_Markers=F [Exceptions General DATABASE:VERTICA DATABASE VERSION:03] Group_By_Alias=F [Exceptions Literals DATABASE:VERTICA DATABASE] Date_Literal=T Timestamp_Literal=T Time_Literal=T Interval_Literal=F IntervalYM_Literal=F TimeTZ_Literal=F TimestampTZ_Literal=F NChar_Literal=T [Exceptions Misc DATABASE:VERTICA DATABASE] [Exceptions Delimiters DATABASE:VERTICA DATABASE] ;Catalog_Delimiter="\"" ;Schema_Delimiter="\"" ;Table_Delimiter="\"" ;Column_Delimiter="\"" ;Procedure_Delimiter="\"" ;Wholename_Delimiter="\"" ;Table_Function_PreStr="TABLE( " ;Table_Function_PostStr=" )" ; ANSI style dashdash is also supported Comment_Begin="/* " Comment_End=" */ " [Exceptions Separators DATABASE:VERTICA DATABASE] Catalog_Separator="." Schema_Separator="." Table_Separator="." [Exceptions Blob Expressions DATABASE:VERTICA DATABASE] Blob_In_Substring=T Blob_In_Trim=T Blob_In_StrCat=T Blob_In_IsNull=T Blob_In_Substring_Return_Blob=F ;b stands for boolean. ;c stands for character. ;x stands for text. ;d stands for date. ;i stands for the interval. ;n stands for the numeric. ;s stands for the timestamp. ;t stands for the time. ;y stands for time with time zone ;z stands for timestamp with time zone [Database Functions DATABASE:VERTICA DATABASE] n:ceiling(%1n)=CEIL(%1); [Builtin Functions DATABASE:VERTICA DATABASE] d:_add_days(%1d,%2n)=CAST(%1 + (INTERVAL '1' day * FLOOR(%2)) AS DATE); d:_add_months(%1d,%2n)=CAST(%1 + (INTERVAL '1' month * FLOOR(%2)) AS DATE); d:_add_years(%1d,%2n)=CAST(%1 + (INTERVAL '1' year * FLOOR(%2)) AS DATE); d:_first_of_month(%1d)=CAST((%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1' DAY ) + INTERVAL '1' DAY) AS DATE); d:_last_of_month(%1d)=CAST( (%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1' DAY ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY ) AS DATE); s:_add_days(%1s,%2n)=%1 + (INTERVAL '1' day * FLOOR(%2)); s:_add_months(%1s,%2n)=%1 + (INTERVAL '1' month * FLOOR(%2)) ; s:_add_years(%1s,%2n)=%1 + (INTERVAL '1' year * FLOOR(%2)) ; s:_first_of_month(%1s)=%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1' DAY ) + INTERVAL '1' DAY ; s:_last_of_month(%1s)=%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1' DAY ) + INTERVAL '1' MONTH; s:_make_timestamp(%1n,%2n,%3n)=cast(TO_TIMESTAMP('%1-%2-%3','YYYY-MM-DD') as timestamp without time zone); s:_add_hours(%1s,%2n)=%1 + (INTERVAL '1' hour * FLOOR(%2)); s:_add_minutes(%1s,%2n)=%1 + (INTERVAL '1' minute * FLOOR(%2)); s:_add_seconds(%1s,%2n)=%1 + (INTERVAL '1' second * FLOOR(%2)); n:_days_between(%1ds,%2ds)=DATEDIFF(day, %2, %1); n:_months_between(%1ds,%2ds)=DATEDIFF(month, %2, %1); n:_years_between(%1ds,%2ds)=age_in_years(%1, %2); n:_hours_between(%1s,%2s)=DATEDIFF('hour', %1 , %2 ); n:_minutes_between(%1s,%2s)=DATEDIFF('minute', %1 , %2 ); n:_seconds_between(%1s,%2s)=DATEDIFF('second', %1 , %2 ); n:_days_to_end_of_month(%1ds)=DATEDIFF('dy', %1, (%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1' DAY ) + INTERVAL '1' MONTH) ); n:_day_of_year(%1ds)=EXTRACT('doy' FROM %1); n:_day_of_week(%1ds, %2n)=(MOD( CAST( TO_CHAR( %1, 'D' ) AS INTEGER ) + 6 - %2, 7 ) + 1); n:_age(%1ds)=CAST((age_in_years(current_date, %1 )*10000 + age_in_months(current_date, %1 ) % 12 * 100 + (current_date - ( %1 + age_in_months(current_date, %1 ) * interval '1' month)::date)) AS INT); ;n:_ymdint_between(%1ds,%2ds)=; n:_week_of_year(%1ds)=EXTRACT( WEEK FROM %1 ); n:_round(%1n, %2n)=ROUND(%1,%2); n:coguda#extract_year(%1ds)=extract(year from %1); n:coguda#extract_month(%1ds)=extract(month from %1); n:coguda#extract_day(%1ds)=extract(day from %1); n:coguda#extract_hour(%1st)=extract(hour from %1); n:coguda#extract_minute(%1st)=extract(minute from %1); n:coguda#extract_second(%1st)=extract(millisecond from %1 )/1000; [Operator Addition] [Operator Subtraction] [Operator Multiplication] [Operator Division] [Operator Comparison] [Operator Cast DATABASE:VERTICA DATABASE] AD=AD,NA BL=AD,NA CH=CH,CH:DB,DB:DM,DM:DT,DT:FL,FL:IT,IT:NU,NU:QD,QD:TM,TM:TS,TS:VC,VC DB=DB,DB:DM,DM:FL,FL:IT,IT:NU,NU:QD,QD DM=DB,DB:DM,DM:FL,FL:IT,IT:NU,NU:QD,QD DT=CH,CH:DT,DT:TS,TS:VC,VC FL=DB,DB:DM,DM:FL,FL:IT,IT:NU,NU:QD,QD IT=CH,CH:DB,DB:DM,DM:FL,FL:IT,IT:NU,NU:QD,QD:VC,VC IV=AD,NA IY=AD,NA NA=CH,CH:DB,DB:DT,DT:FL,FL:IT,IT:QD,QD:TM,TM:VC,VC:NU,NU:DM,DM NC=AD,NA NU=DB,DB:FL,FL:IT,IT:QD,QD QD=CH,CH:DB,DB:DM,DM:FL,FL:IT,IT:NU,NU:QD,QD:VC,VC SM=AD,NA TM=TM,TM TS=DT,DT:TM,TM:TS,TS TT=AD,NA TZ=AD,NA VC=CH,CH:DB,DB:DM,DM:DT,DT:FL,FL:IT,IT:NU,NU:QD,QD:TM,TM:TS,TS:VC,VC [Operator Cast DATABASE:VERTICA DATABASE VERSION:03] AD=AD,NA BL=AD,NA CH=CH,CH:DB,DB:DT,DT:FL,FL:IT,IT:QD,QD:TM,TM:TS,TS:VC,VC DB=DB,DB:FL,FL:IT,IT:QD,QD DT=CH,CH:DT,DT:TS,TS:VC,VC FL=DB,DB:FL,FL:QD,QD IT=CH,CH:DB,DB:FL,FL:IT,IT:QD,QD:VC,VC IV=AD,NA IY=AD,NA NA=CH,CH:DB,DB:DT,DT:FL,FL:IT,IT:QD,QD:TM,TM:VC,VC NC=AD,NA QD=CH,CH:DB,DB:FL,FL:IT,IT:QD,QD:VC,VC SM=AD,NA TM=TM,TM TS=DT,DT:TM,TM:TS,TS TT=AD,NA TZ=AD,NA VC=CH,CH:DB,DB:DT,DT:FL,FL:IT,IT:QD,QD:TM,TM:TS,TS:VC,VC FL=DB,DB:FL,FL:QD,QD [Directives Session] [Builtin Functions DATABASE:VERTICA DATABASE VERSION:03] d:_add_days(%1d,%2n)=CAST(%1 + (INTERVAL '1 day' * FLOOR(%2)) AS DATE); d:_add_months(%1d,%2n)=CAST(%1 + (INTERVAL '1 month' * FLOOR(%2)) AS DATE); d:_add_years(%1d,%2n)=CAST(%1 + (INTERVAL '1 year' * FLOOR(%2)) AS DATE); d:_first_of_month(%1d)=CAST((%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1 DAY' ) + INTERVAL '1 DAY') AS DATE); d:_last_of_month(%1d)=CAST((%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1 DAY' ) + INTERVAL '1 MONTH') AS DATE); s:_add_days(%1s,%2n)=%1 + (INTERVAL '1 day' * FLOOR(%2)); s:_add_months(%1s,%2n)=%1 + (INTERVAL '1 month' * FLOOR(%2)) ; s:_add_years(%1s,%2n)=%1 + (INTERVAL '1 year' * FLOOR(%2)) ; s:_first_of_month(%1s)=%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1 DAY' ) + INTERVAL '1 DAY' ; s:_last_of_month(%1s)=%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1 DAY' ) + INTERVAL '1 MONTH'; s:_add_hours(%1s,%2n)=%1 + (INTERVAL '1 hour' * FLOOR(%2)); s:_add_minutes(%1s,%2n)=%1 + (INTERVAL '1 minute' * FLOOR(%2)); s:_add_seconds(%1s,%2n)=%1 + (INTERVAL '1 second' * FLOOR(%2)); n:_years_between(%1ds,%2ds)=EXTRACT (YEAR FROM AGE (%1 , %2 ) ); n:_days_to_end_of_month(%1ds)=DATEDIFF('dy', %1, (%1 - ( EXTRACT( DAY FROM %1 ) * INTERVAL '1 DAY' ) + INTERVAL '1 MONTH') ); n:_age(%1ds)=CAST((extract('year' from age( %1)) * 10000 + extract('month' from age( %1 )) * 100 + extract('day' from age( %1 )) ) AS INT); n:_week_of_year(%1ds)=EXTRACT( WEEK FROM %1 ); n:_round(%1n, %2n)=ROUND(%1,%2); n:coguda#extract_year(%1ds)=extract(year from %1); n:coguda#extract_month(%1ds)=extract(month from %1); n:coguda#extract_day(%1ds)=extract(day from %1); n:coguda#extract_hour(%1st)=extract(hour from %1); n:coguda#extract_minute(%1st)=extract(minute from %1); n:coguda#extract_second(%1st)=extract(millisecond from %1 )/1000; [DATABASE:VERTICA DATABASE VERSION:03] ; When connecting to Vertica 3.x look at these sections DBINFO_SUBTYPE="VERSION:03"