DB2 CURSOR DECLARARTION in COBOL - > to be dynamic

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
dichandr
Member
Posts: 7
Joined: Sat Nov 12, 2011 11:46 pm

DB2 CURSOR DECLARARTION in COBOL - > to be dynamic

Post by dichandr » Thu Nov 24, 2011 5:52 pm

Hi Friends,

I need a clarification on my below query.

For example:
I have a program which generates a report on Employees of the organistion fetching the Employee number, Name & Age from table.

Now i have a requirement to have instance of jobs, 1 would generate a report for the employees who are working in Computers sections and other report coering employees in Production, Maintenance & Sales by using Dept field in table.

Currently SQL cursor is like

EXEC SQL
DECLARE EMP_CSR CURSOR FOR
SELECT EMP_NO
,EMP_NAME
,EMP_AGE
FROM EMP_TABLE
ORDER BY EMP_NO
END-EXEC.

Now i need this to be amended to have where condition like

WHERE EMP_DEPT in ('PROD','MAIN','SALE') for one report and for another it should be like
WHERE EMP_DEPT = 'COMP'

I want to have same program and two jobs and the where condition should be decided based on the value i pass through JCL PARM Parameter.

For example if i pass 'C' as my JCL PARM value then the job should generate a report only for employees in Computers dept. If if pass 'O' the value should generate report for employees from production, maintenanace & Sales...

Thanks,
Divakar

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Fri Nov 25, 2011 1:09 pm

Okay, so what have you tried?
Regards,
Anuj

dichandr
Member
Posts: 7
Joined: Sat Nov 12, 2011 11:46 pm

Post by dichandr » Fri Nov 25, 2011 3:23 pm

I tried to have two sql cursor declarations and have separate sections to open, fetch & close these cursors. And based on the JCL PARM parameter, correponding section will be called. This is working. But i want to check if there is any better solution.

Thanks...

devegouda.chage
Member
Posts: 32
Joined: Mon Sep 12, 2011 10:50 am
Location: Bangalore

Post by devegouda.chage » Tue Jan 10, 2012 5:31 pm

I think what you did is the best way (According to me)....

otherwise you need to have an extra column having the flag value either "C" or "O"
&by adding this into cursor u acn query ....example
:idea:
EXEC SQL
DECLARE EMP_CSR CURSOR FOR
SELECT EMP_NO
,EMP_NAME
,EMP_AGE
FROM EMP_TABLE
WHERE (EMP_DEPT in ('PROD','MAIN','SALE') AND FLAG_COL='O')
OR (EMP_DEPT = 'COMP' AND FLAG_COL='C')

ORDER BY EMP_NO
END-EXEC.

where FLAG_COL= flag value stored either "O" for'PROD','MAIN','SALE'
or "C" for Computers


corrections are most welcome :)
Thanks & Regards
Dev

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others