Define Common Variable Table

Assumptions – all DataStage jobs execute through the same pool or agent workstation.

Define a variable table to hold parameters common to all DataStage jobs and ADD the variable table to the pool/workstation definition. Do this by editing the workstation definition and supplying a variable tale value. This will need a JnextPlan to execute to take effect!

If you are not using a dedicated workstation for DataStage jobs, define the variables below in the default variable table (usually MAIN_TABLE) instead

Note The settings below are only examples- you must set the variables required for your specific environment

Define Job Specific Variable Table

Next define variable tables for each job and/or job stream that requires parameters that are ether in addition to the common parameters defined above or are different to those specified above i.e. if you need to override the settings specified in the common parameters

Some examples are shown below for two different jobs

Define Job Template

Now create a “template” job definition similar to the one shown below

Schedule for a single DataStage job per job stream/schedule

Create a schedule that uses the “template” job definition – the schedule is a standard schedule with the job definition as shown below.

Note that the job definition is the “template” job, but the Name is specified as job1 – this allows the same job definition to be reused and executed with a different job name in TWS

Schedule for multiple DataStage jobs per job stream/schedule

Create a schedule that uses the “template” job definition – the schedule is a standard schedule with the job definition for each job included..

In this case the common parameters are held in the variable table at the workstation and you will need to specify any job specific parameters in the job definition itself.

Therefore, you will need to create separate job definitions for each job rather than using a “template” job definition mentioned above

Extracted TWS object definitions for the samples above

Variable tables

VARTABLE ORB_DATASTAGE_COMMON_PARAMETERS

DESCRIPTION “Datastage common parameters”

MEMBERS

DS_CUSTEMAIL “orb@scotchwhisky.local”

DS_CYCLEDATE “2013-01-01”

DS_CYCLETYPE “N”

DS_DATASET “/orbdata/dataset”

DS_DOMAIN “ORB_DS_DOMAIN”

DS_DWCONNECTPARMS “DwDevConnectParms”

DS_ENV “/app/dev”

DS_INBOX “/orbdata/inbox”

DS_JOB “Not_Specified”

DS_PARAMETER_FILE “/opt/IBM/InformationServer/Server/DSEngine/bin/DataStageJobExecutor.properties”

DS_PWD “/DATLDSIS02/dsdev”

DS_REJECT “/orbdata/reject”

DS_SERVER “dev.datastageserver.scotchwhisky.local”

DS_SOURCE “OrbData”

DS_STAGING “/orbdata/staging”

DS_TRIGGER “/orbdata/trigger”

DS_TRIGGEROUT “OrbData_Trigger_Stage_compl”

DS_TRUNCCONNECTPARNS “DwDevTruncConnectParms”

DS_USER “Not_Specified”

DS_WARNEMAIL “orb@scotchwhisky.local”

END

VARTABLE ORB_DATASTAGE_JOB1

DESCRIPTION “DS parameters specific to job 1”

MEMBERS

DS_CYCLEDATE “2017-01-01”

DS_JOB “Build_Mart_OU”

DS_USER “orbdata1”

END

VARTABLE ORB_DATASTAGE_JOB2

DESCRIPTION “DS parameters specific to job2”

MEMBERS

DS_CYCLEDATE “2017-08-31”

DS_DATASET “/test/dataset”

DS_DOMAIN “ORB_TESTDS_DOMAIN”

DS_ENV “/app/test”

DS_INBOX “/test/inbox”

DS_JOB “MasterJobSeqLoadEmployeeM_S”

DS_PARAMETER_FILE “/opt/IBM/InformationServer/Server/DSEngine/bin/TestDataStageJobExecutor.properties”

DS_PATH “/opt/IBM/InformationServer/Server/DSEngine/bin”

DS_REJECT “/test/reject”

DS_SERVER “test.datastageserver.scotchwhisky.local”

DS_STAGING “/test/staging”

DS_TRIGGER “/test/trigger”

DS_USER “orbdata2”

END

Jobs

$JOBS

CLYNELISH_AGT#ORB_DATASTAGE_JOB_TEMPLATE

TASK

<?xml version=”1.0″ encoding=”UTF-8″?>

<jsdl:jobDefinition xmlns:XMLSchema=”http://www.w3.org/2001/XMLSchema”; xmlns:jsdl=”http://www.ibm.com/xmlns/prod/scheduling/1.0/jsdl”; xmlns:jsdldatastage=”http://www.ibm.com/xmlns/prod/scheduling/1.0/jsdldatastage”; XMLSchema:text=”resolveVariableTable” name=”DATASTAGE”>

<jsdl:application name=”datastage”>

<jsdldatastage:datastage>

<jsdldatastage:DataStageParameters>

<jsdldatastage:DataStagePanel>

<jsdldatastage:Logon>

<jsdldatastage:Domain>^DS_DOMAIN^</jsdldatastage:Domain>

<jsdldatastage:Server>^DS_SERVER^</jsdldatastage:Server>

<jsdldatastage:UserName>${agent:orbdata.DSuser.username}</jsdldatastage:UserName>

<jsdldatastage:password>${agent:orbdata.DSuser.userpwd}</jsdldatastage:password>

</jsdldatastage:Logon>

<jsdldatastage:JobDefinitionGroup>

<jsdldatastage:ProjectNameGroup>

<jsdldatastage:ProjectName>^DS_PROJECT^</jsdldatastage:ProjectName>

</jsdldatastage:ProjectNameGroup>

<jsdldatastage:JobNameButtonGroup>

<jsdldatastage:JobNameRadioButton>

<jsdldatastage:JobName>^DS_JOB^</jsdldatastage:JobName>

</jsdldatastage:JobNameRadioButton>

</jsdldatastage:JobNameButtonGroup>

<jsdldatastage:FileRemotePath>^DS_PARAMETER_FILE^</jsdldatastage:FileRemotePath>

<jsdldatastage:ParameterTableValues>

<jsdldatastage:ParameterTableValue key=”Source”>^DS_SOURCE^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”TriggerOut”>^DS_TRIGGEROUT^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”WarnEmail”>^DS_WARNEMAIL^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”TruncConnectParms”>^DS_TRUNCCONNECTPARMS^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”DWConnectParms”>^DS_DWCONNECTPARMS^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”$PWD”>^DS_PWD^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”$ENV”>^DS_ENV^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”$INBOX”>^DS_INBOX^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”$STAGING”>^DS_STAGING^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”$TRIGGER”>^DS_TRIGGER^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”$REJECT”>^DS_REJECT^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”$DATASET”>^DS_DATASET^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”CustEmail”>^DS_CUSTEMAIL^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”CycleDate”>^DS_CYCLEDATE^</jsdldatastage:ParameterTableValue>

<jsdldatastage:ParameterTableValue key=”CycleType”>^DS_CYCLETYPE^</jsdldatastage:ParameterTableValue>

</jsdldatastage:ParameterTableValues>

</jsdldatastage:JobDefinitionGroup>

<jsdldatastage:JobExecutionGroup>

<jsdldatastage:ForceReset/>

</jsdldatastage:JobExecutionGroup>

</jsdldatastage:DataStagePanel>

<jsdldatastage:OptionsPanel>

<jsdldatastage:JobOptionsGroup>

<jsdldatastage:WarningLimitButtonGroup>

<jsdldatastage:NoWarningLimitButton/>

</jsdldatastage:WarningLimitButtonGroup>

<jsdldatastage:RowLimitButtonGroup>

<jsdldatastage:NoRowLimitButton/>

</jsdldatastage:RowLimitButtonGroup>

<jsdldatastage:OperationalMetadataGroup>

<jsdldatastage:UseDefault/>

</jsdldatastage:OperationalMetadataGroup>

</jsdldatastage:JobOptionsGroup>

</jsdldatastage:OptionsPanel>

</jsdldatastage:DataStageParameters>

</jsdldatastage:datastage>

</jsdl:application>

</jsdl:jobDefinition>

DESCRIPTION “Test DataStage job definition 1”

RECOVERY STOP

Job stream/schedule

SCHEDULE CLYNELISH_AGT#ORB_DATASTAGE1

DESCRIPTION “Orb Data Stage job stream 1”

VARTABLE ORB_DATASTAGE_JOB1

ON RUNCYCLE WORKDAYS “FREQ=DAILY;INTERVAL=1;BYWORKDAY”

( AT 0600 JSUNTIL 0800 )

MATCHING PREVIOUS

FOLLOWS CLYNELISH_AGT#ORB_DATASTAGE1.@

:

CLYNELISH_AGT#ORB_DATASTAGE_JOB_TEMPLATE AS ORB_DATASTAGE_JOB1

END

SCHEDULE CLYNELISH_AGT#ORB_DATASTAGE2

DESCRIPTION “Orb Data Stage job stream 2”

VARTABLE ORB_DATASTAGE_JOB2

ON RUNCYCLE WORKDAYS “FREQ=DAILY;INTERVAL=1;BYWORKDAY”

( AT 0600 JSUNTIL 0800 )

MATCHING PREVIOUS

FOLLOWS CLYNELISH_AGT#ORB_DATASTAGE1.@

FOLLOWS CLYNELISH_AGT#ORB_DATASTAGE2.@

:

CLYNELISH_AGT#ORB_DATASTAGE_JOB_TEMPLATE AS ORB_DATASTAGE_JOB2

END

Hits: 237