Contents

• Overview
• Tests Performed
• Setup and Configuration
• Test Notes
• Issues and Incompatibilities
• Test Environment
• Other Sources of Information
• Vendor Contact Information

Overview

This section describes each of the products tested and how they were used during testing. This report documents only the platforms and products tested for certification. Other possible combinations of these products and platforms are not certified by Sybase. For more information about Sybase IQ, including known issues not encountered during this certification or those occurring on other platforms, see the Release Bulletin for the specific platform and version. For more information about any of the products, please refer to the product literature or manufacturer website.

This report details the certification of user-defined functions (hereafter referred to generically as 'UDFs') from Fuzzy Logix, LLC. For a short overview of the Sybase IQ User-Defined Function feature, please refer to the previous certification report: Fuzzy Logix DB Lytix 1.0 UDFs running within Sybase IQ 15.3 (Report 363).

Using the Sybase IQ UDF API, Fuzzy Logix has developed a set of UDFs to simulate various types of statistical distributions, perform mathematical computations, summarize groups of data and perform various data mining operations. The UDFs currently available from Fuzzy Logix are DB Lytix version 1.1 (hereafter called 'DB Lytix', unless specifically called out by version below). For more details on the UDFs available within DB Lytix, refer to the DB Lytix User Manual.

DB Lytix 1.1 consists of a set of scalar UDFs (which produce an output result for each input value), single-output aggregate UDFs (which summarize a collection of input values to produce a single output result), and Table Parameterized Functions (hereafter referred to as 'TPFs'), which operate on table input and produce a result set output. See the 'List of UDFs Certified' below for a specific list of the UDFs that are certified in this report.

The TPFs are new to version 1.1 of DB Lytix. This flavor of UDF creates a multiple-output result set, and is invoked differently than a simple scalar or aggregate UDF.

Examples of the SQL invocation syntax for the two types of UDFs is as follows:

Scalar and Aggregate UDFs - SQL invocation pattern:

select

[ column_1, ... ]
scalar_or_aggregate_UDF ( scalar_1 | column_1 [, [ scalar_2 | column_2 ] ... ] )
[ ... , column_N ]

from source table;

Table UDFs (scalar input values only):

select

result_col_1 [ , result_col_2, ... , result_col_N]

from Table_UDF ( scalar_value_1 [ , scalar_value_2, ... ] );

TPFs (can accept a single input table as input as well as several scalar input values):

select

result_col_1 [ , result_col_2, ... , result_col_N]

from TPF ( table ( table_spec ) [ , scalar_value_1 ... ] );

where

table_spec is a result set obtained via SQL (e.g. select * from input_table)

Note the placement of the UDF/TPF within the SQL statement. The scalar and aggregate functions appear along with other scalar and/or columns within the table query, and operate on those columnar or scalar values. The Table UDFs or TPFs appear where a table or view would be within a SQL statement, and are 'selected from'.

Some of the DB Lytix TPFs were originally implemented as file-based columnar functions in earlier versions of DB Lytix, and were previously outside of the scope of certification.

For a more in-depth description of the differences between the various types of UDFs see the latest version of the Sybase IQ User Defined Functions Guide (available at http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01034.1540/doc/html/title.html), or the older Sybase technical white paper: User-Defined Function Development for Sybase IQ and RAPStore - Best Practices Guide (available at http://www.sybase.com/detail?id=1066612).

To view the official "Sybase Inc. Trademarks" regarding trademark and registration information on Sybase and third-party products, click here (or point your browser to: http://www.sybase.com/detail?id=1011207).

Components

DB Lytix 1.1 is currently only supported on Linux 64-bit and Windows 64-bit platforms, with other platforms * to be made available on an as-needed basis. DB Lytix was certified running within Sybase IQ 15.4. For this certification, all software components involved in the test were located on the same machine:

• The Linux certification was performed on a Red Hat Enterprise 5 (64-bit) Linux workstation.
• The Windows certification was performed on a Windows Server 2003 - Enterprise x64 Edition, Service Pack 2.

See the section "Test Environment" for additional details about the certification environment.

• Sybase IQ version 15.4 (hereafter called 'Sybase IQ') served as the database server hosting the DB Lytix UDFs (described below). The Sybase IQ instance and subsequent software described below were all installed on the same machine.
• The Sybase IQ 15.4 "In-Database Analytics" (IQ_IDA) license was obtained and installed, along with an Enterprise (IQ_CORE) CPU-style un-served license.
• A SQL client was invoked from the same machine. The SQL client was used to submit queries against Sybase IQ that incorporated DB Lytix UDF calls. The SQL client used for the majority of this certification was Interactive SQL Java Edition, version 12.0.1 (hereafter referred to as 'Interactive SQL', 'DBISQL' or 'dbisql'). This client and other tools are delivered as part of Sybase IQ.
• DB Lytix UDFs from Fuzzy Logix were installed into the Sybase IQ environment on the same machine as Sybase IQ and the SQL client. The DB Lytix package from Fuzzy Logix consists of:
  • Two documents: an Installation and Administration Guide and a User Manual. These are located in the 'Docs' directory of the installation media.
  • A set of scripts to drop the function declarations and unload the libraries (used during upgrade and uninstallation). These are located in the 'DropRegistration' directory of the installation media.
  • A set of external libraries ('dynamic link libraries' or .DLL files). These libraries contain the executable DB Lytix UDFs, and are located in the 'LIB' directory of the installation media.
  • A set of registration scripts (.sql files) which define the DB Lytix SQL Functions or Stored Procedures. Each of the DB Lytix SQL Functions define a scalar or aggregate column-based UDF. Each of the DB Lytix SQL Stored Procedures define a table-based TPF. Each of the SQL wrapper Functions and Stored Procedures reference the DB Lytix UDF execution code contained in an external library. The DB Lytix registration scripts are located in the Registration directory of the installation media

* Note: The previous version of DB Lytix 1.0 is also supported on other platforms, running with previous releases of Sybase IQ. DB Lytix 1.0 was previously certified with:

1. Sybase IQ 15.3 running on the following platforms: AIX, Linux (RedHat and SUSE), Solaris (SPARC and x86) and Windows. See Sybase Partner Certification Report 363, available on sybase.com at URL: http://www.sybase.com/detail?id=1093454
   
   
2. Sybase IQ 15.2 running on three platforms: AIX, Solaris SPARC and Solaris x86. See Sybase Partner Certification Report 341, available on sybase.com at URL: http://www.sybase.com/detail?id=1080410.
   
   
3. Sybase IQ 15.1 running on Windows and Linux (RedHat and SUSE). See Sybase Partner Certification Report 321, available on sybase.com at URL: http://www.sybase.com/detail?id=1065214.

Refer to the Fuzzy Logix documentation or web-site for the latest list of supported platforms (see the section "Vendor Contact Information" for links).

Component Layout

The following diagram illustrates the layout of the Fuzzy Logix DB Lytix libraries in relation to the Sybase IQ server. The layout is identical in each of the test environments. For detailed information about the certification environment, see the section Test Environment later in this report.

Back to Contents

Tests Performed

This section contains a detailed explanation of the tests performed.

• Performed the following types of tests against the DB Lytix UDFs:
  • Positive functionality - Executed UDFs with the proper number of parameters, within the range supported by each UDF.
  • Executed the following types of negative test cases to determine how well the UDFs catch errors in parameters, or invalid data:
    • specified parameter values outside of the range supported by the UDFs.
    • specified an incorrect number of parameters to the UDFs.
    • attempted to execute UDFs against ordered lists or matrices with missing elements.
    • data handling - invoked UDFs against data that contained nulls.

    See Test Notes in section 4 below for common SQL error messages and UDF Exceptions reported by DB Lytix UDFs, and how to trouble-shoot these errors

List of DB Lytix UDFs Certified

For detailed descriptions of the following UDFs, please refer to the DB Lytix User Manual.

Scalar column-based UDFs which return a single result value for each input row

Note: Scalar UDFs operate on one or more columns from a single row of input.

Aggregate column-based UDFs which return a single result

Note: Aggregate UDFs (a.k.a UDAFs) operate on and summarize data in column(s) coming from multiple rows of input.

** The aggregate UDFs marked with two asterisks above performed properly both during the license grace period and also after the license grace period using a valid Sybase IQ In-Database Analytics license. However, when an attempt was made to invoke those same functions beyond the license grace period when there was no Sybase IQ In-Database Analytics license in effect, a database server problem was encountered (for details, see Issue 3).

* * * There were inconsistencies in the behavior of the FLGMean function between platforms. See Test Note 3.6 in section 3 of the Test Notes.

Table UDFs and Table-Parameterized Functions (TPFs) which return multiple result values (as a result set +)

Note: Table UDFs operate on one or more scalar input parameters (possibly column value(s) from a table row) and return a result set +, while Table-Parameterized Functions (TPFs) also return a result set +, but operate on an input result set + in addition to the scalar input parameter(s).

+ A result set consists of one or more rows of data, each organized according to a pre-set schema consisting of one or more columns of values. An example of a result set is the output of selecting one or more columns from a table in the database, which will comprise one or more rows of data.

+ + The FLMTranspose operation is described in the DB Lytix User Manual, but no SQL stored procedure wrapper or external UDF code is provided. This function is implemented as a pure SQL query only, using the SQL directive 'AS' to swap the semantics of RowID with ColID. See the description of the FLMTranspose function in the DB Lytix User manual for details.

+ + + The FLNBPredict and FLNBPreProc functions are implemented as SQL-only stored procedures, and do not invoke external libraries. The FLNBPreProc function is used to set up the input table in preparation for running a Naïve Bayes analysis. A Naïve Bayes model is then generated by invoking the FLNaïveBayes TPF (which does call an external UDF library). Finally, the FLNBPredict function is used to predict values based on the Naïve Bayes model generated by the Naïve Bayes analysis. For details, refer to the DB Lytix 1.1 User Manual.

Note that DB Lytix also contains another data mining function called FLPCAEigen, which is not included in this certification. While there is no reason to believe this function would not work, it was not certified due to a documentation deficiency (see Issue 4 - DB Lytix 1.1 TPF FLPCAEigen not sufficiently documented).

Scope of certification

UDFs and TPFs were analyzed only to ensure their ability to execute within the Sybase IQ environment. The numerical results were generally not analyzed, except in a few specific cases. In some cases using sample data and expected return value(s) as provided in the DB Lytix User Manual, and in other cases invoking a complementary CDF and InvCDF pair of UDFs to ensure the inverse CDF function numerically 'undid' the operation of the CDF function).

Back to Contents

Setup and Configuration

The following information relates to the installation, setup and configuration of Sybase IQ, as well as the installation and registration of DB Lytix.

Setup and Configuration at a glance:

• Install Products
• Create Demo Database
• Configure Sybase IQ to access DB Lytix UDFs
• Grant Permissions to allow non-DBA users to execute DB Lytix UDFs
• Execute a Sample UDF to Verify the Installation

Install Products:

Note: If it is necessary to replace a DB Lytix library, ensure that the library is unloaded from the IQ Server. A shutdown of the IQ Server will automatically unload all external libraries (refer to Test Note 2.1 for details).

1. Install Sybase IQ on the Windows machine which will host the DB Lytix UDFs, according to the product documentation.
   
2. Obtain and install the Sybase IQ "In-Database Analytics Option" (IQ_IDA license) for Sybase IQ. The IQ_IDA license option is required in order to execute DB Lytix 1.1 UDFs within Sybase IQ 15.4. Note that this is a new license type for Sybase IQ starting with release 15.4 (see Test Note 2.2 for details).
   
3. Install DB Lytix (Note that an automated installer does not exist for DB Lytix, so the installation consists of a series of manual steps)
   
   1. Either unload all previously-loaded external libraries from the IQ Server, or shutdown the IQ Server (note - this is important - see Test Note 2.1 for details)
   2. Create a directory which will contain the DB Lytix libraries. By default, on the Windows platform, DB Lytix libaries should be located in the C:\Sybase\UDX\LIB directory.
      Note - See step 3 in the section called Configure Sybase IQ to access DB Lytix UDFs below for changes required, if an alternate location is used for the libraries.
      
   3. Copy the DB Lytix libraries into this directory

   If upgrading from release 1.0 of DB Lytix, it should be noted that the environment variable "UDXResultSetPath", and the folder/directory to which it points, are no longer necessary in version 1.1 of DB Lytix. When upgrading from DB Lytix 1.0 to DB Lytix 1.1, the folder/directory may be deleted to free up disk space, and the environment variable may be removed to clean up the environment settings.

Create Demo Database:

1. Use the scripts provided by Sybase IQ installation to create the default 'iq_demo' database. For details, refer to the Sybase IQ documentation.
2. Increase the size of the MAIN and TEMP dbspaces to at least 500MB, respectively (for instructions on adding space to the Sybase IQ DBSpaces, see Test Note 1.3)

Configure Sybase IQ to access DB Lytix UDFs

In order for Sybase IQ to be able to invoke a UDF contained in an external library, access to the UDF must be defined in a SQL wrapper function. An example of one of these SQL wrapper function definitions for DB Lytix is shown below:

To facilitate the definition of these SQL wrapper functions, DB Lytix delivers a set of Registration scripts (.sql files), one for each UDF library. Some of the UDF libraries contain multiple UDFs. To create the DB Lytix SQL wrapper functions:

1. Verify that the 'iq_demo' database is running on the Sybase IQ Server.

2. Copy the DB Lytix Registration scripts (.sql files) from the installation media to a location accessible from the Sybase IQ client machine.

3. If desired, configure DB Lytix to access the UDF libraries from a location other than the default directory (/opt/Sybase/UDX/LIB), by changing the 'EXTERNAL NAME' clause in each SQL function definition within the Registration scripts to point to the alternate location
   
   
4. Review the DB Lytix Registration SQL scripts to ensure that the UDF names do not conflict with any SQL functions currently defined in the environment. Using the above example, check if there is an existing SQL function with the name 'FLMean', that would conflict with the DB Lytix UDF with the same name. Rename one of these to remove the name conflict (see Test Note 2.3 for details).
   
   
5. Using the 'DBA' database user, execute the desired Registration script(s) to define the DB Lytix SQL wrapper functions to the Sybase IQ server.
   
   
• (Recommended) Invoke each Registration script by specifying it as an option on the command line to the dbisql tool running in 'command-line' mode. See Test Note 2.6 for example syntax and a description of the command-line parameters.
  
  or
  
  
• (Alternately) Open an Interactive SQL client session, by running dbisql in interactive mode. Use the automatic login dialog or SQL -> Connect menu to login to the Sybase IQ server, using Userid: dba and Password: sql (no other connectivity information is required, since the SQL client is running on the same machine as the Sybase IQ server). For each Registration script:
  
  
  • Load the Registration script with the File -> Open menu or by hitting Ctrl-O, navigating to the directory containing the Registration scripts, and opening the the relevant Registration script (.sql file).
  • Execute the script, by selecting the SQL -> Execute option or by hitting the F9 key

Note: The DB Lytix Registration scripts must be executed by a user with DBA authority. See Test Note 2.7 for details..

Grant Permissions to allow non-DBA users to execute DB Lytix UDFs

If all queries will be performed by the DBA user, then no further installation steps are necessary. Skip to the section "Execute a Sample UDF to Verify the Installation".

In a typical multi-user database environment, the database users who will perform the queries will be non-DBA users. In order to allow non-DBA users the ability to execute Fuzzy Logix DB Lytix UDFs, they must be given specific permissions.

Fuzzy Logix has provided a sample script for granting execute permissions to all DB Lytix UDFs and TPFs. The script is called "Grant_All.sql". It is co-located with the registration scripts (which define wrapper functions and stored procedures), in the Registration folder.

For details on how to give a database query user permission to execute one or more of the specific DB Lytix UDFs, see Test Note 2.4.

Execute a Sample UDF to Verify the Installation

This section describes how to verify that Sybase IQ has been configured properly to access the DB Lytix UDFs.

1. Verify that the iq_demo database is running
2. If it is not already running, start Interactive SQL (dbisql), and connect to the iq_demo database
3. Submit a query that contains a UDF invocation. For example:
   
   select FLMax (CustomerID) from Contacts;
   
   
4. Verify that a result is returned. Note that the first time a function is executed, it may take longer to run than subsequent invocations (see Test Note 3.1 for details).
5. To determine the version of the functions being run, see Test Note 3.2

Note: The DB Lytix User Manual provides an example query for each DB Lytix UDF.

Back to Contents

Test Notes

This section contains information gained during testing, which may be helpful.

Index of Test Notes:

1. Production Environment Considerations

Test Note 1.1: Run DB Lytix UDFs in a Read-Only node of a Multi-Plex Server
Test Note 1.2: Test the DB Lytix UDFs with a Safer Execution Mode prior to Production
Test Note 1.3: Expand the size of the TEMP dbspaces in Sybase IQ to accomodate UDF Execution
Test Note 1.4: How to enable full tracing in Sybase IQ
Test Note 1.5: Enable or disable UDF execution on relevant multiplex server nodes

2. Setup and Configuration Notes

Test Note 2.1: Sybase IQ should be shut down during UDF file maintenance
Test Note 2.2: New license type introduced starting with Sybase IQ 15.4
Test Note 2.3: Ensure that the DB Lytix UDF Names do not Conflict with Existing Function Names.
Test Note 2.4: Grant execute permissions to DB query user(s)
Test Note 2.5: SQL Error Message - Procedure 'FL<name>' not found
Test Note 2.6: Configuring Interactive SQL (dbisql) for continued execution on errors
Test Note 2.7: Register UDFs from a Sybase IQ user with dba authority
Test Note 2.8: SA_EXTERNAL_LIBRARY_UNLOAD syntax difference between platforms

3. UDF Execution Behavior

Test Note 3.1: The first execution of a UDF will take longer due to library load
Test Note 3.2: To view the version of DB Lytix UDFs, view the .iqmsg file
Test Note 3.3: Sybase IQ and displays more digits of accuracy than DB Lytix supports
Test Note 3.4: When invoking DB Lytix UDF functions, fully-qualify them by owner
Test Note 3.5: Use temporary tables prior to invoking TPFs
Test Note 3.6: DB Lytix function FLGMean behavior differs by platform

4. SQL Error Messages and UDF Exception Messages

4.1 Messages encountered during setup and configuration

Test Note 2.5: SQL Error Message - Procedure 'FL<name>' not found
Test Note 4.1.1: SQL Error Message - Permission denied: you do not have permission to execute the procedure "FL..."

4.2 Run-time messages related to invalid parameters specified for DB Lytix UDFs

Test Note 4.2.1: SQL Error Message - Missing parameter to function FL<name>
Test Note 4.2.2: SQL Error Message - data type conversion is not possible. Argument N to FL<name> cannot be implicitly converted to varchar
Test Note 4.2.3: DB Lytix Exception - Error: Argument <N> must be <WORD>, ...
Test Note 4.2.4: DB Lytix Exception - CDF is very close to [0 or 1], inverse will result in extreme value

4.3 Run-time messages related to input data conditions - linear input data

4.4 Run-time messages related to input data conditions - two-dimensional input data

Test Note 4.4.1: DB Lytix Exception - Matrix is not correctly formed
Test Note 4.4.2: DB Lytix Exception - An element in the matrix cannot be null
Test Note 4.4.3: DB Lytix Exception - Matrix is not a square matrix
Test Note 4.4.4: SQL Error Message - Function 'FLMProduct' has invalid parameter 'ROWID'('OUT')
Test Note 4.4.5: SQL Error Message - Feature, UNION for TPF input parameter, is not supported
Test Note 4.4.6: DB Lytix Exception - The number of Cols in Matrix 1 must be equal to the number of rows in matrix 2
Test Note 4.4.7: DB Lytix Exception - Error inverting the Heissian matrix
Test Note 4.4.8: DB Lytix Exception - Matrix must have at least one element

4.5 Other anomolies in output

To trouble-shoot other DB Lytix UDF Exception Messages encountered, refer to the documentation regarding the specific DB Lytix UDF in the DB Lytix User Manual, analyze the SQL Function definition for the specific DB Lytix UDF within the DB Lytix Registration scripts, or contact Fuzzy Logix for assistance (refer to Vendor Contact Information below).

1. Production Environment Considerations

Test Note 1.1: Run DB Lytix UDFs in a read-only node of a multiplex server

For optimum safety, it is highly recommended that customers install and run DB Lytix in a Read-Only node of the database. This will minimize the risk of inadvertently modifying data while invoking a DB Lytix UDF within a query. When running in a multiplex configuration, each node against which queries will be invoked must be configured to enable UDF execution. See Test Note 1.5 for instructions on how to enable or disable UDF execution.

Test Note 1.2: Test the DB Lytix UDFs with a safer execution mode prior to production

To catch some of the more serious API errors, it is recommended that users set the Sybase IQ option 'external_udf_execution_mode' to 2 while testing the DB Lytix UDFs in a debug environment. For increased performance, this setting should be reduced back to 1 or 0 at the completion of testing, before the UDFs are moved into a production environment.

Test Note 1.3: Expand the size of the TEMP dbspaces in Sybase IQ to accomodate UDF Execution

Some of the more complex DB Lytix aggregate UDFs require substantial temporary space in order to perform the analysis. Sybase recommends that the TEMP dbspace be increased in order to provide sufficient room for the DB Lytix UDFs to execute.

To expand the TEMP dbspace in Sybase IQ, execute the following command from an Interactive SQL (dbisql) session:

ALTER DBSPACE IQ_SYSTEM_TEMP 
      ADD FILE iqtemp2 '<path_and_name_of_new.iqtmp file>' 
      SIZE 500 mb RESERVE 100 mb

Note: There are several stored procedures available in Sybase IQ for managing dbspace (see the "New Feature Guide" delivered with Sybase IQ for details). For example, the stored procedure "sp_iqfile" can be used to determine the size of the dbspace device files.

Test Note 1.4: How to enable full tracing in Sybase IQ

Consider turning on full tracing within Sybase IQ in a debug environment. To enable IQ tracing, add the following flags to the Sybase IQ server startup command line or the Sybase IQ config file:

-zr all -zo filename

where filename is the path and filename of the tracing output file.

Test Note 1.5: Enable or disable UDF execution on relevant multiplex server nodes

To execute queries which invoke external UDF libraries on an IQ server, the relevant server must be configured to allow external procedures. By default, external procedures are enabled on non-multiplex instances of Sybase IQ (simplex servers). By default, external procedures are disabled for all nodes participating in a Sybase IQ multiplex.

To disable the execution of external procedures on a particular instance / node of Sybase IQ, the administrator may specifiy the following 'stop feature' (sf) flag in the startup command or configuration file of the instance / node:

-sf external_procedure_v3

To enable the execution of external procedures, the administrator may specifiy the following 'stop feature' (sf) flag in the startup command or configuration file of the node to be enabled for UDF queries:

-sf -external_procedure_v3

Note the somewhat cryptic 'double-negative' syntax used when enabling UDF queries. This is not a misprint. Think of reading the syntax to enable a feature as "stop feature NOT {feature name}" or in more plain English: "Don't stop feature {feature name}" (i.e. "allow feature {feature name}").

It is recommended that UDF queries / external procedures be disabled on any R/W nodes in a multiplex configuration, and that UDF queries / external procedures be disabled on the coordinator node in a multiplex. By default, UDF execution is disabled on ALL multiplex nodes unless an administrator has specifically enabled this feature.

In multiplex configurations, it is recommended to restrict the ability to invoke UDF queries / execute external procedures to nodes that are configured as Read-Only nodes in the multiplex. By default, UDF execution is disabled on ALL multiplex nodes. Hence, UDF execution must be specifically enabled on a Read-Only node by an administrator by using the 'stop feature' flag as documented in the enable instructions above.

2. Setup and Configuration Notes

Test Note 2.1: Sybase IQ should be shut down during UDF file maintenance

As with any application which employs external libraries, it is very important that the application not be attempting to access an external library while the associated library file is being moved or overwritten. See the DB Lytix Installation and Administration Guide for instructions for updating or deleting the library files.

If it is not possible to shutdown Sybase IQ, then it is critical that all external libraries be unloaded during maintenance. In order to ensure that external libraries are not inadvertently reloaded after an unload, the corresponding SQL functions should first be dropped (or execution permission revoked) prior to unloading the libraries referenced by those functions.

On most platforms, there are SQL scripts provided in the DB Lytix "DropRegistration" folder to perform these tasks. Run the 'DropAll.sql' script first to drop all of the SQL functions, then run the UnloadAll.sql script to unload any DB Lytix libraries that may be loaded into Sybase IQ.

SA_EXTERNAL_LIBRARY_UNLOAD may not unload a UDF library

Reason for unloading external UDF libraries from Sybase IQ prior to maintenance

On Unix platforms, there is no protection at the operating-system level to prevent users from manipulating files that are in use. Moving, overwriting or deleting a UDF library while it is loaded in Sybase IQ will most likely cause the IQ Server to abort.

Hence, it is highly recommended that alll UDF libraries be unloaded from the Sybase IQ server before attempting to perform UDF library file maintenance.

Windows handling of library files that are in use

In the Windows environment, the operating system may prevent the user from deleting or modifying an external library that is currently in use. For example, if the SimUnivariate.dll file is currently loaded by the Sybase IQ server, then the following action:

should result in an error message similar to the following:

Note that if you receive this message after attempting to unload the library file, it may be due to the fact that an incorrect syntax was provided to the SA_EXTERNAL_LIBRARY_UNLOAD command. See Test Note 2.8 for details on an inconsistency between Windows and other platforms regarding this command.

Test Note 2.2: New license type introduced starting with Sybase IQ 15.4

In release 15.1, 15.2 and 15.3, the license required to run UDFs in Sybase IQ was called "Partner Solutions Option", and had a license mnemonic of 'IQ_UDF'.

Starting with release 15.4 of Sybase IQ, the "In-Database Analytics Option" is required to run UDFs. This license has a mnemonic of 'IQ_IDA'.

Test Note 2.3: Ensure that the DB Lytix UDF names do not conflict with existing SQL function names.

Prior to registering the DB Lytix UDFs, ensure that none of the functions that exist in the Sybase IQ server have the same names as the DB Lytix UDFs about to be installed. If any of the function names match DB Lytix UDF names, then the DB Lytix Registration SQL scripts will delete and overwrite the existing functions with DB Lytix UDFs.

To avoid overwriting existing SQL functions, perform one of the following:

• rename the existing SQL functions in the environment,
  or
• edit the relevant DB Lytix Registration script(s) and rename any conflicting DB Lytix UDF names prior to running the Registration script(s)

Test Note 2.4: Grant execute permissions to DB query user(s)

Note: The following instructions are not included in the DB Lytix Installation and Administration guide, but it is recommended that DBAs perform this additional procedure when installing DB Lytix into a multi-user environment.

Use the following procedure to grant execute permission on one or more specific DB Lytix UDFs to a given DB user.

1. Ensure that the Sybase IQ server is running
   
   
2. Start the Sybase IQ administration utility called Sybase Central (e.g. on Windows Server 2003, select "Start -> All Programs -> Sybase -> Sybase IQ 15.4 -> Sybase Central v6.1")
   
   
3. From Sybase Central, login to the Sybase IQ server as the DBA user (userid: dba / password: sql)
   
   
4. By default, the first time Sybase Central is opened, it will appear with the 'Tasks' view. Change to the 'Folders' view by selecting 'View -> Folders' from the upper menu.
   
   
5. Expand the tree-view associated with the database instance (e.g. the items underneath the 'iqdemo - DBA' line in the tree-view)
   
   
6. Scroll down and expand the category 'Users & Groups'
   
   
7. In the tree-view at the left-hand-side, select the database user that will need to access the DB Lytix UDFs (e.g. the 'BROWSER' user). In the right-hand side of the Sybase Central window, select the 'Procedure Permissions' tab:
   
   
   
8. In the Procedure Permissions area, right-click and select 'New -> Permissions...'
   
   
   
   
9. In the 'New Permissions' window that pops up, click on the desired DB Lytix function to grant permission for the DB user to execute that function.
   
   Notes:
   
   
• To select multiple functions at a time, use Ctrl-click for each function desired.
  
  
• To select ranges of functions, click on the first DB Lytix function desired in the alphabetized list (e.g. "FLABS"):
  
  
  Scroll down to the last DB Lytix function in the alphabetized list (e.g. "FLWVar"), and shift-click on it to select all of the DB Lytix functions in the range:
  
  
  
10. Click on 'OK' to add the execute permission to the desired DB Lytix function(s). The newly-added permissions will be highlighted with an asterisk / star in the 'Procedure Permissions' view:
    
    
    
11. IMPORTANT: Click on the next DB userid (e.g. 'DBA' user):
    
    
    
12. In the dialog that pops up, select 'Yes' to save the changes to the permissions for the 'BROWSER' user.
    
    Note: If you don't click somewhere else after setting permissions for the 'BROWSER' user, then the permission changes just added will not be 'committed' to the database.
    
    
13. Repeat steps 7 through 12 for each DB user that will need to invoke DB Lytix UDFs in their queries

To remove permissions from a user who should not be allowed to execute one or more UDFs:

1. Go to the Procedure Permissions tab for the user
   
   
2. Select the UDF or UDFs to be disallowed by the user
   
   
3. Right-click and select 'Revoke Execute Permission':
   
   
   
4. Make sure to click on another user after revoking the permissions, so that the permissions change is 'committed' to the database.

Test Note 2.5: SQL Error Message - Procedure 'FL<name>' not found

Problem: When an attempt is made by a non-DBA user to invoke a DB Lytix UDF function, the function cannot be found. The Sybase IQ server returns an error that looks something like this:

Cause: This error may occur if the DB Lytix Registration scripts were modified to create the functions under the DBA user (vs. dbo). Later, when a non-dba user issues a query containing a DB Lytix UDF, the function will not be visible to non-dba users.

Resolution: When invoking DB Lytix UDF functions, they may need to be fully-qualifed by owner. See Test Note 3.4 for details.

Test Note 2.6: Configuring Interactive SQL (dbisql) for continued execution on errors

To reduce the amount of user interaction required while registering DB Lytix UDFs, one of two methods can be employed.

1. Run the script in command-line mode, with the '-onerror continue' parameter.
   
   For example:
   dbisql -c "UID=dba;PWD=sql" -d1 -nogui -onerror continue BasicStats.sql
   
   The following is an explanation of the options used for the 'dbisql' command above:
   • -c "UID=dba;PWD=sql" (this option relays connection parameters to dbisql)
   • -d1 (this option tells dbisql to echo each line to the terminal as it executes the script)
   • -nogui (this option tells dbisql to run in command-line mode, vs. interactive mode)
   • -onerror continue (this option tells dbisql to continue to execute when it encounters an error, instead of stopping on the first error)
   
   - or -
   
   
2. If running in interactive mode, change the setting that dictates how the tool will handle errors:
   
   
   1. Open the settings dialog by selecting 'Tools -> Options...':
      
      
      
      
   2. In the General area of the options, locate the setting for "When an error occurs:". The default setting is 'Prompt'. This setting, along with the 'Notify and continue' setting, will display a dialog after every SQL error.
      The 'Prompt' setting allows the user to decide whether to abort or continue after each error, while the 'Notify and continue' setting simply asks the user to acknowledge each error before proceeding.
      The 'Continue' setting will not stop after each error, but will log the errors encountered in the 'Results' pane of the tool. It is recommended to use this option while registering the DB Lytix UDFs, as this will eliminate a mouse click for each function registered.
      Select the 'Continue' option:
      
      
      
      After making this change to the setting, the errors that result from invoking a SQL script will show up in the 'Results' pane of the Interactive SQL (dbisql) tool, rather than generating a dialog window for each and every error:
      
      
      Compare this error with the dialog example provided in Test Note 4.2.2.

Test Note 2.7: Register UDFs from a Sybase IQ user with dba authority

A Sybase IQ user must have dba authority in order to create SQL functions which invoke external UDF libraries. Hence the DB Lytix registration scripts (which create SQL functions which invoke the DB Lytix UDF libraries) must be invoked by a Sybase IQ userid with dba authority.

Test Note 2.8: SA_EXTERNAL_LIBRARY_UNLOAD syntax difference between platforms

On AIX, Linux, Solaris and other Unix-type platforms, when unloading libraries, the library extension (.so) is specified for the Sybase IQ command 'SA_EXTERNAL_LIBRARY_UNLOAD'. However, when unloading libraries on the Windows platform, the library extension (.dll) should NOT be specified. This syntax inconsistency between platforms is logged as Change Request # 590437 in the Sybase internal tracking system.

After a Windows UDF library has been loaded (by the execution of a UDF query), the library will not be unloaded if the '.dll' extension is provided in the parameter for the SA_EXTERNAL_LIBRARY_UNLOAD command:

Cause: These types of errors occur because the syntax for the Sybase IQ command SA_EXTERNAL_LIBRARY_UNLOAD on Windows requires just the library name without the file extension.

Resolution: In order to unload a library on the Windows platform, simply specify the library name, without the '.dll' file extension, as the parameter to the Sybase IQ command SA_EXTERNAL_LIBRARY_UNLOAD. Verify this syntax in the DB Lytix 'Registration' and 'DropRegistration' scripts.

3. UDF Execution Behavior

Test Note 3.1: The first execution of a UDF will take longer due to library load

The Sybase IQ server will not load the library containing the UDF code until the first time the UDF is invoked. The first execution of a DB Lytix UDF residing in a library that has not yet been loaded may be unusually slow:

When the same DB Lytix UDF or another DB Lytix UDF residing in the same library is subsequently executed, it will not need to reload the library.

Test Note 3.2: To view the version of DB Lytix UDFs, view the .iqmsg file

In order to determine what version of the DB Lytix UDFs are executing, open the IQ Message file (.iqmsg) within the database directory.

Look for the string "DB Lytix" to determine the version of the UDFs that are running. Each DB Lytix UDF will log a version string to the IQ Message Log when it is invoked. The first message in the example log below (containing the string "DB Lytix, ver 1.0" from a previous release of DB Lytix), was logged by the DB Lytix UDF code.

If full logging is enabled, there may be a second entry logged by the IQ server, which will contain the results returned by the UDF. Refer to the second message in the example log below.

For example, after a successful completion of a DB Lytix UDF, there will be one or two log entries, similar to following:

Note that only the first log entry ("DB Lytix, ver 1.x") will be logged by default, unless additional logging is enabled in the Sybase IQ server (the second entry is logged by the Sybase IQ server itself, and not the DB Lytix UDF).

Notes:

1. The DB Lytix 1.0 ESD1 UDFs will also log the string: "DB Lytix, ver 1.0" (i.e. there is no differentiation in the log between DB Lytix 1.0 pre-ESD1 and ESD1 UDFs).
2. The DB Lytix 1.1 UDFs will log the string: "DB Lytix, ver 1.1". Some of the DB Lytix 1.1 UDFs will also log the function or library name (but not all).

See also Issue 1: DB Lytix UDFs do not perform comprehensive logging.

Test Note 3.3: Sybase IQ displays more digits of accuracy than DB Lytix supports

DB Lytix UDFs support numbers accurate to at least 10 decimal places and up to 15 decimal places in some cases. Refer to the DB Lytix User Manual for details on precision and performance.

Sybase IQ stores and maintains 15 decimal places of accuracy, while DB Lytix sometimes stores 10 decimal places. Some results displayed by Sybase IQ may be slightly different than expected. The differing value will exist in the 11th decimal place or beyond. Sybase IQ will display a very small fractional difference where an exact integer may be expected:

To avoid seeing these small fractional differences, it is suggested that the query include the appropriate rounding function to the required accuracy. In the above example, if precision to one ten-millionth (.0000001) is required in the fourth column of the result, specify the Sybase IQ 'ROUND' function with a '7' parameter as follows:

select ROUND ( FLInvCDFBeta ( FLCDFBeta ( 4, 3, 9, 1.4, 5.6 ), 3, 9, 1.4, 5.6 ), 7 ) from Customers C;

Test Note 3.4: When invoking DB Lytix UDF functions, fully-qualify them by owner

After installing DB Lytix libraries and registering the functions, the user may still see an error similar to the following when they attempt to invoke a DB Lytix UDF via a SQL query:

Cause: The DB Lytix UDF name is not within the namespace domain of the querying user.

Resolution: When invoking DB Lytix UDFs via SQL queries, the DB Lytix UDF must be fully qualified with the owner of the UDF, unless the user invoking the SQL query is also the owner of the UDF. For example:

select dba.FLCDFBeta ( 4, 3, 9, 1.4, 5.6) from iq_dummy;

Note: The querying user may also need to be given 'DBA' authority and/or execute permissions to the specific DB Lytix UDFs. For more details, see Test Note 2.4.

Test Note 3.5: Use temporary tables prior to invoking TPFs

Release 15.4 has a limitation of using only one simple result set within the input parameters of a TPF. To get around this limitation, merge multiple input tables together into a temporary table prior to invoking the TPF against a single result set representing the contents of the temporary table.

For example, the following sequence of commands would implement matrix multiplication, using the DB Lytix TPF FLMProduct:

Note: In the query above, matrix D is a 2 x 5 matrix, while matrix A is a 5 x 5 matrix. Using the where clauses to create the temporary table reduces the size of the second matrix to 5 x 2. Multiplying these matrices together in this order produces a 2 x 2 result. Multiplying these sub-matrices in the other order (multiplying A first as MatrixID 1 with D second as Matrix ID 2) would produce a 5 x 5 resulting matrix.

Test Note 3.6: DB Lytix function FLGMean behavior differs by platform

On the Windows platform, the FLGMean function does not operate against data that contains NULL values. If an attempt is made to invoke the function against data that contains NULL values, DB Lytix will generate the message "Input must be positive" (see Test Note 4.3.3 DB Lytix Exception - Input must be positive for details).

On the Linux platform, the FLGMean function does operate against data that contains NULL values, and reports a result.

4. SQL Error Messages and UDF Exception Messages

In addition to error messages previously described, the following are some additional examples of SQL Error messages and UDF Exceptions generated by various DB Lytix UDFs when invalid number, type or values of parameters are specified, or when the input data is not sufficient to perform analysis.

Notes:

This is not a complete set of errors that the DB Lytix UDFs are capable of catching and reporting, but merely a sampling.

To see more information about a SQL Error that may be encountered while installing and testing DB Lytix, see Test Note 2.5: SQL Error Message - Procedure 'FL<name>' not found in the Setup and Configuration Notes section.

4.1 Messages encountered during setup and configuration

See also: Test Note 2.5: SQL Error Message - Procedure 'FL<name>' not found

Test Note 4.1.1: SQL Error Message - Permission denied: you do not have permission to execute the procedure "FL..."

Problem: When an attempt is made to invoke a DB Lytix UDF, a non-DBA database user receives a 'Permission denied' error that may look something like the following:

Cause: By default, non-DBA users do not have authority to invoke functions created by other users (by default, DB Lytix functions are created under the 'dbo' user).

Resolution: The querying user needs to be given execute permissions to the specific DB Lytix UDF or UDFs. For instructions on how to do this, see Test Note 2.4.

4.2 Run-time messages related to invalid parameters specified for DB Lytix UDFs

Test Note 4.2.1: SQL Error Message - Missing parameter to function FL<name>

Cause: An incorrect number of parameters were specified for the DB Lytix UDF. Note that a DB Lytix exception will also be reported if the values for a given parameter fall outside of the range of values supported by the specific DB Lytix UDF.

Resolution: To determine the correct number of parameters, and their range of values for the specific DB Lytix UDF, refer to the DB Lytix User Manual, check the DB Lytix Registration script which contains the SQL Function definition for this particular UDF, or contact Fuzzy Logix for assistance (refer to Vendor Contact Information below).

Test Note 4.2.2: SQL Error Message - Data exception - data type conversion is not possible. Argument N to FL<name> cannot be implicitly converted to varchar

Cause: A numeric value was specified where a string argument was expected.

Resolution: To determine the type of parameters expected for the specific DB Lytix UDF, refer to the DB Lytix User Manual, check the DB Lytix Registration script which contains the SQL Function definition for this particular UDF, or contact Fuzzy Logix for assistance (refer to Vendor Contact Information below).

Test Note 4.2.3: DB Lytix Exception - Error: Argument <N> must be <WORD>, ...

Cause: An invalid keyword was specified for a string parameter (note that string parameters are case-sensitive).

Resolution: To determine the list of valid keywords recognized by the specific DB Lytix UDF, refer to the DB Lytix User Manual, or contact Fuzzy Logix for assistance (refer to Vendor Contact Information below).

Test Note 4.2.4: DB Lytix Exception - CDF is very close to [0 or 1], inverse will result in extreme value

Cause: As the CDF value approaches 1, the Inverse CDF could be a very large number.

Resolution: Avoid invoking any of the FLInvCDF... UDFs with a CDF parameter value very near to 0 or very near to 1. Refer to the DB Lytix User Manual for a description of precision vs. performance.

4.3 Run-time messages related to input data conditions - linear input data

Test Note 4.3.1: DB Lytix Exception - The number of observations for 1 sample t-Test should be more than 1

Cause: The data provided to the DB Lytix UDF was insufficient to perform the analysis. Other DB Lytix UDFs report different messages indicating that there should be more input data.

Resolution:Add sufficient data to the database, or modify the query to provide sufficient data to the DB Lytix UDF. Refer to the DB Lytix User Manual for details on the data requirements for the particular DB Lytix UDF.

Test Note 4.3.2: DB Lytix Exception - The number of non-null observations in the first and second data series must be greater than 0

Cause: An attempt was made to correlate two sets of data (e.g. using the FLtTest2S UDF), but at least one of the sets was empty

Resolution: Add sufficient data to the database, or modify the query to provide sufficient data to the DB Lytix UDF. Refer to the DB Lytix User Manual for details on the data requirements for the particular DB Lytix UDF, or contact Fuzzy Logix for assistance (refer to Vendor Contact Information below)..

Test Note 4.3.3: DB Lytix Exception - Input must be positive

Cause:An attempt was made to invoke the a function that does not support NULL values against data that contains NULL values.

Resolution: Ensure the input data does not contain NULL values, or eliminate the NULL values using a where clause (e.g. by adding the clause:
' where CustomerID not null ' to the end of the query above).

Test Note 4.3.4: DB Lytix Exception - The number of non-null observations in for KS test must be greater than 4

Cause:An attempt was made to invoke a the DB Lytix FLKSTest1s UDF against a set of data containing insufficient data for analysis.

Resolution: Ensure that the UDF is invoked against a set of data containing at least 5 observations.

4.4 Run-time messages related to input data conditions - two-dimensional input data

Test Note 4.4.1: DB Lytix Exception - Matrix is not correctly formed

Cause:An attempt was made to invoke a matrix DB Lytix UDF against a matrix which is missing an element.

Resolution: All elements within all rows and all columns of a matrix must be defined and cannot be null. Refer to the DB Lytix User Manual for instructions on how to define two-dimensional data (matrices) within relational tables in Sybase IQ, or contact Fuzzy Logix for assistance (refer to Vendor Contact Information below).

Test Note 4.4.2: DB Lytix Exception - An element in the matrix cannot be null

Cause:An attempt was made to invoke a matrix DB Lytix UDF against a matrix in which at least one of the elements contains a null value.

Resolution: All elements within all rows and all columns of a matrix must be defined and cannot be null. Refer to the DB Lytix User Manual for instructions on how to define two-dimensional data (matrices) within relational tables in Sybase IQ, or contact Fuzzy Logix for assistance (refer to Vendor Contact Information below).

Test Note 4.4.3: DB Lytix Exception - Matrix is not a square matrix

Cause:An attempt was made to invoke a matrix DB Lytix function which expects a square matrix against a non-square rectanglular matrix.

Resolution: Certain matrix DB Lytix UDFs will only operate on square matrix input. Refer to the DB Lytix User Manual for descriptions of the matrix DB Lytix UDFs and their associated restrictions on input matrix data, or contact Fuzzy Logix for assistance (refer to Vendor Contact Information below).

Test Note 4.4.4: SQL Error Message - Function 'FLMProduct' has invalid parameter 'ROWID' ('OUT')

Cause:The query above has a syntax error. An attempt was made to invoke the DB Lytix TPF FLMProduct as a column-based UDF, rather than as a TPF. Also, the 'UNION' SQL directive is not currently supported within the specification for the result set input parameter to a TPF (see Test Note 4.4.5).

Resolution: Create a temporary table to use as input to the TPF. See Test Note 3.5: Use temporary tables prior to invoking TPF for details on this alternate syntax.

Test Note 4.4.5: SQL Error Message - Feature. UNION for TPF input parameter, is not supported.

Cause: Although the query above is syntactically correct, Sybase IQ does not currently support the use of the 'UNION' SQL directive within the specification for the result set input parameter to a TPF.

Resolution: Create a temporary table to use as input to the TPF. See Test Note 3.5: Use temporary tables prior to invoking TPF for details on this alternate syntax.

Test Note 4.4.6: DB Lytix Exception - The number of Cols in Matrix 1 must be equal to the number of rows in matrix 2

Cause:An attempt was made to invoke matrix multiplication DB Lytix function FLMProduct using two matrices which are incompatible.

Resolution: Ensure that the matrices are specified in the correct order (Matrix ID 1 is the matrix on the left side of the multiplication operation, while Matrix ID 2 is the matrix on the right side of the multiplication operation). Ensure also that the matrix on the left of the multiplication (Matrix 1) has the same number of columns as the number of rows in the matrix on the right side of the multiplication operation (Matrix 2). Note that a sub-matrix component of a larger matrix may be used, by specifying a SQL WHERE clause against the ColID (columns) of the matrix on the left side of the multiplication operation and/or against the RowID (rows) of the matrix on the right side of the multiplication operation, in order to force the matrix operands into being compatible for multiplication.

Test Note 4.4.7: DB Lytix Exception - Error inverting the Heissian matrix

Cause:An attempt was made to invoke Logistic Regression DB Lytix function FLLogReg with insufficient quantity of data.

Resolution: Ensure that sufficient data exists in the database for running this analysis. For more precise data requirements, contact Fuzzy Logix (refer to Vendor Contact Information below).

Test Note 4.4.8: DB Lytix Exception - Matrix must have at least one element

Cause: An attempt was made to invoke a matrix DB Lytix UDF on an empty matrix input.

Resolution: Refer to the DB Lytix User Manual for instructions on how to define two-dimensional data (matrices) within relational tables in Sybase IQ, or contact Fuzzy Logix for assistance (refer to Vendor Contact Information below).

4.5 Other anomolies in output

Test Note 4.5.1: Unprintable character(s) in results returned by DB Lytix function

Note that the appearance of the unprintable character(s) may vary, depending on the platform on which the client is running and/or the specifics of the input data and/or function involved (see screen-shots below for examples).

Example 1: When an attempt is made to invoke the aggregate column-based DB Lytix function 'FLtTest2S' with only a single element in group 2, an unprintable character is returned for the T_STAT and P_VALUE test statistics:

Example 2: When an attempt is made to invoke the aggregate column-based DB Lytix function 'FLLinReg' with insufficient data, an unprintable character is returned for some of the statistics:

Cause: The FLtTest2S requires at least two elements in the second group in order to generate valid T_STAT and P_VALUE test statistics. Linear Regression also requires a sufficient amount of data to perform the analysis. Other functions may also have specific input data requirements beyond what is documented in the DB Lytix 1.1 User Manual.

Resolution: Add at least one additional data point to group 2 for the 'tTest2S' analysis. Ensure that sufficient and appropriate input data is specified for linear regression analysis or other analysis functions being invoked.

Back to top of Test Notes | Back to Contents

Issues and Incompatibilities

This section contains information gained during testing, which may be helpful.

Index of Issues:

Issue 1: DB Lytix UDFs do not perform comprehensive logging
Issue 2: DB Lytix UDFs do not support user cancellation
Issue 3: Problems with certain DB Lytix 1.1 Aggregate UDFs when no license in effect
Issue 4: DB Lytix TPF FLPCAEigen not sufficiently documented

Issue 1: DB Lytix UDFs do not perform comprehensive logging

DB Lytix UDFs only log version strings (see Test Note 3.2 for details) and do not log any additional information in the IQ Message Log. In cases where it is desirable to troubleshoot a UDF, there is not sufficient detail written to the IQ Message Log to provide debugging guidance.

Although DB Lytix UDFs throw pre-defined exceptions, no detail about these exceptions are logged to the message log. After receiveing an exception from an invoked UDF function, the Sybase IQ server will log a generic message, but does not have detailed context information that would normally be logged by the underlying UDF.

When a DB Lytix UDF generates an exception (e.g. invalid parameters specified for the UDF), the following somewhat cryptic messages will appear in the IQ Message Log:

Note - The example above displays a log message from previous version 1.0 of DB Lytix.

In most cases, there is no entry logged to the IQ Message log with the name of the specific UDF function or even in which UDF library the function is located.

There is no 'UDF complete' message logged to the IQ Message log.

Because of these two deficiencies, if the Sybase IQ server goes into an infinite loop, it will be difficult to determine if that was caused by a DB Lytix UDF, or something that happened in the Sybase IQ server after the DB Lytix UDF completed processing.

In the event that a DB Lytix UDF terminates abnormally or goes into an infinite loop, there will not be adequate information in the IQ Message Log to determine which DB Lytix UDF generated the exception or caused the problem (see also the various specific error message exceptions reported by DB Lytix UDFs in Test Notes Section 4 above).

Issue 2: DB Lytix UDFs do not support user cancellation

In rare circumstances, a DB Lytix UDF may take an inordinate amount of time to perform analysis, or may even 'hang' (start executing an infinite loop). If a DB Lytix UDF hangs, then the IQ server in which the UDF is running will also hang.

The Sybase IQ UDF API provides a mechanism to communicate user cancel requests from the IQ Server into the UDF library. Typically, a UDF would catch and interpret a request from the Sybase IQ server to cancel processing, but this has not been implemented in the DB Lytix external UDF libraries.

Note - During beta testing of DB Lytix, it was possible to invoke some of the UDFs with parameters that would cause them to go into an infinite loop. These UDFs were fixed in the released version of DB Lytix, and none of the certified UDFs are known to have this problem.

However, if a DB Lytix UDF were to somehow go into an infinite loop, the user would not be able to cancel the query. When a UDF goes into an infinite loop, this typically causes the Sybase IQ server to become unresponsive, and use nearly 100% of the system CPU. In the case where a UDF cannot be canceled, the IQ Server also cannot be shutdown gracefully.

In this situation, the Sybase IQ server will need to be forcefully stopped (via a Unix 'kill -9' command on the process, or via the 'End Process' feature of Windows Task Manager). When the Sybase IQ server is being shut down forcefully, the user cancellation request may finally appear in the iqmsg log, even though the user cancellation was not effective by itself.

When the Sybase IQ server is shutdown forcefully and then restarted later, it may need to go through a recovery process during the next startup.

Detecting a DB Lytix UDF hang in the IQ Message Log

The following is an illustration of the type of messages that would be logged if a DB Lytix UDF were to go into an infinite loop. The extract below of an example IQ Message Log (*.iqmsg file), and demonstrates what was logged when one of the Beta DB Lytix UDFs went into an infinite loop. Note that in the following log segment, the 'Cancellation request' did not actually get logged until the IQ server process was forcefully terminated (via a Unix 'kill -9' command, or via the Windows Task Manager 'End Process' action). The process was forcefully terminated about a minute after the UDF function hung.

Although the IQ server logs the 'Cancellation request' message (upon forceful termination), there is no detail in the IQ Message Log about which DB Lytix UDF was processing at the time. The only message written to the IQ Message Log was the version string that was logged when the UDF began processing. The log section below also shows messages from a subsequent restart of the IQ Server (according to the timestamps in the log, the IQ server was manually restarted about 14 minutes after the IQ server process was forcefully terminated):

Note - The example above displays a log message from previous version 1.0 of DB Lytix.

Issue 3: Problems with certain DB Lytix 1.1 Aggregate UDFs when no license in effect

Problem: On the Windows platform, while attempting to invoke specific DB Lytix 1.1 ESD1 Aggregate UDFs beyond the grace period for the licenses, and when no Sybase IQ In-Database Analytics (IQ_IDA) license was in effect, the Sybase IQ server generated a stack trace and aborted prematurely. The ten problematic aggregate UDFs for which this problem was encountered are listed below:

Note that the Sybase IQ server should return an error message when an attempt is made to invoke these functions beyond the license grace period if there is no Sybase IQ In-Database Analytics license in effect. All other certified DB Lytix 1.1 scalar and aggregate functions (not listed here), and all Table UDFs and TPFs performed properly. The properly-working UDFs allowed the Sybase IQ server to generate the appropriate 'License not found' error message in this scenario (attempting to run the UDFs when there is no In-Database Analytics license in effect).

Reason: It is unclear whether this problem is due to an internal software defect in the Sybase IQ server, or if the problem is within the specific DB Lytix 1.1 Aggregate UDFs listed above. Change request number 693186 has been filed with Sybase IQ Engineering. This change request has been assigned, and is currently being investigated by an engineer.

Resolution: In order to avoid experiencing the database abort problem, ensure that there is a valid Sybase IQ In-Database Analytics (IQ_IDA) license in effect prior to attempting to invoke any of the ten problematic functions listed above.

Issue 4: DB Lytix 1.1 TPF FLPCAEigen not sufficiently documented

Problem: The DB Lytix 1.1 User Manual describes the FLPCAEigen function in theoretical terms, and some sample SQL is provided, but it is not clear from the user manual what sequence of steps are required in order to set up and perform a Priciple Component Analysis.

Resolution: To obtain further details on how to perform a Priciple Component Analysis computation, please contact Fuzzy Logix customer support (see Vendor Contact Information below for contact information).

Back to Contents

Test Environment

This section provides details about the test environment. In the certification environment, all components were set up on a single machine. In other words, the Interactive SQL client (dbisql) was co-located on the same machine as the Sybase IQ server, which also requires DB Lytix libraries to be co-located or at least accessible from that same machine (e.g. via a shared disk resource).

Linux Certification Machine

Windows 2003 Certification Environment

Back to top of Test Environment | Back to Contents

Other Sources of Information

Sybase e-Shop

Sybase provides product and technical documentation online at MySybase.com, including the certification reports written by the Sybase Interoperability Group. Paper versions of product manuals may also be ordered through Sybase's online store, http://e-shop.sybase.com.

Sybase Partner Program

For more information about the Sybase Partner Program, please visit the Sybase Partner Program website.
http://www.sybase.com/partner

Back to Contents

Vendor Contact Information

Corporate Headquarters

Vendor Name:  Fuzzy Logix, LLC
Address:  10735 David Taylor Dr., Suite 130, Charlotte, NC 28262
Email: info@FuzzyL.com

Technical Support Number:  704.307.4819
Technical Support Email:  support@fuzzyl.com

Home Page: http://www.fuzzyl.com
In-Database Analytics: http://www.fuzzyl.com/products/in-database-analytics/
Sybase/SAP Partnership: http://www.fuzzyl.com/partners/analytics-platforms/sybase/

Financial Institutions and Custom Models

Contact: Jim Zhang
U.S. Telephone:  +1.704.236.6357
Email: Jim.Zhang@FuzzyL.com

Back to Contents