1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l June 2001, week 5
2 105 15_Re: View Puzzle13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 29 Jun 2001 00:14:12 -0500357_iso-8859-1 Endy,<br><br>If I understand what you are asking, you want to take 1 row of 50 columns, and turn this into 50 rows.<br><br>To do this you need to select the 1 row 50 times (as you have done), or cartesian product the 1 row to a table of 50 rows.<br><br>You still have the matter of manufacturing a select list which has 50 possible columns. [...]
108 60 32_Re: Compression with access path13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 29 Jun 2001 00:31:58 -0500632_us-ascii Duam,<br><br>Compression statistics are used for access path selection.<br><br>The compressed tablespace may result in less I/O (due to more compressed rows per page). Although there is an increase in CPU due to decompression, this is often (not always) offset by the reduction in CPU required to support the I/O.<br><br>There is no index compression, so if the query is index-only or no two rows are found on the same synchronously read data page regardless of compression, then there is no difference or CPU degradation respectively. There is also the issue of whether compression results in a larger tablespace due [...]
169 147 15_Re: View Puzzle10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 29 Jun 2001 15:55:45 +1000389_us-ascii Hi Terry,<br><br>I had an attempt at this yesterday. And came up with this SQL. (I used the SYSCOLUMNS table to generate the values). But then I saw that performancewise it might not be as efficient as the UNION. Hence didnt post it. I was wondering if there was a way to actually generate a table of values without going to SYSCOLUMNS. Thanx for the tip on the WITH clause. [...]
317 15 54_Dan Alex/Mechanicsburg/Contr/IBM is out of the office.8_Dan Alex16_dalex@US.IBM.COM31_Fri, 29 Jun 2001 02:19:03 -0400394_us-ascii I will be out of the office starting June 29, 2001 and will not return until July 9, 2001.<br><br>I will respond to your message when I return.<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
333 95 37_Re: Select Varchar groupby nonvarchar13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 29 Jun 2001 01:17:39 -0500411_us-ascii Chris,<br><br>If you use the SUBSTR or CHAR function to bring the variable character string to a size of 255 or less, it can be used by a column function (or GROUP BY, DISTINCT etc). This may or not be of benefit....<br><br>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 [...]
429 163 15_Re: View Puzzle11_Endy Lambey20_endyl@CENTRIN.NET.ID31_Fri, 29 Jun 2001 13:25:28 +0700555_- Terry & Kals,<br><br>Thank you very much for your help. I've tried your suggestions, but I found out that Terry's is faster, because it didn't to do any joins.<br><br>Thx again.<br><br>>Hi Terry, > >I had an attempt at this yesterday. And came up with this SQL. (I used >the SYSCOLUMNS table to generate the values). But then I saw that >performancewise it might not be as efficient as the UNION. Hence didnt >post it. I was wondering if there was a way to actually generate a >table of values without going to SYSCOLUMNS. Thanx for the tip on the [...]
593 93 38_Re: [Re: Compression with access path]8_duam lee16_duam_lee@USA.NET29_Fri, 29 Jun 2001 00:40:41 MDT546_US-ASCII Thanks Terry for the information. But I had a doubt again. We generally go for elapsed time based on cpu time and i/o time. When there is less number of rows are to be in result set the i/o time + cpu time = minimum elapsed time. When there are more no of records in resultset then minimum elapsed time is cpu time. Update me if I m wrong. I feel only compressing the tablepsaces I mean large tablespaces which have queries based on index access will be benefiacial in case of faster search. If the optimizer selects the tablesapce [...]
687 23 26_Re: create trigger problem15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 29 Jun 2001 03:12:17 -0500563_- William<br><br>I don't know, why you want to do that. But I think you need it. Unfortunately it is not possible in Version 6, I think. First of all, in your trigger definition, the update-statement contains no where-condition, so every row will be updated. The SQL you need looks like: UPDATE table SET UID = (SELECT MAX(UID) + 1 FROM table) WHERE well here you have to code a condition, which uniquely identifies this row. And upon all that, an Update with a subquery containing a reference on the table being updated is not allowed. So I think, you have [...]
711 19 21_Re: CA Fast Utilities14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Fri, 29 Jun 2001 05:02:12 -0500503_iso-8859-1 I may be pointing out the obvious, but... there is an alternative to CA Utilities that does work with DB2 V6 and has excellent support and is faster than IBM. It is called BMC.<br><br>Let me know if you want more details.<br><br>From Edward Vassie...<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
731 45 28_Re: Archive log compression.14_Vassie, Edward21_Edward_Vassie@BMC.COM31_Fri, 29 Jun 2001 05:23:20 -0500419_iso-8859-1 You need to test this in your environment.<br><br>If you have a Sysprog-only DB2, use it for crash-and-burn testing.<br><br>There are two main objectives:<br><br>1) Test that DB2 can restart after a failure and read a compressed Archive log.<br><br>2) Test that the RECOVER utility is happy reading an archived log.<br><br>If you have vendor utilities that you use with an archive log, test these also. [...]
777 26 37_Re: Select Varchar groupby nonvarchar13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 29 Jun 2001 08:07:29 -0400599_iso-8859-1 Hi Chris, The classic way to identify duplicate data is<br><br>Select cola, colb from t group by cola, colb having count(*) > 1<br><br>If you need the varchar column to identify the dups, you need to try the SUBSTR or CHAR as Terry suggests.<br><br><br><br>David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@Compuware.com<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
804 73 22_fixpack3 won't install15_Damien Ponthier27_d.ponthier@CASTSOFTWARE.COM31_Fri, 29 Jun 2001 14:22:29 +0200451_- Hi all,<br><br>I'm trying to upgrade db2 7.1 on RedHat 6.2 to fixpack 3 When I launch installpatch (while connected as root, and all instances are stopped), I get the following error :<br><br>Updating ...<br><br>Updating to db2cliv71-7.1.0-41.i386.rpm ... Preparing... ################################ [100%] file /usr/IBMdb2/V7.1/Readme/en_US/Release.Notes from install of db2cliv71-7.1.0-41 conflicts with file from package db2cliv71-7.1.0-0 [...]
878 76 33_Re: Has Anyone modified DSNTPSMP?12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 29 Jun 2001 05:24:34 -0700406_us-ascii My client has extensively modified DSNTPSMP REXX. Partly to allow multiple group processing with same SP name, partly to enforce shop standards and partly to resolve many of the problems we've encountered with DSNTPSMP.<br><br>Currently they have the source, dbrm and load module going into 3 different sets of libraries depending upon the qualifier. And only certain qualifiers are allowed. [...]
955 24 32_Re: Compression with access path16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Fri, 29 Jun 2001 08:50:13 -0400405_us-ascii Concerning the following statement:<br><br><br><br>"There is also the issue of whether compression results in a larger tablespace due to the compression dictionary."<br><br>This is an extremely minor point, and in almost every case is not a factor. Usually a tablespace is being compressed because it is very large. 16 extra pages for a compression dictionary is not a factor in such cases. [...]
980 34 27_Question for Tuning Experts14_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 29 Jun 2001 07:51:40 -0500350_iso-8859-1 Tuning experts,<br><br>What have you found to be a good rule of thumb when estimating costs per cpu minute or CPU second ? I am sure there are consultants out there who charge based on or provide as part of the service a cost analysis.<br><br>I think I have heard .50/sec in the past but wanted to make sure it wasn't 50/minute, etc. [...]
1015 59 31_Re: Question for Tuning Experts16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 29 Jun 2001 09:29:30 -0400790_iso-8859-1 I stumbled across some chargeback numbers at: http://www.state.nd.us/itd/billing/rates_datapro.html http://www.sc.edu/ars/handouts/rates.html and a comparison of various mainframe 'mis' ratings at: http://www.unisys.com/HW/SERVERS/clearpath/16222.htm<br><br>Hope this helps!<br><br><br><br>Regards, eric pearson NS ITO Database Support<br><br><br><br>-----Original Message----- From: Scott Trometer [mailto:scott.trometer@RCI.COM] Sent: Friday, June 29, 2001 8:52 AM To: DB2-L@RYCI.COM Subject: Question for Tuning Experts<br><br><br><br>Tuning experts,<br><br>What have you found to be a good rule of thumb when estimating costs per cpu minute or CPU second ? I am sure there are consultants out there who charge based on or provide as part of the service a cost analysis. [...]
1075 28 31_Re: Question for Tuning Experts15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Fri, 29 Jun 2001 08:40:10 -0500356_- Hello Scott<br><br>Some years ago there was a session at the IDUG conference in which a concept was introduced, to weighten the different SQL-statement. It was a concept to have some figures for the "average" of that SQL-type. The figures in detail:<br><br>SELECT 5 points OPEN 5 points FETCH 1 point INSERT 9 points DELETE 9 points UPDATE 7 points [...]
1104 85 31_Re: Question for Tuning Experts14_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 29 Jun 2001 08:51:53 -0500560_iso-8859-1 Good info...These indicate costs might run anywhere from .50 to .85/cpu sec.<br><br>Thanks!<br><br>-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Friday, June 29, 2001 8:30 AM To: DB2-L@RYCI.COM Subject: Re: Question for Tuning Experts<br><br><br><br>I stumbled across some chargeback numbers at: http://www.state.nd.us/itd/billing/rates_datapro.html http://www.sc.edu/ars/handouts/rates.html and a comparison of various mainframe 'mis' ratings at: http://www.unisys.com/HW/SERVERS/clearpath/16222.htm [...]
1190 23 35_Recall: Question for Tuning Experts14_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 29 Jun 2001 08:54:26 -0500566_iso-8859-1 Trometer, Scott would like to recall the message, "Question for Tuning Experts".<br><br><br><br>"The sender believes that this E-mail and any attachments were free of any virus, worm, or Trojan Horse when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking remedial action about viruses and other defects. Cendant/RCI is not liable for any loss or damage arising in any way from this message or its attachment." [...]
1214 34 17_Strange RC in DB211_Robert Jans26_robert_jans@ALBERTSONS.COM31_Fri, 29 Jun 2001 08:07:39 -0600361_iso-8859-1 Hi, Gurus! We encountered a very odd error code in 3 of our acceptance jobs last night. I've never seen this before and am trying to discover what could cause this and why. We have just applied DB2 maintenance just prior to this to bring DB2 up to completely current. It is possible this is a maintenance issue. Our sysprog is checking into it. [...]
1249 64 36_Re: DB2 connect and stored procedure15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 29 Jun 2001 10:20:47 -0400411_- Thanks Kals. Based on your message, our programmer was able to get the scale value and put the decimal point in correct place. I do not know whether this is the correct way or just a work around.<br><br>Regards..sibi<br><br>-----Original Message----- From: teldb2kals [mailto:teldb2kals@TELSTRA.COM] Sent: Thursday, June 28, 2001 02:33 AM To: DB2-L@RYCI.COM Subject: Re: DB2 connect and stored procedure [...]
1314 25 22_Limiting SELECT output10_Mathai Joy22_mathaijoydb2@YAHOO.COM31_Fri, 29 Jun 2001 07:25:35 -0700422_us-ascii I am selecting three columns from a table and two of the columns are defined as characters. Since these two columns contains lots of blanks, is anyway I can limit the columns output width to small number like in the Oracle.<br><br>Thanks,<br><br>Mathai<br><br>__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ [...]
1340 18 16_Blobs in UDB 7.113_Villa Horacio17_hvilla@TTI.COM.AR31_Fri, 29 Jun 2001 11:26:58 -0300458_iso-8859-1 Hi, Anybody has an example on how to deal with a blob column to generate a file with Java? My database is on NT. I'm getting all the other column values but an error with the blob column. TIA Horacio Villa<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
1359 13 11_PREFORMAT ?12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 29 Jun 2001 09:40:00 -0400367_- Anyone using the preformat option on load/reorg and has it helped the runtime of heavy insertion?<br><br>Thanks in advance.<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
1373 55 15_Re: PREFORMAT ?0_22_blyons2@CSCPLOENZKE.DE31_Fri, 29 Jun 2001 17:14:14 +0200594_us-ascii Bob, I used this a few months back on several tables with heavy insert rates. I don't have the numbers anymore since it was at another customer, but I remeber that it about halved the elapsed time of the batch application in question, while having insignificant impact on the CPU consumption. The REORG took considerably longer though. Looking at some Strobe output for the batch job , the WAIT-FOR-DB2-SERVICE (I think this was the name, but I could be wrong) had been drastically reduced...the program insert pattern was sequential, stuffing each row at the 'end' of the table. [...]
1429 59 27_Odp: Limiting SELECT output15_Piotr Tarnowski20_TarnowskiP@PROKOM.PL31_Fri, 29 Jun 2001 17:07:19 +0200665_us-ascii Hi Mathai,<br><br>Use: select substr(long_col1, 1, 20)<br><br>or even: select substr(rtrim(long_col1)||' '||long_col2,1,20)<br><br>Best regards, Piotr Tarnowski<br><br><br><br><br><br>Mathai Joy <mathaijoydb2@ Do: DB2-L@RYCI.COM YAHOO.COM> DW: (UDW: Piotr Tarnowski/GDYNIA/PROKOM/PL) 29-06-01 16:25 Temat: Limiting SELECT output Odpowiedz do DB2 Data Base Discussion List<br><br><br><br><br><br><br><br><br><br><br><br><br><br>I am selecting three columns from a table and two of the columns are defined as characters. Since these two columns contains lots of blanks, is anyway I can limit the columns output width to small number like in the Oracle. [...]
1489 28 50_User Exit on DB2 UDB (Solaris) using Perl or Shell19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Fri, 29 Jun 2001 16:50:21 +0100468_us-ascii Guys,<br><br>I'm trying to write a user exit for log handling using Perl (or possibly just a shell script).<br><br>First thing I wanted to do was check what details DB2 was passing through to me. I set up a test database and table and adjusted the log sizes so that a test insert script I've written will cause one new log file to be taken. I put the user exit (called db2uext2) into /usr/IBMdb2/sqllib/bin, then set the DB CFG parameter USEREXIT to ON. [...]
1518 38 15_Re: PREFORMAT ?11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 29 Jun 2001 10:46:10 -0500717_- Yes, have used it and saw about 30-40% improvement in runtime. For heavy insert activity I would strongly recommend it.<br><br>HTH.<br><br>Suresh<br><br><br><br>>From: BOB JEANDRON <BOB.JEANDRON@USDA.GOV> >Reply-To: DB2 Data Base Discussion List <DB2-L@RYCI.COM> >To: DB2-L@RYCI.COM >Subject: PREFORMAT ? >Date: Fri, 29 Jun 2001 09:40:00 -0400 > >Anyone using the preformat option on load/reorg and has it helped the >runtime >of heavy insertion? > >Thanks in advance. > >================================================ >To change your subscription options or to cancel your subscription visit >the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can >be reached at DB2-L-REQUEST@RYCI.COM. [...]
1557 28 24_Stored Proc SQLCode -99110_Chris Kern23_Chris_Kern@VANGUARD.COM31_Fri, 29 Jun 2001 11:58:36 -0400451_us-ascii Listers, I'm recieving the following error on a Stored Procedure. I've got other stored procs that work and don't know why this one is having a problem. Could someone give me an idea of something to look at. I've compared the compiles, linkedits, ect between the working SP's and this one. If somebody can give me some hints that would be great. This SP is being called from a batch Cobol module (OS/390) Version 6 in a WLM environment. [...]
1586 58 28_Re: Stored Proc SQLCode -99116_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 29 Jun 2001 12:07:50 -0400361_iso-8859-1 Usually means you tried to connect to DB2 while you were already connected. A typical way for you to do this is to get an implicit connection by issuing an SQL statement, then attempt to get an explicit CAF connection via CAF CONNECT/OPEN/etc.<br><br>To debug, add to your JCL //DSNTRACE DD SYSOUT=* and it will show you the sequence of events. [...]
1645 183 46_OS/390 select count vs exists data fm omegamon10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Fri, 29 Jun 2001 11:03:10 -0500582_iso-8859-1 All,<br><br>I ran a test that had some vvveeerrryy interesting results. We have an application (dynamic SQL only) that has virtual queues of DB2 data into a fraud system. There are constant updates & inserts into this table during the day, and these virtual queues are drained & filled constantly. The queue manager needs to check every n seconds whether a new row fits into one of the preselected queues that had previously been empty. So, what they had been doing was a select count(distinct account) from table. All they cared was is it zero or not. So, to avoid [...]
1829 64 28_Re: Stored Proc SQLCode -9910_26_JCameron@MSI-INSURANCE.COM31_Fri, 29 Jun 2001 11:10:57 -0500714_us-ascii I'm guessing your link should include DSNRLI instead of DSNALI.<br><br><br><br><br><br><br><br>Chris Kern <Chris_Kern@VAN To: DB2-L@RYCI.COM GUARD.COM> cc: Sent by: DB2 Subject: Stored Proc SQLCode -991 Data Base Discussion List <DB2-L@RYCI.COM ><br><br><br><br>06/29/01 10:58 AM Please respond to DB2 Data Base Discussion List<br><br><br><br><br><br><br><br><br><br><br><br>Listers, I'm recieving the following error on a Stored Procedure. I've got other stored procs that work and don't know why this one is having a problem. Could someone give me an idea of something to look at. I've compared the compiles, linkedits, ect between the working SP's and this one. If somebody can give me some hints [...]
1894 47 31_Page movement between BP and HP6_Chenny39_solaiyappan_chenniappan@MAIL.AMSINC.COM31_Fri, 29 Jun 2001 11:39:08 -0500330_- All,<br><br>Does every page being read into the bufferpool gets moved to the Hiperpool (except for dirty pages)?<br><br>I was looking at the bufferpool stats of BP11 collected between 7am-7pm for 3 days. From the following stats, not every single page read via prefetch(Seq+Dyn+list) and sync i/o made it to the hiperpool. [...]
1942 87 31_Re: Question for Tuning Experts14_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 29 Jun 2001 11:55:39 -0500620_iso-8859-1 Thanks Eric and Walter, Good info. It looks like from the links below that a good range is .50/cpu second up to .85/ cpu second.<br><br><br><br>Regards, Scott -----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Friday, June 29, 2001 8:30 AM To: DB2-L@RYCI.COM Subject: Re: Question for Tuning Experts<br><br><br><br>I stumbled across some chargeback numbers at: http://www.state.nd.us/itd/billing/rates_datapro.html http://www.sc.edu/ars/handouts/rates.html and a comparison of various mainframe 'mis' ratings at: http://www.unisys.com/HW/SERVERS/clearpath/16222.htm [...]
2030 29 21_Re: CA Fast Utilities13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Fri, 29 Jun 2001 11:54:20 -0500418_- When will V7 exploitative utilities be available?<br><br>-----Original Message----- From: Edward_Vassie@BMC.COM [SMTP:Edward_Vassie@BMC.COM] Sent: Friday, June 29, 2001 5:02 AM To: DB2-L@RYCI.COM Subject: Re: CA Fast Utilities<br><br>I may be pointing out the obvious, but... there is an alternative to CA Utilities that does work with DB2 V6 and has excellent support and is faster than IBM. It is called BMC. [...]
2060 207 50_Re: OS/390 select count vs exists data fm omegamon13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 29 Jun 2001 12:04:11 -0500331_iso-8859-1 Missy,<br><br>The problem with the EXISTS is that it is non-correlated, so must be evaluated in full, rather than stopping at first existance. Change it to correlated, and you should see the result you expected. Although if there are zero rows matching, then the count and the exists will not show much difference. [...]
2268 13 27_DB2 TCPIP security on OS39012_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 29 Jun 2001 12:02:00 -0400425_- We have WEB apps in development. They are using dynamic sql. How do you stop the users from accessing tables outside the app. such as with QMF for windows?<br><br>Thanks in advance.<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
2282 35 33_Dynamic SQL - Cache some, not all12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Fri, 29 Jun 2001 12:13:44 -0500610_us-ascii Hello,<br><br>We have a situation where we can use limited, structured, prepared Dynamic SQL from a CICS program.<br><br>If we go this route, I'd be interested in turning on Dynamic SQL caching.<br><br>However, we also have oodles of unstructured half-crazed dynamic SQL hitting the same database from our PowerBuilder front end.<br><br>Question! Can we (through bind options or some other trickery) set up Dynamic SQL caching so that the CICS SQL is cached while the PB SQL is just prepared and processed? Our concern is about loading up the EDM pool with Dynamic SQL from PB that will never be [...]
2318 50 21_Re: CA Fast Utilities16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 29 Jun 2001 13:17:19 -0400539_iso-8859-1 CA told us December at the earliest.<br><br>Regards, eric pearson NS ITO Database Support<br><br><br><br>-----Original Message----- From: John Arbogast [mailto:john.arbogast.cquz@STATEFARM.COM] Sent: Friday, June 29, 2001 12:54 PM To: DB2-L@RYCI.COM Subject: Re: CA Fast Utilities<br><br><br><br>When will V7 exploitative utilities be available?<br><br>-----Original Message----- From: Edward_Vassie@BMC.COM [SMTP:Edward_Vassie@BMC.COM] Sent: Friday, June 29, 2001 5:02 AM To: DB2-L@RYCI.COM Subject: Re: CA Fast Utilities [...]
2369 102 38_Re: [Re: Compression with access path]13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 29 Jun 2001 12:32:18 -0500597_us-ascii Duam,<br><br>Elapsed time is not necessarily I/O + CPU, there are considerations such as parallelism and whether the I/O is sync or async.<br><br>Regardless of the whether the access is via an index or tablespace scan, any savings are dependant on whether the reduction in I/O is enough to offset the increase in CPU for decompression. If you obtained 80% compression then you would expect to decent improvement for a tablespace scan, but of course a single I/O would have a higher CPU cost due to the decompression (although there is more chance of finding the page in the buffer). [...]
2472 34 31_Re: DB2 TCPIP security on OS39012_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 29 Jun 2001 10:36:55 -0700604_us-ascii Why not have the web app signon with a single userid that cannot be used by the users? Then the users wouldn't have access to the tables, only this special ID? If you have something like Tivoli Policy Director, it can automatically supply the userid and password for you. --- BOB JEANDRON <BOB.JEANDRON@USDA.GOV> wrote: > We have WEB apps in development. They are using dynamic sql. How do > you stop > the users from accessing tables outside the app. such as with QMF for > windows? > > Thanks in advance. > > ================================================ > To change your subscription [...]
2507 20 31_Re: DB2 TCPIP security on OS39012_Jim Harrison12_jimh@QIS.NET31_Fri, 29 Jun 2001 13:40:05 -0400401_us-ascii We use user IDs that don't have TSO access so the only way they can get to the data is through the web application. Sorry, don't know the details though.<br><br>At 12:02 PM 6/29/01 -0400, BOB JEANDRON said: >We have WEB apps in development. They are using dynamic sql. How do you stop >the users from accessing tables outside the app. such as with QMF for windows? > >Thanks in advance. [...]
2528 40 26_Alternate catalog in Erwin11_Brown.James24_James.Brown@SUNTRUST.COM31_Fri, 29 Jun 2001 13:36:00 -0400288_- Hi all,<br><br>Does anyone know of a way to point Erwin to an alternate catalog? I want the DA's to be able to pull DDL from a catalog other than SYSIBM.*. I want to avoid using another tool to create the DDL in a text file and have that be the source for the reverse engineering. [...]
2569 29 31_Re: DB2 TCPIP security on OS39012_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 29 Jun 2001 12:59:00 -0400320_- How are users stopped from accessing the same tables from a vendor package, for example QMF for windows?<br><br>>>> DB2-L@RYCI.COM@inter2 06/29/01 12:40PM >>> We use user IDs that don't have TSO access so the only way they can get to the data is through the web application. Sorry, don't know the details though. [...]
2599 62 37_Re: Dynamic SQL - Cache some, not all12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Fri, 29 Jun 2001 13:24:27 -0500483_iso-8859-1 Steve<br><br>DB2 is an all or nothing proposition when it comes to SQL caching and it is also done on a fifo basis.<br><br>However, you can accomplish this with Pool Advisor from BMC Software, Inc. You can check it out at www.bmc.com.<br><br>Tom Moulder (BMC Software, Inc.)<br><br>-----Original Message----- From: Steve Grimes [mailto:Steve_Grimes@AISMAIL.WUSTL.EDU] Sent: Friday, June 29, 2001 12:14 PM To: DB2-L@RYCI.COM Subject: Dynamic SQL - Cache some, not all [...]
2662 33 50_Re: OS/390 select count vs exists data fm omegamon10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Fri, 29 Jun 2001 13:25:00 -0500365_us-ascii Terry, I am in awe! Bowing to the master! I ran the query with your suggestion, & here's the results:<br><br>START TIME INDB2 TIME INDB2 CPU SQL SORTS LOCKS ROWS 10:25:41.612 00:02.47354 .53943 5 0 30 79912 10:24:51.740 00:02.70532 1.04761 5 1 35 229501 10:24:03.752 00:01.28613 .83800 5 0 34 79879 12:40:42.374 0:00.00175 .00144 2 0 5 2 * new test * [...]
2696 16 10_Partitions12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Fri, 29 Jun 2001 13:35:55 -0500488_iso-8859-1 We are DB2/390. Does it help adhoc query performance to partition large tables (>2 million rows) if your mainframe only has one processor assuming your tables are reorganized and runstats run regularly?<br><br>TIA for your help, Louise<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
2713 65 31_Re: DB2 TCPIP security on OS39015_Janardhan Ragam16_sragam@YAHOO.COM31_Fri, 29 Jun 2001 11:36:41 -0700479_us-ascii One thing that I can suggest right away is not granting any authority on tables to an id or group to which the id belongs. Make sure applications - coded to have dynamic or staic sqls - are bound to the database. You can then grant execute on plan/package to the id or group. Now the id or group can execute queries only via the plan/package. Even though the id maybe able to connect to the database via qmf etc., the id cannot run queries outside the application. [...]
2779 61 38_BMC Utilities WAS -> CA Fast Utilities13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Fri, 29 Jun 2001 13:33:38 -0500552_- I really wanted to know about BMC. That's why I responded to Edwards post.<br><br>-----Original Message----- From: Eric.Pearson@NSCORP.COM [SMTP:Eric.Pearson@NSCORP.COM] Sent: Friday, June 29, 2001 12:17 PM To: DB2-L@RYCI.COM Subject: Re: CA Fast Utilities<br><br>CA told us December at the earliest.<br><br>Regards, eric pearson NS ITO Database Support<br><br><br><br>-----Original Message----- From: John Arbogast [mailto:john.arbogast.cquz@STATEFARM.COM] Sent: Friday, June 29, 2001 12:54 PM To: DB2-L@RYCI.COM Subject: Re: CA Fast Utilities [...]
2841 36 14_Re: Partitions16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 29 Jun 2001 14:42:36 -0400538_iso-8859-1 Yes. I/O contention can be reduced, thus allowing better processor utilization.<br><br>Regards, eric pearson NS ITO Database Support<br><br><br><br>-----Original Message----- From: Reid, Louise [mailto:ReidL@CI.FORT-WORTH.TX.US] Sent: Friday, June 29, 2001 2:36 PM To: DB2-L@RYCI.COM Subject: Partitions<br><br><br><br>We are DB2/390. Does it help adhoc query performance to partition large tables (>2 million rows) if your mainframe only has one processor assuming your tables are reorganized and runstats run regularly? [...]
2878 52 9_Max Locks10_Alan Smith25_alancsmith@BTINTERNET.COM31_Fri, 29 Jun 2001 19:53:04 +0100586_us-ascii This may have started happening because the workload for this program has increased. As others have pointed out, the program should be committing to clear any locks. If it's not easy to change the program, there are some other things to look at: System Parameters NUMLKTS (number of locks per tablespace) and NUMLKUS (number of locks per user) may be too close together. I've found that if NUMLKUS is set to ten times NUMLKTS, lock escalation is far more likely to happen then hitting the abend. Whoever's in charge of your system may not want to change these values. The [...]
2931 79 31_Re: DB2 TCPIP security on OS39015_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 29 Jun 2001 14:57:12 -0400607_- What about using Dynamic Rules = Bind for the ODBC package which execute dynamic SQL. Then give the owner of the package all the access.<br><br>-----Original Message----- From: Janardhan Ragam [mailto:sragam@YAHOO.COM] Sent: Friday, June 29, 2001 01:37 PM To: DB2-L@RYCI.COM Subject: Re: DB2 TCPIP security on OS390<br><br><br><br>One thing that I can suggest right away is not granting any authority on tables to an id or group to which the id belongs. Make sure applications - coded to have dynamic or staic sqls - are bound to the database. You can then grant execute on plan/package to the id or [...]
3011 20 21_NOT NULL WITH DEFAULT13_Martin, Patti33_Patti_Martin@MAIL.DOR.STATE.MO.US31_Fri, 29 Jun 2001 14:45:10 -0500404_iso-8859-1 Being kinda new to DB2, I have a "picky" question about column specifications. What is the difference between 'NOT NULL WITH DEFAULT "value"' and just 'DEFAULT "value"'? If just 'DEFAULT "value"' is the column specification doesn't that imply that the value would never be null?<br><br>Patti Martin Missouri Department of Revenue Database Administration patti_martin@mail.dor.state.mo.us [...]
3032 123 31_Re: DB2 TCPIP security on OS39014_Bill Gallagher28_BILL.GALLAGHER@PHOENIXWM.COM31_Fri, 29 Jun 2001 15:45:25 -0400337_us-ascii IF you're using RACF security for DB2, be aware that DYNAMICRULES(BIND) doesn't work the same as it does for native DB2 security. IBM's aware of this, and it has been added as a "requirement", but is not on any schedule to be addressed in the immediate future.<br><br>Bill Gallagher, DBA Phoenix Life Insurance Enfield, CT [...]
3156 48 26_Re: Overhead for IFCID 19813_Mohammad Khan20_mkkhan88@HOTMAIL.COM31_Fri, 29 Jun 2001 15:06:50 -0500501_- Hi Joel, Thanks for your input.<br><br>>It depends on what you want to do with the data. If your intent is buffer<br><br>The intent is to get some idea (which is better than NO idea that we have now) of how the objects are being accessed by sampling over a period of time. I do agree that this is no match (and I don't even dare to call it so) for what the commercial tools have to offer but one has to play with the cards one has. After all who buys a tool on the recomendation of a "contract [...]
3205 41 25_Re: NOT NULL WITH DEFAULT16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 29 Jun 2001 16:10:52 -0400439_iso-8859-1 NOT NULL makes the column so that no SQL can set the column value to NULL. Not specifying NOT NULL makes the column nullable (and costs you an extra byte for the null indicator).<br><br>Regards, eric pearson NS ITO Database Support<br><br><br><br>-----Original Message----- From: Martin, Patti [mailto:Patti_Martin@MAIL.DOR.STATE.MO.US] Sent: Friday, June 29, 2001 3:45 PM To: DB2-L@RYCI.COM Subject: NOT NULL WITH DEFAULT [...]
3247 94 31_Re: DB2 TCPIP security on OS39012_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 29 Jun 2001 15:12:00 -0400609_- Interesting idea...In this case the ODBC package is DB2 Connect...You would have to bind the DB2 Connect packages with dynamic rules bind in a Collection using an owner with authority to use DB2 connect as well as ability to access all application tables. Each user can then be granted execute on the package. The WEB application would then have to set the current packageset as the new collection id. The problem would come in where you would have multiple security classes, ie. update users and read only users...you would have to have different collections with different owners having the separate [...]
3342 45 25_Re: NOT NULL WITH DEFAULT12_McKown, John22_JMckown@HEALTHAXIS.COM31_Fri, 29 Jun 2001 15:11:49 -0500457_- No. The "WITH DEFAULT" specifies what the column will be if not otherwise specified. The value could be specified as NULL. The "NOT NULL" specifies that the column cannot contain a NULL value, even if specifically stated in the SQL.<br><br>---------------------------------------------------------------------- John McKown HealthAxis<br><br>All opinions are my own and are not the opinions of my employer. 2+2=5 For sufficiently large values of "2". [...]
3388 87 25_Re: NOT NULL WITH DEFAULT12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Fri, 29 Jun 2001 15:32:44 -0500430_us-ascii Hello,<br><br>NOT NULL WITH DEFAULT makes the column not nullable.<br><br>DEFAULT leaves the column nullable, even though a default value will be used for inserts.<br><br>The implications include (among others I'm sure) are that you should be ready to code indicator variables in COBOL for any nullable column.<br><br>NOT NULL WITH DEFAULT, and DEFAULT will allow you to leave the column out of an INSERT statement. [...]
3476 30 25_Re: NOT NULL WITH DEFAULT13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 29 Jun 2001 16:38:38 -0400362_iso-8859-1 Hi Patti,<br><br>Say C1 is Char(1) Not Null Default 'Y' C2 is Char(1) Default 'Y'<br><br>The difference is that the column C1 can never be set to null while C2 could be.<br><br>For example, Update table Staff Set C2=null where ....<br><br>As you surmised, however, it will never implicitly be set to null by omitting it from an insert statement. [...]
3507 147 31_Re: DB2 TCPIP security on OS39015_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 29 Jun 2001 16:41:11 -0400556_- I do not know what is the problem, but I did set this up. I have two ODBC package, one used for application and one for Developers to run dynamic queries. The one for the application I used DYNAMICRULES(BIND). So application can access all the table. But for the developers it is DYNAMICRULES(RUN), so the access is based on the user-id. If the user do not have access on the table, then they cannot select it. We use SQLserver ISQL and Remote server concept to execute developer dynamic query. We do not have QMF for windows. We use RACF security. [...]
3655 14 21_DB2 Connect Profiling12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 29 Jun 2001 16:01:00 -0400361_- Anyone using Profiling to create packages from Dynamic ODBC SQL? How is it? Easy, complete?<br><br>Thanks in advance.<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3670 20 21_Re: CA Fast Utilities12_Scott Fauque19_scott.fauque@CA.COM31_Fri, 29 Jun 2001 16:39:12 -0500390_- Aside: Eric and others,<br><br>Database Analyzer (PDA) has successfully processed 'LARGE' objects for a couple years so it can't be that general scenerio. Perhaps you are talking about objects with EA (Extendeded Addressability) attribute but PDA also currently handles those. I'm sure our support group will help you narrow down the problem as to what is causing the GETMAIN issue. [...]
3691 43 31_Re: DB2 TCPIP security on OS39012_rl_cotterill24_rl_cotterill@BIGPOND.COM31_Sat, 30 Jun 2001 13:16:57 +1000568_us-ascii Bob, What stops them from using the DB2 Command line interface or QMF for indows or one of the many other tools<br><br>Richard<br><br><br><br>BOB JEANDRON wrote:<br><br>> How are users stopped from accessing the same tables from a vendor package, > for example QMF for windows? > > >>> DB2-L@RYCI.COM@inter2 06/29/01 12:40PM >>> > We use user IDs that don't have TSO access so the only way they can get to > the data is through the web application. Sorry, don't know the details > though. > > At 12:02 PM 6/29/01 -0400, BOB JEANDRON said: > >We have WEB [...]
3735 16 50_William D Trago/ISS/HQ/FHLMC is out of the office.10_Bill Trago33_William_D_Trago_Jr@FREDDIEMAC.COM31_Sat, 30 Jun 2001 01:03:47 -0400460_us-ascii I will be out of the office starting 06/29/2001 and will not return until 07/02/2001.<br><br>I will respond to your message when I return. If action needs to be taken immediately, Please contact Barbara Swart.<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3752 21 39_SQL Querry... Help required urgent.....13_Mathew, Sithu30_sithu.mathew@BLR.HPSGLOBAL.COM31_Sat, 30 Jun 2001 10:19:25 +0500488_- Hi List Members,<br><br>How can I select the first row from the table without using a cursor???........ Can anybody give the SQL statement for achieving this....<br><br><br><br>Suggestions are well appreciated....<br><br>Thanks & Regards Mathew<br><br>================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3774 19 32_To measure the CPU and I/o time.9_john king24_john_king@REDIFFMAIL.COM31_Sat, 30 Jun 2001 09:29:55 -0000397_- Good day listers, here I m posting a question to list for quick answer. How I would measure the CPU and I/O time for a particular query. Is there any tools availavble or any command in db2 is there to know it.<br><br>Your's John<br><br>____________________________________________________ Buy Feng Shui Package for Rs. 151/- only, at http://shopping.rediff.com/shopping/fengshui_mailer.htm [...]
3794 46 26_Re: Another Oracle barrage11_Grant Allen22_grant@TOWERSOFT.COM.AU31_Sat, 30 Jun 2001 21:48:20 +1000541_- Agarwal, Ashish[SMTP:Ashish.Agarwal@HPSGLOBAL.COM] wrote: > > Hii Linda, > > let me tell what i know As far as Oracle is concerned, u > > have one log per database which is equivalent to Db2's subsystem log but > > > that doesnt mean that Oracle has only one database. U can hav multiple > > databases per instance and and multiple instances too in Oracle just > like in > > DB2. > This isn't true, Ashish. You can have one and only one database in use per Oracle instance. This is one of the fundamental differences between the two. [...]
3841 78 26_Re: Another Oracle barrage17_Sanfilippo, David34_David.Sanfilippo@GMACINSURANCE.COM31_Sat, 30 Jun 2001 08:19:15 -0400660_iso-8859-1 You can have multiple schemas in an Oracle instance, and an Oracle schema roughly equates to a database in DB2.<br><br>Dave Sanfilippo GMAC Insurance<br><br>-----Original Message----- From: Grant Allen [mailto:grant@TOWERSOFT.COM.AU] Sent: Saturday, June 30, 2001 6:48 AM To: DB2-L@RYCI.COM Subject: Re: Another Oracle barrage<br><br><br><br>Agarwal, Ashish[SMTP:Ashish.Agarwal@HPSGLOBAL.COM] wrote: > > Hii Linda, > > let me tell what i know As far as Oracle is concerned, u > > have one log per database which is equivalent to Db2's subsystem log but > > > that doesnt mean that Oracle has only one database. U can hav multiple > > databases [...]
3920 60 26_Re: Another Oracle barrage11_Grant Allen22_grant@TOWERSOFT.COM.AU31_Sat, 30 Jun 2001 23:07:50 +1000540_- Sanfilippo, David[SMTP:David.Sanfilippo@GMACINSURANCE.COM] wrote: > > You can have multiple schemas in an Oracle instance, and an Oracle > schema > > roughly equates to a database in DB2. > > > > Dave Sanfilippo > > GMAC Insurance > Sorry Dave, this claim is another of the great Oracle lies (nothing personal ... I mean the Oracle propagandists). An Oracle schema is NOT equivalent to a DB2/Sybase/Informix/SQL Server db. Sure there are a few things you can mimic ... but there are a lot of limitations, or outright impossibilities [...]
3981 45 36_Re: To measure the CPU and I/o time.17_Esmaiel Nokhodian15_dbaen@TEXAS.NET31_Sat, 30 Jun 2001 08:29:51 -0500401_iso-8859-1 Hi John,<br><br>BMC Software (1-800-841-2031) has the perfect tool for measuring a Query cost (CPU , I/O time), call APPTUNE & SQL Explorer.<br><br>Good luck !<br><br>-Essy.<br><br>----- Original Message ----- From: john king <john_king@REDIFFMAIL.COM> Newsgroups: bit.listserv.db2-l To: <DB2-L@RYCI.COM> Sent: Saturday, June 30, 2001 4:29 AM Subject: To measure the CPU and I/o time. [...]
4027 52 31_Re: DB2 TCPIP security on OS39012_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Sat, 30 Jun 2001 09:24:00 -0400299_- Yes, that's the ?, how do you stop the access from outside the app if the user id they are using has authority to the tables.<br><br>>>> DB2-L@RYCI.COM@inter2 06/30/01 12:22PM >>> Bob, What stops them from using the DB2 Command line interface or QMF for indows or one of the many other tools [...]
4080 141 26_Re: Another Oracle barrage17_Sanfilippo, David34_David.Sanfilippo@GMACINSURANCE.COM31_Sat, 30 Jun 2001 10:51:56 -0400323_iso-8859-1 My only experience was with Oracle for OS/390, and I was so impressed that I reversed field and came running back. I have no desire to supply pro- or anti- Oracle propaganda. However, I stand by my original statement that an Oracle schema, while certainly not equivalent, roughly equates to a DB2 database. [...]
4222 72 43_Re: SQL Querry... Help required urgent.....12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 30 Jun 2001 17:49:07 +0200360_Windows-1252 Hi, How do you define "the first" ? Do you mean "the 1st in a known sequence" - like in a running number where you mean the smaller one? (tha same applies the records orderd by date/time/timestamp ) - next thing is "what about duplicates?" - if you may duplicates (read "having the same key") do you want to get both? only one? (which one?). [...]