Command-line SQL Runner

You can execute SQL scripts by invoking Server Studio in a command line mode. This feature allows using Server Studio with an external scheduler and in automated application deployment tasks. SQL scripts can include special LOAD and UNLOAD SQL statements to import and export data files as part of script's logic. Another advantage of Command-line SQL Runner is an ability to execute very large SQL scripts without opening them in graphical SQL editors, which might save significant amount of system memory used by Server Studio application's process.

Invoking Server Studio in a command-line mode

Windows OS: execute 'ServerStudio.bat' located in AGS\serverstudio directory, where AGS is Server Studio installation directory.

UNIX/Linux: execute 'serverstudio' command, located in AGS/serverstudio, where AGS is Server Studio installation directory.

Server Studio command line usage:

-s server name
-h host name[:port] (e.g. production:9088)
-d database (by default sysmaster is used for initial connection)
-u user
-p password
-f SQL script file to execute
-prop connection property file (key=value pairs). Use it to set parameters like
DBDATE, CLIENT_LOCALE, etc.
-out output directory for SQL statement result sets
-outfilename SQL query result set file name formatting option: 0 (default) - append a timestamp to a file name, 1 - append a result set's sequential number to a file name
-l execution log file
-a append results to existing log file
-err specifies the maximum number of errors after which the tool should quit (by default all statements will be executed)
-help Server Studio command line usage

IMPORTANT NOTE: If Informix server utilizes trusted connections, you can omit username and password parameters in the command-line.

Output of SQL script when executed using Server Studio Command-line SQL Runner

SQL script can produce two types of output:

Result sets produced by SQL SELECT statements
Execution status messages, errors, statistics

Each result set produced by SELECT statement in a script file is placed into a separate file. Each result set file has the same name as a SQL script file with a suffix '_result_set_file identifier'. Depending on a value of an optional parameter '-outfilename', the file identifier is either a timestamp of a file creation or a sequential number of a result set produced by a SQL script. If '-outfilename' parameter is not specified, a timestamp is appended to a result set file name. A result set file has an extension '.UNL' (pipe-separated text file) – each column value is separated by a pipe character and each row is written into a separate line.

Example
If your script file name is "batch_update.sql' and you specify the option '-outfilename 1', the first result set produced by this script is placed into a file 'batch_update_result_set_1.unl'
If you specify the option '-outfilename 0' or do not specify '-outfilename' parameter at all, the first result set's file name is 'batch_update_result_20140916_1931_761.unl, where a timestamp format is 'yyyymmdd_hhmm_sec'

If you do not explicitly specify output directory for result set files using command-line '-out' parameter, result set files are created in a directory from where ServerStudio command is invoked. You can use '-out' parameter to specify any other directory to place result set files.

Execution status messages, error messages and statistics are placed into a single log file, which has a plain text format. If you do not specify '-l' parameter in a command-line, the log is displayed in a console window, from where ServerStudio command is executed. You can specify a name and full path for a log file using '-l' parameter in a command-line.

NOTE: If you do not specify '-out' parameter and execute 'serverstudio' command in a directory, which does not have file write permissions for a current user, result set files will not be created. You should either execute 'serverstudio' command from a current directory, in which you have file write permissions, or use '-out' parameter to specify output directory, in which you have file write permissions. For example, if ServerStudio is installed in 'C:\Program Files\AGS' under Windows 7 with User Access Control (UAC) enabled, most likely you will not have a file write permission for a directory where ServerStudio.bat is located (C:\Program Files\AGS\serverstudio). If you simply switch your current directory to 'C:\Program Files\AGS\serverstudio' and attempt to execute SQL script file using “serverstudio” command in this directory without '-out' parameter, result set files, which might be produced by your SQL script, will not be created.

Samples of command-line invocations

Invoking Server Studio from a command-line under Windows:

The following sample command executes all SQL statements in a script file 'c:\data\sql\ecommerce_app_deployment.sql' against database 'ecommerce' located on Informix server with a name 'ol_ids11_qa', host 'qahostgr2:9088'. All execution messages, including error messages, will be recorded into the file 'c:\data\sql\log_ecommerce_app_deployment.log', and all result set files will be created in 'c:\data\sql' directory
Server Studio is installed in a directory 'C:\Program Files\AGS'

In Windows command console (cmd.exe), execute the following commands:

c:\"Program Files"\AGS\serverstudio\serverstudio -s ol_ids11_qa -h qahostgr2:9088 -d ecommerce -u informix -p informix -f c:\data\sql\ecommerce_app_deployment.sql -l c:\data\sql\log_ecommerce_app_deployment.log -out c:\data\sql -outfilename 1

Invoking Server Studio from a command-line under Unix/Linux:

The following sample command executes all SQL statements in a script file '/usr/data/sql/ecommerce_app_deployment.sql' against database 'ecommerce' located on Informix server: servername 'ol_ids11_qa', host 'qahostgr2:9088', user 'informix', password 'informix'. All execution messages, including error messages, will be recorded into the log file '/usr/data/sql/log_ecommerce_app_deployment.log', , and all result set files will be created in '/usr/data/sql' directory
Server Studio is installed in a directory '/opt/AGS'

In a console window, execute the following commands:

cd /opt/AGS/serverstudio

./serverstudio -s ol_ids11_qa -h qahostgr2:9088 -d ecommerce -u informix -p informix -f /usr/data/sql/ecommerce_app_deployment.sql -l /usr/data/sql/log_ecommerce_app_deployment.log -out /usr/data/sql