Get next value from DB Seq

[kaps]

As I stated before I ran with Sequential mode setting in the Lookup stage which only got one value from the lookup for all records.

[pandeesh]

Are you getting the correct result, if you view the data in DB2 stage?
Are you getting some thing like:

Code: Select all

1,1
1,2
1,3
1,4

And your input is:

Code: Select all

2
3
4
5

.

The output you require is :

Code: Select all

2,1
3,2
4,3
5,4

Is this your requirement?
Correct me if i am wrong.

[Kirtikumar]

There is only one thing that I can think might be getting wrong. The lookup is not sparse. Can you post OSH code?

[kaps]

Pandeeswaran - I can't view the data.View data is disabled and I believe the reason is it's sparse lookup. Your understanding about the reqquirement is correct.

Kirtikumar - Following is the OSH code.

Parallel job initiated
# OSH / orchestrate script for Job TestSeq1 compiled at 18:11:25 21 DEC 2011
#################################################################
#### STAGE: Sequential_File_0
## Operator
import
## Operator options
-schema record
{final_delim=end, delim=',', quote=double}
(
ID1:int32;
ID2:int32;
)
-file '/datastage/dev/edwdev/tk_seq1_in.txt'
-rejects continue
-reportProgress yes

## General options
[ident('Sequential_File_0'); jobmon_ident('Sequential_File_0')]
## Outputs
0> [] 'Sequential_File_0:DSLink2.v'
;

#################################################################
#### STAGE: Lookup_13
## Operator
db2lookup
## Operator options
-query 'SELECT 21 as ID1, NEXTVAL FOR EDW.JOURNAL_ENTRY_SEQ as NEXTVALUE FROM sysibm.sysdummy1 WHERE 21=ORCHESTRATE.ID1

'
-dbname '[&_prm_db_name]'
-client_instance '[&_prm_client_inst_name]'
-user '[&_prm_edw_id]'
-password [&_prm_edw_pwd]
-server '[&_prm_server_name]'
-ifNotFound fail

## General options
[ident('Lookup_13'); jobmon_ident('Lookup_13')]
## Inputs
0< [transfer(inputData renames ID1 as id1, ID2 as id2)] 'Sequential_File_0:DSLink2.v'
## Outputs
0> [modify (
NEXTVALUE:not_nullable int64=NEXTVALUE;
keep
id1,id2,NEXTVALUE;)] 'Lookup_13:DSLink15.v'
;

#################################################################
#### STAGE: Sequential_File_16
## Operator
export
## Operator options
-schema record
{final_delim=end, delim=',', quote=double}
(
id1:int32;
id2:int32;
NEXTVALUE:int64;
)
-file '/datastage/dev/edwdev/tk_seq1_out.txt'
-overwrite
-rejects continue

## General options
[ident('Sequential_File_16'); jobmon_ident('Sequential_File_16')]
## Inputs
0< [] 'Lookup_13:DSLink15.v'
;


# End of OSH code

[Kirtikumar]

From the OSH it looks fine to me. Would you be able to ask someone to monitor DB for what SQL is getting fired when the jobs runs and how many of them hit the DB?

Also I have not used it but why not to use the SurrogateKey generator with the DB Sequence option. I do not know how it works, but I saw the stage has a provision.

[kaps]

Yes. There are other ways to accomplish this but I was rying to understand why the job is not working in Parallel.

Thanks for all the inputs.