IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Cobol-db2 : SQL query is running for long time


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Pratik Thorat

New User


Joined: 14 Apr 2021
Posts: 1
Location: India

PostPosted: Wed Apr 14, 2021 11:29 pm
Reply with quote

Below SQL query in cobol-db2 report program is running for a long time. Requirement is to reduce run-time.
Code:

EXEC SQL
         SELECT B.column1
               INTO hostvar1
               FROM table1 A, table2 B
         WHERE A.column2 = B.column2
               AND A.column3 = B.column3
               AND A.column4 = :var1
               AND A.column5 = :var2
               AND A.column6 = :var3
          WITH UR
END-EXEC


As you can see it is picking column2 and column3 value from table1 based on other three conditions (column 4,5,6 values), matching it (column 2,3) with table2 and picking column1 value from table2.

Is there a better way to write this SQL? Or to do this task in general ?

It's a weekly job and It is running for on avg 9 hours, where this query is taking around 8.5 hours. Yes, there are huge no of records ( 5-6 million)
Coded for you
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1590
Location: Andromeda Galaxy

PostPosted: Wed Apr 14, 2021 11:55 pm
Reply with quote

Can you run a explain over this query and get us the output please?

That should be the starting point
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2639
Location: NYC,USA

PostPosted: Thu Apr 15, 2021 12:49 am
Reply with quote

Welcome!
Please use code tags, As suggested , run EXPLAIN or contact DBA so that they can look further into this and provide you suggestions. There may be index lacking, Runstats/Reorg issues or may be either of var1/2/3 is blank to cause one to many or many to many relationship in the joins.

This forum can not solve your performance issues but only gives you directions what and where to look for help.

You could split the query into two SELECTs, SELECT from TableA based on var1/2/3 and use those values to do SELECT from TableB to get B.column1. Try it and see if that works.
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1038

PostPosted: Thu Apr 15, 2021 2:22 am
Reply with quote

Rohit Umarjikar wrote:
may be either of var1/2/3 is blank to cause one to many or many to many relationship in the joins.

This may happen not only with blank values, but with any equal values, like many zeroes to many zeroes, or many ones to many ones. The real data values need to be analyzed to give any advise.

Any complex code MUST be tested step-by-step, starting from minimum operations on minimum amount of data, with MANDATORY verification of ALL intermediate results, and BEFORE seeking for help at forums, or even from the DBA.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2639
Location: NYC,USA

PostPosted: Thu Apr 15, 2021 4:50 am
Reply with quote

sergeyken wrote:
Rohit Umarjikar wrote:
may be either of var1/2/3 is blank to cause one to many or many to many relationship in the joins.

This may happen not only with blank values, but with any equal values, like many zeroes to many zeroes, or many ones to many ones. The real data values need to be analyzed to give any advise.

Any complex code MUST be tested step-by-step, starting from minimum operations on minimum amount of data, with MANDATORY verification of ALL intermediate results, and BEFORE seeking for help at forums, or even from the DBA.

It was a clue to look into this direction as well blank value is just one of those to create such problems and one can only know when analyzed as you said, recently we faced similar situation.

Second if it’s prod issue ( ran only today for long time) then it’s always best to approach for DBA’s expertise as it could be environmental issue or Db2 might have chosen bad access path due to rebind or any changes to code is deployed or different data in the table for this run than last run or something else. They can see more closer than any developer with the accesses and tools.

Let us wait for TS to act than we argue on limited information.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Read UTF-8 file in Cobol COBOL Programming 5
No new posts UUID4 - extracting time element COBOL Programming 1
No new posts Reading subsequent rows in a query. DB2 12
No new posts SYSIBM Tables Query DB2 8
No new posts COBOL/CICS Emulators CICS 4
Search our Forums:

Back to Top