;*********************************************************************** ;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/sybase_iq/cogdmod_iq.ini#4 $ ; ; Module: ; cogdmod_iq.ini ; ; Purpose: ; This module contains the Sybase SYBASE IQ information. ; ; Notes: ; Do NOT modify this file. Doing so could result in unknown behavior ; by the ODBC gateway, possibly resulting in application aborts. ; ; ; General subsections of this file are made based on SYBASE ; ADAPTIVE SERVER IQ VERSTION 12. ; ; ************************************************************************ [Database Functions] n:size(%1c)=LENGTH(%1); c:substring(%1c, %2n {, %3n})=SUBSTR(%1, %2 {, %3}); n:ln(%1n)=LOG(%1); n:position(%1c,%2c)=CHARINDEX(%1,%2); n:octet_length(%1c)=BYTE_LENGTH(%1); n:bit_length(%1c)=BYTE_LENGTH(%1) * 8; ;Because the change we made in odsdb.c (Detail see trakker 321842, change 5574 ), ;Functions cast_numberToString(), ;cast_char(), convert_char(), convert_numberToString() are not avaiable, to fix the problem, ;the following functions are added. ; c:cast_numberToString(%1cdinst)=CAST(%1 AS CHAR(254)); c:convert_numberToString(%1cdnst)=\{fn CONVERT(%1,SQL_CHAR)\}; c:convert_char(%1cdnst)=\{fn CONVERT(%1,SQL_CHAR)\}; ; ; Mappings for casting datatypes to other datatypes ; Legend: ; b: boolean ; c: character string ; d: date ; i: interval ; n: number ; t: time ; s: timestamp ; n:cast_decimal(%1n{,%2n,%3n})=CAST(%1 as DECIMAL{(%2,%3)}); n:cast_double_precision(%1n)=CAST(%1 as DOUBLE); n:cast_float(%1n{,%2n})=CAST(%1 as FLOAT{(%2)}); n:cast_integer(%1n)=CAST(%1 as INTEGER); n:cast_smallint(%1n)=CAST(%1 as SMALLINT); n:cast_char(%1cdints{,%2n})=CAST(%1 as CHAR{(%2)}); c:cast_varchar(%1cdints, %2n)=CAST(%1 as VARCHAR(%2)); d:cast_date(%1cds)=CAST(%1 AS DATE); s:cast_timestamp(%1cds)=CAST(%1 AS TIMESTAMP); t:cast_time(%1ct)=CAST(%1 as TIME); c:trim_both_spaces(%1c)=TRIM(%1); c:trim_leading_spaces(%1c)=LTRIM(%1); c:trim_trailing_spaces(%1c)=RTRIM(%1); ;Functions required to cast division of integer/numeric to a floating point n:coguda#cast_division_float(%1n{,%2n})=CAST(%1 as FLOAT{(%2)}); n:coguda#cast_division_double_precision(%1n)=CAST(%1 as DOUBLE); [Exceptions Aggregates] Avg="avg" Count="count" Count_star="count(*)" Max="max" Min="min" ; ravg ; rdiff ; rsum Stddev="stddev" Sum="sum" Variance="variance" ; xmovingavg ; xmovingsum ; xntile ; xrank ; xratio ; xstddev ; xtertile ; xvariance Count_Literal=T [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="stddev_pop" Olap_Var_Pop="var_pop" Olap_Stddev_Samp="stddev_samp" Olap_Var_Samp="var_samp" Olap_Rank="rank" Olap_Dense_Rank="dense_rank" Olap_Percent_Rank="percent_rank" ;COGCQ00651915 ;Sybase IQ percentile_cont and percentile_disc are based on SQL 92 standard. ;UDA needs to do local processing. Olap_Percentile_Cont= Olap_Percentile_Disc= Olap_Ntile="" [Exceptions OLAP Functions DATABASE:SYBASE IQ VERSION:12.60] 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_Percentile_Cont= Olap_Percentile_Disc= Olap_Ntile= [Exceptions OLAP Functions DATABASE:SYBASE IQ VERSION:12.50] 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_Percentile_Cont= Olap_Percentile_Disc= Olap_Ntile= [Exceptions Clauses] From="from" Group_By="group by" Having="having" Qualify= Where="where" With= With_Recursive= [Exceptions Commands] Call="call " Delete="delete " Insert="insert " Select="select " Update="update " Max_Stmt_Len= [Exceptions Delimiters] Catalog_Delimiter="\"" Schema_Delimiter="\"" Table_Delimiter="\"" Column_Delimiter="\"" Procedure_Delimiter="\"" Wholename_Delimiter="" [Exceptions General] Aggregate_In_Expr=T Aggregate_Value_Expr=T Olap_In_Subquery=F Boolean_Comparison=F Correlated_Subqueries=T Count_Blob=F Count_Non_Distinct=T Count_Value_Expr=T Cross_Product=T Current_Date=C Current_Time=F Current_Timestamp=F Localtime=C Localtimestamp=C Distinct_Aggregates=T Group_By_Expr=T Is_Null_Value_Expr=T Is_Null_Value_Parm=T Like_Value_Expr=T Multiple_Distinct=T ;Null=T Nulls_Sort_Last=F ;Union doesn't allow sort by name Order_By_Name=F Order_By_Alias=F Substring_On_Expr=T Subqueries=T Subquery_Column_Alias=F Subquery_In_Case=F ; Trakker #307169 ; Sybase IQ 12.5 only supports one subquery in the having clause. ; This is the error message return by Sybase IQ 12.5: ; General error: A maximum of one subquery predicate per conjunct is supported in this release. Subquery_In_Having=F ;cannot specify DISTINCT in window functions Olap_Distinct=F ; Constant expression is not supported in partition, order by clauses Constant_In_Olap_Window=F ; CQCOGCQ00873398. Order by without window frames raise error ; [Sybase][ODBC Driver][Sybase IQ]Syntax error - Order By key must be a numeric data in RANGE Window Frame. Olap_Window_Order_By=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=T One_Outer=F 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 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="" [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="{d '%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" Time_Literal=C Time_Literal_Str="{t '%s:%s:%s'}" TimeTZ_Format_Str= TimeTZ_Literal=F TimeTZ_Literal_Str= Timestamp_Format_Str="YYYY MM DD HH MM SS" Timestamp_Literal=C Timestamp_Literal_Str="{ts '%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 Literals DATABASE:SYBASE IQ VERSION:11.05] ; Entries added to overwrite corresponding flags ; in the Sybase database information file. Date_Literal=C Date_Format_Str="MMM DD YYYY" Date_Literal_Str="convert(datetime,'%s %s %s')" Time_Literal=C Time_Format_Str="HH MM SS FFF" Time_Literal_Str="'%s:%s:%s:%s'" [Exceptions Operators] Add="+" And="and" Div="/" Eql="=" Geq=">=" Grt=">" Leq="<=" Les="<" Mul="*" ;Neg="-" Neq="<>" Not="not" Or="or" Sub="-" [Exceptions Predicates] All="all " Any="any " Exists="exists " [Exceptions Predicates DATABASE:SYBASE IQ VERSION:12.50] All= Any= [Exceptions Predicates DATABASE:SYBASE IQ VERSION:12.40] All= Any= [Exceptions Separators] Catalog_Separator="." Schema_Separator="." Table_Separator="." [Exceptions Set Operators] Distinct="distinct" Except= Except_All=F Intersect= Intersect_All=F Union="union" Union_All=T [Exceptions Tables] ;Trakker #521422 ;Sybase IQ does has problem about derived table for 12.60 and they claim that the problem will get fixed by 12.70. Derived=T Joined=F Simple=T UniqueName_Prefix="coguda" Tbl_Ref_Limit="512" [Exceptions Tables DATABASE:SYBASE IQ VERSION:12.60] Derived=F Tbl_Ref_Limit="128" [Exceptions Tables DATABASE:SYBASE IQ VERSION:12.50] Derived=F Tbl_Ref_Limit="128" [Exceptions Tables DATABASE:SYBASE IQ VERSION:12.40] Derived=F Tbl_Ref_Limit="128" [Exceptions Value Expressions] Abs=T Bit_Length=C ;Trakker 276769 fix set case=F since SYBASE IQ 12.03 raises "Internal Error" ;if searched case is used in a subquery. ;Change case =T, Case statements are pushed down to database against Sybase IQ 12.4.2 fix ;trakker 315198. Retested test cases for trakker 276769,subquery case statements ;are pushed down to database properly. ;Trakker 479268, Sybase IQ failed to convert from SQL_C_CHAR to SQL_NUMERIC with error "Data conversion failed" ;This problem prevents UDA to retrieve a blob from database Case=T Case_Simple=T Cast=T Ceiling=T Char_Length=T Coalesce=T Exp=T Extract=C Floor=T Lower=T Ln=C Mod=T Nullif=T Octet_Length=C Position=C Power=T Sqrt=T StrCat=T Substring=C Upper=T Trim=F Trim_Both_Spaces=C Trim_Leading_Spaces=C Trim_Trailing_Spaces=C ; ; Extract=C, above, indicates that SQL'92 EXTRACT functionality is [partially] ; supported by Sybase IQ through the use of the following function maps. ; ; EXTRACT( YEAR FROM )' maps to coguda#extract_year( ). ; 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 [Exceptions Tables DATABASE:SYBASE IQ VERSION:12.50] Coalesce=F [Exceptions Tables DATABASE:SYBASE IQ VERSION:12.40] Coalesce=F [Operator Addition] Date= Time= Timestamp= Interval= [Operator Subtraction] Date= Time= Timestamp= Interval= [Builtin Functions] n:_round(%1n,%2n)=ROUND(%1, %2); d:current_date()=CURRENT DATE; t:localtime({%1n})=CURRENT TIME; s:localtimestamp({%1n})=CURRENT TIMESTAMP; n:ascii(%1c)=ascii(%1); n:round(%1n,%2n)=round(%1,%2); ; ; Business Functions for Date Calculations ; s:_add_days(%1s,%2n)=DATEADD( DAY, %2, %1 ); d:_add_days(%1d,%2n)=DATEADD( DAY, %2, %1 ); s:_add_months(%1s,%2n)=DATEADD( MONTH, %2, %1 ); d:_add_months(%1d,%2n)=DATEADD( MONTH, %2, %1 ); s:_add_years(%1s,%2n)=DATEADD( YEAR, %2, %1 ); d:_add_years(%1d,%2n)=DATEADD( YEAR, %2, %1 ); n:_days_between(%1ds,%2ds)=DATEDIFF( DAY, %2, %1 ); n:_months_between(%1ds,%2ds)=DATEDIFF( MONTH, %2, %1 ); n:_years_between(%1ds,%2ds)=DATEDIFF( YEAR, %2, %1 ); n:_days_to_end_of_month(%1ds)=DATEDIFF( DAY, %1, DATEADD( DAY, -1, DATEADD( MONTH, 1, DATEADD( DAY, 1 - DATEPART( DAY, %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, -DAY( %1 ) + 1, %1 ) ) ); s:_last_of_month(%1s)=DATEADD( DAY, -1, DATEADD( MONTH, 1, DATEADD( DAY, -DAY( %1 ) + 1, %1 ) ) ); s:_make_timestamp(%1n,%2n,%3n)=CAST( YMD(%1, %2, %3 ) AS TIMESTAMP ); n:_ymdint_between(%1ds,%2ds)=; n:_day_of_year(%1ds)=DATEPART( DAYOFYEAR, %1 ); n:_week_of_year(%1ds)=DATEPART( CALWEEKOFYEAR, %1 ); n:_day_of_week(%1ds,%2n)=(MOD(( MOD(( DATEPART( WEEKDAY, %1 ) + 5 ), 7 ) - %2 + 8 ), 7) + 1); ; ; 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 ); ; ; Functions required by EXTRACT. ; n:coguda#extract_year(%1ds)=DATEPART(YEAR, %1); n:coguda#extract_month(%1ds)=DATEPART(MONTH, %1); n:coguda#extract_day(%1ds)=DATEPART(DAY, %1); n:coguda#extract_hour(%1st)=DATEPART(HOUR, %1); n:coguda#extract_minute(%1st)=DATEPART(MINUTE, %1); n:coguda#extract_second(%1s)=DATEPART(SECOND, %1) + (DATEPART(MILLISECOND, %1 ) / 1000.0); n:coguda#extract_second(%1t)=DATEPART(SECOND, %1); [DATABASE:SYBASE IQ VERSION:12.60] ; When connecting to Sybase IQ 12.6 read the subtype of DBINFO_SUBTYPE="Version:12.6" [DATABASE:SYBASE IQ VERSION:12.50] ; When connecting to Sybase IQ 12.5 read the subtype of DBINFO_SUBTYPE="Version:12.5" Convert To Blob Limit="255" [DATABASE:SYBASE IQ VERSION:12.40] ; When connecting to Sybase IQ 12.4 read the subtype of DBINFO_SUBTYPE="Version:12.4" Convert To Blob Limit="255" [DATABASE:SYBASE IQ VERSION:12.00] DBINFO_SUBTYPE="Version:12.0" Convert To Blob Limit="255" [DATABASE:SYBASE IQ VERSION:11.05] DBINFO_SUBTYPE="Version:11.5" [DATABASE:SYBASE IQ VERSION:11.09] DBINFO_SUBTYPE="Version:11.9" [DATABASE:SYBASE IQ VERSION:11.00] DBINFO_SUBTYPE="Version:11.0" [DATABASE:SYBASE IQ] SQL_API_SQLDESCRIBEPARAM="FALSE" SQL_API_SQLSPECIALCOLUMNS="FALSE" ; ; Table Type ; System Views="'SYSTEM VIEW'" DBINFO_SUBSECTIONS="DBNAM,DRVREV" [DATABASE:ADAPTIVE SERVER ANYWHERE] SQL_DBMS_NAME="SYBASE IQ" [DATABASE:ADAPTIVE SERVER IQ] SQL_DBMS_NAME="SYBASE IQ" ; ; 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 ; TEXT TX sqlText ; BLOB BL sqlBlob ; ALL DATATYPES AD ------------- ; ---- NA sqlUnknownType ; [Operator Comparison] DT=DT,OK:TS,OK TS=DT,OK:TS,OK [Operator Cast] AD=NC,NA:NV,NA:IV,NA:TT,NA:TZ,NA [Directives Session] ; ; Row Limit in Sybase IQ Server is achieved by adding ; the following line: TOP n right after the first SELECT or SELECT DISTINCT ; ; The Row Limit in Sybase IQ Server is ; supported from version 12.5 and up ; Query_Row_Limit_Pos=Append_Distinct Query_Row_Limit_Text=" TOP %d " [Directives Session DATABASE:SYBASE IQ VERSION:12.40] Query_Row_Limit_Pos= Query_Row_Limit_Text=