@INROWNUM

veera24 · Post by **veera24** » Mon Jan 05, 2009 4:48 am

Hi all,
When i had used to generate the sequence number using inrownum function, DS itself have generated a duplicate value. Is it because of any buffer problem or what?

Similarly i want to know whether there is any limit in the sequential number value assigned to the stage variable too?
I mean, if we are generating any sequential number using stage variable means then any limit in the value being generated?

Your time will be highly appreciated.

Thanks in advance,
Veera.................

chulett · Post by **chulett** » Mon Jan 05, 2009 8:00 am

Never had a problem with either "ROWNUM" system variable. Please explain your job design and the nature of the "duplicate" value that it generated.

Also, search for EXACTNUMERIC for discussions on precision limits in DataStage.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 05, 2009 4:06 pm

The system variables @INROWNUM and @OUTROWNUM begin from 1 each time the job is run.

If that's not what you want, investigate the SDK key management functions, which use persistent storage of the next available key value.

chulett · Post by **chulett** » Mon Jan 05, 2009 4:10 pm

Well... I was assuming they meant a duplicate within the job run rather than run to run, as that one should be pretty obvious.

veera24 · Post by **veera24** » Tue Jan 06, 2009 3:15 am

chulett wrote:Never had a problem with either "ROWNUM" system variable. Please explain your job design and the nature of the "duplicate" value that it generated.

Also, search for EXACTNUMERIC for discussi ...

Could you tell me about how to change the default value of EXACTNUMERIC?

Thanks,
Veera...

chulett · Post by **chulett** » Tue Jan 06, 2009 8:25 am

In short, you edit your uvconfig file and then 'regen'. Why do you think you need to change it?

veera24 · Post by **veera24** » Thu Jan 08, 2009 7:13 am

chulett wrote:In short, you edit your uvconfig file and then 'regen'. Why do you think you need to change it? ...

I dont get you what you mean 'regen'. Could you explain?
And one more thing i dont want to change it i just wanna how to do that. thats it. Anyways thanks for your info and time.
And also, Could you tell me why DS generated a duplicate value when i used Inrownum function. I've explained in the thread about my design. Just i've used that function in the stage variable and incremented the output value by 1 for each record. When i had handled around 87000 records i've faced that error.

Hope i would've not confused you.

Youe timw would be highly appreciated.

Thanks in advance,
Veera

Mike · Post by **Mike** » Thu Jan 08, 2009 8:06 am

Couple of questions:
1) Do you have an active stage connected to another active stage?
2) Do you have row buffering enabled?

I seem to recall having weird issues with row buffering many moons ago in one of the 7x releases. I don't remember if I ever saw it with @INROWNUM (might have), but I definitely remember an issue where the aggregator stage mysteriously did not include one row's value. Turning off row buffering solved the aggregator issue. And as mysterious as the issue was, it was repeatable for that specific set ot test data.

If you haven't already, try disabling row buffering to see if your @INROWNUM issue goes away.

Mike

chulett · Post by **chulett** » Thu Jan 08, 2009 8:34 am

veera24 wrote:I've explained in the thread about my design.

No, you explained nothing about your job design, just stated you had a problem without providing any details. For example, do you have any kind of buffering enabled in your job? Did you post a list of the stages used or details of the transformations being done or examples of the problematic output?

So, are you saying that you created a stage variable with this as the derivation: @INROWNUM+1 and had a problem with the values it generated? Simple as that or is there more to it? Any downstream stages that might have affected that value? Details, please.

87000 is nowhere near 15 digits so my concern about the number getting 'too large' isn't an issue. You have to 'regenerate' to apply any changes made to the uvconfig file, something you do with DataStage shutdown with a similar command. As one should expect, searching the forums for "regen" will turn up a plethora of information on the subject.

veera24 · Post by **veera24** » Thu Jan 08, 2009 10:16 am

chulett wrote:
veera24 wrote:I've explained in the thread about my design.
No, you explained nothing about your job design, just stated you had a problem without providing any details. For example, do you have any kind of buffering enabled in your job? Did you post a list of the stages used or details of the transformations being done or examples of the problematic output?

So, are you saying that you created a stage variable with this as the derivation: @INROWNUM+1 and had a problem with the values it generated? Simple as that or is there more to it? Any downstream stages that might have affected that value? Details, please.

87000 is nowhere near 15 digits so my concern about the number getting 'too large' isn't an issue. You have to 'regenerate' to apply any changes made to the uvconfig file, something you do with DataStage shutdown with a similar command. As one should expect, searching the forums for "regen" will turn up a plethora of information on the subject.

Hi craig,

I apologise for not giving the design fully as you've expected. My design had a input sequential file stage, transformer and then output sybase table connected through ODBC stage. In my output, i need to generate a sequence number for each input record. So that i had used @INROWNUM+1 in the stage variable. The sequence number will be the key column in the target stage.
I didn't set any buffer alloaction.

When i ran the job i got a log because of dupliacate entry in to target table. As i said earlier, only one of the value got repeated (I wondered how?). So only one row got rejected when loaded into target table. Really we were confused at that time. Dont know how DS was working.

So finally we had changed our logic and ran it succesfully then.
At that time we dont know the reason.
So i just wanted to know how it has been happened? Kindly advice or let me know back if i've missed out anything you want from my end to get my question.

Hope i wouldn't have confused yet.

And one more thing craig, Pls dont post your comments as premium content. I'm much eager to know about the answer.

But definitely i'll be one of the premium member in very soon. So until then could you help me right?

Your time would be highly appreciated.

Thanks in advance,
Veera

veera24 · Post by **veera24** » Thu Jan 08, 2009 10:19 am

chulett wrote:
veera24 wrote:I've explained in the thread about my design.
No, you explained nothing about your job design, just stated you had a problem without providing any details. For example, do you have any kind of buffering enabled in your job? Did you post a list of the stages used or details of the transformations being done or examples of the problematic output?

So, are you saying that you created a stage variable with this as the derivation: @INROWNUM+1 and had a problem with the values it generated? Simple as that or is there more to it? Any downstream stages that might have affected that value? Details, please.

87000 is nowhere near 15 digits so my concern about the number getting 'too large' isn't an issue. You have to 'regenerate' to apply any changes made to the uvconfig file, something you do with DataStage shutdown with a similar command. As one should expect, searching the forums for "regen" will turn up a plethora of information on the subject.

Hi craig,

I apologise for not giving the design fully as you've expected. My design had a input sequential file stage, transformer and then output sybase table connected through ODBC stage. In my output, i need to generate a sequence number for each input record. So that i had used @INROWNUM+1 in the stage variable. The sequence number will be the key column in the target stage.
I didn't set any buffer alloaction.

When i ran the job i got a log because of dupliacate entry in to target table. As i said earlier, only one of the value got repeated (I wondered how?). So only one row got rejected when loaded into target table. Really we were confused at that time. Dont know how DS was working.

So finally we had changed our logic and ran it succesfully then.
At that time we dont know the reason.
So i just wanted to know how it has been happened? Kindly advice or let me know back if i've missed out anything you want from my end to get my question.

Hope i wouldn't have confused yet.

And one more thing craig, Pls dont post your comments as premium content. I'm much eager to know about the answer.

Your time would be highly appreciated.

Thanks in advance,
Veera

chulett · Post by **chulett** » Thu Jan 08, 2009 10:27 am

Sorry, but it's my choice when I post "Premium" comments and when I don't. What "logic change" did you make that made it work - from what to what, precisely?

veera24 · Post by **veera24** » Thu Jan 08, 2009 10:38 am

chulett wrote:Sorry, but it's my choice when I post "Premium" comments and when I don't. What "logic change" did you make that made it work - from what to what, precisely? ...

Hi craig,
Ofcourse craig its completely your wish to post your comments as premium or not. But if you would post that as normal content, then that would be highly helpful for me. Hope you would help me.

Regarding the design change,
Instead of having @INROWNUM+1 in stage variable, we've initialised the value of '1' in a stage variable and in another SV we have summed up

Thanks in advance,
Veera....

chulett · Post by **chulett** » Thu Jan 08, 2009 10:45 am

Sorry, but I can't explain the behaviour you saw. I use the ROWNUM variables "all the time" and have never seeing this issue. Have you opened a case with your official support provider? What exact 7.x version do you have and on what UNIX platform? If it's earlier than 7.5.1a then the answer may be an upgrade.

veera24 · Post by **veera24** » Thu Jan 08, 2009 8:46 pm

chulett wrote:Sorry, but I can't explain the behaviour you saw. I use the ROWNUM variables "all the time" and have never seeing this issue. Have you opened a case with your official support provider? What exact 7.x version do you have and on what UNIX platform? If it's earlier than 7.5.1a then the answer may be an upgrade.

Craig,
My version is 7.5.1.
I'll log the report to my support provider.

Thanks for the info and your time.

As i've noted in my first quetion, Is there any limit in the number value to stage variable?
Let me clearly explain.
If i wanna generate sequence number say, based on the number of records and if i'm using stage variables to generate the same then how much value does stage variable accomodate?is there any limit or no limit?

Your time would be highly appreciated.

Thanks in well advance,
Veera.......