analytics/informix/etc/bootcdc.sql

--
--
--  Licensed Materials - Property of IBM and/or HCL
--
--  IBM Informix Dynamic Server
--  (c) Copyright IBM Corporation 2008, 2011. All rights reserved.
--  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.
--
--

set lock mode to wait;
create database syscdcv1 with log;
database syscdcv1 exclusive;

{ interface version }
    create table informix.syscdcvers
	(
	majvers		informix.integer,	{ Current API major version }
	minvers		informix.integer	{ Current API minor version }
	);
insert into informix.syscdcvers values(1, 1);

{ data capture sessions }
create table informix.syscdcsess
	(
	sessid		informix.integer,	{session identifier	    }
	majvers		informix.integer,	{version of session behavior}
	minvers		informix.integer,	{version of session behavior}
	seqnum		informix.bigint,	{record sequence number}
	timeout		informix.integer,	{timeout associated with reads}
	createtime	informix.bigint,	{UTC time when session created}
	flags		informix.integer,	{reserved for future use}
	status		informix.integer,	{reserved for future use}
	errcode		informix.integer,	{error code		}
	errutc		informix.bigint		{UTC time when error generated}
	);
create unique index informix.syssesssidx on informix.syscdcsess(sessid);
revoke all on informix.syscdcsess from public as informix;

{ captured tables }
    create table informix.syscdctabs
	(
	sessid		integer,	{ session id 			}
	dbname		varchar(128),	{ database name			}
	tabname		varchar(128),	{ table name			}
	owner		char(32),	{ table owner			}
	seqnum		bigint,		{ table version identifier      }
	startutc	bigint,		{ UTC time at which capture started }
	userdata	integer,	{ uninterpreted user data	}
	fixedbytes	integer,	{ bytes in fixed-sized columns	}
	numfixed	integer,	{ number of fixed-size columns	}
	fixedcols	lvarchar(16000),{ fixed-size column names	}
	numvar		integer,	{ number of var-size columns	}
	varcols		lvarchar(16000),{ variable-size column names	}
	flags		integer,	{ reserved 			}
	status		integer 	{ reserved			}
	);
    create unique index informix.systabsidx on informix.syscdctabs(sessid);
    create index informix.sysnameidx on informix.syscdctabs(tabname);
    revoke all on informix.syscdctabs from public as informix;

{ packetization schemes }
    { WARNING: The numerical values associated with each symbolic name  }
    { may change without notice.  Do not use the numerical values       }
    { directly.  Always create an internal mapping table on startup,    }
    { based on the symbolic names.                                      }

    create table informix.syscdcpacketschemes
    	(
	schemenum		integer,	{ numeric value         }
	schemename		varchar(16),	{ symbolic name		}
	schemedesc		varchar(127) 	{ description           }
	);
    revoke all on informix.syscdcpacketschemes from public as informix;

    insert into informix.syscdcpacketschemes values(66, 'CDC_PKTSCHEME_LRECBINARY', 'Binary data format.');

{ record types }
    { WARNING: The numerical values associated with each symbolic name  }
    { may change without notice.  Do not use the numerical values       }
    { directly.  Always create an internal mapping table on startup,    }
    { based on the symbolic names.                                      }

    create table informix.syscdcrectypes
    	(
	recnum		integer,	{ numeric value                 }
	recname		varchar(16),	{ symbolic name			}
	recdesc		varchar(127) 	{ description                   }
	);
    revoke all on informix.syscdcrectypes from public as informix;

    insert into informix.syscdcrectypes values(1, 'CDC_REC_BEGINTX', 'Begin Transaction');
    insert into informix.syscdcrectypes values(2, 'CDC_REC_COMMTX', 'Commit Transaction');
    insert into informix.syscdcrectypes values(3, 'CDC_REC_RBTX', 'Rollback Transaction');
    insert into informix.syscdcrectypes values(40, 'CDC_REC_INSERT', 'Insert row');
    insert into informix.syscdcrectypes values(41, 'CDC_REC_DELETE', 'Delete row');
    insert into informix.syscdcrectypes values(42, 'CDC_REC_UPDBEF', 'Update row before image');
    insert into informix.syscdcrectypes values(43, 'CDC_REC_UPDAFT', 'Update row after image');
    insert into informix.syscdcrectypes values(62, 'CDC_REC_DISCARD', 'Discard log records after LSN');
    insert into informix.syscdcrectypes values(119, 'CDC_REC_TRUNCATE', 'Truncate table');
    insert into informix.syscdcrectypes values(200, 'CDC_REC_TABSCHEMA', 'Format of I/U/D records');
    insert into informix.syscdcrectypes values(201, 'CDC_REC_TIMEOUT', 'Read timeout');
    insert into informix.syscdcrectypes values(202, 'CDC_REC_ERROR', 'Error description');

    { The below symbolic names are deprecated.  DO NOT USE. }
    insert into informix.syscdcrectypes values(1, 'BEGINTX', 'Begin Transaction');
    insert into informix.syscdcrectypes values(2, 'COMMTX', 'Commit Transaction');
    insert into informix.syscdcrectypes values(3, 'RBTX', 'Rollback Transaction');
    insert into informix.syscdcrectypes values(40, 'INSERT', 'Insert row');
    insert into informix.syscdcrectypes values(41, 'DELETE', 'Delete row');
    insert into informix.syscdcrectypes values(42, 'UPDBEF', 'Update row before image');
    insert into informix.syscdcrectypes values(43, 'UPDAFT', 'Update row after image');
    insert into informix.syscdcrectypes values(62, 'DISCARD', 'Discard log records after LSN');
    insert into informix.syscdcrectypes values(119, 'TRUNCATE', 'Truncate table');
    insert into informix.syscdcrectypes values(200, 'TABSCHEMA', 'Format of I/U/D records');
    insert into informix.syscdcrectypes values(201, 'TIMEOUT', 'Read timeout');
    insert into informix.syscdcrectypes values(202, 'ERROR', 'Error description');

{ error codes }
    { WARNING: The numerical values associated with each symbolic name  }
    { may change without notice.  Do not use the numerical values       }
    { directly.  Always create an internal mapping table on startup,    }
    { based on the symbolic names.                                      }
    create table informix.syscdcerrcodes
    	(
	errcode		integer,	{ numeric value                 }
	errname		varchar(16),	{ symbolic name			}
	errdesc		varchar(127) 	{ description                   }
	);
    revoke all on informix.syscdcerrcodes from public as informix;

    insert into informix.syscdcerrcodes values(  0, 'CDC_E_OK', 
            'Operation succeeded.');
    insert into informix.syscdcerrcodes values( -83701, 'CDC_E_NOCDCDB',
            'The syscdcv1 database does not exist.');
    insert into informix.syscdcerrcodes values( -83702, 'CDC_E_APIVERS',
            'The requested CDC API behavior version is not valid or is unsupported.');
    insert into informix.syscdcerrcodes values( -83703, 'CDC_E_NODB',
            'The specified database does not exist.');
    insert into informix.syscdcerrcodes values( -83704, 'CDC_E_DBNOTLOGGED',
            'The specified database is not logged.');
    insert into informix.syscdcerrcodes values( -83705, 'CDC_E_NOTAB',
            'The specified table does not exist.');
    insert into informix.syscdcerrcodes values( -83706, 'CDC_E_TABPROPERTIES',
            'The table properties do not support capture: it is a temporary table, a view, or otherwise not logged.');
    insert into informix.syscdcerrcodes values( -83707, 'CDC_E_NOCOL',
            'The specified column does not exist.');
    insert into informix.syscdcerrcodes values( -83708, 'CDC_E_NOSESS',
            'The specified CDC session does not exist.');
    insert into informix.syscdcerrcodes values( -83709, 'CDC_E_NOREOPEN',
            'The CDC session cannot be reopened.');
    insert into informix.syscdcerrcodes values(-83710, 'CDC_E_TABCAPTURED',
            'The specified table is already being captured by the CDC session.');
    insert into informix.syscdcerrcodes values(-83711, 'CDC_E_TABNOTCAPTURED',
            'The specified table is not being captured by the CDC session.');
    insert into informix.syscdcerrcodes values(-83712, 'CDC_E_ARGNULL',
            'An argument to the function has the SQL NULL value, which is not allowed.');
    insert into informix.syscdcerrcodes values(-83713, 'CDC_E_LSN',
            'Data at the requested log sequence number is not available for capture.');
    insert into informix.syscdcerrcodes values(-83714, 'CDC_E_DIRECTION',
            'Direction on reopen does not match with existing CDC session direction.');
    insert into informix.syscdcerrcodes values(-83715, 'CDC_E_DUPLSESS',
            'A CDC session is already active.');

    { The following error codes provide information about what argument to  }
    { are invalid.  UDR argument binding need not be positional; the        }
    { position values refer to the order in which formal parameter appear   }
    { in the UDR signature.                                                 }

    insert into informix.syscdcerrcodes values(-83720, 'CDC_E_ARG',
            'A parameter passed to the function  is not valid');
    insert into informix.syscdcerrcodes values(-83721, 'CDC_E_ARG1',
            'The first parameter passed to the function is not valid.');
    insert into informix.syscdcerrcodes values(-83722, 'CDC_E_ARG2',
            'The second parameter passed to the UDR is not valid.');
    insert into informix.syscdcerrcodes values(-83723, 'CDC_E_ARG3',
            'The third parameter passed to the UDR is not valid.');
    insert into informix.syscdcerrcodes values(-83724, 'CDC_E_ARG4',
            'The fourth parameter passed to the UDR is not valid.');
    insert into informix.syscdcerrcodes values(-83725, 'CDC_E_ARG5',
            'The fifth parameter passed to the UDR is not valid.');
    insert into informix.syscdcerrcodes values(-83726, 'CDC_E_ARG6',
            'The sixth parameter passed to the UDR is not valid.');

    insert into informix.syscdcerrcodes values(-83790, 'CDC_E_INTERNAL',
            'Internal error.  Contact IBM Informix Technical Support.');
    insert into informix.syscdcerrcodes values(-83791, 'CDC_E_NOMEM',
            'Memory allocation failed.');
    insert into informix.syscdcerrcodes values(-83792, 'CDC_E_MUSTCLOSE',
            'The CDC capture session cannot continue and must be closed.');
    insert into informix.syscdcerrcodes values(-83793, 'CDC_E_BADSTATE',
            'The resource state does not allow the attempted operation.');
    insert into informix.syscdcerrcodes values(-83794, 'CDC_E_BADCHAR',
            'A byte sequence that is not a valid character in the character codeset was encountered.');
    insert into informix.syscdcerrcodes values(-83795, 'CDC_E_INTERRUPT',
            'The CDC session was interrupted.');
    insert into informix.syscdcerrcodes values(-83796, 'CDC_E_LOCALEMISMATCH',
            'The locale setting in the environment does not match the locale of the database.');
    insert into informix.syscdcerrcodes values(-83797, 'CDC_E_LOGWRAP',
            'The current logical log that CDC is reading is overwritten.');
    insert into informix.syscdcerrcodes values(-83798, 'CDC_E_LOGPAGE',
            'CDC received incorrect logical log page data.');
    insert into informix.syscdcerrcodes values(-83799, 'CDC_E_UNIMPL',
            'Unimplemented feature.');
    insert into informix.syscdcerrcodes values(-83800, 'CDC_E_OUTOFSEQ',
            'Internal error. CDC received out-of-sequence logical log page data.');
    insert into informix.syscdcerrcodes values(-83802, 'CDC_E_EXPANDROW',
            'CDC could not expand a disk compressed row.');

create table informix.syscdcfullrowlogging 
(
	table_db	char(256),     { database name }
	table_owner char(64),      { owner name }
	table_name	char(256),     { table name }
	start_logf	int,
	start_logpos	int,
	stop_logf	int,
	stop_logpos	int,
	dictionary	text
);   
create index informix.frl_idx on informix.syscdcfullrowlogging(table_name) in table;

{ create log snoop session }
    create dba function informix.cdc_opensess
    	(
	informix.lvarchar,		{In: server name		    }
	informix.integer,		{In: session id or 0		    }
	informix.integer,		{In: timeout in secs for read calls }
	informix.integer,		{In: max num recs for read calls    }
	informix.integer,		{In: interface behavior maj version }
	informix.integer 		{In: interface behavior min version }
	)
    returns informix.integer external name '(cdc_apiudr_opensess)'
    language C;

{ activate a snoop session }
    create dba function informix.cdc_activatesess
	(
	informix.integer,		{In: session id			}
	informix.bigint			{In: start lsn			}
	)
    returns informix.integer external name '(cdc_apiudr_activatesess)'
    language C;

{ deactivate a snoop session }
    create dba function informix.cdc_deactivatesess
	(
	informix.integer		{In: session id			}
	)
    returns informix.integer external name '(cdc_apiudr_deactivatesess)'
    language C;

{ start capture on a table }
    create dba function informix.cdc_startcapture
    	(
	informix.integer,		{In: session id			}
	informix.bigint,		{In: LSN identifying table	}
	informix.lvarchar,		{In: full table name db@[owner.]table }
	informix.lvarchar,		{In: comma-separated list of colnames }
	informix.integer		{In: user data returned on read calls }
	)
    returns informix.integer external name '(cdc_apiudr_startcapture)'
    language C;

{ end capture on a table }
    create dba function informix.cdc_endcapture
    	(
	informix.integer,		{In: session id			}
	informix.bigint,		{In: LSN uniqid identifying table }
	informix.lvarchar		{In: full table name db@[owner.]table }
	)
    returns informix.integer external name '(cdc_apiudr_endcapture)'
    language C;

{ end a snoop session }
    create dba function informix.cdc_closesess
	(
	informix.integer		{In: session id			}
	)
    returns informix.integer external name '(cdc_apiudr_closesess)'
    language C;

{ advance to log record boundary }
    create dba function informix.cdc_recboundary
	(
	informix.integer		{In: session id			}
	)
    returns informix.integer external name '(cdc_apiudr_recboundary)'
    language C;

{ obtain error message text associated with an error name }
    create dba function informix.cdc_errortext
    	(
	informix.lvarchar,		{In: error name 		   }
	informix.lvarchar		{In: locale name, SQL NULL for default }
	)
    returns informix.lvarchar with(HANDLESNULLS)
        external name '(cdc_apiudr_errortext)' language C;

{ set/unset full row logging for a table }
    create dba function informix.cdc_set_fullrowlogging
	(
	informix.lvarchar,		{In: full table name db@owner.table }
	informix.integer		{In: value 0 to unset, 1 to set	}
	)
    returns informix.integer external name '(SetFullRowLogging)'
    language C;