GoldenGate Custom Processing: SQLEXEC

* Can be used in Extract or Replicat processes to communicate with database via
– DB commands (DBLOGIN, ENCRYPT PASSWORD, LIST TABLES)
– SQL queries
– Stored procedures
– DML
* As a standalone parameter at the root level of the parameter file
– Extract
– Replicat
* As a clause in the statement (procedures and queries only, no db commands)
– TABLE
– MAP

* Can pass and accept parameters.
* Can use queries and procedures, but no db commands.
* SQL syntax

SQLEXEC (
ID <logical name>,  # Used to get col values (ID.Col)
QUERY "<sql query>",  # SQL query
{PARAMS <param spec> | NOPARAMS}  # Input parameters
[, <option>] [, ...]  # Options
)
 
-- Example
SQLEXEC (ID appphone, &
QUERY " select per_type from ps_personal_data " &
" where emplid = :vemplid " &
" and per_status = 'N' and per_type = 'A' ", &
PARAMS (vemplid = emplid)), &
TOKENS (applid = @GETVAL(appphone.per_type));

* Procedure syntax

SQLEXEC (
SPNAME <sp name>,  # Procedure name
[ID <logical name>,]  # Used if need to execute multiple times only
{PARAMS <param spec> | NOPARAMS}  # Input parameters
[, <option>] [, ...]  # Options
)
 
-- Example
SQLEXEC (ID appphone, &
QUERY " select per_type from ps_personal_data " &
" where emplid = ? " &
" and per_status = 'N' and per_type = 'A' ", &
PARAMS (p1 = emplid)), &
TOKENS (applid = @GETVAL(appphone.per_type)); &

* Input

PARAMS (
[OPTIONAL | REQUIRED] 
<param name> 
= {<source column> | <GG function>}
[, ...] )

* Output
– Use dot notation (logical name followed by dot parameter).

<procedure name> | <logical name>.<parameter>

* AFTERFILTER
Execute query ONLY after filter returns true. This is the DEFAULT.
* BEFOREFILTER
Execute query BEFORE filter returns. If filter clause uses query return values, make SURE this is used.
* ALLPARAMS {OPTIONAL | REQUIRED}
If all parameters need to be present for query to execute. Default is OPTIONAL.
* DBOP
Does a commit.
* ERROR
– IGNORE: ignores errors
– REPORT: reports error to discard file. Need to specify DISCARDFILE parameter
– RAISE: handles errors according to rules set by a REPERROR parameter
– FINAL: invoke error processing immediately
– FATAL: abend immediately
* EXEC
– MAP: execute ONCE for each source-target table map. This is DEFAULT.
– ONCE: execute ONCE during the course of GoldenGate run. The results remain valid during the course.
– TRANSACTION: execute ONCE per source transaction. Results remain valid within transaction.
– SOURCEROW: execute once per source row operation.
* MAXVARCHARLEN
– Specifies max length for any output parameter.
– Defaults to 255
– Example:MAXVARCHARLEN 100
* PARAMBUFSIZE
– Max number of bytes allowed for parameter memory buffer.
– Defaults to 10,000 bytes
* TRACE
– Logs input and output parameters to the report file.
– Syntax: TRACE {ALL | ERROR}
– Example

SQLEXEC (SPNAME lookup, PARAMS (long_name = birth_state, short_name = state),
TRACE ERROR)

-- Lookup CUSTOMER table for DESC column whose value start with M
TABLE CUSTOMERS, &
SQLEXEC (ID lookup, &
QUERY "SELECT DESC FROM NAME WHERE ID = :CUSTID", PARAMS(CUSTID = ID), &
BEFOREFILTER, EXEC SOURCEROW), &
FILTER (@STRFIND (lookup.DESC, "M")=1);

* Can use queries, procedures, and db command.
* Can NOT accept input parameters
* Can NOT return output parameters
* Procedure Syntax

SQLEXEC "call <procedure name>()"
[EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}]  # Execute every so often
[ONEXIT]  # Execute on exit

* Query Syntax

SQLEXEC "<sql query>"
[EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}]
[ONEXIT]

* DB commands

SQLEXEC "<database command>"
[EVERY <n> {SECONDS | MINUTES | HOURS | DAYS}]
[ONEXIT]

* Examples

SQLEXEC "call prc_job_count ()"
SQLEXEC " select x from dual "
SQLEXEC "call prc_job_count ()" EVERY 30 SECONDS
SQLEXEC "call prc_job_count ()" ONEXIT
SQLEXEC "SET TRIGGERS OFF"

* gg_wux_admin_v104.pdf, page 264.