Can we Update cursor with group by and union ?

vyasricha · New User Joined: 17 Mar 2008 Posts: 27 Location: banaglore

Hi,

I am defining a cursor with group by and union. and i am updating the same table as per the data fetched from cursor.

when i am running the query it is taking 10 minutes to fetch the data , but while defining the cursor in cobol-db2 program it is taking an hour to open the cursor ,the table doesn't have much data in table it has only 3000 rows but still an hour.
Please let me know if i am making any mistakes in program.

dbzTHEdinosauer · Posted: Sat May 26, 2012 4:17 pm

oh, i am sure that you are not making any mistakes in your program,
but i image that your sql sucks.
does group by and / or union make a cursor read only?

a group by would consolidate rows from the base table and generate fewer row(s) for the result table. can not update the result table.

suggest you go back and read the discussions in the application programmers guide
about what makes a read only cursor and an update cursor.

if there is more than 1 table mentioned in the cursor definition,
it is a read only.

i do not think that you can use a update current cursor on a cursor that you have described,
but then i only have your word for it.

the hour that you speak of to open the cursor,
is your time, not machine time.

vyasricha · New User Joined: 17 Mar 2008 Posts: 27 Location: banaglore

so i can not update the same table if i am defining the cursor with group by, sum and joins.

SELECT xx_CLIENT_ID
FROM AA.DXX_CLIENT
WHERE XX_MTH = :Processing Month
AND XX_YEAR_MTH = :Processing Year / Month
AND XX_CLIENT_ID <> ' '
AND XX_CLASS = ‘ABCD’
AND XX_RT_LVL_PRD_CAT IN ('ABCD’, 'EFGH’, 'IJKL’)
GROUP BY XX_CLIENT_ID
HAVING SUM(XX__AMT) > 125000

If data is there i am updating the same table .

The job is taking long time to run , is there is some problem in cursor definition ?

Smita.t2 · New User Joined: 17 Apr 2012 Posts: 31 Location: Bangalore

Please check if STATS is collected for tables/indexes. If not, u may need to run Runstats aganist table/indexes and rebind the program.

chandan.inst · Posted: Mon May 28, 2012 1:26 pm

You can use EXPLAIN to check which indexes this query is using? May be its doing tablespace scan on one of the table

Thanks and regards,
Chandan

dick scherrer · Posted: Tue May 29, 2012 12:05 am

Hello,

I suspect there are "things missing" from the post. . .

Taking an hour to process 3000 rows is surely questionable.

The posted code is a select from only 1 table even though the initial dialog mentions union. It is not in the posted sql

How many tables are in this tablespace?