1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l March 2001, week 5
2 117 22_Re: Suggestion Please.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 29 Mar 2001 11:58:55 +0530409_iso-8859-1 I agree with James and Rick. The actual purpose is not understood. What is the need of CHG Data Capture(CDC) and it looks it is home grown product. If the need is only to keep the data which are purged so that it can be reloaded if required, then it is better to do this prior to deletion. If at all this is a planned process then it can be planned in a better manner, infact the best manner. [...]
120 16 27_Re: question on outer join.15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 29 Mar 2001 00:33:22 -0600326_- Tim
I must admit, I don't remember what this enhancement was all about. What I do know is that the zparm OJPERFEH is NO at our site. And I saw, looking at many, many explains a matching index scan with matching columns greater than 0 for the left tables of an outer join having predicates in a WHERE- condition. [...]
137 21 27_Re: question on outer join.15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 29 Mar 2001 00:42:01 -0600372_- Terry
Well, as I just read Richard's response, that the whole discussion was about predicates not used for index access, I think that wasn't quite clear from the beginning of this discussion or I didn't notice that (maybe my English isn't that good). But right now, I am a little bit confused. To summarize the discussion (and correct me, if I'm not right): [...]
159 43 25_UDB Solaris and seq.files10_john clark20_pak02449@PIXIE.CO.ZA31_Thu, 29 Mar 2001 09:20:30 +0530380_us-ascii Hi All,
Please forgive me if this is a little OT, but a recent question has piqued my interest.
Given that my Unix expertise is non-existent, this may seem a litte naive, but here goes.
In many instances the usage of sequential files produced by TIAUL or fast unload has proved to be very useful to increase throughput by an order of magnitude. [...]
203 15 52_Re: Outer Join Query Use of literals in ON CLAUSE???15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 29 Mar 2001 01:22:36 -0600293_- David
But be aware, the results could be very strange, if you use a literal in an ON clause in an outer join instead in a WHERE clause. Remember: All rows of the outer table will be in the result set which do not satisfy the join condition (but do satisfy the WHERE conditions). [...]
219 46 33_Re: More Space needed after Reorg16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 29 Mar 2001 14:15:16 +0530610_- As said by others PCTFREE, compression(to build the new dictionary) could be the reason. Freepage is zero in your case otherwise that could also cause increase in space.
Regards, Sanjeev
> -----Original Message----- > From: Martin Berchtold [SMTP:Martin.Berchtold@SYSTEMATICS.DE] > Sent: Wednesday, March 28, 2001 8:15 PM > To: DB2-L@RYCI.COM > Subject: More Space needed after Reorg > > Hi List, > > a short question. > Why do I need more space for my tablespaces, when I have reorganized the > ts. > For example: > My ts needs 33000 tracks. PCTFREE is default (5), Freepage = 0. > There [...]
266 42 30_Re: NPAGES and tablespace scan16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 29 Mar 2001 14:25:42 +0530307_- Its discussed a week or 2 back. I think there is nothing like official documentation available. It is just the personal opinions of some experts mentioned in their articles, white papers and writings. Do not ask me the writer's name. I really do not remember those. This is also my personal opinion, [...]
309 77 30_Re: NPAGES and tablespace scan15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Thu, 29 Mar 2001 11:14:07 +0200695_iso-8859-1 Sanjeev,
I guess only Guy Lohman (mr. Optimizer) from the IBM Sillicon Valley Lab might answer this one..
Kind regards,
Jaap.
-----Oorspronkelijk bericht----- Van: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] Verzonden: donderdag 29 maart 2001 10:56 Aan: DB2-L@RYCI.COM Onderwerp: Re: NPAGES and tablespace scan
Its discussed a week or 2 back. I think there is nothing like official documentation available. It is just the personal opinions of some experts mentioned in their articles, white papers and writings. Do not ask me the writer's name. I really do not remember those. This is also my personal opinion, [...]
387 20 33_Re: More Space needed after Reorg16_Martin Berchtold31_Martin.Berchtold@SYSTEMATICS.DE31_Thu, 29 Mar 2001 03:04:32 -0600506_- Hi Les,
no, the ts is not compressed and we don't use a special edproc. My guess is (was) nearly identical to all the postings. (Thanks to all of you). But I was a little confused, because IBM requested traces for this problem.
Thanks
Martin
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
408 98 30_Re: NPAGES and tablespace scan16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 29 Mar 2001 16:25:17 +0530647_- Jaap,
You mentioned the big one(Mr. Optimizer) but you are absolutely correct, it can be answered by people like them only.
Regards, Sanjeev
> -----Original Message----- > From: Slot, JP (Jaap) [SMTP:J.P.Slot@RF.RABOBANK.NL] > Sent: Thursday, March 29, 2001 2:44 PM > To: DB2-L@RYCI.COM > Subject: Re: NPAGES and tablespace scan > > Sanjeev, > > I guess only Guy Lohman (mr. Optimizer) from the IBM Sillicon Valley Lab > might answer this one.. > > Kind regards, > > Jaap. > > > > -----Oorspronkelijk bericht----- > Van: S, Sanjeev (CTS) [mailto:SSanjeev@CAL.COGNIZANT.COM] > Verzonden: donderdag 29 maart 2001 10:56 > [...]
507 43 33_Re: More Space needed after Reorg16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 29 Mar 2001 16:27:53 +0530622_- Sometimes the small questions when unexpectedly asked looks bit comlicated and tough. That is the only thing which might have happend in your case. I can surely force someone to re-think by asking "What is 2+2 = ??".......just joking, !!
That is all the reason(PCTFREE) looks to me.
Regards, Sanjeev
> -----Original Message----- > From: Martin Berchtold [SMTP:Martin.Berchtold@SYSTEMATICS.DE] > Sent: Thursday, March 29, 2001 2:35 PM > To: DB2-L@RYCI.COM > Subject: Re: More Space needed after Reorg > > Hi Les, > > no, the ts is not compressed and we don't use a special edproc. > My guess is [...]
551 83 52_Re: Outer Join Query Use of literals in ON CLAUSE???16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Thu, 29 Mar 2001 11:25:43 +0000450_us-ascii David,
that is allowed from v6, actually i think there may have been an APAR for v5.
Ciao!
Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.
Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 email: aurora_dellanno@candle.com
1 Archipelago Lyon Way Frimley Camberley Surrey RH16 7ER UK
* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER [...]
635 24 27_More and more list prefetch15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 29 Mar 2001 05:45:01 -0600600_- Hello list
I'm bit helpless. Explain for a given query shows list prefetch in the test environment and sequential prefetch in production. Most of you would so: so what, that's normal because test-tables are smaller than production tables. But, I copied all RUNSTATS-values for the table and indexes in question, which the optimizer takes into account (when I say all, I mean all, even the COLDIST-values). The size of the RID-pool is the same for test and production, bufferpools are not. The maintenance level is the same for both systems. So my problem is: why do I get list prefetch [...]
660 175 27_Re: question on outer join.18_Pudukotai, Nagaraj35_nagaraj.pudukotai@BLR.HPSGLOBAL.COM31_Thu, 29 Mar 2001 17:19:06 +0500595_iso-8859-1 Hi Terry
I did not quite understand your paragraph below. What is "single preserved row table"?. Also can you elaborate on " A WHERE predicate will cause a join simplification if applied to a table which can supply NULLs in the join".
V6 will "push-down" any local boolean term predicate that apply to a single preserved row table (ie. not NULL supplying table or not the right table of a left join). This applies to "pure" preserved row tables only (left table of a left join, right table of a right join). A WHERE predicate (which does not include [...]
836 51 34_Re: Speeding up the DSNTIAUL fetch11_David Nance16_DWNance@FHSC.COM31_Thu, 29 Mar 2001 07:33:59 -0500531_US-ASCII Warren, We'll need a little more info on your query. What does the explain show you? What are the indexes on tables, are you giving the query qualifying rows?
Dave Nance First Health Services, Corp. (804)527-6841
>>> whomer@CSC.COM.AU 03/28/01 06:11PM >>> Hi experts,
I am trying to perform a conversion of a table (MBMSV) with approx 61 million rows (2.5Gb, 20 partitions) by attempting a left outer join with a small (300 rows) (MBWOF) code translation table using DSNTIAUL to unload the data. [...]
888 137 62_DDF termination on DB2 startup after Migration to DB2 V6 OS39012_Aedan Molony20_Aedan.Molony@PSIR.IE31_Thu, 29 Mar 2001 12:58:19 +0100680_iso-8859-1
Has anyone migrated to DB2 V6 and on initial start-up DDF terminates with the following messages? DSNL007I & DSNL008I. One message that normally appears is DSNL519I. This did not appear for TCPPORT.
IBM had no hits...and we've no dump.
Thanks in advance.
Regards,
Aedan
-------------------------------------------- Name: Aedan Molony Company: PS Information Resource (Ireland) Ltd. Address: Computer Centre, Cabinteely, Dublin 18,IRELAND Phone: +353-1-2177000 Fax: +353-1-2177010 X.400: G=Aedan S=Molony C=US A=ATTMAIL P=Bank Of Ireland Internet: Aedan.Molony@psir.ie -------------------------------------------- [...]
1026 124 56_Re: How to prevent remount request of a cartridge in ATL11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Thu, 29 Mar 2001 07:48:57 -0500423_iso-8859-1 It sounds to me like there's a system-type problem here. I have never in my 20 years in data processing specified included a unit parameter when I'm reading a cataloged dataset. That's the whole point of the catalog -- so you don't have to know what device it was created on (3480, 3490, ATL, disk, etc.). I think you should talk to your OS/390 guys because you shouldn't be the only one with this problem. [...]
1151 70 34_Re: Speeding up the DSNTIAUL fetch18_Pudukotai, Nagaraj35_nagaraj.pudukotai@BLR.HPSGLOBAL.COM31_Thu, 29 Mar 2001 17:58:44 +0500346_- I asked a question about left outer join yesterday and Terry mentioned that the predicates on the WHERE clause will be applied after the join in V5 without that APAR and versions below 5. So I think in your case (because you are at ver 4) it is trying to do a join of 61 M rows with 300 rows before applying the predicates on WHERE clause [...]
1222 26 30_Re: NPAGES and tablespace scan10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 29 Mar 2001 14:54:52 +0200411_us-ascii As far as it can be useful, I tried many configuration using DB2 estimator and I never had an access path using seq.
prefetch if an index (unique, non-unique, clusterratio > 90 %, <90%, non-clustered etc) exists, even with very few rows (and
pages) .
It seems that the cost model prefers to use an index (if exists) as the average path length for async I/O is 42240, for sync [...]
1249 24 23_DB2 Connect PE Question13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Thu, 29 Mar 2001 06:45:20 -0600347_- We have a person using DB2 Connect PE V6.1 attaching to OS/390 DB2 V6. They recently had a Visual Basic application installed on their workstation and were unable to get it to work. Every time they executed it, they received a message that said "{Microsoft}{ODBC Driver Manager} Data source name not found and no default driver specified." [...]
1274 22 66_Re: DDF termination on DB2 startup after Migration to DB2 V6 OS39010_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 29 Mar 2001 15:17:46 +0200471_us-ascii I find this APAR (for V5 & V6)
PQ29845: WHEN STOPPING DDF USER GETS DSNL005I DDF STOPPING, DSNL007I DDF ABNORMAL TERMINATION, DSNL008I DDF ABNORMAL TERMINATION COMPLETE
HTH
Max Scarpa
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
1297 44 27_Re: DB2 Connect PE Question8_Rob Lake29_robert.q.lake@PAC.ERICSSON.SE31_Thu, 29 Mar 2001 07:11:15 -0600397_- The CCA can register a database for ODBC. (You can then view the source through the ODBC icon in the windows control panel). The button is on the database properties window on which an ODBC source can be registered. My guess is that this was marked 'yes' on the way in and then hitting the ok's on the way out caused the source to be registered and then the application to work. [...]
1342 51 31_Re: More and more list prefetch11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Thu, 29 Mar 2001 08:38:05 -0500396_ISO-8859-1 Walter
Make sure that the index CLUSTERRATIO values were correctly copied. The Optimizer will go for Sequential Prefetch if CLUSTERRATIO >= 80%, otherwise it goes for List Prefetch. If CLUSTERRATIO is the same, then different CPU power is the most likely reason for the change.
Regards Mike Turner Email: michael_turner@compuserve.com Web: www.michael-turner.ltd.uk [...]
1394 45 30_Re: NPAGES and tablespace scan11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Thu, 29 Mar 2001 08:38:06 -0500450_ISO-8859-1 Madeleine
I believe that 10 is the number. It is sort of implied in the V6 Admin Guide discussion of the new NPGTHRSH parameter:
"Recommendation: Before you use NPGTHRSH,be aware that in some cases, matching index access can be more costly than a table space scan or nonmatching index access. Specify a small value for NPGTHRSH (10 or less). That limits the number of tables for which DB2 favors matching index access." [...]
1440 17 27_Re: DB2 Connect PE Question13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Thu, 29 Mar 2001 07:49:23 -0600284_- Good point. I had not thought about it not being registered as ODBC.
Even though I am "responsible" for the software, I am not allowed to be involved in the installation itself or how and when it is done. I would be interested in knowing if that is true at other sites. [...]
1458 62 42_Re: DB2 UDB V6.1 fixpack inquiry question?14_Scott Saunders20_ssaunders@SIEBEL.COM31_Thu, 29 Mar 2001 06:23:40 -0800499_iso-8859-1 Bob; Use db2level to find the basic info such as: DB21085I Instance xyz uses DB2 code release "SQL05020" with level identifier "020A0103" and informational tokens "DB2 v5.2.0.39", "c000110" and "U469674"
Then go to the IBM ftp site, ftp.software.ibm.com then drill down to your appropriate install and language, for example, /ps/products/db2/fixes/english-us/db2v52 and find a file with a name something like FP12_U469674 which says this db2level is for fixpack 12 of DB2 5.2 [...]
1521 53 22_FYI: CPU per I/O ratio5_Chris17_isatcjk@YAHOO.COM31_Thu, 29 Mar 2001 06:34:35 -0800332_us-ascii Hi Everybody,
FYI, I did a little test to see how much CPU is saved for each Fetch if the page is already in the bufferpool.
For Prefetch, you save about 10% of the CPU cost.
For Sync I/O, you save about 20%.
Does anybody see different numbers?
I suppose it depends on the hardware. [...]
1575 14 31_Re: More and more list prefetch0_26_truman.g.brown@VERIZON.COM31_Thu, 29 Mar 2001 09:40:01 -0500339_us-ascii Are the test and production subsystems both at the same maintenance level?
George
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
1590 76 17_Re: START command12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 29 Mar 2001 06:53:53 -0800568_us-ascii many automation products have the ability to intercept any console command, and either filter, or change it.
You could intercept all db2 commands based on subsystem recognition characters, and if it came from a specific user, either change the command to RW or RO, or send that user a nasty message. I've found that messages that start with JES2 CATASTROPHIC ERROR--COND CODE (IMA1D10T) - PLEASE CALL TECH SUPPORT IMMEDATELY seem to get their attention...especially when they can't find the code in the manual and finally realize what it means... [...]
1667 61 36_Re: More on triggers / control table5_Chris17_isatcjk@YAHOO.COM31_Thu, 29 Mar 2001 06:59:09 -0800657_us-ascii Wouldn't it save more CPU if the trigger is dropped before batch and then re-create it afterwards?
Then the trigger wouldn't have to look to the control table.
Granted you'd have to suffer the auto-rebinds, which might change the accesspath.
Chris
--- Steve Grimes wrote: > Hello, > > We do the admittedly inefficient technique of using Triggers to log > inserts/updates/deletes to all of our critical tables. We have a "global > audit > switch" switch in a control table which every trigger checks in its WHEN > condition before proceeding with creating the audit table entries. > [...]
1729 75 24_Stored Procedure Problem18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 29 Mar 2001 10:02:28 -0500364_iso-8859-1 Here is one for the group. Below is a stored procedure that I created via the stored procedure builder. I am getting the following error on the INSERT statement: EMSUDBR.TEST2: 26: [IBM][CLI Driver][DB2/SUN] SQL0408N A value is not compatible with the data type of its assignment target. Target name is "KEY_COLUMN". LINE NUMBER=26. SQLSTATE=42821 [...]
1805 10 31_Re: More and more list prefetch15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 29 Mar 2001 08:51:52 -0600247_- Yes
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
1816 88 28_Re: Stored Procedure Problem11_David Hayes19_Dave.Hayes@MSDW.COM31_Thu, 29 Mar 2001 16:28:05 +0100607_us-ascii Peter
You appear to have quotes around the variable name which I would think is leading DB2 to believe that you are inserting character data. The 0 (zero) is not quoted and so inserts correctly. HTH Dave Hayes
"Krawetzky, Peter J" wrote:
> Here is one for the group. Below is a stored procedure that I created via > the stored procedure builder. I am getting the following error on the > INSERT statement: > EMSUDBR.TEST2: 26: [IBM][CLI Driver][DB2/SUN] SQL0408N A value is not > compatible with the data type of its assignment target. Target name is > "KEY_COLUMN". LINE [...]
1905 12 14_DB2 Connect PE12_Phil Gunning24_philip.gunning@QUEST.COM31_Thu, 29 Mar 2001 09:32:41 -0600461_- Shauna, The CCA is used to catalog a database. So it creates an entry in the database and node directory, plus registers the database as an ODBC data source. The directories created are sqlndodir and slqdbdir. ---Phil
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
1918 52 38_How many subscribers are on this list?16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Thu, 29 Mar 2001 07:57:58 -0800367_iso-8859-1 How many subscribers are on this list? I'm curious to know how many people throughout the world are getting their mailboxes unnecessarily filled with:
"Me too!" - Send the message off-list.
"This is a test!" - Test by sending a real message when you have one.
"Let's play a trivia game!" - Over 100 responses on a Monday morning. [...]
1971 26 14_DB2 Connect PE12_Phil Gunning24_philip.gunning@QUEST.COM31_Thu, 29 Mar 2001 09:52:17 -0600570_- Shauna, You ask a good question, and I am sure you will get many answers. My experience has been that the DBA group needs to own the software, install and test new releases, train users, and develop a roll out plan in conjuntion with the Network/PC folks and applications. We used to prepare the roll-out documentation and response files, etc. The Network/PC group did some troubleshooting but to really know what to look for you need to have knowledge in the PC and DDF area. Need to be able to test connections, determine where errors are occuring, know if the [...]
1998 158 28_Re: Stored Procedure Problem18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 29 Mar 2001 11:11:32 -0500946_iso-8859-1 I had tried it without the quotes and received this error:
EMSUDBR.TEST2 - Create stored procedure returns -104.
EMSUDBR.TEST2: 36: [IBM][CLI Driver][DB2/SUN] SQL0104N An unexpected token ":" was found following "ES ( ". Expected tokens may include: "". LINE NUMBER=36. SQLSTATE=42601
CREATE PROCEDURE EMSUDBR.Test2 ( IN whichQuery int, IN KEY_COL1 int, IN DATA_COL1 varchar(30), IN DATA_COL2 varchar(10), IN DATA_COL3 varchar(10) ) RESULT SETS 1 LANGUAGE SQL ------------------------------------------------------------------------ -- SQL Stored Procedure ------------------------------------------------------------------------ P1: BEGIN DECLARE v_keycol INT DEFAULT 0; -- Declare cursor DECLARE cursor1 CURSOR WITH RETURN FOR SELECT EMSUDBR.TEST001.KEY_COLUMN AS KEY_COLUMN, EMSUDBR.TEST001.DATA_COLUMN1 AS DATA_COLUMN1, EMSUDBR.TEST001.DATA_COLUMN2 AS DATA_COLUMN2, EMSUDBR.TEST001.DATA_COLUMN3 AS [...]
2157 66 42_Re: How many subscribers are on this list?18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 29 Mar 2001 11:12:16 -0500495_iso-8859-1 2707. It tells you how many people are subscribed in a reply to your email after you send a message to the list...
-----Original Message----- From: Provenzola, Tony [mailto:Tony.Provenzola@NIKE.COM] Sent: Thursday, March 29, 2001 10:58 AM To: DB2-L@RYCI.COM Subject: How many subscribers are on this list?
How many subscribers are on this list? I'm curious to know how many people throughout the world are getting their mailboxes unnecessarily filled with: [...]
2224 71 52_Re: Outer Join Query Use of literals in ON CLAUSE???14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Thu, 29 Mar 2001 10:21:32 -0600357_iso-8859-1 Walter,
Please explain your statement. Perhaps you could provide an example of where the results would be different in the case you mentioned. I mean, how can the following produce different results?
TABA.col2 is not in any index.
SELECT whatever FROM TABA LEFT OUTER TABB ON TABA.col1 = TABB.col1 AND TABA.col2 = '123' [...]
2296 117 17_Re: START command12_Le BD Nguyen20_lnguyen@AHOLDUSA.COM31_Thu, 29 Mar 2001 11:31:49 -0500379_us-ascii Thank you Tim and Sanjeev for your responses.
Tim, when you are refering to automation products, is it product like Netview or Control-O etc... ? If so, can you send me some examples of how you would set up the automation product to intercept console command? It may sound trivial but excuse me for my lack of knowledge. BTW, I like the eye-catcher message. [...]
2414 84 31_Re: Tony Provenzola - I Concur!16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM31_Thu, 29 Mar 2001 12:09:17 -0500476_us-ascii I vehemently concur w/ Mr. Provenzola's note!
This is, supposedly, a listserve for PROFESSIONAL Information Technology people!
Remember, time is money and the more time we all spend reading/writing frivolous and nonsensical notes, the more money we waste on non-productive endeavors!
If people have something obtuse to say, either say it to yourself or send it directly to a given individual (i.e., NOT the entire DB2 listserve community!). [...]
2499 110 31_Re: Tony Provenzola - I Concur!24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Thu, 29 Mar 2001 12:29:29 -0800647_us-ascii Yo Rich, Isn't this a "Me too" that's supposed to go off-list to Tony? Human nature is sooooooooooo hard to overcome.
Frank
On Thu, 29 Mar 2001 12:09:17 -0500, RICHARD E MOLERA wrote:
>I vehemently concur w/ Mr. Provenzola's note! > >This is, supposedly, a listserve for PROFESSIONAL Information Technology >people! > >Remember, time is money and the more time we all spend reading/writing frivolous >and nonsensical notes, the more money we waste on non-productive endeavors! > >If people have something obtuse to say, either say it to yourself or send it >directly to a given individual (i.e., NOT the [...]
2610 109 30_Re: NPAGES and tablespace scan14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Thu, 29 Mar 2001 09:27:58 -0800528_iso-8859-1 Personally, I wouldn't expect to get any "official" word on this. IBM has, rightly so (in my opinion), kept details on the optimizer very close. Considering that a true cost-based optimizer is one of DB2's (many) strengths when compared to Oracle and others, I think these details are best left guarded (However, as a Product Developer writing DB2 tools, I sometimes disagree with this on occasion) Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...]
2720 46 27_Re: DB2 Connect PE Question13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Thu, 29 Mar 2001 09:51:23 -0800567_iso-8859-1 Shauna, my situation is similar to yours. Our Desktop Services group has most PC software installation locked down tight (if you're privileged to have administrator rights to your own workstation you can install stuff, but they reserve the right to wipe it out for you). I haven't yet had to install a new release of the PC software I'm responsible for, so I don't know what kind of hoops I'll have to jump through, but when it breaks I know who they are going to call. All software rollouts are scripted and occur on days when you've been working on [...]
2767 25 32_V5 to V6 - Host variable problem14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 29 Mar 2001 13:10:15 -0500392_iso-8859-1 One of our programmers here found out that in V6 the following statement generates illegal host variable error on COBOL precompile:
select bla,bla,bla from table1 where col1 in (:HOSTVAR);
The error is DSNH312I undefined or unusable host variable.
The same statement works on V5. Has anyone encountered a similar problem and what have you done to fix it. [...]
2793 20 54_CPU Accounting Overhead for DB2 Accounting Class 2 & 310_King C. Au15_King_Au@GAP.COM31_Thu, 29 Mar 2001 10:10:18 -0800528_us-ascii Would like to find out if anyone has some ball park numbers for DB2 accounting class 2 & 3 CPU overhead. Our Capacity Planning group are running some STROBE reports on a few large production jobs and the STROBE reports have some suggestions for tuning opportunity, one of them is to ask DB2 to turn off DB2 Accounting Class 2 & 3. We are running DB2 Version 5 and I thought Accounting Class 2 & 3 were relatively expensive before DB2 Version 4. Any information will be very much appreciated. King Au The Gap, Inc. [...]
2814 50 36_Re: V5 to V6 - Host variable problem12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Thu, 29 Mar 2001 13:33:34 -0500577_us-ascii Have you looked on IBMLINK? There are afew APARS there that may address your error.
Manas Dasgupta @RYCI.COM> on 03/29/2001 01:10:15 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc:
Subject: V5 to V6 - Host variable problem
One of our programmers here found out that in V6 the following statement generates illegal host variable error on COBOL precompile: [...]
2865 73 36_Re: V5 to V6 - Host variable problem10_Chris Kern23_Chris_Kern@VANGUARD.COM31_Thu, 29 Mar 2001 13:46:44 -0500330_us-ascii Is the HOSTVAR in a COBOL copybook COPY statement, SQL INCLUDE, or coded in W-S? If the HOSTVAR is in a COBOL copybook and being statment brought in via the COBOL copy statement, then you would get the problem you mentioned because the precompiler does not expand copybooks via the COPY statement. Just a thought... [...]
2939 101 27_Re: question on outer join.9_Birk, Tim17_BirkT@DIEBOLD.COM31_Thu, 29 Mar 2001 13:47:15 -0500536_ISO-8859-1 I know it's not particularly important to this discussion, but we've had NTEs since V4.
-----Original Message----- From: Terry Purcell [mailto:Terry_Purcell@YLASSOC.COM] Sent: Wednesday, March 28, 2001 6:50 PM To: DB2-L@RYCI.COM Subject: Re: question on outer join.
Walter,
I feel I must reply since you appear to have some issues with my response.
Firstly, this is a discussion on OUTER JOINs. I did not say that WHERE clause predicates are evaluated after the join for INNER JOINs. [...]
3041 146 82_Re: Parallel jobs running longer then same jobs run one at a time DB2V6 for OS/39015_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 29 Mar 2001 13:05:41 -0600599_- The explain for that particular statement (There are 43 altogether.) indicates index access matching on two columns and no sorts. Nothing peculiar about that. I can't tell if a RID Pool failure occurred or not because there are no traces turned on for that subsystem.
One person asked whether there was an input driver file driving the queries. Also, several questioned the use of an NPI when each job is supposed to access only one partition. The explanation I got from the programmer is that a (some?) query accesses another table that is in a different cluster order. This (these?) [...]
3188 82 36_Re: V5 to V6 - Host variable problem14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 29 Mar 2001 14:10:30 -0500488_iso-8859-1 Jeff / Chris:
Thanks, Can't find any hits on this error on the IBM APAR page.
And HOSTVAR is in working storage ... and it works in V5.
Thanks, Manas.
===========================================
Jeff Frazier on 03/29/2001 01:33:34 PM Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: Chris Kern/IT/VGI) Subject: Re: V5 to V6 - Host variable problem [...]
3271 30 62_WEBCAST: IBM DATABASE TOOLSET - LATEST ADDITIONS TO THE FAMILY13_Morrill, John12_JohnM@VP.NET31_Thu, 29 Mar 2001 12:36:21 -0700675_iso-8859-1 This is a extract from the DB2 Interested Parties newsletter.
WEBCAST: IBM DATABASE TOOLSET - LATEST ADDITIONS TO THE FAMILY
On Thursday, March 29 at 1:00pm Easter U.S. Time, there will be a Webcast, "IBM Database Toolset - Latest Additions to the Family - Update". Registration is now available at http://webevents.broadcast.com/ibm/dmt_update/home.asp. (Replay will be available until September.) This broadcast will review the IMS and DB2 tools that are included in this recent announcement. Also covered will be the DB2 Utilities Suite which includes all the DB2 utilities in a single package, as well as the new pricing model. The speakers [...]
3302 24 35_Any problems with LARGE tablespaces14_Mark McCormack27_mamccormack@STATESTREET.COM31_Thu, 29 Mar 2001 14:52:09 -0500430_us-ascii We will be building new partitioned tablespaces under DB2 for OS/390 v6. I am tempted by the new v6 feature to specify either LARGE or DSSIZE 4G. Has anyone had a bad experience with this ?
It is unlikely that we will need more than 64 partitions. I simply like the idea of a 4Gb upper limit (instead of 2 Gb or 1 Gb if numparts > 15 or > 32). I assume that there is no practical difference if numparts < 16. [...]
3327 27 62_Repost: Vote for DB2 in JDJ Reader's Choice Awards 2001 Survey13_Morrill, John12_JohnM@VP.NET31_Thu, 29 Mar 2001 12:52:26 -0700522_- Dear DB2 Interested Parties,
Java Developer's Journal (JDJ) is currently conducting an online survey for their "2001 Reader's Choice Awards". "Best Enterprise Database" (a new category this year) is category 13 (of 23 categories).
Please go to the JDJ site and cast your vote: http://www2.sys-con.com/java/readerschoice2001/nominationformbpa.cfm
Thank you, Mike IBM Silicon Valley Lab - 555 Bailey Avenue - San Jose, CA 95141 Phone: 408 463-4105, Fax: 408 463-4633 Internet: dbswift@us.ibm.com [...]
3355 47 39_Re: Any problems with LARGE tablespaces16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 29 Mar 2001 15:06:38 -0500470_iso-8859-1 If you are using non-IBM utilities, contact your vendor. At least one vendor product has had problems with large tablespaces. The vendor has shipped a fix but we have not yet tested the fix.
regards,
eric pearson NS ITO Database Support
-----Original Message----- From: Mark McCormack [mailto:mamccormack@STATESTREET.COM] Sent: Thursday, March 29, 2001 2:52 PM To: DB2-L@RYCI.COM Subject: Any problems with LARGE tablespaces [...]
3403 51 39_Re: Any problems with LARGE tablespaces15_Billings, Linda30_linda.billings@DOA.STATE.WI.US31_Thu, 29 Mar 2001 14:08:29 -0600296_- From what I understand the LARGE keyword will eventually disappear in favor of DSSIZE so I would use that instead. If you want to specify a DSSIZE greater than 4G then make sure you have the PTFs for these APARs applied: OW41955, OW43750, and OW43019. The last one is really for MQSeries. [...]
3455 36 105_DB2 OS390 and statically bound CICS/COBOL process vs Dynamic SQL in C/S process - general recommendations17_Gerald Bustamente20_ggbusta@LANDSEND.COM31_Thu, 29 Mar 2001 14:32:08 -0600372_iso-8859-1 We are looking for any recommendations on how to optimize C/S processes especially in the context of accessing operational data stores.
Among the more common ones we're aware of are:
1. Stored procedures. 2. Parameter markers. 3. For fetch only with UR for read-only processes. 4. Dynamic statement cache. 5. Bufferpool tuning. 6. Smart SQL. [...]
3492 43 39_Re: Any problems with LARGE tablespaces13_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Thu, 29 Mar 2001 13:17:21 -0700311_iso-8859-1 Currently on 6.1 have a 32 part table at 440 million rows MEMBER CLUSTER DSSIZE 4G. It works fine. Two years ago we used LARGE on some tables that were growing beyond the 2G limit and had to wait for third party software vendor to deliver a tolerant version of a few tools. Otherwise no issues. [...]
3536 30 34_IBM support for DB2 UDB V6 for AIX15_Jackson Reavill18_damcon2@US.IBM.COM31_Thu, 29 Mar 2001 16:23:52 -0500685_us-ascii Hello All,
I was wondering if anyone has heard when IBM will stop support for DB2 UDB V6 for AIX?
TIA, Jay
Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 801-7303, Tie Line 8-427-7303 ----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - Bavarian Motor Works -----------------------------------------------------
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3567 34 27_Re: DB2 Connect PE Question11_Jose' Gomez21_Jose_Gomez@WENDYS.COM31_Thu, 29 Mar 2001 16:39:17 -0500513_us-ascii Hello Shauna:
I have found a number of problems with Client Application Assistant in version 6.1 and 7.1, some of these problems are not 5.x in versions. I have a set of BAT files to configure my clients, ODBC data sources, DB2CLI.INI. They users can launch it from our DBA Web, or a files server. The folks from PC Support group use these files. So, I do not instruct them to use the Client Configuration Assistant at all. That works better... The Profile Export feature has some problems also...
3602 15 48_Need help to create COMPARE strategy in Platinum13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Thu, 29 Mar 2001 16:47:13 -0500
3618 74 27_Re: DB2 Connect PE Question13_Terry McCombs20_tmccomb@SEDGWICK.GOV31_Thu, 29 Mar 2001 15:52:23 -0600655_iso-8859-1 Would you publish the BAT files on the document server or in a note? This sounds like something I'd like to investigate. Thanks.
-----Original Message----- From: Jose' Gomez [mailto:Jose_Gomez@WENDYS.COM] Sent: Thursday, March 29, 2001 3:39 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Connect PE Question
Hello Shauna:
I have found a number of problems with Client Application Assistant in version 6.1 and 7.1, some of these problems are not 5.x in versions. I have a set of BAT files to configure my clients, ODBC data sources, DB2CLI.INI. They users can launch it from our DBA Web, or a files server. The [...]
3693 211 52_Re: Need help to create COMPARE strategy in Platinum16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 29 Mar 2001 23:19:26 +0100591_- Good Evening Enuda,
here are the steps:
1) Option 0 and then choose AUTOMAPPING MASK SERVICES - and create a MASKSET. 2) Then you specify TEST in the SOURCE TABLE CREATOR and ACPT in the TARGET TABLE CREATOR. 3) Then you choose COMPARE RULES also from Option 0. 4) Specify "Y" for the parameters where you want SOURCE definition to take precedence, and "N" where you want to keep the current TARGET parameter even though there are differences. 5) Create Compare strategy (choose DB2-DB2 compare on the next panel. 6) Specify T(able) as the starting point - and then the [...]
3905 109 52_Re: Need help to create COMPARE strategy in Platinum14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM31_Thu, 29 Mar 2001 14:22:31 -0800576_iso-8859-1 Carlton. Do do this requires use of Mapping Masks. If you look in RCM Users Guide in the Profile chapter, there is a discussion of Mapping Masks. The mask that you want to use is Table and you would set Source Creator to TEST and Target Creator to ACPT (assuming that you want the differences to be applied to the ACPT set of tables). Also, you will want to define a RULESET (again in the RCM Profile) to tell RC/Compare to NOT change the creator ID. Hope this helps. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions [...]
4015 13 27_Re: DB2 Connect PE Question13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Thu, 29 Mar 2001 16:26:30 -0600438_- I am making the assumption that you are placing "DB2 Catalog" statements in this bat file for each of your "Databases". Is that correct? What else do you have in it? Are you willing to share it?
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
4029 236 27_Re: question on outer join.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 29 Mar 2001 16:56:58 -0600483_iso-8859-1 Nagaraj,
I will try to expand on the terminology:
Preserved row table - unmatched join rows are preserved for this table, such as a left table of a left join, or right table of a right join. In a full join, both tables are preserved row tables.
NULL supplying table - unmatched join rows are supplied with NULLs for this table, such as the right table of a left join, and left table of a right join. In a full join, both tables can supply NULLs. [...]
4266 64 33_Re: Updates to partitioning index14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 30 Mar 2001 20:56:18 +1000325_us-ascii Walter,
Yes, Indexes are not protected in the same way which allows anomalies to occur. It seems like you given very good ammunition for the removal of the drain locks. The other alternative is that there is an even more complex reason why IBM takes the drain locks, but I can't think of it immediately. [...]
4331 122 52_Re: Need help to create COMPARE strategy in Platinum14_Michael Finnis28_MFinnis@PRINCETONSOFTECH.COM31_Thu, 29 Mar 2001 18:27:09 -0500570_ISO-8859-1 Hi Carlton,
It is extremely simple with Princeton Softech's Compare for DB2. Take a look at: http://www.princetonsoftech.com/comparefordb2
If you want any other information I would be happy to obilige off list.
Cheers, Mike (Technical) Product Specialist Princeton Softech Australia
-----Original Message----- From: Carlton Enuda [mailto:carltonenuda@HOTMAIL.COM] Sent: Friday, March 30, 2001 7:17 AM To: DB2-L@RYCI.COM Subject: Need help to create COMPARE strategy in Platinum [...]
4454 85 42_Re: How many subscribers are on this list?18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Fri, 30 Mar 2001 09:05:21 +0930546_us-ascii Tony
Good to see you applying your catch cry so rigorously!
"The reason we have 2 ears and 1 mouth is because we should listen more than we talk."
TGIF
Cheers Bruce
"Provenzola, Tony" @RYCI.COM> on 30/03/2001 02:27:58
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Subject: How many subscribers are on this list? [...]
4540 52 54_CPU Accounting Overhead for DB2 Accounting Class 2 & 314_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 30 Mar 2001 21:58:17 +1000373_us-ascii King,
Roger Miller should really address this one. The overheads vary from site to site depending on volume and complexity of SQL calls.
Normally class 2 collection overhead is quite a bit more expensive than class 3. So don't turn off class 3 because it measures suspensions such as I/O wait and Lock Wait which is really critical information. [...]
4593 90 55_Re: Table Design for online/fairly heavy reporting app?14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 30 Mar 2001 22:14:52 +1000553_us-ascii Jay,
O.K. I see what you are doing. I don't like the single column surrogate ids. on the lower tables but if they are only INTEGER columns the width is not too bad. If you can't use identifying realtionships to have multi-column primary keys then I would support your denormalisation of higher keys into the lower entites (tables) to enable best possible clustering to be chosen. Yes you normally will not wish to cluster by the random single column surrogate id. because it does not group the data in any useful or meaningful way. [...]
4684 97 52_Re: Outer Join Query Use of literals in ON CLAUSE???14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 30 Mar 2001 22:30:32 +1000459_us-ascii Richard,
I'm afraid you have goofed on this one. You had better try it. Understanding the new Outer Join has some traps in it. I made the same mistake the first time I tried this.
SELECT whatever FROM TABA LEFT OUTER TABB ON TABA.col1 = TABB.col1 AND TABA.col2 = '123'
The predicate in the ON clause limits which rows of TABA participate in the join, but does not effect which rows of TABA are preserved for the result set. [...]
4782 109 27_Re: question on outer join.14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 30 Mar 2001 22:35:31 +1000618_us-ascii Limited Predicate push down for outer joins occurred in DB2 V4. i.e some WHERE predicates were applied before the join.
From: Michael Hannan
>From: Terry Purcell [mailto:Terry_Purcell@YLASSOC.COM] >Sent: Wednesday, March 28, 2001 6:50 PM >To: DB2-L@RYCI.COM >Subject: Re: question on outer join. > > >Walter, > >I feel I must reply since you appear to have some issues with my response. > >Firstly, this is a discussion on OUTER JOINs. I did not say that WHERE >clause predicates are evaluated after the join for INNER JOINs. > >You say you THINK the same holds true for the outer table of [...]
4892 100 36_Re: V5 to V6 - Host variable problem14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 30 Mar 2001 11:00:42 +1000481_iso-8859-1 Would you care to share with us the _actual_ declaration of HOSTVAR? Or are we playing "Battleships"?
/* standard disclaimer */ James Campbell Hansen Corporation
-----Original Message----- From: Manas Dasgupta [mailto:manas.dasgupta@PFSFHQ.COM] Sent: Friday, 30 March 2001 5:11 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] V5 to V6 - Host variable problem
Jeff / Chris:
Thanks, Can't find any hits on this error on the IBM APAR page. [...]
4993 29 38_How many subscribers are on this list?16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Thu, 29 Mar 2001 20:25:31 -0500588_us-ascii HI, I know there are a lot of new subscribers to this list. thought I would post a gateway to All the IBM information sites http://isource.ibm.com/world/resource_directory.shtml HTH our newer members and as a reminder to our older ones Thanks Tom (dinasour) Faglon:-)
ps While working 7 days a week I am glad for an occasioal break in humor and trivia. The last one about languages just shows me how broad and technically diverse out list mambers are, it also brought back a lot of good memories The latin one I started was as a result of the many little postscripts [...]
5023 140 36_Re: V5 to V6 - Host variable problem15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Fri, 30 Mar 2001 11:34:16 +1000543_- Our Applications are had a Bind problem with this type of thing when they tried supplying a HV value similar to the following:
:hv= " 'val1', 'val2', 'val3' "
for a statement like
SELECT * FROM TAB WHERE COL1 IN ( :hv ) ;
I went to great pains explaining that the SQL parser would interpret the :hv as a single IN LIST value and it would not be translated at Runtime to an actual list of values, but I've never known the exact answer. There possibly is a way to provide an IN LIST in a single host variable. [...]
5164 77 0_25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Fri, 30 Mar 2001 09:50:14 +0800420_us-ascii Hi,
Which 3rd party software are we talking about. If it is sensitive please reply to my email id. Also indicate which software it is for eg reorg, unload etc. I have just implimented one large tablespace and need to know the impact.
Thanks
(Embedded image moved DB2 Data Base Discussion List to file: (Tel: ) pic25087.pcx) 30/03/2001 04:17 AM [...]
5242 78 39_Re: Any problems with LARGE tablespaces25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG31_Fri, 30 Mar 2001 09:50:43 +0800420_us-ascii Hi,
Which 3rd party software are we talking about. If it is sensitive please reply to my email id. Also indicate which software it is for eg reorg, unload etc. I have just implimented one large tablespace and need to know the impact.
Thanks
(Embedded image moved DB2 Data Base Discussion List to file: (Tel: ) pic28570.pcx) 30/03/2001 04:17 AM [...]
5321 63 39_Re: Any problems with LARGE tablespaces15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Fri, 30 Mar 2001 13:50:25 +1000398_- Hi Mark,
I wouldn't say a bad experience, just some. I'd suggest not using LARGE - as someone else has also suggested - but stick with DSSIZE. You'll need to make sure you have an SMS data class (or is it management class? can't remember) that allows extended addressability (EA) datasets. There are one or two other prereqs, but they're fairly explicitly outlined in the DB2 blurbs. [...]
5385 57 31_Re: More and more list prefetch16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 30 Mar 2001 11:34:43 +0530330_- 1) Is the index used for the list prefetch exist in production ? 2) Are the cluster ratio same ? 3) Is there any extra statistics exist in production(COLDIST etc) which might force optimizer to go for sequential prefetch ?
So, let's first list down all the stats which influence the optimizer and check all of them. [...]
5443 93 52_Re: Outer Join Query Use of literals in ON CLAUSE???16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 30 Mar 2001 11:47:37 +0530603_- I was tracking this mail very thoroughly and really gave me nice and solid concept of outer join processing. Thanks to Terry, Richard and Michael. Thanks to people who raised the questions in between which let these experts enter more and more into conceptual details.
So, outer join is more comlicated(for better performance) now. Should not each and every organization keeps a track on each and every outer join coded by developer ?. It is not always neccessary or possible that developers will be having clear cut concept of this complicated outer join processing. So, how is it done ? [...]
5537 59 52_Re: Outer Join Query Use of literals in ON CLAUSE???14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Fri, 30 Mar 2001 00:40:12 -0600397_iso-8859-1 Michael,
Yes, you are correct and I caught it later, and Terry yelled at me. Was typing without looking at my notes, which is the wrong way to answer. Worse when looking at my notes later, the examples were directed against the null supplying table and not the preserved row table. Will be interesting to run these examples on our V7 sandbox next week and see what happens. [...]
5597 16 26_NPAGES and tablespace scan18_Madeleine Fournier27_madeleine.f.fournier@AIB.IE31_Fri, 30 Mar 2001 02:24:27 -0600500_- Thanks everybody, We are not yet in V6 but I will keep an eye on NPGTHRSH. So far for the official documentation, at least the rumors are more or less fonded. I will go into the archives and retrieve the previous discussion. Thanks again.
Madeleine.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
5614 58 42_Re: DB2 UDB V6.1 fixpack inquiry question?11_Ross Temple25_Ross.Temple@WORKTHING.COM31_Fri, 30 Mar 2001 09:36:21 +0100655_iso-8859-1 If you enter the command 'db2level' and see 'DB2 v6.1.0.31' then fixpak 6 has been applied. The '31' relates to fixpak 6.
regards,
Ross Temple.
-----Original Message----- From: Bob Abad [mailto:BobRabad@AOL.COM] Sent: 28 March 2001 20:13 To: DB2-L@RYCI.COM Subject: DB2 UDB V6.1 fixpack inquiry question?
My peer has installed the UDB V6.1 many months ago, and supposedly applied Fixpack 6 with it. He can't seem to verify whether the Fixpack was installed or not. Would appreciate any response(command, etc..) how to find out what fixpack he is currently in??? I don't have the answer either.. [...]
5673 174 28_Re: Stored Procedure Problem6_Mr K S22_teldb2kals@TELSTRA.COM31_Fri, 30 Mar 2001 19:05:48 +1000426_us-ascii Peter,
Apart from remving the quotes, also try removing the colon (:). The colon is not allowed for a variable within an SQL procedure.
Cheers, Kals,
-----Original Message----- From: Krawetzky, Peter J [SMTP:KrawetzkyPJ@AETNA.COM] Sent: Friday, March 30, 2001 2:12 AM To: DB2-L@RYCI.COM Subject: Re: Stored Procedure Problem
I had tried it without the quotes and received this error: [...]
5848 50 53_Re: Reorg at partition level with NPIs: Clarification0_18_mebert@AMADEUS.NET31_Fri, 30 Mar 2001 11:07:44 +0200586_us-ascii Hello Mr. Drewe,
sorry for the delay - it's CeBIT time (spend one day there, then 3 days recuperating).
The "Numerous Problems Involved" (=NPIs) are mostly with the LOAD and REORG utilities, when operated on a partition level. As far as SQL activity is concerned, I would expect NPIs to be no different from any other index, except that they tend to be big. For the rate at which they de-organise, I'd say that this depends critically on the type of activity you have, and on the "orientation" of the NPIs with regard to the partitioning index (i.e. if your [...]
5899 225 58_Re: CPU Accounting Overhead for DB2 Accounting Class 2 & 314_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 30 Mar 2001 10:24:51 +0100430_iso-8859-1 Michael,
I'm sure I read something somewhere in the depths of a DB2 Version change (2.3 to 3 or something like that) that DB2 would ALWAYS collect class 1 and 2 information, starting the trace just externalised it. If this is true (Roger???) then there is no 'overhead' to having classes 1 and 2 running. And, as you say, the class 3 information is invaluable in the amount of wait information it provides. [...]
6125 144 52_Re: Need help to create COMPARE strategy in Platinum16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Fri, 30 Mar 2001 09:03:28 +0000386_us-ascii
Carlton,
Candle's DBTools offer you this possibility among other - if a third party tool that could make your life easy is what you're looking for.
Please feel free to drop me a note offline if you might be interested in further info, or some advice.
Ciao!
Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd. [...]
6270 23 26_Update related date column12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Fri, 30 Mar 2001 10:01:31 -0000416_- ALL, I have a date column 1998-02-03 and I would like to change all instances of 1998 to 2004 ie only the year part .The month and date part should be same.Is it possible to do it in QMF ? Any answers.
Thanks in Anticipation.
Regards Rakesh Kumar _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. [...]
6294 51 30_Re: Update related date column12_Teldb2kals S22_teldb2kals@TELSTRA.COM31_Fri, 30 Mar 2001 20:33:04 +1000578_us-ascii Hi Rakesh,
U can try the following SQL :
UPDATE tabname SET datecol = datecol + 6 YEARS WHERE year(datecol) = 1998.
Cheers, Kals
----- Original Message ----- From: Rakesh Kumar Date: Friday, March 30, 2001 8:01 pm Subject: Update related date column
> ALL, > I have a date column 1998-02-03 and I would like to change all > instancesof 1998 to 2004 ie only the year part .The month and date > part should be > same.Is it possible to do it in QMF ? > Any answers. > > Thanks in Anticipation. > > Regards > [...]
6346 35 30_Re: Update related date column31_Cagdas Ucar (Garanti Teknoloji)22_cagdasu@GARANTI.COM.TR31_Fri, 30 Mar 2001 13:19:49 +0300477_- update set date_col = date_col + 6 years where year(date_col) = 1998
-----Original Message----- From: Rakesh Kumar [mailto:rakesh457@HOTMAIL.COM] Sent: Friday, March 30, 2001 1:02 PM To: DB2-L@RYCI.COM Subject: Update related date column
ALL, I have a date column 1998-02-03 and I would like to change all instances of 1998 to 2004 ie only the year part .The month and date part should be same.Is it possible to do it in QMF ? Any answers. [...]
6382 49 30_Re: Update related date column0_19_mike.holmans@BT.COM31_Fri, 30 Mar 2001 11:20:40 +0100638_- Anything wrong with
UPDATE table SET date_column = date_column + 6 YEARS WHERE YEAR(date_column) = 1998
for what you want to to?
Mike Holmans BT ISE Database Services mike.holmans@bt.com
This post represents the views of the author and does not necessarily accurately represent the views of BT
> -----Original Message----- > From: Rakesh Kumar [SMTP:rakesh457@HOTMAIL.COM] > Sent: Friday, March 30, 2001 11:02 AM > To: DB2-L@RYCI.COM > Subject: [DB2-L] Update related date column > > ALL, > I have a date column 1998-02-03 and I would like to change all > instances > of 1998 to 2004 ie only the [...]
6432 50 30_Re: Update related date column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 30 Mar 2001 16:26:44 +0530341_- I think it is possible if you add 6 years but date and month part can be changed depending on the leap year etc. If it is a primary key or the part of the primary key then it would be again different story.
Does not this work...??
UPDATE Table SET column = date(column + 6 years)--------------------check the syntax etc [...]
6483 94 30_Re: Update related date column18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Fri, 30 Mar 2001 21:39:07 +0930388_us-ascii Howzit Rakesh?
Depending on how big your table is, you might want to avoid the date function being applied to every record,
Use the following
UPDATE table SET date_column = date_column + 6 YEARS WHERE date_column between '1998-01-01' and '1998-12-31'
This way at least it will be indexable and sargeable (stage 2).
Cheers Bruce Williamson [...]
6578 94 30_Re: Update related date column18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU31_Fri, 30 Mar 2001 21:54:01 +0930420_us-ascii Howzit Sanjeev?
This is not an issue as 1998 is not a leap year and therefore no '1996-02-29' to worry about. Even if it was DB2 is smart enough to take care of it as follows:
SELECT DATE('29/02/1996') + 6 YEARS FROM SYSIBM.SYSDUMMY1;
28/02/2002
Try it you'll like it!
Cheers Bruce Williamson
DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia [...]
6673 85 31_Re: More and more list prefetch15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 30 Mar 2001 06:05:21 -0600477_- Hi
To my great surprise the list prefetch has gone. Once again I copied the Runstats values and the new explain shows the same access path as in production. Well, I think, you don't believe me, if I say, I copied the same values yesterday (I wouldn't believe anybody else either), but I will keep an eye an this topic and if it happens again, I will provide you with all the Runstats values, at least all the tables I copied. So up to now thanks for all replies. [...]
6759 61 30_Re: NPAGES and tablespace scan12_Isaac Yassin20_yassini@BEZEQINT.NET31_Fri, 30 Mar 2001 14:35:28 +0200435_iso-8859-1 Hi, Please note that TS scan on small tables is not used when the access is via unique index or you ask for a field in an index (unique or not).
Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- From: "Mike Turner" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, March 29, 2001 3:38 PM Subject: Re: NPAGES and tablespace scan [...]
6821 130 30_Re: Update related date column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 30 Mar 2001 18:23:52 +0530334_- DB2 is indeed smart enough to take care of leap year but as Rakesh specified, 1) He does not want days and month fields to be changed. 2) If at all it is a primary key......i mean to say 29th Feb of some leap year + 6 years = 28th feb of non leap year and 1st march of the same leap year + 6 years = 28th feb of non leap year. [...]
6952 67 67_Re: WEBCAST: IBM DATABASE TOOLSET - LATEST ADDITIONS TO THE FAMIL Y15_Lankester, Andy22_Andy_Lankester@BMC.COM31_Fri, 30 Mar 2001 06:57:36 -0600498_iso-8859-1 Did anyone participate? If so any comments?
Andy Lankester DB2 Product Manager EMEA S/390 Field Marketing bmcsoftware Direct: +44 (0) 1784 478 836 Mobile: +44 (0) 77 68 25 79 76 andy_lankester @bmc.com
-----Original Message----- From: Morrill, John [mailto:JohnM@VP.NET] Sent: 29 March 2001 20:36 To: DB2-L@RYCI.COM Subject: WEBCAST: IBM DATABASE TOOLSET - LATEST ADDITIONS TO THE FAMILY [...]
7020 152 30_Re: Update related date column6_prohun24_prohun@TELPACIFIC.COM.AU31_Fri, 30 Mar 2001 22:55:47 +1000459_iso-8859-1 Sanjeev,
I'm afraid that 1st March of a leap year + 6 years is still 1st March of a non-leap year; DB2 only adjusts dates if they are invalid. However, your example of 29th February is correct.
Cheers, Ulrich
----- Original Message ----- From: "S, Sanjeev (CTS)" Newsgroups: bit.listserv.db2-l To: Sent: Friday, March 30, 2001 10:53 PM Subject: Re: Update related date column [...]
7173 152 30_Re: Update related date column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Fri, 30 Mar 2001 18:55:20 +0530533_- Just missed to write 28th Feb instead of 1st March....My oplogies 29th Feb of some leap year + 6 years = 28th feb of non leap year and 28th Feb of the same leap year + 6 years = 28th feb of non leap year.
Regards, Sanjeev
> -----Original Message----- > From: S, Sanjeev (CTS) > Sent: Friday, March 30, 2001 6:20 PM > To: 'DB2 Data Base Discussion List' > Subject: RE: Update related date column > > DB2 is indeed smart enough to take care of leap year but as Rakesh > specified, > 1) He does not [...]
7326 81 54_CPU Accounting Overhead for DB2 Accounting Class 2 & 313_Jodi Murawski27_Jodi.Murawski@METAVANTE.COM31_Fri, 30 Mar 2001 07:39:43 -0600577_us-ascii We recently went through a similar scenario. We had Strobe reports run against DB2 jobs showing high overhead in 'Timer Service' (you didn't indicate in your scenario what resource in the Strobe report was showning up high). There was an IBM APAR out there indicating that the overhead in accounting traces 2 and 7 cause this. I ran statistical reports against an entire production subsystem showing the CPU and wall time for every job. I ran a couple Strobe reports where we showed high overhead in Timer Service. I then set up the DB2 to only have traces 1 and [...]
7408 97 30_Re: NPAGES and tablespace scan11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Fri, 30 Mar 2001 08:47:39 -0500594_iso-8859-1 This part of the Application guide seems to validate the 10 page rule as well as explain an option to change that. See the "recommendation".
7.3.6.8 Using a subsystem parameter
DB2 often does a table space scan or nonmatching index scan when the data access statistics indicate that a table is small, even though matching index access is possible. This is a problem if the table is small or empty when statistics are collected, but the table is large when it is queried. In that case, the statistics are not accurate and can lead DB2 to pick an inefficient access [...]
7506 92 83_Re: Parallel jobs running longer then same jobs run one at a time DB2 V6 for OS/39015_Debra D. Jordan33_debrajordan@DISCOVERFINANCIAL.COM31_Fri, 30 Mar 2001 08:10:23 -0600332_us-ascii Linda, We recently had a problem with image copy jobs (8 that run in parallel) running extremely long. They went from running 15 - 20 minutes to over an hour.
During the investigation, it was determined that there was a problem with the channel path to the tape device.
Just some input.................. [...]
7599 149 55_Re: Table Design for online/fairly heavy reporting app?15_Jackson Reavill18_damcon2@US.IBM.COM31_Fri, 30 Mar 2001 09:19:36 -0500537_us-ascii Michael,
Yes, you are right... I am trying to have my cake and eat it too. So far it's working (fingers and toes are crossed) and I think the primary reason is that I've been able to avoid the additional indexes on columns B and C of an ABCD clustering index. That was a point that I stressed up front to the developers and report writers. When joining we have to come in with the parent key (ABC). When coming in randomly use the surrogate key (D). Yes, I agree, one compound index can do it all. In fact, wherever [...]
7749 147 36_Re: V5 to V6 - Host variable problem14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Fri, 30 Mar 2001 09:26:22 -0500532_iso-8859-1 James:
Here it is ..... the host variable declaration:
05 PART-TYPE-LIST. 10 PART-TYPE-LIST-01 PIC X(08) VALUE ' '. 10 PART-TYPE-LIST-02 PIC X(08) VALUE ' '. 10 PART-TYPE-LIST-03 PIC X(08) VALUE ' '. 10 PART-TYPE-LIST-04 PIC X(08) VALUE ' '. 10 PART-TYPE-LIST-05 PIC X(08) VALUE ' '. 10 PART-TYPE-LIST-06 PIC X(08) VALUE ' '. 10 PART-TYPE-LIST-07 PIC X(08) VALUE ' '. 10 PART-TYPE-LIST-08 PIC X(08) VALUE ' '. 10 PART-TYPE-LIST-09 PIC X(08) VALUE ' '. 10 PART-TYPE-LIST-10 PIC X(08) VALUE ' '. [...]
7897 31 39_Re: Any problems with LARGE tablespaces5_Chris17_isatcjk@YAHOO.COM31_Fri, 30 Mar 2001 06:42:22 -0800519_us-ascii Last fall we ran into a problem with the second space map page on a 254 partition DSSIZE 64g tablespace.
IBM coded a new fix for us in a couple of days.
I believe we found the problem during a Runstats
Sorry, don't know what the fix IDs are.
Chris
===== http://www.geocities.com/isatcjk/index.html
__________________________________________________ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text [...]
7929 58 38_Re: IBM support for DB2 UDB V6 for AIX0_15_leon@CA.IBM.COM31_Fri, 30 Mar 2001 10:41:51 -0500514_us-ascii I fyou go to http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/index.d2w/report
you will see that the end-of service date for V6 is Dec 31, 2001. I also recomend bookmarking this page as this is our official on-line support home page.
Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com
Jackson Reavill/Tampa/Contr/IBM@IBMUS@RYCI.COM> on 03/29/2001 04:23:52 PM
Please respond to DB2 Data Base Discussion List [...]
7988 41 36_JCL to compile a C program on OS/39011_John Puglia16_jpuglia@IWON.COM31_Fri, 30 Mar 2001 11:47:06 -0500393_ISO-8859-1 Hello everyone,
We currently have a stored procedure that was built from the stored procedure builder on a Solaris box.
This stored procedure is in the SPL (SQL procedure language) that needs to be compiled in C on a OS/390 MVS platform.
We do not make use of the WLM (Workload Manager), so we cannot use the stored procedure builder to compile it into C. [...]
8030 66 14_DB2 Chargeback16_Brookman, Gerald32_gerald.brookman@US.ORIGIN-IT.COM31_Fri, 30 Mar 2001 12:05:41 -0500297_iso-8859-1 Env: DB2 OS/390 V6.1
Can someone give me an IBM document reference on "DB2 using Cross Memory Service charges back service units used to the calling TCB - e.g., TSO, CICS, QMF, etc.".
I need this for a customer proposal ASAP. TIA.
Gerald R. (Garry) Brookman
8097 27 36_Can you bind while reorg is running?45_=?iso-8859-1?Q?=22LeBlanc=2C_Andr=E9-ITB=22?=30_AndreD.Leblanc@CCRA-ADRC.GC.CA31_Fri, 30 Mar 2001 12:04:49 -0500282_iso-8859-1 We have a tight outage window in which we would like to run a 6 hour reorg and bind a few hundred packages. Are there any phases in a reorg (shrlevel none) where there would be contention with a bind of packages for that same tablespace? Would the DBD be a problem? [...]
8125 31 36_space realease..... immediate reply.17_ravi kumar hassan18_ravibh@HOTMAIL.COM31_Fri, 30 Mar 2001 11:06:47 -0600339_- Hi Folks,
We are using DB2 V6 on OS390. i have a question on space realease after deleteing records from the table in these three typese of tablespaces.
if we delete a records from SEGMENTED TABLESPACE. does it space will realese and can we reuse the space or do we need to reorg the tablespace to reuse the space???? [...]
8157 83 40_Re: space realease..... immediate reply.15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Fri, 30 Mar 2001 11:45:02 -0600486_iso-8859-1 Yes and no.
DB2 figures out where it would like to put the row (what page) and goes to the space map page for that page and sees if there is space on the page by looking at the space map entry for the page. If there is space there, it will put the row there. If there is no space, it will look (I think) at the space map for the preceding and following 32 pages. If there is no space in those 64 pages, then it is off to the last page of the tablespace to do the [...]
8241 75 40_Re: JCL to compile a C program on OS/39017_Linda F. Claussen18_lindafc@NETINS.NET31_Fri, 30 Mar 2001 11:47:05 -0600578_iso-8859-1 John,
The following steps are outlined in the Appl. programming guide. You can take your existing Program prep JCL and make a few changes. Step 1 will be the SQL translation, the last step will be the CREATE PROCEDURE to define the procedure to DB2.
1. Call the DB2 DSNHPSM program with parameter HOST(SQL) to translate the SQL procedure source code into a C source program. 2. Call the DB2 precompiler with parameter HOST(C) to precompile the C source code in a modified C source code and a DBRM. 3. Call C compiler and link edit to create a load [...]
8317 17 31_IMS to DB2 Conversion Products?15_Gary Fahrlander28_gary.fahrlander@ZURICHNA.COM31_Fri, 30 Mar 2001 11:46:18 -0600313_us-ascii Are there any products on the mainframe market that will assist in conversion of IMS databases to DB2?
And just to make it more interesting, I'm looking for a tool that will intercept the COBOL IMS calls and convert them into SQL statements so we don't have to re-write hundreds of programs. [...]
8335 32 36_dsn1copy both tablespace & indexspac14_Mark McCormack27_mamccormack@STATESTREET.COM31_Fri, 30 Mar 2001 13:02:36 -0500359_us-ascii Alex J. P. wrote (3 days ago):
> Any tips/tricks/gotchas would greatly appreciated.
DSN1COPY is sort of a glorified IEBGENER. It can become I/O bound, and that can make a big difference on large objects. To speed things up, use extra buffers on input and output dd stmts. You will be trading extra region for reduced elapsed time. [...]
8368 38 16_Trigger question10_Shery Hepp17_schepp@SRPNET.COM31_Fri, 30 Mar 2001 11:14:01 -0700482_iso-8859-1 Greetings-
I'm having a problem with creating an after trigger. I've been going through the manuals and trying all kinds of different variations and I'm fresh out of ideas.
What I want to do is after col1 in a table is updated- based on the updated value I want to execute 2 different stored procs. I originally tried it with the Values case clause- but that only works with UDF's. (We aren't set up with WLM yet- so I'm not sure if this is an option) [...]
8407 38 35_Re: IMS to DB2 Conversion Products?14_Shaul Bergfeld18_sbergfeld@TACT.COM31_Fri, 30 Mar 2001 13:09:52 -0500380_iso-8859-1 Hi Gary -
Yes, there are a number of options available to you, incorporating different degrees of automation. Because I am directly involved in some of them, the parameters of this list prevent me from discussing them with you in detail, over this list.
If you are interested, you can contact me directly, and we can discuss these options some more. [...]
8446 94 40_Re: JCL to compile a C program on OS/39014_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Fri, 30 Mar 2001 13:43:38 -0500345_iso-8859-1 John:
Go to the following site : http://www.ibm.com/software/db2os390/sqlproc/
You can download SQL Procedures support for OS/390. It comes with JCL and README's on how to compile a SQL Procedures in OS/390. If you can't get it work let me know and I can send the JCL to you. I had customised the JCL for our shop. [...]
8541 43 35_Re: IMS to DB2 Conversion Products?11_Pedro Ramos18_ramos@MAINFRAME.PT31_Fri, 30 Mar 2001 20:38:27 +0100362_us-ascii Hello. I think i have the solution to your problem. The product DL/2 from CIRCLE enables you to migrate from IMS to DB2, without change your legacy code. http://www.circle-group.com/Software/SoftwareOpeningpage.htm In short DL/2 will substitute the IMS calls stub with one that DB2 calls. Best regards, Pedro Ramos
PS: Have a great weekend. [...]
8585 23 37_32K Varchar Host Variable Definition?14_Joey Advincula29_joey_advincula@MAINFRAMER.NET31_Fri, 30 Mar 2001 14:07:17 -0600470_- successfully created stored procedure (DDL) with several 32k varchar parameter... now that i'm coding the COBOL DB2 stored procedure, i'm kind of confuse how to define the host variable for a 32k varchar parameter...
i defined it like this....
01 OUT-PARAMETER-01. 49 OUT-PARAMETER-01-LEN PIC S9(4) USAGE COMP. 49 OUT-PARAMETER-01-TEXT PIC X(32704).
but don't i have to increase size of OUT-PARAMETER-01-LEN to allow for max length of 32704? [...]
8609 40 32_TRIDEX Classes 4/3/01 and 4/4/019_Judy Nall21_judynall@TELEPORT.COM31_Fri, 30 Mar 2001 19:02:40 -0500573_us-ascii ******************************************************* TRIDEX USER GROUP ANNOUNCES ONE DAY CLASSES !!!! ******************* Sign Up Today! **********************
Instructor: LINDA CLAUSSEN --Will Be Speaking On:
APRIL 3, 2001 -STORED PROCEDURES PART 1
APRIL 4, 2001 -STORED PROCEDURES PART 2
Need More Information? http://www.db2tridex.org/html/education.html#ClaussenClass
Sign Up for One or Both Classes TODAY! Contact: http://www.db2tridex.org/html/class_enrollment.html
Linda Claussen at TRIDEX is Sponsored By: [...]
8650 34 0_24_Islam Abdelaleem Mohamed17_aleemi@EG.IBM.COM31_Sat, 31 Mar 2001 09:03:34 +0300316_us-ascii Hi Everybody, Thanks for your time , I search for the right documentation which guide me to the following definition DRDA / over SNA connection between HOST ( VSE/ESA R241 , VTAM R42 , CICS/TS for VSE and DB2 R610 ) with ------) ( WIN 2000 server , DB2 R71 ,DB2 CONNECT and Communication Server R6 ) . [...]
8685 33 17_SQL30020N RC=125441_Romeo_B_Titong/DB2_Support/ACISystems%ISD59_Romeo_B_Titong/DB2_Support/ACISystems%ISD@ACISYSTEMS.COM.PH31_Sat, 31 Mar 2001 15:12:04 +0800286_us-ascii Hi All!
What is SQL30020N with RETURN CODE = 1254? I'm not familiar with the return code.
I am trying to connect DB2 Connect v7.1 to OS/400 V4R3 and I received the error. I have done this set up many times and this is the first time I got the error message. [...]
8719 15 43_Paul Park/Toronto/IBM is out of the office.21_Paul Park/Toronto/IBM16_ppark@CA.IBM.COM31_Sat, 31 Mar 2001 02:28:10 -0500396_us-ascii I will be out of the office starting March 31, 2001 and will not return until April 7, 2001.
I will respond to your message when I return.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
8735 68 20_Re: Trigger question15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Sat, 31 Mar 2001 18:17:58 +1000308_- Sheryl,
Without seeing your CREATE TRIGGER statement I can't be sure but maybe you just have a syntactical problem. From the OS/390 diagram it seems all you need is to do is code separate SQL CALL statements in the Triggered Action. Of course that easy for me to say since I've never tried it. [...]
8804 107 30_Re: Update related date column14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sun, 1 Apr 2001 06:49:33 +1000646_us-ascii Yes - indexable and sargable (stage 1).
From: Michael Hannan >From: Bruce W Williamson >Subject: Re: Update related date column >To: DB2-L@RYCI.COM > >Howzit Rakesh? > >Depending on how big your table is, you might want to avoid the date function >being applied to every record, > >Use the following > >UPDATE table SET date_column = date_column + 6 YEARS > WHERE date_column between '1998-01-01' and '1998-12-31' > >This way at least it will be indexable and sargeable (stage 2). > >Cheers >Bruce Williamson > >DB2 Database Administrator >DCIS >NT Government >Darwin >NT 0800 >Australia > > > > [...]
8912 223 58_Re: CPU Accounting Overhead for DB2 Accounting Class 2 & 314_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sun, 1 Apr 2001 06:49:43 +1000369_us-ascii Phil,
Never mind the theory. Some sites have shown demonstratable CPU savings from turning off class 2 accounting (but the savings are not massive). Tools like STROBE show the difference in certain DSN modules before and after. I believe the overhead of Class 2 is justified and not high for most systems since it provides good tuning information. [...]
9136 92 38_Re: Reorg at partition level with NPIs14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sun, 1 Apr 2001 06:49:59 +1000451_us-ascii Just to expand a little on NPIs.
For heavy INSERT into large tables, the INSERT throughput is limited by the NPIs' Getpages and Sync I/Os. Even if the data is sorted into order of the Cluster/partitioning index for minimizing Getpages and I/Os there, the NPI makes the performance much worse.
Although the best NPI for performance will be a highly clustered one, not much point in another index similar to the cluster one. [...]
9229 114 30_Re: NPAGES and tablespace scan14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sun, 1 Apr 2001 07:18:09 +1000470_us-ascii Lee & others,
It seems like people are doing their best to favour TS Scans over index access. IBM provided the DSNZPARM mainly to do the reverse. Allow use of Index Access paths when tables had not yet grown to a size possibly too big later for good TS Scan.
I am someone who regularly adjusts the NPAGES upwards to values of 10 or higher if needed to discourage TS Scan. I want DB2 to choose index access when matching columns are possible. [...]
9344 135 52_Re: Outer Join Query Use of literals in ON CLAUSE???14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sun, 1 Apr 2001 07:31:23 +1000370_us-ascii Sanjeev,
Your question: "Should not each and every organization keeps a track on each and every outer join coded by developer ?"
The answer for me is a resounding no! The original DB2 V4 Outer Join was not good! It was very dangerous and produced poor access for complex queries. A lot of skill and experience was needed to use it with care. [...]
9480 80 26_Re: FYI: CPU per I/O ratio14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sun, 1 Apr 2001 07:58:58 +1000575_us-ascii Chris,
A sync I/O costs about .33 millisecs CPU on a 3090-180J (approx 25 MIPS). This is saved if page is in BP. not exactly a percentage of the FETCH CPU which varies depending on many factors. For Seql. Prefetch Akira's rough rule is .33 millisecs per 5 pages.
Your example where 25 pages list prefetched used roughly the same CPU as 90 Getpages via the non-clustering index must have been too small. Particularly since all pages were in the BP. But I would expect the List Prefetch to win providing that you fetched all rows of the result set. [...]
9561 79 18_Warehouse Question11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID31_Sat, 31 Mar 2001 20:45:16 +0700364_ISO-8859-1 Dear all,
I like to know wheter the Warehouse Server in DB2 V.7.1 can be set up on other platform than NT or 2000. I've tried to find any information about this over the web and IBM site, I have even downloaded all the PDFs, but still couldn't find any information about it. If any of you (or even IBMers) in this list know, please tell me. [...]
9641 81 18_Re: DB2 Chargeback16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Sat, 31 Mar 2001 18:03:38 +0000540_us-ascii Gerald,
not completely sure if this is what you're looking for, but you can try the following:
http://www-3.ibm.com/solutions/businessintelligence/pdf/s390peak.pdf
HTH.
Ciao!
Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.
Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 email: aurora_dellanno@candle.com
1 Archipelago Lyon Way Frimley Camberley Surrey RH16 7ER UK
* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER [...]
9723 85 35_Re: IMS to DB2 Conversion Products?16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Sat, 31 Mar 2001 18:14:59 +0000423_us-ascii Gary,
the one I've used in a previous life as an IBM person was actually..... DL/2 from Circle. I am quite happy to quote the competition, since we don't do that kind of product at !Candle ;-)
joking aside, I don't have the link to Circle anymore but I'm sure it shouldn't be too difficult to find, and btw, this neat tool will allow you to read your IMS data without changing your legacy code. [...]
9809 41 18_DB2 v6 experiences0_14_DPetro@AOL.COM29_Sat, 31 Mar 2001 14:55:15 EST317_US-ASCII Hi,
We will be moving to version 6 at some point this year and I was wondering if anyone would be willing to share any significant experiences that they encountered with this version. Things such as performance problems, bugs, etc. that would be important for us to watch out for.
Thanks. Dave.
9851 60 22_Re: Warehouse Question15_Paul S Rockwood19_rockwood@US.IBM.COM31_Sat, 31 Mar 2001 18:12:35 -0500475_iso-8859-1 DB2 Warehouse Manager was first available on June 16, 2000
Platforms: AIX, OS/2, Solaris, Win NT/2000 NOTE: Also an option with DB2 UDB for OS/390 V7.
The previous product was Visual Warehouse V5.2.
Paul S. Rockwood IBM Software I/T Specialist Certified Solutions Expert - DB2 UDB v7.1 6710B Rockledge Drive Bethesda, MD 20817 301-803-3733 DB2 UDB Version 7 - Find out what all the excitement is about at http://www.ibm.com/software/data [...]