1 IDUGDB2-L.ORG /home/listserv/home/db2-l April 2010, week 4
2 194 81_Re: Index-controlled to Table-controlled partitioning - Performance benefits poll16_Robert Catterall21_rfcatterall@GMAIL.COM31_Thu, 22 Apr 2010 01:00:58 -0400503_ISO-8859-1 Sorry about the late response.
A potential performance benefit associated with table-controlled
partitioning is the capability it gives you to implement something akin to
multi-dimensional clustering on the mainframe DB2 platform (albeit with only
two dimensions). You do this by partitioning based on one key and clustering
within partitions according to another key (with index-controlled
partitioning, the clustering and partitioning keys are one and the same). [...]62_o2jde7260f31004212200j407ec5e0le5d40de574adc761@mail.gmail.com
197 329 43_Re: z/OS DEFINE NO with TEMPLATE and UNLOAD11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 22 Apr 2010 07:36:50 +0200850_ISO-8859-1 nope.No cond codes set
Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
http://www.seg.de
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert
Joe Geller
Gesendet von: IDUG DB2-L
21.04.2010 16:33
Bitte antworten an
IDUG DB2-L
An
DB2-L@IDUGDB2-L.ORG
Kopie
Thema
Re: [DB2-L] z/OS DEFINE NO with TEMPLATE and UNLOAD
Roy,
Does the Unload give back a different condition code in this situation
(i.e. 4 vs 0)? If it does, then you can add the cond code to the Load
step and skip [...]64_OF035016CE.8D7E729C-ONC125770D.001ED218-C125770D.001ED656@seg.de
527 300 25_Re: access path selection11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 22 Apr 2010 08:04:31 +0200659_ISO-8859-1 and TABLE ALL INDEX ALL is nowher near the RUNSTATS that you need these
days!
RUNSTATS TABLESPACE DSNDB04.ROYTEST TABLE(ALL)
INDEX(ALL) KEYCARD
FREQVAL NUMCOLS 0001 COUNT 10 MOST
FREQVAL NUMCOLS 0002 COUNT 10 MOST
FREQVAL NUMCOLS 0003 COUNT 10 MOST
FREQVAL NUMCOLS 0004 COUNT 10 MOST
FREQVAL NUMCOLS 0005 COUNT 10 MOST
FREQVAL NUMCOLS 0006 COUNT 10 MOST
FREQVAL NUMCOLS 0007 COUNT 10 MOST
FREQVAL NUMCOLS 0008 COUNT 10 MOST
FREQVAL NUMCOLS 0009 COUNT 10 MOST
FREQVAL NUMCOLS 0010 COUNT 10 MOST
HISTOGRAM NUMCOLS 0010
SHRLEVEL CHANGE
UPDATE ALL REPORT NO
SORTDEVT SYSALLDA
SORTNUM 0004 [...]64_OFEF991446.226C6B18-ONC125770D.001F109E-C125770D.00215F58@seg.de
828 38 31_Re: Question about single quote14_Peter Vanroose17_pvanroose@ABIS.BE31_Thu, 22 Apr 2010 03:27:18 -0400999_UTF-8 Here's what I would do for your problem at hand:
(would not work when the quotes are not followed by an "s" and then a single quote)
create table quote_test(x varchar);
insert into quote_test values ('Teacher''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''s''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''');
SELECT substr(x, 1, posstr(x,'''')-1) || substr(x, posstr(x,'''s''')+1)
FROM quote_test
-- Peter Vanroose,
ABIS Training & Consulting.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no [...]49_8006479125856490.WA.pvanrooseabis.be@www.idug.org
867 72 37_z/OS DB2 9: SQL for Image Copy status13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Thu, 22 Apr 2010 04:04:26 -0400810_UTF-8 I use the following SQL statement to search for tablespaces with no Full Image Copies or Full Image Copies older than 7 days.
It works almost perfect but the SQL statement returns "unneccessary" rows, can anyone re-write the statement to exclude SYSCOPY and the work database?
Thanks in advance.
Olle
SELECT SUBSTR(H.DBNAME,1,8) AS DBNAME,
SUBSTR(H.TSNAME,1,8) AS TSNAME, H.PARTITION,
VALUE(CHAR(D.TS),'*NO COPY*')
AS LASTCOPY
FROM SYSIBM.SYSTABLEPART H
LEFT OUTER JOIN
(
SELECT A.DBNAME,A.TSNAME,A.PARTITION,MAX(C.ITIMESTAMP) AS TS
FROM SYSIBM.SYSTABLEPART A
INNER JOIN
(SELECT DBNAME,TSNAME,DSNUM,MAX(TIMESTAMP) AS ITIMESTAMP
FROM SYSIBM.SYSCOPY WHERE ICTYPE = 'F'
GROUP BY DBNAME,TSNAME,DSNUM
) C
ON A.DBNAME = C.DBNAME
AND A.TSNAME = [...]57_6618467198908553.WA.olle.brostromswedbank.se@www.idug.org
940 331 41_Re: z/OS DB2 9: SQL for Image Copy status11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 22 Apr 2010 11:21:04 +0200469_ISO-8859-1 just join table H to SYSDATABASE nI where H.DBNAME = I.DBNAME AND I.TYPE
= ' ' to ignore WORK and TEMP DB's and simply code AND NOT (H.DBNAME =
'DSNDB06' AND H.TSNAME = 'SYSCOPY') to the query then all should be well
Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
http://www.seg.de [...]64_OFE2572FF8.D03BB343-ONC125770D.003332AA-C125770D.00335DEE@seg.de
1272 213 39_AW: [DB2-L] Question about single quote35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 22 Apr 2010 11:48:00 +0200797_iso-8859-1 Hi
O.k. here is a query, I saw sometime somewhere (don't know, if it was in the list), which eliminates redundant blanks. You can adjust it accordingly:
SELECT REPLACE(
REPLACE(
REPLACE('Walter Janissen', SPACE(1), '<>'),
'><', SPACE(0)),
'<>', SPACE(1))
FROM SYSIBM.SYSDUMMY1
Mit freundlichen Grüßen
Walter Janißen
ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:walter.janissen@itergo.com
Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996 [...]43_DB2-L%201004220548134264.052E@IDUGDB2-L.ORG
1486 59 26_zparm xlkupdlt for target.10_Zhang Ming16_db2sub@YAHOO.COM31_Thu, 22 Apr 2010 02:51:42 -0700342_us-ascii we have many batch jobs running on 4-way db2 v8 with huge seq update and delete.
Currently,xlkupdlt is No.
Can I change it to target at night to do batch job and then
chang back after batch is over and will get some benifit from it?
tks for any experience sharing for xlkupdlt usages and any side effect?
Thanks. [...]44_426859.38574.qm@web113719.mail.gq1.yahoo.com
1546 229 38_AW: [DB2-L] zparm xlkupdlt for target.12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 22 Apr 2010 12:33:02 +0200868_iso-8859-1 Please take a look at
http://www.dbmag.intelligententerprise.com/story/showArticle.jhtml?articleID=209900061&pgno=3
And there search for the chapter called "Another Nuance"
HTH.
With kind regards - mit freundlichen Gruessen,
G e o r g H . P e t e r
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Development and Product Support (E3)
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail G.Peter@dzbw.de
Phone 0049-711-8108-27271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------
P Think before you print. [...]58_99E58D5AC1BE6D4CBF492AF72B942C3D061ADC08@dzstus010.dzbw.de
1776 51 32_deadlock time and deadlock cycle12_Jose Antonio17_jamorcillo@CAM.ES31_Thu, 22 Apr 2010 13:53:04 +0200734_iso-8859-1 Hello!
I was reading "Data Sharing: Planning and Administration" and Chapter 6 talks about Deadlock detection and resource timeouts.
There isn't any recommendation about DEADLOCK TIME and DEADLOCK CYCLE, they only talk about the defaults (5 seconds and 1 cycle).
Any experience with different values?
Thanks a lot!!!!!!!!!!!!!!!!!!!!!!!!
José Antonio Morcillo
Caja Mediterráneo
Alicante (Spain)
Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en [...]57_78E5EB2B7326D240BBB670236211F9D3D5E386@SRVMAIL3.redcam.es
1828 43 38_can't access www.db2expert.com website12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Thu, 22 Apr 2010 14:36:52 +0100525_ISO-8859-1 Is there a problem with the above as I can't seem to get access to it.
Jim McAlpine
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]62_z2y21d1f8c21004220636q470bbc9an785cf388e49cf265@mail.gmail.com
1872 198 42_Re: can't access www.db2expert.com website12_McLaren Phil32_Phil.McLaren@AXAWINTERTHUR.CO.UK31_Thu, 22 Apr 2010 14:47:03 +0100565_us-ascii Same here. I think it's having a bad moment or two.....
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jim McAlpine
Sent: 22 April 2010 14:37
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] can't access www.db2expert.com website
Is there a problem with the above as I can't seem to get access to it.
Jim McAlpine
________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. [...]72_1454A0984F246445950CB759E249F50F4660291382@csis564.wlp.uk.winterthur.com
2071 166 42_Re: can't access www.db2expert.com website35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 22 Apr 2010 09:56:57 -0400458_iso-8859-1 Does not seem to be working, but the isp address appears valid, since my browser doesn't get to an error message, even after 30 seconds when I killed it.
Regards,
Joel
Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com [...]46_17EE476D5D7D46CD8668FF33859D9F46@DellNotebook3
2238 571 49_SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Thu, 22 Apr 2010 15:58:19 +0200467_iso-8859-1 Hi everyone,
Writing SQL statements is not where my best DB2 knowledge are.
I tried to understand what Roy recommend me to do but I fail.
Can anyone possibly construct the complete query including Roy's update?
Thank's to all of you!
Olle
Från: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] För Roy Boxwell
Skickat: den 22 april 2010 11:21
Till: DB2-L@IDUGDB2-L.ORG
Ämne: Re: [DB2-L] z/OS DB2 9: SQL for Image Copy status [...]70_B0000573F0F67C438DC58043C06F4CAB49D3A99801@FSPAS01EV011.fspa.myntet.se
2810 64 42_Re: can't access www.db2expert.com website12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Thu, 22 Apr 2010 14:59:02 +0100667_windows-1252 On Thu, Apr 22, 2010 at 2:47 PM, McLaren Phil <
Phil.McLaren@axawinterthur.co.uk> wrote:
> Same here. I think it’s having a bad moment or two…..
>
>
>
Damn !. Does anyone have a copy of the INSTJAR REXX program from that
website that they could email me.
Jim McAlpine
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
[...]62_g2w21d1f8c21004220659mcba499b2waa65b1222c71ca7d@mail.gmail.com
2875 76 42_Re: can't access www.db2expert.com website11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 22 Apr 2010 16:02:55 +0200817_ISO-8859-1 nope dead for me as well...
Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
http://www.seg.de
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]64_OFBD802117.83F368C4-ONC125770D.004D28D2-C125770D.004D2C1A@seg.de
2952 159 42_Re: can't access www.db2expert.com website10_Roger Hecq18_Roger.Hecq@UBS.COM31_Thu, 22 Apr 2010 10:02:50 -0400394_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. [...]62_1A700EEF49343148A08879B1E3CA5BA61494D721@NSTMC101PEX1.ubsw.net
3112 249 53_Re: SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 22 Apr 2010 16:24:17 +0200807_ISO-8859-1 as I have soooooo much free time.... ;)
SELECT SUBSTR(H.DBNAME, 1 , 8) AS DBNAME,
SUBSTR(H.TSNAME, 1 , 8) AS TSNAME, H.PARTITION,
VALUE(CHAR(D.TS),'*NO COPY*')
AS LASTCOPY
FROM SYSIBM.SYSDATABASE I
INNER JOIN
SYSIBM.SYSTABLEPART H
ON I.NAME = H.DBNAME
LEFT OUTER JOIN
(
SELECT A.DBNAME,A.TSNAME,A.PARTITION,MAX(C.ITIMESTAMP) AS TS
FROM SYSIBM.SYSTABLEPART A
INNER JOIN
(SELECT DBNAME,TSNAME,DSNUM,MAX(TIMESTAMP) AS ITIMESTAMP
FROM SYSIBM.SYSCOPY WHERE ICTYPE = 'F'
GROUP BY DBNAME,TSNAME,DSNUM
) C
ON A.DBNAME = C.DBNAME
AND A.TSNAME = C.TSNAME
AND (A.PARTITION = C.DSNUM
OR (A.PARTITION <> 0 AND C.DSNUM = 0)
)
GROUP BY A.DBNAME,A.TSNAME,A.PARTITION
) D
ON H.DBNAME = D.DBNAME
AND H.TSNAME = D.TSNAME
AND [...]64_OF0CAB8784.B6F4CD65-ONC125770D.004E7923-C125770D.004F211D@seg.de
3362 623 53_Re: SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 22 Apr 2010 10:46:26 -0400692_iso-8859-1 Roy et al., this seems like a lot of code to produce just a list of who isn't getting regular IC's. Why not run something much shorter:
SELECT DBNAME, NAME, PARTITION
FROM SYSIBM.SYSTABLEPART TP
INNER JOIN SYSIBM.SYSDATABASE DB
ON TP.DBNAME = DB.NAME
WHERE NOT EXISTS
(SELECT 1 FROM SYSIBM.SYSCOPY CP
WHERE CP.ICTYPE='F'
AND DATE(TIMESTAMP) > CURRENT DATE - 7 DAYS
AND TP.TSNAME = CP.TSNAME
AND TP.DBNAME = CP.DBNAME
AND (TP.PARTITION = CP.DSNUM
OR TP.PARTITION > 0 AND CP.DSNUM = 0
)
)
AND DB.TYPE = ''
AND NOT (TP.DBNAME='DSNDB06' AND TP.TSNAME='SYSCOPY')
;
...which produces only the names of tablespace partitions [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46864@MAIL02.fisalan.nycnet
3986 616 61_AW: [DB2-L] SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 22 Apr 2010 16:46:54 +0200806_iso-8859-1 Hi Roy
Maybe you want to add:
SELECT SUBSTR(H.DBNAME, 1 , 8) AS DBNAME,
SUBSTR(H.TSNAME, 1 , 8) AS TSNAME, H.PARTITION,
VALUE(CHAR(D.TS),'*NO COPY*'), S.CREATEDTS
AS LASTCOPY
FROM SYSIBM.SYSDATABASE I
INNER JOIN
SYSIBM.SYSTABLEPART H
ON I.NAME = H.DBNAME
INNER JOIN SYSIBM.SYSTABLESPACE S
ON I.NAME = S.DBNAME
AND H.TSNAME = S.NAME
LEFT OUTER JOIN
(
SELECT A.DBNAME,A.TSNAME,A.PARTITION,MAX(C.ITIMESTAMP) AS TS
FROM SYSIBM.SYSTABLEPART A
INNER JOIN
(SELECT DBNAME,TSNAME,DSNUM,MAX(TIMESTAMP) AS ITIMESTAMP
FROM SYSIBM.SYSCOPY WHERE ICTYPE = 'F'
GROUP BY DBNAME,TSNAME,DSNUM
) C
ON A.DBNAME = C.DBNAME
AND A.TSNAME = C.TSNAME
AND (A.PARTITION = C.DSNUM
OR (A.PARTITION <> 0 AND C.DSNUM = 0)
)
GROUP BY [...]43_DB2-L%201004221047065045.058B@IDUGDB2-L.ORG
4603 716 53_Re: SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 22 Apr 2010 10:48:40 -0400494_iso-8859-1 My mistake, the second attribute selected should be TP.TSNAME, not "NAME"
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil
Sent: Thursday, April 22, 2010 10:46 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status
Roy et al., this seems like a lot of code to produce just a list of who isn't getting regular IC's. Why not run something much shorter: [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46865@MAIL02.fisalan.nycnet
5320 202 42_Re: can't access www.db2expert.com website17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Thu, 22 Apr 2010 15:50:29 +0100459_iso-8859-1 are you sure it's not stuck somewhere on top of a volcano?
Sorry, I know it's not Friday yet but I've been marooned for a week due to the blasting volcano and feel grumpy about it :-S
Thanks.
Aurora
Aurora Emanuela Dell'Anno
CA MSC
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
Aurora.Dellanno@ca.com
http://www.ca.com/ [...]56_3D02E8610514C04F991CF832BA154C6610567339@UKSLMS11.ca.com
5523 93 42_Re: can't access www.db2expert.com website13_Dan Luksetich18_danl@DB2EXPERT.COM31_Thu, 22 Apr 2010 15:02:01 +0000439_Windows-1252 I'm away from my computer right now but can get to in on my cell phone and one contact in the USA says access is ok. Might be a routing issue. None the less I will fix it ASAP!
Dan
Sent via BlackBerry from T-Mobile
-----Original Message-----
From: Roy Boxwell
Date: Thu, 22 Apr 2010 16:02:55
To:
Subject: [SPAM] Re: can't access www.db2expert.com website [...]105_1180658608-1271948542-cardhu_decombobulator_blackberry.rim.net-1569611256-@bda205.bisx.prod.on.blackberry
5617 980 61_FW: [DB2-L] SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 22 Apr 2010 11:20:11 -0400769_iso-8859-1 Hej Olle,
Roy did write the query as far as I can tell. I added one more predicate to what Roy did, and it looks great (see below). You just need to add the remaining temp ts's at the very end:
SELECT SUBSTR(H.DBNAME,1,8) AS DBNAME,
SUBSTR(H.TSNAME,1,8) AS TSNAME, H.PARTITION,
VALUE(CHAR(D.TS),'*NO COPY*')
AS LASTCOPY
FROM SYSIBM.SYSTABLEPART H
LEFT OUTER JOIN
(
SELECT A.DBNAME,A.TSNAME,A.PARTITION,MAX(C.ITIMESTAMP) AS TS
FROM SYSIBM.SYSTABLEPART A
INNER JOIN
(SELECT DBNAME,TSNAME,DSNUM,MAX(TIMESTAMP) AS ITIMESTAMP
FROM SYSIBM.SYSCOPY WHERE ICTYPE = 'F'
GROUP BY DBNAME,TSNAME,DSNUM
) C
ON A.DBNAME = C.DBNAME
AND A.TSNAME = C.TSNAME
AND (A.PARTITION = C.DSNUM
OR (A.PARTITION <> 0 [...]56_0A18F096E689AC43BB2F52DAE5674D6008359E62@USILMS14.ca.com
6598 461 42_Re: can't access www.db2expert.com website14_Leblanc, Fritz27_fritz.leblanc@SALLIEMAE.COM31_Thu, 22 Apr 2010 11:26:50 -0400465_us-ascii Oh no! Unresponsive systems.... :)
Is it Friday yet?
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Joel Goldstein - Responsive Systems
Sent: Thursday, April 22, 2010 9:57 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] can't access www.db2expert.com website
Does not seem to be working, but the isp address appears valid, since my browser doesn't get to an error message, even after 30 seconds when I killed it. [...]70_8C61EFDB98B80C4AAEB19EA48ACEF2C179F4D88E6F@VFIEXMBX01.us.ad.usa-ed.net
7060 75 42_Re: can't access www.db2expert.com website10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 22 Apr 2010 17:37:01 +0200872_US-ASCII Tested but I cannot access it as well....
Max Scarpa
*********************************************************************************
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org
I D U G E U 2 0 1 0
EPISODE X
The return of performance
***********************************************************************
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]66_OF92387701.B96C648B-ONC125770D.005300C5-C125770D.0055C882@cesve.it
7136 103 53_Re: [SPAM] Re: can't access www.db2expert.com website13_Dan Luksetich18_danl@DB2EXPERT.COM31_Thu, 22 Apr 2010 15:42:10 +0000627_Windows-1252 Damn I'm at breakfast with my mom if I switch to 24X7 support my mom is upset
Sent via BlackBerry from T-Mobile
-----Original Message-----
From: Max Scarpa
Date: Thu, 22 Apr 2010 17:37:01
To:
Subject: [SPAM] Re: can't access www.db2expert.com website
Tested but I cannot access it as well....
Max Scarpa
*********************************************************************************
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org [...]104_847700368-1271950951-cardhu_decombobulator_blackberry.rim.net-1639768817-@bda205.bisx.prod.on.blackberry
7240 81 46_Re: Re: can't access www.db2expert.com website10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 22 Apr 2010 17:51:08 +0200819_US-ASCII No please ! I think you don't want to make your mom angry, she could beat
you......:-))))))
Max Scarpa
*********************************************************************************
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org
I D U G E U 2 0 1 0
EPISODE 10
The return of performance
***********************************************************************
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM [...]66_OFB1B114B8.806D44C0-ONC125770D.005686C2-C125770D.00571395@cesve.it
7322 42 36_Question about ONLINE REORG on Index13_Sekhar Mekala23_SEKHAR.MEKALA@GMAIL.COM31_Thu, 22 Apr 2010 11:55:26 -0400480_UTF-8 Hi all,
I am planning to run Online REORG on an Index in our Production environment. As per the syntax, I do not have to supply any Mapping table (as we do while perfroming ONLINE REORG on a table space). My question is, if we do NOT supply a mapping table for REORG INDEX SHRLEVEL CHANGE (I mean Online REORG at the Index level), how does it apply the Log onto shadow dataset? Does online REORG INDEX provides the same availability as online REORG TABLESPACE does? [...]55_7165901233961580.WA.SEKHAR.MEKALAGMAIL.COM@www.idug.org
7365 31 29_INDEXONLY = 'Y' in PLAN_TABLE14_Vidya Attuluri27_vidya.attuluri@MARRIOTT.COM31_Thu, 22 Apr 2010 12:34:17 -0400333_UTF-8 Hi,
we are on DB2 V8.1 for z/OS 1.10.
For one of the query against a Partitioned Tablespace (Index Controlled), the Explain output shows ACCESSTYPE = I and INDEXONLY = Y. We had the table space in STOP status. When the query is run, we are getting -904 against TS. why is it accessing TS when INDEXONLY = Y? [...]59_9107985139072779.WA.vidya.attulurimarriott.com@www.idug.org
7397 128 42_Re: can't access www.db2expert.com website12_Asamoto, Roy25_Roy.Asamoto@STATEAUTO.COM31_Thu, 22 Apr 2010 12:37:45 -0400687_iso-8859-1 I am able to get to the page.
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: Thursday, April 22, 2010 10:03 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] can't access www.db2expert.com website
nope dead for me as well...
Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
http://www.seg.de
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]68_0915161C01220E479F8F96BA3A27C7410116D277@SACOLEX8.corp.stateauto.com
7526 53 33_Re: INDEXONLY = 'Y' in PLAN_TABLE14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 22 Apr 2010 13:11:43 -0400630_us-ascii Try running with UR and see if that makes a difference.
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Vidya Attuluri
Sent: Thursday, April 22, 2010 12:34 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE
Hi,
we are on DB2 V8.1 for z/OS 1.10.
For one of the query against a Partitioned Tablespace (Index Controlled), the Explain output shows ACCESSTYPE = I and INDEXONLY = Y. We had the table space in STOP status. When the query is run, we are getting -904 against TS. why is it accessing TS when INDEXONLY = Y? [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46867@MAIL02.fisalan.nycnet
7580 364 42_Re: can't access www.db2expert.com website12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 22 Apr 2010 20:47:40 +0300539_utf-8 Ditto !
Works like a charm J
Isaac Yassin
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Asamoto, Roy
Sent: Thursday, April 22, 2010 7:38 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] can't access www.db2expert.com website
I am able to get to the page.
_____
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: Thursday, April 22, 2010 10:03 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] can't access www.db2expert.com website [...]35_00d401cae243$e7400860$b5c01920$@net
7945 22 56_ADMIN Re: [DB2-L] can't access www.db2expert.com website0_19_galambos@ROGERS.COM31_Thu, 22 Apr 2010 17:51:44 +0000589_- Let's drop this thread please
"Age is an issue of mind over matter. If you don't mind, it doesn't matter." ~ Mark Twain
mobile email sent from my blackberry.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]103_669172947-1271958706-cardhu_decombobulator_blackberry.rim.net-950405795-@bda271.bisx.prod.on.blackberry
7968 30 15_REORG Frequency4_Anil21_alisha_kale@YAHOO.COM31_Thu, 22 Apr 2010 12:38:34 -0400672_UTF-8 Hi All !
For all those DBAs on DB2 for z/os.
Do you prefer regularly scheduled reorg jobs (online reorgs) ? or do you prefer regularly scheduled reorg monitoring jobs and reorg only on as-need basis ?
Thanks.
Anil Kale
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]52_9507726960330898.WA.alishakaleyahoo.com@www.idug.org
7999 445 50_Re: [FLUFF] can't access www.db2expert.com website14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 22 Apr 2010 13:58:22 -0400540_iso-8859-1 Aurora, were you going somewhere that doesn't have train service?
-ps
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dell'Anno, Aurora
Sent: Thursday, April 22, 2010 10:50 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] can't access www.db2expert.com website
are you sure it's not stuck somewhere on top of a volcano?
Sorry, I know it's not Friday yet but I've been marooned for a week due to the blasting volcano and feel grumpy about it :-S [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4686A@MAIL02.fisalan.nycnet
8445 38 25_Re: access path selection9_Subbu Rao24_subrahmanyarao@YAHOO.COM31_Thu, 22 Apr 2010 14:09:19 -0400385_UTF-8 Roy,
Thank you very much for the suggestion. As a matter of fact, we did run an exclusive RUNSTATS with KEYCARD and FREQVAL options included on Prod and as expected, the access path selection matched the one we are getting in TEST !! However, in TEST we still have the statistics KEYCARD and FREQVAL and an explain is giving the access path which is perfrectly good. [...]56_1741812691011074.WA.subrahmanyaraoyahoo.com@www.idug.org
8484 79 33_Re: INDEXONLY = 'Y' in PLAN_TABLE15_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM31_Thu, 22 Apr 2010 14:30:44 -0400585_us-ascii Tried with UR too, it is still same.
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson,
Phil
Sent: Thursday, April 22, 2010 1:12 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE
Try running with UR and see if that makes a difference.
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Vidya
Attuluri
Sent: Thursday, April 22, 2010 12:34 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE [...]81_3E90A949C5684E46AEDAB76BD03B81830799B2B3@HDQNCEXCL1V1.mihdq.marrcorp.marriott.com
8564 68 44_LUW 9.7 SP1, The Tools Database and The Path7_Ed Long19_rdhm99a@PRODIGY.NET31_Thu, 22 Apr 2010 11:48:40 -0700553_iso-8859-1 I'm trying to install on a Windows XP VMWARE image a 9.7 SP1 ESE DB2 instance.
It basically works except...
1: When I go to run a backup and try to add it to a schedule, I get a -551 saying that DB2ADMIN, Administrator or whoever doesn't have the select privilege on TOOLSDB. Sure enough they don't. Only SYSTEM has access; none of these id's can grant the access either. What can I do about this?
2: When I go to create a database and I specify "Use the database path as a storage path" I get SQL1052N The database path X does [...]43_102707.89480.qm@web80203.mail.mud.yahoo.com
8633 61 40_Re: Question about ONLINE REORG on Index13_David Simpson22_dsimpson@THEMISINC.COM31_Thu, 22 Apr 2010 13:49:15 -0500625_utf-8 On a REORG INDEX, the RIDs do not change so there is no need to map old to new.
-----Original Message-----
From: Sekhar Mekala
Sent: Thursday, April 22, 2010 12:57 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Question about ONLINE REORG on Index
Hi all,
I am planning to run Online REORG on an Index in our Production environment. As per the syntax, I do not have to supply any Mapping table (as we do while perfroming ONLINE REORG on a table space). My question is, if we do NOT supply a mapping table for REORG INDEX SHRLEVEL CHANGE (I [...]46_003901cae24c$78b9cd70$0a0b010a@themisinc.local
8695 249 42_Re: can't access www.db2expert.com website15_Gaston, Raymond17_GastonRay@ORU.COM31_Thu, 22 Apr 2010 15:03:54 -0400524_iso-8859-1 Me too!
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Asamoto, Roy
Sent: Thursday, April 22, 2010 12:38 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] can't access www.db2expert.com website
I am able to get to the page.
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: Thursday, April 22, 2010 10:03 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] can't access www.db2expert.com website [...]63_0A69F3794ED5F842B1A75AEF1D59EC430C4A6E9F@exchmbir.conedison.net
8945 98 33_Re: INDEXONLY = 'Y' in PLAN_TABLE14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 22 Apr 2010 15:04:01 -0400635_us-ascii What's the REASON code with the -904? Is it complaining about the tablespace in STOP, or something else?
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Attuluri, Vidya
Sent: Thursday, April 22, 2010 2:31 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE
Tried with UR too, it is still same.
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson,
Phil
Sent: Thursday, April 22, 2010 1:12 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4686B@MAIL02.fisalan.nycnet
9044 363 42_Re: can't access www.db2expert.com website16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Thu, 22 Apr 2010 14:13:07 -0500621_iso-8859-1 I can access the site fine from home, but I did restart the server so please
try again now and let me know if there are still problems.
Thanks,
Dan
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gaston, Raymond
Sent: Thursday, April 22, 2010 2:04 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [SPAM] Re: can't access www.db2expert.com website
Me too!
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Asamoto, Roy
Sent: Thursday, April 22, 2010 12:38 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] can't access www.db2expert.com website [...]35_023901cae24f$d831b330$88951990$@com
9408 501 42_Re: can't access www.db2expert.com website13_Campbell, Jim26_Jim.Campbell@COURTS.WA.GOV31_Thu, 22 Apr 2010 12:13:59 -0700596_iso-8859-1 I'm not having any issues
Jim Campbell
Sr. Database Administrator
360-704-4015 (voice plus voice-mail)
360-586-8869 (fax)
Jim.Campbell@courts.wa.gov
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Daniel Luksetich
Sent: Thursday, April 22, 2010 12:13 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] can't access www.db2expert.com website
I can access the site fine from home, but I did restart the server so please try again now and let me know if there are still problems.
Thanks,
Dan [...]66_2A1B28C5CC983C4F8253CF1FA121853214C2C41C49@EXCHMAIL1.courts.wa.gov
9910 48 19_Re: REORG Frequency10_Joe Geller21_joerg6666@HOTMAIL.COM31_Thu, 22 Apr 2010 14:36:07 -0400497_UTF-8 Yes.
Or, to be more precise: It depends.
Or, to be even more precise: If I know (from analysis) that a tablespace
will benefit from a regularly scheduled reorg, I will do that. If a tablespace
does not look like it will benefit from frequent reorgs, I won't. But a
monitoring job (that will automatically do a reorg based on some conditions
I specify) will take care of those that should occasionally be reorged, but
will do so without manual intervention. [...]53_0855392737223780.WA.joerg6666hotmail.com@www.idug.org
9959 524 50_Re: [FLUFF] can't access www.db2expert.com website10_Roger Hecq18_Roger.Hecq@UBS.COM31_Thu, 22 Apr 2010 15:59:48 -0400394_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. [...]62_1A700EEF49343148A08879B1E3CA5BA61494D87C@NSTMC101PEX1.ubsw.net
10484 55 13_DB2 HPU error14_Phil Smith III15_lists@AKPHS.COM31_Thu, 22 Apr 2010 16:08:28 -0400498_utf-8 DB2 HPU stopped working for us last week. The only messages were in JESMSGLG:
IKJ56245I FILE SYSIN NOT ALLOCATED, NOT ENOUGH SPACE ON VOLUMES+
IKJ56245I USE DELETE COMMAND TO DELETE UNUSED DATA SETS
And these messages in the SYSPRINT:
INZI143E ERROR: DURING SYSIN ALLOCATION
INZU366I UTILITY RETURN CODE 8 (REASON CODE 0x16a001)
Which clearly state that the allocation problems is on the SYSIN. The reason code is not documented and only says call IBM Support. [...]35_031e01cae257$93a75690$baf603b0$@com
10540 80 19_Re: REORG Frequency10_Roger Hecq18_Roger.Hecq@UBS.COM31_Thu, 22 Apr 2010 16:16:55 -0400475_US-ASCII I am a strong believer in only reorging thos indexes and tablespaces
that need it. There are plenty of tools available to monitor stats and
only generate tne necessary reorgs.
Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Anil
Sent: Thursday, April 22, 2010 12:39 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] REORG Frequency [...]62_1A700EEF49343148A08879B1E3CA5BA61494D88E@NSTMC101PEX1.ubsw.net
10621 146 33_Re: INDEXONLY = 'Y' in PLAN_TABLE15_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM31_Thu, 22 Apr 2010 17:17:05 -0400610_us-ascii Phile,
It is complaining that TS is in STOP state. These are the messages from
MSTR.
12.43.21 STC05031 DSNT501I # DSNIDBET RESOURCE UNAVAILABLE 938
938 CORRELATION-ID=XXXXXXX
938 CONNECTION-ID=DB2CALL
938 LUW-ID=X=20772
938 REASON 00C90081
938 TYPE 00000200
938 NAME XXXXX .XXXXX
Regards
Vidya
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson,
Phil
Sent: Thursday, April 22, 2010 3:04 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE [...]81_3E90A949C5684E46AEDAB76BD03B81830799B398@HDQNCEXCL1V1.mihdq.marrcorp.marriott.com
10768 108 33_Re: INDEXONLY = 'Y' in PLAN_TABLE10_Dave Nance16_dav1mo@YAHOO.COM31_Thu, 22 Apr 2010 14:35:21 -0700704_iso-8859-1 Is this an update or delete? David Nance 804-683-1507 ________________________________ From: "Sevetson, Phil" To: DB2-L@IDUGDB2-L.ORG Sent: Thu, April 22, 2010 1:11:43 PM Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE Try running with UR and see if that makes a difference. -----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Vidya Attuluri Sent: Thursday, April 22, 2010 12:34 PM To: DB2-L@IDUGDB2-L.ORG Subject: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE Hi, we are on DB2 V8.1 for z/OS 1.10. For one of the query against a Partitioned Tablespace (Index Controlled), the Explain output shows ACCESSTYPE = I and INDEXONLY = Y. [...]41_65962.7961.qm@web57804.mail.re3.yahoo.com
10877 441 33_Re: INDEXONLY = 'Y' in PLAN_TABLE15_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM31_Thu, 22 Apr 2010 17:37:42 -0400601_us-ascii This is a Select Statement.
Regards
Vidya
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dave Nance
Sent: Thursday, April 22, 2010 5:35 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE
Is this an update or delete?
David Nance
804-683-1507
________________________________
From: "Sevetson, Phil"
To: DB2-L@IDUGDB2-L.ORG
Sent: Thu, April 22, 2010 1:11:43 PM
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE [...]81_3E90A949C5684E46AEDAB76BD03B81830799B3A8@HDQNCEXCL1V1.mihdq.marrcorp.marriott.com
11319 62 19_Re: REORG Frequency10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 22 Apr 2010 17:52:56 -0400643_UTF-8 From DB2 point of view I think today, with RTS (or other monitors, but as RTS are free.....) you can easily detect TSs/IXs needing to be reorganized so basically there's no need to reorganize a given object regularly and somewhat 'blindly'......to discover later that some objects are actually reorganized regularly (in my last company there were some tables with high daily updates/deletes they were selected almost every day) so 'regular' scheduled reorgs can be seen as a particular case of 'non-regular' ones scheduled with a intra-reorg time almost constant (short or long). In practice scheduling reorgs on 'as-need' basis at [...]48_5039441582289168.WA.mscarpacesve.it@www.idug.org
11382 59 53_Re: SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status0_22_DB2information@AOL.COM29_Thu, 22 Apr 2010 19:57:17 EDT655_US-ASCII Olle,
You may wish to view _www.recoveryknowledge.com_
(http://www.recoveryknowledge.com) for Health Check Interface for DB2 will give you a complete
history of all Syscopy and all recovery information.
Ed.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]31_7a193.17ee9353.39023c5d@aol.com
11442 123 43_Re: AW: [DB2-L] Question about single quote0_18_RENUSHARMA@AOL.COM29_Thu, 22 Apr 2010 21:52:55 EDT371_US-ASCII Thank You , This query really helped me
Thanks
Renu
Another thing if if have to insert data like
insert into temp values ('Renu's Sharma') gives an error. i have to insert
'' quotes there to insert a row.
Is there a way I can replace all single quote by 2 '' or if I can insert
renu's sharma with some escape sequence.. [...]29_335d.dc163f8.39025777@aol.com
11566 149 33_Re: INDEXONLY = 'Y' in PLAN_TABLE0_24_hhuang@DCCSH.ICBC.COM.CN31_Fri, 23 Apr 2010 11:59:11 +0800524_GB2312 Vidya,
The plan_table or EXPLAIN output shows statement level access path and
locking information,
but in your case, the locks on tablespace/table are acquired by system or
package level,
such as some IS locks on TS, claimers, and so on.
So, -904 is deserved, and work as design, even with ISOLATION UR, it can't
be avoided.
"Sevetson, Phil"
·¢¼þÈË: IDUG DB2-L
2010-04-23 01:11
Çë´ð¸´ ¸ø
IDUG DB2-L [...]69_OF2D4623EE.44B2EDDA-ON4825770E.00054289-4825770E.0015E59A@icbc.com.cn
11716 57 25_SV: [DB2-L] DB2 HPU error13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Fri, 23 Apr 2010 08:13:15 +0200443_utf-8 Hi Phil,
Why do you use VUM018? You limit the use of temporary datasets to 1 volume.
I recommend you use VUM013 and specify the generic unit type for temporary datasets in your site and leave VUM018 blank.
Olle
-----Ursprungligt meddelande-----
Från: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] För Phil Smith III
Skickat: den 22 april 2010 22:08
Till: DB2-L@IDUGDB2-L.ORG
Ämne: [DB2-L] DB2 HPU error [...]70_B0000573F0F67C438DC58043C06F4CAB49D3A99804@FSPAS01EV011.fspa.myntet.se
11774 48 35_Hilde Lippens is out of the office.13_Hilde Lippens26_Hilde.Lippens@SANLAM.CO.ZA31_Fri, 23 Apr 2010 08:21:45 +0200722_US-ASCII I will be out of the office starting 23/04/2010 and will not return until
04/05/2010.
Please book all requests via the team room; any of my colleagues will
handle them. Janine Le Roux will attend meetings I might be booked for.
I will respond to your mails when I return.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________ [...]70_OFA40EC7DD.63A11397-ON4225770E.0022F349-4225770E.0022F349@Sanlam.co.za
11823 23 53_Re: SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Fri, 23 Apr 2010 03:41:05 -0400556_UTF-8 Thank you Roy for taking your valuable time :-)
The query works exactly in the way I want with great performance!
Olle
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________ [...]57_6131524835189361.WA.olle.brostromswedbank.se@www.idug.org
11847 304 25_Re: access path selection11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 23 Apr 2010 10:07:41 +0200664_ISO-8859-1 do a select in TEST and PROD from SYSIBM.SYSCOLDIST using the TBOWNER,
TBNAME as WHERE and compare the contents. Especially check the STATSTIME
column!
A query like this should do
SELECT STATSTIME
,NAME
,COLVALUE
,TYPE
,CARDF
,COLGROUPCOLNO
,NUMCOLUMNS
,FREQUENCYF * 100
FROM SYSIBM.SYSCOLDIST
WHERE TBOWNER = 'ROYBOY'
AND TBNAME = 'ROYTABLE'
ORDER BY 2 , 4 , 7 , 6 , 1 DESC
;
I find this query shows me the interesting data in SYSCOLDIST. You must
also not forget that even when all statistics are 100% the same there are
*still* reasons that the access path can/will be different. [...]64_OF8855744D.C8AF9FA8-ONC125770E.002C0AD1-C125770E.002CA663@seg.de
12152 231 19_Re: REORG Frequency11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 23 Apr 2010 10:12:33 +0200407_ISO-8859-1 The golden rule of reorg
Dont
Simple isnt it? Of course the real rule is "It Depends" and the way I do
it is
1) Reorg INDEX's on a threshold based basis sometimes more than once a day
2) Reorg TABLESPACES only and really only if the TS must be moved, the PRI
allocation must be changed, or its in a real mess (CLUSTER RATIO) and the
primary access is random [...]64_OF87F534C0.5697F854-ONC125770E.002CBC64-C125770E.002D1864@seg.de
12384 194 53_Re: SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 23 Apr 2010 10:27:47 +0200561_ISO-8859-1 you wait...I get paid with Beer and Whisky over here!!!
Of course I give out Pizza when I write a good bug....so fairs fair!!!
Glad it does what you want and have a great weekend!
Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
http://www.seg.de
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]64_OF78C802DE.5834054C-ONC125770E.002E5D9D-C125770E.002E7D3F@seg.de
12579 290 19_Re: REORG Frequency14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Fri, 23 Apr 2010 10:56:09 +0200399_iso-8859-1 Why do you change the primary allocation. Just define it as -1. ( also change secondary allocation to -1 ) I use this for more than 3 years. Never had problems.
greetings,
Patrick Steurs
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: vrijdag 23 april 2010 10:13
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] REORG Frequency [...]60_96DBEA7143ECD048905FAAA27D86E0D212E404D0@VSRBEX10.LANPRD.NBB
12870 100 19_Re: REORG Frequency11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 23 Apr 2010 11:22:57 +0200366_ISO-8859-1 Why do you change the primary allocation. Just define it as -1. ( also
change secondary allocation to -1 ) I use this for more than 3 years.
Never had problems.
greetings,
Patrick Steurs
If you extract and copy ddl between subsystems it is better to have
7654000, as opposed to -1, in pri..... believe me on this one!!! [...]64_OFE2C4EAD4.0594FCB9-ONC125770E.00337003-C125770E.00338A6C@seg.de
12971 239 51_AW: [DB2-L] AW: [DB2-L] Question about single quote35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Fri, 23 Apr 2010 11:32:59 +0200368_iso-8859-1 Renu
I don't know, if I understand you right, but the REPLACE-function will do that:
insert into temp values (REPLACE('Renu''s Sharma', '''', '"')
But why should you do that? In order to use that function, you have to put 2 quotes in "Renu's" and 4 quotes in the string, you want to change. That doesn't make the statement easier. [...]43_DB2-L%201004230533106281.05C3@IDUGDB2-L.ORG
13211 49 48_Re: LUW 9.7 SP1, The Tools Database and The Path14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 23 Apr 2010 20:34:18 +1000370_US-ASCII 1) My recollection is that a Windows Administrator implicitly has the authority to issue a
UPDATE DBM CFG USING sysadm_group
And then someone in that group can do the appropriate.
2) Perchance, does the instance owner have update access to the database path?
James Campbell
On 22 Apr 2010 at 11:48, Ed Long wrote: [...]47_4BD2044A.27121.225F41@jacampbell.acslink.net.au
13261 167 33_Re: INDEXONLY = 'Y' in PLAN_TABLE14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 23 Apr 2010 10:09:51 -0400509_us-ascii Vidya,
I don't know what to suggest, then. I agree with you that INDEXONLY='Y' access _should_ not need to allocate the tablespace, but clearly your query is trying to do so. Is this important enough to you to open an ETR or problem ticket with IBM?
-ps
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Attuluri, Vidya
Sent: Thursday, April 22, 2010 5:17 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4686C@MAIL02.fisalan.nycnet
13429 385 61_AW: [DB2-L] SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Fri, 23 Apr 2010 16:20:42 +0200848_iso-8859-1 Roy
One more question: Do you want to take image-copies for tablespaces with no tables?
Mit freundlichen Grüßen
Walter Janißen
ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:walter.janissen@itergo.com
Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996
________________________________
Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Roy Boxwell
Gesendet: Donnerstag, 22. April 2010 16:24
An: DB2-L@IDUGDB2-L.ORG
Betreff: Re: [DB2-L] SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status [...]43_DB2-L%201004231020517626.0616@IDUGDB2-L.ORG
13815 206 33_Re: INDEXONLY = 'Y' in PLAN_TABLE12_Martin Hubel17_Martin@MHUBEL.COM31_Fri, 23 Apr 2010 10:39:53 -04000_43_DB2-L%201004231040022586.0618@IDUGDB2-L.ORG
14022 353 33_Re: INDEXONLY = 'Y' in PLAN_TABLE18_B. L. "Tink" Tysor16_Tink@BLTYSOR.COM31_Fri, 23 Apr 2010 10:31:09 -0400370_UTF-8 Hi Phile,
Could it be that DB2 disallows any access to the data (index only or not)
when the tablespace is stopped? If you are not allowed to get data directly
from the table, why let you do it via the back door?
Don't know, just guessing.
Tink
On Fri, Apr 23, 2010 at 10:09 AM, Sevetson, Phil wrote: [...]62_l2i105fd0281004230731we893226dz5541632ded995736@mail.gmail.com
14376 273 19_Re: REORG Frequency16_Robert Catterall21_rfcatterall@GMAIL.COM31_Fri, 23 Apr 2010 09:46:16 -0500505_ISO-8859-1 "or it's in a real mess (CLUSTER RATIO) and the primary access is random "
Roy, don't you mean "or it's in a real mess (CLUSTER RATIO) and the primary
access is *sequential*"? From a row-retrieval point of view, a
well-organized tablespace means better performance for queries that search
based on the clustering key and retrieve a lot of rows (sequential
processing). Response time for random retrieval of just a few rows is less
dependent on tablespace clustering. [...]62_m2rde7260f31004230746q9911ff29u28f9b60d0639dbd9@mail.gmail.com
14650 118 33_Re: INDEXONLY = 'Y' in PLAN_TABLE19_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Fri, 23 Apr 2010 10:46:37 -0400366_us-ascii I disagree with the premise that if the plan table has an index-only access path that it should allow the query to execute.
A hallmark of SQL is that the SQL asks for data without regard to "how the data will be retrieved." So if an SQL query requests data and the tablespace is in STOPped or UT status it should fail regardless of access path. [...]70_C7DCA78FAF334147B2FD39A220A4C50001A3CBE226@HFDPMSGCMS02.aeth.aetna.com
14769 818 33_Re: INDEXONLY = 'Y' in PLAN_TABLE14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 23 Apr 2010 10:47:24 -0400510_us-ascii Martin,
The solution to that would be to either (a) deny access via REVOKE statements, or (b) stop the index as well. The question here is one of performance - if DB2 is not _using_ the tablespace, why is it attempting to _allocate_ the tablespace?
-ps
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Martin Hubel
Sent: Friday, April 23, 2010 10:40 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4686D@MAIL02.fisalan.nycnet
15588 690 19_Re: REORG Frequency14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 23 Apr 2010 10:56:37 -0400336_iso-8859-1 Robert,
At risk of putting words in Roy's mouth, he may be trying to describe the case where the indexes are badly disorganized and many rows in the table are in FREEPAGE and "bottom-of-the-tablespace" locations. You know - after a lot of random inserts which aren't in increasing order by the cluster key :-)
-ps [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4686E@MAIL02.fisalan.nycnet
16279 543 33_Re: INDEXONLY = 'Y' in PLAN_TABLE11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 23 Apr 2010 16:59:21 +0200559_ISO-8859-1 or perhaps a PADDED index with VARCHAR which would need a data access to
get the length (But then again I think that would not get a Y as
INDEX_ONLY....)
Have a good weekend!
Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
http://www.seg.de
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]64_OF732DEDA1.29C81A89-ONC125770E.005244F7-C125770E.005256EB@seg.de
16823 899 33_Re: INDEXONLY = 'Y' in PLAN_TABLE15_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM31_Fri, 23 Apr 2010 11:00:35 -0400564_us-ascii Martin,
If IBM has designed this way, then what is the point of telling
INDEXONLY or even to have a concept of INDEXONLY access.
If someone wants to restrict the access temporarily, may be then can
stop the Database Itself or a particular TS/IX can be stopped.
Regards
Vidya
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Martin Hubel
Sent: Friday, April 23, 2010 10:40 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE [...]81_3E90A949C5684E46AEDAB76BD03B818307A1F780@HDQNCEXCL1V1.mihdq.marrcorp.marriott.com
17723 560 66_Trailer woes (was: [FLUFF] can't access www.db2expert.com website)17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Fri, 23 Apr 2010 16:13:29 +0100456_iso-8859-1 List admins, or someone out there that knows something about computers,
Is there any way we can stop adding a trailer EVERY time the messages go through the idug.org server? it used to work well on yla...
And Phil,
I actually came back at last TODAY on a train, that I booked last Monday morning since there was no availability at all, and the train station was like the startport on the way to the bar in Star Wars... [...]56_3D02E8610514C04F991CF832BA154C661056735E@UKSLMS11.ca.com
18284 155 47_[z/OS] SYSIBM.SYSTABLES Version column question18_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM31_Fri, 23 Apr 2010 08:17:01 -0700443_us-ascii Hello Listers:
Environment:
z/OS - V 1.10
DB2 - V 8.1 NFM
Question:
I would like to know when does the column Version get updated in SYSIBM.SYSTABLES. We have a table that has a Version of 1 in Production and Version of 2 in Test. I never paid any attention to it except when we had a failure when we were trying to do a "Rebuild Index" in Test after copying the data from PROD to TEST using DSN1COPY. [...]55_D42E68651088914B9B327A4EEA8433ACA6CCF25D@VM251.apsc.com
18440 66 42_DB2 for z/OS V8 - problem installing a jar12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Fri, 23 Apr 2010 16:28:10 +0100561_ISO-8859-1 I'm executing the INSTJAR REXX program from www.db2expert.com which invokes
the SQLJ.INSTALL_JAR procedure to install some java code into DB2 but I'm
getting the following -
SQL CODE IS 0
SQL CODE IS -443
CALL FAILED WITH SQLCODE = -443
SQLERRM IS INSTALL_JAR INSTALL_JAR M102 SQLCODE=-552 H30D CREATE JAR
SQLSTATE IS 38502
SQLERRD IS -818 , 0 , 0 , -1 , 0 , 0
***
which indicates a timestamp issue I believe. Is there some bind taking
place behind the scenes when creating procedures that I'm not aware of. [...]62_u2k21d1f8c21004230828pd6324910pe8d60cc26094ced7@mail.gmail.com
18507 551 19_Re: REORG Frequency16_Robert Catterall21_rfcatterall@GMAIL.COM31_Fri, 23 Apr 2010 10:32:11 -0500570_windows-1252 Could be, Phil.
Anyway, I do see tablespace organization as being important to keeping
GETPAGE activity from going bananas when sets of rows are frequently
retrieved from a table by queries searching on the table's clustering key.
Reducing GETPAGE activity is of course key to reducing the CPU cost of SQL
statement execution, and maintaining well-organized tablespaces can be an
important part of a GETPAGE-minimization strategy (again, if rows are often
retrieved in bunches, and if predicates reference the clustering key). [...]62_x2ude7260f31004230832l254b358eg59bce7eb17c817ba@mail.gmail.com
19059 314 46_Re: DB2 for z/OS V8 - problem installing a jar13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 23 Apr 2010 11:40:25 -0400606_utf-8 Hi Jim,
I think the SQLCode = -552 is the problem - looks like CREATE JAR
auth may be missing.
Perhaps just coincidence that sqlerrd=-818
Dave
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jim McAlpine
Sent: Friday, April 23, 2010 11:28 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] DB2 for z/OS V8 - problem installing a jar
I'm executing the INSTJAR REXX program from www.db2expert.com
which invokes the SQLJ.INSTALL_JAR
procedure to install some java code into DB2 but I'm getting the
following - [...]70_E814208E0983274198870632E8697D5B0E3B5404@nasa-dtw-ex001.nasa.cpwr.corp
19374 85 46_Re: DB2 for z/OS V8 - problem installing a jar12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Fri, 23 Apr 2010 16:50:47 +0100435_windows-1252 On Fri, Apr 23, 2010 at 4:40 PM, Seibert, Dave
wrote:
> Hi Jim,
>
>
>
> I think the SQLCode = -552 is the problem – looks like CREATE JAR auth
> may be missing.
>
> Perhaps just coincidence that sqlerrd=-818
>
>
>
> Dave
>
> Dave, this user has DBADM authority, shouldn't that be sufficient (BTW
I'm not a DBA as you may have gathered). [...]62_q2h21d1f8c21004230850z1c3d753ay4696317dac8fe141@mail.gmail.com
19460 110 46_Re: DB2 for z/OS V8 - problem installing a jar12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Fri, 23 Apr 2010 16:54:06 +0100561_windows-1252 On Fri, Apr 23, 2010 at 4:50 PM, Jim McAlpine wrote:
> On Fri, Apr 23, 2010 at 4:40 PM, Seibert, Dave <
> Dave.Seibert@compuware.com> wrote:
>
>> Hi Jim,
>>
>>
>>
>> I think the SQLCode = -552 is the problem – looks like CREATE JAR auth
>> may be missing.
>>
>> Perhaps just coincidence that sqlerrd=-818
>>
>>
>>
>> Dave
>>
>> Dave, this user has DBADM authority, shouldn't that be sufficient (BTW
> I'm not a DBA as you may have gathered).
>
> Jim
> [...]62_r2i21d1f8c21004230854o2b78d878rcf8d48758e36aec4@mail.gmail.com
19571 270 33_Re: INDEXONLY = 'Y' in PLAN_TABLE12_Martin Hubel17_Martin@MHUBEL.COM31_Fri, 23 Apr 2010 12:01:43 -04000_43_DB2-L%201004231201514749.0628@IDUGDB2-L.ORG
19842 340 46_Re: DB2 for z/OS V8 - problem installing a jar13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 23 Apr 2010 12:25:27 -0400467_utf-8 Hi Jim,
No DBADM isn't likely enough. DBADM contains authorities related to
a database.
The JAR is more of a program object.
Normally you could just go to the appropriate SQL reference and find the
statement in question and the reference describes the authorities
required.
This works for CREATE TABLE, VIEW, PROCEDURE, etc.
But the JAR authorities don't appear there. So I assume you'll have to
dig into the [...]70_E814208E0983274198870632E8697D5B0E3B5406@nasa-dtw-ex001.nasa.cpwr.corp
20183 258 33_Re: INDEXONLY = 'Y' in PLAN_TABLE12_Martin Hubel17_Martin@MHUBEL.COM31_Fri, 23 Apr 2010 12:36:59 -04000_43_DB2-L%201004231237099365.062C@IDUGDB2-L.ORG
20442 50 33_Re: INDEXONLY = 'Y' in PLAN_TABLE10_Joe Geller21_joerg6666@HOTMAIL.COM31_Fri, 23 Apr 2010 11:33:35 -0400609_UTF-8 Vidya,
You are mis-interpreting the point of Indexonly. It's purpose is not
availability, it's performance (DB2 does not have to go to the table row
to perform the select).
There may be other situations that are indexonly, but tablespace access
may occur. Someone already pointed out that update statements can be
indexonly (finding the row is indexonly, but the table row is updated). You
might also have a cursor followed by a DELETE WHERE CURRENT OF CURSOR.
I just ran a test with an indexonly query. Both the TS and Index have a
claim.
I think it makes sense that [...]53_4806887503667669.WA.joerg6666hotmail.com@www.idug.org
20493 401 51_Re: [z/OS] SYSIBM.SYSTABLES Version column question15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Fri, 23 Apr 2010 11:54:01 -0600286_us-ascii Not that I know of. It looks like someone did an alter to the TEST object and not to the PROD.
Your failure with DSN1COPY is not surprising. Unless you want to make the change to PROD that was done to TEST, then you have to use UNLOAD and LOAD to get data to test. [...]69_476996CBBE9AF14285E09E63C370072A13EDCE67DE@PHXCCRPRD01.adprod.bmc.com
20895 792 19_Re: REORG Frequency12_Isaac Yassin20_yassini@BEZEQINT.NET31_Fri, 23 Apr 2010 21:07:12 +0300679_utf-8 Hi
And don't forget updates that change the length of VC columns or compressed rows and forces them to move somewhere else …
Isaac Yassin
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil
Sent: Friday, April 23, 2010 5:57 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] REORG Frequency
Robert,
At risk of putting words in Roy’s mouth, he may be trying to describe the case where the indexes are badly disorganized and many rows in the table are in FREEPAGE and “bottom-of-the-tablespace†locations. You know – after a lot of random inserts which aren’t in increasing order by the cluster [...]35_01fa01cae30f$cca88050$65f980f0$@net
21688 634 51_Re: [z/OS] SYSIBM.SYSTABLES Version column question18_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM31_Fri, 23 Apr 2010 12:25:45 -0700326_us-ascii Hello Chris,
Unfortunately, the table and index structure is identical between PROD and TEST as of today but I see that the Version is 1 in PROD and 2 in TEST. I was wondering if VERSION can be altered from "2" to "1" using ALTER command. I didn't try it yet, but thought I should just ask this question. [...]55_D42E68651088914B9B327A4EEA8433ACA6CCF260@VM251.apsc.com
22323 23 37_Run Stored Procedure from JCL on Z/OS17_Trembacki, Robert26_robert.trembacki@BCBSA.COM31_Fri, 23 Apr 2010 15:08:25 -0500537_us-ascii Has anyone run a PL/SQL stored procedures using JCL without using a COBOL program or a REXX procedure?
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________ [...]59_50BA2F81CF76F7438BD87069FCFD41359F92B0D7@cexchmb1.bcbsa.com
22347 777 51_Re: [z/OS] SYSIBM.SYSTABLES Version column question13_McDonald, Ken20_Ken_McDonald@BMC.COM31_Fri, 23 Apr 2010 14:12:50 -0600575_us-ascii Hi Satish,
Given that the PROD and TEST are identical... Look at REPAIR VERSIONS information in the DB2 Utilities manual for updating the target catalog to reflect the version information from the source after a DSN1COPY. There are caveats about when REPAIR VERSIONS will fail.
As for the circumstances that create versions in DB2 Version 8, ALTER COLUMN statements to increase the size of an existing column or change from CHAR/VARCHAR will create a versioned row. In DB2 Version 9, more ALTER events (e.g. ADD COLUMN) can also cause versioning. [...]69_476996CBBE9AF14285E09E63C370072A13EDCE6ABF@PHXCCRPRD01.adprod.bmc.com
23125 36 17_Re: DB2 HPU error14_Phil Smith III15_lists@AKPHS.COM31_Fri, 23 Apr 2010 16:24:36 -0400464_utf-8 Olle Brostrom wrote:
>Why do you use VUM018? You limit the use of temporary datasets to 1 volume.
>I recommend you use VUM013 and specify the generic unit type for
>temporary datasets in your site and leave VUM018 blank.
Sure, that'll work, and on a real system it makes sense. But the error is still incoherent; my main goal in posting was so that anyone else who its it has a reference, rather than wasting a couple of days as we did. [...]35_048301cae322$ff3d2170$fdb76450$@com
23162 83 33_Re: INDEXONLY = 'Y' in PLAN_TABLE14_Wayne Driscoll18_wdrisco@US.IBM.COM31_Fri, 23 Apr 2010 16:25:27 -0500633_UTF-8 Phil,
DB2 most likely isn't attempting to "allocate" the tablespace, instead it
is looking at the DBET, the internal exception table to see if the object
is accessible. For integrity reasons, having the tablespace stopped needs
to restrict access. For instance, suppose the DBA had stopped the
tablespace in order to run DSN1COPY to refresh the data, and was then
going to run REBUILD INDEX ALL to repopulate the indexes? If the
indexonly access was allowed, the data would not be consistent. As for
REVOKE statements, authority is granted to the table, not the index, so
what would you revoke? [...]68_OF8BC30A97.F0A07830-ON8625770E.0075844D-8625770E.0075AF11@us.ibm.com
23246 103 36_Re: deadlock time and deadlock cycle19_Humphris,Richard P.25_RichardP.Humphris@CNA.COM31_Fri, 23 Apr 2010 16:43:36 -0500571_iso-8859-1 Hi Jose,
I think it's a question of how much cpu you're willing to burn (looking for deadlocks) and the number of deadlocks that are occurring on your subsystem.
We have a small number of deadlocks occurring (under 5 a day). So lowering our deadlock time interval would only burn more overhead cpu (at our shop) for very little tangible benefit. However if we had a high frequency of deadlocks, perhaps changing how the application was acquiring locks might be a better solution (than just changing zparms to identify the deadlocks sooner). [...]60_10B866DA3B4BC44FA723C2C315C8FE6F014788B6E5@E2K7CLSTA.cna.com
23350 30 81_Re: Index-controlled to Table-controlled partitioning - Performance benefits poll11_Sameer Rana21_sameerdrana@GMAIL.COM31_Sat, 24 Apr 2010 09:34:07 -0400733_UTF-8 Hey Bob ,
Thanks a lot for this wonderful write-up.
It makes the concept of TCP more palatable and relevant.
Regards,
Sameer
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________ [...]53_5270286366600567.WA.sameerdranagmail.com@www.idug.org
23381 215 36_Re: deadlock time and deadlock cycle0_17_jtonchick@AOL.COM31_Sat, 24 Apr 2010 14:28:49 -0400383_utf-8 The one thing you have to remember is that it takes two cycles (one to detect, the second to take action) before the IRLM tells DB2 (or IMS if using IRLM locking) that one of the deadlock participants needs to be abended. This means that using the dafault values (5,1) an on-line transaction will "hang" for ten seconds before it is either abended, or allowed to proceed. [...]54_8CCB1F31DF00550-1668-279AB@webmail-d080.sysops.aol.com
23597 596 33_Re: INDEXONLY = 'Y' in PLAN_TABLE12_Myron Miller22_myronwmiller@YAHOO.COM31_Sat, 24 Apr 2010 14:44:46 -0700368_us-ascii Remember that a database can have multiple tablespaces in it. Why stop everything under that database if you only want to operate with just one tablespace/index set?
And remember INDEXONLY is an OPTIMIZER only concept, not an access guarantee. Updates as others have mentioned are frequently index only, yet clearly have to access the tablespace. [...]44_351668.63308.qm@web112120.mail.gq1.yahoo.com
24194 32 41_Re: Run Stored Procedure from JCL on Z/OS14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Sun, 25 Apr 2010 11:52:25 -0400319_UTF-8 Hi Bob
As I understand your question you would like to run call a stored procedure in something like a z/os batch SPUFI / DSNTEP2 environment, thus avoiding the intrastructure coding for things like the SQLCA and print routines.
As of V8 QMF, which can be run in batch, has this functionality. [...]56_8748535283162375.WA.ibmsysproggeeksites.com@www.idug.org
24227 88 70_Re: Trailer woes (was: [FLUFF] can't access www.db2expert.com website)10_Mark Labby21_mlabby@AESSUCCESS.ORG31_Sun, 25 Apr 2010 19:58:54 -04000_67_OF4C06E849.E1076932-ON85257710.007B6E56-85257710.0083BAF5@pheaa.org
24316 98 65_Re: AW: [DB2-L] SV: [DB2-L] z/OS DB2 9: SQL for Image Copy status11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 26 Apr 2010 09:56:52 +0200572_ISO-8859-1 Roy
One more question: Do you want to take image-copies for tablespaces with
no tables?
Mit freundlichen Grüßen
Walter Janißen
Why not? I have a whole bunch of customers who do just that. Dont ask why
- it starts to hurt your brain when customers start explaining stuff like
that - ask dilbert...
Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
http://www.seg.de [...]64_OF00244297.A9A624FF-ONC1257711.002B966E-C1257711.002BA86D@seg.de
24415 106 19_Re: REORG Frequency11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 26 Apr 2010 09:58:49 +0200503_ISO-8859-1 "or it's in a real mess (CLUSTER RATIO) and the primary access is random "
Roy, don't you mean "or it's in a real mess (CLUSTER RATIO) and the
primary access is sequential"? From a row-retrieval point of view, a
well-organized tablespace means better performance for queries that search
based on the clustering key and retrieve a lot of rows (sequential
processing). Response time for random retrieval of just a few rows is less
dependent on tablespace clustering. [...]64_OF1575823E.2A79949B-ONC1257711.002BBEBA-C1257711.002BD641@seg.de
24522 89 33_Re: INDEXONLY = 'Y' in PLAN_TABLE10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 26 Apr 2010 10:03:55 +0200630_US-ASCII Which value for CURRENTDATA ? Just a curiosity.
Max Scarpa
- Road Runner theory: Faster cpu means faster problems and slower
solutions.
*******************************************************************************************************************************************
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org
The hitchhikers Galaxy Guide to IDUG EU 2010:
- DON'T PANIC
- DB2 V. 9 and V 9+1...
- DB2 LUW 9.7 with Oracle Comp. Features..
- PureScale
- PureQuery [...]66_OFBFF301AB.BDE1301D-ONC1257711.002A45DA-C1257711.002C4B48@cesve.it
24612 88 51_Re: [z/OS] SYSIBM.SYSTABLES Version column question11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 26 Apr 2010 10:14:58 +0200908_ISO-8859-1 To do what you want with DSN1COPY you *must* run a
REPAIR VERSIONS
TABLESPACE dbname.tsname
after the DSN1COPY
Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
http://www.seg.de
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]64_OF03F049FC.818BF55B-ONC1257711.002BF497-C1257711.002D50B7@seg.de
24701 337 33_Re: INDEXONLY = 'Y' in PLAN_TABLE15_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM31_Mon, 26 Apr 2010 09:34:49 -0400773_us-ascii Tried both YES, NO.
Regards
Vidya
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Max Scarpa
Sent: Monday, April 26, 2010 4:04 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE
Which value for CURRENTDATA ? Just a curiosity.
Max Scarpa
- Road Runner theory: Faster cpu means faster problems and slower
solutions.
************************************************************************
*******************************************************************
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org [...]81_3E90A949C5684E46AEDAB76BD03B818307A1FAE5@HDQNCEXCL1V1.mihdq.marrcorp.marriott.com
25039 158 19_Re: REORG Frequency16_Robert Catterall21_rfcatterall@GMAIL.COM31_Mon, 26 Apr 2010 16:06:27 -0400637_ISO-8859-1 You're a DB2-L question-answering machine, Roy. You're entitled to the
occasional "What I *meant* to say was..."
Robert
On Mon, Apr 26, 2010 at 3:58 AM, Roy Boxwell wrote:
>
> "or it's in a real mess (CLUSTER RATIO) and the primary access is random "
>
> Roy, don't you mean "or it's in a real mess (CLUSTER RATIO) and the primary
> access is *sequential*"? From a row-retrieval point of view, a
> well-organized tablespace means better performance for queries that search
> based on the clustering key and retrieve a lot of rows (sequential
> processing). [...]62_o2lde7260f31004261306ya4268160y4e77ae7f44be5228@mail.gmail.com
25198 45 24_TRIDEX Quarterly Meeting10_Roger Hecq18_roger.hecq@UBS.COM31_Mon, 26 Apr 2010 16:33:36 -0400708_UTF-8 The quarterly meeting of TRIDEX, the tri-state (NY, NJ, CT) DB2 User's Group, will be held on June 3rd at One MetroTech Center in Brooklyn, NY. The featured speakers will be Greg Lotko & John Campbell.
Meeting attendance is free, but pre-registration is required.
TRIDEX AGENDA
8:30 - 9:00 Coffee
9:00 - 9:15 Greg Lotko,VP, Warehouse Solutions,
Data Warehouse Update
9:15 – 10:30 John Campbell, IBM Distinguished Engineer
DB2 for z/OS Technical Overview
10:45– 11:45 John Campbell, IBM Distinguished Engineer
DB2 for z/OS Optimising Insert Performance(Part 1)
12:30 – 1:30 John Campbell, IBM Distinguished Engineer
DB2 for z/OS Optimising Insert [...]50_4195056466129898.WA.roger.hecqubs.com@www.idug.org
25244 42 53_Tri-State (NY,NJ,CT) Quarterly DB2 User Group Meeting10_Roger Hecq18_roger.hecq@UBS.COM31_Mon, 26 Apr 2010 16:57:12 -0400427_UTF-8 The quarterly meeting of TRIDEX, the tri-state (NY, NJ, CT) DB2 User's Group, will be held on June 3rd at One MetroTech Center in Brooklyn, NY, starting at 9:00AM,
The featured speakers will be Greg Lotko, VP of Warehous Solutions, & John Campbell. IBM Distinguished Engineer.
Attendance is free. Pre-registration & photo id are required for security reasons. Register via e-mail to tridex@comcast.net [...]50_0733183226326377.WA.roger.hecqubs.com@www.idug.org
25287 447 33_Re: INDEXONLY = 'Y' in PLAN_TABLE12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 26 Apr 2010 16:54:25 -0700644_utf-8 Locks. It still must obtain a "S-Lock" on the tablespace and that requires allocation of the tablespace. Even if the access is IX-only, the base tablespace must be prevented from a drop or generic delete? Otherwise the IX access would have problems itself. Myron ________________________________ From: "Sevetson, Phil" To: DB2-L@IDUGDB2-L.ORG Sent: Fri, April 23, 2010 10:47:24 AM Subject: Re: [DB2-L] INDEXONLY = 'Y' in PLAN_TABLE Martin, The solution to that would be to either (a) deny access via REVOKE statements, or (b) stop the index as well. The question here is one of performance – if DB2 is not [...]44_919728.23623.qm@web112102.mail.gq1.yahoo.com
25735 154 33_Re: INDEXONLY = 'Y' in PLAN_TABLE0_24_hhuang@DCCSH.ICBC.COM.CN31_Tue, 27 Apr 2010 08:36:07 +0800469_GB2312 It seems that my reply was delayed by the list server last week.
I just try to send it again. :)
Vidya,
The plan_table or EXPLAIN output shows statement level access path and
locking information,
but in your case, the locks on tablespace/table are acquired by system or
package level,
such as some IS locks on TS, claimers, and so on.
So, -904 is deserved, and work as design, even with ISOLATION UR, it can't
be avoided. [...]69_OF2D4623EE.44B2EDDA-ON4825770E.00054289-48257712.00034E9E@icbc.com.cn
25890 31 18_Compare Time stamp10_Ron Thomas17_ron5174@GMAIL.COM31_Tue, 27 Apr 2010 01:39:47 -0400356_UTF-8 Hi,
I have a table that has a last_update_timestamp column that is a TIMESTAMP data type. I am writing a COBOL/DB2 program where I am passing in a number of days as a parameter, and I need the sql to return to me data based on the last_updated_timestamp <= current date - number of days passed in. Could someone help me? This is not working: [...]49_9446509891116542.WA.ron5174gmail.com@www.idug.org
25922 40 22_Re: Compare Time stamp9_Tony Saul30_generalemaillists@YAHOO.COM.AU31_Mon, 26 Apr 2010 23:35:02 -0700597_iso-8859-1 Is it just that the timestamp is date and time (down to milliseconds). Do you need need to convert it to just a date where DATE(last_update_timestamp) <= (CURRENT DATE - v_NumDays DAYS) Regards, Tony ----- Original Message ---- From: Ron Thomas To: DB2-L@IDUGDB2-L.ORG Sent: Tue, 27 April, 2010 3:09:47 PM Subject: [DB2-L] Compare Time stamp Hi, I have a table that has a last_update_timestamp column that is a TIMESTAMP data type. I am writing a COBOL/DB2 program where I am passing in a number of days as a parameter, and I need the sql to return to me data [...]43_474688.11886.qm@web34402.mail.mud.yahoo.com
25963 75 30_AW: [DB2-L] Compare Time stamp35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 27 Apr 2010 09:08:40 +0200850_iso-8859-1 Ron
What do you think about:
where last_update_timestamp <= (SELECT CURRENT DATE - v_NumDays DAYS FROM SYSIBM.SYSDUMM1)
Mit freundlichen Grüßen
Walter Janißen
ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:walter.janissen@itergo.com
Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996
-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Ron Thomas
Gesendet: Dienstag, 27. April 2010 07:40
An: DB2-L@IDUGDB2-L.ORG
Betreff: [DB2-L] Compare Time stamp [...]43_DB2-L%201004270308508724.00CB@IDUGDB2-L.ORG
26039 55 22_Re: Compare Time stamp14_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Tue, 27 Apr 2010 09:27:23 +0200567_UTF-8 What is the error? Did you miss the colon in front of the host variable
v_NumDays?
If the error is a type error in the condition, you could cast the date
on the right side to
a timestamp, using a builtin function. Or extract the date from the
timestamp on the
left side.
Kind regards
Bernd
Ron Thomas schrieb:
> Hi,
> I have a table that has a last_update_timestamp column that is a TIMESTAMP data type. I am writing a COBOL/DB2 program where I am passing in a number of days as a parameter, and I need the sql [...]28_4BD691DB.9030102@t-online.de
26095 26 39_Pelletier, Alison is out of the office.16_Alison Pelletier28_alisonpelletier@DISCOVER.COM31_Tue, 27 Apr 2010 04:00:20 -0500644_US-ASCII I will be out of the office starting 04/26/2010 and will not return until
05/03/2010.
I will respond to your message when I return.
Please consider the environment before printing this email.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]70_OF3F3B5FBE.D0EC9BBD-ON86257712.0031780D-86257712.0031780D@discover.com
26122 71 34_[AD] Cogito webinars - Rescheduled13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 27 Apr 2010 05:48:16 -0400410_iso-8859-1 I promised an update when the recently postponed Cogito Index Optimization webinar had been rescheduled
Well, I can now do more than that
We have moved the April 1st webinar to April 28th and moved the webinar originally scheduled for April 28th to June 3rd
So now the timetable looks like this
Wed, Apr 28 12:00 PM - 1:30 PM EDT Workload Centric Index Optimization [...]60_4440F5DA00E3F3459BBCB97431B91B661287530123@MAILR004.mail.lan
26194 302 196_The DB2Night Show Episode #17 - "DB2 LUW Performance Update & Best Practices", with special guest Berni Schiefer, Distinguished Engineer, IBM Toronto Lab - Friday 30 April 2010 at 10:00 am USA CDT13_David Chapman24_David.Chapman@IAG.COM.AU31_Tue, 27 Apr 2010 20:57:58 +1000771_us-ascii _______________________________________________________________________________________
Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________
Hello DB2 List,
"DB2 LUW Performance Update & Best Practices", with special guest Berni Schiefer, Distinguished Engineer, IBM Toronto Lab
The International DB2 Users Group (IDUG) is pleased to partner with DBI to deliver a free Webcast by renowned presenter Scott Hayes, President & CEO, DBI. This live event will run approximately 60 minutes and participants will have the opportunity to pose questions to the presenter. Capacity is limited, so register early. [...]43_DB2-L%201004270658119955.00E9@IDUGDB2-L.ORG
26497 72 22_Re: Compare Time stamp13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Tue, 27 Apr 2010 07:50:01 -0400422_utf-8 Hi Ron,
As others have posted, providing the specific SQLcode for the failing query will get you better help quicker.
I suspect you're getting -401 because you can't compare a date and a timestamp.
If you convert the timestamp to a date, that'll work.
where date(ts_last_update) >= current date - 90 days
If you're getting -180 or -181 you've got a bad format or an illegal date. [...]70_E814208E0983274198870632E8697D5B0E3B5435@nasa-dtw-ex001.nasa.cpwr.corp
26570 556 19_Re: REORG Frequency13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 27 Apr 2010 08:08:20 -0400464_utf-8 I’m surprised no one has (yet) mentioned AREORP status!
Perhaps that because it’s “only†advisory and most of the posters here seem to be male…..
But in general, yes – reorg by threshold not just “because it’s Sunday and we’ve always reorged on a Sundayâ€
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk [...]60_4440F5DA00E3F3459BBCB97431B91B661287530130@MAILR004.mail.lan
27127 109 22_Re: Compare Time stamp18_B. L. "Tink" Tysor16_Tink@BLTYSOR.COM31_Tue, 27 Apr 2010 08:03:23 -0400590_UTF-8 Hi Ron,
Would (CURRENT TIMESTAMP - v_NumDays DAYS) work for you?
Tink
On Tue, Apr 27, 2010 at 1:39 AM, Ron Thomas wrote:
> Hi,
> I have a table that has a last_update_timestamp column that is a TIMESTAMP
> data type. I am writing a COBOL/DB2 program where I am passing in a number
> of days as a parameter, and I need the sql to return to me data based on the
> last_updated_timestamp <= current date - number of days passed in. Could
> someone help me? This is not working:
>
> where last_update_timestamp <= [...]61_x2k105fd0281004270503o9d313ae8p71e96035041ccfb@mail.gmail.com
27237 238 19_Re: REORG Frequency13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Tue, 27 Apr 2010 06:20:40 -0600564_utf-8 ‘...Perhaps that because it’s “only†advisory and most of the posters here seem to be male…..’
Is that like most male SQL-coding programmers ending up with long-running URs ‘cause they’re afraid to COMMIT?
I’d count AREO* status (and REORP) as valid ‘reorg by threshold’ items too. Hmmm... wonder if we could add a check-box on a certain product to allow reorgs on, ‘Whatever day we hysterically (intentional spelling) always reorg this thing on, whether it needs it or not’. One shouldn’t hold one’s breath. [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420C3CE5D4@PHXCCRPRD04.adprod.bmc.com
27476 63 22_Re: Compare Time stamp9_Sam Baugh24_Sam.Baugh@MOTION-IND.COM31_Tue, 27 Apr 2010 08:50:24 -0500609_utf-8 I have found that many don't seem to realize that you can subtract or add YEARS, MONTHS, DAYS to TIMESTAMPS also, perhaps these were limited to just DATE sometime in the past. Here is a sample SQL to show some of the different options.
select current timestamp
, current timestamp - 7 days
, timestamp(current date - 7 days,'00:00:00')
, round_timestamp(current timestamp - 7 days,'DDD')
, trunc_timestamp(current timestamp - 7 days,'DDD')
, round_timestamp('2010-04-27-12.00.00.000000','DDD')
, trunc_timestamp('2010-04-27-12.00.00.000000','DDD')
from sysibm.sysdummy1 [...]73_1A20EF32A1A1CA44B36AF5D6920397221645EDFED4@MIHQPEXMB1.corp.motion-ind.com
27540 166 46_Re: DB2 for z/OS V8 - problem installing a jar12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Tue, 27 Apr 2010 14:56:09 +0100631_windows-1252 On Fri, Apr 23, 2010 at 5:25 PM, Seibert, Dave
wrote:
> Hi Jim,
>
>
>
> No DBADM isn’t likely enough. DBADM contains authorities related to a
> database.
>
> The JAR is more of a program object.
>
> Normally you could just go to the appropriate SQL reference and find the
> statement in question and the reference describes the authorities required.
>
> This works for CREATE TABLE, VIEW, PROCEDURE, etc.
>
> But the JAR authorities don’t appear there. So I assume you’ll have to dig
> into the
>
> *Application Programming [...]62_u2s21d1f8c21004270656y1b80d1b0ha700a76a45ffc42f@mail.gmail.com
27707 568 46_Re: DB2 for z/OS V8 - problem installing a jar13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Tue, 27 Apr 2010 10:58:53 -0400380_utf-8 Hi Jim,
Looks like a plain ol' UDF, so you should be able to invoke it either
from SPUFI, QMF, or any other SQL processor.
From the names & sizes of the parameters I guess the UDF takes first and
last names and plunks them together to return a combo string.
If you have a table with first and last names, you can just issue the
statement: [...]70_E814208E0983274198870632E8697D5B0E3B543F@nasa-dtw-ex001.nasa.cpwr.corp
28276 34 43_Re: z/OS DEFINE NO with TEMPLATE and UNLOAD13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Tue, 27 Apr 2010 12:51:21 -0400261_UTF-8 One option might be to have your friendly sys prog (or application prog), write a short simple program to open the file and read the first record (set cond code = 00), if end of file at first record (set cond code = 04). You could then skip the load [...]55_6316490780050025.WA.jackrcampbellyahoo.com@www.idug.org
28311 25 22_Re: Compare Time stamp10_Ron Thomas17_ron5174@GMAIL.COM31_Tue, 27 Apr 2010 13:16:35 -0400537_UTF-8 Thanks for all your replies..i was able to fix the issue based on the suggestions.
Thanks.
Ron
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]49_3583589623238893.WA.ron5174gmail.com@www.idug.org
28337 73 43_Re: z/OS DEFINE NO with TEMPLATE and UNLOAD14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 27 Apr 2010 13:50:35 -0400614_us-ascii You mean something like this REXX prog I wrote a couple of days ago?
/*----------------------------- REXX -------------------------------*/
/* READ THE FIRST RECORD OF A FILE TO TEST FOR DATA IN A FILE */
/*------------------------------------------------------------------*/
TRACE R ;
/* GET THE FIRST ROW OF DD SYSUT1, THE FILE WE ARE EXAMINING */
"EXECIO 1 DISKR SYSUT1 (FINIS"
/* SET RETURN CODE TO 1, IF THERE ARE NO RECORDS */
IF QUEUED()=0 THEN RETURN 1;
ELSE DO;
/* SET RETURN CODE TO 0, IF THERE ARE ANY RECORDS */
DELSTACK;
RETURN 0;
END;
EXIT [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4688F@MAIL02.fisalan.nycnet
28411 158 41_[AD] NEODBUG Quarterly Meeting 2010-05-2011_James Szabo24_jim.szabo@ROADRUNNER.COM31_Tue, 27 Apr 2010 15:18:09 -0400322_iso-8859-1 The next quarterly meeting of the Northeast Ohio Database Users Group (NEODBUG) will occur on Thursday, May 20, 2010 in Independence, Ohio (south of Cleveland). We are pleased to have Bob Harbus of IBM and Bill Arledge of BMC Software as speakers. The agenda is as follows:
8:30 - 9:00 Registration [...]41_B4B493F633FB454E8D721C76DFDD4BD8@D150TYB1
28570 50 48_Re: [AD} z/OS DEFINE NO with TEMPLATE and UNLOAD16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 27 Apr 2010 16:56:10 -0400298_utf-8 Warning - [ad] added to the subject.
For those of you who are interested in NOT instantiating the DB2 VSAM dataset for DEFINE NO objects where the input dataset is empty - and you have access to CA Fast Load, there is a parameter to control whether to define the VSAM dataset or not. [...]56_0A18F096E689AC43BB2F52DAE5674D60083FEDDA@USILMS14.ca.com
28621 83 43_Re: z/OS DEFINE NO with TEMPLATE and UNLOAD14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 27 Apr 2010 13:50:35 -0400614_us-ascii You mean something like this REXX prog I wrote a couple of days ago?
/*----------------------------- REXX -------------------------------*/
/* READ THE FIRST RECORD OF A FILE TO TEST FOR DATA IN A FILE */
/*------------------------------------------------------------------*/
TRACE R ;
/* GET THE FIRST ROW OF DD SYSUT1, THE FILE WE ARE EXAMINING */
"EXECIO 1 DISKR SYSUT1 (FINIS"
/* SET RETURN CODE TO 1, IF THERE ARE NO RECORDS */
IF QUEUED()=0 THEN RETURN 1;
ELSE DO;
/* SET RETURN CODE TO 0, IF THERE ARE ANY RECORDS */
DELSTACK;
RETURN 0;
END;
EXIT [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4688F@MAIL02.fisalan.nycnet
28705 116 48_Re: [AD} z/OS DEFINE NO with TEMPLATE and UNLOAD10_Mark Labby21_mlabby@AESSUCCESS.ORG31_Tue, 27 Apr 2010 17:50:49 -04000_67_OF11CEC5DD.9694FAA0-ON85257712.0077E877-85257712.007801D2@pheaa.org
28822 33 51_Getting Ready for DB2 10: The Competitive Advantage11_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 27 Apr 2010 21:30:02 -0400472_UTF-8 Whether you are currently using DB2 Version 8 or Version 9, getting ready for all the CPU efficiencies in DB2 10 is easy. Your company may not be thinking about migrating to DB2 10 for a couple of more years, but DB2 10 is going to get huge industry wide support once the cost savings begin to be realized by management. Everyone one wants to save money and with everyone trying to squeeze their budgets, a faster adoption of DB2 10 is in your future... [more] [...]51_1729240890272896.WA.davedavebeulke.com@www.idug.org
28856 198 55_Re: Getting Ready for DB2 10: The Competitive Advantage0_17_jtonchick@AOL.COM31_Wed, 28 Apr 2010 01:50:29 -0400478_us-ascii But, the problem is that the CPU effeciency benefit is a "soft" cost that may only be realized after the fact, while the license increase is a "hard" cost that shows up on the budget every month. I doubt that in the vast majority of shops, the reduction in CPU related expenses has a large enough ROI to offset the immediate increase in licensing costs. Now if IBM released this as DB2 9.2 instead of DB2 10, then there would be many more shops making the change. [...]52_8CCB4ADD78C4958-D34-94F0@webmail-d063.sysops.aol.com
29055 68 20_PL/1 and XML or CLOB14_Peter Backlund21_BacklundDB2@TELIA.COM31_Wed, 28 Apr 2010 16:47:38 +02000_26_4BD84A8A.8070505@telia.com
29124 137 21_Why do a DB2 Upgrade?10_Mark Labby21_mlabby@AESSUCCESS.ORG31_Wed, 28 Apr 2010 10:38:25 -04000_67_OFD4DDC04D.80603923-ON85257713.004E5D35-85257713.00506B7A@pheaa.org
29262 263 25_Re: Why do a DB2 Upgrade?12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Wed, 28 Apr 2010 15:40:43 +0000506_Windows-1252 Typically I have seen companies consider 1) new functionality 2) improved performane or better availability and lastly to maintain current support. Regrding the last, I have had to personally brief CIOs on there fiduciary responsibility under the various laws to maintain a supported version of DB2, and usually after I have done that, upgrading is not a prpoblem. But if on a currently supported release, then I believe 1) and 2) are the usual drivers. Pg
Sent via BlackBerry by AT&T [...]103_861687836-1272469237-cardhu_decombobulator_blackberry.rim.net-535794842-@bda628.bisx.prod.on.blackberry
29526 657 25_Re: Why do a DB2 Upgrade?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Wed, 28 Apr 2010 09:40:19 -0700466_us-ascii Mark, you make excellent points about the drawbacks of upgrading, but a system outage is not one of them if you utilize data sharing. We upgraded from V8 to DB2 9 NFM with no DB2 outage. That fact also reduces the perception risk and the need for extraordinary testing.
Our main reason for upgrading is almost always to stay on a supported release. Our DBA's and developers may be eager for new features, but that is not enough of an incentive. [...]64_927FE8790B320742927BCF9ADF7AE48A18F1725D0B@PDXMAIL.pacificorp.us
30184 29 25_Re: Why do a DB2 Upgrade?11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Wed, 28 Apr 2010 17:36:41 +0000333_- >* It is a system outage and getting time is not easy
It is not always a system outage on z.
If you have parallel sysplex and datasharing, you can do it a system at a time.
Like z/OS, there are downward compatabilities in sharing.
Unless, you're getting into the semantics of system vs application outage. [...]103_299233975-1272476192-cardhu_decombobulator_blackberry.rim.net-697124654-@bda026.bisx.prod.on.blackberry
30214 455 65_[Job Posting] Senior Application DBA wanted in Houston (DB2 z/OS)14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM31_Wed, 28 Apr 2010 15:43:38 -0500422_us-ascii Firstly please note that relocation will not be offered unless we have not filled the position by June 2010.
Senior Application DBA (DB2 on z/OS)
Required Skills:
* 8+ years of expertise in the administration of DB2 databases.
* 5+ years of expertise in relational database design and maintenance.
* 5+ years of expertise in the use of z/OS/MVS and DB2 database utilities. [...]54_FF18BEB4243AA049ADB77368E3BFE90012C77E9FED@vas52wk8000
30670 195 25_Re: Why do a DB2 Upgrade?12_Martin Hubel17_Martin@MHUBEL.COM31_Wed, 28 Apr 2010 16:59:26 -04000_43_DB2-L%201004281659357057.017E@IDUGDB2-L.ORG