Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Can we Update cursor with group by and union ?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vyasricha

New User


Joined: 17 Mar 2008
Posts: 27
Location: banaglore

PostPosted: Sat May 26, 2012 11:05 am    Post subject: Can we Update cursor with group by and union ?
Reply with quote

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.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sat May 26, 2012 4:17 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
vyasricha

New User


Joined: 17 Mar 2008
Posts: 27
Location: banaglore

PostPosted: Mon May 28, 2012 10:52 am    Post subject: Reply to: Can we Update cursor with group by and union ?
Reply with quote

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 ?
Back to top
View user's profile Send private message
Smita.t2

New User


Joined: 17 Apr 2012
Posts: 22
Location: Bangalore

PostPosted: Mon May 28, 2012 12:25 pm    Post subject:
Reply with quote

Please check if STATS is collected for tables/indexes. If not, u may need to run Runstats aganist table/indexes and rebind the program.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon May 28, 2012 1:26 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 29, 2012 12:05 am    Post subject:
Reply with quote

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 icon_confused.gif

How many tables are in this tablespace?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us