DSXchange

Posted: **Mon Jun 20, 2011 7:49 am**

Hi,

I have requirement where I need to extract the multiple records which are stored in DB2 SP CUSRSOR.

Attached the part of SP. Could someone help how to handle CURSOR in Datastage (STP stage)

Code: Select all

DECLARE P_CURSOR CURSOR WITH RETURN FOR 
  
SELECT 
  WA4.KEY_DATE, 
  WA4.KEY_TIME, 
  WA4.KEY_MILSEC, 
  WA4.RECORDCD, 
  WA4.CRNODE, 
  WA4.UNITCD, 
  WA4.WRKTYPE, 
  WA4.STATCD, 
  WA4.QUEUECD, 
  NBL.LOB_FSNM_001, 
  NBL.LOB_LSNM_001, 
  NBL.LOB_DOFB_001, 
  NBL.LOB_IRSN_001
FROM 
  WA4U999S WA4 INNER JOIN NBLFWKLB NBL ON WA4.KEY_DATE = NBL.KEY_DATE AND 
  WA4.KEY_TIME = NBL.KEY_TIME AND 
  WA4.KEY_MILSEC = NBL.KEY_MILSEC AND 
  WA4.RECORDCD = NBL.RECORDCD AND 
  WA4.CRNODE = NBL.CRNODE
WHERE 
  WA4.UNITCD = 'NBALIFE' AND 
  WA4.QUEUECD = 'N2ORDER2' AND
  WA4.KEY_DATE < DATE(CURRENT_DATE - 5 DAYS)
  FOR READ ONLY WITH UR
	 
OPEN P_CURSOR ; 
  
END P1  ;

Posted: **Mon Jun 20, 2011 5:56 pm**

The Stored Procedure stage calls stored procedures, not cursors.

You would need to create a stored procedure that DataStage can call (that is, one that handles a result set) and possibly call this from a database stage rather than a Stored Procedure stage. Or you can simply use the SELECT statement in your cursor - DataStage will worry about progressing through the rows.

Posted: **Mon Jun 20, 2011 6:06 pm**

Exactly... why not dump the proc and use that SQL 'like normal' in your DataStage job?

DSXchange

CURSOR used in DB2 stored procedure

CURSOR used in DB2 stored procedure