Using database sequence

mydsworld · Post by **mydsworld** » Wed May 07, 2008 3:27 pm

I have a DB2 sequence created in database.I want that to be used in my target DB2 API stage (like NEXTVAL FOR MYSCHEMA.X_SEQ).Can I do that?

ray.wurlod · Post by **ray.wurlod** » Wed May 07, 2008 4:12 pm

Yes. But you will need user-defined SQL, and you will need NOT to pass the key column value from DataStage.

mydsworld · Post by **mydsworld** » Wed May 07, 2008 4:22 pm

By 'User defined SQL', do you mean Custom SQL, then thats what I am trying.

Seq File -> Trnsfrm -> DB2 API (table TEST1)

TEST1 table contains columns id,name
I am generatng id through DB2 sequence and need to bring name from input.

Will my custom SQL inside DB2 API look like this

insert into MYSCHEMA.TEST1 values(NEXTVAL FOR MYSCHEMMA.X_SEQ,?)

But then again 1st column is showing unmapped in Transformer.

narasimha · Post by **narasimha** » Wed May 07, 2008 4:50 pm

Delete the column id and use the below query.

Code: Select all

INSERT INTO MYSCHEMA.TEST1 (id,name) VALUES (NEXTVAL FOR MYSCHEMMA.X_SEQ,:1)

ray.wurlod · Post by **ray.wurlod** » Wed May 07, 2008 7:27 pm

The Transformer stage output link must have only one column (name). It must not output the id column. That way, name will align with the :1 parameter marker (the first parameter).

mydsworld · Post by **mydsworld** » Wed May 07, 2008 9:02 pm

Ray & Narasimha,

I did the way you mentioned.Transformer output only name (and not id).Within DB2 API, I am generating the id using the sequence

INSERT INTO MYSCHEMA.TEST1 (id,name) VALUES (NEXTVAL FOR MYSCHEMMA.X_SEQ,:1)

But since Column definition in DB2 API contains only 'name', so writing the above SQL gives mismatch of metadata.

DB2_UDB_API_2,0: Fatal Error: Fatal: This SQL statement did not contain the same number of parameters as columns on this link. This usually happens with user-defined SQL.

chulett · Post by **chulett** » Wed May 07, 2008 10:24 pm

Sounds like you still have both columns in the output link.

mydsworld · Post by **mydsworld** » Thu May 08, 2008 5:49 am

No, I have just one column name defined in the output link. Still I am getting the same (metadata mismatch) error. Because in SQL I am retrieving 2 columns whereas in column definition I have only 1 column.

chulett · Post by **chulett** » Thu May 08, 2008 7:23 am

Then perhaps that's a PX restriction, what you've done would work just fine in Server. It matches the columns in the link to the parameter markers in your sql, it doesn't give a hoot how many column names you mention.

keshav0307 · Post by **keshav0307** » Thu May 08, 2008 8:56 am

never used DB2, but for this situation with oracle stage, i have written a before insert trigger on the target table. in the mapping i default the input to 1,

chulett · Post by **chulett** » Thu May 08, 2008 9:04 am

I don't have DB2 here but don't recall it using numbered parameter markers, I thought that was strictly an Oracle thing. Are you sure that :1 shouldn't be a ? instead?