analytics/bin/cogdmod_ve.ini

;***********************************************************************
;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"