rem rem $Header: catdefrt.sql,v 1.7 1995/12/08 12:16:12 jstamos Exp $ rem Rem Copyright (c) 1992 by Oracle Corporation Rem NAME Rem catdefrt.sql - CATalog DEFeRred rpc Tables Rem DESCRIPTION Rem create deferred rpc tables Rem RETURNS Rem Rem NOTES Rem This is called from catdefer.sql Rem MODIFIED (MM/DD/YY) Rem jstamos 12/04/95 - 324303: use index to avoid sorting the queue Rem jstamos 08/17/95 - code review changes Rem jstamos 08/16/95 - add comments to tables Rem hasun 01/31/95 - Modify tables for Rep3 - Object Groups Rem hasun 01/31/95 - merge changes from branch 1.1.720.2 Rem hasun 01/11/95 - Add fix to resolve duplicate SCNs Rem dsdaniel 12/08/94 - add def _destinaton constraint Rem dsdaniel 12/08/94 - name defcall primary Rem dsdaniel 11/25/94 - eliminate deftrandest, ect Rem dsdaniel 11/25/94 - Branch_for_patch Rem dsdaniel 11/22/94 - Creation -- create the call table. One row for each deferred call. CREATE TABLE system.def$_call ( deferred_tran_db VARCHAR2(128), -- origin db deferred_tran_id VARCHAR2(22), -- transaction id buffer_number NUMBER, -- parameters buffer number callno NUMBER, -- UID of call, orders calls in transaction CONSTRAINT def$_calls_primary PRIMARY KEY(deferred_tran_db, deferred_tran_id, callno, buffer_number), schemaname VARCHAR2(30), -- schema name NOT NULL packagename VARCHAR2(30), -- package name NOT NULL procname VARCHAR2(30), -- procedue name NOT NULL argcount NUMBER, -- # of args NOT NULL parm_buffer LONG RAW, -- parameters buffer -- THE FOLLOWING COLUMNS ARE NONLY NON-NULL FOR THE FIRST (lowest callno, -- buffno = 1 destination_list CHAR(1), -- R = RepSchema, D = def$_calldest origin_user_id NUMBER, -- userid deferring tran origin_user VARCHAR2(30), -- user deferring tran (used at remote) delivery_order NUMBER, -- order to deliver to destinations origin_tran_id VARCHAR2(22), -- original tid (if copied) origin_tran_db VARCHAR2(128), -- origial node start_time DATE, -- time original tid started destination_count INTEGER, -- set on first push commit_comment VARCHAR2(50) -- commit comment ) / comment on table SYSTEM.DEF$_CALL is 'Information about all deferred remote procedure calls' / comment on column SYSTEM.DEF$_CALL.DEFERRED_TRAN_DB is 'Originating database of the deferred call' / comment on column SYSTEM.DEF$_CALL.DEFERRED_TRAN_ID is 'The unique ID of the associated transaction' / comment on column SYSTEM.DEF$_CALL.BUFFER_NUMBER is '1-based counter for calls with multiple buffers' / comment on column SYSTEM.DEF$_CALL.callno is 'Unique ID, orders calls within a transaction' / comment on column SYSTEM.DEF$_CALL.SCHEMANAME is 'The schema name of the deferred call' / comment on column SYSTEM.DEF$_CALL.PACKAGENAME is 'The package name of the deferred call' / comment on column SYSTEM.DEF$_CALL.PROCNAME is 'The procedure name of the deferred call' / comment on column SYSTEM.DEF$_CALL.ARGCOUNT is 'The number of arguments to the deferred procedure call' / comment on column SYSTEM.DEF$_CALL.PARM_BUFFER is 'Marshalled parameters for the deferred call' / comment on column SYSTEM.DEF$_CALL.DESTINATION_LIST is 'Determine destinations from def$_calldest (D) or repcat (R)' / comment on column SYSTEM.DEF$_CALL.ORIGIN_USER_ID is 'ID of user deferring a transaction' / comment on column SYSTEM.DEF$_CALL.ORIGIN_USER is 'Name of user deferring a transaction' / comment on column SYSTEM.DEF$_CALL.DELIVERY_ORDER is 'Total ordering on transactions' / comment on column SYSTEM.DEF$_CALL.ORIGIN_TRAN_ID is 'Original deferred_tran_id if copied' / comment on column SYSTEM.DEF$_CALL.ORIGIN_TRAN_DB is 'Original deferred_tran_db if copied' / comment on column SYSTEM.DEF$_CALL.START_TIME is 'Time original transaction started' / comment on column SYSTEM.DEF$_CALL.DESTINATION_COUNT is 'Number of outstanding destinations for R-type transactions' / comment on column SYSTEM.DEF$_CALL.COMMIT_COMMENT is 'Any user-supplied comment' / rem create an index on delivery order to speed things up create index system.def$_tranorder on system.def$_call(delivery_order, deferred_tran_id, deferred_tran_db) / -- create the table where the exceptions get logged. One row for each -- transactionXorigin_node when the execution of the transaction at -- this node encountered an error. CREATE TABLE system.def$_error( deferred_tran_db VARCHAR2(128), -- node origination/copying txn deferred_tran_id VARCHAR2(22), -- transaction id callno NUMBER, -- UID of call -- forign key constrain on call not used. Users can create error transacitons -- with null or invalid callno - it is for documenation only -- dummy_buffer_number NUMBER DEFAULT 1, -- CONSTRAINT def$_error_call -- FOREIGN KEY(callno, dummy_buffer_number, deferred_tran_db) -- REFERENCES system.def$_call(callno, buffer_number, deferred_tran_db), destination VARCHAR2(128), -- dblink transaciton destined to CONSTRAINT def$_error_primary PRIMARY KEY(deferred_tran_db, deferred_tran_id, destination), error_time DATE, -- time at which -- conflit occured error_number NUMBER, -- error -- number reported error_msg VARCHAR2(2000)) -- error message / comment on table SYSTEM.DEF$_ERROR is 'Information about all deferred transactions that caused an error' / comment on column SYSTEM.DEF$_ERROR.DEFERRED_TRAN_DB is 'The database originating or copying the deferred transaction' / comment on column SYSTEM.DEF$_ERROR.DEFERRED_TRAN_ID is 'The ID of the transaction that originated or copied the deferred transaction' / comment on column SYSTEM.DEF$_ERROR.CALLNO is 'Unique ID of call that caused an error' / comment on column SYSTEM.DEF$_ERROR.DESTINATION is 'Database link used to address destination' / comment on column SYSTEM.DEF$_ERROR.ERROR_TIME is 'Time error occurred' / comment on column SYSTEM.DEF$_ERROR.ERROR_NUMBER is 'Oracle error number' / comment on column SYSTEM.DEF$_ERROR.ERROR_MSG is 'Error message text' / CREATE TABLE system.def$_destination( dblink VARCHAR2(128) -- queue name CONSTRAINT def$_destination_primary PRIMARY KEY, last_delivered NUMBER -- scn (from deliver_order column of def$_call) DEFAULT 0 NOT NULL, last_tran_id VARCHAR2(22), -- transaction id last delivered last_tran_db VARCHAR2(128), -- node origination/copying last delivered txn disabled CHAR(1), -- T = propogtion to dest disabled F = enabled job NUMBER, -- number of job which does the push last_txn_count NUMBER, -- number of transacitons executed lat push last_error NUMBER, -- sqlcode from last push last_msg VARCHAR2(2000))-- error message from last push / comment on table SYSTEM.DEF$_DESTINATION is 'Information about propagation to different destinations' / comment on column SYSTEM.DEF$_DESTINATION.DBLINK is 'Destination' / comment on column SYSTEM.DEF$_DESTINATION.LAST_DELIVERED is 'Value of delivery_order of last transaction propagated' / comment on column SYSTEM.DEF$_DESTINATION.LAST_TRAN_ID is 'Transaction ID of last transaction propagated' / comment on column SYSTEM.DEF$_DESTINATION.LAST_TRAN_DB is 'Source of last transaction propagated' / comment on column SYSTEM.DEF$_DESTINATION.DISABLED is 'Is propagation to destination disabled' / comment on column SYSTEM.DEF$_DESTINATION.JOB is 'Number of job that pushes queue' / comment on column SYSTEM.DEF$_DESTINATION.LAST_TXN_COUNT is 'Number of transactions pushed during last attempt' / comment on column SYSTEM.DEF$_DESTINATION.LAST_ERROR is 'Oracle error number from last push' / comment on column SYSTEM.DEF$_DESTINATION.LAST_MSG is 'Error message from last push' / -- create the table that identifies a call to be executed -- at a remote node. One row for each callsXnode when the -- destination_list is D CREATE TABLE system.def$_calldest( deferred_tran_db VARCHAR2(128), -- deferred transaction db deferred_tran_id VARCHAR2(22), -- deferred transaction id callno NUMBER, -- call id dummy_buffer_number NUMBER DEFAULT 1, -- for forign key reference CONSTRAINT def$_calldest_call FOREIGN KEY(deferred_tran_db, deferred_tran_id, callno, dummy_buffer_number) REFERENCES system.def$_call(deferred_tran_db, deferred_tran_id, callno, buffer_number), dblink VARCHAR2(128), -- dblink to destination CONSTRAINT def$_calldest_primary PRIMARY KEY(deferred_tran_db, deferred_tran_id, callno, dblink), CONSTRAINT def$_call_destination -- Destination table must have a row FOREIGN KEY(dblink) REFERENCES system.def$_destination(dblink)) / comment on table SYSTEM.DEF$_CALLDEST is 'Information about call destinations for D-type and error transactions' / comment on column SYSTEM.DEF$_CALLDEST.DEFERRED_TRAN_DB is 'Originating database for the deferred transaction' / comment on column SYSTEM.DEF$_CALLDEST.DEFERRED_TRAN_ID is 'Transaction ID' / comment on column SYSTEM.DEF$_CALLDEST.CALLNO is 'Unique ID of call within transaction' / comment on column SYSTEM.DEF$_CALLDEST.DUMMY_BUFFER_NUMBER is 'Used for foreign key reference only' / comment on column SYSTEM.DEF$_CALLDEST.DBLINK is 'The destination database' / CREATE TABLE system.def$_defaultdest ( dblink VARCHAR2(128) -- dblink CONSTRAINT def$_defalutdest_primary PRIMARY KEY) / comment on table SYSTEM.DEF$_DEFAULTDEST is 'Default destinations for deferred remote procedure calls' / comment on column SYSTEM.DEF$_DEFAULTDEST.DBLINK is 'Default destination' / COMMIT /