1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2005, week 5
2 89 28_Access path and index choice12_Adam Baldwin19_y0027528@ES.IBM.COM31_Thu, 29 Dec 2005 03:46:33 -0600294_- Fellow listers: Can anyone throw some light on this.
We have a query:
SELECT * FROM MYTAB WHERE ( CODISER = '11' AND OFIAPE = '111' AND NUMECTA = '111111' AND DIGICTA = '1' AND ENTIDAD = 1 AND NIF ='X11111111' AND SUFNIF IN ( '100',' ') AND REFER IN ( '000030013912',' ')) [...]
92 114 32_Re: Access path and index choice13_Mertens, Bart18_bart.mertens@CZ.NL31_Thu, 29 Dec 2005 11:13:11 +0100457_us-ascii Adam,
In order to answer your question we need to know the table/column statistics. NIF probably has a high filter factor.
-----Oorspronkelijk bericht----- Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Namens Adam Baldwin Verzonden: donderdag 29 december 2005 10:47 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: [DB2-L] Access path and index choice
Fellow listers: Can anyone throw some light on this. [...]
207 38 32_Re: Access path and index choice12_Adam Baldwin19_y0027528@ES.IBM.COM31_Thu, 29 Dec 2005 04:24:26 -0600467_- Sorry - I forgot the col stats:
NAME COLNO COLCARD ------------------ ------ ----------- CODISER 1 11 OFIAPE 2 236 NUMECTA 3 57344 DIGICTA 4 10 ENTIDAD 5 1792 NIF 6 15178 SUFNIF 7 712 REFER 8 165888 IMPORTE 9 4224 FEINIDES 10 7936 FEFINDES 11 848 FEALTA 12 6208 FEULMOD 13 6208 HORULMOD 14 40960 OFIULMOD 15 296 NUMTER 16 1344 USUARIO 17 2176 A2000E_MNDORIG 18 2 A2000E_EUR_IMP 19 3008 A2000N_FEULMOD 20 5248 A2000N_FEALTA 21 5248 A2000_DUMMY 23 623088 [...]
246 40 11_DB2 V8 ENFM13_Palko, George16_gpalko@OPERS.ORG31_Thu, 29 Dec 2005 08:13:24 -0500539_iso-8859-1 We are in the process of migrating from DB2 V8 CM to NF Hi List,
We are in the process of migrating from DB2 V8 CM to NFM. However, we've encountered an issue where a number of our third-party vendors are not yet ready to exploit V8 NFM. To expedite the process of migrating, we've decided to migrate to NFM and then convert back to ENFM, thus disabling new functions. My question is: is it possible for a DBRM that was created within ENFM with NEWFUN set to NO to be re-bound in a V8 CM subsystem? We have multiple [...]
287 35 25_DB2 V8 NFM/ENFM migration12_George Palko16_gpalko@OPERS.ORG31_Thu, 29 Dec 2005 07:08:45 -0600552_- Hi List,
We are in the process of migrating from DB2 V8 CM to NFM. However, we've encountered an issue where a number of our third-party vendors are not yet ready to exploit V8 NFM. To expedite the process of migrating, we've decided to migrate to NFM and then convert back to ENFM, thus disabling new functions. My question is: is it possible for a DBRM that was created within ENFM with NEWFUN set to NO to be re-bound in a V8 CM subsystem? We have multiple development regions and my plan is to have the ENFM development subsystems in [...]
323 99 29_Re: DB2 V8 NFM/ENFM migration0_17_sjvagnier@AEP.COM31_Thu, 29 Dec 2005 08:38:45 -0500372_US-ASCII George, IBM recommends the time window between ENFM and NFM be short in duration. That said, I would recommend deferring any migration changes for applications until the ENFM testing is completed. If that is not possible, you could migrate to ENFM in production and then do the application migrations.
The scenario you describe seems too risky to me. [...]
423 302 32_Re: Access path and index choice15_Pradeep Gunjala31_Pradeep_Gunjala@TAX.STATE.NY.US31_Thu, 29 Dec 2005 09:37:06 -0500509_US-ASCII Can you check the Cluster ratio of both the indexes.
- Pradeep
Adam Baldwin Sent by: DB2 Data Base Discussion List 12/29/2005 04:46 AM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject [DB2-L] Access path and index choice
Fellow listers: Can anyone throw some light on this. [...]
726 10 32_Re: Access path and index choice12_Adam Baldwin19_y0027528@ES.IBM.COM31_Thu, 29 Dec 2005 09:32:11 -0600533_- 99% for the clustering index and 49% for the non clustering.
--------------------------------------------------------------------------------- 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
737 357 0_9_Ali, Omar25_Omar.Ali@CITYOFBOSTON.GOV31_Thu, 29 Dec 2005 15:19:26 -0500393_- Do any one know a simple way to write this sql;
I am trying to perp. For a refresh insert into my test environment The sql that works is as follow;
SELECT DISTINCT 'SELECT * FROM prod ENVIRONMENT.'||NAME||' ;'
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'PRODUCTION environment'
AND TYPE = 'T'
AND CARD > 0
--ORDER BY DBNAME [...]
1095 370 3_Re:13_Richard Fazio21_rfazio@TRANSUNION.COM31_Thu, 29 Dec 2005 14:39:33 -0600387_US-ASCII I don't believe this is supported.
However, the IBM Load utility does have the "Cross load feature". (your LOAD JCL here) //SYSIN DD * EXEC SQL DECLARE C0001 CURSOR FOR SELECT * FROM PROD.TABLE; ENDEXEC LOAD DATA INCURSOR(C0001) LOG NO NOCOPYPEND REPLACE INTO TABLE TEST.TABLE STATISTICS TABLE(ALL) INDEX(ALL)
Although, you cannot run with SHRLEVEL CHANGE :( [...]
1466 91 54_Looking for Part-Time DB2 DBA opportunity - z/OS, LUW.15_Basivi Inaganti30_basivi.r.inaganti@JPMCHASE.COM31_Thu, 29 Dec 2005 15:42:50 -0500643_ISO-8859-1 Hi All, These days, I have been looking desperately for a part-time opportunity as DB2 DBA where I can work remotely form Columbus, OH. Please let me know if you have/heard any opening. Thanks in advance.
Experience Summary · Eleven years+ in software design, development, database administration, systems programming which includes working experience in DB2 UDB for z/OS, DB2 UDB for LUW. · Systems Programming: One+ years in DB2 for z/OS in data sharing and sysplex environment, installation and maintenance of OEM products. · Database administration: Seven in DB2 for OS/390 and z/OS, Three years in DB2 UDB for LUW. [...]
1558 250 3_Re:15_Basivi Inaganti30_basivi.r.inaganti@JPMCHASE.COM31_Thu, 29 Dec 2005 15:52:53 -0500563_US-ASCII Hi, Try the following. SELECT DISTINCT 'INSERT INTO '||NAME||' ', 'SELECT * FROM ENVIRONMENT.'||NAME||' ;' Thanks, Basivi.
"Ali, Omar" Sent by: DB2 Data Base Discussion List 12/29/2005 03:19 PM Please respond to DB2 Database Discussion list at IDUG
To: DB2-L@www.idugdb2-l.org cc: Subject: [DB2-L]
Do any one know a simple way to write this sql; I am trying to perp. For a refresh insert into my test environment The sql that works is as follow; [...]
1809 108 35_Re: Sql to load prod data into test10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 30 Dec 2005 07:55:29 +1100381_utf-8 Hi Omar,
You need a Union to do this.
(Or, the other option is, do it as you have done, and use some edit macro to split the line into two).
Try :
SELECT stmt FROM
( SELECT DISTINCT NAME, 'SELECT * FROM prod ENVIRONMENT.'||NAME||' ;' as stmt FROM SYSIBM.SYSTABLES WHERE DBNAME LIKE 'PRODUCTION environment' AND TYPE = 'T' AND CARD > 0 [...]
1918 330 3_Re:7_T. Chao20_Terry.F.Chao@FRB.GOV31_Thu, 29 Dec 2005 15:46:31 -0500433_US-ASCII Try below, and, on result, right-shift off excess columns using combination of 'BNDS' and '))nnn' to get what you want:
SELECT 'INSERT INTO PROD.ENV.'||NAME,DBNAME,NAME,1 FROM SYSIBM.SYSTABLES WHERE DBNAME LIKE 'test%' AND TYPE = 'T' AND CARD > 0 UNION SELECT 'SELECT * FROM TEST.ENV.'||NAME||';',DBNAME,NAME,2 FROM SYSIBM.SYSTABLES WHERE DBNAME LIKE 'test%' AND TYPE = 'T' AND CARD > 0 ORDER BY 2,3,4 WITH UR ; [...]
2249 173 35_Re: Sql to load prod data into test10_Doyle Mark21_Mark.Doyle@WIN-NA.COM31_Thu, 29 Dec 2005 15:47:39 -0600363_us-ascii Omar, Just to piggyback on what Kals wrote, His SQL assumes no RI, and no Identity columns. If that is your situation, his SQL is what you need. If, however, you have identity columns generated always the SQL gets more complex (see below). If you need to insert in an RI structure, contact me direct, as that sql (undocumented) is > 600 lines long. [...]
2423 13 49_Creating stored procedures using WebSphere Studio13_Waldo Tumanut52_Waldo_Tumanut/MO/americancentury@AMERICANCENTURY.COM31_Thu, 29 Dec 2005 15:55:47 -0600687_- Using WSAD 5.1, when I right-click Stored Procedures -> New, the SQL Stored Procedure and Java Stored Procedure are grayed out. Has anyone have this problem before? Did I miss something in the installation of WSAD?
--------------------------------------------------------------------------------- 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
2437 76 67_Re: Creating stored procedures using WebSphere Studio -- Never mind0_33_waldo_tumanut@AMERICANCENTURY.COM31_Thu, 29 Dec 2005 16:29:55 -0600552_US-ASCII Sorry, too quick to send out this e-mail. I'm learning WSAD and I just figured it out.
Waldo Tumanut Database Analyst
Waldo_Tumanut/MO /americancentury Sent by: DB2 Subject Data Base [DB2-L] Creating stored procedures Discussion List using WebSphere Studio
12/29/2005 03:55 PM
Please respond to DB2 Database Discussion list at IDUG [...]
2514 38 32_Re: Access path and index choice14_Larry Kintisch19_lkint@IX.NETCOM.COM31_Thu, 29 Dec 2005 21:42:17 -0800300_us-ascii Adam, I don't know what version of DB2 you are using. Older versions didn't like the "integer value" 1 that you supplied for ENTIDAD that was defined as DECIMAL (4,0); the index wasn't used. Try the query with "...AND ENTIDAD = 1. " putting the decimal point in as part of the literal. [...]
2553 17 32_Re: Access path and index choice12_Adam Baldwin19_y0027528@ES.IBM.COM31_Fri, 30 Dec 2005 01:22:39 -0600348_- We're running V7 on OS/390 02.10.00. The decimal point in the literal made no difference. Also I received an email reply in relation to PTF PQ97866: INEFFICIENT INDEX CHOSEN FOR SINGLE-TABLE QUERY WHEN COMPETING INDEXES HAVE DIFFERENT NLEVELS 05/05/05 PTF PECHANGE. Both indexes have NLEVEL = 3 so I don't think that this will be it either. [...]
2571 37 26_xLOBs and daily management10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 30 Dec 2005 12:59:11 +0100584_US-ASCII Esteemed listers
First of all best wishes for a new & happy 2006, may this year bring a zilliard of dollars to all listers and tons of high-pressure and high-temperature carbon, better know as diamond, to all DB2-L ladies. Or whatever that can make you happy......
We have a project of implementing a new application with CLOBs and BLOBs (and XML) and for this reason I'm reading redbook and some papers. The redbook 'Large objects with DB2 for z/OS and OS/390' is good and provides a good picture for table(spaces) and indexes management as BP,reorg etc. [...]
2609 56 32_Re: Access path and index choice14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM31_Fri, 30 Dec 2005 06:15:01 -0600511_US-ASCII Adam, I don't have a solution for you, but what about either dropping the NC index or using opthint or some additional predicates (and NUMECTA = NUMECTA) to try to influence the optimizer to use the clustering index and see if you actually do get a better throughput ?
Adam Baldwin Sent by: DB2 Data Base Discussion List 12/30/2005 01:22 AM Please respond to DB2 Database Discussion list at IDUG [...]
2666 201 35_Re: Sql to load prod data into test9_Ali, Omar25_Omar.Ali@CITYOFBOSTON.GOV31_Fri, 30 Dec 2005 07:39:04 -0500635_- Thanks Guys, For all the help/advise And have a happy new year
Omar Ali Jr.Database Admin x0729
-----Original Message----- From: Doyle Mark [mailto:Mark.Doyle@WIN-NA.COM] Sent: Thursday, December 29, 2005 4:48 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Sql to load prod data into test
Omar, Just to piggyback on what Kals wrote, His SQL assumes no RI, and no Identity columns. If that is your situation, his SQL is what you need. If, however, you have identity columns generated always the SQL gets more complex (see below). If you need to insert in an RI structure, contact me direct, as that sql [...]
2868 103 30_Re: xLOBs and daily management14_Avram Friedman21_avramf@IBMSYSPROG.COM31_Fri, 30 Dec 2005 05:12:38 -0800573_iso-8859-1 Hi Max Happy 2006 to you as well. You are reading RedBooks? I thought you just collected them.
Some of the problems I have experienced with LOBs and BLOBs are 1. Many retrieval functions / utilities do not work. Which ones are a moving target and is highly dependent on which vendor is hyping the the quality of support at the second but the basic problem is its a trick for standard access methods to support physical blocks greater than 32K. Things that work by physical block like IBM's copy and recovery tend to work, things that work by logical [...]
2972 35 10_DB2 Audits13_Arnold, Kevin17_karnold@OPERS.ORG31_Fri, 30 Dec 2005 08:17:22 -0500607_iso-8859-1 Our internal audit department has asked for suggestions on how to monitor and verify DDL/DML changes to our production z/OS DB2 v8 database are being performed in an authorized manner. Obviously we have a change control process which requires appropriate signoff's. Beyond that, we produce a report based upon log files which shows what DDL/DML has been done, by whom, and when. We do not currently go back and validate that those changes were all appropriate and authorized, but will do spot checks on occasion. Our DBA's have DBADM, a couple of tech's have Install SYSADM, and we are soon [...]
3008 104 30_Re: xLOBs and daily management0_24_Jim.Rohal@HUNTINGTON.COM31_Fri, 30 Dec 2005 08:23:02 -0500376_us-ascii Max,
The only issues I've had is dealing with space. You can reorg the LOB's (shrlevel none) after deleting old data, but it will not redefine the dataset to eliminate extents. You will see the space available as freespace in a listcat however.
If you want to have the datasets recreated to change the space allocations, the process I use: [...]
3113 121 30_Re: xLOBs and daily management12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 30 Dec 2005 07:14:43 -0800547_iso-8859-1 Max, I'd fully concur with Avram and Jim's comments. Space management and the use of utilities is a real problem with LOBS/BLOBS. Space issues have caused my client many issues with them.
Personally I feel that IBM has put some features into DB2 without fully functional utilities to support the features. I think it's another case where they wanted to trumpet that they had support for some features without fully understanding all the real production issues for that feature. But this is strictly my own personal opinion. [...]
3235 35 30_Re: xLOBs and daily management10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 30 Dec 2005 16:30:42 +0100543_US-ASCII Hi Avram, Jim and Myron
Thank you for replies.
As I never stop learning, I've to read redbooks and papers :-))) . I did some test in the past with xLOBs but I hadn't a real application using it so it was a mere exercise with few rows.
I was awared of problems after reorgs with space, I read some posts in DB2-L and agree with Avram that size matters. And yes I agree from my (very limited) tests that this feature seems to me not yet 'mature' and fully supported and its usage is not so user-friendly as in [...]
3271 68 32_Re: Access path and index choice12_Colleen Clow23_Colleen_Clow@BCBSIL.COM31_Fri, 30 Dec 2005 12:30:59 -0600504_us-ascii Maybe there's something about the index itself that makes the second index a better choice. Have you checked the sysindexepart table? Maybe the index just needs to be reorganized because it's in a lot of extents or it's got a high number of the far leaf pages...?
cmc
"Adam Baldwin" cc: Sent by: "DB2 Subject: Re: [DB2-L] Access path and index choice Data Base Discussion List" [...]
3340 23 42_Any disadvantages by using 0 = 1 predicate15_Pradeep Gunjala31_pradeep_gunjala@TAX.STATE.NY.US31_Fri, 30 Dec 2005 15:49:34 -0600400_- Hello List,
We know that optimizer is not chosing the right index for one of our SQL so we have suggested to use 0 = 1 predicate to influence the access path so that it would consider the right index. But are there any disadavantages with this approach down the road. Does IBM has stated in any manual to use this as a lost resort. I was asked by our management and I need your inputs. [...]
3364 102 21_Re: Holiday Greetings16_Vaughan Phillips24_Vaughan.Phillips@QAS.COM31_Thu, 29 Dec 2005 08:50:06 -0000456_iso-8859-1 Frank, Feel free to add the Welsh Christmas greeting to your list, which is "Nadolig Llawen a Blwyddyn Newydd Dda." It translates to Merry Christmas and a Happy New Year.
Cheers,
Vaughan Phillips.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of The Fillmore Group, Inc. Sent: 23 December 2005 19:03 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Holiday Greetings [...]
3467 44 46_Re: Any disadvantages by using 0 = 1 predicate14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 31 Dec 2005 13:59:11 +1100424_US-ASCII I recall reading somewhere that the DB2 zOS V8 optimizer has the smarts to discard the "or 0=1" predicate as it can never be true. You can draw your own conclusions.
The officially documented method is to use "+ 0" for numeric items or "concat ''" (that's a zero length string) for character items to disable indexability. (Admin Guide, predicate indexabilty section, one of the notes after the chart.) [...]
3512 38 46_Re: Any disadvantages by using 0 = 1 predicate13_William Huang24_hhuang@DCCSH.ICBC.COM.CN31_Sat, 31 Dec 2005 11:12:04 +0800303_US-ASCII If my memory serves me corretly ,this type of statement would be flagged as 'Pruned' ,and never be executed actually, and NOT impact your access path, perhaps begins from V7? So, it depends on where you stand. You can make it with V6 or lower versions.
Wish all a happy new year!!! [...]
3551 59 30_Re: xLOBs and daily management12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 31 Dec 2005 11:20:05 +0200455_us-ascii Hi,
Another venue :-)
We had a CLOB system ("had" is the correct time:-) ) After being hit by all the "good" mentioned before benefits we decided that we'll convert it to VARCHAR(32000) and use "sequence" numbers for managing the order of the rows. We had to change some programs, but it was a good decision for us. YMMV. We had objects up to 200KB with the majority in the range 40-80KB. I did not test it for bigger objects. [...]
3611 22 30_Re: xLOBs and daily management10_Max Scarpa16_mscarpa@CESVE.IT31_Sat, 31 Dec 2005 10:36:19 +0100302_US-ASCII Hi ISaac This application will manage object up to 1 Mb as CLOB and XML each so 32k is not enough, we have to use xLOBs.
But I'm afraid it'll be a hard project, for space requested 'in primis' and for managing object. BTW how big are dataspace where xLOBs are used ? Just curious. [...]
3634 44 30_Re: xLOBs and daily management12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 31 Dec 2005 13:33:23 +0200325_us-ascii Hi Max,
Happy new year 2006 - that's near 2GB :-)))
Currently we don't have the @#$@ LOBZ, so I don't have number. You'll have to rely on locators as much as you can and really move around those BIG objects as last resort. We were happy to ditch them, but we were lucky. Watch for I/O and paging. [...]
3679 68 46_Re: Any disadvantages by using 0 = 1 predicate13_Terry Purcell18_tpurcel@US.IBM.COM31_Sat, 31 Dec 2005 12:38:44 -0600570_- Pradeep,
The optimizer doesn't prune non-boolean term "OR 0=1", but from V7 (with APAR PQ77454), optimizer does prune some boolean term predicates that can never be true, such as WHERE 0=1.
To answer your original question, any type of trick is absolutely a last resort in my opinion.
The preferred approach is instead to determine what the root cause is and resolve that. So that other queries don't suffer the same issue. You don't want to have to use tricks on every similar SQL if the problem can be resolved at the table or index level. [...]
3748 294 46_Re: Any disadvantages by using 0 = 1 predicate15_Pradeep Gunjala31_Pradeep_Gunjala@TAX.STATE.NY.US31_Sat, 31 Dec 2005 14:14:46 -0500588_US-ASCII Terry,
Thanks a lot for your reply. I agree. Let me explain you the scenario.
IX1 is defined on COLA IX2 is defined on COLB
SQL:
Select .........FROM TAB WHERE COLA BETWEEN :HV1 AND :HV2 AND COLB = :HV3
But HV1 and HV2 variable are optional that means in all the cases these values may not be known to application, so the developers are populating with Low values in HV1 and High Values in HV2 respectively in those cases. Even though access path shows index access (IX1) it has to scan the entire index space there by table space because [...]
4043 144 46_Re: Any disadvantages by using 0 = 1 predicate13_Terry Purcell18_tpurcel@US.IBM.COM31_Sat, 31 Dec 2005 13:24:47 -0600417_- Pradeep,
The problem I have with using "OR 0=1" on the BETWEEN predicate is that sometimes it will provide filtering, but the optimizer will never be able to exploit this. What's worse is that you make this a stage 2 predicate.
As you are probably aware, REOPT(VARS) is designed exactly for this situation. Another approach is to have 2 separate SQLs and let the application decide which to use. [...]
4188 409 46_Re: Any disadvantages by using 0 = 1 predicate15_Pradeep Gunjala31_Pradeep_Gunjala@TAX.STATE.NY.US31_Sat, 31 Dec 2005 15:06:33 -0500415_US-ASCII Most probably I am going to suggest to split the sql into 2 separate SQLs. Thanks for your help. Have a Great New Year ahead !!
Terry Purcell Sent by: DB2 Data Base Discussion List 12/31/2005 02:24 PM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@WWW.IDUGDB2-L.ORG cc [...]
4598 113 32_Re: Access path and index choice13_Terry Purcell18_tpurcel@US.IBM.COM31_Sat, 31 Dec 2005 14:15:10 -0600420_- It is difficult to provide a definitive answer with such limited detail. Although with what information is available, and given how V7 makes use of KEYCARD statistics for these type of predicates (V8 makes more extensive use of KEYCARD), both indexes will cost out to be very close.
However I do not wish to speculate which V7 zparms or PTFs may alter the index choice here without more detailed analysis. [...]