1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l April 2000, week 1 2 28 40_Batch Multitasking Application using CLI11_Gil, Victor23_Victor.Gil@NEONSOFT.COM31_Fri, 31 Mar 2000 18:05:36 -0700314_- Hello DB2 experts!

One of our existing batch application successfully multitasks using embedded SQL and direct calls to CAF. We now need to enhance it with an ability to support a new version of data, access to which is encapsulated in a brand new statically linked subroutine that issues CLI calls. [...] 31 58 51_No. row of export function not equal using count(*)22_Suda Petchrungratsamee15_sudap@SCB.CO.TH30_Sat, 1 Apr 2000 18:09:56 +0700288_windows-874 Hi all, I have some trouble about db2 v.5.0 on unix. I use "select count(*) from xxx.table1" It returns no rows ( 265203). When I use "export to xx.del of del select * from xxx.table1". It returns no rows that not equal "select count(*) from xxx.table1" Thanks in advance. Or. 90 29 23_Software Level Question24_The Fillmore Group, Inc.18_fillmore@CLARK.NET30_Sat, 1 Apr 2000 19:07:11 -0800789_us-ascii I have a DB2 UDB for Windows NT V6.1 CD-ROM that is externally labelled "beta". The person who gave it to me swears it is "gold" code. If I install it, how can I determine for sure whether it's beta or gold?

TIA, Frank



+-------------------------------------+--------------------------------------+ | Frank C. Fillmore, Jr. | Voice/Fax: 410.465.6335 | | The Fillmore Group, Inc. (TFG) | Class registration: 800-TFG-RDBMs | | 3213-A Corporate Court | E-mail: fillmore@ws1.tfg-rdbms.com | | Ellicott City, Maryland 21042-2247 | URL: http://www.thefillmoregroup.com | | USA | | +-------------------------------------+--------------------------------------+ | DB2 Family, Oracle, Client/Server, Distributed Database | | "Relational Database Solutions"(sm) | [...] 120 215 53_Re: Recovery - appreciate feedback on how to do wiser15_Toine Michielse18_vndobtm@US.IBM.COM30_Sat, 1 Apr 2000 14:26:59 +0000457_us-ascii Hello,

In addition to the URCHKTH.... I wrote a monitor that will report long running UR's based on wallclock time. Let me know offline if you are interested.

Regards,

Toine Michielse DB2 S/390 Advocate Santa Teresa Laboratory Mobile: +31 6 537 23 256 Email: vndobtm@us.ibm.com Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS



Gerald Bustamente @RYCI.COM> on 03/29/2000 07:42:02 PM [...] 336 25 28_Datasharing Catalog Recovery16_Ouellette, Lee R21_OUELLETTELR@AETNA.COM30_Sun, 2 Apr 2000 13:29:25 -0400586_- Would like to know of other people's experience with Datasharing Catalog Recovery.

We thought that maintenance (resizing of DB2 catalog) could be performed in 1 member, with all other members down. Following the cookbook approach in the Utility Guide for RECOVER TABLESPACE & 'Recovering Catalog and Directory Objects' things worked fine until encountering the RECOVER TABLESPACE DSNDB06.SYSGROUP. This particular jobstep / (& future job attempts) did not seem to be executing (no CPU / I/O ) until all members of the DataSharing Group were up and available. Coincidence? [...] 362 37 21_Re: Shark DASD & DB2.12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Sun, 2 Apr 2000 21:01:01 +0200690_x-user-defined Hi,

What's the size of the NVS on those 16GB cache - or is it 8+8 ?

Isaac Yassin

"Edward(Ed) J. Finnell,III" wrote: > > There were new announcements on Tuesday regarding the Sharks and the new > "F" models, internally about 75% faster transfer, CACHE upped to 16GB, > and some interesting connections for SAN solutions. Further information > as to "Feature" delivery schedules for the coming months. > www.ibmlink.ibm.com click on Announcements. > > ================================================ > 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 [...] 400 65 35_Re: -206 SQLCODE after System Crash12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Sun, 2 Apr 2000 21:09:29 +0200620_x-user-defined Hi,

Try to rebuild all catalog/directory indexes.

Isaac Yassin



BILL_GALLAGHER@PHL.COM wrote: > > Hi, > > We experienced a rather severe and ugly system outage yesterday when somebody > accidentally hit the big "emergency power off" button in the data center > yesterday. We finally got all our systems back up after a couple of hours, but > we are now experiencing a very unusual problem in our test DB2 data sharing > subsystems. > > All the DB2 subsystems came up normally, although a number of tablespaces and > indexes were in GRECP and LPL status. This includes the [...] 466 45 36_Re: What is best DB2 Monitoring Tool12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Sun, 2 Apr 2000 21:14:49 +0200543_x-user-defined Hi, We're using omegamon/db2 (version 500 to be replaced by 510 in few months). In previous site I've used both tmon and insight. I prefer omegamon.

Isaac Yassin

ajay kumar wrote: > > Hi > > We are using DB2 V5R1 and started to use for production environment, I would > like to know what is the best monitoring tool to check the DB2 threads, > performance and Lock & Latch information. > Would like to use the tool to kill the long standing threads too. > > Is DB2 PM good ?.. > > Thanks in Advance > Karhtik [...] 512 92 21_DB2 versus SQL Server14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Sun, 2 Apr 2000 14:53:19 -0400395_x-user-defined All, I am posting this as a service. This was put together by others. But then again, many of us have been making presentations on this in public and conferences. This just says it very straight forward. ============================================= Top 10 Reasons Customers Choose DB2 UDB over Microsoft SQL Server Opinion and analysis by a group of independent consultants [...] 605 15 21_Re: Shark DASD & DB2.25_Edward(Ed) J. Finnell,III23_efinnell@SEEBECK.UA.EDU30_Sun, 2 Apr 2000 15:08:20 -0500348_us-ascii Looked again. Don't see NVS mentioned. Maybe as we get more orders, more information will appear.

================================================ 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. 621 48 15_dbrm to package14_Hans Dingerdis21_dingerdis@SIONELL.COM30_Mon, 3 Apr 2000 00:27:05 +0200207_iso-8859-1 Hello all, I've been willing to get from dbrm's to packages but till up now could not calculate the real benefits. I now the costs. So Q is what are the benefits to justify the costs. Regards Hans 670 40 19_regarding updation.17_ravi kumar hassan18_ravibh@HOTMAIL.COM28_Sun, 2 Apr 2000 20:30:32 PDT331_- Hi Folks,

we are using DB2 V4. i have a question on updation with cursor. i am using FOR UPDATE OF in the declare cursor for updating a table by joing two table. can i do this way

declare cursor p1 for select A.C4,A.C5,B.M3,B.M4 from DEVL.A, DEVL.B WHERE A.C1 = B.M1 AND A.C2 = 'VALUE' FOR UPDATE OF B.M1,B.M2 [...] 711 20 60_Reading a inserted record before commit from another process8_madhavan16_madhavan@INF.COM30_Mon, 3 Apr 2000 10:19:01 +0530507_- Hi,

One user inserts a record into a table but it is not yet committed. Another user tries to read a record from the same table with the same primary key value. Will the second user get sqlcode for record locked or sqlcode of 100 ?

Regards Madhavan.

================================================ 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. 732 100 55_Re: No. row of export function not equal using count(*)11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU30_Mon, 3 Apr 2000 15:15:23 +1000255_- There is a good chance that one of your indexes on that table is corrupted - DB2 will normally use an index for the first query, but has to scan the whole table for the second query. Try a reorg on the table and see whether that fixes the problem. [...] 833 48 64_Re: Reading a inserted record before commit from another process14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Mon, 3 Apr 2000 15:21:54 +1000441_iso-8859-1 Madhavan,

If the second user is using UR they might get a row, or might get +100 depending on the actual timings of when things happen.

Otherwise, the second user will wait. -If the first user holds the lock for 'too long', the second user will eventually get sqlcode -9xx, probably with 00c9008e reason code. - otherwise (ie the first user commits before the timeout period), the second user will get the row. [...] 882 110 23_Re: regarding updation.10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Mon, 3 Apr 2000 04:33:51 GMT389_us-ascii Hi Ravi, As i know FOR UPDATE OF clause cann't be used when you are joining two or more tables.I have tried it and got sqlcode -510 during the bind time. If you change your query and make it using EXISTS clause i think you can use FOR UPDATE OF but in this case you cann't select the columns from the inner tables. For example in your case query can be changed like this :-- [...] 993 36 39_Does CPU upgrade affect DB2 optimizer ?11_Michael Lim24_TSMLIM@HKCLEARING.COM.HK30_Mon, 3 Apr 2000 14:51:56 +0800344_US-ASCII Hello,

We have a plan that was last bound in Aug 99. We happened to rebind it (no SQL changes) in the test environment, with production statistics, and ended up with a poor access path. The production statistics in the production DB2 catalog are patched with the same values all the time, so we know they have not changed. [...] 1030 50 36_Re: What is best DB2 Monitoring Tool17_Erkki Søndergaard20_esonderg@SCA.CSC.COM30_Mon, 3 Apr 2000 10:16:35 +0200356_us-ascii You'll probably get as many answers as there's DB2 monitors, but I've been very happy with Insight/DB2 for many years. It optionally includes 24x7, a tool that can (among many other things) kill threads. It's also very customizable as you have access to all the documented DB2 trace records & fields and can write your own reports from that. [...] 1081 62 43_Re: Does CPU upgrade affect DB2 optimizer ?17_Erkki Søndergaard20_esonderg@SCA.CSC.COM30_Mon, 3 Apr 2000 10:29:45 +0200508_us-ascii Hi Michael,

I've just spent quite a bit of time researching this subject and have had the opertunity to bind the exact same DBRMs on 3 different CPU types. My conclusion is that CPU type has more impact than bufferpool sizes when the optimizer selects access paths. This is most notable on more complex sql. Remember that the optimzer is focused on elapsed time rather than ressource usage and will sometimes do a TS scan with prefetch rather than use an index and synch IOs for the TS. [...] 1144 150 55_Re: No. row of export function not equal using count(*)22_Suda Petchrungratsamee15_sudap@SCB.CO.TH30_Mon, 3 Apr 2000 16:33:40 +0700643_iso-8859-1 Hi Ulrich, I already reorg & runstats on tables . It can't solve this problem. Thanks in advance. Or.

-----Original Message----- From: KUHN,Ulrich Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Monday, April 03, 2000 12:18 PM Subject: Re: No. row of export function not equal using count(*)



There is a good chance that one of your indexes on that table is corrupted - DB2 will normally use an index for the first query, but has to scan the whole table for the second query. Try a reorg on the table and see whether that fixes the problem. [...] 1295 149 55_Re: No. row of export function not equal using count(*)13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Mon, 3 Apr 2000 15:03:48 +0530440_us-ascii



hello I would suggest something... It might happen that there are some broken links... even I faced this problem .. What u can do is unload the data into flat file.. drop the tablespace and then create the table again and load from the flat file ... things will work fine... just check thru explain .... else ... before this verfiy in devp. subsystem by creating the tablespace and table and bind to that .... [...] 1445 79 24_Re: packages and pklists12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Mon, 3 Apr 2000 10:45:00 +0100378_- Ooops, quite right it is a typo, at least it shows someone reads my mails.

Colin. ---------- From: Piontkowski Michael ML To: 'Colin.Allen@ABBEYNATIONAL.CO.UK' Subject: RE: [DB2-L] packages and pklists Date: 31 March 2000 20:08

Colin -

I assuming your statement "...one collection per package..." is a typo?. Should it be "one collection per plan" ? [...] 1525 35 19_Re: dbrm to package12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Mon, 3 Apr 2000 11:09:00 +0100446_- The big winner for most sites was that a single program change doesn't need the whole plan bound, just the package. This means that most development binds can be done on the fly without locking everyone out.

There are also lots of management benefits because of versioning and the ability to block software releases into different collections. I guess other sites will be able to describe how they take advantage of these features. [...] 1561 63 43_Re: Does CPU upgrade affect DB2 optimizer ?0_19_mike.holmans@BT.COM30_Mon, 3 Apr 2000 11:23:51 +0100491_- Yes. The optimizer tries to guess which will be the quickest accesspath. If the CPU speed increases, then a more CP-intensive access method may work out cheaper than one which used less CPU but does more I/O on a slower processor.

That's why, even if you port your production stats to the test system, you may still not get a complete prediction of your production accesspaths if the processors on production and test are significantly different in processing characteristics. [...] 1625 71 29_Re: Osolete SYSIBM Databases?12_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE30_Mon, 3 Apr 2000 13:13:16 +0200430_- After dropping the "obsolete" DSNDDF we got big problems QMF (version 3.3.0). We were bound to recreate the old DDF table. This QMF version is for both DB2 V4 and V5 and may need to be able to access both the old and the new DDF table. (Or then we have something wrong in our QMF customisation.) Anyway, wise of the accident we decided to wait until DB2 and QMF V6 before dropping any of the obsolete system tables. /Seppo [...] 1697 39 25_I/O Hotspots After Reorgs12_Oliver Speed23_GAIASystems@BIGPOND.COM30_Mon, 3 Apr 2000 19:36:01 +0800403_iso-8859-1 If anyone could point me in the right direction on this problem, it would be much appreciated.

After weekly Reorgs, we sometimes experience severe I/O hotspots on some particular packs, after the Reorg process has rebuilt the DB2 files on different packs. As we are using STOGROUPs, we don't appear to have any immediate control of where the files get moved to, during the Reorg. [...] 1737 155 29_Re: Osolete SYSIBM Databases?0_18_mebert@AMADEUS.NET30_Mon, 3 Apr 2000 13:58:13 +0100517_us-ascii We also have QMF 3.3.0 & DB2 V5. When I reorged our DB2 Cat/Dir about 8 months ago, I identified several TSs & IXs that were obsolete in V5, DSNDDF being one of them. I DROPped them without problems observed anywhere. Of course I first HMIGRATEd the VSAM datasets for 2 weeks - nobody recalled them; then I also STOPped the TSs & IXs for another 2 weeks - still no complaints; only then did I drop them - not a certain method but quite useful. We have only the V5 TSs since then. So it does look like a [...] 1893 99 30_Re: Obsolete SYSIBM Databases?12_Sue Janowitz18_SJanowitz@NEFN.COM30_Mon, 3 Apr 2000 08:12:57 -0400362_us-ascii After dropping DSNDDF, you need to rebind QMF in order to avoid problems. (I'd read about that on this list, so was prepared when we migrated to version 5. I used the standard QMF bind JCL, and things went smoothly.)

Sue Janowitz DB2 Systems Administrator New England Financial Information Systems Boston, MA 617-578-2053 SJanowitz@nefn.com [...] 1993 74 29_Re: I/O Hotspots After Reorgs17_Erkki Søndergaard20_esonderg@SCA.CSC.COM30_Mon, 3 Apr 2000 14:15:28 +0200593_us-ascii For a permanent solution I would recommend using more smaller STOGROUPS so datasets won't move around as much. DB2 itself allocates datasets on the first volume within a STOGROUP that has enough freespace to service the allocation request. First in this connection refers to the order volumes has been added to the stogroup. That means that if you remove and then add a volume to the same STOGROUP it'll be the last candidate when allocating new datasets. Now you didn't specify your DB2 version, but if your DB2 version allows it, you could use the REUSE parameter on the reorg [...] 2068 37 36_Re: DB2 Connectivity with SNA Server11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Mon, 3 Apr 2000 08:19:31 -0400536_us-ascii To support the statement that you 'no longer need DB2 Connect', I believe entails placing SNA Server on each PC for a direct connection to the mainframe. If you want to allow just one point of reference, then you'll opt for DB2 Connect EE running on just one box (whether that's AIX or NT or ??) and configure all PC connections to DB2 Connect on that box. This buys you economy of scale and effort in that you won't need SNA Server on each PC that needs to get to the DB2 Connect machine. You also only need to customize [...] 2106 70 29_Re: I/O Hotspots After Reorgs15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM30_Mon, 3 Apr 2000 08:16:35 -0400565_iso-8859-1 If you are on DB2 V4.1 for OS/390 or above with PTF PQ14636 applied, you can use the REUSE parameter on your REORG and it will not do the underlying delete/define of the DB2 tables. This will also save on REORG run times too.

The other part was for the high growth tables with heavy contentions, I had my storage administrators create me a "special" SMS storage group and for those few (there are four) that I need to control, I do a IDCAMS delete/define using the volume reference and the new SMS special storage class that gives me control [...] 2177 77 36_Re: DB2 Connectivity with SNA Server13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Mon, 3 Apr 2000 17:51:28 +0530275_us-ascii Does that mean that we can replace an ODBC application using DB2 Connect with OLE DB server/SNA server ... how can we achieve this... I don't feel we require a SNA server in every machine. We can have SNA client and then this can talk TCPIP to the SNA server . [...] 2255 107 32_Re: Datasharing Catalog Recovery0_18_mebert@AMADEUS.NET30_Mon, 3 Apr 2000 14:33:49 +0100580_us-ascii Glad to oblige. Unfortunately my experience with datasharing is currently limited to a 2-day introduction course. But from your description I would assume that the problem was disk, not DB2 related. Otherwise you should have gotten some DB2 error code. Also, there is nothing special about SYSGROUP that I can see. Maybe you can give more details where the job failed, and which steps were executed before (JCL)? Did the job end with an error, or did you cancel it - when? Was there a swap-out reason code (SR) in SDSF.DA? Messages in the job log/system log? Etc... [...] 2363 20 41_DB2 Connect EE for Windows NT version 6.115_Tjader, Polly A20_patjader@EASTMAN.COM30_Mon, 3 Apr 2000 08:48:45 -0400326_- We just upgraded software this weekend and the NT DB2 service has been up and down. Are there any known problems that we should be aware of? I confess that I haven't even looked at the log to see what the errors are because the server is in another building. I wanted to get this note out right away to get your input. [...] 2384 140 62_More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH"0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Mon, 3 Apr 2000 07:57:37 -0500159_us-ascii Sorry to keep hammering this topic but, as I continue to test, my results consistently tell me that VPSEQT and HPSEQT are thresholds that DB2 does not 2525 229 53_Re: Recovery - appreciate feedback on how to do wiser17_Gerald Bustamente20_ggbusta@LANDSEND.COM30_Mon, 3 Apr 2000 08:31:00 -0500650_us-ascii Toine,

Thank you for the courtesy of your reply. Yes, I would be interested in the monitor you wrote. Any information would be greatly appreciated.

Thank you.

Sincerely,

Jerry Bustamente Lands' End 608-935-4815

Toine Michielse wrote:

> Hello, > > In addition to the URCHKTH.... I wrote a monitor that will report long > running UR's based on > wallclock time. Let me know offline if you are interested. > > Regards, > > Toine Michielse > DB2 S/390 Advocate > Santa Teresa Laboratory > Mobile: +31 6 537 23 256 > Email: vndobtm@us.ibm.com > Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS > [...] 2755 100 29_Re: Osolete SYSIBM Databases?0_17_sjvagnier@AEP.COM30_Mon, 3 Apr 2000 10:08:08 -0400464_us-ascii We are currently running with DB2 for OS/390 V5 Putlevel 9912, QMF for OS/390 V3.3.0 Putlevel 9907 and I am not having any problems with Database DSNDDF being dropped. I believe DSNDDF was converted over to using DSNDB06 during V5 migration.

Regards, Steve Vagnier









"Nurmi, Seppo" on 04/03/2000 07:13:16 AM

Please respond to DB2 Data Base Discussion List [...] 2856 110 36_Re: DB2 Connectivity with SNA Server14_Philip Gunning20_pgunning@BOSCOVS.COM30_Mon, 3 Apr 2000 10:13:22 -0400365_iso-8859-1 V, Does SNA server OLE DB talk DRDA? If it does then you can probably use it. All SNA or TCP/IP will do is get you connectivity, you need DB2 Connect to implement DRDA, specifically Application Requestor services. HTH Phil

Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS [...] 2967 66 43_Re: Does CPU upgrade affect DB2 optimizer ?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 3 Apr 2000 10:45:30 -0400539_us-ascii Michael

It can but in general majority of the access-path will not be a affected by just "cpu upgrade". The important factor to calculate access-path is I/O and CPU. The catalog statistics provides filter factor, size and organization of indexes, table and tablespace etc.The catalog statistics are used to calculate I/O cost (BP size, RID size etc are also used on certain occasion but to a very less extent). The I/O cost decides tablespace scan/ choice of indexes /method of join/ type of I/O operation needed etc. [...] 3034 41 14_DB2 SPs & EXCI10_Rao, Peavy26_Peavy.Rao@TRANSAMERICA.COM30_Mon, 3 Apr 2000 09:53:46 -0500445_- Good morning.

I am testing DB2 SPs with EXCI interface to CICS programs. I am successful in starting a CICS program and getting the results back to DB2 SP. My problem is sending the result back to the client program using a DB2 CURSOR WITH RETURN. Whenever the SP hits OPEN CURSOR after returning from CICS program, the SP is abending. I could not get any dump or any error code other than the following messages from DB2MSTR log. [...] 3076 110 66_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH"14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Tue, 4 Apr 2000 01:04:28 +1000618_us-ascii Larry,

Remember that DB2 will firstly steal unused pages in the buffer pool, and then sequential pages and only disallow consideration of random pages if VPSEQT percentage already exceeded.

So question really is how much of pool used by sequential pages after previously full of random pages.

Note also that DB2 now used mutiple sub-pools to reduce BP latch contention and so maintains LRU chains of sequential pages and also LRU chains of random and sequential pages in each subpool in order to decide whether to reuse any page or exclude random pages if VPSEQT threshhold reached. [...] 3187 34 43_Re: Does CPU upgrade affect DB2 optimizer ?14_Massimo Scarpa16_mscarpa@CESVE.IT30_Mon, 3 Apr 2000 17:05:47 +0200309_us-ascii I kno that Optimizer it's the milestone of DB2. They said 10x10E+69 times to me.

But I made a little search in DB2 manuals and I found only description or hints about Optimizer (I searched not so in deep, however), but no formula or theory description (only Filter Factors more or less). [...] 3222 27 66_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH"14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Mon, 3 Apr 2000 11:15:00 -0400353_ISO-8859-1 Message text written by DB2 Data Base Discussion List > Sorry to keep hammering this topic but, as I continue to test, my results consistently tell me that VPSEQT and HPSEQT are thresholds that DB2 does not apply to<

Sorry to dis-agree, but it absolutely does. This has already been proven ini testing, and in production systems. [...] 3250 17 16_DB2 Data Sharing10_Tom Taylor17_ttaylor@CHUBB.COM30_Mon, 3 Apr 2000 11:45:22 -0400395_us-ascii HI all

question: Are there any advantages and or disadvantages to running 2 members of a Data Sharing group on the same LPAR ?

Tom

================================================ 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. 3268 61 19_Re: dbrm to package14_Hans Dingerdis21_dingerdis@SIONELL.COM30_Mon, 3 Apr 2000 17:36:52 +0200458_iso-8859-1 Sorry, should have mentioned that on this specific site they use a 1 plan 1 dbrm relationship, and they have BMC/CM to autobind all the plans when changing a table. So, is versioning all there is ? Reg. Hans







----- Original Message ----- From: "Allen, Colin" Newsgroups: bit.listserv.db2-l To: Sent: Monday, April 03, 2000 12:09 PM Subject: Re: dbrm to package [...] 3330 47 20_Re: DB2 Data Sharing22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Mon, 3 Apr 2000 16:52:00 +0100377_ISO-8859-1 Hi The only real disadvantage I can think of is if you have an LPAR failure...both DB2s get taken out.

Another thing to think about is the fact that you can only have 2Gig of Central storage and the rest has to be expanded .. so by that you will be sharing Central storage amounst your 2 DB2s...and would obviosuly have more expanded to assist the HP's. [...] 3378 91 20_Re: DB2 Data Sharing15_Gordon Fishwick39_Gordon.Fishwick@SCOTTISH-SOUTHERN.CO.UK30_Mon, 3 Apr 2000 17:08:43 +0100596_us-ascii My question is this...

Wouldn't that be defeating the whole object of data sharing?

Gordon Fishwick DB2 Systems Programmer Scottish and Southern Energy plc







Leslie Pendlebury-Bowe on 03/04/2000 15:52:00

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Gordon Fishwick/HAV/SSE) Subject: Re: DB2 Data Sharing





Hi The only real disadvantage I can think of is if you have an LPAR failure...both DB2s get taken out. [...] 3470 64 18_Re: DB2 SPs & EXCI14_Philip Gunning20_pgunning@BOSCOVS.COM30_Mon, 3 Apr 2000 12:19:16 -0400443_iso-8859-1 Peavy, We are running SPs with EXCI, but not RRSAF. Error message indicates abnormal termination count was reached. We have not had any problems specfically with EXCI in conjunction with SPs. Suggest you look in sys1.logrec for symptom string, you can have someone format it for you if you need it. HTH Phil Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS [...] 3535 52 7_QBSTSGT9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Mon, 3 Apr 2000 17:37:22 +0100387_us-ascii Hi, How does QBSTSGT relate to QBSTSPP, QBSTLPF and QBSTDPP ? from .SDSNSAMP(DSNWMSGS). QBSTSGT - NUMBER OF GETPAGE REQUESTS ISSUED BY SEQUENTIAL ACCESS REQUESTERS. QBSTSPP - NUMBER OF PAGES READ BECAUSE OF NORMAL SEQUENTIAL PREFETCH QBSTLPP - NUMBER OF ASYNCHRONOUS PAGE READS BECAUSE OF LIST PREFETCH QBSTDPP NUMBER OF ASYNCHRONOUS PAGE READS BECAUSE OF DYNAMIC PREFETCH [...] 3588 74 66_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH"0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Mon, 3 Apr 2000 11:46:59 -0500597_us-ascii I am not disagreeing with anyone on this, I am just a technician trying to make my system run better. Since I see that I have objects that are overrunning bufferpools at times, I would like to know why this is happening. I have done some testing that invokes sequential prefetch and the pages always stay "fenced in" within the VPSEQT and/or the HPSEQT thresholds. However, list prefetch seems to ignore this threshold. As I understand these thresholds, when the number of sequential pages read in hits the threshold, then ongoing sequential reads reuse the pages in the bufferpool. [...] 3663 60 43_Re: Does CPU upgrade affect DB2 optimizer ?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 3 Apr 2000 13:31:14 -0400385_us-ascii I guess Frank Ingrassia is expert outside IBM. He has laid down some formula which Optimizer usually considers. Gabrielle has also outlined details on optimizer very well in her book on "DB2 for OS/390 Development for Performance". Sheryl Larsen has many presentation on access paths and optimizer. Michael Hannan's IDUG presentation has very good material on optimizer. [...] 3724 14 43_Re: Does CPU upgrade affect DB2 optimizer ?13_Dan S Brogdon22_Dan.S.Brogdon@AEXP.COM30_Mon, 3 Apr 2000 10:33:43 -0700314_- Max, Check Gabrielle Wiorkowski's book , she gets into it pretty good.

================================================ 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. 3739 94 11_Re: QBSTSGT22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Mon, 3 Apr 2000 13:53:28 -0400508_- Andy -

I'm assuming DB2 for OS/390

Pay very close attention to the terminology. A GETPAGE request is NOT the same metric as NUMBER of PAGES READ...

My understanding is that a GETPAGE request is a request for a page (logical I/O if you will). If the page lives in a bufferpool, then the GETPAGE request is satisfied. If the page is not in the bufferpool, then the page must be READ from DASD (physical I/O) and placed into the bufferpool and then the GETPAGE request is satisfied. [...] 3834 142 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 3 Apr 2000 13:48:27 -0400501_iso-8859-1 Larry

I guess you want the list-prefetch to be disabled when VPSEQT (15% in your case) reaches. Am I right ? The only way to disable list-prefetch by altering bufferpool is to make VPSEQT to zero. Even keeping 15% VPSEQT will not disable your prefetch. Once the limit is reached then prefetch pages over 15% will be stolen if needed by random pages. Joel Goldstein is authority in this but my guess is if no one needs them then list-prefetch pages are allowed to exceed VPSEQT. [...] 3977 51 69_Cancelling threads asynchronously - is there a way to do it in batch?0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM30_Mon, 3 Apr 2000 13:31:57 -0500498_us-ascii Background: Weekly we unload DB2 data from prod and load to test to provide current data for application testing. Weekly , we experience failures in the load due to distributed threads holding locks.

Our solution: Prior to the load, we've added a REXX routine that issues a Start Access(UT) to prevent new threads from geting locks. Then it issues a Display thread to see if there are any threads holding locks and If there are, the next step issues a cancel of those threads. [...] 4029 89 73_Re: Cancelling threads asynchronously - is there a way to do it in batch?13_Lockwood Lyon23_Lockwood_Lyon@AMWAY.COM30_Mon, 3 Apr 2000 14:47:26 -0500454_us-ascii You might want to consider an alternative way of preventing new threads from acquiring locks.

One way that comes to mind (you've probably already thought of this, but here it is) would be to issue a "-STOP DDF MODE(QUIESCE)".

After a suitable amount of time (30 seconds?) you could then:

Do your routine that cancels currently active threads holding locks; Execute the "-START DB(x) TS(x) ACCESS(UT)" Do a "-START DDF" [...] 4119 64 73_Re: Cancelling threads asynchronously - is there a way to do it in batch?12_Bing Alabata25_bing.alabata@MCMASTER.COM30_Mon, 3 Apr 2000 13:40:38 -0500594_us-ascii Why don't you STOP all target objects and run DSN1COPY with object id translations? After laying down all the target VSAM underlying datasets, you can just START the target objects for access? (You can use REXX to facilitate the procedure).

At 01:31 PM 4/3/00 -0500, you wrote: > Background: >Weekly we unload DB2 data from >prod and load to test to provide >current data for application testing. >Weekly , we experience failures in the load >due to distributed threads holding locks. > > Our solution: > Prior to the load, we've added a REXX > routine that issues a Start [...] 4184 23 27_DSNTIAUL - Ordering of Rows15_Knabach, Mark L20_mark.knabach@EDS.COM30_Mon, 3 Apr 2000 14:50:47 -0400426_iso-8859-1 Hi All,

Can anyone tell me in what order DB2 unloads rows from a table when running DSNTIAUL? If there is a Clustering Index, does DB2 use it to order the rows when unloading? What if there is not a Clustering Index on the table, then in what order are the unloaded rows? Thirdly, does DB2 use and index at all to order the rows when unloading (e.g. you get them in the order they reside in the table)? [...] 4208 79 73_Re: Cancelling threads asynchronously - is there a way to do it in batch?22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Mon, 3 Apr 2000 14:54:22 -0400421_- If the majority of the distributed threads causing the problem are idle active distributed threads, then use the ZPARMs IDTHTOIN=non-zero value & CMSTAT=INACTIVE to cancel idle active distributed threads after a specified time out interval. Search the db2-l archives as this has been discussed in the past.





Mike Piontkowski Voice/Fax: 302.886.4612 mailto:michael.piontkowski@astrazeneca.com [...] 4288 75 73_Re: Cancelling threads asynchronously - is there a way to do it in batch?12_craig patton21_prgpatton@HOTMAIL.COM28_Mon, 3 Apr 2000 11:55:54 PDT365_- Shan, Without any real deep research, why don't you display/cancel the threads first, then issue the access(UT) ? Also, if these are 'hung' distributed threads (No longer active) then you can set the IDTHTOIN parameter in your ZPARMS to cancel the 'IDLE DISTRIBUTED THREADS' after 'n' seconds.

Just a couple of ideas off the top of my head, HTH Craig [...] 4364 47 14_control center13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET30_Mon, 3 Apr 2000 15:18:48 -0400344_us-ascii Using DB2 UDB version 6.1 on Windows NT and Solaris Server. I am connected to the server with no problems. Selecting "database" in the control center, right clicking and then selecting "create database" using the smart guide.... after clicking nothing happens, no messages good or bad ,, just returns to where it was. Any help...thanks 4412 12 18_Re: control center0_25_Ed_Vetock@NAVYFEDERAL.ORG30_Mon, 3 Apr 2000 15:30:21 -0400321_us-ascii Do you have the ADMIN SERVER started? Have all utilities been bound???

================================================ 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. 4425 117 32_Re: Datasharing Catalog Recovery12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Mon, 3 Apr 2000 21:32:42 +0200588_x-user-defined Hi, Any user defined index on SYSGROUP ? Isaac Yassin

mebert@AMADEUS.NET wrote: > > Glad to oblige. Unfortunately my experience with datasharing is currently > limited to a 2-day introduction course. But from your description I would assume > that the problem was disk, not DB2 related. Otherwise you should have gotten > some DB2 error code. Also, there is nothing special about SYSGROUP that I can > see. Maybe you can give more details where the job failed, and which steps were > executed before (JCL)? Did the job end with an error, or did you cancel it - [...] 4543 48 18_Re: control center13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET30_Mon, 3 Apr 2000 15:33:43 -0400423_us-ascii yes, the server is up and database utilities bound...

Ed_Vetock@NAVYFEDERAL.ORG wrote:

> Do you have the ADMIN SERVER started? Have all utilities been bound??? > > ================================================ > 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. 4592 48 31_Re: DSNTIAUL - Ordering of Rows24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Mon, 3 Apr 2000 12:43:56 -0700590_- Hi Mark!

Since DSNTIAUL is simply a sample program, it issues SQL just like any program you might have coded. Therefore, all rules about ordering still apply. The rows will not be returned in any specific order, unless you include an order by in your unload syntax.

Fritz

> -----Original Message----- > From: Knabach, Mark L [SMTP:mark.knabach@EDS.COM] > Sent: Monday, April 03, 2000 11:51 AM > To: DB2-L@RYCI.COM > Subject: DSNTIAUL - Ordering of Rows > > Hi All, > > Can anyone tell me in what order DB2 unloads rows from a table when > running > DSNTIAUL? If [...] 4641 200 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Mon, 3 Apr 2000 14:40:03 -0500601_us-ascii I do not want list-prefetch to be disabled (even when the thresholds are hit). What I want is for list prefetch to work the same way that sequential prefetch does. When I start up a sequential prefetch process, the thresholds keep the prefetch process from overrunning the bufferpool. When the number of sequential prefetch pages meets my threshold (1500 pages in my case), then stealing occurs within that 1500 pages. A process of this type will show 1500 or less pages that are used within the bufferpool (with the parameters that I use in place). Performance is still top-notch since [...] 4842 48 31_Re: DSNTIAUL - Ordering of Rows12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Mon, 3 Apr 2000 21:50:02 +0200519_x-user-defined Hi,

If you use a full SQL command then it depends on your "order by" if you put it in. If there is no "order by" or you don't use a full SQL command then it's up to the optimizer.

Isaac Yassin



"Knabach, Mark L" wrote: > > Hi All, > > Can anyone tell me in what order DB2 unloads rows from a table when running > DSNTIAUL? If there is a Clustering Index, does DB2 use it to order the rows > when unloading? What if there is not a Clustering Index on the table, then > in [...] 4891 43 13_DB2 V6 Limits12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV30_Mon, 3 Apr 2000 15:55:25 -0400390_iso-8859-1 Hi,

I have been tasked with compiling a list of UDB for OS390 Limits for V6 (so that DB2 can be compared to other DBMS products). On the web (http://www.s390.ibm.com/bookmgr-cgi/bookmgr.cmd/BOOKS/DSNUG0G1/APPENDIX1%2e 1), the DB2 UDB for OS/390 V6 Utility Guide, Appendix 1 lists:

Maximum number of base tables in a view, select, update, insert or delete: 225 [...] 4935 33 30_DSNTIAUL and COALESCE function13_Philip Nelson21_teamdba@ATTGLOBAL.NET30_Mon, 3 Apr 2000 20:02:06 +0100391_us-ascii We're using DSNTIAUL to do unloads for files to be loaded to various PC databases (after transferring using FTP).

For nullable fields low values is normally produced in the output, which is very confusing for the PC !!!

We get round this by substituting -

COALESCE(field,' ')

where we have to put the right number of spaces as the second parameter. [...] 4969 81 20_DRDA/DDF help needed12_Pinter, Ed A25_EAPINTER@ARKBLUECROSS.COM30_Mon, 3 Apr 2000 15:06:46 -0500450_- I am trying to query our Test DB2 subsystem from our Productional DB2 subsystem, using QMF. Both subsystems are on OS/390, running DB2 version 5.1 Any advice with DRDA/DDF would be greatly appreciated. See below for details. Thanks, Ed Pinter Arkansas Blue Cross Blue Shield 501-396-4131 ***************************************************************

My SQL from QMF is: SELECT * FROM DB2V.DSNV.DWS_MTH_EMPLOYER WHERE COMM_EMP_ID = 10 [...] 5051 56 34_Re: DSNTIAUL and COALESCE function11_White, Mary28_Mary_White@FDLES.STATE.FL.US30_Mon, 3 Apr 2000 16:22:22 -0400445_iso-8859-1 To do this, add SUBSTR(COALESCE(field,' '),1,12). This will make it character data.

Have Fun.

Mary White

-----Original Message----- From: Philip Nelson [mailto:teamdba@ATTGLOBAL.NET] Sent: Monday, April 03, 2000 3:02 PM To: DB2-L@RYCI.COM Subject: DSNTIAUL and COALESCE function



We're using DSNTIAUL to do unloads for files to be loaded to various PC databases (after transferring using FTP). [...] 5108 96 24_Re: DRDA/DDF help needed12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US30_Mon, 3 Apr 2000 15:24:21 -0500665_iso-8859-1 I thought QMF could only get to one DB2 unless you were connecting through datajoiner with QMF For Windows.

-----Original Message----- From: Pinter, Ed A [mailto:EAPINTER@ARKBLUECROSS.COM] Sent: Monday, April 03, 2000 3:07 PM To: DB2-L@RYCI.COM Subject: DRDA/DDF help needed



I am trying to query our Test DB2 subsystem from our Productional DB2 subsystem, using QMF. Both subsystems are on OS/390, running DB2 version 5.1 Any advice with DRDA/DDF would be greatly appreciated. See below for details. Thanks, Ed Pinter Arkansas Blue Cross Blue Shield 501-396-4131 *************************************************************** [...] 5205 263 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 3 Apr 2000 16:44:12 -0400414_iso-8859-1 As Richard said there is no matter which method is used, all the prefetch pages are treated the same way in the bufferpool. There is no difference between list-prefetch pages or sequential prefetch pages, the threshold applies to both the same way. I just tested this. For both List prefetch and sequential prefetch the VPSEQT is allowed is exceed if there is no other activity in that bufferpool. [...] 5469 45 27_PREFORMAT option of REORG??15_Stovall, Martin16_MStovall@USG.COM30_Mon, 3 Apr 2000 15:45:14 -0500193_- Does the PREFORMAT option of the REORG affect tablespace scans if the tablespace is segmented? Is it correct that DB2 reads checks the space map for segmented tablespaces on tablespace scans? 5515 39 47_Help with migrating from VSAM to DB2, puhleeze!14_Greg DiGiorgio29_gdigior@CI.NEWPORT-NEWS.VA.US30_Mon, 3 Apr 2000 16:51:38 -0400621_us-ascii Dear fellow DB2L'ers,

We are a municipality that writes all of our applications in-house using COBOL and VSAM in CICS on OS/390. We are divided into 4 teams, serrving all city departments. We have several thousand programs, over 99% of which use VSAM while the remaining programs use DB2.

We are at odds when it comes to DB2 development/migration. Some of us feel that our VSAM files are already normalized/de-normalized to the point that they can be simply moved to DB2 without any redesign whatsoever. Prudence, logic, and experience tells others of us that doing so is begging for trouble. [...] 5555 88 18_Re: DB2 SPs & EXCI10_Rao, Peavy26_Peavy.Rao@TRANSAMERICA.COM30_Mon, 3 Apr 2000 15:53:25 -0500646_- Hello List,

I could find the problem, in our Endevor/processor group definition. We had wrong include (DSNALI instead of DSNRLI). We have changed the include, re-compiled the test program. It ran successfully.

Thanks



> ---------- > From: Philip Gunning[SMTP:pgunning@BOSCOVS.COM] > Reply To: DB2 Data Base Discussion List > Sent: Monday, April 03, 2000 11:19 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2 SPs & EXCI > > Peavy, We are running SPs with EXCI, but not RRSAF. Error message > indicates > abnormal termination count was reached. We have not had any problems > specfically with EXCI in conjunction with [...] 5644 22 26_REXX for automated backups12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM30_Mon, 3 Apr 2000 17:11:43 -0400412_iso-8859-1 I have heard mentioned several times that some people have developed REXX routines that will coordinate backups for DB2. I would envision that the routine would read the DB2 catalog to determine the storage spaces to be backed up, create GDGs when required, allocated appropriate space for the image copies, etc. etc. Does this ring a bell with anyone and would you be willing to share the REXX? [...] 5667 151 29_SNA Server vs. DB2 Connect EE12_Terry Spikes21_terryspikes@YAHOO.COM30_Mon, 3 Apr 2000 14:18:41 -0700494_us-ascii Is there any documentation (Redbooks, White Papers, etc) that compares these 2 methods ("DB2 Connect EE" vs. "SNA Server/StarSQL") to "Web-Enable" DB2/OS390 data? We have a project in the making and they are leaning towards "SNA Server". We have a couple of PCs running DB2 Connect Personal Edition and I'm trying to convince everyone that we need "DB2 Connect EE" to Web-Enable our DB2/OS390 data. Basically I wanted to know how these 2 methods compare in Price and Performance. [...] 5819 314 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Mon, 3 Apr 2000 16:20:34 -0500548_us-ascii My testing has been in a bufferpool with other activity (so granted, I am somewhat looking at a moving target). When I run the query "SELECT * FROM BIGTAB" and watch the bufferpool usage, the number of pages occupied by this table will remain under the 15% threshold (very consistantly). However, when I run the "SELECT * FROM BIGTAB WHERE COL1 > :HV1 AND COL1 < :HV1" so that the first 500,000 rows from the index are returned, I end up with about 50% or more of the bufferpool occupied by this pageset. In the above test, I can let [...] 6134 91 24_Re: DRDA/DDF help needed12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Mon, 3 Apr 2000 16:23:00 -0400587_- Try running the query in SPUFI (should do the same thing). Do you have entries in both of sets of DDF tables to allow them to talk to each other? Look at SYSLOG and/or DB2PM_trace for any security error messages. Ask your security admin to run an audit report on the id you are using. No warrantee included. >>> DB2-L@RYCI.COM@inter2 04/03/00 03:07PM >>> I am trying to query our Test DB2 subsystem from our Productional DB2 subsystem, using QMF. Both subsystems are on OS/390, running DB2 version 5.1 Any advice with DRDA/DDF would be greatly appreciated. See below for details. [...] 6226 35 28_Data extract and concurrency21_David J. Nuechterlein17_davidn@UNIPAC.COM30_Mon, 3 Apr 2000 16:10:08 -0500587_- Hello,

We have a need to be able to extract records or an entire tablespace from a point in time before our batch cycle or after. This extract is then loaded into another DB2 subsystem for testing or data review. In addition, we are moving to support 24*7 availability as much as possible. We currently use full image copies then the BMC Unload Plus product (called by Princeton's Move for DB2 product) to perform the extract and loading. Essentially we are trying to make our recovery backups useable for data extraction as well. To improve availability we are looking at [...] 6262 344 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "0_31_truman.g.brown@BELLATLANTIC.COM30_Mon, 3 Apr 2000 17:52:36 -0400797_us-ascii Is it partitioned, and if so, does the second query invoke parallel I/O?







L-IS.Kirkpatrick@MUTUALOFOMAHA.COM on 04/03/2000 05:20:34 PM

Please respond to "DB2 Data Base Discussion List"





To: DB2-L@RYCI.COM

cc: (bcc: TRUMAN G. BROWN/EMPL/MD/Bell-Atl)





Subject: Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "











My testing has been in a bufferpool with other activity (so granted, I am somewhat looking at a moving target). When I run the query "SELECT * FROM BIGTAB" and watch the bufferpool usage, the number of pages occupied by this table will remain under the 15% threshold (very consistantly). However, when I [...] 6607 74 17_Re: DB2 V6 Limits40_Andersen, Cindy (CC-Application Support)26_Cindy.Andersen@CONAGRA.COM30_Mon, 3 Apr 2000 17:28:17 -0500339_iso-8859-1 From the DB2 UDB for OS/390 V6 Transition class:

* Maximum number of base tables allowed in a view, SELECT, UPDATE, INSERT, or DELETE statement is increased from 15 to 225.

=> Maximum number of base tables allowed in a FROM clause is 15

=> Maximum number of subqueries allowed in a SQL statement is 15 [...] 6682 90 17_Re: DB2 V6 Limits14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU30_Tue, 4 Apr 2000 08:42:14 +1000364_us-ascii Robert, I believe they both are. Max number of tables in your select is 225...this is because now with nested table expressions each one of those can also have 15 tables. eg: Select from ( nested 15 tables) as tab1, (nested 15 tables) as tab2... so that each FROM clause only relates to 15 tables but the SELECT statement relates to (15 x 15) = 225. [...] 6773 18 22_some db2 SAP questions8_Hao Wang14_Haow88@AOL.COM28_Mon, 3 Apr 2000 19:02:11 EDT297_US-ASCII Hi there,

Could you please tell me what is the difference between DB2 common server and UDB? We are running SAP db2 udb 6.1 on AIX. If you add more CPUs to the system, are there any additional configurations we need to do? Are there any mailing list specifically for DB2 SAP? [...] 6792 59 30_DSNTIAUL and COALESCE function14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Tue, 4 Apr 2000 09:40:49 +1000662_us-ascii Philip,

This is an annoying feature of DB2 that literal strings are treated as VARCHAR, but you can get round it like this:

COALESCE(field,SUBSTR(' ',1,1))

It doesn't matter if 1 char is shorter than field. DB2 will expand to same length.

From: Michael Hannan

>From: Philip Nelson >Subject: DSNTIAUL and COALESCE function >To: DB2-L@RYCI.COM > >We're using DSNTIAUL to do unloads for files to be loaded to various PC databases (after transferring using FTP). > >For nullable fields low values is normally produced in the output, which is very confusing for the PC !!! > >We get round this [...] 6852 59 26_Links about DB2 for OS/3906_DB2usa18_db2usa@HOTMAIL.COM30_Mon, 3 Apr 2000 22:43:03 -0500791_ISO-8859-1 Hi,

Here is a non-profit website about DB2 for OS/390 (IBM mainframes): - English : http://www.multimania.com/db2usa/eliendb2.htm - Français : http://www.multimania.com/db2usa/liendb2.htm

Last update on Tuesday, April 4th 2000 ======================================

- Links about DB2 for OS/390: http://www.multimania.com/db2usa/eliendb2.htm A new Webpage provides information about Stored Procedures on IBM's site.



- Technical papers: http://www.multimania.com/db2usa/earticle.htm Bonnie Baker's articles published in DB2 Mag are available on line: The Woes of Commitment by Bonnie Baker, DB2 Mag, Fall 1998. Leveraging the SQLCA by Bonnie Baker, DB2 Mag, Summer 1998. The Myths of the Clusterratio by Bonnie Baker, DB2 Mag, Winter 1997. [...] 6912 57 29_Performance of JDBC on OS/39014_C S R Krishnan29_csr_krishna@CHENNAI.TCS.CO.IN30_Tue, 4 Apr 2000 09:49:14 +0530339_us-ascii Hi Folks,

We are trying to run Java applications on the mainframe using JDBC to access DB2. We are an OS/390 V2R7 shop running DB2 Ver 6.1. We found that the performance of JDBC on our OS/390 is not very encouraging. Most of the time is involved in result set processing. We are not using stored procedures currently. [...] 6970 109 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Tue, 4 Apr 2000 05:44:08 GMT580_us-ascii Hi Larry, Its a good discussion but there is something which is causing the things to happen in different manner in two different cases mentioned by you. I agree with gurus Michael ,Venkat and Joel with whatever they have said about VPSEQT working in the same manner in case of list prefetch and sequential prefetch but i trust you as well that the statistics you are mentioning about tablespace scan using 15% of the pool(steal occurs in the same 15% area) and list prefetch access using 50% of the pool(both consistently) is what you have observed. As we all know [...] 7080 143 30_Re: REXX for automated backups0_18_mebert@AMADEUS.NET30_Tue, 4 Apr 2000 10:16:25 +0100308_us-ascii I have a 1450 line SAS program (would be possible in REXX, but MUCH more difficult) which merges LISTCAT output and information from the Catalog to create JCL to do the following tasks. Based on a list of job initiators, the program creates up to n separate job streams for parallel execution. [...] 7224 22 17_QMF Userid change0_18_mebert@AMADEUS.NET30_Tue, 4 Apr 2000 10:34:37 +0100401_us-ascii This is probably simple but after the initial contact with it I never bothered to learn QMF: we want to move everything belonging to one user to another one, essentially renaming the original user (we recently changed all our userids). How? (QMF V3.3.0, DB2 V5, OS/390 R2.6)

TIA

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...] 7247 232 30_Re: REXX for automated backups14_Massimo Scarpa16_mscarpa@CESVE.IT30_Tue, 4 Apr 2000 10:42:35 +0200397_us-ascii It's nice to see how different shops arrived at the same results !!

Most of M. Ebert features (our needs differ slightly: by default if TS is partitioned a reorg for each

partition is created, for instance) are included in our REXX execs (a format of LISTCAT too, that's

no so complicated). We don't have SAS (it costs A LOT...) so to execute SQL, we use DSQCIX [...] 7480 50 21_Re: QMF Userid change17_Erkki Søndergaard20_esonderg@SCA.CSC.COM30_Tue, 4 Apr 2000 11:08:44 +0200594_us-ascii Moving native QMF object from one userid to another is easy, just update OWNER in Q.OBJECT_DIRECTORY, Q.OBEJCT_DATA and Q.OBJECT_REMARKS. The catch is that this doesn't change the references within the object itself. This is especially a problem in PROCedures, but can be handled with either a manual edit of each proc or a program that updates directly on the Q.OBJECT_DATA table. Just make sure you don't mess with the headers in the APPLDATA field. For moving user tables the easy way is by generating a PROC that DISPLAY the old table followed by a SAVE DATA AS new table and [...] 7531 199 30_Re: REXX for automated backups13_Aratos Sicyon25_aratos_sicyon@HOTMAIL.COM29_Tue, 4 Apr 2000 12:14:52 EEST305_- Dr Ebert,

You wrote "So why did I post this? Well..."

Do you mind if I answer your rhetorical question?

I think : i) You wanted to show off a little (heck, I can relate to that), and ii) You were looking for some encouragement from the rest of to get the work finished. So ... [...] 7731 212 21_Re: QMF Userid change0_18_mebert@AMADEUS.NET30_Tue, 4 Apr 2000 11:31:20 +01001350_iso-8859-5 Hello Pierre & Erkki,

thanks for your information and your quick response. This should provide the info we need to develop some sort of "MOVE" routine, especially with regard to updating references within the object.

ME.



From: Erkki Søndergaard on 04/04/2000 09:08 GMT

Please respond to DB2 Data Base Discussion List |---------> | | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | >-------------------------------------------| |---------> |To: | |---------> >---------------------------------------------------------------------------> |DB2-L@RYCI.COM | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> |cc: | |---------> >---------------------------------------------------------------------------> | (bcc: Michael Ebert/MUC/AMADEUS) | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> | | |---------> [...] 7944 22 30_Dv2V5 versus Db2V6 / Resources15_Thomas Karlsson19_thkar97@HOTMAIL.COM29_Tue, 4 Apr 2000 12:35:49 CEST567_- Hi! ------ We are on our way to migrate Db2V5 on Os390 up to V6. Are there anybody out there who have checked how much resources on CPU etc, DB2 V6 takes in comparison with V5.



Best Regards Thomas K ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

================================================ 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. 7967 73 51_Re: Help with migrating from VSAM to DB2, puhleeze!13_Lynne Flatley17_LFlatley@NEFN.COM30_Tue, 4 Apr 2000 07:22:00 -0400485_us-ascii Hi Greg!

Actually, there's 2 items to consider when porting an app from VSAM to DB2, the structure of the files (soon to be tables) AND the structure of the app. The files 'may' already be properly normalized. Without looking at all the file layouts, one can't really say. So long as 'key' column lengths are standard across tables, i.e. CUST_NBR is defined as a CHAR(11) (example only!) on all tables and not CHAR(9) on some and 11 on others, you should be okay. [...] 8041 104 39_FW: SQL/VSE 3.5 Problem with DB-restore4_IBM116_IBM1@UBIL.COM.TR30_Tue, 4 Apr 2000 14:28:01 +0300668_ISO-8859-9 hello there, I have been trying to send this reply to Thomas Machovec for the past week or so. since I I have actually never seen it on MY mail, I shall assume that none of you, especially Thomas, has seen it either.

---------------------------------------------------------------------------- ----------------------------------------------------------------------------

> hello thomas, > > the message code is probably ARI0040E. > > if it is so, look at the messages and codes under "DB2 for VSE System > Control, if the DB2 abended you can actually look at message ARI0042I for > a little more info. > > ARIYL22 is actually the name of [...] 8146 33 31_Performance ratings for DB2/UDB0_18_dsi.listsrv@DB.COM30_Tue, 4 Apr 2000 17:26:51 +0530410_us-ascii Hello, I have been recently visiting TPC (Transaction Processing Council) website and found performance ratings (including price/performance) for various combination of Hardware/Operating System/Databases.

I was quite surprised by the sheer absence of DB2/UDB database figures on practically any platform except AS400. Does anybody have figures for DB2/UDB over RS6000, Mainframes etc ..? [...] 8180 53 26_OPCE Authorisation problem0_18_mebert@AMADEUS.NET30_Tue, 4 Apr 2000 14:06:40 +0100593_us-ascii Hello List,

since yesterday we have been looking at a problem which seemed routine at first. The DSNTIAUL sample unload program is run from the OPCE job scheduler to unload table TBSR.T1_TPA. The SYSIN for DSNTIAUL specifies just the creator.table, i.e. PARMS(SQL) is not used. PUBLIC has EXECUTE for plan DSNTIAUL. INSERT, UPDATE, DELETE, SELECT on table TBSR.T1_TPA has been GRANTed (by SYSADM) to RACF Group TBSRU (the Update Group). OPCE is connected to TBSRU. This is standard procedure here and has never caused problems. In this case, however, we get this message: [...] 8234 121 24_CHECK DATA constraints !27_Thakral, Deepak Kumar (CTS)24_TDeepak@CHN.CTS-CORP.COM30_Tue, 4 Apr 2000 18:06:39 +0530338_iso-8859-1 Hi !

I want to create a table say EMPTABLE in which two of my fields are :

LOCATION which can have values only 'CSI', 'WHT', 'NWR' & 'ELN'

SEX which can be M/F.

How do I put a check constraint such that if I try to insert values other than the ones specified , the insert operation will fails. [...] 8356 180 33_Re: SNA Server vs. DB2 Connect EE19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 4 Apr 2000 07:52:25 -0500605_iso-8859-1 Terry, This issue has been discussed. I believe consensus was to use TCP/IP. Scan the archives and you'll get many hits which may include docs.

HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other [...] 8537 218 30_Re: REXX for automated backups22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Tue, 4 Apr 2000 10:29:00 +0100384_ISO-8859-1 wow .. Dr Ebert contributes huge amounts to this list, can I suggest you at the very least go easy with your words? Leslie Pendlebury-Bowe

-----Original Message----- From: Aratos Sicyon at Internet Sent: Tuesday, April 04, 2000 12:14 PM To: DB2-L@RYCI.COM at INTERNET Subject: RE: REXX for automated backups



Dr Ebert, [...] 8756 61 35_Re: Performance ratings for DB2/UDB12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Tue, 4 Apr 2000 14:05:00 +0100475_- If you read through the TPC website you will find that members can submit ratings (subject to scrutiny) and also remove ratings when they feel that they are 'no longer appropriate'. It seems that 'no longer appropriate' is when the competition beat their results.

Colin Allen, DB2 DBA Abbey National

Colin.Allen@AbbeyNational.Co.Uk ---------- From: dsi.listsrv@DB.COM To: DB2-L@RYCI.COM Subject: Performance ratings for DB2/UDB Date: 04 April 2000 12:56 [...] 8818 166 30_Re: OPCE Authorisation problem0_18_mebert@AMADEUS.NET30_Tue, 4 Apr 2000 15:10:22 +01001555_us-ascii How much do you bet? Careful now as you've lost already. That's what I thought at first and considered it a routine problem. But if I do a TSO LG TBSRU, OPCE is listed in first place.

ME.







From: "DAVIS, RICK (SBCSI)" on 04/04/2000 12:32 GMT













|---------> | | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | >-------------------------------------------| |---------> |To: | |---------> >---------------------------------------------------------------------------> |Michael Ebert/MUC/AMADEUS@AMAWORLD | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> |cc: | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> | | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | >-------------------------------------------| [...] 8985 60 28_Re: CHECK DATA constraints !12_craig patton21_prgpatton@HOTMAIL.COM28_Tue, 4 Apr 2000 06:16:36 PDT617_- Deepak, You can define table check constraints at table create time or you can alter the table to add the constraints. Look at the DB2 Administration manual for the proper sytax.

HTH Craig Patton Independent DB2 DBA

>From: "Thakral, Deepak Kumar (CTS)" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: CHECK DATA constraints ! >Date: Tue, 4 Apr 2000 18:06:39 +0530 > >Hi ! > >I want to create a table say EMPTABLE in which two of my fields are : > >LOCATION which can have values only 'CSI', 'WHT', 'NWR' & 'ELN' > >SEX which can [...] 9046 291 30_Re: REXX for automated backups0_18_mebert@AMADEUS.NET30_Tue, 4 Apr 2000 15:16:43 +01001573_us-ascii Don't worry, I'm not that sensitive. After all, Aratos is quite correct... and if I hadn't been ready for this response, I wouldn't have written the last sentence.

ME.





From: Leslie Pendlebury-Bowe on 04/04/2000 09:29 GMT

Please respond to DB2 Data Base Discussion List









|---------> | | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | >-------------------------------------------| |---------> |To: | |---------> >---------------------------------------------------------------------------> |DB2-L@RYCI.COM | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> |cc: | |---------> >---------------------------------------------------------------------------> | (bcc: Michael Ebert/MUC/AMADEUS) | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> | | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | [...] 9338 185 33_Re: SNA Server vs. DB2 Connect EE14_Philip Gunning20_pgunning@BOSCOVS.COM30_Tue, 4 Apr 2000 09:30:32 -0400578_iso-8859-1 Terry, Hopefully Leon will have some info for you on SNA server versus DB2 Connect. We have used DB2 Connect EE on NT and PE on 95 and NT for about 3 years with no problems to speak of. We converted from SNA to TCPIP and noticed a significant performance boost. DB2 runtime clients require specialized setup if SNA server Applicaiton Requestor services are provided by a product other than DB2 Connect, specifically the library that provides the AR services has to be in the right path. I prefer to deal with the company that provides the software solution that [...] 9524 51 35_Re: Performance ratings for DB2/UDB10_Juan Lanus21_jlanus@INTERAR.COM.AR30_Tue, 4 Apr 2000 10:19:46 -0300216_us-ascii mkbasu: THe benchmarks are at http://www-4.ibm.com/software/data/db2/benchmarks/ where you will find no TPC, only ERP. I'd also like to see a TPC. But there's been no notice since one year ago or so. Regards 9576 136 28_Re: CHECK DATA constraints !15_Suneel Konidala30_suneel.konidala@PRUDENTIAL.COM30_Tue, 4 Apr 2000 09:23:17 -0400495_us-ascii



Deepak,

The syntax of 'Create table' statement allows you to have check constraint on any column.

For example...

CREATE TABLE ABCDEF (LOCATION CHAR(4) NOT NULL

,SEX CHAR(1) NOT NULL , .................... ,...................... ,...................... ,PRIMARY KEY (xxxxx ,yyyyy ) ,CONSTRAINT LOCATION_CHK CHECK(LOCATION IN ('CSI', 'WHT', 'NWR','ELN') ) ,CONSTRAINT SEX_CHK CHECK(SEX IN ('M', 'F') ) ) IN Database Name. Tablespace Name [...] 9713 24 29_Power Builder and DB2 on S39019_Scarlett McLaughlin29_SMcLaughlin@CONSULTEC-INC.COM30_Tue, 4 Apr 2000 09:23:26 -0400587_US-ASCII We are in the early stages of constructing a 3 tier Power Builder application using DB2 on a S390 platform. Our development team is averse to using Stored Procedures (and are actually looking at SQL Procedures) because they want an automated way to generate the SQL rather than using a Cobol Program. Has anyone published benchmarking results that reflects the use of dynamic SQL in a 3 tier environment? If so, could you point me to those. Has anyone used the new SQL Procedures with the SQL Procedure Builder product? If so, how is the performance of this generated SQL? [...] 9738 19 24_Re: DRDA/DDF help needed10_Robert Ord21_robertord@HOTMAIL.COM28_Tue, 4 Apr 2000 13:45:40 GMT508_- You need to ensure your zparm for TCP/IP is set to already verified. This can be done through the DB2 installation panel for DDF.

Cheers

Rob ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

================================================ 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. 9758 83 51_Re: Help with migrating from VSAM to DB2, puhleeze!10_Bruce, Mae26_BruceM@MAIL.OA.STATE.MO.US30_Tue, 4 Apr 2000 08:50:53 -0500569_iso-8859-1 I haven't been involved in moving a VSAM system to a DB2 system, but the experience I have comes from a software package that we have purchased. It uses DB2 as its RDBMS. But it was originally written for VSAM and continues to be written for VSAM. The vendor wants the product to be portable across multiple file structures so what we are seeing is data replication and inefficient code just so that the programs are the same regardless of the file structure you are using. The I/O modules are, of course, different, but the way the programs access the [...] 9842 32 19_DDF Startup w/TCPIP13_Tierney, Mark20_mtierney@FIRSTAM.COM30_Tue, 4 Apr 2000 08:50:55 -0500370_- Greetings......

I am just starting to use TCPIP with DDF on V5.1 in OS390 2.5.

Has anyone experienced a slow startup with DDF and TCPIP? I can sometimes wait up to 15 minutes between the: DSNL003I - DDF IS STARTING and the: DSNL519I - DSNLILNR TCP/IP SERVICES AVAILABLE messages. Also during this interval, nothing else can connect to the subystem. [...] 9875 215 30_Re: REXX for automated backups15_Robert Lawrence21_rlawrence@BOSCOVS.COM30_Tue, 4 Apr 2000 09:52:01 -0400532_us-ascii Dr Ebert, Have you considered using the catalog search interface for retrieveing information from the ICF catalog. This is orders of magnitude faster than parsing listcat listings. It is explained in DFSMS/MVS V1R4 Managing Catalogs Apppendix D Catalog Search Interface User's Guide. Sample programs are in SYS1.SAMPLIB(3 Assembler and 1 REXX). Anytime I can use an API rather than parsing output listings I like it since it masks any changes which may happen in the listings. Hope this helps your automation efforts. [...] 10091 21 21_Archive Log Retention0_29_paul.packham@POSTOFFICE.CO.UK30_Tue, 4 Apr 2000 14:57:47 +0000309_us-ascii Hi,

I would like to get a feel for how long other sites keep their DB2 Archive Logs for ? I've looked at the archives and can't find this being discussed before. All I'm after is the number of days, I don't want to re-visit the merits of compression etc. as this has been covered before. [...] 10113 47 43_Finding SYSCOPY entries in the archive logs0_20_Carl_Nelson@AICI.COM30_Tue, 4 Apr 2000 09:07:29 -0500614_us-ascii We run modify weekly to delete SYSCOPY entries DELETE AGE(00060). The actual gdg datasets are not deleted but remain in the VTOC until they roll off. The local copies are written to cart; the primary remote copies are stacked by database on carts. (This has caused another set of nightmares resulting in an effort to unstack all datasets.) Earlier this year I noticed that modify was purging SYSCOPY entries older than 7 days instead of the 60 days. This occurred randomly (meaning I could not determine a pattern) across databases, tablespaces, and time. Not all tablespaces within a database would [...] 10161 52 43_Finding SYSCOPY entries in the archive logs0_20_Carl_Nelson@AICI.COM30_Tue, 4 Apr 2000 09:10:51 -0500602_us-ascii Forgot to tell you this: DB2 for OS/390 v4 BMC DASD Manger v5.4.03



We run modify weekly to delete SYSCOPY entries DELETE AGE(00060). The actual gdg datasets are not deleted but remain in the VTOC until they roll off. The local copies are written to cart; the primary remote copies are stacked by database on carts. (This has caused another set of nightmares resulting in an effort to unstack all datasets.) Earlier this year I noticed that modify was purging SYSCOPY entries older than 7 days instead of the 60 days. This occurred randomly (meaning I could not determine a [...] 10214 10 51_Re: Help with migrating from VSAM to DB2, puhleeze!10_Bill Brown25_bill_brown@SPRINGMAIL.COM30_Tue, 4 Apr 2000 10:15:46 -0400544_- Even if the data is normalized to an acceptable level what you will end up with are application programs that process data as if it were still in VSAM. The hidden agenda here is probably that we can replace VSAM io with DB2 io and get our application converted to DB2 in a very short period of time. As has been stated previously the applications will not take advantage of DB2 and usually the end result is poor performance and the opinion that DB2 is bad. DB2 can make some programs rather simple. However, you need to use a different [...] 10225 216 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Tue, 4 Apr 2000 09:16:10 -0500585_us-ascii I have done some additional testing and I think I have found the triggering mechanism that causes VPSEQT/HPSEQT to be ignored. I am also providing the information requested by Sanjeev below.

My previous testing has been with a portion of a table that did use list prefetch but my predicate was sparse enough that every page was not accessed (in other words, I was using "skip sequential" processing instead of true sequential processing). I have rerun some testing where I used a predicate as I did before (SELECT * FROM BIGTAB WHERE COL1 >= ? AND COL1 <= ?), but [...] 10442 13 30_Re: REXX for automated backups12_henri BOTBOL25_hbotbol@HACHETTE-LIVRE.FR30_Tue, 4 Apr 2000 09:01:06 -0500397_- hi , in sys1.samplib you have 3 pds (IGGSILC IGGCSIVG IGGCSIVS) and 1 REXX IGGCSIRX than shown how to use the API IGGCSI00 (extract catalog) OS/390 2.6.0

================================================ 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. 10456 45 25_Re: Archive Log Retention0_20_Carl_Nelson@AICI.COM30_Tue, 4 Apr 2000 09:09:08 -0500608_us-ascii 60 days but discussions will probably reduced this to 31days this year.











paul.packham@POSTOFFICE.CO.UK on 04/04/2000 02:57:47 PM

To: DB2-L@RYCI.COM cc: (bcc: Carl Nelson/IN002/AICI/US)

Subject: Archive Log Retention





Hi,

I would like to get a feel for how long other sites keep their DB2 Archive Logs for ? I've looked at the archives and can't find this being discussed before. All I'm after is the number of days, I don't want to re-visit the merits of compression etc. as this has been covered before. [...] 10502 17 21_DB2 SORT WORK DSNDB0712_henri BOTBOL25_hbotbol@HACHETTE-LIVRE.FR30_Tue, 4 Apr 2000 09:09:46 -0500405_- hi , i have to increase the size of the DB2 sort work (DSNDB07), what is the the procedure to do this ?

DB2 2.6.0 many thanks. henri BOTBOL system manager

================================================ 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. 10520 62 25_Re: Archive Log Retention22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Tue, 4 Apr 2000 15:26:00 +0100521_ISO-8859-1 we keep ours for 60 days aswell. Les

-----Original Message----- From: Carl_Nelson@AICI.COM at Internet Sent: Tuesday, April 04, 2000 9:09 AM To: DB2-L@RYCI.COM at INTERNET Subject: RE: Archive Log Retention



60 days but discussions will probably reduced this to 31days this year.











paul.packham@POSTOFFICE.CO.UK on 04/04/2000 02:57:47 PM

To: DB2-L@RYCI.COM cc: (bcc: Carl Nelson/IN002/AICI/US)

Subject: Archive Log Retention [...] 10583 47 25_Re: Archive Log Retention13_Brian Charles20_bcharles@RITEAID.COM30_Tue, 4 Apr 2000 10:26:54 -0400576_iso-8859-1 We keep image copies and logs for 5 days.

Brian Charles DBA Rite Aid Corp

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > paul.packham@POSTOFFICE.CO.UK > Sent: Tuesday, April 04, 2000 10:58 AM > To: DB2-L@RYCI.COM > Subject: Archive Log Retention > > > Hi, > > I would like to get a feel for how long other sites keep their > DB2 Archive Logs > for ? I've looked at the archives and can't find this being > discussed before. > All I'm after is the number of days, I don't want to re-visit [...] 10631 14 25_Re: Archive Log Retention14_Massimo Scarpa16_mscarpa@CESVE.IT30_Tue, 4 Apr 2000 16:40:10 +0200338_us-ascii 7 days (a week is our 'unit-of-work') , in sync with image copy GDGs.

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10646 47 25_Re: Archive Log Retention13_Brian Charles20_bcharles@RITEAID.COM30_Tue, 4 Apr 2000 10:32:39 -0400575_iso-8859-1 We keep 5 days of logs and image copies.

Brian Charles DBA Rite Aid Corp

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > paul.packham@POSTOFFICE.CO.UK > Sent: Tuesday, April 04, 2000 10:58 AM > To: DB2-L@RYCI.COM > Subject: Archive Log Retention > > > Hi, > > I would like to get a feel for how long other sites keep their > DB2 Archive Logs > for ? I've looked at the archives and can't find this being > discussed before. > All I'm after is the number of days, I don't want to re-visit [...] 10694 12 25_Re: Archive Log Retention12_henri BOTBOL25_hbotbol@HACHETTE-LIVRE.FR30_Tue, 4 Apr 2000 09:31:05 -0500293_- hi, we keep archive log for 60 days. henri BOTBOL

================================================ 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. 10707 38 25_Re: Archive Log Retention15_Gordon Fishwick39_Gordon.Fishwick@SCOTTISH-SOUTHERN.CO.UK30_Tue, 4 Apr 2000 15:48:05 +0100356_us-ascii Paul,

As you can see from the varied answers so far, I think it really depends on the applications that are running on a particular sub-system. Also there may be underlying business reasons which determine how long the items are kept.

Best regards,

Gordon Fishwick DB2 Systems Programmer Scottish and Southern Energy plc [...] 10746 125 41_Re: DB2 SORT WORK DSNDB07; automated REXX0_18_mebert@AMADEUS.NET30_Tue, 4 Apr 2000 16:52:32 +0100360_us-ascii 1. Thanks to Robert Lawrence and Henri Botbol for telling me about the Catalog Search Interface. I will look into this. 2. Here is my procedure for changing DSNDB07 sizes; we're DB2 V5 but it probably will be the same for previous versions. It executes in a matter of seconds but you should run it in a quiet time since you have to STOP the TSs: [...] 10872 26 25_Re: DB2 SORT WORK DSNDB0714_Clayton, Colin21_Colin_Clayton@BMC.COM30_Tue, 4 Apr 2000 09:37:59 -0500584_ISO-8859-1 Henri,

DB2 2.6.0 ???

Anyway, to resize do STOP DB / DROP / CREATE (bigger) / START





Remember also its better not to allocate any secondary space and spread as many as you can afford, over as many disks and controllers as possible.

Colin Clayton



My opinions only etc..etc...

================================================ 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. 10899 149 41_Re: DB2 SORT WORK DSNDB07; automated REXX22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Tue, 4 Apr 2000 15:55:00 +0100491_ISO-8859-1 Michael et al, Sorry my last post was wrong - typed from memory and I should have realised that DSNDB07 does not need a tablespace drop/re-create .. just a Delete/Define .. what a twat.

I have drop/re-create on the brain as I am doing this everywhere at the moment.

Les

-----Original Message----- From: mebert@AMADEUS.NET at Internet Sent: Tuesday, April 04, 2000 4:52 PM To: DB2-L@RYCI.COM at INTERNET Subject: RE: DB2 SORT WORK DSNDB07; automated REXX [...] 11049 12 41_Re: DB2 SORT WORK DSNDB07; automated REXX10_Ruth Blake15_rblake3@CSC.COM30_Tue, 4 Apr 2000 10:19:23 -0500279_us-ascii I'm hoping you meant "twit".

================================================ 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. 11062 39 16_DB2 Data Sharing0_18_cmunson@US.IBM.COM30_Tue, 4 Apr 2000 09:21:02 -0600556_us-ascii As was previously mentioned the obvious disadvantage would be the single point of failure of the LPAR that the DB2's are on. One advantage is that if your DB2 system is virtual storage constrained by adding another engine you just bought yourself about 1.6GB of workspace. Sure it would be better for availability if you put that member on a different LPAR but if you don't have choice this may be an acceptable solution. Another time for 2 members on the same LPAR might be in a failure scenario. If you lose an LPAR with a DB2 you can have [...] 11102 51 33_Endevor-controlled Data Migration13_Marcus Davage29_Marcus.Davage@LLOYDSTSB.CO.UK30_Tue, 4 Apr 2000 15:39:11 +0100653_iso-8859-1 Hi cyber-colleagues...

Here's a good one... Tell me what you think of this...

Developers are developing... Moving code around from Development to System Test to User Acceptance... All this is done under CA/Endevor control and is fine. Any DB2 objects that are code-dependant are manually migrated via good ol' DBAs. The DBAs also move code-dependant rule data from environment to environment in conjunction with the Endevor migrations. This is done via some unload/load mechanism. Someone has got it in their head that life would be easier if the rule data was also moved between environments using Endevor, along with the [...] 11154 13 50_Re: DB2 SORT WORK DSNDB07; automated REXX -REPONSE12_Henry BOTBOL25_hbotbol@HACHETTE-LIVRE.FR30_Tue, 4 Apr 2000 17:29:21 +0200307_- ** High Priority **

thank you for your help henri BOTBOL

================================================ 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. 11168 35 41_Re: DB2 SORT WORK DSNDB07; automated REXX22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Tue, 4 Apr 2000 16:25:00 +0100369_ISO-8859-1 oh dear - here we go .. In the US is what I said bad? in the UK the word I used means the same as twit ..

???????????????????????

Leslie

-----Original Message----- From: Ruth Blake at Internet Sent: Tuesday, April 04, 2000 10:19 AM To: DB2-L@RYCI.COM at INTERNET Subject: RE: DB2 SORT WORK DSNDB07; automated REXX [...] 11204 78 28_Re: CHECK DATA constraints !9_Don Alden26_Don.Alden@PKSIS.LEVEL3.COM30_Tue, 4 Apr 2000 09:29:03 -0600494_iso-8859-1 There is also samples of DB2 RI usage in the Sample Database => DSN8510

GOOD LUCK, Don Alden (-o:

-----Original Message----- From: craig patton [mailto:prgpatton@HOTMAIL.COM] Sent: Tuesday, April 04, 2000 7:17 AM To: DB2-L@RYCI.COM Subject: Re: CHECK DATA constraints !



Deepak, You can define table check constraints at table create time or you can alter the table to add the constraints. Look at the DB2 Administration manual for the proper sytax. [...] 11283 14 41_Re: DB2 SORT WORK DSNDB07; automated REXX14_Massimo Scarpa16_mscarpa@CESVE.IT30_Tue, 4 Apr 2000 17:37:07 +0200329_us-ascii And for non-english people what can you describe this word ?

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 11298 11 25_Re: DB2 SORT WORK DSNDB0712_henri BOTBOL25_hbotbol@HACHETTE-LIVRE.FR30_Tue, 4 Apr 2000 10:30:37 -0500287_- excuse me , it's a mistake our db2 is 6.1.0

================================================ 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. 11310 203 37_Re: OPCE Authorisation problem SOLVED0_18_mebert@AMADEUS.NET30_Tue, 4 Apr 2000 17:46:54 +0100446_us-ascii Well actually it seems you were not completely wrong; it is EFFECTIVELY disconnected. My colleague, Vitor Pacheco (also a list subscriber), has found out:

OPCE is currently connected to 268 RACF groups - TBSRU occurs on position 257 in alphabetic sequence - and RACF passes only 250 groups to DB2... The only solution for this problem is to remove OPCE from some of this groups (we do not want to GRANT to userids directly). [...] 11514 225 37_Re: OPCE Authorisation problem SOLVED0_22_Rohn.Solecki@MTS.MB.CA30_Tue, 4 Apr 2000 10:47:53 -0500503_us-ascii Try "LU userid" - List User









mebert@AMADEUS.NET on 2000/04/04 11:46:54 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Rohn Solecki/MTSCommunications/MTS) Subject: Re: OPCE Authorisation problem SOLVED







Well actually it seems you were not completely wrong; it is EFFECTIVELY disconnected. My colleague, Vitor Pacheco (also a list subscriber), has found out: [...] 11740 213 30_Re: OPCE Authorisation problem0_19_Tim.Lowe@STPAUL.COM30_Tue, 4 Apr 2000 09:40:28 -0500373_us-ascii Michael, I am sure that you are right, but could you just put in a dsntep2 step with " SET CURRENT SQLID = 'TBSRU' " to see what happens?

Also, are you using RACF, and are your DB2 authorities in RACF or in the DB2 authority tables?

Thanks, Tim













mebert@AMADEUS.NET on 04/04/2000 09:10:22 AM [...] 11954 90 30_Re: OPCE Authorisation problem0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM30_Tue, 4 Apr 2000 10:56:38 -0500591_us-ascii This is a shot in the dark, but could it be that DSNTIAUL may have been recently rebound with different bind option? We had this situation happen at our shop in regard to Probuild..

If DYNAMICRULES(BIND) on plan DSNTIAUL is set, it allows anyone to update any table via a TSO clist or REXX program. This is because DYNAMICRULES(BIND) says that the authority for dynamic SQL will come from the plan owner (SYSADM) instead of from the user. I don't know if OPCE would fall into this category. It should be DYNAMICRULES(RUN), which would use the user's authority for table [...] 12045 18 34_Row level locking on the catalog ?10_Tom Taylor17_ttaylor@CHUBB.COM30_Tue, 4 Apr 2000 12:16:42 -0400486_us-ascii Hi all

I currently have long running applications that are holding 's' locks on the catalog (visual age apps). They are getting in the way of BIND operations. What about changing the catalog to use Row Level Locking ?

Tom

================================================ 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. 12064 54 25_Re: Archive Log Retention19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 4 Apr 2000 11:09:45 -0500593_iso-8859-1 Paul, A lot of good suggestions about retention. The archive logs you MUST have would be all those archive logs created after the one indicated by the REPORT RECOVERY for the tablespace with the oldest image copy.

HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender [...] 12119 30 20_DISPLAY LOCK Command10_ajay kumar19_db2v5r1@HOTMAIL.COM28_Tue, 4 Apr 2000 21:42:10 IST355_- Hi

We are facing a peculiar problem, When issue a "-DISPLAY DB(dbname) SPACE(tsnam) LOCKS' no information is displayed. Even we tried on the CONSOLE too, nothing displayed.

If we issue thru SPUFI, the terminal got hanged, all the SPUFI settings are ok.

We called IBM support no response, Can any of the listusers face this..., [...] 12150 12 41_Re: DB2 SORT WORK DSNDB07; automated REXX12_henri BOTBOL25_hbotbol@HACHETTE-LIVRE.FR30_Tue, 4 Apr 2000 11:12:22 -0500259_- salut merci max

================================================ 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. 12163 78 41_Re: DB2 SORT WORK DSNDB07; automated REXX0_19_Tim.Lowe@STPAUL.COM30_Tue, 4 Apr 2000 11:29:26 -0500534_us-ascii Leslie, I can't say it here, you will have to see for yourself at an online slang UK dictionary: http://www.peevish.co.uk/slang/ The words have the same meanings here in the US. (grin)

Thanks for a smile! Tim













Leslie Pendlebury-Bowe on 04/04/2000 10:25:00 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM [...] 12242 245 37_Re: OPCE Authorisation problem SOLVED12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Tue, 4 Apr 2000 17:36:00 +0100413_iso-8859-1 I guess that would be the List User (LU) command then. LU on its own will list your details including all groups you belong to, LU userid to list someone else, if you have the authority.

Colin Allen, DB2 DBA Abbey National

Colin.Allen@AbbeyNational.Co.Uk ---------- From: mebert@AMADEUS.NET To: DB2-L@RYCI.COM Subject: Re: OPCE Authorisation problem SOLVED Date: 04 April 2000 17:46 [...] 12488 51 25_Re: DB2 SORT WORK DSNDB0722_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Tue, 4 Apr 2000 15:35:00 +0100336_ISO-8859-1 It's in the manual ..

or frmo memory :

1. issue -stop db(dsndb07) space(*) 2. drop all pagesets in DSNDB07 3. Delete all VSAM datasets that relate to the pagesets just dropped 4. Create new VSAM (IDCAMS) datasets for pagesets in DSNDB07 (new size) 5. Create pagesets in DSNDB07 6. start db dsndb07 etc etc [...] 12540 25 18_Best SQL practices19_Lisa Westcott-Dryer29_Lisa_Westcott-Dryer@AMWAY.COM30_Tue, 4 Apr 2000 12:51:36 -0400438_us-ascii I'm looking to find info/book(s) on best SQL programming practices.

When to use different join techniques, when to use different predicate techniques, subselects etc.

I don't mean the syntax of the statements. I mean in certain situations like when presented with SQL that is running badly analyzing it and saying "this shouldn't be an inner join it should be a sub-select" etc. A Best Practices type of book. [...] 12566 58 33_Re: Power Builder and DB2 on S39014_Philip Gunning20_pgunning@BOSCOVS.COM30_Tue, 4 Apr 2000 13:48:22 -0400585_iso-8859-1 Scarlett, 1. There are several benchmarks on 2 vs 3 tier performance at www.software.ibm.com/data/db2/performance. 2. We have a vendor developing stored procedures using the SPB, we set it up initially, it is easy to use. After the skeleton SP is built, it can be edited using the tool to expand the SP, add complex logic, etc. We were using this on a fairly large Warehouse Mgt System, based in Powerbuilder and DB2 EEE on AIX. We did find a shortfall in the documentation on the SPB (it basically has smart guide or micro-help), which in our experience is not enough [...] 12625 55 23_Re: DDF Startup w/TCPIP14_Philip Gunning20_pgunning@BOSCOVS.COM30_Tue, 4 Apr 2000 13:52:42 -0400374_iso-8859-1 Mark, This is definitely unusual. We are up in a matter of a few minutes. There is probably something wrong with your setup. I would check DDF, VTAM, TCPIP. Use the redbook, WOW! DB2 Supports TCPIP, SG24-2212-00. HTH Phil Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS Assoc List Owner [...] 12681 287 37_Re: OPCE Authorisation problem SOLVED12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 4 Apr 2000 10:58:31 -0700542_us-ascii Try LISTUSER user (LU I think) to list the groups for a given user.

--- mebert@AMADEUS.NET wrote: > Well actually it seems you were not completely > wrong; it is EFFECTIVELY > disconnected. My colleague, Vitor Pacheco (also a > list subscriber), has found > out: > > OPCE is currently connected to 268 RACF groups - > TBSRU occurs on position 257 in > alphabetic sequence - and RACF passes only 250 > groups to DB2... > The only solution for this problem is to remove OPCE > from some of this groups > (we do not want to [...] 12969 45 28_control center-script center13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET30_Tue, 4 Apr 2000 14:24:34 -0400210_us-ascii I can use all the tools of the control center except for the script center. There is no system name in the drop down box. How can i configure the script center to get the proper system name. thanks.... 13015 51 25_Re: Archive Log Retention41_=?iso-8859-1?Q?Cl=E1udio_Meluzzi_Mendes?=18_P000002@BEG.COM.BR30_Tue, 4 Apr 2000 15:45:31 -0300326_iso-8859-1 We are lucky enough to take daily full image copies. Our archive logs are kept for at least three days. Supposing we need to issue a RECOVER TABLESPACE tsxxxx (without extra parameters), if the most recent image copy fails we'll still have log records enough to recover from the second most recent image copy. [...] 13067 67 22_Re: Best SQL practices15_DeMarco, Joseph18_DeMarcoJ@CONED.COM30_Tue, 4 Apr 2000 15:00:17 -0400445_windows-1252 Try . . .

DB2 Answers! Certified Tech Support by Yevich, Lawson, Brant and Larsen has excellent chapters on SQL performance including advanced SQL features.

And

SQL for Smarties (don’t know the author – you can find it on amazon.com)



Joseph DeMarco demarcoj@coned.com Con Edison Information Resources Database Administration 4 Irving Place, Room 428 212.460.3392 [...] 13135 262 37_Re: OPCE Authorisation problem SOLVED19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 4 Apr 2000 14:14:11 -0500623_iso-8859-1 Hi Doc, I believe the amount was $1,000,000,000 USD ;~> Best Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 13398 57 24_Re: DISPLAY LOCK Command19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 4 Apr 2000 14:16:49 -0500602_iso-8859-1 Ajay, Hmmmm, do you have DISPLAY privilege? HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 13456 78 22_Re: Best SQL practices14_Arnold, Mark S20_Mark.Arnold@BNSF.COM30_Tue, 4 Apr 2000 14:22:22 -0500378_iso-8859-1 'SQL for Smarties' is by Joe Celko... he also wrote 'SQL Puzzles & Answers' for those of you who have lots of time on your hands...

Mark Arnold BNSF Railway

-----Original Message----- From: DeMarco, Joseph [mailto:DeMarcoJ@CONED.COM] Sent: Tuesday, April 04, 2000 2:00 PM To: DB2-L@RYCI.COM Subject: Re: Best SQL practices



Try . . . [...] 13535 301 44_Re: OPCE Authorisation problem SOLVED -Reply11_Jeff Schade15_JSCHADE@PCH.COM30_Tue, 4 Apr 2000 15:26:36 -0400631_US-ASCII If you log on to the user and type: TSO LU you will be presented with all kinds of RACF information, including the secondary groups available for that user.

Jeff Schade Publishers Clearing House 516-944-2673 jschade@pch.com

>>> RD8246@MOMAIL.SBC.COM 04/04/00 03:14pm >>> Hi Doc, I believe the amount was $1,000,000,000 USD ;~> Best Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have [...] 13837 72 30_Fwd: Re: Archive Log Retention13_John Arbogast16_jfarbo@YAHOO.COM30_Tue, 4 Apr 2000 12:41:04 -0700606_us-ascii 14 days here.

--- Brian Charles wrote: > Date: Tue, 4 Apr 2000 10:26:54 -0400 > Reply-to: bcharles@riteaid.com > From: Brian Charles > Subject: Re: Archive Log Retention > To: DB2-L@RYCI.COM > > We keep image copies and logs for 5 days. > > Brian Charles > DBA > Rite Aid Corp > > > -----Original Message----- > > From: DB2 Data Base Discussion List > [mailto:DB2-L@RYCI.COM]On Behalf Of > > paul.packham@POSTOFFICE.CO.UK > > Sent: Tuesday, April 04, 2000 10:58 AM > > To: DB2-L@RYCI.COM > > Subject: Archive Log Retention > > > > > > Hi, [...] 13910 48 38_Re: Row level locking on the catalog ?19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 4 Apr 2000 14:38:41 -0500570_iso-8859-1 Tom, As of DB2 UDB for OS/390 V5 and V6, you CANNOT ALTER LOCKSIZE on CATALOG tables. I think I'd investigate why the 'visual age apps' are setting the 'S' locks so long. HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete [...] 13959 135 22_Re: Best SQL practices0_22_Rohn.Solecki@MTS.MB.CA30_Tue, 4 Apr 2000 14:53:44 -0500572_us-ascii



Take a look at some of the books (online and paper) that Gabrielle Wiorkowski has written. Check out her web site http://www.GabrielleDB2.com/.

Craig Mullins book, DB2 Developer's Guide (3rd Ed is out, I think he said 4th (DB2 V6) will be available any time now) would also answer some of your questions. I feel that the content Craig's book is in general aimed more at DBA than developer, but as I said, there are parts that developers could benifit from. (PS Craig is 'out there' on the list, so you can ask him questions directly). [...] 14095 129 19_Re: dbrm to package14_Hans Dingerdis21_dingerdis@SIONELL.COM30_Tue, 4 Apr 2000 22:29:04 +0200299_iso-8859-1 Tim, Exceptions are made but it's thrue for 95% of the applications. This site was set up since rel. 2.1 and they are used to it. Under this condition i could not make a positive cost/profit analysis because i need to redesign the fully automated plan migration procedure. Reg. Hans [...] 14225 76 38_Re: Row level locking on the catalog ?12_Kirk Hampton16_khampto1@TXU.COM30_Tue, 4 Apr 2000 15:48:27 -0500489_us-ascii If you mean that the applications have an S lock on the same Package/Plan that you are attempting to BIND, that will always be the case as long as the application threads are active. Have you got these plans/packages bound with RELEASE(DEALLOCATE) so it doesn't give up the locks until the thread is terminated ? Are these apps accessing DB2 via DDF ? Do you have CMTSTAT=INACTIVE and IDTHTOIN=n seconds in your DSNZPARMs so that the threads go inactive and get terminated ? [...] 14302 35 14_DB2/RACF Rules0_22_BILL_GALLAGHER@PHL.COM30_Tue, 4 Apr 2000 16:56:40 -0400446_us-ascii Hi,

I'm still in the process of converting all of our DB2 security from native DB2 to RACF, and am thinking ahead to the "final lockdown" phase where we will completely close the door on DB2 security, so that all security requests will be either approved or denied by RACF.

For example, I am looking to create RACF rules such as the following to lock down all table access that is not covered by a more specific rule: [...] 14338 91 38_Re: Endeavor-controlled Data Migration11_Kwan, James18_James_Kwan@BMC.COM30_Tue, 4 Apr 2000 16:05:22 -0500564_ISO-8859-1 Marcus

I have studied this type of change control strategy in my old job. My finding is that they are 2 complete different animals. Endeavor is a code change management tool and it is not mean for change control of db objects. Of course you can keep all your ddls as part of the code and migrate it that way. However DB version and changes is not simple as adding and subtracting delta codes. Also it is very difficult to synchronize the actual db objects in the database. Just imagine if endeavor decides to fall back to the older release, [...] 14430 22 40_DB2 Maintenance 9909 (DB2 UDB for OS390)12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV30_Tue, 4 Apr 2000 17:44:42 -0400326_iso-8859-1 Hello,

How do I get a list of all the changes that are included in maintenance level 9909? Seems like 9909 is basically a refresh. Also, are there any known problems with 9909?

We are planning to implement 9909 in the near future, so I would appreciate any advice or comments.

TIA, Robert [...] 14453 47 44_Re: DB2 Maintenance 9909 (DB2 UDB for OS390)11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM30_Tue, 4 Apr 2000 17:46:27 -0500349_us-ascii We are OS/390 DB2 ver 6.1 Maint 9909 and the users are not happy! Too many problems. It has been nothing but headaches for me! If nothing is pushing you DON'T go! Because of this we must go closer to the "bleeding edge" soon... : (







From: "Hall, Robert" on 04/04/2000 04:44 PM [...] 14501 16 8_DB2 ODBC17_Charles Jambrosic29_Charles_Jambrosic@CSIHOME.COM30_Tue, 4 Apr 2000 15:08:15 -0700374_us-ascii Has anyone out there any experience with Windows applications accessing DB2 UDB V6 on OS/390 via ODBC?

TIA. Charles.

================================================ 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. 14518 246 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Wed, 5 Apr 2000 09:36:29 +1000634_us-ascii I wonder if the within 16 pages is 'page sequential' rule for dynamic prefetch applies to this? I am trying to find out the exact definition of a sequential page.

Michael Hannan

>From: L-IS.Kirkpatrick@MUTUALOFOMAHA.COM >Subject: Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH > " >To: DB2-L@RYCI.COM > >I have done some additional testing and I think I have found the triggering >mechanism that causes VPSEQT/HPSEQT to be ignored. I am also providing the >information requested by Sanjeev below. > >My previous testing has been with a portion of a table that did use list >prefetch but [...] 14765 30 29_Re: Osolete SYSIBM Databases?12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Wed, 5 Apr 2000 12:10:05 +1200588_us-ascii Seppo,

I seem to remember looking at Sysibm.Sysplandep before dropping an unused DSNDDF and seeing something for QMF. Can't remember the details, but one of the QMF packages was dependent on an object it would never refer to, for us anyway. We rebound the package validate(run) and dropped SYSDDF. All AOK. I think QMF was looking at a synonym it had for one of the renamed DDF tables. You know, to do with the fact the table names aren't 'prefixed' with SYS anymore. So I guess an alternative would be to drop and recreate the synonym/alias/whatever it was to point [...] 14796 86 24_Re: DISPLAY LOCK Command0_29_Greg.Palgrave@BANKWEST.COM.AU30_Wed, 5 Apr 2000 08:31:43 +0800478_us-ascii Karthik,

We had a recent experience after applying some maintenance where DISPLAYs and connects would hang. After a while, you may notice other threads seem to hang, or new connections also hang. They seem to all queue up behind each other.

We found DB2 would respond to *one* command, then no more (e.g. do a DISPLAY via console works OK, but then another DISPLAY or even a STOP is ignored/hung) - it appears that the command thread also gets hung. [...] 14883 415 44_Re: OPCE Authorisation problem SOLVED -Reply10_Ven Ilagan18_vilagan@ONE.NET.AU30_Wed, 5 Apr 2000 10:24:31 +1000547_us-ascii Hi Dr Ebert,

Like you, I'm also fond of writing snippets to generate lots of JCL to aid maintenance, but I use Rexx and ISPF instead of SAS due to the high cost of running Sas. Here's one which I would think you'd like as it addresses the specific LU command you're interested in. I also enclose the panel GENTAB1 used by the rexx command where you can scroll up and down to scrutinize the different groups the user belongs to (LU just displays on the screen page after page after page). You can pretty up GENTAB1 to your own [...] 15299 346 40_Fwd: DB2/390 Rexx question: Memory Leak?12_John Donahue20_aelfweld@HOTMAIL.COM28_Tue, 4 Apr 2000 17:31:37 PDT559_- Newsgroups: comp.databases.ibm-db2,comp.lang.rexx

I am new to Db2 & Rexx and have written a quick and very dirty file in an attempt to allow me to create a db from an existing extract to a comma delimited file in sybase format (we are not running sybase its just an extract we had written). Obviously on 390 loading a comma delimited file is not as straight forward as on other platforms. Since I really only needed to to this quickly while the actual extract & load code is completed I decided to be very "bad" and write a quick REXX script to [...] 15646 20 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Tue, 4 Apr 2000 20:49:37 -0400406_ISO-8859-1 What are the number getpages, I/Os by type, and the number of pages read by each type of I/O when the thresholds appear to be ignored?

Regards, Joel

===============================================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. 15667 98 33_Re: Performance of JDBC on OS/39011_rick creech18_ykcirc@HOTMAIL.COM28_Tue, 4 Apr 2000 17:51:38 PDT621_- You may have already addressed this, but I thought i would mention it anyway. Have you considered tuning the sql involved? Your problem may have little to do with JDBC and a great deal to do with the performance of the sql statements in question.

Regards, Rick Creech

>From: C S R Krishnan >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Performance of JDBC on OS/390 >Date: Tue, 4 Apr 2000 09:49:14 +0530 > >Hi Folks, > >We are trying to run Java applications on the mainframe using JDBC to >access >DB2. We are an OS/390 V2R7 [...] 15766 47 21_Re: QMF Userid change11_rick creech18_ykcirc@HOTMAIL.COM28_Tue, 4 Apr 2000 17:59:42 PDT303_- A method that would work would be to export the objects under one userid (queries,forms,procs,etc.) and then import them with the new userid. If you have sysadm auth you can issue set current sqlid while in qmf and then all qmf objects will have the creator of the last set current sqlid issued. [...] 15814 120 18_Re: DB2/RACF Rules13_Adrian Talbot29_Adrian.Talbot@BANKWEST.COM.AU30_Wed, 5 Apr 2000 08:59:42 +0800529_us-ascii Hi Bill,

From memory the RACF supplied EXIT DSNX@XAC checks for ownership first (Just like DB2) and doesn't check for rules if you are the owner of the table.

This caused problems for us as we were converting from ACF/2 DB2 which does not honour the ownership of tables (unlike DB2). To stop RACF from doing the ownership test we modified the exit DSNX@XAC which meant that you had to explicitly grant access to the tables even if the user had the ability to set themselves to the owner of the table. [...] 15935 403 44_Re: Fwd: DB2/390 Rexx question: Memory Leak?0_29_Greg.Palgrave@BANKWEST.COM.AU30_Wed, 5 Apr 2000 09:30:50 +0800422_us-ascii John,

It is a 'feature' of Rexx - I can't recall the details, but one of our DBA's ran into this some time ago with a Rexx/DB2 program, and it seems rexx does not truly deallocate this memory until rexx terminates. No amount of DB2 connect/disconnect will fix it.

Regards,

Greg Palgrave Database Administration IT Services Bank of Western Australia eMail : greg.palgrave@bankwest.com.au [...] 16339 104 33_Message in db2diag.log (DIA3605C)7_revadee18_revadeev@SCB.CO.TH30_Wed, 5 Apr 2000 10:25:16 +0700574_windows-874 Hi, all My OS is Solaris 5.6 and db2 v.5.2. And I don't know this message in db2diag.log. What does it mean about process_id "db2ipccm" and "DIA3605C"?

Thanks., Revadee Vichitchep. DBA at SCB bank. -------------------------------------------------------------------------------------- 2000-04-05-08.58.48.139400 Instance:db2scb Node:000 PID:24934(db2ipccm) Appid:none common_communication sqlccGetIPCs Probe:1 DIA3605C Memory allocation failure occurred.

ZRC=FFFFD605

Data Title:IPC Shared Memory Size PID:24934 Node:000 0000 f00c .... [...] 16444 96 44_Re: DB2 Maintenance 9909 (DB2 UDB for OS390)15_Shaun Z Lombard36_Shaun.Z.Lombard@TRANSPORT.QLD.GOV.AU30_Wed, 5 Apr 2000 13:50:01 +1000632_us-ascii The following comment can be found on the IBM web site at - http://www-4.ibm.com/software/data/db2/os390/v6apar.html

"With this significant function being delivered in the service stream, we generally recommend that customers test, but plan for production on a DB2 maintenance level that has had the additional testing done in a refresh. This is our recommendation for Version 6 as well. While the work is not yet ready for a commitment or announcement, we plan to deliver a refresh for DB2 Version 6 around the end of May 2000. In addition to the new function that will be integrated, it will incorporate many [...] 16541 20 41_Re: DB2 SORT WORK DSNDB07; automated REXX14_Massimo Scarpa16_mscarpa@CESVE.IT30_Wed, 5 Apr 2000 08:52:28 +0200442_us-ascii Hi, nice to meet you but merci for ...what REXX ?

I sent many execs in these days and I'm happy if some was useful to you !!! :-)))

Regards

Max Scarpa Data & System Admin

================================================ 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. 16562 16 44_Re: DB2 Maintenance 9909 (DB2 UDB for OS390)14_Massimo Scarpa16_mscarpa@CESVE.IT30_Wed, 5 Apr 2000 09:04:31 +0200368_us-ascii What kind of problems did you met ? We are planning for v6..... ll:-((

Regards and good luck

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16579 367 44_Re: Fwd: DB2/390 Rexx question: Memory Leak?10_Ven Ilagan18_vilagan@ONE.NET.AU30_Wed, 5 Apr 2000 17:23:00 +1000335_iso-8859-1 I'd say don't use REXX to insert the individual records - use Rexx to reformat your comma-delimitted file and create instead a series of flatfiles which you can LOAD RESUME YES into your tables - this way, you bypass the Rexx DB2 interface plus eliminate all the logging you'll be doing for each of the record inserts. [...] 16947 106 22_Re: Best SQL practices4_IBM116_IBM1@UBIL.COM.TR30_Wed, 5 Apr 2000 10:36:06 +0300635_- also check out the redbooks - there's some good things also in the redpieces - obviously not half as good-humoured and technically just as good as Joe's works!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it



> -----Original Message----- > From: Arnold, Mark S [SMTP:Mark.Arnold@BNSF.COM] > Sent: Tuesday, April 04, 2000 10:22 PM > To: DB2-L@RYCI.COM > Subject: Re: Best SQL practices > > 'SQL for Smarties' is by Joe Celko... he also wrote 'SQL Puzzles & > Answers' > for those of you who have lots of time on your hands... > > Mark Arnold > BNSF [...] 17054 51 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Wed, 5 Apr 2000 05:39:08 GMT556_us-ascii Hi Larry, I think i am confused with what you are saying VPSEQT and HPSEQT apply or doesn't apply.What i understand from these two apply is that this much %age of the pool is kept for sequential processing and the I/O type is asynchronous. It seems that what you mean from these two apply is that sequential processing will only use this much %age of pages for its processing.I might not have understood correctly but i am presuming this. From my last requirement i was asking the same thing which Joel has asked e.g : Getpage by type of I/O [...] 17106 40 51_Re: Help with migrating from VSAM to DB2, puhleeze!0_22_blyons2@CSCPLOENZKE.DE30_Wed, 5 Apr 2000 09:20:32 +0100625_us-ascii Hi Greg,

In a previous life I actually worked on the development of a set of tools to automate this conversion quite a few years ago. The main issues that came up at curtomer sites (that I can remember) back then were: Multiple Record Types. Many 'harmless'-looking COBOL Record definitions managed to disguise what was really a multiple record-type situation which would need to be modelled differently (at least in the logical model - but probably in the physical model too), using Subtype/Supertype techniques There was quite a lot of information overloading of individual fields going on - one field [...] 17147 38 38_Re: Row level locking on the catalog ?14_Thomas, Steven21_Steven_Thomas@BMC.COM30_Wed, 5 Apr 2000 03:19:03 -0500373_ISO-8859-1 There's a new DB2 APAR which suspends DDF activity and then resumes it. This was designed to allow DDL to execute since it was being stopped by locks on the catalog. Sounds ike you're suffering from the same problem and you might benefit from this. The problem is that it's for V6 only at the moment. See the IBM v6apars page off the IBM DB2 main web page. [...] 17186 97 37_DB2 UDb EXPORT LOCKS out applications13_Adrian Talbot29_Adrian.Talbot@BANKWEST.COM.AU30_Wed, 5 Apr 2000 16:35:41 +0800500_us-ascii Hi,





We are having the following problem and I have reported it via IBMLINK but thought that someone else on the list might have run into the same problem.





We are running DB2 UDB 5.2 Fixpack WR21119 running on Windows NT.

We have tried running an EXPORT (Select * from Table for READ ONLY) to extract data from a table whilst our online application was running and the EXPORT has caused our Online application to abend with Timeouts. [...] 17284 119 30_Re: Obsolete SYSIBM Databases?12_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE30_Wed, 5 Apr 2000 10:53:34 +0200580_- Thank you! I did so in our test system and everything works fine. I also found an APAR: II10546. /Seppo

> -----Original Message----- > From: Sue Janowitz [SMTP:SJanowitz@NEFN.COM] > Sent: Monday, April 03, 2000 2:13 PM > To: DB2-L@RYCI.COM > Subject: Re: Obsolete SYSIBM Databases? > > After dropping DSNDDF, you need to rebind QMF in order to avoid problems. > (I'd read about that on this list, so was prepared when we migrated to > version 5. I used the standard QMF bind JCL, and things went smoothly.) > > Sue Janowitz > DB2 Systems Administrator > New England [...] 17404 133 38_Re: Endeavor-controlled Data Migration13_Marcus Davage29_Marcus.Davage@LLOYDSTSB.CO.UK30_Wed, 5 Apr 2000 09:52:19 +0100473_iso-8859-1 Hi James

We actually meant Data, not DDL. i.e. there is/are data (business rules, processing rules) associated with code. When the code gets migrated, the associated data must be migrated too. Our people want all this automated and under the control of Endeavor.

Marcus

-----Original Message----- From: Kwan, James [mailto:James_Kwan@BMC.COM] Sent: 04 April 2000 22:05 To: DB2-L@RYCI.COM Subject: Re: Endeavor-controlled Data Migration [...] 17538 45 26_REXX for automated backups17_Kenneth J. Kripke22_kkripke@MINDSPRING.COM30_Wed, 5 Apr 2000 05:33:44 -0500109_iso-8859-1 You may want to check out http://www.cdbsoftware.com for automated tools.

Kenneth J. Kripke 17584 60 18_Re: DB2/RACF Rules11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Wed, 5 Apr 2000 08:37:03 -0400580_us-ascii RACF is consulted first. Lacking a definition in RACF, DB2 is consulted. RACF can deny the request but DB2 internal security can override its decision. So, you'll not be able to completely lockdown to using RACF, since the owner of an object will still be able to access that object. And, as you've probably already found, DB2 won't let you REVOKE an ownership privilege. On the same vein, when you bind a package, there will be DB2 grants issued for the tables that the package accesses. Setting a RACF permit to the EXECUTE privilege on a package will, in effect, [...] 17645 42 12_Re: DB2 ODBC19_Lisa Westcott-Dryer29_Lisa_Westcott-Dryer@AMWAY.COM30_Wed, 5 Apr 2000 08:43:39 -0400530_us-ascii Do you mean with the DB2 for Windows Connect product. If so, then yes.

Lisa







Charles Jambrosic @RYCI.COM> on 04/04/2000 06:08:15 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: DB2 ODBC



Has anyone out there any experience with Windows applications accessing DB2 UDB V6 on OS/390 via ODBC? [...] 17688 110 18_Re: DB2/RACF Rules4_IBM116_IBM1@UBIL.COM.TR30_Wed, 5 Apr 2000 15:53:54 +0300504_- hello there,

anybody familiar with RACF for DB2 Server on VSE/VM? I actually believe RACF is only available for VM, and I have a customer where I used to work a long time ago that have got into their funny little mind that I am their VM expert...

the question is: can you point me to publications on the matter, since I am rather sorry to let the client down before at least trying to reply (when I used to work with them with SQL/DS 3.5 for VM, RACF was a nice dream for them....). [...] 17799 453 44_FW: Fwd: DB2/390 Rexx question: Memory Leak?4_IBM116_IBM1@UBIL.COM.TR30_Wed, 5 Apr 2000 15:50:00 +0300649_ISO-8859-9 Greg,

how did your DBA get around this problem? I am asking because we are having much the same problem with one of our VM customers (therefore literally floating in REXX) and this sounds greatly familiar....

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it



> -----Original Message----- > From: IBM1 > Sent: Wednesday, April 05, 2000 10:29 AM > To: 'Pigi Di Giuseppe - IBM ITALIA (BDR)' > Subject: FW: Fwd: DB2/390 Rexx question: Memory Leak? > > ciao pigi, > > solo un idea per il problema del mediocredito... loro usano SOLO exec REXX > (il VM [...] 18253 18 35_DB2 Call Level Interface popularity10_Tom Taylor17_ttaylor@CHUBB.COM30_Wed, 5 Apr 2000 09:32:27 -0400541_us-ascii Hi all Does anyone have info on the call level interface (CLI). I most interested in usage, performance,stability and problems. We currently are using MQ to CICS to DB2 inorder to processes distributed requests. I was wondering if the CLI could/should be used in the same manner?

Tom

================================================ 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. 18272 131 41_Re: DB2 UDb EXPORT LOCKS out applications10_Mike Paris14_MPARIS@TUC.COM30_Wed, 5 Apr 2000 08:29:09 -0500341_US-ASCII Adrian,

Had the same problem in AIX UDB and rebound Export packages with UR to get around this behavior. Be aware that all exports will behave in the same manner until you rebind export with a different isolation level.

We do not have the same flexability in export in open systems as we do with unload on OS390. [...] 18404 82 21_Re: QMF Userid change10_Chiu, Jane18_jane.chiu@SSMB.COM30_Wed, 5 Apr 2000 09:20:13 -0400717_- We use the following queries to transfer the ownership of QMF objects:

UPDATE Q.OBJECT_DIRECTORY SET OWNER='new_userid' WHERE OWNER='old_userid' ; UPDATE Q.OBJECT_DATA SET OWNER='new_userid' WHERE OWNER='old_userid' ; UPDATE Q.OBJECT_REMARKS SET OWNER='new_userid' WHERE OWNER='old_userid' ;

Please not that all 3 queries must be run; otherwise, there will be discrepancies.

I hope this helps.

Jane Chiu

> ---------- > From: rick creech[SMTP:ykcirc@HOTMAIL.COM] > Reply To: DB2 Data Base Discussion List > Sent: Tuesday, April 04, 2000 8:59 PM > To: DB2-L@RYCI.COM > Subject: Re: QMF Userid change > > A method that would work would be to export the objects under one userid [...] 18487 23 25_Re: DB2 SORT WORK DSNDB0711_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Wed, 5 Apr 2000 08:35:13 -0500645_- Why not stop each tablespace one at a time, delete/redefine the vsam, then start tablespace. If you want to increase the number of tablespaces in DSNDB07, then the database object must be stopped.

Proactively, you can create several 'extra' tablespaces with small (1 cyl) vsam allocations and leave them in stop status. When increased space is needed in DSNDB07, then it is a simple matter to redefine these 'stopped' tablespaces, then start them, meanwhile stopping the smaller, undesirable tablespaces. Users will probably not notice any degradation in the work database. Regards, John Rosser Duke Energy My opinions are my own. [...] 18511 44 25_Re: DB2 SORT WORK DSNDB0722_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Wed, 5 Apr 2000 14:49:00 +0100493_ISO-8859-1 nice one .. the simple solutions are always the best.

Leslie

-----Original Message----- From: John Rosser at Internet Sent: Wednesday, April 05, 2000 8:35 AM To: DB2-L@RYCI.COM at INTERNET Subject: RE: DB2 SORT WORK DSNDB07



Why not stop each tablespace one at a time, delete/redefine the vsam, then start tablespace. If you want to increase the number of tablespaces in DSNDB07, then the database object must be stopped. [...] 18556 68 11_large tblsp15_Pelle Svennberg24_per-erik.svennberg@NB.SE28_Wed, 5 Apr 2000 15:08:17 GMT459_us-ascii How do I convert an ordinary tablespace to a large tablespace.





We have a tablespace declared as an ordinary tablespace

and it is controlled by OAM

- NUMPARTS is not given (becomes = non partitioned)





The tablespace has become very large and has filled the datasets .....A001 thru .....A032.

Every dataset is 2 Gb and times 32 makes the tablespace reach the upper limit of 64 Gb. [...] 18625 18 20_Archive logs and VTS11_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Wed, 5 Apr 2000 09:08:19 -0500381_- Currently, archiving to tape. One tape is used as DR copy, the other for local. Local hardware is VTS, and DR hardware is being replaced w/VTS. Any experiences with export/import of VTS volumes at a remote DR site? Anyone archiving to dasd, migrating (FDR/ABR) to tape, eventually to Magstar? Any input for active log sizing is appreciated! Regards, John Rosser Duke Energy [...] 18644 92 15_Re: large tblsp4_IBM116_IBM1@UBIL.COM.TR30_Wed, 5 Apr 2000 17:28:21 +0300639_- bad news I am afraid...

you cannot follow your scenario, I really think it would be lovely, too!!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it



> -----Original Message----- > From: Pelle Svennberg [SMTP:per-erik.svennberg@NB.SE] > Sent: Wednesday, April 05, 2000 6:08 PM > To: DB2-L@RYCI.COM > Subject: large tblsp > > How do I convert an ordinary tablespace to a large tablespace. > > > > We have a tablespace declared as an ordinary tablespace > > and it is controlled by OAM > > - NUMPARTS is not given (becomes = non partitioned) > > > > [...] 18737 106 15_Re: large tblsp15_Murley, Michael22_Michael_Murley@BMC.COM30_Wed, 5 Apr 2000 09:32:33 -0500355_ISO-8859-1 Pelle,

You'll have to UNLOAD/RELOAD the existing data. In a partitioned table space, the rows are assigned to data sets (partitions) based on the partitioning key value. But in a multi-data set, non-partitioned table space, rows are assigned to data sets simply on the basis of size. When A001 fills up, DB2 creates A002 and so on. [...] 18844 54 22_db2 UDB control center13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET30_Wed, 5 Apr 2000 10:39:23 -0400551_us-ascii I have sent message out but have not heard from anyone. Hopefully some IBM technical persons read these messages. I am not trying to bypass the normal IBM support, because we just found out that we have to buy another support package in order to use the support package we purchased with the product. However, I am trying to use the script center out of the control center. The script panel comes up but without any entry in the system drop down box. Naturally I cannot create a new script since it does not know what system to use. All [...] 18899 107 18_Re: DB2/RACF Rules17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Wed, 5 Apr 2000 09:50:13 -0500358_ISO-8859-1 >>>RACF can deny the request but DB2 internal >>>security can override its decision.

This isn't quit right, Joe.

RACF will either authorize (RC=0), deny (RC=8), or make no decision (RC=4).

If approved or denied, that means the resource IS RACF PROTECTED and the decision is final. The DB2 Catalog will not be referenced. [...] 19007 38 15_Re: large tblsp14_Massimo Scarpa16_mscarpa@CESVE.IT30_Wed, 5 Apr 2000 16:49:31 +0200402_us-ascii I did some month ago a conversion from 'normal' to LARGE TS........I'm afraid you must

UNLOAD/RELOAD your tables. If I understood well your 'procedure' you may lost, for instance

xxID internal DB2 identifier. I did some years as a similar trick just to have fun:

STOP TS

VSAM RENAME

DROP TS

CREATE TS

DELETE new vsam

RENAMEold vsam [...] 19046 67 47_Re: DB2 Maintenance 9909 (DB2 UDB for OS390 V5)12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV30_Wed, 5 Apr 2000 10:56:56 -0400379_iso-8859-1 Thanks for the info Jeff. I didn't make it clear in my previous post that we are still on V5, 9805 maint level, so I'm hoping that makes a difference. :)

Robert

-----Original Message----- From: Jeff Faughn [mailto:Jeff_Faughn@MAY-CO.COM] Sent: Tuesday, April 04, 2000 6:46 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Maintenance 9909 (DB2 UDB for OS390) [...] 19114 42 26_Re: db2 UDB control center14_Philip Gunning20_pgunning@BOSCOVS.COM30_Wed, 5 Apr 2000 12:56:46 -0400309_iso-8859-1 Well Jimmy, You need to have an instance created. The script center just pulls in the system names and then has a drop down list from which to select a system from. HTH Phil Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS [...] 19157 36 60_Getting Started w/ VB/Access/NT Data Connection to OS390 DB217_Dever, Gary (SGI)27_Gary.Dever@SPENCERGIFTS.COM30_Wed, 5 Apr 2000 09:20:13 -0700381_iso-8859-1 We are planning to develop applications in an NT environment using MS Access and/or Visual Basic and would like to setup direct data connections to OS/390 DB2. Our Mainframe environment is TCP/IP enabled in that we use FTP, Telnet and SMTP. Our OS/390 DB2 is V4, and will be V5 by summer. It sounds like DB2 Connect Personal Edition would be a good place to start. [...] 19194 77 26_Re: db2 UDB control center13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET30_Wed, 5 Apr 2000 13:16:25 -0400561_us-ascii i have an instance, two databases, tablespace, schema, and 2 tables created. they are on the solaris.. the control center is on the win nt, but connects and pulls up all the data in the main control center panel...

Philip Gunning wrote:

> Well Jimmy, You need to have an instance created. The script center just > pulls in the system names and then has a drop down list from which to select > a system from. HTH Phil > Philip K. Gunning > DB2 DBA > IBM Certified Advanced Technical Expert - DB2 DRDA > IBM Certified Solutions Expert [...] 19272 69 26_Re: db2 UDB control center14_Philip Gunning20_pgunning@BOSCOVS.COM30_Wed, 5 Apr 2000 13:24:00 -0400316_iso-8859-1 Jimmy, I have instances on serveral NT servers, and one on AIX and have no problems with them appearing in the script center. I am at DB2 UDB 6.1 FP1 and 3. What level are at? Phil Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS [...] 19342 104 26_Re: db2 UDB control center13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET30_Wed, 5 Apr 2000 13:24:27 -0400267_us-ascii I am at db2 udb v6.1. We just installed on the solaris and the win nt. I have used the control center to create the tables and table spaces once I created the database on the server. One other thing I cannot do, is generate ddl from the control center. [...] 19447 65 64_Re: Getting Started w/ VB/Access/NT Data Connection to OS390 DB214_Philip Gunning20_pgunning@BOSCOVS.COM30_Wed, 5 Apr 2000 13:29:41 -0400512_iso-8859-1 Gary, At DB2 V4.1 you can only use SNA. There are two ways you can go. Use DB2 Connect EE on NT, with an SNA stack, We used to use Comm Server. This gives you connectivity to DB2 for OS/390. We later converted that to TCP/IP. If you use PE, you get an integrated SNA stack; and it is also easily convertible to TCP/IP. Host VTAM definiitons (independent LU's) need to be set up. To use SNA either on EE or PE requires the same amount of VTAM setup, whether or not you are using EE or PE. I would [...] 19513 30 31_DDF - DISTRIBUTED DATA FACILITY11_HELIO ROCHA22_HPROCHA@ROCKETMAIL.COM30_Wed, 5 Apr 2000 11:06:14 -0700437_us-ascii HI ALL

IŽD LIKE TO KNOW SOMETHING ABOUT THE DDF - DISTRIBUTED DATA FACILITY.

WHAT ARE YOU OPINION ABOUT IT?

IS THERE ANOTHER SOLUTION WHEN WE DONŽT HAVE DATA SHARING IN OUR SITE AND WE WANT TO CONNECT TWO DB2?

THANK YOU

HELIO TELEFONICA/BRAZIL

__________________________________________________ Do You Yahoo!? Talk to your friends online with Yahoo! Messenger. http://im.yahoo.com [...] 19544 244 15_Update question23_Trivedi, Kaushal J, BGM17_kjtrivedi@ATT.COM30_Wed, 5 Apr 2000 14:24:21 -0400487_iso-8859-1 Hi All,

I would like to know if this is possible.

UPDATE TABLE_A SET COLX = COLY (FROM TABLE B) FOR A MATCH OF COL1, COL2 AND COL3 ON TABLEA AND TABLEB.

Thank you

regards

Kaushal Trivedi DBA Universal Biller (732)-457-2858



-----Original Message----- From: Toine Michielse [mailto:vndobtm@US.IBM.COM] Sent: Saturday, April 01, 2000 9:27 AM To: DB2-L@RYCI.COM Subject: Re: Recovery - appreciate feedback on how to do wiser [...] 19789 172 41_Table sharing across multiple DB2 regions16_Mike Friedlander30_friedlander@FIRSTINVESTORS.COM30_Wed, 5 Apr 2000 14:28:22 -0400344_iso-8859-1 Can anyone tell me if it is possible to share databases between two LPARS with shared DASD, one running v4 and the other v5? If it is possible to do so how would one go about it? Any info would be greatly appreciated. BTW in this instance the v5 LPAR may only need read access. Thanks, Mike Friedlander friedlander@firstinvestors.com 19962 32 13_Secondary Ids15_Antonio Ramalho20_aramalho@HOTMAIL.COM28_Wed, 5 Apr 2000 15:33:48 EST451_- Hi, everybody

Suppose I have the following:

- Secondary Id authorizations - 4 DB2 (Prod, Test1, Test2, Test3) - For each access type for each DB2 we have a group with the appropriate authority - Users are connected to the appropriate groups (for diferents DB2)

The question:

If a user start a batch job, what group will be used for DB2 authorization ? Will the DB2 look for the appropriate group for authorization ? [...] 19995 20 15_DB2 Online Help11_Daniel Case25_Dan.Case@PKSIS.LEVEL3.COM30_Wed, 5 Apr 2000 12:41:59 -0600527_iso-8859-1 I've already migrated to DB2 V5.1 without the DB2 Online Help. Can I now go back and install the Online Help for DB2???

Daniel R. Case Data Base Analyst, OLTP and Database Services PKS Information Services, Inc. Phone: (888) 221-3494 E-Mail: Dan.Case@pksis.level3.com

================================================ 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. 20016 19 23_ERwin vs. PowerDesigner7_Db2 Dba24_data_analyst@HOTMAIL.COM30_Wed, 5 Apr 2000 13:53:38 -0500348_- I am modelling a database for UDB 6.1 using ERwin. For creation of the physical model, ERwin does not support DB2 long names. Therefore, we are thinking of switching to another tool, e.g., PowerDesigner.

Does anyone have experience with PowerDesigner and UDB? Would I lose any functionality if I switched from ERwin to PowerDesigner? [...] 20036 60 28_Tool for monitor performance22_Suda Petchrungratsamee15_sudap@SCB.CO.TH30_Thu, 6 Apr 2000 02:40:34 +0700292_windows-874 Hi all, I event monitor for tuning performance DB2 UDB 5.0 on unix. If there are many transactions.It difficult to quick know what 's happen. Are there tool for summary event monitor or help tuning performance? anyone have experience ,please suggest me too.

Thanks.

20097 116 24_Re: DRDA/DDF help needed23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Wed, 5 Apr 2000 15:39:51 -0400345_- Ed,

Populate the LUNAMES table instead. I think DB2 uses SNA (not TCPIP), when it communicates with another DB2 on main frame using 3 part name which falls under private protocol. This again changes with ver 6.1 where a 3 part name is covered by DRDA (private protocol is no more supported). I could be wrong, its been a while !!! [...] 20214 41 27_Re: ERwin vs. PowerDesigner12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Wed, 5 Apr 2000 14:38:34 -0500522_us-ascii Hello.

We're using PowerDesigner 6.1.4 and will be ordering PowerDesigner 7.0 soon, (based on success I've had with the demo copy I downloaded from their site.) (Actually, we're only using the Physical Architect piece -- formerly called AppModeler.)

The bottom line for me is that version 7.0 definately talks to DB2 UDB for OS/390 V6 much better than version 6.1.4. (I had to create my own "def" file (that's like an "ini" type file) for 6.1.4 for DB2 OS/390 V6 -- version 7.0 supplies this.) [...] 20256 32 41_stored procedure error handling questions10_Cathie Jin16_yejin@US.IBM.COM30_Wed, 5 Apr 2000 15:30:49 -0400515_us-ascii Hi,

Q1: I'm writing a stored procedure in C that sets SQLSTATE= 02000 to indicate no result found. The CLI program that calls the stored procedure gets SQLCODE=-463 and SQLSTATE=39001 meaning invalid SQLSTATE set by the user-defined function. Does anyone know why setting SQLSTATE to 02000 is invalid?

Q2: The SQLERRMC resulted from error occurred in stored procedure call had only 63 character, which is different from CHAR(70) described in DB2 Application Programming Guide. Any clue? [...] 20289 35 32_Re: Tool for monitor performance10_Mike Paris14_MPARIS@TUC.COM30_Wed, 5 Apr 2000 14:52:49 -0500628_US-ASCII We've had great success with Database Guys at this site. Check our http//www.databaseguys.com for info.

Michael Paris

Senior Database Analyst IBM Certified Solutions Expert DB2 UDB Database Administration IBM Certified Advanced Technical Expert Data Replication

Trans Union





>>> sudap@SCB.CO.TH 04/05 2:40 PM >>> Hi all, I event monitor for tuning performance DB2 UDB 5.0 on unix. If there are many transactions.It difficult to quick know what 's happen. Are there tool for summary event monitor or help tuning performance? anyone have experience ,please suggest me too. [...] 20325 16 19_Re: DB2 Online Help25_Edward(Ed) J. Finnell,III23_efinnell@SEEBECK.UA.EDU30_Wed, 5 Apr 2000 14:59:02 -0500386_us-ascii Sure. Just receive and apply. If you're got Book manager you don't need to do it. Don't put the EOYLPA in LPA, LINKLST seems just fine.

================================================ 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. 20342 80 17_Re: Secondary Ids17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Wed, 5 Apr 2000 15:04:12 -0500310_ISO-8859-1 Hi Antonio,

Suppose my UserID=KENK and is connected to your groups Test1, Test2 and Test3. In subsystem BMC (needed a shameless plug :-) ) Test1 is DBADM on my database while Test2 and Test3 have SELECT auth on all tables in the system. KENK has no authority granted directly to the ID. [...] 20423 24 33_DB2 SQL error handling techniques14_Theisen, Craig22_CTheisen@GUIDEMAIL.COM30_Wed, 5 Apr 2000 14:55:56 -0500553_iso-8859-1 We are a OS/390 DB2 shop on Version 5.1, going to 6.0 later this year .

We are in the process of writing new programming standards on handling SQL errors in our applications (Stored Procedures, COBOL , JAVA etc), and I am looking for good ideas to incorporate into our shop. I searched the Archives for the past 2 years and didn't really find much. Does anyone care to share some techniques they use and/or point me to a good publication that covers Best Practices using DSNTAIR etc? I want to be thorough so we do not need to do [...] 20448 170 24_Re: DRDA/DDF help needed12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 5 Apr 2000 13:17:23 -0700574_us-ascii DB2 will use TCPIP to communicate with another DB2 using 3 part names. The key is that data for IPNAMES, LOCATIONS must be correctly entered. I think your problem Ed is that you haven't entered allof the information needed (if any) in IPNAMES.

--- "Jain, Sanjay (Exchange)" wrote: > Ed, > > Populate the LUNAMES table instead. I think DB2 uses > SNA (not TCPIP), when > it communicates with another DB2 on main frame using > 3 part name which falls > under private protocol. This again changes with ver > 6.1 where a 3 part name > [...] 20619 97 21_Re: QMF Userid change40_Andersen, Cindy (CC-Application Support)26_Cindy.Andersen@CONAGRA.COM30_Wed, 5 Apr 2000 15:10:33 -0500299_iso-8859-1 I also used the method Jane indicates, but you need to be aware that if the new_userid is an existing id and you are just adding the old-userid's objects to this existing id, you may run into duplicate query, form, or proc names that will have to be resolved for the update to work. [...] 20717 41 30_Re: REXX for automated backups12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Wed, 5 Apr 2000 21:24:37 +0200466_x-user-defined Hi,

I've sent some REXX examples to the document part of the list. IMAGECOPY REORG RUNSTATS DSN1COPY (a new version of it will be sent tomorrow).

A little tailoring is needed (like job cards, file names etc.)

I did not use file tailoring to lower the amount of sources used. Some REXX use a private "address sql" - you can change it to your own or use an example of doing it with DSNTIAUL which is used in one of the REXXes. [...] 20759 29 24_UDB v6.1 on AIX Question0_22_BILL_GALLAGHER@PHL.COM30_Wed, 5 Apr 2000 16:39:13 -0400583_us-ascii Hi,

There is a hidden file link named ".ftok" in the "sqllib" directory for each of our UDB instances on AIX that appears to be a link to itself. For instance:

/home/db2as/sqllib/.ftok

Does anybody know what this is for? Apparently, our AIX system administrators have noticed that this is causing problems for our nightly system ADSM backups (puts it in an infinite loop trying to resolve the link). They can tell ADSM to exclude trying to backup this file to get around the problem, but I'm just curious to see what anybody might know about this. [...] 20789 54 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Wed, 5 Apr 2000 15:51:03 -0500693_us-ascii Joel - I will get this info back to you. I just had some other fires to put out here.

Larry



|--------+-------------------------------> | | Joel Goldstein | | | | | | | | | 04/04/2000 07:49 PM | | | Please respond to DB2| | | Data Base Discussion | | | List | | | | |--------+-------------------------------> >----------------------------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: (bcc: L-IS Kirkpatrick/MutualOMA) | | Subject: Re: More testing of VPSEQT/HPSEQT as it applies to "LIST| | PREFETCH " | >----------------------------------------------------------------------------| [...] 20844 18 34_DB2 version 6 with IMS version 5.10_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Wed, 5 Apr 2000 15:54:18 -0500417_us-ascii Is anyone running with IMS version 5.1 talking to DB2 version 6? Is this combination supposed to work together?

Thanks

Larry Kirkpatrick Mutual of Omaha

================================================ 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. 20863 610 44_Re: DB2 Maintenance 9909 (DB2 UDB for OS390)21_Rechsteiner, Tom (TH)20_trechsteiner@DOW.COM30_Wed, 5 Apr 2000 16:10:35 -0500611_iso-8859-1 Hi Robert,

Seems we have the same maintenance approach. I just completed the SUP 9927 refresh for DB2 v5.1 which was made available Sept 18, 1999.

Below is an .htm attachment of the noticable apars from SUP (refresh) Tape 9901 to 9927, I had also ask IBM the same question, to get all APARS, in ibmlink; SERVICELink Problem Resolution, Q/A and Preventive Service SIS - Search for Service and Support Information PTFS Search software PTF cover letters Search for: HDB5510 Fxxx R510 (where xxx is the last 3 digits of the PUT maintenance) PUT maintenance is YYMM, SUP maintenance is [...] 21474 15 17_Re: DB2 V6 Limits12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 5 Apr 2000 16:30:16 -0500308_- There is one addition. With the star join changes, APAR PQ28813 and others, the limit in the FROM clause was increased (but only for star joins). We've seen a few joins of about 30 tables, but the absolute limit seems to be memory and the complexity of the design.

Roger Miller, DB2 for OS/390 [...] 21490 16 38_Re: DB2 version 6 with IMS version 5.112_Roger Miller19_millerrl@US.IBM.COM30_Wed, 5 Apr 2000 16:34:00 -0500465_- The What's New? book and announcements always have this information. For example, try http://www.ibm.com/software/data/db2/os390/whatsnew/whatv626.htm#Header_133

IMS V5 is there.

Roger Miller, DB2 for OS/390

================================================ 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. 21507 137 24_Re: DRDA/DDF help needed23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Wed, 5 Apr 2000 17:55:38 -0400575_- Also ...if memory serves me right, you have to

1. Define record DDF record in BSDS via DSNJU003 2 .Stop and start DDF

HTH

Sanjay

> -----Original Message----- > From: Jain, Sanjay (Exchange) > Sent: Wednesday, April 05, 2000 3:40 PM > To: 'DB2 Data Base Discussion List' > Subject: RE: DRDA/DDF help needed > > Ed, > > Populate the LUNAMES table instead. I think DB2 uses SNA (not TCPIP), when > it communicates with another DB2 on main frame using 3 part name which > falls under private protocol. This again changes with ver 6.1 where a 3 [...] 21645 28 43_Re: Does CPU upgrade affect DB2 optimizer ?12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 5 Apr 2000 16:58:05 -0500402_- The processor speed can affect optimization, as we say for instance on the first page of chapter 5-9, Maintaining statistics in the Catalog of my V5 Administration Guide.

How the optimizer works is both very complicated and very proprietary. It changes with every release and sometimes in PTFs. The IBM documentation is how to use it, with the three chapters in the Administration Guide: [...] 21674 21 13_RCT questions12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Wed, 5 Apr 2000 17:09:00 -0400308_- In your shop: What is the maximum number of threads connected from a CICS region to DB2 at one time? What is the maximum number of DB2 threads reached for a specific CICS transaction?

I know "it depends" but, I'm trying to get an idea of how many concurrent DB2 users a CICS region can handle. [...] 21696 22 38_Re: DB2 version 6 with IMS version 5.10_17_JTonchick@AOL.COM28_Wed, 5 Apr 2000 18:21:22 EDT323_US-ASCII I have not heard that IMS v5.1 will not work with DB2 V6. The IMS/DB2 interface is not as dependent upon releases as the CICS/DB2 interface. To connect IMS to DB2, just put the SDSNLOAD in the region JCL (or linklist) and add the parm card to the IMS PROCLIB and you're done! (no CICS RCT like requirements). [...] 21719 26 36_Re: DB2 V5 versus DB2 V6 / Resources12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 5 Apr 2000 17:11:59 -0500599_- The biggest improvements are fairly well documented in the DB2 Version 6 Performance Topics red book, SG24-5351.

Go out to www.ibm.com/redbooks Put SG24-5351 in the search and click Go. Click on the book name.

Click on View Online - or I usually use Netscape and right click to download the book.

The amount of storage used has often increased a bit to reduce the cpu time. The biggest improvements are in utilities and queries. Other than where the star join was used, I'm not seeing much regression, and we have the APAR for that problem, PQ36206, closed, with the PTF [...] 21746 46 30_Re: Links about DB2 for OS/39012_Roger Miller19_millerrl@US.IBM.COM30_Wed, 5 Apr 2000 17:25:52 -0500538_- Here are the web links I use most often:

IBM www.ibm.com www.ibm.com/isource IBM Software www.ibm.com/software IBM Data Management: white papers, products www.ibm.com/software/data DB2 Family www.ibm.com/software/db2 DB2 for OS/390 www.ibm.com/software/db2os390 a.k.a. home Get books, latest information, papers, ... DB2 Family Performance www.ibm.com/software/db2/performance

Red Books www.ibm.com/redbooks DB2 Magazine www.db2mag.com DB2 User Groups www.idug.org www.share.org developerWorks www.ibm.com/developer/ [...] 21793 31 33_Re: Performance of JDBC on OS/39012_Roger Miller19_millerrl@US.IBM.COM30_Wed, 5 Apr 2000 17:56:12 -0500297_- You should probably look at trying the updated version in the just closed APARs. They are new enough that you might want to do this on the system programmer subsystem for a while. The initial feedback has been much improved performance.

V5 APAR PQ19814 for V5 V6 APAR PQ36011 for V6 [...] 21825 71 12_Re: DB2 ODBC17_Charles Jambrosic29_Charles_Jambrosic@CSIHOME.COM30_Wed, 5 Apr 2000 16:11:37 -0700445_us-ascii Lisa,

The setup is Microsoft's ODBC with DB2 CAE (Client application enabler) on Windows 95 and a TCP/IP connection to the OS/390 DB2 V6 UDB region where DB2 is the server. Since we are using DB2 as the server we bypassed DB2 Connect.









Lisa Westcott-Dryer on 04/05/2000 05:43:39 AM

Please respond to DB2 Data Base Discussion List [...] 21897 24 45_Re: Table sharing across multiple DB2 regions12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 5 Apr 2000 18:12:12 -0500346_- This is called Data Sharing, and we recommend that mixing of releases be done for a relatively short period of time, such as a weekend or a couple of weeks. When customers try it for a long time, they often get something that depends upon V5 and try to access it from V4. Such operational glitches are tricky to manage over the long term. [...] 21922 15 42_Can we tell when a table was last updated?7_Db2 Dba24_data_analyst@HOTMAIL.COM30_Wed, 5 Apr 2000 18:36:09 -0500369_- Hi,

Using UDB 6.1, is there any column in the System Catalog to indicate when a table was last updated?

Thanks.

================================================ 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. 21938 115 12_Re: DB2 ODBC15_Richard Simpson25_rsimpson@MACQUARIE.COM.AU30_Thu, 6 Apr 2000 10:46:22 +1000321_iso-8859-1 Are you sure abou this ?

As far as I am aware, DB2 CAE cannot talk directly to DB2 for OS/390 version anything (it CAN to DB2 v6.1 for Workstations).

DB2 for OS/390 talks DRDA, DB2 CAE does not. The documentation I have says there is a problem here.

Do you actually have it working ? [...] 22054 428 41_Re: DB2 UDb EXPORT LOCKS out applications11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU30_Thu, 6 Apr 2000 11:14:14 +1000390_iso-8859-1 Adrian,

just to confirm Michael's comments. We had the same problem a couple of years ago and also found that Export ignores the isolation level that you set in your script or online session. It looks like the only way to change it is to rebind the Export packages with UR (or CS or whatever). We could not find anything explicit on this in the documentation either. [...] 22483 282 19_Re: Update question10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Thu, 6 Apr 2000 02:56:13 GMT442_us-ascii I hope you want something like this

UPDATE TABLE_A SET COLX = (select COLY FROM TABLE B where ......).....(one row should come out from inner query. FOR A MATCH OF COL1, COL2 AND COL3 ON TABLEA

Not possible till DB2 for OS/390 V5.If you want something else please specify the query , DB2 platform and version you are using.However it was discussed in the list that it is not possible in DB2 V6 for OS/390 as well. [...] 22766 49 46_Re: Can we tell when a table was last updated?14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 6 Apr 2000 13:21:35 +1000337_iso-8859-1 There is no place where the actual information you want is kept in a nice way. However, you can get somewhat near by either:

- looking at DSNDB01.SYSLGRNX to find the last stopping RBA for the tablespace holding the table - looking at every page header in the tablespace to find the latest RBA in the page header. [...] 22816 64 45_Re: stored procedure error handling questions14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 6 Apr 2000 13:30:24 +1000366_iso-8859-1 Cathie,

I presume you are using DB2 UDB OS/390 V6.

1) Was the SP defined to return a result set? If not, then I think (no direct personal experience) DB2 is indicating that 02000 is used to state that there are no more rows in a result set. Since you are not using results sets, 02000 is invalid. You will have to use a 01Hxx sqlstate. [...] 22881 14 7_DR step16_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU30_Wed, 5 Apr 2000 20:51:00 -0800493_- The DB2 V6 Admin Guide disaster recovery procedures say that after the user tablespaces and indexes are recovered, you should issue commands to START DATABASE db.ts ACCESS(RW).

Is this really necessary? Aren't recovered TS already in RW mode?

================================================ 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. 22896 35 11_Re: DR step22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 6 Apr 2000 06:07:00 +0100423_ISO-8859-1 John yes they are . Leslie

-----Original Message----- From: "JOHN G. MATTHEWS" at Internet Sent: Wednesday, April 05, 2000 8:51 PM To: DB2-L@RYCI.COM at INTERNET Subject: DR step



The DB2 V6 Admin Guide disaster recovery procedures say that after the user tablespaces and indexes are recovered, you should issue commands to START DATABASE db.ts ACCESS(RW). [...] 22932 40 46_Re: Can we tell when a table was last updated?10_Leo Flores23_leoflores@EARTHLINK.NET30_Wed, 5 Apr 2000 23:24:45 -0700697_us-ascii Hi,

Try this to select every table that has been updated since its' creation. You could code more where's and/or order by ..........

select DBNAME, TSNAME, CREATOR, NAME, CREATEDTS, ALTEREDTS from SYSIBM.SYSTABLES TA where TYPE = 'T' and ALTEREDTS > CREATEDTS

HTH

Leo Flores







Db2 Dba wrote:

> Hi, > > Using UDB 6.1, is there any column in the System Catalog to indicate when a > table was last updated? > > Thanks. > > ================================================ > 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 [...] 22973 45 38_Re: DB2 version 6 with IMS version 5.14_IBM116_IBM1@UBIL.COM.TR30_Thu, 6 Apr 2000 09:27:13 +0300377_- yes it is.

Banca di Roma have this kind of scenario, try and ask for Roberto Mandolini's help - he subscribes to this list too... - although he is a bit busy at the moment since their DB2 is being continuosly faced with the infamous -818 / -106's!!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it [...] 23019 20 8_CA World22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 6 Apr 2000 07:38:00 +0100387_ISO-8859-1 Hi all, Anybody else going to CA world and fancy meeting up? New Orleans next week .. regards Leslie Pendlebury-Bowe DB2 DBA OS390 SAP

================================================ 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. 23040 258 46_Re: Can we tell when a table was last updated?11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU30_Thu, 6 Apr 2000 16:52:13 +1000351_- I hate this "UDB" business!!! I believe (looking at previous notes) the 'UDB' in this case is actually the "original" UDB, i.e. UDB for Linux, Unix, NT, OS/2, aka "LUNO", and not OS/390 (and not AS/400 either...). But with our current ambiguous terminology it is not surprising that we keep talking about different things without realising it. [...] 23299 95 46_Re: Can we tell when a table was last updated?0_29_Greg.Palgrave@BANKWEST.COM.AU30_Thu, 6 Apr 2000 15:18:49 +0800556_us-ascii Leo,

This only tells you when a table was ALTERed (add column etc.) , not when it was updated (DML Insert, Delete, or Update).

Cheers, Greg Palgrave Database Administration IT Services Bank of Western Australia eMail : greg.palgrave@bankwest.com.au



---------------------------------------- Message History ----------------------------------------



From: Leo Flores on 06/04/2000 14:24





Please respond to DB2 Data Base Discussion List [...] 23395 22 37_=?iso-8859-1?B?u9i4tDogQ0EgV29ybGQ=?=10_Michale Yu19_yubin@MAIL.DCAC.COM30_Thu, 6 Apr 2000 15:15:12 +0800518_iso-8859-1 Hi Leslie:

I will go to New Orleans this week-end, hope to meet you there.

Yu Bin(Michale Yu) From CHINA WUHAN 04/06/2000

Leslie Pendlebury-Bowe DB2 DBA OS390 SAP asked: Anybody else going to CA world and fancy meeting up? New Orleans next week

================================================ 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. 23418 94 43_Re: Does CPU upgrade affect DB2 optimizer ?17_Erkki Søndergaard20_esonderg@SCA.CSC.COM30_Thu, 6 Apr 2000 10:34:00 +0200392_iso-8859-1





The different access paths picked by the optimizer on different CPU platforms has bitten us badly on a major development project. The development was done on a H5 type CPU and production is on a H6. We had a brief test on a H7 type CPU and with DBRMs, DATA and PTF-level identical we got different access paths on all 3 platforms for more complex SQL. [...] 23513 42 14_REXX & DSNWLIR14_Massimo Scarpa16_mscarpa@CESVE.IT30_Thu, 6 Apr 2000 10:44:06 +0200351_us-ascii I'm trying to call IFI thru a REXX pgm to check if it's possible to read IFI records without an

assembler pgm (I don't know assembler and we don't have a PL/I compiler....). There are

examples in REXX language support for DB2 manual.

I use a LINKPGM to DSNWLIR passing requested parameter, but every time I obtain a [...] 23556 62 41_R: Re: DB2 version 6 with IMS version 5.117_Roberto Mandolini27_roberto.mandolini@LIBERO.IT30_Thu, 6 Apr 2000 10:49:46 +0200485_iso-8859-1 ----- Original Message ----- From: IBM1 Newsgroups: bit.listserv.db2-l To: Sent: Thursday, April 06, 2000 8:27 AM Subject: Re: DB2 version 6 with IMS version 5.1



yes it is.

Banca di Roma have this kind of scenario, try and ask for Roberto Mandolini's help - he subscribes to this list too... - although he is a bit busy at the moment since their DB2 is being continuosly faced with the infamous -818 / -106's!! [...] 23619 63 18_Re: REXX & DSNWLIR22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 6 Apr 2000 09:56:00 +0100447_ISO-8859-1 use the db2 rexx interface - I think???? V5 / V6 only

but I would be interested to know if you can do what you ask ..

Leslie

-----Original Message----- From: Massimo Scarpa at Internet Sent: Thursday, April 06, 2000 10:44 AM To: DB2-L@RYCI.COM at INTERNET Subject: REXX & DSNWLIR



I'm trying to call IFI thru a REXX pgm to check if it's possible to read IFI records without an [...] 23683 54 11_Re: DR step12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Thu, 6 Apr 2000 10:10:00 +0100374_- Interesting, in the V4.1 manuals an earlier step in the procedures says start all user table spaces and index spaces for utility only processing. In the V6.1 manuals there are significant differences to the order of processing in the recovery instructions. One difference is the ACCESS(MAINT) is removed quite early in the proceedings, which looks a bit dodgy to me. [...] 23738 30 18_Re: REXX & DSNWLIR14_Massimo Scarpa16_mscarpa@CESVE.IT30_Thu, 6 Apr 2000 11:18:31 +0200306_us-ascii Hi Leslie, THX for the answer. I know, may be a strange request....

We have DB2 v5 and yes, I try a mix (CONNECT + DSNWLIR, DSNALI + CONNECT +

DSNWLIR ) using new REXX support.

I'm (pretty well) sure I can read IFI via REXX (I saw an example in REXX Language Support for [...] 23769 30 14_CLI 0129 error14_Rajesh Shewani22_rajeshs@DOMINOMAIL.COM28_Thu, 6 Apr 2000 09:29:05 PST390_iso-8859-1 Hi,

We are working on an application with the foll details

Front end - HTML(Browser based) Middle - Java Servlets RDBMS - DB2 UDB Personal Edition 5.2

The application is on some stand alone pcs with Win 98 and PWS.

The problem is that when trying to retrieve records, only 32 records are retrieved and CLI0129 NO MORE HANDLES error is thrown up. [...] 23800 98 11_Re: DR step22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 6 Apr 2000 10:48:00 +0100402_ISO-8859-1 Colin/John

You only need to have access maint up to the end of the recovery of the DB2 Catalog and Directory.

Then , if you so wish and I do not to ensure the quickest approach to recovery , you could start db2 normal mode and then plow into the User Pageset (and Index pageset V6) recoveries ... if done to completion code 0 or 4 then all pagesets will be in R/W status. [...] 23899 143 43_Re: Does CPU upgrade affect DB2 optimizer ?17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM30_Thu, 6 Apr 2000 11:57:55 +0100384_iso-8859-1 Hi

SPRMCPU can be changed to represent SRB execution time per Service Unit for the CPU. You can find the required info in "OS/390 V1R2.0 MVS Initialization and Tuning Guide" under the topic "3.5.2.2 Selecting Service Definition Coefficients (Worksheet Number 1)" which is a table where you can get SRB execution time per service unit vis. a vis. the CPU model. [...] 24043 44 11_Re: DR step10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Thu, 6 Apr 2000 09:55:23 GMT694_us-ascii Not neccessary, if you had started it with ACCESS(UT) before running recovery.

Regards

Sanjeev









"JOHN G. MATTHEWS" @RYCI.COM> on 04/06/2000 11:21:00 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: DR step



The DB2 V6 Admin Guide disaster recovery procedures say that after the user tablespaces and indexes are recovered, you should issue commands to START DATABASE db.ts ACCESS(RW). Is this really necessary? Aren't recovered TS already in RW mode? [...] 24088 34 32_DB2 Capacity Planning - M-Values14_Adrian Collett27_adriancollett@TISCALINET.IT30_Thu, 6 Apr 2000 13:26:02 +0200384_iso-8859-1 This one is worth at least 2 pints to the first correct answer:-

If somebody was unfortunate enough to be involved in a DB2 Capacity Planning Project which followed the methodology laid out in the Redbook:- DB2 for OS390 Capacity Planning(SG24-2244-00), where would they find an updated list of the M-Values used in the final(and most important) calculation ?? [...] 24123 117 43_Re: Does CPU upgrade affect DB2 optimizer ?12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Thu, 6 Apr 2000 06:36:55 -0500436_iso-8859-1 Erkki:

The actual values are stored in the macro DSNDSPRM which is created as part of the DSNZPARM gen. The SPRMCPU is a bit flag, set to zero by default which means use the actual machine's CPU Microsecond value. The field SPRMCPUM is FL4 and stores the CPU Service time in microseconds for the computer. If set to other than zero, the default then this value should be used instead of the actual machine value. [...] 24241 35 12_Re: CA World12_craig patton21_prgpatton@HOTMAIL.COM28_Thu, 6 Apr 2000 04:41:38 PDT617_- Nope.

>From: Leslie Pendlebury-Bowe > >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: CA World >Date: Thu, 6 Apr 2000 07:38:00 +0100 > >Hi all, >Anybody else going to CA world and fancy meeting up? New Orleans next week >.. >regards >Leslie Pendlebury-Bowe >DB2 DBA >OS390 SAP > >================================================ >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. [...] 24277 194 43_Re: Does CPU upgrade affect DB2 optimizer ?17_Erkki Søndergaard20_esonderg@SCA.CSC.COM30_Thu, 6 Apr 2000 13:52:34 +0200592_iso-8859-1 Hi Gerald, and the rest of the list.

Yes, I'm interested in your program.

What's even more interesting is the fact that this field is resolved during assembly of DSNZPARM and not during the start-up of DB2. Why is this interesting ?? Well, we're a facility management shop with 30+ DB2 subsystems on 10+ MVS images spanning 3 different CPU types, but we assemble all DSNZPARMs on the same MVS before we distribute them to the target systems! Before you jump to conclusions I better add that production system I referred to in my previous mail on this subject is [...] 24472 24 13_DB2 for Linux11_Oleg Zhukov19_zhk@IBS-LTD.KIEV.UA30_Thu, 6 Apr 2000 14:49:53 +0300536_koi8-r Hello,

Could anybody prompt, how to create database with codeset KOI8-R? Operation environment - DB2 for Linux.

Thank you in advance,

Oleg Zhukov, System Engineer S/390 IBS Ltd., Floor 9, 5a, Narodnogo opolchenia str., Kiev, 03151, Ukraine Phone: +380 (44) 246-23-81

================================================ 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. 24497 17 14_SQL Procedures19_Scarlett McLaughlin29_SMcLaughlin@CONSULTEC-INC.COM30_Thu, 6 Apr 2000 08:02:38 -0400442_US-ASCII Is anyone using SQL Procedures (available with V5 & V6)? If so, how do they perform compared to regular Stored Procedures? Are you using the SQL Procedure Builder? Thanks, Scarlett McLaughlin

================================================ 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. 24515 122 11_Re: DR step12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Thu, 6 Apr 2000 13:04:00 +0100450_iso-8859-1 Having looked at it a bit longer, I think that I was assuming that the DB2 recovery would be on top of a full volume restore (our normal practice), whereas the manual is assuming empty disks. In my case there is a chance of users nipping in and updating things before I recover them. I know there are other ways of keeping the users out, but I like things to be straightforward and foolproof, after all, we don't go to DR very often. [...] 24638 23 46_Re: Can we tell when a table was last updated?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Thu, 6 Apr 2000 08:11:38 -0400437_iso-8859-1 As everyone else has correctly stated, there is no system-provided way to determine this.

Many applications(every application I've supported) solve this in the application with a Timestamp column on each table which is updated with each change. Unfortunately one has to rely on the application to do this for it to be reliable data. And with purchased packages, you rely on the package to provide this capability. [...] 24662 112 43_Re: Does CPU upgrade affect DB2 optimizer ?19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 6 Apr 2000 07:26:28 -0500640_iso-8859-1 Hi Nagaraj, Have you actually done this? Can I directly enter the decimal value of Service Units, e.g. 632.2?

Best Regards, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, [...] 24775 23 45_Re: Table sharing across multiple DB2 regions11_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Thu, 6 Apr 2000 07:21:14 -0500415_- If you don't want the overhead, etc of implementing parallal sysplex, then enabling data sharing and perhaps having to unload from one DB2 into the DS group, then you could read a table in another DB2 using distributed processing. But that opens up concerns about network management, for example, how much data will be accessed and moved across the network or will the data ever be updated by the requestor? [...] 24799 22 17_OPn traces in DB211_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Thu, 6 Apr 2000 07:28:07 -0500376_- Dear DB2-L, Since most monitors and other products require a MON trace to be started, is there a method to determine who/what/when started the MON trace? There is IFCID 90 to capture the text of a DB2 command, but it is yet another trace. Not to mention trying to decipher the trace data! OPn is limited to 8 by DB2. With so many products using them, I am running out. [...] 24822 55 18_Re: REXX & DSNWLIR12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM30_Thu, 6 Apr 2000 08:48:20 -0400545_us-ascii One of the things the book says about using IFI is that a thread must be made before the IFI call can be done. Try getting current timestamp or some other SQL call before the IFI call.



Martin.

Massimo Scarpa wrote:

> I'm trying to call IFI thru a REXX pgm to check if it's possible to read IFI > records without an > > assembler pgm (I don't know assembler and we don't have a PL/I compiler....). > There are > > examples in REXX language support for DB2 manual. > > I use a LINKPGM to DSNWLIR passing [...] 24878 55 21_Re: OPn traces in DB219_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 6 Apr 2000 08:00:31 -0500583_iso-8859-1 Hi John, The -START TRACE command has a COMMENT arg that you could populate with product info. Might be a bit of a problem to find and edit the command in the various products.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and [...] 24934 130 39_Re: Problem in - ssnm START DB2 Command0_22_aylswop@WELLSFARGO.COM30_Thu, 6 Apr 2000 06:07:28 -0700459_iso-8859-1 We had experienced this same problem. DB2 PTF UQ20428 resolved it. The PTF is for DB2/OS390 V5.1, at the 9808 level, and points to II04773 for added information.

Patricia Aylsworth Database Systems Wells Fargo Services Corp.





-----Original Message----- From: Manish Lall [mailto:Manish.Lall@POWAIMAIL.LTITL.COM] Sent: Wednesday, March 29, 2000 21:59 To: DB2-L@RYCI.COM Subject: Problem in - ssnm START DB2 Command [...] 25065 51 25_Re: Archive Log Retention12_Sue Janowitz18_SJanowitz@NEFN.COM30_Thu, 6 Apr 2000 09:10:27 -0400460_us-ascii We keep our archive logs, as well as our DB2 system image copies, for 14 days. We do weekly pack backups. In the event that we need to do DR, we lay down the most recent Sunday packs from backups, then recover from there. 14 days is in case the most recent pack backups are bad - then we might have to go back 2 weeks. (There should be daily application image copies, but if there are problems with those, we could recover using the logs alone.) [...] 25117 59 17_Re: RCT questions0_22_Bob_Pingston@AMWAY.COM30_Thu, 6 Apr 2000 09:19:22 -0400385_us-ascii Bob,

In a former job, I experimented with the maximum threads for a RCT. My experience was based on DB2 3.1, CICS 3.3, and MVS 4.3. The maximum number of threads I could support, without dire consequences, was 40. As far as an individual transaction, I did not allow more than 10. Dead-lock and/or time-out issues will probably drive your maximum per transaction. [...] 25177 118 37_Fwd: Re: Fwd: ERwin vs. PowerDesigner13_Philip Nelson21_teamdba@ATTGLOBAL.NET30_Thu, 6 Apr 2000 13:25:55 +0100534_us-ascii Comments from our Data Modeller, who uses PowerDesigner and looked at ERwin.

Hopefully it's helpful.

Phil

==================BEGIN FORWARDED MESSAGE================== Philip,

We're up to v.6.1 32 bit .

This version supports longer names in the conceptual model . However it truncates them if generated to physical model . Similarly one can type longer names into the physical model . However they cause ddl script generation to fail . I'd expect the same with direct databse creation . [...] 25296 16 3_UDB13_Donald Tipton29_Donald.Tipton@DAS.STATE.OH.US30_Thu, 6 Apr 2000 09:34:29 -0400352_us-ascii Does anyone know of a mailing list that concentrates on UDB for UNIX and NT?

Thanks Don Tipton

================================================ 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. 25313 169 46_Re: Can we tell when a table was last updated?0_18_damcon2@US.IBM.COM30_Thu, 6 Apr 2000 09:46:18 -0400541_iso-8859-1 Along the same lines as Richard Simpson's naming, I tend to affectionately refer to it as "little DB2".

Cheers, Jay



Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 878-3525, Tie Line 8-427-3525 ----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - BMW -----------------------------------------------------



"KUHN,Ulrich" @RYCI.COM> on 04/06/2000 02:52:13 AM [...] 25483 64 15_Re: large tblsp11_Kwan, James18_James_Kwan@BMC.COM30_Thu, 6 Apr 2000 09:02:37 -0500437_ISO-8859-1 Max,

Even you have the same the xxIDs, I don't think the procedure will work. The row id length is different. Large ts has 5 bytes and non-large TS has 4 bytes.

It would be nice if IBM has an utility for this type of conversion.

James

-----Original Message----- From: Massimo Scarpa [mailto:mscarpa@CESVE.IT] Sent: Wednesday, April 05, 2000 9:50 AM To: DB2-L@RYCI.COM Subject: Re: large tblsp [...] 25548 25 20_Control Center on NT12_Mike O'Neill29_mike.oneill@CUSTOMS.TREAS.GOV30_Thu, 6 Apr 2000 09:44:09 -0400328_US-ASCII I have set up 3 DB2 subsystems via Client Configuration Assistant, and tested and bound the utilities. When I go into the control center, I do not get a full list of objects for the 'second' of multiple SSNs on the same LPAR. The first one I entered works fine, and all objects are accesible. Any thoughts/ ideas? [...] 25574 271 36_SEGMENTED TABLESPACE PRIMARY EXTENTS4_DBAS14_dbas@CWCOM.NET30_Thu, 6 Apr 2000 15:03:44 +0100734_iso-8859-1 Dear DB2-L,

BACKGROUND DB2 V4.1 MVS(OS390) V2.7 TSO V2.6 DFSMS V1.5 ISPF V4.5 DISKS 3390

DATASET LISTING SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A001 43695 Tracks Volume 1 + SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A002 43695 Tracks Volume 2 + SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A003 43695 Tracks Volume 3 + SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A004 43695 Tracks Volume 4 + SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A005 43695 Tracks Volume 5 + SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A006 43695 Tracks Volume 6 + SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A007 43695 Tracks Volume 7 + SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A008 43695 Tracks Volume 8 + SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A009 43695 Tracks Volume 9 + SLIVE.DSNDBD.DLIVE.SLIVE.I0001.A010 11395 Tracks Volume 10 [...] 25846 102 26_Re: db2 UDB control center11_Jakobs, Jef18_JJakobs@HARTIS.COM30_Thu, 6 Apr 2000 09:10:22 -0500471_iso-8859-1 For DB2 UDB v5.2 you have "db2look" to generate ddl (look in the command reference).

It's a statistics extraction tool, but it can also generate ddl.

Have not installed v6.1 yet, but I assume db2look exists in that version too :o)



Jef Jakobs

-----Original Message----- From: Jimmy Willett [mailto:Jimmy.Willett@NCMAIL.NET] Sent: Wednesday, April 05, 2000 12:24 PM To: DB2-L@RYCI.COM Subject: Re: db2 UDB control center [...] 25949 24 7_Re: UDB13_Ricardo Gomes30_ricardo.gomes@MELLO-IMPERIO.PT30_Thu, 6 Apr 2000 15:46:06 +0100521_iso-8859-1 Hi everybody Does anyone know of a mailing list that concentrates on AS400 and DB2/400? Thanks

Best Regards, Cumprimentos,

Ricardo Gomes SISTEMAS Mello - Imperio Serviços Partilhados, ACE Tel.: 217204302 Fax: 217204334 Email: ricardo.gomes@mello-imperio.pt

===============================================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. 25974 67 25_Hierarchies - sql problem19_Briggs, N. - Neil -25_N.Briggs@CANON-EUROPA.COM30_Thu, 6 Apr 2000 16:20:17 +0200835_iso-8859-1 Dear List,

I have been approached with a problem from a developer. He has asked if I can improve on the 'woefully bad' SQL that he has obtained that list parts by hierarchy. The sql he gave me is shown below:-

SELECT CD17.CODE_COMP , CD17.CODE_CONS_COMP FROM SQLUSRC.CDAM17_COMP_CONS CD17 WHERE CD17.CODE_COMP = 'GABX' UNION SELECT CD17.CODE_COMP , CD17.CODE_CONS_COMP FROM SQLUSRC.CDAM17_COMP_CONS CD17 WHERE CD17.CODE_COMP IN (SELECT CD17.CODE_CONS_COMP FROM SQLUSRC.CDAM17_COMP_CONS CD17 WHERE CD17.CODE_COMP = 'GABX' ) UNION SELECT CD17.CODE_COMP , CD17.CODE_CONS_COMP FROM SQLUSRC.CDAM17_COMP_CONS CD17 WHERE CD17.CODE_COMP IN (SELECT CD17.CODE_CONS_COMP FROM SQLUSRC.CDAM17_COMP_CONS CD17 WHERE CD17.CODE_COMP IN (SELECT CD17.CODE_CONS_COMP FROM SQLUSRC.CDAM17_COMP_CONS CD17 WHERE CD17.CODE_COMP = [...] 26042 45 7_Re: UDB11_John Wynton21_jwynton@THEMISINC.COM30_Thu, 6 Apr 2000 10:20:01 -0400555_windows-1252 Don:

Try visiting this web page which contains instructions on how to subscribe as well as a description of the group:

http://people.mn.mediaone.net/scottrmcleod/index.htm

Regards,

John Wynton Themis, Inc. Specialists in DB2, MQ and CICS Training 1-800-756-3000; 908-233-8900 (Int'l); 908-233-7401 (Fax) http://www.themisinc.com





-----Original Message----- From: Donald Tipton [mailto:Donald.Tipton@DAS.STATE.OH.US] Sent: Thursday, April 06, 2000 9:34 AM To: DB2-L@RYCI.COM Subject: UDB [...] 26088 68 51_Re: Help with migrating from VSAM to DB2, puhleeze!0_22_Rohn.Solecki@MTS.MB.CA30_Thu, 6 Apr 2000 09:19:17 -0500554_us-ascii Also under the heading of " ...is also a good oppurtunity to do things that might not normally be done..." would be cleaning up data (ie trying to avoid GIGO, "Garbage In, Garbage Out"). Have programmers and/or DBA identify questionable data for clients to verify. It gives you opportunity to eliminate obsolete/bad data. It is very time consuming but ... For example, it's been 5 years since our conversion, and I'm still finding uncompleted orders that were entered, and have hung around for various reasons, since before the conversion! [...] 26157 109 7_Re: UDB16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM30_Thu, 6 Apr 2000 10:21:57 -0400560_us-ascii



Ricardo, try this has all flovors of db2

The DB2EUG homepage: http://people.mn.mediaone.net/scottrmcleod/ Here you'll find all the scripts that have been sent to the list, as well as the instructions below.



How to:

SUBSCRIBE/UNSUBSCRIBE: Send e-mail to: db2eug-request@lists.best.com The body of the message should contain one of the keywords 'subsingle', 'subscribe' or 'unsubscribe'. You may append the e-mail address at which you wish to receive the list's messages, e.g. subscribe joe@company.com [...] 26267 21 15_Re: large tblsp14_Massimo Scarpa16_mscarpa@CESVE.IT30_Thu, 6 Apr 2000 16:25:58 +0200445_us-ascii The xxIDs are only ONE of 10.000 reasons why a similar trick will never work !!!! For instance





This is only one I personally experimented.

Regards

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 26289 17 21_REXX & DSNWLIR.......14_Massimo Scarpa16_mscarpa@CESVE.IT30_Thu, 6 Apr 2000 16:35:43 +0200378_us-ascii I tried to maintain DB2 connection with some trick, but nothing happened....

THX again Martin.



Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 26307 25 32_Sybase ODBC Driver to OS/390 DB29_Chu, Pius14_ChuP@CONED.COM30_Thu, 6 Apr 2000 10:43:06 -0400357_- This is a Sybase product called ClearConnect. It is a 2-tier ODBC connection from workstation (using TCP/IP or SNA) to OS/390 DB2. This is similar to DB2 Connect Personal Edition. I would like to know if you are using it, the pros and cons.

I know Sybase used to have Intersolv to write their ODBC drivers, I don't know if this is still true. [...] 26333 29 5_AS40013_Ricardo Gomes30_ricardo.gomes@MELLO-IMPERIO.PT30_Thu, 6 Apr 2000 16:13:31 +0100513_iso-8859-1 Thank you for your help. And AS400 specifically? Any hints on a similar discussion-list?

-----Original Message----- From: Thomas E. Faglon [mailto:tfaglon@TELCORDIA.COM] Sent: Thursday, April 06, 2000 3:22 PM To: DB2-L@RYCI.COM Subject: Re: UDB







Ricardo, try this has all flovors of db2

The DB2EUG homepage: http://people.mn.mediaone.net/scottrmcleod/ Here you'll find all the scripts that have been sent to the list, as well as the instructions below. [...] 26363 51 46_Re: Can we tell when a table was last updated?16_Michael McCarthy18_mmccarthy@DTCC.COM30_Thu, 6 Apr 2000 10:53:24 -0400310_us-ascii Table changes can be tracked by altering the table to audit changes on the OS/390 platform. If you just need to know when a particular table is updated, UDB on all platforms, including OS/390 supports triggers. You may want to create an update trigger to record the information in another table. [...] 26415 27 26_Disaster recovery planning41_=?iso-8859-1?Q?Cl=E1udio_Meluzzi_Mendes?=18_P000002@BEG.COM.BR30_Thu, 6 Apr 2000 12:00:22 -0300379_iso-8859-1 We have just started our disaster recovery plan following instructions given in the Administration Guide chapter 4-7 (DB2 OS/390 V4.1). Are these instructions enough for making such plan? I searched the IBM site and did not find any redbook about this. Are there other sources like redbooks or papers about this subject? Does anyone have any additional material? [...] 26443 33 7_Re: UDB0_22_rmadan@CYVEILLANCE.COM30_Thu, 6 Apr 2000 11:05:13 -0400574_iso-8859-1 db2eug@lists.best.com

> -----Original Message----- > From: Donald Tipton [mailto:Donald.Tipton@DAS.STATE.OH.US] > Sent: Thursday, April 06, 2000 9:34 AM > To: DB2-L@RYCI.COM > Subject: UDB > > > Does anyone know of a mailing list that concentrates on UDB > for UNIX and > NT? > > Thanks > Don Tipton > > ================================================ > 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. > [...] 26477 25 43_DB2 Connect v6 and Control Center on OS/39014_James R. Brown25_brownjr@CI.RICHMOND.VA.US30_Thu, 6 Apr 2000 11:10:33 -0400405_iso-8859-1 Hi all,

I would like to activate the control center feature of DB2 Connect v6 to administer sub-systems on OS/390. The Connect manual states that this is implemented thru stored procedures and to reference the Program Directory to get the FMID's required to support this feature. Does anyone know where in the Program Directory, or any place else, that I can find this information. [...] 26503 38 34_00E20003 abends on db2 v5 / os/3900_24_lightsey@ITS.STATE.MS.US30_Thu, 6 Apr 2000 10:22:32 -0500600_us-ascii My main user has started getting this abend on a regular basis during nightly "batch" processing:

DSNL027I ) SERVER DISTRIBUTED AGENT WITH LUWID=GA032022.B909.000406024546=5633 THREAD-INFO=TAPR297:*:TAPR297:starsbs.exe RECEIVED ABEND=04E FOR REASON=00E20003 DSNL028I ) GA032022.B909.000406024546=5633 ACCESSING DATA FOR LOCATION 10.3.32.34 IPADDR 10.3.32.34

The messages and codes manual says that this is a getmain failure - but is this a getmain failure in the DB2 task(s) or for the user's batch job ? IBMLINK seems to say that the error is for subpool 229 ( esqa ? ). [...] 26542 20 32_Subject: ERwin vs. PowerDesigner11_KATHY JONES26_JONESKS@GROUPWISE.CCSD.NET30_Thu, 6 Apr 2000 08:18:01 -0700502_US-ASCII I am in the process of evaluating data modeling software. I am comparing ERwin with Silverrun. I find the Silverrun quite robust. You might want to check it out.

Kathy Jones Database Analyst Consultant 702-799-5040 x366 jonesks@GroupWise.ccsd.net

===============================================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. 26563 105 45_Re: stored procedure error handling questions10_Cathie Jin16_yejin@US.IBM.COM30_Thu, 6 Apr 2000 11:34:06 -0400425_us-ascii Thanx Jim,

1. The stored procedure is defined to return result sets. The CLI client still complain invalid sqlstate 02000 returned. I just use 01Hxx for now.

2. The SQLERRML is 50 or 51 depend on how much information needs to be fit in SQLERRMC, including procedure name, spec name, error sqlstate. The book (SQL Application Guide 4.3) says at least 17 chars are returned for the diagnostic info/ [...] 26669 33 30_Re: Disaster recovery planning12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Thu, 6 Apr 2000 10:32:00 -0400476_- DB2 OFFSITE RECOVERY SAMPLE PROCEDURES GG24-3601-00

>>> DB2-L@RYCI.COM@inter2 04/06/00 09:59AM >>> We have just started our disaster recovery plan following instructions given in the Administration Guide chapter 4-7 (DB2 OS/390 V4.1). Are these instructions enough for making such plan? I searched the IBM site and did not find any redbook about this. Are there other sources like redbooks or papers about this subject? Does anyone have any additional material? [...] 26703 99 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Thu, 6 Apr 2000 10:37:48 -0500295_us-ascii Sanjeev - My problem is that at this current time, the object analysis of Omegamon in our shop is current broken. Our systems people need to upgrade and will do so soon. In the meantime, I am rigging up some traces to get these numbers. As soon as I get them, I will let you know. [...] 26803 44 38_Re: 00E20003 abends on db2 v5 / os/39012_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Thu, 6 Apr 2000 10:37:00 -0400485_- Have you tried increasing the region size on the STEP/JOB?

>>> DB2-L@RYCI.COM@inter2 04/06/00 10:16AM >>> My main user has started getting this abend on a regular basis during nightly "batch" processing:

DSNL027I ) SERVER DISTRIBUTED AGENT WITH LUWID=GA032022.B909.000406024546=5633 THREAD-INFO=TAPR297:*:TAPR297:starsbs.exe RECEIVED ABEND=04E FOR REASON=00E20003 DSNL028I ) GA032022.B909.000406024546=5633 ACCESSING DATA FOR LOCATION 10.3.32.34 IPADDR 10.3.32.34 [...] 26848 42 47_SQL performance with digits in the where clause8_madhavan16_madhavan@INF.COM30_Thu, 6 Apr 2000 21:23:47 +0530424_- Hi,

We have the following sql to be performed in a online program where the number of rows in the table is expected to be 1 million rows.

There will be an alternate index on the po_num column. But since % is used as the first character in the like clause we expect a index scan for this. With digits function, will this sql be a big performance overhead? Is there any alternate way of coding this sqls? [...] 26891 294 40_Re: SEGMENTED TABLESPACE PRIMARY EXTENTS0_19_Tim.Lowe@STPAUL.COM30_Thu, 6 Apr 2000 10:50:03 -0500652_us-ascii This is working as designed.

See your DB2 admin guide. In the DB2 V4 DB2 Admin Guide, Chapter 5-5, under "Improving DASD Utilization: Space and Device Utilization", "Extending DB2-Managed Data Sets", "Extending Nonpartitioned Spaces" (section 5.5.6.1.1 ): >>>>>snip>>>>> Extending Nonpartitioned Spaces: For a nonpartitioned table space or index space, DB2 defines the first piece of the page set starting with a primary allocation space, and extends that piece with secondary allocation spaces. When the end of the first piece is reached, DB2 defines a new piece (which is a new data set) and extends to that new piece starting # [...] 27186 73 38_Re: 00E20003 abends on db2 v5 / os/39014_John P Shipley24_jpshiple@DUKE-ENERGY.COM30_Thu, 6 Apr 2000 11:41:31 -0400347_us-ascii The users task. If it is TSO up the region size when logging on.

Explanation: In order to satisfy an unconditional request for storage, the get variable storage function needed to obtain storage (from GETMAIN) to expand a storage pool. However, GETMAIN indicated that sufficient storage in the private area was not available. [...] 27260 57 37_Re: DB2 SQL error handling techniques0_22_Rohn.Solecki@MTS.MB.CA30_Thu, 6 Apr 2000 10:50:40 -0500550_us-ascii In our shop we have almost a thousand 'standard' error messages (don't ask why that many), plus custom 'one use' ones hard coded as required. When I was coding, I liked to make each error message in a program unique by adding a 1 or 2 char 'uniqueness' field to the message. That way when you are looking at an error message that could be generated from more than 1 place in the code it is easy to spot the exact location the error occured. Whether this 'uniqueness' code shows on user output (screen/print), or is just written to your [...] 27318 32 12_Re: CA World9_Rob Crane22_racrane@CONCENTRIC.NET30_Thu, 6 Apr 2000 10:11:03 -0600603_us-ascii I will be working the World Resource Center for the DB2 area, stop by and say hello.

-Rob Crane

Michale Yu wrote: > > Hi Leslie: > > I will go to New Orleans this week-end, hope to meet you there. > > Yu Bin(Michale Yu) > From CHINA WUHAN > 04/06/2000 > > Leslie Pendlebury-Bowe DB2 DBA OS390 SAP asked: > Anybody else going to CA world and fancy meeting up? New Orleans next week > > ================================================ > 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 [...] 27351 85 36_Re: DB2 Capacity Planning - M-Values12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Thu, 6 Apr 2000 17:08:02 +0200473_x-user-defined Hi,

Model MSU MIPS #CPU %Perf-MSU per cpu %Perf-MIPS RA6 15 88 1 75.00 75.21 R16 20 117 1 100.00 100.00 RB6 28 165 2 70.00 70.51 R26 37 219 2 92.50 93.59 RC6 55 317 3 91.67 90.31 RD6 71 410 4 88.75 87.61

X37 22 127 1 100.00 100.00 X47 41 238 2 93.18 93.70 X57 59 345 3 89.39 98.29 X67 76 447 4 86.36 95.51 X77 93 545 5 84.55 93.16 X87 109 640 6 82.58 91.17 X97 124 713 7 80.52 87.06 XX7 136 783 8 77.27 83.65 XY7 146 846 9 73.74 80.34 XZ7 [...] 27437 275 40_Re: SEGMENTED TABLESPACE PRIMARY EXTENTS14_John P Shipley24_jpshiple@DUKE-ENERGY.COM30_Thu, 6 Apr 2000 11:33:05 -0400283_us-ascii



The only constraint I am aware of is the primary must be able to be taken on another volume in no more the 5 extents or it will fail. I do wonder why you have not paritioned your tablespace into 100+ parts. It must take a long time to reorg this thing. [...] 27713 73 38_Re: 00E20003 abends on db2 v5 / os/39022_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Thu, 6 Apr 2000 12:16:27 -0400377_- I must be missing something. Why do you think the failure is in the user's "batch" job?

The messages indicate the failing thread is attached to DB2 through DDF. Maybe the DDF address space (xxxxDIST) is getting a GETMAIN failure while processing the DDF thread. Check the DDF address space's JES2 job log for messages What is the DDF address space's region size? [...] 27787 128 45_Re: stored procedure error handling questions14_Steven Camitta30_steven.camitta@INGRAMMICRO.COM30_Thu, 6 Apr 2000 09:19:47 -0700498_iso-8859-1 Cathie, I'm not sure why you're setting the SQLSTATE within the SP in the first place. The protocol for SP result set processing is the SP simply opens the cursor, then returns. The client CLI program should get an appropriate sqlstate indicating the result set was empty.

Rgds, Steve

-----Original Message----- From: Cathie Jin [mailto:yejin@US.IBM.COM] Sent: Thursday, April 06, 2000 8:34 AM To: DB2-L@RYCI.COM Subject: Re: stored procedure error handling questions [...] 27916 82 51_Re: SQL performance with digits in the where clause17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM30_Thu, 6 Apr 2000 17:43:00 +0100488_- DIGITS(PO_NUM) LIKE '%098738' is a non-indexable stage 2 predicate, so it will not go the index at all. Since the last five digits of data in the col is always constant ie 98738 can't you use po_num > 98738 in the where clause.

Regards

Nagaraj



______________________________ Reply Separator _________________________________ Subject: SQL performance with digits in the where clause Author: madhavan (madhavan@INF.COM) at unix,mime Date: 06/04/00 16:53 [...] 27999 113 46_Re: Can we tell when a table was last updated?12_Jim Herrmann12_b3d@CUOL.NET30_Thu, 6 Apr 2000 11:49:06 -0500585_us-ascii What's wrong with "UNO"? After all Linux is a flavor of Unix. UNO also has the double meaning of "one" in Spanish. This is appropriate since all of these flavors on DB2 share one code base, whereas DB2/390 has a different code base. So, whatdayathink? DB2 UNO?

"KUHN,Ulrich" wrote:

> > > I hate this "UDB" business!!! I believe (looking at previous notes) > the 'UDB' in this case is actually the "original" UDB, i.e. UDB for > Linux, Unix, NT, OS/2, aka "LUNO", and not OS/390 (and not AS/400 > either...). But with our current ambiguous terminology it is [...] 28113 30 7_Re: UDB16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV30_Thu, 6 Apr 2000 09:55:04 -0700609_- Go to http://people.mn.mediaone.net/scottrmcleod and follow the instructions.

> -----Original Message----- > From: Donald Tipton [SMTP:Donald.Tipton@DAS.STATE.OH.US] > Sent: Thursday, April 06, 2000 6:34 AM > To: DB2-L@RYCI.COM > Subject: UDB > > Does anyone know of a mailing list that concentrates on UDB for UNIX and > NT? > > Thanks > Don Tipton > > ================================================ > 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. [...] 28144 117 40_Re: SEGMENTED TABLESPACE PRIMARY EXTENTS23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Thu, 6 Apr 2000 13:00:08 -0400578_- Hi,

Can you tell us the total number of extents on A1-A10 datasets. Also the largest free space on volume 10 (though it might be too late to get that stats??). Are you telling us that A10 didnot allocate a primary qty, but got a secondary qty space instead ?

Sanjay

> -----Original Message----- > From: DBAS [SMTP:dbas@CWCOM.NET] > Sent: Thursday, April 06, 2000 10:04 AM > To: DB2-L@RYCI.COM > Subject: SEGMENTED TABLESPACE PRIMARY EXTENTS > > Dear DB2-L, > > BACKGROUND > DB2 V4.1 > MVS(OS390) V2.7 > TSO V2.6 > DFSMS V1.5 > ISPF V4.5 > DISKS 3390 [...] 28262 246 40_Re: SEGMENTED TABLESPACE PRIMARY EXTENTS12_HEPP SHERY C17_schepp@SRPNET.COM30_Thu, 6 Apr 2000 09:57:53 -0700484_iso-8859-1 Not sure if this is the same problem- but I've encountered a situation where we have a stogroup defined with 5 volumes. If the tablespace or index requires extents and the the current volume it resides on is full instead of allocating a secondary dataset to another volume in the stogroup the process abends with unavailable resource. The DB2 log message states that it was unable to allocate extents. We are not passed 119 extents- it's just that the volume is full. [...] 28509 170 45_Re: stored procedure error handling questions10_Cathie Jin16_yejin@US.IBM.COM30_Thu, 6 Apr 2000 12:59:52 -0400488_us-ascii Steve,

When I set my PARAMETER STYLE to be DB2SQL, DB2 allocates and passes null indicator, OUT parameters SQLSTATE and SQL diagnostic string, IN parameters for the procedure name and specific name of the stored procedure to the SP.

The SQLSTATE is used to indicate errors occur in the user-defined function, might be non-DB2 related. The diagnostic info is caught by the client (JAVA jdbc exception or SQLCA.SQLERRMC after CLI call) if error SQLSTATE is set. [...] 28680 89 51_Re: SQL performance with digits in the where clause17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM30_Thu, 6 Apr 2000 18:13:11 +0100543_- In case you have not only values like 40098738 but also 40098739, 40098740 and so on.., then what i said earlier will not work obviously. Since this is an online program, do you need all qualifying rows at once, or do you need the first few before displaying the first screen, the next few before showing the second screen and so on. If that is the case you can use OPTIMIZE FOR 1 ROW clause on the SQL to supress materialization of cursor (materialization would occur because you have a group by on your SQL). Do the filtering in your [...] 28770 113 36_Re: DB2 Capacity Planning - M-Values14_Adrian Collett27_adriancollett@TISCALINET.IT30_Thu, 6 Apr 2000 19:21:16 +0200415_iso-8859-1 Isaac,

The two pints are almost yours.

Excuse my ignorance but which of these fields corresponds to the M-Value ? Could you also tell me the source ??

Thanks, Adrian.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM] On Behalf Of Isaac Yassin Sent: 06 April 2000 17:08 To: DB2-L@RYCI.COM Subject: Re: DB2 Capacity Planning - M-Values [...] 28884 134 51_Re: SQL performance with digits in the where clause12_John Cameron26_JCameron@MSI-INSURANCE.COM30_Thu, 6 Apr 2000 12:40:39 -0500521_us-ascii How about -

SELECT PAGE_NUM LINE_NUM SUM(TOT_UNITS) FROM TABLE-A WHERE PO_NUM IN (40098738, 60098738, 70098738, 80098738) GROUP BY PAGE_NUM LINE_NUM

John Cameron 651.631.7188 jcameron@msi-insurance.com





Nagaraj Pudukotai cc: Sent by: DB2 Data Subject: Re: SQL performance with digits in the where clause Base Discussion List



04/06/00 12:13 PM Please respond to DB2 Data Base Discussion List [...] 29019 305 40_Re: SEGMENTED TABLESPACE PRIMARY EXTENTS0_19_Tim.Lowe@STPAUL.COM30_Thu, 6 Apr 2000 12:44:44 -0500435_us-ascii Sherry, Since you have 5 volumes defined, you have specified which volumes to go to. The problem with this can be that the specified volumes filled before you used them. With SMS and stogroups with volume('*'), then DB2 can extend the current dataset to another volume that has space (if there is one). You could then use SMS ACS routines and separate SMS storage classes (to separate your data and indexes for example). [...] 29325 177 51_Re: SQL performance with digits in the where clause17_Nagaraj Pudukotai25_Nagaraj.Pudukotai@WDR.COM30_Thu, 6 Apr 2000 18:49:51 +0100362_us-ascii To begin with IN LIST is a stage 1 predicate. But will you code for all the possible values in the IN LIST in your program?. Well if you are sure that the pattern is nnn98738 (i.e the values in the table column are only of the type nnn98738) then you are better off with po_num > 98738 than having an IN LIST. It would be do a matching index scan. [...] 29503 220 51_Re: SQL performance with digits in the where clause12_John Cameron26_JCameron@MSI-INSURANCE.COM30_Thu, 6 Apr 2000 12:59:26 -0500344_us-ascii I guess I read :

"PO_NUM field in TABLE-A is an interger field which has values 40098738, 60098738, 70098738, 80098738."

as those were the only values that were of interest. I guess you are assuming that there are no values between 40098738 and 60098738 in your reply ?

Perhaps we all need more information ? [...] 29724 26 11_Load/Unload17_Charles Jambrosic29_Charles_Jambrosic@CSIHOME.COM30_Thu, 6 Apr 2000 11:43:41 -0700447_us-ascii We want to automate a procedure that will unload our production tables and load them into a test environment on a weekly or bi-weekly basis. The problem we are facing is that the table qualifiers are not the same in all DB2 environments. Currently we use dsntiaul to select the tables from production and then update the syspunch load sql statement to alter the qualifier with the new name of the target db2 tables prior to loading. [...] 29751 54 15_Re: Load/Unload0_22_BILL_GALLAGHER@PHL.COM30_Thu, 6 Apr 2000 15:08:36 -0400549_us-ascii How about creating views on the production tables with the test qualifiers, and using DSNTIAUL to unload from the view?

Bill Gallagher, DBA Phoenix Home Life Enfield, CT









Charles Jambrosic on 04/06/2000 02:43:41 PM

Please respond to DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: (bcc: BILL GALLAGHER/Phoenix Home Life Mutual Insurance) bcc: BILL GALLAGHER/Phoenix Home Life Mutual Insurance Subject: Load/Unload [...] 29806 64 15_Re: Load/Unload10_Fritz Rapp21_FritzRapp@T-ONLINE.DE30_Thu, 6 Apr 2000 21:15:45 +0200574_us-ascii Hi Charles, a nice possibilty is to unload the tables with UNLOAD+ by BMC, because in the select clause you have the opportunity to use a statement called ' into table qualifier.tablename'. By the the way you'll probably surprised who fast tables can be unloaded and you can load the tables with the IBM LOAD. So you could write a REXX?-procedure that reads the necessary information out of the catalogue and in a nice way produce jobs(with ISPF skeletons....). So you would'nt have to care if a new table comes along the way. Hope this can give you some kind [...] 29871 64 15_Re: Load/Unload23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Thu, 6 Apr 2000 15:28:08 -0400568_- Charles,

I once worked at a client site where we had a set of similar unload and load jobs. To work around the problem of different qualifier, we maintained separate PDS which had load cards for their respective environment. So the load job pointed to TEST PDS which had right load control card. Any time changes were made to table structure, new load cards were created and migrated to respective control card library, as part of the migration procedure. This was less troublesome than changing qualifier in load control card every time data was moved. [...] 29936 56 30_Re: Disaster recovery planning0_20_Carl_Nelson@AICI.COM30_Thu, 6 Apr 2000 14:24:04 -0500770_us-ascii I could not find this book on the IBM site. Is this an IBM book?

DB2 OFFSITE RECOVERY SAMPLE PROCEDURES GG24-3601-00









BOB JEANDRON on 04/06/2000 10:32:00 AM

To: DB2-L@RYCI.COM cc: (bcc: Carl Nelson/IN002/AICI/US)

Subject: Re: Disaster recovery planning

DB2 OFFSITE RECOVERY SAMPLE PROCEDURES GG24-3601-00

>>> DB2-L@RYCI.COM@inter2 04/06/00 09:59AM >>> We have just started our disaster recovery plan following instructions given in the Administration Guide chapter 4-7 (DB2 OS/390 V4.1). Are these instructions enough for making such plan? I searched the IBM site and did not find any redbook about this. Are there other sources like redbooks or papers about [...] 29993 118 13_Puzzling -80313_Helen Johnson25_Helen_Johnson@RAC.RAY.COM30_Thu, 6 Apr 2000 14:23:44 -0500291_us-ascii I have been battle a -803 all day and have no idea of where to look next. Any help would be appreciated.

DB2 v5.1 IMS/DC Table has 2 indexes: INDEX CLUS DUP COL COL ASC DATA NAME INDX UNQ SEQ COLUMN NAME NO DES TYPE -------- ---- --- --- ------------------ --- --- ---- [...] 30112 48 15_Re: Load/Unload0_20_Carl_Nelson@AICI.COM30_Thu, 6 Apr 2000 14:25:18 -0500714_us-ascii We use BMC Change Manager to do that.









Charles Jambrosic on 04/06/2000 11:43:41 AM

To: DB2-L@RYCI.COM cc: (bcc: Carl Nelson/IN002/AICI/US)

Subject: Load/Unload





We want to automate a procedure that will unload our production tables and load them into a test environment on a weekly or bi-weekly basis. The problem we are facing is that the table qualifiers are not the same in all DB2 environments. Currently we use dsntiaul to select the tables from production and then update the syspunch load sql statement to alter the qualifier with the new name of the target db2 tables prior to loading. [...] 30161 88 15_Re: Load/Unload0_22_Rohn.Solecki@MTS.MB.CA30_Thu, 6 Apr 2000 14:22:45 -0500530_us-ascii We have created a process where developers can do a mass backup, or restore from other db backups, without having to do many JCL changes. One of the keys to the process is that we have a set of table control cards that allow JCL substitution for the DB name, ie After generating the table load card we replace the generated header with this generic one, then enter the appropriate INDDN and TBname, leaving the DBname generic. The generic dbname is replaced using JCL parameter substitution later on in the process. [...] 30250 80 15_Re: Load/Unload12_HEPP SHERY C17_schepp@SRPNET.COM30_Thu, 6 Apr 2000 12:26:05 -0700637_ISO-8859-1 Charles- you have a couple of other options here depending whether you want to use 3rd party tools or not.

CA-Platinum has a product called RC-migrator which lets you do global changes- Thus you could create your worklist to unload production data then load/image copy/runstat your test tables/database. Another option available without using 3rd party tools would be to use dsn1copy using the production image copies to lay the data down to the test databases. This solution would require that the structure definitions are the same (you would have to change your obids whenever objects change) and that you want [...] 30331 94 17_Re: Puzzling -80312_HEPP SHERY C17_schepp@SRPNET.COM30_Thu, 6 Apr 2000 12:31:41 -0700586_iso-8859-1 Not knowing how the program is determining to insert the data it almost appears that the program could be trying to insert duplicate data due to differences within the two environments. One option may be to copy production to test and then test the program using a debugging tool. You could then tell if the program is accessing a different logic path due to a data difference. Another option may be to recreate production clustering index as non-unique- let the program run and then query the table for the duplicate information. Not sure if any of these are an option [...] 30426 105 38_Re: 00E20003 abends on db2 v5 / os/3900_24_lightsey@ITS.STATE.MS.US30_Thu, 6 Apr 2000 14:31:31 -0500380_us-ascii As Mr. P noted and I neglected to mention earlier, this is a distributed thread. It comes from "batch services" on 4 NT servers, the ddf address space has region=0k as recommended in apar ii04309. There are no messages in the jes2 log other than racf responses to users who can't seem to remember their passwords. This is db2 v5 at put level 0001 2000. Thanks again [...] 30532 51 15_Re: Load/Unload19_Saunders, William C27_William_Saunders@CGUUSA.COM30_Thu, 6 Apr 2000 15:36:18 -0400554_- We do this as well between Production and a "decision support" subsystem. The qualifier change is done in the load job using a PDSMAN batch function, but it could be easily automated with a REXX exec.

To make this process more bulletproof, you might want to eliminate timeouts against production updates. I did this by binding DSNTIAUL into a new plan with ISOLATION(UR) instead of CS and used this plan for the unloads. I chose sleeping through the night over having the data accurate to the moment (since the target subsystem has no RI). [...] 30584 92 15_Re: Load/Unload12_John Cameron26_JCameron@MSI-INSURANCE.COM30_Thu, 6 Apr 2000 14:46:25 -0500548_us-ascii How about using the test qualifier in production and creating a synonym ?

John Cameron 651.631.7188 jcameron@msi-insurance.com





BILL_GALLAGHE R@PHL.COM To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Re: Load/Unload Discussion List



04/06/00 02:08 PM Please respond to DB2 Data Base Discussion List











How about creating views on the production tables with the test qualifiers, and using DSNTIAUL to unload from the view? [...] 30677 63 17_Re: Puzzling -80313_Lynne Flatley17_LFlatley@NEFN.COM30_Thu, 6 Apr 2000 15:51:58 -0400578_us-ascii Run the CHECK utility against the indices to ensure that they are in sync with the data. Unfortunately, I have seen people get strange results when a utility got termed and the recover pending flags turned off and so the index and tablespace were out of sync.

> -----Original Message----- > From: Helen Johnson [SMTP:Helen_Johnson@RAC.RAY.COM] > Sent: Thursday, April 06, 2000 3:24 PM > To: DB2-L@RYCI.COM > Subject: Puzzling -803 > > I have been battle a -803 all day and have no idea of where to look next. > Any help would be appreciated. > > DB2 v5.1 > [...] 30741 74 15_Re: Load/Unload11_Kwan, James18_James_Kwan@BMC.COM30_Thu, 6 Apr 2000 14:54:14 -0500474_ISO-8859-1 Hi Charles,

If you are looking for a third party tools solution, you can get a tool like BMC Change Manager with or without Unload Plus/Load Plus. This will help you to solve different naming problem. Alternatively if unload or loading time is an issue, you can get Unload Plus and Load Plus. Apart from what Fritz has told you, it can also unload data from image copy at your develop machine. This will minimize the impact to your production system. [...] 30816 70 17_Re: Puzzling -80311_White, Mary28_Mary_White@FDLES.STATE.FL.US30_Thu, 6 Apr 2000 16:05:11 -0400409_iso-8859-1 I know this sounds silly, but could someone have left hard coded test data in the program? We had the same problem before, and the programmer just missed removing a small test reference.

Good luck.

Mary White

-----Original Message----- From: Helen Johnson [mailto:Helen_Johnson@RAC.RAY.COM] Sent: Thursday, April 06, 2000 3:24 PM To: DB2-L@RYCI.COM Subject: Puzzling -803 [...] 30887 12 17_Re: Puzzling -80313_Helen Johnson25_Helen_Johnson@RAC.RAY.COM30_Thu, 6 Apr 2000 14:58:42 -0500303_- I ran the CHECK utility and everything was ok.

Helen

================================================ 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. 30900 34 4_LOAD13_Wilson, Randy22_Randy_Wilson@BCBST.COM30_Thu, 6 Apr 2000 16:13:02 -0400491_- Hiya folks, I'm new to UDB in the AIX world , as is our company... we're just getting started with the product and are impressed with the possibilities we see with it. We just installed UDB/AIX EEE for AIX about 3 weeks ago and I'm working to develop some tests to begin exploring the possibilities of this platform.... I'm having a problem using the LOAD command, I'm trying to load a non-delimited ASCII file ... just a flat file with fixed length records and I'm getting an error: [...] 30935 19 39_WLM Stored Procedures and non-swappable11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Thu, 6 Apr 2000 16:46:57 -0400623_us-ascii 1) We have converted all our in-house stored procedures to WLM. I notice the address space for WLM stored procedures is swappable, while our prior non-WLM address space was nonswappable. Should I ask the MVS systems programmer to make this change?

2) Our next tuning effort will be to make all the stored procedures reentrant and reusable, and to mark them with STAY RESIDENT = Y. How can I check before and after status of the number of program loads I am incurring? Joe Luthman DBA Information Technology Services Phone: (419)372-7750 260 Hayes Hall FAX: (419)372-7723 Bowling Green State University [...] 30955 154 38_Re: 00E20003 abends on db2 v5 / os/39022_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Thu, 6 Apr 2000 16:45:30 -0400431_iso-8859-1 Region=0k means that an address space will request all the private storage up to the maximum. available. If the maximum is 5MB, then that's what the address space gets (although it might not be enough!).

I'm not sure if the region=0k includes below the 16MB line private storage and above the 16MB line private storage. Your OS/390 sysprog can tell you the maximum below/above the 16MB line private storage. [...] 31110 42 26_How do you guys prototype?14_Greg DiGiorgio29_gdigior@CI.NEWPORT-NEWS.VA.US30_Thu, 6 Apr 2000 16:49:51 -0400612_us-ascii Dear SAG-L'ers and DB2-L'ers,

I have cross posted this to 2 listservers in hopes of covering all of our bases...

We are mainframe-based using SAGA's Natural (3.5GL) with DB2 as the RDBMS. We have SAGA's Lightstorm V3.2 for Windows, though no one has ever learned how to use it.

FYI, Natural is a green screen app development environment with a code generator (Construct) and a data dicitionary (Predict). You can't use the code generator and map painter without having file and field definitions in the data dictionary. If you go that far, then you have designed the database. [...] 31153 43 13_optimizerrrrr23_Trivedi, Kaushal J, BGM17_kjtrivedi@ATT.COM30_Thu, 6 Apr 2000 16:50:35 -0400522_iso-8859-1 Hi all, I have a question about the 'OPTIMIZE FOR N ROW' option, I would like somone to throw some light on whats going on here, I have this query,

select x,y,z, from tables a,b,c where ...... order by x

Now I did an explain on it and it showed me 4 steps with the last step being a sort apparently for the ORDER BY (SORTC_ORDERBY WAS 'Y') , This query is a cursor thats run for a number of predicate values and most of the time the result set is just 1 row, except for some values where its [...] 31197 187 38_Re: 00E20003 abends on db2 v5 / os/39022_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Thu, 6 Apr 2000 17:01:50 -0400683_iso-8859-1 Check all the DB2 address spaces for storage failures.

Another possibility is that the IRLM is running out of private storage or does an IRLM private storage failure generate a different error message.

Maybe the error message depends on the type of storage; private, CSA, SQA etc...



Mike Piontkowski Voice/Fax: 302.886.4612 mailto:michael.piontkowski@astrazeneca.com

> ---------- > From: Piontkowski Michael ML > Sent: Thursday, April 06, 2000 4:45 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] 00E20003 abends on db2 v5 / os/390 > > Region=0k means that an address space will > request all the private storage up to the maximum. [...] 31385 63 30_Re: Disaster recovery planning12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Thu, 6 Apr 2000 16:06:00 -0400408_- It is an old redbook which may have been replaced by a new version. I doubled checked the title and #. They are correct.

>>> DB2-L@RYCI.COM@inter2 04/06/00 02:22PM >>> I could not find this book on the IBM site. Is this an IBM book?

DB2 OFFSITE RECOVERY SAMPLE PROCEDURES GG24-3601-00









BOB JEANDRON on 04/06/2000 10:32:00 AM [...] 31449 95 46_Re: Can we tell when a table was last updated?10_Leo Flores23_leoflores@EARTHLINK.NET30_Thu, 6 Apr 2000 14:56:21 -0700697_us-ascii OOP's. I thought this was to easy a DB2 question to answer. But, I mistook UPDATE for ALTERED. Thanks for straightening me out Greg.

Leo Flores



Greg.Palgrave@BANKWEST.COM.AU wrote:

> Leo, > > This only tells you when a table was ALTERed (add column etc.) , not when > it was updated (DML Insert, Delete, or Update). > > Cheers, > Greg Palgrave > Database Administration > IT Services > Bank of Western Australia > eMail : greg.palgrave@bankwest.com.au > > ---------------------------------------- Message History > ---------------------------------------- > > From: Leo Flores on 06/04/2000 14:24 > > Please respond to DB2 Data [...] 31545 14 15_Re: Load/Unload17_Charles Jambrosic29_Charles_Jambrosic@CSIHOME.COM30_Thu, 6 Apr 2000 15:03:21 -0700317_us-ascii Thanks to everyone who responded with their ideas.

Charles.

================================================ 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. 31560 53 62_DB2CONNECT : RQRIOBLK EXCEEDS MAXIMUM SIZE SUPPORTED BY SERVER18_Padmanabham, Paddy27_PPadmanabham@IDAHOPOWER.COM30_Thu, 6 Apr 2000 15:35:20 -0600400_iso-8859-1 Hi All,

We are running DB2 CONNECT EE V6.1 FIXPACK 2 on WINDOWS NT and DB2 V6.1 on OS/390 as TEST DB2 and DB2V5.1 on OS/390 as PRODUCTION DB2. We have both WINDOWS NT & 95 clients. We use THIN CLIENT architecture for CAE. We use TCPIP from client to DB2 CONNECT SERVER and TCPIP to OS/390 TEST DB2. And TCPIP from client to DB2 CONNECT SERVER and SNA to OS/390 PRODUCTION DB2. [...] 31614 20 12_Re: DB2 ODBC12_Jim Harrison12_jimh@QIS.NET30_Thu, 6 Apr 2000 18:53:32 -0400370_us-ascii As far as I know, V5 works in these scenarios as well. At least my Quick Beginnings book says so.

>Allowed scenarios I know of: >1) DB2 Connect Personal Edition <-> DB2 for OS/390 v6 >2) DB2 Connect Personal Edition <-> DB2 Connect Server(See note below) <-> DB2 for OS/390 v6 >3) DB2 CAE <-> Db2 Connect Server(see not below) <-> DB2 for OS/390 v6 [...] 31635 26 36_Re: Sybase ODBC Driver to OS/390 DB212_Jim Harrison12_jimh@QIS.NET30_Thu, 6 Apr 2000 18:56:24 -0400362_us-ascii Big political bruhaha going on over Sybase versus DB2 Connect in our shop. I've never heard of ClearConnect but we have Sybase's Omni middleware and have experienced difficulties w/ compatability. Currently having problems with stored procedures (don't return data) and MS's MTS, although DB2 Connect is just getting up to speed on that one w/ V6. [...] 31662 33 37_Re: DB2 SQL error handling techniques12_Jim Harrison12_jimh@QIS.NET30_Thu, 6 Apr 2000 19:00:55 -0400429_us-ascii We put together a common module to call in the event of an SQL error. I've been meaning to post it to the document list but haven't had a chance. If you are interested I'll try and get it out there either Friday or sometime next week. We also have a DB2 standards site on the web if you are interested in that type of thing. It's due for an overhaul next week and if you want, I'll post the URL after it's updated. [...] 31696 85 66_Re: DB2CONNECT : RQRIOBLK EXCEEDS MAXIMUM SIZE SUPPORTED BY SERVER22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Thu, 6 Apr 2000 19:50:28 -0400346_- Paddy -

At the risk of asking a rookie question & since the RQRIOBLK has to do with the clients, does CAE on the clients have to be upgraded since DB2 CONNECT EE was upgraded?

Don't know much about DB2 CONNECT EE's tracing facilities but is it possible to identify what client machine is requesting an invalid RQRIOBLK size? [...] 31782 39 7_Re: UDB11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM30_Fri, 7 Apr 2000 07:54:47 +0800696_us-ascii Hi Donald, Try db2eug@lists.best.com









Donald Tipton on 06/04/2000 09:34:29 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: ROLAND CHUA CHONG KEE/DATACEN/IT/SGX) Subject: UDB







Does anyone know of a mailing list that concentrates on UDB for UNIX and NT?

Thanks Don Tipton

================================================ 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. [...] 31822 122 66_Re: DB2CONNECT : RQRIOBLK EXCEEDS MAXIMUM SIZE SUPPORTED BY SERVER22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Thu, 6 Apr 2000 20:08:15 -0400323_- On 2nd thought, My question about upgrading CAE is a little unclear. What I meant is - does the CAE version/fixpack on the clients support the DB2 CONNECT EE version/fixpack? The question was was probably researched as part of upgrading DB2 CONNECT EE but I can't help but ask. Sometimes the obvious are overlooked. [...] 31945 35 12_Re: DB2 ODBC15_Richard Simpson25_rsimpson@MACQUARIE.COM.AU30_Fri, 7 Apr 2000 10:43:59 +1000624_iso-8859-1 Yes, v5 works as well, both Connect v5 and DB2.

Regards

Richard

-----Original Message----- From: Jim Harrison [mailto:jimh@QIS.NET] Sent: Friday, 7 April 2000 8:54 AM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DB2 ODBC



As far as I know, V5 works in these scenarios as well. At least my Quick Beginnings book says so.

>Allowed scenarios I know of: >1) DB2 Connect Personal Edition <-> DB2 for OS/390 v6 >2) DB2 Connect Personal Edition <-> DB2 Connect Server(See note below) <-> DB2 for OS/390 v6 >3) DB2 CAE <-> Db2 Connect Server(see not below) <-> DB2 for OS/390 v6 [...] 31981 64 47_SQL performance with digits in the where clause14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Fri, 7 Apr 2000 10:46:41 +1000395_us-ascii madhavan,

Yes it will perform badly because expressions like this require every row to be passed to stage 2 before most are rejected. You could try an IN list with 100 entries in it and compare the performance.

The moral is: "Don't design a database with 2 fields stored in the same column if there is any possibility at all of processing on just one of the fields". [...] 32046 83 25_Hierarchies - sql problem14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Fri, 7 Apr 2000 10:47:00 +1000729_us-ascii What are indexes and clusterratios for table? Access Path? Then I'll attempt something.

From: Michael Hannan

>From: "Briggs, N. - Neil -" >Subject: Hierarchies - sql problem >To: DB2-L@RYCI.COM > >Dear List, > >I have been approached with a problem from a developer. He has asked if I >can improve on the 'woefully bad' SQL that he has obtained that list parts >by hierarchy. The sql he gave me is shown below:- > >SELECT CD17.CODE_COMP >, CD17.CODE_CONS_COMP >FROM SQLUSRC.CDAM17_COMP_CONS CD17 >WHERE CD17.CODE_COMP = 'GABX' >UNION >SELECT CD17.CODE_COMP >, CD17.CODE_CONS_COMP >FROM SQLUSRC.CDAM17_COMP_CONS CD17 >WHERE CD17.CODE_COMP IN > (SELECT CD17.CODE_CONS_COMP > [...] 32130 62 13_optimizerrrrr14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Fri, 7 Apr 2000 10:47:35 +1000633_us-ascii No risk of not receiving in Order of ORDER BY.

Optimize For N Rows changed the access path and therefore changed the performance. I can't give a detailed answer for a question lacking the SQLs and Explains at the very least (Relevant Catlg Stats sometimes needed also).

From: Michael Hannan

>From: "Trivedi, Kaushal J, BGM" >Subject: optimizerrrrr >To: DB2-L@RYCI.COM > >Hi all, >I have a question about the 'OPTIMIZE FOR N ROW' option, I would like somone >to throw some light on whats going on here, I have this query, > >select x,y,z, >from tables a,b,c >where ...... >order [...] 32193 68 18_Re: REXX & DSNWLIR14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 7 Apr 2000 10:54:06 +1000457_iso-8859-1 Try LINKPGMing to DSNWLI2. (The REXX language support describes the use of DSNWLIR because it uses the RRSAF interface, but you're using the CAF interface.)

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: Massimo Scarpa [mailto:mscarpa@CESVE.IT] Sent: Thursday, April 06, 2000 6:44 PM To: DB2-L@RYCI.COM Subject: REXX & DSNWLIR [...] 32262 75 17_Re: optimizerrrrr14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 7 Apr 2000 11:22:35 +1000336_iso-8859-1 Kaushal

If you specify ORDER BY, the rows will always be returned in the order specified. OPTIMIZE FOR n ROWS gives DB2 information so that it can choose an access path optimised for retrieving n rows, but even if there are more than n rows selected they will all be retrieved and presented in the desired order. [...] 32338 78 36_Re: DB2 Capacity Planning - M-Values0_20_gabriele@NZ1.IBM.COM30_Fri, 7 Apr 2000 13:05:53 +1200489_us-ascii Adrian,

M-Value for 9672-Y86 is 39513

the full record for the model: Model 9672-Y86 #CPs 8 M-Value 39513 MIPS 926 MIPS per CP 115.8 DASD I/O rate 3698

The source is IBM internal only.

Kind regards Gabriele Gambassi ------------------------------------------------------- IBM Global Service DB2 Specialist Strategic Outsourcing - New Zealand

Phone: (+64 9) 359 8875 Fax: (+64 9) 359 8833 Mobile: (+64) 021 305 100 Mail: gabriele@nz1.ibm.com [...] 32417 37 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 6 Apr 2000 21:52:32 -0400519_ISO-8859-1 Larry, If you are runing this stuff in it's owm buffer pool, you don't need abject analysis. Just look at detailed pool statistics.

Regards, Joel





Message text written by DB2 Data Base Discussion List > My problem is that at this current time, the object analysis of Omegamon in our shop is current broken. Our systems people need to upgrade and will do so soon. In the meantime, I am rigging up some traces to get these numbers. As soon as I get them, I will let you know. [...] 32455 39 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 7 Apr 2000 12:12:17 +1000442_iso-8859-1 "... you don't need abject analysis. " Although it sometimes feels that way.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: Joel Goldstein [mailto:joel_goldstein@COMPUSERVE.COM] Sent: Friday, April 07, 2000 11:53 AM To: DB2-L@RYCI.COM Subject: Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH " [...] 32495 43 36_Re: Sybase ODBC Driver to OS/390 DB29_SrinivasG17_SRINIVASG@INF.COM30_Fri, 7 Apr 2000 08:09:53 +0530561_iso-8859-1 Hi, Is this product available for download? If then please let me know the url.

Thanks & Regards Srinivas G

-----Original Message----- From: Chu, Pius [mailto:ChuP@CONED.COM] Sent: Thursday, April 06, 2000 8:13 PM To: DB2-L@RYCI.COM Subject: Sybase ODBC Driver to OS/390 DB2



This is a Sybase product called ClearConnect. It is a 2-tier ODBC connection from workstation (using TCP/IP or SNA) to OS/390 DB2. This is similar to DB2 Connect Personal Edition. I would like to know if you are using it, the pros and cons. [...] 32539 82 17_Re: optimizerrrrr10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Fri, 7 Apr 2000 03:25:20 GMT483_us-ascii Hi Kaushal, The only thing can be suggested without explain, catalog and index stats is that something is happening without optimize for 1 row which is destroying the order of data and DB2 has to do physical sorting.One possibility might be list prefetch which always destroys the order of data and requires explicit sort(if order by is mentioned). I think you can solve it by looking at explain output.If not please pass on each and every details with complete query. [...] 32622 281 19_Re: Update question14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Thu, 6 Apr 2000 22:31:17 -0400699_iso-8859-1 This has been added to V6 for OS/390, recently. See: http://www-4.ibm.com/software/data/db2/os390/v6apar.html

Regards, Richard +===+===+===+===+====+ Richard_Yevich@attglobal.net "DB2 Performance Journal", Quarterly Technical Journal www.YLAssoc.com www.DB2-Portal.com

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Trivedi, Kaushal J, BGM > Sent: Wednesday, April 05, 2000 2:24 PM > To: DB2-L@RYCI.COM > Subject: Update question > > > Hi All, > > I would like to know if this is possible. > > UPDATE TABLE_A > SET COLX = COLY (FROM TABLE B) > FOR A MATCH OF COL1, COL2 AND COL3 ON TABLEA AND TABLEB. > > Thank [...] 32904 45 15_Re: large tblsp4_IBM116_IBM1@UBIL.COM.TR30_Fri, 7 Apr 2000 09:32:14 +0300675_- I must have missed something Max...

what on earth are you talking about, please?

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it



> -----Original Message----- > From: Massimo Scarpa [SMTP:mscarpa@CESVE.IT] > Sent: Thursday, April 06, 2000 5:26 PM > To: DB2-L@RYCI.COM > Subject: Re: large tblsp > > The xxIDs are only ONE of 10.000 reasons why a similar trick will never > work > !!!! For instance > > > > This is only one I personally experimented. > > Regards > > Max Scarpa > > ================================================ > To change your subscription options or to cancel [...] 32950 20 39_Program Directory for CLI,ODBC and JDBC9_SrinivasG17_SRINIVASG@INF.COM30_Fri, 7 Apr 2000 12:04:45 +0530288_iso-8859-1 Hi, Can any one provide me with info on where I can find Program Directory for the CLI, ODBC and JDBC installation Tapes for DB2 V6 on OS/390 ? I have searched the net extensively but have been unsucessful so far. I have however done a CBPDO installation of the Basic DB2 [...] 32971 113 36_Re: DB2 Capacity Planning - M-Values4_IBM116_IBM1@UBIL.COM.TR30_Fri, 7 Apr 2000 09:38:32 +0300595_- as well as your two pints,

a big hand for Isaac...

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it



> -----Original Message----- > From: Isaac Yassin [SMTP:yassin@NETVISION.NET.IL] > Sent: Thursday, April 06, 2000 6:08 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2 Capacity Planning - M-Values > > Hi, > > Model MSU MIPS #CPU %Perf-MSU per cpu %Perf-MIPS > RA6 15 88 1 75.00 75.21 > R16 20 117 1 100.00 100.00 > RB6 28 165 2 70.00 70.51 > R26 37 219 2 92.50 93.59 > RC6 55 317 3 91.67 90.31 > RD6 71 410 [...] 33085 35 17_Re: Puzzling -80314_Massimo Scarpa16_mscarpa@CESVE.IT30_Fri, 7 Apr 2000 08:44:33 +0200343_us-ascii I experimented this problems 1 year ago.

The programmer said that all keys were unique, but there was a duplicate key. After a check index we discovered that were were really two equal keys, due to the fact that the key was too short, so you obtain two equal keys (It may be rare, but Murphy's laws rule all the universe) [...] 33121 17 34_00E20003 abends on db2 v5 / os/39028_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM28_Thu, 6 Apr 2000 23:49:50 PDT417_- Bruce,

This reason code indicates a shortage of below-the-line storage in a local storage pool, in a DB2 address space.

Dan DB2 Development dcourter@us.ibm.com

================================================ 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. 33139 178 26_Naming conventions for DB20_23_J.P.Slot@RF.RABOBANK.NL30_Fri, 7 Apr 2000 08:51:57 +0100363_us-ascii Ulrich,

As we probably all know OS/390 is another cookie compared to other platforms DB2 is available on. At the moment the phrase 'DB2 for OS/390' & 'DB2 UDB for OS/390' is the same thing. Other platforms are regarded as 'DB2 UDB for UNO' (Unix(being LINUX a variant of UNIX), NT and OS/2). Your phrase 'DB2 UDB for LUNO' is a nice one too. [...] 33318 49 47_Re: DB2 Connect v6 and Control Center on OS/39012_MILLS,Robert26_robert.mills@DEWRSB.GOV.AU30_Fri, 7 Apr 2000 17:05:25 +1000598_iso-8859-1 James,

Check out the following links in the news section of the DB2 for OS/390 Page:

Now available: DB2 Control Center support for DB2 for OS/390 - http://www-4.ibm.com/software/data/db2/os390/cc390/

Announcing: Refresh of IBM DB2 for OS/390 Version 5 Delivers DB2 Control Center and New Functions - http://www2.ibmlink.ibm.com/cgi-bin/master?xh=Byw48JkDfJSNBx0USenGnN9332&req uest=announcements&parms=U%5f299%2d252&xfr=N#@2h@004@



They contain quite a bit of information (including the FMID's) about using the Control Centre with DB2 for OS/390. [...] 33368 583 42_Little DB2, DB2 UNO, DB2 for Elsewhere ...11_KUHN,Ulrich37_ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU30_Fri, 7 Apr 2000 17:14:47 +1000451_iso-8859-1 I'm happy with any of the suggestions, although "DB2 for Elsewhere" is a bit on the lengthy side for my taste. Is there any sort of consensus out there? Preferences? Does IBM have an opinion??

Cheers, Ulrich

P.S. In case it is not clear from the attached notes: we are trying to come up with a concise unambiguous term for DB2 UDB for Unix, NT, etc, so we don't keep confusing each other by talking simply about "UDB"... [...] 33952 223 46_Re: Little DB2, DB2 UNO, DB2 for Elsewhere ...4_IBM116_IBM1@UBIL.COM.TR30_Fri, 7 Apr 2000 10:23:41 +0300679_ISO-8859-9 maybe (actually, definitely) this is not original, but would this be too stupid:

MF (as in mainframe) DB2 PC DB2 (I do not mean to say that Unix is a pc...)

?????

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it



> -----Original Message----- > From: KUHN,Ulrich [SMTP:ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU] > Sent: Friday, April 07, 2000 10:15 AM > To: DB2-L@RYCI.COM > Subject: Little DB2, DB2 UNO, DB2 for Elsewhere ... > > I'm happy with any of the suggestions, although "DB2 for Elsewhere" is a > bit on the lengthy side for my taste. Is there any sort of consensus [...] 34176 14 43_Re: Program Directory for CLI,ODBC and JDBC12_henri BOTBOL25_hbotbol@HACHETTE-LIVRE.FR30_Fri, 7 Apr 2000 02:49:12 -0500388_- hi i have installed DB2 6 1 0 last year and the program directory was shiped with db2 in paper format. doc number is : GI10-8182-00 henri botbol

================================================ 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. 34191 20 43_DB2 Connect v6 and Control Center on OS/3900_29_Joop.Ammerlaan@NL.ABNAMRO.COM30_Fri, 7 Apr 2000 10:03:52 +0200580_us-ascii James,

The product"DB2 Connect v6 and Control Center on OS/390" needs the "390 enablement" installed on OS/390 (fmid JBB 5/6 D). Installing this FMId and performing the postinstallation steps will add the required stored procedures to the system.



regars, Joop Ammerlaan - ABN Amro bank N.V., The Netherlands

================================================ 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. 34212 38 36_FW: Sybase ODBC Driver to OS/390 DB214_Richard Bourke25_Richard.Bourke@MERANT.COM30_Fri, 7 Apr 2000 10:12:21 +0200401_- Pius, ClearConnect is a sybase product, not OEMed from Merant (the company formerly known as Intersolv). Sybase OEM the odbc driver to Sybase Adaptive Server Enterprise from us though, as they have for several years. Our offering, alternative to ClearConnect, would be either SequeLink odbc/java edition (using it's own server task), or ConnectPremium for DB2 (going via DRDA). Richard, Merant [...] 34251 157 51_Re: SQL performance with digits in the where clause0_18_mebert@AMADEUS.NET30_Fri, 7 Apr 2000 10:27:49 +0100472_us-ascii This should give the best performance, if your DB2 has triggers (V6 or LUNO) (otherwise you change the application):

1. Add a column PO_MOD as integer. 2. Add INSERT/UPDATE (if PO_NUM is updateable) Trigger which sets PO_MOD to PO_NUM modulo 1.000.000: PO_MOD=PO_NUM-1000000*(PO_NUM/1000000) (I don't know whether V6 or LUNO have the MOD operator) 3. Put a Duplicate Index on PO_MOD 4. change your SQL to ....WHERE PO_MOD=098738 [...] 34409 51 38_Re: 00E20003 abends on db2 v5 / os/39011_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Fri, 7 Apr 2000 04:48:50 -0400345_ISO-8859-1 Bruce

This is a Getmain failure inside a DB2 address space, almost certainly the DBM1 address space. If you are using Local Dynamic Statement Caching (bind parameter KEEPDYNAMIC(YES)), then reduce the value of the DSNZPARM MAXKEEPD. Otherwise look at reducing the size of one or more Virtual Buffer Pools or the EDM Pool. [...] 34461 213 46_Re: Little DB2, DB2 UNO, DB2 for Elsewhere ...20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM30_Fri, 7 Apr 2000 09:55:29 +0100465_iso-8859-1 I would like to offer "DB2 Lite" :-)

-----Original Message----- From: KUHN,Ulrich [mailto:ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU] Sent: 07 April 2000 08:15 To: DB2-L@RYCI.COM Subject: Little DB2, DB2 UNO, DB2 for Elsewhere ...





I'm happy with any of the suggestions, although "DB2 for Elsewhere" is a bit on the lengthy side for my taste. Is there any sort of consensus out there? Preferences? Does IBM have an opinion?? [...] 34675 18 46_Re: Little DB2, DB2 UNO, DB2 for Elsewhere ...14_Clayton, Colin21_Colin_Clayton@BMC.COM30_Fri, 7 Apr 2000 03:59:06 -0500360_ISO-8859-1 How about db2 as opposed to DB2 ? or DB2/MM (Mickey Mouse)

Colin

[Personal opinion only etc.]

================================================ 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. 34694 127 26_Re: db2 UDB control center13_Philip Nelson21_teamdba@ATTGLOBAL.NET30_Fri, 7 Apr 2000 09:12:57 +0100380_us-ascii A couple of things -

1) Be careful of "db2look" as a DDL generator. We do this as well, but when we reported that it didn't generate certain things properly we were told it wasn't intended for this but for generating stats distribution for another server. We did get them to agree to fix what we found, but it still has some way to go. BTW it is still in V6. [...] 34822 35 32_Re: REXX & DSNWLIR - It works !!14_Massimo Scarpa16_mscarpa@CESVE.IT30_Fri, 7 Apr 2000 11:23:51 +0200308_us-ascii Hi James,

IT WORKS !!!

I read many times that DSNWLI2 has an entry point in CAF (so creating a connection), but,

due to the lack of experience in using IFI, I never tried to use this pgm. Now I can see all

IFCA fields, return area fields and I can easily read them. [...] 34858 239 46_Re: Little DB2, DB2 UNO, DB2 for Elsewhere ...0_19_mike.holmans@BT.COM30_Fri, 7 Apr 2000 10:35:23 +0100684_iso-8859-1 Given that DB2 for Loonies has arguably got more functionality than DB2 for Big Iron, that would seem slightly inappropriate.

Mike Holmans BT ISE Technical Design mike.holmans@bt.com

> -----Original Message----- > From: Beighton-Dykes, Hugh [SMTP:hugh.beighton-dykes@EDS.COM] > Sent: Friday, April 07, 2000 9:55 AM > To: DB2-L@RYCI.COM > Subject: Re: Little DB2, DB2 UNO, DB2 for Elsewhere ... > > I would like to offer "DB2 Lite" :-) > > -----Original Message----- > From: KUHN,Ulrich [mailto:ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU] > Sent: 07 April 2000 08:15 > To: DB2-L@RYCI.COM > Subject: Little DB2, DB2 UNO, DB2 for Elsewhere ... > > > > I'm happy [...] 35098 24 45_Re: REXX & DSNWLIR - Confirm: It work well !!14_Massimo Scarpa16_mscarpa@CESVE.IT30_Fri, 7 Apr 2000 11:58:29 +0200528_us-ascii Hi James.

Ok, I resolved all problems with areas and I confirm you were right, you can call IFI thru REXX

via DSNWLI2 and formatting output area (for a command, for instance) via REXX. No

assembler required !!!!

THX again & Regards

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 35123 18 12_Mailing list7_Arms M.15_mg.am@LIBERO.IT30_Fri, 7 Apr 2000 12:16:41 +0200517_- Hi All,

I hope this post is not OT. I'd like to spend some months in UK in order to improve my english language and my professional experiences.

Is there any mailing list regarding DB2/Oracle DBA position?

Thanks



Arms M. DB2 and Oracle DBA

===============================================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. 35142 42 16_Re: Mailing list20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM30_Fri, 7 Apr 2000 11:58:49 +0100540_iso-8859-1 Sending a blank email to database-subscribe@computerweeklymail.co.uk will get you regular mailings of UK database vacancies. It is easy to unsubscribe and you won't get junk mail. Computer Weekly is a UK trade paper.

-----Original Message----- From: Arms M. [mailto:mg.am@LIBERO.IT] Sent: 07 April 2000 11:17 To: DB2-L@RYCI.COM Subject: Mailing list



Hi All,

I hope this post is not OT. I'd like to spend some months in UK in order to improve my english language and my professional experiences. [...] 35185 32 36_Re: DB2 Capacity Planning - M-Values12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Fri, 7 Apr 2000 12:05:44 +0200391_x-user-defined Hi,

The numbers were taken from an IBM Journal dated 12/99 (if I'm not mistaken), I don't have it here, but give me a FAX number and I'll fax it next week (or scan and email the jpeg). I put some of the numbers into an EXCEL spreadshit. As for the trnslation into M-values - I don't know, although there is one OS/390 book with the full numbers. I'll look for it. [...] 35218 71 17_Re: Puzzling -80312_Isaac Yassin23_yassin@NETVISION.NET.IL30_Fri, 7 Apr 2000 12:12:40 +0200299_x-user-defined Hi, I guess you're talking about the primary index IXBR002. Do you insert from multiple BMP regions using external mechanism to generate the key ? - This may be the culprit. If the answer is no - I suggest to reorg the whole TS and then check again (as last resort) Isaac Yassin [...] 35290 26 15_Re: Load/Unload12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Fri, 7 Apr 2000 12:22:29 +0200192_x-user-defined Hi,

If the table/space structure is the same (or you can control it to be the same) you may use the DSN1COPY REXX I put on the db2-doc list (under APRIL).



35317 27 17_Re: Puzzling -8030_19_mike.holmans@BT.COM30_Fri, 7 Apr 2000 12:07:02 +0100376_- Massimo Scarpa wrote:

> Another time we discovered that an utility loaded failed to load data and > someone, to > reset some recover pending, executed a START ACCESS(FORCE). > We shooted this person and after execution we had some -803,

Are there any jobs going in Italy? I think I could be very happy in an environment where DBAs have a licence to kill. [...] 35345 48 17_Re: Puzzling -80312_Isaac Yassin23_yassin@NETVISION.NET.IL30_Fri, 7 Apr 2000 12:37:19 +0200551_x-user-defined < a license to kill >

DB227 ?

Isaac Yassin



mike.holmans@BT.COM wrote: > > Massimo Scarpa wrote: > > > Another time we discovered that an utility loaded failed to load data and > > someone, to > > reset some recover pending, executed a START ACCESS(FORCE). > > We shooted this person and after execution we had some -803, > > Are there any jobs going in Italy? I think I could be very happy in an > environment where DBAs have a licence to kill. > > Cheers, > > Mike Holmans > BT ISE Technical Design > [...] 35394 30 39_What's the point of STOGROUPS with SMS?0_19_mike.holmans@BT.COM30_Fri, 7 Apr 2000 12:52:09 +0100339_- Owing to the requirement for V6 ultra-large tablespaces to be SMS-managed, we have to put *some* of our DB2 under SMS management, so we're now looking at bringing *all* of our DB2 datasets under SMS management.

If we have ACS routines assigning datasets to disks, why do we need any more DB2 STOGROUPs defined than SYSDEFLT? [...] 35425 267 46_Re: Little DB2, DB2 UNO, DB2 for Elsewhere ...13_Philip Nelson21_teamdba@ATTGLOBAL.NET30_Fri, 7 Apr 2000 12:13:09 +0100525_us-ascii DB2 UNO was mentioned at a recent GSE meeting as the preferred "unofficial official" description.

We've called it DB2 FP for some time now (FP standing for Fisher Price, a well known toymaker). This more a term of endearment, as we actually are very fond of the new kid on the block. It's been very stable and the functionality is impressive - we wish that the father would take on more of the son's personality (with the exception of its utilities, where we'd like the son to take after the father) !!! [...] 35693 28 17_Re: Puzzling -80314_Massimo Scarpa16_mscarpa@CESVE.IT30_Fri, 7 Apr 2000 14:32:54 +0200333_us-ascii I think you'd be verrrry angry if someone should have canceled a job and then executed a

START ACCESS(FORCE) of the main ($$$$$$$) DB in your production environment,

without any warning to production people. And moreover during a weekend (our db was a 24x7

db). Our CEO gave us licence to kill !!! [...] 35722 50 36_Re: DB2 Capacity Planning - M-Values9_Chris Tee18_TEECHRI@UK.IBM.COM30_Fri, 7 Apr 2000 14:19:34 +0100659_us-ascii Isaac Yassin on 07/04/2000 11:05:44

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Chris Tee/UK/Contr/IBM) Subject: Re: DB2 Capacity Planning - M-Values







Hi,

The numbers were taken from an IBM Journal dated 12/99 (if I'm not mistaken), I don't have it here, but give me a FAX number and I'll fax it next week (or scan and email the jpeg). I put some of the numbers into an EXCEL spreadshit. <== what most people think of Microsoft ?!? As for the trnslation into M-values - I don't know, although there is one OS/390 book [...] 35773 59 43_Re: What's the point of STOGROUPS with SMS?0_14_dcreed@CSC.COM30_Fri, 7 Apr 2000 08:33:51 -0500407_us-ascii Although I am sure you can get it other ways, you could run STOSPACE to monitor application growth. It does seem kindof silly to see 100 different STOGROUPS all with volumes = "*". The "*all* of our DB2 datasets under SMS management." is usually not recommended by this listgroup. (however mileage may vary). I don't know of other reasons. (definitely only 1 cent worth comment or less) Danny [...] 35833 25 31_Documentation for TCP/IP errors15_Tjader, Polly A20_patjader@EASTMAN.COM30_Fri, 7 Apr 2000 09:41:59 -0400340_- Can someone point me to the documentation for errors such as this one that we often receive from our distributed applications? I know it means there is a break in the connectivity somewhere, but in all of the manuals we have, I'm having trouble finding the return code and reason code listing. Any help would be greatly appreciated! [...] 35859 55 17_Re: Puzzling -80312_Myron Miller22_myronwmiller@YAHOO.COM30_Fri, 7 Apr 2000 06:46:13 -0700449_us-ascii Most of us DBAs have occasions that we get extremely angry about applications and certainly wish we could kill a few of them. But we're restricted from doing that no matter the justification. At the minimum we wish we could get the offender fired for particularly egarious offenses but I've seen too many cases where pointy haired management intervened. Like he stated, we'd love to have the actual license to kill, maim or brutalize. [...] 35915 67 17_Re: Puzzling -8030_19_mike.holmans@BT.COM30_Fri, 7 Apr 2000 14:26:08 +0100413_- I agree entirely about the particular incident. What I find difficult to believe is that there are user populations out there which do not inflame their DBAs with the desire to commit mass-murder on a regular basis.

I was looking foward very much to the implementation of triggers in DB2. Then I found out how pathetically limited they were, only allowing us to perform other database-type actions. [...] 35983 212 46_Re: Little DB2, DB2 UNO, DB2 for Elsewhere ...18_Merdjanian, Arthur25_arthur.merdjanian@HBC.COM30_Fri, 7 Apr 2000 09:27:52 -0400597_iso-8859-1 Ulrich, If you want the shortest ... what about reserving DB2 alone for "DB2 UDB for OS/390" and using UDB for all the others? I believe this used to be a common practice before IBM added UDB to what was originally just DB2 for OS/390.

> -----Original Message----- > From: KUHN,Ulrich [SMTP:ulrich.kuhn@EMPLOYMENTNATIONAL.COM.AU] > Sent: Friday, April 07, 2000 3:15 AM > To: DB2-L@RYCI.COM > Subject: Little DB2, DB2 UNO, DB2 for Elsewhere ... > > I'm happy with any of the suggestions, although "DB2 for Elsewhere" is a > bit on the lengthy side for my taste. Is there any [...] 36196 21 17_Re: Puzzling -80314_Massimo Scarpa16_mscarpa@CESVE.IT30_Fri, 7 Apr 2000 15:54:57 +0200519_us-ascii I'm afraid we have to wait DB2 V 69 ARMAGEDDON DB (the 'natural' evolution of UNIVERSAL

DB) to execute your wonderful and very useful SQL. And without the problem of retorts, It'll be

enough to grant the execution only to DBAs !!

Cheers Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 36218 204 17_Re: optimizerrrrr23_Trivedi, Kaushal J, BGM17_kjtrivedi@ATT.COM30_Fri, 7 Apr 2000 09:56:23 -0400326_iso-8859-1 Hi All, cheers for replying folks , I am sending the details you folks ad asked for , One bit of details I missed last time was that there was also a List prefetch that was not chosen after the clause was used, hope this is enough , if not do let me know. (I have replaced some real values for host variables) [...] 36423 92 43_Re: What's the point of STOGROUPS with SMS?13_Adrian Savory24_adrian.savory@ZURICH.COM30_Fri, 7 Apr 2000 14:51:14 +0100574_us-ascii Mike,

If you have only one DB2 stogroup then all your objects will have the same VCAT. This might be a bit restrictive for your ACS routines to work effectively, though they can still pick up the 3rd and 4th qualifiers for the database and spacename.

If your naming standards are strict you might be able to get away with just the one stogroup. Can you guarantee that the ACS routines could easily identify the type of object you're dealing with from just the dataset name? One neat idea I've recently come across is that you can use the VOLUMES [...] 36516 61 43_Re: What's the point of STOGROUPS with SMS?19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Fri, 7 Apr 2000 08:57:53 -0500590_iso-8859-1 Mike, I like your idea! If the naming standards have changed a few times over the years, the ACS routines will be fun to write as far as spreading things throughout your "DASD Safari Park". But then the new SHARK Controller/DASD solves that little problem, right?

Best Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you [...] 36578 59 17_Re: Puzzling -80313_Marcus Davage29_Marcus.Davage@LLOYDSTSB.CO.UK30_Fri, 7 Apr 2000 14:59:53 +0100408_iso-8859-1 Don't you mean DB2 V666?



-----Original Message----- From: Massimo Scarpa [mailto:mscarpa@CESVE.IT] Sent: 07 April 2000 14:55 To: DB2-L@RYCI.COM Subject: Re: Puzzling -803



I'm afraid we have to wait DB2 V 69 ARMAGEDDON DB (the 'natural' evolution of UNIVERSAL

DB) to execute your wonderful and very useful SQL. And without the problem of retorts, It'll be [...] 36638 35 40_Java Stored Proc - NullPointerException?13_Chris Roberts19_CTROBERT@HEWITT.COM30_Fri, 7 Apr 2000 09:16:13 -0500420_us-ascii Hello everyone,

I need some help in figuring out a problem I'm having with a stored procedure.

This is my first attempt at working with java stored procedures.

I am developing under the stored procedure builder on DB2 Personal Edition 6.1 SP3.

First - much of the documentation references a remote debugger, but I have been completely unable to find it. Does anyone have a URL? [...] 36674 42 30_DB2/MVS V4 performance problem11_Hilton Tina15_HiltonT@TCE.COM30_Fri, 7 Apr 2000 09:38:09 -0500572_iso-8859-1 I posed this question before, but didn't get any replies so I thought I'd try again with some additional information. We upgraded to V4 a week and a half ago. Since then we've had problems with distributed dynamic SQL done by one of our applications. It's suddenly taking a lot more cpu to process. The total number of threads per day hasn't changed, but I don't know if the number of concurrent threads have. We seem to have problems when we get 8 or more concurrent users. I've checked several of the select statements (which are mostly very complex and [...] 36717 81 34_Re: DB2/MVS V4 performance problem9_Ted Pesta18_tpesta@AMQUEST.COM30_Fri, 7 Apr 2000 10:47:07 -0400637_iso-8859-1 Tina, Are the V4 address spaces set to the same MVS performance groups as the V3 ones were ?

Ted Pesta AmQUEST, Inc.

-----Original Message----- From: Hilton Tina [mailto:HiltonT@TCE.COM] Sent: Friday, April 07, 2000 10:38 AM To: DB2-L@RYCI.COM Subject: DB2/MVS V4 performance problem

I posed this question before, but didn't get any replies so I thought I'd try again with some additional information. We upgraded to V4 a week and a half ago. Since then we've had problems with distributed dynamic SQL done by one of our applications. It's suddenly taking a lot more cpu to process. The total number [...] 36799 59 34_Re: DB2/MVS V4 performance problem12_Parker, Gary23_GParker@ISD.STATE.AL.US30_Fri, 7 Apr 2000 09:50:55 -0500362_iso-8859-1 Hi Tina, I remember a problem similar to this. It was due to the fact that the SQL was performing a Cartesian join, whereby the tables were not joined by common predicates.

-----Original Message----- From: Hilton Tina [mailto:HiltonT@TCE.COM] Sent: Friday, April 07, 2000 9:38 AM To: DB2-L@RYCI.COM Subject: DB2/MVS V4 performance problem [...] 36859 78 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Fri, 7 Apr 2000 09:57:42 -0500542_us-ascii I have been doing some chasing of the question through my IBM rep and here is the answer that I recieved:

Kirkpatrick, here is the response from STL on your question. Hope this clears up what you are seeing on the use of the buffer pools.

Currently, the VPSEQT threshold will only governor pages that are read by the sequential prefetch process (i.e. sequential access plan set by the Optimzer). Both the list and dynamic prefetches pages are considered as RANDOM accessed pages. I hope this answers the question. [...] 36938 45 46_Re: Little DB2, DB2 UNO, DB2 for Elsewhere ...9_Ray Potts23_rpotts@MAIL.STATE.MO.US30_Fri, 7 Apr 2000 09:14:12 -0500445_iso-8859-1 Since they are all supposed to be the DB2 Universal Database Family, how about either:

DB2/390 DB2/NT DB2/Unix DB2/Sol ,etc.

or

UDB/390 UDB/NT UDB/Unix ,etc.





----- Original Message ----- From: Merdjanian, Arthur Newsgroups: bit.listserv.db2-l To: Sent: Friday, April 07, 2000 8:27 AM Subject: Re: Little DB2, DB2 UNO, DB2 for Elsewhere ... [...] 36984 29 57_Details on User Defined Structured Types and Typed Tables14_Harold Trammel19_hlt@NAPCC.ASPCA.ORG30_Fri, 7 Apr 2000 10:21:10 -0500519_us-ascii I am running DB2/NT 6.1. My logical data model has a number of supertype/subtype situations. Initially I thought that setting up Typed Tables would streamline implementation of these. My further reading of the SQL Reference and the Administration Guide: Design and Implementation has left me somewhat confused as to the practical value of Structured UDT and Typed Tables. Although there seems to be quite a bit of power, there also seems to be some significant, but not very well documented, limitations. [...] 37014 91 34_Re: DB2/MVS V4 performance problem13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Fri, 7 Apr 2000 20:49:10 +0530478_us-ascii hi !!

In such situations the first thing that I would try is do an explain of the queries analyse them for the index access and compare with the previous ones. . We faced similar problems while migrating to DB2 5.1 . Some online queries giving response problems. We did an explain and saw that optimizer has changed its access search method. So we changed the query a bit to make it very much optimized. Now they give excellent responses better that ever . [...] 37106 230 46_Re: Little DB2, DB2 UNO, DB2 for Elsewhere ...13_Umair Hussain25_umair_hussain@HOTMAIL.COM28_Fri, 7 Apr 2000 10:43:29 CDT728_- How about UDB DD ie distributed database ... this should cover unix,nt and os2.

regards,

Umair Hussain******************************** DBA Consultant * DB2 UDB, Oracle, Sybase, DataJoiner * IBM Certified Solution Expert - DB2 UDB DBA * IBM Certified AIX System Administrator * ********|All Disclaimers Apply|**************

>From: "KUHN,Ulrich" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Little DB2, DB2 UNO, DB2 for Elsewhere ... >Date: Fri, 7 Apr 2000 17:14:47 +1000 > >I'm happy with any of the suggestions, although "DB2 for Elsewhere" is a >bit >on the lengthy side for my taste. Is there any sort of consensus [...] 37337 26 59_DB2/OS390 error - trying to resolve a -803 problem.... ASAP0_23_john.lantz@ZURICHUS.COM30_Fri, 7 Apr 2000 11:41:41 -0400448_us-ascii We are getting a -803 sqlcode on an insert. The error message tells us the RID. I then use the repair utility to locate the data using the RID from the error message. That works, but unfortunately the table is compressed and the resulting output is illegible. I've used the DSN!PRNT utility in the past to dump pages using the "format expand" option to see compressed pages. But you need a page number to use the DSN1PRNT, not a RID. [...] 37364 28 40_Compatibility of a DB2 (OS/390) program.17_Giorgio De Nunzio25_giorgio.denunzio@UNILE.IT30_Fri, 7 Apr 2000 18:04:24 +0200533_us-ascii Hi all! We have begun the porting of some of our shop's UNIX/NT applications to DB2 on OS390 (they were based on a proprietary database engine). As we are just at our first attempts, we are using the MVS version of DB2 we already have at home, i.e. DB2 v3 under MVS 5.2.2, and we plan to upgrade as soon as we see that our porting is succesful on that old DB2 version. My question: does a DB2 program (i.e. a load module and the associated DBRM file) precompiled, compiled etc with DB2 version 3, work on a system with [...] 37393 21 33_Loding data in partition of table20_Chatterjee, Anindita28_Anindita.Chatterjee@NIKE.COM30_Fri, 7 Apr 2000 08:52:35 -0700347_us-ascii I have a question. when we load data into one partition of a partitioned table, why do all the other ppartitions get locked , even though we are mentioning the partition number in the jcl and the data is in the correct range for that partition.

can we avoid the locking of all partitions when loading data into one partition? [...] 37415 61 63_Re: DB2/OS390 error - trying to resolve a -803 problem.... ASAP13_McDonald, Ken20_Ken_McDonald@BMC.COM30_Fri, 7 Apr 2000 11:07:17 -0500530_ISO-8859-1 John,

The RID contains the page number... Assuming that you are Version 5, you should have a 5 byte RID. The first four bytes are the page number and the last byte is the Row ID in the page. If the page is partitioned, the (variable depending on page size and partitions in the space) high order bits of the page number represent the DSNUM. If you want to include the RID in a reply along with Is the tablespace partitioned? If so, what page size and number of parts? I'll be glad to see if I can help with [...] 37477 112 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 7 Apr 2000 12:13:30 -0400643_us-ascii My first guess was correct. The test I did was list-prefetch vs. Dynamic prefetch so I saw same results. I confused this with sequential vs list prefetch.

This clears the confusion. Thanks for letting us know.



> -----Original Message----- > From: L-IS.Kirkpatrick@MUTUALOFOMAHA.COM > [SMTP:L-IS.Kirkpatrick@MUTUALOFOMAHA.COM] > Sent: Friday, April 07, 2000 10:58 AM > To: DB2-L@RYCI.COM > Subject: Re: More testing of VPSEQT/HPSEQT as it applies to "LIST > PREFETCH " > > I have been doing some chasing of the question through my IBM rep and here > is > the answer that I recieved: > > Kirkpatrick, here is [...] 37590 101 34_Re: DB2/MVS V4 performance problem0_19_Tim.Lowe@STPAUL.COM30_Fri, 7 Apr 2000 10:43:39 -0500467_us-ascii Tina, If I am "optimistic" about the changes over the years in the optimizer, then it has only improved from version to version. Therefore, assuming that the optimizer has gotten "smarter", then the only other factors are the SQL and the runstats. If the SQL has not changed, then since each version of DB2 takes advantage of more runstats data, then I would like to know if you have full, complete, current runstats on the tables, columns and indexes. [...] 37692 32 26_Re: db2 UDB control center19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM30_Fri, 7 Apr 2000 09:51:34 -0700368_iso-8859-1 The right click "Generate SQL" mentioned actually formats and runs db2look. If IBM is going to use it that way, then you'd think they'd want it to support all DDL!

Tim

-----Original Message----- From: Philip Nelson [mailto:teamdba@attglobal.net] Sent: Friday, April 07, 2000 1:13 AM To: DB2-L@RYCI.COM Subject: Re: db2 UDB control center [...] 37725 33 37_Re: Loding data in partition of table13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Fri, 7 Apr 2000 12:03:11 -0500422_US-ASCII Please provide the JCL so we can see how the load statement is defined. Kurt

>>> "Chatterjee, Anindita" 04/07/00 10:52AM >>> I have a question. when we load data into one partition of a partitioned table, why do all the other ppartitions get locked , even though we are mentioning the partition number in the jcl and the data is in the correct range for that partition. [...] 37759 68 37_Re: Loding data in partition of table12_HEPP SHERY C17_schepp@SRPNET.COM30_Fri, 7 Apr 2000 10:11:41 -0700576_ISO-8859-1 What version of DB2 are you on? Do you have any npi indexes on the table? If version 4 you may want to drop npi and then load data, recreate npi's and recover to avoid locks.



Regards,



Shery Hepp SRP Sr. Database Adminstrator Information Services



(602)236-2650 pager (602)275-2508(8035) Dreams-we don't see things as they are, we see things as we are..... Success is getting what you want.....Happiness is wanting what you have...... "The question is not whether we will die, but how we will live." * Joan Borysenko [...] 37828 219 17_Re: optimizerrrrr12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Fri, 7 Apr 2000 17:32:32 +0200326_x-user-defined Hi,

I know it's redundant but give all index columns in the order by as they are all on '=' it won't change the order of the result set but should cause no sort even without the optimize clause. For clarity you may want to move "A.RATE_STRUC_VER_NBR = 1" before the "A.CHRG_CAP_ID = B.CHRG_CAP_ID". [...] 38048 72 37_Re: Loding data in partition of table0_19_Tim.Lowe@STPAUL.COM30_Fri, 7 Apr 2000 12:55:40 -0500344_us-ascii Anindita, Since there is so little information, I am only making a guess, but I think that the load statement might be the problem. Take a look a "loading partitions" under LOAD in the DB2 utilities manual. (page 2-95 in my DB2 V5 utilities manual) Did you do a LOAD... RESUME YES.... REPLACE PART ... OR a LOAD....PART ..REPLACE [...] 38121 31 17_Re: Puzzling -80312_Bing Alabata25_bing.alabata@MCMASTER.COM30_Fri, 7 Apr 2000 12:52:01 -0500428_us-ascii Hi Helen,

Since you've rebuilt the primary index and ran CHECK successfully, your data must be intact. Run DSN1PRNT to generate a formatted dump of the page in question using the RID in the error message. This should give you the purchase order number which equals the purchase order value being inserted, trigerring an -803 SQL error. Verify if this is the column value the program uses before the INSERT. [...] 38153 220 17_Re: optimizerrrrr9_Birk, Tim17_BirkT@DIEBOLD.COM30_Fri, 7 Apr 2000 14:14:30 -0400303_iso-8859-1 Kaushal, As you pointed out, your first query, without optimize, uses list prefetch. Since LP reads the data in rid sequence, it must be sorted to satisfy the order by. When you use optimize, list prefetch is not used, so reading the data through the index results in it being in order. [...] 38374 97 37_Re: Loding data in partition of table20_Chatterjee, Anindita28_Anindita.Chatterjee@NIKE.COM30_Fri, 7 Apr 2000 11:40:15 -0700294_us-ascii hI!



We do have npi defined on our tables. We are at Ver4 but actually all our npis are defined as type 2. So, I was not suspecting that this should lead to a locking problem as type 2 indexes (I think) have the feature of logically dividing npis across partitions. [...] 38472 140 37_Re: Loding data in partition of table0_19_Tim.Lowe@STPAUL.COM30_Fri, 7 Apr 2000 13:55:27 -0500565_us-ascii I think that you are looking for selective partiion locking (lockpart yes), which was introduced in DB2 V5. See the DB2 V5 release guide.

Thanks, Tim













"Chatterjee, Anindita" on 04/07/2000 01:40:15 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Tim Lowe/sfm/spc)





Subject: Re: Loding data in partition of table [...] 38613 139 34_Re: DB2/MVS V4 performance problem11_Hilton Tina15_HiltonT@TCE.COM30_Fri, 7 Apr 2000 14:14:54 -0500551_iso-8859-1 Thanks to everyone who answered me. They actually wanted to fall back to V3 at first, but I convinced them (I think) that it wouldn't be wise to go back to unsupported software.

We did redo runstats on all the tables involved and even reorged them too, but it didn't help much. We lowered the dispatching priority of the distributed address space to the lowest one we have, and it appears to be helping -- at least for now. I am trying to do traces and explains, but there's no way of knowing what the access path was before so [...] 38753 17 23_Storing pictures in DB210_Vinson Lee19_vlee@SDCCD.CC.CA.US30_Fri, 7 Apr 2000 12:27:29 -0700515_iso-8859-1 We're currently at version 4 of DB2. Does anyone know which versions of DB2 on the mainframe support storing of pictures. I would like to know if anyone out there is doing this and what your experiences are. Anything we have to watch out for, etc.

Thanks.

================================================ 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. 38771 25 40_Finding SYSCOPY transactions in the logs0_20_Carl_Nelson@AICI.COM30_Fri, 7 Apr 2000 14:41:43 -0500340_us-ascii My modify job does not list all datasets removed from SYSCOPY and I need to know what the job is removing. I used the "syscopy" parm on DSN1LOGP against the archived logs but did not recognize any entries as rows deleted from SYSCOPY. I am of the understanding that SYSCOPY activity is recorded only in the logs and SYSLGRNX. [...] 38797 38 27_Re: Storing pictures in DB211_Jerry, Jeff24_Jeff.D.Jerry@NORWEST.COM30_Fri, 7 Apr 2000 14:41:28 -0500298_- DB2 UDB Version 6 for OS/390 allows you to store LOBs (large objects). I believe what you are looking for are BLOBs (binary large objects) and is in this version.

At the present time we are not storing pictures, but may be in the future so this question is of interest to us as well. [...] 38836 110 37_Re: Loding data in partition of table16_Schuchard, Peter34_Peter.Schuchard@STARWOODHOTELS.COM30_Fri, 7 Apr 2000 14:56:05 -0400550_iso-8859-1 Make sure you have lockpart = yes...

-----Original Message----- From: Chatterjee, Anindita [mailto:Anindita.Chatterjee@NIKE.COM] Sent: Friday, April 07, 2000 2:40 PM To: DB2-L@RYCI.COM Subject: Re: Loding data in partition of table



hI!



We do have npi defined on our tables. We are at Ver4 but actually all our npis are defined as type 2. So, I was not suspecting that this should lead to a locking problem as type 2 indexes (I think) have the feature of logically dividing npis across partitions. [...] 38947 178 47_Re: DB2 Connect v6 and Control Center on OS/39012_Hylton Tom P20_Tom.P.Hylton@IRS.GOV30_Fri, 7 Apr 2000 16:24:29 -0400441_ISO-8859-1 To find the appropriate Program Directory listing, look in the $INDEXPD member of wherever you put your PGMDIR dataset. A scan shows:

PROGRAM DIRECTORY INDEX TABLE OF ORDERED PRODUCTS _________________________________________________ PRODUCT PROGRAM PROGRAM DIRECTORY DIRECTORY MEMBER DESCRIPTION ____________________________ _________ ___________

DB2 MGT TOOL PKG 6.01.0 QI108193 IBM DB2 UDB MGMT TOOLS OS/390 [...] 39126 63 27_Re: Storing pictures in DB220_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 7 Apr 2000 16:30:02 -0400612_us-ascii Dear all Does any one has problems updating BLOB columns or using "SET :LOCATOR = :LOCATOR1". I seems to have trouble getting past 40k bytes of BLOB. DB2 issued 00C90101 on module DSNOGLOB.

I would be happy to hear experiences from any big time users of BLOB.

Thanks

> -----Original Message----- > From: Jerry, Jeff [SMTP:Jeff.D.Jerry@NORWEST.COM] > Sent: Friday, April 07, 2000 3:41 PM > To: DB2-L@RYCI.COM > Subject: Re: Storing pictures in DB2 > > DB2 UDB Version 6 for OS/390 allows you to store LOBs (large objects). I > believe what you are looking for are BLOBs (binary [...] 39190 86 43_Re: What's the point of STOGROUPS with SMS?14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Fri, 7 Apr 2000 16:57:30 -0400429_ISO-8859-1 Dataset placement still DOES matter. DASD performance in most installations is a mess at best , and yes, there are some that have good performance - but not when it has all been given to SMS. I know this will will flame the debate again - and I again, I say that I will be happy to change my opinion when someone sends me RMF data that will prove they are getting good performance when everything is SMS managed. [...] 39277 242 47_Re: DB2 Connect v6 and Control Center on OS/39014_Philip Gunning20_pgunning@BOSCOVS.COM30_Fri, 7 Apr 2000 17:03:50 -0400477_iso-8859-1 RE: DB2 Connect v6 and Control Center on OS/390Tom, I got it from my IBM DB2 local rep. It is GI 10--8193-01. Maybe you can order it. HTH Phil Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS

----- Original Message ----- From: Hylton Tom P Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Sent: Friday, April 07, 2000 4:24 PM Subject: Re: DB2 Connect v6 and Control Center on OS/390 [...] 39520 111 67_Re: More testing of VPSEQT/HPSEQT as it applies to "LIST PREFETCH "14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sat, 8 Apr 2000 09:16:31 +1000584_us-ascii Larry,

I got a similar answer from Jim Teng but with a bit more detail. For Dynamic Prefetch, the prefetched pages are initially marked Sequential but altered to Random if Getpage done, i.e. actually used. Same for List Prefetch.

I am puzzled by List Prefetch marking pages as random when Getpage occurs since for sorted RID lists, all list prefetched pages will be used barring those at the end if we stop fetching rows. There seems to minimal chance of reacessing these pages after Getpage of the next, so why mark them as random? I'll try to get to the [...] 39632 243 17_Re: optimizerrrrr14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sat, 8 Apr 2000 09:46:12 +1000371_us-ascii Kaushal,

You didn't actually provide any index stats, e.g. clusterratio, but I will have to guess possible answers anyway.

Try running the version with OPTIMIZE first and 'without' second, to see if CPU saving is consistent. The reason is that the reason is that the Optimize version is going to do more Sync I/O unless BP is assisting a lot. [...] 39876 82 63_Re: DB2/OS390 error - trying to resolve a -803 problem.... ASAP11_rick creech18_ykcirc@HOTMAIL.COM28_Fri, 7 Apr 2000 17:57:18 PDT598_- Hey Ken, You must have forgotten: only Large tablespaces in version 5.1 have 5 byte rids. Everything else is still 4 bytes. Reards, Rick

>From: "McDonald, Ken" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DB2/OS390 error - trying to resolve a -803 problem.... ASAP >Date: Fri, 7 Apr 2000 11:07:17 -0500 > >John, > >The RID contains the page number... Assuming that you are Version 5, you >should have a 5 byte RID. The first four bytes are the page number and the >last byte is the Row ID in the page. If the page [...] 39959 30 17_Re: optimizerrrrr10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM28_Sat, 8 Apr 2000 03:55:43 GMT567_us-ascii Hi Kaushal, Many of the replies have already come and clarified all the things and main culprit was list prefetch which was causing the physical RDS sorting .I presume that the application in which you have coded this SQL is online or may be batch report application as usually we don't require ORDER BY in case of pure batch applications(but you mentioned most of the time one row will be returned is bit confusing). Anyway, if you are using this in online application then you should code the scrolling logic to cope up with optimize for 1 row .If it [...]