User Guide : Best Practices : Common Integration Techniques : Scripting Tips, Tricks, and Techniques
Share this page             
Scripting Tips, Tricks, and Techniques
This section provides a few tips and techniques related to scripting to improve:
ECA Loops
Complex Text Parsing
Character Conversion is Costly
EZscript is a loosely typed scripting language and can impact performance if used too loosely. An example that can be expensive is converting data types unknowingly. If you intend to use numeric functions on a variable, then you must initialize it as a numeric variable.
Let us use a variable to create a new sequence number using the Serial() function:
v_sequenceCounter = Serial(v_sequenceInitValue)
When declaring these variables and setting an initial value, make sure to set it to numeric:
Private v_sequenceCounter, v_sequenceInitValue
v_sequenceCounter = 0
v_sequenceInitValue = 0
If you initialize the variable as a string using v_sequenceCounter = "", then every time the Serial() function is called, there is an unnecessary conversion from string to numeric. This will add up fast if you use this function for a million rows of data.
Constants are Fast
Constants are literal values assigned to names similar to variables. However, they are set at the beginning of the transformation and remain constant until the end. They are much faster than variables, which are evaluated each time they are called. They are also considered similar to field reference so the engine has much faster access to their values.
Use a constant instead of a string or date variable anytime you have to map the same value to multiple instances. This value remains the same for the duration of the transformation map. Access the values through EZscript as:
Avoid Unnecessary Repetition
Use djMessage and custom objects in the appropriate place and event of a map or process. For example, you must initialize an object or open a database session once at the beginning of a map and close at the end of the map unless your use case requirements dictate otherwise.
You do not want to initialize, call, and close the object during the RecordStarted event if you have a million rows in your source data set. Make sure to use the Engine Execution Profiler as it highlights such activities quickly and provides information to optimize the integration.
Option Implicit is the default statement when writing user-defined functions. Hence, developers do not have to declare the variables but it can lead to bugs.
Use the Option Explicit statement at the beginning of the code module to eliminate errors during design time. You can also declare that statement in the TransformationStarted event so that each time the map is validated or run, all the scripts are validated accordingly.
ECA Loops
Some built-in features allow you to easily handle multiple operations of the same action. The Record Count action parameter of an OutputRecord operation supports an integer value if you already know the number of times you want to perform the operation. You can use any expression that resolves to an integer as the value. A commonly used technique is to parse a source field into multiple values and write a target record for each. Others include using the size property of any object, the length of an array, or the CharCount function. However, in this example, the value 3 is hard coded assuming three target records are required for every one source record.
You can access the value of the count parameter and each instance using the Transformation object properties such as IterationCount and IterationNumber. The results of these target field mapping expressions is as shown in the following image.
This technique is used with many hierarchical file formats such as EDI X12, HIPAA, HL7, and XML. It is also used with legacy file formats that simulated hierarchies such as COBOL when "Occurs Clause" were used to store more values in the same amount of space.
Complex Text Parsing
The EZscript language has a powerful set of functions that is useful for converting data types, formatting date values, navigating file systems, performing mathematical operations, and string manipulations.
The following example is a reusable, user defined function that can extract data from a field, file, or message containing text characters. The If-Then-Else flow control is used to exit the code block when the relevant value does not exist in the first place. This is a commonly used best practice to reduce processing overhead and must be used in most scripting scenarios in addition to parsing.
'This Function will extract data between two string arguments
Public Function FindString(nstart, nend, nbody)
dim stringstart, stringend, returnval
'If the string exists, set the start and end positions and pass to the SubString function
if InStr(Start, nbody, nstart) > 0 then
stringstart = CLNG(InStr(1,nbody, nstart)) + Len(nstart)
stringend = CLNG(InStr(stringstart, nbody, nend))
if stringstart == 0 or stringend == 0 then
returnval = ""
returnval = SubString(nbody, stringstart, stringend - stringstart)
end if
return returnval
return ""
end if
End Function
Plan for Customization
You can design for variability and changes that are not easily accommodated for using runtime configuration parameters dynamically:
Config File Lookups
Dynamic Mapping
Config File Lookups
Macros and macro-definition files accommodates most use cases but when you have high number of trading partners or data feeds to manage, it is very useful to store the parameter values in a database table. You can use the lookup and macro define functions to get and set the values "just in time" with a Scripting step in the calling process.
Dynamic Mapping
When you build reusable template process workflows, one of the most common requirements is to call a specific map based on an input parameter. For example, you have several customers that send data files to you through FTP. You have reusable, generic processes to perform operations such as file routing, error handling, and audit logging but each customer sends you a file with a different schema. As a result, you want to use a custom Transformation Map for each customer.
You can do this using a Decision Step but the current versions of DataConnect allow you to reference a macro in the Transformation Step property of a process. Define the macro in a step prior to the map so that the new macro value is used.
The Decision step allows you to use conditional branching in a process workflow. You make an assertion and if it is true, the process branches one way. If false, it branches the other way. This allows a flexibility for customization.
Common use cases include, but are not limited to:
Test a variable to decide the map to execute
Test a flag value for debugging purposes
Test a counter value to exit a loop
Test the validity of data prior to, or after processing
Test the return code of a step to perform error handling
While you evaluate any script, the script must return the expected value. Initially, the expression must resolve to either true or false. The script inside the Decision Step must be as succinct as possible and conform to the rules available in Decision Step.