Transformation Datatypes
The following table describes the transformation datatypes:
Datatype | Size in Bytes | Description |
|---|
Bigint | 8 bytes | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 Precision of 19, scale of 0 Integer value. |
Binary | Precision | 1 to 104,857,600 bytes You cannot use binary data for flat file sources. |
Date/Time | 16 bytes | Jan 1, 0001 A.D. to Dec 31, 9999 A.D. Precision of 29, scale of 9 (precision to the nanosecond) Combined date/time value. |
Decimal | 8 bytes (if high precision is off or precision is greater than 28) 16 bytes (if precision <= 18 and high precision is on) 20 bytes (if precision > 18 and <= 28) | Decimal value with declared precision and scale. Scale must be less than or equal to precision. Precision 1 to 28 digits, scale 0 to 28 |
Double | 8 bytes | Double-precision floating-point numeric value. You can edit the precision and scale. The scale must be less than or equal to the precision. |
Integer | 4 bytes | -2,147,483,648 to 2,147,483,647 Precision of 10, scale of 0 Integer value. |
Nstring | Unicode mode: (precision + 1) * 2 ASCII mode: precision + 1 | 1 to 104,857,600 characters Fixed-length or varying-length string. |
Ntext | Unicode mode: (precision + 1) * 2 ASCII mode: precision + 1 | 1 to 104,857,600 characters Fixed-length or varying-length string. |
Real | 8 bytes | Precision of 7, scale of 0 Double-precision floating-point numeric value. |
Small Integer | 4 bytes | -32,768 and 32,767 Precision of 5, scale of 0 Integer value. |
String | Unicode mode: (precision + 1) * 2 ASCII mode: precision + 1 | 1 to 104,857,600 characters Fixed-length or varying-length string. |
Text | Unicode mode: (precision + 1) * 2 ASCII mode: precision + 1 | 1 to 104,857,600 characters Fixed-length or varying-length string. |
Integer Datatypes
You can pass integer data from sources to targets and perform transformations on integer data. Informatica Cloud supports Bigint, Integer, and Small Integer datatypes.
The transformation integer datatypes represent exact values.
Integer Values in Calculations
When you use integer values in calculations, Informatica Cloud sometimes converts integer values to floating-point numbers before it performs the calculation.
For example, to evaluate MOD( 12.00, 5 ), Informatica Cloud converts the integer value "5" to a floating-point number before it performs the division operation. Informatica Cloud converts integer values to double or decimal values depending on how you set the Enable High Precision advanced session property in the Mapping Configuration task.
Informatica Cloud converts integer values in the following arithmetic operations:
Operation | High Precision Disabled | High Precision Enabled |
|---|
Functions and calculations that cannot introduce decimal points. For example, integer addition, subtraction, and multiplication, and functions such as CUME and SUM. | No conversion. However, if the calculation produces a result that is out of range, Informatica Cloud writes a row error. | Decimal |
Non-scientific functions and calculations that can introduce decimal points. For example, integer division, and functions such as AVG, MEDIAN, and PERCENTILE. | Double | Decimal |
All scientific functions and the EXP, LN, LOG, POWER, and SQRT functions. | Double | Double |
The transformation Double datatype supports precision of up to 15 digits, while the Bigint datatype supports precision of up to 19 digits. Therefore, precision loss can occur in calculations that produce Bigint values with precision of more than 15 digits.
For example, an expression transformation contains the following calculation:
POWER( BIGINTVAL, EXPVAL )
Before it performs the calculation, Informatica Cloud converts the inputs to the POWER function to double values. If the BIGINTVAL field contains the Bigint value 9223372036854775807, Informatica Cloud converts this value to 9.22337203685478e+18, losing the last 4 digits of precision. If the EXPVAL field contains the value 1.0 and the result field is a Bigint, the calculation produces a row error because the result, 9223372036854780000, exceeds the maximum Bigint value.
When you use an Integer datatype in a calculation that can produce decimal values and you enable high precision, Informatica Cloud converts the integer values to decimal values.
For transformations that support the Decimal datatype with precision up to 28 digits, precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits in high precision mode. In this case, Informatica Cloud stores the result as a double. If the field precision is less than or equal to 28 digits and the result produces a value greater than 28 digits in high precision mode, Informatica Cloud rejects the row.
Integer Constants in Expressions
Informatica Cloud interprets constants in an expression as floating-point values, even if the calculation produces an integer result.
For example, in the expression INTVALUE + 1000, Informatica Cloud converts the integer value "1000" to a double value if high precision is not enabled. It converts the value 1000 to a decimal value if high precision is enabled. To process the value 1000 as an integer value, create a variable field with an Integer datatype to hold the constant, and modify the expression to add the two fields.
NaN Values
NaN (Not a Number) is a value that is usually returned as the result of an operation on invalid input operands, especially in floating-point calculations. For example, when an operation attempts to divide zero by zero, it returns a NaN result.
Operating systems and programming languages may represent NaN differently. For example, the following list shows valid string representations of NaN:
- •nan
- •NaN
- •NaN%
- •NAN
- •NaNQ
- •NaNS
- •qNaN
- •sNaN
- •1.#QNAN
- •1.#SNAN
Informatica Cloud converts QNAN values to 1.#QNAN on Win64EMT platforms. 1.#QNAN is a valid representation of NaN.
Convert String Values to Integer Values
When Informatica Cloud performs implicit conversion of a string value to an integer value, it truncates the data at the first non-numeric character.
For example, you link a string field that contains the value "9,000,000,000,000,000,000.777" to a Bigint field. Informatica Cloud converts the string to the Bigint value 9,000,000,000,000,000,000.
Write Integer Values to Flat Files
When writing integer values to a fixed-width flat file, the file writer does not verify that the data is within range.
For example, the file writer writes the result 3,000,000,000 to a target Integer column if the field width of the target column is at least 13. The file writer does not reject the row because the result is outside the valid range for Integer values.
Binary Datatype
If a mapping includes binary data, set the precision for the transformation binary datatype so that Informatica Cloud can allocate enough memory to move the data from source to target.
You cannot use binary datatypes for flat file sources.
Date/Time Datatype
The Date/Time datatype handles years from 1 A.D. to 9999 A.D. in the Gregorian calendar system. Years beyond 9999 A.D. cause an error.
The Date/Time datatype supports dates with precision to the nanosecond. The datatype has a precision of 29 and a scale of 9. Some native datatypes have a smaller precision. When you import a source that contains datetime values, the import process imports the correct precision from the source column. For example, the Microsoft SQL Server Datetime datatype has a precision of 23 and a scale of 3. When you import a Microsoft SQL Server source that contains Datetime values, the Datetime columns in the mapping source have a precision of 23 and a scale of 3.
Informatica Cloud reads datetime values from the source to the precision specified in the mapping source. When Informatica Cloud transforms the datetime values, it supports precision up to 29 digits. For example, if you import a datetime value with precision to the millisecond, you can use the ADD_TO_DATE function in an Expression transformation to add nanoseconds to the date.
If you write a Date/Time value to a target column that supports a smaller precision, Informatica Cloud truncates the value to the precision of the target column. If you write a Date/Time value to a target column that supports a larger precision, Informatica Cloud inserts zeroes in the unsupported portion of the datetime value.
Decimal and Double Datatypes
You can pass decimal and double data from sources to targets and perform transformations on decimal and double data.
Informatica Cloud supports the following datatypes:
- Decimal
Precision 1 to 28 digits, scale 0 to 28. You cannot use decimal values with scale greater than precision or a negative precision. Transformations display any range that you assign to a Decimal datatype, but Informatica Cloud supports precision only up to 28 digits.
When you enable high precision and the field precision is greater than 28 digits, Informatica Cloud stores the result as a Double.
- Double
Double-precision floating-point numeric value.
You can edit the precision and scale. The scale must be less than or equal to the precision.
Decimal and Double Values in Calculations
Precision loss can occur with Decimal and Double datatypes in a calculation when the result produces a value with a precision greater than the maximum.
If you disable high precision, Informatica Cloud converts decimal values to double. Precision loss occurs if the decimal value has a precision greater than 15 digits. For example, you have a mapping with Decimal (20,0) that passes the number 40012030304957666903. If you disable high precision, Informatica Cloud converts the decimal value to double and passes 4.00120303049577 x 1019.
For transformations that support Decimal datatype of precision up to 28 digits, use the Decimal datatype and enable high precision to ensure precision of up to 28 digits.
Precision loss does not occur in a calculation unless the result produces a value with precision greater than the maximum allowed digits. In this case, Informatica Cloud stores the result as a double.
Do not use the Double datatype for data that you use in an equality condition, such as a lookup or join condition.
The following table lists how Informatica Cloud handles decimal values based on how the Enable High Precision advanced session property is set:
Field Datatype | Precision | High Precision Disabled | High Precision Enabled |
|---|
Decimal | 0 to 15 | Decimal | Decimal |
Decimal | 15 to 28 | Double | Decimal |
Decimal | Over 28 | Double | Double |
When you enable high precision, Informatica Cloud converts numeric constants in any expression function to Decimal. If you do not enable high precision, Informatica Cloud converts numeric constants to Double.
You can ensure the maximum precision for numeric values greater than 28 or 38 digits depending on the transformation. Before you perform any calculations or transformations with the transformation functions, truncate or round any large numbers.
Rounding Methods for Double Values
Due to differences in system run-time libraries and the computer system where the database processes double datatype calculations, the results may not be as expected. To provide calculation results that are less susceptible to platform differences, Informatica Cloud stores the 15 significant digits of double datatype values.
The double datatype conforms to the IEEE 794 standard. Changes to database client library, different versions of a database or changes to a system run-time library affect the binary representation of mathematically equivalent values. Also, many system run-time libraries implement the round-to-even or the symmetric arithmetic method. The round-to-even method states that if a number falls midway between the next higher or lower number it round to the nearest value with an even least significant bit. For example, with the round-to-even method, 0.125 is rounded to 0.12. The symmetric arithmetic method rounds the number to next higher digit when the last digit is 5 or greater. For example, with the symmetric arithmetic method 0.125 is rounded to 0.13 and 0.124 is rounded to 0.12.
Informatica Cloud stores the 15 significant digits of double datatype values. For example, if a calculation on Windows returns the number 1234567890.1234567890, and the same calculation on UNIX returns 1234567890.1234569999, Informatica Cloud converts this number to 1234567890.1234600000.
String Datatypes
The transformation string datatypes include Nstring, Ntext, String and Text. Although the Nstring, Ntext, String, and Text datatypes support the same precision up to 104,857,600 characters, Informatica Cloud uses String to move string data from source to target and Text to move text data from source to target.
Because some databases store text data differently than string data, Informatica Cloud needs to distinguish between the two types of character data. If the source displays String, set the target column to String. Likewise, if the source displays Text, set the target column to Text, Long, or Long Varchar, depending on the source.
In general, the smaller string datatypes, such as Char and Varchar, display as String in the source, Lookup transformation, and SQL transformation, while the larger text datatypes, such as Text, Long, and Long Varchar, display as Text in the source.
Use Nstring, Ntext, String, and Text interchangeably within transformations. However, in the source, Lookup transformation, and SQL transformation, the target datatypes must match. The database drivers need to match the string datatypes with the transformation datatypes, so that the data passes accurately. For example, Nchar in a lookup table must match Nstring in the Lookup transformation.