Performing common tasks

The following are some common tasks users perform with the Adaptive Server Plug-in.

For more information about all the following tasks, see the Adaptive Server Plug-in online help.

Starting and stopping Adaptive Server

If the Unified Agent is monitoring Adaptive Server, you can start, stop, and restart the server by right-clicking on the server and selecting Shutdown, Start, or Restart.

If the Unified Agent is not monitoring Adaptive Server, you can shutdown the server by selecting Shutdown.

Connecting to Adaptive Server

You can connect to an Adaptive Server by any of these methods:

• Select the Connect icon from the tool bar.

• Right click on Adaptive Server Enterprise and select Connect from the menu.

• Right click on any server group and select Connect from the menu.

  The connected server is displayed in the Default server group if the connection is initiated from the Adaptive Server Enterprise folder or the connect icon. The Plug-in displays “Connected to server” in the corresponding server group if the connection is initiated from the server group.

You can also specify a server to which you want to connect by any of the following:

• Specifying the server’s hostname and port number in the Connect dialog box.

• Selecting a pre-defined Adaptive Server from the server name dropdown list. This drop down list is derived from the servers listed in the interfaces file (UNIX) and sql.ini files (Windows) and LDAP servers.

• Discover which Adaptive Servers are available by clicking on Find in the Connect dialog. Before you can use this method, you must first define the discovery servers in Server Discovery tab located in the Adaptive Server Enterprise property page.

Creating a database

Before creating a database, make sure enough space is available on the database devices you plan to use.

To create a database:

• Right-click on the Add Database icon in the right-hand panel, or,

1. Select the Databases folder.

2. Choose File | New | Database or click on the Add Database option in the Databases folder. The Create a New Database wizard opens. The Create a New Database wizard asks for the following information:

   Table 4-1: Inputs to create a new database wizard

   Input	Description
   Database name	Enter a name for the database
   Database device	Specify the database device or devices on which to allocate the new database
   Database device size	Specify a size for each database device
   Data or log	Specify whether the database device will store data or the transaction log.
   With override	Specify with override if you want to store data and log on the same device.
   For load	If you are creating the database so you can restore it from a backup, check the For Load check box. This is the case only if you are recovering from media failure or if you are moving a database from one location to another.
   Guest account	Specify whether to create a guest user in the database.

If you do not enter a size, Adaptive Server allocates either the value of the database size configuration variable or the size of the model database, whichever is larger.

If you have limited storage and must put the transaction log and the data on the same logical device, specifying With Override allows Adaptive Server to maintain the log on separate device fragments from the data.

You cannot remove or change a database device after creating the database unless you first delete the database.

Deleting a database

Only the owner of a database can delete it.

To delete a database:

1. Select the database icon.

2. Choose Edit | Delete.

3. Confirm the deletion in the confirmation dialog box.

Adding a user

Database owners can add and delete users in the databases they own.

To create a user:

1. Expand the databases folder (select the “+” icon) and select the Users folder.

2. Choose File | New | User.

   The Add a New User wizard opens and asks for this information:

You can also select the Users folder. In the right pane, double-click the Add User icon.

Deleting a user

You cannot delete a user who owns objects. Since there is no command to transfer ownership of objects, you must delete objects owned by a user before you can delete the user. Also, you cannot delete a user who has granted permissions to other users without first revoking the permissions with cascade. If appropriate, re-grant the permissions to the other users.

Locking a login is a simple alternative to deleting a user.

To delete a user:

1. Select the user icon.

2. Choose Edit | Delete.

3. Confirm the deletion in the confirmation dialog box.

You can also select the user folder by right-clicking on the user icon and select Delete.

Before you delete a user:

1. Revoke the user’s command and object permissions with cascade.

2. Re-grant the permissions to the other users, if appropriate.

3. Delete the user’s objects.

Creating a table

Only a database owner or a user with create table permission can create a table.

To create a table:

1. In a database you are working in, select the User Tables folder.

2. Choose File | New | Table or click on the Add Table icon in the User Tables folder.

   The Table Editor opens.

3. In the Name box, enter a name.

4. From the Owner list, choose an owner. The default is “dbo”.

You can also select the User Tables folder. In the right pane, double-click the Add Table icon.

Deleting a table

Before you delete a table, be sure that no other objects reference it. If any objects reference it, you must edit those objects to avoid errors. To find out if other objects reference a table, check its dependencies.

Only table owners can delete tables.

To delete a table:

• Follow these steps:

  • Select the table icon.

  • Choose Edit | Delete.

  • Confirm the deletion in the confirmation dialog box, or,

• You can also select the table by right-clicking on the table icon and selecting Delete.

Creating a server group

To create a server group:

1. Select Adaptive Server Enterprise

2. Choose File | New | Server Group

3. Follow the steps provided by the Create New Server Group wizard.

You can also add a server group by double-clicking on the Add Server Group from the right-hand pane.

Getting server status

If the Unified Agent is monitoring Adaptive Server, check the server status by any of the following:

• Click on the server group to which the server belongs. Check the Status column in the Details pane of the server group.

• Click on the Adaptive Server Enterprise listed under Sybase Central, and then click on Servers tab on the right hand side panel. The server status is printed in the Status column.

• A green triangle on the lower right-hand side of the server icon indicates that Adaptive Server is running. A red square indicates that Adaptive Server is stopped.

Getting the server log

If the Unified Agent is monitoring Adaptive Server, retrieve the server log by selecting the server and clicking on the Server Log tab in the right-hand pane.

The server log is retrieved based on how you have configured the filter for the the server log. To configure the server log filtering, right-click on the server and select Server Log Filter. By default, the Adaptive Server Plug-in retrieves the last 1000 lines from the server log. You can configure the server filter to retrieve:

• The entire log file.

• The last n number of lines.

• The log from the last n number of days.

• The lines that match the regular expression

Logging SQL statements

To log all SQL statements executed through the Adaptive Server Plug-in:

• Right click on a server and select “Log SQL Statement.”

• Select whether you want SQL statements logged directly to a window or to a file.

Executing SQL statements

You can execute SQL statements from within the Adaptive Server Plug-in by using the Interactive SQL query tool. To start the Interactive SQL tool, you can either:

• Right-click the server on which you want to execute the SQL statements and select Open Interactive SQL from the menu, or

1. Click on Adaptive Server Enterprise.

2. Click the Utilities tab on the right-hand pane and select Interactive SQL

You can execute SQL statements simultaneously on a set of servers belonging to a server group:

1. Right -click the server group and choose Execute SQL.

2. Select the servers on which you want to execute the SQL statements

3. Click Execute.

The result set for each server is listed in the Result Set pane of the SQL Execution dialog.

Viewing SQL execution plan and cost information

You can use the Adaptive Server Plug-in to view a GUI version of the SQL execution plan for individual queries (much like a GUI version of showplan) and execution plans for all queries in a stored procedure. This GUI display includes nodes for each of the operators of the execution plan.

To get the GUI plan:

1. Start Interactive SQL.

2. Execute the query or stored procedure

3. Click on the plan tab in the Results pane of Interactive SQL

4. Select a query from the queries drop down list.

5. Click the Details tab to see the GUI plan of the selected query. Click on an operator node to see the detailed statistics for that node.

6. Click on the XML tab to see an XML representation of the execution plan for the selected query

7. Click on the Text tab to see the execution plan in a text format for the submitted queries

   For more information about Interactive SQL, see “Starting Interactive SQL”.

Viewing and updating object properties

You can view and modify the configuration of any object represented in the Adaptive Server Plug-in using the Property dialog.

To bring up the Property dialog:

1. Click on the object you want to view or modify.

2. Right-click on the object and select Properties.

3. Select the appropriate tab to perform your task.

4. Make any modification in the Property dialog.

5. Click on Apply, OK, or Cancel.

Generate the SQL text for creating an object

You can generate the SQL text required for creating an object, which allows you to reverse engineer the object. To generate SQL text, right-click on the object and select “Generate DDL.”

Viewing and updating Adaptive Server configuration parameters

You can view and update the Adaptive Server configuration parameters using the Server Properties dialog.

To view and update configuration parameters:

1. Right click on the server and select Configuration in the menu

2. Select the functional group from the drop down list in the Show Configuration Parameters

3. Find and select the parameter you want to view or update

4. Enter new valuing the value column if update is necessary

5. Click on Apply/OK/Cancel accordingly

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF