1 IDUGDB2-L.ORG /home/listserv/home/db2-l May 2010, week 2 2 136 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions13_David Simpson22_dsimpson@THEMISINC.COM30_Sat, 8 May 2010 07:49:28 -0500536_utf-8 There is no way to run reorgs using the V8 method in DB2 9. BUILD2 is gone and there is no way to get it back. As was mentioned if you have no NPIs then nothing changes.

Here's another thing to think about.... You do want to run as many parts as possible in the same execution of the utility. However there is currently no easy way to reorg multiple non-contiguous parts (although there is a crazy undocumented hack for this). If you want to reorg parts 1 through 10, that's easy PART 1:10 works. If you want to reorg [...]46_000e01caeeac$e7016c29$0a0b010a@themisinc.local 139 252 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions0_24_hhuang@DCCSH.ICBC.COM.CN30_Sun, 9 May 2010 09:43:22 +0800650_GB2312 SCOPE PENDING

blink

Huang Hao
Senior Manager, Dept. System
ICBC Data Center(Shanghai)
Tel :(+86)021-28989825
Mail:hhuang@dccsh.icbc.com.cn

David Simpson
发件人: IDUG DB2-L
2010-05-08 20:49
请答复 给
IDUG DB2-L

收件人
DB2-L@IDUGDB2-L.ORG
抄送

主题
Re: [DB2-L] z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions

There is no way to run reorgs using the V8 method in DB2 9. BUILD2 is
gone and there is no way to get it back. As was mentioned if you have no
NPIs then nothing changes. [...]69_OF63D7E1C4.A9EC3F25-ON4825771E.00096B7D-4825771E.00097742@icbc.com.cn 392 45 70_AUTO: Sajad Javid is out of the office (Travel) (returning 05/11/2010)11_Sajad Javid19_sajad.javid@TCS.COM30_Sun, 9 May 2010 11:54:31 +0530355_us-ascii I am out of the office until 05/11/2010.

I will be traveling and will not be accessing emails until 11-May-2010

Note: This is an automated response to your message "DB2-L Digest - 8 May
2010 to 9 May 2010 (#2010-130)" sent on 5/9/10 10:30:00.

This is the only notification you will receive while this person is away. [...]65_OF58030548.F69177E2-ON6525771E.00233469-6525771E.0023346A@tcs.com 438 245 69_[AD] Cogito Announce formation of Cogito Software North America, Inc.13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK31_Mon, 10 May 2010 02:10:45 +0100457_US-ASCII The continued success and increasing demand for Cogito software have
justified the formation of a Cogito subsidiary in America. The company is
based in Boston, Massachusetts and is called Cogito Software North America,
Inc.

Cogito NA is a wholly owned subsidiary of Cogito Ltd, a UK based company
founded in 1989 and now celebrating over 20 years of bringing world-class
tools & utilities to IBM DB2R and CA IDMST users. [...]43_00ad01caefdd$a4d84c10$ee88e430$@demon.co.uk 684 361 123_=?utf-8?Q?=E7=AD=94=E5=A4=8D=3A_?=[DB2-L] AD: Buffer Pool Tool and Performance Wizard Demo, new Web browser based Interface21_=?GB2312?B?0e6GtA==?=21_yang.zhe@BANKCOMM.COM31_Mon, 10 May 2010 13:00:42 +0800555_utf-8 Joel:

Compared to BPA from IBM, can you tell me the advantage/disadvantage of BPT ?

鍙戜欢浜: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] 浠ｈ〃 Joel Goldstein - Responsive Systems
鍙戦佹椂闂: 2010骞5鏈7鏃 23:03
鏀朵欢浜: DB2-L@IDUGDB2-L.ORG
涓婚: [DB2-L] AD: Buffer Pool Tool and Performance Wizard Demo, new Web browser based Interface

Wednesday and Thursday, Booth 200, at IDUG/Tampa.

We are not planning demos for the initial Tuesday evening opening, because of the expected large traffic volumes. [...]44_007201caeffd$be0feec0$3a2fcc40$@bankcomm.com 1046 81 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 10 May 2010 07:14:54 +0200329_ISO-8859-1 I think David was talking about the crazy hack not the simple SCOPE
PENDING. Doing a reorg based on reorg pending is trivial. Getting all the
TPs you *wish* to reorg into REORG PENDING takes a little bit more effort!
and before anyone asks *no* I will not tell anyone how to do it! Not even
for beer! [...]64_OFD4334013.59877346-ONC125771F.001CA3D2-C125771F.001CD47D@seg.de 1128 217 18_UDF Z/os DECTODATE14_Carol Goldberg19_puli@MINDSPRING.COM31_Mon, 10 May 2010 12:59:45 -0400672_UTF-8 I have been trying to use an example I found, but can not seem to get the DECLARES to work properly. I have tried changing my delimiters in SPUFI to a /, but to no avail.

Any help would be appreciated.

Thanks,
Carol

CREATE FUNCTION DEC2DATE (
DATEDEC DECIMAL(8, 0) )
RETURNS DATE
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
BEGIN
DECLARE MYRESULT DATE ;
DECLARE INVALIDDATE CONDITION FOR '22007';
DECLARE EXIT HANDLER FOR INVALIDDATE ;
BEGIN
RETURN CAST(NULL AS DATE) ;
RETURN CAST(NULL AS DATE) ;
SIGNAL SQLSTATE '01HDI' SET MESSAGE_TEXT = 'INVALID [...]51_6630568216099074.WA.pulimindspring.com@www.idug.org 1346 242 22_Re: UDF Z/os DECTODATE14_Peter Schwarcz23_schwarcz@BIGPOND.NET.AU31_Mon, 10 May 2010 09:16:51 +1000773_UTF-8 The following will do most of what you want, except for the specific error code that you are setting.

CREATE FUNCTION
DEC2DATE
( DATEDEC DECIMAL(8, 0) )
RETURNS DATE
SPECIFIC DEC2DATE
LANGUAGE SQL
Not DETERMINISTIC
CALLED ON NULL INPUT
CONTAINS SQL
NO EXTERNAL ACTION
RETURN DATE(
SUBSTR(DIGITS(DATEDEC),1,4) CONCAT '-' CONCAT
SUBSTR(DIGITS(DATEDEC),5,2) CONCAT '-' CONCAT
SUBSTR(DIGITS(DATEDEC),7,2))
;
---------+---------+---------+---------+---------+--------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+--------

select dec2date(20090106)
from sysibm.sysdummy1 ;
---------+---------+---------+---------+---------+---------+ [...]38_001001caefcd$b54c96b0$1fe5c410$@net.au 1589 64 64_Tom Glaser is out of the office attending an off campus meeting.10_Tom Glaser25_tom_glaser@MASTERCARD.COM31_Tue, 11 May 2010 04:15:03 -0500596_US-ASCII I will be out of the office starting 05/11/2010 and will not return until
05/12/2010.

Thanks, Tom

CONFIDENTIALITY NOTICE
This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you. [...]72_OF41E05B06.A22BEDDD-ON86257720.0032D117-86257720.0032D118@mastercard.com 1654 165 36_AW: UDF # [DB2-L] UDF Z/os DECTODATE12_PUSCH Othmar27_Othmar.Pusch@IT-AUSTRIA.COM31_Tue, 11 May 2010 14:09:28 +0200876_utf-8 Hi dear Carol !

Peter sent ya an UDF-Solution based on your question ... maybe also a little bit useful simple & default date-functions too -->

IQIBROBA WSE N151142.PRIV.DB2(DATEINF1) - 01.03 Line 00000000 Col
Command ===> Scroll ==
********************************* Top of Data **************************
-- PLAY WITH SYSTEM-DATE_VALUES:
Select char(hex(current date)) from sysibm.sysdummy1 ;
Select char(current date) from sysibm.sysdummy1 ;
Select hex(current date) from sysibm.sysdummy1 ;
Select hex(hex(current date)) from sysibm.sysdummy1 ;
Select getvariable('SYSIBM.VERSION') from sysibm.sysdummy1 ;
Select getvariable('SYSIBM.SYSTEM_EBCDIC_CCSID')from sysibm.sysdummy1;
-- CALL UDF 'DEC2DATE' created by Peter Schwarcz:
Select dec2date(20100511) from sysibm.sysdummy1 ;
-- GO HOME TO OUR LORD:
Commit ; [...]63_81BB61A7ECD28B40BC5E4701B999860896356A86D5@A2XMXS1V1.A2X.LAN.AT 1820 24 10_Lee Prince13_Wayne Stevens29_wayne.stevens@DHR.ALABAMA.GOV31_Tue, 11 May 2010 08:30:27 -0400687_UTF-8 Lee Prince passed away yesterday after an extended illness. He resume included Northrop Grumman, Crowley Maritime, BMC Software, Nordstrom, and Legent. He was an Solar System Ambassador to NASA for the Jet Propulsion Laboratory. His brilliant mind will be missed.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]61_9330707142982418.WA.wayne.stevensdhr.alabama.gov@www.idug.org 1845 49 14_Re: Lee Prince12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Tue, 11 May 2010 08:07:58 -0500582_UTF-8 This is a sad loss for all of us who knew him.

Gerald Hodge
HLS Technologies, Inc.

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Wayne Stevens
Sent: Tuesday, May 11, 2010 7:30 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Lee Prince

Lee Prince passed away yesterday after an extended illness. He resume included Northrop Grumman, Crowley Maritime, BMC Software, Nordstrom, and Legent. He was an Solar System Ambassador to NASA for the Jet Propulsion Laboratory. His brilliant mind will be missed. [...]35_002001caf10a$fac45be0$f04d13a0$@com 1895 80 23_Two Java Items to Check11_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 11 May 2010 10:37:38 -0400640_UTF-8 With DB2 10 coming out and the IDUG North American Conference this week, there should be another great opportunity for further information about the new release.

One problem is that too many shops have performance problems with existing Java application and not even all the new DB2 10 features and enhancements will help solve these issues because the issues are not with DB2 but with the servers and applications delivering transaction to DB2.

Many Java application systems are having issues and here are two items to look at within your Java systems.

To read the entire article, please visit www.davebeulke.com51_5373229908667436.WA.davedavebeulke.com@www.idug.org 1976 88 14_Re: Lee Prince35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 11 May 2010 13:25:46 -0400742_utf-8 Yes, that is sad, I knew Lee at least 20 yrs..

Joel

Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com

Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at:
www.responsivesystems.com/Movie1

tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: "Gerald Hodge"
Newsgroups: bit.listserv.db2-l
To:
Sent: Tuesday, May 11, 2010 9:07 AM
Subject: Re: [DB2-L] Lee Prince [...]46_D5FADA49F0FB4251957B3CD64CCBF292@DellNotebook3 2065 63 14_Re: Lee Prince21_wfavero@attglobal.net21_wfavero@ATTGLOBAL.NET31_Tue, 11 May 2010 20:33:51 -0400674_iso-8859-1 WOW.. that's terrible.. I've know him for such a long time, even before
working at BMC... always enjoyed a good DB2 conversion with him.

He will be missed.

Willie

Original Message:
-----------------
From: Wayne Stevens wayne.stevens@DHR.ALABAMA.GOV
Date: Tue, 11 May 2010 08:30:27 -0400
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Lee Prince

Lee Prince passed away yesterday after an extended illness. He resume
included Northrop Grumman, Crowley Maritime, BMC Software, Nordstrom, and
Legent. He was an Solar System Ambassador to NASA for the Jet Propulsion
Laboratory. His brilliant mind will be missed. [...]41_380-22010531203351747@M2W121.mail2web.com 2129 23 62_Tomas Vasko/IEA/EAGA/EAG/AT ist au=?ISO-8859-1?Q?=DFer_?=Haus.11_Tomas Vasko23_tomas.vasko@GENERALI.AT31_Wed, 12 May 2010 03:32:50 +0200634_ISO-8859-1 Ich bin ab 12.05.2010 au遝r Haus und ab 17.05.2010 wieder im B黵o.
I'm out of the office from 12.05.2010 and will be back on 17.05.2010.

Ich werde Ihre Nachricht nach meiner R點kkehr beantworten.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]68_OFD63DC0FC.9D40801E-ONC1257721.0008800D-C1257721.0008800D@AT.TOP.COM 2153 278 16_db2 ver.9.1, zos37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Wed, 12 May 2010 14:34:21 +0200577_iso-8859-2 Hi all
When we tried to solve a problem with high non-accounted time for a batch application we started a lock trace (duration 1 second).

In the report we can see about 3000 lock requests on different datapages. However for one TS the lock request is always immediately followed by unlock request for the same page (about 300 times for one page in this short trace). This (for me) strange behavior occurred only for one TS (part.12, mainly inserts). The lock duration should be to a commit, but we are certain there is no more than one commit in this [...]58_0675446363E09A4EBE4CCF8D3AD7814F04547F49@MAIL1.cen.csin.cz 2432 453 20_Re: db2 ver.9.1, zos11_Mike Turner19_mike.turner@GMX.COM31_Wed, 12 May 2010 13:52:18 +0100584_iso-8859-2 [DB2-L] db2 ver.9.1, zosHi Dusan

The fact that your are doing inserts is the cause of this phenomenon. DB2 selects the preferred page for a new row, based on the clustering key, it then attempts to take a Conditional X-lock on the page. If another thread has a lock on the page, then this Conditional X-lock is immediately released rather than queued. DB2 then selects a near-by page instead of the preferred page and once again attempts to take a Conditional lock. This process repeats until a page is found that can be used for the insert without waiting for [...]44_00D088B8BF7A4C5C93CF3C45AF3CDCDC@ToshibaA300 2886 65 31_Trigger SQL Exception Capturing0_17_db46@CHRYSLER.COM31_Wed, 12 May 2010 11:00:09 -0400499_US-ASCII In stored procedures you can have handlers for sql exceptions, warnings,
etc.

But that doesn't seem possible in UDB version 8 triggers.

I have a trigger that may get a -803 on an insert using a 'current
timestamp' as one of the fields in the primary key. It "shouldn't" throw
an -803, but it does from time to time (guess processors are getting too
fast). How can I capture this without have to query the DB first in an
after delete/insert/update trigger? [...]86_OFF6C4369B.B93741A1-ON85257721.005205D2-85257721.0052683C@wkamerica.notes.chrysler.com 2952 35 43_Z/OS SQLERRD(5) calculation in syntax error14_Larry Kintisch17_LKint@VERIZON.NET31_Wed, 12 May 2010 12:01:30 -0400542_us-ascii Hi IBM team listening in:

In SQL error code -199 ILLEGAL USE OF KEYWORD ..., for example,
the SQLERRD(5) has the position where the error is detected in the
SQL statement.

I can't find a reference that explains how that position is
calculated. Can someone from the optimizer team explain how a
multi-record SPUFI input statement [for example] is parsed and what
bytes are not retained, such as line feed/return or extra
leading/trailing spaces? Does the result depend on the input Dataset LRECL? [...]40_0L2B00KT8DUQ37A0@vms173015.mailsrvcs.net 2988 88 35_Re: Trigger SQL Exception Capturing14_Peter Backlund21_BacklundDB2@TELIA.COM31_Wed, 12 May 2010 18:24:30 +02000_26_4BEAD63E.4060101@telia.com 3077 57 47_Re: Z/OS SQLERRD(5) calculation in syntax error10_Todd Burch17_toddburch@MAC.COM31_Wed, 12 May 2010 11:33:10 -0500636_US-ASCII I've been meaning to write a REXX edit macro to do this.

Todd

On May 12, 2010, at 11:01 AM, Larry Kintisch wrote:

Hi IBM team listening in:

In SQL error code -199 ILLEGAL USE OF KEYWORD ..., for example, the
SQLERRD(5) has the position where the error is detected in the SQL
statement.

I can't find a reference that explains how that position is
calculated. Can someone from the optimizer team explain how a multi-
record SPUFI input statement [for example] is parsed and what bytes
are not retained, such as line feed/return or extra leading/trailing
spaces? Does the [...]44_629951DB-5556-41CF-BE73-C2044CF2B7BC@mac.com 3135 71 47_Re: Z/OS SQLERRD(5) calculation in syntax error13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Wed, 12 May 2010 12:50:24 -0400408_utf-8 > I've been meaning to write a REXX edit macro to do this.
Me too, Todd.

By default, Larry, SPUFI takes input from 1 to 72 of your input ds.

If I recall, it includes spaces.
So, unless you override the default input spec, take the SQLERRD number
and divide by 72 and count down that number of records and look into the
next record, the remainder from the above division. [...]70_E814208E0983274198870632E8697D5B10142253@nasa-dtw-ex001.nasa.cpwr.corp 3207 99 47_Re: Z/OS SQLERRD(5) calculation in syntax error10_Todd Burch17_toddburch@MAC.COM31_Wed, 12 May 2010 14:27:36 -0500397_US-ASCII Well, I was going to look at the code first to get all the caveats,
like possible adjustments for comment stripping, and whatever else
might affect it.

But yes, that's essentially it. A few empirical tests with that
logic will quickly prove success or failure.

And I was going to check and see if SQLERRD5 was applicable to more
than -104 and -199 as well. [...]44_456E7F99-BCAC-415A-85B7-960EE629157C@mac.com 3307 25 22_Re: UDF Z/os DECTODATE14_Carol Goldberg19_puli@MINDSPRING.COM31_Wed, 12 May 2010 18:08:58 -0400603_UTF-8 Thanks Peter for solution, I also want to add if invalid date make it nulls.

I will look at handle conditions, if you have any recommendations, I would appreciate it.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]51_9407914626737193.WA.puliMindspring.com@www.idug.org 3333 27 22_Re: UDF Z/os DECTODATE14_Carol Goldberg19_puli@MINDSPRING.COM31_Wed, 12 May 2010 18:23:56 -0400630_UTF-8 I got this to work.

RETURN COALESCE( DATE(
SUBSTR(DIGITS(DATEDEC),1,4) CONCAT '-' CONCAT
SUBSTR(DIGITS(DATEDEC),5,2) CONCAT '-' CONCAT
SUBSTR(DIGITS(DATEDEC),7,2)),DATE('0001-01-01') )

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________ [...]51_2416908201925692.WA.puliMindspring.com@www.idug.org 3361 62 22_Re: UDF Z/os DECTODATE14_Carol Goldberg19_puli@MINDSPRING.COM31_Wed, 12 May 2010 18:33:42 -04001106_UTF-8 I tested with invalid date and it is coming back with a -440/. I would like a default date to be returned.

Command ===> Scroll ===> CSR
********************************* Top of Data **********************************
---------+---------+---------+---------+---------+---------+---------+---------+
SET CURRENT SQLID = 'HRDBA'/
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT DEC2DATE(20100532) FROM SYSIBM.SYSDUMMY1/
---------+---------+---------+---------+---------+---------+---------+---------+

---------+---------+---------+---------+---------+---------+---------+---------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNT408I SQLCODE = -181, ERROR: THE STRING REPRESENTATION OF A DATETIME VALUE
IS NOT A VALID DATETIME VALUE
DSNT418I SQLSTATE = 22007 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRTIM SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -6805 0 0 [...]51_3123245531528513.WA.puliMindspring.com@www.idug.org 3424 605 20_Re: db2 ver.9.1, zos37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Thu, 13 May 2010 07:19:03 +0200697_iso-8859-2 Thank you vary much for your prompt answer.
Your explanation nicely agrees with further facts (report shortened by rexx):

BZAVM1 LOCK DATAPAGE OB =BTSVW PAGE=X'000AF0'
BZAVM1 UNLOCK DATAPAGE OB =BTSVW PAGE=X'000AF0'
BZAVM1 LOCK DATAPAGE OB =BTSVW PAGE=X'000AE2'
BZAVM1 UNLOCK DATAPAGE OB =BTSVW PAGE=X'000AE2'
BZAVM1 LOCK DATAPAGE OB =BTSVW PAGE=X'016A0E' now it really takes a lock
BZAVM1 LOCK DATAPAGE OB =BTSVW PAGE=X'000AF0'
BZAVM1 UNLOCK DATAPAGE OB =BTSVW PAGE=X'000AF0'
BZAVM1 LOCK DATAPAGE OB =BTSVW PAGE=X'000AE2'
BZAVM1 UNLOCK DATAPAGE OB =BTSVW PAGE=X'000AE2'
BZAVM1 LOCK DATAPAGE OB =BTSVW PAGE=X'016A0E' now it really takes a lock [...]58_0675446363E09A4EBE4CCF8D3AD7814F04547F4B@MAIL1.cen.csin.cz 4030 733 20_Re: db2 ver.9.1, zos11_Mike Turner19_mike.turner@GMX.COM31_Thu, 13 May 2010 09:15:45 +0100582_iso-8859-2 [DB2-L] db2 ver.9.1, zosHi Dusan

Here are some thoughts based on your feedback.

1. Member Cluster can be used for simple or partitioned tablespaces in V8, but not for segmented.

2. It might be worth investigating which process is holding the blocking lock on page X'000AF0'. It does not have to be the inserting process. Any reader with an S-lock will block the inserter's attempt to get the X-lock. The fact that a similar TS with similar processing does not have problems may indicate the presence of another process accessing the problem TS. [...]44_E639F5B81BF745179562ACBDF9BEF4F5@ToshibaA300 4764 52 47_Re: Z/OS SQLERRD(5) calculation in syntax error14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 13 May 2010 20:04:30 +1000344_US-ASCII It is the application that strips comments, line feeds etc. DB2 reports on the position in the
statement handed over for the PREPARE (or EXECUTE IMMEDIATE) and has no way of
knowing what has (or has not) been stripped. Code I write includes an "error about here"
message to display the text around the position reported. [...]49_4BEC5B4E.31669.57FB7B3B@jacampbell.acslink.net.au 4817 826 20_Re: db2 ver.9.1, zos37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Thu, 13 May 2010 14:50:13 +0200409_iso-8859-2 Thank you vary much for your prompt suggestions. I am going to use them in new investigations
For the time being:
ad 2: all others processes used "selects with ur" (TSLockMode=N in Plan_Table) so I hope they could not block any page for insert
ad 3: all data are now deleted from this TS, so I can't use DSN1PRNT (next time)
By the way, please, how DB2 works with space map pages? [...]58_0675446363E09A4EBE4CCF8D3AD7814F04547F52@MAIL1.cen.csin.cz 5644 1036 20_Re: db2 ver.9.1, zos11_Mike Turner19_mike.turner@GMX.COM31_Thu, 13 May 2010 15:01:16 +0100605_iso-8859-2 [DB2-L] db2 ver.9.1, zosHi Dusan

You are correct that selects 'with ur' should not block the inserts. Even if the rows have been deleted from the tablespace DSN1PRNT formatted output will still tell you the type of page: data or space map (or header or dictionary).

The process of using a conditional lock and inserting in a page other than the preferred page is a normal DB2 mechanism. It is designed to avoid insert delays at the cost of less efficient clustering (which can affect select performance). I suggest that you do not change your tablespace just because this is [...]44_EF89029C51004C94B022B2C65BCDCBFC@ToshibaA300 6681 67 64_DB2 for z/OS V8 - problems running test UDF calling Java program12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Thu, 13 May 2010 15:37:11 +0100444_ISO-8859-1 I'm trying to run the TESTUDF sample from www.db2expert.com and I've got as
far as installing the jar and the UDF and setting up the WLM environment to
run it in. When I try to run it I get -

+DSNX961I DSNX9WLJ ATTEMPT TO PERFORM JNI FUNCTION CreateJavaVM 186
FAILED FOR STORED PROCEDURE . . SSN= DB2B PROC= DB2JWLM ASID=
0107 CLASS= METHOD= ERROR INFO= JAVAENV FILE DOES NOT SPECIFY
DB2_HOME OR JCC_HOME. [...]60_AANLkTim7MEzJCza6oYce9C2-crI7ppqGjadXZEOIlSwN@mail.gmail.com 6749 187 22_Re: UDF Z/os DECTODATE14_Larry Kintisch17_LKint@VERIZON.NET31_Thu, 13 May 2010 10:43:40 -0400565_us-ascii Hi Carol,
I see no problem with the code but I'm going to suggest you try
three things to see if it clears the problem.
First use an alternate delimeter for SPUFI like '\' that is never
part of SQL syntax.
Second, besure the right end of the SPUFI input records don't have
garbage. [Once I deleted a space and carried left a "record number"
byte into position 72 and never noticed it!]
If it still does not work, try removing the blanks before the ';'
at the end of 2 of the DECLAREs. [You never know--a human wrote the
[...]40_0L2D00F6Y4X0XOE0@vms173001.mailsrvcs.net 6937 285 22_Re: UDF Z/os DECTODATE14_Peter Backlund21_BacklundDB2@TELIA.COM31_Thu, 13 May 2010 18:02:39 +02000_26_4BEC229F.9050406@telia.com 7223 170 56_Baltimore Washington DB2 Users' Group June 2010 Meeting!14_Henry L Nalven25_henry.nalven@MARRIOTT.COM31_Thu, 13 May 2010 13:03:55 -0400545_UTF-8 The Baltimore/Washington DB2 Users' Group
Meeting Schedule for June 9, 2010 (Wednesday)

When?
Wednesday, June 9, 2010 9:00 AM-3:30 PM
(Registration at 8:30 AM)

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

Meeting Fees (please note changes):
Pre-registered members $30
Pre-registered non-members $40
At-the-door for members $40
At-the-door for non-members $50

To pre-register, please mail the appropriate fee with attendee's and employer's name to: [...]57_1925001498979054.WA.henry.nalvenmarriott.com@www.idug.org 7394 38 18_IDUG Radio Silence7_Ed Long19_rdhm99a@PRODIGY.NET31_Thu, 13 May 2010 15:52:28 -0700569_us-ascii So About IDUG?

Edward Long

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L43_830908.97642.qm@web80204.mail.mud.yahoo.com 7433 82 14_Re: Threadsafe13_Irwin Deutsch28_ideutsch@PRINCESSCRUISES.COM31_Thu, 13 May 2010 16:08:36 -0700608_US-ASCII Threadsafe is not so much for CPU savings as it is for making use of
multiprocessor. While non-threadsafe tasks would queue up on QR TCB,
threadsafe ones would get their own open (L8) TCBs and could stay there as
long as they issued threadsafe commands. If they issued non-threadsafe
ones, mixed with SQL calls there would be lots of TCB switching, which
could significantly increase elapsed time depending on waits for the QR
and L8 TCBs. I don't think CPU savings is the main factor here, though
there's some overhead in changing TCBs. You'd have to look at CICS SMF
[...]66_OF6058716B.75388A80-ON88257722.007E4F6A-88257722.007F215C@ux2b.pcb 7516 115 22_Re: IDUG Radio Silence13_BOND Victor A30_Victor.A.BOND@ODOT.STATE.OR.US31_Thu, 13 May 2010 16:11:53 -0700702_US-ASCII Just a guess - International DB2 Users Group ?

Sorry, Friday is coming and it has been a long week.

________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long
Sent: Thursday, May 13, 2010 3:52 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] IDUG Radio Silence

So About IDUG?

Edward Long

________________________________

IDUG Mentors - A rewarding experience for everyone!


The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
[...]71_D3B03C67566CCC4BA0527224221B814406AFE61D@EX-SALEMREV-4.odot.state.or.us 7632 62 22_Re: IDUG Radio Silence21_wfavero@attglobal.net21_wfavero@ATTGLOBAL.NET31_Thu, 13 May 2010 19:47:11 -0400429_iso-8859-1 I think I may be the mouthy one about stuff like "what's going on" at
conferences. This year I did not attend IDUG. Maybe Chris Eaton will
have something on his blog about it soon.

Willie

Original Message:
-----------------
From: Ed Long rdhm99a@PRODIGY.NET
Date: Thu, 13 May 2010 15:52:28 -0700
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] IDUG Radio Silence

So About IDUG? [...]42_380-220105413234711505@M2W111.mail2web.com 7695 1207 20_Re: db2 ver.9.1, zos37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Fri, 14 May 2010 02:50:48 +0200414_iso-8859-2 Thank you for nice explanation.
ad 1) we are "deleting" data by recovery to a "empty" copy, so I am afraid it is not possible to identify data / space map pages
ad 2) but these conditional lock and unlock pages are all PROP=Y, so we suspect them to be the reason of our high non- accounted time
clustering on this TS has little meaning to us, the rows are there for a very short time only [...]58_0675446363E09A4EBE4CCF8D3AD7814F05D847F6@MAIL1.cen.csin.cz 8903 148 18_IDUG Radio Silence10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 14 May 2010 13:37:11 +0200494_US-ASCII Hi Ed

I suppose you're talking about IDUG NA....and from what I heard it's
going quite well, more attendees (I haven't any number for now) and good
presentations. We have some 'agents' there and

probably the silence is because they are having fun......

BUT if you want to hear something about EUROPEAN IDUG stay tuned: GRID
will be available soon and it's rich, many DB2 10 presentations (I believe
more than IDUG NA), panels and Special Interest [...]66_OF2E3EFFE9.FF616D91-ONC1257723.00248FB6-C1257723.003FCA42@cesve.it 9052 83 22_Re: IDUG Radio Silence10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 14 May 2010 14:45:26 +0200932_US-ASCII .....and if your boss is asking for more informations about European
Conference see the IDUG justification kit for some suggestions:

http://www.idug.org/idug-justification-toolkit.html

Massimo Scarpa
IDUG EU 2010 Conference Planning Committee
Marketing Team

*******************************************************************************************************************************************
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org

- IDUG's law: To be a happy DB2er you don't need to attend IDUG EU 2010,
but it helps.
- Theory of next step: After you attended IDUG to convince your
programmers there's only a large hammer.
******************************************************************************************************************************************* [...]66_OFE439BCF8.03D11C1D-ONC1257723.0045AD7A-C1257723.004609D7@cesve.it 9136 435 75_Re: [SPAM] DB2 for z/OS V8 - problems running test UDF calling Java program16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Fri, 14 May 2010 10:02:24 -0500365_us-ascii I suspect that the WLM environment you are using is not properly configured.
Perhaps DB2 requires the diver as default for the address space even though
this specific procedure uses no DB2. You can also try removing the import
from the Java file, but it's been so long since I wrote that routine I can't
remember if it is required or not. [...]35_00b101caf376$89222fd0$9b668f70$@com 9572 39 32_rules of engagement for UDF Z/os14_Carol Goldberg19_puli@MINDSPRING.COM31_Fri, 14 May 2010 12:13:39 -0400405_UTF-8 I was hoping to get answers on WHAT IS and WHAT IS NOT Allowed on Z/OS for UDF's.

Can Condition HANDLERS be used in Z/os UDFs?

can you use a COALESCE to handle errors , ie invalid date , give a default date.

When purusing the internet for information , these is alot of information that is LUW only, but it does not specifically state that only works in a LUW environment. [...]51_5551739006022934.WA.pulimindspring.com@www.idug.org 9612 85 36_Re: rules of engagement for UDF Z/os14_Peter Backlund21_BacklundDB2@TELIA.COM31_Fri, 14 May 2010 18:39:27 +02000_24_4BED7CBF.80000@telia.com 9698 1357 20_Re: db2 ver.9.1, zos12_Rao, Diwakar19_Diwakar.Rao@FMR.COM31_Fri, 14 May 2010 12:39:38 -0400611_iso-8859-2 If "clustering on this TS has little meaning "

Try using the APPEND option on the table, DB2 will ignore the clustering and eliminate the search to insert on a preferred page, there by reducing number of lock/unlock requests, and rows are appended to the end of the table.

APPEND works well when Inserts are single threaded (batch job for instance) and may not perform when parallel workloads try to insert into the table at the same time.(you will see high lock/latch) If you have parallel workload inserting at the sametime and run data sharing, consider creating the table with [...]67_C40E7C2C86F2544E8F6A4E2C3845FD940FE7CAB6@MSGBOSCLE2WIN.DMN1.FMR.COM 11056 1252 28_FW: [DB2-L] db2 ver.9.1, zos37_=?iso-8859-2?B?UG9zcO25aWwgRHW5YW4=?=17_DPospisil@CSAS.CZ31_Fri, 14 May 2010 18:51:13 +0200638_iso-8859-2 Perhaps MAXROWS 6 should reduce LOCK/UNLOCK problem?

Regards
dusan

________________________________

From: Posp砉il Du筧n
Sent: Friday, May 14, 2010 2:51 AM
To: 'IDUG DB2-L'
Subject: RE: [DB2-L] db2 ver.9.1, zos

Thank you for nice explanation.
ad 1) we are "deleting" data by recovery to a "empty" copy, so I am afraid it is not possible to identify data / space map pages
ad 2) but these conditional lock and unlock pages are all PROP=Y, so we suspect them to be the reason of our high non- accounted time
clustering on this TS has little meaning to us, the rows are there [...]58_0675446363E09A4EBE4CCF8D3AD7814F04547F5B@MAIL1.cen.csin.cz 12309 31 55_Determining the RUNLIB LOAD library for a bound program11_Mark Kerner18_Mark.Kerner@PB.COM31_Fri, 14 May 2010 15:53:54 -0400570_UTF-8 We were getting -818's after we erroneously bound DSNTIAUL over an existing RUNLIB that was being shared by a second DB2 subsystem. We wound up rebinding the current version of DSNTIAUL from DSNTEJ2A. This led me to think that I might have been able to use a previous version of plan DSNTIB*1 if I knew what RUNLIB it was bound into. Querying SYSDBRM gives you the name of the DBRMLIB library that was used for a BIND. Is there anything you can query or scan to determine the RUNLIB.LOAD library that a program/plan was bound into without locating the actual [...]50_8016817595759212.WA.Mark.Kernerpb.com@www.idug.org 12341 41 59_Re: Determining the RUNLIB LOAD library for a bound program10_Joe Geller21_joerg6666@HOTMAIL.COM31_Fri, 14 May 2010 16:19:43 -0400491_UTF-8 A program is not bound into a load library. The actual compile/lked is not a the DB2 process.
The pre-compile produces the DBRM and modified source. Bind produces the DB2 Package,
compile/lked produces the load module. The timestamp is in the DB2 DBRM & Package and
in the load module. If you know the possible load libraries you can
browse the member and match up the timestamp with that in the package to determine if
you are looking at the corresponding libary. [...]53_2616863947307049.WA.joerg6666hotmail.com@www.idug.org 12383 65 59_Re: Determining the RUNLIB LOAD library for a bound program10_Todd Burch17_toddburch@MAC.COM31_Fri, 14 May 2010 15:22:46 -0500474_US-ASCII Not really. Two different critters.

DSNTIAUL is linkedited into a load lib, a step that happens quite
separately from BINDing.

Just to be clear, are you using the term "bound" as the past tense of
a DB2 BIND, or as the past tense of using the Program Binder?

You could, given enough time and inclination, locate the consistency
token generated by the DB2 precompiler, via the listing, to match up
with the token from the DBRM. [...]44_6CD3B4E6-A603-43A5-BF9B-4CBD557EB0AD@mac.com 12449 33 24_List of views on aliases18_valeriet6@juno.com18_valeriet6@JUNO.COM29_Fri, 14 May 2010 21:49:36 GMT884_windows-1252 Does anyone have SQL that will list views created on aliases? It seems we have several, and we need a report.

Thanks

____________________________________________________________
Penny Stock Jumping 2000%
Sign up to the #1 voted penny stock newsletter for free today!
http://thirdpartyoffers.juno.com/TGL3141/4bedc5e759fb345e558st06vuc

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
[...]45_20100514.174936.6948.0@webmail10.vgs.untd.com