Tuesday, June 17, 2014

Goldengate Stored procedure parameter in SQLEXEC with GETENV


Here GG is extracting ABC.DUMMY table and while It does that, It also does following.

- check Where ORGANIZATION_ID =83,
- if true above execute XYZ.DUMMY_PKG (PACKAGE) to DUMMY_PROCEDURE_NAME (procedure),GG has bug for fully qualify Name of schema and Packagename as prefix.
-while executing above Procedure , It gets value run-time and pass value to procedure.

~database Name using GETENV("DBENVIRONMENT","DBNAME").
~Object Name
~Inventory_item_id (comes within procedure )
~Get Command type INSERT/UPDATE/DELETE ,again case function wrote to convert SQL COMPUPDATE / PKUPDATE to UPDATE string.
~Get Transaction XID of database.

TABLE ABC.DUMMY,WHERE (ORGANIZATION_ID = 83),SQLEXEC (ID XYZ.DUMMY_PKG.DUMMY_PROCEDURE_NAME,SPNAME XYZ.DUMMY_PKG.DUMMY_PROCEDURE_NAME,PARAMS (P_DBNAME=@
GETENV("DBENVIRONMENT","DBNAME"),P_OBJECT_NAME='DUMMY',P_INVENTORY_ITEM_ID=INVENTORY_ITEM_ID,P_ORGANIZATION_ID=ORGANIZATION_ID,P_COMMAND=@CASE(@GETENV("GGHEADER", "OPTYPE"), "INSERT", "INSERT", "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE","DELETE","DELETE"),P_TRANSACTION_ID=@GETENV("TRANSACTION", "XID")));

No comments:

Post a Comment