Reading Parameters From Database directly
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 60
- Joined: Tue Jul 12, 2005 3:34 am
Reading Parameters From Database directly
Hi,
Currently we are reading parameters from parametrs files
that parameter file we create through a job which reads value from
metadata table for a particular module we generate the parameter by selecting the value from dbase for that module & after that we read that
file using a routine which set parameter for job/sequence from that file.
My question is that can we skip this file creation & can we set parameters
from database directly Do we have to create a routine for that
or is their any mechanism available in Data Stage
Currently we are reading parameters from parametrs files
that parameter file we create through a job which reads value from
metadata table for a particular module we generate the parameter by selecting the value from dbase for that module & after that we read that
file using a routine which set parameter for job/sequence from that file.
My question is that can we skip this file creation & can we set parameters
from database directly Do we have to create a routine for that
or is their any mechanism available in Data Stage
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 60
- Joined: Tue Jul 12, 2005 3:34 am
I have several jobs which do this.
Use an appropriate db stage to read the parameters from the database. Then, in the Transformer have a derivation something like this:
If UtilityGetRunJobInfo(UtilityRunJob('JobName', 'Parameter1=':value1: '|Parameter2=':value2 : '|Parameter3=':value3 : '|Parameter4=' : In.Parameter5,0,0), 'JobCompletionStatus', '') = 1 Then In.Database Else UtilityAbortToLog('Error in JobName')
Parameter5 is the one read from the db. The others are just normal job parameters. UtilityRunJob runs the job (JobName) where the real work is done. UtilityGetRunJobInfo checks the status of that job and aborts if there are any errors. The syntax is a tad touchy so be careful with spaces, commas, and quote marks.
Hope this helps.
Larry
Use an appropriate db stage to read the parameters from the database. Then, in the Transformer have a derivation something like this:
If UtilityGetRunJobInfo(UtilityRunJob('JobName', 'Parameter1=':value1: '|Parameter2=':value2 : '|Parameter3=':value3 : '|Parameter4=' : In.Parameter5,0,0), 'JobCompletionStatus', '') = 1 Then In.Database Else UtilityAbortToLog('Error in JobName')
Parameter5 is the one read from the db. The others are just normal job parameters. UtilityRunJob runs the job (JobName) where the real work is done. UtilityGetRunJobInfo checks the status of that job and aborts if there are any errors. The syntax is a tad touchy so be careful with spaces, commas, and quote marks.
Hope this helps.
Larry
-
- Participant
- Posts: 60
- Joined: Tue Jul 12, 2005 3:34 am
Hi Lebos ,
just want to know how this will set parameters for the job will it take automatically i am still confused ,one more thing what would be target in this case as we have to specify an output for transformer i am creating that derivation in stage variable but its not working might be possible i am missing something in syntax
the syntax i am using is :
If UtilityGetRunJobInfo(UtilityRunJob('Folderstagetest', 'M_FTP_MXG2000_Read_Lnk.PARAMETER_NAME =' :M_FTP_MXG2000_Read_Lnk.PARAMETER_VALUE, 0, 0), 'JobCompletionStatus', '') =1 Then M_FTP_MXG2000_Read_Lnk.PARAMETER_VALUE Else UtilityAbortToLog('Error in JobName')
where 'M_FTP_MXG2000_Read_Lnk.PARAMETER_NAME input link contains the name of parameter and M_FTP_MXG2000_Read_Lnk.PARAMETER_VALUE is the input column for parameter value.
the structure od table frm wher ei m reading this parameter metadata is:
PARAMETER_NAME PARAMETER_VALUE
serrver_str_param dsg01.world
i would be thankful if you give
me an example in detail.
thanks for ur help
just want to know how this will set parameters for the job will it take automatically i am still confused ,one more thing what would be target in this case as we have to specify an output for transformer i am creating that derivation in stage variable but its not working might be possible i am missing something in syntax
the syntax i am using is :
If UtilityGetRunJobInfo(UtilityRunJob('Folderstagetest', 'M_FTP_MXG2000_Read_Lnk.PARAMETER_NAME =' :M_FTP_MXG2000_Read_Lnk.PARAMETER_VALUE, 0, 0), 'JobCompletionStatus', '') =1 Then M_FTP_MXG2000_Read_Lnk.PARAMETER_VALUE Else UtilityAbortToLog('Error in JobName')
where 'M_FTP_MXG2000_Read_Lnk.PARAMETER_NAME input link contains the name of parameter and M_FTP_MXG2000_Read_Lnk.PARAMETER_VALUE is the input column for parameter value.
the structure od table frm wher ei m reading this parameter metadata is:
PARAMETER_NAME PARAMETER_VALUE
serrver_str_param dsg01.world
i would be thankful if you give
me an example in detail.
thanks for ur help
Last edited by khanparwaz on Tue Sep 13, 2005 11:40 am, edited 1 time in total.
Re: Reading Parameters From Database directly
Here is one solution that I am using
1. Create a Unix script to select all the necessary parameter values from the database and store them in the unix variables in that script.
Unix_Variable=`sqlplus -silent user/passwd <<END
set pagesize 0 linesize 170 feedback off verify off heading off echo off
select Column_Name
from Table_name
where table_name = 'YOUR_TABLE_NAME';
exit;
END`
2. Later Pass that unix variable as parameter to execute the the datastage job sequence.
dsjob -run -param parameter1="$Unix_Variable" DS_Project_Name DS_sequence_name
This solution is workin for me.
Thanks
Varun Mahindru
1. Create a Unix script to select all the necessary parameter values from the database and store them in the unix variables in that script.
Unix_Variable=`sqlplus -silent user/passwd <<END
set pagesize 0 linesize 170 feedback off verify off heading off echo off
select Column_Name
from Table_name
where table_name = 'YOUR_TABLE_NAME';
exit;
END`
2. Later Pass that unix variable as parameter to execute the the datastage job sequence.
dsjob -run -param parameter1="$Unix_Variable" DS_Project_Name DS_sequence_name
This solution is workin for me.
Thanks
Varun Mahindru
khanparwaz wrote:Hi,
Currently we are reading parameters from parametrs files
that parameter file we create through a job which reads value from
metadata table for a particular module we generate the parameter by selecting the value from dbase for that module & after that we read that
file using a routine which set parameter for job/sequence from that file.
My question is that can we skip this file creation & can we set parameters
from database directly Do we have to create a routine for that
or is their any mechanism available in Data Stage
'M_FTP_MXG2000_Read_Lnk.PARAMETER_NAME' needs to be the actual parameter name, not an input link which contains the parameter name.
The parameter value would be read from the db in a db stage and passed into the Transformer where it can be accessed. And yes, you do need an output from the Transformer - just make it a sequential stage and set the file name to "/dev/null".
Larry
The parameter value would be read from the db in a db stage and passed into the Transformer where it can be accessed. And yes, you do need an output from the Transformer - just make it a sequential stage and set the file name to "/dev/null".
Larry
-
- Participant
- Posts: 60
- Joined: Tue Jul 12, 2005 3:34 am
Re: Reading Parameters From Database directly
hi varun
i dont want creation of any file in between coz at the time of migrating it to other enviroment i have to keep in mind whether i have imported all the scripts or not.
thanks any way
i dont want creation of any file in between coz at the time of migrating it to other enviroment i have to keep in mind whether i have imported all the scripts or not.
thanks any way
Write a job that reads your parameter names and values and writes them to a hash file with ParamName as the key. Next use this routine to start your job.
I took their routine and cleaned it up so it is easier to understand. If you cannot understand this then give up.
This is the way to run a job from routine activity. I plan on including something like this with ParameterNavigator only you can choose a parameter set of values at run time like DEV, TEST or PROD. So the same code works in DEV, TEST and PROD. Read and edit the values out of a database.
I took their routine and cleaned it up so it is easier to understand. If you cannot understand this then give up.
This is the way to run a job from routine activity. I plan on including something like this with ParameterNavigator only you can choose a parameter set of values at run time like DEV, TEST or PROD. So the same code works in DEV, TEST and PROD. Read and edit the values out of a database.
Code: Select all
* ------------------------------------------------------------
* KimRunJobRtn(JobName, WarnFlag, ParamFile)
* Decription: Run a job. Set param value from KimParamsHash
* Written by: Kim Duke
* Notes:
* ------------------------------------------------------------
$INCLUDE DSINCLUDE JOBCONTROL.H
Deffun DSRTimestamp Calling "DSR_TIMESTAMP"
* WarnFlag = @FALSE
* ------------------------------------------------------------
Ans = @FALSE
RoutineName = 'KimRunJobRtn'
JobStartTime = DSRTimestamp()
JobHandle = DSAttachJob(JobName, DSJ.ERRFATAL)
* ------------------------------------------------------------
open ParamFile to ParamFilePtr else
Message = "Error: Unable to open hash file: " : ParamFile
Call DSLogInfo(Message, RoutineName)
goto TheEnd
end
* ------------------------------------------------------------
DsErrCode = DSPrepareJob(JobHandle)
RowLimit = 0
WarnLimit = 0
DsErrCode = DSSetJobLimit(JobHandle, DSJ.LIMITROWS, RowLimit)
DsErrCode = DSSetJobLimit(JobHandle, DSJ.LIMITWARN, WarnLimit)
ParamNameList = DSGetJobInfo(JobHandle, DSJ.ParamNameList)
ParamCount = Dcount(ParamNameList, ',')
For ParamNum = 1 To ParamCount
ParamName = field(ParamNameList, ",", ParamNum)
Read ParamValueRec From ParamFilePtr, ParamName Then
ParamValue = ParamValueRec<1>
Message = "Setting: " : ParamName :" = ": ParamValue
Call DSLogInfo(Message, RoutineName)
DsErrCode = DSSetParam(JobHandle, ParamName, ParamValue)
End
Next ParamNum
DsErrCode = DSRunJob(JobHandle, DSJ.RUNNORMAL)
DsErrCode = DSWaitForJob(JobHandle)
JobStatus = DSGetJobInfo(JobHandle, DSJ.JOBSTATUS)
JobEndTime = DSRTimestamp()
Begin Case
Case JobStatus = DSJS.RUNFAILED
Message = "Job Failed: " : JobName
Call DSLogWarn(Message, RoutineName)
Case JobStatus = DSJS.RUNWARN and WarnFlag
Message = "Job had warnings: " : JobName
Call DSLogWarn(Message, RoutineName)
Case JobStatus = DSJS.RUNOK
Ans = @TRUE
End Case
* ------------------------------------------------------------
* Retrieve more information about this job run.
* ------------------------------------------------------------
Message = 'Getting job statistics'
Call DSLogInfo(Message, RoutineName)
StageNameList = DSGetJobInfo(JobHandle, DSJ.StageNameList)
Message = 'List of Stages = ' : StageNameList
Call DSLogInfo(Message, RoutineName)
StageCount = Dcount(StageNameList, ',')
RowCountRec = ""
RowCountRec<1> = JobName
RowCountRec<2> = "Start Time: " : JobStartTime
RowCountRec<3> = "End Time : " : JobEndTime
FOR StageNum = 1 To StageCount
* ------------------------------------------------------------
* Get links on this stage.
* ------------------------------------------------------------
StageName = Field(StageNameList, ',', StageNum)
LinkNameList = DSGetStageInfo(JobHandle, StageName, DSJ.LINKLIST)
Message = 'LinkNameList for Stage. ' : StageName :@FM: LinkNameList
Call DSLogInfo(Message, RoutineName)
LinkCount = Dcount(LinkNameList, ',')
For LinkNum = 1 To LinkCount
LinkName = Field(LinkNameList, ',', LinkNum)
* ------------------------------------------------------------
* Get Rowcount For this linkname
* ------------------------------------------------------------
RowCount = DSGetLinkInfo(JobHandle, StageName, LinkName, DSJ.LINKROWCOUNT)
Message = 'RowCount for ' : StageName :".": LinkName :" = ": RowCount
Call DSLogInfo(Message, RoutineName)
RowCountRec<-1> = StageName :'.': LinkName :' = ': RowCount
Next LinkNum
Next StageNum
Message = 'RunJob Status = ' : RowCountRec
Call DSLogInfo(Message, RoutineName)
* Ans = @TRUE
* ------------------------------------------------------------
TheEnd:
Mamu Kim
-
- Participant
- Posts: 60
- Joined: Tue Jul 12, 2005 3:34 am
Hi Kim ,
Thanks For your Reply ,but some thing like this we are already following
we are writing that into seqential file & then call that file using routine
but my question was to avoid the files completely just only databse .
so that we can select the requires value for parameters through db
stage & set the parameters from then without writing them into files.
& whenever we want to change the parameters value we can easily go to database & change that so the whole idea is that i just want to ignore the intermidiate work (creation of files to store parameters value).
Can Anyone Give A complete Solution.
Thanks In Advance
Thanks For your Reply ,but some thing like this we are already following
we are writing that into seqential file & then call that file using routine
but my question was to avoid the files completely just only databse .
so that we can select the requires value for parameters through db
stage & set the parameters from then without writing them into files.
& whenever we want to change the parameters value we can easily go to database & change that so the whole idea is that i just want to ignore the intermidiate work (creation of files to store parameters value).
Can Anyone Give A complete Solution.
Thanks In Advance
Hi,
I am following Kim's advice about having a job that reads parameters from a table (containing param names and values) and dumps them into a hash file.
Then I used your routine (in the Routine Activity) to run a job. My question is, if I have multiple jobs using the same parameters, would it be ok if the sequence design is as follows?
Or, is there a way by which I can propagate the parameters from the Initial RoutineActivity to all the Job Activity stages? Also, what should be the argument supplied to the WarnFlag, initially?
I am following Kim's advice about having a job that reads parameters from a table (containing param names and values) and dumps them into a hash file.
Then I used your routine (in the Routine Activity) to run a job. My question is, if I have multiple jobs using the same parameters, would it be ok if the sequence design is as follows?
Code: Select all
RoutineActivity ----> JobActivity1 ----> JobActivity2------>JobActivity3
/ /
RoutineActivity Routine Activity
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That kind of request presupposes some kind of commercial transaction. Complete solutions take some time to prepare, and self-employed consultants particularly don't have all that much "give" time available.khanparwaz wrote:Can Anyone Give A complete Solution.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.