Comparaison Sybase ASE vs Sybase IQ

Chaque page de données contient une ligne entière : méthode la plus efficace pour extraire et ecrire un petit nombre d'information, caractéristique du transactionnel.

Chaque page de données contient les informations d'une seule colonne : méthode la plus efficace pour filtrer ou extraire un très grand nombre d'informations, caractéristique du décisionnel.

Pour cette raison, les paramètres relatifs aux stockage en ligne comme le mode de verrouillage (locking scheme), les paramètres max rows per page, expected row size sont sans équivalent sous IQ.

• logique (fast projection, bitmaps)
• physique (compression de pages)

• ASE

• IQ pour le stockage et la restitution
• ASA (connectivité, parse, securité, CIS)

Sybase a séparé la logique purement décisionnelle de la maintenance et l'administration, raison pour laquelle on trouve une couche SQL Anywhere ou ASA (Adaptive Server Anywhere) à côté du moteur IQ proprement dit. A l'origine c'est la couche ASE qui était associée au moteur IQ.

• Transact SQL

• Watcom SQL (natif ASA , recommandé)
• Transact-SQL (pour compatibilité)

Équivalence de master+sybsys* : métadonnées (metadata) + structures et procédures systèmes.

Les procédures et objets utilisateurs sont enregistrés dans les bases utilisateurs.

Les multiples bases sont possibles, mais non recommandés.

Le catalogue SYSTEM contient les métadonnées, structures, procédures systèmes ET les objets utilisateur (vues, procédures stockées...).

Possibilité de créer des devices sous forme de fichiers ou de raw-device.

Pour le catalogue, le dbspace est obligatoirement un fichier. Pour les dbspace utilisateur, le support peut être un fichier ou un raw-device.

• Indexes btree (nonclustered indexes)
• Indexes btree "clusterisés" (clustered indexes). Les données sont triées sur la clé d'index. La dernière page d'index est la page de données.

• FP (Fast Projection par défaut, 'lookup table')
• LF (Low Fast , btree + bitmap)
• HG (High Group , btree + bitmap + Group Array)
• HNG (High Non Group : bitwize)
• DATE
• TIME
• DTTM
• WORD (Word : +/- 'fulltext', equivalent au HG)

Avantages : garantie transactionnelle implicite du périmètre.

Inconvénients : contention possible.

Avantages : pas de contention de verrous.

Inconvénients : pas de garantie transactionnelle de type ACID. Les différentes versions des snapshots doivent être maintenues en mémoire tant qu'un lecteur n'a pas lancé la commande commit. Des problèmes d'allocation d'espace peuvent se produire.

Le catalogue utilisateur (sysobjects, sysindexes...) est stocké dans la base utilisateur et appartient au propriétaire dbo de la base

Les objets du catalogue appartiennent au compte sys.

Depuis la version 15.0 (2006), le partitionnement sémantique est possible (partitionnement par intervalles, par liste, par valeurs de hachage : RANGE, LIST, HASH).

Le partitionnement sémantique est inutile à moins de 1 To de données.

Le partitionnement sémantique sous IQ a plus été imaginé pour le cycle de vie des applications. Les partitions les plus anciennes et les moins accédées sont déplacées sur des espaces de stockage moins puissants et moins coûteux que les partitions les plus récentes et les plus accédées.

Une caractéristique du moteur IQ consiste à remplacer l'I/O par un usage CPU intensif. Par nature donc, IQ est consommateur de ressources de calcul.

Les stratégies de partitionnement dans un SGBD classique ont deux axes de fonctionnement. Dans le cadre sémantique, on offre une possibilité de filtrer sur un ensemble identifié de données. On procède par élimination de partitions : les données ne peuvent être triées que sur un axe unique dans cette technique. Le deuxieme cadre, historique, est le partitionnement de type "round robin" qui vise à créer une structure où les données sont réparties dans des sous ensembles de taille réduite. L'interrogation de ces structures est réalisée en utilisant toutes les ressources disponibles grâce à du parallélisme : c'est une technique utilisable en mode batch.

À propos du moteur IQ : dans le cas du partitionnement sémantique, le stockage en colonnes, les Fast Projection et les bitmaps limitent par nature le besoin d'avoir à éliminer des partitions. Dans le partitionnement aléatoire (round robin), il va falloir composer avec la puissance de la machine, celle-ci étant déjà fortement mise à contribution par IQ par nature.

Pour ces raisons, en deça d'un volume important, le partitionnement ne s'avère pas forcément être une option indispensable.

(millisecondes arrondies au 1/300)

Format par défaut MMM-DD-YYYY

Attention, le type timestamp IQ est un type datetime contrairement à ASE

Format par défaut : YYYY-MM-DD

Précision par défaut : 18,0

Précision par défaut : 30,6

Équivalent au type "real" : voir l'option FLOAT_AS_DOUBLE pour la compatibilité avec ASE

Commande externe permettant de charger dans une table toute structure texte délimitée

bcp BASE..test in file.csv -c -t';'
-S ... -U ... -P ...

Commande interne à l'instance permettant de charger dans une table toute structure texte délimitée

LOAD TABLE test
(
a date('YYYY/MM/DD') null('0000/00/00'),
     FILLER(1),
b,
c
)
FROM 'file.csv'
DELIMITED BY ';'
ROW DELIMITED BY '\n'
QUOTES off
ESCAPES off
FORMAT ascii
WITH CHECKPOINT ON
PREVIEW ON
;
        commit;

Permet d'adresser tout type de données distantes : ASE, IQ, fichier texte, mais aussi - moyennant connecteurs - Oracle, MSSQL, DB2...

Permet nativement d'adresser des données ASE et iQ distantes. Des interfaces ODBC et JDBC permettent tout type de connexion hétérogene (Oracle, DB2, ...).

Syntaxe spécifique à IQ permettant des chargements massifs performants à partir de données en provenance d'un serveur ASE ou d'un serveur IQ.

create proc sp_test
AS
BEGIN
select a from test
END
go

Compatibilité Transact-SQL assurée pour garantir la création de procédures stockées identique à un serveur ASE.

create proc sp_test ()
RESULT ( a varchar(10) )
BEGIN
select a from test;
END;

out : @val type output

drop proc sp_test
go
create proc sp_test ( @param int )
AS
BEGIN
select a from test
where b=@param
END

exec sp_test 2
a
----------
aaa
bbb

OUT val type

drop proc sp_test;

create proc sp_test ( IN param int)
RESULT ( a varchar(10) )
BEGIN
select a from test where b=param;
END;

sp_test 2
a
----------
aaa
bbb

Le caractère @ est optionnel sous IQ (mais supporté)

declare @var type

create variable var type;

Les syntaxes ASE SET et SELECT sont supportées pour les affectations de variables.

Les variables créées avec la commande DECLARE ne sont persistantes que dans le blobc BEGIN .. END dans lequel elles sont déclarées.

declare variable_name data-type;

set @var=val, select @var=val

set @var=val, select @var=val from dummy

Utiliser de préférence la commande 'SET'.

Pour la commande 'SELECT' , la clause 'from dummy' est optionnelle mais importante. Si elle est omise, la compatibilité Transact-SQL fonctionne mais la requête est parsée par la couche ASA avec un surcoût.

select TransactSQL('create variable @a int')
 from dummy;
-------------------------
declare @a integer

La fonction WatcomSQL(commande) transforme de la syntaxe Transact-SQL en langage Watcom SQL

La fonction SQLDialect(commande) retourne le type de syntaxe (Transact-SQL ou Watcom).

create proc sp_test
as
begin
declare @a int
create table #tmp ( a int not null)

insert #tmp ( a ) values ( @a )
select getdate() 'Date',
  @@error '@@error'
end

Msg 515, Level 16, State 3
Server 'MIP_U1_ASE', Procedure 'sp_test',
 Line 7 Attempt to insert NULL value
into column 'a', table '#tmp';
column does not allow nulls.
Update fails.
Command has been aborted.

Date                @@error
------------------- -----------
Nov 30 2009   3:45AM        515
        

create proc sp_test
as
begin
declare @a int
create table #tmp ( a int not null)
insert into #tmp ( a ) values ( @a )

select  getdate() as 'Date',
        @@error   as 'error'
from dummy
end;

(sa)> sp_test
Could not execute statement.
Tried to insert a null value into a
non-null field a on row 1.
--
db_sqlins.cxx 12375)
SQLCODE=-1002005, ODBC 3 State="HY000"
Line 1, column 1

L'instruction 'ON EXCEPTION RESUME' simule le comportement Transact-SQL

create proc sp_test ()
ON EXCEPTION RESUME
begin
declare @a int;
create table #tmp ( a int not null);
insert into #tmp ( a ) values ( @a );
select  getdate() as 'Date',
        SQLSTATE as 'QLSTATE',
        SQLCODE as 'SQLCODE'
from dummy;
end;
commit;

(sa)> sp_test()
Date                       SQLSTATE SQLCODE
-------------------------- ------- -----------
Nov 30 2009   3:59AM 00000  0

(1 row affected)
        

Elle est effacée au redémarrage de l'instance (configurable, model et/ou traceflag ) .

Chaque utilisateur ayant les permissions sur cet objet voit les données communes.

create table tempdb..temptable ( a int )
go

Exemple : User 1 peut insérer les données dans la table globale.

USER 1> insert tempdb..temptable values ( 1 )
go
USER 1> select a from temptable
USER 1> a
        ------
             1
go

Exemple : User 2 peut voir le contenu de la table globale

USER 2>
USER 2> select a from temptable
USER 2> a
        ------
             1
go

Elle persiste après le redémarrage de l'instance.

Chaque utilisateur possède sa version de la table temporaire.

Le contenu de la table est vidé après chaque commit (configurable : ON COMMIT PRESERVE ROWS)

create global temporary table temptable ( a int ) ;

Exemple : User 1 peut insérer les données dans la table globale. Attention, les lignes sont vidées apres l'ordre commit

USER 1> insert temptable values ( 1 ) ;
USER 1> select a from temptable;
USER 1> a
        ------
             1
USER 1> commit
USER 1> select a from temptable;
USER 1> a
        ------
0 rows ...
        

Si la table est recréée avec l'option ON COMMIT PRESERVE ROWS, alors, comme son nom l'indique les données seront préservées.

USER 1> drop table temptable ;
USER 1> create global temporary table
temptable ( a int ) ON COMMIT PRESERVE ROWS;
USER 1> insert temptable values ( 1 )
USER 1> commit
USER 1> select a from temptable
USER 1> a
        ------
             1

Néanmoins, un deuxième utilisateur, ne verra que sa propre version des données

USER 2> select a from temptable
USER 2> a
        ------

0 row ...
        

Elle est supprimée lors de la déconnexion de l'utilisateur (ou lors d'une commande de suppression explicite ) .

create table #temptable ( a int )

Elle est supprimée lors de la déconnexion de l'utilisateur (ou lors d'une commande de suppression explicite ) .

2 syntaxes pour IQ :

create table #temptable ( a int )

ou

declare local temporary table
temptable ( a int )

L'insertion et/ou la mise à jour implicite est possible

create table test ( a INT identity )
insert test ( a ) values ( 1 )    --> ERR

SET IDENTITY_INSERT test on
insert test ( a ) values ( 1 )    --> OK
insert test ( a ) values ( null ) --> ERR
update test set a=2 where a = 1   --> ERR

SET IDENTITY_UPDATE test on
update test set a=2 where a = 1   --> OK

SET IDENTITY_UPDATE test off
SET IDENTITY_INSERT test off

L'usage d'une instruction select identity_col into ... maintient le caractère 'identity' de la colonne

L'insertion et/ou la mise à jour implicite est possible

create table test ( a INT DEFAULT AUTOINCREMENT )
insert test ( a ) values ( 1 )      --> ERR

SET TEMPORARY OPTION IDENTITY_INSERT = test
insert test ( a ) values ( 1 )      --> OK
insert test ( a ) values ( null )   --> OK
update test set a=2 where a is null --> OK

L'usage d'une instruction select identity_col into ... ne propage pas le caractère 'AUTO INCREMENT'

Les valeurs par défaut sont supportées à partir de la version IQ 12.7

create trigger tr_i_test
on test
for insert
as
  if exists ( select a from
  inserted where a > 10 )
  begin
        print 'super'
  end
go
insert test ( a ) values ( 1 )
--
insert test ( a ) values ( 20 )
super

sp_help / sp_depends / sp_helptext

Seuls les triggers systèmes sont supportés.

create table test ( a varchar(10) )
sp_help 'test'

create table test ( a varchar(10) )
sp_iqhelp 'test'

create table t1 ( a int, b varchar(10) )
insert t1 ( a, b ) values ( 1, 'aa' )
insert t1 ( a, b ) values ( 2, 'bb' )
create table t2 ( a int, b varchar(10) )
insert t2 ( a, b ) values ( 2, 'cc' )
insert t2 ( a, b ) values ( 3, 'dd' )
select * from t1

select  t1.a, t1.b,
        t2.a, t2.b
from t1
join t2 on t2.a=t1.a
a      b      a      b
------ ------ ------ ------
2      bb     2     cc

select  t1.a, t1.b,
        t2.a, t2.b
from t1
full outer join t2 on t2.a=t1.a
a      b      a      b
------ ------ ------ ------
1      bb    (NULL) (NULL)
2      bb     2     cc
(NULL) (NULL) 3     dd

CREATE JOIN INDEX "jidx_test"
FOR   "SCH"."test"
FULL JOIN "SCH"."data"
ON "test"."TST_ID" = "data"."DAT_TST_ID";

select d.col1, d.col2
from (         
        select a.col1, a.col2 
          from tablea a
        union all
        select b.col1, b.col2
          from tableb b 
        ) d

drop table test

create table test( a varchar(10), b int )

insert test ( a, b ) values ( 'aaa', 1 )
insert test ( a, b ) values ( 'aaa', 1 )
insert test ( a, b ) values ( 'aaa', 2 )
insert test ( a, b ) values ( 'bbb', 2 )
insert test ( a, b ) values ( 'bbb', 3 )
insert test ( a, b ) values ( 'ccc', 3 )

select  a,
        count(1) 'nb',
        sum(b) 'tot'
from    test
where  b > 2
group by  a
order by 1

a     nb    tot
----- --- ---
bbb     1   3
ccc     1   3

select  a,
        count(1) 'nb',
        sum(b) 'tot'
from    test
where  b > 2
group by all a
order by 1

a     nb  tot
----- --- ---
aaa     0   0
bbb     1   3
ccc     1   3

drop table test

create table test( a varchar(10),
   b int , c date)

insert test ( a, b, c ) values
( 'aaa', 1 , '20091201')
insert test ( a, b, c ) values
( 'aaa', 1 , '20091202')
insert test ( a, b, c ) values
( 'aaa', 2 , '20091203')
insert test ( a, b, c ) values
( 'bbb', 2 , '20091204')
insert test ( a, b, c ) values
( 'bbb', 3 , '20091205')
insert test ( a, b, c ) values
( 'ccc', 3 , '20091206')
go

select  a,
        count(1) 'nb',
        sum(b) 'tot'
from test
group by a
having sum(b) >= 4
order by 1

a    nb  tot
---- --- ---
aaa    3   4
bbb    2   5

select a, count(1) 'nb', sum(b) 'tot' ,
        c
from test
group by a
having sum(b) >= 4
order by 1

a   nb tot c
---------------------
aaa  3   4 2009-12-01
aaa  3   4 2009-12-02
aaa  3   4 2009-12-03
bbb  2   5 2009-12-04
bbb  2   5 2009-12-05
        

create table test ( a varchar(10), b int )
go
insert test ( a, b ) values ( 'aaaa', 1 )
insert test ( a, b ) values ( 'aaaa', 2 )
insert test ( a, b ) values ( 'aaaa', 3 )
insert test ( a, b ) values ( 'bbbb', 4 )
insert test ( a, b ) values ( 'bbbb', 5 )
insert test ( a, b ) values ( 'cccc', 6 )
go
select a, b
from test
order by 1
compute sum(b), avg(b) by a
go

 a          b
 ---------- -----------
 aaaa                 1
 aaaa                 2
 aaaa                 3
            sum
            -----------
                      6
            avg
            -----------
                      2
 a          b
 ---------- -----------
 bbbb                 4
 bbbb                 5
            sum
            -----------
                      9
            avg
            -----------
                      4
 a          b
 ---------- -----------
 cccc                 6
            sum
            -----------
                      6
            avg
            -----------
                      6
        

Par défaut diffère de la norme ANSI (set ansinull off). null=null => vrai

declare @a varchar(10)
select @a=null

select count(1) where @a=null -- 1
select count(1) where @a is null -- 1

set ansinull on

select count(1) where @a=null -- 0
select count(1) where @a is null -- 1

declare @a varchar(10)
select @a=null

select count(1) where @a=null -- 0
select count(1) where @a is null -- 1

set ansinull off
select count(1) where @a=null -- 1
select count(1) where @a is null -- 1

Chaîne vide = un caractère espace

create table test ( a varchar(10) )

insert test ( a ) values ( '' )

select datalength(a) 'dl',
ascii(a) 'as'
from test
dl   as
---- ---
1    32

Valeur réelle

create table test ( a varchar(10) )

insert test ( a ) values ( '' )

select datalength(test.a) 'dl',
ascii(test.a) 'as'
from test
dl   as
---- ---
0    0

" = '

" != ' sauf si set quoted_identifier appliqué

Il est syntaxiquement possible d'omettre la clause FROM, néanmoins les performances en sont dégradées. Dans ce cas en effet la requête est interprétée par le moteur ASA, non par IQ

declare @a varchar(10)
select @a='ceci est un test'
select 'a',
        patindex('%i%',@a,using chars),
        patindex('%i%',@a,using bytes)
--- --- ---
a     4   4

declare @b univarchar(10)
select @b='ceci est un test'
select 'b',
        patindex('%i%',@b,using chars),
        patindex('%i%',@b,using bytes)

 --- --- ---
b     4   7

declare @a varchar(10)
select @a='ceci est un test'
select 'a',
        patindex('%i%',@a),
        patindex('%i%',@a)
--- --- ---
a     4   4

locate

declare @x varchar(20)
select @x='aa/bb/cc/dd/ee'

select locate(@x, '/', 1) 'a',
locate(@x, '/', -1) 'b'
a        b
-------- ---------
       3        12

drop table test;
create table test( a varchar(10));

insert test ( a ) values ( 'aaa');
insert test ( a ) values ( 'bbb');
insert test ( a ) values ( 'ccc');
insert test ( a ) values ( 'ddd');
insert test ( a ) values ( 'eee');
insert test ( a ) values ( 'fff');

select a, rowid("test") from test;

a     rowid(test)
----- -----------
aaa         1
bbb         2
ccc         3
ddd         4
eee         5
fff         6

select a from test where rowid("test")< 3;
a
-----
aaa
bbb

declare @x varchar(10)
select @x='un TEST'
select upper(@x) 'a', lower(@x) 'c'
a          c
---------- ----------
UN TEST    un test

Compatibilité Transact-SQL : lower/upper autorisés

declare @x varchar(10)
select @x='un TEST'

select  upper(@x) 'a', ucase(@x) 'b',
        lower(@x) 'c',lcase(@x) 'd'
a       b       c       d
------- ------- ------- -------
UN TEST UN TEST un test un test

Même restriction que pour 'select ... from dummy', la couche ASA est sollicitée, donc plus lente.

Utiliser dateadd

declare @x date, @y int
select @x='20091129', @y=1
select dateadd(day,@x,@y) 'demain'

demain
-----------------------
2009-11-30 00:00:00.000

Résultat de type datetime

declare @x date, @y int
select @x='20091129', @y=1
select @x+@y 'demain'

demain
-----------------------
2009-11-30 00:00:00.000

Utiliser dateadd

résultat de type datetime

declare @x datetime, @y datetime
select @x='20091129', @y='03:01:02'
select @x+@y 'demain'
demain
-----------------------
2009-11-29 03:01:02.000

Attention, syntaxe qui permet aussi l'affectation de variables

disk init name=data_device,
        physname='data_device.dat',
        size='100M'
disk init name=log_device,
        physname='log_device.dat',
        size='10M'
create database userdb
        on data_device=100
        log on log_device=10

create database 'userdb.db'
        log on  'userdb.log'
        IQ_PATH 'userdb.iq'

pour assurer une compatibilité avec ASE, ajouter les options CASE RESPECT et BLANK PADDING ON

disk init name=data_device2,
        physname='data_device2.dat',
        size='100M'
alter database userdb
        on data_device2=100

alter dbspace IQ_MAIN
        add file 'userdb2.iq'
        size 100M

select convert(int,(block_count/256)) 'Mb' ,
 * from sysiqdbfile

Les règles s'appliquent de la même manière pour les données et identifiants

sp_helpsort

Les identifiants sont insensibles à la casse.

select Value from sa_db_properties()
where PropName='CaseSensitive'

sp_iqclient_lookup / sp_iqconnection / sp_iqcontext

sa_conn_activity

sp_sysmon

sp_iqsysmon (IQ 12.7/15.0)

IQ Utilities (IQ 12.x)

select name from master..sysdatabases

Si l'on considère qu'une base est un schéma, alors :

select distinct Table_owner
from sp_iqtable()

sp_iqtablesize 'SMF.SECURITY_ANALYTICS'

Permet de voir et modifier les options au niveau serveur

sp_configure 'engines'

Table système : sysconfigures

Cette commande n'est que de la consultation, pour modifier un paramètre : 'set [ public ] option'

Tables systèmes : sysconfigures / sysoptions

Les options sous IQ peuvent être définies au niveau public, utilisateur ou temporaire (à la session courante).

sp_estspace SECURITY_ANALYTICS, 5000000

sp_iqestspace 'SMF.SECURITY_ANALYTICS', 5000000

select name from sysobjects where type='U'

select name from sysobjects where type='V'

select name from sysobjects where type='P'

sp_iqhelp null,null,'table' / sp_iqtable

sp_iqhelp null,null,'view' / sp_iqview

sp_iqhelp null,null,'procedure' / sp_iqprocedure

sp_iqrowdensity 'table SMF.SECURITY_ANALYTICS'

sp_iqindexinfo 'table SMF.SECURITY_ANALYTICS'

sp_iqindexsize 'SMF.SECURITY_ANALYTICS.
   ASIQ_IDX_T936_C100_FP'

Table système sysusers

Tables systèmes sys.sysuser et sysusers

exec sp_addserver DAS_U1_ASE,
    ASEnterprise, DAS_U1_ASE
go

exec sp_addexternlogin DAS_U1_ASE,sa,
   sa,xxxx
go

connect to DAS_U1_ASE
go

use dali_assets
go

exec sp_help
go

disconnect
go

sp_dropserver DAS_U1_ASE
go

create server DAS_U1_ASE class 'ASAJDBC'
using 'FRDAS301:30300';

create externlogin sa to DAS_U1_ASE
remote login sa
identified by xxx;

exec sp_remote_tables DAS_U1_ASE,
    null,null,dali_assets;

drop server DAS_U1_ASE;