Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 
Support > Technical Documents > Document Types > Partner Certification Report > Toad for Cloud Databases 1.3 with Sybase IQ 15.3 -...

Toad for Cloud Databases 1.3 with Sybase IQ 15.3 - Enabling Query Federation with HDFS, Report 368, September 2011 - Updated January 2012

This report details the certification of Toad for Cloud Databases 1.3 with Sybase IQ 15.3 - Enabling Query Federation between Sybase IQ and Hadoop Distributed File System (HDFS), via HIVE.
 
RSS Feed
 
 
 

Contents


Overview

This section describes each of the products tested and how they were used during testing. 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.

  • Toad for Cloud Databases (hereafter called "Toad") tool was used to query and create reports utilizing the Sybase IQ databases. The Toad version tested was a 32-bit application installed and run on a 64-bit Windows Server 2003 machine. Toad includes a component called the Data Hub, which provides connectivity to various traditional relational database sources, as well as HIVE implementations residing on a Hadoop Distributed File System (hereafter called HDFS). Using various Data Hubs, Toad supports Query Federation, allowing users to extract and join data which originates from disparate sources, both relational and big-data.
  • Sybase IQ Version 15.3 (hereafter called "IQ") served primarily as a data source. Two 64-bit IQ database server instances running on the same Sun SPARC Solaris 10 workstation were tested. On ran with the sample "iqdemo" database and the other ran with a database loaded with IQ supported data type test tables and data.
  • 32-bit Sybase IQ 15.3 ODBC Driver for Windows version 15 (hereafter called the "IQ ODBC driver") enabled ODBC connectivity between Toad and the IQ servers. The driver was installed with the Sybase IQ 15.3 Network Database Client for Windows, which includes the driver in both 32-bit and 64-bit word sizes. The 32-bit version of the driver was used because the version of Toad tested was a 32-bit application and the word-size of the driver must match that of the client application, not the target server. For testing, two system data sources names (DSNs) were defined using the IQ ODBC driver: one for the sample iqdemo database and one for the data types test database.
  • Apache HIVE data warehouse infrastructure (hereafter called HIVE) provides a relational view into a Hadoop Distributed File System (hereafter called HDFS). Toad connects to HIVE via the Data Hub, using a direct connection, optionally through an SSH tunnel. Toad uses the native HIVE-QL to query and extract data from the HIVE view of HDFS. The Hadoop system (incorporating HDFS and HIVE) is typically installed in a Linux environment, in a multiple-node grid consisting of several commodity servers.

For more detailed information about the machines used in this environment, see the section Test Environment later in this report.

The following diagram illustrates the test environment.

Toad for Cloud test environment

Back to Contents


Tests Performed

This section contains a detailed explanation of the tests performed.

  • Installed the Sybase database servers. IQ was installed on a 64-bit Sun Sparc Solaris 10 workstation. All default options were accepted during installation. Note that the language packs required for Simplified Chinese language support were installed on the Sybase server host machines. See the section Test Environment for details about the software installed on the test machines.
  • The IQ server instances were run with the sample demo databases. See Test Note 4 for information about the IQ 15 demo database. The server was also run with the database that was created to hold supported data type test tables and data. The IQ demo and data type test databases were configured with character set UTF-8.
  • Installed Toad, a 32-bit application, accepting the default installation options, onto a Windows 2003 Server machine. See the Setup and Configuration section.
  • Installed the Sybase IQ 15.3 Network Client for Windows on the same machine with Toad. This software installed both 32-bit IQ ODBC drivers used for connectivity between Toad and the remote Sybase databases.
  • Installed Microsoft Windows Asian language packs to load the Simplified Chinese language onto the Toad machine.
  • Used the 32-bit Microsoft ODBC Administrator, installed with the Sybase IQ Database Client, to configure two system ODBC data source names (DSNs) for the Sybase servers on the Toad machine. Two DSNs were created using the 32-bit IQ ODBC driver for connectivity to the remote IQ server on Solarais. For details, see the Setup and Configuration section. For information about why it is best to used the ODBC Administrator installed with the Sybase IQ Database client, see Test Note 3.
  • Tested the ODBC connection from the Toad to Sybase servers using the defined ODBC data sources. (see the Setup and Configuration section.).
  • Tested the following Toad User application functionality:
    • Connect to, retrieve from and display available tables in the Sybase Databases. See the section Feature Example.
    • Perform simple queries of data from a single table in each of the Sybase demo databases. See the section Feature Example.
    • Create queries with conditions, groupings and ordering.
    • Format numbers and dates. See Test Note 2 for more information.
  • Use Report Generator to create a report.
  • Display data as a chart.
  • Used Toad's "Custom SQL" feature to test more complex SQL queries (see Issue 2 for information regarding the use of order by clauses within sub queries).
  • Evaluated Toad and the Sybase databases for the following, using ODBC connectivity protocol:
    • Error handling - Database unavailable.
    • Multiple-table joins handling. See Test Note 1 for more information.
    • Character representation of binary data types.
      • Binary data types: bin, varbin. Toad is unable to display bin and varbin type data. See Test Note 5 for more information.
    • Minimum, large and null-able value handling, for a sampling of numeric, date-time and binary data types supported by IQ. Raw data display of Large Objects LOBs).
    • Display of Multi-byte data in a view.
      • Multi-byte data: Simplified Chinese test
    • Retrieval of user-defined data types.

Back to Contents


Setup and Configuration

The following information relates to the installation, setup and configuration of the test environment.

The setup and configuration tasks are described in the following sections:

Install and Configure the Sybase IQ 15.3 servers:

  1. Install IQ 15.3 onto a 64-bit Sun SPARC Solaris 10 workstation, accepting all default installation options except the target installation directory.
  2. Run the provided script to create the 15.3 version iqdemo sample database and then ran the sample database and server. See the Sybase IQ 15.3 Quick Start Guide for more information regarding the creation of and IQ demo database.
  3. Load the Solaris language packs to support the Simplified Chinese data used for multi-byte data testing.

Install and Configure the Sybase database client software:

In the certification test environment, the Sybase IQ Database client for Windows was installed on the same Windows 2003 Server machine where Toad was installed. The IQ Database Client includes the IQ ODBC driver required to enable ODBC connectivity between Toad and IQ.

  1. Install the Sybase IQ 15.3 Database Client for Windows on the same Windows 2003 Server machine where you will install Toad. Note that the client software includes the 32-bit and 64-bit versions of the IQ ODBC driver.
  2. To configure ODBC connectivity:
    • Open the 32-bit ODBC Administrator available from the Start | Programs | Sybase| Sybase IQ 15.3 menu to configure ODBC data sources. Note that using the version of the ODBC Administrator available on this menu ensures you correctly load the 32-bit Sybase drivers. See Test Note 3 for more information about driver loading.
    • Configure data source names (DSNs) for IQ 15.3. For information about configuring DSNs, see the Sybase Whitepaper: Basic Connectivity for Sybase IQ, ASE and Sybase RAP-Trading Edition. Note that the demo databases for IQ 15.3 are configured with the UTF-8 character set. Thus, to connect to the demo databases, you may need to specify "utf8" in the Character Set field of the Advanced tab of the driver configuration dialog box.

Install and Configure Toad for Cloud Databases:

dIn the test environment, Toad was installed on the same 64-bit Windows 2003 Server machine with the Sybase IQ Database client,. Note that even though the operating system was 64-bit, both the Sybase IQ Database client and Toad were 32-bit applications. Toad for Cloud offers two deployment options for the Data Hub. Option one is a Desktop deployment which installs a local private Data Hub on the client machine. Option two is a Cloud deployment which will create and Amazon EC2 instance of the Data Hub. For the purpose of this certification test, the Desktop deployment was chosen.

Install Toad desktop deployment option, accepting all the default installation options. Toad will be available as a Start menu program option.

  1. Launch the Toad application by choosing Start | <All> Programs | Quest Software | Toad for Cloud Databases | Toad for Cloud Databases 1.3. The Toad application opens displaying the Home page.

Toad for Cloud Databases Home page

  1. Configure Toad client to connect, using the ODBC DSN created in the prior step, to the IQ server. Connectivity testing was performed by executing the following steps:
    1. On Start page, right-click on Data Sources, located in the Object Explorer pane.
    2. Select Map Data Source. Map Data Source window displays.
    3. Enter a data source name. Select a data source type by scrolling down to ODBC, which is the type to select for IQ, and select it. Click OK to continue.

Map a Toad Data Source

  1.  
    1. The ODBC parameters information displays in the Map Data Source window. Select the data source you configured for the IQ server and database with which you want to connect.
    2. Enter the IQ server username and password and click OK to continue.
    3. The Data Source successfully created window displays. Click OK to close the window.

Select Data Source

  1. Map the remote data objects to SQL tables.
    1. From within the Object Explorer pane, click Data Sources, select the data source name, expand Remote Data, right-click the table and select Map Remote Data.The Map remote object to Table window displays.
    2. Click OK to continue. The Table successfully created window displays. Click OK to close the window.
    3. Toad is now configured and ready to create data queries with Sybase IQ data. The next section Feature Example describes the process for creating a basic view.

Back to Contents


Feature Example

This section describes how to construct and execute SQL statements in Toad, using the Toad Visual Query building tool, to display Sybase Data.

Construct and execute SQL statements using the Toad Visual Query Building tool

  1. If it is not already open, open the Toad application to display the start page.
  2. Expand the Data Sources tree, located within the Object Explorer pane.
  3. Expand the mapped tables list.
  4. Click the SQL Builder icon which is located on the menu bar. The Query Builder pane opens.

SQL Builder icon

  1. Drag and drop tables from within the Object Explorer onto the Query Builder drawing surface.
  2. To create a relationship between columns in tables, drag column from one table to the corresponding column on the second table.

Creating a table relationship

  1. Toad automatically assigns an inner join as the default join type. See Test Note 1 for more information.
  2. To add columns to the query, select the table and double click the column name you wish to add.
  3. To add a where condition to the query, click the ellipse icon in the relevant column. The Where Condition window opens,

Open Where Condition window

  1. Enter the where condition for the SQL statement. In this example "Central" was selected as the region. Click OK to close the window.

Enter Where condition

  1. To execute the query, right-click in the Query Builder pane and select Execute SQL from the menu.

Execute SQL

  1. The Result Sets window opens displaying the query results.

Query Results set window

  1. Save your work. From the tool bar menu select File | Save All Files.

Create a data report using Toad's Data Report Wizard

Create a Data Report, using the Toad Data Report Wizard, based on an existing SQL file.

  1. From the menu bar select Tools | Reports | Data Reports. The Toad Data Report Wizard screen Opens.

Create a data report

  1. Accept the Standard Report default option as the report type and click Next to continue.
  2. Enter a query statement to generate a result set or you can add a query by opening a previously created .sql file. In this example, an existing .sql file was chosen.
  3. Click the open folder icon and select from the list of available .sql files.

Open existing sql file

  1. The selected .sql file's query statement is displayed. Click Next to continue.

.sql file query statement displays

  1. Using the arrow buttons, select the columns to display in the report. Click Next to continue. The Choose columns window displays.

Choose the columns to display in your report

  1. Using the arrow buttons, add grouping levels to your report. For example, select the Products.NAME field and click the arrow button to add it as the grouping level for the report. Click Next to continue. The Summary Options window displays.

Add grouping levels

  1. To add a summary value to the report, select from on of the options displayed. Click Next to continue. The report layout window displays.

Summary values options

  1. Click Next to navigate through the screens selecting the report's type, orientation and style. For this example, the align left 1 layout and landscape orientation were chosen.
  2. On the final screen, enter a report title and description. Click Finish to save your changes. The Toad Data Report Wizard window closes and the Report Design pane is displayed.
  3.  

Report Design pane

  1. Select the Preview tab to display the report. To save your work select File | Save from the menu bar.

Preview your report

This completes the basic feature demonstration of Toad for Cloud Databases.

Back to Contents


Test Notes

This section contains information gained during testing, which may be helpful. The notes are divided into the following sections:

Test Note 1. When joining Multiple Tables, Toad Automatically assigns an inner join as the default join type
Test Note 2: Using the FormatString Editor to modify how a field is displayed
Test Note 3: To ensure the correct driver is loaded use the ODBC Administrator
Test Note 4: IQ 15.3 Sample iqdemo database schema
Test Note 5: Data Types tested and related notes

Test Notes 1: When joining multiple tables, Toad automatically assigns an inner join type by default.

When joining Multiple Tables, Toad Automatically assigns an inner join as the default join type. To change join types, double click on the join iconjoin icon .

Select a join type from the menu displayed. See the example below.

Selecting a join type

Test Notes 2: Using FormatString Editor to modify how a field is displayed

To format a field within the report, click the field in the detail section of the report. In this example the SalesOrders.ORDERDATE was selected.

  1. Click the arrow button, located above the selected field. The Label Tasks window displays.

Format field button

  1. Select the ellipse button located to the right of the Format String field. The FormatString Editor window displays.

Select Format String from the Label Tasks window displayed

  1. Select the relevant category and click the Standard Type tab to display the different formatting options available. You may also choose to create a custom format string by selecting the Custom tab. For this example, the Category chosen was DateTime. The Standard Types tab was selected and yyyy-MM-dd format was chosen for the SalesOrders.ORDERDATE. Click OK to save your changes and close the window.

Test Note 3:

In some tests Sybase experienced problems loading the correct ODBC driers on the 64-bit Microsoft Windows operating systems. To avoid this problem, Sybase recommends using one of the ODBC Administrators installed with the Sybase IQ 15.3 Database Client software. Both 32-bit and 64-bit versions of the Administrators are installed. For the 32-bit version of Crystal Reports, be sure to use the 32-bit version.

Test Note 4:

With Sybase IQ 15.3, you have the option to install the 15.3 iqdemo sample database. However, neither is automatically created. Scripts to create both are included. So, you may choose. For the certification tests, the 15.3 iqdemo database was created and tested.

Test Note 5: Data Types tested and related notes.

This section contains a table listing the data types tested for IQ 15.3. Following the table are test notes and workarounds for possible data type issues.

Data Types tested

A sampling of following IQ 15.3 data types were tested. Maximum values (or large values where maximum values are theoretical and restricted by hardware) and small values as well as null and not null values were tested. See Test Note 2 for information about formatting data, such as date values, and see the notes following the table below for additional information about data types.

Types Sybase IQ 15.2 types Type mappings in <Partner Product> and handling notes
Exact Numeric Integer (int) Types Tinyint, smallint, int, unsigned int, bigint, and unsigned bigint

Tinyint, smallint, int, unsigned int returned data as expected. Bigint and unsigned bigint not supported. See Issue 1 for details.

Exact Numeric Decimal Types Decimal, numeric, smallmoney and money

Decimal(10,4) and (8,8), smallmoney returned data as expected

Decimal (19,4) and (31,0) field as Number(custom), set decimal to 0. Results in rounding up to an accuracy of 15. For example formatting decimal (31,0) 999,999,999,999,999,999,999,999,999,999 rounds up to 10,000,000,000,000,000,000,000,000,000,000.

Numeric (18,0) returned as scientific notations. See Issue 1. Format numeric(18,0) as Number (custom), set decimal to 0. Results round up to an accuracy of 15. For example formatting decimal (18,0) 123,456,789,012,345, 678 rounds up to 123,456,789,012,345,000.

Money Null/Not null rounding occurs. For example, 922,337,203,685,477.5807 truncates the decimal places and rounds up to an accuracy of 15 digits displaying 922,337,203,685,478.

See Test Note 2 for information about formatting data.

Approximate Numeric Float Types Float, real and double

Float returns scientific notation. Displaying a maximum decimal precision of 15. Numeric data with precision greater than 15 are rounded up.

Character (char) Types Char in lengths 1 and 254 and varchar in lengths 10 and 254

Char and varchar Returned data as expected.

Date/Time Types Date, time, smalldatetime, datetime and timestamp

Date, time, smalldatetime, datetime and timestamp returned data as expected.

See Test Note 2 for information about formatting data.

Toad does not offer an option to display milliseconds. See Issue 2.

Binary (bin) Types Binary and varbinary

Binary and varbinary.

Varbinary not support by Toad.

Image Binary Large Object (BLOB) and Image Types BLOB and image Returns hexadecimal data.
Character Large Object Types (CLOB) and Long Varchar Types CLOB and long varchar in length 1 and 300

CLOB and long varchar in length 1 and 300.

Returned data as expected.

User-Defined Types Test user-defined data types created from char, numeric and identity types

Returned data as expected.

Multi-byte Data Multi-byte data tested in the form of Simplified Chinese data inserted into a varchar column in a UTF8 database Not supported
 

Back to top of Notes section | Back to Contents


Issues and Incompatibilities

This section describes the issue(s) found during testing.

Issue 1: Limits of size and precision on numerical data

Toad has limitations for the display of minimum and maximum values for the following types

Bigint: Sybase IQ is capable of storing integers with a magnitude of 9.22X10^18, and with and accuracy of about 18 digits. Unsigned bigint's are not supported in Toad. The data returned is displayed as #Error.

Real: Sybase IQ is capable of storing real numbers with a magnitude of nearly 3.4X10^38, and with an accuracy of about 18 digits. Toad converts large real numbers to scientific notations, and only displays accuracy up to 15 digits.

Float: Sybase IQ is capable of storing floating point numbers as large as 76 digits before the decimal point, or as small as 96 digits after the decimal point. Toad will convert large floating point numbers to scientific notation, and only displays accuracy up to 15 digits.

Issue 2: Limits of size and precision on numerical data

Toad does not display timestamp milliseconds. Formatting data does not resolve this issue.


Test Environment

This section provides details about the test environment.

Client Machine

Hardware or Software Description or Version

Operating Environment

Two Intel(R) Xeon(R) CPUs, 2.13 GHz each, 64-bit VM running VMWare ESX Server version 3.5, TCP/IP Ethernet E1000, Microsoft Internet Explorer 8.0.6001.18702, Microsoft Asian Language packs.
Memory

4 GB

Disk 20 GB
Operating System Microsoft Windows Server 2003 Enterprise X64 Edition Service Pack 2
Client product, for example Reporting software Toad for Cloud Databases 1.3
Connectivity software

Sybase IQ 15.3 Network Database Client for Windows, which includes 32-bit Sybase IQ ODBC driver version 11.00.01.5615

Server Machine

Hardware or Software Description or Version

Operating Environment

Sun Ultra SPARC IV (64-bit) running four 1.8 GHz processors, Ethernet, TCP/IP
Memory

32 GB

Disk 1 TB
Operating System 64-bit Sun Solaris 10 with language packs installed
Sybase server

64-bit Sybase IQ 15.3 Enterprise Edition

Back to Contents


Other Sources of Information

Sybase IQ Resource Page

Sybase provides a site dedicated to the Sybase IQ product. On this page, there are links to Data Sheets, White Papers, Webcasts / Podcasts, News, Case Studies (customer success stories), Events, Analyst Reports, the Sybase Analytics Blog, Videos, Awards, Newsletters, and Migration Resources. There is also a link on this site to a free download trial of Sybase IQ.

The Sybase IQ Resource Page is located at: http://www.sybase.com/IQ

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

Vendor Name: Quest Software
Website: www.quest.com

Back to Contents



 

Related Links

DOCUMENT ATTRIBUTES
Last Revised: Jan 26, 2012
Product: Sybase IQ
Technical Topics: Data Marts, Data Mining, Data Analysis, Database Admin, Data Management, Accelerated Decision Making
  
Business or Technical: Technical
Content Id: 1094781
Infotype: Partner Certification Report
 
 
 

© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics