Switch to standard view 
  Sybase logo
 
 
 



Contents


Overview

This section describes each of the products tested and how they were used during testing. For more information about any of the products, please refer to the product literature. 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 Sybase Release Bulletin for the relevant product and platform.

*Click here for for Sybase trademark and registration information.

The following diagram illustrates the test environment. For detailed information about the 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.

Back to top of Tests Performed | Back to Contents


Setup and Configuration

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

Install and configure the Sybase IQ 15.4 server

  1. Install IQ 15.4 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.4 version iqdemo sample database and then run the sample database and server. See the Sybase IQ 15.4 New Features Guide for information about the demo database. As stated previously, the test environment setup also included the creation and operation of a second IQ database configured with the UTF-8 character set and populated with supported data type data, including CLOB and Simplified Chinese data.
  3. Load the Solaris language packs required to support the Simplified Chinese data used for multi-byte data testing. Refer to the Sun Solaris documentation for this information.

Install and configure Microsoft Excel client and configure it for ODBC connectivity to Sybase IQ

In the test environment, the Sybase IQ 15.4 Database Client for Windows was installed on the same 64-bit Windows Server 2003 machine where Excel was installed. The IQ 15.4 Database Client includes the IQ 32-bit ODBC driver, which is required to enable connectivity between Excel and Sybase IQ.

  1. Install the Sybase IQ 15.4 Database Client for Windows on the same machine where you will install Excel.
  2. Open the 32-bit ODBC Administrator available from the Start | Programs | Sybase | Sybase IQ 15.4 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 4 for more information about loading drivers.
  3. Configure data source names (DSNs) for IQ. For information about configuring DSNs, see the Sybase White Paper: Basic ODBC Connectivity for Sybase IQ 15.4 and SQL Anywhere 12.0.1. Also note that the demo database for IQ 15.4 was configured with the UTF-8 character set. Thus, to connect to the demo database, you may need to specify "utf8" in the Character Set field of the Advanced tab of the driver configuration dialog box.
In the test environment, Excel was installed on the same 64-bit Windows Server 2003 machine with the Sybase IQ 32-bit database client. Note that even though the operating system was 64-bit, the Sybase ODBC driver tested and Excel were 32-bit applications.
  1. Install Microsoft Office Excel from the Microsoft Office 2010 suite installation, accepting all the default installation options. After installation, the Microsoft Office menu will be an option on the Start | Programs menu and Microsoft Office Excel 2010 will be available on the Microsoft Office menu.
  2. Launch the Excel application from the Start menu. Excel usually opens with three new blank worksheets. If it does not, choose File | New. A dialog box opens displaying the available templates to choose from. Double-click on the Blank workbook template and a new workbook opens displaying three blank worksheets.
  3. Locate the Excel application menu bar. Note that the menus are actually tabs that display different options. Excel opens the blank worksheets with the Home menu open by default. The Home menu/tab options include font, alignment, text style and number formatting options, as well as Excel cell (insert, update and delete) and data editing (AutoSum, Fill, Clear, Sort & Filter and Find & Select) features.

  1. Locate the Data tab and click it to display it. Notice that the menu options change. The Data menu provides the options for data management, which include accessing data from external data sources, such as the Sybase databases.

  1. In the Data menu options, locate the Get External Data options on the left and select drop-down menu for From Other Sources.

  1. Select the From Microsoft Query (bottom) option. MS Query is a feature provided within Excel for creating queries. This is the option to use for Sybase IQ ODBC data sources. The Choose Data Source dialog box opens with the Databases tab in focus. The ODBC data sources (DSNs) defined in the ODBC Administrator will be listed here. For example, if you created an ODBC DSN as described in the section Install and configure the Sybase database client software, you will see that DSN listed.

  1. Select the ODBC DSN you defined for the target Sybase database. You are now ready to retrieve ODBC data into Excel. Notice the option Use the Query Wizards to create/edit queries is selected by default. When you click OK, the Query Wizard will walk you through how to create a query using MS Query and how to retrieve the query result set into MS Query or an Excel spreadsheet. Leave the Query Wizard open and proceed to the next section for an example of how to create a query and retrieve data into Excel.

Back to top of Setup and Configuration | Back to Contents


Feature Example

This section describes how to create a query using the Excel MS Query Wizard and data from the Sybase IQ demo database. Specifically, this example shows how to retrieve the ID, last name (Surname), department ID, City and State from the Employees table in the Sybase demo database. It filters the data by a particular state and orders them alphabetically by the city.

  1. Before proceeding, you will need to create an ODBC data source for the IQ demo database, if you have not done so already.
  2. Begin with Excel Query Wizard open to the MS Query Choose Data Source panel as shown in Step 6 of the previous section. The instructions in the previous section describe how to display this dialog box.
  3. Select the DSN for the Sybase demo database and click OK. A message may appear indicating Excel is connecting to the target database. When it does, the Query Wizard Choose Columns window displays.
  4. In the Available tables and columns pane, scroll down until you see the Employees table and expand it to display its list of columns.
  5. Select each of following columns and then the right-facing arrow to move the columns to the Columns in your query pane: EmployeeID, Surname, DepartmentID, City and State. Click Next. The Query Wizard - Filter Data panel opens.

  1. In the Column to filter pane, select State. Notice under Only includes rows where section that State is listed and the first row of drop-down menus is now available.
  2. Specify you want to include only states beginning with the letter "M" by selecting begins with from the drop-down menu on the left, typing the letter M in the menu on the right, and then clicking Next. The Query Wizard - Sort Order window displays.

  1. Display the drop-down menu next to the Sort by field and select the State column. Leave the default Ascending option selected and click Next. The Query Wizard - Finish window displays.

  1. Leave the default option, within the Return Data to Microsoft Office Excel selected and click Finish. The Query Wizard closes and an Import Data dialog box opens prompting you to specify how you want to view the data in your Excel workbook.

  1. Accept the default Import Data options, which are to view the data as a Table and to put the data in the Existing worksheet, and click OK. The Import Data dialog box closes and you may see a message saying the data is being retrieved from the DSN you specified in the top row of the Excel worksheet. Afterwards, the result set of the query created as specified within the wizard opens in the Excel worksheet with the Design menu/tab in focus. Notice that only employees in Michigan (MI) and Maine (ME), the states beginning with the letter "M", are listed alphabetically by city. Also notice the format of the query results, which returned by default in Excel in the blue Table Style with the header row and banded rows Table Style Options.

  1. Save the query results in the Excel workbook by clicking the save (diskette) icon.
  2. You can perform additional manipulations on the query results in the Excel workbook. While the Query Wizard provides options to filter and sort data, the Excel application menus provide other options, such as AutoSum (basic aggregate functions), Financial, Logical, Text, Date and Time, Lookup & Reference, Math and Trig and more on the Formulas menu. There are also Sort, Filter, Group, Subtotal, Remove Duplicates, Data Validation, Consolidation and What-If Analysis on the Data tab.
  3. You also have the option to return the query results to MS Query. To do so, you select View Data or Edit Query in Microsoft Query on the Finish panel instead of Return Data to Microsoft Office Excel.

This completes the basic Microsoft Excel feature example.

Back to top of Feature Example | Back to Contents


Test Notes

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

  1. Test Note 1: Configuring MS Query Wizard to list other objects stored in Sybase databases
  2. Test Note 2: Selecting columns from more than one table in MS Query results in error
  3. Test Note 3: IQ 15.4 Sample iqdemo Database Schema
  4. Test Note 4: Use the ODBC Administrator installed with the Sybase database client software
  5. Test Note 5: Formatting Data in Excel
  6. Test Note 6: Data Types tested and related notes

Test Note 1: Configuring the MS Query Wizard to list other objects stored in Sybase databases

By default, the MS Query Wizard only shows the list of tables on the Choose Columns panel for the specified owner in the ODBC DSN you selected on the Choose Data panel. To see other objects such as system tables and views, click the Options button on the Choose Columns panel. When the Table Options dialog box opens, select the options you want and click OK. Notice that you can filter by the objects' creator (owner). Also, notice that stored procedures are not an option here. Thus, they were assumed not to be supported and were not tested.

Test Note 2: Selecting columns from more than one table in MS Query Wizard results in error

When you choose columns from more than one table in MS Query Wizard, you will see the following error:

Workaround: To build queries with JOINs, you will have to do so manually (without the Wizard) using the menu options as follows:

  1. In the MS Query Wizard, select the ODBC DSN for the Sybase database as before.
  2. When the Choose Columns panel opens, click Cancel. It will display a message asking you if you want to continue editing the query in Microsoft Query.
  3. Click Yes and the Add Tables dialog box will open.
  4. Select all of the tables from which you want to include columns in the query and click Add. You will see them added to the top of the screen. Notice that the Microsoft Query menu options are grayed out while the Add Tables box is open.

  1. Click Close to save the added tables and dismiss the Add Tables dialog box.
  2. After you close the Add Tables dialog box, the Microsoft Query menu options become available again. Locate the Table menu, display it and select Joins from it. The Joins dialog box opens.

  1. Build the joins by selecting the columns on which you want to join the tables, setting them equal to the other desired columns and clicking Add for each one.
  2. Repeat this until you have created all the joins you want and click Close to close the Joins dialog box. You will see lines between the "joined" columns and tables.

  1. Finish building the Query using the MS Query menu options. For example, to select the columns to include in the query, from the Menu Tool Bar select Records, and then Add Column. The Insert Column dialog box opens.

  1. Select the columns you want from the drop-down list for the Field and click Insert. You have the option to give the column a different name here. As a side note, notice the Total field. It provides the option to apply basic aggregate functions (total, count and sum) to a columns.
  2. When you are finished adding the columns, click Close to dismiss the dialog box.
  3. Continue building query using the MS Query menus. Refer to the Excel on-line Help system for more information.

Test Note 3: IQ 15.4 Sample iqdemo Database Schema

With Sybase IQ 15.4, you have the option to install the 15.4 iqdemo sample database. However, it is not automatically created on UNIX and Linux. Scripts to create the sample database are included. For the certification tests, the 15.4 database was created and tested.

Test Note 4: Use the ODBC Administrator installed with the Sybase database client software

In some tests, Sybase experienced problems loading the correct IQ ODBC driver on the 64-bit Microsoft Windows operating systems. To avoid this problem, Sybase recommends using the 32-bit ODBC Administrator installed with the Sybase IQ 15.4 Database Client software. The Administrator is available with the Sybase IQ 15.4 options on the Start | Programs menu. For the 32-bit version of Excel, be sure to use the 32-bit version.

Test Note 5: Formatting Data in Excel

To format the display of data in an Excel worksheet, right-click in the worksheet and select Format Cells from the pop-up menu. The Format Cells dialog box will open. In this dialog box, you can format all types of data. For example, you can specify the alignment and font of text data, the border and fill of cells, and the display of numeric and date data. For example, for decimal data, you can specify the number decimal places to display as shown below.

Test Note 6: Data type test notes

This section contains two tables of data type information: the first lists the ANSI C/C++ numeric and string types supported by Microsoft Excel and the second lists the data types supported by IQ 15. The Excel type information is available on the following Microsoft web page: http://msdn.microsoft.com/en-us/library/bb687869.aspx, which was the source of the information in the following table. Information about Excel types not tested is also available on this website.

Types Microsoft Excel-Supported Types
Numbers
  • 8-byte double
  • short (signed short int) - for boolean and integer values
  • unsigned short (unsigned short int)
  • int (signed long int)
Strings
  • (signed) char - null terminated byte strings of up to 255 characters
  • unsigned char - length-counted byte strings of up to 255 characters
  • unsigned short - Unicode strings of up to 32,767 characters, which can be null-terminated or length-counted.

Sybase data types tested

A sampling of following Sybase IQ 15.4 supported data types were tested. Large values (where maximum values are theoretical and restricted by hardware) and minimum values as well as null and not null values were tested. The following types were tested despite the limited set of data types supported by Excel. All types were handled as expected unless noted otherwise.See Test Note 5 for information about formatting data, such as decimal values.

Types Sybase IQ 15.4 ODBC

Exact Numeric Integer Types

 

  • tinyint, smallint, int - Excel returned data as expected.
  • unsigned int, bigint and unsigned bigint - Not supported. Excel displayed bigint and unsigned bigint values in scientific notation by default. Using Excel to format the data as a number with zero decimal places resulted in the display of a rounded-up value. For example, the bigint value 9223372036854775807 to 9223372036854780000.

Exact Numeric Decimal Types

  • decimal(10,4), (8,8) and smallmoney - Excel returned data as expected.
  • money, dec(19,4) and (31,0) - By default Excel displayed values in scientific notation. Using Excel to format the data as a number with the decimal places as defined for the column and forcing all digits to display resulted in the display of a rounded-up value.

Approximate Numeric Float Types

float, real and double - Excel returned data as expected.
Character Types
  • char(1), (254), varchar(10), (254) and long varchar(300) - Excel returned data as expected.
  • CLOB - See LOB below
Date/Time Types
  • date, time, smalldatetime - Excel returned data as expected.
  • datetime and timestamp - as stored in IQ include month, date, year, hours, minutes, seconds and milliseconds. Excel displays the time portion of the data with hour, minutes and seconds only. Milliseconds are dropped
Binary Types bin, varbinary - Not supported in Excel. Note that initial attempts to return binary data failed. However, with IQ database option ASE_BINARY_DISPLAY='ON' enabled through a separate Interactive SQL session, Excel returned data as expected.
Bit Array Types Not supported in IQ.
Large Object Types
  • CLOB (character large object and long varchar) types were tested in the form of character strings having a length of 300 bytes - Excel returned as expected.
Multi-byte Data Tested in the form of Simplified Chinese data inserted into a varchar column in a UTF8 database. Note that the Microsoft Asian Language packs were installed on the Excel host machine and the appropriate language packs were installed on the Solaris IQ host machine - Excel returned data as expected.
User-defined Types Based on char and numeric identity types - Excel returned data as expected.

Back to top of Test Notes | Back to Contents


Issues and Incompatibilities

No issues were found during testing for which there are not workarounds. Any such issues are documented with the workarounds in the Test Notes section.

Back to Contents


Test Environment

This section provides details about the test environment.

Microsoft Excel and Sybase Database Client Machine

Hardware or Software Description or Version

Machine

Intel(R) Xeon(R) CPU E7320, 2.13 GHz processor. 32-bit VMWare virtual machine.
Memory (RAM)

4 GB

Disk 20 GB
Operating system Microsoft Windows Server 2003 Enterprise Edition SP2, 64-bit

Network

TCP/IP Ethernet E1000

Reporting Tool

Microsoft Office Excel 2010 Trial Version 14.0.6021.1000 (32-bit) SP1. Part of Microsoft Office Professional Trial 14.0.6023.1000

Database Client software

Sybase IQ 15.4 Network Database Client for Windows, which includes the 32-bit Sybase IQ ODBC driver version 12.00.01.6567

 

Sybase IQ Machine

Hardware or Software Description or Version
Processors Four 1.8 GHZ Ultra SPARC IV (64-bit)
Memory 32 GB RAM
Disk Space 1 TB
Network hardware and software Ethernet, TCP/IP
Operating System 64-bit Sun Solaris 10 with language packs installed

Analytics database

64-bit Sybase IQ 15.4 Enterprise Edition

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 Services Group. You may also order paper versions of product manuals 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 web site, http://www.sybase.com/partner.

Back to Contents


Vendor Contact Information

Vendor Name: Microsoft Corp.

Address: Headquarters: Redmond, WA USA

Website: http://www.microsoft.com

Back to Contents



Back to Top
© Copyright 2010, Sybase Inc.