1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l June 2002, week 3 2 17 49_George Koegel/Rochester/IBM is out of the office.13_George Koegel18_gkoegel@US.IBM.COM31_Sat, 15 Jun 2002 02:45:23 -0400304_us-ascii I will be out of the office starting June 14, 2002 and will not return until June 17, 2002.

I will respond to your message when I return. If this is an Emergency for DBDC Performance, please contact Brian Beahan at Tieline 8-293-5074 or my manager Lauren Pauli at Tieline 8-293-2820 [...] 20 114 22_Re: DRDA TO SQL SERVER17_Robert J. Milonas20_xrjmbob@NETSCAPE.NET31_Sat, 15 Jun 2002 17:07:57 -0400608_iso-8859-1 When you say, Relational Connect only supports selects in V7. Is that DB2 OS/390 V7 or DB2 Connect V7 ? And when you say V2 of DB2; are you referring to SQL2 industry standard? Thanks







"Ruppel, Richard" wrote:

>Relational Connect can be used for DB2/390 also. The catch is that you have >to install DB2 UDB (on unix or windows) to make it work. > >We did it with Oracle. We could use SPUFI on DB2/390 (or a batch COBOL >program) and it would access data from an Oracle table. DB2/390 would go to >a DB2/NT database over DRDA. [...] 135 177 33_Re: DB2 Connect v7.2 / Datajoiner0_15_leon@CA.IBM.COM31_Sat, 15 Jun 2002 10:15:37 -0400355_us-ascii We have been talking about the future direction of the DataJoiner for over a year now. Judging by your posting, you are surprised. Let me set the record straight. DataJoiner product has been stabilized. This means we are not making new investments in enhancing its functionality. It is still fully supported and defects are being addressed. [...] 313 30 26_Disaster Recovery Scenario11_John Kruger16_jkruger@CDTG.COM31_Sat, 15 Jun 2002 21:08:14 -0500397_us-ascii DR Scenario ---------------------------------------------------------------------

DB2 UDB and applications are running on an AIX server in Building A This DB2 database is mirrored and the mirrored database is located in Building B residing on shark DASD.

We can assume if building A is destroyed by a disaster, the mirrored image located in building B, remains intact. [...] 344 25 19_Raid array failures14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sun, 16 Jun 2002 16:47:00 +1000448_US-ASCII In case you don't follow IBM-MAIN http://bama.ua.edu/cgi-bin/wa?A2=ind0206&L=ibm-main&P=R8911

"Anyone considering purchasing Shark DASD should ask their IBM contacts to explain the 70 odd volumes that 'failed' and were LOST at Royal Bank of Scotland in early May this year."

As the follow-ups make plain, it was a simple case of the statistics which every RAID array is subject to. Now, where are those backup tapes ... [...] 370 90 61_DB2 for z/OS and OS/390 Version 7 Selected Performance Topics16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM31_Sun, 16 Jun 2002 13:12:06 +0100393_us-ascii Hello list,

I have inadvertently deleted from my laptop the FIRST edition of the Redpiece relating to V7 Performance Topics - guess if I had a brain I'd be dangerous.

Anyway, if any of you happen to have it and could e-mail it to me, obviously offline, I'd be most grateful.

TIA.

Oh by the way, it was nice to meet so many people in Vienna last week! [...] 461 10 40_Re: High-availability Recovery Scenarios10_Linda Ball18_linda_ball@BMC.COM31_Sun, 16 Jun 2002 10:16:07 -0500316_- Please send requests for this presentation directly to me.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 472 127 34_Re: Problem with Outer Joins in V713_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Sun, 16 Jun 2002 13:07:38 -0500330_us-ascii Bernd,

I haven't experienced the problem that you mention. But there is an APAR which could be related (PQ61094). There are also a number of APARs that relate to V6 & V7, but since you have tested this out on V6 they may not be relevant (unless they were introduced by another V6 APAR which you do not have). [...] 600 94 22_Re: DRDA TO SQL SERVER17_Robert J. Milonas20_xrjmbob@NETSCAPE.NET31_Sat, 15 Jun 2002 17:07:57 -0400608_iso-8859-1 When you say, Relational Connect only supports selects in V7. Is that DB2 OS/390 V7 or DB2 Connect V7 ? And when you say V2 of DB2; are you referring to SQL2 industry standard? Thanks







"Ruppel, Richard" wrote:

>Relational Connect can be used for DB2/390 also. The catch is that you have >to install DB2 UDB (on unix or windows) to make it work. > >We did it with Oracle. We could use SPUFI on DB2/390 (or a batch COBOL >program) and it would access data from an Oracle table. DB2/390 would go to >a DB2/NT database over DRDA. [...] 695 57 18_Re: TCPKPALV ZParm12_Raymond Bell17_rbell@NZ1.IBM.COM31_Mon, 17 Jun 2002 09:58:54 +1200450_us-ascii Hey Philippe,

Even an hour would be OK for us, but I'm not gonna get our Network SP to change it as it wouldn't help us. The long-running (but idle) OTG transactions haven't failed, so a ping would find them valid and leave them there. Not what I want. I guess I'll just have to wait for our migration to OTG 8.1.7 - like someone else on the list is doing, and has posted something on recently. Must have another read of that... [...] 753 62 23_Re: Raid array failures12_Raymond Bell17_rbell@NZ1.IBM.COM31_Mon, 17 Jun 2002 10:03:24 +1200478_us-ascii Interesting discussion, James. Cheers from us all I imagine for the pointer. I guess we all have (or will/want to have) ESS devices around - we've got 2 F20s here and all seems OK so far. I guess it's a bit like the lottery in reverse - you buy a ticket in the hope you won't win. Can't say I'm too worried - mainly 'cause DASD recovery is the preserve of the DASD sysprogs - but at least now I know it's MTBF (before) and not MTTF (to) as I always thought of it. [...] 816 45 37_End-user coded SQL against Production13_Robert Wright17_rwright@LIC.CO.NZ31_Sun, 16 Jun 2002 17:41:58 -0500511_- I have been asked to justify why end users should not be allowed to code and execute dynamic SQL against our production database. Approximately 15 months ago I developed a process to SNAP the production subsystem overnight, copy the snapped data to new datasets and start a new subsystem (for enquiry only) so that end users can code their own queries and run them against production data that is < 24 hours old. I have been asked to review this as it uses 160GB of DASD and is only used by 3 end-users. [...] 862 73 41_Re: End-user coded SQL against Production12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Sun, 16 Jun 2002 19:04:03 -0500390_iso-8859-1 It has always seemed to me that service to our end users is our reason for existence.

The production copy to another subsystem is one way to provide that service.

What about data sharing with your ad-hoc users on one member and your "production" on another?

This saves the DASD, but gives still keeps the ad-hoc users out of the production bufferpools. [...] 936 88 42_Re: Oracle Transparent Gateway v8 question12_Raymond Bell17_rbell@NZ1.IBM.COM31_Mon, 17 Jun 2002 13:14:43 +1200352_us-ascii Hi Ajit,

We're in a similar position to you, except that we don't have Oracle on OS/390 (sighs of relief all 'round). We have AIX-based Oracle clients on both 7.3.4 and 8.1.7 talking to DB2 V5 (soon to be V7) via the OTG 4.0.1.1. Upgrading it to 8.1.7 like you are is on the long, but reducing, list of prereqs for the V7 upgrade. [...] 1025 62 43_Re: Problem in importing data using db2move12_Saima Jelani22_sjelani@COMSATS.NET.PK31_Mon, 17 Jun 2002 11:00:16 +0500633_iso-8859-1 helllo so please tell me how then i can import my database, i need to tranfer it on linux ----- Original Message ----- From: "David Harvey" Newsgroups: bit.listserv.db2-l To: Sent: Friday, June 14, 2002 2:29 PM Subject: Re: Problem in importing data using db2move



> Saima, > > Your problem is not related to FK's. > > The standard msg-ref explains the problem rather well. Take particular note > of the last para. You will need to remove GENERATED ALWAYS (at least under > the version of UDB you are currently running with). > > ............ > SQL3550W [...] 1088 14 42_Cindi Schultz-CA/PGI is out of the office.13_Cindi Schultz17_schultz.ca@PG.COM31_Mon, 17 Jun 2002 01:00:31 -0400392_us-ascii I will be out of the office from 06/07/2002 until 06/24/2002.

I will respond to your message when I return. Thanks!! :-)

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 1103 64 15_-30081N&DSNL32I11_Leila Sadat21_lshosaini@HOTMAIL.COM31_Mon, 17 Jun 2002 00:21:31 -0500478_- HI,i'm trying to enable replication between UDB7 on win2000 and DB2 6 on os/390 ,i do this steps 1-logon with ause with SYSADM auhority 2-issue connect command on COMMAND CENTER 3-then i connected to db2 on os/390,when i issuse ,display command on os/390...i see follow inforamation..

Sel Name St A Req ID Auth ID Plan ASID Token * * * * * * * * * --- -------- -- -- ----- ------------ -------- -------- ---- ----- SERVER RA * 0 db2bp.exe ALVANI DISTSERV 0047 243 [...] 1168 36 16_Re: CA Utilities10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 17 Jun 2002 09:57:11 +0200445_us-ascii Hi

If you have a huge amount of data you may have problems in sorting them, expecially with SyncSort. We had many problems using recovery and log accumulation utilities with very big table. The same is valid for memory consumption, once DB2 was killed by a log accumulation (anyway it was a P97D if I remember well). DR product was not so 'smart', expecially for datasharing. You can easily do it with few REXX & skeletons. [...] 1205 34 23_Re: Raid array failures10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 17 Jun 2002 10:38:06 +0200395_us-ascii Hi

The man who wrote is not a DASD specialist and he reported what he heard. So maybe something isn't correct. Probably it was a microcode failure as happened in old RVAs (I heard something similar about SHARKs) but I don't exclude the problem was worsened (or favoured) by some operational error, it happens. A dual DDM failure as they said. Very rare, I've never seen it. [...] 1240 33 36_ESAME - Anybody else switched it on?23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 17 Jun 2002 09:45:49 +0100466_- Hi all

We switched this on in our production environment this last weekend and was wondering if anybody else has aswell?

When we did this we went from Hiperpools to just VPs in Dataspaces .. I would have expect to just take the old values for VP and HP add them together and come up with the new VP value .. not so. We ran out of Virtual Storage (so DB2 said). In the end I have had to reduce the amount down quite a bit to get it all to fit .. [...] 1274 59 48_Peoplesoft using DSN1COPY getting IEC027I 737-1412_David Wilson30_David_A_Wilson@JOHNLEWIS.CO.UK31_Mon, 17 Jun 2002 10:38:16 +0100550_us-ascii Hi all,

Many thanks to those who replied to my previous query.

We have another 'nasty' in our Peoplesoft upgrade testing.

Environment is DB2 V6, OS/390 V 02.10.00, tablespace is 5Gb non partitioned segmented.

We are trying to use DSN1COPY to copy a live image copy to a test environment. The image copy has been archived by SMS and we recall the dataset prior to the image copy. Restored dataset spans two volumes. The DSN1COPY runs fine until reaching the end of the first input volume where it abends with [...] 1334 76 40_Re: ESAME - Anybody else switched it on?13_Adelino Regio28_adelino.regio@EDINFOR.EDP.PT31_Mon, 17 Jun 2002 12:14:26 +0100590_us-ascii Hi Leslie



We did the same operation a couple of weeks ago... And we also had to reduce the amount allocated to VP...

Best regards

Adelino Regio









"Pendlebury-Bowe, Leslie" @LISTSERV.YLASSOC.COM> on 17-06-2002 09:45:49

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: ESAME - Anybody else switched it on? [...] 1411 33 57_Re: Relationship between syssequences and identity column19_Hynes, Carol A. DOC27_Carol.Hynes@DOC.STATE.WI.US31_Mon, 17 Jun 2002 06:50:25 -0500488_iso-8859-1 You need to join two tables to get the information. Here's the query I use: SELECT DNAME, DCOLNAME, MAXASSIGNEDVAL FROM SYSIBM.SYSSEQUENCESDEP, SYSIBM.SYSSEQUENCES WHERE BSEQUENCEID = SEQUENCEID AND OWNER = 'DCDDBA' ORDER BY DNAME WITH UR;

-----Original Message----- From: Stephen Poulsen [mailto:stephen.r.poulsen@CO.MULTNOMAH.OR.US] Sent: Friday, June 14, 2002 4:05 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Relationship between syssequences and identity column [...] 1445 84 39_Calculate the size of a table in bytes!13_Sandeep Simon33_Sandeep.S.Alumootil@UKTRANSCO.COM31_Mon, 17 Jun 2002 13:01:28 +0100739_- Dear All,

I have a table as below,,

-------------------------tabel start-------------------------------------------------- Given a table CONTR_ID CHAR(12) ACTIVITY_NBR CHAR(6) PRODUCTION_DT DATE SPECIAL_FUNC_ID CHAR(2) START_TIME DECIMAL(2,0) STEP_ENERGY DECIMAL(13,0) STEP_CV DECIMAL(6,4) FLOW_RATE_HOURLY DECIMAL(11,5) EXPECTED_ENERGY DECIMAL(13,0) REQUEST_ST CHAR(1) NOMINATOR_ID CHAR(12) NOM_REASON_ID CHAR(2) APPROVER_ID CHAR(12) HISTORY_EXST_FLG CHAR(1) PCT_CHANGE DECIMAL(7,0) NOM_SUBMIT_TS TIMESTMP NOM_APPROVE_TS TIMESTMP UPDATER CHAR(8) UPDATE_TIMESTAMP TIMESTMP NOM_IND CHAR(1) ---------------------------------------------------------end of table----------------------------------------------------------- [...] 1530 100 40_Re: ESAME - Anybody else switched it on?23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 17 Jun 2002 13:34:17 +0100495_- Adelino

did you investigate why?

I am now on the case of San Jose to find out why ...

all the best

Les

-----Original Message----- From: Adelino Regio [mailto:adelino.regio@EDINFOR.EDP.PT] Sent: Monday, June 17, 2002 12:14 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: ESAME - Anybody else switched it on?



Hi Leslie



We did the same operation a couple of weeks ago... And we also had to reduce the amount allocated to VP... [...] 1631 39 40_DB2 VS PICK or UNIVERSE database servers15_Sharpe, Richard20_rsharpe@AMHERST1.COM31_Mon, 17 Jun 2002 08:51:54 -0400533_iso-8859-1 Hi

I have a strange management group here we have DB2 7.1 and 7.2 on AIX and Win 2000 and it is running great fast, users happy etc.. but management wants to look at "other" databases, has anyone any knowledge of these 2 odd ones PICK and Universe, Universe is one of the Informix products that IBM got when the bought Informix and PICK looks to be a old pre relational database. If you have any info on these for example any flaws or any detriments in those 2 so I can keep my DB2 instances would be of great [...] 1671 134 43_Re: Calculate the size of a table in bytes!14_Dash, Sushanta21_sushanta.dash@EDS.COM31_Mon, 17 Jun 2002 07:53:11 -0500533_iso-8859-1 Hi Sandeep , go through the Administration Guide Chapter 2-12. Everything is there. Consider the nullability(determining the average row length) as well the growth rate for the table( FREE SPACE consideration) for calculating the KB then converts this to bytes.

Hope this helps

With thanks Dash S.K.

EDS/India Solution Center Saturn/AAT Data Base Administration Steeple Reach. 25,Cathedral Road Chennai 600086 India, TamilNadu Hello 8113801 through 8113815 ext-2317 e-mail: sushanta.dash@eds.com [...] 1806 38 47_Answer: Calculate the size of a table in bytes!12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 17 Jun 2002 14:54:32 +0200466_iso-8859-1 Hello Sandeep Simon,

the data type byte count is

CHAR as defined (e.g. CHAR(12) are 12 bytes) DATE 4 bytes DECIMAL(x,y) x is number of digits, y is the precision TIMESTMP 10 bytes

The overall row length is 8 plus "n" plus "i".

Where 8 accounts for a header (6 bytes) and an ID map entry (2 bytes). "n" is 10 if your table has an edit proc, else zero "i" is the sum of maximum columns length of your columns in your table. [...] 1845 25 43_Re: Calculate the size of a table in bytes!14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Mon, 17 Jun 2002 07:54:55 -0500379_iso-8859-1 Sandeep,

you could start by multiplying the rowlength (in bytes) by the number of rows.

Your DDL does not show whether there are nullable columns or variable-length columns - if it does you will need to account for them also.

The lazy (but longer) way would be to create the table, load the data, then run RUNSTATS or equivalent ISV product. [...] 1871 161 31_Re: PAGESIZE 32k for SAPR3.APQD23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 17 Jun 2002 14:14:27 +0100301_- Michael

we have performed this kind of change many times. You will be fine. I have changed many tablespaces along the way in our massive SAP env and never had any problems from the SAP dictionary of the data whilst doing it.

I appreciate this mail is short but I am v busy today. [...] 2033 51 20_Re: plan and package17_Robert J. Milonas20_xrjmbob@NETSCAPE.NET31_Mon, 17 Jun 2002 09:23:27 -0400520_iso-8859-1 We converted when packages first became available about 8 years ago. The most confusing things was the name package. It sounded like it would contain plans. When in reality it was the other way around. If you have some really large programs with a lot of subroutines (we had CSP where everything was bound in one plan and it took an hour to rebind), it cuts down on rebinding everything. You only have to bind the package that changed. And the plan is just a list of packages or a XXXX.* to include all. [...] 2085 34 44_AW: DB2 VS PICK or UNIVERSE database servers12_Peter, Georg15_G.Peter@DZBW.DE31_Mon, 17 Jun 2002 15:23:17 +0200786_iso-8859-1 Rick,

I have found an url that may give you some information on the "pick database": http://members.ozemail.com.au/~dhona/pick.html

And for the universe database exists an url from IBM under http://www-3.ibm.com/software/data/u2/universe/

HTH.

With kind regards - mit freundlichen Gruessen, Georg H. Peter c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, EURurope e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- [...] 2120 9 0_10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 17 Jun 2002 15:52:55 +0200258_-

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 2130 102 44_Re: DB2 VS PICK or UNIVERSE database servers19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Mon, 17 Jun 2002 16:11:36 +0100447_- Rich,

I was asked by our strategy department to have a look at UniVerse last week : one of our business departments was considering a package solution which used this. Here's my report. based on a dig through the UniVerse manuals and a brief look at the product.

Note that UniVerse BASIC is based on Pick BASIC, which may explain where the reference to Pick came from : I think it's something which originated in AS/400-land. [...] 2233 33 43_Re: Problem in importing data using db2move12_David Harvey17_dmh@ONETEL.NET.UK31_Mon, 17 Jun 2002 09:04:57 -0500623_- Saima,

Without knowing the details, you may be able to take a variety of actions:

Method 1. Use individual IMPORT commands against the IXF-type files. For example, if you have (say) 20 tables/ixf-files, the IMPORT command may well be successful against most of them.

Where the IMPORT fails (for the same reason as DB2MOVE fails), simply alter the table to change the "Generated always" definition against the problem columns (i.e. allow NULLS). Then Import data for that table. Once the rows have been populated, it is up to you whether to alter the columns back to the original definitions. It [...] 2267 26 13_Date Question17_Adam Wiedenhaefer20_adamw@IMAGERIGHT.COM31_Mon, 17 Jun 2002 10:30:58 -0400304_iso-8859-1 Greetings all,

I am new to the DB2 environment so bear with me. I am trying to create a view that will show everything where the date is less then or equal to the current time the view is run. In SQL Server I could use the getdate() function to do this. How do you do this in DB2? [...] 2294 97 19_Re: -30081N&DSNL32I12_Phil Gunning28_pgunning@BREAKTHROUGHDB2.COM31_Mon, 17 Jun 2002 10:53:45 -0500505_- Leila You need to connect to the local DB2 UDB Wn2000 machie and then issue the command. There is no reason to be connected to OS/390 at all when issuing this command. HTH Phil







> HI,i'm trying to enable replication between UDB7 on win2000 and DB2 6 on os/390 ,i do this sen teps 1-logon with ause with SYSADM auhority 2-issue connect command on COMMAND CENTER 3-then i connected to db2 on os/390,when i issuse ,display command on os/390...i see follow inforamation.. [...] 2392 118 44_Re: DB2 VS PICK or UNIVERSE database servers15_Sharpe, Richard20_rsharpe@AMHERST1.COM31_Mon, 17 Jun 2002 11:06:25 -0400552_iso-8859-1 Thanks Philip this will help.

-----Original Message----- From: Philip Nelson (DBA) [mailto:teamdba@ATTGLOBAL.NET] Sent: Monday, June 17, 2002 11:12 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: [DB2-L] DB2 VS PICK or UNIVERSE database servers



Rich,

I was asked by our strategy department to have a look at UniVerse last week : one of our business departments was considering a package solution which used this. Here's my report. based on a dig through the UniVerse manuals and a brief look at the product. [...] 2511 72 17_Re: Date Question12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Mon, 17 Jun 2002 10:14:26 -0500455_us-ascii Hi Adam,

select column1 from test.table1 where column1 <= CURRENT DATE

--CURRENT DATE is similar to getdate() where column1 is a DATE or TIMESTAMP datatype.

Be carefully on SQL and DB2 dates, SQL Server has smalldatetime which is a windowed date datatype. DB2 does not have this. DB2 has a DATE, TIME, and TIMESTAMP datatypes. TIMESTAMP also has some differences in higher percision than SQL Server's DATETIME datatype. [...] 2584 76 41_Re: End-user coded SQL against Production10_Dale Smock20_Dale.Smock@BMSUS.COM31_Mon, 17 Jun 2002 10:39:08 -0500423_iso-8859-1 1. Yes - it is their data and we are in business to serve the customer's needs.

2. Yes - the application group writes customized end-user views for each group of users to optimize the SQL, especially joins and where clauses. The application group also designed summary tables that are populated nightly from the detail data (most with BMC Unload/Load), to provide faster access for end-user queries. [...] 2661 183 34_Re: Problem with Outer Joins in V70_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Mon, 17 Jun 2002 11:28:42 -0500336_us-ascii Hmmm, Was researching another item in my DB2 V7 release guide and ran across an interesting statement.

Subsystem parameter OJPERFEH is no longer used in DB2 v7. (So I guess this zparm shouldn't be a strong factor in clearing up your problem, unless you're on releases below db2 v7, which you indicate you are not). [...] 2845 110 52_Re: Peoplesoft using DSN1COPY getting IEC027I 737-1410_Mark Doyle19_mdoyle@JCPENNEY.COM31_Mon, 17 Jun 2002 11:53:39 -0500320_iso-8859-1 This is just a guess since I don't have access to the jcl, etc.

Did you specify both volumes for the input dataset? It looks as though DSN1COPY came to the end of the first volume & expected an 'end of file' marker & didn't get it, and didn't know to continue with the dataset on a second volume. [...] 2956 186 34_Re: Problem with Outer Joins in V713_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Mon, 17 Jun 2002 11:53:00 -0500347_us-ascii Shan,

There is an inconsistency in the documentation.

As per my previous reply, if you read the recent version of the installation guide, this ZPARM was reintroduced in June last year (see APAR PQ48485). But the installation guide still has the reference to the fact that it was no longer used in the GA release of V7. [...] 3143 135 52_Re: Peoplesoft using DSN1COPY getting IEC027I 737-1414_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Mon, 17 Jun 2002 12:11:59 -0500472_us-ascii Seems to me I had a similar problem a long time ago (coincidentally, also with a large PeopleSoft tablespace we were trying to copy from Production to one of our many "test" systems).

I think I "solved" it by IEBGENERing the multi-volume DASD dataset to TAPE, then using the tape dataset as input to the DSN1COPY restore. DSN1COPY liked the tape much better than the DASD dataset -- don't know why (I didn't really have time to find out at the time). [...] 3279 224 34_Re: Problem with Outer Joins in V70_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Mon, 17 Jun 2002 12:21:43 -0500786_us-ascii Thanks for clearing up the confusion and setting me straight.









Terry Purcell cc: Subject: Re: Problem with Outer Joins in V7 Sent by: DB2 Data Base Discussion List



06/17/2002 11:53 AM Please respond to DB2 Data Base Discussion List

















Shan,

There is an inconsistency in the documentation.

As per my previous reply, if you read the recent version of the installation guide, this ZPARM was reintroduced in June last year (see APAR PQ48485). But the installation guide still has the reference to the fact that it was no longer used in the GA release [...] 3504 49 36_Re: Question about update and select14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Mon, 17 Jun 2002 19:21:05 +0200549_- Hello,

you don't need a view to accomplish this. You can GRANT UPDATE to some subset of the columns of a table, and GRANT SELECT to the complete table. See syntax of GRANT.

Regards

Bernd





Am Fre, 14 Jun 2002 schrieben Sie: > > > Hi list, > > A user has made a request in which he asked us to accomplish the following: > > Create a view such that the application accessing the data through the view should be able to select all the data of the underlying table but at the same time should be able to [...] 3554 19 28_Views/Functions within Views0_20_bjnigh@HOUSEHOLD.COM31_Mon, 17 Jun 2002 11:26:11 -0700467_us-ascii Is it possible to create a table which includes columns that are defined with character and/or timestamp datatypes and convert the datatypes to other datatypes within a view? I.E., can a timestamp datatype be converted to a character field using a view or can a character datatype be converted to a numeric datatype using a view? If this is possible, will users be able to update, delete and insert into the table using the converted datatype as input? [...] 3574 45 32_Re: Views/Functions within Views14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Mon, 17 Jun 2002 20:44:37 +0200 3620 122 41_Re: End-user coded SQL against Production0_22_Rohn.Solecki@MTS.MB.CA31_Mon, 17 Jun 2002 13:59:59 -0500456_us-ascii Robert, I like Dale's answer #1, it is 'their' data. Since you are running and populating a totally separate production subsystem for only 3 users, it might make more sense (Dollars and Cents, 160 gig is a lot of storage) to allow them to run against production. But they (ie Senior Management) have to be aware of the potential costs, ie runaway SQL tieing up production. As well as Dale's suggestions, here are a couple more possibilities: [...] 3743 18 41_Definition of Collection-ID on DB2 OS/39012_Phil Trbovic28_phil.trbovic@DWD.STATE.WI.US31_Mon, 17 Jun 2002 13:39:09 -0500471_- Fellow listers, I'm looking for a good definition for how collection-ids relate to plans/packages and where the fall in the hierarchy of DB2.

Any help would be greatly appreciated....

TIA

Phil

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 3762 155 22_Re: DRDA TO SQL SERVER15_Ruppel, Richard26_Richard.Ruppel@ACS-INC.COM31_Mon, 17 Jun 2002 15:18:08 -0400422_iso-8859-1 Relational Connect is a part of DB2 UDB (NT and UNIX) so it's V7 of DB2 UDB for NT (and I would assume UNIX too - I only dealt with NT).

And my understanding for DJ was that it was based on DB2 V2 and some of the SQL enhancements have been added over the years, but not all of them. There have been a couple of messages from Leon about Data Joiner...he may be able to fill in the blanks about that. [...] 3918 55 45_Re: Definition of Collection-ID on DB2 OS/39011_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Mon, 17 Jun 2002 14:45:21 -0500484_us-ascii This came from IDUG in Dallas a couple of years ago!

Imagine a fisherman on a wharf catching fish and putting them in buckets. PLAN = the fisherman (Executable). COLLECTION = the buckets (Non-executable). PACKAGES = the fish (SQL).







Phil Trbovic cc: Sent by: DB2 Data Subject: Definition of Collection-ID on Base Discussion List DB2 OS/390 [...] 3974 34 57_DB2 7.2 EEE Single Node (AIX) Number of free Memory Pages11_Mike Fatula22_mfatula@AESSUCCESS.ORG31_Mon, 17 Jun 2002 16:04:35 -0400485_us-ascii We occasionally have problems with Not enough memory being available and receive SQL0995- All available memory for the database has been used.

The monitor shows us that the number of free memory pages is very low.

We have found that FTP's from our Mainframe to an NFS mounted file will cause the Memory to be used. DB2 loads will also cause much memory to be used. They release the memory they allocate and yes we may need to throttle back these utilities. [...] 4009 148 41_Re: End-user coded SQL against Production25_Smock, Dale, BmS - NMI-PM20_Dale.Smock@BMSUS.COM31_Mon, 17 Jun 2002 15:11:02 -0500549_iso-8859-1 Rohn's response reminded me of one other method we use to control end-user queries - the Resource Limit Table. We have different settings for SPUFI and DB2 Connect to help control "runaway" queries (like when the user omits a "where" clause on large tables). By monitoring average cpu for different queries and making adjustments, we found settings that allow "normal" queries to run but stop "runaway's". Occasionally we have to make temporary exceptions for individual users, but only after we have analyzed and tuned their query. [...] 4158 168 41_Re: End-user coded SQL against Production10_James Kwan19_jamesdb2kwan@CS.COM31_Mon, 17 Jun 2002 15:18:04 -0500577_iso-8859-1 Rohn,

I agreed everything you said except for the 1st point. Yes it is their data, but if something goes wrong, it will be your responsibility because you are the DBA and you are suppose to be an expert in this area and you should have the best recommendation.

Since there is only 3 users, I would talk to them to understand their requirements. May be all they need is some summary data which means you don't need to duplicate the whole 160G database. Let them understand the implications to the production data and let them decide which way they [...] 4327 58 35_Bind SPUFI from OS/390 into DB2 UDB12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Mon, 17 Jun 2002 15:21:52 -0500440_us-ascii I recently saw a message out here reference the article "Bind SPUFI from OS/390 into DB2 UDB". I am receiving the following error from OS/390 when trying to do the bind for SPUFI:

DSNT270I - THE FOLLOWING SQLCA INFORMATION WAS RETURNED FROM DSN, SQLCODE = -30061, SQLSTATE = 08004, SQLERRMT =, SQLWARN 0= ,1= ,2= ,3= ,4= ,5= ,6= ,7= ,8= ,9= ,A=, DSNT233I - UNSUCCESSFUL BIND FOR, PACKAGE = UDB100.DSNESPCS.DSNESM68.(), [...] 4386 184 41_Re: End-user coded SQL against Production14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Mon, 17 Jun 2002 15:22:40 -0500582_us-ascii I would add to Rohn's list:

- Implement reasonable DB2 Governor (and QMF Governor, if applicable) limits for these 3 users to run under, just in case they (or SAS, or whatever tool they're using ) accidentally write stupid SQL that will wreak havoc with the system...



Yes, 160 Gig is a lot of DASD storage, however (IMO) these days, DASD is cheap, it's PEOPLE that are expensive -- if these 3 users cause the DBAs & Sysprogs to have to chase down sporadic performance problems and also cause the other 1000 users on the system to have to twiddle [...] 4571 94 39_Re: Bind SPUFI from OS/390 into DB2 UDB11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Mon, 17 Jun 2002 15:53:49 -0500391_us-ascii I have never been able to get the DNS name recognized in "IPADDR". Change "UDBTEST.COM" to the hard coded TCP/IP address number then stop and start DDF... : )







Mark Buzzard cc: Sent by: DB2 Data Subject: Bind SPUFI from OS/390 into DB2 Base Discussion UDB List [...] 4666 136 39_Re: Bind SPUFI from OS/390 into DB2 UDB12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Mon, 17 Jun 2002 16:22:52 -0500453_us-ascii No luck. Same results. I had switched it earlier to the DNS name but forgot to stop and start DDF. I just switched it back to the ip address and recycled DDF. Hey, I am open to anything at this point!!

Thanks.







Jeff Faughn cc: Subject: Re: Bind SPUFI from OS/390 into DB2 UDB Sent by: DB2 Data Base Discussion List [...] 4803 174 39_Re: Bind SPUFI from OS/390 into DB2 UDB0_23_sjtoeniskoetter@AEP.COM31_Mon, 17 Jun 2002 17:40:00 -0400400_us-ascii It looks like you have the UDB node name as your location in the CDB. The location name should be a database alias in your database directory at that node.







Mark Buzzard cc: Sent by: DB2 Data Subject: Re: [DB2-L] Bind SPUFI from OS/390 into DB2 UDB Base Discussion List [...] 4978 110 27_Re: Unique index > 255 char14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 17 Jun 2002 22:56:34 +0100333_iso-8859-1 or if you are not in a hurry, you could always wait for a release of DB2 that might have increased index key lengths or even truly varying length keys.....

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 5089 123 21_Re: REORG PEND status14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 17 Jun 2002 22:57:42 +0100478_iso-8859-1 I'm interested to know WHY an alternative method is needed.

REORP means that data in your table/partition is potentially IN THE WRONG PLACE and must be 'corrected' with the REORG utility.

If there were a REPAIR .... SET NOREORP what sort of state would that leave your data in??



Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 5213 127 19_Re: Use of Select *14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 17 Jun 2002 23:01:10 +0100401_iso-8859-1 and finally (he says hopefully) and without seeming to sound too sanctimonious....

When coding an application program, surely the developer KNOWS what columns he wants from the table. (If not, maybe you need new developers). If so, it is only laziness to code SELECT * (and I could say the same of FOR UPDATE OF/FOR FETCH ONLY on cursors, but don't get me started on THAT one!) [...] 5341 158 19_Re: Use of Select *14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 17 Jun 2002 23:03:46 +0100390_iso-8859-1 Remember, that if you have SELECT * in a view and ALTER ADD columns to the underlying table, you MUST drop and recreate the view for those new columns to become part of the view. It does NOT happen automatically.

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 5500 142 16_Re: CAF Software14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 17 Jun 2002 23:05:18 +0100457_iso-8859-1 If you have the time (and the inclination) you can also do "really neat" things with CAF

Like - trapping non-zero, non+100 SQLCODES and dumping out the contents of the SQLCA (but, of course, your application programs already do that don't they....?) Like - displaying the contents of host variables when people complain that "this query doesn't find any data, even though I KNOW that the host variables have been populated correctly" [...] 5643 163 16_Re: CAF Software14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 17 Jun 2002 23:06:50 +0100397_iso-8859-1 Well, you can (sort of).

All you need is a selection of DSNHDECP modules, each specifying a different default subsystem name. Then you concatenate the right one in your STEPLIB to control the implicit connection.

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 5807 203 19_Re: Data Encryption14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 17 Jun 2002 23:30:10 +0100367_iso-8859-1 There is one problem with this idea of encrypting data in DB2.

Suppose you use an EDITPROC to encrypt the data. You have no scrambled the data when it is stored on disk. HOWEVER, ALL SQL access will automatically cause DB2 to pass the data through the EDITPROC and decrypt it. So, anyone with SELECT access to the data will be able to read it. [...] 6011 87 7_DSNDB0710_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Mon, 17 Jun 2002 16:57:49 -0600454_iso-8859-1 Hi List, We are running into short of sort space DSNDB07. Now I defined 6 Tablespaces which the first 5 Tablespaces with secondary extension 0 and only the last one has secondary extension. I would like to know if DB2 will use all the six tablepsaces for one query or it can only use one at a time. Can I create these tablepsaces with extended attribute so that they can be allocated on multi-volume? Any suggestion would be appreciated. [...] 6099 128 19_Re: Data Encryption14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Mon, 17 Jun 2002 18:37:45 -0500454_us-ascii Phil:

I was assuming that the only people with SELECT access to the table would be the ones who are supposed to see the data, which (depending on the sensitivity of the data) should be a fairly small group (I guess I should have stated that). Anyone without SELECT authority would not be allowed to see the data from within DB2, nor would anyone be able to see the data unencrypted from outside of DB2, which is what you would want. [...] 6228 160 33_Re: db2 connect driver for AIX 4?13_Kavita Mittal16_kavitam@ZOWI.COM31_Mon, 17 Jun 2002 17:28:33 -0700481_iso-8859-1 We just finished upgrading everything to FP6. FP 6 seems to have fixed the problem (so far no errors).

I tried the datadirect jdbc driver but it does not seem to support transaction processing. which is essential to us.

Thanks to everyone for their help!

-----Original Message----- From: Myron Miller [mailto:myronwmiller@YAHOO.COM] Sent: Tuesday, June 11, 2002 7:25 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: db2 connect driver for AIX 4? [...] 6389 44 19_Re: Data Encryption14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 18 Jun 2002 13:22:15 +1000579_US-ASCII Or, horror-of-horrors, keep the key somewhere under RACF protection, use SECURITY USER, then the UDF will go through RACF to determine if the user has the necessary authority.

Just flying a kite to see what happens James Campbell

On 17 Jun 2002 at 23:30, Grainger, Phil wrote:

> > A better alternative might be to use a UDF to encrypt/decrypt the data, > passing the encryption key as a parameter. > > Now, to SELECT the data, not only will you need SELECT access, you will also > need to know which UDF to use to perform the decryption [...] 6434 75 15_-30081N&DSNL32I11_Leila Sadat21_lshosaini@HOTMAIL.COM31_Tue, 18 Jun 2002 00:39:19 -0500478_- HI,i'm trying to enable replication between UDB7 on win2000 and DB2 6 on os/390 ,i do this steps 1-logon with ause with SYSADM auhority 2-issue connect command on COMMAND CENTER 3-then i connected to db2 on os/390,when i issuse ,display command on os/390...i see follow inforamation..

Sel Name St A Req ID Auth ID Plan ASID Token * * * * * * * * * --- -------- -- -- ----- ------------ -------- -------- ---- ----- SERVER RA * 0 db2bp.exe ALVANI DISTSERV 0047 243 [...] 6510 148 30_Re: sql probelm for subqueries8_duam lee20_duam_lee@HOTMAIL.COM31_Tue, 18 Jun 2002 09:16:57 +0000652_- Good Morning Terry, All your suggestins were good and worked fine. I really thankful to you. I would watch the soccer today. Our country is to play ITALY today and we are hope ful. See you with further probelms Duam



>From: Terry Purcell >Reply-To: DB2 Data Base Discussion List >To: DB2-L@LISTSERV.YLASSOC.COM >Subject: Re: sql probelm for subqueries >Date: Thu, 13 Jun 2002 23:15:35 -0500 > >Duam, > >Here are three possible solutions. The first is similar to the one >suggested >by Siva, although Siva's example showed a GROUP BY. > >You may not see a huge difference in [...] 6659 88 41_Re: End-user coded SQL against Production9_Ben Relle27_Ben.Relle@MORGANSTANLEY.COM31_Tue, 18 Jun 2002 10:55:03 +0100362_us-ascii I'd be asking how complicated the schema the users are querying against is. If it is a complex ER model then there's a case for building a well designed star schema datamart in order to get better read performance and meaningful results. This is backed up by your comments that the data is >24 hours old. This is a longer term solution, obviously. [...] 6748 40 52_Re: Peoplesoft using DSN1COPY getting IEC027I 737-1412_David Wilson30_David_A_Wilson@JOHNLEWIS.CO.UK31_Tue, 18 Jun 2002 10:54:43 +0100630_us-ascii Mark, David

Thank you for your input. I have tried specifying the volumes on the input DD, to no avail, and attach the job output below. Any other suggestions welcome!

(See attached file: job1)

Dave Wilson DBA X4248



*********************************************************************

Notice: This email is confidential and may contain copyright material of the John Lewis Partnership. If you are not the intended recipient, please notify us immediately and delete all copies of this message. (Please note that it is your responsibility to scan this message for viruses). [...] 6789 77 16_Re: CAF Software16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 14 Jun 2002 08:32:07 -0400509_iso-8859-1 Our CAF invocation is pretty simple JCL: //DELETE EXEC PGM=DBSAPGEN,PARM='DBP2,DBSAPGEN '

The COBOL program has LINKAGE SECTION. 01 DBSAPGEN-PARMS. 05 DBSAPGEN-PARM-LENGTH PIC S9(04) COMP. 05 DBSAPGEN-PARM-DB2-SUBSYSTEM PIC X(04). 05 FIRST-COMMA PIC X(01). 05 DBSAPGEN-PARM-PLAN-NAME PIC X(08). PROCEDURE DIVISION USING DBSAPGEN-PARMS. and MOVE DBSAPGEN-PARM-DB2-SUBSYSTEM TO SSID. MOVE DBSAPGEN-PARM-PLAN-NAME TO PLAN. SET CAF-OPEN TO TRUE. PERFORM CALL-ATTACH-FACILITY THRU CAF-EXIT. [...] 6867 62 16_plan and package13_Sandeep Simon33_Sandeep.S.Alumootil@UKTRANSCO.COM31_Fri, 14 Jun 2002 13:47:19 +0100303_- dear all,

i was reading through the archives when i came up with one particular post about plans and packages. could someone point to me what the advantages would be if we used packages instead of plans..we don't have any packages in 'ere.. are we among those few shops still having them? [...] 6930 25 31_RLF and distributed connections20_Martinez Pelaez, Ana25_ana.martinez@IBERDROLA.ES31_Tue, 18 Jun 2002 12:35:14 +0200432_iso-8859-1 Hello,

We would like to give different limits to our distributed connections to DB2 for OS/390 through DB2 Connect. We have tried to use the RLF. The correlation ID is not in the list of fields to choose. All the distributed DB2 connect threads have the same planname (DISTSERV). We cannot use the field AUTHID because we are using RACF groups.The other fields don't seem to be useful for us. Any suggestion? [...] 6956 23 13_xml extenders29_Sherri Sterling DIGEST NOMIME29_Sherri.Sterling@BPD.TREAS.GOV31_Tue, 18 Jun 2002 07:02:33 -0400393_us-ascii I have installed the db2 xml extenders administration wizard 7.2 fixpack4 on my windows 2000 pc. I would like to validate a DTD and/or edit a DAD stored in USS (unix mainframe services) directory on our mainframe. Does this wizard do that and if so how do I set it up. I have connected to our test subsystem but that is about all I can do unless the DTD or DAD resides on my pc. [...] 6980 26 24_DB2 UDB backup from java21_Venkata Reddy Vajrala30_venkatareddy@INFOTECH.STPH.NET31_Tue, 18 Jun 2002 16:41:40 +0530425_us-ascii Hi,

Is it possible to take backup of a DB2 UDB 7.2 running on Linux or NTdatabase from a Java program? The database will be in use always. i.e., I need to take online backup of a database.

I have tried to execute backup and export commands using PreparedStatement, and Statement. I have also tried to put these commands in stored procedures. But none of my attempts were successful. Any thoughts? [...] 7007 19 13_db2 compilers13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Tue, 18 Jun 2002 06:45:23 -0500550_us-ascii OS/MVS 390 just a question everyone know's that the DB2 pre processor does not expand copybooks when translating sql code within a Cobol program

are there any shops out there that uses copybooks with Sql code and how is it getting translated so that the compile is successful ??

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 7027 46 45_Re: Definition of Collection-ID on DB2 OS/39012_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US31_Tue, 18 Jun 2002 07:50:23 -0400348_us-ascii Since I haven't seen an answer to this yet, I will give it a go...

A collection is a logical grouping of packages. You use this logical grouping name (collection_id) when binding a plan. With the collection_id, you can specify either an individual package to be included in the plan or the entire collection (collection_id.*). [...] 7074 198 28_nested group by optimization11_Daniel Adam16_dadam@GRATEX.COM31_Tue, 18 Jun 2002 06:58:02 -0500419_- Hi, I need to optimize following type of a SQL statement:

select from t_premium, (SELECT XPT.NL_PREMIUM_ID, MAX (XPT.IID) AS IID FROM T_PREMIUM XPT, (SELECT P.NL_PREMIUM_ID, MAX (P.TWRITE) AS MAXT FROM T_PREMIUM P WHERE TWRITE <= :tWrite GROUP BY P.NL_PREMIUM_ID ) XSPT WHERE XPT.NL_PREMIUM_ID = XSPT.NL_PREMIUM_ID AND XPT.TWRITE = XSPT.MAXT GROUP BY XPT.NL_PREMIUM_ID ) XSP WHERE XP.IID = XSP.IID [...] 7273 29 17_Re: db2 compilers13_Jimmy Willett24_Jimmy.Willett@NCMAIL.NET31_Tue, 18 Jun 2002 08:07:57 -0400597_us-ascii We use copybooks here.... We place them in Librarian or Endeavor and the first step in the proc retrieves the program and copy members and passes on to the db2 step...

"Derez D. Lusk" wrote:

> OS/MVS 390 > just a question > everyone know's that the DB2 pre processor does not expand copybooks when > translating sql code > within a Cobol program > > are there any shops out there that uses copybooks with Sql code > and how is it getting translated so that the compile is successful ?? > > ================================================ > To change your subscription [...] 7303 219 31_Re: Setting up Distributed Data0_23_edie.richardson@SRS.GOV31_Tue, 18 Jun 2002 08:34:13 -0400276_us-ascii I finally got it working. I had two ZPARMS set wrong. I had maxdbat and condbat set to 0 so it would not let you connect. The bad part about it, in the manuals it does not state anywhere in the ddf section that these parms must be set to something other than 0. [...] 7523 45 17_Re: db2 compilers13_Jeremiah Eden28_JEREMIAH.EDEN@RADIOSHACK.COM31_Tue, 18 Jun 2002 07:55:17 -0500525_iso-8859-1 I may be wrong, but I think you are talking about DCLGENs which after creation are included in the SYSLIB concatenation on the DB2 precompile.

-----Original Message----- From: Jimmy Willett [mailto:Jimmy.Willett@NCMAIL.NET] Sent: Tuesday, June 18, 2002 7:08 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: db2 compilers



We use copybooks here.... We place them in Librarian or Endeavor and the first step in the proc retrieves the program and copy members and passes on to the db2 step... [...] 7569 40 17_Re: db2 compilers11_Jeff Schade15_JSCHADE@PCH.COM31_Tue, 18 Jun 2002 09:02:34 -0400450_US-ASCII All you have to do is use the following SQL include syntax for the copybook in your program just like you do for DCLGENs:

EXEC SQL INCLUDE copybook END EXEC

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

>>> ddlusk@HOUSEHOLD.COM 06/18/02 07:45AM >>> OS/MVS 390 just a question everyone know's that the DB2 pre processor does not expand copybooks when translating sql code within a Cobol program [...] 7610 11 18_Re: TCPKPALV ZParm16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Tue, 18 Jun 2002 07:44:36 -0500273_- Cheers Dude! pg

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 7622 12 20_IBM tools assessment14_Daryl spletzer24_dgspletzer@CMSENERGY.COM31_Tue, 18 Jun 2002 07:50:23 -0500437_- Has anyone had IBM in to do a DB2 Tools Assessment to review the DB2 tools IBM has that might replace present 3rd party DBA tool products. Also does IBM let you trial their tools?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 7635 36 11_Re: DSNDB0716_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Tue, 18 Jun 2002 07:58:51 -0500546_- Hi Grace,

DB2 should 'round robin' the sort 'work files':

(from the admin guide) 5.4.4.2 Understanding how sort work files are allocated

. . . When your application needs to sort data, the work files are allocated on a least recently used basis for a particular sort. For example, if five logical work files (LWFs) are to be used in the sort, and the installation has three work file table spaces (WFTSs) allocated, then: LWF 1 would be on WFTS 1. LWF 2 would be on WFTS 2. LWF 3 would be on WFTS 3. LWF 4 would be on [...] 7672 50 17_Re: db2 compilers15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Tue, 18 Jun 2002 09:02:21 -0400379_iso-8859-1 Derez,

use EXEC SQL INCLUDE copybookname END-EXEC and have your copybook in the same library as your DCLGEN members are for precompiler or concatenate your copy library into precompiler syslib DD or if DB2 V7 with latest COBOL you can use the COPROCESSOR feature to eliminate the precompile step

HTH

Bob Lawrence DBA Boscov's Dept Stores LLc [...] 7723 57 35_Re: RLF and distributed connections11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Tue, 18 Jun 2002 08:28:12 -0500451_iso-8859-1 You need to put a row out there for each package. You have to know what each package does in order to set the ASUTIME to something that's proper. Here's a row from our table.

NAME TYPE LENGTH NULL VALUE AUTHID CHAR 8 PLANNAME CHAR 8 ASUTIME INTEGER 4 N 2950800 LUNAME CHAR 8 PUBLIC RLFFUNC CHAR 1 2 RLFBIND CHAR 1 RLFCOLLN CHAR 18 NULLID RLFPKG CHAR 8 SQLLD200 RLFASUERR INTEGER 4 Y RLFASUWARN INTEGER 4 Y RLF_CATEGORY_B CHAR 1 [...] 7781 39 17_Re: db2 compilers13_Jeremiah Eden28_JEREMIAH.EDEN@RADIOSHACK.COM31_Tue, 18 Jun 2002 08:34:17 -0500352_iso-8859-1 If you are going to start using the new COPROCESSOR and COBOL, you will want to put on recently closed APAR PQ55957 for DB2 V7 and APAR PQ56011 or PQ57522 for whichever release of COBOL you have that supports the COPROCESSOR.

"MSGDSNH104I ON COBOL COMPILE WITH SQL OPTION that include hyphens ('-') in the SQL Identifier names." [...] 7821 76 52_Re: Peoplesoft using DSN1COPY getting IEC027I 737-1414_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Tue, 18 Jun 2002 08:35:35 -0500480_us-ascii Dave: Your "job1" attachment didn't come through (or my e-mail system wouldn't handle it, I don't know which). Perhaps you could copy the job output into your message text and re-post?

Thanks, David











David Wilson cc: Sent by: DB2 Data Subject: Re: Peoplesoft using DSN1COPY getting IEC027I Base Discussion List 737-14 [...] 7898 46 24_Re: IBM tools assessment0_16_khampto1@TXU.COM31_Tue, 18 Jun 2002 08:55:35 -0500571_us-ascii Yes, and yes. You may contact me off-list.

Kirk Hampton DB2 OS/390 Sysprog IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390 TXU Business Services Dallas, Texas









Daryl spletzer @LISTSERV.YLASSOC.COM> on 06/18/2002 07:50:23 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: IBM tools assessment [...] 7945 72 17_Re: db2 compilers13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Tue, 18 Jun 2002 08:57:55 -0500621_us-ascii we're v6 but will be going to v7 in august







Jeremiah Eden To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: db2 compilers Sent by: DB2 Data Base Discussion List



06/18/2002 08:34 AM Please respond to DB2 Data Base Discussion List











If you are going to start using the new COPROCESSOR and COBOL, you will want to put on recently closed APAR PQ55957 for DB2 V7 and APAR PQ56011 or PQ57522 for whichever release of COBOL you have that supports the COPROCESSOR. [...] 8018 40 17_Re: db2 compilers14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 18 Jun 2002 23:54:52 +1000549_US-ASCII You can also use ISRLEMX - part of ISPF. Doesn't have REPLACING, however. I presume that COBOL V2.2 and DB2 V7 will make this irrelevent.

James Campbell

On 18 Jun 2002 at 8:07, Jimmy Willett wrote:

> We use copybooks here.... > We place them in Librarian or Endeavor and the first step in the proc retrieves the program and copy members and passes on to the db2 step... > > "Derez D. Lusk" wrote: > > > OS/MVS 390 > > just a question > > everyone know's that the DB2 pre processor does not expand copybooks when > [...] 8059 18 19_Last name search...16_Stolkarts, Sofia28_sofia.stolkarts@CAREMARK.COM31_Tue, 18 Jun 2002 08:54:41 -0500319_iso-8859-1 We need to do a "wild card" last name search.

We talked about adding a column that would have a part of a last name and creating an index by this column.

My question is, how can I research what is an optimum number of characters that users have to enter in order to do a last name search? [...] 8078 48 19_Re: Data Encryption12_McKown, John22_JMckown@UICIINSCTR.COM31_Tue, 18 Jun 2002 09:15:05 -0500554_iso-8859-1 Except that you must "protect" the encryption key somehow. So if you have a batch program or VB script or ??? which needs access to the column you must either (1) hard code the key in the program (and then don't let anybody see the source code) or (2) ask the "user" for the key. Number 2 requires an interactive response from the user, who will likely start complaining or will just write it down because they "just can remember all that stuff." In batch, number 2 they would likely put the "key" either in-stream so anybody who can see [...] 8127 35 40_Re: High-availability Recovery Scenarios14_Agrawal, Navin21_navin.agrawal@EDS.COM31_Tue, 18 Jun 2002 09:06:15 -0500457_- Hi Linda,

I'm interested in going through the presentation. Please send me one. Our shop maintains database for a Bank in Australia, and we run into precarious situations more often than not.

Thanks in Advance.

Regards Navin Agrawal

-----Original Message----- From: Linda Ball [mailto:linda_ball@BMC.COM] Sent: Sunday, June 16, 2002 8:46 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: High-availability Recovery Scenarios [...] 8163 18 32_ASP calling DB2 Stored Procedure17_Robert J. Milonas20_xrjmbob@NETSCAPE.NET31_Tue, 18 Jun 2002 10:14:18 -0400416_iso-8859-1 I wrote a procedure using Stored Procedure Builder. It tests out okay from SPB. Granted authority on procedure to public at all locations. When we try to access it from an ASP (WEB)application specifying the correct schema.procedure name , we get a -440. We have accessed DB2 through a SQLServer link server from the same application but haven't been able to call a stored procedure. Any thoughts??? [...] 8182 16 16_last name search15_Sofia Stolkarts28_sofia.stolkarts@CAREMARK.COM31_Tue, 18 Jun 2002 09:03:38 -0500310_- We need to do a "wild card" last name search.

We talked about adding a column that would have a part of a last name and creating an index by this column.

My question is, how can I research what is an optimum number of characters that users have to enter in order to do a last name search? [...] 8199 72 46_Union ALL Problem with DB2 Version 7 on OS/39013_Trbovic, Phil28_Phil.Trbovic@DWD.STATE.WI.US31_Tue, 18 Jun 2002 09:24:18 -0500530_iso-8859-1 Fellow listers, The client I am currently working for is trying to upgrade to the most current version of DB2 and has run into a problem with a program that contains a UNION ALL. Here is a synopsis of their problem:

The problem is related to a program with Union All statements being bound in v7 with a Parallelism Degree greater than (1). The query returns multiple rows from the database directly related to the access degree given (i.e. Access Degree (4) returns 4 rows for every 1 row in the database). [...] 8272 47 11_Re: DSNDB0717_Robert J. Milonas20_xrjmbob@NETSCAPE.NET31_Tue, 18 Jun 2002 10:26:30 -0400311_iso-8859-1 We have allocated our 5 tablespaces as 500 cyl with no extension because a bad query can eat up the entire dasd farm. I think one query can use all the space available. I would try increasing the primary on all of the tablespaces (if you have more concurrent queries) and don't use secondaries. [...] 8320 33 11_unsubscribe35_Umapathy, Gopalakrishna (Cognizant)26_UGopalak@CHN.COGNIZANT.COM31_Tue, 18 Jun 2002 17:22:41 +053019_iso-8859-1

8354 54 23_Re: Last name search...11_Suresh Sane21_data_arch@HOTMAIL.COM31_Tue, 18 Jun 2002 09:39:37 -0500315_- Sofia,

I am not sure if anyone can tell you what is correct for your environment without knowing the data distribution - names tend to be very regional in nature and a system that works for (say) American names typically fails miserably for (say) Chinese names. In any case, a couple of suggestions - [...] 8409 180 32_Re: nested group by optimization13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 18 Jun 2002 09:40:56 -0500614_iso-8859-1 Daniel,

Materialization can be beneficial for bulk operations. But since this is a correlated subquery you want to avoid materializing a result set for each execution.

The following correlated subqueries may give you the best opportunity to exploit I1 fetch given your index I4.

select from t_premium XSP WHERE TWRITE <= :tWrite AND TWRITE = (SELECT MAX (P.TWRITE) FROM T_PREMIUM P WHERE P.NL_PREMIUM_ID = XSP.PREMIUM_ID AND P.TWRITE <= :tWrite) AND IID = (SELECT MAX (XPT.IID) FROM T_PREMIUM XPT WHERE XPT.NL_PREMIUM_ID = XSP.NL_PREMIUM_ID AND XPT.TWRITE = XSP.TWRITE) [...] 8590 79 17_Re: db2 compilers0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 18 Jun 2002 09:42:17 -0500471_us-ascii Wow, the EXPLAIN QUERYNO will relate to the compile listing rather that useless precompile listing, yippee! Now we have a reason for moving to V7 .

Have they also fixed the 'feature' ("...it is NOT a 'bug', it is is a 'fad', 'failing as designed' ...") where line numbers put into the QUERYNO field in PLAN_TABLE greater than smallint (32,767) are rounded/rolled to zero? If they haven't this 'new' feature will cause more problems than benifits. [...] 8670 34 20_Re: last name search12_McKown, John22_JMckown@UICIINSCTR.COM31_Tue, 18 Jun 2002 09:43:26 -0500224_iso-8859-1 Sofia, Have you considered using a "soundex" algorithm? What this does, for English, is attempt to "rationalize" the word so that similar sounding words are encoded the same. I.e. Smith, Smythe, and so on.

8705 43 35_Re: RLF and distributed connections13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Tue, 18 Jun 2002 09:41:43 -0500727_iso-8859-1 Ana,

Make different collections of the DB2 Connect packages for different uses? (shudder)

Use WLM to lower the dispatching priority? (you can use corrid there)

Dave

-----Original Message----- From: Martinez Pelaez, Ana [mailto:ana.martinez@IBERDROLA.ES] Sent: Tuesday, June 18, 2002 5:35 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: RLF and distributed connections



Hello,

We would like to give different limits to our distributed connections to DB2 for OS/390 through DB2 Connect. We have tried to use the RLF. The correlation ID is not in the list of fields to choose. All the distributed DB2 connect threads have the same planname (DISTSERV). We cannot use the [...] 8749 29 23_Re: Last name search...9_Rob Crane22_racrane@CONCENTRIC.NET31_Tue, 18 Jun 2002 08:55:09 -0600566_us-ascii Why don't you add a SOUNDEX column for your LAST NAME search. Check the archives for information.

-Rob

"Stolkarts, Sofia" wrote: > > We need to do a "wild card" last name search. > > We talked about adding a column that would have a part of a last > name and creating an index by this column. > > My question is, how can I research what is an optimum number of > characters that users have to enter in order to do a last name search? > > ================================================ > To change your subscription options or to cancel [...] 8779 184 52_Re: Peoplesoft using DSN1COPY getting IEC027I 737-1412_David Wilson30_David_A_Wilson@JOHNLEWIS.CO.UK31_Tue, 18 Jun 2002 16:16:14 +0100756_iso-8859-1 Dave,

Initially rejected as being too long, this is the joblog with the boring bits cut out.:-



0 09.55.33 JOB07698 ---- TUESDAY, 18 JUN 2002 ---- 09.55.33 JOB07698 IRR010I USERID TSO472 IS ASSIGNED TO THIS JOB. 09.55.39 JOB07698 ICH70001I TSO472 LAST ACCESS AT 09:54:25 ON TUESDAY, JUNE 18, 2002 09.55.39 JOB07698 £HASP373 TSO4720 STARTED - INIT 42 - CLASS E - SYS SM04 09.55.39 JOB07698 -**************************************************************************************************************** 09.55.39 JOB07698 -* --TIMINGS (MMMM:SS)-- ----PAGING COUNTS--- * 09.55.39 JOB07698 -* JOBNAME PROCNAME STEPNAME RC EXCP TCB SRB CLOCK SERV PG/SRVCL PAGE SWAP VIO SWAPS * 09.55.39 JOB07698 -* TSO4720 STOPDB 00 1 :00 [...] 8964 150 24_Re: IBM tools assessment13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 18 Jun 2002 10:11:59 -0700573_iso-8859-1 We had one a year ago, and we're going to have another one shortly. At the last one I was disappointed that they brought no documentation for their suggested replacement products, although nowadays I think everything is available on the web. This is important to have so you can compare features (they do not do this for you) and also gauge the effort required to convert from one tool to another. Also, do not look to IBM for performance comparisons. If performance is a concern (isn't that why you had 3rd party tools in the first place?), you'll have to [...] 9115 18 28_multiple volume DB2 datasets12_Richard Pack25_packr@MAIL.OA.STATE.MO.US31_Tue, 18 Jun 2002 12:23:03 -0500537_- We are seeing that when the linear VSAM dataset gets created for a TS or IX and we use IDCAMS outside of DB2, the dataset becomes a multiple volume candidate, 5 volumes at our site. But when we let DB2 create the dataset using stogroups and "*" for the volume, they are not multiple volume candidates. They are created in the same SMS dataclas which is where it is determined if it can span multiple volumes. I have even tried using the zparm to specidy the dataclas for TS's, but it still is not working as we believe it should. [...] 9134 50 37_User Defined Table Function Question?12_PAUL WALTERS23_Paul.A.Walters@SLMA.COM31_Tue, 18 Jun 2002 13:01:35 -0500433_US-ASCII We are unable to get a good join between a table created with a UDF and a table defined in the catalog.

For example: SELECT * FROM (SELECT LKO_XXX_SSN, LKO_XXX_ACT_SFX,LKO_SEQ_NUM FROM TABLE( EXMPL.TST3UDF(1001001) ) AS A WHERE LKO_AUX_SSN = 1001001 AND LKO_TP_CD = 'S' AND LKO_SEQ_NUM > 0) AS B, LSAAA WHERE AAA_XXX_SSN = LKO_XXX_SSN AND AAA_XXX_ACT_SFX = LKO_XXX_ACT_SFX AND AAA_SEQ_NUM = LKO_SEQ_NUM WITH UR; [...] 9185 12 29_Stress test tool for DB2 data16_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU31_Tue, 18 Jun 2002 11:43:00 -0700448_- Does anyone have a recommendation for a stress-test tool for WEB application accessing DB2 for OS390 data? We are looking for integrated feature to recover the DB2 data for repeated testing.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 9198 62 32_Re: multiple volume DB2 datasets0_19_Tim.Lowe@STPAUL.COM31_Tue, 18 Jun 2002 13:55:28 -0500371_us-ascii Richard, From what you have said, it sounds like your SMS ACS routines are providing the additional candidate volumes based on who creates them.

When DB2 creates the datasets using a stogroup with volumes(*), it creates a single-volume dataset. It only adds additional candidate volumes when you run out of space, and it tries to extend the dataset. [...] 9261 23 33_Sample Rexx Application "DRAW"...18_Gaston, Raymond J.17_GastonRay@ORU.COM31_Tue, 18 Jun 2002 14:59:00 -0400378_ISO-8859-1 DB-Listers,

HI, I'm trying to locate the sample REXX application called "DRAW" that's illustrated in Appendix D of the DB2 Version 7, Application Programming and SQL Guide (SC26-9933-00: DB2 Universal Database for OS/390 and Z/OS). I've looked in the mainframe SDSNSAMP pds with no luck. Does any know if it's called something else and/or in another pds? [...] 9285 215 39_Re: Bind SPUFI from OS/390 into DB2 UDB12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Tue, 18 Jun 2002 14:23:27 -0500473_us-ascii That was the problem. I am now connected to UDB. Last step to go is aligning DRDA/FEDERATION/DJ to query SQL Server(s). Any documents recommended to read?

Buzz







sjtoeniskoetter@AE P.COM To: DB2-L@LISTSERV.YLASSOC.COM cc: Sent by: DB2 Data Subject: Re: Bind SPUFI from OS/390 into DB2 UDB Base Discussion List



06/17/2002 04:40 PM Please respond to DB2 Data Base Discussion List [...] 9501 41 37_Re: Sample Rexx Application "DRAW"...15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Tue, 18 Jun 2002 15:40:27 -0400648_iso-8859-1 Ray, I think I cut and pasted it from the online manual

Bob Lawrence DBA Boscov's Dept Stores LLc

> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@LISTSERV.YLASSOC.COM]On Behalf Of Gaston, Raymond J. > Sent: Tuesday, June 18, 2002 2:59 PM > To: DB2-L@LISTSERV.YLASSOC.COM > Subject: Sample Rexx Application "DRAW"... > > > DB-Listers, > > HI, I'm trying to locate the sample REXX application called "DRAW" that's > illustrated in Appendix D of the DB2 Version 7, Application Programming and > SQL Guide (SC26-9933-00: DB2 Universal Database for OS/390 and Z/OS). I've > looked in the [...] 9543 62 51_Re: Answer: Calculate the size of a table in bytes!14_Theisen, Craig22_CTheisen@GUIDEMAIL.COM31_Tue, 18 Jun 2002 14:55:39 -0500306_iso-8859-1 I agree except for the decimal. I believe the Decimal columns are stored internally as packed decimal. And in the definition of Decimal(p,s) p is the precision and s is the scale. so........... If you have Decimal(13,2) you take the precision divided by 2 then and 1 or (13/2)+1 = 7 bytes. [...] 9606 168 52_Re: Peoplesoft using DSN1COPY getting IEC027I 737-1410_Mark Doyle19_mdoyle@JCPENNEY.COM31_Tue, 18 Jun 2002 15:00:12 -0500626_iso-8859-1

The only thing I can see is that _maybe_ the NUMPARTS(0) parm is causing problems. Exerpts of the Utility Guide which are germaine to this error follow.

Alternately, David's suggestion to repro to tape may be your only option.

Mark



Utility Guide & Reference:

# 3.7.2.4 Defining the input data set # Only one input DSN1COPY data set is allowed. Concatenated input data sets # are not permitted. For a table space consisting of multiple data sets, # ensure that you specify the correct data set. For example, if you specify # the CHECK option to validate the pages of [...] 9775 92 12_DSNALI abend26_Paulo Barroso Pinto Junior36_paulo.barroso@BRADESCOSEGUROS.COM.BR31_Tue, 18 Jun 2002 16:54:26 -0300275_- Hi all, My environment is OS/390 2.7 and DB2 version 5. Since I migrated one of our Cobol applications to LE Cobol I'm having a "protection exception" abend in the SQL-OPEN call to DSNALI. We are using CAF and , in fact, the abend occurs in DSNACAF, called by DSNALI. [...] 9868 122 25_Disaster Recovery Problem14_Smith, Brent E19_brent.smith@EDS.COM31_Tue, 18 Jun 2002 16:27:59 -0400464_- Listers,

We are currently testing our disaster recovery hot site process and ran into the following problem for one of the applications during recovery.

During a recover of 3 tablespaces the job abended with the following messages:

DSNU532I DSNUCBMD - RECOVER DATABASE DE06CLP1 TABLESPACE CLPS01

DSNUM 0 START

DSNU515I DSNUCBAL - THE IMAGE COPY DATA SET PDE06.CLPVL.DE06CLP1.CLPT01A.G0052V00 IS PARTICIPATING IN RECOVERY. [...] 9991 166 29_Re: Disaster Recovery Problem0_19_Tim.Lowe@STPAUL.COM31_Tue, 18 Jun 2002 16:12:50 -0500388_us-ascii Brent, Since the message says that it was "unable to allocate" the imagecopy tape, have you checked to make sure that the dataset is cataloged, and that you can allocate it? (perhaps in a little IEFBR14 jobstep or using SORT? Just to make sure that you can read it.) If you can allocate it, then were there any MVS messages about problems allocating it in the recovery job? [...] 10158 43 36_Re: ASP calling DB2 Stored Procedure15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Tue, 18 Jun 2002 17:57:05 -0400312_iso-8859-1 We tried to call a stored procedure from SQLserver, but it did not work. I think Microsoft is not sending the call statement to ODBC driver in the correct format. I trying to test calling a table function from SQLserver using open query syntax. Certainly we can call a stored procedure from ASP. [...] 10202 55 28_Re: DB2 UDB backup from java19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET31_Tue, 18 Jun 2002 23:59:15 +0100397_- Venkat,

The DB2 backup is not an SQL statement : that's why you cannot execute it as such (within Statement or such).

Instead it is a utility, which can either be called from the command line using the DB2 Command Line Processor or by calling the API directly. The latter is documented in the Administrative API Reference manual. This API is documented for C/C++ programming. [...] 10258 30 37_Quest Central for DB2 now for OS/390!13_Jim Wankowski23_jim.wankowski@QUEST.COM31_Tue, 18 Jun 2002 17:08:40 -0500564_ISO-8859-1 Quest software is proud to announce the general availability of Quest Central for DB2 v2.0! This new product represents the next generation of tools for DB2 database administration.

Quest Central™ for DB2 provides today's DBA with an integrated suite of powerful and comprehensive database management tools for seamless administration of DB2 on any platform. Each of the components offers distinct functionality that focuses on the most critical aspects of database management for DB2 UDB OS/390,UNIX,NT,and Linux all from a common console. [...] 10289 66 24_Re: IBM tools assessment12_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 19 Jun 2002 10:38:52 +1200467_us-ascii Hi Daryl,

No, I haven't had IBM in to do a DB2 tools assessment, but I do have a few of their tools installed here - just recently. DB2 PM I'm sure you're aware of, if not familiar with, so I won't dwell on it. The others we have are the DB2 Administration Tool and the DB2 Automation Tool. They're both pretty good, although both seem to be more geared towards DB2 V7 (and probably V6) than V5, which is fortunately only a small problem for us. [...] 10356 34 44_[DB2EUG] Restore from Control centre problem13_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Wed, 19 Jun 2002 09:10:55 +1000525_iso-8859-1 UDB 7.2 fp6 W2000

Due to a novel approach to rolling out software on the site I am on, developers have a need to restore databases from an image not listed in the database directories.

The manual says that using the control centre to manually restore a database does not require the date and time if there is only one backup present in the specified directory. However the SQL generated always shows a date and time (current) and so the restore fails as it cannot find a backup for _that_ time. [...] 10391 90 24_Re: IBM tools assessment11_rick creech18_ykcirc@HOTMAIL.COM31_Tue, 18 Jun 2002 23:25:51 +0000640_- BMC and other vendors, are you listening? The company that I am currently with states that they too will be getting rid of most 3rd party tools because, because as we purchase more and more powerful machines, management does not want to see progressively higher liscening fees.



>From: Raymond Bell >Reply-To: DB2 Data Base Discussion List >To: DB2-L@LISTSERV.YLASSOC.COM >Subject: Re: IBM tools assessment >Date: Wed, 19 Jun 2002 10:38:52 +1200 > >Hi Daryl, > >No, I haven't had IBM in to do a DB2 tools assessment, but I do have a few >of their tools installed here - [...] 10482 53 36_Re: ASP calling DB2 Stored Procedure10_Shery Hepp17_schepp@SRPNET.COM31_Tue, 18 Jun 2002 16:31:55 -0700290_iso-8859-1 Do you have the correct schema name defined? You will get a -440 if you don't have your schema defined on the call/or in the currentfunction path of db2ini file if the schema is other than sysibm/sysproc etc. Have you tried qualifying the schema name on the call to verify? [...] 10536 175 51_Re: Answer: Calculate the size of a table in bytes!14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Tue, 18 Jun 2002 18:36:56 -0500528_iso-8859-1 The SQL Reference (in the section about the LENGTH function) has the # of bytes stored as: 2 for small integer 4 for large integer 4 for single precision floating-point 8 for double precision floating-point INTEGER(p/2)+1 for decimal numbers with precision p 4 for date 3 for time 10 for timestamp The length of the string for character strings The length of the row ID The "+1" in the calculation for DECIMAL is to account for the sign, "p/2" because it is stored as a packed decimal field (2 digits per byte). [...] 10712 32 52_Re: Peoplesoft using DSN1COPY getting IEC027I 737-1414_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Tue, 18 Jun 2002 18:41:03 -0500393_us-ascii I KNEW there was a good reason that copying from DASD to TAPE made DSN1COPY work, I just didn't remember (the brain cells that held that particular tidbit of information got moved to secondary storage to make room for new stuff, I think). Mark just reminded me what the reason was: # Only one input DSN1COPY data set is allowed. Concatenated input data sets # are not permitted. [...] 10745 45 16_Re: DSNALI abend14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 19 Jun 2002 10:19:37 +1000349_US-ASCII The thing that springs to mind is an AMODE error - you get a 31 bit address, drop back to 24 bit addressing and then try to use the address. Only the bottom 24 are used and (unless the extra 7 bits are zero) you either get a S0C4 or an "interesting" situation. (You can also get one going from 24 to 31 bits, but that is less common.) [...] 10791 179 51_Re: Answer: Calculate the size of a table in bytes!12_Paul Redhead35_paul.a.redhead@TRANSPORT.QLD.GOV.AU31_Wed, 19 Jun 2002 10:30:22 +1000389_us-ascii David, Not sure but I think the 8 byte overhead per row is an addition of the 6 byte row prefix and 2 bytes required for that row in the offset at page end to point to it.

Paul.









"David S. Waugh" on 19/06/2002 09:36:56

Please respond to DB2 Data Base Discussion List [...] 10971 30 32_QMF display of a column in SQLCA9_Tim Simon15_tsimon@TSYS.COM31_Tue, 18 Jun 2002 21:04:40 -0400319_us-ascii I would like to display the value sqlerrd(4) from sqlca while in QMF. The panel "relative cost estimate" I believe displays this value. Sometime the panel is displayed and other times well, it not there. If the panel could be displayed even when it is below the governor parameters via a variable or something. 11002 56 24_Re: IBM tools assessment14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Tue, 18 Jun 2002 22:50:51 -0400563_ISO-8859-1 However, IBM also charges you upgrade fees, and higher license fees for more powerful processors.

So they base it on a "Value Unit" euphemism... it's easily convertible into MSU's and vice/versa. Tries to make you think you're getting more value for your money. Sometimes you win, and sometimes you lose. Upgrade your processor and you have to buy more value units... An upgrade charge, is an upgrade charge, is an upgrade charge. IBM also charges you for "service and support" in the first, and every year thereafter, if you want support. [...] 11059 76 24_Re: IBM tools assessment12_Raymond Bell17_rbell@NZ1.IBM.COM31_Wed, 19 Jun 2002 15:31:36 +1200493_us-ascii All good points, Joel. Our situation regarding pricing is different from most customers, in that IBM's customer for our purchase of tools is, IBM. It's an entirely internal deal, so unless you're an ISP that can give us tools for free it's always gonna cost us more to go 'outside'. Actually, 'free' isn't really free; it's still going to cost us something (more bean counters adding 2 beans to 2 more beans to make a very small casserole) but, for us, it's still a lot cheaper. [...] 11136 40 28_Re: DB2 UDB backup from java21_Venkata Reddy Vajrala30_venkatareddy@INFOTECH.STPH.NET31_Wed, 19 Jun 2002 10:04:54 +0530580_us-ascii Thanks Philip. I will try in this direction.

Venkat.

"Philip Nelson (DBA)" wrote:

> Venkat, > > The DB2 backup is not an SQL statement : that's why you cannot execute > it as such (within Statement or such). > > Instead it is a utility, which can either be called from the command > line using the DB2 Command Line Processor or by calling the API > directly. The latter is documented in the Administrative API Reference > manual. This API is documented for C/C++ programming. > > However someone with a little bit of programming skill should be [...] 11177 27 14_SQL Query.....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Tue, 18 Jun 2002 21:54:37 -0700456_us-ascii Hello !!

We are DB2 V5 for OS/390. Developers have given me a list of Tables that their programs will use (say TB1, TB2, TB3). Can there be a query to find out which of these tables does not exist in the catalog (i.e. does not exist in SYSIBM.SYSTABLES).

TIA. Raquel.



__________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com [...] 11205 49 16_Reorganize Table12_Saima Jelani22_sjelani@COMSATS.NET.PK31_Wed, 19 Jun 2002 11:01:05 +0500267_iso-8859-1 Hello i m DB2 contol center interface . i want to reorganize the table contents. I click on the table name , i want to reorganize it on the basis of index colum but it does show any cloumn name in the drop dow list. please tell me how can i reorganize data 11255 125 29_Re: Disaster Recovery Problem17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 19 Jun 2002 09:19:57 +0200514_us-ascii Hi Brent,

it's working as expected. DB2 tries to use the Recoverysite Inline IC from the REORG but cannot allocate it for some reason (you have to check). And you cannot RECOVER "through" a REORG or LOAD REPLACE operation. With other words, a REORG or LOAD REPLACE invalidates all previous ICs as far as recovery to a later point in time (e.g. to current) is concerned (even if it's LOG YES, because in that case DB2 would start the recovery from the log records written in the RELOAD phase). [...] 11381 142 34_import coomand with identityignore12_Saima Jelani22_sjelani@COMSATS.NET.PK31_Wed, 19 Jun 2002 13:31:05 +0500358_iso-8859-1 Hello i want to transfer data from one database to the other. I have used the db2tool to export and import data . It has imported successfully. i have tables with identity colums, identity genrate. To tranfer contents of such tables i have used following coomad import from tab1.ixf of ixf modified by identityignore replace into tabblename. [...] 11524 25 39_Looking DB2/VSE Performance Tuning Tool10_Julian zhu20_julian_db2@YAHOO.COM31_Wed, 19 Jun 2002 03:30:11 -0700624_us-ascii Hi, lister

Anybody can suggest wonderful performance tuning tool you used at DB2/VSE(Previous SQL/DS). Best of all, your feedback at those tools is more appreciated.

TIA.

Regards, Julian

__________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 11550 60 51_Re: Answer: Calculate the size of a table in bytes!13_Sandeep Simon33_Sandeep.S.Alumootil@UKTRANSCO.COM31_Wed, 19 Jun 2002 12:09:53 +0100333_- Dear All,

You have all given me an insight on what wanted,,atleast now I know that it is not as simple as I thought it was.

I tried the DB2 admin guide at the following URL and it failed me.

Does anyone have the correct URL? I had a look at the REXX sent by Tina and that good too.thanks a lot for those! [...] 11611 94 29_Clusterratio<100% after REORG17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 19 Jun 2002 13:42:06 +0200556_us-ascii Hi List,

IBM supplies a utility IRRDBU00 to unload the RACF database to a flat file. They also supply the DDL and LOAD statement to copy the data to DB2. I have just "upgraded" this to the new version that also has OMVS-related info. The DB2 part has one TS (SEGSIZE 64, not compressed), 67 tables and 120 indexes. FREEPAGE and PCTFREE are 0 for the TS and all indexes. Oddly enough, 12 of the clustering indexes have a clusterratio<100% (I'm loading the tables in the proper order, but I've run a REORG to make sure as well). This is [...] 11706 17 43_DB2 OS/390: How to calculate first of year?0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Wed, 19 Jun 2002 08:04:59 -0400442_us-ascii All,

Is there a better way to calculate Jan 1 of the current year than the SQL below? We are DB2 OS/390 V6

SELECT DATE(RTRIM(CHAR(YEAR(CURRENT DATE))) || '-01-01')

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 11724 63 17_Re: db2 compilers13_Wolff, Martin31_Martin.Wolff@GLOBALCROSSING.COM31_Wed, 19 Jun 2002 08:09:26 -0400600_iso-8859-1 Does anyone know when the COPROCESSOR option is going to be mandatory? Will the version following V7 have the DB2 precompiler?

Martin.

From: Robert Lawrence [mailto:rlawrence@BOSCOVS.COM] Sent: Tuesday, June 18, 2002 9:02 AM Subject: Re: db2 compilers



Derez,

use EXEC SQL INCLUDE copybookname END-EXEC and have your copybook in the same library as your DCLGEN members are for precompiler or concatenate your copy library into precompiler syslib DD or if DB2 V7 with latest COBOL you can use the COPROCESSOR feature to eliminate the precompile step [...] 11788 70 38_Re: import coomand with identityignore19_Hynes, Carol A. DOC27_Carol.Hynes@DOC.STATE.WI.US31_Wed, 19 Jun 2002 07:16:54 -0500624_iso-8859-1 In order to import an existing identity column, the target table must have the identity column defined as "generated by default as identity". If it is defined as "generated always" you cannot import a value to that column.

If your column is defined as generated by default, then just remove the identityignore from your import command and it should work. Then the problem that you will have is that importing data does not update the MAXASSIGNEDVAL column in the SYSSEQUENCES table. Depending upon the MAXASSIGNEDVAL for this table in your target system, you may have later inserts fail for duplicate [...] 11859 14 32_Re: multiple volume DB2 datasets12_Richard Pack25_packr@MAIL.OA.STATE.MO.US31_Wed, 19 Jun 2002 07:10:33 -0500334_- But that is the problem, it is not adding volumes when it runs out of space, we are seeing EOV error on the dataset and it is not even in extents over 100. Also, the listcat of the dataset will not show the multiple volume's of '*'. I have an IBM ETR, but having difficulty getting them to understand what we are running into. [...] 11874 15 33_Re: Clusterratio<100% after REORG10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 19 Jun 2002 14:29:28 +0200334_us-ascii What are the values of CLUSTERRATIOF ?

Best regards Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 11890 42 47_Re: DB2 OS/390: How to calculate first of year?15_Chris Van Daele33_Chris.Van.Daele@TOYOTA-EUROPE.COM31_Wed, 19 Jun 2002 14:29:38 +0200593_us-ascii Hi, in stead of using the select you could use the SET command .......









Ed_Vetock@NAVYFEDERAL.ORG@LISTSERV.YLASSOC.COM> on 19/06/2002 14:04:59

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@LISTSERV.YLASSOC.COM cc:

Subject: DB2 OS/390: How to calculate first of year?



All,

Is there a better way to calculate Jan 1 of the current year than the SQL below? We are DB2 OS/390 V6 [...] 11933 39 47_Re: DB2 OS/390: How to calculate first of year?13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 19 Jun 2002 07:40:26 -0500508_iso-8859-1 I do not know if this is better ... but it is another way. Basically, subtract the number of days from yesterday ...



select current date - ( dayofyear(current date) - 1) days from sysibm.sysdummy1



I am sure other suggestions will follow.

-----Original Message----- From: Ed_Vetock@NAVYFEDERAL.ORG [mailto:Ed_Vetock@NAVYFEDERAL.ORG] Sent: Wednesday, June 19, 2002 7:05 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: DB2 OS/390: How to calculate first of year? [...] 11973 56 43_Re: Looking DB2/VSE Performance Tuning Tool24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Wed, 19 Jun 2002 08:44:06 -0700642_us-ascii CA used to offer a tool called VM:DB/Monitor for DB2 Server for VM and there used to be a couple of DB2 tuning tools for VSE, but to my knowledge they have all be withdrawn from support. Control Center under VM has a feature that automates checking catalog statistics and recommending, or scheduling, a TABLE/DBSpace reorganization. There are several techniques that can be implemented with relatively little effort involving periodic output from the COUNTER and SHOW commands and DB2 and VSE system accounting records. The Fillmore Group offers classes U4505 "DB2 Server for VSE&VM Performance Monitoring and Tuning" and U4508 [...] 12030 48 33_Re: Clusterratio<100% after REORG17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 19 Jun 2002 14:43:36 +0200367_us-ascii Look at the bottom of my original posting. In the meantime, I have compressed the TS (PAGESAVE 83%). Now only 9 indexes have Clusterratio<100%, all of these have NEAROFFPOSF=0, FAROFFPOSF=1... (TS PAGESIZE is 4K, max. RECLENGTH is about 3000 bytes).

MfG, ME.





What are the values of CLUSTERRATIOF ?

Best regards Max Scarpa 12079 34 20_DB2 V7 IRLM Question13_Kathy Czepiel30_KathleenCzepiel@GROCERYBIZ.COM31_Wed, 19 Jun 2002 07:56:02 -0500566_us-ascii I've started the process of rolling out DB2 V7 OS/390 on OS/390 2.9 to all my DB2 subsystems and found an interesting problem caused by overlooking the IRLM. I had cloned two of my V6 subsystems onto my test box that was running a V7 subsystem with a linklisted V7 SDSNLINK to check for coexistence problems and to do trial migrations. One V6 migrated perfectly, with the second one I had problems. When I brought the V6 subsystem up with the V7 libraries in the migration step just prior to migrating the catalog I forgot to change the IRLM libraries [...] 12114 92 32_Re: multiple volume DB2 datasets12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Wed, 19 Jun 2002 08:01:58 -0500302_us-ascii I think Volume Count is what you want set on your Dataclas. We have a dataclas called LINEAR that we assign to db2 datasets. Check through ISMF if your dataclass looks somewhat like this and then double check the VTOC information to ensure the dataclas is getting properly assigned -Buzz [...] 12207 28 47_Re: DB2 OS/390: How to calculate first of year?11_Jim Addison28_jim_addison@STANDARDLIFE.COM31_Wed, 19 Jun 2002 14:04:32 +0100696_iso-8859-1 I would use:

SELECT SUBSTR(CHAR(CURRENT DATE),7,4)||'-01-01'

Jim.



For more information on Standard Life, visit our website http://www.standardlife.com/ The Standard Life Assurance Company, Standard Life House, 30 Lothian Road, Edinburgh EH1 2DH, is registered in Scotland (No SZ4) and regulated by the Financial Services Authority. Tel: 0131 225 2552 - calls may be recorded or monitored. This confidential e-mail is for the addressee only. If received in error, do not retain/copy/disclose it without our consent and please return it to us. We virus scan and monitor all e-mails but are not responsible for any damage caused by a virus or alteration [...] 12236 34 36_Re: QMF display of a column in SQLCA14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 19 Jun 2002 23:15:30 +1000379_US-ASCII The value QMF displays is the actually 1/1000 of the sqlca value. QMF displays the value as a way of indicating "if this number is high, then you might have to wait for a while" (You'll notice that both "high" and "while" are imprecise terms); in contrast to "if this number is low then you might not even have to wait long enough to be able to read this message". [...] 12271 67 16_INSERT into View17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 19 Jun 2002 15:25:55 +0200646_us-ascii Hi List,

this is probably some simple thing but I'm currently stumped. A developer has created a view: SET CURRENT SQLID='DDS0DB'; -- DBADM CREATE VIEW DDSS.V1_OFFICE AS SELECT * FROM DDSS.T1_OFFICE; Now he wants to BIND a package that does INSERTs: BIND PACKAGE(DDSS0) MEMBER(memname) OWNER(DDSS) QUALIFIER(DDSS) .... This fails with SQLCODE -551, token DDSS.INSERT.DDSS.V1_OFFICE: "DDSS does not have the privilege to perform operation INSERT on object DDSS.V1_OFFICE". This at first puzzled me - the CREATE VIEW in the SQL Ref seemed to me to imply that it should be possible - , but after reading up on INSERT, I thought [...] 12339 70 48_DB2/390 security join performance considerations17_Erkki Søndergaard20_esonderg@SCA.CSC.COM31_Wed, 19 Jun 2002 15:37:25 +0200543_iso-8859-1 In designing a security view a performance issue has popped up, and we don't really have the time to do a serious benchmark before commiting to a solution. Case as follows: All protected data are in tables, sizes ranges from 400 000 to 40 000 000 rows pr table. To limit access to data to only the relevant rows end-user access is thru views where we join against an access control table. Most users will only have access to a small subset of data, probably around 1-3%, but a few users will have access to up to 60-70% of the [...] 12410 218 51_Re: Answer: Calculate the size of a table in bytes!14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Wed, 19 Jun 2002 08:42:11 -0500432_us-ascii So that extra 2 bytes is: "A 2-byte directory entry for each record stored in the page" in every data page? That would make sense...

Thanks, David











Paul Redhead cc: Sent by: DB2 Data Base Subject: Re: Answer: Calculate the size of a table in Discussion List bytes! [...] 12629 99 20_Re: INSERT into View13_Trbovic, Phil28_Phil.Trbovic@DWD.STATE.WI.US31_Wed, 19 Jun 2002 08:48:55 -0500735_iso-8859-1 you need to do a GRANT INSERT on the view with the DDS0DB authid. DDS0DB owns the view since you set the SQLID to it.

-----Original Message----- From: Dr. Michael Ebert [mailto:mebert@AMADEUS.NET] Sent: Wednesday, June 19, 2002 8:26 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: INSERT into View





Hi List,

this is probably some simple thing but I'm currently stumped. A developer has created a view: SET CURRENT SQLID='DDS0DB'; -- DBADM CREATE VIEW DDSS.V1_OFFICE AS SELECT * FROM DDSS.T1_OFFICE; Now he wants to BIND a package that does INSERTs: BIND PACKAGE(DDSS0) MEMBER(memname) OWNER(DDSS) QUALIFIER(DDSS) .... This fails with SQLCODE -551, token DDSS.INSERT.DDSS.V1_OFFICE: "DDSS [...] 12729 16 18_Re: SQL Query.....33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 19 Jun 2002 08:38:05 -0500282_- Raquel

I think, the only possibility to get that work, is to put your list into a DB2 table and do an outer join with SYSTABLES T WHERE T.NAME IS NULL.

(If I get you right, you want to know, which tables exist and which don't, to create only these, don't you?) [...] 12746 75 20_Re: INSERT into View9_Dave Will26_david.e.e.will@VERIZON.COM31_Wed, 19 Jun 2002 09:51:23 -0400860_iso-8859-1 Dr. Ebert,

When DDS0DB was granted DBADM on the database in question, did it have the "with grant option" added?

Dave









"Dr. Michael Ebert" @LISTSERV.YLASSOC.COM> on 06/19/2002 09:25:55 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: INSERT into View





Hi List,

this is probably some simple thing but I'm currently stumped. A developer has created a view: SET CURRENT SQLID='DDS0DB'; -- DBADM CREATE VIEW DDSS.V1_OFFICE AS SELECT * FROM DDSS.T1_OFFICE; Now he wants to BIND a package that does INSERTs: BIND PACKAGE(DDSS0) MEMBER(memname) OWNER(DDSS) QUALIFIER(DDSS) .... [...] 12822 153 20_Re: INSERT into View0_19_mike.holmans@BT.COM31_Wed, 19 Jun 2002 15:03:36 +0100489_iso-8859-1 Dr Ebert,

You are sure that the view definition is SELECT * FROM T1_OFFICE?

If it were SELECT x,y,z FROM T1_OFFICE and there's also a w which is neither nullable nor defaulted, then you won't be able to do the INSERT or grant the INSERT privilege because it's a read-only view.

Do the relevant users in this scenario have the INSERT privilege on T1_OFFICE WITH GRANT OPTION, because otherwise they won't be able to grant those permissions on the view? [...] 12976 17 25_When do I get the SQLCODE33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 19 Jun 2002 09:00:01 -0500423_- Hi listners

Following situation: Application A1 inserts a row into a table with a unique index on it. Concurrently there is a second application A2 which wants to insert the same key. A2 will get SQLCODE -803, but the question is: when does A2 get -803. I suppose after A1 commits, because it would be possible that the insert of A1 will be rolled back and if A1 doesn't commit A2 will timeout. Right or not? [...] 12994 21 33_Re: Clusterratio<100% after REORG10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 19 Jun 2002 16:16:10 +0200462_us-ascii Hi

I didn't see the part below your sign

As far as I can see you've a CLUSTERRATIOF of 99,99% which means 100%. Are there any VARCHAR in tables ?

Just curious

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 13016 105 20_Re: INSERT into View16_Stolkarts, Sofia28_sofia.stolkarts@CAREMARK.COM31_Wed, 19 Jun 2002 09:16:47 -0500765_- What about authority granted to the table that view was created from ?...

-----Original Message----- From: Dr. Michael Ebert [mailto:mebert@AMADEUS.NET] Sent: Wednesday, June 19, 2002 8:26 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: INSERT into View





Hi List,

this is probably some simple thing but I'm currently stumped. A developer has created a view: SET CURRENT SQLID='DDS0DB'; -- DBADM CREATE VIEW DDSS.V1_OFFICE AS SELECT * FROM DDSS.T1_OFFICE; Now he wants to BIND a package that does INSERTs: BIND PACKAGE(DDSS0) MEMBER(memname) OWNER(DDSS) QUALIFIER(DDSS) .... This fails with SQLCODE -551, token DDSS.INSERT.DDSS.V1_OFFICE: "DDSS does not have the privilege to perform operation INSERT on object DDSS.V1_OFFICE". [...] 13122 135 29_Re: When do I get the SQLCODE14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 19 Jun 2002 15:27:42 +0100276_iso-8859-1 I actually suspect that A1 inserts the key, then A2 WAITS on his insert because A1 has U locks. If A2 times out before A1 commits, then he will get a -904/-911 (I can never remember which is which). If A1 commits BEFORE A2 times out, then A2 will get the -803 [...] 13258 63 32_Re: multiple volume DB2 datasets0_19_Tim.Lowe@STPAUL.COM31_Wed, 19 Jun 2002 09:26:27 -0500370_us-ascii Richard, The first thing that I would check would be the stogroup, to make absolutely sure that is says VOLUMES ('*').

When you run out of space, DB2 will check the stogroup, and if it is VOLUMES('*'), then it will do somthing like an "alter addvols", and retry the allocation. If the allocation fails, it does something like an "alter removevols". [...] 13322 90 29_Re: When do I get the SQLCODE0_19_Tim.Lowe@STPAUL.COM31_Wed, 19 Jun 2002 09:39:14 -0500349_iso-8859-1 Walter, You are absolutely correct, duplicate key inserts are the one case left today where an insert can timeout or deadlock. And, this is just for the reason that you mentioned, application A1 could rollback, in which case application A2's insert would be successful. This is one of my biggest concerns with some database designs. [...] 13413 16 51_Re: Answer: Calculate the size of a table in bytes!14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Wed, 19 Jun 2002 09:45:45 -0500432_iso-8859-1 Sandeep,

try http://www-3.ibm.com/software/data/db2/os390/v7books.html or http://www-3.ibm.com/software/data/db2/os390/v6books.html depending on your version.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 13430 75 43_Re: Calculate the size of a table in bytes!15_Morelli, Joseph32_Joseph.Morelli@ERIEINSURANCE.COM31_Wed, 19 Jun 2002 10:48:46 -0400352_-



-----Original Message----- From: Morelli, Joseph Sent: Monday, June 17, 2002 9:38 AM To: 'DB2 Data Base Discussion List' Subject: RE: [DB2-L] Calculate the size of a table in bytes!



You can get a free (I think it's still free) product from IBM called IBM DB2 Estimator that can give you this info (and much more): [...] 13506 61 24_Re: DB2 V7 IRLM Question15_Zander, Barb J.19_bzander@STATE.ND.US31_Wed, 19 Jun 2002 09:52:32 -0500515_iso-8859-1 Kathy,

Any dataset that is Linklst'd is loaded at IPL and stays there until an IPL or a Linklst refresh.

It may have just been a cause of needing to refresh the LLA. To refresh it enter: F LLA,REFRESH <=== LLA our linklst name

Also, I just finished putting maint on our V6 system and their was 1 PTF whos action stated that you had to bring the lower level IRLMs up before the higher level ones(ie. Any subsystems that did not have PTF need to be brought up first). PTF UQ54647 [...] 13568 40 36_Re: QMF display of a column in SQLCA11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 19 Jun 2002 10:00:14 -0500307_- Tim,

I think predictive governor may do this for you. Within RLF you set 2 limits - one for warning and one for error. By setting the first limit low (almost zero?), you will get a "warning" that displays the estimated cost - this is NOT the timeron value but hopeflly close to what you need? [...] 13609 44 27_Stored procedures question.13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Wed, 19 Jun 2002 10:11:55 -0500471_- DB2 Experts -

I am posting this for a colleague:

Scenario: I have an OS/390 stored procedure that executes a DECLARE GLOBAL TEMPORARY TABLE, inserts rows to this table with data from extraneous sources, SELECTs all rows and returns them, in a cursor (result set), to the calling program.

Problem: We have been experiencing -497 errors, which state that "THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN EXCEEDED FOR DATABASE database-name". [...] 13654 99 33_Re: Clusterratio<100% after REORG17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 19 Jun 2002 17:15:23 +0200391_us-ascii Clusterratio is <100% because some rows are placed near/far off from their optimum position. The question has thus become, "why does DB2 do that?" Here are the details on one of the tables (probably the smallest) to show that there is nothing special:

CREATE TABLE USER01.USER_GROUPS ( USGCON_NAME CHAR(8) NOT NULL, USGCON_GRP_ID CHAR(8) NOT NULL ) IN RACFDB2.IRRDBU00; [...] 13754 175 24_Re: DB2 V7 IRLM Question12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Wed, 19 Jun 2002 11:22:53 -0400411_us-ascii Kathy, i was also interested in where you have the IRLM modules. Are they in Sdsnload or the Sdsnlink? Jeff







"Zander, Barb J." Sent by: DB2 Data Base Discussion List 06/19/02 10:52 AM Please respond to DB2 Data Base Discussion List



To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: Re: DB2 V7 IRLM Question [...] 13930 13 18_Re: SQL Query.....16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Wed, 19 Jun 2002 10:26:21 -0500346_- this'll work... SELECT NAME FROM SYSIBM.SYSTABLES WHERE NAME NOT IN ('TB1','TB2') phil g

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 13944 15 32_Re: multiple volume DB2 datasets16_Philippe Godfrin38_philippe.godfrin@JUDICIARY.STATE.NJ.US31_Wed, 19 Jun 2002 10:31:26 -0500413_- Hi Rich,

Have you tried VOLUMES('***') (or maybe it's '* * *' with spaces between the astericks...) I forget, but see if that works...

phil g

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 13960 44 16_Rexx help needed0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Wed, 19 Jun 2002 10:40:37 -0500514_us-ascii A programmer is experiencing a problem retrieving DB2 data thats been stored in upper and lower case that has an Amperand in it. Adding UCASE returns most of the rows but is unable to retrieve data that has a & in it. This same SQL works fine in SPUFI and returns the row, but not in Rexx. Does anyone know of any gotchas that might explain this or areas one might look or try. He did say that if the ampersand had a space before and after it, the Rexx would return it , but if it had anything other [...] 14005 24 46_Building sql statement within stored procedure19_Hynes, Carol A. DOC27_Carol.Hynes@DOC.STATE.WI.US31_Wed, 19 Jun 2002 10:50:26 -0500339_iso-8859-1 I have a developer that wants to create an SQL stored procedure using stored procedure builder that passes a table name, where clause and user id as a parms. Within the procedure he wants to build a sql select using the values in the parms. Can anyone tell me if this is possible or not and if so, provide a sample syntax? [...] 14030 40 18_Re: SQL Query.....12_Chris Pomasl19_pomasl@STARBAND.NET31_Wed, 19 Jun 2002 04:01:30 -0600320_us-ascii Sorry but this will list every table in the catalog except the ones listed. Not what the user wanted. You cannot select something that is not there so what you will need to do is query the catalog for the tables you are looking for (ie IN instead of NOT IN) and then manually look and see what is missing. [...] 14071 41 32_Re: multiple volume DB2 datasets12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 19 Jun 2002 08:53:50 -0700480_us-ascii Pardon me as I get back up to speed on OS/390 flavor of DB2, but don't you need to specify an * for each volume you want in a stogroup...ie use * * for 2 volumes, * * * for 3? Then SMS has to be set up to allow for that many volumes as well... Tim --- Richard Pack wrote: > But that is the problem, it is not adding volumes > when it runs out of > space, we are seeing EOV error on the dataset and it > is not even in extents > over 100. [...] 14113 52 32_Re: multiple volume DB2 datasets0_19_Tim.Lowe@STPAUL.COM31_Wed, 19 Jun 2002 11:14:25 -0500357_us-ascii Phil, I don't think that it is a good to do this. For example, if you use a stogroup with VOLUMES('*','*') you will get a multi-volume allocation on 2 candidate volumes. However, once you fill the second volume, it will not extend to a third. The way that it is designed to work is using VOLUMES('*'), and I think that is what should be used. [...] 14166 48 50_Re: Building sql statement within stored procedure12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 19 Jun 2002 09:17:30 -0700391_us-ascii Carol, It should be no problem to do this in a stored procedure. You must use dynamic SQL and a prepare statement to do this. Several of my developers have done this in testing. Unfortunately, I can't find any examples of the sample syntax. But if you check the documentation for creating dynamic sql with prepared statements, you should hopefully be able to see how to do it. [...] 14215 69 32_Re: multiple volume DB2 datasets16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 19 Jun 2002 12:20:42 -0400370_iso-8859-1 Tim, That is not the case in our shop. We have a STOGROUP created via: CREATE STOGROUP GDBP2SMS VOLUMES( "*" ) VCAT DB2P2;

DFSMS (we are at OS/390 2.9) places the data on any of 62 volsers within corresponding DFSMS Storage group DBP2DATA based Data Class specifications. The datasets thus allocated can get extents on multiple volumes if needed. [...] 14285 122 24_Re: DB2 V7 IRLM Question12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 19 Jun 2002 09:02:32 -0700573_us-ascii Isn't it just the directory entries of linklisted datasets that are loaded at IPL? (and someone please correct me if I'm wrong). I believe it is usually just the ERLY code that's normally placed in DSNLINK, which would not effect IRLM. If the IRLM modules are Steplib'd to in the IRLM startup JCL, then they should always come from the dataset pointed to by steplib (or joblib). If the db2 sdsnload library is linklisted, and you don't steplib, then you would have to do an LLA refresh. Or perhaps you have some db2 modules which have errantly been placed in [...] 14408 33 20_Logical unit of work17_Adam Wiedenhaefer20_adamw@IMAGERIGHT.COM31_Wed, 19 Jun 2002 12:25:30 -0400360_iso-8859-1 what is the best way to group a set of SQL statements in to a logical unit of work with DB2? (i.e. SQL Server has Begin/End transaction, What does Db2 have?)



The psuedocode would flow like this:

begin transaction

update this where x = y and yada yada yada.....

if error, rollback transaction (do not commit) [...] 14442 39 43_Re: Calculate the size of a table in bytes!12_Pamela Odden26_OddenPJ@GROUPWISE.CCSD.NET31_Wed, 19 Jun 2002 09:28:00 -0700289_US-ASCII Sounds good - hopefully as we go along we will find tools that will be useful and learn how to really use them, so we don't have to depend so much on how Aaron did things.

btw - thank you for the delicious brownie!! I ate half of it for breakfast and it hit the spot! [...] 14482 98 32_Re: multiple volume DB2 datasets12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 19 Jun 2002 09:49:17 -0700542_us-ascii Thanks for setting me straight, Eric. I guess Robert has run into a bug, then. I don't believe I was using full-blown SMS when I had to code multiple * * *'s. Much better solution using SMS's ability to span so many datasets. Tim --- "Pearson, Eric L," wrote: > Tim, > That is not the case in our shop. > We have a STOGROUP created via: > CREATE STOGROUP GDBP2SMS > VOLUMES( "*" ) > VCAT DB2P2; > > DFSMS (we are at OS/390 2.9) places the data on > any of 62 volsers within corresponding DFSMS Storage [...] 14581 90 23_How to use QMF Governor33_REGINA LUCIA FERREIRA DE OLIVEIRA22_regina.oliveira@BCP.PT31_Wed, 19 Jun 2002 18:29:36 +0100432_iso-8859-1 Hello,

I'm trying to implement QMF Governor in my production environment (DB2 5.1 QMF3.3). I created a new RESOURCE_GROUP with my own user included, and created a RESOURCE_OPTION ROWLIMIT = 15. When I tested it was working perfectly. Then I tried to change ROWLIMIT = 100, and when I run a query that brings me about 5.000.000 rows and nothing happened. I changed it back to 15 and again the governor worked. [...] 14672 30 60_PIPE CHARACTER RETURNED FROM STORED PROCEDURE TO UNIX SERVER16_Jeff A L'Italien25_jeff.a.l'italien@AEXP.COM31_Wed, 19 Jun 2002 11:02:14 -0700511_us-ascii We have run into problems in an SQL statement within a stored procedure returning a value back to a Unix Server. The SQL statement should return a value similar to the following:

ABC|DEF|GHI|JKL

However, due to the EBCIDIC to ASCII translation, the value that is being received is:

ABC!DEF!GHI!JKL

For some reason, the pipe character is coming across as an explanation point. Has anyone experienced this type of problem, and if so, been able to implement a workaround? [...] 14703 31 20_Re: Rexx help needed13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Wed, 19 Jun 2002 14:26:24 -0400634_iso-8859-1 Hello Shan,

Didn't Dan get his answer over on the TSO-REXX forum?

The thoughts there were that it's happening in ISPF handling of the Rexx program.

David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@compuware.com









The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...] 14735 47 64_Re: PIPE CHARACTER RETURNED FROM STORED PROCEDURE TO UNIX SERVER13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Wed, 19 Jun 2002 13:28:24 -0500529_iso-8859-1 alternative is to use keyword CONCAT i.e. ... "ABC concat DEF concat GHI ...."



-----Original Message----- From: Jeff A L'Italien [mailto:jeff.a.l'italien@AEXP.COM] Sent: Wednesday, June 19, 2002 1:02 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: PIPE CHARACTER RETURNED FROM STORED PROCEDURE TO UNIX SERVER



We have run into problems in an SQL statement within a stored procedure returning a value back to a Unix Server. The SQL statement should return a value similar to the following: [...] 14783 132 33_Re: Clusterratio<100% after REORG12_Higgins John25_HigginsJohn@JOHNDEERE.COM31_Wed, 19 Jun 2002 13:31:31 -0500386_iso-8859-1 Can you fit 453 rows into one page? If all the "duplicate" keys cannot fit into the one and only "target" page, I would call that either a near off or far off situation.

-----Original Message----- From: Dr. Michael Ebert [mailto:mebert@AMADEUS.NET] Sent: Wednesday, June 19, 2002 10:15 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Clusterratio<100% after REORG [...] 14916 89 21_Re: log no with query12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Jun 2002 13:46:02 -0500563_- I could not resist this one - borrowing from an article last year.

My first question for people who want to turn off the log is, "Why?". Another way of phrasing the question is to tell me the objective, rather than a specific technique. If the objective is to save space on the log and archiving, there are a number of techniques for doing that. One example is compressing the archive log with a tool from IBM or another vendor. If the objective is to improve overall performance or elapsed time by 10%, turning off logging generally cannot do that [...] 15006 27 22_Extended VSAM datasets16_Lamb, Ron (CORP)25_Ron.Lamb@CORPORATE.GE.COM31_Wed, 19 Jun 2002 14:59:53 -0400412_iso-8859-1 Does anyone have experience using extended VSAM datasets for DB2 on OS/390. We have a 10 partition tablespace closing in on the 4 gig limit. Because of the design, adding partitions is not a solution. We want to know what problems you may have encountered using extended architecture, particularly with DB2 version 6.1, and your conversion strategy (we have about 25 million rows per partition). [...] 15034 143 24_Re: IBM tools assessment12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Wed, 19 Jun 2002 14:01:00 -0500404_iso-8859-1 Rick

We are listening, and we have studied this subject. Lets take an example, say a 9672-r166 which is 109MSU according to IBM, 630 MIPS according to Gartner and a tier 120 according to BMC. Now the charges for DB2 from V5 to V7 including V7 without the utilities and with the utilities. And just the utilities, none of the other products written by whomever and marketed by IBM. [...] 15178 34 30_Re: reserve word usage in v7.112_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Jun 2002 13:58:42 -0500478_ISO-8859-1 It appears that there is a lot of confusion about reserved words. The primary reference is the SQL Reference, which has a two page appendix, SQL Reserved Words. For example, YEAR and YEARS are listed as reserved in V5, V6 and V7.

The appendix includes a list of reserved words and also an indication that we try to allow reserved words to be used if we can tell the context. There is no way to provide a simple description of when this becomes a problem. [...] 15213 28 24_DB2 Family SQL Reference12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Jun 2002 14:02:38 -0500431_- Do you want to design or write applications for the entire DB2 family, rather than for just one of the platforms? If so, you need the IBM DB2 Universal Database SQL Reference for Cross-Platform Development that was just added to our manuals on the web. This book covers these product versions: DB2 UDB for UNIX, Windows, OS/2 Version 7 Release 2 DB2 UDB for z/OS and OS/390 Version 7 DB2 UDB for iSeries Version 5 Release 1 [...] 15242 60 51_Re: Answer: Calculate the size of a table in bytes!12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Jun 2002 14:28:36 -0500405_- If you need to type something in, you can start with

ibm.com/software/db2zos

For books, click on Library

For answers to questions, presentations, Redbooks, ... Click on Support

If you want to have a program to calculate the size for you, then I'd suggest clicking on Downloads and getting Estimator.

Roger Miller, DB2 for z/OS

Read the Friendly Web (RTFW) [...] 15303 25 16_isolation levels13_Kavita Mittal16_kavitam@ZOWI.COM31_Wed, 19 Jun 2002 12:41:45 -0700251_iso-8859-1 Hi, I a trying to find the current isolation level on a db2 OS 390 ver 7.1 on AIX 4 , I know you can change it using the change isolation command but how do you find out the current level? also what is the defualt on os 390, is it RR? [...] 15329 25 24_Re: IBM tools assessment12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Jun 2002 14:58:48 -0500578_- There appear to be a number of problems with your calculation of IBM prices. The first is that IBM z/OS & OS/390 prices are not published. There are many different factors, and accurate prices can only be provided by the sales team.

I'm only a developer, so I don't understand the pricing. My sales team has told me so. Still, I think I see some problems in your analysis. For example, if you added the recovery and operational utilities together, the prices would be much higher than for the DB2 utilities suite that includes the full function. Usually, there is [...] 15355 114 29_Re: Disaster Recovery Problem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Wed, 19 Jun 2002 15:07:31 -0500420_us-ascii Dr. Ebert:

A few questions for clarification: 1. So, in the case of REORG LOG YES (without an Inline Image Copy), during a "Recover to current" DB2 ignores any previous Image Copies as a starting point and (a) uses the log records from the RELOAD phase to essentially "rebuild" the tablespace as it looked at reorg end, then(b) rolls forward from that point thru the log to get you back to current? [...] 15470 87 32_Re: multiple volume DB2 datasets13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Wed, 19 Jun 2002 15:11:28 -0500410_iso-8859-1 Tim you are remembering correctly, in the "olden days" you did have to specify more than one *, in fact one for each candidate volume.

Not sure which release that changed in.....

Dave

-----Original Message----- From: tim malamphy [mailto:timalamphy@YAHOO.COM] Sent: Wednesday, June 19, 2002 11:49 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: multiple volume DB2 datasets [...] 15558 35 32_Re: multiple volume DB2 datasets21_Powers, Carol A - CNF20_Powers.Carol@CNF.COM31_Wed, 19 Jun 2002 13:26:18 -0700458_iso-8859-1 Your next step should be to check the space utilization in the SMS storage group assigned to the dataset. If the volumes are full it will not be able to extend (or will start getting little bitty extents). It will not be able to use space in an SMS 'overflow' storage group because it's an extend, not a new allocation - it needs to be in the original storage group. If there is space on the volumes then you need to lean harder on your etr. [...] 15594 144 29_Re: When do I get the SQLCODE14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Wed, 19 Jun 2002 15:41:54 -0500488_iso-8859-1 Assuming that you are locking at the page level and not the row level, can't you still get a timeout or deadlock if A1 and A2 are inserting to the same page, even if they're inserting different keys?

David S. Waugh, NCW DSW Consulting & Services













Tim.Lowe@STPAUL.C OM To: DB2-L@LISTSERV.YLASSOC.COM Sent by: DB2 Data cc: Base Discussion Subject: Re: When do I get the SQLCODE List [...] 15739 184 33_Re: db2 connect driver for AIX 4?8_Marc Ost36_Marc.Ost@DATADIRECT-TECHNOLOGIES.COM31_Wed, 19 Jun 2002 23:06:41 +0200651_iso-8859-1 Kavita,

if you need help or have questions when configuring the datadirect driver or about supported features you can always contact our answerline (see below).

And to Martin, the datadirect driver DOES support connection pooling.

regards,

Marc Ost DataDirect Technologies www.datadirect-technologies.com

Contacting DataDirect Technologies Technical Support E-mail In USA, Canada, Mexico, Australia, and Asia Pacific (except Japan): datadirect.answerline@datadirect-technologies.com In Japan: jpn.answerline@datadirect.co.jp All other countries: int.datadirect.answerline@datadirect-technologies.com [...] 15924 53 29_Re: When do I get the SQLCODE0_19_Tim.Lowe@STPAUL.COM31_Wed, 19 Jun 2002 16:12:48 -0500270_us-ascii David, No, that changed with type-2 indexes. As I understand it, if the "ideal" page on which to insert this row is "busy", then it will insert on a different page. I don't know how they do that, I just consider it DB2 software magic, and I am happy!! (-: [...] 15978 102 52_Re: DB2/390 security join performance considerations13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 19 Jun 2002 16:20:28 -0500423_iso-8859-1 Erkki,

Based upon the "Summary of Predicate Processing" section of the Admin or Application Programming manuals, a BETWEEN involving columns as the low and high values is stage 2 and not indexable. This is unless you are V7, have APAR PQ54042 applied, and the table referred to by columns on the RIGHT side of the BETWEEN are accessed prior to the table on the LEFT. Then the predicate is indexable. [...] 16081 49 50_Re: Union ALL Problem with DB2 Version 7 on OS/39013_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 19 Jun 2002 16:20:28 -0500406_iso-8859-1 Phil,

I would bind with DEGREE(1) if this is what gets you the correct result. Then followup with IBM.

There is APAR PQ54883 which appears to be related, but it's duplicate APAR PQ57699 does not.

Regards Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com [...] 16131 189 29_Re: Disaster Recovery Problem14_Smith, Brent E19_brent.smith@EDS.COM31_Wed, 19 Jun 2002 17:21:32 -0400687_- Tim,

Yes the dataset is cataloged. I've even used it to do a TOCOPY recovery.

No MVS messages relating to allocation problems.

Thanks, Brent

-----Original Message----- From: Tim.Lowe@STPAUL.COM [mailto:Tim.Lowe@STPAUL.COM] Sent: Tuesday, June 18, 2002 4:13 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Disaster Recovery Problem



Brent, Since the message says that it was "unable to allocate" the imagecopy tape, have you checked to make sure that the dataset is cataloged, and that you can allocate it? (perhaps in a little IEFBR14 jobstep or using SORT? Just to make sure that you can read it.) If you can allocate it, then were there [...] 16321 110 27_Re: How to use QMF Governor14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Wed, 19 Jun 2002 16:56:05 -0500359_us-ascii Hi Regina:

I'm a little fuzzy on the details, but I think this is what's happening: There's a default in QMF startup that controls the number of FETCHes QMF does before displaying the first rows on the QMF report panel. I suspect your default is set somewhere between 15 and 100 (I believe the default is 33, but I wouldn't swear to it). [...] 16432 79 29_Re: When do I get the SQLCODE0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 19 Jun 2002 17:02:00 -0500683_us-ascii Oops, Tim is right and my previous posting was wrong (base on obsolete version of DB2).







Tim.Lowe@STPAUL.CO M To: DB2-L@LISTSERV.YLASSOC.COM Sent by: DB2 Data cc: Base Discussion Subject: Re: When do I get the SQLCODE List



2002/06/19 04:12 PM Please respond to DB2 Data Base Discussion List











David, No, that changed with type-2 indexes. As I understand it, if the "ideal" page on which to insert this row is "busy", then it will insert on a different page. I don't know how they do that, I just consider it DB2 software magic, and I am happy!! (-: [...] 16512 61 26_Row Level Locking Overhead0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 19 Jun 2002 16:59:26 -0500386_us-ascii DB2 V6 OS390 2.10

Can anyone point me to some specific numbers, formulas, rules of thumb on the additional overhead associated with row level locking versus page level locking. Maybe a redbook or redpiece or even a white paper. I don't care what color it is or even if the numbers are for DB2 V5, just as long as the source is recoginzeable (ie IBM) and reputable. [...] 16574 194 29_Re: When do I get the SQLCODE0_22_Rohn.Solecki@MTS.MB.CA31_Wed, 19 Jun 2002 17:00:54 -0500446_iso-8859-1 Yes Dave, that is true. We found that out the hard way long ago (see separate posting I just submitted about row level locking)







"David S. Waugh" cc: Sent by: DB2 Data Subject: Re: When do I get the SQLCODE Base Discussion List



2002/06/19 03:41 PM Please respond to DB2 Data Base Discussion List [...] 16769 88 29_Re: When do I get the SQLCODE14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Wed, 19 Jun 2002 17:19:29 -0500413_us-ascii Thanks Tim -- that's good to know. I'm happy too! Hotspots have been a real database design nightmare in times past -- good to know it's less of a problem these days.

Thanks, David











Tim.Lowe@STPAUL.C OM To: DB2-L@LISTSERV.YLASSOC.COM Sent by: DB2 Data cc: Base Discussion Subject: Re: When do I get the SQLCODE List [...] 16858 61 36_DB2 V6 OS390 index definition issues10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Wed, 19 Jun 2002 17:11:04 -0500309_us-ascii All, We have a segmented tablespace defined to use extended addressing, the ACS routine is set to the HLQ.DSNDBD.DATABASE.* name. We spoke with IBM, and have gotten nowhere. Has anyone heard of this? I have searched the archives & came up with nothing also. Any help will be wildly appreciated. [...] 16920 80 20_Re: Reorganize Table13_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Thu, 20 Jun 2002 09:27:55 +1000421_iso-8859-1 You reorganise based on an index, not an index column. I don't know your platform but in Windoze you are given the option to pick an index, the make-up of which, will define your reorganisation order.

HTH

Steve T

-----Original Message----- From: Saima Jelani [mailto:sjelani@COMSATS.NET.PK] Sent: Wednesday, 19 June 2002 16:01 To: DB2-L@LISTSERV.YLASSOC.COM Subject: Reorganize Table [...] 17001 179 29_Re: Disaster Recovery Problem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Wed, 19 Jun 2002 18:33:31 -0500422_us-ascii Brent: A few questions:

1. What version/release of DB2 are you on? What platform?

2. Did the DSNU030I message by chance have any "RC=rr, CODE=ccc" information on it that we're not seeing in what you pasted in your post? That would give you an indication of what happened when DB2 tried to dynamically allocate the Image Copy dataset: DSNU030I csect-name UNABLE TO ALLOCATE dsn, RC=rr, CODE=ccc [...] 17181 91 30_Re: Row Level Locking Overhead13_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Thu, 20 Jun 2002 09:37:48 +1000482_iso-8859-1 Ah but do you need the row-level locking? Have you actually tested the amended design using page level locking?

I ask because I have worked on many similar systems and they were designed before type-2 indexes. This (type 1 indexes) was the real culprit in the ascending key problem as it caused the symptoms you described. type 2 removed this and allowed us to go back to ascending inserts, in fact the old 'hot spot' is now seen as good due to buffer reuse. [...] 17273 36 12_CAF Software0_16_softron@FUSE.NET31_Wed, 19 Jun 2002 19:38:26 -0400417_ISO-8859-1 We've been using a product called DB2CAF for over 10 years. Our batch JCL looks like what you're asking for. We execute our batch programs by name. An input parameter allows us to specify the subsystem id and plan name. Any existing COBOL or PLI DB2 program can run this way without modification. DB2CAF intercepts the first SQL command and makes the connection to DB2 automatically without IKJEFT01. [...] 17310 96 24_Re: DB2 V7 IRLM Question14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Wed, 19 Jun 2002 19:14:35 -0500590_us-ascii Hi Kathy -- A couple of questions for you:

1. Is there anything in your SDSNLINK linklisted library besides the ERLY code (just 6 modules, I think)? Some Sysprogs copy everything from SDSNLOAD into SDSNLINK and then put SDSNLINK into the linklist -- that's why I ask. Or sometimes I've seen Sysprogs copy SDSNLOAD to a "common" library that's in the linklist whose name isn't anything like "SDSNxxxx", which really makes problems hard to track down. Me, I would browse all the datasets in the linklist to see if there are any DSNxxxxx or DXRxxxxx in any of them (other [...] 17407 86 41_Re: User Defined Table Function Question?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Wed, 19 Jun 2002 20:09:13 -0500418_us-ascii Paul,

Are you able to send the explain output?

A few more questions:

- Are you V6 or V7? - What are the datatypes returned from the Table UDF? - What are the datatypes of the corresponding columns? - How many rows are returned from the table UDF? - What is the cardinality value of the UDF definition? - If greater than 1 row returned, how are you returning these rows (scratchpad)? [...] 17494 78 20_Re: INSERT into View12_Raymond Bell17_rbell@NZ1.IBM.COM31_Thu, 20 Jun 2002 13:44:29 +1200573_us-ascii Hey Michael,

I think you're already on the right track. Others have made some valid suggestions already, but I'm waiting for dozens of tape mounts so I have a spare few minutes... (like, 300)

Anyway. If an ID creates a view owned by another user, the user/owner gets select only. The creator of the view (or anyone else authorised) has to grant any further privileges required. Ah, it's all coming back to me. DDS0DB has to have, I think, DBADM on the database 'with grant' so it can then grant the privileges to the view owner. I think this is [...] 17573 72 24_Re: Logical unit of work12_Raymond Bell17_rbell@NZ1.IBM.COM31_Thu, 20 Jun 2002 13:48:13 +1200469_us-ascii Hi Adam,

Your pseudo-code pretty much has what you need, except you can omit the 'begin transaction' bit. The unit of work in DB2 begins with the first SQL statement and ends with either a commit or a rollback, either explicitly (i.e. exec sql commit/rollback) or implicitly (i.e. at successful program termination/AbEnd). Although I believe this changes slightly with the business of syncpoints, which (un)fortunately I don't know anything about. [...] 17646 23 65_Re: DB2 for z/OS and OS/390 Version 7 Selected Performance Topics12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Jun 2002 20:47:25 -0500668_- I think the current version has everything from the first one. Just in base, here is the Redbook URL

http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg246129.html? Open

Here is the Redpaper URL

http://publib-.boulder.ibm.com/Redbooks.nsf/RedpaperAbstracts/redp0162.html? Open

Nice to know the face of another friend I had not yet met.

Roger Miller, DB2 for z/OS

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 17670 13 40_Re: DB2 V6 OS390 index definition issues12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Jun 2002 21:03:02 -0500448_- Here's a wild guess before I run to the next problem. Extended Addressing is for partitioned or LOB table spaces, not for segmented.

Roger Miller, DB2 for z/OS ibm.com/software/db2zos

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 17684 52 24_Re: Logical unit of work14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 20 Jun 2002 12:14:34 +1000415_US-ASCII In DB2 you don't need an explicit "start transaction" because one is implicitly started when you start to access the data.

Between them are SAVEPOINTs which are used to mark intermediate points within a transaction. A rollback can specify a SAVEPOINT, to which the rollback will occur, or it will roll back to the beginning of the transaction. (A COMMIT always commits the entire transaction.) [...] 17737 27 17_Re: db2 compilers14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 20 Jun 2002 12:14:37 +1000413_US-ASCII I can't see it becoming mandatory until all COBOL compilers pre V2.2 are de-supported.

James Campbell

PS It's the Coprocessor "feature" - which is implemented using the SQL "option".

On 19 Jun 2002 at 8:09, Wolff, Martin wrote:

> Does anyone know when the COPROCESSOR option is going to be mandatory? Will > the version following V7 have the DB2 precompiler? > > Martin. [...] 17765 84 17_Re: db2 compilers12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 19 Jun 2002 21:21:31 -0500399_- My guess is that this big an incompatible change would not be coming that soon. No one knows if or when coprocessors are mandatory. DB2 V7 COBOL compilers include

- IBM COBOL for OS/390 and VM Version 2 (5648-A25) optionally, with IBM VisualAge COBOL Enterprise Version 2 Release 2 - IBM COBOL for MVS and VM Release 2 (5688-197) - VS COBOL II Release 4 (5668-958, 5688-023, 5688-805) [...] 17850 44 50_Re: Building sql statement within stored procedure14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 20 Jun 2002 13:04:49 +1000437_US-ASCII Toddle over to http://publib- b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg246418.html

DB2 for z/OS and OS/390 : Squeezing the Most Out of Dynamic SQL (couldn't help but notice that Suresh is one of the co-authors).

Don't forget the additional materials, where there is some sample code.

(For this exercise is doesn't really matter _how_ the program is invoked - _what_ it then does is the same.) [...] 17895 56 33_Taking Backup/Restore on Linux OS12_Saima Jelani22_sjelani@COMSATS.NET.PK31_Thu, 20 Jun 2002 10:30:01 +0500272_iso-8859-1 hello i have database on DB2 of linux. Can any one tell me how to take the back up of database and then restore the data into the another database from coomand promp please give the exact syntax, because i dont have control panel installed

Thanks

17952 48 24_Re: IBM tools assessment16_Michael McCawley20_Mike_DBA@HOTMAIL.COM31_Wed, 19 Jun 2002 23:24:28 -0500445_iso-8859-1 Tom Moulder wrote:

> In point of fact, IBM is determined to raise the price of DB2 with > each new version by at least 15% and in the case of version 7 they have > figured out how to raise it by 18%. At the same time, BMC customers have > experienced miniscule increases of 3% and for existing customers this > amounted to a 3% increase on the maintenance bill without paying new one > time license charges. [...] 18001 15 49_Stephen J Vagnier/OR1/AEPIN is out of the office.0_17_sjvagnier@AEP.COM31_Thu, 20 Jun 2002 00:50:29 -0400403_us-ascii I will be out of the office starting 06/20/2002 and will not return until 06/26/2002.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 18017 16 60_Catherine T Mercado-Chung/US/Household is out of the office.23_Catherine Mercado-Chung29_ctmercado-chung@HOUSEHOLD.COM31_Thu, 20 Jun 2002 01:28:26 -0500451_us-ascii I will be out of the office starting 06/19/2002 and will not return until 06/25/2002.

For Salinas Mainframe DB2 Support, contact Barbara Nigh. For UDB Support, contact Mark Jones.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 18034 131 30_AW: Row Level Locking Overhead12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Thu, 20 Jun 2002 08:43:49 +0200553_iso-8859-1 Hi Rohn,

during an IBM classroom training, we were told the following (according to my notes): "The cost of a lock (doesn't matter if page or row) is the same. So the only reason not to chose row level locking is sequential access which causes more row locks than page locks." Probably, you will find the opposite opinion when reading "Application Design Guidelines for High Performance" (SG24-2233-00). I haven't read it in complete yet, but I know contains much about locking considerations. It seems, that the opinions differ. [...] 18166 130 29_Re: Disaster Recovery Problem17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 19 Jun 2002 09:19:57 +0200514_us-ascii Hi Brent,

it's working as expected. DB2 tries to use the Recoverysite Inline IC from the REORG but cannot allocate it for some reason (you have to check). And you cannot RECOVER "through" a REORG or LOAD REPLACE operation. With other words, a REORG or LOAD REPLACE invalidates all previous ICs as far as recovery to a later point in time (e.g. to current) is concerned (even if it's LOG YES, because in that case DB2 would start the recovery from the log records written in the RELOAD phase). [...] 18297 147 34_import coomand with identityignore12_Saima Jelani22_sjelani@COMSATS.NET.PK31_Wed, 19 Jun 2002 13:31:05 +0500358_iso-8859-1 Hello i want to transfer data from one database to the other. I have used the db2tool to export and import data . It has imported successfully. i have tables with identity colums, identity genrate. To tranfer contents of such tables i have used following coomad import from tab1.ixf of ixf modified by identityignore replace into tabblename. [...] 18445 105 29_Re: Disaster Recovery Problem17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 20 Jun 2002 09:46:37 +0200559_us-ascii 1. Yes. In the RELOAD phase, DB2 writes the entire tablespace contents to the log (plus some more). So there is no point in starting before. 2. DB2 would use the IC first, and only read from the log if the IC is unavailable (=fallback to previous recovery starting point). The Inline IC is SHRLEVEL REF, and DB2 it only has to consider log entries written after completion of the IC (=after RELOAD is complete), while it would have to read additional log records for the LOG YES utility (from the start of the RELOAD phase). Further, there is a [...] 18551 69 30_Re: Row Level Locking Overhead17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 20 Jun 2002 09:52:50 +0200378_us-ascii Hmph. I'd say the improved runtime is due to the change in clustering key and not related to the locking level. Rather I'd guess it'd improve even more with page-level locking. Maybe you can try that out one day (just a simple ALTER TABLESPACE .. LOCKSIZE PAGE)?

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...] 18621 36 26_Re: Extended VSAM datasets10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 20 Jun 2002 09:42:18 +0200525_us-ascii Hi

We have a test part tablespace using EA with 100 partitions as in production we smashed against space limit for a partition of this tablespace. We defined it as LARGE (V5) but now we use DSSIZE as recommended by IBM. I made some test and all ended ok (LOAD, select/insert/update) + reorg. Anyway its only defined EA but there are few data loaded. I performed only some functionality tests. AFAIK there are some APARs regarding DB2 & EA (for instance PQ44635) Our conversion strategy probably will be: [...] 18658 66 29_Re: When do I get the SQLCODE33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 20 Jun 2002 02:50:51 -0500564_- Tim

Someone - I think Bonnie Baker - told me, that inserts request a conditional lock for the "ideal" page and if the inserter doesn't get this lock, it requests a lock for another page.

On Wed, 19 Jun 2002 16:12:48 -0500, Tim.Lowe@STPAUL.COM wrote:

>David, >No, that changed with type-2 indexes. As I understand it, if the "ideal" >page on which to insert this row is "busy", then it will insert on a >different page. I don't know how they do that, I just consider it DB2 >software magic, and I am happy!! (-: > >Thanks, > Tim > >From [...] 18725 14 24_Re: Logical unit of work33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 20 Jun 2002 03:05:41 -0500414_- James

My question digresses a little bit, but do you know, if I can use SAVEPOINTS in an IMS-environment. I think no, because I can not use ROLLBACK.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 18740 152 40_Re: DB2 V6 OS390 index definition issues17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 20 Jun 2002 10:10:52 +0200522_us-ascii Hi,

I can't quite figure out what you did: 1. What was the SECQTY you specified when creating your index? If it was 0, the DS won't extend. 2. 8196 and 32784 are no valid PIECESIZEs - because they're not powers of two. So let's assume you mean 8192 and 32768. Then there is still the Unit missing (K, M, G). 3. If we assume the Unit is K, then it would be ok that the index DSs are not extended, because the PRIQTY is already greater. What is the used space in these index datasets? You can check the [...] 18893 34 30_Re: Row Level Locking Overhead13_Patric Becker25_Patric_Becker@SIS-WEST.DE31_Thu, 20 Jun 2002 03:31:04 -0500308_ISO-8859-1 Rohn,

I found something in some educational material from IBM. The class was called "DB2 for OS/390 Version 5 - Designing for Concurrency". The material is from the end of 1998, so probably the numbers aren´t that correct for Version 7, but I think it could be a good a rule of thumb. [...] 18928 50 25_Sequence of Data Inserted9_Ali Akbar25_ali.akbar@CRESSOFT.COM.PK31_Thu, 20 Jun 2002 14:44:22 +0500445_us-ascii Hi list,

Please see the following query .

INSERT INTO B(key , col) SELECT key , col from A ORDER BY key

My question is . When you use order by clause in a select statement it works fine and sort the data . But when you use the same command to insert into a table .... Is the data inserted is in the same sequence as the order by clause or it varies for servers (perhaps this thing is implemented differently). [...] 18979 35 32_Re: multiple volume DB2 datasets14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Thu, 20 Jun 2002 03:53:13 -0500363_iso-8859-1 Hi,

if you look at the v4 (the earliest I can find) SQL Ref CREATE STOGROUP page you will see: " VOLUMES(volume-id,...) or VOLUMES('*',...) Defines the volumes of the storage group. Each volume-id is a volume serial number of a storage volume. It can have a maximum of six characters and is specified as an identifier or a string constant. [...] 19015 113 33_Re: Clusterratio<100% after REORG17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 20 Jun 2002 10:57:36 +0200541_us-ascii No. The "proper" place where to put a record is on the same page as the record with the preceding key, or the next page if that is full. If records with identical keys occupy several consecutive pages, that is no problem (clusterratio=100, near/faroffposf=0). Also the fact that only one or two records are "off" (and far off more often than near off) means it cannot be due to an intervening page (e.g. space map) either. And finally the oddity exists with unique keys as well (see listing in a previous post). Still puzzled. [...] 19129 22 18_RACF/DB2 Migration13_Duen Nga Chan17_duennga@YAHOO.COM31_Thu, 20 Jun 2002 01:56:39 -0700548_us-ascii How do I enable RACF/DB2 external security? How about the CPU overhead of RACF/DB2? Is it significant?

Regards,

Anita

__________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 19152 154 29_Re: Sequence of Data Inserted17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 20 Jun 2002 11:12:29 +0200561_us-ascii Hi

you do not specify your platform. On OS/390, the situation is as follows, as far as I know:

DB2 tries to insert the data in such a way that the "clustering" (sort order) of the target table is preserved. Specifically, it tries to insert the record into the same page (or the next) as where the record with the preceding key is located. If it cannot do that, it searches the area within 8 pages from the desired point ("near off"). If there's no room there, it searches from the beginning of the tablespace. If that fails as well, [...] 19307 41 38_Re: import coomand with identityignore12_David Harvey17_dmh@ONETEL.NET.UK31_Thu, 20 Jun 2002 04:22:54 -0500603_- Saima, This is the relevant warning IBM provide: " Using Export with Identity Columns The export utility can be used to export data from a table containing an identity column. If the SELECT statement specified for the export operation is of the form "select * from tablename", and the METHOD option is not used, exporting identity column properties to IXF files is supported. The REPLACE_CREATE and the CREATE options of the IMPORT command can then be used to recreate the table, including its identity column properties. If such an IXF file is created from a table containing an identity column [...] 19349 94 38_Re: import coomand with identityignore12_Saima Jelani22_sjelani@COMSATS.NET.PK31_Thu, 20 Jun 2002 15:46:18 +0500441_iso-8859-1 you are saying i need to drop the table if i drop the target table then data will be lost again

please tell me what i do in that case, because table cant be alterd this way

----- Original Message ----- From: "Hynes, Carol A. DOC" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, June 19, 2002 5:16 PM Subject: Re: import coomand with identityignore [...] 19444 145 30_Re: Row Level Locking Overhead15_Dirk Herzhauser23_dherzhau@CSCPLOENZKE.DE31_Thu, 20 Jun 2002 12:14:58 +0200490_iso-8859-1 Hello Listers,

in my opinion the overhead depends : 1. on environment (data sharing or not) 2. on number of rows per page 3. the number of users per page :(

In an data sharing environment you have to pay for additional page p-locks if your using MAXROWS 1 In IBM SG24-4725-00 Locking in a DB2 MVS Environment you'll find following table: Locksize ELAPPSED CLASS2 / CPU CLASS 2 LOCK / LOCK REQUESTS /MAX.LOCKS HELD Table 13.37 1.98 2 25 Page 12.89 2.08 660 607 [...] 19590 70 41_log no with query & the philosophy of DB210_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 20 Jun 2002 12:25:57 +0200385_us-ascii Hi Roger

We discussed this argument in Rome, in october 2001 during Bonnie Baker's seminar (I still have the scars of lashes). What you say (and said) is absolutely correct.

What I want to point out asking log no in SQL is that problem is not (or better not only) technical but it involves the new human resources and the costs, other than new technologies [...] 19661 18 30_Re: Row Level Locking Overhead33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 20 Jun 2002 05:40:57 -0500378_- Patrik

Very interesting, because a few minutes before I read your reply, I got an request, to go to row level locking for two tables and I have always some concerns switching to row level locking.

I think, if someone talks about overhead, he is talking about a datasharing environment. Do you have got any figures where data sharing is taken into account?. [...] 19680 108 30_Re: Row Level Locking Overhead17_Tapio Lahdenmaeki27_tapio_lahdenmaki@FI.IBM.COM31_Thu, 20 Jun 2002 13:53:43 +0300323_iso-8859-1 Patric Becker wrote:

I found something in some educational material from IBM. The class was called "DB2 for OS/390 Version 5 - Designing for Concurrency". The material is from the end of 1998, so probably the numbers aren´t that correct for Version 7, but I think it could be a good a rule of thumb. [...] 19789 57 29_Answer: The philosophy of DB212_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 20 Jun 2002 13:20:48 +0200336_iso-8859-1 Hi Max,

Well spoken!

>>>> DB2 needs a little of outward appearance and a little of make-up and some 'little game' or >>>> it'll remain 'the db of dinos'.

At the IBM Software Symposium (held in Vienna) Beth T. Smith (IBM Silicon Valley Lab) held a session called "DB2 for z/OS version 7 and beyond". [...] 19847 19 30_Re: Row Level Locking Overhead13_Patric Becker25_Patric_Becker@SIS-WEST.DE31_Thu, 20 Jun 2002 06:13:13 -0500413_ISO-8859-1 Walter,

I´m sorry, but I don´t have any additional material regarding row level locking in a data sharing environment.

Regards, Patric

===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 19867 165 20_Re: INSERT into View17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 20 Jun 2002 13:27:09 +0200521_us-ascii Well actually the view is CREATE VIEW DDSS.V1_OFFICE(A, B, C, ..) AS SELECT X, Y, Z.. FROM DDSS.T1_OFFICE; (there is an unspoken rule in the development group that all columns have to renamed and scrambled every chance you get). I had thought the same but did not investigate because of the manual work involved and because the error message clearly spoke about authorisation. To make sure, I've now checked this... all columns get selected, they just get renamed and rearranged (no functions or anything). [...] 20033 29 39_Looking DB2/VSE Performance Tuning Tool10_Julian zhu20_julian_db2@YAHOO.COM31_Wed, 19 Jun 2002 03:30:11 -0700632_us-ascii Hi, lister

Anybody can suggest wonderful performance tuning tool you used at DB2/VSE(Previous SQL/DS). Best of all, your feedback at those tools is more appreciated.

TIA.

Regards, Julian

__________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. . [...] 20063 56 21_OS/390 IRLM More Info13_Kathy Czepiel30_KathleenCzepiel@GROCERYBIZ.COM31_Thu, 20 Jun 2002 07:32:32 -0500526_us-ascii Hopefully this will clarify some things. Thanks.

The OS/390 DB2 Version 7 SDSNLINK library is the only library linklisted. I didn't steplib to any V6 SDSNLINK. I don't do datasharing. I forgot to change the IRLM startup proc library to V7 when I changed the libraries in DBM1, MSTR, and SPAS to V7. Something from either the SDXRRESL or SDSNLOAD becomes resident for the subsystem and prevented me from starting with the version 7 libraries after I realized my mistake. It would not clear without an IPL. [...] 20120 23 51_Sample UDFs for Migration - Oracle or Sybase to DB212_McKown, John22_JMckown@UICIINSCTR.COM31_Thu, 20 Jun 2002 07:36:03 -0500304_iso-8859-1 I found this interesting. It is from IBM. They are free some free UDFs. They are supposed to help migration to DB2 from Oracle or Sybase. They are for the Windows, Linux, and UNIX environments, not OS/390.

http://www7b.software.ibm.com/dmdd/library/samples/db2/0205udfs/



20144 82 33_Re: Answer: The philosophy of DB213_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Thu, 20 Jun 2002 07:55:19 -0500325_iso-8859-1 Do not be totally disheartened as i believe IBM understand that - to a degree. I think many of the new functions and capabilities in V6, V7, V++ take into account compatibility with the other DBMS's. That way, it makes it easier to port an application from DBMS-x to DB2 which IBM can use as a selling point. [...] 20227 180 52_Re: DB2/390 security join performance considerations17_Erkki Søndergaard20_esonderg@SCA.CSC.COM31_Thu, 20 Jun 2002 15:01:55 +0200474_iso-8859-1 Terry, thanks for the input.

The DB2 in question is V7 and I absolutely intend to get DB2 to access the ACCESS table first as this provides the best filtering. Index screening is a must as the data rows are compressed and thus expensive to use just for data screening. To make things more interesting the end-users may end up joining several of these views! Seems like I'll have to run some benchmarks on this before we commit to a specific solution. [...] 20408 219 24_Re: IBM tools assessment14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 20 Jun 2002 14:12:37 +0100496_iso-8859-1 and one final point that is VERY pertinent to both pricing and technology advances.

The absolute WORST thing that can happen in ANY marketplace is loss of competition.

Specifically, in the DB2 tools market place it has been the historic competition between BMC and PLATINUM (no offence to all the other vendors) that has kept prices manageable and ensured that the technology has continued to advance. The addition of a new competitor (IBM) just adds another angle. [...] 20628 208 24_Re: IBM tools assessment14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 20 Jun 2002 14:15:48 +0100558_iso-8859-1 also

Please don't forget, things are always negotiable (even though they might at first not seem so).

This is another advantage of the multi-vendor playground we work in....

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com



-----Original Message----- From: Michael McCawley [mailto:Mike_DBA@HOTMAIL.COM] Sent: 20 June 2002 05:24 To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: [DB2-L] IBM tools assessment [...] 20837 47 55_Problems building a 'SQL Stored Procedure' with the SPB17_Adam Wiedenhaefer20_adamw@IMAGERIGHT.COM31_Thu, 20 Jun 2002 09:26:51 -0400357_iso-8859-1 Hello all,

I am have difficulty creating a simple 'Hello World' stored procedure just to get used to the language. I think that the stored procedure is syntactically correct:

CREATE PROCEDURE IRUSER.Proc1 ( out parm1 char(10), out parm2 char(10) ) LANGUAGE SQL

P1: BEGIN set parm1 = 'Hello'; set parm2 = 'World'; END P1 [...] 20885 99 24_Re: Logical unit of work17_Adam Wiedenhaefer20_adamw@IMAGERIGHT.COM31_Thu, 20 Jun 2002 09:39:24 -0400446_iso-8859-1 James/Raymond,

Thanks for your help. This does help me out.

I looked up the ROLLBACK TO SAVEPOINT clause and it talked about ROLLBACK WORK. Pardon my misunderstanding but does ROLLBACK WORK rollback the entire transaction from the first connection? In other words can I do something like this:

connect to db1;

insert into aaa(f1, f2)values ('stuff', 999); insert into ddd(f1, f2)values ('stuff', 999); [...] 20985 89 29_Re: Disaster Recovery Problem14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 20 Jun 2002 08:45:56 -0500745_us-ascii Thanks Dr. Ebert -- that clears it up quite a bit!

David











"Dr. Michael Ebert" To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Disaster Recovery Problem Sent by: DB2 Data Base Discussion List



06/20/02 02:46 AM Please respond to DB2 Data Base Discussion List













1. Yes. In the RELOAD phase, DB2 writes the entire tablespace contents to the log (plus some more). So there is no point in starting before. 2. DB2 would use the IC first, and only read from the log if the IC is unavailable (=fallback to previous recovery starting point). The Inline IC is SHRLEVEL [...] 21075 54 33_Re: Answer: The philosophy of DB210_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 20 Jun 2002 15:55:25 +0200417_us-ascii Hi ...Peter ? How is ?

Yes Jim Teng in Rome said the same things.

But look:

- since V3 the 'enhancements' seem the same, utilities in primis (but how much an utility may be improved) in every version and the improvements are history for 3rd party utilitie. And IBM realized in late that it might have been a business to write some powerful utilities. In V7 you've to BUY them...... [...] 21130 79 20_Re: Rexx help needed0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Thu, 20 Jun 2002 08:58:11 -0500253_us-ascii Dave, I don't subscribe to the TSO-REXX forum, and Dan conveyed to me that no one had a solution and that there was a thought that ISPF may be the culprit and it may not work, but it wasn't clear to me whether that was a theory or a fact. [...] 21210 120 30_Re: Row Level Locking Overhead11_David Nance16_DWNance@FHSC.COM31_Thu, 20 Jun 2002 09:57:50 -0400534_US-ASCII I don't have any real numbers from IBM, but when I was at a large telecom company we went with row-level locking. We made the switch due to our system being a very heavily used OLTP system and we were noticing a lot of locking, which, was causing a lot of difficulty for us. We changed all tablespaces over from page to row level, we saw about a 20% reduction in transaction times across the board and no real noticeable difference in our CPU overhead. I did the same here at the company I'm with now for just a portion [...] 21331 101 45_IFI and Ifcid 318 (Dynamic Cache information)23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 20 Jun 2002 14:59:34 +0100277_iso-8859-1 Has anybody (other SAP sites using ST04) switched this on and then read the data via IFI?

I am trying it now with a piece of Rexx (usual calls to DSNALI) .. but seem to be having little joy .. I can get other information / ifcids but no info from 318 .. [...] 21433 177 29_Re: When do I get the SQLCODE14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 20 Jun 2002 09:01:40 -0500442_iso-8859-1 I wonder if we could prevail on the venerable (and busy) Roger Miller (or anyone else out there in DB2-L Land who knows) to explain how this new Insert process works (i.e. explain the science behind the DB2 Magic). Some questions I have are:

1. Did this change begin to happen automagically after we switched to Type-2 indexes, or was something else (like migrating to DB2 V5, V6 or V7) required? I suspect the latter. [...] 21611 98 29_Clusterratio<100% after REORG17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Wed, 19 Jun 2002 13:42:06 +0200556_us-ascii Hi List,

IBM supplies a utility IRRDBU00 to unload the RACF database to a flat file. They also supply the DDL and LOAD statement to copy the data to DB2. I have just "upgraded" this to the new version that also has OMVS-related info. The DB2 part has one TS (SEGSIZE 64, not compressed), 67 tables and 120 indexes. FREEPAGE and PCTFREE are 0 for the TS and all indexes. Oddly enough, 12 of the clustering indexes have a clusterratio<100% (I'm loading the tables in the proper order, but I've run a REORG to make sure as well). This is [...] 21710 64 51_Re: Answer: Calculate the size of a table in bytes!13_Sandeep Simon33_Sandeep.S.Alumootil@UKTRANSCO.COM31_Wed, 19 Jun 2002 12:09:53 +0100333_- Dear All,

You have all given me an insight on what wanted,,atleast now I know that it is not as simple as I thought it was.

I tried the DB2 admin guide at the following URL and it failed me.

Does anyone have the correct URL? I had a look at the REXX sent by Tina and that good too.thanks a lot for those! [...] 21775 61 33_Re: Answer: The philosophy of DB210_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 20 Jun 2002 16:09:47 +0200568_us-ascii HI

sorry for the vent but today is a bad day. My office is invaded by ORACLErs. And they are a multitude, I'm alone.

Anyway as I said in another reply (offline) about next improvements in DB2 that I heard in a seminar in Rome:

- since V3 the 'enhancements' seem the same, utilities in primis (but how much an utility may be improved) in every version and the improvements are history for 3rd party utilitie. And IBM realized in late that it might have been a business to write some powerful utilities. In V7 you've to BUY them...... [...] 21837 74 59_Re: Problems building a 'SQL Stored Procedure' with the SPB11_Jakobs, Jef18_JJakobs@HARTIC.COM31_Thu, 20 Jun 2002 09:10:55 -0500423_iso-8859-1 Adam,

Looks like DB2 has difficulty locating your C-compiler. Make sure you have one installed, and the tell DB2 where your C-compiler is. There are a few ways to do this. On my system I used db2set.

db2set DB2_SQLROUTINE_COMPILER_PATH=executable_file

where executable_file is the full path name for the C compiler environment file. I'd read up on it in the Application Building guide. [...] 21912 53 24_Re: IBM tools assessment12_Amit Agarwal33_amit_agarwal@STANDARDANDPOORS.COM31_Thu, 20 Jun 2002 10:13:41 -0400560_us-ascii I totally agree. I donot forsee a situation where everyone starts using IBM tools. This is not good for IBM too. Let us consider a hypothetical situation where everyone starts using IBM tools, for example. In the long run, without pressure from competitors, the quality of support will slowly decline. This will not only lead to a loss of 'Marketplace' in tools but will make Db2 as a product unpopular. Oracle largely dominates the UNIX shop today. One of the main reasons , I feel ( I am not a expert on the subject) is that IBM's focus on Db2 [...] 21966 99 24_Re: IBM tools assessment12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Thu, 20 Jun 2002 09:21:03 -0500597_iso-8859-1 Michael

That might very well be the case. And I re-iterate that with all the terms and conditions tht are associated with contracts today it weould be impossible to handle every nuance. My central point is that ISVs have not raised the priice of software through the roof.

BMC Software, Inc. on day one of the announcement for license manager signed on as a participant. We don't as yet have the license manager to bill from, but when that is available BMC will contract with customers and bill for product usage. This will provide a hedge for customer upgrades that [...] 22066 82 59_Re: Problems building a 'SQL Stored Procedure' with the SPB12_Mark Buzzard21_Mark_Buzzard@ARIC.COM31_Thu, 20 Jun 2002 09:12:24 -0500480_us-ascii Do you have Visual C++ installed and have you ran the "Register Visual C++ Add-ins". I had a similiar problem finding the "make" program of C++. I think I fixed it by installing Visual C++ then reinstalling UDB.

Buzz







Adam Wiedenhaefer cc: Subject: Problems building a 'SQL Stored Procedure' with the SPB Sent by: DB2 Data Base Discussion List [...] 22149 230 29_Re: When do I get the SQLCODE0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 20 Jun 2002 09:31:39 -0500453_iso-8859-1 Check out "DB2 for OS/390 Application Design Guidelines for High Performance", SG24-2233-00, August 1997 (or a newer version). There is a section about Index design and concurrency that answers some of your questions.







"David S. Waugh" cc: Sent by: DB2 Data Subject: Re: When do I get the SQLCODE Base Discussion List [...] 22380 18 30_Re: Row Level Locking Overhead0_19_Tim.Lowe@STPAUL.COM31_Thu, 20 Jun 2002 09:36:08 -0500462_us-ascii I think that lock avoidance should also be considered in a comparison of row versus page locking. (along with all of the other factors that everyone else has mentioned of course)

Thanks, Tim

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 22399 24 22_Database Access Thread19_Luiz Roberto FORONI27_lforoni@CAIXASEGUROS.COM.BR31_Thu, 20 Jun 2002 11:50:45 -0300433_us-ascii What means "to run in SRB MODE" ?

What advantages and disadvantages of a Allied Thread and a Database Access Thread ?

If the same query retrieving millions of rows is executed in both way, which one , can cause a greatest consume ?

How WLM can control a Database Access Thread with a low priority , if this thread is executed on DB2 Address Spaces(with high priority) ? Is this control effective ? [...] 22424 88 49_Re: IFI and Ifcid 318 (Dynamic Cache information)17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Thu, 20 Jun 2002 17:09:58 +0200571_us-ascii Hi Leslie,

there was a presentation from Venkat R. Pillay in IDUG 2000 which might help , you should be able to get it from the IDUG Website ("Customising DB2 Trace output", Session A3). Is this REXX you have shareable? I've been playing a bit with the "Cached Statement Statistics" from the DB2 transaction, but am not sure if everything is set up correctly, or whether I understand what it is supposed to do. When I filter on "Executions>0", I never get more than about 100 entries. If I filter on "CPU>0", I get now about 6.200 entries... and all [...] 22513 136 31_Microsoft drop support for JAVA16_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM31_Thu, 20 Jun 2002 11:29:35 -0400561_iso-8859-1 Following is a news art. about Microsoft dropping support for JAVA within windows. What will happen now with all these DB2 tools that run on the PC platform using JAVA?



http://www.komotv.com/stories/18966.htm



"Service Is Our Best Product"

Robert Galambos Compuware Corp. Of Canada

1-800-348-8299

1-877-950-3594 (quebec) 1-905-803-8603 (fax)

Les renseignements contenus dans le présent message électronique sont confidentiels et concernent exclusivement le(s) destinataire(s) désigné(s). [...] 22650 14 30_Re: Row Level Locking Overhead33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 20 Jun 2002 10:09:48 -0500449_- Dave

Did you do all that in a datasharing environment? And what was about batch jobs? Didn't you experience more lock escalations or did you switch off lock escalation using LOCKMAX 0?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 22665 39 29_Re: When do I get the SQLCODE0_19_Tim.Lowe@STPAUL.COM31_Thu, 20 Jun 2002 10:36:22 -0500305_us-ascii David, I apologize for trying to be humorous about how DB2 checks whether the optimium page to insert on is busy. (Regarding my comment that "I don't know how they do that, I just consider it DB2 software magic, and I am happy!!") My comment seems to have caused confusion instead of humor. [...] 22705 13 35_Checkpoint Restart in DB2 TSO Batch11_Gordon Robb17_robbgo@UK.IBM.COM31_Thu, 20 Jun 2002 10:15:55 -0500474_- Can anyone recommend a reference work/red book on Program design for checkpoint restartability for TSO Batch execution mode. Can't find a great deal in the DB2 product manuals. May be in there but I haven't found it.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 22719 11 17_IDBACK and IDFORE13_John Arbogast32_john.arbogast.cquz@STATEFARM.COM31_Thu, 20 Jun 2002 10:53:39 -0500348_- > Does anyone know of a way to proactively track high-water marks for IDBACK > and IDFORE?

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 22731 158 35_Re: Microsoft drop support for JAVA15_Blaney, Bill L.21_Bill.L.Blaney@SSA.GOV31_Thu, 20 Jun 2002 11:55:43 -0400449_iso-8859-1 Nothing. Since M$ current java "support" is for a derivative of an older version of java, you a most likely using a jre from IBM or Sun. Since neither IBM nor Sun has lost its enthusiasm for java, I think these tools be OK ;)

-----Original Message----- From: Galambos, Robert [mailto:Robert_Galambos@COMPUWARE.COM] Sent: Thursday, June 20, 2002 11:30 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Microsoft drop support for JAVA [...] 22890 29 20_Re: Rexx help needed13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 20 Jun 2002 11:56:06 -0400603_iso-8859-1 Sorry Shan,

I thought the explanation that ISPF was probably intercepting the & (ampersand) would have led to the solution.

David Seibert Compuware Corporation File-AID Product Architect Dave.Seibert@compuware.com









The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...] 22920 105 30_Re: Row Level Locking Overhead14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 20 Jun 2002 11:28:46 -0500519_us-ascii I found this interesting little blurb in the Admin Guide:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Row locks or page locks? The question of whether to use row or page locks depends on your data and your applications. If you are experiencing contention on data pages of a table space now defined with LOCKSIZE PAGE, consider LOCKSIZE ROW. But consider also the trade-offs. [...] 23026 55 43_Microsoft to drop support for JAVA from SUN16_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM31_Thu, 20 Jun 2002 13:00:47 -0400561_iso-8859-1 Following is a news art. about Microsoft dropping support for JAVA within windows. What will happen now with all these DB2 tools that run on the PC platform using JAVA?



http://www.komotv.com/stories/18966.htm



"Service Is Our Best Product"

Robert Galambos Compuware Corp. Of Canada

1-800-348-8299

1-877-950-3594 (Quebec) 1-905-803-8603 (fax)

Les renseignements contenus dans le présent message électronique sont confidentiels et concernent exclusivement le(s) destinataire(s) désigné(s). [...] 23082 88 30_Re: Row Level Locking Overhead0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 20 Jun 2002 12:04:22 -0500555_us-ascii Thanks all for your comments. To respond ...

Dr Ebert & Steve Tennant: I agree, yes the improved run time was totally due to change in clustering order. Row locking has become 'bound' with this issue in our minds because of the problem we encountered at initial implementation (remember DB2 V2.3, TYPE 1 indexes only). As for trying the new clustering order with page locking and TYPE 2 indexes ... it sounds like a good idea, but I don't think I could convince the right people to let us try it. The only way I know of testing it is [...] 23171 129 40_Re: DB2 V6 OS390 index definition issues10_Missy Case28_Missy.Case@FIRSTDATACORP.COM31_Thu, 20 Jun 2002 12:13:09 -0500375_us-ascii Dr. Ebert, Thank you for pointing me in the right direction so I could find the problem. Embarrassment is a great teacher! This index definition should never have been set up with such a small piecesize, it looks like a bit of team education is in order. The piecesize * 1024 is exactly our high used RBA - imagine that! Boy does the devil DBA owe me for this! [...] 23301 94 33_Re: Clusterratio<100% after REORG14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 20 Jun 2002 12:22:30 -0500418_us-ascii It'd be interesting if you'd try it with PCTFREE 1 instead of PCTFREE 0, to see if that makes any difference. Not that it should, mind you, but...

Thanks, David











"Dr. Michael Ebert" To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Clusterratio<100% after REORG Sent by: DB2 Data Base Discussion List [...] 23396 112 59_Re: Problems building a 'SQL Stored Procedure' with the SPB17_Adam Wiedenhaefer20_adamw@IMAGERIGHT.COM31_Thu, 20 Jun 2002 13:33:13 -0400298_iso-8859-1 Thanks to all who helped me. It turned out that I needed to do two things.

1. Install Visual C++ (didn't know i needed it) :) 2. Edit the path statments. Take all statements pointing to visual studio out of the user path statement and copy them to the System path statement. [...] 23509 20 35_Re: Microsoft drop support for JAVA0_17_JTonchick@AOL.COM29_Thu, 20 Jun 2002 13:58:12 EDT569_US-ASCII All Microsoft is saying is that they will not supply JAVA with Windows. You will have to download it from Sun.

The only downside is that if the application was written to the Microsoft proprietary JAVA interfaces instead of the standard Sun JAVA, the application will not work.

Jim Tonchick

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 23530 76 29_Re: When do I get the SQLCODE14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 20 Jun 2002 13:09:00 -0500496_us-ascii No need to apologize -- I got the humor and appreciated it (my favorite way of explaining something is "it works auto-magically").

It just got me thinking "I wonder how this works under the covers?". I thought others might want to know too...

Thanks, David











Tim.Lowe@STPAUL.C OM To: DB2-L@LISTSERV.YLASSOC.COM Sent by: DB2 Data cc: Base Discussion Subject: Re: When do I get the SQLCODE List [...] 23607 95 30_Re: Row Level Locking Overhead0_22_Rohn.Solecki@MTS.MB.CA31_Thu, 20 Jun 2002 14:32:01 -0500375_us-ascii Maybe you can try that out one day (just a simple ALTER TABLESPACE .. LOCKSIZE PAGE)?

Interesting concept, depending what it takes to actually implement the change, beyond simply running the statement) it might make this type of test something I could 'sell'. When does the changed LOCKSIZE take effect? The SQL Reference does not say. [...] 23703 80 87_***BPUG CONFERENCE CALL ON TUNING TIPS AND TECHNIQUES FOR UDB EE/EEE-6/27/02@2PM CDT***12_Imran Kareem13_imran@BEZ.COM31_Thu, 20 Jun 2002 14:50:31 -0500510_iso-8859-1 TUNING TIPS AND TECHNIQUES FOR DB2 UDB EE/EEE VERSION 7

OVERVIEW:

The purpose of this free conference call on June 27, 2002 at 2:00PM CDT is to allow DB2 UDB Database Administrators (DBAs), Performance Analysts and Systems Administrators to discuss their real-life performance tuning and capacity planning experiences with DB2 UDB EE/EEE. By sharing both successes and failures, participants will help each other ensure they meet or exceed their DB2 UDB EE/EEE performance goals. [...] 23784 164 34_Re: AW: Row Level Locking Overhead12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 20 Jun 2002 14:39:28 -0500427_ISO-8859-1 How about Version 4? There are so many "It depends." statements that remembering all of them is unlikely. The biggest of the variables tend to be index scans and table space scans, data sharing, concurrency, contention and lock avoidance. I presume all indexes are type 2.

The old V4 Redbook, SG24-4725 includes a section 5.2 Scenario 2: CPU Cost of Row Locking that appears to match what you asked for. [...] 23949 126 30_Re: Row Level Locking Overhead14_Picou, Michael33_picou.michael@MAIL.DC.STATE.FL.US31_Thu, 20 Jun 2002 16:15:57 -0400581_- Changing the Locksize has more to do with the Plan/package/statement This is from the DB2 Manual:

Let S denote an SQL statement that refers to a table in the table space: The LOCKSIZE change affects S if S is prepared and executed after the change. This includes dynamic statements and static statements that are not bound because of VALIDATE(RUN). If the size specified by the new LOCKSIZE is greater than the size of the old LOCKSIZE, the change affects S if S is a static statement that is executed after the change. The hierarchy of lock sizes, starting with the [...] 24076 124 20_Re: Rexx help needed10_Todd Burch22_tburch@CDBSOFTWARE.COM31_Thu, 20 Jun 2002 15:33:11 -0500570_iso-8859-1 Shan wrote:

"My thinking was that DB2 list server has the brightest minds in the business and if there is a technique one could use to get this to work, then this is the group to ask."

Well, since you put it that way.... :-)

The REXX variable pool interface (on OS/390 - and I'm assuming that is what you are using since you mentioned SPUFI) is a fairly straightforward interface. To set a variable in REXX from a program (PL/1, ASM, etc.), you fill out a control block (SHVBLOCK) that points to the name of the variable, the length [...] 24201 112 29_Re: Sequence of Data Inserted14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 20 Jun 2002 16:15:33 -0500306_us-ascii "are there any setting so that the data inserted is in order"

Nope, sorry. As the Application Programming and SQL Guide says:

To retrieve rows in a specific order, use the ORDER BY clause. Using ORDER BY is the only way to guarantee that your rows are ordered as you want them. [...] 24314 17 13_Wait Sync I/O0_20_bjnigh@HOUSEHOLD.COM31_Thu, 20 Jun 2002 15:00:45 -0700322_us-ascii Does anyone know where I can find information regarding wait sync i/o? I have looked at the archives and they were not very helpful. If applications are always showing that they are in the 'wait sync i/o' state, is this a problem with the database or the query or not a problem we should worry about at all? [...] 24332 74 29_Floating-point numbers in DB214_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 20 Jun 2002 17:02:20 -0500550_us-ascii OK, now I'm confused (OK, OK, I'm always confused, but now I'm even more confused)

We want to store numbers that could possibly have 20 digits on either side of the decimal point (don't ask why -- it's a long story that believe me you do not want to hear).

Storing them as a DECIMAL(p,s) datatype won't work, as that only allows a total of 31 digits plus a sign - we could have 40 plus a sign. So, we thought we'd use FLOAT instead, which will store our number as a double precision floating-point number with a sign. You [...] 24407 68 25_Unqualified Rows Returned11_Ted Flowers25_TedFlowers@GROCERYBIZ.COM31_Thu, 20 Jun 2002 17:02:13 -0500694_us-ascii I'm currently receiving questionable results from a query that defies all the laws of DB2. I'm running V6.1 on an OS/390. The SQL is similar though not exactly the same as what's shown below.

SELECT COL_FWHS ,COL_ORNO ,COL_ITEM ,COL_SUBN ,COL_AQTY ,TAB_WPTI ,TAB_WPHI ,TAB_PRND ,UPQ ,REM ,PCTUP ,NEEDCHNG FROM (SELECT COL_FWHS ,COL_ORNO ,COL_ITEM ,COL_SUBN ,COL_AQTY ,TAB_WPTI ,TAB_WPHI ,TAB_PRND ,(TAB_WPTI * TAB_WPHI) - MOD(COL_AQTY,(TAB_WPTI * TAB_WPHI)) AS UPQ ,MOD(COL_AQTY,(TAB_WPTI * TAB_WPHI)) AS REM ,(TAB_WPTI * TAB_WPHI) / 100 * TAB_PRND AS PCTUP , CASE WHEN MOD(COL_AQTY,(TAB_WPTI * TAB_WPHI)) >= (TAB_WPTI * TAB_WPHI) / 100 * TAB_PRND THEN 'Y' ELSE 'N' END AS [...] 24476 99 29_Re: Unqualified Rows Returned13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 20 Jun 2002 17:47:29 -0500379_us-ascii Ted,

A quick guess (and I'm sure there are other permutations), but what if any of the four columns used in the CASE expression can be NULL? Or if TAB_PRND <= 0?

Just a thought. Regards Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com [...] 24576 43 29_Re: Unqualified Rows Returned14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Thu, 20 Jun 2002 15:48:56 -0700391_iso-8859-1 I am basing my speculation on your indentation - but I think you are missing a set of parenthesis the outer search looks ok (as edited below) - is it NEEDCHNG or NEECHNG?

Susan

-----Original Message----- From: Ted Flowers [mailto:TedFlowers@GROCERYBIZ.COM] Sent: Thursday, June 20, 2002 3:02 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Unqualified Rows Returned [...] 24620 31 13_Wait Sync I/O14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Thu, 20 Jun 2002 18:48:10 -0400612_ISO-8859-1 Barbara,

Sure it's a problem.... your application is waiting for I/O. Usually indictaes the need for buffer pool tuning, and dasd tuning. Naturally this depends on how much wait time you see any given job/transaction.

Regards, Joel

Message text written by DB2 Data Base Discussion List >Does anyone know where I can find information regarding wait sync i/o? I have looked at the archives and they were not very helpful. If applications are always showing that they are in the 'wait sync i/o' state, is this a problem with the database or the query or not a problem we should [...] 24652 110 30_Re: Row Level Locking Overhead14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Thu, 20 Jun 2002 18:27:50 -0500602_iso-8859-1 Thanks, David





----- Forwarded by David Waugh/DASIMS/NEBRLN on 06/20/02 06:30 PM -----

David Waugh To: DB2 Data Base Discussion List 06/20/02 05:45 PM cc: Subject: Re: Row Level Locking Overhead(Document link: David Waugh)







Hi Rohn:

I would think the new LOCKSIZE (and LOCKMAX if you specify it) would take effect after -STOP and -START. The SQL Reference does say that a -STOP is required for LOCKPART, so I would think LOCKSIZE would require it also. You're right though - it doesn't say. [...] 24763 17 12_V5 reprieved13_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Fri, 21 Jun 2002 09:31:33 +1000406_iso-8859-1 Is it true that due to a certain delay in Vnext that V5 will no longer be out of service at the end of 2002?

Regards,

Steve T

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 24781 189 33_Re: Floating-point numbers in DB20_22_Rohn.Solecki@MTS.MB.CA31_Thu, 20 Jun 2002 18:53:02 -0500491_us-ascii Dave, yes scientific notation, FLOAT data type, is highly accurate to umpteen bazillion decimal places . But what you have to realize, is that the scientists only work digits at the extreme left or right of the decimal. To a scientist, there is no "Significant" difference between 1 bazillion (however big that is ) and 1 bazillion + 1. so they can record 1 bazillion as 1.0E+?? (where ?? represents the number of zeros you need to make up your bazillion). [...] 24971 103 33_Re: Floating-point numbers in DB214_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 21 Jun 2002 11:07:10 +1000383_US-ASCII David,

Floating point numbers are stored as numbers in base 16. So anynumber that can be expressed as: 0.h1h2h3..hn * 16**x where - h1, h2, etc are hex digits ( 1<=H1<=F, 0<=hi<=F for i > 1) - n=7 for short floating point and n=15 for long floating point - -64 <= x <= +63 and an integer can, indeed, be stored as an exact number. All others are approximations. [...] 25075 55 26_Re: Database Access Thread14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 21 Jun 2002 11:07:10 +1000456_US-ASCII 1) SRBs are a control block which define work that can be dispatched. They differ in several respects from TCBs, the most important of which (as far as this question is concerned) is that all TCBs in an address space are controlled by WLM using the same service definition while each SRB can be controlled independantly. The "Using a Service Request Block" chapter in the OS/390/zOS Authorized Assembler Services Guide has a lot more detail. [...] 25131 40 80_Pittsburgh User Group - August 20th-21st - DB2 V7 Migration - Susan Lawson, YL&A10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Thu, 20 Jun 2002 21:16:12 -0400306_us-ascii On August 20th-21st, It's my great pleasure to have Susan Lawson come to Pittsburgh to teach a DB2 V7 Migration Class for @ $300 per person. The abstract for the class can be found on the website at http://www.db2parug.org. If you are interested in attending, please let me know. Tks!! Seeya [...] 25172 114 17_Re: Wait Sync I/O13_Robert Wright17_rwright@LIC.CO.NZ31_Fri, 21 Jun 2002 14:10:08 +1200399_us-ascii SYNC I/O may be caused by a JOIN that is not optimised for the structure of the data. This is generally referred to as death by random I/O. Sometimes you may need to code a nested table expression in order to force the data into a better order for the JOIN. ALternatively, you may be better off changing the CLUSTERing of the data if the type of JOIN is common among the applications. [...] 25287 142 24_Re: Logical unit of work12_Raymond Bell17_rbell@NZ1.IBM.COM31_Fri, 21 Jun 2002 14:20:15 +1200387_us-ascii Adam,

I think James already answered your question. A rollback can specify rolling back to a SAVEPOINT, in which case SQL performed between the beginning of the unit of work and the SAVEPOINT is preserved. You will still need to either commit or rollback this work. If you don't specify a SAVEPOINT in your rollback statement the entire unit of work is rolled back. [...] 25430 203 35_Re: Microsoft drop support for JAVA0_15_leon@CA.IBM.COM31_Thu, 20 Jun 2002 22:23:53 -0400479_iso-8859-1 Microsoft's decision to provide or withdraw support for Java has NO IMPACT ON DB2. DB2 install media contains all of the Java enablement that is required to run Java tools on any platform regardless of the Java enablement provided by the platform vendor. DB2 media contains IBM JDK.

The situation with Microsoft and Java makes for good entertainment but has almost no impact on the lives of real people as most people do not use Microsoft JVM in any case. [...] 25634 123 24_Re: Logical unit of work14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 21 Jun 2002 12:30:35 +1000417_US-ASCII WORK is a fluff word - if you don't specify it, it is assumed.

Yes, in the example below, ROLLBACK will undo the 6 inserts, while ROLLBACK TO SAVEPOINT SP1 will undo the last 4.

a SP can connect to more than one database/system - provided it uses TYPE 2 connects.

BTW, which platform are you talking about? There are differences between DB2 for OS390/zOS and DB2 for Multiplatforms [...] 25758 31 37_Re: Taking Backup/Restore on Linux OS14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 21 Jun 2002 12:30:36 +1000545_US-ASCII The manuals are available at http://www-3.ibm.com/cgi- bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d2w/en_main

Download a PDF or zipped HTML version and keep it handy.

Jmaes Campbell



On 20 Jun 2002 at 10:30, Saima Jelani wrote:

> hello > i have database on DB2 of linux. Can any one tell me how to take the back up of database and then restore the data into the another database from coomand promp > please give the exact syntax, because i dont have control panel installed > > Thanks > > [...] 25790 31 20_Re: Rexx help needed14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 21 Jun 2002 12:31:58 +1000358_US-ASCII It turned out (in a later posting to TSO-REXX) that the type of code being used was:

resource = 'A&B' rc = 0 SQ1 = "SELECT ACL_ID, ACCESS_IND", "FROM SYSADM.TB_IS_RSRC_NT_ACL", "WHERE UCASE(NT_RESOURCE) = UCASE('"resource"')" 'ISPEXEC SELECT', 'CMD(TSO2DB2 "'sq1'" TABLE(NTRSRC) VAR(COL) SSN('ssid'))' If rc > 0 THEN say 'rc = ' rc 'SQL [...] 25822 42 23_DB2 Admin Tool question12_Raymond Bell17_rbell@NZ1.IBM.COM31_Fri, 21 Jun 2002 14:49:09 +1200585_us-ascii Colleagues,

This is for anyone out there that has the DB2 Administration Tool V3.1.0. We've (OK, I've) installed this in both a V5 and a V7 subsystem. There are 5 prompt options you can optionally turn on/off when you generate bind statements, SQL alters, etc. If I have these prompts turned off I can generate and run all kinds of statements/commands in both V5 and V7 OK. However if I turn on all the prompts, you're supposed to get the option to edit the generated statements/commands before executing them. This works fine for the V7 subsystem but not for the [...] 25865 177 29_Re: Sequence of Data Inserted9_Ali Akbar25_ali.akbar@CRESSOFT.COM.PK31_Fri, 21 Jun 2002 11:08:14 +0500539_us-ascii



Thanks everyone for the information which was a very useful.

Ali











"Dr. Michael Ebert" on 06/20/2002 02:12:29 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@LISTSERV.YLASSOC.COM cc: (bcc: Ali Akbar/CresSoft)

Subject: Re: Sequence of Data Inserted





Hi

you do not specify your platform. On OS/390, the situation is as follows, as far as I know: [...] 26043 28 17_Re: Wait Sync I/O13_Martin Packer24_martin_packer@UK.IBM.COM31_Fri, 21 Jun 2002 07:40:36 +0100491_us-ascii In DB2 Version 6 you can get a break out of Sync I/O Wait Time into Database (usually Read) I/O time and Log Write time.

Generally, though, Sync I/O Wait Time is dominated by database read misses.

Parenthetically, the SMF 101 Accounting Trace record was changed incompatibly in Version 6 to handle this (and other similar) enhancements. We had to code around it and I mention it in case people haven't reviewed their analysis code for the migration to Version 6. [...] 26072 18 44_Michael Theunissen/TMG is out of the office.18_Michael Theunissen30_Michael.Theunissen@CLARICA.COM31_Fri, 21 Jun 2002 03:16:03 -0400431_us-ascii I will be out of the office from 20/06/2002 until 24/06/2002.



For DB2 questions, email DBA or call x5252

otherwise I will respond when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 26091 138 30_Re: Row Level Locking Overhead17_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Fri, 21 Jun 2002 09:44:10 +0200551_us-ascii According to the SQL Ref (ALTER TABLESPACE LOCKSIZE), the change takes effect for a dynamic statement the next time it is prepared and executed. For a static statement, it takes effect the next time you run it IF you increase the "size" of the lock; if you leave it unchanged or decrease it, you have to rebind. Note that PAGE and ROW locks have the SAME size. This means that you'll have to rebind your OLTP packages. This also means that you can do the ALTER and rebind just one or a few packages to gauge the effect without affecting [...] 26230 47 43_DB2 Connect and INTERRUPT_ENABLED parameter19_Calleja Vilar, Juan22_jcallejav@IBERDROLA.ES31_Fri, 21 Jun 2002 10:05:34 +0200589_iso-8859-1 Hello list. We have recently put into production a Visual Basic application that calls a DB2 for OS/390 stored procedure using DB2 Connect. The application eventually received the following error message:

[IBM][CLI Driver][DB2] SQL30081N Se ha detectado un error de comunicaciones. Protocolo de comunicaciones utilizado: "TCP/IP". API de comunicaciones utilizada: "SOCKETS". Ubicación donde se ha encontrado el error: "". Función de comunicaciones que ha detectado el error: "recv". Código(s) de error específico(s) del Protocolo: "10038", "*", "*" . SQLSTATE=08001 [...] 26278 187 33_Re: Floating-point numbers in DB217_Dr. Michael Ebert18_mebert@AMADEUS.NET31_Fri, 21 Jun 2002 10:30:07 +0200633_us-ascii The References you cite are somewhat misleading. The "real numbers" they talk about are not the "Real Numbers" mathematicians mean (like Pi or SQRT(2)). What the References mean is "numbers in decimal notation". As others have remarked, IBM Mainframes store floating point numbers in Base 16 (with Characteristic 64, 1 sign bit, 7 bit exponent, 56 bits mantissa). Nearly every other computer stores them in IEEE format: base 2, characteristic 1023, 1 sign bit, 11-bit exponent, 52 bits mantissa plus one implied leading "1" bit (normalised numbers). In general, you cannot convert from one base to another and back and [...] 26466 30 17_Re: Wait Sync I/O10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 21 Jun 2002 10:34:38 +0200549_us-ascii Hi

As Joel said, it's a problem and if I/O wait is always present it's time to tune BP tuning and DASD I/O.

In general it's a DASD/BP problem but as it was pointed out some query may request an excessive number of records. DASD response time maybe checked via RMF or some tool (SMF record 42 subtype 6) and may be compared with the I/O wait in the ACCT report . Of course your dasd folks have to check which response time component is high and to behave in consequence. You've to involve your DASD manager(s) to obtain a [...] 26497 109 49_Re: IFI and Ifcid 318 (Dynamic Cache information)22_Bertolino, Salvatore S26_SBertolino@MAIL.SBIC.CO.ZA31_Fri, 21 Jun 2002 12:02:32 +0200394_iso-8859-1 Leslie,

All IFCID 318 does is start the collection of the counter information for IFCID 316. IFCID 317 contains the detail information regarding the SQL statement IN IFCID 316. I have used transaction ST04 to analyse the cache, but we have instead written an assembler program that extracts the 316 and 317 IFCID RECORDS and writes them to DB2 tables for later analysis. [...] 26607 31 37_Re: Taking Backup/Restore on Linux OS13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Fri, 21 Jun 2002 12:41:01 +0000348_- You use the DB2 backup and restore commands and a rollforward.

If you are putting the new database onto the same machine then you will have to do a redirected restore (as you need to change your tablespace locations).

I have a script which will write the basics of this for you, which I can email privately to you if you want. [...] 26639 20 17_Version 7 manuals12_Shafer Roy E24_ShaferRoyE@JOHNDEERE.COM31_Fri, 21 Jun 2002 06:52:59 -0500537_iso-8859-1 Does anyone know where to order hard copy manuals DB2 Universal Database for OS/390 for Version 7? I'm looking for SQL Reference, Utility Guide and Reference and etc. I tried (with no luck) to find them on IBM's Publications Website.

Thanks in advance,

Roy

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 26660 16 21_Re: Version 7 manuals14_Clayton, Colin21_Colin_Clayton@BMC.COM31_Fri, 21 Jun 2002 07:49:31 -0500428_iso-8859-1 Roy,

try http://www-3.ibm.com/software/data/db2/os390/v7books.html or http://www-3.ibm.com/software/data/db2/os390/v6books.html depending on your version.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 26677 21 16_DB2CONNECT TRACE44_Esterno Oberto Franco (Rif.Blangetti Franco)21_F.OBERTO.globc@RAI.IT31_Fri, 21 Jun 2002 14:50:03 +0200460_iso-8859-1 Hi,

I print the DB2 Connect Trace .

Where can i find any information for read it ? In which measure ( sec, millisec ) the " Time elapsed" is it writed ?

TIA , Franco Oberto

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 26699 93 21_Re: Version 7 manuals23_Daniel Van Nieuwenhuyze32_daniel.vannieuwenhuyze@SIDMAR.BE31_Fri, 21 Jun 2002 14:48:42 +0200593_us-ascii Roy, You can find the Version 7 manuals at http://www-3.ibm.com/software/data/db2/os390/v7books.html Good luck ! Daniel







ShaferRoyE@JOHNDEERE.COM Sent by: DB2-L@LISTSERV.YLASSOC.COM 21/06/2002 13:52 Please respond to DB2-L



To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: Version 7 manuals



Does anyone know where to order hard copy manuals DB2 Universal Database for OS/390 for Version 7? I'm looking for SQL Reference, Utility Guide and Reference and etc. I tried (with no luck) to find them on IBM's Publications Website. [...] 26793 141 30_Re: Row Level Locking Overhead0_19_csutfin@AMSOUTH.COM31_Thu, 20 Jun 2002 15:30:35 -0500338_us-ascii Rohn,

I finally had to put my two cents worth in.

Switching from row level locking during your on-line window to page or even tablespace locking during your batch window may help.

We have several tablespaces that do the same thing. I have also used this at in various other shops for just that purpose. [...] 26935 81 78_version 7 question regarding "OPTIMIZE FOR 1 ROWS" vs "FETCH FIRST 1 ROW ONLY"0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Fri, 21 Jun 2002 08:26:44 -0500379_us-ascii Since I am currently on version 6, I cannot find the answer to this question. Could some of you help me out?

When I run the following through an explain, I get the noted estimate of CPU time.

__________

SELECT * FROM BIG_TABLE

(Cpu estimate is 2,000,000 milliseconds)

__________

SELECT * FROM BIG_TABLE OPTIMIZE FOR 1 ROWS [...] 27017 62 38_DB2 DBA - Permanent Position in the UK10_Dave Hayes28_Dave.Hayes@MORGANSTANLEY.COM31_Fri, 21 Jun 2002 14:37:10 +0100616_us-ascii All

Morgan Stanley are looking for an experienced mainframe DB2 DBA to work at our offices in London. The job description can be found below, but we are looking for a combination of strong DB2 skills and good communication skills. This is a permanent position so we are unable to consider contractors/consultants at this time. This is a senior role and so the 4 years experience in the Skills Required section should be considered a minimum. Good benefits package available. Salary dependent on experience. If you are interested, please forward your CV/resume to Eleanor.Mann@morganstanley.com. [...] 27080 209 21_Re: Version 7 manuals18_Angela M Arettines28_aarettines@KEYSPANENERGY.COM31_Fri, 21 Jun 2002 09:40:52 -0400649_us-ascii Hi Roy,

We recently found that information out from IBM. They told us the following:

To order hardcopy manuals from their website, (1) www.ibm.com/shop/publications/order (2) Select a country.

It's self-explanatory from there. A couple of tips that IBM gave us, though - If you choose 'Order Entry', you can enter the Order Number (Publication No.) for a manual (e.g. Enter 'LY37-3740-00' for DB2 Diagnosis Guide and Reference), ...If you then click on "Validate Publications" and then click on the "Publication no." , you can get a description of the manual and the price. Also, if you enter several manuals that [...] 27290 110 82_Re: version 7 question regarding "OPTIMIZE FOR 1 ROWS" vs "FETCH FIRST 1 ROW ONLY"15_Shields Michael28_ShieldsMichael@JOHNDEERE.COM31_Fri, 21 Jun 2002 08:57:24 -0500540_iso-8859-1 "OPTIMIZE FOR 1 ROWS" basically indicates whether it is useful to prefetch the data based on the index and ratio %. The database will still go after all of the rows that fit your criteria... in your case you're getting every row in the table back. "FETCH FIRST x ROWS ONLY" will get the first x rows that fit your criteria and return that data to you. SO... if you specify FETCH FIRST 5 ROWS ONLY on your SELECT * FROM BIG_TABLE without a WHERE clause all rows qualify, but you'll only hit the first 5 rows that qualify and [...] 27401 79 26_Re: db2empfa and tempspace13_Dave Prentice22_dave.prentice@AEXP.COM31_Fri, 21 Jun 2002 09:06:18 -0500371_us-ascii Well the response to my db2empfa/tempspace questions to the list was a deafening silence, so I asked IBM. OK, so I should have done that first. Or perhaps I should have snazzed up the email a little.

Anyway (in case anyone is interested!) here is IBM's answer:

db2empfa has no effect on temp tablespaces. Here is an email detailing this tool: [...] 27481 15 42_Re: DB2 DBA - Permanent Position in the UK10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 21 Jun 2002 16:21:06 +0200374_us-ascii It's my job !! It's a pity the position is in London.....No position near Venice :-)))) ???

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 27497 58 50_Re: Building sql statement within stored procedure19_Hynes, Carol A. DOC27_Carol.Hynes@DOC.STATE.WI.US31_Fri, 21 Jun 2002 09:30:39 -0500535_iso-8859-1 Thanks for the sight reference. I appreciate the help.

-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: Wednesday, June 19, 2002 10:05 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Building sql statement within stored procedure



Toddle over to http://publib- b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg246418.html

DB2 for z/OS and OS/390 : Squeezing the Most Out of Dynamic SQL (couldn't help but notice that Suresh is one of the co-authors). [...] 27556 46 20_Re: Rexx help needed17_Robert J. Milonas20_xrjmbob@NETSCAPE.NET31_Fri, 21 Jun 2002 10:47:08 -0400661_iso-8859-1 What is the web address for the TSO-REXX forum?????????



"Seibert, Dave" wrote:

>Hello Shan, > > Didn't Dan get his answer over on the TSO-REXX forum? > >The thoughts there were that it's happening in ISPF handling of the Rexx >program. > >David Seibert >Compuware Corporation File-AID Product Architect >Dave.Seibert@compuware.com > > > > > >The contents of this e-mail are intended for the named addressee only. It >contains information that may be confidential. Unless you are the named >addressee or an authorized designee, you may not copy or use it, or disclose >it to anyone else. If you [...] 27603 26 42_Re: DB2 DBA - Permanent Position in the UK10_Dave Hayes28_Dave.Hayes@MORGANSTANLEY.COM31_Fri, 21 Jun 2002 15:51:32 +0100539_us-ascii Max On the map I've got, the distance from Venice to London is about 6cm. That's not very far - couldn't you commute? ;-) Dave

Max Scarpa wrote:

> It's my job !! It's a pity the position is in London.....No position near > Venice :-)))) ??? > > Max Scarpa > > ================================================ > To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. [...] 27630 40 30_Re: Row Level Locking Overhead10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 21 Jun 2002 16:54:56 +0200574_us-ascii Hi estimeed listers

I follow this thread with interest as I never use row locking as I resolved all hot spot via MAXROWS (if table small) or some other trick (COMMIT policy mainly,etc).

As I never stop to learn I ask some questions to the lister who use/used RLL:

1) When you use RLL is it mandatory to increase NUMLKTS/NUMLKUS as your lock can increase 10x and you can meet 00C90096 error ? According to Yevich et al (IDUG 2001, Florence) there's the risk to increase contention using RLL and deadlocks so have I to modify IRLMWT ZPARM ? [...] 27671 149 30_Re: Row Level Locking Overhead14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Fri, 21 Jun 2002 08:09:50 -0700360_iso-8859-1 Patric, Calculating times for lock allocation and release are more complex. While each lock required results in a single lock request, the unlock process may be "batched" in that multiple locks may be released in a single request. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 27821 23 42_Re: DB2 DBA - Permanent Position in the UK10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 21 Jun 2002 17:17:34 +0200384_us-ascii MMhhhh

Oh It's an idea.... I colud use the famous Acme Catapult as Wyle Coyote (my hero)...or I could use my paraglider with a propeller and a small engine...the problem might be the Channel...I'll consider you suggestions ; -)))

OK FOLKS folks it' friday and I had a bad week fighting with ESCON channels (and ORACLErs)......my excuses if I bothered you. [...] 27845 66 20_Re: Rexx help needed11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Fri, 21 Jun 2002 10:17:55 -0500780_iso-8859-1 The archive logs are at http://www.marist.edu/htbin/wlvindex?TSO-REXX.

-----Original Message----- From: Robert J. Milonas [mailto:xrjmbob@NETSCAPE.NET] Sent: June 21, 2002 9:47 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Rexx help needed



What is the web address for the TSO-REXX forum?????????



"Seibert, Dave" wrote:

>Hello Shan, > > Didn't Dan get his answer over on the TSO-REXX forum? > >The thoughts there were that it's happening in ISPF handling of the Rexx >program. > >David Seibert >Compuware Corporation File-AID Product Architect >Dave.Seibert@compuware.com > > > > > >The contents of this e-mail are intended for the named addressee only. It >contains information that may be [...] 27912 72 47_Re: DB2 Connect and INTERRUPT_ENABLED parameter13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Fri, 21 Jun 2002 10:34:43 -0500548_iso-8859-1 Juan,

If you do not specify INTERRUPT_ENABLED, clients can do things such as cancel their application in mid-stream, or shut down their PC and go home, leaving long running SQL churning away in DB2 for no useful purpose.

Perhaps the VB app could be altered to not lose it's connection?

Dave



-----Original Message----- From: Calleja Vilar, Juan [mailto:jcallejav@IBERDROLA.ES] Sent: Friday, June 21, 2002 3:06 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: DB2 Connect and INTERRUPT_ENABLED parameter [...] 27985 74 20_Re: Rexx help needed15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Fri, 21 Jun 2002 11:36:00 -0400670_iso-8859-1 For TSO-REXX subscribe / signoff / archive access instructions, send email to LISTSERV@VM.MARIST.EDU with the message: INFO TSO-REXX

Bob Lawrence DBA Boscov's Dept Stores LLc





> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@LISTSERV.YLASSOC.COM]On Behalf Of Robert J. Milonas > Sent: Friday, June 21, 2002 10:47 AM > To: DB2-L@LISTSERV.YLASSOC.COM > Subject: Re: Rexx help needed > > > What is the web address for the TSO-REXX forum????????? > > > "Seibert, Dave" wrote: > > >Hello Shan, > > > > Didn't Dan get his answer over on the TSO-REXX forum? > > > >The [...] 28060 74 30_Re: Row Level Locking Overhead12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 21 Jun 2002 08:42:16 -0700585_us-ascii Max, I've used RLL a number of times in carefully selected spots with great effectiveness.

1) I've never had to increase NUMLKTS/NUMLKUS. And I've never run into the 00C90096 error. Plus my overall locking for that table hasn't increased 10x. At most twice. Remember, carefully selected tables.

2) The few V4 tests I ran I couldn't measure the CPU increase for the tables we defined this on, certainly nothing like 40%. The variability in day to day and hour to hour processing was greater than the increase in CPU. I honestly couldn't find any CPU impact. [...] 28135 92 50_Re: Building sql statement within stored procedure11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 21 Jun 2002 12:43:23 -0500331_- Carol,

Within the Redbook "Squeezing the most out of dynamic SQL" you will find a reference to complee sample code you can download. This has all variations of dynamic SQL you will ever need in COBOL, PL/I, REXX and ODBC C. No one should need to start from scratch (good luck, assembler gurus, I don't do assembler). [...] 28228 25 33_Re: Floating-point numbers in DB214_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 21 Jun 2002 12:47:37 -0500372_us-ascii Thanks James, that answers my questions. Looks like we're going to have to do one of the following:

1. Split the numbers up into 2 columns, one for everything to the left of the decimal point, one for everything to the right. Unfortunately, we run the risk of losing up to 3 digits precision on each side for those numbers that could be 20 precision. [...] 28254 62 42_Re: DB2 DBA - Permanent Position in the UK14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 21 Jun 2002 13:21:52 -0500478_us-ascii Hey -- for the right money, I'LL commute -- and I'm a heck of a lot farther away (Lincoln Nebraska USA) than 6cm...

Thanks, David











Dave Hayes cc: Sent by: DB2 Data Subject: Re: DB2 DBA - Permanent Position in the UK Base Discussion List



06/21/02 09:51 AM Please respond to DB2 Data Base Discussion List [...] 28317 21 23_View, Alias, or Synonym10_Vinson Lee19_vlee@SDCCD.CC.CA.US31_Fri, 21 Jun 2002 11:46:53 -0700430_iso-8859-1 We're looking into using Views for the first time to allow our batch programs to access different database tables while still using the same table qualifier. This will allow us to use our existing migration, compile, and bind procedures between different environments. I just wanted to find out how other shops were using Views, Aliases, or Synonyms. What are the limitations and best applications of each method? [...] 28339 83 42_Re: DB2 DBA - Permanent Position in the UK16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Fri, 21 Jun 2002 14:48:59 -0400467_iso-8859-1 Wouldn't you miss the 11.9 months of winter in NE?

Regards, eric pearson NS ITO Database Support



-----Original Message----- From: David S. Waugh [mailto:dwaugh@NOTES.STATE.NE.US] Sent: Friday, June 21, 2002 2:22 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: DB2 DBA - Permanent Position in the UK



Hey -- for the right money, I'LL commute -- and I'm a heck of a lot farther away (Lincoln Nebraska USA) than 6cm... [...] 28423 12 42_Re: DB2 DBA - Permanent Position in the UK0_26_truman.g.brown@VERIZON.COM31_Fri, 21 Jun 2002 14:49:27 -0400309_us-ascii Cor, blimey mate - I'm all right here, Jack.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 28436 37 41_Static SQL becomes Dynamic after Runstats45_=?iso-8859-1?Q?=22LeBlanc=2C_Andr=E9-ITB=22?=30_AndreD.Leblanc@CCRA-ADRC.GC.CA31_Fri, 21 Jun 2002 15:14:18 -0400386_iso-8859-1 I received the following comment from my application which surprised me:

"Since in Version 6 if the run time statistics date is greater than bind timestamp, it will do dynamic instead of static.."

Is this true? I don't recall seeing this before. I've always believed that a rebind was required to change the access path of static sql when we ran Runstats. [...] 28474 134 30_Re: Row Level Locking Overhead14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 21 Jun 2002 14:16:19 -0500421_us-ascii Hi Max--

My answer to #1 is the standard IBM answer: "It depends" (can you tell I used to work for IBM?)

- I agree with your way of going about it -- start with the tricks of the trade first (good database design for concurrency, COMMIT strategies, MAXROWS, WITH UR, OPTIMIZE FOR n ROWS, FOR UPDATE OF, FOR FETCH ONLY, etc.), then if absolutely nothing else works, consider Row-Level Locking. [...] 28609 102 26_Re: Database Access Thread19_Luiz Roberto FORONI27_lforoni@CAIXASEGUROS.COM.BR31_Fri, 21 Jun 2002 16:34:10 -0300507_iso-8859-1 I need to convince some Java´s Programmers to use Tso environment for "heavy queries" They are using DDF to execute "heavy queries" , what I think is not good to overall performance.

---------------------- Encaminhado por Luiz R Foroni/MATRIZ/SASSE em 21/06/2002 16:27 ---------------------------



James Campbell @LISTSERV.YLASSOC.COM> em 20/06/2002 22:07:10

Favor responder a DB2 Data Base Discussion List [...] 28712 127 42_Re: DB2 DBA - Permanent Position in the UK14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 21 Jun 2002 14:33:03 -0500511_us-ascii I don't know -- I just moved here in March (from New Mexico) -- haven't had to suffer through a winter here yet. OK, I caught the tail end of a pretty mild winter (they tell me) -- 3 snow storms and 2 months of howling wind.

I am currently suffering through hot & humid though (not to mention wind, rainstorms, tornado alerts, softball-sized hail, 2 and 3 severe weather alerts per day, ...) and it's only June. Yes, folks -- it's summer in the Great Plains! Whoopieeeeeeeeeeeeeeeeeeeee... [...] 28840 51 27_Re: View, Alias, or Synonym10_James Kwan19_jamesdb2kwan@CS.COM31_Fri, 21 Jun 2002 14:41:17 -0500454_iso-8859-1 Vince,

Although it is possible to use view to control different target tables, most shop use same table name with different qualifier. If this is your new application, you should consider using the later method. It will be very confusion and difficult to maintain all tables/view relations among different environments.

Regards,

James Kwan IBM Certified Solutions Expert - DB2 V7.1 Database Administration for OS/390 [...] 28892 43 27_Re: View, Alias, or Synonym10_Dale Smock20_Dale.Smock@BMSUS.COM31_Fri, 21 Jun 2002 15:01:25 -0500468_iso-8859-1 We use Alias with 3-part names to access tables in other DB2 systems through DDF, usually for select only access to large production tables from test systems. We use Synonyms to reference a table with an alternate name, like 8 character abreviated names for 18 character tablenames. We use Views to restrict access to a subset of columns, reference by alternate column names, limit data retrieved with Where clauses, or do complex joins for end-users. [...] 28936 55 45_Re: Static SQL becomes Dynamic after Runstats10_James Kwan19_jamesdb2kwan@CS.COM31_Fri, 21 Jun 2002 14:59:57 -0500481_iso-8859-1 Andre

It will do dyanmic what? I don't believe it is true. You might want to clarify the statement.

Regards,

James Kwan IBM Certified Solutions Expert - DB2 V7.1 Database Administration for OS/390

----- Original Message ----- From: "LeBlanc, André-ITB" Newsgroups: bit.listserv.db2-l To: Sent: Friday, June 21, 2002 2:14 PM Subject: Static SQL becomes Dynamic after Runstats [...] 28992 91 45_Re: Static SQL becomes Dynamic after Runstats45_=?iso-8859-1?Q?=22LeBlanc=2C_Andr=E9-ITB=22?=30_AndreD.Leblanc@CCRA-ADRC.GC.CA31_Fri, 21 Jun 2002 17:01:42 -0400395_iso-8859-1 Sorry for the confusion James. By dynamic, I mean to say that the access path is determined at run time instead of bind time.

Thanks, André LeBlanc ICS Database 952-2559



-----Original Message----- From: James Kwan [mailto:jamesdb2kwan@CS.COM] Sent: June 21, 2002 4:00 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Static SQL becomes Dynamic after Runstats [...] 29084 100 45_Re: Static SQL becomes Dynamic after Runstats10_James Kwan19_jamesdb2kwan@CS.COM31_Fri, 21 Jun 2002 16:50:49 -0500579_iso-8859-1 Andre,

Again, I don't believe the statement is true.

Regards,

James Kwan IBM Certified Solutions Expert - DB2 V7.1 Database Administration for OS/390

----- Original Message ----- From: "LeBlanc, André-ITB" Newsgroups: bit.listserv.db2-l To: Sent: Friday, June 21, 2002 4:01 PM Subject: Re: Static SQL becomes Dynamic after Runstats



Sorry for the confusion James. By dynamic, I mean to say that the access path is determined at run time instead of bind time. [...] 29185 41 45_SOS! Partitioned Tablespace Recovery Question11_Linda Ayeni22_lindaayeni@HOTMAIL.COM31_Fri, 21 Jun 2002 15:28:25 -0700371_- Dear listers, Could someone help me understand how DSN1COPY recovery to a partitioned tablespace works. I have a production full image copy tapes that I need to DSN1COPY to our test environment and some of the tablespaces are partitioned. The question I have are (1) What is the correct syntax (JCL) to properly recover to a partitioned tablespace using DSN1COPY? [...] 29227 168 45_Re: Static SQL becomes Dynamic after Runstats14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 21 Jun 2002 17:40:24 -0500561_iso-8859-1

David Waugh To: DB2 Data Base Discussion List 06/21/02 04:58 PM cc: Subject: Re: Static SQL becomes Dynamic after Runstats (Document link: David Waugh)







André:

I think what you're getting at (I'm losing something in the translation, so I may be wrong) is this:

-------------------------------------------------------------------------------------------------------------------------------------------- (Snipping liberally from the DB2 for OS/390 and z/OS manuals) [...] 29396 16 50_Jaroslaw Lagowski/Poland/IBM is out of the office.17_Jaroslaw Lagowski21_J.Lagowski@PL.IBM.COM31_Sat, 22 Jun 2002 01:20:38 +0200429_us-ascii I will be out of the office starting June 22, 2002 and will not return until July 1, 2002.

I'm on vacation now. I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com. 29413 146 49_Re: SOS! Partitioned Tablespace Recovery Question14_David S. Waugh24_dwaugh@NOTES.STATE.NE.US31_Fri, 21 Jun 2002 19:03:57 -0500562_us-ascii Hi Linda:

It's been awhile, so let me see if I remember...

(1) What is the correct syntax (JCL) to properly recover to a partitioned tablespace using DSN1COPY?

//DSN1COPY EXEC PGM=DSN1COPY,PARM='OBIDXLAT' // PARM='NUMPARTS(nn),RESET,FULLCOPY' //SYSUDUMP DD SYSOUT=A //SYSPRINT DD SYSOUT=A //SYSUT1 DD DISP=OLD,DSN=Source.Image.Copy.Dataset //SYSUT2 DD DISP=OLD,DSN=DSNCAT.DSNDBD.Target.Tablespace.I0001.A001 //SYSXLAT DD * Source DBID,Target DBID Source PSID,Target PSID Source OBID,Target OBID Source OBID,Target OBID .... /* [...] 29560 38 21_Partitioning question16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Fri, 21 Jun 2002 21:51:16 -0700494_us-ascii Hello !!

On UDB 7.0 for Linux, I am trying to (unsuccessfully) understand how partitioning keys are mapped to partitions. The Admin. Guide says the following:

"If the partitioning key for a table to be loaded in the database is an integer that has possible values between 1 and 500 000, the partitioning key is hashed to a partition number between 0 and 4 095. That number is used as an index into the partitioning map to select the database partition for that row" [...]