SELECT - connect_cdc_sqdata

The SELECT sub-command performs the primary processing within the Apply Engine. This sub-command has the ability to perform the following:

Reads records from a source datastore, returning the fields listed within the sub-command. This functionality is similar to the SQL SELECT command.
Specifies the target fields that will be updated in one or more target datastores
Specifies the source to target field mapping where the results will be written to one or more target datastores.
Enables the use of Apply Engine functions that can be used for business logic, data transformation, filtering, etc.

Notes:

The FROM source_datastore keyword is required as the last parameter in the SELECT sub-command.
The SELECT sub-command does not end with a semicolon (;) since it is part of a Processing Command such as CHANGE or INSERT.
There can be only one (1) source datastore per SELECT sub-command. If you want to process multiple source datastores within a Apply Engine script, you must use the UNION sub-command.

The SELECT sub-command can be used in several different ways as shown in the Syntax variations below. Each of these cases is explained in more detail later in this section.

Syntax

SELECT  
SELECT  *
SELECT  <source_field>  [,  <source_field>]
SELECT  <source_field>  [,  <source_field>]	       <target_field>  [,  <target_field>]
SELECT  <source_field>  [,  <source_field>]	       <field_alias>   [,  <field_alias>]
SELECT  <function_set>  [,  <function_set>]	       <target_field>  [,  <target_field>]
SELECT  <function_set>  [,  <function_set>]	       <field_alias>   [,  <field_alias>]
FROM  source_datastore  |  NOTHING
;

The subscripts prior to each of the SELECT sub commands in the syntax diagram correlate to the examples below.

Keyword and Parameter Descriptions

Keyword	Description
none	The DEFAULT and most common form of SELECT in the Main Section of the Engine script reads the "next record" from the source_datastore.
*	An asterisk (*) directs the select sub-command to return all of the fields/columns in the source_datastore.
<source_field>	Specifies one (1) or more source fields/columns to be read from the source_datastore.
<target_field>	Specifies one (1) or more target fields/columns, each correlated to a <source_field> or <function_set>, which will have data mapped into them.
<field_alias>	Specifies an alias name of a source field/column. Other statements can subsequently reference this field alias during processing. A field alias can be considered an implicit variable (i.e. not defined via a DECLARE command). If a field alias name matches the name of a field/column in one (1) of the target datastores, the field alias will be mapped to the target field.
<function_set>	Specifies one (1) or more Apply Engine Functions that can be imbedded into the SELECT sub-command. A <function_set> can contain multiple functions (i.e. nested functions), source fields/columns and Apply Engine variables. Information regarding Apply Engine Functions and how to construct them can be found in the Function Reference.
source_datastore \| NOTHING	Specifies a source datastore that the select sub-command will read data from. The keyword NOTHING can be specified if you are not reading from a source datastore in the SELECT sub-command. This may be useful for performing end of file processing, mapping constants to a target datastore, etc.

Examples

Each of the following examples reference the source and target datastores, EMPLOYEE_IN and EMPLOYEE_OUT respectively. These examples are intended to be a progression of the same ‘base’ example to better illustrate the SELECT sub-command construction.

Example 1: Selecting All Source Fields and Mapping them to Like named Target Fields

Select all of the fields from the source datastore and map them to like-named fields the target datastore. Note: The same description is used for the source and the target layouts.

-- Specify the source/target data structures
DESCRIPTION COBOL /home/sqdata/COBOL/employee.cob AS EMPLOYEE;

-- Specify the source and target datastores
DATASTORE /home/sqdata/EMPLOYEE.dat
          OF BINARY
          AS EMPLOYEE_IN
          DESCRIBED BY EMPLOYEE;
DATASTORE MQS:///EMPLOYEE_OUT_QUEUE
          OF BINARY
          AS EMPLOYEE_OUT
          DESCRIBED BY EMPLOYEE;
-- Specify the Processing command
PROCESS INTO EMPLOYEE_OUT
SELECT *
FROM EMPLOYEE_IN;

Example 2: Selecting Specific Source Fields and Mapping them to Like Target Fields

Select the fields NAME and SSN from the source datastore and map them to like-named fields in the target datastore. Note: The same description is used for the source and the target layouts.

-- Specify the source/target data structures
DESCRIPTION COBOL /home/sqdata/COBOL/employee.cob AS  EMPLOYEE;

-- Specify the source and target datastores
DATASTORE /home/sqdata/EMPLOYEE.dat
          OF BINARY
          AS EMPLOYEE_IN
          DESCRIBED BY EMPLOYEE;
DATASTORE MQS:///EMPL_OUT_QUEUE
          OF BINARY
          AS EMPLOYEE_OUT
          DESCRIBED BY EMPLOYEE;
-- Specify the Processing command
PROCESS INTO EMPLOYEE_OUT
SELECT
{
   NAME
   SSN
}
FROM EMPLOYEE_IN;

Example 3: Mapping Selecting Source Fields to Specific Target Fields

Select the fields NAME and SSN from the source datastore and map them to the target fields OUTNAME and OUTSSN, respectively.

DESCRIPTION COBOL /home/sqdata/COBOL/EMPIN.cob   AS  EMPLOYEE_IN;
DESCRIPTION COBOL /home/sqdata/COBOL/EMPOUT.cob AS  EMPLOYEE_OUT;
-- Specify the source and target datastores
DATASTORE /home/sqdata/EMPLOYEE.dat
          OF BINARY
          AS EMPLOYEE_IN
          DESCRIBED BY EMPLOYEE_IN;
DATASTORE MQS:///EMPL_OUT_QUEUE
          OF BINARY
          AS EMPLOYEE_OUT
          DESCRIBED BY EMPLOYEE_OUT;
-- Specify the Processing command
PROCESS INTO EMPLOYEE_OUT
SELECT
{
   OUTNAME     =  NAME
   OUTSSN     =  SSN
}
FROM EMPLOYEE_IN;

Example 4: Assigning and Subsequently Using Field Alias’

Select the fields NAME and SSN from the source datastore and map them to the field alias XNAME and XSSN, respectively. Map these field alias’ to the target fields OUTNAME and OUTSSN, respectively.

-- Specify the source/target data structures
DESCRIPTION COBOL /home/sqdata/COBOL/EMPIN.cob   AS  EMPLOYEE_IN;
DESCRIPTION COBOL /home/sqdata/COBOL/EMPOUT.cob AS  EMPLOYEE_OUT;

-- Specify the source and target datastores
DATASTORE /home/sqdata/EMPLOYEE.dat
          OF BINARY
          AS EMPLOYEE_IN
          DESCRIBED BY EMPLOYEE_IN;
DATASTORE MQS:///EMPL_OUT_QUEUE
          OF BINARY
          AS EMPLOYEE_OUT
          DESCRIBED BY EMPLOYEE_OUT;
-- Specify the Processing command
PROCESS INTO EMPLOYEE_OUT
SELECT
{
   XNAME       =  NAME
   XSSN       =  SSN
   OUTNAME     =  XNAME
   OUTSSN     =  XSSN
}
FROM EMPLOYEE_IN;

Example 5: Using Functions with Source to Target Mapping

Select the fields NAME and SSN from the source datastore and map them to the target fields OUTNAME and OUTSSN, respectively. Increment a variable (CTR) using the INCREMENT function to keep a running count of the number of source records processed and map the result into the target field SEQ.

-- Specify the source/target data structures
DESCRIPTION COBOL /home/sqdata/COBOL/emplin.cob   AS  EMP_DESCR_IN;
DESCRIPTION COBOL /home/sqdata/COBOL/emplout.cob AS  EMP_DESCR_OUT;

-- Specify the source and target datastores
DATASTORE /home/sqdata/empin.dat
          OF BINARY
          AS EMPLOYEE_IN
          DESCRIBED BY EMPLOYEE_DESCR_IN;
DATASTORE MQS:///EMPL_OUT_QUEUE
          OF BINARY
          AS EMPLOYEE_OUT
          DESCRIBED BY EMPLOYEE_DESCR_OUT;
-- Declare the Counter Variable
DECLARE CTR 5 ‘0’;
-- Specify the Processing command
PROCESS INTO EMPLOYEE_OUT
SELECT
{
   SEQ       = INCREMENT(CNTR)
   OUTNAME   = NAME
   OUTSSN   = SSN
}
FROM EMPLOYEE_IN;

SELECT - connect_cdc_sqdata - Latest

Connect CDC (SQData) Apply engine