1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l May 2009, week 1 2 63 34_INDEX SPACES (V8 STOGROUP-DEFINED)25_SUBSCRIBE DB2-L Anonymous17_mairecj@YAHOO.COM30_Fri, 1 May 2009 00:11:14 +0000305_windows-1252 Looking for infomation on what to expect when a space error is encountered on an index space (e.g. DSNP007I # DSNPXTN0 - EXTEND FAILED FOR ...). The DB2 manuals covered some of this in part but not in any one section that I could find. Feedback on any of these questions is appreciated. [...] 66 92 38_Re: INDEX SPACES (V8 STOGROUP-DEFINED)9_Mike Bell21_mbell11a1@VERIZON.NET31_Thu, 30 Apr 2009 22:16:58 -0500481_us-ascii it is the same for indexspace and tablespace. Read access is not affected. Delete access is not affected. Update is not affected if everything fits into the previous space. Update with index changes (if it is the indexspace that is the problem) fails Update where the row doesn't fit into the previous space (varchar or compression) fails and insert fails. Failures will use a lot of cpu time because DB2 will attempt a full space search and extend process each SQL. [...] 159 83 73_AUTO: Ashvin Amin/Dallas/IBM is out of the office. (returning 05/01/2009)11_Ashvin Amin17_ashvin@US.IBM.COM31_Thu, 30 Apr 2009 22:02:50 -0600466_US-ASCII



I am out of the office until 05/01/2009.

I will be out of the office until May 1. I will repsond to your messgaes within 24 hours..

If you need immediate assistance, please contact Jack Mason at .... masonja@us.ibm.com or my manager, Maureen Kinard at ...mkinard@us.ibm.com



Note: This is an automated response to your message "DB2-L Digest - 30 Apr 2009 to 1 May 2009 (#2009-127)" sent on 4/30/09 19:00:08. [...] 243 51 10_Re: ebooks13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Thu, 30 Apr 2009 22:27:22 -0700576_ISO-8859-1 You can get it from Safari Books Online.









On Thu, Apr 30, 2009 at 4:34 PM, Jeffrey Benner, DB2 DBA wrote: > I'm reading Sanders and Snow's "DB2 9 for Linux, UNIX, and Windows Advanced > Database Administration Certification Study Guide " and I would love it if > this were available as a PDF. Just a thought if anyone out there is > listening. > > I use flashcard software and if I had a pdf it would be much easier to > create a flashcard set from some of the material where I want to focus more [...] 295 74 45_Re: DB2 LUW - LOAD CLIENT from Runtime Client13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Thu, 30 Apr 2009 22:28:34 -0700670_ISO-8859-1 You need only the runtime client.









On Thu, Apr 30, 2009 at 11:47 AM, wrote: > A simple question for the DB2-L List: > > Can you run a DB2 "LOAD CLIENT . . . " utility from a DB2 Runtime Client? > Or do you need a full-blown DB2 Client (App Dev, Admin, or Connect client) > to run a "LOAD CLIENT . . . " utility? > > ------------------------------------------------------- > Bill Gallagher, Logical DBA > IT / Infrastructure Services > Phoenix Life Insurance Company > (W) 860-403-6327 > (C) 860-539-5576 > bill.gallagher@phoenixwm.com > > > ----------------------------------------- > [...] 370 94 60_Re: Procedure for fallback from DB2 v8 ENFM/NFM to DB2 v8 CM14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 1 May 2009 20:45:35 +1000477_US-ASCII Consider the following events:

do application updates Stop DB2 in V8 CM Log RBA 1 take DFSMS dump of catalog directory, active logs, BSDS Start DB2 Migrate to V8 NFM Perform application updates Log RBA 2 Stop DB2 Restore DFSMS dumps Start DB2

At this point the RBAs in the catalog and directory clusters match those in DBD01, and all updates to the them are reflected in the active logs. So they don't need a REPAIR LEVELID or an image copy. [...] 465 33 21_Timestamp to contoken5_anand19_mahadea@LABCORP.COM30_Fri, 1 May 2009 12:16:23 +0000366_ISO-8859-1 How does DB2 convert the precompile timestamp to a HEX contoken to store in the DBRM and LoadModule? Willie Favero in a post had said "...DB2 does a shift logical to the timestamp then applies two's compliment to it..."

In what format is the timestamp when DB2 does the logical shift (For that matter, I'm not sure what a logical shift is :-) [...] 499 64 25_Re: Timestamp to contoken9_Mike Bell21_mbell11a1@VERIZON.NET30_Fri, 1 May 2009 08:21:57 -0500301_US-ASCII Go look at the STCK instruction. The TOD clock is a fixed format x(8) that is used all over the place in DB2 log records, IMS also uses it in log records. And if you apply the TIMESTAMP function to a char x(8), DB2 assumes it was in TOD format and converts it to a DB2 format timestamp. [...] 564 111 25_Re: Timestamp to contoken7_Ed Long19_rdhm99a@PRODIGY.NET30_Fri, 1 May 2009 06:30:06 -0700340_us-ascii This is all very interesting but raises the obvious question: Why all the cloak and dagger of the two's complement and logical shift?

Its a pretty common problem to have to resolve a -805 -818; hiding the salient fact, ie the timestamp stored as the contoken, in this case, only makes the problem determination harder. [...] 676 42 25_Re: Timestamp to contoken10_Todd Burch17_toddburch@MAC.COM30_Fri, 1 May 2009 08:36:34 -0500559_US-ASCII Try this link:

http://www.ruban.de/DB2_for_z_OS/z_OS_Code/DB2_z_OS_Code_-_Detail_View/db2_z_os_code_-_detail_view_6.html

Todd

On May 1, 2009, at 7:16 AM, anand wrote:

How does DB2 convert the precompile timestamp to a HEX contoken to store in the DBRM and LoadModule? Willie Favero in a post had said "...DB2 does a shift logical to the timestamp then applies two's compliment to it..."

In what format is the timestamp when DB2 does the logical shift (For that matter, I'm not sure what a logical shift is :-) [...] 719 168 25_Re: Timestamp to contoken12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM30_Fri, 1 May 2009 09:20:18 -0500563_US-ASCII Ed:

Back in the day, there were security concerns about manipulation of the contoken. Some people get around this by using the level parameter when binding. (This is not recommended, as it makes the programmer responsible for correctly matching the load module to DB2.)

We do a lot of examination of the connect token both within and without DB2. You can get and match contokens from the DB2 catalog and DBRMs in a straightforward manner. Matching these to a load module presents issues that appear to be both intended and unintended. [...] 888 153 10_Re: ebooks23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM30_Fri, 1 May 2009 10:01:35 -0500538_ISO-8859-1 Thanks, very useful!

On Fri, May 1, 2009 at 00:27, Ian Bjorhovde wrote:

> You can get it from Safari Books Online. > > > > > > On Thu, Apr 30, 2009 at 4:34 PM, Jeffrey Benner, DB2 DBA > wrote: > > I'm reading Sanders and Snow's "DB2 9 for Linux, UNIX, and Windows > Advanced > > Database Administration Certification Study Guide " and I would love it > if > > this were available as a PDF. Just a thought if anyone out there is > > listening. > > > > I use [...] 1042 239 62_[AD] IBM Authorized DB2 Connect Training Class Starts May 18th12_Kimberly May28_kim.may@THEFILLMOREGROUP.COM30_Fri, 1 May 2009 11:11:31 -0400424_us-ascii The Fillmore Group will deliver IBM course CF602, DB2 Connect to DB2 for z/OS DRDA Implementation with TCP/IP starting on Monday, May 18th, in our Towson, Maryland classroom, just north of Baltimore. If you use DB2 Connect in your organization it's a great class.





This session is guaranteed to run. The course length is 4 days and the cost is $2600. Enroll via the IBM training site at: [...] 1282 179 10_Re: ebooks23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM30_Fri, 1 May 2009 10:01:35 -0500538_ISO-8859-1 Thanks, very useful!

On Fri, May 1, 2009 at 00:27, Ian Bjorhovde wrote:

> You can get it from Safari Books Online. > > > > > > On Thu, Apr 30, 2009 at 4:34 PM, Jeffrey Benner, DB2 DBA > wrote: > > I'm reading Sanders and Snow's "DB2 9 for Linux, UNIX, and Windows > Advanced > > Database Administration Certification Study Guide " and I would love it > if > > this were available as a PDF. Just a thought if anyone out there is > > listening. > > > > I use [...] 1462 261 62_[AD] IBM Authorized DB2 Connect Training Class Starts May 18th12_Kimberly May28_kim.may@THEFILLMOREGROUP.COM30_Fri, 1 May 2009 11:11:31 -0400424_us-ascii The Fillmore Group will deliver IBM course CF602, DB2 Connect to DB2 for z/OS DRDA Implementation with TCP/IP starting on Monday, May 18th, in our Towson, Maryland classroom, just north of Baltimore. If you use DB2 Connect in your organization it's a great class.





This session is guaranteed to run. The course length is 4 days and the cost is $2600. Enroll via the IBM training site at: [...] 1724 110 11_unsubscribe15_Joehlin, Gary L34_gary.l.joehlin@VERIZONBUSINESS.COM30_Fri, 1 May 2009 15:43:56 +0000715_US-ASCII









_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________

IDUG North America 2008 Attendee Testimonial- "The technical content of the conference was excellent and useful. Not only are the "how-to" sessions helpful, it's very beneficial to learn about new features in upcoming releases." _____________________________________________________________________ 1835 158 25_Callable System Services.13_Palko, George16_gpalko@OPERS.ORG30_Fri, 1 May 2009 17:33:12 +0100481_us-ascii Hi List,

We have a need to use Callable System Services within our COBOL batch applications. We would like to issue the SRRBACK command.

We placed the SYS1.CSSLIB data set behind the DB2 datasets in the linkedit step. This seems to work fine. However, all the documentation states that the data set should be placed before the DB2 data sets. However, we are reluctant to do this because of the unknown impact it may have on our existing batch programs. [...] 1994 138 29_Re: Callable System Services.10_Todd Burch17_toddburch@MAC.COM30_Fri, 1 May 2009 11:45:22 -0500444_US-ASCII Hi George.

I don't have any first hand experience with CSS, but I do know how to read an assembler listing.

If you turn listings on with via your COBOL compiler option, you should be able to see the macro cross reference at the bottom of the listing. That will tell you which macros came from what macro libraries, so nothing will be left to guess at whether or not what you expected to be picked up, was picked up. [...] 2133 103 52_Is there an easy way to drop all synonyms for a user0_26_barbara.j.nigh@US.HSBC.COM30_Fri, 1 May 2009 11:21:58 -0700540_US-ASCII Is there a way to drop all synonyms for a userid at once or do you need to drop each synonym individually one at a time?

Barbara J Nigh CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS) 1441 Schilling Place Salinas, CA 93901 ______________________________________________________

Phone. 831 754 1400 2048 Fax. 831-759-7073 Mobile. 831-776-6194 Pager 8317766194@messaging.sprintpcs.com Email. barbara.j.nigh@us.hsbc.com Internet. http://www.hsbc.com ______________________________________________________ [...] 2237 157 56_Re: Is there an easy way to drop all synonyms for a user23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM30_Fri, 1 May 2009 13:29:02 -0500330_ISO-8859-1 If you are running under a Unix environment, you can pipe the output of a catalog view query to an awk that would build the drop statement, write that to a file, then execute the file. I frequently do this with GRANT statements for example.

On Fri, May 1, 2009 at 13:21, wrote: [...] 2395 132 52_Is there an easy way to drop all synonyms for a user0_26_barbara.j.nigh@US.HSBC.COM30_Fri, 1 May 2009 11:21:58 -0700540_US-ASCII Is there a way to drop all synonyms for a userid at once or do you need to drop each synonym individually one at a time?

Barbara J Nigh CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS) 1441 Schilling Place Salinas, CA 93901 ______________________________________________________

Phone. 831 754 1400 2048 Fax. 831-759-7073 Mobile. 831-776-6194 Pager 8317766194@messaging.sprintpcs.com Email. barbara.j.nigh@us.hsbc.com Internet. http://www.hsbc.com ______________________________________________________ [...] 2528 282 56_Re: Is there an easy way to drop all synonyms for a user0_26_barbara.j.nigh@US.HSBC.COM30_Fri, 1 May 2009 11:41:03 -0700677_US-ASCII This is for DB2 z/OS.

Barbara J Nigh CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS) 1441 Schilling Place Salinas, CA 93901 ______________________________________________________

Phone. 831 754 1400 2048 Fax. 831-759-7073 Mobile. 831-776-6194 Pager 8317766194@messaging.sprintpcs.com Email. barbara.j.nigh@us.hsbc.com Internet. http://www.hsbc.com ______________________________________________________





"Jeffrey Benner, DB2 DBA" Sent by: DB2 Data Base Discussion List 05/01/2009 11:29 AM Please respond to DB2 Database Discussion list at IDUG [...] 2811 324 56_Re: Is there an easy way to drop all synonyms for a user13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Fri, 1 May 2009 14:59:53 -0400708_us-ascii Hello Barbara.





Your friendly DBA tool probably does that.

Short of that, you can always execute and SQL statement along the lines of:





select 'Drop synonym '||creator||'.'||name||x'405e'



from sysibm.syssynonyms where creator = 'XXXXX'





and take that output into your favorite SQL processor?





Is that adequate?





Dave

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of barbara.j.nigh@US.HSBC.COM Sent: Friday, May 01, 2009 2:22 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Is there an easy way to drop all synonyms for a user [...] 3136 345 56_Re: Is there an easy way to drop all synonyms for a user13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Fri, 1 May 2009 14:59:53 -0400708_us-ascii Hello Barbara.





Your friendly DBA tool probably does that.

Short of that, you can always execute and SQL statement along the lines of:





select 'Drop synonym '||creator||'.'||name||x'405e'



from sysibm.syssynonyms where creator = 'XXXXX'





and take that output into your favorite SQL processor?





Is that adequate?





Dave

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of barbara.j.nigh@US.HSBC.COM Sent: Friday, May 01, 2009 2:22 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Is there an easy way to drop all synonyms for a user [...] 3482 61 12_DB2 LUW logs10_Jim Rutten35_jim.rutten@STAPLESPROMOPRODUCTS.COM30_Fri, 1 May 2009 19:21:55 +0000413_windows-1252 Hello DB2 gurus,

I just started working in a new location. At previous locations I have worked the numbering on the logs generated by db2 luw v9.5 would work something like this for example:

Day 1 : S0000400.LOG 8:30AM S0000401.LOG 10:10 AM S0000403.LOG 3:35 PM S0000404.LOG 7:19 PM

Day2 : S0000405.LOG 9:36AM S0000406.LOG 11:53 AM S0000407.LOG 2:20 PM S0000408.LOG 9:37 PM [...] 3544 100 16_Re: DB2 LUW logs12_tim malamphy20_timalamphy@YAHOO.COM30_Fri, 1 May 2009 14:38:18 -0700295_us-ascii I've never seen the logs displayed as you have in example 1. What command did you use to display them?

You can certainly look at the time each log file was created to get an idea of when it was created in example 2.

If you want to know what the oldest active log is, [...] 3645 183 16_Re: DB2 LUW logs23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM30_Fri, 1 May 2009 17:08:29 -0500810_ISO-8859-1 Another nice way to determine active logs is using the system administrative view snapdetaillog:

SELECT SUBSTR(DB_NAME, 1, 8) AS DB_NAME, FIRST_ACTIVE_LOG, LAST_ACTIVE_LOG, CURRENT_ACTIVE_LOG, CURRENT_ARCHIVE_LOG, DBPARTITIONNUM FROM SYSIBMADM.SNAPDETAILLOG ORDER BY DBPARTITIONNUM

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0022004.htm

On Fri, May 1, 2009 at 14:21, Jim Rutten < jim.rutten@staplespromoproducts.com> wrote:

> Hello DB2 gurus, > > I just started working in a new location. At previous locations I have > worked > the numbering on the logs generated by db2 luw v9.5 would work something > like this for example: > > Day 1 : S0000400.LOG 8:30AM > S0000401.LOG 10:10 AM > S0000403.LOG 3:35 PM > S0000404.LOG [...] 3829 71 60_Re: Procedure for fallback from DB2 v8 ENFM/NFM to DB2 v8 CM14_Wm G McDermott24_wmcdermott@OPTONLINE.NET30_Fri, 1 May 2009 22:30:54 -0400524_us-ascii Donna,

I would strongly suggest that you contact your IBM advocate and tell them that a fallback procedure is a business requirement of your implementation plan. They will be able to help you with this.

Regards, William McDermott

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Donna Domovic Sent: Tuesday, April 28, 2009 2:31 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Procedure for fallback from DB2 v8 ENFM/NFM to DB2 v8 CM [...] 3901 275 27_URGENT :load Delimited file13_Laila hosaini19_lshosaini@YAHOO.COM30_Fri, 1 May 2009 22:01:15 -0700811_iso-8859-1





















Hi Dear Listers I'm trying to load a Delimited file to a DB2 V9.1 /zos,I got this error"

DSNURCON - INPUT FIELD 'RETAILER_ID', INVALID FOR 'OIL.RETAILER_OIL11', ERROR CODE '02 - INPUT NUMERIC INVALID'

Load statement is this: LOAD DATA INDDN SYSREC LOG NO RESUME YES ASCII FORMAT DELIMITED COLDEL X'2C' CHARDEL X'20' INTO TABLE "OIL"."RETAILER_OIL11" ( "MERCHANT_QUOTA_ID" POSITION(*) INTEGER EXTERNAL(3) , "RETAILER_ID" POSITION(*) DECIMAL EXTERNAL(8,0) , "FIRST_NAME" POSITION(*) VARCHAR , "LAST_NAME" POSITION(*) VARCHAR , "ADDR" POSITION(*) VARCHAR , "TEL" POSITION(*) VARCHAR , "QUOTA" POSITION(*) INTEGER EXTERNAL(3) , "ZIP_CODE" POSITION(*) VARCHAR , "NATION_CODE" POSITION(*) VARCHAR ) [...] 4177 220 31_Re: URGENT :load Delimited file13_Laila hosaini19_lshosaini@YAHOO.COM30_Fri, 1 May 2009 22:51:47 -0700464_iso-8859-1 Hi, with CHARDEL X'22' is the same result;sometimeS load THE first record ; Really I am comfuse;by using a HEX ON AND hex editor i couldn't find the reason...???? Thanks in Advance. L.S.H

--- On Wed, 4/29/09, James Campbell wrote:



From: James Campbell Subject: Re: [DB2-L] URGENT :load Delimited file To: DB2-L@WWW.IDUGDB2-L.ORG Date: Wednesday, April 29, 2009, 4:58 PM [...] 4398 73 18_Image copy on TAPE13_Laila hosaini19_lshosaini@YAHOO.COM30_Fri, 1 May 2009 23:30:07 -0700588_iso-8859-1

Hi Dear LIsters

I'M on DB2 V9.1 ON z/os PLATFORM. there is 5 GDG for daily backup and 2 GDG for WEEKLY full backup. There is a question;We use tape for full backups, and DASD for INCREMENTAL daily backups,for backups on TAPE I use DISP=(NEW,CATLG) ,WHICH has created a COMponent on DASD ,FOR CATALOG OPTION, IF in a special contiditon we decide to recover this full backups on another system with a different catalog ;what would happen? NEVER ,DISP(NEW,KEEP) ,IN next backup was succeed ,because of SYSCOPY records,which was exist. WHAT is your suggestion? [...] 4472 27 30_Mike Tobe is Out of the Office9_Mike Tobe25_Mike_Tobe@PROGRESSIVE.COM30_Sat, 2 May 2009 04:05:41 -0400544_US-ASCII I will be out of the office starting 05/01/2009 and will not return until 05/04/2009.

I will respond to your message when I return.

Happy Holidays!

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 4500 58 32_DSN command through STARTED TASK13_Laila hosaini19_lshosaini@YAHOO.COM30_Sat, 2 May 2009 02:43:10 -0700691_iso-8859-1 Hi Dear Listers

Is it possible to issue DSN and then RUN ,ON a STARTED TASK?

Thanks in advance. L.S.H







_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________

IDUG North America 2008 Attendee Testimonial- "The most important thing I always find at IDUG is the revisiting of old assumptions, with new functions and features." 4559 56 36_Re: DSN command through STARTED TASK9_Mike Bell21_mbell11a1@VERIZON.NET30_Sat, 2 May 2009 09:52:52 -0500306_us-ascii possible, yes but not easy. DSN is closely tied to the TSO structure. That is why you run batch TSO to run DSN in batch. Most people just code up a module that creates and maintains a call attach environment. Much easier than matching the TCB structure from TSO.

Mike HLS Technologies [...] 4616 29 25_Re: Timestamp to contoken5_anand19_mahadea@LABCORP.COM30_Sat, 2 May 2009 16:48:24 +0000449_windows-1252 Todd, Your link deals with the other way round- given a contoken from a DBRM or loadmodule how can I get a human readable timestamp from it. The operation is actually a bit of an approximation from what I understand, probably because how the contoken was originally created! Rgds

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 4646 44 25_Re: Timestamp to contoken5_anand19_mahadea@LABCORP.COM30_Sat, 2 May 2009 16:51:45 +0000573_windows-1252 On Fri, 1 May 2009 08:21:57 -0500, Mike Bell wrote:

> Go look at the STCK instruction. The TOD clock is a fixed format x(8) that >is used all over the place in DB2 log records, IMS also uses it in log >records. And if you apply the TIMESTAMP function to a char x(8), DB2 assumes >it was in TOD format and converts it to a DB2 format timestamp. > >The logical shift and 2's complement are also single instructions in the >principles of operations where machine instructions are documented. > >Mike > Ouch! I think I got more [...] 4691 980 31_Re: URGENT :load Delimited file14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 3 May 2009 14:13:59 +1000304_ISO-8859-1 The way I debug problems like this is to - create a dataset with a single record record containing just the first field (ie "305") - create a table with just the first column - load the record into the table INTO TABLE "OIL"."XXXX" ( "MERCHANT_QUOTA_ID" POSITION(*) INTEGER EXTERNAL(3) ) [...] 5672 94 25_Re: Timestamp to contoken14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 3 May 2009 14:14:00 +1000541_US-ASCII I think (pure speculation) COBOL PIC S9(9) COMP.

COBOL at that time did not have the capacity to manipulate a 10 digit number in binary. So, when constructing the PLIST VALUE clauses, the contoken had to be two 9 digit numbers ie an 18 digit number. So the shifting was to discard the nybble that could not be mapped into the 18 digit number. I suspect the 2s complement was to map the negative STCK values (high bit is on - ie negative) into a numeric positive value - so the first of the two words, at least, would be [...] 5767 338 22_Re: Image copy on TAPE14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 3 May 2009 14:13:59 +1000446_US-ASCII The dataset names of the backups (I presume you actually mean a DB2 image copy - not a z/OS backup, or HSM backup) are recorded in table SYSIBM.SYSCOPY. This table is specific to a DB2 subsystem (I presume this is what you actually mean by "system"). You cannot use use a backup generated by one DB2 subsystem to recover into another DB2 subsystem - because the names will not be in the SYSIBM.SYSCOPY table in the other subsystem. [...] 6106 71 36_Re: DSN command through STARTED TASK14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 3 May 2009 14:14:00 +1000300_US-ASCII What do you mean by "ON" a started task? The RUN program runs in the same address space - so you can't use RUN to create a started task.

You can, however, use RUN in an already created started task.

Most places have something like a DB2RUN CLIST that looks something like [...] 6178 606 31_Re: URGENT :load Delimited file13_Laila hosaini19_lshosaini@YAHOO.COM30_Sun, 3 May 2009 01:49:38 -0700577_iso-8859-1

HI Dear James thank you very much for useful mails... I DID your Instruction. in the first record there isn't any problem ;load was good.. in the second record with delimiter X'2C' between the records, there is the same problem ,like my first mail.... but when line delimiters is X'0A' and then X'0D' ,concatenate the first column of second row to last column of first row. I don't know what is the good line-feed or cariage-return???? to separate records.. Thanks in Advance. L.S.H --- On Sun, 5/3/09, James Campbell wrote: [...] 6785 279 22_Re: Image copy on TAPE13_Laila hosaini19_lshosaini@YAHOO.COM30_Sun, 3 May 2009 02:22:05 -0700470_iso-8859-1 Hi Dear James Thanks a lot for your answers. My mean was this: IS IT POSSIBLE TO use tape full backups (GDG datasets(NEW,CATLG)) as input for DSN1COPY to another DB2 WHICH resides on different MAIN FARME ,WHICH HAS another master-catalog,in target master catalog isn't ANY component of GDGs. you told DISP=(NEW,KEEP) ISN'T GOOD FOR GDGs?????? what is the best DISP for GDG dataset on Tape as DB2 table space's back up?AND WHY??? Thanks in Advance. L.S.H [...] 7065 30 74_Re: Is Xrc a reasonable solution between two databases created separately?21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Mon, 4 May 2009 05:43:50 +0000270_utf-8 Peter,

Sorry , I did'nt check this listserv for 2 weeks , since there was no response . I would be so greatful If you send me the papers you mentioned. Since Db2 Propagator has high load on source side also, I was going to try to use of this facility. [...] 7096 148 63_Presentations from IBM DB2 for z/OS Performance Seminar (Japan)10_DB2usa !!!19_db2usa3@HOTMAIL.COM30_Mon, 4 May 2009 03:52:20 -0400476_Windows-1252

Hi DB2 user,



Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com

Last update on Monday, May 4th 2009



Here are several presentations, from an IBM DB2 for z/OS Performance Seminar (Japan):





- The Best Performance Practices DB2 for z/OS by Akiko Hoshikawa





- DB2 for z/OS PureXML Overview and Performance Practices by Akiko Hoshikawa [...] 7245 56 59_DB2 z/OS V8 - How to get unload from catalog copy in EBCDIC37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ30_Mon, 4 May 2009 12:43:35 +0200551_iso-8859-2

Hi all, how can i get unload from catalog copy in EBCDIC ?

Thanks in advance dusan



_____________ Tato zpráva a všechny připojené soubory jsou důvěrné a určené výlučně adresátovi(-ům). Jestliže nejste oprávněným adresátem, je zakázáno jakékoliv zveřejňování, zprostředkování nebo jiné použití těchto informací. Jestliže jste tento mail dostali neoprávněně, prosím, uvědomte odesilatele a smažte zprávu i přiložené soubory. Odesilatel nezodpovídá za jakékoliv chyby nebo opomenutí způsobené tímto přenosem. [...] 7302 102 71_AW: [DB2-L] DB2 z/OS V8 - How to get unload from catalog copy in EBCDIC0_26_Walter.Janissen@ITERGO.COM30_Mon, 4 May 2009 13:19:21 +0200426_iso-8859-2 Hi

The unload is always in that codepage, you are using on your machine. When I unload a catalog table, my data will be in EBCDIC, with the exception of those columns, which are defined as FOR BIT DATA. These columns will not be converted

Mit freundlichen Grüßen Walter Janißen

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40198 Düsseldorf [...] 7405 122 22_Re: Image copy on TAPE14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Mon, 4 May 2009 22:09:35 +1000656_US-ASCII The "other" system can read tapes using UNIT=,VOL=SER= - this will make no use of the catalog. However some tape managment systems stop you reading un-catalogued datasets (as these are) without special parameters.

Use DISP=(,CATALOG) for GDG datasets

James Campbell

On 3 May 2009 at 2:22, Laila hosaini wrote:

> > Hi Dear James > Thanks a lot for your answers. > My mean was this: > IS IT POSSIBLE TO use tape full backups (GDG datasets(NEW,CATLG)) as input for > DSN1COPY to another DB2 WHICH resides on different MAIN FARME ,WHICH HASanother > master-catalog,in target master catalog [...] 7528 77 75_Re: AW: [DB2-L] DB2 z/OS V8 - How to get unload from catalog copy in EBCDIC14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Mon, 4 May 2009 22:09:36 +1000383_ISO-8859-1 I think a little more information about what you are trying to unload (ie SELECT statement or UNLOAD statement) and what problem you are actually having is needed.

The codepage DB2 converts data to is the codepage specified in the application package BIND.

James Campbell





On 4 May 2009 at 13:19, Walter.Janissen@ITERGO.COM wrote: [...] 7606 1367 31_Re: URGENT :load Delimited file14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Mon, 4 May 2009 22:09:35 +1000374_ISO-8859-1 z/OS MVS does not use "line delimiters". (USS does, but not MVS). That is because the length of each record is known before reading the first data byte - it is either a known fixed length, or is variable length with an explicit length field.

I suspect that you have done something so the second record starts with x'0a0d' - which is invalid numerics. [...] 8974 435 75_Re: AW: [DB2-L] DB2 z/OS V8 - How to get unload from catalog copy in EBCDIC37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ30_Mon, 4 May 2009 15:18:43 +0200520_iso-8859-2 Thanks for your replays. Sorry: My statements for unload from copy are: //STEP1 EXEC DSNUPROC,UID=&SYSUID,UTPROC='',SYSTEM=&SYS //SYSREC DD SYSOUT=* //SYSPUNCH DD SYSOUT=* //SYSPRINT DD SYSOUT=* //FRDD DD DISP=SHR,DSN=DBCATZ.DBZ0.DSNDB06.SYSDBASE.D050109.T0545 //SYSIN DD * UNLOAD TABLESPACE DSNDB06.SYSDBASE FROMCOPYDDN FRDD FROM TABLE SYSIBM.SYSINDEXPART ( PARTITION , IXNAME , IXCREATOR , - - - - - - - - - - - - - - - - 31 Line(s) not Displayed AVGKEYLEN ) WHEN ( IXNAME IN ('SPXA2MN8','SPXA3MN8') ) [...] 9410 115 31_Re: ENCRYPTION of Credit number9_Raj Ghose17_raj.ghose@RBC.COM30_Mon, 4 May 2009 13:54:46 +0000427_windows-1252 Hi Mathew, Thanks for you reply. I was testing some of the SQL statements as mentioned the red book and it seems that one of the statement does not work.

Please note I am using DB2 column Level encryption.

This statement is from Chapter 3 page 72.

"The following statement produces the correct results with almost no impact to performance. SELECT COUNT(*) WHERE COL = ENCRYPT_TDES(:HV);" [...] 9526 69 31_Re: ENCRYPTION of Credit number13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Mon, 4 May 2009 10:52:28 -0400393_us-ascii Hello Raj.

I haven't spent any time with the redbook. I have tested a little with the encrypt and decrypt functions.

Are you working with data you populated in the table? Did you provide a password when you encrypted the column value? If so, you need to use that. You might want to read the SQL reference regarding the encrypt and decrypt functions if you haven't. [...] 9596 131 31_Re: ENCRYPTION of Credit number14_Grainger, Phil20_Phil.Grainger@CA.COM30_Mon, 4 May 2009 16:25:51 +0100491_iso-8859-1 Hi Raj

When you say " We have a requirement to encrypt the credit card .....", what are you needing to protect against? The problem you are trying to solve will dictate which is the most appropriate method to use

For example, if you are concerned that someone may be able to read the PHYSICAL data to determine credit card information, then you could either use encryption or perhaps just reply on DB2 compression. After all, a compressed column is "encrypted" [...] 9728 203 31_Re: ENCRYPTION of Credit number14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Mon, 4 May 2009 12:37:24 -0400640_utf-8 Phil G.,

I believe the current requirements about encrypting credit card numbers are referred to as the PCI (Personal Card Industry) Standard. This standard calls for all credit card numbers to be encrypted-at-rest to prevent exposure by theft of the physical media, e.g. backup tapes or underlying files.

PCI also, if I recall correctly, calls for a policy of regularly changing the passwords required to decrypt the data, so there must also be a key-management strategy -- you can't just say "I'm encrypting this" and then never change the password. I haven't read about DB2's encryption functionality so I don't [...] 9932 333 31_Re: ENCRYPTION of Credit number14_Adrian Collett24_adriancollett@TISCALI.IT30_Mon, 4 May 2009 21:55:01 +0200306_windows-1252 Hi Raj, If you're looking into using DB2's encryption functions I did a presentation about them at IDUG Europe last year ... if you can't find it on the web, drop me a line off-list and I'll send it to you.

Personally, I wouldn't touch them... but as always, "it depends"... :-))) [...] 10266 40 46_For Italian listers - DB2 UDB position in Rome16_mocion@libero.it16_mocion@LIBERO.IT30_Mon, 4 May 2009 15:33:24 -0500377_ISO-8859-1 Esteemed listers

I post a request for a position in Rome. I hope DB2-L administrators won't mind it. If inappropriate, please let me know. I apologize in advance if it'll be the case, but nowadays I think any opportunity should be posted.

It's a position for DB2 UDB on LUW in Rome for a big bank, it's a little bit urgent. Contract is permanent. [...] 10307 137 74_Re: Is Xrc a reasonable solution between two databases created separately?12_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Mon, 4 May 2009 23:12:14 +0200655_Windows-1252

Hi Sanaz,





I've added you to the growing list of recipients. The material will be sent out after the NA Conference, which will allow me to include answers to whatever questions that might come up during the presentations.





Regards,

Peter

> Date: Mon, 4 May 2009 05:43:50 +0000 > From: pourdarab_sanaz@YAHOO.COM > Subject: Re: [DB2-L] Is Xrc a reasonable solution between two databases created separately? > To: DB2-L@WWW.IDUGDB2-L.ORG > > Peter, > > Sorry , I did'nt check this listserv for 2 weeks , since there was no response . > I would be so greatful If you send me [...] 10445 88 79_Re: z/OS db2 v8: testing tools (multiple databases/downloading from production)12_Troy Coleman25_troy.coleman@SOFTBASE.COM30_Mon, 4 May 2009 18:25:26 -0500313_windows-1252 SoftBase Systems has a product that can provide "SLICE" feature. In one set of tables an individual or group of people can insert, update, delete data without impacting other individuals or groups using the same tables. If you would like to learn more about this please send me a note off-line. [...] 10534 55 54_IDUG North America 2009 - User Feedback Opportunities!16_Michael A Wendel19_mawendel@US.IBM.COM30_Mon, 4 May 2009 16:35:20 -0700710_US-ASCII Any Listers attending IDUG next week in Denver, Colorado, might find the following User Feedback Opportunity interesting.

Demonstration of the new installable information center Have you tried the new installable Information Management Software for z/OS Solutions Information Center? The information center provides integrated information for DB2 for z/OS, IMS, DB2 QMF, IBM DB2 and IMS Tools, replication and event publishing, and data warehousing and analytics. The installable information center includes the same content as the Web-based information center, but it runs on a local system or on an intranet server. During this hands-on session, you will install the information center, [...] 10590 205 7_IDUG NA12_Phil Gunning22_pgunning@GUNNINGTS.COM30_Mon, 4 May 2009 19:52:28 -0400666_us-ascii If you are attending IDUG in Denver next week, stop by the booth. Rgds, Phil













Phil Gunning

IBM Data Champion

www.gunningts.com

See us at IDUG North America, May 11-15, 2009 , Denver, CO, Booth 505

DB2 Consulting, Books on DB2, Outsourcing, Education, Remote Support

http://it.toolbox.com/blogs/db2-for-luw

DB2 9.7 Early Access Program --

http://www-01.ibm.com/software/data/db2/technology-sandbox/

Direct +1.610.451.5801

Toll Free 888.241.1070

Support Line 888.200.9588

pgunning@gunningts.com

IBM Business Partner [...] 10796 230 11_Re: IDUG NA7_Ed Long19_rdhm99a@PRODIGY.NET30_Mon, 4 May 2009 17:31:45 -0700631_iso-8859-1 I hope to see you there.

Edward Long

--- On Mon, 5/4/09, Phil Gunning wrote: From: Phil Gunning Subject: [DB2-L] IDUG NA To: DB2-L@WWW.IDUGDB2-L.ORG Date: Monday, May 4, 2009, 7:52 PM























If you are attending IDUG in Denver next week, stop by the booth. Rgds, Phil













Phil Gunning

IBM Data Champion

www.gunningts.com

See us at IDUG North America, May 11-15, 2009 , Denver, CO, Booth 505 [...] 11027 366 11_Re: IDUG NA12_Isaac Yassin20_yassini@BEZEQINT.NET30_Tue, 5 May 2009 05:56:17 +0300543_utf-8 See U there J





Isaac Yassin

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Gunning Sent: Tuesday, May 05, 2009 2:52 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] IDUG NA





If you are attending IDUG in Denver next week, stop by the booth. Rgds, Phil













Phil Gunning

IBM Data Champion

www.gunningts.com

See us at IDUG North America, May 11-15, 2009 , Denver, CO, Booth 505 [...] 11394 45 32_Re: IDUG 2010 - North America ??12_Troy Coleman25_troy.coleman@SOFTBASE.COM30_Mon, 4 May 2009 22:28:01 -0500707_windows-1252 I would hope that the climate will be warm and sunny by then.

Troy Coleman



Jorg Lueke wrote: > That is the best location. I wonder if that one will also be the hardest to get > to due to the economic climate. > > _____________________________________________________________________ > > * IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * > > There is a discussion list for people who are going to IDUG NA09 in Denver - > http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT > _____________________________________________________________________ > > IDUG North America 2008 Attendee Testimonial- > "Each session I took away something that I [...] 11440 51 32_Re: IDUG 2010 - North America ??16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Mon, 4 May 2009 23:37:38 -0400554_us-ascii The weather man say 81 on Sunday - with a little luck the economical climate will climb a couple of degrees too.

Steen Rasmussen CA Sr Engineering Services Architect IBM Certified Database Associate - DB2 9 Fundamentals IBM Certified Database Administrator - DB2 9 DBA for z/OS





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Troy Coleman Sent: Monday, May 04, 2009 10:28 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] IDUG 2010 - North America ?? [...] 11492 77 60_Re: Procedure for fallback from DB2 v8 ENFM/NFM to DB2 v8 CM12_Troy Coleman25_troy.coleman@SOFTBASE.COM30_Mon, 4 May 2009 22:59:03 -0500319_windows-1252 I would contact IBM and get a fallback plan. I'm sure they have done this for many shops. Just from my experience from doing offsite DR type recovery. If the logs don't match the high RBA of the update to the data you will have issues. Some very good points by James Campbell have already been given. [...] 11570 45 29_choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Tue, 5 May 2009 06:08:54 +0000237_utf-8 Hi all , I have a query with a where clause matching 4 columns according to an exist index as follows:

select a , b , c , d from xxx.xxxx where ( a=? and b= ? and c= ? and d>? ) order by a asc , b asc , c asc , d desc; [...] 11616 255 46_Antwort: [DB2-L] choose wrong index in a query11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 5 May 2009 08:15:52 +0200404_ISO-8859-1 what is the index def that it *is* choosing? how are the statistics looking? is this STATIC or DYNAMIC SQL? With or without parameter markers or host variables . . .





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 [...] 11872 115 16_Re: DB2 LUW logs12_Travis, John25_john.travis@CAPGEMINI.COM30_Tue, 5 May 2009 09:03:16 +0100340_us-ascii Jim, This looks like two log sequences are being written to the same location i.e. 549,550,551 and 510,511,512. The logprimary and logsecond do not change the log sequence, they define the MAXIMUM number of log files active.

Have you multiple databases writing to the same log path?

Regards, John Travis DB2 DBA [...] 11988 51 33_Re: choose wrong index in a query14_Lisa Ouellette27_Lisa.Ouellette@WACHOVIA.COM30_Tue, 5 May 2009 12:36:54 +0000410_windows-1252 My best guess is that DB2 must think that having to read all the d's for the specified a, b, and c is more trouble than reading some of the d's and sorting. Note that if your query said

where ( a=? and b= ? and c= ? and d? and the column in the index is descending, it is not really a match? [...] 12040 101 33_Re: choose wrong index in a query37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ30_Tue, 5 May 2009 15:10:15 +0200552_iso-8859-2 Two questions: a) what is the primary index b) why not only select d from xxx.xxxx

Regards dp

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Lisa Ouellette Sent: Tuesday, May 05, 2009 2:37 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] choose wrong index in a query

My best guess is that DB2 must think that having to read all the d's for the specified a, b, and c is more trouble than reading some of the d's and sorting. Note that if your query said [...] 12142 628 55_Re: db2 v8 z/os - applying decimal function to a substr14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 5 May 2009 23:13:40 +1000349_US-ASCII I received a comment offline about my wonderous SQL below.

It was actually something I just wrote (untested) directly into my response. Just like I did with this posting. Although, to be totally truthfull, I actually did have to do this many years ago.

On reflection the way I should really should have done it this way: [...] 12771 893 75_Re: AW: [DB2-L] DB2 z/OS V8 - How to get unload from catalog copy in EBCDIC14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 5 May 2009 23:13:40 +1000659_ISO-8859-1 UNLOAD TABLESPACE DSNDB06.SYSDBASE FROMCOPYDDN FRDD EBCDIC FROM TABLE SYSIBM.SYSINDEXPART ?

James Campbell



On 4 May 2009 at 15:18, Pospísil Dusan wrote:

> > Thanks for your replays. Sorry: > My statements for unload from copy are: > //STEP1 EXEC DSNUPROC,UID=&SYSUID,UTPROC='',SYSTEM=&SYS > //SYSREC DD SYSOUT=* > //SYSPUNCH DD SYSOUT=* > //SYSPRINT DD SYSOUT=* > //FRDD DD DISP=SHR,DSN=DBCATZ.DBZ0.DSNDB06.SYSDBASE.D050109.T0545 > //SYSIN DD * > UNLOAD TABLESPACE DSNDB06.SYSDBASE > FROMCOPYDDN FRDD > FROM TABLE SYSIBM.SYSINDEXPART > ( > PARTITION , > IXNAME , > IXCREATOR , > - - - - - - - - - - - - - - - - 31 [...] 13665 40 33_Re: choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Tue, 5 May 2009 13:26:57 +0000257_windows-1252 sorry I made a mistake in my first mail, My query is like this

select a , b, c, d from xxxx.xxxxx where a= ? and b= ? and c= ? and d> ? ) order by a asc , b asc , c desc , d asc

and the columns order in considered index are: [...] 13706 40 33_Re: choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Tue, 5 May 2009 14:01:53 +0000256_windows-1252 sorry I made a mistake in my mails!!!!, My query is like this

select a , b, c, d from xxxx.xxxxx where a= ? and b= ? and c= ? and d> ? ) order by a asc , b asc , c desc , d asc

and the columns order in considered index are: [...] 13747 229 75_Re: AW: [DB2-L] DB2 z/OS V8 - How to get unload from catalog copy in EBCDIC37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ30_Tue, 5 May 2009 16:50:29 +0200436_iso-8859-2 Thank you dusan

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of James Campbell Sent: Tuesday, May 05, 2009 3:14 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] AW: [DB2-L] DB2 z/OS V8 - How to get unload from catalog copy in EBCDIC



UNLOAD TABLESPACE DSNDB06.SYSDBASE FROMCOPYDDN FRDD EBCDIC FROM TABLE SYSIBM.SYSINDEXPART ? [...] 13977 54 90_[AD] Attend Mike Bell's presentation titled "Bind and Rebind Analysis" at IDUG Denver 20099_Eric West25_ewest@HLSTECHNOLOGIES.COM30_Tue, 5 May 2009 16:52:59 +0000626_ISO-8859-1 Dear IDUG Denver 2009 Attendees,

Make sure you take the time to attend Mike Bell’s presentation titled “Bind and Rebind Analysis” to be given on Wednesday, May 13th, from 2:45PM to 3:45PM in room 210/212. Mike’s presentation will cover the issues and methods used to handle analysis of the large amount of data generated by mass binds and rebinds. The key is identifying Access Path and cost changes before committing to the specific bind or rebind. By dividing the results into safe and problematic categories, Mike is able to demonstrate how to reduce your risks. Additionally, Mike will show methods [...] 14032 33 38_Re: INDEX SPACES (V8 STOGROUP-DEFINED)25_SUBSCRIBE DB2-L Anonymous17_mairecj@YAHOO.COM30_Tue, 5 May 2009 16:57:46 +0000349_windows-1252 Thank you for the quick help!

Does it make much difference in speed/concurrency which utility (rebuild, reorg, recover or drop/create) is used to reconstruct the index if it has exceeded max extents? Are they all fairly interchangeable? Is it a good idea to stop the indexspace (and tablespace) before starting the rebuild? [...] 14066 100 38_Re: INDEX SPACES (V8 STOGROUP-DEFINED)10_Roger Hecq18_Roger.Hecq@UBS.COM30_Tue, 5 May 2009 13:35:45 -0400572_us-ascii I am giving you a short answer, but you may have to do some additional reading, in order to understand it all.

If the index is small, then the time difference probably won't be significant, in terms of total elapsed time. For larger indexes, the time difference could be quite large. Normally, a Recover would be the fastest and then a Reorg. The Reorg would only read the index structure, so it is faster than a Rebuild, which reads the base table. The Drop/ Create should not be done, because that would invalidate all of the plans using the index. [...] 14167 53 38_Re: INDEX SPACES (V8 STOGROUP-DEFINED)19_Jardine, Lawrence J19_JardineLJ@AETNA.COM30_Tue, 5 May 2009 13:43:35 -0400467_us-ascii Recovery is fastest, but requires you to have done an image copy of the index. And the table will not be accessible during the recovery.

Rebuild is next fastest, but the table will be in RO status.

Reorg allows greatest concurrency. Table will be RW until final logapply and switch.

I would never consider drop/create since that have the same concurrency issues as REBUILD, PLUS any packages will be invalidated and require rebind. [...] 14221 76 33_Re: choose wrong index in a query14_Larry Kintisch17_LKint@VERIZON.NET30_Tue, 5 May 2009 14:16:26 -0400489_us-ascii Hi Sanaz, There could be reasons why a "perfect index" for a given query is not chosen. The one you show would be matching on 4 columns AND avoids a sort, but the second index matches on just 2 columns, screens for d, and needs a sort. My guess is that the variable "c" in your program doesn't exactly match the datatype of the column c so that the first index is then also matching on just 2 columns, screening on d and needing a sort. Then the smaller index may be chosen. [...] 14298 102 38_Re: INDEX SPACES (V8 STOGROUP-DEFINED)9_Mike Bell21_mbell11a1@VERIZON.NET30_Tue, 5 May 2009 14:12:06 -0500443_us-ascii IF the only issue is extents, then physical dataset manipulation will also solve the problem. DSN1COPY and ADRDSSU are even faster than recovery and don't require that you make the index copyable. This has been discussed in the archives about once or twice a year.

Now if you physically copy the dataset and don't alter the space parms, the next LOAD REPLACE, REORG, etc that uses the space parms is going to die quickly. [...] 14401 28 39_Quick query tuning table profile script25_SUBSCRIBE DB2-L Anonymous17_mairecj@YAHOO.COM30_Tue, 5 May 2009 19:49:54 +0000460_windows-1252 Does anyone have or can point me to a catalog query that given a table name will return basic tuning information - cardinality, column stats, last stats collection, indexes, indexed columns? Looking for something to put beside explain plan output that could help give rapid tuning feedback.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 14430 142 43_Re: Quick query tuning table profile script10_Roger Hecq18_Roger.Hecq@UBS.COM30_Tue, 5 May 2009 16:02:50 -0400685_us-ascii You can try these two. The first will provide infor about the indexes that are available on a given table. The other will provide info re the access path. Do a find on ? to find the predicate variables. You can contact me directly, if you have any questions.

SELECT CAST(IX.DBNAME AS CHAR(8)) AS DB ,CAST(IX.TBNAME AS CHAR(18)) AS TABLE ,CAST(IX.NAME AS CHAR(18)) AS INDEX ,CAST(IX.UNIQUERULE AS CHAR(1)) AS UR ,CAST(IX.CLUSTERED AS CHAR(1)) AS CL ,CAST(IX.CLUSTERRATIOF AS DECIMAL(3,2)) AS CR ,CAST(IX.NLEAF AS INTEGER) AS LEAF ,CAST(IX.NLEVELS AS SMALLINT) AS LVL ,CAST(IX.FIRSTKEYCARDF AS DECIMAL(9,0)) AS FSTKY ,CAST(IX.FULLKEYCARDF AS DECIMAL(9,0)) AS FULLKY [...] 14573 93 19_db2 v8 nfm z/os 1.712_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM30_Tue, 5 May 2009 16:09:55 -0400595_US-ASCII Hello,

We are looking at changing dsntep2 to dsntep4. I have run some jobs using the same sql in both dsntep2 and dsntep4 but i am not seeing the cpu savings. The table i am going against is about 300,000 rows. The cpu time between the 2 jobs are not more than a few seconds cpu apart. here is the statement i execute DSN SYSTEM(DB2X) RUN PROGRAM(DSNTEP4) PLAN(DSNTP481) + LIB('DB2.DB2X.CM.RUNLIB.LOAD') + PARMS('/ALIGN(MID)') END //* //SYSIN DD * --#SET MULT_FETCH 30000 SELECT * FROM xxxx.xxxxxx; Is there something i am missing? I have read that there is suppose to be a [...] 14667 231 23_Re: db2 v8 nfm z/os 1.710_Roger Hecq18_Roger.Hecq@UBS.COM30_Tue, 5 May 2009 16:25:34 -0400382_us-ascii Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...] 14899 46 41_Re: Is RTS now a prerequisite for DFSORT?10_Nicola Nur17_nick_nur@YAHOO.CA30_Tue, 5 May 2009 13:44:51 -0700335_iso-8859-1 Hi colleagues

We are in DB2 V8.1 z/os.

Can anyone help in this question? We are reorging a very very large DB2 table and the DFSORT says we exceeded capacity of DFSORT.

IBM says that RTS did not capture statistics on this large table so the DFSORT could not allocate the right sizes that he needs. [...] 14946 114 41_Re: Is RTS now a prerequisite for DFSORT?14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 5 May 2009 22:13:16 +0100540_iso-8859-1 I'd not heard this, but looking at PK81133 (which describes a different problem entirely) is the comment:

"When estimating the number of index keys to be sorted, REBUILD INDEX was reading the Real-Time Statistics for this partitioned index even though it was in a REBUILD PENDING state and the RTS values are not reliable. This can result in the wrong estimate being passed to DFSORT and used for sort work data set allocations. If the estimate is too low, DFSORT may not be able to complete the sort and terminate." [...] 15061 237 41_Re: Is RTS now a prerequisite for DFSORT?16_Mark Labby - AES21_mlabby@AESSUCCESS.ORG30_Tue, 5 May 2009 17:15:49 -0400 15299 66 36_Lance D Ziegler is Out of the Office16_Lance D Ziegler125_Lance.D.Ziegler1@AEXP.COM30_Tue, 5 May 2009 18:09:06 -0700324_us-ascii I will be out of the office starting 05/05/2009 and will not return until 05/06/2009.

I will respond to your message when I return to the office. I am reachable by cell phone and will review emails after hours. Kindly address service requests to the TCPS DBA and ABS DBA Lotus Notes distribution lists. [...] 15366 37 33_Re: choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Wed, 6 May 2009 04:19:49 +0000497_utf-8 Hi Larry,

Thanks for the response , I think It ca'nt be datatype mismatching , since I'm trying it in Spufi and the result are the same,choosing wrong index.

And about the statistics , I run Runstats & did Bind also before that . the type of c column is integer in DB2 and in the program we have s9(9) comp , which is the matched type we have in DCLGEN also. This happens when I have a query with 3 columns ( equal condition ) and just the forth one has a range(< or >) . [...] 15404 231 50_Antwort: Re: [DB2-L] choose wrong index in a query11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 6 May 2009 07:22:26 +0200481_ISO-8859-1 what type of runstats? with multi-column freqs you could get a better access but you must tell RUNSTATS that you want 'em !





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 [...] 15636 41 33_Re: choose wrong index in a query14_Peter Vanroose17_pvanroose@ABIS.BE30_Wed, 6 May 2009 06:31:28 +0000607_ISO-8859-1 The particular index choice could also be influenced by the cluster ratio of the two indexes. Check if the (a,b,c,d) index has a relatively low cluster ratio, while the primary (a,b,d) index has cluster ratio 100%. In that case, the access path using index (a,b,c,d) will use list prefetch, to be followed by an additional sort for the ORDER BY, while access through index (a,b,d) doesn't need list prefetch, maybe filters almost equally well, and of course also needs an additional sort. If all this is the case, using index (a,b,d) is indeed the best, i.e. cheapest option! (The optimizer [...] 15678 60 33_Re: choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Wed, 6 May 2009 06:59:38 +0000374_windows-1252 This happens for each index according to the following conditions as I 've tried:

If I have a primary index with the columns (A, B, C) and 2 alternate indexes ,the first one(A,B,D,C) and the second one (A,B,D,E,C) .Now If I have a query like this:

Select A,B, D, C where A= ? and B= ? and d=? and c> ? Oredr by a asc , b asc , d desc , c asc [...] 15739 57 44_Weird - (Insert from Select ) in DB2 z OS v829_SUBSCRIBE DB2-L Krishnanwipro23_krishnanwipro@GMAIL.COM30_Wed, 6 May 2009 07:08:46 +0000579_windows-1252 Hi,

I encountered this statement in one of our COBOL programs. The statement appears little weird to me and i cannot get the meaning of this embedded SQL statement.

Exec SQL

INSERT INTO TABLE11 ( COL1, COL2 ) SELECT :HV-COL1 :HV-COL2 FROM TABLE11 WHERE COL1 = :HV-COL1 AND COL2 = :HV-COL2

END-EXEC.



The following are not clear : 1. What are the column of TABLE11 that are fetched in the SELECT statement? 2. Is it a MULTI-ROW insert ? 3. Is Values clause optional as it is not mentioned in the above Insert statement? [...] 15797 312 50_Antwort: Re: [DB2-L] choose wrong index in a query11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 6 May 2009 09:11:15 +0200501_ISO-8859-1 silly question I know but is the column d defined in the index as DESC ?? and does your runstats look like

RUNSTATS TABLESPACE xxx.xxx TABLE(ALL) INDEX(ALL KEYCARD FREQVAL NUMCOLS 2 COUNT 10 FREQVAL NUMCOLS 3 COUNT 10 FREQVAL NUMCOLS 4 COUNT 10)

??





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 [...] 16110 100 61_Antwort: [DB2-L] Weird - (Insert from Select ) in DB2 z OS v811_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 6 May 2009 09:24:11 +0200310_ISO-8859-1 very strange. Have you changed it with cut and paste as at least one "," is missing on the host variables... It looks very odd to me! As it will insert only the host values if it finds the host values......and that for as many times as COL1 and COL2 are the same as the host vars...odd indeed! [...] 16211 36 54_Re: Antwort: Re: [DB2-L] choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Wed, 6 May 2009 07:25:00 +0000415_windows-1252 Roy , That's d: Desc , but waht do you mean of numcols 2,3,4) for all indexes ? Do you mean It should be run for all indexes and with differet number of cols?

I have the following indexes:

primary : A asc - B asc - C asc index 2 : A asc - B asc - D desc - C asc index 3 : A asc - B asc - D desc - E asc - C asc index 4 :A asc - B asc - D asc - C desc So Please ley me know what your [...] 16248 237 71_Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] choose wrong index in a query11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 6 May 2009 09:30:48 +0200325_ISO-8859-1 I did in the earlier post! That is the runstats syntax that you should be using. The 1st column data is always fetched but you need the data for all the columns upto the last one (this is also done) so as you have a five column index on this table you need NUMCOLS 2 - 4 to be executed (only once of course!) [...] 16486 88 41_Re: Is RTS now a prerequisite for DFSORT?15_Douwe van Sluis21_d.b.van.sluis@HOME.NL30_Wed, 6 May 2009 11:47:01 +0200426_us-ascii Hi Nick,

I think the other replies give you good clues.

I would like to advice people to start using RTS in DB2 V8. Lots of good things are provided with RTS. And more important. IBM is moving to using RTS Stats in more and more "things". In V9 RTS becomes part of the catalog and therewith is mandatory. RTS does not cost much, only the effort to externalize the stats. Collection is done anyway. [...] 16575 62 23_Re: db2 v8 nfm z/os 1.716_mocion@libero.it16_mocion@LIBERO.IT30_Wed, 6 May 2009 05:24:41 -0500345_ISO-8859-1 Hi all

I did some preliminary tests as well, but I didn't find any great difference in cpu. I selected many rows as papers say that there's a range where you have some advantages in using DSNTEP4 instead of DSNTEP2. I did my tests in a sandbox with a table having row length relatively small. Have to do some other tests. [...] 16638 62 48_Re: Weird - (Insert from Select ) in DB2 z OS v814_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 6 May 2009 21:02:13 +1000555_US-ASCII All I can think of is someone is trying to double the number of rows the specified values.

Of course, get it wrong and the newly inserted row(s) becomes the basis for adding another row, which becomes ...

James Campbell

On 6 May 2009 at 7:08, SUBSCRIBE DB2-L Krishnanwipro wrote:

> Hi, > > I encountered this statement in one of our COBOL programs. The statement > appears little weird to me and i cannot get the meaning of this embedded SQL > statement. > > Exec SQL > > INSERT INTO TABLE11 > ( COL1, > COL2 > ) [...] 16701 83 41_Re: Is RTS now a prerequisite for DFSORT?14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Wed, 6 May 2009 13:33:09 +0200474_us-ascii Hi,

Do you use DFSORT and do you use sort-files in your jcl ? Zparm "IGNSORTN=YES" ignores your sort-files in your jcl Zparm "UTSORTAL=YES" ignores SORTNUM-parameters

greetings,

Patick Steurs

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Douwe van Sluis Sent: woensdag 6 mei 2009 11:47 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Is RTS now a prerequisite for DFSORT? [...] 16785 38 75_Re: Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] choose wrong index in a query21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM30_Wed, 6 May 2009 11:34:27 +0000460_windows-1252 Hi Roy,

It worked ! Thanks for your solution.Just I ca'nt understand what you mean by "running only once",I think It should be replaced instead of normal Runstats we do all the time,correct?

In addition , About the value specified for the Freqval , should It be the number of frequency of the combination of those columns we specify ? If I have from 1000 to 200000 rows as the freq for 2 columns , should I specify the average? [...] 16824 346 92_Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] choose wrong index in a query11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 6 May 2009 13:46:39 +0200300_ISO-8859-1 Great news that it worked! The runstats "only once" I meant do not run multiple RUNSTATS each with different FREQVAL counts. One Big runstats for the largest index column count -1 is enough (RUNSTATS does not worry if you specify too many) an example that I use on my sandbox here is [...] 17171 270 92_Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] Antwort: Re: [DB2-L] choose wrong index in a query11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 6 May 2009 13:53:50 +0200390_ISO-8859-1 too quick on send...I missed the second question sorry!

The values for

FREQVAL NUMCOLS 16 COUNT 10 Note that MOST/BOTH/LEAST are then allowed and MOST is the default

Where 16 is the concatenated columns to get the statistics for and 10 is how many frequency stats to be gathered. Here I use the default top 10 but you can specify MOST, LEAST or BOTH eg [...] 17442 236 23_Re: db2 v8 nfm z/os 1.712_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM30_Wed, 6 May 2009 08:42:14 -0400397_US-ASCII yeah Max I do not see the CPU savings. Not worth the effort of changing JCL. Plus some jobs using DSNTEP4 used more CPU. Thanks, Jeff 'No IDUG again this year'





"mocion@libero.it" Sent by: DB2 Data Base Discussion List 05/06/2009 06:25 AM Please respond to DB2 Database Discussion list at IDUG [...] 17679 38 38_Re: INDEX SPACES (V8 STOGROUP-DEFINED)25_SUBSCRIBE DB2-L Anonymous17_mairecj@YAHOO.COM30_Wed, 6 May 2009 13:21:28 +0000305_windows-1252 The several responses received on this thread were invaluable. Thank you very much for your time and effort to respond. It is the kind of hard-won, practical knowledge that you cannot find in manuals. It is great to know now the trade-offs among the various options to rebuild an index. [...] 17718 200 38_Z/OS 1.10, DB2 V8.1, Stored procedures16_Proctor, William25_william.proctor@TGSLC.ORG30_Wed, 6 May 2009 08:41:14 -0500540_us-ascii Hello and thanks for all replies in advance. We have had a problem with a couple of stored procedures that are getting stopped at night due to conflicts with part of our batch process. These stored procedures handle our web registrations and its vital that they complete. We are rewriting part of the code to handle the situation but my concern is that when a stored procedure stops like this at night we can go 8 to 10 hours with it down. How does most shops handle this and is there a way to automate a check for this? Does [...] 17919 229 55_Antwort: Re: [DB2-L] INDEX SPACES (V8 STOGROUP-DEFINED)11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 6 May 2009 15:52:49 +0200638_ISO-8859-1 nope. Db2 will grab space straightaway





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







SUBSCRIBE DB2-L Anonymous Gesendet von: DB2 Data Base Discussion List 06.05.2009 15:21 Bitte antworten an DB2 Database Discussion list at IDUG [...] 18149 206 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Wed, 6 May 2009 16:02:56 +0200733_us-ascii Use a db2-command " -DIS PROCEDURE(*.*) "





greetings,





Patrick Steurs





From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Proctor, William Sent: woensdag 6 mei 2009 15:41 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Z/OS 1.10, DB2 V8.1, Stored procedures





Hello and thanks for all replies in advance. We have had a problem with a couple of stored procedures that are getting stopped at night due to conflicts with part of our batch process. These stored procedures handle our web registrations and its vital that they complete. We are rewriting part of the code to handle the situation but my concern is [...] 18356 197 41_Re: Is RTS now a prerequisite for DFSORT?10_Nicola Nur17_nick_nur@YAHOO.CA30_Wed, 6 May 2009 07:16:09 -0700621_iso-8859-1 Thank you Phil, Douwe, Patrick and Mark. We have these parms on and we still have a problem. IBM is handling it. I shall give you an update on successful resolution. Thanks again and thank God for this List. Nick Nur



--- On Wed, 5/6/09, Steurs Patrick wrote:

> From: Steurs Patrick > Subject: Re: [DB2-L] Is RTS now a prerequisite for DFSORT? > To: DB2-L@WWW.IDUGDB2-L.ORG > Received: Wednesday, May 6, 2009, 7:33 AM > Hi, > > Do you use DFSORT and do you use sort-files in your jcl ? > Zparm "IGNSORTN=YES" ignores your sort-files in [...] 18554 31 33_When was a stored proc last used?13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO30_Wed, 6 May 2009 16:37:59 +0200315_iso-8859-1 I guess this is old, but my search didn't find anything. We are starting a big project that involves a lot of old stored procs. The question came up, witch one of these can we just delete because they haven't been used the last 2 years?

Anyone who have an idée on who to answer the question? [...] 18586 32 33_Re: choose wrong index in a query14_Peter Vanroose17_pvanroose@ABIS.BE30_Wed, 6 May 2009 14:47:46 +0000426_ISO-8859-1 My guess would be the the index being used is the one with the highest cluster ratio. Or actually, the index having a cluster ratio above 90%. Could you query the catalog table SYSIBM.SYSINDEXES for the indexes under consideration, and tell us what's the value in column CLUSTERRATIOF ? Something like: SELECT NAME, CLUSTERRATIOF FROM SYSIBM.SYSINDEXES WHERE TBNAME = mytablename AND TBCREATOR = mytableschema [...] 18619 488 26_DB2 Forum Meeting May 7th!9_Bob Brock28_brock@KRYPTON-CONSULTING.COM30_Wed, 6 May 2009 09:58:57 -0500555_iso-8859-1

DB2 FORUM User Group





Second Quarterly Meeting

Thursday, May 7, 2008

8:00 AM - 12:00 PM





Location: Doubletree Hotel Dallas (at Midway and 635) 4099 Valley View Lane | Dallas, TX 75244 Phone: 972-419-7694| Fax: 972-458-8260





Breakfast Sponsor: NEON Enterprise Software, Inc.





08:00 - 09:00 Registration and Breakfast

09:00 - 09:05 Business and Announcements

09:05 - 10:15 An Hour of DB2 for z/OS Tips and Techniques [...] 19108 280 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures11_Suresh Sane21_data_arch@HOTMAIL.COM30_Wed, 6 May 2009 10:09:42 -0500677_iso-8859-1

Bill,





You can set up things so that it continues after an abend but this itself has drawbacks since a serious failure that keeps happening will create huge dumps reperatedly. You may want to track the console messages (like we do) to take corerctive action. From the SP redbook SG24-7604:





There are options on the CREATE PROCEDURE statement that allow you to control this behavior at the individual stored procedure level. The possible choices are discussed below: STOP AFTER SYSTEM DEFAULT FAILURES This specifies that the stored procedure should be placed in a stopped status after the number of failures [...] 19389 288 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures12_Fay, Colin M13_CFay@UNUM.COM30_Wed, 6 May 2009 11:11:46 -0400574_us-ascii





A quick work-around if the stored procedure is stopping because of package abending, is to increase zparm STORMXAB (MAX ABEND COUNT) - Db2 v 8

to a higher number and allow the stored procedure to continue processing.













________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Proctor, William Sent: Wednesday, May 06, 2009 9:41 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Z/OS 1.10, DB2 V8.1, Stored procedures [...] 19678 56 37_Re: When was a stored proc last used?15_Kalena, Michael27_Michael.Kalena@JPMORGAN.COM30_Wed, 6 May 2009 11:18:40 -0400340_iso-8859-1 I'm sure there's a better way to do this but this worked for us.

All our stored procedures have SQL so they all use packages. We had Omegamon at the time, so we created a daily Omegamon job to look at all the packages that ran and captured the ones we were interested in to file. We then loaded this data to a table. [...] 19735 130 79_Re: z/OS db2 v8: testing tools (multiple databases/downloading from production)16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Wed, 6 May 2009 11:23:29 -0400399_iso-8859-1 Given that someone from a different vendor has jump in on this thread, I would be remise not to do so myself.

Compuware has a solution that can produce/sub select/create etc a testing/QA/User acceptance environment etc. (with optional masking/obfuscating data privacy capabilities). All this With related data. Not only DB2, but with flat files/IMS/Oracle/Ms Access/VSAM etc. [...] 19866 77 47_An informal poll on one aspect of query rewrite5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Wed, 6 May 2009 11:13:43 -0500371_us-ascii We have just started getting version 9 far enough along to do some playing on the system and in general, I am very impressed.

I was very excited about the new ability of the optimizer to rewrite from correlated to non-correlated (and vice-versa). However, I have an example of a query that is not rewritten (and I see this type of query very often). [...] 19944 132 48_Re: Weird - (Insert from Select ) in DB2 z OS v814_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 6 May 2009 17:21:14 +0100387_iso-8859-1 OK, looking at this "inside out", the sub select does not actually select ANY columns from the table

It returns one result "row" (consisting of two host variables!) every time the predicates find a match

SO, if there are two rows with "COL1 = :HV-COL1 AND COL2 = :HV-COL2" then TWO NEW rows will be inserted BOTH having the SAME values in these two columns [...] 20077 274 51_Re: An informal poll on one aspect of query rewrite12_Paul Ogborne19_paulogborne@AOL.COM30_Wed, 6 May 2009 12:36:02 -0400393_us-ascii Hi Larry,

This type of query construction tends to make me bristle somewhat and regrettably?I see it often. My preference is a construct as follows which will return the MAX row and in most instances will be more efficient than your two examples:



SELECT * FROM SYSIBM.SYSCOPY???

?WHERE DBNAME = 'DSNDB01'??????

?? AND TSNAME = 'SPT01'???????? [...] 20352 355 40_INSTEAD OF triggers.... (DB2 9 for z/OS)14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 6 May 2009 17:47:58 +0100324_us-ascii Anyone out there had any luck creating an INSTEAD OF ... INSERT trigger?





I can get an INSTEAD OF ... DELETE trigger to work just fine, but when I try and mimic an INSERT to a read-only view, I'm getting an S04E 00E70005 from SPUFI (and any other mechanism I try and execute it from).... [...] 20708 62 33_Re: choose wrong index in a query14_Larry Kintisch17_LKint@VERIZON.NET30_Wed, 6 May 2009 12:52:36 -0400390_us-ascii I've seen your reply to me and to Peter. The answer is still a mystery. I would attempt to create a "fatter" index that is Index-only and avoids the sort. See the Redbook [you can download the PDF]: http://www.redbooks.ibm.com/abstracts/sg247134.html?Open

In that Redbook, section 2.6.5 describes the index design process and the section "sort avoidance" may help too. [...] 20771 351 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)11_Hunter Cobb22_hhc@TRAINERSFRIEND.COM30_Wed, 6 May 2009 11:47:07 -0600435_ISO-8859-1 Phil,

Here's a (tested) sequence that uses an INSTEAD OF ... INSERT trigger on a read-only view. The ROLLBACK at the end ensures that no rows are permanently added to DSN8910.EMP.

Hunter Cobb hunter@trainersfriend.com ---------------------------------------- SELECT * FROM DSN8910.EMP;

CREATE VIEW EMPJOB AS SELECT JOB, COUNT(*) AS JCT, SUM(SALARY) AS JOBSAL FROM DSN8910.EMP GROUP BY JOB; COMMIT; [...] 21123 819 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 6 May 2009 18:52:30 +0100504_us-ascii Know - that works here too





BUT when I create a new scenario from scratch, I can INSTEAD OF DELETE and UPDATE triggers to work fine, but not an INSTEAD OF .. INSERT one





Most odd





Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: phil.grainger@ca.com





Ditton Park Riding Court Road Datchet Slough SL3 9LL [...] 21943 1105 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 6 May 2009 13:56:24 -0400560_us-ascii Phil G.,





Are you by chance working with a non-materialized view in your scenario? It seems to me that INSERTing to a non-materialized view would force view materialization, or would not be supported.





--Phil Sevetson





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: Wednesday, May 06, 2009 1:53 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS) [...] 23049 1292 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 6 May 2009 19:02:11 +0100368_us-ascii OK, what I have is this:





* A table with a TIMESTAMP column in it

* A view that selects the MAX(TIMESTAMP) column as MAX_TIMESTAMP - thus is a read only view

* The INSTEAD OF INSERT trigger takes a new value for the MAX_TIMESTAMP and attempts a new INSERT on to the base table to create a new row with a new timestamp [...] 24342 605 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 6 May 2009 19:11:12 +0100188_us-ascii Perhaps my DDL might help to shed light:





--Create a table with a TIMESTAMP column in it

create table date_table

(timestamp timestamp);

24948 31 30_CPU Impact on Getpages and i/o10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Wed, 6 May 2009 18:14:12 +0000443_windows-1252 We have an i/o driven query that has a critical SLA. Current normal execution is about 1.3 seconds with 600 getpages and 200 i/o (sync read). When the CPU gets constrained the response time increases over 2 seconds with getpages and i/o increasing to 700-800 and up to 300. So is this just a case of a CPU constrained system taking longer to service i/o's which means lower hit ratios and thus more getpages and io requests? [...] 24980 865 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 6 May 2009 14:46:27 -0400514_us-ascii Phil G.,





Well, for what it's worth I get the same result you do, down to the reason code.





--Phil Sevetson





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: Wednesday, May 06, 2009 2:11 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)





Perhaps my DDL might help to shed light: [...] 25846 95 51_Re: An informal poll on one aspect of query rewrite5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Wed, 6 May 2009 19:02:54 +0000414_windows-1252 I have one addition to my observations on this.

I compare DB2 for Z with DB2/LUW on several fronts (and I refuse to get into arguments about which is better). I realize that the two DB2s will always be "playing catch up" with each other.

I decided to test the capability of DB2/LUW with a query like the one I am speaking of. To do this, I explained the following query in DB2/LUW: [...] 25942 994 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 6 May 2009 20:24:28 +0100503_us-ascii I suppose that's good news L





Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: phil.grainger@ca.com





Ditton Park Riding Court Road Datchet Slough SL3 9LL





CA plc 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. [...] 26937 40 32_Rotate Partition & Compression %9_Dan Chatt15_dchatt@FNNI.COM30_Wed, 6 May 2009 23:17:55 +0000588_windows-1252 We have a table partitioned with 36 parts. All of the parts have about 44% compression except the last two most recent partitions and they have only about 30% compression. All of the growth except for the last 2 weeks was into empty parts. The last 2 weeks of growth were into partitions that previously contained the oldest data. We are doing a LOAD with DUMMY input and KEEPDICTIONARY into the oldest part followed by a ROTATE PARTITION. A weeks worth of data is inserted into the newest partition and then it is reorganized with REORG KEEPDICTIONARY. We didn't think [...] 26978 41 17_Rebinds and SPT0113_Donna Domovic28_Donna.Domovic@EXELONCORP.COM30_Thu, 7 May 2009 02:13:16 +0000324_windows-1252 Hello,

We've recently upgraded to DB2 v8 CM and are now running rebinds on some of our application packages to see if we can gain any CPU savings and take advantage of the new optimizer. While running rebinds in our test environment, we ran out of space on the SPT01 and had to add another dataset. [...] 27020 65 21_Re: Rebinds and SPT010_27_Jeff.D.Jerry@WELLSFARGO.COM30_Wed, 6 May 2009 21:17:27 -0500572_us-ascii Yes it is normal.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Donna Domovic Sent: Wednesday, May 06, 2009 9:13 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Rebinds and SPT01

Hello,

We've recently upgraded to DB2 v8 CM and are now running rebinds on some of our application packages to see if we can gain any CPU savings and take advantage of the new optimizer. While running rebinds in our test environment, we ran out of space on the SPT01 and had to add another dataset. [...] 27086 37 41_DB2 z V9 - KeepDynamic for JDBC and DRDA?11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM30_Thu, 7 May 2009 05:49:10 +0000417_ISO-8859-1 Hello,

if the JDBC driver standard package set has been bound to DB2 with KeepDynamic YES ( (com.ibm.db2.jcc.DB2Binder... -keepdynamic yes), what's the purpose of specifying this same property in the WAS datasource defined upon that driver ( DB2BaseDataSource.YES ) ? Considering only pure JDBC applications where no packages beside the driver standard packageset is bound and used at runtime. [...] 27124 140 51_Re: An informal poll on one aspect of query rewrite14_Peter Backlund21_BacklundDB2@TELIA.COM30_Thu, 7 May 2009 08:32:58 +0200 27265 85 35_R: Rotate Partition & Compression %16_mocion@libero.it16_mocion@LIBERO.IT30_Thu, 7 May 2009 08:39:49 +0200388_UTF-8 Your compression % apperas relatively low as I presume you load your data with LOAD utility which is less efficient. And loaded data could be difefrent and data used for dictionary could be not good for compression and/OR with a population of data not well sampled.

I'd rebuild dictionary with reorg which is more fficient in data sampling for building dictionary tree. [...] 27351 338 20_SORT COMPONENT RC=1613_Sahu, Vikas K30_vikas.k.sahu@BANKOFAMERICA.COM30_Thu, 7 May 2009 15:41:51 +0800528_us-ascii Hi,





Does anyone has any idea about the below error and its resolution. Its reorg job and failed at Unload phase:





DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = REORG

DSNU1044I DSNUGTIS - PROCESSING SYSIN AS EBCDIC



DSNU050I DSNUGUTC - REORG TABLESPACE LIST EPW00052 SHRLEVEL REFERENCE LOG NO SORTDATA SORTKEYS NOSYSREC

COPYDDN(EPWLOCAP) DEADLINE CURRENT TIMESTAMP + 180 MINUTE TIMEOUT TERM STATISTI CS TABLE(ALL) INDEX(ALL) KEYCARD [...] 27690 208 23_R: SORT COMPONENT RC=1616_mocion@libero.it16_mocion@LIBERO.IT30_Thu, 7 May 2009 09:53:26 +0200802_UTF-8

Check message 'sort capacity exceeded' for instance.

Max Scarpa



----Messaggio originale---- Da: vikas.k.sahu@BANKOFAMERICA.COM Data: 07/05/2009 9.41 A: Ogg: SORT COMPONENT RC=16 --> -->

Hi,

Does anyone has any idea about the below error and its resolution. Its reorg job and failed at Unload phase:

DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = REORG DSNU1044I DSNUGTIS - PROCESSING SYSIN AS EBCDIC DSNU050I DSNUGUTC - REORG TABLESPACE LIST EPW00052 SHRLEVEL REFERENCE LOG NO SORTDATA SORTKEYS NOSYSREC .COPYDDN(EPWLOCAP) DEADLINE CURRENT TIMESTAMP + 180 MINUTE TIMEOUT TERM STATISTI CS TABLE(ALL) INDEX(ALL) KEYCARD .KEEPDICTIONARY .DSNU1044I DSNUGTIS - PROCESSING SYSLISTD AS EBCDIC .DSNU1039I DSNUGULM [...] 27899 257 24_Re: SORT COMPONENT RC=1613_Michael Ebert18_mebert@AMADEUS.COM30_Thu, 7 May 2009 07:54:07 +0000612_ISO-8859-1 You have to look at the UTPRINT SYSOUT. Most likely you ran out of space.

Dr. Michael Ebert DB2 & Oracle Database Administrator aMaDEUS Data Processing Erding / Munich, Germany







"Sahu, Vikas K" To DB2-L@WWW.IDUGDB2-L.ORG cc

bcc

Subject [DB2-L] SORT COMPONENT RC=16









"Sahu, Vikas K" Please respond to : DB2 Database Discussion list at IDUG Sent by: DB2 Data Base Discussion List 07-05-09 09:41 [...] 28157 448 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 7 May 2009 10:31:36 +0200312_us-ascii Hi Bill,

In a previous life we (well, OK, it might have been I) took the approach that an SP might fail for reasons beyond its control and deserved a fair chance at starting again. But if it repeatedly failed it suggested there was a more serious problem with the SP and should remain down. [...] 28606 110 51_Re: An informal poll on one aspect of query rewrite14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 7 May 2009 21:28:53 +1000349_US-ASCII Try

select dbname, tsname, ... from ( SELECT A.* , row_number () over(order by a.start_rba desc) as row_num FROM SYSIBM.SYSCOPY A WHERE A.DBNAME = 'DSNDB01' AND A.TSNAME = 'SPT01' ) as a where row_num = 1 ;

I would be interested in your result

This can be extended to, say, get the latest row for every tablespace [...] 28717 639 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures14_Barbara Koenen33_barbara.koenen.bgit@STATEFARM.COM30_Thu, 7 May 2009 04:24:48 -0700313_US-ASCII I've also seen something similar done in production. I think it was set up to automagically restart the production SP so many times in one day, and then leave it down after that limit was reached. An email to support analysts was produced for each restart, and a service ticket if it hit the limit. [...] 29357 44 51_Re: An informal poll on one aspect of query rewrite5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Thu, 7 May 2009 12:33:42 +0000553_windows-1252 I really did not mean to ask "how many different ways can this query be coded efficiently?" or "what is the best manner of coding this?"

I personally encounter many of these (this scenario and the bad manner of coding it) and I am thinking of writing up a requirement for DB2 to rewrite this. It is apparent to me than many developers do not yet know how to code this so if DB2 (on the mainframe) "knows" how to rewrite this (in somewhat the manner that DB2/LUW is doing), then many of these queries will be improved, simply by [...] 29402 276 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Thu, 7 May 2009 08:37:51 -0400443_us-ascii Hey Phils, et al,





Same abend / RC here at CW.





I think it's a conspiracy. You got a PMR, right?





Dave

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Wednesday, May 06, 2009 2:46 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS)





Phil G., [...] 29679 432 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)13_McDonald, Ken20_Ken_McDonald@BMC.COM30_Thu, 7 May 2009 08:35:02 -0500476_us-ascii FWIW... I had an SVC DUMP in my try. No matches on IBMLINK, but this was the symptom:

ABND=04E-00E70005,U=myuserid ,M=(N),C=910.RDS -SQL , M=DSNTFRCV,LOC=DSNXGRDS.DSNXGDT2:M100

Ken



________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Seibert, Dave Sent: Thursday, May 07, 2009 7:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS) [...] 30112 437 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 7 May 2009 15:39:27 +0200533_us-ascii Oi! Stop trying to help the competition! ;o)

Cheers,



Raymond PS. Same 00C70005 under DSNTEP2 here (DB2 9 NFM) and the fractionally more vague 'Abend 04E000 hex occurred processing command 'DSN '.' under SPUFI. Nothing in the *MSTR for either failure other than the standard DSN3201I.

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Seibert, Dave Sent: 07 May 2009 13:38 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS) [...] 30550 38 43_Re: Quick query tuning table profile script15_Patrick Bossman25_patrick.bossman@GMAIL.COM30_Thu, 7 May 2009 13:42:27 +0000517_ISO-8859-1 I am biased, but I like the table, index, indexed column report that Optimization Service Center provides.

You can see a sample in this presentation on slide 93 http://www.bwdb2ug.org/Presentations/Tuning%20with%20OSC%20II.ppt

There isn't currently a column report (for non-indexed columns) but it's under consideration.

When I sit down to perform analysis on a query I start with three things. 1. Annotated formatted query 2. Formatted, compressed explain output 3. Query report [...] 30589 720 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 7 May 2009 14:44:09 +0100643_us-ascii <>





I guess a PMR is in order - unless anyone spots whatever really silly mistake I have made?





Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: phil.grainger@ca.com





2009 IBM Data Champion





Ditton Park Riding Court Road Datchet Slough SL3 9LL





CA plc 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. [...] 31310 262 42_Congratulations to my colleague Kim May...22_Frank C. Fillmore, Jr.19_tfgpublic@GMAIL.COM30_Thu, 7 May 2009 09:49:33 -0400711_US-ASCII for being named an IBM Data Champion!

http://www-01.ibm.com/software/data/champion/profiles/may.html





For more information about the Data Champion program:

http://www-01.ibm.com/software/data/champion





All the best,

Frank





+-------------------------------------+------------------------------------- --------+

| Frank C. Fillmore, Jr. | Office: 410.465.6335 Mobile: 410.963.0416 |

| The Fillmore Group, Inc. (TFG) | Facsimile: 888.828.4717 |

| 8501 LaSalle Road; Suite 318 | IBM Training: 800-TFG-RDBMs (800.834.7326) |

| Towson, Maryland 21286 | URL: http://thefillmoregroup.com | [...] 31573 808 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Thu, 7 May 2009 09:48:41 -0400475_us-ascii In my humble, arrogant opinion, with very few exceptions, whenever SQL causes an abend in DB2, it's worthy of a PMR.

If DB2 can't gracefully tell me I'm a hoser, I figure IBM wants to know about it.





Dave

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: Thursday, May 07, 2009 9:44 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS) [...] 32382 49 22_Insert BLOB using REXX11_Akash Pilot13_akash@USA.COM30_Thu, 7 May 2009 13:51:12 +0000521_windows-1252 Dear listers, I have a need to insert a BLOB column into a DB2 table using REXX script (DSNREXX, DB2-V8 on z/OS). If anyone has done this before would they be kind enough to share the the relevant portions of the REXX syntax that shows a way to read in a BLOB value which in my case can be an excel spreadsheet or a PDF that is already uploaded to host mainframe in binary format, into a REXX variable (not sure if I can use a locator instead) and insert it into the DB2 table. Basically here is what I [...] 32432 622 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Thu, 7 May 2009 09:51:24 -0400374_us-ascii Cheers, my kiwi competitor pal.

I was trying to help him waste his time and be less productive.





By the way, the abend msg with the Rc & the SVC dump msgs are in the DBM1 address sysmsgs.





By another way, my son is trying to come up with 2.5 hours of New Zealand only music for his radio show this afternoon. [...] 33055 667 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 7 May 2009 16:07:28 +0200544_us-ascii Yep, similar to what I've seen. If memory serves, the main (certainly a) driver for automating this was to stop Developers coming 'round to the DBA group to get them to start SPs that DB2 had stopped 'cause they'd failed. Think in Dev STORMXAB was, like you've seen, pretty high. I mean, I love Dev folk dearly but they can be pretty relaxed about error handling. And fair enough in Dev too, while they're testing general functionality. And sometimes (whisper it) it ain't even their fault the SP AbEnds. Shocking theory, I know. [...] 33723 102 51_Re: An informal poll on one aspect of query rewrite14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 7 May 2009 10:09:40 -0400423_us-ascii Larry,

I've seen a fair amount of this from... wait for it...





*** Other DBAs, in production code to generate executable DDL ***





I'm going off to do some review of their code -- worth noting, however, that I'm talking about situations where the SQL in question is executed about twenty times per month, so we're not talking about a lot of CPU savings here. [...] 33826 670 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 7 May 2009 16:14:30 +0200379_us-ascii Ah, look at that; so it is. Didn't spot it; just relied on D D,T to find the dump info.

Brief list coming.

Cheers,



Raymond

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Seibert, Dave Sent: 07 May 2009 14:51 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] INSTEAD OF triggers.... (DB2 9 for z/OS) [...] 34497 808 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures11_Smock, Dale22_dsmock@RANDOMHOUSE.COM30_Thu, 7 May 2009 10:25:17 -0400576_US-ASCII Or if you have BMC Mainview AutoOp, you can also trap the error message (like DSNX906I) to notify the DBAs and/or take appropriate action (like starting the SP).

Dale Smock RandomHouse ----------------------------------------------------------------------------- ------------------------------------------------------------------

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bell, Raymond Sent: Thursday, May 07, 2009 10:07 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Z/OS 1.10, DB2 V8.1, Stored procedures [...] 35306 252 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 7 May 2009 16:33:12 +0200446_us-ascii Man, at this rate I'm gonna run out of cheques... ;o)

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Smock, Dale Sent: 07 May 2009 15:25 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Z/OS 1.10, DB2 V8.1, Stored procedures

Or if you have BMC Mainview AutoOp, you can also trap the error message (like DSNX906I) to notify the DBAs and/or take appropriate action (like starting the SP). [...] 35559 64 26_Re: Insert BLOB using REXX10_Todd Burch17_toddburch@MAC.COM30_Thu, 7 May 2009 09:33:13 -0500464_US-ASCII Have you considered using a File Reference Variable for your BLOB? When using a FRV, you don't even have to read it in to a rexx variable, as long as it is readable by BSAM.

I don't have a REXX example today, but I am working on a (series or one, I haven't decided) technote that shows how to use LOBS in a application, with an emphasis on how to work with LOBs that exceed the host language's capacity in terms of maximum sizes of host vars. [...] 35624 439 58_[FLUFF] RE: [DB2-L] Z/OS 1.10, DB2 V8.1, Stored procedures14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 7 May 2009 10:48:30 -0400627_us-ascii You've never heard of PayPal?





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bell, Raymond Sent: Thursday, May 07, 2009 10:33 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Z/OS 1.10, DB2 V8.1, Stored procedures





Man, at this rate I'm gonna run out of cheques... ;o)





From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Smock, Dale Sent: 07 May 2009 15:25 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Z/OS 1.10, DB2 V8.1, Stored procedures [...] 36064 101 26_Re: Insert BLOB using REXX11_Akash Pilot13_akash@USA.COM30_Thu, 7 May 2009 15:13:56 +0000275_windows-1252 Todd, Thanks for your response. I would definitely use a FRV if I know the syntax. Unfortunately I cannot find it any of the manuals or web resources that I have been searching. How to declare a BLOB variable or FRV in Rexx for z/OS I guess is my question. [...] 36166 124 21_Re: Rebinds and SPT0114_Matthew Donald26_matthew.b.donald@GMAIL.COM30_Fri, 8 May 2009 01:15:38 +1000646_ISO-8859-1 Willie has an interesting blog entry on DB2v8 conversions, rebinding and SPT01

On Thu, May 7, 2009 at 12:13 PM, Donna Domovic wrote:

> Hello, > > We've recently upgraded to DB2 v8 CM and are now running rebinds on some > of our application packages to see if we can gain any CPU savings and take > advantage of the new optimizer. While running rebinds in our test > environment, we ran out of space on the SPT01 and had to add another > dataset. > > It's been a while and I can't seem to find [...] 36291 51 21_Re: copytocopy advice10_Nicola Nur17_nick_nur@YAHOO.CA30_Thu, 7 May 2009 08:19:37 -0700366_us-ascii Hi colleagues

We are at DB2 z/OS v8.1 NFM.

We have the following situation that I need your advice on if possible: 1.We run Incremental Image Copy of a tablespace and put the output dataset on DASD.

2.The dataset produced in job 1 is only produced if there were changes in the table otherwise the dataset in job 1 is not produced. [...] 36343 112 26_Re: Insert BLOB using REXX14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 7 May 2009 11:22:42 -0400448_us-ascii Todd,

That's a fascinating concept of which I've never heard before. Is it found in documentation somewhere? Or can you explain it/point us to working examples of syntax?

--Phil Sevetson

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Todd Burch Sent: Thursday, May 07, 2009 10:33 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Insert BLOB using REXX [...] 36456 115 20_question on DSNTIJP915_Chris Hoelscher21_choelscher@HUMANA.COM30_Thu, 7 May 2009 11:33:58 -0400489_US-ASCII DB2 V8.1 z/OS 1.9

I am running DSNTIJP9 in hopes of someday migrating to V 9 - report 9 lists tables with incomplete definitions

L = An auxiliary table or auxiliary index has not been defined for a LOB column. P = The table lacks a required primary index R = The table lacks a required index on a row ID. U = The table lacks a required index on a unique key. V = An error occurred when an attempt was made to regenerate the internal representation of the view. [...] 36572 349 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures11_Smock, Dale22_dsmock@RANDOMHOUSE.COM30_Thu, 7 May 2009 11:35:49 -0400385_US-ASCII It may be your last cheque to me, so can you make it a big one? I'm out of a job the end of May. All of the BMG North America divisions have been sold off and outsourced. The only thing left will be the RandomHouse Book division which doesn't run DB2. Raymond: you are on your own unless I find another DBA position, which are almost extinct in this part of the country. [...] 36922 147 26_Re: Insert BLOB using REXX14_Riehle, Robert24_Robert.Riehle@SCHWAB.COM30_Thu, 7 May 2009 08:29:45 -0700482_us-ascii Try Sample8b of SG247270 redbook additional material



I am telecommuting today. My home phone number is 480-471-0986. Thanks and have a great day, Bob Riehle Senior Staff MFTS 480-471-0986 (h) 602-361-9252 (c)



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Thursday, May 07, 2009 8:23 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Insert BLOB using REXX [...] 37070 156 48_Re: Weird - (Insert from Select ) in DB2 z OS v813_BOND Victor A30_Victor.A.BOND@ODOT.STATE.OR.US30_Thu, 7 May 2009 08:46:16 -0700344_iso-8859-1 How about a trigger on the table that might modify 1 or both fields?

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: Wednesday, May 06, 2009 9:21 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Weird - (Insert from Select ) in DB2 z OS v8 [...] 37227 138 26_Re: Insert BLOB using REXX9_Don Leahy19_don.leahy@LEACOM.CA30_Thu, 7 May 2009 11:39:26 -0400600_ISO-8859-1 I don't think you can use FRV in Rexx until DB2 V9.

On Thu, May 7, 2009 at 11:22 AM, Sevetson, Phil wrote: > Todd, > > That's a fascinating concept of which I've never heard before. Is it > found in documentation somewhere? Or can you explain it/point us to > working examples of syntax? > > --Phil Sevetson > > -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On > Behalf Of Todd Burch > Sent: Thursday, May 07, 2009 10:33 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] Insert BLOB using REXX > > [...] 37366 448 42_Re: Z/OS 1.10, DB2 V8.1, Stored procedures13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 7 May 2009 17:52:30 +0200506_us-ascii Last? First as well. And making it a large one suits me. The bigger it is, the higher it'll bounce. Good luck with your future employment and if the cheque hasn't arrived by the time you leave I probably shouldn't have sent it via surface mail.

Cheers,



Raymond

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Smock, Dale Sent: 07 May 2009 16:36 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Z/OS 1.10, DB2 V8.1, Stored procedures [...] 37815 332 44_Re: INSTEAD OF triggers.... (DB2 9 for z/OS)12_Troy Coleman25_troy.coleman@SOFTBASE.COM30_Thu, 7 May 2009 10:55:49 -0500 38148 159 26_Re: Insert BLOB using REXX10_Todd Burch17_toddburch@MAC.COM30_Thu, 7 May 2009 11:04:16 -0500604_US-ASCII Yes, it is a DB2 9 feature.

Todd



On May 7, 2009, at 10:39 AM, Don Leahy wrote:

I don't think you can use FRV in Rexx until DB2 V9.

On Thu, May 7, 2009 at 11:22 AM, Sevetson, Phil wrote: > Todd, > > That's a fascinating concept of which I've never heard before. Is it > found in documentation somewhere? Or can you explain it/point us to > working examples of syntax? > > --Phil Sevetson > > -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On > Behalf Of Todd Burch > Sent: Thursday, [...] 38308 216 48_Re: Weird - (Insert from Select ) in DB2 z OS v814_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 7 May 2009 17:16:08 +0100364_iso-8859-1 Oooh - good point

It might be interesting to try

SELECT * FROM FINAL TABLE ( INSERT INTO TABLE11 ( COL1, COL2 ) SELECT :HV-COL1 :HV-COL2 FROM TABLE11 WHERE COL1 = :HV-COL1 AND COL2 = :HV-COL2 )

Phil Grainger CA Senior Principal Product Manager Phone: +44 (0)1753 577 733 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com [...] 38525 218 26_Re: Insert BLOB using REXX11_Akash Pilot13_akash@USA.COM30_Thu, 7 May 2009 16:17:48 +0000337_windows-1252 Does DB2-V8 support any form of BLOB insert using REXX? The samples I have seen on the web are for LUW. Haven't seen anything for z/OS. Is there any place where I can find syntax for declaring LOB variables in REXX? Someone suggested Redbook sg247270 but it doesn't have any information for REXX that I am looking for. [...] 38744 187 26_Re: Insert BLOB using REXX14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 7 May 2009 12:50:02 -0400497_us-ascii Got it, thanks

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Riehle, Robert Sent: Thursday, May 07, 2009 11:30 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Insert BLOB using REXX

Try Sample8b of SG247270 redbook additional material



I am telecommuting today. My home phone number is 480-471-0986. Thanks and have a great day, Bob Riehle Senior Staff MFTS 480-471-0986 (h) 602-361-9252 (c) [...] 38932 192 24_Re: question on DSNTIJP912_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM30_Thu, 7 May 2009 13:28:58 -0400492_US-ASCII Chris, You might want to look at PK73081 and PK73441 as they talk about report 9 changes. HTH Jeff





Chris Hoelscher Sent by: DB2 Data Base Discussion List 05/07/2009 11:34 AM Please respond to DB2 Database Discussion list at IDUG



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject [DB2-L] question on DSNTIJP9











DB2 V8.1 z/OS 1.9 [...] 39125 682 61_[Semi-OT][All Platforms] Info for the [soon to be] Unemployed14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 7 May 2009 13:49:07 -0400414_utf-8 Some may consider this off topic; in a technical sense this is correct, but there's a real need for this information among a dismayingly large number of us, so I'm posting it anyway.





It's come to my attention that there are a number of currently or imminently unemployed US DB2 DBAs on the list. Some of them (you) haven't looked for work since the beginning of the Internet era. [...] 39808 74 24_Re: question on DSNTIJP915_Chris Hoelscher21_choelscher@HUMANA.COM30_Thu, 7 May 2009 13:48:58 -0400534_US-ASCII Jeff - I thank you - PS - love those new Italian subs

Chris Hoelscher Senior IDMS & DB2 Database Administrator 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. [...] 39883 25 41_Doug Holliday/Alcan is out of the office.13_Doug Holliday23_doug.holliday@ALCAN.COM30_Thu, 7 May 2009 13:53:11 -0400521_US-ASCII I will be out of the office starting 05/07/2009 and will not return until 05/08/2009.

I will respond to your message when I return.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 39909 428 65_Re: [Semi-OT][All Platforms] Info for the [soon to be] Unemployed9_Chris Tee23_Chris.Tee@SENTENIAL.COM30_Thu, 7 May 2009 19:19:04 +0100379_UTF-8 www.jobserve.com is the only website I’ve used for the last 11 years as a contractor in the UK. It has jobs worldwide.





From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: 07 May 2009 19:02 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] [Semi-OT][All Platforms] Info for the [soon to be] Unemployed [...] 40338 363 24_Re: SORT COMPONENT RC=1615_Kalena, Michael27_Michael.Kalena@JPMORGAN.COM30_Thu, 7 May 2009 14:50:37 -0400321_US-ASCII We had/have an awful time with v8 Utilities now that DFSORT is used. We ended up opening PMRs with IBM for DB2 and DFSort to resolve them. We got PTFs to apply along with our z/OS system folks making changes to sort settings. The rest of the shop uses SyncSort so DFSORT settings only effect DB2 Utilities. [...] 40702 352 65_Re: [Semi-OT][All Platforms] Info for the [soon to be] Unemployed12_Troy Coleman25_troy.coleman@SOFTBASE.COM30_Thu, 7 May 2009 14:00:46 -0500 41055 28 25_Re: Timestamp to contoken9_Jim Ruddy18_jaruddy@US.IBM.COM30_Thu, 7 May 2009 19:02:49 +0000408_ISO-8859-1 The question no one has asked is - "why do you want to know?" - just curious or trying to solve a problem (like figure what the tokens in the -805 or -818 mean in terms of precompiled date)?

Jim Ruddy DB2 for z/OS Advanced Technology

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 41084 52 25_Re: Timestamp to contoken4_Mike20_ekspert123@GMAIL.COM30_Thu, 7 May 2009 21:11:28 +0200617_ISO-8859-1 Jim.. for example because of PK83078, where is advised to check if the current log RBA is greater then z/OS system timestamp value (contoken/STCK to timestamp comparison)

Mike

2009/5/7 Jim Ruddy : > The question no one has asked is - "why do you want to know?" - just curious > or trying to solve a problem (like figure what the tokens in the -805 or > -818 mean in terms of precompiled date)? > > Jim Ruddy > DB2 for z/OS Advanced Technology > > _____________________________________________________________________ > > * IDUG 09 Denver, CO, USA * May 11-15, 2009 * [...] 41137 573 65_Re: [Semi-OT][All Platforms] Info for the [soon to be] Unemployed13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Thu, 7 May 2009 14:27:51 -0500620_UTF-8 Check out...

http://mainframejobs.zjournal.com/a/jbb/find-jobs

And although Phil already mentioned it, don't underestimate the power of LinkedIn... at a minimum, make sure all of your information is up to date.

Willie

Sevetson, Phil wrote: > > Some may consider this off topic; in a technical sense this is > correct, but there’s a real need for this information among a > dismayingly large number of us, so I’m posting it anyway. > > > > It’s come to my attention that there are a number of currently or > imminently unemployed US DB2 DBAs on the list. Some of them (you) > [...] 41711 160 24_Re: SORT COMPONENT RC=1616_mocion@libero.it16_mocion@LIBERO.IT30_Thu, 7 May 2009 14:46:48 -0500578_windows-1252 We had problems with dfsort and big tablespace as well (max capacity exceeded error U0046) and some other errors.HAd to allocate big sort files to cover all cases.

When I read dfsort should be standard sort for utilities I thought we could have problems as DFSORT is ra z/OS componente and not a DB2 part (there's somewhat a 'detachment'). Bugs followed and as far as I can see usage is still not perfect in V8 after some years. Moreover you cannot easily convert old jcl or skeletons (well tuned in years) with new dfsort use, at least in short times. [...] 41872 722 65_Re: [Semi-OT][All Platforms] Info for the [soon to be] Unemployed10_Roger Hecq18_Roger.Hecq@UBS.COM30_Thu, 7 May 2009 16:11:44 -0400382_us-ascii Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...] 42595 769 65_Re: [Semi-OT][All Platforms] Info for the [soon to be] Unemployed13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Thu, 7 May 2009 13:37:33 -0700567_iso-8859-1 I got my current job using Dice.com and monster.com. I posted my resume in both places, and the recruiter found me in both place, so I can't say which one got me the job. I have had very unsatisfactory results applying for specific positions on the internet. Employers will often post jobs to fulfill legal requirements (H1B or EEO) when they have already chosen a candidate, or they'll post a DBA postition and neglect to say which DBMS (I have gone on interviews only to find out they are running Oracle or Adabas). One thing is for certain: If the [...] 43365 361 65_Re: [Semi-OT][All Platforms] Info for the [soon to be] Unemployed14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 7 May 2009 16:46:34 -0400500_us-ascii Cathy etal,

Nit pick: The site you're referring to is at URL www.theladders.com - the other site actually sells ladders :-)

--Phil Sevetson





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Taddei, Cathy Sent: Thursday, May 07, 2009 4:38 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [Semi-OT][All Platforms] Info for the [soon to be] Unemployed [...] 43727 983 65_Re: [Semi-OT][All Platforms] Info for the [soon to be] Unemployed17_Janes, Ray {PBSG}18_ray.janes@PBSG.COM30_Thu, 7 May 2009 16:03:54 -0500382_iso-8859-1 Ditto on IBM = Black Hole. An Ex-IBM friend of mine, related how the sad/funny part was IBM didn't seem to get it that getting back to a person seeking work, six months after they applied, and then wondering why most of them already had a job.....

Set up a Hotmail or Yahoo or Gmail account. Use that as an email contact when working on Dice/Monster et. all. [...] 44711 44 65_Re: [Semi-OT][All Platforms] Info for the [soon to be] Unemployed13_Irwin Deutsch28_ideutsch@PRINCESSCRUISES.COM30_Thu, 7 May 2009 14:22:48 -0700528_US-ASCII My impression from talking to a friend at IBM is that they usually hire from inside, so it's a long shot anyway... But still common courtesy ....

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 44756 124 39_Re: R: Rotate Partition & Compression %9_Dan Chatt15_dchatt@FNNI.COM30_Thu, 7 May 2009 21:59:58 +0000645_windows-1252 The table is populated with inserts not a load. This weekend I will try to reorg without the KEEPDICTIONARY and see if a rebuilt dictionary helps the compression percentage.

On Thu, 7 May 2009 08:39:49 +0200, mocion@libero.it wrote:

>Your compression % apperas relatively low as I presume you load your data with >LOAD utility which is less efficient. And loaded data could be difefrent and >data used for dictionary could be not good for compression and/OR with a >population of data not well sampled. > >I'd rebuild dictionary with reorg which >is more fficient in data sampling for building [...] 44881 32 26_DELETE CASCADE Parallelism9_Dan Chatt15_dchatt@FNNI.COM30_Thu, 7 May 2009 22:21:49 +0000412_windows-1252 I have a parent table and several dependent tables defined with DELETE CASCADE. If a parent is deleted do the deletes for the children run in parallel? How can I tell the degree of parallelism for the deletes on the dependent tables? Is there a way to control the degree of parallelism on these cascaded deletes or is the parallelism all tied to how parallel the tasks run on the parent table? [...] 44914 224 26_Re: Insert BLOB using REXX0_33_Paul.Z.Fegan@TRANSPORT.QLD.GOV.AU30_Fri, 8 May 2009 09:32:50 +1000721_us-ascii I can't help with DB2 V8 but here's one that I've got running on my PC which attaches to DB2 V9 on z/OS and inserts a whole pile of JPG files into a test image table

/* REXX */

MAIN: /*********************************************************/ /* */ /*********************************************************/ SAY 'Start ' TIME('E') mycstmr = 4001 IF RxFuncQuery('SQLDBS') <> 0 THEN rcy = RxFuncAdd( 'SQLDBS', 'db2ar', 'SQLDBS' ) IF RxFuncQuery('SQLEXEC') <> 0 THEN rcy = RxFuncAdd( 'SQLEXEC', 'db2ar', 'SQLEXEC' ) CALL SQLEXEC 'DECLARE :myfile LANGUAGE TYPE BLOB FILE' CALL Checkerr 'DECLARE' CALL SQLEXEC 'CONNECT TO DTAC USER PZFEGAN USING ?????????' CALL Checkerr 'CONNECT' CALL List_Files [...]