After a successful gateway installation, use the demonstration files stored in the directory $ORACLE_HOME/dg4sybs/demo, where $ORACLE_HOME is the directory under which the gateway is installed. The directory contains the following demonstration files:

The case studies are based on the GTW_EMP, GTW_DEPT, and GTW_SALGRADE tables and the stored procedures InsertDept and GetDept. If the demonstration tables and stored procedures have not been created in the Sybase database, use the bldsybs.sql script to create them. Enter the following:

$ isql -USCOTT -PTIGER2 -ibldsybs.sql

The script creates the demonstration tables and stored procedures in the Sybase database accordingly:

CREATE TABLE GTW_EMP (
EMPNO      SMALLINT NOT NULL 
ENAME      VARCHAR(10),
JOB        VARCHAR(9),
MGR        SMALLINT,
HIREDATE   DATETIME,
SAL        NUMERIC(7,2),
COMM       NUMERIC(7,2),
DEPTNO     SMALLINT)
go

CREATE TABLE GTW_DEPT (
DEPTNO     SMALLINT NOT NULL,
DNAME      VARCHAR(14),
LOC        VARCHAR(13))
go

CREATE TABLE GTW_SALGRADE (
GRADE      MONEY,
LOSAL      NUMERIC(9,4),
HISAL      NUMERIC(9,4))
go

SET CHAINED ON
go

DROP PROCEDURE InsertDept
go

CREATE PROCEDURE InsertDept (@dno INTEGER,
   @dname VARCHAR(14), @loc VARCHAR(13))
AS INSERT INTO GTW_DEPT VALUES (@dno, @dname, @loc)
go

DROP PROCEDURE GetDept
go

CREATE PROCEDURE GetDept (@dno INTEGER, @dname VARCHAR(14) OUTPUT)
AS SELECT @dname=DNAME FROM GTW_DEPT WHERE DEPTNO=@dno
go

Case 1 demonstrates the following:

• A simple query

• A simple query retrieving full date information

The first query retrieves all the data from GTW_DEPT and confirms that the gateway is working correctly. The second query retrieves all the data from GTW_EMP including the time portion of the hire date because the default date format was set to DD-MON-YY HH24:MM:SS for the session by an ALTER SESSION command.

Case 2 demonstrates the following:

• The functions SUM(expression) and NVL(expr1, expr2) in the SELECT list

• The GROUP BY and HAVING clauses

This query retrieves the departments from GTW_EMP whose total monthly expenses are higher than $10,000.

Case 3 demonstrates the following:

• Joins between Sybase tables

• Subselects

The query retrieves information from three Sybase tables and relates the employees to their department name and salary grade, but only for those employees earning more than the average salary.

Case 5 demonstrates data dictionary mapping. It retrieves all the tables and views that exist in the Sybase database that begin with "GTW".

Case 6 demonstrates the gateway pass-through feature that allows an application to send commands or statements to Sybase.

This case demonstrates:

• A pass-through UPDATE statement using bind variables

• A pass-through SELECT statement

Case 7 demonstrates the gateway executing a stored procedure in the Sybase database.

Before running the script, make sure that chained mode is set on, by issuing the following command in Sybase:

set chained on