Sybase IQ Multiplex Nodes and Server Overview

IQ_SYSTEM_MAIN, IQ_SHARED_TEMP, and IQ user main dbspaces are shared by all multiplex servers, and all servers need access to the same physical file. Data managed by SQL Anywhere is not shared. Each node requires a separate copy of such data.  Each server has its own set of files in IQ_SYSTEM_TEMP and IQ_SYSTEM_MSG.

Note: It is always recommended to create a DEFAULT DB space to store objects and data on and not on the IQ_SYSTEM_MAIN Dbspace.

Coordinator Node

Each multiplex configuration requires a coordinator node. When you convert an existing simplex server to multiplex, it becomes the coordinator node.

The coordinator node:

  • Runs read-only and read-write operations against shared IQ objects.
  • Manages IQ main dbspaces.
  • Manipulates local data in SQL Anywhere system and user tables.

Coordinates all read-write operations on shared IQ objects, including:

  • Shared IQ table locking
  • Shared IQ storage management
  • Providing global transaction IDs for read-write transactions involving shared IQ objects
  • Maintaining the global catalog
  • Controls catalog synchronization for secondary servers
  • Performs schema changes on shared IQ store objects
  • Performs schema changes on SQL Anywhere store objects
  • Maintains and cleans up object versions

Secondary NODES ie. (reader/writer)

One or more secondary nodes may participate in a Sybase IQ multiplex configuration. One secondary node acts as a designated failover node, the first choice node to assume the coordinator role if the current coordinator is unable to continue.

The number of secondary nodes supported depends on the license purchased, as follows:

  • Demo/Trial Edition: Unlimited secondary nodes
  • Small Business Edition: None (multiplex not allowed)
  • Single Application Server Edition: One secondary node
  • Enterprise Edition: Unlimited secondary nodes (license needed for each)

Secondary nodes:

  • Can be either read-only nodes (reader nodes) or read-write nodes (writer nodes).

Writer nodes:

  • Can run read-only and read-write operations against shared IQ objects.
  • Can manipulate local data in temporary and SA base tables.

Reader nodes:

  • Can run read-only operations against shared IQ objects.
  • Can manipulate local data in temporary and SA base tables.

 

Sybase IQ Index Tips and Tricks

Parallel = the holy grail of speed! Sometimes, but in this case it does.

Why on Earth does IQ only allow you to create indexes with parallel option? Ask Sybase!


BEGIN PARALLEL
CREATE  HG index idx_1_HG ON <table> (column_1);
CREATE LF index idx_2_LF ON <table> (column_2);
CREATE HG index idx_3_HG ON <table> (column_3);
...... on and on
END PARALLEL


I have a  300 million row table joined to 1.2 million row table and have some records I want to delete and was wondering why it’s taking longer than it should . How can I coax some more speed out of my deletes?

Auh, IQ Optimization options fills this need.  Specifying predicates on columns that have HG indexes greatly improves costing. In order for the HG costing to pick an algorithm other than large delete, it must be able to determine the number of distinct values (groups) affected by deletions.  1st their has to be a HG index on the column in the predicate which is usually the case. IQ chooses three algorithms to process deletes.

set option HG_DELETE_METHOD = 1;  <------------'Small' delete' for a few distinct  rows or 1 row
set option HG_DELETE_METHOD = 2; <------------ 'Large delete'  100000+ rows
set option HG_DELETE_METHOD = 3; <------------' Mid delete' 100 to 10000 rows

Spinnaker Support Announces New Third-Party Maintenance Customer

RS&I, Inc. Moves Third-Party Services to Spinnaker Support

DENVER, August 22, 2012 Spinnaker Support, the global market leader for JD Edwards and SAP third-party maintenance, co-sourcing, and consulting services, is pleased to announce that RS&I, Inc. has joined the growing Spinnaker Support customer family after moving from another third-party maintenance vendor.

RS&I, Inc. is one of the largest and most successful sales and distribution companies of its type in the nation with nine full service regional locations and a retail network of more than 2,750 affiliated dealers.They are currently running JD Edwards EnterpriseOne. Spinnaker Support’s stability, Oracle’s current lawsuit against their previous vendor, and the potential risk to their business as a result of the lawsuit were among the primary reasons for changing vendors.

“When approached by Spinnaker Support about changing third-party maintenance vendors, I was a little standoffish at first,” commented Scott Rydalch, chief information officer of RS&I. “However, after learning about their business model, vetting their IP policies, and researching some of their current clients, I felt that they would be a better long-term fit for our company.”

The transition to Spinnaker Support was very seamless for RSI. “With a very simple on-boarding process, we experienced no frustrations,” Rydalch continued. “I look forward to a very beneficial support experience with Spinnaker Support.”

“I am excited to have RS&I as a new client,” stated Matt Stava, co-founder of Spinnaker Support. “The combination of our proven maintenance model and supplemental consulting services will help RS&I maintain their JD Edwards environment for many years to come.”

About Spinnaker Support

Spinnaker Support, the global market leader for JD Edwards and SAP third-party maintenance, co-sourcing and consulting services, helps companies maximize their ERP software investments. Whether companies are planning to replace their high-cost maintenance provider or are seeking supplemental maintenance support for their ERP applications, Spinnaker Support has a solution to fit their needs. More than 160 clients worldwide have chosen Spinnaker Support as their provider of choice. Spinnaker Support services are available across the globe via offices located in Denver, Singapore and London.

Sybase IQ 15.x Fast Projection Index Overview and Practical Usage

Most Sybase IQ indexes use a bitmap to store data compared to other indexes like B-trees .e.t.c,  Bitmap indexes can be built very fast in IQ and even faster when using the “Parallel IQ” function” which I posted the syntax on a previous blog. This also speeds up data loading and extraction as you don’t have to drop, disable indexes and even Sybase highly recommends leaving the indexes in place. You can also place multiple indexes on a column.  Another plus is when data is added, deleted or updated the index does not need to be rebuilt like traditional RDBMS indexes. In simple terms it’s faster to search/retrieved  a zero or one than  searching for a char value and only one column needs to be searched versus the whole row. This is what makes IQ unique and perhaps a leader  in the new world of “Big Data”.

This overview is for IQ 15.x only, so lets talk about the FP index in some detail.

Sybase IQ has 9 types of indexes but only three are highly used and a couple are considered useless in 15x. In this Blog, I will only discuss the most frequently used which is the FP and explain it’s usages.

—FAST PROJECTION (FP)

When creating a table, IQ creates this type for free (By default) but you can optimize them accordingly.  There are 4 types of FP indexes, 1 byte, 2 byte, 3 byte and flat. You want to avoid going flat because that means the index is not optimized. These types are dependent on 2 factors

— The cardinality of the data

— The value of IQ unique given in the ‘create table ‘ statement or if the, set option  MINIMIZE_STORAGE=’ON’ which is    like declaring IQ UNIQUE (255) on every column. Many times you will see this option hint if you use the INDEX_ADVISOR. If you see this recommendation when you analyze the query output html, you know your flat (No FP optimization) I’ll explain

The space  used to store the index is dependent on the type of the FP index used and the data type being stored.

A flat index stores the data as it’s being loaded with no storage optimization. For example, a char(20) will store 20 bytes of storage per value. As mentioned before, we want to use the 1, 2 or 3 byte FP index and logic says the lower the byte the faster.

The 1 and 2 byte FP indexes are known as optimized FP indexes because they read faster from disk, use less disk space and are used by the query optimizer to obtain information about the data being stored in the column.

The 3 byte is the same as 1 and 2 byte except it holds between 65k and 16 million unique values. Again, It’s easier to sort through zeros and ones than a bunch of characters.

In Order to create an Optimized index you must either specify a value for IQ UNIQUE in a create table statement or set the MINIMIZE_STORAGE database option to ON. MINIMIZE_STORAGE = Guess, compression which another feature IQ uses to speed things up. If you intend on setting the MINIMIZE_STORAGE option, you must do this prior to creating the table where you want the IQ UNIQUE value to defaulted to.

Example;

CREATE TABLE order_entry
(
order_id        int   IDENTITY,
customer_id     int   IQ UNIQUE (255)   NOT NULL,
sales_id        int   IQ UNIQUE (10000)   NOT NULL,
product_id      int   IQ UNIQUE (5000)   NOT NULL
)
SET OPTION MINIMIZE_STORAGE='ON';

CREATE TABLE statement

In a flat FP index a column of raw data is compressed but their is no storage optimization used. 1,2 and 3 byte indexes convert via look up tables.. ) zeros and ones.  Minus pictures I hope I explained  how IQ utilizes FP indexes and the DBA control over their optimization.

 

Creating Sybase IQ Connections to Sybase ASE to Import Data

These Examples assume your using Linux (2.6.32-220.el6.x86_64 GNU/Linux) and Sybase IQ 15.4.

Sybase IQ can create CIS connections to ASE, for example to access ASE data through an Sybase IQ-to-ASE proxy table.
This can be done in two ways: through JDBC and ODBC. JDBC is easier to set up, but ODBC tends to be significantly faster. In addition, the JDBC connectivity class has been deprecated (If you try to use CLASS ‘asejdbc’, You will receive a message box error via ISQL “asejdbc has been deprecated”)   in Sybase IQ 15.4 on Linux.

With Sybase IQ running on Linux, as is this case, setting up ODBC connections to Sybase ASE can be tricky, to say the least. What it doesn’t help is that the IQ documentation isn’t exactly a concise example of clarity on this point, either. Below is what I found out the hard way about setting up Sybase IQ-to-ASE ODBC connectivity on Linux

IQ versions covered
The information about Sybase IQ-to-ASE ODBC connections below applies to Sybase IQ 15.4. As for OpenClient, I’ve used OCS 15.4, though it should also work for OpenClient 15.0 + (wasn’t tested). If you run into problems, (go to www.sybase.com -> Support -> EBFs/Maintenance -> Software Developer Kit).

IQ-to-ASE ODBC connections – basic syntax
To set up an ODBC connection from Sybase IQ to Sybase ASE, you need to use the following Sybase IQ statement:

create server MY_ASE_ODBC class 'aseodbc' using 'MY_ASE'

Precisely what to specify in the USING ‘…’ clause depends on which of these two variations is chosen:

  1. Specify the ODBC connection attributes directly in the IQ create server statement;
  2. Use an ODBC Data Source Name (DSN), which is defined outside IQ and holds the ODBC connection attributes.

We’ll look at both of these.

In the examples below, I’m assuming we’re on Linux and we’re trying to connect from IQ to an ASE server named ‘MY_ASE’ running on port 5000 on host ‘ASE_15’.
An IQ proxy table is then created to an ASE table named my_tab (owned by dbo) in databases my_db.

The IQ-to-ASE ODBC driver – three vital setup steps
But first, and before going into either of the ODBC variations above, you need to get the following three things right:

  1. You need to have the correct ODBC driver library installed: the filename of the driver lib is libsybdrvodb.so.
    However, this driver may not be bundled with your IQ installation. If it is not, the driver is included with ASE as well as with the OpenClient connectivity libraries (downloadable from www.sybase.com -> Support -> EBFs/Maintenance -> Software Developer Kit).
    You must make sure you have this driver installed. It is OK, and perhaps even best, to install it in its own $SYBASE directory (it does not need to be in the IQ installation directory tree) . I copied the file and pasted it in several directories so I knew the ENV path would recognize it.
  2. The second important thing is that you need the 64-bit version of the ODBC driver if your IQ server is 64-bit, and the 32-bit driver if you’re still running 32-bit IQ.
    It is vital to get this right. On the IQ side, you can find the IQ server’s bitness (if that’s considered a word) with a simpleselect @@version or iqsrv15 -v Sybase
    IQ/15.4.0.6567/111107/P/GA/Enterprise Linux64 - x86_64 - 2.6.9-67.0.4.ELsmp/64bit/2011-11-07 00:40:24

    For the ODBC driver, the pathname tells you whether it’s 32- or 64-bit: the location is usually $SYBASE/DataAccess/ODBC/lib/libsybdrvodb.so or /opt/sybase/ASE_15_5/DataAccess64/ODBC/lib
  3. The third essential requirement is that the pathname of the directory containing the ODBC driver file is in the IQ server’s $LD_LIBRARY_PATH environment variable (or equivalent on other platforms). This means you need to run the following before starting the IQ server:
    export LD_LIBRARY_PATH=
    /opt/sybase/ASE_15_5/DataAccess64/ODBC/lib:$LD_LIBRARY_PATH

With these points covered, you can now start your IQ server…

IQ-to-ASE ODBC connection, without DSN
Now, let’s look at defining an ODBC connection from IQ to ASE with declaring the ODBC attributes directly in the IQ create server statement (i.e. without using an ODBC DSN).

  • — create remote server mapping through ODBC
    create server MY_ASE_ODBC_1 class 'aseodbc'
    using ‘Driver=libsybdrvodb.so;Server=bigbox;Port=5000;Database=my_db;PacketSize=16384;EnableServerPacketSize=0’Important:The Driver= clause must specify the ODBC driver’s filename (libsybdrvodb.so). It may also specify the full pathname to the ODBC driver file, but the driver’s directory pathname must still be included in the IQ server’s $LD_LIBRARY_PATH.Note that this also configures -optionally- a network packet size of 16KB; the ASE server must be able to handle this size, or the connection will fail (don’t ask me why EnableServerPacketSize=0 is needed — the PacketSize keyword was ignored without it).
    Also note that the contents of the using ‘…’ clause are not checked at this stage; any errors will only be raised once IQ tries to make an actual connection.
  • — create external login for the IQ ‘dba’ user
    — let’s assume it maps to the ASE ‘sa’ user; adjust as needed
    CREATE EXTERNLOGIN dba to MY_ASE_ODBC_1
    REMOTE  login sa IDENTIFIED BY sql;
    Important: You must create an external login: even though it looks as if you can specify the remote login name+password directly with using ‘…;UID=sa;Password=sql; the specified password is ignored (for some reason, with using ‘…;UserID=sa;…’, the specified username is ignored as well).
  • — test the connection; if there is any error in the setup, error messages will be raised now:
    forward to MY_ASE_ODBC_1 { select @@servername, db_name(), @@version };
  • — if the connection works, create the proxy table:
    create existing table ase_proxy_tab at 'MY_ASE_ODBC_1.my_db.dbo.my_tab';select * from ase_proxy_tab;