datastage sequencer error

[raja123]

Hi All,

I am calling a job from sequencer. The job parameter derivation I am defining in sequencer job activity.

In the job parameter I want to fetch sysdate in following format:

dd_mm_yyyy

the derivation which I am providing in job activity is

OCONV(DATE(),"D_")

But the sequencer is giving me following error:

Exception raised: @Job_Activity_4, Error calling DSSetParam(getsysdate), code=-4 [ParamValue/Limitvalue is not appropriate]

I have tried diff option of Iconv to get above format like

OCONV(DATE(),"D_E")
OCONV(DATE(),"D-DMY[2,2,4]")

but all are giving me the same error.

Please help me.

[sachin1]

Tried to implement what you want and OCONV(@DATE,"D_E") is working fine, also just try to compile your job called in sequencer and sequencer, also see getsysdate is string datatype in called job, it works fine.

[raja123]

Tried to implement what you want and OCONV(@DATE,"D_E") is working fine, also just try to compile your job called in sequencer and sequencer, also see getsysdate is string datatype in called job, it works fine.

thnx sachin.. it was date type in calling job. Thanks for your help !!

[raja123]

Tried to implement what you want and OCONV(@DATE,"D_E") is working fine, also just try to compile your job called in sequencer and sequencer, also see getsysdate is string datatype in called job, it works fine.

thnx sachin.. it was date type in calling job. Thanks for your help !!

Hi sachin,

I have one more issue:

I am generating sequential file randomly with filename_date_version.

what you helped me above is for filename_date, now I need to add version at the end of each generated sequential file name.

The job load is every day. If due to some reason, if I need to run the same job again, it should create a second version of the same file. for eg. filename_date_01 for first run and filename_date_02 for the second version. But if i m running the job on second day for the first time, the version should be reset to 01 again.

I think it could be done by shell script, but i m novice to shell script.

Please help me.

[ray.wurlod]

Write out an algorithm (specification) in English. You will then easily be able to see precisely what you need to do in your job sequence (note: a sequencer is a component in a job sequence) and easily implement it. You should not need to create a shell script (unless you want to); the flexibility of activities and expressions (and, perhaps, user variables) in job sequences ought to be enough.

[Minhajuddin]

To achieve this, you will have to store the "date" and the "sequence" number somewhere, so, that it can be retrieved in the next run.

> Get the date and sequence number at the start of the sequence.

> Compare the date previously fetched with the current date, If they are the same You'll have to increment the sequence number and use these in your filename. If they are not equal, you'll have to use the "current date" and a sequence number "1" in your file name.

> At the end you will have to store back these new values (date and sequence).

I would use a flat file to store this info.

Edited:

<a href="http://picasaweb.google.co.uk/super.min ... 5554"><img src="http://lh3.ggpht.com/super.minhaj/SB1iB ... nce_ss.JPG" /></a>

[ray.wurlod]

A simpler solution would be to get the date/time within a generating expression in the user variables activity.

[sachin1]

i am sorry to post a reply as the topic is already resolved, but i got one solution, so posting it, may raja123 can have a look on it.

imagine your entire file format is abc05_05_2008_01.txt.
'abc' is filename that is fixed and can be parameter, '01' is version and '05_05_2008' is date format.

i am also not good at shell script, but below is the one ...........

the shell script checks for file("filename+date") in directory , if found its version is increased, else outputs a value of format "abc05_05_2008_01" which can be used to create a new file.

with this we do not need to store any date and version in a file and need not update every time.

job design

Execute_Command_ ------------------->Job_Activity,

just call the script in Execute_Command with input parameter as filename just "abc" in our case.


#!bin/sh
count=0
dt=`date "+%d_%m_%Y"`

filename=$1$dt
filecount=`ls -lrt | grep $filename | wc -l`
if [[ $filecount -gt 0 ]] then
count=`ls -lrt | grep $filename | tail -1 | awk '{print $9}'|cut -d "_" -f 4 | cut -c 1,2 `
fi
count=`expr $count + 1`
if [[ $count -lt 10 ]] then
count='0'$count
fi
echo $filename'_'$count