Look up and Relation stage in MVS

rupeshg · Post by **rupeshg** » Tue Dec 12, 2006 11:02 am

Hi All

I am using two lookup stages and their respective Relational stages from which they do the look up.

But in the cobol code generated , one of the sql is generated using cursor. and for the other look up stage its just a direct row fetch.

I am not able to find out how can i stop the cursor generation for the SQL query

Here are the two queries:
=======================
* DECLARE CURSOR FOR SCAN CLIENT_TAB2
*
*
EXEC SQL
DECLARE CNT_TAB2 CURSOR FOR
SELECT CNT_TAB_REF_C_1.CNT_ID
FROM (SELECT CNT_TA1.CNT_ID
FROM CNT_TAB AS CNT_TA1) CNT_TAB_REF_C_1
END-EXEC

*
==================================

The other look up
==================================
* ---------------------------------------------------------------
* DB2-CM-POL-SCHE1: GETROW
* ---------------------------------------------------------------
DB2-CM-POL-SCHE1-GETROW.

EXEC SQL
SELECT DB2_CLM_POL_SCHE_0.POLICY_NBR,
DB2_CLM_POL_SCHE_0.MTA_QTE_ID,
INTO
:HOSTVARS1.HV--POLICY-NBR-DS3750
:HOSTVARS1.HV--POLICY-NBR-DS3750-I,
:HOSTVARS1.HV-MTA-QTE-ID-DS3752
:HOSTVARS1.HV-MTA-QTE-ID-DS3752-I,
FROM (SELECT GLOBAL_CD_RE1.POLICY_NBR,
GLOBAL_CD_RE1.MT_QTE_ID,
FROM GLOBAL_CD_REF AS GLOBAL_CD_RE1
END-EXEC

If Some body can help.

Thanks in Advance

Rupesh

ray.wurlod · Post by **ray.wurlod** » Tue Dec 12, 2006 3:30 pm

The only place you have any customization control is by managing the JCL templates. As far as I am aware you can not influence the COBOL source that is generated. But I'd be happy to be corrected.

dls · Post by **dls** » Tue Dec 12, 2006 3:49 pm

A radio button on the General Tab of the Lookup Stage lets you specify the Lookup Type; i.e., Singleton or Cursor. Singleton is the default.

Is the Lookup Type the same in both of your stages?

ray.wurlod · Post by **ray.wurlod** » Tue Dec 12, 2006 5:42 pm

I missed the fact that you're doing a lookup.

rupeshg · Post by **rupeshg** » Wed Dec 13, 2006 3:41 am

Yes The radio button is singleton for both the stages.

But i am really surprised why the datastage generates the code differently for two similar kind of look ups.

yksjosh · Post by **yksjosh** » Wed Dec 13, 2006 7:04 am

Rupesh,

Just see if you are using any datastage function in the Lookup Stage where you give the look up condition.

It might be possible that, to perform that function it might be fetching the data into the CURSOR.

Yogesh

rupeshg · Post by **rupeshg** » Thu Dec 14, 2006 3:48 am

Yes it worked.
I was using Trim() function in my look up stage.
I removed it and no more CURSOR in the cobol code. :D

Thanks a lot for your help.
Rupesh[/img]