1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l November 2007, week 5 2 390 39_Re: sql store procedure multi row fetch15_John Panopoulos22_j.panopoulos@TTBANK.GR31_Thu, 29 Nov 2007 09:11:00 +0200473_us-ascii I am using an SQL store Procedure in DB2 V8 and it seems that the compiler does not know

this syntax





FETCH NEXT ROWSET FROM curname FOR 5 ROWS

INTO ...............

ILLEGAL SYMBOL "ROWSET".





I have used multi row fetch in a Cobol Store Procedure.

What is your opinion about store procedures regarding performance?

SQL Store Procedures or Cobol Store Procedures (multi row fetch)? [...] 393 197 17_DB2 z/os and .Net15_John Panopoulos22_j.panopoulos@TTBANK.GR31_Thu, 29 Nov 2007 09:20:58 +0200475_us-ascii I have an application server running Windows 2003 and IIS.

I have a Web application in the server that access DB2 though DB2.Net Provider.

CLI does not use ODBC to access DB2 database on the z/os mainframe.

I am getting a quite good performance.

What is your opinion about mixing these two technologies?





Using IBM's BMC tool I found out that fetching 72000 rows requires about 72 fetches of 99 rows per fetch? [...] 591 113 19_Troy Coleman's Blog10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Thu, 29 Nov 2007 07:12:01 -0500650_iso-8859-1

Hi DB2 user,

Here is a non-profit Blog about DB2 for z/OS (IBM mainframes):http://db2usa.blogspot.com Last update on Monday, November 26th 2007 Troy Coleman published several posts about DB2 for z/OS on his blog DB2utor:

- DB2 9 Index with Expressions- DB2 9 Mysteries of DB2 Storage Groups- Smart Reoptimization for Dynamic SQL- Evolution of Online Schema Change to Database Definition on Demand- Reserved Words in DB2 9 for z/OS- Explaining How DB2 Will Get Data for a Given SQL Statement- What Makes Compressed Indexes Tick?- PureXML: Why Is This Feature So Important to Your Business?- DB2 V9 Logged vs. Not [...] 705 260 39_Re: sql store procedure multi row fetch0_19_askwe@OPTONLINE.NET31_Thu, 29 Nov 2007 13:35:29 +0000322_us-ascii I also encountered a similar problem on my FETCH statetement, in my case the error message is about the 'FOR' keyword. When I deleted the 'FOR' keyword, I received another error message that mentioned that the 'FOR' keyword is required. I deployed the DSNTPSMP program preparation in DB2 for z/OS Version 8. [...] 966 339 39_Re: sql store procedure multi row fetch0_26_John.L.Miller@ATCOITEK.COM31_Thu, 29 Nov 2007 09:23:13 -0700442_us-ascii My apologies to everyone! This is one of those things that I thought I tested, but I guess I didn't :-(

When I responded I was certain it worked. My apologies again, hope I didn't waste anybodies time.

On the plus side, Multi-Row fetch is used by distributed applications which call SQL stored procedures who return a non-ambiguous cursor. This functionality is available in CM mode with no code changes required. [...] 1306 429 21_Re: DB2 z/os and .Net0_26_John.L.Miller@ATCOITEK.COM31_Thu, 29 Nov 2007 09:50:58 -0700563_us-ascii John,

We use the DB2.NET Provider to access DB2 on the z/os mainframe. We use the provider to call a mixture of SQL and COBOL stored procedures. This works very well for us and we also see quite good performance. Your statement that CLI does not use ODBC is correct, but keep in mind that the reverse is true, ODBC uses CLI. The .NET provider also uses CLI to access the database. In my opinion, if done properly the .NET provider is a very good language for accessing DB2 from the windows platform. Be sure that you handle your transactions [...] 1736 265 27_Renaming DSNTEP4 to DSNTEP223_Tino Manganaro (DHL US)22_Tino.Manganaro@DHL.COM31_Thu, 29 Nov 2007 10:24:32 -0700304_us-ascii I currently working out the roll-out of DB2 V810 to NFM Mode and I trying to decide if it is good idea to rename DSNTEP4 to DSNTEP2 and force the Multi-Row capabilities on my user community. I was hoping to hear any negative or positive feedback on any issues that you may have run across. [...] 2002 157 31_Re: Renaming DSNTEP4 to DSNTEP213_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Thu, 29 Nov 2007 12:43:05 -0500328_us-ascii Hi Tino,

Here's a consideration. I believe it was Roger Miller at the DB2 Tech Conference as v8 was new who said that IBM found customers who used TEP2 for regression testing of the new release and that's one of the reasons TEP4 did not replace TEP2. You may look out for similar uses of TEP2 in your shop. [...] 2160 75 43_Re: ADMIN: DB2-L Move/ATTN IMPORTANT NOTICE16_Mark Labby - AES21_mlabby@AESSUCCESS.ORG31_Thu, 29 Nov 2007 13:07:24 -0500387_windows-1252 Robert and List, a quick update -

During a conference call this morning the new hosting company assured IDUG that they are ready and this server move was given the OK.

It will occur as planned tomorrow evening- Friday, November 30th as you indicate below.

On Tue, 27 Nov 2007 22:34:09 -0500, Galambos, Robert wrote: [...] 2236 49 47_DB2 v8 for z/OS distributed processing question18_Sandra Lakenburger27_sandra.lakenburger@USDA.GOV31_Thu, 29 Nov 2007 13:31:28 -0500550_windows-1252 I have an application team that has found since migrating their subsystems to v8 CM, several of their distributed jobs are failing. The one example they gave me was a COBOL program that under v7 runs successfully as bound and installed. It contains no SQL requests that execute against the local DB2 for z/OS subsystem, so in v7, they did not have a package bound locally. All of the SQL that the program contains is executed once a CONNECT is issued to the REMOTE LUW location and they did have a package bound with this location. [...] 2286 45 51_Re: DB2 v8 for z/OS distributed processing question17_Gugel, Rich - DOA24_rich.gugel@WISCONSIN.GOV31_Thu, 29 Nov 2007 13:08:41 -0600481_us-ascii Sandra

I'll bet your running into this

PKGLDTOL is no longer supported: The subsystem parameter PKGLDTOL is no

| longer supported. DB2 Version 8 requires the package or plan for | applications with the following SQL statements:



| COMMIT

| CONNECT

| DESCRIBE TABLE

| RELEASE

| ROLLBACK

| SET CONNECTION

| SET host-variable = CURRENT SERVER

| VALUES CURRENT SERVER INTO host-variable [...] 2332 91 51_Re: DB2 v8 for z/OS distributed processing question2_MH25_marcel.harleman@HCCNET.NL31_Thu, 29 Nov 2007 20:24:23 +0100437_us-ascii Hi,

I'm not sure if it relates to your problem (don't know what the code in the COBOL program looks like), but perhaps you could have a look at the installation guide (GC18-7418-06), chapter 6 "Installing, migrating, and updating system parameters" and then look at the remarks under "Database protocol" for panel DSNTIP5 (Distributed data facility panel 2). Perhaps that explains a little bit of what is happening. [...] 2424 213 31_Re: Renaming DSNTEP4 to DSNTEP213_Palko, George16_gpalko@OPERS.ORG31_Thu, 29 Nov 2007 15:14:57 -0500463_iso-8859-1 Hi Tino,

We've been at V8NFM for a couple of years now. We have held off rolling out the DSNTEP4 module as a replacement for DSNTEP2. We discovered that DSNTEP4, under certain circumstances, had performance issues compared to DSNTEP2. Please read APAR PK05360, it pertains to hybrid joins. The APAR has fixed the problem; however, prior to replacing DSNTEP2 with DSNTEP4, our shop plans on performing a complete regression test of DSNTEP4. [...] 2638 460 25_Create View Authority ???9_Dee Reins24_dee.reins@CENTURYTEL.COM31_Thu, 29 Nov 2007 14:42:59 -0600848_us-ascii CREATE VIEW CPR_EQPT AS



SELECT CAST(CPRP.EXCH_CODE AS INTEGER) AS EXCH



,CPRS.BLDG



,CPRP.PROP_ID



,CPRP.MFR_CODE



,CPRP.PART_CODE



FROM CTCPRO.TCPCPR CPRP



,CTCPRO.TCPSITE CPRS



WHERE CAST(CPRP.EXCH_CODE AS INTEGER) IN



(0201,0202,9653,9654)



AND CAST(CPRP.EXCH_CODE AS INTEGER) =



CAST(CPRS.EXCH_CODE AS INTEGER)



;



---------+---------+---------+---------+---------+---------+---------+-- -------+

DSNT408I SQLCODE = -551, ERROR: TSXVDRI DOES NOT HAVE THE PRIVILEGE TO



PERFORM OPERATION CREATE VIEW ON OBJECT CTCPRO.TCPCPR





The user TSXVDRI has authority to insert, update, select on table CTCPRO.TCPCPR [...] 3099 136 29_Re: Create View Authority ???14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 29 Nov 2007 15:52:33 -0500629_utf-8 Hmm. Don't you have to have SELECT...WITH GRANT AUTHORITY in order to create a view with your own creator, which you would then have authority to GRANT privileges on?

--Phil Sevetson, NYCAPS DBA Support Financial Information Services Agency of The City of New York 450 West 33rd Street, 4th Floor New York, NY 10001 phone: (212) 857-1688 mailto: psevetson@fisa.nyc.gov ________________________________________ From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dee Reins Sent: Thursday, November 29, 2007 3:43 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Create View Authority ??? [...] 3236 23 48_DB2 for z/OS V8: Capture package name in Trigger17_Chris Worthington20_cworthi@AGRIBANK.COM31_Thu, 29 Nov 2007 15:43:25 -0500548_windows-1252 I'm looking for a programmatic way to identify/capture the name of the package that caused a trigger to fire. I didn't see anything in the list of special registers that looked like it would help (maybe I missed something)?

Thanks in advance!

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org [...] 3260 45 42_Antwort: [DB2-L] Create View Authority ???0_16_R.Boxwell@SEG.DE31_Thu, 29 Nov 2007 21:57:08 +0100627_UTF-8







Hi

The problem is that the userid does not have SYSADM. To create a VIEW with a different creatorid from the process needs a "hefty" userid. Here is a cut-and-paste from CREATE VIEW

If the statement is dynamically prepared, the following rules apply:

v If the SQL authorization ID of the process has SYSADM authority, the owner of the view can be any authorization ID. If that authorization ID has SYSCTRL but not SYSADM authority, the following is true: the owner of the view can be any authorization ID, provided the view does not refer to user tables or views [...] 3306 173 29_Re: Create View Authority ???15_Akula, Nagesh X27_NageshAkula@FIRSTHEALTH.COM31_Thu, 29 Nov 2007 13:56:00 -0700443_iso-8859-1 I think the key here is CAST function.

Please refer below as per the manual.

Authorization For every table or view identified in the fullselect, the privilege set that is defined below must include at least one of the following:

The SELECT privilege on the table or view Ownership of the table or view DBADM authority for the database (tables only) SYSADM authority SYSCTRL authority (catalog tables only) [...] 3480 46 49_Stored proc receiving -805 executing from trigger13_Donna Domovic28_Donna.Domovic@EXELONCORP.COM31_Thu, 29 Nov 2007 16:10:39 -0500672_windows-1252 Hello,

We have a situation where we are doing processing in a REXX exec which causes a DB2 trigger to be executed. The DB2 trigger then calls a Cobol stored procedure. When this happens, we receive the following message:

DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME DB2TLOC.TESTCOLL.STORPRC- 1.183249E003BEC507 NOT FOUND IN PLAN DSNREXX. REASON 02 DSNT418I SQLSTATE = 51002 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -250 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFF06' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION [...] 3527 40 51_[ADMIN] The DB2L migration is set for November 30th13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 29 Nov 2007 15:25:57 -0600592_ISO-8859-1 The DB2-L list is moving this weekend. This move should bring stability to the list and give us the option to be more flexible in the future. Of course, to make things better, there will be the small cost of an outage, although we hope a very small outage.

This conversion and migration will require a brief stop in service of the DB2-L list as the old and new servers are synchronized. However, we expect this out to be very brief. As subscribers, this transition should be relatively seamless. All existing messages are being archived and migrated. How list messages [...] 3568 163 29_Re: Create View Authority ???9_Mike Bell21_mbell11a1@VERIZON.NET31_Thu, 29 Nov 2007 15:31:43 -0600374_windows-1250 thoughts 1. It doesn't require DBADM or SYSADM to create a view unless you try to qualify the view with a different creator. That would require userid.CPR_EQPT in the create. 2. CAST is an implicit function - doesn't need any authorization. 3. There are 2 tables referenced CTCPRO.TCPCPR and CTCPRO.TCPSITE - Does the user have select auth on both tables? [...] 3732 151 53_Re: Stored proc receiving -805 executing from trigger0_19_askwe@OPTONLINE.NET31_Fri, 30 Nov 2007 02:39:25 +0000398_us-ascii Donna,

Last month I received a similar SQLCODE = -805 with REASON = 02 during the invocation of a Cobol Stored procedure by a DB2 trigger. The problem was solved when I changed the collection id of the Cobol Stored Procedure to the schema name of the trigger. Did you bind your Cobol Stored Procedure with the same collection-id that was listed in the error message: TESTCOLL? [...] 3884 29 37_Global Contention & false contentions8_Nick Nur17_nick_nur@YAHOO.CA31_Thu, 29 Nov 2007 22:33:31 -0500364_windows-1252 Hi Colleagues

How can I reduce real global contention in datasharing group where the environment requires high level of concurrency? I know only a binding parameter release (commit) in DB2 z/OS v8. Is there any thing else I can do to reduce the amount of real lock global contention?

thanks

Nick Nur

IMPORTANT NOTICE: [...] 3914 56 29_Re: Create View Authority ???14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK31_Fri, 30 Nov 2007 08:32:21 +0100330_iso-8859-1 > The user TSXVDRI has authority to insert, update, select on table > CTCPRO.TCPCPR > > What am I missing.

Most likely, the user's secundary ID has this SELECT auth. For DML (select etc.) the secundary ID is automatically consulted, but not for DDL. Since CREATE VIEW is DDL, this could be the explanation. [...] 3971 57 29_Re: Create View Authority ???13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 30 Nov 2007 07:20:13 -0500273_us-ascii And once you get past this, you'll run into the gotcha that when you create a view with an owner other than yourself, you can't Grant privileges on that view unless you have SYSADM. I think it's also true you can't drop the view, but I haven't run into that. [...] 4029 32 51_Re: DB2 v8 for z/OS distributed processing question18_Sandra Lakenburger27_sandra.lakenburger@USDA.GOV31_Fri, 30 Nov 2007 09:43:59 -0500338_windows-1252 Rich,

I think you're right. Thanks for the reminder about PKGLDTOL. I inherited these subsystems and not sure why, but someone coded PKGLDTOL as YES in all of them. I didn't have enough time to explore changing it back before having to get v8 rolling, so I figured that we'd just deal with anything that came up. [...] 4062 34 29_Tablespace DSSIZE Z/OS DB2 V810_Dave Nance16_dav1mo@YAHOO.COM31_Fri, 30 Nov 2007 10:34:54 -0500569_windows-1252 Thanks for any help anyone can give me.

We had some tablespaces that were defined with 50 partitions. Which the book states 50 partitions and not defined as large, then you are limited to 2G. So, we decided rather than dropping and recreating, we would use some of the new V8 features. We added 15 partitions and then used reorg rebalance and reorged again. Worked great!!! Plenty of room for growth or so we thought. Now when using the admin tool and looking at DDL for the tablespace we see that it still has 2G as the DSSIZE. Shouldn't that [...] 4097 318 31_Re: Renaming DSNTEP4 to DSNTEP214_Barbara Koenen33_barbara.koenen.bgit@STATEFARM.COM31_Fri, 30 Nov 2007 08:36:00 -0700340_us-ascii We did replace DSNTEP2 with DSNTEP4, figuring everyone should probably be running DSNTEP4.

We had to back off and put DSNTEP2 back out there. We ran into a problem with one job that couldn't run with DSNTEP4.

I don't remember the details, but I can get them if you need them. (The sysadm involved is ooo today.) [...] 4416 72 33_Re: Tablespace DSSIZE Z/OS DB2 V814_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 30 Nov 2007 10:42:47 -0500426_us-ascii It is probably significant that DSSIZE is not one of the properties of a tablespace which you can ALTER. To get a larger DSSIZE you have to DROP/CREATE the tablespace (and dependent objects, of course).

--Phil Sevetson, NYCAPS DBA Support Financial Information Services Agency of The City of New York 450 West 33rd Street, 4th Floor New York, NY 10001 phone: (212) 857-1688 mailto: psevetson@fisa.nyc.gov [...] 4489 54 33_Re: Tablespace DSSIZE Z/OS DB2 V819_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Fri, 30 Nov 2007 10:46:20 -0500326_us-ascii First, if you do not specify LARGE or DSSIZE, then the limit for a tablespace with 50 partitions is 1G. I suspect you have explicitly defined the tablespace partitions as 2G.

You cannot change DSSIZE by adding partitions; in fact you cannot change DSSIZE except by dropping and recreating the tablespace. [...] 4544 87 41_AW: [DB2-L] Tablespace DSSIZE Z/OS DB2 V80_26_Walter.Janissen@ITERGO.COM31_Fri, 30 Nov 2007 16:51:08 +0100570_iso-8859-1 Dave

64 partitions is the maximun number of partitions for the old ones. So if you omit DSSIZE, the maximum growth is 64 GB. That means, each partition is limited to 1 GB. How do you think 2GB?



Bitte richten Sie, falls Sie es nicht schon tun, in Zukunft alle E-Mails nicht mehr an Einzelpersonen, sondern immer an das Postfach "LI25103D - Entwicklungssupport - Gruppenpostfach". Ansonsten kann sich die Bearbeitung Ihrer E-Mail bedingt durch Urlaub, Gleittag, Krankheit, Projektarbeit in Köln, ZEITplus o.ä. verzögern. Vielen Dank! [...] 4632 53 45_Re: AW: [DB2-L] Tablespace DSSIZE Z/OS DB2 V816_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Fri, 30 Nov 2007 11:13:57 -0500295_us-ascii As already mentioned, you will need to drop and create the tablespace in order to take advantage of DSSIZE and the bigger VSAM datasets. In your case having 50 parts you are limited to 1GB according to the snip from the IBM doc below sice the Tablespace is created WITHOUT DSSIZE. [...] 4686 181 20_ADMIN: REMINDER move16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Fri, 30 Nov 2007 12:14:49 -0500510_iso-8859-1 Just wanted to send out a reminder that the LISTSERVs from IDUG will be moving from one Hosting company to another later today. We do not anticipate that it will be for any length of period of time.

thank you for your understanding







Robert Galambos CIPP/C

Compuware Senior Technical Specialist IBM Certified Solutions Expert - DB2 UDB for OS/390 Database Administration Certified Information Privacy Professional/Canada robert.galambos@compuware.com [...] 4868 40 47_Quick question on OUTBUFF parameter Z/OS DB2 V812_Martin Hubel17_Martin@MHUBEL.COM31_Fri, 30 Nov 2007 12:57:49 -0500 4909 309 51_Re: Quick question on OUTBUFF parameter Z/OS DB2 V811_Bear, Brian23_Brian.Bear@CHARMING.COM31_Fri, 30 Nov 2007 13:10:43 -0500486_US-ASCII The way I read it, it's bytes. Quoting the Installation Guide:

"You can enter the value in bytes (for example, 40960) or use the abbreviation K for kilobytes (for example 40K).





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Martin Hubel Sent: Friday, November 30, 2007 12:58 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Quick question on OUTBUFF parameter Z/OS DB2 V8 [...] 5219 214 51_Re: Quick question on OUTBUFF parameter Z/OS DB2 V835_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Fri, 30 Nov 2007 13:11:19 -0500593_iso-8859-1 Unless things have changed, and it's not likely at this level, 4000 is 4,000K or 1000 4K buffers

Regards, Joel

Joel Goldstein Responsive Systems Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts Group Buffer Pool performance too! www.responsivesystems.com tel. (732) 972-1261 fax.(732) 972-9416 ----- Original Message ----- From: Martin Hubel Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Friday, November 30, 2007 12:57 PM Subject: [DB2-L] Quick question on OUTBUFF parameter Z/OS DB2 V8 [...] 5434 42 40_Segmented tablespace LDS DSN size (Z/OS)0_37_Sangameswara.R.Panchomarthi@CHASE.COM31_Fri, 30 Nov 2007 13:15:28 -0500332_iso-8859-1 According to my knowledge on DB2, Segmented table Dear Experts,

According to my knowledge on DB2, Segmented table space DSN will grow up to 2GB in size and then another dataset will be created until we reach total of 32 datasets for the table space. Maximum size of segmented table space is 32 x 2GB = 64 GB. [...] 5477 142 77_Baltimore/Washington DB2 Users Group--Meeting Announcement--December 12, 200712_Henry Nalven25_henry.nalven@MARRIOTT.COM31_Fri, 30 Nov 2007 13:48:27 -0500512_windows-1252 The Baltimore/Washington DB2 Users' Group Meeting Schedule for September 12, 2007 (Wednesday)

When? Wednesday, December 12, 2007, 9:00 AM-3:30 PM (Registration at 8:30)

Where? Sheraton Columbia, 10207 Wincopin Circle Columbia, MD (410) 730-3900

Meeting Fees: Pre-registered members $20 Pre-registered non-members $30 At-the-door for members $30 At-the-door for non-members $40

To pre-register, please mail the appropriate fee with attendee's and employer's name to: [...] 5620 201 13_ADMIN: status16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Fri, 30 Nov 2007 13:59:03 -0500423_iso-8859-1 we have been directing people to www.idug.org for a status of the move.

That being said we have set up an email account which you can send a note to. In response you will receive an email stating the status of the move, and/or any updates.

DB2-L Status db2lstatus@gmail.com

this should be only used to get a status report. We will not be reading any of the emails sent to this account. [...] 5822 142 77_December 12 2007--Baltimore/Washington DB2 Users' Group--Meeting Announcement12_Henry Nalven25_henry.nalven@MARRIOTT.COM31_Fri, 30 Nov 2007 14:21:30 -0500511_windows-1252 The Baltimore/Washington DB2 Users' Group Meeting Schedule for December 12, 2007 (Wednesday)

When? Wednesday, December 12, 2007, 9:00 AM-3:30 PM (Registration at 8:30)

Where? Sheraton Columbia, 10207 Wincopin Circle Columbia, MD (410) 730-3900

Meeting Fees: Pre-registered members $20 Pre-registered non-members $30 At-the-door for members $30 At-the-door for non-members $40

To pre-register, please mail the appropriate fee with attendee's and employer's name to: [...] 5965 71 51_Re: Quick question on OUTBUFF parameter Z/OS DB2 V812_Martin Hubel17_Martin@MHUBEL.COM31_Fri, 30 Nov 2007 14:41:08 -0500 6037 147 51_Re: Quick question on OUTBUFF parameter Z/OS DB2 V821_wfavero@attglobal.net21_wfavero@ATTGLOBAL.NET31_Fri, 30 Nov 2007 14:49:52 -0500447_iso-8859-1 I'm not sure if this helps..

From the description portion of the DSN6LOGP macro,

A DECIMAL VALUE IN THE RANGE OF 40 TO 400000 SPECIFYING THE OUTPUT LOG BUFFER POOL SIZE IN K-BYTES.

Willie Favero

Original Message: ----------------- From: Martin Hubel Martin@MHUBEL.COM Date: Fri, 30 Nov 2007 14:41:08 -0500 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Quick question on OUTBUFF parameter Z/OS DB2 V8 [...] 6185 467 51_Re: Quick question on OUTBUFF parameter Z/OS DB2 V835_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Fri, 30 Nov 2007 14:51:21 -0500507_iso-8859-1 That is certainly what the manual states for V7 and later. It also says the default now is 4000K.

Going back several versions of DB2, the default was 400 (not K). Which I always used as 400K....

The old default of 400 should have caused instantaneous and massive log performance problems, even on a small system. Many small to medium systems ran quite happily with that value. Of course we have to consider the amount of data being changed. Inserts required a full 4096 bytes. [...] 6653 80 51_Re: Quick question on OUTBUFF parameter Z/OS DB2 V835_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Fri, 30 Nov 2007 16:16:35 -0500465_iso-8859-1 Please not that I had an incorrect statement in my earlier reply, a small brain f**rt.

The amount of data logged for an insert is the row, not the whole page. (Thanks for alert Phil).

Joel

Joel Goldstein Responsive Systems Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts Group Buffer Pool performance too! www.responsivesystems.com tel. (732) 972-1261 fax.(732) 972-9416 [...] 6734 104 51_Re: Quick question on OUTBUFF parameter Z/OS DB2 V811_Bear, Brian23_Brian.Bear@CHARMING.COM31_Fri, 30 Nov 2007 17:11:23 -0500442_iso-8859-1 I don't know how to verify the actual amount of log buffer allocated but my zparms show





* PARM TYPE SCP MIN MAX CURRENT-VALUE *

LOGOUTPT NUM M 40960 409600000 409600





DSNTIPL

2 OUTPUT BUFFER ===> 409600 Size in bytes (40K-400000K)





And Mainview for DB2 shows:

- - - - - - - - - - - - - - - - - BUFFERS - - - - - - - - - - - - - - - - - - [...] 6839 145 51_Re: Quick question on OUTBUFF parameter Z/OS DB2 V812_Martin Hubel17_Martin@MHUBEL.COM31_Fri, 30 Nov 2007 17:23:07 -0500 6985 157 51_Re: Quick question on OUTBUFF parameter Z/OS DB2 V80_34_AnandJayachandran@FREIGHTLINER.COM31_Fri, 30 Nov 2007 16:12:55 -0800481_us-ascii OUTBUFF ZPARM value in

* Test - 0000000400 (in kilobytes) * QA and Prod - 0000000800 (in kilobytes)



From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Martin Hubel Sent: Friday, November 30, 2007 14:23 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Quick question on OUTBUFF parameter Z/OS DB2 V8

Thanks Brian, As I suspected, your values are 10X bigger than my customer's. I thought so. Best regards, Martin [...] 7143 74 13_ADMIN: update16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Fri, 30 Nov 2007 23:47:54 -0500488_iso-8859-1 due to technical difficulties, we were unable to complete the move.

watch for further information on the list..

the old system is back up The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...]