DATASTAGE Upgrade from Server 7.5.2 to Parallel 8.1

sarjushah · Post by **sarjushah** » Wed Aug 24, 2011 10:28 am

Hi,

We have migrated server jobs which were in DS 7.5.2 to DS 8.1 parallel version. Both of these versions installed on same Linux server. Some of the DS server jobs with OCI stage has user defined SQL. These SQLs first column is the oracle sequence next val for surrogate key and the remainder columns are from transformer mapping. This job runs successfully in 7.5.2 ver but not in 8.1. It simply hangs out as it is running but nothing really happening. If I change the SQL to 'Generate SQL' in properties tab with adding DS sequence number for surrogate key column, it runs successfully. Would appreciate your thoughts/comments on this issue. Or may be wondering if anyone had gone through the same issue? Below is the sql..

INSERT INTO SAT.SDT027_USER_AKA
(USER_AKA_ID,
USER_ID,
ID_TYP_ID,
INTRNL_IDNTFYR_TXT,
CRTD_BY,
CRTD_DT,
LST_CHNGD_BY,
LST_CHNGD_DT,DLT_FLG)
VALUES
(SAT.SDQ027_USER_AKA_ID.NEXTVAL,
:1,
:2,
:3,
:4,
TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'),
:6,
TO_DATE(:7, 'YYYY-MM-DD HH24:MI:SS'),
:8)

chulett · Post by **chulett** » Wed Aug 24, 2011 10:49 am

So, DS 8.1 Enterprise Edition. These are still Server jobs I assume. Do you have eight columns defined in the target OCI stage? Does your executing user have the proper grants to the Sequence object? I imagine so since it used to work but seems like it should be asked.

sarjushah · Post by **sarjushah** » Wed Aug 24, 2011 11:17 am

Craig,
Thanks for your reply!
Yes, these are still server jobs in DS 8.1 Enterprise Edition. Yes, target OCI stage has 8 column mapped from XFM stage. Executing user has proper grant to the sequence object as well.

chulett · Post by **chulett** » Wed Aug 24, 2011 12:34 pm

I don't see a problem with what you posted. Is there any significant difference between what you are using and what the stage generates? Taking the NEXTVAL usage out of the picture, of course.

mac4rfree85 · Post by **mac4rfree85** » Tue Aug 30, 2011 12:36 am

The Transformer should not have mapping for the column in which you are populating the NEXTVAL value.
so, your transformer should have only mappings for 7 columns excluding the column in which you will be passing the NEXTVAL value.

chulett · Post by **chulett** » Tue Aug 30, 2011 6:44 am

If you look at the SQL posted, you can see they've done that - the 8 columns mentioned do not include the ID field, it is the 9th field. Hence my "don't see a problem" statement.