1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 1999, week 3
2 42 26_Re: Updating System Tables22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Wed, 15 Dec 1999 05:13:18 +0000498_ISO-8859-1 Harvey sounds pretty dangerous to me .. I would be very surprised if this kind of thing made it out onto the open market.
stick with updating the ones IBM allow you to update .. but only if you HAVE to ..
regards
Leslie Pendlebury-Bowe DB2 SAP OS390
______________________________ Reply Separator _________________________________ Subject: Updating System Tables Author: Harvey Wachtel at Internet Date: 12/14/99 7:04 PM [...]
45 27 21_REXX Language Support15_Shaun Z Lombard36_Shaun.Z.Lombard@TRANSPORT.QLD.GOV.AU31_Wed, 15 Dec 1999 16:57:10 +1000542_us-ascii Has anyone had any success using the sample DRAW program supplied in the REXX Language Support manual for DB2 OS/390 V5 ?
Thanks
Shaun
************************************************************* Opinions contained in this e-mail do not necessarily reflect the opinions of the Queensland Department of Main Roads, or of Queensland Transport. If you have received this electronic mail message in error, please immediately notify the sender and delete the message from your computer. [...]
73 124 26_Re: Updating System Tables9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Wed, 15 Dec 1999 08:03:31 +0000412_us-ascii
I remember a problem back in V2.1 (I think) when some SYSCOPY entries were corrupted by a DB2 bug. By REPAIRing the UPDATES column to 'Y', for the appropriate column, in SYSCOLUMNS for SYSIBM.SYSCOPY it was possible to run 'UPDATE' SQL against SYSCOPY. This allowed the corrupted entries to be corrected. Phil Grainger may remember this one (though he might not care to admit to it now !). [...]
198 90 52_Re: Application Change Management for DB2 for OS/3900_13_hjung@CSC.COM31_Tue, 14 Dec 1999 16:09:49 -0500432_us-ascii
We use Endevor for our source code. We also maintain our DCLGEN's and production DB2 utilities under Endevor.
Endevor is a good configuration management tool which seems to provide a lot of functionality when used properly. It does however require that you have a experienced Endevor administrator or alot of time to train someone thoroughly, and Endevor administrators are not so easy to find. [...]
289 53 25_Re: REXX Language Support17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM31_Wed, 15 Dec 1999 06:26:32 -0500592_- While I haven't used the sample DRAW program (since I already had one), I have done some testing with the REXX DB2 V5 interface. There are several problems IBM is working on that will be addressed by APAR PQ31316, so you should get this applied when it becomes available. There are currently some problems handling NULLs, floating point numbers and getting 0C7's in DSNREXX. These problems aside, the DB2 REXX interface has been working fine for me. It really simplifies accessing DB2 from REXX by just embedding the SQL, and certainly beats the old way of calling DSNTEP2 and parsing [...]
343 38 26_Re: Updating System Tables17_zeliger, bat-chen24_zeliger_bat-chen@EMC.COM31_Wed, 15 Dec 1999 07:44:53 -0500466_iso-8859-1 Hi,
Look for SPRMCTU in macro DSN6SPRC and you should be all set. BUT I completely agree with the other responses to your question. Don't you dare using it on a production system ! it could be nice on a test system though.
Have a nice day, Bat-Chen
-----Original Message----- From: Harvey Wachtel [mailto:HWachtel@UAPC.CUNY.EDU] Sent: Tuesday, December 14, 1999 7:05 PM To: DB2-L@RYCI.COM Subject: Updating System Tables [...]
382 28 28_Re: Total Virtual Pool Size111_John Rosser24_jhrosser@DUKE-ENERGY.COM31_Wed, 15 Dec 1999 08:03:44 -0800577_- Currently allocated nearly 900 Mb of virtual buffer pool storage in both members of a data sharing group. Nervous? Sure I am, but not all buffers are being used concurrently. We are splitting applications into their own DB2s to prove to the app support group, some of their code ...(well, you probably know what I mean). Plenty of work, but will be easier to tune. Other items of interest besides actual virtual buffer pool values in the DBM1 address space: 1)EDM Pool Size 2)RID Pool Size 3)some DB2 code 4)Control blocks for # of open datasets, virtual & hiper pools, [...]
411 43 43_Re: how can I create a full db by db2batch?14_Jakofcic Darja22_darja.jakofcic@N-LB.SI31_Wed, 15 Dec 1999 15:20:18 +0100584_- You can use GRANT sql statement to authority to a user or group . See SQL reference for details. LP Darja
> -----Original Message----- > From: Giuseppe [SMTP:dedonno@ARIADNE.IT] > Sent: 14. december 1999 16:56 > To: DB2-L@ryci.com > Subject: Re: how can I create a full db by db2batch? > > Wayne Driscoll wrote: > > > In UDB, users are not created in the database, instead the UDB process > uses > > the native operating system security system to perform authentication. > In > > addition, system wide authorizations (SYSADM, SYSCTRL and SYSMAINT) are > > maintained by [...]
455 34 55_Problems with DB2 UDB AIX on RS/6000 HACMP (SP) cluster13_Philip Nelson24_philip@PANDP.SALTIRE.ORG31_Wed, 15 Dec 1999 14:29:38 +0000373_us-ascii Is anyone else out there running DB2 UDB for AIX (V5.2) on an HACMP cluster ?
Have you tried the discovery facilities within Client Configuration Assistant ?
Does it find the DB2 instance successfully but catalog the internal ethernet address of the SP cluster ?
Do you get all the options within Control Center is you catalog manually ? [...]
490 14 34_Re: -904 error on DSNDB06.DSNADX0115_Robert Reynolds19_rreynol@INDIANA.EDU31_Wed, 15 Dec 1999 08:46:08 -0800386_- Thank you to all who replied. I did a RECOVER INDEX (SYSIBM.DSNADX01) and then the drop worked fine.
Robert Reynolds Indiana University
================================================ 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.
505 45 26_Re: Updating System Tables14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Wed, 15 Dec 1999 10:14:05 -0500625_us-ascii Thanks to all who responded to my question abour updating restricted systemn tables. We're aware of the dangers and would of course test our changes ver-r-r-r-y carefully on our test database.
This inquiry is related to my initial inquiry to this list about a month ago which was widely ignored, probably because its title contained the phrase "field procedure", which is apparently something that few installation besides us have had occasion to try. (This is a common situation at our shop; I'm not sure if it's because we're more clever than everyone else or, more likely, because we're more foolish; [...]
551 52 28_Re: Total Virtual Pool Size122_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Wed, 15 Dec 1999 14:09:23 +0000706_ISO-8859-1 I am still working on my "very" long mail detailing how you calculate your Virtual Pool storage ..
more to come soon ..
Les
______________________________ Reply Separator _________________________________ Subject: Re: Total Virtual Pool Size1 Author: John Rosser at Internet Date: 12/15/99 8:03 AM
Currently allocated nearly 900 Mb of virtual buffer pool storage in both members of a data sharing group. Nervous? Sure I am, but not all buffers are being used concurrently. We are splitting applications into their own DB2s to prove to the app support group, some of their code ...(well, you probably know what I mean). [...]
604 81 25_Re: REXX Language Support14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Wed, 15 Dec 1999 10:20:27 -0500595_iso-8859-1 Is REXX DB2 V5 interface a seperately priced item or is it available for free for V5 customers ?
Thanks, Manas.
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Scarcella, Gary V > Sent: Wednesday, December 15, 1999 6:27 AM > To: DB2-L@RYCI.COM > Subject: Re: REXX Language Support > > > While I haven't used the sample DRAW program (since I already had one), I > have done some testing with the REXX DB2 V5 interface. There are several > problems IBM is working on that will be addressed by APAR PQ31316, so [...]
686 106 25_Re: REXX Language Support0_22_BILL_GALLAGHER@PHL.COM31_Wed, 15 Dec 1999 10:22:31 -0500544_us-ascii Manas,
It's free.
Bill Gallagher
Manas Dasgupta on 12/15/99 10:20:27 AM
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: Re: REXX Language Support
Is REXX DB2 V5 interface a seperately priced item or is it available for free for V5 customers ? [...]
793 53 11_Re: DSNDB070_23_Mike_Levine@TEKHELP.NET31_Wed, 15 Dec 1999 10:31:32 -0500344_us-ascii Hi,
I don't agree that you should allocate DSNDB07 workfiles with secondary extents. What tends to happen is that the dataset will use up all of the extents and/or space on the volume and the process may abend anyway. You are better off just allocating enough space, all in primary allocations (DASD is cheap now, right?). [...]
847 61 42_Re: DB2 5.0 stored procedures and servlets12_Daniel Kirby25_daniel.kirby@CITICORP.COM31_Wed, 15 Dec 1999 10:34:24 -0500469_us-ascii Thanks for the reply. My problem seems to be that I am not even reaching the stored procedure, let alone returning a result set. The stored procedure execute count is not incrementing. I moved the servlet to the OS390 WebSphere platform, hoping that having both servlet and stored procedure on the same platform would help, but no. The stored procedure works when called by a mainframe program. I am using the following java, which seems pretty standard: [...]
909 15 40_Platinum Recovery Analyzer DR procedures12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Wed, 15 Dec 1999 09:20:22 -0800531_- I am working on setting up disaster recovery procedures for our production DB2 region using PRA. I would like to talk with someone who is sucessfully using this for DR on an active 24X7 system to discuss what customization was needed. Please contact me at my email address.
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.
925 45 30_Re: UDB on NT - NETBIOS errors13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 15 Dec 1999 09:48:59 -0600412_US-ASCII According to the messages reference RC=23 is an Invalid adapter no further information is available. Contact support they will be able to assist you. Kurt
>>> 12/14/99 10:48PM >>> Dear Listers, we've been experiencing a number of problems this morning with our DB2 UDB v 5 production system running on NT. The following messages are being displayed: [...]
971 28 35_Handling -911 and -913 in CICS pgms8_madhavan16_madhavan@INF.COM31_Wed, 15 Dec 1999 21:38:01 +0530463_- Hi,
In CICS programs after executing a sql statement, we have the following requirement if we get a sql code of -911 or -913:
Try executing the sql 3 times to see if the deadlock continues. If yes, give the control back to the user in the same screen with a message. User is allowed to try the same operation again. Basically we are not terminating the application. I guess we should cursor fetch sqls update sqls differently in these cases. [...]
1000 36 36_Installing FP2 on WinNT DB2 V6.1 PDE13_Philip Nelson24_philip@PANDP.SALTIRE.ORG31_Wed, 15 Dec 1999 16:11:12 +0000389_us-ascii I'm attempting to install the fixpack on DB2 V6.1 PDE on WinNT.
I follow the instructions, issue DB2STOP and DB2ADMIN STOP and then run SETUP.EXE.
It then comes up with a list of things which must be stopped before the upgrade can proceed.
Some of the processes I recognized as the DB2 JDBC services and I've stopped these from the NT Services facility. [...]
1037 51 44_Re: Platinum Recovery Analyzer DR procedures22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Wed, 15 Dec 1999 15:14:45 +0000274_ISO-8859-1 Just in case anybody needs this information ..
I have done this is some details this year for my current site ... I am off home now .. but will be happy for you to send me anything you need me to talk you through ... e-mail me on the above address .. [...]
1089 71 47_DB2 / PeopleSoft -904 problems creating indexes14_Johnson, Tracy22_TJohnson@GUIDEMAIL.COM31_Wed, 15 Dec 1999 10:18:12 -0600430_iso-8859-1 Hello! We are installing the PeopleSoft HR (v7.51) package (our first PeopleSoft implementation) on our v5.1 (9907) system and are experiencing problems trying to load the indexes. While creating a large number of indexes, we're receiving the following errors: The error in the job was ... DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00D70017, TYPE OF RESOURCE [...]
1161 80 11_Re: DSNDB0711_rick creech18_ykcirc@HOTMAIL.COM29_Wed, 15 Dec 1999 08:27:59 PST399_- I agree heartily. Whenever I allowed secondary extents on DSNDB07, someone would always blow them out to 119 extents, and the solution was to tune their SQL. So it is easier to maintain the system by tuning the SQL and leaving DSNDB07 tablespaces always on 1 extent; than to tune the SQL and fix the extents on DSNDB07. Merry Christmas everyone and happy Honeka, (sorry if I misspelled) Rick [...]
1242 84 11_Re: DSNDB0716_David Sanfilippo23_david.sanfilippo@DB.COM31_Wed, 15 Dec 1999 10:18:40 -0600582_us-ascii Maybe it's semantics or maybe I'm confused, but doesn't the DASD serve to backup the bufferpool for sorts that can't be completed in the sortpool? Does DB2 write to DASD if the sort can be completed in the bufferpool? That is, does DB2 only write to DASD when the bufferpool thresholds are hit? I thought avoiding these I/Os was one of the advantages of having a large, tuned bufferpool dedicated to DSNDB07. Is the sequence DB2 follows for sorts: 1. Use the sortpool. 2. Use the available bufferpool. 3. Use the available bufferpool and the allocated sortwork files. [...]
1327 75 52_Re: Application Change Management for DB2 for OS/39012_Roy Brickley20_roy_brickley@APL.COM31_Wed, 15 Dec 1999 07:52:50 -0800524_us-ascii Just a thought - Our company has Endevor/MVS and for a brief period tried out Endevor for DB2. Endevor/DB2 was dropped, as it didn't provide us any extra functionality for coordinating DBA actions (using Platinum) and Endevor for programs/DBRMS/binds.
With the recent acquisition of Platinum by CA, and its previous acquisition of Legent (thus, CA-Endevor), does anyone know if CA plans to integrate RC/Migrator more closely with Endevor, or will(has) CA enhanced Endevor/DB2 to interface with Endevor? [...]
1403 29 25_Performance using PACKAGE10_ajay kumar19_db2v5r1@HOTMAIL.COM29_Wed, 15 Dec 1999 22:06:54 IST415_- Hi,
I am going to shift all my DB2/CICS/COBOL programs from TEST region to PROD region. For that, I created 4 collections, Each collection has around 150 programs. All these collections are binded with One Plan.
My question, Is it slow down my application performance or to have separate plan for the each collection...? In the 4 collections few programs are common to all(ie common programs). [...]
1433 106 51_Re: DB2 / PeopleSoft -904 problems creating indexes12_craig patton21_prgpatton@HOTMAIL.COM29_Wed, 15 Dec 1999 08:37:20 PST530_- Tracy,
Can you create the indexes outside of the long stream of DDL provided? In the same Psoft DBs? The error message indicates an ICF problem or a DASD volume problem. Make sure all DASD is ONLINE that WAS in the STOGROUP at ANY TIME (Including when using SMS). Look in the MVS log to see if there are any additional ICF error messages. In the past I have received this error (yes with Psoft) and it was due to the volume containing the ICF catalog was full and the ICF catalog needed to extend. I also ran into a [...]
1540 109 51_Re: DB2 / PeopleSoft -904 problems creating indexes9_Jim Jones33_jim.jones@INGRAMENTERTAINMENT.COM31_Wed, 15 Dec 1999 10:39:34 -0800309_us-ascii Hi Tracy,
The reason code indicates an ICF catalog error. On our first PeopleSoft install, we ran out of space on both VTOC's, and the ICF catalog. If you're sure you've got VTOC space, check the ICF catalog. Unfortunately, I don't remember the details, we've been on PS for three years. [...]
1650 55 11_Re: DSNDB0714_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Wed, 15 Dec 1999 11:41:23 -0500411_ISO-8859-1 Dave, see notes in pieces flagged with ++++ Regards, Joel
Message text written by DB2 Data Base Discussion List >Maybe it's semantics or maybe I'm confused, but doesn't the DASD serve to backup the bufferpool for sorts that can't be completed in the sortpool? ++++ Yes
Does DB2 write to DASD if the sort can be completed in the bufferpool? ++++ Yes, if you hit DWQT, VDWQT [...]
1706 63 42_Re: DB2 5.0 stored procedures and servlets12_Daniel Kirby25_daniel.kirby@CITICORP.COM31_Wed, 15 Dec 1999 12:03:03 -0500354_us-ascii Just after my last e-mail, the DBA granted access to the package to public and the stored procedure call now works. Progress. I then tried to call a simple stored procedure that uses an integer output parameter and a string input-output parameter, but I get a -113 SQLCODE (invalid characters in a string) when performing "executeQuery()": [...]
1770 78 39_Re: Handling -911 and -913 in CICS pgms11_Jim Knisley27_Jim_Knisley@FORETHOUGHT.COM31_Wed, 15 Dec 1999 11:59:11 -0500563_us-ascii We code batch update programs with retry logic, however we do not code online CICS transactions with retry logic. To ensure that we meet service level agreements, we have set the IRLM wait parameter in ZPARM and the deadlock parameters in the IRLM started task so that the timeout occurs within a few seconds instead of the IBM default of 60 seconds. Our philosophy is that if a deadlock or timeout is going to occur in an online environment, lets get it over with as soon as possible to free the resources. If a long running transaction or a batch [...]
1849 83 39_Re: Handling -911 and -913 in CICS pgms12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 15 Dec 1999 09:01:59 -0800533_us-ascii If you received a -911, then there is only one place to retry - from the beginning of the application. You are not holding any locks and are likely to cause data loss. In any case, I would push for this option whenever possible.
For the -913 case, there are two options. If this was just a timeout, you can try to wait longer than the lock parameters, longer than the other unit of work. If this is a deadlock, then you are hoping that the other unit of recovery does not retry. For one of the most common cases [...]
1933 22 60_Recommended Books on Data Modeling in OLAP/ROLAP Environment23_Mulkey, DW David (7091)21_MulkeyDW@GVL.ESYS.COM31_Wed, 15 Dec 1999 11:24:16 -0600391_- Does anyone know of any books (and/or web sites) that they would recommend for learning about data modeling in a relational OLAP (ROLAP) environment? One particular topic of interest is about the design of star schemas. The material can be DB2 specific or generic for any RDBMS.
Currently, training courses are not an option. Thus, I'm hoping to learn more about it on my own. [...]
1956 127 42_Re: DB2 5.0 stored procedures and servlets14_O'Conner, John26_john.oconner@COURTS.WA.GOV31_Wed, 15 Dec 1999 09:43:42 -0800288_iso-8859-1 We had this problem back in June, and I opened a PMR with IBM. The problem is passing a string as an INOUT parameter. The workaround for this is to use one parameter for input and separate parameter for output. I have included a portion of the interchange I had with IBM. [...]
2084 69 29_Re: Performance using PACKAGE11_Jim Knisley27_Jim_Knisley@FORETHOUGHT.COM31_Wed, 15 Dec 1999 12:38:58 -0500581_us-ascii For our shop using one plan turned out to not be a good idea. I remember attending a session at the DB2 Tech conference a few years ago shortly after packages came out and it was stated that one plan was okay. That was also back when we were being told that one large bufferpool was okay, too. You will experience some performance degredation in the buffer pool and EDM pool, depending on the other bind parameters you choose. For example, RELEASE(DEALLOCATE) for plans with many packages will expand the space requirements in the EDM pool. But, the biggest drawback [...]
2154 119 11_Re: DSNDB079_Jim Jones33_jim.jones@INGRAMENTERTAINMENT.COM31_Wed, 15 Dec 1999 11:23:54 -0800503_us-ascii I don't use secondary extents either, I add a few extra DSNDB07 tablespaces, either with a full allocation or 1 cylinder, depending on DASD space available and how soon I expect to need the spare; and leave them in stop status. Then when we run out of space, I start the spare TS. If I find I need it because of growth I don't stop it, but it it was runaway SQL, then I stop it and keep the spare. As needed I enlarge small spares, if I had to allocate them that way. When I run out of or [...]
2274 48 64_Re: Recommended Books on Data Modeling in OLAP/ROLAP Environment11_Jim Knisley27_Jim_Knisley@FORETHOUGHT.COM31_Wed, 15 Dec 1999 12:49:01 -0500450_us-ascii There are couple of excellent books I have found that deal with multi-dimensional modeling (both star and snowflake). They are: The Data Warehouse Lifecycle Toolkit, Ralph Kimball, ISBN 0-471-25547-5 Building the Data Warehouse, W.H. Inmon
Jim
"Mulkey, DW David (7091)" on 12/15/99 12:24:16 PM
Please respond to DB2 Data Base Discussion List [...]
2323 47 39_Re: Handling -911 and -913 in CICS pgms13_Chie, Jim (C)17_chiej@CONNEXT.COM31_Wed, 15 Dec 1999 09:54:30 -0800440_iso-8859-1 Remember, if you get a -911 the application has ALREADY rolled back to the last sync point, in which case any other updates will be lost. I can't remember if changes to CICS or other resources (such as MQ) are also backed out, but you should check before implementing retry logic. A -913 on the other hand is designed to return control to you BEFORE rollback. It does, however close your cursor, possibly making retry hard. [...]
2371 86 24_Re: DRDA re-bind problem15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Wed, 15 Dec 1999 13:12:52 -0500458_iso-8859-1 Craig,
The Disconnect is parameter for plan and the plan name for DRDA seems to be DISTSERV. I am not able to find this plan in Sysibm.sysplan.
Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com
-----Original Message----- From: craig patton [mailto:prgpatton@HOTMAIL.COM] Sent: Tuesday, December 14, 1999 1:56 PM To: DB2-L@RYCI.COM Subject: Re: DRDA re-bind problem [...]
2458 149 13_dont email me5_Ozden23_o_tutuncu@GEOCITIES.COM31_Wed, 15 Dec 1999 20:40:45 +0200663_iso-8859-1 -----Özgün İleti----- Kimden: O'Conner, John Haber Grupları: bit.listserv.db2-l Kime: DB2-L@RYCI.COM Tarih: 15 Aralık 1999 Çarşamba 21:39 Konu: Re: DB2 5.0 stored procedures and servlets
>We had this problem back in June, and I opened a PMR with IBM. The problem >is passing a string as an INOUT parameter. The workaround for this is to >use one parameter for input and separate parameter for output. I have >included a portion of the interchange I had with IBM. > > > > -IBM -5740XYR00 - 99/06/28-12:01- >I have spoken with John about this problem and the suspected bug. I >have [...]
2608 106 24_Re: DRDA re-bind problem13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 15 Dec 1999 13:23:11 -0600409_US-ASCII DRDA uses packages not plans check sysibm.syspackage. Kurt
>>> "Philip, Sibimon" 12/15/99 12:12PM >>> Craig,
The Disconnect is parameter for plan and the plan name for DRDA seems to be DISTSERV. I am not able to find this plan in Sysibm.sysplan.
Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com [...]
2715 75 32_Re: DB2 Compression on RVA boxes24_Humphris,Richard P.(NXI)24_Richard.Humphris@CNA.COM31_Wed, 15 Dec 1999 13:18:16 -0600609_- I just checked via IBMIN, the software PTF faking the CMPSC instruction was implemented as a PTF to the MVS operating systems (not to DB2).
Therefore, DB2 doesn't have to check the processor for the microcode, it just issues the CMPSC (B263) instruction and trusts that it will be handled via microcode or software. If the microcode exists it runs fast. If it doesn't then a program check (or the equiv) occurs, MVS traps it and runs software instructions to simulate the B263 instruction. Therefore, DB2 (and Omegamon) won't know how the instruction was executed; but Omegamon will measure how [...]
2791 138 42_Re: DB2 5.0 stored procedures and servlets12_Daniel Kirby25_daniel.kirby@CITICORP.COM31_Wed, 15 Dec 1999 15:04:12 -0500524_us-ascii Thanks for the reply. I solved my own problem. I used sample code that had [] around the parameters in the escape clause: CallableStatement CStmt = con.prepareCall("{call STPROC3[?,?]}");. I found another code sample that used () instead and it worked. I guess I must be using the corrected version of JDBC driver since the INOUT parameter in my stored procedure worked fine. It is so hard doing this stufff to know whether the mistake is mine or not. This time it was mine, with an assist from www.utcluj.ro. [...]
2930 109 36_Problem calling Java stoproc from VB12_Raj, Dominic25_DRaj@FALLSCHURCH.ESYS.COM31_Wed, 15 Dec 1999 14:52:21 -0500477_iso-8859-1 I've the following problem in running a Java stored procedure called from a Visual basic client program. The stored procedure was developed using stored procedure builder.
The Java stored procedure is running on a UDB windows NT server. I tested this on the server and it works fine. I called this stoproc. from a reporting tool on a client workstation (crystal reports) and it works fine. Also, l'm able to connect to the database from VB through ODBC. [...]
3040 134 24_Re: DRDA re-bind problem15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Wed, 15 Dec 1999 15:29:06 -0500692_iso-8859-1 When you display thread it shows the plan as DISTSERV. I do not know how it got. I think the thread is active in DB2 because of DDF ZPARM CMTSTAT=ACTIVE.
-DIS thread(*) loc(*) detail shows as follows
>DSNV401I = DISPLAY THREAD REPORT FOLLOWS - >DSNV402I = ACTIVE THREADS - >NAME ST A REQ ID AUTHID PLAN ASID TOKEN >SERVER RA * 7 ws-dal-l3728 L3728 DISTSERV 004A 26342 > V437-WORKSTATION=SYSB, USERID=l3728 , > APPLICATION NAME=ws-dal-l3728_317_l3728 > V445-SEANETA.JBDX.07CF0C0E2646=26342 ACCESSING DATA FOR :JBDX > V447--LOCATION SESSID A ST TIME > V448-- F0000000000017EC W R2 9934814191741 >DISPLAY ACTIVE REPORT COMPLETE >DSN9022I = DSNVDT '-DIS [...]
3175 185 24_Re: DRDA re-bind problem13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 15 Dec 1999 14:50:17 -0600474_US-ASCII Sibimon Philip wrote
1. When you display thread it shows the plan as DISTSERV. I do not know how it got
From the DB2 V5 Admin Guide Classifying DDF Threads
PN The DB2 plan name associated with the DDF server thread. For DB2 private protocol requesters and DB2 DRDA requesters that are at Version 3 or subsequent releases, this is the DB2 plan name of the requesting application. For other DRDA requesters, you would use 'DISTSERV' for PN. [...]
3361 37 64_Re: Recommended Books on Data Modeling in OLAP/ROLAP Environment16_Raffi Garabedian22_hirondel@IX.NETCOM.COM31_Wed, 15 Dec 1999 15:23:28 -0600627_us-ascii David,
I find "The Data Model Resource Book" by Len Silverston, W.H. Inmon, Kent Graziano very resourceful. Just a start.
Enjoy,
Raffi Garabedian
"Mulkey, DW David (7091)" wrote:
> Does anyone know of any books (and/or web sites) that they would recommend > for learning about data modeling in a relational OLAP (ROLAP) environment? > One particular topic of interest is about the design of star schemas. The > material can be DB2 specific or generic for any RDBMS. > > Currently, training courses are not an option. Thus, I'm hoping to learn > more about it on my own. [...]
3399 198 59_Re: Problems with DB2 UDB AIX on RS/6000 HACMP (SP) cluster11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU31_Thu, 16 Dec 1999 09:10:03 +1100305_- Hi Philip, We use WinNT workstations (DB2 v5.2, FP7), and used Control Centre to catalog the AIX databases. Yes, Control Centre returns the IP address of the switch, and uses the name of the switch as the system name. I just change both to use the name for the node (not the switch name) I wanted. [...]
3598 20 32_Re: DB2 Compression on RVA boxes15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Tue, 14 Dec 1999 16:28:31 -0600487_us-ascii So, the question is: does your processor have the ability to execute the CMPSC instruction directly?
VERY good question. I will have to check the CVT bits and see if hardware compression is enabled.
Jim Lewandowski
================================================ 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.
3619 32 27_Re: Total Virtual Pool Size15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Tue, 14 Dec 1999 16:33:12 -0600503_us-ascii Very interesting question.
We have the full 1.6G virtual, and another 1.5G or so if Hiperpool buffers.
I suppose that having a smaller virtual with larger hiperpool will save the CPU of having to have MVS move the virtual buffer (addressable by MVS and mapped by some real storage at that instant) out to expanded storage (only page addressable by MVS) via the hiperpool buffer. In other words, hiperpool buffers have to be moved to a virtual bufferpool page to be accessed. [...]
3652 242 59_FW: Problems with DB2 UDB AIX on RS/6000 HACMP (SP) cluster11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU31_Thu, 16 Dec 1999 10:47:01 +1100566_- Correction for previous command. Sorry about that. db2 CATALOG TCPIP NODE nodename REMOTE nodename SERVER db2cDB2 REMOTE_INSTANCE DB2 SYSTEM nodename OSTYPE AIX Regards, Bruce
> Hi Philip, > We use WinNT workstations (DB2 v5.2, FP7), and used Control Centre to > catalog the AIX databases. Yes, Control Centre returns the IP address of > the switch, and uses the name of the switch as the system name. I just > change both to use the name for the node (not the switch name) I wanted. > > We catalog 200 UDB databases with a script. We found that If we [...]
3895 40 26_Re: Updating System Tables20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 15 Dec 1999 09:26:33 -0500612_us-ascii Harvey
There is a bit in the macro DSN6SPRC which has to be turned on in order to allow catalog update. You have to change the following in the macro and reassemble ZPARM.
&SPRMCTU SETC '1'
HTH Venkat Pillay
> -----Original Message----- > From: Harvey Wachtel [SMTP:HWachtel@UAPC.CUNY.EDU] > Sent: Tuesday, December 14, 1999 7:05 PM > To: DB2-L@RYCI.COM > Subject: Updating System Tables > > Somebody here claims he heard from somebody outside (I think from BMC) > that > there is a system option that allows you to update any column of any > catalog table. Does such a [...]
3936 96 26_Re: Updating System Tables0_29_Greg.Palgrave@BANKWEST.COM.AU31_Thu, 16 Dec 1999 09:10:48 +0800472_us-ascii Harvey,
Good Luck!
Would you be able to post the success (or otherwise) of the method to the list at some stage - with any 'war stories' of course!? Like the cat, I'm just curious....and you never know when you might need to try these things!
Regards,
Greg Palgrave Database Administration Group Information Services, Level 10, Tower Bank of Western Australia eMail : greg.palgrave@bankwest.com.au [...]
4033 129 26_Re: Updating System Tables14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Wed, 15 Dec 1999 20:29:30 -0500327_us-ascii I certainly will report on our experiences.
By the way, for anyone also curious about specific glitches with field procedures, our extensive testing has now uncovered a third: a S0C4 in the bowels of DB2 when using MAX(SEMESTER) in a query that defines a nested table. We've got IBM looking into this one. [...]
4163 111 11_Re: DSNDB0724_Gendron, Dave (Exchange)28_Dave.Gendron@COASTALCORP.COM31_Wed, 15 Dec 1999 20:16:34 -0500420_- I have a question pertaining to DSNDB07 tablespace allocations and how DB2 utilizes the available space.
Currently, I have 4 dasd volumes available for DB07 allocation for a production subsystem dedicated to PeopleSoft financials. When I installed the subsystem, I chose to create four DB07 tablespaces (...DSN4K01, ...DSN4K02, etc.) Each tablespace occupies an entire dasd volume with no extents allowed. [...]
4275 17 11_Re: DSNDB0712_Roger Miller19_millerrl@US.IBM.COM31_Wed, 15 Dec 1999 18:27:34 -0800486_us-ascii The way you've done it is good for physical volumes. If you have two data sets per volume (prior to the ESS and PAV), then we try to issue multiple IOs to a volume and IOS will have one wait. This is less efficient.
Roger Miller
================================================ 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.
4293 135 51_Fw: Reverse-engineer catalog statistics collection?9_jim.szabo22_jim.szabo@MCIWORLD.COM31_Wed, 15 Dec 1999 21:50:15 -0500436_iso-8859-1 This is a repost, as I got no responses the first time.
Environs: DB2/OS 390 V5
We are starting to investigate the use of "enhanced" DB2 catalog statistics to improve access to our DW and other tables. By "enhanced", I mean specifying non-default options for RUNSTATS or the third-party equivalent: TABLE, INDEX, COLUMN, KEYCARD, even use of the DSTATS program and manually inserting data. [...]
4429 148 51_Fw: Reverse-engineer catalog statistics collection?14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Thu, 16 Dec 1999 13:17:00 +1000553_us-ascii
Jim, What some sites (including us) do is to use their Production statistics to update those columns that are updateable in the catalog in other environments. We do this by using a SELECT statement on the various catalog tables to produce UPDATE statements that we then apply to whatever environment. You could use this same technique to produce UPDATE statements PRIOR to performing the change so that POST change you could modify the UPDATE statements as required (missing/new columns) and reapply them after the change. [...]
4578 22 24_Clustering affects sorts0_24_ssethi@LOT.TATASTEEL.COM29_Thu, 16 Dec 1999 04:07:24 GMT337_us-ascii Hi all, I was going through the article of Bonnie Baker in the latest edition of DB2 MAG and found some good ideas and some indepth analysis on ORDER BY . It raised a question in my mind that up to what extent the clustering of an index affects the avoidance of an ORDER BY clause except that LIST PREFETCH will not occur. [...]
4601 41 64_Re: Recommended Books on Data Modeling in OLAP/ROLAP Environment10_Mark Doyle23_markdoyle@EARTHLINK.NET31_Wed, 15 Dec 1999 22:15:00 -0600510_iso-8859-1 When we did a data warehouse methodology project in the pharmaceuticals industry, we used three references extensively:
The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing and Deploying Data Warehouses, Kimball, Ralph; Reeves, Laura: Ross, Margy & Thornthwaite, Warren; New York, John Wiley & Sons, Inc 1998
The Data Warehouse Toolkit: Practical Techniques for Building Dimensional Data Warehouses, Kimball, Ralph; New York, John Wiley & Sons, Inc 1996 [...]
4643 20 48_Impossible to create a database with an old name8_Giuseppe18_dedonno@ARIADNE.IT31_Thu, 16 Dec 1999 09:37:03 +0100317_us-ascii Hi, I have DB2 UDB on a NT machine, when I remove a database then I don't create another database with the same name or alias. I tried to delete the directory C:\Db2\Node0000\Sql0000X, but the problem remains. How can I do? Where is the reference to the databases created? Thank you in advance giuseppe [...]
4664 89 52_Re: Application Change Management for DB2 for OS/3909_Slot J.P.23_J.P.Slot@RF.RABOBANK.NL31_Thu, 16 Dec 1999 09:56:40 +0100357_ISO-8859-1 Iris,
The Endevor solution might work if you are looking at storing individual objects. You will have to think about the size of this objects Database DDL, Tablespace DDL and/or Table and Index DDL. Implementing database changes with Endevor is not used within our shop. CA-Endevor is merely used as a storage for DDL version control [...]
4754 79 40_Re: Installing FP2 on WinNT DB2 V6.1 PDE13_Philip Nelson24_philip@PANDP.SALTIRE.ORG31_Thu, 16 Dec 1999 10:54:02 +0000650_us-ascii Yes, I've tried that - still have WINLOGON.EXE there.
Disabling DB2 startup in this way has the same effect as running DB2STOP and DB2ADMIN STOP and then manually stopping the JDBC services.
Phil
On Wed, 15 Dec 1999 11:49:54 -0500, Rakesh Madan wrote:
>did you set db2 startup in the control panel to manual or disabled and then >rebooted befor trying to install the fix pack?.......needs to be done > >> -----Original Message----- >> From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On >> Behalf Of >> Philip Nelson >> Sent: Wednesday, December 15, 1999 11:11 AM >> To: DB2-L@RYCI.COM >> Subject: [...]
4834 155 11_Re: DSNDB070_23_Mike_Levine@TEKHELP.NET31_Thu, 16 Dec 1999 08:50:30 -0500577_us-ascii Hi,
The amount of space seems fine, but you should consider breaking it up into more tablespaces on separate UCBs (volumes) not just more tablespaces on the same volume. If you have RVA (or similar) DASD you can create lots of UCBs (four characters now!) with less than a full 3390 (3300 CYLS) amount of space. For example, if you have 13,000 CYLs of space available, you can create 13 'volumes' (UCBs) with 1,000 CYLs each. This will reduce enqueue wait times on the logical UCBs. If all you have to work with are 'real' 3390's, you should still consider [...]
4990 44 28_Re: Clustering affects sorts0_23_Mike_Levine@TEKHELP.NET31_Thu, 16 Dec 1999 09:05:52 -0500352_us-ascii Hi,
Although sorts can be avoided by use of an index (clustered or non-clustered) in the correct sequence, you should ALWAYS include an ORDER BY clause if you want the data in a certain order. DB2 could always switch to a different index which may require a sort or return the data in a different order if the ORDER BY is omitted. [...]
5035 122 27_QMF - Determining Index Use0_14_dcreed@CSC.COM31_Thu, 16 Dec 1999 08:25:22 -0600346_us-ascii Depending on release there is a section in the QMF manual "Managing QMF" chapter "Maintaining a DB2 Subsystem". In this section there is an additional section entitled "Determining Index Use".
I tried the following from the manual to see if the system I am looking at is using the index.
"5.10.3 Determining Index Use [...]
5158 17 11_Re: DSNDB070_31_truman.g.brown@BELLATLANTIC.COM31_Thu, 16 Dec 1999 09:28:20 -0500496_us-ascii Dave, you have a pool. You can paint white stripes on the bottom but it will still hold the same amount of water. DB2 will allocate multiple pagesets in each dataset using a LRU algorhythm. Just be glad you don't have any problems.
George
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
5176 161 31_Re: QMF - Determining Index Use20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 16 Dec 1999 09:54:14 -0500656_iso-8859-1 Have you tried running the following query
SELECT BCREATOR, BNAME FROM SYSIBM.SYSPLANDEP WHERE DNAME='QMF330'
without the BTYPE='I' ??? Do you still get something out there ?
> -----Original Message----- > From: dcreed@CSC.COM [SMTP:dcreed@CSC.COM] > Sent: Thursday, December 16, 1999 9:25 AM > To: DB2-L@RYCI.COM > Subject: QMF - Determining Index Use > > Depending on release there is a section in the QMF manual "Managing QMF" > chapter "Maintaining a DB2 Subsystem". In this section there is an > additional > section entitled "Determining Index Use". > > I tried the following from the manual to see if the system [...]
5338 41 32_Re: DSNDB07 (in a perfect world)0_23_Mike_Levine@TEKHELP.NET31_Thu, 16 Dec 1999 09:54:57 -0500464_us-ascii Hi George,
We didn't think we had a problem either. However, an IBM performance study showed that we had 200+ MS wait times on the DSNDB07 datasets (four of them on four dedicated volumes) due to enqueuing on the UCB. IBM said that in a perfect world we should have as many DSNDB07 workfiles on separate UCBs as the tablespace with the most partitions. We settled on ten and the wait times were reduced significantly. Every little bit help... [...]
5380 129 8_00C9020714_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Thu, 16 Dec 1999 15:58:53 +01009_us-ascii
5510 22 32_Re: DSNDB07 (in a perfect world)0_31_truman.g.brown@BELLATLANTIC.COM31_Thu, 16 Dec 1999 10:19:28 -0500343_us-ascii This problem probably (?) derives from the number of active page sets within each 4K dataset. Try to keep the sorts from using the 4K work datasets at all, if possible. This means a BIG dedicated DSNDB07 bufferpool (maybe 50,000 pages); you might try parm values of VPSEQT of 98, DWQT 75, VDWQT 50 , and max SORTPOOL at 64,000K. [...]
5533 19 63_DB2 activity using IBM/Tivoli's PERFORMANCE REPORTER for OS/3900_17_ttaylor@CHUBB.COM31_Thu, 16 Dec 1999 10:34:05 -0500470_us-ascii Hi all
Our capacity and planning group has decided to use Performance Reporter to capture MVS, IMS, CICS, and DB2 info.
Can anyone give me an idea how much overhead I will see with this product.
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.
5553 155 12_Re: 00C902070_18_mebert@AMADEUS.NET31_Thu, 16 Dec 1999 16:32:54 +0100517_us-ascii Hello,
this sounds like a problem we had a while ago. It was caused by an update to a partitioning key that caused the record to move to a different partition. In our case, if I remember correctly, a QMF query worked but SPUFI did not. I guess this is caused by indexed vs. TS Scan access. If this is indeed the same as your problem, there's a PTF for it. I searched my emails, but unfortunately I did not find anything relating to this problem so you have to look in IBMLINK for the PTF Number. [...]
5709 33 52_Re: Impossible to create a database with an old name13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 16 Dec 1999 09:51:44 -0600598_US-ASCII When you remove the database using the control center you simply uncatalog the database, you need to recatalog the database and issue the drop database command if you want to get rid of the database. All the information you need is in the information center. HTH Kurt
>>> Giuseppe 12/16/99 02:37AM >>> Hi, I have DB2 UDB on a NT machine, when I remove a database then I don't create another database with the same name or alias. I tried to delete the directory C:\Db2\Node0000\Sql0000X, but the problem remains. How can I do? Where is the reference to the [...]
5743 43 32_Re: DSNDB07 (in a perfect world)0_23_Mike_Levine@TEKHELP.NET31_Thu, 16 Dec 1999 10:53:55 -0500381_us-ascii Hi George,
For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool with the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We have a 99.9% hit ratio and excellent response times. What would be the rational for changing these parms? (we have a change freeze anyway). Are these general recommendations for the DSNDB07 bufferpool? [...]
5787 53 28_Re: Clustering affects sorts13_Helen Johnson25_Helen_Johnson@RAC.RAY.COM31_Thu, 16 Dec 1999 10:06:09 -0600330_us-ascii I have always told programmers and users to use ORDER BY to be certain of the data order. But what about when the SQL contains a DISTINCT and the columns are listed in the same order as the ORDER BY would be listed. If the EXPLAIN shows only 2 sorts (DISTINCT and ORDER BY), wouldn't the ORDER BY sort be redundant? [...]
5841 24 35_What is the Fastest Unload process?12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Thu, 16 Dec 1999 10:06:49 -0800342_- I am looking for feedback on what users have found to be the fasted unload process. Has anyone found a third party unload that is much faster than IBM's DSNTIAUL? I know that IBM is saying that they are making theirs to be competitive with the vendors products. We need the data in the unload format. Any feedback will be appreciated. [...]
5866 77 28_Re: Clustering affects sorts0_23_Mike_Levine@TEKHELP.NET31_Thu, 16 Dec 1999 11:13:36 -0500617_us-ascii Hi Helen,
If nothing else, you should leave the ORDER BY in for documentation purposes. With a SELECT DISTINCT, the desired order may not be obvious to future programmers who may make modifications to the code.
Regards,
Michael Levine Premier Data Services, Inc.
>I have always told programmers and users to use ORDER BY to be certain of the data order. But what about when the SQL contains a DISTINCT and the columns are listed in the same order as >the ORDER BY would be listed. If the EXPLAIN shows only 2 sorts (DISTINCT and ORDER BY), wouldn't the ORDER [...]
5944 230 31_Re: QMF - Determining Index Use0_14_dcreed@CSC.COM31_Thu, 16 Dec 1999 09:06:36 -0600747_us-ascii Yes I did, And there are no rows for QMF330 in SYSPLANDEP. I am sure that that is the PLAN that is used for QMF in this environment.
Just looking generic, I see the following: ---------+---------+---------+---------+------ SELECT BNAME , BCREATOR , BTYPE , DNAME , IBMREQD FROM SYSIBM.SYSPLANDEP WHERE DNAME LIKE 'QMF%' ---------+---------+---------+---------+------ ---------+---------+---------+---------+------ BNAME BCREATOR BTYPE DNAME IBM ---------+---------+---------+---------+------ DSQTBACK DSQDBCTL R QMFOBJB N QMF_OBJECT_BACKUP Q T QMFOBJB N OBJECT_DATAX Q I QMFOBJB N DSQTSCT3 DSQDBCTL R QMFOBJB N OBJECT_DATA Q T QMFOBJB N OBJECT_REMARKSX Q I QMFOBJB N DSQTSCT2 DSQDBCTL R QMFOBJB N OBJECT_REMARKS Q T QMFOBJB N [...]
6175 91 28_Re: Clustering affects sorts12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 16 Dec 1999 08:14:41 -0800331_us-ascii If we can use one sort for two purposes, we do. The rule is, "If you want the rows in order, you must specify ORDER BY." Otherwise you might be lucky or partially lucky or out of luck.
There is a new DB2 Magazine article by Bonnie Baker, titled "Looking for a Little Order?" It's excellent and I recommend it. [...]
6267 77 39_Re: What is the Fastest Unload process?0_15_sokeefe@VCU.EDU31_Thu, 16 Dec 1999 11:14:16 -0500408_us-ascii Richard, At VCU, we use BMC Unload Plus. We are on OS/390 V2.5, DB2 V4.1. Using IBM supplied utilities, an unload of the SIS+ prod database took 8-10 hours. Using BMC Unload Plus V5.1, I can unload the same data in 35 minutes. (Loading takes < 4 hours using BMC Load Plus, IBM's load took over 18 hours!) I understand that the utilities have been improved in DB2 for OS/390 V5 & V6. Good luck, [...]
6345 67 39_Re: What is the Fastest Unload process?0_18_mebert@AMADEUS.NET31_Thu, 16 Dec 1999 17:22:56 +0100531_us-ascii IBM's REORG UNLOAD EXTERNAL is much much faster if you want to unload an entire tablespace. However, it does allow only row-level SELECTs with simple conditions, and it does not operate at the partition level even when you specify a PART clause (at least when I tested it half a year ago - there may be a fix for that in the meantime). Recently there was also an announcement from IBM about a new FAST UNLOAD; however I did not keep it and so cannot tell you the details. It's probably somewhere on the IBM web site. [...]
6413 86 32_Re: DSNDB07 (in a perfect world)0_31_truman.g.brown@BELLATLANTIC.COM31_Thu, 16 Dec 1999 11:27:35 -0500456_us-ascii Mike:
You don't want ANY I/O if you can help it. SORTPOOL should be large enough to reduce the possibility of "runs" being written to disk which then incurs a merger to get the results set(a "run" is a sort which is too large for the SORTPOOL).
Obviously BP and SORTPOOL size will be relative to the amount of 4K activity you have in the database, and of course I don't know what tools you have to monitor your work dataset BP. [...]
6500 52 39_Re: What is the Fastest Unload process?0_19_mike.holmans@BT.COM31_Thu, 16 Dec 1999 16:27:57 -0000322_- There's an IBM unload which is considerably faster than DSNTIAUL, as long as you're on V5 or above. My experience with REORG UNLOAD EXTERNAL is that it runs about five times faster than DSNTIAUL assuming your output is to disk, and uses 30% less CPU.
Mike Holmans BT ISE Technical Design mike.holmans@bt.com [...]
6553 57 39_Re: What is the Fastest Unload process?11_Jim Knisley27_Jim_Knisley@FORETHOUGHT.COM31_Thu, 16 Dec 1999 11:22:50 -0500556_us-ascii Richard, We have been using BMC's Unload Plus for several years and have had no major problems. It will unload into a variety of formats, including the standard IBM unload format. It has a lot of features and functionality built into the product. Our benchmarking has found it to be 5 - 8 times faster that DSNTIAUL against the same table. I have read that IBM is improving the performance of the unload utility in Version 6, but I have not had a chance to test it yet. BMC (and most other third party vendors) achieves their speed by native [...]
6611 52 39_Re: What is the Fastest Unload process?14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 16 Dec 1999 11:35:17 -0500607_iso-8859-1 If you are at V5 at a certain level, you can try REORG...UNLOAD EXTERNAL. I've found it to be significantly faster than DSNTIAUL....about 50% less CPU and elapsed for the tests that I ran.
And its free!
Manas.
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Richard Pack > Sent: Thursday, December 16, 1999 1:07 PM > To: DB2-L@RYCI.COM > Subject: What is the Fastest Unload process? > > > I am looking for feedback on what users have found to be the fasted unload > process. Has anyone found a third party unload [...]
6664 76 25_Re: REXX Language Support14_O'Conner, John26_john.oconner@COURTS.WA.GOV31_Thu, 16 Dec 1999 08:35:03 -0800482_iso-8859-1 We just installed DB2 Rexx Language Support, and we would like to try it out. Is there documentation available? We must be looking in the wrong places because we are unable to find any.
Any help would be greatly appreciated.
John O'Conner John.OConner@courts.wa.gov
-----Original Message----- From: Scarcella, Gary V [mailto:ScarcellaGV@AETNA.COM] Sent: Wednesday, December 15, 1999 3:27 AM To: DB2-L@RYCI.COM Subject: Re: REXX Language Support [...]
6741 96 39_Re: What is the Fastest Unload process?14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 16 Dec 1999 11:45:02 -0500620_iso-8859-1 True, there is (or was) a problem with PART option in that the entire tablespace is unloaded even if you specify PART n. But if you also specified the SORTDATA option then it would unload by partition.
Manas.
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > mebert@AMADEUS.NET > Sent: Thursday, December 16, 1999 11:23 AM > To: DB2-L@RYCI.COM > Subject: Re: What is the Fastest Unload process? > > > IBM's REORG UNLOAD EXTERNAL is much much faster if you want to > unload an entire > tablespace. However, it does allow only row-level [...]
6838 101 25_Re: REXX Language Support14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 16 Dec 1999 11:46:40 -0500709_iso-8859-1 Try ftp://ftp.software.ibm.com/software/os390/db2server/books/REXXV5.PDF
Manas.
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > O'Conner, John > Sent: Thursday, December 16, 1999 11:35 AM > To: DB2-L@RYCI.COM > Subject: Re: REXX Language Support > > > We just installed DB2 Rexx Language Support, and we would like to try it > out. Is there documentation available? We must be looking in the wrong > places because we are unable to find any. > > Any help would be greatly appreciated. > > John O'Conner > John.OConner@courts.wa.gov > > -----Original Message----- > From: Scarcella, Gary V [mailto:ScarcellaGV@AETNA.COM] > [...]
6940 52 39_Re: What is the Fastest Unload process?12_craig patton21_prgpatton@HOTMAIL.COM29_Thu, 16 Dec 1999 08:43:02 PST438_- I have used both products from BMC and CA/PLAT and with large volumes of data, both are WAY faster than DSNTIAUL, especially for full unloads. When using a where predicate, they are slower, but still faster than DSNTIAUL. For small numbers of rows (less than millions) the differences are not worth the purchase price. But for handling 10s of millions and more, there is NO comparison and the products start to pay for themselves. [...]
6993 49 28_Re: Clustering affects sorts12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 16 Dec 1999 08:44:46 -0800524_us-ascii The degree of clustering affects the costs for access, but if we are using an index for access, it is ordered by those columns.
I enjoyed Bonnie's article too. http://www.db2mag.com/winter99/prog.shtml
Roger Miller
ssethi@LOT.TATASTEEL.COM@RYCI.COM> on 12/15/99 08:07:24 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Subject: Clustering affects sorts [...]
7043 105 25_Re: REXX Language Support12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 16 Dec 1999 08:46:37 -0800657_us-ascii There is a pdf you can download - see under news on the DB2 for OS/390 home page www.ibm.com/software/db2os390
Roger Miller
"O'Conner, John" @RYCI.COM> on 12/16/99 08:35:03 AM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Subject: Re: REXX Language Support
We just installed DB2 Rexx Language Support, and we would like to try it out. Is there documentation available? We must be looking in the wrong places because we are unable to find any. [...]
7149 53 30_reorg w/copyddn and it abends?14_Jessen Michael29_JessenMichel@JDCORP.DEERE.COM31_Thu, 16 Dec 1999 10:46:43 -0600538_- We're on DB2 Version 5.1 (OS390).
I'm looking into using the COPYDDN option of the REORG.
I've looked thru the utility guide, but I'm not finding much information on the following (and I was hoping someone out there has already done some research into this):
1. Is this faster than running a copy after the reorg (My 1st guess would be yes, but I was wondering if anyone has tested this - and whether it's better for certain type of tables (partitioned, very large, etc. - or if it matters on the table size)? [...]
7203 73 39_Re: What is the Fastest Unload process?12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 16 Dec 1999 08:58:03 -0800346_us-ascii Who wins the race? It depends upon the race track. There are many different kinds of unloads. I'll describe a couple of options and what we have from IBM. I'm sure that several vendors will be pointing out their advantages.
DSNTIAUL does pretty well where the optimizer chooses a good path and you return a few hundred rows. [...]
7277 66 39_Re: What is the Fastest Unload process?0_18_damcon2@US.IBM.COM31_Thu, 16 Dec 1999 11:52:37 -0500581_us-ascii Richard,
Haven't used this myself, but take a look at url http://www-4.ibm.com/software/data/dbtools/db2unload/. This is IBM's new High Performance Unload.
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 -----------------------------------------------------
Richard Pack @RYCI.COM> on 12/16/99 01:06:49 PM [...]
7344 48 39_Re: What is the Fastest Unload process?0_23_Mike_Levine@TEKHELP.NET31_Thu, 16 Dec 1999 12:15:01 -0500370_us-ascii Hi,
Several years ago we gave up the 3rd party fast utilities. The way we accomplished the same work in the same time frame was to run more concurrent jobs. So instead of running five OEM unloads, reorgs, etc., that took two hours total we now run ten concurrent IBM utility jobs that run in the same window. Divide and conquer and save some dough! [...]
7393 129 34_Re: reorg w/copyddn and it abends?0_18_mebert@AMADEUS.NET31_Thu, 16 Dec 1999 18:34:30 +0100536_us-ascii 1. I have not tested it either, but I would think that you gain a little time. Not much, because an Imagecopy is much faster than a REORG anyway. The main advantage would be that you get a SHRLEVEL REFERENCE IC and may not need a subsequent QUIESCE (all our developers always do a QUIESCE after an IC for what that is worth). 2. This depends on the Imagecopy DD disposition. If you use (MOD,CATLG,CATLG), you do not need to change JCL if you have to restart. I think the entry in SYSCOPY depends on whether or not you use [...]
7523 19 25_Re: REXX Language Support13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 16 Dec 1999 12:42:26 -0500471_iso-8859-1 >Is there documentation available?
Look in ftp://ftp.software.ibm.com/software/os390/db2server/books/ for REXXV5.PDF
David Seibert Compuware Corporation File-AID product planner Dave_Seibert@compuware.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.
7543 103 32_Re: DSNDB07 (in a perfect world)12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Thu, 16 Dec 1999 20:02:55 +0200299_x-user-defined Hi, Lately I did some measurements in our system and changed the BP7 as follows: 60MB DWQT 90% VDWQT 90% VPSEQT 95% work files are spread over 6 disks on 2 separate boxes (HDS 7700). It minimized greatly the physical I/O to the workfiles and it occurs only on some batch nightly runs.
7647 56 68_Re: DB2 activity using IBM/Tivoli's PERFORMANCE REPORTER for OS/3 909_Wu, James13_jwu@KRAFT.COM31_Thu, 16 Dec 1999 11:12:05 -0600496_- I have been using Performance Reporter Since Version 1.1 (now we are at V1.4) and I love the product. Because it is built on DB2/SQL/QMF, it can be opened to all users who know SQL.
The CPU overhead on DB2 and MVS is not a big concern because 1). most of the CPU usage is from the batch collection and DB2 maintenance jobs, 2). usually there are limited number of on-line users during the primary shift and 3). we can set up QMF resource limits to control the ad-hoc queries/users. [...]
7704 44 67_Re: DB2 activity using IBM/Tivoli's PERFORMANCE REPORTER for OS/39012_Isaac Yassin23_yassin@NETVISION.NET.IL31_Thu, 16 Dec 1999 20:12:20 +0200356_x-user-defined Hi,
We used an older version about 2 years ago. Was horrible - slow, ugly, unfriendly and did a lot of processing for nothing. Reports were ok. , but the way to get them ..... I especially loved the option to "open" a code value table on each field - it always caused TS scan ... The only way out was to cancel your TSO session. [...]
7749 21 31_Re: QMF - Determining Index Use13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 16 Dec 1999 13:15:20 -0500385_iso-8859-1 You should be able to find the information you want by looking in SYSPACKDEP.
In SYSPACKDEP, Dname contains a package name. At my installation, the packages in the QMF plan all begin with DSQ. so change the query to go after SYSPACKDEP and look for DNAME like 'DSQ%'
David Seibert Compuware Corporation File-AID product planner Dave_Seibert@compuware.com [...]
7771 126 39_Re: What is the Fastest Unload process?12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 16 Dec 1999 10:23:06 -0800427_us-ascii I would agree that REORG EXTERNAL is a very good option PROVIDED you don't have a lot of tables in the tablespace and don't want just one table out of all of them. If you have a very large number of tables and some of them are pretty large (granted this is not a good condition but is common with Peoplesoft and some other products), it can be pretty combersome with REORG EXTERNAL to get the data that you want. [...]
7898 97 34_Re: reorg w/copyddn and it abends?12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 16 Dec 1999 10:33:20 -0800369_us-ascii 1) Its faster but the savings depend upon the size of the tablespace being copied. Obviously a small tablespace won't have much savings whereas an extremely large tablespace could save 1+hours.
2) In my runs, when the reorg abends, the copy is cataloged and put into syscopy. This may or may not be a problem. Depends on how you have things setup. [...]
7996 36 71_Questions regarding userids for those connecting from mainframe t o UDB13_Lynne Flatley17_LFlatley@NEFN.COM31_Thu, 16 Dec 1999 13:42:35 -0500453_us-ascii Our shop is beginning to get into connecting to UDB and DataJoiner (NT) databases from OS/390. The last place I was at we were doing the same thing.
My question is, when going from the mainframe DB2 to the server UDB, do other shops map/translate all outgoing IDs to a single one (via the SYSIBM.USERNAMES) (what we've started doing here) or adding the mainframe IDs to the server security facility (what we did at the last place)? [...]
8033 29 5_Query14_subrata mondal25_subratamondal@HOTMAIL.COM29_Thu, 16 Dec 1999 18:51:55 GMT426_- Dear Db2 Gurus...
I was thinking the capabilities of the databases that we might have soon. we have UDb which can accept images or movies or resumes .. etc. Is there any technique to retrieve these images interactively.? ( Other than giving out the select statements )
Eg : Lets say there are 5 resumes with snaps of them . Interactively can it be retrieved - something like Using QMF or some other tool. [...]
8063 65 32_Re: DSNDB07 (in a perfect world)14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Thu, 16 Dec 1999 14:18:19 -0500421_ISO-8859-1 Message text written by DB2 Data Base Discussion List >For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool with the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We have a 99.9% hit ratio and excellent response times. What would be the rational for changing these parms? (we have a change freeze anyway). Are these general recommendations for the DSNDB07 bufferpool?< [...]
8129 75 31_Re: QMF - Determining Index Use0_14_dcreed@CSC.COM31_Thu, 16 Dec 1999 13:26:17 -0600388_us-ascii Hi Dave, Sorry to be so dense(wouldn't be the first time), but the query
QMFV3R3 (Managing QMF)
> SELECT BCREATOR, BNAME > FROM SYSIBM.SYSPLANDEP > WHERE DNAME='QMF330' > AND BTYPE='I'
QMFV6 (Installing and Managing QMF on OS/390) > SELECT BCREATOR, BNAME > FROM SYSIBM.SYSPLANDEP > WHERE DNAME='QMF610' > AND BTYPE='I'
come from the IBM manual. [...]
8205 16 17_RID pool analysis0_17_ttaylor@CHUBB.COM31_Thu, 16 Dec 1999 14:42:36 -0500321_us-ascii Hi all
Can some one clearly define 'QW0125TH'
Thanks 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.
8222 32 21_Re: RID pool analysis20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Thu, 16 Dec 1999 14:44:08 -0500641_- This is threshold value stored at the time of bind for static sqls. Assume 20000 rows table and list-prefetch has already got done 5000 qualifying RIDs. This 5000 is threshold when RID access would fail. (min of 4k rids). This is 25% limit for rid failure
> -----Original Message----- > From: ttaylor@CHUBB.COM [SMTP:ttaylor@CHUBB.COM] > Sent: Thursday, December 16, 1999 2:43 PM > To: DB2-L@RYCI.COM > Subject: RID pool analysis > > Hi all > > Can some one clearly define 'QW0125TH' > > Thanks Tom > > ================================================ > To change your subscription options or to cancel your subscription visit [...]
8255 15 39_Re: What is the Fastest Unload process?14_Elaine Morelli19_morellie@US.IBM.COM31_Thu, 16 Dec 1999 14:47:04 -0500525_us-ascii DSNTIAUL is not an unload utility. It is a sample program that everyone uses to do unloads. IBM REORG UNLOAD EXTERNAL is a faster method of unloading using the IBM base utilities. IBM also markets a DB2 High Performance Unload utility that is an additionally priced product.
================================================ 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.
8271 121 32_Re: DSNDB07 (in a perfect world)0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 16 Dec 1999 14:06:02 -0600420_us-ascii Just out of curiosity, do you know what the old values were? (I know they don't relate much to any one else's system but ...)
Isaac Yassin on 99/12/16 12:02:55 PM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: Rohn Solecki/MTSCommunications/MTS) Subject: Re: DSNDB07 (in a perfect world) [...]
8393 16 40_Invalid select procedure and maintenance0_17_ttaylor@CHUBB.COM31_Thu, 16 Dec 1999 15:21:45 -0500322_us-ascii Hi all
Can someone explain QISTCOLS. and or ifcid 224
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.
8410 38 39_Re: What is the Fastest Unload process?0_19_Tim.Lowe@STPAUL.COM31_Thu, 16 Dec 1999 14:26:09 -0600329_us-ascii Richard, The primary problem that I have had with using REORG UNLOAD EXTERNAL is the authorization required. You have to have REORG authorization on the database in order to unload a table with this. I would rather not grant someone reorg authority on a database to unload a table, so this does have a limited use. [...]
8449 69 68_Re: DB2 activity using IBM/Tivoli's PERFORMANCE REPORTER for OS/3 909_Wu, James13_jwu@KRAFT.COM31_Thu, 16 Dec 1999 14:29:19 -0600650_- The key is not to use all Tivoli supplied DDL for tablespaces and indexes. With some customization, the product works fine in our shop.
James Wu :-)
(847)646-5548 jwu@kraft.com
> -----Original Message----- > From: Isaac Yassin [SMTP:yassin%NETVISION.NET.IL@internet.kraft.com] > Sent: Thursday, December 16, 1999 12:12 PM > To: DB2-L%ryci.com@internet.kraft.com > Subject: Re: DB2 activity using IBM/Tivoli's PERFORMANCE REPORTER for > OS/390 > > Hi, > > We used an older version about 2 years ago. Was horrible - slow, ugly, > unfriendly and did a lot of processing for nothing. Reports were ok. , but > the > way to get [...]
8519 48 44_Re: Invalid select procedure and maintenance0_23_pjackson@SUMMITBANK.COM31_Thu, 16 Dec 1999 15:29:44 -0500484_us-ascii I found this when we first went to Version 5
5.3.3.5 Considerations for Rebinding Certain Plans and Packages
SQL queries in applications that are bound in DB2 Version 3 or later releases, automatically undergo an internal optimization that could improve their performance, depending on the number of rows fetched. The SQL statements most likely to show a decrease in elapsed time are those that select a large number of columns and fetch hundreds of rows. [...]
8568 34 31_Re: QMF - Determining Index Use13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 16 Dec 1999 15:52:19 -0500573_iso-8859-1 Hi Danny, No I'm certainly not suggesting any error on your part. I'm just pointing out that since I also couldn't find any Plandep information for the QMF plan, I looked in Packdep and found them all.
I'm suggesting that for your shop and mine, the manual is incorrect. It would be correct if the QMF DBRMs were bound directly into a plan rather than into packages and then into the plan. Dependency data is stored in SYSPLANDEP for plans made of DBRMS. For packages, that dependency data is found in SYSPACKDEP. I suspect that it's an old piece of [...]
8603 89 44_Re: Invalid select procedure and maintenance0_17_ttaylor@CHUBB.COM31_Thu, 16 Dec 1999 15:55:09 -0500924_us-ascii Thanks
pjackson@SUMMITBANK.COM on 12/16/99 03:29:44 PM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM
cc: (bcc: Thomas G Taylor/ChubbMail)
Subject: Re: Invalid select procedure and maintenance
I found this when we first went to Version 5
5.3.3.5 Considerations for Rebinding Certain Plans and Packages
SQL queries in applications that are bound in DB2 Version 3 or later releases, automatically undergo an internal optimization that could improve their performance, depending on the number of rows fetched. The SQL statements most likely to show a decrease in elapsed time are those that select a large number of columns and [...]
8693 141 40_Re: Problem calling Java stoproc from VB12_Raj, Dominic25_DRaj@FALLSCHURCH.ESYS.COM31_Thu, 16 Dec 1999 17:01:16 -0500591_iso-8859-1 I haven't received any reply on this problem.
Please reply if you are aware of this problem.
> -----Original Message----- > From: Raj, Dominic > Sent: Wednesday, December 15, 1999 2:52 PM > To: 'DB2-L@RYCI.COM' > Subject: Problem calling Java stoproc from VB > > I've the following problem in running a Java stored procedure called from > a Visual basic client program. > The stored procedure was developed using stored procedure builder. > > The Java stored procedure is running on a UDB windows NT server. I tested > this on the server and it works fine. > I [...]
8835 64 31_Re: QMF - Determining Index Use0_14_dcreed@CSC.COM31_Thu, 16 Dec 1999 16:15:07 -0600394_us-ascii Thanks Dave, That makes sense. I guess I will annoy IBM to see if they have any document updates to show which packages should be checked and/or rebound. I could probably look for those that contain the ts DSQDBCTL DSQTSCT3 and have the tb Q.OBJECT_DATA .
Again thanks Danny
Dave_Seibert@compuware.com on 12/16/99 02:52:19 PM [...]
8900 73 40_Re: Problem calling Java stoproc from VB14_Steven Camitta30_steven.camitta@INGRAMMICRO.COM31_Thu, 16 Dec 1999 15:40:32 -0800511_iso-8859-1 Raj, Don't have a good answer but in situations like this I would turn on the ODBC trace and see if anything unusual comes up. I keep the following coded in my DB2CLI.INI:
; Comment lines start with a semi-colon. [COMMON] ;TRACE=1 TRACEPATHNAME=C:\DB2TRACE TRACEFLUSH=1 TRACECOMM=1
Then when I want to turn the trace on, just uncomment the ; Your output will appear in the DB2TRACE directory. I've used this a couple of times to get better insight an interface problem. HTH, Steve [...]
8974 141 40_Re: Installing FP2 on WinNT DB2 V6.1 PDE13_Philip Nelson24_philip@PANDP.SALTIRE.ORG31_Fri, 17 Dec 1999 00:34:23 +0000652_us-ascii Got it in one.
Installed successfully now.
Looks like IBM needs to do some work on the installation instructions for fixpacks !!!
Phil
On Thu, 16 Dec 1999 07:44:17 -0600, dcreed@csc.com wrote:
> > >Try this.. > >Issue the following from a MSDOS prompt > >db2perfi /u > >then reboot. > >Followed by your normal stops. Could be the same thing I hit for DB2 Connect >for NT trying to put on maint. > >Regards >Danny > > > > >philip@pandp.saltire.org on 12/16/99 04:54:02 AM > >Please respond to philip@pandp.saltire.org > >To: DB2-L@ryci.com >cc: (bcc: Daniel S Creed/HI/CSC) >Subject: Re: [...]
9116 51 47_Correct CCSID to convert codes set between DB2s10_Michale Yu19_yubin@MAIL.DCAC.COM31_Mon, 20 Dec 1999 08:35:35 +0800394_iso-8859-1 Hello list:
I am trying to connect with DB2 for OS 390 with DB2 CONNECT ENTERPRISE EDITION running on platform NT. The probelm is :
I have connected to DB2 for OS 390 successfully through SNA(VTAM), but when I tried to execute a SQL statement to retrieve data from a table in DB2 for OS 390 from COMMAND CENTER of DB2 CONNECT on NT, I got error message code -332. [...]
9168 83 25_Re: REXX Language Support15_Shaun Z Lombard36_Shaun.Z.Lombard@TRANSPORT.QLD.GOV.AU31_Fri, 17 Dec 1999 10:49:04 +1000450_us-ascii Thanks for the reply Gary. We will be watching that APAR closely. I have written other programs using the interface which worked fine, but for some reason I keep getting a -188 on the DESCRIBE TABLE statement in the DRAW program. Any ideas.
Thanks
Shaun
"Scarcella, Gary V" on 15/12/99 21:26:32
Please respond to DB2 Data Base Discussion List [...]
9252 19 21_Unicenter TNG for DB29_SrinivasG17_SRINIVASG@INF.COM31_Fri, 17 Dec 1999 08:10:09 +0530477_iso-8859-1 Hi, I would like to know if anyone has implemented Unicenter TNG (CA's Enterprise Management Solution in competition with IBMs TIVOLI) for DB2 (OS/390). Any issues, performance etc.
Thanks & Regards,
Srinivas G
================================================ 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.
9272 18 39_Re: What is the Fastest Unload process?17_suthep thampranee18_suthepth@SCB.CO.TH31_Fri, 17 Dec 1999 11:14:13 -0000435_ISO-8859-1 Dear DB2 Specialists, In my opinion IBM REORG UNLOAD EXTERNAL could not unload with conditions for example,unload table owner.name where name like '?%' but DSNTIAUL could be done JOE
================================================ 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.
9291 112 28_Re: Clustering affects sorts0_24_ssethi@LOT.TATASTEEL.COM29_Fri, 17 Dec 1999 04:36:38 GMT542_us-ascii Hi all, Will DB2 EXPLAIN really show 2 sorts(DISTINCT & ORDER BY).Is not DB2 smart enough to understand that sorting is already done(FOR DISTINCT) ?.Actually my question was not about not coding the ORDER BY clause ,It was about how clustering affects DB2 not to explicitly sort even if the ORDER BY clause is coded and columns that are included in the ORDER BY clause is the part of the index that is used and it is in the same order. As i understand Indexes are always in order and if it is used for the access and columns in [...]
9404 77 47_Select from table with different magic numbers.14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Fri, 17 Dec 1999 15:32:41 +1000570_us-ascii Folks, I just thought I'd communicate a couple of things that recently changed my understanding of a couple of DB2 concepts. It may be that I'm the only one surprised by them, and if so then I hope I can get a couple of explanatory comments.
Situation was that there were 2 'temporary' development environments, say DEV1 and DEV2. The DEV1 environment had been completely backed up using a DFDSS DUMP to tape at some earlier stage. I was then requested to retrieve the data from this for 6 tables and use that data to populate those tables in DEV2. [...]
9482 152 51_Re: Select from table with different magic numbers.13_MARTIN, Keith33_Keith.MARTIN@SUNCORPMETWAY.COM.AU31_Fri, 17 Dec 1999 15:48:48 +1000337_iso-8859-1 Paul,
1. DB2 worked as designed. DB2 would have looked for spacemap pages with the new OBID, found none and returned no rows. (if anyone wants to put a more technical explanation out there please do). I have assumed that you had not done a reorg and a modify catalog to remove all image copies prior to the reorg. [...]
9635 190 51_Re: Select from table with different magic numbers.14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Fri, 17 Dec 1999 16:05:56 +1000541_us-ascii Keith, Thanks for your reply. I like your idea about the SELECT from the table. The only problem I have with it (and I should have stated this more clearly in the original post) is that ALL THREE magic numbers were different. The database had been dropped and recreated. The DBID, PSID and OBID were ALL different. I would have expected DB2 to detect this. Maybe it just uses the DBD to 'overlay' and looks for OBID values related to that table. If this is the case then I accept your explanation. The tablespace IS segmented. [...]
9826 20 14_Paging Cursors8_madhavan16_madhavan@INF.COM31_Fri, 17 Dec 1999 10:46:39 +0530290_- We need to build many programs with paging cursor logic.
We are informed that using AND NOT logic is better than OR logic. Though we would prefer to use OR logic for ease of understanding. Is there performance or any other benefits in using AND NOT logic instead of OR logic? [...]
9847 135 32_Re: DSNDB07 (in a perfect world)12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Fri, 17 Dec 1999 08:28:34 +0200613_x-user-defined Hi, My mistake - should have mentioned that. 60MB (15000 - pages) no change here (prev. round it was 40MB). DWQT 65% VDWQT 60% VPSEQT 99%.
Rohn.Solecki@MTS.MB.CA wrote: > > Just out of curiosity, do you know what the old values were? (I know they don't relate much to any one else's system but ...) > > Isaac Yassin on 99/12/16 12:02:55 PM > > Please respond to DB2 Data Base Discussion List > > To: DB2-L@RYCI.COM > cc: (bcc: Rohn Solecki/MTSCommunications/MTS) > Subject: Re: DSNDB07 (in a perfect world) > > Hi, > Lately I did some [...]
9983 35 39_Re: What is the Fastest Unload process?12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Fri, 17 Dec 1999 08:36:33 +0200571_x-user-defined Hi, Please don't forget that DSNTIAUL is using SQL to access data. It's older versions had an incorrect way to calculate blocksize for the output file so it needed many I/O's to do it which prolonged the run (i used to manually fix the assembler code to make it run much faster just by changing the blocksize calculations) - nowadays the calculation are ok. 3rd party products don't use SQL to read the data but access the VSAM CI by themselves (and then there's the need to flush buffers from memory to get latest data changes and handle all changes [...]
10019 183 51_Re: Select from table with different magic numbers.14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Fri, 17 Dec 1999 16:58:49 +1000474_us-ascii Keith, As you suggested in (2.) I went back and did a DSN1PRNT of the spacemap page and DID find a segment with a listing for the OBID of 155 that was in the CATALOG. I don't understand how this got there. There was no update performed to this table. There were no data rows associated with it...but it was there, and maybe DSN1COPY insists on a conversion for it. Could you explain this mechanism ? I'm stumped, but would be extremely grateful for an answer. [...]
10203 54 18_Re: Paging Cursors14_Purcell, Terry37_Terry.Purcell@CORPMAIL.TELSTRA.COM.AU31_Fri, 17 Dec 1999 19:58:14 +1100593_- Madhavan,
Apart from being "visually" less complex, the performance benefit can be that the AND NOT logic will use a single access path (if available) because the AND NOT predicates are not indexable. The OR logic can make use of a multiple index access path.
Note I said "can be", because you will need to test it for yourself as to which performs better for your situation: either the single index access path, whereby you have decreased your indexable predicates (now stage 1 or 2), or the multi-index access, involving an additional index access and RID sort (if > 32 [...]
10258 114 25_Re: REXX Language Support17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM31_Fri, 17 Dec 1999 07:00:24 -0500417_- The problems I was having were resulting in SQLCODE = -104 and -105 (which are supposed to be resolved with the APAR).
One possible reason for the -188, might be that some string is not enclosed in single quotes when it gets to DB2. To get a REXX variable set to 'ABC' you need to set variable = "'"ABC"'" (that's ABC) [...]
10373 64 32_Re: DSNDB07 (in a perfect world)0_23_Mike_Levine@TEKHELP.NET31_Fri, 17 Dec 1999 08:21:19 -0500721_us-ascii Hi,
I will try VPSEQT=95, DWQT=80 and VDWQT=80. We will also increase the sortpool by 10,000K after the freeze.
Thanks for all your inputs!
Regards,
Michael Levine Premier Data Services, Inc.
>Message text written by DB2 Data Base Discussion List >>For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool >with >the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We >have a 99.9% hit ratio and excellent response times. What would be the >rational for changing these parms? (we have a change freeze anyway). Are >these general recommendations for the DSNDB07 bufferpool?< > >++++++++++++++++++++++++ >VPSEQT should be 95-98%. VDWQT [...]
10438 121 13_PAGING CURSOR8_madhavan16_madhavan@INF.COM31_Fri, 17 Dec 1999 19:39:21 +0530307_- Madhavan, The reason we use AND NOT logic is that it will match on an extra column. The following is an example of the SQL:
CREATE TABLESPACE AHS00005 IN AHDB0001 USING STOGROUP G461524 PRIQTY 40 SECQTY 40 ERASE NO FREEPAGE 0 PCTFREE 0 BUFFERPOOL BP0 LOCKSIZE ANY SEGSIZE 4 CLOSE YES; COMMIT; [...]
10560 187 17_Re: PAGING CURSOR14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sun, 18 Dec 1994 02:52:00 +1000468_us-ascii Terry was correct in saving that AND NOT logic has special benefit in preventing Multi-index access although NOT logic is difficult to understand. There is another alternative.
Suppose our initial SQL is as follows (no matching cols without Multi-index):
SELECT C1, C2, C3, C4 FROM tables WHERE ((C1 >:h1) OR (C1 =:h1 AND C2 >:h2) OR (C1 =:h1 AND C2 =:h2 AND C3 >:h3) OR (C1 =:h1 AND C2 =:h2 AND C3 =:h3 AND C4 >:h4) ) ORDER BY C1, C2, C3; [...]
10748 109 28_Re: Clustering affects sorts14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sun, 18 Dec 1994 02:52:10 +1000359_us-ascii Helen,
DB2 could potentially do any sort it felt like to eliminate duplicates, but it just so happens that it sorts on columns in sequence specified, ascending on all of them.
Note however that non-correlated IN subqueries sort for uniqueness into descending sequence and only 1 sort used despite 2 sort indicators in the Explain. [...]
10858 41 21_Multiple Index Access14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sun, 18 Dec 1994 03:00:02 +1000395_us-ascii Brian,
Very simply, the optimizer is not as smart as you are in this situation. Its intellegence is limited by need to be fast at BIND time. Use OPTIMIZE FOR n ROWS to discourage Multi-index processing, or redesign your indexes. Note however if 1st index returns <= 32 RIDs, 2nd index will be skipped, for Intersection type MI processing. This avoids the wastage sometimes. [...]
10900 217 51_Re: Select from table with different magic numbers.11_Ball, Linda18_Linda_Ball@BMC.COM31_Fri, 17 Dec 1999 10:38:00 -0600550_ISO-8859-1 I'm a little lost about all the steps you took here, but this fact may help you: when a table is created in a segmented table space, the first anchor segment is reserved. Could that be how you got the segment in your space map?
Opinions are, of course, my own and not BMC Software's official position! -------------------------------------------- Linda Ball DB2 Corporate Architect OS/390 Product Development Direct Phone: (512) 340-6722 Fax: (512) 340-6646 Toll Free: (800) 841-2031 linda_ball@bmc.com [...]
11118 79 39_Re: What is the Fastest Unload process?14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Fri, 17 Dec 1999 10:51:15 -0600557_ISO-8859-1 There has been a lot of discussion the past couple of days on UNLOAD speed. I just wanted to drop in my two cents worth to say that speed, though probably the single most important feature of an UNLOAD utility, is only one of many factors that should be considered. The goal of UNLOAD, as I see it, is to move consistent data from DB2 tables into flat files as non-disruptively to your overall environment as possible. To accomplish this, some things to look for (in addition to speed) include: * the ability to unload from an image copy or [...]
11198 114 68_Re: DB2 activity using IBM/Tivoli's PERFORMANCE REPORTER for OS/3 900_17_ttaylor@CHUBB.COM31_Fri, 17 Dec 1999 12:27:27 -0500394_us-ascii James Using the ispf screens it indicates that there is a DB2 component, but I can't find any DB2 component listed at either IBM or Tivoli web sites. What's up with this. I also have no doc
Tom
"Wu, James" on 12/16/99 03:29:19 PM
Please respond to DB2 Data Base Discussion List [...]
11313 130 28_Re: Clustering affects sorts13_Chie, Jim (C)17_chiej@CONNEXT.COM31_Fri, 17 Dec 1999 10:32:37 -0800397_iso-8859-1 I wonder is there a real cost to the second sort, or does DB2 simply call the sort routine which says "I'm already sorted so leave me alone", but it still shows as a sort in the explain table.
-----Original Message----- From: Michael Hannan [mailto:mhannan@C031.AONE.NET.AU] Sent: Saturday, December 17, 1994 8:52 AM To: DB2-L@RYCI.COM Subject: Re: Clustering affects sorts [...]
11444 147 68_Re: DB2 activity using IBM/Tivoli's PERFORMANCE REPORTER for OS/3 909_Wu, James13_jwu@KRAFT.COM31_Fri, 17 Dec 1999 12:57:34 -0600686_- Tom,
Tivoli's Website only mentions that the product needs DB2 V3 and up. For more information, you have to obtain the manuals, such as Administration Guide, from IBM/Tivoli.
James Wu :-)
> -----Original Message----- > From: ttaylor%CHUBB.COM@internet.kraft.com > [SMTP:ttaylor%CHUBB.COM@internet.kraft.com] > Sent: Friday, December 17, 1999 11:27 AM > To: DB2-L%ryci.com@internet.kraft.com > Subject: Re: DB2 activity using IBM/Tivoli's PERFORMANCE REPORTER for > OS/3 90 > > James > Using the ispf screens it indicates that there is a DB2 component, but I > can't > find any DB2 component listed at either IBM or Tivoli web sites. > What's up with [...]
11592 32 24_DB2 Maintenance Question15_Michael Bancale21_mbancale@TXFB-INS.COM31_Fri, 17 Dec 1999 13:22:02 -0600371_iso-8859-1 Since we began with DB2 we have been putting ESO Maintenance on the system prior to migrating to a new release of DB2. I know this is not a good thing to wait so long between maint but we did it. Recently we applied ESO Maintenance to DB2 for put level 9907. Once this maintenance was applied we pointed DB2 and the utilities to the new SDSNLOAD library. [...]
11625 18 33_DB2 5.0 stored procedures and CLI12_Daniel Kirby25_daniel.kirby@CITICORP.COM31_Fri, 17 Dec 1999 14:22:27 -0800421_- Does anyone have any experience calling a DB2 5.0 OS390 stored procedure through CLI (via a java servlet)? I am getting an SQL return code -805. The server containing the servlet is local and the manual seems to say that we must bind the stored procedure package with the plan DSNACLI. We have tried to bind the package with one of the collections under the plan DSNACLI and then rebind the plan, but to no avail. [...]
11644 71 39_Re: What is the Fastest Unload process?13_John Arbogast16_jfarbo@YAHOO.COM31_Fri, 17 Dec 1999 12:28:32 -0800553_us-ascii I heard the IBM "Fast Unload" was purchased from Infotel. Dunno if this is correct.
--- Tim.Lowe@STPAUL.COM wrote: > Richard, > The primary problem that I have had with using REORG > UNLOAD EXTERNAL is the > authorization required. You have to have REORG > authorization on the database in > order to unload a table with this. I would rather > not grant someone reorg > authority on a database to unload a table, so this > does have a limited use. > > Other than that, some of my tests have shown REORG > UNLOAD EXTERNAL to perform [...]
11716 123 28_Re: Clustering affects sorts13_Helen Johnson25_Helen_Johnson@RAC.RAY.COM31_Fri, 17 Dec 1999 15:34:18 -0600557_us-ascii Michael,
End user queries are where I see a lot of this. Our end users are using QMF and most are using prompted queries. They don't know or understand their data or the implications of what they do. The user picks SORT so they can order their data but they also also pick 'Keep single copy of each row' which added the DISTINCT. Usually this is superfluous because the columns selected are already unique. The user just knows he doesn't want to see two copies of the same row. He doesn't know he is generating another sort. Then I get [...]
11840 18 28_Backup UDB for NT using ADSM11_Kenneth Lam14_klam@BTMNA.COM31_Fri, 17 Dec 1999 16:44:53 -0500456_us-ascii Can someone point me to the right reference book or Redbook on how to setup configuration variables in UDB using ADSM backup. Our environment is DB2 UDB V5.2 for Window NT.
Thanks advance
Ken
================================================ 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.
11859 74 28_Re: DB2 Maintenance Question10_Bill Welch27_spgwdw@UCLINK4.BERKELEY.EDU31_Fri, 17 Dec 1999 14:35:10 -0800547_us-ascii >At 01:22 PM 12/17/99 -0600, Michael Bancale wrote: >Since we began with DB2 we have been putting ESO Maintenance on the system >prior to migrating to a new release of DB2. I know this is not a good thing >to wait so long between maint but we did it. Recently we applied ESO >Maintenance to DB2 for put level 9907. Once this maintenance was applied we >pointed DB2 and the utilities to the new SDSNLOAD library. > >Is this the way everyone else does it? > >I would be interested in your maintenance strategies. > >TIA > >Michael > > [...]
11934 85 38_Re: DSN1COPY and Segmented Tablespaces15_Murley, Michael22_Michael_Murley@BMC.COM31_Fri, 17 Dec 1999 17:20:04 -0600314_ISO-8859-1 One thing I have always enjoyed about working with DB2 is that the product documentation is generally quite clear compared with that of many other products. But the section on 'Recovery of Dropped Objects' (DB2 V5 Admin Guide p. 4-147) that Brian Patella cites below leaves me a little bewildered. [...]
12020 36 9_need help13_Sunil M Gupte29_Sunil.M.Gupte@MAIL.SPRINT.COM31_Fri, 17 Dec 1999 18:54:28 -0500399_ISO-8859-1 Hi
Can somebody please point out if following is allowed in DB2 ? (OS/390) Specifically the Select in the Set statement ?
Update test1.tt1_cust_conv_stus Set last_upd_user_id = userid, bckt_id = (Select bckt_id From test1.tt1_sub_prcs a, Test1.tt1_bckt b Where sub_prcs_nme = SUBPROCESS2 and b.bckt_nme = BUCKET2 and a.sub_prcs_id = b.sub_prcs_id) where cms_id = CMSID [...]
12057 15 13_Re: need help12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 17 Dec 1999 18:27:47 -0800360_us-ascii That looks like it's fine with DB2 for OS/390 Version 6 and APAR PQ30383, but not before.
Roger Miller
================================================ 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.
12073 24 37_Re: DB2 5.0 stored procedures and CLI13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Sat, 18 Dec 1999 09:02:08 +0530565_us-ascii hi !!
The funda behind the stored procedure is . The stored procedures when called from java servlets don't need a plan to execute. It just requires a package. Now when we call from the servlet it tries to create a package with collection ID as NULLID. So give PACKADM authority to the person on Collection ID NULLID and create the package in NULLID , and then execute the same . It will work . Otherwise. Create the package in some other collection ID and then set the current collection Id as that collection ID from the servlet ann execute. [...]
12098 59 13_Re: need help0_24_ssethi@LOT.TATASTEEL.COM29_Sat, 18 Dec 1999 05:41:13 GMT462_us-ascii Hi all,
If it is possible with DB2 V6 for OS390 APAR PQ30383 then doesn't it mean that DB2 has to determine the relation among the tables(dynamically) and then find out if only one record is qualifying for the result(used in the SET clause) then only use it in the SET clause. I am really wondering how is this possible. Can somebody please explain me the way this query works and what is the kind of relation three tables used should have. [...]
12158 767 0_13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Sat, 18 Dec 1999 12:03:39 +0530422_us-ascii
Hi !! I have a problem on connectivity !!
The problem is in the connectivity to Japanese Database in OS/390 DB2 V5.1 from Japanese NT running Japanese database.
Windows NT Server configuration :
1. Windows NT Japanese. 2. DB2 UDB V5.2 Enterprise version
OS/390 running DB2 V5.1 Database enabled for DBCS support.
The Codepage sequence in Os/390 Db2 v5.1 is [...]
12926 768 20_Connectivity Problem13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Sat, 18 Dec 1999 12:51:19 +0530422_us-ascii
Hi !! I have a problem on connectivity !!
The problem is in the connectivity to Japanese Database in OS/390 DB2 V5.1 from Japanese NT running Japanese database.
Windows NT Server configuration :
1. Windows NT Japanese. 2. DB2 UDB V5.2 Enterprise version
OS/390 running DB2 V5.1 Database enabled for DBCS support.
The Codepage sequence in Os/390 Db2 v5.1 is [...]
13695 49 13_Re: need help15_Mohammed Nayeem21_db2udbxpert@YAHOO.COM31_Sat, 18 Dec 1999 09:10:26 -0800614_us-ascii Hi Sunil
Yes ! a scalar subquery can be used in the SET clause of an UPDATE statment in DB2 UDB , which can only retrieve one value (i.e one row of one column).
regards
Nayeem
--- Sunil M Gupte wrote: > Hi > > Can somebody please point out if following is > allowed in DB2 ? (OS/390) > Specifically the Select in the Set statement ? > > Update test1.tt1_cust_conv_stus > Set last_upd_user_id = userid, > bckt_id = (Select bckt_id > From test1.tt1_sub_prcs a, > Test1.tt1_bckt b > Where sub_prcs_nme = SUBPROCESS2 > and b.bckt_nme = BUCKET2 [...]
13745 83 13_Re: need help14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Mon, 19 Dec 1994 11:35:52 +1000465_us-ascii Sanjeev,
Any subquery of form:
column = (subquery)
requires that the subquery return a single column and single row. If multiple rows returned, the SQL terminates with an error code.
Exactly when the subquery is executed depends on whether correlated or not.
The SET clause would have to be executed in stage 2 processing after the subquery is executed for each outer row found (if it is correlated to outer query). [...]
13829 39 3_!!!5_Ozden23_o_tutuncu@GEOCITIES.COM31_Mon, 20 Dec 1999 00:22:45 +020025_iso-8859-9 don't email me
13869 265 51_Re: Select from table with different magic numbers.14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Mon, 20 Dec 1999 09:44:58 +1000644_us-ascii Linda, With the new revelation regarding the segment entry in the spacemap page my question as to how I saw what I saw has been resolved. My question is now about the mechanism. I'll break my question down into the following and hope someone can explain the mechanism.
1. Create database, tablespace, table. Lets say DBID=1, PSID=1,OBID=1 (for example). 2. DFDSS DUMP this. 3. Drop and recreate the database using the same DDL. 4. New Database, tablespace, table numbers are DBID=2, PSID=2, OBID=2. 5. STOP tablespace, DFDSS RESTORE tablespace and indexspace datasets. 6. REPAIR LEVELID on tablespace, START tablespace. 7. [...]
14135 117 13_Re: need help0_24_ssethi@LOT.TATASTEEL.COM29_Mon, 20 Dec 1999 04:07:44 GMT492_us-ascii Thanks Michael ,i was bit confused.U are right that anyway it checks for the mutiple records and gives the error code(-811) if more than 1 record exists and "=" instead of "IN" is specified. Regarding the relation there would be one to many relationship(atleast) with the parent and child tables ,so primary/Unique key has to be specified in the subquery to make it single record returnable. As far as the original question is concern it gives syntax error in DB2 V5 for OS390. [...]
14253 21 65_=?iso-8859-1?B?UulmLiA6IEJhY2t1cCBVREIgZm9yIE5UIHVzaW5nIEFEU00=?=0_26_denzil.coulter@UNICIBLE.CH31_Mon, 20 Dec 1999 11:31:51 +0100547_us-ascii One source is -
Administration Guide Chapiter 7 Recovering a Database ADSTAR Distributed Storage Manager (ADSM)
This deals with set up for all environments.
If your ADSM server is located on another machine ensure that the ADSM client has been installed on the local machine.
================================================ 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.
14275 153 28_Re: Clustering affects sorts14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Tue, 20 Dec 1994 22:34:23 +1000290_us-ascii Jim,
It looks like DB2 does avoid the 2nd sort in many cases based on a very quick test run. Not sure that this was always the case.
My test appeared to show no measurable extra cost for ORDER BY on top of DISTINCT. I'll try to do more definitive tests shortly. [...]
14429 30 32_Integrity Control Facility/BLOBs0_18_mebert@AMADEUS.NET31_Mon, 20 Dec 1999 13:07:59 +0100452_us-ascii Hello all,
can somebody point me in the right direction for the following two questions:
1. What is a product called "DB2 Integrity Control Facility (ICF)", and how can I find out if it is installed/operating? It's probably something quite old. 2. Are BLOBs (for storing e.g. audio, video, images) part of DB2 (V5/V6/retrofit)? What is the syntax (column definition), restrictions, other things you should know for a start? [...]
14460 300 51_Re: Select from table with different magic numbers.11_Ball, Linda18_Linda_Ball@BMC.COM31_Mon, 20 Dec 1999 07:56:46 -0600382_ISO-8859-1 I have an idea that the code in DB2 which repairs missing or inaccurate segmented chains actually put an entry into the space map as an anchor. We will have to prove that with a testing. The SELECT * could have triggered that code. We will verify this. But that sure looks like what happened to you. Thanks for making the light bulb go off for me. I hate mysteries! [...]
14761 34 42_DSNTIAUL to access data at remote location12_Fertaki Gina20_GFertaki@EUROBANK.GR31_Mon, 20 Dec 1999 16:12:26 +0200283_iso-8859-7 Hello everyone, we have a DRDA connection between our DB2 V4 and DB2/400 up and running. I did a bind of DSNTIAUL as an application package both at local DB2 and at AS/400. I then try to unload a DB2 table from AS/400 by running DSNTIAUL using PARM('SQL') and doing: [...]
14796 34 22_MASS INSERT is idle...10_ajay kumar19_db2v5r1@HOTMAIL.COM29_Mon, 20 Dec 1999 21:25:27 IST368_- Hi.
We are downloaded a data from ADL, the data is in float format, we want to convert to decimal, for that we defined a temporary table with column defined as float. We uploaded data to the temporary table(using LOAD utility). After that we insert the temporary table rows into main table (The corresponding column is defined as decimal), it went thru, [...]
14831 18 46_Re: DSNTIAUL to access data at remote location12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 20 Dec 1999 09:08:13 -0800497_us-ascii DSNTIAUL depends upon the parser to find the semicolons, and the specific code is only in the DB2 for OS/390 parser. You can use the option to specify only the table name, or you can put in code to find the semicolon yourself.
Roger Miller
================================================ 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.
14850 16 39_Re: What is the Fastest Unload process?12_Roger Miller19_millerrl@US.IBM.COM31_Mon, 20 Dec 1999 09:49:59 -0800366_us-ascii APAR PQ23219 adds many of the other predicates, including the LIKE predicate to REORG UNLOAD EXTERNAL.
Roger
================================================ 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.
14867 28 27_RS/6000 Recovery Procedures9_Colin Fay13_cfay2@CSC.COM31_Mon, 20 Dec 1999 17:04:17 -0500478_us-ascii Hi,
We are implementing a DB2 application with drawings (graphical objects) which can be either stored as BLOB's or in an external file format on RS./6000.
We have been advised to stored the drawings in external files for performance reasons, but we are concerned with synchronized recovery of the external files with the database. There are drawing lookup tables which reference the external file names which must be kept in synch with these files. [...]
14896 39 25_Thread wait/reuse problem11_Dave Raiman19_raiman@VNET.IBM.COM29_Mon, 20 Dec 1999 17:13:57 PST391_- Did you get this figured out? If not, here are some details that may help.
Protected threads are held in the unused state as previously indicated for 2 purge cycles (default up to 1 minute).
When the thread is used, the timer is "reset". You can tell if the threads in question are being used by looking at the REQ column of the DB2 "-DISPLAY THREAD(*)" command output. [...]
14936 19 23_DB2 5.1 and Cold Fusion12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM31_Mon, 20 Dec 1999 21:58:51 -0500346_iso-8859-1 Is anyone using Cold Fusion to access a DB2 5.1 db on OS/390. We are planning on going into production is about 3 months and some disturbing questions have come up. ie. binding everytime someone accesses the db, maintaining threads, etc. I would like to discuss this whole process with anyone with experience in this environment. [...]
14956 113 52_How to Compile, Lked and Execute of Ezt Pgm with DB224_praveen Kumar komaragiri21_kpkumar@WIPHYD.GE.COM31_Tue, 21 Dec 1999 10:00:10 +0930554_iso-8859-1 Hi all, We are developing new Easytrieve pgms with SQL statements(Access to DB2) in our shop. These Easytrieve pgms also calls COBOL subroutines which is also having SQL (Access to DB2) statements.
So far we have executed Only easytrieves pgms (with SQL statements) and called pure Cobol subroutines(No DB2 Access). Now we need to have SQL statements in Easytrieve and also in Cobol subroutines. How to linkedit the above Easytrieve and Cobol object modules into one Loadmodule and how to bind its corresponding DBRMLIB to a PLAN. [...]
15070 61 56_Re: How to Compile, Lked and Execute of Ezt Pgm with DB214_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US31_Tue, 21 Dec 1999 08:23:47 -0500407_us-ascii Praveen,
I would not combine these objects or the DBRMs. To handle this situation
I would add a collection (say EZTCOBS.*) to the Easytrieve plan bind statement and rebind it. Then all your called COBOL (or whatever) subprograms should be bound as packages in that collection. Just ensure that the subprogram linklib is part of the concatenation of the Easytrieve execution JCL. [...]
15132 35 30_Reorg unload external question15_Backes, Michael26_BackeM@MAIL.OA.STATE.MO.US31_Tue, 21 Dec 1999 08:09:05 -0600566_iso-8859-1 In running the reorg utility with the unload external option we are recognizing some serious cpu reduction over dsntiaul(73% reduction) on one of our midsize databases. we want to use this on one of our larger databases, however we do not have it set up with one table per tablespace thus presenting the problem. when using the from table with the unload external it appears to be ignoring the from table syntax and unloaded every row from every table. it appears that you must code a when clause, but how to specify all the rows of the table in the [...]
15168 135 28_Re: Clustering affects sorts14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Thu, 22 Dec 1994 01:04:34 +1000445_us-ascii Helen,
I would like to take back my previous answer.
My tests seem to indicate now that once SELECT DISTINCT is coded and causes a DB2 RDS Sort, additional ORDER BY takes no appreciable additional CPU and presumeably utilises just one sort to do both DISTINCT and ORDER BY.
My test included a join with Selected columns from both tables and ORDER BY columns 2, 3, 1. CPU roughly same with or without ORDER BY. [...]
15304 69 42_Stress Test DB2 from CICS Socket Interface9_Mona Syed18_msyed@JCPENNEY.COM31_Tue, 21 Dec 1999 08:47:37 -0800593_iso-8859-1 Dear DB2 Listers,
I am a CICS Systems Programmer and have never worked with a DB2/SQL program. I have the need to write a driver program (in Assembler) that will stress test DB2 access from CICS TCP/IP socket interface.
I will need to use Dynamic SQL because the input to construct my SELECT statement will come from a temporary storage queue that my program will read. The output from the SQL SELECT statement will be a fixed-list result table. The row content (columns) will be the same always, but the number of rows returned will vary. Here’s what my program [...]
15374 69 34_Re: Reorg unload external question12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 21 Dec 1999 06:43:10 -0800583_us-ascii According to IBM support, its working as designed. Its a "feature". They don't believe that it is a bug to have to do when clauses for each table and manually reject every other table in that tablespace.
--- "Backes, Michael" wrote: > In running the reorg utility with the unload > external option we are > recognizing some serious cpu reduction over > dsntiaul(73% reduction) on one > of our midsize databases. we want to use this on > one of our larger > databases, however we do not have it set up with one > table per tablespace [...]
15444 69 34_Re: Reorg unload external question14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Tue, 21 Dec 1999 09:50:03 -0500664_iso-8859-1 REORG TABLESPACE dbname.tsname UNLOAD EXTERNAL FROM TABLE qualifier.tbname works for me.
If it does not work for you, you may want to check the available PTFs.
Thanks, Manas.
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Backes, Michael > Sent: Tuesday, December 21, 1999 9:09 AM > To: DB2-L@RYCI.COM > Subject: Reorg unload external question > > > In running the reorg utility with the unload external option we are > recognizing some serious cpu reduction over dsntiaul(73% reduction) on one > of our midsize databases. we want to use this on one of [...]
15514 120 46_Re: Stress Test DB2 from CICS Socket Interface20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 21 Dec 1999 10:10:12 -0500449_iso-8859-1 I am on the other side of fence. I haven't done CICS programming for years and almost forgotten.
Below is an example of Assembler trying run dynamic SQL.
L R6,=A(WORKEND-WORKAREA) GET LENGTH STORAGE REQUIRED GETMAIN RC,LV=(R6) GET THE STORAGE ST R15,RETHIGH SAVE RETURN CODE LTR R15,R15 CHECK RETURN CODE BNZ RETURN END IF NOT OK ELSE .... LR R10,R1 .... .... EXEC SQL DECLARE CURSOR FOR [...]
15635 20 46_Re: Stress Test DB2 from CICS Socket Interface13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 21 Dec 1999 20:53:52 +0530310_us-ascii hi Mona !!
First of all why are u trying to do the dynamic SQL query program thru the Assembler . YOu have better and simple options in the form of cobol . I have the cobol code which will be firing dynamic sql's after a getmain . This option can fire the queries from the cics terminals. [...]
15656 88 34_FW: Reorg unload external question14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Tue, 21 Dec 1999 10:38:17 -0500334_iso-8859-1 Whoops!
You're right, Michael...it does unload all the tables in the tablespace. I was running it against a Peoplesoft tablespace which had only one table populated (out of about 20...most of them were temp tables and are not always populated) and it appeared to work because that was the only table populated. [...]
15745 25 25_Running utilities in REXX13_Scott Goodell24_Scott.E.Goodell@WCOM.COM31_Tue, 21 Dec 1999 09:51:04 -0600465_us-ascii I'm trying to run the runstats utility from a REXX exec on DB2 MVS by calling DSNUTILB.
I keep getting the reason code 00E40002, which indicates an APF auth problem. I don't have any other libraries in my steplib than I do if I just run a plain runstats through DSNUTILB. I suspect our system is setup somehow to not allow any TSO (IKJEFT01) program run authorized. Has anyone tried this with any success or do you have any suggestions to try? [...]
15771 36 46_Re: DSNTIAUL to access data at remote location12_Fertaki Gina20_GFertaki@EUROBANK.GR31_Tue, 21 Dec 1999 18:00:30 +0200421_windows-1253 Although If I do exactly the same with the DSNTEP2 program, AS/400 recognizes the semicolumns and returns data. The problem is that the data is in a report format and not in UNLOAD format as desired. Gina Fertaki -----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Monday, December 20, 1999 7:08 PM To: DB2-L@RYCI.COM Subject: Re: DSNTIAUL to access data at remote location [...]
15808 127 46_Re: Stress Test DB2 from CICS Socket Interface15_Backes, Michael26_BackeM@MAIL.OA.STATE.MO.US31_Tue, 21 Dec 1999 10:03:16 -0600406_iso-8859-1 sorry for wasting your time if you know all this already. but what you are trying to do sounds way beyond the point of where sockets even cares about what you are doing. i am assuming the driver program calling yours is the pgm associated with the transaction being started by sockets. and once out of sockets the real test is your db2 performance, cics/db2 interface, tcpip network speed. [...]
15936 53 29_Re: Running utilities in REXX20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 21 Dec 1999 11:00:20 -0500332_- How are you passing parameters to DSNUTILB ? The problem is when you link to program from REXX, the MVS linkage convention is not followed. You need to have another program "in between" which loads the correct address to Register 1 before calling DSNUTILB. Following are macros ,which will help you mapping ('SYS1.MACLIB') - [...]
15990 18 35_Questions about DB2V6 RECOVER INDEX24_Marchese, Frank D, CSCIO18_fdmarchese@ATT.COM31_Tue, 21 Dec 1999 10:52:41 -0500344_iso-8859-1 Hi - I am trying to find out about this feature. Specifically my questions:
1. If I just image copy the indexspace and not the tablespace, will the COPY PENDING bit be set for the tablespace and any other indexspaces ? 2. If I recover one index, will DB2 handle the recovery of the ts, etc or is that my responsibility ? [...]
16009 19 0_12_Hayes, Chris19_CHRIS.HAYES@SSA.GOV31_Tue, 21 Dec 1999 11:04:55 -0500615_iso-8859-1 While looking through some reports, I noticed that one of our production DB2 subsystems is checkpointing over 400 times an hour during certain times. I checked our checkpoint frequency parameter and it is set at 50,000. IBM recommends using 25,000 to 500,000 in their Installation Guide. Am I correct in thinking that 400 times an hour is excessive? Should I be concerned about raising the threshold? IBM's Installation Guide mentions that start-up time may be excessive (over 30 minutes) if you specify a value over 300,000 and you terminate DB2 without a quiesce. Is this a real concern or should [...]
16029 29 11_LIKE CLAUSE0_20_ddlusk@HOUSEHOLD.COM31_Tue, 21 Dec 1999 10:14:36 -0600433_us-ascii HI ALL.. We're on DB2V5 / OS390
I have an online program that is called from a POWERBUILDER application thru Trans Global Messaging we are attempting to do a name search, so the cursor is
Declare name cursor for select name_first, name_init, name_last from customer_name where name_last like :input-field.
of course the query does not recognize the value of the input-field which contains 'smith%' [...]
16059 53 32_Re: Checkpoint Frequency to high12_craig patton21_prgpatton@HOTMAIL.COM29_Tue, 21 Dec 1999 08:39:11 PST578_- Chris,
First, 400 checkpoints per hour is extremely high! Definitely raise your LOGLOAD parameter so that you take about 4 checkpoints per hour. Keep in mind that while DB2 is taking a checkpoint, NO OTHER ACTIVITY can take place. Checkpoints are very short in duration, but at 400 per hour, your system is quite degraded as you are performing 1 checkpoint about every 9 seconds....Are you positive about the 400 per hour or is that for the whole reporting period?? 400 is about right for a 24 hour time frame. If it really is 400/hour, I would take that LOAGLOAD [...]
16113 76 32_Re: Checkpoint Frequency to high12_Hayes, Chris19_CHRIS.HAYES@SSA.GOV31_Tue, 21 Dec 1999 12:01:42 -0500430_iso-8859-1 Craig - Thanks for your prompt response.
The report tool I am using breaks up various system statistics by the hour. The checkpoint frequency numbers range between 0 and 561 (the latest high water mark) with the highest occurring between 22:00 and 01:00 when we do a lot of batch updates. Most of the time, the number is under 10 per hour. I will talk with the system's people about raising this parameter. [...]
16190 146 28_Re: Clustering affects sorts13_Helen Johnson25_Helen_Johnson@RAC.RAY.COM31_Tue, 21 Dec 1999 11:14:45 -0600572_us-ascii Michael,
In your tests, was the order of the DISTINCT columns different from the order of the ORDER BY columns, or were both the same? Thanks,
Helen
Michael Hannan wrote:
> Helen, > > I would like to take back my previous answer. > > My tests seem to indicate now that once SELECT DISTINCT is coded and causes > a DB2 RDS Sort, additional ORDER BY takes no appreciable additional CPU and > presumeably utilises just one sort to do both DISTINCT and ORDER BY. > > My test included a join with Selected columns from both tables and [...]
16337 50 39_Re: Questions about DB2V6 RECOVER INDEX14_Grainger, Phil21_Phil.Grainger@CAI.COM31_Tue, 21 Dec 1999 17:19:46 -0000492_- Frank,
If you copy an index then you are making a point-in-time backup of that index. There is no reason for DB2 to put the tablespace or any of the other indexes into any restrictive state.
If you recover one index then what happens next depends on WHEN you recover to. If you do a recover to current, then no problem. BUT, if you recover back to a prior point in time, then DB2 will put the tablespace and all other indexes into RECP to force you to recover THEM also. [...]
16388 71 39_Re: Questions about DB2V6 RECOVER INDEX24_Marchese, Frank D, CSCIO18_fdmarchese@ATT.COM31_Tue, 21 Dec 1999 12:28:39 -0500377_iso-8859-1 Thanks Phil for the info.
Point number 2 makes sense and I needed a confirmation.
My concern with #1 was this: by only copying one index, you've created a recovery point/ pt of consistency for only one object and if you need to recover other indexes/the tablespace, those same points do not exists except if RECOVER starts processing the DB2 log. [...]
16460 29 33_accessing MVS os/390 data from PC0_26_mmetcalf@NOTES.STATE.NE.US31_Tue, 21 Dec 1999 12:01:38 -0600480_us-ascii I'm seeking some expertise in the area of PC applications accessing (reading and updating) data which resides on MVS OS/390.
We have one project which unloads the MVS OS/390 data and FTP's the datasets to the desired platform. We have another (pilot) project which is via the WEB and using stored procedures - the user enters the URL , the application connects to DB2 and returns the WEB page/data. Is there any better possilities of accomplishing this task? [...]
16490 20 46_Re: DSNTIAUL to access data at remote location12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 21 Dec 1999 08:34:13 -0800347_us-ascii That's right. DSNTEP2 has code to find the semicolon. If you translate that into assembler to find the semicolon, then the DSNTIAUL program will work with other platforms. We couldn't convince other platforms to implement the returned information about the ending semicolon. The complete job of searching for tokens is very complex. [...]
16511 32 22_Indus software and DB218_Whittaker, Stephen26_stephen.whittaker@CPLC.COM31_Tue, 21 Dec 1999 12:53:17 -0500473_iso-8859-1 Is anyone out there using the Indus/Passport product??
We've currently installed Indus/Passport Version 7.0 and we are currently running DB2 V5. I'm looking at going to DB2 Version 6 next year and I was wondering if anyone is currently running Indus 7.0 with DB2 V6?? My understanding is that Indus has not certified DB2 V6 to run with their Version 7.0 and I'm trying to get some clarification on this. Any information would be greatly appreciated. [...]
16544 64 0_12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Tue, 21 Dec 1999 20:25:29 +0200517_x-user-defined Hi,
In my opinion you're way too low on checkpoint frequency. I try to keep it at around once every 10-15 minutes. It generates a hell lot of an I/O. The problem is that you can't adjust it dynamically so sometimes it's too much and others much less. However since it concerns amount of work done then you should move it up. Mathematically speaking if you put in 500,000 it'll give you 40 times an hour on you peak time which is still too often. I would go to 2,000,000 based on your data. [...]
16609 24 33_DB2 connect and stored procedures13_Martin Kenney25_martin.kenney@RAILINC.COM31_Tue, 21 Dec 1999 13:48:31 -0500576_us-ascii We now have a requirement to have a PC based application which can connect to DB2 and VSAM files. Our plan is to use DB2 connect and stored procedures to accomplish this. Our VP/IT wants us to justify using IBM's products over other solutions. Can anyone give me their brief good or bad experiences(performance and ease of use)? Also, if you have compared various products what your findings were. There seems to be one major (at least in our case) advantage to staying within IBM's product line, and that is when we put maintenance on DB2 or upgrade to version [...]
16634 55 19_DB2 from EasyTrieve16_Madhu K Banavati25_Madhu.K.Banavati@AEXP.COM31_Tue, 21 Dec 1999 11:37:58 -0700727_- Hi,
I am trying to access DB2 tables from a Easytrieve program....but have not been able to. Hope you guys can help me out !
Following is the SYSPRINT output of the program I tried to execute.
------------------------------------------------------------------------------- ------------------------------------ PARM SSID('DB2T') + SQLID('SYSIBM')
FILE OUTRPT
W-NAME W 8 A
JOB INPUT SQL SELECT NAME FROM SYSPLAN ORDER BY NAME + *******B204 SQL - DB2 SUBSYSTEM "DB2T" DOES NOT EXIST *******B204 SQL - CHANGING TO "PARTIAL" SQL SYNTAX CHECKING INTO :W-NAME ------------------------------------------------------------------------------- ------------------------------------ [...]
16690 48 15_Re: LIKE CLAUSE0_23_Mike_Levine@TEKHELP.NET31_Tue, 21 Dec 1999 15:11:54 -0500657_us-ascii Hi,
Just try initializing the field with '%%%%%%%...'. Then you may end up with LIKE 'SMITH%%%%%' which will work.
Regards,
Michael Levine Premier Data Services, Inc.
>HI ALL.. >We're on DB2V5 / OS390 > >I have an online program that is called from a POWERBUILDER application thru >Trans Global Messaging >we are attempting to do a name search, so the cursor is > >Declare name cursor for > select name_first, name_init, name_last > from customer_name where > name_last like :input-field. > >of course the query does not recognize the value of the input-field >which contains 'smith%' > >is there a [...]
16739 94 39_Re: Questions about DB2V6 RECOVER INDEX15_Di Carlo, Donna23_Donna_Bermender@BMC.COM31_Tue, 21 Dec 1999 14:17:48 -0600491_ISO-8859-1 Once again, it is up to you to provide a consistent point of recovery. DB2 will not prevent you from copying the table space and indexes at different points because these copies are perfectly fine for recovering to current or to a common quiesce point. If you foresee the need for TOCOPY recoveries, it is advisable to copy the table space and all the indexes in one COPY list (with SHRLEVEL REFERENCE). This will cause all the copies to be registered at the same log point. [...]
16834 100 33_Re: Checkpoint Frequency too high13_John Arbogast16_jfarbo@YAHOO.COM31_Tue, 21 Dec 1999 12:18:02 -0800608_us-ascii I agree 400 per hour is way out of line!! We shoot for 4 to 6 per hour during our nightly batch flows. During (relative) quiet times we somtimes go over an hour without a checkpoint.
Insert your favorite disclaimer here.
Happy Holidays! John Arbogast.
--- craig patton wrote: > Chris, > > First, 400 checkpoints per hour is extremely high! > Definitely raise your > LOGLOAD parameter so that you take about 4 > checkpoints per hour. Keep in > mind that while DB2 is taking a checkpoint, NO OTHER > ACTIVITY can take > place. Checkpoints are [...]
16935 123 33_Re: Checkpoint Frequency too high13_John Arbogast16_jfarbo@YAHOO.COM31_Tue, 21 Dec 1999 12:20:27 -0800546_us-ascii Yes they do, but they are marked for reuse.
--- "Hayes, Chris" wrote: > Craig - Thanks for your prompt response. > > The report tool I am using breaks up various system > statistics by the hour. > The checkpoint frequency numbers range between 0 and > 561 (the latest high > water mark) with the highest occurring between 22:00 > and 01:00 when we do a > lot of batch updates. Most of the time, the number > is under 10 per hour. I > will talk with the system's people about raising > this parameter. > [...]
17059 62 26_Re: Indus software and DB215_Fred Occhipinti23_Fred_Occhipinti@FPL.COM31_Tue, 21 Dec 1999 15:22:09 -0500439_us-ascii Steve: Currently have 7.0.1(Baseline) in our test region with DB2 V5. From the installation documentation I have read for PassPort, it looks like Indus is still recommending Type 1 index for their tables. I believe that DB2 6.0 does not support type 1 indexes any longer. We have created the tables with Type 2, but this Baseline Test Region has the minimal amount of Baseline Test Data needed to work. Best Regards, Fred O. [...]
17122 86 35_Re: Indus Passport software and DB20_23_Mike_Levine@TEKHELP.NET31_Tue, 21 Dec 1999 15:55:38 -0500514_us-ascii Hi,
We have Passport here in production R5+ customized and 7.0 in Demo (A/P, Inventory, purchasing, doc mgmt, MSDS, AITTS, etc.). In the three years I've been supporting the system, I've seen nothing in the application that ties it to a specific release of DB2 (for upward compatibility). They just generate TYPE 1 indexes for old DB2 systems. We have converted all of our indexes to TYPE 2. In fact they provide much generated DDL that we find necessary to modify (i.e., simple tablespaces)! [...]
17209 105 26_Re: Indus software and DB212_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 21 Dec 1999 13:01:38 -0800499_us-ascii I put in Indus 6 on a DB2 release 4 client's shop and made all of the indexes Type 2 a couple of years ago. It has run in production just fine with them since then with moderate volumes. One major DBA consultant that works very closely with INDUS and a number of their shops about had kittens because I had changed to type 2 indexes, but the system ran just fine. Switching to type 2 indexes should be done as soon as possible - the other performance benefits are definitely worth it. [...]
17315 139 26_Re: Indus software and DB215_Fred Occhipinti23_Fred_Occhipinti@FPL.COM31_Tue, 21 Dec 1999 16:18:16 -0500590_us-ascii As long as you are benchmarking everything, that is fine. Our current release of PassPort (5.0.3 heavily customized) requires a certain table to remain Type 1 (TIDECOVL). When this table is converted to Type 2 index, the response time is exponentially larger (where you can measure in many seconds) versus Type 1. The only characteristic about this table is that it is really small. On version 5.0 of DB2 there is an IBM fix regarding small volume tables with Indexes(Indices?). Our systems group has not applied the maintenance on 5.0 yet to see if it works. DB2 V6.0 is not [...]
17455 41 23_Re: DB2 from EasyTrieve12_Jim Harrison12_jimh@QIS.NET31_Tue, 21 Dec 1999 15:15:48 -0500342_us-ascii I don't know a thing about Easytrieve, but are you submitting the job to the correct system? (assuming you have multiple MVS images and assuming DB2T is the correct name). We ran into similar messages when people forgot to use a "/*JBS BIND system" card or the jobclass we dedicated to DB2 and the jobs ended up on another box. [...]
17497 127 26_Re: Indus software and DB20_23_Mike_Levine@TEKHELP.NET31_Tue, 21 Dec 1999 16:22:51 -0500365_us-ascii Hi Myron,
I don't recall any specific recommendations from INDUS advising not to convert to TYPE 2. However, in high volume systems you did have to be careful with full index scans now locking many data pages but we corrected this with use of REOPT(VARS) on selective programs.
Regards,
Michael Levine Premier Data Services, Inc. [...]
17625 161 26_Re: Indus software and DB20_23_Mike_Levine@TEKHELP.NET31_Tue, 21 Dec 1999 16:31:54 -0500681_us-ascii Hi,
Never, never, never drop any UNIQUE baseline indexes! This is how INDUS enforces uniqueness. The code often checks for a -803 and takes appropriate action. You can sometimes drop non-unique indexes but NEVER unique ones.
Regards,
Michael Levine Premier Data Services, Inc.
>As long as you are benchmarking everything, that is fine. Our current release >of PassPort (5.0.3 heavily customized) requires a certain table to remain Type >1 (TIDECOVL). When this table is converted to Type 2 index, the response time >is exponentially larger (where you can measure in many seconds) versus Type 1. >The only characteristic about this [...]
17787 61 23_Re: DB2 from EasyTrieve16_Madhu K Banavati25_Madhu.K.Banavati@AEXP.COM31_Tue, 21 Dec 1999 14:37:31 -0700646_- You were right !! Thanks a lot.......It worked....I just had to put a /*Route XEQ 'db2' statement.....
lRegards and have a great holiday !
Madhu
From: jimh%QIS.NET@Internet on 12/21/99 02:22 PM To: DB2-L%RYCI.COM@Internet cc: (bcc: Madhu K Banavati) Subject: Re: DB2 from EasyTrieve
I don't know a thing about Easytrieve, but are you submitting the job to the correct system? (assuming you have multiple MVS images and assuming DB2T is the correct name). We ran into similar messages when people forgot to use a "/*JBS BIND system" card or the jobclass we dedicated to DB2 and the jobs ended up [...]
17849 94 38_Re: LOGLOAD and checkpoint frequencies12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 21 Dec 1999 14:38:56 -0800537_us-ascii There have been many changes over the various releases of DB2. I'll try to note the key ones, but there are two concerns for checkpoints: cost and disruption of the checkpoints restart time for the subsystem after a crash
While 400 checkpoints per hour is too many, ten or fifteen minutes between checkpoints is likely to cause problems in restart time, especially if you use that for the batch update timing. After a normal quiesce stop, there is no work to do. The concern is for a DB2 abend or modify irlm,abend. [...]
17944 171 28_Re: Clustering affects sorts14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Thu, 22 Dec 1994 09:57:23 +1000588_us-ascii ORDER BY 2, 3, 1 was different to DISTINCT 1, 2, 3 but logically the sequence of columns for DISTINCT is unimportant, so I guess DB2 might have internally converted to to DISTINCT 2, 3, 1 to match the ORDER BY.
From: Michael Hannan >From: Helen Johnson >Subject: Re: Clustering affects sorts >To: DB2-L@RYCI.COM > >Michael, > >In your tests, was the order of the DISTINCT columns different from the order of the ORDER BY columns, or were both the same? Thanks, > >Helen > >Michael Hannan wrote: > >> Helen, >> >> I would like to take back [...]
18116 103 37_Re: accessing MVS os/390 data from PC0_30_Milorad_Cerovac@TAC.VIC.GOV.AU31_Wed, 22 Dec 1999 10:40:56 +1000454_us-ascii We have installed the MQSeries software to allow our PC-based applications (written in VB) to access our CA-IDMS database running on an MVS OS/390 mainframe. In our case, the PC-application also accesses a DB2 UDB on an NT platform.
MQSeries allows programs to talk to each other irrespective of the processors used, operating system, communication protocols et al. This involved a fair bit of work ... but very basically we'd have: [...]
18220 81 29_Re: Running utilities in REXX14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Tue, 21 Dec 1999 19:23:33 -0500581_us-ascii If you invoke an APF-authorized module through LINKMVS or ATTACHMVS, it will be run in an ordinary unauthorized environment and will be unable to perform any authorized functions. This is the same thing that would happen if you wrote an unauthorized assembler program and coded LINK EP=DSNUTILB or ATTACH EP=DSNUTILB. The APF authorization of the environment is established at the time the job-step program is invoked, based on the jobstep program's authorization level in the load library directory and on the kashruth of the concatenation from which it is fetched. [...]
18302 54 0_10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Tue, 21 Dec 1999 19:52:48 -0600738_iso-8859-1 Yes taking a checkpoint 400 times an hour is REALLY excessive. Think of it your checkpointing 6.4 times per minute.
Currently our checkpoint frequency is set at 200,000 and that's on a very busy sysplex.
Good luck...
______________________________ Reply Separator _________________________________ Subject: Author: owner-db2-l@RYCI.COM at INTERNET Date: 12/21/1999 11:19 AM
While looking through some reports, I noticed that one of our production DB2 subsystems is checkpointing over 400 times an hour during certain times. I checked our checkpoint frequency parameter and it is set at 50,000. IBM recommends using 25,000 to 500,000 in their Installation Guide. Am I correct in [...]
18357 123 38_Re: LOGLOAD and checkpoint frequencies0_24_ssethi@LOT.TATASTEEL.COM29_Wed, 22 Dec 1999 03:42:53 GMT393_us-ascii I am fully convinced with good indepth analysis given by Roger about the high restart time and mainly about using VDWQT and DWQT more smartly which reduce the work to be performed during the check point. Regarding the staying of the pages in buffer after getting written to DASD during the checkpoint ,DB2 marks that pages as available pages and that can be stealed for new use. [...]
18481 81 15_Re: LIKE CLAUSE0_24_ssethi@LOT.TATASTEEL.COM29_Wed, 22 Dec 1999 03:29:16 GMT297_us-ascii Is there any case sensitive issue over here as i have found many times the application is trying to access the data via host variables and not able to pick up mainly the lower case letters.Just check it out how name or last name is stored in the table.
I think it can help u. [...]