1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2008, week 3
2 30 15_AIX DB2 Version13_Roman Gelfand19_rgelfand2@YAHOO.COM31_Tue, 15 Jan 2008 00:22:26 +0000365_utf-8 After doing some googling for the error below, it seems to be that this error pertains to db2 v8.2 or 8.1.
2008-01-10-10.10.36.579007-360 I16865488A285 LEVEL: Severe PID : 160024 TID : 1 PROC : db2fmp (C) 0 INSTANCE: db2inst1 NODE : 000 FUNCTION: DB2 UDB, common communication, sqlccipcdarihandshake, probe: 3 RETCODE : ZRC=0x83000024=-2097151964 [...]
33 107 8_SQL HELP0_18_RENUSHARMA@AOL.COM29_Mon, 14 Jan 2008 22:45:32 EST381_US-ASCII Hello List
I need help to write this sql Table name is TEST1 Table structure is : id smallint, idx smallint, Detail varchar(30)
Data is : id , idx , detail 1 , 0 , 'Mike Angel' 1 , 1 , '323 apt ' 1 , 2 , 'Main Street' 1 , 3 , 'NY' 1 , 4 , 'NY' 2 , 0 , 'David Ester' 2 , 1 , '12 Edgemere Street' 2 , 2 , 'Princeton' 2 , 3 , 'NJ' 3 , 0 , 'RS' 3 , 1 , ' ' [...]
141 12 19_Re: AIX DB2 Version13_Roman Gelfand19_rgelfand2@YAHOO.COM31_Tue, 15 Jan 2008 03:58:18 +0000556_utf-8 Sorry about this... It was a bad question. Please, ignore this posting
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
154 53 12_Re: SQL HELP25_jacampbell@acslink.net.au25_jacampbell@ACSLINK.NET.AU31_Tue, 15 Jan 2008 15:39:18 +1100484_iso-8859-1 Multiple LEFT OUTER JOINS from TEST1 to TEST1, with a COALESCE on detail to convert NULLs (when there isn't a right side of the join) to a blank. I'll leave the details to you.
James Campbell
> Hello List > > I need help to write this sql > Table name is TEST1 > Table structure is : > id smallint, > idx smallint, > Detail varchar(30) > > Data is : > id , idx , detail > 1 , 0 , 'Mike Angel' > 1 , 1 , '323 apt ' > 1 , 2 , 'Main Street' [...]
208 49 18_Automatic runstats13_Roman Gelfand19_rgelfand2@YAHOO.COM31_Tue, 15 Jan 2008 05:03:15 +0000434_utf-8 I have a stored procedure which is called by db2 "call ....." statement from ksh script. When running from AIX DB2 v. 9.1.3 environment, after a while the stored procedure starts to hang and snapshot says it is on fetch statement. The job of the stored procedure is to loop through the cursor executing ddl command found in one of the columns and updating the table which is cursored. It is using handler and hold options. [...]
258 300 25_Antwort: [DB2-L] SQL HELP0_15_rk@AFORMATIK.DE31_Tue, 15 Jan 2008 07:54:11 +0100552_ISO-8859-1
Renu,
you can do it with recursion, but if you have lots of data, you may run into performance headaches...
WITH PARENT ( ID , IDX , ADDRESS ) AS (SELECT A.ID , A.IDX , VARCHAR(A.DETAIL, 1024) FROM TEST1 A WHERE NOT EXISTS (SELECT 1 FROM TEST1 T WHERE T.ID = A.ID AND T.IDX < A.IDX) UNION ALL SELECT B.ID , B.IDX , CONCAT (C.ADDRESS, CONCAT(' ', B.DETAIL)) FROM TEST1 B ,PARENT C WHERE B.ID = C.ID AND B.IDX > C.IDX AND NOT EXISTS (SELECT 1 FROM TEST1 X WHERE X.IDX > C.IDX AND X.IDX < B.IDX) ) SELECT ID, [...]
559 43 12_Reorg Prblem8_duam lee20_duam_lee@HOTMAIL.COM31_Tue, 15 Jan 2008 08:40:14 +0000551_ks_c_5601-1987 Hi List, What is the best way to disorganize a table which is now perfectly re-organized. Deletion of records more that 20 percent and insert the same would be one example I think of now. If record length is variable, then mass update can also do. I am looking for any other way I can disorganize the tablespace soon.With ThanksDuam. _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
603 271 16_Re: Reorg Prblem0_22_agentlease@HOTMAIL.COM31_Tue, 15 Jan 2008 09:59:28 -0000477_us-ascii By deleting and re-inserting would you not be just plugging the same holes again?
This would not be the case if the table is in APPEND_MODE.
Many thanks,
Mark Horrocks.
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of duam lee Sent: 15 January 2008 08:40 To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Reorg Prblem
Hi List, [...]
875 166 16_Re: Reorg Prblem14_Andy Lankester26_alankester@CDBSOFTWARE.COM31_Tue, 15 Jan 2008 05:07:07 -0600445_windows-1250 Inserting and then deleting would be better since it would increase space, index levels etc to sizes above what reorg would produce. You must of course insert more than can be accommodated by PCTFREE/FREEPAGE.
Andy
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of duam lee Sent: Tuesday, January 15, 2008 8:40 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Reorg Prblem [...]
1042 30 36_Re: Isolation Level on DB2 UDB 9.1.10_25_jacampbell@ACSLINK.NET.AU31_Tue, 15 Jan 2008 22:34:58 +1100554_US-ASCII For reference, some information centers:
DB2 LUW V8: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp
DB2 LUW V9: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp
DB2 LUW V9R5: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp
These make it fairly simple to search the LUW manuals without having physical books.
James Campbell
On 14 Jan 2008 at 14:06, Avram Friedman wrote:
> Roman, > I don't have easy access to AIX DB2 manuals being a mainframe person [...]
1073 20 32_Re: DB2 Backups Strategy on z/OS16_Lavoie, Frederic29_Frederic.Lavoie@CRA-ARC.GC.CA31_Tue, 15 Jan 2008 09:35:44 -0500621_us-ascii Hello,
I would like to thank everybody that responds to me. That was useful.
Fred Lavoie DB Common Group *613-954-9286
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
1094 198 16_Re: Reorg Prblem10_Doyle Mark26_Mark.Doyle@QBEAMERICAS.COM31_Tue, 15 Jan 2008 10:11:57 -0600469_us-ascii Duam,
Your question prompts me to ask "why?"
If, for instance, you want to trigger a conditional reorg, there is a much easier way to do this, namely, updating the FAROFFPOS to be the cardinality of the table.
Mark
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of duam lee Sent: Tuesday, January 15, 2008 2:40 AM To: DB2-L@www.idugdb2-l.org Subject: Reorg Prblem [...]
1293 293 16_Re: Reorg Prblem14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 15 Jan 2008 16:27:18 -0000419_us-ascii to run reproducible benchmarks perhaps?
That's ALWAYS a challenge
An easier way is to DSN1COPY the table BEFORE you reorg it and then put the copy back to run the next test - also ensures that all reorgs are reorganising exactly the SAME disorganised data
Phil Grainger CA Product Management Director Phone: +44 (0)1753 577 733 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com [...]
1587 89 50_Re: DB2 V7 zOS stored procedure ID of who calls it11_Bear, Brian23_Brian.Bear@CHARMING.COM31_Tue, 15 Jan 2008 11:48:55 -0500542_iso-8859-1 I uploaded to Code Place last week but I still don't see it there.
~Brian.
________________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of MarkVickers@GROCERYBIZ.COM Sent: Thursday, January 10, 2008 12:24 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 V7 zOS stored procedure ID of who calls it
Brian, Are you able to share this program ? I also like new challenges, but why re-invent the wheel, when you have done such a good job ? [...]
1677 143 16_Re: Reorg Prblem8_duam lee20_duam_lee@HOTMAIL.COM31_Tue, 15 Jan 2008 19:50:45 +0000447_ks_c_5601-1987 Correct Phil,
Bench marks are challenge always. But when we go for the DSN1COPY then what about indexes build. The data portion is disorganized , but I guess index build would make the index more organized than earlier. So any updates/suggestions for this.
With ThanksDuam.
Date: Tue, 15 Jan 2008 16:27:18 +0000From: Phil.Grainger@CA.COMSubject: Re: [DB2-L] Reorg PrblemTo: DB2-L@www.idugdb2-l.org [...]
1821 91 58_FW: [From Vwall: Suspected SPAM]: Re: [DB2-L] Reorg Prblem14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 15 Jan 2008 15:13:02 -0500589_utf-8 Any reason not to DSN1COPY the indexes too? (Don't forget to put the TS and index in RO or STOP mode first)
--Phil Sevetson, NYCAPS DBA Support Financial Information Services Agency of The City of New York 450 West 33rd Street, 4th Floor New York, NY 10001 phone: (212) 857-1688 mailto: psevetson@fisa.nyc.gov ________________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of duam lee Sent: Tuesday, January 15, 2008 2:51 PM To: DB2-L@www.idugdb2-l.org Subject: [From Vwall: Suspected SPAM]: Re: [DB2-L] Reorg Prblem [...]
1913 89 22_Re: Automatic runstats12_tim malamphy20_timalamphy@YAHOO.COM31_Tue, 15 Jan 2008 14:33:31 -0800555_iso-8859-1 You might want to set up an event monitor to capture the deadlocks. This will give you the actual resources being locked and by whom. --- Roman Gelfand wrote:
> I have a stored procedure which is called by db2 > "call ....." > statement from ksh script. When running from AIX > DB2 v. 9.1.3 environment, > after > a while the stored procedure starts to hang and > snapshot says it is on > fetch statement. The job of the stored procedure is > to loop through > the cursor executing ddl command found in one of [...]
2003 367 35_IDUG Webcast Replay - Now Available10_JANE LEYBA21_leyba0224@COMCAST.NET31_Tue, 15 Jan 2008 17:04:12 -0600520_iso-8859-1 IDUG Webcast Replay - DB2 Prerequisites, Migrations and Deprecations Presented by Phil Grainger, CA, Inc.
January 9, 2008
A replay of this valuable and informative webcast is now available.
Go to: http://www.ca.com/us/webcasts/ondemand/item.aspx?e=161006&eis=1
About the Webcast:
Moving from one DB2 version to another is a monumental task that takes
careful planning and execution to achieve a successful result. This, the first [...]
2371 304 47_Free IDUG Webcast - Let's Talk DB2 Access Paths10_JANE LEYBA21_leyba0224@COMCAST.NET31_Tue, 15 Jan 2008 17:11:18 -0600431_iso-8859-1 Free IDUG Webcast - Let's Talk DB2 Access Paths
Wednesday, February 13, 11 a.m. ET
The International DB2 Users Group (IDUG) is pleased to partner with CA to deliver a free Webcast by renowned industry expert Sheryl M. Larsen, SMLI. This live event will run approximately 60 minutes and participants will have the opportunity to pose questions to the presenter. Capacity is limited, so register early. [...]
2676 19 23_DB2 for z/OS v8 Install10_teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 16 Jan 2008 13:48:02 +1100333_utf-8 Hi,
I am in the process of installing DB2 v8. The very first installation screen asks if we want to do an INSTALL or UPDATE or MIGRATE or ENFM. I am not sure what to select here, as it mentions that INSTALL would directly generate jobs for New Function Mode, while I would like to first move to compatibility mode. [...]
2696 146 16_Re: Reorg Prblem12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Tue, 15 Jan 2008 19:46:40 -0800636_iso-8859-1 How about following the below steps to disorganize a table
1. drop the clustering index, hopefully you have two indexes on this table 2. reorg the table 3. create the clustering index
Regards, Ayalew
duam lee wrote: .hmmessage P { margin:0px; padding:0px } body.hmmessage { FONT-SIZE: 10pt; FONT-FAMILY:Tahoma } Correct Phil,
Bench marks are challenge always. But when we go for the DSN1COPY then what about indexes build. The data portion is disorganized , but I guess index build would make the index more organized than earlier. So any updates/suggestions for this. [...]
2843 374 35_IDUG Webcast Replay - Now Available10_JANE LEYBA21_leyba0224@COMCAST.NET31_Tue, 15 Jan 2008 17:04:12 -0600520_iso-8859-1 IDUG Webcast Replay - DB2 Prerequisites, Migrations and Deprecations Presented by Phil Grainger, CA, Inc.
January 9, 2008
A replay of this valuable and informative webcast is now available.
Go to: http://www.ca.com/us/webcasts/ondemand/item.aspx?e=161006&eis=1
About the Webcast:
Moving from one DB2 version to another is a monumental task that takes
careful planning and execution to achieve a successful result. This, the first [...]
3218 14 35_Horacio Villa is out of the office.13_Horacio Villa17_hvilla@AR.IBM.COM31_Wed, 16 Jan 2008 04:06:11 -0200627_US-ASCII I will be out of the office starting 15/01/2008 and will not return until 04/02/2008.
I will respond to your message when I return.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
3233 418 16_Re: Reorg Prblem14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 16 Jan 2008 08:45:04 -0000381_us-ascii er, yes
DSN1COPY the indexes as well - it's not only for tablespaces
btw, DON'T use dfp to do this unless you can guarantee that extents won't e consolidated
Phil Grainger CA Product Management Director Phone: +44 (0)1753 577 733 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com
Ditton Park Riding Court Road Datchet Slough SL3 9LL [...]
3652 50 27_Re: DB2 for z/OS v8 Install13_Palko, George16_gpalko@OPERS.ORG31_Wed, 16 Jan 2008 08:05:24 -0500537_utf-8 Kals, In your case that is correct. You would want to choose Migrate on the first panel. As far as DSNTIDX is concerned. If you don't have that member I would recommend rebuilding one. There's various ways to do it. You could take the DSNTIDXA member copy it to DSNTIDXB and then update DSNTIDXB with the correct values. Or, indeed doing a dummy install would generate the same results. In any case, you have a situation where you're going to have to reconcile you're new DSNTIDX member with the current one that you've lost. [...]
3703 75 27_Re: DB2 for z/OS v8 Install14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Wed, 16 Jan 2008 13:27:47 +0000331_utf-8 If you wish to bring a V7 system to the V8 level then Migrate is the correct option on the first pannel of the install CLIST.
The V8 IVPs can not be run in V8 CM mode which is why they are not generated by the install CLIST when Migrate is specified. They will be generated when you run the CLIST again for ENFM. [...]
3779 71 29_v8.1 Z/OS: ENFM/NFM concerns?15_Chris Hoelscher21_choelscher@HUMANA.COM31_Wed, 16 Jan 2008 09:26:33 -0500300_US-ASCII good morning all - we are ready to move past CM, and are wondering:
is there value to staying at ENFM any length of time (i.e. longer than the time needed to run the NFM job)? a week? 2 weeks? test? prod? did anyone find problems with EXISTING functionality going astray in NFM? [...]
3851 237 33_Re: v8.1 Z/OS: ENFM/NFM concerns?0_19_mike.holmans@BT.COM31_Wed, 16 Jan 2008 14:42:43 -0000595_us-ascii Chris,
IBM's intention was always that ENFM was exactly that; the enablement phase. You're only meant to stay in it if something goes wrong while you are going through the catalog reformat/reorg process.
If you are really thinking of leaving your system in limbo for a period rather than go all the way through to NFM operation, consider this: which of the 3rd-party suppliers you use devoted any time at all to testing whether or not their products worked in ENFM, given that they were not expecting any customers to stay in ENFM for longer than it took to complete [...]
4089 405 33_Re: v8.1 Z/OS: ENFM/NFM concerns?13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Wed, 16 Jan 2008 14:44:28 -0000447_us-ascii Hey Chris,
It used to be, and probably still is, that you should stay in ENFM for as long as it takes you to be comfortable with V8. Nice and vague, eh? Sorry, not trying to be funny, just that different sites will view it differently. Some will want to go through an entire month's processing before switching, some do it right through and spend 30 seconds in ENFM before going to NFM. Depends where your comfort level lies. [...]
4495 83 33_Re: v8.1 Z/OS: ENFM/NFM concerns?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 16 Jan 2008 09:57:02 -0500599_us-ascii Raymond...
That sounds an awful lot like you're talking about CM, not ENFM. You sure about the nomenclature, here?
--Phil Sevetson, NYCAPS DBA Support Financial Information Services Agency of The City of New York 450 West 33rd Street, 4th Floor New York, NY 10001 phone: (212) 857-1688 mailto: psevetson@fisa.nyc.gov ________________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bell, Raymond Sent: Wednesday, January 16, 2008 9:44 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] v8.1 Z/OS: ENFM/NFM concerns? [...]
4579 121 33_Re: v8.1 Z/OS: ENFM/NFM concerns?13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Wed, 16 Jan 2008 15:03:56 -0000535_us-ascii Hey Phil,
Appreciate the diplomacy, but you needn't worry. No, I did mean ENFM for your_comfortable_period then moving to NFM. Although the same is also true of sitting at CM before going ENFM i.e. move as quickly as you're comfortable with.
Cheers,
Raymond
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: 16 January 2008 14:57 To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] v8.1 Z/OS: ENFM/NFM concerns? [...]
4701 385 33_Re: v8.1 Z/OS: ENFM/NFM concerns?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 16 Jan 2008 15:10:27 -0000424_us-ascii "which of the 3rd-party suppliers you use devoted any time at all to testing whether or not their products worked in ENFM, given that they were not expecting any customers to stay in ENFM for longer than it took to complete the upgrade process?"
Actually, I suspect all of us did - for sure CA have a V8 ENFM subsystem that's been in ENFM since before V8 went GA (and is STILL in ENFM so far as I know) [...]
5087 204 33_Re: v8.1 Z/OS: ENFM/NFM concerns?12_Travis, John25_john.travis@CAPGEMINI.COM31_Wed, 16 Jan 2008 15:50:18 -0000433_us-ascii
Chris, We stayed in ENFM until all subsystems were converted then ran an online ZPARM update to activate new functionality - (that is all the DSNTIJNF does really).
I still have an issue with DSNTEP2 and 4 binding to remote DB2 (LUW) and had to resurrect the V7 version. That will have to wait to resolve as we are in the progress of moving our mainframe so I can't apply any maintainance at the moment. [...]
5292 104 33_Re: v8.1 Z/OS: ENFM/NFM concerns?13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Wed, 16 Jan 2008 11:14:41 -0600559_windows-1252 As the name implies, ENFM is an enabling step for NFM. You should spend as little time as possible in ENFM.
You should have performed (and completed) all of your testing in CM. Once you make the decision to leave CM, you have decided your testing is complete and V8 works as advertised. ENFM simply converts the catalog, and a few other things, preparing you for NFM. This has been stressed in every IBM presentation I have ever heard. I have also discussed in a number of times in my blog. A few of those entries are listed here.... [...]
5397 77 50_Re: DB2 V7 zOS stored procedure ID of who calls it11_Bear, Brian23_Brian.Bear@CHARMING.COM31_Wed, 16 Jan 2008 13:23:33 -0500494_iso-8859-1 It's now available on Code Place.
________________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of MarkVickers@GROCERYBIZ.COM Sent: Thursday, January 10, 2008 12:24 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 V7 zOS stored procedure ID of who calls it
Brian, Are you able to share this program ? I also like new challenges, but why re-invent the wheel, when you have done such a good job ? [...]
5475 108 27_Re: DB2 for z/OS v8 Install10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 16 Jan 2008 18:25:15 +0000329_UTF-8 Thanks, Avram. That was the big question initially...do we have it somewhere ? Still looking, but not much hopeful now. V7 was installed a few years ago, by another DBA no longer here, and lots of datasets have changed since then. The DSNTIDxx member was probably either somewhere else (is this possible?) or deleted. [...]
5584 79 67_V8.1 Z/os CM- how long NFM in non-prod before going NFM in prod ???15_Chris Hoelscher21_choelscher@HUMANA.COM31_Wed, 16 Jan 2008 13:46:26 -0500329_US-ASCII
good afternoon all
I know there is no right or wrong answer - but just trying to get a feel of what others have done - how long did you stay in NFM in non-production subsystems before going NFM in production? did anyone go NFM in production first? (sounds intriguing but i do not think it would fly) [...]
5664 171 27_Re: DB2 for z/OS v8 Install14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Wed, 16 Jan 2008 19:05:10 +0000586_utf-8 There are a large number of values on the pannels that should be ignored in setting up a migration. As you point out they have nothing to do with ZPARMS And more importantly they do effectivly influance the update migration jobs.
The values you pointed out, Number of databases and other values on the two sizes pannels DSNTIPD and DSNTIP7 tend to be for space calculations for the various catalog and directory datasets. I have never had much luck updating these to get proper sizes generated for the new objects created by a migration. The Install CLIST is going to [...]
5836 45 12_Re: SQL HELP14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK31_Wed, 16 Jan 2008 20:07:44 +0100420_iso-8859-1 This functionality is called "aggregate concatenation": instead of a SUM or COUNT of all entries within a GROUP BY, you essentially want the concatenation of them.
Have a look at slides 44 through 50 of http://www.abis.be/resources/presentations/idug20071106ctedb2.pdf where a similar problem is worked out using recursive SQL.
-- Peter Vanroose ABIS Training & Consulting Leuven, Belgium. [...]
5882 59 71_Re: V8.1 Z/os CM- how long NFM in non-prod before going NFM in prod ???14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Wed, 16 Jan 2008 19:21:34 +0000413_utf-8 Both shops where I did this migration the time between NFM in test to NFM in prod was under 1 month.
There is not much of an opertunity to drag this out. When Test and Production are mismatched it invalidates your ability to do development, maintance, adjustments and studies.
Calls about application roll outs failing because the test system does not match production are no fun at all. [...]
5942 205 71_Re: V8.1 Z/os CM- how long NFM in non-prod before going NFM in prod ???0_17_sjvagnier@AEP.COM31_Wed, 16 Jan 2008 14:27:36 -0500424_US-ASCII Hi Chris, to minimize the chance that the applications groups would start coding new features of NFM in development and try porting them to production in compatibility mode, I spent one week between upgrading the next environment to NFM; assuming there were no problems. The decision really comes down to a comfort level. I considered going to NFM in production first; but management thought it was too risky. [...]
6148 50 12_Re: SQL HELP12_McKown, John29_John.Mckown@HEALTHMARKETS.COM31_Wed, 16 Jan 2008 13:27:19 -0600614_us-ascii > -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter Vanroose > Sent: Wednesday, January 16, 2008 1:08 PM > To: DB2-L@www.idugdb2-l.org > Subject: Re: [DB2-L] SQL HELP > > > This functionality is called "aggregate concatenation": instead of > a SUM or COUNT of all entries within a GROUP BY, you essentially > want the concatenation of them. > > Have a look at slides 44 through 50 of > http://www.abis.be/resources/presentations/idug20071106ctedb2.pdf > where a similar problem is worked out using recursive SQL. > > -- Peter Vanroose [...]
6199 33 28_Cursors in Stored Procedures13_Roman Gelfand19_rgelfand2@YAHOO.COM31_Wed, 16 Jan 2008 20:35:59 +0000332_utf-8 DB version - DB2 9.1 on AIX
Consider the following script construct...
open curs1;
fetch curs1 into a, b;
while ( a > 0) begin
set a = -1; fetch curs1 in to a, b; end while;
What happens when an end of table is reached? Does a remain -1? If not, what will it be? [...]
6233 40 78_DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE13_Fleming, Mark21_MFLEMING@ALLSTATE.COM31_Wed, 16 Jan 2008 14:39:24 -0600401_us-ascii I haven't used DB2-enforced RI much. I'm hoping you can give me some insights.
Here's the scenario. I have a parent table and 4 child tables with keys to the parent table. Each FK is set up similar to this:
ALTER TABLE DB2PROD.child_tab_1 FOREIGN KEY fkey1 (IN4_col1 , IN4_col2 , IN4_col3 ) REFERENCES DB2PROD.parent_tab (IN5_col1 , IN5_col2 , IN5_col3 ) ON DELETE CASCADE [...]
6274 26 44_Data migration and DRDA (3 Part Name) Access10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 16 Jan 2008 20:49:11 +0000576_utf-8 We are looking at at end state of migrating the remaining data that is on stand alone subsystems into our data sharing environment. With a four way data sharing group on 4 available lpars it does not make a lot of sense to have wholly separate DB2 subsystems sharing the production resources. But of course moving data may be easy but the impacts can be far reaching. One thought is to create aliases using 3 part names for objects after they are moved to allow applications (especially adhoc, obsolete, unknown, rare, and lazy ones) to access the data without any [...]
6301 59 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Wed, 16 Jan 2008 13:49:20 -0700465_us-ascii Hi Mark- do you have indexes defined on the FK's in the child tables to support index access for the db2 delete?
Our standard has always been to defined delete restrict or delete set null. Mostly I think to give exposure to what exactly on the child is being deleted. I have never used delete with cascade so can't really comment on it one way or the other. I would make sure you have indexes defined. Especially if the child tables are large. [...]
6361 82 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 16 Jan 2008 16:00:04 -0500371_us-ascii From a strictly performance standpoint, you save a DB2 call by DELETEing the parent table, as the whole thing is accomplished with one DB2 call.
However, I don't know anyone who's doing ON DELETE CASCADE in production; most people are very concerned with the consequences of deleting a parent and accidentally getting the child data as well. [...]
6444 191 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE13_David Simpson22_dsimpson@THEMISINC.COM31_Wed, 16 Jan 2008 16:05:38 -0500614_iso-8859-1 If you REALLY want this behavior and everyone REALLY understands the nature of the cascade, and you have appropriate indexes to support the RI, then I prefer to do the cascade. Fewer API calls usually = better performance.
David Simpson Senior Technical Advisor Themis Training dsimpson@themisinc.com http://www.themisinc.com
________________________________
From: DB2 Data Base Discussion List on behalf of Hepp Shery C Sent: Wed 1/16/2008 2:49 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE [...]
6636 73 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 16 Jan 2008 16:04:51 -0500358_us-ascii Oh, as Sherry has noted, slow response time from deleting the parent _may_ be a signal of missing or disorganized Foreign Key indexes.
--Phil Sevetson, NYCAPS DBA Support Financial Information Services Agency of The City of New York 450 West 33rd Street, 4th Floor New York, NY 10001 phone: (212) 857-1688 mailto: psevetson@fisa.nyc.gov [...]
6710 95 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE14_Amsden, John W19_John.Amsden@LFG.COM31_Wed, 16 Jan 2008 16:11:02 -0500457_iso-8859-1 We have used delete cascade regularly for many years. We have had no issues *** as long as we created indexes to support the delete ***.
The scenario you described (deleting the children and then still getting slow response on the parent delete) would be expected if you do not have the indexes to support the delete as the delete process will still look (tablespace scan) for children whether there are children rows present or not. [...]
6806 13 71_Re: V8.1 Z/os CM- how long NFM in non-prod before going NFM in prod ???10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 16 Jan 2008 21:47:33 +0000502_utf-8 We did 2-3 weeks
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
6820 52 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE13_Fleming, Mark21_MFLEMING@ALLSTATE.COM31_Wed, 16 Jan 2008 16:35:14 -0600456_us-ascii Great responses everyone. Thanks.
Yes, there are indexes on all the foreign keys on each of the dependant tables, however, in some cases there are additional columns after the first two columns. In other words, the FK consists of (col1, col2, col2) but the IX on a dependent table is (col1, col2, col4, col5, col3). So there is an IX that can be used... not exactly optimal, but it is there. I didn't set this up, I just inherited it. [...]
6873 250 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE0_21_mlabby@AESSUCCESS.ORG31_Wed, 16 Jan 2008 18:38:19 -0500512_us-ascii If I follow your response, I think the indices are the issue.
the FK consists of (col1, col2, col2) but the IX on a dependent table is (col1, col2, col4, col5, col3)
Assuming you mean col3 in the FK, then DB2 will not use this index on the dependant table for RI checking. You must match exactly the full key of the parent as the high order columns on the child. The index on the child can have the extra col4 and col5, but they cannot come until AFTER the full parent columns match. [...]
7124 81 71_Re: V8.1 Z/os CM- how long NFM in non-prod before going NFM in prod ???12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 16 Jan 2008 16:02:28 -0800601_iso-8859-1 We spent the first time about a month from test to production for NFM. But we had to backout entirely that implementation due to serious problems with DB2 base code. (actually ended up backing out twice due to various serious issues.)
Myron --- Avram Friedman wrote:
> Both shops where I did this migration the time between NFM in test to NFM in > prod was under 1 month. > > There is not much of an opertunity to drag this out. When Test and > Production are mismatched it invalidates your ability to do development, > maintance, adjustments [...]
7206 164 27_Re: DB2 for z/OS v8 Install13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Wed, 16 Jan 2008 16:10:30 -0800546_us-ascii Hi Kals. I have been in this situation before, and I did what George and Avram suggested and executed the install CLIST in INSTALL mode, using DSNTIDXA as input. I did not skip any panels or take defaults if I had good values. I had to list the BSDS to get DDF information. I didn't count tablespaces; I just took a SWAG and then used the override fields on panel DSNTIPC to fill in actual values for EDM pool, RID pool, etc. You don't have to fill in everything just to migrate, but having accurate values for bufferpools, etc. in [...]
7371 85 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE12_Troy Coleman21_troy@TROYLCOLEMAN.COM31_Wed, 16 Jan 2008 18:21:02 -0600320_ISO-8859-1 Hi Mark, You will want to make sure you have indexes that have the same columns as the primary key of the parent table. If you only have a few of the first columns and not all the column then you are going to have to do data access. So you have more I/O for scanning the index and then I/O to scan data. [...]
7457 75 32_ADMIN Reminders from list owners16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Wed, 16 Jan 2008 19:29:49 -0500498_iso-8859-1 Hello DB2-L Subscribers,
This posting is something we (the list owners) are sending out about every couple of months. The purpose is to highlight information about DB2-L and also remind subscribers about certain information. Some information will be repeated to benefit new subscribers. We hope you will find the information useful, and save it for future reference. If you have feedback or suggestions, please contact us (the list owners) at DB2-L-Request@www.idugdb2-l.org [...]
7533 71 27_Re: DB2 for z/OS v8 Install10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 17 Jan 2008 12:45:04 +1100576_utf-8 Thanks, Cathy. I suppose that when you say "executed the install CLIST in INSTALL mode,using DSNTIDXA as input", you mean the v7 install clist.
After a bit of digging around, I did manage to find a DSNTID member from our v7 install. It did not have any subsystem "ssid" references in it, hence I did not think it was the right one. But one of my colleagues tells me that, at the time, they just created the one member for all subsystems together, and then individually customised the DSNTIJUZ job for each subsystem. Hence I did not find individual DSNTIDxx [...]
7605 45 32_Re: Cursors in Stored Procedures0_25_jacampbell@ACSLINK.NET.AU31_Thu, 17 Jan 2008 20:56:27 +1100355_US-ASCII After the failing-fetch (ie the one that returns sqlcode=+100) the value of a is undefined; it could be anything; in practice it will be unchanged. However when, for whatever reason, it is not -1 you will have to deal with a failing application.
This is what sqlocde=+100 / sqlstate='02000' is for: use one of them as the while test. [...]
7651 34 44_Mark Kerner/MSD/US/PBI is out of the office.0_18_Mark.Kerner@PB.COM31_Thu, 17 Jan 2008 05:06:14 -0500267_US-ASCII
I will be out of the office starting 01/17/2008 and will not return until 01/25/2008.
For DB2 questions, please contact John Anthony at X8-440-6674. For IMS questions, please contact John Scandurra at X8-440-6642. Thank you. Thank you.
7686 412 71_Re: V8.1 Z/os CM- how long NFM in non-prod before going NFM in prod ???13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 17 Jan 2008 10:34:38 -0000565_us-ascii Ah, what's wrong with ya? Suck it up! What's the worst that could happen... er, good point.
Actually I wouldn't suggest going NFM in Prod first. It would be 2nd; the first would be your playpen subsystem just to practice the task. But Mr. Sevetson was right, my 'wait til you're happy' nonsense was more appropriate to sitting in CM, less so for ENFM. If it were me (and it isn't anymore, so what do I know?) I'd go CM everywhere in traditional order (play, dev, QA, Prod), wait 'a bit', then ENFM everywhere (play, dev, QA, Prod), then switch [...]
8099 412 71_Re: V8.1 Z/os CM- how long NFM in non-prod before going NFM in prod ???13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 17 Jan 2008 10:34:38 -0000565_us-ascii Ah, what's wrong with ya? Suck it up! What's the worst that could happen... er, good point.
Actually I wouldn't suggest going NFM in Prod first. It would be 2nd; the first would be your playpen subsystem just to practice the task. But Mr. Sevetson was right, my 'wait til you're happy' nonsense was more appropriate to sitting in CM, less so for ENFM. If it were me (and it isn't anymore, so what do I know?) I'd go CM everywhere in traditional order (play, dev, QA, Prod), wait 'a bit', then ENFM everywhere (play, dev, QA, Prod), then switch [...]
8512 228 27_NO VICTIM BUFFERS AVAILABLE0_22_agentlease@HOTMAIL.COM31_Thu, 17 Jan 2008 10:48:17 -0000368_us-ascii Hi,
Using UDB V8 on AIX, I am seeing a high percentage of the above page cleaning i.e. 40% of the total page cleaning triggers are 'NO VICTIM BUFFER AVAILABLE'
Also when working out the average number of pages written by the IOCLEANERS it woks out at 5 pages per cleaner which indicates aggressive cleaning. [...]
8741 452 35_007E0005 abend in DSNXGRDS.DSNXGDT230_=?iso-8859-1?Q?Arild_L=F8kke?=10_ARL@VPS.NO31_Thu, 17 Jan 2008 13:54:27 +0100737_iso-8859-1 Hi all, We have just upgraded to DB2 9 CM mode (z/os) and are experiencing a lot of 00E70005 abends in our test system. We are running a datasharing environment with 2 members which only the first member is migrated to v9 CM mode. z/os level is 1.8. This is what we see in dsnMSTR: DSNL027I -DB2T SERVER DISTRIBUTED AGENT WITH 088 LUWID=P248FDE1.G1BE.C1CF8A24FF4E=23839 THREAD-INFO=TESTGEN:vswsbsa:testgen:db2jcc_application RECEIVED ABEND=04E FOR REASON=00E70005 And this is what we see inn dsnDBM1: IEA794I SVC DUMP HAS CAPTURED: 054 DUMPID=012 REQUESTED BY JOB (DB2TDIST) DUMP TITLE=DB2T,ABND=04E-00E70005,U=TESTGEN ,M=(C),C=910.LOCN=: :146.72.250.2 ,LOC=DSNXGRDS.DSNXGDT2:M204 And this is what we see in sys1.logrec: [...]
9194 27 27_DB2 Social Networking Sites10_Mark Labby21_mlabby.idug@GMAIL.COM31_Thu, 17 Jan 2008 13:23:54 +0000508_UTF-8 The IDUG website is going to adding an area to the DB2 Resources page linking to some of the social networking sites that talk about IDUG and DB2. I know about the IDUG Facebook site and one on LinkedIn. Are any of you aware of others?
The same thing goes for some of the Blogs that are popping up - if you have one that primarily deals with DB2 and would like to be listed on the IDUG site, drop me a note. Obviously not every one can be listed, but we will have someone check it out - it [...]
9222 255 39_Re: 007E0005 abend in DSNXGRDS.DSNXGDT20_28_Massimo.Scarpa@PHOENIXSPA.IT31_Thu, 17 Jan 2008 14:34:47 +0100282_UTF-8 It seems that the level of module is a new function ergo it could be a bug. I think you'd open (probably you did it already) a PMR (= Pleeeaasesse Make (it) Run).
I found a similar error in the same LOC in IBM knowledge database but it's for native SQL procedure. [...]
9478 108 39_Re: 007E0005 abend in DSNXGRDS.DSNXGDT214_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Thu, 17 Jan 2008 13:46:24 +0000455_utf-8 Arild You might want to report the problem to IBM.
I did a quick search on IBMLINK for "DB2 F204" Found PK53081 that seems to match
APAR Title ABND04E RC00E70005 DSNXGDT2M204 IN SQLPL / UDF WITH PARMS CHAR() GRAPHIC() BLOB() FLOAT() DECIMAL() INSTEAD OF SQLCODE604
The Discription Error description CREATE PROCEDURE PROC1(IN V1 VARCHAR(), OUT V2 CHAR()) LANGUAGE SQL BEGIN SET V2 = '2'; END [...]
9587 120 35_007E0005 abend in DSNXGRDS.DSNXGDT228_=?utf-8?B?QXJpbGQgTMO4a2tl?=10_ARL@VPS.NO31_Thu, 17 Jan 2008 15:05:01 +0100442_utf-8 Hi
We have looked at PK53081 and this seems to apply only to creating native sql procedures and in our case the abends happens on SELECT sql's.
Arild
-----Original Message----- From: Avram Friedman [mailto:ibmsysprog@GEEK-SITES.COM] Sent: 17. januar 2008 14:46 To: DB2-L@www.idugdb2-l.org; Arild Løkke Subject: Re: 007E0005 abend in DSNXGRDS.DSNXGDT2
Arild You might want to report the problem to IBM. [...]
9708 28 22_test please don't read0_28_Massimo.Scarpa@PHOENIXSPA.IT31_Thu, 17 Jan 2008 15:14:21 +010067_US-ASCII I say don't read....
anyway cheers.....ciao
9737 14 26_Re: test please don't read12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 17 Jan 2008 14:33:09 +0000510_utf-8 I didn't.
Cheers.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
9752 104 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE10_Dave Churn19_davechurn@GMAIL.COM31_Thu, 17 Jan 2008 08:39:44 -0600607_ISO-8859-1 Mark,
What kind of better are you looking for? Faster performance? A single parent delete that cascades into child deletes performs faster than deleting the children then the parent. Fewer calls to DB2 means less time the processor has to spend transfering control from the program to DB2 and back.
However, using DELETE CASCADE can give you a large unit of work. If there are several levels of RI using CASCADE, then a delete to the top level parent would cause deletes throughout the structure. It is important to have a rough estimate on how many deletes will be generated. [...]
9857 127 42_[FLUFF] RE: [DB2-L] test please don't read14_Andy Lankester26_alankester@CDBSOFTWARE.COM31_Thu, 17 Jan 2008 08:43:28 -0600765_windows-1250 I didn't either, but surely the subject line should have read "[FLUFF] test don't read", given the latest encyclical from admin!
Andy
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Massimo.Scarpa@PHOENIXSPA.IT Sent: Thursday, January 17, 2008 2:14 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] test please don't read
I say don't read....
anyway cheers.....ciao The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at HYPERLINK "http://www.idug.org/lsidug"www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code [...]
9985 14 54_[FLUFF] Re: [FLUFF] RE: [DB2-L] test please don't read12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 17 Jan 2008 14:51:18 +0000603_utf-8 Andy, you are quite right. I also forgot the [FLUFF]. I feel, however, that for the test of "please don't read" to be valid, someone - maybe from Admin - should actually also test the opposite condition....
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic [...]
10000 33 58_Re: [FLUFF] Re: [FLUFF] RE: [DB2-L] test please don't read14_Andy Lankester26_alankester@CDBSOFTWARE.COM31_Thu, 17 Jan 2008 09:07:54 -0600511_windows-1250 However surely the encyclical should be updated to say something along the lines of 'If the subject already has a [...] category do not add it again unless the category has changed'. Otherwise subject lines will get very long!
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Adam Baldwin Sent: Thursday, January 17, 2008 2:51 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] [FLUFF] Re: [FLUFF] RE: [DB2-L] test please don't read [...]
10034 208 46_Re: [FLUFF] RE: [DB2-L] test please don't read0_26_MarkVickers@GROCERYBIZ.COM31_Thu, 17 Jan 2008 09:10:55 -0600473_US-ASCII If you guys are bored, come to Houston, I have LOTS of work for you - migrating to v8 !! Cheers, Mark.
Andy Lankester Sent by: DB2 Data Base Discussion List 01/17/2008 09:08 AM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@www.idugdb2-l.org cc
Subject Re: [FLUFF] Re: [FLUFF] RE: [DB2-L] test please don't read [...]
10243 12 58_Re: [FLUFF] Re: [FLUFF] RE: [DB2-L] test please don't read12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 17 Jan 2008 15:12:01 +0000534_utf-8 ...maybe we could go for a [ ]² [ ]³ approach?
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
10256 53 58_Re: [FLUFF] Re: [FLUFF] RE: [DB2-L] test please don't read14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 17 Jan 2008 10:12:37 -0500567_us-ascii Starting our Friday afternoon happy hours a little early, are we, gentlefolk?
--Phil Sevetson, NYCAPS DBA Support Financial Information Services Agency of The City of New York 450 West 33rd Street, 4th Floor New York, NY 10001 phone: (212) 857-1688 mailto: psevetson@fisa.nyc.gov -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Andy Lankester Sent: Thursday, January 17, 2008 10:08 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] [FLUFF] Re: [FLUFF] RE: [DB2-L] test please don't read [...]
10310 134 32_Re: Cursors in Stored Procedures0_19_askwe@OPTONLINE.NET31_Thu, 17 Jan 2008 15:14:25 +0000448_us-ascii My test using DB2 V9 Windows SQL Procedure supported James reply. Variable a contained the value of the column of the last result row after end of result table was reached (SQLCODE = 100).
Agus Kwee Themis Training http://www.themisinc.com
----- Original Message ----- From: jacampbell@ACSLINK.NET.AU Date: Thursday, January 17, 2008 5:03 am Subject: Re: [DB2-L] Cursors in Stored Procedures To: DB2-L@www.idugdb2-l.org [...]
10445 28 26_Re: test please don't read0_28_Massimo.Scarpa@PHOENIXSPA.IT31_Thu, 17 Jan 2008 16:06:15 +010037_US-ASCII well done
Max
10474 13 58_Re: [FLUFF] Re: [FLUFF] RE: [DB2-L] test please don't read12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 17 Jan 2008 15:19:25 +0000600_utf-8 Phil, not having read any of the previous messages in this thread - in strict compliance with Max's test conditions - I don't know what you mean.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member [...]
10488 367 48_Press Release - DB2 Disaster Recovery Automation0_22_DB2information@AOL.COM29_Thu, 17 Jan 2008 11:15:35 EST573_US-ASCII
DB2 Disaster Recovery If you do not see IBM logo above, please click on your Show Images & Enable Links ICON.
Recovery Knowledge, an IBM Business Partner, is pleased to announce an enhanced version 5.0 of the GENDB2 software product. GENDB2 is a product that automates the recovery of DB2 for OS/390-z/OS resources Off-site Disaster Recovery as well as On-site and automates many utilities used by the DB2 DBAs and DB2 System Programmers. The enhancements to the GENDB2 software product include: [...]
10856 128 27_Re: DB2 for z/OS v8 Install13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Thu, 17 Jan 2008 08:26:17 -0800559_us-ascii Hi Kals. Yes, I meant that in your case you would run the version 7 clist in Install mode. You could still run it in install mode using the DSNTIDxx member you found, which would give you the opportunity to get the ssid, dataset names, and other parms correct.
I always like to start a new version upgrade with a correct DSNTIDxx, because IBM sometimes changes things that will make it difficult for you to compare a v7 DSNTIJUZ to the V8 DSNTIJUZ. Some zparms will be gone, some will have new defaults, and there will be brand new ones. [...]
10985 106 71_Re: V8.1 Z/os CM- how long NFM in non-prod before going NFM in prod ???10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Thu, 17 Jan 2008 17:10:57 +0000491_utf-8 We had to back out the very first effort to go V8 CM mode because there were too many distributed threads that ended up eating up all the storage. After that the CM mode went fine.
The biggest issue we've had in NFM is that some access paths negatively changed and could only be restored with correlated statistics. Since neither the vendors nor IBM supports inline correlated stats in load jobs this has created a bit more of a headache around runstats than I would like. [...]
11092 54 21_DB2L Archive Question0_22_DB2information@AOL.COM29_Thu, 17 Jan 2008 12:21:47 EST688_US-ASCII Hello All, I can not find the screen to search for old messages to the DB2L. It used to be on the emails.
Ed.
**************Start the year off right. Easy ways to stay in shape. http://body.aol.com/fitness/winter-exercise?NCID=aolcmp00300000002489
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership [...]
11147 35 25_Re: DB2L Archive Question14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Thu, 17 Jan 2008 17:30:50 +0000700_utf-8 http://www.idugdb2-l.org/cgi-bin/wa?LIST=DB2-L
On Thu, 17 Jan 2008 12:21:47 EST, DB2information@AOL.COM wrote:
>Hello All, >I can not find the screen to search for old messages to the DB2L. It used >to be on the emails. > >Ed. > > > >**************Start the year off right. Easy ways to stay in shape. >http://body.aol.com/fitness/winter-exercise?NCID=aolcmp00300000002489 > >The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG [...]
11183 179 25_Re: DB2L Archive Question0_21_mlabby@AESSUCCESS.ORG31_Thu, 17 Jan 2008 12:37:23 -0500544_us-ascii DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab
The direct links were taken off the footer since they may be changing, but the IDUG website will always contain the correct links.
Mark Labby
Senior Database Administrator American Education Services/PHEAA 717-720-2731 -- mlabby@aessuccess.org IBM Certified Solutions Expert, DB2 UDB v7.1 Database Administration for OS/390 IBM Certified Database Administrator, DB2 UDB v8.1 for z/OS IDUG Web Committee [...]
11363 66 47_St. Louis DB2 Users Group Tools Fair; March 4th10_Tom Glaser14_tg5444@ATT.COM31_Thu, 17 Jan 2008 19:06:36 +0000403_utf-8 DB2 Users,
The St. Louis DB2 Users Group committee is in the process of bringing a DB2 Tools Fair to St. Louis, March 4th, 2008. We have good news....
Curt Cotner (IBM Fellow and the VP / CTO for Data Server Technology) has been confirmed as the keynote speaker. Curt will provide an informative session about directions, challenges, and opportunities in the database industry. [...]
11430 36 82_Re: DB2 for z/OS v8 -- Referential Integrity question when using ON DELETE CASCADE17_Edward Krisieiwcz20_ekrisiewicz@BEAR.COM31_Thu, 17 Jan 2008 19:27:50 +0000709_utf-8 I wrote the following SQL. It lists the RI that has missing matching indexes. It was once questioned, but I was unable to recreate the case. Ed
SELECT DISTINCT F.CREATOR, F.TBNAME AS CHILD, T1.CARD, F.RELNAME, R.REFTBNAME AS PARENT, T2.CARD, R.IXNAME FROM SYSIBM.SYSFOREIGNKEYS F, SYSIBM.SYSRELS R, SYSIBM.SYSTABLES T1, SYSIBM.SYSTABLES T2 WHERE R.RELNAME = F.RELNAME AND R.TBNAME = F.TBNAME AND R.CREATOR = F.CREATOR AND T1.CREATOR = F.CREATOR AND T1.NAME = F.TBNAME AND T2.CREATOR = R.REFTBCREATOR AND T2.NAME = R.REFTBNAME AND NOT EXISTS ( SELECT * FROM SYSIBM.SYSINDEXES I, SYSIBM.SYSKEYS K WHERE F.CREATOR = I.TBCREATOR AND F.TBNAME = I.TBNAME AND F.COLNAME = K.COLNAME [...]
11467 144 27_DB2 Connect Fixpack Version14_Stevens, Wayne29_Wayne.Stevens@DHR.ALABAMA.GOV31_Thu, 17 Jan 2008 13:56:32 -0600765_us-ascii We are running DB2 connect 7.2 How would I find out what fix pack we are on?
Wayne Stevens
334-353-7482
888-535-1552 Cell
888-535-1554 FAX
Wayne.Stevens@dhr.alabama.gov
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services [...]
11612 256 31_Re: DB2 Connect Fixpack Version11_Bear, Brian23_Brian.Bear@CHARMING.COM31_Thu, 17 Jan 2008 15:01:56 -0500912_US-ASCII Try command DB2LEVEL
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Stevens, Wayne Sent: Thursday, January 17, 2008 2:57 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] DB2 Connect Fixpack Version
We are running DB2 connect 7.2 How would I find out what fix pack we are on?
Wayne Stevens
334-353-7482
888-535-1552 Cell
888-535-1554 FAX
Wayne.Stevens@dhr.alabama.gov
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library [...]
11869 201 31_Re: DB2 Connect Fixpack Version18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Thu, 17 Jan 2008 15:14:43 -0500428_US-ASCII At a command window enter: DB2LEVEL and then look at the return. It should tell you at the end of the stmt what if any Fixpak you have installed.
--STeve....
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Stevens, Wayne Sent: Thursday, January 17, 2008 2:57 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] DB2 Connect Fixpack Version [...]
12071 157 31_Re: DB2 Connect Fixpack Version14_Jeff Skogsberg18_SKOGSBEJ@SLRMC.ORG31_Thu, 17 Jan 2008 13:23:33 -0700699_us-ascii The Version, Release and Modification (fix pack level) are in the registry.
Jeff
>>> Wayne.Stevens@DHR.ALABAMA.GOV 1/17/2008 12:56:32 >>>
We are running DB2 connect 7.2 How would I find out what fix pack we are on? Wayne Stevens334-353-7482888-535-1552 Cell888-535-1554 FAXWayne.Stevens@dhr.alabama.gov
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up [...]
12229 438 31_Re: DB2 Connect Fixpack Version14_Stevens, Wayne29_Wayne.Stevens@DHR.ALABAMA.GOV31_Thu, 17 Jan 2008 14:26:06 -0600576_us-ascii Thanks everyone who replied. I was able to get the fixpack level from your replies.
Wayne Stevens
Wayne.Stevens@dhr.alabama.gov
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jeff Skogsberg Sent: Thursday, January 17, 2008 2:24 PM To: DB2-L@www.idugdb2-l.org Subject: Re: DB2 Connect Fixpack Version
The Version, Release and Modification (fix pack level) are in the registry. [...]
12668 35 26_Re: test please don't read13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 17 Jan 2008 15:01:40 -0600647_ISO-8859-1 I couldn't help myself. I can't resist a test.
Willie
Massimo.Scarpa@PHOENIXSPA.IT wrote: > > I say don't read.... > > anyway cheers.....ciao > The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L > list archives, the FAQ, and delivery preferences are at _ www.idug.org > _ under the Listserv tab. While at the > site, you can also access the IDUG Online Learning Center, Tech > Library and Code Place, see the latest _ IDUG conference information > <%20http://www.idug.org/lsconf>_, and much more. > If you have not yet signed up for Basic Membership in IDUG, available [...]
12704 96 71_Re: V8.1 Z/os CM- how long NFM in non-prod before going NFM in prod ???12_Troy Coleman21_troy@TROYLCOLEMAN.COM31_Thu, 17 Jan 2008 16:27:17 -0600342_windows-1252 I love your answer Raymond... I would do it the same way.. Is it Friday yet?
Ah in India it is. I'm on the phone with India support for Magellan as I'm typing this and waiting and waiting... The young lady tells me she is used to working all night. It is about 4:00am Friday for her and it is 4:00pm Thursday for me. [...]
12801 48 14_DB2 v8 on z/os0_24_Martin.Flavell@I-TCS.COM31_Fri, 18 Jan 2008 11:53:27 +0000
12850 46 30_DB2 active log repro questions3_Joe16_tzs61x@YAHOO.COM31_Fri, 18 Jan 2008 08:20:30 -0800539_iso-8859-1 I created and pre-formatted two new active logs
D2T1.LOGCOPY1.DS04 and D2T1.LOGCOPY2.DS04
I then shutdown DB2 normally. the current active logs were
D2T1.LOGCOPY1.DS01 and D2T1.LOGCOPY2.DS01
I then reproed DS01 to DS04 and found something interesting.
Here are the REPRO output: ========================================== REPRO INFILE(INPUT1) OUTFILE (OUTPUT1) REUSE
IDC0005I NUMBER OF RECORDS PROCESSED WAS 5940
REPRO INFILE(INPUT1) OUTFILE (OUTPUT1) REUSE [...]
12897 79 34_Re: DB2 active log repro questions14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Fri, 18 Jan 2008 16:38:19 +0000395_utf-8 Joe Did you do any of the following before the repro (or it may still be meaning full to do now) -DIS LOG Log at the archive messages in MSTR Print a log inventory
I expect one of two common issues A) Your logs are not dataset synced That is when you are logging to LOGCOPY1.DS01 the other log may be LOGCOPY2.DS02. This is very common if you have experienced archive failures [...]
12977 259 18_Re: DB2 v8 on z/os15_Christopher Tee19_CHRISTEE@UK.IBM.COM31_Fri, 18 Jan 2008 16:56:45 +0000510_US-ASCII Martin
Unfortunately, the erly code can only be loaded by an IPL. If the erly code is available, you should see a DSN3100I message at IPL time.
Chris Tee DB2 Systems Programmer Database Team SSO IGA Technical Services Internal Tel: 250072 External Tel: 023 9256 0072
Martin.Flavell@I-TCS.COM Sent by: DB2 Data Base Discussion List 18/01/2008 11:53 Please respond to DB2 Database Discussion list at IDUG [...]
13237 93 33_DB2 v8 Serverpac UNIX mountpoints0_26_MarkVickers@GROCERYBIZ.COM31_Fri, 18 Jan 2008 13:28:54 -0600564_US-ASCII Confused: - I am not sure which mountpoints to use ?
My serverpac install created UNIX directories (what looked to be two identical sets):
/Services/usr/lpp..... and /usr/lpp......
then mounted the filesystems on /Services/usr/lpp....
BUT created the BPXPRMDB parmlib member with mountpoints of MOUNTPOINT('/usr/lpp.....')
DDEF entries includes /Service/usr/lpp....
Which mountpoint should I use /Service/usr/lpp... or /usr/lpp....
and if you can, does this look correct, and why are there two sets ? [...]
13331 62 32_Re: Cursors in Stored Procedures13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 18 Jan 2008 15:15:30 -0500391_us-ascii Hi Roman,
Are you using the SQL WHENEVER statement to trap error and warning conditions?
If not, let me politely suggest that your pseudo-code is either pseudo to the extreme or your error handling is grossly deficient.
In general, it is my humble arrogant opinion that you need to explicitly check SQLCode and SQLWarn after each executable SQL statement. [...]
13394 62 37_Re: DB2 v8 Serverpac UNIX mountpoints20_Ediger Mark - medige22_Mark.Ediger@ACXIOM.COM31_Fri, 18 Jan 2008 14:56:52 -0600484_us-ascii Mark, The filesystem /Service/usr/lpp ... is where the serverpac will probably install the target library code. The other filesystem is a model for your run time subsystem code. Check you DDDEF statements to verify this. You can call the run time filesystem anything you want (e.g. /usr/dbxx/lpp). Presumably, you will have one mountpoint for each subsystem. You can then copy the /Service filesystem code to each one after each maintenance application to implement it. [...]
13457 79 32_Re: Cursors in Stored Procedures12_Troy Coleman21_troy@TROYLCOLEMAN.COM31_Fri, 18 Jan 2008 15:09:43 -0600300_ISO-8859-1 Hi Dave, I would agree with you 100%. In most z/OS shops the WHENEVER is outlawed and standards would require the coding for +0, +100, and then all other codes. Of course you may have other specific test as well, -803 the result of the insert is a duplicate row so lets do an update. [...]
13537 238 37_Re: DB2 v8 Serverpac UNIX mountpoints0_26_MarkVickers@GROCERYBIZ.COM31_Fri, 18 Jan 2008 15:19:30 -0600476_US-ASCII Thanks, Mark. Are you saying I should have the BPXPRMDB parmlib member mount to the /Service/usr/lpp.... directories ? thanks, Mark.
Ediger Mark - medige Sent by: DB2 Data Base Discussion List 01/18/2008 02:57 PM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@www.idugdb2-l.org cc
Subject Re: DB2 v8 Serverpac UNIX mountpoints [...]
13776 53 37_Re: DB2 v8 Serverpac UNIX mountpoints20_Ediger Mark - medige22_Mark.Ediger@ACXIOM.COM31_Fri, 18 Jan 2008 15:29:24 -0600394_us-ascii Mark I would have a parmlib mountpoint entry for both the /SERVICE and target directories. I am assuming that they are in separate HFS files.
There is all kinds of fancy UNIX stuff you can do with multiple mountpoints, links etc. but I have just one HFS file per subsystem plus one for the SERVICE with a mountpoint for each in the parmlib and this works well enough. Mark [...]
13830 112 34_Re: DB2 active log repro questions12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Fri, 18 Jan 2008 21:42:27 -0800435_iso-8859-1 I agree with Avram that the copy 1 and copy 2 logs may not have been synced. You can easily check if they are synced or not by checking the timestamp and the RBA points.
Best Regards, Ayalew
Avram Friedman wrote: Joe Did you do any of the following before the repro (or it may still be meaning full to do now) -DIS LOG Log at the archive messages in MSTR Print a log inventory [...]
13943 39 18_Re: DB2 v8 on z/os0_25_jacampbell@ACSLINK.NET.AU31_Sat, 19 Jan 2008 17:53:40 +1100295_US-ASCII Some time in the past week I read something about some code in SYS1.SAMPLIB that removes a subsystem from the SSVT chain. I just noted it and moved on. From memory the query was from someone who had forgotten to include the early code parameters, so just had the basic definition. [...]
13983 52 42_[z/OS] GA dates of old(er) versions of DB214_Grainger, Phil20_Phil.Grainger@CA.COM31_Sun, 20 Jan 2008 13:58:43 -0000583_iso-8859-1 I know this information is on the web somewhere, but I am blowed if I can find it
Can someone tell me the GA dates for V3, 4 and 5 of DB2 for MVS (and the even earlier ones if you can)
Many thanks
Phil G CA
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much [...]
14036 370 46_Re: [z/OS] GA dates of old(er) versions of DB210_Meir Zohar19_zmeyer@BEZEQINT.NET31_Sun, 20 Jan 2008 16:27:13 +0200381_us-ascii Version 5 - June 1997
Version 4 - December 1995
Version 3.1 (or 3, depending on who you asked) - August 1993
Version 2.3 - October 1991 (to be used at your own risk at the time.) or March 1992
Meir Zohar
CISSP, IBM Certified DBA for DB2 for z/OS
Tel: +972 3 5747860
Fax: +972 3 5747864 [...]
14407 51 46_Re: [z/OS] GA dates of old(er) versions of DB212_Troy Coleman21_troy@TROYLCOLEMAN.COM31_Sun, 20 Jan 2008 13:45:21 -0600339_ISO-8859-1 Hi Phil, We have this on our DB2 UDB for Z/OS Version 8 Quick Reference Cards.. You can get it at http://www.softbase.com/sbcontactformv8.php Otherwise, here you go. DB2 V1 - 1983 DB2 V2 - 1988 DB2 V3 - Dec. 1993 DB2 V4 - Nov. 1995 DB2 V5 - Jun. 1997 DB2 V6 - Jun 1999 DB2 V7 - Mar 2001 DB2 V8 - Mar 2004 DB2 V9 - Mar 2007 [...]
14459 45 10_SQLERRD(3)5_Rao A17_a.rao08@YAHOO.COM31_Mon, 21 Jan 2008 03:51:41 +0800307_us-ascii We are curently on DB2 version 7.0 and most of the COBOL\DB2 programs have "UPDATEs ...." followed by "SELECT count(*) ..."to count the updated rows in the prior UPDATE statement. There are no other columns in the table (like Timestamp columns) that would help us in knowing the updated rows. [...]
14505 49 14_Re: SQLERRD(3)14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK31_Sun, 20 Jan 2008 21:15:27 +0100449_iso-8859-1 > I would like to know if there is any flip side by using > SQLERRD(3) verses SELECT COUNT(*)....
Of course: since SQLERRD is always available (whether used or not), performing an additional SELECT count(*) is always more expensive. Moreover, since there will be some kind of "code duplication" between the UPDATE and the SELECT count(*), there's always the risk that the two don't match and that you get and incorrect count. [...]
14555 83 46_Re: [z/OS] GA dates of old(er) versions of DB212_Weaver, Rick19_Rick_Weaver@BMC.COM31_Sun, 20 Jan 2008 14:25:56 -0600599_us-ascii Hi Troy. I read your articles in the IBM Systems magazine last year in preparation for an IDUG presentation I'm putting together. They're very useful and I may cite them during my presentation if that's ok with you.
Do you happen to remember when Partitioned and Segmented tablespaces showed up? I've got a couple of moldy textbooks from the late '80s that lead me to believe that Segmented showed up in 1989, and partitioned were already in place in 1988. By your table below, that leads me to believe that partitioned came about maybe with V1.2 or V1.3, and Segmented [...]
14639 107 46_Re: [z/OS] GA dates of old(er) versions of DB212_Troy Coleman21_troy@TROYLCOLEMAN.COM31_Sun, 20 Jan 2008 16:13:34 -0600303_ISO-8859-1 Hi Rick, Thank you for the kind words. You can site my articles anytime you want. Just put a plug in for IBM Systems Magazine :-) As for the dates on segmented and partitioning. Maybe Willie or Roger have it. I was thinking segmented was available in 1.3 but maybe it was in 2.x series. [...]
14747 239 39_Hiring -- DB2 DBA, Linux, UNIX, Windows12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Sun, 20 Jan 2008 18:31:13 -0500580_us-ascii Gunning Technology Solutions, the world leader in DB2 consulting on Linux, UNIX and Windows has an opening for an entry-level full-time DB2 DBA remote DBA position. Due to the significant growth and revenue of DB2 on Linux, UNIX and Windows over the past year, GTS is hiring. In fact we are seeing record growth of DB2 with migrations from MySQL and Oracle to DB2. This position requires a minimum of 1 yr of solid experience with DB2 (version 8 and above) on Linux, UNIX or Windows in a production environment. Linux or UNIX experience is a must for this position, [...]
14987 135 46_Re: [z/OS] GA dates of old(er) versions of DB213_Willie Favero21_wfavero@ATTGLOBAL.NET31_Sun, 20 Jan 2008 20:35:14 -0600535_ISO-8859-1 Segmented table spaces arrive in DB2 V2.1 and partitioning has been there since the beginning.
Willie
Troy Coleman wrote: > Hi Rick, > Thank you for the kind words. You can site my articles anytime you > want. Just put a plug in for IBM Systems Magazine :-) > As for the dates on segmented and partitioning. Maybe Willie or Roger > have it. > I was thinking segmented was available in 1.3 but maybe it was in 2.x > series. > > Good Luck! > Troy > > Weaver, Rick wrote: >> Hi Troy. I read your articles in [...]
15123 119 46_Re: [z/OS] GA dates of old(er) versions of DB212_Martin Hubel17_Martin@MHUBEL.COM31_Sun, 20 Jan 2008 21:35:39 -0500
15243 35 46_Re: [z/OS] GA dates of old(er) versions of DB216_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Sun, 20 Jan 2008 22:29:36 -0500352_us-ascii Willie is correct - I just checked my V1R1M0 DB2 reference summary guide from 1984 and the CREATE TABLESPACE allows 16 partitions (isn't that kind of funny).
Steen Rasmussen CA Principal Technical Specialist DB2 tools IBM Certified Database Associate -- DB2 9 Fundamentals IBM Certified Database Administrator - DB2 9 DBA for z/OS [...]
15279 76 36_IDUG Solutions Journal (Winter 2007)9_DB2usa!!!19_db2usa3@HOTMAIL.COM31_Mon, 21 Jan 2008 04:29:17 -0500868_iso-8859-1
Hi DB2 user, Here is a non-profit Blog about DB2 for z/OS (IBM mainframes):http://db2usa.blogspot.com Last update on Monday, January 21st 2008 Winter 2007 issue of IDUG Solutions Journal is available on line. Current issue contains following articles: - DB2/Java Myths Debunkedby Julian Stuhler - SQL Performance Tuning for Application Developersby Peter Plevka - Compliance Needs Drive Data Acess Auditing Requirementsby Craig Mullins Regards, DB2usa.http://db2usa.blogspot.com PS: DB2usa website is still available at:http://db2usa2.free.fr/eliendb2.htm . _________________________________________________________________ Connect and share in new ways with Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008 The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, [...]
15356 384 44_Exemple JCL for overrid SDSNLOAD i storeproc13_Hanne Lyssand10_HAN@VPS.NO31_Mon, 21 Jan 2008 15:51:31 +0100303_us-ascii Dear listserve members Can anyone be willing to share an exampel JCL on how to switch between SDSNLOADS when one member is in v9.1 and the other in 8. We have a naming convention that is not so good.
For the v9.1 system the name is SYS1.TEST.SDSNLOAD The V8 have SYSS.DB1T.SDSNLOAD [...]
15741 472 48_Re: Exemple JCL for overrid SDSNLOAD i storeproc12_McKown, John29_John.Mckown@HEALTHMARKETS.COM31_Mon, 21 Jan 2008 09:14:19 -0600793_us-ascii Have you considered using the new // INCLUDE JCL statement? You could have 3 JCL members. One named DB2TW02, one DB2TEST, the other DB2DB1T.
DB2TW02 would look like:
//*************************************************************
//* DB2TW02 FOR VPS STORED PROCS PL1 /ASSEMBER/SQL
//*************************************************************
//DB2TW02 PROC RGN=0K,APPLENV=DB2TW02,DB2SSN=DB2T,NUMTCB=40
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,
// PARM='&DB2SSN,&NUMTCB,&APPLENV'
//STEPLIB DD DISP=SHR,DSN=VS.TEST.LOAD
// INCLUDE MEMBER=DB2&DB2SSN
//JESJCLIN DD SYSOUT=X,HOLD=YES
//JESMSGLG DD SYSOUT=X,HOLD=YES
//JESJCL DD SYSOUT=X,HOLD=YES
//JESYSMSG DD SYSOUT=X,HOLD=YES [...]
16214 14 23_SMF Accounting Question10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 21 Jan 2008 16:10:19 +0000586_utf-8 Why does Class2 and Class3 Parallel CPU usage gt reported in SMF but not back to the job that is executing. If I look in the SDSF output I only see the Class1 CPU time accounted.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership [...]
16229 174 40_[z/OS v7] Baseline CPU and Storage usage0_26_MarkVickers@GROCERYBIZ.COM31_Mon, 21 Jan 2008 10:35:46 -0600347_US-ASCII If anyone was able to create a baseline of CPU usage and Storage usage for DB2 v7 and IRLM, could you share some details about your capture methods please.
I can get all sorts off stats about what happened within DB2 and as a % of what DB2 used, but I want to measure what % of the whole machine the started tasks are using ? [...]
16404 30 27_Re: SMF Accounting Question9_Mike Bell21_mbell11a1@VERIZON.NET31_Mon, 21 Jan 2008 10:56:55 -0600520_UTF-8 Well, the major reason is that those CPU numbers occur in different address spaces. Z/os ties cpu usage to address spaces. If you look at DBM1 address space you can see the cpu increase. What you can't do is tie the cpu usage back to the originating SQL in a job because Z/os mangles all the cpu time for all the jobs together. DB2 manages to collect that information by SQL and pass it back in the DB2 SMF records for you and NO, that is not an inherent feature of Z/os. If you look at the CPU statistics in [...]
16435 62 27_Re: SMF Accounting Question10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 21 Jan 2008 17:16:51 +0000343_utf-8 Thanks Mike, I did not know that. It's very interesting. In this case I was looking at a utility job. That brings me to a second question. When using vendor utilities, they often generate parallel tasks as well. Are those typically structured in a way so that the CPU also does not get reported back to the job that one is running? [...]
16498 78 44_Re: [z/OS v7] Baseline CPU and Storage usage11_Bear, Brian23_Brian.Bear@CHARMING.COM31_Mon, 21 Jan 2008 12:33:24 -0500357_iso-8859-1 I capture package level, plan level, user level stats using BMC's Performance Reporter. I capture weekly averages and download/import into an excel spreadsheet.
I do not have anything at the started task level though. I have looked into it but never figured out how I could do it.
I'd be very interested in a solution as well. [...]
16577 48 27_Re: SMF Accounting Question12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Mon, 21 Jan 2008 11:40:08 -0600553_utf-8 When some vendors develop their utilities they are very sensitive to CPU chargeback. Use of Cross Memory Services is a standard way to mask some of these charges. When doing comparisons between vendor products you have to be very careful in assessing the actual CPU costs. If runtime and other factors are about the same then low CPU either means the CPU is being cost to others or the vendor has discovered a way to do the same work at a lower cost. The question then should be how is that cost reduction achieved. Is one product as safe as [...]
16626 69 27_Re: SMF Accounting Question10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 21 Jan 2008 17:54:45 +0000596_utf-8 So is there a way, preferably a simple way, to understand the full cost of such as job?
On Mon, 21 Jan 2008 11:40:08 -0600, Gerald Hodge wrote:
>When some vendors develop their utilities they are very sensitive to CPU chargeback. Use of Cross Memory Services is a standard way to mask some of these charges. When doing comparisons between vendor products you have to be very careful in assessing the actual CPU costs. If runtime and other factors are about the same then low CPU either means the CPU is being cost to others or the vendor has [...]
16696 88 27_Re: SMF Accounting Question9_Mike Bell21_mbell11a1@VERIZON.NET31_Mon, 21 Jan 2008 12:07:37 -0600559_UTF-8 Parallel tasks can be generated multiple ways. For the 2 points of discussion here I will call them SQL parallel and utility parallel.
SQL parallel takes an SQL statement from a program and passes portions of the work to be processed to existing DB2 subsystems. Those DB2 subsystems then process the portion of the work and store the results in temp storage ( what used to be DSNDB07). The work that is processed runs as an SRB in the DBM1 address space so it will show up as SRB time in all the DBM1 address that participated. Only DB2 can [...]
16785 17 44_: [DB2-L] Z/OS DB2 measuring WLM dispatching9_Colin Fay14_cmfay@UNUM.COM31_Mon, 21 Jan 2008 18:16:59 +0000586_utf-8 Hi,
I am trying to measure the impact of a WLM boost to the DDF service classes. In what SMF record types would I expect to see a reduction of 'waiting for CPU' , or how would I calculate that factor in order to measure the impact of WLM adhustments?
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference [...]
16803 300 44_Re: [z/OS v7] Baseline CPU and Storage usage0_26_MarkVickers@GROCERYBIZ.COM31_Mon, 21 Jan 2008 12:58:57 -0600491_US-ASCII Seems strange, answering my own email...
I hope someone will give me more, but at this point the best I can come up with is SMF 30 interval records.
Does anyone have a SAS or something that extracts/analyzes SMF Type 30 records ?
thanks, Mark.
MarkVickers@GROCERYBIZ.COM Sent by: DB2 Data Base Discussion List 01/21/2008 10:37 AM Please respond to DB2 Database Discussion list at IDUG [...]
17104 117 56_FW: [DB2-L] Exemple JCL for overrid SDSNLOAD i storeproc15_Douwe van Sluis21_d.b.van.sluis@HOME.NL31_Mon, 21 Jan 2008 20:11:07 +0100411_us-ascii Hanne,
Try using the WLM provided variable for the DB2 subsystem name &IWMSSNM. WLM assigns the DB2 membername to this variable. Below is owner WLM Setting: Appl Environment Name . . DB0USP00 Description . . . . . . . Stored procedures DB0U APF Subsystem type . . . . . DB2 Procedure name . . . . . DB0USP00 Start parameters . . . . APPLENV=DB0USP00,DB2SSID=&IWMSSNM,JOBNAM E=&IWMSSNM.SP00 [...]
17222 37 27_Re: SMF Accounting Question10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 21 Jan 2008 19:42:26 +0000390_utf-8 Mike,
This is working a little bit differently. For the IBM utility the parallel tasks do not show up in the job in SDSF under TCB or SRB, they are accounted for in the Class2 and Class3 parallel CPU fields. The vendor utilities don't show up in either place. So either they are magical and take 10% the CPU of IBM utilities or they hide the CPU time in a different task. [...]
17260 54 27_Re: SMF Accounting Question9_Mike Bell21_mbell11a1@VERIZON.NET31_Mon, 21 Jan 2008 14:18:25 -0600508_UTF-8 It has been a while since I did a full out research on what is going on in the utilities. GTF is the tool to figure this kind of stuff out. 1. DB2 can spawn SRB's to anywhere - when you are authorized (APF auth - ask a systems programmer what that means), which DB2 is, you can do all kinds of things. 2. when you are authorized, you can even spawn SRB's or tasks that don't update the fields that are reported as CPU time. That is one way to make sure you always win the cpu billing calculation. [...]
17315 52 27_Re: SMF Accounting Question10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 21 Jan 2008 20:47:11 +0000380_utf-8 Fascinating! You think you know something after ten years, and then it turns out you really have no idea!
Hopefully we gave Strobe and it can trace this too. GTF would be a bit harder to work with.
I agree that IBM it reporting all the CPU, it was just a matter of understanding where it was. Hopefully the vendor is also reporting all the CPU somewhere. [...]
17368 97 27_Re: SMF Accounting Question35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Mon, 21 Jan 2008 15:57:36 -0500622_utf-8 There too, it depends...... If the utility uses a lot of prefetch, that prefetch IO cost is charged to DBM1.
Regards, Joel
Joel Goldstein Responsive Systems Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts Group Buffer Pool performance too! www.responsivesystems.com tel. (732) 972-1261 fax.(732) 972-9416 ----- Original Message ----- From: "Jorg Lueke" Newsgroups: bit.listserv.db2-l To: Sent: Monday, January 21, 2008 3:47 PM Subject: Re: [DB2-L] SMF Accounting Question [...]