Some Important Function of Datastage

Datastage String Functions:
#1. AlNum:
This functions check whether given string contains only and only alphanumeric
characters. I.e. if string contains any special characters it returns -1(False).
Example:If given string is ‘John$007’ then
AlNum (link_name.string_name) = -1 (false) as it contains special character ‘$’.
If given string is ‘John007’ then
AlNum (link_name.string_name) = 1 (True) as it contains only alphanumeric characters.
#2. Alpha:
This function checks whether thegiven string contains only alphabetic characters. I.e. from ‘Aa’ to ‘Zz’.
Example:If given string is ‘John007’ then
Alpha (link_name.string_name) =- 1 (False)
If given string is ‘John Margaret’ then
Alpha (link_name.string_name) = 1 (True)
#3. CompactWhiteSpace:
It just reduce the unnecessary white spaces between two words to single white space i.e. if given string contains ‘I    am   John’ then this function will give output ‘I am John’.
#4. Convert:
This function provides facility to change the content of string or whole string as a replacement. Example: If given string is ‘Jennifer’ and requirement is to change characters ‘nn’ to ‘NN’ then use we can use this function as follows
Convert(‘nn’,’NN’, link_name)
#5. Count:
It counts the number of times the given substring occurs in the string.
Example: If the given string is‘We are going really, really fast’. This function will give count 2 for substring ‘really’.
Count(link_name,’really’) =2.
#6. Dcount:
This functions returnscount of delimited fields in the string.
Example: If the given string is ‘john, ron, harry, rowling’ then this function will give count 3 for delimiter ‘,’ as shown below.
Dcount(link_name,’,’) =3
#7. Downcase:
This function is used change all the upper case letters in the string to lower case.
Example: If the given string contains ‘HorSeRiDiNg’ then this function will give
Downcase(link_name)=horse riding
#8. Dquote:
It puts the given string in double quotes.
Example: If given string is I am john, then this function returns
Dquote(link_name) =”I am John”
#9. Field:
This is important function which deals with the delimiter fields as it returns one or more substring depending upon the specified delimiters in a given string.
Example: Suppose given string is “adc89@nxcbs@nahs@ncn@xcz” where ‘@’ will be treated as a delimiter then
Field (link_name,’@’,1) = adc89
Field (link_name,’@’,1,3) = adc89@nxcbs@nahs
Field (link_name,’@’,2,2) = nxcbs@nahs
#10. Left:
This function returns the leftmost ‘n’ characters of string.
 Example: Suppose given string is “adc89@nxcbs@nahs@ncn@xcz” then
 Left (link_name, 3) = adc
#11. Len:
It returns the length of the given string.
 Example: if given string is “Chocolate” then
 Len (link_name) = 9
#12. Right:
This function returns the rightmost ‘n’ characters of string.
 Example: Suppose given string is “adc89@nxcbs@nahs@ncn@xcz” then
 Right (link_name, 3) = xcz
#13. Trim:
Trim is used to reduce additional space, tab or any unwanted occurrence of an character to one but it strictly removes the leading and trialing characters.This has the provision of additional argument to get desire result. We will see those with examples.
Example:

1. If given string is   I am James Bond  then Trim (link_name) =I am James bond. It removes additional trailing and leading whitespaces.
2. If given string is …I…am..James…Bond..then Trim (link_name, ‘.’) = I.am.James.Bond. It removes all leading, trailing dots with other reduced to one.
3.  Trim (link_name, ‘.’,’A’)=IamJamesBond. It removes all dots.
4.  Trim (link_name,’.’,’T’) = …I…am..James…Bond. Removes trailing dots only.

#14. TrimB:
It removes all trailing spaces and tabs from a string
TrimF: It removes all Leading spaces and tabs from a string
TrimLeadingTrailing:It removes all spaces and tabs from a string

Datastage Logical Functions:

#1. BitAnd:
This function returns the bitwise AND operation. Although function returns decimal numbers only but for understanding purpose one should be aware of general AND operation. Let’s see how it works here with a simple example.
Example: Consider two columns contains two values 10 and 12 respectively then
BitAnd (91,63) will perform bitwise operation and will return 27.
#2. BitCompress:
This returns integer made from or we can see it returns decimal equivalent of the input binary expression i.e. in 1s and 0s.
Example: Consider link Link_name.Value contains 0011011 then it will return
BitCompress(Link_Name.Value) = 27
#3. BitExpands:
This works exactly opposite to the BitCompress . It returns binary equivalent of input decimal expression.
Example: ConsiderLink_name.Value contains 27 then it will return
BitExpands(Link_Name.Value) = 0011011
#4. BitOr: 
This function returns bitwise OR operation.
Example: Consider two columns contains two values 91 and 63 respectively then
BitOr (91,63) will perform bitwise OR operation and will return 127.

#5. BitXOr: 
This function returns bitwise XOR operation.
Example: Consider two columns contains two values 91 and 63 respectively then
BitXOr (91,63) will perform bitwise XOR operation and will return 100.
#6. Not:
This function returns complement of the logical expression i.e. it returns True if the value of the logical expression is False and vice versa. This function works little bit differently for numeric expression. If it gives value not null value then this function will return false instead of true i.e. 0 and vice versa. Let’s take following example.
Example:If there is a subtraction “10-10” then
Not (10-10) = 1
For expression like there will not be null value output then
Not (10-9) = 0
#7. SetBit:
AS name suggest this function is used set particular bit in the expression. While declaring the function just we need give the Bit number which is to set. Let’s see one example.
Example:If input has value 99 which has binary equivalent 01100011 and if you want to set the bits numbered 3,4 and 5 then
SetBit (99, “3,4,5”, 1) = 127.
It will return 127 as shown.

Datastage Null Handling Functions:

Sometimes if there is any Null value in input derivation and if it is forwarded as it is then it may causes problems for further calculations. For this purpose these Null Handling Functions are used. We will see which are these functions and their functions one by one in detail.
#1. IsNotNull:
This functions checks if there is any Null value or not. It returns 1 when derivation does not contain null value. This function mostly used in condition statements like If…then. Let’s see an example.
Example: If IsNotNull(link_name.column_name) then link_name.column_name else “0”
In above example if any record in column_name column is not null then it is forwarded as it is and if it is null then it is written as 0.
#2. IsNull:
This functions exactly opposite to the previous one. It returns 1 when derivation evaluates a null value.
Example: If IsNull(link_name.column_name) then “0” else link_name.column_name
It sets 0 if column value contains null otherwise set to column value itself.
#3. NullToEmpty:
As name suggestsit simply writes null value to empty otherwise column value carried as it is.
Example: NullToEmpty(link_name.column_name )
#4. NullToZero:
As name suggestsit simply writes null value to 0 otherwise column value carried as it is.
Example: NullToZero(link_name.column_name )
#5. NullToValue:
As name suggests it simply writes null to a specific value which we declared otherwise column value carried as it is. See following example.
Example: NullToValue(link_name.column_name,12 )
Here it simply writes 12 if column contains any null value.
#6. SetNull:
It simply assigns null value to target/output column
Example: SetNull()
Above expression in Derivation field in the Transformer stage causes to set the target column to null.

Datastage Date Functions:

Functions to be use are should be in some specific formats like.
• For date %yyyy-%mm-%dd
• For time%hh:%nn:%ss. If extended in microseconds then %hh:%nn:%ss.xwhere ‘x’ givesnumber of decimals for seconds.
• For timestamp %yyyy-%mm-%dd %hh:%nn:%ss. If it’s include microseconds then %yyyy-%mm-%dd %hh:%nn:%ss.xwhere ‘x’ gives number of decimals for seconds.
• If some arguments have days of week then first three letters of that particular days are used.
Lets study some useful Datastage Date and Time Functions in detail.
1. CurrentDate():
Gives current date of the job runs in default date format as mention above.
2. CurrentTime():
Gives current time of the job runs in default time format as mention above.
3. CurrentTimeMS():
It gives time of the job runs in time format which include microseconds
4. CurrentTimeStamp():
Returns the time at which the job runs in timestamp format
5. CurrentTimeStampMS():
Gives the time at which the job runs in timestamp format, the time includes microseconds.
6. DateFromDaySince():
This one is used get a date by adding integer value (for number of days) in specified date. Let’s see one example.
Example: Consider mylink.mynumber contains 3 and mylink.mydate contains 1991-10-31. Following function will return
DateFromDaysSince(mylink.mynumber,mylink.mydate)= 1991-11-3
DateFromDaysSince(mylink.mynumber, 1991-10-31)= 1991-11-3
DateFromDaysSince(3, 1991-10-31)= 1991-11-3
If integer value is negative then that much days are get reduced from the specified date.
DateFromDaysSince(3, 1991-10-31)= 1991-10-27.
7. Date From Component():
This function gives representation in Date format for given three different values. Let’s take one example Example: Consider mylink.myyear contains 2010, Mylink.mymonth contains 5 and mylinkmyday contains 21. So following function will return
DateFromComponents(mylink.myyeal, mylink.mymonth, mylink.myday)= 2010-05-21.
If anybody wants to change the order of declaration, it will give the same result.
DateFromComponents(mylink.myyeal, mylink.myday, mylink.mymonth)= 2010-05-21.
One can directly give the data too i.e.
DateFromComponents(2010, 05, 21) = 2010-05-21.
8. DateFromJulian():
This one returns date from given Julian date.
DateFromJulian(mylink.myineteger)
9. DateOffsetByComponents:
This function is used to obtain a specific required date by applying offset to given date. Offset is applied over each of year, month and day of month for given date. Each offset value can be positive, negative or zero. Let’s consider on e example,
Example: If mylink.mydate contains 2015-05-25 and mylink.myyear contains ‘3’, mylink.mymonth contains ‘-2′ and mylink.myday contains ‘0’ then
DateOffsetByComponents(mylink.mydate, mylink.myyear, mylink.mymontho, mylink.myday) = 2018-02-25
From above we can say Positive value gets added, Negative gets subtracted and zero remains as per given date.
10. DaysSinceFromDate:
This function returns number of days from source date to the given date.
Example: If mylink.source contains 2014-06-09 and mylink.givendate contains 2014-06-15 then following function will return
DaysSinceFromDate(mylink.sourcedate, mylink.givendate)= 6.
One can give direct values for source and given date. i.e.
DaysSinceFromDate(“2014-06-09”,”2014-06-15”)= 6
11. DaysInMonth:
This returns number days in the month in the given source date
DaysInMonth(“1999-01-18″)= 31.
12. DaysInYear:
Similar to the above DaysInMonth
13. DateOffsetByDays:
Earlier we have seen offset by year, month as well as days. This function returns date offset by given number of days only. Similarly it also can hold positive, negative and zero values.
Example: Consider mylink.sourcedate contains 2014-06-09 and mylink.myoffsetday contains 6 then
DateOffsetByDays(mylink.basedate, mylink.dayoffset)= 2014-06-15.
If mylink.myoffsetday= -6 then
DateOffsetByDays(mylink.basedate, mylink.dayoffset)= 2014-06-03
14. HoursFromTime:
It just returns Hour portion of time.
HoursFromTime(mylink.mytime)
HoursFromTime(“20:09:00″)= 20
15. JulianDayFromDate:
It returns Julian day from given date.
JulianDayFromDate(mylink.mydate)
16. MicroSecondsFromTime:
Returns microsecond portion of a time.
MicroSecondsFromTime(mylink.mytime)
17. MidnightSecondsFromTime:
This one returns number of seconds from midnight i.e. from 00:00:00 to the specified time.
Example : if mylink.mydate contains 00:20:22(Consider date is in ‘Timestamp’ format) then it will return
MidnightSecondsFromTime(mylink.mydate)= 1222.
18. MinutesFromTime:
It’s same as above but returns minutes instead of seconds.
19. MonthsFromDate:
This is also same but returns month from given date specified.
20. NextWeekdayFromDate:
This one returns the date of specified day of week which is next for the date specified. The day, we can specify by full name or by just specifying first 3 letters of day. Let’s see an example,
Example: If mylink.mydate contains 2014-06-06 and date for day which we want is Wednesday then
NextWeekdayFromDate(mylink.mysourcedate, “wed”) = 2014-06-11
21. NthWeekdayFromDate:
It’s same as above but just returns date for ‘nth’ week for specified day from specified base date. ‘n’ can hold negative value also.
Example: If mylink.mydate contains 2014-06-06, day is ‘wednesday’ and week offset i.e. value of n is 1 then
NthWeekdayFromDate(mylink.mydate,”Wednesday”,1) = 2014-06-18.
NthWeekdayFromDate(mylink.mydate,”Wednesday”,-2) = 2014-05-21
22. PreviousWeekdayFromDate
This one returns the date of specified day of week which is previous for the date specified. Same as NextWeekdayFromDate
23. SecondsFromTime:
This returns seconds portion of time
24. SecondsSinceFromTimestamp:
This one returns difference in seconds between two timestamps.
Example: Let’s consider two dates are specified under this function.
SecondsSinceFromTimestamp(“2014–06–06 20:13:45″,”2014–06–07 20:13:45″) = -86400
25. TimeDate:
It returns the system Date and time in the format of ‘hh:mm:ss dd month yyyy’
26. TimeFromComponents:
Same as DateFromComponents. Just returns time instead of date.
27. TimeFromMidnightSeconds:
It returns the time given the number of seconds since midnight.
Example: If mylink.myseconds contains 120 then following value will return
TimeFromMidnightSeconds(“mylink.myseconds”) =00:02:00.

Datastage Mathematical Functions:

#1 Abs:

 This function performs ‘Absolute’ expression for given numeric value. Absolute returns unsigned magnitude of the numeric expression. Let’s see one example.
Example: If Datalink.Number1 contains 15 an Datalink. Number2 contains 20 then following function will returns.
Abs(Datalink.Number1 – Datalink.Number2) = 5
Abs(15 – 20) = 5

#2 Acos, Asin, Atan:

These functions calculates arc-cosine, arc-sine and arc- tangent of an expression. The expression should be in numeric value and result expressed in radians or we can say it simply calculates inverse sine, cosine and tangent value of given value.

#3 Ceil:

 This function gives integer value from given decimal values depending upon the fraction part of that decimal value i.e. if fraction part is greater than 0.5 then it returns next integer. Lets see one example.
Example: Consider datalink.mynumber  contains the number 10.61 then it will return
Ceil(datalink.mynumber) = 11

#4 Sin, Cos, Tan  

These functions return trigonometric Sine, Cosine and Tangent of an expression. The provided expression should ne numeric value and it will return angle in radian.
Example:  Sin (0.3) = 0.2955
Cos(0.6) = 0.8253
Tan(0.2) = 0.2027

#5 Sinh, Cosh, Tanh():

These return hyperbolic Sine, Cosine and Tangent instead of trigonometric of an expression. The provided expression should ne numeric value.
Example:  Sinh (2.5) = 6.0502
Cosh(2.5) = 6.1322
Tanh(2.5) = 0.9866

#6 Div:

This function returns division of two numbers. It has format like (dividend, divisor).
Example: Consider datalink.dividend contains the number 24 and datalink.divisor contains 6 then following function will return
Div(datalink.dividend, datalink.divisor) = 4
Div(24,6) = 6

#7 Exp:

It calculates exponential function i.e. it returns result of base ‘e’ raised to the power for the provided value. Value of ‘e’ is approximately 2.71828.
Example: If datalink.mynumber contains the number 5 then this will return
Exp(datalink.mynumber) = 148.4126
Exp(5) = 148.4126

#8 Fabs:

Calculates absolute value of the given float number.
Example: if datalink.mynumber contains -11.22 then this will return
Fabs(datalink.mynumber) = 11.22

#9 Floor:

This function returns largest integer value less than or equal to the specified decimal value.
Example: If datalink.mynumber contains 93.31 then this will return
Floor(datalink.mynumber) = 93

#10 Llabs:

Returns absolute value of integer.
Example: If datalink.mynumber contains -5 then this will return
Llabs(datalink.mynumber) = 5

#11 Ln:

This is one of the most useful functions we require while dealing with Datastage. It calculates logarithmic of expression in base ‘e’ which is called as Natural Log of an expression. Natural Log of 0 does not exist and ‘e’ holds approximate value of 2.71828.
Example: If datalink.mynumber contains 25 then this will return
Ln(datalink.mynumber) = 3.2188

#12 Log10:

Calculates logarithmic value to the base 10 of given expression.
Example: If datalink.mynumber contains 25 then this will return
Log10(datalink.mynumber) = 1.398

#13 Max:

It simply returns greater or maximum value between two provided values.
Example: If datalink.mynumber1 and datalink.mynumber2 contains 25 and 12 respectively then this will return
MiX (datalink.mynumber1, datalink.mynumber2) = 25

#14 Min:

It returns smaller or minimum value between two provided values.
Example: If datalink.mynumber1 and datalink.mynumber2 contains 25 and 12 respectively then this will return
Min (datalink.mynumber1, datalink.mynumber2) = 12

#15 Mod:

This one returns remainder of two expressions i.e. dividend and divisor.
Example: If datalink.mydividend and datalink.mydivisor contains 25 and 12 respectively then this will return
Mod (datalink.mydividend, datalink.mydivisor) = 1

#16 Neg:

It simply negates the given expression.
Example: If datalink.mynumber contains 25 then this will return
Neg(datalink.mynumber) = -25

#17 Pwr:

It calculates the value of expression raise to a specific value.
Example: If datalink.myexpression and datalink.mypower  contains 4 and 3 respectively then this will return
Pwr (datalink.myexpression, datalink.mypower) = 64

#18 Sqrt:

Returns square root of given value.
Example: If datalink.mynumber contains 25 then this will return
Sqrt(datalink.mynumber) = 5