CODEWITHSUNDEEP
stored-proceduresdb2database-cursor
akr
akr

Reputation: 133

How to return Cursor as OUT parameter in DB2 for z/OS

I'm using DB2 for z/OS as my database. I have written one stored procedure in DB2 where it will return some result set. Currently I have declared one cursor and calling OPEN Cur at the end of the stored procedure. I,m calling my procedure from Java and I'm getting the result set using ResultSet resultSet = callableStatement.getResultSet();My SP is working for few hundred records. But getting failed when table contains millions of data:

Caused by: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-904, SQLSTATE=57011, SQLERRMC=00C90084;00000100;DB2-MANAGED SPACE WITHOUT SECONDARY ALLOCATION OR US, DRIVER=4.24.92

I want to know

  1. Is it possible to return Cursor as OUT parameter in my SP ?
  2. What is the difference between taking data using OPEN curs way and CURSOR as OUT parameter ?
  3. How to solve issue when data is huge ?
  4. Will CURSOR as OUT parameter solve the issue ?

EDITED (SP detail):

DYNAMIC RESULT SET 1
P1: BEGIN

    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
     
    select a.TABLE_A_ID as TABLE_A_ID,
           b.TABLE_B_ID as TABLE_B_ID
    from TABLE_A a 
    left join TABLE_C c on 
    a.TABLE_A_ID = c.TABLE_A_ID
    inner join TABLE_B b on
    b.CONTXT_ID = a.CONTXT_ID 
    AND b.CONTXT_POINT_ID = a.CONTXT_POINT_ID 
    AND b.CONTXT_ART_ID = a.CONTXT_ART_ID 
    where c.TABLE_A_ID is null ;

    OPEN cursor1;

Upvotes: 0

Views: 769

Answers (1)

mao
mao

Reputation: 12287

Refer to the documentation here for suggestions for handling this specific condition. Consider each suggestion.

Talk with your DBA for Z/OS and decide on the best course of action in your specific circumstances.

As we cannot see your stored-procedure source code, more than one option might exist, especially if the queries in the stored-procedures are unoptimised.

While usually it's easier to allocate more temporary space for the relevant tablespace(s) at the Db2-server end, that may simply temporarily mask the issue rather than fix it. But if the stored-procedure has poor design or unoptimised queries, then fix that first.

An SQL PL procedure can return a CURSOR as an output parameter, but that cursor is usable by the calling SQL PL code. It may not be usable by Java.

You ask "how to solve issue when data is huge", although you don't define in numbers the meaning of huge. It is a relative term. Code your SQL procedure properly, index every query in that procedure properly and verify the access plans carefully. Return the smallest possible number of rows and columns in the result-set.

Upvotes: 0

Related Questions