View previous topic :: View next topic
|
Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hello Guyz,
I am struck with this for some time now and i am wondering how can this be done using a query.
Please see my below scenario,
Code: |
SELECT COLLID,NAME,CONTOKEN, BINDTIME
FROM SYSIBM.SYSPACKAGE
ORDER BY COLLID,NAME,BINDTIME DESC; |
Gives You,
Code: |
COLLID NAME CONTOKEN BINDTIME
AAI1 BTCENKY .¢jà..°m 2008-02-10-14.37.05.676354
AAI1 KABG1SVR .jYß.3Õ0 2008-02-10-13.22.42.054753
AAI1 KABG1SVR .&±~.Æ ° 2008-02-10-13.22.42.017145
AAI1 KABG1SVR .\K,..LY 2008-02-10-13.22.41.961497
AAI1 KABG1SVR .hÿ..@ . 2008-02-10-13.22.41.943247
AAI1 KFMG1SVR .«÷º.I&% 2008-02-10-13.22.42.179674
AAI1 KFMG1SVR .« ö.!.y 2008-02-10-13.22.42.158599
AAI1 KFMG1SVR .ä.Þ.yT- 2008-02-10-13.22.42.124639
AAI1 KFMG1SVR .3¡..*ùÀ 2008-02-10-13.22.42.077827
AAI1 KM025SVR .ã8Q.Í'¯ 2008-02-10-14.37.05.721965
AAI1 KM027SVR .>Äë.¥ú{ 2008-02-10-14.37.05.770995
AAI1 KTCDBGET .>Ä*.£|. 2008-02-10-14.37.06.054400
AAI1 KTCDBGET .ñ:ô.§Ý 2008-02-10-14.37.06.004641
ASFB ASCLCF2P .¼ è.Z 2010-03-24-14.13.18.469808
ASFB ASCLCF2P ..'Ë.áô. 2008-02-10-13.22.54.321930
INEB NETMAMS1 .ݮQ.(˯ 2010-01-22-16.38.42.711078
INEB NETMAMS1 .¡³@..F 2010-01-05-11.02.26.628679
INEB NETMAMS1 .zÐy.ݳù 2009-12-28-03.54.05.122905
INEB NETMAMS1 .Ƽ....µ 2009-10-19-11.16.50.298305
INEB NETMAMS1 .ÆyK.§È« 2009-10-19-02.48.09.721788 |
I need get 2 most recently bound package versions(if available) in the collection based on bindtime,
like i need to get the below output
Code: |
COLLID NAME CONTOKEN BINDTIME
AAI1 BTCENKY .¢jà..°m 2008-02-10-14.37.05.676354
AAI1 KABG1SVR .jYß.3Õ0 2008-02-10-13.22.42.054753
AAI1 KABG1SVR .&±~.Æ ° 2008-02-10-13.22.42.017145
AAI1 KFMG1SVR .«÷º.I&% 2008-02-10-13.22.42.179674
AAI1 KFMG1SVR .« ö.!.y 2008-02-10-13.22.42.158599
AAI1 KM025SVR .ã8Q.Í'¯ 2008-02-10-14.37.05.721965
AAI1 KM027SVR .>Äë.¥ú{ 2008-02-10-14.37.05.770995
AAI1 KTCDBGET .>Ä*.£|. 2008-02-10-14.37.06.054400
AAI1 KTCDBGET .ñ:ô.§Ý 2008-02-10-14.37.06.004641
ASFB ASCLCF2P .¼ è.Z 2010-03-24-14.13.18.469808
ASFB ASCLCF2P ..'Ë.áô. 2008-02-10-13.22.54.321930
INEB NETMAMS1 .ݮQ.(˯ 2010-01-22-16.38.42.711078
INEB NETMAMS1 .¡³@..F 2010-01-05-11.02.26.628679 |
Can this be done using a query, how ?
Iam using DB2 V8.
Thank You in advance,
Sushanth |
|
Back to top |
|
|
bauer
New User
Joined: 03 Mar 2009 Posts: 28 Location: germany
|
|
|
|
Try this:
Code: |
SELECT COLLID , NAME , MAX(BINDTIME) AS BINDTIME
FROM SYSIBM.SYSPACKAGE
-- ONLY SOME COLLECTIONS
WHERE COLLID = ......
GROUP BY COLLID, NAME
UNION ALL
SELECT COLLID, NAME, MAX(BINDTIME) AS BINDTIME
FROM (
SELECT COLLID , NAME , BINDTIME
FROM SYSIBM.SYSPACKAGE T1
-- ONLY SOME COLLECTIONS
WHERE COLLID = .........
AND BINDTIME <
(
SELECT MAX(BINDTIME)
FROM SYSIBM.SYSPACKAGE
WHERE COLLID = T1.COLLID
AND NAME = T1.NAME
)
) T2
GROUP BY COLLID, NAME
ORDER BY COLLID ASC
, NAME ASC
, BINDTIME DESC
|
|
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
back on mainframe faster than I thought :
correct version (should add location because of indexes and a few '(' and correlations missing).
Code: |
select * from sysibm.syspackage A
where A.bindtime>=
coalesce((Select max(B.bindtime) from sysibm.syspackage B
where B.location=A.location
and B.collid=A.collid
and B.name=a.name
and b.bindtime < (select max(C.bindtime) from sysibm.syspackage C
where C.location=A.location
and C.collid=A.collid
and C.name=a.name)
)
,'0001-01-01-00.00.00') |
(Any mod can remove previous post to avoid confusion) - Done. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Thank You Very Much GuyC. |
|
Back to top |
|
|
|