1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l June 2009, week 2 2 67 44_Retrieve the result set from Store Procedure6_keyRho16_keyrho@GMAIL.COM30_Mon, 8 Jun 2009 00:12:56 -0400399_ISO-8859-1 Lister's !

To retrieve all rows from the cursors which created from Stored-procedure (z/OS V8, 9), I do believe, I must issue sets of SQL statements, such as Describe Procedure, Associate Locators, Allocate Cursor ... and Describe Cursor, .. Fetch ...

If anyone has a good sample routines, then I'd like to share, if it's possible, regards host language's compiler ... [...] 70 126 56_AW: [DB2-L] [MVS-Fluff] Ruminations on the DB2 optimiser0_26_Walter.Janissen@ITERGO.COM30_Mon, 8 Jun 2009 11:11:36 +0200528_iso-8859-1 Hi Nick

You mentioned: What if we had open to us the ability to specify a Partial hint: ie: DB2 I want you to start at Table X (rather than Y) and then you can do what you want.

There is something available, which with you can achieve just that, but unfortunately it is IBM internal and I don't know, if you are able to get a documentation. The feature I am talking about are the CTE-hints, where you can tell the optimizer using a CTE, which table it has to start with in a join or which index it [...] 197 679 36_AW: [DB2-L] LOCK TABLE SQL Statement0_26_Walter.Janissen@ITERGO.COM30_Mon, 8 Jun 2009 11:24:19 +0200324_iso-8859-1 Hi

One additional comment to that: By accident I heard last week from an IBM'er, that even if you have executed a LOCK TABLE ... IN EXCLUSIVE MODE, others can read the table, even WITH CS, because of the lock avoidance feature. You will not believe that, won't you?

P.S. This works as "coded". [...] 877 369 33_DB2 for z/OS Version Polls (2009)10_DB2usa !!!19_db2usa3@HOTMAIL.COM30_Mon, 8 Jun 2009 05:32:35 -0400441_Windows-1252

Hi DB2 user,

Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com



Last update on Monday, June 2nd 2009









Here is a new poll about DB2 for z/OS:



DB2 for z/OS Version (2009)

IBM currently supports only two versions of DB2 for z/OS. On June 1st 2009, which highest version did you use in production? [...] 1247 295 41_AW: [DB2-L] AW: [DB2-L] AW: [DB2-L] Tools0_26_Walter.Janissen@ITERGO.COM30_Mon, 8 Jun 2009 11:35:45 +0200597_iso-8859-1 Hi Nenad

Sorry for the delay, but I wasn't in my office last week. It's very interesting, what you said and I am very interested in how you set up QM.

One kind we did our measurements was, to execute the assembler-makro TIMEUSED (I don't know, if that is an official macro or an homegrown, because my knowledge about assembler is very limited) before the SQL-statement and immediately afterwards. The difference is the time used by that statement. Abd there was a difference with and without QM and different for different statement-types (all time in microseconds): [...] 1543 191 28_Presentations from SQL Adria10_DB2usa !!!19_db2usa3@HOTMAIL.COM30_Mon, 8 Jun 2009 05:39:22 -0400461_Windows-1252

Hi DB2 user,

Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com

Last update on Monday, June 8th 2009







Here are several presentations from SQL Adria, available on SQL Adria website:



- DB2 9 for z/OS - Hints and Tips for DBAs by Namik Hrle





- DB2 9 for z/OS – Hints and Tips for Application Programmers by Namik Hrle [...] 1735 35 34_Information about Reorg Tablespace24_SUBSCRIBE DB2-L Karthick16_karthickn@HCL.IN30_Mon, 8 Jun 2009 11:55:43 +0000513_windows-1252 Hi,

I need a information about how to find when Reorg ran on particular tablespace if inline image copy not taken.

I also need to Know the difference between Oracle gateways and DB2DDF facility,Is it possible to use DDF instead of OTG.

Kindly Share your ideas.

I thank everyone in Advance.

Thanks, Karthick.N

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 1771 89 46_AW: [DB2-L] Information about Reorg Tablespace12_PUSCH Othmar27_Othmar.Pusch@IT-AUSTRIA.COM30_Mon, 8 Jun 2009 14:24:54 +0200621_iso-8859-1 Hi dear Karthick !

A little bit info related to your OTGW-question (based on our results):

o) We run 8 SYSPLEX's (e.g. a 5way-db2-sharing-member in the biggest plex) with about 600 operative CTS/CICS_Region's. o) ORACLE has software-version's of OTGW with DDF and without DDF. o) From the aspect of performance, feature's and possibilities the OTGW-Version without DDF is (from my point of view) the absolute-clear winner. o) But, if ya wanna to put some workload to ZIIP (ya know: TCB-mode versus SRB-mode on the mainframe --> DRDA via TCP/IP to DB2's DDF) --> DDF-Usage makes maybe sense. [...] 1861 255 38_Re: Information about Reorg Tablespace15_Leon Katsnelson15_leon@CA.IBM.COM30_Mon, 8 Jun 2009 08:38:10 -04002089_US-ASCII

>I also need to Know the difference between Oracle gateways and DB2DDF >facility,Is it possible to use DDF instead of OTG.

DDF is not a replacement for Oracle Transparent Gateway but IBM DB2 Connect is.



|------------> | From: | |------------> >---------------------------------------------------------------------------------------------------------------------------------------------| |SUBSCRIBE DB2-L Karthick | >---------------------------------------------------------------------------------------------------------------------------------------------| |------------> | To: | |------------> >---------------------------------------------------------------------------------------------------------------------------------------------| |DB2-L@WWW.IDUGDB2-L.ORG | >---------------------------------------------------------------------------------------------------------------------------------------------| |------------> | Date: | |------------> >---------------------------------------------------------------------------------------------------------------------------------------------| |06/08/2009 07:58 AM | >---------------------------------------------------------------------------------------------------------------------------------------------| |------------> | Subject: | |------------> >---------------------------------------------------------------------------------------------------------------------------------------------| |[DB2-L] Information about Reorg Tablespace | >---------------------------------------------------------------------------------------------------------------------------------------------| |------------> | Sent by: | |------------> >---------------------------------------------------------------------------------------------------------------------------------------------| |DB2 Data Base Discussion List | >---------------------------------------------------------------------------------------------------------------------------------------------| [...] 2117 32 40_Re: Performance from Z/os(V7) to AIX(V9)13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM30_Mon, 8 Jun 2009 08:08:52 -0700284_ISO-8859-1 Based on your description of the access plan, I am assuming that the query is doing a nested loop join, with T1 as the inner? What does the plan look like on the mainframe?

What percentage of the rows in T1 qualify for the predicates C2 = :val2 and C3 = :val3? [...] 2150 63 32_Database performance fluctuation17_Alexandre Efremov19_alexandef@GMAIL.COM30_Mon, 8 Jun 2009 15:02:06 +0000600_windows-1251 Dear members!

I need ideas on performance issue.

Running DB2 UDB under Linux. Creating table from scratch, inserting ~20 million records. After first million inserts, performance decreases up to 20 times. There's no visible resource deficiency on the server.

The same exact system used to perform better with all inserts for the same table of 20 million records being over in about 2 hours. The performance of inserts fluctuated without changes in system/instance/database configuration between 2 hours per table and way over 24 hours (we had to stop inserts). [...] 2214 28 38_Re: Information about Reorg Tablespace12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Mon, 8 Jun 2009 15:13:17 +0000373_windows-1252 You can find the information in SYSCOPY. A reorg will show up as 'W' or 'X' in ICTYPE. If no inline copy was taken, then there will be no corresponding row with STYPE W or X.

Hope that helps. Adam

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 2243 34 32_Re: Presentations from SQL Adria11_Sameer Rana21_sameerdrana@GMAIL.COM30_Mon, 8 Jun 2009 15:25:49 +0000285_windows-1252 Thanks a lot for this information. I have been trying to search and collect documents that deal with real life SQL scenarios and 'How do I handle that query' in particular has some of the typical issues DBA's may face on a day to day basis and how to remediate them. [...] 2278 62 36_Re: Database performance fluctuation14_Peter Vanroose17_pvanroose@ABIS.BE30_Mon, 8 Jun 2009 15:27:00 +0000408_ISO-8859-1 > ... After first million inserts, performance decreases up to 20 times. ...

DB2 will (try to) place new inserted rows at the "logically correct" place, i.e., where the cluster sequence of the table dictates it.

Suppose you have created a new table, 2 columns, where the (index on the) first column is the clustering index; then you start inserting rows in the following order: [...] 2341 143 26_REXX, SQL and FOR BIT DATA15_Leland Goldston23_lgoldston@EARTHLINK.NET30_Mon, 8 Jun 2009 17:11:59 +0000408_windows-1252 Does anyone have an example of an SQL cursor in REXX where a column has the FOR BIT DATA attribute? I can get the SQL to work in SPUFI and as well as DSNTIAUL. But, I can't seem to get the query to work in REXX. The problem is centered around the LIKE predicate. The SPUFI version is listed below. I have listed portions of the REXX after the SPUFI. Any help would be greartly appreciated. [...] 2485 85 41_Re: SQL Programming Guideline - unleashed10_Roger Hecq18_Roger.Hecq@UBS.COM30_Mon, 8 Jun 2009 13:29:59 -0400412_US-ASCII Your blog is a good piece of work. Well done.



Roger Hecq MF IB USA DB Support 203-719-0492 / 19-337-0492

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Cuneyt Goksu (Gmail) Sent: Friday, May 29, 2009 7:20 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] SQL Programming Guideline - unleashed

Dear DB2 Community, [...] 2571 82 29_DB2 LUW v9.5 Client (Windows)0_28_BILL.GALLAGHER@PHOENIXWM.COM30_Mon, 8 Jun 2009 14:17:25 -0400468_US-ASCII We are in the midst of upgrading our DB2 LUW environment from v8.2 to v9.5. We only have one v9.5 instance/database created thus far, for use in testing the DB2 LUW v9.5 desktop clients.

The first client I'm testing is the DB2 v9.5 Client (formerly known as the Application Developer Client in v8.2). It works fine with the v9.5 instance and database, but I'm running into some issues when going against the existing v8.2 instances and databases: [...] 2654 30 33_Re: DB2 LUW v9.5 Client (Windows)11_Ted MacNEIL18_eamacneil@YAHOO.CA30_Mon, 8 Jun 2009 18:20:58 +0000331_- >We tried opening a PMR with IBM, but because we did not purchase an extended support license for v8.2, they've told us "Sorry, we can't answer any questions about v8.2".

>Anybody have any thoughts?

Do you have a support contract for 9.5? If so, IBM should be addressing why 9.5 is having problems seeing 8.2. [...] 2685 42 33_Re: DB2 LUW v9.5 Client (Windows)13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM30_Mon, 8 Jun 2009 12:32:12 -0700444_ISO-8859-1 > Anybody have any thoughts?

It sounds like you have both V8.2 and V9.5 instances running on a single server.

However, you can only have 1 DAS running (since it listens on 523) -- I assume it's on the V9.5 code, since it sees the V9.5 DB2 instances. But I suspect that is why it doesn't see the V8.2 instances. So most likely that is why discovery doesn't work properly -- this may apply to both problems 1 and 2. [...] 2728 166 50_Re: AW: [DB2-L] Information about Reorg Tablespace12_Myron Miller22_myronwmiller@YAHOO.COM30_Mon, 8 Jun 2009 13:06:46 -0700682_iso-8859-1 We converted several app's from Oracle Gateway to ODBC DDF and the performance improved between 25 and 125% depending upon the queries. Only change was using a DDF DB2 connect connection versus a OTG connection. And this was four different app's. So my experience is that DDF DB2 Connect out performs the Oracle gateway all the time. Your mileage may vary. Myron ________________________________ From: PUSCH Othmar To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Monday, June 8, 2009 8:24:54 AM Subject: [DB2-L] AW: [DB2-L] Information about Reorg Tablespace Hi dear Karthick ! A little bit info related to your OTGW-question (based on our results): [...] 2895 26 36_Re: Database performance fluctuation12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Tue, 9 Jun 2009 04:57:45 +0000386_windows-1252 Following on from what Peter has already said, you say "Over the weekend, the developers changed the order of keys for the table" - did they also change the sort order of the data that drives the insert processing???

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 2922 43 14_usage of alias12_Jeremy Huang23_huangjh@SDC.ICBC.COM.CN30_Tue, 9 Jun 2009 15:47:19 +0800429_US-ASCII Dear listers,

We have hundreds of programs now accessing TABLEA. But we found out that TABLEA was not qualified our naming convention. So we want to change the name from TABLEA to TABLEB without changing our programs. We are going to do the following:

1. CREATE TABLEB LIKE TABLEA; 2. UNLOAD from TABLEA and LOAD into TABLEB; 3. DROP TABLEA; 4. CREATE ALIAS TABLEA FOR TABLEB; 5. REBIND our programs. [...] 2966 33 31_Antwort: [DB2-L] usage of alias11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 9 Jun 2009 10:53:25 +0200402_US-ASCII Hi! I would say yes ALIAS's are used a lot in the real world. If I remember correctly they hang around after a DROP which can be a) A good thing or b) A bad thing depending on what you like or expect! I prefer them to SYNONYMS that disappear when you drop the base as you must explicity drop an ALIAS and I just find it better for GRANTS and for avoiding messy REBINDS.. Just my thoughts [...] 3000 26 18_Re: usage of alias12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Tue, 9 Jun 2009 09:00:52 +0000555_windows-1252 Jeremy - do your naming conventions also apply to views? If not, another solution would be to create a view called TABLEA against your TABLEB.

Just a thought....

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 3027 62 18_Re: usage of alias12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Tue, 9 Jun 2009 09:04:19 +0000567_windows-1252 Jeremy - I forgot to add that all of the information re ALIAS and SYNONYM is in the manuals. The answer to you question re differences is:

The differences between aliases and synonyms are as follows:

SYSADM or SYSCTRL authority or the CREATE ALIAS privilege is required to define an alias. No authorization is required to define a synonym. An alias can be defined on the name of a table or view, including tables and views that are not at the current server. A synonym can only be defined on the name of a table or view at the current [...] 3090 67 56_=?GB2312?Q?=B4=F0=B8=B4=3A_?= Re: [DB2-L] usage of alias12_Jeremy Huang23_huangjh@SDC.ICBC.COM.CN30_Tue, 9 Jun 2009 17:06:03 +0800433_GB2312 Thanks, Adam.

I know using view is another solution. But I think the overhead should be higher than using alias. Do you think so?

Jeremy







Adam Baldwin ·¢¼þÈË£º DB2 Data Base ÊÕ¼þÈË£º Discussion List DB2-L@WWW.IDUGDB2-L.ORG Ö÷Ì⣺ Re: [DB2-L] usage of alias 2009-06-09 17:00 Çë´ð¸´ ¸ø DB2 Database Discussion list at IDUG [...] 3158 43 31_DB2 V9 z/OS - LOAD build phase?11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM30_Tue, 9 Jun 2009 09:56:28 +0000734_ISO-8859-1 Hello,

LOAD utility abended in BUILD phase (space shortage in index pageset) and the tablespace is left in RECP . Has anyone had a similar experience ? I would expect only rebuild pending on indexes ....

8:02.31 DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED 8:02.31 DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:04:02 8:59.54 DSNUGSOR - SORT PHASE STATISTICS - NUMBER OF RECORDS=8644013 ELAPSED TIME=00:00:57 11:34:22.39 DSNURBXA - ERROR LOADING INDEX, ERROR = X'00E40322', INDEX = DPGP.IA 4:22.39 DSNUGBAC - RESOURCE UNAVAILABLE REASON 00D70014 TYPE 00000220 NAME DSNCAT.DSNDBC.DPGDBPA2.IATEPR90.I0001.A001 4:22.39 DSNUGBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION [...] 3202 42 48_Re: Retrieve the result set from Store Procedure7_Jan tje25_jan.moeyersons@ADELIOR.BE30_Tue, 9 Jun 2009 10:37:38 +0000491_windows-1252 On Mon, 8 Jun 2009 00:12:56 -0400, keyRho wrote:

>Lister's ! > >To retrieve all rows from the cursors which created from Stored-procedure >(z/OS V8, 9), >I do believe, I must issue sets of SQL statements, such as Describe >Procedure, Associate Locators, Allocate Cursor ... >and Describe Cursor, .. Fetch ... > Have you read; http://www.redbooks.ibm.com/abstracts/sg247604.html ? Seems to me most (if not all) about stored procedures is in there. [...] 3245 45 41_Re: SQL Programming Guideline - unleashed10_Todd Burch17_toddburch@MAC.COM30_Tue, 9 Jun 2009 08:14:21 -0500478_US-ASCII Looks good.

I would suggest more visual clues to make each tip distinct. It all kinda runs together now as it is.

Todd

----------------------- Dear DB2 Community,

As you remember, I'd requested contribution from the list to prepare SQL Programming Guideline weeks ago. There was a huge feedback arrived online / offline.

Here is all the information, I've collected; Please check the link below. http://sqlguideline.blogspot.com/ [...] 3291 60 45_AD: Describe V3.0 Available for Free Download9_Eric West25_ewest@HLSTECHNOLOGIES.COM30_Tue, 9 Jun 2009 13:27:43 +0000528_- Dear List Serve Subscribers,

For those of you who have not downloaded Describe V3.0 yet, you can still download it FREE from our website at www.hlstechnologies.com.

Describe mimics the DSNZPARM function provided in Visual Explain. The program provides the same display options by Parameters, Install Fields, and Install Panels. The value for each DSNZPARM is displayed, as are the Install Field, the Install Panel, the Macro, and the Parameter. A description for each and every parameter value is provided. [...] 3352 90 18_Re: usage of alias15_Coleman, Troy L19_Troy.Coleman@CA.COM30_Tue, 9 Jun 2009 10:07:04 -0400575_US-ASCII Have you looked at RENAME TABLE table-name TO new-table-name This would avoid the create/unload/load/drop As for ALIAS versus VIEW versus synonyms:

I've never liked the use of synonyms because anyone with select authority can create them and if you as a DBA drop a table and recreate it all the synonyms are lost. That is unless you are using a catalog management product to keep track of them and rebuild them for you. I prefer ALIAS because they stay around after the drop. As for performance of using an ALIAS or VIEW I have not bench marked it but I [...] 3443 98 18_Re: usage of alias10_Roger Hecq18_Roger.Hecq@UBS.COM30_Tue, 9 Jun 2009 10:47:35 -0400341_US-ASCII My recollection is that synonyms were available from the beginning and that aliases became available in the same release as packages. The great advantages of aliases were that they were persistent and the owner could be other than the user id of the creator. The alias could also be defined on an object on a different server. [...] 3542 126 44_Food Taster's Needed: 6/18/09 NEDB2UG Picnic7_Ed Long19_rdhm99a@PRODIGY.NET30_Tue, 9 Jun 2009 08:06:30 -0700487_utf-8 Our last meeting of the year is Thursday 6/18/09 at the Publick House in Sturbridge. Please help us evaluate their hot dogs and hamburgers; after all, this is the first year they've done our picnic. It will be a terrific day. Please preregister.

*****ANNOUNCEMENT***** The next meeting of the New England DB2 User's group will occur on Thursday June 18, 2009 in the Paige Room at the Publick House in Sturbridge Mass. As usual we will start at 0900 and wrap up by 4pm. [...] 3669 127 41_[z/OS v8 CM] Update query SQL error -470012_Mark Vickers26_MarkVickers@GROCERYBIZ.COM30_Tue, 9 Jun 2009 10:36:55 -0500352_US-ASCII This dynamic SQL got a -4700 on v8 C.M. from SPUFI & a distributed connection :

UPDATE WW44G.WCSSTWHI WHI SET WHI.STR_ORD_PAL_RD_PCT = 0 WHERE EXISTS (SELECT 1 FROM WW44G.WCSSTITM ITM WHERE ITM.SHP_CSE_FL = 'Y' AND ITM.ITM_NBR = WHI.ITM_NBR) AND WHI.STR_ORD_PAL_RD_PCT > 0

Not sure what to do, so I tried making it a join : [...] 3797 276 45_Re: [z/OS v8 CM] Update query SQL error -470012_Theresa Ryan27_Theresa.Ryan@HUNTINGTON.COM30_Tue, 9 Jun 2009 12:53:16 -0400491_ISO-8859-1 You are in Compatibility Mode and trying to use a "New Function" of Version 8.

¦ -4700 ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE

¦ Explanation: Functions that this release of DB2 introduces cannot be used ¦ before new function mode has been enabled. An attempt was made to execute ¦ one of these functions. In addition, support for extending the length of a ¦ VARCHAR (supported in V7) is restricted in V8 until new function mode has ¦ been enabled. [...] 4074 113 48_Re: Food Taster's Needed: 6/18/09 NEDB2UG Picnic0_28_BILL.GALLAGHER@PHOENIXWM.COM30_Tue, 9 Jun 2009 13:00:32 -0400 4188 71 18_Performance Expert11_Jimmy Auden19_jfauden@HOTMAIL.COM30_Tue, 9 Jun 2009 13:30:18 -0500331_iso-8859-1 I am using the windows desktop DB2 Performance Expert V2 and connecting to Z/OS. When I view threads and try to Explain the SQL I get the error message 'Visual Explain is not installed, or not properly installed, on your system'. I have Visual Explain V8 and can use it stand-alone. How can I integrate it into PE? [...] 4260 394 45_Re: [z/OS v8 CM] Update query SQL error -470012_Mark Vickers26_MarkVickers@GROCERYBIZ.COM30_Tue, 9 Jun 2009 13:46:03 -0500435_ISO-8859-1 Thanks Teresa, I understood that, but maybe I am confused between the rules for v7 and v8, but I am unable to identify what part of the statement is trying to invoke a new function ?







Theresa Ryan Sent by: DB2 Data Base Discussion List 06/09/2009 11:56 AM Please respond to DB2 Database Discussion list at IDUG [...] 4655 97 22_Re: Performance Expert7_Ed Long19_rdhm99a@PRODIGY.NET30_Tue, 9 Jun 2009 11:55:42 -0700316_iso-8859-1 At least with 4.1.0 of PE there is an integrated explain that has to be installed as a bolt on product. Your message may refer to that. I'd look at the PE install manual under the explain section and see. There is a separate install for the front end. Do expect a target rich environment for APAR's. [...] 4753 519 45_Re: [z/OS v8 CM] Update query SQL error -470011_Ron Johnson25_rjohnson@DATA-TRONICS.COM30_Tue, 9 Jun 2009 14:33:59 -0500492_us-ascii Mark, If I remember right it is the correlation variable in the SET clause. Try taking that off and see if that works: SET STR_ORD_PAL_RD_PCT = 0

Ron Johnson Data-Tronics Corp (ABF Freight System)



________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Vickers Sent: Tuesday, June 09, 2009 1:46 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [z/OS v8 CM] Update query SQL error -4700 [...] 5273 573 45_Re: [z/OS v8 CM] Update query SQL error -470012_Mark Vickers26_MarkVickers@GROCERYBIZ.COM30_Tue, 9 Jun 2009 16:00:28 -0500590_ISO-8859-1 THAT WAS IT !

Thanks, Ron.







Ron Johnson Sent by: DB2 Data Base Discussion List 06/09/2009 02:36 PM Please respond to DB2 Database Discussion list at IDUG



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

Subject Re: [DB2-L] [z/OS v8 CM] Update query SQL error -4700











Mark, If I remember right it is the correlation variable in the SET clause. Try taking that off and see if that works: SET STR_ORD_PAL_RD_PCT = 0 [...] 5847 501 49_Re: Group Bufferpool (Critical Shortage of Space)16_Robert Catterall21_rfcatterall@GMAIL.COM30_Tue, 9 Jun 2009 17:48:35 -0400451_ISO-8859-1 I apologize if this response is too late to be of use to you, Rao (I fell way behind on reading DB2-L threads, and I'm now working to get caught up).

Yes, a page set P-lock will be retained by a DB2 subsystem in a data sharing group until the data set on which the P-lock is held is physically closed on that subsystem (while it is being held, the P-lock type could change as a result of P-lock negotiation between subsystems). [...] 6349 111 56_=?GB2312?Q?=B4=F0=B8=B4=3A_?= Re: [DB2-L] usage of alias12_Jeremy Huang23_huangjh@SDC.ICBC.COM.CN31_Wed, 10 Jun 2009 09:14:36 +0800361_GB2312 Thanks for your reply, Coleman.

The RENAME TABLE can do the rename. But it invalidates all the packages and we have to change all the programs to use the new name. So I think it can't be our choice. The number of records is small in the table so we can even do INSERT INTO TABLEB SELECT * FROM TABLEA. So we can accept the small outage time. [...] 6461 256 34_Re: RUNSTATS INLINE STATISTICS SQL16_Robert Catterall21_rfcatterall@GMAIL.COM30_Tue, 9 Jun 2009 22:31:58 -0400373_windows-1252 Apologies for the delayed response - I'm behind on DB2-L stuff.

You don't run RUNSTATS with inline statistics. "Inline statistics" refers to the generation of catalog stats WITHOUT running RUNSTATS - you instead include the STATISTICS keyword (and associated options) in the control statement for a REORG, LOAD, or REBUILD INDEX utility operation. [...] 6718 190 70_Re: z/OS DB2 V8, a question about DB2 disaster recover/restart and GBP16_Robert Catterall21_rfcatterall@GMAIL.COM31_Wed, 10 Jun 2009 10:02:02 -0400562_ISO-8859-1 Sorry about the delayed response.

First of all, I would recommend that, for load-balancing purposes, you place the SECONDARY GBPs in the CF LPAR with the lock structure and the SCA (CF1, in your example), and the primary GBPs in the other CF (CF2, in your example). The primary GBPs are a good bit more active than the secondary because they are used for page registration as well as page caching (the secondary GBPs are only used for page caching). This assumes that the two CFs are relatively equivalent in terms of processing capacity. [...] 6909 77 40_Re: Stored Procedure with 200 parameters9_Rob Stein23_db2list@STEINGLOBAL.NET31_Wed, 10 Jun 2009 10:17:10 -0400369_iso-8859-1 I am actually with your coders on this one.... To me the importance of understanding the parms to a SP is more important then the easier path of stuffing it in a varchar and making the coders deal with it in the code. Will it be a pain in dev when the parms keep changing - for sure but longer term IMO it's far better to have discrete definable parms. [...] 6987 189 60_Re: =?gb2312?Q?=B4=F0=B8=B4=3A_?= Re: [DB2-L] usage of alias15_Coleman, Troy L19_Troy.Coleman@CA.COM31_Wed, 10 Jun 2009 10:58:37 -0400623_gb2312 When you drop a table you will invalidate all the packages also. So in either case you have to rebind the packages. If you rename then create the alias and rebind you will be fine.



Troy Coleman CA Principal Product Manager Phone: 1-630-505-6025 Mobile: 1-847-894-5086 eMail: troy.coleman@ca.com

Blog: http://ibmsystemsmag.blogs.com/db2utor/



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jeremy Huang Sent: Tuesday, June 09, 2009 8:15 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] ´ð¸´: Re: [DB2-L] usage of alias [...] 7177 26 36_Re: Database performance fluctuation17_Alexandre Efremov19_alexandef@GMAIL.COM31_Wed, 10 Jun 2009 15:15:39 +0000549_windows-1251 No, up to my knowledge they did not. The data is being streamed from the large flat data file and change of the sorting order would be too difficult to implement.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA *

There is a discussion list for people who are going to IDUG NA09 in Denver - http://www.idug.org/cgi-bin/wa?A0=DB2-NA-IDUG-CONF-PARTICIPANT _____________________________________________________________________ [...] 7204 50 19_Static SQL in ODBC?14_Peter Vanroose17_pvanroose@ABIS.BE31_Wed, 10 Jun 2009 16:55:39 +0000728_ISO-8859-1 Dear List,

I'm exploring the possibilities of using statically bound SQL in an MS-Windows application (dotNet: ODBC and OLEDB) accessing DB2 for z/OS. Do some of you have experience with this? And comment on setup, complexity, advantages, disadvantages, ...

A first quick search leads me to the "db2cap" command and the page http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.cmd.doc/doc/r0002454.html This seems to indicate that dynamic ODBC queries are "catched" and turned into static in some way or another. This is not what I'm looking for, I believe. Are there other ways to force "static" from within the application, something in the style of SQLJ? [...] 7255 695 39_Fw: Tom Kyte Blogs About COLLABORATE 097_Ed Long19_rdhm99a@PRODIGY.NET31_Wed, 10 Jun 2009 10:07:30 -0700568_iso-8859-1

43% less to administer Oracle 11g than the equivalent 9.5 system. Hmmm, a faster typist perhaps?

Edward Long

--- On Wed, 6/10/09, Oracle Database Insider wrote:



From: Oracle Database Insider Subject: Tom Kyte Blogs About COLLABORATE 09 To: RDHM99A@PRODIGY.NET Date: Wednesday, June 10, 2009, 12:46 PM























INFORMATION INDEPTH NEWSLETTERS Database Insider Edition June 2009 [...] 7951 1685 43_Re: Fw: Tom Kyte Blogs About COLLABORATE 0914_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 10 Jun 2009 13:40:47 -0400462_us-ascii Ed,





When I want to read about Oracle, I know a bunch of places I can go. Can we keep this list on-topic, please?





--Phil Sevetson





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long Sent: Wednesday, June 10, 2009 1:08 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Fw: Tom Kyte Blogs About COLLABORATE 09 [...] 9637 62 36_Re: Database performance fluctuation9_Mike Bell21_mbell11a1@VERIZON.NET31_Wed, 10 Jun 2009 12:32:47 -0500269_US-ASCII

Then your choices are 1. live with the elapsed time 2. do the inserts without an index and built the index later 3. do #2 and then do a reorg to put into the new order 4. go back to the old index and build the new index after the inserts complete [...] 9700 1251 43_Re: Fw: Tom Kyte Blogs About COLLABORATE 097_Ed Long19_rdhm99a@PRODIGY.NET31_Wed, 10 Jun 2009 11:50:54 -0700424_iso-8859-1 Hi Phil. I consider this statement directly on topic since TCO is a major factor in database selection. Sorry you disagree.

Edward Long

--- On Wed, 6/10/09, Sevetson, Phil wrote:



From: Sevetson, Phil Subject: Re: [DB2-L] Fw: Tom Kyte Blogs About COLLABORATE 09 To: DB2-L@WWW.IDUGDB2-L.ORG Date: Wednesday, June 10, 2009, 1:40 PM [...] 10952 168 31_Re: Stored Procedures - ASUTIME16_Robert Catterall21_rfcatterall@GMAIL.COM31_Wed, 10 Jun 2009 17:17:20 -0400580_ISO-8859-1 Sorry about the late response.

The ASUTIME for an external stored procedure is based on the accumulated CPU time charged to the stored procedure's task (TCB). That means that it includes the CPU time for not-in-DB2 processing as well as the CPU time for execution of SQL statements issued by the stored procedure. This also means that ASUTIME includes the CPU time used by any COBOL subprograms invoked by a stored procedure via COBOL call (versus a nested stored procedure call, which would result in a new TCB being used for the called stored procedure). [...] 11121 46 35_Re: DB2 V9 z/OS - LOAD build phase?22_Devyani R Sahasrabudhe22_devyani.sah@IN.IBM.COM31_Thu, 11 Jun 2009 04:59:47 +0000600_windows-1252 Hello,

Please space allocation for index especially PRIQTY and SECQTY. From the error code it looks like there is no enough space in the index pageset.

After sorting the records and loading them to index pageset, space is not enough. Therefore this is giving error in build state.

From the error message, it looks like job has failed in SORT phase. In sort phase, indexes are created and records sorted and loaded into the table as per the index key. As the job is failed while loading the sorted data into the table because of insufficient space in the index, [...] 11168 48 48_Re: Retrieve the result set from Store Procedure22_Devyani R Sahasrabudhe22_devyani.sah@IN.IBM.COM31_Thu, 11 Jun 2009 05:14:13 +0000487_windows-1252 Hello there,

Here is an example; For SQL programming langauge;



DECLARE LOC_CUR RESULT_SET_LOCATOR VARYING ;

CALL Stored Procedure; ASSOCIATE RESULT SET LOCATORS(LOC_CUR) WITH PROCEDURE Stored procedure; Here stored procedure name should be correctly mention same as in Call statement. ALLOCATE Alloc_CUR CURSOR FOR RESULT SET LOC_CUR;



More information is available in IBM Red book Stored Procedures for Z/OS Call and beyond. [...] 11217 27 36_Re: Database performance fluctuation12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 11 Jun 2009 07:00:29 +0000392_windows-1252 You say "The data is being streamed from the large flat data file and change of the sorting order would be too difficult to implement."

Would it really be so hard to include a sort based on the new clustering order?

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 11245 107 70_Re: z/OS DB2 V8, a question about DB2 disaster recover/restart and GBP0_24_hhuang@DCCSH.ICBC.COM.CN31_Thu, 11 Jun 2009 15:47:32 +0800539_US-ASCII Robert

Many thanks for your reply. Maybe my description made something confused, sorry. The assumed scenario is, We run 2-way data sharing with 2 CFs, members named DB2A and DB2B. DB2A is in the BOX#1, DB2B is in the BOX#2. SCA, LOCK1 and Primary GBPs are in the CF#1. Secondary GBPs are in the CF#2. BOX#1 and CF#1 are in the SITE#1, BOX#2 and CF#2 are in the SITE#2. The case is, one day we lose the whole SITE#1. It must be a group restart in SITE#2 using BOX#2 and CF#2. (Because we lose SCA and LOCK1 and DB2A in [...] 11353 40 37_GALLAGHER, BILL is out of the office.0_28_BILL.GALLAGHER@PHOENIXWM.COM31_Thu, 11 Jun 2009 04:01:33 -0400808_US-ASCII I will be out of the office starting 06/11/2009 and will not return until 06/15/2009.

If you have any DB2 questions or problems that need immediate attention, please contact Jean Williams at x5945.



----------------------------------------- ******************************************************************* CONFIDENTIAL: This communication, including attachments, is intended only for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, you are hereby notified that you have received this document in error, and any use, review, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately [...] 11394 29 70_Re: z/OS DB2 V8, a question about DB2 disaster recover/restart and GBP12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 11 Jun 2009 10:37:39 +0000442_windows-1252 If you haven't already done so take a look at the available manuals and redbooks including: DB2 for z/OS: Data Sharing in a Nutshell - SG247322.

I agree with Robert's answer re your scenario not being a true DR one but rather a component or member failure scenario.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * [...] 11424 139 30_Re: REXX, SQL and FOR BIT DATA9_Don Leahy19_don.leahy@LEACOM.CA31_Thu, 11 Jun 2009 08:29:07 -0400620_ISO-8859-1 Does the Rexx variable SYSPACKDEP_BNAME contain any leading/trailing spaces? If so, then make sure to use the STRIP() function to get rid of them before wrapping the value in percent signs when you build your LIKE predicate.

hvSYSPACKSTMT_STMT_LIKE = perct_lit||, STRIP(SYSPACKDEP_BNAME)||, perct_lit

On Mon, Jun 8, 2009 at 1:11 PM, Leland Goldston wrote: > Does anyone have an example of an SQL cursor in REXX where a column has > the FOR BIT DATA attribute? I can get the SQL to work in SPUFI and as well > as DSNTIAUL. But, I can't seem to get the query to work [...] 11564 382 40_HOTDUG Special Event on DB2 for LUW V9.78_Lo, Mary17_Mary.Lo@TGSLC.ORG31_Thu, 11 Jun 2009 15:56:13 -0500334_us-ascii COBRA strikes again. IBM has announced the next release of DB2 for LUW, V9.7 (code name COBRA II). Join us on Tuesday, June 16, 2009. Bob Harbus from Toronto Lab will give your first hand information on this new release. Also learn about next generation of data management and development tools, Data Studio and Mashup. [...] 11947 62 44_SwisSQL Oracle to DB2 conversion - comments?23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Thu, 11 Jun 2009 22:38:11 -0500453_ISO-8859-1 Has anyone on the list used SwisSQL http://www.swissql.com/ migration tools to do an Oracle to DB2 database migration? If you have, were you happy with the results? In the end, did the tool save you sufficient time to justify the licensing cost?

Thanks in advance for your consideration and comments.



Jeffrey Benner eBenner.com Consulting http://www.ebenner.com +001 312 520 0090 http://www.linkedin.com/in/ebenner [...] 12010 47 12_COMMIT QUERY34_SUBSCRIBE DB2-L Tejas V Jadhav_IBM17_tejasvj@GMAIL.COM31_Fri, 12 Jun 2009 04:54:06 +0000363_windows-1252 We are in the process of coding Batch code that takes the Max Count from the TABLE1 on the creator D2TST01 and inserts a row.

The code that updates the table can be called in parallel by 4 other batch programs. We are currently debating where we should commit; be it in the lower level batch program or in the higher level batch program. [...] 12058 68 16_Re: COMMIT QUERY16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Fri, 12 Jun 2009 13:03:30 +0800474_us-ascii Why don't you use a SEQUENCE or an IDENTITY column instead?

Cheers

Greg

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of SUBSCRIBE DB2-L Tejas V Jadhav_IBM Sent: Friday, 12 June 2009 12:54 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] COMMIT QUERY

We are in the process of coding Batch code that takes the Max Count from the TABLE1 on the creator D2TST01 and inserts a row. [...] 12127 27 16_Re: COMMIT QUERY34_SUBSCRIBE DB2-L Tejas V Jadhav_IBM17_tejasvj@GMAIL.COM31_Fri, 12 Jun 2009 05:23:55 +0000719_windows-1252 Hi Greg- thanks for solution - It's 1 way of achieving the max count.

But we are not looking to CHANGE DESIGN . Please can you help with alternative ?

Cheers ! Tejas

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG North America Attendee Testimonial- "The technical content of the conference was excellent and useful. Not only are the "how-to" sessions helpful, it's very beneficial to learn about new features in upcoming releases." _____________________________________________________________________ 12155 23 35_Re: DB2 V9 z/OS - LOAD build phase?34_SUBSCRIBE DB2-L Tejas V Jadhav_IBM17_tejasvj@GMAIL.COM31_Fri, 12 Jun 2009 05:28:06 +0000637_windows-1252 NINAD-

Please can you send the list cat for : DSNCAT.DSNDBC.DPGDBPA2.IATEPR90.I0001.A001 ?

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG North America Attendee Testimonial- "The technical content of the conference was excellent and useful. Not only are the "how-to" sessions helpful, it's very beneficial to learn about new features in upcoming releases." _____________________________________________________________________ 12179 65 16_Re: COMMIT QUERY15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Fri, 12 Jun 2009 09:06:35 +0300473_us-ascii On Fri, 12 Jun 2009 04:54:06 +0000 SUBSCRIBE DB2-L Tejas V Jadhav_IBM wrote:

:>We are in the process of coding Batch code that takes the Max Count from :>the TABLE1 on the creator D2TST01 and inserts a row.

:>The code that updates the table can be called in parallel by 4 other batch :>programs. We are currently debating where we should commit; be it in the :>lower level batch program or in the higher level batch program. [...] 12245 68 16_Re: COMMIT QUERY14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 12 Jun 2009 18:26:51 +1000394_US-ASCII If you put a WITH RR on the select, then the table should be locked until the commit - so program 2 will wait for the commit.

If you have a unique index on count, then it doesn't matter what the sequence of inserts is. An attempt to re-insert a value will fail with -803 (after waiting for the commit), so the failing program should just up the value by one and try again. [...] 12314 71 16_Re: COMMIT QUERY15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Fri, 12 Jun 2009 11:45:01 +0300549_us-ascii On Fri, 12 Jun 2009 18:26:51 +1000 James Campbell wrote:

:>If you put a WITH RR on the select, then the table should be locked until the commit - so :>program 2 will wait for the commit.

Wouldn't that serialize even more activities?

:>If you have a unique index on count, then it doesn't matter what the sequence of inserts is. :>An attempt to re-insert a value will fail with -803 (after waiting for the commit), so the failing :>program should just up the value by one and try again. [...] 12386 26 16_Re: COMMIT QUERY34_SUBSCRIBE DB2-L Tejas V Jadhav_IBM17_tejasvj@GMAIL.COM31_Fri, 12 Jun 2009 12:07:44 +0000648_windows-1252 Hi Jon-

Thank for your suggetion it's very helpful, it did worked out for us.

Cheers ! Tejas J

_____________________________________________________________________

* IDUG 09 Rome, Italy * October 5-9, 2009 * http://IDUG.ORG/EU *

_____________________________________________________________________

IDUG North America Attendee Testimonial- "The technical content of the conference was excellent and useful. Not only are the "how-to" sessions helpful, it's very beneficial to learn about new features in upcoming releases." _____________________________________________________________________ 12413 85 40_Re: Stored Procedure with 200 parameters13_Mackey, Glenn20_GMackey@GUIDEONE.COM31_Fri, 12 Jun 2009 07:36:46 -0500360_us-ascii I agree with "exposing" (my oo term for the day) all of the parameters. One method to manage the large number of parameters is to use a table.

Insert the parameter data in a table with nicely named columns with a unique id, then call the Sp with the unique parameter. The SP can then access the table using the unique id which was passed. [...] 12499 41 30_Re: REXX, SQL and FOR BIT DATA15_Leland Goldston23_lgoldston@EARTHLINK.NET31_Fri, 12 Jun 2009 15:25:49 +0000544_windows-1252 THANKS for the suggestion.

I tried the STRIP function and same result.

hvSYSPACKSTMT_STMT_LIKE = perct_lit||, STRIP(hvSYSPACKDEP_BNAME)||, perct_lit

say '==>' hvSYSPACKSTMT_STMT_LIKE length(hvSYSPACKSTMT_STMT_LIKE)



SAY..... ==> %TMFDD00_OBJECTS% 17



I have even tried the CAST function on both sides of the like predicate. Still same result. I am wondering if this is similar to COBOL program where the % sign needs to filled to the maximum length of the STMT or some flavor. [...] 12541 62 30_Re: REXX, SQL and FOR BIT DATA9_Chris Tee23_Chris.Tee@SENTENIAL.COM31_Fri, 12 Jun 2009 16:41:09 +0100401_us-ascii Leland

I missed the start of this thread, what result do you get?

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Leland Goldston Sent: 12 June 2009 16:34 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] REXX, SQL and FOR BIT DATA

THANKS for the suggestion.

I tried the STRIP function and same result. [...] 12604 62 31_DB2 for z/OS partitioned reorgs18_Sandra Lakenburger27_sandra.lakenburger@USDA.GOV31_Fri, 12 Jun 2009 16:57:45 +0000374_windows-1252 Good morning,

My DBAs are re-evaluating their partitioned tablespace reorgs now that we're getting v9 implemented and have a couple of questions. In the past they have reorged multiple partitions concurrently using multiple concurrent jobs. With the BUILD2 phase being eliminated in v9, the NPIs defined are causing them to rethink this philosophy. [...] 12667 72 59_Re-link to convert Stored Proc from SPAS to WLM not working4_Amar18_amar_moh@YAHOO.COM31_Fri, 12 Jun 2009 17:37:50 +0000600_windows-1252 Hi,

As part of converting stored proc from SPAS to WLM I am trying to re-link the application program load module used in SPAS to use RRSAF using the following JCL. But its not working.

The JCL as per Application Guide is,

//LINKRRS EXEC PGM=IEWL, // PARM='LIST,XREF,MAP' //SYSPRINT DD SYSOUT=* //SYSLIB DD DISP=SHR,DSN=Myuserid.LOAD // DD DISP=SHR,DSN=SYS1.DB2.LOAD //SYSLMOD DD DISP=SHR,DSN=Myuserid.LOAD //SYSUT1 DD SPACE=(1024,(50,50)),UNIT=SYSDA //SYSLIN DD * ENTRY TESTPROC REPLACE DSNALI INCLUDE SYSLIB(DSNRLI) INCLUDE SYSLMOD(TESTPROC) NAME TESTPROC(R) [...] 12740 84 82_June 19th NY - DB2 Connect and DB2 in the Cloud - Leon Katsnelson, IBM Toronto Lab10_anne Stout23_anne.stout@DOWJONES.COM31_Fri, 12 Jun 2009 17:58:26 +0000 12825 455 86_Re: June 19th NY - DB2 Connect and DB2 in the Cloud - Leon Katsnelson, IBM Toronto Lab12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Fri, 12 Jun 2009 14:04:46 -0400962_us-ascii I highly recommend this session, I attended it at IDUG Denver. pg













Phil Gunning

IBM Data Champion

www.gunningts.com

Twitter:@db2luw

See us at IDUG Europe, 5-9 Oct, 2009 Rome, Italy

Database Industry Analyst

DB2 Consulting, Books on DB2, Outsourcing, Education, Remote Support

http://it.toolbox.com/blogs/db2-for-luw

DB2 9.7 Early Access Program --

http://www-01.ibm.com/software/data/db2/technology-sandbox/

Direct +1.610.451.5801

Toll Free 888.241.1070

pgunning@gunningts.com

IBM Business Partner

IBM Authorized Reseller

This message (including attachments, header and footer details) contains confidential information intended for a specific individual or individuals and purpose(s), and is protected by law. If you are not the intended recipient, you should delete this message and any [...] 13281 238 86_Re: June 19th NY - DB2 Connect and DB2 in the Cloud - Leon Katsnelson, IBM Toronto Lab13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 12 Jun 2009 14:25:23 -0400455_us-ascii I agree.

Leon is an excellent presenter.





His session on DB2 in the Cloud was one of the top sessions at IDUG for me.





Dave

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Gunning Sent: Friday, June 12, 2009 2:05 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] June 19th NY - DB2 Connect and DB2 in the Cloud - Leon Katsnelson, IBM Toronto Lab [...] 13520 73 30_Re: REXX, SQL and FOR BIT DATA9_Don Leahy19_don.leahy@LEACOM.CA31_Fri, 12 Jun 2009 14:25:45 -0400343_ISO-8859-1 The following sequence of statements works just fine for me:

test_name = "%TRO_OPTN%"

SQLSTMT = , " SELECT STMT FROM SYSIBM.SYSPACKSTMT " , " WHERE LOCATION = ' ' " , " AND COLLID = 'TST_RO_ONLINE' " , " AND STMT LIKE ?"

... some stuff omitted....

Address DSNREXX "EXECSQL OPEN C1 using :test_name" [...] 13594 31 63_Re: Re-link to convert Stored Proc from SPAS to WLM not working11_Pam Shepard27_pshepard@HASTINGSMUTUAL.COM31_Fri, 12 Jun 2009 18:53:35 +0000368_windows-1252 I remember having that same problem when we converted our stored procedures. I was never able to get it to work with a re-link as the documentation suggested. To get around the problem, we did all of the steps: pre-compile, compile, link (with DSNRLI), bind.

Probably not what you wanted to hear, and maybe someone else has a better solution. [...] 13626 74 63_Re: Re-link to convert Stored Proc from SPAS to WLM not working9_Don Leahy19_don.leahy@LEACOM.CA31_Fri, 12 Jun 2009 15:45:17 -0400655_ISO-8859-1 Was the program recompiled with ATTACH(CAF)?



On Fri, Jun 12, 2009 at 1:37 PM, Amar wrote: > Hi, > > As part of converting stored proc from SPAS to WLM I am trying to re-link the > application program load module used in SPAS to use RRSAF using the > following JCL. But its not working. > > The JCL as per Application Guide is, > > //LINKRRS EXEC PGM=IEWL, > // PARM='LIST,XREF,MAP' > //SYSPRINT DD SYSOUT=* > //SYSLIB DD DISP=SHR,DSN=Myuserid.LOAD > // DD DISP=SHR,DSN=SYS1.DB2.LOAD > //SYSLMOD DD DISP=SHR,DSN=Myuserid.LOAD > //SYSUT1 DD SPACE=(1024,(50,50)),UNIT=SYSDA > //SYSLIN DD * > ENTRY TESTPROC [...] 13701 82 63_Re: Re-link to convert Stored Proc from SPAS to WLM not working9_Don Leahy19_don.leahy@LEACOM.CA31_Fri, 12 Jun 2009 15:50:30 -0400588_ISO-8859-1 Oops. I meant *pre*compiled not recompiled.

We don't use ATTACH(CAF), so all we had to do is relink with INCLUDE DSNRLI.



On Fri, Jun 12, 2009 at 3:45 PM, Don Leahy wrote: > Was the program recompiled with ATTACH(CAF)? > > > On Fri, Jun 12, 2009 at 1:37 PM, Amar wrote: >> Hi, >> >> As part of converting stored proc from SPAS to WLM I am trying to re-link the >> application program load module used in SPAS to use RRSAF using the >> following JCL. But its not working. >> >> The JCL as per Application Guide [...] 13784 92 63_Re: Re-link to convert Stored Proc from SPAS to WLM not working9_Mike Bell21_mbell11a1@VERIZON.NET31_Fri, 12 Jun 2009 14:51:23 -0500393_us-ascii The REPLACE has to immediately precede the load module you want to modify - in this case TESTPROC So what you need is ENTRY TESTPROC REPLACE DSNALI INCLUDE SYSLMOD(TESTPROC)

INCLUDE SYSLIB(DSNRLI) NAME TESTPROC(R)

It is covered in link edit manual which is now called binder or program manager or something. I assume from your comment that the DB2 manual is wrong? [...] 13877 72 63_Re: Re-link to convert Stored Proc from SPAS to WLM not working4_Amar18_amar_moh@YAHOO.COM31_Sat, 13 Jun 2009 00:09:55 +0000470_windows-1252 Mike/Don,

I tried that, it didn't help as shown below. Yes, originally it was precompiled with CAF.

//SYSLIN DD * ENTRY TESTPROC REPLACE DSNALI INCLUDE SYSLMOD(TESTPROC) INCLUDE SYSLIB(DSNRLI) NAME TESTPROC(R)

output IEW2322I 1220 1 ENTRY TESTPROC IEW2322I 1220 2 REPLACE DSNALI IEW2322I 1220 3 INCLUDE SYSLMOD(TESTPROC) IEW2400I 5D10 A REQUEST TO DELETE CSECT OR SYMBOL DSNALI WAS RECEIVED, AND THE CSECT OR SYMBOL WAS NOT FOUND. [...] 13950 84 63_Re: Re-link to convert Stored Proc from SPAS to WLM not working9_Don Leahy19_don.leahy@LEACOM.CA31_Sat, 13 Jun 2009 00:06:58 -0400610_ISO-8859-1 ATTACH(CAF) causes the precompiler to generate calls to DSNHLI2 instead of DSNHLI.

DSNHLI2 is an alias of DSNALI, so I think you need to re-compile the program without using the ATTACH option, and then relink using DSNRLI.

On Fri, Jun 12, 2009 at 8:09 PM, Amar wrote: > Mike/Don, > > I tried that, it didn't help as shown below. Yes, originally it was precompiled > with CAF. > > //SYSLIN DD * > ENTRY TESTPROC > REPLACE DSNALI > INCLUDE SYSLMOD(TESTPROC) > INCLUDE SYSLIB(DSNRLI) > NAME TESTPROC(R) > > output > IEW2322I 1220 1 ENTRY TESTPROC > IEW2322I 1220 [...] 14035 136 48_Re: SwisSQL Oracle to DB2 conversion - comments?14_Matthew Donald26_matthew.b.donald@GMAIL.COM31_Sat, 13 Jun 2009 16:32:43 +1000538_ISO-8859-1 I have used a similar open source product SQLFairyto convert Oracle data to DB2 LUW. SQLFairy is a 'Swiss Army knife' for database. It provides any-to-any conversions between DB2, Oracle, Sybase, SQLServer, SQLite, MySQL, PostgreSQL, XML and CSV. It will read the database definitions from one type of database and generate equivalent definitions for the other. SQLFairy also provides some filtering, schema manipulation and diff tools as well. There is a very usable data transfer tool. [...] 14172 54 30_Re: REXX, SQL and FOR BIT DATA14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 13 Jun 2009 16:37:43 +1000609_US-ASCII Can we see the rest of your REXX code - like the PREPARE and the OPEN?

James Campbell

On 12 Jun 2009 at 15:25, Leland Goldston wrote:

> THANKS for the suggestion. > > I tried the STRIP function and same result. > > hvSYSPACKSTMT_STMT_LIKE = perct_lit||, > STRIP(hvSYSPACKDEP_BNAME)||, > perct_lit > > say '==>' hvSYSPACKSTMT_STMT_LIKE length(hvSYSPACKSTMT_STMT_LIKE) > > > SAY..... > ==> %TMFDD00_OBJECTS% 17 > > > I have even tried the CAST function on both sides of the like predicate. Still > same result. I am wondering if this is similar to COBOL program where the % > [...] 14227 81 44_SwisSQL Oracle to DB2 conversion - comments?23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Thu, 11 Jun 2009 22:38:11 -0500453_ISO-8859-1 Has anyone on the list used SwisSQL http://www.swissql.com/ migration tools to do an Oracle to DB2 database migration? If you have, were you happy with the results? In the end, did the tool save you sufficient time to justify the licensing cost?

Thanks in advance for your consideration and comments.



Jeffrey Benner eBenner.com Consulting http://www.ebenner.com +001 312 520 0090 http://www.linkedin.com/in/ebenner [...] 14309 137 63_Re: Re-link to convert Stored Proc from SPAS to WLM not working9_Mike Bell21_mbell11a1@VERIZON.NET31_Sat, 13 Jun 2009 09:46:43 -0500452_us-ascii Just reading the error messages - the origional TESTPROC had DSNELI so it was not precompiled with ATTACH(CAF)

The tool to identify this without multiple passes with LKED is AMBLIST JCL looks like //LIST EXEC PGM=AMBLIST //SYSLIB DD DISP=SHR,DSNAME=your load library //SYSPRINT DD SYSOUT=* //SYSIN DD * LISTLOAD MEMBER=TESTPROC,OUTPUT=XREF What you are looking for is what csect has the DSNHLI and is there a reference to DSNHLI2. [...] 14447 176 48_Re: SwisSQL Oracle to DB2 conversion - comments?23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Sat, 13 Jun 2009 11:22:55 -0500700_ISO-8859-1 Thanks Matthew! Unfortunately it appears that SQLFairy hasn't been updated in 4 years.

On Sat, Jun 13, 2009 at 01:32, Matthew Donald wrote:

> I have used a similar open source product SQLFairyto convert Oracle data to DB2 LUW. > SQLFairy is a 'Swiss Army knife' for database. It provides any-to-any > conversions between DB2, Oracle, Sybase, SQLServer, SQLite, MySQL, > PostgreSQL, XML and CSV. It will read the database definitions from one > type of database and generate equivalent definitions for the other. > SQLFairy also provides some filtering, schema manipulation and diff tools > as well. There is [...] 14624 258 48_Re: SwisSQL Oracle to DB2 conversion - comments?14_Matthew Donald26_matthew.b.donald@GMAIL.COM31_Sun, 14 Jun 2009 03:22:01 +1000685_ISO-8859-1 The web page may not have been updated, but there have been constant updates to the code, as recently as a five days ago. The tracker shows that in June there were 23 updates to 119 files. In May there 27 updates to 71 files. It's a pretty active project. Bugs are being reported and fixed- the last fix was two months ago. The nice thing about open source code is that if you hit a show stopping bug, you can fix it yourself. There is a developers mailing listwhere issues and problems can be discussed. This product has been [...] 14883 77 26_DB2 DRIVER(DBM1 cpu usage)13_Laila hosaini19_lshosaini@YAHOO.COM31_Sat, 13 Jun 2009 23:08:30 -0700551_iso-8859-1

Hi Dear Listers

I had problems in DBM1 cpu usage,I gathered ACCOUNTING report class(1,2,3)

and then generate TOPREF (TOP PREFETCH) reports,I saw users, who use DB2 DRIVER or Embarcadero DB2 Driver,to connect to DDF has most PREFETCH . PREFETCH from this environment is 1,000,000 times more than others.

I ran EXPLAIN on all of user SQL statement ,all of them are OK. I think problem is DRIVER 's configuration.but I don't know ,how can i change it? I tried on DB2CLI.INI file ,but couldn't find any thing. [...]