The following topics provide information about scripting tips, tricks, and techniques:

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:

When declaring these variables and setting an initial value, make sure to set it to numeric:

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 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:

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.

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.

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.

You can design for variability and changes that are not easily accommodated for using runtime configuration parameters dynamically:

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.

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 Actian DataConnect allows 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:

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.