SAND CDBMS Tools Reference Guide
Data Loader (ndlm)

The NDL++ Data-Load Scripting Language

Introduction to NDL++

NDL++ is a scripting language that enables the analysis, manipulation, and conversion of data as it is loaded into a database.

The NDL++ language provides both character string and math functions. It can perform data-type conversion and data analysis of specified fields, and can also generate certain kinds of data for insertion into the target table (including current date and time as well as a unique key for a specified group of fields). Variables can be declared to receive the output of data manipulation or analysis; these variables can then be used in statements or expressions elsewhere in the script. Conditional expressions can be used to specify courses of action that are to be taken only when certain conditions are present.

The NDL++ String Functions

Character string functions can be used in the map specification (table-name) section and in the NULLIF, MISSINGIF, and SKIPIF clauses of ndlm import specification script files. They are typically included in the map specification to specify operations to be performed on input data. Analytical functions are useful for inclusion in variables and conditional expressions.

The following table lists in alphabetical order the character string functions that can be used in NDL++ scripts, and provides a short description of each function along with its input and output data types. More detailed descriptions and examples are included in the String Function Syntax and Examples section.

Function	Description	Input Data Type	Output Data Type
CMP()	Compares the ASCII values of two strings	char	int
DATE()	Returns the current date in a specified format	—	char
ENFTONF()	Converts a European Number (ENF) string to a Standard Number (NF) string	char	char
EXTRACTWORD()	Searches the input string for the nth occurrence of a substring, returning the substring if found, or an empty string otherwise	char and int	char
GENKEY()	Generates a unique key, 19 characters in length (RECORD section field input)	char	char
GENKEYCOL()	Generates a unique key, 19 characters in length (mapping section column input)	char	char
INSTR()	Determines the position of the first character of the first occurrence of one string within another	char	int
INSTRREV()	Determines the position of the last character of the last occurrence of one string within another	char	int
ISENF()	Determines whether an input string is a valid European Number (ENF)	char	int
ISNULL()	Determines whether a column contains a null value	char	int
ISNUMERIC()	Determines whether a string is numeric	char	int
LCASE()	Converts a string to lowercase characters	char	char
LEFT()	Returns a specified number of characters from the left side of a string	char	char
LEN()	Returns the length of a string	char	int
LOOKUP()	Searches for a key value in either a table, the output of an SQL statement, or the fields in the RECORD or table section of a specification file, returning the value from a field in the same row as the first match if the key value is found, or a user-specified default value otherwise	char	char, int, or float
LPAD()	Adds a specified character to the left side of a string to achieve a specified length	char	char
LTRIM()	Removes all instances of a specified character from the left side of a string	char	char
MID()	Returns a specified number of characters from a string	char	char
NFTOENF()	Converts a Standard Number (NF) string to a European Number (ENF) string	char	char
PAD()	Adds a specified character to both sides of a string to achieve a specified length	char	char
REPLACE()	Finds and replaces a substring within a string	char	char
RIGHT()	Returns a specified number of characters from the right side of a string	char	char
RPAD()	Adds a specified character to the right side of a string to achieve a specified length	char	char
RTRIM()	Removes all instances of a specified character from the right side of a string	char	char
SPACE()	Returns a string consisting of a specified number of spaces	int	char
STR()	Converts a numeric value into a string value	int or float	char
STRENF()	Converts an int or float input value to a European Number (ENF) string	int or float	char
STRING()	Returns a string consisting of a specified character repeated a specified number of times	char	char
STRREV()	Reverses a string	char	char
TIME()	Returns the current time in a specified format		char
TRIM()	Removes all instances of a specified character from the left and right sides of a string	char	char
UCASE()	Converts a string to uppercase characters	char	char
VALENF()	Converts a European Number (ENF) string to a float value	char	float
VALENI()	Converts a European Number (ENF) string to an int value	char	int
VALF()	Converts a string containing numeric characters into a float value	char	float
VALI()	Converts a string containing numeric characters into an integer value	char	int

The NDL++ Math Functions

Math functions can be used in the map specification (table-name) section and in the NULLIF, MISSINGIF, and SKIPIF clauses of ndlm import specification script files. The following table lists in alphabetical order the math functions that can be used in NDL++ scripts, and provides a short description of each function along with its input and output data types. More detailed descriptions and examples are included in the Math Functions Syntax and Examples section.

Function	Description	Input Data Type	Output Data Type
ABS(x)	Calculates the absolute value of x	int or float	same as x
ATN(x)	Calculates the arctangent of x in radians	int or float	same as x
COS(x)	Calculates the cosine of x in radians	int or float	same as x
EXP(x)	Calculates the exponential of x	int or float	float
LOGN(x)	Calculates the natural logarithm of x	int or float	float
POW(x,y)	Calculates x raised to the power of y	int or float	int or float
ROUND(x)	Rounds x to the decimal point	int or float	int
SIGN(x)	Determines the sign of x: returns 1 if x is positive, �1 if x is negative, and 0 if x is zero.	int or float	int
SIN(x)	Calculates the sine of x in radians.	int or float	float
SQR(x)	Calculates the square root of x	int or float	float
TAN(x)	Calculates the tangent of x in radians	int or float	float
TRUNC(x)	Truncates x at the decimal point	int or float	int

 

Data Type Declaration in NDL++ Scripts

By default, data references in NDL++ scripts are considered to be of character type. When a column or field represents a numeric value (that is, FLOAT or INTEGER), the data type must be declared, either within an optional TYPE section (as in Example 1 below), or when referencing the field in an expression (as in Example 2).

If included, the TYPE section precedes the RECORD section (and the VAR section if this is present). Type declarations in a TYPE section are enclosed by braces ( { } ).

Data Type Declaration Syntax

TYPE
{
data-type field-name,field-name1,...field-nameN
...
}

or, when declared with an expression elsewhere in the script:

data-type(field-name)

The data-type entry may be either FLOAT or INT.

Examples

Example 1:

input: -5,5,0.55

IMPORT @sample.dat
{

TYPE
{
INT field1, field2
FLOAT field3
}
RECORD
{
field1 *,
field2 *,
field3 *\r\n
}
schema1.table1
{
col1 field1+field2+field3
}

}

Results:

schema1.table1.col1 0.55

Example 2:

IMPORT @sample.dat
{

RECORD
{
field1 *,
field2 *,
field3 *\r\n
}
schema1.table2
{
col1 INT(field1)+INT(field2)+FLOAT(field3)
col2 ABS(INT(field1))
col3 ABS(INT(field1)*INT(field2))
}

}

Results:

schema1.table2.col1 0.55
schema1.table2.col2 5
schema1.table2.col3 25