Double convert in Datastage

Convert Function

Replaces every instance of specified characters in a string with substitute characters.

Syntax-Convert (list, new.list, string)

The two lists of characters correspond. The first character of new.list replaces all instances of the first character of list, the second replaces the second, and so on. If the two lists do not contain the same number of characters:

1)Any characters in list with no corresponding characters in new.list are deleted from the result. 

2)Any surplus characters in new.list are ignored.

3)We can not replace a string with string  by using convert function we have to use parallel Ereplace c++ routine

Example-How to separate alphabets and numeric values?

Input               

col             

Abc123456            

Pqrs698               

output              

col_1,col_2                 

Abc,123456

Pqrs,698

Convert(Convert('ABCDEFGHIJKLMNOPQRSTUVWXYZZ','',Upcase(Col))

,'',Col)=col_1

Convert(Convert('0123456789','',Upcase(Col)),'',Col)=col_2

Here I am using  second point as a reference

How to find the list of sequences that are restart able in a Datastage project?

1)Get the the list of the Jobs using dsjob -ljobs <project> 
2)Identify restartability of Job by using dsjob -jobinfo <project> <job> command[Job Restartable].If the Job Restartable=0 means then that sequence is non-restartable.
3)The step 2 should be repeated for all the Jobs in a project[Use Unix scripting or Loop activity in Datastage]

Counter Using Transformer

In a parallel job  a unique counter is generated by using special parallel macros.

  

1.  Create a stage variable for the counter, eg. SVCounter.  

2.  At the Stage Properties form set the Initial Value of the Stage Variable to 

"@PARTITIONNUM - @NUMPARTITIONS + 1".

3.Set the derivation of the stage variable to "svCounter + @NUMPARTITIONS". 

4.Create a column(counter) in the output of the Transformer and  set the derivation of the column(counter) to svCounter(stage variable)

 For 4 node configuration each instance will start at a different number, eg. -3, -2,-1,0 When the counter is incremented each instance is increment by the number of partitions, eg. 4. This gives us a sequence in instance 1 of 1, 5, 9, 13... Instance 2 is 2, 6, 10, 14... etc.In this process the counter will always start from 1.This method only works if the input data is evenly balanced i.e. equal number of rows going through each partition.

In 8.5 version  developer can generate the unique counter(Surrogate Key) by using Surrogate Key tab in the transformer.User need to mention database sequence name and database crendentials where database sequence name is created.

Version.xml File in Datastage

Version.xml File

In many cases Datstage Admin  need to know the history of patches that have been applied to their DataStage or Information Server installations.We can get patch information from the file Version.xml.The file is loacated in $DSHOME/../.. for 8.x versions .For 7.5.x versions of DataStage, there is not a required place where patch history is kept.There is one case where you may not have a Version.xml file, and that is any 8.0 system where no patches or fix packs have been applied. Then there will not be a Version.xml file, and that will tell you that no patches or fix packs have been applied

1) Go to cd $DSHOME/../.. from Unix command prompt

2)In Version.xml we can see following information based upon patches that are applied to Datastage or Information Server

<History>
    <HistoricalEvent description="Information Server Installation" eventDate="2011-02-15T13:17:29.95" installType="INSTALL" installerId="information.server" rollback="/opt/IBM/InfoSphere/InformationServer/_uninstall" status="SUCCESS" userId="root" version="8.5.0.0"/>
    <HistoricalEvent description="Information Server Installation" eventDate="2011-02-15T16:02:15.642" installType="INSTALL" installerId="information.server" rollback="/opt/IBM/InfoSphere/InformationServer/_uninstall" status="SUCCESS" userId="root" version="8.5.0.0"/>
    <HistoricalEvent description="Information Server 8.5 FixPack 1 (fixpack.is_85.aix.FP1_RC1.2.110408)" eventDate="2011-07-15T16:29:15.511" installType="PATCH" installerId="fixpack_FP1_IS85_8500" rollback="/opt/IBM/InfoSphere/InformationServer/Updates/fixpack_FP1_IS85_8500" status="Success" userId="root" version="8.5.0.1"/>
    <HistoricalEvent description="This modpack is Business Glossary V8.5.0.0. It contains updates to both Admin and Browser function." eventDate="2011-07-15T20:39:25.483" installType="PATCH" installerId="patch_JR39083_BG_all_85" rollback="/opt/IBM/InfoSphere/InformationServer/Updates/patch_JR39083_BG_all_85" status="Fail" userId="root" version="8.5.0.1"/>
    <HistoricalEvent description="patch_JR39537_ISM_unix_8500-1: Fix for error - Object ID locked by another user" eventDate="2011-07-15T21:15:53.270" installType="PATCH" installerId="patch_JR39537_ISM_unix_8500-1" rollback="/opt/IBM/InfoSphere/InformationServer/Updates/patch_JR39537_ISM_unix_8500-1" status="Success" userId="root" version="8.5.0.1"/>
    <HistoricalEvent description="patch_JR34358_ISM_unix_8500-1: Unable to include External Folder files in a package if a DataStage project is created in non default location." eventDate="2011-07-20T19:05:03.64" installType="PATCH" installerId="patch_JR34358_ISM_unix_8500-1" rollback="/opt/IBM/InfoSphere/InformationServer/Updates/patch_JR34358_ISM_unix_8500-1" status="Success" userId="root" version="8.5.0.1"/>
  </History>

3)We can also obtain IBM Product information  and their versions as below in Version.xml

<Products>
    <Product productId="metadata.server" version="8.5.0.1"/>
    <Product productId="DataStageCommon" version="8.5.0.1"/>
    <Product productId="datastage" version="8.5.0.1"/>
    <Product productId="qualitystage" version="8.5.0.1"/>
    <Product productId="InformationAnalyzer" version="8.5.0.1"/>
    <Product productId="BusinessGlossary" version="8.5.0.1"/>
    <Product productId="BusinessGlossaryAnywhere" version="8.5.0.1"/>
    <Product productId="MetadataWorkbench" version="8.5.0.1"/>
    <Product productId="FastTrack" version="8.5.0.1"/>
    <Product productId="ISTools" version="8.5.0.1"/>
    <Product productId="ISALite" version="8.5.0.1"/>
  </Products>