1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l July 2002, week 3
2 104 42_Re: Stored procedure usefulness and LE....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Sun, 14 Jul 2002 22:26:51 -0700666_us-ascii Hello Myron,
Could you please explain (or point to a redbook/publication which could help me understand) how stored procedures can help in a single LPAR DB2?
For remote processing, stored procedures result in reduction of network traffic and this makes perfect sense to me.
For 'Local client processing' (single LPAR DB2), a point mentioned was that stored procedures provide with a 'consistent interface' to access tables. While I do appreciate the need and advantages of a 'consistent interface' for accessing tables, what I fail to understand is as to why a 'Stored procedure' is needed for providing this consistent interface? [...]
107 34 31_pdf question (Non-DB2 question)16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Sun, 14 Jul 2002 22:36:49 -0700483_us-ascii Esteemed listers,
This is as non-DB2 question as can be but hope you will excuse and help me out.
I open up an IBM redbook on the internet which is in the PDF (.pdf) format. Now, is there a way to 'cut and paste' a paragraph from the book, if I want to preserve the paragraph 'somewhere' (let us say in a Word document) for future reference? Or is it illegal to do so (probably if no one but IBM is allowed to 'reproduce' these books in full or in part)? [...]
142 15 45_Paolo Bruni/Almaden/IBM is out of the office.11_Paolo Bruni17_pbruni@US.IBM.COM31_Mon, 15 Jul 2002 00:41:19 -0600410_us-ascii I will be out of the office starting July 14, 2002 and will not return until July 22, 2002.
I will have only occasional access to my mail.
================================================ 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.
158 70 35_Re: pdf question (Non-DB2 question)11_Tom Flesher13_TomF@ENET.COM31_Sun, 14 Jul 2002 23:58:42 -0700318_us-ascii Adobe Acrobat Reader (I'm using 5.0) has both a text select tool and a graphics select tool. You click on the tool and then select what you're after. Then just copy (ctrl-C) and paste (ctrl-V) into your target application, e.g. Word. If you acknowledge where you got the material, I would think it's OK. [...]
229 22 34_Connect to DB2 on AS/400 from m/f.9_jane mike18_ibm390@HOTMAIL.COM31_Mon, 15 Jul 2002 07:13:48 +0000589_- hi,
We have reqiurement like this..We need to do a data transfer from m/f to DB2 on AS/400. How to go about this.
thanx in advance. Mike.
_________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx
================================================ 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.
252 18 46_MXG has different SMF 100 offset under DB2 V6?15_Stephen Mallett25_Stephen.Mallett@NT.GOV.AU31_Mon, 15 Jul 2002 02:14:15 -0500306_- Hi,
I can't say I know a lot about it myself but our Capacity planners believe they are experiencing difficulties with thier MXG product when they try to interpret SMF 100 (& 101?) records under DB2 V6. Does that ring a bell with any other MXG people out there?
regards and TIA, Steve [...]
271 30 35_Large Data in Table causing Problem9_Ali Akbar25_ali.akbar@CRESSOFT.COM.PK31_Mon, 15 Jul 2002 13:52:13 +0500434_us-ascii Hello everyone.
My Java application is inserting data (big data) into a table . A single query inserts about 80,000 records each time the applications runs. Application works fine and takes about 4-5 mins to insert the data.
But as soon as the count of data in the table increases above 500,000 (round about )records . Now the same application takes about 30 min to insert the same no of records (80,000). [...]
302 132 32_Platinum-log analyser problem???13_Sandeep Simon33_sandeep.s.alumootil@UKTRANSCO.COM31_Mon, 15 Jul 2002 10:09:41 +0100369_us-ascii Dear Db2 experts,
We run a CDC(Change DATA capture ) job to extract the changes to the database between two fixed points in time using a platinum startegy. The log analyser then picks up the data from the db2 archive logs and generates an sql for each update/insert/delete into the table. Today the platinum utility failed with the following error [...]
435 163 36_Re: Platinum-log analyser problem???23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 15 Jul 2002 10:56:38 +0100781_- Hi
How about increasing the region size on the step?
//STEP1 EXEC PGM=PTLDRIVM,REGION=4M,PARM='EP=BPLBCTL'
make it 16M or 32M ... ie make it :
//STEP1 EXEC PGM=PTLDRIVM,REGION=32M,PARM='EP=BPLBCTL'
Les
-----Original Message----- From: Sandeep Simon [mailto:sandeep.s.alumootil@UKTRANSCO.COM] Sent: Monday, July 15, 2002 10:10 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Platinum-log analyser problem???
Dear Db2 experts,
We run a CDC(Change DATA capture ) job to extract the changes to the database between two fixed points in time using a platinum startegy. The log analyser then picks up the data from the db2 archive logs and generates an sql for each update/insert/delete into the table. Today the platinum [...]
599 159 36_Re: Platinum-log analyser problem???15_Thomas Karlsson21_thkar2002@HOTMAIL.COM31_Mon, 15 Jul 2002 10:04:23 +0000747_- Hi Sandeep! Try to increase REGION up til 9M and increase DYNSORT = (DSNUM(2),SPACE(10,10),MAINSIZE(1000),MSG(N))
Both DSNUM, space and MAINSIZE
/Thomas
>From: Sandeep Simon >Reply-To: DB2 Data Base Discussion List >To: DB2-L@LISTSERV.YLASSOC.COM >Subject: Platinum-log analyser problem??? >Date: Mon, 15 Jul 2002 10:09:41 +0100 > >Dear Db2 experts, > >We run a CDC(Change DATA capture ) job to extract the changes to the >database between two fixed points in time using a platinum startegy. The >log analyser then picks up the data from the db2 archive logs and generates >an sql for each update/insert/delete into the table. Today the platinum [...]
759 164 36_Re: Platinum-log analyser problem???14_Harvey Puckett19_hpuckett@GTA.GA.GOV31_Mon, 15 Jul 2002 06:56:22 -0400552_us-ascii Yep. It does happen ... all I do is adjust the following
==> DYNSORT = (DSNUM(2),SPACE(10,10),MAINSIZE(1000),MSG(N)) to become DYNSORT = (DSNUM(12),SPACE(100,510),MAINSIZE(10000),MSG(N))
hth
-----Original Message----- From: Sandeep Simon [mailto:sandeep.s.alumootil@UKTRANSCO.COM] DYNSORT = (DSNUM(2),SPACE(10,10),MAINSIZE(1000),MSG(N)) Sent: Monday, July 15, 2002 5:10 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Platinum-log analyser problem??? [...]
924 53 17_Re: Trigger names11_Hardy, Dale14_DHardy@MIB.COM31_Fri, 12 Jul 2002 15:00:19 -0400296_iso-8859-1 We use TxxxGan.
Where T constant for Table xxx table identifier G constant for triGger a before / after & trigger type identifier 1 = before insert 2 = after insert 3 = before update 4 = after update 5 = before delete 6 = after delete n sequence number / order of creation [...]
978 119 63_Re: Can you issue a CALL stored procedure statement from SPUFI?11_Emrah GUVEN28_Emrah.GUVEN@PAMUKBANK.COM.TR31_Mon, 15 Jul 2002 14:20:16 +0300514_iso-8859-9 what about Version 6 ?? do you mean V7 supports only ?
-----Original Message----- From: Myron Miller [mailto:myronwmiller@YAHOO.COM] Sent: Friday, July 12, 2002 6:40 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Can you issue a CALL stored procedure statement from SPUFI?
You really don't need WinSQL. You can just go into the command line of DB2 Connect and issue the connect and sql directly. Or you can use Command Center. Either allows direct testing of the sql. [...]
1098 53 50_Re: MXG has different SMF 100 offset under DB2 V6?12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Mon, 15 Jul 2002 06:32:45 -0500544_iso-8859-1 Steve:
Normally, each release has a new map for the SMF records. This is true for the 100s, 101s and some of the 102s. There certainly should be support from MXG by now for V7, let alone V6. The normal pattern is to add fields to the end of a group within each record. The record consists of an SMF Header, this should not have changed. Then the record has a series of pointers. Each pointer consist of a length field, the number of occurrences of that group and the offset from the header to the first occurrence of the [...]
1152 115 63_Re: Can you issue a CALL stored procedure statement from SPUFI?12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 15 Jul 2002 05:03:41 -0700590_us-ascii DB2 OS/390 V6 as well as V7 support the SPB. You need DB2 Connect V7 to get the Stored procedure Builder (SPB). --- Emrah GUVEN wrote: > what about Version 6 ?? do you mean V7 supports only ? > > > -----Original Message----- > From: Myron Miller [mailto:myronwmiller@YAHOO.COM] > Sent: Friday, July 12, 2002 6:40 PM > To: DB2-L@LISTSERV.YLASSOC.COM > Subject: Re: Can you issue a CALL stored procedure statement from SPUFI? > > > You really don't need WinSQL. You can just go into the command line of DB2 > Connect and issue the connect and sql [...]
1268 76 39_ACTIVE LOGS IN UDB ON UNIX/NT/LINUX/OS217_Rabindra Senapati21_rsenapati@HOTMAIL.COM31_Mon, 15 Jul 2002 12:14:42 +0000
1345 25 16_SQLJ Access path12_Mark Anzmann24_manzmann@DHR.STATE.MD.US31_Mon, 15 Jul 2002 06:52:18 -0500556_- Listers-
We have some SQLJ programs (Java) that are accessing the mainframe (v6. The problem we are having is that the access path that DB2 is selecting during the remote bind process for any table with a key other than integer is either an index scan (mc=0) or tablespace scan. I have tried to use the dclgen feature to produce the host variables, as well as just keying them in. Both ways we get the same result. The only way that I have found for DB2 to get the "correct" accesspath is to use functions such as decimal (:hv, length,scale) [...]
1371 36 50_Re: MXG has different SMF 100 offset under DB2 V6?14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 15 Jul 2002 08:42:06 -0400330_ISO-8859-1 Record layout change with every release of DB2. Are you running the latest updates for MXG? MXG Version 19.19 dated Feb 14, 2002, was Air Mailed to all sites Feb 19-20, 2002.
www.mxg.com
If you're havinbg a problem, contact Barry Merrill at MXG. I'm sure he has a fix, or will help you very quickly. [...]
1408 91 36_Re: Platinum-log analyser problem???14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 15 Jul 2002 13:50:53 +0100355_iso-8859-1 Sandeep,
I assume you have logged this with CA technical support???
Without going into depth, it looks like there maybe too many log records to fit in some storage area that we have allocated.
As a test, I'd try rerunning the job with a larger region size (0M for example!) to see if that gets you around the problem. [...]
1500 90 36_Re: Platinum-log analyser problem???13_Sandeep Simon33_sandeep.s.alumootil@UKTRANSCO.COM31_Mon, 15 Jul 2002 13:45:35 +0100636_us-ascii Dear All,
Thank you for your inputs....we managed to get over this. The solution was quite funny. The CDC job was extracting data for 4 tables. We ran them independently one for each table and all except one table went through. We were extracting the changes for only one day and it kept failing for this one table until we kept decreasing the time perdiods to one hour gaps..and the error was LAE0036I Log Extract processing completed normally. LAE0183E URID control block limit reached, unable to expand block size. LAE0003E Insufficient virtual storage. GETMAIN error CSECT LAA@RPTI LAE0032E LAL@REPT terminated [...]
1591 156 42_Re: Stored procedure usefulness and LE....12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 15 Jul 2002 06:06:54 -0700453_us-ascii Raquel, I don't know of a reference right off the top of my head.
And yes, you can create a callable routine that can referenced. If its a separate package, then it will be as easy to track and maintain as a stored procedure. Whether its easier to do it this way and provides greater performance benefits, I don't know. I have my own personal preferences and feel that stored procedures are easier to manage than ordinary routines. [...]
1748 143 36_Re: Platinum-log analyser problem???13_Sandeep Simon33_sandeep.s.alumootil@UKTRANSCO.COM31_Mon, 15 Jul 2002 14:19:39 +0100477_us-ascii Phil,
We have received a ZAP from CA which increases the URID control bl0ock limit....Am not sure if that is the actual problem...also what is worrying me is why should some tables(equally large) be successful while some(this one in particular) not
Thanks and Regards, Sandeep Simon ________________________________ Sandeep Simon Alumootil
Office: 01455 892062 (Internal 715 32062) Mobile: 07879430971 Email:Sandeep.S.Alumootil@Uktransco.com [...]
1892 93 52_Re: Bufferpool Management: Random/Sequential Objects12_Hayden Jones25_Hayden_Jones@GRAINGER.COM31_Mon, 15 Jul 2002 08:49:41 -0500613_us-ascii Yep, The -DIS BUFFERPOOL command gives pages read as sequential or random, though not, of course, at the object level. My guess was that it doesn't get that information directly, but rather calculates it from more granular reporting.
The monitoring tool I mentioned is CA Insight. It has a standard report that provides information at the object level. My hope was that I could calculate the random/sequential statistics from what it provides. Yes, it uses traces to get its data, and I'm sure I could get more detailed infromation from a custom Insight report, but my experience in the past [...]
1986 188 36_Re: Platinum-log analyser problem???14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 15 Jul 2002 15:06:08 +0100372_iso-8859-1 Sandeep,
This is good news.
Also, as an aside, now might be a good time to suggest upgrading to a supported version of Log Analyzer (I am told you are on a VERY old version).......
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 [...]
2175 25 41_Re: selecting first 200 rows from a table0_19_Tim.Lowe@STPAUL.COM31_Mon, 15 Jul 2002 09:16:09 -0500297_us-ascii Duam, This past January Mohammad Khan sent a simple UDF that generated sequential numbers. The thread was called "Getting Row number in 'SELECT'". It could be used in your select to limit the number of rows returned in DB2 V6. For example: Select col1 from table1 where SEQ() <= 200 [...]
2201 59 50_Re: MXG has different SMF 100 offset under DB2 V6?13_Martin Packer24_martin_packer@UK.IBM.COM31_Mon, 15 Jul 2002 15:29:40 +0100281_us-ascii SMF 101 record layout changed quite markedly in V6. The QWAC section had some fields moved to the new QWAX section. To support both Version 5 and Version 6+ was a significant deal for us 2 years ago. Version 7 did not introduce more of that - just additional fields. [...]
2261 180 36_Re: Platinum-log analyser problem???13_Sandeep Simon33_sandeep.s.alumootil@UKTRANSCO.COM31_Mon, 15 Jul 2002 15:25:56 +0100352_us-ascii Dear Phil,
Thank you for that....we would have considered the option of upgrading but in this context it does not really help as we are on db24.1 and upgrading platinum from 97d to 99 will still not make it supportable!! or will it?
Thanks and Regards, Sandeep Simon ________________________________ Sandeep Simon Alumootil [...]
2442 37 31_Empty QWACWLME for "db2cca.exe"13_Martin Packer24_martin_packer@UK.IBM.COM31_Mon, 15 Jul 2002 15:37:12 +0100524_us-ascii I'm working with SMF 101 Accounting Trace to classify DDF users (QWHCATYP = QWHCDUW or QWHCRUW) and understand more about who would get at a given client's mainframe DB2 subsystem via DDF and how well Inactive Threads etc are working for them.
I turned my attention to the QWACWLME field (which gets populated with the WLM Service Class for DDF work and is otherwise hex zeroes). This works fine for all but a few cases. In the anomalous cases I see hex zeroes for DDF work when I wouldn't expect to... [...]
2480 28 17_Consistency Token18_Philippe J. Herman15_pherman@EPO.ORG31_Mon, 15 Jul 2002 16:27:35 +0200390_us-ascii Hi All,
I'm busy decoding the DB2 consitency token into a timestamp
The formatted timestamp gives me always a GMT time and date
Question : Is the contoken always taken from the GMT time and date or is my algorythm wrong ?
Thanks in advance for your help
Philippe J. HERMAN DB2 System Administrator European Patent Office - The Hague [...]
2509 117 22_MsAccess 2000 & DB2 V618_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Mon, 15 Jul 2002 10:51:30 -0400557_iso-8859-1 Can anyone shed any light on this. I have 3 tables in DB2 production (version 6) that we created. One of the columns is varchar(6000). Largest table has 65,000 rows, approx. When I view the tables using MsAccess 97 I can see the thread coming through our TMON monitor and it returns the results set to my screen. I can scroll up and down left/right and max to the bottom, no problems. The problem is our client(s) are using MsAccess 2000 and when they try to open up these tables they get a 'ODBC call failed' Memory allocation failure (#0) [...]
2627 84 35_Re: Empty QWACWLME for "db2cca.exe"28_pgunning@breakthroughdb2.com17_db2jock@YAHOO.COM31_Mon, 15 Jul 2002 08:21:39 -0700295_us-ascii If WLM is in goal mode and DDF is not classified, it will default to a discretionary service class of SYSOTHER, very little priority. If WLM is in compatibility mode and DDF is not specifically managed, then all DDF enclaves run at the priority of the Dist address space. HTH Phil [...]
2712 86 34_Re: Copy DB2 Archive Tape question12_Weaver, Rick19_Rick_Weaver@BMC.COM31_Mon, 15 Jul 2002 10:30:04 -0500702_iso-8859-1 Look for the following message on the console log:
DSNJ139I LOG OFFLOAD TGASK ENDED
You can trigger events from that.
Rick Weaver Product Manager Enterprise Database Recovery BMC Software, Inc.
-----Original Message----- From: Lee Mandell [mailto:DBMSUser@AOL.COM] Sent: Friday, July 12, 2002 10:38 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Copy DB2 Archive Tape question
Hi All, We would like to setup NetView to catch DB2 message when an archive is completed and automatically start a started task to copy the archive tape to another tape for disaster recovery. What DB2 message should we look at and also do you have an example of [...]
2799 214 36_Re: Platinum-log analyser problem???14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Mon, 15 Jul 2002 10:41:56 -0500375_us-ascii I usually use REGION=0M on the JOB or STEP (yes, that's zero Meg, not a mis-type) -- that will give you up to the maximum size allowed in your MVS system. Note that your Systems Programmers have probably set an upper limit for batch REGION sizes (i.e. you may specify 0M or 32M, but their limit only allows you to go up to , say, 8M or 9M), so check with them. [...]
3014 145 36_Re: Platinum-log analyser problem???14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 15 Jul 2002 17:04:23 +0100383_iso-8859-1 I wasn't going to mention what version of DB2 you were on, but thinking about it you may be right.
P99 IS supported (obviously) but may NOT be supported when running with V4 of DB2
I'll check
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 [...]
3160 29 37_cursor sometimes sensitive to inserts11_Daniel Adam16_dadam@GRATEX.COM31_Mon, 15 Jul 2002 11:19:16 -0500510_- Hi, This is DB2 v7 on OS/390. Our application opens a cursor on a table (T1) and in a loop it fetches the rows and does some processing for every row - among other things it inserts into the very same T1. When the cursor returns <100 rows, everything works fine. When the number is >= 100 rows, we encounter infinite loop - or at least it loops a lot more than the initial number of the rows that need to be processed. (I was not able to trace how the processing ended in that case.) When we define our [...]
3190 67 39_Re: Large Data in Table causing Problem16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM31_Mon, 15 Jul 2002 10:52:17 -0700583_us-ascii Ali,
The first thing I would do is to check your average getpage counts for the insert operation. I'm suspect that you may be running out of PCTFREE and FREEPAGE on your objects. Once this begins to happen, DB2 can spend a relatively large amount of time trying to find free space for the inserts. You can validate this by running RUNSTATS and viewing your NEAROFFPOSF, FAROFFPOSF, and LEAFDIST values. If this is your situation, you may want to consider providing more adequate values for PCTFREE and FREEPAGE, then running a reorg/runstats against the object. [...]
3258 63 42_Re: Stored procedure usefulness and LE....0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Mon, 15 Jul 2002 13:04:38 -0500530_us-ascii Hello,
We are running a single LPAR DB2 and use SP's extensively. Here are a few of the reasons:
1) Accessibility. We can access the SP from Cobol, CICS Cobol, Rexx in Batch, Rexx in TSO, etc. We also access them from Powerbuilder, off-platform via DRDA (DDF), but perhaps that goes beyond the scope you were referring too. Now, perhaps it's our lack of other installed technologies which make this the case, but our SP's are the closest thing we have to common "objects" in our mainframe environment. [...]
3322 71 41_Re: cursor sometimes sensitive to inserts16_Frank E. Giguere13_giguef@NU.COM31_Mon, 15 Jul 2002 15:03:55 -0400419_us-ascii Daniel,
I've encountered this same problem. When you insert (or update) rows that would logically position them at the end of your cursor, and you have filled up your buffer pool space upon the open of the cursor without satisfying the entire result set, you may be "reselecting" those rows when the cursor is reloading the buffer with remainder of your result set. Thus, you can fall into a loop. [...]
3394 15 46_Gerri Lloyd/Stamford/IBM is out of the office.11_Gerri Lloyd17_glloyd@US.IBM.COM31_Mon, 15 Jul 2002 14:20:48 -0500409_us-ascii I will be out of the office starting July 15, 2002 and will not return until July 22, 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.
3410 23 39_Testing Disaster Recovery at local site11_Jeff Agosta20_agost003@MC.DUKE.EDU31_Mon, 15 Jul 2002 15:07:43 -0500581_- Hello esteemed colleagues (and the rest of all y'all), We are currently looking at options of doing a local d/r to a) get "truer" d/r timings, b) practice our d/r scenarios, and c) (mostly) appease management. My question to the list is to inquire what option(s) are recommended? In additional to REXX and the standard utilities, we have at our disposal: BMC's Recovery Manager, Recover Plus, and Change Manager. The options discussed with my associates are: establish a tracker site, use our test LPAR to do the d/r, do a d/r on our prod box while playing the rename game. [...]
3434 15 13_Index trivia.13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Mon, 15 Jul 2002 15:47:16 -0500529_iso-8859-1 Just had a little "index trivia" someone asked me about and I wasn't sure how to respond (well, I had a rough idea how to respond, just not how to answer the question). Does anyone out there know what an "Index minipage" is? This is listed as resource type "00000301" in the messages and codes manual ("DB.SP.PG.MP" -- "Index Minipage"), but from a quick check this appears to be the only place it's mentioned. Not that it's important (as I said, just a little trivia), but does anyone know what a "minipage" is? [...]
3450 33 27_TCPKPALV - TCPIP keep alive13_Jeremiah Eden28_JEREMIAH.EDEN@RADIOSHACK.COM31_Mon, 15 Jul 2002 15:45:43 -0500403_- We have had several occasions where DDF has hung after getting an error condition from a server connection. IBM pointed out ZPARM TCPKPALV, which may require changing from our current value of ENABLE. Has anyone ever had to set this to a value? Our last occurrence was from a DataJoiner server that took a hardware error. Stopping DDF with Force mode did not work and canceling brought DB2 down. [...]
3484 54 17_Re: Index trivia.0_19_Tim.Lowe@STPAUL.COM31_Mon, 15 Jul 2002 16:16:30 -0500715_us-ascii Could it have to do with the old "subpages" that used to be in type-1 indexes?
"Vaughan, Mike" cc: Sent by: DB2 Data Subject: Index trivia. Base Discussion List
07/15/2002 03:47 PM Please respond to DB2 Data Base Discussion List
Just had a little "index trivia" someone asked me about and I wasn't sure how to respond (well, I had a rough idea how to respond, just not how to answer the question). Does anyone out there know what an "Index minipage" is? This is listed as resource type "00000301" in the messages and [...]
3539 118 17_Re: Index trivia.16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 15 Jul 2002 22:11:47 +0100455_iso-8859-1 Hi Mike - I might be wrong but I'm shooting anyway. I believe I recall MINIPAGE from way back when I used TYPE 1 indexes and SUBPAGES 1,2,4,8,16. The MINIPAGE was the logical SUBPAGE within an index page.
Kind regards Steen Rasmussen Computer Associates
-----Original Message----- From: Vaughan, Mike [mailto:Vaughan.Mike@PRINCIPAL.COM] Sent: 15. juli 2002 22:47 To: DB2-L@LISTSERV.YLASSOC.COM Subject: Index trivia. [...]
3658 44 17_Re: Index trivia.13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Mon, 15 Jul 2002 16:32:27 -0500332_iso-8859-1 Maybe it has something to do with a technique for accessing data efficiently ... i did not read all of this, but feel free to follow the link ... do a search on minipage, db2 etc in this document.
I guess the following 2-line url will get split in this posting, so don't forget to join them again .... enjoy. [...]
3703 50 21_Re: Consistency Token13_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Tue, 16 Jul 2002 08:07:07 +1000432_iso-8859-1 Hi Philippe, I could be wrong here (chorus "He usually is") but I think it depends on how your sysprogs have set up your machine. You can run on GMT with an offset (timezone in unixspeak) or you can run on local time. If you run on local time you need to stop during any daylight saving changeover, so many (most?) shops choose to run on GMT. The RBA/LRSN uses the storclock which does not have any offsets applied. [...]
3754 15 26_Re: MsAccess 2000 & DB2 V622_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Mon, 15 Jul 2002 17:49:18 -0500434_- The information contained in this e-mail is strictly confidential and for the intended use of the addressee only; it may also be legally privileged and/or price sensitive. Notice is hereby given that any disclosure, use or copying of the information by anyone other than the intended recipient is prohibited and may be illegal. If you have received this message in error, please notify the sender immediately by return e-mail. [...]
3770 71 21_Re: Consistency Token14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 16 Jul 2002 11:05:42 +1000307_US-ASCII Well (Steve), you're not wrong here - well a bit wrong perhaps.
Where the (slight) error creeps in is that the STCK value includes the (currently) 21 leap seconds that have occurred. That is, if you ignore the leap seconds, the calculated time will be 21 seconds after the actual time. [...]
3842 99 41_Re: cursor sometimes sensitive to inserts14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 16 Jul 2002 11:24:56 +1000586_US-ASCII The name of this particular bug is the "Hallowe'en" bug. Because some poor sod was working late on Hallowe'en when it was first encountered.
The bug occurs with an un-materialised query - ie no sorts etc. If the query is materialised (or, I suupose, uses INSENSITIVE), then all the data required is stored away in DSNDB07 and data passed to the application from there: inserting a row will not affect DSNDB07 and so the new row cannot be passed to the application. If the query is non- materialised, then data is retrieved from the actual index and data pages, and [...]
3942 62 25_Restore Database on Linux12_Saima Jelani22_sjelani@COMSATS.NET.PK31_Tue, 16 Jul 2002 10:19:45 +0500319_iso-8859-1 Hello i have used following commad to restore the database on linux
restore database iin from /home/db2admin taken at 2002071562806 into dbname redirect without rolling forward without prompting
but it gives error invalif time stamp.please tell where i am wrong
Thanks in advance
4005 43 29_Re: Restore Database on Linux11_Grant Allen22_Grant@TOWERSOFT.COM.AU31_Tue, 16 Jul 2002 14:53:08 +1000498_- Saima Jelani[SMTP:sjelani@COMSATS.NET.PK] wrote: > >Hello > > i have used following commad to restore the database on linux > > > > restore database iin from /home/db2admin taken at 2002071562806 into > dbname redirect without rolling forward without prompting > > > >but it gives error invalif time stamp.please tell where i am wrong > > > >Thanks in advance > Your time fragment has only five digits ... 62806 (assuming the date is 20020715). Do you mean 062806? If so try 20020715062806. [...]
4049 79 29_Re: Restore Database on Linux12_Saima Jelani22_sjelani@COMSATS.NET.PK31_Tue, 16 Jul 2002 12:34:10 +0500490_iso-8859-1 Hello timestamp problem is solved now but i have one new problem, i have used following commad
restore database test from /home/db2admin/ taken at 20020716115714 into two redirect without rolling forward without prompting
but get the following error
SQL1277N Restore has detected that one or more table space containers are inaccessible, or has set their state to 'storage must be defined'. DB20000I The RESTORE DATABASE command completed successfully. [...]
4129 62 20_Re: SQLJ Access path18_Jose Ramon Vazquez17_jrvazquez@ATCA.ES31_Tue, 16 Jul 2002 09:04:49 +0200525_us-ascii Hi Mark,
We had the same problem with DECIMAL DB2 columns, the DBRM was wrong in the host variables. The solution in our LINUX to DB2 OS/390 V6 environment was the FIXPAK 5.
Regards.
Mark Anzmann @LISTSERV.YLASSOC.COM> con fecha 15/07/2002 13:52:18
Por favor, responda a DB2 Data Base Discussion List
Enviado por: DB2 Data Base Discussion List [...]
4192 37 43_Re: Testing Disaster Recovery at local site11_Lee Mandell16_DBMSUser@AOL.COM29_Tue, 16 Jul 2002 03:12:41 EDT353_US-ASCII Jeff, You may wish to look at a software called GENDB2. I think the firm name is recovery knowledge or something close to that. Don't know the web site, but I am sure you can find it. The software have a procedure to do a partial DR at you local site using another set of DR BSDS. They say its about 95 percent of the complete DR process. [...]
4230 18 50_Dave Raiman/Santa Teresa/IBM is out of the office.11_Dave Raiman17_raiman@US.IBM.COM31_Tue, 16 Jul 2002 01:58:45 -0600321_us-ascii I will be out of the office starting July 15, 2002 and will not return until July 19, 2002.
I am on vacation. I will not receive phone mail or email until I return. Please contact Nick Gariaeff 463-3389 for CCE issues or my manager, Annmarie O'Donnell at 408-463-4280 for other urgent issues. --Dave [...]
4249 19 40_Elizabeth F. Brune is out of the office.18_Elizabeth F. Brune25_Brune_Elizabeth_F@CAT.COM31_Tue, 16 Jul 2002 03:08:46 -0500373_us-ascii I will be out of the office from 16Jul2002 until 5Aug2002.
I will not have access to email and limited access to audis. If you need DRM/Database Administration service, please contact: Mike Roling (Supervisor, DRM OS/390) (6-3146) Garrett Gardner (Dist. Platform DBA) (6-3919) Ryan Clark (Dist. Platform DBA) (6-0134) Dan Parsons (Senior DBA) (6-3665) [...]
4269 97 29_Re: Restore Database on Linux13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Tue, 16 Jul 2002 10:06:57 +0100359_iso-8859-1 Saimi,
You have used the keyword "REDIRECT" which specifies that you are going to do a redirected restore.
The next stage in the process is, within the same unit of work, to specify a SET TABLESPACE CONTAINERS command for each tablespace you want to place on a different location and then issue a "RESTORE ... CONTINUE" command. [...]
4367 28 39_Re: Large Data in Table causing Problem11_Jürgen Götz25_juergen_goetz@T-ONLINE.DE31_Tue, 16 Jul 2002 10:58:10 +0200352_iso-8859-1 Hi Ali,
you can also try to
ALTER TABLE XXXXXXX APPEND ON
which avoids freespace lookups and only appends data to the table. But this may decrease reading performance afterwards.
If you have a timestamp in your records, it could be helpful to define this field (and others in addition) as a clustered index. [...]
4396 83 71_Very simple SQL problem: Could this SQL be made to run under 2 minutes?11_Linda Ayeni22_lindaayeni@HOTMAIL.COM31_Tue, 16 Jul 2002 03:21:17 -0700624_- Good day listers, I have the following online(CICS) sql consistently taking about 2+ minutes to display a selection and this is not acceptable(to put it lightly) This is a 2.4million row table:
EXEC SQL SELECT AOL_ NUMBER , AOL_ NBR_CHECK_DGT , AOL_NBR , AOL_ACTION_BEGIN_DATE INTO :AOL-LAYOUT.AOL-NUMBER:WS-AOL-NO-IND, :AOL-LAYOUT.AOL-NBR-CHECK-DGT , :AOL-LAYOUT.LIC-NBR, :AOL-LAYOUT.ACTION-BEGIN-DATE FROM AOL_ACTION WHERE (VIOLATION_CODE = 'D51' AND SUBSTR(AOL_ NUMBER,4,6) = :AOL-LAYOUT.AOL-NUMBER :WS-AOL-NO-IND AND AOL_ NBR_CHECK_DGT = :AOL-LAYOUT. AOL-NBR-CHECK-DGT AND REINSTATE_DATE IS NULL) END-EXEC [...]
4480 210 57_Re: Very simple SQL problem: Could this SQL be made to ru14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 16 Jul 2002 12:16:11 +0100493_iso-8859-1 Linda what exactly ARE the index definitions?? (i.e. what are the key columns)
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: Linda Ayeni [mailto:lindaayeni@HOTMAIL.COM] Sent: 16 July 2002 11:21 To: DB2-L@LISTSERV.YLASSOC.COM Subject: [DB2-L] Very simple SQL problem: Could this SQL be made to run under 2 minutes? [...]
4691 146 54_AW: questions regarding the Oracle Transparent Gateway11_Martin Balz23_Martin.Balz@HVBINFO.COM31_Tue, 16 Jul 2002 13:08:31 +0200469_iso-8859-1 Hallo,
we are using OTG V4.0.1.1 together with DB2 V7.1. Some Problems occurred after the Migration of DB2 from V6 to V7:
=> SQL -302 in Oracle: - Set "DB2DESCTAB = NO" in G4DB2ENV because it seems that OTG V4 does not understand the DB2 V7 "Describe Table" Stmt
=> Deadlocks / TimeOuts in the Gateway: - Rebind the OTG-Plan with ACQUIRE(USE) and RELEASE(COMMIT). - Set Row-Level Locking for the Tablespace with the Oracle2PC Table [...]
4838 184 75_Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes?17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Tue, 16 Jul 2002 13:24:13 +0200631_us-ascii Hi Linda,
you'll probably get a dozen responses suggesting to add a column AOL_NUM2 where AOL_NUM2=substr(AOL_NUMBER, 4, 6) and search on that (you could use a trigger to maintain that column). Use of an index is obviously not possible for the substring search. You did not specify sufficient information for good suggestions (for example the index keys are missing, how many rows does the query return, what is the primary key). Check the filter factors for VIOLATION_CODE, especially value D51 (if it is fixed), and AOL_NBR_CHECK_DIGIT. The filter factor for the latter should be at least 10 (if numeric) or [...]
5023 111 21_Re: Consistency Token18_Philippe J. Herman15_pherman@EPO.ORG31_Tue, 16 Jul 2002 14:49:32 +0200365_us-ascii Hi James and Steve and the others,
In my case I don't have a few seconds difference but exactly 2 hours.
When I use macro STKCONV to display the Token in a readeable format I got the same 2 hours leap.
Our systrem use an ETR with an offset of GMT +2
So I guess that DB2 when formatting a timestamp is applying this offset. [...]
5135 135 75_Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 16 Jul 2002 08:16:17 -0500472_iso-8859-1 Linda,
As Dr Ebert suggested, splitting the AOL_NUMBER into two separate columns is probably not an option, thus I would suggest some alternate indexing to allow matching on the equals predicates you do have.
At best you would want:
VIOLATION_CODE AOL_ NBR_CHECK_DGT REINSTATE_DATE AOL_ NUMBER
and add AND AOL_NUMBER LIKE '%six-char-value%' to the WHERE clause to at least obtain stage 1 access and potentially index screening. [...]
5271 26 25_IRLM Dispatching Priority11_Ruth Sutlic28_ruth.sutlic@MAIL.STATE.AR.US31_Tue, 16 Jul 2002 08:02:30 -0500485_- Up until this weekend, we started IRLM outside of DB2. This weekend, I removed the S IRLM and let DB2 bring it up. Immediately at startup we saw the message. DSNT405E - DSNTLIDE DISPATCH PRIORITIES NOT IN SYNC: IRLM
I suspect we had this problem all along, but since IRLM was now being started by DB2, it was more apparent. I worked with a coworker to change the dispatching priority for IRLM. We don't run in goal mode and we have 5 IRLM regions up. (Yes, we have IMS). [...]
5298 24 15_Query re-write?18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Tue, 16 Jul 2002 08:08:34 -0500446_- Hi All,
Trying to catch up on the happenings here on DB2-L after a brief hiatus. There is an article on Outer joins by Terry which says.... -------- The DB2 for OS/390 and z/OS optimizer rewrites all right outer joins to become left outer joins, by simply inverting the tables in the FROM clause and by changing the keyword RIGHT to LEFT -------- I would like to know what forces the optimizer to re-write right outer join queries. [...]
5323 60 19_Re: Query re-write?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 16 Jul 2002 09:00:05 -0500358_iso-8859-1 Rajendra,
My understanding is that this is simply a matter of code reuse. Rather than the DB2 developers writing code to support left and right outer joins, they merely convert the right joins upfront to left joins and use the one set of program modules. This occurs for all right joins (that are cannot be simplified to inner joins). [...]
5384 33 11_add to text15_Shields Michael28_ShieldsMichael@JOHNDEERE.COM31_Tue, 16 Jul 2002 09:21:14 -0500253_iso-8859-1 I would like to concatenate a chunk of text onto a VarChar column. The row already exists and I have the key to do the update. I don't want to do the select and manipulate the text in my app. I only want to add to the text on the table. [...]
5418 50 15_Re: add to text15_Shields Michael28_ShieldsMichael@JOHNDEERE.COM31_Tue, 16 Jul 2002 09:58:29 -0500605_iso-8859-1 I figured out one solution:
update adrdnst.entry_sys set entry_sys_cd = rtrim(entry_sys_cd) || 'I' where entry_sys_nm = 'COMPUTER_GENERATED'
If anyone has a better one please forward it on to me.
> -----Original Message----- > From: Shields Michael > Sent: Tuesday, July 16, 2002 9:21 AM > To: 'DB2-L@LISTSERV.YLASSOC.COM' > Subject: add to text > > I would like to concatenate a chunk of text onto a VarChar column. > The row already exists and I have the key to do the update. > I don't want to do the select and manipulate the text in my app. > I only want [...]
5469 93 15_Re: add to text14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Tue, 16 Jul 2002 11:10:53 -0400836_iso-8859-1 Works with "||" as below...
Abhijit
#db2 "create table mac.halu (id int, varcol varchar(255) ) in mac_d_4k_tsp001 index in mac_i_4k_tsp001" DB20000I The SQL command completed successfully.
#db2 "insert into mac.halu values (1, 'jala')" DB20000I The SQL command completed successfully.
#db2 "select * from mac.halu"
ID VARCOL
----------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- --------------------------- 1 jala
1 record(s) selected.
#db2 "update mac.halu set varcol = varcol || 'bula' where id = 1" DB20000I The SQL command completed successfully. [...]
5563 20 20_Re: SQLJ Access path12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 16 Jul 2002 10:08:20 -0500384_- Java data type matching is relatively difficult, as you've found. Are you using db2profc? Some of the performance improvements are only in V7. There are quite a few more suggestions in the V7 Selected Performance Topics Redpaper and in presentation DMAS2: Java: Design for High Performance The pdfs are on the web: http://www.ibm.com/software/data/db2/os390/presentations.html [...]
5584 76 43_Re: Testing Disaster Recovery at local site0_24_William_O'Black@FFIC.COM31_Tue, 16 Jul 2002 08:14:17 -0700502_us-ascii We run PeopleSoft here (thousands of tables and hundreds of tablespaces) and use Recovery Manager to generate our recovery JCL. We've run several tests both with a local recovery of a test system, and a full blown recovery of our prod system at a DR site, and we've never had a problem with the jobs. If you need to run a DR test locally, I'd lobby for a test using a test DB in a test subsystem and then extrapolate out to get prod timings. That's what we did and were reasonably close. [...]
5661 58 29_Re: IRLM Dispatching Priority12_tim malamphy20_timalamphy@YAHOO.COM31_Tue, 16 Jul 2002 08:22:30 -0700565_us-ascii Should you have set the ICS instead of ICD? This is where the mappings of what job/tran names get put into which performance groups happens. Or perhaps the names of the DB2 IRLMs are not in SYS1.PARMLIB(IEAICS01)? Tim --- Ruth Sutlic wrote: > Up until this weekend, we started IRLM outside of > DB2. This weekend, I > removed the S IRLM and let DB2 bring it up. > Immediately at startup we saw > the message. > DSNT405E - DSNTLIDE DISPATCH PRIORITIES NOT IN SYNC: > IRLM > > I suspect we had this problem all along, but [...]
5720 177 75_Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes?10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Tue, 16 Jul 2002 10:29:08 -0500433_iso-8859-1 Linda, I hate to state the obvious, but sometimes that's what trips me up the worst! Do your program definitions exactly match your DB2 column definitions? This will many times cause a tablespace scan. You're correct, the substring negates the index usage on AOL_NUMBER. Are your indexes all only 1 column? Do your queries only support 1 column in the where clauses, doubtful, at least here they don't work that way. [...]
5898 23 21_Re: Consistency Token12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 16 Jul 2002 10:33:54 -0500537_- I presume you understand that the CONTOKEN is not a defined interface for a timestamp. If somone uses the precompiler option LEVEL, then it's not a timestamp at all, for example. The design for this field is a consistency token that can be used in the interface with many languages, not a timestamp. Local time has duplicates more often, during the daylight savings shift, for example. So local time would not be a good choice for this field. In short, if a timestamp conversion technique breaks, then you get to keep the pieces. [...]
5922 19 17_Temporal Database14_Stewart, Karen20_kstewart@STATE.PA.US31_Tue, 16 Jul 2002 12:32:42 -0400567_iso-8859-1 Is anybody using a temporal database for their online TP applications? One of our applications is proposing a new temporal DB and since temporal data can not fully be enforced with DB2 RI they also propose removing all DB2 RI and replacing it with application RI mainly at a DB2 Trigger level. As a DBA supporting this database I am very concerned about performance with the size of the data and the complexity of the SQL and also the lack of DB2 RI. Any input to the topic of temporal data in an online CICS environment would be greatly appreciated. [...]
5942 51 20_Re: SQLJ Access path12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Tue, 16 Jul 2002 12:38:11 -0400499_us-ascii Roger-
Thanks for the reply!
We are using db2profc. I did some more investigation and found that I probably need to add the "-online= db2ssid" parm of the db2profc command (note- boy is it hard to find documentation on the on this command!)
For anyone else having this problem, the other place the I found that had some good information (although somewhat dated) was www.txmq.com/PHP/Newsletter they have a DB2 section where I found the thing on the -online parm. [...]
5994 123 43_DSNT500I, resource unavailable, during LOAD17_Giorgio De Nunzio25_giorgio.denunzio@UNILE.IT31_Tue, 16 Jul 2002 18:37:43 +0200311_US-ASCII Hi all! Before all, sorry for the long post! I am having a problem while using (in a batch job I called LDSIAFDB) the LOAD utility (DB2 v3, MVS 5.2.2). The flat text files containing the data to be loaded into my database are a lot and some of them are quite large. They are the members of a PDS. [...]
6118 79 40_IEC070I 210(8,306)-213,abend0c4 IGDZILLA13_Jeremiah Eden28_JEREMIAH.EDEN@RADIOSHACK.COM31_Tue, 16 Jul 2002 12:01:49 -0500625_- IBM has said that several other shops are now experiencing this problem. If anyone wants share notes, please contact me directly.
-----Original Message----- From: Jeremiah Eden Sent: Friday, July 05, 2002 3:55 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: SMS problems
We recently went to SMS management for our DB2 objects. Since then, every now and then we are getting the following errors below when trying to take an extent. FYI, we don't extend VSAM datasets with any type of X37 product, so that is rule out. This error may occur over and over again and then it finally gets the extent (maybe [...]
6198 51 41_Re: cursor sometimes sensitive to inserts12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 16 Jul 2002 11:56:52 -0500537_- We talk to someone with this misconception every month or so. Bonnie Baker chose this subject for her next article in the DB2 Magazine. It's in the publishing cycle now, but watch for it on http://www.db2mag.com/
Q1) Cursors are not scrollable unless you specify that. Until V7, there have not been any static sensitive or insensitive cursors, and the default cannot change. When you use a cursor, you can sometimes see the changes you make. Isolation is from others, not from yourself, unless you use insensitive cursors. [...]
6250 16 29_Re: IRLM Dispatching Priority11_Ruth Sutlic28_ruth.sutlic@MAIL.STATE.AR.US31_Tue, 16 Jul 2002 12:00:44 -0500451_- OOps. I had a typo. It was the ICS and IOS that were changed to set the DP priority higher.
Any input on why the IRLM's didn't change that were stared by DB2?
Regards, Ruth Sutlic
================================================ 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.
6267 159 47_Re: DSNT500I, resource unavailable, during LOAD12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 16 Jul 2002 20:16:33 +0200345_- DB2 V3 - Are you serious ???
Anyway - look at the extents info under dslist (3.4) of ISPF, you'll probably see something between 119 -123 .
Your tablespace is defined with too small PRIQTY & SECQTY (or not at all - using the defaults). Look at SQL guide for CREATE TABLESPACE info. - how to define it with the correct size. [...]
6427 49 21_Re: Temporal Database24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Tue, 16 Jul 2002 13:32:55 -0700613_us-ascii Karen, Informix, now part of IBM has a DataBlade (read Extender) for temporal data. You might contact IBM regarding the availability of this functionality in DB2. If you run into a roadblock, get back to me.
Frank
On Tue, 16 Jul 2002 12:32:42 -0400, Stewart, Karen wrote:
>Is anybody using a temporal database for their online TP applications? One of our applications is proposing a new temporal DB and since temporal data can not fully be enforced with DB2 RI they also propose removing all DB2 RI and replacing it with application RI mainly at a DB2 Trigger level. As [...]
6477 29 41_OS390 via Control Center...missing adb2re12_tim malamphy20_timalamphy@YAHOO.COM31_Tue, 16 Jul 2002 10:33:38 -0700419_us-ascii Hi folks-
I'm using Control Center (7.2) on WIN2000 to get to V5.1 DB2 on OS/390. When I select a table and try to generate DDL, I get an error message SQL0440N indicating function ADB2RE is not found. Examining the SQL shows a "CALL ADB.ADB2RE" statement with a bunch of parms. I Couldn't find any reference to ADB2RE on the IBM Web site. Any pointers in the right direction would be appreciated. [...]
6507 103 41_Re: cursor sometimes sensitive to inserts12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 16 Jul 2002 20:36:09 +0200591_- Just a hypothesis - could be wrong:
Do you commit every 100 records and then reopen the cursor ? (means not using "with hold")
Cursor type Result table Visibility of own changes Visibility of other changes Updateability (*) No scroll (V6) Fixed, workfile No No No No scroll (V6) Base table access, No workfile Yes Yes Yes Insensitive scroll (V7) Fixed, declared temp table No No No Sensitive static scroll (V7) Fixed, declared temp table Yes(Inserts not allowed) Yes(No Inserts) yes Sensitive dynamic scroll (not V7) Base table access, No temp table Yes Yes Yes [...]
6611 90 39_Re: Large Data in Table causing Problem0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 16 Jul 2002 13:12:31 -0500323_iso-8859-1 Go to IBM website and look for following article "Insert Performance Considerations in DB2 for OS/390 - Part 1" (& pt 2) by Akira Shibamiya (file name S1323.PDF & S1326.PDF)
If you are doing a mass insert, have you considered writing the insert records out to a flat file then doing an online LOAD?. [...]
6702 55 45_Re: OS390 via Control Center...missing adb2re18_Gert van der Kooij16_gkooij@XS4ALL.NL31_Tue, 16 Jul 2002 20:14:40 +0200717_iso-8859-1 It's described in this PDF, it looks like you need to install db2admin on OS/390. ADB2RE is a stored procedure mentioned in this presentation..
http://www.share.org/proceedings/sh96/data/S1365.PDF
----- Original Message ----- From: "tim malamphy" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, July 16, 2002 7:33 PM Subject: OS390 via Control Center...missing adb2re
> Hi folks- > > I'm using Control Center (7.2) on WIN2000 to get to > V5.1 DB2 on OS/390. When I select a table and try to > generate DDL, I get an error message SQL0440N > indicating function ADB2RE is not found. Examining > the SQL shows a [...]
6758 151 41_Re: cursor sometimes sensitive to inserts15_Jackson Reavill18_damcon2@US.IBM.COM31_Tue, 16 Jul 2002 14:18:34 -0400395_us-ascii I'm coming in to this thread late so someone else probably already mentioned this... Couldn't you force materialization of the cursor, thereby, creating the result set prior to fetching? I would think this would avoid this type of problem.
Cheers, Jay
Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 356-5317, Tie Line 8-697-5317 [...]
6910 55 49_Question on disabling a data sharing environment.50_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM31_Tue, 16 Jul 2002 14:48:54 -0400367_iso-8859-1 I have been looking into removing the data sharing definitions between 2 db2 subsystems, on the same LPAR. The way in which this environment was established is somewhat bizarre. They originally defined datasharing between two LPARS, but then backed it off.
1) I did a display of the xcf, structure, strname and it displays that they are empty. [...]
6966 226 53_Re: Question on disabling a data sharing environment.13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM31_Tue, 16 Jul 2002 15:50:48 -0400667_US-ASCII Robert,
Tread VERY carefully here.
If, indeed, as you say below, "8) Both subsystems refer to separate catalogs/directory", then you may *not* have a 2-way data sharing group; it may be two one-way groups. (!!)
Re-check your information very carefully. Verify that the active ZPARMs point to different VCATs. Verify that each system has its own Workfile Databases (You don't say what Version of DB2 you've got; typically one subsystem uses DSNDB07 as the Workfile Database, the second and subsequent subsystems use *different* databases. (See SYSIBM.SYSDATABASE where TYPE = "W"). I'd say 99% sure that the subsystem defined to use [...]
7193 69 12_SQL question14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Tue, 16 Jul 2002 23:35:59 +0200532_- Hello DB2-Listers,
I had a SQL problem today and couldn't find a simple solution.
This statement would do it right, but it takes too much resources:
select t1.key1, t1.key2, count1, coalesce(count2,0) from (select key1, key2, count(*) as count1 from table1 where key1 like :hostvar group by key1, key2) as t1 left join (select key1, key2, count(*) as count2 from table2 where key1 like :hostvar and another_col between :hv1 and :hv2 group by key1, key2) as t2 on t1.key1 = t2.key1 and t1.key2 = t2.key2; [...]
7263 103 16_Re: SQL question13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 16 Jul 2002 17:46:52 -0500427_iso-8859-1 Bernd,
Change it to a FULL JOIN and let me know. If it works, I'll tell you why. If it doesn't, I've made an incorrect assumption about the access path and will ask for explain output, index information (cardinalities etc).
Regards 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 [...]
7367 49 47_Re: DSNT500I, resource unavailable, during LOAD17_Giorgio De Nunzio25_giorgio.denunzio@UNILE.IT31_Wed, 17 Jul 2002 00:48:16 +0200330_US-ASCII On Tue, 16 Jul 2002, Isaac Yassin wrote:
> DB2 V3 - Are you serious ???
ehm, yes :-) For development... It is enough, at present: till now we have developed in other environments, so we are now converting to DB2 some software that used a proprietary database. If things grow, we'll probably upgrade... [...]
7417 134 21_Re: Consistency Token14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 17 Jul 2002 11:09:25 +1000704_US-ASCII What's the value of CVTLSO - where the leap second offset is stored? Sounds like it's zero. Do you are have VM? Read http://bama.ua.edu/cgi-bin/wa?A2=ind0207&L=ibm- main&P=R46667&I=1 and following posts.
If you obtain consistency token at, say, 9am UCT, then you'll get (because you run your clock on UCT) a given consistency token irrespective of whether it's summer or winter. It's only if you want to convert the consistency token to local time that you have to worry about summer/winter time. Neither DB2/consistency-token-processing nor STCKCONV apply timezone offsets - DB2/consistency-token-processing because it doesn't, STCKCONV because it doesn't really know where you got [...]
7552 76 58_Re: AW: questions regarding the Oracle Transparent Gateway12_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 17 Jul 2002 15:56:47 +1200369_us-ascii Hi Martin,
You are a winner! I set DB2DESCTAB = NO as you suggested and Bingo! OTG 4.0.1.1 can talk to DB2 V7! Yee ha! I like your bind options. We've rebound the OTG plan iso(ur) as well, but probably won't make the Oracle2pc tablespace row-level locking. All our locking issues are with the objects being queried, not the OTG objects themselves. [...]
7629 158 41_Re: cursor sometimes sensitive to inserts12_Nosey Thimma16_cokey0@YAHOO.COM31_Tue, 16 Jul 2002 21:19:51 -0700584_us-ascii My friend also is facing a similar problem. But it is slightly different. His application program (query) gets into an infinite loop when the program is run under V7 (non-scrollable cursor). But it runs as expected when it is run under V6. Has anybody faced this problem?
--- Isaac Yassin wrote: > Just a hypothesis - could be wrong: > > Do you commit every 100 records and then reopen the > cursor ? > (means not using "with hold") > > > Cursor type Result table > Visibility of own changes > Visibility of other changes Updateability (*) [...]
7788 37 31_NODYNAM in stored Procedures...16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Tue, 16 Jul 2002 22:07:58 -0700465_us-ascii Esteemed Listers,
I understand that NODYNAM must be used to compile Stored Procedures. Suppose three stored procedures SP1, SP2 and SP3 call a stored Procedure SP. Now, if the called stored procedure SP is changed (or for that matter any subroutine that is being called is changed) and recompiled, it would mean that ALL the stored procedures CALLing it (SP1, SP2 and SP3 in this case) would need to be recompiled. Is my understanding correct? [...]
7826 79 47_AW: DSNT500I, resource unavailable, during LOAD41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Wed, 17 Jul 2002 08:09:06 +0200492_iso-8859-1 Giorgio,
there's always 'ALTER TABLESPACE tsname PRIQTY .... SECQTY ....'; there's no need to re-create the objects. If I were you, however, I'd check with some sort of 'space-manager' first to find out the appropriate tablespace-sizes. (and don't forget your indexes) If you haven't got any 'space-manager' I'm sure someone on this list is ready to provide some home-brew stuff. (and there are the archives' and documents' sites, you might want to check those first). [...]
7906 144 62_Re: Large Data in Table causing Problem - Temporary Tablespace9_Ali Akbar25_ali.akbar@CRESSOFT.COM.PK31_Wed, 17 Jul 2002 12:44:37 +0500541_us-ascii
Hello there,
I figured out that the problem was not in inserting data in the table. However select in the insert query was taking this much time.
Query : insert into A (a,b,c) select e,f,g from B // type of query
The problem is somewhat like this. The select query in the insert takes 2 - 3 min to fetch data from 2 or more table when the data is less than round about 500,000 . Now when the data in the table is more than 500,000 it was taking more than 30 min . I checked all the indexes [...]
8051 19 40_Elizabeth F. Brune is out of the office.18_Elizabeth F. Brune25_Brune_Elizabeth_F@CAT.COM31_Wed, 17 Jul 2002 02:07:51 -0500373_us-ascii I will be out of the office from 16Jul2002 until 5Aug2002.
I will not have access to email and limited access to audix. If you need DRM/Database Administration service, please contact: Mike Roling (Supervisor, DRM OS/390) (6-3146) Garrett Gardner (Dist. Platform DBA) (6-3919) Ryan Clark (Dist. Platform DBA) (6-0134) Dan Parsons (Senior DBA) (6-3665) [...]
8071 24 41_DR to another subsystem - dsndb07 problem11_Daniel Adam16_dadam@GRATEX.COM31_Wed, 17 Jul 2002 02:19:04 -0500567_- Hi, DB2 v7 on os390 v2r8. I'm doing disaster recovery to clone/copy one subsystem to another one in the same LPAR. After recovering the system catalog and directory I need to change vcat for the user tablespaces, workfile and temporary database. For user tablespaces and temp db it's easy and documented - using alter ts (not to use old stogroup), drop/create stogroup with new vcat, alter ts to use the stogroup again and recovering ts. I cannot figure out the way to do this for dsndb07 - DB2 will not allow to alter the tablespaces - sqlcode -620. I cannot [...]
8096 82 45_Re: DR to another subsystem - dsndb07 problem17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 17 Jul 2002 09:52:25 +0200441_us-ascii Normally, the DSNDB01, DSNDB06, and DSNDB07 are user-managed (USING VCAT), not DB2-managed (USING STOGROUP). A DROP won't delete the datasets, and you need to define them using IDCAMS before you create the TSs. I'm not even sure whether you actually can create DSNDB07 as DB2-managed. Check your SYSTABLESPACE on how exactly they are defined. If they really are DB2-managed, change them to user-managed (a one-time operation). [...]
8179 52 45_Re: DR to another subsystem - dsndb07 problem11_Daniel Adam16_dadam@GRATEX.COM31_Wed, 17 Jul 2002 03:05:59 -0500655_- Michael, We have the dsndb07 tablespaces defined as db2-managed - it works. Thanks for the idea, I think I will recreate them as user managed. Best regards, Daniel
On Wed, 17 Jul 2002 09:52:25 +0200, Dr. Michael Ebert wrote:
>Normally, the DSNDB01, DSNDB06, and DSNDB07 are user-managed (USING VCAT), >not DB2-managed (USING STOGROUP). A DROP won't delete the datasets, and >you need to define them using IDCAMS before you create the TSs. >I'm not even sure whether you actually can create DSNDB07 as DB2-managed. >Check your SYSTABLESPACE on how exactly they are defined. If they really >are DB2-managed, change [...]
8232 33 51_Re: AW: DSNT500I, resource unavailable, during LOAD17_Giorgio De Nunzio25_giorgio.denunzio@UNILE.IT31_Wed, 17 Jul 2002 14:08:58 +0200368_X-UNKNOWN On Wed, 17 Jul 2002, [iso-8859-1] "Kurtz, Rüdiger" wrote:
> there's always 'ALTER TABLESPACE tsname PRIQTY .... SECQTY ....';
OK, thanks! My question was also about changing my DB2 defaults, which are probably to small: should I go back to the installation JCL, or is it possible to change DB2 default parameters globally and dynamically? [...]
8266 184 48_Bonnie Baker at SIRDUG (Charlotte, NC) 8/22-8/2311_Paul Turpin32_pault@ALUMMAIL.SSB.ROCHESTER.EDU31_Wed, 17 Jul 2002 07:15:14 -0500397_- SIRDUG Meeting August 22 & 23, 2002
Featuring: Bonnie Baker
Back by popular demand!! We are very pleased to announce that Bonnie will again be sharing her unique perspectives on DB2 during her sixth annual Bonnie Baker class hosted by SIRDUG.
On August 22th, Bonnie will present "Explain Explained" for those who know explains are important, but aren't quite sure why. [...]
8451 118 16_Re: SQL question11_David Nance16_DWNance@FHSC.COM31_Wed, 17 Jul 2002 09:22:05 -0400600_US-ASCII Bernd, I'd be interested in finding out how Terry's solution worked for you. But, here's another solution that may work for you that I have used in processes here. select t1.key1, t1.key2, count1, coalesce(count2,0) from (select key1, key2, count(*) as count1 from table1 where key1 like :hostvar group by key1, key2) as t1 left join (select key1, key2, count(*) as count2 from table2 T2 ,table1 T1 where T1.key1 like :hostvar and T1.key1 = T2.key1 and T1.key2 = T2.key2 and another_col between :hv1 and :hv2 group by T2.key1, T2.key2) as t2 on t1.key1 = t2.key1 and t1.key2 = t2.key2; [...]
8570 63 35_Re: NODYNAM in stored Procedures...13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 17 Jul 2002 08:30:56 -0500367_iso-8859-1 Hi, The reason IBM says to use NODYNAM relates to the fact that DSNHLI is an alias to DSNELI - look at SDSNLOAD.
To solve the problem, I created a new loadlib, called DB2.DBxx.DSNALI.LOAD which contains one load module, DSNALI, and created an ALAIS of DSNHLI for it. This library is the first in the STEPLIB for the SPAS address space. [...]
8634 34 44_Question on limit key for partitioning Index11_David Nance16_DWNance@FHSC.COM31_Wed, 17 Jul 2002 09:42:03 -0400569_US-ASCII OS/390 DB2V6. I thought this would work, but apparently not. Wondering if others have had similar situation and how you overcame.
We are partitioning a fairly large table into 10 parts based on a new column(smallint), which contains the last two digits of the foreign key column(integer). Since the index would consist of this new column and the FK column, I wanted to put the FK column first and the new column second, so as not to have a partitioning index and a foreign key index. I really only want to give a limit key based on the new column, [...]
8669 67 41_Re: CICS Commands in Stored procedures...11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Wed, 17 Jul 2002 08:49:33 -0500405_iso-8859-1 IBM does provide a stored procedure that can be used to execute CICS transactions though. It's called DSNACICS. I haven't used it, but I installed it recently via a PTF.
Tina Hilton
-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: July 13, 2002 4:19 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: CICS Commands in Stored procedures... [...]
8737 50 34_Problem with export command UDB NT34_Luiz Cesar Vinhaes da Costa Junior27_vinhaes@CAIXASEGUROS.COM.BR31_Wed, 17 Jul 2002 10:56:45 -0300443_us-ascii Hi listers
I'm trying to export some lines from a table and I'm geting the error masage
SQL3104N The Export utility is beginning to export data to file "e:\exporta\imag.ixf".
SQL3015N An SQL error "-452" occurred during processing.
SQL0452N Unable to access the file referenced by host variable "4". Reason code: "3". SQLSTATE=428A1
SQL3105N The Export utility has finished exporting "0" rows. [...]
8788 153 34_Calling DSNWZP in Rexx - Need Help18_Venegas, Francisco26_Francisco.Venegas@MBNA.COM31_Wed, 17 Jul 2002 09:55:47 -0400473_iso-8859-1 hello,
i am trying to make the following call via REXX:
------------------------------------------------------- SQLDA.SQLD = 1 SQLDA.1.SQLTYPE = 449 SQLDA.1.SQLLEN = 32000 SQLDA.1.SQLDATA = LEFT(' ',32000,' ') SQLDA.1.SQLIND = 0
EXECSQL CALL SYSPROC.DSNWZP USING DESCRIPTOR :SQLDA" -------------------------------------------------------
i am getting a good sqlcode back but i can't seem to get the data (parm) back from DSNWZP. [...]
8942 83 35_Re: NODYNAM in stored Procedures...13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 17 Jul 2002 09:01:39 -0500428_iso-8859-1 Ooops, I sent too early ... To complete the story ... we do not compile with NODYNAM, we do use dynamic linking. You should be able to switch the DSNALI to whatever language environment you want.
-----Original Message----- From: Mackey, Glenn [mailto:GMackey@GUIDEMAIL.COM] Sent: Wednesday, July 17, 2002 8:31 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: NODYNAM in stored Procedures... [...]
9026 33 48_Stored procedure Question / multiple collections13_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Wed, 17 Jul 2002 09:01:55 -0500648_us-ascii Hello fellow Listservers,
Here is the situation. We currently have multiple unit test environments within one subsystem with concurrent development going on. The problem we are having is a stored procedures when you define it you can only list one collection. We have tried to create the same stored procedure with a different internal name but same external name and that works but the developer has to change there call procedure statement when they want to test the stored procedure changes they made. How can we execute the same stored procedure using multiple collections to simulate multiple environments without needing [...]
9060 98 35_Re: NODYNAM in stored Procedures...0_19_Tim.Lowe@STPAUL.COM31_Wed, 17 Jul 2002 09:10:07 -0500349_us-ascii Raquel, I don't think that your understanding is correct. DYNAM/NODYNAM does effect called subroutines, but not called stored procedures since they execute in a different address space and are not part of your load module. Therefore, if a DB2 stored procedure is changed, then other programs that call it do nto need to be recompiled. [...]
9159 196 38_Re: Calling DSNWZP in Rexx - Need Help7_Ed Long19_rdhm99a@PRODIGY.NET31_Wed, 17 Jul 2002 07:36:35 -0700671_us-ascii Check on your maintenance status. This SP has been quality challenged on V7.1.
--- "Venegas, Francisco" wrote: > hello, > > i am trying to make the following call via REXX: > > ------------------------------------------------------- > SQLDA.SQLD = 1 > SQLDA.1.SQLTYPE = 449 > SQLDA.1.SQLLEN = 32000 > SQLDA.1.SQLDATA = LEFT(' ',32000,' ') > SQLDA.1.SQLIND = 0 > > EXECSQL CALL SYSPROC.DSNWZP USING DESCRIPTOR :SQLDA" > ------------------------------------------------------- > > i am getting a good sqlcode back but i can't seem to > get the data (parm) back from DSNWZP. > > any help would be appreciated. > > thanks in [...]
9356 25 0_15_Zimmerman Terry35_Terry.Zimmerman@GENERALCASUALTY.COM31_Wed, 17 Jul 2002 09:34:49 -0500397_iso-8859-1 Hello,
We currently have PeopleSoft Financials Version 8 installed using DB2 Version 7 on OS/390. I have a project plan to look at the separation of the PeopleSoft databases into another production DB2 subsystem. The objective is to gain higher availability with this product.
Has anyone done this either with PeopleSoft or anything else to gain higher availability? [...]
9382 31 37_DB2 Date Arithmetic Compared to COBOL13_Wolff, Martin31_Martin.Wolff@GLOBALCROSSING.COM31_Wed, 17 Jul 2002 10:54:58 -0400476_iso-8859-1 Hi everyone.
As part of the Y2K conversion, we acquired (from Platinum I believe) a date arithmetic program that converts to Y2K and many other date conversion functions. As with all things that work, people found many other uses for its date arithmetic functions and it is now used a lot. While Strobe'ing a heavy CPU program, I saw that 36% CPU was accounted in this sub program. While we don't have the source, I can see that it is written in COBOL. [...]
9414 101 8_Ugly sql45_=?iso-8859-1?Q?=22LeBlanc=2C_Andr=E9-ITB=22?=30_AndreD.Leblanc@CCRA-ADRC.GC.CA31_Wed, 17 Jul 2002 11:07:07 -0400376_iso-8859-1 I've inherited this sql, which times out when it deals with tables containing 79,000 rows each. It works fine in testing with 1000 row tables.
I believe the problem lies with temporary tables C and D which result in almost 79,000 rows each. These temporary tables are each scanned as part of left outer joins and I think this is where the problem lies. [...]
9516 17 30_REXX - Default Isolation Level12_Joe DeCastro14_JoeFDC@AOL.COM31_Wed, 17 Jul 2002 11:06:27 -0400524_iso-8859-1 Hello Listers,
I have a REXX DB2 question that I couldn't find an answer to after RTFM. We are running OS/390 2.10 and DB2 V6. We had a REXX exec fail after applying maintenance to DB2. A SET CURRENT PACKAGESET statement wasn't prefixed with an EXECSQL statement. The programmer bypassed the bad return code and the exec worked. I assume that it took a default isolation level (most likely RR). My question is what is the default isolation level if you don't issue a SET CURRENT PACKAGESET statement? [...]
9534 160 3_Re:14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 17 Jul 2002 16:30:01 +0100430_iso-8859-1 Terry,
Depending on how large the subsystem is, it might be quicker to create a duplicate of the subsystem and then drop from each copy the databases that now belong in the "other one".
This can be very quick and painless
The downsides to this approach include having high level dataset qualifiers that don't necessarily match the subsystem name (leading to potential confusion in the future) [...]
9695 61 41_Re: DB2 Date Arithmetic Compared to COBOL16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 17 Jul 2002 11:24:17 -0400429_iso-8859-1 We recently benchmarked this for :hostvariabledate - 30 days.
Results for 1,000,000 iterations:
DB2 SELECT from sysdummy1 used 2 MIN 31.42 sec of CPU DB2 SET :hv used 0 MIN 53.42 sec of CPU LE COBOL routine used 0 MIN 06.15 sec of CPU
We have not done a benchmark for anything like your 'last Saturday' example. I suspect an assmebler routine could reduce the results to perhaps 4-5 seconds. [...]
9757 67 38_Re: Problem with export command UDB NT15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US31_Wed, 17 Jul 2002 10:54:35 -0500465_- Do not qualify the lobfile (use "photo" instead of qualifying it as " e:\largeobj\photo"). You should be fine after that.
-----Original Message----- From: Luiz Cesar Vinhaes da Costa Junior [mailto:vinhaes@CAIXASEGUROS.COM.BR] Sent: Wednesday, July 17, 2002 8:57 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Problem with export command UDB NT
Hi listers
I'm trying to export some lines from a table and I'm geting the error masage [...]
9825 44 34_Re: REXX - Default Isolation Level11_David Nance16_DWNance@FHSC.COM31_Wed, 17 Jul 2002 12:01:42 -0400625_US-ASCII Joe, Wouldn't it use whichever isolation your plan is bound with?
Dave Nance First Health Services, Corp. (804)527-6841
>>> JoeFDC@AOL.COM 07/17/02 11:06AM >>> Hello Listers,
I have a REXX DB2 question that I couldn't find an answer to after RTFM. We are running OS/390 2.10 and DB2 V6. We had a REXX exec fail after applying maintenance to DB2. A SET CURRENT PACKAGESET statement wasn't prefixed with an EXECSQL statement. The programmer bypassed the bad return code and the exec worked. I assume that it took a default isolation level (most likely RR). My question is what is the default [...]
9870 130 20_Another SQL question11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Wed, 17 Jul 2002 08:59:11 -0700280_US-ASCII Sorry in advance for the long email.
I have a developer working on an SQL that I feel is not taking the correct index. It is a 3 table join and when I do the explain, it tells me it is taking index XSASI30D instead of XSASI30C which I thought it should take. [...]
10001 16 25_automatic rebind question17_Christophe Radier17_c300501@YAHOO.COM31_Wed, 17 Jul 2002 11:38:26 -0500514_- Hi all, A part from when you alter your table affecting to columns used by the program , which are the conditions where we can have an automatic rebind ? Is that mean that the VALID column of SYSPACKAGE/SYSPLAN is set to N (and operative = Y)
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.
10018 141 24_Re: Another SQL question16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 17 Jul 2002 13:02:57 -0400636_iso-8859-1 Why 'no firstkeycar or fullkeycard stats' on the index you want the optimizer to select?
Regards, eric pearson NS ITO Database Support
-----Original Message----- From: KATHY JONES [mailto:jonesks@GROUPWISE.CCSD.NET] Sent: Wednesday, July 17, 2002 11:59 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Another SQL question
Sorry in advance for the long email.
I have a developer working on an SQL that I feel is not taking the correct index. It is a 3 table join and when I do the explain, it tells me it is taking index XSASI30D instead of XSASI30C which I thought it should take. [...]
10160 38 34_Re: REXX - Default Isolation Level12_Joe DeCastro14_JoeFDC@AOL.COM31_Wed, 17 Jul 2002 13:04:08 -0400445_iso-8859-1 Dave,
REXX is dynamic SQL. It come with 4 packages: DSNREXRR, DSNREXRS, DSNREXCS, and DSNREXUR. You pick you isolation level by doing a set current packageset to one of the 4 packages.
Joe
----------------------------- Joe, Wouldn't it use whichever isolation your plan is bound with?
Dave Nance First Health Services, Corp. (804)527-6841
>>> JoeFDC@AOL.COM 07/17/02 11:06AM >>> Hello Listers, [...]
10199 45 29_Re: automatic rebind question10_Rick Davis18_RDavis8727@AOL.COM29_Wed, 17 Jul 2002 13:19:28 EDT368_US-ASCII A couple more are dropping the table(space) or an index and recreating them.
Regards, Rick
> Hi all, > A part from when you alter your table affecting to columns used by the > program , which are the conditions where we can have an automatic rebind ? > Is that mean that the VALID column of SYSPACKAGE/SYSPLAN is set to N > (and operative = Y) >
10245 24 30_Composite Partition Key Design29_ISRAEL SIRISH David Alexander20_DIsrael@COVANSYS.COM31_Wed, 17 Jul 2002 13:35:16 -0400546_- Hi List, We have DB2 Ver 7.0 on S/390. We have Huge data (> 100,000 records) coming in, on weekly basis. From every state we get the product details with unique product ID. We have decided to go for a Partition Tablespace to store the state wise and Product wise Data. This is can be perceived as Two dimensional matrix. I want to have partitions as (state, Product ID) combination as each state has got more than one product sale. For example if the data from TN with product ID 12345 should go into one partition. Similarly data from TN [...]
10270 174 24_Re: Another SQL question11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Wed, 17 Jul 2002 10:37:01 -0700373_US-ASCII I am a fairly new DBA so I don't know why - I even reorged the index and ran runstats and still no firstkeycard or fullkeycard stats. If runstats is not how I get firstkeycard or fullkeycard, how do I get them?
Kathy Jones Central Information Services Clark County School District O/S390 DB2 DBA NT DB2 DBA 702-799-5040 x366 jonesks@GroupWise.ccsd.net [...]
10445 178 24_Re: Another SQL question16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 17 Jul 2002 13:58:31 -0400526_iso-8859-1 Try looking in FIRSTKEYCARDF and FULLKEYCARDF.
Regards, eric pearson
-----Original Message----- From: KATHY JONES [mailto:jonesks@GROUPWISE.CCSD.NET] Sent: Wednesday, July 17, 2002 1:37 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Another SQL question
I am a fairly new DBA so I don't know why - I even reorged the index and ran runstats and still no firstkeycard or fullkeycard stats. If runstats is not how I get firstkeycard or fullkeycard, how do I get them? [...]
10624 149 22_Re: Separate Subsystem15_Zimmerman Terry35_Terry.Zimmerman@GENERALCASUALTY.COM31_Wed, 17 Jul 2002 13:02:45 -0500565_iso-8859-1 Thanks for the comment Phil. Has anyone moved a system into it's own DB2 subsystem to gain higher availability? I question if it will buy us anything.
Terry
-----Original Message----- From: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Sent: Wednesday, July 17, 2002 10:30 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re:
Terry,
Depending on how large the subsystem is, it might be quicker to create a duplicate of the subsystem and then drop from each copy the databases that now belong in the "other one". [...]
10774 62 46_Help with DB2 Information / Insert data to LOB22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Wed, 17 Jul 2002 13:02:03 -0500398_- Dear Listeners,
I was looking up for help thru' DB2 Information regarding inserting data into table with CLOB columns and its not working. I see this link below:
Application Development Guide - Example: Inserting Data Into a CLOB Column
(0.9200)
But, when I click on it, it says page cannot be displayed and I also get this message when I try it the second time: [...]
10837 72 47_Re: DSNT500I, resource unavailable, during LOAD12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 17 Jul 2002 21:58:17 +0200532_- For DSNDB01 /06 /07 they are defined while creating the subsystem. For user TS you define them in the CREATE TABLESPACE command.
Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@LISTSERV.YLASSOC.COM] On Behalf Of Giorgio De Nunzio Sent: Wednesday, July 17, 2002 12:48 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: DSNT500I, resource unavailable, during LOAD [...]
10910 78 52_Re: Stored procedure Question / multiple collections13_BL.Tink Tysor16_tink@BLTYSOR.COM31_Wed, 17 Jul 2002 12:14:01 -0700435_us-ascii Robert,
I believe that a way to do this is: if you have many version of a database in the same DB2 subsystem, distinguished by qualifier at bind time, then if you want a separate version of the SP for each qualifier, you must have separate WLM Environment for each version pointed to by the Schema in SYSROUTINES. Different WLM Environments will have to point to separate load libraries in the WLM jcl in proclib. [...]
10989 23 47_Re: DSNT500I, resource unavailable, during LOAD17_Giorgio De Nunzio25_giorgio.denunzio@UNILE.IT31_Wed, 17 Jul 2002 21:15:15 +0200323_US-ASCII Thanks Bob and Yassin again! I have simply added the prqty and secqty parameters to CERATE TABLESPACE, I have put some big values just for a test (50 MB/10 MB) and it seems to be working now.
Of course I am interested in getting a tool which can estimate the needed space: I'll look for it on the Web. [...]
11013 20 29_Rexx call to Stored Procedure17_Robert J. Milonas20_xrjmbob@NETSCAPE.NET31_Wed, 17 Jul 2002 15:29:04 -0400534_iso-8859-1 I remember a few months ago that someone posted an example of a rexx program calling a stored procedure. I'd appreciate it if the person would post the example again.
Thanks
__________________________________________________________________ Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ [...]
11034 123 33_Re: Rexx call to Stored Procedure0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 17 Jul 2002 14:43:24 -0500376_us-ascii Here's one of mine... (SQLPROB is a routine that someone else posted a while back, to display key SQLCA info.)
/* The next line invokes an assembler routine which clears the screen */
"UT1CLEAR"
SUBCOM DSNREXX /* SET UP HOST ENVIRONMENT */ IF RC THEN /* IS HOST COMMAND THERE? */ X = RXSUBCOM('ADD','DSNREXX','DSNREXX') /* NO - CREATE IT */ [...]
11158 105 52_Re: Stored procedure Question / multiple collections11_David Nance16_DWNance@FHSC.COM31_Wed, 17 Jul 2002 15:44:38 -0400546_US-ASCII Robert, The way we are doing this so far has been to have a different external name and the same internal name. This way you do not have to change every program that makes a call to the stored proc, just have multiple copies of your load. Thankfully, we have WLM now and are setting up a SPA for each development region. With the WLM managed SPA's you can, at last, have the same internal name pointed at the same external name and have your load libs concatenated so that you either use the development region or higher version of [...]
11264 103 16_Re: SQL question14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Wed, 17 Jul 2002 21:35:54 +0200501_- Hi Dave and Terry,
thanks for your answers.
Dave's solution is the kind of trick I was looking for. But in fact, in the meantime, I wrote a C function that does it in the way I've outlined yesterday. The result has to be put in an ISPF table, and I can manage it that way. The user does not see the C looping behind it. Works ok for me, and I can stop after getting 200 records, which is ok for our users. The queries are index only, no sort, so I have 2 seconds or less. This is [...]
11368 174 24_Re: Another SQL question11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Wed, 17 Jul 2002 13:02:40 -0700351_US-ASCII After looking in the sysibm table directly instead of using the PRF query, the firstkeycard is 100 and 112994 for the B index. So any reasons why it is taking the C index over the B index?
Kathy Jones Central Information Services Clark County School District O/S390 DB2 DBA NT DB2 DBA 702-799-5040 x366 jonesks@GroupWise.ccsd.net [...]
11543 14 11_CURRENTDATA13_Jeremiah Eden28_JEREMIAH.EDEN@RADIOSHACK.COM31_Wed, 17 Jul 2002 15:21:06 -0500462_- Does anyone know how to change the BIND default of CURRENTDATA(YES) to CURRENTDATA(NO). I could not find ZPARM value for this on a Search of the DB2 manuals. Or, it is just not showing up under that name.
================================================ 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.
11558 79 52_Re: Stored procedure Question / multiple collections13_Michalik, Ken19_kmichalik@KRAFT.COM31_Wed, 17 Jul 2002 16:02:01 -0500614_iso-8859-1 Best Way: Implement WLM.
Possible way:
Define schema names for each environment. Set up the stored procedure definition for each environment- CREATE PROCEDURE schema.name....with the collections that correspond to the proper environments.
When binding the caller, use the PATH(SYSIBM,SYSFUN,,SYSPROC) bind option. Use the environment you need. The schema applies to all unqualified procedure calls in the caller, just like a QUALIFIER(...) applies to all uqualified table references. The caller then calls the right version of the stored procedure package. [...]
11638 41 37_Instance/Database Naming Standards...15_Janardhan Ragam16_sragam@YAHOO.COM31_Wed, 17 Jul 2002 14:19:29 -0700428_us-ascii Hello DB2 List Members,
I wanted to know if anyone has implemented naming standards for db2 instances/database on servers? If so can you share the details? My question is more in terms of how you do it - by deprtmant, application, function etc. OR do most of you go with the default instance names like db2inst1, db2inst2 etc. and create database names that are more in line with application name/function. [...]
11680 12 15_Startup Scripts15_Stephen Poulsen36_stephen.r.poulsen@CO.MULTNOMAH.OR.US31_Wed, 17 Jul 2002 16:06:15 -0500429_- We use DB2/UDB v7.2 on Unix/Solaris. Where are the script(s) located that are used to do instance and database startup following a power off/power on of a database server?
================================================ 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.
11693 50 15_Re: CURRENTDATA0_19_csutfin@AMSOUTH.COM31_Wed, 17 Jul 2002 16:35:20 -0500715_us-ascii For OS/390
The CURRENTDATA parameter is part of the BIND statement.
Carol Sutfin Corporate DBA AmSouth Bank (205)326-5214 Fax:(205)326-5613 csutfin@amsouth.com
Jeremiah Eden cc: Sent by: DB2 Data Subject: CURRENTDATA Base Discussion List
07/17/02 03:21 PM Please respond to DB2 Data Base Discussion List
Does anyone know how to change the BIND default of CURRENTDATA(YES) to CURRENTDATA(NO). I could not find ZPARM value for this on a Search of the DB2 manuals. Or, it is just not showing up under that name. [...]
11744 16 49_Leon Katsnelson/Toronto/IBM is out of the office.0_15_leon@CA.IBM.COM31_Wed, 17 Jul 2002 17:50:37 -0400440_us-ascii I will be out of the office starting July 17, 2002 and will not return until July 19, 2002.
I will respond to your message when I return. Shili Yang will be my back up.
================================================ 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.
11761 57 41_Re: Instance/Database Naming Standards...14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Wed, 17 Jul 2002 14:52:29 -0700412_iso-8859-1 well the short answer is yes department, function or application - it depends really on the data and the standards for that area
as for the second, I try to give them semi meaningful names where I have a choice to do so
Two or more applications may certainly share a common table/database; this is most likely a Read (or SELECT) only table and therefore contention is not an issue. [...]
11819 20 41_Re: cursor sometimes sensitive to inserts12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 17 Jul 2002 19:17:13 -0500567_- Ask your friend if the EXPLAIN for V6 shows a temporary file or query materialization, while the V7 plan avoids using the temporary. If that is so, then this is the experience customers have had in V2, V4, V6, ...
There are quite a few techniques that do require a temporary today, but the optimizer may get smarter tomorrow, be faster, and avoid the temporary. You need to distinguish between the documented behavior and the way it happens to work today. If you use a "dirty trick", then you have additional checking to do when you migrate to the next [...]
11840 55 24_Re: Another SQL question14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 18 Jul 2002 11:21:41 +1000715_US-ASCII Kathy,
Fullkeycard for indexes B and D disagree. Try running a full runstats and seeing what happens.
RUNSTATS TABLESPACE TABLE(SSASIDB1.AMST_MSTR_SCHEDULE) INDEX(ALL) SHRLEVEL CHANGE
For this purpose there is no need to, but you might for direct access to this table want to add COLUMN( Schoolnum, SEctionID, Classlink, course)
James Campbell
> -----Original Message----- > From: KATHY JONES [mailto:jonesks@GROUPWISE.CCSD.NET] > Sent: Wednesday, July 17, 2002 11:59 AM > To: DB2-L@LISTSERV.YLASSOC.COM > Subject: Another SQL question > > > Sorry in advance for the long email. > > I have a developer working on an SQL that I feel is not [...]
11896 94 58_Re: AW: questions regarding the Oracle Transparent Gateway14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 18 Jul 2002 11:29:30 +1000573_US-ASCII Since Martin hasn't responded, I'll take a wild guess and suggest that OTG uses PREPARE stmt FROM "SELECT * FROM " DESCRIBE stmt which probably is less efficient than DESCRIBE TABLE as it goes through access path selection.
James Campbell
On 17 Jul 2002 at 15:56, Raymond Bell wrote:
> Hi Martin, > > You are a winner! I set DB2DESCTAB = NO as you suggested and Bingo! OTG > 4.0.1.1 can talk to DB2 V7! Yee ha! I like your bind options. We've > rebound the OTG plan iso(ur) as well, but probably won't make the Oracle2pc > [...]
11991 57 52_Re: Stored procedure Question / multiple collections14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 18 Jul 2002 12:26:43 +1000606_US-ASCII If you really want to stay with non-WLM SPAS then you can specify NO COLLID, so that the caller's collection-id is used by the SP.
However, that can still leave a problem if you want to run two versions of the SP code in a single subsystem. If you want to do this, you have to - have a different external/internal name for each version of the SP - use http://listserv.ylassoc.com/cgi/wa.exe?A2=ind0001D&L=DB2-L&D=0&P=3893 to determine the current qualifer (ie current environment) - determine the SP name to call based on the current qualifier - use the CALL :hostvar form to call the [...]
12049 62 50_Re: Help with DB2 Information / Insert data to LOB14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 18 Jul 2002 12:26:44 +1000457_US-ASCII I'ld be a little wary of using a DB2 for Distributed platforms manual to determine DB2 for OS/390 techniques. And, while the concept behind it is good, I've had many linkage failures accessing the DB2 for DP manuals through help.
If all else fails http://www-3.ibm.com/cgi- bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d2w/en_main
James Campbell
On 17 Jul 2002 at 13:02, Venkatesh Mokshagundam wrote: [...]
12112 168 41_Re: cursor sometimes sensitive to inserts14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 18 Jul 2002 12:26:44 +1000542_US-ASCII What are the access paths under V6 and V7? Are they the same?
On 16 Jul 2002 at 21:19, Nosey Thimma wrote:
> My friend also is facing a similar problem. But it is > slightly different. His application program (query) > gets into an infinite loop when the program is run > under V7 (non-scrollable cursor). But it runs as > expected when it is run under V6. Has anybody faced > this problem? > > --- Isaac Yassin wrote: > > Just a hypothesis - could be wrong: > > > > Do you commit every 100 [...]
12281 56 34_Re: Composite Partition Key Design14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 18 Jul 2002 12:26:45 +1000370_US-ASCII Hmm, 50 states (I presume you're talking USA here - what about Canadian provinces, Australian states :-p), how many products - more than 5 I'ld guess, that means more than 254 partitons (DB2's limit). That means that you cannot have a partition for each state/product combination At least some partitions will have to store more than one such combination. [...]
12338 38 19_Re: Startup Scripts11_Grant Allen22_Grant@TOWERSOFT.COM.AU31_Thu, 18 Jul 2002 12:26:54 +1000361_- Stephen Poulsen[SMTP:stephen.r.poulsen@CO.MULTNOMAH.OR.US] wrote: > >We use DB2/UDB v7.2 on Unix/Solaris. Where are the script(s) located that > >are used to do instance and database startup following a power off/power > on > >of a database server? > Look in your inittab file - it'll have an entry for autostarting db2, and you can track it from there. [...]
12377 61 19_Re: Startup Scripts19_Michael Piontkowski34_michael_piontkowski@COMPUSERVE.COM31_Wed, 17 Jul 2002 23:08:51 -0400796_iso-8859-1 I'm not familiar with Unix/Solaris but on AIX we don't have an inittab entry. Our AIX administrator invokes the db2 startup script in the /etc/rc.local script.
Mike Piontkowski
-----Original Message----- From: owner-db2-l@LISTSERV.YLASSOC.COM [mailto:owner-db2-l@LISTSERV.YLASSOC.COM]On Behalf Of Grant Allen Sent: Wednesday, July 17, 2002 22:27 To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: [DB2-L] Startup Scripts
Stephen Poulsen[SMTP:stephen.r.poulsen@CO.MULTNOMAH.OR.US] wrote: > >We use DB2/UDB v7.2 on Unix/Solaris. Where are the script(s) located that > >are used to do instance and database startup following a power off/power > on > >of a database server? > Look in your inittab file - it'll have an entry for autostarting db2, [...]
12439 172 35_Re: NODYNAM in stored Procedures...16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Wed, 17 Jul 2002 20:41:55 -0700585_us-ascii Thanks for the inputs Tim. But probably owing to my lack of knowledge, your answer has raised more questions:
1. Simply put, the way I understand NODYNAM is that if a program P1 CALLS program P2 and program P1 has been compiled with NODYNAM, all external references (P2 in this case) must be resolved at the time of compilation and hence, the load module of P2 will 'contain' P1. If this is true, how does it matter if stored procedures run in a separate address space? If stored Procedure SP1 (compiled with NODYNAM option) CALLs Stored Procedure SP2, then would [...]
12612 123 35_Re: NODYNAM in stored Procedures...16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Wed, 17 Jul 2002 20:43:51 -0700357_us-ascii Hello Glen,
Thanks for your insight on this. Actually, the way I understood it was that all applications using CAF (DSNALI) 'have' to use NODYNAM. As 'DB2-established' stored procedures use CAF, they have to use NODYNAM too. So, could you explain what you mean by 'use of NODYNAM relates to the fact that DSNHLI is an alias to DSNELI'. [...]
12736 44 19_Re: Startup Scripts11_Grant Allen22_Grant@TOWERSOFT.COM.AU31_Thu, 18 Jul 2002 14:10:55 +1000601_- Michael Piontkowski[SMTP:michael_piontkowski@COMPUSERVE.COM] wrote: > >I'm not familiar with Unix/Solaris but on AIX we don't have > >an inittab entry. Our AIX administrator invokes the db2 startup > >script in the /etc/rc.local script. > > > > > >Mike Piontkowski > On Solaris, you'll probably have entry in inittab for runlevels 2, 3 and 4 pointing to rc.db2. rc.db2 then calls db2istrt. From memory, this is put there by default by the installer. Can't say I've looked at AIX for many years (not from any prejudice - our poor old RS/6000 died several years back, and we never got the budget [...]
12781 174 35_Re: NODYNAM in stored Procedures...14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 18 Jul 2002 14:49:18 +1000397_US-ASCII Raquel
Calls to DSNALI only "have" to use NODYNAM because IBM was too lazy to construct suitable linkage/load mechanisms to construct the mechanism that Glen described. In later manuals I seem to the recall the process being described.
Calls to DSNCLI, however, must use NODYNAM because the linkage mechanism that DYNAM generates just won't work in a CICS environment. [...]
12956 20 20_Re: SQLJ Access path12_Simon George26_simon_x_george@HOTMAIL.COM31_Thu, 18 Jul 2002 07:56:32 +0100
12977 54 47_AW: DSNT500I, resource unavailable, during LOAD41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Thu, 18 Jul 2002 09:40:47 +0200575_iso-8859-1 Giorgio,
depending on what version your are on ....
Obtaining DB2 Estimator DB2 Estimator is one of several features and tools offered with DB2 Universal Database Server for OS/390 Version 6:
you might try http://www-3.ibm.com/software/data/db2/os390/v6facts/estimate.html
Regards Ruediger
-----Ursprüngliche Nachricht----- Von: Giorgio De Nunzio [mailto:giorgio.denunzio@UNILE.IT] Gesendet: Mittwoch, 17. Juli 2002 21:15 An: DB2-L@LISTSERV.YLASSOC.COM Betreff: Re: DSNT500I, resource unavailable, during LOAD [...]
13032 92 34_Re: REXX - Default Isolation Level10_Sysdba AHE18_Sysdba.AHE@TNT.COM31_Thu, 18 Jul 2002 09:39:32 +0100425_us-ascii Dave/Joe,
In our system REXX has ONE package bound into 5 collections, and a Plan with PKLIST(DSNREXX.DSNREXX, DSNREXUR.DSNREXX, DSNREXCS.DSNREXX, DSNREXRS.DSNREXX, DSNREXRR.DSNREXX).
The default if CURRENT PACKAGESET is blank (not set) is to use the first one in the list, DSNREXX.DSNREXX, which has ISOLATION(CS) in our system.
HTH
Neil Price IS Central Systems TNT Post Group, UK [...]
13125 231 19_SQLCODE translation13_Ealey, Paul M18_paul.ealey@EDS.COM31_Thu, 18 Jul 2002 11:51:01 +0100456_-
I'm presented with :-
SQLCODE = 00000090M
Which I'm told "we just know is a -904".
Can anyone tell me how to trsanslate this please?
Cheers - Paul
================================================ 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.
13357 52 23_Re: SQLCODE translation14_Dash, Sushanta21_sushanta.dash@EDS.COM31_Thu, 18 Jul 2002 06:37:17 -0500513_iso-8859-1 Once you go for HEX of these values would get correct values. Play with HEX OFF and ON.
-----Original Message----- From: Ealey, Paul M Sent: Thursday, July 18, 2002 4:21 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: SQLCODE translation
I'm presented with :-
SQLCODE = 00000090M
Which I'm told "we just know is a -904".
Can anyone tell me how to trsanslate this please? [...]
13410 18 23_Re: SQLCODE translation34_matthias.wehrens@amb-informatik.de34_matthias.wehrens@AMB-INFORMATIK.DE31_Thu, 18 Jul 2002 06:23:42 -0500294_- The variable containing the SQLCODE obviously was declared without a sign, e.g. in COBOL as PIX 9(9) - declaring it as PIC -(9)9 will give a correct result.
If you examine 0000090M in hex, you will see in the last Byte "D4" - and "D" indicates a negative sign, whereas "4" is "4" [...]
13429 48 23_Re: SQLCODE translation12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Thu, 18 Jul 2002 07:43:07 -0400451_us-ascii Paul-
Look at the code in hex. You will see: 90M FFD4444 9040000
HTH!
Mark Anzmann Tech Services Manager DHRIS Program - Team Accenture Phone: 410-238-1213 email: manzmann@dhr.state.md.us
>>> paul.ealey@EDS.COM 07/18/02 06:51AM >>>
I'm presented with :-
SQLCODE = 00000090M
Which I'm told "we just know is a -904".
Can anyone tell me how to trsanslate this please? [...]
13478 17 28_DDF is taking 20% of our CPU0_29_WilliamsByron@RUSSELLCORP.COM31_Thu, 18 Jul 2002 07:34:01 -0500575_iso-8859-1 Did Rob get an answer to this question?
From: Robert Ord Subject: DDF not behaving? We are currently running DB2 V5.1 on OS/390 using TCPIP. 1) DDF on the mainframe consuming huge amounts of cpu even when no remote queries are running causing the mainframe to slow drastically.
================================================ 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.
13496 149 23_Re: SQLCODE translation22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Thu, 18 Jul 2002 10:30:04 -0300393_iso-8859-1 In addition to the SQLCODE variable I always advise people to display SQLERRMC, so that they can see a text message and some important tokens associated to the error.
Regards, Claudio **************************************** Claudio Meluzzi Mendes BANCO BEG S.A. Goiania - Goias - BRAZIL claudio@beg.com.br cmeluzzi@uol.com.br ****************************************
13646 163 16_Re: SQL question13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 18 Jul 2002 08:33:04 -0500299_iso-8859-1 Bernd,
Sorry about the dud answer. I was thinking that maybe table2 was a child of table1 and thus a full join would give you the same result. The full join was to force a merge scan join as it was implied from your first mail that you were repeatedly scanning (nested loop). [...]
13810 41 33_Re: Rexx call to Stored Procedure11_Daniel Adam16_dadam@GRATEX.COM31_Thu, 18 Jul 2002 08:15:29 -0500357_- Robert, I just posted my general purpose rexx script for calling a stored procedure. On the windows version of DB2 there is DB2 Command Line Processor that will interpret your CALL statements and I needed something similar on OS/390 - I use the rexx script for that. The subject of the posting is "Rexx calling a stored procedure". Good luck, Daniel [...]
13852 58 75_Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes?12_Hayden Jones25_Hayden_Jones@GRAINGER.COM31_Thu, 18 Jul 2002 08:38:35 -0500324_us-ascii Not being very bright, I am sometimes challenged by syntax diagrams. One example is the runstats systax diagram. I have made various attempts in the past at coding FREQVAL and KEYCARD parameters. I always get error messages. I have found no examples in IBM manuals or Mullins book or anywhere else. I gave up. [...]
13911 186 38_Re: Calling DSNWZP in Rexx - Need Help12_Boone, Henry16_HBoone@GEICO.COM31_Thu, 18 Jul 2002 09:58:50 -0400388_iso-8859-1 Try playing with your MON ZPARM. For example, I've found (through trial and error) that I get positive results when I set MON=NO if the DB2 is on an LPAR that doesn't have Omegamon for DB2 running. MON=YES works for the DB2s that reside on an LPAR that does have Omegamon running. Of course the Omegamon thing could simply be a coincidence, as I've not followed up on it. [...]
14098 152 24_Re: Another SQL question13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 18 Jul 2002 09:04:25 -0500577_us-ascii Kathy,
The problem maybe due to the clusterratio of C. Trying setting it to 81 or above. You could also try for index-only by adding section_id to the C index (but that wont guarantee uniqueness on the first 2 columns).
A few other points: - Is table B redundant or mandatory to ensure only certain schoolnum values are returned? - You can only use an index on the first table accessed if you have local predicates that can be applied to the leading index columns, such as entercode and/or grade. - You may wish to promote stage 2 SUBSTR predicates [...]
14251 163 35_Re: NODYNAM in stored Procedures...14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Thu, 18 Jul 2002 07:07:35 -0700434_iso-8859-1 Raquel, The stored procedures would NOT have to recompiled or relinked. NODYAM requires that all routines invoked via the COBOL CALL statement must be link edited with the calling program. However, a SQL CALL statement is routed through the DBMS and will invoke the stored procedure load module directly. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...]
14415 157 52_Re: Stored procedure Question / multiple collections14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Thu, 18 Jul 2002 07:11:58 -0700438_iso-8859-1 One method would to have the stored procedure always have the same name, but use a different schema then have the program retreive the correct schema to use (through a parm file etc.) then issue a SET CURRENT PATH statement to add the schema, then issue the CALL statement with an unqualified procedure name. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...]
14573 90 75_Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 18 Jul 2002 09:13:46 -0500613_us-ascii Hayden,
Checkout this excellent article:
http://www-1.ibm.com/support/manager.wss?rs=64&rt=0&org=SW&doc=1023318
Regards 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
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@LISTSERV.YLASSOC.COM]On Behalf Of Hayden Jones Sent: Thursday, July 18, 2002 8:39 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes? [...]
14664 185 22_Re: Separate Subsystem13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Thu, 18 Jul 2002 09:16:08 -0500645_iso-8859-1 Just curious -- how will moving it to it's own DB2 subsystem gain higher availability? Are you having issues where "other" applications are causing the subsystem to crash? Would you do something differently (from an availability standpoint) in the new subsystem that you couldn't do currently?
Since you mentioned Peoplesoft specifically - the topic of Peoplesoft dedicated subsystems has gone around a couple times in the past -- you might want to check the archives, but the bottom line is that there's a bunch of people doing this both ways (isolating Peoplesoft, and keeping it with "other" applications). We actually [...]
14850 98 75_AW: Very simple SQL problem: Could this SQL be made to run under 2 minutes?41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Thu, 18 Jul 2002 16:20:52 +0200456_iso-8859-1 Hi,
I use the following statements and they work fine for us :
RUNSTATS TABLESPACE dbname.tsname TABLE(ALL) INDEX (ALL KEYCARD FREQVAL NUMCOLS 1 COUNT 10 FREQVAL NUMCOLS 2 COUNT 10 FREQVAL NUMCOLS 3 COUNT 10 FREQVAL NUMCOLS 4 COUNT 10 FREQVAL NUMCOLS 5 COUNT 10 FREQVAL NUMCOLS 6 COUNT 10 FREQVAL NUMCOLS 7 COUNT 10 FREQVAL NUMCOLS 8 COUNT 10 FREQVAL NUMCOLS 9 COUNT 10 FREQVAL NUMCOLS 10 COUNT 10) SHRLEVEL CHANGE REPORT YES [...]
14949 94 75_Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes?24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Thu, 18 Jul 2002 07:23:50 -0700572_- This is what I use.
RUNSTATS TABLESPACE CTSA.EVT02 TABLE ALL INDEX(ALL KEYCARD FREQVAL NUMCOLS 1 COUNT 10 FREQVAL NUMCOLS 2 COUNT 10 FREQVAL NUMCOLS 3 COUNT 10 FREQVAL NUMCOLS 4 COUNT 10 FREQVAL NUMCOLS 5 COUNT 10 FREQVAL NUMCOLS 6 COUNT 10 FREQVAL NUMCOLS 7 COUNT 10 FREQVAL NUMCOLS 8 COUNT 10 FREQVAL NUMCOLS 9 COUNT 10 FREQVAL NUMCOLS 10 COUNT 10 FREQVAL NUMCOLS 11 COUNT 10 FREQVAL NUMCOLS 12 COUNT 10 FREQVAL NUMCOLS 13 COUNT 10 FREQVAL NUMCOLS 14 COUNT 10 FREQVAL NUMCOLS 15 COUNT 10 FREQVAL NUMCOLS 16 COUNT 10) UPDATE(ALL) REPORT NO SHRLEVEL(CHANGE) [...]
15044 218 62_Re: Large Data in Table causing Problem - Temporary Tablespace14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 18 Jul 2002 09:23:50 -0500363_iso-8859-1 Ali:
OK, I'm a little confused -- can you give us the exact text of the query you're using?
And what do you mean by "fetch data from 2 or more table"??? Are you doing this serially, or via UNION, or how?
Can you give us the DDL you used to create the System temporary tablespace? How big is the bufferpool you assigned it to? [...]
15263 116 29_Re: automatic rebind question14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 18 Jul 2002 15:31:54 +0100502_iso-8859-1 In fact altering ANYTHING that might have an impact on the access path(s) chosen by DB2.
Others that spring to mind include (I think)
- Changing partition limit keys - Dropping ANY dependent objects (triggers, UDFs etc) - Altering/dropping any objects related (with DB2 RI) to objects used in this plan
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 [...]
15380 217 22_Re: Separate Subsystem14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 18 Jul 2002 15:34:31 +0100404_iso-8859-1 OK
Try a related question
"How often is the current subsystem unavailable"?
If it is almost ALWAYS available, then you are right - a separate subsystem probably doesn't buy you anything.
BUT, I was asked to isolate applications in their own subsystems to isolate them from each other, so some idiots bad SQL doesn't kill other applications as well as his own! [...]
15598 47 35_Re: Stored procedures usefulness...11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Thu, 18 Jul 2002 09:37:50 -0500505_iso-8859-1 We're in the process of implementing our first stored procedure to be used by an application. It will be called by CICS transactions and will write error information to a sequential dataset. This provides a good log of problems and is easy for everyone to see.
Tina Hilton Bertelsmann mediaSystems
-----Original Message----- From: Mike Deer [mailto:Michael.Deer@UAL.COM] Sent: July 12, 2002 9:44 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Stored procedures usefulness... [...]
15646 98 74_Re: Very simple SQL problem: Could this SQL be made to run under2 minutes?11_David Nance16_DWNance@FHSC.COM31_Thu, 18 Jul 2002 10:46:41 -0400598_US-ASCII Hayden, Try something along these lines:
RUNSTATS TABLESPACE DATABASE.TABLESPACE TABLE(CREATOR.TABLENAME) COLUMN(ALL) INDEX(ALL KEYCARD FREQVAL NUMCOLS 1 COUNT 10)
I suggest changing the numcols to however many columns you have in your index, some people prefer to limit that though. The count is to get info on the most frequent values you can bump that up if you like, depending on the table I like to use up to 25 and on some of the small high use tables I bump that up to 50. I left out the other info you need such as whether to update and shrlevel, etc... [...]
15745 160 12_Re: Ugly sql13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 18 Jul 2002 09:53:12 -0500506_iso-8859-1 Andre,
I'm surprised there wasn't a suggestion to have only 1000 rows in the tables, since this works for you.
The problem with trying to give you a solution is knowing what we are trying to solve. The performance issue may be due to poor indexing, the EXISTS check may have a large outer table and small inner (which would make a non-correlated IN a better choice), or DB2 may be choosing a nested loop join and suffering sequential suffocation (or death by sequential I/O). [...]
15906 107 35_Re: NODYNAM in stored Procedures...0_19_Tim.Lowe@STPAUL.COM31_Thu, 18 Jul 2002 10:10:16 -0500618_us-ascii Raquel, Thanks to James Campbell, I think most of your questions are already answered, right? But, you also asked exactly what I meant by a "separate library", and exactly what the linkedit statements in my example do. This jcl was really intended for a DB2 systems programmer, who installs and maintains the DB2 system software. If that is not your responsibility, then perhaps you should forward this on to them. If they decide to use this, then they would be accepting an additional responsibility to maintain a new library when maintenance is applied. But, assuming that this is your responsibility, [...]
16014 221 29_Re: automatic rebind question14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 18 Jul 2002 10:11:16 -0500450_iso-8859-1 Stealing (I mean copying) liberally from the "DB2 Application Programming and SQL Guide":
--------------------------------------------------------------------------------- In most cases, DB2 marks a plan or package that needs to be automatically rebound as invalid. A few common situations in which DB2 marks a plan or package as invalid are:
When a table, index, or view on which the plan or package depends is dropped [...]
16236 95 50_Re: Help with DB2 Information / Insert data to LOB22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Thu, 18 Jul 2002 10:16:38 -0500531_- Thanks, James. I think I got the answer I was looking for.
-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: Wednesday, July 17, 2002 9:27 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Help with DB2 Information / Insert data to LOB
I'ld be a little wary of using a DB2 for Distributed platforms manual to determine DB2 for OS/390 techniques. And, while the concept behind it is good, I've had many linkage failures accessing the DB2 for DP manuals through help. [...]
16332 60 75_Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes?12_Hayden Jones25_Hayden_Jones@GRAINGER.COM31_Thu, 18 Jul 2002 10:50:13 -0500356_us-ascii Due to my advanced age and diminished mental capacity, I am sometimes challenged by syntax diagrams. One example is the runstats systax diagram. I have made various attempts in the past at coding FREQVAL and KEYCARD parameters. I always get error messages. I have found no examples in IBM manuals or Mullins book or anywhere else. I gave up. [...]
16393 151 22_Re: Separate Subsystem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 18 Jul 2002 10:51:47 -0500364_us-ascii Hmmm... Every once in awhile, I've gotten a request to create a separate DB2 subsystem for a single application system. The reasons for wanting it vary: (a) we don't want other DB2 applications interfering with our performance, (b) we don't want our application interfering with others', or (c) [my favorite] we need SYSADM authority over our stuff. [...]
16545 19 22_Re: Separate Subsystem0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 18 Jul 2002 11:13:09 -0500436_us-ascii BUT, I was asked to isolate applications in their own subsystems to isolate them from each other, so some idiots bad SQL doesn't kill other applications as well as his own!
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning". Mark Mischler. [...]
16565 56 15_Re: CURRENTDATA14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 18 Jul 2002 11:18:23 -0500300_us-ascii The only place I know of where you can change CURRENTDATA from YES to NO is in the DSNH clist. You can also modify the settings for the variables &DSNEYV7A and &DSNEBV7A in DB2I panels DSNEBP10 and DSNEBP11 (library SDSNPFPE) to use "NO" for "Data Currency" instead of "YES" or "SAME". [...]
16622 15 32_Toni Addie is out of the office.10_Toni Addie26_Toni_Addie@PROGRESSIVE.COM31_Thu, 18 Jul 2002 12:22:08 -0400403_us-ascii I will be out of the office starting 07/18/2002 and will not return until 07/30/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.
16638 45 37_DSNDB07 bufferpool and data set sizes14_Mark McCormack27_mamccormack@STATESTREET.COM31_Thu, 18 Jul 2002 12:26:00 -0400318_us-ascii DB2v6 OS/390 v2.10 I seek advice on sizing DSNDB07. We have an application that is growing by quantum leaps and bounds. It appears that everything associated with DSNDB07 is undersized. The question is how to increase things. Does anyone have useful advice and / or rules of thumb for handling DSNDB07 ? [...]
16684 22 41_Re: DSNDB07 bufferpool and data set sizes0_26_truman.g.brown@VERIZON.COM31_Thu, 18 Jul 2002 12:46:55 -0400508_us-ascii The biggest bang for the buck will probably be to increase the size of the dedicated DSNDB07 bufferpool. Also, you may need to increase SORTWORK to reduce the number of sort strings being written to DASD, e.g., sorts performed in storage instead.
VPSEQT of 98 usually works well for a DSNDB07 bufferpool, and try DWQT of 45 and VDWQT of 15. A hiperpool may or may not work for you; you'll have to try it and see - if read-to-write isn't over 10 percent it probably won't help that much. [...]
16707 67 41_Re: DSNDB07 bufferpool and data set sizes11_Robert Jans26_robert.jans@ALBERTSONS.COM31_Thu, 18 Jul 2002 09:49:01 -0700540_- Mark, our current DSNDB07 definitions are 14 1(one) volume 3390 Mod 3 datasets, (that's about 2900 cyls each). Sometimes even that's not enough :). DSNDB07 is in BP7 by itself, size is 25000 buffers, no Hiperpool, default values for the thresholds
Robert Jans Albertson's, Inc. < standard disclaimers apply >
-----Original Message----- From: Mark McCormack [mailto:mamccormack@STATESTREET.COM] Sent: Thursday, July 18, 2002 10:26 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: DSNDB07 bufferpool and data set sizes [...]
16775 49 41_Re: DSNDB07 bufferpool and data set sizes14_Harvey Puckett19_hpuckett@GTA.GA.GOV31_Thu, 18 Jul 2002 12:58:08 -0400489_us-ascii Hey Mark!
Definitely look at increasing SRTPOOL if it is not atleast 64M and DWQT VDWQT to 90 ... VPSEQT to 95-98 ... should help. But what's up with the 100cyl cap on your datasets ... HP rarely helps ... never seen it help ...
Cheers!
-----Original Message----- From: truman.g.brown@VERIZON.COM [mailto:truman.g.brown@VERIZON.COM] Sent: Thursday, July 18, 2002 12:47 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: DSNDB07 bufferpool and data set sizes [...]
16825 192 16_Re: SQL question14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Thu, 18 Jul 2002 17:42:12 +0200468_- Hello Terry,
many thanks for the answer.
First, I'd like to repeat, that "in real life" I found a solution which works OK (with C programming implied). So this is only an item of interest and DB2 discussions.
Indeed, the solution you suggested does no materialization on table2, which is very good. But the left side subquery does materialize, and I don't understand, why. I'll switch to my real examples, to show you some strange effects. [...]
17018 70 41_Re: DSNDB07 bufferpool and data set sizes12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 18 Jul 2002 20:19:19 +0200628_- Hi, I would start with :
VPSIZE(20000) VPSEQT(95) DWQT(65)VDWQT(55)
Get ready to go to VPSIZE(40000)
As per Hiperpool - you'll have to check for yourself
BTW - you probably mistyped - you can't have DWQT < VDWQT
Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@LISTSERV.YLASSOC.COM] On Behalf Of Mark McCormack Sent: Thursday, July 18, 2002 6:26 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: DSNDB07 bufferpool and data set sizes [...]
17089 89 41_Re: DSNDB07 bufferpool and data set sizes13_Martin Packer24_martin_packer@UK.IBM.COM31_Thu, 18 Jul 2002 18:18:23 +0100524_us-ascii Be careful with SRTPOOL. (I often do point out to clients it's a little on the small size.) Care needs to be taken to ensure you don't have too large a value if you have too many concurrent sorts as each requires its own tournament tree of that size.
I believe the IFCID 217/225 Virtual Storage instrumentation in V6 (+ APAR PQ47973) and V7 contains a bucket for this storage. You can probably use the number to guesstimate both real storage demand and 31-bit virtual storage usage by concurrent sorts. [...]
17179 76 41_Re: DSNDB07 bufferpool and data set sizes16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 18 Jul 2002 13:11:48 -0400412_iso-8859-1 Mark, One of our subsys has 2820 cyls in 7 datasets. HPOOL=10000,VPOOL=0,VPSEQT=100,DWQT=60,VDWQT=40, VPPSEQT=100. This one does about 40M Getpage per day and has Random hit rate = 99.9%, Seq hit rate = 95.3%.
However, DSNDB07 does only 2-3% of our total Getpages in that subsystem.
Do you maybe have some large queries (esp JOIN or ORDER BY) which might need some index attention? [...]
17256 23 21_remote trigger event.12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Thu, 18 Jul 2002 12:50:44 -0500385_us-ascii Does anybody have a good reference on how to execute a trigger on a federated SQL Server table that calls a stored procedure on OS/390. The trigger I am assume will be on the federated table in UDB. What does the call to 390 look like?
CREATE TRIGGER TEST.INSREMOTE AFTER INSERT ON DBO.UDBTABLE FOR EACH ROW MODE DB2SQL CALL MY.SP390PROC(NEW.VALUE1, NEW.VALUE2) ; [...]
17280 70 21_Fwd: Re: SQL question14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Thu, 18 Jul 2002 19:51:02 +0200398_- Hello DB2-Listers,
for the question below, I found the answer myself in the meantime. I have to explain you, because it's kind of a trap.
The "first 200 rows only" does not really save time, because the GROUP BY has to be executed on the complete result.
So the "first 200 rows only" query should need almost the same time as the full query. But why does it need more ? [...]
17351 171 22_Re: Separate Subsystem15_Zimmerman Terry35_Terry.Zimmerman@GENERALCASUALTY.COM31_Thu, 18 Jul 2002 13:03:03 -0500426_iso-8859-1 Thanks for the comments. It was a suggestion that we separate PeopleSoft in it's own subsystem. Currently we are only down now when we do a snap for the backup, and when we do online reorgs on the weekend.
Terry
-----Original Message----- From: David S. Waugh [mailto:dwaugh@NOTES.STATE.NE.US] Sent: Thursday, July 18, 2002 10:52 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Separate Subsystem [...]
17523 139 41_Re: DSNDB07 bufferpool and data set sizes14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 18 Jul 2002 13:04:53 -0500636_us-ascii Mark:
[Caveat: Note that these recommendations may or may not be suitable for a data sharing environment]
This is what I've been using for thresholds for the DSNDB07 bufferpool (BP7 in my case):
-ALTER BUFFERPOOL (BP7) VPSIZE(14400) HPSIZE(14400) + VPSEQT(97) VPPSEQT(50) VPXPSEQT(0) HPSEQT(80) + DWQT(90) VDWQT(90) + CASTOUT(YES)
This was as a result of a series of questions I put in IBMLink ETR a couple of years ago. The net of IBM's recommendation at the time was: - DSNDB07 should be in its own bufferpool - It's helpful if each DSNDB07 LDS is allocated on a different DASD volume and/or [...]
17663 27 20_Subset of a Subsytem12_Amit Agarwal33_amit_agarwal@STANDARDANDPOORS.COM31_Thu, 18 Jul 2002 14:11:24 -0400518_us-ascii OS/390 V5 A user wants to reduce data in one of our (test) subsystems. Existing data is refreshed from production. What they have suggested is they would systematically issue delete statements to the tables until the data is about one percent. Most of these tables are referentially linked.
For those who have gone through something like this before, are there any thoughts? We do not have many tools here, but I would be interested to know if you have used any tools to give them another option. [...]
17691 94 75_Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 18 Jul 2002 21:19:26 +0200344_- Hi,
Part of the REXX builder of RUNSTATS:
QUEUE " RUNSTATS TABLESPACE "DB"."TS" " PRTN QUEUE " SHRLEVEL CHANGE TABLE ALL " QUEUE " INDEX (ALL " QUEUE " KEYCARD FREQVAL NUMCOLS 1 COUNT 10 " QUEUE " FREQVAL NUMCOLS 2 COUNT 10 " QUEUE " FREQVAL NUMCOLS 3 COUNT 10 " QUEUE " FREQVAL NUMCOLS 4 COUNT 10 " QUEUE " ) REPORT YES " [...]
17786 53 24_Re: Subset of a Subsytem13_Vickers, Mark22_Mark.Vickers@ANICO.COM31_Thu, 18 Jul 2002 14:20:57 -0500453_iso-8859-1 Amit, This is really an application issue. Test data should be designed way before testing of the application even begins, and as enhancements and system changes occur, so does the test data. However, if someone has figured out a way to do this, they will make bazillions. I know this does not solve your problem, but you need to start impressing this on the developers to avoid this monkey landing back on your shoulders. cheers, Mark. [...]
17840 64 41_Re: DSNDB07 bufferpool and data set sizes10_Dale Smock20_Dale.Smock@BMSUS.COM31_Thu, 18 Jul 2002 14:28:50 -0500432_iso-8859-1 For a detailed explanation of the DB2 sort and how to tune it, I recommend a presentation by Chuck Hoover titled "Understanding the DB2 Sort" from the IDUG 2002 conference Proceedings.
Dale Smock BMG
-----Original Message----- From: Mark McCormack [mailto:mamccormack@STATESTREET.COM] Sent: Thursday, July 18, 2002 11:26 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: DSNDB07 bufferpool and data set sizes [...]
17905 28 17_online backups???15_Mark Maziarczyk22_MMaziarczyk@SYMCOR.COM31_Thu, 18 Jul 2002 15:40:19 -0400487_us-ascii O.k
I have changed the db cfg to have LOGRETAIN=YES and USEREXIT=YES,
what else do I have to do to ensure that the online backups work?
I have read the documentation on online backups and that you have to do a full offline backup as soon as you make those changes to the configuration, but what do you I have to do with active/archive logs? and also with the userexit?(I have not compiled it because I would like to know beforehand what to do with it? [...]
17934 55 25_Re: Subset of a Subsystem0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 18 Jul 2002 14:40:27 -0500536_us-ascii Loading a copy of production then deleting 99% of it seems like a real waste of time. You should suggest that they create an extract process that captures the data they want to load to devel. There are a number of commercial products that make doing this easier. I know that File-Aid (Compuware) has a module that allows you to setup a extract that has RI built in to it. We haven't used it yet, but we have had a demonstration (it looks really interesting) and are considering it to replace a home built extract process. [...]
17990 34 30_Strobe Against A DB2 Batch Job18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 18 Jul 2002 15:42:45 -0400342_iso-8859-1 We ran a stobe against a DB2 batch job. Below are a couple of functions that the strobe captured but I have no idea what they are. Has anyone seen these in a strobe before? Can you explain what they are?
Module Section Name Name Function DSNIDM DSNIONX2 NEXT ON CUB ON IXED FAN DSNIDM DSNIOST2 SET ON CUB DEF ON IX FAN [...]
18025 199 12_Re: Ugly sql45_=?iso-8859-1?Q?=22LeBlanc=2C_Andr=E9-ITB=22?=30_AndreD.Leblanc@CCRA-ADRC.GC.CA31_Thu, 18 Jul 2002 15:49:07 -0400422_iso-8859-1 Terry,
I'll give this a try and see if it works. Thank you for your help.
André LeBlanc
-----Original Message----- From: Terry Purcell [mailto:Terry_Purcell@YLASSOC.COM] Sent: July 18, 2002 10:53 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Ugly sql
Andre,
I'm surprised there wasn't a suggestion to have only 1000 rows in the tables, since this works for you. [...]
18225 205 22_Re: Separate Subsystem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 18 Jul 2002 14:55:58 -0500672_us-ascii Let me guess -- this suggestion came from PeopleSoft, didn't it?
Thanks, David
Zimmerman Terry cc: Sent by: DB2 Data Base Subject: Re: Separate Subsystem Discussion List
07/18/02 01:03 PM Please respond to DB2 Data Base Discussion List
Thanks for the comments. It was a suggestion that we separate PeopleSoft in it's own subsystem. Currently we are only down now when we do a snap for the backup, and when we do online reorgs on the weekend. [...]
18431 77 24_Re: Subset of a Subsytem16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Thu, 18 Jul 2002 15:56:08 -0400733_us-ascii platinum rc migrator works for me
"Vickers, Mark" on 07/18/2002 03:20:57 PM
Please respond to DB2 Data Base Discussion List
To: DB2-L@LISTSERV.YLASSOC.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: Re: Subset of a Subsytem
Amit, This is really an application issue. Test data should be designed way before testing of the application even begins, and as enhancements and system changes occur, so does the test data. However, if someone has figured out a way to do this, they will make bazillions. I know this does not solve your problem, but you need to start impressing this on the developers [...]
18509 103 34_Re: Strobe Against A DB2 Batch Job16_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM31_Thu, 18 Jul 2002 16:05:15 -0400525_iso-8859-1 Feel free in contacting our Hotline with this, and any other question you may have at 1-800-538-7822.
Another way is going to our support website at frontline.compuware.com
"Service Is Our Best Product"
Robert Galambos Compuware Corp. Of Canada
1-800-348-8299
1-877-950-3594 (Québec) 1-905-803-8603 (fax)
Les renseignements contenus dans le présent message électronique sont confidentiels et concernent exclusivement le(s) destinataire(s) désigné(s). [...]
18613 39 30_Strobe Against A DB2 Batch Job14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Thu, 18 Jul 2002 16:10:58 -0400275_ISO-8859-1 Looks like you are doing a lot of Index access, and may be scanning an index. Not on the mainframe now so I can't look for sure. If the IONX2 is a higher CPU value, then you're probably scanning. CUB is Cursor Usage Block, IXED FAN refers to the index fanset [...]
18653 96 24_Re: Subset of a Subsytem12_Amit Agarwal33_amit_agarwal@STANDARDANDPOORS.COM31_Thu, 18 Jul 2002 16:10:00 -0400473_us-ascii Mark, To be fair, this is not really the application guys fault. The story goes like this. This particular database has a ORACLE clone on a UNIX system. The ORACLE database is being moved to another machine with a different flavor of Unix. Due to a temporary shortage of space in the new UNIX box, they have brought over a subset of oracle database from the old UNIX box. They are now trying to make a similar subset for testing purposes on the OS/390 , Db2. [...]
18750 123 24_Re: Subset of a Subsytem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 18 Jul 2002 15:18:36 -0500342_koi8-r
Evidently, the new DB2 UNLOAD utility (V7) has SAMPLE and LIMIT keywords that can be used to unload subsets of data from tables. Unfortunately, it looks like it doesn't keep track of Referential Integrity in order to unload a coherent set of referentially-related data, which is what you want. (And you're on V5, not V7). [...]
18874 23 22_FW: Optimal DASD Calcs13_Vickers, Mark22_Mark.Vickers@ANICO.COM31_Thu, 18 Jul 2002 15:26:28 -0500501_iso-8859-1 I am sure everyone knows the answer to this except me - here goes anyway.
We use 3390-9 DASD devices and I am getting conflicting opinions as to optimal PRIQTY/SECQTY for physical usage of DASD. We use increments of 720 (1K blocks) attempting to create cylinder boundaries. From the info I have, 1 cylinder on a 3390 holds 849,960 bytes and divided by 1024 = 830 (1K Blocks). Am I missing something, or is there another algorithm to determine a better PRIQTY/SECQTY increment ? [...]
18898 71 21_Re: online backups???0_19_csutfin@AMSOUTH.COM31_Thu, 18 Jul 2002 15:34:56 -0500607_us-ascii Mark,
There are detailed instructions in the sample exit.
You need to customize DB2CUEXT2.C changing the ARCHIVE_PATH and RETRIEVE_PATH to reflect the directory where you intend to keep your archive logs.
Compile and link and then place the executable into sqllib/adm.
Carol Sutfin Corporate DBA AmSouth Bank (205)326-5214 Fax:(205)326-5613 csutfin@amsouth.com
Mark Maziarczyk cc: Sent by: DB2 Data Subject: online backups??? Base Discussion List [...]
18970 72 15_Re: CURRENTDATA13_Jeremiah Eden28_JEREMIAH.EDEN@RADIOSHACK.COM31_Thu, 18 Jul 2002 15:37:28 -0500581_- We don't use either, so we are SOL on this issue without a ZPARM. Thanks for your input.
-----Original Message----- From: David S. Waugh [mailto:dwaugh@NOTES.STATE.NE.US] Sent: Thursday, July 18, 2002 11:18 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: CURRENTDATA
The only place I know of where you can change CURRENTDATA from YES to NO is in the DSNH clist. You can also modify the settings for the variables &DSNEYV7A and &DSNEBV7A in DB2I panels DSNEBP10 and DSNEBP11 (library SDSNPFPE) to use "NO" for "Data Currency" instead of "YES" or "SAME". [...]
19043 74 22_Re: Separate Subsystem13_Baldon, David20_David_Baldon@BMC.COM31_Thu, 18 Jul 2002 16:06:43 -0500479_- Hello Terry,
I think one of the biggest advantages to keeping any ERP/CRM/??? system in its own DB2 system is that you can perform a recovery of the system without affecting other applications. For an ERP (read black box & non-DB2 RI) this is particularly important since there are times when the only way to do a point in time recovery is by doing a conditional restart. Since this is done at the DB2 system level, every application in the DB2 system is affected. [...]
19118 161 35_Re: NODYNAM in stored Procedures...10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 19 Jul 2002 09:08:53 +1200440_us-ascii Raquel,
When you compile a COBOL/DB2 program, it codes the DB2 calls as calls to the LE Interface module DSNHLI, which is by default an alias to DSNELI. When you use CAF (as in stored procedures), DB2 has to call DSNHLI2 (alias of DSNALI).
For stored procedures, if you use NODYNAM, then you would be linking the DSNALI module explicitly along with the load module. So, there wouldn't be any problem at runtime. [...]
19280 67 26_Re: FW: Optimal DASD Calcs14_MIKE FRIEDRICH30_MFRIEDRICH@REVENUE.STATE.IL.US31_Thu, 18 Jul 2002 16:10:46 -0500388_Windows-874 Mark,
On a 3390: 12 4K pages per track = 48 1K per track 180 4K pages per cylinder = 720 1K per track
There is some wasted space.
Specifying at least 720 1K for Priqty and Secqty will provide cylinder boundaries.
More helpful information can be found in:
Version 7 Administration Guide: Chapter 8. Estimating disk storage for user data [...]
19348 61 42_Administration Server Headaches (V7.2 EEE)14_Steve Westfall26_Steve.Westfall@EQUIFAX.COM31_Thu, 18 Jul 2002 16:15:06 -0500634_us-ascii Our platform: DB2 UDB EEE 7.2 (7.1 fixpack 3). RS/6000 SP2 AIX 4.3.3.0.
Until yesterday, my Control Center client (on Windows 2000 Professional) was able to connect to either of two UDB EEE instances on AIX nodes and perform administration tasks. (One of the instances has four logical partitions on two SP2 nodes. The other has eight logical partitions on four SP2 nodes.) Yesterday, when attempting to do this, I started getting an error message, SQL4414N "The DB2 Administration Server is not active." However, the Admin Server for both instances was running. Stopping and restarting the Admin Servers did not [...]
19410 12 26_Re: FW: Optimal DASD Calcs0_26_truman.g.brown@VERIZON.COM31_Thu, 18 Jul 2002 17:22:40 -0400321_us-ascii If you're talking about DB2 the CI sizes are 4K, not 1K.
================================================ 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.
19423 71 24_Re: Subset of a Subsytem12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 18 Jul 2002 15:14:47 -0700550_us-ascii I created a script to insert values into tables in the order DB2 RI wanted it. For a data refresh, I dropped, recreated, and then repopulated the data with the insert script. This made sure all the test data was always the same to start with, and once testers were familiar with it, they could tell right away when program changes caused data to go astray. We could also compare the result sets from queries before and after program changes to make sure we still got what we expected. I was never a fan of using a subset of production. [...]
19495 42 22_Re: Separate Subsystem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 18 Jul 2002 17:12:37 -0500298_us-ascii [snip] "For an ERP (read black box & non-DB2 RI) this is particularly important since there are times when the only way to do a point in time recovery is by doing a conditional restart." [snip]
All I can say is "Oh, ..... My ...... God" ..... what a truly frightening concept! [...]
19538 107 46_Re: Administration Server Headaches (V7.2 EEE)12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 18 Jul 2002 15:24:23 -0700604_us-ascii Have you tried rebooting the box? I had similar problems on FP2 because some kind network errors kept anything from talking to the admin server ports, and restarting the admin servers didn't totally clean up the ports either. I don't think restarting TCP/IP helped either. Tim --- Steve Westfall wrote: > Our platform: DB2 UDB EEE 7.2 (7.1 fixpack 3). > RS/6000 SP2 > AIX 4.3.3.0. > > Until yesterday, my Control Center client (on > Windows 2000 Professional) > was able to connect to either of two UDB EEE > instances on AIX nodes and > perform administration [...]
19646 204 34_Re: Strobe Against A DB2 Batch Job15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Fri, 19 Jul 2002 09:51:16 +1000320_us-ascii Its processing an index, but don't get stressed about this - this data is essentially irrelevent to us.
Further down the report you should find each DBRM/Package, each SQL statement it issued and the CPU time and delay accumulated by the statement. That is where to look for tuning a DB2 batch job. [...]
19851 131 23_Re: SQLCODE translation14_Agrawal, Navin21_navin.agrawal@EDS.COM31_Thu, 18 Jul 2002 22:27:53 -0500513_-
one of the easy ways is to go into an ispf editor and paste the value "00000090M" and then enter 'hex on' at command line. You'll get the desired value.
Cheers. Navin
PS : When I send this message earlier in default format(probably rich text) I got a message saying " Your message cannot be distributed to the DB2-L list because it exceeds the maximum message size of 250 lines." How does this mail exceeds 250 lines ? I'm sending the same message in text format now. [...]
19983 213 35_Re: NODYNAM in stored Procedures...16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Thu, 18 Jul 2002 20:29:27 -0700570_us-ascii Wow !!! Thank you Kals, Glenn, James Campbell and Tim Lowe for clearing up all the confusion I had (so what's new in this..you would say). You guys are great and this platform stupendous. Appreciate it so much.
Thanks again. Raquel.
--- teldb2kals wrote: > Raquel, > > When you compile a COBOL/DB2 program, it codes the > DB2 calls as calls > to the LE Interface module DSNHLI, which is by > default an alias to > DSNELI. When you use CAF (as in stored procedures), > DB2 has to call > DSNHLI2 (alias of DSNALI). > > [...]
20197 53 26_Getting Reason code C9009415_soumitra.mandal32_soumitra.mandal@TATAINFOTECH.COM31_Fri, 19 Jul 2002 11:23:46 +0530366_US-ASCII We are running DB2 V6.1 on OS390. This is a new installation. We encountered the problem when somebody tried to use the "AS IDENTITY" clause in a "CREATE TABLE" statement. The got SQL code -904. The resource was DSNDB06.DSNSQX02. I think the reason code was C90094.
When I executed from QMF SELECT * FROM SYSIBM.SYSSEQUENCES I am getting error: [...]
20251 32 31_accessing DB2/400 data from m/f9_jane mike18_ibm390@HOTMAIL.COM31_Fri, 19 Jul 2002 07:09:20 +0000368_- hi list,
We are trying to access DB2/400 from m/f (db2 UDB 6.1). We hav made necessary changes in CDB and we are able to execute the CONNECT TO sql statement..But when we try to run a select sql statement we get the following error.
DSNT408I SQLCODE = -805, SQLSTATE = 51002, INVALID APPLICATION STATE FROM OS/400 TOKENS DSNESM68 ..DSNESPRR JUNK. [...]
20284 28 25_Re: Subset of a Subsystem11_Jim Addison28_jim_addison@STANDARDLIFE.COM31_Fri, 19 Jul 2002 09:15:45 +0100654_iso-8859-1 We use the FileAid tool (FileAid/RDX) (with a home built front end) - it certainly is good for this work - let me know if you would like any specific details.
Jim.
For more information on Standard Life, visit our website http://www.standardlife.com/ The Standard Life Assurance Company, Standard Life House, 30 Lothian Road, Edinburgh EH1 2DH, is registered in Scotland (No SZ4) and regulated by the Financial Services Authority. Tel: 0131 225 2552 - calls may be recorded or monitored. This confidential e-mail is for the addressee only. If received in error, do not retain/copy/disclose it without our consent and [...]
20313 65 34_R: accessing DB2/400 data from m/f18_MARCOTULLI ARMANDO26_AMarcotulli@EULERGROUP.COM31_Fri, 19 Jul 2002 10:28:14 +0200539_iso-8859-1 Did you bind (or copy) the package DSNESM68 on AS/400? Also you need to rebind the plans DSNESPRR and DSNESPCS to modify the packlist, something like this: BIND PLAN(DSNESPCS) PKLIST(localDB2.DSNESPCS.DSNESM68, - remoteAS/400.BR1.DSNESM68, - ....., - ......) - ISOLATION(CS) ACTION(REPLACE) RETAIN END
HTH
Armando
-----Messaggio originale----- Da: jane mike [mailto:ibm390@HOTMAIL.COM] Inviato: venerdì 19 luglio 2002 9.09 A: DB2-L@LISTSERV.YLASSOC.COM Oggetto: accessing DB2/400 data from m/f [...]
20379 83 25_Re: Subset of a Subsystem10_Alan Johns27_ajohns@PRINCETONSOFTECH.COM31_Fri, 19 Jul 2002 04:49:48 -0400351_ISO-8859-1 Hi guys. You might want to look at the Move for Servers tool from Princeton Softech. It's a lot faster than RDX and can handle nasties like referential cycles easily. The RI within the tool can be defined using column substringing, concatenation and data driven criteria, so getting the extact subset you want should not be a problem. [...]
20463 103 24_Re: Subset of a Subsytem14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Fri, 19 Jul 2002 10:48:17 +0100357_iso-8859-1 Princeton Softech's Move for DB2 will do all you want and more.
You may define all application-specific RI yourself, and it'll extract all the data from all the related tables.
(Can I have a commission, please Mr. Parentes?)
Marcus ("These are my opinions, not my company's or my friends' or my dearly departed dog's.") [...]
20567 22 11_Unsubscribe10_Keith Kuhn21_Keith_Kuhn@WENDYS.COM31_Fri, 19 Jul 2002 06:33:47 -04009_us-ascii
20590 24 23_Hang tcp/ip connections12_Atalay Tanis22_atalayt@GARANTI.COM.TR31_Fri, 19 Jul 2002 05:22:06 -0500527_- Hi dear listers, We have migrated our V6 OS390 DB2 to V7 two days ago on our test system, and now we can not connect to DB2 through tcp/ip. We have no problem with SNA connections, but when we try to connect from either DB2 connect or from a UNIX DB2 (V7.2) the requester just hangs. There is certainly no error message on either side. OS390 DB2 does not show a mention of either a connection or an error. Nor the client issue any error message. It just hangs and waits till killed or cancelled. Did anybody face such a [...]
20615 36 54_AW: questions regarding the Oracle Transparent Gateway11_Martin Balz23_Martin.Balz@HVBINFO.COM31_Fri, 19 Jul 2002 12:44:44 +0200371_iso-8859-1 Hi Raymond,
sorry - this Setting was a Tip from the Oracle-Service, I have no Idea how the Gateway works without the Describe-Info. James could be right with his suggestion.
Maybe the Deadlock / Timeouts we had after swiching DB2DESCTAB to NO resulted from the "missing performance enhancements"...
Anyway, I am glad it works for you [...]
20652 152 46_Re: Administration Server Headaches (V7.2 EEE)0_23_db2admin@JULIUSBAER.COM31_Fri, 19 Jul 2002 12:54:17 +0200543_us-ascii Hi !
Have you checked your DNS to insure that your aliases are still in place (within an SP-Cluster you have to ensure that your send and return-path are identical).
HTH Peter -----------------------------------------------------------------------------------
Peter Horner, ISDD Bank Julius Baer & Co. Ltd. Hohlstrasse 602, CH-8010 Zurich, Switzerland Telephone +41 (58) 887 44 04, Fax +41 (58) 887 44 75 www.juliusbaer.com ----------------------------------------------------------------------------------- [...]
20805 41 27_Re: Hang tcp/ip connections12_Martin, Paul22_Paul.Martin@ECOLAB.COM31_Fri, 19 Jul 2002 06:19:53 -0500636_iso-8859-1 Do other daemons like ping and ftp work going from your unix system to os390?
-----Original Message----- From: Atalay Tanis [mailto:atalayt@GARANTI.COM.TR] Sent: Friday, July 19, 2002 5:22 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Hang tcp/ip connections
Hi dear listers, We have migrated our V6 OS390 DB2 to V7 two days ago on our test system, and now we can not connect to DB2 through tcp/ip. We have no problem with SNA connections, but when we try to connect from either DB2 connect or from a UNIX DB2 (V7.2) the requester just hangs. There is certainly no error message on either side. OS390 [...]
20847 105 38_Re: R: accessing DB2/400 data from m/f9_jane mike18_ibm390@HOTMAIL.COM31_Fri, 19 Jul 2002 11:24:27 +0000466_- hi,
thanx for the reply. we havnt copied the package DSNESM68 to AS/400.How to do it ?
we tried binding a package for the remote db2. but the job failed with the following error.
DSNT270I + THE FOLLOWING SQLCA INFORMATION WAS RETURNED FROM QSQSCPKG SQLCODE = -204 SQLSTATE = 42704 SQLERRMT = QSYS.DSNESPCS COLLECTION SQLWARN 0= ,1= ,2= ,3= ,4= ,5= ,6= ,7= ,8= ,9= ,A= DSNT233I + UNSUCCESSFUL BIND FOR PACKAGE = AS400.DSNESPCS.DSNESM68.() [...]
20953 46 12_TCP\IP Error12_alex adebayo15_bayo9@YAHOO.COM31_Fri, 19 Jul 2002 04:30:15 -0700429_us-ascii
Hi all,
Does anyone have ideas of this tcpip problem
40003: [IBM][CLI Driver][DB2/6000] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "client ip". Communication function detecting the error: "recv". Protocol specific error code(s): "78", "*", "*". SQLSTATE=08001 [...]
21000 24 27_Re: Hang tcp/ip connections32_Atalay Tanis (Garanti Teknoloji)22_AtalayT@GARANTI.COM.TR31_Fri, 19 Jul 2002 14:30:04 +0300398_iso-8859-1 Yes they do work. Also I can connect to UNIX DB2 V7 from OS390 but I can't do the opposite.
-----Original Message----- From: Martin, Paul [mailto:Paul.Martin@ECOLAB.COM] Sent: Friday, July 19, 2002 2:20 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: [DB2-L] Hang tcp/ip connections
Do other daemons like ping and ftp work going from your unix system to os390? [...]
21025 88 24_Re: Subset of a Subsytem16_Bednarz, Michael22_Michael.Bednarz@CA.COM31_Fri, 19 Jul 2002 13:13:14 +0100426_iso-8859-1 Hi folks RC/Migrator from CA (former PLATINUM - Tool) does the same .
work!
OK who is the next vendor!!!
Michael Bednarz
FSG Consultant
Computer Associates Germany 40547 Düsseldorf Emanuel-Leutze-Str. 4 Phone: +49 (0) 211 5306 295 Mobile: +49 (0) 170 85 38 576 Fax: +49 (0) 211 5306 200 mailto:michael.bednarz@ca.com
21114 51 22_Re: Optimal DASD Calcs16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 19 Jul 2002 08:34:54 -0400300_iso-8859-1 Mark, yes you are missing something. You specify 1k blocks but physically they are allocated as 4k blocks. You can fit 12 4k blocks on a 3390 DASD track. The rest of of the track is wasted. There are 15 tracks per cylinder. Ergo 180 4k blocks (same space as PRIQTY=720) per cylinder. [...]
21166 62 22_Re: Separate Subsystem15_Zimmerman Terry35_Terry.Zimmerman@GENERALCASUALTY.COM31_Fri, 19 Jul 2002 08:21:08 -0500391_iso-8859-1 David,
You sound like the systems programmer that sits across the aisle from me. I always talk to him when I want a good opinion. He has been in IS almost 40 years.
Terry
-----Original Message----- From: David S. Waugh [mailto:dwaugh@NOTES.STATE.NE.US] Sent: Thursday, July 18, 2002 5:13 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Separate Subsystem [...]
21229 236 31_Re: Separate Subsystem (part 1)12_Weaver, Rick19_Rick_Weaver@BMC.COM31_Fri, 19 Jul 2002 08:28:49 -0500418_iso-8859-1 If you can afford the outage, you could copy everything SHRLEVEL REFERENCE and do a TOCOPY recovery. This might require a several hour outage for copying the application and subsystem.
If you afford a brief outage and have the hardware, you could SUSPEND logging, snap copy all the volumes, and RESUME logging (typically about a 15 minute outage). Your restore will be to the point of the snap. [...]
21466 192 22_Re: Separate Subsystem13_Baldon, David20_David_Baldon@BMC.COM31_Fri, 19 Jul 2002 08:35:44 -0500454_- "Oh, ..... My ...... God" ..... This is one of the first phrases learned when a DBA starts working with an ERP/CRM/???.
My colleague, Rick Weaver, has already answered in depth (and more seriously). One point he didn't mention however, is that many of the ERP/CRM/??? Vendors document a conditional restart as "the method" of doing a point in time recovery. For a lot of shops they will only do things using methods approved by the vendor. [...]
21659 99 30_Re: Getting Reason code C9009414_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 09:12:26 -0500728_us-ascii It sounds like the SYSIBM.SYSSEQUENCES tables and indexes were IDCAMS DEFINEd, but never populated with data. Verify that the DSNTIJID installation job ran successfully, particularly these steps: //DSNTUB5 EXEC DSNTIN,SAMP=DSNTISB5,LIB=SYSSEQ //DSNTUB6 EXEC DSNTIN,SAMP=DSNTISB6,LIB=DSNSQX01 //DSNTUB7 EXEC DSNTIN,SAMP=DSNTISB7,LIB=DSNSQX02 //DSNTUB8 EXEC DSNTIN,SAMP=DSNTISB8,LIB=SYSSEQ2 //DSNTUB9 EXEC DSNTIN,SAMP=DSNTISB9,LIB=DSNSRX01
You may even need (or want) to go back and re-run the DSNTSEQ and DSNTSEQ2 steps in install job DSNTIJIN that IDCAMS DEFINE the SYSSEQ and SYSSEQ2 tablespaces and indexes, just to make sure they're all there and set up correctly before you rerun the steps in DSNTIJID. [...]
21759 28 22_Re: Separate Subsystem0_19_Tim.Lowe@STPAUL.COM31_Fri, 19 Jul 2002 09:26:38 -0500424_us-ascii David Baldon, Actually, my reaction was somewhat similar to David Waugh's "O My God", but I don't think it appropriate to use those words here!
But, aside from the obvious problems of anyone recommending a "conditional restart as a method for doing a point-in-time recovery", I don't think that "conditional restart" can even accomplish a "point in time recovery". Is there something else invoved here? [...]
21788 111 35_Re: accessing DB2/400 data from m/f14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 09:24:20 -0500436_iso-8859-1 This is kind of a wild guess, but it looks like the DSNESM68 package in collection DSNESPRR for plan DSNESPRR (SPUFI Repeatable Read Plan) was not bound properly on this DB2 subsystem (-805 means it can't be found).
Quick fix: On the SPUFI Defaults panel, switch to an ISOLATION of "CS" (Cursor Stability) instead of "RR" (you should be using CS instead of RR anyway when you're running SPUFI), and try it again. [...]
21900 33 41_Re: selecting first 200 rows from a table13_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Fri, 19 Jul 2002 10:07:08 -0500330_- Tim & Duam, This will surely restrict the *output* to "first accessed" 200 rows but at a heavy price of scanning the whole table ( and invoking the UDF for each row ). Keeping a count in the program to stop FETCHing is much more efficient. HTH Mohammad
On Mon, 15 Jul 2002 09:16:09 -0500, Tim.Lowe@STPAUL.COM wrote: [...]
21934 13 38_Re: Connect to DB2 on AS/400 from m/f.10_Can Techie22_can_technical@YAHOO.CA31_Fri, 19 Jul 2002 09:51:33 -0500328_- Hi,
You can achieve the file transfer thru SNADS, RJE, and FTP.
================================================ 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.
21948 80 22_Re: Separate Subsystem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 10:14:30 -0500364_us-ascii Thank you Tim, I couldn't agree more.
[This is just my opinion again, but] To me, a Vendor recommending conditional restart as their "approved" method of point in time recovery means: they haven't put a great deal of thought into recovery, and have chosen to take the "easy" (or is that sleazy?) way out. Easy for them, at least; hard for us. [...]
22029 235 34_Re: Strobe Against A DB2 Batch Job18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Fri, 19 Jul 2002 11:29:20 -0400467_- This is very detailed information. Thank you very much for your help. We are reviewing our strobes based on what you provided.
Peter J. Krawetzky, DBA IBM Certified Solutions Expert DB2 UDB V7.1 Database Administration For Unix, Windows and OS/2
-----Original Message----- From: Richard Simpson [mailto:rsimpson@AU1.IBM.COM] Sent: Thursday, July 18, 2002 7:51 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Strobe Against A DB2 Batch Job [...]
22265 26 41_Re: selecting first 200 rows from a table0_19_Tim.Lowe@STPAUL.COM31_Fri, 19 Jul 2002 10:56:42 -0500484_us-ascii Mohammad, I agree with you that there could certainly be performance concerns using a UDF to limit the result set, but I don't think that Duam mentioned the size of the table or the reasons behind his requirement. And, the UDF solution satisifies his requirement. There are also cases with "FETCH FIRST n ROWS" (in DB2 V7) where the entire table will be scanned and sorted before "throwing away" most of the result set, so the UDF solution is not a bad one with DB2 V6. [...]
22292 26 51_Re: AW: DSNT500I, resource unavailable, during LOAD17_Giorgio De Nunzio25_giorgio.denunzio@UNILE.IT31_Fri, 19 Jul 2002 18:10:21 +0200443_X-UNKNOWN On Thu, 18 Jul 2002, [iso-8859-1] "Kurtz, Rüdiger" wrote:
> Obtaining DB2 Estimator > DB2 Estimator is one of several features and tools offered with DB2
Thanks for the info, Ruediger! I have written to IBM and they said that Estimate "should" work (not fully, perhaps) with DB2 v3. So I am going to try it out. They have also encouraged me to upgrade from DB2v3 to something better, but this is another story ;-) [...]
22319 173 22_Re: Separate Subsystem13_Baldon, David20_David_Baldon@BMC.COM31_Fri, 19 Jul 2002 11:17:08 -0500479_- The conditional restart takes care of in-flight transactions which is then followed a recovery of the table spaces.
The main reason that the CR is recommended by SAP is that it is virtually impossible to get a system wide point of consistency. There are so many objects in an SAP system that the QUIECSE utility cannot handle all of them at once. Then when you consider the high availability requirements of most shops you begin to see why this method is required. [...]
22493 54 78_IBM tutorial: Creating an XML-based customer repository using DB2 XML Extender16_Jeffrey I Condon19_jicondon@US.IBM.COM31_Fri, 19 Jul 2002 09:42:22 -0700619_iso-8859-1 hi all, Thought this may be of interest. cheers jeff
Creating an XML-based customer repository using DB2 XML Extender This tutorial introduces DB2 XML Extender and shows you how to set up DB2 to support an XML-based customer repository, and how to import an existing EAR file in to the Application Developer configuration of WebSphere Studio. It also explains the interaction between the application and database. Finally, this tutorial demonstrates how this all works with a sample application https://www6.software.ibm.com/reg/devworks/dw-db2xmlx-i?S_TACT=102B7W62&S_CMP=DEVSOL?open&l=327,t=gr [...]
22548 48 43_Stopping DB2 UDB 7.2 Replication ASNCCP.exe14_Harold Trammel18_hlt@APCC.ASPCA.ORG31_Fri, 19 Jul 2002 11:48:37 -0500345_us-ascii Good day.
I have begun a project to replicate data from three DB2 UDB/Win2K databases to another DB2 UDB/Win2K database. In production, we need the replication process to run as a service automatically and are using the ASNSERV Replication Service. This service kicks out instances of ASNCCP.exe and ASNAPPLY.exe as needed. [...]
22597 213 46_Re: Separate Subsystem (part 3 - and the last)12_Weaver, Rick19_Rick_Weaver@BMC.COM31_Fri, 19 Jul 2002 12:01:06 -0500646_iso-8859-1 It's a lot of work. Some shops use recovery for ERP a lot more than they'd like to, frequently in support of the development/deployment environments - but the same recoverability as production is required.
Which is why we developed Smart Recover for SAP. It completely automates the process of backup, recovery, and most importantly the analysis of which techniques to use or functionality to exploit (for instance Physical Backout, or Backup/Recovery Avoidance). We avoid Conditional Restart if at all possible, but as described above sometimes it's the only way to go. One customer likens the product to watching a video [...]
22811 22 22_Re: Separate Subsystem0_19_Tim.Lowe@STPAUL.COM31_Fri, 19 Jul 2002 12:05:21 -0500333_us-ascii David, Can you explain what you mean by "The conditional restart takes care of in-flight transactions which is then followed a recovery of the table spaces."???
A normal restart takes care of in-flight transactions, why do you do a conditional restart? And, why follow this with a recovery of the tablespaces??? [...]
22834 204 22_Re: Separate Subsystem12_Weaver, Rick19_Rick_Weaver@BMC.COM31_Fri, 19 Jul 2002 12:09:47 -0500606_iso-8859-1 Unfortunately my original response exceed the 250 word speed limit, so I had to break the answer up into 3 parts. Sorry if it's confusing and disjointed.
To address another's comments, believe me we did NOT take the 'easy way out'. We avoid Conditional Restart at all costs, we have MUCH MORE effective ways to recover the changed objects (and avoiding recovery for those objects that don't need it, which is most of them in a common ERP application). We've been trialed at recovering all the objects changed by 120,000 log records in less than one minute (via our exclusive [...]
23039 67 75_Re: Very simple SQL problem: Could this SQL be made to run under 2 minutes?12_Hayden Jones25_Hayden_Jones@GRAINGER.COM31_Fri, 19 Jul 2002 12:25:35 -0500561_us-ascii Thanks to all for responses on RUNSTATS syntax. The article Terry Purcell mentioned http://www-1.ibm.com/support/manager.wss?rs=64&rt=0&org=SW&doc=1023318 was very helpful.
However, ..... this stuff is goofy. Getting lots of abends. Version 5.
RUNSTATS INDEX ( owner.indexname ) works just fine
RUNSTATS INDEX ( owner.indexname KEYCARD ) works just fine ..... AS LONG AS the index does not have more than two columns in it (if there is no data in the table, the job is successful no matter how many columns are in the index.) [...]
23107 88 24_Re: Subset of a Subsytem10_CEI, Bruno30_Bruno.CEI@ANDOVER.SEMA.SLB.COM31_Fri, 19 Jul 2002 18:32:16 +0100472_iso-8859-1 Amit,
I guess what you want is a subset of data that is logically consistent form an application point of view, because you mentioned the fact that many tables are linked one another.
There are tools available that can help you in doing this.
Many companies produce something that can help you. Names that spring to my mind are CA, Princeton Softech and IBM. I guess that also Compuware can help but I'm not familiar with their tools. [...]
23196 99 44_Re: IEC070I 210(8,306)-213,abend0c4 IGDZILLA16_Snyder, Art, ITD24_asnyder@CO.ALAMEDA.CA.US31_Fri, 19 Jul 2002 10:57:40 -0700564_iso-8859-1 Jeremiah,
Do you have an IBM APAR number for this? We would like to track the problem, but we didn't find anything searching the IBM database.
Art Snyder Alameda County ITD 510-272-3712
-----Original Message----- From: Jeremiah Eden [mailto:JEREMIAH.EDEN@RADIOSHACK.COM] Sent: Tuesday, July 16, 2002 10:02 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: IEC070I 210(8,306)-213,abend0c4 IGDZILLA
IBM has said that several other shops are now experiencing this problem. If anyone wants share notes, please contact me directly. [...]
23296 80 49_Update with Host Variable at level 10 or level 0123_Tello Najera Juan Pablo26_jp.tello@BBVA.BANCOMER.COM31_Fri, 19 Jul 2002 13:07:12 -0500519_iso-8859-1 Hi list. Environment DB2 OS/390 V6.
One of our programmers came with a very rare situation. He has a program that makes this UPDATE:
EXEC SQL UPDATE PARAM SET DATOSPAR = SUBSTR(DATOSPAR,1,27) || :WS-T54-DAT-EJECUCION || SUBSTR(DATOSPAR,49,10) WHERE ITIPOPAR = 'T54' AND IPARAM = ' ' AND SUBSTR(DATOSPAR,48,1) = :WS-EJEC-ZZ08
The variable WS-T54-DAT-EJECUCION is decalred at level 10 and the update returns SQLCODE +100. Here is how the varable is declred in Working Storage [...]
23377 17 44_Kevin Brown/Dallas/IBM is out of the office.11_Kevin Brown18_brownkd@US.IBM.COM31_Fri, 19 Jul 2002 12:11:24 -0600281_us-ascii I will be out of the office starting July 19, 2002 and will not return until July 22, 2002.
I will be out of the office on July 19 and will reply to your note when I return on July 22. If an emergency arises, I can be reached via my mobile phone 214-498-5430. [...]
23395 151 22_Re: Separate Subsystem13_Baldon, David20_David_Baldon@BMC.COM31_Fri, 19 Jul 2002 13:30:26 -0500596_- Tim, You are correct about a normal restart handling in-flight transactions. I should have said the CR gets rid of the "problem" by effectively setting the DB2 system back in time (yes, it is a heavy handed method). This of course leaves tables spaces with RBAs higher than the "system" RBA not to mention unwanted data. Recovering the table spaces takes care of that little problem. This results in a system where the data, from a DB2 point of view, is physically consistent. The vendor must then make the data logically consistent by synchronizing things between DB2 and the application [...]
23547 63 22_Re: Separate Subsystem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 13:39:07 -0500530_us-ascii "...which is then followed [by] a recovery of the table spaces."
And what kind of recovery do they recommend for this ERP afterwards? Point-in-time, or to-current, or what? From what backups, & what kind of backups?
David Waugh
P.S. David, I'm not trying to pick on you, I'm just trying to understand what the ERP vendors are recommending and why. If anything, I'm picking on the ERP vendors (sure wish they'd pop in and clear this up for us, instead of all of us speculating on what they mean) [...]
23611 42 41_Re: DSNDB07 bufferpool and data set sizes14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 13:59:30 -0500640_us-ascii Just found this little blurb in IBMLink, concerning whether Hiperpools are useful for DSNDB07 or not (one appendee thought they made no discernible difference). In part, it says:
---------------------------------------------------------------------------------------------------------------------------------------------------------------- Item RTA000105271
ABSTRACT: DSNDB07 HIPERPOOLS
Can you recommend/explain any advantages there might be of having DB2 workfiles (DSNDB07) backed by hiperpools? Is this a good idea? I need info as it relates to a data sharing environment as well as non data sharing. [...]
23654 37 16_trigger question11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Fri, 19 Jul 2002 14:07:09 -0500295_iso-8859-1 The only triggers we have so far inserts a row into alog table after an insert, update, or delete is done. I've been re-reading the V6 documentation and see reference to selects in the trigger. I'm not sure I understand what happens. Here's an example from the V6 release guide: [...]
23692 81 47_Re: Stopping DB2 UDB 7.2 Replication ASNCCP.exe18_Gert van der Kooij16_gkooij@XS4ALL.NL31_Fri, 19 Jul 2002 21:11:29 +0200452_iso-8859-1 Hi,
Is your ASNPATH variable set to the right directory? The ASNCMD and ASNCCP programs seem to communicate with each other using files in the ASNPATH directory, so this might be related.
----- Original Message ----- From: "Harold Trammel" Newsgroups: bit.listserv.db2-l To: Sent: Friday, July 19, 2002 6:48 PM Subject: Stopping DB2 UDB 7.2 Replication ASNCCP.exe [...]
23774 46 21_DB2 Precompiler error9_Don Cross20_Don.Cross@TC.FAA.GOV31_Fri, 19 Jul 2002 15:14:43 -0400326_us-ascii I am trying to compile some embedded sql in C but can't get aroung precompiler errors. This is the closest I have gotton. Our language is Cobol but I am overriding it with HOST(C). Any ideas where I have erred? We are OS390v2.9 DB2 v6.
tapi is a stored procedure(not yet created) with one input variable. [...]
23821 47 41_Re: DSNDB07 bufferpool and data set sizes14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 14:26:22 -0500646_us-ascii There's also an interesting entry in IBMLink that has some of the more current recommendations for bufferpool & hiperpool settings for DSNDB07:
Item BDC000014147 Last updated....: 01/10/2002 Abstract........: Proper Threshold Settings for DSN4Knn Bufferpool
It's too long to append here, but here's the net of it: - Isolating the DSNDB07 database in its own bufferpool is an excellent idea - The recommendation for VPSEQT is between 95-99%. There is going to be some random activity , so you want to leave some room for that. Start at 97% and tune from there. - Since DB2 workfiles are sequentially accessed, changing [...]
23869 78 20_Re: trigger question11_David Nance16_DWNance@FHSC.COM31_Fri, 19 Jul 2002 15:41:51 -0400517_US-ASCII Tina, This really has nothing to do with your question, but with your current use of triggers. A while back there was some talk about the high cost of using triggers to accomplish a log as you say you do. I think it was Phil Grainger summed it up best, with exactly what costs were involved. The gist of it is why would you want to set up a trigger to keep an audit log for you, when the DB2 log already does this. You can find out who updated, inserted, deleted anything by reading the log. Due to our [...]
23948 32 23_Bind parameter question11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Fri, 19 Jul 2002 12:39:04 -0700521_US-ASCII I remember from my contract application programming days that at one shop, we did not fully qualify the tables but used a bind parameter. I was not a DBA at the time so I just did as they said and now as a DBA, I would like to look into using that in my current shop. the previous DBA had everyone to fully qualify the tables in the programs but I seem to remember that isn't necessary. And we have a few cases where the same tables are in different databases (prod, Point in time, New Year rollover) and I [...]
23981 59 22_Re: Separate Subsystem17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Fri, 19 Jul 2002 21:30:19 +0200561_us-ascii To me it seems that the origin of this approach lies in the fact that the original DB system of ERP applications is Oracle. I took a series of Oracle DBA courses in the past months (so apart from a few exercises I have only theoretical knowledge), and one thing that struck me is that the backup & recovery abilities of Oracle are rudimentary at best. Obviously Oracle (and I assume other Unix & MS RDBMSs as well) was designed to be backed up (and restored) with operating system means (i.e. file copy) while the instance is down. Online backups [...]
24041 67 27_Re: Bind parameter question13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Fri, 19 Jul 2002 14:58:02 -0500730_us-ascii in your bind card
OWNER(xxxxxxx) QUALIFIER(xxxxxxx)
these two paramaters you could change
KATHY JONES To: DB2-L@LISTSERV.YLASSOC.COM Subject: Bind parameter question Sent by: DB2 Data Base Discussion List
07/19/2002 02:39 PM Please respond to DB2 Data Base Discussion List
I remember from my contract application programming days that at one shop, we did not fully qualify the tables but used a bind parameter. I was not a DBA at the time so I just did as they said and now as a DBA, I would like to look into using that in my current shop. the [...]
24109 20 27_Re: Bind parameter question13_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Fri, 19 Jul 2002 15:12:12 -0500454_us-ascii Use the following option. I know it works for OS/390, But I can not speak on the other DB2 environments
Add the following Option.
QUALIFIER(XXXXXXXX)
Bob Tilkes
================================================ 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.
24130 116 53_FW: Update with Host Variable at level 10 or level 0124_ANDERSON Margaret * IRMD29_Margaret.Anderson@STATE.OR.US31_Fri, 19 Jul 2002 13:14:04 -0700475_iso-8859-1 Juan, If I remember correctly, this may be due to the fact that the 10-Level host variable used in the SQL statement (WS-T54-DAT-EJECUCION in your example) is part of a REDEFINES of the single variable WS-T54-CTL-PARAM. Since the DB2 Pre-processor runs before the COBOL compiler, it tries to deal with host variables before the COBOL compiler has determined the offsets of the subsidiary items in the REDEFINES and doesn't have enough information to do that. [...]
24247 94 20_Re: trigger question11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Fri, 19 Jul 2002 15:22:04 -0500282_iso-8859-1 We don't have a log analyzer tool, and doubt we could justify it. We (the system DBAs) don't have the time to write a process to do this and the applications folks don't have the knowledge to do it. They only do this for 6 tables and there's not that much activity. [...]
24342 41 27_Re: Bind parameter question11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Fri, 19 Jul 2002 13:19:20 -0700362_US-ASCII I am on OS/390 - sorry forgot to include that - now for another question - what about the programs that already have the qualifier hard coded? Can we use this parameter without changing the programs?
Kathy Jones Central Information Services Clark County School District O/S390 DB2 DBA NT DB2 DBA 702-799-5040 x366 jonesks@GroupWise.ccsd.net [...]
24384 129 22_Re: Separate Subsystem13_Baldon, David20_David_Baldon@BMC.COM31_Fri, 19 Jul 2002 15:43:50 -0500436_- The recovery is always to CURRENT using whatever image copies are available. For the dynamically created table spaces it may have to be a LOG ONLY recovery.
The hardware vendors push their "intelligent" DASD as a solution for ERP's. IBM still maintains that image copies should be made though I have heard of companies that do not make image copies any longer (given time they'll figure out what a bad decision that was). [...]
24514 68 27_Re: Bind parameter question16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM31_Fri, 19 Jul 2002 13:55:37 -0700758_us-ascii Kathy,
Yes, including the QUALIFIER parm on your bind (and binding) won't impact the existing application.
Regards, Jeff L'Italien American Express
From: "KATHY JONES" @LISTSERV.YLASSOC.COM> on 07/19/2002 01:19 PM
Please respond to "DB2 Data Base Discussion List"
Sent by: "DB2 Data Base Discussion List"
To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: Re: Bind parameter question
I am on OS/390 - sorry forgot to include that - now for another question - what about the programs that already have the qualifier hard coded? Can we use this parameter without changing the programs? [...]
24583 34 22_Re: Separate Subsystem0_19_Tim.Lowe@STPAUL.COM31_Fri, 19 Jul 2002 15:51:05 -0500508_us-ascii David, I think that I see what they are doing, please correct my if I am wrong. Are they simply changing the STARTRBA and ENDRBA in the CRESTART record so that DB2 restart creates log records to "backout" inflight transactions? And, then recoverying everything to current so that the new log records that backed out the "inflight" transactions are included? If so, aren't there some potential problems that might cause conditional restart to fail due to changes that occurred after that point? [...]
24618 94 27_Re: Bind parameter question14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 15:57:10 -0500471_us-ascii Kathy:
I think you're thinking about the QUALIFIER parameter of the BIND command. It determines the implicit qualifier for unqualified names of tables, views, indexes, and aliases contained in the plan or package.
So if you coded your SQL in your program as: SELECT a, b, c FROM TABLE1...
and bound the plan on your Production subsystem with QUALIFIER(PRODUCTION), at execution time it would do SELECT a, b, c FROM PRODUCTION.TABLE1... [...]
24713 95 27_Re: Bind parameter question15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Fri, 19 Jul 2002 17:24:19 -0400343_iso-8859-1 Kathy, If you wish to have the application to act like the calls are unqualified then you must use unqualified calls. The QUALIFIER / OWNER parameters on the bind command are only used for unqualified calls. The situation you have of hardcoded qualified calls will take precedence unless the hardcoded qualification is removed [...]
24809 122 0_12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV31_Fri, 19 Jul 2002 17:31:18 -0400362_iso-8859-1 Generally speaking, your PS applications should be in their own subsystem for their own good, as well as the health and welfare of your other workloads,not to mention your own health and sanity when something or other causes a problem. I believe maintenance, ZParms, and other tuning has to be handled differently for a certified PS environment. [...]
24932 120 27_Re: Bind parameter question11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Fri, 19 Jul 2002 14:32:20 -0700394_US-ASCII thank you David and everyone else that replied - this is exactly what I was looking for. We do have multiple databases so I will research into the ALIAS - thanks again
Kathy Jones Central Information Services Clark County School District O/S390 DB2 DBA NT DB2 DBA 702-799-5040 x366 jonesks@GroupWise.ccsd.net
>>> dwaugh@NOTES.STATE.NE.US 07/19/02 01:57PM >>> Kathy: [...]
25053 33 47_Re: Stopping DB2 UDB 7.2 Replication ASNCCP.exe14_Harold Trammel18_hlt@APCC.ASPCA.ORG31_Fri, 19 Jul 2002 16:44:04 -0500346_us-ascii At 09:11 PM 7/19/2002 +0200, you wrote: >Hi, > >Is your ASNPATH variable set to the right directory? The ASNCMD and ASNCCP >programs seem to communicate with each other using files in the ASNPATH >directory, so this might be related. >
This variable is set and the CCP file and the asnserv.log file are showing up correctly. [...]
25087 136 22_Re: Separate Subsystem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 16:41:31 -0500468_us-ascii I think you hit the nail on the head Dr. Michael.
PeopleSoft, for example (I love to pick on them, obviously) was a port from, I think, Sybase to something else to something else to Oracle and then finally to DB2. I remember them "recommending" to us that we (a) give all the applications programmers SYSADM authority and (b) put each PeopleSoft instance in its' own DB2 subsystem. This was easy to do in Oracle, they said, so what's the problem? [...]
25224 76 20_Re: trigger question11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 19 Jul 2002 16:48:24 -0500331_- Tina,
Your assumptions are correct. Notice the granularity is statement (not row). If only 1 row is inserted, this is trivial but when multiple rows are inserted with a signgle statement(INSERT INTO ...SELECT FROM or by other means including anotehr trigger), all inserted rows are placed in the transition table NT. [...]
25301 72 27_Re: Bind parameter question14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 16:44:55 -0500484_us-ascii No - anything that's hard-coded with a qualifier in your programs won't pick up the QUALIFIER from the BIND instead -- it'll use what's hard-coded.
David Waugh
KATHY JONES cc: Sent by: DB2 Data Subject: Re: Bind parameter question Base Discussion List
07/19/02 03:19 PM Please respond to DB2 Data Base Discussion List [...]
25374 85 55_Re: PeopleSoft DB2 Install (Was New subsystem help!!!!)12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV31_Fri, 19 Jul 2002 18:03:32 -0400451_iso-8859-1 May I ask which PS list you find useful?
An search revealed several, but none seem to report the list membership quantities I would expect for the primary public one.
thanks, tom
-----Original Message----- From: Melissa Rogers [mailto:mrogers@NYSTRS.STATE.NY.US] Sent: Thursday, July 11, 2002 10:50 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: PeopleSoft DB2 Install (Was New subsystem help!!!!) [...]
25460 49 57_problem with DB2 Incremental copy with LISTDEF/TEMPLATE ?0_19_Tim.Lowe@STPAUL.COM31_Fri, 19 Jul 2002 17:02:59 -0500648_us-ascii I have asked for a DB2 (for OS/390) design change request for the way that the new listdef/template utilities and the incremental copy utility work together. Currently, the listdef/template utilities allocate the incremental copy output datasets before incremental copy checks to see if any pages have changed. The result is that we wind up with empty incremental copy datasets when no pages have changed. Creating these empty datasets wastes time and resources, managing them wastes more time, and deleting them involves the risk of accidentally deleting one that was in the process of being written to. Therefore, the Design Change [...]
25510 75 22_Re: Separate Subsystem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 17:00:52 -0500483_us-ascii I'm not sure what claims & drains ARCHIVE LOG MODE(QUIESCE) attempts (I couldn't find anything in the manuals), but I would think it would have the same problem that a QUIESCE of all the ERP objects does -- waiting for all activity from the ERP (and everything else on the subsystem) to stop before writing the system-wide checkpoint that would give you the point of consistency you'd need. Would it complete, or timeout? Would it cause other units of work to timeout? [...]
25586 101 27_Re: Bind parameter question11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Fri, 19 Jul 2002 15:09:16 -0700412_US-ASCII Which is okay because that means we don't have to change the existing programs but could do new ones unqualified with a 'PRODDB' or 'TESTDB' Alias. This is list is so helpful to us newbie DBAs. You have given me ideas to research so I appreciate it.
Kathy Jones Central Information Services Clark County School District O/S390 DB2 DBA NT DB2 DBA 702-799-5040 x366 jonesks@GroupWise.ccsd.net [...]
25688 92 27_Re: Bind parameter question14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 19 Jul 2002 17:17:09 -0500423_us-ascii Um, let me clarify this a little bit:
Anything you have hard-coded with a Qualifier in your program won't pick up the QUALIFIER parameter from the BIND command and override it.
So if you coded your SQL: SELECT a, b, c FROM TESTSYS.TABLE1...
you couldn't override it with QUALIFIER(PRODUCTION) on the BIND and expect it to change automagically to: SELECT a, b, c FROM PRODUCTION.TABLE1... [...]
25781 18 20_Re: trigger question0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Fri, 19 Jul 2002 17:37:09 -0500477_us-ascii Hello Tina,
My assumption (perhaps wrong) is that the UDF in question is doing a Signal SQLSTATE to abend the process. Thus, the SELECT in this case is merely the mechanism for invoking the UDF.
Stg
================================================ 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.
25800 126 27_Re: Bind parameter question11_KATHY JONES26_jonesks@GROUPWISE.CCSD.NET31_Fri, 19 Jul 2002 15:39:53 -0700504_US-ASCII That would be acceptable - that way we don't have to change existing programs but new programs would not have to be qualifiied except thru the bind - that way test binds would use the qualifier 'testsys' and prod binds would use the qualifier 'prodsys' but all old programs would still use the hard coded qualifier. Since it is the same in most systems for production and test, that will suffice. I just wanted a way to do this without changing the numerous existing programs. Thanks again [...]
25927 169 22_Re: Separate Subsystem10_Dale Smock20_Dale.Smock@BMSUS.COM31_Fri, 19 Jul 2002 18:17:15 -0500589_iso-8859-1 I'll throw my 2 cents in, since in a previous life I installed and supported early releases of Peoplesoft on DB2. It started out on Gupta's SQL database (ever heard of that one?), then was ported to Oracle and DB2 for larger environments. At the time, we were forced to create a separate DB2 system for the Peoplesoft app, because the numerous objects would put us over the 2,000 dataset limit at the time. Even when the limit was increased to 10,000, it was still an issue, since Peoplesoft convinced the application staff that they needed multiple instances for adequate [...]
26097 63 25_Re: DB2 Precompiler error14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 20 Jul 2002 11:30:13 +1000568_US-ASCII Quoting from the SQL Reference Manual section on the PREPARE statement "[i]n C, the host variable must not be a NUL-terminated string."
There is a sample program in Appl Prog and SQL Guide.
James Campbell
On 19 Jul 2002 at 15:14, Don Cross wrote:
> I am trying to compile some embedded sql in C but can't get aroung > precompiler errors. This is the closest I have gotton. Our language is > Cobol but I am overriding it with HOST(C). Any ideas where I have erred? We > are OS390v2.9 DB2 v6. > > tapi is a stored procedure(not [...]
26161 52 25_Re: DB2 Precompiler error16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Fri, 19 Jul 2002 21:01:05 -0700331_us-ascii Hello !!
I am coding my first CAF program (by the name 'DB2PLN1') in COBOL. I use the following Call to OPEN a connection with DB2:
CALL 'DSNALI' USING FUNCOP SSNM PLAN RET REASON
Where: FUNCOP is defined as PIC X(12) VALUE 'OPEN ' SSNM is PIC X(4) VALUE 'DSNT' PLAN is PIC X(8) VALUE 'DB2PLN1'. [...]
26214 15 55_Daryl G Spletzer/Pr/Consumers/CMS is out of the office.16_Daryl G Spletzer24_dgspletzer@CMSENERGY.COM31_Sat, 20 Jul 2002 03:18:16 -0400403_us-ascii I will be out of the office starting 07/20/2002 and will not return until 07/29/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.
26230 72 25_Re: DB2 Precompiler error14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 20 Jul 2002 18:57:07 +1000358_US-ASCII 1) 0015925312 = x'00F30040'
2) Well yes. Having failed to create an explicit attachment, are you _really_ surprised that you couldn't create an implicit attachment?
3) You might find it usefull, at least for debugging, to read the Diagnosis Guide and Reference and put a //DSNTRACE DD SYSOUT=* into your job
James Campbell [...]
26303 90 25_Re: DB2 Precompiler error14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Sat, 20 Jul 2002 10:56:08 +0200436_- Hello,
for PREPARE, the host variable must be of type VARCHAR, and in C, you declare VARCHARs as structs, containing a length field of type short and a char array of the desired maximum length, for example:
struct {short length; char data [40];} HV0041_prodbs;
and later, for example:
EXEC SQL FETCH CXX0041 INTO :HV0041_auftragsnr , :HV0041_prodbs , :HV0041_gevo , :HV0041_kzdbrm , :HV0041_jobdatum; [...]
26394 37 22_Re: Separate Subsystem17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Sat, 20 Jul 2002 11:57:17 +0200380_us-ascii The ARCHIVE LOG MODE(QUIESCE) also has a TIMEOUT parameter (I'm not quite sure whether it's called TIMEOUT). If you set this to a low value (lower than the SQL Timeout), you won't have a problem with failing transactions. It would be easy to write a REXX to issue the ARCH command and check the result, then repeat (after a suitable delay) if the command timed out. [...]
26432 63 41_Re: DSNDB07 bufferpool and data set sizes14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Sat, 20 Jul 2002 18:44:48 -0400424_ISO-8859-1 - Both DWQT and VDWQT should be set at 90% for starters. Absolutey NOT - unless all your sort can complete in the pool. This guarantees you will slam into SPTH, DMTH, IWTH.
Regards, Joel
Message text written by DB2 Data Base Discussion List >There's also an interesting entry in IBMLink that has some of the more current recommendations for bufferpool & hiperpool settings for DSNDB07: [...]
26496 92 20_Re: trigger question11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Sat, 20 Jul 2002 22:50:42 -0500597_iso-8859-1 Thanks to everyone for confirming my assumptions.
Tina
-----Original Message----- From: Suresh Sane [mailto:data_arch@HOTMAIL.COM] Sent: Friday, July 19, 2002 4:48 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: trigger question
Tina,
Your assumptions are correct. Notice the granularity is statement (not row). If only 1 row is inserted, this is trivial but when multiple rows are inserted with a signgle statement(INSERT INTO ...SELECT FROM or by other means including anotehr trigger), all inserted rows are placed in the transition table NT. [...]
26589 89 20_Re: trigger question11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Sun, 21 Jul 2002 06:15:21 -0400346_ISO-8859-1 Suresh
A slight correction to your otherwise accurate explanation. The UDF will be invoked once for each transition table row that has TOTAL_PRICE > 10000. Using VALUES instead of SELECT would not have the same effect because it would not do the check for TOTAL_PRICE > 10000 and because it would only invoke the UDF once. [...]
26679 46 27_cli c++ heavy memory usage.14_Roman Gavrilov18_romio@IL.ADUVA.COM31_Sun, 21 Jul 2002 07:55:22 -0500578_- Good day to every one, I have a question regarding db2 memory usage. I wrote an xml-rpc server which accepts remote commands to the database like connect, disconnect ,insert delete and etc... The program works in the following way: whenever the server receives a connect request it connects to the database by creating an instance of some db class, creates a unique key that will identify this client for this session and stores the db object in an array. All further operations on the database will be identified by this key for this specific client. i.e. by this key I [...]
26726 102 41_Re: DSNDB07 bufferpool and data set sizes13_Martin Packer24_martin_packer@UK.IBM.COM31_Sun, 21 Jul 2002 16:42:03 +0100322_us-ascii I agree, Joel. I've always said the following...
1) Bias strongly towards sequential (and we all seem to be agreed on that)
2) Avoid all writes IF YOU CAN. If not then dribble them out.
3) Check whether you're seeing a lot of actual read-ins. If you are your pool is probably too small. [...]
26829 79 22_Re: Optimal DASD Calcs12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sun, 21 Jul 2002 21:59:02 +0200362_- Hi,
What I keep in mind is that a "full" 3390 (model-3) has 3339 cylinders. I round it to 3300 (the rest for VTOC / VVDS) And then for DB2 it's simple :
3300*15*12 = 594,000 blocks of 4KB each. (or ~ 2,320MB ~ 2.26GB)
Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 [...]
26909 16 50_Aurora E DellAnno/MUN/Candle is out of the office.16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Mon, 22 Jul 2002 01:00:31 +0200492_us-ascii I will be out of the office starting 19/07/2002 and will not return until 05/08/2002.
I have limited access to my e-mail, but I shall respond to your message ASAP. In the meantime, you may contact me on +49 171 674 6930.
================================================ 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.
26926 44 45_Help Cat/Dir recovery - Initialize necessary?11_Lee Mandell16_DBMSUser@AOL.COM29_Sun, 21 Jul 2002 19:10:00 EDT471_US-ASCII Hi List, Initializing a user Tablespaces after delete/define is not necessary before a recovery. How about Cat/Dir Tablespaces. We delete/define all cat/dir Tablespaces. We ran recovery on the Tablespaces and rebuild all indexes up to sysdbaut with no problems, but when we attempted a rebuild index(all) on sysdbaut we experience an abend resource unavailable reason 00C90094 type 201 name dsndb06.dsnssh01 reason x'00E40305' Any ideas, suggestions, help. [...]
26971 115 20_Re: trigger question11_Suresh Sane21_data_arch@HOTMAIL.COM31_Sun, 21 Jul 2002 18:17:10 -0500359_- Mike,
Agreed and thanks for pointing out the difference. Depending on the action needed (if any large order do something ONCE v/s do something for each large order), invoking it once or multiple times may achieve the same purpose. By changing the granularity to row, even VALUES could achieve the same purpose. Your point is well taken, though. [...]
27087 102 22_Re: Separate Subsystem14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Mon, 22 Jul 2002 11:21:22 +1000567_iso-8859-1 While Oracle does have *some* good features backup and recovery is definitely *not* one of them.
As you say, there is the "cold" backup, where you shutdown Oracle and take an OS level backup of the files, and there is a "hot" backup, where you tell Oracle "I'm doing a backup" - and then take an OS level backup of the files while they're open...and woe betide you if you forget to tell Oracle "I've finished the backup"!! And you need to know where you put the backups, and what they were called in case [...]
27190 37 40_problem : unable to obtain physical lock7_Eric Ng22_ngyh@PUBLICBANK.COM.MY31_Sun, 21 Jul 2002 21:40:32 -0500398_- Hi, we are on OS390 DB2 V5 data sharing, having 2 members D1R1 & D1R2, running on 2 machines S1M1 & S1M2 respectively.
Recently, our backup job abended with 00C20255.
DSNU050I DSNUGUTC - COPY TABLESPACE IB001AD.IBADDRS COPYDDN(COPY03) DSNT500I DSNUGBAC - RESOURCE UNAVAILABLE REASON 00C20255 TYPE 00000210 NAME IB001AD .IBADDRS .00000002
This backup is running on S1M1. [...]
27228 40 35_DB2 Automation Tool Exception Panel12_Raymond Bell17_rbell@NZ1.IBM.COM31_Mon, 22 Jul 2002 16:04:17 +1200324_us-ascii Colleagues,
Could someone do me a favour and e-mail me (or the List; up to you) the top half of panel HAA$EPRU, the 'Update Exceptions Profile Display' panel from the DB2 Automation Tool? The panel we have differs from that described in the User's Guide and I just want to know if anyone else has this. [...]
27269 58 58_Re: AW: questions regarding the Oracle Transparent Gateway12_Raymond Bell17_rbell@NZ1.IBM.COM31_Mon, 22 Jul 2002 16:13:52 +1200347_us-ascii Hi Martin,
We'll persevere with upgrading the OTG to 8.1.7 I think, but nice to know we have this 'get out of jail more-or-less free' card in the shape of the DB2DESCTAB parm if needed. Frankly I don't give a toss if OTG performance suffers; their punishment for going against God and IBM for using Oracle in the first place. [...]
27328 211 16_Re: SQL question13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sun, 21 Jul 2002 23:22:53 -0500590_iso-8859-1 Bernd,
Glad you have answered your 2nd question.
As to your 1st question: - why does the first statement on the top of this mail do an explicit sort for GROUP BY ?
Possible answers:
- Index to avoid sort is not utilized. - Index is utilized however list prefetch is invoked. - Could fall into the category of Walter Janissen's similar problem on DB2-L titled "Why do I get an additional sort?". Sanjeev, Mike Vaughan and myself attempted answers on this one but concluded that it was something that should be brought up with IBM. I was unable to [...]