Useful IIS Datastage Transformer Variables and Functions

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

Infosphere Information Server (IIS) Commonly Used Parameters

Parameters are a very big key in, Infosphere Information Server (IIS), to process flexibility in sequences, DataStage jobs, and DataQuality jobs.  Parameterization also helps reduce development effort, reduce the number of jobs required, and reuse of jobs, by allowing construction of multi-instance jobs, which are essentially reused code.

However, sometimes when starting a project, parameters need to created and doing so from memory, doesn’t always achieve the best results. So here is a quick starter list of parameters, which seem to be commonly encountered.  Hopefully, this list will aide in your parametrization efforts and setup.

TypePromptDescriptionExample
StringDS_DIRDataset Directory Path 
StringDS_LOG_DIRLog File Directory Path 
StringQUOTESQuotes
StringRECIPIENT_EMAILRecipient Email Address 
StringSENDER_EMAILSenders Email Address 
StringSMTP_SERVERSMTP Mail Server Name 
StringSQL_DIRSQL File Directory path 
StringDATE_OFFSETDate Offset Number1
StringDS_ENVIRONMENTDatastage EnvironmentPROD
StringSRC_DIRSource Files Directory 
StringSRC_KEY_GEN_DIRSource Key Generator Files Directory Path 
StringSRC_REJ_DIRSource Reject Files Directory Path 
StringWRK_DIRWorking Directory Path 
StringSRC_TABLESource Table Name 
StringDB_SCHEMADatabase Schema Name 
StringTGT_TBLTarget Table Name 
StringPROC_DTERun Control or processing date 
StringCURR_DTECurrent Date 

Related References