Reading Parameters From Database directly

[khanparwaz]

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

[Sainath.Srinivasan]

You can use job control and native db connection to do it. The only diff will be that the db is shared and can be accessed by various methods.

[kduke]

We have done this a lot. It is usually dumped to a hash file with the parameter name as the key then it is easily read in job control or a routine. You could ask the job what parameters it has and then automatically look them up.

[khanparwaz]

Hi Sri ,
i am new to datastage atleast give me an idea how to implement what ever you suggested.


thanks in advance.

[lebos]

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

[khanparwaz]

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

[mahindru]

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

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

[lebos]

'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

[khanparwaz]

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

[kduke]

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.

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:

[khanparwaz]

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

[kduke]

All you have to is run the job to extract once after you change the table. That is not hard. Doing SQL connects in BASIC is not easy and not recommended.

[gateleys]

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?

Code: Select all

RoutineActivity ----> JobActivity1 ----> JobActivity2------>JobActivity3
                                                /                    /
                                      RoutineActivity      Routine Activity

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?

[ray.wurlod]

Can Anyone Give A complete Solution.

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.

[kduke]

I think in 7.5.1 you can create variables in a sequence and reuse them. I think in earlier versions that all the arguments of a routine are available in the sequence after th routine has been called. So yes they should be resuable.