Get next value from DB Seq
Moderators: chulett, rschirm, roy
Are you getting the correct result, if you view the data in DB2 stage?
Are you getting some thing like:
And your input is:
.
The output you require is :
Is this your requirement?
Correct me if i am wrong.
Are you getting some thing like:
Code: Select all
1,1
1,2
1,3
1,4
Code: Select all
2
3
4
5
The output you require is :
Code: Select all
2,1
3,2
4,3
5,4
Correct me if i am wrong.
pandeeswaran
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
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.
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
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
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.
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.
Regards,
S. Kirtikumar.
S. Kirtikumar.