1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l June 2000, week 1 2 123 36_Re: User Defined Functions/RRS woes.0_18_TOUSSAN@UK.IBM.COM30_Thu, 1 Jun 2000 08:29:20 +0100726_us-ascii Do you have a profile ssid.RRSAF in class DSNR and does the connecting authid have read access to it?

Regards, Neil

Email: Neil_Toussaint@uk.ibm.com



Cliff Boley on 31/05/2000 21:22:48

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Neil Toussaint/UK/IBM) Subject: Re: User Defined Functions/RRS woes.







I did look up the 00F0013 message in the Messages and Code but still a little perplexed. The M&C states its a DB2 auth problem but from the concol message text it looks like the issue is with RRS not a DB2 subsystem. The M&C mentions looking in the syslog [...] 126 71 31_Re: removal of SYSADM from DBAs0_18_TOUSSAN@UK.IBM.COM30_Thu, 1 Jun 2000 08:32:04 +0100443_us-ascii The DB2 admin guide describes in detail a technique to avoid the cascade effect. Essentially you make the id an install SYSADM then issue the revoke. Cascade does not occur for an install SYSADM. Remember to reassemble ZPARM afterward!

Regards, Neil

Email: Neil_Toussaint@uk.ibm.com



william.poston@DB.COM on 31/05/2000 22:00:18

Please respond to DB2 Data Base Discussion List [...] 198 85 56_Re: DB2-L Digest - 31 May 2000 to 1 Jun 2000 (#2000-160)0_25_john.halloran@SGIO.COM.AU30_Thu, 1 Jun 2000 18:20:32 +1030443_us-ascii I am having trouble testing a catalog reorg in a test lpar and have come across a strange situation. I have a step that should stop dsndb06.syspkage tablespace as well as the associated index spaces. However, the step finishes with rc=12 but the job output indicates that the stop command completed normally but the tablespaces are not stopped? below is the latest version of the jcl I have tried to use to stop the tablespaces. [...] 284 31 28_Re: Extracting data for test18_Madeleine Fournier27_madeleine.f.fournier@AIB.IE30_Thu, 1 Jun 2000 03:17:21 -0500386_- Hello,

About 3 years ago when I was still in France, the company I was working for (hi guys, if you read me) decided to look for such a tool, in prevision of Y2K and euro migration. I ran some tests for the project I was on, eith File-Aid RDX and Move for DB2. The french company selling Move had an agreement with BMC so I would be very surprised if it had its own tool. [...] 316 13 25_Re: compare/update schema18_Madeleine Fournier27_madeleine.f.fournier@AIB.IE30_Thu, 1 Jun 2000 03:23:13 -0500494_- I am using Rc/Compare (CA) on a monthly basis for the same purpose. I can not only compare 2 different subsystems, but I can also provide a sequential file containing the DDL and compare to a DB2 subsystem (very handy for production/test comparison).

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 330 47 88_Datasharing Very Interesting - How to perform a DR Recovery from one system to a new one23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Thu, 1 Jun 2000 10:02:03 +0100391_iso-8859-1 I would like to put a question to the floor about how you can perform a system copy (disaster recovery of one system to a new system with a name change) in a Datasharing environment. IBM tell us that when you come to change the group name in the BSDS using DSNJU003 you will not be able to do it. In short they said that what we were trying is impossible and cannot be done. [...] 378 65 25_Re: DB2 PM Online Monitor23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Thu, 1 Jun 2000 10:28:34 +0100234_iso-8859-1 Ken I seem to be one of the only people in the world that is seriously using the PM monitor(5 & 6) - I am ofcourse basing this on the fact that I only had a few replies to a mail I put out last week on the PM Monitor. [...] 444 22 38_Re: WLM stored procedures -444 SQLCODE9_Joe Smith18_smithmq@RBOS.CO.UK30_Thu, 1 Jun 2000 04:42:31 -0500514_- Thank you for the replies. Below is the JCL being used. The load library 'ttdf.smithmq.load' is where the load module resides and it has not gone into extents.

Are all libraries required and do they require APF authorisation?

DBVWLM1 PROC RGN=0K,TME=1440,DB2SSN=DBV,NUMTCB=8,APPLENV=STPCPILOT IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=&TME, PARM='&DB2SSN,&NUMTCB,&APPLENV.' STEPLIB DD DISP=SHR,DSN=TTDF.SMITHMQ.LOAD DD DISP=SHR,DSN=SYS1.SCEERUN DD DISP=SHR,DSN=TTDF.DBVV51.SYSDB2.RUNLIB.LOAD [...] 467 33 21_Collections in Plans.12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM30_Thu, 1 Jun 2000 08:19:15 -0400574_us-ascii This thought came to me while working with JAVA/CLI packages but it is applicable to other situations also.

The way to go with JAVA/CLI (JDBC) servlets running under UNIX services and accessing DB2 stored procedures on the same LPAR is to treat the request as local and create a plan with the calling program and stored procedure packages in it. The CLI interface can only use one plan name (DSNACLI is the default) so with many servlets and stored procedures in place, it makes sense to create the DSNACLI plan with a generic collection (such as P.*). [...] 501 25 15_DB2-SAP problem16_Panopoulos Nikos12_npano@NBG.GR30_Thu, 1 Jun 2000 15:18:17 +0300341_- Hello List,

We try to install the database layer of SAP R/3 at DB2 and we have the following problem: A great amount of paging activity takes place at the auxiliary storage, but these pages do not become free after their use. In order to become free we have to stop DB2. Does anybody has an idea about what might be happening? [...] 527 32 53_ongoing tracking of DB2 health - what do you measure?0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Thu, 1 Jun 2000 07:41:40 -0500569_us-ascii I would like to know what some of the rest of you measure in order to track the ongoing health of DB2. In other words, what is the "take the temperature of DB2" monitoring that you do.

For example, on a daily/weekly/monthly basis, we track the per transaction average "in DB2" times (or class 2) for our transactions that access DB2 (in our case, this is IMS and CICS transactions). We also track the number of deadlocks or timeouts (the number of DSNT500I or DSNT501I messages that we get on the ssidMSTR log) on a daily basis. These two measures [...] 560 49 19_Re: DB2-SAP problem14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 1 Jun 2000 08:53:07 -0400569_ISO-8859-1 You have not provided any information about the size of your defined buffer pools and other resources. So.. as a generalization, it sounds like your processor does not have enough real memory.... so some of DB2 is paged to expanded. Since the pages still are owned by DB2 they will not be freed until DB2 is brought down. I expect that the number of pages will vary, and pages are constantly being moved for real to expanded as other pages are moved from expaned to real. Aside from a lack of memory resources, high pages movement rates will impact the [...] 610 77 19_Re: DB2-SAP problem23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Thu, 1 Jun 2000 13:54:07 +0100332_iso-8859-1 Joel yes I agree .. but this does sounds like a classic storage creep problem of the SAP world .. well everyones world but more evident in SAP due to Dynamic nature....

whilst writing I am about to light the engines of the BP tool again in our DS environment .. can I come direct to you if I have any issues? [...] 688 56 19_Re: DB2-SAP problem23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Thu, 1 Jun 2000 13:52:34 +0100419_iso-8859-1 Nikolaos

yes .... you have storage creep .. e-mail me direct and I will assist you to make sure you have everything set correctly ,,., you need to check a few things and apply maintenance.

Leslie Pendlebury-Bowe DB2 SAP etc etc

-----Original Message----- From: Panopoulos Nikos [mailto:npano@NBG.GR] Sent: Thursday, June 01, 2000 1:18 PM To: DB2-L@RYCI.COM Subject: DB2-SAP problem [...] 745 75 19_Re: DB2-SAP problem13_Adrian Savory24_adrian.savory@ZURICH.COM30_Thu, 1 Jun 2000 14:13:32 +0100524_us-ascii Nikos,

It sounds like you might be experiencing storage shortages which are common in large SAP sites. There have been a large number of DB2 fixes for storage issues in the last 18 months or so, so make sure you're up to date on maintenance. There is an apar out there that documents all storage fixes (don't know the number - sorry!). Similarly there is an SAP OSS note that documents storage problems on DB2. Also investigate using the CONSTOR ZPARM - this will help reduce your storage requirements. [...] 821 50 39_Re: Commiting within a Stored Procedure14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Thu, 1 Jun 2000 09:24:59 -0400613_iso-8859-1 Raymond:

You cannot COMMIT from within a stored procedure, period.

Manas.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Raymond Bell > Sent: Thursday, June 01, 2000 12:28 AM > To: DB2-L@RYCI.COM > Subject: Commiting within a Stored Procedure > > > Colleagues, > > A quick question: I believe it's not possible to issue commit statements > from within a stored procedure if the process calling the procedure is a > CICS transaction. Is this true? > > DB2 for OS/390 V5 (about 9910) > CICS 4.1 > OS/390 2.6 > > Cheers, [...] 872 69 39_Re: Commiting within a Stored Procedure19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US28_Thu, 1 Jun 2000 09:26:42 EDT338_us-ascii Hi,

I may be wrong on this but I think I heard somewhere that some sneaky programmers issue a commit in a stored procedure anyway. This results in a stored procedure related error which is returned to the client program. The client program then issues a commit.

If it works, it is certainly clever and sneaky! [...] 942 95 57_Re: ongoing tracking of DB2 health - what do you measure?9_Jim Leask27_jim.leask@RS-COMPONENTS.COM30_Thu, 1 Jun 2000 14:33:32 +0100381_iso-8859-1 Hi Larry,

Funny enough I have just started running a monthly report of Key Performance Indicators for DB2. The results for last month are copied in below (we have approximately 800,000 CICS transactions per day).

The systems team also produce stats showing what %age of CICS transactions are less than 1 second for each CICS region and other reports. [...] 1038 113 19_Re: DB2-SAP problem23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Thu, 1 Jun 2000 14:40:15 +0100270_iso-8859-1 ...re EDMPOOL .. there is an optimal value for this that I have found returns maximum bang for the buck. i.e there is a point at which, in my opinion, you get more payback by using the pages in the VPs rather than in the Global Cache within the EDMPOOL. [...] 1152 68 39_Re: Commiting within a Stored Procedure14_Philip Gunning20_pgunning@BOSCOVS.COM30_Thu, 1 Jun 2000 09:54:22 -0400390_iso-8859-1 In version 7 that will change. Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS

----- Original Message ----- From: Manas Dasgupta Newsgroups: bit.listserv.db2-l To: Sent: Thursday, June 01, 2000 9:24 AM Subject: Re: Commiting within a Stored Procedure [...] 1221 93 39_Re: Commiting within a Stored Procedure4_IBM116_IBM1@UBIL.COM.TR30_Thu, 1 Jun 2000 16:54:56 +0300702_- but only if you pay for it as an extra.... SORRY COULD NOT RESIST!!

ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it _____________________________________________ No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced.

> -----Original Message----- > From: Philip Gunning [SMTP:pgunning@BOSCOVS.COM] > Sent: Thursday, June 01, 2000 4:54 PM > To: DB2-L@RYCI.COM > Subject: Re: Commiting within a Stored Procedure > > In version 7 that will change. > Philip K. Gunning > DB2 DBA > IBM Certified Advanced Technical Expert - DB2 DRDA > IBM Certified [...] 1315 37 25_Re: DB2 PM Online Monitor23_Lee, Kenneth (Exchange)14_kmlee@BEAR.COM30_Thu, 1 Jun 2000 10:06:31 -0400711_- The cost is THE main factor for considering replacement. The existing vendor suddenly demands a quarter of million dollar per each CPU upgrade which is totally out of whack.

> -----Original Message----- > From: Edward(Ed) J. Finnell, III [SMTP:efinnell@SEEBECK.UA.EDU] > Sent: Wednesday, May 31, 2000 6:19 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2 PM Online Monitor > > Please include decision criteria and if applicable weighting factors. > Edward(Ed) J. Finnell, III > Enterprise Systems/Proj. Mgr. > url:www.ua.edu > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. [...] 1353 24 57_Re: ongoing tracking of DB2 health - what do you measure?0_31_truman.g.brown@BELLATLANTIC.COM30_Thu, 1 Jun 2000 10:09:00 -0400312_us-ascii It's also a good idea to look at EDM pool usage for growth trends and failures, and RID pool for failures due to storage short. If you're using DDF check for queue on DBATS; and if your message rate for DDF seems abnormally high for the amount of activity check the VTAM pag- ing value (we use 8). [...] 1378 87 39_Re: Commiting within a Stored Procedure13_Lynne Flatley17_LFlatley@NEFN.COM30_Thu, 1 Jun 2000 10:39:33 -0400600_us-ascii Actually, that may a ROLLBACK in the stored procedure. The caller of the stored procedure must then issue a ROLLBACK before any further SQL statements will be processed. This is a nice architectural feature in V5 that is going away in V6 (V7?)...

> -----Original Message----- > From: Rajeev P. Dhanawade [SMTP:rdhanawade@STATE.DE.US] > Sent: Thursday, June 01, 2000 9:27 AM > To: DB2-L@RYCI.COM > Subject: Re: Commiting within a Stored Procedure > > Hi, > > I may be wrong on this but I think I heard somewhere that some sneaky > programmers issue a commit in a stored procedure [...] 1466 28 24_Extracting data for test10_Feeny, Jim23_Jim_Feeny@COMPUWARE.COM30_Thu, 1 Jun 2000 10:41:42 -0400377_iso-8859-1 Hello Peter:

File-AID/RDX from Compuware extracts DB2 data related by DB2 RI or by application relationships. This product can extract related and unrelated data from DB2 tables as well as VSAM, sequential and IMS data.

It is also tightly integrated with File-AID for DB2 enabling extracted data to be easily customized to add special test cases. [...] 1495 34 4_-44415_Peter C. Sexton18_psexton@US.IBM.COM30_Thu, 1 Jun 2000 08:38:58 -0600367_us-ascii If everything else checks out ok, one thing that is sometimes overlooked is the loadlib dataset(s). If you're running out of STEPLIB in the WLM started address space, then make sure you don't have a mix of APF authorized and non-APF authorized loadlibs concatenated together. This is not supported.

Pete Sexton DB2 Technical Specialist IBM Corp. [...] 1530 25 58_DB2 OS/390 Stored Procedures and the -DISPLAY PROC command13_Lynne Flatley17_LFlatley@NEFN.COM30_Thu, 1 Jun 2000 11:00:02 -0400406_us-ascii What are other folks doing to enable their beleaguered stored procedure developers to be able to issue a -DISPLAY PROC command, given that granting the developers SYSOPR auth is unacceptable?

This is DB2 V5, of course...

"A foolish consistency is the hobgoblin of small minds" - Ralph Waldo Emerson

Lynne A. Flatley New England Financial (617) 578-4079 lflatley@nefn.com [...] 1556 31 7_Re: BPT14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 1 Jun 2000 11:05:38 -0400410_ISO-8859-1 Sure.. Regards, Joel

Message text written by DB2 Data Base Discussion List >yes I agree .. but this does sounds like a classic storage creep problem of the SAP world .. well everyones world but more evident in SAP due to Dynamic nature....

whilst writing I am about to light the engines of the BP tool again in our DS environment .. can I come direct to you if I have any issues? [...] 1588 50 62_Re: DB2 OS/390 Stored Procedures and the -DISPLAY PROC command13_Smith, Joseph18_Smithmq@RBOS.CO.UK30_Thu, 1 Jun 2000 16:11:39 -0000598_- Can you not grant the Display priviledge?

> -----Original Message----- > From: Lynne Flatley [SMTP:LFlatley@NEFN.COM] > Sent: 01 June 2000 15:00 > To: DB2-L@RYCI.COM > Subject: DB2 OS/390 Stored Procedures and the -DISPLAY PROC command > > > *** Warning : This message originates from the Internet *** > > What are other folks doing to enable their beleaguered stored procedure > developers to be able to issue a -DISPLAY PROC command, given that > granting > the developers SYSOPR auth is unacceptable? > > This is DB2 V5, of course... > > "A foolish consistency is the hobgoblin of [...] 1639 22 39_Re: Commiting within a Stored Procedure17_Massimo Biancucci19_mbiancucci@DEBIS.IT30_Thu, 1 Jun 2000 09:54:26 -0500423_- As well as the standard routines, the stored procedures invoked under CICS, IMS or similar can't invoke the sql "commit" verb because the UOW owner is the TP-monitor (sqlcode -925 for commit and -926 for rollback).

Finally a personal idea: routines shouldn't invoke commit or rollback because only the main program really has the knowledge of the "process" and then only it can invoke "commit" or "rollback". [...] 1662 75 62_Re: DB2 OS/390 Stored Procedures and the -DISPLAY PROC command13_Lynne Flatley17_LFlatley@NEFN.COM30_Thu, 1 Jun 2000 11:43:39 -0400602_us-ascii Actually, yes, you can.

Is it too late to recall this message ;-) ?

> -----Original Message----- > From: Smith, Joseph [SMTP:Smithmq@RBOS.CO.UK] > Sent: Thursday, June 01, 2000 12:12 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2 OS/390 Stored Procedures and the -DISPLAY PROC > command > > Can you not grant the Display priviledge? > > > -----Original Message----- > > From: Lynne Flatley [SMTP:LFlatley@NEFN.COM] > > Sent: 01 June 2000 15:00 > > To: DB2-L@RYCI.COM > > Subject: DB2 OS/390 Stored Procedures and the -DISPLAY PROC command > > > > > > *** Warning : This message [...] 1738 16 12_msg DSNR044E10_Tom Taylor17_ttaylor@CHUBB.COM30_Thu, 1 Jun 2000 14:18:30 -0400319_us-ascii hi all

does anyone know what message DSNR044E is ?

Tom

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 1755 49 16_Re: msg DSNR044E0_17_sjvagnier@AEP.COM30_Thu, 1 Jun 2000 14:48:58 -0400803_us-ascii Hello Tom,

see APAR's II11442 and PQ34199.

Hope this helps...

Steve Vagnier American Electric Power Sr. Systems Administrator











Tom Taylor @RYCI.COM> on 06/01/2000 02:18:30 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: msg DSNR044E



hi all

does anyone know what message DSNR044E is ?

Tom

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. [...] 1805 254 26_Re: Hiperpool in DSNDB07 ?12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Thu, 1 Jun 2000 21:23:03 +0200557_x-user-defined Hi,

Per LPAR. The current level of OS/390 (aka MVS) can address that much only, it is being told that very soon we'll hear about a change in that as well. CMOS memory is configurable although not everything can be changed dynamically by console commands. Some changes can only be done with "power on reset" - means shutting down the whole machine, configure and then start up. Any way, currently if you have a machine with one LPAR and 4 GB then you can configure it as 2 GB central max. and 2 GB expanded. You may decide to make [...] 2060 36 18_bit.listserv.db2-l10_Leo Flores23_leoflores@EARTHLINK.NET30_Thu, 1 Jun 2000 11:55:00 -0700612_us-ascii Hello

I would like to set my DB2-L profile to NOMAIL and access the list via bit.listserv.db2-l.

My ISP is earthlink.net and when I subscribed to bit.listserv.db2-l there were just a few messages. I expected to see all of the DB2-L message I receive here at my PC. Today after I downloaded all of the new messages there are a total of 47 messages ranging from 5/3/00 to 5/29/00. The name of the discussion list is bit.listserv.db2-l and is on server news.earthlink.net. I also subscribed to comp.database.ibm-db2 on this server and it has 1008 messages ranging from 5/4/00 to 6/1/00. [...] 2097 335 26_Re: Hiperpool in DSNDB07 ?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Thu, 1 Jun 2000 15:08:51 -0400647_iso-8859-1 Thanks.

-----Original Message----- From: Isaac Yassin [SMTP:yassin@NETVISION.NET.IL] Sent: Thursday, June 01, 2000 3:23 PM To: DB2-L@RYCI.COM Subject: Re: Hiperpool in DSNDB07 ?

Hi,

Per LPAR. The current level of OS/390 (aka MVS) can address that much only, it is being told that very soon we'll hear about a change in that as well. CMOS memory is configurable although not everything can be changed dynamically by console commands. Some changes can only be done with "power on reset" - means shutting down the whole machine, configure and then start up. Any way, currently if you have a machine with one LPAR [...] 2433 79 38_Re: WLM stored procedures -444 SQLCODE12_Kirk Hampton16_khampto1@TXU.COM30_Thu, 1 Jun 2000 14:27:23 -0500627_us-ascii We have all the same kinds of datasets listed by Joe Smith, below, concatenated in STEPLIB in our WLM-managed stored procedure address spaces, plus our SDSNLOAD dataset, and it works just fine for us. The only thing "unsupported" I am aware of as far as mixing APF-authorized and non-APF-authorized libs in a concatenation is that the whole concatenation becomes non-APF-authorized. Since we must override the the LNKLST copy of SDSNLOAD in the test environment, (LNKLST is still v510 for production, test env. is V610) we did not have a choice, since we declined to APF-authorize a loadlib that developers could [...] 2513 111 25_Re: DB2 PM Online Monitor12_Kirk Hampton16_khampto1@TXU.COM30_Thu, 1 Jun 2000 14:53:44 -0500554_us-ascii We have provided DB2PM for our DBA's and developers to use for almost as long as we have had DB2 in the shop. Until about 2 years ago, we also had Candle's OMEGAMON for DB2, which only us Systems Programmers used, and we also had OMEGAMON for every other Systems function (IMS, VTAM, SMS, MVS, etc.) I found OMEGAMON to be much faster than DB2PM in helping me locate the source of a system problem, both in response time and in ease of navigation. OMEGAMON was never made available to users outside of the systems area, because it was able [...] 2625 15 26_Runstats with online reorg20_Johnson, Bill (GLIC)18_bjo23@ALLSTATE.COM30_Thu, 1 Jun 2000 14:30:17 -0500342_- All, Can runstats run concurrently with online reorg? TIA, Bill Johnson DBA Allstate Insurance Co.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2641 70 22_Re: bit.listserv.db2-l22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Thu, 1 Jun 2000 16:18:35 -0400426_- I've noticed the same small # of entries on bit.listserv.db2-l but I never pursued the discrepancy. I'm curious also as to why there is a difference in the # of entries between bit.listserv.db2-l and db2-l email.

I subscribe to it from my personal ISP (Compuserve) and yes the problem existed before AOL purchased Compuserve :-). I can't remember the Compuserve news server name... maybe is news.compuserve.com? [...] 2712 151 56_Re: DB2-L Digest - 31 May 2000 to 1 Jun 2000 (#2000-160)21_Smith, Ronald M - CNF20_smith.ronald@CNF.COM30_Thu, 1 Jun 2000 13:19:08 -0700548_iso-8859-1 Hi John,

In DB2 Messages & Codes under DSN9022I you find: DSN9022I csect-name verb-name pkw-name NORMAL COMPLETION Explanation: All synchronous processing for the command specified in the message as 'verb-name pkw-name' completed successfully. Any tasks executing asynchronously for the specified command might still be executing when this message is displayed. System Action: Synchronous processing for the specified command is complete. Subsequent system actions defined by the command are documented in Command Reference. [...] 2864 35 30_Re: Runstats with online reorg14_McIntosh, John27_John.McIntosh@NORDSTROM.COM30_Thu, 1 Jun 2000 14:22:37 -0600482_iso-8859-1 Not against the same target object. See "Table 61. REORG TABLESPACE compatibility" in the Utilities Guide.

John McIntosh Database Administrator Nordstrom



-----Original Message----- From: Johnson, Bill (GLIC) [mailto:bjo23@ALLSTATE.COM] Sent: Thursday, June 01, 2000 12:30 PM To: DB2-L@RYCI.COM Subject: Runstats with online reorg



All, Can runstats run concurrently with online reorg? TIA, Bill Johnson DBA Allstate Insurance Co. [...] 2900 80 30_Re: Runstats with online reorg0_19_Tim.Lowe@STPAUL.COM30_Thu, 1 Jun 2000 15:37:01 -0500542_us-ascii Bill, With DB2 version 6, your online reorg could use the new STATISTICS option to collect inline statistics, updating the catalog tables during a reorganization. Is this what you are asking?

Thanks, Tim













"McIntosh, John" on 06/01/2000 03:22:37 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: Tim Lowe/sfm/spc) [...] 2981 100 22_Re: bit.listserv.db2-l13_Thomas, Janis19_Janis.Thomas@CA.COM30_Thu, 1 Jun 2000 16:58:26 -0400434_iso-8859-1 Postings to the DB2 listserv are not posted to newsgroup bit.listserv.db2-l. They were for a while in the past when DB2-L was hosted at American University - that's the way they had it set up, but it still did not work 100%. It is rare for a listserv to mirror postings to a 'bit.listserv' newsgroup, and for it to actually work 100%. Usually, a discussion list is one or the other - either a listserv or a newsgroup. [...] 3082 131 22_Re: bit.listserv.db2-l22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Thu, 1 Jun 2000 17:10:09 -0400660_- Janis -

Thanks for the clarification.



Mike Piontkowski Voice/Fax: 302.886.4612 mailto:michael.piontkowski@astrazeneca.com

> ---------- > From: Thomas, Janis[SMTP:Janis.Thomas@CA.COM] > Sent: Thursday, June 01, 2000 4:58 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] bit.listserv.db2-l > > Postings to the DB2 listserv are not posted to newsgroup > bit.listserv.db2-l. > They were for a while in the past when DB2-L was hosted at American > University - that's the way they had it set up, but it still did not work > 100%. It is rare for a listserv to mirror postings to a 'bit.listserv' > newsgroup, and for it to actually [...] 3214 17 17_Compilation error15_Raghu Tarigonda29_rtarigo@NOTES.MNET.USWEST.COM30_Thu, 1 Jun 2000 17:05:12 -0600492_us-ascii Hi, As i try to insert a new row i got an error "statement inserts worng number of values" during precompilation. I checked the number of host variables and db2 variables .It's the same in every aspect. I appreciate your views.

Raghu.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3232 34 33_Who controls DB2 authorizations ?12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Fri, 2 Jun 2000 09:53:53 +1200564_us-ascii Thomas,

At the site I'm at now, the DBAs control all access to DB2 data. The Security (i.e. RACF) Administrators look after everything else. They set up the groups that we want and connect people, while we grant those groups whatever DB2 privileges are required.

Educating SAs in the weird and wonderful ways of DB2 security is a daunting task. I've been a DB2 DBA for 12 years now, and every now and then I still get caught out by some obscure way DB2 authorises something. That's not to say it can't - or even shouldn't - be done. If [...] 3267 140 22_Re: bit.listserv.db2-l10_Leo Flores23_leoflores@EARTHLINK.NET30_Thu, 1 Jun 2000 16:25:05 -0700617_us-ascii Thank you Mike and Janis. I was afraid this might be the case. I was hoping I was doing something wrong so that I coild correct it and get all of the DB2-L messages, shucks.

Leo Flores

Piontkowski Michael ML wrote:

> Janis - > > Thanks for the clarification. > > Mike Piontkowski > Voice/Fax: 302.886.4612 > mailto:michael.piontkowski@astrazeneca.com > > > ---------- > > From: Thomas, Janis[SMTP:Janis.Thomas@CA.COM] > > Sent: Thursday, June 01, 2000 4:58 PM > > To: DB2-L@RYCI.COM > > Subject: Re: [DB2-L] bit.listserv.db2-l > > > > Postings to the DB2 listserv are not posted to [...] 3408 16 28_Help Update TimeStamp Column16_Christine Sawyer20_cosawyer@HOTMAIL.COM28_Thu, 1 Jun 2000 19:54:25 EDT 3425 63 32_Re: Help Update TimeStamp Column13_Neil Courtney19_NCOURTN@CLEAR.CO.NZ30_Fri, 2 Jun 2000 12:06:48 +1200376_iso-8859-1 Hi Christine, the date part of your string must be "0001-01-01", as this is the minimum date that DB2 will allow. Not sure how you would handle 1 BC.

Cheers, Neil Courtney Senior IS Specialist, SD Core Systems " Into a crystal cup the dusky wine, I pour , and musing at so rich a shrinem, I watch the star that haunts its ruddy gloom. " George Sterling [...] 3489 38 32_Re: Help Update TimeStamp Column13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Thu, 1 Jun 2000 20:33:40 -0400501_iso-8859-1 Hello Christine and Neil,

As the SQL reference indicates you can only store valid dates in the range of 1 January 0001 through 31 December 9999.

" Table 52. Datetime limits ------------------------------------------------------- Item Limit ------------------------------------------------------- Smallest DATE value (shown in ISO format) 0001-01-01 ------------------------------------------------------- Largest DATE value (shown in ISO format) 9999-12-31 [...] 3528 54 39_Re: Commiting within a Stored Procedure14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Thu, 1 Jun 2000 20:45:38 -0400713_iso-8859-1 Just a note. In Version 7, all, COMMIT CAN be issued in stored procedures, etc.

Hope this helps, Richard Yevich +======+======+======+ Richard_Yevich@attglobal.net DB2 Performance Journal, Quarterly www.YLAssoc.com -- www.DB2-Portal.com





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Massimo Biancucci > Sent: Thursday, June 01, 2000 10:54 AM > To: DB2-L@RYCI.COM > Subject: Re: Commiting within a Stored Procedure > > > As well as the standard routines, the stored procedures invoked under > CICS, IMS or similar can't invoke the sql "commit" verb because the UOW > owner is the TP-monitor (sqlcode -925 for [...] 3583 43 34_how to run batch on UDB 6.1 on NT416_Eric Ng Yew Heng22_ngyh@PUBLICBANK.COM.MY30_Fri, 2 Jun 2000 10:53:38 +0800404_us-ascii Hi, we have an Lotus Notes application integrating with UDB 6 on NT4 using ODBC. We are starting the user acceptance test now.

We have Microfocus Cobol. We do not have C compiler. We do not have ADSM. We do not have third-party batch scheduler. And the mgt has no intention to buy them either. If we are to get the operators to run the batch jobs at night, what shd be the best way? [...] 3627 60 37_Links about DB2 for OS/390 (06/02/00)6_DB2usa18_db2usa@HOTMAIL.COM30_Thu, 1 Jun 2000 22:55:22 -0500636_ISO-8859-1 Hi,

Here is a non-profit website about DB2 for OS/390 (IBM mainframes):

- English : http://www.multimania.com/db2usa/eliendb2.htm - Français: http://www.multimania.com/db2usa/liendb2.htm

Last update on Friday, June 2nd 2000 ====================================

- Links about DB2 for OS/390: http://www.multimania.com/db2usa/eliste.htm Books (Bookmanager format) of last refresh of DB2 UDB for OS/390, Version 6 are available on IBM's website.

Wintercorp launches the Database Scalability Program 2000. Take a look at previous winners of the world's largest databases in 1997 and 1998. [...] 3688 21 22_DB2 UDB clarifications25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG28_Thu, 1 Jun 2000 10:25:57 GMT440_us-ascii Hi,

Some doubts about DB2 UDB for HP UX. Please help.

1. Is there a online backup for DB2 UDB. 2. For Auditing: a. Can we audit for authorised access to tables.

Regards

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3710 159 28_Problem with DB2 Replication11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID30_Fri, 2 Jun 2000 12:33:07 +0700586_iso-8859-1 Hello... We are trying to implement DB2 Replication on Windows NT by using the Update Anywhere scenario. But we encounter a big problem. The configuration is like this : 7 Remote DB2 Wokgroup Server (WinNT) trying to replicate to 1 Corporate DB2 Enterprise Server (WinNT).

As the Redbook said, the Capture program has to be run on every Server (in this case 8 places), and the APPLY program can be run on any Server. But, everytime we try to start the Apply program on every Server together, the Capture program on the Central DB2 server will exit to the prompt. [...] 3870 20 32_Re: Problem with DB2 Replication11_Doug Fuerst14_djfirst@LI.NET30_Fri, 2 Jun 2000 02:20:02 -0400449_us-ascii This has a virus.



At 12:33 02-06-00 +0700, you wrote: >This file was infected with a virus. The file was quarantined by Norton >AntiVirus. Friday, June 02, 2000 02:

Doug Fuerst

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3891 21 11_IFCID - 12613_Mayank Sharma33_Mayank.Sharma@POWAIMAIL.LTITL.COM30_Fri, 2 Jun 2000 11:50:18 +0530518_us-ascii Hi all

I am trying to read log records thrue IFCID 126. but it reflects nothing into buffer. Can anybody guide me to read log records of redo records.

Thanks in advance.

Mayank Sharma L & T Information Technology Limited, Powai Ph. 8581940 - 8035

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3913 22 17_Clob problem.....16_Arunava Majumdar36_Arunava.Majumdar@VASHIMAIL.LTITL.COM30_Fri, 2 Jun 2000 13:25:30 +0530299_us-ascii Hi,

I'm finding problem in passing a clob from my C++ program to an SP also writen in C++. I actually have no clue on how to send it by creating the SQLDA. I had tried a couple of options but not been successful. I require this urgently.

Thanks in advance, Regards, Ron. [...] 3936 62 32_Re: Help Update TimeStamp Column4_IBM116_IBM1@UBIL.COM.TR30_Fri, 2 Jun 2000 11:39:13 +0300460_- christine,

the date you insert must be a valid date, i.e. comprised between Jan. 1 0001 and Dec. 31 9999. see the SQL Ref. for more details, if needed.

ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it _____________________________________________ No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced. [...] 3999 80 21_Re: Clob problem.....14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 2 Jun 2000 19:00:30 +1000600_iso-8859-1 I am responding due to your urgency - not my competence.

If you have created a clob host variable "SQL TYPE IS CLOB(size)) clob_var", I think you can set up your SQLDA entry: - if you are passing m parameters, the SQLDA will need 2*m entries - this is the n+1'th entry - strcpy(daptr->sqlaid, "SQLDA 2 "; sqlda.sqln = 2 * m; daptr->sqltype[n] = 408; -- a non-null clob daptr->sqllen[n] = 0; -- always for a clob daptr->sqldata[n] = *clob_var; daptr->sqlind[n] = NULL; -- or, if null, the address of the indiactor variable daptr->sqlname.length[n] = 0; -- no CCSID translation. [...] 4080 30 56_US Tech Conference CD from last year - DS papers anybody23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Fri, 2 Jun 2000 11:38:01 +0100700_iso-8859-1 Anybody have a copy of the Tech Conference CD from last year? I would like to get my hands on a copy of the Datasharing Tuning paper(s) that are on it?

Would anybody be able to give these to me? ????

Many thanks

Leslie Pendlebury-Bowe



********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Sony cannot accept liability for statements made which are clearly the sender's own and not made on behalf of Sony. (01) ********************************************************************** [...] 4111 57 23_Bad Indicator Variable?13_Lockwood Lyon23_Lockwood_Lyon@AMWAY.COM30_Fri, 2 Jun 2000 06:48:06 -0400527_us-ascii One of our programmers is getting this weird SQL error when attempting to pre-compile a program. We would appreciate anyone's assistance.

The SQL Statement in question is:

SELECT BUS_ENTTY_ID, BUS_ENTTY_TY, EDI_TP_ID, EDI_COORD_ID, ROW_UPDT_USER_ID, ROW_UPDT_DTM, TPNICKNM INTO :BUS-ENTTY-ID , :BUS-ENTTY-TY, :EDI-TP-ID, :EDI-COORD-ID, :ROW-UPDT-USER-ID, :ROW-UPDT-DTM, :TPNICKNM:TPNICKNM-IND FROM BUS_EDI WHERE BUS_ENTTY_ID=:BUS-ENTTY-ID AND BUS_ENTTY_TY=:BUS-ENTTY-TY

The Error Message is: [...] 4169 87 27_Re: Bad Indicator Variable?4_IBM116_IBM1@UBIL.COM.TR30_Fri, 2 Jun 2000 14:14:18 +0300738_- if this is a cut and paste job, then your problem may be you missed a blank between :TPNICKNM:TPNICKNM-IND.

ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it _____________________________________________ No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced.

> -----Original Message----- > From: Lockwood Lyon [SMTP:Lockwood_Lyon@AMWAY.COM] > Sent: Friday, June 02, 2000 1:48 PM > To: DB2-L@RYCI.COM > Subject: Bad Indicator Variable? > > One of our programmers is getting this weird SQL error when attempting to > pre-compile a program. We would appreciate anyone's assistance. > [...] 4257 38 21_Re: Compilation error17_Linda F. Claussen18_lindafc@NETINS.NET30_Fri, 2 Jun 2000 06:19:46 -0500581_ISO-8859-1 Without seeing a copy of the statement it would be strictly guess work. I would suggest you repost with a copy of the statement pasted into the message.

Cheers, Linda

---------- From: Raghu Tarigonda To: DB2-L@RYCI.COM Subject: Compilation error Date: Thursday, June 01, 2000 6:05 PM

Hi, As i try to insert a new row i got an error "statement inserts worng number of values" during precompilation. I checked the number of host variables and db2 variables .It's the same in every aspect. I appreciate your views. [...] 4296 26 14_DB2 UDB on w2k14_Rajesh Shewani22_rajeshs@DOMINOMAIL.COM28_Fri, 2 Jun 2000 11:18:05 GMT388_iso-8859-1 Hi all,

Has any one tried to install DB2 UDB 6.1 on win 2000 Adv server.

I've been facing a few problems, The installation goes through but the control center nor the information center comes up. All the services are running perfectly.

Regards, Rajesh Shewani DB2 Tech.Specialist

Free Web Mail for Notes Professionals http://www.dominomail.com [...] 4323 60 38_Re: how to run batch on UDB 6.1 on NT410_Juan Lanus21_jlanus@INTERAR.COM.AR30_Fri, 2 Jun 2000 08:28:38 -0300441_us-ascii Eric: NT has an "at" command to start a job at a predefined point in s time. Jobs are run by a "service" named "scheduler". The user and pw are entered in the dialog where you set the start parameters for that service, so they are not in clear text in the job. Anyway, somebody could schedule a job to be run at some other time on behalf of the authorized schedule owner. With the "at" command you tipically run a .BAT file. Regards 4384 30 27_Re: Bad Indicator Variable?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Fri, 2 Jun 2000 07:43:07 -0400351_iso-8859-1 Good morning Lockwood,

Out of curiosity, I ran a quick test defining 2 null-indicator variables. I defined one as you did with the Comp on the Group level and another with COMP on the elementary item. I used them in identical Select statements and the pre-compiler only complained about the variable defined implicitly as Comp. [...] 4415 129 21_Re: Clob problem.....16_Arunava Majumdar36_Arunava.Majumdar@VASHIMAIL.LTITL.COM30_Fri, 2 Jun 2000 17:23:04 +0530333_us-ascii Hi James, Thanks for your reply. I'm still breaking my head over how to pass the LOB locator. I get the data from SAP through MQ series and not from the table. So somehow I have to transfer it to some host variable before I can do any DB operation on it. I'm having the following code : Correct me if I'm wrong........ [...] 4545 81 46_Re: Problem with DB2 Replication - VIRUS ALERT12_Troy Coleman29_troycci@COLEMANCONSULTING.COM30_Fri, 2 Jun 2000 07:15:17 -0500488_iso-8859-1 I received a notice from Norton that this email had an attachment with a virus.

Troy Coleman IBM Certified Solutions Expert - DB2 UDB V5 Database Administration Voice: 847-705-1468 Email: troycci@colemanconsulting.com AOL IM: troycci MSN IM: troycci

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Doug Fuerst Sent: Friday, June 02, 2000 1:20 AM To: DB2-L@RYCI.COM Subject: Re: Problem with DB2 Replication [...] 4627 122 27_Re: Bad Indicator Variable?12_Troy Coleman29_troycci@COLEMANCONSULTING.COM30_Fri, 2 Jun 2000 07:15:25 -0500382_iso-8859-1 I would go with your first guess and that is put the COMP on the elementary item. I've never put the usage on the 01 level, and I know that putting the usage on the elementary level works.

Troy Coleman IBM Certified Solutions Expert - DB2 UDB V5 Database Administration Voice: 847-705-1468 Email: troycci@colemanconsulting.com AOL IM: troycci MSN IM: troycci [...] 4750 80 27_Re: Bad Indicator Variable?17_Vijay Subramanyam33_vijay.subramanyam@HPSBLR.SOFT.NET30_Fri, 2 Jun 2000 17:57:46 +0500665_- Hello, It is not possible to USAGE on the Group-item. Always the group-item is alphanumeric. Declare COMP on the elementary items & it should work fine.

Vijay



> -----Original Message----- > From: Lockwood Lyon [SMTP:Lockwood_Lyon@AMWAY.COM] > Sent: Friday, June 02, 2000 4:18 PM > To: DB2-L@RYCI.COM > Subject: Bad Indicator Variable? > > One of our programmers is getting this weird SQL error when attempting to > pre-compile a program. We would appreciate anyone's assistance. > > The SQL Statement in question is: > > SELECT > BUS_ENTTY_ID, > BUS_ENTTY_TY, > EDI_TP_ID, > EDI_COORD_ID, > ROW_UPDT_USER_ID, > ROW_UPDT_DTM, > TPNICKNM [...] 4831 25 26_DB2 Row Length/Performance14_Chris Michaels35_Christopher_A_Michaels@VANGUARD.COM30_Fri, 2 Jun 2000 08:22:54 -0400532_us-ascii Using DB2 V6 on OS/390: I'm in the process of designing a new database. One of the tables has a unusually large record length (for us), and was wondering if there are any application performance considerations. Right now I'm estimating 154 rows with a total record length of 1,492 K, so I'll get two rows on one 4K page. The IBM Admin guide doesn't say much other than a row just over 1/2 a page is a waste of space, because only one row fits on a page and the rest is wasted. Oh, also I'm estimating the total number [...] 4857 97 27_Re: Bad Indicator Variable?4_IBM116_IBM1@UBIL.COM.TR30_Fri, 2 Jun 2000 15:38:25 +0300421_- by the way, not only does there seem to be a blank missing, also you cannot declare a level 01 group-item with a USAGE mode.

the correct declaration should be:

01 NULL-INDICATORS. 10 ROW-UPDT-USER-ID-IND PIC S9(4). 10 ROW-UPDT-DTM-IND PIC S9(4). 10 TPNICKNM-IND PIC S9(4).

then if you need to use NULL-INDICATORS as comp, declare another level 01 variable COMP .... REDEFINES NULL-INDICATORS. [...] 4955 55 30_Re: DB2 Row Length/Performance4_IBM116_IBM1@UBIL.COM.TR30_Fri, 2 Jun 2000 15:39:08 +0300723_- what about even larger pages?

ciao!

Aurora Dell'Anno Systems Engineer IBM Software Support e-mail: ibm1@ubil.com.tr aurora.dellanno@sistinf.it _____________________________________________ No trees were killed in the sending of this message. However - a large number of electrons were terribly inconvenienced.

> -----Original Message----- > From: Chris Michaels [SMTP:Christopher_A_Michaels@VANGUARD.COM] > Sent: Friday, June 02, 2000 3:23 PM > To: DB2-L@RYCI.COM > Subject: DB2 Row Length/Performance > > Using DB2 V6 on OS/390: > I'm in the process of designing a new database. One of the tables has a > unusually large record length (for us), and was wondering if there are any > application [...] 5011 54 30_Re: DB2 Row Length/Performance19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Fri, 2 Jun 2000 07:50:35 -0500575_ISO-8859-1 Hi Chris, Actually, the math works out that a 8K page would be best. However, if 8K pages are handled similar to the way 32K pages were fetched in V5 and down, I'd have second thoughts.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at [...] 5066 59 32_Re: Problem with DB2 Replication18_Yosia Widi Nugroho20_yosia@CENTRIN.NET.ID30_Fri, 2 Jun 2000 19:43:00 +0700629_- Dear all,

We have a problem with DB2 Replication. Below is the report written by our staff.

I. Background

We are doing a project in Indonesian Army. We use 14 servers. One server for Head Quarter (HQ) and others for branches. We use update-anywhere replication since both side could update their data. Source of Replication reside in HQ and target of replication reside in each branches. Each branch's servers will get only their data (we use filtering by area_code using the where-clause in the subscribtion). For communication beetwen server we use dialup modem (PSTN). We use DB2 Enterprise 5.2 in [...] 5126 88 38_Re: Bad Indicator Variable? -- Solved!13_Lockwood Lyon23_Lockwood_Lyon@AMWAY.COM30_Fri, 2 Jun 2000 09:09:21 -0400342_us-ascii My thanks to everyone who replied. (Actually, I'm quite surprised at the number of replies I got.)

Below is a cut/paste of a recent note from our developer. Adding USAGE COMP to the elementary items (i.e., the Indicator Variables themselves rather than the Group) seems to have resolved the problem.

Thanks again! [...] 5215 58 30_Re: DB2 Row Length/Performance14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Fri, 2 Jun 2000 09:17:26 -0400325_iso-8859-1 Chris:

Is using compression an option?

If it is then, if you get about 45-50% compression (which will depend on your data) , you'll be able to fit 5 pages on a 4K page. And that should bring your 5million row table size down from ~10gig (2.5 million pages * 4K) to ~4gig (1 million pages * 4K). [...] 5274 16 23_DB2 Row Archive Manager11_Jim Medlock24_jmedlock@ALLIANCESYS.COM30_Fri, 2 Jun 2000 08:25:04 -0600 5291 46 15_Re: IFCID - 12620_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 2 Jun 2000 09:26:37 -0400331_us-ascii Mayank

In order to figure out your problem, Can you provide more detail on the following -

1. Is your IFCA populated properly 2. Is your ECB address is correctly specified in BUFFER AREA 3. Have you put number of bytes properly in BUFFER AREA 4. Are you setting first two bits B'00' for ECB before WAIT [...] 5338 28 51_sortdata with unload external in the reorg utility?14_Backes,Michael26_BackeM@MAIL.OA.STATE.MO.US30_Fri, 2 Jun 2000 08:34:13 -0500432_iso-8859-1 This may be in left field but maybe someone has tried this.

when doing a reorg, sortdata, to my understanding says to not unload the data through the clustering index but instead use ts scan. my thought was if this was used in conjunction with the unload external option when unloading an entire table, would this provide a performance increase or not or will it not make a difference, or can you even do it. [...] 5367 22 18_CA and BMC manuals13_kiran haryadi22_db2_question@YAHOO.COM30_Fri, 2 Jun 2000 06:26:16 -0700527_us-ascii greetings,

Can we get CA and BMC manuals in PDF format like IBM manuals? Does anyone have? If so please send it across

Thanks.

__________________________________________________ Do You Yahoo!? Yahoo! Photos -- now, 100 FREE prints! http://photos.yahoo.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5390 42 22_Re: CA and BMC manuals13_Thomas, Janis19_Janis.Thomas@CA.COM30_Fri, 2 Jun 2000 09:46:15 -0400418_iso-8859-1 CA product documentation in PDF format can be found here: http://support.ca.com. Just select the specific product from the drop down list under the 'Support by Product' section. Also within this drop down box, there is a selection for 'DB2 for OS/390' - this will bring you to the general DB2 support page, where all the product doc is listed, instead of going to the individual product support pages. [...] 5433 36 22_Re: CA and BMC manuals18_Bourgoine, Peter A33_Peter.A.Bourgoine@JPFINANCIAL.COM30_Fri, 2 Jun 2000 09:45:43 -0400394_iso-8859-1 Shouldn't you be asking CA and BMC? Are there not copyright issues?

-----Original Message----- From: kiran haryadi [mailto:db2_question@YAHOO.COM] Sent: Friday, June 02, 2000 9:26 AM To: DB2-L@RYCI.COM Subject: CA and BMC manuals



greetings,

Can we get CA and BMC manuals in PDF format like IBM manuals? Does anyone have? If so please send it across [...] 5470 19 40_single byte to double byte character set10_Tom Taylor17_ttaylor@CHUBB.COM30_Fri, 2 Jun 2000 09:58:58 -0400437_us-ascii Hi all

Has anyone changed from mixed data = no to mixed data=yes inorder to include Double Byte Character set?

what does MCCSID=65534 relate to in the code pages

Tom

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5490 45 22_Re: CA and BMC manuals15_Murley, Michael22_Michael_Murley@BMC.COM30_Fri, 2 Jun 2000 08:51:20 -0500344_ISO-8859-1 BMC manuals are available in PDF format at our web site at http://www.bmc.com in the product support section. If you don't have a logon id and password to access the support section, you can register for one there.

You can order hard-copy documentation or CDs from the web site as well, or you can contact you sales rep. [...] 5536 55 55_Re: sortdata with unload external in the reorg utility?12_Claude Birtz24_Claude.Birtz@CIE.ETAT.LU30_Fri, 2 Jun 2000 15:58:59 +0200597_us-ascii REORG without SORTDATA is approximately 10 % faster.

Measurements on a (compressed) tablespace with 3520843 rows were:

625,6 seconds CPU and 952 seconds elapsed with SORTDATA and 567,8 seconds CPU and 710 seconds elpased without SORTDATA.

I encountered one little problem during my tests which was known to the group: if you REORG TABLESPACE XXXXDB.XXXXS02 LOG NO UNLOAD EXTERNAL PART n (i.e.without SORTDATA), you will get all the rows and not just those rows in partition n. As far as our support told me back then the problem was solved in V6, but not in V5. [...] 5592 21 30_Re: DB2 Row Length/Performance11_Joe Bitetto31_joseph.bitetto@US.PWCGLOBAL.COM30_Fri, 2 Jun 2000 08:47:19 -0500344_- I just like to share my past experience with this type of problem. Years back when I was an ADABAS DBA we faced a similar problem and did actual created a larger Block size (Similar to page size). I just wanted to make you aware that in that environment there was a definite performance boost. Less I/O did equate to better performance. [...] 5614 40 11_SR. DB2 DBA14_Patricia Pardo19_ppardo@ICBMCORP.COM30_Fri, 2 Jun 2000 10:31:35 -0400662_us-ascii Hello all,

We are currently seeking a Senior DB2 DBA to fill key position responsible for physical and logical database design and implementation in Upstate New York. This is a Technical Lead/Functional Manager position. The DBA will manage, mentor and lead a team of DB2 DBAs, a mid-level and an entry level and will interface with other key departments. Key Responsibilities: Database Design and Implementation in a DB2 environment. Including monitoring and optimizing database performance and ensuring security and data integrity. The talented individual we are seeking will have at least 5 years experience with DB2 preferably V6 or V5, [...] 5655 24 24_DB2 - SAP problem -again16_Panopoulos Nikos12_npano@NBG.GR30_Fri, 2 Jun 2000 17:54:21 +0300466_- Hello List,

Thanks for the questions to the previous issue. We are working on it. Unfortunately we are have experienced another problem: When we run heavy jobs or queries against the DB2 SAP database, then the corresponding processes consume almost the whole of CPU power: The utilization of CPU reach at 90-99%. Is this a new problem or is it somehow related with the paging problem we had (The latter problems seems to be solved for the time being)? [...] 5680 46 39_Re: Commiting within a Stored Procedure13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 31 May 2000 23:51:52 -0500343_iso-8859-1 you cannot issue a commit/rollback in any stored procedure, regardless of where it is invoked. They must be issue from the calling program, or set the "Commit_on_return" on in the SYSPROCEDURES. Remember, if you do that, the commit is for the whole thread, and will apply to what updates etc the calling program has issued. gm [...] 5727 78 15_Re: IFCID - 12613_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Fri, 2 Jun 2000 19:03:58 +0530430_us-ascii Hi Venkat.

me too along with mayank !! well we have done all that .. the bufsize and the IFCA is populated fine. even the ecb is getting posted properly . this we tested by starting the same program using different IFCid ... and class.

Any other possiblity?? Vishy

LTITL

powai









"Pillay, Venkat (PCA)" on 02/06/2000 06:56:37 PM [...] 5806 46 21_Re: Compilation error0_22_Rohn.Solecki@MTS.MB.CA30_Fri, 2 Jun 2000 09:48:18 -0500709_us-ascii Are any of the fields nullable? If yes, do you have null indicator flag fields for them?

What version of DB2 and Platform









Raghu Tarigonda @RYCI.COM> on 2000/06/01 06:05:12 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Compilation error



Hi, As i try to insert a new row i got an error "statement inserts worng number of values" during precompilation. I checked the number of host variables and db2 variables .It's the same in every aspect. I appreciate your views. [...] 5853 29 18_Tech conference CD23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Fri, 2 Jun 2000 16:01:33 +0100568_iso-8859-1 guys thanks I now have a copy of the presentation.

Thanks to Venkat and Cindy Andersen.

regards

Leslie



********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Sony cannot accept liability for statements made which are clearly the sender's own and not made on behalf of Sony. (01) ********************************************************************** [...] 5883 106 30_Re: Runstats with online reorg20_Johnson, Bill (GLIC)18_bjo23@ALLSTATE.COM30_Fri, 2 Jun 2000 09:57:06 -0500645_- We did use the STATISTICS option, but still received the following:

<<...>>

Any suggestions? When we remove the STATISTICS option, it completes successfully!

Thanks, Bill Johnson



> -----Original Message----- > From: Tim.Lowe@STPAUL.COM [SMTP:Tim.Lowe@STPAUL.COM] > Sent: Thursday, June 01, 2000 4:37 PM > To: DB2-L@RYCI.COM > Subject: Re: Runstats with online reorg > > Bill, > With DB2 version 6, your online reorg could use the new STATISTICS option > to > collect inline statistics, updating the catalog tables during a > reorganization. > Is this what you are asking? > > Thanks, > Tim > > > > > > [...] 5990 88 28_Re: DB2 - SAP problem -again23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Fri, 2 Jun 2000 16:29:21 +0100370_iso-8859-1 Nikolaos

We need more information ...

What is most of the CPU being attributed back to ?

How have you configured your Virtual Pools .. i.e have you over allocated your Virtual pools ..

what are your HP sizes? (if any)

How many engines do you have - Mipps?

How large is your Database? Gig .. number of objects .. [...] 6079 27 20_ACF2/DB2 Experiences13_Brian Charles20_bcharles@RITEAID.COM30_Fri, 2 Jun 2000 11:50:22 -0400638_iso-8859-1 Does anyone have any experience with CA ACF2/DB2? More specifically..: - Is there an increase in CPU utilization going from DB2 managed security to ACF2 managed? Apparently, this product commandeers plan and package authorization. If ACF2 invokes an exit to check security in a heavy online transaction environment, wouldn't this be a problem? - Has anyone found this loss of control restrictive. i.e. When a drop and recreate is required for a alter, repartition etc, do you have to request that the security department grant the necessary authorizations following the recreate? - Are there real advantages to the DBA or [...] 6107 76 88_Datasharing Very Interesting - How to perform a DR Recovery from one system to a new one0_18_cmunson@US.IBM.COM30_Fri, 2 Jun 2000 10:02:10 -0600402_us-ascii Hi Leslie, Maybe you can help me understand why you are trying to rename the subsystem at the D/R site. Typically we see people doing D/R (you guessed it) in case of a disaster. In a disaster the other system is dead and customers usually want to bring things up as they existed before the disaster (including names). Maybe there is another solution to what you are trying to accomplish. [...] 6184 65 60_Re: US Tech Conference CD from last year - DS papers anybody0_17_sjvagnier@AEP.COM30_Fri, 2 Jun 2000 12:31:36 -0400320_us-ascii Hi Leslie..

I have the Tech Conference CD from last year. I can send you directly an e-mail with a list of the Data Sharing papers on the CD and you could let me know which ones you want. Is that agreeable ???

Steve Vagnier American Electric Power Sr. Systems Administrator sjvagnier@aep.com [...] 6250 59 24_Re: ACF2/DB2 Experiences22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Fri, 2 Jun 2000 12:46:30 -0400580_- 1. Don't know. ACF2/DB2 has been used since I started working at this assignment. 2. No. The one nice thing about external security for DB2 is that authorizations on an object are not lost when an object is modified. Authorizations are setup per DB2 subsystem. 3. From the ACF2/DB2 V1.1 Administrator Guide on page 4-9 "CA-ACF2/DB2 protects everything that native DB2 security protects." ACF2/DB2 supports implicit ownership of a resource. Authorizations are derived from ownership of a resource. One possible negative is delayed support for authorizing new DB2 resources. [...] 6310 43 35_SQL2036N on Load in UDB v6.1 on AIX0_22_BILL_GALLAGHER@PHL.COM30_Fri, 2 Jun 2000 13:42:56 -0400323_us-ascii Hi,

Just a quick SOS to the list about a problem we are encountering while trying to run the load utility in UDB 6.1 (fixpack 2d) on AIX.

I believe that this is probably the first time we are trying to run the load utility in the AIX environment, so maybe we're doing something obviously wrong. [...] 6354 52 34_DB2 unload IBM utility - examples?16_Fields, Janice G19_jfields@INDIANA.EDU30_Fri, 2 Jun 2000 12:36:29 -0500440_iso-8859-1 Hello List: I am having some problems with syntax using the IBM unload utitity DSNTIAUL for partition tables. I am also having problems finding examples in the manuals or on the web. I have a very large DB2 table which has 10 partitions. Even though I got a successful unload using below syntax for first partition, I still received a -104 for illegal symbol and job ended with cond code 8. Any help very much appreciated!! [...] 6407 25 21_Sr. DB2 DBA follow up14_Patricia Pardo19_ppardo@ICBMCORP.COM30_Fri, 2 Jun 2000 13:45:21 -0400591_us-ascii hello again,

I have received couple of requests regarding the location of the opportunity I had emailed earlier. The position is located in Albany, New York. Please contact me if you are interested!

Thanks again!

Patricia Pardo International Computer Business Management Corp. (ICBM) Tel: 813-290-0113 Fax: 813-290-0119

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 6433 80 38_Re: DB2 unload IBM utility - examples?14_Backes,Michael26_BackeM@MAIL.OA.STATE.MO.US30_Fri, 2 Jun 2000 12:57:08 -0500498_iso-8859-1 if you are v5 with a certain ptf(i dont know which), the reorg unload external, will do what dsntiaul does at about 80% less cpu, at least thats what i have found.

your problem is that it is looking for a table name with that list you gave it, and basically what it is saying is that your syntax is wrong and the 001 you have is invalid. you unloaded the whole table with the first line of your syntax, the second line dsntiaul thinks that you are specifying a second table. [...] 6514 122 38_Re: DB2 unload IBM utility - examples?16_Fields, Janice G19_jfields@INDIANA.EDU30_Fri, 2 Jun 2000 13:37:10 -0500401_iso-8859-1 Mike, Thank you very much for your help. Yes-- the whole table was unloaded on that first step. Is there somewhere I can look for samples of DSNUM? I am able to use image copy for all 10 partition but I am having problems unloading each partition into individual tapes using the unload utility. This is for a one time backup, to be kept. Any help very much appreciated!! We are on v5. [...] 6637 92 4_-44415_Peter C. Sexton18_psexton@US.IBM.COM30_Fri, 2 Jun 2000 12:53:36 -0600418_us-ascii My mistake, I was a bit unclear. You're right you can mix APF and non-APF loadlibs in the STEPLIB and the whole thing becomes non-APF. The problem occurs when running DSNLOAD (APF auth) out of LNKLST and a non-APF auth loadlib,that has the stored proc load modules, out of STEPLIB. This will give you a -444. You need to either move the DSNLOAD/DSNEXIT into STEPLIB or APF authorize the SP load library. [...] 6730 55 33_URGENT: Clob to be passed to a SP16_Arunava Majumdar36_Arunava.Majumdar@VASHIMAIL.LTITL.COM30_Sat, 3 Jun 2000 00:44:52 +0530284_us-ascii Can anyone urgently send me the details and a code snippet about hor to set up the SQLDA for passing a clob to an SP. I get the data to be passed not from a UDB table, so i need to form the clob structure as well. I've coded the following which gives me a -311 SQLCODE. [...] 6786 148 38_Re: DB2 unload IBM utility - examples?11_rick creech18_ykcirc@HOTMAIL.COM28_Fri, 2 Jun 2000 12:17:38 PDT626_- You might to use parm('sql') and a 'where clause' that will limit to the desired partition. hth, Rick Creech



>From: "Fields, Janice G" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DB2 unload IBM utility - examples? >Date: Fri, 2 Jun 2000 13:37:10 -0500 > >Mike, >Thank you very much for your help. Yes-- the whole table was unloaded on >that first step. Is there somewhere I can look for samples of DSNUM? I am >able to use image copy for all 10 partition but I am having problems >unloading each partition into individual tapes using [...] 6935 156 38_Re: DB2 unload IBM utility - examples?14_Backes,Michael26_BackeM@MAIL.OA.STATE.MO.US30_Fri, 2 Jun 2000 14:19:43 -0500509_iso-8859-1 i beleve you are a little confused on dsntiaul. it is a program not a utility. from your email i take it you are not having problems with your image copy job, and that is where you are getting the dsnum stuff. dsntiaul is not an ibm utility, it is simply a program provided to select data out of your tables. as such it doesnt use the same keywords etc the utilities do. or at least i dont think it does. and i am not sure how you would use dsntiaul to unload just a partition of a table, ive [...] 7092 153 38_Re: DB2 unload IBM utility - examples?16_Johnstone, Glenn30_Glenn.Johnstone@TRICON-YUM.COM30_Fri, 2 Jun 2000 15:13:00 -0500393_iso-8859-1 As far as I know, there is no parameter on dsntiaul to unload a part, like dsnum. You can simulate it using a where clause with the column(s) in your partitioning key, and include the PARMS('SQL') parameter in the RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) statement.

SELECT * FROM PRODDBA.GL_ENTRY_T WHERE acct_no <= 100000; for example, if accounts up to 100000 are in pt 1 [...] 7246 147 32_Re: Help Update TimeStamp Column13_Robert S Gray14_rsgray@ACM.ORG30_Fri, 2 Jun 2000 15:18:49 -0400567_iso-8859-1 Christine,

The value you insert has to be between '0001-01-01-00.00.00.000000' and '9999-12-31-24.00.00.000000'.



Regards, Bob Gray -----Original Message----- From: owner-db2-l@RYCI.COM [mailto:owner-db2-l@RYCI.COM]On Behalf Of Christine Sawyer Sent: Thursday, June 01, 2000 7:54 PM To: DB2-L@RYCI.COM Subject: Help Update TimeStamp Column



Dear List,

I need to update a column defined as TIMESTAMP programatically as opposed to having DB2 do the update. I strung all the neccessary piece of data as follows: [...] 7394 174 38_Re: DB2 unload IBM utility - examples?16_Fields, Janice G19_jfields@INDIANA.EDU30_Fri, 2 Jun 2000 14:32:56 -0500516_iso-8859-1 Thanks for all your help and suggestions. There were most helpful! I was making my job more difficult than it needed to me. There's nothing wrong with unloading the whole table at once. I was trying to make it similiar to my image copy jobstream.



Jan Fields DBATEAM Indiana University

-----Original Message----- From: Johnstone, Glenn [mailto:Glenn.Johnstone@TRICON-YUM.COM] Sent: Friday, June 02, 2000 3:13 PM To: DB2-L@RYCI.COM Subject: Re: DB2 unload IBM utility - examples? [...] 7569 159 38_Re: DB2 unload IBM utility - examples?14_Toolan, George21_George.Toolan@FMR.COM30_Fri, 2 Jun 2000 15:34:10 -0400566_iso-8859-1 Unfortunately DSNTIAUL is a sample unload program, not a utility. In its simplest form (running without the PARM option) it takes a line from sysin, puts a 'select * from table' in front of it and uses that to prepare a standard cursor. (you can have a short where clause that fits in the line with the table name) If you use the PARM('SQL') in the SYSTSIN RUN PROGRAM ... You can type complete SQL statements (delimited by a semi-colon), putting the part range information in a where clause. This is still standard processing and there is no way to [...] 7729 129 30_Re: CREATE TRIGGER hangs Spufi12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Fri, 2 Jun 2000 14:52:04 -0500349_us-ascii Solved!

Okay, if you want to hang your TSO/SPUFI session with a Create Trigger, here's how you do it.

1) Create a simple table which has a UNIQUE parameter specified containing fewer columns than the primary key. 2) Try to create a Before Insert Trigger.

It's the last thing you'll do during that TSO session..... [...] 7859 236 37_Audit trace or is there another way ?23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Fri, 2 Jun 2000 15:57:13 -0400388_iso-8859-1 Hi all !!!

I am trying to determine the access pattern (users/programs) of a particular database. Here is what I have done..

-- I have gotten the list of plans and packages for catalog. -- I have gotten the list of corrid/connid/primary authid etc.. of those dynamic SQL that does insert/update/delete against the database in question, through Log Analyzer. [...] 8096 314 41_Re: Audit trace or is there another way ?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Fri, 2 Jun 2000 16:06:33 -0400363_iso-8859-1 Sanjay

Don't go for Audit trace. Use Either SQL EVENT (class 3) OR individual IFCIDs 60 - singleton select 61 - insert/update/delete 63 - text for dynamic sql and 65 - for open cursor.

Collect them and then analyzed the access pattern.

I have done this long time ago but I lost all my clist and rexxes which did the analysis. [...] 8411 335 41_Re: Audit trace or is there another way ?12_Myron Miller22_myronwmiller@YAHOO.COM30_Fri, 2 Jun 2000 13:17:15 -0700561_us-ascii Unless Detector has changed in the last year or so since I've used it, it will do everything that you mentioned you're trying to do. I've used it several times to collect that type of data and been very happy with its ability to identify by database the activity and users, including dynamic SQL. --- "Jain, Sanjay (Exchange)" wrote: > Hi all !!! > > I am trying to determine the access pattern > (users/programs) of a particular > database. Here is what I have done.. > > -- I have gotten the list of plans and packages for [...] 8747 23 11_DB2 Connect14_Dean Montevago18_DMonteva@VNSNY.ORG30_Fri, 2 Jun 2000 16:15:35 -0400268_- Hi, We are installing DB2 Connect on a AIX box to communicate with DB2 on S390. What has to be done from an S390 perspective to complete this process. Does DB2 need to be updated ? Do I need to reserve ports in IP ? Any help would be appreciated. Regards, Dean [...] 8771 90 62_Re: DB2 OS/390 Stored Procedures and the -DISPLAY PROC command15_Belfield, Al G.15_AGBELFI@TWA.COM30_Fri, 2 Jun 2000 15:21:27 -0500405_iso-8859-1 So -DISPLAY PROC is okay, but -START PROC and -STOP PROC in V5 require at least SYSOPR auth. How are sites enabling their developers to -start and -stop their stored procedures?

-----Original Message----- From: Lynne Flatley [mailto:LFlatley@NEFN.COM] Sent: Thursday, June 01, 2000 10:44 AM To: DB2-L@RYCI.COM Subject: Re: DB2 OS/390 Stored Procedures and the -DISPLAY PROC command [...] 8862 22 15_DB2 UDB Restore13_Helen Johnson25_helen_johnson@RAC.RAY.COM30_Fri, 2 Jun 2000 15:28:15 -0500610_us-ascii Hi, We are using DB2 UDB v5.2 on AIX. This system uses off-line backups of our production database which are periodically restored to databases on development boxes. The restores are done with the "without rolling forward" parameter and we have no problem with them. Now our window is shortening and we need to go to online backups of our production system. We use archive logging on the production system so recovery to production should be no problem. I will still need to do restores to the development databases. Those databases have circular logging. Can I still restore from the production [...] 8885 78 39_Re: SQL2036N on Load in UDB v6.1 on AIX13_Umair Hussain25_umair_hussain@HOTMAIL.COM28_Fri, 2 Jun 2000 15:33:29 CDT352_- Just a quick thought ... your problem could very well be a bug but I would avoid running load utility from the command line. It is best to run load utility from a file .. issuing following command from clp ...db2 -stvf load.cmd > load.out .. Where load.cmd will have your load command ending with ; and load.out is the output from load command. [...] 8964 44 27_release(deallocate) problem16_Alison Pelletier26_alison_pelletier@YAHOO.COM30_Fri, 2 Jun 2000 13:40:06 -0700617_us-ascii DB2 OS/390 V5.

I have bound several online packages for one application with release(deallocate) which has reduced CPU significantly. One problem, though. There is a batch program that does a delete of many rows during online hours (one delete statement) which evidently was escalating. So when it tries to escalate to tablespace lock it got resource unavailable. In the log of the db2 master it indicates that the holder was the online plan (several packages) fighting for type of D01 and then the DBID.OBID which matches to the table not the tablespace. So I bound the one online package which [...] 9009 49 19_Re: DB2 UDB Restore10_Leo Pedron19_lpedron@BOSCOVS.COM30_Fri, 2 Jun 2000 16:57:14 -0400422_iso-8859-1 No, DB2 will require that you roll forward in time till the end of the backup image. You will need to have all the log files from the start of the backup image till the time it completes.

Leo Pedron

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Helen Johnson Sent: Friday, June 02, 2000 4:28 PM To: DB2-L@RYCI.COM Subject: DB2 UDB Restore [...] 9059 62 31_Re: release(deallocate) problem12_Lisle, Kathy29_Kathy.Lisle@GMACINSURANCE.COM30_Fri, 2 Jun 2000 17:00:06 -0400478_iso-8859-1 One possibility: is your plan bound with RELEASE(DEALLOCATE)? If the package and plan RELEASE parameters conflict, DB2 chooses RELEASE(DEALLOCATE) (according to the Admin Guide, chapter 5-7). Unfortunately, that doesn't explain why you don't see the locks when you display db.

-----Original Message----- From: Alison Pelletier [mailto:alison_pelletier@YAHOO.COM] Sent: Friday, June 02, 2000 4:40 PM To: DB2-L@RYCI.COM Subject: release(deallocate) problem [...] 9122 65 31_Re: release(deallocate) problem24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Fri, 2 Jun 2000 14:09:40 -0700342_- You might also consider disabling lock escalation by altering the tablespace to LOCKSIZE PAGE. It's probably set to LOCKSIZE ANY. Be aware, though that you could possibly bump into the maximum lock limits set by the DB2 system administrator. You may still run into contention problems due to the large number of page locks being held. [...] 9188 87 15_Re: DB2 Connect17_Linda F. Claussen18_lindafc@NETINS.NET30_Fri, 2 Jun 2000 16:26:43 -0500382_ISO-8859-1 Dean,

The following must match up when using DB2 Connect to access OS/390:

Connect Workstations Host Host Name Host Name (TCPIP.DATA) IP address IP address (TCPIP definitions) PORT number (usually 446) PORT number (TCPIP.PROFILE.TCPIP and DB2 BSDS) Target DB name (DCS directory) LOCATION name in DB2 BSDS) LUNAME in DB2 BSDS match LUNAME in VTAM APPL [...] 9276 88 31_Re: release(deallocate) problem16_Alison Pelletier26_alison_pelletier@YAHOO.COM30_Fri, 2 Jun 2000 14:30:41 -0700666_us-ascii The plan is bound release(commit). I thought the package would override... --- "Lisle, Kathy" wrote: > One possibility: is your plan bound with > RELEASE(DEALLOCATE)? If the > package and plan RELEASE parameters conflict, DB2 > chooses > RELEASE(DEALLOCATE) (according to the Admin Guide, > chapter 5-7). > Unfortunately, that doesn't explain why you don't > see the locks when you > display db. > > -----Original Message----- > From: Alison Pelletier > [mailto:alison_pelletier@YAHOO.COM] > Sent: Friday, June 02, 2000 4:40 PM > To: DB2-L@RYCI.COM > Subject: release(deallocate) problem > > > DB2 OS/390 V5. > > I have [...] 9365 44 15_Re: DB2 Connect14_Philip Gunning20_pgunning@BOSCOVS.COM30_Fri, 2 Jun 2000 17:36:11 -0400436_iso-8859-1 Dean, I would suggest you use the redbook, SG24-2212-00, WOW! DRDA Supports TCP/IP for DB2 for OS 390 et al. Phil Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert - DB2 DRDA IBM Certified Solutions Expert -- CICS TS

----- Original Message ----- From: Dean Montevago Newsgroups: bit.listserv.db2-l To: Sent: Friday, June 02, 2000 4:15 PM Subject: DB2 Connect [...] 9410 93 31_Re: release(deallocate) problem9_Rob Crane22_racrane@CONCENTRIC.NET30_Fri, 2 Jun 2000 15:53:50 -0600525_us-ascii Check to see that the plan executing the batch delete is bound with ACQUIRE(USE) and not ACQUIRE(ALLOCATE), all packages are bound with ACQUIRE(USE) as this is the only option for packages.

RELEASE(DEALLOCATE) does not effect X lock retention for pages, only tablespace locks are held until thread deallocation. RELEASE(COMMIT) increases resources because you have to release, reacquire and repropagate logical locks at the tablespace level. As you have found the real problem is the lack of committing. [...] 9504 71 31_Re: release(deallocate) problem14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Fri, 2 Jun 2000 17:36:36 -0400732_iso-8859-1 One potential solution is to turn off lock escalation on that tablespace.

Hope this helps, Richard Yevich +======+======+======+ Richard_Yevich@attglobal.net DB2 Performance Journal, Quarterly www.YLAssoc.com -- www.DB2-Portal.com





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Alison Pelletier > Sent: Friday, June 02, 2000 4:40 PM > To: DB2-L@RYCI.COM > Subject: release(deallocate) problem > > > DB2 OS/390 V5. > > I have bound several online packages for one > application with release(deallocate) which has reduced > CPU significantly. One problem, though. There is a > batch program that does a delete of many rows during [...] 9576 62 30_Re: DB2 Row Length/Performance14_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Fri, 2 Jun 2000 17:42:08 -0400449_iso-8859-1 Chris,

It would appear appropriate to move to 8K page. Locking would be cut in half, but you will potentially drive 2 I/Os for each logical page retrieval. If you are totally online and random, perhaps not a good idea. If mixed sequential and random, good idea.

Hope this helps, Richard Yevich +======+======+======+ Richard_Yevich@attglobal.net DB2 Performance Journal, Quarterly www.YLAssoc.com -- www.DB2-Portal.com [...] 9639 28 26_release(deallocate) update16_Alison Pelletier26_alison_pelletier@YAHOO.COM30_Fri, 2 Jun 2000 15:45:45 -0700382_us-ascii The batch pgm. that escalates completed successfully this afternoon. The online CICS pgm. that was changed back to release(commit) was new copied about 2 hrs. before the batch pgm ran yesterday. I did NOT do a dis db/ts locks yesterday so I'm not sure if the TS lock was released right away. Will it be held for a while or will it release as soon as new pgm. is used? [...] 9668 76 15_Re: DB2 Connect13_Martin Kenney25_martin.kenney@RAILINC.COM30_Fri, 2 Jun 2000 20:42:43 -0400365_us-ascii I believe you also have to bind package DSNWZP and add it to SYSIBM.SYSPROCEDURES. The information for the bind and adding to SYSPROCEDURES can be located in hlq.SDSNSAMP(DSNTIJSG). You also need to have TCP/IP turned on in your DB2 region which means you need to be at least at version 5 of DB2. OMVS also needs to be configured for TCP/IP for this. [...] 9745 76 15_Re: DB2 Connect14_Roy R Reynolds20_Roy_Reynolds@GAP.COM30_Fri, 2 Jun 2000 18:08:32 -0700570_us-ascii Dean, Be sure to look for any informational APAR in IBMLINK.

The following is only applicable if you are using TCP/IP for communication to os/390 DB2. Be aware that you might have to add the SYSTCPD DD to your db2xDIST proc if you have a more than one TCP/IP component defining HOSTNAME and TCPIPJOBNAME. It ensures DB2 knows which host name to use. I've been told by an IBMer that it isn't clearly documented, but might be alluded to indirectly. It can be a very confusing show-stopper when applicable. Here is an example of the error you can get: [...] 9822 20 15_Re: IFCID - 12613_Mayank Sharma33_Mayank.Sharma@POWAIMAIL.LTITL.COM30_Sat, 3 Jun 2000 08:36:01 +0530512_us-ascii Hi Venkat

Thanks for your reply. This we have checked, found no problem in this side. Is there any Patch required for IFCID trace?? I had go thru PDFs. it only tells about DB2 4.1 and below. and LE/370. While We are using DB2 V5 and LE/390.

Mayank

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 9843 200 21_Re: Clob problem.....14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Sat, 3 Jun 2000 16:02:26 +1000362_iso-8859-1 Ron,

1) You have to declare your variables to DB2. Read Sect 4.2.2 Declaring LOB host variables and LOB locators in the DB2 Appl Prog Guide. You'll need something like: SQL TYPE IS CLOB(65536) clob_var; to create a clob variable. ****Without this, DB2 is doing to treat your data as some sort of varying length string - not as a clob.**** [...] 10044 52 15_Re: SR. DB2 DBA14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Sat, 3 Jun 2000 16:10:09 +1000700_iso-8859-1 Good luck in finding someone with 5 years experience in DB2 V6.

-----Original Message----- From: Patricia Pardo [mailto:ppardo@ICBMCORP.COM] Sent: Saturday, June 03, 2000 12:32 AM To: DB2-L@RYCI.COM Subject: SR. DB2 DBA



Hello all,

We are currently seeking a Senior DB2 DBA to fill key position responsible for physical and logical database design and implementation in Upstate New York. This is a Technical Lead/Functional Manager position. The DBA will manage, mentor and lead a team of DB2 DBAs, a mid-level and an entry level and will interface with other key departments. Key Responsibilities: Database Design and Implementation in a DB2 environment. [...] 10097 72 37_Re: URGENT: Clob to be passed to a SP14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Sat, 3 Jun 2000 16:15:20 +1000381_iso-8859-1 You have certainly tried to be too clever 1) use SQL TYPE IS CLOB to create your clob variable 2) then just EXEC SQL CALL esw.esw_save1(:integer_var, :clob_var);

-----Original Message----- From: Arunava Majumdar [mailto:Arunava.Majumdar@VASHIMAIL.LTITL.COM] Sent: Saturday, June 03, 2000 5:15 AM To: DB2-L@RYCI.COM Subject: URGENT: Clob to be passed to a SP [...] 10170 256 21_Re: Clob problem.....16_Arunava Majumdar36_Arunava.Majumdar@VASHIMAIL.LTITL.COM30_Sat, 3 Jun 2000 13:31:30 +0530746_us-ascii Hi James, Thank you for the valuable nformation. I know the deadline is almost unfeasable but cannot help it. It has to be made ready at nay cost. I still have not tried this I'll do it right away. I looked at the code I had sent in a hurry.... there is a small mistake(rather a critical one) It should read as: for (int nBreakCounter=0;nBreakCounter=65536) { strncpy(clob_var.data,pSAPData+65536*nBreakCounter,65536); clob_var.length = 65536; } else { strcpy(clob_var.data,pSAPData+65536*nBreakCounter); clob_var.length = strlen(pSAPData+65536*nBreakCounter); } if (nBreakCounter=0) EXEC SQL SET :clob_loc = :clob_var; else EXEC SQL SET :clob_loc = :clob_loc || :clob_var; } [...] 10427 109 37_Re: URGENT: Clob to be passed to a SP16_Arunava Majumdar36_Arunava.Majumdar@VASHIMAIL.LTITL.COM30_Sat, 3 Jun 2000 13:40:34 +0530517_us-ascii James,

I cannot declare the host variable as SQL TYPE IS CLOB(100 M) since it will declare a static array of 100MB even if the data to be passed is say 100K or even less than that. Usually the volume of data is 64K and the max load might reach 100MB. So I need to take care of that. A 64K buffer is the best idea I could come up with using the locator (the mail I had already sent). Anyway are the parameters of the sqlda set properly or is there anything else required. Just let me know. Thanks [...] 10537 312 25_URGENT: Clob problem.....16_Arunava Majumdar36_Arunava.Majumdar@VASHIMAIL.LTITL.COM30_Sat, 3 Jun 2000 14:42:47 +0530569_us-ascii Hi James, The precompiler is giving the following error :

SQL0060W The "C++" precompiler is in progress. 94 SQL0104N An unexpected token ":hC_Loc_SAP_data = :hC_SAP_data" was found following "set ". Expected tokens may include: "". SQLSTATE=42601 97 SQL0104N An unexpected token ":hC_Loc_SAP_data = :hC_Loc_SAP_data" was found following "set ". Expected tokens may include: "". SQLSTATE=42601 SQL0095N No bind file was created because of previous errors. SQL0091W Precompilation or binding was ended with "3" errors and "0" warnings. [...] 10850 81 30_Re: DB2 Row Length/Performance15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Fri, 2 Jun 2000 07:42:23 -0500328_us-ascii Richard Yevich wrote: > > Chris, > > It would appear appropriate to move to 8K page. Locking would be cut in > half, but you will potentially drive 2 I/Os for each logical page retrieval.



Since I am not versed at all in this aspect of DB2, but do have a tremendous background in I/O subsystems..... [...] 10932 50 26_Re: Hiperpool in DSNDB07 ?15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Fri, 2 Jun 2000 07:45:51 -0500467_us-ascii Pillay, Venkat (PCA) wrote: > > Is it 2GB per address space or per LPAR ? Due 32bit addressing, 2GB is limit > per address space.



Actually, 31-bit addressing as the high bit is used by the PSW to tell which addressing mode to execute in.



> > I thought in CMOS the memory is configurable. To configure it as REAL or > EXPANDED is up to MVS SYSPROG. > If CICS,IMS & STC etc. needs more REAL then it is possible to configure. [...] 10983 130 62_Re: DB2 OS/390 Stored Procedures and the -DISPLAY PROC command9_jim.szabo22_jim.szabo@MCIWORLD.COM30_Sat, 3 Jun 2000 22:00:50 -0400410_iso-8859-1 We haven't built it yet, but are talking about constructing a stored procedure to do this.

Here are our plans: build a stored procedure that makes a security call (ours is RACF) to get back the secondary authids of the executor. Verify that the stored procedure that they wish to stop/start has EXECUTE authority granted to one of the secondary IDs in the list. If so, do what they ask. [...] 11114 22 53_Joel Zigelstein/Canada/HALLMARK is out of the office.15_Joel Zigelstein20_jzigel1@HALLMARK.COM30_Sun, 4 Jun 2000 09:41:10 -0400431_us-ascii I will be out of the office from 05/08/2000 until 06/15/2000.

Should you need immediate help .. - please contact for Payroll/HR concerns, contact Dave Gibson at dgibso1@hallmark.com or ext. 2579 - please contact for Database concerns, contact Lori Chin at lchin1@hallmark.com or ext. 2210

Please Note: I may be returning later than the above date and I will respond to all other messages when I return. [...] 11137 65 64_=?iso-8859-1?Q?Announcing_the_YL&A_DB2=AE_Performance_Journal!?=16_Richard A Yevich21_ryevich@BELLSOUTH.NET30_Sun, 4 Jun 2000 18:29:57 -0400414_iso-8859-1 Announcing the YL&A DB2® Performance Journal!

A quarterly journal dedicated to bringing you in-depth papers on the hottest topics in DB2 performance on all platforms written by well known industry authors, lecturers and consultants. We will deliver the highest quality, in-depth papers on all aspects of DB2 Performance written by the experts - without limits. Many other benefits included. [...] 11203 21 6_DSTATS17_Vijay Subramanyam33_vijay.subramanyam@HPSBLR.SOFT.NET30_Mon, 5 Jun 2000 11:15:20 +0500532_- Hello List, I would like all the information regarding Dstats. What I have heard is that DSTATS will give you additional statistics beyond the statistics collected by RUNSTATS utility. I would like more information on the DSTATS compared to RUNSTATS.

Thanks in advance

VIJAY

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 11225 53 10_Re: DSTATS16_Richard A Yevich21_ryevich@BELLSOUTH.NET30_Mon, 5 Jun 2000 01:31:32 -0400316_iso-8859-1 Best go to www.idug.org and look for past issues of the Solution Journal. There was a long article on DSTATS there. For any system with major NUDS and correlated index columns, you really need to look at this free utility. I forget at the moment, but there are places to download it from on the web. [...] 11279 47 10_Re: DSTATS15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM30_Mon, 5 Jun 2000 16:43:30 +1000436_- Vijay, We had a requirement to collect distribution statistics on columns that didn't appear in indexes but for which we needed reliable filter factors for performance reasons. After installing the DSTATS software we needed to create a table named SYSIBM.SYSTEST1 (or similar) before it would BIND.

The information is available in IDUG Solutions Journal March 1999 - Volume 6, Number 1 (may be under http://www.idug.org ) [...] 11327 86 55_Re: sortdata with unload external in the reorg utility?20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM30_Mon, 5 Jun 2000 10:09:24 +0100404_iso-8859-1 I think it is misleading to say "REORG without SORTDATA is approximately 10 % faster."

Which method is faster depends entirely on the organisation of the particular index and tablespace. The point of the parameter for a normal (reorganising) reorg is to allow you to choose whether to unload via the clustering index or to unload via a tablespace scan and then do a separate sort. [...] 11414 30 32_Re: Problem with DB2 Replication12_Mike O'Neill29_mike.oneill@CUSTOMS.TREAS.GOV30_Mon, 5 Jun 2000 05:48:39 -0400475_US-ASCII We received a virus alert on the message below, which came from the RYIC list server. It shows as having 2 attachments of text files. One is 648 bytes the other is 179. They are not named files. They are also not attached to this reply.



______________________________ Reply Separator _________________________________ Subject: Problem with DB2 Replication Author: "DB2 Data Base Discussion List" at smtplink Date: 6/2/00 1:33 AM [...] 11445 57 34_REUSE with LOAD...PART n REPLACE ?0_18_mebert@AMADEUS.NET30_Mon, 5 Jun 2000 13:40:22 +0100590_us-ascii Hello List,

we have a big Tablespace (22 Partitions, partition size 1.5 ... 2 GB) where the partitions are LOAD REPLACEd in a round-robin fashion. We have had several failures of the LOAD utility because there wasn't enough space to define the VSAM dataset (or better, not enough space in a sufficiently low number of extents). For non-partitioned TSs, using the REUSE parameter solved this type of problem. But I have not managed to make it work for partitioned TSs. In all cases where DB2 doesn't complain about a syntax error on the LOAD stmt, the REUSE seems to be [...] 11503 71 10_Re: DSTATS32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR30_Mon, 5 Jun 2000 14:57:51 +0300480_- I downloaded it from the URL below. I hope it's still there, otherwise i can send anyone who wants it.

Go to http://www.redbooks.ibm.com Then Choose Additional Materials / Download FTP Site. It's there..

It showed its value for some of our heavily used DW Queries.

Good luck. Cuneyt Goksu

-----Original Message----- From: Richard A Yevich [mailto:ryevich@BELLSOUTH.NET] Sent: Monday, June 05, 2000 8:32 AM To: DB2-L@RYCI.COM Subject: Re: DSTATS [...] 11575 14 34_Re: DSTATS info request from Vijay0_18_jchimes@PARKER.COM30_Mon, 5 Jun 2000 08:07:47 -0400444_us-ascii Go to www.idug.org and do a search on 'DSTATS'. The DB2 Solutions journal has an article 'Improving DB2 for OS/390 Query Performance with DSTATS' that also contains a link to download the code.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 11590 75 30_Re: DB2 Row Length/Performance12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Mon, 5 Jun 2000 13:27:00 +0100435_- Have you considered compression?

Colin Allen DB2 DBA Abbey National

Colin.Allen@AbbeyNational.Co.Uk ---------- From: DAVIS, RICK (SBCSI) To: DB2-L@RYCI.COM Subject: Re: DB2 Row Length/Performance Date: 02 June 2000 13:50

Hi Chris, Actually, the math works out that a 8K page would be best. However, if 8K pages are handled similar to the way 32K pages were fetched in V5 and down, I'd have second thoughts. [...] 11666 22 24_@ DB2 for OS390 V6 vs V711_John Rosser24_jhrosser@DUKE-ENERGY.COM30_Mon, 5 Jun 2000 07:25:55 -0500318_- Is anyone considering V7 migration from V5, skipping V6?

After all in the V7 announcement (#200-087) TITLE IBM DB2 UDB SERVER FOR OS/390, VERSION 7 DELIVERS THE POWER FO E-BUSINESS AND DATA WAREHOUSE APPLICATIONS it states: "DB2 UDB for OS/390, Version 7 has support for migration from Version 5 or 6. " [...] 11689 35 24_Stored procedure problem0_19_Sven.Heidorn@CSN.SE30_Mon, 5 Jun 2000 14:57:35 +0200490_ISO-8859-1 Hi all,

could someone please assist me with the following problem. I get a WLM error when I try to execute a stored procedure in the DB2 managed SPAS.

STC09784 DSNX967I - DSNX9OPN ATTEMPT TO PERFORM WLM FUNCTION JOIN FAILED WITH WLM RC = 00000008 RSN = 033A0810 SSN = DB22 PROC= ASID = 00000035 WLM_ENV = STC09784 DSNX967I - DSNX9CLO ATTEMPT TO PERFORM WLM FUNCTION LEAVE FAILED WITH WLM RC = 00000008 RSN = 033D0810 SSN = DB22 PROC= ASID = 00000035 WLM_ENV = [...] 11725 87 38_Re: REUSE with LOAD...PART n REPLACE ?15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM30_Mon, 5 Jun 2000 08:55:54 -0400425_iso-8859-1 I'm reviewing the PTF #UQ22814/PQ14636 documentation that I put on in our DB2 V4.1 and REUSE on the LOAD statement syntax doesn't include a PART keyword capability and the only reason I could think of that may make since is don't you LOAD a table; not a tablespace and partition control is at the tablespace level? I too use REUSE on my LOADS, but never had a need to include PART on my load. Just a thought. [...] 11813 47 28_Re: @ DB2 for OS390 V6 vs V713_Martin Kenney25_martin.kenney@RAILINC.COM30_Mon, 5 Jun 2000 09:07:33 -0400426_us-ascii We considered it but are only at OS/390 1.3, DB2V7 requires 2.7. We would also wait to find out about the pricing for utilities changes in DB2V7 before considering skipping releases.







John Rosser on 06/05/2000 08:25:55 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Marty R Kenney/RICC/AAR) [...] 11861 20 24_IFCID for SQL statements8_Kurian B23_Bejoy_Kurian@MAY-CO.COM30_Mon, 5 Jun 2000 09:30:29 -0500359_us-ascii Hi All, Could somebody tell me which IFCID will collect SQL statements? Is it possible to trace a plan online and collect all information's for SQL statements in DB2PM, like ATF trace on Omegamon for DB2? I tried different options and could get only the over all details for that plan. I am looking for a report like omegamon for DB2 generates. [...] 11882 31 29_DB2 dataset info from Catalog15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Mon, 5 Jun 2000 10:44:12 -0400430_iso-8859-1 To all,

Can we find the following information for DB2 table space dataset from Catalog using REXX. I don't know REXX but if I can find, I will learn REXX. This must be one of the simple question. But it is big question for me now!!!!

1. Number of Tracks allocated. 2. Number of Tracks used. 3. Number of extends 4. What are volume in which the dataset is allocated. ----This is just for information. [...] 11914 54 33_Re: DB2 dataset info from Catalog14_Steve Domarski26_sdom@PROPAPPR.MARION.FL.US30_Mon, 5 Jun 2000 10:42:05 -0400330_- If this is OS/390 or VM then take a look at PIPES. There is an SQL interface that can be set up to interface DB2 and then it can Select on just about anything. PIPES can be called by rexx and the output can easily be routed back but pipes is capable of formatting data much better than rexx. Try it you'll never look back. [...] 11969 49 33_Re: Question on Runstats Sampling11_Ray H Janes27_ray_h_janes@EMAIL.MOBIL.COM30_Mon, 5 Jun 2000 09:45:24 -0500354_us-ascii We just implemented this in DB2 V5, OS 2.8. We kept the default of SAMPLE 25. We are a large SAP shop and our worst case weekly runstats ran about 21 hours. With SAMPLE, we have about 10 percent reduction in run time. No performance problems. We implemented in test and acceptance test several weeks ago with same results. Hope this helps. [...] 12019 50 15_Re: IFCID - 12620_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 5 Jun 2000 10:57:57 -0400313_us-ascii Mayan

I don't remember any patch or PTF for IFCID 126 and you are not providing me enough info.

If you think everything is fine and you have checked it then I think I can't help much without proper information.

I can give you few hints. Check it out if it is applicable to you. [...] 12070 39 98_Central PA DB2 Users Group Classes by Martin Hubel - June 27th & June 28th - The DB2 I/O Workshops10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Mon, 5 Jun 2000 11:06:09 -0400370_us-ascii Just a reminder, if you have not already registered & would like to attend, please drop me an em/vm.

The Central PA DB2 Users Group will be hosting Martin Hubel & his DB2 I/O Workshop series. The classes are being held: Tuesday, June 27th - The DB2 I/O Workshop: Part 1: Physical I/O Wednesday, June 28th - The DB2 I/O Workshop: Part 2: Logical I/O [...] 12110 77 33_Re: DB2 dataset info from Catalog14_David Williams25_David-O.Williams@UBSW.COM30_Mon, 5 Jun 2000 16:17:51 +0100337_iso-8859-1 We have written several os/390 REXX routines to do this. Using LISTC ENT(data set name ) to find the high-u-RBA and from this generate an alter statement.

The number of extents can also be found using this command.

The REXX OUTTRAP function can be used to send the results of the LISTC command to an array. [...] 12188 39 28_Re: @ DB2 for OS390 V6 vs V713_Toppins, Mike19_MToppins@UNIPAC.COM30_Mon, 5 Jun 2000 09:26:05 -0600623_iso-8859-1 We will be considering it but the pricing "issues" are a bigger factor for determining which route to go.

-----Original Message----- From: John Rosser [mailto:jhrosser@DUKE-ENERGY.COM] Sent: Monday, June 05, 2000 6:26 AM To: DB2-L@RYCI.COM Subject: @ DB2 for OS390 V6 vs V7



Is anyone considering V7 migration from V5, skipping V6?

After all in the V7 announcement (#200-087) TITLE IBM DB2 UDB SERVER FOR OS/390, VERSION 7 DELIVERS THE POWER FO E-BUSINESS AND DATA WAREHOUSE APPLICATIONS it states: "DB2 UDB for OS/390, Version 7 has support for migration from Version 5 or 6. " [...] 12228 223 33_Re: DB2 dataset info from Catalog0_18_mebert@AMADEUS.NET30_Mon, 5 Jun 2000 17:34:17 +0100478_iso-8859-1 One possibility is calling the TSO LISTCAT command from REXX, intercepting the output using OUTTRAP and parsing the data returned. When I mentioned doing this a couple of weeks ago, several people pointed me to a REXX interface program called IGGCSI00 (the Catalog Search Interface). I have in the meantime implemented this, and IGGCSI00 is really much much faster and more versatile than the LISTCAT version (thanks again to everyone who mentioned this to me). [...] 12452 59 28_Re: @ DB2 for OS390 V6 vs V712_John Cameron26_JCameron@MSI-INSURANCE.COM30_Mon, 5 Jun 2000 08:59:15 -0500392_us-ascii We're considering going from 5 to 7 later this year, possibly third quarter. I'm hoping to see any "gotchas" posted here from anyone who is going sooner ?

John Cameron jcameron@msi-insurance.com





John Rosser cc: Sent by: DB2 Subject: @ DB2 for OS390 V6 vs V7 Data Base Discussion List [...] 12512 38 37_Using VSAM Files in Stored Procedures11_Clark, Tony26_Tony.Clark@TWC.STATE.TX.US30_Mon, 5 Jun 2000 10:43:08 -0500561_- Hello List:

We are trying to use DB2 stored procedures to access VSAM files (we read somewhere that IBM says you can do this). In order to make this work I had to steplib the VSAM dataset name into the JCL for the SPAS address space. This is not satisfactory because once the dataset is enqueued into the SPAS JCL it is no longer available to other non-DB2 programs that need to update the dataset. My question is: Has anyone found a way to use VSAM with stored procedures without affecting the started task address spaces? Ideally, we would like [...] 12551 70 33_Re: DB2 dataset info from Catalog11_Burch, Todd18_Todd_Burch@BMC.COM30_Mon, 5 Jun 2000 10:46:26 -0500324_ISO-8859-1 Sibimon,

If you are going to do this from within a TSO environment, it's pretty easy.



First, you'll need DB2 access from REXX. There are a few vendors that support this today. You will use this to get your object names from the DB2 catalog, in order to construct the DB2 dataset names. [...] 12622 59 41_Re: Using VSAM Files in Stored Procedures18_Erringer, Keith M.24_Keith.Erringer@ALCOA.COM30_Mon, 5 Jun 2000 12:00:34 -0400573_- Although I haven't used strored procedures there are two questions I might ask. Does your JCL use disp=shr ? Are your VSAM SHAREOPTIONS at least (2,3) ?

Keith.

> ---------- > From: Clark, Tony[SMTP:Tony.Clark@TWC.STATE.TX.US] > Reply To: DB2 Data Base Discussion List > Sent: Monday, June 05, 2000 11:43 AM > To: DB2-L@RYCI.COM > Subject: Using VSAM Files in Stored Procedures > > Hello List: > > We are trying to use DB2 stored procedures to access VSAM files (we read > somewhere that > IBM says you can do this). In order to make this work I had to [...] 12682 87 41_Re: Using VSAM Files in Stored Procedures11_Clark, Tony26_Tony.Clark@TWC.STATE.TX.US30_Mon, 5 Jun 2000 11:21:42 -0500653_- The DD card in the JCL is set to DISP=SHR and the VSAM SHAREOPTIONS for the dataset are (2,3).

Tony Clark DB2 System Administrator Texas Workforce Commission 15th Congress Ave ADP Room 320 Austin, Texas 78778-0001 E-Mail: tclark@twc.state.tx.us Phone: (512) 463-3079 Fax: (512) 936-3190

> -----Original Message----- > From: Erringer, Keith M. [SMTP:Keith.Erringer@ALCOA.COM] > Sent: Monday, June 05, 2000 11:01 AM > To: DB2-L@RYCI.COM > Subject: Re: Using VSAM Files in Stored Procedures > > Although I haven't used strored procedures there are two questions I might > ask. > Does your JCL use disp=shr ? Are your VSAM SHAREOPTIONS [...] 12770 73 41_Re: Using VSAM Files in Stored Procedures17_Massimo Biancucci19_mbiancucci@DEBIS.IT30_Mon, 5 Jun 2000 11:07:38 -0500615_- On Mon, 5 Jun 2000 12:00:34 -0400, Erringer, Keith M. wrote:

>Although I haven't used strored procedures there are two questions I might >ask. >Does your JCL use disp=shr ? Are your VSAM SHAREOPTIONS at least (2,3) ? > >Keith. > >> ---------- >> From: Clark, Tony[SMTP:Tony.Clark@TWC.STATE.TX.US] >> Reply To: DB2 Data Base Discussion List >> Sent: Monday, June 05, 2000 11:43 AM >> To: DB2-L@RYCI.COM >> Subject: Using VSAM Files in Stored Procedures >> >> Hello List: >> >> We are trying to use DB2 stored procedures to access VSAM files (we read >> somewhere that >> IBM [...] 12844 104 33_Re: DB2 dataset info from Catalog0_22_Rohn.Solecki@MTS.MB.CA30_Mon, 5 Jun 2000 11:21:48 -0500389_us-ascii Some of the info can be found in the catalog, some of the catatlog info depends on runstats to update it. To name a few:

sysibm.systablespace.nactive - number of active db2 pages (depends on runstats) sysibm.systablespace.space - # of KB allocated in a stogroup (depends on STOSPACE util for updates)

sysibm.systablepart.storname - stogroup name used by the TS [...] 12949 363 41_Re: Audit trace or is there another way ?23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Mon, 5 Jun 2000 12:36:38 -0400564_iso-8859-1 Thanks Venkat...I will look them up

Sanjay

> -----Original Message----- > From: Pillay, Venkat (PCA) [SMTP:venkat_pillay@ML.COM] > Sent: Friday, June 02, 2000 4:07 PM > To: DB2-L@RYCI.COM > Subject: Re: Audit trace or is there another way ? > > Sanjay > > Don't go for Audit trace. Use Either SQL EVENT (class 3) OR > individual IFCIDs > 60 - singleton select > 61 - insert/update/delete > 63 - text for dynamic sql > and 65 - for open cursor. > > Collect them and then analyzed the access pattern. > > I have done this long time ago [...] 13313 137 62_Re: DB2 OS/390 Stored Procedures and the -DISPLAY PROC command0_19_Tim.Lowe@STPAUL.COM30_Mon, 5 Jun 2000 11:46:39 -0500298_us-ascii Lynne, I built an ISPF dialog that can issue a message that is picked up by our automation package (OPS/MVS), that does the start or stop using a SYSOPR authid. It uses our own REXX/DB2 interface, so it is not something that is easily shared. Maybe someone else has something better? [...] 13451 32 14_DSNTIAUL Doubt11_Anshu Mohan20_anshu_garg@YAHOO.COM30_Mon, 5 Jun 2000 10:12:47 -0700427_us-ascii Hi !

I had been under the impression that IBM supplied program DSNTIAUL was for 'downloading' the tables while program DSNTIAD was for executing 'DDLs'. But, on insistence from a colleague, I tried giving an 'Alter' statement with DSNTIAUL and .....it worked !!

Could someone from the list enlighten me on this..If DSNTIAUL encompasses features of DSNTIAD too, why would someone use DSNTIAD at all. [...] 13484 58 18_Re: DSNTIAUL Doubt18_Bourgoine, Peter A33_Peter.A.Bourgoine@JPFINANCIAL.COM30_Mon, 5 Jun 2000 13:34:01 -0400352_iso-8859-1 Hi Anshu, TIAD, TIAUL and DSNTEP2 are sample programs. There is a nice chart and discussion of what each can do in the DB2 Installation Guide (starting on page 2-354 of the V5 manual). Pete

Peter Bourgoine - DBA Data Resource Management Jefferson Pilot Financial 1 Granite Place Concord, NH 03301 603-226-5906 603-226-4212 (fax) [...] 13543 139 41_Re: Using VSAM Files in Stored Procedures20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 5 Jun 2000 13:40:09 -0400508_iso-8859-1 The whole thing could be a problem if you want to do multiple updates from various systems. You have to make your VSAM SHAREOPTIONS (3,3) and use ENQ/DEQ method to make it work across regions.

SHARE OPTIONS (cross-region cross-system)

Region - 1 - all users read, only 1 can read & write to data set concurrently 2 - all users read and 1 user writes concurrently 3 - all users read and write concurrently 4 - all users read and write concurrently, user program must use ENQ/DEQ [...] 13683 87 38_Re: REUSE with LOAD...PART n REPLACE ?13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Mon, 5 Jun 2000 13:03:45 -0500638_US-ASCII Hello Michael, I tried to recreate here and was unable to reproduce your results. Its working as described in the manual. Were OS/390 V5.1 PDO 0003 + HYPERS Kurt



>>> mebert@AMADEUS.NET 06/05/00 07:40AM >>> Hello List,

we have a big Tablespace (22 Partitions, partition size 1.5 ... 2 GB) where the partitions are LOAD REPLACEd in a round-robin fashion. We have had several failures of the LOAD utility because there wasn't enough space to define the VSAM dataset (or better, not enough space in a sufficiently low number of extents). For non-partitioned TSs, using the REUSE parameter solved this type [...] 13771 75 28_Re: @ DB2 for OS390 V6 vs V70_22_Rohn.Solecki@MTS.MB.CA30_Mon, 5 Jun 2000 13:19:56 -0500543_us-ascii We just heard about the option of direct V5 to V7 upgrade last week.

One of the calculations we will be doing will compare the one time costs (testing etc) for doing a V6 upgrade against the increased(?) cost of V7 "DBMS" plus utilities (both purchase and maintenance costs, of course). We are wondering if it might not be cheaper in the long run to convert to V6 then hold off on V7 for as long as we don't need the extra V7 functionality. We expect to upgrade to V6/7 in the next year or two because there is additional [...] 13847 38 51_Federated databases setup error for UDB v7.1 for NT8_Rob Seay24_robert.seay@ZURICHUS.COM30_Mon, 5 Jun 2000 13:18:02 -0500443_- Hello folks,

I am having trouble setting up federated databases on an NT server running UDB version 7.1. The foreign source I am attempting to access is DB2 for OS/390 version 5.1.2. The problem occurs when I run the following "Create Server" command in UDB/NT:

CREATE SERVER "DB2TFED" TYPE DB2/390 VERSION 5.1.2 WRAPPER "DRDA" AUTHID mymainframeid PASSWORD mymainframepassword OPTIONS(NODE 'servername', DBNAME 'dbname') [...] 13886 175 41_Re: Using VSAM Files in Stored Procedures11_Clark, Tony26_Tony.Clark@TWC.STATE.TX.US30_Mon, 5 Jun 2000 13:37:24 -0500695_iso-8859-1 Pat:

Here is another response suggesting different SHRLEVEL options for the dataset. This seems to suggest that a non-DB2 program can get access to the dataset.



Tony Clark DB2 System Administrator Texas Workforce Commission 15th Congress Ave ADP Room 320 Austin, Texas 78778-0001 E-Mail: tclark@twc.state.tx.us Phone: (512) 463-3079 Fax: (512) 936-3190

> -----Original Message----- > From: Pillay, Venkat (PCA) [SMTP:venkat_pillay@ML.COM] > Sent: Monday, June 05, 2000 12:40 PM > To: DB2-L@RYCI.COM > Subject: Re: Using VSAM Files in Stored Procedures > > The whole thing could be a problem if you want to do multiple updates from > various systems. [...] 14062 20 18_Passing clob to SP16_Arunava Majumdar36_Arunava.Majumdar@VASHIMAIL.LTITL.COM30_Tue, 6 Jun 2000 00:06:39 +0530352_us-ascii Does anyone have any idea about how to pass a clob to a stored proc? Please mail me the piece of code that does the trick. I cannot declare the clob variable as a host since it will result in a static array. I need to set up the SQLDA to pass a clob and 2 integers...... And how to retrieve it in the SP. Thanks in advance, Regards, Ron. [...] 14083 159 32_Re: Help Update TimeStamp Column15_Andrea Milligan25_Andrea_Milligan@BCBST.COM30_Mon, 5 Jun 2000 15:20:00 -0400731_- If these are year 2000 dates, the form is:

"2000-00-00-02.44.26.725410" and "2000-00-00-15.44.40.417100".



Andrea Milligan DataBase Administrator BlueCross BlueShield of Tennessee

> -----Original Message----- > From: Christine Sawyer [SMTP:cosawyer@HOTMAIL.COM] > Sent: Thursday, June 01, 2000 7:54 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] Help Update TimeStamp Column > > Dear List, > > I need to update a column defined as TIMESTAMP programatically as opposed > to having DB2 do the update. I strung all the neccessary piece of data as > follows: > > "0000-00-00-02.44.26.725410" and > "0000-00-00-15.44.40.417100". > > Every effort to update returned the following message: DSNT408I SQLCODE [...] 14243 70 32_Re: Help Update TimeStamp Column19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US28_Mon, 5 Jun 2000 15:17:31 EDT504_us-ascii Andrea/Christine,

The problem with both your solutions is that you have '00' in the month and date portion of your values. DB2 will not allow that. In both the examples, change those to '01' and DB2 will accept it.

Hope this helps.

Regards,

Rajeev P Dhanawade Phone : 302-577-4659 Ext. 221 ++ Fax : 302-577-4454 ++ ------------- Original Text From: "Andrea Milligan" , on 06/05/2000 3:20 PM: If these are year 2000 dates, the form is: [...] 14314 70 32_Re: Help Update TimeStamp Column13_Lynne Flatley17_LFlatley@NEFN.COM30_Mon, 5 Jun 2000 15:21:29 -0400737_us-ascii The month and day must be valid also,

"2000-06-05-02.44.26.725410" and "2000-06-05-15.44.40.417100".



> -----Original Message----- > From: Andrea Milligan [SMTP:Andrea_Milligan@BCBST.COM] > Sent: Monday, June 05, 2000 3:20 PM > To: DB2-L@RYCI.COM > Subject: Re: Help Update TimeStamp Column > > If these are year 2000 dates, the form is: > > "2000-00-00-02.44.26.725410" and > "2000-00-00-15.44.40.417100". > > > > Andrea Milligan > DataBase Administrator > BlueCross BlueShield of Tennessee > > -----Original Message----- > From: Christine Sawyer [SMTP:cosawyer@HOTMAIL.COM] > Sent: Thursday, June 01, 2000 7:54 PM > To: DB2-L@RYCI.COM > Subject: [DB2-L] Help Update TimeStamp Column > > Dear List, > [...] 14385 205 41_Re: Using VSAM Files in Stored Procedures22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Mon, 5 Jun 2000 15:30:15 -0400662_iso-8859-1 I have not worked with it but could VSAM Record Level Sharing (RLS?) help? I don't know the DFSMSdfp release required for RLS and if DB2 managed SPAS or WLM SPAS support LRS.



Mike Piontkowski Voice/Fax: 302.886.4612 mailto:michael.piontkowski@astrazeneca.com

> ---------- > From: Clark, Tony[SMTP:Tony.Clark@TWC.STATE.TX.US] > Sent: Monday, June 05, 2000 2:37 PM > To: DB2-L@RYCI.COM > Subject: Re: [DB2-L] Using VSAM Files in Stored Procedures > > Pat: > > Here is another response suggesting different SHRLEVEL options for the > dataset. This seems > to suggest that a non-DB2 program can get access to the dataset. > > > [...] 14591 156 41_Re: Using VSAM Files in Stored Procedures13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Mon, 5 Jun 2000 14:34:05 -0500491_iso-8859-1 Also ... we had a situation where batch jobs would delete/define and then REPRO into the VSAM datasets which were allocated to the SPAS. To get around this, we defined the VSAM datasets with the REUSE option and then the REPRO job and SPAS co-existed happily ever after. Glenn

-----Original Message----- From: Pillay, Venkat (PCA) [mailto:venkat_pillay@ML.COM] Sent: Monday, June 05, 2000 12:40 PM To: DB2-L@RYCI.COM Subject: Re: Using VSAM Files in Stored Procedures [...] 14748 369 41_Re: Audit trace or is there another way ?23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Mon, 5 Jun 2000 16:54:58 -0400627_iso-8859-1 Myron,

I checked again all the detector manuals and finally called CA support, it does not have the capability to provide the info on 'which programid(s)/userid(s) are using a given table'. Any chance it was a different product, or could you elaborate how ?

Regards

Sanjay



> -----Original Message----- > From: Myron Miller [SMTP:myronwmiller@YAHOO.COM] > Sent: Friday, June 02, 2000 4:17 PM > To: DB2-L@RYCI.COM > Subject: Re: Audit trace or is there another way ? > > Unless Detector has changed in the last year or so > since I've used it, it will do everything that you > [...] 15118 97 32_Re: Problem with DB2 Replication11_Jakobs, Jef18_JJakobs@HARTIS.COM30_Mon, 5 Jun 2000 16:13:21 -0500307_iso-8859-1 Hello Yosia,

I've run into trouble setting up DB2 replication on DB2 UDB 5.2 too (Windows NT version). Now it turned out that my client didn't really need it (so I never had to solve it), but I do know that a lot of replication problems go away when you apply the latest FixPak (13). [...] 15216 80 33_Re: DB2 dataset info from Catalog14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 6 Jun 2000 09:48:49 +1000397_iso-8859-1 Several responders to this post have mentioned using LISTCAT to get catalog information. I have recently discovered that there is a callable interface to get this information - IGGCSI00 - and there is a sample program in SYS1.SAMPLIB(IGGCSIRX). It is documented in a sufficiently late DFSMS Managing Catalogs manual (ie, it's not in the DFSMS 1.3 manual but is in the 1.5 manual). [...] 15297 38 39_Re: Commiting within a Stored Procedure12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Tue, 6 Jun 2000 13:38:19 +1200520_us-ascii As I get a 'Digest' I'm a bit behind the pace with responses. So, to buffer up the replies:

Aurora, that's probably where I'm going wrong. Perhaps if I slip a $20 note in the pocket of our SE as he walks by... ;o) By the way, sorry to hear about the electrons... Manas, looks like you're right, at least until V7. Cheers Phil, Richard. I'll look forward to V7 (and V6 before that). As an aside, I believe you can issue commits within an SP in DB2 for AIX V6. In fact, as it was presented to me, the [...] 15336 36 32_Re: Problem with DB2 Replication18_Yosia Widi Nugroho20_yosia@CENTRIN.NET.ID30_Tue, 6 Jun 2000 10:00:17 +0700465_- >Hello Yosia, > >I've run into trouble setting up DB2 replication on DB2 UDB 5.2 too (Windows >NT version). >Now it turned out that my client didn't really need it (so I never had to >solve it), but >I do know that a lot of replication problems go away when you apply the >latest FixPak (13). > >Especially if you never applied any before :-) > >Take a look at http://www-4.ibm.com/software/data/db2/db2tech/version5.html > >Hope this helps! > > >Jef Jakobs [...] 15373 56 62_Re: Patch for Running JAVA Stored Procedures for DB2 on OS/39014_Julian Stuhler27_julian.stuhler@TRITON.CO.UK30_Tue, 6 Jun 2000 06:14:14 +0100415_iso-8859-1 Hi.

The last I heard from the labs was that the APARs for Java Stored Procedure support were about to be closed. However, I just checked and they are still not listed on the APAR enhancements web page.

Maybe Roger can provide an update?

regards,

Julian



--------------------------

Julian Stuhler DB2 Specialist, IBM Gold Consultant Triton Consulting [...] 15430 54 28_Re: Stored procedure problem18_Michael Arlebrandt28_micke_arlebrandt@HOTMAIL.COM29_Tue, 6 Jun 2000 09:16:23 CEST636_- Hi Sven,

It looks strange, how does your CREATE PROCEDURE statement look like, I suppose you have defined NO WLM ENVIRONMENT while you want to use SPAS but the system think that you want to use WLM.

mvh Michael Ärlebrandt Volvo IT



>From: Sven.Heidorn@CSN.SE >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Stored procedure problem >Date: Mon, 5 Jun 2000 14:57:35 +0200 > >Hi all, > >could someone please assist me with the following problem. I get a WLM >error when I try to execute a stored procedure in the DB2 >managed SPAS. > >STC09784 DSNX967I - DSNX9OPN [...] 15485 40 15_Re: IFCID - 12614_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 6 Jun 2000 17:15:44 +1000374_iso-8859-1 If you do your programming correctly, you will initialise the ECB before you tell DB2 about it. So, even if DB2 posts it before the WAIT, the WAIT will see that the ECB has been posted and will continue without any (actual) wait.

(The situation you describe seems to have the initialisation after telling DB2 about it, but db2 had already posted it.) [...] 15526 98 26_Rowids and compressed data14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 6 Jun 2000 17:19:49 +1000328_iso-8859-1 Just a warning. We have a PMR out on using compressed rows with a ROWID and no index on the ROWID column. When a row is actually compressed, it can no longer be found using the rowid.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au [...] 15625 67 15_Re: IFCID - 12613_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Tue, 6 Jun 2000 13:35:17 +0530382_us-ascii The ECB is initialized. See the code works fine with different IFCID's ... and only in IFCID 126 it does not return anything.... The trace start fine and there is no error , but at the same time it does not return any records... Thats the problem .. Vishy









James Campbell on 06/06/2000 12:45:44 PM [...] 15693 30 25_Cancelling a batch job...0_25_Julian_Peacock@SWEB.CO.UK30_Tue, 6 Jun 2000 09:51:00 +0100431_ISO-8859-1 Fellow DB2'ers,

Last night a batch job was cancelled because it was contending with our 24 x 7 CICS' system. The operator correctly cancelled the batch job. However, and yes you've guessed it, the operator then proceeded to issue the FORCE cancel command because the offending batch job was still contending with CICS. I appreciate that DB2 will back out all uncommitted changes whenever a job is cancelled. [...] 15724 24 29_Re: Cancelling a batch job...9_Stan Hoey24_stan_hoey@CIRCLE-DL2.COM30_Tue, 6 Jun 2000 10:12:31 +0100412_us-ascii Julian,

DB2 has 2 tasks which it determines to be 'MUST COMPLETE'. These tasks are commit and rollback. If DB2 is prevented from completing either of these on behalf of an allied agent, DB2 abends. This is so that DB2 can complete the commit/rollback during restart.

I would leave it to DB2 - if it doesn't abend, then it has completed rollback and data integrity is not jeopardised. [...] 15749 63 29_Re: Cancelling a batch job...7_Sanjeev19_Sanjeev@INTERRA.COM30_Tue, 6 Jun 2000 14:43:32 +0530602_iso-8859-1 Hi Julian, I faced exactly the same situation in my earlier installation.There was a batch job did a lot of updates and then after it cancelled and doing the rollback we issued the forced cancel from operator console.When we issued the -DIS THREAD(*) ,DB2 showed that thread in D* status and i found the description in Admin Guide and which says it was in deallocated state.It was holding the locks in all the concerned tablespaces and indexspaces.We tried stopping the tablespace and it was put into stop pending status.I really was not able to understand whether DB2 is still backing [...] 15813 46 0_7_Sanjeev19_Sanjeev@INTERRA.COM30_Tue, 6 Jun 2000 14:57:00 +0530459_iso-8859-1 Hi Stan, As i said ,DB2 didn't abended in our case rather it kept holding all the resources it acquired with the respective thread in D* status.Yes, once we stopped and started the DB2 then it did the corresponding rollbacks.



Regards

Sanjeev

-----Original Message----- From: Stan Hoey [mailto:stan_hoey@circle-dl2.com] Sent: Tuesday, June 06, 2000 2:43 PM To: DB2-L@RYCI.COM Subject: Re: Cancelling a batch job... [...] 15860 50 29_Re: Cancelling a batch job...12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Tue, 6 Jun 2000 10:30:00 +0100373_- Yes, even if you cancel DB2 it will restart the backout when you restart DB2. This is because DB2 does the backout, not your job. If you force DB2 out it will continue the backout at startup as it processes any units of work which have not completed, in fact it backs out the backout then starts the backout again, but this is drift ing from your original question. [...] 15911 84 29_Re: Cancelling a batch job...23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Tue, 6 Jun 2000 11:23:59 +0100316_iso-8859-1 Colin

Just sliding this off to the right a little....I think you can defer the rollback in V6?? i.e when DB2 comes back up we can all everyone in while DB2 happily rolls back the objects involved in the earlier rollback or commit?

.. I really must stick my head in a V6 manual soon :-) [...] 15996 20 25_Problem of too many I/O's9_DB2 Admin21_db2admin@KFUPM.EDU.SA30_Tue, 6 Jun 2000 13:38:47 +0300530_US-ASCII Hello all,

We are running DB2 v5 on OS/390 2.4. These days we are consistently getting an alert that there is a problem with DSNDBM1 in the number of I/O's per sec. The I/O's per sec are too many, as reported by CA-SYSVIEW.

Any pointers on this issue?

Thanks

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16017 22 33_Re: DB2 dataset info from Catalog0_18_mebert@AMADEUS.NET30_Tue, 6 Jun 2000 13:24:25 +0100522_us-ascii Hello List,

the promised IGGCSI00 REXX/SAS code is now available on the DB2-L-DOCUMENT site: http://jupiter.ryci.com/archives/db2-l-documents.html.

Have fun

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16040 45 28_Re: Stored procedure problem13_Lynne Flatley17_LFlatley@NEFN.COM30_Tue, 6 Jun 2000 07:49:53 -0400610_us-ascii Could you tell us what the row in SYSIBM.SYSPROCEDURES looks like for this stored procedure? I'm thinking perhaps that it got defined as a WLM stored procedure...

> -----Original Message----- > From: Sven.Heidorn@CSN.SE [SMTP:Sven.Heidorn@CSN.SE] > Sent: Monday, June 05, 2000 8:58 AM > To: DB2-L@RYCI.COM > Subject: Stored procedure problem > > Hi all, > > could someone please assist me with the following problem. I get a WLM > error when I try to execute a stored procedure in the DB2 > managed SPAS. > > STC09784 DSNX967I - DSNX9OPN ATTEMPT TO PERFORM WLM FUNCTION > JOIN FAILED WITH [...] 16086 115 29_Re: Cancelling a batch job...6_monica19_monica@BANEB.COM.BR30_Tue, 6 Jun 2000 09:01:21 -0300431_iso-8859-1 Hi,

I think we cannot defer rollback because of all database integrity. We had a similar situation here, and what we did was : STOP db2 with force, and then START with COLD, reseting logs. You can see this procedure in Adm Manual. It works as if you have had total loss of logs. We had luck, cause this happened in our testing environment. Be aware you can loose others updates, made by others transactions. [...] 16202 206 62_Re: DB2 OS/390 Stored Procedures and the -DISPLAY PROC command0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM30_Tue, 6 Jun 2000 07:22:46 -0500507_us-ascii Lynne, In V5, We created a mini application for our stored procedure (rexx,cobol, panels) that has these kind of options. (We'll obviously need to be revamp a bit for V6 to point to new tables)

ACS STORED PROCEDURES MAIN MENU

GENERAL OPTIONS 1 LIST EXISTING STORED PROCEDURES

DBA OPTIONS 2 ADD A STORED PROCEDURE TO SYSIBM.SYSPROCEDURES 3 COPY A STORED PROCEDURE FROM ONE SSID TO ANOTHER 4 LIST PROCEDURES STAGED FOR MIGRATION 5 LIST ARCHIVED STORED PROCEDURE DEFINITIONS [...] 16409 19 29_Re: Cancelling a batch job...0_29_paul.packham@POSTOFFICE.CO.UK30_Tue, 6 Jun 2000 13:37:52 +0000521_us-ascii Les,

What DB2 v6 gives us at restart time is the ability to process new work prior to it resolving all its backward recovery. This is controlled by the LBACKOUT parameter in the Zparms. We have ours set to auto which seens to be working well.

Regards, Paul

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16429 71 15_Re: IFCID - 12620_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Tue, 6 Jun 2000 09:11:31 -0400343_us-ascii That is not always true. I have seen certain situations when DB2 Posts and unless READA is issued it (DB2) never posts again. So if you initialize your ECB and wait you wait forever because DB2 will not post ever.

These are rare circumstance but I always issue READA immediately after -STA TRA command then I go for wait. [...] 16501 32 18_Damaged tablespace12_Andy Wheeler25_Andy.Wheeler@GRIZZARD.COM30_Tue, 6 Jun 2000 09:26:47 -0400299_iso-8859-1 We had a situation over the weekend where someone tried to do a SELECT * FROM TABLE XYZ where the table only had one row. The query never returned (nor did it time out). It was cancelled and the query SELECT * FROM TABLE XYZ WHERE columna = 'abc' and the query returned immediately. [...] 16534 110 15_Re: IFCID - 12620_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Tue, 6 Jun 2000 09:30:19 -0400584_us-ascii Vishy & Mayank

I have few suggestions after looking at details (it may be unrelated to your problem but try this and let me know if your code still doesn't work) -

- Issue the trace command with more BUFSIZE "-STA TRA (P) DEST(OPX) IFCID (126) BUFSIZE(32)" - Increase the RETAREA size to match with BUFSIZE, which is 32k. - Increase the WBUFBC (the byte count) for buffer area and make it little over 4K (before starting trace) - Issue READA immediately after "-sta tra" command. (This is harmless, because the first record is always IFCID 4, which is the [...] 16645 35 29_Re: Problem of too many I/O's14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Tue, 6 Jun 2000 09:31:31 -0400426_ISO-8859-1 What is the I/O rate/sec? Just stating it is too high does not really mean anything.

The only way to reduce it is to tune your buffer pools, and/or tune your applications to (hopefully) get better access paths to your data. The Buffer Pool Tool is the only effective way to tune the pools. Visit www.responsivesystems.com for more information, and white papers/presentations that address system tuning. [...] 16681 66 15_Re: IFCID - 12620_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Tue, 6 Jun 2000 09:33:45 -0400520_us-ascii James

You are right. I should initialize after "wait" not before "wait" in my wait loop.

-----Original Message----- From: James Campbell [SMTP:James.Campbell@HANCORP.COM.AU] Sent: Tuesday, June 06, 2000 3:16 AM To: DB2-L@RYCI.COM Subject: Re: IFCID - 126

If you do your programming correctly, you will initialise the ECB before you tell DB2 about it. So, even if DB2 posts it before the WAIT, the WAIT will see that the ECB has been posted and will continue without any (actual) wait. [...] 16748 49 29_DB2 PM and storing trace data19_GLASER, TOM (SBCSI)21_tg5444@MOMAIL.SBC.COM30_Tue, 6 Jun 2000 08:40:08 -0500388_iso-8859-1 DB2 OS/390 community and DB2 PM users:

I'm in the process of installing DB2 PM in our environments so I don't have much experience with this tool. I'm looking for some suggestions/help on setting up this tool and wanted to get an idea how other companies set up DB2 PM.

We currently have everything going to SMF data (such as IMS, MVS, CICS) except for DB2. [...] 16798 87 33_Re: DB2 PM and storing trace data23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Tue, 6 Jun 2000 14:41:13 +0100384_iso-8859-1 Tom ...how about GTF? but then this does not Archive .. just rolls back to the start.



...you already know about reducing the SMF data for PM use later .. so it looks like you are snookered.

We store our Data in the History Tables .. makes your life real easy for producing graphs and seeing how thinsg change over time or as a result of changes. [...] 16886 33 19_SEGSIZE 32 in DB2V413_McDonald, Ian32_Ian.McDonald@ABBEYNATIONAL.CO.UK30_Tue, 6 Jun 2000 14:53:00 +0100426_- Folks

we are running DB2V4 on OS/390 MVS 6.0.8 and have started having problems with VSAM/ICF catalogs when defining tablespaces with SEGSIZE 32. If we use SEGSIZE 4 all is fine.

The CREATE actually creates the VSAM cluster and dataportion and we can view it through MVS Utilities 3.4 but when returning to DB2 causes either a D70017 error or causes an abend 'error in VSAM Catalog' DSNP012I in DB2 MSTR. [...] 16920 88 15_Re: IFCID - 12619_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 6 Jun 2000 08:53:19 -0500455_iso-8859-1 Going from memory here so please verify -- As I recall DB2 posts the ECB when the buffer is nn% (80% rings a bell) full. This may have changed in newer releases. I have seen that often, even with good dispatching priority, a program couldn't get to the buffer fast enough before DB2 ran out of buffer and you loose data. This is flagged by DB2 when it occurs. Thus, consider a buffer size somewhat larger than you think will be necessary. [...] 17009 58 23_Re: SEGSIZE 32 in DB2V420_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Tue, 6 Jun 2000 10:00:28 -0400309_us-ascii That is strange. All DB2 does with different segsize is - it changes the SPACEMAP information and number of spacemaps needed nothing else. SPACEMAP is just another 4k page. This logically should not have effect on VSAM itself. I wonder if there is something else which is causing this problem ! [...] 17068 75 28_SV: Stored procedure problem0_19_Sven.Heidorn@CSN.SE30_Tue, 6 Jun 2000 16:13:25 +0200578_ISO-8859-1 The procedure is created with NO WLM ENVIRONMENT and the column WLM_ENVIRONMENT in SYSIBM.SYSROUTINES is blank for this procedure.

We have narrowed down the -904 problem to a declare global temporary table statement, and the resource is the temp database. Currently we have no clue why the temp database is considered unavailable, it has been created and there are two tablespaces assigned to it which both have been granted to public. Also, all other resources like bufferpools and storagegroups that are used by the temp database are granted to public. [...] 17144 178 29_Re: Cancelling a batch job...12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Tue, 6 Jun 2000 15:21:00 +0100362_iso-8859-1 Yes, we've done a cold start occasionally as well (yesterday as it happens). I've just looked in the V6 manuals about restart processing but can't really see that it buys me anything, ok, you can lose others updates but a normal STOP to let all others complete followed by a FORCE seems to cover that. Anyone care to comment about what you gain? [...] 17323 21 21_Wanna-be DBA training13_Oelkers, Phil25_phil.oelkers@EXPERIAN.COM30_Tue, 6 Jun 2000 07:21:00 -0700290_- > Ok say i wanna be a DBA - anybody got suggestions on training? > We're using DB2-UDB on an IBM SP. Right now I'm an application programmer (I suck data out of the database in new and creative ways). So I'm pretty comfortable with the SQL side of DB2. We've got two for real DBA's. [...] 17345 61 32_Connect problem (AIX to OS/390 )13_Pat Carbonaro19_pat2ldb2@EXCITE.COM30_Tue, 6 Jun 2000 07:20:45 -0700458_us-ascii We are having a problem establishing a TCP connection to DB2 V5 on OS/390 1.3 from AIX 4.3.3. We are using DB2 Connect EE V5.

We had a contractor come in and configure the connection to a "Test" DB2 subsytem. We've tried to configure a connection the "Production" subsytem. When trying to: "connect to dsnc user ub0pc" We are receiving: SQL30061N The database name or alias name "DSNC " was not found at the remote node. SQLSTATE=08004. [...] 17407 61 38_Advise needed on DB2 and RACF security13_Henry Fogwill17_fogwill.h@GHC.ORG30_Tue, 6 Jun 2000 07:35:03 -0700385_- We are a new DB2 shop (1 month old) and would like to implement external security right from the start.

We have all the RACF classes defined and all the exists assembled. I used the auth exit supplied in SYS1.SAMPLIB because the one that can with DB2 didn't do anything.

I tried to do a DISPLAY THREAD from TSO and failed because I do not have authority to do it. [...] 17469 45 29_Re: Cancelling a batch job...23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Tue, 6 Jun 2000 13:50:15 +0100353_iso-8859-1 Paul ...yes that is the boy. When I am not chasing my tail I will get stuck into reading about all these new bits and bobs.

How is V6 at your place .. stable?

Datasharing in our V5 production envrionment is going well .. at this rate I think I will be able to write a red book on it soon .. "SAP meets Datasharing" .. :-)) [...] 17515 186 29_Re: Cancelling a batch job...23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Tue, 6 Jun 2000 13:20:59 +0100494_iso-8859-1 Monica I had to perform a cold start in one of our QA environments a few weeks back .. as we had a complete loss of Active logs .. works a dream. My quesiton is that you can bring DB2 happy .. then go for a rolloback of the N objects involved in the original rollback .. this way the other n thousand objects are available to be used by other Apps .. i.e you do not wait for Db2 to rollback the one long running thread for 6 hours(as we had a few weeks ago on V5 in production) [...] 17702 47 34_DB2 V5 -904 Resource of all zero's12_Bruce Fuller17_BFuller@EBSCO.COM30_Tue, 6 Jun 2000 09:58:31 -0500708_- Hi everyone

We are running DB2 V5 with very high maintenance on OS/390.

This morning starting around 3:00 AM CST all of the users attempting to use an update transaction to our billto_customer table received the following:

DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00000000, TYPE OF RESOURCE 0000000 AND RESOURCE NAME DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXRUID SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -150 13172746 0 13226969 -975765504 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFF6A' X'00C9000A' X'00000000' X'00C9D3D9' X'C5D70000' X'00000000' SQL DIAGNOSTIC INFORMATION [...] 17750 74 23_Re: SEGSIZE 32 in DB2V413_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Tue, 6 Jun 2000 09:23:30 -0500378_iso-8859-1 Incase you have not done this already, look at the SDSF log. I would expect to see some extra information and then you can drill down on the IDCAMS error and follow that lead. Glenn

-----Original Message----- From: Pillay, Venkat (PCA) [mailto:venkat_pillay@ML.COM] Sent: Tuesday, June 06, 2000 9:00 AM To: DB2-L@RYCI.COM Subject: Re: SEGSIZE 32 in DB2V4 [...] 17825 61 38_Re: DB2 V5 -904 Resource of all zero's12_Ragam, Sai J18_Sai.Ragam@BNSF.COM30_Tue, 6 Jun 2000 10:12:08 -0500503_iso-8859-1 Did you do a display for locks and users on the db/ts?

-----Original Message----- From: Bruce Fuller [mailto:BFuller@EBSCO.COM] Sent: Tuesday, June 06, 2000 9:59 AM To: DB2-L@RYCI.COM Subject: DB2 V5 -904 Resource of all zero's



Hi everyone

We are running DB2 V5 with very high maintenance on OS/390.

This morning starting around 3:00 AM CST all of the users attempting to use an update transaction to our billto_customer table received the following: [...] 17887 92 36_Re: Connect problem (AIX to OS/390 )16_Michael McCarthy18_mmccarthy@DTCC.COM30_Tue, 6 Jun 2000 10:51:42 -0400539_us-ascii Pat,

When you catalog the TCPIP node on the DB2 Connect gateway, the IP address (or hostname) must match the IP address of the LPAR where the DB2/OS390 subsystem resides. Also the SERVER (port number) must match the port number in the DDF record of the boot strap dataset for the subsystem you are connecting to. After cataloging the node, dcs, and database directory entries on the gateway, I have found it necessary to do a db2stop and db2start in order for remote clients to connect successfully using the gateway. [...] 17980 110 28_Re: Stored procedure problem13_OConner, John26_John.OConner@COURTS.WA.GOV30_Tue, 6 Jun 2000 08:25:43 -0700527_iso-8859-1 Have you already CREATEd the global temporary table?

John O'Conner DB2 DBA Office of the administrator for the courts (360) 705-5210 Mailto:john.oconner@courts.wa.gov



-----Original Message----- From: Sven.Heidorn@CSN.SE [mailto:Sven.Heidorn@CSN.SE] Sent: Tuesday, June 06, 2000 7:13 AM To: DB2-L@RYCI.COM Subject: SV: Stored procedure problem



The procedure is created with NO WLM ENVIRONMENT and the column WLM_ENVIRONMENT in SYSIBM.SYSROUTINES is blank for this procedure. [...] 18091 133 36_Re: Connect problem (AIX to OS/390 )13_Pat Carbonaro19_pat2ldb2@EXCITE.COM30_Tue, 6 Jun 2000 08:34:54 -0700294_us-ascii Thanx for your response. Both DB2 subsystems are on the same 'node'/LPAR, so we believe that part is setup correctly. How do we setup the "SERVER" port number to match the DDF record in the BSDS, which has 5021 as the port number. I think that is the piece that we are missing !! [...] 18225 50 25_Re: Wanna-be DBA training15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Tue, 6 Jun 2000 10:40:01 -0500405_iso-8859-1 I would go to IDUG. It is a very well organized conference and very informational. Also, if you ever get a chance to go to one of Bonnie Baker's lectures, do it. She has a very good understanding of how DB2 works and is very good at explaining it. I recently went to her lectures in Chicago through IBM and I took 29 pages of notes. I have never taken so many notes in a class in my life. [...] 18276 122 28_SV: Stored procedure problem0_19_Sven.Heidorn@CSN.SE30_Tue, 6 Jun 2000 18:00:41 +0200718_ISO-8859-1 John,

This is not a CREATE global temp table, it's a DECLARE global temp table and the table is called something like SESSION.TABX. Also, there is no existing table with this name in this DB2 subsystem.

Sven

> -----Ursprungligt meddelande----- > Från: John.OConner@COURTS.WA.GOV [SMTP:John.OConner@COURTS.WA.GOV] > Skickat: den 6 juni 2000 17:34 > Till: DB2-L@RYCI.COM > Ämne: Re: Stored procedure problem > > Have you already CREATEd the global temporary table? > > John O'Conner > DB2 DBA > Office of the administrator for the courts > (360) 705-5210 > Mailto:john.oconner@courts.wa.gov > > > -----Original Message----- > From: Sven.Heidorn@CSN.SE [mailto:Sven.Heidorn@CSN.SE] > [...] 18399 110 18_Re: DSNTIAUL Doubt0_19_Tim.Lowe@STPAUL.COM30_Tue, 6 Jun 2000 10:56:59 -0500368_us-ascii Anshu, While I agree 100% with Peter about the documentation, I would also like to mention that the reason that you can do "other things" with the sample unload utility is because of the SQL option that was added to DSNTIAUL many years ago (I forget the release). Prior to that point, without the SQL option, the sample unload utility could only unload. [...] 18510 59 33_Re: DB2 dataset info from Catalog15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Tue, 6 Jun 2000 12:06:39 -0400484_iso-8859-1 Thanks everyone who responded to my questions

I have one more questions 1. Can I see the book DFSMS/MVS V1R5 in internet.

All I could get from IBM site is as follows. This is from the book DGT1C105.BOO D.7.4 IGGCSIRX IGGCSIRX is a REXX EXEC that uses CSI. Move this EXEC to a REXX EXEC library and ensure that the linkage library for IGGCSI00 is accessible by the TSO session. IGGCSIVS will run with any release of MVS/DFP or DFSMS/MVS supported by CSI. [...] 18570 161 36_Re: Connect problem (AIX to OS/390 )0_23_sjtoeniskoetter@AEP.COM30_Tue, 6 Jun 2000 12:09:26 -0400485_us-ascii Pat,

Although both DB2 subsystems are on the same LPAR, they are different nodes to DB2 Connect. You need to catalog another node for the production subsystem using port 5021. Then re-issue the catalog database command specifying the new node.

Steve







Pat Carbonaro on 06/06/2000 11:34:54 AM

Please respond to pat2ldb2@excite.com

To: DB2-L@RYCI.COM cc: (bcc: Steve J Toeniskoetter/OR1/AEPIN) [...] 18732 32 11_IO question11_Anshu Mohan20_anshu_garg@YAHOO.COM30_Tue, 6 Jun 2000 09:19:11 -0700417_us-ascii Hi !

Apart from RMF , is there any way to find out the 'write efficiency' of DB2 . i.e. the number of pages DB2 is writing per I/O on an average and the number of I/Os DB2 is doing ….say on a per second basis .

All the buffer pool tuning is based on the I/O efficiency but I wonder how we find that. We have Platinum detector installed. Is there a place in it I can find this information. [...] 18765 35 26_online reorg of index only0_26_mmetcalf@NOTES.STATE.NE.US30_Tue, 6 Jun 2000 11:21:26 -0500433_us-ascii Does anyone know where there are some detailed white papers or articles of the online reorg utility that I could reference - mainly for index only? We have encountered a log read error and are unsure as to why. We recently (Monday - Memorial Day) changed our DB2 archive logs from 7 to 3 datasets. On Tuesday we submitted an online reorg (shrlevel change) of an index only and the job abended with the following error: [...] 18801 51 15_Re: IO question11_rick creech18_ykcirc@HOTMAIL.COM28_Tue, 6 Jun 2000 09:33:37 PDT572_- You will be able to find that info in Platinum's Sub-system analyzer. hth, Rick Creech



>From: Anshu Mohan >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: IO question >Date: Tue, 6 Jun 2000 09:19:11 -0700 > >Hi ! > > Apart from RMF , is there any way to find out the >'write efficiency' of DB2 . i.e. the number of pages >DB2 is writing per I/O on an average and the number of >I/Os DB2 is doing ….say on a per second basis . > >All the buffer pool tuning is based on the I/O >efficiency [...] 18853 44 15_Re: IO question14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Tue, 6 Jun 2000 12:38:13 -0400514_ISO-8859-1 Anshu, You can't determine DB2 write efficiency from RMF. This can be easily determined from DB2 statistics data - divide pages written/write i/os. Calc the i/os per sec from stats - divide the number of i/os by the seconds in the stats report.

For buffer pool tuning you should look at the Buffer Pool Tool, since it's the only product that can predict the effect of tuning changes, and shows you how to properly group objects by access type and working set size. www.responsivesystems.com [...] 18898 141 41_Re: Audit trace or is there another way ?10_Beck, Norm18_Norm.Beck@NIKE.COM30_Tue, 6 Jun 2000 09:56:57 -0700595_iso-8859-1 Sanjay,

I can do this by using both Detector and Subsystem Analyzer. The Detector collection needs to include the exception collection for dynamic SQL which is accomplished through the initialize collection option from the Detector main menu. Myron was correct, it is Detector that collects the dynamic SQL

You can see that dynamic SQL is being collected by choosing View Type ==> X (for Exception) while in the DETECTOR Planname Summary Display - the resulting display should get you to the dynamic SQL statements (you still need to drill down to get to the SQL). [...] 19040 71 30_Re: online reorg of index only19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Tue, 6 Jun 2000 12:09:03 -0500490_iso-8859-1 Michelle, Is Copy for Indexes available for DB2 V5? Did the job complete successfully? Are you using dual active logs? The DSNJ106I msg. can simply indicate a problem was encountered reading one of the dual logs, but a read of the other log might have been successful. Did DBT.DBT2.LOGCOPY2.DS04 archive successfully? Was DBT.DBT2.LOGCOPY2.DS04 accidentally left in the BSDS? What is the status of all the logs? Are they all listed in the BSDS? Where all logs preformatted? [...] 19112 52 22_Re: Damaged tablespace41_=?iso-8859-1?Q?Cl=E1udio_Meluzzi_Mendes?=18_P000002@BEG.COM.BR30_Tue, 6 Jun 2000 14:14:13 -0300459_iso-8859-1 I had a similar problem after running a DSN1COPY (with some incorrect parameters) to recover data. In order to correct it the data was unloaded and re-loaded into a new tablespace. It might not be your case.

Claudio

-----Mensagem original----- De: Andy Wheeler Grupos de notícias: bit.listserv.db2-l Para: DB2-L@RYCI.COM Data: Tuesday, June 06, 2000 10:26 Assunto: Damaged tablespace [...] 19165 84 33_Re: DB2 dataset info from Catalog15_Robert Lawrence21_rlawrence@BOSCOVS.COM30_Tue, 6 Jun 2000 13:22:45 -0400461_us-ascii If you are asking where the sample resides it is in SYS1.SAMPLIB(IGGCSIRX). You will have to modify it for your own use. Read the documentation very carefully on how to retrieve different information. HTH Bob Lawrence DBA Boscov's Dept Stores Inc





-----Original Message----- From: Philip, Sibimon [SMTP:SPhilip@CSXLINES.COM] Sent: Tuesday, June 06, 2000 12:07 PM To: DB2-L@RYCI.COM Subject: Re: DB2 dataset info from Catalog [...] 19250 208 36_Re: Connect problem (AIX to OS/390 )13_Pat Carbonaro19_pat2ldb2@EXCITE.COM30_Tue, 6 Jun 2000 10:41:54 -0700631_us-ascii Steve, We followed your suggestion, all commands appeared to complete successfully, but, unfortunately, We still rec'd same error when issuing: connect ..... We did db2stop / db2start.

Thanx for the help, any further aid would be welcome.

Pat



On Tue, 6 Jun 2000 12:09:26 -0400, DB2 Data Base Discussion List wrote:

> Pat, > > Although both DB2 subsystems are on the same LPAR, they are different nodes to > DB2 Connect. You need to catalog another node for the production subsystem > using port 5021. Then re-issue the catalog database command specifying the new > node. > > Steve [...] 19459 212 38_Re: DB2 V5 -904 Resource of all zero's15_Stovall, Martin16_MStovall@USG.COM30_Tue, 6 Jun 2000 12:56:16 -0500517_iso-8859-1 What a coincidence, this morning a batch job went down on a -904 on a UPDATE statement. I checked the thread history and there were no Abnormal amount of locking going on and the job ran fine after being restarted. The following is the SQL ERROR captured by BMC's APPTUNE; again No -904 was logged to DB2PMSTR messages. We are version 5 at 9912 maintenance level. Does anyone have any ideas of what causes a -904 that the resource does not show up in the error message? Is this a bug to be reported?? [...] 19672 55 104_Re: UDB Version 6 on NT reading a table on DB2 OS/390 V5 (via DB2 Con nect V6 on AI) I get an ODBC error11_Roland Owen20_Roland.Owen@UBSW.COM30_Tue, 6 Jun 2000 12:40:49 -0500398_- I've just been checking the archives and wondered if there has been any further news on the nullid problem when running SQL from the Command Centre connecting to DB2 on OS/390.

I'm getting "SQL0332N There is no available conversion for the source code page target code page "1252". Reason code "1". SQLSTATE=57017", using the nullid function from DB2 Connect PE 6, to UDB for OS/390. [...] 19728 65 22_Re: Damaged tablespace15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Tue, 6 Jun 2000 13:21:08 -0500378_iso-8859-1 This is just a thought. Perhaps the access path picked in the first case was a tablespace scan and some information within the DB2 pages (i.e. pointers) was "confused". Perhaps the second query used an index so it could bypass the "confused" pointers.

Linda Billings Enterprise Systems Programmer Wisconsin Department of Administration Info-Tech Services [...] 19794 52 38_Re: DB2 V5 -904 Resource of all zero's15_Stovall, Martin16_MStovall@USG.COM30_Tue, 6 Jun 2000 13:21:57 -0500221_- Bruce, is your billto_customer table partitioned? Did the update change the partitioning key? It just so happens that the table involved in our problem fits This scenario, this is new functionality in version 5.

19847 125 33_Re: DB2 dataset info from Catalog12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Tue, 6 Jun 2000 21:35:50 +0200306_x-user-defined Hi, I've seen many times that people have no easy REXX-DB2 connection and to all those who don't have V5 or the REXX-DB2 interface coming with it I would like to put a small example of doing it with the "unload" program (DSNTIAUL). Taylor it as you like, use at your own risk, etc..... [...] 19973 161 33_Re: DB2 dataset info from Catalog11_Daryl Wells18_dwwell1@US.IBM.COM30_Tue, 6 Jun 2000 14:54:15 -0400540_us-ascii You could also use RX-SQL (which is unsupported but works).

------------------------------------------------------------------ From: Daryl Wells DB/DC Support IBM Global Services Tie Line 427-7487 Phone (813) 801-7487 Pager (800) 759-8888 pin 1366198 or http://www.skytel.com/Paging/index.html



Isaac Yassin @RYCI.COM> on 06/06/2000 03:35:50 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 20135 67 32_R: DB2 dataset info from Catalog10_Max Scarpa16_mocion@INWIND.IT30_Tue, 6 Jun 2000 22:26:38 +0200358_iso-8859-1 I have many REXX execs to extract formatted space infos from ICF CATALOGS and they are

useful to generate ALTER (and to generate reorg, copy etc JCL with correct spaces).

A verrrry simple REXX using QMF (DSQCIX call) is in DB2-l documents.

If you need more info contact me

Regards Max Scarpa System (?) and DB (?) Admin 20203 57 33_Re: DB2 dataset info from Catalog10_Max Scarpa16_mocion@INWIND.IT30_Tue, 6 Jun 2000 22:40:53 +0200209_iso-8859-1 Hi Isaac, have you never used DB2WWWX ? it's a verry simple way to execute SQL statements.

(you need Open Edition to uncompress pax files)...





Regards Max Scarpa

20261 22 49_Getting Data for Test environment from Production10_Feeny, Jim23_Jim_Feeny@COMPUWARE.COM30_Tue, 6 Jun 2000 16:51:49 -0400364_iso-8859-1 Linda:

You may also want to consider File-AID/RDX from Compuware. It automates the tasks required to gather the data you need for testing. You need only to specify the application relationships if your data does not use RI and then specify the selection criteria to get just what you want.

Please reply if you have further questions. [...] 20284 11 36_31 character table name in DB2/OS3904_Beth19_bdato@OMPUS.JNJ.COM30_Tue, 6 Jun 2000 17:12:00 -0500386_- Does anybody know when IBM is planning to match the 31 character table name of the other RDBMSs? Or has anybody found a way to go around this?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 20296 154 55_Immediate IMS and DB2 DBA Opportunity in Ft. Lauderdale20_Mark Nehmer - Ajilon27_florida.jobs@MINDSPRING.COM30_Tue, 6 Jun 2000 18:54:17 -0400646_iso-8859-1 Ajilon is the world's largest IT consulting company with 80 offices and over 15,000 employee consultants world-wide. Please check out our web site at www.ajilon.com

Ajilon is looking for 2 Senior Mainframe DBAs with strong DB2 and/or IMS Database Design and Administration experience for a position which we have immediately available in Fort Lauderdale, Florida. The successful consultant will design and implement and support several very large, new databases using DB2 6 and IMS 7. This is an off-site team of 4 Senior DBAs. Two of those positions have already been secured by long-time candidates of mine who have been [...] 20451 34 44_Version 5 COPY Utility CHANGELIMIT parameter0_20_bjnigh@HOUSEHOLD.COM30_Tue, 6 Jun 2000 16:25:08 -0700568_us-ascii I currently setting up a new image copy job for a DB2 application that wants to make daily incremental image copies using GDG datasets. Is there a problem if you make an incremental image copy of a DB2 tablespace that has not changed since the last image copy was taken? In the DB2 Version 5 Utility Guide and Reference, there is a reference to problems with this approach and that the CHANGELIMIT parameter should be used to determine if the pages have changed since the last image copy before taking a new copy. The manual recommends adding a step for [...] 20486 71 24_Opportunity in Charlotte12_Wallace Shah21_upajobs@BELLSOUTH.NET30_Tue, 6 Jun 2000 20:28:05 -0700548_iso-8859-1 Universal Placement Agency currently has an opportunity for a DB2 DBA in Charlotte NC. This opportunity is with one of the largest retail companies in the area. We would love to network with anyone who could be interested in such an opportunity. The same company is also in need of a Systems/Data Architect who has case tools experience. Please feel free to contact us at the number listed below or at our email address upajobs@bellsouth.net. Thanks for your time and we hope to hear from some talented DB2 professionals very soon. [...] 20558 47 33_Re: DB2 dataset info from Catalog14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 7 Jun 2000 12:06:30 +1000555_iso-8859-1 http://www.s390.ibm.com/ftp/books/os390/pdf/dgt1c105.pdf Read the whole of Appendix D.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: Philip, Sibimon [mailto:SPhilip@CSXLINES.COM] Sent: Wednesday, June 07, 2000 2:07 AM To: DB2-L@RYCI.COM Subject: Re: DB2 dataset info from Catalog



Thanks everyone who responded to my questions

I have one more questions 1. Can I see the book DFSMS/MVS V1R5 in internet. [...] 20606 25 25_ONline backup for DB2 UDB25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG30_Wed, 7 Jun 2000 10:44:53 +0800401_us-ascii Hi,

Not sure if the posting reached you all earlier. Apologise if this is a repost.

Does DB2 UDB have an online backup utility. Also how does DB2 UDB implement point in time recovery similar to DB2 on os390. I am more looking for a solution using native DB2 product. If not supported on native (base db2) what is the additional software/hardware that need to support this. [...] 20632 46 40_Re: 31 character table name in DB2/OS39014_Richard Yevich21_ryevich@ATTGLOBAL.NET30_Tue, 6 Jun 2000 23:05:23 -0400345_iso-8859-1 According to IBM, look for it in the future, on the OS/390, after V7. Which could mean V8 or V9 or V10. I understand, that it is not simple but a 20+ man year effort (actually that is small if you think about all the implications to a highly productive 24x7 product). On the good side, I also understand that it is being worked. [...] 20679 82 48_Re: Version 5 COPY Utility CHANGELIMIT parameter7_Sanjeev19_Sanjeev@INTERRA.COM30_Wed, 7 Jun 2000 10:11:43 +0530477_iso-8859-1 Hi, CHANGELIMIT Parameters performs it functions on the basis of given values and the probable results would be i) No Image copy (if change limit value doesn't meet) or ii) Incremental Image copy or iii) Full Image copy.

I would not prefer to take the report only if i need to standardize the things with the proper knowledge of the growth of data in my installation.Rather , i would prefer to code the CHANGELIMIT values depending on my growth of data. [...] 20762 64 30_Re: online reorg of index only7_Sanjeev19_Sanjeev@INTERRA.COM30_Wed, 7 Jun 2000 10:26:47 +0530487_iso-8859-1 Hi Michelle, I would reccommend you to run DSNJU004 on your BSDS and look at what is the problem.It looks to me it is inconsistent with the log copies.Otherwise, there should not be any problem with reorg on tuesday if the things are changes on monday. As i understand, Reorg for index only apply logs starting from SYSLGRNX to BSDS and to respective log copies. You can also send the output of DSNJU004 and details about your log copies(active and archive) in the list. [...] 20827 36 42_Reorganizing all Partitions simulatneously15_Bergeson, Loren24_LBergeson@IDAHOPOWER.COM30_Tue, 6 Jun 2000 18:33:57 -0600419_iso-8859-1 I have a tablespace that's in 8 partitions. I would like to kick off 8 reorg jobs simultaneously -- each job will reorg one partition. I'm confused as to whether or not I'll need to also submit a job to reorg the non-partitioned indexes on the tablespace. In the DB2 V5 Utility Guide, it says under the TABLESPACE option description: "If you reorganize a table space, its indexes are also reorganized." [...] 20864 129 46_Re: Reorganizing all Partitions simulatneously0_18_mebert@AMADEUS.NET30_Wed, 7 Jun 2000 09:33:43 +0100552_us-ascii This is a recurring topic; check the archives. To summarise:

- A full TS Reorg will reorg the entire TS and all indexes, whether partitioning or NPI. With OLR, you will have a long SWITCH phase but no BUILD2 phase. - A partition level Reorg (or Load, for that matter) will reorg (load) the TS partition and the corresponding cluster index partition. The NPI(s) will NOT be reorged, but the corresponding Logical Partition will be updated using a SQL-like process (BUILD2 phase in OLR). This is a very slow & CPU intensive process. [...] 20994 64 54_Update re BSDS in a DS environment an modifications of23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Wed, 7 Jun 2000 08:35:46 +0100547_iso-8859-1 Guys as promised last week here is what I found with regard to the BSDS in DS environment.

We needed to change the group name in the BSDS of two members of a Datasharing group. the groupname is added to a BSDS at DS implementation time using, you guessed it, DSNJU003. Well there is no documentation on the DS commands for the DSNJU003 module .. you get what you get from the CLIST .. anyway we do what are called system refreshes .. one way of doing these refreshes is to implement a DR recovery in the destination LPAR of [...] 21059 142 42_Re: Advise needed on DB2 and RACF security13_Adrian Talbot29_Adrian.Talbot@BANKWEST.COM.AU30_Wed, 7 Jun 2000 16:10:47 +0800436_us-ascii

Hi,

I believe you have hit the WORKING AS DESIGNED response. There is also a hit in IBMLINK for this problem but I can't remember the number.

When you externalise DB2 Security to RACF there are several commands which although RACF has rules for and states that these rules will give you required access, RACF defaults back to Native DB2 security when checking them. Hence they need Granting via DB2. [...] 21202 32 15_4 GB partitions15_Shaun Z Lombard36_Shaun.Z.Lombard@TRANSPORT.QLD.GOV.AU30_Wed, 7 Jun 2000 15:02:26 +1000666_us-ascii Hi All,

Anybody out there have a partitioned tablespace (with less than 17 parts) , with a partition that has grown above 2 GB ?

I assume a second dataset using the primary quantity is created, but what will the dataset be called ?

Thanks

Shaun







************************************************************* Opinions contained in this e-mail do not necessarily reflect the opinions of the Queensland Department of Main Roads, or of Queensland Transport. If you have received this electronic mail message in error, please immediately notify the sender and delete the message from your computer. [...] 21235 133 48_Re: Version 5 COPY Utility CHANGELIMIT parameter0_18_mebert@AMADEUS.NET30_Wed, 7 Jun 2000 10:09:04 +0100620_us-ascii In your JCL, you will probably allocate the Image copy file using something like

//SYSCOPY DD DSN=dsname(+1),DISP=(,CATLG,DELETE),SPACE=(CYL,(x,y),RLSE)

where the x SPACE parameter is as large as a full IC (to prevent B37s if COPY decides to take a full IC).

If DB2 determines that no pages have changed, or that the lower CHANGELIMIT is not exceeded, it won't take an IC and won't place an entry in SYSCOPY. However, the IC dataset will be allocated & catalogued by the DD card above - this is not under DB2s control. What's worse, because there was no write operation, you will have [...] 21369 113 19_Re: 4 GB partitions0_18_mebert@AMADEUS.NET30_Wed, 7 Jun 2000 10:15:05 +0100369_us-ascii Non-large Partitioned TSs with up to 16 partitions, or large TSs with any number of partitions, can have partition sizes of up to 4 GB (see Utility Guide, p. X-4 or SQL Ref, p. 466). A partition will never consist of more than one file. (DB2 for OS/390)

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...] 21483 73 19_Re: 4 GB partitions14_David Williams25_David-O.Williams@UBSW.COM30_Wed, 7 Jun 2000 09:39:33 +0100572_us-ascii Shaun

To go over 2GB the tablespace must be defined as large. A large partition can grow to 4GB. There will not be a second dataset.



Dave Williams UBS Warburg





______________________________ Reply Separator _________________________________ Subject: 4 GB partitions Author: Shaun.Z.Lombard (Shaun.Z.Lombard@TRANSPORT.QLD.GOV.AU) at unix,mime Date: 07/06/00 06:02



Hi All,

Anybody out there have a partitioned tablespace (with less than 17 parts) , with a partition that has grown above 2 GB ? [...] 21557 96 58_Re: Update re BSDS in a DS environment an modifications of13_Adrian Savory24_adrian.savory@ZURICH.COM30_Wed, 7 Jun 2000 10:12:06 +0100464_us-ascii Thanks Les, useful info.

What did your IBM DS specialist (JC) have to say about it? Has it been raised with IBM as a formal enhancement request?

Adrian







"Pendlebury-Bowe, Leslie" cc: (bcc: Adrian Savory/ZI/England/Zurich) Subject: Update re BSDS in a DS environment an 07/06/00 08:35 modifications of Please respond to DB2 Data Base Discussion List [...] 21654 73 22_Re: Damaged tablespace14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU30_Wed, 7 Jun 2000 15:59:44 +1000378_us-ascii Andy, After you found that the query worked by adding a where clause to use an index, did you again try to access the table without a where clause ? If not, sounds to me awfully like your tablespace dataset had been migrated by HSM. Your 'hung' job was cancelled, the HSM process had independently recalled your dataset, and then all appeared OK for the next run. [...] 21728 115 58_Re: Update re BSDS in a DS environment an modifications of23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Wed, 7 Jun 2000 10:41:17 +0100475_iso-8859-1 ..sort of. will keep the list posted as I get updates.



Leslie

-----Original Message----- From: Adrian Savory [mailto:adrian.savory@ZURICH.COM] Sent: Wednesday, June 07, 2000 10:12 AM To: DB2-L@RYCI.COM Subject: Re: Update re BSDS in a DS environment an modifications of



Thanks Les, useful info.

What did your IBM DS specialist (JC) have to say about it? Has it been raised with IBM as a formal enhancement request? [...] 21844 32 40_Re: 31 character table name in DB2/OS39019_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 7 Jun 2000 06:28:14 -0500568_iso-8859-1 The rumor is that it will be at least V8 or beyond . . . I thought it was going to be 30 bytes?

Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, [...] 21877 63 19_Contention problems13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET30_Wed, 7 Jun 2000 08:53:39 -0400447_us-ascii I have an application in which data entered in two areas. The first area consists of operators entering from mail, batching transactions 50 per batch. After the 50, the batch is totaled and balanced, then the 50 added to a database. The primary key consists of year-month-day-sequence-num-check digit. The sequence number is retrieved from a systemcontrol table, then one added, then updated. So far no problems, works like a champ. [...] 21941 208 48_Re: Version 5 COPY Utility CHANGELIMIT parameter15_Robert Lawrence21_rlawrence@BOSCOVS.COM30_Wed, 7 Jun 2000 09:12:57 -0400585_us-ascii I believe if you add dcb=dsorg=ps then rlse should release space. I have just started to do incremental ic's myself based on changelimit and yes it does add steps. I use different naming standards for full, incremental and mergecopy for incremental copies therefore I do not use same GDG base for full as incremental. Also if you are doing your backups this way and going to non-dasd(tape,cart,vts,etc) where you code label=fseq be aware of missing files if steps are not executed based on return codes. IF you need a sample of JCL that I created where I use tape output [...] 22150 65 59_Re: Immediate IMS and DB2 DBA Opportunity in Ft. Lauderdale19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US28_Wed, 7 Jun 2000 09:13:22 EDT626_us-ascii Mark,

Must say that you have a great attitude towards your prospective employees. Seems that you will be doing them a BIG favor by offering this great opportunity to keep them from starving to death. My sincerest best wishes to you in your search.

Rajeev P Dhanawade Phone : 302-577-4659 Ext. 221 ++ Fax : 302-577-4454 ++ ------------- Original Text From: "Mark Nehmer - Ajilon" , on 06/06/2000 6:54 PM: Ajilon is the world's largest IT consulting company with 80 offices and over 15,000 employee consultants world-wide. Please check out our web site at www.ajilon.com [...] 22216 110 48_Re: Version 5 COPY Utility CHANGELIMIT parameter0_18_mebert@AMADEUS.NET30_Wed, 7 Jun 2000 15:35:36 +0100340_us-ascii Unfortunately it doesn't release space (try it using IEFBR14). The JCL Reference has this to say:

RLSE: Requests that space allocated to an output data set, but not used, is to be released when the data set is closed. Unused space is released only if the data set is open for output and the last operation was a write. [...] 22327 198 48_Re: Version 5 COPY Utility CHANGELIMIT parameter11_Kwan, James18_James_Kwan@BMC.COM30_Wed, 7 Jun 2000 09:10:31 -0500470_ISO-8859-1 Michael,

If you maintain say 5 generations of GDG dataset and if you delete the gdg 5 times. Would you lose all you image copies?

James Kwan

-----Original Message----- From: mebert@AMADEUS.NET [mailto:mebert@AMADEUS.NET] Sent: Wednesday, June 07, 2000 4:09 AM To: DB2-L@RYCI.COM Subject: Re: Version 5 COPY Utility CHANGELIMIT parameter



In your JCL, you will probably allocate the Image copy file using something like [...] 22526 72 40_Backup strategy for 1000s of tablespaces13_Adrian Savory24_adrian.savory@ZURICH.COM30_Wed, 7 Jun 2000 15:09:56 +0100474_us-ascii Hi folks,

We have implemented an SAP R/3 system, release 4.5b. We have an issue with backups, as with this release SAP have decided to isolate nearly all objects in their own tablespace. In our environment this equates to nearly 7,500 tablespaces and nearly 20,000 indexes! These objects are for the whole of SAP, yet in this landscape we are currently only using the HR module. Therefore a large majority of the tables are empty, or at least not used. [...] 22599 134 59_Re: Immediate IMS and DB2 DBA Opportunity in Ft. Lauderdale16_Robert Blackwell31_robert.blackwell@PIEDMONTNG.COM30_Wed, 7 Jun 2000 10:15:07 -0400853_us-ascii





Would you really want to hire anyone gullible enough to believe this?













Mark Nehmer - Ajilon on 06/06/2000 06:54:17 PM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc:





Subject: Immediate IMS and DB2 DBA Opportunity in Ft. Lauderdale















This is a long term project that we are preventing from going off-shore. The only way that was able to work was by taking a low margin and paying salaries lower than we were able to just 18 months ago (when the last mainframe DBA opening was available in this market). It is the same situation as [...] 22734 178 48_Re: Version 5 COPY Utility CHANGELIMIT parameter15_Robert Lawrence21_rlawrence@BOSCOVS.COM30_Wed, 7 Jun 2000 10:25:25 -0400503_us-ascii You are correct about space release, I forgot that was to solve another problem. If you can tolerate possibility of not having enough space on volume for dataset(since you have software to allocate on another volume when needs to expand with no space on first volume) then space=cyl,(0,x),rlse) will not allocate any space if not opened. Also I forgot to mention I do a COPY ..... CHANGELIMIT(x) REPORTONLY step to determine type of copy to perform based on return code in following steps [...] 22913 111 44_Re: Backup strategy for 1000s of tablespaces0_22_Rohn.Solecki@MTS.MB.CA30_Wed, 7 Jun 2000 09:33:19 -0500468_us-ascii " ... do the math ..." sure, why not ...

27,000 files * 3 sec/file = 81,000 sec 81,000 sec / 60 sec/min = 1,350 min ... hmmm that number is close to 1440, which sounds familiar ... 1,350 min / 60 min/hr = 22.5 hours ... ... ... 10 hours sure sounds better than 23 hours! (that reminds me, during an OS conversion we had a job that had to run daily at the start of the batch stream. The first version of the converted code ran for 28 hrs ... oops) [...] 23025 104 44_Re: Backup strategy for 1000s of tablespaces12_craig patton21_prgpatton@HOTMAIL.COM28_Wed, 7 Jun 2000 07:48:04 PDT537_- Adrian,

I don't know if this will help, but if you are only using 1 module of the ERP, why not move those 'unused' tables to a minimum of segmented tablespaces? This would reduce the datasets required for IC. Also, if doing snap shot, why copy the index dsns, they can be recovered from the TS data (index recovery timings and RI NOT considered). Also, if you have DASD Manager from BMC (I am not sure if that is the official name) can't you run that and have IT create copies based on whether or not the data was updated? [...] 23130 134 44_Re: Backup strategy for 1000s of tablespaces12_craig patton21_prgpatton@HOTMAIL.COM28_Wed, 7 Jun 2000 08:05:52 PDT300_- Question # 2: We use A VTS (Virtual Tape System) that does EXACTLY what you request. The system copies to DASD, then offloads the files to TAPE at a later time. We use MAGSTAR as the tape medium that has the VTS attached.

HTH Craig Patton Db2 DBA Specializing in PeopleSoft Performance [...] 23265 10 23_Re: Contention problems10_Bill Brown25_bill_brown@SPRINGMAIL.COM30_Wed, 7 Jun 2000 11:06:39 -0400291_- For your control table where the sequence number is when you use a singleton select DB2 doesn't know it you are going to update. Your best bet is to create a cursor with the for update of clause, open the cursor, fetch, update. In this way you willget your lock and all will be good. [...] 23276 60 23_Re: Contention problems19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 7 Jun 2000 09:38:13 -0500609_iso-8859-1 Jimmy, Have you considered locking the SeqNumTable in mode exclusive while you get the next number? This might timeout others but they can code a loop to retry.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your [...] 23337 24 12_UDF Question8_Kurian B23_Bejoy_Kurian@MAY-CO.COM30_Wed, 7 Jun 2000 10:12:28 -0500324_us-ascii Hi All,

Can somebody share the experiences to create User-Defined Functions? We would like to know where to keep the load modules? Do we need a separate lib and to step lib in DB2 started task? Is this same library can be used for stored proc's as well?

We are running with DB2 V6 and OS390 2.6. [...] 23362 133 44_Re: Backup strategy for 1000s of tablespaces19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Wed, 7 Jun 2000 10:08:06 -0500583_iso-8859-1 Adrian, I'm not "SAP'd", but it seems to me that SAP should have the capability to install only those tables needed for the function(s) you are using. If not, I would ask them to identify them.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at [...] 23496 142 44_Re: Backup strategy for 1000s of tablespaces12_Allen, Colin31_Colin.Allen@ABBEYNATIONAL.CO.UK30_Wed, 7 Jun 2000 16:18:00 +0100392_iso-8859-1 We use volume level copies for an application and have recovered using both full pack restores and individual datasets followed by recover LOGONLY. As long as you can afford the downtime for the volume dumps its fast and efficient. You may want to decide if you want to back up your indexes or not. If not, you need them on separate volumes and have recover index jobs ready. [...] 23639 175 44_Re: Backup strategy for 1000s of tablespaces15_Robert Lawrence21_rlawrence@BOSCOVS.COM30_Wed, 7 Jun 2000 11:25:59 -0400365_us-ascii If you are using magstar directly I think it originaly had problems with very small datasets with repositioning. There maybe some changes(ptfs or engineering changes I do not know, I am not that conversant with its internals) to rectify this. I had the same problem a couple years ago with PEOPLESOFT when converted from CART drive to MAGT drive. HTH [...] 23815 93 23_Re: Contention problems17_Massimo Biancucci19_mbiancucci@DEBIS.IT30_Wed, 7 Jun 2000 17:13:12 +0100553_us-ascii



If I'm not misunderstanding your question there will be at least 3 way to solve the problem: (I don't know well what's your environment, OS390, NT etc.)

Update tab_seq_nbr set seq_nbr = seq_nbr + 1 where ........ (which locks the records) Declare crs1 cursor for select seq_nbr from tab_seq_nbr where ....... for update of seq_nbr and after Open crs1 Fetch crs1 (which locks the records) Update .... where current of crs1 Close crs1 Select seq_nbr from tab_seq_nbr where .......... with RR (which locks the records) [...] 23909 74 80_DB2 Connect problem - Enterprise edition - SOCKET=READ RC= 1121 R eason=12EE029116_Galluzzo, Lori A21_lori.galluzzo@EDS.COM30_Wed, 7 Jun 2000 11:26:32 -0400654_ISO-8859-1 Dear DB2 LISTSERV, Here is an issue from a team member that I hope someone can shed light on. Thanks in advance,

Lori Ann Galluzzo EDS - DARTT/MetaVance Infrastructure team phone *: 717-975-5482 beeper *: 1-717-913-1361 email *: mailto:lori.galluzzo@eds.com

I have run into a problem that I hope someone may be able to help me out with. It involves DB2 Connect. Currently, I am a DBA working with programmers supporting a Visual Basic Application that runs fine using DB2 Connect 5.2 Personal Edition. However, when this application is run using DB2 Connect 6.1 Enterprise Edition, problems are encountered. The application [...] 23984 253 48_Re: Version 5 COPY Utility CHANGELIMIT parameter15_Robert Lawrence21_rlawrence@BOSCOVS.COM30_Wed, 7 Jun 2000 11:41:45 -0400487_us-ascii You would only lose the oldest when you create a new GDG with no empty slots. If you then delete the just created GDG that slot is then available for the next GDG to be created which if then deleted will again be available for use. The only time you would lose a GDG is if the GDG index is full. for example using limit 3 JOB 1 creates GDG 1 JOB 2 creates GDG 2 JOB 3 creates GDG 3 JOB 4 creates GDG 4 ---> GDG 1 rolls off index since limit is exceeded deletes GDG 4 based [...] 24238 50 16_Re: UDF Question13_OConner, John26_John.OConner@COURTS.WA.GOV30_Wed, 7 Jun 2000 08:52:23 -0700560_iso-8859-1 We are still in the beginning stages of using Stored Procedures and UDFs, but we will probably use the same lib for both. When I was experimenting with UDFs I discovered something interesting. We have a stored procedure (it calls an assembly module that returnes a unique pseudo random number that we use as an ID in some of our tables). To create a UDF to accomplish the same thing, all I did was CREATE FUNCTION ... and specify the SAME load module (and the same WLM environment, etc.) that the stored procedure uses. That is, the SP and the [...] 24289 94 23_Re: Contention problems13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET30_Wed, 7 Jun 2000 12:09:06 -0400566_us-ascii Thanks,,,that was what I was looking at, but may consider opening cursor... even though only one row is involved..

"DAVIS, RICK (SBCSI)" wrote:

> Jimmy, > Have you considered locking the SeqNumTable in mode exclusive while > you get the next number? This might timeout others but they can code a loop > to retry. > > HTH, > Rick Davis > > "This e-mail and any files transmitted with it are the property of SBC, > are confidential, and are intended solely for the use of the individual > or entity to whom this e-mail is addressed. If you [...] 24384 163 44_Re: Backup strategy for 1000s of tablespaces23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Wed, 7 Jun 2000 17:01:00 +0100386_iso-8859-1 in V6 you can do a DEFER create ...

we are going V6 before we go 4.6C ...

this will help in the backups - i.e no backup as they do not exist.

Les

-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:RD8246@MOMAIL.SBC.COM] Sent: Wednesday, June 07, 2000 4:08 PM To: DB2-L@RYCI.COM Subject: Re: Backup strategy for 1000s of tablespaces [...] 24548 98 23_Re: Contention problems13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET30_Wed, 7 Jun 2000 12:10:40 -0400590_us-ascii Thanks,,,,we are mainframe db2 version 5. The cursor will probably work best...

Massimo Biancucci wrote:

> If I'm not misunderstanding your question there will be at least 3 way to solve > the problem: (I don't know well what's your environment, OS390, NT etc.) > > Update tab_seq_nbr set seq_nbr = seq_nbr + 1 where ........ (which locks > the records) > Declare crs1 cursor for select seq_nbr from tab_seq_nbr where ....... for > update of seq_nbr > and after > Open crs1 > Fetch crs1 (which locks the records) > Update .... where current of crs1 > Close crs1 [...] 24647 149 44_Re: Backup strategy for 1000s of tablespaces9_Dan Vogel19_dan_vogel@YAHOO.COM30_Wed, 7 Jun 2000 09:35:52 -0700553_us-ascii We're running PeopleSoft which has a similar issue. We used CM/Pilot from BMC to move all the empty tables spaces into a seperate database which we back up monthly. The CM/Pilot script is one of the samples in the documentation. We monitor the empty tablespace to make sure they don't start using them. If that happens we move the tablespace from the empty database to one that is backed up daily. The next thing we did is move the image copies to DASD and then used a second step to sweep the IC datasets to tape. Writing a large quanity [...] 24797 66 28_Re: IFCID for SQL statements0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Wed, 7 Jun 2000 11:40:19 -0500392_us-ascii Kurian - I am not sure if you are just looking at static SQL, dynamic SQL or both. We are doing some tracking of dynamic SQL using IFCID 22 and 63. IFCID 22 will track the plan table information that would be generated by dynamic SQL (so we can track which indexes are being used by dynamic SQL) and IFCID 63 will track the first 5000 bytes of the actual SQL that is being run. [...] 24864 50 33_Re: DB2 dataset info from Catalog12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Wed, 7 Jun 2000 19:49:37 +0200541_x-user-defined Hi Max, I'm using our homegrown REXX-DB2 access, simple, faster & cheaper then any other tool I've met by now (many). I can't put it on the list as it's proprietary (some assembler code that moves chunks of memory instead of using the regular REXX routines) - if you look at the DSN1COPY REXX I put on the document side of the list, you'll see it mentioned over there. We have it even working under USS now as cgi with html as well as DB2 calls, replaced batch reports, the user was so pleased with the demo that we were [...] 24915 90 22_Re: Damaged tablespace12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Wed, 7 Jun 2000 19:54:42 +0200542_x-user-defined Hi, Another "far away" idea. I once saw a table in simple tablespace where the application used to do daily "delete from table" (all records) and then many inserts. The tablespace was never reorganized. After 4 years like that a new program on the table accessed it not by it's index but by TS-SCAN and it took eons. A reorg solved the problem. The point was that there were many pages with deleted data and as it was not segmented and not reorged then the number of pages went up. Maybe it's what you have?? Isaac Yassin [...] 25006 56 59_Re: Immediate IMS and DB2 DBA Opportunity in Ft. Lauderdale12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Wed, 7 Jun 2000 19:59:07 +0200577_x-user-defined Hi, If it's 75k$ for each DBMS then it may be .... Isaac Yassin



Robert Blackwell wrote: > > Would you really want to hire anyone gullible enough to believe this? > > Mark Nehmer - Ajilon on 06/06/2000 06:54:17 PM > > Please respond to DB2 Data Base Discussion List > > To: DB2-L@RYCI.COM > > cc: > > Subject: Immediate IMS and DB2 DBA Opportunity in Ft. > Lauderdale > > This is a long term project that we are preventing from going off-shore. The > only way that was able to work was by taking [...] 25063 46 43_Advice on Order of keys in clustering index12_Fertaki Gina20_GFertaki@EUROBANK.GR30_Wed, 7 Jun 2000 20:19:02 +0300573_ISO-8859-7 Hello list!! We have a design problem and your opinions would be greatly appreciated... We have several tables with the columns of bank code, branch code , accountno and several other columns depending on the table. Since the application is developed by a third party SW house we have noticed that some tables have the order of the keys of the clustering index with the sequence of bank code, branch code and account and others with the sequence of branch code, account and bank code. Since now there is only one value for the bank code (one bank) it seems [...] 25110 137 44_Re: Backup strategy for 1000s of tablespaces0_20_John_Lendman@FPL.COM30_Wed, 7 Jun 2000 13:28:30 -0400309_us-ascii Hi Adrian,

We have SAP 4.5B installed and in production. We have allocated 180 GB, but we are only using about 90GB. Let me outline my backup strategy, which by the way was not easy to come to, but after much testing I now believe that it works very well and produces the desire results. [...] 25248 185 41_Re: Audit trace or is there another way ?23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Wed, 7 Jun 2000 13:45:16 -0400459_iso-8859-1 Norm,

Thanks for your insight. Unfortunately we don't have sub-system analyzer. Looking at each entry at the Exception SQL Planname Summary Panel in detector and digging down to each SQL to determine if it goes against the table I am interested in, is too tedious. What I am looking for, is a neat daily report on access by corrid/authid on all the tables in a database. Seems me to me that 'Tracing' is the most convenient way to go.. [...] 25434 28 48_OS/390 RRS - Must Logger Datsets be SMS Managed?15_Jeffery A Price17_jprice@IPALCO.COM30_Wed, 7 Jun 2000 12:53:50 -0500533_us-ascii Hello All,

I was curious... After sifting through the redbook 'Getting Started with DB2 Stored Procedures - Give them a Call Through the Network', I was left in the dark about some SMS issues. Specifically, in section 3.14.1.1 (defining RRS Logstreams to DASD), there is a great example of how to define RRS Logstreams to DASD, but it is not mentioned if the Log Datasets must be SMS managed. Can anyone shed some light on this subject - I am having a hard time finding adequate documentation pertaining to RRS. [...] 25463 91 28_Re: IFCID for SQL statements8_Kurian B23_Bejoy_Kurian@MAY-CO.COM30_Wed, 7 Jun 2000 13:04:32 -0500408_us-ascii Larry, Thanks for the info. I am looking for both static and dynamic. Basically i am looking for the accounting information for each sql statements in a plan/program or a dynamic SQL. I hope IFCID 63 may be helpful to me. Thanks bejoy





From: L-IS.Kirkpatrick@MUTUALOFOMAHA.COM on 06/07/2000 11:40 AM

Please respond to DB2 Data Base Discussion List [...] 25555 133 28_Re: IFCID for SQL statements20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 7 Jun 2000 14:09:58 -0400411_us-ascii IFCID 63 is just pure text. If you are looking for accounting info, I guess you mean you want elapsed time and CPU time per SQL. Then your trace should have TDATA(CPU) to attach CPU header in your trace and then subtract end-of-SQL TIME to start-of-sql TIME to get this info. I don't know about DB2PM but I guess it should give you that info. You need PERFORMANCE TRACE CLASS(3) for this purpose. [...] 25689 129 28_Re: IFCID for SQL statements9_Fox, Dave16_Dave_Fox@BMC.COM30_Wed, 7 Jun 2000 13:22:28 -0500512_ISO-8859-1 Pardon the shameless vendor plug, but BMC's Apptune for DB2 provides SQL-level accounting information for both static and dynamic SQL without incurring trace overhead. Product information is available at: http://www.bmc.com/products/proddocview.html?id=2006&r=Americas&l=en

Regards, Dave Fox BMC Software, Inc.

-----Original Message----- From: Kurian B [mailto:Bejoy_Kurian@MAY-CO.COM] Sent: Wednesday, June 07, 2000 1:05 PM To: DB2-L@RYCI.COM Subject: Re: IFCID for SQL statements [...] 25819 20 13_Quiesce Point0_20_bjnigh@HOUSEHOLD.COM30_Wed, 7 Jun 2000 11:41:44 -0700439_us-ascii I have been using the Platinum utilities to make image copies over the past 3 years but want to use the IBM utilities for a new application. Do I need to put a step into my JCL to run the quiesce utility before taking the image copy? Platinum issues the quiesce as part of the image copy job. Do I need a quiesce point before and after taking the image copy? Does it matter if I am taking an image copy with SHRLEVEL CHANGE? [...] 25840 49 17_Re: Quiesce Point0_20_John_Lendman@FPL.COM30_Wed, 7 Jun 2000 14:52:47 -0400692_us-ascii With SHARE level CHANGE you do not need to take a Quiesce point. You need to logs to recover any tablespace. John







bjnigh@HOUSEHOLD.COM@RYCI.COM> on 06/07/2000 02:41:44 PM

Please respond to "DB2 Data Base Discussion List"

Sent by: "DB2 Data Base Discussion List"



To: DB2-L@RYCI.COM cc:

Subject: Quiesce Point



I have been using the Platinum utilities to make image copies over the past 3 years but want to use the IBM utilities for a new application. Do I need to put a step into my JCL to run the quiesce utility before taking the image copy? Platinum issues the [...] 25890 95 47_Re: Advice on Order of keys in clustering index11_Suresh Sane21_data_arch@HOTMAIL.COM28_Wed, 7 Jun 2000 13:59:13 CDT287_- Gina: Here is my take on the questions you have posed: 1. Choice of clustering index - The carddinality of columns is important but not the sole reason to determine the order. The question is: are you likely to retrive all branches for a bank + all accounts for a bank and branch [...] 25986 47 17_Re: Quiesce Point20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 7 Jun 2000 15:01:09 -0400477_us-ascii Usually with SHRLEVEL CHANGE it is advisable to take QUIESCE point after imagecopy because SHRLEVEL change may copy uncommitted data (which could have been rolled back after imagecopy). QUIESCE provides you a point where your data is consistent.

BMC, PLANTINUM or others may take QUIESCE before because they some times read underlying VSAM instead of DB2 buffers and therefore it may be necessary for them to take QUIESCE before COPY to flush the buffers. [...] 26034 81 47_Re: Advice on Order of keys in clustering index14_John P Shipley24_jpshiple@DUKE-ENERGY.COM30_Wed, 7 Jun 2000 15:34:16 -0400597_us-ascii If you are usually supplying the account number on the queries and account number is random then you might consider not only clustering all tables on the account number (highest cardinality key) but also partitioning the tables as well to take advantage of parallelism. You can always add global indexes on the other columns as necessary if only a few cases of transaction processing don't use the account number. I would suppect that many of the transactions use the account number supplied and if the other info is also provided then thats ok too. I would suspect that large batch [...] 26116 78 17_Re: Quiesce Point12_craig patton21_prgpatton@HOTMAIL.COM28_Wed, 7 Jun 2000 12:41:42 PDT568_- It is NOT REQUIRED to take the quiesce point with IBM as IBM copy reads the buffers with BOTH shrlevel change and reference. I disagree with Pillay on the quiesce after shrlevel change as in order to perform the quiesce, db2 must get LOCK the tablespace (or partition), and the reason for the shrlevel change is to be able to copy the tablespace without interrupting concurrent updates being performed on the data. A quiesce WOULD, for a brief moment, prevent updates to the data. And for recovery, DB2 simply starts reading the log at the RBA for the IC START [...] 26195 138 17_Re: Quiesce Point20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 7 Jun 2000 15:49:47 -0400435_iso-8859-1 You didn't read my note properly. I said SHRLEVEL CHANGE can read uncommitted data. Would you like to recover TOCOPY when there is a possibility of uncommitted data?

Consider the following scenario -

PROGRAM A was update/deleting/inserting TABLE A with no commits. IBM imagecopy was executing with SHRLEVEL CHANGE. Imagecopy ended but PROGRAM A still didn't commit. PROGRAM A ROLLED BACK for some reason. [...] 26334 95 33_DSSIZE Performance Considerations10_Mark Feven19_markfeven@YAHOO.COM30_Wed, 7 Jun 2000 20:56:30 +0100422_iso-8859-1 Can anybody tell me if there are any performance considerations regarding the selection of the DSSIZE parameter when creating a tablespace (OS/390 - Version 6).

For those not familiar, this parameter is used to 'switch on' extended addressability within DFSMS allowing datasets of 8, 16, 32 or 64 GB to be created (and can also be used to create LARGE 'non EA Enabled' tablespaces when set to 4GB). [...] 26430 48 37_Re: DSSIZE Performance Considerations20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 7 Jun 2000 15:58:54 -0400369_us-ascii IBM (manuals) claim that there is no performance hit due to EA enabled dataset. You could have everything under EA enabled SMS pool. I have not done any benchmark on our side.

-----Original Message----- From: Mark Feven [SMTP:markfeven@YAHOO.COM] Sent: Wednesday, June 07, 2000 3:57 PM To: DB2-L@RYCI.COM Subject: DSSIZE Performance Considerations [...] 26479 177 17_Re: Quiesce Point12_craig patton21_prgpatton@HOTMAIL.COM28_Wed, 7 Jun 2000 13:03:27 PDT514_- Pillay, I believe there is a misunderstanding in HOW db2 performs recovery when using COPY and CHANGE. DB2 READs the LOG starting at the Beginning of the copy RBA, it checks the LRSN of the page on the copy and in the LOG, if the LOG version is MORE recent than what is on the COPY, it applies the data from the log, if the page ON the copy is more recent than the LOG, it does keeps the page from the copy, THUS ROLLBACKS and COMMITED data that occurs AFTER the copy IS applied during the RECOVERY and the [...] 26657 258 17_Re: Quiesce Point20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 7 Jun 2000 16:23:17 -0400358_iso-8859-1 That is not correct.

RECOVER TOCOPY will not do LOGAPPLY. It is kind of equivalent of DB2 doing REPRO from imagecopy to VSAM. RECOVER TORBA/TOLRSN/TOLOGPOINT would do LOGAPPLY RECOVER LOGONLY checks the header page RBA and applies LOG. RECOVER with no keyword recovers to current LOGPOINT using LOG.

Here is one such scenario - [...] 26916 12 56_Condition code 08 in DSNUproc does not generate an abend12_Vernon, John27_John.Vernon@TRS.STATE.TX.US30_Wed, 7 Jun 2000 15:28:27 -0500269_iso-8859-1 any ideas why???

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 26929 67 60_Re: Condition code 08 in DSNUproc does not generate an abend0_19_Tim.Lowe@STPAUL.COM30_Wed, 7 Jun 2000 15:53:31 -0500453_us-ascii John, As delivered by IBM, the sample DB2 utility proc, DSNUPROC, does not have an abend step in it. Some shops add a conditional abend step based on a parm in the proc that indicates a "good condition code". (default to 0 or 4?) Others build a new proc that includes this conditional abend code step. Or, if you like you can run it "as is" and add a step in your jcl to do this. It is up to the DB2 system administrator who installs DB2. [...] 26997 605 17_Re: Quiesce Point18_Bond, Victor - CFC20_Bond.Victor@CFWY.COM30_Wed, 7 Jun 2000 14:13:44 -0700360_iso-8859-1 The V6 Admin Guide says:

Planning for point-in-time recovery: TOCOPY and TORBA are viable alternatives in many situations in which recovery to the current point in time is not possible or desirable. To make these options work best for you, take periodic quiesce points at points of consistency that are appropriate to your applications. [...] 27603 76 29_Re: ONline backup for DB2 UDB14_Julian Stuhler27_julian.stuhler@TRITON.CO.UK30_Wed, 7 Jun 2000 19:21:26 +0100412_iso-8859-1 Hi.

I assume you're talking about DB2 UDB for NT, Unix, etc.

Yes, DB2 does support online backups. No additional software/hardware is required.

Point in time recovery is also supported, using concepts that are very similar to DB2 for OS/390 (the major external difference is the use of a real timestamp rather than an RBA or LRSN to specify the point in time to recover to). [...] 27680 38 17_TS Status changes0_22_Rohn.Solecki@MTS.MB.CA30_Wed, 7 Jun 2000 16:46:20 -0500531_us-ascii We had a little problem over the weekend, and I'm looking for some input, hoping to avoid problems in future.

Situation: DB2 V5, OS390 Made some table changes, new columns added. Table was put into UT access status. Started a RUNSTATS with SHRLEVEL REFERENCE.

The job ran late, into the online and we had many REBINDS to run. So one of the other DBAs changed the table access status from UT to RW, WHILE the runstats job was running. Then started the rebinds to run in parallel with remaining runstats. [...] 27719 155 28_Re: IFCID for SQL statements8_Kurian B23_Bejoy_Kurian@MAY-CO.COM30_Wed, 7 Jun 2000 16:55:39 -0500692_us-ascii Venkat, Thanks. I think, I would be able to produce the report which i am looking for. Bejoy





From: "Pillay, Venkat (PCA)" on 06/07/2000 01:09 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Bejoy Kurian/MDSC/MAYCO)

Subject: Re: IFCID for SQL statements







IFCID 63 is just pure text. If you are looking for accounting info, I guess you mean you want elapsed time and CPU time per SQL. Then your trace should have TDATA(CPU) to attach CPU header in your trace and then subtract end-of-SQL TIME to start-of-sql TIME to get this info. I [...] 27875 26 15_signal sqlstate15_Benjamin Reches24_Benjamin.Reches@MSDW.COM30_Wed, 7 Jun 2000 18:24:08 -0400402_us-ascii The IBM Doc specifies that a stored procedure can issue the SIGNAL SQLSTATE command. (B.2 SQL statements allowed in functions and stored procedures ---- Table 56.)

When I try to compile this the db2 Pre-compiler kicks out the line of code with the following message:

DSNH142I E DSNHSM5R THE SQL STATEMENT IS NOT SUPPORTED SIGNAL SQLSTATE'RPSBD'('CHECK WLM LOG FOR DETAILS') [...] 27902 37 38_Re: DB2 V5 -904 Resource of all zero's12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Thu, 8 Jun 2000 10:49:40 +1200471_us-ascii Bruce (and Deborah),

We had a similar problem here awhile ago. We had a 2-partition table(space) and had a one-off program that was going to update the partitioning key such that the row would move partitions. Cool, eh? Anyway, we needed a PTF applied (can't remember which one) that would allow it, and to drop and recreate the table so that it's create date was later than the date the PTF was applied. Couldn't see why that was, but there you go. [...] 27940 30 26_Catch 22 on Stogroup Auths11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Wed, 7 Jun 2000 16:04:41 -0700376_iso-8859-1 I seem to have a bit of a catch 22. I'm trying to clean up SYSRESAUTH stogroup auths from grantors who are long gone or no longer used. When I try to REVOKE USE (I have SYSADM), I get a; SQLCODE = -556, ERROR: xxxx CANNOT HAVE THE USE PRIVILEGE ON SG01 REVOKED BY DB2SYS BECAUSE THE REVOKEE DOES NOT POSSESS THE PRIVILEGE OR THE REVOKER DID NOT MAKE THE GRANT [...] 27971 111 29_Re: ONline backup for DB2 UDB22_Piontkowski Michael ML35_michael.piontkowski@ASTRAZENECA.COM30_Wed, 7 Jun 2000 19:05:22 -0400321_- Keep in mind that the timestamp used for recovery is specified in UTC time (i.e. GMT). Because DB2 always uses the UTC time for recoveries, you can get confused quite easily. I believe a backup timestamp is recorded in local time (in the history file?). However, the recovery command expects the timestamp in UTC. [...] 28083 53 30_Re: Catch 22 on Stogroup Auths13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Wed, 7 Jun 2000 16:12:44 -0700626_us-ascii Do a SET CURRENT SQLID = and then do the Revoke. Should work.

Thanks.

Ashish.

-----Original Message----- From: Cliff Boley [SMTP:Maurice.C.BOLEY@ODOT.STATE.OR.US] Sent: Wednesday, June 07, 2000 4:05 PM To: DB2-L@RYCI.COM Subject: Catch 22 on Stogroup Auths

I seem to have a bit of a catch 22. I'm trying to clean up SYSRESAUTH stogroup auths from grantors who are long gone or no longer used. When I try to REVOKE USE (I have SYSADM), I get a; SQLCODE = -556, ERROR: xxxx CANNOT HAVE THE USE PRIVILEGE ON SG01 REVOKED BY DB2SYS BECAUSE THE REVOKEE DOES NOT [...] 28137 68 30_Re: Catch 22 on Stogroup Auths11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Wed, 7 Jun 2000 16:22:14 -0700344_iso-8859-1 I was using the SET CURRENT SQLID = 'X' when I got the -555 ?? The SET worked when I got rid of X's auths to other Grantees but not to itself. cliff:-( -----Original Message----- From: Mohan, Ashish [mailto:Ashish.Mohan@NIKE.COM] Sent: Wednesday, June 07, 2000 4:13 PM To: DB2-L@RYCI.COM Subject: Re: Catch 22 on Stogroup Auths [...] 28206 53 30_Re: Catch 22 on Stogroup Auths40_Andersen, Cindy (CC-Application Support)26_Cindy.Andersen@CONAGRA.COM30_Wed, 7 Jun 2000 18:26:32 -0500488_iso-8859-1 Did you include the BY ALL or BY "grantor id" on the REVOKE USE OF STOGROUP SG01 FROM xxxx command? It should allow you to remove this authority.

REVOKE USE OF STOGROUP SG01 FROM xxxx BY ALL; or REVOKE USE OF STOGROUP SG01 FROM xxxx BY "grantor id";

Either should work.

-----Original Message----- From: Cliff Boley [mailto:Maurice.C.BOLEY@ODOT.STATE.OR.US] Sent: Wednesday, June 07, 2000 6:05 PM To: DB2-L@RYCI.COM Subject: Catch 22 on Stogroup Auths [...] 28260 24 33_Re: DB2 PM and storing trace data15_Richard Simpson25_rsimpson@MACQUARIE.COM.AU30_Thu, 8 Jun 2000 09:57:20 +1000402_iso-8859-1 We get DB2 to write the traces to SMF, then have PM regularly extract the data from SMF, format it, then load it into DB2 tables.

We have compression turned on for these tables in DB2 and (for our data) they compress at about 90%.

We currently have all our DB2 performance data since Jan 1997 available for query via SQL. It is summarised by hour and CICS Tran / Jobname. [...] 28285 15 65_droping of DB2 Catalog and directory tables - SYSUTIL and SYSLGRN11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM30_Thu, 8 Jun 2000 08:19:38 +0800406_us-ascii Hi, Could anyone tell me, whether DB2 V5 still used these two tables: SYSUTIL and SYSLGRN? IF I drop the above two tables, will it affect my DB2 Subsystem?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 28301 37 40_Backup strategy for 1000s of tablespaces12_Dennis Raher27_Dennis_Raher@COMPUSERVE.COM30_Wed, 7 Jun 2000 20:35:45 -0400469_ISO-8859-1 Rick

It's clear from your comment that you have not had the JOY of dealing with these ERP companies like SAP and Peoplesoft. I would have agreed with your thoughts completely, since they are logical, before I delt with peoplesoft. They install all the tables, no choice. We also asked about which tables went with which applications and they stated right up front that they didn't know. Do you believe they can actually sell something like that. [...] 28339 41 39_from: Ravi Kumar (for DB2 DBA position)17_ravi kumar hassan18_ravibh@HOTMAIL.COM28_Wed, 7 Jun 2000 19:00:26 PDT470_- Hello folks,

I need a help from all of you. let me introduce myself first. my name is Ravi kumar. i am working as a DB2 DBA, right now i am working for the Department of Health and Mental Hygene which is the Maryland State Department located in Baltimore. i am looking for a better oppertunity. if you guys find any position for db2 dba in any good organisation as a full time employee. pl. let me know. i hope you guy's will help me in finding a postion. [...] 28381 103 85_Re: DB2 Connect problem - Enterprise edition - SOCKET=READ RC= 11 21 R eason=12EE029114_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 8 Jun 2000 12:09:17 +1000427_iso-8859-1 Lori

There doesn't appear (to me) to be anything relavent in the aparlist distributed with fixpak3. Is there anything in the db2diag.log files on either the EE server or the clients, or in the NT(is that what EE is running on?) event log?

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au [...] 28485 69 16_Re: UDF Question14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 8 Jun 2000 12:11:07 +1000417_iso-8859-1 Bejoy

A 'separate lib' from what? Do you have separate libraries from batch programs and CICS/IMS programs? Probably. So have one for stuff running in the SP address space.

If you have DB2 modules that are invoked in, say, both batch and in the SPAS it is essential, because the SPAS version has to use DSNRLI and it unlikely (not impossible) that your batch interface is through RRSAF. [...] 28555 287 17_Re: Quiesce Point7_Sanjeev19_Sanjeev@INTERRA.COM30_Thu, 8 Jun 2000 10:34:06 +0530534_iso-8859-1 Hi , I also think the same thing as mentioned by Venkat because if we are telling DB2 to recover TO COPY then why should it apply log and till where it apply logs.If it apply logs till the current time then there is no difference between TO COPY recover and recover without mentioning anything. I would like to add one more question in the same thread. As we all know DB2 records the start RBA while taking the image copy(Say I1) with SHRLEVEL CHANGE then if we take that RBA and use RECOVER TO RBA then, will DB2 use [...] 28843 65 44_Re: Backup strategy for 1000s of tablespaces23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Thu, 8 Jun 2000 06:02:30 +0100525_iso-8859-1 Dennis



I know we are getting off thread, however, I work on SAP and I have been asking for a datamodel for ages....same reply "we do not have it". Well I heard yesterday that release X of SAP (I assume 5 as it is the next big number change and we are off to 4.6C this year ourselves) is going to inlcude a full, correct datamodel ... so I am told via the grape vine SAP are unhappy at the cost of doing it but understand that they will benefit [...]