Passive Gateways

Passive Gateways are the “Poor Man’s Gateway.” In most cases, they are simple programs written in-house to refresh portions of two different databases. However, if you do not plan your Passive Gateway project well, it can end up costing more than any other solution. Furthermore, if you need greater synchronization between databases, a Passive Gateway might just be a “redesign of the wheel” that can be avoided using one of Oracle’s gateway options. It is important to use these simple gateways only when the task is not too complex.

The Zen Gateway–—Does It Really Exist?

I have termed a Passive Gateway as one where data is moved across one database to another remote database manually. This data movement can be termed as “passive” because it does not occur within the structure of transactions but is only moved when a user-defined process is started. It is also passive because from the point of view of each database and each process, the gateway itself does not exist. The Passive Gateway comes into existence only for a brief moment in time.

A monthly data refresh between two separate computer systems is an example of a Passive Gateway. Data is simply transferred as a series of files by a user-defined set of programs. These programs move data first out of one computer’s database into an operating system file and then move it into another computer’s file system and database. Both databases consider these programs no different from any other database-related process that moves data from the database to a flat file.

The advantage of the Passive Gateway is that it is usually less expensive. To unload data from one database, transfer it, and then load it into another database is much simpler than the low-level translations of SQL*Net and Oracle’s Transparent Gateways. You can perform this operation with standard tools that do not cost extra—no need to purchase SQL*Net with this method.

The disadvantage with Passive Gateways is that they are so simple and fit such a specified need that they usually need to be custom built. Yet if development costs are managed for a simple Passive Gateway, the cost of Oracle’s SQL*Net product and of Oracle’s Open Gateway products will be greater. The hours spent simply configuring SQL*Net and an Open Gateway can be higher than the development of a simple Passive Gateway.

It is important to decide which kind of gateway you need. If you want to keep replicated databases running at two sites, probably designing your own data-transfer and logging routines will be too complicated. Yet if you need data from one machine periodically sent to another, consider a Passive Gateway.

Building a Passive Gateway, Mainframe to Oracle Example

Look at your fictitious airline business (Figure 8.6). Assume that there is only a frequent-flier Oracle database that is running over the Web; it cannot book flights. The database to reserve and purchase flights exists separately on a mainframe.

Figure 8.6.  With a transaction such as a monthly refresh that does not need to occur for each transaction, many times a Passive Gateway can handle this simpler relation.

The frequent-flier application is running on a UNIX server that is running the Oracle8 database and the Web server. The main database of airline purchases exists on a mainframe running DB2. The only reason that these applications need to talk to each other is because flight data must be moved monthly from the mainframe database and used to update the Oracle/UNIX frequent-flier database.

Because data is moving from the mainframe to the UNIX environment, you first must extract the information from DB2 and convert it to a flat file. The query might be something such as this:

EXEC SQL

Select

Customer_name, sum(miles_traveled)

From flight_activity

Where month = :current_month

Group By Customer_name



END EXEC

This query can be written with SQL embedded in COBOL that writes to a file (dataset) on the mainframe with the results of the query as in Figure 8.7. You can also use DB2’s SPUFI tool instead to execute this query and spool data to a dataset.

Figure 8.7.  With a Passive Gateway for a periodic refresh, you see many different user- created programs working together yet not communicating with one another.

You would create a JCL script that would run once a month to execute this COBOL job, on the night of the last day of the month. The results of the job would sit in a dataset called monthact.

That same evening on the Oracle UNIX box, there would be a cron job command that might run every 15 minutes to ping the network and see if the MVS dataset existed and was available. After the data finished loading, a UNIX command from the Oracle box would pull the dataset through FTP with the Get command and move the information to a file on the UNIX file system.

After this UNIX cron job finished and the UNIX file existed with the month of new flight activity, the cron script would call a PRO*C program. This program would read in the rows of the data that were extracted from the DB2 Select statement:

Our data:

 1: Ernie Salesnutt    34000

 2: Bill Gateway        12000

 3: Garuda Deli        10000

 4: GetMainframeData.pc - Program Shell:

 5: EXEC SQL BEGIN DECLARE SECTION;

 6: VARCHAR    customer_name[48];

 7: int        miles;

 8: EXEC SQL END DECLARE SECTION;

 9:

10: EXEC SQL INCLUDE SQLCA;

11:

12: EXEC SQL WHENEVER SQLERROR GOTO err;

13:

14: FILE *fp_data, *fp_log;

15:

16: main()

17: {

18:     init();

19:

20:     open_file_from_mainframe(fp_data, “monthact.dat”);

21:     open_log(fp_log,  “monthact.log”);

22:

23:     declare_cursors();

24:     while ( read_a_row() == 0)

25:     {

26:         update_frequent_fliers();

27:         add_to_log();

28:     }

29:     close_cursors();

30:

31:     close_files();

32:     clean_up();

33: }

For each row, the program would perform an update of the UNIX Oracle frequent-flier database and the table Customer_Miles. The C program would issue the following SQL:

EXEC SQL

Update Customer_Miles

Set total_miles = total_miles + :miles_just_read_in

Where customer_name = :name_I_just_read_in;