Stage Variable NOT Stored Correctly

gainfomart · Post by **gainfomart** » Tue Apr 10, 2012 3:28 am

I am very green for the DataStage and I have just 2 days experiences for the DataStage.

I have been advised by my team-mates for having Stage Variable. However, he is out of town and I have to solve this problems within 3 days. But the Stage Variable which seems to be never stored.

I have captured most of the tasks imposed during my trials.

Step 1 - Sort Stage

Step 2 - Transformer Stage

kandyshandy · Post by **kandyshandy** » Tue Apr 10, 2012 3:38 am

When is that guy coming back? Will he?

priyadarshikunal · Post by **priyadarshikunal** » Tue Apr 10, 2012 4:40 am

:D

Yes you can do it with stage variables, you need to define a few stage variables.

Stage Variable=Derivation
svPrevTime=svCurrTime
svCurrTime=<input col>.Time_Chk
svPrevKey=svCurrKey
svCurrKey=<input col>.key1:'~':key2
svTimeDiff=compare the time here.
svConstraintYN=if svPrevKey=svCurrKey and timediff<30 then 'N' else 'Y'

then put a constraint as svConstraintYN='Y'

you need to make sure that svPrevTime and svPrevKey is defined above/before svCurrTime and svCurrKey respectively. you can use the exact order as i defined above.

Stage variables are executed sequentially from top to bottom and the same principal is being exploited here to store the last value.

qt_ky · Post by **qt_ky** » Tue Apr 10, 2012 4:57 am

Welcome to DataStage!

Parallel jobs have a lot of date and time functions built in. They are documented in the appendix of the Parallel Job Developer's Guide. You should have it on your client as a PDF under the Start menu... documentation icon. Check out this one for comparing two timestamps:

SecondsSinceFromTimestamp()

vamsi.4a6 · Post by **vamsi.4a6** » Tue Apr 10, 2012 6:05 am

priyadarshikunal wrote:Yes you can do it with stage variables, you need to define a few stage variables.

I have few doubts with respect to above post.

1)what is the intial value of svCurrTime and svCurrKey stage variables?If i did not mention anything in stage variables tab what values it will take after processing first record from input?

2)Is it necesary to mention intial values in the above requirement?

2)can i run the transformer stage in parallel mode in the above requirement?

priyadarshikunal · Post by **priyadarshikunal** » Tue Apr 10, 2012 8:56 am

vamsi.4a6 wrote:I have few doubts with respect to above post?
1)what is the intial value of svCurrTime and svCurrKey stage variables?If i did not mention anything in stage variables tab what values it will take after processing first record from input?

0 for integer and empty for varchar and so on. in stage variable tab you will see initial value column, define it there. as the stage variables can not be null.

2)Is it necesary to mention intial values in the above requirement?

Null should not be or better to say cannot be assigned to a stage variable.

2)can i run the transformer stage in parallel mode in the above requirement?

Yes if your data is partitioned. this will remove the records for the same key which came within 30 mins.

Kryt0n · Post by **Kryt0n** » Tue Apr 10, 2012 4:17 pm

Your primary fault is in your partitioning, you do not want to include chk_time in your partition as that may cause the two records to go down different lines.

jwiles · Post by **jwiles** » Tue Apr 10, 2012 9:12 pm

Regarding sorting and partitioning in your job:

1) DON'T select "Perform Sort" on the partitioning tab for the input link of a SORT stage. There are a few specific cases where this is useful (yours is not one of them!) and in most cases this is a waste of resources and reduces throughput.

2) Assuming that your transformer is immediately after your sort stage, you don't need to select "Perform Sort" in the input link of the transformer...you have just sorted the file and there is no need to sort it again.

3) As already mentioned in this thread, you shouldn't use the time field as a partition key...it will keep your logic from working correctly

4) Allow your transformer to run in parallel execution mode and set partitioning on the transformer to either Auto or Same...in this case, Same would probably be the better choice. You partitioned the data at the Sort stage and don't need to repartition it for your transformer.

Regards,

gainfomart · Post by **gainfomart** » Wed Apr 11, 2012 12:28 am

Thanks for your hints. It has been solved. My mate has also suggested me be aware about time functions. For example, "MinutesFromTime" is just for minute and it should be converted by hours + minutes...

Also, catering for cross-date transaction is needed.

Before the end of the reply, I would like to raise my thanks to all of you.

priyadarshikunal · Post by **priyadarshikunal** » Wed Apr 11, 2012 2:10 am

isn't it the time to say what solution worked for you and mark the topic resolved

qt_ky · Post by **qt_ky** » Wed Apr 11, 2012 4:56 pm

gainfomart wrote:For example, "MinutesFromTime" is just for minute and it should be converted by hours + minutes...

Also, catering for cross-date transaction is needed.

If you use the SecondsSinceFromTimestamp() function that I mentioned above, then I think you can avoid doing it "the hard way" (checking across hours and across days at midnight). By definition, the function "Returns the number of seconds between two timestamps." One check and you're done!