analytics/bin/cogdmod_rb.ini

;***********************************************************************
;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: $
;
; Module:
;    cogdmod_rb.ini
;
; Purpose:
;    This module contains the redbrick SQL information.
;
; Notes:
;    Do NOT modify this file.  Doing so could result in unknown SQL
;    being generated for redbrick, possibly resulting in application aborts.
;
; This file is cloned from cogdmod_redbrick.ini version 49. Any change 
; after that must be propagated by into this one. XC // TODO
;
; ************************************************************************


[Builtin Functions]
;
; Business Functions for Date Calculations
;
d:_add_days(%1d,%2n)=DATEADD( DAY, %2, %1 );
s:_add_days(%1s,%2n)=DATEADD( DAY, %2, %1 );
d:_add_months(%1d,%2n)=DATEADD( MONTH, %2, %1 );
s:_add_months(%1s,%2n)=DATEADD( MONTH, %2, %1 );
d:_add_years(%1d,%2n)=DATEADD( YEAR, %2, %1 );
s:_add_years(%1s,%2n)=DATEADD( YEAR, %2, %1 );
n:_days_between(%1ds,%2ds)=DATEDIFF( DAY, %1, %2 );
n:_months_between(%1ds,%2ds)=DATEDIFF( MONTH, %1, %2 );
n:_years_between(%1ds,%2ds)=DATEDIFF( YEAR, %1, %2 );
n:_days_to_end_of_month(%1ds)=DATEDIFF( DAY, DATEADD( DAY, -1, DATEADD( MONTH, 1, DATEADD( DAY, 1 - DATEPART( DAY, %1 ), %1 ) ) ), %1 );
n:_age(%1ds)=;
d:_first_of_month(%1d)=DATEADD( DAY, -DATEPART( DAY, %1 ) + 1, %1 );
s:_first_of_month(%1s)=DATEADD( DAY, -DATEPART( DAY, %1 ) + 1, %1 );
d:_last_of_month(%1d)=DATEADD( DAY, -1, DATEADD( MONTH, 1, DATEADD( DAY, -DATEPART( DAY, %1 ) + 1, %1 ) ) );
s:_last_of_month(%1s)=DATEADD( DAY, -1, DATEADD( MONTH, 1, DATEADD( DAY, -DATEPART( DAY, %1 ) + 1, %1 ) ) );
s:_make_timestamp(%1n,%2n,%3n)=DATEADD( YEAR, (%1) - 1, DATEADD( MONTH, (%2) - 1, DATEADD( DAY, (%3) - 1, TIMESTAMP '0001-01-01 00:00:00' ) ) );
n:_ymdint_between(%1ds,%2ds)=;
n:_day_of_year(%1ds)=EXTRACT( DAYOFYEAR FROM %1 );
n:_week_of_year(%1ds)=;
n:_day_of_week(%1ds, %2n)=;

;
; Additional Business Functions for Date Calculations
;
s:_add_hours(%1s,%2n)=DATEADD( HOUR, %2, %1 );
t:_add_hours(%1t,%2n)=DATEADD( HOUR, %2, %1 );
s:_add_minutes(%1s,%2n)=DATEADD( MINUTE, %2, %1 );
t:_add_minutes(%1t,%2n)=DATEADD( MINUTE, %2, %1 );
s:_add_seconds(%1s,%2n)=DATEADD( SECOND, %2, %1 );
t:_add_seconds(%1t,%2n)=DATEADD( SECOND, %2, %1 );
n:_hours_between(%1ds,%2ds)=DATEDIFF( HOUR, %2, %1 );
n:_minutes_between(%1ds,%2ds)=DATEDIFF( MINUTE, %2, %1 );
n:_seconds_between(%1ds,%2ds)=DATEDIFF( SECOND, %2, %1 );

c:trim_both_spaces(%1c)=TRIM(%1);
c:trim_leading_spaces(%1c)=LTRIM(%1);
c:trim_trailing_spaces(%1c)=RTRIM(%1);

[Database Functions]
n:ceiling(%1n)=CEIL(%1);
t:localtime()=CURRENT_TIME;
t:localtime(%1n)=CURRENT_TIME(%1);
s:localtimestamp()=CURRENT_TIMESTAMP;
s:localtimestamp(%1n)=CURRENT_TIMESTAMP(%1);
n:cume(%1n)=CUME(%1);
n:ntile(%1n,%2n)=NTILE(%1,%2);
n:rank(%1n)=RANK(%1);
c:substring(%1c, %2n {,%3n})=SUBSTR(%1, %2 {,%3});
n:position(%1c,%2c)=POSITION(%1,%2);
n:octet_length(%1c)=LENGTHB(%1);
c:coguda#concat(%1c,%2c)=CONCAT(%1, %2);
c:coguda#concat(%1bdints,%2bdints)=CONCAT(%1, %2);
n:char_length(%1c)=LENGTH(%1);
n:_round(%1n, %2n)=ROUND(%1, %2);
n:round(%1n, %2n)=ROUND(%1, %2);
n:power(%1n,%2n)=exp((%2) * ln((%1)));
c:cast_char(%1bdints {, %2n {, %3n }})=LTRIM(STRING(%1 {, %2 {, %3}}));
d:cast_date(%1dcs)=DATE(%1);
n:cast_decimal(%1cn)=DECIMAL(%1 {, %2 {, %3}});
n:cast_float(%1bcn)=FLOAT(%1);
n:cast_integer(%1bcn)=INTEGER(%1);
n:cast_real(%1nc)=REAL(%1);
t:cast_time(%1cs)=TIME(%1);
s:cast_timestamp(%1cdt {, %2t})=TIMESTAMP(%1 {, %2});
d:date(%1cds)=DATE(%1);

;
; CAST Functions
;
c:coguda#cast_char(%1n, %2n)=LTRIM(STRING(%1, ( %2 + 1 ) ));
c:coguda#cast_char(%1bdits,%2n)=LTRIM(STRING(%1, %2));
d:coguda#cast_date(%1dcs)=DATE(%1);
n:coguda#cast_decimal(%1cn {, %2n {, %3n }})=DECIMAL(%1 {, %2 {, %3}});
n:coguda#cast_integer(%1bcn)=INTEGER(%1);
n:coguda#cast_float(%1bcn)=REAL(%1);
n:coguda#cast_double_precision(%1bcn)=FLOAT(%1);
t:coguda#cast_time(%1cst)=TIME(%1);
s:coguda#cast_timestamp(%1cs)=TIMESTAMP(%1);
s:coguda#cast_timestamp(%1d)=TIMESTAMP(%1, \{ t '00:00:00'\});
s:coguda#cast_timestamp(%1t)=TIMESTAMP(CURRENT_DATE, %1);

;Functions required to cast division of integer/numeric to a floating point
n:coguda#_division_cast_float(%1bcn)=REAL(%1);
n:coguda#_division_cast_double_precision(%1bcn)=FLOAT(%1);

[Database Functions Version:6.20]
n:_round(%1n, %2n)=FLOOR( (%1) * EXP( (%2) * LN(10)) + 0.5 ) / EXP( (%2) * LN(10) );
n:round(%1n, %2n)=FLOOR( (%1) * EXP( (%2) * LN(10)) + 0.5 ) / EXP( (%2) * LN(10) );

[Exceptions Aggregates]
    Avg="avg"
    Count="count"
    Count_star="count(*)"
    Max="max"
    Min="min"
    Sum="sum"

[Exceptions OLAP Functions]
Olap_Max="max"
Olap_Min="min"
Olap_Sum="sum"
Olap_Avg="avg"
Olap_Count="count"
Olap_Count_Star="count(*)"
Olap_Stddev_Pop=
Olap_Var_Pop=
Olap_Stddev_Samp=
Olap_Var_Samp=
Olap_Rank="rank"
Olap_Dense_Rank="dense_rank"
Olap_Percent_Rank="percent_rank"
Olap_Cume_Dist="cume_dist"
Olap_Row_Number="row_number"
Olap_Ratio_To_Report="ratio_to_report"
Olap_Ntile="ntile"

[Exceptions OLAP Functions Version:6.0]
Olap_Max=
Olap_Min=
Olap_Sum=
Olap_Avg=
Olap_Count=
Olap_Count_Star=
Olap_Stddev_Pop=
Olap_Var_Pop=
Olap_Stddev_Samp=
Olap_Var_Samp=
Olap_Rank=
Olap_Dense_Rank=
Olap_Percent_Rank=
Olap_Cume_Dist=
Olap_Row_Number=
Olap_Ratio_To_Report=
Olap_Ntile=

[Exceptions OLAP Functions Version:6.1]
Olap_Max=
Olap_Min=
Olap_Sum=
Olap_Avg=
Olap_Count=
Olap_Count_Star=
Olap_Stddev_Pop=
Olap_Var_Pop=
Olap_Stddev_Samp=
Olap_Var_Samp=
Olap_Rank=
Olap_Dense_Rank=
Olap_Percent_Rank=
Olap_Cume_Dist=
Olap_Row_Number=
Olap_Ratio_To_Report=
Olap_Ntile=

[Exceptions OLAP Functions Version:6.11]
Olap_Max=
Olap_Min=
Olap_Sum=
Olap_Avg=
Olap_Count=
Olap_Count_Star=
Olap_Stddev_Pop=
Olap_Var_Pop=
Olap_Stddev_Samp=
Olap_Var_Samp=
Olap_Rank=
Olap_Dense_Rank=
Olap_Percent_Rank=
Olap_Cume_Dist=
Olap_Row_Number=
Olap_Ratio_To_Report=
Olap_Ntile=

[Exceptions OLAP Functions Version:6.20]
Olap_Percent_Rank=
Olap_Cume_Dist=

[Exceptions Clauses]
    From="from"
    Group_By="group by"
    Having="having"
    Where="where"

[Exceptions Commands]
    Call="call "
    Delete="delete "
    Insert="insert "
    Select="select "
    Update="update "

[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
    Group_By_Alias=T
    Group_By_Expr=T
    Is_Null_Value_Expr=T
	Is_Null_Value_Parm=F
    Like_Value_Expr=T
    Multiple_Distinct=T
    ; Null=T
    Nulls_Sort_Last=T
    Subqueries=T
    Subquery_Column_Alias=T
    Subquery_In_Aggregate=T
    Substring_On_Expr=T
    Order_By_Alias=F
	Olap_Null_Order=T
	Comments=F

[Exceptions General Version:6.20]
	Group_By_Expr=F

[Exceptions Joins]
    Cross=T
    Cross_Syntax=" CROSS JOIN "
    Full_Non_Equi_Join=F
    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=""
    Natural=T
    Natural_Syntax=" NATURAL JOIN "
    Nested_Outer=T
    Non_Equi_Joins=F
    Non_Join_Expr=T
    Non_Join_Non_Equi_Expr=F
    On_Condition_Between_Predicate=F
    On_Condition_In_Predicate=F
    On_Condition_IsNull_Predicate=F
    On_Condition_Like_Predicate=F
    On_Condition_Not_Predicate=F
    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=""
    Two_Sided_Join_Restrict=F
    Union_Syntax=""
    Outer_Non_Equi_Join=F

[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=T
    Date_Literal_Str="'%s-%s-%s'"
    Date_Sub_Function_Str=
	False_Literal_Str=
    Interval_Format_Str=
    Interval_Literal=F
    Interval_Literal_Str=
    IntervalYM_Format_Str=
    IntervalYM_Literal=F
    IntervalYM_Literal_Str=
    Time_Format_Str="HH MM SS FFFFFF"
    Time_Literal=C
    Time_Literal_Str="{t '%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="{ts '%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=

[Exceptions Operators]
    Add="+"
    And="and"
    Div="/"
    Eql="="
    Geq=">="
    Grt=">"
    Leq="<="
    Les="<"
    Mul="*"
    ;Neg="-"
    Neq="<>"
    Not="not"
    Or="or"
    Sub="-"

[Exceptions Predicates]
    All="all"
    Exists="exists "

[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]
    Derived=T
    Joined=T
    Simple=T
	UniqueName_Prefix="coguda"

[Exceptions Value Expressions]
    Bit_Length=F
    Case=T
    Cast=C
    Char_Length=C
    Coalesce=T
    Extract=T
    Lower=T
    Nullif=T
    Octet_Length=C
    Position=C
    StrCat=C
    Substring=C
    Upper=T
    Current_Date=T
    Current_Time=F
    Current_Timestamp=F
    Localtime=C
    Localtimestamp=C
    Abs=T
    Ceiling=C
    Exp=T
    Floor=T
    ln=T
    Sqrt=T
	Trim=F
	Trim_Both_Spaces=C
	Trim_Leading_Spaces=C
	Trim_Trailing_Spaces=C
	Power=C
	Mod=F

[Exceptions Value Expressions Version:6.0]
	Position=F
	
[Exceptions Blob Expressions]
	Blob_In_Substring=T
	Blob_In_Trim=F
	
[Exceptions Misc]
Session_Sort_Order=T	
	
;
; Operations supported and results of the supported operation.
; The format is;
; [Operator <operator name>]
; <LHS>=<RHS>,<RES>{:<RHS>,<RES>}
;
; 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 Addition]
    Date=
    Time=
    Timestamp=
    Interval=

[Operator Comparison]
    Date=DT,OK:TS,OK
    Timestamp=DT,OK:TS,OK
    Time=TM,OK:TS,OK
 
[Operator Subtraction]
    Date=
    Time=
    Timestamp=
    Interval=
    
[Operator Cast]
	NA=AD,NA
	AD=VC,NA:NC,NA:NV,NA:SM,NA:DB,NA:QD,NA:IV,NA:IY,NA:TT,NA:TZ,NA
	TX=AD,AD
	BL=AD,AD


[Misc]

; Default processing mode.
Processing Mode=Database Only

[DATABASE:RED BRICK WAREHOUSE]
; When connecting to Redbrick read the DbInfo entries from
; the od_redbrick ini file with no subtype

;Override some cast functions
DBINFO_SUBSECTIONS="DBNAM"
State Overwrite 0="CCLMSG_UDA_SQL_GENERAL SQL_API_SQLCANCEL WS000 01S05"
Exec_DegreeOfParallelism="SET QUERYPROCS %d"
Exec_RowLimit_Set="SET ROWCOUNT %d"
Exec_RowLimit_Reset="SET ROWCOUNT 0"
System View Supported="F"
System Alias Supported="F"
System Synonym Supported="F"

;Some of the Redbrick drivers do not report this
SQL_IDENTIFIER_QUOTE_CHAR="""
;The database doesn't properly support ltrim/rtrim
;functions because of lacking varchar support
SQL_STRING_FUNCTIONS="6213"
SQL_MAX_SCHEMA_NAME_LEN="30"
SQL_API_SQLFETCHSCROLL="FALSE"


[DATABASE:RED BRICK WAREHOUSE VERSION:06.00]
; When connecting to Redbrick Version 6.0 read the subtype of <Version:6.0>
DBINFO_SUBTYPE="Version:6.0"

[DATABASE:RED BRICK WAREHOUSE VERSION:06.10]
; When connecting to Redbrick Version 6.10 read the subtype of <Version:6.10>
DBINFO_SUBTYPE="Version:6.1"

[DATABASE:RED BRICK WAREHOUSE VERSION:06.11]
; When connecting to Redbrick Version 6.11 read the subtype of <Version:6.11>
DBINFO_SUBTYPE="Version:6.11"

[DATABASE:RED BRICK WAREHOUSE VERSION:06.20]
; When connecting to Redbrick Version 6.20 read the subtype of <Version:6.20>
DBINFO_SUBTYPE="Version:6.20"

[DATABASE:RED BRICK WAREHOUSE VERSION:06.30]
; When connecting to Redbrick Version 6.30 read the subtype of <Version:6.30>
DBINFO_SUBTYPE="Version:6.30"

[DRIVER:RB32ODBC.DLL]
; This driver only supports Red Brick Warehouse
SQL_DBMS_NAME="RED BRICK WAREHOUSE"

[DRIVER:RB32ODBC.DLL VERSION:05.02]
SQL_API_SQLNATIVESQL="FALSE"

[DRIVER:RBODBC32.DLL]
; This driver only supports Red Brick Warehouse
SQL_DBMS_NAME="RED BRICK WAREHOUSE"
;Some of the Redbrick drivers do not report this
SQL_IDENTIFIER_QUOTE_CHAR="""
;The database doesn't properly support ltrim/rtrim
;functions because of lacking varchar support
SQL_STRING_FUNCTIONS="6213"


[I18N Encoding Mapping DATABASE:RED BRICK]
JapanEUC=EUC-JP
MS1250=cp1250
MS1251=cp1251
MS1252=cp1252
MS1253=cp1253
MS1254=cp1254
MS1255=cp1255
MS1256=cp1256
MS1257=cp1257
MS1258=cp1258
MS932=sjis
MS874=cp874
MS54936=gb18030