1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2009, week 5
2 153 59_Re: Query output difference on using new index after rebind10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Mon, 28 Dec 2009 23:59:44 +0000567_utf-8 Thanks, Mike. Yes, we plan to open a PMR.
I have tried to copy the stats to test envt, but unable to reproduce production access path (index2). Still exploring options.
Kals. On Mon, 28 Dec 2009 17:54:05 -0600, Mike Bell wrote:
>Item 1 - time to open a problem report. Incorrect output is one of the more >common problems >Item 2 - first thing IBM will ask for is check index for both index 1 and 2. >Item 3 - access path changes are usually tied to runstats - if the index has >more than 2 columns, you always [...]
156 155 59_Re: Query output difference on using new index after rebind10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Mon, 28 Dec 2009 23:56:53 +0000386_utf-8 Thanks, Richard. I will find out if check index was run (I think it was).
The indexes are on the same table.
I agree, change in index should not change o/p. Hence our confusion. I tried searching for any INCORROUT related PTFs, but nothing seemed applicable.
Kals.
On Mon, 28 Dec 2009 17:45:29 -0600, Fazio, Richard wrote: [...]
312 196 59_Re: Query output difference on using new index after rebind9_Chris Tee25_chris.tee@NOVAMAGIC.CO.UK31_Tue, 29 Dec 2009 00:52:50 +0000623_iso-8859-1
Kals
I've seen this type of incorrout in the past when parallelism is involved. Is the package bound with DEGREE(ANY) or zparm CDSSRDEF=ANY? If so, try rebinding with DEGREE(1). If this sorts it out, open a PMR with IBM.
Chris
> Date: Mon, 28 Dec 2009 22:57:49 +0000 > From: teldb2kals@TELSTRA.COM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] Query output difference on using new index after rebind > > Hi, > > We had a production issue over the weekend where some transactions were > appearing twice, and after a lot of investigation, the only thing that had > changed was [...]
509 162 59_Re: Query output difference on using new index after rebind13_Terry Purcell18_tpurcel@US.IBM.COM31_Tue, 29 Dec 2009 04:09:41 +0000338_utf-8 Hi Kals,
Before opening a PMR, you may want to make sure that this is truly an IBM issue.
If the UPDATE changes the values of columns col5, col4 or col6 (columns of Index2 that do not have equals predicates in the cursor), then the current application design will allow the cursor to see the rows more than once. [...]
672 46 47_AUTO: Sajad Javid is out of the office (Travel)11_Sajad Javid19_sajad.javid@TCS.COM31_Tue, 29 Dec 2009 10:00:42 +0530371_us-ascii I will be out of the office starting 12/28/2009 and will not return until 12/30/2009.
Note: This is an automated response to your message "DB2-L Digest - 28 Dec 2009 to 29 Dec 2009 (#2009-371)" sent on 12/29/09 6:30:01.
You will receive a notification for each message you send to this person while the person is away. [...]
719 118 59_Re: Query output difference on using new index after rebind13_Gerard Le Roy24_gerard.le.roy@WANADOO.FR31_Tue, 29 Dec 2009 07:34:12 +0100374_iso-8859-1 Hi Kals, Updating a row involved in a cursor still opened may give unpredictable result, depending on wether the result table is materialised or not. With index 1 the result table is materialised because of the order clause. With index 2 you might fetch the same row twice. That’s probably te problem. Have a look on SQL Reference describing OPEN statement. [...]
838 71 77_Re: Are the various tokens in SQLERRM and their sequence documented anywhere?15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Tue, 29 Dec 2009 12:02:20 +0200635_us-ascii That is it. I just wanted confirmation on the sequence, that DSNTIAR did not have logic to reorder the tokens.
On Mon, 28 Dec 2009 12:04:31 -0500 "Seibert, Dave" wrote:
:>Hello Binyamin, :> :> I have found that the Codes manual accurately documents the content :>and sequence for SQLERRM. But I have not studied whether this can change :>release to release. It certainly changes between SQLCodes. For instance :>as you say, the -803 first token is indexspace and for -805 the first :>token is package/dbrm name. :>I would be surprised to see the order or contents change, but I [...]
910 35 56_DSNE345I with terminal CCSID blank after RSU0910 applied24_SUBSCRIBE DB2-L Karthick16_karthickn@HCL.IN31_Tue, 29 Dec 2009 11:58:20 +0000355_utf-8 Hi,
I have applied RSU0910 in our system DB2 V8.1 CM.While accessing spufi i received DSNE345I message with terminal CCSID Blank and Spufi CCSID is 37.I don't find any error while executing queries.Can anyone give some idea what has to be done to fix this issue.
Thank you all in advance.
Wish you all very Happy New Year. [...]
946 324 41_Free Optim PoT's - Baltimore, January 7th7_Kim May28_kim.may@THEFILLMOREGROUP.COM31_Tue, 29 Dec 2009 09:24:31 -0500548_us-ascii For those of you in the Baltimore area, please join us if you are interested in learning more about Optim.
Date: Thursday, January 7, 2010
Time: 9:00am - 3:30pm
Location: The Fillmore Group
8501 LaSalle Road, Suite 318
Towson, Maryland, 21286
(410) 465-6335
Abstract
Enterprise applications and databases don't just help run your business - they ARE your business. And every year, they grow in size and complexity - making them harder to manage. [...]
1271 26 60_Re: DSNE345I with terminal CCSID blank after RSU0910 applied12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Tue, 29 Dec 2009 15:22:22 +0000711_utf-8 Check out the possible causes of a blank terminal CCSID under the relevant DSNE345I message in the DB2 V8 Messages manual.
The probable causes and the corrective actions are listed there.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups, there is probably one near you! Regional User Groups are your local connection to the Worldwide DB2 User Community _____________________________________________________________________ [...]
1298 148 44_DB2 Stored procedure called from COBOL CICS.16_mallik vemugunta19_kris_vemu@YAHOO.COM31_Tue, 29 Dec 2009 08:05:01 -0800313_iso-8859-1 I am new to DB2 stored procedure , can you please help me how I can call from COBOL CICS application program. We already have a DB2 stored procedure that is being called from Java application and we had a requirement to call the same procedure from COBOL program, here is CREATE procedure syntax. [...]
1447 66 38_Re: Applying lots of PTFs a DB2 system10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 29 Dec 2009 17:19:06 +0100411_US-ASCII I applied in the past some hundreds PTFs in 'one shot' (I think once they twere 620) + some PTFs for z/Os (for LOBs if I remember well) and I had no problem, apart nuisance to check actions but with appropriate SMP/E options it can be easy. Sometimes some PTFs are SUPed by others so the number could decrease at the very end. Problems could happen if you have PTFs requesting IPL or DB2 restart. [...]
1514 189 59_Re: Query output difference on using new index after rebind16_Mark Labby - AES21_mlabby@AESSUCCESS.ORG31_Tue, 29 Dec 2009 11:17:16 -0500
1704 195 59_Re: Query output difference on using new index after rebind10_Dave Nance16_dav1mo@YAHOO.COM31_Tue, 29 Dec 2009 10:11:22 -0800671_iso-8859-1 This used to be a fairly well known occurance. In fact, several years ago Bonnie Baker wrote an article that covered this on the DB2 Mag. You should have your development staff take a look at it. Terry pretty much covered it, add in predicates to ensure it does not show up again, use FOR UPDATE etc.. David Nance ________________________________ From: Teldb2kals To: DB2-L@WWW.IDUGDB2-L.ORG Date: 12/28/2009 07:46 PM Subject: Re: [DB2-L] Query output difference on using new index after rebind Sent by: "IDUG DB2-L" ________________________________ Thanks, Mike. Yes, we plan to open a PMR. I have tried to [...]
1900 748 59_Re: Query output difference on using new index after rebind13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Tue, 29 Dec 2009 13:49:39 -0500489_utf-8 A version of this is called the Halloween bug, because it was discovered on Halloween .
See Don and Pat discuss it here:
http://www.mcjones.org/System_R/SQL_Reunion_95/sqlr95-System.html#Index1 97
Dave
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dave Nance Sent: Tuesday, December 29, 2009 1:11 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Query output difference on using new index after rebind [...]
2649 180 25_SQL query with variables?7_DB2 DBA21_the.db2.dba@GMAIL.COM31_Tue, 29 Dec 2009 16:38:46 -0500329_ISO-8859-1 Hello:
DB2 V7 z/OS
I have a rather interesting yet strange problem. I thought it thru but with the limited available sources (SQL & SORT), I just couldn't arrive at a solution. Then decided to ask the 'Gurus' for some help.
I have two tables. Both these tables are loaded every second Friday [...]
2830 219 59_Re: Query output difference on using new index after rebind10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 29 Dec 2009 22:10:48 +0000553_utf-8 Thanks, Terry (and all the others who replied). It is clear to me now, and of course, it did bring some old memories back.
I wanted to send off a mail to the list as the answer was not obvious to me immediately (considering the problem happened on a long weekend, and you start seeing duplicate transactions, and the developers had no idea what was wrong as the program had not been changed in a long time !!). I just thought it was wierd that the use of a different index could cause an incorrect output. Your answers clearly explain [...]
3050 134 50_Re: AD: graingerdatabasesolutions.com is now LIVE!16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Tue, 29 Dec 2009 18:13:25 -0500596_utf-8 > Hopefully the list admins will cut me a little festive slack for a > quick > advertisement/announcement. >
I am hurt.... slack.....i ALWAYS give slack to everyone... ;-)
Good luck
p.s. as long as you put 'AD' then no slack needed to be given. It follows the guidelines
Robert Galambos CIPP/C CIPP/IT
Compuware Senior Technical Specialist IBM Certified Database Associate IBM Certified DB2 9 for z/OS Database Administration Certified Information Privacy Professional/Canada Certified Information Privacy Professional/Information Technology [...]
3185 197 60_Re: DSNE345I with terminal CCSID blank after RSU0910 applied28_Balachandran Chandrasekaran119_balaccha@IN.IBM.COM31_Wed, 30 Dec 2009 09:39:31 +0530578_US-ASCII The message guide says, for DSNE345I, when terminal CCSID is blank, DB2 is unable to determine the terminal CCSID. There are 3 reasons stated for so. But, you could overcome this warning message by updating the "3. Blank CCSID warning " on the SPUFI defaults panel-2 to 'NO'. This panel could be reached by typing 'Yes' on the Change defaults option on the SPUFI main panel and again typing 'Yes' against '5. change plan names' on the SPUFI defaults panel. I remember this information is also given either on messages guide or on the technote at IBM support site. [...]
3383 253 59_Re: Query output difference on using new index after rebind13_Gerard Le Roy24_gerard.le.roy@WANADOO.FR31_Wed, 30 Dec 2009 05:37:31 +0100434_iso-8859-1 Hi Kals, We said that it could read the same row twice, but a row that would be fetched with index 1, can be missed with index 2. It depends on the Update. Regards Gerard
-----Message d'origine----- De : IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] De la part de Teldb2kals Envoyé : mardi 29 décembre 2009 23:11 À : DB2-L@WWW.IDUGDB2-L.ORG Objet : Re: [DB2-L] Query output difference on using new index after rebind [...]
3637 482 29_Re: SQL query with variables?28_Balachandran Chandrasekaran119_balaccha@IN.IBM.COM31_Wed, 30 Dec 2009 13:07:40 +0530440_US-ASCII Hi Josh,
I am afraid you might not be able to achieve that in SQL while in DB2 v7. But, DB2 v8 offers recursive SQL using Common table expressions. If you can not wait til v8, is it possible to use QMF programming interface ? I am sure you could write logics with QMF procedure while maintaining the code/logic outside quality controlled environment (viz, changeman, endevor, SCLM ). Below is the SQL that would do it. [...]
4120 41 27_Re: SV: SER.TASK SWTCH high13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Wed, 30 Dec 2009 09:22:15 +0000495_utf-8 Hi again, In a DB2 Statistic Report You should check for:
UNAVAILABLE OUTPUT LOG BUFF 0.00 OUTPUT LOG BUFFER PAGED IN 0.00
Theese values should always be zero otherwise you have to increase the Log Buffer and/or check if you are contstrained on memory. And even if you have your logs on fast devices you should check the response times for the logs. Disk writes used to be fast because they are just written to the disk controller chache which normally is non volatile. [...]
4162 36 23_Data cluster or Index ?14_Prashant Kalal25_prashant.k.kalal@AEXP.COM31_Wed, 30 Dec 2009 09:31:59 +0000443_utf-8 Hi Group , I have a question wrt DB2 tablespace dataset . Given that we have the TS/IX underlying cluster name , properties IE (listcat) etc either for a segmented or for a partitioned TS . Is it possible to identify with this information whether this cluster dataset if for a index or for Table space. Briefly given the cluster name I need to know if its for index or not ? Any help or pointers of how to identify . Please advise. [...]
4199 148 38_Re: Applying lots of PTFs a DB2 system20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Wed, 30 Dec 2009 11:51:44 +0200375_us-ascii Lately we had applied 1500+ PTFs for a V8 system to be ready for V9 preperation.
It was tough but it worked...
Cuneyt
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Max Scarpa Sent: Tuesday, December 29, 2009 6:19 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Applying lots of PTFs a DB2 system [...]
4348 66 27_Re: Data cluster or Index ?14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Wed, 30 Dec 2009 11:47:12 +0100593_UTF-8 Hi,
I created to sms-groups in the job DSNTIJUZ
SMSDCFL=DCDB2TBL, SMSDCIX=DCDB2IDX,
SMSDCFL specifies a DFSMS data class for table spaces. If you assign a value to SMSDCFL, DB2 specifies that value when it uses Access Method Services to define a data set for a table space. If the value of SMSDCFL is one or more blanks, DB2 does not specify a data class when it creates data sets for table spaces. SMSDCIX specifies a DFSMS data class for indexes. If you assign a value to SMSDCIX, DB2 specifies that value when it uses Access Method Services to define a data [...]
4415 365 48_Re: DB2 Stored procedure called from COBOL CICS.14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 30 Dec 2009 22:50:23 +1100838_US-ASCII http://publibz.boulder.ibm.com/cgi- bin/bookmgr_OS390/BOOKS/dsnapj15/2.4.3.3?ACTION=MATCHES&REQUEST=and+do+n ot+use+host+variable+names+that+begin&TYPE=EXACTW&SHELF=DSNSHJA8&DT=2008 0201235049&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank= &ScrollTOP=FIRSTHIT#FIRSTHIT
Sometimes you might be lucky. But when you're not you have to fix your code.
James Campbell
On 29 Dec 2009 at 8:05, mallik vemugunta wrote:
> > I am new to DB2 stored procedure , can you please help me how I can call from COBOL CICS > application program. > We already have a DB2 stored procedure that is being called from Java application and we had a > requirement to call the same procedure from COBOL program, here is CREATE procedure > syntax. > > CREATE PROCEDURE ABC ( OUT SQLSTATE_OUT char(5), [...]
4781 43 27_Re: Data cluster or Index ?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 30 Dec 2009 22:50:23 +1100477_US-ASCII There is nothing intrinsic in a cluster/data component name that differentiates between those used by tablespaces and those used by indexspaces. Often, but not always, site naming conventions will help.
The only way to know is by reference to SYSIBM.SYSTABLEPART/SYSINDEXPART.VCATNAME SYSIBM.SYSTABLESPACE/SYSINDEXES.DBNAME SYSIBM.SYSTABLESPACE.NAME / SYSIBM.SYSINDEXES.INDEXSPACE
James Campbell
On 30 Dec 2009 at 9:31, Prashant Kalal wrote: [...]
4825 50 38_Re: Applying lots of PTFs a DB2 system10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 30 Dec 2009 15:56:57 +0100697_US-ASCII Ehi I'm only a normal DB2 sysprog (if PTFs < 1000 then LABEL='normal sysprog') , that's why IBM datachampions exist ;-)))))
Kidding of course.....Ciao Cuneyt.
Max Scarpa
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.IDUG.org membership is now free. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________
4876 99 50_Re: AD: graingerdatabasesolutions.com is now LIVE!10_Roger Hecq18_Roger.Hecq@UBS.COM31_Wed, 30 Dec 2009 12:03:05 -0500480_US-ASCII Best wishes for joyous holidays and a great 2010.
Roger Hecq MF IB USA DB Support 203-719-0492 / 19-337-0492
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger Sent: Wednesday, December 23, 2009 9:05 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] AD: graingerdatabasesolutions.com is now LIVE!
Hopefully the list admins will cut me a little festive slack for a quick advertisement/announcement. [...]
4976 153 54_z/OS DB2 V8. What triggerred the change in Access path3_Ray22_ray_in_doubt@YAHOO.COM31_Wed, 30 Dec 2009 13:15:49 -0800344_iso-8859-1
Dear listers,
Am little surprised why the optimizer brings in a Sequential and List Prefetch on an SQL that has not been changed and has seen below average growth in data. I think that the runstats somehow precipitated this change but am not sure why there was a change! Any pointers will be appreciated. [...]
5130 229 41_Re: DB2 z/OS Code Samples - any requests?10_Todd Burch17_toddburch@MAC.COM31_Wed, 30 Dec 2009 15:26:14 -0600321_US-ASCII Point taken Avram, but being an IBMer, and in following the processes that must be followed to post code for public consumption, Developerworks is what works for me. If the IDUG list admins do not want these posts, I will certainly comply and stop.
With that said, I have posted some more free code. [...]
5360 317 29_Re: SQL query with variables?11_Hardy, Dale14_DHardy@MIB.COM31_Wed, 30 Dec 2009 17:24:05 -0500335_US-ASCII If the tables aren't too big, how about defining after insert trigger(s) on Table B.
1. Sort the input file to Table B by the key columns and LOAD_DATE.
2. Load TABLE B with SHRLEVEL CHANGE.
The trigger could check table b for a match on col1, col2, col3, col4 and valid_from is null [...]
5678 286 59_Re: Query output difference on using new index after rebind13_Terry Purcell18_tpurcel@US.IBM.COM31_Thu, 31 Dec 2009 15:45:49 +0000544_utf-8 Hi Kals,
Let me try to provide you a simple example........everyone in the IT department is getting a 5% pay increase, so you issue the following SQL
UPDATE PAYROLL SET SALARY = SALARY * 1.05 WHERE DEPT = 'IT';
Index1 (DEPT) Index 2 (DEPT, SALARY)
If DB2 uses Index1, then it reads the qualified rows from Index1, and the rows are updated, but no changes actually occur to Index1, because no columns of Index1 are being updated. Only the underlying data row and the corresponding column of Index2 change. [...]
5965 117 58_Re: z/OS DB2 V8. What triggerred the change in Access path13_Terry Purcell18_tpurcel@US.IBM.COM31_Thu, 31 Dec 2009 16:08:08 +0000338_utf-8 Ray,
RUNSTATS is a likely reason for the access path change. No question. Maintenance upgrade can also result in a change.
My thought is that the sequential prefetch is likely a reasonable choice, given the range predicate on T2 and it's index-only. I am guessing you are concerned about the list prefetch on T1. [...]