Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


4 - Run ISQL In Batch Mode

ISQL can be run in non-interactive "batch" mode just like the other command line utilities. Not only does this make it easier to run repetitive SQL commands but it also lets you gather actual performance statistics in an output text file.

Figure 4A shows how to run ISQL in batch mode. TEST.SQL is named as the input file containing the SQL statements to execute. This example assumes the server and database are already up and running so the -c parameter only needs to provide the user id and password.

Figure 4A - Run ISQL In Batch Mode When The Server Is Already Running 
ISQL -c "UID=DBA;PWD=SQL" c:\test\test.sql

Figure 4B shows how to run ISQL in batch mode when a standalone database needs to be started. The -c parameter now includes the DBF subparameter to specify the database file location. The -s parameter is also used to specify the database engine command line parameter.

Figure 4B - Start A Standalone Database And Run ISQL In Batch Mode 
ISQL -c "DBF=c:\test\test.db;UID=DBA;PWD=SQL" -s "DBENG50" c:\test\test.sql

Figure 4C shows the TEST.SQL input command file to be run in batch mode. It includes a series of commands to create and fill a test table and search index, followed by three SELECT statements to test performance. The output of these SELECTS is captured by the ">&c:\test\isql.txt" and ">>&c:\test\isql.txt" parameters at the end of the statements.

The > operator tells ISQL to redirect the output of the SELECT to a text file, overwriting anything that might already be there. The >> operator tells ISQL to do the same but this time append the new output to the end of the file if there's already something there. The & operator tells ISQL to include performance statistics in the output.

The two SET TEMPORARY OPTION COMMAND_DELIMITER statements are used to tell ISQL to treat everything between the BEGIN and END as a single execution unit by changing the delimiter from the semicolon to a different character and then changing it back.

Figure 4C - >& And >>& Operators In The ISQL Input File TEST.SQL 
CREATE TABLE query1

   ( primary1 INTEGER NOT NULL,

     search1 INTEGER NOT NULL,

     search2 INTEGER NOT NULL,

     data1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1) );


CREATE INDEX iquery1 ON query1

   ( search1, search2 );


SET TEMPORARY OPTION COMMAND_DELIMITER = '`';

BEGIN

DECLARE i INTEGER;

SET i = 1;

WHILE i <= 10000 LOOP

   INSERT INTO QUERY1 ( primary1, search1, search2, data1 )

      VALUES ( i, i, i, i );

   SET i = i + 1;

END LOOP;

END`

SET TEMPORARY OPTION COMMAND_DELIMITER = ';'`


SELECT * FROM query1

 WHERE search1 = 44 >&c:\test\isql.txt;


SELECT * FROM query1

 WHERE search1 = 44

   AND search2 = 44 >>&c:\test\isql.txt;


SELECT * FROM query1

 WHERE search2 = 44 >>&c:\test\isql.txt;

Figure 4D shows the output captured in the text file. As you can see, this batch output is more complete than the interactive ISQL Statistics window because it includes actual I/O counts and execution times as well as execution plan estimates.

Figure 4D - Actual Performance Statistics In The ISQL Output File ISQL.TXT 
% Executing command:

% SELECT * FROM query1

%     WHERE search1 = 44;

% Estimated 1 rows in query (I/O estimate 2)

% PLAN> query1 (iquery1)

44,44,44,44

% 1 record(s) selected -- actual I/O 0

% select time including I/O 0.0 seconds - current time 09:13:52


% Executing command:

% SELECT * FROM query1

%     WHERE search1 = 44

%     AND search2 = 44;

% Estimated 1 rows in query (I/O estimate 2)

% PLAN> query1 (iquery1)

44,44,44,44

% 1 record(s) selected -- actual I/O 0

% select time including I/O 0.1 seconds - current time 09:13:52


% Executing command:

% SELECT * FROM query1

%     WHERE search2 = 44;

% Estimated 3 rows in query (I/O estimate 404)

% PLAN> query1 (seq)

44,44,44,44

% 1 record(s) selected -- actual I/O 1

% select time including I/O 0.8 seconds - current time 09:13:52

If you look in the Help file DBENG50W.HLP you won't find much information about running ISQL in batch mode. In fact, you won't even find the correct specifications for the command line switches. Figures 4E and 4F show a rare discrepancy between what the Help says and what the program actually expects.

Figure 4E - What The Help Says About ISQL 
Syntax

 ISQL [switches] [isql-command]


Switch                      Description

-b                       Do not print banner

-c "keyword=value; ..."  Supply database connection parameters

-k                       Close window when finished (RTSQL only)

-q                       Quiet mode—no windows or messages

-v                       Verbose—output information on commands

-x                       Syntax check only—no commands executed

Figure 4F - What ISQL Says About Itself

[Home] [Table of Contents] [Previous Section] [Next Section]