1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l July 2002, week 2
2 12 34_Re: Identifying changes to a table33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Mon, 8 Jul 2002 01:51:11 -0500429_- If you are on Version 7 you can install the real time statistics. It's an enhancement of DB2 and it is for free (with the exception of additional CPU costs and DASD space)
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
15 128 79_Re: How do you resolve a SQL -904 code, Reason-CODE 00D70025, Resource type 22014_Massimo Scarpa16_mocion@INWIND.IT30_Mon, 8 Jul 2002 09:55:25 +0200379_us-ascii Hi
I'm on holidays so I've no error message manual, but if I remember well 0D007xx (with xx=14 or xx=25 in general) is a problem related to DB2 VSAM dataset which is unable to extend or to allocate a shadow copy ie it's mainly a DASD space problem. Probably when you allocate shadow datasets there isn't enough space for the shadow ds or they cannot extent. [...]
144 25 32_One plan many packages db2 os39019_Briggs, N. - Neil -28_Neil.Briggs@CANON-EUROPE.COM30_Mon, 8 Jul 2002 09:52:46 +0200348_iso-8859-1 Dear Lister's
There has been a suggestion at our site to move toward one big plan (2 plans really - one for on line and one for batch) for our application. Bound into this plan will be all application packages (about 4000). I remember that in version 5 of DB2 that this could cause a problem with excessive scans of the SCT. [...]
170 16 51_David Ayers/ISG/CORP/Highmark is out of the office.11_David Ayers24_david.ayers@HIGHMARK.COM30_Mon, 8 Jul 2002 05:31:50 -0400494_us-ascii I will be out of the office starting 07/08/2002 and will not return until 07/09/2002.
For DB2 OS/390 assistance contact your support DBA. For Datajoiner / DB2 connect support contact Chuck Krall. Jon Capelli, Dawn Kastelic
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
187 111 78_Re: How do you resolve a SQL -904 code, Reason-CODE 00D70025,Resource type 22015_Patrick Hignett33_Patrick.Hignett@MORGANSTANLEY.COM30_Mon, 8 Jul 2002 11:00:10 +0100570_us-ascii Linda You are probably failing to find the space to allocate the primary extent for the "shadow " dataset so try altering the priqty on the tablespace and then run you online reorg.
Linda Ayeni wrote:
> Hi Dr. Ebert, > Glad to know your you are up and about today (lucky me!) and thanks for the > educational pointer! Okay, so, my process abended with "unable to allocate > the shadow copy", I terminated REORG process, and tried to start the process > (ignorance 101) afresh with same result. Looked up the message as you > described (now I [...]
299 12 36_Re: One plan many packages db2 os39033_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Mon, 8 Jul 2002 05:06:24 -0500346_- Neil
There has been a lof of discussion regarding that item. Look in the archive.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
312 21 45_IOS000I on Archive Tape for Backward Recovery14_Harvey Puckett19_hpuckett@GTA.GA.GOV30_Mon, 8 Jul 2002 08:07:30 -0400586_US-ASCII Anybody out there ever see the following during recovery processing of DB2 subsystem :
"IOS000I 0B40,08,DCK,0C,0600,,**,350335,DBR1MSTR 407 0840AC234000F92000BD(7DD17DD17DD1BDBD)00E9(02004088)4E8F372573110000"
Yes, we archive to tape!#!@#
The repercussions are major!
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
334 55 49_Re: IOS000I on Archive Tape for Backward Recovery11_Moore, Tony15_TMoore@IKON.COM30_Mon, 8 Jul 2002 08:55:41 -0400421_iso-8859-1 Harvey, This may or may not be related, but it might point you in the right direction...
Awhile back, we had a bug in our tape management software (RMM) that would not allow backward reading of our archive log tapes. There's been too much water over the bridge (as they say) since then to remember the PTF number that corrected the problem, but you may want to check your tape management software. [...]
390 68 34_Re: Identifying changes to a table14_Harvey Puckett19_hpuckett@GTA.GA.GOV30_Mon, 8 Jul 2002 09:04:36 -0400414_US-ASCII If no sophisticated tools onsite, repro SYSLOGRNX to a wok dataset and parse that with a rexx to find the dbid/obid/psid for the time frame you are interested in ... the layout is in the diag guide ...
-----Original Message----- From: Sanjeev .... [mailto:sanjeev_db2l@YAHOO.CO.IN] Sent: Friday, July 05, 2002 4:09 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Identifying changes to a table [...]
459 58 32_Re: Elevon on OS/390 with DB2 V70_17_cflink@AWGINC.COM30_Mon, 8 Jul 2002 08:22:11 -0500684_us-ascii We are currently running walker 17.01 on db2 os390 v7 with no problems....
DBAS To: DB2-L@LISTSERV.YLASSOC.COM Sent by: DB2 Data cc: Base Discussion Subject: Elevon on OS/390 with DB2 V7 List
07/04/02 10:24 AM Please respond to DB2 Data Base Discussion List
We are in the process of making plans to migrate DB2 for OS390 from V5 to V7. We are currently running Walker V17.01 and QSP 4.6.1. We have contacted the supplier requesting confirmation that both products (Walker & QSP) are DB2 V7 compliant, their answer was very non committal. [...]
518 98 34_Re: Identifying changes to a table11_Judy Kibler21_judith.kibler@TRW.COM30_Mon, 8 Jul 2002 08:29:07 -0500694_us-ascii Are there any such tools for DB2 on the UNIX platforms?
Thanks, Judy
Harvey Puckett on 07/08/2002 08:04:36 AM
Please respond to DB2 Data Base Discussion List
To: DB2-L@LISTSERV.YLASSOC.COM
cc: (bcc: Judy Kibler/Nashville)
Subject: Re: Identifying changes to a table
If no sophisticated tools onsite, repro SYSLOGRNX to a wok dataset and parse that with a rexx to find the dbid/obid/psid for the time frame you are interested in ... the layout is in the diag guide ... [...]
617 126 31_Re: BMC tools and DB2 version 711_Hayden, Lee18_Lee_Hayden@CSX.COM30_Mon, 8 Jul 2002 10:03:17 -0400367_iso-8859-1 I notice that BMC COPY+ also uses the IBM QUIESCE utility. We run SHRLEVEL ANY and this usually means BMC will take a SHRLEVEL CHANGE copy. The quiesce then seems a good idea. Is there any way to get COPY+ to NOT use QUIESCE ? We also use DB2 Change ACCUM and those QUIESCE points seem required for a successful recovery with that product as well.... [...]
744 35 36_DB2 certification materials question15_Sharpe, Richard20_rsharpe@AMHERST1.COM30_Mon, 8 Jul 2002 10:25:07 -0400422_iso-8859-1 Hi
Can anyone tell me where the free certification materials are located, I was talking to another DBA from a different company and they said IBM was giving away cd's and books for the certification exams to help prepare for them, they had a IBM book "DB2 Universal Database v7.1 Database Administration Guide for Unix, Linux, Windows and OS/2" that was free when you register but I did not see it. [...]
780 33 34_Re: Identifying changes to a table11_Jim Addison28_jim_addison@STANDARDLIFE.COM30_Mon, 8 Jul 2002 15:33:08 +0100387_iso-8859-1 Further to Sanjeev's comments, it is possible to use the start/end timestamps to determine LRSNSTART and LRSNEND parameters (even for non-DS) which combined with the DBID and OBID parameters relating to TABLE_As tablespace will give the records you require from DSN1LOGP.
You may only want the SUMMARY records or the data records SUBTYPE(1) DATAONLY(YES) I think. [...]
814 49 33_Re: UPDATE table WHERE CURRENT OF13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Mon, 8 Jul 2002 08:31:51 -0700419_iso-8859-1 Read an interesting recommendation the other day that if the index being updated is a clustering index, always prefer delete-insert over update. This is to preserve the data clustering.
Thanks. Ashish.
-----Original Message----- From: Roger Ghose [mailto:roger.ghose@MTO.GOV.ON.CA] Sent: Friday, July 05, 2002 12:00 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: UPDATE table WHERE CURRENT OF [...]
864 177 31_Re: BMC tools and DB2 version 715_Murley, Michael22_Michael_Murley@BMC.COM30_Mon, 8 Jul 2002 10:34:02 -0500593_iso-8859-1 Lee,
If you are seeing that COPY PLUS uses the Quiesce Utility during a SHRLEVEL CHANGE copy, and you want it to stop, I encourage you to contact our Technical Support staff. We should be able to help.
COPY PLUS uses the Quiesce Utility during a SHRLEVEL CHANGE copy when it discovers that it is otherwise unable to register a valid SHRLEVEL CHANGE image copy. There is an install option that controls whether or not we use the Quiesce Utility in those situations. If that option is set to NO and the utility cannot otherwise discover a valid registration point, [...]
1042 12 23_REXX support download ?17_Christophe Radier17_c300501@YAHOO.COM30_Mon, 8 Jul 2002 11:01:57 -0500474_- Do you know where i can download the free FMID JDB771H (REXX DB2 V7) - we have not ordered it at the product order , and our intermediate sales guy want us to pay for this specific product pack (installation fees ! )
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
1055 91 33_Re: UPDATE table WHERE CURRENT OF0_26_truman.g.brown@VERIZON.COM30_Mon, 8 Jul 2002 12:28:15 -0400338_iso-8859-1 I'm always a little leery of recommendations like this. Data access may not be strictly sequential and the DELETE/INSERT method uses more CPU, causes more more bufferpool activity and log records to be written, etc. Also, unless PCTFREE or FREESPACE is available the row may not be placed optimally via the INSERT anyway. [...]
1147 13 27_Re: REXX support download ?9_Jim Ruddy18_jaruddy@US.IBM.COM30_Mon, 8 Jul 2002 11:52:02 -0500351_- The REXX support is not available via download.
Jim Ruddy IBM DB2 for z/OS Development
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
1161 21 40_Re: DB2 certification materials question17_Denis Vasconcelos28_denis.vasconcelos@PBT.COM.BR30_Mon, 8 Jul 2002 14:33:18 -0300518_iso-8859-1 This only happens when you register into the Scholar Program, this program are only to universities professors.
Denis Vasconcelos DBA - Fábrica de Software Portais Brasil Tecnologia S.A. 0 XX 81 3465 - 3211 Ramal 31 Denis.Vasconcelos@pbt.com.br
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
1183 12 11_unsubscribe13_Mike P Martin22_mike.p.martin@AEXP.COM30_Mon, 8 Jul 2002 10:27:42 -0700258_-
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
1196 156 26_Help With Catalog Query on18_Blake, Betty@HHSDC19_BBlake@HHSDC.CA.GOV30_Mon, 8 Jul 2002 10:36:11 -0700566_- DB2 for OS390 V6. I would appreciate any help you can give me on the query below.
The query below was provided by IBM with a PTF in the maintenance I'm installing. I have included the description of it's purpose from the hold report.
In one of our customer's subsystems, it uses all the space in DSNDB07 just in the subselect without the group by. Omegamon shows over 4 million getpages. There are only 228 partitioned tablespaces in this subsystem and none of them have an excessive number of columns in the key. Even when I limit the query to [...]
1353 128 33_Re: UPDATE table WHERE CURRENT OF13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Mon, 8 Jul 2002 10:49:36 -0700334_iso-8859-1 Actually I think it is a recommendation which should be actively considered. The fact that an index column(s) is being updated generally tells me that it is not a 'mass update' situation and so the application as such will not notice any significant difference in response time if UPDATE is replaced by DELETE/INSERT. [...]
1482 22 20_Re: SPB sqlcode -10417_Walter Trovijo Jr18_wtrovijo@IG.COM.BR30_Mon, 8 Jul 2002 14:32:20 -0300503_iso-8859-1 That´s the problem Terry, SPB issues this SQL statement during connect to DB2 OS390...
> Walter, > > I can't speak for SPB, but your SQL statement is missing a column name on > the right side of the '='. > > Terry Purcell
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
1505 40 40_Re: DB2 certification materials question15_Sharpe, Richard20_rsharpe@AMHERST1.COM30_Mon, 8 Jul 2002 14:13:02 -0400565_iso-8859-1 I guess he lied then as he is no professor.
-----Original Message----- From: Denis Vasconcelos [mailto:denis.vasconcelos@PBT.COM.BR] Sent: Monday, July 08, 2002 1:33 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: [DB2-L] DB2 certification materials question Importance: High
This only happens when you register into the Scholar Program, this program are only to universities professors.
Denis Vasconcelos DBA - Fábrica de Software Portais Brasil Tecnologia S.A. 0 XX 81 3465 - 3211 Ramal 31 Denis.Vasconcelos@pbt.com.br [...]
1546 68 40_Re: DB2 certification materials question22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR30_Mon, 8 Jul 2002 15:22:40 -0300631_iso-8859-1 Richard,
Try this link out: http://www-3.ibm.com/software/data/db2/selfstudy/
It may not be exacly what you're looking for but it might help you anyway.
Good luck, Claudio **************************************** Claudio Meluzzi Mendes BANCO BEG S.A. Goiania - Goias - BRAZIL claudio@beg.com.br cmeluzzi@uol.com.br ****************************************
----- Original Message ----- From: "Sharpe, Richard" Newsgroups: bit.listserv.db2-l To: Sent: Monday, July 08, 2002 11:25 AM Subject: DB2 certification materials question [...]
1615 18 0_10_muthu samy23_muthu_samy1@HOTMAIL.COM30_Mon, 8 Jul 2002 13:28:53 -0500459_iso-8859-1 Hi All, I have a HTML type file stored in database. I want to get rid of all tags from HTML file using Query( Select). Is it any function available to do this?
Thanks in advance, Muthu.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
1634 33 0_14_Larry Kintisch19_lkint@IX.NETCOM.COM30_Mon, 8 Jul 2002 14:49:15 -0400563_us-ascii I suggest you re-submit this with a "SUBJECT" line. LK
At 01:28 PM 7/8/02 -0500, you wrote: >Hi All, > I have a HTML type file stored in database. I want to get rid of all >tags from HTML file using Query( Select). >Is it any function available to do this? > >Thanks in advance, >Muthu. > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. > [...]
1668 20 33_Re: UPDATE table WHERE CURRENT OF11_Roger Ghose25_roger.ghose@MTO.GOV.ON.CA30_Mon, 8 Jul 2002 13:54:45 -0500284_- Sanjeev, Thanks for your reply.
Yes we are trying to update the same index in which we have "WHERE CURRENT OF" during an open cursor. I might add that this is a batch job and not an online transaction. Will this make a difference? also it is not a partition tablespace. [...]
1689 27 27_to execute SQL concurrently11_Todd Martin15_tm9us@YAHOO.COM30_Mon, 8 Jul 2002 12:04:07 -0700386_us-ascii Hi All,
Is their anyway thru JCL or any command in JCL to run execute the SQL,ten times concurrently at a time .I would like to capture some perfomance results .
Your help would be highly appreciated
TIA, Todd
__________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com [...]
1717 184 30_Re: Help With Catalog Query on12_Isaac Yassin20_yassini@BEZEQINT.NET30_Mon, 8 Jul 2002 22:06:06 +0200283_- Hi,
"Few" things are missing: 1 - DBNAME (on the join) - means you join all TS with same name from all DBs . 2 - IXCREATOR (on the join) - same as above ... 3- TBCREATOR (on the join) - same as above ...........
You got yourself a multiple Cartesian product ... [...]
1902 30 60_Change in default for DB2 CAE Version 7.1 WR21254 Intended ?11_Sinha, Amit19_Sinha.Amit@MBCO.COM30_Mon, 8 Jul 2002 14:09:35 -0500549_iso-8859-1 Hi,
We connect to DB2 on OS/390 thru DB2connect on AIX. We just moved to DB2 CAE 7.1. In the new version of CAE when we do add database using client configuration assistant using Search the Network option and using Known Systems.
The default in CAE 6.1 used to be Connect to the server via the gateway, now in CAE 7.1 the default is connect directly. Why the Change ? It's really giving us a hard time as most of the user's are not CAE user friendly and they just use the defaults which makes the client directly go to [...]
1933 211 30_Re: Help With Catalog Query on16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM30_Mon, 8 Jul 2002 12:43:11 -0700822_us-ascii Betty,
Try this SQL:
SELECT CREATOR, TBNAME, SUM(PARTS*LENGTH)+PARTS*106+140 AS SIZE FROM (SELECT E.TBCREATOR AS CREATOR, D.TBNAME AS TBNAME, C.COLNAME, B.PARTITIONS AS PARTS, E.LENGTH AS LENGTH FROM SYSIBM.SYSTABLEPART A, SYSIBM.SYSTABLESPACE B, SYSIBM.SYSKEYS C, SYSIBM.SYSINDEXES D, SYSIBM.SYSCOLUMNS E, SYSIBM.SYSTABLES F WHERE A.TSNAME=B.NAME AND B.PARTITIONS > 0 AND AND A.DBNAME=B.DBNAME AND F.DBNAME = A.DBNAME AND F.TSNAME = A.TSNAME AND D.TBCREATOR = F.CREATOR AND D.TBNAME = F.NAME AND A.IXCREATOR = D.CREATOR AND E.TBNAME = F.NAME AND E.TBCREATOR = F.CREATOR AND C.IXCREATOR = D.CREATOR AND A.IXNAME=C.IXNAME AND C.IXNAME=D.NAME AND D.TBNAME=E.TBNAME AND C.COLNAME=E.NAME GROUP BY E.TBCREATOR, D.TBNAME, C.COLNAME, B.PARTITIONS, E.LENGTH) AS LIMITKEYCHECK GROUP BY CREATOR, TBNAME, [...]
2145 71 39_Re: AVOID SORT WHILE LOADING PARTITIONS11_Roger Ghose25_roger.ghose@MTO.GOV.ON.CA30_Mon, 8 Jul 2002 14:23:23 -0500726_- Sanjeev, Thanks for your reply. The partition INDEX has the following columns and partitions:
CREATE UNIQUE INDEX "usergroup"."index1" ON "usergroup"."tablename" ("DATE_BUSINESS" DESC ,"USER_ID" ASC ,"SYSTEM_TIMESTAMP" DESC ,"LOG_CODE" ASC ) CLUSTER CLUSTER (PART 1 VALUES ('2002-12-31') USING STOGROUP "TSSXOJG" PRIQTY 360000 SECQTY 36000 ERASE NO FREEPAGE 0 PCTFREE 0 ,PART 2 VALUES ('2002-06-30') USING STOGROUP "TSSXOJG" PRIQTY 360000 SECQTY 36000 ERASE NO FREEPAGE 0 PCTFREE 0 ,PART 3 VALUES ('2002-03-31') USING STOGROUP "TSSXOJG" PRIQTY 360000 SECQTY 36000 ERASE NO FREEPAGE 0 PCTFREE 0 ,PART 4 VALUES ('2002-01-01') USING STOGROUP "TSSXOJG" PRIQTY 360000 SECQTY 36000 ERASE NO FREEPAGE 0 PCTFREE 0 ,PART [...]
2217 210 30_Re: Help With Catalog Query on18_Blake, Betty@HHSDC19_BBlake@HHSDC.CA.GOV30_Mon, 8 Jul 2002 12:38:03 -0700489_- Thanks for the help.
I just discovered they did this same thing with another query in this round of maintenance. That query actually finished and produced erroneous results so it was easier for me to find the problem.
For anyone who is interested, the query below came from the hold for UQ61346 and the other one with the same problem came from the hold for UQ57024. They don't take into account multiple tables/indexes with the same name but different owner/creator. [...]
2428 41 27_Re: REXX support download ?12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Mon, 8 Jul 2002 14:50:11 -0500330_iso-8859-1 Jim:
We have this running on our V7 system fine, but find that there are V7 specific features that will not allow it to be move to V6. Is there a feature number for V6, the REXX we have on our V6 system does not seem to support all the V6 function. We want to test against all current releases, but not V5! [...]
2470 20 33_Re: UPDATE table WHERE CURRENT OF11_Roger Ghose25_roger.ghose@MTO.GOV.ON.CA30_Mon, 8 Jul 2002 14:41:36 -0500551_- Terry, Thanks ever so much for your reply. We are using full equals key match when updating the index key. But your point is well taken and we have decided not to update the INDEX key, but instead take the DELETE/INSERT route.
Your help is much appreciated.
Regards!
Roger
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
2491 80 47_Sort work space used - Maybe a little off-topic22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM30_Mon, 8 Jul 2002 14:57:24 -0500363_- Dear Listeners,
We're on DB2 V7.1 MVS V2.10 and this question may be more related to DFSORT than DB2.
I ran a Load Test on one of the biggest tables we're converting from Datacom to DB2. It has about 489 Million rows. The tablespace containing this table has 50 partitions.
DB2 Estimator came up with the following for the sort space: [...]
2572 96 51_Re: Sort work space used - Maybe a little off-topic22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM30_Mon, 8 Jul 2002 15:00:14 -0500425_- One more thing, in the UTPRINT, I have INSERT 100000, DELETE 100000, but in the other 2, I have the right record counts of the actual # of records loaded into the table, 488,805,218.
Thanks again,
Venkatesh
-----Original Message----- From: Venkatesh Mokshagundam Sent: Monday, July 08, 2002 2:57 PM To: 'DB2-L@LISTSERV.YLASSOC.COM' Subject: Sort work space used - Maybe a little off-topic [...]
2669 25 50_New IBM Paper comparing LOAD to INSERT Performance0_22_Rohn.Solecki@MTS.MB.CA30_Mon, 8 Jul 2002 15:05:04 -0500431_us-ascii FYI: in the current issue of DB2 Today (from IBM) they have the following link: New DM Tutorials and Redbooks Available DB2 for z/OS -- Load vs. Insert The rise of e-business applications have made the performance of mass inserts even more critical. The costs and benefits of LOAD vs. INSERT are presented for DB2 for z/OS. See: http://www-106.ibm.com/developerworks/nlredirects/r-d1151.html?np-115 [...]
2695 42 31_Re: to execute SQL concurrently17_Robert J. Milonas20_xrjmbob@NETSCAPE.NET30_Mon, 8 Jul 2002 16:07:52 -0400712_iso-8859-1 You get ten people with spufi or qmf and say Everybody, on three. One, Two, Three !
Todd Martin wrote:
>Hi All, > >Is their anyway thru JCL or any command in JCL to run >execute the SQL,ten times concurrently at a time .I >would like to capture some perfomance results . > >Your help would be highly appreciated > >TIA, >Todd > >__________________________________________________ >Do You Yahoo!? >Sign up for SBC Yahoo! Dial - First Month Free >http://sbc.yahoo.com > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of [...]
2738 60 40_Re: DB2 certification materials question13_Helen Johnson25_helen_johnson@RAC.RAY.COM30_Mon, 8 Jul 2002 15:09:54 -0500686_iso-8859-1 This Administration Guide is the manual for the unix, linux, windows and os/2 versions. It is available in html or as a pdf download on this site: http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d2w/en_main. DB2 for the PC can be downloaded from IBM also.
> -----Original Message----- > From: Sharpe, Richard [mailto:rsharpe@AMHERST1.COM] > Sent: Monday, July 08, 2002 9:25 AM > To: DB2-L@LISTSERV.YLASSOC.COM > Subject: DB2 certification materials question > > > Hi > > Can anyone tell me where the free certification materials are > located, I was talking to another DBA from a different > company and they said > IBM was giving away [...]
2799 30 11_DB2 Connect13_Michael Kovas14_mkovas@USG.COM30_Mon, 8 Jul 2002 15:16:27 -0500648_- I have installed DB2 Connect PE v7.1 on my Windows 2000 Workstation(Before installing I made sure I was able to ping the host). We currently have DB2 v6.1 on the OS/390. Next, I cataloged the node, the dcs database, and the database. When I attempt to test the connection or connect to the database I recieve the following error: SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "". Communication function detecting the error: "connect". Protocol specific error code(s): "10061", "*", "*". SQLSTATE=08001 db2 => I [...]
2830 59 31_Re: to execute SQL concurrently13_Olson, Carlos14_COlson@QRS.COM30_Mon, 8 Jul 2002 13:39:25 -0700694_iso-8859-1 You could submit 10 batch DSNTEP2 jobs with TYPRUN=HOLD and then release all 10 at the same time. __________________
Carlos Olson Database Administrator QRS Corporation
This message contains information that may be confidential or proprietary, or protected by the attorney-client privilege or work product doctrine intended solely for the use of the addressee(s) named above. Any review, disclosure, distribution, copying or use of the information by others is strictly prohibited. If you have received this message in error or without authorization, please advise the sender by immediate reply and delete the original message. All email sent to this address will [...]
2890 77 31_Re: to execute SQL concurrently11_Suresh Sane21_data_arch@HOTMAIL.COM30_Mon, 8 Jul 2002 15:48:51 -0500385_- Todd,
Although I got a kick out of Robert's answer (!), I don't think that is what you had in mind. One possibility is to place the SQL in different jcl's (executing dsntep2, dsntiad etc) and have a "drive jcl" that simply executes ijjeft01 with a submit on each individual jcl. These jobs may or may not run concurrently depending on you system workload, job class etc. [...]
2968 152 15_Re: DB2 Connect14_Wayne Driscoll24_wayne.driscoll@QUEST.COM30_Mon, 8 Jul 2002 13:50:57 -0700392_iso-8859-1 DB2 Connect communicates with DB2/390 via DRDA. DRDA requires the DDF address space so you have to install DDF. As part of the DDF install, you need to run step DSNTLOG of the DSNTIJUZ job which updates the BSDS with the DDF startup information. Hope this helps. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...]
3121 47 24_JDBC Performance Problem15_Jackson Reavill18_damcon2@US.IBM.COM30_Mon, 8 Jul 2002 17:12:52 -0400618_us-ascii Hello all,
Our platform is DB2 V7.2.4 for AIX V4.3.3 using WebSphere V3.5.4, Java V1.1, and JDBC Type 2 (app) driver. We are experiencing a performance problem when executing SQL thru JDBC. We are getting vastly different results between executing the SQL from the AIX command line (or DB2 command center) and executing it from the Web application. When executed from the command line or DB2 command center the result set returns within a second or two. However, when executing thru the application it's taking minutes. I believe JDBC is somehow causing DB2 to choose a different access path from [...]
3169 34 18_Peoplesoft Threads13_Martin Packer24_martin_packer@UK.IBM.COM30_Mon, 8 Jul 2002 22:30:08 +0100352_us-ascii I'm doing some work on classifying / inventorying DDF SMF 101 Accounting Trace records...
I notice that Peoplesoft threads (PSAPPSRV, PSQCKSRV) seldom have a QWACRINV of 12 (deallocation) but much more frequently cut records with a QWACRINV of 10 (thread went inactive) in an environment where Inactive Threads support is enabled. [...]
3204 226 15_Re: DB2 Connect13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Mon, 8 Jul 2002 14:39:30 -0700614_iso-8859-1 I can only speak from my own experience, others may have things set up differently. I am DB2 V6 on OS/390. The port numbers are in the BSDS. You update them with offline utility DSNJU003. Here are sample control statements:
DDF LOCATION=,LUNAME=, NOPASSWD,PORT=n1,RESPORT=n2
There is a "well-known" port reserved for DB2, I think it's 446, but if you want to use DDF in multiple subsystems on the same MVS system, you'll have to use different ones. Just make sure whatever you choose is not in use by anyone else. I've reserved a specific range of ports for DB2 use. [...]
3431 96 52_Real Time Stats (was Identifying changes to a table)12_Raymond Bell17_rbell@NZ1.IBM.COM30_Tue, 9 Jul 2002 09:45:14 +1200430_iso-8859-1 Just a word on RTS. We only have one DB2 V7 subsystem, and it isn't being used in anger. Without my knowledge (!) we had the PTF to enable RTS installed here. As I said, I didn't know, so when I reassembled the ZParms there wasn't a value for the stats gathering interval STATSINT. The macro that uses it expects a value between 1 and 1440, so it AbEnded when it didn't get one. Freaked me out for a minute, that. [...]
3528 19 27_Re: REXX support download ?9_Jim Ruddy18_jaruddy@US.IBM.COM30_Mon, 8 Jul 2002 16:41:25 -0500300_- V6 Feature numbers for the REXX Language Support are: 5108 - 3480 Cartridge 5543 - 4mm DAT Cartridge 5216 - 9/6250 Magnetic Tape
I just don't know if you can still order it for V6 since V6 was withdrawn from marketing the end of June 2002.
Jim Ruddy IBM DB2 for z/OS Development [...]
3548 48 49_Re: IOS000I on Archive Tape for Backward Recovery10_Trinh Linh24_ltrinh@WOOLWORTHS.COM.AU30_Tue, 9 Jul 2002 08:02:40 +1000326_- Hi,
It looks like an IO error on the drive OB40 while trying to mount tape # 350335. I believe we often had this problem in our company when the utility need to mount lots of carts. What we did was take that particular drive out and re-started.
Regards,
Linh Trinh *: 02 9843-1329 Database Analyst [...]
3597 97 40_Re: DB2 certification materials question14_Steve Westfall26_Steve.Westfall@EQUIFAX.COM30_Mon, 8 Jul 2002 17:10:33 -0500433_us-ascii There is a book entitled "DB2 Universal Database v7.1 Database CERTIFICATION Guide for Unix, Linux, Windows and OS/2" by George Baklarz and Bill Wong, both of the IBM Toronto Lab. I suspect that Richard was actually referring to this book and not to the Administration Guide, since the title of the former is almost word-for-word identical with the title he listed. The full title of the Admin Guide is very different. [...]
3695 122 15_Re: DB2 Connect14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Mon, 8 Jul 2002 17:23:56 -0500567_us-ascii To add a little bit to this:
The DSNTLOG step in job DSNTIJUZ updates the BSDS with some basic DDF communication information (LOCATION= and LUNAME= for this DB2 Subsystem), and it also allows you to specify the PORT and RESPORT values, which is what shows up in the DSNL004I message text (I don't remember whether this message shows up in the xxxxMSTR or the xxxxDIST address space output). If the DSNTLOG step (DSNJU003) hasn't been run, you won't see the DSNL004I message (you don't add the port # to the MSTR log, you specify it to the BSDS). [...]
3818 33 19_Data import problem14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Mon, 8 Jul 2002 19:02:05 -0400568_iso-8859-1 Hello List,
I have the following problem while trying to load data using the IMPORT utility from a delimited data file into a DB2 table for a column defined as DECIMAL. For the column in question, one record in the data file contains a value: 1674.6000000000000000000000000000. If you don't want to count, this is 4 places before and 28 places after decimal, giving a total of precision of 32 digits. But a DECIMAL column in DB2 has a max precision of 31 digits, so the load for this record goes thru but loads a null value into the column. The [...]
3852 105 39_Re: AVOID SORT WHILE LOADING PARTITIONS31_=?iso-8859-1?q?Sanjeev=20....?=24_sanjeev_db2l@YAHOO.CO.IN30_Tue, 9 Jul 2002 00:18:07 +0100656_iso-8859-1 Roger,
Do you have any other index on the table?
With Best Regards, Sanjeev
--- Roger Ghose wrote: > Sanjeev, > Thanks for your reply. > The partition INDEX has the following columns and > partitions: > > CREATE UNIQUE INDEX "usergroup"."index1" > ON "usergroup"."tablename" > ("DATE_BUSINESS" DESC > ,"USER_ID" ASC > ,"SYSTEM_TIMESTAMP" DESC > ,"LOG_CODE" ASC > ) > CLUSTER > CLUSTER > (PART 1 > VALUES ('2002-12-31') > USING STOGROUP "TSSXOJG" PRIQTY 360000 SECQTY > 36000 ERASE NO > FREEPAGE 0 PCTFREE 0 > ,PART 2 > VALUES ('2002-06-30') > USING STOGROUP "TSSXOJG" PRIQTY 360000 SECQTY > [...]
3958 89 61_Obtaining the value of the IDENTITY column I've just inserted14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Mon, 8 Jul 2002 18:31:18 -0500354_us-ascii Esteemed listers:
Well, I've been RTFM'ing this afternoon, and I can't seem to find what I'm looking for, so I thought someone out there would know the answer to this off the top of his/her head. As this is the first time I've tried working with IDENTITY columns, I haven't a clue, and the UDB manuals haven't been much help so far. [...]
4048 65 69_Re: =?iso-8859-1?Q?Problem_with_CCSID_500_and_spanish_character_=D1?=12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 8 Jul 2002 18:44:43 -0500368_ISO-8859-1 The situation is a bit more complex. CCSID 500 is called Latin 1 and is commonly used in Switzerland and Belgium to represent many European language characters. If this is the first instance of distributed access, perhaps there is an easy technique. The usual SCCSID for Spain is 284 or 1145 with the euro. Here is a query that can help find problems: [...]
4114 118 65_Re: Obtaining the value of the IDENTITY column I've just inserted14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Mon, 8 Jul 2002 20:11:16 -0400408_iso-8859-1 David,
I have successfully used the following on NT and Unix (DB2 V7 EE):
SELECT IDENTITY_VAL_LOCAL() INTO :hostvar FROM SYSIBM.SYSDUMMY1
This, immediately following the INSERT, where you do not specify any value for the identity column.
Of course, we later decided not to use identity columns for its certain drawbacks and choose to use sequence objects instead. [...]
4233 121 65_Re: Obtaining the value of the IDENTITY column I've just inserted12_Rebecca Bond26_rebecca.bond@XPSYSTEMS.COM30_Mon, 8 Jul 2002 17:30:22 -0700389_iso-8859-1 Unless something has changed recently (please tell me I'm wrong), identity columns are not supported for EEE if there is more than one partition.
-----Original Message----- From: David S. Waugh [mailto:dwaugh@NOTES.STATE.NE.US] Sent: Monday, July 08, 2002 4:31 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Obtaining the value of the IDENTITY column I've just inserted [...]
4355 42 27_Re: REXX support download ?12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Mon, 8 Jul 2002 20:43:43 -0500466_iso-8859-1 Jim:
Thanks, I'll try and let the list know the result.
GSH
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@LISTSERV.YLASSOC.COM]On Behalf Of Jim Ruddy Sent: Monday, July 08, 2002 4:41 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: REXX support download ?
V6 Feature numbers for the REXX Language Support are: 5108 - 3480 Cartridge 5543 - 4mm DAT Cartridge 5216 - 9/6250 Magnetic Tape [...]
4398 88 29_Middleware P/A opportunity...18_peabody associates20_jkoval@OPTONLINE.NET30_Mon, 8 Jul 2002 17:05:30 -0700704_iso-8859-1 Hello, I was hoping someone out there might know of someone for a Middleware Programmer Analyst (MQ Series/Mainframe) full time/permanent career opportunity with a good manager at an International Investment Bank located in New York City:
For a major Brokerage Firm in New York City. Seeking full time/permanent Middleware Programmer Analyst. Good manager to work for. Salary is 80 to 95k plus bonus. No agencies or H-1's please. Major Responsibilities/Essential Functions: * Inventory of all mainframe messaging application software. * Architecting a conversion of all non-standard messaging application code to conform to company's MQ Series standard and architecture. * Develop [...]
4487 127 65_Re: Obtaining the value of the IDENTITY column I've just inserted18_Gert van der Kooij16_gkooij@XS4ALL.NL30_Tue, 9 Jul 2002 08:42:02 +0200520_iso-8859-1 You're absolutely right about that. Only if David can wait until V8 it will be possible. This statement is taken from 'DB2 Futures: Trends and Directions' at http://www7b.boulder.ibm.com/dmdd/library/events/0205dwlive/e4129/E4129.pdf
----- Original Message ----- From: "Rebecca Bond" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, July 09, 2002 2:30 AM Subject: Re: Obtaining the value of the IDENTITY column I've just inserted [...]
4615 72 30_Re: Help With Catalog Query on17_Dr. Michael Ebert18_mebert@AMADEUS.NET30_Tue, 9 Jul 2002 09:25:44 +0200543_us-ascii I too ran across these issues when I applied maintenance 4 months ago. I think they've even got a PTF now correcting these queries. What will they think of next...
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany
Thanks for the help.
I just discovered they did this same thing with another query in this round of maintenance. That query actually finished and produced erroneous results so it was easier for me to find the problem. [...]
4688 15 35_Mike P Martin is out of the office.13_Mike P Martin22_mike.p.martin@AEXP.COM30_Tue, 9 Jul 2002 01:02:21 -0700379_us-ascii I will be out of the office from 07/09/2002 until 07/14/2002.
I will respond to your message when I return.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
4704 126 15_DSN1LOGP OPTION5_khkim15_khkim@LGCNS.COM30_Tue, 9 Jul 2002 17:02:42 +0900659_ks_c_5601-1987 Hello, I need your help.
I want to know the options of DSN1LOGP utility to get this option.
************************************************************************************ DSN1212I DSN1LGRD FIRST LOG RBA ENCOUNTERED 00000335916E
0000033591D4 MEMBER(M01 ) LRSN(AB62536BE583) DBID(0006) OBID(00B2) TYPE(PAGE SET CONTROL) SUBTYPE(PAGE SET STATUS RECORD) *LRH* 00660066 00020009 0E800000 00000000 00000335 916E0126 00000335 916EAB62 536BE583 0001 0000 000600B2 C4E2D5C4 C2F0F640 C4E2D5E3 D5E7F0F1 00010000 92018000 00000334 0020 EC3AAB62 5260AB0B 00000000 00000000 00000000 00000000 00000000 00000000 [...]
4831 93 19_Re: DSN1LOGP OPTION11_Jim Addison28_jim_addison@STANDARDLIFE.COM30_Tue, 9 Jul 2002 10:32:15 +0100718_euc-kr TYPE(2) is all you require although this will give more subtypes than the ones you have listed (1 and 9).
I would also include LRSNSTART/LRSNEND which relate to the timeframe required.
Cheers,
Jim.
For more information on Standard Life, visit our website http://www.standardlife.com/ The Standard Life Assurance Company, Standard Life House, 30 Lothian Road, Edinburgh EH1 2DH, is registered in Scotland (No SZ4) and regulated by the Financial Services Authority. Tel: 0131 225 2552 - calls may be recorded or monitored. This confidential e-mail is for the addressee only. If received in error, do not retain/copy/disclose it without our consent [...]
4925 33 65_Re: Obtaining the value of the IDENTITY column I've just inserted14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 9 Jul 2002 19:35:10 +1000588_US-ASCII In addition to the other problems, note that if you insert a row with an Identity column and then rollback, the generated number is not reused.
James Campbell
On 8 Jul 2002 at 18:31, David S. Waugh wrote:
> Esteemed listers: > > I have a table, whose definition will be as follows: > CREATE TABLE EDI_UNIQUE_NUMBER > (EDI_TRANSNUM DECIMAL(11,0) NOT NULL > GENERATED ALWAYS AS IDENTITY > (START WITH 1 INCREMENT BY 1 NO CACHE), > and we don't want any > skipped sequence numbers (that would be bad for audit trail purposes). [...]
4959 99 89_=?iso-8859-1?Q?RE=3A_Problem_with_CCSID_500_and_spanish_charac?= =?iso-8859-1?Q?ter_=D1?=19_Calleja Vilar, Juan22_jcallejav@IBERDROLA.ES30_Tue, 9 Jul 2002 12:19:03 +0200507_iso-8859-1 The result of the execution of the query you proposed is the following:
SELECT ENCODING_SCHEME, SBCS_CCSID, COUNT(*) AS TOTAL FROM SYSIBM.SYSTABLESPACE GROUP BY ENCODING_SCHEME, SBCS_CCSID;
---------+---------+---------+---------+-- ENCODING_SCHEME SBCS_CCSID TOTAL ---------+---------+---------+---------+-- 0 18 E 0 3470 E 500 285
As you can see most of our objects have SBCS_CCSID = 0. The code page used in our PCs is 850 (Windows 95). What should be our next step? [...]
5059 35 45_DB2 Futures - Trends & Directions by Les King12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 9 Jul 2002 12:32:26 +0200351_iso-8859-1 Fellow listers,
I am searching a presentation given by Les King, DB2 Solution Development, IBM Toronto Laboratory, with the title "Session E4129 DB2 Futures - Trends & Directions".
Does anyone know where I can find the *.pdf from this session ?
Thanks in advance for any information you are willing to pass along. [...]
5095 65 83_Re: Repost: How do you set up Data Replication from DB2v5, DB2CON NECT to SQLSERVER15_Neff, Stephen R16_NeffSR@STATE.GOV30_Tue, 9 Jul 2002 08:31:07 -0400520_iso-8859-1 Hello, We are on OS/390 version 6. Is SQLSERVER an IBM product? We have UDB EE and DPROP and DATA Joiner. The DATA Joiner piece is needed because we are replicating to ORACLE. Otherwise my understanding is the UDB EE V7 has replication built in.
-----Original Message----- From: Linda Ayeni [mailto:lindaayeni@HOTMAIL.COM] Sent: Thursday, June 27, 2002 12:03 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Repost: How do you set up Data Replication from DB2v5, DB2CONNECT to SQLSERVER [...]
5161 23 49_Re: DB2 Futures - Trends & Directions by Les King16_Friemel, Norbert24_Friemel@RRZ-KARLSRUHE.DE30_Tue, 9 Jul 2002 14:50:39 +0200598_iso-8859-1 > - > I am searching a presentation given by Les King, DB2 Solution > Development, > IBM Toronto Laboratory, with the title "Session E4129 DB2 > Futures - Trends & > Directions". > > Does anyone know where I can find the *.pdf from this session ? >
http://www7b.boulder.ibm.com/dmdd/library/events/0205dwlive/e4129/E4129.pdf
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
5185 83 15_Re: DB2 Connect14_Wayne Driscoll24_wayne.driscoll@QUEST.COM30_Tue, 9 Jul 2002 05:58:19 -0700363_iso-8859-1 Another important detail that can be easy to overlook is that the userid associated with the DB2 address spaces MUST have a OMVS segment (using RACF terminology) in order to allocate the TCP/IP socket. Also, the DSNL004I message is in the MSTR log, issued during DDF start. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com [...]
5269 53 49_Re: DB2 Futures - Trends & Directions by Les King11_Mark Ediger22_MEDIGER@TRANSUNION.COM30_Tue, 9 Jul 2002 08:05:21 -0500510_US-ASCII The link was just included in a note yesterday. Here it is again.
http://www7b.boulder.ibm.com/dmdd/library/events/0205dwlive/e4129/E4129.pdf
Mark Ediger Acxiom Corp.
>>> G.Peter@DZBW.DE 07/09/02 05:32AM >>> Fellow listers,
I am searching a presentation given by Les King, DB2 Solution Development, IBM Toronto Laboratory, with the title "Session E4129 DB2 Futures - Trends & Directions".
Does anyone know where I can find the *.pdf from this session ? [...]
5323 33 83_AW: Repost: How do you set up Data Replication from DB2v5, DB2CON NECT to SQLSERVER12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 9 Jul 2002 15:13:25 +0200446_iso-8859-1 >> Is SQLSERVER an IBM product? <<<<
No. It is a database system from the company of Bill Gates....
With kind regards - mit freundlichen Gruessen, Georg H. Peter
-----Ursprüngliche Nachricht----- Von: Neff, Stephen R [mailto:NeffSR@STATE.GOV] Gesendet am: Dienstag, 9. Juli 2002 14:31 An: DB2-L@LISTSERV.YLASSOC.COM Betreff: Re: Repost: How do you set up Data Replication from DB2v5, DB2CON NECT to SQLSERVER [...]
5357 145 56_Re: Real Time Stats (was Identifying changes to a table)14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 9 Jul 2002 14:30:05 +0100578_iso-8859-1
You should also be aware, you CANNOT disable Real Time Statistics, all = you can do is disable their externalisation. They are ALWAYS collected though.......
Phil Grainger=20 Computer Associates=20 Product Manager, DB2=20 Tel: +44 (0)161 928 9334=20 Fax: +44 (0)161 941 3775=20 Mobile: +44 (0)7970 125 752=20 phil.grainger@ca.com=20
-----Original Message----- From: Raymond Bell [mailto:rbell@NZ1.IBM.COM] Sent: 08 July 2002 22:45 To: DB2-L@LISTSERV.YLASSOC.COM Subject: [DB2-L] Real Time Stats (was Identifying changes to a table) [...]
5503 18 49_AW: DB2 Futures - Trends & Directions by Les King12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 9 Jul 2002 15:33:11 +0200505_iso-8859-1 Thanks to all who replied so immediately !
For those who are interested too the URL is http://www7b.boulder.ibm.com/dmdd/library/events/0205dwlive/e4129/E4129.pdf
With kind regards - mit freundlichen Gruessen, Georg H. Peter
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
5522 34 15_Re: DB2 Connect13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Tue, 9 Jul 2002 09:43:02 -0400407_iso-8859-1 Just a small clarification on the CDB (comm. Database) requirement:
You need no CDB entries if DB2 on z/OS (OS/390) is merely your server. That is, if you are using your Win2000 client and DB2 connect to get to DB2 on z/OS, you don't need CDB rows.
If you wish to have your DB2 subsystem on z/OS act as a client (to other DB2 z/OS or to other platforms) you need the CDB rows. [...]
5557 22 33_Re: UPDATE table WHERE CURRENT OF11_Roger Ghose25_roger.ghose@MTO.GOV.ON.CA30_Tue, 9 Jul 2002 08:30:35 -0500514_- Hi Truman, Is not true that DB2 internally deletes the row and then inserts the new row when a key is being updated. Please correct me if I am wrong.
Thanks!
Roger
I'm always a little leery of recommendations like this. Data access may not be strictly sequential and the DELETE/INSERT method uses more CPU, causes more more bufferpool activity and log records to be written, etc. Also, unless PCTFREE or FREESPACE is available the row may not be placed optimally via the INSERT anyway. [...]
5580 16 39_Re: AVOID SORT WHILE LOADING PARTITIONS11_Roger Ghose25_roger.ghose@MTO.GOV.ON.CA30_Tue, 9 Jul 2002 08:37:51 -0500431_- Sanjeev, This is the only index we have and we do not want to create any NPI on this table since it is going to be a very large table.
Thanks once again!
Roger
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
5597 174 65_Re: Obtaining the value of the IDENTITY column I've just inserted14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Tue, 9 Jul 2002 08:44:52 -0500468_us-ascii Please keep in mind that this is NOT a partitioned tablespace we are talking about -- it is a single table in a (segmented, if it applies on this platform) tablespace.
Thanks, David
Gert van der Kooij To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Obtaining the value of the IDENTITY column Sent by: DB2 Data I've just inserted Base Discussion List [...]
5772 187 65_Re: Obtaining the value of the IDENTITY column I've just inserted14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Tue, 9 Jul 2002 09:14:08 -0500303_us-ascii I guess I'm unsure if this will give me what I want. Let's say I have a dozen users inserting into my table all at about the same time. Let's also say that 6 of them have finished their unit of work and COMMITted, and 6 of them (including myself) have not yet finished their unit of work. [...]
5960 39 27_Re: REXX support download ?11_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Tue, 9 Jul 2002 10:23:57 -0500378_iso-8859-1 I know with version 5 you had specifically order the feature, but I thought with version 6 that everyone got it. I bet if you find your original install tape it will be there.
Tina
-----Original Message----- From: Jim Ruddy [mailto:jaruddy@US.IBM.COM] Sent: July 08, 2002 4:41 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: REXX support download ? [...]
6000 142 33_Re: UPDATE table WHERE CURRENT OF14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 9 Jul 2002 16:36:38 +0100369_iso-8859-1 Roger,
DB2 deletes and reinserts the INDEX entry when you update a key column, but the row is merely updated in-place (unless you've made it longer, in which case it may get relocated)
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...]
6143 215 65_Re: Obtaining the value of the IDENTITY column I've just inserted14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 9 Jul 2002 16:40:03 +0100384_iso-8859-1 David,
The function will always return the last insert executed in the CURRENT unit of work at the CURRENT level.
This means, if you do an INSERT followed by a SELECT from IDENTITY_VAL_LOCAL, you will get the right value.
BUT If you do an INSERT in a UDF, Trigger etc and then do the SELECT from the main program, you may NOT get the right value. [...]
6359 81 59_Reposting - Sort work space used - Maybe a little off-topic22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM30_Tue, 9 Jul 2002 10:38:10 -0500363_- Dear Listeners,
We're on DB2 V7.1 MVS V2.10 and this question may be more related to DFSORT than DB2.
I ran a Load Test on one of the biggest tables we're converting from Datacom to DB2. It has about 489 Million rows. The tablespace containing this table has 50 partitions.
DB2 Estimator came up with the following for the sort space: [...]
6441 145 65_Re: Obtaining the value of the IDENTITY column I've just inserted14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Tue, 9 Jul 2002 11:10:13 -0500514_us-ascii Thanks Phil, that's what I needed to know.
Because of some limitations with the tool that will do the inserting to the table (don't ask, you don't want to know), they're talking about creating a Stored Procedure to do the INSERT.
Will we run the risk of getting the WRONG value returned by the SELECT if we use a Stored Procedure to do both the INSERT and the SELECT? Or would we only run the risk if we issued the INSERT with the Stored Procedure and the SELECT from the main program? [...]
6587 194 27_Re: REXX support download ?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Tue, 9 Jul 2002 09:18:35 -0700487_iso-8859-1 The FMID is JDB661H. It was not on my V6 install tapes, I had to order it separately. Cathy
-----Original Message----- From: Tina Hilton [mailto:Tina.Hilton@BMSUS.COM] Sent: Tuesday, July 09, 2002 8:24 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: REXX support download ?
I know with version 5 you had specifically order the feature, but I thought with version 6 that everyone got it. I bet if you find your original install tape it will be there. [...]
6782 240 65_Re: Obtaining the value of the IDENTITY column I've just inserted12_tim malamphy20_timalamphy@YAHOO.COM30_Tue, 9 Jul 2002 09:21:59 -0700561_us-ascii Why use EEE if you're not going to partition? It's way more expensive.
If you use EEE software on a single NODE (as Rebecca indicated), you can use identity columns with V7. And don't think that the tablespace is partitioned, but the database is partitioned across nodes (sort of like Datasharing but where each member only has acess to a subset of the data). And a node can be a separate physical machine, or a logical machine on the same box (like an LPAR). Note that you can also generate a EE database from the EEE code. If you're only [...]
7023 31 44_WLM DB2 Stored Procedure environment refresh13_Michalik, Ken19_kmichalik@KRAFT.COM30_Tue, 9 Jul 2002 11:37:47 -0500319_iso-8859-1 Dear List:
I'd like to find out how the process of refreshing the WLM SP environment is being managed when an SP is modified.
We are using Endevor as our COBOL source code repository. For SPAS SPs, Endevor is set up to issue a DB2 stop and start for the procedure when the SP is modified. [...]
7055 24 15_QMF windows 6.112_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL30_Tue, 9 Jul 2002 13:05:01 -0400644_us-ascii Trying to install QMF windows 6.1 on a subsystem and keep getting the error 'DSQSGLOG' not found,
But what is dsqsglog, I can't find reference to it......I ran the same on another subsystem with no problem....
Stan Goodwin Defense Enterprise Computing Center (DECC) Mechanicsburg DB2 Support
Stanley_goodwin@mech.disa.mil DSN: 430-4335 Outside:- 717-605-4335
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
7080 167 65_Re: Obtaining the value of the IDENTITY column I've just inserted12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 9 Jul 2002 10:22:41 -0700283_us-ascii David, We've been using one stored procedure to do both the insert and select for over a year now with no problems. We've always gotten the correct value from the SP. I can see getting an incorrect value if you use a SP to insert and then try to select with a program. [...]
7248 31 15_Re: unsubscribe19_prasad rao Mocharla19_mocharlap@YAHOO.COM30_Tue, 9 Jul 2002 10:40:41 -0700105_us-ascii
--------------------------------- Do You Yahoo!?
7280 25 63_Re: Reposting - Sort work space used - Maybe a little off-topic13_Martin Packer24_martin_packer@UK.IBM.COM30_Tue, 9 Jul 2002 19:04:30 +0100642_us-ascii A comment, and then a question....
Comment: Disk work space by DFSORT isn't as deterministic as one might like.
Question: Did you get a figure for hiperspace / dataspace usage from the DFSORT messages?
Cheers, Martin
Martin Packer, MBCS Martin Packer/UK/IBM 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile 07802-245584)
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
7306 53 48_Re: WLM DB2 Stored Procedure environment refresh12_John Maenpaa24_john_maenpaa@YLASSOC.COM30_Tue, 9 Jul 2002 13:20:28 -0500513_iso-8859-1 On Tuesday 09 July 2002 11:37 am, you wrote: > Dear List: > > I'd like to find out how the process of refreshing the WLM SP environment > is being managed when an SP is modified. > > We are using Endevor as our COBOL source code repository. For SPAS SPs, > Endevor is set up to issue a DB2 stop and start for the procedure when the > SP is modified. > > It seems to me that refreshing the WLM environment could have a bigger > impact on the production system than this STOP/START, and probably we [...]
7360 69 48_Re: WLM DB2 Stored Procedure environment refresh12_Mark Buzzard21_Mark_Buzzard@ARIC.COM30_Tue, 9 Jul 2002 13:34:46 -0500519_us-ascii We move our stored procedure (external) objects into production and then issue the refresh process through BMC's AutoOperator after the process completes. We do both of what you suggested. Stopping and starting the procedure(s) as well as their WLM address spaces.
Buzz
"Michalik, Ken" cc: Subject: WLM DB2 Stored Procedure environment refresh Sent by: DB2 Data Base Discussion List [...]
7430 59 63_Re: Reposting - Sort work space used - Maybe a little off-topic22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM30_Tue, 9 Jul 2002 13:36:06 -0500450_- Thanks, Martin.
Data and Hiper space usage is 0s in all 3 sort messages.
-----Original Message----- From: Martin Packer [mailto:martin_packer@UK.IBM.COM] Sent: Tuesday, July 09, 2002 1:05 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Reposting - Sort work space used - Maybe a little off-topic
A comment, and then a question....
Comment: Disk work space by DFSORT isn't as deterministic as one might like. [...]
7490 200 27_Re: REXX support download ?12_Moulder, Tom19_Tom_Moulder@BMC.COM30_Tue, 9 Jul 2002 13:43:59 -0500452_iso-8859-1 That is exactly the same thing I found out when I researched this at the first of the year(2002). I found a reference in a Washington System Center Redbook to a free download, but that site no longer exists. BTW, all of what I am saying applies to DB2 on z/OS, not UOW. And the FMID is JDB771H for Version 7. Jim's information is also important since you need to order the correct feature to get the correct media for your environment. [...]
7691 56 44_Sheryl Larsen seminar in Princeton July 17th11_Stout, Anne23_Anne.Stout@DOWJONES.COM30_Tue, 9 Jul 2002 15:17:48 -0400431_iso-8859-1 On Wednesday July 17, noted DB2 SQL authority Sheryl Larsen is presenting "DB2 Advanced & Complex SQL Coding", a one-day seminar focused on maximizing your understanding of the power of DB2 SQL. The seminar is only $175/seat.
The seminar will be held at Dow Jones Auditorium in Princeton, NJ, which is located approximately 50 miles North of Center City Philadelphia. The class will run from 9 am till 5 pm. [...]
7748 81 53_Buffers Never Being Used and MVS Subpools for Buffers24_ANDERSON Margaret * IRMD29_Margaret.Anderson@STATE.OR.US30_Tue, 9 Jul 2002 12:22:11 -0700575_iso-8859-1 Greetings Everyone,
We are at DB2 V6.1 on OS/390 2.8 with a single CPU engine (so no CPU parallelism is possible).
I have recently come on board in this shop (and my DB2 Tech experience is pretty old and rusty - anyone have some WD-40 for squeaky DB2 wheels?) I am trying to deal with high elapsed times compared to low CPU times for Batch (DSNTEP2) dynamic queries against one of our larger Production tables (General Ledger). I just discovered that =ALL but one= of the DB2 objects are using BP0 (in both Production and Test). Gasp! I can see [...]
7830 117 27_Re: REXX support download ?12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 9 Jul 2002 12:30:40 -0700561_us-ascii Tom, One real major need for REXX, even if you don't write much is to run DSNTPSMP and possibly modify it as required. That's the stored procedure used for the STORED PROCEDURE BUILDER TOOL with DB2 Connect.
Myron --- "Moulder, Tom" wrote: > That is exactly the same thing I found out when I researched this at the > first of the year(2002). I found a reference in a Washington System Center > Redbook to a free download, but that site no longer exists. BTW, all of > what I am saying applies to DB2 on z/OS, not UOW. [...]
7948 72 31_Web Enable our DB2 for OS/390 ?12_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM30_Tue, 9 Jul 2002 15:39:38 -0400411_iso-8859-1 Group,
Within our current environment, we have a Powerbuilder application running on our client windows based PC, using a middleware product call Relay (which the vendor dropped support of ) and dialing into our OS/390 mainframe which calls a REXX Exec to upload/download many non-DB2 files and then initiates execution of a DB2 plan that runs on our DB2 V6 for OS/390 V2.10 -- in short. [...]
8021 20 19_Communication Error0_24_db46@DAIMLERCHRYSLER.COM30_Tue, 9 Jul 2002 16:04:46 -0400495_us-ascii Trying to connect to a udb instance on a work station and get the following.
(Embedded image moved to file: pic03340.pcx)
Also, what should the DB2COMM environment variable be set to?
Dean J. Burchill 776-8319
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
8042 98 57_Re: Buffers Never Being Used and MVS Subpools for Buffers12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Tue, 9 Jul 2002 15:13:03 -0500424_iso-8859-1 It seems to me that a high level of sync reads while doing sequential prefetch is indicating that you have a lot of rows out of clustering sequence. What does RUNSTATS say?
-----Original Message----- From: ANDERSON Margaret * IRMD [mailto:Margaret.Anderson@STATE.OR.US] Sent: Tuesday, July 09, 2002 2:22 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Buffers Never Being Used and MVS Subpools for Buffers [...]
8141 130 65_Re: Obtaining the value of the IDENTITY column I've just inserted14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Tue, 9 Jul 2002 15:17:07 -0500338_us-ascii OK, this is the point at which I throw myself on my sword and beg for mercy...
I just went back and checked with the guy who told me the platform was going to be EEE, and had him check with the "Keeper of the RFP" to be sure about this. Sure enough, we've ordered Enterprise Edition, not Extended Enterprise Edition. [...]
8272 178 65_Re: Obtaining the value of the IDENTITY column I've just inserted12_tim malamphy20_timalamphy@YAHOO.COM30_Tue, 9 Jul 2002 13:39:04 -0700366_us-ascii Put the sword away. You should have 'full' support of identity columns with EE on V7. Just the gotcha's previously listed in this thread, along with a little import/export problem if you go with GENERATED ALWAYS instead of GENERATED BY DEFAULT.
Tim (no credentials either, but I do have some scars from playing with Ident columns on EE and EEE) [...]
8451 138 53_Buffers Never Being Used and MVS Subpools for Buffers14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Tue, 9 Jul 2002 16:49:15 -0400374_ISO-8859-1 Margaret, Your buffers are all allocated. You are looking at a field that called "buffers active" - it should really be called "buffers unavailable". All buffers for a pool are allocated when the first object assigned to a pool is opened. As a simple shotgun approach, get your sort/work files into their own pool, then get your indexes into their own pool. [...]
8590 29 17_DB2 LPAR Movement50_Knight, Robert A. (Cameron of Pittsburgh at Alcoa)23_Robert.Knight@ALCOA.COM30_Tue, 9 Jul 2002 16:51:11 -0400411_iso-8859-1 Has anybody ever moved an entire DB2 subsystem from 1 LPAR to another LPAR? All subsystem libraries and data. Would like to hear about any pitfalls, etc.
Some current facts::
Going from SMS to non SMS dasd management.
Changing of the subsystem identifiers.
Any problems, hidden areas, anything that may have jumped up and bit you. Would greatly appreciate any input. [...]
8620 14 57_Re: Buffers Never Being Used and MVS Subpools for Buffers0_26_truman.g.brown@VERIZON.COM30_Tue, 9 Jul 2002 16:59:32 -0400478_us-ascii High sync reads during seq prefetch may mean a lot of page stealing is occurring if you have heavy concurrency; the sync reads might be to reacquire stolen pages. Bigger bufferpool and/or hiperpool might (?) help.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
8635 51 27_Re: DB2 V5.1 & IBM Snapshot12_Weaver, Rick19_Rick_Weaver@BMC.COM30_Tue, 9 Jul 2002 16:33:24 -0500612_iso-8859-1 One thing to point out about the BMC COPY PLUS with Snapshot. It does NOT require 'intelligent storage' devices to successfully produce a SHRLEVEL REFERENCE copy with a very brief outage (there is still an outage to establish the copy RBA). We do this with unique patented technology exploiting ESA Dataspace cache. In the event of a failure in the hardware snapshot (maybe due to new datasets being created on volumes that don't support hardware snapshot), the COPY PLUS utility can 'fall back' to using the cache, hence still producing the desired result - a clean SHRLEVEL REFERENCE copy with [...]
8687 91 21_Re: DB2 LPAR Movement10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Tue, 9 Jul 2002 17:00:14 -0500533_us-ascii Robert, In a past life we did an LPAR split & moved the corresponding DB2 regions to the new LPAR. With the CICS, MVS, DASD, scheduling & DB2 folks all on-site for the duration & a LOT of planning by the project manager, it went almost flawlessly. It was very impressive, the DB2's were stopped, the DASD guy switched the volumes from the old LPAR to the new LPAR, system files & all DB2 volumes, the DB2 region was brought up & it worked like a charm. It was almost anticlimactic, it went so well, and we had spent so [...]
8779 195 57_Re: Buffers Never Being Used and MVS Subpools for Buffers14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Tue, 9 Jul 2002 17:16:08 -0500366_us-ascii Hi Margaret:
Q1: The DB2 bufferpools (Virtual Pools) reside in the ssnmDBM1 address space (unless you tell DB2 to put them in a Data Space) which is mostly in extended private storage. I don't remember which MVS subpool, sorry (229 sticks in my head, but I may be wrong about that -- a query of IBMLink should provide that kind of information). [...]
8975 216 65_Re: Obtaining the value of the IDENTITY column I've just inserted14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Tue, 9 Jul 2002 17:21:55 -0500315_us-ascii Thanks Tim, that's good news!
I caught the discussions about GENERATED ALWAYS vs GENERATED BY DEFAULT and Import/Export problems in the other DB2-L threads. Hopefully we won't be doing that, but if we do at least I'll be able to go back and find the thread again so I'll know how to handle it. [...]
9192 65 19_Re: QMF windows 6.114_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Tue, 9 Jul 2002 17:59:26 -0500405_us-ascii I'm assuming by "on a subsystem" you mean you're installing on OS/390...
DSQTSLOG is a tablespace containing the table Q.ERROR_LOG, where QMF logs errors of one sort or another that QMF users encounter. It's used for debugging purposes.
DSQSGLOG is the DB2 Storage Group that the DSQTSLOG tablespace uses. Is that STOGROUP defined on the subsystem you're trying to install on? [...]
9258 103 27_Re: DB2 V5.1 & IBM Snapshot23_Tello Najera Juan Pablo26_jp.tello@BBVA.BANCOMER.COM30_Tue, 9 Jul 2002 18:19:49 -0500503_iso-8859-1 I agree with Rick. We have this featture in our shop and we have every night image copies SHRLEVEL REFERENCE with this Snapshot from BMC. We havce used this image copies in a DRP test and the data was recovered at that point in time and was 100% valid. The only thing that we don´t like some times is that brief outage. This is a QUIESCE point that some times take to long to happen, but we are soing some work arround and it seems that we do not have that long outage for that so long. [...]
9362 106 21_Re: DB2 LPAR Movement14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US30_Tue, 9 Jul 2002 18:37:22 -0500570_us-ascii We did something like this when we "cloned" some DB2 subsystems for Y2K testing on another MVS "LPAR" (essentially it was another MVS Guest under the same VM Host, but same idea & same process).
The biggest problem we ran into was when we decided to SnapShot the DB2 Catalog & Directory at one point in time, moved them to the new LPAR, then went back a little later and SnapShot the Application tablespaces & indexes, and moved them to the new LPAR. Got dozens & dozens of errors about application data pages being "at a different level" than what [...]
9469 37 36_DB2 PM User Authorization (sic) Exit12_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 10 Jul 2002 14:28:01 +1200525_us-ascii Colleagues,
A question for those of you with DB2 PM (preferably V7) who are using the User Authorisation Exit DGOVUUAE to get the Data Collector to verify valid DB2 PM requests.
It's all very nice, granting monitor1 and monitor2 to the ID associated with the Data Collector. With the above exit activated, I nicely get the 'DGOV614 - User Authorisation Exit Returns No Data' message, which means I'm not authorised to use the Data Collector. But do you think I can find where you specify who can? [...]
9507 66 40_Re: DB2 PM User Authorization (sic) Exit10_teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 10 Jul 2002 15:19:16 +1200376_us-ascii Raymond,
Looking at the Installation and Customisation manual, it is mentioned that (sec 2.2.2) the exit is available as a sample(DGOVUUAE) in SDGOSAMP. I do not have access to this module at the moment so I cannot say for sure, but are you not able to find what you wanted in this module ? Thats where I would think you will mention the desired userids. [...]
9574 104 40_Re: DB2 PM User Authorization (sic) Exit12_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 10 Jul 2002 16:15:23 +1200369_us-ascii Hi Kals,
The exit is assembler, which I don't know that well. I guess I could try to 'reverse-engineer' it to work out where it gets the id/access combination, but I doubt a) it, and b) that the id/access combinations are in the exit itself. It would be a bit silly to have to reassemble the exit every time you made a Data Collector grant/revoke. [...]
9679 38 40_Roles of DBAs on different platforms....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM30_Tue, 9 Jul 2002 22:12:03 -0700451_us-ascii Hello !!
Had a rather generic question for people who have worked with DB2 on various platforms. On OS/390, generally speaking, the DB2 system programmers have a separate set of tasks (installing DB2, SMP/E, installing vendor products etc.) than the DB2 Application DBAs.
My question is...is it the same for UDB on Unix, Win NT as well or does the same person perform the installation as well as act as an Application DBA? [...]
9718 17 43_John Campbell1/UK/IBM is out of the office.14_John Campbell119_CAMPBELJ@UK.IBM.COM31_Wed, 10 Jul 2002 07:30:09 +0100498_us-ascii I will be out of the office starting July 10, 2002 and will not return until September 16, 2002.
Working out of IBM Silicon Valley Lab, San Jose, CA. Returning to the UK on Monday 16th September. Telephone No. +1-408-463-3504.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
9736 117 44_Re: Roles of DBAs on different platforms....12_Peter Horner27_peter.horner@JULIUSBAER.COM31_Wed, 10 Jul 2002 10:57:54 +0200609_us-ascii Raquel
We are a rather small DB2 Shop in Switzerland. On Os/390 we have a system programmer with the tasks you mentioned. I'm the application DBA.
On all the other platforms (AI/X, SUN, NT) I'm the system programmer and the application DBA (no I don't get both salary :-)
Cheers Peter
>Hello !! > > Had a rather generic question for people who have >worked with DB2 on various platforms. On OS/390, >generally speaking, the DB2 system programmers have a >separate set of tasks (installing DB2, SMP/E, >installing vendor products etc.) than the DB2 >Application DBAs. [...]
9854 154 44_Re: Roles of DBAs on different platforms....19_Briggs, N. - Neil -28_Neil.Briggs@CANON-EUROPE.COM31_Wed, 10 Jul 2002 11:32:00 +0200379_iso-8859-1 In my experience as a DB2 DBA I perform the role of all things DB2. This include's helping with design and investigating application error's and performance. Through to installing/migrating (includes unloading tape and running the SMPE jcl and checking all previous maint has been applied) to new levels of DB2 aswell as been responsible for backup and recovery. [...]
10009 51 40_Re: DB2 PM User Authorization (sic) Exit13_LANGLEY Moira25_moira.langley@ATOFINA.COM31_Wed, 10 Jul 2002 12:35:42 +0200599_us-ascii
Hi Raymond
The bad(?) news is that DGOVUUAE looks for your list of authorised ids ..... where you tell it to look. You have to code the exit to get that information according to where you've decided to store it (if that makes sense). As supplied, the sample exit checks (for THSU) your userid against QWHCAID, which is I believe the userid associated with the thread you're trying to monitor. If they match then you're authorised. SYSP and SQL are just not authorised, full stop. You could hardcode your authorised userids in the exit itself or, for example, modify the [...]
10061 191 21_Re: DB2 LPAR Movement14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 10 Jul 2002 11:59:30 +0100590_iso-8859-1 Worse than that, we once moved HALF of one DB2 subsystem from one LPAR to another!!
We made the following decisions up front.
1. The subsystem would be different but the HLQs would stay the same (changing them is just too much trouble) 2. We grouped tablespaces/indexspaces into three groups. 2a. One group to stay in the existing subsystem and NOT move 2b. One group to move into the new subsystem and NOT stay in the existing one 2c. One group that would have to exist in both subsystems (cat/dir/dsndb07 etc) 3. We also defined three groups of DASD volumes [...]
10253 162 44_Re: Roles of DBAs on different platforms....15_Sharpe, Richard20_rsharpe@AMHERST1.COM31_Wed, 10 Jul 2002 07:01:56 -0400395_iso-8859-1 Agree with this one, Linux, NT and Solaris installs are much easier, maybe they have put more effort into them as this seems to where the demand is.
-----Original Message----- From: Briggs, N. - Neil - [mailto:Neil.Briggs@CANON-EUROPE.COM] Sent: Wednesday, July 10, 2002 5:32 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: [DB2-L] Roles of DBAs on different platforms.... [...]
10416 107 19_Re: QMF windows 6.112_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL31_Wed, 10 Jul 2002 07:37:51 -0400342_us-ascii David, No what I meant was we were attaching/installing QMF Windows 6.1 on a subsystem. The Host already has QMF61 for OS/390. Turns out there is a "create Index' statement in the attach process for a table on the mainframe and when it tried to run there was an invalid default stogroup defined fore the DSQDBDEF database...... [...]
10524 53 24_how does UR really work?17_zbigniew kowalski12_zbikov@WP.PL31_Wed, 10 Jul 2002 12:49:42 +0200394_iso-8859-2 Dear listers,
i issue a select in spufi (which IS is CS), and do not commit the result, in omegamon i get the following information:
ISOLATION option = Cursor Stability (...) Claim Information Type Class Resource ------- ----- ------------------------------------------- TS CS DB=PLANTABL PS=xxxx (...)
but when i issue the same select WITH UR, i get this: [...]
10578 91 44_Code Page Translation Problem (XML-Extender)15_Hans Schuermann20_hschuermann@DBPRO.CH31_Wed, 10 Jul 2002 06:30:10 -0500394_ISO-8859-1 Hello list,
We are testing with the new XML extender and have a table as follows
CREATE TABLE G10.TG10067 (ORDER_ID CHAR(8) NOT NULL , ORDER_ROWID ROWID NOT NULL GENERATED ALWAYS , CREATION_DATE TIMESTAMP , ORDER_INSTANCE DB2XML.XMLCLOB , DXXROOT_ID CHAR(13) FOR BIT DATA ) IN DG10.SG10067 AUDIT NONE CCSID EBCDIC
We are accessing data with a java program: [...]
10670 17 47_Richard Strong/MAN/Candle is out of the office.14_Richard Strong25_Richard_Strong@CANDLE.COM31_Wed, 10 Jul 2002 13:41:38 +0100481_us-ascii I will be out of the office starting 10/07/2002 and will not return until 15/07/2002.
If you require an update to a problem, or are providing an update for a problem, please email uk_support@candle.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
10688 29 14_Real data-type41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Wed, 10 Jul 2002 15:05:54 +0200394_iso-8859-1 Colleagues,
I'm posting this on behalf of a colleague of mine. He's created a table with a real-datatype-column. Now, after inserting certain values into the table he's surprised to find that the data he's selecting from his table doesn't represent the data he's inserted. For example. He's inserted 818007720 and the data brought back via select reads +0,818007808E+09. [...]
10718 183 44_Re: Roles of DBAs on different platforms....17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM31_Wed, 10 Jul 2002 08:11:55 -0500492_us-ascii I am the system dba for Windows and Unix here at May Company. We have a group of Application dbas who design the databases, create the tables and assist the programmers in tuning the sql. I handle the install, upgrades, migrating, and the tuning of the database engine etc.
Could not agree more about the installation of db2 on these 2 platforms. The system dba was amazed at how easy the installation was the last time he watched me install it on one of our Unix boxes. [...]
10902 76 18_Re: Real data-type17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 10 Jul 2002 15:42:22 +0200356_us-ascii The value gets rounded to a 4-byte REAL (I can tell you're on OS/390). Use FLOAT, DOUBLE PRECISION or FLOAT(n) with n>21, all of which result in a 8-byte floating point number (with 7 bytes mantissa). 818007720=x30C1CEA8 818007808=x30C1CF00
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...]
10979 47 22_Answer: Real data-type12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 10 Jul 2002 15:49:09 +0200449_iso-8859-1 Hallo Ruediger,
AFAIK the REAL DB2 function converts any numeric value or character representation of a numeric value into a single precision floating point value....
What is the definition (coltype, length, scale)of the column where your collegue inserted the value 818007720 ?
With kind regards - mit freundlichen Gruessen, Georg H. Peter ---------------------------------------------------------------------- [...]
11027 68 26_AW: Answer: Real data-type41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Wed, 10 Jul 2002 16:12:10 +0200582_iso-8859-1 Hi Georg,
my colleague isn't using the REAL-function, the column is defined as REAL.
Dr. Ebert,
thanks a lot, I'll pass the information on.
Best regards Ruediger
-----Ursprüngliche Nachricht----- Von: Peter, Georg [mailto:G.Peter@DZBW.DE] Gesendet: Mittwoch, 10. Juli 2002 15:49 An: DB2-L@LISTSERV.YLASSOC.COM Betreff: Answer: Real data-type
Hallo Ruediger,
AFAIK the REAL DB2 function converts any numeric value or character representation of a numeric value into a single precision floating point value.... [...]
11096 37 17_Critical datasets41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Wed, 10 Jul 2002 16:22:16 +0200615_iso-8859-1 Colleagues,
seems it's my busy day today. Before I ask my question here's how we work in our shop: We implemented an automatic application that works out which objects to reorg on a Sunday-basis. We set up thresholds the application uses to generate the respective reorg-jobs. We're also using SMS since we don't want to bother with the physical placement of datasets. We have one DB2-STogroup and 6 different SMS-Storagegroups. Usually we encounter no problems whatsoever whith this approach. But .... for the third or fourth time in a relatively short span of time we found that two of our [...]
11134 21 24_IBM APPLY product on AIX12_Martin, Paul22_Paul.Martin@ECOLAB.COM31_Wed, 10 Jul 2002 09:37:51 -0500387_iso-8859-1 Anyone know why APPLY on AIX would do the following select? SELECT USER, MIN(TRY_CONNECT_TIME)FROM IBMSNAP_ROUTING
On a new AIX box with UDB EE V7 w/ fixpak5 on it APPLY errors out because the ASNAX125 package is not out there when it does the above select -- I can't find any doc on why it is doing the select on the IBMSNAP_ROUTING table or on ASNAX125 package. [...]
11156 23 27_Re: REXX support download ?12_Joe DeCastro14_JoeFDC@AOL.COM31_Wed, 10 Jul 2002 10:41:05 -0400383_iso-8859-1 Just curious as well ..... Can someone explain the interest that appears to exist in REXX? How many are using it as a programming language? How widespread is its use? Is there a talent pool of good REXX programmers? Are there significant limitations to its use? Just curious.....
Tom Moulder BMC Software, Inc. my opinions, not my employers ... [...]
11180 75 44_Re: Roles of DBAs on different platforms....12_John Maenpaa24_john_maenpaa@YLASSOC.COM31_Wed, 10 Jul 2002 10:01:24 -0500548_iso-8859-1 On Wednesday 10 July 2002 12:12 am, you wrote: > Hello !! > > Had a rather generic question for people who have > worked with DB2 on various platforms. On OS/390, > generally speaking, the DB2 system programmers have a > separate set of tasks (installing DB2, SMP/E, > installing vendor products etc.) than the DB2 > Application DBAs. > > My question is...is it the same for UDB on Unix, Win > NT as well or does the same person perform the > installation as well as act as an Application DBA? > > Responses from people experienced [...]
11256 124 21_Re: DB2 LPAR Movement12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 10 Jul 2002 08:20:02 -0700555_us-ascii One word of caution. After moving the subsystem, make sure all automated operations are shut down on the old LPAR. There is (or was with V5) a small window of time, when the same subsystem could be started on both LPARs. It's not a big problem as long as no one updates anything on one of the systems. But as soon as some updates occur on both systems, and DB2 is shut down on the erroneous system, it flushes its buffers and gives you an interesting scenario to practice your recovery skills on the corrupted data. Tim --- "Grainger, Phil" [...]
11381 81 21_Re: Critical datasets10_Dale Smock20_Dale.Smock@BMSUS.COM31_Wed, 10 Jul 2002 10:34:50 -0500574_iso-8859-1 We specify SMSDCFL & SMSDCIX parameters in the zparm to specify different Dataclass for tablespaces & indexes, then use ACS routines to allocate these in separate SMS storage groups, so tables and indexes will always be on separate volumes. Usually, SMS spreads these across multiple volumes (unlike DB2 Stogroups) satisfactorily. We also use dataset masks in the ACS routine to direct datasets to different storage groups and/or volumes, in our case to separate businesses for disaster recovery backups, but this could also be used to avoid I/O contention. [...]
11463 95 36_DB2 PM User Authorization (sic) Exit14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Wed, 10 Jul 2002 10:35:06 -0500511_us-ascii Hi Raymond:
In the DGOVUUAE module, there's a check made for a specific userid:
***************************************************************** * Request type THREADSUMMARY * * - if uae_uid = QWHCAID * * return code 0 to grant access * * - if uae_uid ^= QWHCAID * * return code 4 to denie access * ***************************************************************** L 5,PARAM005 L 10,UAEQWHC@(,5) L 5,PARAM004 CLC QWHCAID(8,10),UAE_UID(5) BNE @RF00064 SLR @04_RX,@04_RX B @SE00062 * [...]
11559 44 69_Strange result for SQL Stored Procedure IN LIST with dynamic variable12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 10 Jul 2002 08:55:59 -0700614_us-ascii One of our SQL stored procedure programmers came up with the following type of sql which produces an interesting result:
DECLARE cursor1 CURSOR WITH RETURN FOR SELECT st.creator as table_creator, st.name as table_name, st.type as table_type FROM sysibm.systables st WHERE
st.dbname = upper(indbname) and st.type in (INTYPE) order by table_creator,table_name
INDBNAME is just an input database name and INTYPE is a SP input variable to allow selection by type. What the programmer wanted to do was to pass a IN LIST string to the SP and use the variable as a type of dynamic sql. [...]
11604 18 37_VB call to DB2 Cobol Stored Procedure17_Robert J. Milonas20_xrjmbob@NETSCAPE.NET31_Wed, 10 Jul 2002 12:08:54 -0400557_iso-8859-1 I have a DB2 Cobol Stored Procedure on the mainframe that I can call successfully from Stored Procedure Builder (RUN)(which goes through DB2 Connect) and I can call from a mainframe Cobol program. But when I try to access it from VB, I get a -440. Which says that it can't find the name or the parameters don't match or doesn't have authority. Don't you just wish they wouldn't use one message for 27 different errors, when they know in the code exactly what the hell is wrong! I have the parameter style set to general for the cobol stored [...]
11623 32 47_DB2 Connect - Long wait time at initial connect10_Marc Costa28_Marc.Costa@MUTUALOFOMAHA.COM31_Wed, 10 Jul 2002 11:09:28 -0500313_us-ascii All,
Current environment: DB2 for OS/390 version 6 Z/OS 1.2 DB2 Connect EE version 5.2 (I know this is out of service) running on AIX 4.2 (I know this is out of service also) connecting to MVS using TCP/IP. We are working on upgrading to DB2 Connect PE version 7.2, but we are not there yet. [...]
11656 21 40_Re: DB2 PM User Authorization (sic) Exit13_Martin Packer24_martin_packer@UK.IBM.COM31_Wed, 10 Jul 2002 17:13:28 +0100585_us-ascii QWHCAID is the name of a field in the Correlation Header, not a literal. Or am I missing something here?
QWHCAID (according to DSNWMGS) is the Primary Auth ID.
Cheers, Martin
Martin Packer, MBCS Martin Packer/UK/IBM 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile 07802-245584)
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
11678 46 40_Re: DB2 PM User Authorization (sic) Exit13_LANGLEY Moira25_moira.langley@ATOFINA.COM31_Wed, 10 Jul 2002 18:14:20 +0200602_us-ascii
Hi Raymond and David
I think I should have been a little more precise in my earlier post - QWHCAID is the name of a variable in the correlation data (mapped by macro DSNDQWHC) which is passed to DGOVUUAE. DGOVUUAE is called for each thread involved in a thread summary request and the correlation data all relates to that thread ie. QWHCAID contains the authorisation id of the thread that the Data Collector is dealing with at that specific moment (remember, this only applies to THSU, the other options are coded differently). So a RACF group QWHCAID definitely won't do [...]
11725 101 27_Re: DB2 V5.1 & IBM Snapshot10_Todd Burch22_tburch@CDBSOFTWARE.COM31_Wed, 10 Jul 2002 11:24:57 -0500345_iso-8859-1 Hi Juan.
I think there is a perception out there that going to a hardware snapshot/flashcopy type of back up is a panacea of problem solving, but as you and a couple other posters point out, there is still an outage.
With CDB/Auto-Online Copy, there is no outage whatsoever, even while obtaining the Copy RBA/LRSN. [...]
11827 40 27_Re: REXX support download ?11_Jim Addison28_jim_addison@STANDARDLIFE.COM31_Wed, 10 Jul 2002 17:30:38 +0100579_iso-8859-1 The attractiveness of the REXX interface is the flexibility it offers i.e. you can very quickly put together a REXX which can perform a variety of functions in a variety of environments in a single keystroke e.g. you could write a rexx which when invoked dynamically generates a query which accesses the db2 catalog, uses the results to allocate some datasets, issues a few DB2 commands, runs a few programs, generates some utility jobs, submits them then displays a summary of what it has done on a panel. This kind of thing could be put together in a couple of [...]
11868 32 51_Re: DB2 Connect - Long wait time at initial connect11_Jim Addison28_jim_addison@STANDARDLIFE.COM31_Wed, 10 Jul 2002 17:37:37 +0100671_iso-8859-1 I think we had a similar problem due to our MAXDBAT/CONDBAT zparms being too low.
You may be able to verify this by -DIS THD(SERVER) TYPE(INACTIVE) - I would appreciate someone confirming if this is the case or not.
Cheers,
Jim.
For more information on Standard Life, visit our website http://www.standardlife.com/ The Standard Life Assurance Company, Standard Life House, 30 Lothian Road, Edinburgh EH1 2DH, is registered in Scotland (No SZ4) and regulated by the Financial Services Authority. Tel: 0131 225 2552 - calls may be recorded or monitored. This confidential e-mail is for the addressee only. If received in [...]
11901 64 21_Re: Critical datasets11_Robert Jans26_robert.jans@ALBERTSONS.COM31_Wed, 10 Jul 2002 08:21:39 -0700250_ISO-8859-1 Ruediger, We have the same problem. Our SMS guru wrote some rules that state dataset x can't be on the same logical volume as dataset y or a or b or.... You get the idea. It's a pain in the arse for them to set up but it can be done. [...]
11966 26 16_Partitioning key15_Sniderman, Karl21_ksniderman@BCBSOK.COM31_Wed, 10 Jul 2002 11:50:45 -0500522_iso-8859-1 I'm working in OS/390 DB2 V6. I have a table that is going to be about 4G so I think that it needs to be partitioned. As a general question, can the partitioning key be different than the primary key? Since the data must be clustered based on the partitioning key, what does that do to speed of access based on the primary key if they're different? Does that make the primary index an NPI? Is this concept one of those "You don't want to go there" ideas? Any help will be appreciated. Thank you very much. [...]
11993 56 20_Re: Partitioning key11_David Nance16_DWNance@FHSC.COM31_Wed, 10 Jul 2002 13:14:10 -0400324_US-ASCII Karl, Yes, your primary key can be different from your partitioning key. Yes, your primary key index is then an NPI. The speed of access on your primary key is going to depend on your cluster-ratio for that index and the levels. We have not had any problems with this type configuration on about 15-20 tables. [...]
12050 60 72_Re: Strange result for SQL Stored Procedure IN LIST with dynamicvariable14_Glen Sanderson26_glen.sanderson@SAFEWAY.COM31_Wed, 10 Jul 2002 12:26:09 -0500601_us-ascii If I recall correctly, I think you must include the parentheses ['(' ,')'] in the string as well and then not put them in the the SQL statement.
Myron Miller wrote:
> One of our SQL stored procedure programmers came up with the following type of > sql which produces an interesting result: > > DECLARE cursor1 CURSOR WITH RETURN FOR > SELECT st.creator as table_creator, > st.name as table_name, > st.type as table_type > FROM > sysibm.systables st WHERE > > st.dbname = upper(indbname) and > st.type in (INTYPE) > order by table_creator,table_name > > INDBNAME is just an [...]
12111 44 20_Re: Partitioning key14_Glen Sanderson26_glen.sanderson@SAFEWAY.COM31_Wed, 10 Jul 2002 12:27:32 -0500545_us-ascii The primary key IS the partitioning key and the clustering key by default. You cannot mix & match them.
"Sniderman, Karl" wrote:
> I'm working in OS/390 DB2 V6. I have a table that is going to be about 4G > so I think that it needs to be partitioned. As a general question, can the > partitioning key be different than the primary key? Since the data must be > clustered based on the partitioning key, what does that do to speed of > access based on the primary key if they're different? Does that make the > primary [...]
12156 72 40_Re: DB2 PM User Authorization (sic) Exit14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Wed, 10 Jul 2002 12:40:41 -0500553_us-ascii Nope, you're right -- my bad! [I thought QWHCAID was an awfully funny-looking default id.]
So if QWHCAID (according to DSNWMSGS) is the Primary Authorization ID, there must be another variable available that is the list of Secondary Auth IDs. Hmmmm...
The closest thing I can find in DSNWMSGS is:
QW0083SL LENGTH OF SECONDARY AUTHORIZATION ID LIST, INCLUDING SELF. QW0083SL IF NO SECONDARY AUTHORIZATION IDS EXIST, THIS FIELD QW0083SL CONTAINS X'0002'. QW0083SA SECONDARY AUTHORIZATION IDS. EACH NAME IS 8 BYTES LONG. [...]
12229 220 20_Piecesize for an NPI16_Jeffrey D. Jerry27_Jeff.D.Jerry@WELLSFARGO.COM31_Wed, 10 Jul 2002 12:40:09 -0500543_iso-8859-1 I have been having problems searching the archives so I thought I would ask this directly.
We are currently using a mixture of SMS and non-SMS managed data set placement in our DB2 subsystems. We are in the process of partitioning a tablespace into 20 parts and have 2 NPIs defined as well. Since we can't specify a stogroup for each piece,... without clever coding in a non-SMS environment each piece will be on the same volume and in an SMS environment it may scatter the pieces or they may reside next to each other. [...]
12450 55 27_Re: REXX support download ?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 10 Jul 2002 21:00:14 +0200574_- Hi Tom,
Can't live without it. Point.
We have our own REXX-DB2 interface and it is widely used, even for replies from web access (USS). Many automated tasks are dome with it.
Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@LISTSERV.YLASSOC.COM] On Behalf Of Joe DeCastro Sent: Wednesday, July 10, 2002 4:41 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: REXX support download ? [...]
12506 39 20_Re: Partitioning key0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 10 Jul 2002 13:10:02 -0500415_us-ascii Hello,
We have one partitioned table, 12 partitions. About 6 million records, so, small stuff by comparison to what I often see on this list.
Our partitioning, primary and clustering columns are all different!
We partition on Fiscal month. We cluster on Fiscal month, Fiscal year, Account Field 1, Account Field 2 Our Primary key is a pair of surrogate keys. (Don't cha love it!) [...]
12546 39 22_Partitioning key redux15_Sniderman, Karl21_ksniderman@BCBSOK.COM31_Wed, 10 Jul 2002 13:13:17 -0500577_iso-8859-1 Dear Listers: I need some additional replies. So far David Nance said that "Yes, they can be different" and Glen Sanderson said (I think) that No they can't. Thanks
> -----Original Message----- > From: Sniderman, Karl > Sent: Wednesday, July 10, 2002 11:51 AM > To: 'db2-l@listserv.ylassoc.com' > Subject: Partitioning key > > I'm working in OS/390 DB2 V6. I have a table that is going to be about 4G > so I think that it needs to be partitioned. As a general question, can > the partitioning key be different than the primary key? Since the data > must [...]
12586 247 24_Re: Piecesize for an NPI10_Dale Smock20_Dale.Smock@BMSUS.COM31_Wed, 10 Jul 2002 13:42:24 -0500397_iso-8859-1 The problem we experienced before using piecesize, is when the NPI reaches 2GB an additional dataset is allocated using the priqty, so unless you have an empty volume ready for this the extend will fail. We have fewer allocation problems finding space for 1GB or 512MB datasets using piecesize. It also allows for multiple concurrent I/O to the NPI when it is on multiple volumes. [...]
12834 62 26_Re: Partitioning key redux19_Hynes, Carol A. DOC27_Carol.Hynes@DOC.STATE.WI.US31_Wed, 10 Jul 2002 13:44:30 -0500388_iso-8859-1 Karl, We also have a partitioned tablespace where the primary key is NOT the partitioning key. The partitioning key is the clustering index. The partitioned table includes blob data stored in aux tablespaces matching the partitions. The base tablespace is about 22mg, the aux tablespaces are about 4.5 gig. We access the data via a web app and have no performance issues. [...]
12897 57 26_Re: Partitioning key redux17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 10 Jul 2002 20:45:06 +0200502_us-ascii Yes they can be different. Some more remarks: 1. A primary (or unique) key is not required in DB2. 2. Your partitioning=clustering index may specify more keys that are strictly required for partitioning. For example, many of our tables are partitioned by (IDPART, more columns) where only IDPART (=01, 02,03,...) determines the partition. The other column specify an order within a partition. 3. A non-partitioned table can have up to 64 GB (32 pieces of 2GB each - no LARGE, no DSSIZE). [...]
12955 99 72_Re: Strange result for SQL Stored Procedure IN LIST with dynamicvariable12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 10 Jul 2002 11:48:02 -0700493_us-ascii Glen, If I understand what you're saying, make the SQL where look something like this: where st.type in INTYPE. If this is what you're implying, it doesn't syntax check. Must have the parentheses in the SQL statement.
Myron
PS: Found another odd extension. changed the where to st.type in (INTYPE,INTYPE2). works just fine with two variables. And I don't need to actually give values for both variables. Just whichever one of the two I'd like, even the second one. [...]
13055 58 26_Re: Partitioning key redux13_Popwell, Greg26_Greg.Popwell@BELLSOUTH.COM31_Wed, 10 Jul 2002 14:47:57 -0400560_iso-8859-1 Karl,
Yes they can be different. Like David, I've seen it done. Cluster ratio is important, as is high cardinality. A primary key NPI can still do the job.
Greg Popwell
-----Original Message----- From: Sniderman, Karl [mailto:ksniderman@BCBSOK.COM] Sent: Wednesday, July 10, 2002 1:13 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Partitioning key redux
Dear Listers: I need some additional replies. So far David Nance said that "Yes, they can be different" and Glen Sanderson said (I think) that No they can't. Thanks [...]
13114 30 22_DSNTIJPM migration job13_Jeremiah Eden28_JEREMIAH.EDEN@RADIOSHACK.COM31_Wed, 10 Jul 2002 13:54:33 -0500587_iso-8859-1 For those who haven't migrated to V7 yet. This is about a thread earlier this year on whether JPM was applicable to V7. I got an APAR and it is now closed with the following info.
PQ58832/UQ67071
DSNTIJPM is a premigration checkout job that helps to identify objects in your current DB2 catalog that cannot be migrated to DB2 Version 7. . Currently, some of the comments and remarks in DSNTIJPM indicate that the job needs to be run only if you are migrating from DB2 Version 5. However, you should run DSNTIJPM prior to migrating from -either- DB2 Version [...]
13145 65 26_Re: Partitioning key redux14_Hickman, Mindy29_Mindy.Hickman@DEG.STATE.WI.US31_Wed, 10 Jul 2002 14:06:08 -0500553_iso-8859-1 I agree with David. They can be different. Your primary key index would be a unique NPI if you didn't use it as your partitioning index. Your partitioning index is your clustering index.
Mindy Hickman State of Wisconsin Bureau of Tech Support DB2 DBA IBM Certified Solutions Expert 608-261-5059 mindy.hickman@deg.state.wi.us
-----Original Message----- From: Sniderman, Karl [mailto:ksniderman@BCBSOK.COM] Sent: Wednesday, July 10, 2002 1:13 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Partitioning key redux [...]
13211 63 26_Re: Partitioning key redux16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 10 Jul 2002 15:06:07 -0400539_iso-8859-1 Partitioning index *must* be the CLUSTERING index. The partition boundaries will be determined by the first forty bytes of columns of this index.
Partitioning index *may* also be the primary key, in which case it must be defined as UNIQUE in addition to CLUSTERING.
Regards, eric pearson NS ITO Database Support
-----Original Message----- From: Sniderman, Karl [mailto:ksniderman@BCBSOK.COM] Sent: Wednesday, July 10, 2002 2:13 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Partitioning key redux [...]
13275 31 28_DB2 and STK DASD Performance10_Dale Smock20_Dale.Smock@BMSUS.COM31_Wed, 10 Jul 2002 14:30:08 -0500500_iso-8859-1 Since migrating from EMC DASD to STK DASD cache subsystems (DB2 V6, OS390 2.6, and STK V960), we have observed spikes in access times for DB2 4K Sortwork datasets in the DSNDB07 database. Our performance monitor and DB2 buffer displays show maximum I/O wait times for these datasets over 20,000 ms and average I/O wait times over 400 ms. We did not experience this with EMC, and do not observe this behavior for any other DB2 datasets (maximum I/O wait times < 100 and average < 20). [...]
13307 21 14_control center13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET31_Wed, 10 Jul 2002 15:32:56 -0400490_us-ascii I am using db2/udb version 6.0 on windows nt Each time I click on the "Control Center" I get a msgbox with "The Segment is Already Unlocked"
I cannot get past this msgbox.... Any help or suggestions??? thanks J Willett
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
13329 76 32_Re: DB2 and STK DASD Performance23_Tello Najera Juan Pablo26_jp.tello@BBVA.BANCOMER.COM31_Wed, 10 Jul 2002 15:00:34 -0500274_iso-8859-1 We had that kind of problems but not only with DSNDB07, but with many other heavy accessed tablespaces and the only way we could solve the problem was to take out from STK this heavy used datasets. We have HDS 9900 also and in those disks every thing is OK. [...]
13406 84 26_Re: Partitioning key redux13_Davis, Ronald17_DavisRo@CONED.COM31_Wed, 10 Jul 2002 16:05:56 -0400296_iso-8859-1
To add a little more : A Primary Key is used to enforce referential integrity. It has to have a unique index (primary index) based on the primary key columns . Are we're really only talking about a unique index as a NPI ? or do you have referential integrity constraints? [...]
13491 21 11_DB2 Connect17_Adam Wiedenhaefer20_adamw@IMAGERIGHT.COM31_Wed, 10 Jul 2002 16:49:32 -0400361_iso-8859-1 Hey All,
I'm trying to install a DB2 ODBC driver on a client computer for my application to use. What I have been doing is installing DB2 Connect on the machine. I noticed that it needs to install a local instance of DB2 on that client. Why does it do this? Is there an easier way to install an ODBC driver on a client machine (Windows)? [...]
13513 42 41_Partitioning Key - Using month(datefield)15_Sniderman, Karl21_ksniderman@BCBSOK.COM31_Wed, 10 Jul 2002 16:12:24 -0500492_iso-8859-1 First, I want to thank all of you who responded. I find it really delightful to be able to access all this information so quickly. It gives me the same kind of feeling of pleasure that I get everytime I use an infra-red remote control. I can remember having to get up from the easy chair to go over to the TV(Telly) to change the channel, or having to get out of the car to open the garage door. Sorry, I'm old. Old people reminisce. Anyway, I think this is a wonderful tool. [...]
13556 101 26_Re: Partitioning key redux13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 10 Jul 2002 16:19:25 -0500314_iso-8859-1 Just to add a bit here ... In DB2 V5/OS390 - possible V4 - anyway, the foreign key RI constraint does not have to reference a Primary Key of the parent. It can reference columns of a UNIQUE INDEX - the column order of the index and the columns in the foreign key constraint must be the same order. [...]
13658 64 41_Re: VB call to DB2 Cobol Stored Procedure16_adriana.teixeira27_adriana.teixeira@UOL.COM.BR31_Wed, 10 Jul 2002 18:09:04 -0300405_iso-8859-1 We've had a similar problem in our environment, and in our case we discovered that the problem was the client application language that was written in VB that didn't recognized a decimal output parameter. So if any of column from a table acessed in a SQL inside the stored procedure is defined with this data type I recommed you to convert it to real or float before returning the result. [...]
13723 70 45_Re: Partitioning Key - Using month(datefield)11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 10 Jul 2002 16:42:07 -0500662_- Karl,
Indexes can be built on columns only - not on functions. So you must have a month column. You could, however, use a before trigger to populate it with the month extracted from the date column.
HTH.
Suresh
>From: "Sniderman, Karl" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@LISTSERV.YLASSOC.COM >Subject: Partitioning Key - Using month(datefield) >Date: Wed, 10 Jul 2002 16:12:24 -0500 > > First, I want to thank all of you who responded. I find it really >delightful to be able to access all this information so quickly. It gives >me the same kind of feeling [...]
13794 15 56_Eva Molina Jimenez/Spain/Contr/IBM is out of the office.18_Eva Molina Jimenez20_evamolina@ES.IBM.COM31_Thu, 11 Jul 2002 00:31:57 +0200409_us-ascii I will be out of the office starting July 11, 2002 and will not return until July 22, 2002.
I will respond to your message when I return.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
13810 57 15_Re: DB2 Connect14_Michael Kaplan25_micaelkp@NETVISION.NET.IL31_Thu, 11 Jul 2002 01:32:58 +0200340_iso-8859-1 Hi Adam,
I am not sure, you are right. If you are talking about simple DB2 Connect Personal Edition, It does not create UDB on your client.
Actually, you will get DB2 Connect itself ( including ODBC Driver ) and some client ( for ver 7.2, it is in Java) Applications, line Control Center, Command Center, etc. [...]
13868 81 15_Re: DB2 Connect18_Gert van der Kooij16_gkooij@XS4ALL.NL31_Thu, 11 Jul 2002 01:11:15 +0200368_iso-8859-1 Well, in fact it does create an instance on your local machine but it's only a client instance, not a server instance. With the 'db2 get dbm cfg' command it will show you which 'node type' you are running.
With DB2 Workgroup Edition (and most likely Enterprise Edition also) it shows: 'Node type = Database Server with local and remote clients' [...]
13950 15 48_Michal Bialecki/Poland/IBM is out of the office.15_Michal Bialecki21_m.bialecki@PL.IBM.COM31_Thu, 11 Jul 2002 01:24:37 +0200409_us-ascii I will be out of the office starting July 11, 2002 and will not return until July 16, 2002.
I will respond to your message when I return.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
13966 24 13_CICS and LOBs15_Mike Brauweiler22_MikeBrau@ALUMNI.ND.EDU31_Wed, 10 Jul 2002 18:43:54 -0500576_iso-8859-1 I have a client who is interested in creating a CICS application that presents BLOB data (PDFs and JPEGs, primarily) to a Web Server. Everything is running on OS/390. My personal opinion is that they should cut CICS out of the picture - but I haven't told them that yet. (After all, how many concurrent 50MB transactions can they reasonably support?) I've spent a large part of the last two days searching everything I could think of - including the DB2-L archives - but can find little in the way of direction, gotchas, etc. Any assistance would be very much [...]
13991 38 40_Re: DB2 PM User Authorization (sic) Exit12_Raymond Bell17_rbell@NZ1.IBM.COM31_Thu, 11 Jul 2002 12:01:59 +1200572_us-ascii Hi David, Moira, Martin,
Thanks for all your comments, even if you got me going down the wrong path to start with (I'm talking to YOU, David!). Just kidding; it's all good stuff.
I didn't even know to look in SDSNSAMP member DSNWMSGS for the explanation of QWCHAID. Yes, it (the DGOVUUAE exit) seems to be setup to only allow you to monitor your own thread, and only for threadsummary, but even then it doesn't look right to me. It seems to be saying 'if your userid is your primary authid, you're in!'. Actually, that is right. It didn't work [...]
14030 76 28_Re: how does UR really work?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 11 Jul 2002 13:20:04 +1000439_US-ASCII The UR/non-lock applies to the accessed data.
DB2 still needs to lock the plan, package (if one is used), and all databases, tablespaces, tables and indexes used by the query - to prevent them being dropped/altered while the query is running. However, as it would be unlikely(?)/unwise to alter these resources while accessing the underlying data, it is unlikely/fortunate that you will get a -904 on those resources. [...]
14107 24 43_Re: Roles of DBAs on different platforms...16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Wed, 10 Jul 2002 23:28:52 -0700379_us-ascii Thank you Peter, Richard, Jeremy and John for you valuable inputs. Guess I will continue to try to hone my skills on Application DBA aspects on UDB for Lin- Win rather than undergo SMP/E training now.
Thanks. Raquel.
__________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com [...]
14132 31 16_DB2 Installation29_RAGHAVAN Vadakkupattu Sampath21_VRaghava@COVANSYS.COM31_Thu, 11 Jul 2002 02:30:32 -0400420_- Hi Everyone,
I am installing an db2 instance( subsystem ) in addition to the existing DB2 instance on OS/390 ( OS: V2R9, DB2 : V6R1 ) . While referring the installation guide they were mentioning about SMP/E steps to be followed before running the CLIST. Since I have already carried out the SMP/E steps for the existing DB2 instance , whether can I use the same CSI datasets for my new instance creation. [...]
14164 67 28_Re: how does UR really work?11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Thu, 11 Jul 2002 04:44:53 -0400563_ISO-8859-1 Zbigniew
The Omegamon information you are looking at shows Claims not Locks. There is a Cursor Stability Read Class claim on the tablespace. DB2 always makes claims on the objects it processes, whatever locking strategy is used. A claim, in most cases, only requires DB2 to increment a claim count in a memory control block - much cheaper than a lock. Claims are used to manage conflicts between SQL and Utilities/Commands (which drain the claim classes to get control of an object). Locks are used to manage conflicts between SQL and SQL. [...]
14232 54 20_Re: DB2 Installation14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 11 Jul 2002 19:46:31 +1000544_US-ASCII Yes you can use the same CSI/target datasets sets, but consider and plan for - if you use the same target datasets, when you apply maintenance to one subsystem, it will also go on the other - if you share SDSNEXIT you will also be sharing DSNDHECP, and you'll need individual DSNZPARM members - there has been much discussion in the past on techniques for managing individual stes of target libraries for each subsystem (seach the archives) eg http://listserv.ylassoc.com/cgi/wa.exe?S2=db2-l&L=DB2- L&q=target+smp&0=S&s=&f=&a=&b= [...]
14287 28 15_Isolation Level9_Ali Akbar25_ali.akbar@CRESSOFT.COM.PK31_Thu, 11 Jul 2002 15:38:16 +0500375_us-ascii Hi there.
Huge data is inserted in a table (60,000 records). At the same time i need to access the table and view records previously in the table from some other connection. I dont need to view the uncommited data but the previous commited data. I am using the default isolation level (i think it is Read Commited) but i am not able to access the table. [...]
14316 30 15_Isolation Level9_Ali Akbar25_ali.akbar@CRESSOFT.COM.PK31_Thu, 11 Jul 2002 17:00:19 +0500426_us-ascii Sorry if you are receiving this mail twice.
Hi there.
Huge data is inserted in a table (60,000 records). At the same time i need to access the table and view records previously in the table from some other connection. I dont need to view the uncommited data but the previous commited data. I am using the default isolation level (i think it is Read Commited) but i am not able to access the table. [...]
14347 14 4_Test14_Bill Gallagher28_BILL.GALLAGHER@PHOENIXWM.COM31_Thu, 11 Jul 2002 08:52:04 -0400269_us-ascii Test
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
14362 42 22_new subsystem help!!!!14_Melissa Rogers26_mrogers@NYSTRS.STATE.NY.US31_Thu, 11 Jul 2002 09:22:08 -0400398_iso-8859-1 We are in the process of installing our new peoplesoft DB2 subsystem. We have been trying to follow the DB2 extra-large size recommendations but keep getting the following warning messages:
DSNT481I DSNTINST SYSDBASE DATA SET SIZE MAY BE TOO SMALL. REASON IS DATA SET SIZE LIMIT
DSNT481I DSNTINST SYSVIEWS DATA SET SIZE MAY BE TOO SMALL. REASON IS DATA SET SIZE LIMIT [...]
14405 24 50_questions regarding the Oracle Transparent Gateway0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Thu, 11 Jul 2002 08:49:36 -0500455_us-ascii We began using the Oracle Transparent Gateway several years ago and I have a question regarding compatibility. Does version 4.1.1 of the Oracle Transparent Gateway work with version 7 of DB2 (for OS/390)? I realize that this is an older version of the Oracle Transparent Gateway, but we are currently attempting to phase it out and probably will not be done eliminating the Oracle Transparent Gateway by the time we get version 7 installed. [...]
14430 38 26_Online Reorg and ERASE YES19_Gagliardi, Michelle21_GagliardiM2@AETNA.COM31_Thu, 11 Jul 2002 09:52:45 -0400514_iso-8859-1 Hi everybody,
I'm working on DB2 v7 on OS/390. We utilize the FASTSWITCH option of Online Reorg. It appears that during this phase of online reorg, DB2 uncatalogs the current dataset and catalogs the new "reorged" dataset which is how it's supposed to work. I get that. However, the "old" datasets that get uncataloged are not being deleted. Is this the way it is meant to be? We have indexes that are reorged nightly so, as you can imagine, we have a lot of datasets building up out there. [...]
14469 188 19_Re: Isolation Level14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 11 Jul 2002 15:08:10 +0100356_iso-8859-1 Ali
Short answer - "NO"
If you just run a SELECT with no WHERE clause, DB2 will try and return you ALL the data in the table. Depending on which access path it decides to use, you may end up waiting for a lock on a page held by your inserting transaction. You will then wait for that lock to be released (commit or rollback). [...]
14658 13 28_Re: how does UR really work?13_Patric Becker25_Patric_Becker@SIS-WEST.DE31_Thu, 11 Jul 2002 08:47:53 -0500415_- By the way, a SELECT statement using UR also aquires a S-Mass Delete Lock on the table to prevent you from being hit by mass delete statements.
Patric
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
14672 33 19_DB2UEXT2.C for Unix0_19_csutfin@AMSOUTH.COM31_Thu, 11 Jul 2002 09:05:45 -0500425_us-ascii I hope someone on the list can help me here.
We have an application running DB2 UDB V7.1 on Unix. The users want 24 X 7 access.
Management has decided that I need to activate DB2UEXT2 to start archiving the logs. Only one problem, there is no C compiler or runtime on this system. They can add runtime files but not compiler/linker. C compiler/linker is available on another Unix system in house. [...]
14706 162 26_Re: new subsystem help!!!!12_David Booher22_david.booher@QUEST.COM31_Thu, 11 Jul 2002 07:16:47 -0700358_iso-8859-1 Hello,
First of all, I don't see why you would need 600 databases on your subsystem. The size of your subsystem DB2 catalog VSAM files will be HUGE! There really is only 1 PeopleSoft database. We have a rather large PeopleSoft testing ground that really only has 8 PeopleSoft databases with 18 databases total on the entire subsystem. [...]
14869 17 26_Re: new subsystem help!!!!13_Julie Johnson23_Julie.Johnson@UCONN.EDU31_Thu, 11 Jul 2002 10:21:39 -0400423_us-ascii Hi Melissa,
You are getting these messages when you try to run the CLIST, correct? Are they actually preventing the CLIST from completing?
Julie
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
14887 147 51_PeopleSoft DB2 Install (Was New subsystem help!!!!)13_Lockwood Lyon24_Lockwood.Lyon@MEIJER.COM31_Thu, 11 Jul 2002 10:33:43 -0400667_US-ASCII MR,
Relax! First, the warning messages are just that: Warnings. Once your DB2 subsystem JCL members are generated (into the SDSNSAMP dataset) you still have to run them. Before doing that, you can change the DSNZPARMs specifications in member DSNTIJUZ to your heart's content.
As for your initialization parm settings (DB=600, TB=100) this is *not* how PeopleSoft is usually defined. Check with your PeopleSoft Installation folks; in particular, depending on what version(s) of what PeopleSoft modules (Financials, HR, Payroll, you don't say) you have, your number of tables is probably more in the 2,000 - 10,000 range. Again, check with [...]
15035 97 30_Re: Online Reorg and ERASE YES17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 11 Jul 2002 16:45:34 +0200638_us-ascii Hi Michelle,
ERASE YES just causes the file contents being overwritten with binary zeros (I guess) before deleting them. This is a security measure to prevent somebody else from reading your sensitive data off the disk after you have deleted it, and won't help you. We're only on V6, so I can't contribute V7 experience, but OLR should allocate (and catalog) the datasets when it starts executing and delete (and uncatalog) the original ones after the switch. At least with DB2-managed datasets, it should. User-managed datasets (which probably should be used only for the Cat&Dir) should not get allocated, deleted, [...]
15133 98 55_Re: PeopleSoft DB2 Install (Was New subsystem help!!!!)13_Julie Johnson23_Julie.Johnson@UCONN.EDU31_Thu, 11 Jul 2002 10:47:32 -0400435_us-ascii Lyon,
Well said! That's sort of where I was going with my earlier question regarding the CLIST. I found that when I was creating new DB2 subsystems for PS, I had to do what you outlined below and customize DSNTIJUZ after the fact. Melissa, hang in there (believe me, I know you probably feel like hanging YOURSELF right about now) because it will get done......been there, done that (don't want to do it again...) [...]
15232 94 26_Re: new subsystem help!!!!22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Thu, 11 Jul 2002 09:49:35 -0500541_- I have received similar messages before and all these are informational and shouldn't stop you from defining the Sub-System.
This is because the Dataset size calculation has a size overflow. You can fix it either by reducing the number of objects or just run the Job as it comes out of CLIST calc (just make sure you're not exceeding the total # of tracks for all datasets in DSNTIJIN and the volumes that you have) and then re-size your catalog datasets as you go - this way, you can spread the datasets on different volumes. [...]
15327 168 55_Re: PeopleSoft DB2 Install (Was New subsystem help!!!!)14_Melissa Rogers26_mrogers@NYSTRS.STATE.NY.US31_Thu, 11 Jul 2002 10:49:30 -0400543_iso-8859-1 Thanks to those who have responded. I agree that the number of tables for peoplesoft should be much higher however, the max value that can be placed in the tables field is 400 (tables per database). That is why we have increased the number of databases to get a higher ratio (tables per database). We have continued with the install. We actually used 200 databases and the max (400) tables as our parameters. We are going currently modifying the VSAM allocations of the catalog tables that we received the warning messages on. [...]
15496 70 23_Re: DB2UEXT2.C for Unix11_Judy Kibler21_judith.kibler@TRW.COM31_Thu, 11 Jul 2002 09:45:09 -0500470_us-ascii Hi Carol,
Yes, you can compile it on another box that has the C compiler, then copy the executable to the box where you need it to live. We did the same thing here.
Hope that helps! Judy
csutfin@AMSOUTH.COM on 07/11/2002 09:05:45 AM
Please respond to DB2 Data Base Discussion List
To: DB2-L@LISTSERV.YLASSOC.COM
cc: (bcc: Judy Kibler/Nashville) [...]
15567 25 32_I am currently out of the office11_Wilkin Eric18_Eric.Wilkin@NBB.BE31_Thu, 11 Jul 2002 17:01:25 +0200676_iso-8859-1 Je suis absent du 12/07 au 02/08. Durant cette période, vous pouvez prendre contact avec les personnes suivantes :
Domain Contact Tel IDMS Walter Van den Wijngaert 29.65 DB2 Patrick Steurs 53.84 SQL/Server Laurent Scerri 43.70
Visit our website! http://www.nbb.be
"DISCLAIMER: The content of this e-mail message should not be constructed as binding for the National Bank of Belgium (NBB) unless otherwise and previously stated. Besides, the opinions expressed in this message are solely those of the author and do not necessarily represent those of the NBB, which is particularly the case if the content of the present message, or [...]
15593 143 26_Re: new subsystem help!!!!17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 11 Jul 2002 17:12:30 +0200608_us-ascii Our SAP systems have 10.000 databases (total of 35.000 VSAM files), and the Cat/Dir files are not huge...
When reading the installation guide, I got the feeling that the sizings for small, medium and large installations still stem from days where a 1GB DB was considered large. What I would do without any advance information on how big everything will get: edit the generated JCL that allocates the files (DSNTIJIN) and specify every VSAM file size as CYL(1 1) (I think doing allocations in blocks is silly nowadays). Most likely you won't need more than 255 CYLs anywhere. Use ADRDSSU [...]
15737 26 30_Re: Online Reorg and ERASE YES0_19_Tim.Lowe@STPAUL.COM31_Thu, 11 Jul 2002 10:15:16 -0500325_us-ascii Michelle, What do you mean by "However, the "old" datasets that get uncataloged are not being deleted"? For example, using OLR with FASTSWITCH, if it was reading the ".I0001" dataset and creating the ".J0001" dataset, are you saying that on completion the ".I0001" dataset is being uncataloged but not deleted? [...]
15764 36 30_Re: Online Reorg and ERASE YES10_Bruce, Mae26_BruceM@MAIL.OA.STATE.MO.US31_Thu, 11 Jul 2002 10:17:50 -0500625_iso-8859-1 We have not run into this problem. After the switch, only the new reorged datasets are there. Ours are DB2-managed datasets and we are using SMS.
Mae Bruce State of Missouri OA/Division of Information Services
Hi everybody,
I'm working on DB2 v7 on OS/390. We utilize the FASTSWITCH option of Online Reorg. It appears that during this phase of online reorg, DB2 uncatalogs the current dataset and catalogs the new "reorged" dataset which is how it's supposed to work. I get that. However, the "old" datasets that get uncataloged are not being deleted. Is this the way it is meant [...]
15801 178 30_Re: Online Reorg and ERASE YES14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 11 Jul 2002 16:24:57 +0100527_iso-8859-1 Michelle,
When On-Line reorg completes and you have STOGROUP defined tablespaces/indexes then the 'old' versions SHOULD be deleted by DB2......
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com
-----Original Message----- From: Gagliardi, Michelle [mailto:GagliardiM2@AETNA.COM] Sent: 11 July 2002 14:53 To: DB2-L@LISTSERV.YLASSOC.COM Subject: [DB2-L] Online Reorg and ERASE YES [...]
15980 128 30_Re: Online Reorg and ERASE YES14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 11 Jul 2002 16:27:57 +0100414_iso-8859-1 Just thought of a question I should have asked.
Are you using FASTSWITCH YES (the default)??
If so, the pageset names do NOT change.
You start off with I0001 datasets, the reorg runs creating J0001 shadows. THEN Fastswitch changes the DB2 CATALOG so that DB2 is aware that the J0001 datasets are now the REAL ones and DB2 will (should?) delete the now-obsolete I0001 datasets. [...]
16109 37 47_Performance Problems With SQL Stored Procedures18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 11 Jul 2002 11:41:22 -0400490_iso-8859-1 Running DB2 OS/390 Version 7. DB2Connect gateway. Crystal Enterprise is the requesting application.
We have a couple of SQL stored procedures defined to DB2 OS/390 that are called from Cystal Reports. Every once and while, we notice that the stored procedure will sit it DB2 accumulating elapsed time (class 1) but all of the work seems to be finished. Getpage requests and actual DB2 cpu time doesn't change. Eventually the thread ends. These stats come from DB2PM. [...]
16147 35 30_Re: Online Reorg and ERASE YES19_Gagliardi, Michelle21_GagliardiM2@AETNA.COM31_Thu, 11 Jul 2002 11:44:51 -0400561_iso-8859-1 Dr. Ebert, Thanks for the clarification on ERASE YES.
Tim, your statement:
"For example, using OLR with FASTSWITCH, if it was reading the ".I0001" dataset and creating the ".J0001" dataset, are you saying that on completion the ".I0001" dataset is being uncataloged but not deleted?"
Yes, that's what I'm saying.
We are using DB2-managed datasets and SMS and there are people looking into the "DB2 bug" or "SMS bug" scenarios. I just thought I'd throw it out there and see if anyone else had experienced the problem. [...]
16183 17 30_Re: Online Reorg and ERASE YES0_19_Tim.Lowe@STPAUL.COM31_Thu, 11 Jul 2002 11:02:07 -0500397_us-ascii Michelle, As far as I know, SMS does not allow uncataloged datasets. Are you sure that this is what is occurring?
Thanks, Tim
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
16201 29 12_(no subject)12_Dave Johnson21_DavidLJohnson@AOL.COM29_Thu, 11 Jul 2002 12:02:45 EDT43_US-ASCII SIGNOFF * (NETWIDE
16231 127 17_Re: CICS and LOBs13_Jackson, Phil21_PJackson@TXFB-INS.COM31_Thu, 11 Jul 2002 11:12:02 -0500387_iso-8859-1 Yes, I wouldn't think you would want to use legacy methods such as CICS. How about a DB2 stored procedure to extract the data, called via ADO from an ASP application?
Phil J.
-----Original Message----- From: Mike Brauweiler [mailto:MikeBrau@ALUMNI.ND.EDU] Sent: Wednesday, July 10, 2002 6:44 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: CICS and LOBs [...]
16359 20 30_Re: Online Reorg and ERASE YES9_Sally Mir22_sally.mir@WACHOVIA.COM31_Thu, 11 Jul 2002 12:10:08 -0400337_us-ascii I can't find it in the manual right now, but I seem to remember that if the shadow dataset already exists when you begin the reorg, it will not be deleted at the completion of the utility. I suspect that maybe you had a reorg that abended and left the shadow datasets. From then on, the utility will not have deleted them. [...]
16380 25 12_(no subject)12_Dave Johnson21_DavidLJohnson@AOL.COM29_Thu, 11 Jul 2002 12:29:37 EDT20_US-ASCII unsubscribe
16406 44 20_Re: Partitioning key12_Mehdi Fakhar25_Mehdi.Fakhar@JMFAMILY.COM31_Thu, 11 Jul 2002 13:09:29 -0500353_- Your partitioning Key and your Primary Key can (and in most cases) should be different. You need to evaluate the batch programs accessing the table to make sure you are defining the right partitioning index (which become your clustering index by default). Your Primary Index will become your NPI. I have used this successfully with large tables. [...]
16451 103 64_Free Webcast: DB2 V7 Recovery Without Using IBM's Utility Suites13_Thomas, Janis19_Janis.Thomas@CA.COM31_Thu, 11 Jul 2002 15:40:32 -0400653_iso-8859-1 When: Wednesday, July 17, 2:00 PM ET Duration: 60 Minutes Presenter: Rob Crane, CA, Senior Consultant Description: Recovering objects when your shop has not purchased IBM's optional DB2 Utility Suites (Operational Suite, Recovery & Diagnostics Suite) can be easy when using Computer Associates DB2 recovery offerings. This webcast will help answer these questions: - Can I recover my objects without buying IBM's optional utility suites and just using CA products? - What objects can I recover with IBM's optional utilities without purchasing IBM's optional utility suites? - What are my fallback choices if I have a problem with one of [...]
16555 31 14_case statement13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Thu, 11 Jul 2002 14:54:22 -0500384_us-ascii Hi.. We have some long running queries and would like to limit the execution time is there a way to use a CASE statement to terminate a query
for example:
select cola, colb, colc, cold,cole,colf, colg case where "some internal db2 time in seconds < 30 seconds" from user_table where indexa = host-variable col_b = host-variable optimize for 1 row with ur; [...]
16587 63 21_Re: Critical datasets14_Massimo Scarpa16_mocion@INWIND.IT31_Thu, 11 Jul 2002 22:15:06 +0200670_us-ascii Another trivial trick we used some years ago (if reorg NOT running at the same time)
REORG TS A
CHECK volumes where LDS is defined (we use a trivial REXX+ skeleton)
/V sms,vol(xxxxxxx),disable,new
Reorg TS B
/V sms,Vol(xxxxxx),enable
But I'm sure we use another trick, I've to check my REXXs
HTH
Max Scarpa
At 16.22 10/07/2002 +0200, you wrote: >Colleagues, > >seems it's my busy day today. >Before I ask my question here's how we work in our shop: >We implemented an automatic application that works out which objects to >reorg on a Sunday-basis. We set up thresholds the application [...]
16651 54 51_Re: Performance Problems With SQL Stored Procedures13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Thu, 11 Jul 2002 15:21:31 -0500511_iso-8859-1 Peter,
Two quick ideas: 1) Is a commit being issued at the end of the work? 2)Is the INTERRUPT_ENABLED check box selected in the gateway datasource configuration? (a user might be canceling the app or shutting down the pc leaving the thread hanging).
Dave
-----Original Message----- From: Krawetzky, Peter J [mailto:KrawetzkyPJ@AETNA.COM] Sent: Thursday, July 11, 2002 10:41 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Performance Problems With SQL Stored Procedures [...]
16706 30 15_Re: DB2 Connect7_DB2 DBA16_db2dba@EMAIL.COM31_Thu, 11 Jul 2002 20:40:30 +0000190_iso-8859-1 Yes you do need DDF.
The message you get look like this:
DSNL004I - DDF START COMPLETE LOCATION ???? LU ????.???? GENERICLU -NONE DOMAIN ??? TCPPORT ??? RESPORT ????
16737 144 29_Adminstrative clients on Unix15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US31_Thu, 11 Jul 2002 15:51:55 -0500579_- UDB 7.1 on AIX 4.3
I was in a meeting discussing issues with distribution of DB2 UDB Windows clients accessing DB2 UDB databases on Unix. Someone mentioned a solution of instead having a central Unix application server with the client tools which could be accesses through a browser or something like Xwindows. While I know that, it is not a solution for applications that run on the windows environment needing a CLI interface etc, it may be a solution for database administration clients that invoke tools like Command Center, Control Center etc. [...]
16882 72 22_SQL query help, please11_Robert Jans26_robert.jans@ALBERTSONS.COM31_Thu, 11 Jul 2002 14:59:56 -0600557_iso-8859-1 Hi. Listers. I've got a very painful (for me) query that I'm just at a loss to improve the performance of. This is coming into DB2 V6 on OS/390 from a web app we're developing. This query must run in under 10 seconds (sounds easy). Currently, I can get it to run in about 15 seconds, down from the developers 2 to 3 minutes by building an index containing all the columns needed from table ITEM_ALB_COMMON and update then update the clusterratiof to 100% to convince the optimizer to use this index. If I don't change the cluster ratio, the [...]
16955 83 56_db2dclgn and C host variables for CHAR(n) data [DB2 UDB]14_Steve Westfall26_Steve.Westfall@EQUIFAX.COM31_Thu, 11 Jul 2002 16:13:34 -0500397_us-ascii Our platform: DB2 UDB EEE on AIX.
All IBM documentation that I have seen says that for an SQL column type of CHAR(n) (where n is greater than 1), the C language host variable should be defined as char[n + 1]. Yet, in spite of this, DB2's db2dclgn command generates a C host variable with length n instead of n+1. Here is an example for the STAFF table in the SAMPLE database: [...]
17039 73 40_Re: DB2 PM User Authorization (sic) Exit14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 11 Jul 2002 16:21:29 -0500485_us-ascii What can I say? I'm an idiot sometimes (but then, we've already established that, haven't we?). Oh well, maybe in my next lifetime...
Thanks, David
Raymond Bell cc: Sent by: DB2 Data Subject: Re: DB2 PM User Authorization (sic) Exit Base Discussion List
07/10/02 07:01 PM Please respond to DB2 Data Base Discussion List [...]
17113 204 57_FW: Buffers Never Being Used and MVS Subpools for Buffers24_ANDERSON Margaret * IRMD29_Margaret.Anderson@STATE.OR.US31_Thu, 11 Jul 2002 14:35:51 -0700564_- I'd like to thank those who helped with this question from a couple of days ago. This is to complete the thread as to determinations that have been made. Once folks reminded me (as I was coming out of shock about the bufferpool situation here) that all the buffers in the pool get allocated on first use of the pool, that was when TMON's label of "ALLOCATED BUFFERS" meaning those "in use" at the moment because of locks, updates not written to disk yet and pages where actual reads are in progress (in other words, "unstealable" buffers) the light dawned. [...]
17318 71 18_Re: case statement13_Neil Courtney37_neil.courtney@TEAM.TELSTRACLEAR.CO.NZ31_Fri, 12 Jul 2002 09:41:02 +1200341_- Derez, you might like to try something like this:
SELECT cola, colb, colc, cold,cole,colf, colg FROM user_table A, (SELECT CURRENT TIME AS TM FROM SYSIBM.SYSDUMMY1) AS TMP WHERE TM < (SELECT CURRENT TIME + 30 SECOND AS TM1 FROM SYSIBM.SYSDUMMY1) AND indexa = host-variable AND col_b = host-variable optimize for 1 row with ur; [...]
17390 80 18_Re: case statement10_Mark Doyle19_mdoyle@JCPENNEY.COM31_Thu, 11 Jul 2002 16:51:06 -0500376_iso-8859-1 You didn't mention what platform, etc, but in DB2 for OS/390, you have some 'subsystem-level' facilities that will do just that. Look for documentation on the DSNRLST Resource Limit Facility or the QMF Governor. There may be simular facilities in the other platforms. However, if you specifically want to do it using only SQL, then I think you're out of luck. [...]
17471 122 26_Re: new subsystem help!!!!14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 11 Jul 2002 17:07:20 -0500623_us-ascii This is just my personal opinion (from my experiences installing DB2 many, many times since 1985, and from about 3 years worth of PeopleSoft experience):
I believe it's much better if you count the ACTUAL number of databases, tablespaces, tables, views, plans, packages, etc. and plug REAL, VALID numbers into the DSNTIPx panels. If you take the time to do this, you can be reasonably assured you get the "right" sizes for Catalog & Directory tablespaces, EDM pool sizes, RID pool sizes, SORT pool sizes, etc. Using the figures you supply on the Install/Migration clist panels, the DSNTINST clist goes [...]
17594 71 54_Re: questions regarding the Oracle Transparent Gateway12_Raymond Bell17_rbell@NZ1.IBM.COM31_Fri, 12 Jul 2002 10:13:51 +1200534_us-ascii Hi Larry,
First up, I guess you mean 4.0.1.1 of the OTG, not 4.1.1. We're in the same boat as you; DB2 V5 (I'm guessing that's you too), going to V7, currently have the OTG for DB2 4.0.1.1. I've been trying to get my hands on the 8.1.7 version of the OTG to no avail - not Oracle's fault; an internal ordering nightmare - and have just started to talk to Oracle directly. Anyway, for fun and 'cause I'm bored (yeah, right) I decided to install OTG 4.0.1.1 in our new playpen V7 subsystem just to see if it works. [...]
17666 54 40_Re: DB2 PM User Authorization (sic) Exit12_Raymond Bell17_rbell@NZ1.IBM.COM31_Fri, 12 Jul 2002 10:19:10 +1200277_us-ascii Chill, David. You know I was just poking fun at you, doncha? Like I said, your comments were useful 'cause it got me really thinking about how the exit works. Badly, as it turns out, but at least I know.
Don't wait for the next lifetime - we need you now! [...]
17721 112 26_Re: new subsystem help!!!!14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 11 Jul 2002 17:16:04 -0500426_us-ascii To add a little bit to this, 600 plans seems pretty excessive as well. PeopleSoft uses a lot of dynamic SQL (or at least it did when I was working with it), so there are probably very few plans & packages to be bound. Views 10 is far too low. Be careful about how many "tables per tablespace" and "indexes per table" you specify -- they are far higher for PeopleSoft than for "normal" application installations. [...]
17834 134 55_Re: PeopleSoft DB2 Install (Was New subsystem help!!!!)14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 11 Jul 2002 17:26:03 -0500338_us-ascii [snip] As far as recommendations from Peoplesoft, it has been like pulling teeth to get any DB2 for OS/390 information from them. We contacted the person who is suppose to be doing the installation and he was unfamiliar with the DB2 installation panels so was going to contact a DBA but I haven't heard from him yet. [snip] [...]
17969 66 18_Re: case statement14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 11 Jul 2002 17:31:58 -0500573_us-ascii Are these queries Dynamic SQL or Static SQL?
Thanks, David
"Derez D. Lusk" cc: Sent by: DB2 Data Subject: case statement Base Discussion List
07/11/02 02:54 PM Please respond to DB2 Data Base Discussion List
Hi.. We have some long running queries and would like to limit the execution time is there a way to use a CASE statement to terminate a query [...]
18036 153 26_Re: SQL query help, please15_Barutcu, Cuneyt26_cbarutcu@IDCFS.STATE.IL.US31_Thu, 11 Jul 2002 17:36:20 -0500922_iso-8859-1 Robert,
Here is my two cents..
It looks like you can eliminate the first temp table (A). Your query would look like the following . This is a quick and dirty improvement. Give it a try.
SELECT A.INV_CS_QTY, A.INV_EA_QTY, A.ITEM_NUM, A.ITM_PK_QTY, RTRIM(A.SZ_TXT) SZ_TXT, RTRIM(A.INV_ITM_DESC) INV_ITM_DESC, A.CASE_WT_QTY, A.STR_CS_CST_AMT, A.COI_ALLWC_AMT, A.STR_EXT_CST_AMT, RTRIM(A.DRCT_DLV_CD) DRCT_DLV_CD, A.RTL_QTY, A.RTL_PRC_AMT, A.GP_MRGN_PCT, A.ORIG_INV_NUM, A.OUTS_RSN_CD, RTRIM(B.OUTS_RSN_DESC) OUTS_RSN_DESC, C.CATEGORY_NUM, C.SUBCAT_NUM FROM PCRD2LO.VOREN_OUTS_DETAIL A LEFT OUTER JOIN (SELECT CATEGORY_NUM, SUBCAT_NUM, item_numb, dist_ctr_num FROM PCRD2LO.VITEM_ALB_COMMON WHERE DIST_CTR_NUM = 8231 and rec_orgn_aplcn_nm = 'WHSE') C ON A.Item_NUM = C.Item_NUMB LEFT OUTER JOIN (SELECT OUTS_RSN_DESC, OUTS_RSN_CD FROM PCRD2LO.VOREN_OUTS_RSN WHERE DIST_CNTR_ID = 8231) B ON [...]
18190 119 18_Re: case statement10_Mark Doyle19_mdoyle@JCPENNEY.COM31_Thu, 11 Jul 2002 17:57:44 -0500502_iso-8859-1 Neil,
I don't think your SQL will give you what you think it does. I tried the following SQL on a multi-million row table (DB2 V6 for OS/390 & SPUFI). The query ran almost 1 minute 30 seconds wall time (by my watch) to get to the point it did -- it still ran over the resource limit. But what I find interesting is that the TM value of the last row is the same as the TM value of the first row. This leads me to believe that current time(stamp) is retrieved only once per query. [...]
18310 35 18_Re: case statement13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 11 Jul 2002 19:29:59 -0400322_iso-8859-1 >This leads me to believe that current time(stamp) is retrieved only once per query.
Hello Mark,
I have seen that written somewhere from a source I consider reputable -- Either from a doc somewhere or in a presentation. It might even have been stated by a DB2er here, maybe it was Jim Ruddy. [...]
18346 16 49_Leon Katsnelson/Toronto/IBM is out of the office.0_15_leon@CA.IBM.COM31_Thu, 11 Jul 2002 20:18:08 -0400440_us-ascii I will be out of the office starting July 11, 2002 and will not return until July 15, 2002.
I will respond to your message when I return. Shili Yang will be my back up.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
18363 151 55_Re: PeopleSoft DB2 Install (Was New subsystem help!!!!)14_Melissa Rogers26_mrogers@NYSTRS.STATE.NY.US31_Thu, 11 Jul 2002 20:25:58 -0400666_iso-8859-1 Thanks David for all of you comments, suggestions and help - it is greatly appreciated.
-----Original Message----- From: David S. Waugh [mailto:dwaugh@NOTES.STATE.NE.US] Sent: Thursday, July 11, 2002 6:26 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: PeopleSoft DB2 Install (Was New subsystem help!!!!)
[snip] As far as recommendations from Peoplesoft, it has been like pulling teeth to get any DB2 for OS/390 information from them. We contacted the person who is suppose to be doing the installation and he was unfamiliar with the DB2 installation panels so was going to contact a DBA but I haven't heard from him yet. [snip] [...]
18515 25 39_DB2V6 Package bind using over 5 min CPU12_Warren Homer17_whomer@CSC.COM.AU31_Fri, 12 Jul 2002 11:25:27 +1000356_us-ascii Hi all,
could anyone out there explain why DB2 would require over 5 min CPU to bind a package. The package does contain several joins on fairly large tables (approx 1.2Gb, 21 million rows each), but I have not seen a bind require this much CPU before. When a couple of the joins were removed the bind CPU dropped down to 0.65 min CPU. [...]
18541 25 12_(no subject)12_Dave Johnson21_DavidLJohnson@AOL.COM29_Thu, 11 Jul 2002 22:09:21 EDT20_US-ASCII unsubscribe
18567 65 43_Re: DB2V6 Package bind using over 5 min CPU12_Troy Coleman29_troycci@COLEMANCONSULTING.COM31_Thu, 11 Jul 2002 21:29:31 -0500485_us-ascii I've seen this in large installations when the SQL is complex with view on view processing. You may want to reorg your catalog and run runstats. This may help your SQL prepare times.
Cheers, Troy Coleman DB2 Technologist
IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for OS/390 IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for UNIX, Linux, Windows and OS/2 IBM Certified Advanced Technical Expert DB2 - DRDA [...]
18633 39 76_How to interpret 'Time waited for prefetch (ms)' - would it include iowaits?10_Umesh Apte19_apteumesh@YAHOO.COM31_Thu, 11 Jul 2002 19:30:12 -0700444_us-ascii Hi list,
I have an instance with 7 nodes. I have value of NUM_IOSERVERS set to 48 for each node. I know this could on higher side but I was hoping to error on side of 'too much' rather than 'too little'.
As prep for actual run, when the data from base table (15 million jrnl line) gets moved to work tables I get a huge - real huge 'Time waited for prefetch (ms)' readings in snapshot monitor, as high as 171301 ms. [...]
18673 48 18_Re: case statement14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 12 Jul 2002 12:47:25 +1000542_US-ASCII SQL Reference: General rules for special registers.
James Campbell
On 11 Jul 2002 at 19:29, Seibert, Dave wrote:
> >This leads me to believe that current time(stamp) is retrieved only once > per query. > > Hello Mark, > > I have seen that written somewhere from a source I consider reputable -- > Either from a doc somewhere or in a presentation. It might even have been > stated by a DB2er here, maybe it was Jim Ruddy. > > If my memory weren't so lousy and I weren't so lazy, I'd be able to tell > you. > [...]
18722 61 31_Stored procedures usefulness...16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Thu, 11 Jul 2002 19:49:27 -0700688_us-ascii Hello!!
We are OS/390 V6. I have some very fundamental questions about stored procedures. The admin. Guide says suggests using stored procedures for a 'client/server' application that executes many 'remote SQL' statements. Remote SQL statements can create many network send and receive operations, which results in increased processor costs.
What does 'remote SQL' mean? Does it mean that only the programs which 'connect' to another location (by DRDA or Private protocol) can get benefited by Stored Procedures? Essentially in our situation where there are no 'cross platform' applications (we have 2 LPARs but no application accessing any other platform but [...]
18784 32 33_Language environment question....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Thu, 11 Jul 2002 19:50:47 -0700346_us-ascii Hello !!
Per the DB2 appl. Guide says, an external stored procedure can be written in Assembler, C, C++, COBOL, Java, REXX or PL/I. It further says: "All programs must be designed to run using Language Environment."
What is "Language Environment" and how do we "design" the programs to run using Language Environment? [...]
18817 93 18_Re: case statement13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Thu, 11 Jul 2002 23:12:31 -0500667_us-ascii On OS/390 - V6 - Static sql
"David S. Waugh" To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: case statement Sent by: DB2 Data Base Discussion List
07/11/02 05:31 PM Please respond to DB2 Data Base Discussion List
Are these queries Dynamic SQL or Static SQL?
Thanks, David
"Derez D. Lusk" cc: Sent by: DB2 Data Subject: case statement Base Discussion List [...]
18911 109 25_AW: Re: Critical datasets41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Fri, 12 Jul 2002 08:36:08 +0200428_iso-8859-1 Thanks Massimo,
that's exactly what we're up to but it doesn't really solve the problem for good, does it ? You disable ONE volume for new allocations, reorg the index and it ends up on yet another critical volume .. get the idea ?
Robert,
we might try and ask our SMS-folks to set up the respective ACS-routines, even if they are a pain in the a..., guess, they won't exactly jump with joy [...]
19021 43 47_V7 Install - BLKSIZE for Archive Files Question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Fri, 12 Jul 2002 10:04:06 +0100393_iso-8859-1 Guys
We currently have our BLKSIZE value in Zparm (for archive file writes to 3390 disk) set to 24576 (one 3390 track /2 - 4096).
From looking at my notes from the V6 install and V5 upgrade I can see that we and SVL made a decision to set this this way. I am now trying to re-visit the value again for a V7 install have only the following as a guide (my notes) : [...]
19065 22 11_Unsubscribe10_Keith Kuhn21_Keith_Kuhn@WENDYS.COM31_Fri, 12 Jul 2002 06:18:55 -04009_us-ascii
19088 59 43_Re: DB2V6 Package bind using over 5 min CPU12_Gerald Hodge20_ghodge@ATTGLOBAL.NET31_Fri, 12 Jul 2002 06:51:07 -0500562_us-ascii Warren:
How large is the DBRM? I mean physically? The number of bytes and the number of statements. The longest I have ever seen is eight hours of elapsed time, I have no memory of the CPU. There used to be a competition of sorts between one place in Australia and one in California as to the longest bind time. These problems were resolved by changing the program design and the use of packages. Both initial DBRMs were from programs that were over 32,767 statements and this cause a problem in using a two byte field for the SQL Statement [...]
19148 154 26_Re: SQL query help, please13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 12 Jul 2002 07:15:03 -0500342_iso-8859-1 Robert,
Without explain ouput, column & table cardinalities and index data, it is difficult to determine why you are not obtaining the desired response time. Also without the explain output I cannot tell whether the NTEs are being merged or materialized (they should be merged in V6 unless OJPERFEH is disabling this). [...]
19303 36 51_Re: V7 Install - BLKSIZE for Archive Files Question13_LANGLEY Moira25_moira.langley@ATOFINA.COM31_Fri, 12 Jul 2002 14:23:38 +0200241_us-ascii
Hi Les
After a rather painful experience at a previous site when we (inadvertently - don't ask!) changed to a larger blksize, I've always stuck with 24576 as per the recommendation in APAR II06249.
Cheers Moira
19340 163 26_Re: SQL query help, please11_David Nance16_DWNance@FHSC.COM31_Fri, 12 Jul 2002 08:40:48 -0400544_US-ASCII Robert, I have re-written the query, but that will not improve the speed at all as DB2 is doing the rewrite already. I did it just for easier reading. It is kind of odd to have the larger table being the parent in this relationship, normally, you would see the smaller tables being the parent, meaning the left outer join would not be to the smallest table but the reverse. You don't give any info about the explain what indexes your query is using other than the one you created. You did mention that you created an index on the [...]
19504 21 3_WG:13_Chrigu Brambi35_Christian.Brambilla@GS-VBS.ADMIN.CH31_Fri, 12 Jul 2002 14:46:24 +0200318_iso-8859-1 > set db2-l nomail > set db2-l nomail > > > > > > >
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
19526 98 51_Re: V7 Install - BLKSIZE for Archive Files Question0_17_sjvagnier@AEP.COM31_Fri, 12 Jul 2002 08:57:54 -0400466_us-ascii Leslie, we are also running with archive blksize of 24576 for DB2 V7. I have performed several DB2 Disaster recoveries at our hot site using this blksize with no problems.
Regards..
**************************************** Steve Vagnier Principal IT Systems Administrator American Electric Power Information Management Infrastructure One Riverside Plaza Columbus, Ohio 43215 Email: sjvagnier@aep.com Phone: 614-223-3677 Audinet: 200-3677 [...]
19625 108 51_Re: V7 Install - BLKSIZE for Archive Files Question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Fri, 12 Jul 2002 14:13:17 +0100422_- Moira
thanks for the APAR number, I'll search and read it ..
I knew I had configured it for a reason, I just couldn't determine WHY from my notes :-)
best regards
Leslie
-----Original Message----- From: LANGLEY Moira [mailto:moira.langley@ATOFINA.COM] Sent: Friday, July 12, 2002 1:24 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: V7 Install - BLKSIZE for Archive Files Question [...]
19734 18 59_Can you issue a CALL stored procedure statement from SPUFI?20_ARCHER, MARY-ELLEN H17_MARCHER@SCANA.COM31_Fri, 12 Jul 2002 09:21:13 -0400429_iso-8859-1 We are running OS/390 DB2 v6. I have a stored procedure that is written in COBOL. I have a COBOL program that is calling the stored procedure that is working successfully. Can I also test the stored procedure through SPUFI with a DB2 CALL statement? I get a -084. We are new to stored procedures and our programmers are looking for a way to test the stored procedure before they write the calling program. thanks [...]
19753 58 11_Unsubscribe10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Fri, 12 Jul 2002 07:42:31 -0600597_iso-8859-1 Unsubscribe
************************************************************************************************** The contents of this email and any attachments are confidential. It is intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to any one or make copies.
** eSafe scanned this email for viruses, vandals and malicious content ** **************************************************************************************************
19812 34 63_Re: Can you issue a CALL stored procedure statement from SPUFI?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 12 Jul 2002 09:42:55 -0400562_iso-8859-1 Hi Mary-Ellen,
No you can't issue the Call statement from SPUFI or QMF.
If you have DB2 v7 on Unix or Windows & DB2 connect, you can issue the call from the command line or command center there. The stored procedure builder has a nifty feature enabling testing remote stored procedures also. You connect to the server and bring up a list of procedures and can right click on the procedure you want to test. From there you can review the properties for the SP and you can invoke it. You click Run and it prompts you for the required [...]
19847 79 51_Re: Performance Problems With SQL Stored Procedures12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 12 Jul 2002 06:44:47 -0700292_us-ascii Peter, I've seen this several times when a very large result set is being sent back. And it could be a large number of rows or a very large (over 25k) single row. In both cases, it looks like the SP has finished and is waiting for something. Its just network transmission time. [...]
19927 87 35_Re: Stored procedures usefulness...12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 12 Jul 2002 06:54:05 -0700421_us-ascii Basically "remote SQL" is SQL that is originating from some type of client using DB2 Connect/Neon Shadow, etc or from another "remote" DB2. It is not SQL originating on the same LPAR as the DB2 is running on.
Stored procedures will reduce the number of network transmission in this case, as you only have to send one SQL statement here (call sp) versus the potentially hundreds that the SP performs. [...]
20015 63 33_Re: Adminstrative clients on Unix12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 12 Jul 2002 06:58:06 -0700507_us-ascii I can't give you the manual reference. But one option is to run the application on Windows Citrix. Then the user connects to the application thru a web browser and executes the application on that Citrix server. We've done that for a number of app's. Especially some Microsoft Access applications that access DB2 OS/390. The Microsoft Access app and DB2 Connect are on the Citrix server. We "publish" the URL. Then the user uses his web browser to connect to the app and then execute the app. [...]
20079 84 37_Re: Language environment question....12_John Maenpaa24_john_maenpaa@YLASSOC.COM31_Fri, 12 Jul 2002 08:58:17 -0500420_iso-8859-1 On Thursday 11 July 2002 09:50 pm, you wrote: > Hello !! > > Per the DB2 appl. Guide says, an external stored > procedure can be written in > Assembler, C, C++, COBOL, Java, REXX or PL/I. It > further says: "All programs > must be designed to run using Language Environment." > > What is "Language Environment" and how do we "design" > the programs to run > using Language Environment? > > TIA > Raquel. [...]
20164 123 37_Re: Language environment question....14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 12 Jul 2002 09:35:21 -0500525_us-ascii OK, let me dust off the old IBM SE part of my brain and try to answer this question...
"Language Environment" started out as "IBM SAA AD/Cycle LE/370" back in the late 80s/early 90s. In that incarnation, it was an integral part of IBM's AD/Cycle (Application Development Cycle) strategy. When the AD/Cycle strategy collapsed a few years later (nobody really understood what AD/Cycle was, not even those of us in IBM at the time), Language Environment was one of the pieces of the strategy that survived. [...]
20288 30 35_Re: Stored procedures usefulness...9_Mike Deer20_Michael.Deer@UAL.COM31_Fri, 12 Jul 2002 09:43:54 -0500572_ISO-8859-1 Another use we have found for DB2 stored procedures is to provide access to non-DB2 data. For example, if you have a java application running on a unix web/app server (ie:Weblogic from BEA systems) connected to DB2 via DB2 connect, you can set up a stored procedure to allow the application to get data from an IDMS database by issuing a simple sql CALL statement. While the vast majority of our web-based apps access DB2 only, this is a niche use of stored procedures that we found very useful where you want web-based apps to have access to IDMS without [...]
20319 59 63_Re: Can you issue a CALL stored procedure statement from SPUFI?15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 12 Jul 2002 10:55:55 -0400524_iso-8859-1 Download DB2 connect personal edition and create a DSN. Then Download WinSQL then execute the stored procedure like
{call (parameter)}
You can execute any other sql also.
This is the easiest way I found.
thanks..sibi
-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: Friday, July 12, 2002 08:43 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Can you issue a CALL stored procedure statement from SPUFI? [...]
20379 149 17_Re: CICS and LOBs12_Blair Moores19_blair.moores@GM.COM31_Fri, 12 Jul 2002 11:06:14 -0400654_us-ascii
Mike:
Here is another opinion from a CICS/DB2 Specialist...
As far as the discussion is concerned, as always, there are considerable variables to be considered.
1. What will be the transmission rate of the PDF's and JPEGS data to the Web? 2. How much over head is associated with the transmission processing. (i.e., long headers, short headers, commits, etc.) If the transmit is being done through MQ series, we are talking major over head. 3. What is being planned to use for disaster recovery? How will the disaster recovery "stuff" be engineered, planned, executed, tested? 4. If the transmission processing [...]
20529 105 36_Re: Poor performance - type2 indexes14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 12 Jul 2002 16:11:00 +0100501_iso-8859-1 Hmm,
Still seems a bit odd (though much better as you say).
I've copied db2-l on this reply in case there are any things I have missed!
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com -----Original Message----- From: Deeney, Maggs [mailto:margaret.deeney@eds.com] Sent: 12 July 2002 14:40 To: Grainger, Phil Subject: [DB2-L] Poor performance - type2 indexes [...]
20635 43 15_Re: Unsubscribe19_prasad rao Mocharla19_mocharlap@YAHOO.COM31_Fri, 12 Jul 2002 08:22:55 -0700624_us-ascii
Grace Chen wrote: Unsubscribe **************************************************************************************************The contents of this email and any attachments are confidential.It is intended for the named recipient(s) only.If you have received this email in error please notify the system manager or thesender immediately and do not disclose the contents to any one or make copies.** eSafe scanned this email for viruses, vandals and malicious content **************************************************************************************************** [...]
20679 138 65_New Article Available - Testing & Debugging DB2 Stored Procedures11_John Wynton21_jwynton@THEMISINC.COM31_Fri, 12 Jul 2002 11:24:46 -0400516_- Dear DB2 List:
We have expanded the "Technical Tips" section of our web site to include an article on Testing & Debugging DB2 Stored Procedures (OS/390). This is a copy of the presentation Themis Senior Systems Advisor, Dave Cohn, gave at this year's IDUG in San Diego
A PDF copy of the David's presentation can be downloaded from the "Technical Tips" Section which can be found on our home page at http://www.themisinc.com/home . [...]
20818 17 63_Re: Can you issue a CALL stored procedure statement from SPUFI?10_Mike Ervin16_mervin@PHEAA.ORG31_Fri, 12 Jul 2002 10:15:34 -0500348_- Hey Mary-Ellen
When we first started testing Stored Procedures(SP) I wrote a VB app that called a SP on an os/390. If you have anyone that knows VB have them write an app, then the developers can test the SP from thier windows platform. That is assuming the database is cataloged on the machine and they have the DB2 client installed [...]
20836 22 15_Date data types16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Fri, 12 Jul 2002 10:17:37 -0500420_- Greetings Oh esteemed listees,
Any rumblings about date like data types, for example year, month or day? Does anyone think that IBM will or should (or not) implement some built in data types for this?
Granted, the scalar functions exist, but they require storing a date. In the event a design is interesting in the current year, why should we be forced to store a data when all we want is the year? [...]
20859 79 63_Re: Can you issue a CALL stored procedure statement from SPUFI?12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 12 Jul 2002 08:40:20 -0700639_us-ascii You really don't need WinSQL. You can just go into the command line of DB2 Connect and issue the connect and sql directly. Or you can use Command Center. Either allows direct testing of the sql.
Plus with DB2 connect, you've got stored procedure builder. If you enable the stored procedures for this, you can test stored procedures written in any language with it, including COBOL extremely easily. --- "Philip, Sibimon" wrote: > Download DB2 connect personal edition and create a DSN. Then Download WinSQL > then execute the stored procedure like > > {call (parameter)} > > You [...]
20939 39 30_Copy DB2 Archive Tape question11_Lee Mandell16_DBMSUser@AOL.COM29_Fri, 12 Jul 2002 11:38:09 EDT334_US-ASCII Hi All, We would like to setup NetView to catch DB2 message when an archive is completed and automatically start a started task to copy the archive tape to another tape for disaster recovery. What DB2 message should we look at and also do you have an example of the NetView code to catch the message and start the task? [...]
20979 48 48_Bufferpool Management: Random/Sequential Objects12_Hayden Jones25_Hayden_Jones@GRAINGER.COM31_Fri, 12 Jul 2002 10:30:28 -0500393_us-ascii Circumstances prevent us from purchasing a tool for bufferpool management. We do have a monitoring tool which gives read I/O statistics by object. That should enable us to determine which objects are primarily sequentially accessed and which are primarily randomly accessed.
But it doesn't put a big sign on the staistics saying "THIS IS SEQUENTIAL" and "THIS IS RANDOM". [...]
21028 81 63_Re: Can you issue a CALL stored procedure statement from SPUFI?13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Fri, 12 Jul 2002 10:48:16 -0500556_iso-8859-1 Interesting... Can WinSQL handle "out" parameters? (I didn't see a way that it did)
Personally, I've found that just using the DB2 Command line interface is a pretty quick and easy way. You can pass input parameters similar to anywhere else - out parameters can be represented by a parameter marker ("?") and the command line interface is apparently handling this. If there is a result set, it is also displayed. For example - calling a procedure that accepts two parameters and returns one (this one doesn't return a result set)... [...]
21110 150 19_Re: Date data types14_Grainger, Phil20_Phil.Grainger@CA.COM31_Fri, 12 Jul 2002 16:58:41 +0100378_iso-8859-1 To be pedantic, a year is not a date.....
Of course, you COULD define a UDF (User Defined Type) to suit your requirements.........
YEAR - 4 digit numeric, range 1900-2400 (or whatever) MONTH - 2 digit numeric, range 1-12 DAY - 2 digit numeric, range..... (oh dear, you can't define the range of a day part of a date without knowing the month part) [...]
21261 20 52_Re: Bufferpool Management: Random/Sequential Objects16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Fri, 12 Jul 2002 10:59:31 -0500584_- Hi Hayden,
The -DIS BUFFERPOOL gives more information than what you're listing.
DB2 has all (er-most) of the information you need via traces. Is your monitoring tool using the traces? Do you have the correct traces turned on?
Could there be more information available but not reported?
have fun! pg
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
21282 15 17_Re: CICS and LOBs16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Fri, 12 Jul 2002 11:06:30 -0500367_- Hi Mike,
What about using non-OS/390 (gasp) DB2? What is the right-size for this system?
phil g
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
21298 113 63_Re: Can you issue a CALL stored procedure statement from SPUFI?15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 12 Jul 2002 12:33:56 -0400513_- It takes sometime to bring up command center for me and interface is not that good personally for me. So I use very rarely the command center or stored procedure builder. It is just a personal preference. WinSQL can connect to other DBMS also.
thanks..sibi
-----Original Message----- From: Myron Miller [mailto:myronwmiller@YAHOO.COM] Sent: Friday, July 12, 2002 10:40 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Can you issue a CALL stored procedure statement from SPUFI? [...]
21412 111 63_Re: Can you issue a CALL stored procedure statement from SPUFI?15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Fri, 12 Jul 2002 12:44:57 -0400499_iso-8859-1 Generally we are not code output variable if it interact with client application since client application is not able to get output parameter properly. So I have not tested this in Winsql. I will try this some time.
Thanks..Sibi
-----Original Message----- From: Vaughan, Mike [mailto:Vaughan.Mike@PRINCIPAL.COM] Sent: Friday, July 12, 2002 10:48 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Can you issue a CALL stored procedure statement from SPUFI? [...]
21524 20 26_Re: SQL query help, please11_Robert Jans26_robert.jans@ALBERTSONS.COM31_Fri, 12 Jul 2002 11:01:16 -0600332_- My thanks to David, Terry, Cuneyt and Jeff L'Italien. even with the flimsy data I gave out, they all nailed the problem. I had missed a column in my index. Bad, bad man. The query now runs in less than 2 seconds and the developers have elevated the DBA's to the level of magicians I don't think I'll tell them different. [...]
21545 12 19_Re: Date data types16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Fri, 12 Jul 2002 12:17:25 -0500293_- ooh - that's a good idea!
pg
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
21558 21 13_Trigger names16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 12 Jul 2002 13:50:54 -0400455_iso-8859-1 Please, someone.
Share with us your naming conventions for TRIGGERs.
(DB2 Universal Database for OS/390 and z/OS)
Regards, eric pearson NS ITO Database Support
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
21580 75 57_Re: List of Stage 1 and Stage 2 Predicates (v7 on OS/390)18_Elizabeth A. Pratt14_prattea@NU.COM31_Fri, 12 Jul 2002 13:59:47 -0400335_us-ascii Hi Phil -
Sorry for the delay... been on vaca for a couple of weeks and I am just catching up on e-mails.....
At the IDUG conference I picked up a very handle little quad-fold brochure from CBI ( Computer Business International). They are a training and consulting group. Their web-site is www.cbi4you.com. [...]
21656 15 45_Re: Partitioning Key - Using month(datefield)16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Fri, 12 Jul 2002 13:12:51 -0500366_- nope...
However, using a month will limit you to 12 partitions - is that what you want?
phil g
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
21672 108 57_Re: List of Stage 1 and Stage 2 Predicates (v7 on OS/390)16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM31_Fri, 12 Jul 2002 11:36:32 -0700663_us-ascii Phil,
Here's a link which provides stage 1 and stage 2 predicate definitions for DB2 OS390 V7.
Regards, Jeff L'Italien American Express
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnagh11/5.8.3.2?DT=20010726121552#TBLISARG
From: "Elizabeth A. Pratt" @LISTSERV.YLASSOC.COM> on 07/12/2002 01:59 PM AST
Please respond to "DB2 Data Base Discussion List"
Sent by: "DB2 Data Base Discussion List"
To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: Re: List of Stage 1 and Stage 2 Predicates (v7 on OS/390) [...]
21781 53 17_Re: Trigger names13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Fri, 12 Jul 2002 13:34:41 -0500527_iso-8859-1 Hi, My trigger name are based on a tablespace - also 1 table/tablespace.
So, the tablespace format is: exxxxxx Where e = the environemnt. D-Development, P-Production etc. The details is not relevant here, but aassnn where aa= the system/application, ss=sub-system within the application and nn=sequential number.
Trigger is xxxxxxtn Where t=trigger type (Update, Insert,Delete) and n = sequential number. This allows 10-triggers for each type - i.e. 30 triggers. I do not plan hitting that limit! [...]
21835 55 17_Re: Trigger names15_Barutcu, Cuneyt26_cbarutcu@IDCFS.STATE.IL.US31_Fri, 12 Jul 2002 13:38:39 -0500410_iso-8859-1 Eric,
Here is what we do around here. The format is
Schema.triggername
naming convention follows XXYYWWWW.XXTGYY01 - ZZ
schema - lets us keep the trigger names same between different databases within the same subsystem.
XX - denotes to agency, unit, branch or whatever YY - denotes to the application WWWW - denotes to the specific database in the subsystem. [...]
21891 61 17_Re: Trigger names22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Fri, 12 Jul 2002 11:39:34 -0700542_us-ascii Hi Eric,
We use our tablespace names and a combination of before or after and a sequence for order of creation.
Example Tablespace SHM01 Trigger name SHM01B01 -- Before insert trigger SHM01A02 -- After insert Trigger SHM01A03 -- After insert/update trigger SHM01A04 -- After delete trigger
HTH..
Marty Killen Sr. DB2 DBA CNF Inc. 503-450-2681 killen.martin@cnf.com IBM Certified Solutions Expert DB2 V7.1 Database Administration for OS/390 DB2 V7.1 Database Administration for UNIX, Windows and OS/2 [...]
21953 28 33_sql update year of a date field ?15_Neff, Stephen R16_NeffSR@STATE.GOV31_Fri, 12 Jul 2002 14:42:15 -0400612_iso-8859-1 Hello,
Would someone help correct the following sql UPDATE CLSODV5.CLSTCLP
SET YEAR(DATE_ISSUED) = 1975
WHERE YEAR(DATE_ISSUED) = 2000
AND CID LIKE 'DOSPTCMM%'
The idea is to update the year portion of the date_issued field to 1975 when the year
portion of the date_issued field = 2000.
Thank you
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
21982 55 17_Re: Trigger names12_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US31_Fri, 12 Jul 2002 14:09:49 -0500605_US-ASCII AAAAAAAAAAAAAAAxyz
15 A's = Business Function / Table name x = A (after) B (before) y = U (update) I (insert) D (delete) z = sequential number 0-9
When I have a Business Function that involves insert, update and delete triggers on 3 tables, the triggers are sorted (in catalog and lists) by business function first. Instead of by table name or action. We found that sorting by business function helped us identify which triggers were related so we could easily perform maintenance to a business function, like remove all triggers related to a business function or move all the [...]
22038 64 37_Re: sql update year of a date field ?16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM31_Fri, 12 Jul 2002 12:20:24 -0700474_us-ascii Stephen,
Your SQL should look like the following:
SET DATE_ISSUED = DATE_ISSUED - 5 YEARS WHERE YEAR(DATE_ISSUED) = YEAR('2000') AND CID LIKE 'DOSPTCMM%'
The problem you may have (depending on table size) is that because of the YEAR function, this predicate is not indexable.
Regards, Jeff L'Italien American Express
From: "Neff, Stephen R" @LISTSERV.YLASSOC.COM> on 07/12/2002 02:42 PM AST [...]
22103 46 37_Re: sql update year of a date field ?12_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US31_Fri, 12 Jul 2002 14:12:18 -0500501_US-ASCII UPDATE CLSODV5.CLSTCLP
SET DATE_ISSUED = DATE_ISSUED - 25 years
WHERE YEAR(DATE_ISSUED) = 2000
AND CID LIKE 'DOSPTCMM%'
>>> NeffSR@STATE.GOV 07/12/02 01:42PM >>> Hello,
Would someone help correct the following sql UPDATE CLSODV5.CLSTCLP
SET YEAR(DATE_ISSUED) = 1975
WHERE YEAR(DATE_ISSUED) = 2000
AND CID LIKE 'DOSPTCMM%'
The idea is to update the year portion of the date_issued field to 1975 when the year [...]
22150 52 37_Re: sql update year of a date field ?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 12 Jul 2002 15:12:58 -0400504_iso-8859-1 Maybe try:
UPDATE CLSODV5.CLSTCLP SET DATE_ISSUED = DATE_ISSUED - 25 YEARS WHERE YEAR(DATE_ISSUED) = 2000 AND CID LIKE 'DOSPTCMM%'
Regards, eric pearson NS ITO Database Support
-----Original Message----- From: Neff, Stephen R [mailto:NeffSR@STATE.GOV] Sent: Friday, July 12, 2002 2:42 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: sql update year of a date field ?
Hello,
Would someone help correct the following sql UPDATE CLSODV5.CLSTCLP [...]
22203 76 17_Re: Trigger names16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 12 Jul 2002 15:26:58 -0400304_iso-8859-1 It would be very nice if IBM took pity on us poor beknighted mainframers an gave us an 18 character trigger name. Alas, they gave us only 8. I like the convention you use. To fit your 15 A's into 5 bytes will require us to do some serious curtation, but that is probably what we will do. [...]
22280 27 17_Re: Trigger names0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Fri, 12 Jul 2002 14:49:38 -0500431_us-ascii Here's ours....
XXYZZZZN
XX -- Application Y -- Trigger Type, I - Insert, U - Update, E - Delete. (Don't ask. ;-) ) ZZZZ -- 4 character table name. Unique for every table in the DB. N -- Sequence number. Like someone else, we didn't expect to use more than 10 of a particular type of trigger on a single table. Um, last I checked, we were up to 12 Insert triggers on one table. That makes "C", I think. [...]
22308 69 53_Re: sql update year of a date field - handle warnings11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 12 Jul 2002 15:06:38 -0500644_- The sql below wll work but watch out for any Feb 29. Since 1975 was not a leap year and 2000 was, you could get a warning (sqlcode 0, sqlstate 01506). Just a possible gotcha.
HTH.
Suresh
>From: DOUG KESTNER >Reply-To: DB2 Data Base Discussion List >To: DB2-L@LISTSERV.YLASSOC.COM >Subject: Re: sql update year of a date field ? >Date: Fri, 12 Jul 2002 14:12:18 -0500 > >UPDATE CLSODV5.CLSTCLP > >SET DATE_ISSUED = DATE_ISSUED - 25 years > >WHERE YEAR(DATE_ISSUED) = 2000 > >AND CID LIKE 'DOSPTCMM%' > > > > >>> NeffSR@STATE.GOV 07/12/02 01:42PM >>> >Hello, > > [...]
22378 48 17_Re: Trigger names19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Fri, 12 Jul 2002 22:38:37 +0100390_- And here's my effort -
Hsssnnnx
where H is to identify a trigger (because T is table : not sure why we chose H rather than some of the other unused letters).
sss = system ID (links us to database Desssooo and tablespace Ssssmmm) nnn = table number (Tnnn0something) x = letter to make it unique (starting with A, allowing 36 triggers per table (a-z,0-9) !!!) [...]
22427 48 17_Re: Trigger names13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Fri, 12 Jul 2002 15:42:24 -0500419_iso-8859-1 Steve, If the number of triggers did increase, I was wondering if we could get a single SP to be called by the trigger, and get all of the work done in there. Glenn Ps Let me guess E-Erase?
-----Original Message----- From: Steve_Grimes@AISMAIL.WUSTL.EDU [mailto:Steve_Grimes@AISMAIL.WUSTL.EDU] Sent: Friday, July 12, 2002 2:50 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Trigger names [...]
22476 81 17_Re: Trigger names0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Fri, 12 Jul 2002 16:46:48 -0500391_us-ascii Hello Glenn,
Actually, the third character "E", is there because of our larger naming convention for DB2 objects. "D" was already taken for "database". (T - tables, S - tablespace, Z - Stored Procedures with "INOUT" parms, Y - Store Procedures with only "IN" parmspace, G - storage groups, X - Index, etc.) So, you may be right, "E" for erase was probably the thinking. [...]
22558 14 23_Re: DB2UEXT2.C for Unix15_Stephen Poulsen36_stephen.r.poulsen@CO.MULTNOMAH.OR.US31_Fri, 12 Jul 2002 17:08:09 -0500343_- We also compiled the user exit on one box and used the executable on another with no problems. One thing though - the "installation defined variables" that are modified in the c code must be the same on both machines. In other words, the archive, retrieve and error paths must be the same on each machine if you "share" the executable. [...]
22573 190 29_Re: AW: Re: Critical datasets14_Massimo Scarpa16_mocion@INWIND.IT31_Sat, 13 Jul 2002 00:24:28 +0200474_iso-8859-1 Hi
This is not what I meant reeading your reply. We used this procedure to manage some critical datasets (TSS files, hot tables etc).
From your post I didn't understand the problem was an index allocation problem, I supposed that Tablespace A (and its indexes) haven't to be allocated on the same volumes of tablespace B (and its index(es)), after all reorgs. It was not clear (at least to me) that there might be some other critical datasets. [...]
22764 36 38_Stored procedure usefulness and LE....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Fri, 12 Jul 2002 19:07:47 -0700366_us-ascii Thank you David S. Waugh, John Maenpaa and Myron Miller for answering my questions regarding LE and Stored Procedures usefulness.
John, I did check our compile JCL here and it does have SCEELKED and so, I guess we are using LE.
David, the insight you provided into the historical perspective of how LE gained form was really informative. [...]
22801 26 37_CICS Commands in Stored procedures...16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Fri, 12 Jul 2002 19:09:04 -0700596_us-ascii Hello !!
Had a quickie!! Can stored procedures be compiled as CICS-DB2-COBOL programs? I.e. can they contain CICS commands (like EXEC CICS RETURN END-EXEC).
TIA Raquel.
__________________________________________________ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
22828 65 51_Re: V7 Install - BLKSIZE for Archive Files Question14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 13 Jul 2002 19:18:35 +1000368_US-ASCII The block size must be a multiple of 4096. 24756 allows 2 blocks each 6*4096 bytes on a track 28672 allows 1 block of 7*4096 bytes on a track. Even if you archive to tape you should still use 24756 since - the effective space saved is minimal - if you ever want to copy your tape archives back to disk (for recovery), you have to keep the same blocksize [...]
22894 75 17_Re: Trigger names14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 13 Jul 2002 19:18:36 +1000309_US-ASCII Personally, I use the same name for the schema as table creator-id.
And also use it for the schema name of SPs and UDFs that access the table.
I keep database name, environment-id and the like out of the second part of the name. All of that stuff belongs purely in the first part. [...]
22970 61 43_Re: DB2V6 Package bind using over 5 min CPU14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 13 Jul 2002 19:18:37 +1000442_US-ASCII What is important is, not the size of the tables, but the number of potential access paths.
Consider, any table could be the "first" table. It could be accessed either by a ts scan, or by any of its indexes.
Any table it is joined to could be the "second" table, and it could (again) be accessed by a ts scan or through any index, and through any appropriate join type, and for all possible sets of join criteria. [...]
23032 49 41_Re: CICS Commands in Stored procedures...14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 13 Jul 2002 19:18:38 +1000461_US-ASCII Well, yes they can be _compiled_ as CICS-DB2- any_suitable_language. But they cannot be run as CICS programs.
If you track through what happens when an EXEC CICS ... statement is is actually executed, you'll find that the CICS linkage module tracks through a series of OS level control blocks to the TCBX, from whence it pulls the address of an internal CICS control block, which contains the actual address of the CICS processing routine. [...]
23082 107 51_Re: V7 Install - BLKSIZE for Archive Files Question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Sat, 13 Jul 2002 13:07:36 +0100621_- James et al, thanks for the info ... I knew I had it right but you know sometimes you need to know why.
all the best
Les
-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: Saturday, July 13, 2002 10:19 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: V7 Install - BLKSIZE for Archive Files Question
The block size must be a multiple of 4096. 24756 allows 2 blocks each 6*4096 bytes on a track 28672 allows 1 block of 7*4096 bytes on a track. Even if you archive to tape you should still use 24756 since - the effective space saved is minimal [...]
23190 54 42_Re: Stored procedure usefulness and LE....12_Myron Miller22_myronwmiller@YAHOO.COM31_Sat, 13 Jul 2002 11:41:21 -0700603_us-ascii Raquel, You can also benefit using stored procedures without using DRDA. Certainly, they help in a cross-DB2 environment, but within a single LPAR DB2, they can be extremely useful as well.
Myron --- Raquel Rodriguez wrote: > Thank you David S. Waugh, John Maenpaa and Myron > Miller for answering my questions regarding LE and > Stored Procedures usefulness. > > John, I did check our compile JCL here and it does > have SCEELKED and so, I guess we are using LE. > > David, the insight you provided into the historical > perspective of how LE gained [...]
23245 98 37_Re: sql update year of a date field ?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sat, 13 Jul 2002 18:00:03 -0500622_us-ascii Stephen,
To maintain indexability:
SET DATE_ISSUED = DATE_ISSUED - 25 YEARS WHERE DATE_ISSUED BETWEEN '2000-01-01' AND '2000-12-31' AND CID LIKE 'DOSPTCMM%'
Regards Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@LISTSERV.YLASSOC.COM]On Behalf Of Jeff A L'Italien Sent: Friday, July 12, 2002 2:20 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: sql update year of a date field ? [...]
23344 24 37_selecting first 200 rows from a table8_duam lee20_duam_lee@HOTMAIL.COM31_Sun, 14 Jul 2002 14:10:05 +0000357_- Hi listers, I m with version 6 of Db2 and I know that there are fetch for n rows enhancements in versin 7. Due to a urgency we need to select first 200 rows of a table. Could any body suggest me about how to write a sql so that I could select first 200 rows from the table. The table is having 20000 rows at present. It would be a great help for me. [...]
23369 32 41_Re: selecting first 200 rows from a table13_Patric Becker25_Patric_Becker@SIS-WEST.DE31_Sun, 14 Jul 2002 10:49:01 -0500363_ISO-8859-1 Duam,
you didn´t say where you want to do it : in an application program or via direct SQL. And what are the "first" 200 rows ? It doesn´t matter what rows or the first 200 rows referring to a key ?
Well, in an application you can simple count the number of (successful) fetches for a cursor and then end the loop after 200 fetches. [...]