Creating a Database Operation

Set up a database operation to extract data from a relational database. A data service—and the view model on which it is based—can use database operations as input sources.

1. To connect to the grid domain controller (GDC) for the Data Federation domain where you will create your database operation, right-click the GDC in Enterprise Explorer, then click Connect. The data catalog for the GDC’s domain appears beneath the GDC in Enterprise Explorer.
2. To locate your database connectors in Enterprise Explorer, expand Categories/ViewLibrary/DatabaseServices. DatabaseServices contains a folder for each database connector; the folder contains the database connector itself and its database operations.
3. (Optional) If you’re not sure which database connector you want, right-click on any of the items in the folders in DatabaseServices and select Get Info. WorkSpace Data Federation displays a summary description of the item.
4. Right-click on a database connector’s folder and select Database Operation. WorkSpace Data Federation launches the database operation wizard.
5. Complete the following information on the wizard pages:

   Table 1. Database connector selection page

   Field	Description
   Domain	Select the Data Federation domain where you want the database operation to be created.
   Database connector	Select a database connector for your new database operation to use.

   Table 2. Name and Description page

   Field	Description
   Name	Enter a name for this database operation.
   Description	(Optional) Enter some descriptive information about this database operation.

   Table 3. Basic Operation Properties page

   Field	Description
   SQL Statement	Enter the query that this database operation will execute. For example: select * from dept select * from emp where ENAME = ?
   Modifies Database	Click Yes if this database operation performs an update, insert, or other operation that writes to the back-end database.
   Caching	Choose the caching behavior for the results of this database operation.
   Run as	Specify which Data Federation user the database operation will be run as. To run the database operation as the current user, click The user running the operation. To run the database operation as the same user every time, regardless of which user or application calls it, click A specific user and enter a qualified user ID. Use this format: user-name@auth-service-name.auth-service-type.domain-name auth-service-type is one of Grid, Ldap, Nis. For example: wilma@DefaultAuthService.Grid.Bedrock Alternatively, click Browse to select a user.
   Calls a stored procedure	Select this option if the SQL statement involves a stored procedure in the underlying database.
   Supports batch operations	Select this option if the statement is an update that can be used with JDBC batch mode.

6. If your SQL statement requires any parameters, specify a data type for each parameter on the Parameter Types page. If desired, also enter descriptions for the parameters. Then click Next. The database operation execution dialog uses parameter descriptions to prompt the executing user for parameter values.
7. If your SQL statement requires any parameters, specify a value for each parameter on the Parameter Values page, then click Next. WorkSpace only uses these values when it executes the database operation to generate the schema. You will be able to supply different values at runtime.
8. Click Finish. WorkSpace Data Federation creates the new database operation. It appears in the data catalog under Categories/ViewLibrary/DatabaseServices/database-connector-name.
9. (Optional.) If the new database operation does not update the database, right-click it and select Generate Schema.
   Note: WorkSpace executes the database operation to generate its schema. You might not want to update the database to get a schema. If you do not generate schema, WorkSpace generates it the first time it executes the database operation.
   WorkSpace uses the schema when the database operation is used to generate a SQL view or as an input source for a view model.

• Database Operations
  A database operation is the vehicle through which users and applications have access to data in relational databases.
• Rules for Aliases in DBOPs that Contain Aggregate Functions
  If your database operation includes a SQL statement that involves aggregate functions such as SUM, you must use aliases for any column names to which such aggregate functions refer.

For product-related issues, contact Sybase Technical Support at 1-800-8SYBASE. Send your feedback on this help topic directly to Sybase Technical Publications: pubs@sybase.com