Exit isql [Description]

isql can be started in Unix system command mode. [Command format]

isql [Options]

[Option description]

-U username One of login accounts allowing user to access SQL Server. -P password The password allowing user to access the current SQL Server. -S Server The name of specified SQL Server which is allowed to link with user. -i inputfile Specify the input file name.

-o outputfile Specify the output file name.

-w column_width Set up screen volumn width for output.

[Example]

Start isql as user "sa" and the password for current SQL Server access is "1234":

$ isql -U sa -P 1234

or:

$ isql -U sa

Password: (password does not display)

Note:

z In the above example, the user name and password behind –U and –P can use some other characters

than spaces. For example: isql -Usa -Pserver1234

z If starting normally, isql will display a command prompt "1> " like an operating system. Here "1" is the

command line number -- when multiple lines of commands are typed in, prompt "2> ", "3> " etc. will appear to indicate the number of command lines until they are submitted to the Sybase system for execution. End a command with "go" when typing is finished, the Sybase system will begin executing and display the result, or output the result to a specific file. If there is a mistake in the command line, use command "reset" to clear the query buffer and return to prompt "1> ".

z If isql starts properly, the Sybase Server should also have started successfully. Of course, the reasons

why isql does not start up properly are complex: it could be either the Sybase Server is not running, or the user has typed in incorrect login name or password.

II. Exit isql [Description]

To quit isql, type "quit" or "exit" on isql command prompt.

[Command format] quit (or exit) [Example]

Quit isql enviornment:

1> quit $ or: 1> exit $

C.2.2 bcp

Batch copying command "bcp" is used to copy database tables to the operating system files or vise versa. This is a quick and convenient way of data transfer between database tables and operating system files.

[Command format]

bcp [[database.]owner.] database tables {copy to in|out} data files [optional] [Parameter description]

-f formatfile It means the user has saved a format file (extension: ".fmt") when processing the same table last time which includes the full path.

-U username Allow user to specify a registration name when linking to the server (the default value is the user name identified by the user environment.)

-P password Allow user to set the current SQL Server password. (If it is not specified here, the system will prompt the user to input a password when logging on.)

-S Server Allow user to specify the SQL Server name he is trying to link. (If the server name is not specified, the $DSQUERY value will be used; if $DSQUERY value is not available, "Sybase" will be used.)

-c File types (for example: c-char, t-text, i-int etc.)

[Parameter description]

Database: The database in which the table is. Owner: The owner of the data table to be copied. Database table: Data table to be copied.

Copying direction in/out: Copying direction ("in" means from system files to data tables; "out" means from data tables to system files).

Data files: Source data files or target data files (depending on the copying direction), including the full path and file description.

[Example]

Backup table "history" in database "warn" as "history.dat" (file type: char):

$ bcp warn.. history out history.dat -U sa -P 1234 -c

Starting copy...

Caution:

If the copying direction of the above command is changed from "out" to "in", it will restore the data table.

C.2.3 Showserver

[Description]

Command "showserver" can be used to show the SQL Server currently running, but remember the user here must be a Sybase user.

[Command format] showserver [Example]

Show SQL Server currently running in this machine:

$ showserver

UID PID PPID C STIME TTY TIME CMD

sybase 204 203 1 Feb 19 944: 19 /home1/sybase/bin/dataserver -ssybserver -d/dev/rdsk/c0t0d0s4 -e/home1/sybase/i

sybase 208 206 0 Feb 19 0: 00 /home1/sybase/bin/backupserver -Ssybserver_back -e/home1/sybase/install/sybserv

The above information indicates that the active/standy server have both started.

Caution:

If command "showserver" is executed while SQL Server is running, system will print all the above information; otherwise, only the title will be printed.

C.2.4 Startserver

[Description]

Command "startserver" is used to start SQL Server.

[Command format] startserver [Options] [Option description]

-f run_serverfile Specifying a run-server file. (This file is referenced each time SQL Server restarts. Its name in the machine is "RUN_sybserver" or "RUN_sybserver_back".)

-m Starting SQL Server in the single-user mode, which is used to restore master database.

C.2.5 Shutdown

[Description]

The system administrator can use command "shutdown" to shut down SQL Server or Backup Server. This command is used in the "isql" enviornment.

[Command format]

1) Shut down SQL Server

shutdown [Options]

2) Shut down Backup Server

shutdown Backup Server [Options] [Option description]

with {wait|nowait} Controlling whether the system shuts down immediately or not (Parameter "with nowait" will skip the checkpoint operation and shut down the system straight away, as a result it will take much more time and efforts for the system auto-restoration to complete when it reboots next time. Parameter "with wait" does the opposite.)

[Parameter description]

Note:

z Always shut down the backup server prior to the active server.

z If no server name is specified after "shutdown" in the command line, the SQL Server currently running

will be shut down. On "shutdown", SQL Server will do the following: Prohibit all user login, except the system administrator

Perform “checkpoint” operation: saving all the modified pages from memory to disk. Wait until all SQL Server statements or procedures finish.

Shutting down SQL Server in this way will minimize the auto-recovery workload when SQL Server reboots.

z By default, Backup Server will shut down with parameter "with wait", so that the system will finish all

the on-going data transfer and loading before terminating the Backup Server procedure. On receipt of the "shutdown" command, Backup Server will stop processing any new data transfer or loading session.

C.3 T-SQL

T-SQL (Tansact-SQL) is a kind of enhanced SQL (Structured Query Language), which is compliant with IBM's SQL and most of other SQLs. It has been expanded on the SQL basis. Lots of new functions have been included while users' dependency on the programming lauguage has been minimized.

Standard SQL was originally considered as a query and execution language other than a programming language. As an expanded SQL, T-SQL has included new features such as program flow control structure, local variants while allowing DBA to create stored procedure and trigger, etc.

Standard SQL consists of Data Definition Language (DDL), Data Manipulation Language (DML) and Data Control Language (DCL). In this section, we'll mainly discuss DDL and DML.