1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l August 2006, week 4 2 77 35_Re: Reorging a Base table in DB2 V722_Cuneyt Goksu (Usa.net)19_cuneytgoksu@USA.NET31_Tue, 22 Aug 2006 13:46:29 +0300454_iso-8859-9 Hi,

I have a similar structure. Reorg does not work for extent consolidation or space reuse.

I suggest COPY / RECOVER method on partition level.

It works with some appliaction outage.



Iyi calismalar, Best Regards, Saludos

------------------------- Cuneyt Goksu DB2 Specialist UDB for z/OS V8 Certified +90 555 2092562 +90 532 2540342 ------------------------- Think Enterprise, Think Mainframe! [...] 80 74 77_Press Release - Recovery Knowledge and DBA Sistemi Italy announce Partnership0_22_DB2information@AOL.COM29_Tue, 22 Aug 2006 10:17:15 EDT436_US-ASCII Recovery Knowledge and DBA Sistemi Italy announce Distribution Agreement. Recovery Knowledge www.recoveryknowledge.com the developer of GENDB2 software product (which simplifies and automates the DB2 MVS, OS/390, z/OS recovery process Offsite and Onsite and DBA Sistemi Italy http://www.dbasistemi.it announce a distribution agreement under which DBA Sistemi Italy will market the GENDB2 software in the country of Italy. [...] 155 31 25_DSN1COPY PARTITIONED CLOB9_Raj Ghose17_raj.ghose@RBC.COM31_Tue, 22 Aug 2006 15:08:13 -0500334_- Hi List, We are using DSN1COPY utility with Image copies of the base and auxiliary table from system(IST) to System(TEST) which are physically apart.

Image copies of all 4 partitions of the base tablespace is on a single Image copy dataset, but the Auxiliary tablespace have a separate Image copy for each partition(4). [...] 187 65 29_Re: DSN1COPY PARTITIONED CLOB12_HEPP SHERY C17_schepp@SRPNET.COM31_Tue, 22 Aug 2006 13:22:08 -0700555_us-ascii Raj- a couple of quick questions. Did you run a rebuild index following the dsn1copy job? Also you may want to run a check on the base table to check the auxiliary table. Here's the syntax.

CHECK DATA TABLESPACE base.tsname SCOPE AUXONLY AUXERROR REPORT

Good luck-

Regards, Shery



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Raj Ghose Sent: Tuesday, August 22, 2006 1:08 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DSN1COPY PARTITIONED CLOB [...] 253 44 29_Re: DSN1COPY PARTITIONED CLOB13_Johnny Wilder28_Johnny.Wilder@MOTION-IND.COM31_Tue, 22 Aug 2006 15:23:07 -0500465_US-ASCII -----Original Message----- Make sure you rebuild the indexes.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Raj Ghose Sent: Tuesday, August 22, 2006 3:08 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DSN1COPY PARTITIONED CLOB

Hi List, We are using DSN1COPY utility with Image copies of the base and auxiliary table from system(IST) to System(TEST) which are physically apart. [...] 298 153 29_Re: DSN1COPY PARTITIONED CLOB7_J Anand34_AnandJayachandran@FREIGHTLINER.COM31_Tue, 22 Aug 2006 13:25:18 -0700317_us-ascii Select from base table excluding the LOB column. This will work. You cannot do the following actions on a AUX TS. -766 THE OBJECT OF A STATEMENT IS AN AUXILIARY TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED

Explanation: An auxiliary table was named in one of the following statements: [...] 452 65 16_DB2 V7 - zOS 1.615_Fletcher, Kevin26_KEVIN_FLETCHER@CONSECO.COM31_Tue, 22 Aug 2006 16:54:06 -0400663_us-ascii All,

I am trying to find the redbook migration guide for going from DB2 V7.1 to V8. Any help would be appreciated.

Thanks,

Fletch 817-3545







--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 518 70 20_Re: DB2 V7 - zOS 1.60_19_csutfin@AMSOUTH.COM31_Tue, 22 Aug 2006 17:37:50 -0500667_us-ascii try SG24-6079-00.

Everything You wanted to know........

Carol Sutfin Corporate DBA AmSouth Bank (205)261-5214 csutfin@amsouth.com







"Fletcher, Kevin" To Sent by: "DB2 DB2-L@WWW.IDUGDB2-L.ORG Data Base cc Discussion List" [DB2-L] DB2 V7 - zOS 1.6



08/22/2006 03:54 PM



Please respond to "DB2 Database Discussion list at IDUG"











All,

I am trying to find the redbook migration guide for going from DB2 V7.1 to V8. Any help would be appreciated. [...] 589 15 39_DB2 V7 z/OS 01.04.00 - DSSIZE and DFSMS12_Adam Baldwin19_y0027528@ES.IBM.COM31_Wed, 23 Aug 2006 03:19:43 -0500363_- I'm puzzled! We're running DB2 V7 on z/OS 01.04.00 with DFSMS at 1.3. All of the documentation that I have seen says that in order to specify DSSIZE > 4Gb DFSMS must be at 1.5. We have tablespaces > 4Gb with VS-E VSAM.

What am I missing? How are we managing this with SMS1.3? - it's been a long day already so I imagine that it's something obvious. [...] 605 80 43_Re: DB2 V7 z/OS 01.04.00 - DSSIZE and DFSMS13_William Huang24_hhuang@DCCSH.ICBC.COM.CN31_Wed, 23 Aug 2006 16:46:18 +0800465_GB2312 Adam

Tablespace dataset size > 4G is a story, and DB2 can access the data part over 4G is a different story. Just my guess. And you'd better do LISTC ALL to confirm the HI-A-RBA and HI-U-RBA.







Adam Baldwin ·¢¼þÈË: DB2 Data Base Discussion List 2006-08-23 16:19



ÊÕ¼þÈË DB2-L@WWW.IDUGDB2-L.ORG ³­ËÍ

Ö÷Ìâ [DB2-L] DB2 V7 z/OS 01.04.00 - DSSIZE and DFSMS [...] 686 15 43_Re: DB2 V7 z/OS 01.04.00 - DSSIZE and DFSMS12_Adam Baldwin19_y0027528@ES.IBM.COM31_Wed, 23 Aug 2006 03:49:30 -0500596_- One of the VSAMs in question has:

HI-A-RBA------5074698240 HI-U-RBA------4909174784

So DB2 is accessing 4.5Gb

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 702 42 43_Re: DB2 V7 z/OS 01.04.00 - DSSIZE and DFSMS10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT31_Wed, 23 Aug 2006 12:00:21 +0200352_US-ASCII Adam, I had DB2 v6 and os/390 2.10 when I defined DSSIZE > 4 Gb (and defined as EA in SMS as regards these tablespaces/indexes). Here I had DFSMS 1.5.

You have SMS 1.3 but it's related to z/OS which is an higher level.

HTH

Max Scarpa

--------------------------------------------------------------------------------- 745 23 28_db2 Backup kills our machine16_Squeak Smalltalk20_wallenberg@GMAIL.COM31_Wed, 23 Aug 2006 13:28:39 +0200700_ISO-8859-1 Hi,

We run one backup on our new machine. The problem is that "db2 backup" creates a big load on our machine.

How can I slow down the backup so that we are not killing the machine ?

Best Regards W-

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 769 38 32_Re: db2 Backup kills our machine13_Mark Horrocks22_agentlease@HOTMAIL.COM31_Wed, 23 Aug 2006 11:34:48 +0000691_- What version of UDB are you using and what platform?



>From: Squeak Smalltalk >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: [DB2-L] db2 Backup kills our machine >Date: Wed, 23 Aug 2006 13:28:39 +0200 > >Hi, > >We run one backup on our new machine. >The problem is that "db2 backup" creates a big load on our machine. > >How can I slow down the backup so that we are not killing the machine ? > >Best Regards >W- > >--------------------------------------------------------------------------------- >Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home >page [...] 808 57 32_Re: db2 Backup kills our machine16_Squeak Smalltalk20_wallenberg@GMAIL.COM31_Wed, 23 Aug 2006 14:07:26 +0200706_ISO-8859-1 We use 8.1.6 and here is the command

BACKUP DATABASE Proddb ONLINE TO "/backup","/backup","/backup","/backup","/backup","/backup" WITH 1 BUFFERS BUFFER 4096 PARALLELISM 1 WITHOUT PROMPTING;

Best Regards W.



On 8/23/06, Mark Horrocks wrote: > What version of UDB are you using and what platform? > > > >From: Squeak Smalltalk > >Reply-To: DB2 Database Discussion list at IDUG > >To: DB2-L@WWW.IDUGDB2-L.ORG > >Subject: [DB2-L] db2 Backup kills our machine > >Date: Wed, 23 Aug 2006 13:28:39 +0200 > > > >Hi, > > > >We run one backup on our new machine. > >The problem is that "db2 backup" [...] 866 95 32_Re: db2 Backup kills our machine13_Mark Horrocks22_agentlease@HOTMAIL.COM31_Wed, 23 Aug 2006 12:17:11 +0000769_- Hi,

Two options,

Reduce the number of streams you are backing up to i.e

/backup

and use the backup option UTIL_IMPACT_PRIORITY



But my guess it's the number of directories you are backing up to is killing it.

Many Thanks,

Mark Horrocks.

>From: Squeak Smalltalk >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Re: [DB2-L] db2 Backup kills our machine >Date: Wed, 23 Aug 2006 14:07:26 +0200 > >We use >8.1.6 >and here is the command > >BACKUP DATABASE Proddb ONLINE TO >"/backup","/backup","/backup","/backup","/backup","/backup" WITH 1 >BUFFERS BUFFER 4096 PARALLELISM 1 WITHOUT PROMPTING; > >Best Regards >W. > [...] 962 41 0_6_reutso23_reutso@MDOT.STATE.MD.US31_Wed, 23 Aug 2006 08:40:53 -0400559_us-ascii www.marylandtransportation.com



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 1004 112 32_Re: db2 Backup kills our machine16_Squeak Smalltalk20_wallenberg@GMAIL.COM31_Wed, 23 Aug 2006 14:55:01 +0200633_ISO-8859-1 Hi,

With one stream still high load.



Will try with UTIL_...

Best R. W.

On 8/23/06, Mark Horrocks wrote: > Hi, > > Two options, > > Reduce the number of streams you are backing up to i.e > > /backup > > and use the backup option UTIL_IMPACT_PRIORITY > > > But my guess it's the number of directories you are backing up to is killing > it. > > Many Thanks, > > Mark Horrocks. > > >From: Squeak Smalltalk > >Reply-To: DB2 Database Discussion list at IDUG > >To: DB2-L@WWW.IDUGDB2-L.ORG > >Subject: Re: [DB2-L] [...] 1117 14 24_Reopened Job Annoucement6_reutso23_reutso@MDOT.STATE.MD.US31_Wed, 23 Aug 2006 08:48:00 -0400518_us-ascii

www.marylandtransportation.com

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 1132 123 20_Re: DB2 V7 - zOS 1.615_Fletcher, Kevin26_KEVIN_FLETCHER@CONSECO.COM31_Wed, 23 Aug 2006 09:04:33 -0400487_us-ascii Carol,

This is just what I needed. Thanks for taking the time to send the reference....time to RTFM :-)

Fletch 817-3545







-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of csutfin@AMSOUTH.COM Sent: Tuesday, August 22, 2006 6:38 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V7 - zOS 1.6



try SG24-6079-00.

Everything You wanted to know........ [...] 1256 148 32_Re: db2 Backup kills our machine11_Davies, Ian31_Ian.Davies@CGI.HEALTH.GOV.AB.CA31_Wed, 23 Aug 2006 07:20:42 -0600401_us-ascii What is your Utility Heap Size and how big is it compared to your server memory?

Cheers, Ian.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Squeak Smalltalk Sent: August 23, 2006 06:55 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] db2 Backup kills our machine

Hi,

With one stream still high load. [...] 1405 90 29_Re: DSN1COPY PARTITIONED CLOB9_Raj Ghose17_raj.ghose@RBC.COM31_Wed, 23 Aug 2006 08:53:25 -0500333_- Hi Shery, Thanks ever so much for your help.

The "CHECK" utility was the answer. The report indicated that the INDEX for the AUXILIARY table was missing the index entries. I ran the REBUILD utility against the AUX. indexes and now the SPUFI works fine.

Thanks to all who took their time to reply to my question. [...] 1496 28 44_Abend S0C4 DB2V7.1 + Z/OS1.5 after migration15_Bertrand Barret25_bertrand.barret@ORANGE.FR31_Wed, 23 Aug 2006 14:12:26 +0000385_UTF-8 Hello, We have just migrated from Z/OS1.2 to Z/OS1.5 and we are facing abend on DB2. A simple query (select or delete) is abending with S0C4 code. (thru native SQL or PLI pgm)

We have seen the same problem on the DB2-L archives on April 2005 (sender Jaime Fernandez)

ABND=0C4-00000010,U=UAA0105 ,C=XYR00.710.BMC -DSNB1SWS, M=DSNTFRCV,LOC=DSNBBM .DSNB1SWS+066A [...] 1525 158 8_question29_Goodwin, Stanley CTR DISA CDB26_GoodwinS.ctr@MECH.DISA.MIL31_Wed, 23 Aug 2006 10:56:37 -0400320_us-ascii It seems that when we run a DB2 LOAD RESUME YES, and there are duplicates that the duplicates are not being removed from the table. According to the error message DSNU344I that the records should be removed at the end of processing. Any one shed light on this process..





Stan Goodwin [...] 1684 223 12_Re: question15_Gaston, Raymond17_GastonRay@ORU.COM31_Wed, 23 Aug 2006 11:03:36 -0400331_us-ascii Stan...

As I understand it, if record A is already in the table and record A' is "loaded" into the table then record A remains and record A' is discarded. I there is no record type "A" in the table and both A and A' are loaded then "both" records are discarded. (Note: where A and A' are duplicate in nature). [...] 1908 42 12_Re: question10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT31_Wed, 23 Aug 2006 17:59:37 +0200370_US-ASCII A LOAD with duplicated records leaves the record that ist on the table and duplicated records from input are discarded. This is done in INDEXVAL phase. If your index have, for instance, space problem during insert phase before INDEXVAL and you recover it you'll find duplicate records when rebuilding this index(es) as there are orphans rows on the table. [...] 1951 23 32_MVS/DB2 DB2 managed Table spaces2_Su22_subha_sivaji@YAHOO.COM31_Wed, 23 Aug 2006 10:52:30 -0500288_- Hi Listers,

I have a situation where I have to increase the size of an Index space with out stopping the TS, since it is a busy Tablespace - DB2 managed.

For the Table space, I am able to do the ALTER on primary and secondary QTYs, but not on Index without stopping. [...] 1975 28 12_Re: question10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT31_Wed, 23 Aug 2006 18:01:06 +0200223_US-ASCII And yes if both record are in input file they are both discarded....DB2 trusts only in what it has (or haven't) in the table.

--------------------------------------------------------------------------------- 2004 120 23_DB2 Performance on z/OS12_Steve DeGiso16_sdegiso@TACT.COM31_Wed, 23 Aug 2006 12:07:56 -0400402_iso-8859-1

Whether your priority is Tuning your Dynamic SQL, Preventing Undesirable Access Path Changes, Optimizing your Index Design or Managing your Catalog Statistics,

EZ-DB2® is the must have family of products for you. EZ-DB2 with its unique patented SQL Consolidation feature has everyone talking! Find out what the buzz is all about at these upcoming Webinars - August 29th. [...] 2125 67 36_Re: MVS/DB2 DB2 managed Table spaces9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 23 Aug 2006 11:43:28 -0500568_US-ASCII Yes, you can alter the priqty and secqty on the tablespace and index but you can't specify the using clause. ALTER TABLESPACE TESTM01.TABLESP1 PRIQTY 1440 SECQTY 1440; COMMIT; ALTER INDEX INDXP201 PRIQTY 1440 SECQTY 1440 ; both worked sqlcode = 0.

the problem is the only impact is on new extents for the dataset for the index. The existing extents will stay until something affects the entire indexspace. The usual DB2 utilities are REORG, RECOVER, REBUILD, and LOAD REPLACE. the outside DB2 answers are DF/DSS, DSN1COPY, IDCAMS REPRO and others [...] 2193 45 26_Re: DEFINE NO and DSN1COPY17_Walter Trovijo Jr19_wtrovijo@ELB.COM.BR31_Wed, 23 Aug 2006 14:20:46 -0300509_us-ascii Lori,

In our weekly subsystem DSN1COPY, we use RTS info to identify unchanged and empty tablespaces; if a table space is empty in both systems we just do nothing; if a tablespace is empty in the source subsystem and has data in the target subsystem we do LOAD REPLACE to empty target tablespace. We have been using RTS for a while and it works fine, with exception of some ghost objects left in RTS after DROP that we take care of using couple SQLs before we start DSN1Copying subsystem. [...] 2239 86 36_Re: MVS/DB2 DB2 managed Table spaces2_Su22_subha_sivaji@YAHOO.COM31_Wed, 23 Aug 2006 12:45:25 -0500635_- It worked. Thanks for the help.

Su.



On Wed, 23 Aug 2006 11:43:28 -0500, Mike Bell wrote:

>Yes, you can alter the priqty and secqty on the tablespace and index but >you can't specify the using clause. >ALTER TABLESPACE TESTM01.TABLESP1 >PRIQTY 1440 SECQTY 1440; >COMMIT; > ALTER INDEX INDXP201 > PRIQTY 1440 SECQTY 1440 > ; >both worked sqlcode = 0. > >the problem is the only impact is on new extents for the dataset for the >index. The existing extents will stay until something affects the entire >indexspace. >The usual DB2 utilities are REORG, RECOVER, REBUILD, and LOAD [...] 2326 16 53_Can you save CPU time by compressing a DB2 Tablespace12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Wed, 23 Aug 2006 13:16:16 -0500732_- Hello,

I compressed a tablespace last weekend which reduced the getpage on the table to 36,000,000 from 144,000,000. The question is that is is reduction due to I/o saving or CPU? How can I track the CPU reduction if there is any? Thanks, Ayalew "Mark"

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 2343 58 40_DB2 V8CM on z/OS 1.6 Memory allocations.0_19_csutfin@AMSOUTH.COM31_Wed, 23 Aug 2006 13:55:55 -0500483_us-ascii I have the following question from my DB2 System Programmer about the MEMLIM in the DBM1 task and the MLMT in the IRLM task.

Our LPAR has a total of 3.8G available.

What is the consenus of other shops on setting this limit?

"Under DB2 V8, procs DB2TDBM1 and DBITPROC add new parameters that appear to specify how much storage will be available "above the 2GB" bar. I am curious to hear opinions as to an appropriate setting for the new parameters. [...] 2402 69 57_Re: Can you save CPU time by compressing a DB2 Tablespace14_Andy Lankester26_alankester@CDBSOFTWARE.COM31_Wed, 23 Aug 2006 14:00:39 -0500536_windows-1250 Probably not, but you gain in all sorts of other ways:

Reduced IO/higher buffer hit rates - this saves real CPU Reduced (sometimes significantly) demand on all levels of storage: main/cache/dasd/backup

Avoid compression where the size/compressibility is low, the update/insert rates are high or LOADs/REORGs(unless you have CDB REORG) are frequent.

It depends on the relative cost/value of CPU vs IO/storage at your site and the equation will probably change over your hardware replacement cycle. [...] 2472 36 23_Combining multiple rows0_25_Dave.Schaeffer@BENDIX.COM31_Wed, 23 Aug 2006 15:44:13 -0400390_us-ascii Hello,

I have a table like:

REPORT_NUMBER LINE TEXT

12345 1 ABC 12345 2 DEF 12345 3 GHI 67890 1 QWE 67890 2 ZAQ 45678 1 VFR

Is there a way in SQL that I can combine the data into rows that would look like:

12345 ABC|DEF|GHI 67890 QWE|ZAQ 45678 VFR

I'm using DB2 version 7 on a Z/os mainframe. Any help would be very much appreciated. [...] 2509 87 27_Re: Combining multiple rows10_Doyle Mark19_Mark.Doyle@WUSH.COM31_Wed, 23 Aug 2006 15:16:32 -0500554_us-ascii Dave, If there is a 'maximum number of lines' for your report, you can use SQL of this form:

Select a.text concat Case when b.text is not null then b.text else ' ' end concat Case when c.text is not null then c.text else ' ' end concat Case when d.text is not null then d.text else ' ' end concat Etc. From table a Left outer join table b On a.report_number = b.report_number Left outer join table c On a.report_number = c.report_number Left outer join table d On a.report_number = d.report_number Etc. Where a.line = 1 and b.line = [...] 2597 88 44_Re: DB2 V8CM on z/OS 1.6 Memory allocations.11_Tom Moulder30_tom.moulder@TREXASSOCIATES.COM31_Wed, 23 Aug 2006 16:14:02 -0500611_us-ascii Someone from IBM may correct me on this, but I have been told that DB2 will ignore these settings and automatically get 4TB.

Tom Moulder TREX Associates, Inc. 9728 Delmonico Dr. Keller, Tx. 76248-9559 +1 817 741-5549 Office +1 817 741-5548 Fax +1 682 558-6527 Mobile tom.moulder@trexassociates.com www.t-rex-associates.com



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of csutfin@AMSOUTH.COM Sent: Wednesday, August 23, 2006 1:56 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V8CM on z/OS 1.6 Memory allocations. [...] 2686 87 44_Re: DB2 V8CM on z/OS 1.6 Memory allocations.11_Tom Moulder30_tom.moulder@TREXASSOCIATES.COM31_Wed, 23 Aug 2006 16:15:39 -0500555_us-ascii My apologies, DBM1 will get 4TB and IRLM will get 2GB automatically.

Tom Moulder TREX Associates, Inc. 9728 Delmonico Dr. Keller, Tx. 76248-9559 +1 817 741-5549 Office +1 817 741-5548 Fax +1 682 558-6527 Mobile tom.moulder@trexassociates.com www.t-rex-associates.com



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of csutfin@AMSOUTH.COM Sent: Wednesday, August 23, 2006 1:56 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V8CM on z/OS 1.6 Memory allocations. [...] 2774 189 32_Re: db2 Backup kills our machine12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 23 Aug 2006 14:34:28 -0700441_iso-8859-1 It looks to me that although you specify /backup multiple times, you're probably doing this because of the large output size of your image copies, not because you're trying to minimize the elapsed time of the copy process. I think you'd need to increase Parallellism and have different /backup directories to really hammer the machine. If your copy will fit on a single file, then by all means only send it to one directory. [...] 2964 62 27_Re: Combining multiple rows14_Brad E. Melton24_Brad.Melton@WAL-MART.COM31_Wed, 23 Aug 2006 16:49:40 -0500301_- Dave,

You can group max values from a table expression of case expressions to create a pivot table as long as the number of report lines is fixed. For example:

DPTEST.TEST_TABLE01 REPORT_NUMBER LINE_NBR TEXT 12345 1 ABC 12345 2 DEF 12345 3 GHI 67890 1 QWE 67890 2 ZAQ 45678 1 VFR [...] 3027 144 44_Re: DB2 V8CM on z/OS 1.6 Memory allocations.12_David Limkin25_David.Limkin@CITEC.COM.AU31_Thu, 24 Aug 2006 09:34:58 +1000508_us-ascii The MEMLIMIT parm with regard to DBM1 is discussed in PK03680.

...In the absence of an explicit MEMLIMIT setting, DB2 uses 4 TB. If MEMLIMIT is specified, DB2 only honors MEMLIMIT settings above 4 TB...In summary, MEMLIMIT is an upper limit, it does not imply usage. DB2 does allocate memory objects above the 2GB bar - but only uses what it needs. It remains important to monitor use of real and auxiliary storage, and to avoid allocating buffer pools to be larger than real storage... [...] 3172 165 38_Bonnie Baker comes to SIRDUG, Sept. 1511_Kathy Lisle19_Kathy.Lisle@ATT.NET31_Wed, 23 Aug 2006 20:55:47 -0400475_ISO-8859-1 *SIRDUG Meeting September 15, 2006*

*Featuring: Bonnie Baker*

What do "they" know about DB2 that could help you do your job? Do you wonder what the DBAs have learned or what the programmers have figured out that could help you? Come find out! Join us on Friday, September 15th, as Bonnie Baker presents "DBA Things a Programmer Should Know -- and Vice Versa". Bring along a buddy another part of your department and learn a few things together! [...] 3338 15 57_Re: Can you save CPU time by compressing a DB2 Tablespace17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Thu, 24 Aug 2006 01:39:32 -0500263_- Dont you have monitors that show class 2 times. You can get it from 101 records?... At a workload level RMF's absorption rate that can tell whether or not you saved CPU. But it isn't avlbl per SQL. I would tend to think you did save few CPU cycles. Venkat [...] 3354 48 48_Re: Abend S0C4 DB2V7.1 + Z/OS1.5 after migration15_Jaime Fernandez17_jaimedez@YAHOO.ES31_Thu, 24 Aug 2006 02:47:46 -0500708_- Hi Bertrand,

Yes we did! We was told by IBM to aply following ptf's:

UQ78008, UQ78220, UQ92608, UQ77014, UQ79712

(as Mr. Scarpa wisely had previously suggested)

After that, it worked just fine.

Good luck

Jaime





On Wed, 23 Aug 2006 14:12:26 +0000, Bertrand Barret wrote:

>Hello, >We have just migrated from Z/OS1.2 to Z/OS1.5 and we are facing abend on DB2. >A simple query (select or delete) is abending with S0C4 code. (thru native SQL or PLI pgm) > >We have seen the same problem on the DB2-L archives on April 2005 (sender Jaime Fernandez) > >ABND=0C4-00000010,U=UAA0105 ,C=XYR00.710.BMC -DSNB1SWS, [...] 3403 15 22_Kumaresh Thimma is OOO15_Kumaresh Thimma26_Kumaresh_Thimma@JBHUNT.COM31_Thu, 24 Aug 2006 04:01:29 -0500642_US-ASCII I will be out of the office starting 08/24/2006 and will not return until 08/28/2006.

If you need any help, please call x12078 (DB2/Oracle/CICS/MQ Hotline)

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 3419 54 48_Re: Abend S0C4 DB2V7.1 + Z/OS1.5 after migration10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT31_Thu, 24 Aug 2006 11:41:23 +0200745_US-ASCII I did what ????? I helped someone ? Impossible. Not admitted. Unbelievable, pure science fiction, according my new bosses :-(((((

Cheers

Max Scarpa

DB2 sysprog IOA bartender CICS babysitter z/OS shoeshine CA DB2 tools plumber MQ cleaner



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 3474 120 48_Re: Abend S0C4 DB2V7.1 + Z/OS1.5 after migration11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 24 Aug 2006 11:43:51 +0200570_us-ascii Rumour has it that you have helped loads of DBAs over the years...of course DBAs aren't people are they............. :(



Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strasse 5 40470 Duesseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-35 E-mail r.boxwell@seg.de Homepage www.seg.de









Max Scarpa Gesendet von: DB2 Data Base Discussion List 24.08.2006 11:41 Bitte antworten an DB2 Database Discussion list at IDUG [...] 3595 336 22_The Marvellous Mr. Max13_Bell, Raymond22_raymond.bell@LANDG.COM31_Thu, 24 Aug 2006 10:53:20 +0100360_iso-8859-1 Digressing slightly, but I didn't receive Max's reply, and wouldn't have seen it if Roy hadn't replied. I get that sometimes, missing posts.

Oh, and Max, if you want to help some more, my shoes could do with a clean. They're special z/OS ones (steel-capped, for kicking Developers) so you should be qualified according to your footer... [...] 3932 93 48_Re: Abend S0C4 DB2V7.1 + Z/OS1.5 after migration10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT31_Thu, 24 Aug 2006 12:48:42 +0200466_US-ASCII > > Rumour has it that you have helped loads of DBAs over the years...of > course DBAs aren't people are they............. :( > >

Rumors from professional liars I payed A LOT to promote my image and to raise my wage. Unfortunately it didn't work and I wasted my money..... But it worked a little (very little actually :-( )with some ladies. I succeeded in doing a perm to them, what did you believe ? Using my JES2 hairdresser certification.... [...] 4026 66 32_Runstats taking a very long time14_Melissa Rogers26_mrogers@NYSTRS.STATE.NY.US31_Thu, 24 Aug 2006 08:47:19 -0400544_us-ascii We are a peoplesoft HRMS & Financial DB2 Z/OS v7 shop. We have three subsystems housing several peoplesoft instances. Over the last few months, the time it takes to complete our regular runstat jobs are increasing. Jobs that use to take a couple of hours to run are now taking 8-10 hours. Can anyone offer some suggestions as to why our runstat jobs would suddenly take so much longer to run. We have had an increase in the amount of data in the system and we do have a lot of objects within the peoplesoft system but for jobs to [...] 4093 99 49_Antwort: [DB2-L] Runstats taking a very long time11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 24 Aug 2006 14:59:31 +0200514_us-ascii could you please post the exact RUNSTATS control cards you are using?

Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strasse 5 40470 Duesseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-35 E-mail r.boxwell@seg.de Homepage www.seg.de









Melissa Rogers Gesendet von: DB2 Data Base Discussion List 24.08.2006 14:47 Bitte antworten an DB2 Database Discussion list at IDUG [...] 4193 17 36_Re: Runstats taking a very long time14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM31_Thu, 24 Aug 2006 08:05:04 -0500628_US-ASCII Have you tried the SAMPLE parameter - 15% seems to be OK, but if you are not sure go for 25%. Also you may need a REORG on the catalog.

Mark.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 4211 223 53_Re: Antwort: [DB2-L] Runstats taking a very long time14_Melissa Rogers26_mrogers@NYSTRS.STATE.NY.US31_Thu, 24 Aug 2006 09:08:35 -0400528_us-ascii THis is one of the many runstats statements included in the job.

RUNSTATS TABLESPACE (PSHRPRD.PSIMAGE ) TABLE(ALL) INDEX(ALL) SHRLEVEL CHANGE

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: Thursday, August 24, 2006 9:00 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Antwort: [DB2-L] Runstats taking a very long time





could you please post the exact RUNSTATS control cards you are using? [...] 4435 199 70_Antwort: Re: [DB2-L] Antwort: [DB2-L] Runstats taking a very long time11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 24 Aug 2006 15:22:47 +0200545_us-ascii hmmm..my guess is Peoplesoft index bloat - They seem to have lots of indexes to cover "all cases" and of course when you run with INDEX(ALL) it must do work for all indexes (even those that are never used). I would also recommend using SAMPLE 25 or so to try and get it faster. I see from an old posting that peoplesoft had in excess of 14000 indexes and one of the posters recommended dropping all unsed indexes (They then got down to about 1700 indexes) of course how they found out which were used or unused was left unsaid.... [...] 4635 25 25_DB2 Compression on zOS v814_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Thu, 24 Aug 2006 06:44:49 -0700229_iso-8859-1 I'd like some input on whether one should use DB2 compression or not. Any references to any article and comments are appreciated.

--------------------------------------------------------------------------------- 4661 56 8_question14_Mark McCormack27_mamccormack@STATESTREET.COM31_Thu, 24 Aug 2006 10:31:22 -0400604_US-ASCII Stan,

I believe that LOAD RESUME YES SHRLEVEL REFERENCE is potentially the most dangerous of utilities on the mainframe. I assume that is what you are running (as opposed to LOAD RESUME YES SHRLEVEL CHANGE, which operates very differently).

Consider these points: 1. Data which passes a format check is added to the table in the reload phase. No check for improper duplicates (same unique key column values) is made at this time. 2. Corresponding index entries are not added until later. If there is a failure (and it is not handled properly), you may be left with table rows [...] 4718 61 57_Re: Can you save CPU time by compressing a DB2 Tablespace35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 24 Aug 2006 10:51:16 -0400400_iso-8859-1 Well, you will save cpu cycles from IO elimination, you will save cpu cycles by reducing the number of getpage requests. Expanding and re-compressing data will cost you cpu cycles.

Synch IOs are charged to the user address space. Prefetch IOs are charged to the DBM1 address space. Getpages are charged to the user address space. I don't remember where compression is charged. [...] 4780 34 37_Accessing DB2 from multiple LPARs zOS14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Thu, 24 Aug 2006 07:59:24 -0700342_iso-8859-1 Scenerio:

I have a sysplex with multiple LPARS. One LPAR (LP1) has DB2 v8 on it.

I want to run batch jobs on LP2 & LP3 pointing to LP1 DB2 database. Is there something we need specifically for this?

Is it possible without installing DB2 in the different LPARs?

Any references would be appreciated. [...] 4815 186 36_Re: Runstats taking a very long time0_25_LL581@DAIMLERCHRYSLER.COM31_Thu, 24 Aug 2006 13:16:28 -0400424_US-ASCII Melissa,

I think that your RunStats jobs are running longer because, well, they have more work to do. :)

Consider: in order to get accurate Key Cardinality information (i.e. FIRSTKEYCARD) for some indexes, a sort may be required. More data means longer sorts and longer run times. The same logic can apply to other statistics, particularly index stats such as LEAFDIST, NEAROFFPOS, and so forth. [...] 5002 312 36_Re: Runstats taking a very long time14_Sniatecki, Jim31_Jim.Sniatecki@ERIEINSURANCE.COM31_Thu, 24 Aug 2006 13:35:56 -0400422_us-ascii You actually should now be using DB2 Real Time Stats. The two tables that are used for RTS are updated real time by DB2 on a specified frequency, the default being every 30 minutes.





Using the two tables sysibm.tablespacestats and sysibm.indexspacestats you can determine with simple queries which tablesapces need to have runstats run on them as well as which ones need to be reorged. [...] 5315 88 36_Re: Runstats taking a very long time10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT31_Thu, 24 Aug 2006 19:42:17 +0200344_US-ASCII Just a considerationabout a problem with elapsed time of RUNSTATS increasing every day that happened to me.

We had the same problem, but this happened slowly but from a certain date it was very evident. The problem was that RUNSTATS job were generated dinamically by a SAS program which had a bug. Runstats statement were: [...] 5404 61 36_Re: Runstats taking a very long time9_Mike Bell21_mbell11a1@VERIZON.NET31_Thu, 24 Aug 2006 13:08:12 -0500597_US-ASCII One consideration I haven't seen mentioned yet is that your runstats elapsed time may be a symptom of a performance problem building. Consider what runstats does - reads every block of every tablespace and index requested plus sorts and SQL updates.

I would check the SMF100 records for these utilities. check for elapsed time for IO, waiting for CPU, etc. You might also find that the runstats jobs used to run by themselves and now there is major workload running at the same time. If you started to lose any buffers from prefetch before the utility processed the page, DB2 [...] 5466 54 37_Create Precedure Errors in DB2 v8 zOS14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Thu, 24 Aug 2006 13:32:59 -0700355_iso-8859-1 Any ideas on why the following does not work? CREATE PROCEDURE SPTOP(IN CFIL CHAR(01)) RESULT SETS 1 LANGUAGE SQL WLM ENVIRONMENT DBT1WLM COMMIT ON RETURN YES READS SQL DATA P1: BEGIN DECLARE CTL CHAR(1 ); DECLARE ACCT_CSR CURSOR WITH RETURN FOR SELECT ACCT_SID, ACCT_NUM FROM ACCT WHERE TL_RELF = :CTL ; CTL = 'C' ; OPEN ACCT_CSR; END P1 [...] 5521 129 26_Re: The Marvellous Mr. Max13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 24 Aug 2006 20:31:17 GMT382_- And it's not even Friday yet (or maybe it is, and I just spaced out a whole day - it happens to me sometimes).

Max, you've been invaluable to me, not only as a smart DB2 guy, but as a CRSP (Comic Relief Services Provider). Too bad your current bosses don't recognize your value. Want me to, uh, take care of them for you? (he says, using his best wise-guy impression) [...] 5651 273 41_Re: Create Precedure Errors in DB2 v8 zOS13_Horacio Villa17_hvilla@AR.IBM.COM31_Thu, 24 Aug 2006 17:54:43 -0300411_US-ASCII Hi Ramon,

Are you running this in SPUFI? Look in:

5 CHANGE DEFAULTS ===> YES

Enter the following to control your SPUFI session: 1 SQL TERMINATOR .. ===> ; (SQL Statement Terminator)

You should change the SQL Statement Terminator (which is by default ;) to something else (we use @).

That character is the one you have to use at the end of your CREATE PROCEDURE. [...] 5925 48 29_Re: DB2 Compression on zOS v813_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 24 Aug 2006 16:34:50 -05001008_ISO-8859-1 I have a little bit of information in my blog..

Part I http://blogs.ittoolbox.com/database/db2zos/archives/the-incredible-shrinking-data-db2s-compression-part-i-10568 Part II http://blogs.ittoolbox.com/database/db2zos/archives/db2-data-compression-part-ii-the-saga-continues-10921 Part III http://blogs.ittoolbox.com/database/db2zos/archives/db2-data-compression-part-iii-the-conclusion-11100

There are also some reference to other manuals there..

Willie

Ramon Santiago wrote:

> I'd like some input on whether one should use DB2 compression or not. > Any references to any article and comments are appreciated. > --------------------------------------------------------------------------------- > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and > home page at http://www.idugdb2-l.org/archives/db2-l.html. From that > page select "Join or Leave the list". The IDUG DB2-L FAQ is at > http://www.idugdb2-l.org. The IDUG List Admins can [...] 5974 157 26_Re: The Marvellous Mr. Max0_19_csutfin@AMSOUTH.COM31_Thu, 24 Aug 2006 16:47:45 -0500437_us-ascii David

You are also included in the CRSP list.

Max needs to start selling the boots. It could be a greater way to have some additional income.

I could use a pair for not only the Developers, but also my users.

I believe that ALL computer articles should be banned from Airline magazines.

These just give managers the crazy ideas that we can do project on a mainframe at the drop of a hat. [...] 6132 67 41_Re: Create Precedure Errors in DB2 v8 zOS13_Kenney, Marty24_Marty.Kenney@RAILINC.COM31_Thu, 24 Aug 2006 17:47:40 -0400674_iso-8859-1 Try adding the following before your CREATE procedure --#SET TERMINATOR !



________________________________

From: DB2 Data Base Discussion List on behalf of Ramon Santiago Sent: Thu 8/24/2006 4:32 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Create Precedure Errors in DB2 v8 zOS



Any ideas on why the following does not work? CREATE PROCEDURE SPTOP(IN CFIL CHAR(01)) RESULT SETS 1 LANGUAGE SQL WLM ENVIRONMENT DBT1WLM COMMIT ON RETURN YES READS SQL DATA P1: BEGIN DECLARE CTL CHAR(1 ); DECLARE ACCT_CSR CURSOR WITH RETURN FOR SELECT ACCT_SID, ACCT_NUM FROM ACCT WHERE TL_RELF = :CTL ; CTL = 'C' ; OPEN ACCT_CSR; END P1 [...] 6200 65 26_Re: The Marvellous Mr. Max13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 24 Aug 2006 23:05:50 GMT495_- Thanks Carol - there'll be a little something extra in your paycheck this week.

Yeah, "Management by Airline Magazine". Makes you just want to Manage them by Tranquilizer Dart, doesn't it?

David "El Grumpo Grande" Waugh, Curmudgeon at Large DSW Consulting & Services

More from http://www.isham-research.co.uk/one_liners.html Intel inside. Idiot outside. Hit any user to continue. Smash forehead on keyboard to continue..... Press any key... no, no, no, NOT THAT ONE! [...] 6266 268 56_Re: Just how fast & reliable is RAID technology, anyhow?13_David S Waugh16_dsw-dba@JUNO.COM29_Fri, 25 Aug 2006 00:01:50 GMT361_- "I tend to think that [snip] they're risking a hard DB2 crash they may not be able to recover from if (God forbid) there's some catastrophic failure of the RVA that takes the whole thing out. Unlikely, perhaps, but still..."

Thought I'd take a little time to update this little saga. Those words turned out to more prophetic than I ever imagined. [...] 6535 244 29_Re: DB2 Compression on zOS v819_Alekos Papadopoulos13_apapad@NBG.GR31_Fri, 25 Aug 2006 08:56:20 +0200819_iso-8859-7 See also redbook SG24-5261-00 DB2 for OS/390 and Data Compression. Quite old, but many topics are still valid





hth

Alekos

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ramon Santiago Sent: Thursday, August 24, 2006 3:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 Compression on zOS v8





I'd like some input on whether one should use DB2 compression or not. Any references to any article and comments are appreciated. ---------------------------------------------------------------------------- ----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". [...] 6780 401 37_Create Precedure Errors in DB2 v8 zOS14_Galeos Antonis19_AGaleos@EUROBANK.GR31_Fri, 25 Aug 2006 10:14:51 +0300455_us-ascii Hi listers

I have try to create an sql procedure in DB2 V7 zOS using SPUFI following your examples. I change sql terminator to @ but I get the same errors





SET CURRENT SQLID='XXXX' @

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0





CREATE PROCEDURE SPTOP(IN CFIL CHAR(01))

RESULT SETS 1

LANGUAGE SQL

WLM ENVIRONMENT DBT1WLM

COMMIT ON RETURN YES [...] 7182 247 30_QMF and DB2 mismatching CCSIDs13_Bell, Raymond22_raymond.bell@LANDG.COM31_Fri, 25 Aug 2006 08:56:28 +0100595_iso-8859-1 All,

Something hopefully (for me) at least one of you has seen before. We're into our DB2 for z/OS V8 CM migration and it's looking good so far. QMF 8.1's been done, and works fine, except for a rather worrying warning message when run in batch:

---------- ****** 06/08/24 12.13.01 ****** ------------------- USERID: ??????? AUTHORIZATION-ID: MESSAGE NUMBER: DSQ10344 MESSAGE TEXT: The GDDM CCSID 351 does not match the CCSID of 285 at database ????. &C1: 351 &C2: 285 &C3: QMF V8R1.0 &C4: ???? --------------------------------------------------------------------- [...] 7430 460 41_Re: Create Precedure Errors in DB2 v8 zOS13_Mertens, Bart18_bart.mertens@CZ.NL31_Fri, 25 Aug 2006 10:26:13 +0200451_us-ascii I've never actually written a sql stored procedure and too lazy to read the sql reference so maybe I'm wrong.

Try adding a ; after or before the "END IF" and "END CASE".

________________________________

Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Namens Galeos Antonis Verzonden: vrijdag 25 augustus 2006 9:15 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: [DB2-L] Create Precedure Errors in DB2 v8 zOS [...] 7891 85 41_Re: Create Precedure Errors in DB2 v8 zOS11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Fri, 25 Aug 2006 09:28:13 +0100392_US-ASCII Only the terminator after the END P2 should be changed to @. All the terminators used to end statements within the body of the procedure (between BEGIN and END) must remain as ;. You have to change the main terminator so that SPUFI can tell the difference between the end of a statement that is inside the procedure (always ;) and the end of the CREATE PROCEDURE statement (@). [...] 7977 473 41_Re: Create Precedure Errors in DB2 v8 zOS14_Galeos Antonis19_AGaleos@EUROBANK.GR31_Fri, 25 Aug 2006 12:25:48 +0300639_us-ascii



I am sorry that I keep asking but I get this error again





SET CURRENT SQLID='XXXX' @

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

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

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

CREATE PROCEDURE SPTOP(IN CFIL CHAR(01))

RESULT SETS 1

LANGUAGE SQL

WLM ENVIRONMENT DBT1WLM

COMMIT ON RETURN YES

READS SQL DATA

IF CFIL = ' ' THEN

GOTO P2

END IF

CASE CFIL

WHEN '1' THEN

CFIL = '5'

END CASE [...] 8451 462 41_Re: Create Precedure Errors in DB2 v8 zOS13_Mertens, Bart18_bart.mertens@CZ.NL31_Fri, 25 Aug 2006 12:50:53 +0200655_us-ascii END IF; END CASE; ________________________________



I am sorry that I keep asking but I get this error again





SET CURRENT SQLID='XXXX' @

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

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

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

CREATE PROCEDURE SPTOP(IN CFIL CHAR(01))

RESULT SETS 1

LANGUAGE SQL

WLM ENVIRONMENT DBT1WLM

COMMIT ON RETURN YES

READS SQL DATA

IF CFIL = ' ' THEN

GOTO P2

END IF

CASE CFIL

WHEN '1' THEN [...] 8914 105 35_Re: Reorging a Base table in DB2 V728_Chandrasekaran, Balachandran35_balachandran.chandrasekaran@EDS.COM31_Fri, 25 Aug 2006 16:53:35 +0530482_US-ASCII "Reorg does not work for extent consolidation or space reuse. " - Is it because of baste table Lob table reorg ?? Or in general ?

Doesn't the REORG drop and recreate LDS unless REUSE keyword is specified ?

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cuneyt Goksu (Usa.net) Sent: Tuesday, August 22, 2006 4:16 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Reorging a Base table in DB2 V7 [...] 9020 275 52_Antwort: Re: [DB2-L] Reorging a Base table in DB2 V711_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 25 Aug 2006 13:42:09 +0200583_us-ascii not for LOBs

Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strasse 5 40470 Duesseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-35 E-mail r.boxwell@seg.de Homepage www.seg.de









"Chandrasekaran, Balachandran" Gesendet von: DB2 Data Base Discussion List 25.08.2006 13:23 Bitte antworten an DB2 Database Discussion list at IDUG



An: DB2-L@WWW.IDUGDB2-L.ORG Kopie: Thema: Re: [DB2-L] Reorging a Base table in DB2 V7 [...] 9296 414 74_Re: Antwort: Re: [DB2-L] Antwort: [DB2-L] Runstats taking a very long time28_Chandrasekaran, Balachandran35_balachandran.chandrasekaran@EDS.COM31_Fri, 25 Aug 2006 17:34:11 +0530448_US-ASCII Slightly an offtopic to this discussion. IS there a way to find unused index at all ? Probably an index not been used by any of dynamic SQL too ?



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: Thursday, August 24, 2006 6:53 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Antwort: Re: [DB2-L] Antwort: [DB2-L] Runstats taking a very long time [...] 9711 132 41_Re: Create Precedure Errors in DB2 v8 zOS9_Agus Kwee19_askwe@OPTONLINE.NET31_Fri, 25 Aug 2006 12:20:45 +0000439_us-ascii Ramon,

Remove the ':' in the following line: WHERE TR_RELF = :CTL

In SQL Procedure you do not need to use ':' in front of a parameter or declared variable;

Regards, Agus Kwee Themis Training http://www.themisinc.com



----- Original Message ----- From: Ramon Santiago Date: Thursday, August 24, 2006 4:33 pm Subject: [DB2-L] Create Precedure Errors in DB2 v8 zOS To: DB2-L@WWW.IDUGDB2-L.ORG [...] 9844 86 12_Re: question28_Chandrasekaran, Balachandran35_balachandran.chandrasekaran@EDS.COM31_Fri, 25 Aug 2006 17:08:30 +0530532_US-ASCII A problem similar to an example briefed by Stan could arise when somebody created index (after a drop) with DEFER YES and forgot to REBUILD it. This is often done for bigger tables. A Check Index utility would be a good point to start with fixing this problem.

Stan, LOAD RESUME YES SHRLEVEL CHANGE is equivalent to multiple SQL INSERTs and its IBM's ready program to avoid ppl writing program everytime they need to insert rows . It takes no advantage of a utility except that it can run in ACCESS MODE(UT) :) [...] 9931 288 32_Runstats taking a very long time11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 25 Aug 2006 15:18:01 +0200465_us-ascii I have heard rumours that in a "coming version" the RTS tables or even perhaps a table in the catalog will include a "usage indicator" that will be valid for static *and* dynamic SQL. This will, of course, be fantastic if and when it happens!

Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strasse 5 40470 Duesseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-35 E-mail r.boxwell@seg.de Homepage www.seg.de [...] 10220 16 41_Re: Accessing DB2 from multiple LPARs zOS33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Fri, 25 Aug 2006 08:39:09 -0500700_- Hi

I guess, that's not possible. The only option I am aware of, is the bind all your plans with current server pointing to LP1, but that means, you have any DB2 installed on LP2 and LP3.

I don't know, if you can use DB2 Connect an the mainframe.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at [...] 10237 72 38_Re: The Marvellous (depressed) Mr. Max10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT31_Fri, 25 Aug 2006 15:54:57 +0200334_US-ASCII Marvellous ? My usual luck...why no lady call me 'marvellous' I really don't know......I'm called marvellous by a man ...I want to cry....:-(((

Ah sorry for the vent, I feel so idiot now. But sometimes life is a really nuisance.

Anyway call 69696969 -ACME for iron boots. Ask Wile E. Coyote for reference. [...] 10310 18 34_Re: QMF and DB2 mismatching CCSIDs33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Fri, 25 Aug 2006 08:52:43 -0500311_- Raymond

I am living with this problem since we migrated to DB2 V8. I reported it to the sysprog guys, but it seems a little bit difficult to do anything against it, because noone hasn't yet changed it. I don't understand the problems they have, but it seems that there some impact on other tools. [...] 10329 101 88_Please join us!!!! The Baltimore-Washington DB2 Users' Group September Meeting 9/13/200612_Henry Nalven25_henry.nalven@MARRIOTT.COM31_Fri, 25 Aug 2006 09:55:02 -0500650_- The Baltimore-Washington DB2 Users' Group (www.bwdb2ug.org)

Please join us!!!!

September 13, 2006 9:00AM - 3:00PM (registration at 8:30) Sheraton Columbia Hotel

Agenda: 8:30-9:00 Registration

9:00-10:15 Planning Your Migration to DB2 z/OS V8, Willie Favero, IBM

The best way to protect your DB2 for z/OS investment is to aggressively prepare for the next version. Just because migrating to DB2 for z/OS Version 8 may only be a dream today, that is no reason to put off the planning and preparation for the almost guaranteed move that you will have to make to Version 8 tomorrow. There are lots of things you [...] 10431 330 82_FW: [DB2-L] Antwort: Re: [DB2-L] Antwort: [DB2-L] Runstats taking a very long time15_Richard Penfold31_richard.penfold@CDBSOFTWARE.COM31_Fri, 25 Aug 2006 10:11:23 -0500580_us-ascii In answer to your off-topic question, EZ-DB2 (see http://www.cogito.co.uk ) can capture a workload from your production subsystems with a minimal overhead of between 0.5 -1% of the DB2 subsystem CPU. This can include both static and dynamic SQL. Once this has been captured, various reports can be generated including one which identifies those indexes not used by the workload. If the workload is totally representative of the SQL that you run (remember those month end / annual reports), then this report will be listing unused indexes. [...] 10762 24 41_Index partitioning vs. Table partitioning16_Deborah Grunwald19_DGrunwald@GLHEC.ORG31_Fri, 25 Aug 2006 10:14:42 -0500495_ISO-8859-1 We’ve gone to DB2 V8 NFM within the past 3 months, and are now looking at changing some of our partitioning from index partitioning to table partitioning and want to hear some real life experiences with this. We’ve seen some APAR’s out there about tables that were created prior to V6, having problems with this. We’ve also found out RC migrator doesn’t handle index-partitioned tablespaces to table-partitioned tablespaces. Can anyone share their good/bad/ugly stories with us? [...] 10787 75 45_Re: Index partitioning vs. Table partitioning13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 25 Aug 2006 11:45:59 -0400371_US-ASCII Hi Deborah,

I vaguely recall that we've run into a few minor problems that have been fixed.

But ultimately, you don't have a lot of choice.

Many, if not most, changes you make to an index-controlled partitioned table will automatically convert it to table-controlled.

You shouldn't need RC-Migrator to do the conversion for you. [...] 10863 92 34_Re: QMF and DB2 mismatching CCSIDs13_Bell, Raymond22_raymond.bell@LANDG.COM31_Fri, 25 Aug 2006 16:57:16 +0100412_iso-8859-1 Hey Walter,

One of our Sysprog lads was on the ball. He's managed to reassemble the right GDDM module, specifying the two EBCDIC codes and Shazam! no more warning message. I've asked him to send me a quick not, in English (as opposed to the techy gobbledegook most SPs talk), on what he did. When he does I'll forward it on so you may have a chance at getting your GDDM conversions right. [...] 10956 100 51_Some interesting UNICODE implications and questions5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Fri, 25 Aug 2006 11:07:59 -0500316_iso-8859-1 DB2 listserv readers,

I have just had a revelation regarding UNICODE that I thought I should share.

Forgive me if this is something that you already know about. Also, I am posing questions for which I know the answers, but the teacher in me compels me to withhold the answer (for now). [...] 11057 125 34_Re: QMF and DB2 mismatching CCSIDs15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Fri, 25 Aug 2006 12:34:06 -0400546_ISO-8859-1 Raymond,

Would you be so kind as to forward the memo to the rest of us here on the list, too? I rather expect this to be a common problem for QMF users, based on your note so far.

--Phil S.

On 8/25/06, Bell, Raymond wrote: > > Hey Walter, > > One of our Sysprog lads was on the ball. He's managed to reassemble the > right GDDM module, specifying the two EBCDIC codes and Shazam! no more > warning message. I've asked him to send me a quick not, in English (as > opposed to the techy [...] 11183 131 45_Re: Index partitioning vs. Table partitioning11_Hardy, Dale14_DHardy@MIB.COM31_Fri, 25 Aug 2006 14:23:23 -0400547_US-ASCII First the Good. One table, which had a LOB column that we wanted to add more partitions. It was originally created as a table partitioned table with 10 partitions on a single smallint column. The limit values for the partitions were 1 thru 10. Just altered the table to add the new partition with the new limit values. DB2 added the partitions using the same stogroup and priqty/secqty as the last partition. I then created the additional LOB tablespaces, tables and indexes to go with the new partitions. Couldn't have been easier. [...] 11315 29 57_Re: Can you save CPU time by compressing a DB2 Tablespace14_Lisa Ouellette27_Lisa.Ouellette@WACHOVIA.COM31_Fri, 25 Aug 2006 13:35:31 -0500285_- Might not be ALL the CPU time but the most of it should show up in Class 2 CPU time for the plans that access that table. You can get it in DB2PM accounting report or whatever you have equivalent. Just add up a weeek before and a week after to get a feel for what you saved.... [...] 11345 109 55_Re: Some interesting UNICODE implications and questions11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 25 Aug 2006 14:16:30 -0500690_- Larry,

Have you tried using CAST (col name as char(...) ccsid(ebcdic) )? This should work.

Thx Suresh



>From: Larry >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: [DB2-L] Some interesting UNICODE implications and questions >Date: Fri, 25 Aug 2006 11:07:59 -0500 > >DB2 listserv readers, > > I have just had a revelation regarding UNICODE that I thought I >should share. > > Forgive me if this is something that you already know about. Also, >I >am posing questions for which I know the answers, but the teacher in me >compels me to withhold the answer [...] 11455 123 41_Re: Create Precedure Errors in DB2 v8 zOS14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Fri, 25 Aug 2006 12:24:43 -0700422_iso-8859-1 Thanks....

That worked just fine

Horacio Villa wrote:

Hi Ramon,

Are you running this in SPUFI? Look in:

5 CHANGE DEFAULTS ===> YES

Enter the following to control your SPUFI session: 1 SQL TERMINATOR .. ===> ; (SQL Statement Terminator)

You should change the SQL Statement Terminator (which is by default ;) to something else (we use @). [...] 11579 129 46_Mergecopy and Partitioned tablespaces Problems16_Nichols, Suzanne27_Suzanne_Nichols@KYFBINS.COM31_Fri, 25 Aug 2006 15:48:06 -0400627_us-ascii We are running V8.1. We use incremental imagecopies for our very large tables with a full imagecopy weekly (Large for this shop, probably not for others). It this point, I am preparing to add a mergecopy on Wednesdays to shorten the run time for a recovery.

Our imagecopies are of the entire tablespace (all partitions in one file), rather than separate imagecopies for each partition. I am doing testing to get used to the mergcopies and recoveries with them. I am successful recovering non-partitioned tablespaces with the mergecopies, but not successfully trying to recover the partitioned tablespaces. [...] 11709 96 50_Re: Mergecopy and Partitioned tablespaces Problems15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Fri, 25 Aug 2006 15:58:40 -0400597_ISO-8859-1 Have you had your sysprog call DB2 on this? That function is pretty basic, recovering from a merged copy, and DB2 shouldn't care whether the tablespace is partitioned or not. That looks like a bug to _this_ non-IBM guy.

On 8/25/06, Nichols, Suzanne wrote: > > We are running V8.1. We use incremental imagecopies for our very large > tables with a full imagecopy weekly (Large for this shop, probably not for > others). It this point, I am preparing to add a mergecopy on Wednesdays to > shorten the run time for a recovery. > > Our imagecopies [...] 11806 20 50_Re: Mergecopy and Partitioned tablespaces Problems14_Brad E. Melton24_Brad.Melton@WAL-MART.COM31_Fri, 25 Aug 2006 15:16:21 -0500428_- Suzanne,

We also perform a weekly full, daily incremental cycle. A number of years ago we eliminated the need for merge copies to speed up recovery by writing the incrementals to DASD. We migrate the incrementals. So, prior to a recovery we recall the required incrementals.

Another thing we did to reduce our recovery times is to ensure the most recent 24 hours of archived logs is on DASD and unmigrated. [...] 11827 216 55_Re: Some interesting UNICODE implications and questions13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Fri, 25 Aug 2006 15:32:04 -0500566_iso-8859-1 SYSPACKSTMT (and the STMT column specifically) appears to be one of the oddballs because it's not completely unicode (yeah, I know what the DDL and catalog definition says for a CCSID, but it also has "FOR BIT DATA" on the STMT column). We had noticed the behvaior you mention when trying to determine the impact of some maintenance that was being applied (tried searching SYPACKSTMT for certain types of queries) and the only reliable way to get this to work was casting the literal to something (anything, actually) rather than casting the column. [...] 12044 137 55_Re: Some interesting UNICODE implications and questions25_Thanikachalam Sundarrajan21_billysundar@YAHOO.COM31_Fri, 25 Aug 2006 15:17:56 -0700401_iso-8859-1 Larry,

The CCSID of the statement is in column ENCODING_CCSID in SYSIBM.SYSPACKAGE. If you join SYSPACKAGE and SYSPACKSTMT on LOCATION, COLLID, NAME,CONTOKEN, and perform a CAST in conjunction with a CASE operator you should get the result. I think ENCODING_CCSID is set to 0 for packages bound in v7.

Cheers, Billy.

--- Suresh Sane wrote: [...] 12182 163 62_Re: How can I make a database come up as Read-Only by default?13_Michael Ebert18_mebert@AMADEUS.COM31_Sat, 26 Aug 2006 00:31:15 +0200354_ISO-8859-1 It's maybe a bit late, but I've just returned from two weeks vacation, when I got this huge surprise... has Oracle released a brand-new fully reengineered DB version in the meantime? No? Then I can only assume this is supposed to be an ironic remark. Careful, there are people who would believe this sort of stuff, they're called CEOs... [...] 12346 39 74_Re: Antwort: Re: [DB2-L] Antwort: [DB2-L] Runstats taking a very long time17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Fri, 25 Aug 2006 18:12:07 -0500559_- For Dynamic SQLS, there is no easy way. If you accumulate IFCID 22 over a period of time, you can do elimination game. This will likely yield better result. Compare the activity.

Move all suspected indexes to their own pool. Any insert on a table will have to implicitly read index pages to figure out where to insert. You can match up the geptpages to the write I/O. The point is somehow you should conclude that all read activity was from the inserts. You cannot be perfect but works. You have to make sure that you are satisfied with current [...] 12386 44 53_Re: Antwort: [DB2-L] Runstats taking a very long time17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Fri, 25 Aug 2006 19:14:02 -0500613_- Melissa, 75% of what is in PSIMAGE is unlikely to have growth. PSIMAGE is in 32K pool, right?. Have you been externalizing RTS?. If you haven't been doing RTS, I strongly suggest you implement RTS. Many customers have implemented RTS successfuly. It used to have storage issues but it is stable. Change your runstats to conditional runstats based on the real time stats. Certain items like (REORGFAR + REGORGNEAR) / REORGINSRTS on indexspacestats and the same thing on ReorgFarindref and nearindref on tablespacestats provide clue on when you need to do maintainance. Most of PSIMAGE is bit data stuff. If [...] 12431 204 41_Re: Create Precedure Errors in DB2 v8 zOS14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 26 Aug 2006 13:31:03 +1000452_US-ASCII You need a semi-colon after "GOTO P2" - because this is a nested contol statement (whereas the WHEN is part of the CASE statement). And, as Bart pointed out, after END IF and END CASE.

If you get this working I would be interested in seeing how. I spent some time trying to get SQL SPs working some time ago. I was unable to the get sequential statements working outside a BEGIN ... END block. You may need to write something like [...] 12636 13 41_Re: Accessing DB2 from multiple LPARs zOS17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Sat, 26 Aug 2006 00:13:56 -0500616_- Ramon, If you read the cover letter when the product was delivered to you, the intention itself can warrant a lawsuit. The answer is no. Venkat

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 12650 196 74_Re: Antwort: Re: [DB2-L] Antwort: [DB2-L] Runstats taking a very long time13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Sat, 26 Aug 2006 23:00:15 -0500565_iso-8859-1 Regarding the recommendation of moving the empty tables off to the side somewhere so you don't need to worry about running runstats on them -- back when I used to support a Peoplesoft application I tried taking the approach of segregating all of the empty tables from the "real" ones and dropping the indexes and I ended up regretting it in the long run. What I found was that I successfully moved off all of the tables that were empty on that particular day, and not neccessarily "non used". To make matters worse, I also took the step of dropping [...] 12847 80 50_Re: Reorg on PARTITIONED Base Table and LOB TABLES12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Sat, 26 Aug 2006 21:36:16 -0700558_iso-8859-1 Hi

I am assigned to create a reorg job for a base tablespace with 10 partitions and its associated lob tablespaces. I am thinking to design the job as follow.. Can any one check this and let me know it is a good desgin.

step 1. run quiesce on the base and lob tablespace step 2. run copy on the base and lob tablespace step 3. run runstats job on the base tablespace step 4. run conditional reorg on the base table. When I say conditional reorg, I mean non online reorg with offposlimit and offposlimit parameter which is shown [...] 12928 108 58_IDUG 2007 NA Call for presentations - deadline 6 days away11_Paul Turpin34_pault@ALUMMAIL.SIMON.ROCHESTER.EDU31_Sun, 27 Aug 2006 08:38:30 -0500364_ISO-8859-1 ONLY 6 more days until the September 1st deadline ... We don't want to miss out on a great topic, so if you have one keep reading.

Want to attend the IDUG 2007 NA for free? OK, that got your attention!

The IDUG 2007 North America Conference is being held in San Jose, California this year. The conference will be held May 6-10, 2007. [...] 13037 462 34_Re: QMF and DB2 mismatching CCSIDs12_David Limkin25_David.Limkin@CITEC.COM.AU31_Mon, 28 Aug 2006 09:07:35 +1000512_us-ascii This is the procedure as suggested by IBM.





BDC000032708

Source..........: US ASKQQA

Last updated....: 20050926

Abstract........: GDDM - GDDM default code page





QUESTION:





Our DB2 group is wondering how the default GDDM code page can be

changed. Looking in the docs, it looks to me like a modification of

the ADMDATRN module may be necessary.





They are getting this message: [...] 13500 21 34_Marc M.W. van der Valk is afwezig.17_Marc van der Valk34_mw.van.der.valk@BELASTINGDIENST.NL31_Mon, 28 Aug 2006 01:24:01 +0200579_us-ascii Ik ben afwezig vanaf 28-08-2006 en ik ben niet eerder terug dan 18-09-2006.

Ik antwoord op uw bericht wanneer ik terug ben.



------------------------------------------------------------------------------ De Belastingdienst gebruikt e-mail niet voor de uitwisseling van vertrouwelijke informatie met de burger of voor de bekendmaking van beslissingen. De Belastingdienst hanteert conventies voor het gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl. ============================================================================= [...] 13522 17 45_Re: Index partitioning vs. Table partitioning33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Mon, 28 Aug 2006 02:58:33 -0500359_- Hi

The conversion isn't the problem, but once you have converted, you cannot use RC/Migrator or RC/Compare for those tablespaces, because even the new release does not support table controlled partitioned tablespaces.

So, think twice, before you go for table controlled partitioning. May be it would be a good advice to save the old DDL. [...] 13540 144 41_Re: Accessing DB2 from multiple LPARs zOS3_Les21_db2dba@BTINTERNET.COM31_Mon, 28 Aug 2006 11:29:08 +0100299_us-ascii Hi why not turn the Original DB2 to a member of a datashairng group and bring up the second member on LP2 or LP3 as required?

or if you are trying to avoid licenses .. then the simple answer is "not that I am aware of" ....

can you explain why you wish to work this way? [...] 13685 189 41_Re: Accessing DB2 from multiple LPARs zOS13_Michael Ebert18_mebert@AMADEUS.COM31_Mon, 28 Aug 2006 13:09:25 +0200545_US-ASCII I imagine one reason could be to avoid license costs. We possibly had a similar situation. Because batch jobs used more and more CPU (OO-programming...), we needed bigger and bigger machines, driving up the DB2 & QMF licensing costs. However, only a small percentage of the CPU usage was actually due to DB2. Running the batches on one machine and DB2 on a dedicated DB2-machine would probably have saved a lot of money... if anyone had been interested in doing it that way. Instead it was used to justify the move to Oracle/Unix. [...] 13875 40 40_inserting blob in DB@ using perl and DBI18_The SportComputMan27_sport_compu_man@HOTMAIL.COM31_Mon, 28 Aug 2006 08:35:56 -0700585_- Hello,

I would like to load some images as blobs in a DB2 database. I am using perl and DBI-DB2 to interact with the database. I tried to use this syntax to do it:

open(my $fh, $path_to_image ) or die $!; ## to open the image file read( $fh, $var, -s $fh ); ## read the image file and store them in $var $sql="INSERT INTO standard_image (image_id,height,width,source,size,is_original,type,created_by) VALUES ($image_id,$image_height,$image_widht,'$var',$temp_image_size,1,'$image_type',$created_by)"; $standard_image_id=execute_insert_query_and_get_id($dbh,$sql); [...] 13916 50 17_Column validation14_Brad E. Melton24_Brad.Melton@WAL-MART.COM31_Mon, 28 Aug 2006 12:32:21 -0500338_- DB2-L,

I am attempting to write an SQL query that validates the contents of a column. I'd like to run this validation query in a batch unload.

I intended to use paired case expressions and where predicates to write the rows containing a violation along with the reason for the violation out to the side. For example: [...] 13967 51 7_Quiesce20_VIDYA SAGAR ATTULURI19_vattuluri@GMAIL.COM31_Mon, 28 Aug 2006 14:48:23 -0400356_ISO-8859-1 Hi,

Platform: DB2 V8.1 for z/OS

I have an insert sql completed but not yet commited, at this point i am running Quiesce with WRITE YES, i thought QUIESCE will wait for the insert operation to finish (either till commit or rollback)? But Quiesce successfully finished. How the updated page due to insert is handled by Quiesce? [...] 14019 21 70_Looking for Training for Development Center (Stored Procedure Builder)11_Suresh Sane21_data_arch@HOTMAIL.COM31_Mon, 28 Aug 2006 14:15:39 -0500667_- A few of our developers are interested in obtaining training for Dev Center (V8).

Any suggestions? Are there classes aimed primarily at developers? IBM or other vendors?

Thx Suresh

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 14041 44 41_Re: Accessing DB2 from multiple LPARs zOS14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Mon, 28 Aug 2006 12:36:37 -0700298_iso-8859-1 Thanks everyone,

I guess the answer is no. The reason I posed the question is that management was trying to find out why we can't simply run DB2 batch jobs on different LPARs of the same sysplex that don't have DB2 on it but some how point to the data on the LPAR that does. [...] 14086 46 74_Re: Looking for Training for Development Center (Stored Procedure Builder)12_HEPP SHERY C17_schepp@SRPNET.COM31_Mon, 28 Aug 2006 13:45:02 -0700422_us-ascii HI Suresh- we had Themis come in and teach a class on building SQL stored procedures using the Dev Center.

Regards, Shery

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Suresh Sane Sent: Monday, August 28, 2006 12:16 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Looking for Training for Development Center (Stored Procedure Builder) [...] 14133 341 41_Re: Accessing DB2 from multiple LPARs zOS3_Les21_db2dba@BTINTERNET.COM31_Mon, 28 Aug 2006 22:19:48 +0100463_US-ASCII let this very thing be a statement to IBM about reducing the TCO for DB2 on Mainframe ...

I am hearing more and more that people are considering the move to Oracle, I wish management would realise that Oracle is not the anywhere near being in the same league as DB2, however, it takes two to tango and IBM need to realise and realise fast that more and more shops are going to go the route of the "dark side" unless they reduce their prices. [...] 14475 82 39_Stored procedures Implementation - z/OS15_Basivi Inaganti30_basivi.r.inaganti@JPMCHASE.COM31_Mon, 28 Aug 2006 17:22:23 -0400430_ISO-8859-1 Any pros and cons on both the approaches below.

1) One WLM APPLENV for each application in DB2 data sharing group. 2) One WLM APPLENV for each Cobol SPs, JAVA SPs, REXX SPs etc per Data Sharing group.

Which one you recommend?



Thanks, Basivi R. Inaganti :: Production Quality DBA :: Application Technology Services :: Enterprise Systems :: Å 614-213-5324 (Office) 1-888-354-5236 (Page) [...] 14558 217 11_Re: Quiesce14_Gonella, Vamsi22_vamsi.gonella@21ST.COM31_Mon, 28 Aug 2006 14:39:08 -0700459_iso-8859-1 Hi Sagar,

Are you sure the INSERT is not committed. Can you display the locks on the TS.

Quiesce should get message 'DSNU306I -DB2X DSNUQUIA - UNABLE TO DRAIN WRITERS FROM TABLESPACE'

Regards Vamsi

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of VIDYA SAGAR ATTULURI Sent: Monday, August 28, 2006 11:48 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Quiesce [...] 14776 75 29_New Index on SYSIBM.SYSTABLES12_Steve Grimes22_steve_grimes@WUSTL.EDU31_Mon, 28 Aug 2006 17:12:30 -0500753_US-ASCII Hello, DB2 V7.1 - z/OS 1.4 here,

When I attempt (with a SYSADM id) to add an index (using DDL that I think came packaged with our 390 enablement stuff) to our test system SYSIBM.SYSTABLES table, I get:

CREATE INDEX SYSIBM.SQLDTX01 ON SYSIBM.SYSTABLES (TBCREATOR, TBNAME); ---------+---------+---------+---------+---------+---------+---------+---------+ DSNT408I SQLCODE = -551, ERROR: SYSIBM DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION CREATE INDEX ON OBJECT SYSIBM.SYSTABLES DSNT418I SQLSTATE = 42501 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXODD2 SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 50 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'00000032' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' [...] 14852 91 33_Re: New Index on SYSIBM.SYSTABLES0_19_csutfin@AMSOUTH.COM31_Mon, 28 Aug 2006 17:44:49 -0500505_us-ascii Steve

Be sure to have the SQL

SET CURRENT SQLID = 'your techadm authid' ;

before the CREATE sql.

Assumption here is that you are using secondary authids for your SYSADM.

Carol Sutfin Corporate DBA AmSouth Bank (205)261-5214 csutfin@amsouth.com







"Steve Grimes" To Sent by: "DB2 DB2-L@WWW.IDUGDB2-L.ORG Data Base cc Discussion List" [DB2-L] New Index on SYSIBM.SYSTABLES [...] 14944 61 44_Re: inserting blob in DB@ using perl and DBI14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 29 Aug 2006 09:14:30 +1000440_US-ASCII I'm sort of guessing in this response, so make your own allowances:

I suspect that there is an x'27' in the blob which is "terminating" the byte sequence. You could - parse the blob, doubling each x'27' - which will be de-doubled during the insert - read the postings at http://groups.google.com.au/group/comp.databases.ibm- db2/search?group=comp.databases.ibm- db2&q=%2Bperl+%2Bblob&qt_g=1&searchnow=Search+this+group [...] 15006 185 21_Re: Column validation14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 29 Aug 2006 09:14:29 +1000365_US-ASCII TRANSLATE(col,'?',' A .. Za .. z0 .. 9@#',' ') <> ' ' (and, no, I don't literally mean 'A .. Z'). This will translate a space to a non- space, all other acceptable characters to a space, and check that the result is all spaces.



> 1) I could use the "POSSTR(column,'x') <> 0" ... > However, that only checks for valid EBCDIC characters. [...] 15192 170 72_Dropping DB2 mainframe TCO (was: Accessing DB2 from multiple LPARs zOS )13_David S Waugh16_dsw-dba@JUNO.COM29_Tue, 29 Aug 2006 00:36:53 GMT561_- Les: I too am hearing more and more that people are considering moving to Oracle because "DB2 is too expensive", and (as a DB2 bigot) it distresses me no end.

Two points, if I may:

1. Is it DB2 being too expensive, or is it (the perception of) Oracle being less expensive? I suspect both. I've seen the Oracle marketing folks do an AMAZING song-and-dance about what Oracle can do for the client (and TOTALLY fib about how much less it's going to cost), while IBM does it's usual fairly impressive pitch about what DB2 can do for the client [...] 15363 154 76_Re: Dropping DB2 mainframe TCO (was: Accessing DB2 from multiple LPARs zOS )11_Tom Moulder30_tom.moulder@TREXASSOCIATES.COM31_Mon, 28 Aug 2006 20:38:02 -0500581_us-ascii Another example of TCO manipulation I've seen occur:

Oracle gives the client a bid on the number of servers for the production workload and IBM gives a bid for DB2 on a z machine. IBM in this case looks significantly more expensive. However, the first thing the client has to do for Oracle is buy as many servers for the development (sometimes more) as they do for production. Whereas on the z machine they place development and production in separate LPARS and the cost is the same for production and development. Seems to me like some people forget that TCO [...] 15518 152 76_Re: Dropping DB2 mainframe TCO (was: Accessing DB2 from multiple LPARs zOS )35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Mon, 28 Aug 2006 23:10:30 -0400382_iso-8859-1 All it would really take is some guts at the exec mgt level to hold Oracle accountable for their cost estimates. Of course Oracle would never agree to anything like this contractually. They always lie, they've been caught lying many times, and companies rarely stand up and follow through on legal options. They're too embarrassed to admit their mistakes publicly . [...]