Corrections - RisingRoad SQL Anywhere and MobiLink Professional Services

\\\\\ for page 331, where first line below is missing... Before CALL p_error2 p_error2 before CALL p_error1 p_error1 before RAISERROR p_error1 after RAISERROR p_error2 after CALL p_error1 Error detected in p_error2... @@ERROR = 20000 ERRORMSG() = RAISERROR executed: Diagnostic number = 999, string = "AAA". After CALL p_error2 Error detected in outer block... @@ERROR = 20000 ERRORMSG() = RAISERROR executed: Diagnostic number = 999, string = "AAA". \\\\\ Corrections - RisingRoad SQL Anywhere and MobiLink Professional Services

RisingRoad

Corrections to SQL Anywhere Studio 9 Developer's Guide

(To buy the book: www.amazon.com for ISBN 1556225067)

Page 64 in Section 2.4 ISQL Input

The BNF syntax for "ISQL INPUT with inline data" is missing a semicolon after the INPUT statement, before the actual data. Here is the corrected syntax:

   <isql_inline_input>                 ::= INPUT <inline_input_option_list> ;
                                           <inline_data>
                                           <end_of_input_marker>

For your convenience, here is the expanded syntax:

   <inline_input_option_list>          ::= <input_option_list> not including FROM or PROMPT
   
   <input_option_list>                 ::= { <input_option> }

   <input_option>                      ::= INTO [ <owner_name> "." ] <target_table_name> -- required
                                         | "(" <column_name_list> ")" -- default all columns
                                         | FROM <input_file>
                                         | PROMPT
                                         | BY NAME        -- for self-describing file formats
                                         | BY ORDER       -- default
                                         | COLUMN WIDTHS "(" <column_width_list> ")" -- for FORMAT FIXED
                                         | DELIMITED BY <input_delimiter>            -- default ','
                                         | ESCAPE CHARACTER <input_escape_character> -- default '\'
                                         | FORMAT <input_format>                     -- default ASCII
                                         | NOSTRIP        -- default strip unquoted trailing blanks

   <inline_data>                       ::= lines of data immediately following the INPUT statement

   <end_of_input_marker>               ::= END -- all by itself on a separate line
                                         | end of the executed lines in the "SQL Statements" pane
                                         | end of file in the ISQL command file

Page 431 in Section 10.6.4 Index Fragmentation

The WHERE clause in the procedure p_index_fragmentation should read:

   WHERE USER_NAME ( SYSTABLE.creator ) = @owner_name

instead of:

   WHERE USER_NAME ( SYSTABLE.creator ) = 'DBA'

Here is the corrected procedure:

CREATE PROCEDURE p_index_fragmentation ( IN @owner_name VARCHAR ( 128 ) )
RESULT ( table_name       VARCHAR ( 128 ),
         index_name       VARCHAR ( 128 ),
         rows             UNSIGNED BIGINT,
         leaf_pages       UNSIGNED INTEGER,
         levels           INTEGER,
         density          NUMERIC ( 8, 6 ),
         concerns         VARCHAR ( 100 ) )
BEGIN
SELECT sa_index_levels.TableName  AS table_name,
       sa_index_levels.IndexName  AS index_name,
       SYSTABLE.count             AS rows,
       sa_index_density.LeafPages AS leaf_pages,
       sa_index_levels.Levels     AS levels,
       sa_index_density.Density   AS density,
       STRING (
          IF levels > 2
             THEN 'deep'
             ELSE ''
          ENDIF,
          IF levels > 1 AND density < 0.5
             THEN IF levels > 2
                     THEN ', low density'
                     ELSE 'low density'
                   ENDIF
              ELSE ''
          ENDIF ) AS concerns
  FROM sa_index_levels ( owner_name = @owner_name )
       INNER JOIN sa_index_density ( owner_name = @owner_name )
               ON sa_index_density.TableName = sa_index_levels.TableName
              AND sa_index_density.IndexName = sa_index_levels.IndexName
       INNER JOIN SYSTABLE
               ON SYSTABLE.table_name = sa_index_density.TableName
 WHERE USER_NAME ( SYSTABLE.creator ) = @owner_name
 ORDER BY table_name,
       index_name;
END;

This page was last updated on October 22, 2004.