1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l August 2005, week 2 2 36 53_Re: DB2 V8 Compatibility Mode - Alter VARCHAR problem14_Grainger, Phil20_Phil.Grainger@CA.COM30_Mon, 8 Aug 2005 09:25:50 +0100303_us-ascii The post on "long" index names reminded me of something we stumbled across (or rather one of our customers did) the other week - something else that works find in V7 but changes subtly in v8 CM

YOU have been able to ALTER a VARCHAR length for some time and this is NOT a v8 feature [...] 39 101 62_BUILD for SP fails for DB2 for OS/390 and z/OS - HELP required0_18_PPantazi@LAIKI.COM30_Mon, 8 Aug 2005 12:37:41 +0300383_us-ascii

The environment is DB2 for OS/390 and z/OS v7 and DB2 Connect Personal Edition v8.2.2 and I am using the Development Center for building the Stored Procedures.

Please note that I have never tried this before and this is the first time that I am using the Development Center. Until now no Stored Procedure Development existed for DB2 for OS/390 and z/OS. [...] 141 77 66_Re: BUILD for SP fails for DB2 for OS/390 and z/OS - HELP required13_Thomas Dunlap21_tsdunlap@PEOPLEPC.COM30_Mon, 8 Aug 2005 06:34:05 -0400357_ISO-8859-1 PPantazi,

The return code (RC) and reason (RSN) form the WLM Services indicates that the application environment is not active. It seems that the "Application Environment" define to DB2 is REXX_WLMDBP1. However the one specified in the JCL procedure is "REXX WLMDBP1". One contains an "underscore" character and one contains a space. [...] 219 226 66_Re: BUILD for SP fails for DB2 for OS/390 and z/OS - HELP required0_18_PPantazi@LAIKI.COM30_Mon, 8 Aug 2005 14:03:55 +030011_ISO-8859-7 446 306 42_Re: How to eliminate idle thread time-out?17_Walter Trovijo Jr19_wtrovijo@ELB.COM.BR30_Mon, 8 Aug 2005 09:32:02 -0300584_iso-8859-1 I know it´s quite obvious, but make sure you have CMTSTAT=INACTIVE set; the other cause could be the client application doing something else in DB2 after commit. You can try to trace what applications are doing to uncover hidden sql statements being issued against DB2. We used to have an application here that for each useful SQL did things like 'SELECT 1' and 'SELECT * FROM SYSIBM.SYSDUMMY1' and other weird things just to "make sure DB2 is alive", using their words. The funniest part is that developers weren´t able to find where some SQL statements came from... [...] 753 466 66_Re: BUILD for SP fails for DB2 for OS/390 and z/OS - HELP required14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Mon, 8 Aug 2005 23:01:06 -0700449_ISO-8859-1 You could try checking the meaning of the DSNX967I message and following through to the meaning of the 11580C1A reason code. It didn't take me long to get to the words "Meaning: The application environment name is not defined in the active WLM policy. Action: Check whether the correct application environment name is being used. If so, a service administrator must define the application environment in the WLM service definition." [...] 1220 71 20_index usage problem.13_Murat Umurhan25_Murat.Umurhan@TCMB.GOV.TR30_Mon, 8 Aug 2005 16:39:56 +0300345_windows-1254 Hello list, we have an index usage problem on a query and can not understand the situation. As a result It's running a heavy workload. The query is "DELETE FROM C1.T1 WHERE TARIH=?" I EXPLAIN ed the query: The result is "Tablespace scan, no index usage"

Table T1 has following columns. We are interested in TARIH column [...] 1292 464 80_DB2 UDB Performance Management Webinar with Gunning Technologies and BEZ Systems11_Lori Rapoza15_lrapoza@BEZ.COM30_Mon, 8 Aug 2005 08:41:38 -0500408_us-ascii Please join us for an "insightful" and "actionable" Webinar (And a chance to win a free iPod Shuffle, too!)





"DB2 UDB Performance: Get it right! -- Keep it right!"





Tactical and Strategic DB2 Performance Management Solutions from BEZ and Gunning Technology Solutions LLC.





When: Tuesday, August 23, 2005 -- Time: 11:30 AM (EST) [...] 1757 42 48_Quick question about connection to DB2s via DDF.10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 8 Aug 2005 15:45:34 +0200371_US-ASCII Estimeed listers

We're implementing a connection mainframe to mainframe to let 2 DB2s (local and remote) for z/OS to communicate between them. I read the redbook 'Distributed functions of DB2 for z/OS and OS/390' and configurations looks simple as we've all TCP/IP stuff implemented (we don't want to use SNA) and WLM is ready for workload type DDF. [...] 1800 123 24_Re: index usage problem.17_SALVADORI Etienne17_esalvadori@GMF.FR30_Mon, 8 Aug 2005 15:53:24 +0200345_iso-8859-1 Do you think that approximatively evry datapage will be affected by the delete ? If so, i think the optimizer have shossen the right pathway. There is no need to read index pages. Do you issue some performance problem (what is the commit frequency)?

Etienne Salvadori

AZUR-GMF . SGBD Levallois . ISM 01-49-64-37-73 [...] 1924 54 38_Adding Columns to a Partitioning Index14_Mark McCormack27_mamccormack@STATESTREET.COM30_Mon, 8 Aug 2005 10:04:24 -0400569_US-ASCII DB2v7, z/OS 1.4

We are required to add columns to a partitioning index. Only the index is changing; the table structure remains unchanged. The table / tablespace involved is one of our biggest - more than one hundred million compressed rows in 254 partitions. Under DB2v8 we could use alter index . Under DB2v7 we are faced with the following: unload table drop tablespace recreate tablespace and table with no structure changes recreate index with new column lineup reload table There would also be backups before / after, runstats, rebinds, etc. [...] 1979 153 24_Re: index usage problem.13_Murat Umurhan25_Murat.Umurhan@TCMB.GOV.TR30_Mon, 8 Aug 2005 17:12:33 +0300486_windows-1254 4 million rows are deleted by this query. Table has 25 million rows. No commit till the end of this query. It takes approximately 30 min. to run. Messages coming to console that indicates group buffer pool has shortage, and has adequate storage respectively.





-----Original Message----- From: SALVADORI Etienne [mailto:esalvadori@GMF.FR] Sent: Monday, August 08, 2005 4:53 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] index usage problem. [...] 2133 50 42_Re: Adding Columns to a Partitioning Index14_Jeff L'Italien18_azdb2dba@YAHOO.COM30_Mon, 8 Aug 2005 09:36:07 -0500419_- Mark,

I have performed a similar task in past WITHOUT having to perform the DSN1COPY. My procedure is similar to yours but with a couple of variations:

Here is the procedure that I have used:

1) Stop the tablespace.

2) Perform partition level image copies or DSN1COPY against all data partitions.

3) Rename underlying VSAM datasets for the data.....use B001 for the last node. [...] 2184 216 24_Re: index usage problem.17_SALVADORI Etienne17_esalvadori@GMF.FR30_Mon, 8 Aug 2005 16:49:19 +0200321_iso-8859-1 If the tablespace is in a sharing group, the coupling facility must recieve the content of the 4 million rows, and, as you does not commit till the end, i think db2 must keep the wole content in the GBP. There is another point: you must log every delete in the DB2LOG... 4 million rows to log and Commit. [...] 2401 102 42_Re: Adding Columns to a Partitioning Index9_Mike Bell21_mbell11a1@VERIZON.NET30_Mon, 8 Aug 2005 10:06:15 -0500321_windows-1250 If you 1. make sure you remove all the remnants of the table from the DBD 2. specify OBID's on the creates You can get there with just renames. The DBD cleanup is just MODIFY RECOVERY - run before you drop the tablespace- and is covered in the utility guide. the creates with OBID are in the SQL guide. [...] 2504 186 24_Re: index usage problem.12_Roy Cornford19_cornford@US.IBM.COM30_Mon, 8 Aug 2005 10:21:58 -0500528_ISO-8859-1 You will be updating a lot of pages in the table, about one in six may be more: presumably the optimiser thinks with all these pages it is best to do a relational scan. I assume your stats are current.

Regarding the GBP, you will be causing a lot of writing to the GBP and then on to DASD. You may be writing to the CF faster than can be externalised. Note the updates in the local BPs will be forced to the GBPs at commit, but can be transferred earlier. You might want to review your VDWQT and possibly [...] 2691 244 42_Re: Adding Columns to a Partitioning Index11_David Nance16_DWNance@FHSC.COM30_Mon, 8 Aug 2005 09:27:44 -0600557_us-ascii Mark, It sounds plausible and a few others, it looks like have tried it or some variation. You are still looking at a bit of down time for this task. One of the things we have done in the past is to create two copies of the table. The first(shadow) has no index just the table structure. We then create a trigger on original table(orig), that is for after insert, to insert the columns to the shadow table(you might need additional shadows if you do delete/update processing on table). Then we perform unload of orig to load into "new" table. [...] 2936 23 31_Create(clone) new DB2 subsystem7_Do Park19_doparkdba@YAHOO.COM30_Mon, 8 Aug 2005 10:41:33 -0500323_ISO-8859-1 Hello all,

I will create new DB2 subsystem. We have Production and Development DB2 subsystems. We have one SMP/E datasets. We’re on DB2 V7 and z/OS v1.4.

1. Would you please let me know how to create(clone) new DB2 subsystem? 2. Would you please recommend a manual or web-site as a reference? [...] 2960 104 27_Re: EDM pool and large DBDs12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Aug 2005 11:19:20 -0500524_- Keeping the DBD sizes reasonable is still a recommendation. There is very little to gain and potentially a lot to lose by having hundreds of tables in a single DBD.

The Installation Guide has this paragraph: Recommendation: If your tables are small, you should use fewer than 50 tables in a segmented table space. For very large tables that are partitioned and larger than 1-GB, you should use one table per database. You can use additional databases to assist with logging, space management, and concurrency. [...] 3065 134 42_Re: Adding Columns to a Partitioning Index14_Wayne Driscoll34_wdriscoll@WESTERN-METAL-SUPPLY.COM30_Mon, 8 Aug 2005 12:32:58 -0500562_US-ASCII One way that I have used to determine if there is a chance that short rows exist for the table is the check if SYSIBM.SYSTABLES column ALTEREDTS is not equal to CREATEDTS (then the table has been altered). I then look at SYSIBM.SYSCOPY to see if there has been a REORG done on the tablespace where TIMESTAMP > ALTEREDTS. If the tablespace has been reorged since the last alter, the short rows will have been expanded. As an additional comment, I have found (at least on V7 and earlier) that issueing an alter table add column if 09C101 errors been [...] 3200 34 35_Re: Create(clone) new DB2 subsystem12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Aug 2005 12:50:45 -0500678_ISO-8859-1 There is a book on cloning, but specialized for SAP: SAP on DB2 for z/OS and OS/390: DB2 System Cloning

http://www.redbooks.ibm.com/abstracts/sg246287.html?Open http://www.redbooks.ibm.com/redbooks/pdfs/sg246287.pdf

Roger Miller

On Mon, 8 Aug 2005 10:41:33 -0500, Do Park wrote:

>Hello all, > >I will create new DB2 subsystem. We have Production and Development DB2 >subsystems. We have one SMP/E datasets. We’re on DB2 V7 and z/OS v1.4. > >1. Would you please let me know how to create(clone) new DB2 subsystem? >2. Would you please recommend a manual or web-site as a reference? > > >Thanks in advance, >Do. > [...] 3235 34 25_DSNUTILS Stored Procedure14_Toppins, Smike21_smike.toppins@GWL.COM30_Mon, 8 Aug 2005 11:59:38 -0600689_us-ascii Does anyone have an example of calling DSNUTILS from a Windows .BAT file that I can use as a starting point? Thanks.

SMike Toppins Great-West Life 8525 E. Orchard Road Englewood, CO 80111 smike.toppins@gwl.com





----Statement of Confidentiality----

This e-mail, and any attachments thereto, is intended for the exclusive use of the addressee(s) named herein and may contain information that is privileged or confidential or otherwise legally exempt from disclosure. If you are not a named addressee or an employee or agent responsible for delivering this message to a named addressee, you are not authorized to read, print, retain, copy or [...] 3270 173 24_Re: index usage problem.12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Aug 2005 13:00:29 -0500358_ISO-8859-1 This appears to be a good situation to use REORG DISCARD, rather than an application. With 4 million deletes and no commits, you don't have any concurrency. REORG will add some commits and do the reorg that is probably needed at the end.

Roger Miller

On Mon, 8 Aug 2005 10:21:58 -0500, Roy Cornford wrote: [...] 3444 34 91_DB2 7.1 / ZOS Finding all Plans dependent of all objects ( TS, TB L, Indexes) of a Database11_Sinha, Amit19_sinha.amit@MBCO.COM30_Mon, 8 Aug 2005 13:07:09 -0500362_iso-8859-1 Hi,

I am scheduling a job to do Reorg of all tablespaces at Database when they become fragmented. After Reorg will do the Runstats & then after that will like to do rebind of all the Plans:

It's afternoon and feel so sleepy after a nice lunch can't think well. Was wondering if this SQL will find all the Plans, I am guessing not : [...] 3479 52 33_Re: SQL Statement Limit in DB2 V812_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Aug 2005 13:15:00 -0500348_- We need to run DSNTIAD (and I think SPUFI too) in compatibility mode, so changing to put in new function could be a problem. DSNTEP2, DSNTEP4 and DSNTIAUL were changed to have the longer statements, when you use the V8 versions.

Roger Miller

On Sat, 6 Aug 2005 23:49:41 -0500, James Campbell wrote: [...] 3532 159 27_Re: EDM pool and large DBDs14_Seth Bienstock19_sethdb2@COMCAST.NET30_Mon, 8 Aug 2005 18:57:13 +0000622_- My two centavos:

I find that recommendation in the doc (Version 8) confusing, or perhaps incomplete.

One could probably devote several pages of text discussing approaches to "sizing" your DBDs, and those three sentences just don't seem to tell me all that much.

Why would the size of the table (i.e. number of rows or row size) be any factor in computing or managing the DBD size? The focus of the first two of the three sentences in the recommendation are on table size. There's nothing mentioned about the number of space-consuming objects (i.e. indexes, tables, constratints) that those "50 [...] 3692 34 27_Re: EDM pool and large DBDs9_Mike Bell21_mbell11a1@VERIZON.NET30_Mon, 8 Aug 2005 14:18:29 -0500564_windows-1250 The elephant in the fridge symptom.

Anything you do to a table requires a lock on the entire DBD. Suppose you have 3 high activity tables in the same DBD. You arrange to have a table modification, table is not being used but your alters always fail on timeout and deadlock because the the ongoing activity on the other 2 tables in the DBD. I tend to recommend one table per database unless they are tightly coupled. It does cost you EDM pool space because you will have many small DBD's but the alternative is to quiesce everything in the [...] 3727 125 49_Situation Available - Experienced DB2 Consultants24_The Fillmore Group, Inc.18_fillmore@CLARK.NET30_Mon, 8 Aug 2005 15:52:28 -0400624_iso-8859-1

Job Postings Experienced DB2 Consultants

The Fillmore Group, Inc., is a premier international provider of IBM Information Management (DB2) systems integration, consulting, and training solutions. Current and projected client demand enables us to expand our consulting team with qualified, experienced, senior DB2 consultants. Consultants will have the opportunity to work on cutting-edge projects with the most advanced software and receive ongoing training on other IBM Information Management platforms and software products. These are full-time, career positions; no subcontractors, please. [...] 3853 56 30_Windows/udb task center issues27_DB2 Database Solutions Port26_db2dba_certified@YAHOO.COM30_Mon, 8 Aug 2005 13:41:17 -0700399_iso-8859-1 In our client's place, we are having problem with 'UDB Task Center' on windows. It takes minimum 5 minutes to open. We are currently on fixpack 7 (udb v8). We tried playing with several db/dbm cfg parms but no use. I have noticed the following message in db2diag.log everytime we open task center. Even after setting all the db cfg parms of DWCTRLDB, we have no luck. Please advise. [...] 3910 26 38_Adding Columns to a Partitioning Index14_Mark McCormack27_mamccormack@STATESTREET.COM30_Mon, 8 Aug 2005 17:33:05 -0400554_US-ASCII Jeff, Mike, David, and Wayne,

Thanks for the information. I am the mad schemer around here, and it's nice to know that some of my wild ideas might actually work.

I am intrigued by the idea of reusing the original vsam data sets and eliminating the need for dsn1copy. I can see how modify delete age(*) - followed by drop - can clean up the DBD and make the original obid available to create table obid nnnn. It is less clear to me how to get the same psid assigned to the resurrected tablespace. If there has been much create [...] 3937 65 42_Re: Adding Columns to a Partitioning Index9_Mike Bell21_mbell11a1@VERIZON.NET30_Mon, 8 Aug 2005 17:20:27 -0500405_windows-1250 I only did this once and when I tested, we always reused the tablespace obid after the cleanup. I thought you could specify the obid on create tablespace but it isn't in the manual. My exercise was back on V5 but don't think this stuff has changed much. Yes, I had to create a new test DBD to do my testing because the normal test dbd's never had image copies let alone MODIFY RECOVERY. [...] 4003 79 24_Re: Unloading DB2 Tables14_M. Khalid Khan25_mohammad.khan@COMCAST.NET30_Mon, 8 Aug 2005 19:27:44 -0500384_windows-1250 I once wrote a DSNTEP2 like program in COBOL which I'm sure is lying around somewhere. I can look for it if anyone would like to have it. Khalid

----- Original Message ----- From: "Mike Bell" Newsgroups: bit.listserv.db2-l To: Sent: Saturday, August 06, 2005 9:02 AM Subject: Re: [DB2-L] Unloading DB2 Tables [...] 4083 56 95_Re: DB2 7.1 / ZOS Finding all Plans dependent of all objects ( TS, TB L, Indexes) of a Database6_ducduc17_ndt.db2@GMAIL.COM30_Tue, 9 Aug 2005 08:26:05 +0200528_ISO-8859-1 i think you should use syspackdep, as we rebind as the package level , it avoidsredundant rebind

On 8/8/05, Sinha, Amit wrote: > Hi, > > I am scheduling a job to do Reorg of all tablespaces at Database when they > become fragmented. After Reorg > will do the Runstats & then after that will like to do rebind of all the > Plans: > > It's afternoon and feel so sleepy after a nice lunch can't think well. Was > wondering if this SQL will find > all the Plans, I am guessing not : > > [...] 4140 100 73_Re: [Maybe Spam] [DB2-L] Quick question about connection to DB2s via DDF.13_Bell, Raymond22_raymond.bell@LANDG.COM30_Tue, 9 Aug 2005 10:04:13 +0100649_iso-8859-1 Max-baby,

I see you haven't had a reply yet - either that or I'm missing e-mails again. Anyway, I think this stuff is done via the Change Log Utility. Have a crack at running something like this:

//*--------------------------------------------- //* //* CHANGE LOG INVENTORY //* //*--------------------------------------------- //DSNTLOG EXEC PGM=DSNJU003,COND=(4,LT) //STEPLIB DD DISP=SHR,DSN=DSN.SDSNLOAD //SYSUT1 DD DISP=OLD,DSN=BLAH.BLAH.BSDS01 //SYSUT2 DD DISP=OLD,DSN=BLAH.BLAH.BSDS02 //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSIN DD * DDF LOCATION=YOURLOCN,LUNAME=YOURLUNM, NOPASSWD,RESPORT=NNNN,PORT=NNN [...] 4241 75 52_Re: [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs13_Bell, Raymond22_raymond.bell@LANDG.COM30_Tue, 9 Aug 2005 10:10:38 +0100368_windows-1250 Eh? Elephant? Fridge? Another example of cross-continent incontinence? Explanation please, Mike.

That's a valid point about small DBDs allowing greater likelihood of a DBD update succeeding. Currently we have the luxury of an outage when making structure changes. Doesn't mean it'll stay that way. This might be the time to break up our DBDs. [...] 4317 106 52_Re: Quick question about connection to DB2s via DDF.0_28_jim_addison@STANDARDLIFE.COM30_Tue, 9 Aug 2005 10:15:36 +0100515_US-ASCII Hi Max,

I believe linkname should be bbbbbb.

Jim.

Jim Addison ISOS DBA&Middleware Data Centre 8 Glenogle Road

t: 0131 245 3206 e: Jim_Addison@Standardlife.com







Max Scarpa cc: Sent by: bcc: DB2 Data Base Subject: [DB2-L] Quick question about connection to DB2s via DDF. Discussion List



08/08/2005 14:45 Please respond to DB2 Database Discussion list at IDUG [...] 4424 61 52_Re: Quick question about connection to DB2s via DDF.15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM30_Tue, 9 Aug 2005 11:21:40 +0200664_iso-8859-1 From my understanding from installation guide, for IP configuration the LINKNAME column is just a "LINK" from various tables, it can be whatever you want. Only when you configure SNA connections it must be (most of the times) the LUNAME.

HTH

Mauro Moschelli SanPaoloIMI S.p.A.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On > Behalf Of Max Scarpa > Sent: Monday, August 08, 2005 3:46 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] Quick question about connection to DB2s via DDF. > > > Estimeed listers > > We're implementing a connection mainframe to mainframe to let > [...] 4486 67 27_Re: EDM pool and large DBDs13_Bell, Raymond22_raymond.bell@LANDG.COM30_Tue, 9 Aug 2005 10:29:36 +0100358_iso-8859-1 Hi Roger,

Thanks for the additional details. A couple of things that maybe you can clarify for me. Yes, I agree, the larger DBD was probably created prior to V6. If I do a REPAIR DBD REBUILD (after doing a REPAIR DBD TEST first, of course) do you think that would count as a DBD built after V6 and, therefore, eligible for 'chunking'? [...] 4554 64 28_Pagein for read i/o under v818_McAuliffe, Stephen35_Stephen.McAuliffe@BANKOFAMERICA.COM30_Tue, 9 Aug 2005 10:51:49 +0100420_us-ascii hi all,

I'm after some faults on the impact of a figure for pagein for read i/o, or QBSTRPI.

I have DB2 V8 running on z/OS 1.4, DB2 is pretty much the only system running on this lpar and there is 4Gb of central storage available. When I migrated to V8 I ramped up the size of a couple of buffer pools and page fixed most of them, we have the storage available so may as well use some of it. [...] 4619 136 52_Re: [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 9 Aug 2005 11:04:44 +0100473_us-ascii The only elephant/fridge joke I know, goes like this:

"How do you know when there are elephants in your fridge??"

Pause for effect

"They leave their footprints in the butter"

Pause of laughs

Of course, I have NO IDEA how this relates to edmpools or dbds!



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 4756 61 52_Re: [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs13_Bell, Raymond22_raymond.bell@LANDG.COM30_Tue, 9 Aug 2005 11:08:57 +0100507_iso-8859-1 Indeed. I remember seeing the same joke in a book I had called '101 Elephant Jokes'. I had it when I was, um, 13 I think. My favourite? What's grey and lights up? An electric elephant.

Well, it was amusing when I was 13...



Raymond Bell Database Administrator

-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Sent: 09 August 2005 11:05 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs [...] 4818 223 24_Re: index usage problem.13_Murat Umurhan25_Murat.Umurhan@TCMB.GOV.TR30_Tue, 9 Aug 2005 13:30:17 +0300546_windows-1254 Thank you Roger, Salvador and Roy. I will go through "REORG DISCARD"





-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Monday, August 08, 2005 9:00 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] index usage problem.



This appears to be a good situation to use REORG DISCARD, rather than an application. With 4 million deletes and no commits, you don't have any concurrency. REORG will add some commits and do the reorg that is probably needed at the end. [...] 5042 70 24_Re: DSNZSCH 00E6080B OC412_Charles Tsao21_tsao.cct@FORCES.GC.CA30_Tue, 9 Aug 2005 06:30:26 -0500647_- Many thanks

C. Tsao





On Thu, 4 Aug 2005 15:01:13 -0500, Bruce, Mae wrote:

>No, it doesn't work with V8. I ran into the same thing. It has been >replaced by the EXPLAIN STMTCACHE ALL. This explains the sql in the >dynamic cache and populates a table called DSN_STATEMENT_CACHE_TABLE. It >gives you lots of info about what work the statement has done. It also >gives you the text for the statement. > >There is more info about it in the manual where it talks about EXPLAIN. > >Mae > >-----Original Message----- >From: C Tsao [mailto:tsao.cct@FORCES.GC.CA] >Sent: Thursday, August 04, [...] 5113 20 11_DB2 Connect10_John Lantz20_john.p.lantz@FRB.GOV30_Tue, 9 Aug 2005 06:31:13 -0500273_- Does anybody know of a way that you can determine what version of DB2 Connect is running on a client based on the connection to the mainframe? We are running DB2 Connect Personal Edition and are trying to find the old clients that still have not upgraded to DB2 V8. [...] 5134 86 24_Re: Unloading DB2 Tables12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV30_Tue, 9 Aug 2005 07:43:42 -0400413_- Yes, syncsort can execute sql queries.

If you have a syncsort programmer's guide, look for "DB2 Query Support" chapter, which is chapter 11 in my version.





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bergeson, Loren Sent: Friday, August 05, 2005 7:19 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Unloading DB2 Tables [...] 5221 98 15_Re: DB2 Connect33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE30_Tue, 9 Aug 2005 14:50:03 +0200418_iso-8859-1 It's a part of DB2-DDF Accounting record.

TMON/DB2 shows them like this. I believe other mointors provide the same info.

Version 8.2.2 is DB2 Connect V8 FP9a. The product means OS/2 but it's really a Windows workstatation. Version 8.1.7 is DB2 Connect V8 FP7.



JOBNAME: DB2SMDB Thread MVS/DDF Accounting DATE: 08/09/05 DB2 : DB2S Collection Analysis TIME: 14:46:35 COMMAND: [...] 5320 103 32_Re: Pagein for read i/o under v814_Avram Friedman21_avramf@IBMSYSPROG.COM30_Tue, 9 Aug 2005 05:58:03 -0700338_iso-8859-1 The problem with paging is the address space referencing the page waits. Note this is THE ADDRESS SPACE waits not the task. If the address space is a CICS region, the CICS region waits. If the address space is DBM1 DB2 as a whole waits. If the address space is user batch or TSO only the user batch job or TSO user waits. [...] 5424 99 32_Re: Pagein for read i/o under v835_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Tue, 9 Aug 2005 09:04:43 -0400309_iso-8859-1 Without expanded memory, your paging is to/from dasd. So at best, you are looking at 1 Ms of delay for every page-in, on the assumption that the pages are referenced quickly enough so they are in the dasd cache. If a page is not in the dasd cache, you may have upwards of 15 Ms for a page-in. [...] 5524 44 60_Registration for the European IDUG conference 2005 in Berlin12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 9 Aug 2005 15:05:22 +0200508_- Fellow Collegues,

I just tried to register for the European IDUG conference 2005 in Berlin.

Clicking at the VeriSign SecureSite at the end of the online registration form I saw that the following:

Name secure.sba.com Status Expired Validity Jan.30,2003 - Feb.27,2004 Class Digital ID Class 3 - Global Server ID Renewal .....

While looking at the Status Expired I assume that a secure conference registration (with credit card info and so on) is not possible in the moment. [...] 5569 91 52_Re: [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs16_Dennis H Werling18_DH-Werling@WIU.EDU30_Tue, 9 Aug 2005 08:12:45 -0500616_ISO-8859-1 What's grey and comes in a red and white can? Campbell's Cream of Elephant soup

Quoting "Bell, Raymond" :

> Indeed. I remember seeing the same joke in a book I had called '101 > Elephant Jokes'. I had it when I was, um, 13 I think. My favourite? > What's grey and lights up? An electric elephant. > > Well, it was amusing when I was 13... > > > Raymond Bell > Database Administrator > > -----Original Message----- > From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] > Sent: 09 August 2005 11:05 > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] [Maybe Spam] [...] 5661 404 32_Re: Pagein for read i/o under v818_McAuliffe, Stephen35_Stephen.McAuliffe@BANKOFAMERICA.COM30_Tue, 9 Aug 2005 14:13:13 +0100442_us-ascii Hi Avram,

thanks for the response, I'm fairly comfortable with the impact of paging in the old world of limited storage, in the new world, where we have a bit more storage available I was a little more interested in why the system shows paging for read i/o when there are spare frames available? My assumption is that z/OS will not necessarily go to the free frames but may page steal instead if the pages are not fixed. [...] 6066 315 52_Re: [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Tue, 9 Aug 2005 08:19:22 -0500501_US-ASCII Why do ducks have flat feet ?

To stamp out burning fires.

Why do elephants have flat feet ?

To stamp out burning ducks.

Mark E Vickers Grocers Supply Co. 3131 E. Holcombe Blvd. Houston, TX 77021 Tel: (713) 842-6541 Cell: (713) 819-3183





Dennis H Werling Sent by: DB2 Data Base Discussion List 08/09/2005 08:12 AM Please respond to DB2 Database Discussion list at IDUG [...] 6382 58 23_Fwd: Re: Compress parm.9_David Pat22_davidpatty72@YAHOO.COM30_Tue, 9 Aug 2005 06:36:57 -0700564_iso-8859-1 > > Hello > > What is the effect of COMPRESS yes parameter in distributed environement for a tables with more than 15 million rows. The COMPRESS parameter is used to define the tables which are used in JOIN involved with 4 tables (All tables are more than 10 million rows). > > The program is from disributed end which performs ONLINE enquiry. When this transaction is triggered the CPU usage is spiking up and our analysis is that the culprit is compress parameter and looking for any suggestions which may help me to confirm the high CPU time. > [...] 6441 218 27_Re: Fwd: Re: Compress parm.14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 9 Aug 2005 14:42:55 +0100394_us-ascii The COMPRESS parameter compresses rows until they are needed to be passed back to the application program (so you can get more rows per page, more rows in the bufferpool etc)

If your join is causing cpu spikes and you are sure that it is the decompression that is causing it, then I suspect that DB2 is needing to decompress many rows to formulate the result of your join. [...] 6660 70 10_query help15_Jones, Trace R.33_trace.jones@CENTERPOINTENERGY.COM30_Tue, 9 Aug 2005 09:26:16 -0500673_us-ascii Hi all,

I have a table like this:

ACCT TABLE ============ ID CHAR(10)



How can I write a query that will list all ID's that are repeated 20 times?

TRJ



--------------------------------------------------------------------------------- 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 6731 161 14_Re: query help14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 9 Aug 2005 15:33:07 +0100492_us-ascii How about

SELECT ID, COUNT(*) FROM ACCT TABLE GROUP BY ID HAVING COUNT(*) = 20

??



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jones, Trace R. Sent: 09 August 2005 15:26 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] query help [...] 6893 122 14_Re: query help13_Rhea, Matthew24_Matthew_Rhea@CONSECO.COM30_Tue, 9 Aug 2005 09:34:45 -0500495_iso-8859-1 SELECT ID FROM ACCT GROUP BY ID HAVING COUNT(*) = 20

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Jones, Trace R. Sent: Tuesday, August 09, 2005 9:26 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: query help





Hi all,

I have a table like this:

ACCT TABLE

============

ID CHAR(10)



How can I write a query that will list all ID's that are repeated 20 times? [...] 7016 54 11_cursor help18_Murali K Rajendran18_mrktheni@YAHOO.COM30_Tue, 9 Aug 2005 07:37:53 -0700292_iso-8859-1 Hi,

When building a cursor by joining two tables , can DB2 FETCH from the base table with out building a Result-set, Because, I face this

a) QUERY joins two tables A & B - this will retrieve 3 rows

12345 AA - 1st row 12346 BB - 2nd row 12347 CC - 3rd row [...] 7071 24 27_Antwort: [DB2-L] query help12_Rodney Krick15_rk@AFORMATIK.DE30_Tue, 9 Aug 2005 16:37:52 +0200 7096 188 31_Re: Antwort: [DB2-L] query help15_Jones, Trace R.33_trace.jones@CENTERPOINTENERGY.COM30_Tue, 9 Aug 2005 09:59:54 -0500498_iso-8859-1 That is what I need (I always forget about the HAVING clause). Thanks for the help everyone.











Rodney Krick 08/09/2005 09:37 AM Please respond to DB2 Database Discussion list at IDUG

To: DB2-L@WWW.IDUGDB2-L.ORG@SMTP@Exchange cc: (bcc: Trace R Jones/ADM/CPE) Subject: [DB2-L] Antwort: [DB2-L] query help



SELECT ID, COUNT(*) FROM ACCTTABLE GROUP BY ID HAVING COUNT(*) = 20 [...] 7285 77 64_Re: Registration for the European IDUG conference 2005 in Berlin12_Martin Hubel17_Martin@MHUBEL.COM30_Tue, 9 Aug 2005 11:02:57 -0400452_- Hi Georg, I reported this to IDUG HQ, and they are fixing it. Please try it again in a few minutes. Thanks--Martin





=========================================================== Martin Hubel MHC Inc. www.mhubel.com +1 905-764-7498 +1 416-670-7498 mobile +1 905-764-8411 fax

Ask me how to find bad SQL faster than you can say WOW! ===========================================================

>> Fellow Collegues, [...] 7363 208 24_Re: Unloading DB2 Tables15_Bergeson, Loren24_LBergeson@IDAHOPOWER.COM30_Tue, 9 Aug 2005 09:17:15 -0600322_iso-8859-1 Unfortunately, syncsort does not support VARCHAR columns over 255 bytes long, and the tables I'll be unloading have lots of long VARCHAR columns. Syncsort also seems to pad VARCHAR columns with spaces, which drastically increases the size of the unload file -- which is the same problem that DSNTIAUL has. [...] 7572 264 24_Re: Unloading DB2 Tables11_Hardy, Dale14_DHardy@MIB.COM30_Tue, 9 Aug 2005 12:13:03 -0400359_us-ascii We have a few applications where we have found it easier than unload and load for a few thousand rows.

The V7 Performance Topics Redbook, sg24-6129-00, give examples where it had a 30% improvement over straight SQL inserts. Depending on the number of rows being inserted, it could compare favarably with doing a straight unload and load. [...] 7837 207 15_Re: cursor help19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM30_Tue, 9 Aug 2005 10:26:52 -0600576_us-ascii Murali, It depends on the Cursor. If the cursor needs to materialize during the Open, then the Fetch is done from the resulset. This will happen when you have an ORDER BY clause (with no supporting index to avoid sort) or GROUP BY clause coded in the cursor.

In your case, the cursor is not materialized at the Open and hence the Fetch is done from the table directly resulting in fetching the rows that were updated or inserted during the cursor processing and qualify the selection of the cursor. Adding the timestamp predicate to the cursor is the way [...] 8045 337 15_Re: cursor help14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 9 Aug 2005 17:33:36 +0100344_us-ascii OR

make it a scrollable cursor. This will force a materialisation of the result set (well, until v8 anyway, but you can always code it as a SENSITIVE (or INSENSITIVE) STATIC cursor - the "STATIC" is the bit that guarantees a materialised result set)

BEWARE though that this might come with performance implications. [...] 8383 81 52_Re: Quick question about connection to DB2s via DDF.12_tim malamphy20_timalamphy@YAHOO.COM30_Tue, 9 Aug 2005 09:34:28 -0700576_iso-8859-1 Max- According to my notes, the last time I set this up I just used the name associated with the APPL parm in VTAMLST which defined the LU of the system I was trying to connect to. It was just a one-part name though. tim



--- Max Scarpa wrote:

> Estimeed listers > > We're implementing a connection mainframe to > mainframe to let 2 DB2s (local > and remote) for z/OS to communicate between them. > I read the redbook > 'Distributed functions of DB2 for z/OS and OS/390' > and configurations > looks simple as we've all [...] 8465 79 42_Re: Adding Columns to a Partitioning Index0_22_sally.mir@WACHOVIA.COM30_Tue, 9 Aug 2005 12:44:28 -0400383_US-ASCII Mark,

If the recreation of your tablespace doesn't happen to get the same PSID, it's an easy 'zap' using REPAIR to change the page header to the new value. And of course you know that you can specify the OBID for the table, so that's easy.



Sally A. Mir, AVP IBM Certified Database Administrator DB2 Universal Database V8.1 for Z/OS (336) 773-4011 [...] 8545 267 31_Re: Antwort: [DB2-L] query help11_David Nance16_DWNance@FHSC.COM30_Tue, 9 Aug 2005 10:56:11 -0600447_iso-8859-1 Also, just as an FYI, since all of the respondents included it. You do not need to add count(*) to your select clause. You can keep your original SQL and just add the group by .. having clause.

Dave Nance First Health Services, Corp. (804)527-6841



>>> trace.jones@CENTERPOINTENERGY.COM 8/9/05 10:59:54 AM >>>

That is what I need (I always forget about the HAVING clause). Thanks for the help everyone. [...] 8813 124 31_Re: Antwort: [DB2-L] query help13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Tue, 9 Aug 2005 13:48:27 -0400431_- The only reason I can think you might want to keep the count(*) in the query is if you really want IDs with 20 or more rows and you care precisely how many exist.

Dave



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Nance Sent: Tuesday, August 09, 2005 12:56 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Antwort: [DB2-L] query help [...] 8938 77 61_ENC: [DB2-L] Inactive threads using Cristal Reports (Seagate)19_Luiz Roberto Foroni27_lforoni@CAIXASEGUROS.COM.BR30_Tue, 9 Aug 2005 15:46:15 -0300568_iso-8859-1 Thanks Bart. Now the threads from Cristal Reports are becoming inactives.



Foroni DBA DB2-Z/OS

-----Mensagem original----- De: Mertens, Bart [mailto:bart.mertens@CZ.NL] Enviada em: sexta-feira, 5 de agosto de 2005 03:33 Para: DB2-L@WWW.IDUGDB2-L.ORG Assunto: Re: [DB2-L] Inactive threads using Cristal Reports (Seagate)



Luiz,

Have you tried to disable cursorhold?

met vriendelijke groeten, Bart Mertens Database Administrator DB2 CZ Actief in Gezondheid - Tilburg * (013) 593 8256 * bart.mertens@cz.nl [...] 9016 364 52_Re: [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs15_Billings, Linda30_Linda.Billings@DOA.STATE.WI.US30_Tue, 9 Aug 2005 13:51:12 -0500424_- Why do elephants paint their toenails red?

To hide in cherry trees.

Have you ever seen an elephant in a cherry tree?

Works good, doesn't it?



Linda Billings IS Enterprise Technical Consultant State of Wisconsin Department of Administration Division of Enterprise Technology Bureau of Development and Operations phone - 608-264-6396 fax - 608-261-0618 linda.billings@doa.state.wi.us [...] 9381 52 35_SYSIBM.SYSCOPY in DB2 v7.1 z/OS 1.66_TS/RDS15_tsrds@YAHOO.COM30_Tue, 9 Aug 2005 17:47:35 -0700639_iso-8859-1 Hi all,

DB2 v7.1 z/OS 1.6 JES2 non-data sharing

Application Programming 101 question:

We have been preaching to our application groups to incorporate the MODIFY RECOVERY utility in their batch jobs to delete aged syscopies entries in SYSIBM.SYSCOPY. I have some say they don't want or have the need to make an image copy of their data but realize they are putting entries in SYSIBM.SYSCOPY by loads, reorgs, quiesce, etc.. Anyway, when they run the MODIFY RECOVERY DELETE AGE(xxx) utility to clear their entries, it leaves their objects in copy-pending because they don't have a full image copy. I may [...] 9434 40 39_Re: SYSIBM.SYSCOPY in DB2 v7.1 z/OS 1.60_22_DB2information@AOL.COM28_Tue, 9 Aug 2005 21:31:50 EDT605_US-ASCII Yep, you have it. To automate the UNLOAD/LOAD and REPAIR process you may wish to look at www.recoveryknowledge.com

Ed.

--------------------------------------------------------------------------------- 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 9475 171 39_Re: SYSIBM.SYSCOPY in DB2 v7.1 z/OS 1.613_Richard Fazio21_rfazio@TRANSUNION.COM30_Tue, 9 Aug 2005 20:40:18 -0500339_US-ASCII MODIFY RECOVERY also removes entries from DSNDB01.SYSLGRNX. These entries document points of where a dataset is open for update activity; recovery operations can skip over large areas of log if it know where the table was not updated. So, if you never run MODIFY RECOVERY....SYSLGRNX will continue to grow and grow and grow. [...] 9647 59 39_Re: SYSIBM.SYSCOPY in DB2 v7.1 z/OS 1.613_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 10 Aug 2005 01:59:04 GMT586_- Dear BS in TX: (can't believe I said that) It would be better (and safer) if they ran a REPAIR ... NOCOPYPEND against it, like this: REPAIR OBJECT LOG NO SET TABLESPACE dbname.tsname NOCOPYPEND Keep in mind that -START ... ACCESS(FORCE) turns off ALL flags for the tablespace, not just the Image Copy Pending flag. That would include things like Check Pending, Recover Pending, etc. Those are things you would want to know about but wouldn't even see because you turned them off with the ACCESS(FORCE). Don't know if it's still the case, but years ago a -START ... ACCESS(FORCE) [...] 9707 122 72_AW: [DB2-L] Registration for the European IDUG conference 2005 in Berlin12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 10 Aug 2005 08:10:05 +0200537_iso-8859-1 Thanks, Martin.

And see you in Berlin ?

With kind regards - mit freundlichen Gruessen, Georg

-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Martin Hubel Gesendet: Dienstag, 9. August 2005 17:03 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: Re: [DB2-L] Registration for the European IDUG conference 2005 in Berlin



Hi Georg, I reported this to IDUG HQ, and they are fixing it. Please try it again in a few minutes. Thanks--Martin [...] 9830 418 52_Re: [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs13_Bell, Raymond22_raymond.bell@LANDG.COM31_Wed, 10 Aug 2005 09:10:19 +0100484_iso-8859-1 Ah, it's all coming back to me now. How many elephants can you get in a Mini? 6; 3 in the back, two in the front and one in the glove compartment.

Last one, I promise.



Raymond Bell Database Administrator and Elephant Fancier

-----Original Message----- From: Billings, Linda [mailto:Linda.Billings@DOA.STATE.WI.US] Sent: 09 August 2005 19:51 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs [...] 10249 261 39_Re: SYSIBM.SYSCOPY in DB2 v7.1 z/OS 1.613_Bell, Raymond22_raymond.bell@LANDG.COM31_Wed, 10 Aug 2005 09:24:37 +0100618_iso-8859-1 Davey-baby,

I think you're right about using Repair rather than access(force) to get out of the copy-pending situation. But it can be a problem authorising people for repair. It's a bit of an all-or-nothing privilege and, as you know, there's more than just resetting copy pending to Repair. Still, if you're a 'guns don't kill people, people do' kinda guy, grant them repair and see what happens. I reckon you'll have blood on your hands before long. Mind you, if they're Developers they deserve what they get (oooh, controversy). But if we're talking a Production batch suite it's probably OK [...] 10511 469 32_Re: Pagein for read i/o under v818_McAuliffe, Stephen35_Stephen.McAuliffe@BANKOFAMERICA.COM31_Wed, 10 Aug 2005 11:41:16 +0100658_us-ascii > some more info, which just confuses me even more, perhaps I'm > misunderstanding the figures I am seeing. > > ---------+---------+---------+---------+---------+---------+--- > DATE TIME BPNAME PAGEIN_READIO PAGEIN_WRITEIO > ---------+---------+---------+---------+---------+---------+--- > 08.08.2005 00.00.02 BP8K 0 0 > 08.08.2005 00.05.03 BP8K 0 0 > 08.08.2005 00.10.04 BP8K 3 0 > I'm showing 3 pagein for readio at 10 past midnight from DB2 > statistics. Looking at the paging report from RMF for the same time > period though I can see nothing (apologies if the formatting has gone > as it seems to do when anything is sent to the list). [...] 10981 135 39_Re: SYSIBM.SYSCOPY in DB2 v7.1 z/OS 1.614_Seth Bienstock19_sethdb2@COMCAST.NET31_Wed, 10 Aug 2005 11:25:37 +0000356_- I agree completely with Rich's response.

I'd also like to point out that by not removing those SYSCOPY/LGRNx entries you might also be preventing the DBD from contracting. To the best of my recollection, the DBD will still contain OB/PSIDs for those objects which have been DROPped from the database, but still have entries in SYSCOPY/LGRNx. [...] 11117 457 66_Re: BUILD for SP fails for DB2 for OS/390 and z/OS - HELP required0_18_PPantazi@LAIKI.COM31_Wed, 10 Aug 2005 14:31:10 +0300745_ISO-2022-KR

$)CThanks James.

I converted all my WLM AE to have the same names with the JCL Procedure and now that problem is solved.

Thanks again





|---------+-----------------------------> | | James Campbell| | | | | | Sent by: DB2 Data Base| | | Discussion List| | | | | | | | | 09/08/2005 09:01 %p%l | | | Please respond to DB2| | | Database Discussion list at| | | IDUG| | | | |---------+-----------------------------> >-------------------------------------------------------------------------------------------------------------| | | | To: DB2-L@WWW.IDUGDB2-L.ORG | | cc: | | bcc: | | Subject: Re: [DB2-L] BUILD for SP fails for DB2 for OS/390 [...] 11575 73 62_DB2 for OS/390 and z/OS Stored Procedure Development with .NET0_18_PPantazi@LAIKI.COM31_Wed, 10 Aug 2005 14:56:40 +0300481_us-ascii

The environment is DB2 for OS/390 and z/OS v7 running on z/OS v1.4 without any data sharing.

We want to setup the environment so that it supports Stored Procedure Development with .NET and Development Center. Note that currently we do not use SP on the Database Server.

I have found the requirements for the development center and I am already working on them but what I would like to know is any experiences out there with such an environment. [...] 11649 59 35_Log yes parameter DB2 v7.1 z/OS 1.413_arlen stovall24_arlenstovall@HOTMAIL.COM31_Wed, 10 Aug 2005 12:12:08 +0000333_- I have a question about using log yes for table spaces. All of our table spaces were created with log yes, but we currently do not have any LOB tables. The SQL reference states that this should not be used for non LOB table spaces. Does any one know what kind of over head that this parameter causes for non lob table spaces? [...] 11709 91 39_Re: Log yes parameter DB2 v7.1 z/OS 1.414_Wayne Driscoll34_wdriscoll@WESTERN-METAL-SUPPLY.COM31_Wed, 10 Aug 2005 07:22:18 -0500549_US-ASCII LOG YES|NO is only valid for a LOB tablespace. For non-lob tablespaces you have no choice, DB2 logs all DML alterations to the object. I personally can see no reason why you wouldn't want changes logged. While rare, DASD problems do still occur, so having the data logged can save you. As for logging overhead, I don't (and I don't think IBM has) any hard numbers, but it isn't that expensive for DML operations. That being said, for LOAD and REORG operations, I use LOG NO and then run an image copy, but that is due to the fact that [...] 11801 34 31_Re: 00C90101 after DSN1COPY....24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Wed, 10 Aug 2005 07:32:59 -0500325_- I have also come across the same problem , but I always have to unload from source and reload into target.

I am sure what you are saying is correct, because a Load is also like a reorg.

Can you please explain a little bit on what exactly happens when you do an Alter with out a reorg and after the reog? [...] 11836 21 5_reply24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Wed, 10 Aug 2005 07:39:47 -0500328_- Have you checked to see if there were 19 more inserts to this table that were rolled back after the initial load by your DBA?

Does any one know what happens to the SEQUENCE if there is a roll back? Is the sequence rolled back too?

Also check your SYSIBM.SYSSEQUENCES table to see what the current value is. [...] 11858 32 45_Re: DB2 version 7 - Recovery of a deleted row24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Wed, 10 Aug 2005 07:48:56 -0500352_- Sir,

Try taking a DFDSS dump of that tablespace and run a DSN1PRNT of it or an available image copy from 3 weeks back with teh VALUE option

If you know some key or a string value in the row you are looking for . If it finds that key value ( may be a primary key value or a some unique string in that row ) it will print that page. [...] 11891 64 24_Re: index usage problem.12_Cuneyt GOKSU19_cuneytgoksu@USA.NET31_Wed, 10 Aug 2005 16:05:55 +0300311_ISO-8859-1 Murat,

Reorg discard is the ideal solution.

But I would like to make a comment for the issue that if you change the tablespace's BP to a local BP (which does not have a corresponding GBP), it'll not be moved to GBP. And run that SQL from that specific member.

Keep in mind! [...] 11956 19 17_Large Bufferpools15_Newbie Question21_db2_dba@BELLSOUTH.NET31_Wed, 10 Aug 2005 08:30:33 -0500540_- Our manager still believes that everything should be in one bufferbool. Our production DB2 system has two bufferpools defined, BP7 for sort and BP0 for everything else. I was able to convince him that taking the sort activity out of BP0 was a good idea and he reluctantly went along. We are in the process of upgrading to v8 and he is so excited that we will now be able to take advantage of memory above the line. Wants to increase the size of BP0 even more. I have tried to present a case of seperating tablespaces and indexes and [...] 11976 69 21_Re: Large Bufferpools18_McAuliffe, Stephen35_Stephen.McAuliffe@BANKOFAMERICA.COM31_Wed, 10 Aug 2005 14:46:48 +0100488_us-ascii The V8 admin guide, chapter 26 has a section entitled 'Determining size and number of buffer pools' and 'choosing one or many buffer pools'.

However if they just won't listen, then I don't envy you ...



Cheers, Steve McAuliffe



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Newbie Question Sent: 10 August 2005 14:31 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Large Bufferpools [...] 12046 73 21_Re: Large Bufferpools35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 10 Aug 2005 09:48:18 -0400346_iso-8859-1 Absolutely NOT!! Never !!!! No Way !!!

That's a guaranteed poor performance approach. I can guarantee that there is not one large production system in the world with good performance using only one pool. And no small ones either.

The one pool approach went out the window with the arrival of MVS/XA.... in the 80's. [...] 12120 65 21_Re: Large Bufferpools9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 10 Aug 2005 08:55:19 -0500458_windows-1250 Go find Joel Goldstein's site. http://www.responsivesystems.com/ buy the buffer pool tuning tool run the simulations and watch the impact of tablespace scans on the index pages in BP0. especially since you have almost 0 utilization of the tablespace scan pages, after the scan completes. Look for Joel's articles on why you tune buffer pools. The answer is to avoid IO waits. If you want more data, Joel has lots of his papers in PDF form. [...] 12186 36 39_00C90101 after massive RECOVER PARALLEL15_Daniel Cremieux17_c300501@YAHOO.COM31_Wed, 10 Aug 2005 08:59:45 -0500341_- Dear all,

After a massive Recover to rba parallel , we get 00C90101 in some tablespaces and indexspaces. I 've dsn1prnt the page which is displayed as in error, but don't find any error. I've used COPY with the check page option : no error I've used DSN1COPY with the check page option : no error The same with the indexspace. [...] 12223 25 24_DB2 V7 to DB2 V8 on z/OS0_19_csutfin@AMSOUTH.COM31_Wed, 10 Aug 2005 09:06:23 -0500763_us-ascii Has anyone determined the amount of CPU usage increase between DB2 V7 and DB2 V8 on z/OS(1.4).

Management is the driving force here. They are looking for reasons why we need to add more "power" in the next 12 months.

Thanks in advance.

Carol Sutfin Corporate DBA AmSouth Bank (205)261-5214 csutfin@amsouth.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 [...] 12249 25 52_Re: [Maybe Spam] Re: [DB2-L] EDM pool and large DBDs0_22_sally.mir@WACHOVIA.COM31_Wed, 10 Aug 2005 10:07:03 -0400747_US-ASCII What's harder than getting a pregnant elephant in a Volkswagen?





Getting an elephant pregnant in a Volkswagen....

:)



Sally A. Mir, AVP IBM Certified Database Administrator DB2 Universal Database V8.1 for Z/OS (336) 773-4011

--------------------------------------------------------------------------------- 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 12275 447 39_Re: SYSIBM.SYSCOPY in DB2 v7.1 z/OS 1.612_Hilton, Tina23_thilton@RANDOMHOUSE.COM31_Wed, 10 Aug 2005 09:12:24 -0500586_us-ascii We do modify recoveries monthly. The jobs are owned by us in the DB2 tech support team, so they have the correct authorizations. We use BMC's modify recovery utility (part of copy plus) to first delete all copy rows where the image copy dataset isn't cataloaged (we never create an uncataloged dataset). In production, it will take an image copy if the tablespace is not recoverable or if the last image copy was more than 60 days ago. That prevents any production tablespace from becoming copy pending from the modify. Then a rexx generates control cards to do a "modify [...] 12723 74 28_Re: DB2 V7 to DB2 V8 on z/OS18_McAuliffe, Stephen35_Stephen.McAuliffe@BANKOFAMERICA.COM31_Wed, 10 Aug 2005 15:23:53 +0100505_us-ascii Carol,

We have a very simple Db2 system, which I'm having fun trying to drive the CPU back down again post V8. we have seen a batch cpu increase from V7 to V8 NFM of 40% and up



Cheers, Steve McAuliffe Tel: (020) 8760 6988 Internal: x 66988

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of csutfin@AMSOUTH.COM Sent: 10 August 2005 15:06 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V7 to DB2 V8 on z/OS [...] 12798 48 29_AW: [DB2-L] Large Bufferpools12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 10 Aug 2005 16:24:01 +0200346_iso-8859-1 From my point of view the days when most shops employed the single buffer pool strategy are really over.

As the amount of data stored in DB2 increases, specialized types of tuning are necessary to optimize the data access. This usually results in implementing multiple buffer pools. Why else would IBM provide 80 of them ? [...] 12847 75 24_Re: index usage problem.13_Murat Umurhan25_Murat.Umurhan@TCMB.GOV.TR31_Wed, 10 Aug 2005 17:34:31 +0300583_windows-1254 Thank you Cuneyt. you are right, it's another issue to take care of.

Murat.

-----Original Message----- From: Cuneyt GOKSU [mailto:cuneytgoksu@USA.NET] Sent: Wednesday, August 10, 2005 4:06 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] index usage problem.



Murat,

Reorg discard is the ideal solution.

But I would like to make a comment for the issue that if you change the tablespace's BP to a local BP (which does not have a corresponding GBP), it'll not be moved to GBP. And run that SQL from that specific member. [...] 12923 59 43_Re: 00C90101 after massive RECOVER PARALLEL6_ducduc17_ndt.db2@GMAIL.COM31_Wed, 10 Aug 2005 16:38:35 +0200548_ISO-8859-1 Daniel, you should read x'18' as : 11000 in binary , then interpret , 18 is a normal value (non segmented page set )



On 8/10/05, Daniel Cremieux wrote: > Dear all, > > After a massive Recover to rba parallel , we get 00C90101 in some > tablespaces and indexspaces. > I 've dsn1prnt the page which is displayed as in error, but don't find any > error. > I've used COPY with the check page option : no error > I've used DSN1COPY with the check page option : no error > The same with the indexspace. [...] 12983 80 31_Re: 00C90101 after DSN1COPY....14_Wayne Driscoll34_wdriscoll@WESTERN-METAL-SUPPLY.COM31_Wed, 10 Aug 2005 10:22:35 -0500527_US-ASCII Ram, DB2 maintains a flag in the DBD that if set, indicates that the rows are of variable length. There is then a second flag that indicates that short rows are temporarily allowed, due to an ALTER ADD COLUMN. If the first flag is off, and a short row is found, and the second flat isn't on, a 00C90101 abend will occur. When a reorg is done, after the reload, any short rows will be replaced with null values for the new column and the second flag will be reset. Now if, before a reorg is done, a new tablespace [...] 13064 124 28_Re: DB2 V7 to DB2 V8 on z/OS14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 10 Aug 2005 16:35:19 +0100449_us-ascii There are a number of presentations from IBM on this subject (one of the better is from John Campbell)

To cut a long story (fairly) short

1) There are quite considerable increases in CPU in some places in DB2. Some of these can easily add up to an increase of 30%-40% extra cpu 2) There are many performance improvements in DB2, some in access path efficiencies some elsewhere which can add up to a saving of 20%-40% cpu [...] 13189 46 21_Re: Large Bufferpools15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Wed, 10 Aug 2005 19:21:02 +0200579_us-ascii So,

what arguments does your manager use for this approach? If we know them we can counter them ...

Regards, Marcel.

>Our manager still believes that everything should be in one bufferbool. Our >production DB2 system has two bufferpools defined, BP7 for sort and BP0 for >everything else. I was able to convince him that taking the sort activity >out of BP0 was a good idea and he reluctantly went along. We are in the >process of upgrading to v8 and he is so excited that we will now be able to >take advantage of memory above the line. Wants [...] 13236 37 27_Up serge in CPU time in DDF13_Malik, Munwar31_munwar.malik@ASSOCIATES.DHS.GOV31_Wed, 10 Aug 2005 13:33:51 -0400527_US-ASCII I have an application that is on the following platforms:

part1 - is using .Net web application running on Windows 2000 accessing data on Z/OS DB2 V8. Part2- is using Windows VB accessing data on Z/OS DB2 V8. Part3- is Websphere running on AIX accessing data on Z/OS DB2 V8.

Part1 & part2 is running ok. We just implemented part 3 and our CPU usage went through the roof. Most of the CPU usage is showing in DDF. As soon as we shut down the Web access, the DDF CPU usage goes back to normal normal. [...] 13274 16 49_Re: DB2 Version 8, DFSORT and other sort packages11_Jeff Agosta20_agost003@MC.DUKE.EDU31_Wed, 10 Aug 2005 12:58:41 -0500737_- Max makes a very valid point and raises likely valid questions... Jim, Although DFSORT maint is included in the OS maint, one would assume and expect that there may be addition/seperate fixes to DFSORT that may be required and not necessarily included as part of ones normal maintenance schedule.

--------------------------------------------------------------------------------- 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 [...] 13291 57 15_Re: cursor help14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Wed, 10 Aug 2005 11:58:23 -0700421_us-ascii Are you using the WITH HOLD option on your cursor?

Susan

-----Original Message----- From: Murali K Rajendran [mailto:mrktheni@YAHOO.COM] Sent: Tuesday, August 09, 2005 7:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] cursor help



Hi,

When building a cursor by joining two tables , can DB2 FETCH from the base table with out building a Result-set, Because, I face this [...] 13349 12 21_Re: Large Bufferpools15_Newbie Question21_db2_dba@BELLSOUTH.NET31_Wed, 10 Aug 2005 14:03:35 -0500699_- The basic reason is that he feels that with everything in one bufferpool DB2 should figure out how to manage it the most efficient way. By increasing the size it will allow you to keep more data in the bufferpool reducing i/o.

--------------------------------------------------------------------------------- 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 13362 164 32_Re: Pagein for read i/o under v814_Avram Friedman21_avramf@IBMSYSPROG.COM31_Wed, 10 Aug 2005 12:09:15 -0700383_iso-8859-1 What is the total number of read-io's for the period? Are your RMF intervals synced to the DB2 Stats interval?

I am wondering if the I/O rate is close to zero. I would also have the same question about the get page rate. 3 page I/O's in a 5 minute interval and even more in the entire 15 minutes of stats there is a 10 min interval with no pages per read-i/o. [...] 13527 63 15_Re: cursor help14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Wed, 10 Aug 2005 15:16:55 -0400444_- Maybe turning on cursor blocking will solve your problem ==> BLOCKING ALL option during bind.

-----Original Message----- From: Murali K Rajendran [mailto:mrktheni@YAHOO.COM] Sent: Tuesday, August 09, 2005 7:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] cursor help



Hi,

When building a cursor by joining two tables , can DB2 FETCH from the base table with out building a Result-set, Because, I face this [...] 13591 146 15_Re: cursor help13_Richard Fazio21_rfazio@TRANSUNION.COM31_Wed, 10 Aug 2005 14:30:47 -0500431_US-ASCII Ahh..the "Halloween Query".

Try adding "CURRENT DATA(YES)" on your bind.

faz

Rich Fazio DB2 OS390 DBA

TransUnion, LLC SSG/Data Services Team, 5th Floor 555 West Adams St. Chicago, IL 60661 Phone (312) 985-3270 Fax (312) 466-8398

Talk to teach - Listen to learn



>>> susan.a.allen@BOEING.COM 2005-08-10 1:58:23 PM >>> Are you using the WITH HOLD option on your cursor? [...] 13738 38 21_Re: Large Bufferpools13_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 10 Aug 2005 19:59:54 GMT413_- Ahhhh... the plot thins... Your manager is operating on some REALLY old information.

Back in the early days of DB2 (mid-'80s or so), IBM actually was recommending that everything be kept in one bufferpool (I remember hearing this in the IBM Internal seminars I went to at the time). 'Way back then, it really WAS more efficient to have a single large bufferpool than multiple (smaller) bufferpools. [...] 13777 174 21_Re: Large Bufferpools13_Richard Fazio21_rfazio@TRANSUNION.COM31_Wed, 10 Aug 2005 15:04:05 -0500434_US-ASCII This sounds like a great solution...providing that you can read your entire DB2 subsystem into core at one time...AND....you read every page in the bufferpool equally...and...oh---now I'm just making stuff up. This really stinks.

Consider printing the New York phone book. Putting one person's info on a piece of paper. Now, put all the pieces of paper on a table (a big table)....Find "Rudolph Giuliani" please. [...] 13952 72 21_Re: Large Bufferpools9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 10 Aug 2005 15:16:52 -0500548_windows-1250 The purpose of buffer pools is NOT to just keep data. The purpose is to keep pages that will probably be reused within a reasonable length of time. Joel has done the heavy math and modeling but the general purpose is very simple. 1. Identify buffer pages that tend to be reused (especially index pages) 2. Identify buffer pages that won't be reused (especially tablepsace scans) 3. separate the buffer pools so that tablespace scans won't trample on pages that would have been reused if they were around just a little bit longer. [...] 14025 124 38_DB2 V7 on Z/OS Unload Utility question11_David Nance16_DWNance@FHSC.COM31_Wed, 10 Aug 2005 15:33:28 -0600553_us-ascii Has anyone used the unload utility to create a syspunch for a very large table? I'm really not interested in unloading any data at the moment, I was just trying to easily generate a syspunch file for later use. I used the from table specification and used the parm LIMIT 1, however, the utility continued to go through the entire table. I thought this was really strange behavior as I would expect the utility to end when the limit had been reached. Even when I used a when clause on a large partitioned table that would only find data in [...] 14150 188 42_Re: DB2 V7 on Z/OS Unload Utility question9_Dee Reins24_dee.reins@CENTURYTEL.COM31_Wed, 10 Aug 2005 16:39:38 -0500463_iso-8859-1 Put a time limit on the job card. The job will abend, but the sysprint with have the definition, or create an empty table (new name) just like one you want to unload. Then unload that table.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of David Nance Sent: Wednesday, August 10, 2005 2:33 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V7 on Z/OS Unload Utility question [...] 14339 120 42_Re: DB2 V7 on Z/OS Unload Utility question0_28_Missy.Case@FIRSTDATACORP.COM31_Wed, 10 Aug 2005 16:49:11 -0500382_US-ASCII Or put a sysrec DD DUMMY card. Or template the table with no data & run the unload on the empty version.

Missy Case FDR 701-275-6358







Dee Reins cc: Sent by: DB2 Data bcc: Base Discussion Subject: Re: [DB2-L] DB2 V7 on Z/OS Unload Utility question List [...] 14460 473 42_Re: DB2 V7 on Z/OS Unload Utility question15_Pradeep Gunjala31_Pradeep_Gunjala@TAX.STATE.NY.US31_Wed, 10 Aug 2005 17:58:52 -0400411_US-ASCII This will work if you can use DSNTIAUL program then you can just download only 1 record by specifying FETCH 1 ROW ONLY in SYSIN card of your select statement.

-Pradeep







Missy.Case@FIRSTDATACORP.COM Sent by: DB2 Data Base Discussion List 08/10/2005 05:49 PM Please respond to DB2 Database Discussion list at IDUG [...] 14934 28 32_Re: Pagein for read i/o under v817_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Wed, 10 Aug 2005 17:15:39 -0500579_- Stephen, Pagein /Read is not a concern if the number is less than the total pages allocated for the pool. Paging per second is of a serious concern. RMF will show paging rate.

It is presumably because the counter in DB2 control blocks is incremented in a curious way.

Immediately following a sub system restart I see the counter incrementing at a greater pace even though there is literally no paging per Mon 2, RMF. It never goes beyond the total pages allocated for the pool. I remember Joel telling that the first page reference is considered as a paging [...] 14963 214 42_Re: DB2 V7 on Z/OS Unload Utility question13_Horacio Villa17_hvilla@AR.IBM.COM31_Wed, 10 Aug 2005 19:42:14 -0300605_US-ASCII I've done that with UNLOAD ... FROM TABLE LIMIT 0

Horacio Villa





Pradeep Gunjala cc: Sent by: DB2 Data Base Subject: Re: [DB2-L] DB2 V7 on Z/OS Unload Utility question Discussion List



10/08/2005 18:58 Please respond to DB2 Database Discussion list at IDUG











This will work if you can use DSNTIAUL program then you can just download only 1 record by specifying FETCH 1 ROW ONLY in SYSIN card of your select statement. [...] 15178 45 42_Re: DB2 V7 on Z/OS Unload Utility question10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 11 Aug 2005 09:40:33 +1000443_utf-8 Hi Dave,

LIMIT seems to work for segmented tablespaces. For simple tablsepaces, it reads all the pages. (Try a quick test on SYSIBM.SYSCOPY (simple) and SYSIBM.SYSPACKAGE(segmened)). I am not sure why though, yet.

And for partitioned tablespaces, the LIMIT is applicable for each partition, when partition parallelism is activated. Haven't tested much with that yet, but LIMIT didn't seem to work on my first attempt. [...] 15224 27 42_Re: Adding Columns to a Partitioning Index17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Wed, 10 Aug 2005 19:30:00 -0500549_- This thread is interesting. couldnt this be made easier by doing a alter on the tablespace to use vacat instead of stogroup; I dont think it puts the object into a pending status due to the alter. After all you stop the pageset. Dont do the 'drop tablespace' at all. We do several things behind the scnes with adrdssu without D 2 knowing a single bit. Later when you are ready, alter back to stogroup?.....DB2 is completely blinded?... May be you can avoid renaming?... you still have to go thru rebuild of indexes but I am not sure how much [...] 15252 45 39_Re: SYSIBM.SYSCOPY in DB2 v7.1 z/OS 1.613_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 11 Aug 2005 00:51:43 GMT606_- Raymundo-baby:

Yep, I agree that granting the unwashed masses something as potentially harmful as REPAIR authority in a Production environment is not a good idea. However, I've seen people do it. The only thing that saves them, I think, is that the people using REPAIR...NOCOPYPEND are too lazy to delve further into the capabilities of the REPAIR utility. Good thing too, 'cause you can hose yourself in a big way if you don't know what you're doing or aren't careful. In Development & Test, you can grant them REPAIR with the understanding that if they clobber themselves, they're on their [...] 15298 58 55_Re: [Elephant Spam] Re: [DB2-L] EDM pool and large DBDs13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 11 Aug 2005 01:02:12 GMT301_- And the followup to that:

Q. Why do Elephants paint their toes red? A. So that they can hide in the cherry trees. ***** Q. How did Tarzan die? A. By plucking cherries from the cherry tree. *****

And another:

What does and elephant call when he breaks his toe? A toe truck! [...] 15357 124 21_Re: Large Bufferpools35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 10 Aug 2005 21:27:55 -0400401_windows-1250 Mike,

Either you mis-construed something I said, or are thinking of something (incorrect) from someone else's presentation.

There is no cpu effect for managing large pools versus small ones, since the DBM uses a hashing technique, and never has to scan pool pages to determine if the necessary data page is there, or to find available pages from one of the LRU queues. [...] 15482 54 21_Re: Large Bufferpools35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 10 Aug 2005 21:42:15 -0400424_iso-8859-1 As others have stated, either your manager is stuck in the early 80's if he ever knew anything, or just dead from the neck up.

Whatever he "feels/thinks" is just wrong and irrelevant to the way DB2 really works.

With the release of DB2 V3 we went from 4 pools to 60 pools. Now why would IBM spend millions to provide this ability if they weren't convinced it was necessary for DB2 performance? [...] 15537 15 21_Re: Large Bufferpools17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Wed, 10 Aug 2005 20:46:44 -0500681_- For curiosity sake, how does one accomplish item #2?...I would really appreciate any innovative pointers. Item #1 is perhaps answerable to a little extent by re-read, if thats all what you meant?...... 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 15553 124 32_Re: Pagein for read i/o under v817_Karthik Vinayagam18_vkarthik@YAHOO.COM31_Wed, 10 Aug 2005 23:42:39 -0500354_- Stephen,

I suspect the "available frames" mean frames eligible for stealing rather than "free" frames. for, IIRC, rsm maintains available frame queue from which it can steal rather than free frame queue. take a look at storf, if you have rmf mon iii running or access to, it will give you count of aux slots occupied by each address space. [...] 15678 29 31_Re: Up serge in CPU time in DDF24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Thu, 11 Aug 2005 00:50:24 -0500361_- Sir,

I have a few questions.

1. What is the current usage of your CPU before you turn on your websphere?

http://www.db2-dba.net/Article-Usage%20Factor.html



2. DO you know the kind of SQLs that are coming from your Websphere? If you have an online monitor you can try sampling the type of SQLs that are coming through. [...] 15708 17 31_Re: 00C90101 after DSN1COPY....24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Thu, 11 Aug 2005 01:04:42 -0500580_- Sir,

I am very grateful for your explanation. Thank you very much.

Ram

www.db2-dba.net

--------------------------------------------------------------------------------- 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 15726 47 42_Re: DB2 V7 on Z/OS Unload Utility question24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Thu, 11 Aug 2005 01:36:48 -0500327_- Usually what I do is I run a DSNTIAUL with a "where clause" but the where clause will have a predicate that has no match in the table.

For example , let us assume a table EMPLOYEE.



I will do a SELECT * FROM EMPLOYEE WHERE SOCIAL_SEC_NO='999-99-9999' ; knowing well that there is no SSN 999-99-999. [...] 15774 32 21_Re: Large Bufferpools24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Thu, 11 Aug 2005 05:58:08 -0500342_- May be you can try explaining to him about how costly it is to maintain the LRU chain and the hash table when it comes to a massive bufferpool. You can also tell him that Latch contention (from Latch manager) is significant for large bufferpools.

I have tried to create a small web page for my team members to explain the same. [...] 15807 93 32_db2 cobol quotation mark problem55_=?iso-8859-9?Q?Kadir_G=FCray_Meri=E7_=28B.T.S.Y.G.=29?=22_Kadir.Meric@AKBANK.COM31_Thu, 11 Aug 2005 16:34:57 +0300660_iso-8859-9 hi to all, 2 months ago, we wrote a COBOL program. Since DB2 uses two quotation marks('') to insert just one quotation mark('), we used the following syntax in COBOL program to replace quotation mark(') in string WS-METIN1 with two quotation marks('')

EXEC SQL SELECT REPLACE(:WS-METIN1,'''','''''') INTO :WS-METIN2 FROM SYSIBM.SYSDUMMY1 END-EXEC. EXEC SQL UPDATE GTS_TANA_GONDERI SET GTS_ANA_SMS_METIN = :WS-METIN2, GTS_ANA_STATU = 'S998' WHERE GTS_ANA_REFERANS = :GTS-ATP-REFERANS END-EXEC. so, they succeded to insert quotation mark(') FOR EXAMPLE, it is: UPDATE GTS_TANA_GONDERI SET GTS_ANA_SMS_METIN = 'TURKEY''S CAPITAL IS ANKARA' [...] 15901 60 24_Message from Db2diag.log27_DB2 Database Solutions Port26_db2dba_certified@YAHOO.COM31_Thu, 11 Aug 2005 06:43:27 -0700587_iso-8859-1 I noticed the following two messages in db2diag.log so many occurances. Anyone has any clue about them. Input is greatly appreciated.

--VG

2005-08-11-09.01.56.251000-240 I2155398H524 LEVEL: Warning PID : 4516 TID : 4508 PROC : db2syscs.exe INSTANCE: DB2 NODE : 000 DB : BQPFMIS1 APPHDL : 0-68 APPID: *LOCAL.DB2.050811125920 FUNCTION: DB2 UDB, data management, sqldOLRLoadStateFile, probe:12723 MESSAGE : Error verifying OLR state file E:\DB2\NODE0000\SQL00001\00040002.OLR - Reason Code: 1 DATA #1 : Hexdump, 4 bytes 0x40048518 : F900 0480 .... =========== [...] 15962 146 36_Re: UDB LUW timeout - Can't remember13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Thu, 11 Aug 2005 09:43:52 -0400517_iso-8859-1 In case anyone is following this thread, the problem I was having was with compiling UDB stored procedures from Visual Studio. Under Visual Studio Options, Tools, IBM DB2 Tools, there is a timeout option

Scott Hodgin, Database Administrator South Carolina Farm Bureau Insurance Company shodgin@scfbins.com

-----Original Message----- From: Hodgin, Scott Sent: Monday, July 25, 2005 12:58 PM To: 'DB2 Database Discussion list at IDUG' Subject: RE: [DB2-L] UDB LUW timeout - Can't remember [...] 16109 39 24_SQL Question DB2 z/OS V715_Tami Van Dreese27_tami.vandreese@LANDSEND.COM31_Thu, 11 Aug 2005 08:41:03 -0500325_- We have a developer who would like to create a display table that would show the intersection of two columns. An example is below. If this was a known number of values involved, I could do this with a case statement. Is there a way to code this when the number of distinct colors and items could vary from day to day? [...] 16149 76 28_Re: SQL Question DB2 z/OS V714_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 11 Aug 2005 14:51:26 +0100408_us-ascii Tami

In an application program it'd be easy (indeed in QMF it's easy also) but I have no idea how you'd create a SELECT statement with a varying number of COLUMNS depending on the number of distinct items

Sorry



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 16226 70 21_Re: Large Bufferpools35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 11 Aug 2005 09:51:46 -0400489_iso-8859-1 Some very good explanations, with neat illustrations.

Two things though; a. While any overhead that can be avoided should be, the overhead of managing hash locations, and lru queues is not significant. Meaning that you won't see any "noticeable" performance difference when switching to FIFO. If you have a real benchmark driven system, where you have 100% repeatable workload, you will be able to measure a small difference. b. I would not use fifo for a sort pool [...] 16297 86 44_Problem with DB2CC Stored Procedure DSNACCDL13_Bell, Raymond22_raymond.bell@LANDG.COM31_Thu, 11 Aug 2005 15:03:55 +0100502_iso-8859-1 All,

DB2 UDB PE for Windows V8 FP6A, DB2 for z/OS V7.

Bit of a puzzle this one. I've setup the DB2 Control Center (sp) stuff to interrogate a z/OS DB2 subsystem. I've had to run SDSNSAMP member DSNTIJCC and once I got it right it seems fine. When I fire up the Control Center I can click the drop-down menu for my target z/OS subsystem and see options for 'Databases', 'Table Spaces', 'Catalog Tables', etc. Most of the options I've tried so far work, except 'Data Sets'. [...] 16384 157 36_Re: UDB LUW timeout - Can't remember12_Martin Hubel17_Martin@MHUBEL.COM31_Thu, 11 Aug 2005 10:09:27 -0400609_- Scott,

I think the value you're looking for is in the db2cli.ini file.

Keyword Description: Delay (in seconds) between checks for a query timeout.

db2cli.ini Keyword Syntax: QueryTimeoutInterval = 0 | 5 | positive integer

Default Setting: 5 seconds

hth--Martin





=========================================================== Martin Hubel MHC Inc. www.mhubel.com +1 905-764-7498 +1 416-670-7498 mobile +1 905-764-8411 fax

Check out www.db-hq.net Your Headquarters of How-To for DB2 =========================================================== [...] 16542 90 28_Re: SQL Question DB2 z/OS V714_Seth Bienstock19_sethdb2@COMCAST.NET31_Thu, 11 Aug 2005 14:17:48 +0000494_- If you're trying to do all of this with only using SQL, it ain't gonna happen !

The only way this could possibly happen with 1 SQL statement is if you were to set a realistic (true) limit on the number of values in EITHER of the dimensions. For instance, if you were to be able to definitively say that "there can be no more than 50 colors", then I do believe that a single SQL statement could be constructed to do this. But without that limitation I don't believe it's possible. [...] 16633 122 48_Re: Problem with DB2CC Stored Procedure DSNACCDL10_Tee, Chris21_Chris.Tee@EU.SONY.COM31_Thu, 11 Aug 2005 15:18:15 +0100675_iso-8859-1 Raymond

Is the STEPLIB of the proc APF-authorised?

Chris

-----Original Message----- From: Bell, Raymond [mailto:raymond.bell@LANDG.COM] Sent: 11 August 2005 15:04 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Problem with DB2CC Stored Procedure DSNACCDL



All,

DB2 UDB PE for Windows V8 FP6A, DB2 for z/OS V7.

Bit of a puzzle this one. I've setup the DB2 Control Center (sp) stuff to interrogate a z/OS DB2 subsystem. I've had to run SDSNSAMP member DSNTIJCC and once I got it right it seems fine. When I fire up the Control Center I can click the drop-down menu for my target z/OS subsystem and see options for [...] 16756 169 48_Re: Problem with DB2CC Stored Procedure DSNACCDL0_19_mike.holmans@BT.COM31_Thu, 11 Aug 2005 15:31:08 +0100531_iso-8859-1 Chris, Raymond,

It's authorized on mine, and I get the same error (although at a lower level of fixpack). But I didn't bother to chase it up when I got it because I wasn't actually interested.

Mike Holmans Lead DB2 Professional BT Exact OneIT Operational Integrity -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Tee, Chris Sent: 11 August 2005 15:18 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Problem with DB2CC Stored Procedure DSNACCDL [...] 16926 93 21_Re: Large Bufferpools24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Thu, 11 Aug 2005 09:30:36 -0500383_- Sir,

Thank you for your feedback. Can you explain to me as to why you would not use FIFO for DSNDB07? I was always taught to use FIFO for sort space.

Also , are you saying that for a totally resident tablespace ( all the pages are in a dedicated buffer ) FIFO will not reduce overhead? am I understanding you correctly? If so , can you explain to me the reason? [...] 17020 177 48_Re: Problem with DB2CC Stored Procedure DSNACCDL13_Bell, Raymond22_raymond.bell@LANDG.COM31_Thu, 11 Aug 2005 15:41:33 +0100407_iso-8859-1 Hi guys,

Ours are/were in a WLM that had loads of non-APF authorised libraries. I'm moving them now, but it doesn't sound like it'll make a difference (cheers, Mike). I'm trying out all the DB2CC options to see if it's dangerous/safe to roll out to the Dev Plebs. But I think it might be a bit like the old Repair thingey - give 'em enough rope/GUI tools and they'll hang themselves. [...] 17198 198 48_Re: Problem with DB2CC Stored Procedure DSNACCDL10_Tee, Chris21_Chris.Tee@EU.SONY.COM31_Thu, 11 Aug 2005 15:48:33 +0100674_iso-8859-1 Raymond

Do you have the DAS started?

Chris

-----Original Message----- From: Bell, Raymond [mailto:raymond.bell@LANDG.COM] Sent: 11 August 2005 15:42 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Problem with DB2CC Stored Procedure DSNACCDL



Hi guys,

Ours are/were in a WLM that had loads of non-APF authorised libraries. I'm moving them now, but it doesn't sound like it'll make a difference (cheers, Mike). I'm trying out all the DB2CC options to see if it's dangerous/safe to roll out to the Dev Plebs. But I think it might be a bit like the old Repair thingey - give 'em enough rope/GUI tools and they'll hang [...] 17397 228 48_Re: Problem with DB2CC Stored Procedure DSNACCDL13_Bell, Raymond22_raymond.bell@LANDG.COM31_Thu, 11 Aug 2005 15:53:36 +0100325_iso-8859-1 Guys,

I was about to say 'What's the DAS?' but I've just fired up DB2CC and clicked on 'Data Sets' and Shazam! It works! So the only problem was the APF bit.

Mike, if you care, I'd check that there aren't any non-APF authorised libraries in the WLM JCL managing the DSNACC% SPs - worked for me. [...] 17626 269 42_Re: DB2 V7 on Z/OS Unload Utility question12_Ray Williams23_Ray.Williams@UNITED.COM31_Thu, 11 Aug 2005 09:55:25 -0500390_US-ASCII IBM provides a DRAW Edit Macro (REXX) that works almost IDENTICAL to a DRAW command in QMF. In fact I have copied it and added the ability to DRAW SELECT statements in a CSV (Comma Seperated) format. The delivered version will draw Select, Insert, Update, Delete statements, as well as LOAD statements (DSNTIAUL format). It works pretty good once you get use to the syntax. . [...] 17896 13 29_Dividing two decimals columns10_Tara Mason27_tara.mason@SAFETY-KLEEN.COM31_Thu, 11 Aug 2005 09:55:33 -0500633_- We have a requirementment to divide 2 decimal columns and retrieve the value to 3 decimal positions.

The two decimal values in the colums are as follows:

--------------------------------------------------------------------------------- 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 17910 32 29_Dividing two decimals columns10_Tara Mason27_tara.mason@SAFETY-KLEEN.COM31_Thu, 11 Aug 2005 10:07:53 -0500535_- Sorry for the incomplete message earlier....

We have a requirementment to divide 2 decimal columns and retrieve the value to 3 scalable positions.

The two decimal values in the columns are as follows:

684425.000 and 558726.00

If you divide these with a calculator 684425.00 / 558726.00, you receive 1.224.... Also, if I just divide these as literals in my SQL statement I receive this value. But when these are two columns in a table, I can't seem to get this same value. The best I can do is 1.200. [...] 17943 65 33_Re: Dividing two decimals columns14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 11 Aug 2005 16:18:09 +0100473_us-ascii Have you tried

DEC(col1, 8, 3) / DEC(col2, 8,3)??



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Tara Mason Sent: 11 August 2005 16:08 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Dividing two decimals columns [...] 18009 60 33_Re: Dividing two decimals columns10_Dave Churn19_davechurn@GMAIL.COM31_Thu, 11 Aug 2005 10:21:32 -0500340_ISO-8859-1 Tara,

I've run into this with DECIMAL math. In searching through the manuals, I found that DB2 will only save 15 digits of precision around for decimal math. The easy way we got around this was to cast the DB2 columns as FLOAT before the division and cast them back to DECIMAL after the division. Here is an example. [...] 18070 91 33_Re: Dividing two decimals columns14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 11 Aug 2005 16:26:36 +0100430_us-ascii I've just created a table with 2 decimal (10,3) columns. Populated them with Taras values and just done a SELECT COL1/COL2.... The result I get is 1.22497 not Taras 1.200 (and DECIMAL(col1/col2,8,3) returns 1.224 as expected) so I am confused now



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 18162 132 46_Migrating DB2 V8 from z/OS to another platform15_Zander, Barb J.19_bzander@STATE.ND.US31_Thu, 11 Aug 2005 10:26:00 -0500426_- We are looking at moving DB2 V8 from the z/OS platform to another(probably Unix or Linux) and have a couple of questions.

1. What kind of effort does it take to move the data? 2. Are there tools/built-in commands to handle converting the data and bringing down the ddl? 3. Any major stumbling blocks anyone has encountered. 4. Did you have to do a lot of application code changes because of the platform change? [...] 18295 71 33_Re: Dividing two decimals columns12_Ray Williams23_Ray.Williams@UNITED.COM31_Thu, 11 Aug 2005 10:27:20 -0500562_US-ASCII Try this . . . SELECT dec(DEC(684425.000,9,3) / DEC(558726.000,9,3),9,3) FROM SYSIBM.SYSDUMMY1

Ray Williams DBA - WHQKO United Airlines (847) 700-4251



-----Original Message----- From: tara.mason@SAFETY-KLEEN.COM [mailto:tara.mason@SAFETY-KLEEN.COM] Sent: Thursday, August 11, 2005 10:08 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Dividing two decimals columns

Sorry for the incomplete message earlier....

We have a requirementment to divide 2 decimal columns and retrieve the value to 3 scalable positions. [...] 18367 123 33_Re: Dividing two decimals columns10_Dave Churn19_davechurn@GMAIL.COM31_Thu, 11 Aug 2005 10:44:20 -0500425_ISO-8859-1 Phil,

Our original test case was using larger numbers, DECIMAL(17,6). Our solution is found with the following query.

SELECT 26.000260 / 286.000000 , DECIMAL (26.000260,17,6) / DECIMAL (286.000000,17,6) , DECIMAL (DECIMAL(26.000260,17,6) / DECIMAL (286.000000,17,6) ,17,6) , FLOAT (26.000260) / FLOAT (286.000000) , DECIMAL ( FLOAT (26.000260) / FLOAT (286.000000) , 17,6) FROM SYSIBM.SYSDUMMY1 [...] 18491 219 50_Re: Migrating DB2 V8 from z/OS to another platform14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 11 Aug 2005 16:47:43 +0100343_us-ascii Rather than answers, I guess most of us will be asking "Why?" - I'm guessing it's to do with perceived costs - right? (or is there another reason - please let us know)

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 18711 68 33_Re: Dividing two decimals columns14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 11 Aug 2005 16:49:15 +0100584_us-ascii True,

But Taras examples looked like DECIMAL(9,3) unless the columns themselves are defined differently

Tara - what are the column definitions of the table??

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dave Churn Sent: 11 August 2005 16:44 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Dividing two decimals columns [...] 18780 128 33_Re: Dividing two decimals columns9_Mike Bell21_mbell11a1@VERIZON.NET31_Thu, 11 Aug 2005 10:51:59 -0500556_windows-1250 This behavior is dependent on DEC31 setting. check your ZPARM and precomile options. DB2 will keep the precision before the decimal point first and truncate from the right. DEC31 simply uses 31 digit decimal precision and then truncates to the result set.



Mike Bell HLS Technologies -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: Thursday, August 11, 2005 10:27 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Dividing two decimals columns [...] 18909 87 28_Re: SQL Question DB2 z/OS V711_Rob Barbour22_rbarbour@ESAIGROUP.COM31_Thu, 11 Aug 2005 11:56:57 -040041_ISO-8859-1 Doesn't sound like it.

18997 350 50_Re: Migrating DB2 V8 from z/OS to another platform0_20_simon.george@UBS.COM31_Thu, 11 Aug 2005 16:57:49 +0100464_iso-8859-1 I'm with Phil on this one!

We have just spent 18-months and many $ trying to move a DB2 for z/OS system to Linux albeit to Oracle. It was a costly disaster. If you are moving to DB2 on LUW then you might not hit all the code page conversion issues, performance, SQL incompatibility issues we hit but you will almost certainly hit the availability, scalability and reliability issue of the basic hardware compared to z/OS (to mention a few)! [...] 19348 162 50_FW: Migrating DB2 V8 from z/OS to another platform15_Zander, Barb J.19_bzander@STATE.ND.US31_Thu, 11 Aug 2005 10:58:32 -0500419_- You are correct. Management has bought into the idea that it will be cheaper on another platform and they are afraid that in 10 years from now they will not be able to find people with z/OS experience.

Barb

_____________________________________________





We are looking at moving DB2 V8 from the z/OS platform to another(probably Unix or Linux) and have a couple of questions. [...] 19511 17 49_Re: DB2 Version 8, DFSORT and other sort packages9_Jim Ruddy18_jaruddy@US.IBM.COM31_Thu, 11 Aug 2005 10:57:34 -0500309_- Just as with any other additional/separate fixes in z/OS that may be required and not necessarily included as part of ones normal maintenance schedule, the system programmer will have to handle that on a case by case basis. Is the particular examples you have in mind or s this just a general concern? [...] 19529 263 54_Re: FW: Migrating DB2 V8 from z/OS to another platform14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 11 Aug 2005 17:05:14 +0100338_us-ascii You have my sympathies

Of course, it all depends on what "cheaper" means. I am sure you will be inundated with security (ever heard of a z/OS virus), performance/scalability (how big are your applications anyway), reliability (would you like YOUR bank account or ATM system running on a "lesser" platform etc etc etc [...] 19793 19 33_Re: Dividing two decimals columns10_Tara Mason27_tara.mason@SAFETY-KLEEN.COM31_Thu, 11 Aug 2005 11:00:55 -0500743_- Thanks for all the responses.

My columns are defined as 15,3 and 15,2 respectively.

The following are the two examples that worked - thanks so much for your help...

DECIMAL(FLOAT(col1) / FLOAT(col2),8,3)

DEC(DEC(col1,9,3) / DEC(col2,9,3),9,3)

--------------------------------------------------------------------------------- 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 19813 356 48_Re: Problem with DB2CC Stored Procedure DSNACCDL14_David S. Waugh16_dswaugh@LANL.GOV31_Thu, 11 Aug 2005 10:12:44 -0600486_US-ASCII Just a wild guess, but could it be lack of proper RACF authority to access/list the datasets? What signon id are you coming through to DB2 z/OS with? . Thanks, . David Waugh IM-3 DB2 Database Administration Los Alamos National Laboratory







"Bell, Raymond" Sent by: DB2 Data Base Discussion List 08/11/2005 08:03 AM Please respond to DB2 Database Discussion list at IDUG [...] 20170 143 53_z/OS V7 Template and Listdef - dataset name not right13_Rhea, Matthew24_Matthew_Rhea@CONSECO.COM31_Thu, 11 Aug 2005 11:22:27 -0500298_iso-8859-1 Hello list,

I'm using a template and listdef for image copies. I want the ICTYPE included in the GDG of the image copy. I understand the ICTYPE should be F for full, I for incremental, or C for changelimit. When I specify changelimit, I get an ICTYPE of I, not C. Any ideas? [...] 20314 63 21_Re: Large Bufferpools15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Thu, 11 Aug 2005 18:41:17 +0200298_us-ascii On Wed, 10 Aug 2005 14:03:35 -0500, you wrote:

>The basic reason is that he feels that with everything in one bufferpool DB2 >should figure out how to manage it the most efficient way. By increasing >the size it will allow you to keep more data in the bufferpool reducing i/o. [...] 20378 353 54_Re: FW: Migrating DB2 V8 from z/OS to another platform9_Dee Reins24_dee.reins@CENTURYTEL.COM31_Thu, 11 Aug 2005 11:53:38 -0500546_iso-8859-1 I just attended a DB2 System's class. The instructor informed us that IBM is having a big push to get people trained on the mainframe. Also, our head of IT has admitted that everytime there was a project to convert to another platform because it was cheaper. It has never been cheaper, and the project was a failure. I would ask to see the cost per user for each of the types of systems, mainframe, versus client server. What I have seen on knowledge base shows the mainframe to be about 1/3 the cost of client server per user. I [...] 20732 308 54_Re: FW: Migrating DB2 V8 from z/OS to another platform10_Irwin, Jim19_Jim.Irwin@OA.MO.GOV31_Thu, 11 Aug 2005 12:07:54 -0500476_us-ascii Forgive me a wry smile, but the "cheaper" and "won't be able to find experienced staff" arguments are the same ones IBM used to get us to begin our move off of IDMS.







________________________________



From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Zander, Barb J. Sent: 11 August 2005 16:59 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] FW: Migrating DB2 V8 from z/OS to another platform [...] 21041 424 54_Re: FW: Migrating DB2 V8 from z/OS to another platform10_Doyle Mark21_Mark.Doyle@WIN-NA.COM31_Thu, 11 Aug 2005 12:09:50 -0500722_us-ascii Try the dinosaur myth 2004 update at http://www-306.ibm.com/software/info/zseries/

Also some info at http://www.arcati.com/papers.html

HTH

Mark -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dee Reins Sent: Thursday, August 11, 2005 11:54 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] FW: Migrating DB2 V8 from z/OS to another platform



I just attended a DB2 System's class. The instructor informed us that IBM is having a big push to get people trained on the mainframe. Also, our head of IT has admitted that everytime there was a project to convert to another platform because it was cheaper. It has never [...] 21466 209 57_Re: z/OS V7 Template and Listdef - dataset name not right20_Pauliah, Alexander J33_Alexander_J_Pauliah@HOMEDEPOT.COM31_Thu, 11 Aug 2005 13:42:49 -0400634_us-ascii Matthew,

In this case ICTYPE should be only 'F' or 'I' (or 'T' incase the copy was terminated) - There is no 'C' The changelimit option forces your copy to perform an Incremental copy because the percentage of your changed pages is less than 10%.

Alex Pauliah Database Administrator Home Depot - Austin Technology Center 512 977 2228 800 946 3376 4667216 -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Rhea, Matthew Sent: Thursday, August 11, 2005 11:22 AM To: DB2-L@www.idugdb2-l.org Subject: z/OS V7 Template and Listdef - dataset name not right [...] 21676 315 57_Re: z/OS V7 Template and Listdef - dataset name not right13_Rhea, Matthew24_Matthew_Rhea@CONSECO.COM31_Thu, 11 Aug 2005 12:55:09 -0500391_iso-8859-1 Maybe I'm misinterpreting the Utility Manual. Is the manual not correct?

From the DB2 V7 Utility Manual:

Table 95. UTILITY variables Variable Description

&ICTYPE. or &IC. Single character image copy type. Valid only for image copy

templates. The substitution is governed by whether a full

image copy (F), an incremental image copy (I) or a [...] 21992 93 21_Re: Large Bufferpools35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 11 Aug 2005 14:01:05 -0400435_iso-8859-1 Yes, but it goes far, far, beyond thresholds. Some objects may be large/random and will never get a good percentage of the pages found in the pool. Others may have small working sets and can have many pages found in the pool. These don't belong together. Same for sequential.

The way to get good performance is to group into pools based upon access type and working set size (not related to catalog statistics). [...] 22086 143 54_Re: FW: Migrating DB2 V8 from z/OS to another platform12_Martin Hubel17_Martin@MHUBEL.COM31_Thu, 11 Aug 2005 14:06:17 -0400377_- Hi Barb,

As I still do both z/OS and LUW, let me offer the following short responses:

1. What kind of effort does it take to move the data?

It depends on how much data, and any conversions required. This is probably the easiest thing on your list.

2. Are there tools/built-in commands to handle converting the data and bringing down the ddl? [...] 22230 173 54_Re: FW: Migrating DB2 V8 from z/OS to another platform12_Smith, Allan23_Allan_Smith@KYFBINS.COM31_Thu, 11 Aug 2005 14:11:43 -0400380_us-ascii -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Martin Hubel Sent: Thursday, August 11, 2005 2:06 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] FW: Migrating DB2 V8 from z/OS to another platform



Hi Barb,

As I still do both z/OS and LUW, let me offer the following short responses: [...] 22404 318 54_Re: FW: Migrating DB2 V8 from z/OS to another platform12_Baron Carter31_baron.carter@ROCKETSOFTWARE.COM31_Thu, 11 Aug 2005 14:32:40 -0400882_us-ascii Check some of these out





http://www.vnunet.com/computing/features/2072215/mainframes-return-busin ess-landscape

http://www.arcati.com/papers.html

http://www-306.ibm.com/software/info/zseries/ Dinosaur Myth





Baron Carter

Rocket Software





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dee Reins Sent: Thursday, 11 August, 2005 11:54 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: FW: Migrating DB2 V8 from z/OS to another platform





I just attended a DB2 System's class. The instructor informed us that IBM is having a big push to get people trained on the mainframe. Also, our head of IT has admitted that everytime there was a project to convert to another platform because it was cheaper. It has never been cheaper, and [...] 22723 56 31_Re: Up serge in CPU time in DDF13_Malik, Munwar31_munwar.malik@ASSOCIATES.DHS.GOV31_Thu, 11 Aug 2005 14:51:21 -0400464_US-ASCII I sent this e-mail yesterday and I have not heard from anybody. May be did not get delivered so I am sending it again. I am looking for general guielines.



Munwar Malik 703-921-6151 Munwar.Malik@associates.dhs.gov





MUNWAR MALIK To: DB2-L@WWW.IDUGDB2-L.ORG 08/10/2005 01:33 cc: PM Subject: Up serge in CPU time in DDF









I have an application that is on the following platforms: [...] 22780 41 24_db2 task center question27_DB2 Database Solutions Port26_db2dba_certified@YAHOO.COM31_Thu, 11 Aug 2005 12:18:21 -0700345_iso-8859-1 Does anyone know what kind of maintenance we need to do on "udb task center"?. We have set of job scheduled thru udb task center that run daily. We noticed that db2 inserts rows into several tables whenever an event like 'job runs, update job properties etc' occurs. Now we noticed a major slowness with task center. Any advise? [...] 22822 162 31_Re: Up serge in CPU time in DDF12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Thu, 11 Aug 2005 15:20:09 -0400420_us-ascii Are you running WLM on mainframe under z/os? what class are these threads coming in as? Jeff







"Malik, Munwar" Sent by: DB2 Data Base Discussion List 08/11/2005 02:51 PM Please respond to DB2 Database Discussion list at IDUG



To: DB2-L@WWW.IDUGDB2-L.ORG cc: Subject: Re: [DB2-L] Up serge in CPU time in DDF [...] 22985 32 21_Re: Large Bufferpools15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Thu, 11 Aug 2005 21:21:55 +0200556_us-ascii On Thu, 11 Aug 2005 09:30:36 -0500, you wrote:

>Sir, > > Thank you for your feedback. Can you explain to me as to why you would >not use FIFO for DSNDB07? I was always taught to use FIFO for sort space. >

Sorts take place in memory (e.g. bufferpools) with intermediate results written out to DASD if the memory space needs to be reused. You wouldn't want pages that are being heavily accessed / updated to be written to DASD based on a FIFO algorithm. There might be pages that are less heavily accessed but read in later. They [...] 23018 95 31_Re: Up serge in CPU time in DDF15_Campbell, Wayne28_Wayne.Campbell@COURTS.WA.GOV31_Thu, 11 Aug 2005 12:49:49 -0700403_us-ascii We had saw this when we started using WebSphere. What was happening was that it was creating hundreds of SQL statements where it could use one. The developers made changes to reduce the number of SQL statement created. Isn't point and Click programming wonderful.



Wayne Campbell DB2 DBA Administrative Office of the Courts (360) 705-5268 Email: wayne.campbell@courts.wa.gov [...] 23114 94 48_[z/OS v7 and Visual Explain] -567 SQLSTATE 4250314_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM31_Thu, 11 Aug 2005 15:00:27 -0500507_US-ASCII When trying to dynamically explain some SQL I have users getting a -567 and sqlstate of 42503.

Unfortunately, VE does not return all the package-name nor the privilege.

When looking up both of these, I am confused:

-567 bind-type AUTHORIZATION ERROR USING auth-id AUTHORITY PACKAGE = package-name PRIVILEGE = privilege Explanation: The authorization ID given does not have the privilege indicated, and cannot invoke the indicated subcommand against the indicated package. [...] 23209 375 57_Re: z/OS V7 Template and Listdef - dataset name not right13_Rhea, Matthew24_Matthew_Rhea@CONSECO.COM31_Thu, 11 Aug 2005 15:48:23 -0500412_iso-8859-1 FYI...

When I remove the OPTIONS PREVIEW from the control card, the datasets are generated correctly, with the 'C' in the name.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Rhea, Matthew Sent: Thursday, August 11, 2005 12:55 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: z/OS V7 Template and Listdef - dataset name not right [...] 23585 290 50_Re: Migrating DB2 V8 from z/OS to another platform25_Richard Cotterill/Bigpond24_rl_cotterill@BIGPOND.COM31_Fri, 12 Aug 2005 10:09:13 +1000540_iso-8859-1 Migrating DB2 V8 from z/OS to another platformHi, I am in a similar situation. A few issues I have ran into so far.

1. Not all SQL is compatible ( I have two situations where SQL will work on the mainframe but not on Windows/AIX - 2. Codepage issues If you are like us and had DB2 on the mainframe set to CCSID=500 you may have some issues with the 7 special characters Also most non-printable characters do to convert to the same value ( eg x'FF' x'9F') this means if you have character data which is not the normal [...] 23876 263 35_Migration from Mainframe to DB2 LUW4_Phil22_pgunning@GUNNINGTS.COM31_Thu, 11 Aug 2005 20:53:48 -0400594_US-ASCII







Hi Barb, Many have done this already and for quite a few years. In many cases it makes sense and in many it doesn't. I have a current client that is moving their last mainframe DB2 application to DB2 on Windows. They previously moved everything else to SAP on SQL Server (unfortunately). They will save major outsourcing fees and licensing costs with this move. But every situation is different and there has to be a compelling reason to move off of the mainframe. I am training the current mainframe DB2 DBAs on DB2 on Windows. As you know, it is [...] 24140 130 21_Re: Large Bufferpools35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 11 Aug 2005 21:00:20 -0400298_iso-8859-1 The sort pool activity may be very different than you think. There is always some, to a lot (sometimes 50%) of random access to pages. It's my opinion, but not as benchmarked data, that the LRU algorithms will handle the activity to the DSNDB07 objects better than a FIFO approach. [...] 24271 24 27_Re: EDM pool and large DBDs10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Aug 2005 10:09:33 +0200745_US-ASCII Hi all estimeed lister

An (indicative) table about what affect DBD size is in the paper:



DB2 UDB for z/OS & OS/390 - EDM Pool Management, Monitoring and Tuning - by George Wise >>> (at page 12)

HTH

Max Scarpa Renegade DB2 sysprog

--------------------------------------------------------------------------------- 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 24296 125 48_Re: Problem with DB2CC Stored Procedure DSNACCDL13_Bell, Raymond22_raymond.bell@LANDG.COM31_Fri, 12 Aug 2005 09:43:29 +0100361_iso-8859-1 Hi Dave,

Nah, it was just that all the libraries in my WLM STC weren't APF authorised. I just moved them to a WLM that is and it worked. Well, kind of. When I tried to edit one of the datasets it informed me that the DAS wasn't active. Chris Tee, where are you when I need you? Anyway, at that point I've done enough for my own interest. [...] 24422 23 49_Re: DB2 Version 8, DFSORT and other sort packages10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Aug 2005 10:50:48 +0200387_US-ASCII Jim

Of course it's sysprog's job to keep track of maintenance needed. But you've to at least to know if a maintenance is DB2 related or z/OS related, just to know WHO should apply PTFs.

I manage DB2, not DFSORT we don't 'have' (we have CA-SORT, are you happy Phil ? ;-)) so as far as I'm concerned this is **the sort program** I've to deal with and maintain. [...] 24446 113 37_Stored Procedure DSNWSPM does not run0_18_PPantazi@LAIKI.COM31_Fri, 12 Aug 2005 12:08:27 +0300401_us-ascii

DB2 for OS/390 and z/OS Version 7 DB2 Development Center V8.2.2

When ever I try to run the stored procedure SYSPROC.DSNWSPM it always fails. I tried all manuals and also the internet but could not find any reference about this problem.

The reference I found was the PTF UQ64926 .

Has anyone else experienced this before? What is the solution to the problem? [...] 24560 25 36_LOCKSIZE LOCKMAX and LOCK ESCALATION12_Adam Baldwin19_y0027528@ES.IBM.COM31_Fri, 12 Aug 2005 04:08:40 -0500425_- O.K. fellow listers, I know that I should know this but it's been a long night and my head has swapped out. In the Admin Guide (V7)P.672 it states that: LOCKSIZE Default for LOCKMAX ANY SYSTEM Other 0

My question is with LOCKSIZE specified as a value other than ANY and LOCKMAX left to default (i.e. 0) can escalation still occur or will DB2 always return a resource unavailable message once/if NUMLKTS is hit? [...] 24586 27 40_Re: LOCKSIZE LOCKMAX and LOCK ESCALATION10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Aug 2005 11:28:26 +0200784_US-ASCII LOCKMAX specification = 0 disables lock escalation so you cannot promoted intent locks to gross locks. ONLY for LOCKSIZE ANY LOCKMAX is defaulted to SYSTEM.

HTH

Max Scarpa

Senior DB2 Sysprog

Hell data center Earth's core Roasted Chicken Boulevard Lost Souls Building Circle 69

--------------------------------------------------------------------------------- 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 24614 12 40_Re: LOCKSIZE LOCKMAX and LOCK ESCALATION12_Adam Baldwin19_y0027528@ES.IBM.COM31_Fri, 12 Aug 2005 04:34:24 -0500631_- Thanks Max - I just needed a voice in the wilderness to put my head straight. In that case LOCKSIZE PAGE and default LOCKMAX will disable escaltion - correct?

--------------------------------------------------------------------------------- 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 24627 33 40_Re: LOCKSIZE LOCKMAX and LOCK ESCALATION10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Aug 2005 11:56:09 +0200395_US-ASCII Hi Adam

Lock escalation will be triggered with LOCKSIZE ANY,PAGE and ROW when you hit some system threshold value (NUMLKTUS). For some critical tablespaces lock escalation is disabled putting LOCKMAX 0 (default if LOCKSIZE isn't ANY).

If you don't trust me (as you HAVE to do usually :-)) see SQL Reference, table at CREATE TABLESPACE when describing LOCKMAX option. [...] 24661 17 40_Re: LOCKSIZE LOCKMAX and LOCK ESCALATION12_Adam Baldwin19_y0027528@ES.IBM.COM31_Fri, 12 Aug 2005 05:14:23 -0500370_- Thanks again - and yes, of course I trust you! I just needed confirmation that the default for a tablespace defined with locksize page is lockmax zero and, therefore, no escalation. i.e. one has to specifically enable escalation by specifying lockmax.

My tired head has just about swapped back in although I think that I've lost some areas above the line. [...] 24679 17 40_Re: LOCKSIZE LOCKMAX and LOCK ESCALATION10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Aug 2005 12:24:16 +0200562_US-ASCII Hope I solved some doubts. And try to rest a little :-)))

Cheers

Max

--------------------------------------------------------------------------------- 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 24697 33 49_Re: DB2 Version 8, DFSORT and other sort packages12_Roy Cornford19_cornford@US.IBM.COM31_Fri, 12 Aug 2005 05:44:06 -0500588_- Max,



it's not clear to me what the issue is, can you give an example? DFSORT is no different to say DFSMS for example. The maintenance is separate from DB2. If there should be cross system dependancies, then these will be handled in the same way as other products. For example in the program directory, installation guide or HOLDDATA. The main difference for an IT shop, I see is that you'll need a basic maintenance policy for DFSORT, unless it's already being handled under the general MVS one. Handling DFSORT the same as your key MVS products (like DFSMS) would [...] 24731 136 67_[z/OS v7] Visual Explain dynamic explain gets -567 & sqlstate 4250314_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM31_Fri, 12 Aug 2005 07:44:33 -0500347_US-ASCII I posted this late yesterday (central US time) so in case the right person missed it (open mouth wide, reverberate the vocal chords, switch loud on) ...HELP!

I have users getting a -567 and sqlstate of 42503 when using the dynamic explain in Visual Explain. When looking up both of these error codes, I get a little confused: [...] 24868 42 49_Re: DB2 Version 8, DFSORT and other sort packages10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Aug 2005 14:58:27 +0200490_US-ASCII Hi Roy

I'm guessing what could be the problems , in theory,with this 'hidden' DFSORT that you DON'T have but actually it's used by V8.

We apply regularly (more or less) HIPERs or the needed PTFs for our products and the sort product is CA-SORT. This is official sort here, known by our managers as well. But if I have a 'hidden' sort product it could be happend that it's not maintaned well or at all as it's not clear to me how DFSORT maintenance is performed. [...] 24911 27 50_UDB LUW 8.2.2 - Batch stored procedure deployment?13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Fri, 12 Aug 2005 09:04:48 -0400480_iso-8859-1 Hi list,

Udb luw 8.2.2.

I know you can deploy multiple stored procedures from one udb server to another using development center.

My question is, is there a way to use batch commands of some kind to mass deploy stored procedures from one server to another without using development center?

Thanks,

Scott Hodgin, Database Administrator South Carolina Farm Bureau Insurance Company shodgin@scfbins.com [...] 24939 120 71_Re: [z/OS v7] Visual Explain dynamic explain gets -567 & sqlstate 425030_20_peter.n.gavin@AIB.IE31_Fri, 12 Aug 2005 14:06:22 +0100516_us-ascii Hi Mark,

We get our users to create their own plan_table first, and when going into the dynamic explain window, changing the SQLID to their own ID. That writes the dyn explain to their one.... hope that helps or have i misinterpreted? Cheers, Pete





Mark E Vickers cc: Sent by: DB2 Data Subject: [DB2-L] [z/OS v7] Visual Explain dynamic explain gets -567 & sqlstate 42503 Base Discussion List [...] 25060 99 49_Re: DB2 Version 8, DFSORT and other sort packages0_17_sjvagnier@AEP.COM31_Fri, 12 Aug 2005 10:11:11 -0400501_US-ASCII Hi Max, our MVS sysprog had deleted the FMID for DFSORT when applying MVS maintenance since our sort utility is also CA-SORT. Now that we are going to be migrating to DB2 V8 in the near future, the MVS sysprog will add the FMID for DFSORT and apply any DFSORT maintenance when also applying maintenance for the other MVS FMID's. So even though DFSORT is "hidden" and we don't have a license for DFSORT, we are going to start applying maintenance to DFSORT since DB2 V8 will be using it. [...] 25160 454 71_Re: [z/OS v7] Visual Explain dynamic explain gets -567 & sqlstate 4250314_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM31_Fri, 12 Aug 2005 09:27:36 -0500322_US-ASCII Pete, The common plan & statement tables are linked to ispf panel compiles so that the binds all explain to the same table which we monitor for performance etc., so it is too imbedded to be changed.

I am hoping someone can tell me how to allow the programmers to issue the SET CURRENT SQLID = 'PGMR'. [...] 25615 154 49_Re: DB2 Version 8, DFSORT and other sort packages10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 12 Aug 2005 16:32:06 +0200715_US-ASCII An extra work with an extra cost with the risk of missing some upgrade.









sjvagnier@AEP.COM Sent by: DB2 Data Base Discussion To List DB2-L@WWW.IDUGDB2-L.ORG Subject Re: [DB2-L] DB2 Version 8, DFSORT 12/08/2005 16.11 and other sort packages



Please respond to DB2 Database Discussion list at IDUG











Hi Max, our MVS sysprog had deleted the FMID for DFSORT when applying MVS maintenance since our sort utility is also CA-SORT. Now that we are going to be migrating to DB2 V8 in the near future, the MVS sysprog will add the FMID for DFSORT and apply any [...] 25770 216 71_Re: [z/OS v7] Visual Explain dynamic explain gets -567 & sqlstate 4250314_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 12 Aug 2005 15:39:28 +0100439_us-ascii To do a SET CURRENT SQLID the id to be set MUST be a valid secondary authid of the user issuing the SET

SO

They will ALL have to have a secondary id of PGMR which (assuming RACF) means that they will all have to be attached to the PGRM group



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 25987 204 49_Re: DB2 Version 8, DFSORT and other sort packages0_17_sjvagnier@AEP.COM31_Fri, 12 Aug 2005 10:48:30 -0400454_US-ASCII The MVS sysprog does not think adding DFSORT maintenance to the regular MVS maintenance cycle is significant. In my opinion, adding DFSORT maintenance so support DB2 V8 utilities is not a show stopper for upgrading to DB2 V8.

**************************************** Steve Vagnier American Electric Power Database Administration One Riverside Plaza Columbus, Ohio 43215 Email: sjvagnier@aep.com Phone: 614-716-3677 Audinet: 200-3677 [...] 26192 86 41_Re: Stored Procedure DSNWSPM does not run15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Fri, 12 Aug 2005 17:00:54 +0200612_us-ascii Pantazis,

are there no messages in the syslog of the address space where the stored procedure itself runs in? Plus: did you check the z/OS syslog itself for any RACF messages?

Regards,

Marcel.

On Fri, 12 Aug 2005 12:08:27 +0300, you wrote:

> >DB2 for OS/390 and z/OS Version 7 >DB2 Development Center V8.2.2 > >When ever I try to run the stored procedure SYSPROC.DSNWSPM it always >fails. I tried all manuals >and also the internet but could not find any reference about this problem. > >The reference I found was the PTF UQ64926 . > >Has anyone else experienced [...] 26279 45 27_Re: EDM pool and large DBDs15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Fri, 12 Aug 2005 17:19:31 +0200718_us-ascii And the paper (Acrobat format) can be found at http://www.gseukdb2.org.uk, under "Tech info"

Regards,

Marcel.



On Fri, 12 Aug 2005 10:09:33 +0200, you wrote:

>Hi all estimeed lister > >An (indicative) table about what affect DBD size is in the paper: > > >DB2 UDB for z/OS & OS/390 - EDM Pool Management, Monitoring and Tuning - >by George Wise >>> (at page 12) > >HTH > >Max Scarpa >Renegade DB2 sysprog > >--------------------------------------------------------------------------------- >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". [...] 26325 146 43_Focused DB2 Education at IDUG 2005 - Europe32_=?iso-8859-1?q?Marcel=20L=E9vy?=24_marcel_idug_db2@YAHOO.FR31_Fri, 12 Aug 2005 17:38:52 +0200692_iso-8859-1 Dear DB2 user,

My name is Marcel Lévy and I'm a member of the IDUG 2005 Europe Conference Planning Committee.

********************************************************************* Mark your calendars and make plans to attend... IDUG 2005 Europe Conference 24-28 October 2005 Estrel Hotel & Convention Center Berlin, Germany

Click here for details: http://conferences.idug.org/europe/2005/index.cfm *********************************************************************

Maximise Your IDUG 2005 - Europe Experience with Educational Seminars ------------------------------------------------------------------------------------------------------------- [...] 26472 47 30_DB2 v8.2 AIX - hostname change14_Martin Flavell18_mflavell@I-TCS.COM31_Fri, 12 Aug 2005 15:42:09 +0100 26520 38 39_Re: Migration from Mainframe to DB2 LUW13_David S Waugh16_dsw-dba@JUNO.COM29_Fri, 12 Aug 2005 18:06:43 GMT433_- Barb:

In addition to what others have said, you should be prepared to learn some new script languages, because you're going to be writing a LOT of scripts. The utilities provided with DB2 UDB are OK, but you're going to want to customize how they work in order to have them do things just the way you want them to (like to emulate, as much as possible, how your utilities & maintenance jobs now work on the mainframe). [...] 26559 229 34_Re: DB2 v8.2 AIX - hostname change12_Martin, Paul22_Paul.Martin@ECOLAB.COM31_Fri, 12 Aug 2005 13:24:28 -0500323_us-ascii Once you change your hostname do the following -- issue hostname, just to make sure it was changed -- then check your db2nodes.cfg file in your SQLLIB library -- it will have to be changed to your new hostname -- then do the db2start and it should start.

[instprd3@unixprod] /instprd3/sqllib> hostname [...] 26789 111 31_com.ibm.db2.jcc.b.SqlException:10_Irwin, Jim19_Jim.Irwin@OA.MO.GOV31_Fri, 12 Aug 2005 13:39:58 -0500705_us-ascii Does anyone know where I can find a list of the values and their meanings that follow the com.ibm.db2.jcc.b.SqlException: java error?





**************************** CONFIDENTIALITY STATEMENT: This e-mail and any attachments are intended only for those to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure and unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in [...] 26901 262 71_Re: [z/OS v7] Visual Explain dynamic explain gets -567 & sqlstate 4250314_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM31_Fri, 12 Aug 2005 13:55:27 -0500405_US-ASCII Phil, I do not have the user exit for RACF setup as yet, so no RACF in DB2. Is there another way to do this without RACF - SYSUSERNAMES maybe ? thanks, Mark.







"Grainger, Phil" Sent by: DB2 Data Base Discussion List 08/12/2005 09:39 AM Please respond to DB2 Database Discussion list at IDUG [...] 27164 34 11_SP DSNUTILS14_Toppins, Smike21_smike.toppins@GWL.COM31_Fri, 12 Aug 2005 13:21:40 -0600703_us-ascii Is it possible to call the SYSPROC.DSNUTILS stored procedure from within a trigger?

SMike Toppins Great-West Life 8525 E. Orchard Road Englewood, CO 80111 smike.toppins@gwl.com





----Statement of Confidentiality----

This e-mail, and any attachments thereto, is intended for the exclusive use of the addressee(s) named herein and may contain information that is privileged or confidential or otherwise legally exempt from disclosure. If you are not a named addressee or an employee or agent responsible for delivering this message to a named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If [...] 27199 190 0_33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Fri, 12 Aug 2005 21:36:43 +0200519_iso-8859-1 RRSAF (DSNRLI) will not use DSNTRACE only CAF (DSNALI) will do.

It seems you try to access a stoped SP. You may issue -START PROC(*.*) and the retry the request. Post the results from this first request (SYSLOG).



Roland



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of PPantazi@LAIKI.COM Sent: Friday, August 12, 2005 11:08 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Stored Procedure DSNWSPM does not run [...] 27390 22 37_SPUFI defaults panel - SQL TERMINATOR15_Pradeep Gunjala31_pradeep_gunjala@TAX.STATE.NY.US31_Fri, 12 Aug 2005 15:14:29 -0500343_- Hello DB2 Experts,

This might be a dumb question, please bear with me. Our shop is DB2 7.1 on Z/OS. When I look at the SPUFI defaults panel, I don't find the option 1 which is SQL TERMINATOR. Is this disabled by our system admin. As you know in some cases we need to change this parameter, like while creating the trigger etc... [...] 27413 65 41_Re: SPUFI defaults panel - SQL TERMINATOR14_Toppins, Smike21_smike.toppins@GWL.COM31_Fri, 12 Aug 2005 14:22:08 -0600690_us-ascii You should be able to issue a "--SET TERMINATOR #" (no quotes, starting in column one) of your SPUFI input to change it.

SMike Toppins Great-West Life 8525 E. Orchard Road Englewood, CO 80111 smike.toppins@gwl.com





----Statement of Confidentiality----

This e-mail, and any attachments thereto, is intended for the exclusive use of the addressee(s) named herein and may contain information that is privileged or confidential or otherwise legally exempt from disclosure. If you are not a named addressee or an employee or agent responsible for delivering this message to a named addressee, you are not authorized to read, print, retain, copy [...] 27479 236 41_Re: SPUFI defaults panel - SQL TERMINATOR15_Pradeep Gunjala31_Pradeep_Gunjala@TAX.STATE.NY.US31_Fri, 12 Aug 2005 16:41:17 -0400473_US-ASCII It doesn't work, I tried all the combinations.

SET TERMINATOR # --SET TERMINATOR # SET TERMIANTOR = #

More over I don't find this command in SQL Reference manual. Is this a valid command.

Thanks, Pradeep







"Toppins, Smike" Sent by: DB2 Data Base Discussion List 08/12/2005 04:22 PM Please respond to DB2 Database Discussion list at IDUG [...] 27716 80 41_Re: SPUFI defaults panel - SQL TERMINATOR10_Bruce, Mae19_Mae.Bruce@OA.MO.GOV31_Fri, 12 Aug 2005 15:43:35 -0500706_us-ascii This works for me.

--#SET TERMINATOR :

Mae



*********************************** CONFIDENTIALITY STATEMENT: This e-mail and any attachments are intended only for those to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure and unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in error, please return the material received to the sender and delete all copies [...] 27797 139 41_Re: SPUFI defaults panel - SQL TERMINATOR15_Pradeep Gunjala31_Pradeep_Gunjala@TAX.STATE.NY.US31_Fri, 12 Aug 2005 16:46:53 -0400846_US-ASCII This one worked for me. Thanks Bruce and Smike.







"Bruce, Mae" Sent by: DB2 Data Base Discussion List 08/12/2005 04:43 PM Please respond to DB2 Database Discussion list at IDUG



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

Subject Re: [DB2-L] SPUFI defaults panel - SQL TERMINATOR











This works for me.

--#SET TERMINATOR :

Mae *********************************** CONFIDENTIALITY STATEMENT: This e-mail and any attachments are intended only for those to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure and unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you [...] 27937 395 41_Re: SPUFI defaults panel - SQL TERMINATOR14_Toppins, Smike21_smike.toppins@GWL.COM31_Fri, 12 Aug 2005 14:48:34 -0600389_us-ascii I use it all the time. Here's a snippet from one of my members ...

--SET TERMINATOR # CREATE PROCEDURE GDBADB.SMTNAME ( IN P_PRV_NO DECIMAL(12,0) ... BEGIN DECLARE V_NAME_TYP CHAR(01); SET V_NAME_TYP = SUBSTR(P_PRV_ALPHA_NME,40,1); IF V_NAME_TYP = '1' THEN ... END IF; END # SMike Toppins Great-West Life 8525 E. Orchard Road Englewood, CO 80111 smike.toppins@gwl.com [...] 28333 150 41_Re: SPUFI defaults panel - SQL TERMINATOR9_Mike Bell21_mbell11a1@VERIZON.NET31_Fri, 12 Aug 2005 16:00:04 -0500400_windows-1250 from the spufi panel option 5 change defaults - change to yes next screen is options 1 SQL TERMINATOR .. ===> # (SQL Statement Terminator) hit enter to process the change remember to change it back

This is a SPUFI command not SQL so it won't be in SQL manual. For DSNTIAD there is a parm SQLTERM(#) that does the same thing. Don't know about DSNTEP2 or other SQL processors. [...] 28484 415 41_Re: SPUFI defaults panel - SQL TERMINATOR15_Pradeep Gunjala31_Pradeep_Gunjala@TAX.STATE.NY.US31_Fri, 12 Aug 2005 17:03:39 -0400306_US-ASCII Mike,

That was my original question. When I select YES for Option 5 (Change Defaults), it is not showing the SQL TERMINATOR option. May be this can be disabled at the time of installation.I don't know, so I sent out an email to our sysprog also. I will post here once I hear from him. [...] 28900 44 66_Re: DB2 for OS/390 and z/OS Stored Procedure Development with .NET14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 13 Aug 2005 22:38:58 -0700347_US-ASCII Stored procedures run on a database server. If you "really" want to use .NET, the server will have to be a Windows platform, which means it will have to do remote access to the DB2 z/OS server. Which somewhat destroys the benefits of stored procedures.

James Campbell

On 10 Aug 2005 at 14:56, PPantazi@LAIKI.COM wrote: [...] 28945 80 41_Re: SPUFI defaults panel - SQL TERMINATOR14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 13 Aug 2005 22:38:59 -0700578_US-ASCII Is it possible you've picked up an "old" version of SPUFI.

There's a # after -- --#SET TERMINATOR ?

It's documented in Appl Prog and SQL Guide.

James Campbell



On 12 Aug 2005 at 16:41, Pradeep Gunjala wrote:

> > > It doesn't work, I tried all the combinations. > > SET TERMINATOR # > --SET TERMINATOR # > SET TERMIANTOR = # > > More over I don't find this command in SQL Reference manual. Is this a valid command. > > Thanks, > Pradeep > > > > "Toppins, > Smike" > Sent by: DB2 Data Base Discussion [...] 29026 92 21_Re: Large Bufferpools14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 13 Aug 2005 22:38:58 -0700324_ISO-8859-1 You might _wish_ that you were making stuff up, but '...the Prevalent Hypothesis is (direct quote) œThat there is enough RAM to hold all business objects in your system. That's right. Users of Prevayler don't have to worry about there being enough RAM because... we assume there will always be enough RAM!' [...] 29119 89 36_Re: db2 cobol quotation mark problem14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 13 Aug 2005 22:38:58 -0700596_ISO-8859-1 What I find surprising is not the current behavoir (TURKEY''S CAPITAL IS ANKARA), but that it ever produced the prior behavoir (TURKEY'S CAPITAL IS ANKARA).

You seem to be confusing the way one codes a text string in an external format and the way it is held internally. Looking at it from COBOL's and DB2's point of view, they are only using double apostrophes because otherwise they do not know where the string ends. Internally, they know where the sting ends - so they can strip the leading, trailing and second apostrophes. So your example is actually wrong. What you [...] 29209 31 48_Upcoming IBM zSeries as Database Server Seminars13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Sat, 13 Aug 2005 13:30:56 -0500437_ISO-8859-1 Hi all,

I wanted to let you all know about a seminar series that IBM is running called "The Value of zSeries as a Database Server" in a bunch of cities starting in Kansas City this week and Dallas next week.. more in the following weeks.

A complete description of the seminar, the agenda, the cities participating, and registration is all available at http://www-306.ibm.com/software/os/zseries/db2zseries/ [...] 29241 19 49_Re: DB2 Version 8, DFSORT and other sort packages10_Max Scarpa16_mscarpa@CESVE.IT31_Sun, 14 Aug 2005 14:52:29 +0200770_US-ASCII Steve

I'm not saying that it's a show stopper, we are planning to migrate to V8 next year. I'm doing some consideration based on my recent and new experience with some DFSORT maintenance coming from for total different reason other than a DFSORT or DB2 maintenance.

Max Scarpa

--------------------------------------------------------------------------------- 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 29261 17 27_Re: EDM pool and large DBDs10_Max Scarpa16_mscarpa@CESVE.IT31_Sun, 14 Aug 2005 14:55:34 +0200594_US-ASCII Hi Marcel

Thank for the link. I didn't remember the web site where I found the paper :-(.

Max Scarpa

--------------------------------------------------------------------------------- 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 29279 41 54_Re: UDB LUW 8.2.2 - Batch stored procedure deployment?11_Grant Allen28_Grant.Allen@TOWERSOFT.COM.AU31_Mon, 15 Aug 2005 09:34:09 +1000425_iso-8859-1 > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On > Behalf Of Hodgin, Scott > Hi list, > > Udb luw 8.2.2. > > I know you can deploy multiple stored procedures from one udb > server to > another using development center. > > My question is, is there a way to use batch commands of some > kind to mass > deploy stored procedures from one server to another without using > development center? [...]