1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l September 2002, week 2
2 16 44_Larry Karl/Boulder/IBM is out of the office.10_Larry Karl16_lkarl@US.IBM.COM30_Sun, 8 Sep 2002 01:27:14 -0600491_us-ascii I will be out of the office starting September 6, 2002 and will not return until September 9, 2002.
I will be away from the office the afternoon of 9/6, returning on 9/9/02 Please contact Betty Kessock for DBA inquires.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
19 83 80_Re: Bind unsuccessful when EXPLAIN(YES) but works whenEXPLAIN(NO). Any idea why?17_Robert J. Milonas20_XRJMBOB@NETSCAPE.NET30_Sun, 8 Sep 2002 10:12:30 -0400733_iso-8859-1 You probably don't have a plan table set up. Create a plan table for the version of DB2 that you are on.
"Thomas E. Faglon" wrote:
>================================================ >To change your subscription options or to cancel your subscription visit >the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list >can be reached at DB2-L-REQUEST@listserv.ylassoc.com. > > > > > "VanDevender, Tom" > NERGY.COM> cc: (bcc: Thomas E. Faglon/Telcordia) > Subject: Re: Bind unsuccessful when EXPLAIN(YES) > 09/07/02 08:59 AM but works when EXPLAIN(NO). > Please respond to Any idea why? > DB2 Data Base > Discussion [...]
103 123 80_Re: Bind unsuccessful when EXPLAIN(YES) but works whenEXPLAIN(NO). Any idea why?16_VanDevender, Tom27_Tom.VanDevender@CINERGY.COM30_Sun, 8 Sep 2002 09:43:33 -0500438_iso-8859-1 someone get me off this list please tom vandevender
-----Original Message----- From: Robert J. Milonas [mailto:XRJMBOB@NETSCAPE.NET] Sent: Sunday, September 08, 2002 9:13 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Bind unsuccessful when EXPLAIN(YES) but works whenEXPLAIN(NO). Any idea why?
You probably don't have a plan table set up. Create a plan table for the version of DB2 that you are on. [...]
227 150 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM30_Sun, 8 Sep 2002 11:13:42 -0500485_iso-8859-1 that's why the explain (yes) on the bind fails, we had a simular problem but couldn't remember all of the specifics
|---------+-+-----------------------------------> | | | | | | | Carlton Enuda | | | | | | | | Sent by: DB2 Data | | | | Base Discussion List | | | | | | | | | | | | | | | | 09/07/02 11:44 PM | | | | Please respond to DB2| | | | Data Base Discussion | | | | List | | [...]
378 186 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Sun, 8 Sep 2002 15:26:39 -0400
565 153 43_Leaving/Changing your subscription to DB2-L12_Raymond Bell17_rbell@NZ1.IBM.COM30_Mon, 9 Sep 2002 09:50:45 +1200455_us-ascii Tom,
As someone else on the list tried to explain, at the bottom of every DB2-L post are instruction on how to alter/cancel your subscription. To be clear, the text is here:
>================================================ >To change your subscription options or to cancel your subscription visit >the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list >can be reached at DB2-L-REQUEST@listserv.ylassoc.com. [...]
719 209 47_Re: Leaving/Changing your subscription to DB2-L16_VanDevender, Tom27_Tom.VanDevender@CINERGY.COM30_Sun, 8 Sep 2002 18:46:16 -0500442_iso-8859-1 as i have EXPLAINED or splained as ricky used to say i have tried that 5 times with no luck so please all of you on this list if you have tvandevender@cinergy.com anywhere delete it please
-----Original Message----- From: Raymond Bell [mailto:rbell@nz1.ibm.com] Sent: Sunday, September 08, 2002 4:51 PM To: DB2 Data Base Discussion List Cc: VanDevender, Tom Subject: Leaving/Changing your subscription to DB2-L [...]
929 76 47_Re: Leaving/Changing your subscription to DB2-L12_Raymond Bell17_rbell@NZ1.IBM.COM30_Mon, 9 Sep 2002 12:15:51 +1200515_us-ascii Tom,
Have you tried e-mailing the List owners directly? Their e-mail address is also at the end of every post.
Raymond PS. No need to SHOUT - I'm just trying to help ya. That's what DB2-L is all about, after all. (helping, not shouting... ;o) )
"VanDevender, Tom" cc: Sent by: DB2 Data Subject: Re: Leaving/Changing your subscription to DB2-L Base Discussion List [...]
1006 213 47_Re: Leaving/Changing your subscription to DB2-L13_John Davidson21_jdadmin@IDATATECH.NET30_Sun, 8 Sep 2002 20:23:11 -0400
1220 100 47_Re: Leaving/Changing your subscription to DB2-L16_VanDevender, Tom27_Tom.VanDevender@CINERGY.COM30_Sun, 8 Sep 2002 20:39:16 -0500423_iso-8859-1 yes yes yes how many times do i have to say yes to this question
-----Original Message----- From: Raymond Bell [mailto:rbell@NZ1.IBM.COM] Sent: Sunday, September 08, 2002 7:16 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Leaving/Changing your subscription to DB2-L
Tom,
Have you tried e-mailing the List owners directly? Their e-mail address is also at the end of every post. [...]
1321 13 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 8 Sep 2002 20:46:57 -0500342_- And the L-thingy? Do you have that yet? DB2 is waiting for it.
James Campbell
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
1335 113 47_Re: Leaving/Changing your subscription to DB2-L12_Chris Pomasl19_pomasl@STARBAND.NET30_Sun, 8 Sep 2002 20:14:54 -0600708_us-ascii This is a lilstserv list. To remove YOURSELF, send a message to: listserv@listserv.ylassoc.com with the following command in the body of the message. Send it as plain text.
unsubscribe DB2-L
That should do the trick.
Chris
VanDevender, Tom wrote:
>yes yes yes >how many times do i have to say yes to this question > >-----Original Message----- >From: Raymond Bell [mailto:rbell@NZ1.IBM.COM] >Sent: Sunday, September 08, 2002 7:16 PM >To: DB2-L@LISTSERV.YLASSOC.COM >Subject: Re: Leaving/Changing your subscription to DB2-L > > >Tom, > >Have you tried e-mailing the List owners directly? Their e-mail address is >also at the end of every post. [...]
1449 37 82_Re: Bind unsuccessful when EXPLAIN(YES) but works when EX PLAIN(NO). Any idea why?13_David S Waugh16_dsw-dba@JUNO.COM28_Mon, 9 Sep 2002 03:58:00 GMT599_- Hi Carlton:
In almost every DB2 version/release, the format of the PLAN_TABLE changes a little -- mostly they add new columns for new information for the new version/release, but it's possible they might change the data type characteristics of existing columns.
I would recommend comparing the CREATE statements for the PLAN_TABLE for V5 with the CREATE statements for the PLAN_TABLE for V6 to see what changes have been made to it (one of the IVP jobs in SDSNSAMP - I disremember which - has the job step in it that creates a default PLAN_TABLE). Then I would work on updating [...]
1487 41 40_Re: Calling DSNUTILB from appln. program14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 8 Sep 2002 23:42:21 -0500637_- Had a few thoughts about this over the weekend. Perhaps a more viable (as in fewer things to go wrong, greater verification, etc) option would be:
- take the code in http://listserv.ylassoc.com/cgi/wa.exe?A2=ind9912D&L=DB2- L&P=R1133 as the starting point - modify TSOUTILB so that it examines the parameter string. If it ends with, say, "...|" it assumes the something is the desired initial sqlid, in which case it -- opens a CAF(?) connection to DB2 and attempts to dynamically SET CURRENT SQLID = '' (and closes the connection) -- if the SET CURRENT SQLID fails then the user is not authorised to [...]
1529 69 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?15_soumitra.mandal32_soumitra.mandal@TATAINFOTECH.COM30_Mon, 9 Sep 2002 10:32:37 +0530341_X-UNKNOWN Hello,
I am a greenhorn, so I will make careful statements. I have more questions to add.
If this 'L' is an installation exit, I am expecting them in SDSNEXIT dataset. I could not find one in our V6 environment. (Tried in SDSNLOAD too.)
Where can I find information on Database Procedures? What are they? [...]
1599 93 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?10_Hessel Rus13_H.Rus@OTRA.NL30_Mon, 9 Sep 2002 09:09:10 +0100419_iso-8859-1 If your problem is not yet solved, perhaps this is the matter: Is the tablespace and/or database containing the plan_table started for RW?
Carlton Enuda cc: Sent by: DB2 Data Subject: Bind unsuccessful when EXPLAIN(YES) but works when Base Discussion EXPLAIN(NO). Any idea why? List [...]
1693 19 33_Kevin Davis is out of the office.11_Kevin Davis18_kevin.davis@DB.COM30_Mon, 9 Sep 2002 02:15:54 -0600139_us-ascii I will be out of the office from 09/09/2002 until 09/16/2002.
I will respond to your message when I return.
1713 105 83_Re: Bind unsuccessful when EXPLAIN(YES) but works when EX PLAIN(N O). Any idea why?35_Umapathy, Gopalakrishna (Cognizant)26_UGopalak@CHN.COGNIZANT.COM30_Mon, 9 Sep 2002 12:56:12 +0530565_iso-8859-1 Did you succeed for EXPLAIN in the QMF.........(Just wondering.....if you have access to PLAN_TABLE.) Does your PLAN_TABLE format.......confirms to the DSNTESC available in the db2 sample library.
__________________________________________________ Regards, Gopalakrishnan Umapathy,
-----Original Message----- From: David S Waugh [mailto:dsw-dba@JUNO.COM] Sent: Monday, September 09, 2002 9:28 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Bind unsuccessful when EXPLAIN(YES) but works when EX PLAIN(NO). Any idea why? [...]
1819 48 47_Re: Leaving/Changing your subscription to DB2-L11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Mon, 9 Sep 2002 03:58:27 -0400555_ISO-8859-1 There does appear to be a problem with the listserver. I have not been receiving acknowledgements to my postings. When I send QUERY DB2-L to LISTSERV@LISTSERV.YLASSOC.COM I get no response.
Regards Mike Turner Email: michael_turner@compuserve.com Web: www.michael-turner.ltd.uk
Message text written by DB2 Data Base Discussion List >as i have EXPLAINED or splained as ricky used to say i have tried that 5 times with no luck so please all of you on this list if you have tvandevender@cinergy.com anywhere=20 delete it please [...]
1868 26 45_Calling DSNTIAUL utility from a COBOL Program15_Leonardo Zrycki29_leonardo.zrycki@ACCENTURE.COM30_Mon, 9 Sep 2002 10:06:59 +0200484_us-ascii Hello list:
It's possible to call DSNTIAUL or DSNTEP2 from a COBOL program to generate unloads files ???
Thanks in advance
Leonardo Zrycki Buenos Aires - Argentina
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. [...]
1895 58 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?13_Chris Andrews20_andrews_c@UK.IBM.COM30_Mon, 9 Sep 2002 03:18:25 -0500371_- 1. Not checked the error message but I wonder if you have 46 columns in your plan table ? If you do you need 49 columns see Admin Guide V6 page 933, Figure 137 as you upgraded DB2 from V5 to V6. These are the extra 3 columns needed.
OPTHINT CHAR(8)NOT NULL WITH DEFAULT HINT_USED CHAR(8)NOT NULL WITH DEFAULT PRIMARY_ACCESSTYPE CHAR(1)NOT NULL WITH DEFAULT [...]
1954 45 49_Re: Calling DSNTIAUL utility from a COBOL Program10_Ali OZTURK27_Ali.OZTURK@PAMUKBANK.COM.TR30_Mon, 9 Sep 2002 11:34:46 +0300493_iso-8859-9 Yes, but you have to allocate required files.
-----Original Message----- From: Leonardo Zrycki [mailto:leonardo.zrycki@ACCENTURE.COM] Sent: Monday, September 09, 2002 11:07 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Calling DSNTIAUL utility from a COBOL Program
Hello list:
It's possible to call DSNTIAUL or DSNTEP2 from a COBOL program to generate unloads files ???
Thanks in advance
Leonardo Zrycki Buenos Aires - Argentina [...]
2000 80 49_Re: Calling DSNTIAUL utility from a COBOL Program15_Leonardo Zrycki29_leonardo.zrycki@ACCENTURE.COM30_Mon, 9 Sep 2002 11:15:41 +0200462_us-ascii Ali:
How can I do it ?? Specially if I invoke DSNTIAUL twicw or more times.
Leonardo Zrycki Buenos Aires-Argentina
Ali OZTURK cc: Sent by: DB2 Data Base Subject: Re: Calling DSNTIAUL utility from a COBOL Program Discussion List
09/09/2002 10:34 AM Please respond to DB2 Data Base Discussion List [...]
2081 19 41_Mike Dupuis/OTT/TMG is out of the office.11_Mike Dupuis23_Mike.Dupuis@CLARICA.COM30_Mon, 9 Sep 2002 09:19:49 -0400432_us-ascii I will be out of the office starting 09/06/2002 and will not return until 09/16/2002.
Please contact DBDC for any pressing matters.
Ext. 7951 E-mail DBDC
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
2101 81 40_Re: Calling DSNUTILB from appln. program19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM30_Mon, 9 Sep 2002 09:35:52 -0400568_US-ASCII Thanks a lot for all of you for having taken your time to offer some great valuable suggestions. James, you are right, not really sure if my system programer will cooperate for the changes you have suggested. Really worth a try I will persue this path, mean time since the testing is also part of a deliverable for a subsequent test, we couldn't wait for long, so the input load files were converted to have the sqlid values for the defaultable column. It was a good exercise atleast for me attempting various methods to make this work. Thanks again for [...]
2183 34 10_-904 error6_Al Pat18_alpat225@YAHOO.COM30_Mon, 9 Sep 2002 07:09:31 -0700579_us-ascii
We have the business table's tablespaces defined in database DB01 and PLAN_TABLE's tablespace defined in database DB02. All the plans use (read & update) tables that are defined in the tablespaces of DB01.
We started the database DB01 in RO mode to take the daily image copies which run for about half hour. At the same time, we had few plans to be bound and issued the binds with EXPLAIN(YES). The binds failed with -904 message saying the database is in read only mode. Why will I get this error, when my PLAN_TABLE is in database DB02 which is in [...]
2218 50 26_db2udp could not be loaded16_Swinski, Kenneth23_KSwinski@MASSMUTUAL.COM30_Mon, 9 Sep 2002 11:07:04 -0400608_iso-8859-1 We have been getting '"db2udp" could not be loaded' errors creating stored procedures in several of our UDB instances, both through the Stored Procedure Builder and from telnet sessions. We are running DB2 AIX 7.2. I have added /opt/dbinstxx/sqllib/function, the directory where db2udp lives, to the PATH of two of the instances and recycled them. In one instance, it seemed to fix the problem, but the other instance continues to get the error. The instances are on different AIX boxes. I've included the first few lines of the stored procedure and the results below. I have a PMR open with [...]
2269 29 13_Sorting NULLS11_Ed Mullikin17_EMullikin@SCU.EDU30_Mon, 9 Sep 2002 08:08:50 -0700526_US-ASCII Somebody had suggested to us that there was a Database setting that can change the fact that the Database sorts the nulls as a low value rather than a high value. Here's an example: Basically when we run some payroll processes - there are rows inserted in the Leave Accrual Tables with the payroll Accrual Process Date as null, so when we then run the Advice File or Print File SQRs - it looks for the highest row - and it pulls the null row - so this is what prints on their stub. Has anybody heard of this? We [...]
2299 46 30_Re: DEFINE NO and mass deletes14_Gary Bernhardt30_gary.bernhardt@MAIL.SPRINT.COM30_Mon, 9 Sep 2002 10:14:42 -0500430_ISO-8859-1 BTW, We are on DB2 V7, z/OS, and it is working the way you describe, i.e., the index datasets are being defined. This is a problem for us also.
Let us know what IBM says and whether there are any workarounds.
Gary
-----Original Message----- From: lorinda.l.bewley [mailto:lorinda.l.bewley@US.HSBC.COM] Sent: Friday, September 06, 2002 1:36 PM To: DB2-L Subject: Re: DEFINE NO and mass deletes [...]
2346 72 14_Re: -904 error10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Mon, 9 Sep 2002 10:16:02 -0500486_us-ascii Al, I would check the owner keyword in your bind card. That is the plan_table creator you are using when you bind. Barring the explicit use of the owner keyword, it uses the auth of your submitter, when we use prod it's CA7ONL who has the authority but we use the owner keyword so we don't have a bunch of CA7ONL owned tables out there. You can find out where the rows went, check the available plan tables for your program & timestamp, it should clear things up for you. [...]
2419 104 14_Re: -904 error19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM30_Mon, 9 Sep 2002 11:33:00 -0400687_US-ASCII Bind requires exclusive lock on the object's (business tables) databases, in your case though you have the plan tables defined in DB02, bind needs exclusive lock on DB01 and the database should be in RW mode.
Murari Selvakesavan. Data Resource Management. First Health Services Corp. 804.965.7601
>>> alpat225@YAHOO.COM 09/09/02 10:09AM >>>
We have the business table's tablespaces defined in database DB01 and PLAN_TABLE's tablespace defined in database DB02. All the plans use (read & update) tables that are defined in the tablespaces of DB01. We started the database DB01 in RO mode to take the daily image copies which run [...]
2524 108 36_Re: HELP HELP! dDB2 UDB ON NT CRISIS16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM30_Mon, 9 Sep 2002 11:15:59 -0500565_- On Fri, 6 Sep 2002 08:24:06 -0500, Jeremy Schleicher wrote:
>Any trap or dump files created? > > > > Aurora Dell'Anno > ANDLE.COM> cc: > Sent by: DB2 Data Subject: HELP HELP! dDB2 UDB ON NT CRISIS > Base Discussion > List > ASSOC.COM> > > > 09/06/2002 05:59 > AM > Please respond to > DB2 Data Base > Discussion List > > > > > > >Hello list, > >my "problem" of last night has now become a real PROBLEM with all block >caps. > >I created 2 BPs and [...]
2633 111 15_Re: Help please16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM30_Mon, 9 Sep 2002 11:20:59 -0500332_- Hi Leslie and Phil (it's alright for some! I'm working my way through water loggged countries instead ;-)
The situation is still crytical. I have opened a Sev 1 with IBM and I am talking to L3 in the US but we are getting nowhere.
I'll try the db2dart tonight I think and guess what? the client has no backups. [...]
2745 37 33_RTFM & supercilious BP parameters10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 9 Sep 2002 18:33:54 +0200457_us-ascii Hi estimeed Listers
I'm reading my DB2-L mails after a week of vacations. I noticed new BP parameters : 'supercillious' & RTFM plus an high level of flame usage.
I know something about the RTFM parameter but many of my collegues don't have the manuals (even on CD - I'm still missing some manuals as well) nor a web access to read manuals where they can find an explanation about RTFM parameter or to search on DB2-L some infos. [...]
2783 94 36_Re: Job Posting - Support Technician11_Carl Nelson22_Carl.Nelson@ANTHEM.COM30_Mon, 9 Sep 2002 11:53:32 -0500830_us-ascii Tina, How do I get this posted on the InDUG job listing?
Thanks, Carl Nelson 317-287-7222
Pam Greenfield cc: Sent by: DB2 Data Subject: Job Posting - Support Technician Base Discussion List
09/04/2002 04:00 PM Please respond to DB2 Data Base Discussion List
DGI, the world's fastest route to breakthrough DB2 productivity, is looking for a highly qualified support technician to provide technical support and assistance for current and prospective customers. If you are a high-level performer, welcome challenges, and enjoy working with like-minded professionals, continue reading to learn more about this great employment [...]
2878 78 14_Re: -904 error6_Al Pat18_alpat225@YAHOO.COM30_Mon, 9 Sep 2002 10:15:18 -0700576_us-ascii
Thanks Murali. Will it work, if I change the command from -START DATABASE(DB01) ACCESS(RO) to -START DATABASE(DB01) SPACENAM(*) ACCESS(RO) ?. This should leave the database in RW mode and the objects to be image copied in RO mode. Murari Selvakesavan wrote:Bind requires exclusive lock on the object's (business tables) databases, in your case though you have the plan tables defined in DB02, bind needs exclusive lock on DB01 and the database should be in RW mode. Murari Selvakesavan. Data Resource Management. First Health Services Corp. 804.965.7601 [...]
2957 32 50_Plans, Packages, Collections and Stored Procedures14_Michael Vanner19_mvanner@CANTIRE.COM30_Mon, 9 Sep 2002 13:07:26 -0400632_us-ascii We recently implemented a number of new applications developed by an outside consulting firm. The two different teams had entirely different approaches to the set-up of the DB2 application objects (plans, packages, collections, stored procedures). We had only been using packages for infrastructure (DDF, 3-rd party tools and utilities), so we didn't have the expertise or experience to realize what we were getting into. We have had numerous problems with developers binding with the wrong collections, binding the plan but not the package, dbrms in multiple collections and many other combinations and permutations. [...]
2990 114 30_Re: db2udp could not be loaded20_Sitaram Vijay kartik21_vijaykartik@YAHOO.COM30_Mon, 9 Sep 2002 10:47:03 -0700484_us-ascii Hi:
1. Did you install "Application Development Toolkit" (ADT), it comes with the DB2 UDB AIX CD..
2. After installing ADT, do a db2iupdt to update the instance with the latest path and binaries.
3. Apply the fixpacks if you had already applied to the exisiting db2 installation.
it should work now.. there is no need to add the sqllib/function to the path.. the db2profile takes care of it all... atleast so far i have seen. [...]
3105 24 22_-101 After DB2 Upgrade23_Srinivasa R. Guntupalli27_srinivasa.guntupalli@BT.COM30_Mon, 9 Sep 2002 18:48:54 +0100347_iso-8859-1 HI All,
It was mentioned in the DB2 V7 Installation manual that after migration it is possible to receive -101 SQL code while trying to execute Long and complicate SQLs.
Has any one, who migrated to V7 faced this problem.
Does any one have an idea how long the SQL should be to avoid -101. Thanks in Advance.. [...]
3130 61 26_Re: -101 After DB2 Upgrade15_John C. Lendman20_John_Lendman@FPL.COM30_Mon, 9 Sep 2002 14:50:57 -0400470_us-ascii Yes, I believe this was discussed before. I think all you need to do is rebind.
John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413
"Srinivasa R. Guntupalli" To: DB2-L@LISTSERV.YLASSOC.COM Subject: -101 After DB2 Upgrade Sent by: "DB2 Data Base Discussion List"
09/09/02 01:48 PM Please respond to "DB2 Data Base Discussion List" [...]
3192 83 26_Re: -101 After DB2 Upgrade27_Hilton, Tina, BmS - NMI -PM29_Tina.Hilton@ARVATOSYSTEMS.COM30_Mon, 9 Sep 2002 14:04:02 -0500279_iso-8859-1 As I remember it, the only way to fix it is to rewrite the SQL to make it less complex. I don't think it's a very common thing to hit though. This was also supposed to be a potential problem with V5 - V6 upgrade. We haven't run into it yet in our upgrades to V6. [...]
3276 25 21_SQLEJ.REPLACE_CLASSES16_Swinski, Kenneth23_KSwinski@MASSMUTUAL.COM30_Mon, 9 Sep 2002 15:11:36 -0400514_iso-8859-1 When replacing a jar file used in a DB2 UDB stored procedure or user-defined function definition, IBM makes reference to a command 'SQLEJ.REPLACE_CLASSES' in a document on DB2 UDB V7 Incompatibilities (http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/documen t.d2w/report?fn=db2v7d0db2d0292.htm). However, we can't find either 'SQLEJ.REPLACE_CLASSES' anywhere, or in fact any other reference to it, and suspect that they mean 'SQLJ' rather than 'SQLEJ'. Can anyone confirm this? [...]
3302 33 26_Re: -101 After DB2 Upgrade12_Jim Harrison17_jharrison@GMX.NET30_Mon, 9 Sep 2002 15:14:58 -0400570_us-ascii We actually ran into this on V6. Our resident Rexx guru wrote something to smoosh it all up - technical term meaning it takes out the extra spaces and blank lines.
I seem to remember 32K as being the max, but I could be mistaken.
At 06:48 PM 9/9/2002 +0100 Srinivasa R. Guntupalli said: >HI All, > >It was mentioned in the DB2 V7 Installation manual that after migration it >is possible to receive -101 SQL code while trying to execute Long and >complicate SQLs. > >Has any one, who migrated to V7 faced this problem. > >Does any one [...]
3336 89 22_XML Extenders Question13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM30_Mon, 9 Sep 2002 16:20:54 -0400459_US-ASCII List,
With our DB2 V7 for z/OS and OS/390 upgrade this spring I "installed" the DB2 XML Extenders. At least I think I did. I did the appropriate RECEIVE and APPLY SMP/E jobs for the XML Extenders FMID; I found no "Enable XML Extender" paperwork anywhere, so I stopped there.
Upon perusing the manual "DB2 UDB for OS/390 and z/OS V7 XML Extender Administration and Programming" (SC26-9949-00) I chanced upon the following sentence: [...]
3426 101 26_Re: XML Extenders Question11_Tina Hilton29_Tina.Hilton@ARVATOSYSTEMS.COM30_Mon, 9 Sep 2002 16:09:46 -0500583_iso-8859-1 I think that's the low level qualifier of a target dataset that was installed for it.
-----Original Message----- From: Lockwood Lyon [mailto:Lockwood.Lyon@MEIJER.COM] Sent: September 09, 2002 3:21 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: XML Extenders Question
List,
With our DB2 V7 for z/OS and OS/390 upgrade this spring I "installed" the DB2 XML Extenders. At least I think I did. I did the appropriate RECEIVE and APPLY SMP/E jobs for the XML Extenders FMID; I found no "Enable XML Extender" paperwork anywhere, so I stopped there. [...]
3528 105 77_Re: Anyone with a utility to calculate DB2 database Used vs Alloc ated Space?11_Andy Cliffe18_andy.cliffe@BT.COM30_Mon, 9 Sep 2002 22:32:29 +0100678_iso-8859-1 Sorry, the REXX exec was missing a few lines. Here is the full version:
/******** REXX *********/ /* Find tables and indexes which have been allocated a lot more */ /* space than they are using. */ /***********************/ VSXTCHKX: arg DebugOpt subsys .
if DebugOpt = DEBUG then trace i
xtcount = 0 extentlimit=25 /*display datasets with more than this number of extents*/ checklimitmb=500 /*display datasets with this number of MB spare space*/ checklimit = 1024*1024*checklimitmb say 'Checking for datasets with' checklimitmb 'MB or more spare space' say 'Checking for datasets with' extentlimit 'or more extents' /* Read a list [...]
3634 116 98_Keep >>>---------Re: Re: Anyone with a utility to calculate DB2 database Used vs Alloc ated Space?14_Essy Nokhodian24_ENOKHODIAN@AUSTIN.RR.COM30_Mon, 9 Sep 2002 17:01:57 -0500562_iso-8859-1 ----- Original Message ----- From: Andy Cliffe Newsgroups: bit.listserv.db2-l To: Sent: Monday, September 09, 2002 4:32 PM Subject: Re: Anyone with a utility to calculate DB2 database Used vs Alloc ated Space?
Sorry, the REXX exec was missing a few lines. Here is the full version:
/******** REXX *********/ /* Find tables and indexes which have been allocated a lot more */ /* space than they are using. */ /***********************/ VSXTCHKX: arg DebugOpt subsys . [...]
3751 65 37_Re: RTFM & supercilious BP parameters13_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Tue, 10 Sep 2002 08:15:27 +1000402_iso-8859-1 Max, To understand the new parameters you will need a brief etymological lesson. The super-cilia are muscles above the eyes which help prevent your eyebrows from slipping down your face. As a result the new ZPARM parameter VEHL (or Vertical Eyebrow Height Limit) is employed to reduce the frequency with which the RDS (Ridiculous Diatribe System) kicks in to dismiss unwarranted scorn. [...]
3817 18 40_Re: Calling DSNUTILB from appln. program9_Jim Ruddy18_jaruddy@US.IBM.COM30_Mon, 9 Sep 2002 16:59:29 -0500485_- If you are on DB2 fo z.OS and OS/390 Version 7, in the utility input stream you can say (for saxmple):
EXEC SQL SET CURRENT SQLID = 'Murari' ENDEXEC LOAD INTO TABLE ....
Jim Ruddy DB2 for z/OS Developement
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
3836 18 40_Re: Calling DSNUTILB from appln. program9_Jim Ruddy18_jaruddy@US.IBM.COM30_Mon, 9 Sep 2002 16:59:38 -0500485_- If you are on DB2 fo z.OS and OS/390 Version 7, in the utility input stream you can say (for example):
EXEC SQL SET CURRENT SQLID = 'Murari' ENDEXEC LOAD INTO TABLE ....
Jim Ruddy DB2 for z/OS Developement
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
3855 47 54_Re: Plans, Packages, Collections and Stored Procedures14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 10 Sep 2002 08:12:39 +1000703_US-ASCII Here is my suggestions: http://listserv.ylassoc.com/cgi/wa.exe?A2=ind0111A&L=DB2-L&P=R7692
You may need to adjust to fit your current standards
James Campbell
On 9 Sep 2002 at 13:07, Michael Vanner wrote:
> We recently implemented a number of new applications developed by an > outside consulting firm. The two different teams had entirely different > approaches to the set-up of the DB2 application objects (plans, packages, > collections, stored procedures). We had only been using packages for > infrastructure (DDF, 3-rd party tools and utilities), so we didn't have > the expertise or experience to realize what we were getting into. We have > had [...]
3903 32 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 10 Sep 2002 08:12:40 +1000399_US-ASCII No, you don't. An explain will work quite happily with either - any of the specified subsets of columns that were valid at prior versions. A V6 explain will work with the 46 columns desired (not required) at V5; or - additional columns. If you have all the columns for your current version, you can have additional columns. Why? So you can prepare for/fall back from the next version. [...]
3936 105 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?13_David S Waugh16_dsw-dba@JUNO.COM28_Mon, 9 Sep 2002 22:18:53 GMT335_- I have a suspicion that "NAME L" is a cut-and-paste problem; that "L" is the first character of the object being flagged, but it got cut off when it was pasted into the original DB2-L posting. [So I guess it is an "L-Thingy" in that sense]
I found an interesting entry in IBMLink that described a similar 00C9008A error: [...]
4042 82 26_Re: -101 After DB2 Upgrade13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 9 Sep 2002 18:16:04 -0500429_us-ascii Srivinas,
When migrating to V6 or to V7, there are many more situations where the optimizer can merge rather than materialize a SQL statement.
The SQL statement length is 32K. If you exceed this limit (about 450 lines with excess spaces removed or 1400 lines with spaces), you will receive an error stating you have exceeded the statement length (this is a prepare time error and thus not a SQLCODE). [...]
4125 61 17_Re: Sorting NULLS13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 9 Sep 2002 18:25:10 -0500442_us-ascii Ed,
I must admit that I am not familiar with such a setting for an ORDER BY, but new features creep in all of the time.
What I am aware of is that within the DB2 UDB Ranking functions, the clause NULLS FIRST or NULLS LAST can be specified with regard to the ORDER BY clause applicable to these functions. These ORDER BY clauses apply only to the ranking functions and not the ORDER BY coded at the end of a query. [...]
4187 71 39_Stored procedure using transition table13_LOMBARD,Shaun25_shaun.lombard@DEWR.GOV.AU31_Tue, 10 Sep 2002 10:15:29 +1000540_us-ascii List,
I am trying to set up a trigger that calls a stored procedure passing the entire row.
I create the procedure with the TABLE LIKE qual.table AS LOCATOR clause, so far so good. I then create the trigger on table qual.table REFERENCING NEW TABLE NEWCODE and calling the stored procedure with (TABLE NEWCODE). The trigger create fails with -440. I know the procedure exists so I am guessing there is a mismatch with the parms. I am only using the transition table at this stage so the parameters should match. [...]
4259 19 19_DB2 SPB and DSN8ED417_Robert J. Milonas20_XRJMBOB@NETSCAPE.NET30_Mon, 9 Sep 2002 22:00:06 -0400490_iso-8859-1 I created SP1 using SPB. Did a build and run and it reurned ten rows. Created SP2(same sql different SP name) using SPB. Instead of doing an SPB build. I ran batch DSN8ED4 to invoke the REXX-SP (to do what SPB build does) and I got a zero return code. Then I used SPB to do a run on SP2. It runs but returns no output data. I do an SPB build and run on SP2 and still no output data. Anyone have an Idea? The reason for doing this is to create a batch proc to promote a test [...]
4279 238 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Mon, 9 Sep 2002 22:18:18 -0400
4518 104 43_Re: Stored procedure using transition table13_LOMBARD,Shaun25_shaun.lombard@DEWR.GOV.AU31_Tue, 10 Sep 2002 13:23:10 +1000475_us-ascii I have found the problem.
It is addressed with APAR PQ54847 for DB2 V6. I should be getting an authorization failure (-551) not a -440.
Thanks
Shaun
-----Original Message----- From: LOMBARD,Shaun Sent: Tuesday, September 10, 2002 10:15 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Stored procedure using transition table
List,
I am trying to set up a trigger that calls a stored procedure passing the entire row. [...]
4623 51 30_Re: DEFINE NO and mass deletes11_Jon Nolting24_noltatwork@SBCGLOBAL.NET30_Mon, 9 Sep 2002 21:29:56 -0700561_us-ascii Peoplesoft has brought this behaviour to IBM's attention and a fix is supposed to be on the way. I'll try and post the IBM apar number when I get logged on at work.
At 08:57 AM 9/6/02 -0500, you wrote: >A question about indexes created DEFINE NO. I've discovered that an >unqualified delete on an empty table causes the index(es) on the table to >become defined, i.e. the VSAM datasets get created. This also means the >DBD gets logged, which is how this issue came to my attention. > >I'm sure you all are asking "why would anyone issue a [...]
4675 15 31_Mike Tobe is out of the office.0_25_Mike_Tobe@PROGRESSIVE.COM31_Tue, 10 Sep 2002 01:01:48 -0400403_us-ascii I will be out of the office starting 09/09/2002 and will not return until 09/16/2002.
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://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
4691 31 81_Re: Bind unsuccessful when EXPLAIN(YES) but works when EXPLAIN(NO). Any idea why?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 10 Sep 2002 00:16:32 -0500504_- SELECT * FROM SYSIBM.SYSFIELDS WHERE TBCREATOR = '' AND TBNAME = 'PLAN_TABLE';
SELECT EDPROC, VALPROC FROM SYSIBM.SYSTABLES WHERE CREATOR = '' AND NAME = 'PLAN_TABLE' AND EDPROC = ' ' AND VALPROC = ' ';
should both return zero rows.
I suspect that you'll find either a SYSFIELDS.FLDPROC, SYSTABLES.EDPROC or a SYSTABLES.VALPROC containing 'L'. This causes DB2 to search for the routine named L, doesn't find it, and so fails with reason code 00C9008A. [...]
4723 26 24_Pulling Data from Oracle12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Tue, 10 Sep 2002 06:05:03 +0000412_- All, I have a requirement for a client where I have to pull data from Oracle into a DB2 V5 table in S/390 in a daily basis.All the activity I have to do from the S/390 end.Can anybody suggest a method of doing this and what are the necessary changes to be made in the CDB tables in DB2 . This is urgent so kindly suggest what can be done.Also we do not have any tools like Data Joiner or Data Propogater. [...]
4750 163 49_Re: Calling DSNTIAUL utility from a COBOL Program10_Ali OZTURK27_Ali.OZTURK@PAMUKBANK.COM.TR31_Tue, 10 Sep 2002 09:55:13 +0300441_iso-8859-9 Hi Leonardo, Actualy, I didn't call these programs from a cobol program before this. But i used in a rexx program. But you can code as follow. I think ,it must be run properly.
IDENTIFICATION DIVISION. PROGRAM-ID. XXXXXX. ENVIRONMENT DIVISION. CONFIGURATION SECTION. INPUT-OUTPUT SECTION. FILE-CONTROL. DATA DIVISION. FILE SECTION. WORKING-STORAGE SECTION. .... PROCEDURE DIVISION . CALL ÜDSNTIAULÜ. STOP RUN. [...]
4914 62 26_Re: -101 After DB2 Upgrade11_Dirk Johann15_D.Johann@SEG.DE31_Tue, 10 Sep 2002 09:35:50 +0200527_us-ascii We were running into the same problem in our shop a few months ago. Rebind doesn't fix the problem. We rebound all our programs with V7, but didn't help. The only solution IMHO is to shorten the SQL, until it runs again.
Regards, Dirk
Software Engineering GmbH
"Srinivasa R. Guntupalli" An: DB2-L@LISTSERV.YLASSOC.COM Thema: -101 After DB2 Upgrade Gesendet von: DB2 Data Base Discussion List [...]
4977 15 42_db2 v5 upgrade to v7 : more cpu required ?7_Eric Ng22_ngyh@PUBLICBANK.COM.MY31_Tue, 10 Sep 2002 04:11:08 -0500477_- We are going to upgrade our production DB2 V5 to DB2 V7 on our OS/390 host. Generally, assuming we do not make use of the V7 new features yet, would DB2 V7 takes up more mainframe cpu resources ?
regards, eric ng
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
4993 233 49_Re: Calling DSNTIAUL utility from a COBOL Program15_Leonardo Zrycki29_leonardo.zrycki@ACCENTURE.COM31_Tue, 10 Sep 2002 11:21:12 +0200589_iso-8859-1 Thanks Ali. I'll try.
Leonardo
Ali OZTURK cc: Sent by: DB2 Data Base Subject: Re: Calling DSNTIAUL utility from a COBOL Program Discussion List
09/10/2002 08:55 AM Please respond to DB2 Data Base Discussion List
Hi Leonardo, Actualy, I didn't call these programs from a cobol program before this. But i used in a rexx program. But you can code as follow. I think ,it must be run properly. [...]
5227 21 37_Re: RTFM & supercilious BP parameters10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 10 Sep 2002 11:01:24 +0200485_us-ascii Hi hi hi ah ah ah - Hi Steve
Great explanation Steve ! Unfortunately I missed the flame.
I'll send Miss september centerfold to that guy, I think he needs it or something similar.
Cheerz
Max S
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
5249 15 38_db2 v5 upgrade to v7 : is ipl a must ?7_Eric Ng22_ngyh@PUBLICBANK.COM.MY31_Tue, 10 Sep 2002 04:35:50 -0500438_- We are upgrading from db2 v5 to v7 os/390. In the migration step, is the IPL a must ? What is the implication if the IPL is not performed during migration ?
Regards, eric ng
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
5265 40 42_Re: db2 v5 upgrade to v7 : is ipl a must ?10_Ali OZTURK27_Ali.OZTURK@PAMUKBANK.COM.TR31_Tue, 10 Sep 2002 13:49:14 +0300463_iso-8859-9 Migration step needs to add db2 v7 libraries to linklist and apflist. If you add these libraries to linklist and apflist via dynamic set command (ask mvs system programmer), you don't need IPL during migration.
regards Ali Ozturk
-----Original Message----- From: Eric Ng [mailto:ngyh@PUBLICBANK.COM.MY] Sent: Tuesday, September 10, 2002 12:36 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: db2 v5 upgrade to v7 : is ipl a must ? [...]
5306 48 26_Re: XML Extenders Question12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 10 Sep 2002 05:10:28 -0700580_us-ascii The DXXGPREP job script is in the library hlq.SDXXJCL
Don't forget that before you can run any jobs in this library you must also install the XML Tool kit (5655-J51 XML toolkit for OS/390). Its no charge but must be ordered as well. It doesn't come automatically even though its a pre-requisiste for the XML extenders. --- Lockwood Lyon wrote: > List, > > With our DB2 V7 for z/OS and OS/390 upgrade this spring I "installed" the DB2 > XML Extenders. At least I think I did. I did the appropriate RECEIVE and > APPLY SMP/E jobs for [...]
5355 16 42_Re: db2 v5 upgrade to v7 : is ipl a must ?17_Walter Trovijo Jr18_wtrovijo@IG.COM.BR31_Tue, 10 Sep 2002 09:45:26 -0300390_iso-8859-9 An IPL is needed to refresh subsystem, unless your subsystem was dynamically installed using SETSSI.
Walter Trovijo.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
5372 88 55_Re: Recommended Educational Resources for Certification13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 10 Sep 2002 07:51:18 -0500412_iso-8859-1 James,
I'm sure you didn't mean it the way it sounded regarding the OS/390 Certification book.
As far as I know, the DB2 Universal Database for OS/390 Version 7.1 Certification Guide written by Richard Yevich & Susan Lawson is the only book written with the sole purpose of preparing for the z/OS & OS/390 DBA certification exams. That's why it was authorized and published by IBM. [...]
5461 128 55_Re: Recommended Educational Resources for Certification17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Tue, 10 Sep 2002 15:20:30 +0200406_us-ascii The way I see it, James said "it's ok for Certification, but not otherwise"... which seems to be exactly what you are saying as well. So where's the problem?
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany
James,
I'm sure you didn't mean it the way it sounded regarding the OS/390 Certification book. [...]
5590 25 26_Re: db2empfa and tempspace12_David Harvey17_dmh@ONETEL.NET.UK31_Tue, 10 Sep 2002 08:05:30 -0500424_- Dave,
Some of us ARE indeed interested in db2empfa ! Thanks for the posting of IBM's explanation, especially the tempspace issue.
In a Tivoli environment (AIX with UDB 7.2) there happen to be DMS t/spaces but some are traditional SMS. The issue is that once defined as yes, the "multipage allocation flag" cannot be switched back. Therefore our benchmarks involve Backup/Recover in case of degradation. [...]
5616 58 14_DB2 interfaces14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Tue, 10 Sep 2002 15:18:48 +0200396_- Hello all,
in our application (TSO), we have all the DB2 activity concentrated in one load module. As it runs with TSO, we have the interface DSNELI linked to it.
Now the same load module should also be used with batch, that is, CAF.
We don't want to have two modules, but we'd like to have an interface, which switches dynamically from DSNELI to DSNALI, for example. [...]
5675 50 14_DB2 interfaces14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Tue, 10 Sep 2002 15:41:45 +0200396_- Hello all,
in our application (TSO), we have all the DB2 activity concentrated in one load module. As it runs with TSO, we have the interface DSNELI linked to it.
Now the same load module should also be used with batch, that is, CAF.
We don't want to have two modules, but we'd like to have an interface, which switches dynamically from DSNELI to DSNALI, for example. [...]
5726 106 18_Re: DB2 interfaces10_Ben Dissen22_Ben.Dissen@NEONSYS.COM31_Tue, 10 Sep 2002 09:00:08 -0500344_iso-8859-1 TSO does not "spontaneously" remove LOADed modules from memory. A loaded module will go away when the task (TCB) that issued the LOAD goes away. If you have an ISPF screen abend it may cause a task termination and an automatic DELETE. To make sure the module does not vanish requires that the LOADing rask always remain around. [...]
5833 49 28_Re: Pulling Data from Oracle23_GORRELLA, SRINI (SBCSI)14_sg7974@SBC.COM31_Tue, 10 Sep 2002 10:17:08 -0500308_iso-8859-1 Rakesh -
I did the same type of task just with a JCL ...You need to have ORACLEs CMDLOAD and AUTHLOAD in your S/390 libraries ...Using SQLPLUS program in JCL to pull the data from ORACLE to a flat file ..and use this file in ur DB2 LOAD job ...If you need more info contact me offline [...]
5883 35 42_Re: db2 v5 upgrade to v7 : is ipl a must ?19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Tue, 10 Sep 2002 10:35:56 -0500350_- It depends.
Changing hlq.SDSNLINK (the "early code") requires an IPL. But you should have already synchronized the early code between V5 and V7 before you start (you'll want to have planned for fallback compatibility). And if you've already IPL'ed with that v5 early code you don't have to do it again (and/or with that v7 early code). [...]
5919 32 28_UDB defect on v7.2 fixpack 711_Richard Yan23_dhspa3f@DHS.STATE.IL.US31_Tue, 10 Sep 2002 10:24:03 -0500372_- Defect on "old" fixpack 7. (Reference: IY33275)
We applied the fixpack 7 to our production environment in this past weekend and the next day(this past monday) our production database went down for almost 6 hours, the biggest DB2 down time ever in our department. With the help from IBM, we finally realized the problem came from the fixpack 7 we were using. [...]
5952 139 14_Re: -904 error19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM31_Tue, 10 Sep 2002 12:06:59 -0400290_US-ASCII From what I experienced in the past, bind requires a drain lock on the database itself. No matter what you try to do as you mentioned. Looks like it is a timing issue in your case, you probably have to schedule your binds after the imagecopy job or prior and not in parallel. [...]
6092 48 42_Re: db2 v5 upgrade to v7 : is ipl a must ?13_David S Waugh16_dsw-dba@JUNO.COM29_Tue, 10 Sep 2002 16:08:40 GMT305_- Hi Eric:
The IPL recommended in the DB2 Migration process is primarily there to put the new DB2 version's ERLY code (the modules residing in SDSNLINK) into play. The DB2 ERLY code is executed during IPL and registers DB2 as a formal MVS Subsystem by updating the MVS Subsystem Vector Table. [...]
6141 58 32_Re: UDB defect on v7.2 fixpack 713_David S Waugh16_dsw-dba@JUNO.COM29_Tue, 10 Sep 2002 16:25:47 GMT376_- A question I have is:
Assuming that the "new" copy of FP7 has fixes for the problems that the "old" copy had, if we've already applied the "old" FP7 to our DB2 UDB system, can we simply apply the "new" FP7 (and hopefully overlay the "old" FP7 code), or do we need to back off to our backup copy of our system (taken at FP6) before we try applying the "new" FP7? [...]
6200 137 14_Re: -904 error19_Murari Selvakesavan22_MSelvakesavan@FHSC.COM31_Tue, 10 Sep 2002 18:26:46 +0200290_US-ASCII From what I experienced in the past, bind requires a drain lock on the database itself. No matter what you try to do as you mentioned. Looks like it is a timing issue in your case, you probably have to schedule your binds after the imagecopy job or prior and not in parallel. [...]
6338 84 18_Re: DB2 interfaces13_David S Waugh16_dsw-dba@JUNO.COM29_Tue, 10 Sep 2002 16:32:28 GMT530_- I'm curious: would putting the load module(s) in VLF (TSO/ISPF Virtual Lookaside Facility) make any difference?
David Waugh, NCW DSW Consulting & Services
======================== BUMPER STICKERS FOR LIFE ======================== > If Barbie is so popular, why do you have to buy her friends?
---------- Ben Dissen writes:
From: Ben Dissen To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: DB2 interfaces Date: Tue, 10 Sep 2002 09:00:08 -0500 [...]
6423 82 14_Re: -904 error11_David Nance16_DWNance@FHSC.COM31_Tue, 10 Sep 2002 12:47:49 -0400318_US-ASCII The answer is "IT DEPENDS". If you are trying to bind a program that would use the tablespace that you started as read only then no, otherwise yes. As Murari said in his last mail to you, you might want to work on the scheduling aspect of it.
Dave Nance First Health Services, Corp. (804)527-6841 [...]
6506 19 43_Re: -Resetting the value in Identity Column14_William Poston21_william.poston@DB.COM31_Tue, 10 Sep 2002 11:43:38 -0600227_us-ascii Does any one know if the Alter Table Alter Column Reset With
is available on OS390? I know it works on distributed UDB databases. Is there a PTF for this on os390?
6526 84 26_Re: -101 After DB2 Upgrade13_David S Waugh16_dsw-dba@JUNO.COM29_Tue, 10 Sep 2002 16:50:26 GMT445_- In the vague recesses of my memory banks, I seem to remember that you can also exceed the 32K limit even though you don't think you should have.
It goes something like this: Through things like Transitive Predicate Closure, DB2 "re-writes" your SQL under the covers, so what started out as an SQL query that was slightly less than 32K can get flagged as exceeding the 32K limit because the DB2 Optimizer added a line or two to it. [...]
6611 57 37_Re: RTFM & supercilious BP parameters13_David S Waugh16_dsw-dba@JUNO.COM29_Tue, 10 Sep 2002 16:55:45 GMT730_- Man, are we getting a wee bit obscure here, or WHAT?
David
P.S. Is it ignoramii or ignoramuses?
---------- Steve Tennant writes:
From: Steve Tennant To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: RTFM & supercilious BP parameters Date: Tue, 10 Sep 2002 08:15:27 +1000
Max, To understand the new parameters you will need a brief etymological lesson. The super-cilia are muscles above the eyes which help prevent your eyebrows from slipping down your face. As a result the new ZPARM parameter VEHL (or Vertical Eyebrow Height Limit) is employed to reduce the frequency with which the RDS (Ridiculous Diatribe System) kicks [...]
6669 41 24_Automatic Rebind in CICS14_Mark McCormack27_mamccormack@STATESTREET.COM31_Tue, 10 Sep 2002 13:29:14 -0400480_us-ascii I post this for a colleague. Any help will be appreciated. ------------------------------------------------------------------------------------------------------------------------------
I have a question regarding the Automatic Rebind Option when dealing with CICS DB2 programs.
We recently dropped and re-created an index for a particular table. This caused several DB2 packages to become invalid, but not inoperative, (according to the DB2 catalog). [...]
6711 154 26_Re: -101 After DB2 Upgrade13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 10 Sep 2002 12:31:01 -0500509_iso-8859-1 David,
No you are not wet.....
The 32K limit is purely a textual limit, and is a separate issue than the -101 problem.
Consider that there is a door into the SQL Optimization room.
To fit through the door the query you write you must be smaller than 32K (before DB2 does anything to it). Now if you are in SPUFI that includes every space and blank line, so you only get about 450 lines before you blow the limit. DSNTEP2 however removes the spaces and blank lines. [...]
6866 17 32_Re: UDB defect on v7.2 fixpack 711_Richard Yan23_dhspa3f@DHS.STATE.IL.US31_Tue, 10 Sep 2002 12:35:19 -0500494_- David,
What We did to fix the problem is that we downloaded the fixpack 7 from DB2 support website again and install it on top of the old fp7, then run db2iupdt. This seems to fix the problem with the old fp7.
Richard Yan.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
6884 148 14_Re: -904 error31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Tue, 10 Sep 2002 18:48:29 +0100477_iso-8859-1 Hi Dave/Murari,
I am still not sure why a database, where we have tables used in the DBRMs resides, will be locked exclusively during bind. I feel there is no activity performed during BIND by DB2 on the tables used by DBRM. The PLAN_TABLE in question resides on the different database and BIND with EXPLAIN(YES) should work. As far as i understand, DB2 looks for catalog tables while binding. The optimizer can look for the database size etc using DBD. [...]
7033 92 37_Re: RTFM & supercilious BP parameters16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM31_Tue, 10 Sep 2002 12:48:27 -0500407_- I think it's ignorami with one i ;-)
And Steve this was a MASTERPIECE...
Ciao.
Aurora
not yet certified but with the whitecoats working on it in a hurry with all my problems with "Little" DB2 - a BIG pain in the neck!
-----------------------------------------------------------------
On Tue, 10 Sep 2002 16:55:45 GMT, David S Waugh wrote: [...]
7126 28 33_High CPU in batch job after maint12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Tue, 10 Sep 2002 13:53:20 -0400324_us-ascii Hello All. We recently applied maint to our production DB2's on Saturday night and now we have a batch Peoplesoft payroll job that went from 3 minutes DB2 CPU to 55 minutes DB2 CPU. It is also using the heck out of the RID Pool now where before the use was very minor. Has anyone else had this problem or any ideas?
7155 83 28_Re: Automatic Rebind in CICS31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN31_Tue, 10 Sep 2002 19:04:58 +0100361_iso-8859-1 Mark,
I am not aware of any specific Automatic Rebind parameter from CICS. There may be a possibility that the automatic rebind failed due to contention. Its better if we can check the Automatic Rebind always fails. And if it does, try using REBIND command instead of BIND to trap the error message due to which REBIND/AUTOREBIND failed. [...]
7239 43 37_Re: RTFM & supercilious BP parameters12_Rohn Solecki19_Rohn.Solecki@MTS.CA31_Tue, 10 Sep 2002 13:16:33 -0500508_us-ascii I always thought it was ignoramii, but my Random House Dictionary says ignoramuses, go figure!
David S Waugh cc: Sent by: DB2 Data Subject: Re: RTFM & supercilious BP parameters Base Discussion List
2002/09/10 11:55 AM Please respond to DB2 Data Base Discussion List
Man, are we getting a wee bit obscure here, or WHAT? [...]
7283 92 14_Re: -904 error12_Sanjeev ....24_sanjeev_db2l@YAHOO.CO.IN31_Tue, 10 Sep 2002 20:11:47 +0200477_iso-8859-1 Hi Dave/Murari,
I am still not sure why a database, where we have tables used in the DBRMs resides, will be locked exclusively during bind. I feel there is no activity performed during BIND by DB2 on the tables used by DBRM. The PLAN_TABLE in question resides on the different database and BIND with EXPLAIN(YES) should work. As far as i understand, DB2 looks for catalog tables while binding. The optimizer can look for the database size etc using DBD. [...]
7376 36 32_Re: UDB defect on v7.2 fixpack 711_Richard Yan23_dhspa3f@DHS.STATE.IL.US31_Tue, 10 Sep 2002 13:21:49 -0500516_- To all the listeners sening me emails regarding the fixpack 7:
We are using AIX version of DB2. The db2level for old fixpack 7 reads:
DB21085I Instance "vcmtest" uses DB2 code release "SQL07025" with level identifier "03060105" and informational tokens "DB2 v7.1.0.65", "s020616" and "U482561".
The new fixpack 7 reads:
DB21085I Instance "db2prod" uses DB2 code release "SQL07025" with level identifier "03060105" and informational tokens "DB2 v7.1.0.68", "s020616" and "U484480". [...]
7413 101 37_Re: RTFM & supercilious BP parameters16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Tue, 10 Sep 2002 14:32:33 -0400678_us-ascii idtent
David S Waugh cc: (bcc: Thomas E. Faglon/Telcordia) Subject: Re: RTFM & supercilious BP parameters 09/10/02 12:55 PM Please respond to DB2 Data Base Discussion List
Man, are we getting a wee bit obscure here, or WHAT?
David
P.S. Is it ignoramii or ignoramuses?
---------- Steve Tennant writes:
From: Steve Tennant To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: RTFM & supercilious BP parameters Date: Tue, 10 Sep 2002 08:15:27 +1000 [...]
7515 96 12_Archive logs13_Wolf, Melissa26_Wolf.Melissa@PRINCIPAL.COM31_Tue, 10 Sep 2002 13:50:29 -0500509_iso-8859-1 Hi All! -
I am researching if we should keep the dual archive logging that we do today or if having one archive log is enough. A little background...We used to have 2 DASD sites and one archive log went to site one and the other to site two and only used for a recovery situation. Now the two sites have become one and we are using GDPS to mirror the combined site to site three. So if we lose either site one, site two or both, we would go to site three and still have all the data to [...]
7612 59 28_Re: Automatic Rebind in CICS12_Sanjeev ....24_sanjeev_db2l@YAHOO.CO.IN31_Tue, 10 Sep 2002 20:42:46 +0200361_iso-8859-1 Mark,
I am not aware of any specific Automatic Rebind parameter from CICS. There may be a possibility that the automatic rebind failed due to contention. Its better if we can check the Automatic Rebind always fails. And if it does, try using REBIND command instead of BIND to trap the error message due to which REBIND/AUTOREBIND failed. [...]
7672 71 44_Sybase ECMap and IDENTITY columns in DB2 UDB14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Tue, 10 Sep 2002 13:57:43 -0500499_us-ascii Environment is DB2 UDB PE V7 with Fixpak 6 installed on Windows/NT (our development environment), Sybase ECMap 2.9
We're having a little problem that I cannot seem to find a complete solution to. I spent the better part of the day yesterday RTFM'ing and searching the Web, but cannot seem to find more details on a "fix" I need to make. I was hoping there was someone out there who is also using Sybase ECMap and DB2 UDB together who knows what the details of the solution are. [...]
7744 82 28_Re: Automatic Rebind in CICS0_19_csutfin@AMSOUTH.COM31_Tue, 10 Sep 2002 13:57:38 -0500586_us-ascii There is a ZPARM that allows or dis-allows automatic Rebind, regardless of where the package or plan is running.
The parameter is ABIND. If it is set to NO DB2 will not "rebind" invalid packages or plans.
Check with your DB2 System Programmer.
Carol Sutfin Corporate DBA AmSouth Bank (205)326-5214 Fax:(205)326-5613 csutfin@amsouth.com
Mark McCormack cc: Sent by: DB2 Data Subject: Automatic Rebind in CICS Base Discussion List [...]
7827 59 37_Re: High CPU in batch job after maint16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM31_Tue, 10 Sep 2002 12:08:10 -0700422_iso-8859-1 Jeff,
I do know from a recent experience that when we upgraded to V6, some of the Peoplesoft batch jobs started running longer due to a different access path taken by the optimizer based on statistics in the catalog tables SYSIBM.SYSCOLDIST and SYSIBM.SYSCOLDISTSTATS. What we ended up doing was deleting rows from these respective tables to get the SQL to perform in a manner prior to the upgrade. [...]
7887 16 49_Leon Katsnelson/Toronto/IBM is out of the office.0_15_leon@CA.IBM.COM31_Tue, 10 Sep 2002 14:58:46 -0400481_us-ascii I will be out of the office starting September 10, 2002 and will not return until September 16, 2002.
I will be away on vacation. In case of emergency I can be reached on my cell phone (available in BluePages)
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
7904 43 37_Re: RTFM & supercilious BP parameters12_Rohn Solecki19_Rohn.Solecki@MTS.CA31_Tue, 10 Sep 2002 14:20:07 -0500508_us-ascii I always thought it was ignoramii, but my Random House Dictionary says ignoramuses, go figure!
David S Waugh cc: Sent by: DB2 Data Subject: Re: RTFM & supercilious BP parameters Base Discussion List
2002/09/10 11:55 AM Please respond to DB2 Data Base Discussion List
Man, are we getting a wee bit obscure here, or WHAT? [...]
7948 99 12_Archive logs13_Wolf, Melissa26_Wolf.Melissa@PRINCIPAL.COM31_Tue, 10 Sep 2002 21:22:49 +0200509_iso-8859-1 Hi All! -
I am researching if we should keep the dual archive logging that we do today or if having one archive log is enough. A little background...We used to have 2 DASD sites and one archive log went to site one and the other to site two and only used for a recovery situation. Now the two sites have become one and we are using GDPS to mirror the combined site to site three. So if we lose either site one, site two or both, we would go to site three and still have all the data to [...]
8048 67 18_Re: DB2 interfaces14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Tue, 10 Sep 2002 21:26:57 +0200487_- Hello all,
thanks for your replies.
If someone wants to do similar things, one more hint:
there are also CSECTs named DSNWLI with the same name in DSNELI and DSNALI. So I had to rename them too. Look at my control statements for the linkage editor:
MODE AMODE(31),RMODE(ANY) CHANGE DSNHLI(EDSNHLI) CHANGE DSNWLI(EDSNWLI) INCLUDE DD1(DSNELI) CHANGE DSNHLI(ADSNHLI) CHANGE DSNWLI(ADSNWLI) INCLUDE DD1(DSNALI) ENTRY DSNALFLI ALIAS DSNHLI NAME DSNALFLI(R) [...]
8116 49 16_Re: Archive logs16_Michael McCawley20_Mike_DBA@HOTMAIL.COM31_Tue, 10 Sep 2002 15:10:29 -0500330_iso-8859-1 Melissa -
My shops have always kept both A and B archive sets.
There have been several occasions (over the past 15 years, at my own shops and at others) when the "A" copy was unavailable (lost, corrupt, scratched, overwritten, en route to the DR site, etc.) and the "B" copy was absolutely required. [...]
8166 48 18_Re: DB2 interfaces14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Tue, 10 Sep 2002 22:02:44 +0200487_- Hello all,
thanks for your replies.
If someone wants to do similar things, one more hint:
there are also CSECTs named DSNWLI with the same name in DSNELI and DSNALI. So I had to rename them too. Look at my control statements for the linkage editor:
MODE AMODE(31),RMODE(ANY) CHANGE DSNHLI(EDSNHLI) CHANGE DSNWLI(EDSNWLI) INCLUDE DD1(DSNELI) CHANGE DSNHLI(ADSNHLI) CHANGE DSNWLI(ADSNWLI) INCLUDE DD1(DSNALI) ENTRY DSNALFLI ALIAS DSNHLI NAME DSNALFLI(R) [...]
8215 170 26_Re: -101 After DB2 Upgrade13_David S Waugh16_dsw-dba@JUNO.COM29_Tue, 10 Sep 2002 20:36:46 GMT315_- Thanks once again Terry for the good information. Maybe not wet, just a little damp...
Just out of curiousity, would the limit for, say, Static SQL statements coded in a COBOL program also be ~ 455 lines (32,767 max divided by 72 chars per line)?
Same for Dynamic SQL coded in a COBOL program? [...]
8386 114 37_Re: RTFM & supercilious BP parameters15_Sniderman, Karl21_ksniderman@BCBSOK.COM31_Tue, 10 Sep 2002 15:57:23 -0500531_iso-8859-1 What's the plural of hippopotamus? Karl
-----Original Message----- From: Aurora Dell'Anno [mailto:aurora_dellanno@CANDLE.COM] Sent: Tuesday, September 10, 2002 12:48 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: RTFM & supercilious BP parameters
I think it's ignorami with one i ;-)
And Steve this was a MASTERPIECE...
Ciao.
Aurora
not yet certified but with the whitecoats working on it in a hurry with all my problems with "Little" DB2 - a BIG pain in the neck! [...]
8501 98 55_Re: Recommended Educational Resources for Certification12_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 11 Sep 2002 09:58:27 +1200456_us-ascii Ah, Michael. How I miss your ability to see things in absolutes. James is right, Terry's right, all is right in the world. As a fluent English speaker I would have thought you could have seen immediately where the problem was. Or are you just being obtuse. Anyway, it's simple really, as I suspect you already know. James gently nudged Terry's button, that's all. No harm done to either side, I believe; they're both big boys (right, guys?). [...]
8600 18 19_DB2 Connect PE V7.216_Evelyn J. Runyon28_runyon.evelyn@CORP.SYSCO.COM31_Tue, 10 Sep 2002 17:05:14 -0500566_- How do I choose what Username and Password to use during the Setup of DB2 Connect PE V7.2 on Windows clients? What are the ramifications of using DB2ADMIN or different usernames for each user in my network? We have one OS/390 DB2 that the DB2 Connect clients (several Windows PE clients and 1 DB2/DW on AIX) connect to. Will diagnostics be easier for network techsupport, if any clients run amok, if the usernames are unique? Or is that username even visible to network technicians? I'm a mainframe person and may not be saying the right buzz words to ask my [...]
8619 25 22_extracting DB2 version7_Shaja R19_radhangan@YAHOO.COM31_Tue, 10 Sep 2002 15:01:01 -0700638_us-ascii Hi list,
Is there any SQL statement available to extract the version of DB2 from an application program...? (or any other simple methods other than to dig into control blocks...)
Shaja
__________________________________________________ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
8645 73 43_Re: Stored procedure using transition table10_Shery Hepp17_schepp@SRPNET.COM31_Tue, 10 Sep 2002 15:15:18 -0700483_ISO-8859-1 Shaun- check out the schema ids for both the trigger and sp. Your trigger needs to have the same schema as the schema of the stored proc.
Shery
-----Original Message----- From: LOMBARD,Shaun [mailto:shaun.lombard@DEWR.GOV.AU] Sent: Monday, September 09, 2002 5:15 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Stored procedure using transition table
List,
I am trying to set up a trigger that calls a stored procedure passing the entire row. [...]
8719 19 36_Re: HELP HELP! dDB2 UDB ON NT CRISIS19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Tue, 10 Sep 2002 17:17:01 -0500468_- I'm not a NT person... but did you see apar descriptions for IY22434 and/or IY05745?
Could this still be a problem today on UDB albeit on the NT platform? Hoping this might help.
Rich Humphris CNA
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
8739 76 18_Re: DB2 interfaces14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 11 Sep 2002 08:31:20 +1000335_US-ASCII If you use the program with CAF, what is creating the CAF environment? What I would do is get the routine that issues the CAF CONNECT etc to call an (assembler) subroutine that loads DSNHLI2 and DSNWLI2 and for each issues an IDENTIFY for DSNHLI or DSNWLI. Let the application program dynamically load DSNHLI and DSNWLI. [...]
8816 59 37_Re: RTFM & supercilious BP parameters14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 11 Sep 2002 08:40:53 +1000608_US-ASCII Perhaps they are the ignoramii. As it comes from Latin, it has (make that "should have") a Latin plural.
But then we use "indexes" and not "indicies", so we can't complain too loudly.
James Campbell
On 10 Sep 2002 at 13:16, Rohn Solecki wrote:
> I always thought it was ignoramii, but my Random House Dictionary says > ignoramuses, go figure! > > > > > David S Waugh > > cc: > Sent by: DB2 Data Subject: Re: RTFM & supercilious BP parameters > Base Discussion > List > LASSOC.COM> > > > [...]
8876 56 54_Re: Plans, Packages, Collections and Stored Procedures10_Jerry Long24_Jerry.Long@MACQUARIE.COM31_Wed, 11 Sep 2002 11:20:24 +1000438_iso-8859-1 Hi Michael,
Under V7, stored procedures are handled differently than in V5. You now CREATE a stored proc rather than insert into SYSIBM.SYSPROCEDURES, and there are explicit GRANTs required on the stored proc rather than the package associated with it. The primary location of stored proc definitions is now a Catalog table called SYSIBM.SYSROUTINES, so any processing around SYSPROCEDURES will need to be changed. [...]
8933 113 55_Re: Recommended Educational Resources for Certification14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 10 Sep 2002 20:27:30 -0500456_- I did not say or, I hope, imply Michael's "not otherwise".
Some publishers rate their books on a "Beginner - Intermediate - Advanced - Expert" scale. I would rate Yevich+Lawson in the Beginner to Intermediate range - remember that this assesment is based on "[f]lipping though it in a bookshop" and should be treated accordingly. You (or rather "a beginner") will continue to find it usefull for at least some time after certification level. [...]
9047 146 55_Re: Recommended Educational Resources for Certification13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 10 Sep 2002 20:56:08 -0500432_iso-8859-1 James,
My apologies for placing you in the position that you needed to respond.
As you stated, my 2nd and 3rd paragraphs were not directed to you and should have been addressed to Scott who originally asked the question.
Thankyou Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com [...]
9194 105 28_Re: Automatic Rebind in CICS6_Al Pat18_alpat225@YAHOO.COM31_Tue, 10 Sep 2002 19:20:24 -0700596_us-ascii
Automatic rebinds do fail for contention. If they do, only option left is to issue a manual bind. To avoid -904 errors for our online users, we always issue manual binds if the plans/packages become invalid. "Sanjeev ...." wrote:Mark,
I am not aware of any specific Automatic Rebind parameter from CICS. There may be a possibility that the automatic rebind failed due to contention. Its better if we can check the Automatic Rebind always fails. And if it does, try using REBIND command instead of BIND to trap the error message due to which REBIND/AUTOREBIND failed. [...]
9300 108 28_Re: Automatic Rebind in CICS6_Al Pat18_alpat225@YAHOO.COM31_Tue, 10 Sep 2002 19:23:34 -0700619_us-ascii
Correction: Automatic rebinds do fail for contention. If they do, only option left is to issue a manual REbind. To avoid -904 errors for our online users, we always issue manual REbinds if the plans/packages become invalid.
"Sanjeev ...." wrote:Mark,
I am not aware of any specific Automatic Rebind parameter from CICS. There may be a possibility that the automatic rebind failed due to contention. Its better if we can check the Automatic Rebind always fails. And if it does, try using REBIND command instead of BIND to trap the error message due to which REBIND/AUTOREBIND failed. [...]
9409 108 28_Re: Automatic Rebind in CICS6_Al Pat18_alpat225@YAHOO.COM31_Tue, 10 Sep 2002 19:23:37 -0700619_us-ascii
Correction: Automatic rebinds do fail for contention. If they do, only option left is to issue a manual REbind. To avoid -904 errors for our online users, we always issue manual REbinds if the plans/packages become invalid.
"Sanjeev ...." wrote:Mark,
I am not aware of any specific Automatic Rebind parameter from CICS. There may be a possibility that the automatic rebind failed due to contention. Its better if we can check the Automatic Rebind always fails. And if it does, try using REBIND command instead of BIND to trap the error message due to which REBIND/AUTOREBIND failed. [...]
9518 207 26_Re: -101 After DB2 Upgrade13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 10 Sep 2002 22:04:10 -0500315_iso-8859-1 David,
I am going to admit that I have never looked into this for static or dynamic in a program. All of the super large queries I have worked with are dynamic and run outside of an application program. The queries get really large when people try to do everything in a single SQL statement. [...]
9726 115 28_Re: Automatic Rebind in CICS6_Al Pat18_alpat225@YAHOO.COM31_Wed, 11 Sep 2002 05:07:45 +0200596_us-ascii
Automatic rebinds do fail for contention. If they do, only option left is to issue a manual bind. To avoid -904 errors for our online users, we always issue manual binds if the plans/packages become invalid. "Sanjeev ...." wrote:Mark,
I am not aware of any specific Automatic Rebind parameter from CICS. There may be a possibility that the automatic rebind failed due to contention. Its better if we can check the Automatic Rebind always fails. And if it does, try using REBIND command instead of BIND to trap the error message due to which REBIND/AUTOREBIND failed. [...]
9842 118 28_Re: Automatic Rebind in CICS6_Al Pat18_alpat225@YAHOO.COM31_Wed, 11 Sep 2002 05:07:45 +0200619_us-ascii
Correction: Automatic rebinds do fail for contention. If they do, only option left is to issue a manual REbind. To avoid -904 errors for our online users, we always issue manual REbinds if the plans/packages become invalid.
"Sanjeev ...." wrote:Mark,
I am not aware of any specific Automatic Rebind parameter from CICS. There may be a possibility that the automatic rebind failed due to contention. Its better if we can check the Automatic Rebind always fails. And if it does, try using REBIND command instead of BIND to trap the error message due to which REBIND/AUTOREBIND failed. [...]
9961 118 28_Re: Automatic Rebind in CICS6_Al Pat18_alpat225@YAHOO.COM31_Wed, 11 Sep 2002 05:07:45 +0200619_us-ascii
Correction: Automatic rebinds do fail for contention. If they do, only option left is to issue a manual REbind. To avoid -904 errors for our online users, we always issue manual REbinds if the plans/packages become invalid.
"Sanjeev ...." wrote:Mark,
I am not aware of any specific Automatic Rebind parameter from CICS. There may be a possibility that the automatic rebind failed due to contention. Its better if we can check the Automatic Rebind always fails. And if it does, try using REBIND command instead of BIND to trap the error message due to which REBIND/AUTOREBIND failed. [...]
10080 38 26_Re: extracting DB2 version10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 10 Sep 2002 23:58:08 -0500724_- Hi Shaja,
One way is to issue a "DIS GROUP" DSN command. The version is listed under DB2 LVL.
Regards, Kals
On Tue, 10 Sep 2002 15:01:01 -0700, Shaja R wrote:
>Hi list, > >Is there any SQL statement available to extract the >version of DB2 from an application program...? >(or any other simple methods other than to dig into > control blocks...) > >Shaja > > >__________________________________________________ >Yahoo! - We Remember >9-11: A tribute to the more than 3,000 lives lost >http://dir.remember.yahoo.com/tribute > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage [...]
10119 157 41_AW: Re: RTFM & supercilious BP parameters41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Wed, 11 Sep 2002 07:38:04 +0200456_iso-8859-1 Hippopotami, and it should be ignorami, but then again my Latin is somewhat rusty, so the plural could be ignoramus as well - pronounced with a loooooong 'u' - oh, what the heck, frankly I couldn't care less
Ruediger
-----Ursprüngliche Nachricht----- Von: Sniderman, Karl [mailto:ksniderman@BCBSOK.COM] Gesendet: Dienstag, 10. September 2002 22:57 An: DB2-L@LISTSERV.YLASSOC.COM Betreff: Re: RTFM & supercilious BP parameters [...]
10277 45 36_Re: HELP HELP! dDB2 UDB ON NT CRISIS16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM31_Wed, 11 Sep 2002 01:17:30 -0500327_- Hi Rich, Lesl, Phil,
well last night the situation came to a strange conclusion.
How? Well all of a sudden applications started connecting to the DB again - without anything being done apart from a series of meetings to discuss the situation. Don't ever tell me again that meetings are a waste of time ;-) [...]
10323 33 42_Poor SQL, any ideas? DB2 UDB on Windows NT16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM31_Wed, 11 Sep 2002 01:29:29 -0500322_- Hi list,
hopefully last part of this saga here on Windows on an NT box.
I have an application creating reports from tables in my DB, and the query for the monthly report has been running since 18.20 last night, which makes it 14 hours while I write.
Any ideas of how to improve this pesky SQL? [...]
10357 48 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM31_Wed, 11 Sep 2002 01:45:30 -0500584_- Forgot to mention that I have an index on T1 on these columns: APPLICATION (asc) RESPONSE (asc) ELAPSED (asc)
and that T1 has approx 51 million row, while T2 has approx 25,000.
On Wed, 11 Sep 2002 01:29:29 -0500, Aurora Dell'Anno wrote:
>Hi list, > >hopefully last part of this saga here on Windows on an NT box. > >I have an application creating reports from tables in my DB, and the query >for the monthly report has been running since 18.20 last night, which makes >it 14 hours while I write. > >Any ideas of how to improve [...]
10406 82 19_with hold &rollback10_Ozgur Ozen13_oozen@YKB.COM31_Wed, 11 Sep 2002 10:16:53 +0300701_Windows-1254 why a cursor opened with hold option within a nonterminal oriented task , is not closed when exec cics rollback is issued, thanks.
OZGUR OZEN YAPI VE KREDI BANKASI A.S. / TEKNOLOJI YONETIMI Tel : 262 647 32 33 Fax : 262 647 17 11 www.ykteknoloji.com
________________________________________________________ Bu mesajda, yalnizca muhatabini ilgilendiren, kisiye veya kuruma özel bilgiler yer aliyor olabilir. Mesajin muhatabi degilseniz, içerigini ve varsa ekindeki dosyalari kimseye aktarmayiniz ya da kopyalamayiniz. Böyle bir durumda lütfen göndereni uyarip, mesaji imha ediniz. Göstermis oldugunuz hassasiyetten ötürü tesekkür ederiz. [...]
10489 115 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 11 Sep 2002 09:31:36 +0200365_us-ascii Try creating an index on T2( IP ).
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany
Forgot to mention that I have an index on T1 on these columns: APPLICATION (asc) RESPONSE (asc) ELAPSED (asc)
and that T1 has approx 51 million row, while T2 has approx 25,000. [...]
10605 72 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM31_Wed, 11 Sep 2002 02:44:07 -0500601_- Hello Doc!
how are things? I forgot to mention that index too, on IP in T2. The query is still running, by the way, and now it's 15.5 hours...
Did you have a nice hol? Clear skies?
Thanks and ciao,
Aurora
On Wed, 11 Sep 2002 09:31:36 +0200, Dr. Michael Ebert wrote:
>Try creating an index on T2( IP ). > >Dr. Michael Ebert >DB2 Database Administrator >aMaDEUS Data Processing >Erding / Munich, Germany > > > > >Forgot to mention that I have an index on T1 on these columns: >APPLICATION (asc) >RESPONSE (asc) >ELAPSED (asc) > >and [...]
10678 86 20_help with a sqlquery30_=?iso-8859-1?q?Vijay=20Salvi?=23_vithalvijay@YAHOO.CO.IN31_Wed, 11 Sep 2002 08:57:35 +0100431_iso-8859-1
Hi DB2-Listers,
Could anyone please help me write a query for following req :
1) My input table is like this :
C_Date Type Value
Jan 2002 A 20
Jan 2002 B 100
Jan 2002 C 10
Feb 2002 D 1000
Feb 2002 A 50
2) Interested in output table like this
C_Date Type_A_Value Type_B_Value Type_C_Value Type_D_Value ......... all distinct types....... [...]
10765 48 16_Re: Archive logs10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 11 Sep 2002 09:41:31 +0200619_us-ascii Hi Melissa
In my opinion the redundancy is important but as every thing it depends.
What I want to say is: what happens if Geographic Parallel Dispersed Sysplex fails for any reason ? I know of problems in similar installation due to DASD mirroring (microcode problems), network failures or PPRC anomalies. Another important question is what delay (if any) do I introduce using site three to obtain archive logs in my recovery procedure ? It might be an inacceptable delay for some applications (IBM says 60 minutes for a COMPLETE planned switch, 45 for unplanned switch to the mirror site) [...]
10814 52 23_Re: with hold &rollback14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 11 Sep 2002 03:12:59 -0500453_ISO-8859-1 Does this paragraph apply:
"Cursors that are declared with WITH HOLD in CICS or in IMS non-message- driven programs will not be closed by a rollback operation if the cursor was opened in a previous unit of work and no changes have been made to the database in the current unit of work. The cursor cannot be closed because CICS and IMS do not broadcast the rollback request to DB2 for a null unit of work." (SQL Reference Manual)? [...]
10867 14 9_with hold10_Ozgur Ozen13_oozen@YKB.COM31_Wed, 11 Sep 2002 03:15:58 -0500456_- Hi , why a cursor defined with hold option within a nonterminal oriented task is not closed after exec cics rollback statement? is there way to close it,without using close cursor statement? thanks.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
10882 92 20_help with a sqlquery11_Vijay Salvi23_vithalvijay@YAHOO.CO.IN31_Wed, 11 Sep 2002 10:26:47 +0200431_iso-8859-1
Hi DB2-Listers,
Could anyone please help me write a query for following req :
1) My input table is like this :
C_Date Type Value
Jan 2002 A 20
Jan 2002 B 100
Jan 2002 C 10
Feb 2002 D 1000
Feb 2002 A 50
2) Interested in output table like this
C_Date Type_A_Value Type_B_Value Type_C_Value Type_D_Value ......... all distinct types....... [...]
10975 197 37_Re: RTFM & supercilious BP parameters19_Alekos Papadopoulos13_apapad@NBG.GR31_Wed, 11 Sep 2002 12:52:04 +0200408_iso-8859-1 Well, Hippopotami is the correct plural according to Greek grammar, since it is a Greek word meaning "River Horse"(!!!). Latin grammar is quite different, and it results to ignoramii. The only -hardly- DB2 related thing in this topic is the correct plural of index -> indicii. Could we move to an etymology/grammar-related list somewhere, before making other people in the list very unhappy? [...]
11173 63 55_Re: Recommended Educational Resources for Certification13_Chris Andrews20_andrews_c@UK.IBM.COM31_Wed, 11 Sep 2002 05:08:41 -0500587_- I have 12 years experience with DB2 (mostly 390), have read both the 390 and Windows/UDB certification guides and passed the 390 certification.
The DB2 for OS/390 Certification Guide is very good, - there are errors (these look like some poor proof-reading and/or it being written before V7 was GA or very early GA at best), - there are a good few tables which are practically copies of the manual pages and which add little value, too detailed for this book. - the biggest problem is the poor typesetting (not unreadable but slows you up) in comparison to the Windows/UDB [...]
11237 36 37_Re: RTFM & supercilious BP parameters12_Tasch Holger25_holger.tasch@ITELLIUM.COM31_Wed, 11 Sep 2002 12:14:59 +0200572_iso-8859-1 > Latin grammar is quite > different, and it results to ignoramii.
"ignoramii" would be the plural form of a word like "ignoramius", where else should the second (or first) "i" come from. In fact, "ignoramus" is not a latin word anyway, but a verbal form (from the verb ignorare (to know not) the 1st p. pl.: we don't know), that has become an english word with the meaning of something that in latin would have been expressed as "ignorans", Pl: "ignorantes". As "ignoramus" is not a latin noun, but is a noun only in english, I would suggest using [...]
11274 30 26_Re: extracting DB2 version16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Wed, 11 Sep 2002 13:18:00 +0200512_- DSNALI Connect returns the RIB (release information cblock) and the RIB contains the release/version
Roland
> -----Original Message----- > From: Shaja R [SMTP:radhangan@YAHOO.COM] > Sent: Wednesday, September 11, 2002 12:01 AM > To: DB2-L@LISTSERV.YLASSOC.COM > Subject: extracting DB2 version > > Hi list, > > Is there any SQL statement available to extract the > version of DB2 from an application program...? > (or any other simple methods other than to dig into > control blocks...) > > [...]
11305 35 49_Re: Calling DSNTIAUL utility from a COBOL Program14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 11 Sep 2002 21:28:38 +1000378_US-ASCII Since the DD names used by DSNTIAUL are fixed, your driver program will have to handle allocation/deallocation of them. There are a number of dynamic allocation routines you can use, but perhaps bpxwdyn at http://www- 1.ibm.com/servers/eserver/zseries/zos/unix/bpxa1ty2.html would be easiest to get installed - if you don't already have one.
James Campbell [...]
11341 39 26_Re: extracting DB2 version14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 11 Sep 2002 21:28:38 +1000599_US-ASCII I think whether you can extract the version of DB2 _from_ an application depends on the application.
Or did you mean extract it _into_ an application? If so, CONNECT.
James Campbell
On 10 Sep 2002 at 15:01, Shaja R wrote:
> Hi list, > > Is there any SQL statement available to extract the > version of DB2 from an application program...? > (or any other simple methods other than to dig into > control blocks...) > > Shaja > > > __________________________________________________ > Yahoo! - We Remember > 9-11: A tribute to the more than 3,000 lives lost > [...]
11381 71 42_Re: db2 v5 upgrade to v7 : is ipl a must ?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 11 Sep 2002 21:28:38 +1000325_US-ASCII My V6 Diagnosis Guide and Reference has Figure 207. Control Block Linkage from Early Processing Block (ERLY). So I guess ERLY code is the code that is executed earlier (not a pun, but a description) than the start of DB2.
A bit of a shock to the system, I know, to find an abbreviation that makes sense. [...]
11453 69 24_Re: help with a sqlquery14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 11 Sep 2002 21:28:38 +1000434_US-ASCII Yes, I will help - but you'll have to do the grunt work:
select all rows, grouping by C-date
In the select list, have c-date and, for all other columns, a sum of a case expression; the case expression will resolve to the value of Value if Type indicates that it is appropriate for this column (in the result table), and zero otherwise. Have as many additional columns as there are discrete values of Type. [...]
11523 198 26_Re: -101 After DB2 Upgrade11_Tina Hilton29_Tina.Hilton@ARVATOSYSTEMS.COM31_Wed, 11 Sep 2002 07:11:03 -0500429_iso-8859-1 We've had them work in QMF but fail in DSNTEP2, so QMF seems to remove the blanks but not DSNTEP2. We have a rexx that removes all the blanks and nice formatting for the long selects so they'll run.
Tina Hilton arvato systems
-----Original Message----- From: David S Waugh [mailto:dsw-dba@JUNO.COM] Sent: September 10, 2002 3:37 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: -101 After DB2 Upgrade [...]
11722 108 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT11_David Nance16_DWNance@FHSC.COM31_Wed, 11 Sep 2002 08:10:11 -0400586_US-ASCII Aurora, I don't think there's going to be anything we can do to the query, its very simple and straight-forward. You have to look at alternatives. You're only matching on the first two columns of the index and I'm guessing that isn't very unique by the looks of it. I think your best bets would be another index on t1, if the IPNET would be more restrictive and then DB2 would probably choose tablespace or non-matching index access on T2. The other suggestion would be to start looking at some kind of archival solution or partitioning/redesigning using data buckets for [...]
11831 41 23_Re: DB2 Connect PE V7.211_Tina Hilton29_Tina.Hilton@ARVATOSYSTEMS.COM31_Wed, 11 Sep 2002 07:17:06 -0500398_iso-8859-1 We leave it up to the desktop people what they sign on with to do the install. Just make sure it's not longer than 8 bytes or you will have problems. So, I don't think there's any difference between using one common name or one per user, as long as it has admin rights. This is assuming you're talking about NT or 2000. I don't know what the requirements are for other Windows OSs. [...]
11873 13 55_Re: Recommended Educational Resources for Certification7_DB2 DBA21_db2_dba@BELLSOUTH.NET31_Wed, 11 Sep 2002 08:17:08 -0400529_ISO-8859-1 I purchased the book prior to taking the certification exams. It seemed to be a good tool to get your mindset back into test taking mode. I have not done that in a while. I also found it interesting that the sample questions in the back of the book for the 512 exam and the 516 exam are exact coppies of the questions that appear on the IBM ICE site for getting your free certification voucher. You only get 25 questions on the web site. Also, you can only get one voucher number per month. So don't pay the $120 [...]
11887 84 49_Re: Calling DSNTIAUL utility from a COBOL Program15_Leonardo Zrycki29_leonardo.zrycki@ACCENTURE.COM31_Wed, 11 Sep 2002 14:15:29 +0200462_us-ascii James: Thanks.
This was my first idea: using an LE Envirronment Variable with the 'DD' data definition (via CALL 'PUTENV'). But this is OK ??? If my COBOL program calls DSNTIAUL,an assembler program that allocates the files, why I have to allocate the files. I change my first idea not to use dsntiaul, and declare a cursor ,open it, fetch, write the file, etc.. using dynamic sql to prepare the select. But if I can use DSNTIAUL is better. [...]
11972 27 20_Bind Package Failing12_Rakesh Kumar21_rakesh457@HOTMAIL.COM31_Wed, 11 Sep 2002 12:27:27 +0000291_- Hi All, I have a requirement of getting Oracle data onto DB2.I have setup the CDB tables as specified by the Manuals.I have written an application in COBOL and when I try to bind the package to the remote database it is giving an error with SQLCODE = -30081 and the SQLSTATE = 58020. [...]
12000 157 37_Re: RTFM & supercilious BP parameters0_19_mike.holmans@BT.COM31_Wed, 11 Sep 2002 13:28:48 +0100769_iso-8859-1 Very worrying, especially if they're charging at you.
Mike
>>-----Original Message----- >>From: Sniderman, Karl [mailto:ksniderman@BCBSOK.COM] >>Sent: Tuesday, September 10, 2002 9:57 PM >>To: DB2-L@LISTSERV.YLASSOC.COM >>Subject: Re: [DB2-L] RTFM & supercilious BP parameters >> >> >>What's the plural of hippopotamus? >>Karl >> >>-----Original Message----- >>From: Aurora Dell'Anno [mailto:aurora_dellanno@CANDLE.COM] >>Sent: Tuesday, September 10, 2002 12:48 PM >>To: DB2-L@LISTSERV.YLASSOC.COM >>Subject: Re: RTFM & supercilious BP parameters >> >> >>I think it's ignorami with one i ;-) >> >>And Steve this was a MASTERPIECE... >> >>Ciao. >> >>Aurora >> >>not yet certified but with the whitecoats working on it in a >>hurry with all [...]
12158 60 55_Re: Recommended Educational Resources for Certification14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 11 Sep 2002 22:04:26 +1000409_US-ASCII I consider the DB2 V7 for Windows etc Certification Guide appallingly written. It obviously lacked strong technical editting by someone who knows the material. But perhaps this is a good thing, since one must keep referring to the manuals to learn what the Guide was supposed to mean.
There is at least one question that can appear in the exam that is not mentioned anywhere in the book. [...]
12219 51 14_Re: -904 error28_Koleto, Kenneth M. [NCS SLC]21_KKoleto@CSCUS.JNJ.COM31_Wed, 11 Sep 2002 08:43:38 -0400263_iso-8859-1 Sanjeev, I believe it is DB2's way of making sure NO OTHER process can change properties of the database that might interfere with or otherwise invalidate the choice of the access path and other aspects of the bind for your plan or package. Ken
12271 166 22_Re: Your post on DB2-L16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Wed, 11 Sep 2002 14:00:18 +0100326_us-ascii Rebecca,
Thanks very much for your input. I have now reduced the BP size to 262144 - and modified the default BP size in case they want to create new ones in the future.
What is your view on multiple BPs? Is it a good idea to have at least 1 extra one, since my main user table is 51 million rows? [...]
12438 116 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM31_Wed, 11 Sep 2002 07:52:53 -0500363_- Dave,
my report is just "finished", after a running time of almost 20 hours!
No the index is not unique at all - the data is inserted by 3rd party software and I am not sure if it would be a good idea, but I thought of adding an automatically generated value column to add to the index, in order to make it unique. What do the experts think? [...]
12555 132 24_Re: help with a sqlquery34_Mayannan, Dharmalingam (Cognizant)26_MDharmal@CHN.COGNIZANT.COM31_Wed, 11 Sep 2002 18:11:33 +0530485_iso-8859-1 The answer is
SELECT C_DATE, SUM (TYPE_A_VALUE) AS TYPE_A_VALUE, SUM (TYPE_B_VALUE) AS TYPE_B_VALUE, SUM (TYPE_C_VALUE) AS TYPE_C_VALUE, SUM (TYPE_D_VALUE) AS TYPE_D_VALUE FROM (SELECT C_DATE, (CASE TYPE WHEN 'A' THEN VALUE ELSE 0 END) AS TYPE_A_VALUE, (CASE TYPE WHEN 'B' THEN VALUE ELSE 0 END) AS TYPE_B_VALUE, (CASE TYPE WHEN 'C' THEN VALUE ELSE 0 END) AS TYPE_C_VALUE, (CASE TYPE WHEN 'D' THEN VALUE ELSE 0 END) AS TYPE_D_VALUE FROM table) A GROUP BY C_DATE [...]
12688 106 49_Re: Calling DSNTIAUL utility from a COBOL Program14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 11 Sep 2002 23:23:32 +1000391_US-ASCII I doubt that DSBTIAUL allocates any files. Opens, writes and closes, yes; allocates, no.
If you are happy with PUTENV, then fine.
File allocations are global to an address space. Any program running in one has access to all files allocated in it.
If you are happy to manipulate the SQLDA and buffer areas in your own program, then you don't need DSNTIAUL. [...]
12795 34 19_Bufferpool Analyzer13_Vickers, Mark22_Mark.Vickers@ANICO.COM31_Wed, 11 Sep 2002 08:34:44 -0500609_iso-8859-1 Greetings and kind thoughts for all who grieve 9/11 today.
We are a relatively small shop using DB2 UDB v7 and almost on z/OS (OS/390 v10). Now at the point of requiring some serious bufferpool analysis and control (all thresholds maxing out). Let me start by saying that the thought of identifying and classifying each tablespace is a daunting task to say the least. Partly because aside fro the Data Warehouse and this year's developments, there is no relation between any type of processing and any other bufferpools ! We are considering vendors tools to help with this analysis and [...]
12830 19 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT12_Jim Harrison17_jharrison@GMX.NET31_Wed, 11 Sep 2002 09:42:43 -0400547_us-ascii Aurora, you obviously have monitoring tools. Didn't they give some indication of what was consuming all the time? What were the getpage counts?
At 07:52 AM 9/11/2002 -0500 Aurora Dell'Anno said: >Dave, > >my report is just "finished", after a running time of almost 20 hours!
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
12850 154 15_Re: Help please23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 11 Sep 2002 14:45:36 +0100550_- Aurora
let me know if you need me any further .. I'm on hols tomorrow from 12pm but available until then to help out.
I concur with Phil (who has more experience than me at these things).
For information I had a similar experience a few years back and sadly there was no backup . I managed to fool db2 into using the application pagesets but with a new catalog/directory. I am very rusty on this and from memory IBM were very little help. Things have come along way since 2.12 of the product and as such db2dart will help [...]
13005 135 24_Re: help with a sqlquery34_Mayannan, Dharmalingam (Cognizant)26_MDharmal@CHN.COGNIZANT.COM31_Wed, 11 Sep 2002 15:41:50 +0200485_iso-8859-1 The answer is
SELECT C_DATE, SUM (TYPE_A_VALUE) AS TYPE_A_VALUE, SUM (TYPE_B_VALUE) AS TYPE_B_VALUE, SUM (TYPE_C_VALUE) AS TYPE_C_VALUE, SUM (TYPE_D_VALUE) AS TYPE_D_VALUE FROM (SELECT C_DATE, (CASE TYPE WHEN 'A' THEN VALUE ELSE 0 END) AS TYPE_A_VALUE, (CASE TYPE WHEN 'B' THEN VALUE ELSE 0 END) AS TYPE_B_VALUE, (CASE TYPE WHEN 'C' THEN VALUE ELSE 0 END) AS TYPE_C_VALUE, (CASE TYPE WHEN 'D' THEN VALUE ELSE 0 END) AS TYPE_D_VALUE FROM table) A GROUP BY C_DATE [...]
13141 42 22_Re: Table Partitioning32_Atalay Tanis (Garanti Teknoloji)22_AtalayT@GARANTI.COM.TR31_Wed, 11 Sep 2002 16:55:57 +0300328_utf-8 Scott,
Not sure about windows environment but UDB V7 UNIX does not support key range partitioning. However if your table is read only you may create distinct tables for each partition and UNION ALL them in a view definition. I case you want to do this don't forget to enforce key rabges via check constraints. [...]
13184 23 33_Performance parms - DB2 UDB on NT16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM31_Wed, 11 Sep 2002 08:43:15 -0500588_- Hi list,
back again with my problems.
In your shops (where DBs are better looked after, I'm sure!) what is the value for prefetch-enabling parms? To be more psecific, what value have you set for num_ioservers (here it's currently 3) and for dft_prefetch_sz (here currently 32)?
TIA.
Ciao.
Aurora
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
13208 45 35_IDCAMSdefinitions for catalog REORG12_David Wilson30_David_A_Wilson@JOHNLEWIS.CO.UK31_Wed, 11 Sep 2002 14:54:34 +0100500_us-ascii Fellow DB2 people,
We have decided it is time to REORG the catalogs of our many DB2 subsystems in order, amongst other things, to resize the underlying VSAM datasets. Does anybody have a method of generating the IDCAMS control statements to define the underlying VSAM datasets, given a daatset name or pattern. We could probably write a REXX but if someone has already been through this hoop I would be grateful if they could share their experience/code. Many thanks in advance. [...]
13254 59 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT16_Aurora Dell'Anno26_aurora_dellanno@CANDLE.COM31_Wed, 11 Sep 2002 09:03:31 -0500577_- Jim,
the only monitoring tool I have available is GET SNAPSHOT from the CLP - the IBM Default Monitor IS BROKEN :-s and they have no Omegamon here...
This is the BP snapshot (only one BP by the way, in case I did not mention it):
Buffer pool data logical reads = 51027973 Buffer pool data physical reads = 22274385 Buffer pool data writes = 99275 Buffer pool index logical reads = 93842671 Buffer pool index physical reads = 2165721 Total buffer pool read time (ms) = 108776002 Total buffer pool write time (ms) = 144765 Asynchronous pool data page [...]
13314 43 29_Result from the query to big.19_Briggs, N. - Neil -28_Neil.Briggs@CANON-EUROPE.COM31_Wed, 11 Sep 2002 16:12:52 +0200657_iso-8859-1 Dear Listers,
os390 DB2 v6
I have a situation where a query runs
SELECT A.SERVICE ,A.DOCDB_PUB_NO_17 ,B.SEQUENCE ,B.ROW_DATA FROM TLS020_BIBLIO_SORT A (10 million rows) ,TLS021_BIBLIO_REP B (12 million rows) WHERE A.SERVICE = 'PFS' AND A.SERVICE = B.SERVICE AND A.DOCDB_PUB_NO_17 = B.DOCDB_PUB_NO_17 ORDER BY A.SORT_FIELD ,B.SEQUENCE
The problem that I have is that when the query runs, I get a resource unavailable because DSNDB07 is not big enough to process all the records returned. The DBA say's the query needs to be changed to process less data as he is not prepared to increase the size of DB07. How do I [...]
13358 96 42_Antwort: Performance parms - DB2 UDB on NT12_Rodney Krick30_rk@HAENCHEN.SOFTWAREZENTRUM.DE31_Wed, 11 Sep 2002 16:26:12 +0200591_iso-8859-1 Hi Aurora,
Give a look at this two articles from Scott Hayes (DB2 Magazine).
http://www.db2mag.com/db_area/archives/2001/q4/hayes.shtml http://www.db2mag.com/db_area/archives/2001/q1/hayes.shtml
HTH
Mit freundlichen Grüßen / Best regards,
Rodney Krick Hänchen & Partner GmbH Calwer Straße 1 D-71034 Böblingen
Aurora Dell'Anno Kopie: Gesendet von: DB2 Thema: Performance parms - DB2 UDB on NT Data Base Discussion List [...]
13455 57 39_Re: IDCAMSdefinitions for catalog REORG7_DB2 DBA21_db2_dba@BELLSOUTH.NET31_Wed, 11 Sep 2002 10:36:38 -0400611_ISO-8859-1 The JCL to run the IDCAMS to create the datasets was created as part of your install. Look in SDSNSAMP. > > From: David Wilson > Date: 2002/09/11 Wed AM 09:54:34 EDT > To: DB2-L@LISTSERV.YLASSOC.COM > Subject: IDCAMSdefinitions for catalog REORG > > Fellow DB2 people, > > We have decided it is time to REORG the catalogs of our many DB2 subsystems > in order, amongst other things, to resize the underlying VSAM datasets. > Does anybody have a method of generating the IDCAMS control statements to > define the underlying VSAM datasets, given a daatset name or [...]
13513 67 33_Re: Result from the query to big.11_David Nance16_DWNance@FHSC.COM31_Wed, 11 Sep 2002 10:38:45 -0400294_US-ASCII Neil, It would be due to your order by requirement. You could do a select count(*) on this to see exactly how much data you are trying to sort. Then use that info to get the sysprog to allocate that much more space. Or run query without the order by and sort in a different step. [...]
13581 54 19_Bufferpool Analyzer14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 11 Sep 2002 10:46:09 -0400453_ISO-8859-1 The Buffer Pool Tool from Responsive Systems is the industry standard for tuning pools. We pioneered the approach and the technology. We provide the proven methodology for effective pool tuning, and can provide sample justifications - and client references you can speak with.
Regards, Joel Goldstein
Message text written by DB2 Data Base Discussion List >Greetings and kind thoughts for all who grieve 9/11 today. [...]
13636 157 49_Re: Calling DSNTIAUL utility from a COBOL Program15_Leonardo Zrycki29_leonardo.zrycki@ACCENTURE.COM31_Wed, 11 Sep 2002 16:47:31 +0200581_us-ascii James:
Thanks. This answers my question
File allocations are global to an address space. Any program running in one has access to all files allocated in it.
What is the meaning of If you are happy with PUTENV, then fine.
Do you think that exist a better way to allocate files.
Leonardo
James Campbell cc: Sent by: DB2 Data Base Subject: Re: Calling DSNTIAUL utility from a COBOL Program Discussion List [...]
13794 44 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT12_Jim Harrison17_jharrison@GMX.NET31_Wed, 11 Sep 2002 10:55:10 -0400468_us-ascii Sorry - with a domain name of candle.com, I just assumed you were a Candle employee and had access to the company's products. I don't recall if you posted the number of rows in the tables involved, but it seems like these are some pretty high numbers if they are tied to your job - possibly indicating multiple TS scans. One BP is definitely not helping your cause. Since I'm too lazy to parse the SQL, I'll wait for Terry to weigh in with his analysis. [...]
13839 23 39_Re: IDCAMSdefinitions for catalog REORG16_Friemel, Norbert24_Friemel@RRZ-KARLSRUHE.DE31_Wed, 11 Sep 2002 17:03:37 +0200315_iso-8859-1 > pattern. We > could probably write a REXX but if someone has already been > through this > hoop I would be grateful if they could share their experience/code. > Many thanks in advance. > >
Take a look at file 493, file 528 and file 360 of the CBT tape (http://www.cbttape.org/cbtdowns.htm) [...]
13863 17 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 11 Sep 2002 17:08:09 +0200330_us-ascii Ciao Aurora
Working again with MM DB2 ?
Saludos Max
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
13881 96 33_AW: Result from the query to big.12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 11 Sep 2002 17:07:18 +0200458_iso-8859-1 Neil,
be sure that m u l t i p l e tablespaces are defined for DSNDB07 (these tablespaces should be placed on separate DASD devices).
In addition ensure that the underlying VSAM data sets for the DSNDB07 tablespaces are not using multiple extents.
If you would rather have a runaway query fail than have it aquire the storage for sort work files using extents, define the work files without the ability to catch extents. [...]
13978 51 51_Problem with passing file in SYSTSIN under IKJEFT0111_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Wed, 11 Sep 2002 09:58:34 -0500466_iso-8859-1 Hi,
I am trying to execute Rebind Package in batch by passing to SYSTSIN a sequential file with the Rebind statement. I see it
adds the DSN word again. The JCL looks like
//TMPREBND EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSTSPRT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSTSIN DD DSN=AS23.RBPK.INPUT,DISP=SHR
The Input file looks like:
DSN SYS(DB2T) REBIND PACKAGE - (XXXX.XXXXXXX) V(B) IS(CS) EX(N) DEGREE(ANY) OWNER(G057) [...]
14030 56 17_CastOut ownership32_Atalay Tanis (Garanti Teknoloji)22_AtalayT@GARANTI.COM.TR31_Wed, 11 Sep 2002 18:17:12 +0300368_iso-8859-9 Hello dear listers, when I entered DIS DB.TS LOCKS for a partioned ts I received the following response, which shows cast out ownership over the base tablespace belongs to one member while the ownership of a certain part belongs to another. Is the ownership on the base tablespace meaningful? If so what job is done by this member in means of castout? [...]
14087 44 13_Join vs. Case17_Sanfilippo, David34_David.Sanfilippo@GMACINSURANCE.COM31_Wed, 11 Sep 2002 11:28:53 -0400599_iso-8859-1 OS 390, v6.1
From an efficiency standpoint, what are the situations, if any, that a SQL statement should use a CASE statement instead of joining to a small reference table?
In reviewing some SQL here, found several statements where, rather than joining a large (> 6 million rows) table to small reference tables, the programmer was coding multiple CASE conditions. (e.g. Table A contains a status code, but the desired results are to show the text name for that code on each row. Instead of joining to a small reference table (< 50 rows), the statement was coded to do [...]
14132 100 55_Re: Problem with passing file in SYSTSIN under IKJEFT0113_Rick N Molera22_rick.n.molera@AEXP.COM31_Wed, 11 Sep 2002 08:32:20 -0700533_us-ascii Amit,
Try adding a STEPLIB so MVS "knows" this job is attempting to access DB2. Like this:
//STEP010 EXEC PGM=IKJEFT01,COND=(4,LT) //STEPLIB DD DISP=SHR, // DSN='SYS1.DSN.DSNEXIT' // DD DISP=SHR, // DSN='SYS1.DSN.DSNLOAD' //SYSPRINT DD SYSOUT=* //SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=133 //SYSUDUMP DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DBD1) REBIND PACKAGE (DONC1T.ONBCPMAP.(UHPLR0)) CURRENTDATA(NO) END //*
If you do not know the STEPLIB names for your DB2, look in D*MSTR started task (in SDSF;DA). [...]
14233 35 27_Help! Trying to free a plan9_Dan Lamas22_DLamas@DUKE-ENERGY.COM31_Wed, 11 Sep 2002 10:34:35 -0500510_us-ascii Hello Everyone,
I previously posted this question to the group. It has been a week and I have not seen any responses to my problem. If anyone can help, please do.
Thanks again!
Dan Lamas Duke Energy Gas Transmission Jr. DBA
I am preparing to migrate from V6 to V7 and ran the query to check my catalog entries. I came accross a plan that has two special characters in the first two bytes of the plan name and I cannot seem to figure out how to free this plan. [...]
14269 48 35_IDCAMSdefinitions for catalog REORG12_David Wilson30_David_A_Wilson@JOHNLEWIS.CO.UK31_Wed, 11 Sep 2002 16:50:41 +0100592_us-ascii Fellow DB2 people,
The Listserver has asked me to resend this message so apologies if it appears twice.
We have decided it is time to REORG the catalogs of our many DB2 subsystems in order, amongst other things, to resize the underlying VSAM datasets. Does anybody have a method of generating the IDCAMS control statements to define the underlying VSAM datasets, given a daatset name or pattern. We could probably write a REXX but if someone has already been through this hoop I would be grateful if they could share their experience/code. Many thanks in advance. [...]
14318 66 31_Re: Help! Trying to free a plan16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM31_Wed, 11 Sep 2002 09:17:20 -0700510_us-ascii Dan,
Your problem might be related to the leading spaces before your plan name. Another option to freeing your plan would be performing this function within DB2I.
Regards, Jeff L'Italien American Express
From: "Dan Lamas" @LISTSERV.YLASSOC.COM> on 09/11/2002 10:34 AM EST
Please respond to "DB2 Data Base Discussion List"
Sent by: "DB2 Data Base Discussion List" [...]
14385 64 33_Re: Result from the query to big.10_James Kwan19_jamesdb2kwan@CS.COM31_Wed, 11 Sep 2002 11:08:50 -0500484_iso-8859-1 1) How many rows do you expect to return? 2) Any index on Sort_field? 3) What access method does explain tell you?
Regards,
James Kwan IBM Certified Solutions Expert - DB2 V7.1 Database Administration for OS/390
----- Original Message ----- From: "Briggs, N. - Neil -" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, September 11, 2002 9:12 AM Subject: Result from the query to big. [...]
14450 156 49_Re: Calling DSNTIAUL utility from a COBOL Program15_Leonardo Zrycki29_leonardo.zrycki@ACCENTURE.COM31_Wed, 11 Sep 2002 18:15:14 +0200573_us-ascii James:
Thanks. This answers my question
File allocations are global to an address space. Any program running in one has access to all files allocated in it.
What is the meaning of If you are happy with PUTENV, then fine.
Do you think that exist a better way to allocate files.
Leonardo
James Campbell cc: Sent by: DB2 Data Base Subject: Re: Calling DSNTIAUL utility from a COBOL Program Discussion List [...]
14607 50 31_Re: Help! Trying to free a plan11_Tina Hilton29_Tina.Hilton@ARVATOSYSTEMS.COM31_Wed, 11 Sep 2002 11:25:23 -0500493_iso-8859-1 Sounds like you forgot the DSN(ssid) before it, so TSO is erroring your command. It's never getting to DB2.
-----Original Message----- From: Dan Lamas [mailto:DLamas@DUKE-ENERGY.COM] Sent: September 11, 2002 10:35 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Help! Trying to free a plan
Hello Everyone,
I previously posted this question to the group. It has been a week and I have not seen any responses to my problem. If anyone can help, please do. [...]
14658 65 55_Re: Problem with passing file in SYSTSIN under IKJEFT0113_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 11 Sep 2002 11:30:28 -0500449_iso-8859-1 Try an "END" statement as the last item in you input file as well.
-----Original Message----- From: Sinha, Amit [mailto:Sinha.Amit@MBCO.COM] Sent: Wednesday, September 11, 2002 9:59 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Problem with passing file in SYSTSIN under IKJEFT01
Hi,
I am trying to execute Rebind Package in batch by passing to SYSTSIN a sequential file with the Rebind statement. I see it [...]
14724 48 18_Reverse index scan11_Daniel Adam16_dadam@GRATEX.COM31_Wed, 11 Sep 2002 11:45:31 -0500431_- Hi all, Can DB2 for os/390 v7 use an index in revers order ? Example:
SELECT max(NL_QUARTREPT_ID) FROM zpzb.T_QUARTREPT WHERE VALID IN (0, 1)
There is an ascending index IX_T_QUARTREPT_1 on t_quartrept (nl_quartrept_id). Most of the rows (99,9%) in the table have valid 0 or 1. Rows with valid other than 0,1 have highest nl_quartrept_id. Cardinality of the table is 100.000 rows and getting bigger every day. [...]
14773 20 41_Re: Self Repositioning Cursor Declaration14_William Poston21_william.poston@DB.COM31_Wed, 11 Sep 2002 12:29:15 -0600348_us-ascii How are shop out there handling Self Repositioning Cursor Declaration for Restart logic when the Cursor involves Joins on multiple tables? Are you contacting fields together in your where clause to get to the position where you left out on a restart or is there a better method? I don like concatenation because of performance degradation.
14794 144 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT15_Russell Collins26_RCollins@ZCOREBUSINESS.COM31_Wed, 11 Sep 2002 12:51:03 -0500396_us-ascii The only thing that I would suggest is changing your predicate around to see if there is a different order that might be a little quicker. Example:
>>WHERE T1.APPLICATION = 'myapp' AND >> T1.ELAPSED <= 1031184000 AND >> T1.ELAPSED >= 1031011200 AND >> T1.RESPONSE <= 300000
I would play with that predicate. If that doesn't do it, then you may have to look elsewhere. [...]
14939 43 20_DSN9023I on -STOP DB14_Ruggles, Scott17_SRuggles@LSIC.COM31_Wed, 11 Sep 2002 13:28:21 -0400513_- We are running version DB2 V7 and having an occasional problem with our nightly image copies, and I have a two-part question.
About once a month we receive a DSN9023I 'Abnormal Completion' on the -STOP DB command, which we do prior to the image copy. This results in a RC=12, and the actual image copy steps run successfully with RC=0. We have been unable to identify any locked resources or concurrent jobs that are interferring. Has anyone seen this problem or come up with a possible resolution? [...]
14983 67 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Wed, 11 Sep 2002 10:58:21 -0700705_iso-8859-1 I would have coded
t1.elapsed BETWEEN 031011200 and 103118400
but it may not matter for efficiency. Ranks your where clause so the indexed field is tested first.
have you done a runstats on your table? Could it require a reorg? Is this a sudden loss of efficiency or a gradual creeping loss?
Susan
>> >>Any ideas of how to improve this pesky SQL? >> >>SELECT T1.ELAPSED, T1.RESPONSE, T1.TYPE, T1.TIMEZONE, T1.APPLICATION, >>T1.CLIENTTIME, T1.NTIME, T1.APPTIME, T2.SITE, T2.OFFICE, T2.CITY, >T2.STATE >>FROM MYDB.T1 INNER JOIN MYDB.T2 >>MAPPINGS ON T1.IPNET = T2.IP >>WHERE T1.ELAPSED >= 1031011200 AND >> T1.ELAPSED <= 1031184000 AND >> T1.APPLICATION = [...]
15051 77 31_FW: Help! Trying to free a plan15_Hernandez, Hugo22_Hugo_Hernandez@BMC.COM31_Wed, 11 Sep 2002 13:00:35 -0500323_iso-8859-1 Hello Dan, you could try the following:
1) Do you know the characters before the SYSIND??, if not, in the PDS where you have the result of the query do an HEX ON command in order to see wich characters are.. 2) After that you can construct a batch job to invoke the DSN proccesor in batch mode like: [...]
15129 139 55_Re: Problem with passing file in SYSTSIN under IKJEFT0111_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Wed, 11 Sep 2002 13:14:12 -0500489_iso-8859-1 Thanks all for your help. The problem was with the sql statement using which I was building the Rebind Statement my sql statement was not adding upto 72 characters due to which in the input file after the rebind there were junk characters.
Stupid me.
Thanks.
Amit
FYI : The SQL
SELECT SUBSTR(' (' || RTRIM(COLLID) ||'.'||RTRIM(NAME) || ')',1,20) ,SUBSTR('V(B) IS(CS) EX(N) DEGREE(ANY) OWNER(G057) ',1,52) FROM SYSIBM.SYSPACKAGE ; [...]
15269 134 18_Re: Freeing a plan9_Dan Lamas22_DLamas@DUKE-ENERGY.COM31_Wed, 11 Sep 2002 14:09:44 -0500372_us-ascii Thanks Tina for your response. I did not see that Hugo had responded either.
But as to Hugo's response. I had already tried his suguestion that is what resulted in my error.
FREE PLAN ( SYSIND) IKJ56702I INVALID PLAN IDENTIFIER, SYSIND IKJ56701I MISSING ONE OR MORE PLAN NAMES++ IKJ56701I MISSING 1 ALPHABETIC + UP TO 7 ALPHANUM/NATIONAL CHARS [...]
15404 34 60_THANKS - Recommended Educational Resources for Certification12_Scott Taylor25_scott.taylor@AUTOZONE.COM31_Wed, 11 Sep 2002 15:02:02 -0500449_us-ascii Thanks to everyone for their input on my question regarding educational resources for the certification exams. Sorry I listed a book (the All-In-One by Sanders) as an option for consideration. I'd gotten sidetracked and listed that book instead of the certification guide by Yevich and Lawson.
Again, thanks for all your help. ---------------------------------------------------------------------------------------------------- [...]
15439 37 52_DB2 V6.1: Coding existence checking via SQL in COBOL0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Wed, 11 Sep 2002 16:13:17 -0400463_us-ascii All,
(1) I want to code the following SQL (which does existence checking) in a COBOL program. The SQL is from an article written by Richard Yevich and Susan Lawson.
SELECT 1 FROM SYSIBM.SYSDUMMY1 X WHERE EXISTS (SELECT 1 FROM table_name WHERE col_name = some_value AND X.IBMREQD = X.IBMREQD)
(2) The precompile is failing because the programmer does not have an INTO clause. Can the '1' be assigned to a host variable such as : [...]
15477 49 0_9_Rama Kypa17_rkypa@HOTMAIL.COM31_Wed, 11 Sep 2002 20:29:05 +0000447_- Yes, You Can. Rama .. -----Original Message----- From: Ed_Vetock@NAVYFEDERAL.ORG [mailto:Ed_Vetock@NAVYFEDERAL.ORG] Sent: Wednesday, September 11, 2002 3:13 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: DB2 V6.1: Coding existence checking via SQL in COBOL
All,
(1) I want to code the following SQL (which does existence checking) in a COBOL program. The SQL is from an article written by Richard Yevich and Susan Lawson. [...]
15527 30 17_QMF: PRINT REPORT9_Oliver Su14_ojsu@EMAIL.COM31_Wed, 11 Sep 2002 12:30:50 -0800266_iso-8859-1 Hi Listers,
When I run a QMF batch job with PROC contains two reports such as: RUN query1 (form=form1 PRINT REPORT RUN query2 (form=form2 PRINT REPORT
The job output shows only the second report. Any resolution?
Thanks, Oliver
15558 67 56_Re: DB2 V6.1: Coding existence checking via SQL in COBOL16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM31_Wed, 11 Sep 2002 13:41:17 -0700543_us-ascii Ed,
You only need the first host variable on the outer select and not the correlated subquery.
Regards, Jeff L'Italien American Express
From: Ed_Vetock@NAVYFEDERAL.ORG@LISTSERV.YLASSOC.COM> on 09/11/2002 04:13 PM AST
Please respond to "DB2 Data Base Discussion List"
Sent by: "DB2 Data Base Discussion List"
To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: DB2 V6.1: Coding existence checking via SQL in COBOL [...]
15626 82 56_Re: DB2 V6.1: Coding existence checking via SQL in COBOL15_Jackson Reavill18_damcon2@US.IBM.COM31_Wed, 11 Sep 2002 16:43:58 -0400622_us-ascii Hi Ed,
Yes, you need :HV1, but not :HV2.
Cheers, Jay
Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 356-5317, Tie Line 8-697-5317
----------------------------------------------------- Happiness is not around the corner. Happiness is the Contour (SVT) of the road. -----------------------------------------------------
Ed_Vetock@NAVYFED ERAL.ORG To: DB2-L@LISTSERV.YLASSOC.COM Sent by: DB2 Data cc: Base Discussion Subject: DB2 V6.1: Coding existence checking via SQL in COBOL List [...]
15709 72 0_13_Villa Horacio17_hvilla@TTI.COM.AR31_Wed, 11 Sep 2002 17:32:33 -0300529_iso-8859-1 Well, "into :HV2" is not correct, is in a subquery. Horacio
-----Mensaje original----- De: DB2 Data Base Discussion List En nombre de Rama Kypa Enviado el: Miércoles 11 de Septiembre de 2002 17:29 Para: DB2-L@LISTSERV.YLASSOC.COM Asunto:
Yes, You Can. Rama .. -----Original Message----- From: Ed_Vetock@NAVYFEDERAL.ORG [mailto:Ed_Vetock@NAVYFEDERAL.ORG] Sent: Wednesday, September 11, 2002 3:13 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: DB2 V6.1: Coding existence checking via SQL in COBOL [...]
15782 71 24_Re: DSN9023I on -STOP DB13_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 11 Sep 2002 21:04:15 GMT379_- Scott:
Odd that you're getting the Abnormal Termination on the -STOP -- since you're doing a QUIESCE first, I would have thought it would have been the one encountering the problem first (assuming it's a contention/lock problem), not the -STOP. What other messages do you receive (if any)before/after the DSN9023I? Any indication of a timeout waiting on resources? [...]
15854 47 48_DB2 SQL Function for LUWID or thread identifier?16_Scott.Gjerdingen27_Scott.Gjerdingen@TARGET.COM31_Wed, 11 Sep 2002 16:12:33 -0500151_iso-8859-1 Is there a DB2 function and/or other means by which code within a trigger can obtain the thread ID and/or LUWID?
tia
- scott -
15902 66 33_Re: Result from the query to big.13_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 11 Sep 2002 21:25:04 GMT604_- Neil:
Any chance that establishing indexes on A.SORT_FIELD and B.SEQUENCE could help the DB2 Optimizer avoid the ORDER BY sort? That would solve the DSNDB07 too small problem (though the additional index maintenance overhead might cause other performance problems for other applications).
God, aren't DB2 Sysprogs insufferable sometimes? DASD's cheap, peoples' time is what's expensive. You'll probably end up spending more on a workaround for this problem than you'll save in DASD costs for bigger DSNDB07 datasets. 10 million and 12 million row tables are not that big these days. [...]
15969 45 16_Re: PRINT REPORT9_Birk, Tim17_BirkT@DIEBOLD.COM31_Wed, 11 Sep 2002 17:41:35 -0400521_- Oliver,
If your output is going to a sysout, you should be getting both reports. If your output is going to a dataset, make sure you use DISP=(MOD
Tim
-----Original Message----- From: Oliver Su [mailto:ojsu@EMAIL.COM] Sent: Wednesday, September 11, 2002 4:31 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: QMF: PRINT REPORT
Hi Listers,
When I run a QMF batch job with PROC contains two reports such as: RUN query1 (form=form1 PRINT REPORT RUN query2 (form=form2 PRINT REPORT [...]
16015 45 21_Re: QMF: PRINT REPORT13_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 11 Sep 2002 21:52:09 GMT346_- Oliver:
QMF opens & closes the DSQPRINT dataset for each PRINT REPORT command, so the second PRINT overwrites the first. Try putting DISP=MOD on the DSQPRINT dataset allocation instead of DISP=SHR or DISP=OLD. That should allow QMF to concatenate the 2 reports in the same dataset.
David Waugh, NCW DSW Consulting & Services [...]
16061 50 39_Re: IDCAMSdefinitions for catalog REORG13_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 11 Sep 2002 22:03:36 GMT590_- David:
The job name in SDSNSAMP is DSNTIJIN. You'll have to figure out a way to update the RECORDS(nnn nnn) parameter (manual, REXX routine, whatever). You can get information for allocated vs used from IDCAMS LISTCAT or from Platinum's Dataset Facility to use in your re-sizing efforts.
If you're mainly interested in consolidating extents, you can use DFDSS COPY to move the Cat & Dir datasets to another volume, and it will put all the extents back into a single one (if there's enough contiguous space on the new volume). Then if you want you can do the REORG, and [...]
16112 60 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT13_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 11 Sep 2002 22:24:22 GMT614_- Aurora:
I wonder if this might make any difference:
Is there a lower limit value you can specify for T1.RESPONSE, like zero? That way you could establish some BETWEEN predicates such as:
WHERE T1.ELAPSED BETWEEN 1031011200 AND 1031184000 AND T1.RESPONSE BETWEEN 0 and 300000 AND T1.APPLICATION = 'myapp'
I seem to remember BETWEEN being a better choice for range predicates than "value1 >= value2 AND value1 <= value3" in older releases of DB2. Don't know if it still makes a difference or not (I suspect the DB2 Optimizer is smart enough these days to know they're equivalent). [...]
16173 48 26_Re: -101 After DB2 Upgrade13_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 11 Sep 2002 22:32:16 GMT370_- Thanks Tina, that's good information. I seem to remember reading somewhere that QMF does some "massageing" (sp?) of the SQL before it passes it along to DB2. Evidently part of the massageing removes extra spaces.
I sincerely hope this 32K limit has been lifted in DB2 Version-Next, like some of the other limits, so we can stop having to work around it... [...]
16222 42 21_Re: QMF: PRINT REPORT14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 12 Sep 2002 08:27:37 +1000596_US-ASCII Is DSQPRINT allocated to a dataset with DISP=NEW or OLD? If so, when you write the second report DSQPRINT is reopened - overwriting the first. You have to use DISP=MOD.
James Campbell
On 11 Sep 2002 at 12:30, Oliver Su wrote:
> Hi Listers, > > When I run a QMF batch job with PROC contains two reports such as: > RUN query1 (form=form1 > PRINT REPORT > RUN query2 (form=form2 > PRINT REPORT > > The job output shows only the second report. > Any resolution? > > Thanks, > Oliver > > -- > __________________________________________________________ > Sign-up for [...]
16265 35 49_Re: Calling DSNTIAUL utility from a COBOL Program14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 12 Sep 2002 08:27:37 +1000332_US-ASCII "if the results of using putenv are what you want them to be, then use it."
There are other ways. But a way that works and one is comfortable with is the best way. It appears that putenv is best _for you_. It might not be for others.
James Campbell
On 11 Sep 2002 at 18:15, Leonardo Zrycki wrote: [...]
16301 47 53_Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX14_Vasu Kamireddi33_vkamireddi@STARVIEWTECHNOLOGY.COM31_Wed, 11 Sep 2002 15:28:56 -0700425_iso-8859-1 Hi List,
Please forgive me if it's a too small question for this list. I'm new to DB2.
Well, my question is:
Does DB2 UDB support function based indexes / Is a function based index required if I have to use a function on the column of a query?
Here are the details:
I have a table with a column called "item_name". I need to do a case-insensitive sorting on the result set. [...]
16349 46 36_Re: HELP HELP! dDB2 UDB ON NT CRISIS13_David S Waugh16_dsw-dba@JUNO.COM29_Wed, 11 Sep 2002 22:39:51 GMT415_- Maybe the DB2 Fairies came in while everyone was in the meeting and fixed it. Or the DB2 Gremlins left for the day. They may be back tomorrow...
David :-)
---------- Aurora Dell'Anno writes:
From: Aurora Dell'Anno To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: HELP HELP! dDB2 UDB ON NT CRISIS Date: Wed, 11 Sep 2002 01:17:30 -0500 [...]
16396 33 25_stored procedure question11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Wed, 11 Sep 2002 15:06:02 -0700400_US-ASCII We are just starting to look at stored procedures (db2 V5 going to V7 later this year). As the DBA, I have a few questions before we share the knowledge with our application programmers.
1. How good are stored procedures in V5? better in V7? 2. In other shops, does the DBA build the stored procedure or does the application team? 3. What kind of standards does the DBA enforce? [...]
16430 109 24_Re: DSN9023I on -STOP DB12_Raymond Bell17_rbell@NZ1.IBM.COM31_Thu, 12 Sep 2002 11:00:01 +1200583_us-ascii Hi Scott,
My favourite 'all wet' former DB2 sysprog, and now frustrated applications DBA, has already made some good points. I just thought I'd chip in with a few more.
We don't stop, start, quiesce, or in any way bugger around with the objects we're about to image copy; we just submit the sharelevel(reference) copy and hope for the best. Never had a contention issue, but then we're pretty small DB2-wise. Even if we were a full-on, pushing the edge of the envelope environment, I still don't think we'd do any of that stuff. What you might want to do [...]
16540 19 21_Re: QMF: PRINT REPORT9_Oliver Su14_ojsu@EMAIL.COM31_Wed, 11 Sep 2002 15:59:38 -0800132_iso-8859-1 Thank you all. DISP=MOD is the key to output multiple reports to one output dataset. My problem resolved.
16560 81 29_Re: stored procedure question12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 11 Sep 2002 17:02:15 -0700591_us-ascii Here's my opinion, but I'll freely admit before hand that I'm highly prejudiced and biased.
V5 stored procedures are (imho) marginally useful. They must be in a programming language and a DBA (usually) must do all of the DB2 definitions for them to DB2. This means that real programs must be written for all of the stored procedures. This might be fine in your shop, that I can't say. For some shops this is all they need. You can access almost anything from the procedural language stored procedures so they do provide significant functionality. But V7 has it all over [...]
16642 75 53_Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 11 Sep 2002 17:09:26 -0700362_us-ascii To the best of my knowledge, DB2 UDB doesn't support function based indexes.
But in your example, the only thing an index would be used for on your Order by is the elimination of a sort. DB2 will still search by an index on item_name if it exists and is referenced in your where clause. And it will sort the data properly with the ORDER BY. [...]
16718 139 52_Greensboro NC-SIRDUG/TRIDUG hosting vendor fair 9/2611_Paul Turpin32_pault@ALUMMAIL.SSB.ROCHESTER.EDU31_Wed, 11 Sep 2002 20:48:33 -0500607_ISO-8859-1 Join us for the 2002 Southeastern Database Vendor Fair in Greensboro, NC (near the Greensboro Airport) on Thursday, September 26th. This year's Vendor Fair theme is "Perform Better, Produce More". If that sounds like your management's mantra, then this is the event for you. We have lined up numerous vendors covering a wide variety of database products. Whatever you need to manage your data more effectively, we have vendors who can help you, including tools to help you comply with HIPAA or the Gramm, Leach, Bliley Acts. Here is your chance to - Scout out products your company needs. - [...]
16858 127 46_Re: Poor SQL, any ideas? DB2 UDB on Windows NT13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 11 Sep 2002 21:46:05 -0500334_iso-8859-1 David,
Anything can make a slight difference, and that may be enough. The issue with BETWEEN vs ">= AND <=" was resolved in about V4 on DB2 OS/390. They still have slightly different filter factors but other than that are pretty much equivalent. Changing a range to a BETWEEN may also impact the filter factor. [...]
16986 43 52_Re: DB2 SQL Function for LUWID or thread identifier?11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 11 Sep 2002 22:06:53 -0500342_- Scott,
I am not aware of any means of doing exactly what you ask for.
Assuming the reason you need to know is to take alternative actions, you can call a Stored Procedure (use the dbinfo parm) which makes the token available to the SP. The SP could take different actions then. The trigger itself (as far I know) cannot. [...]
17030 67 24_Re: DSN9023I on -STOP DB6_Al Pat18_alpat225@YAHOO.COM31_Wed, 11 Sep 2002 20:33:31 -0700357_us-ascii
STOP DB is not recommended as it closes the datasets and this is not good if your table spaces are defined CLOSE NO. We issue START DB(whatever) ACCESS(RO), Image copy, then ACCESS(RW). "Ruggles, Scott" wrote:We are running version DB2 V7 and having an occasional problem with our nightly image copies, and I have a two-part question. [...]
17098 57 29_Repost - DSN9023I on -STOP DB12_Raymond Bell17_rbell@NZ1.IBM.COM31_Thu, 12 Sep 2002 15:44:38 +1200367_us-ascii Colleagues,
Just been advised that the post below was rejected as a duplicate, which I think is incorrect. If not, please ignore my repost.
Raymond
Hi Scott,
My favourite 'all wet' former DB2 sysprog, and now frustrated applications DBA, has already made some good points. I just thought I'd chip in with a few more. [...]
17156 102 17_Re: Join vs. Case13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 11 Sep 2002 22:57:15 -0500577_iso-8859-1 David,
We did some testing on this primarily because we were had a single large CASE expression in a NTE merged with an outer query so it was being executed 5 times. When we introduced materialization (using the RAND function in V7) we cut CPU significantly. We also compared this with multiple CASE and a join scenario, but we had a code table that spanned 2 index leaf pages and our getpage count went thru the roof as DB2 flip-flopped between these 2 pages (even with index lookaside). We did find a point however when the join outperformed the CASE, [...]
17259 74 24_Re: DSN9023I on -STOP DB6_Al Pat18_alpat225@YAHOO.COM31_Thu, 12 Sep 2002 05:52:50 +0200357_us-ascii
STOP DB is not recommended as it closes the datasets and this is not good if your table spaces are defined CLOSE NO. We issue START DB(whatever) ACCESS(RO), Image copy, then ACCESS(RW). "Ruggles, Scott" wrote:We are running version DB2 V7 and having an occasional problem with our nightly image copies, and I have a two-part question. [...]
17334 114 53_Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 11 Sep 2002 23:04:26 -0500446_US-ASCII Vasu,
You can ORDER BY FUNCTION(ITEM_NAME) in all DB2's (as of V7 in z/OS).
You may want to check out generated columns. I haven't worked with them, but have seen that you can create a generated column that is for example the LOWER(ITEM_NAME) that you may name LOWER_ITEM_NAME, and index the resultant column. When DB2 sees the LOWER(ITEM_NAME) in the WHERE clause it can actually use the index on the derived column. [...]
17449 116 33_Re: Result from the query to big.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 11 Sep 2002 23:19:04 -0500583_iso-8859-1 Unfortunately no. DB2 cannot avoid a sort using indexes across 2 tables.
One suggestion is to remove A.SERVICE from the SELECT list (just saved 3 bytes X number of rows). This is probably not going to be enough though.
A couple of other suggestions that may be completely useless. You could denormalize the two tables so you have the opportunity to utilize a single index to avoid a sort (yuk). Or separate the 2 queries into 2 cursors so that each can individually utilize an index to avoid a sort (another yuk but more possible than the first). Or as [...]
17566 13 29_Index Rebuild, SYSUT1 on tape12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Wed, 11 Sep 2002 16:04:00 -0400435_- Anyone care to share the DD statement you are using to put SYSUT1 on tape? We are getting S713-04 abends on large indexes when using SYSUT1 in the SORT phase. Thanks in advance.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
17580 74 31_Re: Help! Trying to free a plan19_Alekos Papadopoulos13_apapad@NBG.GR31_Thu, 12 Sep 2002 08:48:39 +0200422_iso-8859-7 Just a guess, that might not work: Did you try to put plan names in double quotes? (I.e. FREE PLAN (" SYSIND") - taking care of the first two chars to have the correct hex value). If it doesn't work, I remember a case where some unprintable characters ("~" in our case in an SAP ss, had to be replaced by "@", but I don't know if it works in your case / We have a special CSID to support Greek characters) [...]
17655 93 37_Please comment on a recovery scenario17_Douglas Hawthorne33_douglas.hawthorne@CARDLINK.COM.AU31_Thu, 12 Sep 2002 15:49:39 +1000439_iso-8859-1 I would like comments on the following disaster recovery scenario on UDB for OS/390 V6.1:
(1) -DB2P ARCHIVE LOG MODE(QUIESCE) is run on the production database with the log archives being saved to DASD and TAPE. (2) FDR backup is run against all DB/2 packs (data, system, and online log archives). This takes approx. 8 minutes. (3) These backups are sent offsite and restored onto the DR site once each working day. [...]
17749 51 33_Re: Index Rebuild, SYSUT1 on tape12_Steve Howell23_HOWELLS@STGEORGE.COM.AU31_Thu, 12 Sep 2002 15:58:12 +1000403_us-ascii Hi Bob,
How about this ?
//SYSUT1 DD UNIT=CART,DISP=(,PASS)
Regards
Steve Howell Database Administration (612) 9952 1468
>>> BOB.JEANDRON@USDA.GOV 09/12/02 06:04am >>> Anyone care to share the DD statement you are using to put SYSUT1 on tape? We are getting S713-04 abends on large indexes when using SYSUT1 in the SORT phase. Thanks in advance. [...]
17801 106 59_AW: Re: Problem with passing file in SYSTSIN under IKJEFT0141_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Thu, 12 Sep 2002 08:40:08 +0200679_iso-8859-1 Amit,
does the following JCL help ?
Ruediger
//BIND EXEC PGM=IKJEFT01,DYNAMNBR=20 //STEPLIB DD DSN=your-DB2-Load-Library,DISP=SHR //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(ssid) REBIND PACKAGE( collid.package.+ (version)) + OWNER(owner) + VALIDATE(BIND) ISOLATION(CS) FLAG(I) + RELEASE(COMMIT) EXPLAIN(YES) + CURRENTDATA(NO) + DEGREE(1) + QUALIFIER(qualifier) END -----Ursprüngliche Nachricht----- Von: Mackey, Glenn [mailto:GMackey@GUIDEMAIL.COM] Gesendet: Mittwoch, 11. September 2002 18:30 An: DB2-L@LISTSERV.YLASSOC.COM Betreff: Re: Problem with passing file in SYSTSIN under IKJEFT01 [...]
17908 35 80_question about one of the features of version next (the version after version 7)0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Thu, 12 Sep 2002 00:44:11 -0500402_us-ascii I almost was able to ask this during the tech conference, but I do not think that I will get it in. I am wondering if the version after version 7 would support either a "SHRLEVEL CHANGE" or a "SHRLEVEL REFERENCE" for the reorganization of a LOB tablespace. We are having a hand full of applications beginning to want LOB functionality. However, they also want close to 24x7 availability. [...]
17944 31 45_Calling DSNTIAUL utility from a COBOL Program15_Leonardo Zrycki29_leonardo.zrycki@ACCENTURE.COM31_Thu, 12 Sep 2002 08:41:51 +0200633_us-ascii James:
Thanks. This answers my question
"File allocations are global to an address space. Any program running in one has access to all files allocated in it."
What is the meaning of: "If you are happy with PUTENV, then fine."
Do you think that exist a better way to allocate files.
Leonardo
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. [...]
17976 96 37_Please comment on a recovery scenario17_Douglas Hawthorne33_douglas.hawthorne@CARDLINK.COM.AU31_Thu, 12 Sep 2002 08:42:52 +0200439_iso-8859-1 I would like comments on the following disaster recovery scenario on UDB for OS/390 V6.1:
(1) -DB2P ARCHIVE LOG MODE(QUIESCE) is run on the production database with the log archives being saved to DASD and TAPE. (2) FDR backup is run against all DB/2 packs (data, system, and online log archives). This takes approx. 8 minutes. (3) These backups are sent offsite and restored onto the DR site once each working day. [...]
18073 115 52_DB2 V6.1: Coding existence checking via SQL in COBOL12_Rodney Krick30_rk@HAENCHEN.SOFTWAREZENTRUM.DE31_Thu, 12 Sep 2002 09:26:58 +0200460_iso-8859-1 No, you cannot! Just user :HV1. The use of :HV2 is wrong.
Mit freundlichen Grüßen / Best regards,
Rodney Krick Hänchen & Partner GmbH Calwer Straße 1 D-71034 Böblingen
Rama Kypa Kopie: Gesendet von: DB2 Thema: Data Base Discussion List
11.09.2002 22:29 Bitte antworten an DB2 Data Base Discussion List [...]
18189 65 24_Re: DSN9023I on -STOP DB17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 12 Sep 2002 09:38:29 +0200563_us-ascii 1. Why should this be not good? (Here, only the Catalog & RLIMIT TSs/IXs are CLOSE NO; everything else is CLOSE YES. If you have a mixture of both, you can get into grave problems under certain conditions. Look in the archives. Note that the CLOSE semantics have changed from what they were 10 years ago.). 2. Why do people keep restricting access to their data when running a simple Imagecopy? SHRLEVEL CHANGE is here (and has been for some time)! 3. I seem to remember that if you do a -STA... ACCESS(RO), it will NOT prevent further updates from [...]
18255 121 25_stored procedure question12_Rodney Krick30_rk@HAENCHEN.SOFTWAREZENTRUM.DE31_Thu, 12 Sep 2002 09:47:52 +0200588_iso-8859-1 Hi Kathy,
I've worked in a project two years ago, and we implemented stored procedures for "exchanging" data between two sites. It was DB2 V6. 1. How good are stored procedures in V5? better in V7? --> The experience with V6 was good. We had some problems with LE370 and SPAS (no experience... I can't remember exactly what). 2. In other shops, does the DBA build the stored procedure or does the application team? --> We (the Appl.Dev. Team) developed the stored procedures; the DBAs "installed" them on DB2. 3. What kind of standards does the DBA enforce? --> It [...]
18377 38 39_Re: IDCAMSdefinitions for catalog REORG12_David Wilson30_David_A_Wilson@JOHNLEWIS.CO.UK31_Thu, 12 Sep 2002 10:14:01 +0100526_us-ascii David, Norbert
Many thanks for your replies. The CBT tape is just what I needed.
Dave Wilson DBA X4248
*********************************************************************
Notice: This email is confidential and may contain copyright material of the John Lewis Partnership. If you are not the intended recipient, please notify us immediately and delete all copies of this message. (Please note that it is your responsibility to scan this message for viruses). [...]
18416 35 39_Re: IDCAMSdefinitions for catalog REORG13_LANGLEY Moira25_moira.langley@ATOFINA.COM31_Thu, 12 Sep 2002 11:16:04 +0200312_us-ascii
Or you could take a look at Dr. Ebert's Catalog Reorg JCL etc. in the DB2-L documents archive (Nov 2001). I've found it useful. If you really want to automate the resizing process then it probably wouldn't be too hard to combine his catlist rexx with the IDCAMS definitions in catsize.doc. [...]
18452 206 22_Re: Reverse index scan14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 12 Sep 2002 10:57:47 +0100482_iso-8859-1 Reverse Index Scan??
Not yet.
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com
-----Original Message----- From: Daniel Adam [mailto:dadam@GRATEX.COM] Sent: 11 September 2002 17:46 To: DB2-L@LISTSERV.YLASSOC.COM Subject: [DB2-L] Reverse index scan
Hi all, Can DB2 for os/390 v7 use an index in revers order ? Example: [...]
18659 209 22_Re: Reverse index scan14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 12 Sep 2002 12:18:51 +0200482_iso-8859-1 Reverse Index Scan??
Not yet.
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com
-----Original Message----- From: Daniel Adam [mailto:dadam@GRATEX.COM] Sent: 11 September 2002 17:46 To: DB2-L@LISTSERV.YLASSOC.COM Subject: [DB2-L] Reverse index scan
Hi all, Can DB2 for os/390 v7 use an index in revers order ? Example: [...]
18869 33 52_Rejected posting [was Repost - DSN9023I on -STOP DB]14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 12 Sep 2002 21:44:17 +1000442_US-ASCII If you look at the mail headers you'll see it went from you (original posting) to listserv.ylassoc.com to db2.admin@JULIUSBAER.COM (original distribution) and from there back to listserv.ylassoc.com.
It's the "posting" from db2.admin@JULIUSBAER.COM that is being rejected. (Better this way than the great flood of a few years ago when re-postings weren't rejected - which leads to another re-posting - which leads .... ) [...]
18903 78 26_Re: -101 After DB2 Upgrade11_Tina Hilton29_Tina.Hilton@ARVATOSYSTEMS.COM31_Thu, 12 Sep 2002 07:07:37 -0500397_iso-8859-1 I agree. Although if they can't lift the 32K limit, I think it should be on the compressed SQL with the only spaces counted being the ones between words. Otherwise, these complicated SQL statements are impossible to understand when you remove the formatting (indenting, blank lines, etc.). For me (a systems DBA), they near to impossible to understand even when they're formatted. [...]
18982 64 24_Re: DSN9023I on -STOP DB11_Tina Hilton29_Tina.Hilton@ARVATOSYSTEMS.COM31_Thu, 12 Sep 2002 07:26:04 -0500458_iso-8859-1 We hit this problem after upgrading to V6 with "-sto db(xxx) space(*)" and it was a bug, but I was also getting 0C4 abends in DB2 (DB2 didn't actually fail, just issued the abends). Have you checked syslog or your DB2 started tasks to see if you're getting any abends? I looked in IBMLink and see there's a V7 of the bug too. The fixing PTF is UQ53954. See if you have that PTF on and if you're getting the 0C4 dumps when you issue the stop. [...]
19047 115 24_Re: DSN9023I on -STOP DB11_Tina Hilton29_Tina.Hilton@ARVATOSYSTEMS.COM31_Thu, 12 Sep 2002 07:29:19 -0500423_iso-8859-1 In reply to #2, we have a couple of applications that we refresh test from production image copies via DSN1COPY and they want it to be an exact snapshot of what it was like then. So, that's one reason to do it.
Tina
-----Original Message----- From: Dr. Michael Ebert [mailto:mebert@AMADEUS.NET] Sent: September 12, 2002 2:38 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: DSN9023I on -STOP DB [...]
19163 75 22_Re: Reverse index scan14_Hickman, Mindy29_Mindy.Hickman@DEG.STATE.WI.US31_Thu, 12 Sep 2002 07:44:01 -0500510_iso-8859-1 I was at a session at IDUG with James Guo where I was told that this is possible now with version 7. The accesstype that indicates DB2 is doing one fetch index access is I1.
Mindy Hickman State of Wisconsin DB2 DBA IBM Certified Solutions Expert 608-261-5059 mindy.hickman@deg.state.wi.us
-----Original Message----- From: Daniel Adam [mailto:dadam@GRATEX.COM] Sent: Wednesday, September 11, 2002 11:46 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Reverse index scan [...]
19239 126 41_Re: Please comment on a recovery scenario11_Tina Hilton29_Tina.Hilton@ARVATOSYSTEMS.COM31_Thu, 12 Sep 2002 07:49:32 -0500579_iso-8859-1 If you're taking an inflight backup of your DB2 volumes (DB2 remains up and updates may be occuring), then your results are unpredictable. When you start DB2 on your DR system, there may be problems if some updates were captured and some not. The updates may or may not be reflected in the log depending upon when the log was backed up by FDR. Even if you do dump the DB2 catalog/directory and logs last, it won't automatically apply updates to the tables that occurred after those volumes were dumped. DB2 will assume that anything committed exists on the DASD. [...]
19366 115 41_Re: Please comment on a recovery scenario12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Thu, 12 Sep 2002 08:14:24 -0500311_iso-8859-1 That is my backup strategy to a tee. Are you using FDRSNAP for the back up during the quiesced period to ensure no activity is missed. My recovery test is this month so I will be able to tell you if it works after that point. This is the first true test for me since going to this new strategy. [...]
19482 123 41_Re: Please comment on a recovery scenario12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Thu, 12 Sep 2002 08:18:22 -0500471_iso-8859-1 ***correction***. I am also doing the SET LOG SUSPEND and SET LOG RESUME commands as well as noted in Tina's post.
Buzz
__________________
That is my backup strategy to a tee. Are you using FDRSNAP for the back up during the quiesced period to ensure no activity is missed. My recovery test is this month so I will be able to tell you if it works after that point. This is the first true test for me since going to this new strategy. [...]
19606 31 22_Re: Reverse index scan10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 12 Sep 2002 15:29:31 +0200535_us-ascii Hi Mindy
I'm a little bit perplexed as in Rome James Guo, while he was talking about MAX/MIN enhancement in V7 and answering to a question about the possibility for DB2 to read index backwards, said this is not a 'real' possibility (ie DB2 cannot read for instance leaf pages forwards and backwards) but DB2 in V7 can read the top and the botton to evaluate MIN/MAX using only one index without having one ascending and one descending indexes (ie now DB2 can read the 'head' and the 'tail end' of the same index). [...]
19638 88 29_Re: stored procedure question14_Theisen, Craig22_CTheisen@GUIDEMAIL.COM31_Thu, 12 Sep 2002 09:27:03 -0500497_iso-8859-1 Hi Kathy........
#1. As others have said, in V5 you must write your stored procedures in a language such as COBOL. In our shop that has not been a big issue because we have quite a few COBOL programmers, however I look forward to using SP Builder in the near future so our WEB team of JAVA developers can do their own. Outside of a little extra development effort, the stored procedures we created in V5 with COBOL have worked great for WEB, GUI, CICS & Batch applications. [...]
19727 85 41_Re: Please comment on a recovery scenario15_John C. Lendman20_John_Lendman@FPL.COM31_Thu, 12 Sep 2002 10:27:34 -0400355_us-ascii I have done something very similar. And I have tested it and it works if you want to recover to a PIT. (point in time)
I put all the tablespace in READ ONLY mode. I then do the archive log mode QUIESCE. I then take a 'FLASHCOPY' of all my volumes. This by the way includes the DB2 catalog and directory. Active logs and Archive logs. [...]
19813 38 33_Re: Index Rebuild, SYSUT1 on tape14_Gary Bernhardt30_gary.bernhardt@MAIL.SPRINT.COM31_Thu, 12 Sep 2002 09:36:15 -0500487_ISO-8859-1 Why not just remove the sysut1 dataset from your jcl all together.
If it is not present, the rebuild utility bypasses outputing index entries to the temp file, and loads them directly into the index dataset. This results in a faster rebuild because fewer i/o are needed.
-----Original Message----- From: BOB.JEANDRON [mailto:BOB.JEANDRON@USDA.GOV] Sent: Wednesday, September 11, 2002 3:04 PM To: DB2-L Subject: Index Rebuild, SYSUT1 on tape [...]
19852 66 33_Re: Index Rebuild, SYSUT1 on tape13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 12 Sep 2002 14:47:37 GMT870_- Bob, this is what I use for REORGing large tablespaces:
//* SYSREC IS WHERE THE TABLESPACE DATA IS UNLOADED TO PRIOR TO RELOAD //* IT SHOULD BE THE SIZE OF THE LARGEST TABLESPACE PLUS 10% //SYSREC DD DSN=DBA.&JOBNAME..REORG.&DBNAMEW..SYSREC, // DISP=(MOD,DELETE,CATLG), //* DCB=BUFNO=30,UNIT=(DBAWK,3), <=== TO DASD //*** VOL=SER=(DBA001,DBA002), <=== TO DASD //* SPACE=(CYL,(1300,260),RLSE) <=== TO DASD // UNIT=CART, <=== TO TAPE // DCB=(ADM.TAPE.GDGMODEL,BLKSIZE=32760,BUFNO=30,TRTCH=COMP) //* SORTOUT IS A TEMPORARY WORK FILE FOR SORT OUTPUT. //SORTOUT DD DSN=DBA.&JOBNAME..REORG.&DBNAME..SORTOUT, // DISP=(MOD,DELETE,CATLG), //* DCB=BUFNO=30,UNIT=(DBAWK,3), <=== TO DASD //*** VOL=SER=(DBA003,DBA004), <=== TO DASD //* SPACE=(CYL,(650,130),RLSE) <=== TO DASD // UNIT=CART, <=== TO TAPE // DCB=(ADM.TAPE.GDGMODEL,BLKSIZE=32760,BUFNO=30,TRTCH=COMP) //* [...]
19919 48 18_DB2 MODULES in LPA12_Bejoy Kurian28_Bejoy.Kurian@EDWARDJONES.COM31_Thu, 12 Sep 2002 10:00:33 -0500429_US-ASCII Hi All, Did anybody tried putting DB2 modules in LPA other than documented in DB2 installation manual. Strobe shows the following modules taking very high CPU and wants to know any benefits on putting them in LPA.
DSNAPRH DSNBBM DSNGEDM DSNIDM DSNJL002 DSNK2DM DSNRGLM1 DSNSLD1 DSNTGSCC DSNTLM DSNVGEPL DSNVRMEL DSNVSR DSNWVAPR DSNWVCOL DSNWVRC2 DSNWVSR1 DSNWVZPS DSNWVZSA DSNWZDGO DSNXEEZ DSNXGRDS DSN3AMGP [...]
19968 33 33_Re: Index Rebuild, SYSUT1 on tape14_Gary Bernhardt30_gary.bernhardt@MAIL.SPRINT.COM31_Thu, 12 Sep 2002 16:51:54 +0200487_ISO-8859-1 Why not just remove the sysut1 dataset from your jcl all together.
If it is not present, the rebuild utility bypasses outputing index entries to the temp file, and loads them directly into the index dataset. This results in a faster rebuild because fewer i/o are needed.
-----Original Message----- From: BOB.JEANDRON [mailto:BOB.JEANDRON@USDA.GOV] Sent: Wednesday, September 11, 2002 3:04 PM To: DB2-L Subject: Index Rebuild, SYSUT1 on tape [...]
20002 23 39_Re: IDCAMSdefinitions for catalog REORG0_17_JTonchick@AOL.COM29_Thu, 12 Sep 2002 11:29:36 EDT375_US-ASCII SDSNSAMP(DSNTIJIN) contains the skeleton for all the IDCAMS defines needed to initialize all a subsystem's files including Boot Strap and logs. I keep a separate PDS for each subsystem - DB2T.VSAMCNTL where each file has it's own member with the IDCAMS control cards. This makes it easy to create JCL that takes the subsystem, dbdname and object name as parms. [...]
20026 26 79_Execute a cobol Micro Focus stored procedure from the command line. DB2 EEE AIX11_Mike Fatula22_mfatula@AESSUCCESS.ORG31_Thu, 12 Sep 2002 11:35:39 -0400561_us-ascii Can anyone give me the syntax to perform this.
example
db2 "CALL .J2LGS ('006587222',' ',' ',' ')"
there is one input field and 3 output fields
I keep getting the error
DB21101E Too few command line parameters specified for the stored procedure.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
20053 54 24_Re: DSN9023I on -STOP DB13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 12 Sep 2002 16:36:02 GMT550_- I know I'm going to get a ration of sh__ from some of the people on the list for some of this (particulary #2), but I have a couple of things to add:
1. I think Al Pat may have meant to say CLOSE YES. I remember that (prior to V4, I think) CLOSE YES had some pretty adverse consequences, partly because DB2 did a physical close of the dataset, and partly because of the processing that was done to SYSLGRNG during the CLOSE process. These days, DB2 goes through a "psuedo-close" process that doesn't have the same adverse consequences. [...]
20108 20 27_UNLOAD data into PDF format23_GORRELLA, SRINI (SBCSI)14_sg7974@SBC.COM31_Thu, 12 Sep 2002 11:47:34 -0500449_iso-8859-1 Hi All,
Is their any way after UNLOAD we can covert the output file on Mainframe (or on Hard drive ) to .PDF format .
Hope you can help me out in this
TIA, Srini
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
20129 57 59_DB2 SQL Function for LUWID or thread identifier? - perhaps.11_Suresh Sane21_data_arch@HOTMAIL.COM31_Thu, 12 Sep 2002 11:50:20 -0500611_- >Scott, > >I am not aware of any means of doing exactly what you ask for. > >Assuming the reason you need to know is to take alternative actions, you >can >call a Stored Procedure (use the dbinfo parm) which makes the token >available to the SP. The SP could take different actions then. The >trigger >itself (as far I know) cannot. > >HTH and I will watch this thread to see if anyone has ideas. > >Suresh > > > >From: "Scott.Gjerdingen" > >Reply-To: DB2 Data Base Discussion List > >To: DB2-L@LISTSERV.YLASSOC.COM > >Subject: DB2 SQL Function [...]
20187 42 29_Index Rebuild, SYSUT1 on tape14_Mark McCormack27_mamccormack@STATESTREET.COM31_Thu, 12 Sep 2002 13:44:39 -0400331_us-ascii Anyone care to share the DD statement you are using to put SYSUT1 on tape? We are getting S713-04 abends on large indexes when using SYSUT1 in the SORT phase.
Bob,
I remember getting S713-04 on SYSUT1 in REBUILD INDEX, and I don't think it has to do with the SYSUT1 dd stmt parameters. [...]
20230 17 33_RTFM & supercilious BP parameters14_Mark McCormack27_mamccormack@STATESTREET.COM31_Thu, 12 Sep 2002 13:54:32 -0400400_us-ascii Holger,
I feel better knowing that someone can remember noun plural forms for both 2nd and 3rd declensions in Latin.
Mark
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
20248 39 31_Re: UNLOAD data into PDF format11_Tina Hilton29_Tina.Hilton@ARVATOSYSTEMS.COM31_Thu, 12 Sep 2002 13:12:48 -0500399_iso-8859-1 Check out http://www.lbdsoftware.com/. Lionel has a TXT2PDF that might do what you want. I haven't tried it yet, so I don't know for sure but it sounds right.
Tina Hilton arvato systems
-----Original Message----- From: GORRELLA, SRINI (SBCSI) [mailto:sg7974@SBC.COM] Sent: September 12, 2002 11:48 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: UNLOAD data into PDF format [...]
20288 74 31_Re: UNLOAD data into PDF format15_Itschak Mugzach26_i_mugzach@SECURITEAM.CO.IL31_Thu, 12 Sep 2002 19:58:04 +0200307_iso-8859-1 Srini,
Look at Lionel B. dyck homepage. He just published new code that does this. This is a cut from his email found in the MVS group:
This morning, perhaps to demonstrate that the terrorists did not win and that life and work still continues, I am releasing via my web site: [...]
20363 58 32_DB2 6.1 UDB for Windows Question15_John C. Lendman20_John_Lendman@FPL.COM31_Thu, 12 Sep 2002 14:55:02 -0400595_us-ascii John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413 ----- Forwarded by John Lendman/IM/FPL on 09/12/02 02:48 PM -----
John Lendman
To: DB2-L@listserv.ylassoc.co 09/12/02 02:47 PM cc:
Subject: DB2 6.1 UDB for Windows Question
I am having problems connecting to one of my database on a different workstation. When ever I try to connect using Command Center or Control Center I get the following error. This use to work, but stopped working about a month ago. I have asked the PC experts here and they have no idea. [...]
20422 24 37_Re: RTFM & supercilious BP parameters16_Dennis H Werling18_DH-Werling@WIU.EDU31_Thu, 12 Sep 2002 14:02:18 -0500610_ISO-8859-1 Reading the DB2 Listserv reminds me of General Joseph ("Vinegar Joe") Stillwell's bastardized motto: Illegitimati non carborundum est.
dh-werling@wiu.edu
Quoting Mark McCormack : > I feel better knowing that someone can remember noun plural forms for > both 2nd and 3rd declensions in Latin. > > Mark
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
20447 48 17_S0C4 in a DB2SPAS15_Donnie Woodruff19_dwoodruff@NNGOV.COM31_Thu, 12 Sep 2002 15:11:38 -0400521_iso-8859-1 DB2L'ers,
I am trying to execute my first DB2 Stored Procedure on DB2 v6.1 for OS390 in a non-WLM environment. I am executing a simple COBOL stored procedure which executes correctly that has a CURSOR defined WITH RETURN FOR. But, once I have executed this COBOL stored procedure other stored procedure (the one's shipped with Control Center) fail with the following message: SQL0430N User defined function "PROCEDURE" (specific name "SYSPROC .DSNACCAV ") has abnormally terminated. SQLSTATE=38503 [...]
20496 28 26_Re: - DSN9023I on -STOP DB13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 12 Sep 2002 19:16:57 GMT403_- Thank you Raymond. The lights in my office thank you too... What's that? Oh - they're telling me to stop wasting time and get back to work! Gotta go! [Must... Obey... the... Lights...]
David
---------- Raymond Bell writes:
Hi Scott,
My favourite 'all wet' former DB2 sysprog, and now frustrated applications DBA, has already made some good points. [...]
20525 30 14_Deleting stats12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Thu, 12 Sep 2002 15:12:14 -0400455_us-ascii Hi All. We are V6 and OS/390 2.10. We are having performance problems with a few of our Peoplesoft processes running long. What we found out(many thanks to Jeff L'italien) was that by deleting rows out of SYSCOLDIST and SYSCOLDISTSTAT the processes went back to normal run times. Our question is, does anyone else do this after running Runstats? Has anyone else encountered this and was there a PTF applied to resolve it? i have searched IBMLINK.
20556 50 36_Re: DB2 6.1 UDB for Windows Question15_Russell Collins26_RCollins@ZCOREBUSINESS.COM31_Thu, 12 Sep 2002 14:34:17 -0500
20607 119 53_Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX0_22_BRIAN_GOLDBERG@QVC.COM31_Thu, 12 Sep 2002 15:23:52 -0400594_us-ascii It's possible that a table expression with the function in the table expression may help. The following article has some info on table expressions (if this doesn't show up as a link, you may have to copy and paste):
http://www7b.boulder.ibm.com/dmdd/library/techarticle/0204larsen/0204larsen.html
Brian
Myron Miller cc: (bcc: BRIAN GOLDBERG/QVC) Ext: NA Subject: Re: Indexes and Standard Functions: DB2 UDB Sent by: DB2 Data Win2k/AIX Base Discussion List [...]
20727 24 37_Re: RTFM & supercilious BP parameters16_Dennis H Werling18_DH-Werling@WIU.EDU31_Thu, 12 Sep 2002 21:30:54 +0200610_ISO-8859-1 Reading the DB2 Listserv reminds me of General Joseph ("Vinegar Joe") Stillwell's bastardized motto: Illegitimati non carborundum est.
dh-werling@wiu.edu
Quoting Mark McCormack : > I feel better knowing that someone can remember noun plural forms for > both 2nd and 3rd declensions in Latin. > > Mark
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
20752 33 56_Re: Greensboro NC-SIRDUG/TRIDUG hosting vendor fair 9/2613_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 12 Sep 2002 19:45:12 GMT465_- [Soapbox/Lament on] "Perform Better, Produce More"? In many of the companies I've worked for, it's more like "Do More with Less".
When translated, of course, this usually means: "Work longer hours, for less money, with little training and few tools -- you DBAs are overhead, and you cost too much". Would that it were easy to convince management that a healthy investment in tools & training would pay dividends far beyond what the bean counters see. [...]
20786 49 41_Re: Deleting stats (on Peoplesoft tables)12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 12 Sep 2002 12:49:24 -0700609_us-ascii We're having that very same problem. I've opened a problem with IBM and Psoft but we haven't gotten anywhere yet. IBM problem number is 50127-550. And the problems aren't just on the "temporary worktables" that PSoft uses. We're chasing this as an optimizer problem. It appears we have all the known optimizer PTFs applied on V6. Would any other PSoft users having this problem where they get worse performance with runstats than without runstats respond to this thread so we can get an idea of how many customers are experiencing this. Especially if you've found a solution OTHER than deleting [...]
20836 76 41_Re: Deleting stats (on Peoplesoft tables)14_Gary Bernhardt30_gary.bernhardt@MAIL.SPRINT.COM31_Thu, 12 Sep 2002 15:10:11 -0500567_ISO-8859-1 In some cases runstats helps, in others it hurts. We started out with PS7 and DB2v4. It has always been this way for us and still is on PS8 and DB2v7.
We learned to supply our own stats to for tables to maintain adequate performance. These statistics are derived from experience and trial and error. We have an automated process that ensures the catalog statistics are kept current, which consists of update statements to the catalog stat columns. In some cases we had to provide non-uniform distribution stats to make certain queries perform. [...]
20913 135 53_Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX0_22_BRIAN_GOLDBERG@QVC.COM31_Thu, 12 Sep 2002 16:32:58 -0400387_us-ascii I received a rejected posting on this. Sorry to anyone who receives this twice.
Brian ----- Forwarded by BRIAN GOLDBERG/QVC on 09/12/2002 04:32 PM -----
BRIAN GOLDBERG Ext: 1210 To: DB2 Data Base Discussion List 09/12/2002 03:23 cc: PM Subject: Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX(Document link: BRIAN GOLDBERG) [...]
21049 59 41_Re: Deleting stats (on Peoplesoft tables)14_Gary Bernhardt30_gary.bernhardt@MAIL.SPRINT.COM31_Thu, 12 Sep 2002 22:32:54 +0200567_ISO-8859-1 In some cases runstats helps, in others it hurts. We started out with PS7 and DB2v4. It has always been this way for us and still is on PS8 and DB2v7.
We learned to supply our own stats to for tables to maintain adequate performance. These statistics are derived from experience and trial and error. We have an automated process that ensures the catalog statistics are kept current, which consists of update statements to the catalog stat columns. In some cases we had to provide non-uniform distribution stats to make certain queries perform. [...]
21109 30 53_Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX13_David S Waugh16_dsw-dba@JUNO.COM29_Thu, 12 Sep 2002 21:12:06 GMT441_- EVERYONE who posts to DB2-L has been getting these "duplicate posting" messages. Evidently, one of our subscribers (db2.admin@juliasbaer.com or mathius.weinmann@juliasbaer.com) is somehow causing each of our posts to be bounced back to DB2-L, which tries to re-process them.
No need to send your posts in again -- they made it the first time. And a second time from juliasbaer.com. If you re-send it, that'll make three times. [...]
21140 83 15_JDBC connection10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Thu, 12 Sep 2002 15:15:50 -0600329_iso-8859-1 Hi List, Our programmer has a JAVA program running on his workstation and want to access database on OS/390 DB2 V7 through JDBC. Is there anything that I need to setup on OS/390 DB2(for example: a port number for JDBC connection)? What kind of coding need to be done on the JAVA program to connect to OS/390 DB2? [...]
21224 180 29_Re: stored procedure question15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Thu, 12 Sep 2002 17:21:33 -0400577_iso-8859-1 we have around 500 stored procedure in production. All are in cobol and in a db2 version 6.1. I think, the development time reduced to 1/4th compared to RSP's we were writing to run it through direct connect. We do it in the following way
1. Developer create source and a control member with some statement for create ddl (Whatever specific to SP which programmer need to supply) in Chgman 2. When they promote Chgman package, it will create the definition of the stored procedure and copy the load module to the library and refresh the wlm. 3. Once the [...]
21405 50 39_Update with a Join getting -811 on AIX?15_Jackson Reavill18_damcon2@US.IBM.COM31_Thu, 12 Sep 2002 17:30:05 -0400587_us-ascii Hello everyone,
I'm trying to help out a colleague and am having trouble getting DB2 V7 for AIX to accept the following syntax (which works on OS/390 as of DB2 V6)...
Update tab1 set col1 = (select col2 from tab2 where tab2.col3 = tab1.col3) where col3 in (select col3 from tab2)
We get the following error... SQL0811N (-811) stating that more than one row is being returned to an into clause. I've verified that col3 on the subselect is unique so only one value can be returned for each occurrence of col3 on the updated table. However, it seems that [...]
21456 100 23_Resource Limit facility10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Thu, 12 Sep 2002 15:30:01 -0600547_iso-8859-1 Hi List, We want to limit the CPU usage for the user coming from DDF with specific userids and package name. However, two things really bother us: 1. We have many user id need to be restricted, but not all. Since this facility doesn't allow me to give a generic name, such as BS*, I have to create two entries for each user. If I have 200 users and I need 400 rows. 2. The package name seems keep changing. Sometime it was SQLLF000, sometime it changed to SQLLC000. We have have to create separate entry for different package name [...]
21557 125 53_Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Thu, 12 Sep 2002 17:49:41 -0400491_iso-8859-1 Vasu,
A workaround:
Why don't you create a summary table with auto refresh based on the base table, something like: CREATE SUMMARY TABLE ABC.MY_SUMMARYTABLE AS ( SELECT BASE_TABLE_COL1, BASE_TABLE_COL2, lower(item_name) as LOWER_ITEM_NAME, COUNT(*) as COUNT FROM ABC.BASE_TABLE GROUP BY BASE_TABLE_COL1, BASE_TABLE_COL2 ) DATA INITIALLY DEFERRED REFRESH IMMEDIATE ENABLE QUERY OPTIMIZATION NOT LOGGED INITIALLY IN INDEX IN ; [...]
21683 92 43_Re: Update with a Join getting -811 on AIX?16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM31_Thu, 12 Sep 2002 15:17:54 -0700403_us-ascii Jay,
I suspect what is happening is that you are getting more than one row returned from your correlated subquery which is not valid in the context of an update operation. You might want to try the following SQL as an alternate approach:
Update tab1 set col1 = (select col2 from tab2 where tab2.col3 = tab1.col3) where exists (select 1 from tab2 where tab2.col3 = tab1.col3) [...]
21776 141 30_Re: db2udp could not be loaded16_Swinski, Kenneth23_KSwinski@MASSMUTUAL.COM31_Thu, 12 Sep 2002 18:13:27 -0400406_us-ascii Thanks for your reply. I have an open PMR with IBM and have tried everything you suggested. No luck. Out of 21 UDB instances on this AIX box, we can create a stored procedure in only one instance. Is it possible that this instance has the Unix equivalent of a mainframe DISP=OLD on /$DB2HOME/sqllib/function/db2udp? I have tried the Unix fuser command on it, but nobody seems to be using it. [...]
21918 82 29_JDBC connection to OS/390 DB210_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Thu, 12 Sep 2002 16:13:50 -0600329_iso-8859-1 Hi List, Our programmer has a JAVA program running on his workstation and want to access database on OS/390 DB2 V7 through JDBC. Is there anything that I need to setup on OS/390 DB2(for example: a port number for JDBC connection)? What kind of coding need to be done on the JAVA program to connect to OS/390 DB2? [...]
22001 101 36_questions on resource limit facility10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Thu, 12 Sep 2002 16:15:18 -0600547_iso-8859-1 Hi List, We want to limit the CPU usage for the user coming from DDF with specific userids and package name. However, two things really bother us: 1. We have many user id need to be restricted, but not all. Since this facility doesn't allow me to give a generic name, such as BS*, I have to create two entries for each user. If I have 200 users and I need 400 rows. 2. The package name seems keep changing. Sometime it was SQLLF000, sometime it changed to SQLLC000. We have have to create separate entry for different package name [...]
22103 66 19_Re: JDBC connection14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 13 Sep 2002 08:11:10 +1000561_US-ASCII Grace, the first thing you will need is something that converts JDBC calls (ie, the stuff that the JAVA program is doing) into DRDA (ie stuff that DB2 understands). There are a variety of ways of doing this: - use DB2 Connect. JDBC calls go through DB2 CAE (Client Application Enabler) which passes the call to DB2 Connect, which translates it into DRDA and sends it off to the m/f. Two versions of DB2 Connect -- Personal Edition - it lives on the client workstation and can be used only by clients on that workstation. You can download this for [...]
22170 149 27_Re: Resource Limit facility13_LOMBARD,Shaun25_shaun.lombard@DEWR.GOV.AU31_Fri, 13 Sep 2002 08:17:03 +1000632_us-ascii Have you tried populating RLFCOLLN column with NULLID as the collection id ? -----Original Message----- From: Grace Chen [mailto:gchen@CUCENTRAL-AB.COM] Sent: Friday, September 13, 2002 7:30 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Resource Limit facility
Hi List, We want to limit the CPU usage for the user coming from DDF with specific userids and package name. However, two things really bother us: 1. We have many user id need to be restricted, but not all. Since this facility doesn't allow me to give a generic name, such as BS*, I have to create two entries for each user. If I have 200 users [...]
22320 137 41_Re: Deleting stats (on Peoplesoft tables)12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 12 Sep 2002 15:21:56 -0700561_us-ascii Gary- Thanks for the reply. I've heard there's a history with this, and every DB2 or Psoft upgrade re-introduces this problem, sometimes on different tables. I've only been looking into it for the last month or so. We're playing with using different types of runstats and dsstats to try to get this to work. Personally, I think there's a bug somewhere (and I think it's GOT to be the db2 optimizer) that causes an inefficient access path to be selected. So I guess I'll continue to chase it for a while until I find a solution, or finally give up [...]
22458 75 43_Re: Update with a Join getting -811 on AIX?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 13 Sep 2002 08:17:40 +1000446_US-ASCII The classic way of ensuring only one row is to use max Update tab1 set col1 = (select coalase(max(col2),0) from tab2 where tab2.col3 = tab1.col3) where col3 in (select col3 from tab2)
I've also used coalase because, on m/f, you can get problems if the the subselect returns a nullable column - which MAX is since it is possible (looking at the subselect only) for the subselect to find zero rows satisfying its where clause. [...]
22534 19 44_Glenn McDonald/OTT/TMG is out of the office.14_Glenn McDonald26_Glenn.McDonald@CLARICA.COM31_Thu, 12 Sep 2002 18:14:43 -0400373_us-ascii I will be out of the office starting 12/09/2002 and will not return until 02/10/2002.
I will respond to your message when I return. If you need to contact someone from Clarica DBDC please call ext. 7951 or Clarica MVS team please call 5500 . If you need to speak to someone from IBM Global Services DBDC please contact Fatima Khimji ( 416-496-4657 ) . [...]
22554 152 27_Re: Resource Limit facility13_LOMBARD,Shaun25_shaun.lombard@DEWR.GOV.AU31_Fri, 13 Sep 2002 00:51:00 +0200632_us-ascii Have you tried populating RLFCOLLN column with NULLID as the collection id ? -----Original Message----- From: Grace Chen [mailto:gchen@CUCENTRAL-AB.COM] Sent: Friday, September 13, 2002 7:30 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Resource Limit facility
Hi List, We want to limit the CPU usage for the user coming from DDF with specific userids and package name. However, two things really bother us: 1. We have many user id need to be restricted, but not all. Since this facility doesn't allow me to give a generic name, such as BS*, I have to create two entries for each user. If I have 200 users [...]
22707 52 37_Re: RTFM & supercilious BP parameters16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Thu, 12 Sep 2002 21:03:21 -0400405_us-ascii You have to live it (DB2) on a daily basis 24 x 8 to understand. These seeming lapses into obscurity are the only way to stay (in)sane. ;->
Dennis H Werling To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: RTFM & supercilious BP parameters
09/12/02 03:02 PM Please respond to DB2 Data Base Discussion List [...]
22760 22 33_Kevin Davis is out of the office.11_Kevin Davis18_kevin.davis@DB.COM30_Mon, 9 Sep 2002 02:15:54 -0600139_us-ascii I will be out of the office from 09/09/2002 until 09/16/2002.
I will respond to your message when I return.
22783 93 41_Re: Please comment on a recovery scenario17_Douglas Hawthorne33_douglas.hawthorne@CARDLINK.COM.AU31_Fri, 13 Sep 2002 11:42:32 +1000478_iso-8859-1 To Tina, Mark, and John,
Thank you for taking the time to answer my query. I found your replies to be informative and concise.
(1) We do not have FDRSNAP, FLASHCOPY, or anything similar.
(2) I did try LOG SUSPEND/RESUME with a FDR back up in between. My management was not happy with the eight (8) minute outage this caused every day. Our developers did not code 'FOR FETCH ONLY' on all enquiry cursors and this locked out almost all users. [...]
22877 72 27_Problem with DB2 connection21_=?gb2312?B?wubOxL+h?=16_wenjunl@21CN.COM31_Fri, 13 Sep 2002 09:49:41 +0800579_gb2312 Hello all, A sales branch of my company should access a db2 database located in the company's headquarter, which is running on the AIX platform. At first,we use 512K DDN to connect, but the bandwidth seems not enough, so we use 2M tranport circuit to replace the DDN , its QOS is not as good as DDN,but it's less expensive.Most strangely,when the communation switches from DDN to the circuit, the client in the sales branch can not connect to the DB2 database,but at the same time, we can ping the server,and we can telnet the server,even the oracle connection works [...]
22950 96 41_Re: Please comment on a recovery scenario17_Douglas Hawthorne33_douglas.hawthorne@CARDLINK.COM.AU31_Fri, 13 Sep 2002 03:51:50 +0200478_iso-8859-1 To Tina, Mark, and John,
Thank you for taking the time to answer my query. I found your replies to be informative and concise.
(1) We do not have FDRSNAP, FLASHCOPY, or anything similar.
(2) I did try LOG SUSPEND/RESUME with a FDR back up in between. My management was not happy with the eight (8) minute outage this caused every day. Our developers did not code 'FOR FETCH ONLY' on all enquiry cursors and this locked out almost all users. [...]
23047 75 27_Problem with DB2 connection21_=?gb2312?B?wubOxL+h?=16_wenjunl@21CN.COM31_Fri, 13 Sep 2002 04:11:52 +0200579_gb2312 Hello all, A sales branch of my company should access a db2 database located in the company's headquarter, which is running on the AIX platform. At first,we use 512K DDN to connect, but the bandwidth seems not enough, so we use 2M tranport circuit to replace the DDN , its QOS is not as good as DDN,but it's less expensive.Most strangely,when the communation switches from DDN to the circuit, the client in the sales branch can not connect to the DB2 database,but at the same time, we can ping the server,and we can telnet the server,even the oracle connection works [...]
23123 72 22_Re: Reverse index scan14_Larry Kintisch19_lkint@IX.NETCOM.COM31_Thu, 12 Sep 2002 23:21:26 -0400573_us-ascii Dan, You didn't mention if the index on NL_QUARTREPT_ID is unique or not. If not unique, why not fatten the index a bit and reverse it: (NL_QUARTREPT_ID DESC, VALID)? That should provide a faster index-only scan (MC=0) in your 99.9% scenario. But will VALID be updated? That would mean at least two I/O's to my suggested index design that will impact the UPDATE process. The IBM course CF960 teaches a lot about index design and access paths. Larry Kintisch At 11:45 AM 9/11/02 -0500, you wrote: >Hi all, >Can DB2 for os/390 v7 use an index in revers order ? [...]
23196 133 43_Re: Update with a Join getting -811 on AIX?15_Janardhan Ragam16_sragam@YAHOO.COM31_Thu, 12 Sep 2002 20:58:52 -0700265_us-ascii If the suggestions provided by others do not work - one more thing that you might want to check is if there are any update triggers defined on table and see if one of the triggers are failing with the -811 error - I have had to deal with such issues. [...]
23330 50 71_Selecting unnecessary columns / updating columns that have not changed.16_Peter Lehmensich27_peter.lehmensich@HIC.GOV.AU31_Fri, 13 Sep 2002 14:21:09 +1000453_us-ascii Hi All.
My environment : JAVA WEBSPHERE on AIX accessing DB2 V6.1 on OS390.
We are considering a design proposal that always retrieves all columns and always updates all columns. The approach is supported by the standard OO best practice argument and a simple stopwatch test from the environment mentioned above that suggested no significant elapsed time difference between a singleton selecting 1 or 10 non-indexed columns. [...]
23381 112 54_IDUG North America 2002 - Las Vegas, Nv 19-23 May 200312_Phil Gunning20_pgunning@COMCAST.NET31_Thu, 12 Sep 2002 21:28:46 -0700402_iso-8859-1 Dear DB2 Professional,
Please, give me your undivided attention:)! The deadline for abstract submissions is fast approaching. You have just 4 days to get an abstract submitted. We are still looking for abstracts of all types. Remeber, if your presentation abstract gets selected, you get free seminar attendance. An almost $1700 value. And, we have a great location this year:). [...]
23494 115 54_IDUG North America 2002 - Las Vegas, Nv 19-23 May 200312_Phil Gunning20_pgunning@COMCAST.NET31_Fri, 13 Sep 2002 06:46:56 +0200402_iso-8859-1 Dear DB2 Professional,
Please, give me your undivided attention:)! The deadline for abstract submissions is fast approaching. You have just 4 days to get an abstract submitted. We are still looking for abstracts of all types. Remeber, if your presentation abstract gets selected, you get free seminar attendance. An almost $1700 value. And, we have a great location this year:). [...]
23610 15 49_Martha Kijak/HRD/Prudential is out of the office.12_Martha Kijak27_martha.kijak@PRUDENTIAL.COM31_Fri, 13 Sep 2002 01:04:20 -0400403_us-ascii I will be out of the office starting 09/13/2002 and will not return until 09/17/2002.
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://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
23626 124 80_Antwort: Selecting unnecessary columns / updating columns that have not changed.12_Rodney Krick30_rk@HAENCHEN.SOFTWAREZENTRUM.DE31_Fri, 13 Sep 2002 08:51:47 +0200578_iso-8859-1 Peter,
give a look at this article from James Campbell:
http://www7b.software.ibm.com/dmdd/library/techarticle/0202campbell/0202campbell.html
HTH,
Mit freundlichen Grüßen / Best regards,
Rodney Krick Hänchen & Partner GmbH Calwer Straße 1 D-71034 Böblingen
Peter Lehmensich Kopie: Gesendet von: DB2 Thema: Selecting unnecessary columns / updating columns that have not Data Base changed. Discussion List [...]
23751 43 20_Question on Package.35_Umapathy, Gopalakrishna (Cognizant)26_UGopalak@CHN.COGNIZANT.COM31_Fri, 13 Sep 2002 13:06:50 +0530219_iso-8859-1 Hi Db2'ians,
Does DB2 keep track of the USER-ID which issued the FREE PACKAGE command of the package?.
__________________________________________________ Regards, Gopalakrishnan Umapathy
23795 41 39_DB2 6.1 UDB for Windows Question REPOST15_John C. Lendman20_John_Lendman@FPL.COM31_Fri, 13 Sep 2002 07:46:45 -0400374_us-ascii Not sure if this made it the first time around, sorry if it did.
I am having problems connecting to one of my database on a different workstation. When ever I try to connect using Command Center or Control Center I get the following error. This use to work, but stopped working about a month ago. I have asked the PC experts here and they have no idea. [...]
23837 221 75_Re: Selecting unnecessary columns / updating columns that have not changed.14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 13 Sep 2002 12:59:45 +0100261_iso-8859-1 Suggest the person with the stopwatch goes to the local supermarket and tries to get 10 items off the shelf in the same time he can get 1 - even if the 10 items are on the same shelf (page) it will STILL take longer than just taking the 1 item. [...]
24059 52 46_Connect for Linux accessing DB2 (OS/390 7.1.1)16_Blumenthal, Gary21_GBlument@UNCH.UNC.EDU31_Fri, 13 Sep 2002 08:03:30 -0400416_iso-8859-1 I have been trying to get DB2 Connect for Linux (V7.2) to connect to DB2 (OS/390 7.1.1). I am getting the following error which looks like a permissions error:
SQL30082N Attempt to establish connection failed with security reason "17" ("UNSUPPORTED FUNCTION"). SQLSTATE=08001
This is what I was able to find out regarding this error (could it be an encryption or DCE problem?): [...]
24112 75 50_Re: Connect for Linux accessing DB2 (OS/390 7.1.1)13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Fri, 13 Sep 2002 07:23:15 -0500370_iso-8859-1 I did get a similar error once, but cannot recall if it is exactly the same. Anyway, in my case, I think I had 2 errors over time. 1. I had not selected DCS in the "DB2 Instance Properties" screen and/or 2. In the "catalog database xx as xx at node dbp1node authentication dcs" - I did not specify the "authentication dcs " clause and it used a default. [...]
24188 123 48_Antwort: DB2 6.1 UDB for Windows Question REPOST12_Rodney Krick30_rk@HAENCHEN.SOFTWAREZENTRUM.DE31_Fri, 13 Sep 2002 14:25:28 +0200426_iso-8859-1 Hi John,
Check if somebody applied a fixpack on the server (upgrade). You can compare the date of your DB2-driver (db2java.zip) with the date of the Java-process on the server (db2jstrt.exe). If they don't match and your file is older, you will become this error. I've tried here (one server running DB2 UDB V7.2.5 and another running V7.1.0; 7.2.5 -> 7.1.0 works; 7.1.0 -> 7.2.5 get the same message). [...]
24312 21 55_Re: Administration Server Headaches resolved (V7.2 EEE)12_David Harvey17_dmh@ONETEL.NET.UK31_Fri, 13 Sep 2002 07:38:23 -0500399_- Steve,
What a wonderful example of LISTSERV. Apart from EE, our situation was identical and the IBM documentation was 'unhelpful'. The "523" default was also the root of our problem.
In order for the Control Centre to monitor, we could point it at several d/b's but in the case of Tivoli (AIX-based) we always got the SQL4414N error which we now know was a total waste of time. [...]
24334 16 13_Sysplex Timer18_Di Franco Vincenzo22_difrancov@ISIDE.BCC.IT31_Fri, 13 Sep 2002 14:31:09 +0200504_iso-8859-1 We run DB2 V5 for OS/390, our hardware clock is set with local time; we are about to remove a sysplex timer an will set our hardware clock to GMT. Someone remember if I have to stop DB2 during this transition ? Thank you in advance. Bye.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
24351 180 52_Re: Antwort: DB2 6.1 UDB for Windows Question REPOST15_John C. Lendman20_John_Lendman@FPL.COM31_Fri, 13 Sep 2002 08:47:31 -0400318_iso-8859-1 Rodney, I believe there was a fix pack put on the workstation that I am having the problem with.
So what is the solution, I have to have the same fix pack, or close to it as you note says, on all the different databases John C. Lendman
DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413 [...]
24532 27 41_Re: Deleting stats (on Peoplesoft tables)12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Fri, 13 Sep 2002 08:58:50 -0400368_us-ascii We are backing off the DB2 maint i cut over last saturday due to this problem. I would suggest maybe looking at PQ49205/UQ56413 as it relates to problems with stats in SYSCOLDIST. We deleted rows out of these tables and ran into many more long running Peoplesoft processes. Additional questions: Did shops having this problem delete all rows out of SYSCOLDIST
24560 127 75_Re: Selecting unnecessary columns / updating columns that have not changed.16_Peter Lehmensich23_brumbies@BIGPOND.NET.AU31_Fri, 13 Sep 2002 23:07:16 +1000356_iso-8859-1 RE: [DB2-L] Selecting unnecessary columns / updating columns that have not changed.Phil.
I'll play devil's advocate here. Does it matter if it takes longer to get 10
items off the shelf, it might be insignificant compared to actually getting
to the local supermarket and back. I guess you could argue that my carry bag [...]
24688 36 24_Re: Question on Package.11_David Nance16_DWNance@FHSC.COM31_Fri, 13 Sep 2002 09:15:40 -0400773_US-ASCII It does, in the log.
Dave Nance First Health Services, Corp. (804)527-6841
>>> UGopalak@CHN.COGNIZANT.COM 09/13/02 03:36AM >>> Hi Db2'ians,
Does DB2 keep track of the USER-ID which issued the FREE PACKAGE command of the package?.
__________________________________________________ Regards, Gopalakrishnan Umapathy This message, including any attachments,is intended solely for the use of the named recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution of this communications is expressly prohibited. If you are not the intended recipient,please contact the sender by reply e-mail and destroy any and all copies of the original message. Thank you. [...]
24725 45 55_Re: Administration Server Headaches resolved (V7.2 EEE)14_Steve Westfall26_Steve.Westfall@EQUIFAX.COM31_Fri, 13 Sep 2002 08:18:16 -0500642_us-ascii David,
Glad my posting about the Port 523 problem was helpful to you. That's what DB2-L is all about.
Steve Westfall Equifax, Inc.
David Harvey cc: David Harvey Subject: Re: Administration Server Headaches resolved (V7.2 EEE) 09/13/2002 07:38 AM
Steve,
What a wonderful example of LISTSERV. Apart from EE, our situation was identical and the IBM documentation was 'unhelpful'. The "523" default was also the root of our problem. [...]
24771 166 43_Re: Update with a Join getting -811 on AIX?0_22_BRIAN_GOLDBERG@QVC.COM31_Fri, 13 Sep 2002 09:31:25 -0400454_us-ascii Is col3 unique in tab2? If not, you'll get the equivalent of an outer join from the subquery.
Brian
Janardhan Ragam cc: (bcc: BRIAN GOLDBERG/QVC) Ext: NA Subject: Re: Update with a Join getting -811 on AIX? Sent by: DB2 Data Base Discussion List
09/12/2002 11:58 PM Please respond to DB2 Data Base Discussion List [...]
24938 167 76_Re: Selecting unnecessary columns / updating col umns that have not changed.14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 13 Sep 2002 14:53:19 +0100368_iso-8859-1 Unless people have time (or MIPS) to burn, I always work on the assumption that you NEVER do ANYTHING that you don't have to (in fact, that spills over into my personal life too!!!)
That's MY best practice!
PS This world is getting REAL small - I flew back from Greece last week and on the flight was a guy in a Brumbies CA rugby shirt!! [...]
25106 60 53_Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX13_Thomas, Janis19_Janis.Thomas@CA.COM31_Fri, 13 Sep 2002 10:03:55 -0400687_iso-8859-1 I removed db2.admin@juliasbaer.com from the list, I did not see mathius.weinmann@juliasbaer.com subscribed. Hopefully this will clear up the problem.
Janis Thomas (one of the list owners)
-----Original Message----- From: David S Waugh [mailto:dsw-dba@JUNO.COM] Sent: Thursday, September 12, 2002 4:12 PM Subject: Re: Indexes and Standard Functions: DB2 UDB Win2k/AIX
EVERYONE who posts to DB2-L has been getting these "duplicate posting" messages. Evidently, one of our subscribers (db2.admin@juliasbaer.com or mathius.weinmann@juliasbaer.com) is somehow causing each of our posts to be bounced back to DB2-L, which tries to re-process them. [...]
25167 59 38_DB2 6.1 UDB for Windows Question - ???12_Rodney Krick30_rk@HAENCHEN.SOFTWAREZENTRUM.DE31_Fri, 13 Sep 2002 16:09:13 +0200709_iso-8859-1 John,
give a look at
http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/download.d2w/report
and check the file FixpackReadme.txt for the fixpack you've installed.
This one is for DB2 UDB V7 Fixpack 7:
2.7.1 Loss of Control Center Functionality
There should be no problems introduced against downlevel Control Center clients by applying FixPak 2 or FixPak 3 to a DB2 server. However, in DB2 Version 7.2, downlevel Control Center clients will lose nearly all functionality. Downlevel in this case refers to any Version 6 client prior to FixPak 6, and any Version 7 client prior to FixPak 2. Version 5 clients are not affected. [...]
25227 38 83_Re: Execute a cobol Micro Focus stored procedure from the command line. DB2 EEE AIX11_Daniel Adam16_dadam@GRATEX.COM31_Fri, 13 Sep 2002 09:07:46 -0500688_- Hi, try this: db2 "call .J2LGS ('006587222',?,?,?)" Daniel
On Thu, 12 Sep 2002 11:35:39 -0400, Mike Fatula wrote:
>Can anyone give me the syntax to perform this. > >example > >db2 "CALL .J2LGS ('006587222',' ',' ',' ')" > > >there is one input field and 3 output fields > > >I keep getting the error > >DB21101E Too few command line parameters specified for the stored >procedure. > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. [...]
25266 14 75_Re: Selecting unnecessary columns / updating columns that have not changed.7_DB2 DBA21_db2_dba@BELLSOUTH.NET31_Fri, 13 Sep 2002 10:29:05 -0400492_ISO-8859-1 This type of design is usually implimented because it is felt that called programs can be used and eliminate the need for all developers needing DB2 expertise. To test the falacy of this theory find a table with a large number of rows (500,000 is a good guess because how large is large) and running a batch SPUFI first selecting one column, then selecting 5 columns, then selecting 10 columns, etc, etc. Now compare the numbers from a monitor. You should see the difference. [...]
25281 63 40_Re: Faroffposf & Nearoffposf after reorg9_Chad, Rao17_RChad@STATE.NM.US31_Fri, 13 Sep 2002 08:29:48 -0600676_- The only way you can change by doing REORG on Table space with an option INDEx(All).
-----Original Message----- From: Dale E. Miller [mailto:dmiller@UGI.COM] Sent: Saturday, September 07, 2002 10:39 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Faroffposf & Nearoffposf after reorg
Listers, I have a question on index Reorgs. This is DB2 for OS/390 V6. I run an index reorg and Runstats immediately afterwards. When I check the catalog stats, the LEAFDIST has gone to zero. This I expected. However, the NEAROFFPOSF and FAROFFPOSF values are almost identical both before and after the reorg. I then tried various combinations of PCTFREE and FREEPAGE [...]
25345 48 22_FOR UPDATE OF question10_Mark Labby21_mlabby@AESSUCCESS.ORG31_Fri, 13 Sep 2002 10:34:33 -0400579_us-ascii We are running DB2v6 on z/OS 1.1.
We have an application programmer who has coded a subroutine that is used to assign account numbers to new customers. The next available account number is stored in the account number table with a specific key - it has the ClientID and "MAXSEQNUM" in the column where the SSN would normally be. The application reads this row in a cursor which includes the WITH UPDATE OF clause so that no other thread can be reading the row and assigning the same account number. The module then increments the stored account number by 1, [...]
25394 47 20_Question on Package.35_Umapathy, Gopalakrishna (Cognizant)26_UGopalak@CHN.COGNIZANT.COM31_Fri, 13 Sep 2002 13:06:50 +0530219_iso-8859-1 Hi Db2'ians,
Does DB2 keep track of the USER-ID which issued the FREE PACKAGE command of the package?.
__________________________________________________ Regards, Gopalakrishnan Umapathy
25442 149 84_Re: Antwort: Selecting unnecessary columns / updating columns that have not changed.12_Rohn Solecki19_Rohn.Solecki@MTS.CA31_Fri, 13 Sep 2002 09:44:12 -0500422_iso-8859-1 Also check the archives, this question has been asked and answered in the past. Here is a summary of one of the discussions:
We've been running into unexpectedly long elapsed times and high CPU times. We've identified one of the issues as the number of columns selected (we know it's good to select only the columns you need but are surprised at the amount of elapsed time and CPU time this takes). [...]
25592 89 33_Re: Index Rebuild, SYSUT1 on tape0_19_csutfin@AMSOUTH.COM31_Fri, 13 Sep 2002 09:50:44 -0500449_us-ascii Mark, I haven't seen anyone send a sample, so here goes:
//SYSUT1 DD DSN=TANV.BPMA.DDWORK.LDBPEJ3.SYSUT1, // DISP=(MOD,DELETE,CATLG), // UNIT=TAPE,VOL=(,,,50) //*
This will also allow you to use more than 3 tape volumes (or the default set by your system programmer). This will go to 50 volumes.
Good luck.
Carol Sutfin Corporate DBA AmSouth Bank (205)326-5214 Fax:(205)326-5613 csutfin@amsouth.com [...]
25682 125 42_Re: DB2 6.1 UDB for Windows Question - ???15_John C. Lendman20_John_Lendman@FPL.COM31_Fri, 13 Sep 2002 10:52:41 -0400276_iso-8859-1 Thanks for your reply. I did try to install the fix pack on my machine. It seemed to go on OK, but now I am having trouble with the control center and I can not get to any of the database on my list.
Guess I will call support.
Thanks for trying. [...]
25808 104 19_Re: JDBC connection10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Fri, 13 Sep 2002 09:00:25 -0600318_- Hi James, I am still confused with this JDBC connection. It looks like DB2 for OS/390 V7 support JDBC connection since it support JDBC application. May be I am wrong. Does DB2 Connect include DB2 CAE or this is a separate product? Any setting on DB2 CAE or on JAVA program to make the JDBC call go through CAE? [...]
25913 23 54_Identity Columns: are they Good, Bad or... just Ugly ?10_Jose Moura25_jose.antonio.moura@CGD.PT31_Fri, 13 Sep 2002 16:04:02 +0100478_- What is your experience with Identity Columns ? Have you had many problems? Do you use it first in a sequencing table and then in the tables you need it ?
Thanks in advance,
Jose Moura
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. [...]
25937 79 23_Re: Bufferpool Analyzer13_McKenny, John20_John_McKenny@BMC.COM31_Fri, 13 Sep 2002 10:13:39 -0500370_iso-8859-1 Mark,
Let me suggest that you consider evalating BMC's Pool Advisor for DB2 product.
Pool Advisor for DB2 is the only product that can help you achieve optimum use of your DB2 storage 24 hours a day, 365 days a year by dynamically modifying storage allocations and settings as your storage usage and requirements change throughout the day. [...]
26017 67 31_Re: Help! Trying to free a plan13_Helen Johnson25_helen_johnson@RAC.RAY.COM31_Fri, 13 Sep 2002 10:26:40 -0500340_iso-8859-1 Dan,
Did you try putting HEX ON to see the hex value of these two characters? I know you can set the character type to hex in QMF or in a SPUFI output you could put HEX ON. Then when you code your batch job to free the plan, turn HEX ON again to enter these two characters before submitting the job. Hope this helps. [...]
26085 78 58_Re: Identity Columns: are they Good, Bad or... just Ugly ?13_Rick N Molera22_rick.n.molera@AEXP.COM31_Fri, 13 Sep 2002 09:10:22 -0700446_us-ascii Jose,
If you are attempting to utilize an identity column to establish a system generated unique key, I would consider the following technique, instead:
1. Create candidate key column as DEC(15) NOT NULL & supporting unique index
2. Code the program to issue this SQL statement, to generate a random key value:
SELECT DECIMAL(RAND() * 999999999999999 ,15,0) INTO :HOSTVAR1 FROM SYSIBM.SYSDUMMY1 WITH UR; [...]
26164 44 26_Restart Image copy utility11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Fri, 13 Sep 2002 09:13:59 -0700522_US-ASCII First, let me thank all of you who are so patient with a newbie and answer my questions. I have researched the manuals and I think I know what I should be doing verus what I have been doing.
The scenario is that an image copy job abends with a -911 because the operator ran it as the same time as an update process which is against how we requested it to be scheduled. It is supposed to be run first then the other jobs. We do a full image copy. Unfortunately, restarting a utility was not one of the [...]
26209 91 26_Re: FOR UPDATE OF question11_David Nance16_DWNance@FHSC.COM31_Fri, 13 Sep 2002 12:24:37 -0400311_US-ASCII We do the same type operation with a little difference, we update the row first, select the value into host variable, then commit. I would think the process you describe would work just fine, though. What about using page for the locks and force the different client rows to be on separate pages? [...]
26301 130 49_Re: Identity Columns: Good, Bad or... just Ugly ?13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM31_Fri, 13 Sep 2002 12:32:23 -0400444_US-ASCII I vote for UGLY.
There are few 'good' reasons for identity columns; most good logical systems' designs have no use for them. The only place I've seen them used to good effect were for denormalization or performance reasons.
My past experiences with Identity Columns, GUIDs, and other such system-generated surrogate keys is that they can sometimes provide benefits . . . but *only* if all stakeholders are involved. [...]
26432 72 30_Re: Restart Image copy utility17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Fri, 13 Sep 2002 18:57:20 +0200583_us-ascii Hi Kathy,
it looks like your analysis wasn't quite correct (well, wrong). -911 is an SQLCODE. It is not issued by a Utility (they abend instead). Also, if the IC (and its dataset) was recorded in SYSCOPY, it was successful. So it looks like the IC job blocked the Update program (rather than vice versa), which then failed with -911, which can happen if you employ a IC strategy that blocks updaters, like SHRLEVEL REF. The problem that the scheduler incorrectly adjusts the GDG number when doing a restart seems to be common. We've had the same problem once we [...]
26505 48 19_Tablespace creation29_RAGHAVAN Vadakkupattu Sampath21_VRaghava@COVANSYS.COM31_Fri, 13 Sep 2002 13:29:43 -0400487_- Hi everyone,
I am getting the following error while creating the tablespace.
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN
UNAVAILABLE RESOURCE. REASON 00D70025, TYPE OF RESOURCE 00000220, AND RESOURCE NAME DSN710.DSNDBC.V049D001.TS000001.I0001.A001
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXICTS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 105 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION [...]
26554 154 19_Re: JDBC connection12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 13 Sep 2002 10:43:09 -0700593_us-ascii Grace, Maybe I can explain it a little differently.
OS/390 DB2 supports JDBC, ODBC, and other types of remote connections thru the facility DDF. The type of access depends upon the requesting machine.
On your PC/server, in order to use JDBC access to any DBMS, you must have a JDBC driver, usually a "JAVA JDBC Type 2 or 4". Several companies provide these drivers. Type 2 drivers are independent of the JAVA code itself. Type 4 are included in the Java Code. But both must be purchased from somewhere. Merant, Neon, IBM as well as several others all have drivers. [...]
26709 51 23_Re: Tablespace creation17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Fri, 13 Sep 2002 19:51:42 +0200430_us-ascii Most likely you don't have enough space. Check your console log (SDSF.LOG) for DSNP009I and DSNP010I messages and IDCAMS messages. If that is the problem, you can try reducing the PRIQTY for your tablespace.
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany
Hi everyone,
I am getting the following error while creating the tablespace. [...]
26761 119 30_Re: Restart Image copy utility11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Fri, 13 Sep 2002 11:03:16 -0700579_US-ASCII Well, I ahve not converted the reorgs to use inline copy yet as I just recently took over and we recently went to version 5 just before the previous DBA retired so we are still doing things his way. It was the image copy job that abended. I called it a -911 because the message I got was resource unavailable see below. DSNUGUTC - COPY TABLESPACE M6525DB1.M6525TSB COPYDDN SYSCPY02 DSNUM ALL FULL DSNUGBAC - RESOURCE UNAVAILABLE REASON 00C200EA TYPE 00000200 NAME M6525DB1.M6525TSB DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E4010A' [...]
26881 101 23_Re: Tablespace creation13_Rick N Molera22_rick.n.molera@AEXP.COM31_Fri, 13 Sep 2002 11:03:01 -0700413_us-ascii Raghavan,
You are getting an IDCAMS VSAM dataset allocation error. Check the MVS LOG or the DB2 MSTR started task for the actual IDCAMS error messages (it is probably a DASD space - lack of - issue).
From the DB2 Messages and Codes manual:
00D70025
Explanation: An access method services request failed.
This reason code is issued by the following CSECT: DSNPAMSI [...]
26983 77 30_Re: Restart Image copy utility13_David S Waugh16_dsw-dba@JUNO.COM29_Fri, 13 Sep 2002 18:26:19 GMT408_- Hi Kathy:
This is a really old-fashioned way of doing things, but I've found it works pretty good. Here's an excerpt of how I've set up my maintenance jobs for restart:
//DB2P190W JOB (DB2P190W),'DB2P IMAGE COPY', //***** RESTART=STEP050, <=== TO RESTART THE IMAGE COPY STEP 1 //***** RESTART=STEP055, <=== TO RESTART THE IMAGE COPY STEP 2 // REGION=0M,MSGLEVEL=(1,1),MSGCLASS=X,CLASS=P [...]
27061 62 30_Re: Restart Image copy utility17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Fri, 13 Sep 2002 20:52:13 +0200601_us-ascii OK: the 00C200EA means that the Utility failed because it could not drain writers. In that case there shouldn't be an entry in SYSCOPY for the IC dataset - so there's something wrong (a quick check didn't reveal any known problems in the APAR DB). Check SYSCOPY for the timestamp where the dataset was created: SELECT DBNAME, TSNAME, DSNUM FROM SYSIBM.SYSCOPY WHERE DSNAME LIKE '{GDG-Name w/o GooVoooo suffix}%'. Compare this with the time when your job failed. Also look at the JESYSMSG for the exact Generation number that JES allocated when the job failed, and that was used when you [...]
27124 145 30_Re: Restart Image copy utility11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Fri, 13 Sep 2002 12:56:34 -0700333_US-ASCII David, thank you for such good advice. I will ask our jobtrac guru about the GDGs but this sounds like a good approach for a small, old-fashioned shop. I will try it out.
Kathy Jones Central Information Services Clark County School District O/S390 DB2 DBA NT DB2 DBA 702-799-5040 x366 jonesks@GroupWise.ccsd.net [...]
27270 80 42_Re: DB2 6.1 UDB for Windows Question - ???15_Russell Collins26_RCollins@ZCOREBUSINESS.COM31_Fri, 13 Sep 2002 15:53:02 -0500
27351 18 20_Which SQL is better?16_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU31_Fri, 13 Sep 2002 13:42:00 -0700381_- DB2 for OS390 V6. 2 queries have same EXPLAIN info - ACESSTYPE I with MATCHCOLS = 1, not indexonly. The index has COL1, COL2, COL3 in order.
Is there any performance difference between Q1 and Q2?
Q1 - Select * from T1 where COL1 >= ? and COL1 concat COL2 concat COL3 between ? and ? ; Q2 - Select * from T1 where COL1 concat COL2 concat COL3 between ? and ? ; [...]
27370 80 30_Re: Restart Image copy utility11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Fri, 13 Sep 2002 13:45:34 -0700442_US-ASCII That pointed out something I missed. The restart bombed because the dataset from the first tablespace was cataloged - we copy 3 tablespaces in this job stacking them on tape. So although jobtrac removed the GDG, DB2 had the tape for the first one in syscopy. So did I need to remove the first tablespace from the image copy before the run? but that wouldn't get all 3 stacked on one tape so for that gdg I would have two tapes? [...]
27451 209 19_Re: JDBC connection10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Fri, 13 Sep 2002 14:55:49 -0600395_- Myron, Thank for your explanation. We have DB2 connect personal edition installed on my and our developer's workstation. We all be able to connect to the OS/390 DB2 with DB2 connect. I see Control center, command center, client configuration assistant. I can't find CAE and Store procedure builder. Is there is a way that I can verify if I have CAE and Store procedure builder installed? [...]
27661 22 31_sysplex parallelism explotation10_Tom Taylor17_ttaylor@CHUBB.COM31_Fri, 13 Sep 2002 17:14:13 -0400528_us-ascii Hi all
I recently ran some batch tests exploiting sysplex parallelism.... my results seem to indicate there is not that big of a difference.
Is anyone exploiting it?
What are your CPU / ELAP times VS. NO sysplex parallelism
Tom
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
27684 216 27_Re: Resource Limit facility10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Fri, 13 Sep 2002 15:34:49 -0600444_- Shaun, I didn't populate RLFCOLLN with NULLID. Now there is nothing there. we only want to restrict certain query which is coming from Data Warehouse Manager on PC. All other access from DDF shouldn't be restricted.
Thanks! Grace
-----Original Message----- From: LOMBARD,Shaun [mailto:shaun.lombard@DEWR.GOV.AU] Sent: Thursday, September 12, 2002 4:17 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Resource Limit facility [...]
27901 90 30_Re: Restart Image copy utility17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Fri, 13 Sep 2002 23:30:00 +0200555_us-ascii Hi Kathy,
It seems you've got about the worst possible setup for taking ICs, at least when something goes wrong. Here I make my life simple: only one IC per job step, catalogued datasets on disk for TSs below a certain size, catalogued datasets on tape (only one DS per tape) for the biggies. We had some old jobs that stacked ICs on tape (each with its own name, and catalogued); they were a major hassle to restart, so I changed them when I had the chance. Here it's also the case that if you specify DISP=(,CATLG,CATLG) for a disk [...]
27992 54 29_DB2 V7 Dataspaces Question...70_Charles_Valentin15_VALENCH@AOL.COM29_Fri, 13 Sep 2002 18:07:20 EDT535_US-ASCII
We're running DB2 V7 w/z/OS & 64-bit on our Production environments. Can anyone share any User experiences using DB2 Bufferpool in Dataspaces??? We would like to "relieve" the "below-the-line" DBM1 storage constraint by moving DB2 Bufferpools to Dataspaces & have the following concerns:
1) Are they "stable" (if that's possible) in V7 (we're at RSU 0203 + Hipers)? 2) What are some of the known issues/problems with IBM, BMC, CDB Utilities (such as Online Reorgs) and using DB2 Bufferpools in Dataspaces? [...]
28047 61 24_Re: Which SQL is better?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 13 Sep 2002 17:09:59 -0500494_iso-8859-1 John,
You may want to doublecheck the explain output for the second query. It's not possible to get a matchcols = 1 since the predicate is stage 2 (due to the concatenation).
So from your examples, Q1 should get better performance. But you really should to avoid the concatenation.
So something like this will work if COL1, COL2, COL3 are fixed length strings and you break up the low and high range values of the BETWEEN into 3 parts to match the columns. [...]
28109 25 27_db2 os/390 v7 book link????11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Fri, 13 Sep 2002 15:36:43 -0700303_iso-8859-1 Hi all, So far I've found, Big Foot, Jimmy Hoffa and the Lost Dutchman's mine but my search skills have been thwarted by the IBM web. Does any one have the link to the v7 books in general and the Application Programming and SQL guide in specific.
Thanks in advance for your help. [...]
28135 41 31_Re: db2 os/390 v7 book link????10_Shery Hepp17_schepp@SRPNET.COM31_Fri, 13 Sep 2002 15:53:13 -0700603_iso-8859-1 Hey Cliff- here you go-
http://publib.boulder.ibm.com/cgi-bin/bookmgr/Shelves/DSNSHHA2?searchRequest =administration+guide
-----Original Message----- From: Cliff Boley [mailto:Maurice.C.BOLEY@ODOT.STATE.OR.US] Sent: Friday, September 13, 2002 3:37 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: db2 os/390 v7 book link????
Hi all, So far I've found, Big Foot, Jimmy Hoffa and the Lost Dutchman's mine but my search skills have been thwarted by the IBM web. Does any one have the link to the v7 books in general and the Application Programming and SQL guide in specific. [...]
28177 57 31_Re: db2 os/390 v7 book link????13_Rick N Molera22_rick.n.molera@AEXP.COM31_Fri, 13 Sep 2002 16:12:32 -0700824_us-ascii Cliff,
This is the library link that has served me best:
http://www-3.ibm.com/software/data/db2/os390/library.html
Good luck, Rick Molera
From: "Cliff Boley" @LISTSERV.YLASSOC.COM> on 09/13/2002 03:36 PM
Please respond to "DB2 Data Base Discussion List"
Sent by: "DB2 Data Base Discussion List"
To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: db2 os/390 v7 book link????
Hi all, So far I've found, Big Foot, Jimmy Hoffa and the Lost Dutchman's mine but my search skills have been thwarted by the IBM web. Does any one have the link to the v7 books in general and the Application Programming and SQL guide in specific. [...]
28235 73 28_Table partitioning question?19_prasad rao Mocharla19_mocharlap@YAHOO.COM31_Fri, 13 Sep 2002 20:20:30 -0700710_us-ascii
Hello friends,
1) A table has cardinality of 95,000,000 and ten parts. This table has two indexes non both of them allow duplicates. One of them is NPI and other is partitioned index. Other is partitioned. The partitoned index has four column combined key.
CREATE INDEX XXX.XINDEX ON XXX.TBLE1 (COL1 ASC, -- CHAR(9) COL2 ASC, -- CHAR(4) COL3 ASC, -- CHAR(7) COL4 DESC) -- TIMESTAMP. CLUSTER (PART 1 VALUES('099999999'), PART 2 VALUES('199999999'), PART 3 VALUES('299999999'), PART 4 VALUES('399999999'), PART 5 VALUES('499999999'), PART 6 VALUES('599999999'), PART 7 VALUES('699999999'), PART8 VALUES('799999999'), PART 9 VALUES('899999999'), PART 10 VALUES('999999999') ); [...]
28309 90 30_Re: Restart Image copy utility13_David S Waugh16_dsw-dba@JUNO.COM29_Sat, 14 Sep 2002 06:31:47 GMT569_- Kathy:
I guess have a slightly different opinion on this -- I've been stacking Image Copies on tape for years now, and I've rarely had any problems with restarting the job or recovering tablespaces later. Of course, I don't have something like JOBTRAC uncataloging tape datasets after a job abend.
The advantages I see with this approach are: 1. Faster job execution with less overhead -- JES and MVS take a certain amount of time to initialize and cleanup each job step. I remember doing a test a long time ago where I ran an I/C job that had 200 [...]
28400 50 76_Re: Selecting unnecessary columns / updating col umns that have not changed.11_Grant Allen22_Grant@TOWERSOFT.COM.AU31_Sat, 14 Sep 2002 21:51:01 +1000425_- Grainger, Phil[SMTP:Phil.Grainger@CA.COM] wrote: > >Unless people have time (or MIPS) to burn, I always work on the > assumption that you NEVER do ANYTHING that you don't have to (in fact, > that spills over into my personal life too!!!) > > > >That's MY best practice! > > > >PS This world is getting REAL small - I flew back from Greece last week > and on the flight was a guy in a Brumbies CA rugby shirt!! > Phil, [...]
28451 101 29_Re: stored procedure question14_Michael Kalena15_kalena@BEAR.COM31_Sat, 14 Sep 2002 11:49:53 -0500485_- Hi Kathy,
Just wanted to pass on our experience. Hope it helps.
1. With every release of DB2 using Stored Procedures has gotten better. We are Version 7 but have been using them since Version 4. We have found the following items has made them much easier:
- Defining stored procedures with create, alter, and drop sql vs. the old way of inserting into SYSPROCEDURES. I should also include the grant/revoke of execute on them, made managing them easier too. [...]
28553 44 33_Re: DB2 V7 Dataspaces Question...16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Sat, 14 Sep 2002 23:52:41 +0200719_- Charles,
we use DB2 bufferpools in dataspace/hiperspace since a year with V6 WITHOUT any problem.
Roland
> -----Original Message----- > From: HREF="http://listserv.ylassoc.com./#full_name">Charles_Valentin > [SMTP:VALENCH@AOL.COM] > Sent: Saturday, September 14, 2002 12:07 AM > To: DB2-L@LISTSERV.YLASSOC.COM > Subject: DB2 V7 Dataspaces Question... > > > We're running DB2 V7 w/z/OS & 64-bit on our Production environments. > Can anyone share any User experiences using DB2 Bufferpool in > Dataspaces??? > We would like to "relieve" the "below-the-line" DBM1 storage constraint by > moving DB2 Bufferpools to Dataspaces & have the following concerns: > > 1) Are they "stable" (if [...]