1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l July 2007, week 2 2 121 57_Re: Combining Two Subsystems - Moving Data? (V7 for z/OS)14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Sun, 8 Jul 2007 09:55:49 -0500337_- When I read the original question on the 6th of July what instantly went through my head was the old set of questions:

Does it need to be said? Does it need to be said now? Does it need to be said by me?

I thought the first two questions should be answered yes but was hoping the third question would be a clear no. [...] 124 265 40_Re: [zOS 1.6 db2 v7] - Triggers - update25_Thanikachalam Sundarrajan21_billysundar@YAHOO.COM30_Sun, 8 Jul 2007 10:46:31 -0700544_ascii Brian,

The session variables GETVARIABLE('SYSIBM.PLAN_NAME') and GETVARIABLE('SYSIBM.PACKAGE_NAME') provide information on the plan/package. However, inside of a trigger, the package name changes to the trigger package name and are probably not useful in your situation.

For distributed apps (where PLAN = DISTSERV) the client variables can be set using JDBC Universal driver methods mentioned by Troy. For RRS apps, you can use the SET_CLIENT fuctions to set the value. The values map to fields in the DSNDQWHC macro. [...] 390 273 53_[AD] Compuware July 2007 Technical Spotlight Sessions17_Dell'Anno, Aurora32_Aurora.DellAnno@UK.COMPUWARE.COM30_Mon, 9 Jul 2007 08:15:49 +0100363_US-ASCII Hi All,

please find below the July schedule for Spotlight Sessions, for all you Compuware users/customers out there.

Remember, our customers are required to register for FrontLine to access these sessions, the July 10 Spotlight is a great opportunity to register and gain an insight and understanding of how FrontLine can benefit you. [...] 664 100 33_Re: Z/os DB2 V8: database stopped13_Mertens, Bart18_bart.mertens@CZ.NL30_Mon, 9 Jul 2007 11:27:50 +0200378_us-ascii Hello Laurens,

If the command was entered from tso you can use the following method. Don't know is this is the way to do it but it works for me...

Use dsn1logp to print out the db2 transaction log. Look for TYPE CHECKPOINT records. You will find a user "SYSOPR" who issued the command followed by another TYPE CHECKPOINT record with the "real" user. [...] 765 125 81_CALL db2XUfunctions.XMLUPDATE stored procedure from A Java Application or Trigger13_Essam Mansour23_essam.mansour@GMAIL.COM30_Mon, 9 Jul 2007 12:27:12 +0100752_ISO-8859-1 Hi everybody,

I am using db2XUfunctions.XMLUPDATE stored procedure. the signature of XMLUPDATE stored procedure is

XMLUPDATE(IN COMMANDSQL VARCHAR(32000), IN QUERYSQL VARCHAR(32000), IN UPDATESQL VARCHAR(32000), OUT errorCode INTEGER, OUT errorMsg VARCHAR(32000))

When I call the stored procedure from the SQl prombet or Command Editor, it is working. the call is ================================================== Call db2XUfunctions.XMLUPDATE ( ' 2007-07-05 13:36:13.336092 registered ', [...] 891 482 44_Re: The Log and Log Analysis Tool (LAT) Z/OS15_Gaston, Raymond17_GastonRay@ORU.COM30_Mon, 9 Jul 2007 10:32:52 -0400451_us-ascii

>> Triggers to capture changes ... problem where doyou put the captured information, inserting into a DB2 table real time is a very expensive option. >>

We went the "trigger route" for some applications, because of the question: HOW LONG DO YOU KEEP THE LOGS AROUND??? At this shop the "log tapes" are only kept for a few weeks. If the issue of "who changed what a month ago" comes up, we would have complications !!!!!!! [...] 1374 302 40_Re: [zOS 1.6 db2 v7] - Triggers - update7_Ed Long19_rdhm99a@PRODIGY.NET30_Mon, 9 Jul 2007 08:14:15 -0700595_iso-8859-1 Interesting problem. What if you add a column to the original table of Char(16) or so. Everyone who inserts to the table is required to plug in their identification(correlation Id). You can default the column to UnKnown or something equally obvious. You could even do a UDF or something that rejects the insert attempt if its not filled in. While this approach is not as absolute as an IFI based solution, it will also be a lot cheaper to develop and operate. I have seen companies use similar designs for EMP (Event Monitoring Points) management in CICS with excellent success. [...] 1677 78 53_Combining Two Subsystems - Moving Data? (V7 for z/OS)17_McCormack, Mark A27_mamccormack@STATESTREET.COM30_Mon, 9 Jul 2007 11:20:23 -0400318_us-ascii Jay,

Sally Mir gave a presentation at IDUG on this topic a few years ago. I suggest you contact her. Perhaps she can send you a copy of her presentation; I do not have a copy myself. She contributes to this list. You can find contact info for her in the archives. Her most recent post was in May. [...] 1756 32 93_SV: [DB2-L] CALL db2XUfunctions.XMLUPDATE stored procedure from A Java Application or Trigger14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK30_Mon, 9 Jul 2007 17:22:10 +0200435_iso-8859-1 For problem 2, I guess you should remove the line "values(" (and the corresponding ")" at then end).

For problem 1, look up SQLCODE -16132 in Chapter 4 of the v9 "Codes" reference (GC18-9843, for z/OS) or Part 2, Chapter 2 of the "Message Reference" (SC10-4239, for L/U/W). I guess it will be a general XML parse error, so check for any unclosed tag or any invalid characters (maybe the end-of-line characters?) [...] 1789 115 57_Re: Combining Two Subsystems - Moving Data? (V7 for z/OS)0_22_sally.mir@WACHOVIA.COM30_Mon, 9 Jul 2007 11:22:37 -0400438_US-ASCII We use a method that was described in my IDUG NA2005 presentation. It requires no additional vendor products. We have used this process to collapse many subsystems (I've lost count at this point) into data sharing groups.

It basically involves generating the DDL for all the objects (in the correct order so as to preserve OBIDs, etc.) and renaming the VSAM datasets. Then a REPAIR is done to update DBID, PSID, etc. [...] 1905 174 57_Re: Combining Two Subsystems - Moving Data? (V7 for z/OS)11_Jay Reavill23_Jay.Reavill@CERTEGY.COM30_Mon, 9 Jul 2007 11:34:38 -0400517_us-ascii Thanks Sally! This sounds like it's exactly what we're looking for! I'd love to get my hands on the presentation! As it turns out, what's driving this is a move to data sharing as well.

Thanks again! Jay

------------------------------------------------------------------ Jay Reavill DBA Fidelity National Information Services, Inc. 11601 Roosevelt Blvd. St. Petersburg, FL. 33716 Office (727) 227-2144 Jay.Reavill@fnf.com ------------------------------------------------------------------ [...] 2080 162 57_Re: Combining Two Subsystems - Moving Data? (V7 for z/OS)13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Mon, 9 Jul 2007 09:16:11 -0700647_us-ascii Thanks for the link, Avram. It was very interesting reading. What amazes me is that the DBA apparently did not expect to get caught.

I do not agree that DSN1COPY is not auditable. If the process is adequately documented, and appropriate controls enforced, why not use whatever tools make the most sense? One control I advocate that makes me very unpopular at change control discussions is division of responsibilities -- remove DBA access to the physical objects, and let the storage administrator run DSN1COPY. Better yet, remove everyone's access to nearly everything in production, and let production control implement all [...] 2243 58 57_Re: Combining Two Subsystems - Moving Data? (V7 for z/OS)13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Tue, 10 Jul 2007 08:38:36 +0100494_us-ascii Removing access to the VSAM clusters? Wow, remind me never to come work for you! Occasionally I need to delete/define LDS clusters and not being able to forces me to get inventive - probably not a good idea, but hey, what are you gonna do? Wait half an hour for the right people to grant you access/regrant you access when they get it wrong? Nah, I'd rather have the access all along. But then I would say that; Auditors (and those that have been stung) might see it differently. [...] 2302 31 48_Akhilesh MITTAL/ITD GLT/HSDI/HSBC is on Training15_Akhilesh Mittal25_akhileshmittal@HSBC.CO.IN31_Tue, 10 Jul 2007 13:21:08 +0530611_US-ASCII I will be out of the office starting 10/07/2007 and will not return until 17/07/2007.

I am on Training starting 9th till 13th July, will have limited access to emails.



----------------------------------------- ******************************************************************* This e-mail is confidential. It may also be legally privileged. If you are not the addressee you may not copy, forward, disclose or use any part of it. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return e-mail. [...] 2334 59 44_Re: The Log and Log Analysis Tool (LAT) Z/OS19_Cuneyt Goksu (.NET)19_cuneytgoksu@USA.NET31_Tue, 10 Jul 2007 11:27:46 +0300446_ISO-8859-1 Bill,

There is a product called DBARS from SPR http://www.sprdb2.com/index.htm

It's an Audit tool and also records SELECTs and host variables..

FYI, Cuneyt GOKSU



-----Original Message----- From: Bill Cummins [mailto:brcummin@ILSTU.EDU] Sent: Friday, July 06, 2007 5:06 PM Subject: The Log and Log Analysis Tool (LAT) Z/OS



For a set of tables, I’d like to capture “all” activity. [...] 2394 46 20_Trace class overhead10_sami bitan20_sami.bitan@GMAIL.COM31_Tue, 10 Jul 2007 13:28:13 +0200608_ISO-8859-1 Hi,

On my DB2 instance, I have defined several traces in order to collect various information. According to the DB2 Admin. Guide: "Monitor class 2 is equivalent to accounting class 2 and results in equivalent overhead." (Administration Guide v.8, page 1166) Does this mean that the same work is being done both for the Monitor class 2 and for the Accounting class 2, meaning that only the information will be directed to two different output destinations? Or are we getting a double workload overhead? What happens if the two traces are directed to the same output (such as SMF), will [...] 2441 204 97_Re: SV: [DB2-L] CALL db2XUfunctions.XMLUPDATE stored procedure from A Java Application or Trigger13_Essam Mansour23_essam.mansour@GMAIL.COM31_Tue, 10 Jul 2007 13:02:13 +0100372_ISO-8859-1 Dear Peter,

Thank you very much.

Regarding the 2nd problem "Calling the Stored Procedure (SP) from a trigger": Note: the stored proedure is working fine, when I call it from the SQL Command Editor.

I have followed your advice. I got an error which is

SQL0418N A statement contains a use of a parameter marker that is not valid. [...] 2646 36 21_DB2 utilities V8 z/os0_21_BudGreenman@ONGOV.NET31_Tue, 10 Jul 2007 08:58:24 -0400324_US-ASCII Can the V9 utilities be used for a V8 database? We are in the process of upgrading to V8 and we were wondering if we can order the v9 utilities thereby saving a future step.

Bud Greenman Applications Programming Manager

--------------------------------------------------------------------------------- 2683 49 12_Re: DSNACCOR13_Marcus Davage29_marcus.davage@LLOYDSTSB.CO.UK31_Tue, 10 Jul 2007 07:59:42 -0500639_- Hi,

Thanks for your Rexx sample. I've just tried it out and got the following messages:

EXECSQL CALL returned SQLCODE = 0 LASTSTATEMENT=EXEC SQL PREP RETURNCODE =12 ERRORMSG =***SQL e IFCARETCODE =0 IFCARESCODE =0 EXCESSBYTES =0 LOC1=0

LOC2=0

DSNT408I SQLCODE = -482, ERROR: THE PROCEDURE DSNACCOR RETURNED NO LOCATORS DSNT418I SQLSTATE = 51030 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXELOC SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -320 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFEC0' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION [...] 2733 171 25_Re: DB2 utilities V8 z/os14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 10 Jul 2007 14:13:01 +0100379_us-ascii Hi Bud

If by "utilities" you mean "reorg, "copy" etc etc then so far as I know, these are installed when you install DB2. Although they are a separate thing you have to BUY, they are not a physically separate thing you have to install

So, the answer to your question is "no", but then there isn't a "future step" so you are not losing out in any case [...] 2905 37 12_Re: DSNACCOR13_Marcus Davage29_marcus.davage@LLOYDSTSB.CO.UK31_Tue, 10 Jul 2007 08:01:48 -0500671_-

Hi,

Thanks for your Rexx sample. I've just tried it out and got the following messages:

EXECSQL CALL returned SQLCODE = 0 LASTSTATEMENT=EXEC SQL PREP RETURNCODE =12 ERRORMSG =***SQL e IFCARETCODE =0 IFCARESCODE =0 EXCESSBYTES =0 LOC1=0 LOC2=0 DSNT408I SQLCODE = -482, ERROR: THE PROCEDURE DSNACCOR RETURNED NO LOCATORS DSNT418I SQLSTATE = 51030 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXELOC SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -320 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFEC0' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION [...] 2943 153 38_Antwort: [DB2-L] DB2 utilities V8 z/os11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 10 Jul 2007 15:07:33 +0200539_iso-8859-1 just for fun I ran a RUNSTATS on a V8 subsystem but with the V9 LOADLIB. Result is

DSNU001I DSNUTILB - RELEASE DEPENDENT MODULE DSNUT810 WAS NOT FOUND DSNU016I DSNUTILB - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E40018'

So for a simple runstats it looks like the answer is no!





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de [...] 3097 103 47_Re: z/OS v7 Declared Temporary Database naiming19_Killian, Richard R.27_Dick.Killian@ENERGYEAST.COM31_Tue, 10 Jul 2007 10:01:31 -0400656_US-ASCII Avram and Phil, thank you for the advice, it's just what I needed.

Dick Killian 585-771-6049(W) 585-329-7813(C) -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Avram Friedman Sent: Friday, July 06, 2007 2:58 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] z/OS v7 Declared Temporary Database naiming

Dick, One consideration in choosing a name is Requirements for any future data sharing where Each member should have its own TEMP database TEMP database buffer pool should be diffrent than the WORK buffer pool. Neither the TEMP or WORK should be a group buffer pool. [...] 3201 287 25_Re: DB2 utilities V8 z/os12_Troy Coleman29_troycci@COLEMANCONSULTING.COM31_Tue, 10 Jul 2007 09:06:12 -0500422_us-ascii I would say no. There are features that are used in the utilities that rely on you being on the correct version of the database.





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/ [...] 3489 45 13_SQL Question.19_Cuneyt Goksu (.NET)19_cuneytgoksu@USA.NET31_Tue, 10 Jul 2007 17:36:55 +0300478_iso-8859-9 Hi,

Is it possible to write a single SQL for the following requirement?

Here is the data;

NO NAME ---------- ---------- 20 SMITH 30 ALLEN 30 WARD 20 JONES 30 MARTIN 30 BLAKE 10 CLARK 20 SCOTT 10 KING 30 TURNER 20 ADAMS 30 JAMES 20 FORD 10 MILLER

Required Output.

NO NAMES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD [...] 3535 69 17_Re: SQL Question.11_Moe Safiari26_msafiari@EMAIL.ARIZONA.EDU31_Tue, 10 Jul 2007 08:13:05 -0700615_iso-8859-9 Try somthing like:

SELECT A.EMPNO, A.FIRSTNME FROM EMP A WHERE A.FIRSTNME <> (SELECT B.FIRSTNME FROM EMP B WHERE A.EMPNO = B.EMPNO);

----- Original Message ----- From: "Cuneyt Goksu (.NET)" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, July 10, 2007 7:36 AM Subject: [DB2-L] SQL Question.



> Hi, > > Is it possible to write a single SQL for the following requirement? > > Here is the data; > > NO NAME > ---------- ---------- > 20 SMITH > 30 ALLEN > 30 WARD > 20 JONES > 30 MARTIN > 30 BLAKE > 10 CLARK > 20 SCOTT > 10 [...] 3605 79 17_Re: SQL Question.14_Andy Lankester26_alankester@CDBSOFTWARE.COM31_Tue, 10 Jul 2007 10:33:40 -0500446_windows-1250 If there is a maximum of x names with the same number then an x-way outer join would do it.

Andy Lankester

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cuneyt Goksu (.NET) Sent: Tuesday, July 10, 2007 3:37 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] SQL Question.

Hi,

Is it possible to write a single SQL for the following requirement? [...] 3685 301 17_Re: SQL Question.10_Doyle Mark26_Mark.Doyle@QBEAMERICAS.COM31_Tue, 10 Jul 2007 10:32:28 -0500502_us-ascii -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cuneyt Goksu (.NET) Hi,

Is it possible to write a single SQL for the following requirement?

Here is the data;

NO NAME ---------- ---------- 20 SMITH . . . . 10 MILLER

Required Output.

NO NAMES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD [...] 3987 38 25_Re: DB2 utilities V8 z/os13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Tue, 10 Jul 2007 11:24:17 -0500684_ISO-8859-1 Troy and Phil are correct. The IBM utilities are release (version) dependent. They can only run with the version they are designed for.

Willie

BudGreenman@ONGOV.NET wrote: > > Can the V9 utilities be used for a V8 database? > We are in the process of upgrading to V8 and we were wondering if we > can order the v9 utilities thereby saving a future step. > > Bud Greenman > Applications Programming Manager > --------------------------------------------------------------------------------- > 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 [...] 4026 113 57_Re: Combining Two Subsystems - Moving Data? (V7 for z/OS)13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 10 Jul 2007 09:36:21 -0700558_us-ascii Raymond, you said: >But then I would say that; Auditors (and those that have been stung) might see it differently.

That's exactly what I was commenting on, people who have been stung. I don't advocate removing access to VSAM clusters to everyone -- that might not work very well in small shops that do not have a group devoted to storage management. I just used it as an example of controls a company might wish to put in place after (or to prevent) an incident like the one that happened at Certegy. But it's really not a big deal. How [...] 4140 91 17_Re: SQL Question.11_John Miller26_John.L.Miller@ATCOITEK.COM31_Tue, 10 Jul 2007 12:50:25 -0600487_us-ascii The following recursive statement will also work:

WITH TEMP(KEYNO, LIST) AS (SELECT KEYNO, RTRIM(NAME) FROM TBL UNION ALL SELECT TBL.KEYNO, TEMP.LIST CONCAT ',' CONCAT RTRIM(TBL.NAME) FROM TEMP, TBL WHERE TEMP.KEYNO = TBL.KEYNO AND LOCATE(RTRIM(TBL.NAME), TEMP.LIST) = 0 ) SELECT B.KEYNO, MIN(B.LIST) FROM (SELECT KEYNO, MAX(LENGTH(LIST)) AS LEN FROM TEMP GROUP BY KEYNO) A, TEMP B WHERE A.KEYNO = B.KEYNO AND LEN = LENGTH(B.LIST) GROUP BY B.KEYNO ORDER BY B.KEYNO; [...] 4232 13 21_Type 4 drivers (z/OS)12_Eric Pearson19_eric.pearson@53.COM31_Tue, 10 Jul 2007 13:51:17 -0500626_- Anybody running the type 4 drivers? Good news? Bad news? Any difficulty managing the HFS stuff for multiple subsystems on multiple LPARS??

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 4246 71 25_Re: Type 4 drivers (z/OS)14_Julian Stuhler27_julian.stuhler@TRITON.CO.UK31_Tue, 10 Jul 2007 20:12:19 +0100608_iso-8859-1 Hi Eric

I assume you mean the DB2 Universal Driver (aka JCC driver) distributed with V8? This can support both type 2 and type 4 connections depending on the connection properties.

I've worked with a number of customers who are using the JCC driver and provided you stay up to date on maintenance and are realistic in your performance expectations there are no major issues that I am aware of. If you're in a data sharing environment, its possible to share the HFS structures across multiple members which makes the maintenance side of things a little easier. Otherwise, it's a [...] 4318 75 25_Re: Type 4 drivers (z/OS)23_Lazowski, James S (Jim)34_Jim.Lazowski@NAV-INTERNATIONAL.COM31_Tue, 10 Jul 2007 14:25:50 -0500456_us-ascii Haven't had any problems with the UVD's, but handling upgrades can be more difficult.

This is something we do to separate out subsystems in the defs to make it easier to swap in/out different levels. I haven't really found a best way yet.

For batch/telnet/omvs sessions put uvd_db2x_env in profile or .profile for batch (where db2x is the subsystem name)....works different for datasources See break down of uvd_db2x_env below. [...] 4394 28 29_DB2 Restrict On Drop question14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Tue, 10 Jul 2007 12:43:08 -0700631_iso-8859-1 Where is the flag value is set when the following SQL is issued? SYSTABLES? If so what field?

ALTER TABLE owner.tblname DROP RESTRICT ON DROP;

--------------------------------------------------------------------------------- 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 4423 101 33_Re: DB2 Restrict On Drop question24_Gopalakrishnan, Sreejith31_Sreejith.Gopalakrishnan@FMR.COM31_Tue, 10 Jul 2007 15:49:58 -0400481_us-ascii CLUSTERTYPE. 'Y' means RESTRICT ON DROP applies. blank means it doesn't.

Sreejith Gopalakrishnan Fidelity Investments



_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ramon Santiago Sent: Tuesday, July 10, 2007 3:43 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 Restrict On Drop question



Where is the flag value is set when the following SQL is issued? SYSTABLES? If so what field? [...] 4525 66 33_Re: DB2 Restrict On Drop question13_FRITZ LEBLANC27_FRITZ.LEBLANC@SALLIEMAE.COM31_Tue, 10 Jul 2007 15:50:14 -0400793_US-ASCII It's in SYSTABLES and is called CLUSTERTYPE.

>>> Ramon Santiago 07/10/07 3:43 PM >>> Where is the flag value is set when the following SQL is issued? SYSTABLES? If so what field?

ALTER TABLE owner.tblname DROP RESTRICT ON DROP; --------------------------------------------------------------------------------- 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 This E-Mail has been scanned for viruses. [...] 4592 48 33_Re: DB2 Restrict On Drop question14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Tue, 10 Jul 2007 13:00:15 -0700524_iso-8859-1 Thanks....late in the day and my mind is going blank.

"Gopalakrishnan, Sreejith" wrote: CLUSTERTYPE. 'Y' means RESTRICT ON DROP applies. blank means it doesn't.

Sreejith Gopalakrishnan Fidelity Investments



--------------------------------- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ramon Santiago Sent: Tuesday, July 10, 2007 3:43 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 Restrict On Drop question [...] 4641 42 33_Re: DB2 Restrict On Drop question14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Tue, 10 Jul 2007 13:00:49 -0700803_iso-8859-1 Thanks....

Late in day and my mind is going blank...

FRITZ LEBLANC wrote: It's in SYSTABLES and is called CLUSTERTYPE.

>>> Ramon Santiago 07/10/07 3:43 PM >>> Where is the flag value is set when the following SQL is issued? SYSTABLES? If so what field?

ALTER TABLE owner.tblname DROP RESTRICT ON DROP; --------------------------------------------------------------------------------- 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 [...] 4684 70 17_Re: SQL Question.9_Sam Baugh24_Sam.Baugh@MOTION-IND.COM31_Tue, 10 Jul 2007 16:26:31 -0500550_us-ascii This sort of gets you close using recursive SQL in DB2v8. I created a small table using your data, but it is close to time to go home and my brain hurts. I'll let you figure out the rest, but a "program solution" is probably a far better solution.

WITH X (LVL, ID_NO, ID_NAME, ID_LST ) AS (SELECT DISTINCT 0 , ID_NO , VARCHAR('',20) , VARCHAR('',100) FROM SAM_TEST UNION ALL SELECT X.LVL+1 , X.ID_NO , S.ID_NAME , X.ID_LST || ',' || S.ID_NAME FROM X , SAM_TEST S WHERE X.ID_NO = S.ID_NO AND X.ID_NAME < S.ID_NAME AND X.LVL < 5 ) [...] 4755 126 17_Re: SQL Question.11_John Miller26_John.L.Miller@ATCOITEK.COM31_Tue, 10 Jul 2007 15:34:37 -0600535_us-ascii I apoligize. I prematurely sent my solution out. The statement should read:

WITH TEMP(KEYNO, LIST) AS (SELECT KEYNO, CAST(RTRIM(NAME) AS VARCHAR(256)) FROM TBL UNION ALL SELECT TBL.KEYNO, TEMP.LIST CONCAT ',' CONCAT RTRIM(TBL.NAME) FROM TEMP, TBL WHERE TEMP.KEYNO = TBL.KEYNO AND LOCATE(RTRIM(TBL.NAME), TEMP.LIST) = 0 ) SELECT B.KEYNO, MIN(B.LIST) FROM (SELECT KEYNO, MAX(LENGTH(LIST)) AS LEN FROM TEMP GROUP BY KEYNO) A, TEMP B WHERE A.KEYNO = B.KEYNO AND LEN = LENGTH(B.LIST) GROUP BY B.KEYNO ORDER BY B.KEYNO; [...] 4882 175 17_Re: SQL Question.10_Doyle Mark26_Mark.Doyle@QBEAMERICAS.COM31_Tue, 10 Jul 2007 17:11:17 -0500335_us-ascii John,

That's slick! Change the varchar(256) to varchar(254) to avoid a -171 SQL code and it runs in V8. Final SQL:

--CREATE TABLE TEMPTBL --(KEYNO INTEGER NOT NULL, LNAME CHAR(20) NOT NULL) ; --COMMIT; -- --INSERT INTO TEMPTBL VALUES ( 20,'SMITH' ); --INSERT INTO TEMPTBL VALUES ( 30,'ALLEN' ); -- etc. . . [...] 5058 49 10_DB2Connect11_Jim Wheeler27_James.Wheeler@HEALTHNET.COM31_Tue, 10 Jul 2007 15:45:08 -0700463_us-ascii This may not be the place to ask this question, but here goes! We would like to put DB2Connect (Personal, Enterprise, whatever) on our CITRIX servers. There is an article on IBM's web site that states it is possible. But there is no details as to how to do it, nor the cost (both in cost of the product(s) and sizing of the servers). Can anyone out there point me in the direction where I can find information on this? Any help wold be appreciated. [...] 5108 75 14_Re: DB2Connect12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 10 Jul 2007 18:09:00 -0700458_iso-8859-1 The cost will depend upon the number of users of the product. Best is contact your IBM rep for sizing.

I'm not sure about the sizing of the server. From what I've seen, it pretty much depends upon the applications that would be using DB2 connect rather than DB2 connect itself, unless you're actually using DB2 connect on the Citrix server as a pure gateway. but i would question why you're putting it on a Citrix server in this case. [...] 5184 353 97_Re: SV: [DB2-L] CALL db2XUfunctions.XMLUPDATE stored procedure from A Java Application or Trigger13_Essam Mansour23_essam.mansour@GMAIL.COM31_Wed, 11 Jul 2007 09:57:00 +0100736_ISO-8859-1 Hi everybody,

I have discovered that it is not allowed to call a stored procedure with output parameter from a action of trigger. Is that correct?

I removed the output parameter from the stored procedure and it is working. create trigger AIM_Trg_PP1234_R01 after update of Number_of_Days on aim_TimingDEvent_tab referencing old as oldrow new as newrow for each row mode db2sql WHEN ( newrow.Number_of_Days = 2 ) BEGIN ATOMIC CALL DB2XUFUNCTIONS.XMLUPDATE2( '2007-07-10 15:36:13.336092', 'Select CIDoc from aim_ComplexInfo_tab', 'update aim_ComplexInfo_tab set CIDoc=?'); END # [...] 5538 96 17_Re: SQL Question.19_Cuneyt Goksu (.NET)19_cuneytgoksu@USA.NET31_Wed, 11 Jul 2007 14:17:33 +0300565_windows-1250 No. There is no max#

Cuneyt

-----Original Message----- From: Andy Lankester [mailto:alankester@CDBSOFTWARE.COM] Sent: Tuesday, July 10, 2007 6:34 PM Subject: Re: SQL Question.



If there is a maximum of x names with the same number then an x-way outer join would do it.

Andy Lankester

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cuneyt Goksu (.NET) Sent: Tuesday, July 10, 2007 3:37 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] SQL Question. [...] 5635 324 17_Re: SQL Question.19_Cuneyt Goksu (.NET)19_cuneytgoksu@USA.NET31_Wed, 11 Jul 2007 14:17:37 +0300475_US-ASCII Mark,

Thanks for writing this but there is no upper limit and can not be...

Yes it's ugly, it works but it's not practical :-)

Thank you, Cuneyt

-----Original Message----- From: Doyle Mark [mailto:Mark.Doyle@QBEAMERICAS.COM] Sent: Tuesday, July 10, 2007 6:32 PM Subject: Re: SQL Question.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cuneyt Goksu (.NET) Hi, [...] 5960 196 17_Re: SQL Question.19_Cuneyt Goksu (.NET)19_cuneytgoksu@USA.NET31_Wed, 11 Jul 2007 14:38:28 +0300473_US-ASCII John, Mark and All,

This is just perfect! Thanks...

Cuneyt



-----Original Message----- From: Doyle Mark [mailto:Mark.Doyle@QBEAMERICAS.COM] Sent: Wednesday, July 11, 2007 1:11 AM Subject: Re: SQL Question.



John,

That's slick! Change the varchar(256) to varchar(254) to avoid a -171 SQL code and it runs in V8. Final SQL:

--CREATE TABLE TEMPTBL --(KEYNO INTEGER NOT NULL, LNAME CHAR(20) NOT NULL) ; --COMMIT; 6157 431 97_Re: SV: [DB2-L] CALL db2XUfunctions.XMLUPDATE stored procedure from A Java Application or Trigger13_Essam Mansour23_essam.mansour@GMAIL.COM31_Wed, 11 Jul 2007 12:46:24 +0100871_ISO-8859-1 I would like to thank you all

I think I was wrong, it is allowed to call a stored procedure from an action of a trigger. Please see the next code: ================ create trigger AIM_Trg_PP1234_R01 after update of Number_of_Days on aim_TimingDEvent_tab referencing old as oldrow new as newrow for each row mode db2sql WHEN ( newrow.Number_of_Days = 2 ) BEGIN ATOMIC DECLARE errorCode INTEGER ; DECLARE errorMsg VARCHAR(320); CALL DB2XUFUNCTIONS.XMLUPDATE( '2007-07-05 09:36:13.336092 registered', 'Select CIDoc from aim_ComplexInfo_tab', 'update aim_ComplexInfo_tab set CIDoc=?' , errorCode,errorMsg); END # ================ [...] 6589 40 26_MODIFY utility performance0_17_jtonchick@AOL.COM31_Wed, 11 Jul 2007 09:46:56 -0400665_us-ascii I am experienceing a performance problem with running the MODIFY RECOVERY utility.? A large application was installed with many partitioned tablespaces (250 partitions each).? The application DBAs also copy the indexes for these tablespaces.? I have a utility statement generation program that queries SYSCOPY and generates LISTDEF & MODIFY statements by tablespace to remove entries? beyond an age theshold.? When the image copies for the new application became elligable for removal, the MODIFY utility step started taking an excessive amout of time and resources.? It seems to be I/O bound.? Analysis of the utility threads show a very high number [...] 6630 23 36_AD2R abend after migrating to DB2 v810_Steve Lamb25_steven.lamb@CAPGEMINI.COM31_Wed, 11 Jul 2007 08:51:32 -0500580_- Strange problem...

Last weekend I migrated a production system (z/OS 1.7) from DB2 v7 to v8 CM mode. Everything seemed to be OK, but we've had one CICS TS 2.3 transaction that started abending with an AD2R (S0C4 Reason Code 7D614CC8)in program CEECRINI +x'0AEC'.

The problem occured on virtually the first SQL call - an Open Cursor. A SET CURRENT PACKAGESET command before this in the program worked OK. I had a look on the IBM web site and couldn't find anything that seemed to fit the symptoms. I bound the package again and lo and behold, it's now working [...] 6654 205 30_Re: MODIFY utility performance13_Michael Ebert18_mebert@AMADEUS.COM31_Wed, 11 Jul 2007 16:09:26 +0200387_ISO-8859-1 The runtime of the MODIFY Utility should be approximately proportional to the number of entries it has to remove. Can you specify what "excessive" means? A looooong time ago (in 2001), there was a fix for a problem with a similar description (at the time, the removal time was proportional to the number of existing records for a TS, times the number of rows to remove). [...] 6860 55 43_(DB2-LUW)performance issue while monitoring11_sanjoy khan17_san.hyd@GMAIL.COM31_Wed, 11 Jul 2007 19:53:10 +0530749_ISO-8859-1 Hi,

I want to monitor some databases of datawarehouse area using some event and snapshot monitor elements.

How they will effect the performance ?

any idea? or

can you give me any link or document regarding it?



Thanks, Sanjoy

--------------------------------------------------------------------------------- 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 6916 26 36_Db2 new Fixpack and Active Directory3_lan18_lxw176@HOTMAIL.COM31_Wed, 11 Jul 2007 09:39:42 -0500575_- Hi,

We have an DB2 server setup on AIX 5l with DB2 V8.1 UDB enterprise server edition plus Fixpack 14.installed. On client side, we had DB2 Admin Client V8.1 with Fixpack 5 install and everyhting worked fine during the past couple of years. Today, I installed db2 Admin Client Fixpack 14 on the client side, there are error arose. when I tried to open the DB2 Control Center and login as database user, I got erro SQL1092N "lxw177" does not have the authority to perform the requested command. When I open the DB2 command Line Processor, I got error:"Db2 1015E [...] 6943 46 18_time-based Trigger13_Essam Mansour23_essam.mansour@GMAIL.COM31_Wed, 11 Jul 2007 16:07:32 +0100387_ISO-8859-1 He everybody,

I have an attribute, its value (AV) is calculated by subtracting the current time from (AV). It is required to update the value of the attribute every minute.

I am thinking to write a stored procedure and calling it from a java thread.

I am looking for a solution to let the DB2 execute the stored procedure every minute automatically. [...] 6990 73 40_Re: Db2 new Fixpack and Active Directory20_Stritzinger, David G25_david.stritzinger@EDS.COM31_Wed, 11 Jul 2007 11:11:30 -0400459_US-ASCII Not sure what all your issues may be, but first a couple of questions and an observation.

1) What version of DB2 are you running on the server? You state 8.1

2) Is this the first time some one tried to access the DB2 server with a client at fix pack 14? If so then the first connect will try to bind the packages that come with fix pack 14 and maybe "lxw177" does not have DBAMIN authority and therefore the SQL1092N error message [...] 7064 76 40_Re: AD2R abend after migrating to DB2 v813_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Wed, 11 Jul 2007 08:44:38 -0700592_us-ascii No, I have not experienced abend S0C4 in CEECRINI, but yes, I have experienced this phenomenon. At this shop, with every single DB2 upgrade since version 5, there has always been 1 package that started giving a flakey error, which rebinding fixed. With version 8, we had 2 packages give errors which rebinding fixed. And I experienced this once with PUT maintenance as well. I don't know what it is about us, maybe we're cursed because we use IEF/Cool:Gen/Advantage Gen/All Fusion Gen/etc. At least now I remember to try rebinding first when there's a problem -- one time I had [...] 7141 368 22_Re: time-based Trigger14_Fazio, Richard21_RFAZIO@TRANSUNION.COM31_Wed, 11 Jul 2007 11:14:56 -0500462_iso-8859-1 How often do you need this value? Online/Batch?





If not too often, why not use a UDF and calculate on the fly. Not to often is relative, but I can state that if you try calling a UDF several billion times a day...bad things can happen :-)





If you need to persist the value ("update" the attribute value sounds like you're storing it in a table), you may want to try a simpler approach than a timed SP. [...] 7510 17 22_Re: time-based Trigger11_Bill Cioffi19_Cioffiw@LABCORP.COM31_Wed, 11 Jul 2007 12:29:45 -0400630_us-ascii THis is a job for CA/OPS or some other automatic packages, CA7 can handle it as well as 100 different packages or methods. It has nothing to do with DB2!!!! ----------------------------------------- This e-mail and any attachments may contain CONFIDENTIAL information, including PROTECTED HEALTH INFORMATION. If you are not the intended recipient, any use or disclosure of this information is STRICTLY PROHIBITED; you are requested to delete this e-mail and any attachments, notify the sender immediately, and notify the LabCorp Privacy Officer at privacyofficer@labcorp.com or call (877) 23-HIPAA / (877) 234-4722. [...] 7528 47 30_DB2 v8 Create Trigger Question14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Wed, 11 Jul 2007 09:59:42 -0700475_iso-8859-1 I want to create a trigger that would set a child field to current timestamp and update a parent table with the timestamp generated.

I don't think this is the correct syntax any ideas?

CREATE TRIGGER TGR_CHILD_UPD NO CASCADE BEFORE UPDATE ON TEST_CHILD REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET N.CHILD_TMS = CURRENT TIMESTAMP; UPDATE TEST_PARENT SET PARENT_TMS = N.CHILD_TMS WHERE TEST_PARENT.PARENT_ID = N.PARENT_ID; END@ [...] 7576 52 40_Re: Db2 new Fixpack and Active Directory3_lan18_lxw176@HOTMAIL.COM31_Wed, 11 Jul 2007 12:28:35 -0500369_- Hi Dave,

I guess the server side DB2 is 8.2, because when I tried to connect to the server from another client machine with DB2 V 8.1 Work group server edition, but without Active Directory installed, everything works fine, and I get the following from DB2 command line processor: db2 => connect to nemis431 user lxw177 Enter current password for lxw177: [...] 7629 82 62_FW: DSNTINST DSNTIPD Actual Counts - Anyone have SQL for this?26_ANDERSON Margaret * OR SDC29_Margaret.Anderson@STATE.OR.US31_Wed, 11 Jul 2007 11:02:45 -0700522_us-ascii > Hello All, > I need to clone a DB2 subsystem that has grown over many years and the > ZPARMs have not been kept up to date for the values on the DSNTIPD > panel in the DSNTINST dialog. This would be values like Average > Tablespaces per Database, Average Views per Table and so on. > > The Archives show a discussion about this a couple years ago and > someone mentioned doing the needed queries of the catalog tables to > get the actual values. Before I re-invent the wheel, does anyone have > the SQL to [...] 7712 34 34_Re: DB2 v8 Create Trigger Question12_Steve Grimes22_steve_grimes@WUSTL.EDU31_Wed, 11 Jul 2007 13:20:36 -0500191_US-ASCII Hello,

I thought updates were limited to AFTER triggers. At least in V6/V7.

Stg

--------------------------------------------------------------------------------- 7747 372 22_Re: time-based Trigger12_Jim Harrison22_jimh_lists@VERIZON.NET31_Wed, 11 Jul 2007 14:52:54 -0400410_iso-8859-1 And there just happens to be some code I wrote available on the Code Place to invoke the STIMTER

http://www.idug.org/wps/wcm/myconnect/resources/file/eb44814cc887d8f/wait.txt?MOD=AJPERES

You may have to go thru some more registration pages - evidently Idug wants more information from it's non-paying members. If you can't get thru, let me know off list and I can send it to you. [...] 8120 56 40_Re: AD2R abend after migrating to DB2 v814_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Wed, 11 Jul 2007 13:45:57 -0500444_- Like Cathy, I have not personally seen this problem. There are a few issues that often caused me some problem

Dependencies on SYSIBM.SYSDUMMY1 which tends to be dropped and recreated by the upgrade process.

If the upgrade did not include an IPL (We usually do the ERLY code weeks in advance) Long running address spaces that loaded parts of DB2 or Applications and did not restart. Did CICS stay up throughout the upgrade? [...] 8177 39 34_Re: DB2 v8 Create Trigger Question14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Wed, 11 Jul 2007 12:22:31 -0700828_iso-8859-1 You can have a trigger set up to evaluate a row before/after of insert/delete/update.

This allows for testing for conditions or setting values of a field or updating other tables.



Steve Grimes wrote:

Hello,

I thought updates were limited to AFTER triggers. At least in V6/V7.

Stg --------------------------------------------------------------------------------- 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 [...] 8217 59 34_Re: DB2 v8 Create Trigger Question14_Wayne Driscoll25_wdriscoll@JMESOFTWARE.COM31_Wed, 11 Jul 2007 15:44:09 -0400506_iso-8859-1 That is correct, however, only AFTER triggers are allowed to perform database updates, a before trigger cannot issue DML, as a before trigger cannot cascade.

Wayne Driscoll Product Developer JME Software LLC NOTE: All opinions are strictly my own.





From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ramon Santiago Sent: Wednesday, July 11, 2007 2:23 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 v8 Create Trigger Question [...] 8277 49 20_DB2 v7 CASE question0_25_Dave.Schaeffer@BENDIX.COM31_Wed, 11 Jul 2007 16:06:39 -0400779_us-ascii Hello everyone,

I'm trying the following SQL on DB2 v7 running on a z/OS system:





SELECT IKA_CUSTOMER_BASE, IKA_CUST_BILL_TO, IKA_CUST_SHIP_TO , IKA_INVOICE_NUM, IKB_PART_NUM , CASE WHEN (SELECT COUNT(*) FROM SAPDB2.DISTCHAN

WHERE CUST_BASE = IKA_CUSTOMER_BASE AND DC_CUST_SHIP = IKA_CUST_SHIP_TO AND SAP_DIST_CHAN IN ('10','20') AND SUBSTR(DIGITS(IKA_SHIP_PLANT),3,1) = '1') = 2 THEN '10' ELSE (SELECT SAP_DIST_CHAN FROM SAPDB2.DISTCHAN WHERE DC_CUST_BASE = IKA_CUSTOMER_BASE AND DC_CUST_SHIP = IKA_CUST_SHIP_TO) END AS DIST_CHANNEL FROM DB2MAST.IVHDRKAT , DB2MAST.IVDETKBT WHERE IKA_INVOICE_NUM = IKB_INVOICE_NUM AND IKA_INVOICE_NUM = 1349671 AND IKA_CUSTOMER_BASE = 10040 AND IKA_CUST_BILL_TO = 003 AND IKA_CUST_SHIP_TO = 004 [...] 8327 33 21_DB2 v8 Create Trigger14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Wed, 11 Jul 2007 13:16:08 -0700366_iso-8859-1 Scenerio:

Parent table has a mod_tmstmp field. There exists a trigger upon update to populate it with current timestamp. Great..

Child table also has a mod_tmstamp field. There also exists a trigger upon update to populate it with the current timestamp. In addition a after update trigger exists to update the parent table's timestamp. [...] 8361 409 30_Re: MODIFY utility performance0_17_jtonchick@AOL.COM31_Wed, 11 Jul 2007 16:40:51 -0400551_utf-8 Dr. Ebert, I found your Jan 2000 posting in the DB2-L archives. I was disheartened when I read that the run time was proportional to the SQUARE of the number of records. But I was hopeful that in the several years between then and now (we are running DB2 8.1), IBM would have tweaked the utility's performance. I also found an old (1993) entry on IBMLink that confirmed that V2.3 of DB2 did perform two tablespace scans per tablespace record. That was a long time ago, and IBM has made many performance enhancements to DB2 since then. [...] 8771 72 24_Re: DB2 v7 CASE question10_Doyle Mark26_Mark.Doyle@QBEAMERICAS.COM31_Wed, 11 Jul 2007 15:47:42 -0500449_us-ascii You can't use sub-selects in a WHEN clause. You can, however, change the subselect to a join and use that column in a WHEN clause. It looks like you may need to use an NTE for this query.

Mark

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dave.Schaeffer@BENDIX.COM Sent: Wednesday, July 11, 2007 3:07 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 v7 CASE question [...] 8844 295 30_Re: MODIFY utility performance13_Michael Ebert18_mebert@AMADEUS.COM31_Wed, 11 Jul 2007 22:58:54 +0200577_ISO-8859-1 The MODIFY performance problem was fixed by IBM not long after my postings. Most likely the problem doesn't exist any more. I don't have the PTF or APAR number any more; it was V5 anyway. Maybe the problem is not really with the MODIFY utility, but with the LIST processing? Try generating MODIFY statements without using LISTDEFs. You might also check whether it is possible to curtail the growth of SYSCOPY by reducing the number of image copies you take - are all of them really necessary? The number of records does seem excessive. Check whether an IC of a [...] 9140 108 40_Re: Db2 new Fixpack and Active Directory12_Travis, John25_john.travis@CAPGEMINI.COM31_Thu, 12 Jul 2007 08:19:01 +0100546_us-ascii Lan, Your server is running AIX so the packages bound when the server was updated are different to the packages uded by your windows client. You need to bind the packages once for every different client platform, using a userid that has administrative privilege on the server.

Regards, John Travis

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of lan Sent: 11 July 2007 18:29 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Db2 new Fixpack and Active Directory [...] 9249 328 22_Re: time-based Trigger13_Essam Mansour23_essam.mansour@GMAIL.COM31_Thu, 12 Jul 2007 08:36:03 +0100625_WINDOWS-1252 Thanks Jim,

It will be nice if you can send me it. I am not a member.



Best Regards, Essam

On 7/11/07, Jim Harrison wrote: > > And there just happens to be some code I wrote available on the Code > Place to invoke the STIMTER > > > http://www.idug.org/wps/wcm/myconnect/resources/file/eb44814cc887d8f/wait.txt?MOD=AJPERES > > You may have to go thru some more registration pages - evidently Idug > wants more information from it's non-paying members. If you can't get thru, > let me know off list and I can send it to you. > > > > ----- Original Message [...] 9578 75 24_Re: DB2 v7 CASE question14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK31_Thu, 12 Jul 2007 10:11:17 +0200364_iso-8859-1 > SELECT ..., CASE

> WHEN (SELECT COUNT(*) ...) = 2 > THEN '10' > ELSE (SELECT ...) > END AS DIST_CHANNEL

This will be possible in version 8.

Use a nested table expression to generate both the COUNT(*) and the other subquery's result, either with a join or with a correlated NTE, e.g.: (didn't try it out, so no guarantees...) [...] 9654 85 22_DB2 V8 and DB2 Connect11_Leong, Werv24_wellington.leong@EDS.COM31_Thu, 12 Jul 2007 10:34:24 +0200418_US-ASCII Hi,

Apologies if this question has been raised before. We are planning to migrate to DB2 V8 for z/OS and I wonder if any one knows what is the current recommended minimum level DB2 Connect (Enterprise, Personal and unlimited edition) to be on?

I believe that recommended minimum for the Enterprise edition of DB2 Connect was DB2 V8.1 Fixpack 10 or DB2 V8.2 Fixpack 3. Is this still valid ? [...] 9740 428 66_Re: FW: DSNTINST DSNTIPD Actual Counts - Anyone have SQL for this?13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 12 Jul 2007 09:36:37 +0100574_us-ascii Hi Margaret,

If I understand your requirement, you want a PDS member that you can feed into the DSNTINST Clist that will give you a DSNTIJUZ job to match your current Zparm values, right? I doubt there's SQL to obtain those values as they're not stored in the Catalog.

All I can think of is a bit manual. You could copy your DSNTINST Clist input member to another library and pass it through DSNTINST, generating a DSNTIJUZ job. Then use 3.13 to compare that output with the original DSNTIJUZ job (you still have that, right? If not you can just [...] 10169 18 40_Re: AD2R abend after migrating to DB2 v810_Steve Lamb25_steven.lamb@CAPGEMINI.COM31_Thu, 12 Jul 2007 04:09:21 -0500373_- I ran all the pre-migration checks - they picked up the usual stuff, like BMC tools and stored procedures but no application problems - packages or views.

The ERLY code went in a few weeks before without any problems and the CICS sevice was taken down and reloaded with the DB2 v8 datasets in the STEPLIB and DFHRPL concatenations as part of the migration... [...] 10188 42 66_Re: FW: DSNTINST DSNTIPD Actual Counts - Anyone have SQL for this?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 12 Jul 2007 20:19:32 +1000366_US-ASCII Why bother?

These values are used to generate suitable initial sizes for the catalog and directory objects. So, use a clone of the current actual allocations for DSNDB01 and DSNDB06 clusters and don't use the DEFINE statements in the generated job.

James Campbell



On 11 Jul 2007 at 11:02, ANDERSON Margaret * OR SDC wrote: [...] 10231 454 30_Re: MODIFY utility performance14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 12 Jul 2007 12:09:08 +0100395_iso-8859-1 Also, it might be worth considering altering MODIFY to delete fewer obsolete SYSCOPY records (by reducing the time between "now" and the purge date)

In the past people have found that multiple runs of MODIFY work faster than a single big-bang purge (also multiple smaller executions can be less intrusive)

More work in the short term, but with perhaps more success [...] 10686 16 40_Re: AD2R abend after migrating to DB2 v810_Steve Lamb25_steven.lamb@CAPGEMINI.COM31_Thu, 12 Jul 2007 09:51:48 -0500659_- It looks like the 7D614CC8 is an FRB reason code (the corresponding return code being the S0c4). Does anybody know where the FRB reason codes are documented?

Regards,

Steve

--------------------------------------------------------------------------------- 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 10703 138 40_Re: AD2R abend after migrating to DB2 v813_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Thu, 12 Jul 2007 10:44:48 -0500536_US-ASCII Question are you using DB2 Data Sharing? If so Check out PK33340. There is an issue with P-LOCKS

Bob Tilkes





Steve Lamb

Sent by: DB2 Data Base Discussion List







07/12/2007 09:51 AM Please respond to DB2 Database Discussion list at IDUG

From Steve Lamb To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject Re: [DB2-L] AD2R abend after migrating to DB2 v8 [...] 10842 173 30_Re: MODIFY utility performance12_Isaac Yassin20_yassini@BEZEQINT.NET31_Thu, 12 Jul 2007 19:41:24 +0300487_us-ascii Hi,

Last time I had to handle that problem was in V4 - it took 30+ jobs, running serially, during 2-3 weeks using small time periods until all was cleared. As it was in the previous millennium ...



Isaac Yassin

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of jtonchick@AOL.COM Sent: Wednesday, July 11, 2007 4:47 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] MODIFY utility performance [...] 11016 33 46_DB2 v8 z/OS Data Storage Unicode versus EBCDIC14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Thu, 12 Jul 2007 10:03:29 -0700475_iso-8859-1 Is there any benefit in storing the data in UNICODE versus EBCDIC if the application code resides on the mainframe?

1) Is it simply a matter of changing the CCSID to UNICODE in the CREATE Database/tablespace? 2) Is there any performance benefits for making the change? 3) From what I have read, the data is stored base on the CCSID of the server. Since the server is the mainframe, does that necessarily mean EBCDIC? 4) How are utilities/SQL impacted? [...] 11050 93 50_Re: DB2 v8 z/OS Data Storage Unicode versus EBCDIC9_Mike Bell21_mbell11a1@VERIZON.NET31_Thu, 12 Jul 2007 12:36:41 -0500550_windows-1250 This needs a small book but a short list of issues comes to mind. Others will add to the list. 1. If you start using JAVA, then all EBCDIC data has to be converted to unicode and back. 2. If you have to support many characters sets, read multiple CCSID's then unicode will prevent you from losing data due to character conversions. 3. If you are supporting distributed applications (say running on pc's in ASCII) then unicode will let you run whether the pc's all have the same basic CCSID or not. 4. If you have to match to legacy [...] 11144 447 22_Re: time-based Trigger12_Jim Harrison22_jimh_lists@VERIZON.NET31_Thu, 12 Jul 2007 13:48:47 -0400410_Windows-1252 Basic membership is free - just fill out the registration form. I discovered there's a lot of new stuff since I last browsed the code page. Definitely worth signing up for and taking a look.



----- Original Message ----- From: Essam Mansour Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thursday, July 12, 2007 3:36 AM Subject: Re: [DB2-L] time-based Trigger [...] 11592 120 26_Re: DB2 V8 and DB2 Connect22_Dillon, Christine, ITD22_chris.dillon@ACGOV.ORG31_Thu, 12 Jul 2007 12:26:52 -0700607_US-ASCII we understand that it is FP10 for COMPAT mode & FP14 for full function mode

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Leong, Werv Sent: Thursday, July 12, 2007 1:34 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V8 and DB2 Connect





Hi,

Apologies if this question has been raised before. We are planning to migrate to DB2 V8 for z/OS and I wonder if any one knows what is the current recommended minimum level DB2 Connect (Enterprise, Personal and unlimited edition) to be on? [...] 11713 116 50_Re: DB2 v8 z/OS Data Storage Unicode versus EBCDIC14_Ramon Santiago28_ramon_santiago@SBCGLOBAL.NET31_Thu, 12 Jul 2007 13:09:42 -0700579_iso-8859-1 Thanks

Mike Bell wrote: This needs a small book but a short list of issues comes to mind. Others will add to the list. 1. If you start using JAVA, then all EBCDIC data has to be converted to unicode and back. 2. If you have to support many characters sets, read multiple CCSID's then unicode will prevent you from losing data due to character conversions. 3. If you are supporting distributed applications (say running on pc's in ASCII) then unicode will let you run whether the pc's all have the same basic CCSID or not. 4. If you [...] 11830 81 50_Re: DB2 v8 z/OS Data Storage Unicode versus EBCDIC14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK31_Fri, 13 Jul 2007 00:52:50 +0200444_iso-8859-1 Short answers:

> 1) Is it simply a matter of changing the CCSID to UNICODE in the > CREATE Database/tablespace?

Yes.

> 2) Is there any performance benefits for making the change?

You may avoid (some or all) conversions EBCDIC <--> Unicode. (But if chosen carelessly, you may have more conversions than before.)

> 3) From what I have read, the data is stored base on the CCSID of > the server. [...] 11912 10 40_Re: AD2R abend after migrating to DB2 v810_Steve Lamb25_steven.lamb@CAPGEMINI.COM31_Fri, 13 Jul 2007 05:19:03 -0500520_- No data sharing - this is a stand-alone system.

--------------------------------------------------------------------------------- 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 11923 43 12_Re: DSNACCOR14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Fri, 13 Jul 2007 16:31:36 +0100502_- Do it yourself, Marcus! I've sorted out the strange DSNACCOR return code - I'd created the database and forgotten to BIND the package! Tadaa! It works now!

Marcus



This e-mail is only for the above addressees. It may contain confidential or privileged information. If you are not an addressee you must not copy, distribute, disclose or use any of the information in it or any attachments. If you have received it in error please notify the sender and delete it immediately. [...] 11967 74 66_Re: FW: DSNTINST DSNTIPD Actual Counts - Anyone have SQL for this?26_ANDERSON Margaret * OR SDC29_Margaret.Anderson@STATE.OR.US31_Fri, 13 Jul 2007 10:16:16 -0700307_us-ascii James, I was under the impression that at least a couple of values on this screen are used as input to some of the calculations (like DSMAX) that produce DSNZPARM values. Are you saying that the only thing the values on this screen are used for is determining the VSAM dataset sizes? Margaret [...] 12042 132 32_DB2 Administration Tool Question13_Laine, Rogers22_rlaine@WHITNEYBANK.COM31_Fri, 13 Jul 2007 16:01:19 -0500520_us-ascii I'm using the ADMIN Tool to view tablespaces that are using more than one extent. Once I have them identified I run the following job to....

1. Stop the TS 2. Alter the TS PRIQTY & SECQTY values 3. Reorg the TS 4. Start the TS 5. Execute RUNSTATS

I again use the ADMIN Tool to review TS with more than one extent and it still shows the same number of extents and the PRI/SEC values that I changed them to in step 2. Can anyone explain why I'm not seeing the extents changing after the reorg? [...] 12175 100 36_Re: DB2 Administration Tool Question9_Mike Bell21_mbell11a1@VERIZON.NET31_Sat, 14 Jul 2007 12:24:08 -0500557_windows-1250 The normal answer is 'It Depends'. In this case the depends is on the available space on the pack. When you do a define cluster, SMS will allocate the primary quantity in up to 5 extents depending on the available free space on the packs available for that dataset. The normal tool for consolidating free space on a pack is either FDR or DF/DSS. This is usually a problem with large datasets where the primary allocation is larger than the largest contiguous free space on any pack. SMS knows how much free space is on the pack so it will [...] 12276 53 36_Re: DB2 Administration Tool Question14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Sat, 14 Jul 2007 20:09:59 -0500389_- Rogers, a)Your disk packs may be fragmented. PRIQTY may be allocated in as many as 5 extents.

b) sometimes a reorged objects takes more space than the original as free space is honored

if your desire is to remove the extents the suggested process is to use RECOVER, not Reorg for your step 3. This however still leaves you exposed to the problem indicated in point a. [...]