1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l October 1999, week 1 2 40 59_If you want to get off the DB2-L mailing list, here is how.12_John Morrill12_johnm@vp.net30_Mon, 4 Oct 1999 20:35:19 -0500375_iso-8859-1 Greetings!

The new DB2-L list is about to go into production.

We have made every attempt to remove those who do not want to subscribe. However, due to several problems we have had, and the fact we have had to move to new software, we may have missed some cases of were people have asked to be taken off the mail list, but are still on the list. [...] 43 28 20_What is new at DB2-L12_John Morrill12_johnm@vp.net30_Mon, 4 Oct 1999 21:07:52 -0500341_iso-8859-1 Greetings!

It has been a hard week, be things are getting back to better then normal for all the DB2-L subscribers!

First, for many of you, this will be the first message you have received from DB2-L in a week. We have problems with the Mailing List software, so we have switch back to good ole Lsoft Listserv. [...] 72 10 28_Test Posting - Please Ignore13_NoreenLaFleur26_Noreen.LaFleur@prodigy.net30_Mon, 4 Oct 1999 23:34:24 -040039_iso-8859-1 Test Posting - Please Ignore 83 9 0_14_george mclaren31_george_mclaren@standardlife.com30_Tue, 5 Oct 1999 07:56:42 +010020_iso-8859-1 Test Mail 93 9 62_test, please ignore, do not reply....... sorry for that ......13_Raabe, Stefan30_Stefan.Raabe@Dresdner-Bank.com30_Tue, 5 Oct 1999 10:35:13 +02006_- test 103 23 10_Existence?9_Slot J.P.23_J.P.Slot@rf.rabobank.nl30_Tue, 5 Oct 1999 10:43:22 +0100352_us-ascii Dear DB2-L,

Last time I heard from u was at 25 september. At that time I got the last digest. Am I filtered out? What happened to u, or to my subscription? I am anxiously waiting for your reply.

Regards,

Jaap Slot, DB2 DBA, Rabofacet BV +31 30 215 2220

--- Without any norm there can only be chaos --- anonymous --- 127 143 48_CURRENTDATA and The Australian solution again...11_Thomas Berg34_thomas.berg@foreningssparbanken.se30_Tue, 5 Oct 1999 14:31:28 +0200326_iso-8859-1 I send this one time again because i didn't get so many answers earlier and we are in the process of report the error to IBM: I wonder if ANY of You had a experience resembling this ? TIA

We have a problem with -803 in an INSERT with a value originating from incrementing a numeric counter in this way: [...] 271 9 19_Test, please ignore14_Rebecca A Bond22_rebecca_a_bond@uhc.com30_Tue, 5 Oct 1999 08:50:41 -040028_us-ascii Test, please ignore 281 17 21_Plans and Tablespaces0_13_mosswr@NU.COM30_Tue, 5 Oct 1999 09:12:51 -0400303_us-ascii Can someone please give me an example of when a plan would be dependent on a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a tablespace, but I don't understand why. I understand the dependency on views and tables because of the SQL in the plan, but not tablespaces. [...] 299 13 24_No postings since Friday14_Stevens, Wayne21_Wayne_Stevens@bmc.com30_Tue, 5 Oct 1999 08:30:07 -0500185_iso-8859-1 I have not received any postings from the thread since October 1st. Is anybody else having this problem. Please email me directly because of this problem

Wayne Stevens 313 39 25_Re: Plans and Tablespaces14_Wayne Driscoll19_wdriscoll@quest.com30_Tue, 5 Oct 1999 09:23:22 -0500478_iso-8859-1 A plan should be dependant upon all tablespaces that contain tables that the plan is dependent upon. SYSPLANDEP is used to mark plans invalid when a dependent object is dropped. Since a TS drop also drops the tables, the plans will be invalidated. This begs the question regarding why SYSPLANDEP doesn't list databases, but that one I can't answer. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com NOTE: All opinions are strictly my own. [...] 353 133 45_DBA Advisor wanted in K.C, MO area (USA only)0_54_Shan_Leatherman/MO/americancentury@americancentury.com30_Tue, 5 Oct 1999 09:10:56 -0500547_us-ascii



We are looking for a DBA to fill a new ADVISOR position at my company. They need to have a strong technical DBA background with an emphasis of expertise in WEB development. I'm not a recruiter but a Senior DBA who would be working with this new person. I've been with American Century Investments for the past 10 years and can attest that it is a wonderful company with excellent benefits. Below is the job description. If you have an interest, email me your resume and I'll get it to our HR folks for consideration. [...] 487 39 15_DB2-L Test Post0_22_kls_tech@ix.netcom.com30_Tue, 5 Oct 1999 10:41:42 -0400438_iso-8859-1 I haven't seen any posts since this one on Friday.

This is a test to check if this list is functional.

-----Original Message----- From: owner-db2-l@ryci.com [mailto:owner-db2-l@ryci.com]On Behalf Of Leo Flores Sent: Friday, October 01, 1999 6:56 PM To: Multiple recipients of list db2-l Subject: All Quiet On The Western Front



I am not recieving any post's. Should I be? Below are my options. [...] 527 112 48_Re: db2connect ee trusted connections to mvs/db217_Hipenbecker, Doug25_Hipenbecker.Doug@mbco.com30_Tue, 5 Oct 1999 10:03:56 -0500359_iso-8859-1 Your comment: "On DB2 subsystem set DDF to accept ALREADY_VERYFIED APPC security"

Is this the SECURITY_IN & SECURITY_OUT columns on the SYSIBM.LUNAMES table.

They are both set to "A" which should allow for a userid w/o password to be passed.

I'm still not able to get this trusting to work...any other things to look for? [...] 640 15 31_I have stopped receiving posts!13_Keith Gilbert17_kgilbert@csa1.com30_Tue, 5 Oct 1999 10:29:51 -0500168_iso-8859-1 I have stopped receiving posts to the list. I am sending this to see if I'm still subscribed.

Keith Gilbert DBA Central Service Association Tupelo, MS 656 38 18_ROWID partitioning13_Marcus Davage29_Marcus.Davage@LloydsTSB.co.uk30_Tue, 5 Oct 1999 15:31:54 +0100516_iso-8859-1 Hello all,

I haven't received an email from DB2-L for days, but here goes...

We have an "off the shelf" product which does NOT support partitioning. The tablespaces are getting rather huge, and we could do with some relief when it comes to batch jobs - i.e. parallelism.

Has anyone out there partitioned a tablespace on a column with a ROWID data type in V6 (OS/390)? If so, how easy was it? Did you have to change your applications? Did you experience any parallelism benefits? [...] 695 7 13_Test - ignore11_Robert Jans21_Jans.Robert@amstr.com30_Tue, 5 Oct 1999 10:03:00 -060040_- This is a test message, please delete. 703 20 11_Audit trace9_Sally Mir16_smir@bcbs-ga.com30_Tue, 5 Oct 1999 15:38:18 -0400288_us-ascii Is there any way to determine who has accessed a particular table (for either update or read) without turning on an audit trace?

Thanks,

Sally Mir Blue Cross Blue Shield of Georgia

P.S. Where did everybody go? I haven't received any list mail for a few days. 724 11 28_The Listserve appears quiet?0_14_DPetro@AOL.COM28_Tue, 5 Oct 1999 18:49:39 EDT99_us-ascii The Listserve appears quiet since the switchover - is something wrong with my setup maybe? 736 32 63_Re: If you want to get off the DB2-L mailing list, here is how.0_19_boe.franklin@DB.COM30_Tue, 5 Oct 1999 19:02:06 -0400445_- Thank you for your hard and I'm sure stressful work.

Thanks, Boe Franklin 410-308-6796 Technical Services, Atrium Deutsche Banc Alex. Brown



> -----Original Message----- > From: John Morrill [SMTP:johnm@vp.net] > Sent: Monday, October 04, 1999 9:35 PM > To: DB2-L@RYCI.COM > Subject: If you want to get off the DB2-L mailing list, here is how. > > Greetings! > > > > Cheers! > > J. Michael Morrill > DB2-L Chief List Owner 769 17 36_test post - not receiving from db2-l21_Smith, Ronald M - CNF20_Smith.Ronald@CNF.COM30_Tue, 5 Oct 1999 15:47:48 -0700248_- This is a test. This is *only* a test. If this had been a *real* post, someone would have received it.....

Ron Smith DB2 DBA CNF ADTECH Office: (503) 450 - 3164 Fax: (503) 450-3629 E-mail: smith.ronald@cnf.com 787 7 34_test post - please ignore this msg16_Ronald Smith-CNF20_smith.ronald@CNF.COM30_Tue, 5 Oct 1999 17:56:53 -070068_- this is a test post - i have received nothing since last Friday... 795 129 49_Re: DBA Advisor wanted in K.C, MO area (USA only)19_Richards, Katherine26_Katherine_Richards@BMC.COM30_Tue, 5 Oct 1999 18:01:33 -0500718_iso-8859-1 -----Original Message----- From: Shan_Leatherman/MO/americancentury@americancentury.com [mailto:Shan_Leatherman/MO/americancentury@americancentury.com] Sent: Tuesday, October 05, 1999 9:11 AM To: DB2-L@RYCI.COM Subject: DBA Advisor wanted in K.C, MO area (USA only)

<< File: ATT795626.txt >>

We are looking for a DBA to fill a new ADVISOR position at my company. They need to have a strong technical DBA background with an emphasis of expertise in WEB development. I'm not a recruiter but a Senior DBA who would be working with this new person. I've been with American Century Investments for the past 10 years and can attest that it is a wonderful company with excellent benefits. Below [...] 925 27 25_Re: Plans and Tablespaces11_Peter Broad20_mjs.pbb@SYMPATICO.CA30_Tue, 5 Oct 1999 20:44:01 -0400549_us-ascii Bill;

I would think the plan is dependent upon the tablespace because that is the physical object which contains one or more of your tables.

Peter

mosswr@NU.COM wrote:

> Can someone please give me an example of when a plan would be dependent on > a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a > tablespace, but I don't understand why. I understand the dependency on > views and tables because of the SQL in the plan, but not tablespaces. > > Thanks, > > Bill Moss > Northeast Utilities 953 26 24_Re: What is new at DB2-L11_Tom Flesher13_tomf@ENET.COM30_Tue, 5 Oct 1999 17:28:23 -0700409_ISO-8859-1 John and List Managers,

The ability to visit a web site to set/change subscriber attributes - brilliant!

I recommend bookmarking the URL ( http://jupiter.ryci.com/cgi/wa.exe?SUBED1=db2-l&A=1) for possible future use, since the listserver menu does not appear to have a link to this page, or am I missing something?

Congratulations on migrating the list to the new server! [...] 980 42 34_Re: All Quiet On The Western Front19_J. Michael Morrilll21_xavier@NETEXPRESS.NET30_Wed, 6 Oct 1999 00:23:34 -0500396_iso-8859-1 You should be receiving mail now. Let me know if you are still not getting any mail.

Cheers!

J. Michael Morrill DB2-L Chief List Owner

-----Original Message----- From: owner-db2-l@ryci.com [mailto:owner-db2-l@ryci.com]On Behalf Of Leo Flores Sent: Friday, October 01, 1999 5:56 PM To: Multiple recipients of list db2-l Subject: All Quiet On The Western Front [...] 1023 67 20_Re: List Moving Day!19_J. Michael Morrilll21_xavier@NETEXPRESS.NET30_Wed, 6 Oct 1999 01:05:02 -0500409_iso-8859-1 You should be receiving mail now. Let me know if you are still not getting any mail.

Cheers!

J. Michael Morrill DB2-L Chief List Owner

-----Original Message----- From: owner-db2-l@ryci.com [mailto:owner-db2-l@ryci.com]On Behalf Of Piontkowski Michael ML Sent: Monday, September 27, 1999 12:18 PM To: Multiple recipients of list db2-l Subject: RE: [DB2-L] List Moving Day! [...] 1091 41 22_y2k - taking a chances11_Daniel Adam15_dadam@GRATEX.SK30_Wed, 6 Oct 1999 09:10:44 +0200557_- This is repost, original I think haven't got through.

> Hi everyone, > > I would like to hear from you guys, during the y2k readiness efforts, how > many of you had to cope with software that is labeled by it's producent as > being NOT READY. Specifically, is anyone outthere working for company > where they use so old versions that are not supported anymore. Did any of > the shops preferred not upgrading to y2k ready versions, but rather do the > y2k testing and save the money ? Specifically I'm interested in financial > institutions. > [...] 1133 8 0_12_Guru, Bala X17_bala.guru@EDS.COM30_Wed, 6 Oct 1999 01:05:20 -040041_iso-8859-1 Test mail. Please ignore it... 1142 29 28_Access to DB2 other than SQL17_Giorgio De Nunzio25_giorgio.denunzio@UNILE.IT30_Wed, 6 Oct 1999 10:37:36 +0200290_us-ascii Hi. I am quite new to DB2. Somebody asked me if there exist some way, other from SQL, to access DB2 databases from a C program (in MVS): the idea is to get a faster access by directly calling parts of the DB2 subsystem instead of using SQL queries. Anybody can answer? Thanks [...] 1172 79 38_DB2 OS/390 Disaster Recovery questions0_23_robin.leske@SGIO.COM.AU30_Wed, 6 Oct 1999 19:09:43 +1030579_us-ascii Ed, We ironically completed a Disaster Recovery exercise last week, recovering subsystems on both DB2 V3 and DB2 V4. We coincidently had problems with SYSLGRNX. We noticed that while the V3 recoveries applied logs as expected, the V4 recoveries did not.

The V4 recovery of SYSLGRNX left SYSLGRNX and its indexes in a state of RW,LPL. Subsequent application recoveries do not apply logs at all. The REPORT utility indicated SYSLGRNX was missing log ranges. Checking the V4 manual revealed we had omitted the recovery of the SYSLGRNX indexes introduced in V4! [...] 1252 17 14_Scripts in UDB13_Donald Tipton29_Donald.Tipton@DAS.STATE.OH.US30_Wed, 6 Oct 1999 07:59:44 -0400515_us-ascii We are using Control Center version 5.2 to create scripts to run against our UDB version 5.0 database that resides on an AIX platform. If we use the smartguides to create the scripts we are unable to view the contents of the script after it has been created. The output from the script execution is also lacking meaningful details. Has anyone found a way to view the contents of the scripts when using the smartguides? IS there a way to get more detailed output when executing the script? Thanks Don Tipton 1270 93 18_Re: Scripts in UDB11_Hoch, Chris19_choch@GOERLICHS.COM30_Wed, 6 Oct 1999 08:04:40 -0400581_- yes reboot your computer in safe mode and delete all the files relating to your problem then reinstall them.

> -----Original Message----- > From: Donald Tipton [SMTP:Donald.Tipton@DAS.STATE.OH.US] > Sent: Wednesday, October 06, 1999 8:00 AM > To: DB2-L@RYCI.COM > Subject: Scripts in UDB > > We are using Control Center version 5.2 to create scripts to run against > our UDB > version 5.0 database that resides on an AIX platform. If we use the > smartguides > to create the scripts we are unable to view the contents of the script > after it > has been created. The [...] 1364 30 25_Re: Plans and Tablespaces0_23_Mike_Levine@TEKHELP.NET30_Wed, 6 Oct 1999 08:28:05 -0400541_us-ascii Hi Bill,

If you drop a tablespace, you also drop the table/view thereby invalidating the plan/package.

Regards,

Michael Levine Premier Data Services, Inc.



>Can someone please give me an example of when a plan would be dependent on >a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a >tablespace, but I don't understand why. I understand the dependency on >views and tables because of the SQL in the plan, but not tablespaces. > >Thanks, > >Bill Moss >Northeast Utilities > > 1395 102 29_Using Reorg 'Unload External'0_19_ddzienni@US.IBM.COM30_Wed, 6 Oct 1999 08:26:42 -0400579_us-ascii Hello,

I am seeking information on the use of reorg 'unload external' for better performance when unloading data. We are trying to reduce clock time on some of our batch unload jobs. I have tested this performance enhancement and found it to be faster (comparable to OEM products).

If anyone is using this utility/parms in production I would like to hear the pro's and con's. I noticed one difference, the loadctl written to syspunch dd starts in position 7, so when loading the data the loadctl created from the 'unload external' reorg must be used. [...] 1498 28 25_Grouping results of Union20_Mastan, Shabbir (S.)16_smastan@FORD.COM30_Wed, 6 Oct 1999 13:37:36 +0100377_iso-8859-1 Below is an example of what I would get from 'unioning' 2 tables.

Fld1 Fld2 Count ------ ------ --------- KEYA KEYB 12 KEYA KEYB 03 KEYC KEYD 22

what I want to end up with is:

KEYA KEYB 15 KEYC KEYD 22

Basically I want to group by the first two fields and sum the count field, is this possible in conjunction with unioning 2 tables. [...] 1527 24 42_inserts into DB2 OS390 with unique indexes0_23_john.lantz@ZURICHUS.COM30_Wed, 6 Oct 1999 08:57:37 -0400569_us-ascii In a case where you have secondary indexes defined on a table (perhaps to obtain "index only") access, is there any difference to DB2 in the amount of work that has to be done when INSERT'ing if the index is defined as unique or not when doing INSERT's? Somebody had mentioned that an INSERT into a unique index takes more processing than an INSERT into a non-unique index because DB2 has to make sure it's unique. The point was that if you didn't care about enforcing uniqueness with the secondary index, then save some time with INSERT's by defining it [...] 1552 20 32_Re: Access to DB2 other than SQL10_Ken Koleto23_kkoleto@PAINEWEBBER.COM30_Wed, 6 Oct 1999 09:01:54 -0400402_us-ascii Giorgio, SQL is the only way to access DB2 data using DB2. If there were other ways to so, the data integrity, recoverability and security controls and processes would be compromised and therefore the value of DB2 would be lost. Incidentally, if you haven't implemented adequate security measures appropriate to the platform on which you are running your DB2, you risk similar exposures. [...] 1573 140 49_Re: DBA Advisor wanted in K.C, MO area (USA only)0_24_Munwar.Malik@ACS-GSG.COM30_Wed, 6 Oct 1999 10:30:51 -0400413_US-ASCII American Century Services is a GREAT company. I have consulted there. Their DBA group is GREAT. Shan , is this a full time or contract position?

Munwar



______________________________ Reply Separator _________________________________ Subject: DBA Advisor wanted in K.C, MO area (USA only) Author: DB2 Data Base Discussion List at Internet Date: 10/5/99 9:10 AM [...] 1714 69 22_y2k - taking a chances11_Gavin Hogan18_ghogan@METLIFE.COM30_Wed, 6 Oct 1999 09:12:59 -0400608_us-ascii My company is taking the approach of upgrading (in some cases replacing software with another vendor's) anywhere it finds lack of compliance. There may be exceptions (vendor packages for which there is no immediately available appropriate substitute, etc. -- but in such a case the software is tested, and its behavior and associated risks etc. documented. (I'm not sure I know of any specific cases of the latter scenario, but it's a big company and my position doesn't give me the need to access detail on other projects, although I know it is available, as everything is being tracked.) The [...] 1784 49 22_Re: ROWID partitioning14_Philip Gunning20_pgunning@BOSCOVS.COM30_Wed, 6 Oct 1999 09:34:30 -0400399_- Marcus, Just to let you know your post made it and the list is back to normal. Phil

Phil Gunning Assoc List Owner DB2 DBA IBM Certified Solutions Expert -- DB2 UDB IBM Certified Solutions Expert -- CICS TS

-----Original Message----- From: Marcus Davage [SMTP:Marcus.Davage@LloydsTSB.co.uk] Sent: Tuesday, October 05, 1999 10:32 AM To: DB2-L@RYCI.COM Subject: ROWID partitioning [...] 1834 150 49_Re: DBA Advisor wanted in K.C, MO area (USA only)0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM30_Wed, 6 Oct 1999 08:31:27 -0500582_us-ascii Full Time

Thanks for the confirmation.

As always , nice to hear from you .

Shan







Munwar.Malik@ACS-GSG.COM on 10/06/99 09:30:51 AM





Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Shan Leatherman/MO/americancentury) Subject: Re: DBA Advisor wanted in K.C, MO area (USA only)





American Century Services is a GREAT company. I have consulted there. Their DBA group is GREAT. Shan , is this a full time or contract position? [...] 1985 14 27_SAP Question using SNAPSHOT0_20_John_Lendman@FPL.COM30_Wed, 6 Oct 1999 09:43:30 -0400320_us-ascii How can SNAPSHOT best be used to do SAP/DB2 backups/recoveries? Are the MVS and DB2 catalogs part of the SNAP? Are the ARCHIVE LOGS part of the SNAP? Can we ROLL forward? Is Virtual Concurrent Copy with DB2 Image copy and alternative? Is information in CCMS of any value to the OS390 and DB2 System Programmers? 2000 93 19_DB2 UDB Connections11_Emery, Matt19_matt.emery@JNLI.COM30_Wed, 6 Oct 1999 09:40:22 -0400367_iso-8859-1 On AIX, when doing a "list application" command the ip address of a connected client can be found in the "application id" field, in hex format. When I convert the hex value to decimal I get an ip address that is not in our network. Does anyone know the trick for interpreting these hex tcp/ip numbers and determining who is connected to the database? [...] 2094 42 24_Re: What is new at DB2-L22_Piontkowski Michael ML38_michael.piontkowski@ZCSWILM.ZENECA.COM30_Wed, 6 Oct 1999 09:54:37 -0400718_- Tom -

I'm not sure what the "listserver menu" is.

On the http://www.ryci.com/db2-l page is a link to subscribe through the web interface. Follow that link to another link to join, leave or change your subscription settings.



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

> ---------- > From: Tom Flesher[SMTP:tomf@ENET.COM] > Sent: Tuesday, October 05, 1999 8:28 PM > To: DB2-L@RYCI.COM > Subject: Re: What is new at DB2-L > > John and List Managers, > > The ability to visit a web site to set/change subscriber attributes - > brilliant! > > I recommend bookmarking the URL ( > http://jupiter.ryci.com/cgi/wa.exe?SUBED1=db2-l&A=1) for [...] 2137 38 23_Re: DB2 UDB Connections13_Fisher, Larry19_LFISHER@INTEGON.COM30_Wed, 6 Oct 1999 09:59:43 -0400572_- The IP address is not hex. It is a binary dotted decimal address. 10000000 00001010 00011110 converts to an IP address of 128.10.2.30.

> -----Original Message----- > From: Emery, Matt [SMTP:matt.emery@JNLI.COM] > Sent: Wednesday, October 06, 1999 9:40 AM > To: DB2-L@RYCI.COM > Subject: DB2 UDB Connections > > On AIX, when doing a "list application" command the ip address of a > connected client can be found in the "application id" field, in hex > format. When I convert the hex value to decimal I get an ip address that > is not in our network. > > Does [...] 2176 30 25_Re: Plans and Tablespaces11_sanjay jain25_sanjay_jain@EMAIL.MSN.COM30_Wed, 6 Oct 1999 07:04:53 -0700709_- Bill,

If you drop the tablespace, then the table/views go too and the plan become invalid. Hence the dependency....This is my understanding.

Sanjay

-----Original Message----- From: mosswr@NU.COM Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Tuesday, October 05, 1999 3:48 PM Subject: Plans and Tablespaces



>Can someone please give me an example of when a plan would be dependent on >a tablespace. I find rows in SYSIBM.SYSPLANDEP that show dependence on a >tablespace, but I don't understand why. I understand the dependency on >views and tables because of the SQL in the plan, but not tablespaces. > >Thanks, > >Bill Moss [...] 2207 131 29_Neon--DB2 Connection: HELP!!!11_David Brown15_brown@CS.UA.EDU30_Wed, 6 Oct 1999 09:07:20 -0500374_iso-8859-1 Everyone:

I am working with a client who is just bringing up a major project management system application that uses an ODBC connection to DB2 using Neon Systems Shadow Direct.

The application only has about 65 users on it but it is bringing the current (55 MIPs) system down. We have an idea it has to do with improper installation or tuning. [...] 2339 21 37_Catalog and Directory Inconsistencies12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Wed, 6 Oct 1999 09:17:00 -0400571_- Not sure if this got through, so this is a resend.

hrough several manipulations of Alter to TYPE 2 of indexes, repairs, reorgs, quiesces and copies. There is an index out of sync with the directory/catalog. We are getting 00C90101 when accessing data using the bad index and 00E40317 when either recovering or checking the index. Can't drop the index or table. Production DB2, so can't drop the tablespace, not sure if it would work anyway. Ironic thing is that this is a test table and index. Does anyone know a way to get rid of this bad table/index pair [...] 2361 114 23_Re: DB2 UDB Connections16_Michael McCarthy24_Michael_McCarthy@DTC.ORG30_Wed, 6 Oct 1999 10:21:28 -0400294_us-ascii Matt,

When an application is connected via tcpip, the first part of the application id is the hex representation of the client's ip address. Each two bytes of the first part of the appl id represents a part of the ip address. The example below will show how to convert it. [...] 2476 53 31_Re: SAP Question using SNAPSHOT13_Adrian Savory34_Adrian.Savory@CCMAIL.EU.SONY.CO.JP30_Wed, 6 Oct 1999 15:21:24 +0100438_ISO-8859-1 John,

I work at a SAP shop and we use SNAPSHOT as a quick way of backing up the whole environment (using volume-level snaps). As we are a 24*7 shop (almost anyway!) we can only use this approach when there is an outage e.g. prior to a mass transport. We do not use concurrent copy as all our backups are SHRLEVEL CHANGE - wouldn't it be nice if you could do a SHRLEVEL CHANGE snap? That one's high on my wish list. [...] 2530 45 15_Re: Audit trace13_McDonald, Ken20_Ken_McDonald@BMC.COM30_Wed, 6 Oct 1999 09:24:29 -0500380_iso-8859-1 Hi Sally,

Updates (INSERT, UPDATE, and DELETE) to DB2 tables are logged, however, read access is not logged, and therefore, traces are required to track SELECTs against tables. There are both log analysis utilities (i.e. BMC Patrol DB-Log Master for DB2, CA/Pt Log Analyzer) and SMF Trace analysis tools available as well to extract and format information. [...] 2576 20 22_IDMS to DB2 conversion14_Huang, Eugenia16_HuangE@AETNA.COM30_Wed, 6 Oct 1999 10:25:48 -0400342_- Hello friends:

We're interested in any shops that have experienced IDMS to DB2 conversion. Could you please let me know on the following from your experience on this kind of conversion projects:

1. Is there any third party tool(s) that handle IDMS to DB2 conversion? Their PROs and CONs? 2. What should we watch out for? [...] 2597 173 52_Re: CURRENTDATA and The Australian solution again...10_Rick Davis22_richard.g.davis@DB.COM30_Wed, 6 Oct 1999 09:16:12 -0600592_us-ascii Thomas, See DB2 V5 Admin. Guide, bookmanager ref 5.7.5.4.4 Effects of Isolation Values: "CURRENTDATA This option has two effects: For local access, it tells whether the data upon which your cursor is positioned must remain identical to (or "current with") the data in the local base table. For cursors positioned on data in a work file, the CURRENTDATA option has no effect. This effect only applies to read-only or ambiguous | cursors in plans or packages bound with CS isolation. For | SELECT statements in which no cursor is used, such as those | that return a single row, a [...] 2771 121 23_Re: DB2 UDB Connections0_14_dcreed@CSC.COM30_Wed, 6 Oct 1999 09:31:54 -0500562_us-ascii



You have to use the "show detail" For example: Auth Id Application Name Appl. Client Application Id Seq# C lient Client Client Client Host Application Id Seq# Host DB Name Host Handle D B Alias Node Release Codepage Release -------- -------------------- ---------- -------------------------------- ---- - ------- -------- -------- ---------- -------------------------------- ---- ----- --------------- -------- YIRTOP1 TER.EXE 153 902E668E.0498.991006142405 0001 D B2PROD SQL02011 850 xxxxxxxxx.BC39.991006142406 0000 DSN1 DSN05011 [...] 2893 73 33_Re: Neon--DB2 Connection: HELP!!!16_Skrivanek, David28_David.Skrivanek@DOWJONES.COM30_Wed, 6 Oct 1999 10:41:17 -0400507_iso-8859-1 David,

Please see the responses to you questions below.

-----Original Message----- From: David Brown [ mailto:brown@CS.UA.EDU ] Sent: Wednesday, October 06, 1999 10:07 AM To: DB2-L@RYCI.COM Subject: Neon--DB2 Connection: HELP!!!



Everyone:

I am working with a client who is just bringing up a major project management system application that uses an ODBC connection to DB2 using Neon Systems Shadow Direct. [...] 2967 18 15_Altering fields22_Leonardo Kenji Shikida17_kenji@DCC.UFMG.BR30_Wed, 6 Oct 1999 12:43:10 -0300304_us-ascii Is that true that DB2 doesn't allow to modify fields type after a table is created ? I said, how can I chance for example a "name" field from character(10) to varchar(30) for example ? Control Center doesn't allow me to do this, neither alter database SQL command.

thanks in advance [...] 2986 59 26_Re: IDMS to DB2 conversion0_34_SHILKMJ_SushilKumarJaiswal@PHL.COM30_Wed, 6 Oct 1999 10:39:27 -0400454_us-ascii Hello Huang, As per my knowledge, there are two ways of implementing the IDMS to DB2 conversion, I'm not sure of the name of the tools that are avaiable, but the approach would be -

1. Map the IDMS structure to DB2 and replace all the IDMS calls with SQL calls in the programs. The changes are minimal but the application doesn't make use of the relational power. The only advantage I see is the reduced development life cycle time. [...] 3046 16 31_query involving 2 different DBs22_Leonardo Kenji Shikida17_kenji@DCC.UFMG.BR30_Wed, 6 Oct 1999 12:47:46 -0300191_us-ascii I want to make a query involving 2 different DBs on the same DB2 machine. How do I do this ? Or it's better to put all tables in the same database ?

thanks in advance

K 3063 39 103_Central PA DB2 Users Group hosting 3 Classes in November - Willie Favero & Bonnie Baker coming to town!12_Cathy L Peck23_cathy.peck@HIGHMARK.COM30_Wed, 6 Oct 1999 10:50:10 -0400349_us-ascii Hi all, I have the distinct pleasure of hosting three DB2 classes in November here in Central PA! BOTH Willie Favero and Bonnie Baker are coming to town. This is way cool....

Below are the class specifics. Email/call me if you have any questions. Tks!!! Seeya

Cathy Peck - (717) 975-7396 / Cathy.Peck@Highmark.Com [...] 3103 54 26_Re: IDMS to DB2 conversion14_Mike Ockenfels28_Michael.D.Ockenfels@WCOM.COM30_Wed, 6 Oct 1999 09:45:29 -0500579_us-ascii There is a third party tool called DARS which converts IDMS to DB2. It's made by a company called International Software which is located in Canada. Their phone number is 1-800-295-7609. I'm sure they would be happy to work with you.

We started a project to convert an old IDMS system to DB2 but have put in on the back burner for now. We worked through several problems with the DARS code but, when we postponed the project, we were getting pretty close to a workable solution. Interbational Software is a small company but is very responsive to any issues [...] 3158 66 15_Re: Audit trace9_Sally Mir16_smir@BCBS-GA.COM30_Wed, 6 Oct 1999 10:53:00 -0400463_us-ascii Thanks for your reply, Ken! I am a huge BMC fan. Log Master was the first place I went when I got the request. So I do know how to get the information about what has been updated and by whom. But management has decided that they want to know who has been reading the data as well. I was hoping there might be some way outside of DB2 to figure this out. Of course, they want *historical* data, so turning on the audit trace wouldn't be fruitful now. [...] 3225 41 19_Re: Altering fields14_Mullins, Craig21_Craig_Mullins@BMC.COM30_Wed, 6 Oct 1999 09:53:18 -0500531_iso-8859-1 K:

Yes, it is true that you can not simply ALTER the data type of columns in DB2 tables once they have been created. In order to change the data type you must DROP and re-CREATE the object. You must be sure to save off all of the data, and related objects (indexes, views, security, etc.) before dropping it so that you can re-apply it all once the object has been re-created. There are several vendor tools on the market that automate this functionality making it simpler to perform, if you are interested. [...] 3267 30 19_Re: Altering fields14_Huang, Eugenia16_HuangE@AETNA.COM30_Wed, 6 Oct 1999 10:54:22 -0400280_- It is true. You'll need to drop and recreate the table that you want to change its column characteristics. If you want to keep the "old" data on that table, you'll need to unload it before you drop the table, and load it to the new format after you've recreated the table. [...] 3298 25 35_Re: query involving 2 different DBs17_Bruzdzinski, Mike24_MIKE.BRUZDZINSKI@SSA.GOV30_Wed, 6 Oct 1999 10:55:58 -0400520_iso-8859-1 Assuming that you do not want to hard code the DB qualifier, consider using an ALIAS. This will keep your coding and BIND parameters to a minimum.

-----Original Message----- From: Leonardo Kenji Shikida [mailto:kenji@DCC.UFMG.BR] Sent: Wednesday, October 06, 1999 11:48 AM To: DB2-L@RYCI.COM Subject: query involving 2 different DBs



I want to make a query involving 2 different DBs on the same DB2 machine. How do I do this ? Or it's better to put all tables in the same database ? [...] 3324 66 108_Re: Central PA DB2 Users Group hosting 3 Classes in November - Wi llie Favero & Bonnie Baker coming to town!11_Mayo Arthur22_Arthur.Mayo@M1.IRS.GOV30_Wed, 6 Oct 1999 10:58:38 -0400619_- Good Morning Cathy!

I am waiting on funding, but please include my name in Bonnie's class and Willie's Recovery class.

If this email isn't enough and I have to register, please advise.

Thanks

Regards,

Art > -----Original Message----- > From: Cathy L Peck [SMTP:cathy.peck@HIGHMARK.COM] > Sent: Wednesday, October 06, 1999 10:50 AM > To: DB2-L@RYCI.COM > Subject: Central PA DB2 Users Group hosting 3 Classes in November - > Willie Favero & Bonnie Baker coming to town! > > Hi all, I have the distinct pleasure of hosting three DB2 classes in > November > here in Central PA! > [...] 3391 51 46_Re: inserts into DB2 OS390 with unique indexes11_rick creech18_ykcirc@HOTMAIL.COM28_Wed, 6 Oct 1999 08:00:48 PDT349_- It used to be the case before DB2 version 4 that non-unique indexes could sometimes cause significant performance problems, and therefore users would add extra columns to the secondary indexes to make them unique. Of course, adding extra columns when you do not really need them causes extra overhead, makes the tree larger than needed, etc. [...] 3443 52 32_Re: Access to DB2 other than SQL15_Blaicher, Chris22_Chris_Blaicher@BMC.COM30_Wed, 6 Oct 1999 10:12:53 -0500469_iso-8859-1 Giorgio,

I do not know of any user-friendly way to access DB2 data without using SQL. Obviously the OEM's such as ourselves have ways of doing it, but they are not designed, nor intended, for general use.

I do not know why you would want to do it in any case. DB2 applications when properly designed and optimized can be very efficient. If you need more speed, then you probably need to tune the application or the DB2 subsystem, or both. [...] 3496 15 11_QBE support22_Leonardo Kenji Shikida17_kenji@DCC.UFMG.BR30_Wed, 6 Oct 1999 12:44:39 -0300125_us-ascii IBM invented QBE (query-by-example) queries. Do DB2 has any QBE built-in support ?

thanks in advance

K 3512 18 7_NetData22_Leonardo Kenji Shikida17_kenji@DCC.UFMG.BR30_Wed, 6 Oct 1999 12:40:29 -0300302_us-ascii I am trying to use NetData for LINUX, but It seems not to work well. It's quite strange because I installed on NT and it worked well. In LINUX, he sends me the message "can't find the running address" of the DTW_SQL library.

anyone has any suggestion ?

thanks in advance

K 3531 23 15_Re: QBE support17_Bruzdzinski, Mike24_MIKE.BRUZDZINSKI@SSA.GOV30_Wed, 6 Oct 1999 11:20:49 -0400336_iso-8859-1 Did you look at QMF?

-----Original Message----- From: Leonardo Kenji Shikida [mailto:kenji@DCC.UFMG.BR] Sent: Wednesday, October 06, 1999 11:45 AM To: DB2-L@RYCI.COM Subject: QBE support



IBM invented QBE (query-by-example) queries. Do DB2 has any QBE built-in support ?

thanks in advance

K 3555 76 33_Re: Neon--DB2 Connection: HELP!!!13_Ricardo Gomes30_ricardo.gomes@MELLO-IMPERIO.PT30_Wed, 6 Oct 1999 16:14:39 +0100601_iso-8859-1 Hello David

We have been using Shadow Server and Shadow Direct for some three years by now. Currently Shadow Server v 4.1 and DB2 v 5.1. One important fact: the communication's protocol is Interlink's TCPIP. The machine: 9672 R45 , 200 MIPS running OS390 2.6. ( bear in Mind: Machine has 3 LPAR's Production + Y2K + SAP/R3 ) The scope is 1) a maximum of 20 simultaneous active users from a potential 360, mining data from a 226 tables+30 views DataWarehouse. 2) most users running Ms-Access Read applications and only a few running read/write applications 3) One application in [...] 3632 26 11_Re: NetData14_Sunit C. Patke19_patkes1@STATE.MI.US30_Wed, 6 Oct 1999 11:22:12 -0400410_US-ASCII Have you specified the library where the netdata software in installed in the path ?

Sunit

>>> Leonardo Kenji Shikida 10/06 11:40 AM >>> I am trying to use NetData for LINUX, but It seems not to work well. It's quite strange because I installed on NT and it worked well. In LINUX, he sends me the message "can't find the running address" of the DTW_SQL library. [...] 3659 24 15_Re: QBE support20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 6 Oct 1999 11:27:22 -0400464_- Happen to attend presentation by Rocket Software at TRIDEX yesterday. They have QBE kind of software on various platform accessing DB2-390. This is marketed by IBM.

> -----Original Message----- > From: Leonardo Kenji Shikida [SMTP:kenji@DCC.UFMG.BR] > Sent: Wednesday, October 06, 1999 11:45 AM > To: DB2-L@RYCI.COM > Subject: QBE support > > IBM invented QBE (query-by-example) queries. > Do DB2 has any QBE built-in support ? > > thanks in advance > > K 3684 37 35_Re: query involving 2 different DBs14_Wayne Driscoll19_wdriscoll@QUEST.COM30_Wed, 6 Oct 1999 10:30:27 -0500407_iso-8859-1 It depends on which DB2 you are using. With DB2 OS/390, this can be done as long as both databases are in the same subsystem. With DB2 UDB for Windows, OS/2 or UNIX, the only way this can be done is through an additional product such as IBM's Datajoiner. This is because on these platforms, databases are distinct objects. Wayne Driscoll Product Developer Quest Software wdriscoll@quest.com [...] 3722 25 15_Re: QBE support14_Sunit C. Patke19_patkes1@STATE.MI.US30_Wed, 6 Oct 1999 11:25:00 -0400300_US-ASCII QMF supports QBE.

Start QMF Press PF11 to goto profile Change Language to QBE

HTH Sunit

>>> Leonardo Kenji Shikida 10/06 11:44 AM >>> IBM invented QBE (query-by-example) queries. Do DB2 has any QBE built-in support ?

thanks in advance

K 3748 50 41_Re: Catalog and Directory Inconsistencies12_craig patton21_prgpatton@HOTMAIL.COM28_Wed, 6 Oct 1999 08:31:00 PDT353_- Bob,

I have run the REPAIR DBD REBUILD without any ill effects and it corrected this type of problem. Have you run CHECKINDEX or DSN1CHKR against the index? The error messages can, possibly, be helpful in directing you to the proper corrective action.

P.S. make sure to back up the directory and catalog PRIOR and AFTER the REPAIR. [...] 3799 20 39_Accessing DB2 from Oracle forms via CAE14_James R. Brown25_brownjr@CI.RICHMOND.VA.US30_Wed, 6 Oct 1999 11:41:46 -0400492_iso-8859-1 Hi all,

We have a need to access data on DB2/MVS v4 from an Oracle forms application. DB2 Connect EE is installed on an NT server with SNA to VTAM via LU6.2. I can connect thru the command center or MS/Access with no problem, however when the application connects, Connect is issuing a new connect for each request. How can I change the set-up so that it will reuse the existing connection? I tried setting the "keepconnnect" parameter to true, but this had no effect. [...] 3820 79 29_Re: Grouping results of Union12_Joseph Burns25_joseph.burns@HIGHMARK.COM30_Wed, 6 Oct 1999 11:43:59 -0400487_us-ascii Probably the best way to do this is to use a program, but if that isn't feasible, then another option may be:

SELECT COALESCE(NST1.FLD1,NST2.FLD1) ,COALESCE(NST1.FLD2,NST2.FLD2) ,COALESCE(NST1.CNT,0) + COALESCE(NST2.CNT,0) FROM (SELECT FLD1, FLD2, COUNT(*) AS CNT FROM TAB1 WHERE .... GROUP BY FLD1, FLD2) AS NST1 FULL OUTER JOIN (SELECT FLD1, FLD2, COUNT(*) AS CNT FROM TAB2 WHERE ... GROUP BY FLD1, FLD2) AS NST2 ON NST1.FLD1 = NST2.FLD1 AND NST1.FLD2 = NST2.FLD2 [...] 3900 107 46_Re: inserts into DB2 OS390 with unique indexes0_19_Tim.Lowe@STPAUL.COM30_Wed, 6 Oct 1999 10:51:53 -0500623_us-ascii Even with type-2 indexes, non-unique indexes can still cause performance problems: During INSERT processing of duplicate values, the binary search to determine where to store this RID can reduce your performance. Of course, it depends on the number of duplicate values. During SELECT processing, if an index can be defined as unique then doing so can give the optimizer information that may enable it to improve processing in some cases. During UPDATE or DELETE processing, while the binary search of a type-2 index will perform much better than the sequential scan of a type-1 index, this may take more time [...] 4008 57 15_Re: QBE support0_19_Tim.Lowe@STPAUL.COM30_Wed, 6 Oct 1999 11:00:09 -0500338_us-ascii During the DB2 technical conference, Jim Ruddy from IBM gave an excellent presentation on DB2 UDB OS/390 REXX support, during which he shared an example REXX routine that could DRAW select, insert, update or load statements for a table. I am not sure if this satisfies your needs, but at least it is included free with DB2. [...] 4066 83 22_Re: ROWID partitioning11_James Drewe20_James.Drewe@AEXP.COM30_Wed, 6 Oct 1999 09:07:19 -0700361_- Marcus

I asked pretty much this question about a year ago to IBM because of a new developement we were doing. In this development, we would eventually have 10 TB tables spread across 254 partitions. Since we did not have a "solid" partitioning key, I asked Curt Cotner from Santa Teresa if the rowid would be a good randomizer. Here is his reply: [...] 4150 39 35_Re: query involving 2 different DBs0_15_leon@CA.IBM.COM30_Wed, 6 Oct 1999 12:31:52 -0400352_us-ascii Can you be a bit more specific. Where do you want to do this query from PC, UNIX, Web, SPUFI, batch etc.? When you say "2 different DBs" do you mean tables in different databases on the same DB2 for OS/390 subsystem or one table in DB2 UDB on UNIX and another in DB2 for OS/390 or one table in DB2 for OS/390 and the other in Oracle etc. [...] 4190 126 22_Re: ROWID partitioning0_19_Tim.Lowe@STPAUL.COM30_Wed, 6 Oct 1999 11:46:24 -0500356_us-ascii From what I have heard, the use of the ROWID to partition is an excellent way to "randomize" your data. However, as in all cases of clustering on a "random key", as you "scale up" you may face poor performance due to excessive syncronous I/O. If possible, it would be better to cluster on columns that can help avoid as much I/O as possible. [...] 4317 24 18_DB2 for the AS/40011_Danny Davis16_DTDAVIS@PCMH.COM30_Wed, 6 Oct 1999 13:03:18 -0400419_US-ASCII I have been doing DB2/OS390 for several years. The hospital I work for is buying a new application that only runs on the AS/400 platform and uses DB2. I am trying to determine how similar administering DB2 on the AS/400 is to administering DB2 on the mainframe. Is it more intensive, less intensive? How similar are the toolsets? How much will I have to know about the AS/400 to administer the databases? [...] 4342 22 15_Varchar columns15_Philip, Sibimon19_PhilipS@SEALAND.COM30_Wed, 6 Oct 1999 13:05:22 -0400446_iso-8859-1 > We use hardware compression in our environment. So do we need to use > VARCHAR column to save storage? VARCHAR take more CPU processing and more > programming, so I would like to avoid using VARCHAR if we do not gain any > storage Or any other benefits. > > Please let me know if anybody has any opinion on this. > > > Thanks > Sibimon Philip > 972-702-2515 - Office > 972-417-3597 - Residence > E-mail - Sibimon_philip@sealand.com > 4365 182 52_Re: CURRENTDATA and The Australian solution again...0_19_Tim.Lowe@STPAUL.COM30_Wed, 6 Oct 1999 12:13:40 -0500547_us-ascii Thomas, I am out of ideas, and I hope that IBM can resolve it. I am very curious and would like to hear the response from IBM.

I remember that you said that you were using data sharing, and I think that this would be the key. I know that with data sharing it is possible to get "old data" from the disk using isolation=CS and currentdata=NO. However, since you are doing an UPDATE first, then DB2 would have to lock this page (or row depending on locksize), and store it in the "local" DB2's bufferpool. Since the X-lock from [...] 4548 41 35_Re: query involving 2 different DBs12_craig patton21_prgpatton@HOTMAIL.COM28_Wed, 6 Oct 1999 10:17:35 PDT493_- Leonardo,

DAta is accessed via TABLEs and NOT at the database level. Other than perhaps a performance consideration, it does NOT matter in which database the tables reside. Generally, I prefer to keep JOINed tables in the same database (to avoid loading 2 different DBD's in the EDM Pool). However, if the EDM pool is sized correctly and BOTH DBD's are resident, Then it really should NOT matter where the two tables reside. This is actually one of the benefits of the DB2 DBMS. [...] 4590 30 30_Archlog copy from tape to disk12_PENROD, Dale15_dpenrod@SHL.COM30_Wed, 6 Oct 1999 10:16:03 -0700378_- I ran into this opportunity at our last Disaster Recovery exercise, can anyone help me out?

After the operating system was restored and the DB2 system was restored successfully, we started the forward recovery of the users data under DB2. The recovery procedure called in the ARCHLOG which lived on tape. You can already see the problem, single threaded recovery. [...] 4621 48 13_CANCEL THREAD74_ESTRIBEAU Christian (DN=TO38737, AVIONS RECIPIENTS, TOEXC01, AEROSPATIALE)42_christian.estribeau@AVIONS.AEROSPATIALE.FR30_Wed, 6 Oct 1999 16:20:01 +0200319_- Comment puis je CANCELER les 5 derniers THREADs? CANCEL DDF THREAD ne marche pas OMEGAMON DB2 n'y arrive pas. OPERTUNE de BMC n'y arrive pas. Un arret de DDF ne les detruit pas Seul un arret de DB2 est efficace (mais cher).

Configuration: application PC -----> TCPIP----> SP2------>SNA ----> DB2V4 OS/390 [...] 4670 53 34_Re: Archlog copy from tape to disk14_Alan Dishowitz23_adishowitz@RURALINS.COM30_Wed, 6 Oct 1999 12:43:38 -0500295_- Dale,

I've done this a couple of times, and I didn't run into any timestamp issues. Just be sure to run DSNJU003 twice after using IEBGENER: once to delete the old Archive Log from the BSDS, and again to define the DASD copy of the Archive Log to the BSDS.

Hope this helps, [...] 4724 11 11_Unsubscribe12_Pranit Kumar26_Pranit.Kumar@METAMORGS.COM30_Wed, 6 Oct 1999 13:14:36 -040082_iso-8859-1 Please remove me immediately from this list. Pranit.Kumar@metamorgs.com 4736 71 34_Re: Archlog copy from tape to disk11_Case, Missy15_CASEM@PKSIS.COM30_Wed, 6 Oct 1999 12:44:18 -0500275_- Dale,

We have used for the last 4 tests & one more next month the following JCL - you were right and someone was mistaken:

It worked great for us - good luck!* One tip - make sure your sizes match - this was set up to match our active/archive log sizes. [...] 4808 162 22_Re: ROWID partitioning12_Joseph Burns25_joseph.burns@HIGHMARK.COM30_Wed, 6 Oct 1999 13:58:37 -0400486_us-ascii Being cautious about syncronous I/O problems is good advice. In the original posting, you mentioned that you are dealing with more than 1tablespace that you want to partition.

If I remember right, the ROWID in V6 is not something which can be updated. DB2 just assigns it when you insert a row. If you have related information stored in tableA and tableB, then using the ROWID, it is possible that the first row in tableA will be related to the last row in tableB. [...] 4971 52 35_Re: query involving 2 different DBs11_rick creech18_ykcirc@HOTMAIL.COM28_Wed, 6 Oct 1999 11:39:09 PDT604_- If you are talking about different DB2 sub-systems, there will be overhead in accessing tables from different sub-systems that would be avoided if the tables were in the same sub-system. By the way, one DB2 sub-system will like to have as much of the machine resources for itself as possible. By having more than one sub-system on the same machine, the machine resources (cpu and memory) will have to bedivided between the sub-systems as well as everything else that is running on that machine. One of the advantages of a data-sharing system is that multiple members running on different machines [...] 5024 39 19_Re: Varchar columns20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 6 Oct 1999 14:46:53 -0400606_- Sibimon

This is more design issue. In future if you have a possibility to increase the column length then VARCHAR is ideal because V6 or V5 with APAR, lets you increase the VARCHAR length thru ALTER statement but if you stick with CHAR then you don't have that option left.

Regards Pillay > -----Original Message----- > From: Philip, Sibimon [SMTP:PhilipS@SEALAND.COM] > Sent: Wednesday, October 06, 1999 1:05 PM > To: DB2-L@RYCI.COM > Subject: Varchar columns > > > We use hardware compression in our environment. So do we need to use > > VARCHAR column to save storage? VARCHAR take [...] 5064 22 14_% OVERHEAD CPU17_Barbosa, Odimir J22_odimir.barbosa@EDS.COM30_Wed, 6 Oct 1999 14:41:55 -0500288_-



One user of our shop is asking the following question:

% CPU overhead trace accounting class 7,8 and performance class 30.

I would be grateful for any suggestion, feedback, ideas, etc....

Thanks in advance.

Odimir

DBA Team - EDS Brazil 5087 35 18_Re: % OVERHEAD CPU20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 6 Oct 1999 14:57:13 -0400325_- There is no easy answer to that. Since you need to know the overhead of PERFORMANCE CLASS(30). This class initial only those IFCIDs which are mentioned in the IFCID( ) key word of -START trace command, which could be any IFCID, hence your overhead could reach up to 100% and totally depends on what ifcid you specify. [...] 5123 50 30_Is row level locking that bad?27_Scott.Gjerdingen (Exchange)28_Scott.Gjerdingen@DHCMAIL.COM30_Wed, 6 Oct 1999 13:37:55 -0500506_iso-8859-1 I am looking to solve a contention issue and I am looking for feedback. Here is the scenario:

Lets say for example (and this is just an example) we have a table called WORK_REQUEST and this table has a unique identifier (call it IX1) which contains the type of work request and a timestamp associated with the request (first key card is approx 20). There is a second index that contains the identifier of a street address as a foreign key (call this IX2 - first key card very high). [...] 5174 18 31_Output from the TSO DSN Command15_Lentine Chuck O26_Chuck.O.Lentine@M1.IRS.GOV30_Wed, 6 Oct 1999 15:06:57 -0400551_iso-8859-1 I would like to write a CLIST or a REXX command to issue the -DISPLAY DATABASE(XYZ) LIMIT(*) command and have the output written to a file instead of displayed on the screen. If I run the command in batch the output goes to the SYSTSPRT file. So I tried allocating that file to a dataset but the data still displayed on the screen. So I tried allocating SYSPRINT and SYSTERM to files but the data still displayed on the screen. Has anyone successfully captured the command output from a DSN command in a dataset? If so, how did you do [...] 5193 60 41_Re: Catalog and Directory Inconsistencies18_COLLINS, BUFF (PB)23_EACOLLI@MSG.PACBELL.COM30_Wed, 6 Oct 1999 12:14:03 -0700644_iso-8859-1 Bob, How were your indexes altered to type 2. Did you alter them and then recover them with an OEM product? We encountered a problem similar to what you describe when using BMC to recover the indexes. BMC put a value of x'40' at +7F of page 0 of the index. The valid values are '00' (type 1), x'40' and x'50'. If you determine this is your problem, you can run a verify similar to what I list below: //Jobcard //********************************************************************* //STEP1 EXEC DSNUPROC,SYSTEM=xxxx,UID='REPAIR.IDX', // UTPROC='' //SYSIN DD * REPAIR OBJECT LOG YES LOCATE INDEX index name PAGE X'000000' VERIFY [...] 5254 36 35_Re: Output from the TSO DSN Command17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Wed, 6 Oct 1999 14:23:29 -0500493_iso-8859-1 Chuck,

I haven't personally tried this with the DSN processor, but I think you can use the REXX OUTTRAP function to capture the DSN output and then process the lines captured with an EXECIO yourself.

Hope this helps, Ken Kornblum Product Developer BMC Software

-----Original Message----- From: Lentine Chuck O [mailto:Chuck.O.Lentine@M1.IRS.GOV] Sent: Wednesday, October 06, 1999 2:07 PM To: DB2-L@RYCI.COM Subject: [DB2-L] Output from the TSO DSN Command [...] 5291 215 52_Re: CURRENTDATA and The Australian solution again...10_Rick Davis22_richard.g.davis@DB.COM30_Wed, 6 Oct 1999 14:18:16 -0600378_us-ascii Thomas, Tim, I tried to send this earlier but the server said it had a problem. I don't know if you got it or not. Why not just set up a little loop... startloop lock table in exclusive mode if locked add one to number select number into variable commit (I believe this commit releases the exclusive lock) if not locked try some number of times endloop. HTH, Rick [...] 5507 44 35_Re: Output from the TSO DSN Command16_Michael McCarthy24_Michael_McCarthy@DTC.ORG30_Wed, 6 Oct 1999 15:28:08 -0400402_us-ascii Chuck,

Before executing your DSN command in REXX, issue the OUTTRAP command to capture the screen output to a stem variable. An example would be: DUMMY = OUTTRAP('STEM.'). After the DSN command, you can use OUTTRAP('OFF') to reset the output to the screen. In CLIST you will need to set &SYSOUTTRAP to the number of lines you wish to capture, and use &SYSOUTLINE to retrieve them. [...] 5552 246 35_FW: Output from the TSO DSN Command17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM30_Wed, 6 Oct 1999 15:30:36 -0400785_- FYI, Here's a REXX exec named DSNX and DSNXSAY you can use as an example to run DSN commands. May need to be modified for your particular environment and DB2 subsystem names.

/*.REXX */ /*.*******************************************************************/ /*.DSNX: EXECUTE DB2 DSN COMMANDS & OUTPUT TO DISPLAY VIA EDIT */ /*.*******************************************************************/ /*.THIS EXEC IS A CUSTOMIZED VARIATION OF THE TSOX EXEC, WHICH CAN */ /*. BE USED FOR ALL TSO/IDCAMS/TSS COMMANDS. */ /*.*******************************************************************/ /*.FORMAT: DSNX SYSTEM */ /*.WHERE SYSTEM = S(SSID) */ /*.IF SYSTEM NOT PROVIDED THEN DEFAULT OR PROMPT FOR CORRECT SSID */ /*.NOTE: ARS SUBSYSTEMS GIVEN PREFERENCE FOR DEFAULT SSID */ [...] 5799 82 15_Re: QBE support12_Pat Mehrhoff47_Pat_Mehrhoff/MSI-CORP/MSI-INC@MSI-INSURANCE.COM30_Wed, 6 Oct 1999 14:24:30 -0500742_us-ascii Tim, Where is this DRAW routine located (you mentioned it was free)? Thanks, patrk











Tim.Lowe@STPAUL.COM@RYCI.COM> on 10/06/99 11:00:09 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: QBE support



During the DB2 technical conference, Jim Ruddy from IBM gave an excellent presentation on DB2 UDB OS/390 REXX support, during which he shared an example REXX routine that could DRAW select, insert, update or load statements for a table. I am not sure if this satisfies your needs, but at least it is included free with DB2. [...] 5882 10 17_Re: CANCEL THREAD15_Herb E. Johnson18_WongBaDan1@AOL.COM28_Wed, 6 Oct 1999 15:28:55 EDT40_us-ascii cancel thread please, thank you 5893 69 35_Re: Output from the TSO DSN Command11_rick creech18_ykcirc@HOTMAIL.COM28_Wed, 6 Oct 1999 12:44:28 PDT805_- I fyou want to do it from batch, here is the JCL:

//STEP01 EXEC PGM=IKJEFT01 //STEPLIB DD DSN=DB2.DBT1.SDSNLOAD,DISP=SHR //SYSTSPRT DD DSN=DB2.DBT1.GBPOOL.INFO, // DISP=(NEW,CATLG,DELETE), // UNIT=SYSDA,SPACE=(TRK,(1,1)), // DCB=(LRECL=80,RECFM=FB,BLKSIZE=0) //SYSUDUMP DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DBT1) -DISPLAY GBPOOL(GBP0) GDETAIL(*) -DISPLAY GBPOOL(GBP1) GDETAIL(*) END //

Rick Creech





>From: "Kornblum, Kenneth" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Output from the TSO DSN Command >Date: Wed, 6 Oct 1999 14:23:29 -0500 > >Chuck, > >I haven't personally tried this with the DSN processor, but I think you can >use the REXX OUTTRAP function to capture the DSN output [...] 5963 35 18_Re: Scripts in UDB0_15_leon@CA.IBM.COM30_Wed, 6 Oct 1999 15:48:57 -0400423_us-ascii I recomend you take a look at the Control Center (and Script Center) in V6.1. I think it has what you are looking for.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



Donald Tipton on 10/06/99 07:59:44 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: Scripts in UDB [...] 5999 119 34_Re: Is row level locking that bad?0_19_Tim.Lowe@STPAUL.COM30_Wed, 6 Oct 1999 14:51:38 -0500644_us-ascii Scott, I think you are on the right track.

The difference in locking overhead between LOCKSIZE=ROW and LOCKSIZE=PAGE depends on the number of rows per page. Obviously, there would be little difference in locking overhead between LOCKSIZE=ROW and and LOCKSIZE=PAGE with MAXROWS=1.

We are not running a data sharing environment, where the number of locks is more critical. We have gone to row-level locking on a few of our tables in order to reduce contention problems. Like you, we also have some batch processes that "process the rows" that online programs insert, and then delete them. These batch processes should [...] 6119 88 35_Re: Output from the TSO DSN Command0_19_Tim.Lowe@STPAUL.COM30_Wed, 6 Oct 1999 15:06:27 -0500397_us-ascii Ken, Excellent idea. I have used this a few times. I works very well. Example:

/************************** REXX **************************************/ Queue "-DIS thread(*)" Queue "END" Y = OUTTRAP(DB2CMND.,"*","NOCONCAT") "DSN S("DB2t")" rc_xx = rc ; if rc_xx > 8 then do;say 'rc=' rc_xx ;exit 4 ; end Y = OUTTRAP("OFF") DO I = 1 TO DB2CMND.0 SAY '!!!' DB2cmnd.I END exit 0 [...] 6208 21 34_Re: Is row level locking that bad?0_31_truman.g.brown@BELLATLANTIC.COM30_Wed, 6 Oct 1999 16:12:00 -0400557_us-ascii Tim Lowe drew a parallel between row level locking and page level locking with row isolation in one page. Hmmmm. Unless your rows are large enough or your table small enough, I'm not sure I would agree with his point.

Yes, the IRLM would do the same amount of work, but one enemy here is LOCK WAIT time, and the other enemy in DB2 is I/O. If multiple rows per page are possible they MIGHT be better off with row level locking. Add the D/S environment and you get the coupling facility (CF) activity with possible false contention woes. [...] 6230 16 28_DB2 and PL1 upgrade problems0_28_staci_stephenson@ACS-INC.COM30_Wed, 6 Oct 1999 15:15:23 -0500328_US-ASCII Has anyone encountered any problems with PL1 programs that access DB2 when upgrading PL1? We have a customer upgrading from PL1 V1.5 to V2.3 receiving S0C7 abends on PL1 programs (that use DB2) compiled with PL1 V2.3. Our DB2 is V4.1. Any suggestions/advice would be greatly appreciated.

Thanks, Staci Stephenson 6247 10 11_unsubscribe15_Herb E. Johnson18_WongBaDan1@AOL.COM28_Wed, 6 Oct 1999 16:22:59 EDT19_us-ascii unsubcribe 6258 11 27_How do I get off this list?15_Herb E. Johnson18_WongBaDan1@AOL.COM28_Wed, 6 Oct 1999 16:23:59 EDT95_us-ascii Hi, someone added me to the list and I would like to be removed. Any ideas? Thank you. 6270 19 14_RID in DB2 UDB14_Huang, Eugenia16_HuangE@AETNA.COM30_Wed, 6 Oct 1999 16:26:52 -0400422_- Greetings! UDB experts and colleagues:

Is there any easier way to track down the exact data of the "bad" row of an UDB table that we know its RID? Problem is that we've encountered "duplicate key" errors in an UDB application, so that we know the rids (from the error messages) but do not know which are the rows that are "bad". Has anyone encountered this similar problems and are willing to share with us? [...] 6290 42 18_Re: % OVERHEAD CPU14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 6 Oct 1999 16:29:07 -0400704_ISO-8859-1 The overhead for Actg 7,8 is less than 1%.

Performance traces can vary drastically depending on what you turn on.

Regards, Joel |======================================================= | Joel Goldstein | Responsive Systems Company |======================================================= | WEB Site | www.responsivesystems.com |======================================================= | Buffer Pool Tool for DB2 & DASD/Xpert for DB2 | >>> The Answers for your performance problems <<< |------------------------------------------------------- | Tel.(800) DB2-EXPErt Tel.(800) 322-3973 | Tel.(732) 972-1261 Fax.(732) 972-9416 |======================================================= 6333 41 31_Re: How do I get off this list?12_Janis Thomas20_thomasj@PLATINUM.COM30_Wed, 6 Oct 1999 15:34:09 -0500489_us-ascii Send an email to: listserv@ryci.com

Include this command in this email text: SIGNOFF DB2-L

If you have problems, contact me directly and I will remove you (thomasj@platinum.com).

Janis Thomas Associate list owner









"Herb E. Johnson" on 10/06/99 03:23:59 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Subject: How do I get off this list? [...] 6375 181 34_Re: Archlog copy from tape to disk16_Eric Harper st1719_eharper@ROSEINT.COM30_Wed, 6 Oct 1999 14:25:46 -0500621_iso-8859-1 I too have done this. No timestamp problems. But you have to let DB2 know that the device changed. Here's a sample of DSNJU003. I included the delete and registration of the active logs, in case anyone was interested.

//DB2UHOH JOB (98060),'DB2-DISASTER RCVY',CLASS=A, // NOTIFY=HARPERE,MSGCLASS=X,TIME=1439,REGION=4M //* //* ======================================================== //* ==== I N S T R U C T I O N S ==== //* ==== ==== //* ==== 1. CHG '*_*' 1 OR 2, FOR LOG COPY USED. ==== //* ==== 2. CHG DASDC? TO VOLUME FOR CURRENT ARCHLOG ==== //* ==== PLACEMENT. ==== //* ==== 3. CHG DASDP? TO [...] 6557 137 15_Re: QBE support0_19_Tim.Lowe@STPAUL.COM30_Wed, 6 Oct 1999 15:35:25 -0500492_us-ascii Pat, I have not tried this yet. We were told that the REXX support in DB2 is "was a no-price feature of DB2". (rather than "free") See your DB2 systems programmer.

The DB2 implementation APAR numbers for DB2 REXX support from my notes were V5=PQ29706, V6=PQ30219 My notes from the presentation also indicate "BETA testing only" currently for DB2 V6. Also, DB2 V5 is limited to only character data types, DB2 V6 does not have this restriction . (use the DIGITS function?) [...] 6695 39 19_Re: Varchar columns11_James Drewe20_James.Drewe@AEXP.COM30_Wed, 6 Oct 1999 13:25:25 -0700341_- If you find hardware compression acceptable, then you shouldn't need VARCHARs. Essentially you make the table a varying length table anyway with compression so the VARCHAR is extraneous work on the central processor. You will have to watch your NEARINDREF and FARINDREF statistics with compression to let you know when to reorganize. [...] 6735 101 35_Re: Output from the TSO DSN Command16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV30_Wed, 6 Oct 1999 13:48:42 -0700750_iso-8859-1 Try this; it works on my system. Just change the SSID's that you check for.

Duane

/* REXX - DISPDB ***************************************************/ /* */ /* DISPLAY DATABASE AND THREAD INFORMATION FOR THE SSID INDICATED */ /* */ /********************************************************************/ CLEAR /*ARG SSID */ SAY "ENTER SSID" PULL SSID CALL CHKSSID IF SSIDOK = "NOGO" THEN DO SAY "SSID IS REQUIRED - PLEASE ENTER A VALID SSID" CALL GETSSID END QUEUE "-DISPLAY DB(*) SPACE(*) RESTRICT" QUEUE "-DISPLAY DB(*) SPACE(*) LIMIT(*)" QUEUE "-DISPLAY THREAD(*)" QUEUE "END" X=OUTTRAP("DBNFO.",,"NOCONCAT") ADDRESS TSO "DSN SYSTEM("||SSID||")" X=OUTTRAP("OFF") /* */ /* PROCESS DATABASE INFORMATION */ /* */ DO I = [...] 6837 13 33_UDB Date conversion - SQL support14_Rebecca A Bond22_rebecca_a_bond@UHC.COM30_Wed, 6 Oct 1999 16:46:09 -0400237_us-ascii Is there any UDB supported single date conversion function that will take a date formatted as MM/DD/CCYY and return it formatted as CCYY/MM/DD? I've been told that such a function exists in other database products.

Thanks 6851 17 35_Re: query involving 2 different DBs18_Jorge Daniel Ortiz20_jdortiz@ARNET.COM.AR30_Sun, 3 Oct 1999 17:45:46 -0300 6869 29 37_Re: UDB Date conversion - SQL support14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Wed, 6 Oct 1999 17:15:06 -0400553_iso-8859-1 Try char(datefield,ISO)...should return CCYY-MM-DD.

Manas.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Rebecca A Bond > Sent: Wednesday, October 06, 1999 4:46 PM > To: DB2-L@RYCI.COM > Subject: UDB Date conversion - SQL support > > > Is there any UDB supported single date conversion function that > will take a > date formatted as MM/DD/CCYY and return it formatted as CCYY/MM/DD? I've > been told that such a function exists in other database products. > > Thanks > 6899 51 17_Re: CANCEL THREAD13_Leo Conchello18_lconche@USWEST.COM30_Wed, 6 Oct 1999 15:17:51 -0600583_us-ascii "ESTRIBEAU Christian (DN=TO38737, AVIONS RECIPIENTS, TOEXC01, AEROSPATIALE)" wrote:

> Comment puis je CANCELER les 5 derniers THREADs? > CANCEL DDF THREAD ne marche pas > OMEGAMON DB2 n'y arrive pas. > OPERTUNE de BMC n'y arrive pas. > Un arret de DDF ne les detruit pas > Seul un arret de DB2 est efficace (mais cher). > > Configuration: > application PC -----> TCPIP----> SP2------>SNA ----> DB2V4 OS/390 > > Merci. > > -DIS THREAD(*) LOC(*) DETAIL > DSNV401I - DISPLAY THREAD REPORT FOLLOWS - > DSNV402I - ACTIVE THREADS - > NAME ST A REQ ID AUTHID PLAN ASID [...] 6951 105 26_Re: IDMS to DB2 conversion0_22_Rohn.Solecki@MTS.MB.CA30_Wed, 6 Oct 1999 16:18:54 -0500389_us-ascii We went from a Non IBM environment to DB2 so I can't answer #1.

#2 - If you hire an external company to do conversion, keep control of the project. Don't just send them the code and let them go crazy.

Don't get me started ... ! OK, here is a 'short list' of things to consider. Most of them are so "common sense" that we didn't think about them ahead of time.h [...] 7057 53 19_Re: Varchar columns11_sanjay jain25_sanjay_jain@EMAIL.MSN.COM30_Wed, 6 Oct 1999 14:34:27 -0700755_- Jim,

Can you elaborate how compression might affect NEARINDREF and FARINDREF stats ?

Sanjay

-----Original Message----- From: James Drewe Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Wednesday, October 06, 1999 1:44 PM Subject: Re: Varchar columns



>If you find hardware compression acceptable, then you shouldn't need >VARCHARs. Essentially you make the table a varying length table anyway with >compression so the VARCHAR is extraneous work on the central processor. You >will have to watch your NEARINDREF and FARINDREF statistics with compression >to let you know when to reorganize. > >Jim > > > >From: PhilipS%SEALAND.COM@Internet on 10/06/99 10:10 AM >To: [...] 7111 17 35_Re: Output from the TSO DSN Command13_Dan S Brogdon22_Dan.S.Brogdon@AEXP.COM30_Wed, 6 Oct 1999 14:19:54 -0700300_- We have a batch job that does a display , i.e. -DIS BPOOL(BP0) DETAIL(INTERVAL) LIST(*) LSTATS via TSO, then parse/format the output, then load it to a DB2 table for analysis, (doing the same for GDETAIL and MDETAIL for several n-way plexes.) The last step executes QMF for a formated report. [...] 7129 216 34_Re: Archlog copy from tape to disk12_Roy Brickley27_roy_brickley@CCGATE.APL.COM30_Wed, 6 Oct 1999 14:58:14 -0700432_US-ASCII Eric,

I've seen two posts that have the DCB with BLKSIZE=28672, and I recall having to do that for DB V3 D/R test, so it would match the tape blocksize. When I copy this to a 3393 RAID device, it used many extents. When I copied it with no DCB on the SYSUT2 dataset, the IEBGENER ran fine and I got a BLKSIZE=24576 on the DASD file and much less DASD space (300 cylinders compared to 470+ cylinders for 28672). [...] 7346 41 25_Re: Plans and Tablespaces12_Ran Abeykoon23_Ran_Abeykoon@AMP.COM.AU30_Thu, 7 Oct 1999 09:35:17 +1000613_us-ascii Bill,

In additions to the reasons that Wayne Driscoll pointed out, one other reason that tablespaces are recorded in sysibm.sysplandep or sysibm.syspackdep table is as follows. As per Section 5.7.5.5.1 of DB2 version 4.1 Administration Guide, Modes of Locks Acquired for SQL Statements are dependent on the value of LOCKSIZE for the target table. Also, as per section 4.3.3.3 of DB2 Version 4.1 Release Guide, If you alter the lock size for a table space, it is not necessary to rebind plans that access its tables. But rebinding might be desirable to let DB2 consider the new lock size when [...] 7388 38 26_SP's & DB2 & Power Builder12_Dennis Raher27_Dennis_Raher@COMPUSERVE.COM30_Wed, 6 Oct 1999 20:17:57 -0400537_ISO-8859-1 I could really use some of the experience out there to figure out what isn't configured right. We are trying to execute a Cobol stored procedure from a Power Builder program, through DB2 Connect, to DB2 V 5 OS390. We are half the way there. The PB program executes the SP which runs fine and does what it is supposed to. The problem is in getting the information back to the PB program. This SP is a simple one, no cursors, just parameters being passed in and out. When control goes back to the PB program an SQLCA = 100 [...] 7427 52 18_Re: RID in DB2 UDB14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Thu, 7 Oct 1999 11:14:19 +1000288_us-ascii Eugenia

Is the UDB for OS/390, or UDB for NT or ...? If it's UDB for OS/390 then:

- The RID is a four byte field, the first 3 bytes are a page number, the last byte is an ID number within the page. (Large tablespaces use 5 byte RIDs with a 4 byte page number) [...] 7480 15 15_Re: QBE support22_Leonardo Kenji Shikida17_kenji@DCC.UFMG.BR30_Thu, 7 Oct 1999 00:56:59 -0300146_us-ascii "Bruzdzinski, Mike" wrote:

> Did you look at QMF?

No. What is this ? I haven't found anything in the DB2 information center 7496 16 35_Re: query involving 2 different DBs22_Leonardo Kenji Shikida17_kenji@DCC.UFMG.BR30_Thu, 7 Oct 1999 01:02:57 -0300413_us-ascii Jorge Daniel Ortiz wrote:

> For some reason we are getting dozens of messages from different senders, indicating that reply must be sent to your address. We would greatly appreciate that you do something to stop this.We do not want to be included in any list for any purposes whatsoever. This is an inconvenience that I am sure you do not wish to cause. Thank you for your help. Best wishes. [...] 7513 25 30_DBM1 asynch write buffer flush15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Wed, 6 Oct 1999 08:16:58 -0500345_us-ascii When DBM1 takes a checkpoint and has to flush buffers, is there any specific order to buffer flushing? Does DBM1 start at the lowest BP# and go upwards?

The reason for this is that I would like to put the "least important" writes in a BP# that is taken care of last by DBM1 such that the more important writes finish first. [...] 7539 70 26_SQL Return codes using ksh12_Kent Collins15_kcollins@WF.NET30_Wed, 6 Oct 1999 21:38:50 -0700212_Windows-1252 Is there a way to access SQLCA type information like, for example SQLCODE, from a ksh script?

Robert M. Collins Jr ( Kent ) IBM Certified DB2 UDB DBA Phone: 940.241.2242 Email: kcollins@wf.net 7610 42 41_Re: Catalog and Directory Inconsistencies13_David Chapman24_david_chapman@CGU.COM.AU30_Thu, 7 Oct 1999 14:45:52 +1000523_iso-8859-1 Bob,

If you're sure it's the index then stop the index and deleting the underlying dataset. If the index is stogroup defined then recover the index to recreate it. Otherwise, for a non-stogroup defined index, create the underlying dataset and then do the recovery.

David.



-----Original Message----- From: BOB JEANDRON [SMTP:BOB.JEANDRON@USDA.GOV] Sent: Wednesday, October 06, 1999 11:17 PM To: DB2-L@RYCI.COM Subject: Catalog and Directory Inconsistencies Sensitivity: Personal [...] 7653 55 19_Re: Altering fields22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 7 Oct 1999 06:59:39 +0100668_ISO-8859-1 Hi Craig Good to see you back on-line .. Les



______________________________ Reply Separator _________________________________ Subject: Re: Altering fields Author: "Mullins; Craig" at Internet Date: 10/6/99 9:53 AM



K:

Yes, it is true that you can not simply ALTER the data type of columns in DB2 tables once they have been created. In order to change the data type you must DROP and re-CREATE the object. You must be sure to save off all of the data, and related objects (indexes, views, security, etc.) before dropping it so that you can re-apply it all once the object has been re-created. [...] 7709 84 41_Re: Catalog and Directory Inconsistencies22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 7 Oct 1999 07:09:52 +0100517_ISO-8859-1 Bob I have encountered this problem myself (infact I raised a PMR at IBM for the very problem).

My situation was that when recovering a user defined index on the Catalog we received the 00E40317 message.



Quick fix

Should you ever need to get around this in the event of emergency then just create an index with a new name on the table using the same columns, type etc - then modify the stats in sysindexes to force the optimizer to choose that index over the problem one. [...] 7794 135 26_Re: IDMS to DB2 conversion14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Thu, 7 Oct 1999 16:19:44 +1000340_us-ascii Eugenia

Some years ago we (or rather, since it was before my time - I'm innocent, the business whose IT systems we manage) used some software houses to convert from DMS II (which is also a network dbms) to DB2. Some of the blunders that were made include:

- Converting "GET " to "SELECT * FROM ". [...] 7930 55 19_Re: Altering fields0_28_ruediger.kurtz@HUK-COBURG.DE30_Thu, 7 Oct 1999 08:27:31 +0100603_us-ascii Craig,

>I presume we're talking about UDB, so what are those vendors you're mentioning, apart from embarcadero and BMC; we're currently looking desperately for such a tool since we're about to launch our first application and are awaiting the first changes to structures.

TIA Ruediger Kurtz







Mullins, Craig wrote: > > K: > > Yes, it is true that you can not simply ALTER the data type of columns in > DB2 tables once they have been created. In order to change the data type you > must DROP and re-CREATE the object. You must be sure to save off [...] 7986 51 34_Re: DBM1 asynch write buffer flush22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 7 Oct 1999 07:28:37 +0100393_ISO-8859-1 Jim Interesting question.

BP writes .. I may know a man who knows ..

I would assume that when DB2 has been told that this page is now modifed and can be written to DASD the page then becomes available to be read .... I would be very interested to know if this is the case and the same man above may know the answer to that aswell .. I will get back to you on it. [...] 8038 37 11_Unsubscribe28_Shivamurthy, Dayananda (CTS)25_SDayanan@CHN.CTS-CORP.COM30_Thu, 7 Oct 1999 12:13:12 +053027_iso-8859-1



8076 68 34_Re: Archlog copy from tape to disk22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 7 Oct 1999 07:46:47 +0100622_ISO-8859-1 I agree with this .. we do this every time at our DR tests.

Leslie Fastrack Information Systems



______________________________ Reply Separator _________________________________ Subject: Re: Archlog copy from tape to disk Author: Alan Dishowitz at Internet Date: 10/6/99 12:43 PM



Dale,

I've done this a couple of times, and I didn't run into any timestamp issues. Just be sure to run DSNJU003 twice after using IEBGENER: once to delete the old Archive Log from the BSDS, and again to define the DASD copy of the Archive Log to the BSDS. [...] 8145 17 42_Accessing DB2 from Oracle forms via CAE(2)14_Richard Bourke25_Richard.Bourke@MERANT.COM30_Thu, 7 Oct 1999 09:04:18 +0200547_- James, I'm assuming you're accessing db2 via odbc from oracle forms via the open client adapter(oca). I've had similar issues with using the OCA 1.x, which is shipped / compatible with oracle forms 4.5. (I'm being diplomatic; actually, nothing much worked). The OCA version 2.x, shipped / compatible with oracle forms 5.0 is much, much better. They actually tested it against other databases and provide useful documentation ! If the upgrade to forms 5.0 is an option, then it's a must. See the file oca_info.pdf for lots of useful tips. Richard 8163 22 23_About Data Warehoousing0_17_wgcruz@BPI.COM.PH30_Thu, 7 Oct 1999 15:37:37 +0800553_us-ascii We are in the process of evaluating data warehousing, and are looking for the right database software. Has anyone using DB2 UDB for their DW encountered problem? We are also looking at TERADATA, a specialized software for DW. There are a lot of write up that tell us about TERADATA not being able to handle killer queries and CEO queries which UDB can handle thru the WLM. And with regards to UDB, TERADATA also say that to mix OLTP with DW, there will be a dramatic degradation in response time of the DW. And IBM told us that there will [...] 8186 33 49_Triggers / User Defined SQLSTATEs Recommendations13_Philip Nelson21_teamdba@ATTGLOBAL.NET30_Thu, 7 Oct 1999 09:40:30 +0100627_us-ascii I believe the following didn't make it to the list because of the server swap, so I'm resending. Sorry if you got it twice -





We're about to start implementing triggers in our DB2 UDB for AIX (V5.2) database.

I've a couple of questions relating to the "SIGNAL SQLSTATE" capability.

In the SQL reference there are some rules defining the valid values for these SQLSTATEs. But what would be recommendations as to how we should proceed. With five characters (some of them restricted) I don't think there's much chance of tying SQLSTATEs back to the triggers that produced them by [...] 8220 111 32_Distributed Connections Question0_20_vpacheco@AMADEUS.NET30_Thu, 7 Oct 1999 11:20:36 +0100600_us-ascii First of all I would like to thank the people involved in the effort of making this List happen. This is a valuable service that the DB2 community has learned to learn with. The perspective of loosing this support brought me some nightmares. Thanks!

Dear DB2 fellows,

Once again I have a puzzling situation I cannot understand and that the manuals do not teach. Hence I hope one of you have already came across a similar situation and can bring some light to this issue. We are running three DB2 (Version 5.1, level 99something), in different OS/390 (version 2.6) regions [...] 8332 39 27_RES: IDMS to DB2 conversion17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Thu, 7 Oct 1999 09:50:56 -0300475_iso-8859-1 It´s not so simple as one have said. There are lots of diferences between network model and relational model (obviously) and they are almost incompatible, so changing just database access commands is not possible.

I don´t know how you are using IDMS (network only? relational? both? COBOL/DC? ADS?) but if you are network only or almost network databases, i would suggest you to start redesigning your databases and rewriting your programs after that. [...] 8372 49 23_Re: DB2 UDB Connections20_BREIDENSTINE JOHN L.21_jlb@LEGIS.STATE.PA.US30_Thu, 7 Oct 1999 09:29:49 -0400254_us-ascii Matthew J. Emery

It is in hex format (at least on my machine) and crossing it to decimal works fine. Heres how it works.

HEX Number. AABA5174 AA = 170 BA = 186 51 = 81 74 = 116 Therefore IP # = 170.186.81.116

Regards John B. 8422 10 4_Test17_Flavio Y. Togashi23_flaviot@SOFTPLAN.COM.BR30_Thu, 7 Oct 1999 11:22:01 -030013_us-ascii Test 8433 41 27_FW: About Data Warehoousing32_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR30_Thu, 7 Oct 1999 16:35:46 +0300391_- Our DW is on OS/390 with DB2. We have some OLTP trans. goes into DW but no DW trans. go to Operational Data. Mixed workload is just working fine. Use different BPs for OLTP and DW if you're in Datasharing. Use Part. ts. and parallesim as mush as possible.



Cuneyt GOKSU GARANTÝ TEKNOLOJÝ Istanbul / TURKEY Tel : 0090-212-657 0404 Ext: 1218 mailto:Cuneytg@garanti.com.tr [...] 8475 126 35_Re: Output from the TSO DSN Command13_Scott Goodell24_Scott.E.Goodell@WCOM.COM30_Thu, 7 Oct 1999 08:23:09 -0500681_us-ascii I added a few lines to Tim Lowe's exec to allow the output to be browsed instead of scroll off. Thought I'd share it.

/************************** REXX **********************************/ PARSE ARG SSID CMD QUEUE CMD QUEUE "END" Y = OUTTRAP(DB2CMND.,"*","NOCONCAT") "DSN S("SSID")" RC_XX = RC ; IF RC_XX > 8 THEN DO;SAY 'RC=' RC_XX ;EXIT 4 ; END Y = OUTTRAP("OFF") "ALLOC F(TEMPDS) STORCLAS(STANDARD) AVGREC(U) SPACE(300 0), RECFM(F B) LRECL(80) DSORG(PS) REUSE" "EXECIO " DB2CMND.0 " DISKW TEMPDS (STEM DB2CMND. FINIS" ADDRESS "ISPEXEC" "LMINIT DATAID(DDVAR) DDNAME(TEMPDS)" "BROWSE DATAID(&DDVAR)" "LMFREE DATAID(DDVAR)" ADDRESS "TSO" "FREE F(TEMPDS)" EXIT 0 [...] 8602 12 34_Re: DBM1 asynch write buffer flush0_31_truman.g.brown@BELLATLANTIC.COM30_Thu, 7 Oct 1999 10:05:38 -0400244_us-ascii Just because a page is on the "write list" doesn't mean it can't be read, or updated. This will depend on its lock status at the time of the request. And of course the page will remain in the BP until it is stolen (no longer "in use"). 8615 43 34_Re: DBM1 asynch write buffer flush11_rick creech18_ykcirc@HOTMAIL.COM28_Thu, 7 Oct 1999 07:07:57 PDT379_- The reason that asynchronous writes are called asynchronous writes is because there is no waiting on locks or anything else by the transaction. Once a commit has taken place, the locks are released and data pages may stay in the buffer for a long period of time thereafter. The integrity of the data is assured because the log records are written synchronously at commit. [...] 8659 50 27_Re: About Data Warehoousing10_Rick Davis22_richard.g.davis@DB.COM30_Thu, 7 Oct 1999 09:34:21 -0600597_us-ascii At you're current juncture, I think the more appropriate questions would be:

How much data? How many users? How much data must be delivered to how many users at the same time? Is time-to-recover or reorg an issue? What aggregate data transfer rate must the proposed platform handle; avg and peak? What does the data look like; char, mixed, LOB, BLOB, GLOB and can the proposed DB and platform handle it? What will be the business-critical access type(s) and can the proposed DB and platform handle it? When can I benchmark the options? What methods will be available with the [...] 8710 177 26_Re: IDMS to DB2 conversion0_22_Rohn.Solecki@MTS.MB.CA30_Thu, 7 Oct 1999 09:30:53 -0500389_us-ascii Oh yah, James point about the multi column indexes is a good one I forgot about. We do a lot of SELECTing and sorting on a 3 column key. In the old system we could define 'group' names on the 3 columns (telephone numbers) so that we could deal with one variable rather than 3. In DB2 you might want to define a concatenation in a view to simplify coding. Using James example [...] 8888 24 8_SDSNEXIT15_Michael Bancale21_mbancale@TXFB-INS.COM30_Thu, 7 Oct 1999 09:52:07 -0500279_iso-8859-1 We currently have one SDSNEXIT loadlib for each DB2 Subsystem. I cannot recall the reason we choose this method but we did. Now one of our systems guys has asked if we could only have one SDSNEXIT loadlib to put in our link list and not have to steplib this guy. [...] 8913 60 30_Re: SP's & DB2 & Power Builder10_Rao, Peavy26_Peavy.Rao@TRANSAMERICA.COM30_Thu, 7 Oct 1999 09:45:48 -0500525_- Hi, I have spent last one month trying to PB7 work with ODBC driver to PB7, but still no success. As per Sybase and some discussion forums, the ODBC way of getting OUPUT parms from Stored procedure is thru FETCH. I had tested in with MS SQL Server, thru ODBC, it works. But the similar SP coded in COBOL and succesfully running thru COBOL driver program is not returning OUTPUT parms to PB. Note that the OUTPUT parms works only with PB7 (except Native SQL server driver which works even with earlier versions of PB). [...] 8974 41 30_Re: SP's & DB2 & Power Builder10_Rao, Peavy26_Peavy.Rao@TRANSAMERICA.COM30_Thu, 7 Oct 1999 09:51:29 -0500559_- Did you guys talk to IBM, they should have some info on these OUTPUT parms

> ---------- > From: Dennis Raher[SMTP:Dennis_Raher@COMPUSERVE.COM] > Reply To: DB2 Data Base Discussion List > Sent: Wednesday, October 06, 1999 7:17 PM > To: DB2-L@RYCI.COM > Subject: SP's & DB2 & Power Builder > > I could really use some of the experience out there to figure out what > isn't configured right. We are trying to execute a Cobol stored procedure > from a Power Builder program, through DB2 Connect, to DB2 V 5 OS390. We > are > half the way there. The [...] 9016 35 8_Re: Test12_David Thomas21_davidthomas@CHUBB.COM30_Thu, 7 Oct 1999 10:48:56 -0400380_us-ascii "Flavio Y. Togashi" on 10/07/99 10:22:01 AM

Please respond to DB2 Data Base Discussion List















To: DB2-L@RYCI.COM

cc: (bcc: David C Thomas/ChubbMail)





Subject: Test















Test 9052 45 12_Re: SDSNEXIT17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM30_Thu, 7 Oct 1999 10:34:32 -0500383_iso-8859-1 Hi Michael,

You probably did that so you could have individual DSNZPARMs for each ssid without having to establish a naming convention for them (i.e. they're all named DSNZPARM). Or maybe you're using different DSN3@ATH, DSNHDECP or DSNX@XAC versions (or some such thing) in various subsystems, and having these in your SDSNEXIT is an easy way to manage that. [...] 9098 57 12_Re: SDSNEXIT10_Bill Welch27_spgwdw@UCLINK4.BERKELEY.EDU30_Thu, 7 Oct 1999 09:03:41 -0700308_us-ascii We have one SDSNEXIT per DB2 subsystem, and that's the method I've seen at other shops.

If you use the method suggested, you'll have to have a different name for DSNZPARM for each DB2 subsystem, since this is located in SDSNEXIT, and specify that unique name in each -START DB2 command. [...] 9156 17 21_Bufferpool Allocation19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM30_Thu, 7 Oct 1999 08:56:57 -0700293_- What are the potential issues with a bufferpool that is allocated more space than it really needs to handle the objects assigned to it?

The first one that comes to mind is that the space isn't "available" for other pools, but are there others that we need to be concerned about? [...] 9174 68 12_Re: SDSNEXIT0_19_Tim.Lowe@STPAUL.COM30_Thu, 7 Oct 1999 11:20:15 -0500491_us-ascii Michael, If you go to 1 SDSNEXIT for multiple DB2 subsystems, then you really cannot use DB2 IMPLICIT Call Attach. (you can still use DB2 EXPLICIT call attach, but not IMPLICIT call attach.) DB2 implicit call attach requires a steplib concatenation with the right DSNHDECP module for the DB2 subsystem that you want to attach to. (The SDSNEXIT library is where this module is normally stored.) Not very many people use this, but I have found it to be handy in some situations. [...] 9243 18 81_DB2connect : SQL10007N Message "-1092" could not be retrieved. R eason code: "4".14_Masone, Franco24_F.Masone@CEDACRIOVEST.IT30_Thu, 7 Oct 1999 18:24:32 +0200310_- Hi to all, I am trying to configure DB2 Connect Personal edition version 5.2 on my NT, I would like to connect to our DB2 4.1 on OS390 the adding Database function terminate with this message :



SQL10007N Message "-1092" could not be retrieved. Reason code: "4". Any suggest ? Thanks in advance 9262 94 37_Re: FW: Neon--DB2 Connection: HELP!!!14_Gregg Willhoit21_gwillhoit@NEONSYS.COM30_Thu, 7 Oct 1999 11:42:18 -0500443_us-ascii Gregg Willhoit@NEON 10/07/99 11:42 AM

You folks have hit a known bug in IBM OE TCP/IP that we have seen at a few other customer sites. The APAR list for this problem follows. Notice the CLOSE STARTED message in the Trace Browse. This indicates that an OE Socket is being closed. A CLOSE EXECUTED message should follow immediately. However, because of the bug in OE Sockets, the close is never completed. The APAR list is: [...] 9357 39 85_Re: DB2connect : SQL10007N Message "-1092" could not be retrieved. R eason code: "4".14_Philip Gunning20_pgunning@BOSCOVS.COM30_Thu, 7 Oct 1999 13:10:10 -0400559_- Franco, Your getting this because you are not a local administrator on your NT workstation. You need a local admin NT userid to catalog databases. You can install ok, but when you go to add a database thats when you get the message. Some organizations don't like giving them out because that allows users to install software on company workstations. But you will need it or have someone that can create a local NT admin userid create one for you. Some places have a standard userid and pw that they create on all NT workstation images, that way the PC [...] 9397 51 25_Re: Bufferpool Allocation14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 7 Oct 1999 13:14:18 -0400657_ISO-8859-1 Message text written by DB2 Data Base Discussion List >What are the potential issues with a bufferpool that is allocated more space than it really needs to handle the objects assigned to it?

The first one that comes to mind is that the space isn't "available" for other pools, but are there others that we need to be concerned about? TIA Tim< ++++++++++++++++++++++++++++++++++ The only real issue is wasting memory that could be used effectively someplace else. The hard part is determining that the pool is too large, and what the best smaller size would be. Regards, Joel |======================================================= | [...] 9449 114 25_Neon: Thanks for the help11_David Brown15_brown@CS.UA.EDU30_Thu, 7 Oct 1999 12:24:46 -0500436_iso-8859-1 Gregg:

I appreciate your help on this and have forwarded it to them. I believe that they will be getting in touch with you shortly.

Thanks -- dave brown@cs.ua.edu



-----Original Message----- From: Gregg Willhoit Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Thursday, October 07, 1999 11:59 AM Subject: Re: FW: Neon--DB2 Connection: HELP!!! [...] 9564 57 34_Re: DBM1 asynch write buffer flush30_Humphris,Richard,CNA Insurance24_Richard.Humphris@CNA.COM30_Thu, 7 Oct 1999 10:58:54 -0500583_- I think you'll be happy to know that all your pages that DB2 want's to flush from the buffer are important to DB2. Even if the system crashes before writting out your important pages, DB2 knows they have to be written because they were already written to the active log (and all pages committed after the last completed checkpoint will be written when DB2 comes back up; and all rollback'ed changes and/or incompleted changes will be rolled back). In addition, locking has nothing to do with the flushing of buffer pages; locks are removed at commit and/or rollback, not when [...] 9622 49 27_Re: About Data Warehoousing22_Piontkowski Michael ML38_michael.piontkowski@ZCSWILM.ZENECA.COM30_Thu, 7 Oct 1999 14:41:15 -0400770_- Check out the article DB2 for OS390: Providing Business Value in a Query Environment in IDUG Solutions Journal Vol 6 No 2 http://www.idug.org.

IBM's DB2 for OS/390 website, http://www.software.ibm.com/data/db2/os390, has a link to white papers and case studies on data warehousing. Also check out http://www.software.ibm.com/data/



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

> ---------- > From: wgcruz@BPI.COM.PH[SMTP:wgcruz@BPI.COM.PH] > Sent: Thursday, October 07, 1999 3:37 AM > To: DB2-L@RYCI.COM > Subject: [DB2-L] About Data Warehoousing > > We are in the process of evaluating data warehousing, and are looking for > the > right database software. Has anyone using DB2 UDB for their [...] 9672 92 34_Re: Archlog copy from tape to disk22_Piontkowski Michael ML38_michael.piontkowski@ZCSWILM.ZENECA.COM30_Thu, 7 Oct 1999 14:55:15 -0400318_- We also copy the current and 2nd current ARCHLOG from tape to disk using IEBGENER and the same data set name. We update the BSDS (both of them) using DSNJU003 - remove tape ARCHLOG entry and add DASD ARCHLOG entry. We also alter the ICF catalog entries for the ARCHLOG data sets - helps keep things consistent. [...] 9765 32 32_JDBC connect to OS/390 subsystem12_Kirk Hampton16_khampto1@TXU.COM30_Thu, 7 Oct 1999 14:33:13 -0500562_us-ascii Hi all, good to have the group back 'on the air', long time listener, first time caller.

We are OS/390 v 2.6 running (I believe) the Websphere - formerly known as Domino Go - web server. We are successfully running Net.Data queries on our intranet against any of three DB2 subsystems that all run on the same LPAR (one test, two prod). Now we are experimenting with Java and JDBC, and running the sample Java application that comes with the JDBC installation. We can only seem to make this work (all it does is a SELECT * from SYSTABLES) if [...] 9798 90 21_DB2 SUBSYSTEM MONITOR16_Mary Ann Pickett24_pickettm@DOT.STATE.AL.US30_Thu, 7 Oct 1999 14:54:33 -0500593_iso-8859-1 Hello all,

We are looking for a good DB2 activity monitor and subsystem analyzer. We are currently using BMC's Activity Monitor and for most things it has worked very well. However we have recently implemented a Powerbuilder application that replaces most of our Accounting and Project Management programs and this has caused us to be running mostly dynamic SQL. We are experiencing some performance problems and the tracing capabilities for dynamic SQL has not been satisfactory with our current monitor. The operating system is OS390 and we are on version 5.1 of DB2. [...] 9889 105 25_Re: DB2 SUBSYSTEM MONITOR0_22_rob.crane@PLATINUM.COM30_Thu, 7 Oct 1999 15:26:36 -0500368_us-ascii Detector and Subsystem Analyzer work well for finding and tuning Dynamic SQL problems your applications may be having. They can report on both static and dynamic SQL very easily. I think you would be very happy Detector and Subsystem Analyzer.

If you would like more information please let me know, I can get you in touch with the correct people. [...] 9995 43 28_DB2 SUBSYSTEM MONITOR -Reply11_Jeff Schade15_JSCHADE@PCH.COM30_Thu, 7 Oct 1999 16:32:31 -0400668_- I believe that BMC has an application monitor called APPTUNE which would provide the SQL analysis you are looking for but I have never seen it in action.

Jeff Schade Publishers Clearing House 516-944-2673 jschade@pch.com

>>> Mary Ann Pickett 10/07/99 03:54pm >>> Hello all,

We are looking for a good DB2 activity monitor and subsystem analyzer. We are currently using BMC's Activity Monitor and for most things it has worked very well. However we have recently implemented a Powerbuilder application that replaces most of our Accounting and Project Management programs and this has caused us to be running mostly [...] 10039 30 37_CA's DB2 for OS/390 Tech Forum (U.S.)12_Janis Thomas20_thomasj@PLATINUM.COM30_Thu, 7 Oct 1999 15:34:17 -0500316_us-ascii Just a reminder that The DB2 for OS/390 Technology Forum is back and here to stay! Get a Wealth of DB2 Information -- Absolutely FREE! Learn about what's new in DB2 V6 and how to plan for DB2 Disaster Recovery. Don't miss your chance to hear about the hottest topics affecting the DB2 community today. [...] 10070 87 19_Re: Varchar columns16_Richard A Yevich15_ryevich@IBM.NET30_Thu, 7 Oct 1999 17:07:12 -0400603_iso-8859-1 DB2 Compression is row level compression, and results in varying length rows, with all the same implications as having a VARCHAR in a non-compressed row. Therefore, NEARINDREF and FARINDREF are always possible since any row updated will probably never be the same length. After the update, and due to the dictionary style compression (Lempel-Ziv Welch Algorithm -- or PKZIP style), changes generally do not compress or do not compress to the same length -- row length quite often increases. If there is no available space on the page, then you get the INDEFS as the row must be moved to [...] 10158 38 41_Re: CA's DB2 for OS/390 Tech Forum (U.S.)0_22_Rohn.Solecki@MTS.MB.CA30_Thu, 7 Oct 1999 16:13:25 -0500541_us-ascii Janis it's nice to see the new (US) schedule. Any idea when the schedule for the Canadian 'tour' will be coming out? TIA Rohn









Janis Thomas on 99/10/07 03:34:17 PM Just a reminder that The DB2 for OS/390 Technology Forum is back and here to stay! Get a Wealth of DB2 Information -- Absolutely FREE! Learn about what's new in DB2 V6 and how to plan for DB2 Disaster Recovery. Don't miss your chance to hear about the hottest topics affecting the DB2 community today. [...] 10197 35 32_-904 on package when not binding15_Philip, Sibimon19_PhilipS@SEALAND.COM30_Thu, 7 Oct 1999 17:46:08 -0400654_iso-8859-1 I think this may be a simple question for many DB2 gurus in the list. But I cannot think of any reason

We getting -904 with following message in the production.

DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90090, TYPE OF RESOURCE 00000801, AND RESOURCE NAME MQ.PRGM1.165C0B9D115E6246 DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXEAAL SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -130 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFF7E' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION [...] 10233 61 40_-904 on package when not binding - reply0_22_rob.crane@PLATINUM.COM30_Thu, 7 Oct 1999 16:55:25 -0500346_us-ascii You might wan't to check to see if other packages were getting bound into the same collection (MQ). Chances are your package was not in the EDM pool, and another package was being bound into the collection you are using.

-Rob ---------------------- Forwarded by Rob Crane/Pti on 10/07/99 04:55 PM --------------------------- [...] 10295 123 19_Re: Varchar columns15_Philip, Sibimon19_PhilipS@SEALAND.COM30_Thu, 7 Oct 1999 18:01:08 -0400433_iso-8859-1 Thanks to everyone for answering the question. But I got few more, if the compression is made by hardware

1. When is the compression happening? Is it at the time of writing into DASD from write buffer?. 2. When does de-compression happens? Is it while reading to buffer pool? 3. If the decompression is happening while reading from DASD, does it means one page need more than one buffer pool to hold the data. [...] 10419 42 19_Re: Altering fields16_Richard A Yevich15_ryevich@IBM.NET30_Thu, 7 Oct 1999 18:01:40 -0400569_iso-8859-1 There IS ONE ALTER that is being added to DB2 and retrofitted to V5 and that is to increase the length of a VARCHAR.

Richard Yevich +=====+======+ Information Technology Consulting, Data Modeling, Advanced Education RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930 Web: Email: info@ryci.com Offices: USA and Europe DB2® Family and Oracle® Specialists - Parallel Technologies VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®) Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999 [...] 10462 61 27_Re: About Data Warehoousing16_Richard A Yevich15_ryevich@IBM.NET30_Thu, 7 Oct 1999 18:12:06 -0400575_iso-8859-1 Have you looked at the customer reference stories and references on DB2 Warehouses on the www.software.ibm.com/db2 web sites? There are many VERY large and successful warehouses on OS/390 and UNIX. There are also white papers and many articles on the internet as well showing the success of these platforms and DB2 in the warehouse and BI area. Your choice of platform needs to be based on the business requirements for your warehouse, and where the source data is coming from. Many of those articles and white papers should point you in the right direction. [...] 10524 79 44_Re: -904 on package when not binding - reply15_Philip, Sibimon19_PhilipS@SEALAND.COM30_Thu, 7 Oct 1999 18:13:19 -0400271_iso-8859-1 We were getting the -904 at 13.36 today, the only program we bound was at 14.46. I also checked the DB2 log and it does not show me anything unusual. Can we execute a program without having the package or plan in the EDM pool? This program runs 24 hours. [...] 10604 70 34_Re: Is row level locking that bad?16_Richard A Yevich15_ryevich@IBM.NET30_Thu, 7 Oct 1999 18:23:16 -0400496_iso-8859-1 I have to agree with Truman since row level locking and page level locking only have the same overhead if there is one row per page. In benchmarks, done personally, with multiple rows per page and row level locking, versus MAXROWS 1 and page level locking, the results can be significantly in favor of the MAXROWS 1 -- in some cases. Also, the level of use of lock avoidance, readers versus writers, and the number of writers on a single page at the same time affect the results. [...] 10675 136 19_Re: Varchar columns24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM30_Thu, 7 Oct 1999 15:27:03 -0700410_iso-8859-1 Although I didn't post the initial question, I've been watching with great interest. This discussion in my team has posed a few additional questions.



First, we believe that if no length is supplied when inserting a varchar value, then DB2 determines the length essentially by checking for trailing nulls. Is there a way for DB2 to set the length by checking for trailing spaces? [...] 10812 156 19_Re: Varchar columns15_Blaicher, Chris22_Chris_Blaicher@BMC.COM30_Thu, 7 Oct 1999 17:33:56 -0500327_iso-8859-1 Compression, either hardware or software, is done at the row level before a row is added to a page. It is de-compressed after a row is selected from a page. All pages on DASD are the same size based upon the page size specified at creation of the tablespace. A page on DASD only takes a page in the bufferpool. [...] 10969 40 22_Re: ROWID partitioning16_Richard A Yevich15_ryevich@IBM.NET30_Thu, 7 Oct 1999 18:38:58 -0400354_iso-8859-1 > tablespaces are getting rather huge, and we could do with some relief when > it comes to batch jobs - i.e. parallelism. > Has anyone out there partitioned a tablespace on a column with a > ROWID data > type in V6 (OS/390)? If so, how easy was it? Did you have to change your > applications? Did you experience any parallelism benefits? [...] 11010 42 19_Re: Varchar columns16_Richard A Yevich15_ryevich@IBM.NET30_Thu, 7 Oct 1999 18:39:00 -0400490_iso-8859-1 > 1. When is the compression happening? Is it at the time of > writing into DASD from write buffer?.

DB2 compression occurs in the DB2 buffer. It has nothing to do with DASD compression.

DASD compression occurs in the storage device and has nothing to do with DB2 compression.

DB2 compression is row level and not all data and not all rows are compressed. But when that page gets to DASD, the entire page is DASD level compressed. This is all goodness. [...] 11053 62 86_Re: DB2connect : SQL10007N Message "-1092" could not be retrieved . R eason code: "4".0_15_leon@CA.IBM.COM30_Thu, 7 Oct 1999 18:40:52 -0400427_us-ascii Being a local administrator on an NT machine was a requirement on Db2 Connect pre-V6.1 systems. In v6.1 being a local administrator is no longer a requirement to catalog databases etc.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



Philip Gunning on 10/07/99 01:10:10 PM

Please respond to DB2 Data Base Discussion List [...] 11116 51 19_Re: Varchar columns16_Richard A Yevich15_ryevich@IBM.NET30_Thu, 7 Oct 1999 18:46:58 -0400409_iso-8859-1 > Although I didn't post the initial question, I've been watching with great > interest. This discussion in my team has posed a few additional > questions. > First, we believe that if no length is supplied when inserting a varchar > value, then DB2 determines the length essentially by checking for trailing > nulls. Is there a way for DB2 to set the length by checking for trailing > spaces? [...] 11168 167 19_Re: Varchar columns12_Kirk Hampton16_khampto1@TXU.COM30_Thu, 7 Oct 1999 17:49:49 -0500372_us-ascii



I have seen other posts here that reference 'hardware compression', and I have to ask, are we talking about something that happens in a DASD subsystem and/or cache controller, as opposed to the tablespace attribute COMPRESS YES, which would be, I suppose, classified as 'software compression' ? How is hardware compression enabled/disabled ? [...] 11336 97 19_Re: Varchar columns16_Richard A Yevich15_ryevich@IBM.NET30_Thu, 7 Oct 1999 19:06:19 -0400376_iso-8859-1 DB2 compression is either hardware or software. Hardware assisted DB2 compression is enabled if the feature is installed. Software is normally for backup.

There is also hardware compression in the DASD Storage devices and this is a completely different issue, and should not even be our concern. It is a native method used by the devices to store data. [...] 11434 11 69_Re: DB2connect : SQL10007N Message "-1092" could not be retrieved ...12_Phil Gunning15_Cicsesa@AOL.COM28_Thu, 7 Oct 1999 19:22:37 EDT38_us-ascii I believe he was on 5.2. Phil 11446 60 36_Re: -904 on package when not binding14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Fri, 8 Oct 1999 09:39:40 +1000461_us-ascii Sibimon

Check your DB2's MSTR JES Message log. There should be a DSNT376I message which tells you who else has a lock on the package.

/* standard disclaimer */ James Campbell DBA Hansen Corporation, Tooronga +61 3 9261 1802 james.campbell@energyis.com.au -----Original Message----- From: Philip, Sibimon [mailto:PhilipS@SEALAND.COM] Sent: Friday, October 08, 1999 7:46 AM To: DB2-L@RYCI.COM Subject: -904 on package when not binding [...] 11507 271 34_Re: Archlog copy from tape to disk0_29_Greg.Palgrave@BANKWEST.COM.AU30_Fri, 8 Oct 1999 08:55:54 +0800391_us-ascii Roy/Eric,

You have to have the same BLKSIZE. If they are different, DB2 will have difficulty finding some (but not all!) log records. We had problems with this ages ago in a DR test. DB2 kept giving an error message along the lines of "can't find log/archlog blah blah..." and would not complete the recover. But only for one or two logs in one or two jobs....very odd. [...] 11779 41 41_Re: CA's DB2 for OS/390 Tech Forum (U.S.)13_Marcel Lepage13_iml@COLBA.NET30_Thu, 7 Oct 1999 22:03:44 -0600585_iso-8859-1 Hi,

Anything for Montréal, Québec !!???

Thanks.

M. Lepage

Janis Thomas a écrit :

> Just a reminder that The DB2 for OS/390 Technology Forum is back and here to > stay! > Get a Wealth of DB2 Information -- Absolutely FREE! > Learn about what's new in DB2 V6 and how to plan for DB2 Disaster Recovery. > Don't miss your chance to hear about the hottest topics affecting the DB2 > community today. > > Coming the next couple of months to: > > 10/13/99 Philadelphia, PA > 10/14/99 Boston, MA > 10/27/99 Cincinnati, OH > 10/29/99 Atlanta, [...] 11821 78 35_Re: Output from the TSO DSN Command12_Ran Abeykoon23_Ran_Abeykoon@AMP.COM.AU30_Fri, 8 Oct 1999 12:13:12 +1000588_iso-8859-1 Chuck,

As others suggested you can capture the output of DB2 commands into a data set using the REXX OUTTRAP command. However it's a longer way of achieving the desired result. The best and the cleanest way to capture the output is by allocating a data set to a file called DSNTRACE which DB2 uses to write the results of DB2 commands. But remember to use the TEST(0) option when connecting to the DB2 sub system to guarantee the output is written to DSNTRACE file. I've used this technique and developed extremely useful utilities for a number of organisations to [...] 11900 30 34_Re: DBM1 asynch write buffer flush15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Thu, 7 Oct 1999 06:22:03 -0500349_us-ascii So, if a modified, uncommitted page is sitting in the bufferpool and it's checkpoint time, DBM1 does NOT place any lock whatsoever on that page, correct?

If we are getting waits on asynch writes, is the ONLY possibility that our log buffers are getting full at that precise time?

Very interesting.

Jim Lewandowski [...] 11931 75 34_Re: DBM1 asynch write buffer flush14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Thu, 7 Oct 1999 23:46:18 -0400393_ISO-8859-1 Message text written by DB2 Data Base Discussion List >So, if a modified, uncommitted page is sitting in the bufferpool and it's checkpoint time, DBM1 does NOT place any lock whatsoever on that page, correct?

If we are getting waits on asynch writes, is the ONLY possibility that our log buffers are getting full at that precise time? Very interesting. Jim Lewandowski> [...] 12007 35 44_TEST TO NEW LIST,PLS NOT REPLY..SORRY FOR IT6_zoujie20_zoujie@MAIL.DCAC.COM30_Fri, 8 Oct 1999 11:48:08 +080023_gb2312 TEST TO NEW LIST 12043 93 35_Re: Output from the TSO DSN Command12_Ran Abeykoon23_Ran_Abeykoon@AMP.COM.AU30_Fri, 8 Oct 1999 13:46:18 +1000373_iso-8859-1 Chuck, A small correction to my previous reply. Please use TEST(1) instead of TEST(0) when connecting to DB2 sub system. Also if you don't want the output written to both screen and the DSNTRACE data set use MSG('OFF') REXX command which stops messages written directly to the screen.



Regards,

Ran Abeykoon Technical Consultant/AMPLUS [...]