1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 2009, week 1
2 30 31_WITH UR in subquery of a DELETE8_Reinhard28_reinhard.meyer@ACCENTURE.COM30_Sun, 1 Nov 2009 09:19:56 +0000342_UTF-8 Hello DB2 experts, when I do a DELETE on table H01 with contains a subquery on table H11, I sometimes get a -911 (timeout) on H11. I tried to use WITH UR in the subquery but it seems to be not allowed. DB2 z/OS V9.1 Any ideas how to read uncommitted within a subquery? (SKIP LOCKED is not applicable in our case) Regards, Reinhard [...]
33 510 41_AW: [DB2-L] Virtual Indexes in DB2 9 z/os35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Sun, 1 Nov 2009 11:11:05 +0100582_iso-8859-1 Hi Troy
You must create this table with the same qualifier as under which you execute your explain.
HTH
Mit freundlichen Grüßen Walter Janißen
ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf mailto:walter.janissen@itergo.com
Vorsitzender des Aufsichtsrates: Jürgen Vetter Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön. Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996 [...]
544 344 33_Re: Virtual Indexes in DB2 9 z/os13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Sun, 1 Nov 2009 16:45:37 +0100514_us-ascii Oi! 'Without a paid tool from someone' might have been better, and not offended my delicate sensibilities. ;o) If you're in the UK next week, one of the things I'll be touching on briefly at our Customer Day is how to do this sort of what-if analysis in a kind of point-and-click fashion. OK, more like select-and-enter; it's a mainframe after all. But it also works for DB2 9's index on expression feature, so you know if those new types of indexes will be useful too before actually creating them. [...]
889 212 25_Re: DB2 9 z/os PE Issues?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 2 Nov 2009 07:22:08 +0100477_ISO-8859-1 yes. I am waiting on a long chain of PTFs for RUNSTATS and it just gets longer and longer and longer...every day our sysprog pokes his head around the corner and says "still 2 in PE Roy...sorry" quite why *he* is sorry is also a puzzle...
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de [...]
1102 162 49_Roger Miller's presentations about DB2 9 for z/OS10_DB2usa !!!19_db2usa3@HOTMAIL.COM30_Mon, 2 Nov 2009 03:25:14 -0500437_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, November 2th 2009
Here are several Roger Miller's presentations about DB2 for z/OS available on IBM website:
- DB2 9 for z/OS and Beyond by Roger Miller
- DB2 for z/OS Selected Performance Topics by Roger Miller [...]
1265 55 25_Re: DB2 9 z/os PE Issues?6_Leslie21_db2dba@BTINTERNET.COM30_Mon, 2 Nov 2009 08:49:17 -0000597_us-ascii Jorg All the time ... Incorrout is my current nightmare! Leslie
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jorg Lueke Sent: 30 October 2009 14:08 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 9 z/os PE Issues?
We have wanted to apply some PTF's for a few rather annoying problems around RRF and DFSORT in Reorgs but our system programmer keeps running into PEs along the way. Bypassing the PE'd modules removes the majority of the PTFs. Are others seeing this be more of an issue in this release than in previous versions? [...]
1321 330 38_ASN0011E: Reorg without keepdictionary32_Lainez Gutierrez, Maria Victoria21_mvlainez@IBERDROLA.ES30_Mon, 2 Nov 2009 10:01:13 +0100639_iso-8859-1 Hi all,
We are using Data Propagator. After a REORG without keepdictionary we are getting the following error: ASN0011E. When trying to start the capture task it's shut down. Reading on internet information about that we can see: "A compression dictionary may be lost if you use the REORG utility without specifying KEEPDICTIONARY=YES. In this case, the Capture program follows the error action that is specified by the STOP_ON_ERROR option for the registration. If STOP_ON_ERROR=N (no), Capture deactivates the registration. If STOP_ON_ERROR=Y (yes), the Capture program issues an ASN0011E message and terminates." [...]
1652 63 25_Re: DB2 9 z/os PE Issues?12_McLaren Phil32_Phil.McLaren@AXAWINTERTHUR.CO.UK30_Mon, 2 Nov 2009 10:23:43 +0000370_us-ascii Yes, we had similar issues with DB2 9 maint all through this year. However, from our point of view RSU0909+ has cleaned things up a bit.
Regards Phil
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jorg Lueke Sent: 30 October 2009 14:08 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 9 z/os PE Issues? [...]
1716 38 78_DSN1COPY question concerning a non partitioned T/S with multiple vsam datasets11_Leong, Werv17_werv.leong@HP.COM30_Mon, 2 Nov 2009 10:21:49 +0000445_us-ascii Hi,
We are rebuilding a new client DB2 system from tape files which were created via DSN1COPY from a client DB2 system located in different country.
One of the tablespaces (a non partitioned T/S) is on 4 datasets (i.e. A001-A004) and therefore is on 4 tape datasets. The new target system has only one empty VSAM cluster which was created automatically when we created the tablespace. We are not sure how to proceed. [...]
1755 58 82_Re: DSN1COPY question concerning a non partitioned T/S with multiple vsam datasets14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Mon, 2 Nov 2009 12:01:14 +0100497_us-ascii Hi,
You can't concatenate Vsam-files. So, use the other option : create another 3 vsam dataset with the correct characteristics and run 4 separate DSN1COPY-jobs.
greetings,
Patrick Steurs.
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Leong, Werv Sent: maandag 2 november 2009 11:22 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DSN1COPY question concerning a non partitioned T/S with multiple vsam datasets [...]
1814 235 82_Re: DSN1COPY question concerning a non partitioned T/S with multiple vsam datasets11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 2 Nov 2009 12:18:06 +0100432_ISO-8859-1 both ideas are wrong!
You must simply do an IDCAMS create of the needed 2 - 4 datasets (use MODEL to get the attributes from the 1 dataset) and then give DSN1COPY the 1 dataset as normal
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de [...]
2050 332 82_Re: DSN1COPY question concerning a non partitioned T/S with multiple vsam datasets11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 2 Nov 2009 12:40:14 +0100474_ISO-8859-1 I wouldnt do that! Just do the IDCAMS pre-allocate and all is ok. Trust me on this I have done it 1000's of times...
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de
Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]
2383 39 73_How to update the statistics of the newly created index on MIMIC database10_S.D. Pawar17_sdpawar@GMAIL.COM30_Mon, 2 Nov 2009 12:52:59 +0000551_UTF-8 This is regarding the updating the statistics of the indexes which are created on the database. We had recieved the the statitics of production database with following db2look option db2look -m I had created the test/blank database and load the statistics on that database. for testing purposes I need to create new index idx1 on the table ABC on column col1, col2,col3 .. In the database I had column statistics of those columns .... Is there any way to update the statistics of index idx1 ... I had tried runstats but it does not helped. [...]
2423 42 35_Re: WITH UR in subquery of a DELETE14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Mon, 2 Nov 2009 14:05:09 +0100415_utf-8 Hi,
Put the result-set in an temp-table ( with or without an additioanl index ) and join afterwards with the outer-table from your delete-sql.
greetings,
Patrick Steurs
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Reinhard Sent: zondag 1 november 2009 10:20 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] WITH UR in subquery of a DELETE [...]
2466 370 42_Re: ASN0011E: Reorg without keepdictionary9_Chris Tee25_chris.tee@NOVAMAGIC.CO.UK30_Mon, 2 Nov 2009 15:34:29 +0000512_Windows-1252
Vicky
Capture needs the compression dictionary as it was when the compressed data was logged. If you rebuild the compression dictionary before Capture has processed the relevant log records, it is no longer able to decompress those log records. You will have to do a full refresh for the table.
Regards
Chris
Date: Mon, 2 Nov 2009 10:01:13 +0100 From: mvlainez@IBERDROLA.ES To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] ASN0011E: Reorg without keepdictionary [...]
2837 42 42_Re: ASN0011E: Reorg without keepdictionary12_Pete Woodman20_pete.woodman@EDS.COM30_Mon, 2 Nov 2009 16:06:00 +0000496_utf-8 I believe that this might work ...
1. Image Copy the Tablespace. 2. Put the in Tablespaces UT. 3. Recover the Tablespace (or Partition) to before the Reorg ran without Keepdictionary. 4.Start the Tablespace in RO. 5. Start Capture and allow it to catch up to where the Reorg ran. 6. Shut Capture down. 7. Start the Tablespace in UT. 8. Recover the Tablespace back to 1. 9.Start the Tablespace in RO 10. Start Capture and wait for it to get up to date. 11.Start Tablespace in RW [...]
2880 132 25_Re: DB2 9 z/os PE Issues?7_Ed Long19_rdhm99a@PRODIGY.NET30_Mon, 2 Nov 2009 08:28:28 -0800631_iso-8859-1 Maybe its related to Swine Flu, the Admin tool has had a stretch of PE trouble in 2009 as well. Edward Long ________________________________ From: Roy Boxwell To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Mon, November 2, 2009 1:22:08 AM Subject: Re: [DB2-L] DB2 9 z/os PE Issues? yes. I am waiting on a long chain of PTFs for RUNSTATS and it just gets longer and longer and longer...every day our sysprog pokes his head around the corner and says "still 2 in PE Roy...sorry" quite why *he* is sorry is also a puzzle... Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 [...]
3013 318 33_Re: Virtual Indexes in DB2 9 z/os12_Myron Miller22_myronwmiller@YAHOO.COM30_Mon, 2 Nov 2009 10:25:02 -0800719_utf-8 As someone else indicated, the real key is creating a DSN_VIRTUAL_INDEXES under the same qualifier as the explain is run under. Here's one sample of SQL that I used for create index and note that the DSN_VIRTUAL_INDEX is under my own authid, NOT SYSIBM. DELETE FROM DSN_VIRTUAL_INDEXES WHERE TBNAME = 'TYUL1A'; INSERT INTO DSN_VIRTUAL_INDEXES (TBCREATOR, TBNAME, IXCREATOR, IXNAME, ENABLE, MODE, UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE, PGSIZE, FIRSTKEYCARDF, FULLKEYCARDF, CLUSTERRATIOF, PADDED ,COLNO1, ORDERING1 ,COLNO2, ORDERING2 ,COLNO3, ORDERING3 ,COLNO4, ORDERING4 ,COLNO5, ORDERING5 ) SELECT 'YUT', 'TYUL1A', 'YUT' , 'YYUL1A08', 'Y', 'C', 'D', 5, 'N', -1, 4, '2', 4, -1, -1, 99.0 [...]
3332 60 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security3_Ray22_ray_in_doubt@YAHOO.COM30_Mon, 2 Nov 2009 14:39:47 -0800565_iso-8859-1 Dear Listers,
We have a table for which we need to report to compliance whenever someone browses any data (via Reads/SELECTs) via the various application online transaction (CICS) or any Batch jobs.
Whats the best practice for the above? Have any one implemented anything like this.
Cheers, Ray
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
3393 46 56_Re: converting simple tablespace to segmented tablespace14_Peter Vanroose17_pvanroose@ABIS.BE30_Mon, 2 Nov 2009 23:01:02 +0000608_UTF-8 On Fri, 30 Oct 2009 22:30:17 +0000, Roger Miller wrote: >When a release is not announced, it is not possible to confirm details like >this or even the name or number of the release.
Just skimmed through the presentation slides for tomorrow's webinar on "DB2 X". Here's what is (finally!) announced about the ability to convert between TS types: – Convert single table segmented into UTS PBG – Convert single table simple into UTS PBG – Convert classic partitioned table space into UTS PBR – Convert UTS PBR to UTS PBG Apart from that, it will also be possible [...]
3440 212 45_Re: AW: [DB2-L] Virtual Indexes in DB2 9 z/os14_Peter Schwarcz23_schwarcz@BIGPOND.NET.AU30_Mon, 2 Nov 2009 23:34:27 +0000359_UTF-8 There are a couple of things to be aware of:
1. the table name of the virtual index table changes between version 8 and 9.
V8 DSN_VIRTUAL_INDEX V9 DSN_VIRTUAL_INDEXES
2. I have had issues with views, in that db2 will not find the virtual index if you use a view name on the tables. I think that has been fixed through an APAR. [...]
3653 375 42_Re: ASN0011E: Reorg without keepdictionary20_Guillermo Gil Carral22_gilcarral@YAHOO.COM.BR30_Mon, 2 Nov 2009 23:54:20 -0200758_iso-8859-1 ASN0011E: Reorg without keepdictionaryI corrected this type of problem rebuilding the dictionary during the TS reorganization.
Regards, Gil ----- Original Message ----- From: Lainez Gutierrez, Maria Victoria Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Monday, November 02, 2009 7:01 AM Subject: [DB2-L] ASN0011E: Reorg without keepdictionary
Hi all,
We are using Data Propagator. After a REORG without keepdictionary we are getting the following error: ASN0011E. When trying to start the capture task it's shut down. Reading on internet information about that we can see: "A compression dictionary may be lost if you use the REORG utility without specifying KEEPDICTIONARY=YES. In this case, [...]
4029 70 56_Re: converting simple tablespace to segmented tablespace12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 3 Nov 2009 02:21:57 +0000524_utf-8 Tomorrow is not an announcement, but a preview. There is a big disclaimer on slide 2 of the material that is very important. As many customers could not see this at IOD last week, Jeff is talking about the work on the webcast. There is still a lot of work, much yet do do. The usual DB2 for z/OS process includes a beta, ranging from 6 months to over a year, helping us make sure that the final product is ready, for instance. I hope that you will register if you have not seen this. Replays will be available if [...]
4100 152 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security14_Wayne Driscoll18_wdrisco@US.IBM.COM30_Mon, 2 Nov 2009 19:38:00 -0700477_US-ASCII Ray, SELECT (ie read only) activity isn't written to the DB2 log, so there is no information on read access. You could alter the sensitive tables to set the AUDIT ALL attribute, and then run an AUDIT TRACE. An AUDIT trace will cut a record for the FIRST READ and/or the FIRST UPDATE/ALTER/DELETE to an audited object for a unit of work. It doesn't record the total number of access, just the fact that a given UOW read/updated an object that has AUDIT specified. [...]
4253 59 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 3 Nov 2009 03:05:42 +0000709_utf-8 With this set of demands, you'll probably be looking for tools. Before the next step, you'll want to know just what is expected for amounts of access and returned information. A number of companies have products in this space. Here is my best practices for DB2 security paper and Jim Pickel's webcast on PCI compliance. They have many other pointers for compliance, audit and monitoring products. ftp://ftp.software.ibm.com/software/data/db2/zos/presentations/security/best- practice-security-share-2009-miller.pdf http://www.ibm.com/software/os/systemz/telecon/aug4/ Library for security: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.do c.admin/db2z_securityintro.htm [...]
4313 468 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Mon, 2 Nov 2009 22:23:00 -0500326_us-ascii Good evening Ray,
You have a few options here.
You can enable AUDIT ALL for the tables where this is needed. The problem is, you will only get a SMF record cut for the first access to the table within each UOW.
You can use the LOG to capture every delete, insert and update. [...]
4782 41 27_DB2 ODBC\CLI driver problem15_Mehdi Hejazifar18_mhej2004@YAHOO.COM30_Tue, 3 Nov 2009 12:22:51 +0000566_utf-8 Hi, I'm working with DB2 v9 for z/OS and my Client is an application with Delphi. I used DB2 Data Server ODBC/CLI driver v9 for connecting to server. Server codepage is 1252 and client codepage is 1256. When I issue a connection to server it connects but when I execute a SELECT or CALL STORED PROCEDURE that returns a string (with CCSID=1252) it encounters with error ( SQLSTATE=01S01 , Error in row) !! I wrote the character set to 1252 in CONNECTION string of Delphi program and db2 .INI file too. Why I can't retrieve character data (with CCSID 1252) [...]
4824 57 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security14_Lyon, Lockwood20_Lockwood.Lyon@53.COM30_Tue, 3 Nov 2009 08:15:46 -0500631_us-ascii Ray,
In a previous life I had to deal with this requirement. Our budget was low (i.e., nothing) so we implemented access to our "audited tables" thru stored procedures. The SPs Inserted rows with access information into a history table.
HTH
Lock Lyon Fifth Third Bancorp
>Dear Listers, >? >We have a table for which we need to report to compliance whenever? someone browses >any data (via Reads/SELECTs)?via the various application online transaction (CICS)?or >any Batch jobs. >? >Whats the best practice for the above? Have any one implemented anything like this. > >Cheers, Ray [...]
4882 34 43_DB2 Control Center - very long connect time10_Bill Milam18_bmilam59@YAHOO.COM30_Tue, 3 Nov 2009 05:39:53 -0800538_iso-8859-1 I am currently running DB2 Control Center (v8) on my Windows Desktop. The DB2 is v8.1.5 on z/OS 1.8. We recently upgraded z/OS from 1.6 to 1.8. Since that upgrade (I think), The time it takes for me to connect to a DB2 using DB2CC is about 20-plus minutes! However, this only happens on any of the 3 DB2s in my production LPAR. I have 9 DB2s on a non-production LPAR (same CPU, same z/OS 1.8) and they connect in seconds, as before the upgrade. So, I don't really know if the upgrade is the difference in the long connect [...]
4917 99 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security16_Bednarz, Michael24_michael.bednarz@CITI.COM30_Tue, 3 Nov 2009 13:52:55 +0000560_us-ascii Hi Ray,
If you have the chance to have CA Log Analyzer you can setup a strategy to report such things.
We did that and report every DDL changes and DML filtered by some includes and excludes. If you need further INFO contact CA or mail me offline.
Regards
Michael Bednarz CTI, Data Center, EMEA Mainframe DB2 DBA +49 2132 74 259 For Group contact details and processes visit our website: https://globalconsumer.collaborationtools.consumer.citigroup.net/sites/CTI_EMEA/Platforms_Management/Teams/Mainframe.aspx [...]
5017 139 47_Re: DB2 Control Center - very long connect time37_=?iso-8859-1?Q?=C4rlebrandt_Michael?=28_Michael.Arlebrandt@VOLVO.COM30_Tue, 3 Nov 2009 15:43:55 +0100361_iso-8859-1 Hi,
We also are dealing with intermittent 20-30 minutes logon time using DB2 Connect Personal Edition 9.5 but so far I don't know what cause except it is stuck in the db2 license code part, IBM suspects something in the windows/network environment.
The traces I run before I opened the PMR against IBM was CLI trace and DB2 trace. [...]
5157 465 47_Re: DB2 Control Center - very long connect time12_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM30_Tue, 3 Nov 2009 10:09:48 -0500399_ISO-8859-1 Hello, is it possible that your db2cli.ini file in sqllib is different between your test and prod? we had a problem with users pulling back all schemas and taking forever. Just a thought, Jeff
Ärlebrandt Michael Sent by: IDUG DB2-L 11/03/2009 09:45 AM Please respond to IDUG DB2-L [...]
5623 30 25_Re: DB2 9 z/os PE Issues?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Tue, 3 Nov 2009 15:17:14 +0000320_UTF-8 Good to hear, in a way, that we are not the only one's. We are going to go to RSU0907 because SAP wants that level but with the errors we still won't get the fix we really want. We could ignore a lot of the PEs but one of them has potential data corruption when doing add columns, and that is just too risky. [...]
5654 54 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security14_Peter Vanroose17_pvanroose@ABIS.BE30_Tue, 3 Nov 2009 18:03:58 +0000547_UTF-8 On Tue, 3 Nov 2009 08:15:46 -0500, Lyon, Lockwood wrote:
> [...] we implemented access to our "audited tables" thru stored > procedures. The SPs Inserted rows with access information into a history table.
I was also thinking in that direction: don't grant SELECT on the table to anyone but grant EXECUTE on either a program or a stored procedure; this program may then write log records as well (containing also things like USER and CURRENT TIMESTAMP), besides returning the requested rows/columns. [...]
5709 170 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security3_Ray22_ray_in_doubt@YAHOO.COM30_Tue, 3 Nov 2009 10:18:45 -0800472_iso-8859-1
Dear listers,
Thank you all for the pointers. I will have to look closely at each one of them. The problem as you all guessed is "funding". That almost rules out new products.
Further, these are exisiting tables with existing transactions going against them. The challenge is to come up to record 'Read' access and log them for current online transactions with minimal impact. It is a bit easier with Update / Delete actions. [...]
5880 74 37_Long-running INSERT - Fix implemented14_Lyon, Lockwood20_Lockwood.Lyon@53.COM30_Tue, 3 Nov 2009 13:45:40 -0500402_us-ascii Esteemed List,
As you may remember, we had an issue involving long (> 5 second) elapsed times for dynamic SQL single-row INSERTs. (DB2z V8 NFM, 6-way DS grp). Tables defined with Identity Column generated by default with CACHE 20 NO CYCLE NO ODER.
Tables are in tablespaces with:
FREEPAGE 0 PCTFREE 0 GBPCACHE CHANGED TRACKMOD YES SEGSIZE 4 LOCKSIZE ANY COMPRESS YES [...]
5955 182 41_Re: Long-running INSERT - Fix implemented3_Ray22_ray_in_doubt@YAHOO.COM30_Tue, 3 Nov 2009 10:51:16 -0800454_iso-8859-1 Hi Lock,
Do you have the deck for #1643 at IOD titled, "Concurrent Insert Best Practices" for further reference. I would love to take a look at it.
Cheers, Ray
--- On Tue, 11/3/09, Lyon, Lockwood wrote:
From: Lyon, Lockwood Subject: [DB2-L] Long-running INSERT - Fix implemented To: DB2-L@WWW.IDUGDB2-L.ORG Date: Tuesday, November 3, 2009, 1:45 PM [...]
6138 123 41_Re: Long-running INSERT - Fix implemented35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Tue, 3 Nov 2009 14:17:33 -0500426_iso-8859-1 It seems that attending an education conference was money well spent.
Hope you have made management aware of the quick payback..
Thanks, Joel
Joel Goldstein Responsive Systems IBM Gold Consultant Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts IO Rate !! Predicts Group Buffer Pool performance too www.responsivesystems.com [...]
6262 562 42_Re: ASN0011E: Reorg without keepdictionary15_Campbell, Wayne28_Wayne.Campbell@COURTS.WA.GOV30_Tue, 3 Nov 2009 13:25:07 -0800393_iso-8859-1 We use IBM InfoSphere Change data Capture to replicate and had the same problem a couple of weeks back. The problem is that DB2 needs to access the dictionary to get the value of the compressed data when accessing the log. You will need to reload the table(s) that your are replicating. I've also seen this problem when using a log analysis tool to look at changes in the log. [...]
6825 62 70_Re: DB2V8- How to restore only a few tables at DR, are there BSDS i...0_22_DB2information@AOL.COM28_Tue, 3 Nov 2009 16:32:58 EST783_US-ASCII Kevin, You may wish to look at _www.recoveryknowledge.com_ (http://www.recoveryknowledge.com) for the automation of DB2 DR and other goodies.
Ed.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations! DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among many more categories of help waiting for you! Whether you are an old hand or a DB2 newbie, we have presentations for every level. _____________________________________________________________________ [...]
6888 174 47_Re: DB2 Control Center - very long connect time0_17_jtonchick@AOL.COM30_Wed, 4 Nov 2009 00:01:04 -0500487_us-ascii
Since you didn't change DB2 and you only experience the problem on the production lpar, I'd look at the WLM policy on the production lpar first. Check to see how the policy handles the DB2CC related stored procedures and the address spaces they run in.
Jim Tonchick
-----Original Message----- From: Bill Milam To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tue, Nov 3, 2009 7:39 am Subject: [DB2-L] DB2 Control Center - very long connect time [...]
7063 30 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Wed, 4 Nov 2009 09:48:35 +0000510_utf-8 Ray, you could always write an assembler routine to get hold of the relevant audit trace records and process them as per your requirements. As with any auditing / tracing, you'll have to take the volume of accesses to the table(s) into account.
Regards, Adam
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
7094 1213 48_DB2 V8 migration issue with REXX/Assembler macro12_Ian Reynolds28_ireynold@FIRSTDATACORP.CO.UK30_Wed, 4 Nov 2009 14:31:59 +0000713_- //JTAD140B JOB (@DB),'ASMDB2',NOTIFY=&SYSUID,MSGCLASS=9,CLASS=A JOB02217 //* ------------------------------------------------------------- */ 00000200 //* */ 00000300 //*An SQL interface to ISPF Dialog Manager */ 00000400 //* */ 00000500 //* */ 00000600 //*Problem addressed */ 00000700 //* */ 00000800 //*One of the interfaces missing from DB2 is an interface to ISPF */ 00000900 //*Dialog Manager - in particular, an interface to ISPF tables. */ 00001000 //*The following program, called DB2ISPF2, stores the results */ 00001100 //*from an SQL select query directly into an ISPF table. */ 00001200 //* */ 00001300 //* */ 00001400 //*Solution */ 00001500 //* */ 00001600 //*The SQL query is passed to the [...]
8308 1213 48_DB2 V8 migration issue with REXX/Assembler macro12_Ian Reynolds28_ireynold@FIRSTDATACORP.CO.UK30_Wed, 4 Nov 2009 14:32:03 +0000713_- //JTAD140B JOB (@DB),'ASMDB2',NOTIFY=&SYSUID,MSGCLASS=9,CLASS=A JOB02217 //* ------------------------------------------------------------- */ 00000200 //* */ 00000300 //*An SQL interface to ISPF Dialog Manager */ 00000400 //* */ 00000500 //* */ 00000600 //*Problem addressed */ 00000700 //* */ 00000800 //*One of the interfaces missing from DB2 is an interface to ISPF */ 00000900 //*Dialog Manager - in particular, an interface to ISPF tables. */ 00001000 //*The following program, called DB2ISPF2, stores the results */ 00001100 //*from an SQL select query directly into an ISPF table. */ 00001200 //* */ 00001300 //* */ 00001400 //*Solution */ 00001500 //* */ 00001600 //*The SQL query is passed to the [...]
9522 1213 48_DB2 V8 migration issue with REXX/Assembler macro12_Ian Reynolds28_ireynold@FIRSTDATACORP.CO.UK30_Wed, 4 Nov 2009 14:32:00 +0000713_- //JTAD140B JOB (@DB),'ASMDB2',NOTIFY=&SYSUID,MSGCLASS=9,CLASS=A JOB02217 //* ------------------------------------------------------------- */ 00000200 //* */ 00000300 //*An SQL interface to ISPF Dialog Manager */ 00000400 //* */ 00000500 //* */ 00000600 //*Problem addressed */ 00000700 //* */ 00000800 //*One of the interfaces missing from DB2 is an interface to ISPF */ 00000900 //*Dialog Manager - in particular, an interface to ISPF tables. */ 00001000 //*The following program, called DB2ISPF2, stores the results */ 00001100 //*from an SQL select query directly into an ISPF table. */ 00001200 //* */ 00001300 //* */ 00001400 //*Solution */ 00001500 //* */ 00001600 //*The SQL query is passed to the [...]
10736 39 37_Panwar, Manoj S is out of the office.15_Manoj S. Panwar26_manoj.s.panwar@US.HSBC.COM30_Wed, 4 Nov 2009 09:52:48 -0600566_US-ASCII I will be out of the office starting 11/03/2009 and will not return until 11/10/2009.
I will respond to your message when I return.
----------------------------------------- ****************************************************************** This E-mail is confidential. It may also be legally privileged. If you are not the addressee you may not copy, forward, disclose or use any part of it. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return E-mail. [...]
10776 66 52_Re: DB2 V8 migration issue with REXX/Assembler macro15_Lizette Koehler23_starsoul@MINDSPRING.COM30_Wed, 4 Nov 2009 10:58:59 -0500668_UTF-8 Ian,
Have you looked in SYSLOG at the time this abend occurs to see if you are getting any DSNT500I messages? Or maybe some other message from DB2?
Lizette
-----Original Message----- >From: Ian Reynolds >Sent: Nov 4, 2009 9:32 AM >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: [DB2-L] DB2 V8 migration issue with REXX/Assembler macro > >Since migrating from DB2 Version 7 to Version 8 we have had issues with >generating IBM Utility JCL, which is done by performing SQL queries on the >DB2 Catalog tables. We believe that the problem lies within the TSO/ISPF >Call Attachment Facility (CAF) which is accessed via [...]
10843 1585 52_Re: DB2 V8 migration issue with REXX/Assembler macro11_Mike Turner19_mike.turner@GMX.COM30_Wed, 4 Nov 2009 16:14:05 -0000625_UTF-8 Could it be due to the fact that many columns in the Catalog tables have increased in length in V8?
Regards Mike Turner Email: mike.turner@gmx.com Tel: +44 (0)1565-873702 Web: www.michael-turner.ltd.uk Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44. ----- Original Message ----- From: "Ian Reynolds" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, November 04, 2009 2:31 PM Subject: [DB2-L] DB2 V8 migration issue with REXX/Assembler macro [...]
12429 111 52_Re: DB2 V8 migration issue with REXX/Assembler macro12_Felton, John25_Felton.John@PRINCIPAL.COM30_Wed, 4 Nov 2009 10:51:21 -0600689_us-ascii Ian:
Also, check to see if the catalog Unicode conversion could be causing your issue.
Regards, John
-----Original Message----- >From: Ian Reynolds >Sent: Nov 4, 2009 9:32 AM >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: [DB2-L] DB2 V8 migration issue with REXX/Assembler macro > >Since migrating from DB2 Version 7 to Version 8 we have had issues with >generating IBM Utility JCL, which is done by performing SQL queries on >the >DB2 Catalog tables. We believe that the problem lies within the >TSO/ISPF Call Attachment Facility (CAF) which is accessed via a REXX >assembler macro, DB2ISPF2, but have been unable to identify the [...]
12541 28 52_Re: DB2 V8 migration issue with REXX/Assembler macro10_Todd Burch17_toddburch@MAC.COM30_Wed, 4 Nov 2009 10:57:00 -0600665_US-ASCII Ian, I reviewed your code.
Uncomment the TPUTs in the CHECK_SQL routine and you'll probably see that you are getting an SQLCODE -302.
Todd
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal Technical atricles from world famous authors in DB2's most prestigious, peer reviewed magazine now on-line! _____________________________________________________________________ [...]
12570 26 25_Re: DB2 9 z/os PE Issues?10_John Miles22_jmiles2@MASSMUTUAL.COM30_Wed, 4 Nov 2009 18:39:07 +0000465_utf-8 That is really good news to hear, Phil. After slogging through the available PTF's recently, it looked like some relief was probably on the way, so we're very happy to hear that someone's been able to prove out that first-hand. _____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
12597 52 29_Tape on DASD for DB2 backups?16_Mark Labby - AES21_mlabby@AESSUCCESS.ORG30_Wed, 4 Nov 2009 14:19:45 -0500
12650 38 22_Optimizers and Indexes12_Denise Gantz22_denise.m.gantz@EDS.COM30_Wed, 4 Nov 2009 20:42:24 +0000555_utf-8 I have a user who submits dynamic queries via a websphere front-end application. He had an issue with a long-running query. I did some checking on it and found that it was pointing to an index which matched 3 of the 6 columns used in the predicate. The query was using the clause OPTIMIZE FOR 1 ROW. There was another index available on the table that would match all 6 columns of the predicate. By running the query without the OPTIMIZE clause, I was able to get the query to use the 6-column matching index, and greatly improved the response [...]
12689 65 26_Re: Optimizers and Indexes9_Mike Bell21_mbell11a1@VERIZON.NET30_Wed, 4 Nov 2009 15:39:41 -0600530_us-ascii One of the things optimize for one row disables in list prefetch - if cluster ratio less than the magic number (by memory .80 but not sure) then use list prefetch for that index. I would have hoped that optimize for one row would still use the 6-column index but just not use list prefetch. My guess would be it is time for put the query on OSC and open a PMR. The other issue is whether the optimizer has stats for the cardinality of the 3 columns vs the 6 columns. The opitmizer is getting more and more dependent [...]
12755 129 56_[z/OS V9] Default Behavior when no CLUSTER index exists?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 4 Nov 2009 16:47:20 -0500340_us-ascii I have a segmented tablespace in production. It has no index defined with CLUSTER. Can anyone point me to documentation of the behavior of an INSERT statement on this table? (My understanding is that the oldest, or lowest OBID, index will be used for clustering data in the event of a REORG; is this true for INSERT as well?) [...]
12885 105 26_Re: Optimizers and Indexes10_Dave Nance16_dav1mo@YAHOO.COM30_Wed, 4 Nov 2009 13:55:41 -0800621_iso-8859-1 Denise, Never lie to the optimizer it will get you in trouble as you have seen. By telling it to optimize for one row, it probably used the smaller of the two indexes, less index reads As Mike has already told you make sure that you have good stats and have your users be honest and upfront with your DB. David Nance ________________________________ From: Denise Gantz To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Wed, November 4, 2009 3:42:24 PM Subject: [DB2-L] Optimizers and Indexes I have a user who submits dynamic queries via a websphere front-end application. He had an issue with a [...]
12991 191 60_Re: [z/OS V9] Default Behavior when no CLUSTER index exists?12_Weaver, Rick19_Rick_Weaver@BMC.COM30_Wed, 4 Nov 2009 15:43:13 -0700580_us-ascii If memory serves, the first index defined becomes the clustering index if no index has CLUSTER defined.
There, I just used index, cluster, and defined twice each in the same sentence and it's not even Friday.
Rick Weaver Product Manager DB2 z/OS Solutions BMC Software
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Wednesday, November 04, 2009 3:47 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] [z/OS V9] Default Behavior when no CLUSTER index exists? [...]
13183 311 60_Re: [z/OS V9] Default Behavior when no CLUSTER index exists?35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Wed, 4 Nov 2009 18:17:35 -0500464_iso-8859-1 Encore.... ???
Friday should be remarkable.
Thanks, Joel
Joel Goldstein Responsive Systems IBM Gold Consultant Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts IO Rate !! Predicts Group Buffer Pool performance too www.responsivesystems.com
Buffer Pool Tool for DB2 on www.LinkedIn.com Watch the 3-Minute Buffer Pool Tool Movie at: www.responsivesystems.com/Movie1 [...]
13495 514 60_Re: [z/OS V9] Default Behavior when no CLUSTER index exists?16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Wed, 4 Nov 2009 18:19:15 -0500405_us-ascii Correct Rick - the first index defined becomes the clustering until an explicit one is created.
What changed in DB2 V9 was, that reorg now honors this implicit created clustering - so data will be organized according to this one. In the past reorganizing a tablespace without an explicitly created clustering index - only freespace was reclaimed, the data remained in the same order. [...]
14010 301 60_Re: [z/OS V9] Default Behavior when no CLUSTER index exists?14_Peter Backlund21_BacklundDB2@TELIA.COM30_Thu, 5 Nov 2009 07:35:35 +0100
14312 351 37_host variables versus constant values12_Jose Antonio17_jamorcillo@CAM.ES30_Thu, 5 Nov 2009 10:14:55 +0100666_iso-8859-1 Hi everbody!
We have a WHERE clause as follows:
AND T042_EST_OPERACION IN (
'0001', '0002', '0004', '0014','0019', '0020', '0021','0024'
)
T042_EST_OPERACION type is CHAR(4)
My fellow Hector recommends me to use host variables as in DB2 version prior to 8 it seems to be a problem with data types comparation.
Any ideas?
Thanks a lot!!!!
__________________________ José A Morcillo Valenciano Tfno.: +34 965 90 51 43 747-Producción Informática [...]
14664 58 52_Re: DB2 V8 migration issue with REXX/Assembler macro12_Ian Reynolds28_ireynold@FIRSTDATACORP.CO.UK30_Thu, 5 Nov 2009 11:05:44 +0000497_utf-8 Thanks to all those that have replied and made helpful suggestions.
At first we thought it was due to the increased length of columns in the Catalog table but this was ruled out.
However, further investigation revealed the following errors:
Variable 'NUM_DEP_' is syntactically incorrect
Variable 'SPLIT_RO' is syntactically incorrect
These columns are derived from SYSIBM.SYSTABLES - NUM_DEP_MQTS and SPLIT_ROWS, both of which are new columns in V8. [...]
14723 61 33_Re: Tape on DASD for DB2 backups?14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 5 Nov 2009 22:30:31 +1100362_US-ASCII Most (all that the I know of - but there might be some I don't know of) virtual tape subsystems actually use real tapes. What they do is map "virtual tapes" (what z/OS sees as a tape) as individual datasets (or something similar) on the backing tapes. When z/OS writes a "tape", the data is written to disk, but is eventually written to real tape. [...]
14785 256 42_Re: ASN0011E: Reorg without keepdictionary32_Lainez Gutierrez, Maria Victoria21_mvlainez@IBERDROLA.ES30_Thu, 5 Nov 2009 12:57:16 +0100436_iso-8859-1 Hi all,
Thanks a lot for all the replies. We got the problem in a production environment (as always). With IBM support we applied the following procedure:
1. Backup and quiesce of data propagator tables 2. Deregister and register the problematic table 3. Start capture task. 4. Start apply task. At this point a gap was detected by data propagator for this table that was solved by an application program. [...]
15042 275 41_Re: host variables versus constant values14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Thu, 5 Nov 2009 13:30:36 +0100581_iso-8859-1 Hi,
If you have problems with datatype comparison, use a CAST-function
sample : DELETE FROM DSN8710.EMP WHERE EMPNO = CAST(? AS CHAR(6))
greetings,
Patrick Steurs
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jose Antonio Sent: donderdag 5 november 2009 10:15 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] host variables versus constant values
Hi everbody!
We have a WHERE clause as follows: [...]
15318 28 26_Re: Optimizers and Indexes15_Sharad D. Pawar17_sdpawar@GMAIL.COM30_Thu, 5 Nov 2009 13:27:34 +0000319_utf-8 very True , never lie with optimizer so optimizer will prepare explain plan for 1 row only ... we had observered same behaviour with one of our customer .... so basically cost of query was reduced if we specified for "optimize for one 1 rows" but acutally query execution was slow due to bad execution plan. [...]
15347 54 58_z/OS DB2 V8 - Effects of LOAD ... PART n REPLACE on access14_Lyon, Lockwood20_Lockwood.Lyon@53.COM30_Thu, 5 Nov 2009 09:25:16 -0500336_us-ascii Esteemed List:
I'm implementing a table(space) purge process. Table has 7 partitions, one per day-of-week. Partitioning is Table-based. Table has multiple indexes, none of which is partitioned.
Data is only to be kept for 24 hours, so (for example), on Day 3 I want to empty PART 1, Day 4 empty PART 2, etc. [...]
15402 112 33_Re: Tape on DASD for DB2 backups?20_Govan, Hal (RET-DAY)29_Harold.Govan@REEDELSEVIER.COM30_Thu, 5 Nov 2009 09:25:23 -0500534_us-ascii Another variation on this idea which we have been using is to have dual virtual tape systems, one local and the other at a remote D/R site.
Hal Govan Senior Database Administrator Reed Elsevier - Technology Services harold.govan@reedelsevier.com Phone: (937) 865-7820
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of James Campbell Sent: Thursday, November 05, 2009 6:31 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Tape on DASD for DB2 backups? [...]
15515 599 60_Re: [z/OS V9] Default Behavior when no CLUSTER index exists?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 5 Nov 2009 09:53:14 -0500431_us-ascii Thanks, all. That confirms what I'm seeing, but I wasn't sure whether it was DB2 or the application which was causing the effect before this. --Phil S.
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Rasmussen, Steen Sent: Wednesday, November 04, 2009 6:19 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [z/OS V9] Default Behavior when no CLUSTER index exists? [...]
16115 28 60_Re: [z/OS V9] Default Behavior when no CLUSTER index exists?12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Thu, 5 Nov 2009 15:06:35 +0000722_utf-8 Didn't the way that reorg handles an implicit clustering index change once before back in V4 / V5 days? My memory isn't what it was. It's not even friday and I can't remember wednesday.
Cheers, Adam
_____________________________________________________________________
* 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 _____________________________________________________________________ [...]
16144 82 62_Re: z/OS DB2 V8 - Effects of LOAD ... PART n REPLACE on access14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 5 Nov 2009 10:15:35 -0500439_us-ascii You might want to try running SQL against one partition (in test) while emptying another. I think you're going to see some availability issues in your NPIs.
--Phil
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Lyon, Lockwood Sent: Thursday, November 05, 2009 9:25 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] z/OS DB2 V8 - Effects of LOAD ... PART n REPLACE on access [...]
16227 79 68_AW: [DB2-L] [z/OS V9] Default Behavior when no CLUSTER index exists?12_Peter, Georg15_G.Peter@DZBW.DE30_Thu, 5 Nov 2009 16:19:42 +0100663_iso-8859-1 >> My memory isn't what it was. <<<
Adam, you are not alone ;-))
SCNR.
Cheers, G e o r g ----------------------------------------------------------------------
-----Ursprüngliche Nachricht----- Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Adam Baldwin Gesendet: Donnerstag, 5. November 2009 16:07 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: Re: [DB2-L] [z/OS V9] Default Behavior when no CLUSTER index exists?
Didn't the way that reorg handles an implicit clustering index change once before back in V4 / V5 days? My memory isn't what it was. It's not even friday and I can't remember wednesday. [...]
16307 113 24_SNMP agent for DB2 z/OS?18_GRESHAM, DEBORAH B18_DGRESHAM@SCANA.COM30_Thu, 5 Nov 2009 10:38:07 -0500619_us-ascii Hi, Can anyone tell me if there is an SNMP agent for DB2 z/OS?
Thank you, Efrain J. Berdecia
_____________________________________________________________________
* 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 _____________________________________________________________________ [...]
16421 77 60_Re: [z/OS V9] Default Behavior when no CLUSTER index exists?10_Roger Hecq18_Roger.Hecq@UBS.COM30_Thu, 5 Nov 2009 11:00:48 -0500406_us-ascii Interesting article on CNN re causes of sudden short term memory loss.
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 Adam Baldwin Sent: Thursday, November 05, 2009 10:07 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [z/OS V9] Default Behavior when no CLUSTER index exists? [...]
16499 26 60_Re: [z/OS V9] Default Behavior when no CLUSTER index exists?12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Thu, 5 Nov 2009 14:58:35 +0000469_utf-8 ...didn't the way that Reorg handles an implicit clustering index change once before...back in V4 / V5 days?? I no longer have the manuals and my memory isn't what it was. It's not even friday and I can't remember wednesday.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
16526 93 62_Re: z/OS DB2 V8 - Effects of LOAD ... PART n REPLACE on access12_Isaac Yassin20_yassini@BEZEQINT.NET30_Thu, 5 Nov 2009 18:27:50 +0200528_utf-8 Hi,
The NPI will "kill" you
Isaac Yassin
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Lyon, Lockwood Sent: Thursday, November 05, 2009 4:25 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] z/OS DB2 V8 - Effects of LOAD ... PART n REPLACE on access
Esteemed List:
I'm implementing a table(space) purge process. Table has 7 partitions, one per day-of-week. Partitioning is Table-based. Table has multiple indexes, none of which is partitioned. [...]
16620 134 25_Using DSN_VIRTUAL_INDEXES12_Ayalew Kassa20_ayalakassa@YAHOO.COM30_Thu, 5 Nov 2009 08:52:57 -0800433_iso-8859-1
Hello:
I am simulating impact of dropping an index on the access path of the SQL using DSN_VIRTUAL_INDEXES in DB2 V9 CM mode
The owner of the plan tables and DSN_VIRTUAL_INDEXES is DB2OSC. I do not have any views or aliases on DSN_VIRTUAL_INDEXES or any of the plan tables. My ACF2 id have SYSADM authorization and I was able to SET CURRENT SQLID ='DB2OSC'; before explaining the SQL statement. [...]
16755 134 33_Re: Tape on DASD for DB2 backups?16_Mark Labby - AES21_mlabby@AESSUCCESS.ORG30_Thu, 5 Nov 2009 12:03:37 -0500
16890 216 32_Changing limitkey on a Partition13_Mackey, Glenn20_GMackey@GUIDEONE.COM30_Thu, 5 Nov 2009 11:40:33 -0600339_us-ascii Hi, On a DB2 V8.1 z/OS environment. I have a 700million row table with 24 partitions and the last partition is getting full. I plan to add some more partitions, but also decrease the 24th partition to a lower value from its current high value. I have tested all this and I know all the steps, except if something goes wrong. [...]
17107 141 33_Re: Tape on DASD for DB2 backups?16_Mark Labby - AES21_mlabby@AESSUCCESS.ORG30_Thu, 5 Nov 2009 12:03:37 -0500
17249 674 36_Re: Changing limitkey on a Partition16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 5 Nov 2009 13:01:13 -0500434_us-ascii Have you considered ADDING a new partition using the simple ALTER ?
As long as you are in NFM, you can do this. The object will be converted from Index Controlled Partitioning to Table Controlled partitioning in a heartbeat ;-) but that's not an issue.
Steen Rasmussen CA
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals [...]
17924 248 29_Re: Using DSN_VIRTUAL_INDEXES9_Chris Tee25_chris.tee@NOVAMAGIC.CO.UK30_Thu, 5 Nov 2009 18:00:43 +0000400_iso-8859-1
There isn't much documentation on this at the moment but my guess is that it ignores simulated drops of catalog indexes as you can't drop these indexes. Perhaps Terry or someone else from SVL can shed some light on this.
Chris
Date: Thu, 5 Nov 2009 08:52:57 -0800 From: ayalakassa@YAHOO.COM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Using DSN_VIRTUAL_INDEXES [...]
18173 31 37_Re: DB2 Stored Procedures (mainframe)16_Wolters, Jeff B.24_Jeff.Wolters@PGNMAIL.COM30_Thu, 5 Nov 2009 13:18:41 -0500603_utf-8 Hi All,
We are DB2 V8 NFM on Z/OS 1.9 using WLM defined stored procedures. We have 7 LPARs with 13 subsystems, 4 prod, 9 test. We are also using INSTALL/1 for our 2 Customer Systems.
Our development architect wants to streamline the developers process of creating Stored Procedures. They want to be able to compile the stored procedure directly into the SPAS loadlib (it's just a name -- we use WLM address spaces) and have the programmers be able to refresh the stored procedure load module without intervention from the DBA (currently we(DBAs) issue the WLM refresh command). [...]
18205 97 33_Re: Tape on DASD for DB2 backups?15_Lizette Koehler23_starsoul@MINDSPRING.COM30_Thu, 5 Nov 2009 13:23:35 -0500
18303 511 36_Re: Changing limitkey on a Partition14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 5 Nov 2009 14:05:47 -0500365_us-ascii Glenn,
Did the failed REORG have a curable cause? Can you rerun the REORG after restoring the pending partition? We've had problems with REORP partitions in slightly different circumstances and that's all I come up with on this. Word to the wise: be sure your limitkey is, as you've said, higher than the current data when you make the change. [...]
18815 817 33_Re: Virtual Indexes in DB2 9 z/os13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Thu, 5 Nov 2009 14:14:08 -0500583_utf-8 If your SDSNSAMP library doesn’t contain the DDL for DSN_VIRTUAL_INDEXES you may need to follow this advice I received in a PMR:
Please apply PTF UK34216. The missing SQL will be found in
SDSNSAMP(DSNTIJOS) after installing the PTF. More information can be
found in the parent APAR PK59183.
Dave
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Myron Miller Sent: Monday, November 02, 2009 1:25 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Virtual Indexes in DB2 9 z/os [...]
19633 596 36_Re: Changing limitkey on a Partition12_Isaac Yassin20_yassini@BEZEQINT.NET30_Thu, 5 Nov 2009 21:25:38 +0200683_utf-8 Hi,
Did you check the FlashCopy venue ? (before you alter the limitkey)
Isaac Yassin
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mackey, Glenn Sent: Thursday, November 05, 2009 7:41 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Changing limitkey on a Partition
Hi, On a DB2 V8.1 z/OS environment. I have a 700million row table with 24 partitions and the last partition is getting full. I plan to add some more partitions, but also decrease the 24th partition to a lower value from its current high value. I have tested all this and I know all the steps, except if something goes [...]
20230 601 36_Re: Changing limitkey on a Partition13_Mackey, Glenn20_GMackey@GUIDEONE.COM30_Thu, 5 Nov 2009 13:32:34 -0600322_us-ascii Phil,
I have not had an error yet. I plan to do the work on the weekend and I am making sure that if the REORG did fail with a non-curable cause (whatever that could be) then how would I recover. Also, I can re-run the reorg after running the recover, but if that still fails then I am no better off. [...]
20832 772 36_Re: Changing limitkey on a Partition13_Mackey, Glenn20_GMackey@GUIDEONE.COM30_Thu, 5 Nov 2009 13:37:32 -0600456_us-ascii I do plan to add two new partitions, however the current last partition has a limitkey of high values which I want to reduce. That has been like that since about V6.1
eg: Before: partition 23: 170,000 partition 23: 180,000 partition 24: 999,999
After partition 23: 170,000 partition 23: 180,000 partition 24: 190,000 (When I lower this value from 999,999 then it goes REORGP) partition 25: 200,000 partition 26: 210,000 [...]
21605 474 36_Re: Changing limitkey on a Partition14_Benjamin Kniaz26_Benjamin.X.Kniaz@CHASE.COM30_Thu, 5 Nov 2009 13:55:47 -0600573_us-ascii Hi Glen -
A few years ago, right after my old shop migrated to V8, I did this exact same thing with a very large object. I had to alter the last partition LKey down (From High values) then added partitions to the end.
My REORG did fail (I was using a third party REORG, and I could not make it work, it was performing some calculations incorrectly at the time). I switched to IBM REORG, and the entire time it was running, took about an hour as I recall, I was sitting there trying to figure out 'what am [...]
22080 38 37_Re: DB2 Stored Procedures (mainframe)12_Hepp Shery C21_Shery.Hepp@SRPNET.COM30_Thu, 5 Nov 2009 13:24:31 -0700556_utf-8 I seem to recall there is a stored procedure that can refresh wlm- have you thought about having them run that?
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Wolters, Jeff B. Sent: Thursday, November 05, 2009 11:19 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Stored Procedures (mainframe)
Hi All,
We are DB2 V8 NFM on Z/OS 1.9 using WLM defined stored procedures. We have 7 LPARs with 13 subsystems, 4 prod, 9 test. We are also using INSTALL/1 for our 2 Customer Systems. [...]
22119 55 37_Re: DB2 Stored Procedures (mainframe)0_21_chanck@WELLSFARGO.COM30_Thu, 5 Nov 2009 14:30:35 -0600711_utf-8 WLM_REFRESH
Jacky Chak Kei Chan Community Banking & Enterprise Services Technology (CBEST) chanck@wellsfargo.com Work Phone: (480) 724-6887 Home Office: (480) 456-3436 MAC: S3929-047 http://db2zos.wellsfargo.com
Has a WFS Team Member gone above and beyond providing exceptional service? If so, click to recognize their service.
"This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and [...]
22175 1227 36_Re: Changing limitkey on a Partition13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK30_Thu, 5 Nov 2009 20:54:10 -0000379_us-ascii Hi Glenn
Does you table actually contain any rows that have a higher limitkey than the new value you want to set?
This is why DB2 is setting REORP
If there are NO rows with a key higher than 190000 I'm wondering if you could do a -START ACCESS(FORCE) to obviate the need for the reorg altogether [...]
23403 49 37_Re: DB2 Stored Procedures (mainframe)16_Proctor, William25_William.Proctor@TGSLC.ORG30_Thu, 5 Nov 2009 14:54:26 -0600404_utf-8 You can set it up just like your thinking and they can use the provided stored procedure to refresh the WLM. We went thru this a year ago. It really helped when we didn't have to refresh the WLM several times an hour. We only allow this in the development subsystem. In QA and user acceptence we still do it or make them go thru production control. Production it goes thru production control. [...]
23453 133 37_Re: DB2 Stored Procedures (mainframe)13_arlen stovall22_arlenstovall@GMAIL.COM30_Thu, 5 Nov 2009 15:55:01 -0500619_ISO-8859-1 The stored procedure to refresh the WLM is DSN8ED6.
On Thu, Nov 5, 2009 at 3:24 PM, Hepp Shery C wrote:
> I seem to recall there is a stored procedure that can refresh wlm- have you > thought about having them run that? > > -----Original Message----- > From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Wolters, Jeff > B. > Sent: Thursday, November 05, 2009 11:19 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] DB2 Stored Procedures (mainframe) > > Hi All, > > We are DB2 V8 NFM on Z/OS 1.9 using WLM defined stored procedures. We [...]
23587 83 37_Re: DB2 Stored Procedures (mainframe)21_wfavero@attglobal.net21_wfavero@ATTGLOBAL.NET30_Thu, 5 Nov 2009 15:56:34 -0500530_iso-8859-1 Check out APAR PK74330 for details on using WLM_SET_CLIENT_INFO, the stored procedure Shery was referring to.
Willie
Hepp Shery C wrote:
I seem to recall there is a stored procedure that can refresh wlm- have you thought about having them run that?
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Wolters, Jeff B. Sent: Thursday, November 05, 2009 11:19 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Stored Procedures (mainframe) [...]
23671 361 36_Re: Changing limitkey on a Partition12_Hepp Shery C21_Shery.Hepp@SRPNET.COM30_Thu, 5 Nov 2009 14:35:18 -0700500_us-ascii It is my experience that the only way out of a reogp flag is a reorg share level NONE. Forcing doesn't work. we had the same issue. In our situation we were going from 8 parts to 16. In order to implement it we had to drop and recreate the table because of the limit keys. However- at least in v9 might work in v8 you can add parts but you have to start from the bottom up. I recently had to change some limit keys and it only worked when I altered them last to first. Then reorg. Good [...]
24033 176 37_Re: DB2 Stored Procedures (mainframe)10_Mark Stone17_mastone@GMAIL.COM30_Thu, 5 Nov 2009 13:35:45 -0800589_ISO-8859-1 You can also use the compile directive
--
If your compiling SP's using Visual Studio
On Thu, Nov 5, 2009 at 12:54 PM, Proctor, William wrote:
> You can set it up just like your thinking and they can use the provided > stored procedure to refresh the WLM. We went thru this a year ago. It > really helped when we didn't have to refresh the WLM several times an hour. > We only allow this in the development subsystem. In QA and user acceptence > we still do it or make them go [...]
24210 719 36_Re: Changing limitkey on a Partition13_Mackey, Glenn20_GMackey@GUIDEONE.COM30_Thu, 5 Nov 2009 15:45:58 -0600298_us-ascii g'day all,
I did ponder that and dismissed because in my mind it was too much work and introduced more risk. I thought I would also have to get the catalog back in sync with the table after a restore - I did not want to play with the catalog and interrupt the whole subsystem. [...]
24930 679 36_Re: Changing limitkey on a Partition13_Mackey, Glenn20_GMackey@GUIDEONE.COM30_Thu, 5 Nov 2009 16:08:07 -0600483_us-ascii The max value is lower than 190,000 in my example
Good thought regarding the START ACCESS FORCE, but I just tried it but no go. It remains in REORGP I looked for a REPAIR option - but no luck there either.
Of course, if I knew where that baby was recorded I could zap it - couldn't I? _ maybe no But, even though the REORG will do "nothing" from a conceptual point of view, there maybe something I am not aware of which may bite me( read lose job) later. [...]
25610 159 62_Re: z/OS DB2 V8 - Effects of LOAD ... PART n REPLACE on access11_Nick Cianci19_ncianci@AU1.IBM.COM30_Fri, 6 Nov 2009 09:10:54 +1100437_ISO-8859-1 We do something similar without issues, however I need to point out that we don't have NPI's on the object. The only problem I believe we encountered was contention with other utilities.
If you do have NPI's review if they can be converted to DPSI's. DPSI's may not be a good idea if all your SQL doesn't prune (or qualify) the partition, although with only 7 partitions it sound like that may still be an option. [...]
25770 36 27_Security for Delete Cascade10_Teldb2kals22_teldb2kals@TELSTRA.COM30_Thu, 5 Nov 2009 22:26:25 +0000365_utf-8 Hi,
I see that when a user deletes a record from a table that is defined as the parent of a child table with DELETE CASCADE, the user does not need delete access on the child table. He needs access only on the parent table.
Can someone tell me where this is documented ? I am not sure I am searching correctly, as I am not able to find it. [...]
25807 134 36_Re: Changing limitkey on a Partition11_Nick Cianci19_ncianci@AU1.IBM.COM30_Fri, 6 Nov 2009 09:49:53 +1100325_UTF-8 Hi Glenn,
Some good notes and replies and notes. Phil's note about START ACCESS(FORCE) would be worth considering if the end partition is empty and the ReOrg goes south. Good one Phil I'll bear that one in mind for my next round of RePartitioning. (Oh no just read Shery Hepp's note ... oh fizzle sticks !) [...]
25942 60 26_Re: Optimizers and Indexes13_Terry Purcell18_tpurcel@US.IBM.COM30_Thu, 5 Nov 2009 23:21:10 +0000400_utf-8 Denise,
If you are truly fetching a small subset of the rows, then OPTIMIZE FOR 1 ROW is valid to use. And you would expect the optimizer to favor an index with more filtering.
Without having IBM support look at it, it's hard to give you an exact answer. Although there is at least 1 fix out there that could be a hit - once again, hard to say given the limited information. [...]
26003 125 26_Re: Optimizers and Indexes12_Myron Miller22_myronwmiller@YAHOO.COM30_Thu, 5 Nov 2009 15:37:27 -0800596_iso-8859-1 Remember that Optimize for 1 row is a special case that the optimizer treats totally differently from other situations. It considers clustering and min size indexes to get that first row the fastest regardless of other situations. The path can be drastically different with OPTIMIZE for 1 row verus other options. Optimize for x rows is now taken into account by the optimizer whereas it used to be that only OPTIMIZE for 1 row was the only special case that the optimizer treated differently. I would, however, disagree in general with the other people that say never lie to the [...]
26129 453 29_Re: Using DSN_VIRTUAL_INDEXES13_Terry Purcell18_tpurcel@US.IBM.COM30_Fri, 6 Nov 2009 02:07:24 +0000510_utf-8 My first thought is that you shouldn't be able to model the drop of a primary index. And in addition, when you do model a drop, unfortunately you need the COLNO & ORDERING columns to be populated.
Here is an example against another catalog table - SYSINDEXES:
INSERT INTO DSN_VIRTUAL_INDEXES (TBCREATOR, TBNAME, IXCREATOR, IXNAME, ENABLE, MODE, UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE, PGSIZE, FIRSTKEYCARDF, FULLKEYCARDF, CLUSTERRATIOF, PADDED, COLNO1, ORDERING1) [...]
26583 317 182_Upcoming IDUG Webcast - "The DB2Night Show Episode #5 - DB2 LUW SQL Snapshot POTLUCK! - Scott Hayes, DBI Software" - Thursday, November 12, 2009 at 10:00 am USA Central Standard Time13_David Chapman24_David.Chapman@IAG.COM.AU30_Fri, 6 Nov 2009 17:37:44 +1100751_us-ascii
_______________________________________________________________________________________
Note: This e-mail is subject to the disclaimer contained at the bottom of this message. _______________________________________________________________________________________
Hello DB2 List,
The DB2Night Show Episode #5 - DB2 LUW SQL Snapshot POTLUCK! - Scott Hayes, DBI Software
The International DB2 Users Group (IDUG) is pleased to partner with DBI to deliver a free Webcast by renowned presenter Scott Hayes, President & CEO, DBI. 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. [...]
26901 52 37_Re: DB2 Stored Procedures (mainframe)14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Fri, 6 Nov 2009 09:23:31 +0100472_utf-8 Hi,
The stored procdure's name is WLM_REFRESH. It has 4 parameters.
greetings,
Patrick Steurs
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Hepp Shery C Sent: donderdag 5 november 2009 21:25 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Stored Procedures (mainframe)
I seem to recall there is a stored procedure that can refresh wlm- have you thought about having them run that? [...]
26954 205 37_Re: DB2 Stored Procedures (mainframe)14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Fri, 6 Nov 2009 09:26:00 +0100579_us-ascii Hi,
DSN8ED6 is a sample program that calls the SP WLM_REFRESH.
greetings,
Patrick Steurs
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of arlen stovall Sent: donderdag 5 november 2009 21:55 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Stored Procedures (mainframe)
The stored procedure to refresh the WLM is DSN8ED6.
On Thu, Nov 5, 2009 at 3:24 PM, Hepp Shery C wrote: [...]
27160 50 29_Question on a DSNZPARM change13_Robert Knight28_bknight@REMOTEDBAEXPERTS.COM30_Fri, 6 Nov 2009 10:13:55 -0500516_us-ascii DB2 V8
I would like to increase the NUMLKTS & NUMLKUS parameters.
Currently they are using the default parameters of 1000/10000.
If I execute the ZPARM procedure do I need to execute all of the steps involved. After execution I plan to execute: -SET SYSPARM RELOAD(DSNZPARM).
DSNTIZA STEP ASSEMBLE DSN6.... MACROS, CREATE DSNZPARM DSNTIZL STEP LINK EDIT DSNZPARM DSNTIZP STEP ASSEMBLE DSNHDECP DATA-ONLY LOAD MODULE DSNTIZQ STEP LINK EDIT DSNHDECP LOAD MODULE INTO SDSNEXIT [...]
27211 91 33_Re: Question on a DSNZPARM change21_wfavero@attglobal.net21_wfavero@ATTGLOBAL.NET30_Fri, 6 Nov 2009 10:33:38 -0500441_iso-8859-1 You just need to create a new DSNZPARM load module with the name that you are going to use on the SET SYSPARM command.
Willie
Original Message: ----------------- From: Robert Knight bknight@REMOTEDBAEXPERTS.COM Date: Fri, 6 Nov 2009 10:13:55 -0500 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Question on a DSNZPARM change
DB2 V8
I would like to increase the NUMLKTS & NUMLKUS parameters. [...]
27303 75 37_Re: DB2 Stored Procedures (mainframe)15_Kalena, Michael27_Michael.Kalena@JPMORGAN.COM30_Fri, 6 Nov 2009 10:36:13 -0500632_us-ascii Hi,
Last time I used this SP, I found it will let you restart any WLM Environment. For example, you could run against test but refresh a production WLM Environment.
Just something I thought you'd want to know/verify before giving to developers.
Michael Kalena 973-793-2133 Michael.Kalena@jpmorgan.com
DB2 Info Page at hBSC (http://whsysops1.is.bear.com/db2/)
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Steurs Patrick Sent: Friday, November 06, 2009 3:24 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Stored Procedures (mainframe) [...]
27379 328 33_Re: Question on a DSNZPARM change12_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM30_Fri, 6 Nov 2009 10:39:19 -0500503_ISO-8859-1 Hello Robert,
No. I have set up 2 separate jobs for ZPARMs and DSNHDECP using DSNTIJUZ as model. Remove the: DSNTCIDA STEP ASSEMBLE DSNHMCID DATA-ONLY LOAD MODULE DSNTCIDE STEP LINK EDIT DSNHMCID LOAD MODULE INTO SDSNEXIT DSNTCIDL STEP LINK EDIT DSNHMCID LOAD MODULE INTO SDSNLOAD DSNTLOG STEP UPDATE PASSWORDS DSNTIMQ STEP SMP/E PROCESSING FOR DSNHDECP from zparm job. ZPARM job has 2 steps: DSNTIZA STEP ASSEMBLE DSN6.... MACROS, CREATE DSNZPARM DSNTIZL STEP LINK EDIT DSNZPARM [...]
27708 62 33_Re: Question on a DSNZPARM change10_Todd Burch17_toddburch@MAC.COM30_Fri, 6 Nov 2009 09:48:34 -0600533_US-ASCII Hi Bob.
There's no point in remaking DSNHDECP.
And, unless you are changing CCSID's, there no point in jacking with DSNHMCID either.
Todd
On Nov 6, 2009, at 9:13 AM, Robert Knight wrote:
DB2 V8
I would like to increase the NUMLKTS & NUMLKUS parameters.
Currently they are using the default parameters of 1000/10000.
If I execute the ZPARM procedure do I need to execute all of the steps involved. After execution I plan to execute: -SET SYSPARM RELOAD(DSNZPARM). [...]
27771 72 33_Re: Question on a DSNZPARM change13_Mick P Graley16_mgraley2@CSC.COM30_Fri, 6 Nov 2009 16:00:31 +0000627_UTF-8 Hi Bob,
You just need to run steps DSNTIZA and DSNTIZL then run the set sysparm reload command.
Cheers,
Mick.
MICK GRALEY DB2 for z/OS DBA - GOS DBA Service & Capability Centre CSC
GOS | office: +44 (0)1246 214871 | fax: +44 (0)1246 214503 | mgraley2@csc.com | www.csc.com
CSC • This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit [...]
27844 82 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security13_Mick P Graley16_mgraley2@CSC.COM30_Fri, 6 Nov 2009 16:16:10 +0000346_UTF-8 Hi All,
Does anyone know if an EDITPROC could help Ray here? They get executed each time a row is retrieved from the table but they have the following limitations that may make them useless to him: It must not invoke any DB2 services—for example, through SQL statements. It must not invoke any SVC services or ESTAE routines. [...]
27927 784 37_Re: DB2 Stored Procedures (mainframe)13_David Simpson22_dsimpson@THEMISINC.COM30_Fri, 6 Nov 2009 12:17:13 -0500550_us-ascii The procedure does need to run in an authorized address space, so this is a concern. In one shop we wrapped the call to WLM_REFRESH in a REXX for the developers to use. If you do it this way you can control which WLMs they are allowed to refresh.
________________________________________________________________________ ______ David Simpson | Senior Technical Advisor | Themis Education 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | dsimpson@themisinc.com [...]
28712 65 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security13_Irwin Deutsch28_ideutsch@PRINCESSCRUISES.COM30_Fri, 6 Nov 2009 09:32:32 -0800543_US-ASCII Mick (and Ray),
I'm not an expert on Editproc, but there is a branch entry invocation of SMF for authorized programs, which might work to write records to SMF. Check the SMF manuals on MVS bookshelf. Macro is SMFEWTM (with Branch=Yes).
Regards,
Irwin
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
28778 54 37_DB2 z/OS Code Samples - any requests?10_Todd Burch17_toddburch@MAC.COM30_Fri, 6 Nov 2009 14:23:40 -0600341_US-ASCII Hi All. Happy Friday.
I've gotten on a kick lately to post some code examples to the DB2 z/ OS Exchange out on Developerworks. I've posted a few things, and instead of just guessing what might be interesting to people, I figured I would ask.
So, you got any requests? I've quite an arsenal in my recreate bucket. [...]
28833 118 41_Re: DB2 z/OS Code Samples - any requests?15_Kalena, Michael27_Michael.Kalena@JPMORGAN.COM30_Fri, 6 Nov 2009 16:20:37 -0500628_us-ascii Funny you should ask.
Another DBA here was just experimenting with using DBINFO on a SP call to get the thread's LUWID and pass it back to the caller. We're DB2 v8 on z/OS.
It didn't work as documented but looks like it's an undocumented parm?
i.e.
This Linkage Section:
LINKAGE SECTION. *DECLARE THE SQLSTATE THAT CAN BE SET BY STORED PROC 01 P-SQLSTATE PIC X(5). * DECLARE THE QUALIFIED PROCEDURE NAME 01 P-PROC. 49 P-PROC-LEN PIC 9(4) USAGE BINARY. 49 P-PROC-TEXT PIC X(27). * DECLARE THE SPECIFIC PROCEDURE NAME 01 P-SPEC. 49 P-SPEC-LEN PIC 9(4) USAGE BINARY. 49 P-SPEC-TEXT [...]
28952 95 41_Re: DB2 z/OS Code Samples - any requests?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Fri, 6 Nov 2009 16:43:19 -0500484_us-ascii I'm not sure if it'd be useful, or just cute... but I rewrote one of Suresh Sane's recursive SQLs a while ago, the one that generates a Fibonacci series. Would that be something you'd like to put out there for educational purposes?
--Phil
WITH FIB_SUM(LEVEL, PREV_NUM, NEW_NUM) AS (SELECT 1,0,1 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT LEVEL+1, NEW_NUM, PREV_NUM + NEW_NUM FROM FIB_SUM WHERE LEVEL<=10 ) SELECT A.LEVEL, A.NEW_NUM FROM FIB_SUM A ORDER BY 1 ; [...]
29048 52 66_Re: z/OS DB2 V8. Best practice to log READ on a table for security11_Nick Cianci19_ncianci@AU1.IBM.COM30_Sat, 7 Nov 2009 12:53:02 +1100356_ISO-8859-1 Sorry to cast a further headache, but if you are trying to log any access to the table you may also need to consider, the Auth-ID that created the table as well as any with DBADM SYSADM and ID's with utility access (even DSN1COPY / DSN1PRNT) Then there is the log (active and Archive) & any Log scraping tools that you may want to consider [...]
29101 133 41_Re: DB2 z/OS Code Samples - any requests?10_Todd Burch17_toddburch@MAC.COM30_Fri, 6 Nov 2009 22:21:44 -0600406_US-ASCII Hi Phil.
I would suggest that since this something that you've done, or Suresh has done, that one of y'all post it out there.
Just create a logon id, filter for ALL, and click SUBMIT on the right margin.
Here's the link for the DB2 z/OS stuff on Developerworks: http://www.ibm.com/developerworks/data/products/db2zos/
Scroll down to the link for the DB2 Exchange. [...]
29235 131 41_Re: DB2 z/OS Code Samples - any requests?10_Todd Burch17_toddburch@MAC.COM30_Fri, 6 Nov 2009 23:19:02 -0600378_US-ASCII I'll look into this.
Todd
On Nov 6, 2009, at 3:20 PM, Kalena, Michael wrote:
Funny you should ask.
Another DBA here was just experimenting with using DBINFO on a SP call to get the thread's LUWID and pass it back to the caller. We're DB2 v8 on z/OS.
It didn't work as documented but looks like it's an undocumented parm? [...]