CREATE RDBMS CURSOR - connect_cdc_sqdata - Latest

Cursors can perform any legal SQL operation. They can be used for many purposes including:

• Validation of source data including foreign keys prior to writing to the primary Target.
• Insert, update and delete data that may result from normalization of the Source CDC data, performing one-to-one, one-to-many and many-to-one datastore operations
• Substitution of field values based on a correlating value from another datastore.
• Transform source data based on values translation tables.

Cursors are made up of four constructs. Precisely recommends using the inline syntax /+..+/ for the DESCRIPTION command and the CREATE RDBMS CURSOR command.

• Key set
• Result set
• Cursor Select statement
• Execution of the Cursor

Syntax

BEGIN GROUP <cursors>
DESCRIPTION DB2SQL|ORASQL|MSSQL <table_name>  AS <table_alias>;
DESCRIPTION DB2SQL|ORASQL|MSSQL <table_name2> AS <table_alias2>;
DESCRIPTION DB2SQL|ORASQL|MSSQL <table_nameN> AS <table_aliasN>;
END GROUP;

DATASTORE RDBMS
         OF RELATIONAL
         AS <cursors>
         DESCRIBED BY <cursors>;

DESCRIPTION DB2SQL|ORASQL|MSSQL
/+
CREATE TABLE K_<table_name>       --< The Key set
(
      <where_clause_column1>
    [<where_clause_column2>...
      <where_clause_columnN>]
);
+/
AS K_<table_name>;

DESCRIPTION <relational_type>|[DB2SQL|ORASQL|MSSQL]
/+
CREATE TABLE R_<table_name>       --< The Results set
(
      <result_set_column1>
    [<result_set_column2>...
      <result_set_columnN>]
);
+/
AS R_<table_name>;

CREATE RDBMS CURSOR AS C_<table_name> WITH --< The Cursor Select statement
/+
SELECT <result_set_column1>    <data_type>
    [<result_set_column2>    <data_type>...
      <result_set_columnN>    <data_type>]
FROM <table_name>
WHERE <where_clause_column1> = :h1
    [<where_clause_column2> = :h2 ...
      <where_clause_columnN> = :hN]
+/
DESCRIBED BY R_<table_name>
USING <where_clause_column1> = :h1
COMMIT EVERY  <commit_frequency> | <source_field_name> | KEY CHANGE FOR <datastore_alias>;