Switch to standard view 
  Sybase logo
 
 
 



Column Compression Sample

This sample illustrates SQL Anywhere 10's column compression feature. The compression ratio depends on the nature of the data. This example shows a ratio of approximately 47% of the base size composed of binary files inserted as BLOBs in the database. Note that if the files are already compressed, such as JPG images, then there isn't much difference between compressed and uncompressed columns.

For more information about column compression in SQL Anywhere 10, refer to the online documentation: SQL Anywhere SQL User's Guide | Designing Your Database | Designing the database table properties | Choosing whether to compress columns.

Requirements

You can run this sample under Windows or Linux.

Walkthrough

  1. Shut down any SQL Anywhere databases that are running on your computer.
     
  2. Open a command prompt and create a temporary directory. Change to this directory.
     
  3. Create a new empty database and start it by running the following commands:
     
    dbinit compress.db
    dbeng10 compress.db

     
  4. Start Interactive SQL and connect to the sample database by running the following command:
     
    dbisql -c "uid=dba;pwd=sql;eng=compress"
     
  5. Copy and paste the following script into Interactive SQL and execute it (press F5) to create and populate a couple of tables - one with a compressed column and one without it. Note that the files we store in the tables are SQL Anywhere 10 binaries. These are found in the default installation path: C:\Program Files\Sybase\SQL Anywhere 10 (for Windows) or /opt/sybase/sqlanywhere10 (for Linux). If your install path is different, please change it accordingly in the script before executing it.
  6. create table binaries (id int primary key default autoincrement, sabin long binary)
    go
     
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbcis10.dll'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbcon10.dll'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbctrs10.dll'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbeng10.exe'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbicu10.dll'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbicudt10.dll'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbinfo.exe'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbisqlc.exe'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dblgen10.dll'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dblib10.dll'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dblog.exe'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbserv10.dll'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbsrv10.exe'))
    go
    insert into binaries (sabin) values (xp_read_file('C:\Program Files\Sybase\SQL Anywhere 10\win32\dbtool10.dll'))
    go
    create table compbinaries (id int primary key default autoincrement, sabin long binary compressed)
    go
    insert into compbinaries select * from binaries
    go
    commit
    go
    checkpoint
    go
    select ext_page_count,table_name from systable order by table_id desc

  7. Note the use of the column definition COMPRESSED when creating the table compbinaries. This is what allows the column to be compressed. The other table binaries does not use this column definition, and so it is not compressed.
     
  8. The results of the script display the number of extended pages for both binaries and compbinaries. The value in ext_page_count is the total number of extension (BLOB) pages used by the underlying table. The ratio between both values is roughly 47%.
     
  9. ext_page_count

    table_name

    1,606

    compbinaries

    3,369

    binaries


  10. This concludes the sample. Close Interactive SQL and shut down the database server by running this command:
     
    dbstop -c "uid=dba;pwd=dba;eng=compress"

Clean-up

Remove the temporary folder you created in step 2 and all its contents.



Back to Top
© Copyright 2010, Sybase Inc.