1 IDUGDB2-L.ORG /home/listserv/home/db2-l July 2010, week 2 2 95 39_Re: z/OS SQL Stored Procedure statement17_Billy Sundarrajan21_billysundar@YAHOO.COM30_Wed, 7 Jul 2010 21:38:52 -0700556_us-ascii You can use the GETSQLSP Rexx routine that was supplied with the Stored
Procedure redbook.
The routine splits the stored proc source (CREATESTMT column in
SYSROUTINES_SRC VARCHAR 7500) to 80 chars.

With a small change, you can make the routine write the entire line (without
splitting) to a VB dataset.

Cheers

________________________________
From: Teldb2kals
To: DB2-L@IDUGDB2-L.ORG
Sent: Wed, July 7, 2010 7:20:33 AM
Subject: [DB2-L] z/OS SQL Stored Procedure statement [...]43_972595.95641.qm@web51404.mail.re2.yahoo.com 98 319 40_Re: DB2 Z/Os Version 9 Spatial Functions11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 8 Jul 2010 07:22:04 +0200709_UTF-8 yep that and a good presentation from IDUG are what I used

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

"Schade, Jeffrey"
Gesendet von: IDUG DB2-L
07.07.2010 16:05
Bitte antworten an
IDUG DB2-L

An
DB2-L@IDUGDB2-L.ORG
Kopie

Thema
Re: [DB2-L] DB2 Z/Os Version 9 Spatial Functions [...]64_OFE8A3ABEB.E84CA666-ONC125775A.001D7791-C125775A.001D7C5F@seg.de 418 253 50_Re: IBM DB2 Unload Control Deck Creation (3 lines)11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 8 Jul 2010 07:30:15 +0200570_ISO-8859-1 Just tried it in V7 and it looks like your example

INTO TABLE "DSNRGCOL"."DSN_REGISTER_APPL "

but still in my V8 NF, 9 and 10 systems I always get that format!! Looks
like:

INTO TABLE
"SVNXTEST".
"SVNXT003"

So my guess is that this change happens when V8 goes "long name" mode in
NF.

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_OFF1C9A862.F0DAF5C7-ONC125775A.001E2129-C125775A.001E3C6B@seg.de 672 90 50_Re: IBM DB2 Unload Control Deck Creation (3 lines)11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 8 Jul 2010 08:13:01 +0200928_ISO-8859-1 Just tried it in V8 CM and it looks like

INTO TABLE
"SVNXTEST".
"SVNXT003"

So my it changed in V8 CM not just in NF

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 Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
[...]64_OFB2E5A286.AA254B6E-ONC125775A.002211A9-C125775A.002226F5@seg.de 763 593 34_Re: A DB2 virtual storage question15_Smartcurl Zhang19_smartcurl@GMAIL.COM30_Thu, 8 Jul 2010 14:42:54 +0800686_windows-1252 Thank you,hampton
Thank you,barry

I recycled these two DB2 last friday.and found that different virtual
storage consuming in Monday.Today it's Thursday,two DB2 storage comsuming
number come to even.So I think it's OK for this situation.

Smartcurl Zhang

On Wed, Jul 7, 2010 at 5:17 AM, Kirk Hampton <
kirk.hampton@energyfutureholdings.com> wrote:

> We don’t do datasharing here, but I have attended the workshop a few
> years ago. If I am not mistaken, the two subsystems in your datasharing
> group have separate ZPARMS, correct? So they can have different size parms
> for EDMPOOL, etc. Also each subsystem can easily [...]60_AANLkTinUA174Fxjwdl9Q2Uw-b7t1M7kmobziloFcM2SZ@mail.gmail.com 1357 491 78_Re: [Slightly OT] - IBM Hot Water-Cooled Supercomputer Goes Live at ETH Zurich17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM30_Thu, 8 Jul 2010 08:58:47 +0100612_us-ascii imagine what costs you could save on the canteen....

oh wait they closed the canteen.

Thanks.

Aurora

Aurora Emanuela Dell'Anno
CA Technologies - MSC
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
Aurora.Dellanno@ca.com

CA Limited
Ditton Park, Riding Court Road, Datchet, SL3 9LL, UK

CA Limited is a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179. [...]56_3D02E8610514C04F991CF832BA154C66105679F7@UKSLMS11.ca.com 1849 93 19_Re: MERGE Statement20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Thu, 8 Jul 2010 11:35:35 +0300357_us-ascii MERGE in DB2 LUW, Source can be an SQL Statement like my example.
But in DB2 for z/OS it only can be a VALUES clause which is a single row
or multiple rows with host variable array. So MERGEing in DB2 for z/OS
means many left outer joins with target table according to input data.
In LUW it may be an only one big left outer join. [...]42_04bb01cb1e78$89ff4d70$0a14a8c0@cuneytgoksu 1943 140 19_Re: MERGE Statement13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 8 Jul 2010 05:40:06 -0400376_iso-8859-1 And that's also set me thinking

My simple test was 10, single row merges

Which is 10 joins

So (in theory at least) 1 TEN row merge ought to be significantly more efficient

Unfortunately, I don't think I have enough test data to make sensible measurements, but might be worth bearing in mind if anyone else is writing MERGEs on z/OS [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E8200E@MAILR004.mail.lan 2084 105 35_DB2 for z/OS V8 - copying LOB table12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 8 Jul 2010 10:49:42 +0100773_ISO-8859-1 I'm trying to copy a table that contains LOB data to another table which is
defined identically. The definition looks like this -

(
RID ROWID GENERATED BY DEFAULT NOT NULL
,MIME_TYPE VARCHAR (100)
,BASE_TABLE VARCHAR (30)
,TABLE_KEY VARCHAR (220)
,FILENAME VARCHAR (250)
,ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
,NOTES VARCHAR (2048)
,USER_NAME VARCHAR (20)
,FILE_DATA BLOB(2G)
,CREATE_DATE DATE NOT NULL
,CREATE_TIME TIME NOT NULL
,LAST_AMENDED_DATE DATE
,LAST_AMENDED_TIME TIME
,LAST_AMENDED_USER VARCHAR (20)
,DISTRIBUTABLE CHAR(1)
,VISIBILITY SMALLINT
,CONSTRAINT FILES_PKEY PRIMARY KEY (ID)
)
When I do "INSERT INTO newtable SELECT * FROM oldtable ;" I get the
following - [...]60_AANLkTikfWWYxOvFfM5t4wSoirzdPkU5JnkLnSO7Xvi3q@mail.gmail.com 2190 307 47_AW: [DB2-L] DB2 for z/OS V8 - copying LOB table35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Thu, 8 Jul 2010 11:58:53 +0200689_iso-8859-1 Jim

You cannot store values into column ID. Try:

INSERT INTO newtable OVERRIDING USER VALUE SELECT * FROM oldtable ;"

then the values for ID are ignored and DB2 generates them.

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%201007080559028958.1F5C@IDUGDB2-L.ORG 2498 97 51_Re: AW: [DB2-L] DB2 for z/OS V8 - copying LOB table12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 8 Jul 2010 11:17:39 +0100482_ISO-8859-1 2010/7/8 Walter Janißen

> Jim
>
> You cannot store values into column ID. Try:
>
> INSERT INTO newtable OVERRIDING USER VALUE SELECT * FROM oldtable ;"
>
> then the values for ID are ignored and DB2 generates them.
>
>
> Mit freundlichen Grüßen
> Walter Janißen
>
>

Walter, I did try that and got -

DSNT408I SQLCODE = -109, ERROR: OVERRIDING USER VALUE CLAUSE IS NOT
PERMITTED [...]60_AANLkTilEnvMrAEajSaEgCZoKf94sWOImaLbRke6AXOe_@mail.gmail.com 2596 210 59_AW: [DB2-L] AW: [DB2-L] DB2 for z/OS V8 - copying LOB table35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Thu, 8 Jul 2010 13:02:47 +0200861_iso-8859-1 Jim

Interesting. Did you try to select all columns except those, which are defined as GENERATED ALWAYS?

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 Jim McAlpine
Gesendet: Donnerstag, 8. Juli 2010 12:18
An: DB2-L@IDUGDB2-L.ORG
Betreff: Re: [DB2-L] AW: [DB2-L] DB2 for z/OS V8 - copying LOB table [...]43_DB2-L%201007080703014426.1F85@IDUGDB2-L.ORG 2807 263 76_Re: [FLUFF] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 8 Jul 2010 13:22:21 +0200416_UTF-8 Well we could start a underground, unofficial, ultra secret contest, 'Put
yout text Dinos' ( in some notice board) and win a bottle of great, unique
and spectacular Italian Grappa....only for IDUG EMEA

attendees of course. The jury will be made up by all handsome/gorgeous
CPC members with a beard, speaking italian fluently, living near Venice,
50 years old, green eyes and thin hair. [...]66_OFBFA1CC3A.524FE4ED-ONC125775A.003D270D-C125775A.003E78BF@cesve.it 3071 81 63_Re: AW: [DB2-L] AW: [DB2-L] DB2 for z/OS V8 - copying LOB table12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 8 Jul 2010 12:43:13 +0100433_ISO-8859-1 2010/7/8 Walter Janißen

> Jim
>
> Interesting. Did you try to select all columns except those, which are
> defined as GENERATED ALWAYS?
>
> Mit freundlichen Grüßen
> Walter Janißen
>
>
Walter, that now gives me -

DSNT408I SQLCODE = -408, ERROR: THE VALUE IS NOT COMPATIBLE WITH THE DATA
TYPE
OF ITS TARGET. TARGET NAME IS ID
Jim McAlpine [...]60_AANLkTimLpnamusKnbz52hM9Dr2yIFHu7HVtMUfjBt7ge@mail.gmail.com 3153 134 57_DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 8 Jul 2010 13:01:31 +0100917_ISO-8859-1 We are trying to convert some Oracle SQL below to DB2 -

select
*
from
( select ROW_NUMBER() OVER(ORDER BY supplier0_.NAME ASC) AS
rownumber,
supplier0_.SUPP as SUPP58_,
supplier0_.CMPY as CMPY58_,
supplier0_.STLMNT as STLMNT58_,
supplier0_.DSBLD as DSBLD58_,
supplier0_.LOCK_FLAG as LOCK4_58_,
supplier0_.MULT_PYMNT_ADDR as MULT5_58_,
supplier0_.NAME as NAME58_,
supplier0_.POP_FLAG as POP7_58_,
supplier0_.SHORT_NAME as SHORT8_58_,
supplier0_.ACCT_TYPE as ACCT9_58_
from
H30D.TPBSUPP supplier0_
where
supplier0_.CMPY = 'PM'
and supplier0_.DSBLD<>'Y'
and ( supplier0_.POP_FLAG like 'Y')
and (
upper(cast(supplier0_.SUPP as varchar(255))) like '%SU%'
or upper(cast(supplier0_.NAME as varchar(255))) like '%SU%'
or upper(cast(supplier0_.SHORT_NAME as varchar(255))) like
'%SU%'
)
) as temp_
where rownumber = 10 [...]60_AANLkTimJvfb4JGElcpjmfQ40-pEZM1HzsJaCZAbIZagp@mail.gmail.com 3288 143 39_Re: DB2 for z/OS V8 - copying LOB table11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 8 Jul 2010 14:17:53 +02001157_ISO-8859-1 I would do this

INSERT INTO NEWTABLE
(MIME_TYPE,
,BASE_TABLE
,TABLE_KEY
,FILENAME
,NOTES
,USER_NAME
,FILE_DATA
,CREATE_DATE
,CREATE_TIME
,LAST_AMENDED_DATE
,LAST_AMENDED_TIME
,LAST_AMENDED_USER
,DISTRIBUTABLE
,VISIBILITY)
VALUES
(SELECT
MIME_TYPE,
,BASE_TABLE
,TABLE_KEY
,FILENAME
,NOTES
,USER_NAME
,FILE_DATA
,CREATE_DATE
,CREATE_TIME
,LAST_AMENDED_DATE
,LAST_AMENDED_TIME
,LAST_AMENDED_USER
,DISTRIBUTABLE
,VISIBILITY
FROM OLDTABLE)
;

Should work fine...

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 Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted [...]64_OF375EE019.EA984F0E-ONC125775A.00433B2A-C125775A.00438E5D@seg.de 3432 126 39_Re: DB2 for z/OS V8 - copying LOB table12_Myron Miller22_myronwmiller@YAHOO.COM30_Thu, 8 Jul 2010 05:27:21 -0700545_us-ascii Just change the definition of ID to Generated by default before the copy with an
ALTER and then after the copy change it back.

Then your copy select will work.

Myron

________________________________
From: Jim McAlpine
To: DB2-L@IDUGDB2-L.ORG
Sent: Thu, July 8, 2010 5:49:42 AM
Subject: [DB2-L] DB2 for z/OS V8 - copying LOB table

I'm trying to copy a table that contains LOB data to another table which is
defined identically. The definition looks like this - [...]44_963029.30065.qm@web112114.mail.gq1.yahoo.com 3559 49 63_Re: AW: [DB2-L] AW: [DB2-L] DB2 for z/OS V8 - copying LOB table10_Joe Geller21_joerg6666@HOTMAIL.COM30_Thu, 8 Jul 2010 08:52:59 -0400443_UTF-8 Jim,
Did you skip ID from both the column list and the select list? If you included it on one side,
then the subsequent columns will not be aligned.

Joe

2010/7/8 Walter Janißen

Jim

Interesting. Did you try to select all columns except those, which are defined as GENERATED ALWAYS?

Mit freundlichen Grüßen
Walter Janißen

Walter, that now gives me - [...]53_0280494231122214.WA.joerg6666hotmail.com@www.idug.org 3609 457 61_Re: DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 8 Jul 2010 09:02:08 -0400670_us-ascii When are you moving to DB2 9?

That DOES have row numbering - with, surprisingly, almost the exact same syntax
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2010 - EMEA, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jim McAlpine
Sent: 08 July 2010 13:02
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E8203C@MAILR004.mail.lan 4067 31 22_DB2 DBA jobs in the UK13_Sekhar Mekala23_SEKHAR.MEKALA@GMAIL.COM30_Thu, 8 Jul 2010 08:31:11 -0400283_UTF-8 Hi friends,
I am planning to move to the UK. But before starting the process, can any one tell me the job market for DB2 DBAs in the UK? What is the average pay and which technology in DB2 (Whether DB2 on LUW or DB2 on z/OS) has good market?

Thanks in advance!! [...]55_1591408903752105.WA.SEKHAR.MEKALAGMAIL.COM@www.idug.org 4099 440 76_Re: [FLUFF] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)20_ARCHER, MARY-ELLEN H17_MARCHER@SCANA.COM30_Thu, 8 Jul 2010 09:04:21 -0400530_utf-8 Sounds like that would be incentive enough to attend IDUG EMEA! ☺

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Max Scarpa
Sent: Thursday, July 08, 2010 7:22 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] [FLUFF] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)

Well we could start a underground, unofficial, ultra secret contest, 'Put yout text Dinos' ( in some notice board) and win a bottle of great, unique and spectacular Italian Grappa....only for IDUG EMEA [...]68_3F9E5008D0AD9949951F4795A23A6E34061AD9FC1D@EXCHANGE02.CORP.SCANA.COM 4540 303 69_AW: [DB2-L] DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Thu, 8 Jul 2010 15:06:50 +0200712_iso-8859-1 Jim

What DB2 version are you running? In DB2 V9 NFM there is a OLAP-function ROW_NUMBER:

ROW_NUMBER() OVER ([PARTITION BY expression [ORDER BY expression]])

e.g. SELECT ROW_NUMBER() OVER() FROM table

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%201007080906599789.1F90@IDUGDB2-L.ORG 4844 87 61_Re: DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 8 Jul 2010 14:15:56 +0100425_windows-1252 On Thu, Jul 8, 2010 at 2:02 PM, Phil Grainger wrote:

> When are you moving to DB2 9?
>
>
>
> That DOES have row numbering – with, surprisingly, almost the exact same
> syntax
>
> *Phil Grainger*
> Cogito Ltd.
>
> phil.grainger@cogito.co.uk
> +44 (0) 1298 872 148
> +44 (0) 7505 266 768
>
We can't use V9 as we have clients on V8. [...]60_AANLkTininf6eAtFVJzIZ9jGYr41YSW7s-wvTJlwhLv7i@mail.gmail.com 4932 51 16_LOGARCHMETH1 CFG2_CJ23_carole.olson@SENTRY.COM30_Thu, 8 Jul 2010 08:57:08 -0400847_UTF-8 Hello

I have a database with this configuration -
First log archive method (LOGARCHMETH1) = DISK:/shoapp/db2/backups/cminst/rmdb/

In the DIAGLOG, I see messages archiving the logs -
ADM1844I Successfully archived log file "S0008654.LOG" to
"/shoapp/db2/backups/cminst/rmdb/cminst/RMDB/NODE0000/C0000000/" from
"/shoapp/db2/logs/cminst/rmdb/NODE0000/".

I can see the archived logs -
odms23:/shoapp/db2/backups/cminst/rmdb/cminst/RMDB/NODE0000/C0000000) ls -ltr
total 545968
-rw-r----- 1 cminst db2iadm1 995328 Jul 6 22:02 S0008653.LOG
-rw-r----- 1 cminst db2iadm1 4104192 Jul 6 23:14 S0008654.LOG
-rw-r----- 1 cminst db2iadm1 3563520 Jul 7 05:34 S0008655.LOG
-rw-r----- 1 cminst db2iadm1 4104192 Jul 7 08:28 S0008656.LOG
-rw-r----- 1 cminst db2iadm1 4104192 Jul 7 08:35 S0008657.LOG [...]55_0599047448069757.WA.carole.olsonsentry.com@www.idug.org 4984 157 39_Re: DB2 for z/OS V8 - copying LOB table12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 8 Jul 2010 14:26:04 +0100776_ISO-8859-1 On Thu, Jul 8, 2010 at 1:17 PM, Roy Boxwell wrote:

>
> I would do this
>
> INSERT INTO NEWTABLE
> (MIME_TYPE,
> ,BASE_TABLE
> ,TABLE_KEY
> ,FILENAME
> ,NOTES
> ,USER_NAME
> ,FILE_DATA
> ,CREATE_DATE
> ,CREATE_TIME
> ,LAST_AMENDED_DATE
> ,LAST_AMENDED_TIME
> ,LAST_AMENDED_USER
> ,DISTRIBUTABLE
> ,VISIBILITY)
> VALUES
> (SELECT
> MIME_TYPE,
> ,BASE_TABLE
> ,TABLE_KEY
> ,FILENAME
> ,NOTES
> ,USER_NAME
> ,FILE_DATA
> ,CREATE_DATE
> ,CREATE_TIME
> ,LAST_AMENDED_DATE
> ,LAST_AMENDED_TIME
> ,LAST_AMENDED_USER
> ,DISTRIBUTABLE
> ,VISIBILITY
> FROM OLDTABLE)
> ;
>
> Should work fine...
> *
> Roy Boxwell*
> SOFTWARE [...]60_AANLkTilrc2I3WqgG_fGYl33Fbezcuw1UNId4BH9ejiDp@mail.gmail.com 5142 109 78_Re: [Slightly OT] - IBM Hot Water-Cooled Supercomputer Goes Live at ETH Zurich13_Mick P Graley16_mgraley2@CSC.COM30_Thu, 8 Jul 2010 14:39:24 +0100463_UTF-8 Phil, you'll be pleased to know that I still support the DB2 sub-systems
that ran on the machine in the data centre you've described below (although
they live in a different data centre now). I was a programmer in
development land at the time but I remember the machine was an IBM
3090-600J. By the time I moved into the systems side (not long after you
left) it had already been replaced by the air cooled machines (Amdahl and
HDS). [...]65_OF021846AF.5E7013F2-ON8025775A.0049A5F0-8025775A.004B04E6@csc.com 5252 270 47_AW: [DB2-L] DB2 for z/OS V8 - copying LOB table35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Thu, 8 Jul 2010 15:44:50 +0200804_iso-8859-1 Jim

Erase the clause VALUES, hope that's then running fine

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 Jim McAlpine
Gesendet: Donnerstag, 8. Juli 2010 15:26
An: DB2-L@IDUGDB2-L.ORG
Betreff: Re: [DB2-L] DB2 for z/OS V8 - copying LOB table [...]43_DB2-L%201007080944596932.1F96@IDUGDB2-L.ORG 5523 390 61_Re: DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 8 Jul 2010 09:49:51 -0400436_us-ascii Bad news

Your only solution then, I think, is to write a UDF that numbers the rows. Of course, that means you have to process them all twice - once to number them and once again to return the data

Unless there are better suggestions out there?
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E8204E@MAILR004.mail.lan 5914 133 78_Re: [Slightly OT] - IBM Hot Water-Cooled Supercomputer Goes Live at ETH Zurich13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 8 Jul 2010 09:51:00 -0400491_utf-8 You've got a good memory Mick - we migrated up from a 3083

I still remember those late nights (aka early mornings) on the motorway - thank heaven they didn't have speed cameras in those days :)

Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2010 - EMEA, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E8204F@MAILR004.mail.lan 6048 223 69_AW: [DB2-L] DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Thu, 8 Jul 2010 16:19:28 +0200788_iso-8859-1 Hi Jim

You can code that simulating row_number by only using SQL. You have to use correlated nested tables like

SELECT A.something, X.ROW_NUMBER
FROM T1 A
INNER JOIN TABLE
(SELECT key of T1, count(*) as ROW_NUMBER FROM T1 B
WHERE A.key <= B.key) as X
ON x.key = A.key

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%201007081019377499.1F9B@IDUGDB2-L.ORG 6272 77 61_Re: DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 8 Jul 2010 16:20:01 +0200413_US-ASCII I was digging in my docs and I found some papers dealing wioth ORACLE
<--> DB2 conversions. It seems before V9 there's no equivalent statement
for ROW_NUMBER, it's left to program to generate a

row number for result set. But it seems (if I read it correctly) it can
be used also to limit the number of rows in result set and this could be
easily done in DB2 if this is your case. [...]66_OFEFDE4721.2188A469-ONC125775A.004C28EB-C125775A.004EBD2C@cesve.it 6350 135 51_Re: AW: [DB2-L] DB2 for z/OS V8 - copying LOB table12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 8 Jul 2010 15:26:29 +0100926_ISO-8859-1 2010/7/8 Walter Janißen

> Jim
>
> Erase the clause VALUES, hope that's then running fine
>
>
> Mit freundlichen Grüßen
> Walter Janißen
>
>

OK, the sql now looks like this -

INSERT INTO FYLES
(MIME_TYPE
,BASE_TABLE
,TABLE_KEY
,FILENAME
,NOTES
,USER_NAME
,FILE_DATA
,CREATE_DATE
,CREATE_TIME
,LAST_AMENDED_DATE
,LAST_AMENDED_TIME
,LAST_AMENDED_USER
,DISTRIBUTABLE
,VISIBILITY)
SELECT
(MIME_TYPE
,BASE_TABLE
,TABLE_KEY
,FILENAME
,NOTES
,USER_NAME
,FILE_DATA
,CREATE_DATE
,CREATE_TIME
,LAST_AMENDED_DATE
,LAST_AMENDED_TIME
,LAST_AMENDED_USER
,DISTRIBUTABLE
,VISIBILITY)
FROM FILES
;

But I get -

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT
BE
LEGAL ARE: + -
and I can't see the syntax error. [...]60_AANLkTilX8yOMinJ2HuZ7t2iekDx5rd1mFZhitDbICSQw@mail.gmail.com 6486 138 51_Re: AW: [DB2-L] DB2 for z/OS V8 - copying LOB table14_Doyle, Mark M223_mark.m2.doyle@CHASE.COM30_Thu, 8 Jul 2010 10:31:25 -0400942_us-ascii Try removing the second set of parentheses (after SELECT . . .)
Like this:

INSERT INTO FYLES
(MIME_TYPE
,BASE_TABLE
,TABLE_KEY
,FILENAME
,NOTES
,USER_NAME
,FILE_DATA
,CREATE_DATE
,CREATE_TIME
,LAST_AMENDED_DATE
,LAST_AMENDED_TIME
,LAST_AMENDED_USER
,DISTRIBUTABLE
,VISIBILITY)
SELECT
MIME_TYPE
,BASE_TABLE
,TABLE_KEY
,FILENAME
,NOTES
,USER_NAME
,FILE_DATA
,CREATE_DATE
,CREATE_TIME
,LAST_AMENDED_DATE
,LAST_AMENDED_TIME
,LAST_AMENDED_USER
,DISTRIBUTABLE
,VISIBILITY
FROM FILES
;

But I get -

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: + -
and I can't see the syntax error.

Jim McAlpine

This transmission may contain information that is privileged, confidential, legally privileged, and/or exempt from disclosure under applicable law. If you are [...]75_0BF6D36E311EAB4BA3F891660E254C363C215A0ADC@EMARC124VS01.exchad.jpmchase.net 6625 41 0_16_Charles Crockett24_cfcrockett_db2@YAHOO.COM30_Thu, 8 Jul 2010 07:56:18 -0700720_us-ascii www.rxhealthpillsworld.net

_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
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!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv43_753820.93602.qm@web54304.mail.re2.yahoo.com 6667 102 76_Re: [FLUFF] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 8 Jul 2010 17:03:14 +0200394_US-ASCII I could add a bottle of Prosecco di Valdobbiadene (white wine) and
Cabernet del Friuli ( the famous red wine of 'Less Internet and more
Cabernet !' motto) as first prize if you think it's not enough to
partecipate to unofficial contest :-))

Max Scarpa
Certified 'It's always friday for DB2 experts'

"IDUG EMEA attendees can tan simply reading 'The Sun'" [...]66_OF51574B28.68056AC9-ONC125775A.0050C001-C125775A.0052B224@cesve.it 6770 74 51_Re: AW: [DB2-L] DB2 for z/OS V8 - copying LOB table12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 8 Jul 2010 16:09:16 +0100879_ISO-8859-1 On Thu, Jul 8, 2010 at 3:31 PM, Doyle, Mark M2 wrote:

> Try removing the second set of parentheses (after SELECT . . .)
>
> Like this:
>

That was it. Thanks to all for your invaluable responses.

Much appreciated.

Jim McAlpine

_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
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!
_____________________________________________________________________ [...]60_AANLkTinCyu-egIwCjZHLNIHyZGMTcjDHFvkl0JNMu997@mail.gmail.com 6845 93 73_Re: AW: [DB2-L] DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 8 Jul 2010 16:11:02 +0100481_ISO-8859-1 2010/7/8 Walter Janißen

> Jim
>
> What DB2 version are you running? In DB2 V9 NFM there is a OLAP-function
> ROW_NUMBER:
>
> ROW_NUMBER() OVER ([PARTITION BY expression [ORDER BY expression]])
>
> e.g. SELECT ROW_NUMBER() OVER() FROM table
>
> Mit freundlichen Grüßen
> Walter Janißen
>
>

Walter, unfortunately we need to convert the Oracle sql to something that
will run in DB2 V8. [...]60_AANLkTiki9wQQZSnLzRRXJoTGxKWq2-wQnKHAdvikOLxf@mail.gmail.com 6939 284 50_Re: IBM DB2 Unload Control Deck Creation (3 lines)14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 8 Jul 2010 11:13:12 -0400426_iso-8859-1 Roy etal, you understand that this is probably because of expanding the CREATOR and NAME attribute in DB2V8?

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: Thursday, July 08, 2010 2:13 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] IBM DB2 Unload Control Deck Creation (3 lines)

Just tried it in V8 CM and it looks like [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46ABA@MAIL02.fisalan.nycnet 7224 65 10_ADMIN SPAM16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Thu, 8 Jul 2010 11:25:25 -0400465_UTF-8 We recently received an email that was clearly SPAM. While we endeavor
to weed out these parasites, sometimes they get through anyways.

BUT the only reason they even try is that they do get responses from
(from one estimate I have read about 1-2% of the recepetaints of the
offending email/spam)

Please do not add to the problem. Delete spams, and tell all your
friends/relatives/grandmother/mother/son/daughter to do the same. [...]70_551C24F0D5EA4E47BA9B76E14D016DD5092DC961@nasa-dtw-ex005.nasa.cpwr.corp 7290 34 19_What is a Database?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 8 Jul 2010 11:35:15 -0400508_UTF-8 Every so often from somewhere up high I will get a request for reports detailing the number and size of databases we manage. While I am tempted to report the number of counts from SYSIBM.SYSDATABASE, especially on a SAP subsystem, I don't think that's what they want. Even on LUW or Oracle, the Create Database command creates an instance of the RDBMS but a given instance can hold quite a few schemas, so I'm not sure if that's what they want. Usually I choose SUBSTR(NAME,1,3) from SYSDATABASE. [...]53_0951774661697731.WA.jorghluekebcbsmn.com@www.idug.org 7325 38 33_LIKE predicate with host variable11_Gary Snider29_gsnider@CENTRAL-INSURANCE.COM30_Thu, 8 Jul 2010 11:46:19 -0400655_UTF-8 I have a query with the following predicate:
WHERE UPPER(EA.AGT_EMAIL_ADDRESS) LIKE :WS-EM-EMAIL
I have also defined an index as follows:
CREATE INDEX CMIC.XAGTEMALA2
ON CMIC.TAGT_EMAIL_ADDRESS
(UPPER(AGT_EMAIL_ADDRESS,'EN_US'))
When I explain the query, it indicates that a tablespace scan will be used (costly). I understand this is because the optimizer has no way of knowing if the value of the host variable will be indexable. Explain does indicate that the index will be used if I replace the host variable with a value like 'SMITH%'. I have seen remarks in a couple of articles indicating that when LIKE is used with a [...]60_4534218720609632.WA.gsnidercentralinsurance.com@www.idug.org 7364 110 23_Re: What is a Database?15_Chris Hoelscher21_choelscher@HUMANA.COM30_Thu, 8 Jul 2010 11:56:06 -0400737_US-ASCII a database is a collection of work, temp, or "traditional" tablespaces
with like

DBADM
DBCTRL
DBMAINT
CREATETAB
CREATETS
DISPLAYDB
DROP
IMAGCOPY
LOAD
RECOVERDB
REORG
REPAIR
STARTDB
STATS
STOPDB

privileges?

Chris Hoelscher
IDMS/DB2 System & Database Architect
Humana Inc
502-476-2538
choelscher@humana.com

you only need to test the programs that you want to work correctly

The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information. [...]68_OF17E80A93.14084235-ON8525775A.0056F02A-8525775A.00578B09@humana.com 7475 56 29_Length Of Encryption password5_anand19_mahadea@LABCORP.COM30_Thu, 8 Jul 2010 10:34:35 -0400405_UTF-8 I've posted on IBM-Developerworks which is apparently a mirror to ibm.software.db2.mvs newsgroup, so I hope this isn't considered a multi-post...

We have an encrypted column on a table and are retrieving it using the following syntax (Cobol program running on z/OS)
EXEC SQL SET ENCRYPTION PASSWORD = :HVPSWD END-EXEC
EXEC SQL SELECT DECRYPT_CHAR(COL1) INTO :HV1 FROM etc END-EXEC [...]51_1564379679908322.WA.mahadealabcorp.com@www.idug.org 7532 445 50_Re: IBM DB2 Unload Control Deck Creation (3 lines)13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 8 Jul 2010 12:50:53 -0400577_iso-8859-1 But not in V8 CM it isn't!
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2010 - EMEA, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil
Sent: 08 July 2010 16:13
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] IBM DB2 Unload Control Deck Creation (3 lines) [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E8205F@MAILR004.mail.lan 7978 69 37_Re: LIKE predicate with host variable14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 8 Jul 2010 12:52:23 -0400528_us-ascii Gary,
Have you EXPLAINED the alternate construct:
WHERE UPPER(EA.AGT_EMAIL_ADDRESS) LIKE UPPER(:WS-EM-EMAIL)
?
I'm not sure that'd be an improvement but the optimizer might be smart enough to notice that the domains become equivalent in this case.

--Phil Sevetson

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gary Snider
Sent: Thursday, July 08, 2010 11:46 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] LIKE predicate with host variable [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46ABC@MAIL02.fisalan.nycnet 8048 432 76_Re: [FLUFF] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 8 Jul 2010 13:06:06 -0400371_us-ascii Seriously folks - we ARE considering making this competition part of IDUG EMEA

Let's try and bring some fun back into our conferences....

Of course, as Max said earlier ONLY IDUG EMEA 2010 attendees will be eligible to win the prize (whatever that turns out to be)

I could even bring along a bottle of Concord British wine...... Or not [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E82061@MAILR004.mail.lan 8481 591 50_Re: IBM DB2 Unload Control Deck Creation (3 lines)14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 8 Jul 2010 13:43:33 -0400568_iso-8859-1 They probably didn't make the distinction in the utility code base, given what we're seeing here.

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger
Sent: Thursday, July 08, 2010 12:51 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] IBM DB2 Unload Control Deck Creation (3 lines)

But not in V8 CM it isn't!
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46ABD@MAIL02.fisalan.nycnet 9073 35 20_Re: LOGARCHMETH1 CFG13_Jack Campbell23_jackrcampbell@YAHOO.COM30_Thu, 8 Jul 2010 13:07:50 -0400413_UTF-8 Carole

the additonal directry levels are added because you may have MORE THAN 1 DATABASE per instance, and DB2 needs to identify the archologs for each database

You can use "db2 list active databases" to see the default DB path - you should see a correlation for the "NODE0000/C0000000" on your database. If you created a second database the path would end NODE0000/C0000001....and so on [...]55_9386306096887723.WA.jackrcampbellyahoo.com@www.idug.org 9109 94 37_Re: LIKE predicate with host variable14_Larry Kintisch17_LKint@VERIZON.NET30_Thu, 8 Jul 2010 13:56:05 -0400542_us-ascii Hi Gary,

I can't answer your question about an optimizer reference. In
Tapio Lahdenmaki's book "Relational Database Index Design and the
Optimizers" and in his course [that I teach for him], we emphasize
three uncertainties that affect the optimizer choice: no statistics,
range predicates and host-variable usage. Your predicate has the
latter two. We also teach that if you need a fast response time, and
reduced CPU, then providing an "ideal" index makes the optimizer
favor that index [usually]. [...]40_0L5900K9O35OF790@vms173011.mailsrvcs.net 9204 80 33_Re: Length Of Encryption password10_Todd Burch17_toddburch@MAC.COM30_Thu, 8 Jul 2010 13:04:59 -0500493_US-ASCII VARCHAR.

Todd

On Jul 8, 2010, at 9:34 AM, anand wrote:

I've posted on IBM-Developerworks which is apparently a mirror to
ibm.software.db2.mvs newsgroup, so I hope this isn't considered a
multi-post...

We have an encrypted column on a table and are retrieving it using the
following syntax (Cobol program running on z/OS)
EXEC SQL SET ENCRYPTION PASSWORD = :HVPSWD END-EXEC
EXEC SQL SELECT DECRYPT_CHAR(COL1) INTO :HV1 FROM etc END-EXEC [...]44_A83CBDE0-E4F7-4FD3-B913-E8980947BFEF@mac.com 9285 87 20_Re: LOGARCHMETH1 CFG16_Dave Stritzinger19_davestr@COMCAST.NET30_Thu, 8 Jul 2010 14:23:56 -0400456_UTF-8 Carol,

What I believe you are seeing is the DB2 default naming convention when
you use LOGARCHMETH1 to disk. DB2 will append the instance name, database
name, and node number to the path given in the CFG parameter. The C00000000
is what is called the "Log chain" and will also be appended to the path
name. This "Log chain" number will incremented automatically when the data
base is restored with the DB2 restore command. [...]37_DFD57988009341E6A9A86F29F075DDBD@Dave 9373 73 23_Re: What is a Database?9_Ray Lopez19_raylopez@US.IBM.COM30_Thu, 8 Jul 2010 14:33:23 -0400335_UTF-8 In DB2, a database is a collection of tables. First you create a Database and then you create tables in the database.
(This is really an oversimplification because we know there are many more objects in a DB than just tables, such as indexes).

So listing the contents of SYSIBM.SYSDATABASE is technically correct. [...]51_4928496360578578.WA.raylopezus.ibm.com@www.idug.org 9447 339 97_[AD] Themis Education - DB2 DBA, Systems Administration, Stored Procedures and Performance Tuning14_John Caccavale18_jcac@THEMISINC.COM30_Thu, 8 Jul 2010 14:47:39 -0400373_UTF-8 Themis has the following virtual DB2 classes coming up in July/August. Attend from your home or work office, at any location worldwide.
Attendees will receive the same material and instruction that is delivered in our public venues. Hands on workshops will be provided along with complete freedom to interact with the instructor for discussion and questions. [...]50_1462012488054510.WA.jcacthemisinc.com@www.idug.org 9787 90 23_Re: What is a Database?14_Doyle, Mark M223_mark.m2.doyle@CHASE.COM30_Thu, 8 Jul 2010 14:49:27 -0400577_utf-8 Jorg,

You ask interesting questions, and ones that I don't think have a single correct answer.

You ask "But really, what is a database?"

Two definitions, both from IBM. The first from the DB2 Admin Guide, the second from the IBM Glossary of Computer Terminology:
1) Database: A set of DB2 structures that include a collection of tables, their associated indexes, and the table spaces in which they reside.
2) database (DB): A collection of interrelated or independent data items that are stored together to serve one or more applications. [...]75_0BF6D36E311EAB4BA3F891660E254C363C21652A5A@EMARC124VS01.exchad.jpmchase.net 9878 262 36_Converting Security from DB2 to RACF13_Joehlin, Gary26_Gary.Joehlin@SALLIEMAE.COM30_Thu, 8 Jul 2010 15:00:45 -0400370_us-ascii Hello Listers!

We are in the process of converting our security enforcer from DB2-based to RACF-based. We have used the RACFDB2 REXX to assist us thus far.

Part of the audit requirement is that after the RACF-based definitions are confirmed and the DB2-security exit is activated, we will then need to remove (REVOKE) the DB2 authorizations. [...]70_13D67285E468EF43A05E85E9A9AE8AA98EFC1A11EE@VFIEXMBX01.us.ad.usa-ed.net 10141 29 23_Re: What is a Database?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 8 Jul 2010 15:30:22 -0400444_UTF-8 Mark,

The second definition is indeed interesting and the route management may want to take. To me there's always an issue with correlating an application and data. I understand building a data centric view of an environment and/or an applciation view. But asking how much data is an application using, that becomes questionable since applications often share databases. Then do they each use the full amount or a percentage? [...]53_5519487936501392.WA.jorghluekebcbsmn.com@www.idug.org 10171 23 31_Space in Table versus Tablepart10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 8 Jul 2010 15:37:23 -0400712_UTF-8 Given that stats are updated for a given subsystem the sum(spacef) in systables should = sum(spacef) in systablepart, or not?

_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
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_6875905062062710.WA.jorghluekebcbsmn.com@www.idug.org 10195 28 23_Re: What is a Database?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 8 Jul 2010 15:32:17 -0400314_UTF-8 Ray,

Your reply is quite sensible. I don't know that I can do a report by application but I can do it by application owner or data stewrd of the data. That is by the appication most likely to be in charge of the load processes. Except SAP of course which is just a subsystem by subsystem report. [...]53_6624777407574337.WA.jorghluekebcbsmn.com@www.idug.org 10224 35 37_Re: LIKE predicate with host variable13_Terry Purcell18_tpurcel@US.IBM.COM30_Thu, 8 Jul 2010 20:22:07 -0400384_UTF-8 Gary,

Support for matching index access with Index on Expression and LIKE with host variable was not delivered originally in DB2 9 for z/OS. It is something that is currently being worked on and tested. If you want a more concrete answer on the APAR number and expected delivery date, you may want to open a PMR so that you have a way of tracking the APAR progress. [...]50_5705475579599430.WA.tpurcelus.ibm.com@www.idug.org 10260 381 40_Re: Converting Security from DB2 to RACF12_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Fri, 9 Jul 2010 03:15:05 +0200646_Windows-1252 Hi Gary,

a few considerations on your topic:

The DB2 security exit isn't too smart, I'm afraid. Definitely not smart enough to perform whatsoever comparison of internal and external access right definitions during a REVOKE action.

Furthermore, while the RACFDB2 tools generates the required RDEF/PERMIT pairings, this will be done on a like-for-like basis. With larger DB2 systems, this is likely to become an overkill for the RACF database, therefore you will want to convert these into some form of generic approach (which is much more suitable for RACF). As a result, you might want to implement a new / [...]43_SNT107-W51404D6E2AB159A2EA890DF1B50@phx.gbl 10642 201 31_PeopleSoft and DB2 Data Sharing0_29_craig.mcginnis@WELLSFARGO.COM30_Thu, 8 Jul 2010 22:21:17 -0400911_us-ascii Is there anyone from Lowe's or other that would be willing to discuss their experiences with PeopleSoft and data sharing?

Thanks in advance.

Craig McGinnis
Enterprise Database Services (EDBS) - Operating Systems Engineer
1525 West W.T. Harris Blvd. | Charlotte, NC 28288
MAC D1112-025 | Work: 828-475-0858 | Cell: 828-413-7831 | Pager: 8284137831@vtext.com
craig.mcginnis@wellsfargo.com

_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, [...]73_C1B4F7AAACD14D44B162312629D9CF994FA15C5704@MSGCMSIL1001.ent.wfb.bank.corp 10844 989 22_No estoy en la oficina32_Lainez Gutierrez, Maria Victoria21_mvlainez@IBERDROLA.ES30_Fri, 9 Jul 2010 07:01:02 +0200751_us-ascii Hola,

Hasta el 14 de Julio no voy a estar en la oficina. Para cualquier tema
urgente relacionado con DB2 escribe un correo a Idoia Monasterio (
imonasterio@iberdrola.es).

Un saludo,
Vicky

===============================================================
Este mensaje se dirige exclusivamente a su destinatario. Los datos incluidos en el
presente correo son confidenciales y sometidos a secreto profesional, especialmente en lo que
respecta a los datos personales, se prohibe divulgarlos, en virtud de las
leyes vigentes. Si usted no lo es y lo ha recibido por error o tiene conocimiento
del mismo por cualquier motivo, le rogamos que nos lo comunique por este [...]73_534FFF2C8B16444DBF458EFA2E0C9F74016B6501@CLFEXBUZ23A.corp.iberdrola.local 11834 50 35_Hilde Lippens is out of the office.13_Hilde Lippens26_Hilde.Lippens@SANLAM.CO.ZA30_Fri, 9 Jul 2010 07:04:25 +0200755_US-ASCII I will be out of the office starting 09/07/2010 and will not return until
15/07/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 Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both [...]70_OF0B7C303E.A6090E64-ON4225775B.001BDEF5-4225775B.001BDEF5@Sanlam.co.za 11885 124 37_Re: DB2 for z/OS - convert Oracle DDL14_Matthew Donald26_matthew.b.donald@GMAIL.COM30_Fri, 9 Jul 2010 15:22:14 +1000478_ISO-8859-1 I'm a bit late, but I would suggest you look at
SQLFairy,
which was specifically invented to translate Oracle DDL to DB2 (and later MS
SQL, Sybase, MySQL, PostgreSQL and SQLite). It's also good as a tool to
build data bridges between DBMS's. SQLFairy is perl-based, and it requires
a unix-like environment to run.

Matthew

On Wed, Jul 7, 2010 at 12:47 AM, Jim McAlpine wrote: [...]60_AANLkTinxr3OeD54s4xWMW67-GM4b6-6y0aBKVKQn6fyh@mail.gmail.com 12010 55 31_SQLCODE -30082 with REASON 15 ?11_Nenad Vidak21_nenad.vidak@GMAIL.COM30_Fri, 9 Jul 2010 03:58:34 -0400616_UTF-8 Hello folks,

could you please help me out with the following error:

SELECT * FROM DB2BDWP.SYSIBM.SYSDUMMY1
---------+---------+---------+---------+---------+---------+---------+--------
DSNT408I SQLCODE = -30082, ERROR: CONNECTION FAILED FOR SECURITY REASON
15.SECURITY_FAILURE :0A ()
DSNT418I SQLSTATE = 08001 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNLTAS1 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 9 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000009' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION [...]53_3119365394518847.WA.nenad.vidakgmail.com@www.idug.org 12066 220 35_Re: Space in Table versus Tablepart11_Roy Boxwell16_R.Boxwell@SEG.DE30_Fri, 9 Jul 2010 10:03:18 +0200597_ISO-8859-1 yes....sort of...bear in mind the comments about spacef in systables and
systablepart

SPACEF FLOAT(8) NOT NULL WITH DEFAULT -1

Kilobytes of DASD storage. The value is -1 if statistics
have not been gathered. The value might be non-zero for
an auxiliary table in the LOB table space.

So does your data include aux tables?

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_OFA88DDAF0.103D16C1-ONC125775B.002C17D9-C125775B.002C3F17@seg.de 12287 72 35_Re: SQLCODE -30082 with REASON 15 ?10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 9 Jul 2010 10:25:02 +0200851_US-ASCII Hi Nenad

Which values do you have in IPNAMES ?

Max Scarpa

Attend IDUG EMEA 2010, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org

DB2 law of gravity: One manager's 'YES' weighs more than 25 DBA 'NO'

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]66_OF0E409809.043328D3-ONC125775B.002D1DEB-C125775B.002E3D30@cesve.it 12360 31 35_Re: SQLCODE -30082 with REASON 15 ?11_Nenad Vidak21_nenad.vidak@GMAIL.COM30_Fri, 9 Jul 2010 05:09:49 -0400780_UTF-8 Hi Max,

this is from the DB2 I'm connecting from :

LINKNAME SECURITY_OUT USERNAMES IBMREQD IPADDR
-------- ------------ --------- ------- ------------
LUDB2WP P O N xx.xx.xx.xx

Regards, Nenad

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]53_4573950095492003.WA.nenad.vidakgmail.com@www.idug.org 12392 50 73_Re: AW: [DB2-L] DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Fri, 9 Jul 2010 12:22:21 +0100748_ISO-8859-1 Just to let you know that we did run the ROW_NUMBER sql against a DB2 for
z/OS V9 database and it ran without change. Doesn't help our V8 customers
though.

Jim McAlpine

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]60_AANLkTikqCOkEK7gfUYvF_JVNkB19QToXc6CZZ2yiOS5D@mail.gmail.com 12443 31 64_AUTO: Prabeesh Raman is out of the office (returning 12/07/2010)14_Prabeesh Raman20_rprabeesh@IN.IBM.COM30_Fri, 9 Jul 2010 17:13:29 +0530374_US-ASCII I am out of the office until 12/07/2010.

I am in training this week.Will return to office on Monday(12/7/10).
For SIA releated issues contact Santosh K Mishra
For BIW releated issues contact Indira Tatiparthi.

Note: This is an automated response to your message "Re: [DB2-L] DB2 for
z/OS V8 - copying LOB table" sent on 7/8/10 18:56:04. [...]68_OFAD089483.75FB914B-ON6525775B.00406819-6525775B.00406819@in.ibm.com 12475 143 35_Re: SQLCODE -30082 with REASON 15 ?10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 9 Jul 2010 13:52:42 +0200347_US-ASCII It looks like your user/PWD is accepted (you'd have other ret codes) but
your user couldn't be granted to perform some activities (SPUFI ?) . Has
that user the same RACF group in remote system ?

We always used TCPALVER=NO in our subsystem(s) and we had no problem, with
adequate GRANTs, in connecting 2 DB2s on z/OS. [...]66_OFE59F2F0E.DC8DA73A-ONC125775B.00400FFD-C125775B.00414043@cesve.it 12619 59 35_Re: SQLCODE -30082 with REASON 15 ?14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 9 Jul 2010 21:56:54 +1000735_US-ASCII SQLCODE=-30082 = problem on remote system
0A = Local (ie local at the remote site) Security Service non-retryable error.

Check the JES log for the remote DIST service.

James Campbell

On 9 Jul 2010 at 3:58, Nenad Vidak wrote:

> Hello folks,
>
> could you please help me out with the following error:
>
> SELECT * FROM DB2BDWP.SYSIBM.SYSDUMMY1
> ---------+---------+---------+---------+---------+---------+---------+--------
> DSNT408I SQLCODE = -30082, ERROR: CONNECTION FAILED FOR SECURITY REASON
> 15.SECURITY_FAILURE :0A ()
> DSNT418I SQLSTATE = 08001 SQLSTATE RETURN CODE
> DSNT415I SQLERRP = DSNLTAS1 SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD = 9 0 [...]47_4C379B26.517.9057275C@jacampbell.acslink.net.au 12679 132 73_Re: AW: [DB2-L] DB2 for z/OS V8 - any equivalent to the Oracle ROW_NUMBER12_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Fri, 9 Jul 2010 15:57:46 +0200344_iso-8859-1 Jim,
you mentioned earlier that your version problem is the V8 clients, not the server side. With DB2, version compatibility between servers and clients is much more permissive than what people are used from Oracle. We do have a variety of workstations with V8 clients connecting to our V9 z/OS servers without any problems. [...]43_SNT107-W378CC9A625015CE87194DAF1B50@phx.gbl 12812 85 31_AW: [DB2-L] What is a Database?35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Fri, 9 Jul 2010 16:07:57 +0200587_iso-8859-1 Joerg

From time to time I also get those questions and I translate them always to tables. In the good old IMS-world, I think, there was no confusion. If anybody wanted to know, how many databases, the answer was clear. Nobody would have counted the different segments in a database.

Similar, when there are questions related to some competitions, which company or which installation has the biggest database. In this case, I do not know, what the answer should be. Is it supposed to be the number of gigabytes a single table has, a single database or what. [...]43_DB2-L%201007091008059743.202A@IDUGDB2-L.ORG 12898 87 23_Re: What is a Database?14_Larry Kintisch17_LKint@VERIZON.NET30_Fri, 9 Jul 2010 10:45:07 -0400481_us-ascii Hi Jorg,
Your question and the various replies recalled a situation 25
years ago when I was a consultant involved with SQL/DS. Two
executives at the client wanted a report to be aggregated and ordered
differently [and maybe in the future others would have their preferences, too.]

Say the hierarchy was by product within division. We added
another table that had the executive's Code:
EXEC_CODE, DIV, PROD, AGGR_CODE, AGGR_DESCR, ORDER_CODE. [...]40_0L5A000EQOZHW6M9@vms173003.mailsrvcs.net 12986 64 23_Re: What is a Database?14_Doyle, Mark M223_mark.m2.doyle@CHASE.COM30_Fri, 9 Jul 2010 11:41:09 -0400381_utf-8 Jorg wrote:
But asking how much data is an application using, that becomes questionable since applications often share databases. Then do they each use the full amount or a percentage?

That really depends on the context or purpose for the question. For 'accounting' purposes, split percentages are probably quite useful, as Larry Kintisch so ably pointed out. [...]75_0BF6D36E311EAB4BA3F891660E254C363C2170AE93@EMARC124VS01.exchad.jpmchase.net 13051 259 17_Strange behaviour10_Mark Stone17_mastone@GMAIL.COM30_Fri, 9 Jul 2010 08:42:30 -0700495_ISO-8859-1 A coworker showed me a query he was working which when coded in a more
familiar simple form performed poorly but in a more verbose format seems to
exhibit parallelism.

What we know for a fact is that one of the letternames has a much higher
distribution than the others (10/1).

The 1st two queries use a simple single index scan to obtain the data. The
3rd query performs 3 separate index scans which are subsequently XOR'd and
XAND'd back together. [...]60_AANLkTin3G1UZZkK8BClx4hKudVcJrqMd9IuPKWgRpSxd@mail.gmail.com 13311 108 17_UNION and INDEXES12_George Henke22_henke_george@YAHOO.COM30_Fri, 9 Jul 2010 11:55:02 -0400280_UTF-8 I have the following UNION SQL, but the indexes for the table in the 2nd SUBSELECT, CLM_SRVNBR_T, do not appear to be in th BIND according to DB2ADMIN, on the indexes for the table in the 1st SUBSELECT. QC20 in the 1st SUBSELECT is actually a VIEW based on DLR_ACCT_T. [...]53_1557011053556134.WA.henkegeorgeyahoo.com@www.idug.org 13420 216 39_Re: z/OS SQL Stored Procedure statement12_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Fri, 9 Jul 2010 18:07:34 +0200506_Windows-1252 I've just done this for a single SQL StoProc on DB2 9 z/OS with a simple:

SELECT TEXT
FROM SYSIBM.SYSROUTINES
WHERE SCHEMA = 'xy' and NAME= 'xy'

Executed from a DB2 Client's CLP under Windows, the complete "CREATE PROCEDURE... ...END MAIN" statement was displayed, neatly formatted exactly as our developer had coded it. Directly on z/OS, this doesn't work for QMF (column 'TEXT' is CLOB datatype residing in an auxiliary table), and SPUFI spits it out unformattedly. [...]43_SNT107-W1246D9287C72971878EE36F1B50@phx.gbl 13637 25 35_Re: Space in Table versus Tablepart10_Jorg Lueke23_jorg_H_lueke@BCBSMN.COM30_Fri, 9 Jul 2010 12:34:01 -0400765_UTF-8 No, I'm looking at a segmented tablespace.
SYSTABLES tells me SPACEF is 1GB while TABLEPART tells me 6GB. TABLEPART is correct, not sure what the 1GB in SYSTABLES means?
I've opened an ETR for this

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]53_6920988725243961.WA.jorgHluekebcbsmn.com@www.idug.org 13663 36 23_Re: What is a Database?10_Jorg Lueke23_jorg_H_lueke@BCBSMN.COM30_Fri, 9 Jul 2010 12:41:27 -0400484_UTF-8 Thanks Mark and Larry. It does make sense to link applications and databases as you described for reporting purposes. The funny thing is there's been month long projects involving who knows how many resources to try and create listings like this. Somehow they have not come up with anything useful. I think the same is true of some ITIL tool which in it's theoretical glory would be able to link everything together and could presumably holds space information for tables. [...]53_3921074422745979.WA.jorgHluekebcbsmn.com@www.idug.org 13700 45 35_Re: Space in Table versus Tablepart14_Doyle, Mark M223_mark.m2.doyle@CHASE.COM30_Fri, 9 Jul 2010 12:51:37 -0400710_utf-8 Jorg wrote:
No, I'm looking at a segmented tablespace.
SYSTABLES tells me SPACEF is 1GB while TABLEPART tells me 6GB. TABLEPART is correct, not sure what the 1GB in SYSTABLES means?

Most likely explanation -- empty (unused) segments with formatted pages (the result, for example, of a mass or semi-mass delete).

Mark

This transmission may contain information that is privileged, confidential, legally privileged, and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. [...]75_0BF6D36E311EAB4BA3F891660E254C363C2170B133@EMARC124VS01.exchad.jpmchase.net 13746 42 35_Re: Space in Table versus Tablepart19_Jardine, Lawrence J19_JardineLJ@AETNA.COM30_Fri, 9 Jul 2010 13:27:28 -0400525_us-ascii Is statstime the same on systables and systablepart?

Larry Jardine
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jorg Lueke
Sent: Friday, July 09, 2010 12:34 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Space in Table versus Tablepart

No, I'm looking at a segmented tablespace.
SYSTABLES tells me SPACEF is 1GB while TABLEPART tells me 6GB. TABLEPART is correct, not sure what the 1GB in SYSTABLES means?
I've opened an ETR for this [...]70_C7DCA78FAF334147B2FD39A220A4C50001A4BCFF09@HFDPMSGCMS02.aeth.aetna.com 13789 133 21_Re: Strange behaviour10_Joe Geller21_joerg6666@HOTMAIL.COM30_Fri, 9 Jul 2010 13:44:09 -0400579_UTF-8 Mark,
You are asking two questions:
1) Why the different access path?
2) Why the 3rd query performs much better?

The answer to the first question is straightforward. An IN list with a single column can produce index access (Method='N')
with 3 probes of the index (for each of the 3 values). Originally, any OR in the query resulted in a tablespace scan, but some
time ago, DB2 began to recognize that an OR involving the same column = a value was equivalent to an IN list. Therefore
queries 1 & 2 result in the same access path. However, when [...]53_4921788140918011.WA.joerg6666hotmail.com@www.idug.org 13923 29 37_Re: LIKE predicate with host variable11_Gary Snider29_gsnider@CENTRAL-INSURANCE.COM30_Fri, 9 Jul 2010 15:46:11 -0400790_UTF-8 Thanks for the response Phil. I tried the alternate contruct:
SELECT EA.AGT_EMAIL_ADDRESS
FROM CMIC.TAGT_EMAIL_ADDRESS EA
WHERE
UPPER(EA.AGT_EMAIL_ADDRESS ) LIKE UPPER(:HV)
No change, still uses a tablespace scan.

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]60_6734702420984767.WA.gsnidercentralinsurance.com@www.idug.org 13953 39 37_Re: LIKE predicate with host variable11_Gary Snider29_gsnider@CENTRAL-INSURANCE.COM30_Fri, 9 Jul 2010 16:14:53 -0400525_UTF-8 Thanks for the reply Larry. The full query is:
SELECT EA.AGT_EMAIL_ADDRESS
FROM CMIC.TAGT_EMAIL_ADDRESS EA
WHERE
UPPER(EA.AGT_EMAIL_ADDRESS ) LIKE :HV
I recreated the index as follows:
CREATE INDEX CMIC.XAGTEMALA2
ON CMIC.TAGT_EMAIL_ADDRESS
(UPPER(AGT_EMAIL_ADDRESS,'EN_US'),
AGT_EMAIL_ID)
This added the primary key to the index. No change, still get a tablespace scan.
I think based on Terry Purcell's response we will use a dynamic query until the APAR is available.
Thanks. [...]60_7075403881177580.WA.gsnidercentralinsurance.com@www.idug.org 13993 23 37_Re: LIKE predicate with host variable11_Gary Snider29_gsnider@CENTRAL-INSURANCE.COM30_Fri, 9 Jul 2010 15:49:50 -0400649_UTF-8 Thanks for the reponse Terry. I have opened a PMR to get an update on the APAR. Thanks.

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]60_4907606334095263.WA.gsnidercentralinsurance.com@www.idug.org 14017 50 37_Re: LIKE predicate with host variable14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Fri, 9 Jul 2010 16:19:43 -0400511_us-ascii Sorry, that's all I got. :-(

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gary Snider
Sent: Friday, July 09, 2010 3:46 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] LIKE predicate with host variable

Thanks for the response Phil. I tried the alternate contruct:
SELECT EA.AGT_EMAIL_ADDRESS
FROM CMIC.TAGT_EMAIL_ADDRESS EA
WHERE
UPPER(EA.AGT_EMAIL_ADDRESS ) LIKE UPPER(:HV)
No change, still uses a tablespace scan. [...]64_1BA00CBB414DA34AA25ED82F4647CDE20349B975DD@MAIL02.fisalan.nycnet 14068 69 37_Re: LIKE predicate with host variable14_Larry Kintisch17_LKint@VERIZON.NET30_Fri, 9 Jul 2010 17:32:12 -0400817_us-ascii Hi Gary,
My suggestion of the primary key was just an example. For your
case try this index to perhaps get Index-only access and let us
know. Terry's point may still be valid.
Larry Kintisch

CREATE INDEX CMIC.XAGTEMALA2
ON CMIC.TAGT_EMAIL_ADDRESS
(UPPER(AGT_EMAIL_ADDRESS,'EN_US'),
AGT_EMAIL_ADDRESS)

At 04:14 PM 7/9/2010, you wrote:
>Thanks for the reply Larry. The full query is:
>SELECT EA.AGT_EMAIL_ADDRESS
>FROM CMIC.TAGT_EMAIL_ADDRESS EA
>WHERE
>UPPER(EA.AGT_EMAIL_ADDRESS ) LIKE :HV
>I recreated the index as follows:
>CREATE INDEX CMIC.XAGTEMALA2
> ON CMIC.TAGT_EMAIL_ADDRESS
> (UPPER(AGT_EMAIL_ADDRESS,'EN_US'),
> AGT_EMAIL_ID)
>This added the primary key to the index. No change, still get a
>tablespace scan.
>I think [...]40_0L5B00M9Z7TVMRO0@vms173019.mailsrvcs.net 14138 55 60_-805 after changing to DBPROTOCL(DRDA) - a BIND/REBIND query11_Leong, Werv17_werv.leong@HP.COM31_Mon, 12 Jul 2010 09:09:56 +0000626_us-ascii Hi,

Apologies in advance for our lack of knowledge concerning BIND/REBIND....

We encountered a problem when we belatedly converting a system to DB2 V8 which may require use to do some binds/rebinds....

When we converted an account to DB2 V8, their DDF appls started to fail with RC00C89015. A close match was found on IBM website. PK34538 indicate that there may be issues with DDF programs using DB2 "private" protocol with old pre-V2R3 DBRMs which results in RC00C89015. The "recommendation" is to use DBPROTOCOL(DRDA). "Local" fix was apparently to rebind the plan with DBPROTOCOL(DRDA). [...]74_EC25E8C4647DAB40AD428F0257B4021D44E8C275F4@GVW1347EXA.americas.hpqcorp.net 14194 56 60_-805 after changing to DBPROTOCL(DRDA) - a BIND/REBIND query11_Leong, Werv17_werv.leong@HP.COM31_Mon, 12 Jul 2010 09:13:36 +0000626_us-ascii Hi,

Apologies in advance for our lack of knowledge concerning BIND/REBIND....

We encountered a problem when we belatedly converting a system to DB2 V8 which may require use to do some binds/rebinds....

When we converted an account to DB2 V8, their DDF appls started to fail with RC00C89015. A close match was found on IBM website. PK34538 indicate that there may be issues with DDF programs using DB2 "private" protocol with old pre-V2R3 DBRMs which results in RC00C89015. The "recommendation" is to use DBPROTOCOL(DRDA). "Local" fix was apparently to rebind the plan with DBPROTOCOL(DRDA). [...]74_EC25E8C4647DAB40AD428F0257B4021D44E8C27600@GVW1347EXA.americas.hpqcorp.net 14251 57 35_Re: SQLCODE -30082 with REASON 15 ?11_Nenad Vidak21_nenad.vidak@GMAIL.COM31_Mon, 12 Jul 2010 06:31:38 -0400404_UTF-8 nothing in the DIST joblog.

But when I execute the same query via SPUFI against a DB2 member which runs on the same LPAR as the target ( remote ) DB2 subsys, it ends successfully ( TSO connection type is used in that case ). As I mentioned before besides DB2 SQLCA, no RACF or any other error messages is issued.
I also tried with DENTEP4 sample program but the result is the same : [...]53_6568326879018606.WA.nenad.vidakgmail.com@www.idug.org 14309 289 19_Re: MERGE Statement11_Suresh Sane21_data_arch@HOTMAIL.COM31_Mon, 12 Jul 2010 10:58:53 -0500473_iso-8859-1 Cuneyt,

>> "So MERGEing in DB2 for z/OS > means many left outer joins with target table"

You may be right, but I don't have any evidence to prove it either way. Based on the syntax, the input array is treated like a logical table so an in-core table is certainly possible. Since it performs much better than the application counterpart, I believe it does - only Optimizer folks like Terry Purcell can tell us for sure what is done internally. [...]42_COL104-W7E2A9AA67F74D452BD45298B80@phx.gbl 14599 125 14_Re: ADMIN SPAM17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Mon, 12 Jul 2010 17:18:13 +0100532_us-ascii You mean nEVER, not EVER, don't you Robert?

Thanks.

Aurora

Aurora Emanuela Dell'Anno
CA Technologies - MSC
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
Aurora.Dellanno@ca.com

CA Limited
Ditton Park, Riding Court Road, Datchet, SL3 9LL, UK

CA Limited is a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179. [...]56_3D02E8610514C04F991CF832BA154C6610567A5B@UKSLMS11.ca.com 14725 38 48_After CM in db2 v8.1 on z/OS 1.7 error in dclgen14_Sherry Simmerl20_ssimmerl@CSIHOME.COM31_Mon, 12 Jul 2010 15:53:49 -0400693_UTF-8 Receive this error from spufi for dclgen. Noticed in testing after migrating to v8.1 CM. Not corrected with dsntijnf from NFM migration even though this received cc of 0. Any ideas? I think this problem has been there before I started db2 v8.1 migration.

DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME CTSMAFDB24..DSNECP6-
8.17FA1A280DD915B2 NOT FOUND IN PLAN DSNEDCL. REASON 03
DSNT418I SQLSTATE = 51002 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -251 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF05' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC [...]52_7448505428265878.WA.ssimmerlcsihome.com@www.idug.org 14764 69 40_SDSNLOAD usage/impact in batch app. jcls15_nguyen duc tuan17_ndt.db2@GMAIL.COM31_Tue, 13 Jul 2010 10:03:56 +0200511_ISO-8859-1 Dear all ,

In our test environment, after the V9 CM migration, an alias was not
updated to point to V9 SDSNLOAD , so it is still pointed to the V8 SDSNLOAD.

They did some V9 performance / validation test having this alias in the
steplib ( the current release sdsnload is in linklist but hundred old jcls
were not updated to remove sdsnload from steplib) and discover this
problem. So they wonder if this can change something in the meaning of their
validation test. [...]60_AANLkTin88NDXIPS6ZXzByrpax9WJMobBPFQ-CImvVD8B@mail.gmail.com 14834 68 53_Tom Glaser is out of the office, returning on Monday.10_Tom Glaser25_tom_glaser@MASTERCARD.COM31_Tue, 13 Jul 2010 04:08:18 -0500678_US-ASCII I will be out of the office starting 07/12/2010 and will not return until
07/19/2010.

If you require solidDB assistance, please contact Ted Achelpohl or Kate
Kavanaugh. Thanks!

CONFIDENTIALITY NOTICE
This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you. [...]72_OF99662CDA.5FE6C5E9-ON8625775F.003232E3-8625775F.003232E3@mastercard.com 14903 34 33_z/OS: RUNSTATS & automatic REBIND14_Daniel Erdmann21_edh@VOLKSWOHL-BUND.DE31_Tue, 13 Jul 2010 11:59:59 +0200388_iso-8859-1 Hello,

I was asked to verify that there's no option to automatically rebind
packages after RUNSTATS was run.

I don't know of such an option/setting - is this correct or is there any
possibility to tell RUNSTATS to rebind corresponding packages or to setup
packages during the initial bind to become rebinded after a statistics
change or similar? [...]36_D593EEB3C79A454DA13EC4E8AABA56D3@edh 14938 82 37_Re: z/OS: RUNSTATS & automatic REBIND13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 13 Jul 2010 07:03:38 -0400444_iso-8859-1 Hi Daniel

You are right - rebinding would have to be done manually

It might be possible to write a program to look at the objects you are running RUNSTATS against and, using the DB2 catalog, figure out what should be rebound

But...

How certain are you that the resulting rebind would result in a "better" access path? What if the new statistics collected by RUNSTATS resulted in a worse access path? [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8F7A3B5@MAILR004.mail.lan 15021 223 37_Re: z/OS: RUNSTATS & automatic REBIND11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 13 Jul 2010 13:26:05 +0200594_ISO-8859-1 correct. There is no option to do this automatically (and it would be very
dangerous to do this!!). So you must simply check which packages were last
bound before tha latest runstats
and not just rebind 'em all!!!

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_OFFFA7582E.280C9142-ONC125775F.003CEA02-C125775F.003ED086@seg.de 15245 138 37_Re: z/OS: RUNSTATS & automatic REBIND10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 13 Jul 2010 13:29:50 +0200444_US-ASCII AFAIK, without a tool or a user-written program (REXX,Cobol....) there's
no way to trigger a rebind if you update tables/indexes only with
RUNSTATS.

Max Scarpa

Attend IDUG 2010 EMEA, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org

The most searched words in internet are "Paris Hilton". Paris Hilton's
most searched words are 'IDUG EMEA'. [...]66_OFBEBD2792.16F1661B-ONC125775F.003CC304-C125775F.003F27BF@cesve.it 15384 71 40_False Contentions during Modify Recovery13_Graham O'Hara32_graham.ohara@NORWICH-UNION.CO.UK31_Tue, 13 Jul 2010 07:04:03 -0400559_UTF-8 We have a weekly Modify Recovery housekeeping process to remove data from SYSCOPY and SYSLGRNX

However, this has been taking an excessive amount of time to execute and I am trying to determine why that is
and what can be done to improve the situation.

The thread stats indicate quite a lot of false contentions, along with L-lock/unlock propagations. However, the CF
Lock Table seems to be sized reasonably enough (although I am no expert in CF structures) and there is not a
great deal of concurrent activity in the system. [...]63_3258780204088793.WA.graham.oharanorwichunion.co.uk@www.idug.org 15456 71 64_Re: -805 after changing to DBPROTOCL(DRDA) - a BIND/REBIND query14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 13 Jul 2010 22:13:33 +1000570_US-ASCII First thing to consider is plan LCT05Q0 - it must be bound (or rebound) to include a
collection at DBYCOM_DB2A - either by explicitly including the location or having an '*' entry.
Could be
PKLIST( ...., DBYCOM_DB2A.SOME_COLLID), or
PKLIST( ...., *.SOME_COLLID) or a variation which includes the member name
PKLIST( ...., DBYCOM_DB2A.SOME_COLLID.DT01M0), or
PKLIST( ...., *.SOME_COLLID.DT01M0)
This is so when the plan goes to the remote location, it can search the collections there that
are listed in PKLIST and find the package. [...]48_4C3CE50D.6219.A4FFD892@jacampbell.acslink.net.au 15528 226 19_Re: MERGE Statement14_Matthew Donald26_matthew.b.donald@GMAIL.COM31_Tue, 13 Jul 2010 22:59:46 +1000615_ISO-8859-1 Few years ago, Troy Coleman of CA wrote an article for the DB2Tutor column
of IBM Systems magazine on MERGE statement
performance.
It
was fairly empirical - he built some test tables with 100,000 rows - and
compared performance using three algorithms- SELECT, INSERT or UPDATE
loop, INSERT,
if duplicate SQLCODE -803 then UPDATE, and MERGE. The MERGE was done with
two array sizes - one of 1,000 and one of 10,000. All the code was written
in COBOL, so the effect of differing array sizes was explored. [...]60_AANLkTimBcBD0ZlO1XaWJYBw68yP3c05PQv55B4Bz7MK5@mail.gmail.com 15755 30 56_Re: (OT) Happy canada day - There is a reason that . . .12_George Henke22_henke_george@YAHOO.COM31_Tue, 13 Jul 2010 10:37:10 -0400818_UTF-8 the 5th largest Canadian city is Los Angeles

Canadian bumper stickers read, "My Canada includes Quebec",
Quebec bumper stickers read, "My Quebec includes Florida".

Canada could have had English justice, French culture, and American "knowhow".
But instead it ended up with French justice, American culture, and English "knowhow" ;-)

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * 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, [...]53_8992111796551788.WA.henkegeorgeyahoo.com@www.idug.org 15786 23 56_Re: (OT) Happy canada day - There is a reason that . . .12_George Henke22_henke_george@YAHOO.COM31_Tue, 13 Jul 2010 10:43:54 -0400697_UTF-8 you cross over from the beautiful southern tropics of Canada into the northern wasteland of the United States.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * 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_9304092197418601.WA.henkegeorgeyahoo.com@www.idug.org 15810 78 44_Re: SDSNLOAD usage/impact in batch app. jcls9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 13 Jul 2010 09:50:23 -0500321_US-ASCII There are a very limited list of things for normal DB2 batch programs that
are affected by the SDSNLOAD. The primary one I know of is the attach code
(all the different versions of DSNHLI for DSN, CALL ATTACH, RRS, etc). Once
the program has started, there is NO difference in the SQL performance. [...]43_1990EE03FAED422EAA422E40C2BA1CEA@mikelaptop 15889 43 44_Re: SDSNLOAD usage/impact in batch app. jcls15_nguyen duc tuan17_ndt.db2@GMAIL.COM31_Tue, 13 Jul 2010 17:28:43 +0200670_ISO-8859-1 Thank you for your input Mike !

Duc
DB2 Sysprog at Euroclear France.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * 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!
_____________________________________________________________________ [...]60_AANLkTinaufDrKx81Gn8RhHffVIZ9FX_vtOpuISk-KSYx@mail.gmail.com 15933 24 33_Re: Length Of Encryption password5_anand19_mahadea@LABCORP.COM31_Tue, 13 Jul 2010 12:04:05 -0400752_UTF-8 Thanks Todd. I was hoping for a programmatic solution rather than a structure change (since the password column in TAB_THAT_HAS_PASSWD is currently defined as CHAR).

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * 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!
_____________________________________________________________________ [...]51_6304501752055726.WA.mahadealabcorp.com@www.idug.org 15958 31 56_Re: (OT) Happy canada day - There is a reason that . . .11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Tue, 13 Jul 2010 16:06:21 +0000371_- >Canada could have had English justice, French culture, and American "knowhow".
But instead it ended up with French justice, American culture, and English "knowhow" ;-)

The only reason I would consider this funny is if you turned out to be Canadian.
If not, I find it offensive in many ways.

Insults, and prejudicial comments, are NEVER funny! [...]105_1262408526-1279037146-cardhu_decombobulator_blackberry.rim.net-1531681365-@bda026.bisx.prod.on.blackberry 15990 49 70_ADMIN Re: [DB2-L] (OT) Happy canada day - There is a reason that . . .0_19_galambos@ROGERS.COM31_Tue, 13 Jul 2010 16:55:38 +0000461_- This thread is closed. Please do not respond/reply to this post.

-----------Original Message-----------
From: Ted MacNEIL
To: IDUG DB2-L
ReplyTo: IDUG DB2-L
Date: Jul 13 12:52:39
Subject: Re: [DB2-L] (OT) Happy canada day - There is a reason that . . .

>Canada could have had English justice, French culture, and American "knowhow".
But instead it ended up with French justice, American culture, and English "knowhow" ;-) [...]105_1634202891-1279040143-cardhu_decombobulator_blackberry.rim.net-2025242528-@bda274.bisx.prod.on.blackberry 16040 31 14_DB2 10 and SMS12_George Palko16_gpalko@OPERS.ORG31_Tue, 13 Jul 2010 13:03:32 -0400348_UTF-8 Hi List,

We are preparing for DB2 10 by converting our DB2 catalogs to SMS. My question is whether DB2 10 requires the use of DFHSM? We use CA-DISK in place of DFHSM at our shop. However, I noticed that the DB2 panels require that you specify a SMS Management CLASS. Since, we don't use DFHSM we don't use SMS Management Classes. [...]48_9990011494932538.WA.gpalkoopers.org@www.idug.org 16072 44 47_Improve Performance by Reducing Your Other Time11_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 13 Jul 2010 13:45:58 -04000_51_6592452271539035.WA.davedavebeulke.com@www.idug.org 16117 119 44_Re: SDSNLOAD usage/impact in batch app. jcls13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 13 Jul 2010 10:57:33 -0700341_us-ascii Mike, you said
"There are a very limited list of things for normal DB2 batch programs that are affected by the SDSNLOAD. The primary one I know of is the attach code..."

What is your take on utilities? I have a colleague who insists that utilities run completely in DBM1, yet I frequently see hold data such as this: [...]64_927FE8790B320742927BCF9ADF7AE48A18FF09BBE6@PDXMAIL.pacificorp.us 16237 157 44_Re: SDSNLOAD usage/impact in batch app. jcls9_Mike Bell21_mbell11a1@VERIZON.NET31_Tue, 13 Jul 2010 13:53:14 -0500351_US-ASCII I have been burned by the utilities running from a load library that didn't
match 2 times now in the last 20 years. You would think I would learn. For
at least the last 15 years, the utilities now check that the release level
is correct. I think that is a good reason to assume IBM has found problems
with mis-matching code. [...]43_C893A16E8FC044BEBB388BCD8170AD35@mikelaptop 16395 41 56_Re: (OT) Happy canada day - There is a reason that . . .12_George Henke22_henke_george@YAHOO.COM31_Tue, 13 Jul 2010 15:51:20 -0400405_UTF-8 Ted previously wrote:

"Canada could have had English justice, French culture, and American "knowhow".
But instead it ended up with French justice, American culture, and English "knowhow" ;-)

The only reason I would consider this funny is if you turned out to be Canadian.
If not, I find it offensive in many ways.

Insults, and prejudicial comments, are NEVER funny!" [...]53_4607727426169198.WA.henkegeorgeyahoo.com@www.idug.org 16437 53 90_ADMIN SECOND/FINAL NOTICE Re: [DB2-L] (OT) Happy canada day - There is a reason that . . .0_19_galambos@ROGERS.COM31_Tue, 13 Jul 2010 20:01:48 +0000552_- Second and FINAL notice

This thread is CLOSED

Any further posting will force me to institute stronger measures.

-----------Original Message-----------
From: George Henke
To: IDUG DB2-L
ReplyTo: IDUG DB2-L
Date: Jul 13 15:51:39
Subject: Re: [DB2-L] (OT) Happy canada day - There is a reason that . . .

Ted previously wrote:

"Canada could have had English justice, French culture, and American "knowhow".
But instead it ended up with French justice, American culture, and English "knowhow" ;-) [...]103_85816301-1279051317-cardhu_decombobulator_blackberry.rim.net-1875222387-@bda274.bisx.prod.on.blackberry 16491 27 24_ADMIN topics that are OT0_19_galambos@ROGERS.COM31_Tue, 13 Jul 2010 20:32:57 +0000334_- While the rules of this listserv allow for off topics threads, when it becomes obvious that the subject 'has gone way beyond' the administrators reserve the right to 'close' the thread' (or any thread that we deem off subject/against the rules). Ignore that notice, will only mean that we may have to impose stronger measures. [...]104_1482310723-1279053184-cardhu_decombobulator_blackberry.rim.net-492178404-@bda274.bisx.prod.on.blackberry 16519 122 30_Re: Temporary Tables and TCB=116_Robert Catterall21_rfcatterall@GMAIL.COM31_Wed, 14 Jul 2010 01:40:09 -0400546_ISO-8859-1 Here I am, late again to the DB2-L party.

Gary, my understanding is that declaration of global temporary tables is not
recorded in the catalog, so contention there shouldn't be an issue.

Created temporary tables are catalog-recorded when CREATED, but I don't
think that there is a catalog impact associated with INSTANTIATION of
created temp tables (generally speaking, DBAs create created temp tables,
and application programs instantiate them by referencing them in SQL DML
statements like INSERT). [...]60_AANLkTinCVXK6guGsyXOgQrEuT9Bx4jaOzKVgKrcE0uSU@mail.gmail.com 16642 86 52_SV: [DB2-L] False Contentions during Modify Recovery13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Wed, 14 Jul 2010 07:40:27 +0200447_utf-8 Hello Graham,
This problem was very significantly on DB2 V7 and V8 but I think the contention problem concerns SYSLGRNX. Now we are running DB2 9 where this problem seems to be eliminated/reduced, but We do the following to get good performance.
1. Reorg shrlevel change of SYSLGRNX and SYSCOPY once a week prior we run our MODIFY RECOVERY housekeeping
2. Execute 1 MODIFY RECOVERY statement with LISTDEF containg all objects. [...]70_B0000573F0F67C438DC58043C06F4CAB49D3A99910@FSPAS01EV011.fspa.myntet.se 16729 24 65_Auto Reply: DB2-L Digest - 13 Jul 2010 to 14 Jul 2010 (#2010-188)0_27_scott.a.saunders@ORACLE.COM31_Tue, 13 Jul 2010 23:05:28 -0700749_utf-8 From July 12 - 16, I will be visiting a customer or in transit. There will be a delay in responding to your email. For emergencies, contact my manager Pete Vammino at pete.vammino@oracle.com.

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]44_4bad10da-bc84-4a36-9f65-c90c7923d563@default 16754 235 30_Re: Temporary Tables and TCB=114_Michael Kalena27_Michael.Kalena@JPMORGAN.COM31_Wed, 14 Jul 2010 10:31:16 -0400472_us-ascii What if you create an index on the Declared Temporary Table? We've seen Catalog contention when folks do that, specifically SYSDBASE table space. We're version 8.

It mostly shows up with contention on binds. For example, an unrelated package (not used in current thread) is bound, we've seen the SP's that use Declared Temp Tables, get timeouts/deadlocks with those binds. Sometimes the bind fails but worse sometimes the thread running the SP fails. [...]75_276F3617E5150C48A5795557E7E68D200237EFBEE3@EMASC201VS01.exchad.jpmchase.net 16990 25 62_Tomas Vasko/IEA/EAGA/EAG/AT ist au=?ISO-8859-1?Q?=DFer_?=Haus.11_Tomas Vasko23_tomas.vasko@GENERALI.AT31_Wed, 14 Jul 2010 17:29:09 +0200768_ISO-8859-1 Ich bin ab 14.07.2010 außer Haus und ab 22.07.2010 wieder im Büro.
I'm out of the office from 14.07.2010 and will be back on 22.07.2010.

Ich werde Ihre Nachricht nach meiner Rückkehr beantworten.

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]68_OFA7109C99.5FE25106-ONC1257760.005510F9-C1257760.005510F9@AT.TOP.COM 17016 138 18_Re: DB2 10 and SMS10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 14 Jul 2010 17:49:51 +0200456_US-ASCII I presume you're in V9 isn't it ? In V8 there's no request for a
management class. If you do a LISTCAT in a DB2 file managed (migrated ???)
by

CA-DISK which management class is displayed ? Maybe blank ? Anyway
Management class isn't mandatory for SMS management (we hadn't HSM as
well).

Max Scarpa

The most searched words in internet are "Paris Hilton". Paris Hilton's
most searched words are 'IDUG EMEA'. [...]66_OF1B97437C.9C8F8543-ONC1257760.004FBBCE-C1257760.0056FA00@cesve.it 17155 211 18_Re: DB2 10 and SMS13_Palko, George16_gpalko@OPERS.ORG31_Wed, 14 Jul 2010 19:01:48 +0100407_us-ascii Thanks Max,
Yes we are at v9. We're working on positioning ourselves for v10 by getting the catalog/directory SMS managed. We also want to set up the regions so that we can use the system backup/restore.

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Max Scarpa
Sent: Wednesday, July 14, 2010 11:50 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] DB2 10 and SMS [...]69_85A0B6733C13D741B4A62BAD06216DB90A4DE82EC6@s1inf012.inhouse.opers.org 17367 85 14_invalid pakage13_Rahul Chandra20_adminrahul@GMAIL.COM31_Thu, 15 Jul 2010 01:09:24 +0530408_ISO-8859-1 Hi All,

I am working on DB2 V8 on Z/OS. I have these queries :-

1. How to know at what date & time(or timestamp) any package became
INVALID?
2. What exact event (alter,drop etc..) had made it invalid ?
3. How to know when package was last used ?

As SYSIBM.SYSPACKAGE has only "VALID" column but there is no information
about when it is last updated (Y to N). [...]60_AANLkTim4IYSBwUoQWUc8VV7KTpLavdPFID0-EnV0dnk4@mail.gmail.com 17453 637 18_Re: invalid pakage12_Kirk Hampton37_kirk.hampton@ENERGYFUTUREHOLDINGS.COM31_Wed, 14 Jul 2010 16:49:05 -0500557_us-ascii Hello Rahul,

Unfortunately, these are all pieces of info that are very hard to find.

1) There would have been a record of this update to SYSIBM.SYSPACKAGE in
the log, but it is not kept anywhere else that I know of.

2) Same answer as (1)

3) We have the package stats (trace 7 and 8) running all the time, and I
extract from the SMF records every day and keep a history file of plans
and packages, so we can go back and purge (FREE) old items that have not
been used for a long time. This last-used info [...]71_31F1BA84DF370A4C825F6E64D59DAF54044DC9AF@MDCTXUEXCL01N4.corptxu.txu.com