1 IDUGDB2-L.ORG /home/listserv/home/db2-l April 2010, week 3 2 198 32_AW: [DB2-L] [z/OS] DSNTEP2 Input35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Thu, 15 Apr 2010 07:05:43 +0200346_iso-8859-1 Anil,

I'm not sure whether an input dataset with a linesize > 80 characters will work, we got round the problem by writing a small REXX-routine that reads the output and reformatts it into a 80-byte-input dataset (but then, I'm afraid, you don't see the output in 1 line as your original mail suggested you would like to): [...]69_31CF7002A1132E40BD5AD816A179C1588C93438743@VMX00100.lan.huk-coburg.de 201 27 24_Re: [z/OS] DSNTEP2 Input14_Peter Vanroose17_pvanroose@ABIS.BE31_Thu, 15 Apr 2010 03:02:48 -0400734_UTF-8 The simplest solution is probably to modify the DSNTEP2 source code to make it accept (say) 1024 byte records.

-- Peter Vanroose,
ABIS Training & Consulting.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* 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 $1600 off when you both come to the conference!
_____________________________________________________________________ [...]49_3681304570103693.WA.pvanrooseabis.be@www.idug.org 229 90 51_AW: [DB2-L] Question about result table of a cursor35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 15 Apr 2010 09:23:57 +0200401_iso-8859-1 Hi

I think, Terry can answer this question at best. I am sure, with method 3, the result set will be materialized, but I am not sure, if the result set is materialized as soon as a 'Y' is somewhere in the SORT-columns. And I know for sure, that if you use static scrollable cursors, you won't see the materialization in the plan_table just due to the use of scrollable cursors. [...]43_DB2-L%201004150324092820.0251@IDUGDB2-L.ORG 320 477 23_DB2 Abnormal Terminaton9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Thu, 15 Apr 2010 13:00:39 +0530328_us-ascii Hi,

Due to an In-Abort Transaction , DB2 is Abending. I have log Data sets only until last two days but this thread is 4 days old.

I have tried BACKODUR=0 as well as LBACKOUT=YES but still the thread does not go to POSTPONED ABORT.
Tried RECOVER POSTPONED CANCEL and yet no luck.
What do I do? [...]69_E5D4741C0D67254EA3601CFEC7810DFA0FB79A2E62@BLRKECMBX05.ad.infosys.com 798 107 37_Re: Extended Addressability questions10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 15 Apr 2010 09:41:58 +0200467_US-ASCII Hi

1) We used extensively EA-enables tablespace and we had no problem, apart
some issues at the beginning when we were missing some PTFs for SMS (long
time ago). Being current with PTFs for SMS/EA for your z/OS and DB2 is
the general advice I can give (it seems to me to remember V8 had some
problem but I could be wrong) . For the rest they worked well for us and
for other collegues I heard in the past, as far as I'm concerned. [...]66_OF7A9C0143.331DF8DF-ONC1257706.0025FA0D-C1257706.0029E8C3@cesve.it 906 145 37_Re: Extended Addressability questions0_24_hhuang@DCCSH.ICBC.COM.CN31_Thu, 15 Apr 2010 17:02:51 +0800498_US-ASCII Sameer,

1.Performance degradation by EA-enabled data sets, if any, is probably
related to MIDAW.
You can use D IOS,MIDAW cammand to check it out. If MIDAW is on, you
should be safer.
For larger data sets instead of more partitions, you need to consider less
parallelism
and longer backup/maintenance time, such as IMAGE COPY, REORG, etc.

2.I just want to know is there any undocument or offline method to enlarge
DSSIZE without
DROP/CREATE/LOAD. [...]69_OF181FC458.BC19A55D-ON48257706.0030A067-48257706.0031B37E@icbc.com.cn 1052 753 27_Re: DB2 Abnormal Terminaton16_Broyles, Carol L27_Carol.L.Broyles@ACS-INC.COM31_Thu, 15 Apr 2010 06:24:33 -0500591_US-ASCII This may be too late to help you, but you'll have to put a conditional
restart record with BACKOUT=NO. All objects involved in any inflight
transactions will be placed in RECP status.

Carol L. Broyles

Affiliated Computer Services, Inc.

A Xerox Company

Office Phone: 937-495-4003

carol.l.broyles@acs-inc.com

________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of SrinivasG
Sent: Thursday, April 15, 2010 3:31 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] DB2 Abnormal Terminaton [...]70_B22003942F5A0C4DBF34DC9C6121BF9B0C4E91@A1DAL1SWPES22MB.ams.acs-inc.net 1806 54 24_Re: [z/OS] DSNTEP2 Input19_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Thu, 15 Apr 2010 07:40:51 -0400652_us-ascii Just curious, if you have the IBM DB2 ADMIN Tool, why don't you use the GEN command instead of writing your own SQL?

Larry Jardine
Aetna
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Anil
Sent: Wednesday, April 14, 2010 9:33 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] [z/OS] DSNTEP2 Input

Hi All,

The DSNTEP2 program (DB2 on z/os) reads SQL statements from a 80 bytes input data. Actually, the SQL statement is to be coded in the first 72 bytes. Although it is possible to span the sql statment on multiple lines (records), is it possible to specify a input [...]70_C7DCA78FAF334147B2FD39A220A4C50001A3B15CD7@HFDPMSGCMS02.aeth.aetna.com 1861 639 27_Re: DB2 Abnormal Terminaton13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 15 Apr 2010 06:49:19 -0400467_us-ascii Your only option might be to try a -CANCEL THREAD ..... NOBACKOUT

But bear in mind, the manual says "Canceling the thread with NOBACKOUT leaves objects in an inconsistent state. Do not issue this command with NOBACKOUT unless you have a plan to resolve the data inconsistency."

Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk [...]58_4440F5DA00E3F3459BBCB97431B91B6611B01844@MAILR004.mail.lan 2501 133 41_SQL -904 during EXCHANGE on a clone table15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL31_Thu, 15 Apr 2010 14:20:13 +0200471_us-ascii We got the next message during the exchange on a CLONE TABLE.

ERROR: DB2 execute error DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL
EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE.
REASON 00E70081, TYPE OF RESOURCE 00000A00, AND RESOURCE NAME
DPME01.SAS_AANTAL DSNT418I SQLSTATE = 57011 SQLSTATE
RETURN CODE

This table is accessed through dyn. sql and loaded and switched every
night at the same time. Until now we had no problems. [...]76_OF092279A0.7FD658E9-ONC1257706.0040F7F5-C1257706.0043CB11@belastingdienst.nl 2635 447 45_Re: SQL -904 during EXCHANGE on a clone table13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 15 Apr 2010 08:54:27 -0400573_us-ascii It may be that the error text is out of date

It should probably say "A DROP, ALTER or EXCHANGE statement was issued but ......."

So you should probably assume that it's right and you did contend with a SQL statement
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 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org [...]58_4440F5DA00E3F3459BBCB97431B91B6611B01848@MAILR004.mail.lan 3083 722 27_Re: DB2 Abnormal Terminaton13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 15 Apr 2010 07:04:54 -0600575_us-ascii I was wondering how long it would take you to suggest that, Phil. ;o)

Raymond

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger
Sent: 15 April 2010 11:49
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] DB2 Abnormal Terminaton

Your only option might be to try a -CANCEL THREAD ..... NOBACKOUT

But bear in mind, the manual says "Canceling the thread with NOBACKOUT leaves objects in an inconsistent state. Do not issue this command with NOBACKOUT unless you have a plan to resolve the data inconsistency." [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420AAA6CDB@PHXCCRPRD04.adprod.bmc.com 3806 754 27_Re: DB2 Abnormal Terminaton9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Thu, 15 Apr 2010 19:04:29 +0530638_us-ascii Hi,

Thanks for the help.

Out of desperation , I did a CRESTART finally and DB2 came up ...

Regards,
Srinivas G

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger
Sent: Thursday, April 15, 2010 4:19 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] DB2 Abnormal Terminaton

Your only option might be to try a -CANCEL THREAD ..... NOBACKOUT

But bear in mind, the manual says "Canceling the thread with NOBACKOUT leaves objects in an inconsistent state. Do not issue this command with NOBACKOUT unless you have a plan to resolve the data inconsistency." [...]69_E5D4741C0D67254EA3601CFEC7810DFA0FB7A6F9D8@BLRKECMBX05.ad.infosys.com 4561 203 24_Re: [z/OS] DSNTEP2 Input11_Chuck Kosin24_chuck.kosin@EXPERIAN.COM31_Thu, 15 Apr 2010 09:39:55 -0400326_UTF-8 Hello,

Here’s one way to do this. In step 1, with DSNTIAUL, generated the needed DDL/DCL in record lengths that are multiples of 80 bytes. In step 2, with DFSORT, cut the DSNTIAUL output into 80 byte chunks. The LRECL=80 on the input DD makes this happen. In step 3, with DSNTEP2, execute the sort output. [...]56_8152755335945038.WA.chuck.kosinexperian.com@www.idug.org 4765 908 27_Re: DB2 Abnormal Terminaton13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 15 Apr 2010 10:01:34 -0400542_us-ascii Glad you are back on-line but please bear in mind that depending on what you did with the CRESTART you may well have invalidated ALL the image copies you have in SYSCOPY and will need now to image copy EVERYTHING

In other words, if you have "cold started" DB2, you will now have a gap in the log - DB2 will not be able to perform recoveries across that gap
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk [...]58_4440F5DA00E3F3459BBCB97431B91B6611B01856@MAILR004.mail.lan 5674 279 24_Re: [z/OS] DSNTEP2 Input9_Ford Wong14_fordie@SHAW.CA31_Thu, 15 Apr 2010 08:50:37 -0600470_us-ascii Here is another idea to use if the output lines are all the same length...
I use DSNTIAUL to unload and build SQL that I want to run later into a dataset and then run the dataset into SYNCSORT to remove extraneous chars and reformat to < 72 chars. The problem with this is that the output from DNSTIAL is all the same length, etc and I know where to break the input SQL line.
Hope this helps. Some sample of the SYNCSORT step is given below.
Ford [...]30_cf06f0bf2123e.4bc6d35d@shaw.ca 5954 43 45_Re: SQL -904 during EXCHANGE on a clone table11_Chuck Kosin24_chuck.kosin@EXPERIAN.COM31_Thu, 15 Apr 2010 10:04:17 -0400714_UTF-8 Hello,

Maybe this will help. With RUNSTATS, it look like it is possible to invalidate the cache. This is from the manual.

http://publib.boulder.ibm.com/epubs/pdf/dsnugj18.pdf

Invalidating statements in the dynamic statement cache DB2 invalidates statements in the dynamic statement cache when you run
RUNSTATS on objects to which those statements refer. In a data sharing RUNSTATS environment, the relevant statements are also invalidated in the cache of other members in the group. DB2 invalidates the cached statements to ensure that the
next invocations of those statements are fully prepared and that they use the latest access path changes. You can invalidate statements [...]56_1785131416491908.WA.chuck.kosinexperian.com@www.idug.org 5998 61 45_Re: SQL -904 during EXCHANGE on a clone table13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 15 Apr 2010 11:12:28 -0400495_utf-8 I don't think the problem was that there was a statement in the cache, but that there was a statement in the cache THAT WAS IN USE

To get rid of it, the op would have to -term the relevant thread

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 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org [...]58_4440F5DA00E3F3459BBCB97431B91B6611B01861@MAILR004.mail.lan 6060 397 45_Re: SQL -904 during EXCHANGE on a clone table13_Walters, Paul28_Paul.A.Walters@SALLIEMAE.COM31_Thu, 15 Apr 2010 11:49:03 -0400427_us-ascii We had a similar problem with just alters against objects. You can search the list for "How To Flush the Cache ?"

Someone recommend the following (we have not tested since our problem as not reoccurred):
Our SYSDBA turned off the statement cache at the Sub-system (ZPARM CACHEDYN=NO) then we could able to Alter the table, To do the turn off of Statement cache you don't need to recycle the sub-system. [...]70_EBC218E23BF7B5468DC64F2ED8547F427701DFBD35@VREEXMBX01.us.ad.usa-ed.net 6458 32 25_Database size calculation13_Sekhar Mekala23_SEKHAR.MEKALA@GMAIL.COM31_Thu, 15 Apr 2010 14:43:09 -0400287_UTF-8 Hi all,
Is there any way to get the size of a DB2 Database in z/OS environment, without running the STOSPACE Utility or RUNSTATS utility? We have CA Database Analyzer in our environment. Is there any way that I can use Database Analyzer to know the size of a DB2 Database? [...]55_6461412086057150.WA.SEKHAR.MEKALAGMAIL.COM@www.idug.org 6491 86 29_Re: Database size calculation16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Thu, 15 Apr 2010 13:52:48 -0500685_UTF-8 I use liscat, parse the results using rexx, and put that result into an excel spreadsheet. Fast and accurate.

Everything you need is on the db2expert.com web site for free. It's on the downloads page called lcatspce.

Cheers,
Dan

Daniel L Luksetich
IBM Information Champion
IBM Certified Database Administrator - DB2 9 for z/OS
IBM Certified System Administrator - DB2 9 for z/OS
IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database Administration for UNIX, Windows, and OS/2
IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development
IBM Certified Advanced Technical Expert - DB2 Data Replication [...]35_005701cadccc$da5827d0$8f087770$@com 6578 68 52_Re: AW: [DB2-L] [z/OS - DB2 V9] Access Path question18_Satish Srikakulapu26_satish.srikakulapu@APS.COM31_Thu, 15 Apr 2010 15:15:10 -0400626_UTF-8 Hello Walter,

May be I wasn't clear. I wanted to know if anybody experienced what we are experiencing (i.e., performance degradation) after upgrading from DB2 V8 NFM to DB2 V9 CM.

In DB2 V8 NFM, we used to run RUNSTATS with no other special options except TABLE(ALL) INDEX(ALL).

After upgrading to DB2 V9 CM, we ran the same RUNSTATS without any special options. At that point of time, we noticed that accesspaths have gone bad. So, based IBM’s recommendation, we ran the RUNSTATS with KEYCARD in DB2 V9. After rebinding the programs after running the RUNSTATS with KEYCARD, accesspaths were [...]58_7237938303715042.WA.satish.srikakulapuaps.com@www.idug.org 6647 265 46_Re: Tuning high inserts in DB2 DPF environment16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Thu, 15 Apr 2010 14:49:14 -0500447_us-ascii We have used APPEND with DMS managed with many file containers, but this was
not in a DPF environment. Not sure how much different it would be, but we
did get some very nice insert performance.

Cheers,

Dan

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mir, Sally
Sent: Tuesday, April 13, 2010 2:02 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [SPAM] Tuning high inserts in DB2 DPF environment [...]35_005b01cadcd4$bcab7090$360251b0$@com 6913 57 29_Re: Database size calculation20_Kumara Sai Singaraju19_satdb2dba@GMAIL.COM31_Thu, 15 Apr 2010 14:50:18 -0500663_ISO-8859-1 If your DB2 subsystem is collecting reatimestats then column SPACE in
tables systablespacestata/sysindexspacestats will give you the
allocated space for a database.

Thanks
Singaraju

On Thursday, April 15, 2010, Sekhar Mekala wrote:
> Hi all,
> Is there any way to get the size of a DB2 Database in z/OS environment, without running the STOSPACE Utility or RUNSTATS utility? We have CA Database Analyzer in our environment. Is there any way that I can use Database Analyzer to know the size of a DB2 Database?
>
> Thanks in advance!!
>
> Best regards,
> Sekhar Mekala
>
> [...]62_x2p4d222ac61004151250za1b22637redbadc3c6bcae746@mail.gmail.com 6971 925 58_[AD] Virtual Class, DB2 9 for z/OS Database Administration14_John Caccavale18_jcac@THEMISINC.COM31_Thu, 15 Apr 2010 16:39:41 -0400493_UTF-8 Themis is proud to announce our first distance learning opportunity for our DB2 9 for z/OS Database Administration course. Having trouble finding local DB2 DBA training? Are travel budgets non-existent?
Join us for this course from 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_3539602998020923.WA.jcacthemisinc.com@www.idug.org 7897 65 108_FW: Thank You: DB2 10 for z/OS - Helping you improve operational efficiencies and gain competitive advantage12_Weaver, Rick19_Rick_Weaver@BMC.COM31_Thu, 15 Apr 2010 15:00:37 -0600464_us-ascii I got permission from IBM to share this link to the Roger Miller/David Beulke V10 update conducted on Tuesday April 13.

Rick Weaver
DB2 z/OS Product Manager
BMC Software

-----Original Message-----
From: jshear@us.ibm.com [mailto:jshear@us.ibm.com]
Sent: Thursday, April 15, 2010 3:24 PM
To: Weaver, Rick
Subject: Thank You: DB2 10 for z/OS - Helping you improve operational efficiencies and gain competitive advantage [...]69_B04A515BCAE7C34D90FEE204B055E3B25CBD7BEBB5@PHXCCRPRD02.adprod.bmc.com 7963 374 46_Re: Tuning high inserts in DB2 DPF environment12_Phil Gunning19_pkgunning@GMAIL.COM31_Thu, 15 Apr 2010 17:01:05 -0400532_us-ascii As Dan indicated APPEND ON tells DB2 not to search freespace control records
and just insert end of table, I have used this on tables with high volume
inserts to get top insert performance. Downside is cant do an online reorg
if using it. For DPF buffered inserts can help. PG

_____

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Daniel Luksetich
Sent: Thursday, April 15, 2010 3:49 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Tuning high inserts in DB2 DPF environment [...]39_583B53C7427C43519240DFB6AAD27F5F@GTSLT4 8338 97 37_AW: [DB2-L] Database size calculation12_Thomas Weber22_Thomas.Weber@IT.NRW.DE31_Fri, 16 Apr 2010 08:22:30 +0200891_iso-8859-1 Sekhar,

you might query the PDA stats tables as RAVL_STATS_1105 (for r115 or higher). Here is an example:

SUM (A.RAVL_PQTY_USED + A.RAVL_SQTY_USED)*4/1000 AS TS_IN_MB_USED,
SUM (A.RAVL_PQTY + A.RAVL_SQTY)*4/1000 AS TS_IN_MB_ALLOC
FROM PTI.RAVL_STATS_1105 A
INNER JOIN
(SELECT MAX(B.RAVL_TIMESTAMP_D) AS MAX_TS
,B.RAVL_CREATOR
,B.RAVL_OBJECT
,B.RAVL_SYSID
,B.RAVL_TYPE
FROM PTI.RAVL_STATS_1105 B
GROUP BY B.RAVL_CREATOR
,B.RAVL_OBJECT
,B.RAVL_SYSID
,B.RAVL_TYPE) AS I
ON I.RAVL_CREATOR = A.RAVL_CREATOR
AND I.RAVL_OBJECT = A.RAVL_OBJECT
AND I.RAVL_SYSID = A.RAVL_SYSID
AND I.RAVL_TYPE = A.RAVL_TYPE
AND I.MAX_TS = A.RAVL_TIMESTAMP_D
WHERE A.RAVL_TYPE = ? <------------------------------- TS/IX
AND A.RAVL_SYSID = 'your ssid'
AND A.RAVL_CREATOR = 'your dbname'
GROUP BY A.RAVL_CREATOR
WITH UR; [...]72_C129734D873435418B3C3C91E9CBF3A368F44F9D92@LDSXS01EVS.service.lds.nrw.de 8436 114 29_Re: Database size calculation10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 16 Apr 2010 08:57:49 +0200338_US-ASCII Among other methods suggested by other listers there's SMS method (using
SMS REPORT feature), if you've access to SMS menu. You can customize it,
saving the report and you can run even batch. It's quite easy and simple
to use. You can run it batch as well (it was NAVIQUEST feature if I
remember well the name). [...]66_OF3596989D.1F464579-ONC1257707.0023904C-C1257707.0026406F@cesve.it 8551 46 22_Fallback from V8 ENFM?11_Leong, Werv17_werv.leong@HP.COM31_Fri, 16 Apr 2010 08:42:54 +0000400_us-ascii Hi,

We are planning to convert 3 live DB2 systems from V8 CM to V8 ENFM in our monthly maintenance slot. We will of course take DFDSS backups of the BSDS's, Logcopy's and DB2 Catalogs.

In the unfortunate that some time in the near future, we might need to regress back one of the DB2 Catalogs, what actions will we need to do? I must admit it's somewhat thorny issue..... [...]74_EC25E8C4647DAB40AD428F0257B4021D4389009AFA@GVW1347EXA.americas.hpqcorp.net 8598 988 27_Re: DB2 Abnormal Terminaton9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Fri, 16 Apr 2010 14:20:00 +0530516_us-ascii Ok. I have done a COPY on all the objects.

Thanks and Regards,
Srinivas G

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG], On Behalf Of Phil Grainger
Sent: Thursday, April 15, 2010 7:32 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] DB2 Abnormal Terminaton

Glad you are back on-line but please bear in mind that depending on what you did with the CRESTART you may well have invalidated ALL the image copies you have in SYSCOPY and will need now to image copy EVERYTHING [...]69_E5D4741C0D67254EA3601CFEC7810DFA0FB7A6FC87@BLRKECMBX05.ad.infosys.com 9587 120 60_AW: [DB2-L] AW: [DB2-L] [z/OS - DB2 V9] Access Path question35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Fri, 16 Apr 2010 12:43:39 +0200335_iso-8859-1 Hi

I think, everybody has made this experience in a few cases, so do we. Most of the time, access path remains the same or got better, but there were a few queries, which got worse. These were in general static SQL and because we use PLANMGMT EXTENDED (at least in production), we used the rebind switch option. [...]43_DB2-L%201004160643520222.02C3@IDUGDB2-L.ORG 9708 31 29_Re: Database size calculation13_Mick P Graley16_mgraley2@CSC.COM31_Fri, 16 Apr 2010 11:48:16 +0100444_US-ASCII If you just want a one-off quick and dirty total just go into PDF.11
(workplace) and get a list of the data sets for the database:
hlq.DSNDBD.database.*
Then select view, total. This will give you the overall number of tracks
allocated. If these are on 3390 DASD then multiply tracks by 48 to get the
number of kilobytes. I think you need to be on at least z/OS 1.9 (maybe
1.7) for the view/total option though. [...]65_OF768BE4D1.1FAE21B3-ON80257707.003ABD5A-80257707.003B59CA@csc.com 9740 187 72_AW: [DB2-L] AW: [DB2-L] AW: [DB2-L] [z/OS - DB2 V9] Access Path question12_Peter, Georg15_G.Peter@DZBW.DE31_Fri, 16 Apr 2010 13:05:02 +0200725_iso-8859-1 ....KEXCARD.....

Walter,

do you mean KEYCARD ?

Just kidding ;-)) See you in Cologne next Monday......

Any info that anybody can provide on the above would be greatly appreciated.

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
---------------------------------------------------------------------- [...]58_99E58D5AC1BE6D4CBF492AF72B942C3D061ADBFA@dzstus010.dzbw.de 9928 28 50_IDENTITY vs SEQUENCE: Is this a BETA/VHS scenario?12_George White25_george_white@VANGUARD.COM31_Fri, 16 Apr 2010 08:37:33 -0400438_UTF-8 I understand the overall pros and cons of IDENTITY & SEQUENCE. Other than the standard 'use SEQUENCE when several related tables require a unique value across them' or 'use SEQUENCE when more than one sequential number is required within a table', etc..

My question is twofold: (1) is there any notion that one will fade away in use or popularity? (2) are there profund performance/maintenance issues I am not aware of? [...]56_1824592990510357.WA.georgewhitevanguard.com@www.idug.org 9957 34 51_Thinking of voting for the IDUG board of directors?13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 16 Apr 2010 09:44:24 -0400367_utf-8 If, like me, you were uneasy about voting for the proposed IDUG board because of the need to sign in first, I noticed today that the voting page (at http://www.idug.org/top-spotlight/idug-2010-2011-board-of-directors.html) now says "Since voting is for IDUG Members, you must be signed in, but no attempt is made to associate names with individual votes." [...]58_4440F5DA00E3F3459BBCB97431B91B6611B018B0@MAILR004.mail.lan 9992 51 54_Re: IDENTITY vs SEQUENCE: Is this a BETA/VHS scenario?13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 16 Apr 2010 09:45:53 -0400336_utf-8 People have certainly hit problems in moving tables with IDENTITY columns between subsystems. If you have to unload/reload data and you specified GENERATED ALWAYS, you are in for some real fun

On the other hand, SEQUENCES rely on people using them properly - but do make management of the underlying data much simpler [...]58_4440F5DA00E3F3459BBCB97431B91B6611B018B1@MAILR004.mail.lan 10044 59 54_Re: IDENTITY vs SEQUENCE: Is this a BETA/VHS scenario?12_Rao, Diwakar19_Diwakar.Rao@FMR.COM31_Fri, 16 Apr 2010 13:36:47 -0400557_us-ascii Can NOT create synonyms of SEQUENCES.

If you host >1 application/test DB2 environments in a single DB2
subsystem you might relay on synonyms to migrate your code between
environments.

Diwakar Rao | FIDELITY INVESTMENTS
TWO CONTRA WAY, MERRIMACK, NH, 03054,
603-791-3319

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of George White
Sent: Friday, April 16, 2010 8:38 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] IDENTITY vs SEQUENCE: Is this a BETA/VHS scenario? [...]67_C40E7C2C86F2544E8F6A4E2C3845FD940FC6AC8A@MSGBOSCLE2WIN.DMN1.FMR.COM 10104 227 54_Re: IDENTITY vs SEQUENCE: Is this a BETA/VHS scenario?11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 16 Apr 2010 14:28:37 -0500573_iso-8859-1 Just to elaboarte on Phils' point about:

>> SEQUENCES rely on people using them properly

(1) If an application ends up accidentally looping (we have never seen code do that, right!), the numbers can be depleted quite quickly - about 1 million a minute in my test a few years ago. This may not be a big deal, but if your designers try to size the columns too small, you can reach the end and then worry about recycling. This would generally be slower due to the actual inserts which must take place when using Identity cols. Looping will occur [...]43_COL104-W3158598FFB36B9D0F9A3EC980E0@phx.gbl 10332 1195 24_Re: [z/OS] DSNTEP2 Input14_Peter Schwarcz23_schwarcz@BIGPOND.NET.AU31_Sat, 17 Apr 2010 12:44:57 +1000488_us-ascii The following SQL embedded in DSNTIAUL will generate 80 byte records ready
tol feed into TEP2 without having to post process the output.

WITH TAB ( TABNAME, TYPE, DBNAME )

AS ( SELECT STRIP(CREATOR)||'.'|| STRIP(NAME)

, TB.TYPE

, TB.DBNAME

FROM SYSIBM.SYSTABLES TB

WHERE CREATOR LIKE 'SYS%' )

SELECT CAST(CMD AS CHAR(80)) FROM (

SELECT 1 AS SEQ, TABNAME,

'GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE ' CMD [...]38_008501caddd7$f7c35040$e749f0c0$@net.au 11528 137 42_Re: Lack of DBA's harms the market for DB29_DB2DBAzOS21_bala.db2dba@GMAIL.COM31_Sat, 17 Apr 2010 10:24:56 +0530643_ISO-8859-1 "Even offshoring is not an option because all you find over there is
generally junior level script monkey talent."

You may have to choose the offshore company better. When you want to get
done things cheaply, you get what you pay for.
And, sometimes the DBAs leave the companies (its totally different in the
offshore/bestshore part of world !). And, the offshore
companies have "strategies" that make them hire younger or cheaper labor,
over which the clients or service seeker has no control.
And, at some places, when the support is moved several thousands miles away,
'monkey talent' is what [...]62_v2t35e6ff9a1004162154s572458dbwb005e442c8cd8350@mail.gmail.com 11666 69 42_Re: Lack of DBA's harms the market for DB214_Bernd Oppolzer26_bernd.oppolzer@T-ONLINE.DE31_Sat, 17 Apr 2010 11:15:33 +0200551_ISO-8859-1 Another recent story regarding offshoring:

to get offshoring working, a three letter company well known to us all
was chosen to control the offshore workers and to hire a specialist who
should translate our (the customer's) requirements to the offshore workers
and control their work. The specialist was required to have deep
knowledge about
all the topics involved, that is, PL/1, C, DB2, Java, maybe Job Control,
REXX etc.
and to understand our requirements. The contract was signed for five
years !!! [...]28_4BC97C35.2070409@t-online.de 11736 111 42_Re: Lack of DBA's harms the market for DB235_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Sat, 17 Apr 2010 11:58:11 -0400794_iso-8859-1 I don't find this scenario surprising at all....

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

Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at:
www.responsivesystems.com/Movie1

tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: "Bernd Oppolzer"
Newsgroups: bit.listserv.db2-l
To:
Sent: Saturday, April 17, 2010 5:15 AM
Subject: Re: [DB2-L] Lack of DBA's harms the market for DB2 [...]46_F3B03148BF214CBE88925D2EDFFF8133@DellNotebook3 11848 228 33_[AD] Listdef and Template for DB213_Edward Benoit29_db2tech@RECOVERYKNOWLEDGE.COM31_Sat, 17 Apr 2010 09:19:06 -0700685_iso-8859-1 Listdef and Template features of DB2 z/OS are now automated. LISTDEF MANAGER forDB2automates the LISTDEF (Grouping of DB2 Objects) and TEMPLATE (Dynamic Allocation) features of DB2 z/Os. The software product stores all listdef, template and group entries in DB2 tables for better security, management and automation. The management of these entries (add, change, delete and view) are all ISPF Panel driven. The DB2 DBA and DB2 System organization use this process to automate the generation of DB2 utilities including the recovery process. The Automation of Listdef feature includes: 1. Recover Forward TORBAusing Archive Log Quiesce command [...]46_527505.33019.qm@web1202.biz.mail.gq1.yahoo.com 12077 56 23_Re: DB2 Unload and Load0_22_DB2information@AOL.COM29_Sat, 17 Apr 2010 12:27:52 EDT606_US-ASCII MaryJane,
Take a look at _www.recoverykowledge.com_ (http://www.recoverykowledge.com)
Unloading from one DB2 and Loading to another DB2 is all automated.

Ed.

_____________________________________________________________________

* 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
_____________________________________________________________________ [...]30_e04b.4e71e3a0.38fb3b88@aol.com 12134 146 24_Re: [z/OS] DSNTEP2 Input14_Peter Schwarcz23_schwarcz@BIGPOND.NET.AU31_Sat, 17 Apr 2010 19:12:47 -0400647_UTF-8 The following SQL embedded in DSNTIAUL will generate 80 byte records ready to feed into TEP2 without having to post process the output.

WITH TAB ( TABNAME, TYPE, DBNAME )
AS ( SELECT STRIP(CREATOR)||'.'|| STRIP(NAME)
, TB.TYPE
, TB.DBNAME
FROM SYSIBM.SYSTABLES TB
WHERE CREATOR LIKE 'SYS%' )

SELECT CAST(CMD AS CHAR(80)) FROM (

SELECT 1 AS SEQ, TABNAME,
'GRANT SELECT, UPDATE, DELETE, INSERT ON TABLE ' CMD
FROM TAB WHERE TAB.TYPE = 'V'
UNION ALL
SELECT 2 AS SEQ, TABNAME, TABNAME AS CMD
FROM TAB WHERE TAB.TYPE = 'V'
UNION ALL
SELECT 3 AS SEQ, TABNAME, 'TO ''SOMEUSER''' || ';' [...]55_7828903826043750.WA.schwarczbigpond.net.au@www.idug.org 12281 76 59_The effects of RI - wow - EXPLAIN doesn't reveal some stuff12_Roy Reynolds17_royr@BERKELEY.EDU31_Sun, 18 Apr 2010 11:46:15 -0400595_UTF-8 All,
This is a open THANK YOU to DB2 support for reminding me, as Roger says so often, to RTFW.

We had a very slow SQL DELETE according to QueryMon (BTW, a very good IBM tool). EXPLAIN, OSC, Data Studio all showed
the index was perfect for the predicate, pointing to one row. QueryMon showed a suspiciously high amount of cpu used for this
DELETE.
After looking a bit deeper I finally noticed there were child and grandchild RI tables. They specified DELETE CASCADE ENFORCED.
I wasn't at this shop when these tables were defined and, frankly, I haven't reviewed [...]49_6143607663056777.WA.royrberkeley.edu@www.idug.org 12358 92 63_Re: The effects of RI - wow - EXPLAIN doesn't reveal some stuff10_Todd Burch17_toddburch@MAC.COM31_Sun, 18 Apr 2010 11:10:37 -0500497_US-ASCII Thanks for the kudos to IBM Support Roy. Wish I could say I was
involved with the explanation!! Your post educated me on this. So,
yes, it was worth it to post this.

The guys and gals who work the Access Path Performance / Query
Optimization queue, and the developers behind them, are truly to be
admired for all the minutia they have to keep straight - and it seems
all that minutia is forever a moving target. When I grow up, I want
to work on that queue. [...]44_AF9A4C88-F2FD-41A5-B90A-95D106D05D2F@mac.com 12451 24 23_Re: DB2 Unload and Load18_Mary-Jane Mulligan31_MaryJane.Mulligan@SENTENIAL.COM31_Mon, 19 Apr 2010 05:21:28 -0400668_- Thanks Ed, will have a read. Got the samples running myself. So that's progress!

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* 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!
_____________________________________________________________________ [...]63_3627352616002780.WA.MaryJane.MulliganSentenial.com@www.idug.org 12476 86 33_LOBs - (was IBM Unload and Limbo)10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 19 Apr 2010 13:21:01 +0200350_US-ASCII Just discussed about joy of using LOBs....., I've read Willie Favero's
'Top ten' list for new DB2 V10. A lot of new things but among others I
read SPT01 (some parts) will use LOBs.

Should l I start to worry about it ?

Max Scarpa
Certified 'funk' DB2 sysprog
Certified 'This won't hurt, I promise.' DB2 sysprog [...]66_OF83A62BA9.3064C989-ONC125770A.00372BDE-C125770A.003E590F@cesve.it 12563 298 37_Re: LOBs - (was IBM Unload and Limbo)12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 19 Apr 2010 17:16:14 +0300441_UTF-8 Continue (to worry …)

Isaac Yassin

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Max Scarpa
Sent: Monday, April 19, 2010 2:21 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] LOBs - (was IBM Unload and Limbo)

Just discussed about joy of using LOBs....., I've read Willie Favero's 'Top ten' list for new DB2 V10. A lot of new things but among others I read SPT01 (some parts) will use LOBs. [...]35_001f01cadfca$df2e1ed0$9d8a5c70$@net 12862 92 52_Re: AW: [DB2-L] [z/OS - DB2 V9] Access Path question15_Patrick Bossman25_patrick.bossman@GMAIL.COM31_Mon, 19 Apr 2010 10:32:45 -0400641_UTF-8 Hello Satish,
I haven't seen your ETR/PMR and there aren't any specifics provided here. You can ask this question about any release and any relational database.

"Has any customer experienced query performance regression when they migrated from X to Y?" You will get the same answer.

Some yes, some no.

The vast majority of query performance regressions we observe are for queries where the optimizer never accurately estimated the cost of the query. This can happen for a variety of reasons but the most common is - the optimizer cannot accurately estimate the qualified rows in some important context. So [...]57_6162951059499131.WA.patrick.bossmangmail.com@www.idug.org 12955 343 37_Re: LOBs - (was IBM Unload and Limbo)13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Mon, 19 Apr 2010 10:38:01 -0400471_utf-8 But how often have you needed to unload/reload SPT01??

Or reorg it for that matter (REORG wasn’t even allowed until relatively recently)

Copy (of course) is a different matter – but copying LOBs (of “sensible” sizes) has never been a problem

The one thing we ought to be able to deduce is that if IBM are putting LOBs in the catalog/directory, then their utility support will HAVE to be 100% bullet proof (or pretty close to 100%) [...]60_4440F5DA00E3F3459BBCB97431B91B66128752F686@MAILR004.mail.lan 13299 692 37_Re: LOBs - (was IBM Unload and Limbo)14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 19 Apr 2010 10:51:37 -0400326_us-ascii I believe the relevant question in this case is "Small LOB, or regular LOB?" With V10 (or is it V9?), LOBs of size <32K can be included in the base tablespace. We may presume that such LOBs-in-name-only will be easier to manage than the original type.

Does SPT01 get an auxiliary table? Does anyone know? [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4683B@MAIL02.fisalan.nycnet 13992 95 37_Re: LOBs - (was IBM Unload and Limbo)10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 19 Apr 2010 16:52:47 +0200441_US-ASCII The problem is this time it's catalog/directory.......the DB2 'heart' and
this scares me a litlle. :-(. V10 sounds very interesting anyway,
expecially about improvement in performances Anyway we'll see.

Thank you for your hint........

Max Scarpa
Certi(horri)fied DB2 sysprog

Kutta-Jukowski corollary: Smile now, tomorrow there'll be a worse DB2
access path (from latin motto 'Carpe DB2 Diem'). [...]66_OF23E305FD.A260FCB2-ONC125770A.005022A7-C125770A.0051BC1D@cesve.it 14088 753 37_Re: LOBs - (was IBM Unload and Limbo)13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Mon, 19 Apr 2010 11:12:09 -0400638_us-ascii I'm guessing these are real LOBs not the in-line ones - otherwise there is no real benefit to SPT01
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 - Europe, 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: 19 April 2010 15:52
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] LOBs - (was IBM Unload and Limbo) [...]60_4440F5DA00E3F3459BBCB97431B91B66128752F693@MAILR004.mail.lan 14842 226 37_Re: LOBs - (was IBM Unload and Limbo)7_Ed Long19_rdhm99a@PRODIGY.NET31_Mon, 19 Apr 2010 08:57:53 -0700357_utf-8 As a former employer was fond of saying, Past performance does not guarantee future results.
In the case of LOBS this aphorism may be a harbinger of good things since to date LOB support has consistently been 11th in the list of the top 10 priorities.

Edward Long

--- On Mon, 4/19/10, Isaac Yassin wrote: [...]42_34815.37946.qm@web80207.mail.mud.yahoo.com 15069 98 37_Re: LOBs - (was IBM Unload and Limbo)10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 19 Apr 2010 18:17:29 +0200466_US-ASCII I'm not worried about utilities, I'm worried (from personal experience
that hasn't any statistical value) about the 'historical' number of PTFs
involving LOBs (I had to apply500/600+ PTFs to use them) ,the performance
problem we had in a recent past (and from what I read there still are) and
the relative complexity of LOBs (I think in V10 they're real LOBs as
well), considering they came with V6 ie. four version ago (GA: june 1999). [...]66_OF7F5136FB.9E7FE61C-ONC125770A.0051E8C8-C125770A.00597D57@cesve.it 15168 34 17_Database Decision18_valeriet6@juno.com18_valeriet6@JUNO.COM29_Mon, 19 Apr 2010 16:27:27 GMT919_windows-1252 Does anyone have a decision process (e.g., a set of questions or a matrix) they go through for new applications to decide what database should be used (e.g., DB2 z/OS, Oracle, SQL Server)?

Thanks.

____________________________________________________________
Penny Stock Jumping 2000%
Sign up to the #1 voted penny stock newsletter for free today!
http://thirdpartyoffers.juno.com/TGL3141/4bcc84b649f72cd4cest01vuc

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* 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 [...]45_20100419.122727.4260.1@webmail24.vgs.untd.com 15203 101 37_Re: LOBs - (was IBM Unload and Limbo)10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 19 Apr 2010 18:34:17 +0200449_US-ASCII IDUG DB2-L wrote on 19/04/2010 17.57.53:

> As a former employer was fond of saying, Past performance does not
> guarantee future results.

Absolutely true, but I don't know if we can consider it 'past'. Anyway a
old 'piece' inserted in a new (DB2 cat/dir in this case) structure to
build something different doesn't prevent problems. See Ariane V or recent
windows bug, 17 years old :-). [...]66_OFA35E2BDF.875D1044-ONC125770A.0059C52D-C125770A.005B0727@cesve.it 15305 108 21_Re: Database Decision14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 19 Apr 2010 13:38:25 -0400531_us-ascii Valerie,

Different types of applications require different consideration and different processes. In my experience:

1) For Vendor applications, off the shelf or for modification (e.g. Peoplesoft); it's generally been best to go with the platform the software-package vendor recommends. This avoids all manner of conversion problems and "We didn't know the DBMS would do that!" -- I've seen two such (convert the software to run on a new platform) implementations fail, one to the tune of about US$140M. [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4683F@MAIL02.fisalan.nycnet 15414 66 17_Database Decision14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 19 Apr 2010 14:03:51 -0400746_us-ascii On reflection, I've added some edits to my original post and posted it on my blog, for those who are interested. The link is:
http://it.toolbox.com/blogs/relearning-programming/on-selecting-a-dbms-for-your-project-38162
or
http://bit.ly/al7Z2w
for those whose browsers don't like long links.

-ps

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of valeriet6@juno.com
Sent: Monday, April 19, 2010 12:27 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Database Decision

Does anyone have a decision process (e.g., a set of questions or a matrix) they go through for new applications to decide what database should be used (e.g., DB2 z/OS, Oracle, SQL Server)? [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46841@MAIL02.fisalan.nycnet 15481 63 52_Re: AW: [DB2-L] [z/OS - DB2 V9] Access Path question11_Sameer Rana21_sameerdrana@GMAIL.COM31_Mon, 19 Apr 2010 15:36:20 -0400624_UTF-8 Hello Pat ,

Thanks for providing a vivid explanation which will minimize instances where DBAs scratch their heads wondering why DB2 made that choice ( after an upgrade )!
Anyways, in extension to this question, having sophisticated products such as Statistics Advisor ( and others from 3rd party vendors ) can aid you with RUNSTATS control card to improve query responses only for the ones that those products are analyzing.
However it doesn't necessarily factor other workloads ( which could be both dynamic and static ).
I do believe there is a means to capture Production workload and then run [...]53_1337269797323968.WA.sameerdranagmail.com@www.idug.org 15545 106 52_Re: AW: [DB2-L] [z/OS - DB2 V9] Access Path question14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 19 Apr 2010 15:49:45 -0400582_us-ascii Sameer,
I'm not Pat but I'd like to take a shot at answering one of your questions.

Data Warehouses will frequently have a small number of queries that are constantly being run (with data values changing every time, depending on the user and the reporting period). You ought to make sure the FREQVAL statistics are up to date on these queries. For example, if a query contains predicates to allow Index X to provide an exact match on up to four columns, you ought to have the first four columns of X specified something like this:
FREQVAL NUMCOLS 1 COUNT [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46846@MAIL02.fisalan.nycnet 15652 38 7_NUMLKTS4_Anil21_alisha_kale@YAHOO.COM31_Mon, 19 Apr 2010 18:38:49 -0400514_UTF-8 hi all !

This might be something basic that I am overseeing. May be just a long day. Not sure. Please help me understand why don't I see the LOCK ESCALATION.

I have DB2 V9 on z/os, and NUMLKTS set to 1000.

So if I have a COBOL program that performs 5000 INSERTS on a table, and NO COMMIT DONE. I would expect a LOCK ESCALATION. I mean since (5000 Inserts) its more than NUMLKTS (LOCKS PER TABLE as 1000), I expect to see LOCK ESCALATION. But I don't see it. What am I missing here. [...]52_8266683679980866.WA.alishakaleyahoo.com@www.idug.org 15691 79 11_Re: NUMLKTS9_Mike Bell21_mbell11a1@VERIZON.NET31_Mon, 19 Apr 2010 17:55:28 -0500329_US-ASCII Everyone will want to answer this - it is fairly simple question - how many
rows per page and are the column values for the clustering index similar to
the input sequence.

Rule # 1 - no locks for indexes since type 2 indexes.
Rule # 2 - locksize page means that 100 rows in one page means one lock. [...]43_C571CA8FE3A74AFEADBBD33FE92E922B@mikelaptop 15771 31 11_Re: NUMLKTS4_Anil21_alisha_kale@YAHOO.COM31_Mon, 19 Apr 2010 19:04:54 -0400840_UTF-8 Yes, the LOCKSIZE here is ROW. I should have given that input with my original question:

DB2 V9 on z/os, NUMLKTS set to 1000, LOCKSIZE ROW

I expect to see LOCK ESCALATION after 5000 INSERTS and no commits done. What say ??

Anil

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* 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!
_____________________________________________________________________ [...]52_3008323782309994.WA.alishakaleyahoo.com@www.idug.org 15803 233 83_DB2 V8, z/os 1.10, oracle j developer tool using top link with type 4 jdbc drivers.16_Proctor, William25_William.Proctor@TGSLC.ORG31_Mon, 19 Apr 2010 18:35:46 -0500603_us-ascii Thanks for all replies in advance. Any input will be appreciated. We have a database that is being used by several applications with 5 project being developed against it. All of the projects are using the java developer tools except one which is using Informatica. The tables are all tied together using generated object id's that have no relation to the business data. There are numerous parent child relationships. We are debating on the method of assigning the object id's which are integer types. The java developers are say that for performance they have to use a table that contains [...]65_550D0016ABCA22488AD932ACA94AC56831B7D45C64@CORPEXCHMBOX.tgslc.org 16037 74 21_Re: Database Decision14_Chris Zampogna34_Chris.Zampogna@STERLINGSAVINGS.COM31_Mon, 19 Apr 2010 16:46:25 -0700668_us-ascii If you (or someone in your organization) have a Gartner subscription, they have some great tools to help with this.

Best Regards,

Chris Zampogna, PMP(r)
Program Manager, Enterprise Data Warehouse
Sterling Savings Bank

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of valeriet6@juno.com
Sent: Monday, April 19, 2010 9:27 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Database Decision

Does anyone have a decision process (e.g., a set of questions or a matrix) they go through for new applications to decide what database should be used (e.g., DB2 z/OS, Oracle, SQL Server)? [...]70_439D775FB76C6C41A8FC90D4382D84BE792AF959A8@SSBMAIL.sterlingsavings.com 16112 47 30_Capacity Planning for Test Env4_Anil21_alisha_kale@YAHOO.COM31_Mon, 19 Apr 2010 19:59:24 -0400612_UTF-8 Hi All !

I read a lot about capacity planning for your database environment. While nobody denies that capacity planning is all about being proactive, the common view is that its all applicable to the production environment. Which is supported by most documents I read. They are all about capacity planning int the production environment.
As it is, capacity planning is an art and science. And it gets even challenging when you talk about the test environment.
I mean its relatively easy to make case by citing your jobs that fails with an out-of-space condition and justify more space in [...]52_5299607002176385.WA.alishakaleyahoo.com@www.idug.org 16160 47 39_Capacity Planning for Test Environments4_Anil21_alisha_kale@YAHOO.COM31_Mon, 19 Apr 2010 19:48:27 -0400611_UTF-8 Hi All !

I read a lot about capacity planning for your database environment. While nobody denies that capacity planning is all about being proactive, the common view is that its all applicable to the production environment. Which is supported by most documents I read. They are all about capacity planning in the production environment.
As it is, capacity planning is an art and science. And it gets even challenging when you talk about the test environment.
I mean its relatively easy to make case by citing your jobs that fails with an out-of-space condition and justify more space in [...]52_8754644776864414.WA.alishakaleyahoo.com@www.idug.org 16208 64 11_Re: NUMLKTS9_Mike Bell21_mbell11a1@VERIZON.NET31_Mon, 19 Apr 2010 20:12:49 -0500446_US-ASCII the only idea I have right now is look for a LOCK TABLE IN EXCLUSIVE. That
will give you one lock and everything will run with no further locks. After
that you need to start looking a lock traces and that is something to do in
a quiet time because it will have a fair amount of overhead. The other
alternatives would involve opening an ETR with IBM because this could
require some deep searches through lots of data. [...]43_6F8EEF99ADBF41F49A6A10D3DE994A68@mikelaptop 16273 84 38_DB2 REXX; help with DSNLOAD allocation10_Tom Glaser25_tom_glaser@MASTERCARD.COM31_Tue, 20 Apr 2010 00:16:55 -0400324_UTF-8 Hi, I'm trying to run dsntep2 using rexx. I don't have access to using steplib, so I'm trying to allocate dsnload. When executing dsntep2, I'm specifying the load lib, which is in a different load library. However, when I run it, I'm getting the message that dsn cannot be found. So....I'm doing something wrong: [...]56_7150303803775115.WA.tomglasermastercard.com@www.idug.org 16358 117 42_Re: DB2 REXX; help with DSNLOAD allocation14_Fazio, Richard21_RFAZIO@TRANSUNION.COM31_Tue, 20 Apr 2010 00:04:36 -0500338_us-ascii Yikes! Any reason why you are not using the Rexx DB2 interface? You
can call dsntep2 like this, but it would be better to use something a
bit more native. I did a presentation on this at IDUG back in 2005, but
this all still works the same. I can PM you the presentation and some
code samples if you would like. [...]70_0CF187805D9C924DAFF59A877104C36A04523AA5@CHI4EVS04.corp.transunion.com 16476 78 11_Re: NUMLKTS17_Tunen, Marcel van31_Marcel.van-Tunen@CORUSGROUP.COM31_Tue, 20 Apr 2010 08:13:32 +0200443_us-ascii What's your value for LOCKMAX (tablespace property)?
If it is zero no escalation will occur.

Marcel

-----Original Message-----
From: Anil [mailto:alisha_kale@YAHOO.COM]
Sent: Tuesday, April 20, 2010 12:39 AM
Subject: NUMLKTS

hi all !

This might be something basic that I am overseeing. May be just a long
day. Not sure. Please help me understand why don't I see the LOCK
ESCALATION. [...]77_95955CB1A0786744B601CEA40207647F017FF3EB@IJMEXCMAIL02.ce.altis.corusgroup.com 16555 220 26_Explain in QMF for windows17_Tunen, Marcel van31_Marcel.van-Tunen@CORUSGROUP.COM31_Tue, 20 Apr 2010 09:35:24 +0200460_us-ascii Hello listies,

I'm wondering if anyone knows a gracefull way to do an explain in QMF
for windows.

We now use a procedure that depends on the user preceding his or her
query with; explain all for
Then the user runs our procedure which;

Runs the query
Runs a query on the plan_table
Displays the report.

QMF now opens 3 windows, gives a message 'the database will be updated'
then displays the report. [...]77_95955CB1A0786744B601CEA40207647F017FF3EC@IJMEXCMAIL02.ce.altis.corusgroup.com 16776 100 42_Re: DB2 REXX; help with DSNLOAD allocation14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 20 Apr 2010 21:19:06 +1000553_US-ASCII Apart from Faz's comments (use DSNREXX, unless you like decoding
SYSPRINT), the command you are looking for is TSOLIB - used from
READY mode. However it doesn't take effect until the REXX terminates (a
"feature" of the REXX environment.)

TSO, when it wants to execute a command (such as DSN) does not use
ISPF allocated datasets (only ISPEXEC SELECTed commands work that
way.) TSO uses the standard search order for a LOAD - that is a local task
library (which is what TSOLIB sets up), STELIB, Linklist, LPA, etc. [...]49_4BCE1A4A.13451.5CF3131F@jacampbell.acslink.net.au 16877 139 42_Re: DB2 REXX; help with DSNLOAD allocation12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Tue, 20 Apr 2010 09:10:20 -0500339_us-ascii Tom:

We have an unfortunate amount of experience here. DSNREXX does not support
LIBDEF. As such you have to do a trick to allocate DSNLOAD if it is not in
your standard pathing. Most people will not accept additional setup steps
when trying to run DSNREXX. So this is a very site dependant set of
answers. [...]35_001901cae093$3684fd20$a38ef760$@com 17017 79 36_DB2 Security Review Step 1: User IDs11_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 20 Apr 2010 10:39:55 -0400599_UTF-8 I have a new post on my blog entitled "DB2 Security Review Step 1: User IDs"

"Security improvements within DB2 make it essential to do a security review of old existing and new practices and applications that are executing in your environment. While helping to review a system recently, several findings highlighted many interesting items from previous administrators, applications and associated vendors. Since this was an internal audit, the issues were handled inside the DBA department."

Please visit my blog , www.davebeulke.com to read the post in its entirity.

Dave51_6784654357771702.WA.davedavebeulke.com@www.idug.org 17097 163 42_Re: DB2 REXX; help with DSNLOAD allocation13_Walters, Paul28_Paul.A.Walters@SALLIEMAE.COM31_Tue, 20 Apr 2010 10:49:05 -0400522_us-ascii We had a similar problem - We wanted to invoke SQL outside of spufi quickly.

We found HTTP://WWW.GSEUKDB2.ORG.UK/ SOURCE LOCATION FOR EDBAB016 AND EDBAB017

These programs could easily be added to a REXX

XPARMS = SEPARATOR','COLUMNS','LAYOUT','PAGEL','NUMR','NUME

/* PLACE THE RUN AND END COMMANDS ON THE DATA STACK AND EXECUTE DSN */
QUEUE "RUN PLAN(EDBAB017) PROGRAM(EDBAB016)
LIB('DA.DB2.DARWIN.LOAD')
PARM('"XPARMS"')"
QUEUE "END"
/* PARM(' Y C 20 9999 00')" */ [...]70_EBC218E23BF7B5468DC64F2ED8547F427701DFBD60@VREEXMBX01.us.ad.usa-ed.net 17261 291 45_V9 CM Migration, strange SQL access path / V815_nguyen duc tuan17_ndt.db2@GMAIL.COM31_Tue, 20 Apr 2010 16:56:53 +0200849_ISO-8859-1 Dear all,

We are validating the migration to V9 CM and a developer submits to me a
difference of access path compared to V8.

The query is : (just retain the last WHERE clause)

SELECT (...)
FROM pdb2I.rp_cev_02 cev
JOIN pdb2I.pp_cev_typ_01 typ
ON typ.cev_typ_id = cev.cev_typ_id
JOIN pdb2I.pp_cev_exe_sec exe
ON exe.cev_ct = cev.cev_ct
LEFT JOIN (SELECT a.cev_ct,
a.cev_exe_opt_ct,
MIN(b.cev_acr_itr_day_ct) AS pcd_acr_itr_day_ct
FROM pdb2I.rp_rlt_cev_opt_01 a
JOIN pdb2I.rp_cev_pcd_sec_02 b
ON a.cev_ct = b.cev_ct
AND a.cev_pcd_opt_ct = b.cev_pcd_opt_ct
AND b.cev_acr_itr_day_ct <> 0
WHERE a.cev_ct = 2662480
GROUP BY a.cev_ct,
a.cev_exe_opt_ct) pcd
ON exe.cev_ct = pcd.cev_ct
AND exe.cev_exe_opt_ct = pcd.cev_exe_opt_ct
WHERE cev.cev_ct = 2662480
[...]62_y2lfe4fdff51004200756j788e192du40817faf0b9e57e0@mail.gmail.com 17553 27 10_BMC Unload14_Vidya Attuluri27_vidya.attuluri@MARRIOTT.COM31_Tue, 20 Apr 2010 11:18:38 -0400637_UTF-8 Does the BMC Unload supports CTEs with DIRECT NO?

Regards
Vidya

_____________________________________________________________________

* 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
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________ [...]59_2896419599928995.WA.vidya.attulurimarriott.com@www.idug.org 17581 88 49_Re: V9 CM Migration, strange SQL access path / V811_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 20 Apr 2010 17:22:29 +0200506_ISO-8859-1 It looks like you have different optimizer code at work! The optimizer is
changed between releases and so I am not suprised here. Best bet is to get
some fresh new stats with all new options on these tables and then explain
again and see if helps....

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strae 5
40470 Dsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
http://www.seg.de [...]64_OF4416258B.F7134384-ONC125770B.005444A6-C125770B.005474C6@seg.de 17670 207 17_Schema Processor?13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Tue, 20 Apr 2010 11:25:49 -0400485_utf-8 I'm curious how many shops use or even install the z/OS DB2 Schema
Processor.

This is the program DSNHSP which can be installed from SDSNSAMP member
DSNTEJ1S.
The way I see it, the Schema Processor processes a subset of SQL DDL to
cause z/OS to behave more like LUW. For example, a Primary Key clause on
a table causes an index to be created. Also, it enables the CREATE
SCHEMA statement - which on z, outside of the Schema Processor is
invalid. [...]70_E814208E0983274198870632E8697D5B0E3B53C6@nasa-dtw-ex001.nasa.cpwr.corp 17878 46 14_Re: BMC Unload13_Bright, Randy20_Randy_Bright@BMC.COM31_Tue, 20 Apr 2010 10:51:29 -0500310_utf-8 Unload Plus V9.2 (and later) does support Common Table Expressions in DIRECT NO mode.

Please contact me off-list if you want some more information. Or, if you are experiencing a problem attempting to use CTEs, open a problem ticket with our Support team and they will be happy to assist you. [...]69_476996CBBE9AF14285E09E63C370072A13EDAE758E@PHXCCRPRD01.adprod.bmc.com 17925 570 49_Re: V9 CM Migration, strange SQL access path / V818_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM31_Tue, 20 Apr 2010 09:44:38 -0700515_us-ascii Hi,

What kind of RUNSTATS do you run? We had a similar kind of issue in V9 CM.

Do you have Data Studio owned by IBM? If not, this is a free tool that you can download from the internet. This gives you Stats Advisor that is quite helpful. We used this tool identify what other stats were needed and ran the same query again after running the RUNSTATS with new options (eg. HISTOGRAM stats and NUMQUANTILES). After running the RUNSTATS, the accesspath seemed to be same as what was in V8. [...]55_D42E68651088914B9B327A4EEA8433ACA6CCF22A@VM251.apsc.com 18496 169 21_Re: Schema Processor?12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 20 Apr 2010 11:32:39 -0700660_utf-8 I've never used it at any shop nor seen it used. Myron ________________________________ From: "Seibert, Dave" To: DB2-L@IDUGDB2-L.ORG Sent: Tue, April 20, 2010 11:25:49 AM Subject: [DB2-L] Schema Processor? I’m curious how many shops use or even installthez/OS DB2 Schema Processor. This is the program DSNHSP which can be installed fromSDSNSAMP memberDSNTEJ1S. Theway I see it, theSchema Processor processes a subset of SQL DDL to cause z/OS to behave more like LUW. For example, a Primary Key clause on a table causes an index to be created. Also, it enables the CREATE SCHEMA statement–which on z, outside of the [...]44_512458.29561.qm@web112119.mail.gq1.yahoo.com 18666 150 87_Re: DB2 V8, z/os 1.10, oracle j developer tool using top link with type 4 jdbc drivers.12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 20 Apr 2010 11:40:05 -0700568_utf-8 Our java developers use Sequence numbers. They used to use Identity columns but we switched over to Sequence numbers once they became available. They use tools such as toplink, etc. as well as many others with no performance issues at all on the sequence numbers. We used a table with an identity column and a sp for several apps but others didn't want this and wanted the identity columns in the table. This caused no end of troubles copying the data. That's why we forced the switch to sequence numbers. Since the switch it's been the best of both worlds [...]44_527465.35075.qm@web112110.mail.gq1.yahoo.com 18817 73 52_Re: AW: [DB2-L] [z/OS - DB2 V9] Access Path question15_Patrick Bossman25_patrick.bossman@GMAIL.COM31_Tue, 20 Apr 2010 16:10:03 -0400453_UTF-8 Hello,
Let me unpack good and bad things with your proposed approach:
TABLE ( ALL ) INDEX ( ALL ) KEYCARD
FREQVAL NUMCOLS 1 COUNT 10
FREQVAL NUMCOLS 2 COUNT 10
FREQVAL NUMCOLS 3 COUNT 10

1. You're collecting single column frequencies on the leading column. This is GOOD.

I see some customers doing collecting a slight variation - which is BAD.
TABLE ( ALL ) INDEX ( ALL ) KEYCARD
FREQVAL NUMCOLS 3 COUNT 10 [...]57_2150081084525072.WA.patrick.bossmangmail.com@www.idug.org 18891 48 21_access path selection9_Subbu Rao24_subrahmanyarao@YAHOO.COM31_Tue, 20 Apr 2010 22:23:25 -0400393_UTF-8 Hi,

Here are the inputs:

Table name Tb1
Index for this table: ix1, ix2, ix3,ix4 and ix5
Number of row in the table: apprx 57 million rows
Buffer pool names only are different in prod and test. However BP sizes, tablespace is segmented on both sides(prod and test),table name, index names, columns, statistics are same both on production and test sub-systems. [...]56_4036917594492302.WA.subrahmanyaraoyahoo.com@www.idug.org 18940 85 25_Re: access path selection11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 21 Apr 2010 06:51:47 +0200571_ISO-8859-1 Runstats data collected and timestamps are different perhaps?

With such a vague "input" it is impossible for me (or the list) to
actually help you. Please be more specific in your question!

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strae 5
40470 Dsseldorf/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 Dsseldorf, HRB 37894
Geschftsfhrung: Siegfried Frst, Gerhard Schubert [...]64_OFCCAAAB3C.596E56A2-ONC125770C.001A9C23-C125770C.001AB6E5@seg.de 19026 43 49_Re: V9 CM Migration, strange SQL access path / V815_nguyen duc tuan17_ndt.db2@GMAIL.COM31_Wed, 21 Apr 2010 09:08:18 +0100568_ISO-8859-1 Yes, new stats are of course run and they are generated from the free
downloadable IBM OSC tool ..an PMR has been opened.

Regards

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]62_g2wfe4fdff51004210108s4f1b086dy508e752cf8e46634@mail.gmail.com 19070 134 25_Re: access path selection0_24_hhuang@DCCSH.ICBC.COM.CN31_Wed, 21 Apr 2010 16:09:20 +0800355_GB2312 Although this reply seems not so convincible, but it's true that CPU speed

is an important factor to impact access path, especially for your case
that
few details are given.

And something interesting is, in your index list, there is NO ix0 which is

used by test system. Then comes my question, are they really same? [...]69_OFF439D8DE.FCB907E4-ON4825770C.002B1B13-4825770C.002CCCFC@icbc.com.cn 19205 119 25_Re: access path selection15_nguyen duc tuan17_ndt.db2@GMAIL.COM31_Wed, 21 Apr 2010 09:11:01 +0100677_ISO-8859-1 Data not the same ? (=> distribution stats ...)

On Wed, Apr 21, 2010 at 5:51 AM, Roy Boxwell wrote:

>
> Runstats data collected and timestamps are different perhaps?
>
> With such a vague "input" it is impossible for me (or the list) to actually
> help you. Please be more specific in your question!
>
>
> *
> Roy Boxwell*
> SOFTWARE ENGINEERING GMBH
> -Product Development-*
> Robert-Stolz-Strae 5
> 40470 Dsseldorf/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 [...]62_j2mfe4fdff51004210111we03d35a2zc86504a3a3755311@mail.gmail.com 19325 113 39_z/OS DEFINE NO with TEMPLATE and UNLOAD11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 21 Apr 2010 11:07:15 +0200412_ISO-8859-1 Just saw something "odd"...If you run an UNLOAD utility (not DSNTIAUL of
course!) on a DEFINE NO object and you use a TEMPLATE for the unloaded
data what do you think happens?

a) Dataset is allocated, empty, with the correct DCB and with a very small
space allocation
or
b) A DSNU185I message and no dataset allocated at all leading to a later
LOAD to die with JCL error [...]64_OF9555A770.7761C4BB-ONC125770C.0031A7A8-C125770C.00321A2A@seg.de 19439 82 25_Re: access path selection11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 21 Apr 2010 11:14:45 +0200575_ISO-8859-1 well if you ran with FREQVAL on one system and not on the other....or many
many other RUNSTAT differences.Not to mention "old" data lurking in the
SYSCOLDIST...there's tons of room for differences!

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strae 5
40470 Dsseldorf/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 Dsseldorf, HRB 37894
Geschftsfhrung: Siegfried Frst, Gerhard Schubert [...]64_OFAFF689DF.72073CFC-ONC125770C.0032B43B-C125770C.0032CA4E@seg.de 19522 64 21_Re: Schema Processor?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 21 Apr 2010 22:21:32 +1000415_ISO-8859-1 I have a vague recollection that the schema processor is provided to satisfy
an SQL Standard conformance requirement.

Specify SET CURRENT RULES = 'STD' and DB2 for z/OS behaves like
LUW - well, actually it follows the SQL Standard Rules that came into
existance after DB2/z started doing something different.

James Campbell

On 20 Apr 2010 at 11:32, Myron Miller wrote: [...]48_4BCF7A6C.5219.625299E3@jacampbell.acslink.net.au 19587 46 43_Re: z/OS DEFINE NO with TEMPLATE and UNLOAD10_Joe Geller21_joerg6666@HOTMAIL.COM31_Wed, 21 Apr 2010 08:42:27 -0400312_UTF-8 Why do you need the correct DCB? Since the file will be empty,
you could just use DD DUMMY on the Load.

Joe

Just saw something "odd"...If you run an UNLOAD utility (not DSNTIAUL of course!) on a DEFINE NO object and you use a TEMPLATE for the unloaded data what do you think happens? [...]53_4831548381451576.WA.joerg6666hotmail.com@www.idug.org 19634 242 43_Re: z/OS DEFINE NO with TEMPLATE and UNLOAD11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 21 Apr 2010 14:56:24 +0200528_ISO-8859-1 that's the rub...if the UNLOAD has unloaded data I wish to LOAD it if not
then I wish to load nothing. Doing DUMMY is a JCL change that I do not
want.

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Strae 5
40470 Dsseldorf/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 Dsseldorf, HRB 37894
Geschftsfhrung: Siegfried Frst, Gerhard Schubert [...]64_OFFDE057B6.D85EC961-ONC125770C.0046FA00-C125770C.00471537@seg.de 19877 92 43_Re: z/OS DEFINE NO with TEMPLATE and UNLOAD10_Joe Geller21_joerg6666@HOTMAIL.COM31_Wed, 21 Apr 2010 10:33:21 -0400374_UTF-8 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 it (e.g. COND=(0,LT,unldstep).

Joe

that's the rub...if the UNLOAD has unloaded data I wish to LOAD it if not then I wish to load nothing. Doing DUMMY is a JCL change that I do not want. [...]53_9152945760799239.WA.joerg6666hotmail.com@www.idug.org 19970 24 14_Re: BMC Unload12_Arledge Bill20_bill_arledge@BMC.COM31_Wed, 21 Apr 2010 10:56:18 -0400522_UTF-8 Vidya,

BMC Unload Plus for DB2 with DIRECT NO does support CTEs (Common Table Expressions).

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]51_5711619299101528.WA.billarledgebmc.com@www.idug.org 19995 61 14_Re: BMC Unload15_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM31_Wed, 21 Apr 2010 11:03:45 -0400600_us-ascii Bill,

We have Unload Plus 9.1, Which is throwing me the below error:

BMC50102I UNLOAD
BMC50102I DIRECT NO
BMC50102I WITH T1 (C1,C2,C3,C4) AS
BMC50102I @
BMC50104E UNEXPECTED TOKEN 'WITH' ENCOUNTERED IN COMMAND
BMC50013I UTILITY EXECUTION TERMINATING, RETURN CODE = 8

Probably, the next release will support it.

Regards
Vidya

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Arledge Bill
Sent: Wednesday, April 21, 2010 10:56 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] BMC Unload [...]81_3E90A949C5684E46AEDAB76BD03B81830799AD7D@HDQNCEXCL1V1.mihdq.marrcorp.marriott.com 20057 39 25_Re: access path selection9_Subbu Rao24_subrahmanyarao@YAHOO.COM31_Wed, 21 Apr 2010 12:03:22 -0400567_UTF-8 Thank you for your prompt responses. Let me add further details.

This is happening on DB2 V8 on Z/os. When the program(having the affected query) ran on TEST region, TEST was updated with production data. Along with the LOAD statistics were collected with TABLE ALL INDEX ALL and with UPDATE option. When the program was promoted to production and the program was run, the affected query started running bit longer than in TEST. (In TEST it took 5 secs and on PROD it took 2 minutes). That is when we were called to look in. The first we looked was [...]56_6186033194158096.WA.subrahmanyaraoyahoo.com@www.idug.org 20097 24 25_Re: access path selection10_Joe Geller21_joerg6666@HOTMAIL.COM31_Wed, 21 Apr 2010 12:08:20 -0400624_UTF-8 You say that the production data was loaded into Test and statistics were
collected. But, when was Runstats last run in production? The data may
be the same, but the statistics may be different.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]53_7955174977236120.WA.joerg6666hotmail.com@www.idug.org 20122 244 14_Re: BMC Unload10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 21 Apr 2010 18:33:34 +0200344_US-ASCII HI Vidya

I met the error 'UNEXPECTED TOKEN 'WITH' ENCOUNTERED IN COMMAND some
times with other BMC tools, usually it was a unsupported statement fixed
in next

release OR with a new fix. Open a ticket with BMC (if not yet done), they
could have a solution handy without waiting a new release (it happened in [...]66_OF58AF3095.E8F3CDF5-ONC125770C.005977A2-C125770C.005AF678@cesve.it 20367 27 38_[OT] Anyone been outsourced to HP/EDS?12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 21 Apr 2010 11:41:06 -0700660_us-ascii Has anyone been recently outsourced to HP? I'm mostly wondering what working conditions are like for whoever remains and they "rebadge". Please contact me off-list if you would be willing to share your experiences. Thanks in advance.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]43_807813.77053.qm@web51103.mail.re2.yahoo.com 20395 84 27_Question about single quote0_18_RENUSHARMA@AOL.COM29_Wed, 21 Apr 2010 23:17:07 EDT796_US-ASCII hello Team

Looking for a sql which can tell me how to remove single quote

the data looks like

Teacher'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''s'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I have to remove single quote from between

Thanks

Renu Sharma

Ace DBA inc

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]31_3b0de.7b5ac6c7.390119b3@aol.com