;*********************************************************************** ;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 ] ; =,{:,} ; ; 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 DBINFO_SUBTYPE="Version:6.0" [DATABASE:RED BRICK WAREHOUSE VERSION:06.10] ; When connecting to Redbrick Version 6.10 read the subtype of DBINFO_SUBTYPE="Version:6.1" [DATABASE:RED BRICK WAREHOUSE VERSION:06.11] ; When connecting to Redbrick Version 6.11 read the subtype of DBINFO_SUBTYPE="Version:6.11" [DATABASE:RED BRICK WAREHOUSE VERSION:06.20] ; When connecting to Redbrick Version 6.20 read the subtype of DBINFO_SUBTYPE="Version:6.20" [DATABASE:RED BRICK WAREHOUSE VERSION:06.30] ; When connecting to Redbrick Version 6.30 read the subtype of 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