1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l March 2000, week 2
2 154 17_Trigger Defintion29_Sireesh Chandra.M,J16 Chennai25_msireesh@MSDC.HCLTECH.COM30_Wed, 8 Mar 2000 11:58:31 +0530388_iso-8859-1
To the esteemed list,
I am trying to create a simple trigger in DB2 UDB V6.1 for OS/390, but inturn getting the following error message.
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: QUOTE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR [...]
157 121 41_Re: Cursor name on DECLARE/OPEN and FETCH14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 8 Mar 2000 18:52:50 +1000512_- The name of a cursor is not something that you can specify in a host variable. It is a constant.
I suspect you will need to (and here my skimpy C++ knowledge will be exposed for the thin veneer it really is) have a set of cursors and use class variables to determine which are "in use" at a given moment. When your method needs a to open a cursor, it uses the class variables to find an unused one and saves its name as an object variable. When a method needs to, for example, fetch a row, it will [...]
279 119 34_Re: Strange Problem - DB2 Stogroup14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 8 Mar 2000 19:10:43 +1000569_- Rick,
I am not sure what you are asking. I suspect, though, a carefull study of the tables contained in the 'Catalog Formats' chapter of the Diagnosis Guide and Reference might answer it.
If it is not, please expand on your question.
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:RD8246@MOMAIL.SBC.COM] Sent: Tuesday, March 07, 2000 11:03 PM To: DB2-L@RYCI.COM Subject: Re: Strange Problem - DB2 Stogroup [...]
399 232 22_Re: Trigger Definition14_Julian Stuhler27_julian.stuhler@TRITON.CO.UK30_Wed, 8 Mar 2000 00:28:05 -0800406_- Trigger DefintionHi Sireesh.
My guess is that you're using SPUFI to create the trigger, and the semi-colon (;) character terminating your UPDATE statement is causing the problem as SPUFI is seeing this as the end of the CREATE TRIGGER statement.
Try changing the statement terminator to some other character (such as a colon (:) ) on your SPUFI defaults panel, and give it another go. [...]
632 86 21_Re: Trigger Defintion14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 8 Mar 2000 19:30:37 +1000388_iso-8859-1 Well, I tried the trigger and it passed the syntax check phase - (actually failed because I don't have your tables)
What is in the 293'rd character position of your CREATE statement? The text you have below implies it is a space, is it?
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au [...]
719 26 10_RACF - DB213_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Wed, 8 Mar 2000 14:37:12 +0530545_us-ascii hi !!
I am facing a strange problem . Well we are at OS390 V2.6 and DB2 V5.1 We have applied the Maintenance tapes till 1999-09 . There is a PTF UW62861 of MVS which makes changes to the IRR@XACS member in the SYS1.SAMPLIB . This updates the member to introduce new RACF classed for stored procedures , triggers etc for DB2 V 6 . Now after applying this patch the member does not assemble. It give RC = 8 telling some symbols not found. like CHKEXECF . This symbol was not found during assembly. Has anyone faced this error [...]
746 16 43_Re: DB2 for MVS and Command Level Interface17_Giorgio De Nunzio25_giorgio.denunzio@UNILE.IT30_Wed, 8 Mar 2000 12:15:23 +0100322_us-ascii Thanks to Wayne and Alessandro for your kind reply!
Ciao Giorgio
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
763 89 25_Re: DB2 Hangs at Shutdown9_Jim Leask27_jim.leask@RS-COMPONENTS.COM30_Wed, 8 Mar 2000 11:25:57 -0000656_ISO-8859-1 Add one more, it takes 30 odd seconds normally.
However, we shut down all CICS regions and no batch jobs are running.
-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:RD8246@MOMAIL.SBC.COM] Sent: 07 March 2000 19:10 To: DB2-L@RYCI.COM Subject: Re: DB2 Hangs at Shutdown
Hi Max, You've got a great idea; there are about 2300? people on db2-l, let's see how many can stop DB2 with -STOP DB2 (defaulted MODE(QUIESCE)). You're #1. Best Regards, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or [...]
853 87 25_Re: DB2 Hangs at Shutdown0_19_mike.holmans@BT.COM30_Wed, 8 Mar 2000 12:09:14 -0000312_- Make that 4.
Most of our DB2 production systems are up from one IPL to the next, so we don't do a lot of -STOP DB2, but when we are closing pre-IPL or for a maintenance slot, that's the command we use. Occasionally it takes a bit of time, but that's because there's an aborted thread rolling back. [...]
941 69 25_DB2/VSE vs UDB for OS/39012_Jacob Ganzel19_jacobg@SEMECH.CO.IL30_Wed, 8 Mar 2000 14:59:36 +0200418_x-user-defined Hello. I am considering migrating from VSE to OS/390. I need a list of advantages of UDB for OS/390 V6 comparing to DB2 for VSE V6. Thanks for your help. Jacob. --------------------------------------------------------------- Jacob Ganzel Database Products Mgr. SEMECH Software Marketing Ltd.
3b Netanyahu St., OR YEHUDA, ISRAEL Email: jacobg@semech.co.il Tel.: +972-3-5333144 Fax.: +972-3-5333132
1011 121 25_Re: DB2 Hangs at Shutdown12_Guru, Bala X17_bala.guru@EDS.COM30_Wed, 8 Mar 2000 07:11:49 -0600522_- SIGNOFF DB2-L
-----Original Message----- From: mike.holmans@BT.COM [SMTP:mike.holmans@BT.COM] Sent: Wednesday, March 08, 2000 5:39 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Hangs at Shutdown
Make that 4.
Most of our DB2 production systems are up from one IPL to the next, so we don't do a lot of -STOP DB2, but when we are closing pre-IPL or for a maintenance slot, that's the command we use. Occasionally it takes a bit of time, but that's because there's an aborted thread rolling back. [...]
1133 42 36_Re: Connecting Two DB2 V5 Via TCP/IP10_Robert Ord21_robertord@HOTMAIL.COM28_Wed, 8 Mar 2000 14:15:20 GMT478_- I've just gone through the pain you are experiencing. Check the DB2 log to ensure DDF and TCPIP are available. There should be similar messages to the ones below for both subsystems;
+DSND DDF IS STARTING +DSND DSNLILNR TCP/IP SERVICES AVAILABLE FOR DOMAIN MFDEVP AND PORT 401 +DSND DSNLIRSY TCP/IP SERVICES AVAILABLE FOR DOMAIN MFDEVP AND PORT 421 +DSND DDF START COMPLETE LOCATION DB2DSND LU GBARG00.DDFDSND GENERICLU -NONE DOMAIN MFDEVP TCPPORT 401 RESPORT 421 [...]
1176 17 32_Deleting old DB2 log files on NT12_Martin, Paul22_Paul.Martin@ECOLAB.COM30_Wed, 8 Mar 2000 08:52:56 -0600537_iso-8859-1 Does anyone know how I can determine when the NT active logs are obsolete? or Does anyone have a script/program (REXX) that we could use to delete all of the old/obsolete logs files (I hate to reinvent the wheel)?
I searched the LISTSERV archives and couldn't find any scripts.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
1194 101 28_DPROPR Capture Performance ?11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Wed, 8 Mar 2000 09:54:34 -0500529_iso-8859-1 Is high CPU use the norm for this product ?
The CPU it uses is very high and causes the DB2 group to be the focus whenever there is a performance bottle neck. We are trying to keep up with IBM and have put on maintenance at regular intervals. It has gotten some better CPU wise and is now more reliable but still uses about twice the CPU of the entire DB2 subsystem that we are accessing. It does not seem to be a problem with the interfaces to DB2 but rather path length in the Capture's internal code. I [...]
1296 101 21_Re: Trigger Defintion18_Martinson, Barbara25_Barbara_Martinson@BMC.COM30_Wed, 8 Mar 2000 09:08:31 -0600653_ISO-8859-1 Sireesh, Maybe this will fix your problem. Read the APAR ABSTRACT: : MISCELLANEOUS UPDATES TO INSTALL AND SAMPLES FOR DB2 FOR OS/390 VERSION 6 (I included part of it below) This PTF implements the following changes in Install and Samples parts for DB2 for OS/390 Server Version 6: (1) Adds support in DSNTEP2 (DB2 sample dynamic SQL processor) for handling "functional comments" (--#SET) in the SQL input stream (SYSIN). DSNTEP2 recognizes the following functional comments: --#SET TERMINATOR x where 'x' specifies a one-byte character DSNTEP2 should use to locate the end of subsequent SQL statements. 'x' may be any character [...]
1398 73 30_Re: Start procedure automation14_Julian Stuhler27_julian.stuhler@TRITON.CO.UK30_Wed, 8 Mar 2000 07:24:37 -0800358_- Hi Gina.
In the development environment, many people get around this issue by setting the STORMXAB DSNZPARM parameter to a high value (this parm tells DB2 how many times to allow an SP to fail before putting it in stopped status). It can go as high as 255, which should give you plenty of time to iron out the bugs when developing a procedure. [...]
1472 138 24_Re: LOG YES on Utilities16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Wed, 8 Mar 2000 07:23:52 -0800367_iso-8859-1 Regarding Image Copies, since a REORG LOG YES effectively logs the entire tablespace, wouldn't that become a recovery starting point, making an Image Copy unnecessary? Apart from the question of whether REORG LOG YES is a good idea, if it's being done on a regular basis, wouldn't an Image Copy be redundant, since you've got a full image in the log? [...]
1611 149 32_Re: DPROPR Capture Performance ?13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Wed, 8 Mar 2000 21:04:34 +0530495_us-ascii What the rate of change of data in the databases which are selected for capture. Actually any capture job will be taking a fair amount of CPU. Ya of course the only thing is that is some other job requires CPU it should give it depending on the priority. Actually its always best to utilize the CPU max , but the contention should not occur. I hope thats not the case in your shop ?? or is it ?? by the way , how do u find the dProp ?? What is the scenario like os390 to NT or ??? [...]
1761 158 24_Re: LOG YES on Utilities0_23_Mike_Levine@TEKHELP.NET30_Wed, 8 Mar 2000 11:01:20 -0500372_us-ascii Hi Tony,
Yes, with REORG LOG YES, an after REORG image copy is not necessary. However, recovering a tablespace before the next full image copy is taken would take longer because DB2 would have to start with the before REORG image copy and read through the log. We do not take after REORG (LOG YES) image copies because there is no data loss exposure. [...]
1920 54 25_Re: DB2 Hangs at Shutdown17_Hynes, Kenneth J.17_khynes@TRIGON.COM30_Wed, 8 Mar 2000 11:27:26 -0500593_iso-8859-1 I'll have to add my 2-cents worth on the topic of "Shutdown". Add one more to the I can and do shutdown normally the majority of the time.
The DB2 MODE(QUIESCE) is designed to allow tasks to complete normally. IF DB2 is failing to come down then you have a long-running Batch Job, an active transaction, or a connected Gateway / Distributed Thread holding a resource (AKA "lock"). We have been shutting DB2 down with MODE(QUIESCE) for years with only an occasional issue. If you are having a consistent problem then a Display Thread(*) prior to DB2 shutdown should give [...]
1975 21 35_Changing OS/390 DB2 INSTALLl SYSADM9_Chu, Pius14_ChuP@CONED.COM30_Wed, 8 Mar 2000 11:37:17 -0500345_- I need to change the current INSTALL SYSADM from ID "AAAA" to "BBBB" and give AAAA the plain SYSADM authority. Just updating the DSNZPARM and recycle DB2. I did this test about 6 or 7 years ago and I did not see any issues. All the cascade grant from AAAA still in place and work without any problems. Can any one see any issues, thanks. [...]
1997 91 18_DB2 DBA AIX Needed15_Lori Hanslmaier17_lorih@NCSJOBS.COM30_Wed, 8 Mar 2000 09:31:30 -0800498_iso-8859-1 DB2 DBA Opportunity!!!
Location: Columbus, OH
Skills required: DB2 DBA AIX/RS6000. Experience with Crystal Reports, Seagate Information, JAVA based applications or Websphere would be a plus. Candidate must possess proven skill and ability to learn. Client is looking for a junior person and invest in their training.
Perm target salary: $65-85K DOE
If you may be interested in this position or know someone who is please send resume to lorih@ncsjobs.com [...]
2089 53 43_DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL15_Darius Parvaneh26_d.parvaneh@GROUPE-ANDRE.FR30_Wed, 8 Mar 2000 18:56:22 +0100515_us-ascii Hello,
We use DB2 V5.1 on OS/390 V2R6. I have tried to write a stored procedures in COBOL (5668-958 IBM VS COBOL II Release 4.0). I get systematically an SQLCODE = -471 with reason code 00E79006. This means that the load module is not "compatible with Language Environment preinitialization environment".
Can someone give me a help for this problem. Does this means that we haven't the correct COBOL or LE/370 version ? Is there any specific compilation for Cobol Stored Procedures ? Regards,
2143 43 39_Re: Changing OS/390 DB2 INSTALLl SYSADM0_23_Mike_Levine@TEKHELP.NET30_Wed, 8 Mar 2000 13:15:05 -0500374_us-ascii Hi Pius,
Yes, this is the correct procedure. BTW...rather than granting SYSADM directly to a userid, consider setting up a security group with SYSADM granted to it and then add (connect) the users to that group. This way you will not have the problem of cascading revoke when removing SYSADM in the future (you would just remove the id from the group). [...]
2187 74 47_Re: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL0_24_lightsey@ITS.STATE.MS.US30_Wed, 8 Mar 2000 12:30:25 -0600737_us-ascii
Yes - you must use Cobol for MVS or Cobol for OS/390. Older versions just won't work.
Darius Parvaneh cc: Sent by: DB2 Data Subject: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL Base Discussion List
03/08/2000 11:56 AM Please respond to DB2 Data Base Discussion List
Hello,
We use DB2 V5.1 on OS/390 V2R6. I have tried to write a stored procedures in COBOL (5668-958 IBM VS COBOL II Release 4.0). I get systematically an SQLCODE = -471 with reason code 00E79006. This means that the load module is not "compatible with Language Environment preinitialization [...]
2262 102 47_Re: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL16_Michael McCarthy18_mmccarthy@DTCC.COM30_Wed, 8 Mar 2000 13:55:08 -0500411_us-ascii Darius,
Another problem might be that you are not including DSNALI in the link-edit step. This is the language runtime module you will need. If you are using RRS, you will need to link-edit with DSNRLI and have a WLM established Stored Procedure Address space. hope this helps,
-Mike.
lightsey@ITS.STATE.MS.US on 03/08/2000 01:30:25 PM [...]
2365 51 18_Re: Visual Explain12_Martin Wolff31_martin_wolff@GLOBALCROSSING.COM30_Wed, 8 Mar 2000 14:19:08 -0600608_- I too am having exactly the same problem. There seems to be a common mistake here. Bob or Danny, if you finally got it working, I would appreciate knowing how you did it.
Thanks,
Martin Wolff, Global Crossing.
>I have also run into the exact same problem and would be interested in any resolutions anyone could provide as well. > > >Danny Davis >Systems Analyst >Univerity Health Systems >dtdavis@pcmh.com > >>>> BOB JEANDRON 01/05/00 01:12PM >>> >We are just getting started with DB2 Connect. We are going from an NT to an >AIX UNIX system then on to DB2 [...]
2417 74 18_Re: Visual Explain10_Park, Stan17_SPark@STATE.NM.US30_Wed, 8 Mar 2000 13:53:20 -0700472_iso-8859-1 I was able to use Visual Explain just yesterday with the Sample database on UDB V6.1 Win NT 64 Meg. Both Connect and UDB are installed on my workstation. (Last week I doubled Virtual Memory in Performance System Properties. May or may not be related)
Stan State of NM
-----Original Message----- From: Martin Wolff [mailto:martin_wolff@GLOBALCROSSING.COM] Sent: Wednesday, March 08, 2000 1:19 PM To: DB2-L@RYCI.COM Subject: Re: Visual Explain [...]
2492 85 18_Re: Visual Explain12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 8 Mar 2000 12:54:34 -0800551_us-ascii How are you connecting? I'm using it extensively with no problems of that sort. I connect directly from my PC (win 95) to DB2 using DB2 Connect PE (currently 6.1 but 5.2 works just as well).
--- Martin Wolff wrote: > I too am having exactly the same problem. There > seems to be a common > mistake here. Bob or Danny, if you finally got it > working, I would > appreciate knowing how you did it. > > Thanks, > > Martin Wolff, Global Crossing. > > >I have also run into the exact same problem and [...]
2578 24 41_Re: Cursor name on DECLARE/OPEN and FETCH12_Raymond Bell20_raymondb@NZ1.IBM.COM30_Thu, 9 Mar 2000 09:54:22 +1300443_us-ascii Hi Vitor,
I guess you could try preparing the cursor declare/open/fetch statements dynamically. I've never done it, but I think you just construct the cursor statement (my magic, I guess) giving the cursor name you want, prepare the statement and execute it. Don't know how that'll go down at Amadeus. Apart from that, I'm pretty sure you can't have a host variable as the cursor name. Can't do it in V6 either. Maybe V7? [...]
2603 36 61_Re: Dynamic File Allocation from a COBOL DB2 Stored Procedure12_Jim Harrison12_jimh@QIS.NET30_Wed, 8 Mar 2000 16:11:36 -0500649_us-ascii To all who inquired, the necessary files are zipped up and now available on the document site at:
http://jupiter.ryci.com/archives/db2-l-documents.html
At 06:24 AM 3/7/00 -0600, you wrote: >Jim, > I'd be interested. Might as well post them to the document site at >DB2-L. >Thank You, > >-----Original Message----- >From: Jim Harrison [mailto:jimh@QIS.NET] >Sent: Monday, March 06, 2000 4:32 PM >To: DB2-L@RYCI.COM >Subject: Re: Dynamic File Allocation from a COBOL DB2 Stored Procedure > >Wow - talk about timing. I just put together a SP to do this in our shop >as a debugging aid. It calls an assembler routine I wrote [...]
2640 109 18_Re: Visual Explain12_Martin Wolff31_martin_wolff@GLOBALCROSSING.COM30_Wed, 8 Mar 2000 15:03:24 -0600507_- My configuration looks similar to yours. I have win95 and DB2 Connect 5.2 . I have four DB2 system registered and they all checkout when I use the test button.
The Visual Explain download gave me a ZIP'd file which I unZIP'd to a single folder. There wasn't any kind of install program so all the DLLs are all still in the unZIP'd folder (not windows/system or anywhere else). I don't see anything in Visual Explain that connects me to DB2 Connect itself - I don't know if that is my problem. [...]
2750 133 18_Re: Visual Explain11_Danny Davis16_DTDAVIS@PCMH.COM30_Wed, 8 Mar 2000 16:19:43 -0500435_US-ASCII For what it's worth, I'm also having the exact same problem. We are using DB2 Connect EE and Windows NT.
Danny Davis Systems Analyst Univerity Health Systems dtdavis@pcmh.com
>>> Martin Wolff 03/08/00 04:03PM >>> My configuration looks similar to yours. I have win95 and DB2 Connect 5.2 . I have four DB2 system registered and they all checkout when I use the test button. [...]
2884 159 18_Re: Visual Explain12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 8 Mar 2000 13:21:43 -0800485_us-ascii From the main Visual Explain menu, you should have a tab called subsystem. If you pull that tab down, there will be an entry called ADD. You need to click on ADD and then fill out the panel that comes up with the nickname and Alias, id, etc. This puts an entry into DB2 Connect that basically allows you to use DB2 connect and the "Connect" tab on the Subsystem panel. WIthout doing this, you cannot CONNECT to anything. It just won't make the connection to DB2 Connect. [...]
3044 27 31_Extenders and DB2 UDB for OS39014_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Wed, 8 Mar 2000 16:59:39 -0500461_iso-8859-1 We're at a point where we think we can greatly benefit from using DB2 Extenders (specifically Text Extenders) for DB2 UDB for OS/390? I've been doing some reading and so far I've been able to find excellent literature (especially Introducing Object-Relational OS/390 by R. Yevich & Susan Lawson in DB2 Mag) on the features and capabilities of the product. However, I cannot find any info on how to enable this feature in DB2 UDB (V6) for OS390. [...]
3072 27 10_DB2 UDB/NT15_Michael Bancale21_mbancale@TXFB-INS.COM30_Wed, 8 Mar 2000 16:17:23 -0600282_iso-8859-1 I give up... I have been searching the IBM site for the machine requirements for a DB2 UDB 6.1 on an NT workstation and I can't seem to find it. Would someone please let me know what type of base Windows NT machine I should have for the personal edition of DB2 UDB. [...]
3100 50 37_[Fwd: syntax error on create trigger]15_Benjamin Reches24_Benjamin.Reches@MSDW.COM30_Wed, 8 Mar 2000 18:14:32 -0500759_us-ascii -------- Original Message -------- Subject: syntax error on create trigger Date: Wed, 08 Mar 2000 17:56:43 -0500 From: Benjamin Reches Reply-To: Benjamin.Reches@msdw.com Organization: Morgan Stanley Dean Witter & Co. To: DB2-L@RYCI.COM, Benjamin Reches
Could somebody explain why I am getting the following syntax error???
PAGE 1 ***INPUT STATEMENT: CREATE TRIGGER M80119.SBTRIGR AFTER INSERT ON M80119.SBDEALS REFERENCING NEW_TABLE AS NUTBL FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC CALL POPULATE_SBDEALS(TABLE NUTBL); SQLERROR ON CREATE COMMAND, PREPARE FUNCTION RESULT OF SQL STATEMENT: DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "NUTBL)". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ; [...]
3151 87 41_Re: [Fwd: syntax error on create trigger]14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 9 Mar 2000 11:08:55 +1000446_- Benjamin
You left the comma out between 'table' and 'nutbl'. Or something like that.
Also, you have left off the END. As has been pointed out in replies to Sireesh, in a CREATE TRIGGER statement a semi-colon is used to terminate the triggered statements, and so cannot be used to terminate the CREATE statement itself - you have to use another character. Using the the V6 version of DSNTEP2, you have to have something like: [...]
3239 49 14_Re: RACF - DB214_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 9 Mar 2000 11:15:40 +1000685_- Vishy,
OW42534 seems to be what you need.
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au
-----Original Message----- From: Viswanathan N [mailto:Viswanathan.N@POWAIMAIL.LTITL.COM] Sent: Wednesday, March 08, 2000 7:07 PM To: DB2-L@RYCI.COM Subject: RACF - DB2
hi !!
I am facing a strange problem . Well we are at OS390 V2.6 and DB2 V5.1 We have applied the Maintenance tapes till 1999-09 . There is a PTF UW62861 of MVS which makes changes to the IRR@XACS member in the SYS1.SAMPLIB . This updates the member to introduce new RACF classed for stored procedures , [...]
3289 168 41_Re: [Fwd: syntax error on create trigger]14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 9 Mar 2000 11:48:35 +1000631_iso-8859-1 Benjamin,
1) are you certain that you are using the V6 version of DSNTEP2? Try
--#SET TERMINATOR ##
if you don't get *** ERROR ************************************************************** * DSNTEP2 halted due to a functional comment (--#SET) statement error: * Invalid value, "##", specified for TERMINATOR. ************************************************************************ then you're not.
2) try CALL (POPULATE_SBDEALS(TABLE NUTBL)); (I got this syntax from the Appl Prog and SQL Guide Sect 3.5.3.1 "Passing transition tables to user-defined functions and stored procedures") [...]
3458 182 41_Re: [Fwd: syntax error on create trigger]15_Benjamin Reches24_Benjamin.Reches@MSDW.COM30_Wed, 8 Mar 2000 20:07:50 -0500288_us-ascii James,
Yup! Tried ## and it didn't even barf!
Now i gotta find where the V6 stuff resides. Unfortunately the db2 dba man is gone for the day. So I'll pick up from here tomorrow. Thanks for your help so far and I will let you know tomorrow what the outcome is. [...]
3641 180 24_Re: LOG YES on Utilities29_Sand, Sverre (FUNDI at Alcoa)24_Sverre.Sand@ALCOA.COM.AU30_Thu, 9 Mar 2000 09:27:50 +0800331_iso-8859-1 An image copy would never be redundant since you need an image copy to start a recovery. The only objects I'm aware of that not need an image copy (only needs the log) for recovery is the directory tables and the SYSCOPY tablespace. Make sure you keep an image copy of every tablespace and all the logs back to it. [...]
3822 63 26_Links about DB2 for OS/3906_DB2usa18_db2usa@HOTMAIL.COM30_Wed, 8 Mar 2000 21:37:11 -0600583_ISO-8859-1 Hi,
Here is a non-profit website about DB2 for OS/390 (IBM mainframes):
- English : http://www.multimania.com/db2usa/eliendb2.htm - Français : http://www.multimania.com/db2usa/liendb2.htm
Last update on Monday, March 6th 2000 =====================================
- Links about DB2 for OS/390: http://www.multimania.com/db2usa/eliendb2.htm IBM added new functions in DB2 version 6 through APARs.
- Technical papers: http://www.multimania.com/db2usa/earticle.htm Raul Gonzalez updated his website: DB2 Questions and Answers. [...]
3886 100 30_Determine DMS & SMS tablespace7_revadee18_revadeev@SCB.CO.TH30_Thu, 9 Mar 2000 11:15:34 +0700635_windows-874 Dear ,all
I have question about creating tablespace. My system is about keeping licence of customers each branch (~500 branches). In table have blob colomn size not exceed 10kb. Mostly of transactions have select command to show the licence. Now, programmer was created default 3 tablespaces is DMS. (DB2V.6.1) And I read the admin book ,commonly said that DMS tablespaces is good performance than SMS. And shouldn't create all of tables in userspace. Should it create 1 tablespace / 1 table?. Including should I seperate tablespace of index too.? Anyway this system is on NT, so although create DMS tablespace [...]
3987 38 9_COPY JOBS18_ARNOLD ADLKIRCHNER27_arnold-adlkirchner@HOME.COM30_Wed, 8 Mar 2000 21:43:31 -0800617_us-ascii Some time ago I inherited a number of DB2 image copy jobs. Each job copies all tablespaces in a given database and all tablespace sets are fully contained within the given database. The image copy jobs consist of four steps.
Step 1: Start all tablespaces read only Step 2: Copy each tablespace. One COPY TABLESPACE statement per tablespace is used resulting in different STARTRBA's for each tablespace. SHRLEVEL REFERENCE is specified. Step 3: QUIESCE all tablespaces. One QUIESCE statement for all tablespaces resulting in same rba for all tablespaces. Step 4: Start all tablespaces read write. [...]
4026 154 14_Re: DB2 UDB/NT11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU30_Thu, 9 Mar 2000 16:59:13 +1100318_- Hi Michael, I run it on a 32M Pentium, and it runs. Everything is slow on that machine as it pages a lot. I don't think I started Control Centre though.
On a 64M machine, and starting Control Centre, it runs slow as it pages a lot.
On a 256M Pentium3, it runs just fine, including Control Centre. [...]
4181 181 27_Re: DRDA - Three part names14_Julian Stuhler27_julian.stuhler@TRITON.CO.UK30_Wed, 8 Mar 2000 22:08:33 -0800634_- Craig,
Prior to V6, the protocol used for distributed access was implicitly specified through the way in which you connected to the remote database (use of 3 part name in the SQL implied DB2 private protocol, explicit use of CONNECT meant DRDA was used).
IBM have been strongly hinting that they will be removing support for private protocol very soon. V6 provides the means to use DRDA protocols in conjunction with 3-part names, thereby allowing customers currently using private protocol to convert to DRDA with no program changes being required. DRDA also offers a number of advantages over private protocols [...]
4363 142 47_Re: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Thu, 9 Mar 2000 13:00:09 +0530452_us-ascii
I think after you change the load module . refresh the stored procedure with start procedure command . I think it will work after that . Even I had got similar problem. vishy
Michael McCarthy on 03/09/2000 12:25:08 AM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM
cc: (bcc: Viswanathan N/LTITL) [...]
4506 51 47_Re: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL4_IBM116_IBM1@UBIL.COM.TR30_Thu, 9 Mar 2000 09:37:47 +0200435_- Hello Darius,
the reply is just that. IBM stopped providing service for COBOL II back in 1998.
The new COBOL for OS/390 is only a compiler, it does not include a run-time environment, as COBOL II did before. You should therefore upgrade to "IBM Language Environment for MVS and VM" and re-compile your stored procedures with the new compiler (please note that all the above also applies to PL/I -> PL/I OS/^)=!!). [...]
4558 109 47_Re: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL4_IBM116_IBM1@UBIL.COM.TR30_Thu, 9 Mar 2000 09:39:33 +0200758_- Mike,
please note that COBOL OS/390 does NOT include a run-time environment. This is handled by/included in LE/MVS!!
Aurora Dell'Anno Systems Engineer IBM Software Support e-mail ibm1@ubil.com.tr aurora.dellanno@sistinf.it
> -----Original Message----- > From: Michael McCarthy [SMTP:mmccarthy@DTCC.COM] > Sent: Wednesday, March 08, 2000 8:55 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL > > Mesajiniz Virus taramasindan gecirilmis ve temiz bulunmustur > iyi calismalar, > Universal Bilgisayar Hizmetleri A.S. > > File: vcard.vcf (418 bytes) > Encoding: Base64 > Result: Clean. > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > Darius, > > Another problem might be that you are [...]
4668 137 47_Re: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL12_Guru, Bala X17_bala.guru@EDS.COM30_Thu, 9 Mar 2000 02:13:01 -0600437_- Signoff db2-l
-----Original Message----- From: Viswanathan N [SMTP:Viswanathan.N@POWAIMAIL.LTITL.COM] Sent: Thursday, March 09, 2000 1:00 PM To: DB2-L@RYCI.COM Subject: Re: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL
I think after you change the load module . refresh the stored procedure with start procedure command . I think it will work after that . Even I had got similar problem. vishy [...]
4806 21 40_Monitiring the growth of db2 tablespaces19_Briggs, N. - Neil -25_N.Briggs@CANON-EUROPA.COM30_Thu, 9 Mar 2000 09:38:18 +0100530_iso-8859-1 Dear All,
I have been asked to provide information as to how the tables are growing in our DB2 system. Please can anyone send me what practices are followed are their sites to give me some ideas.
We have DB2 version 5 running on OS/390
Kindest regards Neil
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
4828 149 47_Re: DB2 OS/390 V5.1 STORED PROCEDURES AND COBOL15_Darius Parvaneh26_d.parvaneh@GROUPE-ANDRE.FR30_Thu, 9 Mar 2000 09:55:53 +0100660_iso-8859-1 Thanks for all your prompt response. So the problem is certainly wih the COBOL version we have, because my link-edit include DSNALI and have inserted my proc in sysibm.sysprocedures and done an stop/start procedures.
Best Regards, Darius.
IBM1 a écrit: > > Mike, > > please note that COBOL OS/390 does NOT include a run-time environment. This > is handled by/included in LE/MVS!! > > Aurora Dell'Anno > Systems Engineer > IBM Software Support > e-mail ibm1@ubil.com.tr > aurora.dellanno@sistinf.it > > > -----Original Message----- > > From: Michael McCarthy [SMTP:mmccarthy@DTCC.COM] > > Sent: Wednesday, March 08, 2000 8:55 PM > [...]
4978 111 44_Re: Monitiring the growth of db2 tablespaces0_18_mebert@AMADEUS.NET30_Thu, 9 Mar 2000 09:57:57 +0100616_us-ascii At our site, we're mainly using BMC DASD Manager/BMCSTATS for this. BMCSTATS runs each night as a batch job and updates the BMC Historical Database. Using DASD Mgr, you can do a graphical display on individual TS/Tables/Indexes and plot lots of info over time: used/allocated space, space required if reorged, extents, clusterratio,... I also run a SAS program that reads this historical database and produces a summary by several dimensions, e.g. by application (first 3 characters of the DB name), by TS/IX, by the percentage size change if reorged, and by total. I keep daily data for 2 months, and [...]
5090 44 25_Re: DB2 Hangs at Shutdown14_Massimo Scarpa16_mscarpa@CESVE.IT30_Thu, 9 Mar 2000 09:59:40 +0100338_us-ascii Oh, I'm very lucky man !!! I'm #1. Finally I am the first in something....
Last night I stopped DB2 'cause I had to modify some DSNZPARM (DDF parms). This time I waited
for 30+ mins, but the story was the same:
In SDSF system log I saw the message that DB2 was stopped normally, but in SDSF all address [...]
5135 48 24_FW: ODBC and Access 200014_Richard Bourke25_Richard.Bourke@MERANT.COM30_Thu, 9 Mar 2000 10:39:01 +0100618_- Craig, I agree about downloading the data access components. Basically, MDAC is 21.x, shipped with various MS applications including Office2000, is buggy. It's however a vital component of pretty much all MS products. ADO a pile of junk ? Well, you can work very productively with non-secure data with it - many shops actually do allow their programmers update/insert/delete/select on the underlying db2 objects. If this isn't in line with your site security standards, then from ms applications (via ADO or ODBC) you can either: 1. Use an odbc product that supports static sql, such as our connect premium for [...]
5184 64 44_Re: Monitoring the growth of db2 tablespaces12_Claude Birtz24_Claude.Birtz@CIE.ETAT.LU30_Thu, 9 Mar 2000 10:58:27 +0100347_us-ascii We execute Stospace each night and Runstats (with SAMPLE option) each sunday. I've created the following table CREATE TABLE INFO ("NAME" CHAR(18 ) NOT NULL ,TSNAME CHAR(8 ) NOT NULL ,CARD FLOAT NOT NULL ,SPACE INTEGER NOT NULL ,STATSTIME TIMESTAMP NOT NULL )
which gets filled after each execution of RUNSTATS by this select: [...]
5249 95 44_Re: Monitoring the growth of db2 tablespaces0_19_mike.holmans@BT.COM30_Thu, 9 Mar 2000 10:16:23 -0000400_- Now there's interesting. I haven't run STOSPACE in years because it seemed pretty useless. As far as I recall, it was useless because we were using partitioned tablespaces, our stogroups are mostly defined as (primary_vol, overflow_vol), and each partition would be in a different stogroup. The figures which STOSPACE came up with in that scenario were nonsensical, at least as I remember it. [...]
5345 84 44_Re: Monitiring the growth of db2 tablespaces13_Adrian Savory24_adrian.savory@ZURICH.COM30_Thu, 9 Mar 2000 10:25:00 +0000498_us-ascii Neil,
If you're SMS-managed you can use the DCOLLECT utility to generate a dataset report which shows allocated space, number of extents, secondary extent size, etc. This runs very quickly (100,000 datasets in 3 minutes). Here's some sample JCL:
//STEP0010 EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=X //OUTDS DD DSN=&&DATAIN, // DISP=(NEW,PASS), // SPACE=(CYL,(30,30)), // DCB=(DSORG=PS,RECFM=VB,LRECL=4096) //SYSIN DD * DCOLLECT - OFILE(OUTDS) - STORAGEGROUP(GDVSYS,GDVDB2) [...]
5430 53 35_Re: Extenders and DB2 UDB for OS3900_20_gabriele@NZ1.IBM.COM31_Fri, 10 Mar 2000 00:19:19 +1300657_us-ascii Manas,
DB2 Extenders IAV (Image, Audio and Video) and Text are delivered under OS/390 Unix Services.
In order to get the Text Extender going you will need first to get the Text Search Engine for OS/390 installed and configured. Most of the information for the installation of both, DB2 Text Extender and Text Search Engine for OS/390, come in a README file delivered with the appropriate HFS file under the install directory. The manuals (OS/390 Text Search: Installation and Administration for the Text Search Engine SH12-6387-00 and DB2 UDB for OS/390 Text Extender: Administration and Programming SC26-9651-00) don't say much [...]
5484 65 13_Re: COPY JOBS15_Mallett, Steven38_Steven.Mallett@CORPMAIL.TELSTRA.COM.AU30_Thu, 9 Mar 2000 22:26:18 +1100444_- Arnold,
We're a small shop but we also use the QUIESCE immediately after the START . . . (RO) command to ensure we actually do have the Tablespaces as exclusive Read Only. If we only issue the START ... (R0) command by itself any active Units of Work (including Update tasks) will still proceed, even though the Tablespaces are displayed as Status = RO. If there are active update tasks, the QUIESCE fails and we try again later. [...]
5550 80 14_timeout itself21_Mykhaylo Krasnyanskyy30_krasnyan@SWH600.LANGEN.BULL.DE30_Thu, 9 Mar 2000 12:48:58 +0100692_us-ascii Hello List,
we have observed a strange phenomenon (DB2 V5.1 for OS/390). A job produced a timeout with itself. Different jobs and different tables (in partition tablespace) can be involved in this case. The situation doesn't reproduce itself by the next restart.
Could somebody explain this?
Some Outputs:
12.55.10 STC13220 DSNT376I -DB2W PLAN=P096H002 WITH CORRELATION-ID=B16UMS CONNECTION-ID=BATCH LUW-ID=DB2W.A8SWDB2W.B3B5F3E93506=8163 THREAD-INFO=ZE1096H:*:*:* IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=P096H002 WITH CORRELATION-ID=B16UMS CONNECTION-ID=BATCH LUW-ID=DB2W.A8SWDB2W.B3B5F3E93506=8151 THREAD-INFO=ZE1096H:*:*:* ON MEMBER DB2W [...]
5631 70 18_Re: Visual Explain12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM30_Thu, 9 Mar 2000 07:59:50 -0500507_us-ascii I'll keep the emails public for the moment as there is at least one other interested party.
I had already done all this, the database alias was the name defined at the client configuration assistant and double checked by using the LIST DB DIRECTORY command on the command line processor.
Did you install Visual Explain from the ZIP'd download or from a CD-ROM? If from the CDROM, did the SETUP.EXE ask for any information and if so, what did it do other than UNZIP the software? [...]
5702 80 13_Re: COPY JOBS12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM30_Thu, 9 Mar 2000 08:20:18 -0500362_us-ascii In our pre 24x7 days, steve's way was how we did it.
The other thing to remember is that while you must have SHRLEVEL REFERENCE, you will be putting the tablespaces into UTRO and update jobs will fail. The only easy option if you want COPYs and update jobs running is to make the change to SHRLEVEL CHANGE but you may not be ready for that. [...]
5783 68 25_Re: DB2 Hangs at Shutdown19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 9 Mar 2000 07:23:45 -0600604_iso-8859-1 Max, Call "The Exorcist" or "Ghost Busters"
Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...]
5852 30 39_00E70005 with Apply os390 and udb HP_UX0_24_lightsey@ITS.STATE.MS.US30_Thu, 9 Mar 2000 07:23:57 -0600549_us-ascii We are getting the 00e70005 error when starting an Apply process on os390 ( DB2 v5, dpropr v5 - both current on maintenance) that is supposed to communicate with udb v6 on a hp_ux box. This originally happened a couple of months ago and IBM told us to get current on maintenance which we have done. End result is that the offset in the Apply module that gets the error has moved but the error still occurs. While I'm waiting for ibm to chew on this, is anyone else currently running apply with this configuration ? Or, has anyone else [...]
5883 83 25_Re: DB2 Hangs at Shutdown19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 9 Mar 2000 07:32:39 -0600599_iso-8859-1 Total is 6 so far . . . 4 of which qualified, in some way, their vote.
Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this [...]
5967 151 24_Re: LOG YES on Utilities19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 9 Mar 2000 07:41:14 -0600285_iso-8859-1 Hi Mike, Tony, In another view of this issue. Quite a few shops see speed to recover a major issue. Every minute down is lost revenue. Speed to recover is directly related to when the most recent image copy was taken and how many of the archive logs are still on DASD. [...]
6119 102 25_Re: DB2 Hangs at Shutdown12_craig patton21_prgpatton@HOTMAIL.COM28_Thu, 9 Mar 2000 05:42:28 PST595_- 1 more for -STO DB2, does it count if we check active threads first?? We currently have 8 SSIDs.
>From: "DAVIS, RICK (SBCSI)" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: DB2 Hangs at Shutdown >Date: Thu, 9 Mar 2000 07:32:39 -0600 > >Total is 6 so far . . . 4 of which qualified, in some way, their vote. > >Rick Davis >"This e-mail and any files transmitted with it are the property of SBC, >are confidential, and are intended solely for the use of the individual >or entity to whom this e-mail is addressed. [...]
6222 109 18_Re: Visual Explain12_Myron Miller22_myronwmiller@YAHOO.COM30_Thu, 9 Mar 2000 05:45:12 -0800309_us-ascii I downloaded it from the IBM Web site and Unzipped it. Then I execute DSNVE.EXE.
After you do the ADD do you get a line on the main panel with the name of the system that you added and a lightning bolt in the status field? If you do, do you highlight that line and then click on Connect? [...]
6332 38 25_Re: DB2 Hangs at Shutdown19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 9 Mar 2000 07:48:04 -0600592_iso-8859-1 Total is 7 so far . . . 5 of which qualified, in some way, their vote. Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this [...]
6371 64 35_Update Anywhere replication problem12_Michael Best12_mbest@GR.COM30_Thu, 9 Mar 2000 08:01:42 -0600468_- Hello all,
I'm having a problem with my Update Anywhere replication. I've searched the archives of this mailing list, but havent found any definitive answers. Any help would be greatly appreciated.
SHORT VERSION:
I am trying to set up Update Anywhere replication, and have been able to get data changed on the source server to replicate to the target, but have been unable to get data changed on the target to replicate back to the source. [...]
6436 46 44_Re: Monitiring the growth of db2 tablespaces13_John Arbogast16_jfarbo@YAHOO.COM30_Thu, 9 Mar 2000 06:17:42 -0800331_us-ascii
We use a tool from Candle called !DBDASD.
"Briggs, N. - Neil -" wrote: Dear All,
I have been asked to provide information as to how the tables are growing in our DB2 system. Please can anyone send me what practices are followed are their sites to give me some ideas. [...]
6483 36 25_Re: DB2 Hangs at Shutdown12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV30_Thu, 9 Mar 2000 09:28:28 -0500576_iso-8859-1 Most of the time, DB2 shuts down normally, but we do have sporadic cases where DB2 won't shut down. (We shutdown DB2 daily and IPL daily....and b4 any asks, no, I don't have control over the IPL schedule!) Even mode(force) won't bring DB2 down, so operations cancels DB2 from MVS. The problem appears to be related to threads that can't be terminated for some reason. We time out inactive threads after 30 minutes so it is unclear why a thread would remain connected to DB2 for longer than 30 minutes. We have seen CICS, distributed and TSO threads cause this [...]
6520 210 24_Re: LOG YES on Utilities16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Thu, 9 Mar 2000 06:36:13 -0800279_iso-8859-1 I ran an Image Copy and a recover - it used the Image Copy. I ran a REORG LOG YES and a recover - it used the DB2 logs. I ran a LOAD REPLACE LOG NO to make sure that I had no recovery point, then a REORG LOG YES, then a recover - it recovered using the DB2 logs. [...]
6731 95 25_Re: DB2 Hangs at Shutdown9_Birk, Tim17_BirkT@DIEBOLD.COM30_Thu, 9 Mar 2000 09:37:50 -0500395_iso-8859-1 All right, I guess I'll put in my vote. We take down DB2 every Sunday night for stand-alone backups. CICS is down and there's no batch. Occasionally we have to cancel a TSO user, but DB2 always comes down normally. We used to have a problem with DDF threads, but idle thread timeout took care of that. BTW, Mainview for DB2 remains up through the process and causes no problems. [...]
6827 43 44_Re: Monitoring the growth of db2 tablespaces11_Kwan, James18_James_Kwan@BMC.COM30_Thu, 9 Mar 2000 08:42:02 -0600357_ISO-8859-1 Neil
If you don't have a third party software, I would suggest you to write a rexx/clist to listc all db2 datasets and extract high used rba for each dataset. This will give you a rough figure of your data growth. If you need detailed information for each tablespace, I would suggest you to look for one of the third party solutions. [...]
6871 847 18_Re: Visual Explain19_Lisa Westcott-Dryer29_Lisa_Westcott-Dryer@AMWAY.COM30_Thu, 9 Mar 2000 09:43:18 -0500575_us-ascii
DSNVE.EXE (Wiondows 95 version)downloaded from the IBM web site for MVS comes with a tutorial. It really helped me get things set up and going. Then you add database subsystems under the menu option Subsystem Add. When you have added your subsystems your opening window looks something like this. Click on the lightning bolt to get and an and password screen to get connected. Remember, your backend connection (protocol) to DB2 should be the DB2 Connect product (I'm not sure of the supported release levels). This is as much as I know. I use it, I like it.
7719 123 18_Re: Visual Explain12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM30_Thu, 9 Mar 2000 09:47:28 -0500394_us-ascii Yes, I am doing all that. When I click on Connect, a user ID & password screen come up, I put my password in and a Visual Explain Status window appears with the message 'Connecting to Subsystem' and a cancel button. My pointer 'hourglasses' for about two seconds and then goes back to a pointer. At that point, Visual Explain locks up and can only be cleared with a CTRL-ALT-DEL. [...]
7843 30 23_Dynamic Statement Cache0_20_John_Lendman@FPL.COM30_Thu, 9 Mar 2000 09:51:09 -0500393_us-ascii I have a SAP table where I added a column in the middle of a row. The structure look OK in DB2. When this table was selected in an SAP program the new column would end up with trash in it.
Could this be caused by Dynamic Statement Cache, where the statement already exist in cache and SAP/DB2 did not PREPARE the statement, therefore it did not pick up the new structure? [...]
7874 159 18_Re: Visual Explain11_Kurt Rompot21_RompotK@SCHNEIDER.COM30_Thu, 9 Mar 2000 08:56:38 -0600510_us-ascii I had what sounds like a similar problem with Visual Explain at one time.
Make sure your PATH statement in your autoexec.bat isn't all hosed up. Repeated installs and deinstalls of the DB2 client software can produce something similar to this:
set PATH=%PATH%;%PATH%;%PATH%.... (rest of line snipped)
If your path statement gets too long, Visual Explain won't be able to find the DB2 client files to make the connection. Once I trimmed down my PATH , everything worked fine. [...]
8034 469 18_Re: Visual Explain19_Lisa Westcott-Dryer29_Lisa_Westcott-Dryer@AMWAY.COM30_Thu, 9 Mar 2000 10:03:34 -0500532_us-ascii
C:\VisualExp
ftp://ftp.software.ibm.com/software/os390/db2server/fixes/db2ve/winnt/ vewin95.zip
I've had a heck of a time trying to figure out what version release of DB2 connect we're using. Here's from the Help Product information in the Control Center. I believe we're at DB2 Connect 5020. (Embedded image moved to file: pic20432.pcx)
hope this helps.
Lisa
MARTIN WOLFF @RYCI.COM> on 03/09/2000 09:47:28 AM [...]
8504 254 24_Re: LOG YES on Utilities12_craig patton21_prgpatton@HOTMAIL.COM28_Thu, 9 Mar 2000 07:07:42 PST364_- Tony,
Just curious, but when doing the recover after the load LOG NO and the REORG LOG YES, where did DB2 START the recover?? Did it start with a copy? (I didn't see where you made one after the log no load) How did you specify the RECOVER parameters? Did you do a LOGONLY? Also, how long did your LOG recoveries take, compared with the IC recovery? [...]
8759 70 13_Re: COPY JOBS11_Kwan, James18_James_Kwan@BMC.COM30_Thu, 9 Mar 2000 09:13:19 -0600585_ISO-8859-1 Arnold,
I think starting TS RO is not just for quiesce, but also for copy with shrlevel reference. If you have an update job running, the copy might fail. With your current back strategy, none of the tablespaces will be available for update until all copies are completed. To improve the availability, either change to shrlevel change or only start the TS or group of logical related TS in RO mode just before the copy of that tablespace. For quiesce, you might or might not need to quiesce for all tablespaces. May be just for the logical groups of tablespaces [...]
8830 56 27_Re: Dynamic Statement Cache22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP30_Thu, 9 Mar 2000 14:16:47 +0000443_ISO-8859-1 John it sounds like you are barking up the right tree .. have you considered contacting IBM (Germany) to talk about it?
Do you want me to as I talk direct with the developers....?
Worried of England ...
Les
______________________________ Reply Separator _________________________________ Subject: Dynamic Statement Cache Author: John_Lendman@FPL.COM at Internet Date: 3/9/00 9:51 AM [...]
8887 79 35_Re: Extenders and DB2 UDB for OS39014_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Thu, 9 Mar 2000 10:22:24 -0500596_iso-8859-1 Gabriele:
Thank you very much. Thats exactly what I was looking for - gives me a good starting point.
Manas.
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > gabriele@NZ1.IBM.COM > Sent: Thursday, March 09, 2000 6:19 AM > To: DB2-L@RYCI.COM > Subject: Re: Extenders and DB2 UDB for OS390 > > > Manas, > > DB2 Extenders IAV (Image, Audio and Video) and Text are delivered under > OS/390 Unix Services. > > In order to get the Text Extender going you will need first to > get the Text > Search Engine for [...]
8967 19 58_Can Isolation level UR and updates be in the same package?12_Alan Gredell28_agredell@KEMPERINSURANCE.COM30_Thu, 9 Mar 2000 09:03:38 -0600595_- Hello, list. I've tried looking but haven't found a quick answer. Can I bind a package with ISOLATION LEVEL(UR) that contains update statements (outside of a cursor)? I've been assured by the application folks that the two simultaneous batch processes we're considering aren't interested in the same data, so a conflict shouldn't be possible or a problem. Therefore I hope to allow an extract process to SELECT rows from several tables WITH UR, then process an UPDATE on a single table, and only a single character of a single table, to show that the batch process has run. Any opinions? [...]
8987 33 62_Re: Can Isolation level UR and updates be in the same package?9_Rob Crane22_racrane@CONCENTRIC.NET30_Thu, 9 Mar 2000 08:52:10 -0700615_us-ascii I prefer to use the ISOLATION LEVEL(UR) at the statement level rather then at the package level(bind time). I would bind the package with ISOLATION(CS) and add WITH UR to each select statement.
-Rob
Alan Gredell wrote: > > Hello, list. I've tried looking but haven't found a quick answer. Can I > bind a package with ISOLATION LEVEL(UR) that contains update statements > (outside of a cursor)? I've been assured by the application folks that the > two simultaneous batch processes we're considering aren't interested in the > same data, so a conflict shouldn't be possible or a problem. [...]
9021 43 62_Re: Can Isolation level UR and updates be in the same package?12_craig patton21_prgpatton@HOTMAIL.COM28_Thu, 9 Mar 2000 07:42:05 PST676_- Alan,
Have you considered binding with ISOLATION(CS) and adding WITH UR at the end of the SELECT statement? This gives ONLY the SELECT the UR isolation.
HTH, Craig
>From: Alan Gredell >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Can Isolation level UR and updates be in the same package? >Date: Thu, 9 Mar 2000 09:03:38 -0600 > >Hello, list. I've tried looking but haven't found a quick answer. Can I >bind a package with ISOLATION LEVEL(UR) that contains update statements >(outside of a cursor)? I've been assured by the application folks that the >two simultaneous batch [...]
9065 175 18_Re: Visual Explain12_Myron Miller22_myronwmiller@YAHOO.COM30_Thu, 9 Mar 2000 07:48:45 -0800372_us-ascii I put it on C:\db2est . But I've used several different directories successfully. However, try to put it on the same drive as your DB2 Connect. I've had unknown random type problems when I've had it on different one. Not sure why.
Also, make sure the Path statement is ok in your AUTOEXEC.BAT. If it gets too long, you may lose access to DB2 Connect. [...]
9241 108 30_Re: Start procedure automation13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Thu, 9 Mar 2000 10:02:42 -0600573_iso-8859-1 I've never quite understood the reasons behind this one. Everywhere I see the "STORMXAB" parm, there's a recommendation to leave this set to the default (zero) for production systems. I don't see this as being very practical in a high-volume production environment. Yes, you want to know if there was an error, but do you really want to shut down your production application every time an error occurs? (Just because 1 out of 1,000 users happens to hit the right mix of data to cause an error, why shut down the 999 who are not having a problem?). It seems [...]
9350 15 62_Re: Can Isolation level UR and updates be in the same package?12_Alan Gredell28_agredell@KEMPERINSURANCE.COM30_Thu, 9 Mar 2000 09:56:24 -0600393_- Thanks, Craig and Rob, that probably will work well in this case, since they're batch applications. The question might still stand in case of our online environment, where the SQL is frequently generated by a home-grown process, and even the BIND parms are difficult (actually impossible) to change for and individual program. We can always REBIND and override the Isolation Level..... [...]
9366 124 18_Re: Visual Explain13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Thu, 9 Mar 2000 08:15:26 -0600578_iso-8859-1 You need to execute the SETUP.EXE after unzipping and then execute the DSNVE.EXE
-----Original Message----- From: Myron Miller [mailto:myronwmiller@YAHOO.COM] Sent: Thursday, March 09, 2000 7:45 AM To: DB2-L@RYCI.COM Subject: Re: Visual Explain
I downloaded it from the IBM Web site and Unzipped it. Then I execute DSNVE.EXE.
After you do the ADD do you get a line on the main panel with the name of the system that you added and a lightning bolt in the status field? If you do, do you highlight that line and then click on Connect? [...]
9491 66 44_Re: Monitiring the growth of db2 tablespaces15_Backes, Michael26_BackeM@MAIL.OA.STATE.MO.US30_Thu, 9 Mar 2000 10:14:07 -0600503_iso-8859-1 without a little more detail on what tools you have etc its kind or hard to give anything other than some generic ideas which might not be much help. and since you say tables not tablespaces the lack of specifics gets even more difficult, as we dont even know what kind of tables you have. ie if you have 10 tables in a tablespace and need to know that 1 of the tables is growing at x percent, is a whole world different than needing to know that the tablespace is growing at y percent. [...]
9558 19 25_Re: DB2 Hangs at Shutdown12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV30_Thu, 9 Mar 2000 11:22:05 -0500448_iso-8859-1 Just a footnote to my previous email.....DB2 hung last night and had to be cancelled before this morning's IPL. Maybe I shouldn't say "this doesn't happen too often"!
FYI,
R
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
9578 148 18_Re: Visual Explain11_Robert Jans21_Jans.Robert@AMSTR.COM30_Thu, 9 Mar 2000 09:28:00 -0700604_- I'm having problems setting up VE6 also. I have hte zip file I downloaded from IBM. There is no setup.exe in it............
Robert Jans Albertsons, Inc 299 South Main Street Mail Drop 5S-43 Salt Lake City, UT 84111 (801) 961-5313 jans.robert@albertsons.com
------------------( Forwarded letter 1 follows )--------------------- Date: Thu, 9 Mar 2000 08:15:26 -0600 To: DB2-L@RYCI.COM From: Glenn.Mackey[GMackey]@GUIDEMAIL.COM Sender: owner-db2-l@RYCI.COM Reply-To: DB2.Data.Base.Discussion.List[DB2-L]@RYCI.COM Subject: Re: Visual Explain [...]
9727 192 18_Re: Visual Explain12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM30_Thu, 9 Mar 2000 11:49:39 -0500374_us-ascii I moved the directory to my C: - no change.
The path looked promising it was quite large so I trimmed it down. I assume that the important files in the path are C:\IFOR\WIN\BIN and C:\IFOR\WIN\BIN\EN_US. Unfortunately, after cutting it down to under 160 bytes and rebooting, it's still the same. Do you have any other relavant directories in your path? [...]
9920 168 119_Repost: New White Paper: "IBM DB2 Universal Database: Building Ex tensible , Scalable Business Solutions" by Judy Davis13_Morrill, John12_JohnM@VP.NET30_Thu, 9 Mar 2000 09:57:58 -0700722_iso-8859-1 Greetings!
Below is a posting from Mike Swift's DB2 Interested Parties mailing list. If you would like to join his list, you can e-mail him at dbswift@us.ibm.com. Not all posting to DB2 Interested Parties are re-posted here.
Cheers!
J. Michael Morrill Chief DB2-L List Owner =================================================
Dear DB2 Interested Parties,
A new white paper by data management consultant, Judy Davis, about DB2 UDB for UNIX, Windows, OS/2 entitled "IBM DB2 Universal Database: Building Extensible, Scalable Business Solutions" is now available. The paper focuses on how DB2 is built to address the requirements of current and emerging applications. [...]
10089 44 62_Re: Can Isolation level UR and updates be in the same package?14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Thu, 9 Mar 2000 12:00:08 -0500632_iso-8859-1 Yes you can....UPDATE, INSERT, DELETE would use CS.
Manas
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Alan Gredell > Sent: Thursday, March 09, 2000 10:04 AM > To: DB2-L@RYCI.COM > Subject: Can Isolation level UR and updates be in the same package? > > > Hello, list. I've tried looking but haven't found a quick answer. Can I > bind a package with ISOLATION LEVEL(UR) that contains update statements > (outside of a cursor)? I've been assured by the application > folks that the > two simultaneous batch processes we're considering aren't > [...]
10134 101 13_Re: COPY JOBS0_19_Tim.Lowe@STPAUL.COM30_Thu, 9 Mar 2000 11:14:40 -0600503_us-ascii Arnold. The 2 advantages I could see in starting the tablespace set for read-only would be to guarantee a consistent, sharelevel reference backup of a tablespace set, and to attempt to ensure that the copy step does not fail. But, I think that a batch -START RO command runs asyncronously (not taking effect immediately if the tablespaces are in use), and the step that issues this command will not get any kind of "bad return code" in that case, so the image copy step could fail anyway. [...]
10236 265 18_Re: Visual Explain12_Myron Miller22_myronwmiller@YAHOO.COM30_Thu, 9 Mar 2000 09:22:03 -0800605_us-ascii I just asked IBM how to determine the version/maintenance of DB2 Connect as we have the same problem. Here's their answer: "You can find the service/build level by going into the registry as follows:
.
Win95 => regedit
NT 3.51 => regedt32
NT 4.0 => regedt or regedt32
.
To find the build level run REGEDIT(see the above note to determine which regedit) from a dos prompt. A window will appear, you need to trace down to the software you are running. For example, if you were running db2 2.1.2 you would follow the route: HKEY_LOCAL_MACHINE SOFTWARE [...]
10502 277 18_Re: Visual Explain12_Myron Miller22_myronwmiller@YAHOO.COM30_Thu, 9 Mar 2000 09:26:47 -08001089_us-ascii Here's the relevant portion of my AUTOEXEC.BAT SET PATH=C:\ORAWIN\BIN;%PATH%;C:\PROGRA~1\E!PC;C:\ORAWIN95\BIN;C:\READIBMW SET PATH=%PATH%;C:\PROGRA~1\E!PC;C:\SQLLIB\SNA\SYSTEM;C:\SQLLIB\BIN;C:\SQLLIB\FUNCTION;C:\SQLLIB\SAMPLES\REPL;C:\SQLLIB\HELP;C:\IFOR\WIN\BIN;C:\IFOR\WIN\BIN\EN_US IF EXIST C:\IMNNQ_95\IMNENV.BAT CALL C:\IMNNQ_95\IMNENV.BAT SET DB2INSTANCE=DB2 SET DB2PATH=C:\SQLLIB SET LIB=C:\SQLLIB\LIB SET INCLUDE=C:\SQLLIB\INCLUDE SET CLASSPATH=.;C:\SQLLIB\JAVA\DB2JAVA.ZIP;C:\SQLLIB\JAVA\RUNTIME.ZIP SET IPF_PATH32=C:\IFOR\WIN\BIN\EN_US SET NLSPATH=C:\IFOR\LS\MSG\%%L\%%N SET HELP=C:\IFOR\WIN\BIN SET I4_LANG=EN_US SET I4_INSTALL_DRIVE=C: SET BOOKSHELF=C:\IFOR\WIN\BIN\EN_US
--- MARTIN WOLFF wrote: > I moved the directory to my C: - no change. > > The path looked promising it was quite large so I > trimmed it down. I assume that the > important files in the path are C:\IFOR\WIN\BIN and > C:\IFOR\WIN\BIN\EN_US. > Unfortunately, after cutting it down to under 160 > bytes and rebooting, it's still [...]
10780 191 18_Re: Visual Explain12_Myron Miller22_myronwmiller@YAHOO.COM30_Thu, 9 Mar 2000 09:27:19 -0800693_us-ascii Just execute the DSNVE.EXE
--- Robert Jans wrote: > I'm having problems setting up VE6 also. I have hte > zip file I downloaded from > IBM. There is no setup.exe in it............ > > > > Robert Jans > Albertsons, Inc > 299 South Main Street > Mail Drop 5S-43 > Salt Lake City, UT > 84111 > (801) 961-5313 > jans.robert@albertsons.com > > > ------------------( Forwarded letter 1 follows > )--------------------- > Date: Thu, 9 Mar 2000 08:15:26 -0600 > To: DB2-L@RYCI.COM > From: Glenn.Mackey[GMackey]@GUIDEMAIL.COM > Sender: owner-db2-l@RYCI.COM > Reply-To: > DB2.Data.Base.Discussion.List[DB2-L]@RYCI.COM > Subject: [...]
10972 306 24_Re: LOG YES on Utilities16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Thu, 9 Mar 2000 09:34:27 -0800382_iso-8859-1 I got the following messages: DSNU050I DSNUGUTC - RECOVER TABLESPACE DLTEC.SLTECB00 DSNU510I ? DSNUCALA - NO GOOD FULL IMAGE COPY DATA SET FOR RECOVERY DSNU549I ? DSNUCALA - RECOVER TABLESPACE=DLTEC.SLTECB00 DSNUM=0 USES ONLY DB2 LOGS STARTING FROM LOGPOINT=X'00857EBD36BB'.
It looked for a copy and didn't find it, so it recovered from the RBA of the REORG. [...]
11279 15 18_Re: Visual Explain19_Lisa Westcott-Dryer29_Lisa_Westcott-Dryer@AMWAY.COM30_Thu, 9 Mar 2000 12:35:09 -0500343_us-ascii I have C:\SQLLIB\BIN in my path (this was created from the DB2 connect install).
Lisa
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
11295 34 25_migration to OS/390 DB2V511_Servaes Els21_els.servaes@ORDAB.COM30_Thu, 9 Mar 2000 18:27:50 +0100617_iso-8859-1 Hello all,
We are in the process of migrating to V5 from V4(PUT level 9801). Last year we already finished a successful migration on our system-test DB2, but we had to freeze the project (and fallback) because of the 2000-project in our company. We ordered new tapes (to have the most recent service level), but now we encounter an error at startup of V5: DSNX204I with reasoncode 00000000; Catmaint gives a 04E-abend with reasoncode 00c90101. It seems that our V4 needed some maintenance before migration (cfr II10128: 14 PTF's; 570 prereq. with even open problems). Apparently we are not the [...]
11330 292 18_Re: Visual Explain19_Lisa Westcott-Dryer29_Lisa_Westcott-Dryer@AMWAY.COM30_Thu, 9 Mar 2000 12:39:42 -0500638_us-ascii Version 5 of the DB2 client Application Enabler
service level WR0984
Lisa
Myron Miller @RYCI.COM> on 03/09/2000 12:22:03 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc:
Subject: Re: Visual Explain
I just asked IBM how to determine the version/maintenance of DB2 Connect as we have the same problem. Here's their answer: "You can find the service/build level by going into the registry as follows: [...]
11623 239 18_Re: Visual Explain12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM30_Thu, 9 Mar 2000 12:40:50 -0500617_us-ascii BINGO !!!! That's it, I have a connection!
Thanks RompotK, thanks Myron, thanks Lisa, thanks Kurt and thanks to anyone else I've missed. I hope I can repay you all with interest!
Martin.
RompotK@Schneider.Com wrote:
> The /sqllib/bin directory is very important to have in your path statement. > > > MARTIN WOLFF > SSING.COM> cc: > Sent by: DB2 Data Base Subject: Re: Visual Explain > Discussion List > > > > 03/09/2000 10:49 AM > Please respond to DB2 > Data Base Discussion > List > > > > I moved the directory [...]
11863 304 18_Re: Visual Explain11_Kurt Rompot21_RompotK@SCHNEIDER.COM30_Thu, 9 Mar 2000 11:47:33 -0600694_us-ascii Did anyone suggest executing "db2level" from the \sqllib\bin directory?
-------------------- Kurt Rompot DB2 Database Analyst Schneider National, Inc. rompotk@schneider.com
Myron Miller cc: Sent by: DB2 Subject: Re: Visual Explain Data Base Discussion List
03/09/2000 11:22 AM Please respond to DB2 Data Base Discussion List
I just asked IBM how to determine the version/maintenance of DB2 Connect as we have the same problem. Here's their answer: "You can find the service/build level by going into the registry as follows: [...]
12168 29 11_db2 and sms11_D. Williams17_dale@METRONET.COM30_Thu, 9 Mar 2000 11:15:41 -0600441_iso-8859-1 I'm trying to explore using SMS for the basics of setting-up smallish test databases in MVS (and OS/390) on our p/390 and r/390 "test" systems one with db2 v3 and another db2 v6. We're trying to explore SMS in DB2 for a couple of reasons:
- to simplify storage management (so that naming specific storage objects are not required). - to closer emulate equivalent test databases and ddl in our aix db2 udb environments [...]
12198 31 36_Accessing Oracle (HP-UX) from OS/39022_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM30_Thu, 9 Mar 2000 10:03:21 -0800287_- Hi Folks, We are an insurance company with a diverse databases. We have a Policy system running on Oracle/HP-UX and a claims system using DB2 (V5) OS390 V2.6. We are now faced with a situation where CICS txns and MVS Batch pgms need to access the policy information in Oracle 8i. [...]
12230 36 36_Re: Deleting old DB2 log files on NT14_Phil Forestall19_forestall@SPRINT.CA30_Thu, 9 Mar 2000 13:07:35 -0500675_iso-8859-1 Paul, any direct replies on this? I have the same requirement. Phil Forestall Toyota Canada ----- Original Message ----- From: "Martin, Paul" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, March 08, 2000 9:52 AM Subject: Deleting old DB2 log files on NT
> Does anyone know how I can determine when the NT active logs are obsolete? > or > Does anyone have a script/program (REXX) that we could use to delete all of > the old/obsolete logs files (I hate to reinvent the wheel)? > > I searched the LISTSERV archives and couldn't find any scripts. > > ================================================ [...]
12267 23 19_DBA in Portland, OR6_RandyS27_RandyS@EMPLOYMENTTRENDS.COM30_Thu, 9 Mar 2000 10:05:54 -0800395_iso-8859-1 Can you help in finding an experienced DB2 DBA for a position in Portland, OR? Company is an internet start up company and is looking for their first DBA. Tremendous growth potential for the right person! Looking for someone with at least five years DB2 DBA experience with some experience in Unix. Any referrals would be greatly appreciated (we do offer a $500 Referral Bonus). [...]
12291 154 37_Cursor name on DECLARE/OPEN and FETCH0_20_vpacheco@AMADEUS.NET30_Thu, 9 Mar 2000 19:06:33 +0100593_us-ascii Dear DB2 List
Thanks for your replies on my doubt. Unfortunately it seems it had been mis-interpreted. Here it is some more information for the problem:
We are using Dynamic SQL. STATIC DYNAMIC SQL within C++ programs. I am not sure if you are familiar with C++, but it has an inheritance facility that is being exploited by the App. guys: they build a generic interface class where they code the SQL interface: EXEC SQL DECLARE cursorname CURSOR FOR ... and then for each table they inherit another class that will suply the interface to that table. The key thing [...]
12446 48 31_-607 on a Build Index Defer Yes9_Dan Vogel19_dan_vogel@YAHOO.COM30_Thu, 9 Mar 2000 10:14:40 -0800533_us-ascii Any idea why a create index defer yes could fail with a -607 option not supported?? ---------+---------+---------+---------+---------+---------+---------+---------+ CREATE TYPE 2 INDEX ISDB2DBA.IXIIPSG ON SYSIBM.SYSINDEXPART (STORNAME , IXCREATOR , IXNAME) USING STOGROUP BMCSG PRIQTY 720 SECQTY 720 FREEPAGE 0 PCTFREE 10 CLOSE NO DEFER YES ; ---------+---------+---------+---------+---------+---------+---------+---------+ DSNT408I SQLCODE = -607, ERROR: OPERATION OR OPTION DEFER YES IS NOT SUPPORTED FOR THIS OBJECT [...]
12495 81 35_Re: -607 on a Build Index Defer Yes0_22_BILL_GALLAGHER@PHL.COM30_Thu, 9 Mar 2000 13:30:00 -0500357_us-ascii Dan,
You're probably getting this error because you're trying to create this index on a catalog table. Keep in mind that IBM likes to treat catalog tables differently from "normal" tables, so you don't always have the same options available when you're dealing with the catalog.
Bill Gallagher, DBA Phoenix Home Life Enfield, CT [...]
12577 78 35_Re: -607 on a Build Index Defer Yes0_19_Mark_Lang@AMWAY.COM30_Thu, 9 Mar 2000 13:36:18 -0500655_us-ascii Dan:
looks like DEFER YES can't be used on user indexes created on SYSIBM.SYSINDEXPART.......see SQL Reference (V5) page 486.
Dan Vogel @RYCI.COM> on 03/09/2000 01:14:40 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc:
Subject: -607 on a Build Index Defer Yes
Any idea why a create index defer yes could fail with a -607 option not supported?? ---------+---------+---------+---------+---------+---------+---------+---------+ [...]
12656 24 13_tablespaceset23_Trivedi, Kaushal J, BGM17_kjtrivedi@ATT.COM30_Thu, 9 Mar 2000 13:40:04 -0500437_iso-8859-1 Hi All, I ran the REPORT utility with tablespaceset option and specified a table name. I t did display the table and its corresponding dependent tables also, in addition it also displayed a number of other tablespaces also, on reading it stated that Report utility displays all the tablespaces in a "Tablespaceset", can anyone throw some light on this what decides a tablespaceset and what and how about a Tablespaceset. [...]
12681 110 35_Re: -607 on a Build Index Defer Yes9_Dan Vogel19_dan_vogel@YAHOO.COM30_Thu, 9 Mar 2000 10:53:00 -0800561_us-ascii That's it. Thanks
--- Mark_Lang@AMWAY.COM wrote: > Dan: > > looks like DEFER YES can't be used on user indexes > created on > SYSIBM.SYSINDEXPART.......see SQL Reference (V5) > page 486. > > > > > > Dan Vogel @RYCI.COM> on > 03/09/2000 01:14:40 PM > > Please respond to DB2 Data Base Discussion List > > > Sent by: DB2 Data Base Discussion List > > > > To: DB2-L@RYCI.COM > cc: > > Subject: -607 on a Build Index Defer Yes > > > Any idea why a create index defer yes could fail > with [...]
12792 328 24_Re: LOG YES on Utilities0_23_Mike_Levine@TEKHELP.NET30_Thu, 9 Mar 2000 14:01:45 -0500482_us-ascii Hi Tony,
I think you mean: image copy (ref or chg, full or incr), QUIESCE, REORG LOG NO, SHRLEVEL REF image copy. If you use REORG LOG NO you should certainly take an image copy first! It is much more likely that you would have to recover as a result of a REORG rather than a DASD failure after a REORG LOG YES. Also, after a REORG LOG NO you cannot take a SHRLEVEL CHANGE image copy (the tablespace is marked COPY PENDING). It must be a FULL, SHRLEVEL REF IC. [...]
13121 94 29_Re: migration to OS/390 DB2V519_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM30_Thu, 9 Mar 2000 13:13:16 -0600410_iso-8859-1 Els, Here's DSNX204I explanation:
DSNX204I csect-name ERROR LOADING THE DB2 CATALOG, REASON reason: DB2 STARTED ACCESS(MAINT)
Explanation: The internal structure (DBD) for the catalog database (DSNDB06) was either not found or inconsistent when DB2 was started. See the appropriate reason for a more detailed description of the problem.
The reason is one of the following: [...]
13216 59 17_Re: tablespaceset0_22_BILL_GALLAGHER@PHL.COM30_Thu, 9 Mar 2000 14:16:14 -0500437_us-ascii Kaushal,
A tablespaceset consists of all tables that are referentially related. When you run the tablespaceset report for a particular tablespace, you not only will get all the tables that are referentially related to that one tablespace you specified, but all the other tables that are related to them, and so on. It will give you a complete set of all the tables that are related to each other one way or another. [...]
13276 49 17_Re: tablespaceset12_craig patton21_prgpatton@HOTMAIL.COM28_Thu, 9 Mar 2000 11:19:54 PST373_- Kaushal, A TABLESPACESET is all of the TABLESPACES whose TABLES are connected via RI. Since recovery is specified at the TABLESPACE level, this is the list of TABLESPACEs that need recovered to the same point in time, if doing a partial recovery on any of the tables in the list. By partial recovery, I mean a TOCOPY or TORBA (LOGPOINT with Data Sharing). HTH Craig [...]
13326 18 15_Re: db2 and sms14_Larry D Bolick24_ldbolick@DUKE-ENERGY.COM30_Thu, 9 Mar 2000 14:24:10 -0500496_us-ascii For your test system, I am assuming that you will not be placing datasets on specific volumes. If this is the case, create a STOGROUP with VOLUMES ('*'). This will allow SMS to place the DB2 VSAM datasets.
Regards, Dean Bolick Duke Energy
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13345 73 35_Re: -607 on a Build Index Defer Yes14_Stevens, Wayne21_Wayne_Stevens@BMC.COM30_Thu, 9 Mar 2000 13:26:16 -0600420_ISO-8859-1 In the SQL Reference for DB2 OS/390 v5.1, page 486, it states that DEFER YES is not allowed on SYSINDEXES, SYSINDEXPART, and SYSKEYS.
Wayne Stevens BMC Software, Inc. wayne_stevens@bmc.com 512 340 6927
-----Original Message----- From: Dan Vogel [mailto:dan_vogel@YAHOO.COM] Sent: Thursday, March 09, 2000 12:15 PM To: DB2-L@RYCI.COM Subject: -607 on a Build Index Defer Yes [...]
13419 59 25_Re: DB2 Hangs at Shutdown9_alex chan22_alex_ky_chan@YAHOO.COM30_Thu, 9 Mar 2000 11:23:20 -0800382_us-ascii Hi,
we are converting our BMC DB2 COPY PLUS image copy jobs (some of them are using CARTE (800Mb storage), and some are using VTS now ) to write to native MAGSTAR tapes from IBM (MAGSTAR tapes have capacity of 10G uncompressed and 30G compressed ).
We are basically going to change our control card in our BMC COPYPLUS job from UNIT CARTE to UNIT CARTM. [...]
13479 18 20_stogroup vol err msg14_Marg Gozdowski27_marg.gozdowski@LA-Z-BOY.COM30_Thu, 9 Mar 2000 14:45:35 -0500476_iso-8859-1 RECAP: I had a problem with stogroups using all numbers in the volume.
Ron Graham said to put quotes around the volume name.
I did & it worked.
I just wanted to thank Ron for his help - I appreciate it.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13498 24 19_UDB import problems7_I Singh21_ISingh@DSTSYSTEMS.COM30_Thu, 9 Mar 2000 13:52:57 -0600583_us-ascii I am hanging up when trying to run my import statement from the application server (sun solaris).Database server is also sun solaris. I am logged on as user1 but connect to the database as webuser. I have all the necessary read privileges as far as the data file that I am trying to import is concerned. On the database, user webuser has access to select,insert,update,delete on the table being imported into through appropriate ibmgroups. The import does not hold any locks and just goes into UOW waiting. It does show that it has read 1 page. (some catalog table for [...]
13523 18 21_db2 for unix question12_Pablo Lucero18_plucero@TTI.COM.AR30_Thu, 9 Mar 2000 17:05:40 -0300425_iso-8859-1 Hi, all We have a request to provide a RDBMS with a multithreaded architecture. Do you know if DB2 (in UNIX environments) fulfill this requirement? Thanks all, Pablo Lucero
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13542 16 16_No subject given15_Sherri Sterling23_ssterling@BPD.TREAS.GOV30_Thu, 9 Mar 2000 15:29:32 -0500376_US-ASCII Has anyone successfully back out db2 version 6 migration. If so will you share your experiences and procedures.
Sherri
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
13559 30 36_Re: Deleting old DB2 log files on NT18_Gert van der Kooij15_geko@WANADOO.NL30_Thu, 9 Mar 2000 22:03:04 +0100479_iso-8859-1 >> Does anyone know how I can determine when the NT active logs are obsolete? > > or > > Does anyone have a script/program (REXX) that we could use to delete all > of > > the old/obsolete logs files (I hate to reinvent the wheel)? >
If you are running UDB 5.0 / 5.2 you can determine the first active log file with the 'get db cfg for your-dbname'. You can delete all logs with a lower sequence number. If you are on 6.1 you can use the prune log command. [...]
13590 43 43_Dual Active Logs/Single Active Log Question12_Bruce Fuller17_BFuller@EBSCO.COM30_Thu, 9 Mar 2000 15:28:55 -0600354_- Hi everyone, I am sure this topic has been addressed sometime in the past but I can not find a reference to my specific question so here is my situation.
We have mirrored dasd and are very constrained for disk space (like everyone else). We are attempting to use some smaller disks for our active logs and we came up with a plan like this. [...]
13634 61 36_Re: Deleting old DB2 log files on NT14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Thu, 9 Mar 2000 18:17:55 -0500583_iso-8859-1 Gert:
Correct me if I'm wrong, but I'd think you would lose rollforward recovery to a point in time earlier than the first log file if you deleted all log files prior to the first active log file.
Manas.
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Gert van der Kooij > Sent: Thursday, March 09, 2000 4:03 PM > To: DB2-L@RYCI.COM > Subject: Re: Deleting old DB2 log files on NT > > > >> Does anyone know how I can determine when the NT active logs are > obsolete? > > > [...]
13696 84 41_Re: Cursor name on DECLARE/OPEN and FETCH14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 10 Mar 2000 10:39:10 +1100644_- Vitor,
Since it appears you didn't like the idea of using class variables to manage a chain of free cursor names, your alternative is to use ODBC.
I might point out that, although I haven't found an equivalent statement for DB2, in the Oracle manual "Programmer's Guide to the Oracle Pro*C/C++ Precompiler Release 8.0 A54661_01" I did find the following statements: - "ANSI standard X3.135-1992 (known informally as SQL92) provides three levels of compliance: Full SQL[;] Intermediate SQL (a subset of Full SQL)[;] Entry SQL (a subset of Intermediate SQL)[.] ... A conforming SQL implementation must support at least Entry [...]
13781 86 36_Re: Deleting old DB2 log files on NT13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Fri, 10 Mar 2000 00:08:30 +0000396_us-ascii You certainly would.
Using the LIST HISTORY command you'll see under each backup listed the first image copy file you must keep if the earliest point in time you'll need to recover from is that backup.
The DB CFG tells you which logs are active.
I use a Perl script I wrote to remove logs older than a certain number of backups on our DB2 for AIX V5.2 servers. [...]
13868 44 24_Re: LOG YES on Utilities14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Fri, 10 Mar 2000 11:16:36 +1100599_- Tony,
Another alternative is REORG LOG YES followed by giving the users the data and doing a COPY SHRLEVEL CHANGE.
Ultimately, this is a business decision - each alternative has costs and benefits. So ask your users which option they choose (and get their choice in writing).
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: Provenzola, Tony [mailto:Tony.Provenzola@NIKE.COM] Sent: Friday, March 10, 2000 3:34 AM To: DB2-L@RYCI.COM Subject: Re: LOG YES on Utilities [...]
13913 108 36_Re: Deleting old DB2 log files on NT0_29_Greg.Palgrave@BANKWEST.COM.AU31_Fri, 10 Mar 2000 08:45:58 +0800360_us-ascii My suggestion is to at least keep all logs that are newer than your most recent backup, plus perhaps a few prior to allow for any inflight units of work that may need to be rolled out if you recover.
We have daily backups and log retain/userexit, but we keep 7 days worth of each - probably overkill, but disk space is cheap. [...]
14022 80 36_Re: Deleting old DB2 log files on NT18_Gert van der Kooij15_geko@WANADOO.NL31_Fri, 10 Mar 2000 03:03:03 +0100466_iso-8859-1 Sorry, you are right. You don't need it for autorecovery but you sure do need it for rollfoward recovery. If you use the userexit this can be managed automatically. We used it with ADSM and it all ran without problems.
----- Original Message ----- From: "Manas Dasgupta" Newsgroups: bit.listserv.db2-l To: Sent: Friday, March 10, 2000 12:17 AM Subject: Re: Deleting old DB2 log files on NT [...]
14103 21 17_Raw SQL Formatter15_Mallett, Steven38_Steven.Mallett@CORPMAIL.TELSTRA.COM.AU31_Fri, 10 Mar 2000 15:28:05 +1100565_- Hi y'all, Our Development team have been asking if there's a good tool to formatDB2 Data Base Discussion List raw SQL (like those stored in SYSIBM.SYSSTMS, etc) in an MVS OS/390 environment (running DB2 V5.1).
Anyone using anything to make the SQL "pretty" enough for a human to read?
regards, Steve
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
14125 57 21_Re: Raw SQL Formatter15_Toine Michielse18_vndobtm@US.IBM.COM31_Fri, 10 Mar 2000 08:12:13 +0000448_us-ascii Hello Steve,
I have a some REXX code that'll do just that. Haven't maintained it since V4 but I guess it'll do as a starting point.
Regards,
Toine Michielse DB2 S/390 Advocate Santa Teresa Laboratory Mobile: +31 6 537 23 256 Email: vndobtm@us.ibm.com Lotus: Toine Michielse/Santa Teresa/Contr/IBM@IBMUS
"Mallett, Steven" @RYCI.COM> on 03/10/2000 04:28:05 AM [...]
14183 27 43_Clients connecting to DB2 through Websphere12_Magnus Lassi13_lassi@HOME.SE31_Fri, 10 Mar 2000 09:27:06 +0100540_iso-8859-1 hi, I'm wondering about how DB2 creates sessions for clients.
We have a webapplication on Websphere and the clients will connect to the database through the applicationserver via JDBC. All clients will connect as the same useraccount on the database. Does DB2 see it as one client only that makes several queries, or will it create a session for every connection? I assume it is the latter.. would it be any different connecting via JDBC from every client straight to DB2 through one account? would it work the same? [...]
14211 432 24_Re: LOG YES on Utilities0_18_mebert@AMADEUS.NET31_Fri, 10 Mar 2000 09:45:45 +0100333_us-ascii Sorry but I have to disagree with that. It does say in the docu that you have to take a SHRLEVEL REF IC to reset COPY Pending status... not true (wouldn't make sense anyway). SHRLEVEL CHANGE is enough. (DB2 V5, OS/390 R2.6) (tried it because I didn't believe it, even if it was the Admin & Utilities Guides saying it). [...]
14644 73 47_Re: Dual Active Logs/Single Active Log Question9_Stan Hoey24_stan_hoey@CIRCLE-DL2.COM31_Sat, 11 Mar 2000 08:47:31 -0800322_us-ascii Bruce,
Its some time since I did any testing in this area, and we have had some debate about this in the office. We think that DB2 will only drop into single logging as a last resort. It will always attempt to allocate a matching pair of logs, so in the case you describe, DB2 will use the DS03 pair. [...]
14718 21 31_Ways to generate Surrogate Keys7_Db2 DBA24_data_analyst@HOTMAIL.COM31_Fri, 10 Mar 2000 02:53:53 -0600336_- Hi all,
I am looking for some advice on how to generate Surrogate Keys effectively in UDB EEE 6.1.
Is it better to generate it in the application, through a DB trigger, or otherwise?
I have some ideas for generating these keys but am wondering if anyone has actually done this and can give me some feedback. [...]
14740 21 52_Bufferpools and Async i/o and indexes (DB2 v4 OS390)9_Jim Leask27_jim.leask@RS-COMPONENTS.COM31_Fri, 10 Mar 2000 10:12:19 -0000479_ISO-8859-1 Question:
Would you put indexes into the bufferpool reserved for sequential access, if the index is predominantly accessed sequentially?
I am inclined to say yes.
Any opinions greatly received.
Jim.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
14762 54 40_Re: Accessing Oracle (HP-UX) from OS/39032_Cuneyt Goksu (Garanti Teknoloji)22_CuneytG@GARANTI.COM.TR31_Fri, 10 Mar 2000 13:05:06 +0200533_- We have a similiar requirement.
Datajoiner is the one we're evaluating right now.
> Cuneyt GOKSU > DBA > IBM CSE - DB2 Universal Database V5 Database Administration > GARANTÝ TEKNOLOJÝ > Istanbul / TURKEY > Tel : 0090-212-657 0404 Ext: 1218 > mailto:Cuneytg@garanti.com.tr > > Think Enterprise, Think Mainframe!
-----Original Message----- From: Ramesh Balasubramanyan [mailto:RAMBAL@SAIF.COM] Sent: Thursday, March 09, 2000 8:03 PM To: DB2-L@RYCI.COM Subject: Accessing Oracle (HP-UX) from OS/390 [...]
14817 28 43_Possible Solution to Visual Explain Problem15_Jeffery A Price17_jprice@IPALCO.COM31_Fri, 10 Mar 2000 06:39:48 -0500410_us-ascii Hi all,
I too had problems with visual explain dying with no errors upon issuing a connect. My solution was to alter the following setting under the Client Configuration Assistant:
Start the CCA, select your Database Alias, click on properties. Click on properties under the Connection portion of window. Make sure that you have 'On the Host or AS/400' checked on the Security tab. [...]
14846 73 56_Re: Bufferpools and Async i/o and indexes (DB2 v4 OS390)10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Fri, 10 Mar 2000 11:48:47 GMT517_us-ascii Hi Jim, I will say it depends.It depends on the some of the issues which i can think of are :-
1) I think u may be having pool of type sequential with update and without update and it depends on how much %age of ur index columns which are accessed sequentially are updated ,if it is not so u can place it on the separate bufferpool for indexes only because u can have ur VDWQT and DWQT as 0 and VPSEQT as 100% (and may be HP can be used). If it is highly updated then threshold manipulation will [...]
14920 20 21_Re: Raw SQL Formatter13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 10 Mar 2000 08:02:58 -0500469_iso-8859-1 Hi Toine, Is that something you could share with the lists by posting it to the documents site?
thanks in advance.
David Seibert Compuware Corporation File-AID product planner Dave.Seibert@Compuware.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
14941 142 14_Re: DB2 UDB/NT10_Leo Flores23_leoflores@EARTHLINK.NET31_Fri, 10 Mar 2000 05:03:52 -0800600_us-ascii Michael,
I believe you will find this site an excellent starting point
ftp://ftp.software.ibm.com/ps/products/db2/info/vr6/htm/index.htm
HTH Leo Flores
ALLEN,Bruce wrote:
> > > Hi Michael, > I run it on a 32M Pentium, and it runs. Everything is slow on that machine as it pages > a lot. I don't think I started Control Centre though. > > On a 64M machine, and starting Control Centre, it runs slow as it pages a lot. > > On a 256M Pentium3, it runs just fine, including Control Centre. > > So I would say 64M unless you want to use Control Centre, in [...]
15084 116 56_Re: Bufferpools and Async i/o and indexes (DB2 v4 OS390)9_Jim Leask27_jim.leask@RS-COMPONENTS.COM31_Fri, 10 Mar 2000 13:25:15 -0000392_ISO-8859-1 Sanjeev,
Thank you for your informative reply.
More info: Bufferpool Set Up (No Hiperpools as CMOS on the horizon):- BP0 DB2 Catalog/Dir BP1 Indexes BP2 Critical indexes and small look up tables BP3 Sequential accessed objects (currently tables only, update & non-update) BP4 Tables BP7 Sort work area
BP3: VPSEQT=95; VDWQT=7; DWQT=50. DWQT is never hit. [...]
15201 35 10_RRS vs CAF16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Fri, 10 Mar 2000 14:58:35 +0100473_- Hi,
because of mix-environment MQS and DB2 we switch to RRS instead of CAF.
Unfortunally we can not invoke RRS application programs under TSO/ISPF while running SPUFI/BMC in split mode. This works with CAF
Some Displays ***** Start of DB2-RRS-Interface *** FC= IDENTIFY Reason00F30024 FC= SIGNON Reason000000C8 FC= CREATE THREAD Reason00C12204 ***** SqlCode *****000000098J FC= TERMINATE THREAD Reason00C12204 ***** End of DB2-RRS-Interface *** [...]
15237 107 56_Re: Bufferpools and Async i/o and indexes (DB2 v4 OS390)14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sat, 11 Mar 2000 01:05:23 +1000613_us-ascii Sanjeev,
You have missed another very major reason why indexes are processed sequentially, even when high matchcols and no prefetch shown in access path. If batch process has been designed for performance, it will process data in the sequence data is stored in the tables and major indexes, i.e. sequentially and not randomly. The driving process may be a cursor or a flat file or whatever but the ideal is that other SQLs executed inside the driving process will process as many database objects as possible sequentially whether indexes or data. Some of the lookups inside the loop may have [...]
15345 61 31_Ways to generate Surrogate Keys14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sat, 11 Mar 2000 01:25:10 +1000435_us-ascii Some of these ideas may be radical but I have a lot of experience with DB2 systems doing too much random I/O and surrogate ids. are a major contributor when not used very carefully.
Try to have as few independently generated Surrogate Ids. as possible but rather try to promote having related tables similarly clustered and have child tables reuse surrogate id. of parent with additional columns in composite key. [...]
15407 57 14_Re: RRS vs CAF20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 10 Mar 2000 09:24:36 -0500346_us-ascii From an address space you can't have mix of CAF/DSN or RRSAF. Which ever connection is established first that should be used for every tasks in that address space for concurrent access to DB2.
For example: If you establish RRSAF first then your concurrent CAF connection will fail or vice-versa.
Regards Venkat Pillay [...]
15465 134 56_Re: Bufferpools and Async i/o and indexes (DB2 v4 OS390)9_Jim Leask27_jim.leask@RS-COMPONENTS.COM31_Fri, 10 Mar 2000 14:27:32 -0000569_ISO-8859-1 Michael,
Thanks, your PS comment is especially useful.
I also missed the prospect of sequential detection.
As an aside, can you (or someone else) confirm that number of pages shown for Dynamic Prefetch (Display Bufferpool - DSNB414I) are due to Sequential Detection.
Thanks again to Michael and Sanjeev. Jim.
-----Original Message----- From: Michael Hannan [mailto:mhannan@C031.AONE.NET.AU] Sent: 10 March 2000 15:05 To: DB2-L@RYCI.COM Subject: Re: Bufferpools and Async i/o and indexes (DB2 v4 OS390) [...]
15600 45 40_Re: Accessing Oracle (HP-UX) from OS/39018_Merdjanian, Arthur25_arthur.merdjanian@HBC.COM31_Fri, 10 Mar 2000 09:25:26 -0500584_- We use Oracle Client for OS/390.
> -----Original Message----- > From: Ramesh Balasubramanyan [SMTP:RAMBAL@SAIF.COM] > Sent: Thursday, March 09, 2000 1:03 PM > To: DB2-L@RYCI.COM > Subject: Accessing Oracle (HP-UX) from OS/390 > > Hi Folks, > We are an insurance company with a diverse databases. We have a > Policy system running on Oracle/HP-UX and a claims system using DB2 (V5) > OS390 V2.6. We are now faced with a situation where CICS txns and MVS > Batch pgms need to access the policy information in Oracle 8i. > > Since such a diverse platform is not unusual, I [...]
15646 76 73_Repost: DB2 UDB vs. Oracle Performance...One Oracle Expert's Expe riences13_Morrill, John12_JohnM@VP.NET31_Fri, 10 Mar 2000 07:41:28 -0700596_- Greetings!
Below is a posting from Mike Swift's DB2 Interested Parties mailing list. If you would like to join his list, you can e-mail him at dbswift@us.ibm.com. Not all posting to DB2 Interested Parties are re-posted here.
Cheers!
J. Michael Morrill Chief DB2-L List Owner =================================================
Dear DB2 Interested Parties,
I thought many of you would enjoy this note I received from Adrian Challinor, Director of R&D at e-zData.net, a vendor/consultancy of data management products/solutions (see www.e-zdata.net): [...]
15723 16 14_AW: RRS vs CAF16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Fri, 10 Mar 2000 15:45:52 +0100368_- Thank you Venkat, but unbelieveable
(back to the days where ISPF (PDF) doesn't allow Split screens :-))
Roland
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
15740 35 14_Re: RRS vs CAF20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Fri, 10 Mar 2000 09:56:57 -0500659_us-ascii Unfortunately while DB2 establishes first connection, the address space is identified as user of DB2. The address space is initialized if CAF is used then DSNACAB is loaded. The RRSAF will not work concurrently even as a subtask.
Lets hope this improves in future as ISPF did.
> -----Original Message----- > From: Roland Schiradin [SMTP:SchiradinR@ALTE-LEIPZIGER.DE] > Sent: Friday, March 10, 2000 9:46 AM > To: DB2-L@RYCI.COM > Subject: AW: RRS vs CAF > > Thank you Venkat, but unbelieveable > > (back to the days where ISPF (PDF) doesn't allow Split screens :-)) > > Roland > > ================================================ > [...]
15776 91 25_Re: DB2 Hangs at Shutdown12_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE31_Fri, 10 Mar 2000 16:14:59 +0100618_- These ghosts are real! Started tasks (not only DB2) sometimes showing on operators console even after successfull shut down. They haunted us in OS/390 version 2.4, but disappeared after upgrading to 2.6. /Seppo
> -----Original Message----- > From: DAVIS, RICK (SBCSI) [SMTP:RD8246@MOMAIL.SBC.COM] > Sent: Thursday, March 09, 2000 2:24 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2 Hangs at Shutdown > > Max, > Call "The Exorcist" or "Ghost Busters" > > Rick Davis > "This e-mail and any files transmitted with it are the property of SBC, > are > confidential, and are intended solely for the use of [...]
15868 57 18_Re: Visual Explain12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 10 Mar 2000 09:12:00 -0500633_- >>> DB2-L@RYCI.COM@inter2 03/08/00 02:19PM >>> I too am having exactly the same problem. There seems to be a common mistake here. Bob or Danny, if you finally got it working, I would appreciate knowing how you did it.
Thanks,
Martin Wolff, Global Crossing.
>I have also run into the exact same problem and would be interested in any resolutions anyone could provide as well. > > >Danny Davis >Systems Analyst >Univerity Health Systems >dtdavis@pcmh.com > >>>> BOB JEANDRON 01/05/00 01:12PM >>> >We are just getting started with DB2 Connect. We are going from an NT to an >AIX UNIX [...]
15926 87 47_Re: Dual Active Logs/Single Active Log Question19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 10 Mar 2000 09:22:35 -0600598_iso-8859-1 Bruce, I think you're on the right track as far as contention issues are concerned. I've never experienced the DASD failure situation you've described. One of the things I have seen occurred when a write failed during logging. DB2 truncated Logcopy1 and 2, then switched to the next log pair. DB2 then archived and used the truncated logs as normal. You didn't say where you intend to put the BSDSs. Nor did you address availability issues. From an availability, and performance standpoint I'd configure as follows: on Controller A, attached to channels 1, 2, 3, and at least 4, on [...]
16014 68 47_Re: Dual Active Logs/Single Active Log Question12_Bruce Fuller17_BFuller@EBSCO.COM31_Fri, 10 Mar 2000 09:27:35 -0600612_- My chart did not show up correctly so I will attempt to clarrify this sorrow for the confusion.
Vol1 Logcopy1 DS01 Logcopy2 DS03
Vol2 Logcopy1 DS02 Logcopy2 DS04
Vol3 Logcopy1 DS03 Logcopy2 DS01
Vol4 Logcopy1 DS04 Logcopy2 DS02
Bruce Fuller
> -----Original Message----- > From: Bruce Fuller > Sent: Thursday, March 09, 2000 3:29 PM > Subject: Dual Active Logs/Single Active Log Question > > Hi everyone, > I am sure this topic has been addressed sometime in the past but I can > not find a reference to my specific > question so here is my situation. > > [...]
16083 16 25_Re: DB2 Hangs at Shutdown14_Massimo Scarpa16_mscarpa@CESVE.IT31_Fri, 10 Mar 2000 16:28:35 +0100366_us-ascii Thanks SEPPO !! I was thinking to be the only one who had visions !!!
Regards Max Scarpa Data & System admin
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
16100 44 19_Connection Pooling?15_Klassy, Carolyn30_Carolyn.Klassy@TRS.STATE.TX.US31_Fri, 10 Mar 2000 09:28:51 -0600628_iso-8859-1 It has been recommended in our shop to use connection pooling so our JAVA application accessing DB2 OS/390 will perform more efficiently. The connection pooling will be performed by the product Toplink which states it uses an IBM DB2 connection pool since it is using IBM's DB2 JDBC driver.
With connection pooling, a defined number of initial database connections are established at server boot time and placed into a pool. When a connection is needed, it is returned to the pool for later use without actually destroying it. A later call to create a connection merely retrieves and available connection [...]
16145 24 31_who can update production data?0_22_Thomas_Abbott@HESC.COM31_Fri, 10 Mar 2000 10:48:00 -0500575_us-ascii Our DBA group gets sporadic requests from users to update production accounts . Typically this would be for 1 to 5 accounts and involved updating 1 or 2 columns where the changes cannot be done using existing batch/on-line applications. We use SPUFI to perform the updates and file the spufi output as an audit. Over the years this has proven to be a good way to make accurate changes with a quick turnaround. Now, our auditors are telling us that DBA's should not have update authority on production data. Their proposal is to have our group code and test the [...]
16170 42 35_Re: who can update production data?9_Jim Leask27_jim.leask@RS-COMPONENTS.COM31_Fri, 10 Mar 2000 15:50:35 -0000400_ISO-8859-1 Here it is Development Support, one codes the SQL another checks it and runs it. The DBA gets involved when a large number of rows are to be changed, to assess impact offer alternatives etc.
Jim.
-----Original Message----- From: Thomas_Abbott@HESC.COM [mailto:Thomas_Abbott@HESC.COM] Sent: 10 March 2000 15:48 To: DB2-L@RYCI.COM Subject: who can update production data? [...]
16213 21 28_Converting to Type 2 Indexes11_Tim Blewitt22_timblewitt@HOTMAIL.COM29_Fri, 10 Mar 2000 15:57:44 GMT523_- Fellow DB2 Colleagues,
When converting indexes to type 2, is the application data still available, albeit via another access path?
Much obliged,
Tim ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
16235 51 35_Re: who can update production data?9_Don Alden26_Don.Alden@PKSIS.LEVEL3.COM31_Fri, 10 Mar 2000 08:54:34 -0700494_iso-8859-1 When I worked at Motorola the DBA group ran the SPUFI's to update Production data. But like you folks, the Auditors discovered this fact and shut the process down. They said that the DBA's are responsible for maintaining the DBMS and that the data was the end-users responsibility.
So what we did was set-up a process using DB2 Batch jobs that the End User groups used to run their own Updates. Prior to running the jobs the DBA group had to sign-off on the SQL. [...]
16287 57 35_Re: who can update production data?18_Teegarden, Stephen22_Teegarden@AEGONUSA.COM31_Fri, 10 Mar 2000 10:00:11 -0600317_- Thomas;
What we have done in this case was to setup an on request production batch SPUFI job that requires managements approval to run. The update SQL must first be transferred into a secured library with managements approval. The batch job is requested to run reading the SQL from the secured library. [...]
16345 56 32_Re: Converting to Type 2 Indexes14_Bistricer, Avi20_BistricerA@CONED.COM31_Fri, 10 Mar 2000 11:10:02 -0500390_windows-1252 The index will be RECP (Recovery Pending) after the ‘alter index… convert….’ . Dynamic SQL with prepare / E.I. may choose the index and will get unavailable resource. All plan / packages that are dependent on the index will get the error also. .
One thing I’m not clear is if plans / packages become invalid. The SQL ref book says yes but people on the BB said no. [...]
16402 86 23_Re: Connection Pooling?12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 10 Mar 2000 08:14:03 -0800606_us-ascii I have a suggestion that you may not want to hear. Release 6 of DB2 does connection pooling for DB2 COnnect threads very much like CICS does. That is, it will reuse threads (connections) if possible. So upgrading from V5 to V6 would accomplish what you want.
--- "Klassy, Carolyn" wrote: > It has been recommended in our shop to use > connection pooling so our JAVA > application accessing DB2 OS/390 will perform more > efficiently. The > connection pooling will be performed by the product > Toplink which states it > uses an IBM DB2 connection pool [...]
16489 64 35_Re: who can update production data?0_22_BILL_GALLAGHER@PHL.COM31_Fri, 10 Mar 2000 11:15:54 -0500421_us-ascii Our philosophy in the DBA group at my shop is "we don't do data". In the past, we've been asked to take care of requests such as the the ones you mention below, but I always had a problem with doing this because (a) I didn't know the applications, and (b) I didn't know the impact of the requested production data update, and (c) didn't want any fingers pointed my way if something got updated incorrectly. [...]
16554 65 35_Re: who can update production data?11_Paul Mayhew15_mayhewp@CIBC.CA31_Fri, 10 Mar 2000 11:26:19 -0500626_us-ascii So here is my 2 cents.....
The business OWNS the data.
The database/ops groups are the custodian of the data for the business.
In my experience, the application people generally where the group that determined (with the business, or based on business rules) what needed to be changed for firefight or whatever. A request was forwarded to the dba's for verification and execution. Mostly to stop stupid things from happening (like scans, humongous deletes or whatever), once approved, the dba ran it (SPUFI). This was somewhat of an audit problem because of the lack of a trail. It would not [...]
16620 70 23_Re: Visual Explain (VE)12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Fri, 10 Mar 2000 09:44:00 -0500515_- I ran out of time to work on this problem but this is where I am: We are on 2 different levels of OS390, V1.2 in production and V2.6 in test. To access the DB2s in V1.2 we are using APPC through an AIX box using DB2 Connect enterprise edition and DB2 connect Personal Edition on an NT. To access the DB2s on V2.6 we are directly connecting to DB2 databases via TCPIP from DB2 connect PE. VE works with the TCPIP direct connection on OS390 2.6. With the connection to AIX, VE does not work. It just dies with [...]
16691 81 35_Re: who can update production data?0_19_mike.holmans@BT.COM31_Fri, 10 Mar 2000 16:16:31 -0000617_- We do the same, except we don't bother with the QUIESCE.
Mike Holmans BT ISE Technical Design mike.holmans@bt.com
> -----Original Message----- > From: Teegarden, Stephen [SMTP:Teegarden@AEGONUSA.COM] > Sent: Friday, March 10, 2000 4:00 PM > To: DB2-L@RYCI.COM > Subject: Re: who can update production data? > > Thomas; > > What we have done in this case was to setup an on request production batch > SPUFI job that requires managements approval to run. > The update SQL must first be transferred into a secured library with > managements approval. > The batch job is requested to run reading the [...]
16773 69 14_AW: RRS vs CAF16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE31_Fri, 10 Mar 2000 17:33:35 +0100636_iso-8859-1 The Db2 stuff should be on TCB level in this case. I also open a PMR and will see what development think about this. Maybe I'll raise a Desgin-Change
Roland
> -----Ursprüngliche Nachricht----- > Von: Pillay, Venkat (PCA) [SMTP:venkat_pillay@ML.COM] > Gesendet am: Freitag, 10. März 2000 15:57 > An: DB2-L@RYCI.COM > Betreff: Re: RRS vs CAF > > Unfortunately while DB2 establishes first connection, the address space is > identified as user of DB2. The address space is initialized if CAF is used > then DSNACAB is loaded. The RRSAF will not work concurrently even as a > subtask. > [...]
16843 34 35_Re: who can update production data?13_Helen Johnson25_Helen_Johnson@RAC.RAY.COM31_Fri, 10 Mar 2000 10:51:48 -0600417_us-ascii Here the DBAs run the production updates. The auditors would have a fit if programmers had direct update on production tables. All production SPUFI requests must have a signed request. A query is also run to check the update. (The output hasn't always matched what the programmers want.) After the update query is run, the SPUFI output is printed out and filed with the signed request as documentation. [...]
16878 53 35_Re: who can update production data?11_Robert Jans21_Jans.Robert@AMSTR.COM31_Fri, 10 Mar 2000 09:53:00 -0700371_- We have update authority in production. However, we can only exercise it with VP level management or higher approval and only in an after hours emergency, ie: production is down, for a small amount of data changes at most 20 or 30 rows of data. More than that requires the application to code a corrective job and submit it as a special request job to operations. [...]
16932 51 35_Re: who can update production data?16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Fri, 10 Mar 2000 08:54:55 -0800378_iso-8859-1 At Nike, we have a production on-request job that runs dynamic SQL.
The developer processes a one-time turnover request (which requires a secondary approval), and passes the SQL and a range of rows to be affected. The job runs the SQL and verifies that SQLERRD(3) is within the range. If not, rollback; if so, it commits and adds a row to an audit table. [...]
16984 111 35_Re: who can update production data?14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Fri, 10 Mar 2000 12:00:56 -0500562_iso-8859-1 Bill:
I like the "we don't do data" principle. I beleive that DBAs own the structures while the application teams own code that manipulates the data and the users own the data. However in our shop we (DBAs) run adhoc SPUFIs - the assumption being that only we are in a position to determine what impact an adhoc SQL can have on the live 24x7 (well, almost) production environment and should the SQLs start timing out online users we're in the best position to cancel the thread etc. . In our shop we normally get an adhoc request from the [...]
17096 148 34_Re: Strange Problem - DB2 Stogroup19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 10 Mar 2000 11:11:46 -0600563_iso-8859-1 James, DB2 for OS/390 V5. I thought you were saying the order could be determined via an SQL statement. I never did like the way allocations were handled across volumes in a STOGROUP. So, can I draw the following conclusions? If, when using STOGROUPs, DB2 uses this order it would explain why an attempt to extend to candidate volumes might fail provided there were more than 5 volumes in the STOGROUP and none of the first 5 had enough space (each volume had some space available but of which the total on the first 5 volumes was not enough) for [...]
17245 107 47_Re: Dual Active Logs/Single Active Log Question24_Gendron, Dave (Exchange)28_Dave.Gendron@COASTALCORP.COM31_Fri, 10 Mar 2000 12:41:28 -0500359_- Bruce,
This doesn't speak to the contention issue but you did state you are constrained for disk space. A technique I have used and helps prioritize the limited disk space is to have an unequal number of logcopy1 and logcopy2 data sets. In this way, you can use your limited total space weighted toward logcopy1. One possible scenario would be: [...]
17353 50 35_Re: who can update production data?0_20_John_Lendman@FPL.COM31_Fri, 10 Mar 2000 13:15:39 -0500337_us-ascii As far as DB2 goes only the DBA's with approved request. We also keep a audit record of all changes. Our auditors are also trying to stop us, but we just tell them this is the way the industry is doing it. John Lendman Florida Power and Light
Thomas_Abbott@HESC.COM on 03/10/2000 10:48:00 AM [...]
17404 68 35_Re: who can update production data?16_Cockerill, Steve30_SLCockerill@NATIONALGYPSUM.COM31_Fri, 10 Mar 2000 13:21:31 -0500515_windows-1252 Question Does this include ERP system?
-----Original Message----- From: John_Lendman@FPL.COM [mailto:John_Lendman@FPL.COM] Sent: Friday, March 10, 2000 1:16 PM To: DB2-L@RYCI.COM Subject: Re: who can update production data?
As far as DB2 goes only the DBA's with approved request. We also keep a audit record of all changes. Our auditors are also trying to stop us, but we just tell them this is the way the industry is doing it. John Lendman Florida Power and Light [...]
17473 30 35_Re: Ways to generate Surrogate Keys7_Db2 Dba24_data_analyst@HOTMAIL.COM31_Fri, 10 Mar 2000 12:31:40 -0600351_- Michael,
Thanks for the information. I agree that random generation is not a good idea. However, doesnt' sequential generation put a burden on a specific database partition?
A few other questions:
- Does DB2 have any built in features to generate unique values that can be used for this sort of keys? I belive Oracle does! [...]
17504 90 35_Re: who can update production data?0_20_John_Lendman@FPL.COM31_Fri, 10 Mar 2000 13:52:30 -0500550_us-ascii No John
"Cockerill, Steve" on 03/10/2000 01:21:31 PM
Please respond to "DB2 Data Base Discussion List"
To: DB2-L@RYCI.COM cc: Subject: Re: who can update production data?
Question Does this include ERP system?
-----Original Message----- From: John_Lendman@FPL.COM [mailto:John_Lendman@FPL.COM] Sent: Friday, March 10, 2000 1:16 PM To: DB2-L@RYCI.COM Subject: Re: who can update production data? [...]
17595 340 24_Re: LOG YES on Utilities19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 10 Mar 2000 13:23:11 -0600609_iso-8859-1 Doc, Agree on all counts! Many of these "methods" originated many years ago. Some were correct and have since changed, improved, or are no longer required. Most were wrong!
Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your [...]
17936 50 27_LOAD utility on DB2 for AIX17_Gabriel Collantes23_gcollantes@NETSCAPE.NET29_Fri, 10 Mar 2000 14:59:15 EST349_US-ASCII Environment: DB2 UDB 5.2 EE Fixpack 9 on AIX 4.3.1
Got called by users indicating a table is not accessible. Found the tablespace which the table is residing in is in 'LOAD PENDING' status:
Tablespace ID = 3 Name = DATA01A Type = System managed space Contents = Any data State = 0x0008 Detailed explanation: Load pending [...]
17987 64 31_Re: LOAD utility on DB2 for AIX18_McDowell, Scott M.19_SMMcDowell@WEST.COM31_Fri, 10 Mar 2000 14:24:36 -0600446_windows-1252 First, you have to be running using the same userid and be in the same directory as the user that ran the failed load. Then you can run "db2 quiesce tablespaces for table reset" Then you can either terminate the load or restart it.
-----Original Message----- From: Gabriel Collantes [mailto:gcollantes@NETSCAPE.NET] Sent: Friday, March 10, 2000 1:59 PM To: DB2-L@RYCI.COM Subject: LOAD utility on DB2 for AIX [...]
18052 63 24_Re: LOG YES on Utilities0_19_Tim.Lowe@STPAUL.COM31_Fri, 10 Mar 2000 14:36:02 -0600519_us-ascii Michael, OK, I'll bite. I agree with most of what you said, but I am not sure what you meant by this: > Statement 2: people do a QUIESCE after an IC just because "that's the way to do > it", not because it makes sense. What makes sense is: take a QUIESCE just BEFORE > doing massive data changes so if the job fails, you can recover to just before > that (and rerun a hopefully fixed job). Take an IC SHRLEVEL CHANGE afterwards so > if something else then fails, you don't have to redo your processing. A [...]
18116 20 13_Database Rely18_GRESHAM, DEBORAH B18_DGRESHAM@SCANA.COM31_Fri, 10 Mar 2000 15:54:33 -0500353_iso-8859-1 We are evaluating a product called Database Rely from SoftBase Systems. The product documentation says that this is a repackaging of Database Checkpoint and Database Attach. We have used Database Attach for several years and are quite pleased with it. Does anyone have any experience or comments about the Database Checkpoint component? [...]
18137 36 51_DB2 Developer's Guide, 4th edition - Coming in May!8_cmullins23_CMullins@COMPUSERVE.COM31_Fri, 10 Mar 2000 17:24:41 -0500413_ISO-8859-1 Just a quick e-mail to let everyone on DB2-L know that an updated version of my book, DB2 Developer's Guide (4th edition) will be published by SAMS in May 2000. The book is fully updated to cover DB2 through Version 6.
You can pre-order the book from amazon.com today at a 25 per cent discount at: http://www.amazon.com/exec/obidos/ASIN/0672318288/qid=952726369/sr=1-5/104- 4682642-4996465 [...]
18174 19 33_Partitions in separate copy jobs?15_Mitchell, Sandi29_Sandi.Mitchell@AVISTACORP.COM31_Fri, 10 Mar 2000 14:38:26 -0800370_iso-8859-1 OS/390 DB2V4.1 We have a table with 12 partitions, each partition is about 150 cylinders. Image copy steps have been created for each partition and placed in separate jobs. The jobs, which include more tables than just the partitioned one, run simultaneously. What are the advantages of this for a table this size? Are there additional advantages in V5? [...]
18194 139 55_Re: DB2 Developer's Guide, 4th edition - Coming in May!15_Macleod, Robert23_Robert.Macleod@ICBC.COM31_Fri, 10 Mar 2000 14:51:16 -0800533_iso-8859-1 Hey... can we get an autographed copy at IDUG ?
Robert N. MacLeod DBA - ICBC
-----Original Message----- From: cmullins [mailto:CMullins@COMPUSERVE.COM] Sent: Friday, March 10, 2000 2:25 PM To: DB2-L@RYCI.COM Subject: DB2 Developer's Guide, 4th edition - Coming in May!
Just a quick e-mail to let everyone on DB2-L know that an updated version of my book, DB2 Developer's Guide (4th edition) will be published by SAMS in May 2000. The book is fully updated to cover DB2 through Version 6. [...]
18334 53 29_OS390 SQR & Process Scheduler13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM31_Fri, 10 Mar 2000 11:07:55 -0600367_iso-8859-1 Hi,
Before I start ... I am relatively new to PeopleSoft and do not know the process scheduler in detail. I have followed the JCL trail to gain knowledge.
Question: Where do sites place their in-house developed SQRs which will be executed in the OS390 environment via Process Scheduler initiated from the client Process Scheduler Panel? [...]
18388 45 45_Accessing Oracle (HP-UX) from OS/390 - Repeat22_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM31_Fri, 10 Mar 2000 16:34:26 -0800563_- Folks, Sorry to be a pest, I did not receive the number responses to any conclusion. If its relevant, can you please take a moment and respond Thanks! Ramesh Bala
> ---------- > From: Ramesh Balasubramanyan > Sent: Thursday, March 09, 2000 10:03 AM > To: 'db2-l@ryci.com' > Subject: Accessing Oracle (HP-UX) from OS/390 > > Hi Folks, > We are an insurance company with a diverse databases. We have a > Policy system running on Oracle/HP-UX and a claims system using DB2 (V5) > OS390 V2.6. We are now faced with a situation where CICS txns and MVS > [...]
18434 150 56_Re: Bufferpools and Async i/o and indexes (DB2 v4 OS390)14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sat, 11 Mar 2000 13:38:25 +1000716_us-ascii Jim,
Dynamic prefetch is as a result of Sequential Detection.
From: Michael Hannan
>From: Jim Leask >Subject: Re: Bufferpools and Async i/o and indexes (DB2 v4 OS390) >To: DB2-L@RYCI.COM > >Michael, > >Thanks, your PS comment is especially useful. > >I also missed the prospect of sequential detection. > >As an aside, can you (or someone else) confirm that number of pages shown >for Dynamic Prefetch (Display Bufferpool - DSNB414I) are due to Sequential >Detection. > >Thanks again to Michael and Sanjeev. >Jim. > > > >-----Original Message----- >From: Michael Hannan [mailto:mhannan@C031.AONE.NET.AU] >Sent: 10 March 2000 15:05 >To: DB2-L@RYCI.COM [...]
18585 82 35_Re: Ways to generate Surrogate Keys14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sat, 11 Mar 2000 14:00:53 +1000618_us-ascii 'sequential generation put a burden on a specific database partition?'
Using partitions historically by some date or month oriented approach can be very good. A 'burden' where inserts are happening all in one partition is a good one (where is the burden? as long as enough pages hit to avoid significant update contention). More chance of finding required pages in the buffer pool and less Sync I/O. Also old partitions with no update don't require reorg. Spreading data evenly amongst all the partitons is generally O.K. for medium large sized tables but not ideal for very large tables. I don't [...]
18668 68 56_Re: Bufferpools and Async i/o and indexes (DB2 v4 OS390)10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Sat, 11 Mar 2000 03:56:17 GMT563_us-ascii Hi Jim, Have u checked the utilization of BP1 ?.If it is perfectly utilized then there is no point moving indexes to BP3 as it contains the tables which are getting updated as well.Ur indexes are not updated very frquently then it is better to let them be at BP1.If u move them to BP3 then there are chances that in ur batch processing (mainly at night) ,ur applications are putting all the sequentially accessed tables and indexes in BP3 and updating them(mostly tables) as well, so there are chances that they also get slowed down if they have to [...]
18737 94 14_Re: RACF - DB213_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Sat, 11 Mar 2000 11:34:46 +0530395_us-ascii hi James !!
This patch comes in which service level ?? 09- 1999 ?? we have applied till 09-1999 . Can you help me as I do'nt have this patch , I feel so . I queried the CSI. !!
Vishy
James Campbell on 03/09/2000 06:45:40 AM
Please respond to DB2 Data Base Discussion List [...]
18832 32 32_Optimizer in DB2 and other RDBMS10_Sanjeev ..24_ssethi@LOT.TATASTEEL.COM29_Sat, 11 Mar 2000 06:25:49 GMT495_us-ascii Hi all, First of all this is not a technical question.It came out in a discussion with one of my colleague and he told me that why DB2 Optimizer is talked about a lot by the people working with DB2 but as far as the other RDBMS is concerned not much of talking is there about the portion of the database which optimizes the SQLs.As i am have not experienced any other RDBMS than DB2 i could only tell that DB2 optimizer is an intelligent device and i don't know about other RDBMS. [...]
18865 46 34_Re: Strange Problem - DB2 Stogroup14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Sat, 11 Mar 2000 17:42:20 +1100535_- Rick,
Since you specifically asked me, I must reply that I have only used volumes('*') since DB2 V4. However, my speculation would be that this is not an area where any development work is happening.
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au
-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:RD8246@MOMAIL.SBC.COM] Sent: Saturday, March 11, 2000 3:12 AM To: DB2-L@RYCI.COM Subject: Re: Strange Problem - DB2 Stogroup [...]
18912 94 14_Re: RACF - DB214_James Campbell29_James.Campbell@HANCORP.COM.AU31_Sat, 11 Mar 2000 18:20:19 +1100412_- Vishy
Since the PTF List is: Release 240 : UW67517 available 00/02/23 (F002 ) Release 260 : UW67518 available 00/02/23 (F002 ) Release 608 : UW67519 available 00/02/23 (F002 ) I guess it'll be a year 2000 service level. (BTW: UW67518 is the one that supercedes UW62861)
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au [...]
19007 48 35_Re: Ways to generate Surrogate Keys14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Sat, 11 Mar 2000 18:30:09 +1100385_- I know you won't be able to use this information now, but you should be aware the DB2 V6 for OS/390 has gained the ability to do this automatically - a column can be created with 'AS IDENTITY' and DB2 will create incremental values on row insertion. (APAR PQ30652/PTF UQ38405 for those interested). It cannot(?) be too long before this is included into DB2 for other platforms. [...]
19056 19 16_Rename data base10_Pat Brazil27_pbrazil@MIDATLANTIC.AAA.COM31_Sat, 11 Mar 2000 13:17:08 -0500329_iso-8859-1 I've got a problem where a DBA created a database with about 400 tablespaces in production with our test naming convention. I know that technically it does not hurt anything, but everytime I see those dataset names out there with the test name, it makes me nervous.
Is there any easy to rename a database? [...]
19076 145 35_Re: who can update production data?16_Milligan, Andrea25_Andrea_Milligan@BCBST.COM31_Sat, 11 Mar 2000 14:12:50 -0500324_- We have fought the same battle with our auditors; however, we have convinced them that since we HAVE that kind of authority anyway -- how else would we be able to do our jobs -- we are in a better position to insure appropriate recoverability of the data in case of problems. Otherwise known as a contingency plan. . [...]
19222 14 40_Re: Accessing Oracle (HP-UX) from OS/39013_John Schluter24_john.schluter@SWIPNET.SE31_Sun, 12 Mar 2000 06:20:48 -0600351_- We're also looking at Datajoiner.
/John Schluter Swedish State Railways Information Systems Division
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
19237 92 21_Re: Stored Procedures13_John Schluter24_john.schluter@SWIPNET.SE31_Sun, 12 Mar 2000 08:10:11 -0600336_- Hi,
We are working on implementing stored procedures for DB2 on OS/390 and have a couple of years experience with them for DB2 on Win NT.
The real short answer to the question is that the principle is the the same but the environment and implementation are very different.
Without going into alot of detail: [...]
19330 240 21_Re: Raw SQL Formatter15_Toine Michielse18_vndobtm@US.IBM.COM31_Sun, 12 Mar 2000 17:24:18 +0000435_us-ascii
Hello Siebert,
I could but need some a little more time than I can spend now. (need to translate some stuff to English to make it understandable...)
Here's the exec that is part of the code for a tool I wrote during the days of V4 (many moons ago). It's formats the SQL statement that is stored in an ISPF table (tb1pack). See the exec code for the layout of this table. (only four fields). [...]
19571 23 40_Re: Accessing Oracle (HP-UX) from OS/39012_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Sun, 12 Mar 2000 11:26:30 -0600425_iso-8859-1 We have purchased datajoiner to provide simultaneous access to DB2, Oracle and SQL Server. We have just proved the concept though, we have not put this into production yet. One test was to have one query running under Qmf for Windows that joined 12 tables. The tables were on db2/390 v5, Oracle Risc, Oracle NT, and Sql Server. Response time was fine. Although we were testing - these were production tables. [...]
19595 193 85_Repost: "An Ideal Match for mySAP.com"...An Interview With Karl-H einz Hess of SAP AG13_Morrill, John12_JohnM@VP.NET31_Sun, 12 Mar 2000 13:01:25 -0700597_iso-8859-1 Greetings!
Below is a posting from Mike Swift's DB2 Interested Parties mailing list. If you would like to join his list, you can e-mail him at dbswift@us.ibm.com. Not all posting to DB2 Interested Parties are re-posted here.
Cheers!
J. Michael Morrill Chief DB2-L List Owner =================================================
Dear DB2 Interested Parties,
SAP recently published an interview with Karl-Heinz Hess of SAP AG in sapinfo.net No. 67, focusing on DB2 becoming SAP's database of choice. From the article's introductory paragraph: [...]
19789 21 48_restore job hung at the Journal (running window)11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Mon, 13 Mar 2000 09:01:44 +0800462_us-ascii Hi,
Our shop is running UDB V5.2 (fix-9074) on NT platform. Last week, when I tried to restore a tablespace, the DB2 abended and the restore job now hung at the Control Central - Journal (running window). It is not executing and the system does not provide me a way to delete it. Reboot the server also did not get rid of the "hung restore job". Could any one tell me how can I remove the job from journal running window. Thanks in advance. [...]
19811 69 32_Re: Hardware compression (again)15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Mon, 6 Mar 2000 18:20:02 -0600619_us-ascii Yes. 25-33% more CPU to touch (decompress) a row from the buffer pool.
As I said, I hate to beat a dead horse but this hardware vendor and I happened into this discussion (somewhat related to DASD compression).
Jim Lewandowski
Richard Yevich wrote: > > Jim, > > Are you saying that there is a 25% to 33% increase in CPU in DB2 for a > single compressed row to be decompressed? Please clarify. > > Regards, > Richard > +===+===+===+===+====+ > ryevich@attglobal.net > > > -----Original Message----- > > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > [...]
19881 67 33_Re: OS390 SQR & Process Scheduler18_Kishore Babu Kolla20_KishoreB@INTERRA.COM31_Mon, 13 Mar 2000 10:21:12 +0530428_iso-8859-1 May I know about SQRs. Thanks , Regards kishore kolla
-----Original Message----- From: Mackey, Glenn [mailto:GMackey@GUIDEMAIL.COM] Sent: Friday, March 10, 2000 10:38 PM To: DB2-L@RYCI.COM Subject: OS390 SQR & Process Scheduler
Hi,
Before I start ... I am relatively new to PeopleSoft and do not know the process scheduler in detail. I have followed the JCL trail to gain knowledge. [...]
19949 62 18_SMF product naming15_Jim Lewandowski22_jlewand@STARNETINC.COM30_Mon, 6 Mar 2000 19:33:49 -0600383_us-ascii Sorry, I haven't gotten back in the last week (house renovation project is eating my life up something serious).
Here are the finals of the SMF 42 product naming contest (you need not be present to win or accept).
1st place
Kurt Veitengruber DB2IOSPY
Modifying this concept, the official product name will be:
DB2 SMF CACHE + I/O SPY [...]
20012 59 32_Re: Hardware compression (again)16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Sun, 12 Mar 2000 23:13:03 -0500497_iso-8859-1 Jim,
I would dearly love to see where that DASD vendor got those stats from, since even with a Hauptman editproc for DB2 page compression (all software), it is barely above 7% extra CPU overhead (same with the older vendor supplied compression products). Even software support of DB2 compression does not approach anything near that, and CPU compression with the DB2 assist microcode hardware, is generally less than 1% additional CPU overhead. Sorry Jim, but that is fact. [...]
20072 32 8_CLI0150E14_Denzil Coalter26_denzil.coalter@UNICIBLE.CH31_Mon, 13 Mar 2000 08:34:37 +0100516_us-ascii Has anyone had a similar problem trying to execute a utility from the NT Control Center after installing 390 Enablement for Management Tools Package (FMID JDB551D).
For example: REPORT utility for any tablespace in DSNDB06
Message : IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 I have found more information in the file C:\SQLLIB\DB2\db2dbamr.prf which contains the following text : DUMMY_PER_APPLICATION_KEY_RECORD but have been unable to find a reference in any manual. [...]
20105 171 24_Re: LOG YES on Utilities0_18_mebert@AMADEUS.NET31_Mon, 13 Mar 2000 09:33:49 +0100447_us-ascii Hi Tim,
just did a quick scan of your mail, and a response as quick (I'm about to leave for a training for this week, so sorry if I'm a bit hurried and not to the point): I too would prefer something like a "before batch" QUIESCE and "after batch" IC. More than half of the entries in our SYSCOPY are QUIESCEs, which annoys me somewhat, as I try to keep SYSCOPY size under control (cf. previous thread on MODIFY performance). [...]
20277 53 32_Re: Hardware compression (again)14_Massimo Scarpa16_mscarpa@CESVE.IT31_Mon, 13 Mar 2000 10:10:41 +0100402_us-ascii I agree with Richard Yevich's opinion.
We tested a lot many years ago and harware compression (for DB2 rows, sequential
& VSAM files) is better, much better than software compression.
We knew that 'hardware' compression instruction is a low-level sofware instruction but not 'full'
hardware (we had many good articles describing harware compression features). [...]
20331 22 0_14_Rajesh Shewani22_rajeshs@DOMINOMAIL.COM29_Mon, 13 Mar 2000 10:54:00 PST401_iso-8859-1 Hi,
I'am aware that there is a RAM calculation formula with which we can determine the RAM required.There are a number of factors such as database size.etc.
I'd like to know the exact formula,if anyone can help me,it'll be great.
we're working on DB2 UDB 6.1 on Windows NT
Rajesh Shewani Free Webmail for Lotus Notes Professionals http://www.dominomail.com [...]
20354 85 32_Re: Hardware compression (again)14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 13 Mar 2000 08:41:27 -0500569_ISO-8859-1 Jim, I have to challange this number. If the overhead was that high nobody could afford to use it. Benchmarks are not something that can be easily run. Fo real accuracy yo need a completely stand-alone system and the ability to run & drive the identical heavy workload both before and after compression. A few runs of a small batch job does not provide benchmark quality results. Benchmark quality results would not allow for a variance of 8%. The overhead must also be measured at the "total processor busy rate" not just the application class 2 time. [...]
20440 37 23_QMF - VARIABLE DSQAITMN25_CAPLIN Philippe (UA 1101)32_philippe.caplin@BANQUE-FRANCE.FR31_Mon, 13 Mar 2000 14:58:13 +0100313_- Hi ! I wish to recover in a QMF report the name of the origin Query. While
using the variable DSQAITMN, I succeeded only to obtain the name of
the Procedure as well as the one of the Form. How can I do ? I know that a join with QMF tables could be used, but i'm looking for something easier! [...]
20478 477 24_Re: LOG YES on Utilities0_23_Mike_Levine@TEKHELP.NET31_Mon, 13 Mar 2000 08:58:51 -0500594_us-ascii Dr. Michael Ebert,
The purpose of the QUIESCE before the REORG LOG NO (or YES) is to establish an application-wide point of consistency. We stop the database, start for UT ONLY and QUIESCE a single known tablespace. I did try resetting COPY PENDING with a SHRLEVEL CHANGE image copy (DB2 V5.1) and it worked. However, the tablespaces are still unavailable for update (due to COPY PENDING) until the image copy completes, so in effect it is still a SHRLEVE REFERENCE image copy (I tried this also). BTW... you cannot reset COPY PENDING with just a QUIESCE. The purpose of a [...]
20956 83 32_Re: Hardware compression (again)14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Tue, 14 Mar 2000 01:04:10 +1000584_us-ascii Jim,
25-33% more than what? I presume you mean more than normal cost of passing row to apply stage 1 predicates which is only a small part of total cost of processing qualifying rows? or is it 25-33% more than something else?
From: Michael Hannan
At 06:20 PM 6/3/00 -0600, you wrote: >Yes. 25-33% more CPU to touch (decompress) a row from the buffer pool. > >As I said, I hate to beat a dead horse but this hardware vendor and I >happened into this discussion (somewhat related to DASD compression). > >Jim Lewandowski > > >Richard Yevich wrote: >> [...]
21040 55 19_Index won't rebuild0_19_mike.holmans@BT.COM31_Mon, 13 Mar 2000 14:03:34 -0000438_- DB2 V5, OS/390 1.2
We've got an index which keeps going into RECP status.
REBUILD INDEX executes satisfactorily, as far as I can see:
DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = CHIDGK1R
DSNU050I DSNUGUTC - REBUILD INDEX(FSTT3.ITPE01)
DSNU555I ? DSNUCRUL - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS PROCESSED=1089293 DSNU705I DSNUCRIX - UNLOAD PHASE COMPLETE - ELAPSED TIME=00:00:19 [...]
21096 85 23_Re: Index won't rebuild13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Mon, 13 Mar 2000 19:40:05 +0530480_us-ascii Is it that even erpair is not solving the problem ?? vishy
mike.holmans@BT.COM on 03/13/2000 07:33:34 PM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: Viswanathan N/LTITL)
Subject: Index won't rebuild
Note: Some recipients have been dropped due to syntax errors. Please refer to the "$AdditionalHeaders" item for the complete headers. [...]
21182 575 24_Re: LOG YES on Utilities16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM31_Mon, 13 Mar 2000 07:56:34 -0800352_iso-8859-1 I believe that the point about not needing a QUIESCE before a REORG is due to the fact that the REORG puts a START_RBA in SYSCOPY before the RELOAD phase, so a QUIESCE prior to that doesn't add any recoverability.
Tony Provenzola BEST Consulting, Inc. Phone * (503) 532-0772 Fax * (503) 532-1041 Email * Tony.Provenzola@nike.com [...]
21758 46 33_Why does this package bind fail ?13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Mon, 13 Mar 2000 16:41:38 +0000373_us-ascii I've set up a database DB1, with some tables and views qualified with QUAL1.
I've set up a generic qualifier to created plans and packages QUALGEN.
I've given DBADM authority on the database, and use of storage group, to QUALGEN.
I can issue the following SQL from SPUFI -
SET CURRENT SQLID = 'QUALGEN'; SELECT * FROM QUAL1.VIEW1; [...]
21805 33 18_Partition or not??21_Soja, Matthew J WDC3122_Matthew.Soja@CIGNA.COM31_Mon, 13 Mar 2000 11:54:14 -0500591_- We are having a discussion here debating the merits of actually partitioning a tablespace (only one table, about 800 cylinders in size, and 7 indexes) in order to spread IO to multiple packs or just allocate multiple datasets to force this spread. This active tablespace is getting hit sporadically with 40+ IOs per second which is causing problems with DASD response times. One thought is to partition the tablespace into 3 or 4 parts and place each part on a separate DASD volume. The other recommendation is to use an allocation trick to force the creation of multiple datasets on [...]
21839 90 37_Re: Why does this package bind fail ?16_Michael McCarthy18_mmccarthy@DTCC.COM31_Mon, 13 Mar 2000 12:16:37 -0500507_us-ascii ---------------------- Forwarded by Michael McCarthy/DTC on 03/13/2000 12:10 PM ---------------------------
Michael McCarthy 03/13/2000 12:15 PM
To: Philip Nelson cc: Subject: Re: [DB2-L] Why does this package bind fail ? (Document link not converted)
Phil,
You need to do the following:
SET CURRENT SQLLID = 'QUALGEN'; GRANT BINDAGENT TO TBDB1DEV;
Now the user TBDB1DEV will be able to run the job that does the bind. [...]
21930 43 22_Re: Partition or not??9_Rob Crane22_racrane@CONCENTRIC.NET31_Mon, 13 Mar 2000 10:24:39 -0700622_us-ascii I would go with partitioning. You can gain more benefit with partitions in the utility area, and with the new partition scanning capabilities of the optimizer your SQL should benefit as well. Partitioning gives you much more functionality and flexibility.
"Soja, Matthew J WDC31" wrote: > > We are having a discussion here debating the merits of actually partitioning > a tablespace (only one table, about 800 cylinders in size, and 7 indexes) in > order to spread IO to multiple packs or just allocate multiple datasets to > force this spread. This active tablespace is getting hit sporadically with [...]
21974 39 24_Re: LOG YES on Utilities0_19_Tim.Lowe@STPAUL.COM31_Mon, 13 Mar 2000 11:15:48 -0600404_us-ascii Michael Levine, I agree with you that the evidence that a COPY Sharelevel change works when a tablespace is copy pending may be interesting, but has very little practical use. Furthermore, it would seem to me that anyone that depends on something that the manual says will not work might be "surprised" in the future if the "bug" is fixed and the information in the manual is made correct. [...]
22014 38 20_Re: Rename data base13_Thomas, Janis19_Janis.Thomas@CA.COM31_Mon, 13 Mar 2000 12:20:08 -0500369_iso-8859-1 If you had a third party product, like CA's RC/Migrator or BMC's Change Manager, the product would handle all the changes to the dependent objects that would be necessary to rename the database: all objects would be recreated under the new database, all tables would be unloaded/reloaded, table security would be recreated, plans would be rebound, etc. [...]
22053 75 22_Re: Partition or not??20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Mon, 13 Mar 2000 12:23:00 -0500516_us-ascii 800 Cylinders is not that much big a table. However, if you have I/O contention than partitioning would help.
If you have lots of delete/insert activities and VARCHAR columns in the table then segmented tablespaces are usually the best because of 4 bits per page on the spacemap.
Partitioned tablespace are good for parallel I/O . You could spread the datasets to different volumes. But 7 indexes on the this table would stop you short of taking full advantage of partitioned tablespace. [...]
22129 67 23_Re: Index won't rebuild13_Leo Conchello18_lconche@USWEST.COM31_Mon, 13 Mar 2000 10:12:22 -0700635_iso-8859-2 Have you looked for any messages in DB2mstr that may help determine the problem?
Regards,
Leo
mike.holmans@BT.COM wrote:
> DB2 V5, OS/390 1.2 > > We've got an index which keeps going into RECP status. > > REBUILD INDEX executes satisfactorily, as far as I can see: > > DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = CHIDGK1R > > DSNU050I DSNUGUTC - REBUILD INDEX(FSTT3.ITPE01) > > DSNU555I ? DSNUCRUL - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS > PROCESSED=1089293 > DSNU705I DSNUCRIX - UNLOAD PHASE COMPLETE - ELAPSED TIME=00:00:19 > > DSNU042I DSNUGSOR - SORT PHASE STATISTICS - [...]
22197 92 22_Re: Partition or not??20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Mon, 13 Mar 2000 12:29:00 -0500580_us-ascii Also DB2 V5 with APAR (or V6) lets you update the partitioning key if the table has been created after the APAR. This removes the biggest restriction which would have stopped you from choosing your favorite clustering index to be a partitioning index.
> -----Original Message----- > From: Pillay, Venkat (PCA) > Sent: Monday, March 13, 2000 12:23 PM > To: 'DB2 Data Base Discussion List' > Subject: RE: Partition or not?? > > 800 Cylinders is not that much big a table. However, if you have I/O > contention than partitioning would help. > > If you have lots [...]
22290 81 23_Re: Index won't rebuild10_Leo Flores23_leoflores@EARTHLINK.NET31_Mon, 13 Mar 2000 10:26:04 -0800606_us-ascii Mike,
The ITPE01 is the index-name(internal name to DB2). The ITPE1OCY1 is the indexspace-name(external name to DB2, part of the MVS dataset name).
When you create an index DB2 will use that index-name to build a UNIQUE 8 character indexspace-name.
You should have a Naming Convention and Standards so that you are in control of what DB2 will end up using as a tablespace's space-name or index's indexspace-name and so that you can determine, with a very good degree of accuracy, that an object being DISPLAYED is a tablespace or an index and also if an MVS dataset is a [...]
22372 391 24_Re: LOG YES on Utilities19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Mon, 13 Mar 2000 12:30:37 -0600397_ISO-8859-1 Hi Mike, I think one of the major benefits of QUIESCE is to set up for a point-in-time recovery by QUIESCEing a TABLESPACESET after IC. In this way, I can roll off a bad batch run to the QUIESCE point set just prior to the batch run. This implies other steps were taken to ensure the batch process was the only application that could UPDATE, DELETE, or INSERT the tables involved. [...]
22764 117 22_Re: Partition or not??29_=?iso-8859-1?Q?Jos=E9_Moura?=25_jose.antonio.moura@CGD.PT31_Mon, 13 Mar 2000 18:35:51 -0000399_us-ascii Hi, I agree with what Venkat says, but I think that the problem of concurrency on the non partitioned indexes could be bypassed with their logical partitioning through the use of PIECESIZE (DB2 Version 5) dimension. With that parameter you can work with the same (more, less, as you wish...) number of logical partitions in NPIs as you have true partitions in your partitioning index. [...]
22882 24 22_Deadlock on an insert?0_20_John_Lendman@FPL.COM31_Mon, 13 Mar 2000 13:48:31 -0500374_us-ascii DB2V5.1
I have a system that's inserts records into tableA. I can run many copies of this same program inserting records into tableA in order to speed up the process, each insert has a unique key. I have row level locking in the tablespace. I get a deadlock while running many process. The deadlock always shows the tableA as the table in the deadlock. [...]
22907 43 29_Deadlock on an insert? -Reply11_Jeff Schade15_JSCHADE@PCH.COM31_Mon, 13 Mar 2000 13:58:39 -0500551_- Check to see if you have any lock escalation occuring.
Jeff Schade Publishers Clearing House 516-944-2673 jschade@pch.com
>>> 03/13/00 01:48pm >>> DB2V5.1
I have a system that's inserts records into tableA. I can run many copies of this same program inserting records into tableA in order to speed up the process, each insert has a unique key. I have row level locking in the tablespace. I get a deadlock while running many process. The deadlock always shows the tableA as the table in the deadlock. [...]
22951 65 12_DB2 UDB LOAD7_Gustavo26_lozano@BANDEIRANTES.COM.BR31_Mon, 13 Mar 2000 16:22:48 -0300360_iso-8859-1 Hi folks!
We wish to load data on UDB (Win/NT) tables using datasets generated by DSNTIAUL (DB2/OS390). The tables have exactly the same lay-out. Is there a way to do that? Note: we do not use QMF to generate IXF format files. Thank you very much.
Gustavo Lozano (lozano@bandeirantes.com.br) - DBA Banco Bandeirantes Sao Paulo - Brazil
23017 26 55_Help jog my memory - OS/390 5.1 maint migration problem14_Roy R Reynolds20_Roy_Reynolds@GAP.COM31_Mon, 13 Mar 2000 11:27:03 -0800504_us-ascii I seem to remember running into this a few months ago but can't recall how I solved it.
After bringing up V5.1 with new (PUT9909) maintenance (OS/390 1.3), and before running CATMAINT, I ran a simple SPUFI that got a -206 when I queried SYSIBM.SYSTABLES, naming each column. Then I tried the query using an asterisk. (SELECT * FROM SYSIBM.SYSTABLES). It worked fine. I repeated the queries using DSNTEP2. Same result. Obviously, I can't allow this situation to exist. Backed it out. [...]
23044 74 35_Re: who can update production data?10_Feeny, Jim23_Jim_Feeny@COMPUWARE.COM31_Mon, 13 Mar 2000 14:55:44 -0500459_iso-8859-1 Hi:
The Audit Trail feature of File-AID for DB2 might be a good fit here. It can be turned on or off via DBA control and stores the information it collects in the tamper-proof SMF records.
Jim Feeny DB2 Product Manager Compuware Corporation
-----Original Message----- From: Don Alden [mailto:Don.Alden@PKSIS.LEVEL3.COM] Sent: March 10, 2000 10:55 AM To: DB2-L@RYCI.COM Subject: Re: who can update production data? [...]
23119 64 33_Re: Deadlock on an insert? -Reply0_21_FNovak@SCHOLASTIC.COM31_Mon, 13 Mar 2000 15:29:33 -0500328_- If you do inserts into a table and the row size is less than the page size and the target page is the same, wouldn't DB2 escalate the record lock to a page lock thus causing a deadlock pending the order of access?
Frank Novak Scholastic, Inc. Ph. (201) 372-2475 Beeper: (917) 380-9126 e-mail FNovak@scholastic.com [...]
23184 87 33_Re: Deadlock on an insert? -Reply0_20_John_Lendman@FPL.COM31_Mon, 13 Mar 2000 15:35:29 -0500627_us-ascii Lock escalation is turned off in this system. It is SAP and that is what is recommended. So I don't think the lock is getting escalated. John
FNovak@SCHOLASTIC.COM on 03/13/2000 03:29:33 PM
Please respond to "DB2 Data Base Discussion List"
To: DB2-L@RYCI.COM cc: Subject: Re: Deadlock on an insert? -Reply
If you do inserts into a table and the row size is less than the page size and the target page is the same, wouldn't DB2 escalate the record lock to a page lock thus causing a deadlock pending the order of access? [...]
23272 113 33_Re: Deadlock on an insert? -Reply20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Mon, 13 Mar 2000 15:43:54 -0500366_us-ascii What is the key ? Is it timestamp ? Is attempt of duplicates insert possible during concurrent inserts ?
DB2 current timestamp can have duplicates possible for concurrent applications.
It is a deadlock (00C90088) and not time-out (00C9008E) ? (just confirming) The DL/I reports different reason code (I guess SAP does not have IMS calls) [...]
23386 133 33_Re: Deadlock on an insert? -Reply0_20_John_Lendman@FPL.COM31_Mon, 13 Mar 2000 15:50:06 -0500481_us-ascii The key is a seq. number IE 0000000001, 000000002 ect. It is a 00C0088 deadlock.
"Pillay, Venkat (PCA)" on 03/13/2000 03:43:54 PM
Please respond to "DB2 Data Base Discussion List"
To: DB2-L@RYCI.COM cc: Subject: Re: Deadlock on an insert? -Reply
What is the key ? Is it timestamp ? Is attempt of duplicates insert possible during concurrent inserts ? [...]
23520 143 33_Re: Deadlock on an insert? -Reply0_25_joseph.burns@HIGHMARK.COM31_Mon, 13 Mar 2000 15:53:32 -0500328_us-ascii You should be able to verify lock escalation through monitoring.
However, from the situation you described, I would also look for possible -803 conditions (dup key). If an insert gets a -803 due to a key that has been inserted by another process but not yet committed, then the -803 process begins to wait. [...]
23664 164 33_Re: Deadlock on an insert? -Reply20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Mon, 13 Mar 2000 15:57:58 -0500305_iso-8859-1 How is the seq number generated ? UK, Swiss or Australian way, I mean
1.- Some application do select latest seq no from first table, insert into second table and go back and update seq no up by 1 on first table. 2.- Some application update the seq no by adding 1 , and then insert. [...]
23829 167 33_Re: Deadlock on an insert? -Reply0_21_FNovak@SCHOLASTIC.COM31_Mon, 13 Mar 2000 16:06:58 -0500298_iso-8859-1 Being new to DB2 I would still bet on you record lock is being escalated to a page lock under the covers, especially being that the keys is sequentially assigned and if it is part of a primary key. I would get the number is a separate transaction and pass it to update transaction. [...]
23997 189 33_Re: Deadlock on an insert? -Reply20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Mon, 13 Mar 2000 16:04:41 -0500613_iso-8859-1 Nope. The row level locks are never escalated to page. They either escalate to table(segmented) or tablespace(non-segmented).
> -----Original Message----- > From: FNovak@SCHOLASTIC.COM [SMTP:FNovak@SCHOLASTIC.COM] > Sent: Monday, March 13, 2000 4:07 PM > To: DB2-L@RYCI.COM > Subject: Re: Deadlock on an insert? -Reply > > Being new to DB2 I would still bet on you record lock is being escalated > to > a page lock under the covers, especially being that the keys is > sequentially assigned and if it is part of a primary key. I would get the > number is a separate transaction and pass it [...]
24187 29 42_Date/Timestamp Formats on MVS DB2 V5 Loads16_Galluzzo, Lori A21_lori.galluzzo@EDS.COM31_Mon, 13 Mar 2000 16:48:12 -0500410_iso-8859-1 Dear List, We currently have load files in a character format for the dates of YYYYMMDD. We tried to load with DATE EXTERNAL(8), but was unsuccessful. The LOCAL DATA LENGTH is set to 10 for our installation. Is there a way to load this data with the 8 character format into a field defined as DATE? We also have this same issue with the TIMESTAMP of YYYYMMDDHHMMSSNNNNNN (again no -'s or .'s). [...]
24217 76 26_Re: Deadlock on an insert?0_19_Tim.Lowe@STPAUL.COM31_Mon, 13 Mar 2000 16:14:25 -0600561_us-ascii John, Is it possible that the 2 processes that are contending with each other are attempting to insert the same value? (An insert of a duplicate could get a deadlock or timeout if the same key value is currently inserted by another process which has not release its lock.) Are you getting a deadlock, or a timeout? (What is the message as formatted by DSNTIAR after you get the bad sqlcode, including the reason code, the resource type and the resource name?) Are there any related messages in the DB2 log? (a DSNT501I and/or a DSNT375I message?) [...]
24294 41 46_Re: Date/Timestamp Formats on MVS DB2 V5 Loads9_Birk, Tim17_BirkT@DIEBOLD.COM31_Mon, 13 Mar 2000 17:35:31 -0500634_iso-8859-1 If you have BMC's LoadPlus, it can handle those formats.
-----Original Message----- From: Galluzzo, Lori A [mailto:lori.galluzzo@EDS.COM] Sent: Monday, March 13, 2000 4:48 PM To: DB2-L@RYCI.COM Subject: Date/Timestamp Formats on MVS DB2 V5 Loads
Dear List, We currently have load files in a character format for the dates of YYYYMMDD. We tried to load with DATE EXTERNAL(8), but was unsuccessful. The LOCAL DATA LENGTH is set to 10 for our installation. Is there a way to load this data with the 8 character format into a field defined as DATE? We also have this same issue with the TIMESTAMP of [...]
24336 17 59_Re: Help jog my memory - OS/390 5.1 maint migration problem11_Joe Bitetto31_joseph.bitetto@US.PWCGLOBAL.COM31_Mon, 13 Mar 2000 16:42:35 -0600303_- I had a similar problem when I migrated to V5.1 (PUT9906). You may want to check any OEM product installed in your shop that interfaces with DB2. In my case we have a product that was re-linked with DB2 module DSNXGRDS. IBM support supplied me with a series of informational APARS that may help. [...]
24354 49 26_Links about DB2 for OS/3906_DB2usa18_db2usa@HOTMAIL.COM31_Mon, 13 Mar 2000 18:46:34 -0600623_- Hi,
Last update on Sunday, March 12th 2000 ======================================
- Links about DB2 for OS/390: http://www.multimania.com/db2usa/eliendb2.htm DB2 Today, IBM's DB2 newsletter: March 2000.
- Technical papers: http://www.multimania.com/db2usa/earticle.htm Spring issue of DB2 Mag is available on line: The Trigger Effect by Willie Favero, DB2 Mag, Spring 2000. Introduction Object-Relational OS/390 by S. Lawson and R. Yevich, DB2 Mag, Spring 2000. Much Ado about Nulls by Bonnie Baker, DB2 Mag, Spring 2000. Time to Reconsider SMS by Robert Caterall, DB2 Mag, Spring 2000. [...]
24404 13 25_Possible trivia question?14_Penrod, Dale E19_dale.penrod@EDS.COM31_Mon, 13 Mar 2000 18:56:36 -0600362_- Can anyone share with me, the significance of the low-level qualifier I0001.Accc in the DB2 Dataset Naming Convention?
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
24418 217 33_Re: Deadlock on an insert? -Reply11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Tue, 14 Mar 2000 09:03:43 +0800518_us-ascii Are u on type 1 index or type 2 index?
"Pillay, Venkat (PCA)" on 14/03/2000 05:04:41 AM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: ROLAND CHUA CHONG KEE/DATACEN/IT/SGX) Subject: Re: Deadlock on an insert? -Reply
Nope. The row level locks are never escalated to page. They either escalate to table(segmented) or tablespace(non-segmented). [...]
24636 19 51_Switch to large tablespace - anything to watch for?9_Paul Yuen16_pyuen@GIO.COM.AU31_Tue, 14 Mar 2000 16:42:32 +1000495_us-ascii Dear List, One of the partition of our large table has reached the 2G limit, aside from re-partitioning, we're considering using large tablespace, could anyone share their experience in using large tablespaces and point out any gotcha? TIA Paul
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
24656 165 33_Re: Deadlock on an insert? -Reply22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Tue, 14 Mar 2000 05:16:34 +0000338_ISO-8859-1 John which tables is this occuring on in SAP - home or supplied?
we were getting a lot of these ..
Prior to V6 of DB2 you need to manually change the Stats in the catalog (forget the OSS note number) ... but with V6 you get to force the optimizers hand by the use of a handy ZPARM option to do this for you. [...]
24822 33 55_Re: Switch to large tablespace - anything to watch for?14_Massimo Scarpa16_mscarpa@CESVE.IT31_Tue, 14 Mar 2000 09:06:48 +0100371_us-ascii We converted a normal 64 partition tablespace to a large tablespace (100 partitions) and all
works fine. We made many SQL tuning and we enjoy the new features of REORG as REORG
DISCARD and REORG UNLOAD EXTERNAL to delete and unload data by partition faster.
The problem is the presence of NPI. In this case using PIECESIZE helps a lot. [...]
24856 69 32_Re: Converting to Type 2 Indexes12_Nurmi, Seppo23_Seppo.Nurmi@RIKSBANK.SE31_Tue, 14 Mar 2000 11:07:06 +0100422_iso-8859-1 The book has right, that was what I saw about one year ago when I converted all our type 1 indexes to type 2. But in most cases, I guess, automatic rebind is trigged so users seldom realize what happends. That is, if you did run recover index, otherwise the index would be recovery bending and unavailable. Automatic rebind will fail in certain cases, so I would run rebinds too (that's how I did). /Seppo [...]
24926 58 49_Re: Why does this package bind fail ? (CORRECTED)13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Tue, 14 Mar 2000 10:35:47 +0000328_us-ascii If you want the right answer you should ask the right question - in changing the question from our own names to generic names I missed one, and the question took on a whole new meaning. TDB1DEV should have been QUALGEN !!!
So here is the question again - rewritten to ask what I actually wanted to know !!! [...]
24985 87 49_Re: Why does this package bind fail ? (CORRECTED)20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM31_Tue, 14 Mar 2000 11:18:56 -0000398_iso-8859-1 Philip,
Is QUALGEN either the primary or one of the secondary authorisation ids for the BIND process? If it isn't then QUALGEN needs to grant BINDAGENT to one or other of those authorisation ids. Note the difference between this and what you did - you granted BINDAGENT to QUALGEN from a SYSADM id, which would allow QUALGEN to perform BINDS quoting that SYSADM id as OWNER. [...]
25073 44 45_00C9009C abend on NPI when LOADing RESUME YES12_Ian Sinclair30_ian_sinclair@CAPITALBANK.CO.UK31_Tue, 14 Mar 2000 05:18:14 -0600355_- Hi Has anyone any ideas on this one or has experienced anything similar?
We are getting the following abend intermittently, ostensibly due to an attempt to insert into a full logical partition of an NPI, when running a LOAD RESUME YES under DB2 V4 into a partitioned tablespace. RELOAD phase completes ok, but then the BUILD phase fails.... [...]
25118 47 40_Re: Accessing Oracle (HP-UX) from OS/39013_Lynne Flatley17_LFlatley@NEFN.COM31_Tue, 14 Mar 2000 07:07:42 -0500611_us-ascii We're using DataJoiner to replicate from Oracle into DB2 (and then we're using Sybase replication to replicate the data into SQL Server).
> -----Original Message----- > From: Ramesh Balasubramanyan [SMTP:RAMBAL@SAIF.COM] > Sent: Thursday, March 09, 2000 1:03 PM > To: DB2-L@RYCI.COM > Subject: Accessing Oracle (HP-UX) from OS/390 > > Hi Folks, > We are an insurance company with a diverse databases. We have a > Policy system running on Oracle/HP-UX and a claims system using DB2 (V5) > OS390 V2.6. We are now faced with a situation where CICS txns and MVS > Batch pgms need to access the [...]
25166 88 16_FW: DB2 UDB LOAD17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM31_Tue, 14 Mar 2000 07:43:22 -0500615_- Here's an example of what I did to unload tables from DB2/OS390 via DSNTIAUL to DB2/UNIX (Sun Solaris). The key is to unload all columns in external format before downloading.
Sample OS/390 unload command using DSNTIAUL to format (all external character data) for UNIX loading SELECT DIGITS(INT_ID) , PLN_NUM , PLN_SYSTEM_CD , PLN_COMPANY_CD , CON_NUM , CON_SYSTEM_CD , CON_COMPANY_CD , CUS_SSN_TIN_NUM , INT_RQST_TYPE , DIGITS(INT_RQST_CUS_ID) , CHAR(INT_RPT_PER_BEG_DT) , CHAR(INT_RPT_PER_END_DT) , INT_INTERCEPT_LOC , INT_INTERNET_POST , SUBSTR('END-OF-RECORD',1,13) FROM GUSRT00D.TINT001 WITH UR; [...]
25255 22 16_DEGREE in BIND ?10_ajay kumar19_db2v5r1@HOTMAIL.COM29_Tue, 14 Mar 2000 18:24:55 IST532_- Hi
We are declared all tables with SIMPLE TABLESPACE, Is there any better performance to specify DEGREE(ANY) in BIND parm ?
thanks in advance karthik
______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
25278 97 33_Re: Deadlock on an insert? -Reply19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 14 Mar 2000 07:43:27 -0600588_ISO-8859-1 John, A couple more suggestions. To ensure Lock Escalation is really turned off check in two places. 1) In DSNZPARM, DSN6SPRM NUMLKTS = 0. This sets LOCKMAX(SYSTEM) to zero and turns off lock escalation. 2) The TABLESPACEs involved have LOCKMAX(0). To ensure you always have a unique key, use a separate table to keep track of your key. In Psuedo-Code below, KEYTABLE is a one row table containing KEY (no index either). This method might skip a key every once in a while because a subsequent insert may fail after you've gotten a key. This isn't fastest method; just one [...]
25376 100 32_Re: Converting to Type 2 Indexes14_Bistricer, Avi20_BistricerA@CONED.COM31_Tue, 14 Mar 2000 09:02:11 -0500364_- The book said that plans will become invalid when you convert from type 2 to type 1. I did not notice it on first reading. Thanks All Avi Bistricer, DBA 212.460.6695
-----Original Message----- From: Nurmi, Seppo [SMTP:Seppo.Nurmi@RIKSBANK.SE] Sent: Tuesday, March 14, 2000 5:07 AM To: DB2-L@RYCI.COM Subject: Re: Converting to Type 2 Indexes [...]
25477 122 33_Re: Deadlock on an insert? -Reply0_20_John_Lendman@FPL.COM31_Tue, 14 Mar 2000 09:21:51 -0500404_us-ascii I double check the DSNZPARM NUMLKTS and it is 0 plus the LOCKMAX for the tablespace is 0. So I am sure that I am not getting lock escalation.
I am still pursuing the possibility of dup keys on insert. John
"DAVIS, RICK (SBCSI)" on 03/14/2000 08:43:27 AM
Please respond to "DB2 Data Base Discussion List" [...]
25600 56 55_Re: Switch to large tablespace - anything to watch for?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 14 Mar 2000 09:21:04 -0500508_us-ascii If you are on V5 then LARGE tablespace is the choice. I had very good experience with them except:
What will you do when you touch 4GB limit ? In V6 DSSIZE is new parameter, which along with OS/390 version 2.7 and DFSMS 1.2 can give you capability to expand up to 64 GB per partition using EA enabled datasets. This takes your tablespace to 16 Terabyte limit. The LARGE tablespace can't grow beyond 4 GB and you have to drop and recreate them in future if you want them to grow further. [...]
25657 38 20_Re: DEGREE in BIND ?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 14 Mar 2000 09:35:39 -0500689_us-ascii DEGREE(ANY) can take into effect only when at least on of the table is partitioned (till V5). In version 6, segmented tablespaces can also participate in parallelism.
> -----Original Message----- > From: ajay kumar [SMTP:db2v5r1@HOTMAIL.COM] > Sent: Tuesday, March 14, 2000 1:25 PM > To: DB2-L@RYCI.COM > Subject: DEGREE in BIND ? > > Hi > > We are declared all tables with SIMPLE TABLESPACE, Is there any better > performance to specify DEGREE(ANY) in BIND parm ? > > thanks in advance > karthik > > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > > ================================================ [...]
25696 13 30_Re: Start procedure automation12_Fertaki Gina20_GFertaki@EUROBANK.GR31_Tue, 14 Mar 2000 16:26:59 +0200297_iso-8859-1 Thank you for all your answers. Gina Fertaki
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
25710 71 19_DSNREXX And Updates9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Tue, 14 Mar 2000 15:10:11 +0000599_us-ascii Hi, Im testing out the REXX/DB2 interface, DSNREXX. Has anybody got any examples of carrying out UPDATEs ?
I am using a straight copy of the SQL shown on page 13 of the REXX Language Support guide. Code used is shown below.....
/* rexx */
ssid = 'DB2C' address tso "SUBCOM DSNREXX" /* Host cmd environment available ? */ if rc then /* No. Then make it available */ s_rc = rxsubcom('ADD','DSNREXX','DSNREXX') /* Add to DSNREXX to */ /* command table */ address dsnrexx /* Set DSNREXX as default command envnt */ "CONNECT" ssid /* Connect to DB2 */ "EXECSQL [...]
25782 91 23_Re: DSNREXX And Updates20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 14 Mar 2000 10:19:20 -0500598_us-ascii Use could use direct update statement: "EXECSQL "|| "UPDATE DSN8410.EMP " ||, "SET MIDINIT = "||rexx_variable||" " ||, "WHERE EMPNO = '000200'"
> -----Original Message----- > From: Andy Hunt [SMTP:Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK] > Sent: Tuesday, March 14, 2000 10:10 AM > To: DB2-L@RYCI.COM > Subject: DSNREXX And Updates > > Hi, > Im testing out the REXX/DB2 interface, DSNREXX. Has anybody got any > examples of > carrying out UPDATEs ? > > I am using a straight copy of the SQL shown on page 13 of the REXX > Language > Support guide. > Code used is shown below..... > > [...]
25874 110 23_Re: DSNREXX And Updates13_Scott Goodell22_Scott.Goodell@WCOM.COM31_Tue, 14 Mar 2000 09:26:21 -0600330_us-ascii I have done an Insert the way that you have done the update. I had to DECLARE it in addition to the PREPARE.
SQLSTMT = "INSERT INTO TEST.EMP ", "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) "EXECSQL DECLARE C10 CURSOR FOR S10" "EXECSQL PREPARE S10 FROM :SQLSTMT" "EXECSQL EXECUTE S10 USING :DB, :TS, :PART, " , . . . [...]
25985 135 23_Re: DSNREXX And Updates9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Tue, 14 Mar 2000 15:31:21 +0000431_us-ascii Pillay, Yes, the direct update statement works for this particular SQL statement but I would like to specify host variables (and indicator variables) using a PREPARE (as described in the manual). Thanks, Andy Hunt - Scottish And Southern Energy
"Pillay, Venkat (PCA)" on 14/03/2000 15:19:20
Please respond to DB2 Data Base Discussion List [...]
26121 144 46_Re: Date/Timestamp Formats on MVS DB2 V5 Loads16_Milligan, Andrea25_Andrea_Milligan@BCBST.COM31_Tue, 14 Mar 2000 10:38:35 -0500602_-
Andrea Milligan DataBase Administrator BlueCross BlueShield of Tennessee
> -----Original Message----- > From: Galluzzo, Lori A [SMTP:lori.galluzzo@EDS.COM] > Sent: Monday, March 13, 2000 4:48 PM > To: DB2-L@RYCI.COM > Subject: Date/Timestamp Formats on MVS DB2 V5 Loads > > Dear List, > We currently have load files in a character format for the dates of > YYYYMMDD. > We tried to load with DATE EXTERNAL(8), but was unsuccessful. The LOCAL > DATA > LENGTH is set to 10 for our installation. Is there a way to load this > data > with the > 8 character format into a field [...]
26266 156 23_Re: DSNREXX And Updates20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 14 Mar 2000 10:38:07 -0500642_iso-8859-1 I think you missed DECLARE CURSOR "EXECSQL DECLARE C100 CURSOR FOR S100" before "PREPARE"
Should work !!
> -----Original Message----- > From: Andy Hunt [SMTP:Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK] > Sent: Tuesday, March 14, 2000 10:31 AM > To: DB2-L@RYCI.COM > Subject: Re: DSNREXX And Updates > > Pillay, > Yes, the direct update statement works for this particular SQL statement > but I > would like to specify host variables (and indicator variables) using a > PREPARE > (as described in the manual). > Thanks, > Andy Hunt - Scottish And Southern Energy > > > > > "Pillay, Venkat (PCA)" on [...]
26423 157 46_Re: Date/Timestamp Formats on MVS DB2 V5 Loads16_Milligan, Andrea25_Andrea_Milligan@BCBST.COM31_Tue, 14 Mar 2000 10:45:03 -0500435_- Valid Date, time and timestamp representations are discussed in the SQL reference manual under "String Representation Values of Datetime Values". Unless an exit routine is written and defined to DB2 at install time, one of these valid formats must be used. Usually it would be either ccyy-mm-dd or mm/dd/yyyy for dates. Timestamps are ccyy-mm-dd-00.00.00.000000. There are tables in the manual to show you all possible formats. [...]
26581 129 23_Re: DSNREXX And Updates15_Filip Van Parys24_filip.vanparys@SIDMAR.BE31_Tue, 14 Mar 2000 09:33:21 -0600635_- Hello,
we had the same problem and have reported it to IBM. it seems we always have to DECLARE a cursor, even for non-select queries. IBM says that's the SQL standard they apply ?!?
Filip Van Parys - Sidmar nv Belgium
On Tue, 14 Mar 2000 09:26:21 -0600, Scott Goodell wrote:
>I have done an Insert the way that you have done the update. I had to DECLARE >it in addition to the PREPARE. > >SQLSTMT = "INSERT INTO TEST.EMP ", > "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) >"EXECSQL DECLARE C10 CURSOR FOR S10" >"EXECSQL PREPARE S10 FROM :SQLSTMT" >"EXECSQL EXECUTE S10 USING [...]
26711 176 23_Re: DSNREXX And Updates9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Tue, 14 Mar 2000 15:46:23 +0000422_us-ascii Thanks to everybody who replied..... Sure enough it works if you declare a cursor. Its a shame that the examples did not make this 'standard' clear in the Rexx Language Support manual. Many Thanks, Andy Hunt - Scottish And Southern Energy
Filip Van Parys on 14/03/2000 15:33:21
Please respond to DB2 Data Base Discussion List [...]
26888 62 55_Re: Switch to large tablespace - anything to watch for?10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Tue, 14 Mar 2000 11:02:29 -0600630_iso-8859-1 Paul,
We had the same problem with a couple of partitions reaching the 2G limit. The LARGE tablespace option works as advertised. The only down side is the unload/drop/create/reload which in our case was a tablespace with 270 million rows.
The last time I talked to the CDB salesperson he touted that their reorg product could change the tablespace to LARGE without the unload/drop/create/reload. It was explained that their reorg utility could turn on the LARGE option in the DB2 catalog and then reorg each partition which would rebuild the DB2 objects. An interesting option, but NOT a first hand [...]
26951 96 49_Re: Why does this package bind fail ? (CORRECTED)13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Tue, 14 Mar 2000 10:04:16 -0600574_iso-8859-1 Actually, the error code you are getting on the bind is pretty accurate -- The QUALGEN ID does not have select access to the view being referenced, so you QUALGEN cannot be the package owner for a package selecting from that view. Yes, you have given QUALGEN DBADM on the database being used, but having DBADM on a database does not implicitly give you access to views -- these must be granted separately. Your test of setting sqlid to QUALGEN and then selecting from the view really just means that YOU have access to the view (not neccessarily through the [...]
27048 51 49_Re: 00C9009C abend on NPI when LOADing RESUME YES12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Tue, 14 Mar 2000 10:05:00 -0500458_- Look at the console/syslog for any additional messages. >>> DB2-L@RYCI.COM@inter2 03/14/00 05:18AM >>> Hi Has anyone any ideas on this one or has experienced anything similar?
We are getting the following abend intermittently, ostensibly due to an attempt to insert into a full logical partition of an NPI, when running a LOAD RESUME YES under DB2 V4 into a partitioned tablespace. RELOAD phase completes ok, but then the BUILD phase fails.... [...]
27100 101 40_Re: Switch to large tablespace - anythin10_Ron Graham23_rgraham@CDBSOFTWARE.COM31_Tue, 14 Mar 2000 10:26:20 -0600434_ISO-8859-1 Hi,
Actually, it is our CDB/RePart product that will do the conversion. If the TOLARGE parameter is specified when rebalancing (and REORGing) a partitioned tablespace, CDB/RePart will convert the tablespace to LARGE format.
Ron
-----Original Message----- From: DCYR@EMAIL.USPS.GOV Sent: Tuesday, March 14, 2000 10:12 AM To: DB2-L@RYCI.COM Subject: Re: Switch to large tablespace - anythin [...]
27202 142 20_Re: DEGREE in BIND ?12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 14 Mar 2000 08:49:12 -0800398_us-ascii We have a case where we get degree(3) for a set of tables in V5 that are not partitioned. Note that if we change the last qualifications (ro.wrrocc and ro.wrroyy) to BETWEENs from the = OR condition we lose the parallelism and then have a tablespace scan on that table rather than a Hybrid INDEX scan. Needless to say the query runs significantly longer (like an order of magnitude). [...]
27345 129 23_Re: Index won't rebuild0_19_mike.holmans@BT.COM31_Tue, 14 Mar 2000 16:51:47 -0000615_iso-8859-1 Thanks, Leo.
The reply startled me, because we do have a very strong naming convention, and it should not allow indexes to have names different from their indexspace names.
But it confirms what had actually happened.
The error goes back years, to when we converted to type 2 indexes (before my time on the project). It seems that some rationalisation of what had been a fairly chaotic environment took place at the same time, and hlqs were brought into line - or so we thought. The original index FSTT3D.ITPE01 should have been dropped and the type 2 index FSTT3.ITPE01 created [...]
27475 72 33_Re: Deadlock on an insert? -Reply13_Adrian Savory24_adrian.savory@ZURICH.COM31_Tue, 14 Mar 2000 08:25:34 +0000318_us-ascii John,
Just a thought - the table is not buffered on the app. server is it? If it is any changes to the table would have to be reflected on the app. server copy - this results in a read of the table using RS. If you have a number of processes doing this concurrently this could cause your problem. [...]
27548 40 29_Re: Possible trivia question?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 14 Mar 2000 15:58:18 -0000328_- I really cannot beleive no-one else has answered this yet
This means I can be at the front of the flood!!
The low level qualifier is the DATESET NUMBER of a tablespace or indexspace. For simple pagesets under 2GB, this will always be A001. Second (and subsequent) datasets will get A002, A003, A004 etc etc [...]
27589 67 29_Re: Possible trivia question?22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Tue, 14 Mar 2000 16:39:19 +0000468_ISO-8859-1 Phil I thought it was a loaded question - I was taught at school to avoid loaded questions :-))
Les
______________________________ Reply Separator _________________________________ Subject: Re: Possible trivia question? Author: "Grainger; Phil" at Internet Date: 3/14/00 3:58 PM
I really cannot beleive no-one else has answered this yet
This means I can be at the front of the flood!! [...]
27657 24 23_Return Code (Cond Code)14_Bistricer, Avi20_BistricerA@CONED.COM31_Tue, 14 Mar 2000 12:38:18 -0500420_windows-1252 Using EXEC PGM=IKJEFT01
When does a –display db(dbname)…… or –Start db(dbname)…….
return a CC of 4 ?
thanks
Avi Bistricer, DBA 212.460.6695
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
27682 18 16_Re: -904 SQLCODE12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU31_Tue, 14 Mar 2000 12:45:53 -0500453_iso-8859-1 Hi all, I would appreciate some suggestions on a -904 SQLCODE that is being returned when attempting to perform an insert. The tables and tablespace is in R/W status.
Thank you in advance. Jeff
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
27701 22 28_DB2 Migration from V4 to V6.12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM29_Tue, 14 Mar 2000 10:09:33 PST297_- Hi Folks :
I'm not sure it is true but I heard something about a way to do the db2 migration from db2 v4 to v6 skipping v5. I would like to know if it is true and if somebody could tell me the name of the product. Is it IBM product or Three-party product? Thanks in advance. [...]
27724 50 32_Re: DB2 Migration from V4 to V6.15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM31_Tue, 14 Mar 2000 13:17:21 -0500492_iso-8859-1 Harbry,
I only know that I will be skipping V5, but I will have to do an unload of my V4 system and a load into my V6 system. Currently there is no availability of using the migration process to skip a version; however, at the recent SHARE Tech Conference, IBM is changing it's direction on many of their products to allow migration of skipping a version as you are asking. It's being worked on was my understanding from the IBM developers who attended the conference. [...]
27775 28 16_Re: -904 SQLCODE13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 14 Mar 2000 13:33:14 -0500441_iso-8859-1 Hi Jeff, You really need the reason code and resource name(& type occasionally) to resolve -904 problems. We could provide some guesses as to what might happen in that case, but you'll save your and our time by providing the information listed. I hope your application is calling DSNTIAR to display the SQLCA information. If it's not, it should. But for now, if you have access to the SQLCA, the above fields are in SQLERRM. [...]
27804 40 16_Re: -904 SQLCODE19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 14 Mar 2000 12:28:06 -0600616_ISO-8859-1 Jeff, try this: -DIS DB(dbdbd) SPACE(spspsp) LOCKS
HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...]
27845 44 16_Re: -904 SQLCODE12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU31_Tue, 14 Mar 2000 13:32:24 -0500667_iso-8859-1 Thank you for your reply.
Jeff
-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: Tuesday, March 14, 2000 1:33 PM To: DB2-L@RYCI.COM Subject: Re: -904 SQLCODE
Hi Jeff, You really need the reason code and resource name(& type occasionally) to resolve -904 problems. We could provide some guesses as to what might happen in that case, but you'll save your and our time by providing the information listed. I hope your application is calling DSNTIAR to display the SQLCA information. If it's not, it should. But for now, if you have access to the SQLCA, the above fields are in SQLERRM. [...]
27890 69 55_Re: Switch to large tablespace - anything to watch for?0_19_Tim.Lowe@STPAUL.COM31_Tue, 14 Mar 2000 12:29:34 -0600490_us-ascii Paul, I did not have any real problems with large tablespaces. The only thing that I noticed was that some columns in the DB2 catalog are no longer populated by runstats for large tablespaces, and you must use the new floating point columns. (For example, in sysibm.systables you must use the new CARDF column instead of the old CARD column. See runstats in the utility guide a list of the other columns.) I had some queries in automated processes that were effected by this. [...]
27960 100 33_Re: Deadlock on an insert? -Reply0_20_John_Lendman@FPL.COM31_Tue, 14 Mar 2000 13:36:36 -0500311_us-ascii Table is not buffered on the Apps Server.
We do think thought that the load program is doing a read of the PROP table before it does an INSERT, therefore causing a deadlock when one process is in the READ phase and the other process is in the INSERT phase. We are trying to prove this now. [...]
28061 13 32_Re: DB2 Migration from V4 to V6.0_16_HesterRC@AOL.COM29_Tue, 14 Mar 2000 13:41:59 EST374_US-ASCII CA/Platinum's RC/Merger (add-on to RC/Migrator) can be used skip releases without unloading and reloading application data.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
28075 55 16_Re: -904 SQLCODE12_Kalman, Jeff20_JKalman@UNCH.UNC.EDU31_Tue, 14 Mar 2000 13:54:18 -0500677_iso-8859-1 Thank you for your reply. Jeff
-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:RD8246@MOMAIL.SBC.COM] Sent: Tuesday, March 14, 2000 1:28 PM To: DB2-L@RYCI.COM Subject: Re: -904 SQLCODE
Jeff, try this: -DIS DB(dbdbd) SPACE(spspsp) LOCKS
HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete [...]
28131 97 55_Re: Switch to large tablespace - anything to watch for?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 14 Mar 2000 13:57:47 -0500425_us-ascii CARD is not used even with non-large tablespaces. Optimizer only looks at all float columns counterparts with V5 onwards. E.g. CARDF, COLCARDF, FREQUENCYF etc. More columns are included in V6 - CLUSTERATIOF & NACTIVEF
Although CARD is populated by runstat for non-large tablespaces but is not used by optimizer at all. All the automated process should consider this regardless of tablespace size option. [...]
28229 144 55_Re: Switch to large tablespace - anything to watch for?0_19_Tim.Lowe@STPAUL.COM31_Tue, 14 Mar 2000 13:13:50 -0600411_us-ascii I know that CARD is not used by DB2 anymore, but I was only pointing out that if you have any of your own processes that use this, then this will impact you. You should try to remember that there is a lot of sql out there that accesses the catalog, and everyone may not have changed everything yet. And, since this did have a minor impact to me, then I thought it might help others to mention it. [...]
28374 74 55_Re: Switch to large tablespace - anything to watch for?13_Moss, William15_MossW@AETNA.COM31_Tue, 14 Mar 2000 14:46:22 -0500383_- Paul,
These are the catalog columns that do not get populated with runstats for large tablespaces.
| RUNSTATS sets the following columns to -1 for large table spaces. * | COLCARD in SYSCOLUMNS * | CARD in SYSTABLES * | CARD in SYSINDEXPART * | FAROFFPOS in SYSINDEXPART * | NEAROFFPOS in SYSINDEXPART * | FIRSTKEYCARD in SYSINDEXES * | FULLKEYCARD in SYSINDEXES [...]
28449 23 11_No of locks9_Jagan Rao17_Jagan_Rao@GAP.COM31_Tue, 14 Mar 2000 12:03:29 -0800400_us-ascii Dear list
We are developing an application in CICS online system with DB2. We have very high transaction rate. We need to insert 63 million rows in one Database , putting all tables together in a day. ( 5.3 millions per hour and 1500 per sec etc). I am just wondering if there is any limit on the number of locks in DB2 system or also can db2 handle such high rate of insertion. [...]
28473 79 37_DB2 CONNECT PE holding locks forever.0_14_dcreed@CSC.COM31_Tue, 14 Mar 2000 14:06:20 -0600363_us-ascii I am currently having a problem with DB2 Connect PE V52 (WR9094) level. I know there are newer patch levels, but I have not seen any indication in the one line readme files that would indicate that they would help.
Here is what I see. An application is utilizing ODBC to access OS/390 V5R1. The problem is that the claimers are held forever. [...]
28553 169 55_Re: Switch to large tablespace - anything to watch for?20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 14 Mar 2000 15:10:05 -0500281_iso-8859-1 You are right. There are lots of home grown utilities in many places which refer to CARD etc.unknowingly and no one to change it for various reasons - "The guy who wrote has left the company" & "Why fix when something is not broke" etc. This worries me some times. [...]
28723 98 51_quick add too DB2 CONNECT PE holding locks forever.0_14_dcreed@CSC.COM31_Tue, 14 Mar 2000 14:23:22 -0600476_us-ascii Added last SQL stmt:
DB2 CONNECT PE holding locks forever.
I am currently having a problem with DB2 Connect PE V52 (WR9094) level. I know there are newer patch levels, but I have not seen any indication in the one line readme files that would indicate that they would help.
Here is what I see. An application is utilizing ODBC to access OS/390 V5R1. The problem is that the claimers are held forever. [...]
28822 100 41_Re: DB2 CONNECT PE holding locks forever.13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Tue, 14 Mar 2000 14:27:47 -0600511_US-ASCII Hello Danny, Check zparms CMTSTAT= and IDTHTOIN= If you set CMTSTAT=INACTIVE your DDF threads will go inactive after committing and any active DDF threads that do not commit, can be terminated by the IDTHTOIN= parm. HTH Kurt
>>> 03/14/00 02:06PM >>> I am currently having a problem with DB2 Connect PE V52 (WR9094) level. I know there are newer patch levels, but I have not seen any indication in the one line readme files that would indicate that they would help. [...]
28923 47 15_Re: No of locks0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 14 Mar 2000 14:45:19 -0600391_us-ascii Just out of curiosity ... are you saying that your initial load of the DB will be 63 million rows (and has to be finished in one day) or that your daily transaction rate will require that many inserts/updates.
Jagan Rao on 2000/03/14 02:03:29 PM
Please respond to DB2 Data Base Discussion List [...]
28971 45 15_Re: No of locks19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 14 Mar 2000 14:54:28 -0600584_ISO-8859-1 Jagan, The real question is do you have enough machine and I/O subsystem to do what you need. Use LOCK TABLE to get around number of locks. HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. [...]
29017 36 20_Update with subquery13_Jaydeep Ghosh29_Jaydeep_Ghosh@PROGRESSIVE.COM31_Tue, 14 Mar 2000 16:03:17 -0500518_us-ascii Hi All , The problem query :
UPDATE WOM.ROUTE_CALL_DETAIL SET POL_NBR = ( SELECT INTEGER(SUBSTR(VAR10,5,8)) FROM WOM.ROUTE_CALL_DETAIL ) WHERE DAY_NBR = 145802 AND ROUT_CALL_KEY = 48040 AND ROUT_ID = 7855
The purpose is to update the column POL_NBR ( defined as INT ) with a value from VAR10 ( defined as Varchar 40 ) . I am using the function INTEGER to convert the data type from char ( in VAR10 ) to integer ( for POL_NBR ) . The error returned is : SQL error at or before INTEGER ..... [...]
29054 58 24_Re: Update with subquery20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 14 Mar 2000 16:23:17 -0500319_us-ascii Question #1 : Is it possible to use subquery for this update . Ans: No. you can't use it. Probably, you should consider doing this using program.
#2 : How to handle char to int conversion . Ans: Unfortunately, you can't do char to integer conversion using SQL. You have to do this in your program. [...]
29113 65 15_Re: No of locks9_Jagan Rao17_Jagan_Rao@GAP.COM31_Tue, 14 Mar 2000 13:41:11 -0800523_us-ascii There are around 18 million business transactions per day which will result in 63 million inserts in DB2.
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: Jagan Rao/SB/GAPINC) Subject: Re: No of locks
Just out of curiosity ... are you saying that your initial load of the DB will be 63 million rows (and has to be finished in one day) or that your daily transaction rate will require that many inserts/updates. [...]
29179 138 41_Re: DB2 CONNECT PE holding locks forever.8_Kurian B23_Bejoy_Kurian@MAY-CO.COM31_Tue, 14 Mar 2000 15:55:28 -0600309_us-ascii We also have a similar problem. System shows very slow response and Most of the time CPU is 100% and not much users are using. I noticed the DBM1 address space using more than 55% and TCPIP more than 10%. We are in OS390 2.6 and DB2v6.1. Most of the queries are through DB2 Connect. Any ideas?? [...]
29318 22 41_Migrating QMF user objects using DSNTIAUL0_17_JTonchick@AOL.COM29_Tue, 14 Mar 2000 16:57:52 EST489_US-ASCII We are in the process of moving datacenters. We need to "extract" QMF saved queries, forms, and procedures from one system to another. Using the QMF Export command would be a laborious task. Since QMF is just an application to DB2, would it be feasible to use DSNTIAUL to select rows from the 3 QMF object tables by userid, then LOAD them into a fresh QMF environment at the new location? Note: no 3rd party software is involved. The version of QMF is 3.2 in both locations. [...]
29341 70 24_Re: Update with subquery9_Birk, Tim17_BirkT@DIEBOLD.COM31_Tue, 14 Mar 2000 16:59:34 -0500458_iso-8859-1 Actually you can convert a character to integer like this: INTEGER(DECIMAL(SUBSTR(VAR10,5,8),8,0))
-----Original Message----- From: Pillay, Venkat (PCA) [mailto:venkat_pillay@ML.COM] Sent: Tuesday, March 14, 2000 4:23 PM To: DB2-L@RYCI.COM Subject: Re: Update with subquery
Question #1 : Is it possible to use subquery for this update . Ans: No. you can't use it. Probably, you should consider doing this using program. [...]
29412 48 45_Re: Migrating QMF user objects using DSNTIAUL14_Mike Ockenfels28_Michael.D.Ockenfels@WCOM.COM31_Tue, 14 Mar 2000 16:09:36 -0600485_us-ascii You could use DFDSS to move/copy the datasets and not have to mess with loading, exporting, etc at all. I did this a few months ago with no problems.
Mike Ockenfels MCI-Worldcom
JTonchick@AOL.COM on 03/14/2000 03:57:52 PM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: Mike Ockenfels/CDR/BSM/MCI) Subject: Migrating QMF user objects using DSNTIAUL [...]
29461 154 41_Re: DB2 CONNECT PE holding locks forever.8_Kurian B23_Bejoy_Kurian@MAY-CO.COM31_Tue, 14 Mar 2000 16:10:23 -0600718_us-ascii Adding one more. Lot of 0C4 abends can see in the MSTR started task.
16.04.23 STC04260 DSNL027I -FDBP SERVER DISTRIBUTED AGENT WITH LUWID=GA010317.BA09.000314210246=918 THREAD-INFO=WBA0001:*:wba0001:db2jd.exe RECEIVED ABEND=0C4 FOR REASON=00000000 16.04.23 STC04260 DSNL028I -FDBP GA010317.BA09.000314210246=918 ACCESSING DATA FOR LOCATION 10.1.3.23 IPADDR 10.1.3.23
Thanks Bejoy
We also have a similar problem. System shows very slow response and Most of the time CPU is 100% and not much users are using. I noticed the DBM1 address space using more than 55% and TCPIP more than 10%. We are in OS390 2.6 and DB2v6.1. Most of the queries are through DB2 Connect. Any ideas?? [...]
29616 42 45_Re: Migrating QMF user objects using DSNTIAUL12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Tue, 14 Mar 2000 16:14:40 -0600510_iso-8859-1 I have used the unload utility to unload 3 of the Qmf tables, make changes with tso and then reload the data back. I have done this in the past when I wanted to put a copy of a query in everyone's "library" and when an employee left and then came back. When the employee came back HR issued the employee a new employee id. I just used tso to modify the employee id (creator) and then loaded the data back. Seems similar. I did back everything up first - just in case I messed it up. By the way [...]
29659 110 24_Re: Update with subquery23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM31_Tue, 14 Mar 2000 17:15:45 -0500388_- Jaydeep,
You can not use the same table in sub-select of an 'update' statement. In any case, your SQL in the subselect has no where clause which would bring (if it were to work) all the values of column VAR10. Assuming that you are trying to update POL_NBR with a substring of VAR10 converted into integer for all the rows satisfying the where clause, following should work [...]
29770 33 21_sqlcode -805 reson 0215_Benjamin Reches24_Benjamin.Reches@MSDW.COM31_Tue, 14 Mar 2000 17:39:41 -0500397_us-ascii am running db2 v6 os/390
I created which calls a stored procedure passing it a table reference.
The stroed procedure is invoked. I decalre a curosr on the table. However, when I attempt to open the cursor I get a SQLCODE -805 Reason 02.
The message reads as follows:
DBRM OR PACKAGE NAME MSCONYDTA1.M80119.DEALPROC. NOT FOUND IN PLAN DSNESPCS. Reason 02. [...]
29804 41 25_Re: sqlcode -805 reson 0212_Jim Harrison12_jimh@QIS.NET31_Tue, 14 Mar 2000 18:24:07 -0500518_us-ascii IIRC, your SP is inheriting the plan of the program that invoked it. In this case, SPUFI. I ran into tons of -805 problems when I concocted my first SP and spent days trying to sort it all out. In the end, what I did was bind both the invoking program and the SP in the same collection and I also issued a "SET CURRENT PACKAGE SET = xxxxxx" at the start of the SP. I suspect I only needed the SET statement, but since they were both my own programs, I wasn't taking any chances because plans, packages & [...]
29846 172 41_Re: DB2 CONNECT PE holding locks forever.24_Gendron, Dave (Exchange)28_Dave.Gendron@COASTALCORP.COM31_Tue, 14 Mar 2000 18:37:08 -0500455_- I may be off track, but could this be related to APAR PQ28037, which addresses "DDF threads become inactive while holding locks..."?
Dave
Dave Gendron Colorado Springs, CO dave.gendron@coastalcorp.com
-----Original Message----- From: Kurian B Sent: Tuesday, March 14, 2000 3:10 PM To: DB2-L@RYCI.COM Subject: Re: DB2 CONNECT PE holding locks forever.
Adding one more. Lot of 0C4 abends can see in the MSTR started task. [...]
30019 24 27_Naming Standards/Suggestion7_Db2 Dba24_data_analyst@HOTMAIL.COM31_Tue, 14 Mar 2000 17:29:19 -0600346_- Hi all,
I am designing a large EEE UDB database on AIX platform and am looking for any suggestions on naming standards/conventions that are proven practical, while taking advantage of long names.
I already have a good idea for naming Nodegroups, Volume Groups, Logical Volumes, and File Systems (but am open to suggestions). [...]
30044 83 29_Re: Possible trivia question?14_Randall Ibbott18_ribbott@QBE.COM.AU31_Wed, 15 Mar 2000 10:58:51 +1100416_us-ascii What happens when a PARTITIONED tablespace (say 17-32 partitions) breaks the 2GIG limit - if the 'A00x' qualifier is the partition number, what is the name of the second dataset? This is something we were wondering when doing some partitioning recently and assumed (rightly or wrongly) that DB2 may not allow the break as you are already partitioned. Unfortunately we didn't have time to check it out. [...]
30128 46 29_Re: Possible trivia question?14_James Campbell29_James.Campbell@HANCORP.COM.AU31_Wed, 15 Mar 2000 11:23:21 +1100484_- A partitioned tablespace is limited to one cluster per partition, so there's no second dataset.
So there's a big downside in getting the partitioning key values wrong!
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9840 3864 James.Campbell@Hancorp.com.au -----Original Message----- From: Randall Ibbott [mailto:ribbott@QBE.COM.AU] Sent: Wednesday, March 15, 2000 9:59 AM To: DB2-L@RYCI.COM Subject: Re: Possible trivia question? [...]
30175 19 12_ERWin Macros0_14_DPetro@AOL.COM29_Tue, 14 Mar 2000 20:34:39 EST329_US-ASCII We'd like to program some ERWin Macros to help in forward/reverse engineering similar to get some basic functions that the Bachman NSSL file provided. Has anyone done anything like this before? We see in ERWin HELP that there are standard macro calls provided but where are these coded and specified for execution? [...]
30195 121 29_Re: Possible trivia question?12_Reg Munusamy20_rmunusamy@GIO.COM.AU31_Wed, 15 Mar 2000 12:25:36 +1000371_us-ascii Randall,
We had a LOAD job fail last week-end when trying to append data to the last partition of one of our big tables which hit the 2Gig limit. Fortunately for us it was a history table and didn't need to be fixed immediately. Currently working on re-partitioning the table to take care of some "hot spots" that existed in the original definition. [...]