1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l May 2004, week 4
2 249 13_Re: IBM REORG13_Michael Ebert18_mebert@AMADEUS.NET31_Sat, 22 May 2004 09:36:19 +0200591_iso-8859-1 Hi Angela,
the elapsed times seem to be ok (they depend on the machine power and -load so they're not too comparable). With the parameters you specify, there is normally no additional overhead for the SORT as it's done in parallel with the Unload. There are some possibilities for improvement that I see: 1.The DISCARD clause could be moved to a preceding SQL DELETE. The REORG has to inspect all 28M rows to see whether the condition is fulfilled, but most likely only a small number of rows actually qualifies. An SQL DELETE could run outside the batch window. This [...]
252 54 31_Re: Excessive DB2 Services Time12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 22 May 2004 11:37:17 +0200531_- Hi Bill, Sorry for the delay. If it's unique index then the garbage collection is minimal (Diag. guide), it's more noticeable in duplicate indexes. What's the batch job priority in WLM? Can you make it higher? As for GTT - did you use Declared or Created ?
Isaac Yassin
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of BILL.GALLAGHER@PHOENIXWM.COM Sent: Thursday, May 20, 2004 5:05 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Excessive DB2 Services Time [...]
307 44 19_Error code 00C900AE15_Daniel Cremieux17_c300501@YAHOO.COM31_Sat, 22 May 2004 05:14:15 -0500550_- Hi all , I get this message
11.32.04 STC04931 DSNT501I DB06 DSNIDBET RESOURCE UNAVAILABLE 209 209 CORRELATION- ID=AKY0010 209 CONNECTION- ID=DB2CALL 209 LUW- ID=* 209 REASON 00C900AE 209 TYPE 00000201 209 NAME DSQDBCTL.OBJECTRD
C900AE means that the index which name is : DSQDBCTL.OBJECTRD is in RBPD. However , i could not find this index. I've displayed all the index in database DSQDBCTL but there is not OBJECTRD. I've also Rebuild all the indexes of tablespace in DSQDBCTL , but when i type : DIS DP(DSQDBCTL) SP(*) [...]
352 73 10_Re: DB2 V66_ghodge26_ghodge@HLSTECHNOLOGIES.COM31_Sat, 22 May 2004 09:58:16 -0500326_us-ascii Angela:
We are running V6, V7 and V8 on a Z/OS 1.4 system with V7 Early Code. We have done nothing special to V6 or Z/OS to allow it to run. We have done a detailed comparison between thirteen access path choices made at each level of DB2. It is available at www.hlstechnologies.com at the download page. [...]
426 88 23_Re: Error code 00C900AE13_Horacio Villa17_hvilla@AR.IBM.COM31_Sat, 22 May 2004 12:12:29 -0300460_US-ASCII Daniel,
it's an index for table Q.OBJECT_DIRECTORY. OBJECTRD is not the index name (which is OBJECT_DIRECTORYX), it's the indexspace name.
Horacio Villa
Daniel Cremieux cc: Sent by: DB2 Data Subject: Error code 00C900AE Base Discussion List
05/22/04 07:14 AM Please respond to DB2 Database Discussion list at IDUG [...]
515 134 23_Extended Storage on AIX4_Smit24_robert.erwin.smit@WXS.NL31_Sat, 22 May 2004 17:33:11 +0200637_iso-8859-1 Dear Readers,
I trying to use extended storage on an AIX box and I'am getting the error message: SQL10003C There are not enough system resources to process the request. The request cannot be processed. SQLSTATE=57011
Everything works OK until we use an UDF.
Perhaps that someone has can explain somethings about extended storage? - V7.2 documentation tells me that if I set DB2_MMAP_READ and DB2_MMAP_WRITE to OFF then this will frees up extra 256 Mb segments V8 documentation tells me that the above will free up only 1 segment. So...that 2*256Mb or 256Mb can be used for bufferpools....what is it? [...]
650 32 71_Re: Does DB2 have a native EXECUTE function to call stored procedure s?11_Mike Kalena16_mkalena@BEAR.COM31_Sat, 22 May 2004 12:10:55 -0500395_- Tina,
You can also call SPs directly from DB2 Connect's Command Line Processor/Window. Code the call with values for the input and inout parms, and question marks for the output parm(s).
For example, we have a simple SP named SYSPROC.SPSRVR01 that returns one output parm which is the number of databases (it's just used to demo SPs and do testing). To call it we just use: [...]
683 100 38_SIRDUG presents Jorge Quintana, June 911_Kathy Lisle19_Kathy.Lisle@ATT.NET31_Sun, 23 May 2004 20:35:06 -0400393_us-ascii SIRDUG Meeting June 9th, 2004 Charlotte, NC
Featuring: Jorge Quintana - IBM
Join us on June 9, as Jorge Quintana from IBM offers presentations on both DB2 Connect and Visual Explain. These are tools are available on both the DB2 for z/OS and DB2 for LUW. Visual Explain is a FREE tool that has great deal of functionality that is rarely used to its full potential. [...]
784 14 23_Re: Error code 00C900AE15_Daniel Cremieux17_c300501@YAHOO.COM31_Mon, 24 May 2004 02:02:01 -0500678_- Thank you,
Do you know how indexspace's name is determined . I suppose that if the index name is < 8 , index name = indexspace name and if index name > 8 then DB2 decides about the indexspace name ?
--------------------------------------------------------------------------------- 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
799 72 20_SQL performance ....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Mon, 24 May 2004 01:56:35 -0700346_us-ascii We are DB2V7 for OS/390. This is regarding a badly performing program. The run times provided have been recorded through Omegamon.
TBL1 is a 18M row table with just one index: Unique index on KY_RP_NO, KY_PTS and ST_KY_TOT (with FIRSTKEYCARD of 1 Million, Clustered 94%).
The program first executes the following SQL: [...]
872 118 24_AW: SQL performance ....12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 24 May 2004 11:23:16 +0200719_iso-8859-1 Raquel
a descending index on column DT_GDR_TO may help to speed up the SELECT MAX.
HTH.
With kind regards - mit freundlichen Gruessen, Georg H. Peter c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- In nature, nothing is ever right. Therefore, if everything is going right ... something is wrong. [...]
991 52 37_Re: CA's Top Secret Interface For DB216_Proctor, William25_William.Proctor@TGSLC.ORG31_Mon, 24 May 2004 08:11:31 -0500422_us-ascii We have been using it for a number of years. We work with the security group when we need something changed or setup. I would be willing to answer any questions that I can. We are still on OS/390 2.10 DB2 Version 7.1. We have very few problems with our security. Occasionally we run into not knowing the right commands to grant something but CA has always been good about getting us the answer pretty quick. [...]
1044 133 47_Philadelphia RUG welcomes Roger Miller June 4th10_Anne Stout23_anne.stout@DOWJONES.COM31_Mon, 24 May 2004 08:15:52 -0500573_ISO-8859-1 Download your registration forms from WWW.DVDUG.ORG . Don't miss Roger Miller and Dwaine Snow talk about DB2 Version 8.
DELAWARE VALLEY DB2 USERS GROUP
___________________________________________________________________________ __ MEETING DATE: Friday, June 4th, 2004 LOCATION: Dave & Buster’s STARTING TIME: 9:00 AM REGISTRATION: 8:30 AM - 9:00 AM COST: ADVANCE REGISTRATION: MEMBERS $30, NON-MEMBERS $40 WALK IN REGISTRATION: MEMBERS $50, NON-MEMBERS $60 ___________________________________________________________________________ __ AGENDA [...]
1178 103 24_Re: SQL performance ....14_Seth Bienstock17_seth2@SKARVEN.NET31_Mon, 24 May 2004 09:33:49 -0400478_us-ascii Raquel,
The only possible explanation I can think of for the difference in times is that the index and data pages might already be in the buffer when running the second SQL statement.
I don't see any need for DB2 to do any SORTing on that first SQL statement. He/she is simply looking for the MAX value and it would therefore make sense to only retain the (current) high value as it peruses the rest of the data, until he/she found something HIGHER. [...]
1282 266 13_Re: IBM REORG14_Seth Bienstock17_seth2@SKARVEN.NET31_Mon, 24 May 2004 09:52:10 -0400590_iso-8859-1 Angela,
In my experience, ELAPSED TIMEs are values to be wary of. They don't always tell the whole story. In this case, I would want to look at the CPU usage, Service Units, and memory usage collected from the executions of both REORG jobs.
When you said the "same tablespace" was REORGed with REORG-Plus was it TRULY the "same" tablespace? In other words, did you restore the data to precisely what it looked like BEFORE you ran that FIRST (IBM) REORG? This can easily be done using DSN1COPY or even image COPY. But if I were doing a side-by-side performance [...]
1549 225 24_Re: SQL performance ....11_David Nance16_DWNance@FHSC.COM31_Mon, 24 May 2004 09:52:16 -0400563_ISO-8859-1 Raquel, Not giving us much wiggle room on this one. I would guess, as Seth did, on the difference in execution time. The first query pulled all necessary rows into the buffer pool, so the second one doesn't. This was due to having to refer to the data pages of all those rows that qualify on the first 2 cols of index. Is the table partitioned or just segmented? I'm guessing its probably partitioned, but if not you may want to consider. Other suggestions would be to keep that max date elsewhere, though I'd suspect you'd see extended run times [...]
1775 125 42_V7 Catalog/Directory REORG JCL... and more13_Michael Ebert18_mebert@AMADEUS.NET31_Mon, 24 May 2004 16:36:45 +0200551_us-ascii Hello List,
on DB2-L-DOCUMENTS please find the new V7 version of the Cat/Dir REORG JCL and other helpful JCLs. There are two new files in this release... which I have to split in two message because that list has a 50k size limit, annoying me intensely. More annoyances: attachments are no longer possible, all JCLs are in-stream text so you have to cut them up yourself. Also it has "Cathy Peck" (who acts as a reviewer) in the From: field so you won't find it if you look for it a year from now and only remember that it's from [...]
1901 21 37_PeopleSoft 7.6 and UDB for OS/390 7.112_Adams, Scott20_SAdams@AMERISURE.COM31_Mon, 24 May 2004 10:41:40 -0400776_- Hello, We are currently running PeopleSoft GL and AP version 7.6 and are in the process of upgrading UDB for OS/390 to 7.1.
Does anyone know if this a supported platform for PeopleSoft?
Scott W Adams Database Administration Amerisure Companies 26777 Halsted Rd. Farmington Hills, Mi 48331 (248) 426-7887 SAdams@Amerisure.com
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences [...]
1923 24 26_Cylinder boundries & Shark0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Mon, 24 May 2004 10:01:50 -0500449_us-ascii Hello,
This is actually repeating a question I found answered in the archives on 12/13/2000, but I thought I'd repeat it, slightly rephrased.
Many of our PeopleSoft tablespaces are allocated with sizes like 46 tracks and 61 tracks. For everything else, I've been taking great care to allocate things in 15 track (cylinder) increments. Is this no longer necessary? (We are OS/390 2.10, DB2 V7.1, running with Shark dasd.) [...]
1948 94 41_Re: PeopleSoft 7.6 and UDB for OS/390 7.10_26_Jeffrey_Frazier@WENDYS.COM31_Mon, 24 May 2004 11:03:13 -0400551_us-ascii Scott, we are z/os db2 7.1. we were running peoplesoft 7.5 and recently upgraded to peoplsoft 8.4.
"Adams, Scott" Sent by: DB2 Data Base Discussion List 05/24/2004 10:41 AM Please respond to DB2 Database Discussion list at IDUG
To: DB2-L@WWW.IDUGDB2-L.ORG cc: Subject: PeopleSoft 7.6 and UDB for OS/390 7.1
Hello, We are currently running PeopleSoft GL and AP version 7.6 and are in the process of upgrading UDB for OS/390 to 7.1. [...]
2043 28 19_numlkts and numlkus0_26_Jeffrey_Frazier@WENDYS.COM31_Mon, 24 May 2004 11:09:31 -0400320_us-ascii Hello, DB2 V7 , z/OS V1.4. numlkts=10000, numlkus=100000. recently some of our peoplesoft batch jobs have abended due to exceeding numlkus after the upgrade. has anyone else hit this and how did you resolve this? TIA, Jeff
---------------------------------------------------------------------------------
2072 80 29_Re: DB2 tools need to run SAP14_Paul P Packham16_ppackham@CSC.COM31_Mon, 24 May 2004 16:14:28 +0100322_US-ASCII Grace,
We are a big SAP shop running on z/OS with a DB2 backend. We used to run DB2PM to monitor SAP but when we went to v7 over a year ago, we decided to do without a 'mainframe' monitor and use SAP's CCMS instead. We have not hit any problems since we made this change, infact I prefer to use CCMS. [...]
2153 74 41_Re: PeopleSoft 7.6 and UDB for OS/390 7.10_16_FRUSA@BCBSIL.COM31_Mon, 24 May 2004 11:09:11 -0500488_us-ascii I think it might work - but PeopleSoft certification really should be checked if you want any help from them down the line. We went to 8.8 and tools 8.43 and HAD to be on version 7 of DB2 zOs
"Adams, Scott" cc: Sent by: "DB2 Subject: PeopleSoft 7.6 and UDB for OS/390 7.1 Data Base Discussion List" 05/24/2004 09:41 AM Please respond to "DB2 Database Discussion list at IDUG" [...]
2228 117 41_Re: PeopleSoft 7.6 and UDB for OS/390 7.115_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Mon, 24 May 2004 12:43:52 -0400524_us-ascii We're running Peoplesoft 8.14-or-so on UDB DB2 OS/390 V7.1. No platform issues of which I'm aware. Can't necessarily speak to Peoplesoft v7 on DB2 v7, since we upgraded Peoplesoft first. But it should be a backward-compatibility thing, running more "primitive" SQL on the v7 engine should be no problem. Remember that Peoplesoft's interface to DB2 is almost 100% SQL. (I only say "almost" because I haven't seen their architectural manual; in all my contact with Peoplesoft, they use only SQL to talk to DB2.) [...]
2346 113 30_Re: Cylinder boundries & Shark15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Mon, 24 May 2004 12:45:19 -0400477_us-ascii Considering how cheap a single cylinder is these days, are you sure you care whether you're wasting one cylinder or less per tablespace?
--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com
Steve_Grimes@AISMAIL.WUSTL.EDU Sent by: DB2 Data Base Discussion List 05/24/2004 11:01 AM Please respond to DB2 Database Discussion list at IDUG [...]
2460 33 22_Emeengency help needed3_lan18_lxw176@HOTMAIL.COM31_Mon, 24 May 2004 11:50:11 -0500544_- Hi,
To rephrase my problem here:
I created a database with all empty tables in it first, then I tried to load a table called test1 with exported data. This table has a primary key with dependent foreign key tables and a foreign key that was referenced by a primary key in another table ( no data yet). I guess this is the mistake I made that I shouldn't load this test1 table first, which caused an error "SQL0668N Operation not allowed for reason code "" on table ""." . I found the loaded table is in check pending state [...]
2494 25 30_Re: Cylinder boundries & Shark0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Mon, 24 May 2004 12:16:42 -0500783_us-ascii Hello,
Regarding:
--Considering how cheap a single cylinder is these days, are you sure you care whether --you're wasting one cylinder or less per tablespace?
I think I don't, (although multiplied by several hundred tablespaces it does add up a little.)
Would that leave performance as the only potential issue?
Stg
--------------------------------------------------------------------------------- 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 [...]
2520 69 26_Re: Emeengency help needed0_17_jrvazquez@ATCA.ES31_Mon, 24 May 2004 19:34:37 +0200540_us-ascii In a z/OS environment I'll run a CHECK DATA against the Check Pending tablespace. You can also try to run a LOAD REPLACE with a empty file.
Regards.
lan @IDUGDB2-L.ORG> con fecha 24/05/2004 18:50:11
Por favor, responda a DB2 Database Discussion list at IDUG
Enviado por: DB2 Data Base Discussion List
Destinatarios: DB2-L@WWW.IDUGDB2-L.ORG CC: Asunto: Emeengency help needed [...]
2590 183 81_New England DB2 Users Group Meeting, June 17, Sturbridge, MA (Picnic by the Lake)0_27_mamccormack@STATESTREET.COM31_Mon, 24 May 2004 13:35:35 -0400597_iso-8859-1 Please join your fellow DB2 advocates at the next New England DB2 Users Group Meeting on Thursday, June 17, from 8AM (registration, meeting starts at 9) to 3PM, at the Sturbridge Host Hotel, Route 20 in Sturbridge, MA. We have an interesting mix of DB2 information that includes further info on DB2 for zOS Version 8, a presentation on how DB2 works with Websphere and Information Integrator, and the long-awaited "Top 10 Things We Learned at IDUG". This year's IDUG recap will be a point/counterpoint top 10 from the mainframe side and the distributed side (really a "top 20" for [...]
2774 117 30_Re: Cylinder boundries & Shark15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Mon, 24 May 2004 13:55:10 -0400411_us-ascii I think it would, and I don't think that's a real issue either -- the only place where it would/should matter would be in formatting/allocating additional space. We haven't been _looking_ for potential waste in that area, but we haven't _noticed_ any, either, so it's not prohibitive.
--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com [...]
2892 254 13_Re: IBM REORG18_Angela M Arettines28_aarettines@KEYSPANENERGY.COM31_Mon, 24 May 2004 14:04:02 -0400541_iso-8859-1 Seth,
I'll provide the information that our DBA gave me. This is also in response to a couple of questions from Steve and Michael, who also responded:
- The IBM and BMC REORG jobs were run on the same systems, during the same time of day, with the same workload taking place during each job. We're interested in the elapsed time because we have a very tight batch window. We want to be able to get the same IBM job to perform as well as the BMC job without having to make changes to the IBM job. With regard to [...]
3147 29 31_Declared Temp Table performance11_Mike Jessen22_jessenmike@HOTMAIL.COM31_Mon, 24 May 2004 13:06:58 -0500591_- We have a product called TableBase. I don't know much about it, but some developers are considering moving some temp VSAM processing to these TableBase structures. Initial testing has shown that there are significant performance improvements over VSAM access.
Does anyone know how declared temp tables would compare to Tablebase? Does anyone have any information on the use of declared temp tables and performance? This table would be inserted, updated, deleted, and read both randomly and sequentially. Do declared temp tables perform the same as non-temp db2 tables/indexes? [...]
3177 246 26_Re: Emeengency help needed17_Duane Lee - EGOVX22_DLee@MAIL.MARICOPA.GOV31_Mon, 24 May 2004 11:24:26 -0700424_iso-8859-1 Or do a repair NOCHECKPEND. Check the utility manual for more info.
-----Original Message----- From: jrvazquez@ATCA.ES [mailto:jrvazquez@ATCA.ES] Sent: Monday, May 24, 2004 10:35 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Emeengency help needed
In a z/OS environment I'll run a CHECK DATA against the Check Pending tablespace. You can also try to run a LOAD REPLACE with a empty file. [...]
3424 147 27_shadow Image & DB2 recovery16_Proctor, William25_William.Proctor@TGSLC.ORG31_Mon, 24 May 2004 13:46:43 -0500328_us-ascii Thanks for all replies in advance.
Is anyone out there in DB2 land running with shadow image. If so what is your procedures for doing disaster recovery? Can you recover a single tablespace and keep the catalogs, logs and tablespace in sync? What other questions should I be asking? Any help is appreciated. [...]
3572 149 35_Re: Declared Temp Table performance15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Mon, 24 May 2004 14:27:45 -0400619_us-ascii Mike,
You've asked a mouthful.
1) Tablebase gets a lot of its speed advantage by having its physical data resident in memory. I'm not familiar with Tablebase's offered functionality, but you can get a big *speed* improvement in DB2 if you allocate few enough objects to a large enough bufferpool that all the pages of the objects are present in memory, which is DB2's analog for that process. You're still running through more code in DB2 than you are in Tablebase (when you retrieve data), so for well-defined functions, you probably get some microseconds savings in Tablebase. You have to [...]
3722 65 24_Workfiles in dataspaces?14_Heckman, Carol23_carol_heckman@MEDCO.COM31_Mon, 24 May 2004 14:38:06 -0400351_- All, Because we were constrained on virtual storage in DBM1, we have moved all of our local bufferpools to dataspaces except BP0 and BP3 (work database). We are hesitant to move BP3 to a dataspace because we do not want to do anything that might slow down the DB2 sorts even a little. Does anyone have any experiences to relate regarding this? [...]
3788 131 28_Re: Workfiles in dataspaces?14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Mon, 24 May 2004 15:03:01 -0400432_us-ascii I have provided several referals to the following paper in the past
"DB2 UDB for Z/OS and OS/390 Performance on the IBM zSeries 900 Server"
It answers your question.
One of the recomendations in the paper is "Rember that large buffer pools become most effective when data is re-referenced. Applications, such as certain utilites that tend to read pages only once may not benefit from large pools" [...]
3920 79 28_Re: Workfiles in dataspaces?13_Martin Packer24_martin_packer@UK.IBM.COM31_Mon, 24 May 2004 20:06:50 +0100379_US-ASCII I would say "go for it" as the restrictions on hiperpools that limited their applicability for sort work don't apply to dataspace. And I have known customers put their work file tablespaces in dataspace pools.
Regards, Martin
Martin Packer, MBCS Martin Packer/UK/IBM 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile 07802-245584) [...]
4000 329 13_Re: IBM REORG14_Seth Bienstock17_seth2@SKARVEN.NET31_Mon, 24 May 2004 15:08:15 -0400591_us-ascii Angela,
I appreciate your responding so quickly with the details. With the information that you've given there are MANY areas that you might want to explore. Some more obvious than others.
From my own experiences I am sorry to tell you that I would not give too much weight to the ELAPSED times. I've been in too many situations where a single change in the workload (or OS configuration) could have too significant of an impact (i.e. 5 seconds elapsed vs 5 minutes elapsed). There are just too many things in the environment that could alter that value and make [...]
4330 214 35_Re: Declared Temp Table performance11_David Nance16_DWNance@FHSC.COM31_Mon, 24 May 2004 15:09:54 -0400309_US-ASCII One other item is that performance is not the same, by a long shot. As one of the listers pointed out last week, when using a GTT, his cpu and elapsed cost went up by about 5x compared to a predefined table in an existing tablespace.
Dave Nance First Health Services, Corp. (804)527-6841 [...]
4545 248 28_Re: Workfiles in dataspaces?11_Tom Moulder23_tmoulder@HOUSTON.RR.COM31_Mon, 24 May 2004 14:24:09 -0500320_us-ascii Carol
Just a suggestion, please benchmark in your own environment to make sure your get desirable results.
The size of buffer pools for work data sets is typically not set to a large value because of sequential access to the data. That would hold true for every buffer pool you have defined. [...]
4794 101 31_Re: shadow Image & DB2 recovery14_David R Slocum22_drslocum@CMSENERGY.COM31_Mon, 24 May 2004 15:48:56 -0400334_US-ASCII Bill said: "Thanks for all replies in advance. Is anyone out there in DB2 land running with shadow image. If so what is your procedures for doing disaster recovery? Can you recover a single tablespace and keep the catalogs, logs and tablespace in sync? What other questions should I be asking? Any help is appreciated." [...]
4896 56 23_Re: Error code 00C900AE13_Horacio Villa17_hvilla@AR.IBM.COM31_Mon, 24 May 2004 16:51:19 -0300445_US-ASCII Daniel,
yes, if the name's length > 8, DB2 picks up a name for it (indexes & tablespaces). I don't know how it does it.
Horacio Villa
Daniel Cremieux cc: Sent by: DB2 Data Subject: Re: Error code 00C900AE Base Discussion List
05/24/04 04:02 AM Please respond to DB2 Database Discussion list at IDUG [...]
4953 48 23_Re: Error code 00C900AE14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Mon, 24 May 2004 16:32:50 -0500553_us-ascii Daniel, When DB2 builds the indexspace name, the first thing it will do is to replace all underscore '_' characters with 'R'. After that, it will attempt to use the first 8 characters for the space name. If there is a collision, it will then use the first 4 characters of the index, with the last four characters coming from an internal translate table. Wayne Driscoll Sr. Product Architect Quest Software http://www.quest.com/db2 wdriscoll@listserv.quest.com NOTE: All opinions are strictly my own. EMail Address in sig must be modified. [...]
5002 77 30_Re: Cylinder boundries & Shark15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Tue, 25 May 2004 09:27:02 +1000529_us-ascii When you are using highly partitioned tablespaces and are working in test, where there may be no data in a table and you have multiple copies of the table, then it can really add up.
If you have, for example, a 254 partition tablespace and 1 partitioning index, then the minimum footprint at 1 cylinder PQTY for both is 508 * 0.056 * 15 or a bit more than 400 Meg (even if the tablespace is empty). Using instead 1 track it is a bit under 29 meg. When you have half a dozen tables partitioned this way and a [...]
5080 105 24_Re: SQL performance ....13_Neil Courtney37_Neil.Courtney@TEAM.TELSTRACLEAR.CO.NZ31_Tue, 25 May 2004 11:39:26 +1200484_- Hi Raquel, my take on this would be that the first query needs to read all of the rows for the particular KY_RP_NO, KY_PTS values. You don't tell us how many records this might be, as ST_KY_TOT is not part of the SQL. There could be 1000s of records involved, and it will need to read all of them to get the MAX time. It will not need to do a sort to get the records. A cluster ratio of 94% might indicate that it is doing a lot of I/O to get these records. Try a reorg of the [...]
5186 257 38_Re: Optimization hints for dynamic SQL12_Warren Homer17_whomer@CSC.COM.AU31_Tue, 25 May 2004 09:48:36 +1000768_us-ascii All,
we managed to get opthints to work with DSNTEP2 by binding the DSNTEP2 plan with REOPT(VARS).
Regards, Warren Homer
----------------------------------------------------------------------------------------
Please respond to DB2 Database Discussion list at IDUG Sent by: DB2 Data Base Discussion List To: DB2-L@WWW.IDUGDB2-L.ORG cc: Subject: Re: Optimization hints for dynamic SQL
May be you could try using parameter markers "?" --- Warren Homer wrote: > Hi all, > > We are using opthints quite successfully in batch but we have had no luck > with dynamic SQL at all (via online, dsntep2 or QMF). We are using DB2 V6 > for [...]
5444 118 28_Re: Workfiles in dataspaces?35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Mon, 24 May 2004 19:51:24 -0400391_iso-8859-1 Many sites have used HPs behind their VPs for Sort with good payback. Naturally, it all depends on the size of the sort, the volume of pages written, and how many are in the HP when sort wants to read them. Don't see any problem using dataspaces, and every installation is different, so before/after performance measurements are important - as with any change. Regards, Joel [...]
5563 15 41_DB2 UDB for z/OS Universal driver install14_Matthews, John25_JMatthews@MEDNET.UCLA.EDU31_Mon, 24 May 2004 16:58:52 -0700725_iso-8859-1 We need to install this into our DB2 for OS/390 V7 subsystem. We received this feature as a separate FMID as opposed to a PTF. Should there be any issues installing a FMID into my DB2 V7 SMPE environment or would it be better to order as PTFs?
--------------------------------------------------------------------------------- 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
5579 57 41_Re: PeopleSoft 7.6 and UDB for OS/390 7.113_Leo Conchello22_lconchello@HOTMAIL.COM31_Mon, 24 May 2004 21:03:25 -0600316_- Scott,
Don't forget to check also the version of DB2 Connect and FixPack that you are planning to use. In general, there are very few compatibility issues but there were some problems in Version 6 and 7 especially with People Tools and Personal Connect. They can be avoided having the correct FixPack. [...]
5637 58 30_Re: Cylinder boundries & Shark12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 25 May 2004 08:05:03 +0200284_- Hi, Now add to it the cost of handling another disk box using PPRC and the price doubles. If you use raid-1 then double again. At the end, even though it's cheap to start with, you end up paying much more. Last time we put some work into it, we cleaned up 30 * 3390-3 volumes. [...]
5696 45 28_Re: Workfiles in dataspaces?0_16_mscarpa@CESVE.IT31_Tue, 25 May 2004 10:30:41 +0200555_US-ASCII Carol
Recently I did some measurements in our prod environment regarding DB2 RDS SORT performances using a simple combination of IFCID 96 and REXX. We found that our sort environment doesn't need big but more physical work files and our BP7 was big enough, even if relatively small. This is a characterization that, IMHO, should be done in every environments as I found some 'unexpected' results (many sorts with 1 or 0 rows, very few logical files and merge passes which probably derives from a sufficient reorg policy as well,etc). [...]
5742 102 28_Re: Workfiles in dataspaces?13_Martin Packer24_martin_packer@UK.IBM.COM31_Tue, 25 May 2004 09:37:58 +0100528_US-ASCII Thanks for the concurrence, Max.
A question: What is your SRTPOOL value? I always look at that as it gives some idea as to whether people are forcing RDS Ops to chuck data back and forth more times than are necessary - because of forced additional intermediate merges.
Trade-off of the day: Larger SRTPOOL *might* reduce the number of intermediate merges. But it also *might* cause more virtual storage usage. I would imagine IFCID 96 documented the former and I know IFCID 225 documents the latter. [...]
5845 275 13_Re: IBM REORG13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 25 May 2004 10:55:53 +0200617_us-ascii Seth,
I have to disagree with your comments in several ways. First of all, elapsed time is a perfectly valid metric, especially if your objective is to optimise utilisation of a wall-clock time period (batch window). And unless the system load is quite high (let's say >90% CPU), then elapsed time for utilities is quite well reproducible in my experience. If one product performs more than twice as well as the other in repeated tests, there is little question that it is indeed faster (under the given conditions). Furthermore, there is no point in specifying "comparable" parameters to set up [...]
6121 33 28_Re: Workfiles in dataspaces?0_16_mscarpa@CESVE.IT31_Tue, 25 May 2004 11:26:42 +0200426_US-ASCII Martin
Absolutely correct (of course). DB2 RDS SORT is a 3-levels process (SORT POOL, BP,disks) and you 'overflow' in less performing levels if your RDS is not big enough to contain all 'run(s)' created by first phase of sort (but the number of runs depends even of how data come in SORT pool). But it could be not so easy to achieve and as you said you *could* increase memory usage with few advantages. [...]
6155 78 26_Re: Emeengency help needed18_Savin, Mark (EHUK)26_Mark.Savin@EULERHERMES.COM31_Tue, 25 May 2004 05:42:05 -0400469_- Lan,
If you're using DB2 for LUW (which I think you are), you can use the set integrity command with the IMMEDIATE UNCHECKED option. This will take the table out of check pending. If does have implications for data integrity. See the SQL reference for details.
If you're going to be loading tables in an arbitrary order, eg for testing purposes, it might make more sense for you to remove your referential integrity then reapply it as appropriate. [...]
6234 77 28_Re: Workfiles in dataspaces?13_Martin Packer24_martin_packer@UK.IBM.COM31_Tue, 25 May 2004 10:38:05 +0100393_US-ASCII Yes, that's what I meant.
And if you were looking at IFCID 225 you'd've seen the behaviour of RDS Ops Pool to not be "rising and falling as the sorts come and go". The pools' virtual memory hangs around until DB2 reclaims it.
Martin
Martin Packer, MBCS Martin Packer/UK/IBM 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile 07802-245584) [...]
6312 28 28_Re: Workfiles in dataspaces?0_16_mscarpa@CESVE.IT31_Tue, 25 May 2004 12:22:40 +0200551_US-ASCII So I understood it correctly and I completely agree. It was since many years I do it (and I recommend it, if the case).
Yes it's what I saw, RDS OP POOL STORAGE doesn't change during time/days (during 10 different days), by I think it's designed so because it's more performing (less work for the system) and so memory is always available for incoming work, until - as you said - DB2 really need that chunk(s) of memory, which wasn't our case when I did the measurements. Or at least it was so in our environment, which is not so [...]
6341 27 28_Re: Workfiles in dataspaces?0_16_mscarpa@CESVE.IT31_Tue, 25 May 2004 12:33:46 +0200390_US-ASCII Among other things I run at fixed times a IPCS job monitoring all address space memory usage and I see that DBM1 address
space has the most important increse/decrease for ESQA (as %) and for ECSA (few %). SQA and CSA are costant (I hope
so !) but I'm still not able to 'map' these changes using IFCID 225 (but I just started to work on it) ie to see where these [...]
6369 112 32_RFI: DB2 Extended Storage on AIX4_Smit24_robert.erwin.smit@WXS.NL31_Tue, 25 May 2004 13:23:36 +0200630_iso-8859-1 Hello Everybody,
I'am still looking for information about DB2 Extended Storage on AIX. DB2 Redbooks and google don't give answers to my questions.
Perhaps that someone or a group off people can explain something's about extended storage? - Extended Storage.....do you need control blocks for it...just like with bufferpools? - Where does db2 use memory for control..... in the DBHEAP...and how must memory and is this shared memory? - If I have 5 bufferpools and I want to use extended storage for 3 off them (same pagesize) how muts NUM_ESTORE_SEGS do I use and why? - The default for ESTORE_SEG_SZ [...]
6482 36 28_Re: CA -- Old Platinum tools12_billy larsen19_billarsen@YAHOO.COM31_Tue, 25 May 2004 04:28:42 -0700320_us-ascii
Have a lot of problems since we are P01E , a lot of them come from ISPF panels.
--------------------------------- Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger
---------------------------------------------------------------------------------
6519 78 28_Re: Workfiles in dataspaces?13_Martin Packer24_martin_packer@UK.IBM.COM31_Tue, 25 May 2004 12:30:58 +0100366_US-ASCII Actually, Max, that's a new wrinkle. But I think IFCID 225 etc give us plenty of other ways of noticing impending storage constraint. I just hope people have got the message about monitoring it. My ROT for when to monitor storage is....
if SUM(BPOOLS)>512MB OR TOT(HIGH_PRIVATE+LOW_PRIVATE)>1024MB
for a single subsystem then monitor it. [...]
6598 68 28_Re: Workfiles in dataspaces?13_Martin Packer24_martin_packer@UK.IBM.COM31_Tue, 25 May 2004 12:27:47 +0100483_US-ASCII Maybe that's a requirement on DB2 Lab. I happen to know the guys who own IFCID 225 and 217. Would you like to try and pursue that?
Cheers, Martin
Martin Packer, MBCS Martin Packer/UK/IBM 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile 07802-245584)
mscarpa@CESVE.IT Sent by: DB2 Data Base Discussion To List DB2-L@WWW.IDUGDB2-L.ORG Subject Re: Workfiles in dataspaces? 25-05-04 11:33 [...]
6667 18 23_Re: numlkts and numlkus12_Mehdi Fakhar26_mehdifakhar@WINN-DIXIE.COM31_Tue, 25 May 2004 08:08:50 -0500400_- On Mon, 24 May 2004 11:09:31 -0400, Jeffrey_Frazier@WENDYS.COM wrote:
>Hello, DB2 V7 , z/OS V1.4. numlkts=10000, numlkus=100000. recently some of >our peoplesoft batch jobs have abended due to exceeding numlkus after the >upgrade. has anyone else hit this and how did you resolve this? >TIA, Jeff
We use numlkts=25000 and numlkus=0 for both Peoplesoft HR and Financials. [...]
6686 42 28_Re: Workfiles in dataspaces?0_16_mscarpa@CESVE.IT31_Tue, 25 May 2004 15:21:13 +0200572_US-ASCII Martin
I agree, now with IFCID 225 we have more chances to detect storage issues. On condition that this IFCID is started ! And in combination with 'old' ways of measuring memory usage could be very useful in big environments.
Thank for your ROT, but (again) I agree with you, a 'ROT is a ROT' and for this reason couln't be applicable in some environments which differ from the environment(s) from which the ROT came. I saw many and many times (actually in another field, not in IT...) not applicable well-defined ROTs. It's a starting point. [...]
6729 70 23_Re: numlkts and numlkus0_16_FRUSA@BCBSIL.COM31_Tue, 25 May 2004 08:23:49 -0500482_us-ascii Did you check with PeopleSoft to see if they have additional fixes that need to be added after the upgrade ? Typically our upgrades require tuning in the DB2 and DB2 subsystem arena right away.
"Mehdi Fakhar" cc: Sent by: "DB2 Subject: Re: numlkts and numlkus Data Base Discussion List" 05/25/2004 08:08 AM Please respond to "DB2 Database Discussion list at IDUG" [...]
6800 130 28_Re: Workfiles in dataspaces?13_Martin Packer24_martin_packer@UK.IBM.COM31_Tue, 25 May 2004 14:38:45 +0100308_US-ASCII I agree - nice thread! And apologies to the original starter of it. I hope the digression's been worthwhile. :-)
As for grouping - that's not going to happen in either IFCID. But it's easy to do it - if you want to. Personally I don't group but rather sort on component size descending. [...]
6931 141 20_Identity column info4_buzw15_buzw@SWBELL.NET31_Tue, 25 May 2004 09:17:46 -0500775_us-ascii Details:
DB2 v7
z/OS
Question:
Where is the data about identity columns stored? I'd like to be able to see the START WITH, INCREMENT BY, CACHE, MAXVALUE & MINVALUE information.
Thanks.
Buz Williams
--------------------------------------------------------------------------------- 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
7073 205 24_Re: Identity column info24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Tue, 25 May 2004 07:20:07 -0700505_us-ascii SYSIBM.SYSSEQUENCES
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of buzw Sent: Tuesday, May 25, 2004 7:18 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Identity column info
Details:
DB2 v7
z/OS
Question:
Where is the data about identity columns stored? I'd like to be able to see the START WITH, INCREMENT BY, CACHE, MAXVALUE & MINVALUE information. [...]
7279 29 25_DB2 tools need to run SAP10_Feeny, Jim23_Jim_Feeny@COMPUWARE.COM31_Tue, 25 May 2004 10:34:33 -0400623_iso-8859-1 Hello Grace:
We at Compuware have a solution that specifically addresses the performance requirements and backup/recovery concerns of an SAP site. I would be happy to discuss with you off-line.
Jim Feeny Product Manager Compuware Corporation
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...]
7309 45 15_Asynch I/O wait0_19_mike.holmans@BT.COM31_Tue, 25 May 2004 15:52:51 +0100304_iso-8859-1 I've got a query which the users maintain used to execute in a few seconds but now takes 2-3 minutes.
It's using list prefetch to get the data, which resides in multiple partitions of a partitioned TS, and nearly all the elapsed time seems to be consumed by asynchronous I/O wait. [...]
7355 26 28_Re: Workfiles in dataspaces?0_16_mscarpa@CESVE.IT31_Tue, 25 May 2004 16:56:24 +0200458_US-ASCII Apologies, sure !
But I think we discussed about DB2 arguments, maybe useful, maybe not (assuming that someone READ this thread :-) )
Yes I saw the new macro but I wasn't able to work with it. Maybe next week I'll try to use it. I don't know why, but I suspect as you said that in the next future DDF and IRLM should have more 'details' in memory usage (ok there are IRLM commands to 'check' storage), maybe with improved traces. [...]
7382 155 19_Re: Asynch I/O wait15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Tue, 25 May 2004 11:00:50 -0400433_us-ascii I'm guessing that the CESS_DTE value of "Null" has high cardinality? I'm surprised that the query ever worked well.
Try REORGing the NPI if you haven't already. If you have the ability, try building an NPI
--Phil Sevetson Database Administration Wakefern Food Corporation CISD 230 Raritan Center Parkway, P5-105 Edison, NJ 08837 Phone: (732) 225-8086 Fax: (732) 225-8109 mailto:phil.sevetson@wakefern.com [...]
7538 203 19_Re: Asynch I/O wait15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Tue, 25 May 2004 11:13:34 -0400422_us-ascii Oops, my bad; I hit "send" by accident.
I was going to say: try building an NPI on KEYVAL and CESS_DATE and see if you get faster response that way.
--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com
----- Forwarded by Phil Sevetson/CISD/WAKEFERN on 05/25/2004 11:01 AM -----
Phil Sevetson 05/25/2004 11:00 AM [...]
7742 123 36_Binding SPUFI plan with location * ?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 25 May 2004 11:30:41 -0400503_- Hello all,
I'm curious how many installations have enabled SPUFI for distributed access.
I have seen a bind for the SPUFI Plan including PKLIST of (DSNESPCS.DSNESM68, RMTLOC1.DSNESPCS.DSNESM68,RMTLOC2.DSNESPCS.DSNESM68,RMTLOC3.DSNESPCS.DSNESM6 8, ...) ...
In our playground Subsystem I bound it with PKLIST(*.DSNESPCS.DSNESM68) ...
This enables easy addition of additional locations, especially non z/OS locations by merely binding the package on the remote location. [...]
7866 67 19_Re: Asynch I/O wait25_Thanikachalam Sundarrajan21_billysundar@YAHOO.COM31_Tue, 25 May 2004 08:42:17 -0700590_us-ascii Is your query currently exploting parallelism? (LP cannot use sysplex parallelism) Using piece size in NPI and the placement of the datasets could help as well. Is it possible that multiple partitions reside on the same volume?
Cheers. Billy. --- mike.holmans@BT.COM wrote: > I've got a query which the users maintain used to execute in a few seconds but now > takes 2-3 minutes. > > It's using list prefetch to get the data, which resides in multiple partitions of a > partitioned TS, and nearly all the elapsed time seems to be consumed by asynchronous > I/O wait. > [...]
7934 30 40_Re: Binding SPUFI plan with location * ?13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 25 May 2004 17:46:01 +0200324_us-ascii Hi Dave,
I have SPUFI, DSNTEP2, DSNTIAUL all enabled for distributed access. This makes for very easy administration from one central location. If I want to check something on another SSID, I don't have to log in to that LPAR but simply specify the location in the SPUFI panel (or a CONNECT statement). [...]
7965 86 36_RESERVED Data sets during STOP DB???0_18_PPantazi@LAIKI.COM31_Tue, 25 May 2004 18:55:11 +0300311_us-ascii Hi Listers,
I have a series of batch jobs that I use during the refresh of the QA subsystem using DSN1COPY.
As you know the tablespace must be stopped in order for DSN1COPY to work and for this reason I have included as the first step in all jobs the STOP DB command as shown below: [...]
8052 35 19_Re: Asynch I/O wait20_desiz db2 consultant20_desig@CONSULTANT.COM31_Tue, 25 May 2004 10:54:58 -0500
8088 90 14_SQL Union help11_Carl Nelson22_carl.nelson@ANTHEM.COM31_Tue, 25 May 2004 11:13:16 -0500378_- I need some help with some SQL. Unions & joins are my weakness. This is the report I'm trying to generate and below it is the SQL I've managed to develop so far. I can not figure out how to consolidate the lines so each database & tablespace occupy only 1 line. I also need the report to list any tablespaces that have no backups, hence the last union to systablespaces. [...]
8179 31 40_Re: RESERVED Data sets during STOP DB???13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 25 May 2004 18:15:31 +0200514_us-ascii This is a JES effect. If you submit a job that has a dataset with DISP=MOD or OLD, or a VSAM dataset with any disposition (I think those are the rules), then JES puts an exclusive enqueue on the dataset - the dataset is reserved from the start of the job, not from the job step where it is actually referred. If DB2 (ssidDBM1) currently has the dataset allocated (opened), then the job waits. You have to put steps that directly use a VSAM file via a DD card in a separate job from any DB2 function. [...]
8211 369 24_Re: Identity column info12_HEPP SHERY C17_schepp@SRPNET.COM31_Tue, 25 May 2004 09:15:32 -0700489_iso-8859-1 Here's a query that I use
SELECT A.START, A.MAXASSIGNEDVAL, B.DNAME, B.DCOLNAME FROM SYSIBM.SYSSEQUENCES A inner join SYSIBM.SYSSEQUENCESDEP B on A.CREATEDBY = B.DCREATOR AND A.SEQUENCEID = B.BSEQUENCEID WHERE A.CREATEDBY = ?
Regards, Shery
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of buzw Sent: Tuesday, May 25, 2004 7:18 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Identity column info [...]
8581 454 13_Re: IBM REORG18_Angela M Arettines28_aarettines@KEYSPANENERGY.COM31_Tue, 25 May 2004 12:15:12 -0400674_us-ascii Michael/Seth,
Yes, Michael, you're correct. I mistakenly omitted the DISCARD in my posting. The SYSIN was:
REORG TABLESPACE tablespace_name PART 1:20 LOG NO NOSYSREC SORTDATA SORTKEYS SORTDEVT SYSDA DISCARD FROM TABLE table_name WHEN (DTE_EXPIRED!='01/01/0001' AND (DTE_EXPIRED
And, in answer to Seth's questions, with additional information from our DBA:
1) The WHERE criteria is correct. In both cases, no rows were removed from the database. BMC uses the where clause, IBM uses when. 2) Run time was close whether using the PART parameter or not. 3) DTE_EXPIRED is not part of any index. 4) In this case [...]
9036 299 26_Storing Spanish Characters22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Tue, 25 May 2004 09:18:57 -0700555_us-ascii Hi Listers,
We have an app that is writing Spanish characters to one of our DB2 tables. These characters display on the screen fine and they are inserted into the database fine. When the user goes to look at the row on the table using SQL the values do not display. If we prefix the column with HEX(remarks_txt) the values display. See below. Is there a way to get the values to display on a straight select.. I am searching the archives for an answer as you read this, but thought this may be quicker. Any help would be appreciated. [...]
9336 23 13_Re: IBM REORG9_Jim Ruddy18_jaruddy@US.IBM.COM31_Tue, 25 May 2004 11:28:45 -0500432_- Unless your data is compressed, you have an EDITPROC, or are running your machine close to 100% capacity, removal of the DISCARD should not result in an appreciable difference in elapsed time - it will only improve CPU time. REORG is I/O bound (spends much of it's elapsed time waiting for workfile I/O such as with sort). The predicate you are using is very simple to evaluate and should only add a tiny amount of CPU time. [...]
9360 405 30_Re: Storing Spanish Characters0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Tue, 25 May 2004 09:43:43 -0700452_- Marty, everything has to be on the same CCSID(or country), table, DSNTEP2, SPUFI, 3270 emulator, command center etc, etc, etc.
I had/have a similar problem it that a table was created with CCSID 500 (Europe) and everything else as US. cliff:-)
-----Original Message----- From: Killen, Martin W - CNF [mailto:Killen.Martin@CNF.COM] Sent: Tuesday, May 25, 2004 9:19 AM To: DB2-L@www.idugdb2-l.org Subject: Storing Spanish Characters [...]
9766 101 19_Re: Asynch I/O wait35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 25 May 2004 12:56:28 -0400566_iso-8859-1 Mike, If the application is waiting for Asynch I/O, and you have already tried isolating the object to be sure the pages aren't being impacted by other objects, you have an I/O performance problem. The LP should normally complete fast enough so the application does not wait (much, if at all). You're getting cache misses at the controller level, and the devices are supposed to be smart enough to prestage the data into the cache - but that may depend upon how far apart the pages really are, and how many are chained to a single LP read. You could [...]
9868 82 40_Re: RESERVED Data sets during STOP DB???0_18_PPantazi@LAIKI.COM31_Tue, 25 May 2004 20:13:50 +0300563_us-ascii Michael, Thank you for the prompt response. I too believe that the reason you mentioned is the correct but I did not understand why the STOP worked when it was issued from the DBADM panels.
I did not understand the action that you propose for the batch handling.
I know that this works for normal VSAM operations but for DB2 I am not sure how to implement it since this involves DB2 commands and functions that do not directly reference the physical VSAM cluster, but rather the definitions in the catalog and DB2 takes care the rest. [...]
9951 114 40_Re: RESERVED Data sets during STOP DB???14_Seth Bienstock17_seth2@SKARVEN.NET31_Tue, 25 May 2004 13:30:12 -0400392_us-ascii Pantazis,
Are you on JES3 or JES2?
I was under the impression that this was a JES parameter that the shop can change as they see fit. I believe might only be an issue in JES3 only and I recall that it could be defeated if desired, at the JES level (by the Systems Programmer). I believe it's called something like MAIN DEVICE SCHEDULING or MAIN DEVICE ALLOCATION. [...]
10066 46 40_Re: RESERVED Data sets during STOP DB???13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 25 May 2004 19:29:53 +0200537_us-ascii Within DB2, it's ssidDBM1 that allocates/deallocates the VSAM datasets. If a file is opened by DB2, then it is reserved for this task, and a DSN1COPY (or other) job that refers to it in a DD card has to wait because JES also tries to reserve the files at the beginning of job execution for exclusive use (as mentioned, I think that for VSAM files it doesn't matter whether you use DISP=SHR or OLD). Note that it is not the presence or absence of a STOP (or other DB2 function or utility) in the job that is the problem but [...]
10113 139 40_Re: RESERVED Data sets during STOP DB???14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Tue, 25 May 2004 13:48:11 -0400539_us-ascii The DB2 "STOP DATABASE" command when run from the console or from TSO including the batch TMP IKJEFT01 runs ASYNC ... that is the DATABASE STOPPED message does not mean the DATABASE is Stopped!!!!!!
This is explained in the DB2 Command manual under "-STOP DATABASE" the usage notes section.
So first of all I would recommend making sure the database is really stopped by doing a "-DIS DB" and making sure it is not in STOPP (Stop Pending) or waiting for the message in MSTR that says ASYNCRONOUS STOP COMPLETE. [...]
10253 46 56_Re: Cancelling / Terminating an in-process SQL statement14_Ben Eisenstein22_beisenstein@THEOCC.COM31_Tue, 25 May 2004 12:53:04 -0500388_- I ran into the same problem with V8 of the Command Center. Hiting cancel on the progress button does nothing.
I discovered that you can cause a cancel of the procedure by exiting Command Center completely. THis will work if you are ODBC. You may want to try that with your Quest tool. You will know it worked if you do a LIST DCS APPLICATIONS and see your thread disappear. [...]
10300 158 19_Re: Asynch I/O wait13_Paul Weissman21_paul.weissman@UBS.COM31_Tue, 25 May 2004 13:56:50 -0400389_iso-8859-1 Mike,
In addition, is it possible that you're getting a RID pool failure, in which case the access path becomes a tablescan?
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Joel Goldstein - Responsive Systems Sent: Tuesday, May 25, 2004 12:56 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Asynch I/O wait [...]
10459 126 40_Re: RESERVED Data sets during STOP DB???12_michael bell21_mbell11a1@VERIZON.NET31_Tue, 25 May 2004 13:00:56 -0500337_Windows-1252 Some thing that no one has mentioned is the relationship between the JCL DISP=OLD vs DISP=SHR. It does not control whether you can update the dataset. It only controls whether a different job can reference the dataset at the same time. VSAM SHROPTION does verify whether another update job is running at the same time. [...]
10586 151 40_Re: RESERVED Data sets during STOP DB???13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 25 May 2004 20:02:22 +0200546_us-ascii Wrong. In batch (IKJEFT01/1B) the STOP command operates synchronously, i.e. it waits. See Command Reference, STOP DATABASE, 2.73.5. Also the SHAREOPTIONS for DB2 VSAM datasets are required to be (3,3).
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany
From: Avram Friedman @IDUGDB2-L.ORG on 25-05-2004 13:48 AST Please respond to Avram.Friedman@morganstanley.com Sent by: DB2 Data Base Discussion List [...]
10738 335 18_Re: SQL Union help11_David Nance16_DWNance@FHSC.COM31_Tue, 25 May 2004 14:18:15 -0400579_US-ASCII
Carl, How about the following with no unions, just a left outer join to catch those tables without backups? I didn't try this out and just cut and pasted from your mail, so, disclaimers apply, but, should give you a general idea. SELECT g.DBNAME, g.TSNAME, sum(case when h.ICTYPE = 'F' AND h.ICBACKUP = ' ' then 1 else null end) AS LOCAL_PRIM, sum(case when h.ICTYPE = 'F' AND h.ICBACKUP = 'LB' then 1 else null end) AS LOCAL_BKUP, sum(case when h.ICTYPE = 'F' AND h.ICBACKUP = 'RP' then 1 else null end) AS REMOTE_PRIM, sum(case when h.ICTYPE is null then [...]
11074 228 55_Reorg fails with UTILITY BATCH MEMORY EXECUTION ABENDED11_David Nance16_DWNance@FHSC.COM31_Tue, 25 May 2004 14:17:54 -0400561_US-ASCII Hi all. Some help would be appreciated. Environment is DB2 V7 on Z/OS1.4. I am reorging one of our large tables and it keeps failing during the unload phase, anywhere from 14 million to 45 million records unloaded. I, successfully reorged larger and smaller objects over the weekend, but can't get this one. The table contains about 65 million records. Am passing in a region size of 0M for the reorg step. Here is the output from the job, I've highlighted the lines that give any kind of clue as to what may be failing. Thanks in advance for any [...]
11303 111 33_can select,, but can't delete????11_David Nance16_DWNance@FHSC.COM31_Tue, 25 May 2004 14:19:19 -0400568_US-ASCII
Has anyone else seen anything like this???? Really driving me nuts, which I don't need on a Friday.
Z/OS1.4 DB2 V7 pretty current on maint.
We run a select similar to:
select count(*) from table1 a where exists (select 1 from table2 b where a.col1 = b.col1 and b.col2 <> 'a') receive a count of 5282 then run delete statement: delete from table1 a where exists (select 1 from table2 b where a.col1 = b.col1 and b.col2 <> 'a') and get a +100. This makes no sense to me at all. Have any of you seen anything like this? Thanks. [...]
11415 51 28_Re: Workfiles in dataspaces?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 25 May 2004 21:23:27 +0200529_- Hi Max, Martin
The closest you can get with V8 is the modify,irlm,status,alli (or stor) And even that is not enough
Isaac Yassin
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of mscarpa@CESVE.IT Sent: Tuesday, May 25, 2004 4:56 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Workfiles in dataspaces?
Apologies, sure !
But I think we discussed about DB2 arguments, maybe useful, maybe not (assuming that someone READ this thread :-) ) [...]
11467 209 37_Re: can select,, but can't delete????15_Billings, Linda30_Linda.Billings@DOA.STATE.WI.US31_Tue, 25 May 2004 13:25:08 -0500393_iso-8859-1 I haven't looked at your problem, but I am dying to know what freaky time zone you are in. On my planet it is Tuesday. :-)
Linda Billings Database Administrator State of Wisconsin Department of Administration Division of Enterprise Technology Bureau of Development and Operations phone - 608-264-6396
"I find tinsel distracting." - Frank Costanza [...]
11677 207 37_Re: can select,, but can't delete????15_Billings, Linda30_Linda.Billings@DOA.STATE.WI.US31_Tue, 25 May 2004 13:31:50 -0500406_iso-8859-1 My colleague Rich Gugel, who is too busy to type an answer, :-) says to check your index with a check index utility. One is probably using it and one isn't.
Linda Billings Database Administrator State of Wisconsin Department of Administration Division of Enterprise Technology Bureau of Development and Operations
"I find tinsel distracting." - Frank Costanza [...]
11885 443 59_Re: Reorg fails with UTILITY BATCH MEMORY EXECUTION ABENDED11_Sinha, Amit19_sinha.amit@MBCO.COM31_Tue, 25 May 2004 13:33:57 -0500539_iso-8859-1 Calculate again the sortwork space by the formula
2 * (LONGEST INDEX KEY + 9) * (NUMBER OF KEYS EXTRACTED) THE RESULT IS IN BYTES
Put sysrec & Syscopy on tape & allocate sort work space as SWNNWKNN to make your Reorg faster by parallel tasks & allocate them this way
//SW01WK01 DD UNIT=SYSDA,SPACE=(CYL,(500,500)) //SW02WK01 DD UNIT=SYSDA,SPACE=(CYL,(500,500)) //SW03WK01 DD UNIT=SYSDA,SPACE=(CYL,(500,500)) //SW04WK01 DD UNIT=SYSDA,SPACE=(CYL,(500,500)) //SW05WK01 DD UNIT=SYSDA,SPACE=(CYL,(500,500)) [...]
12329 159 59_Re: Reorg fails with UTILITY BATCH MEMORY EXECUTION ABENDED13_Paul Weissman21_paul.weissman@UBS.COM31_Tue, 25 May 2004 14:32:33 -0400694_iso-8859-1 Dave,
you need to change UNIT=(SYSDA,3) to UNIT=SYSDA or something like that from your JCL. Add more SORTWK's instead of increasing the UNIT count.
WER263A ILLEGAL USE OF MULTI-VOLUME SORTWK EXPLANATION: SyncSort does not support the use of multi-volume disk SORTWK data sets. (However, if SyncSort only requires the use of the space on the first volume of a multi-volume SORTWK file, this error message will not be issued.) ACTION: Remove the volume count subparameters of the UNIT parameter on all SORTWK DD statements that specify more than one volume. ****************************** BOTTOM OF DATA ******************************* sage----- From: DB2 Data Base [...]
12489 182 37_Re: can select,, but can't delete????17_Duane Lee - EGOVX22_DLee@MAIL.MARICOPA.GOV31_Tue, 25 May 2004 11:36:00 -0700451_iso-8859-1 Have you executed DSN1COPY or something similar without rebuilding the index? It almost sounds like an index problem.
-----Original Message----- From: David Nance [mailto:DWNance@FHSC.COM] Sent: Tuesday, May 25, 2004 11:19 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: can select,, but can't delete????
Has anyone else seen anything like this???? Really driving me nuts, which I don't need on a Friday. [...]
12672 35 56_Update on object SYSIBM.SYSTABLESPACE.NACTIVEF with -5517_Do Park19_doparkdba@YAHOO.COM31_Tue, 25 May 2004 14:00:47 -0500493_- Hello
The following job abended because of -551. BATCHUSR user has select and update authority on SYSIBM.SYSTABLESPACE. What privilege is needed?
.UPDATE SYSIBM.SYSTABLESPACE . SET NACTIVE = 17000, . NACTIVEF = 17000, . STATSTIME = '99' || SUBSTR(CHAR(CURRENT TIMESTAMP),3,24) . WHERE DBNAME = 'ISV3#ARX' . AND NAME = 'AXOCITT1' ; . DSNT408I SQLCODE = -551, ERROR: BATCHUSR DOES NOT HAVE THE PRIVILEGE TO . PERFORM OPERATION UPDATE ON OBJECT SYSIBM.SYSTABLESPACE.NACTIVEF [...]
12708 255 37_Re: can select,, but can't delete????19_Janes, Ray H {PBSG}20_Ray.H.Janes@PBSG.COM31_Tue, 25 May 2004 14:08:42 -0500397_- Here on Zorclog, it is Uliy, 13, 208:43:flyo
-----Original Message----- From: Billings, Linda [mailto:Linda.Billings@DOA.STATE.WI.US] Sent: Tuesday, May 25, 2004 1:25 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: can select,, but can't delete????
I haven't looked at your problem, but I am dying to know what freaky time zone you are in. On my planet it is Tuesday. :-) [...]
12964 244 37_Re: can select,, but can't delete????11_David Nance16_DWNance@FHSC.COM31_Tue, 25 May 2004 15:32:50 -0400347_US-ASCII No. I had, originally, sent the mail on Friday, but then saw today that I had sent it to the old address, So, I resent today to the correct address. Same with my reorg question, sent to old address yesterday.
Dave Nance First Health Services, Corp. (804)527-6841
>>> Ray.H.Janes@PBSG.COM 5/25/04 3:08:42 PM >>> [...]
13209 241 37_Re: can select,, but can't delete????11_David Nance16_DWNance@FHSC.COM31_Tue, 25 May 2004 15:34:28 -0400493_US-ASCII Also, in response to other replies about this the index was fine and we have not used dsn1copy.
Dave Nance First Health Services, Corp. (804)527-6841
>>> Ray.H.Janes@PBSG.COM 5/25/04 3:08:42 PM >>>
Here on Zorclog, it is Uliy, 13, 208:43:flyo
-----Original Message----- From: Billings, Linda [mailto:Linda.Billings@DOA.STATE.WI.US] Sent: Tuesday, May 25, 2004 1:25 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: can select,, but can't delete???? [...]
13451 249 37_Re: can select,, but can't delete????11_David Nance16_DWNance@FHSC.COM31_Tue, 25 May 2004 15:38:12 -0400348_US-ASCII One last item to mention on this post. I did delete all the records involved by running the same SQL to generate 5,282 individual delete statements and that worked fine.
Also, in response to other replies about this the index was fine and we have not used dsn1copy.
Dave Nance First Health Services, Corp. (804)527-6841 [...]
13701 32 18_ONLINE REORG ERROR11_Roger Ghose25_roger.ghose@MTO.GOV.ON.CA31_Tue, 25 May 2004 14:50:44 -0500751_- Hi List, We have been experiencing problems running ONLINE REORG with tablespace that have a secondary Indexes (NPI) or columns that have long varchar. If any one knows how to fix this problem your help will be much appreciated.
Thanks!
Roger
Following is the error message:
1DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = REORG01 0DSNU050I DSNUGUTC - REORG TABLESPACE DBNAME.TSNAME PART 1 LOG NO SHRLEVEL CHANGE DEADLINE 2004-05-25-11.01.16.691958 FASTSWITCH YES MAPPINGTABLE XXXXXX.MAPTGLGFSS1 MAXRO 300 LONGLOG CONTINUE DELAY 1200 TIMEOUT ABEND KEEPDICTIONARY COPYDDN(CPLA0001) STATISTICS REPORT NO UPDATE ALL HISTORY ALL UNLDDN SSSREC WORKDDN( SSSUT1, SORTOUT) UNLOAD CONTINUE -DSNU017I DSNUGBAC [...]
13734 319 37_Re: can select,, but can't delete????15_Billings, Linda30_Linda.Billings@DOA.STATE.WI.US31_Tue, 25 May 2004 14:53:24 -0500414_iso-8859-1 Did you use the EXISTS clause in each delete statement? Mindy and I were discussing this. It might have something to do with your EXISTS clause. According to the V7 SQL manual, EXISTS returns a TRUE/FALSE result. A DELETE will delete a row at a cursor position. Since a TRUE/FALSE result is being returned by your subselect maybe a cursor is not positioned and the DELETE can't perform its delete. [...]
14054 350 37_Re: can select,, but can't delete????15_Billings, Linda30_Linda.Billings@DOA.STATE.WI.US31_Tue, 25 May 2004 14:57:49 -0500495_iso-8859-1 Oooops! Correction. A cursor is positioned for a positioned delete. Otherwise, a searched delete is performed. But again, since a TRUE/FALSE is being returned, how is DB2 to know what to delete? The SELECT COUNT(*) probably worked because it found the existence of a rows 5282 times.
Linda
-----Original Message----- From: Billings, Linda Sent: Tuesday, May 25, 2004 2:53 PM To: 'DB2 Database Discussion list at IDUG' Subject: RE: can select,, but can't delete???? [...]
14405 23 19_Re: Asynch I/O wait17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Tue, 25 May 2004 15:11:36 -0500297_- App should not wait for Async I/O. If you do wait, then it could mean two things.
1.0 There is something wrong in I/O subsystem so the throughput is affected.
2.0 App is not really interested in the page the prefetch tries to bring in resulting in more and more I/O requests. [...]
14429 142 22_db2 cics/cobol program16_Proctor, William25_William.Proctor@TGSLC.ORG31_Tue, 25 May 2004 15:30:19 -0500335_us-ascii We have a cics program that we want to be able to access two different databases depending on the time of day. The databases are mirror images of each other except for the database name. Is it possible in the program to switch from one database qualifier (slim) to the other database qualifier(cacs) in the same program? [...]
14572 155 26_Re: db2 cics/cobol program14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Tue, 25 May 2004 13:36:57 -0700474_iso-8859-1 just do a conditional search -- define both tables to the program and use the one that meets the qualifications at run time
there is no magic for a program to access different databases or tables; you may need your DBA to get a fully qualified name.
Susan
-----Original Message----- From: Proctor, William [mailto:William.Proctor@TGSLC.ORG] Sent: Tuesday, May 25, 2004 1:30 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: db2 cics/cobol program [...]
14728 76 26_Re: db2 cics/cobol program0_22_sally.mir@WACHOVIA.COM31_Tue, 25 May 2004 16:41:45 -0400362_US-ASCII Absolutely, if you're talking about having different table qualifiers on identical tables. Write your code using unqualified SQL. Bind the package into two different collections, one with each qualifier you want to use. In your program, issue the SQL statement: SET CURRENT PACKAGESET = collectionID to point to one collection or the other. Voila! [...]
14805 112 26_Re: db2 cics/cobol program10_Mark Labby21_mlabby@AESSUCCESS.ORG31_Tue, 25 May 2004 16:48:09 -0400376_iso-8859-1 Bill,
We have COBOL programs switching which copies of identical tables they need to process by issuing a Set Current Packageset command and switching to a different Collection. All of our programs are coded with unqualified SQL and are bound into a different Collection for each table qualifier.
EXEC SQL SET CURRENT PACKAGESET = 'SLIM_COLLID'; [...]
14918 117 31_Re: Cylinder boundaries & Shark13_Solecki, Rohn19_Rohn.Solecki@GWL.CA31_Tue, 25 May 2004 15:52:35 -0500578_iso-8859-1 This is a question I've been interested in myself.
The last time I tested it was on mvs 2.10 and Db2 V6.1 and shark drives.
As I remember, DB2 does the allocations in the catalogue by 4kb blocks. But if you check the physical allocation on the disk pack it is done in multiples of whole tracks and cylinders by the operating system file management system. As far as I know, the OS does not recognise "Shark" drives as a physical or logical type. Instead the Shark is logically partitioned into units it does recognise i.e. variations of 3390, i.e. [...]
15036 22 30_Re: Cylinder boundries & Shark17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Tue, 25 May 2004 15:59:01 -0500397_- Since you asked Peoplesoft, more than the wastage of space, the performance does matter.
You can find many indexes allocated on track boundaries and since these are inserted / deleted during the job havily, you will incur unit switch waits.
This could hurt performance.
Identify such critical / important tables and allocate on cyl boundary, preferably with preformat. [...]
15059 13 22_Re: ONLINE REORG ERROR9_Jim Ruddy18_jaruddy@US.IBM.COM31_Tue, 25 May 2004 16:30:35 -0500533_- Apply apar PQ83074
Jim Ruddy DB2 for z/OS Development
--------------------------------------------------------------------------------- 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
15073 186 26_Re: db2 cics/cobol program18_Pudukotai, Nagaraj25_Nagaraj.Pudukotai@FMR.COM31_Tue, 25 May 2004 17:35:55 -0400359_us-ascii You can do that by creating aliases. In your case create say an alias A1 both for slim and cacs and then bind your package to the alias A1 and you are done.
-----Original Message----- From: Proctor, William [mailto:William.Proctor@TGSLC.ORG] Sent: Tuesday, May 25, 2004 4:30 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: db2 cics/cobol program [...]
15260 92 24_Re: SQL performance ....13_Solecki, Rohn19_Rohn.Solecki@GWL.CA31_Tue, 25 May 2004 17:01:38 -0500319_iso-8859-1 Do you need all three keys to define uniqueness? (???i.e. maybe third was added to allow another SQL statement Index Only access???) ??? if the 3rd field isn't needed for uniqueness, could you sneak the date field into the key before it.
Do you have the 3rd key value to add to the WHERE clause? [...]
15353 43 25_DB2 tools need to run SAP11_Rob Barbour22_rbarbour@ESAIGROUP.COM31_Tue, 25 May 2004 19:27:22 -04001040_us-ascii Grace,
The BCV4 product can help you clone your DB2, SAP and PeopleSoft systems. It complements IBM, HDS, EMC hardware copy facilities by making a clone usable in the fastest possible manner.
The following links will give you more info:
(1) BCV4 page
http://www.esaigroup.com/products/bcv4.htm (2) Business Partner page:
http://www.developer.ibm.com/bpconnections/bpcms.nsf/public/78A776A1505BF2DE86256E3D008062A1?OpenDocument
All the Best,
Rob
---------------------------------
--------------------------------------------------------------------------------- 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 [...]
15397 375 26_Re: db2 cics/cobol program12_Troy Coleman25_troy.coleman@SOFTBASE.COM31_Tue, 25 May 2004 20:53:03 -0500374_us-ascii Hi Bill, The way you would do this is using two different collection names with the program bound in each of them with a different qualifier. For example, PLANA PKLIST(SLIM_C.*, CACS_C.*)
Now your program would do a SET CURRENT PACKAGESET to either SLIM_C or CACS_C.
I hope this helps. Troy Coleman, Sales Engineer IBM Certified Solutions Expert [...]
15773 99 40_Re: RESERVED Data sets during STOP DB???0_18_PPantazi@LAIKI.COM31_Wed, 26 May 2004 08:08:06 +0300406_us-ascii Thanks for the explanation Michael. This explains things further.
We have JES2 and DB2/390 V6.
Based on everyone's comments, It seems that my only options are
(1) Use DISP=SHR on the db2 dataset (SYSUT1)
(2) Use a separate job to do the STOP DB (solely)
I will review both and see which one is easier to implement since the jobs are generated through REXX. [...]
15873 137 19_Re: Asynch I/O wait19_Alekos Papadopoulos13_apapad@NBG.GR31_Wed, 26 May 2004 09:07:24 +0200604_ISO-8859-7 Hello Mike,
If somebody added lots of rows with NULL CESS_DTE recently (even if KEYVAL was not 'ABCDEFGH') then you have a case that list prefetch worked fine before (well, at least not bad), and now takes ages. What's the percentage of NULLs in CESS_DTE in your table? Double check what Paul Weissman said (RID pool failure). It results to the same effect (converts a relatively fast query to a turtle). IMHO, although an index on (KEYVAL, CESS_DTE) seems quite promising as stated in many other responses, BEFORE creating a new NPI check if the application can reduce the number [...]
16011 204 59_Re: Reorg fails with UTILITY BATCH MEMORY EXECUTION ABENDED19_Alekos Papadopoulos13_apapad@NBG.GR31_Wed, 26 May 2004 09:50:52 +0200339_iso-8859-7 Hello David,
It might not help in your case, but could you try running STOSPACE in the appropriate storage groups? I know at least one case that REORG was passing incorrect parameters to sort (DFSORT in my case), just because of that (STOSPACE was never executed), although 'RUNSTATS update all' was used regularly. [...]
16216 56 30_BP thresholds for mass inserts14_Heiss, Gerhard20_Gerhard.Heiss@RUV.DE31_Wed, 26 May 2004 09:28:04 +0200535_iso-8859-1 Hello all,
I will shortly be facing some inevitable batchjobs, doing mass inserts into large tables. Up to 15 are scheduled to run in parallel. During a run on the test system, I found unusual high values (up to 40%) for elapsed time spent in DB2 waiting for write I/O done by another thread (QWACAWTW). Now I wonder if it would be beneficial to temporarily adjust DWQT or VDWQT or others for this kind of workload. Currently we have two separate pools, one for tablespace and one and one for indexes like this: [...]
16273 91 19_Re: Asynch I/O wait0_19_mike.holmans@BT.COM31_Wed, 26 May 2004 11:17:03 +0100621_iso-8859-1 Joel,
Thanks very much indeed.
Thanks also to Phil Sevetson and the others who replied - I'm at least relieved to know that I hadn't missed anything obvious, because we've tried reversing the order of the key fields (yes, CESS_DTE is null in 99.23% of cases) but that had no noticeable effect. And we reorg the indexes weekly and the underlying tablespace partitions get individually reorged when the clusterratio drops below 96%, and that hasn't had much effect either. What puzzled me about those solutions was how they would address the problem of asynchronous I/O wait - they'd be ideal [...]
16365 251 59_Re: Reorg fails with UTILITY BATCH MEMORY EXECUTION ABENDED16_Proctor, William25_William.Proctor@TGSLC.ORG31_Wed, 26 May 2004 06:25:44 -0500395_iso-8859-7 You might try changing you region card to 64m or 32m. We have a couple of processes that used syncsort that 0m caused too many parallel activities to start at once. Changing to 32m reduced that and allowed our jobs to run successfully. Hope that helps.
Bill Proctor Database Administrator (Adabas/DB2) Texas Guaranteed Student Loan Corp. Austin, Texas Phone: 512-219-4847 [...]
16617 69 25_Checking negative SQLCODE58_=?iso-8859-9?Q?Serdar_Sabri_=D6zkubulay_=28B.T.V.Y.G.=29?=27_Serdar.Ozkubulay@AKBANK.COM31_Wed, 26 May 2004 15:21:34 +0300347_iso-8859-9 Hi list,
One of our application developer checks something in her SQL by controlling negative SQLCODE (-305). But she can do this control also by defining null indicator to host output variable.
I wonder which one makes sense? Checking negative SQLCODE or host variable (This occurs more than 100.000 times in a day) [...]
16687 233 29_Re: Checking negative SQLCODE15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Wed, 26 May 2004 08:39:50 -0400384_Windows-1254 I can't actually speak to the internals of DB2, but it _should_ be cheaper to have a null indicator defined; that should avoid a lot of error-routine processing by DBM1. Shouldn't she be using WHERE colname IS NOT NULL, or some logical variant of that??
--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com [...]
16921 107 34_Re: BP thresholds for mass inserts14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Wed, 26 May 2004 09:08:35 -0400504_iso-8859-1 It is MHO that VDWQT should always be about zero most shops. IBM changed the meaning of the value 0 about 5 years ago. They observed that many (most) shops had such large buffer pools that any VDWQT interger percentage repersented a huge number of buffers. VDWQT is now coded as (%,# Buffers) If %=0 then If # Buffers not coded about 32 pages are written when the buffer contains 40 modified pages for a single space # Buffers is used to fine tune how many pages are written if %=0 only. [...]
17029 89 36_RESERVED Data sets during STOP DB???0_18_PPantazi@LAIKI.COM31_Tue, 25 May 2004 18:55:11 +0300311_us-ascii Hi Listers,
I have a series of batch jobs that I use during the refresh of the QA subsystem using DSN1COPY.
As you know the tablespace must be stopped in order for DSN1COPY to work and for this reason I have included as the first step in all jobs the STOP DB command as shown below: [...]
17119 34 40_Re: Binding SPUFI plan with location * ?13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 25 May 2004 17:46:01 +0200324_us-ascii Hi Dave,
I have SPUFI, DSNTEP2, DSNTIAUL all enabled for distributed access. This makes for very easy administration from one central location. If I want to check something on another SSID, I don't have to log in to that LPAR but simply specify the location in the SPUFI panel (or a CONNECT statement). [...]
17154 91 29_Re: Checking negative SQLCODE9_Agus Kwee13_askwe@ATT.NET31_Wed, 26 May 2004 09:20:15 -0400352_iso-8859-9 How many input host variables are used in the SELECT INTO statement? If the SELECT INTO statement received the -305 SQLCODE, non null values from the row will be be moved to the host variables while the host variables for the nulls from the row will be unchanged. How does the program check the host variables that "receive" the nulls? [...]
17246 85 40_Re: RESERVED Data sets during STOP DB???0_18_PPantazi@LAIKI.COM31_Tue, 25 May 2004 20:13:50 +0300563_us-ascii Michael, Thank you for the prompt response. I too believe that the reason you mentioned is the correct but I did not understand why the STOP worked when it was issued from the DBADM panels.
I did not understand the action that you propose for the batch handling.
I know that this works for normal VSAM operations but for DB2 I am not sure how to implement it since this involves DB2 commands and functions that do not directly reference the physical VSAM cluster, but rather the definitions in the catalog and DB2 takes care the rest. [...]
17332 17 22_Re: ONLINE REORG ERROR11_Roger Ghose25_roger.ghose@MTO.GOV.ON.CA31_Wed, 26 May 2004 09:06:44 -0500597_- Jim, Thanks ever so much for your response. We have asked our sytem programmers to look into it.
Thanks!
Roger
--------------------------------------------------------------------------------- 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
17350 86 40_Re: Binding SPUFI plan with location * ?7_Ed Long19_rdhm99a@PRODIGY.NET31_Wed, 26 May 2004 07:57:19 -0700632_us-ascii I'm doing the same thing. The only sticky bit is keeping straight the to and fro's, as in, system a has a local and a remote version as does system b. I've been using separate JCL proc's that autmatically point you to the local or remote guy.
--- "Seibert, Dave" wrote: > Hello all, > > I'm curious how many installations have enabled > SPUFI for distributed > access. > > I have seen a bind for the SPUFI Plan including > PKLIST of > (DSNESPCS.DSNESM68, > RMTLOC1.DSNESPCS.DSNESM68,RMTLOC2.DSNESPCS.DSNESM68,RMTLOC3.DSNESPCS.DSNESM6 > 8, ...) ... > > In our playground Subsystem I [...]
17437 108 76_SQL1042C An unexpected system error occurred after installed UDB v8 FixPAK 57_Do Park19_doparkdba@YAHOO.COM31_Wed, 26 May 2004 10:20:53 -0500349_- Hello
I installed UDB V8.1 FixPak 5 from Fixpak 3 on Red Hat Linux without any problem, but UDB could not come up.
I got the following.
SQL1042C An unexpected system error occurred.
Is anyone have the same problem? What caused the problem? How can I fix this problem?
Ths following is from the db2diag.log. [...]
17546 131 29_Re: Checking negative SQLCODE16_Graeme St. Clair23_Graeme.St.Clair@HDS.COM31_Wed, 26 May 2004 08:28:13 -0700581_iso-8859-1 Absolutely, she should use indicator variables. Even I know how to do this (!), because it's well documented at the earliest levels of study.
For programming elegance and maintainability, you should always use a mechanism for its designed purpose (indicator variables), and not use a mechanism for some accidental side effect (SQLCODE -305). And in any case, how did she propose to handle the case where -305 occurred for some other variable?! And even if there is currently only one null-possible variable, what if somebody else adds another in the future? [...]
17678 153 29_Re: Checking negative SQLCODE58_=?iso-8859-9?Q?Serdar_Sabri_=D6zkubulay_=28B.T.V.Y.G.=29?=27_Serdar.Ozkubulay@AKBANK.COM31_Wed, 26 May 2004 18:37:50 +0300594_iso-8859-9 If it returns null its null indicator will be -1
-----Original Message----- From: Agus Kwee [mailto:askwe@ATT.NET] Sent: Wednesday, May 26, 2004 4:20 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Checking negative SQLCODE
How many input host variables are used in the SELECT INTO statement? If the SELECT INTO statement received the -305 SQLCODE, non null values from the row will be be moved to the host variables while the host variables for the nulls from the row will be unchanged. How does the program check the host variables that "receive" the nulls? [...]
17832 44 95_Priviledge needed for programmers to use Declare Global Temporary Tables on DB2 UDB V81. on AIX18_Stritzinger, David28_David.Stritzinger@ALTRIA.COM31_Wed, 26 May 2004 11:56:39 -0400344_- Folks,
My environment is DB2 V8.1 fix pack 3 on AIX 5.1. The development staff is trying to use the Stored Procedure Builder to compile SQL Stored Procedures that use Declared Global Temporary tables. I have them using a generic id that they can su over to on the AIX box and also log on from their workstation using the SPB GUI. [...]
17877 42 53_DB2 Packs need Defragmentation: OS/390 tools, anyone?15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Wed, 26 May 2004 12:24:15 -0400402_us-ascii We have a number of large packs with a 24x179.5 (two scheduled outages per year) environment running on them. We need to be able to defragment while up-and-running. Does anyone have or recommend tools which can accomplish this, or a reasonable facsimile? Vendors are invited.
--Phil Sevetson Database Administration Wakefern Food Corporation CISD mailto:phil.sevetson@wakefern.com [...]
17920 57 52_Replacing BINDAGENT When Using RACF Security for DB29_Al Heiden31_alheiden@NORTHWESTERNMUTUAL.COM31_Wed, 26 May 2004 11:37:02 -0500470_- We are in the process of converting our DB2 Security to RACF. We have now run into the BINDAGENT Issue that others have mentioned in the past on this list. We would like to know what other people/companies have done to resolve this issue using RACF for their DB2 Security. We have been told by our management this is the way we are going, otherwise I would have dropped the project (what it sounds like others have done in the past) and left the security in DB2. [...]
17978 18 29_Re: Checking negative SQLCODE0_13_askwe@ATT.NET31_Wed, 26 May 2004 16:43:48 +0000683_- Serdar,
If the application uses null indicator(s), then it will receive SQLCODE = 0 instead of SQLCODE = -305, and agreeing with the previous responses, this should be the method to use.
Agus Kwee
--------------------------------------------------------------------------------- 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
17997 49 11_CDB, Please15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Wed, 26 May 2004 12:50:38 -0400843_us-ascii Would a representative of CDB reply to me off-list, please? I need to inquire about the performance characteristics of your proprietary LOAD product.
--Phil Sevetson Database Administration Wakefern Food Corporation CISD 230 Raritan Center Parkway, P5-105 Edison, NJ 08837 Phone: (732) 225-8086 Fax: (732) 225-8109 mailto:phil.sevetson@wakefern.com
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
18047 19 34_Re: BP thresholds for mass inserts17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Wed, 26 May 2004 12:04:22 -0500742_- Can you not do thru LOAD?.
Favor VDWQT to zero. Since you have only 2 bufferpool, you may be disbaling prefetch( for other queries that really benefit) to a great extent because you have VPSEQT set to 10. Also look at your checkpoint time.
Regards, 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
18067 101 41_Seattle DB2 User Group - June 8th Meeting17_Lidstrom, Barbara27_barbara.lidstrom@BOEING.COM31_Wed, 26 May 2004 11:03:24 -0700510_us-ascii > We are very excited to have Sean Lee speaking at our June 8th meeting > on "DB2 is from Mars & WebServers are from Venus". His presentation > will include: > > * Into to WAS, JDBC, and SQLJ > * JDBC / SQLJ Driver Support in DB2 > * WebSphere / DB2 architectures > * Configuring DB2 DataSources in WebSphere > * Transaction Management > * What is Two phase commit / XA? > * How is XA implemented in DB2? > * Auditing and security > * Select DB2 UDB for z/OS Version 8 enhancements for WebSphere [...]
18169 20 18_Sideway references18_valeriet6@juno.com18_valeriet6@JUNO.COM29_Wed, 26 May 2004 18:26:10 GMT330_- In the V8 SQL Reference manual under CREATE TABLE 5.38, there is mention of sideway references. Specifically, it is in reference to an MQT, and when you specify ENABLE QUERY OPTIMIZATION, there are some restrictions and one of them is that the subselect cannot contain sideway references. Does anyone know what this means? [...]
18190 95 58_Baltimore/Washington DB2 Users' Group-June 9, 2004 Meeting14_Henry L Nalven25_henry.nalven@MARRIOTT.COM31_Wed, 26 May 2004 14:00:25 -0500484_- REMINDER about the upcoming Baltimore/Washington DB2 Event!!!!
Baltimore/Washington DB2 Users' Group
WWW.BWDB2UG.ORG
Wednesday, June 9, 2004 Meeting Agenda When? Wednesday, June 9, 2004, 9:00 AM-3:30 PM (Registration at 8:30)
Where? Sheraton Columbia Inn, 10207 Wincopin Circle Columbia, MD (410) 730-3900
Meeting Fees: Pre-registered members: $20 Pre-registered non-members: $30 At-the-door for members: $30 At-the-door for non-members: $40 [...]
18286 18 22_Re: Sideway references15_Patrick Bossman18_bossman@US.IBM.COM31_Wed, 26 May 2004 15:12:22 -0500673_- Side-ways reference:
SELECT cols FROM T1, TABLE (SELECT 1 FROM T2 WHERE T1.C1 = T2.C1) X;
The correlated table expression is also called a sideways reference.
Regards, Pat Bossman
--------------------------------------------------------------------------------- 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
18305 73 60_Re: Update on object SYSIBM.SYSTABLESPACE.NACTIVEF with -55112_michael bell21_mbell11a1@VERIZON.NET31_Wed, 26 May 2004 17:32:26 -0500533_iso-8859-1 Looking at the admin guide for V7 - NACTIVE is no longer on the list of updateable fields. DB2 always uses the float variable NACTIVEF now. To update NACTIVEF in V7, I had to use floating point format NACTIVEF=+0.1700000000000000E+05
Mike Bell HLS Technologies
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Do Park Sent: Tuesday, May 25, 2004 2:01 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Update on object SYSIBM.SYSTABLESPACE.NACTIVEF with -551 [...]
18379 87 99_Re: Priviledge needed for programmers to use Declare Global Temporary Tables on DB2 UDB V81. on AIX16_Lance D. Jackson30_LJackson@PANDRUEASSOCIATES.COM31_Wed, 26 May 2004 21:11:12 -0400556_us-ascii Dave,
Try issuing the following: GRANT ALL PRIVILEGES ON TABLE SYSIBM.SQTPROCEDURECOLS TO xxxxxxxx where "xxxxxxxx" is either the primary or secondary userid.
I hope it helps.
Lance J. Pandrue Associates, Inc.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Stritzinger, David Sent: Wednesday, May 26, 2004 11:57 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Priviledge needed for programmers to use Declare Global Temporary Tables on DB2 UDB V81. on AIX [...]
18467 110 60_Re: Update on object SYSIBM.SYSTABLESPACE.NACTIVEF with -5517_DB2 DBA26_db2dba_certified@YAHOO.COM31_Wed, 26 May 2004 19:14:24 -0700358_us-ascii I tried the same query by setting SQLID to an ID which has DBADM privilege and it also has EXPLICIT update access on SYSIBM.SYSTABLESPACE and the query worked for me. As DBADM doesn't control DB2 Catalog, I guess anyone with update access to SYSTABLESPACE should be able to perform this. DB2 uses NACTIVEF but still NACTIVE field is updatable. [...]
18578 114 18_Re: Synch I/O wait12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 27 May 2004 07:50:37 +0200318_- Hi Mike,
I've been burnt in the past by the EMC symmetrix boxes. Many times they claimed it was our fault at some other part of the system (response times of 1700ms - no mistake - it's 1.7 seconds) were common. It only went away when we replaced them with another company boxes (more details - offline). [...]
18693 75 60_Re: Update on object SYSIBM.SYSTABLESPACE.NACTIVEF with -5514_Kals22_teldb2kals@BIGPOND.COM31_Thu, 27 May 2004 00:57:33 -0500634_- Hi,
Updates to columns in catalog tables need explicit update access granted on the desired columns. Please verify if the user has update auth on the NACTIVE column.
Regards, Kals On Wed, 26 May 2004 19:14:24 -0700, DB2 DBA wrote:
>I tried the same query by setting SQLID to an ID which has DBADM privilege and it also has EXPLICIT update access on SYSIBM.SYSTABLESPACE and the query worked for me. As DBADM doesn't control DB2 Catalog, I guess anyone with update access to SYSTABLESPACE should be able to perform this. >DB2 uses NACTIVEF but still NACTIVE field is updatable. [...]
18769 181 24_Re: SQL performance ....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Thu, 27 May 2004 00:05:24 -0700460_us-ascii Thanks Rohn, Seth, David and Neil for valuable suggestions. The table is partitioned. Pages being brought into the buffer pool by the first query was my initial thought too, but I dismissed it because even the CPU time of the second query is much lesser than the first query. Had I/O into the buffer pool been the deciding factor, I would have thought that the CPU time for both queries would at least have been almost the same (am I correct??). [...]
18951 51 34_Re: BP thresholds for mass inserts14_Heiss, Gerhard20_Gerhard.Heiss@RUV.DE31_Thu, 27 May 2004 10:59:09 +0200502_us-ascii It was decided to use existing programs first, which usually process only a few records per day, not millions. Because of interdependencies it is rather complex to generate loadfiles with rewritten programs. Therefore it would be costly (in terms of manpower) to use LOAD. VDWQT to zero; okay, got that :-). Should VPSEQT be set to zero too? No queries will be running at the time when the jobs execute. But each insert is accompanied by some random selects (mostly unique index access). [...]
19003 24 53_About the general formula for computing RID Pool size15_Daniel Cremieux17_c300501@YAHOO.COM31_Thu, 27 May 2004 07:45:15 -0500719_ISO-8859-1 Hi all ,
Just wonder why we have to multiply by 2 in the formula ?
Number of concurrent RID processing activities average number of RIDs × 2 × A (bytes per RID) where A is: v 4 for non-large tables v 5 for large tables
(Source : Install Guide)
--------------------------------------------------------------------------------- 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 [...]
19028 65 34_Re: BP thresholds for mass inserts14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Thu, 27 May 2004 08:51:41 -0400322_us-ascii One of the common objectives of tuning and capacity efforts is a stable environment ... that is one that does not need to be readjusted 10 seconds, minutes, hours, days, months later. This is not the only possible objective ... best performance at all cost (even instability) is the objective of some shops. [...]
19094 90 34_Re: BP thresholds for mass inserts35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 27 May 2004 09:04:51 -0400441_iso-8859-1 vpseqt should never be set to zero, since this will completely disable all types of prefetch and would drive up the synch i/o rate if something did cause a prefetch.
However, lowering this threshold is quite common and I highly recommend this for pools that should have only/mostly random access. For pools that are all/mostly random, vpseqt should not be more than 25%, and even as low as 5-10% for very large pools. [...]
19185 116 100_Re: Priviledge needed for programmers to use Declare Global Tempo rary Tables on DB2 UDB V81. on AIX18_Stritzinger, David28_David.Stritzinger@ALTRIA.COM31_Thu, 27 May 2004 09:30:51 -0400440_- Lance,
Thanks for the tip, but I tried it and it still did not help me. I also send the same question to the DB2EUG list and Fred (do not know his last name), sent back that I had to first run a script with just the declared temp table syntax and then compile the SP. This worked, but I can not believe that I have to take the syntax for the declare temp tables out of the SP, run that through first and then do the SP build. [...]
19302 89 57_Re: About the general formula for computing RID Pool size0_28_Missy.Case@FIRSTDATACORP.COM31_Thu, 27 May 2004 08:47:27 -0500380_ISO-8859-1 The normal is 1 count for In RIDs & 1 count for out RIDs as they are sorted while the process is running. Missy Case FDR 701-275-6358
Daniel Cremieux cc: Sent by: DB2 Data bcc: Base Discussion Subject: About the general formula for computing RID Pool size List [...]
19392 43 57_Re: About the general formula for computing RID Pool size25_Thanikachalam Sundarrajan21_billysundar@YAHOO.COM31_Thu, 27 May 2004 06:48:33 -0700577_us-ascii i think RID pool needs 2x (because one for SORTIN and one for SORTOUT)?
--- Daniel Cremieux wrote: > Hi all , > > Just wonder why we have to multiply by 2 in the formula ? > > > Number of concurrent RID processing activities > average number of RIDs × 2 × A (bytes per RID) > where A is: > v 4 for non-large tables > v 5 for large tables > > (Source : Install Guide) > > --------------------------------------------------------------------------------- > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page [...]
19436 47 57_Re: About the general formula for computing RID Pool size9_Agus Kwee13_askwe@ATT.NET31_Thu, 27 May 2004 09:49:34 -0400440_iso-8859-1 Daniel,
According to the DB2 Administration Guide manual Chapter 5.4.3: The RID pool is used for the following RID processing: 1. For enforcing unique keys. 2. For sorting RIDS during the following operations: list prefetch, access via multiple indexes and hybrid join. I think for the sort to run optimally, storage are required to contain the RIDs for the input of the sort and the RIDs for the output of the sort. [...]
19484 16 34_Re: BP thresholds for mass inserts14_Heiss, Gerhard20_Gerhard.Heiss@RUV.DE31_Thu, 27 May 2004 16:01:29 +0200574_us-ascii Thanks Avram, Joel and Venkat for your most valuable suggestions.
Best regards, Gerhard
--------------------------------------------------------------------------------- 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
19501 80 57_Re: About the general formula for computing RID Pool size35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 27 May 2004 10:15:13 -0400786_iso-8859-1 Because you need twice as much space as the RIDs occupy in order to sort them in the RID pool. Regards, Joel
----- Original Message ----- From: "Thanikachalam Sundarrajan" To: Sent: Thursday, May 27, 2004 9:48 AM Subject: Re: About the general formula for computing RID Pool size
> ---------------------- Information from the mail header ----------------------- > Sender: DB2 Data Base Discussion List > Poster: Thanikachalam Sundarrajan > Subject: Re: About the general formula for computing RID Pool size > -------------------------------------------------------------------------- ----- > > i think RID pool needs 2x (because one for SORTIN and one [...]
19582 26 16_Contention rates12_Michael Hall21_mhhall1@ATTGLOBAL.NET31_Thu, 27 May 2004 09:27:51 -0500359_- I am looking at the RMF CF activity report. The RMF book says if true contention for DB2 lock structure is over 1% there is a contention problem that will impact data sharing performance.
The DB2 PM book says that anything over 5% is a problem.
What kinds of guidelines do other listers use for guidelines for DB2 Global lock contention? [...]
19609 74 34_Re: BP thresholds for mass inserts11_Chris White18_crick616@YAHOO.COM31_Thu, 27 May 2004 10:05:00 -0500327_- Just a thought about a possible secondary problem here. Since you are using existing programs that were originally developed for much smaller workloads, it would be a good idea to review the commit scope. If it's a hard coded small value (or worse, commit after each insert), you will encounter severe pain. Been there. [...]
19684 22 60_Re: Update on object SYSIBM.SYSTABLESPACE.NACTIVEF with -5517_Do Park19_doparkdba@YAHOO.COM31_Thu, 27 May 2004 11:46:52 -0500428_- Hello
I can update the columns in the DB2 catalog with DBA privilege, but the user who has update and select auth cannot update the NACTIVEF in the DB2 catalog. I checked the SYSIBM.SYSCOLAUTH table. The user has update auth on the NACTIVE column, but the user doesn't have the update auth on the NACTIVEF column. I think that the user should be able to update the column if the user has update auth on the table. [...]
19707 63 60_Re: Update on object SYSIBM.SYSTABLESPACE.NACTIVEF with -55112_Brill, Steve22_Steve.Brill@ECOLAB.COM31_Thu, 27 May 2004 12:04:32 -0500533_iso-8859-1 Hi,
I am pretty sure that catalog tables are special, and that to update the columns on a catalog table, you need at a minumum update authority on each column. Obviously, if you have DBADM or SYSADM authority, that should be covered.
On the other hand, I think that it is inadvisable to let "users" update catalog tables. I assume that your user is attempting to influence the Optimizer. Perhaps I am too conservative, but that a job for a DBA, Systems Administrator, or authorized Performance Analyst. [...]
19771 28 44_What log files are needed by recover utility0_27_mamccormack@STATESTREET.COM31_Thu, 27 May 2004 14:14:11 -0400513_us-ascii We recently had an adventure with the DB2 Recover utility. A Recover Logonly job, which needed to process many archive log files, ran for 13 hours. We determined from circumstantial evidence that there was trouble recalling archive logs to dasd. We did not know which logs were actually required. The Recover utility produced no messages indicating which log files were needed. Some messages were produced with rba/lrsn ranges, but they did not appear until end of jobstep (too late to be of help). [...]
19800 19 60_Re: Update on object SYSIBM.SYSTABLESPACE.NACTIVEF with -5517_Do Park19_doparkdba@YAHOO.COM31_Thu, 27 May 2004 13:56:49 -0500741_- Hello Steve
Thank you for your update.
We ran a job with that user-id through Control-M to update the columns in the DB2 catalog after reorg some tablepsaces for Performance. This USER-ID is kind of special userid for Batch jobs.
Best Regards, Do.
--------------------------------------------------------------------------------- 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
19820 93 56_DSNT408I SQLCODE = -911, RESOURCE NAME 00000338.000000033_FCH16_tzs61x@YAHOO.COM31_Thu, 27 May 2004 12:06:17 -0700418_us-ascii To lister: I received the following -911 message. Instead of giving me the table name directly from the error message, it gave me 00000338.00000003 (which is dbid.obid). I located the table name by executing the following SELECT. My question is why DB2 not giving me the tablename directly ?
Thanks
======================================================= SQL ERROR DURING EXECUTE IMMEDIATE [...]
19914 87 48_Re: What log files are needed by recover utility12_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Thu, 27 May 2004 15:33:28 -0400305_iso-8859-1 I know of someone who wrote a program to do exactly that, figure out what archive log is needed. I have sent him a note to see if he wants to send it to you.
But in the mean time, you can reduce that amount of data you need to recover by doing some other things on a regular basis. [...]
20002 111 78_Re: [CMS-MAY BE SPAM] DSNT408I SQLCODE = -911, RESOURCE NAME 00000338.0000000314_David R Slocum22_drslocum@CMSENERGY.COM31_Thu, 27 May 2004 16:14:12 -0400324_US-ASCII Is your DSNDB06 or DSNDB01 database stopped, or your SYSDBASE or DBD01 tablespaces stopped? If so, then DB2 can't access the information that it needs to decode DBID and OBID. I comonly see this during a disaster recovery test when the catalog and directory haven't yet been fully recovered.
FCH wrote: [...]
20114 16 27_Anyone in DB2 V8 for z/OS??6_Suneel17_dba_udb@YAHOO.COM31_Thu, 27 May 2004 15:13:05 -0500605_- Hi folks,
Anyone installed or migrated to DB2 V8 on z/OS ? Appreciate, if you can share your experiences !
TIA, Suneel
--------------------------------------------------------------------------------- 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
20131 128 60_Re: DSNT408I SQLCODE = -911, RESOURCE NAME 00000338.0000000312_michael bell21_mbell11a1@VERIZON.NET31_Thu, 27 May 2004 15:43:05 -0500573_Windows-1252 DB2 uses the DBID.OBID in the skeleton cursor table and the internal DBD and everywhere else to uniquely identify a table. If possible, DB2 will translate it to database name.tablename but if there is a problem with the translation, the message will come out with DBID.OBID.
Mike Bell HLS Technologies
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of FCH Sent: Thursday, May 27, 2004 2:06 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DSNT408I SQLCODE = -911, RESOURCE NAME 00000338.00000003 [...]
20260 34 31_Re: Anyone in DB2 V8 for z/OS??19_dca95240@axcess.net19_dca95240@AXCESS.NET31_Thu, 27 May 2004 14:06:55 -0700747_us-ascii We have upgraded 8 DB2 V7 subsystems (non-production), 4 to compatibility, 4 thru compatibility to new-function mode. There were no problems. Be sure to have the SPE on V7.
Daniel Allen Serena
> --- Original Message --- >Sent: 05/27/04 13:50:15 >From: dba_udb@YAHOO.COM >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Anyone in DB2 V8 for z/OS?? > >Hi folks, > >Anyone installed or migrated to DB2 V8 on z/OS ? Appreciate, if you can >share your experiences ! > >TIA, >Suneel > >--------------------------------------------------------------------------------- >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 [...]
20295 52 44_viewing values in a bkup of syspackage table10_Derek Lund22_derek.lund@UTORONTO.CA31_Fri, 28 May 2004 00:06:51 -0400615_us-ascii Hello, we are currently having code page problems with QMF for Windows (aka Rocket Shuttle). The packages were recently bound against the host (DB2 OS/390 V7) which updated the sysibm.syspackage table for those packages (12) under the specific collection. Now we have code page and translation problems where there were none prior to the new binding of the packages. We want to bind again with the correct parameters but we do not know what the previous bind parameters were for the packages before the recent bind. In particular column I want to find out the previous value for column ENCODING_CCSID [...]
20348 18 31_Re: Anyone in DB2 V8 for z/OS??0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Fri, 28 May 2004 02:17:55 -0500544_us-ascii Hi,
What does "SPE on V7" mean?
Thanks.
Stg
--------------------------------------------------------------------------------- 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
20367 26 59_Procedure to reduce the number of extent for LOB tablespace11_Akay Sharma18_aomprakash@CSC.COM31_Fri, 28 May 2004 04:05:26 -0500504_- I have a LOB tablespace which is exceeding 90 extents. I wish to increase the primary and secondary extent size.Would someone verify or suggest the proper way of implementing this.
1) Start the tablespace in utility mode. 2) Perform quiesce. 3) Perform imagecopy on the LOB tablespace. 4) Alter primary/secondary extent sizes for the LOB tablespace and its indexes. 5) Recover the LOB tablespace. (with TOLASTCOPY syntax) 6) Rebuild all the indexes. 7) Start tablespace in read,write mode. [...]
20394 71 63_Re: Procedure to reduce the number of extent for LOB tablespace12_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Fri, 28 May 2004 06:13:42 -0400310_iso-8859-1 I believe that the way you have stated will work.
Something we used to do was to:
1. Stop the space 2. use DFS to copy off and back(if you have flashcopy or snapshot this is very fast) 3. Start the space.
This is a short term solution, until you can reallocated the space. [...]
20466 80 63_Re: Procedure to reduce the number of extent for LOB tablespace14_Paul P Packham16_ppackham@CSC.COM31_Fri, 28 May 2004 11:56:18 +0100707_US-ASCII Ashok,
You will have to Run CHECK DATA against the base table tablespace
Regards,
Paul
----------------------------------------------------------------------------------------
This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. ---------------------------------------------------------------------------------------- [...]
20547 85 48_Re: viewing values in a bkup of syspackage table13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Fri, 28 May 2004 07:30:24 -0500321_iso-8859-1 Don't bother including the OBID translations for the global temp tables -- there wouldn't be any data rows in your dataset to translate for these. Removing those should just leave you with around 8 tables. The other (and in my mind, better) option would be to run an unload utility against the imagecopy. [...]
20633 11 31_Re: Anyone in DB2 V8 for z/OS??6_Suneel17_dba_udb@YAHOO.COM31_Fri, 28 May 2004 07:39:57 -0500590_- That's special maintenance, to be applied on V7 before migrating to V8. SPE allows you to fallback to V7 if required.
--------------------------------------------------------------------------------- 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
20645 34 31_Re: Anyone in DB2 V8 for z/OS??14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Fri, 28 May 2004 09:03:29 -0400365_us-ascii The Fallback SPE is UQ81009 on PUT0310.
One should also check the DB2 V8 PSP install bucket on IBMLINK. As of this morning there were 48 PTFs.
May be a some what trival issue but the S in SPE does not stand for special ... With respect to service there is no such thing as special. SPE in fact stands for "Small Programming Enhancement" [...]
20680 71 31_Re: Anyone in DB2 V8 for z/OS??14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Fri, 28 May 2004 08:03:51 -0500481_us-ascii Of course, unlike previous releases, Version 8 now verifies that the SPE has been applied to the Version 7 system BEFORE you can even start the migration to version 8. I'm curious, on the 4 that you went to NFM with, how much of increase in disk usage did you see for your catalog? Wayne Driscoll Sr. Product Architect Quest Software http://www.quest.com/db2 wdriscoll@listserv.quest.com NOTE: All opinions are strictly my own. EMail Address in sig must be modified. [...]
20752 91 31_Re: Anyone in DB2 V8 for z/OS??16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Fri, 28 May 2004 09:06:02 -0400421_iso-8859-1 My understanding is that we should wait until the June release tapes before going into production. I am told of at least one company that have 'pulled' out of version 8 because of issues.
Robert Galambos
Compuware Senior Technical Specialist IBM Certified Solutions Expert - DB2 UDB for OS/390 V7 Database Administration robert.galambos@compuware.com [...]
20844 12 22_Re: Sideway references15_Daniel Cremieux17_c300501@YAHOO.COM31_Fri, 28 May 2004 08:18:18 -0500643_- it was discussed in IBM DB2 FAQ - You can go to DB2 for Z/OS homepage , search for "Support" link . I use My Support , it gives me all new entries in the domains i chose.
--------------------------------------------------------------------------------- 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
20857 16 28_DSMAX and affected storage .15_Daniel Cremieux17_c300501@YAHOO.COM31_Fri, 28 May 2004 09:50:31 -0500652_- Hi all ,
Can you confirm that the storage used in dbm1 for opened datasets is allocated for dsmax value , and not for the currently opened datasets value ?
Thank you
--------------------------------------------------------------------------------- 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
20874 50 29_Archive Log MODE(Quiesce) RBA0_16_DBMSUser@AOL.COM29_Fri, 28 May 2004 11:02:12 EDT398_US-ASCII Hi All, What IBM DB2 Catalog Tablespace is the RBA stored in when a ARCHIVE LOG MODE(QUIESCE) command is issued. We are attempting to automate the recovery of some tablespaces to a Archive Quiesce RBA rather than a tablespaces Quiesce RBA. Can we recover a group of tablespaces to the Archive LOG Quiesce RBA? Is this possible? Any suggestions or recommendation would be appreciated. [...]
20925 271 33_Re: Archive Log MODE(Quiesce) RBA12_Isaac Yassin20_yassini@BEZEQINT.NET31_Fri, 28 May 2004 18:11:49 +0200551_windows-1255 Hi,
In the BSDS, not in the DB2 catalog.
You can recover to any RBA. The question is not about being able to do it, but about how it affects your data integrity as the RBA given can be in the middle of a work (some COMMIT point in it).
Isaac Yassin
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of DBMSUser@AOL.COM Sent: Friday, May 28, 2004 5:02 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Archive Log MODE(Quiesce) RBA [...]
21197 74 48_Re: viewing values in a bkup of syspackage table0_32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Fri, 28 May 2004 08:30:33 -0700277_- Derek, I had to look at the backup for SYSCOLUMNS, all I did was run a unload from the image copy, did a CREATE TABLE .. LIKE.., loaded the new table from the unload file using the load card commands and then was able to query the new table. It was no problem. cliff:-) [...]
21272 84 31_Re: Anyone in DB2 V8 for z/OS??19_dca95240@axcess.net19_dca95240@AXCESS.NET31_Fri, 28 May 2004 08:55:42 -0700613_us-ascii All 8 DB2 subsystems were built from scratch. We have just started to turn these DB2 subsystems over to development. What do you mean by disk usage (space or I/O)?
Daniel Allen Serena Software > --- Original Message --- >Sent: 05/28/04 06:38:01 >From: wayne.driscoll@QUEST.COM >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: Re: Anyone in DB2 V8 for z/OS?? > >Of course, unlike previous releases, Version 8 now verifies that the SPE >has been applied to the Version 7 system BEFORE you can even start the >migration to version 8. I'm curious, on the 4 that you went to NFM >with, how much of increase [...]
21357 13 62_Is there any way to detect a transaction with x elapsed time ?12_Billy Larsen19_billarsen@YAHOO.COM31_Fri, 28 May 2004 10:57:41 -0500635_- hi all , i know that there are some tools doing it , but is there any fast way to achieve this ? If you have an IFI program for the job , i would be interested ..
--------------------------------------------------------------------------------- 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
21371 87 31_Re: Anyone in DB2 V8 for z/OS??14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Fri, 28 May 2004 11:02:17 -0500418_us-ascii Since the subsystems were built from scratch, it's a moot point. I am interested in some real world figures of the increase id disk space utilization when going to NFM, due to the increased column lengths and the like. Wayne Driscoll Sr. Product Architect Quest Software http://www.quest.com/db2 wdriscoll@listserv.quest.com NOTE: All opinions are strictly my own. EMail Address in sig must be modified. [...]
21459 19 31_Benefits of Incremental Backups0_20_jane.leyba@PCIAA.NET31_Fri, 28 May 2004 11:29:57 -0500381_us-ascii In an attempt to analyze and test the concept of incremental backups, we as an organization are trying to understand how different companies are utilizing this concept and what benefits are gained. Current testing in our shop did not result in much CPU savings but could result in quicker availability of some of the larger tables. Any thoughts to be shared on this? [...]
21479 33 66_Re: Is there any way to detect a transaction with x elapsed time ?14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Fri, 28 May 2004 12:40:47 -0400509_us-ascii One of the problems here is 'transaction' is not a DB2 term. DB2 has this concept of threads where a thread may repersent a particular connection or in some cases a combonation of a thread and a plan (program). These threads are oten reusable either by virtue of DB2 or application options ... That is a thread may process a fraction of a single user transaction or many. DB2 is not unique in this regard ... many message processing systems like IMS for example share the same processing types. [...]
21513 22 21_Edmonton, Alberta RUG10_Ian Davies31_ian.davies@CGI.HEALTH.GOV.AB.CA31_Fri, 28 May 2004 11:41:09 -0500712_- Hi Folks,
Anyone in the Edmonton, Alberta area interested in joining or helping to form a DB2 RUG?
Please respond to me directly if you are interested.
Cheers, Ian.
Ian Davies Database Administrator CGI Edmonton
--------------------------------------------------------------------------------- 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
21536 35 38_Wanted/DB2 Connect or Websphere Person12_Sutlic, Ruth18_Ruth.Sutlic@53.COM31_Fri, 28 May 2004 12:55:43 -0400396_iso-8859-1 Fifth Third Bank in Cincinnati, Ohio has an opening that can go either way. The Websphere team is looking for help. The DB2 team is looking for a way to manage our DB2 Connectivity. We need help managing our gateway environments. We had a posting on Monster.com, but I can't seem to find out the specifics. I'm only getting out the message because we only had one resume to date. [...]
21572 307 22_DBA opening in Chicago20_Patterson, Michael T25_mtpatterson@CPS.K12.IL.US31_Fri, 28 May 2004 13:09:50 -0500774_us-ascii I am looking for someone in living in the City of Chicago with the following skill sets. Please e-mail resumes to: mtpatterson@cps.k12.il.us
DESCRIPTION
Job Description: Database Administrator
Requirements (include but are not limited to):
Candidate must have strong IBM mainframe background and understand technical infrastructure components required to support application environments. Candidate will be highly motivated DB2 DBA responsible for revamping the corporate data structure to drive down cost and improve efficiency. The ideal candidate must be able to analyze and understand existing architecture and data models with a vision for improvement that builds a foundation for next generation architecture. [...]
21880 12 25_"Native DB2" vs "SQL DB2"4_Bill28_Bill.Happel@HARTFORDLIFE.COM31_Fri, 28 May 2004 13:37:05 -0500631_- I have an Architect ask me if our Cobol DB2 programs were written in 'native db2' or 'sql db2'? How do I find out? Can any one explain what the difference is?
--------------------------------------------------------------------------------- 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
21893 79 29_Re: "Native DB2" vs "SQL DB2"0_28_BILL.GALLAGHER@PHOENIXWM.COM31_Fri, 28 May 2004 15:32:26 -0400379_us-ascii Sounds to me like your Architect doesn't understand what he's looking for, because his question doesn't make any sense (to me, at least).
I'd ask him to restate his question so that you can understand what kind of information he wants, rather than waste a lot of time trying to figure out what he thinks the difference is between "native db2" and "sql db2". [...]
21973 69 29_Re: "Native DB2" vs "SQL DB2"12_michael bell21_mbell11a1@VERIZON.NET31_Fri, 28 May 2004 14:53:43 -0500612_iso-8859-1 it sounds as if they are asking about precompiler option SQL This only affects precompile - the execution is exactly the same. SQLFLAG can also affect some of these issues.
SQL(ALL|DB2) | Indicates whether the source contains SQL statements other | | | than those recognized by DB2 for OS/390 and z/OS. | SQLFLAG(IBM|STD | Specifies the standard used to check the syntax of SQL | | [(ssname | statements. When statements deviate from the standard, the | | [,qualifier])]) | SQL statement processor writes informational messages | | | (flags) to the output listing. The SQLFLAG option is | [...]
22043 299 29_Re: "Native DB2" vs "SQL DB2"15_Pedro Luz Cunha22_pedro.cunha@UFO.COM.BR31_Fri, 28 May 2004 16:52:13 -0300398_iso-8859-1 Hello Bill,
I think the Architect is looking for what is defined in DB2 installation parameters for application programming. IF in panel DSNTIP4 standard sql language was defined as YES DB2 will use the portions of the 1992 ANSI SQL standard implemented by DB2, and if you specify NO, programs are written in accordance with the SQL language defined by DB2 SQL standard. []s [...]
22343 33 29_Re: "Native DB2" vs "SQL DB2"17_Chris Worthington20_cworthi@AGRIBANK.COM31_Fri, 28 May 2004 14:51:21 -0500725_- On Fri, 28 May 2004 13:37:05 -0500, Bill wrote:
>I have an Architect ask me if our Cobol DB2 programs were written >in 'native db2' or 'sql db2'? How do I find out? Can any one explain what >the difference is? > >--------------------------------------------------------------------------- ------ >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 [...]
22377 124 29_Re: "Native DB2" vs "SQL DB2"14_David R Slocum22_drslocum@CMSENERGY.COM31_Fri, 28 May 2004 15:54:52 -0400305_US-ASCII I'm not sure if this is what he's looking for or not, but in the DSNHDECP module, the STDSQL parameter lets you choose between standard "1992 ANSI SQL" or "native DB2 SQL". This isn't a compile option, and it applies to all programming languages you may use to talk to DB2 (not just COBOL). [...]
22502 173 42_Re: Wanted/DB2 Connect or Websphere Person24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Fri, 28 May 2004 16:07:03 -0400649_iso-8859-1
Ruth, Consider posting your opening at http://www.justdb2jobs.com
Frank
On Fri, 28 May 2004 12:55:43 -0400, Sutlic, Ruth wrote:
>Fifth Third Bank in Cincinnati, Ohio has an opening that can go either way. >The Websphere team is looking for help. The DB2 team is looking for a way to >manage our DB2 Connectivity. We need help managing our gateway >environments. We had a posting on Monster.com, but I can't seem to find out >the specifics. I'm only getting out the message because we only had one >resume to date. > >Interested parties may contact Dave Snyder at 513/534-0984. His email >address [...]
22676 17 29_Re: "Native DB2" vs "SQL DB2"14_Gary Bernhardt29_gary.bernhardt@YELLOWCORP.COM31_Fri, 28 May 2004 15:03:14 -0500486_- I've worked with other databases that had a "native" and "SQL" method of navigation. I suspect this is what he is asking about. If this is the case, DB2 does not support a "native call." All data access is through SQL.
The advantages of native is that it was faster because it bypassed the optimizer. But with the speed, there is a trade off in flexibility. You had to understand the data and how to navigate to it. In DB2, that knowledge is less important in most cases. [...]
22694 142 29_Re: "Native DB2" vs "SQL DB2"15_Philip Sevetson49_Phil_Sevetson/CISD/WAKEFERN.WAKEFERN@WAKEFERN.COM31_Fri, 28 May 2004 16:03:08 -0400421_us-ascii If your architect was asking whether you were writing to the ANSI '92 SQL Standard (and at what level of compliance; more than one level exists), then the question would make sense -- not using the DB2 "extensions" to ANSI '92 would make the SQL code more portable.
And _don't_ ask me to explain what the differences are -- we're at the limit of what I remember about the whole thing right here. :-) [...]
22837 132 30_Re: "Native DB2" vs. "SQL DB2"13_Solecki, Rohn19_Rohn.Solecki@GWL.CA31_Fri, 28 May 2004 15:57:20 -0500560_iso-8859-1 The DB2 "SQL Reference for Cross-Platform Development" may expand a little on Phil's point.
This book is intended for programmers who want to write portable applications using SQL that is common to the DB2 UDB relational database products and the SQL 1999 Core standard.1 DB2 UDB SQL is consistent with the SQL 1999 Core standard. DB2 UDB SQL also provides functional extensions to the SQL 1999 Core standard. For example, many of the scalar functions defined in this book are extensions to the SQL 1999 Core standard. [...]
22970 74 35_Re: Benefits of Incremental Backups12_Brill, Steve22_Steve.Brill@ECOLAB.COM31_Fri, 28 May 2004 16:04:13 -0500567_- To answer a question with a question, what are you currently doing for backup and recovery planning? Depending upon your environment, and the amount and kind of activity, you may be limited in what you can and can't do. In general, I agree in your assessment that you can have "quicker availability" by using Incremental image copies. But, if your tables are highly volatile, then you may not gain any more than if you use full image copies. I have seen some thresholds published that suggest that you should use FICs instead of IICs when you update 5% or 10% [...]