1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 2005, week 5
2 68 40_(UDB)db2ilist & "profiles.reg" QUESTIONS12_Anurag Kumar22_kumaranurag@HSBC.CO.IN31_Tue, 29 Nov 2005 14:58:03 +0530525_US-ASCII Hi, I have 2 related questions. DB2 UDB ESE v8.x (8.1 till 8.2 FP2 - all fixpaks included) on AIX 5.4.x I am the instance owner and can "su" to all other instance owners. _________________________________________________________________________ (QUESTION 1) Output of "db2ilist" does not list all the instances on my AIX box. DB2 functions normally - I do not get any unexpected errors. Any ideas what configuration has been missed out? _________________________________________________________________________ [...]
71 299 48_Z/OS DB2 V7::Nested Loop Join - to - Hybrid Join21_Sriramulu, Vijayababu28_vijayababu.sriramulu@EDS.COM31_Tue, 29 Nov 2005 16:11:36 +0530470_us-ascii Dear list,
We have a long running SQL in production. Here are the related (Simplified) details.
Table-1 (Total number of rows in this table is 100)
Column1 decimal(10,0) Column2 char(100)
Index-1 on Table-1 above
Column1 Asc
Table-2 (Total number of rows in this table is 41 million)
Column-3 decimal(10,0) Column4 ... Other columns exist.. .. ..
Index-2 on Table-2 above (Primary index) Column3 Asc [...]
371 280 52_Re: Z/OS DB2 V7::Nested Loop Join - to - Hybrid Join13_Richard Fazio21_rfazio@TRANSUNION.COM31_Tue, 29 Nov 2005 07:39:17 -0600442_ISO-8859-1 It seems that your query is based upon a very limited list of values (100 or less).
Personally, I would just do an inner join of the two, but why not take the "TABLE 1" out of the equation (and a join). Try selecting from "TABLE 2" with a hard coded list. Witness the I/O and CPU extracting from TABLE-2 as a base line. You should have a best case scenario to judge if the optimizer is working in your favor ...or not. [...]
652 42 7_Trigger5_James20_james.y.park@SSA.GOV31_Tue, 29 Nov 2005 10:00:22 -0600377_- Hi, List.
We use DB2 on OS/390 (v7). I've tried to create a simple trigger using SPUFI, but kept getting an error message.
CREATE TRIGGER TRIGSSNN AFTER UPDATE ON JAMES01.SSNN FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE HEIGHT SET HEIGHT = HEIGHT + 10; END; _______________________________________________________ This is the error message I got. [...]
695 76 11_Re: Trigger10_Tee, Chris21_Chris.Tee@EU.SONY.COM31_Tue, 29 Nov 2005 16:11:41 -0000429_iso-8859-1 Try changing the SQL delimiter in SPUFI defaults to something other than ; , say @
Chris
Chris Tee DB2 DBA Infrastructure Services & SAP Solutions (iS3) Information Systems Europe SONY Europe Tel: +44 (0) 1256 82 8342
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Sent: 29 November 2005 16:00 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Trigger [...]
772 136 11_Re: Trigger13_Richard Fazio21_rfazio@TRANSUNION.COM31_Tue, 29 Nov 2005 10:12:37 -0600491_US-ASCII Change your SQL TERMINATOR to something other than a semi...like "@". Then change your stmt as follows. CREATE TRIGGER TRIGSSNN AFTER UPDATE ON JAMES01.SSNN FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE HEIGHT SET HEIGHT = HEIGHT + 10; END@ _______________________________________________________
Rich Fazio Information Architecture
TransUnion, LLC Marketing Services, 8th Floor 555 West Adams St. Chicago, IL 60661 Phone (312) 985-3270 Fax (312) 466-6453 [...]
909 214 11_Re: Trigger25_Biswal, Manas (Cognizant)26_Manas.Biswal@COGNIZANT.COM31_Tue, 29 Nov 2005 21:42:39 +0530452_iso-8859-1
I think this was discussed before in the list. You are using the semicolon as the SQL delimiter both inside the trigger body and as the generic SQL delimiter in SPUFI. Change the SPUFI defaults to use a different SQL delimiter. It should work out then.
Thanks, Manas
"It is not the strongest of the species that survives, nor the most intelligent; it is the one that is most adaptable to change". - Charles Darwin [...]
1124 141 11_Re: Trigger13_Richard Fazio21_rfazio@TRANSUNION.COM31_Tue, 29 Nov 2005 10:12:37 -0600491_US-ASCII Change your SQL TERMINATOR to something other than a semi...like "@". Then change your stmt as follows. CREATE TRIGGER TRIGSSNN AFTER UPDATE ON JAMES01.SSNN FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE HEIGHT SET HEIGHT = HEIGHT + 10; END@ _______________________________________________________
Rich Fazio Information Architecture
TransUnion, LLC Marketing Services, 8th Floor 555 West Adams St. Chicago, IL 60661 Phone (312) 985-3270 Fax (312) 466-6453 [...]
1266 367 11_Re: Trigger14_Park, James Y.20_James.Y.Park@SSA.GOV31_Tue, 29 Nov 2005 11:32:51 -0500608_- Hi, Rich.
I ran your stmt but kept getting an error message.
CREATE TRIGGER TRIGSSNN AFTER UPDATE ON JAMES01.SSNN FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE HEIGHT SET HEIGHT = HEIGHT + 10; END@ ____________________________________ CREATE TRIGGER TRIGSSNN 00010004 AFTER UPDATE ON JAMES01.SSNN 00020004 FOR EACH ROW MODE DB2SQL 00030004 BEGIN ATOMIC 00040004 UPDATE HEIGHT SET HEIGHT = HEIGHT + 10; 00050008 ---------+---------+---------+---------+---------+---------+---------+------ ---+ DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: QUOTE [...]
1634 205 11_Re: Trigger18_Maria Isabel Sousa23_Isabel.Sousa@PT.IBM.COM31_Tue, 29 Nov 2005 16:23:23 +0000477_US-ASCII Hi James
Change the terminator character of the SQL statements.
--#SET TERMINATOR ? CREATE TRIGGER TRIGSSNN AFTER UPDATE ON JAMES01.SSNN FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE HEIGHT SET HEIGHT = HEIGHT + 10; END?
Isabel Sousa
James Sent by: DB2 Data Base Discussion List 29-11-2005 16:00 Please respond to DB2 Database Discussion list at IDUG [...]
1840 155 11_Re: Trigger15_Basivi Inaganti30_basivi.r.inaganti@JPMCHASE.COM31_Tue, 29 Nov 2005 11:35:31 -0500533_US-ASCII Hi James, Change your SQL TERMINATOR .. ===> on CURRENT SPUFI DEFAULTS panel from ; to # and try. Thanks, Basivi.
James Sent by: DB2 Data Base Discussion List 11/29/2005 11:00 AM Please respond to DB2 Database Discussion list at IDUG
To: DB2-L@www.idugdb2-l.org cc: Subject: [DB2-L] Trigger
Hi, List.
We use DB2 on OS/390 (v7). I've tried to create a simple trigger using SPUFI, but kept getting an error message. [...]
1996 154 11_Re: Trigger15_Basivi Inaganti30_basivi.r.inaganti@JPMCHASE.COM31_Tue, 29 Nov 2005 11:35:31 -0500467_US-ASCII and change END; to END#.
Thanks, Basivi.
James Sent by: DB2 Data Base Discussion List 11/29/2005 11:00 AM Please respond to DB2 Database Discussion list at IDUG
To: DB2-L@www.idugdb2-l.org cc: Subject: [DB2-L] Trigger
Hi, List.
We use DB2 on OS/390 (v7). I've tried to create a simple trigger using SPUFI, but kept getting an error message. [...]
2151 160 11_Re: Trigger15_Basivi Inaganti30_basivi.r.inaganti@JPMCHASE.COM31_Tue, 29 Nov 2005 11:35:31 -0500533_US-ASCII Hi James, Change your SQL TERMINATOR .. ===> on CURRENT SPUFI DEFAULTS panel from ; to # and try. Thanks, Basivi.
James Sent by: DB2 Data Base Discussion List 11/29/2005 11:00 AM Please respond to DB2 Database Discussion list at IDUG
To: DB2-L@www.idugdb2-l.org cc: Subject: [DB2-L] Trigger
Hi, List.
We use DB2 on OS/390 (v7). I've tried to create a simple trigger using SPUFI, but kept getting an error message. [...]
2312 165 11_Re: Trigger15_Basivi Inaganti30_basivi.r.inaganti@JPMCHASE.COM31_Tue, 29 Nov 2005 11:35:31 -0500533_US-ASCII Hi James, Change your SQL TERMINATOR .. ===> on CURRENT SPUFI DEFAULTS panel from ; to # and try. Thanks, Basivi.
James Sent by: DB2 Data Base Discussion List 11/29/2005 11:00 AM Please respond to DB2 Database Discussion list at IDUG
To: DB2-L@www.idugdb2-l.org cc: Subject: [DB2-L] Trigger
Hi, List.
We use DB2 on OS/390 (v7). I've tried to create a simple trigger using SPUFI, but kept getting an error message. [...]
2478 36 35_Re: z/OS DB2 V7.1 S813-04 abend....15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Tue, 29 Nov 2005 21:44:19 +0100331_us-ascii > (...) >James, when I code the following in my restore JCL (no >other changes being done): > >//TAPE1 DD >DSN=XYA.DSNDB04.TS1.D112405.V03.TP,DISP=OLD > >The Job finishes with RC=4 and a message: No data was >copied from the input to the output. So, basically >Restore still does not seem to work. Any ideas. > (...) [...]
2515 276 15_Date comparison10_Grace Chen20_grace.chen@CELERO.CA31_Tue, 29 Nov 2005 14:48:29 -0700873_US-ASCII Hi list, We are running DB2 V8 New function Mode for Z/OS. There is a very strange result on the date comparison. Can anyone there explain this result? . SELECT FMGOU.BR_NBR, FMGME.ACCT_NBR, P20_ACTTYP.SHRT_NM, FMGAC.SUB_NBR, FHGAS.END_DT_ID, FHGAS.BALANCE_LOW_MTH, FHGAS.BAL_INT_MIN_MTH, FHGAS.BALANCE, P20_ACTSTS.SHRT_NM FROM FMGOU, FMGME, TDWHP0S.FCVCL_ACTTYP P20_ACTTYP, FMGAC, FHGAS, TDWHP0S.FCVCL_ACTSTS P20_ACTSTS WHERE ( FMGOU.OU_ID=FMGME.OU_ID ) AND ( FMGOU.CU_NBR=FMGME.CU_NBR ) AND ( FMGAC.ACCT_ID=FHGAS.ACCT_ID ) AND ( P20_ACTTYP.CL_ID=FMGAC.ACCT_TYPE_ID ) AND ( FMGAC.ACCT_STATUS_ID=P20_ACTSTS.CL_ID ) AND ( FHGAS.CU_NBR=FMGAC.CU_NBR ) AND ( FHGAS.MRSP_ID=FMGAC.MRSP_ID ) AND ( FMGME.MRSP_ID=FMGAC.MRSP_ID ) AND ( FMGME.CU_NBR=FMGAC.CU_NBR ) AND ( P20_ACTTYP.SHRT_NM IN ('CSIF','CSSP') AND FHGAS.END_DT_ID BETWEEN '2004-11-30' AND '2005-10-31' ) [...]
2792 121 29_V8 Z/OS Bufferpool Statistics12_Higgins John26_HigginsJohnP@JOHNDEERE.COM31_Tue, 29 Nov 2005 15:05:31 -0600456_us-ascii Hello, List
Can anyone tell me why I see so many more "prefetch requests" than "prefetch I/O Operations" in these stats?
I picked a sample 15 minute interval; most of the other intervals look similar.
Pages Read via Seq Prefetch 1421298 Seq Prefetch I/O Operations 48168 Sequential Prefetch Requests 71118
Pages Read via List Prefetch 358707 List Prefetch I/O Operations 14472 List Prefetch Requests 25918 [...]
2914 168 33_Re: V8 Z/OS Bufferpool Statistics35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 29 Nov 2005 18:13:02 -0500395_iso-8859-1 V8 Z/OS Bufferpool StatisticsHi John, This means that the I/O was not necessary because all the pages necessary for the prefetch request were already in the pool.
Regards, Joel ----- Original Message ----- From: Higgins John Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tuesday, November 29, 2005 4:05 PM Subject: [DB2-L] V8 Z/OS Bufferpool Statistics [...]
3083 88 19_Re: Date comparison18_Pratheek, Dil (IT)30_Dil.Pratheek@MORGANSTANLEY.COM31_Tue, 29 Nov 2005 18:52:27 -0500835_iso-8859-1 Hi Grace,
Are you sure you are displaying all the values from your result set? Are you running this thru SPUFI? Did you try to run this thru TEP2?
Thanks,
Dil Pratheek
Hi list, We are running DB2 V8 New function Mode for Z/OS. There is a very strange result on the date comparison. Can anyone there explain this result? . SELECT FMGOU.BR_NBR, FMGME.ACCT_NBR, P20_ACTTYP.SHRT_NM, FMGAC.SUB_NBR, FHGAS.END_DT_ID, FHGAS.BALANCE_LOW_MTH, FHGAS.BAL_INT_MIN_MTH, FHGAS.BALANCE, P20_ACTSTS.SHRT_NM FROM FMGOU, FMGME, TDWHP0S.FCVCL_ACTTYP P20_ACTTYP, FMGAC, FHGAS, TDWHP0S.FCVCL_ACTSTS P20_ACTSTS WHERE ( FMGOU.OU_ID=FMGME.OU_ID ) AND ( FMGOU.CU_NBR=FMGME.CU_NBR ) AND ( FMGAC.ACCT_ID=FHGAS.ACCT_ID ) AND ( P20_ACTTYP.CL_ID=FMGAC.ACCT_TYPE_ID ) AND ( FMGAC.ACCT_STATUS_ID=P20_ACTSTS.CL_ID ) [...]
3172 91 19_Re: Date comparison18_Pratheek, Dil (IT)30_Dil.Pratheek@MORGANSTANLEY.COM31_Tue, 29 Nov 2005 18:52:27 -0500372_iso-8859-1 Hi Grace,
Are you sure you are displaying all the values from your result set? Are you running this thru SPUFI? Did you try to run this thru TEP2?
Thanks,
Dil Pratheek
Hi list, We are running DB2 V8 New function Mode for Z/OS. There is a very strange result on the date comparison. Can anyone there explain this result? [...]
3264 141 11_Re: Trigger45_Cyr, Daniel P - Wilkes Barre, PA - Contractor21_daniel.p.cyr@USPS.GOV31_Tue, 29 Nov 2005 18:27:41 -08001278_iso-8859-1 John,
I use DSNTEP2 and it works just fine.
//YOURIDCC JOB (DBA02),'TRIGSSNN', // CLASS=P,MSGLEVEL=1,MSGCLASS=H,REGION=0M,TIME=NOLIMIT, // NOTIFY=&SYSUID /*JOBPARM SYSAFF=*,L=99999 //*+-----------------------------------------------------------------+ //*| JCL Stored in Dataset: ??????.???.JCL(??????) | //*+-----------------------------------------------------------------+ //*| new trigger ??????.TRIGSSNN | //*+-----------------------------------------------------------------+ //CCB603A EXEC PGM=IKJEFT1B,DYNAMNBR=119 //STEPLIB DD DISP=SHR,DSN=SYS3.SSID.DSNEXIT // DD DISP=SHR,DSN=SYS3X.DB2.SSID.LOAD //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(SSID) RUN PROG(DSNTEP2) PLAN(DSNTEP2) LIB('SYS3.SSID.RUNLIB.LOAD') END /* //SYSIN DD DISP=SHR,DSN=HIGHLVL.???.????????(MEMBER) See MEMBER below //*+-----------------------------------------------------------------+ //*| REBIND trigger ???????? | //*+-----------------------------------------------------------------+ //REBIND EXEC PGM=IKJEFT1B,DYNAMNBR=119 //STEPLIB DD DISP=SHR,DSN=SYS3.SSID.DSNEXIT // DD DISP=SHR,DSN=SYS3X.DB2.SSID.LOAD //ABNLIGNR DD DUMMY //SYSTERM DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSTSIN DD * DSN [...]
3406 23 36_Resolving deadlocks on DB2/UDB - Aix10_teldb2kals22_teldb2kals@BIGPOND.COM31_Wed, 30 Nov 2005 21:20:51 +1100584_utf-8 Hi,
I am trying to trace the occurrence of lots of deadlocks within a newly developed application when used by multiple users. It uses DB2/UDB on Aix.
The application has been developed using a tool called Hibernate, which generates and executes queries dynamically. I have turned on a couple of event monitors on locks and transactions, and also wading thru heaps of o/p from my event monitors and explains, trying to pinpoint the exact cause, but in the meantime I wanted to check with others on the list to see if they can suggest the best way to go about [...]
3430 22 35_Re: z/OS DB2 V7.1 S813-04 abend....10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 30 Nov 2005 11:38:20 +0100387_US-ASCII Raquel
to check if the dump has the dataset(s) you need you can do a RESTORE(*.**) with the option TYPRUN=NORUN.This produce a list of datasets to be restored without really restoring them (it's a simulation). On the other end it could be a SMS problem so try to allocate from ISPF 3.2 a dataset with the same name and characteristics of the dataset to be restored. [...]
3453 66 40_Re: Resolving deadlocks on DB2/UDB - Aix13_Mark Horrocks22_agentlease@HOTMAIL.COM31_Wed, 30 Nov 2005 10:41:08 +0000381_- Hi,
If using UDB V8 then there is an deadlock event monitor called DB2DETAILEDEADLOCK that should be tunrned on automatically. you should then be able to use db2evmon to dump the trace.
Have you tried db2pd -locks
Also increase your diaglevel to 4 so you can see the deadlocks.
Also look at your instance.nfy log to see the thresholds being hit. [...]
3520 239 36_Antwort: Re: [DB2-L] Date comparison11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 30 Nov 2005 11:45:19 +0100616_us-ascii looks like V8 has a problem with IN LIST and stage two predicates perhaps?? How is the column FHGAS.END_DT_ID defined exactly? Perhaps the problem lies therein??
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strasse 5 40470 Duesseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-35 E-mail r.boxwell@seg.de Homepage www.seg.de
"Pratheek, Dil (IT)" Gesendet von: DB2 Data Base Discussion List 30.11.2005 00:52 Bitte antworten an DB2 Database Discussion list at IDUG [...]
3760 84 39_Z/OS DB2 V7:: SQL Tuning Basic Question21_Sriramulu, Vijayababu28_vijayababu.sriramulu@EDS.COM31_Wed, 30 Nov 2005 16:29:01 +0530788_us-ascii Dear list users,
This is one of the basic beginner question I have.
When do we use the predicates 0=1 and 1=1 while tuning queries and what are their implications ?
Any hints/ideas/links is greatly appreciated.
As always, thanks for your time.
Regards Vijay
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
3845 363 43_Re: Z/OS DB2 V7:: SQL Tuning Basic Question19_Alekos Papadopoulos13_apapad@NBG.GR31_Wed, 30 Nov 2005 13:10:30 +0200804_iso-8859-7 Hello Vijay,
A quick try:
0=1 (always false) is sometimes ORed with another predicate in order to make it not indexable.
1=1 (always true) can be used to force the materialization of a view
For more details search in Admin Guide for "0=1" and for "1=1"
hth
Alekos
---------------------------------------------------------------------------- ----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at [...]
4209 244 36_Antwort: Re: [DB2-L] Date comparison11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 30 Nov 2005 11:45:19 +0100616_us-ascii looks like V8 has a problem with IN LIST and stage two predicates perhaps?? How is the column FHGAS.END_DT_ID defined exactly? Perhaps the problem lies therein??
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Strasse 5 40470 Duesseldorf/Germany Tel. +49 (0)211 96149-0 Fax +49 (0)211 96149-35 E-mail r.boxwell@seg.de Homepage www.seg.de
"Pratheek, Dil (IT)" Gesendet von: DB2 Data Base Discussion List 30.11.2005 00:52 Bitte antworten an DB2 Database Discussion list at IDUG [...]
4454 126 19_Re: Date comparison9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 30 Nov 2005 08:07:33 -0600455_Windows-1252 I can only think of 2 possible answers. 1. broken index somewhere in one of the access paths 2. optimizer error - one of those PTF's that are marked as INCORR.
IBM will ask you to run check index on all the tables as a starting diagnostic so that won't hurt. RUNSTATS REPORT ALL shouldn't change anything and will give you a second chance to verify structures. collect the access path from explain and check all the indexes used. [...]
4581 113 40_Re: Resolving deadlocks on DB2/UDB - Aix0_28_David.Stritzinger@ALTRIA.COM31_Wed, 30 Nov 2005 09:17:26 -0500359_- Kals,
In addition to the suggestions from Mark let me also remind you of the SNAPSHOT_LOCKWAIT table function. This will only show those applications that are currently waiting on locks and not all locks that are being held ( This can help to reduce the o/p you are looking at).
If you are on the system you can run the following syntax: [...]
4695 146 19_Re: Date comparison13_Keemle Joan T25_KeemleJoanT@JOHNDEERE.COM31_Wed, 30 Nov 2005 08:27:02 -0600491_us-ascii It might be helpful to list the DDL for the FHGAS table and tablespace.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Bell Sent: Wednesday, November 30, 2005 8:08 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Date comparison
I can only think of 2 possible answers. 1. broken index somewhere in one of the access paths 2. optimizer error - one of those PTF's that are marked as INCORR. [...]
4842 149 19_Re: Date comparison13_Keemle Joan T25_KeemleJoanT@JOHNDEERE.COM31_Wed, 30 Nov 2005 08:27:02 -0600491_us-ascii It might be helpful to list the DDL for the FHGAS table and tablespace.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Bell Sent: Wednesday, November 30, 2005 8:08 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Date comparison
I can only think of 2 possible answers. 1. broken index somewhere in one of the access paths 2. optimizer error - one of those PTF's that are marked as INCORR. [...]
4992 179 37_Using FASTSWITCH YES in Online Reorgs13_Goertz, Duane29_Duane.Goertz@COMMERCEBANK.COM31_Wed, 30 Nov 2005 09:54:16 -0600459_us-ascii
Is there any reason not to use FASTSWITCH YES in an online reorg (but rather to opt for FASTSWITCH NO which causes the SWITCH phase to use IDCAMS RENAME)? I realize FASTSWITCH YES is faster but are there situations where its use isn't advisable or where it can cause problems? We currently use FASTSWITCH NO and I'm trying to determine if there's any reason not to change to FASTSWITCH YES. We are using DB2 for z/OS version 7.1 [...]
5172 164 41_Re: Using FASTSWITCH YES in Online Reorgs13_Michael Ebert18_mebert@AMADEUS.COM31_Wed, 30 Nov 2005 17:45:00 +0100403_ISO-8859-1 With FASTSWITCH YES, you have a mixture of *.I0001.* and *.J0001.* VSAM dataset names. You have to take that into account if you access VSAM files directly, e.g. if you use DSN1COPY to populate a test database.
Also a DS list of a partitioned object won't be sorted in an orderly manner (by partition) if you've got a mixture of I and J datasets from running partition-level OLR. [...]
5337 197 80_The Verdict Is In, Users Attest - 20% Savings and More When Using EZ-DB2 !!!!!!!10_David Kane14_dkane@TACT.COM31_Wed, 30 Nov 2005 12:56:31 -0500777_iso-8859-1 EZ-DB2 Delivers Instant ROI by Saving CPU usage
CLICK HERE to sign up for a free Webinar Session
Testimonial 1: A Major US client reports over 25% prime-time DB2 CPU savings
Testimonial 2: A Leading Global Insurer and Financial Services provider reports 20% CPU savings
To learn how you too can achieve savings in your CPU usage, join us online for a free EZ-DB2 "Webinar" presentation: "How EZ-DB2 Captures and Consolidates Dynamic SQL from the Distributed Environment, and helps you dramatically improve your Mainframe DB2 Performance." [...]
5535 315 43_Re: DB2 z/OS V7 - Avoiding SORT on ORDER BY15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Wed, 30 Nov 2005 16:16:30 -0500345_ISO-8859-1 Ray,
Try adding "Table2.COLA = Table2.COLA" to the query to encourage DB2 to use Table2 as the outer table for this query. It looks like you're getting Table1 as the outer query; but since you then join on Table1.COL2 = Table2.COLA, and COL2 is not in order in the index, the result set is not ordered by COLA, COLB, etc. [...]
5851 233 61_Re: How do I feed Turkish, Greek and English in the same DB2?15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Wed, 30 Nov 2005 16:23:40 -0500602_ISO-8859-7 zakis, Phil's right, that's what Unicode was made for. For things like the "Product Name" column where the product name on the jar could be in Arabic, Chinese, English, Farsi, French, Greek, Hebrew, Spanish, or Turkish, just to pick one example not at random from the grocery business. Do the Lebanese speak Arabic, or a language of their own? (Fortunately no one's asked _me_ to solve this kind of problem, _yet_.) --Phil S. On 11/24/05, zakis wrote: > > I already had Unicode in mind and I was basically hoping for a less time > and resource consuming procedure. [...]
6085 27 19_Re: Date comparison33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Wed, 30 Nov 2005 23:09:27 +0100390_iso-8859-1 The Visual Explain feature "Service SQL" is very useful to capture the info for IBM. I believe IBM support will ask for this.
Roland
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Keemle Joan T Sent: Wednesday, November 30, 2005 3:27 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Date comparison [...]
6113 17 41_Index Reorgs following dummy LOAD REPLACE10_Jeff Kluth19_jeff.kluth@WAMU.NET31_Wed, 30 Nov 2005 15:58:49 -0600295_- We have a large partitioned table that gets emptied weekly via a dummy LOAD REPLACE. Data is then Inserted into throughout the week.. The indexes have never been reorged... I believe that there is still value in performing a reorg on these indexes to clean up the logically deleted data. [...]
6131 48 45_Re: Index Reorgs following dummy LOAD REPLACE16_Trivedi, Kaushal34_Kaushal.J.Trivedi@CITIZENSBANK.COM31_Wed, 30 Nov 2005 17:21:11 -0500443_us-ascii The index does get rebuild when the load happens. A Hi Jeff,
The index does get rebuild when the load happens. A separate rebuild index may not be required at that point.
HTH
-KT
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Sent: Wednesday, November 30, 2005 4:59 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Index Reorgs following dummy LOAD REPLACE [...]
6180 61 45_Re: Index Reorgs following dummy LOAD REPLACE9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 30 Nov 2005 16:29:07 -0600329_Windows-1252 load replace resets all the indexes to clean and empty if it specifies the full table not just one partition.
If you are doing rolling partitions, then you may be leaving deleted data for the next insert or not, depending on the key structure. You should be able to tell from the runstats for the index. [...]
6242 86 32_UNLOAD Duplicates on TIME column0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 30 Nov 2005 17:10:00 -0600395_US-ASCII Hello, DB2 V7.1, z/OS 1.4 here.
After our Load utility choked with duplicates from an unloaded (UNLOAD utility) production table, I tried the following cross-system loader.
EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM DBP1DDF.DBAPROD.SFT_AWAR_DETL ORDER BY 1, 2, 3, 4, 5, 6, 7 ENDEXEC LOAD DATA REPLACE INCURSOR(C1) LOG NO NOCOPYPEND INTO TABLE DBATEST.SFT_AWAR_DETL [...]
6329 57 49_Calling BMC Fast Unload from CA-Migrator Strategy13_Douglas Hecht23_douglas_hecht@MEDCO.COM31_Wed, 30 Nov 2005 17:38:48 -0600303_- I am receiving a Return Code 20 when attempting to execute BMC Fast Unload from the CA Batch Processor. I can run ADUUMAIN natively (outside of CA) including the same libs I'm using in Batch Processor, the same exec parms, and the same control card specifications, and it completes successfully. [...]
6387 94 53_Re: Calling BMC Fast Unload from CA-Migrator Strategy9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 30 Nov 2005 18:15:40 -0600691_Windows-1252 standard APF auth issue would be my guess. call BMC support for the full explanation.
Mike Bell HLS Technologies
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Douglas Hecht Sent: Wednesday, November 30, 2005 5:39 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Calling BMC Fast Unload from CA-Migrator Strategy
I am receiving a Return Code 20 when attempting to execute BMC Fast Unload from the CA Batch Processor. I can run ADUUMAIN natively (outside of CA) including the same libs I'm using in Batch Processor, the same exec parms, and the same control card specifications, and it completes [...]
6482 97 53_Re: Calling BMC Fast Unload from CA-Migrator Strategy9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 30 Nov 2005 18:15:40 -0600691_Windows-1252 standard APF auth issue would be my guess. call BMC support for the full explanation.
Mike Bell HLS Technologies
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Douglas Hecht Sent: Wednesday, November 30, 2005 5:39 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Calling BMC Fast Unload from CA-Migrator Strategy
I am receiving a Return Code 20 when attempting to execute BMC Fast Unload from the CA Batch Processor. I can run ADUUMAIN natively (outside of CA) including the same libs I'm using in Batch Processor, the same exec parms, and the same control card specifications, and it completes [...]
6580 60 49_Calling BMC Fast Unload from CA-Migrator Strategy13_Douglas Hecht23_douglas_hecht@MEDCO.COM31_Wed, 30 Nov 2005 17:38:48 -0600303_- I am receiving a Return Code 20 when attempting to execute BMC Fast Unload from the CA Batch Processor. I can run ADUUMAIN natively (outside of CA) including the same libs I'm using in Batch Processor, the same exec parms, and the same control card specifications, and it completes successfully. [...]
6641 57 43_Re: Z/OS DB2 V7:: SQL Tuning Basic Question14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 1 Dec 2005 12:34:10 -0800439_US-ASCII "0=1" has often been used to "trick" the optimiser into not choosing to access data via an specific index. For example AND (COL1 = :hv OR 0=1) The optimiser would have to check every index entry on the off chance that the "0=1" predicate were true - not just the few that satisfy the other predicate. The extra getpages needed for this should have been enough to discourage access via an index with COL1 as its first column. [...]
6699 145 36_Re: UNLOAD Duplicates on TIME column14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 1 Dec 2005 12:34:10 -0800341_US-ASCII EXEC SQL DECLARE C1 CURSOR FOR SELECT cola, ... , char(col_time, ISO) , ... FROM ... ?
A better option would be to change your default time format to ISO - but that does depend on whether your installation can handle the change.
James Campbell
On 30 Nov 2005 at 17:10, Steve_Grimes@AISMAIL.WUSTL.ED wrote: [...]