Useful IIS Datastage Transformer Variables and Functions

Algorithm
Algorithm

This is a list of InfoSphere Information Server (IIS)  Datastage transformer variables and functions, which I have found to be helpful over the years and tend to be frequently used.

ItemDescriptionSample
INROWNUM and OUTROWNUM variables@INROWNUM and @OUTROWNUM are internal DataStage variables which do the following:

 

●       @INROWNUM counts incoming rows to a transformer in a DataStage job

●       @OUTROWNUM counts outcoming rows from a transformer in a DataStage job

These variables can be used to generate sequences, primary keys, id’s, numbering rows

@INROWNUM

 

@OUTROWNUM

Substring in DataStageReturns delimited substrings in a string.

 

Syntax

Field (string, delimiter, instance [ ,number] )

 

string is the string containing the substring. If a string is a null value, null is returned.

 

delimiter is the character that delimits the substring. If delimiter is an empty string, string is returned. If string does not contain delimiter, an empty string is returned unless instance is 1, in which case string is returned. If delimiter is a null value, a run-time error occurs. If more than one substring is returned, delimiters are returned with the substrings.

 

instance specifies which instance of delimiter terminates the substring. If instance is less than 1, 1 is assumed. If string does not contain instance, an empty string is returned. If instance is a null value, a run-time error occurs.

 

number specifies the number of delimited substrings to return. If number is an empty string or less than 1, 1 is assumed. If number is a null value, a run-time error occurs.

The variable MyString is set to the data between the third and fourth occurrences of the delimiter “#”:

 

 

MyString = Field(“###DHHH#KK”,”#”, 4) ;* returns “DHHH”

 

In the following example SubString is set to “” since the delimiter “/” does not appear in the string.

 

MyString = “London+0171+NW2+AZ”

SubString = Field(Mystring, “/”, 1) ;* returns “”

 

In the following example SubString is set to “0171+NW2” since two fields were requested using the delimiter “+” (the second and third fields):

 

MyString = “London+0171+NW2+AZ”

SubString = Field(Mystring, “+”, 2, 2)

* returns “0171+NW2”

Special Characters in DataStageHandles/converts special characters in a transformer stage, which can cause issues in XML processing and certain databases.General:

 

Convert(“;:?\+&,*`#’$()|^~@{}[]%!”,””, TrimLeadingTrailing(DSLink_ES_XML.File))

 

Decimal and Double Quotes:

Convert(‘ ” . ‘,”, DSLink12.Field)

DSJobStartTimestampType: String. Date and time when the job started on the engine in the form YYYY-MM-DD hh:mm:ss.This variable can be helpful when you need to append the date on the end of a file.
DSJobStartTimeType: String. time when the job started on the engine in the form (24 hour clock) HH:MM:SS. 
DSJobStartDateType: String. Date and time when the job started on the engine in the form YYYY-MM-DD.This variable can be helpful when needing to provide a loaded date, which is consistent across all rows.
DSJobNameType: String. Actual name of the job referenced by the job handler.This variable can be helpful when needing to prove which processes row into a table or file, if more than one process is inserting data.

Vendor Documentation

For additional information the complete list of Infosphere Information Server (IIS) DataStage system variables, see IBM online documentation at:

System Variables

https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.5.0/com.ibm.swg.im.iis.ds.serverjob.dev.doc/topics/r_dsvjbref_System_Variables.html?lang=en

Supported macros and system variables

https://www.ibm.com/support/knowledgecenter/SSZJPZ_11.5.0/com.ibm.swg.im.iis.ds.parjob.dev.doc/topics/limitationsmacros.html?lang=en

Related References

4 thoughts on “Useful IIS Datastage Transformer Variables and Functions

Comments are closed.