Sequence number generation issue in target database

[prasson_ibm]

Hi,

I have requirment of migrating PL/SQL codes into datastage jobs.My source and target are Oracle.

In PL/SQL code while loading data into targtet table ,seqno is generated in insert statement using stored proc and in case of exceptions its again called.Sql code is like this:-

Code: Select all

"BEGIN    INSERT INTO DWH.WAC(SYS_SOURCE_BATCH_ID, SYS_SOURCE_SEQUENCE_NO, SYS_LOAD_DATE, SYS_PROCESS_CODE)
VALUES(?<<OutputRoot.MQRFH2.usr.iSequenceNumber>>,
TO_NUMBER(RTRIM(?<<Environment.usr.counter>>)),
TRUNC(SYSDATE), 'N', 'A');
EXCEPTION   WHEN OTHERS   THEN
INSERT INTO DWH.WAC_HSP (SYS_SOURCE_BATCH_ID, SYS_SOURCE_SEQUENCE_NO, SYS_LOAD_DATE, SYS_PROCESS_CODE)
VALUES(?<<OutputRoot.MQRFH2.usr.iSequenceNumber>>,TO_NUMBER(RTRIM(?<<Environment.usr.counter>>)), TRUNC(SYSDATE), 'E', 'A');
END;"

I am converting this code into below design:-

srcOra-------> Transf--------> TgtOra -------------> HsptRejOra

In my design,i am generating seqence number in transformer

SeqNo=(@PARTITIONNUM+((@INROWNUM-1)* @NUMPARTITIONS))+1


My issue with this design is whenever hospital rejection happens on target table,TgtOra will miss that seqno and that seqno will be present in HsptRejOra.But in PL/SQL code its generating seqno saperatly for target and rejections.
Can anyone assist me to chieve the same design.Is it possible we can call TO_NUMBER(RTRIM(?<<Environment.usr.counter>>)) in insert statement in TgtOra and HsptRejOra.

[boxtoby]

Hi Prasoon,

Can you clarify what input and output you get from the pl/sql example and what DataStage is giving you so we can be clear as to what the problem is?

Something like:

Input TgtOra RejOra
1 1
2 2
3 3
4 4

Cheers,
Bob.

[boxtoby]

That didn't really work, did it!

Hopefully, this will demonstrate what I was trying to say:

Input 1
TgtOra 1
RejOra

Input 2
TgtOra 2
RejOra

Input 3
TgtOra
RejOra 3

Input 4
TgtOra 4
RejOra


Cheers,
Bob.

[prasson_ibm]

Hi Bob,
Yes you are correct,but PL/SQL out will be different.If input has 10 records and in that 2 records are rejected to Hspt,is should generate below sequene numbers:-

TgtOp
1
2
3
4
5
6
7
8
RejOra
1
2

But current deisgn will give below output:-

TgtOp
1
2
5
6
7
8
9
10
RejOp
3
4

[boxtoby]

Hi Prasoon,

The problem is that you have @INROWNUM in your key generation statement which always reflect the current record therefore you cannot restart the numbering for the reject table.

What I would suggest is using inserting a transformer between the target table and the reject table like this:

srcOra-------> Transf--------> TgtOra -----TRANSF--------> HsptRejOra

In both transformer I would try using the NextSurrogateKey function to generate the key. Each transformer would use a different source file for the key and so the sequence numbering will be independent for each table.

You will need to enter the details of the surrogate key file on the appropriate tab on the trasnformer stage properties panel.


Either that or find a different expression for your key generator!


Cheers,
Bob.

[prasson_ibm]

Hi bob,
Thanks for your solution,but I what about the missing seqno in TgtOra.let's take our same example.With the solution given by you,I can generate 1,2 for RejtOra but still in my target 3,4 sewno will be missing.my reqt is to acieve below output:-
TgtOra
1
2
3
4
5
6
7
8
RejOra
1
2
If I am able to call some counter function in insert statement of tgtOra and rejOra stages,then my issue will may be resolved.

[chulett]

Guys, please stop with the constant "Reply with quote" action. Save it for when it actually serves a purpose, otherwise please use the perfectly lovely Reply to topic link that doesn't repeat everything from the previous post. And that also saves me from having to run around and clean up posts constantly...

Thanks.

[boxtoby]

Hi Prasoon,

You should be ok if you use a DIFFERENT file in each transformer for the s/key generation.

Cheers,
Bob.

[soumya5891]

In place of @INROWNUM can you please try with @OUTROWNUM

[prasson_ibm]

Hi Bob,
I think sarrogate key generator/file will only ensure unique records,but it will not give sequence number,initially I tried with that but it did not work.

[ray.wurlod]

Is there a SEQUENCE that you can invoke in the INSERT statement?

[chulett]

It's hard to tell from your examples so far but does the "sequence" need to start from 1 each time? Meaning this part:

RejOra
1
2

When you run it a second time then can it look like this:

RejOra
3
4
5

Or should it start with 1 again?

[prasson_ibm]

Hi,
It should start from 1 in the second run also,not from the max value.

Ray , I am checking for the sequence,if its available,I think we can use it in insert statement in Oratgt and Orarej.

[chulett]

Yes, you can use a sequence automagically in the insert statement however the fact that you want it to always start at 1 rules that approach out. Which is why I asked.

[ray.wurlod]

... unless, of course, you reset the sequence in Before/SQL.