1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 2005, week 1 2 452 26_Re: SMTP Server Assistance19_Michael Piontkowski28_piontkowskim@GSICOMMERCE.COM30_Tue, 1 Nov 2005 08:38:00 -0500436_us-ascii Did you find a solution to your problem? We're also troubleshooting the same problem.

Our test & production DAS servers are Windows Server 2003 SP1 1. ensure that the SMTP server will accept e-mail traffic from the DAS server 2. From a DOS command prompt on the DAS server issue the command telnet 25. If the communication lines are open, you will be presented with the SMTP "command line" interface. [...] 455 85 22_Active Logs Filling up21_Sriramulu, Vijayababu28_vijayababu.sriramulu@EDS.COM30_Tue, 1 Nov 2005 19:17:17 +0530539_iso-8859-1 Dear List Users,

Is there any way I can find out which all threads that are active currently that are filling up the logs.

When I get a call from the operators that the active logs are getting filled up fast and I see that only 2 or 3 more active logs are still available out of the 24 defined active logs, how can I find out the thread/utility that is filling up these logs. There could be a lot of threads which are doing mass inserts/deletes or it could be utilities doing a LOG YES processing or it could [...] 541 34 33_Re: EDM POOL monitoring Question?10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 1 Nov 2005 14:54:18 +0100570_US-ASCII Hi all

EDM pool tuning rules (at least for me) quite simple: No LOAD failures in a SIGNIFICANT (significant for me is month, but YMMV) period of time (MainView has some tables for collecting data) ? You're ALMOST OK,you're using EDM pool without wasting nothing as % of free pages is low. But why Almost ? Because you've few free pages left but probably you've NO IDEA about EDM pool's objects growth in the next future (new applications ? objects ?) as I currently haven't in the shop were I work. In my old shop I progressively decreased EDM pool [...] 576 151 26_Re: Active Logs Filling up13_Keemle Joan T25_KeemleJoanT@JOHNDEERE.COM30_Tue, 1 Nov 2005 07:59:44 -0600528_us-ascii This is the procedure we use: If someone is rolling logs excessively, review the console log for exact time of messages. Print the BSDS to view log archival times and RBAs. To view details of logs, run DSN1LOGP with SUMMARY(ONLY). Look for URs INFLIGHT in the restart summary section at the end of the report. Or to get Detail report, execute with DATAONLY(NO) and SUMMARY(NO). Find 'urid' all. If the same job appears multiple times, this is probably the culprit. Good luck! Joan ________________________________ [...] 728 85 7_Goodbye12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Tue, 1 Nov 2005 09:05:28 -0500426_us-ascii I'm sending my goodbyes to all my listserves. I'm being laid off and my last day is Friday. They extended me twice, but this time it's really happening. I've really appreciated all the help I've received from this list. I hope I've provided help to others in return.

I don't plan on staying in the IT field, but am keeping my options open. It's been an enjoyable ride, but I think I'm ready for a change. [...] 814 66 11_Re: Goodbye0_25_scott.taylor@AUTOZONE.COM30_Tue, 1 Nov 2005 08:07:56 -0600621_UTF-8 Best of luck in your future endeavors and Thanks for all your contributions to this list! ----------------------------------------------------------------------------------------------------

Scott Taylor Systems Programmer, Autozone 123 S Front St, Memphis, TN 38103 (901)/495-7797







"Hilton, Tina" To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List [DB2-L] Goodbye



11/01/2005 08:05 AM



Please respond to DB2 Database Discussion list at IDUG [...] 881 399 26_Re: SMTP Server Assistance14_Toppins, Smike21_smike.toppins@GWL.COM30_Tue, 1 Nov 2005 07:32:22 -0700683_us-ascii I've got a PMR opened with IBM and am awaiting a response from them. Sounds like a similar situation as ours.

SMike Toppins Great-West Life 8525 E. Orchard Road Englewood, CO 80111 smike.toppins@gwl.com ----Statement of Confidentiality---- This e-mail, and any attachments thereto, is intended for the exclusive use of the addressee(s) named herein and may contain information that is privileged or confidential or otherwise legally exempt from disclosure. If you are not a named addressee or an employee or agent responsible for delivering this message to a named addressee, you are not authorized to read, print, retain, copy or [...] 1281 17 11_Re: Goodbye10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 1 Nov 2005 15:57:20 +0100597_US-ASCII GOOD LUCK Tina ! I enjoyed your posts and I hope you''l remain in DB2 area :-))

Cheers ___ Max " V -1 " Scarpa

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 1299 17 11_Re: Goodbye14_Kevin Fletcher26_kevin_fletcher@CONSECO.COM30_Tue, 1 Nov 2005 09:00:23 -0600638_- Tina,

I have always enjoyed your posts and will miss them greatly. No matter what you decided to do, I wish you the best of luck.

Thanks,

Fletch

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 1317 200 26_Re: Active Logs Filling up16_Trivedi, Kaushal34_Kaushal.J.Trivedi@CITIZENSBANK.COM30_Tue, 1 Nov 2005 08:54:08 -0500514_- Vijay,

With omegamon one thing you can do is sort out your threads on SQL_CNT and find out which ones are the top ones. And go from there. You may also want to look at displaying if any UTILITY is in progress which may have LOG set to YES.





HTH





Kaushal Trivedi

_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Sent: Tuesday, November 01, 2005 8:47 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Active Logs Filling up [...] 1518 25 54_Questions on MEMLIMIT setting in DB2 V8 (APAR PK06380)14_Wayne Driscoll34_wdriscoll@WESTERN-METAL-SUPPLY.COM30_Tue, 1 Nov 2005 10:20:46 -0600545_us-ascii There has been a lot of discussion on the IBM-MAIN list lately about DB2 V8 ignoring the MEMLIMIT setting and enforcing a setting of 4TB for the DBM1 address space. I find this astonishing that IBM feels it is acceptable to allow an installation to set the limit for address space, only to have IBM products not only ignore the limit, but to set a value that is larger for a single address space, than z/OS supports for the total paging subsystem. Is this really a good idea, to have a setting such that a typo on a size parameter [...] 1544 108 58_Re: Questions on MEMLIMIT setting in DB2 V8 (APAR PK06380)14_Avram Friedman21_avramf@IBMSYSPROG.COM30_Tue, 1 Nov 2005 10:20:58 -0800387_iso-8859-1 IMHO There are two issues going on here. 1. DB2 V8 is one of the first aggressively 64 bit exploiting applications. It is important to both the consumer and vendor that it work 'out of the box'. 2. There is a very old philosophical controversy, where do controls belong, at some super master level like an operating system or at a micro managed level like a application. [...] 1653 195 26_Re: Active Logs Filling up15_Carol L Broyles30_carol.broyles@MEADWESTVACO.COM30_Tue, 1 Nov 2005 13:38:26 -0500344_US-ASCII I can usually tell who's filling up a log by tracking the DSNJ031I messages in the DSNMSTR log. Our ZPARMs are set to display messages on URs that haven't committed after writing 100,000 log records (URLGWTH). This is an online ZPARM, so you could dynamically set it lower if you needed to in order to catch the offending thread. [...] 1849 144 11_Re: Goodbye10_Jwkwan200025_jwkwan2000@HOUSTON.RR.COM30_Tue, 1 Nov 2005 12:46:38 -0600380_iso-8859-1 GoodbyeTina,

Sorry to hear you are leaving the IT industry. We kept talking about could not find mainframe skills and yet we kept losing good people due to job loss.

James Kwan ----- Original Message ----- From: Hilton, Tina Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Tuesday, November 01, 2005 8:05 AM Subject: [DB2-L] Goodbye [...] 1994 154 67_EZ-DB2 Public Webinar - Save 25% of total prime shift DB2 CPU Usage10_David Kane14_dkane@TACT.COM30_Tue, 1 Nov 2005 16:24:35 -0500484_iso-8859-1

We boldly challenge you to find another product that Captures and Consolidates Dynamic SQL that are essentially the same, thus allowing you to automatically IDENTIFY the worst resource hogs! Dear DB2 Technician, Join TACT Software online for a free "Webinar" presentation:

"How EZ-DB2 Captures and Consolidates Dynamic SQL from the Distributed Environment, and helps you dramatically improve your Mainframe DB2 Performance." Thurs. Nov 3rd 1 pm Eastern [...] 2149 34 11_unsubscribe12_John Lorance19_lorancejd@YAHOO.COM30_Tue, 1 Nov 2005 16:50:45 -0800211_iso-8859-1



--------------------------------- Yahoo! FareChase - Search multiple travel sites in one click.

--------------------------------------------------------------------------------- 2184 189 31_union all vs. left outer join ?15_Henrik Sorensen27_Henrik_Sorensen@RCOMEXT.COM30_Wed, 2 Nov 2005 11:20:28 +0100309_US-ASCII Esteemed list,

On z/OS: DB2 V8 CM and DB2 V7.

For several days I have tried to figure out why two sql with an union all is much faster than using a single sql with left outer join. But I cannot get my head around this one. It is quite possible I am overlooking something simple... [...] 2374 59 43_Design of archival and retrieval techniques17_gousrdb2 gousrdb218_gousrdb2@GMAIL.COM30_Wed, 2 Nov 2005 16:23:08 +0530589_ISO-8859-1 Hi All,

We are designing a DB2 system in which we need to keep *6 months* of data in table and total of 10 years of arhived data. There are couple of function in a system which frequently requires archived data (for both read and update). In this system there will be 5 tables which will contain approx *5-7 terra bytes* of data over a period of *10 years*. Please suggest the best design by which we can optimally archive and retrieve. Appreciate if you folks can share any techniques/implementation strategies you know off. *DB2 --> version 7.1 Platform--> OS390* [...] 2434 88 34_Queries on Partitioned Table Space17_gousrdb2 gousrdb218_gousrdb2@GMAIL.COM30_Wed, 2 Nov 2005 17:07:21 +0530474_ISO-8859-1 Hi All,

I have some queries regarding partitioned table space. They are as follows:

1. Can we have unequal partitioned table space?

2. If yes, Can we map different partitioned table space to different media? (Ex: one PTS to dasd other to tape)

3. If a record is to be requested from tape partition, does DB2 handle it internally or what would be the procedure to be followed in this case? Should this be handled programmatically? [...] 2523 36 28_lock and concurrency problem8_duam lee20_duam_lee@HOTMAIL.COM30_Wed, 2 Nov 2005 11:57:37 +0000567_- Hi Esteemed Listers,

here is a situation I describe and expecting responses form you. I know that there are experts in this list about db2 locking and I would be thankful to all.

There are two application and can access the data on a particular table. The application 1 is extarct and through ETL tool the data is extracted form db2. The thread stays there and read it for 15 minutes etc. The query is based on a timestamp range. The application 2 is a online transaction and it has the facility to update the transaction date. The application 1 [...] 2560 220 38_Re: Queries on Partitioned Table Space14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 2 Nov 2005 12:07:02 -0000447_us-ascii See below for answers.

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com







_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of gousrdb2 gousrdb2 Sent: 02 November 2005 11:37 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Queries on Partitioned Table Space [...] 2781 464 38_Re: Queries on Partitioned Table Space12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Wed, 2 Nov 2005 07:58:19 -0500588_us-ascii For the answer to #5, shouldn't it be "it depends"? If the tablespace is not defined with the LARGE or DSSIZE parameter, then the last partition will contain all rows after the limit key of the previous partition. It ignores the limit key of the last partition. Right?





Tina Hilton





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: Wednesday, November 02, 2005 7:07 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Queries on Partitioned Table Space [...] 3246 460 38_Re: Queries on Partitioned Table Space14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 2 Nov 2005 13:05:29 -0000372_us-ascii You know, I can never remember, but that whole mess disappears with table-bases partitioning anyway

Also, I think it might also be true of ALL partitioned tablespaces created after (about) V4.....

I think this has been discussed before, so does anyone know the RIGHT answer to when DB2 will NOT honour the high limit key of the last partition? [...] 3707 421 38_Re: Queries on Partitioned Table Space12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Wed, 2 Nov 2005 08:06:14 -0500484_us-ascii Correction: I meant to say that the row beyond the limit key of the last partition might or might not be inserted. I didn't mean to imply that partitions may be created/deleted.





For the answer to #5, shouldn't it be "it depends"? If the tablespace is not defined with the LARGE or DSSIZE parameter, then the last partition will contain all rows after the limit key of the previous partition. It ignores the limit key of the last partition. Right? [...] 4129 50 27_Asynchronous Read I/O Waits21_Deepak Gujaba Gaikwad24_deepakggaikwad@YAHOO.COM30_Wed, 2 Nov 2005 06:04:35 -0800487_iso-8859-1 Hello All,

Last night one of monthly job ran about 18 hours without finishing. We cancelled and reran it without any changes to job or DB2 and it finished within 50 mins. The job regularly runs in an hour. The job runs DSNTIAUL over a view which consists of join with a table with 14GB data. Omegamon showed Asynchronous Read I/O Waits for 17+ hours for that particular run. Please help us know what contributes to Asyn. Reads and what could be the problem error. [...] 4180 191 38_Re: Queries on Partitioned Table Space14_Andy Lankester26_alankester@CDBSOFTWARE.COM30_Wed, 2 Nov 2005 08:21:33 -0600470_windows-1250 It is possible to have partitions migrated to tape via HSM or equivalent. However, any reference or update to such a partition will cause it to be migrated back to DASD by HSM before being processed.

Andy

_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of gousrdb2 gousrdb2 Sent: Wednesday, November 02, 2005 11:37 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Queries on Partitioned Table Space [...] 4372 94 47_Re: Design of archival and retrieval techniques14_Avram Friedman21_avramf@IBMSYSPROG.COM30_Wed, 2 Nov 2005 06:45:49 -0800439_iso-8859-1 For actual archive and possible archive retrieval I suggest purchasing a product. The oldest vendor and still the industry leader in this area is Princeton Softech Archive for DB2

In terms of data / data base design 1. Do not move things to archive until you are 99.9% sure the archive data will never be retrieved. This degree of assurance is needed to guarantee that archiving will result in performance benefits. [...] 4467 83 31_Re: Asynchronous Read I/O Waits9_Joe Burns25_joseph.burns@HIGHMARK.COM30_Wed, 2 Nov 2005 08:40:35 -0600495_- Hi Deepak,

Well that doesn't sound good at all. So the job ran for roughly 18 hours, and had 17 hours of Async Wait time. That means that it got no more than 1 hour of CPU time.

When I read your post I thought it might be some time of RID pool failure that resulted in a tablescan. Or maybe it was a tablescan that happened just because DSNTIAUL was using dynamic SQL and happened to pick a bad path. But with less than 1 hour of CPU time, then it's NOT likely a tablescan. [...] 4551 122 31_Re: Asynchronous Read I/O Waits35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Wed, 2 Nov 2005 09:40:28 -0500303_iso-8859-1 These are waits for prefetch I/Os, and might be all from this job, or other system prefetch activity. You need to l,ook at the number of asynch read requests from the two executions of this job to see if the level of activity was about the same. You obviously had a massive I/O problem. [...] 4674 234 32_Re: lock and concurrency problem25_Biswal, Manas (Cognizant)26_Manas.Biswal@COGNIZANT.COM30_Wed, 2 Nov 2005 20:29:05 +0530600_iso-8859-1

Depends on what you want. If you want no other online transactions (application 2 in your case) to update data in the range of your ETL select (application 1 in your case), use an isolation level of RS for your select statement. That will prevent other applications from making changes to the data in the range of your select statement. If your select range is almost the entire table(75% or more), I would suggest locking the entire table explicitly using a "lock table" statement to conserve locklist space. (DB2 will most probably escalate it to a tablespace lock anyway). [...] 4909 92 38_Re: Queries on Partitioned Table Space14_Avram Friedman21_avramf@IBMSYSPROG.COM30_Wed, 2 Nov 2005 07:01:56 -0800437_iso-8859-1 Unless you are designing a journal write once, read never application think long and hard before designing a partitioning index that is ever increasing. One such bad choice for bad key selection might be a time stamp and sequence number.

Such designs result in a small number of very hot partitions that change from time to time and a large number of very quite partitions. A management and performance nightmare. [...] 5002 339 38_Re: Queries on Partitioned Table Space16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Wed, 2 Nov 2005 09:25:36 -0600359_us-ascii DB2 does honor the HIGH limitkey when the object is defined with LARGE or it's a V8 table based partitioned object - even if you try to alter from a INDEX based partitioned by using one of the new ALTER statements, DB2 might place the partition in REORP.





Kind regards Steen Rasmussen Computer Associates Senior Consultant [...] 5342 468 35_Re: union all vs. left outer join ?11_David Nance16_DWNance@FHSC.COM30_Wed, 2 Nov 2005 07:49:36 -0700495_us-ascii Henrik, Based on the SQL and the join conditions, I would think what you really want is something like below. In your current SQL that you provided you get the materialization due to the AND/OR condition on the join to the D table and no direct correlation to the A table from either the D or E tables. SELECT * from E inner join D left outer join B INNER JOIN A on A.id = B.aid ON d.bid = B.id left outer join C INNER JOIN A on A.id = C.aid ON d.bid = C.bid on E.id = D.eid where [...] 5811 83 34_Soc4 and bind of Package on DB2 v813_Wayne Stevens25_twstevens@DHR.STATE.AL.US30_Wed, 2 Nov 2005 09:29:30 -0600693_us-ascii Binding of a package with SQL:

SELECT UPPER(RTRIM(TX_TYPE_ASGN)) ,ID_WRKR INTO :P1.V_LAST_ASGN_TYPE :P1.SQLP_I17, :P1.V_LAST_WRKR_ID :P1 .SQLP_I10 FROM (SELECT ID_TYPE_ASGN ,ID_WRKR FROM (SELECT ID_TYPE_ASGN ,ID_WRKR_OFC FROM PIV_ASGN_INVS WHERE ID_INVS = :SQLRoutine.P_ID_INVS : SQLRoutine.SQLP_I1 AND DT_ACTV > DT_INACTV AND ID_WRKR_OFC = (SELECT DISTINCT ID_WRKR_OFC FROM APPROVAL WHERE DT_ACTV > DT_INACTV AND ID_APRV IN (SELECT ID_APRV FROM PIV_INVS_APRV WHERE ID_INVS = :SQLRoutine. P_ID_INVS :SQLRoutine. SQLP_I1 ) ) ) T1, PIV_WORKER_OFFICE WHERE T1.ID_WRKR_OFC = PIV_WORKER_OFFICE.ID_WRKR_OFC ) T2, REF_TYPE_ASGN WHERE T2.ID_TYPE_ASGN = REF_TYPE_ASGN.ID_TYPE_ASGN [...] 5895 152 31_Re: Asynchronous Read I/O Waits11_David Nance16_DWNance@FHSC.COM30_Wed, 2 Nov 2005 07:54:40 -0700375_us-ascii Deepak, It seems that you probably would have had some really odd access path change last night. When you run DSNTIAUL, the SQL is dynamic and therefore susceptible to a change in access path each time you run it. Was there anything going on last night that could have affected the optimizer's decision that wasn't going on this morning when you reran the job? [...] 6048 472 26_Re: SMTP Server Assistance19_Michael Piontkowski28_piontkowskim@GSICOMMERCE.COM30_Wed, 2 Nov 2005 10:40:03 -0500415_us-ascii I opened a PMR too and DB2 Support confirmed that the DAS is configured correctly.

Our Windows Admin has determined that the problem seems to be related to Windows maintenance (not a service pack) that was applied to the DAS server. The "telenet 25" command fails on all Windows servers where the maintenance was applied. Unfortunately, I don't have the specific maintenance yet. [...] 6521 185 47_Re: Design of archival and retrieval techniques11_David Nance16_DWNance@FHSC.COM30_Wed, 2 Nov 2005 08:09:10 -0700544_us-ascii Gousr, As someone else has already suggested you probably want to consider a tool for this. Especially if there is RI between the tables and you would need to retrieve the data in whole. We do use Princeton Archive here with our applications. Though we are looking at making some changes, once we upgrade to V8. The amount of data we archive is fairly significant and the deletes eat up a lot of the log and cost a bit CPU wise. Our solution is to add an archive date on all of these referential tables that would be populated at [...] 6707 88 38_Re: Soc4 and bind of Package on DB2 v812_Roger Miller19_millerrl@US.IBM.COM30_Wed, 2 Nov 2005 10:07:28 -0600699_- It looks like you should call service for this one. You need to get the information from the abend header at a minimum to avoid wasting time.

Roger Miller

On Wed, 2 Nov 2005 09:29:30 -0600, Wayne Stevens wrote:

>Binding of a package with SQL: > >SELECT UPPER(RTRIM(TX_TYPE_ASGN)) > ,ID_WRKR >INTO :P1.V_LAST_ASGN_TYPE :P1.SQLP_I17, :P1.V_LAST_WRKR_ID :P1 > .SQLP_I10 >FROM (SELECT ID_TYPE_ASGN > ,ID_WRKR > FROM (SELECT ID_TYPE_ASGN > ,ID_WRKR_OFC > FROM PIV_ASGN_INVS > WHERE ID_INVS = :SQLRoutine.P_ID_INVS : > SQLRoutine.SQLP_I1 > AND DT_ACTV > DT_INACTV > AND ID_WRKR_OFC = > (SELECT DISTINCT ID_WRKR_OFC > FROM APPROVAL > WHERE DT_ACTV > [...] 6796 127 26_Re: Active Logs Filling up11_Chris White18_crick616@YAHOO.COM30_Wed, 2 Nov 2005 11:26:48 -0600576_- All the suggestions so far are good but some are time consuming and others have loopholes that might not catch the culprit. Here's a technique using Omegamon that works well for me...

Using the "All Threads" screen in Omegamon (Option "T."), you can sort the output in descending order on the "Update" column by placing the cursor on that column (anywhere in that column) and then press the "PF10" key. This produces a sorted list that will show those who have done the most updates at the top, *BUT* there is one caveat... all activity in DSNDB07 is counted as [...] 6924 48 61_XML Extender joining multiple "tables" or multi-value results15_J. Scott Walton21_swalton@ATTGLOBAL.NET30_Wed, 2 Nov 2005 11:43:26 -0600355_- I have the XML in a CLOB (It's the ACORD XML) on UDB and am using the XML Extender to access it. I can successfully retrieve an element by using the extractDate, extractVarChar and other XML Extender UDF's. I can also use the multiple entry UDF's to extract a table of all the elements using the form: select returnedVarchar from TABLE(col, path). [...] 6973 29 20_GTT and physical I/O15_Pradeep Gunjala31_pradeep_gunjala@TAX.STATE.NY.US30_Wed, 2 Nov 2005 12:29:04 -0600331_- Hello All,

I am trying to understand if I read a Global Temp. Table in a COBOL program, would that cause DB2 to perform a physical I/O, assuming requested page is not in buffer.

As per IBM DB2 Admin manual, GTT's would reduce logging and also locking for some extent but no mention of I/O or bufferpool usage. [...] 7003 75 77_Question about how to product an audit report for an "alters" on DB2 for z/OZ12_John Lendman27_lendman@PALMBEACH.K12.FL.US30_Wed, 2 Nov 2005 13:57:26 -0500793_windows-1250 Does any one know how to produce an "alter" report for DB2 on z/OS?

Tia



John C. Lendman IBM Certified Solutions Expert IBM Certified Database Administrator DB2 Universal Database V8.1 for zOS Office 561-357-7523 Cell 772-485-1592 Email lendman@palmbeach.k12.fl.us







--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 7079 149 81_Re: Question about how to product an audit report for an "alters" on DB2 for z/OZ14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 2 Nov 2005 19:08:36 -0000580_us-ascii If you mean a report of "who altered what and when" then you will need a log analysis program from someone (like CA!)



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of John Lendman Sent: 02 November 2005 18:57 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Question about how to product an audit report for an "alters" on DB2 for z/OZ [...] 7229 48 3_SQL5_James20_james.y.park@SSA.GOV30_Wed, 2 Nov 2005 13:41:00 -0600482_- Hi, listers. --I ran this SQL,

SELECT RAVL_DBNAME, RAVL_TYPE, RAVL_DSID, SUM(RAVL_PQTY)/100 AS SPACE_ALLOCATED, SUM(RAVL_PQTY_USED)/100 AS SPACE_USED, CAST(CAST(SUM(RAVL_PQTY_USED)/100 AS FLOAT(53)) * 100 / CAST(SUM(RAVL_PQTY)/100 AS FLOAT(53)) +0.005 AS NUMERIC(5,2)) AS PERCENT FROM PTI.RAVL_STATS_0206 WHERE RAVL_TYPE IN ('TS', 'IX') AND RAVL_DBNAME = 'ABCD'

GROUP BY RAVL_DBNAME, RAVL_TYPE, RAVL_DSID



-- This is the part of the report I got. [...] 7278 42 24_Re: GTT and physical I/O19_Humphris,Richard P.24_Richard.Humphris@CNA.COM30_Wed, 2 Nov 2005 13:55:09 -0600444_us-ascii Hi Pradeep,

It's treated like any other table except that they are treated like DB2 workfiles (which are normally used for sorting) and which reside in the same bufferpool as the other workfiles.

Once DB2 decides to write out the DGTT pages AND once DB2 has actually overwritten the DGTT pages in the bufferpool (two very separate events) then DB2 will have to read them back in when you do a select on those pages. [...] 7321 451 81_Re: Question about how to product an audit report for an "alters" on DB2 for z/OZ13_McDonald, Ken20_Ken_McDonald@BMC.COM30_Wed, 2 Nov 2005 14:00:47 -0600509_us-ascii (Or like BMC!)





I did a presentation on DSN1LOGP at the North American IDUG this year which included examples of how to find things like DROPs and GRANTs in DSN1LOGP output when you don't have a log analysis program. It can be pretty cumbersome and you will have to have some knowledge of log record formats. It's good information for one off research, but building an audit report around it might be difficult. But, I'd be glad to send you the presentation if you'd like. [...] 7773 83 7_Re: SQL14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Wed, 2 Nov 2005 14:00:55 -0600771_US-ASCII Remove the RAVL_DSID from the select and the group by.







James Sent by: DB2 Data Base Discussion List 11/02/2005 01:41 PM Please respond to DB2 Database Discussion list at IDUG



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject SQL











Hi, listers. --I ran this SQL,

SELECT RAVL_DBNAME, RAVL_TYPE, RAVL_DSID, SUM(RAVL_PQTY)/100 AS SPACE_ALLOCATED, SUM(RAVL_PQTY_USED)/100 AS SPACE_USED, CAST(CAST(SUM(RAVL_PQTY_USED)/100 AS FLOAT(53)) * 100 / CAST(SUM(RAVL_PQTY)/100 AS FLOAT(53)) +0.005 AS NUMERIC(5,2)) AS PERCENT FROM PTI.RAVL_STATS_0206 WHERE RAVL_TYPE IN ('TS', 'IX') AND RAVL_DBNAME = 'ABCD' [...] 7857 253 7_Re: SQL11_David Nance16_DWNance@FHSC.COM30_Wed, 2 Nov 2005 13:07:13 -0700633_us-ascii Just taking a quick glance, looks like you just have to remove the DSID from the select and group by clauses.





Dave Nance First Health Services, Corp. (804)527-6841



>>> james.y.park@SSA.GOV 11/2/05 2:41:00 PM >>> Hi, listers. --I ran this SQL,

SELECT RAVL_DBNAME, RAVL_TYPE, RAVL_DSID, SUM(RAVL_PQTY)/100 AS SPACE_ALLOCATED, SUM(RAVL_PQTY_USED)/100 AS SPACE_USED, CAST(CAST(SUM(RAVL_PQTY_USED)/100 AS FLOAT(53)) * 100 / CAST(SUM(RAVL_PQTY)/100 AS FLOAT(53)) +0.005 AS NUMERIC(5,2)) AS PERCENT FROM PTI.RAVL_STATS_0206 WHERE RAVL_TYPE IN ('TS', 'IX') AND RAVL_DBNAME = 'ABCD' [...] 8111 55 24_Re: GTT and physical I/O19_Humphris,Richard P.24_Richard.Humphris@CNA.COM30_Wed, 2 Nov 2005 13:55:09 -0600444_us-ascii Hi Pradeep,

It's treated like any other table except that they are treated like DB2 workfiles (which are normally used for sorting) and which reside in the same bufferpool as the other workfiles.

Once DB2 decides to write out the DGTT pages AND once DB2 has actually overwritten the DGTT pages in the bufferpool (two very separate events) then DB2 will have to read them back in when you do a select on those pages. [...] 8167 62 61_XML Extender joining multiple "tables" or multi-value results15_J. Scott Walton21_swalton@ATTGLOBAL.NET30_Wed, 2 Nov 2005 11:43:26 -0600355_- I have the XML in a CLOB (It's the ACORD XML) on UDB and am using the XML Extender to access it. I can successfully retrieve an element by using the extractDate, extractVarChar and other XML Extender UDF's. I can also use the multiple entry UDF's to extract a table of all the elements using the form: select returnedVarchar from TABLE(col, path). [...] 8230 90 7_Re: SQL14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Wed, 2 Nov 2005 14:00:55 -0600771_us-ascii Remove the RAVL_DSID from the select and the group by.







James Sent by: DB2 Data Base Discussion List 11/02/2005 01:41 PM Please respond to DB2 Database Discussion list at IDUG



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject SQL











Hi, listers. --I ran this SQL,

SELECT RAVL_DBNAME, RAVL_TYPE, RAVL_DSID, SUM(RAVL_PQTY)/100 AS SPACE_ALLOCATED, SUM(RAVL_PQTY_USED)/100 AS SPACE_USED, CAST(CAST(SUM(RAVL_PQTY_USED)/100 AS FLOAT(53)) * 100 / CAST(SUM(RAVL_PQTY)/100 AS FLOAT(53)) +0.005 AS NUMERIC(5,2)) AS PERCENT FROM PTI.RAVL_STATS_0206 WHERE RAVL_TYPE IN ('TS', 'IX') AND RAVL_DBNAME = 'ABCD' [...] 8321 42 20_GTT and physical I/O15_Pradeep Gunjala31_pradeep_gunjala@TAX.STATE.NY.US30_Wed, 2 Nov 2005 12:29:04 -0600331_- Hello All,

I am trying to understand if I read a Global Temp. Table in a COBOL program, would that cause DB2 to perform a physical I/O, assuming requested page is not in buffer.

As per IBM DB2 Admin manual, GTT's would reduce logging and also locking for some extent but no mention of I/O or bufferpool usage. [...] 8364 60 3_SQL5_James20_james.y.park@SSA.GOV30_Wed, 2 Nov 2005 13:41:00 -0600482_- Hi, listers. --I ran this SQL,

SELECT RAVL_DBNAME, RAVL_TYPE, RAVL_DSID, SUM(RAVL_PQTY)/100 AS SPACE_ALLOCATED, SUM(RAVL_PQTY_USED)/100 AS SPACE_USED, CAST(CAST(SUM(RAVL_PQTY_USED)/100 AS FLOAT(53)) * 100 / CAST(SUM(RAVL_PQTY)/100 AS FLOAT(53)) +0.005 AS NUMERIC(5,2)) AS PERCENT FROM PTI.RAVL_STATS_0206 WHERE RAVL_TYPE IN ('TS', 'IX') AND RAVL_DBNAME = 'ABCD'

GROUP BY RAVL_DBNAME, RAVL_TYPE, RAVL_DSID



-- This is the part of the report I got. [...] 8425 103 77_Question about how to product an audit report for an "alters" on DB2 for z/OZ12_John Lendman27_lendman@PALMBEACH.K12.FL.US30_Wed, 2 Nov 2005 13:57:26 -0500799_windows-1250 Does any one know how to produce an "alter" report for DB2 on z/OS?

Tia



John C. Lendman IBM Certified Solutions Expert IBM Certified Database Administrator DB2 Universal Database V8.1 for zOS Office 561-357-7523 Cell 772-485-1592 Email lendman@palmbeach.k12.fl.us







--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm [...] 8529 142 26_Re: Active Logs Filling up11_Chris White18_crick616@YAHOO.COM30_Wed, 2 Nov 2005 11:26:48 -0600576_- All the suggestions so far are good but some are time consuming and others have loopholes that might not catch the culprit. Here's a technique using Omegamon that works well for me...

Using the "All Threads" screen in Omegamon (Option "T."), you can sort the output in descending order on the "Update" column by placing the cursor on that column (anywhere in that column) and then press the "PF10" key. This produces a sorted list that will show those who have done the most updates at the top, *BUT* there is one caveat... all activity in DSNDB07 is counted as [...] 8672 177 81_Re: Question about how to product an audit report for an "alters" on DB2 for z/OZ14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 2 Nov 2005 19:08:36 -0000580_us-ascii If you mean a report of "who altered what and when" then you will need a log analysis program from someone (like CA!)



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of John Lendman Sent: 02 November 2005 18:57 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Question about how to product an audit report for an "alters" on DB2 for z/OZ [...] 8850 55 41_CA Database Analyzer (PDA) - RAVL summary12_Scott Fauque19_scott.fauque@CA.COM30_Wed, 2 Nov 2005 17:41:33 -0600351_- Hello James,

Nice job on putting that SQL together.

Unless I missed something you just need to remove the RAVL_DSID. This should be the partition number or the relative dataset number in the case of a multi-dataset object.

I added the SQTY in this version of the SQL. You may have left that out on purpose for some reason. [...] 8906 200 35_Re: union all vs. left outer join ?14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 3 Nov 2005 13:24:36 -0800370_US-ASCII It''s not that an " 'A.id' can be either in B or C, but never in both B and C " which is the problem, but does an A.id **have** to be in either B or C? Because, using LOJ's, DB2 has to develop a path to handle the situation where an A.id is in neither B nor C. The cost of going E->D->B/C->A to reveal this is, to DB2, higher than its current access path. [...] 9107 209 24_Re: GTT and physical I/O27_Vaidyanathan Krithikaivasan22_k.vaidyanathan@TCS.COM30_Thu, 3 Nov 2005 09:00:43 +0530464_US-ASCII

Hi Pradeep

Yes. If the requested page is not buffer then DB2 will request for physical I/O to get a page from temporary table space (DASD) to bufferpool since GTT is created in a temporary table space with in TEMP database on DASD. Only logging to DB2 active log is avoided during insert, update and delete scenarios on this GTT. This GTT is getting created only on temporary tablespace (TEMP Database) in DASD only and not in buffer. [...] 9317 89 7_Re: SQL14_Larry Kintisch19_lkint@IX.NETCOM.COM30_Wed, 2 Nov 2005 23:29:35 -0800373_us-ascii If I understand your question, try removing the RAVL_DSID from both the SELECT clause and the GROUP BY clause.

Larry Kintisch Index design class http://www.DBIndexDesign.com Wayne NJ 11/28-29

Able Information Services 208 Hilltop Drive PO Box 809 Nyack NY 10960-0809 phone: (845)-353-0885 & 3809 "DB2, QMF, Data Modeling and DB2 & Index Tuning" [...] 9407 118 45_Re: CA Database Analyzer (PDA) - RAVL summary14_Park, James Y.20_James.Y.Park@SSA.GOV30_Thu, 3 Nov 2005 07:17:17 -0500345_-

Thanks for everyone who has responded to my questions. Thanks Scott for the SQL. I regard your SQL can produce more accurate report. As suggested I removed RAVL_DSID from SELECT and Group by clause and ran the SQL. I am able to pull all the data from every database except two which are quite huge in size. After running this SQL [...] 9526 177 11_Re: Goodbye16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Thu, 3 Nov 2005 08:35:12 -0500435_iso-8859-1 All the best Tina. Your contribution here is one that we will very sorely miss





Robert Galambos

Compuware Senior Technical Specialist IBM Certified Solutions Expert - DB2 UDB for OS/390 V7 Database Administration robert.galambos@compuware.com



Tel: +1 905 886 7000 Toll Free: +1 800 263 7189 Fax: +1 905 886 7023 Quebec: +1 877-281-1888 [...] 9704 22 44_DB2 Connect EE V7 FP10A and Db2 for ZOS v8.112_Parker, Gary27_gary.parker@ISD.ALABAMA.GOV30_Thu, 3 Nov 2005 08:08:46 -0600564_- Hello, We have just converted my test DB2 for ZOS from version 7 to version 8 compat mode. We have C/S application that utilizes DB2 connect and DB2. All database access is done thru stored procedures running thru user defined wlm environments. We are having a problem when multiple users try to access the system at the same time. It appears that only one user can process it request, while the other hang. At on time, the other users got a message that there was a virtual storage or database resource was unavailable. Another time the hung users did not [...] 9727 18 22_Searching for percents13_Marcus Davage29_marcus.davage@LLOYDSTSB.CO.UK30_Thu, 3 Nov 2005 08:23:44 -0600747_- Dear List,

My client wants to search for the value "12%" in a string. How would you express this in SQL? Merely putting "WHERE linedata LIKE '%12%%'" brings back all occurrances of "12", and the duplicated %% is redundant.

Interesting one this...

Marcus

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 9746 179 35_Re: union all vs. left outer join ?15_Henrik Sorensen27_Henrik_Sorensen@RCOMEXT.COM30_Thu, 3 Nov 2005 15:31:30 +0100303_US-ASCII James, >Because, using LOJ's, DB2 has to develop a path to handle the situation where an A.id is This is a good observation, but why wouldn't the same argument apply for the union all case ?

When looking at the explain of the 'union all' there is certaintly no materialsation of A. [...] 9926 12 38_Re: Soc4 and bind of Package on DB2 v813_Marcus Davage29_marcus.davage@LLOYDSTSB.CO.UK30_Thu, 3 Nov 2005 08:26:48 -0600543_- You haven't got hidden plan tables switched on have you?

Marcus

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 9939 16 26_Re: Searching for percents13_Marcus Davage29_marcus.davage@LLOYDSTSB.CO.UK30_Thu, 3 Nov 2005 08:32:35 -0600610_- Forget it...

AND HEX(LINETEXT) LIKE '%F1F26C%'

works quite nicely, but doesn't match the column on the index.

Marcus

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 9956 64 26_Re: Searching for percents14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 3 Nov 2005 14:43:52 -0000354_us-ascii WHERE colname LIKE '12+%' ESCAPE '+' should work

The ESCAPE clause says that "any wildcard after this escape character is NOT a wildcard at all but a real character"

HOWEVER, the '%' then become position dependent

Eg the example above will match 'AB%' but not 'A%' or 'ABC%' - the percent MUST be in the third position [...] 10021 60 26_Re: Searching for percents15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM30_Thu, 3 Nov 2005 15:51:04 +0100661_iso-8859-1 Marcus,

try WHERE linedata LIKE '12+%' ESCAPE '+'

you can substitute the + character with any other char you not use in the expression

HTH





Mauro Moschelli SanPaoloIMI S.p.A.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On > Behalf Of Marcus Davage > Sent: Thursday, November 03, 2005 3:24 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] Searching for percents > > > Dear List, > > My client wants to search for the value "12%" in a string. > How would you > express this in SQL? Merely putting "WHERE linedata LIKE > '%12%%'" brings > back [...] 10082 14 26_Re: Searching for percents13_Marcus Davage29_marcus.davage@LLOYDSTSB.CO.UK30_Thu, 3 Nov 2005 08:51:05 -0600577_- Thanks to all who replied... WHERE LINETEXT LIKE '%12+%%' ESCAPE '+' is much more legible.

Marcus

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 10097 29 31_Explain Results Without Binding15_Gaston, Raymond17_GastonRay@ORU.COM30_Thu, 3 Nov 2005 10:18:42 -0500429_iso-8859-1 > Dear Lister... Submitting this for a colleague zos390 DB2V7. Thanks in advance for your replies. > > Hi Ray, > > I'm looking for a way to do an explain on a plan or a package (without doing a bind). > > "After reorgs before we do the actual rebinds I would like to see an explain for plans and packages that may benefit from > a rebind. How can this be done (without doing the actual rebind)." > > Thanks > > > [...] 10127 87 35_Re: Explain Results Without Binding11_Cindy Foote29_Cindy.Foote@FIRSTDATACORP.COM30_Thu, 3 Nov 2005 08:28:48 -0700762_US-ASCII We use the CA tool Plan Analyzer.

Cindy

Cindy L Foote 303-889-6350 (office) 303-488-8992 (fax) Cindy.Foote@firstdatacorp.com





"Gaston, Raymond" To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List Explain Results Without Binding



11/03/2005 08:18 AM



Please respond to DB2 Database Discussion list at IDUG











> Dear Lister... Submitting this for a colleague zos390 DB2V7. Thanks in advance for your replies. > > Hi Ray, > > I'm looking for a way to do an explain on a plan or a package (without doing a bind). > > "After reorgs before we do the [...] 10215 61 35_Re: Explain Results Without Binding16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 3 Nov 2005 09:35:49 -0600592_us-ascii In order to get the "correct" access path reflected in the PLAN_TABLE, I would do a BIND into a different collection. The reason is, if you do an EXPLAIN using EXPLAIN SET QUERYNO, you will have to replace host variables with parameter markers, and the access path can be different since the static explain will see the host variable definitions which is not the case executing the dynamic explain. I believe most of these differences will be eliminated in V8NFM since the Optimizer then will provide a matching index scan where the host variable e.g. is VARCHAR but the column [...] 10277 96 28_DB2 z/OS V7 Revoke Authority18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Thu, 3 Nov 2005 10:35:27 -0500554_us-ascii Not having any 3rd party vendor tool to help us; does anyone out there have any home-grown solution to checking a particular USERID and its various authorizations, grants etc prior to Revoking the userid?? An example would be if one of our DBA's left (which does tend to happen) and his/her userid is DBA1234. I know using various QMF queries I can check the various catalogs to see what authority this id has. But it can be time consuming and things can get missed. If anyone has a solution they would like to share I would appreciate it. [...] 10374 155 35_Re: Explain Results Without Binding14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 3 Nov 2005 15:37:44 -0000314_us-ascii What a good idea!

On the other hand, I wrote some REXX a long while ago that would take the DBRMs in a plan and bind a new plan, with those DBRMs but to a dummy plan name (usually my TSO id) with EXPLAIN(YES). You now have a new "version" of the plan_table output to compare with the old one. [...] 10530 172 51_Question about DB2 Backup product QiNetix or Galaxy12_John Lendman27_lendman@PALMBEACH.K12.FL.US30_Thu, 3 Nov 2005 10:45:31 -0500616_iso-8859-1 Does anyone use either of these products from ComVault for the DB2 UDB/AIX backups? If so any pro's or Con's?

QINETIX OR GALAXY

TIA



John C. Lendman IBM Certified Solutions Expert IBM Certified Database Administrator DB2 Universal Database V8.1 for zOS Office 561-357-7523 Cell 772-485-1592 Email lendman@palmbeach.k12.fl.us

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Whittaker, Stephen Sent: Thursday, November 03, 2005 10:35 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 z/OS V7 Revoke Authority [...] 10703 173 35_Re: Explain Results Without Binding14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 3 Nov 2005 15:59:03 -0000317_us-ascii WHAT a good idea!!!!

On the other hand, I wrote some REXX a long while ago that would take the DBRMs in a plan and bind a new plan, with those DBRMs but to a dummy plan name (usually my TSO id) with EXPLAIN(YES). You now have a new "version" of the plan_table output to compare with the old one. [...] 10877 84 41_CA Database Analyzer (PDA) - RAVL summary12_Scott Fauque19_scott.fauque@CA.COM30_Thu, 3 Nov 2005 09:57:31 -0600425_- James,

Another point that I forgot to remind you of was that the PDA tables are historical so there would be multiple rows out there for the same objects having different timestamps. Various places in the product deal with this by having a subSELECT that does a MAX timestamp to make sure the report (Latest option) or JCL building Action Condition/Trigger is only on the most current row for any given object. [...] 10962 101 26_Re: Searching for percents15_Moschelli Mauro31_mauro.moschelli@SANPAOLOIMI.COM30_Thu, 3 Nov 2005 15:51:04 +0100661_iso-8859-1 Marcus,

try WHERE linedata LIKE '12+%' ESCAPE '+'

you can substitute the + character with any other char you not use in the expression

HTH





Mauro Moschelli SanPaoloIMI S.p.A.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On > Behalf Of Marcus Davage > Sent: Thursday, November 03, 2005 3:24 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: [DB2-L] Searching for percents > > > Dear List, > > My client wants to search for the value "12%" in a string. > How would you > express this in SQL? Merely putting "WHERE linedata LIKE > '%12%%'" brings > back [...] 11064 144 35_Re: Explain Results Without Binding11_David Nance16_DWNance@FHSC.COM30_Thu, 3 Nov 2005 08:26:58 -0700324_us-ascii Ray, There are a couple of tools out there that will show you what your explain would look like if it were to be rebound. You could create a new collid/plan and bind everything under that with explain yes. That way you can review the explain info and your packages in their current collection are not touched. [...] 11209 124 11_Re: Goodbye15_Philip Sevetson22_db2.dba.guru@GMAIL.COM30_Thu, 3 Nov 2005 11:21:04 -0500552_ISO-8859-1 Tina, We'll miss you. I hope you decide to come back and that something good opens up for you. --Phil S.

On 11/1/05, Hilton, Tina wrote: > > I'm sending my goodbyes to all my listserves. I'm being laid off and my > last day is Friday. They extended me twice, but this time it's really > happening. I've really appreciated all the help I've received from this > list. I hope I've provided help to others in return. > > I don't plan on staying in the IT field, but am keeping my options open. > It's been [...] 11334 21 52_Question from an Architect : DB2 Z/OS to Unix Oracle15_Daniel Cremieux17_c300501@YAHOO.COM30_Thu, 3 Nov 2005 10:19:22 -0600415_- Dear colleagues ,

An architect in our shop asked me the following question :

Is it possible, from a CICS transaction to call a Store Procedure on Oracle + doing some selects , updates on Oracle and come back ?

I don't know these things so much, instinctively i think that it is possible to do sqls on Oracle tables from DB2 Z/OS, but launch an Oracle SP , it is almost impossible to me. [...] 11356 97 35_Re: Explain Results Without Binding11_Cindy Foote29_Cindy.Foote@FIRSTDATACORP.COM30_Thu, 3 Nov 2005 08:28:48 -0700762_us-ascii We use the CA tool Plan Analyzer.

Cindy

Cindy L Foote 303-889-6350 (office) 303-488-8992 (fax) Cindy.Foote@firstdatacorp.com





"Gaston, Raymond" To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List Explain Results Without Binding



11/03/2005 08:18 AM



Please respond to DB2 Database Discussion list at IDUG











> Dear Lister... Submitting this for a colleague zos390 DB2V7. Thanks in advance for your replies. > > Hi Ray, > > I'm looking for a way to do an explain on a plan or a package (without doing a bind). > > "After reorgs before we do the [...] 11454 129 11_Re: Goodbye15_Philip Sevetson22_db2.dba.guru@GMAIL.COM30_Thu, 3 Nov 2005 11:21:04 -0500552_ISO-8859-1 Tina, We'll miss you. I hope you decide to come back and that something good opens up for you. --Phil S.

On 11/1/05, Hilton, Tina wrote: > > I'm sending my goodbyes to all my listserves. I'm being laid off and my > last day is Friday. They extended me twice, but this time it's really > happening. I've really appreciated all the help I've received from this > list. I hope I've provided help to others in return. > > I don't plan on staying in the IT field, but am keeping my options open. > It's been [...] 11584 149 35_Re: Explain Results Without Binding11_David Nance16_DWNance@FHSC.COM30_Thu, 3 Nov 2005 08:26:58 -0700324_us-ascii Ray, There are a couple of tools out there that will show you what your explain would look like if it were to be rebound. You could create a new collid/plan and bind everything under that with explain yes. That way you can review the explain info and your packages in their current collection are not touched. [...] 11734 180 35_Re: Explain Results Without Binding15_Gaston, Raymond17_GastonRay@ORU.COM30_Thu, 3 Nov 2005 11:55:26 -0500680_iso-8859-1 Listers: Thanks for your replies. They've been very helpful.

"thanks Ray. I got my answer".

Avi Bistricer, DBA 212.460.6695



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Grainger, Phil Sent: Thursday, November 03, 2005 10:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Explain Results Without Binding



What a good idea!

On the other hand, I wrote some REXX a long while ago that would take the DBRMs in a plan and bind a new plan, with those DBRMs but to a dummy plan name (usually my TSO id) with EXPLAIN(YES). You now have a new "version" of the [...] 11915 52 31_Explain Results Without Binding15_Gaston, Raymond17_GastonRay@ORU.COM30_Thu, 3 Nov 2005 10:18:42 -0500429_iso-8859-1 > Dear Lister... Submitting this for a colleague zos390 DB2V7. Thanks in advance for your replies. > > Hi Ray, > > I'm looking for a way to do an explain on a plan or a package (without doing a bind). > > "After reorgs before we do the actual rebinds I would like to see an explain for plans and packages that may benefit from > a rebind. How can this be done (without doing the actual rebind)." > > Thanks > > > [...] 11968 416 45_Re: CA Database Analyzer (PDA) - RAVL summary16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 3 Nov 2005 11:29:57 -0600347_us-ascii James,

The SQL below will avoid the overflow condition (see the additions marked with RED. The problem then is, I have not been able to use the AS SPACE_ALLOCATED and AS SPACE_USED, but if you create a view, I believe you can get the headlines as needed. (By the way - Scott Fauque pointed me in the direction of adding 0.0). [...] 12385 34 41_CA Database Analyzer (PDA) - RAVL summary12_Scott Fauque19_scott.fauque@CA.COM30_Thu, 3 Nov 2005 12:25:18 -0600425_- James,

Another point that I forgot to remind you of was that the PDA tables are historical so there would be multiple rows out there for the same objects having different timestamps. Various places in the product deal with this by having a subSELECT that does a MAX timestamp to make sure the report (Latest option) or JCL building Action Condition/Trigger is only on the most current row for any given object. [...] 12420 53 56_Re: Question from an Architect : DB2 Z/OS to Unix Oracle9_Dee Reins24_dee.reins@CENTURYTEL.COM30_Thu, 3 Nov 2005 12:35:17 -0600527_iso-8859-1 I would think that if the DB2 system could signon to an ORACLE database, given the proper authority, the stored procedure could be executed. I think that security would be more of an issue. Where are the passwords stored to signon to oracle, and how are they changed an a regular basis to meet the SOX requirements. I would answer this question before deciding to use such a procedure. If the Architect is going to design it, make them maintain it for 60 days without failure before someone else takes it over. [...] 12474 32 56_Re: Question from an Architect : DB2 Z/OS to Unix Oracle6_ducduc17_ndt.db2@GMAIL.COM30_Thu, 3 Nov 2005 20:06:43 +0100605_ISO-8859-1 Daniel, i don't think that update/insert is possible , i don't see how the whole system can manage a rollback for example .

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 12507 18 49_Is there a tool to recover a dropped tablespace ?12_Billy Larsen19_billarsen@YAHOO.COM30_Thu, 3 Nov 2005 13:06:13 -0600356_- Dear all, Is there a tool to recover the dropped table space without loosing any data ? We hava an in house tool to dsn1copy the dropped tablespace from its last full image copy, but it is really hard to read forward the log to apply the updates. We have CA Log Analyzer but it seems that it works only on existing objects (based on object IDs) ... [...] 12526 51 56_Re: Question from an Architect : DB2 Z/OS to Unix Oracle16_Burnette, George27_George_Burnette@KYFBINS.COM30_Thu, 3 Nov 2005 14:23:43 -0500348_us-ascii Sounds pretty tricky to me. I'm not sure how cics works with oracle anyway. One possible alternative to look at is utilizing websphere/db2 information integrator with an Oracle connector. I know it's not cheap, but it may come in handy if this starts to happen a lot. Although... I don't see how this could be done with an Oracle SP. [...] 12578 213 53_Re: Is there a tool to recover a dropped tablespace ?16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 3 Nov 2005 13:29:25 -0600357_us-ascii Hello Billy,

You can use Log Analyzer to generate all the REDO records from the Imagecopy you recover to the RBA prior to the DROP. You can use PLA to generate the CREATE TABLESPACE, CREATE TABLE statements etc. too. If for some reason you can NOT reuse the OBID, you can use the DDL FILE MAPPING feature to map the new and old OBID's. [...] 12792 56 25_Display the Current Sqlid12_Smith, Allan23_Allan_Smith@KYFBINS.COM30_Thu, 3 Nov 2005 15:28:56 -0500600_us-ascii Can someone tell me how to display the CURRENT SQLID within Spufi? Or issue a DB2 command?

Thanks





--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 12849 112 29_Re: Display the Current Sqlid13_Rhea, Matthew24_Matthew_Rhea@CONSECO.COM30_Thu, 3 Nov 2005 15:33:09 -0500516_iso-8859-1 To get the current sqlid, run the SQL

SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1

I don't believe you can issue DB2 commands in SPUFI. You have to use option 7 of the DB2I Primary option menu, which is DB2 COMMANDS. You can issue DB2 commands in batch, too.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Smith, Allan Sent: Thursday, November 03, 2005 3:29 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Display the Current Sqlid [...] 12962 21 35_Active log Buffers and real storage12_Bernie Stein24_Bernard.J.Stein@USPS.GOV30_Thu, 3 Nov 2005 14:25:27 -0600530_- We are looking at increasing our OUTBUFF parm which is currently 400K since its was discovered that the system was waiting for unavailable buffers this running on Z/OS 1.4 running DB2V7 utilizing Datasharing. My question is I was wondering how this relates to real storage since it also appears that paging is taking place with the Log output buffers which hints at not enough real storage to back our current settings so could increasing the OUTBUFF cause more harm without increasing the real storage used to back it up. [...] 12984 105 39_Re: Active log Buffers and real storage14_Avram Friedman21_avramf@IBMSYSPROG.COM30_Thu, 3 Nov 2005 13:35:15 -0800394_iso-8859-1 Not sure how one tells that there are page faults in the log buffers. The log buffers are very referenced frequently in DB2. You might want to do a simple calculation to determine how frequently they wrap. Active Log Size / OUTBUFF gives you the number of wraps per active log. Divide this number by the seconds it takes to fill up an active log and you have the time per wrap. [...] 13090 12 53_Re: Is there a tool to recover a dropped tablespace ?12_Billy Larsen19_billarsen@YAHOO.COM30_Thu, 3 Nov 2005 15:34:14 -0600636_- Hi Steen , Thank you very much for your quick reply, i will read the PLA reference to find out what you mentioned. If i have any problem , i will come back to you.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 13103 134 39_Re: Active log Buffers and real storage13_Martin Packer24_martin_packer@UK.IBM.COM30_Thu, 3 Nov 2005 21:46:54 +0000356_US-ASCII I'd assume 1-for-1 and in any case the buffer set isn't that large. Largest cust I ever saw had 96GB - earlier this year. :-)

And this is in MSTR so virtual storage doesn't play much.

Martin

Martin Packer, MBCS CITP Martin Packer/UK/IBM 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile 07802-245584) [...] 13238 89 29_Re: Display the Current Sqlid13_Mackey, Glenn20_GMackey@GUIDEONE.COM30_Thu, 3 Nov 2005 16:09:31 -0600858_us-ascii select current sqlid from sysibm.sysdummy1

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Smith, Allan Sent: Thursday, November 03, 2005 2:29 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Display the Current Sqlid





Can someone tell me how to display the CURRENT SQLID within Spufi? Or issue a DB2 command?

Thanks



------------------------------------------------------------------------ --------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on [...] 13328 40 53_Re: Is there a tool to recover a dropped tablespace ?0_22_DB2information@AOL.COM28_Thu, 3 Nov 2005 17:18:39 EST565_US-ASCII Yes for a dropped tablespace tool visit: www.recoveryknowledge.com



Ed.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 13369 122 15_Latch Questions12_Paul Burczyk19_pburczyk@HUMANA.COM30_Thu, 3 Nov 2005 17:20:54 -0500622_US-ASCII Hello,

I have questions about dispatching priorities as they relate to the DB2 started tasks, in a non data sharing environment. We have our IRLM task higher than all others, and the DBM1, DIST, and MSTR are all the same. It's my understanding that's recommended, so that locking will always first crack at the CPU resources. But what about latches? We saw today a period of about 30 seconds where no transactions completed, at 3PM. That's EXTREMELY unlikely in our extremely busy production subsystem. In another monitor we were watching at the time, nearly 300 transactions were showing as stalled, [...] 13492 61 53_Re: Is there a tool to recover a dropped tablespace ?15_Murley, Michael22_Michael_Murley@BMC.COM30_Thu, 3 Nov 2005 16:51:48 -0600456_us-ascii The BMC Recovery Management for DB2 Solution includes Automated Drop Recovery support.

The BMC RECOVER PLUS for DB2 utility also supports recovery of a dropped table space without data loss. Using the utility alone, without the automated support, there are a few more manual steps.



________________________________

Michael Murley BMC Software

phone: 512-340-6153 fax : 512-340-6646 email : mmurley@bmc.com [...] 13554 23 41_Ralph R.I. van Zagten/BAC/BLD is afwezig.16_Ralph van Zagten32_ri.van.zagten@BELASTINGDIENST.NL30_Fri, 4 Nov 2005 00:03:33 +0100655_us-ascii Ik ben afwezig vanaf 04-11-2005 en ik ben niet eerder terug dan 14-11-2005.

Ik ben van 4-11 t/m 13-11 niet aanwezig.

Voor dringende zaken kan er gemaild worden naar de Postbus van Logisch DB2.



------------------------------------------------------------------------------ De Belastingdienst gebruikt e-mail niet voor de uitwisseling van vertrouwelijke informatie met de burger of voor de bekendmaking van beslissingen. De Belastingdienst hanteert conventies voor het gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl. ============================================================================= [...] 13578 55 39_Re: Active log Buffers and real storage35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Thu, 3 Nov 2005 18:07:49 -0500492_iso-8859-1 Bernie, While increasing the outbuff may be necessary based on the activity of your system, you may want to take a look at the dasd system performance. It's quite common for dasd write delays to cause this problem.

Regards, Joel

----- Original Message ----- From: "Bernie Stein" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, November 03, 2005 3:25 PM Subject: [DB2-L] Active log Buffers and real storage [...] 13634 53 39_Re: Active log Buffers and real storage19_Humphris,Richard P.24_Richard.Humphris@CNA.COM30_Thu, 3 Nov 2005 17:10:34 -0600482_us-ascii Hi Bernie,

This also sounds like a serious problem to me. And if you're running out of log buffers DB2 should have already been running full tilt trying to write them out. But to see paging in your log buffers at the same time (which will serious slow down the write and commit processes) sounds like it is a problem with the over committing of real storage. And, as you say, increasing the log buffers at this point would probably only exacerbate the problem. [...] 13688 147 56_Re: Question from an Architect : DB2 Z/OS to Unix Oracle15_nguyen duc tuan17_ndt.db2@GMAIL.COM30_Fri, 4 Nov 2005 00:37:25 +0100337_ISO-8859-1 Billy, As George pointed to DB2 II, i had a quick look from DB2 Information Integrator Redbook : -Update only possible if the updates within the UR are from a single source (single commit), i.e you cannot update DB2 and Oracle in the same UOW -No Oracle SP from DB2, but call to a "remote SP" is possible (DB2 family SP) [...] 13836 52 32_Re: DB2 z/OS V7 Revoke Authority14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 4 Nov 2005 10:39:42 -0800302_ISO-8859-1 Once, many versions ago, DB2 had a REVOKE impact analysis tool. Rather poor code I thought as I watched it doing repeated tablespace scans.

You could try: - dump all the SYSxxxAUTH tables and SYSVIEWS - do the revoke - redump all those tables - rollback - compare the two dumps. [...] 13889 31 35_Re: union all vs. left outer join ?14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 4 Nov 2005 10:39:41 -0800556_US-ASCII My guess is that this is one of those OR predicate situations that's no developer has yet got around to writing code to handle.

James Campbell

On 3 Nov 2005 at 15:31, Henrik_Sorensen@rcomext.com wrote:

> James, > >Because, using LOJ's, DB2 has to develop a path to handle the situation > where an A.id is > This is a good observation, but why wouldn't the same argument apply for > the union all case ? > > When looking at the explain of the 'union all' there is certaintly no > materialsation of A. > > So where does it [...] 13921 79 19_Re: Latch Questions14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 4 Nov 2005 10:39:42 -0800443_US-ASCII I have seen this type of situation when WLM was denying service to a bottom-crawler group because the system was 100% busy. When we gave the bottom-crawlers a bit of CPU everything cleared up.

My feeling is that one of the bottom-crawlers had grabed some critical bit of latch code and, before releasing it, was denied service by WLM. So, with a little bit of CPU, the critical bit of code did its thing and was released. [...] 14001 189 39_Re: Active log Buffers and real storage14_Avram Friedman21_avramf@IBMSYSPROG.COM30_Thu, 3 Nov 2005 16:26:47 -0800361_iso-8859-1 Martin, While the log buffers are owned by MSTR I think they are populated through the magic of cross memory services by the various allied address spaces. This may be the fundamental reason why access to the log buffers is controlled by a latch mechanism. If there was a page fault access a log buffer the requester probably would not be MSTR. [...] 14191 38 11_Re: Goodbye12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 3 Nov 2005 19:17:14 -0600598_- Thanks for your contributions over the years. We've enjoyed the virtual conversations. I hope this works out well for you, but for the rest of us, it would be nice if another DB2 job finds you.

Roger Miller

On Tue, 1 Nov 2005 09:05:28 -0500, Hilton, Tina wrote:

>I'm sending my goodbyes to all my listserves. I'm being laid off and my last >day is Friday. They extended me twice, but this time it's really happening. >I've really appreciated all the help I've received from this list. I hope >I've provided help to others in return. > >I don't [...] 14230 129 19_Re: Latch Questions12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 3 Nov 2005 19:45:39 -0600521_- James indicated the probable situation, but just to answer the questions you asked,

1) Are latches managed by the DBM1 address space? No. There are many different latches. What latch class showed this result? Latch use and management are very complex, trying to solve this problem over the past 20 years and make the process faster. There are some WLM changes in most of the z/OS releases and some changes for this symptom in most of the DB2 releases. Still, if you pound on DB2 hard enough, it will break. [...] 14360 34 81_Re: Question about how to product an audit report for an "alters" on DB2 for z/OZ12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 3 Nov 2005 19:51:49 -0600356_- It is also possible that you can use the audit trace, class 3 if the tables are defined to audit. Then you'd be looking at the trace records, depending upon where they are written. There are products that can format the records for you.

Roger Miller

On Wed, 2 Nov 2005 13:57:26 -0500, John Lendman wrote: [...] 14395 77 35_Re: Explain Results Without Binding12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM30_Thu, 3 Nov 2005 22:01:59 -0600312_us-ascii IBM has a tool Path Checker that has a TEST facility. It will give you the PLAN TABLE and DSN STATEMENT TABLE information without a bind. The advantage of not binding is the contention reduction. It will also do the compare between the new access path and cost information and the old information. [...] 14473 231 39_Re: Active log Buffers and real storage13_Martin Packer24_martin_packer@UK.IBM.COM30_Fri, 4 Nov 2005 09:08:43 +0000329_US-ASCII I'm not really disagreeing with anything. :-)

Just pointing out that log buffers as such aren't going to DRIVE paging load or memory consumption much. So being sparing with them isn't nearly as good a deal as being tight on other memory users in the system (such as low-access CICS regions or buffer pools). [...] 14705 155 35_Re: union all vs. left outer join ?15_Henrik Sorensen27_Henrik_Sorensen@RCOMEXT.COM30_Fri, 4 Nov 2005 10:37:21 +0100487_US-ASCII David,

Thanks for your answer.

I am a bit surprised when you say there is no direct correlation from D to A. Clearly the paths E-D-B-A and E-D-C-A are valid correlations ? After all, when using the union all the query does work as expected.

With a changed query like you have suggested, I have two instances of A, that I would have to merge into just one. Maybe it can be done, but I am not convinced this is a better approach, but will give it a try. [...] 14861 25 19_Re: Latch Questions33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Fri, 4 Nov 2005 04:14:49 -0600280_- Hi

When I read this thread, I was somehow reminded to a problem we recently had with long latch times (see the discussion in this list). We were in DB2 V8 NFM and used the new ZPARM SKIPUNCI. In the meantime IBM solved the problem. May be you have the same problem. [...] 14887 17 6_Thanks14_Park, James Y.20_James.Y.Park@SSA.GOV30_Fri, 4 Nov 2005 08:46:54 -0500699_- Thanks to all who responded to my emails and provided solutions and help for my SQL problem. Especially thanks to respectful DBAs, Deepak, Mark, Henrik, David, Kenney and CA team, Scott and Rasmussen.



James Park

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 14905 24 44_Question about the 2006 technical conference5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Fri, 4 Nov 2005 10:04:53 -0600482_us-ascii Does anyone have some insight into the 2006 technical conference (time and place). I attended the 2005 conference in Florida and heard very clearly that the 2006 conference would be held in Dallas in late August. Now I go the the IBM web site (http://www-304.ibm.com/jct03001c/services/learning/ites.wss/us/en?pageType=page&c=a0000713) and see that the technical conference will be held in Anaheim in October. I take it that the time and place has officially changed. [...] 14930 20 18_DB2 Admin training9_Bob Henry21_bhenry@SUNGARDSCT.COM30_Fri, 4 Nov 2005 10:12:24 -0600588_- Hello list,

Having recently lost our DBA resource, I need to obtain some DB2 DBA training. We run a small R7 environment, soon to migrate to R8. We have very limited DBA requirements but occasionally the need arises. I've looked at the training courses from Themis Inc. and have registered for the DBA course. They also have a R7 to R8 migration class available. I'd like to hear anyone's comments on the quality of Themis training. Also, with VERY limited DBA skills myself, would both courses be beneficial? Obviously cost is a factor and they're not cheap so I'm looking [...] 14951 47 48_Re: Question about the 2006 technical conference13_Keemle Joan T25_KeemleJoanT@JOHNDEERE.COM30_Fri, 4 Nov 2005 10:27:31 -0600484_us-ascii They are combining DB2 with IMS and Content Management into one very large conference. This may make it difficult for some to attend, due to coverage back home. It is also unfortunately the same week as IDUG Europe 2006.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Larry Sent: Friday, November 04, 2005 10:05 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Question about the 2006 technical conference [...] 14999 136 48_Re: Question about the 2006 technical conference12_Martin Hubel17_Martin@MHUBEL.COM30_Fri, 4 Nov 2005 11:39:40 -0500 15136 346 48_Re: Question about the 2006 technical conference14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 4 Nov 2005 16:44:38 -0000483_us-ascii on the other hand (speakers possibly excluded) we are also wondering whether there really IS any overlap between attendees at a dedicated DB2 IDUG Europe conference (in Athens, Greece....) and attendees at a mega event with potentially less DB2 focus

Difficult one to call at the moment......



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 15483 124 39_Re: Active log Buffers and real storage19_Humphris,Richard P.24_Richard.Humphris@CNA.COM30_Fri, 4 Nov 2005 10:54:27 -0600617_us-ascii

Hi Avram,

I agree with you, log output buffers shouldn't be paging. So my belief is either virtual storage being way over committed compared to the real storage supporting it OR an incredibly bad i/o problem such that buffers are being paged before they can be written. .

Btw, most if not all DB2 subsystems have the statistics to document this log write paging problem. That's because page faults in log output buffers is counted by DB2 subsystems and is documented to be in the field QJSTBPAG which is part of the QJST section documented in hlq.SDSNMACS(DSNDQJST). This field is [...] 15608 74 22_Re: DB2 Admin training14_Avram Friedman21_avramf@IBMSYSPROG.COM30_Fri, 4 Nov 2005 09:43:05 -0800656_iso-8859-1 DB2 for Z/os? Does the DBA do product installation for DB2 and related components or is it done by a systems programming team?



With some rare exceptions, large platform DB2 implementations usually require dedicated DBAs and dedicated Systems Programmers. There are just too many weird things that can and do crop up unexpectitly. In addition to DB2 knowledge these jobs often demand expert skills in operations, operating system tools, storage management, specialized documentation sources like SVC dumps, Logrec etc ... After hours support is the norm as well as day time support. A very big requirement for a newly trained [...] 15683 39 19_Re: Latch Questions12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 4 Nov 2005 11:48:17 -0600434_- My statement is accurate, but your assumption is not a logical corollary. There is latching in most of the address spaces, certainly in DBM1, MSTR, and IRLM - for different types of latches. Cross memory access makes some of the boundaries very fuzzy. We still don't have a clear idea of which of the many types of latches is involved, which version of DB2, which release of z/OS, whether this is goal mode or compat mode, ... [...] 15723 99 56_DB2 z/OS - dynamic statement different in COBOL vs SPUFI10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Fri, 4 Nov 2005 15:30:52 -0500556_us-ascii We are having major issues with one of our applications that is using dynamic SQL that is embedded in a batch COBOL program, that the statement seems to get into some DB2 never-never land where the cursor does not finish opening. The statement is a relatively simple 2 table join that produces 120,000 rows. I have captured the statement tat is being executed at the time and pasted the statement into a member and run it in SPUFI and it finished in less than 30 seconds. However, when being run in this batch job, it has been sitting on the [...] 15823 425 60_Re: DB2 z/OS - dynamic statement different in COBOL vs SPUFI15_Carol L Broyles30_carol.broyles@MEADWESTVACO.COM30_Fri, 4 Nov 2005 15:36:36 -0500505_US-ASCII Mark,

Are you using parameter markers in the COBOL program vs. literal values in the SPUFI statement? If the optimizer has the values for the predicate, it can make a big difference in the access path if the values are skewed.

Carol











Mark Labby Sent by: DB2 Data Base Discussion List 11/04/2005 03:30 PM Please respond to DB2 Database Discussion list at IDUG [...] 16249 334 60_Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Fri, 4 Nov 2005 16:21:45 -0500550_us-ascii In both cases, they are using literals.

I can get an explain with the SPUFI before executing, but how do I get it from the program with dynamic SQL?

I can see that when it is running in the COBOL, that it seems to stay on one of the tables and spends all it's time spinning around reading the data in the MR53 table. It has 4.49 million get pages against the IMR53C0 clustering index, when there are only 12,000 pages. It calls for 47 million getpages against the data, all driven by the index read, and there are 137,000 [...] 16584 110 64_Re: Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI13_Richard Fazio21_rfazio@TRANSUNION.COM30_Fri, 4 Nov 2005 15:28:10 -0600388_US-ASCII Could you check the access path?

Change the SQL to "EXPLAIN FOR" and let's see the paths generated via both techniques. It may shed some light.

faz

Rich Fazio Information Architecture

TransUnion, LLC Marketing Services, 8th Floor 555 West Adams St. Chicago, IL 60661 Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn [...] 16695 141 60_Re: DB2 z/OS - dynamic statement different in COBOL vs SPUFI9_Mike Bell21_mbell11a1@VERIZON.NET30_Fri, 4 Nov 2005 15:32:35 -0600318_Windows-1252 Things to check - 1. Bind DEGREE (ANY) or set current DEGREE statements 2. Is it using host variables and what are the values?

You might try a trace on ifcid 22 and 63 - which are minimal overhead - to see what the actual access path looks like. This would be required for IBM support anyway. [...] 16837 215 64_Re: Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI10_Bruce, Mae19_Mae.Bruce@OA.MO.GOV30_Fri, 4 Nov 2005 15:37:27 -0600500_us-ascii 3000 requests to process at 30 seconds each? Are you sure it is the same exact SQL statement that has been executing for the last 2 hours? And not variations of it with the owner and loan program information changed?

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Labby Sent: Friday, November 04, 2005 3:22 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI [...] 17053 86 48_Re: Question about the 2006 technical conference13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Fri, 4 Nov 2005 16:56:34 -0500359_- Perhaps they heard more than just my whinging about August being a lousy time for parents to be able attend because of vacations or taking younguns back to college.

Or maybe the European attendees told them no one would interrupt their August holiday to come. Then again someone could have pointed out the average Dallas temperatures in August. [...] 17140 21 23_change tablespace state3_lan18_lxw176@HOTMAIL.COM30_Fri, 4 Nov 2005 15:50:49 -0600525_- Hi,

I have a new AIX 5.1 server with db2 8.1 work group server installed. I recovered a new database using redirected restore from an online backup image created from another same platform server. I didn't realize the tablespace problem until I applied db2 Fixpak 9a on my new server. I was able to connect to the database, However, one of the tablesapce are in still in backup pending state. Does anyone can tell me the trick on how to set the tablespace stateinto normal with having to recreate the database? [...] 17162 244 48_Re: Question about the 2006 technical conference0_17_jtonchick@AOL.COM30_Fri, 4 Nov 2005 17:09:05 -0500387_us-ascii Seeing the Chicago White Sox defeat the Angels again would be a nice Wednesday outing, at least for me and all the other Chicagoians attending the conference.

-----Original Message----- From: Seibert, Dave To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Fri, 4 Nov 2005 16:56:34 -0500 Subject: Re: [DB2-L] Question about the 2006 technical conference [...] 17407 150 48_Re: Question about the 2006 technical conference15_Murley, Michael22_Michael_Murley@BMC.COM30_Fri, 4 Nov 2005 16:20:38 -0600373_us-ascii Perhaps they've been to Dallas in late August before and prefer the weather in southern California in October (I say that as a native and lifelong Texan).

And speaking of baseball, if they would only move it to Houston - on second thought, I don't want to talk about baseball.

________________________________

Michael Murley BMC Software [...] 17558 456 48_Re: Question about the 2006 technical conference35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Fri, 4 Nov 2005 17:24:01 -0500405_iso-8859-1 Phil,

There are many issues other than user attendees, or speakers.

Small vendors can't be in both places. All the IBM speakers from the lab will be at the IBM conf.

IBM tried this approach before. It was a disaster. The client attendees were the most upset because they had a scaled down program, and this applied to everything supposedly addressed at the mega conf. [...] 18015 168 48_Re: Question about the 2006 technical conference13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Fri, 4 Nov 2005 16:24:26 -060092_ISO-8859-1 Gee, Houston in August would be the perfect place for a conference... LOL

18184 264 64_Re: Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Fri, 4 Nov 2005 17:47:29 -0500531_us-ascii Bruce,

I was monitoring it and it was stuck on the single statement. It was set to commit on each one and no commits were taken, and we were watching the SQL test in a monitor.

I am in the process of changing the clustering indices on both tables to align and provide for more columns in the match. That should help, but doesn't explain why the thing ran in seconds in SPUFI and hours in COBOL. I think the clients who are still waiting for their EOM reports are gonna be a tad upset, so I have to come [...] 18449 295 64_Re: Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI12_Phil Gunning22_pgunning@GUNNINGTS.COM30_Fri, 4 Nov 2005 18:02:07 -0500717_us-ascii No. Only after knocking everyone off or just after a db2start....I bought some wireless access waiting for my flight here in Cleveland....Cheers, Phil







www.gunningts.com/db2zone.htm 610.451.5801 Fax 514-509-6886 "We help people succeed with DB2" See us at IDUG Asia-Pacific Forum 23-24 Mar 2006. ITgain: www.it-gain.com GET READY TO RELEASE THE VIPER AT: http://www-306.ibm.com/software/data/db2/udb/viper/ -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Labby Sent: Friday, November 04, 2005 5:47 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI [...] 18745 322 83_Re: Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI,Inadvertent Post:)12_Phil Gunning22_pgunning@GUNNINGTS.COM30_Fri, 4 Nov 2005 18:06:01 -0500344_us-ascii Well, after many years it happened...I unintentionally replied to a db2 post and thought I was answering an email from someone else. I guess that's what I get checking my email quickly while waiting for a flight. Good luck Mark. Sorry (glad) I am not there:)... Cheers, Phil



For those affected by Hurricane Katrina [...] 19068 53 83_Re: Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI,Inadvertent Post:)10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Fri, 4 Nov 2005 18:10:42 -0500465_us-ascii LOL - I did get a chuckle out of the reply Phil and was wondering when you would catch it.

Hmm, didn't they write this piece of crap code while you were here?







"Phil Gunning" To Sent by: "DB2 DB2-L@WWW.IDUGDB2-L.ORG Data Base cc Discussion List" Re: Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI,Inadvertent Post:) 11/04/2005 06:06 PM [...] 19122 54 27_Re: change tablespace state14_Robert E. Smit24_robert.erwin.smit@WXS.NL30_Sat, 5 Nov 2005 08:34:14 +0100367_us-ascii Lan,

You can try a db2 rollforward to the end of logs and complete for that tablespace but then you need to have transaction logs.

If you don't have the logs then you could try to get the data in that tablespace with db2dart but then you don't know if the data is OK because perhaps some data should be rolled back or should be committed. [...] 19177 69 27_Re: change tablespace state10_Ravi Reddy19_dbadb2udb@YAHOO.COM30_Fri, 4 Nov 2005 23:40:33 -0800613_iso-8859-1 Hi Lan,

Simple...take a backup if the object is in backup pending state.

If that is not an option, DB2DART tool can reset the backup pending flag.

db2dart -h will show you the options. I dont remember which option exactly.

thanks, Ravi.

--- lan wrote:

> Hi, > > I have a new AIX 5.1 server with db2 8.1 work group > server installed. I > recovered a new database using redirected restore > from an online backup > image created from another same platform server. I > didn't realize the > tablespace problem until I applied db2 Fixpak [...] 19247 29 19_Re: Latch Questions10_Max Scarpa16_mscarpa@CESVE.IT30_Sat, 5 Nov 2005 11:07:03 +0100616_US-ASCII Hi Roger

I read in many papers that latches are for 'physical' consistency of data (while 'logical' consistency is IRLM's duty) so they must 'live' in different components of DB2 (for example EDM pool management is done by latch. We find latches in log management and in BP management) so latch usage couldn't be controlled by something like IRLM but should be controlled by that component managing the proper DB2 function (logging,BPs,EDM pool management). On the other hand I find in a paper that there was a 'latch manager' before V3 (Favero,2001) for system objects and indexes and it still [...] 19277 80 21_Stopped getting mails21_Sriramulu, Vijayababu28_vijayababu.sriramulu@EDS.COM30_Mon, 7 Nov 2005 13:03:30 +0530347_us-ascii Dear List Users,

Outlook is our mail server and we have a size limit on the inbox. Since I was on vacation for all through last week, I did not check mails thereby I exceeded this limit. My guess is that the mails will be knocked back. Is there any process on the DB2-L server to stop sending mails if they are bounced back ? [...] 19358 44 64_Re: Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI12_Backes, Mike21_Mike.Backes@OA.MO.GOV30_Mon, 7 Nov 2005 09:28:50 -0600461_US-ASCII Mark, this sounds an awful lot like a problem I am having, only different.

At some point for your cobol program you have to do a bind to get the dbrm etc. im assuming that initially your cobol pgm is using parameter markers or something and the access path chosen at run time is different from that when you run it in spufi. As others have noted it would be interesting to see the explain from your bind and from the explain of your spufi. [...] 19403 35 50_DB2 Accounting Problem - Excessive SMF 101 Records9_Dan Lamas22_dlamas@DUKE-ENERGY.COM30_Mon, 7 Nov 2005 09:53:31 -0600561_US-ASCII Hello everyone,

Our shop is experiencing problems with performance and I am unable to find the problem. We are os/390 v2.10 with DB2 v7. Our issue is that everyday "something" is cutting tons of SMF type 101 records. Our response time goes in the toilet. Nothing seems to be running. So our sysprog has a task in place to shut off all accounting traces when his SMF volumes reach a certain capacity. This does not solve the problem. Only he wont run out of DASD for SMF reporting. One other catch is I only have DB2PM as a monitoring tool. [...] 19439 94 34_-911 fetching from a declared GTT.0_30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Mon, 7 Nov 2005 10:21:08 -0600662_US-ASCII Hello, z/OS 1.4, DB2 1.7 here!

We implemented a GRS (ring mode) last weekend and are seeing some interesting performance behaviours.

One thing however, that I don't recall seeing before is the following abend:

DC6PRAIQ - DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN DC6PRAIQ - ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E, TY DC6PRAIQ - OF RESOURCE 00000302, AND RESOURCE NAME DSNDB06 .00000167.X' DC6PRAIQ - 000E6' DC6PRAIQ - DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE DC6PRAIQ - DSNT415I SQLERRP = DSNXOCN SQL PROCEDURE DETECTING ERROR DC6PRAIQ - DSNT416I SQLERRD = -200 13172746 13172878 [...] 19534 259 34_Performance Class Dec. 7th Detroit11_Dave Beulke17_DaveBeulke@CS.COM28_Mon, 7 Nov 2005 11:18:01 EST723_US-ASCII Hi Everyone,

I wanted to let everyone know about the upcoming class in Detroit, Wednesday, December 7th.

How to do a DB2 Performance Review by David Beulke, IBM Gold Consultant

Abstract This class will detail the research, process and impact of doing a DB2 performance review of your DB2 environments, systems, and applications. An overall system, architectural, database and performance tuning review can dramatically reduce costs and improve system and application performance, availability and scalability. By analyzing many processing performance conditions and detailing various recommendations you can quickly improve database application performance, throughput and response time. [...] 19794 92 54_Re: DB2 Accounting Problem - Excessive SMF 101 Records12_Kirk Hampton28_khampto1@CAPGEMINIENERGY.COM30_Mon, 7 Nov 2005 10:30:33 -0600555_us-ascii Hi Dan, I can't exactly tell you how to find it with DB2PM. But this sounds a lot like something we have seen in the past with distributed (DB2 Connect) applications. One of ours used to get into a flurry of very quick connections to the mainframe just to "check our heartbeat', i.e. check to be sure the mainframe DB2 is up. They would happen so fast that it was hard to catch them with an online monitor like DB2PM or even BMC Mainview. But each of those quick connect/disconnects cuts am SMF record. Look for whatever Correlation ID does [...] 19887 99 34_-911 fetching from a declared GTT.0_30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Mon, 7 Nov 2005 10:21:08 -0600661_US-ASCII Hello, z/OS 1.4, DB2 1.7 here!

We implemented a GRS (ring mode) last weekend and are seeing some interesting performance behaviours.

One thing however, that I don't recall seeing before is the following abend:

DC6PRAIQ - DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN DC6PRAIQ - ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E, TY DC6PRAIQ - OF RESOURCE 00000302, AND RESOURCE NAME DSNDB06 00000167.X' DC6PRAIQ - 000E6' DC6PRAIQ - DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE DC6PRAIQ - DSNT415I SQLERRP = DSNXOCN SQL PROCEDURE DETECTING ERROR DC6PRAIQ - DSNT416I SQLERRD = -200 13172746 13172878 [...] 19987 194 60_Re: DB2 z/OS - dynamic statement different in COBOL vs SPUFI10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Mon, 7 Nov 2005 11:38:20 -0500529_us-ascii It was actually returning all of the correct data in SPUFI and did the same thing in the CA batch processor. The authID of the COBOL batch job was running under the production authID and would have a much higher dispatch priority than when I ran manually. It had to hit the same tables, the qualifier was hardcoded in the statement. There were no host variables or parameter markers. The stats were full current runstats on tables that had just been loaded last week and in RO status so were in pristine condition. [...] 20182 329 38_Re: -911 fetching from a declared GTT.16_Proctor, William25_william.proctor@TGSLC.ORG30_Mon, 7 Nov 2005 10:39:24 -0600690_us-ascii We do not have our DB2 datasets shared and in the GRS ring they are excluded. We do use GTT tables but they are fetched from a GUI going thru DB2 Connect.





Bill Proctor

Database Administrator (Adabas/DB2)

DB2 UDB V7.1 Database Administration for OS/390

Texas Guaranteed Student Loan Corp.

Austin, Texas

Phone: 512-219-4847









-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Steve_Grimes@AISMAIL.WUSTL.EDU Sent: Monday, November 07, 2005 10:21 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] -911 fetching from a declared GTT. [...] 20512 166 38_Re: -911 fetching from a declared GTT.15_Philip Sevetson22_db2.dba.guru@GMAIL.COM30_Mon, 7 Nov 2005 11:48:42 -0500628_ISO-8859-1 Resource type 302 is a tablespace page. Since it's unlikely you'll be able to identify Page x'00E6' at this late date, you're left with speculation: could it be about creating a new table in the tablespace, and be encountering a space-page contention issue? In which case, your resolution (partial) would be to create more tablespaces in the temporary database in order to reduce the incidence of space-page collisions. BTW, are your temp database tablespaces segmented? If they're simple (SEGSIZE 0), you might be trying to put data from multiple tables on the same page... which would also cause this problem. [...] 20679 120 54_Re: DB2 Accounting Problem - Excessive SMF 101 Records35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Mon, 7 Nov 2005 11:49:10 -0500397_iso-8859-1 You can extract the 101 actg records from smf during the day too, if you need to get at the data.

Regards, Joel

----- Original Message ----- From: "Kirk Hampton" Newsgroups: bit.listserv.db2-l To: Sent: Monday, November 07, 2005 11:30 AM Subject: Re: [DB2-L] DB2 Accounting Problem - Excessive SMF 101 Records [...] 20800 311 60_Re: DB2 z/OS - dynamic statement different in COBOL vs SPUFI13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Mon, 7 Nov 2005 10:52:31 -0600424_us-ascii I think you will find that SPUFI (and probably the batch processor) are also doing a prepare/execute and not an execute immediate. I don't believe you can use EXECUTE IMMEDIATE for a SELECT statement. It sounds like you already have things changed so you are probably not able to recreate it anymore, but it would be interesting to see if a trace shows the same access path being picked up in both executions. [...] 21112 217 38_Re: -911 fetching from a declared GTT.7_troycci29_troycci@COLEMANCONSULTING.COM30_Mon, 7 Nov 2005 11:18:34 -0600485_us-ascii I have not seen this but have a few things to look at: Do you have multiple tablespaces available in your temp database? Are you seeing any lock escalation in the DB2MSTR log?









Troy Coleman, Support Engineer IBM Certified Solutions Expert

SoftBase Systems, Inc. 847-776-0618 828-670-9900 ext. 334 troy.coleman@softbase.com

Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/ [...] 21330 242 60_Re: DB2 z/OS - dynamic statement different in COBOL vs SPUFI7_troycci29_troycci@COLEMANCONSULTING.COM30_Mon, 7 Nov 2005 11:18:34 -0600467_us-ascii Hi Mark, More thoughts. Is your data skewed? Are you using the same value in the COBOL program on the OPEN cursor that you are using in SPUFI? You could be getting a tablespace scan if the value on a where predicate is qualifying more than 50% of the data. I see you are doing a lot of sort so that tells me your open cursor is having to resolve the result with a sort. This is also common if you are joining two tables that are not clustered the same. [...] 21573 159 60_Re: DB2 z/OS - dynamic statement different in COBOL vs SPUFI11_David Nance16_DWNance@FHSC.COM30_Mon, 7 Nov 2005 10:27:19 -0700381_us-ascii One other difference that you should take a look at is your bind parms. Compare how you have the cobol program bound to the way spufi is. Anything there that stands out. I know someone asked late last week if it could be degree any or 1 being a difference, but I don't remember seeing a response, but there are other bind parms that could make a difference, as well. [...] 21733 109 22_Re: DB2 Admin training15_Philip Sevetson22_db2.dba.guru@GMAIL.COM30_Mon, 7 Nov 2005 12:29:14 -0500577_ISO-8859-1 Bob, I was happy with the Themis DB2 training which I received (DB2 OS/390 Stored Procedures). --Phil S., who doesn't work for them.

On 11/4/05, Bob Henry wrote: > > Hello list, > > Having recently lost our DBA resource, I need to obtain some DB2 DBA > training. We run a small R7 environment, soon to migrate to R8. We have > very limited DBA requirements but occasionally the need arises. I've > looked at the training courses from Themis Inc. and have registered for > the DBA course. They also have a R7 to R8 migration class [...] 21843 29 25_LISTCAT HI-U-RBA Question16_Sharon Zitterman24_sharon.zitterman@NGC.COM30_Mon, 7 Nov 2005 11:33:47 -0600472_- Good morning,

We have a partitioned table with 128 partitions. We fill those partitions at a rate of approximately 1 per month and we track the growth on a weekly basis.

We are in partition 99, so I ran LISTCAT that included partition 100. The HI-U-RBA number returned for partition 100 indicates the partition is 0.18% full even though the catalog says there are no rows in that partition. Have never seen this LISTCAT result on any other partition. [...] 21873 164 60_Re: DB2 z/OS - dynamic statement different in COBOL vs SPUFI11_David Nance16_DWNance@FHSC.COM30_Mon, 7 Nov 2005 10:27:19 -0700381_us-ascii One other difference that you should take a look at is your bind parms. Compare how you have the cobol program bound to the way spufi is. Anything there that stands out. I know someone asked late last week if it could be degree any or 1 being a difference, but I don't remember seeing a response, but there are other bind parms that could make a difference, as well. [...] 22038 222 38_Re: -911 fetching from a declared GTT.7_troycci29_troycci@COLEMANCONSULTING.COM30_Mon, 7 Nov 2005 11:18:34 -0600485_us-ascii I have not seen this but have a few things to look at: Do you have multiple tablespaces available in your temp database? Are you seeing any lock escalation in the DB2MSTR log?









Troy Coleman, Support Engineer IBM Certified Solutions Expert

SoftBase Systems, Inc. 847-776-0618 828-670-9900 ext. 334 troy.coleman@softbase.com

Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/ [...] 22261 142 38_Re: -911 fetching from a declared GTT.0_18_iwant2beme@AOL.COM30_Mon, 7 Nov 2005 13:09:21 -0500484_us-ascii I believe we experienced a similar problem with Declared temporary tables when the tablespace was attempting to add additional extents. More space was added to the Temp tablespace, and more TCBs were added to the stored procedure address space.

Jacquie



-----Original Message----- From: Philip Sevetson To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Mon, 7 Nov 2005 11:48:42 -0500 Subject: Re: [DB2-L] -911 fetching from a declared GTT. [...] 22404 357 38_Re: -911 fetching from a declared GTT.12_Isaac Yassin20_yassini@BEZEQINT.NET30_Mon, 7 Nov 2005 21:12:14 +0200510_us-ascii Hi,

I would go for star GRS



Isaac Yassin





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Proctor, William Sent: Monday, November 07, 2005 6:39 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] -911 fetching from a declared GTT.





We do not have our DB2 datasets shared and in the GRS ring they are excluded. We do use GTT tables but they are fetched from a GUI going thru DB2 Connect. [...] 22762 137 25_Re: Stopped getting mails12_Isaac Yassin20_yassini@BEZEQINT.NET30_Mon, 7 Nov 2005 21:19:07 +0200430_us-ascii Hi,

too late now - but next time use the NO MAIL option of the subscription. (from personal experience - I'm just back from vacation ...)



Isaac Yassin





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sriramulu, Vijayababu Sent: Monday, November 07, 2005 9:33 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Stopped getting mails [...] 22900 41 27_Re: change tablespace state3_lan18_lxw176@HOTMAIL.COM30_Mon, 7 Nov 2005 13:38:22 -0600410_- Hi Ravi,

Thanks for your message. Actually, my database is showed as normal state, I can connect to it and execute any db2 command which not involved in the perticular tablespace that is in backup pending state. the error tablespace is found as the following: Tablespace ID = 4 Name = USER_REG Type = Database managed space Contents = Any data State = 0x0020 Detailed explanation: Backup pending [...] 22942 362 38_Re: -911 fetching from a declared GTT.12_Isaac Yassin20_yassini@BEZEQINT.NET30_Mon, 7 Nov 2005 21:12:14 +0200510_us-ascii Hi,

I would go for star GRS



Isaac Yassin





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Proctor, William Sent: Monday, November 07, 2005 6:39 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] -911 fetching from a declared GTT.





We do not have our DB2 datasets shared and in the GRS ring they are excluded. We do use GTT tables but they are fetched from a GUI going thru DB2 Connect. [...] 23305 367 38_Re: -911 fetching from a declared GTT.12_Isaac Yassin20_yassini@BEZEQINT.NET30_Mon, 7 Nov 2005 21:12:14 +0200510_us-ascii Hi,

I would go for star GRS



Isaac Yassin





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Proctor, William Sent: Monday, November 07, 2005 6:39 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] -911 fetching from a declared GTT.





We do not have our DB2 datasets shared and in the GRS ring they are excluded. We do use GTT tables but they are fetched from a GUI going thru DB2 Connect. [...] 23673 111 64_Re: Fw: DB2 z/OS - dynamic statement different in COBOL vs SPUFI10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Mon, 7 Nov 2005 16:11:44 -0500578_us-ascii Hi Mike,

The COBOL program is building the statement on the fly and then calling for a prepare before executing it, so there are no parameter markers or anything. That is why I am so perplexed - the same statement is being executed dynamically against the same tables in both cases. There was no difference in the tables (they remain in RO status) and no changes in bufferpools or anything else. I did try the reopt vars in the packages bind parameters, but since there were no variables, there was nothing that should have changed. I got the same results. [...] 23785 75 108_DB2 z/OS v8 - dynamic statement access different in COBOL vs SPUFI access path -- optimizer in v8 different?12_Backes, Mike21_Mike.Backes@OA.MO.GOV30_Mon, 7 Nov 2005 15:48:12 -0600318_US-ASCII Since going to v8, I have several static queries in a cobol pgm using host variables. Since going to v8 the cost of these has basically tripled. (several small queries ran with different values in the host variables millions of times) so its not like im trying to cut hours of cpu off of a single query. [...] 23861 143 112_Re: DB2 z/OS v8 - dynamic statement access different in COBOL vs SPUFI access path -- optimizer in v8 different?11_Mark Kimble25_mark.a.kimble@US.HSBC.COM30_Mon, 7 Nov 2005 15:57:04 -0600735_US-ASCII Is STTL_LONG_DESC VARCHAR? I forget the parm, but there are specifics for getting index only with V8 and VARCHAR.

Mark Kimble Data Operations - WD2 Phone: 630-521-3287 Pager: 866-219-1720



|---------+-----------------------------------> | | "Backes, Mike" | | | | | | Sent by: DB2 Data | | | Base Discussion List | | | | | | | | | | | | 11/07/2005 03:48 PM | | | Please respond to DB2| | | Database Discussion | | | list at IDUG | | | | |---------+-----------------------------------> >-----------------------------------------------------------------------------------------------------------------------| | | | To: DB2-L@www.idugdb2-l.org | | cc: [...] 24005 15 17_uninstall db2 NSE3_lan18_lxw176@HOTMAIL.COM30_Mon, 7 Nov 2005 16:24:49 -0600632_- Hi,

Does anyone know the command to uninstall the db2 net search extender 8.2 which was installled on AIX 5.3 server with db2 8.2? Thanks inadvance! Lan

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 24021 196 112_Re: DB2 z/OS v8 - dynamic statement access different in COBOL vs SPUFI access path -- optimizer in v8 different?12_Backes, Mike21_Mike.Backes@OA.MO.GOV30_Mon, 7 Nov 2005 16:31:24 -0600517_US-ASCII No, here are the column defs STTL_CD CHAR 2 STTL_EFF_DATE CHAR 8 TITL_CD CHAR 6 STTL_EXP_DATE CHAR 8 STTL_LONG_DESC CHAR 30 DELETE_FL CHAR 1



Mike Backes ph: 522-5829

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Kimble Sent: Monday, November 07, 2005 3:57 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 z/OS v8 - dynamic statement access different in COBOL vs SPUFI access path -- optimizer in v8 different? [...] 24218 146 112_Re: DB2 z/OS v8 - dynamic statement access different in COBOL vs SPUFI access path -- optimizer in v8 different?11_Mark Kimble25_mark.a.kimble@US.HSBC.COM30_Mon, 7 Nov 2005 15:57:04 -0600735_US-ASCII Is STTL_LONG_DESC VARCHAR? I forget the parm, but there are specifics for getting index only with V8 and VARCHAR.

Mark Kimble Data Operations - WD2 Phone: 630-521-3287 Pager: 866-219-1720



|---------+-----------------------------------> | | "Backes, Mike" | | | | | | Sent by: DB2 Data | | | Base Discussion List | | | | | | | | | | | | 11/07/2005 03:48 PM | | | Please respond to DB2| | | Database Discussion | | | list at IDUG | | | | |---------+-----------------------------------> >-----------------------------------------------------------------------------------------------------------------------| | | | To: DB2-L@www.idugdb2-l.org | | cc: [...] 24365 128 112_Re: DB2 z/OS v8 - dynamic statement access different in COBOL vs SPUFI access path -- optimizer in v8 different?7_troycci29_troycci@COLEMANCONSULTING.COM30_Mon, 7 Nov 2005 17:02:44 -0600502_US-ASCII You can get the same access path as using a host variable by using "?". SELECT STTL_EFF_DATE, STTL_LONG_DESC FROM REF_STTL WHERE TITL_CD = ? AND STTL_CD = ? AND STTL_EFF_DATE <= ? AND STTL_EXP_DATE >= ? AND DELETE_FL = ?

DB2 will assume even distribution and guess at the cardinality of the columns. To get the same access as you do in SPUFI you may want to try BIND option REOPT(VARS). You also may want to do a runstats at take a look at the KEYCARD and FREQVAL NUMCOLS options. [...] 24494 218 112_Re: DB2 z/OS v8 - dynamic statement access different in COBOL vs SPUFI access path -- optimizer in v8 different?25_Thanikachalam Sundarrajan21_billysundar@YAHOO.COM30_Mon, 7 Nov 2005 16:32:45 -0800603_iso-8859-1 Check your SYSCOLDIST fpr the table. If you have a test environment, you can try re-binding the program with the stats copied over from PROD with the exception of SYSCOLDIST. --- "Backes, Mike" wrote:

> No, here are the column defs > STTL_CD CHAR 2 > STTL_EFF_DATE CHAR 8 > TITL_CD CHAR 6 > STTL_EXP_DATE CHAR 8 > STTL_LONG_DESC CHAR 30 > DELETE_FL CHAR 1 > > > Mike Backes > ph: 522-5829 > > -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On > Behalf Of Mark Kimble > Sent: Monday, November 07, 2005 3:57 PM [...] 24713 109 19_Re: z/os v8 problem7_troycci29_troycci@COLEMANCONSULTING.COM30_Mon, 7 Nov 2005 20:58:49 -0600549_us-ascii Did you ever resolve this? You mentioned that the statement should be: RUNSTATS TABLESPACE FSPROD2.GLWORK TABLE(FSPROD.PS_JRNL_LN_TAO2) SAMPLE 25 INDESX(FSPROD.PS_JRNL_LN_TAO2) REPORT NO SHRLEVEL CHANGE UPDATE ACCESSPATH

Yet Peoplesoft is passing: UTILID = PS11143533 RUNSTATS TABLESPACE FSPROD2.GLWORK TABLE(FSP RUN RECSTATS FOR TABLE (PS_JRNL_LN_TAO2,LOW)

Is the (FSP RUN RECSTATS.... Correct?

Second utility id: PS11143533313776 RUNSTATS TABLESPACE FSPROD2.GLWORK TABLE(FSPROD.PS_JRNL_LN_TAO2) SAMPLE 25 INDEX( [...] 24823 82 49_Re: VARCHAR vs CHAR in cross-platform development7_troycci29_troycci@COLEMANCONSULTING.COM30_Mon, 7 Nov 2005 21:25:07 -0600518_us-ascii You are correct in that DB2 V8 throws out a lot of the reasons why we avoided VARCHAR. However, I've also seen a lot of developers never check the length of input and always store the max length in the varchar field. So if you define a field as varchar(254) and most of the time the data is only char(20) then you are wasting a lot of disk space. Yet if your application has a real need to store an entry as 254 once in awhile then you must design to support that. Some people like to take this approach [...] 24906 174 24_Re: clean up plan tables7_troycci29_troycci@COLEMANCONSULTING.COM30_Mon, 7 Nov 2005 21:34:05 -0600578_us-ascii If you want a very good tool that can manage not only the number of versions but can also apply quality rules to your SQL explain output and provide a feature to compare explain output and changes then look at DBIQ/QA from Insoft http://www.insoft.de/index.php?lang=en &cont=20&PHPSESSID=d10b0bd58880ea51ada95100ef88d297

This could also be a good tool to capture your V7 explains before you upgrade to V8 and compare and look for negative changes to access paths. [...] 25081 104 49_Re: VARCHAR vs CHAR in cross-platform development8_pjfjacks19_pjfjacks@SWBELL.NET30_Mon, 7 Nov 2005 22:14:03 -0600307_us-ascii I thought that the whole purpose of a varchar was to be able to define as the largest expected value, but store only the N characters of the actual value + a byte or so that indicate this actual size. (given that the input value is properly trimmed of extraneous spaces). Is this not correct? [...] 25186 136 112_Re: DB2 z/OS v8 - dynamic statement access different in COBOL vs SPUFI access path -- optimizer in v8 different?13_Terry Purcell18_tpurcel@US.IBM.COM30_Mon, 7 Nov 2005 22:26:12 -0600358_- Hi Mike,

Given the information you have provided, I will make some assumptions about the remainder (remember, I am guessing).

I expect INDEXA is considerably smaller than INDEXB, both in number of NLEAF and also NLEVELS (INDEXB may be at least 1 NLEVELS higher than INDEXA). FULLKEYCARDF of INDEXA is relatively close to that of INDEXB. [...] 25323 110 54_Re: DB2 Accounting Problem - Excessive SMF 101 Records12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 7 Nov 2005 22:36:38 -0600367_- This is when the DB2 PM report options get useful. I'd start with order by CONNTYPE,CONNECT,PACKAGE to see the numbers. It looks as though the TOP option does not have connections as one of the choices.

If this is distributed connections or RRS attach, we did add an option to accumulate multiple accounting records into one, but that's only in DB2 V8. [...] 25434 28 21_Re: uninstall db2 NSE14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 2 Aug 2005 17:02:20 -0700702_US-ASCII Don't have a definitive answer, but have you tried using SMIT?

James Campbell

On 7 Nov 2005 at 16:24, lan wrote:

> Hi, > > Does anyone know the command to uninstall the db2 net search extender 8.2 > which was installled on AIX 5.3 server with db2 8.2? > Thanks inadvance! > Lan > > --------------------------------------------------------------------------------- > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. [...] 25463 230 49_Re: VARCHAR vs CHAR in cross-platform development14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 2 Aug 2005 17:02:20 -0700610_US-ASCII You are correct, Phil (although it's actually two bytes to indicate the actual size).

However the points that Troy and Lauren are making is that this is not free of user responsibility to manage eg "properly trimmed of extraneous spaces" and (in prior versions) indexability of comparisions between fixed and variable length columns. Or (can't resist the example), as written by one off-shore software house: 01 COL. 49 COL-LEN PIC 9999 COMP. 49 COL-DATA PIC X(255). ... IF NO-DATA MOVE SPACE TO COL END-IF. (At least the person thought of the no-data condition. Not that s/he tested it.) [...]