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
 

 

Fine Tuning SQL

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

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Thu Nov 12, 2009 2:10 pm    Post subject: Fine Tuning SQL
Reply with quote

Hi Experts,

I am having performance a performance issue in a query. Please can you give me your suggestions. This should be performed only with SQL and is a part of a main query.

The data in table is as follows:-

Table Name GWACTD

Code:
Act-No(Decimal 16)   Day-key(Int 4)   Time(Dec12)  Debit-amt(Dec18)
---------------------------------------------------------------------------------
1111                        4            123456          80000
1111                        5            111234          90000
3333                        1            999999          12242
3333                        2            343434          98779
3333                        2            122123          99923



Required result should be one record for every Act-No, with the latest transaction alone. i.e. record with max(day key) and if more than one transaction happens on the same day, the record with the highest time stamp is required.

Hence the result of the select should be as follows:-
Result:-

Code:
Act-No(Decimal 16)   Day-key(Int 4)   Time(Dec12)  Debit-amt(Dec18)
---------------------------------------------------------------------------------
1111                        5            111234          90000
3333                        2            343434          98779


Please can suggest me a simple query which provides the required result.


Regards,
Back to top
View user's profile Send private message

Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Thu Nov 12, 2009 2:44 pm    Post subject:
Reply with quote

Your query doesn't sound like a 'Fine Tuning SQL' query or an SQL performance Issue - it sounds like a 'How do I write the SQL to get this output' sort of question !

Have you tried anything yourself, if so please post the SQL you have tried.
Back to top
View user's profile Send private message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Thu Nov 12, 2009 7:52 pm    Post subject: Reply to: Fine Tuning SQL
Reply with quote

Hi Davies,
Please find the query


Code:
 
SELECT
 B.ACT-NO
,B.DAY-KEY
,B.DEBIT-AMT
,B.TIME
FROM(
        SELECT
        A.ACT-NO
       ,MAX(CONCAT(CAST(DIGITS(A.DAY-KEY) AS CHAR(10)),CAST(DIGITS(A.TIME) AS CHAR(12)))) AS DAY_KEY_COM
        FROM GWACTD
        GROUP BY
        A.ACT-NO)AS AA
INNER JOIN GWACTD B
ON B.ACT-NO        = AA.ACT-NO
AND B.DAY-KEY      = CAST(SUBSTR(AA.DAY_KEY_COM,1,10)  AS INT)
AND B.TIME         = CAST(SUBSTR(AA.DAY_KEY_COM,11,12) AS DECIMAL(12,0))


Please let me know if there is any other way simpler way.

Regards,
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Thu Nov 12, 2009 8:15 pm    Post subject:
Reply with quote

I don't know if it's SIMPLER ( or even if it's more EFFICIENT ), but I would probably code it thusly:
Code:
SELECT A.ACT-NO
      ,A.DAY-KEY
      ,A.DEBIT-AMT
      ,A.TIME
  FROM GWACTD A
 WHERE A.TIME =
      (SELECT MAX(B.TIME)
         FROM GWACTD B
        WHERE B.ACT-NO = A.ACT-NO
          AND B.DAY-KEY =
             (SELECT MAX(C.DAY-KEY)
                FROM GWACTD C
               WHERE C.ACT-NO = A.ACT-NO)))
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 Batch job tuning sgandhla Testing & Performance analysis 5 Fri Mar 24, 2017 9:41 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts Performance tuning of Online system bipinpeter All Other Mainframe Topics 2 Thu Nov 26, 2015 2:29 pm
No new posts Fine tuning for VSAM dataset Vasanthr JCL & VSAM 3 Sat Jan 17, 2015 2:24 am
No new posts IMS Pre-load Validation - Performance... yogi.47eie IMS DB/DC 4 Fri Oct 24, 2014 1:18 pm


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