Docstoc

SQL Anywhere Tips and Tricks - Sybase

Document Sample
SQL Anywhere Tips and Tricks - Sybase Powered By Docstoc
					SQL ANYWHERE TIPS AND TRICKS


JASON HINSPERGER
PRODUCT MANAGER, SQL ANYWHERE

SEPTEMBER 2011
         INTRODUCTION

         • This is a collection of interesting and useful ways to use the
           features of SQL Anywhere

         • There may be alternative (and maybe better) ways to
           accomplish the same goals

         • The idea is that you will be exposed to some of the lesser
           known features of SQL Anywhere




2 – October 4, 2011
DIRECTORY ACCESS
         DIRECTORY ACCESS

         CREATE SERVER html_tree CLASS 'directory'
         USING
         'root=c:\inetpub\wwwroot;SUBDIRS=3;READONLY=
         NO';
         CREATE EXTERNLOGIN DBA TO html_tree;
         CREATE EXISTING TABLE html_files AT
         ‘html_tree;;;.';

         • Note: Select * doesn’t include content for directory tables, but
           selecting specific columns will




4 – October 4, 2011
WEB SERVICES
         SQL ANYWHERE HTTP SERVER

         • You can use SQL Anywhere to provide access to data over http
         • Use CREATE SERVICE to expose a database procedure
            – Can return html, json, xml or your own format (raw)
            – Provides access to http header data (in and out)
            – Maps to a stored procedure to perform the actual work
            – Can access files in the file system

         • You can create a “root” web service to provide a more web-like
           interface to the database




6 – October 4, 2011
         ROOT WEB SERVICE


         CREATE SERVICE "root"
             TYPE 'RAW' AUTHORIZATION OFF SECURE OFF
         URL PATH ON USER webuser AS
         call sp_root(:url)
         go




7 – October 4, 2011
         XML
         SELECT xmlelement( name item,
                         xmlelement( 'ID', ID ),
                         xmlelement( 'title', Name ),
                         xmlelement( 'description', Description ),
                         xmlelement( 'Size', Size),
                         xmlelement( 'Color', Color),
                         xmlelement( 'Quantity', Quantity),
                         xmlelement( 'Price', UnitPrice),
                         xmlelement( 'pubDate', now() )),
                         xmlelement( 'link', 'http://localhost:8080/') )
                as atom_entry
         FROM Products

8 – October 4, 2011
EVENTS
         DATABASE BACKUP
         • Backing up databases is a requirement of all applications that
           care about their data

         • There are lots of management issues related to backup
            – How to schedule
            – How to validate
            – Where to store
            – How to test recoverability
            – When to delete
            –…

         • Use Events…

10 – October 4, 2011
         EVENTS
         The database should be validated before the backup occurs

    • Use SQL to validate and notify failure:
           DECLARE crsr_validate dynamic scroll cursor FOR CALL
           sa_validate();
           OPEN crsr_validate;
           FETCH NEXT crsr_validate INTO res_validate;
           IF res_validate <> 'No error detected' THEN
               CALL xp_startsmtp(‘hinsperg','mail.sybase.com');
               CALL xp_sendmail('admin@ianywhere.com',
               'Database validation Failed!',NULL,NULL,NULL,
               'Validation failed for database: ' || res_validate);
               CALL xp_stopsmtp();
               RETURN
             END IF;



11 – October 4, 2011
         EVENTS
         Several backups need to be kept before deleting old backups


         • Decide where to put the backup
               SET backup_dir = 'c:\_backup\ ' + dayname(today());

               BACKUP DATABASE DIRECTORY backup_dir;
               EXCEPTION WHEN OTHERS THEN
                   SELECT errormsg() INTO res_backup;
                   CALL
               xp_startsmtp(‘hinsperg','mail.sybase.com');
                   CALL xp_sendmail('admin@ianywhere.com',
                   'Database Backup Failed!',NULL,NULL,NULL,
                   'Backup failed for database: ' || res_backup);
                   CALL xp_stopsmtp();
                 END;
12 – October 4, 2011
         EVENTS
         Backups can run on a schedule or be triggered as needed


         ALTER EVENT “BackupDatabase"
         ADD SCHEDULE "BackupSched"
         START TIME '23:00:00' ON ('Sunday',
         'Saturday', 'Friday', 'Thursday',
         'Wednesday', 'Tuesday', 'Monday')

         OR

         TRIGGER EVENT "BackupDatabase"



13 – October 4, 2011
         EVENTS
         Query backups

         CREATE SERVER backup_tree CLASS 'directory' USING
           'root=c:\_backup';
         CREATE EXTERNLOGIN DBA TO backup_tree;
         CREATE EXISTING TABLE backup_files AT 'backup_tree;;;.';


         SELECT * FROM backup_files;




14 – October 4, 2011
         EVENTS
         Automatically cleanup backup logs on database mirror server

         CREATE EVENT cleanuplogs SCHEDULE START TIME '12:00am'
           EVERY 3 HOURS
         HANDLER BEGIN
                       declare dbmirror_state char(64);
                       select property('mirrorstate') into dbmirror_state;
                       IF dbmirror_state = 'synchronized' THEN
                    delete from backup_files where datediff( week,
              today(), access_date_time) >= 1;
                       END IF
         END




15 – October 4, 2011
SPATIAL DATA
         SPATIAL DATA
      SQL Graph paper




17 – October 4, 2011
         SPATIAL DATA
         SQL Graph paper




18 – October 4, 2011
         SPATIAL DATA
         Heat maps


         • Graphical display of variables
            – Eg. Population density




19 – October 4, 2011
         SPATIAL DATA
         QGIS Plugin


         • QGIS – Cross-platform open source tool with many GIS features
           and functions
            – View and overlay vector and raster data in different
              formats
            – Create maps and interactively explore spatial data
            – Create, edit and export spatial data
            – Perform spatial analysis

         • SQL Anywhere provides a plugin that allows for easy access to
           SQL Anywhere spatial data from within QGIS.
            – Ability to visualize, add and alter spatial data

20 – October 4, 2011
LOGIN PROCEDURE
         LOGIN PROCEDURE
         Set application variables when user connects


         • Login procedure executes when a user connects, after
           connection has been validated
         • Allows for post connection validation
            – Limit concurrent connections by a user
            – Send out password expiry notifications
            –…
         • Can use the "APPINFO" connection parameter to send
           application specific variables for the connection.
            – Use these variables throughout the application as default
              values, arguments, etc…

22 – October 4, 2011
         LOGIN PROCEDURE

       CREATE OR       REPLACE PROCEDURE DBA.login_check( )
       BEGIN
         DECLARE       t varchar(512);
         DECLARE       posstart integer;
         DECLARE       posend integer;

             CREATE VARIABLE @cuser varchar(32);

         SET t = connection_property('Appinfo');
         SET posstart = 6 + locate(t, 'cuser' );
         SET posend = locate(t, '''', posstart );
         SET @cuser = substr(t, posstart, posend - posstart);
         CALL sp_login_environment;
       END;


23 – October 4, 2011
QUERY PROCESSING
         MATERIALIZED VIEWS
         Query factoring to create a view that improves performance of a class of base queries

              SELECT a, b, c, d
              FROM T1 JOIN T2 JOIN T3 JOIN T4
              WHERE c between ‘x' and ‘y‘ and e = z
              GROUP BY a, b, c




              CREATE MATERIALIZED VIEW MV1 AS
              SELECT a, b, c, d, e
              FROM T1 JOIN T2 JOIN T3 JOIN T4
              GROUP BY a, b, c, e




25 – October 4, 2011
         OLAP
         Use windows and partitions to provide more information in result sets


         SELECT EmployeeID,
               Surname,
               Salary,
               State,
               AVG( Salary ) OVER Salary_Window
         FROM Employees
               WINDOW Salary_Window AS ( PARTITION BY State )
         ORDER BY State, Surname;




26 – October 4, 2011
         SELECT OVER DML
         Atomically select information from the results of a DML statement


         SELECT old_products.name,     old_products.quantity,
               final_products.quantity
         FROM ( UPDATE Products
                     SET quantity = quantity - 10
                     WHERE color = ‘White' )
               REFERENCING ( OLD AS old_products
                           FINAL AS final_products
               )
         WHERE final_products.quantity < 0.5 *
         old_products.quantity;




27 – October 4, 2011
         OPENSTRING
         Deconstruct strings into result sets


         SELECT *
         FROM OPENSTRING( VALUE geocode_result )
              WITH (return_code integer,
                    accuracy integer,
                    lat double,
                    lon double)
              OPTION( DELIMITED BY ',' ) AS location
         ;



28 – October 4, 2011
         MERGE
         Merge data from one table to another


         MERGE INTO myTargetTable ( I1, I2, .. ., In )
         USING mySourceTable
         ON TT.I1 = ST.U1 AND TT.I2 = ST.U2
         WHEN NOT MATCHED THEN
               INSERT ( I1, I2, .. In )
               VALUES ( ST.U1, ST.U2, ..., ST.Un )
         WHEN MATCHED THEN
               UPDATE SET TT.I1 = ST.U1,
                           TT.I2 = ST.U2, ...
                           TT.In = ST.Un;




29 – October 4, 2011
TOOLS
         SYBASE CENTRAL




31 – October 4, 2011
         SQL ANYWHERE MONITOR




32 – October 4, 2011
THANK YOU
         FEEDBACK

         • Other suggestions
            – Procedure for pivoting data – does Ani have anything?
            – Full Text Search – can also search numeric data
                          Plugin architecture for indexing text data
               – OLAP Window function – Glenn’s whitepaper – moving
                 average
                          My slides on OLAP


         • State where people can get the examples on a slide – on blog




37 – October 4, 2011

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:2/16/2013
language:Unknown
pages:34