Stuart Turner Services Architect stuart turnerca com cainc

Stuart Turner Services Architect stuart. turner@ca. com @cainc Slideshare. net/CAinc https: //www. linkedin. com/pub/stuart-turner/17/7 a 7/73 a www. ca. com

Improving Large Volume Integration Performance a. k. a. self-spawning processes GEL Insights #1

Challenges ● ● ● ● Volumes of data Increased demand for near real-time information System in use 24/5 Bulk processing time window is limited XOG is mandatory for object inserts/updates Significant processing time is ‘waiting’ On. Demand means no-shortcuts Scaling a process horizontally is not straightforward

Requirements ● Speed up integration processing o Scale processes horizontally by means of run-time parameter (only) ● Single code base o Use a common technique across all integrations ● Ensure processing effort is not duplicated no matter what the data set o Straightforward pattern to balance workload ● On. Demand-compatible

Tools We Can Use ● GEL o o o Generic Execution Language The programming language for Clarity Used in steps in Clarity processes ● SQL o Structured Query Language ● XOG o o XML Open Gateway The only supported mechanism for Clarity inserts/updates

Balance Workload ● ● Multiple process instances per integration ~Equal load per process instance Use numeric unique record id to split data Mathematical function ‘modulo’ finds the remainder after division of one number by another… Unique Record ID Modulo remainder when divided by 3 5000000 2 5000001 0 5000002 1 5000003 2 Process Instance 0 Process Instance 1 Process Instance 2 3 process instances to split the load

Balance Workload ● ~Equal load per process instance o o Start each process instance with parameters for § Process Count § Modulo Remainder Process uses Process Count and Modulo Remainder in SQL to determine list of records to process

Balance Workload Is Process Count > 1? Set Modulo Remainder to 0 Is Modulo Remainder defined? Process data using [Process Count] and [Modulo Remainder] Spawn [Process Count] copies of _self_ with [Modulo Remainder] set to 0, 1, 2, etc Exit

Time Saving ● Client required ~5500 Cost Plans to be auto-generated from Allocation/Estimates plus custom non-labour object instances ● Run on each of 4 consecutive days at month end ● 7. 5 hours with single process, 734 plans/hour ● 2 hours with 3 process instances, 2750 plans/hour ● Your mileage may vary…

GEL/SQL/XOG Highlights ● Invoke. Action XOG call o Starts a non-object process ● Run-time XML processed in GEL step o o o ● GEL parameters created/updated at run-time <gel: get. Document> tag <core: set> tag with run-time parameter value AND parameter name <sql: param> tag

Invoke. Action XOG <Process xmlns="http: //www. niku. com/xog/Invoke. Action"> <code>[Process ID]</code> <root> <Any> <Valid /> <XML /> </Any> </root> </Process>

Invoke. Action XOG <Process xmlns="http: //www. niku. com/xog/Invoke. Action"> <code>[Process ID]</code> <root> <Parameters> <Parameter name=”process_count”>[Process Count]</Parameter> <Parameter name=”mod_remainder”>[Modulo Remainder]</Parameter> </Parameters> </root> </Process>

SQL and MOD - Oracle <sql: query> SELECT. . . FROM. . . WHERE MOD([Internal ID], ${process_count}) = ${mod_remainder} </sql: query>

SQL and MOD - SQL Server <sql: query> SELECT. . . FROM. . . WHERE ([Internal ID] % ${process_count}) = ${mod_remainder} </sql: query>

Horizontal Scaling ● Maximum 3 process instances o Clarity allocates ~12 threads for GEL scripts ● Utilise ‘standard’ optimisations o o o Keep SQL result sets small Use <sql: param> tag XOG multiple records per file

Example GEL Script and Demo

To Do (nice to have) ● Allow process to decide number of instances to spawn o o Based on SQL anlaysis of volume of data to process Up to a pre-defined maximum number of process instances ● Rendezvous mechanism o Notification when all process instances have finished

Questions ?

Stuart Turner Services Architect stuart. turner@ca. com @cainc Slideshare. net/CAinc https: //www. linkedin. com/pub/stuart-turner/17/7 a 7/73 a www. ca. com