1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l February 2001, week 1
2 114 52_Re: What kind of DBA does it take for an ERP system?23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Thu, 1 Feb 2001 06:45:00 -0000336_iso-8859-1 Terry
I am sure you have had many replies to this mail.
My honest opinion is that you need to have the following basic ingredients for someone to support an ERP (SAP, Peoplesoft or other) :
1. Between 1 to 2 years solid general support knowledge of DB2, which should include reasonable knowledge of [...]
117 52 51_Re: REORG option DISCARD + a request to Db2-Listerz10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 1 Feb 2001 09:09:52 +0100332_us-ascii Hi Phil and many & many thanks for your reply. I read all manuals and DSNDUNLX member in SDSNMACS library but
there was nothing about the limitation in using REORG DISCARD. I'll perform some test in the next future.
I was thinking to buy a brand new ORACLE DBMS to resolve this problem.....(believe it). [...]
170 158 50_Re: Help with Partition Table with historical data0_18_mebert@AMADEUS.NET30_Thu, 1 Feb 2001 09:42:03 +0100425_us-ascii Hi Mr B,
yes indeed. But when I asked about this technique after starting here, the application people told me "We tried that, but the performance is lousy". I never bothered to verify that statement (guess why).
Dr. E.
From: "Bell, Raymond W" on 31/01/2001 22:35 GMT
Please respond to DB2 Data Base Discussion List [...]
329 13 25_Re: Timestamp differences15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Thu, 1 Feb 2001 02:42:30 -0600369_- Hello Marcus
I think, that's not possible, because the duration could be some years, so the time format does not work.
================================================ 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.
343 39 12_Rid Failures11_John Curran26_John.Curran@DRESDNERKB.COM30_Thu, 1 Feb 2001 09:17:50 -0000495_iso-8859-1 Hello.
DB2 OS/390 V5
We have been suffering rather a lot of RID failures recently. Is there any way to trace the SQL that's getting these? We have Omegamon, but I can't find anything to trace RID failures. Anyone know how?
We also have some SQL with OPTIMIZE FOR 1 ROW, to suppress list prefetch. But since we upgraded to DB2 V5 (from V4) it seems that list prefetch is being used anyway. I thought OPTIMIZE FOR 1 ROW prevented list prefetch. Is this wrong? [...]
383 73 16_AW: Rid Failures12_Thomas Weber23_thomas.weber@LDS.NRW.DE30_Thu, 1 Feb 2001 11:12:00 +0100624_iso-8859-1 Hi John,
i remember an article by Bonnie Baker "Demystifying OPTIMIZE FOR N ROWS" or so. I think it was in DB2 Magizine 2/2000. (www.db2mag.com).
HTH
Thomas
> -----Ursprüngliche Nachricht----- > Von: John Curran [mailto:John.Curran@DRESDNERKB.COM] > Gesendet am: Donnerstag, 1. Februar 2001 10:18 > An: DB2-L@RYCI.COM > Betreff: Rid Failures > > Hello. > > DB2 OS/390 V5 > > We have been suffering rather a lot of RID failures > recently. Is there any way to trace the SQL that's getting > these? We have > Omegamon, but I can't find anything to trace RID failures. > Anyone know [...]
457 70 25_Re: Timestamp differences10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Thu, 1 Feb 2001 04:44:39 -0600775_- Hello Mork,
This seems to do what you want:
select time(timestamp('0001-01-01-00.00.00.000000') + (end_timestamp - start_timestamp)) from yourtab;
Of course, if end_timestamp - start_timestamp >= 24 hours then it will still work but the result will be a little misleading...
On Wed, 31 Jan 2001 10:32:43 -0000, Davage, Marcus wrote:
>Nanno, Nanno. > >I have a basic, teeny table with JOBNAME, JOBNUM, START_TIMESTAMP and >END_TIMESTAMP on it. >When I select from it, how can I get the END_TIMESTAMP - START_TIMESTAMP >value (returned in seconds) to be displayed in TIME format (hh:mm:ss) >without faffing about with stuff like... >HOUR(END_TIMESTAMP - START_TIMESTAMP) [...]
528 112 25_DB2 Connect - TCPIP Error12_Jacob Ganzel19_jacobg@SEMECH.CO.IL30_Thu, 1 Feb 2001 13:11:36 +0200466_x-user-defined Hello.
While trying to connect Windows/9x client (Version 7.1 fixpack 2 ) to DB2 for MVS 5.1 via DB2 Connect EE on NT we receive on some of the clients the following error: SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKET". Location: "". Communication function" "socket" Protocol specific error code(s): "10047","*","*" or "10044","*","*" sqlstate=08001 [...]
641 141 29_Re: DB2 Connect - TCPIP Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Thu, 1 Feb 2001 17:30:24 +0530377_us-ascii
I am facing this problem right from Version 5.1 on mainframe .. we upgraded to 6.1 ... nobody from IBM is helping ... My shop users are really complaining a lot. Again the probelm occurs intermittently .. i.e ... sometimes it occurs .. sometimes it does'nt .... Can leon throw some light on this issue... is there any patch that is to be applied. [...]
783 25 25_CA/Platinum On-Line Reorg18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM30_Thu, 1 Feb 2001 07:03:14 -0500427_iso-8859-1 Is any out there in DB2-land using this vendors on-line reorg tool?? If so I'd like to talk dba-to-dba to see how satisfied or unsatisfied you are with the tool. Please feel free to contact me off-line from the list, either by email or by phone.
Thanks: --Steve....
Steve Whittaker DB2/DBA Progress Energy, Inc. Mail stop:OHS 10- Raleigh Email: stephen.whittaker@pgnmail.com Phone: (919) 546-7267 [...]
809 182 44_Re: Joining DB2 Tables across DB2 Subsystems10_Keith Kuhn21_Keith_Kuhn@WENDYS.COM30_Thu, 1 Feb 2001 07:08:56 -0500278_iso-8859-1 Hi Listers, I work with Jose'. Just so you know, we started with the ALIAS in the join. When we received the -512 we tried the 3-part name with the same results. We did not want to confuse the issue (question to the list) by bringing the ALIAS into the picture. [...]
992 140 29_Re: DB2 Connect - TCPIP Error12_Martin, Paul22_Paul.Martin@ECOLAB.COM30_Thu, 1 Feb 2001 06:14:11 -0600385_x-user-defined Here is what it says about the the two errors
WSAESOCKTNOSUPPORT 10044 Socket type not supported.
WSAEAFNOSUPPORT 10047 Address family not supported by protocol
family.
On a client that doesn't work I would issue the IP command "ipconfig /all" and compare it to a client that works, to make sure everything on TCP/IP is configured right [...]
1133 31 26_Adabas to DB2 Conversion !16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 1 Feb 2001 17:24:14 +0530356_- Hi All,
I need to get some idea about Adabas to DB2 conversion. If any installation have done it or anyone who was involved in these kind of projects, please share your experience, views and ideas/suggestions. Please also send me the websites which can give me some information about how to go about this or any other issues related to this. [...]
1165 23 55_Impact of online reorg at the partition level on an NPI13_Johnson, Bill18_bjo23@ALLSTATE.COM30_Thu, 1 Feb 2001 07:29:52 -0600558_- All, I guess I need to further qualify my requests. What I am looking for is a white paper or something similar that points out the degradation that occurs to the NPI when doing online reorg at the partition level. I am trying to convince my compatriots and have an article from a Craig Friske from 1999 that indicate that for large tablespaces with an NPI, it is better to perform the reorg at the tablespace level rather than the partition level mainly because the build2 phase is needed for the partition level reorg. What it also mentions is that [...]
1189 87 34_Re: Compact Data for Archive Logs?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Thu, 1 Feb 2001 07:29:34 -0600565_iso-8859-1 James, I agree with you, where speed to rollback locally is an issue, enough active logs should be used where most rollbacks can be accomplished from disk. A small word of caution. My experience with this issue is several years old now and I won't mention the vendor but they make tape handlers that are round and tall. They made the claim that their newest (back then) tape drive could read a compressed tape backwards as fast as an uncompressed tape. Well, when I put it to a test -- they couldn't. Don't know how far this technology has advanced [...]
1277 240 44_Re: Joining DB2 Tables across DB2 Subsystems9_Chu, Pius14_ChuP@CONED.COM30_Thu, 1 Feb 2001 08:45:11 -0500482_iso-8859-1 I did a research on this couple years ago because a programmer did exactly the same thing. She coded a a SQL statement that join a local and a remote DB2 on our OS/390 platform. I did not find much in any DB2 manuals but there are a few places in the Red Books that talk about this in the DRDA area under the Distributed Unit of Work. This came from P137 of the Red Book "WOW! DRDA Supports TCP/IP: DB2 Server for OS/390 and DB2 Universal Database (SG24-2212-00)" . [...]
1518 150 44_Re: Joining DB2 Tables across DB2 Subsystems15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU30_Thu, 1 Feb 2001 09:31:40 -0500474_iso-8859-1 We are attempting to use DB2 UDB's "Federated database" to create a nickname to an Oracle Database using NET8/DB2 Relational Connect. We have created another nickname for OS/390 DB2 V5.1 database table and hope to perform SQL against all three structures. (DB2 UDB on NT, DB2 V.5.1 OS/390, Oracle)
All the parts work separately, but we have not tried to join them yet as I have gotten pulled off to fight some fires....lol. Anybody tried this before? [...]
1669 38 33_Re: v6 migration & host variables11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Thu, 1 Feb 2001 09:46:37 -0500493_iso-8859-1 There are also some posts on this out on the DB2L Documents site. I have a SAS enhanced version of IBM's code that runs much faster, and Venkat Pillay has also posted a REXX that will fix the DBRMs.
See:
http://jupiter.ryci.com/archives/db2-l-documents.html
December 2000 and October 2000
-----Original Message----- From: Tom Taylor [mailto:ttaylor@CHUBB.COM] Sent: Wednesday, January 31, 2001 4:57 PM Subject: v6 migration & host variables [...]
1708 50 12_BIND Problem14_Toppins, Smike21_smike.toppins@GWL.COM30_Thu, 1 Feb 2001 07:53:51 -0700519_- We're going crazy here ...
I can run the following SQL in SPUFI, no problem. When we go to compile/bind it however, we keep getting a -206 error. Any ideas???
SELECT PRV_CAPTN_REC_BEG ,DATE(PRV_CAPTN_REC_TRM - 1 DAY) ,PRV_CAPTN_REC_TRM ,CAPTN_AGRMT_NO FROM GI30BLDB.T_PRV_CAPTN_AGRMT WHERE RP_NO = '001' AND PRV_NO = :PRV-NO AND PRV_LCTN_NO = :PRV-LCTN-NO AND PRV_BUSN_CD = :PRV-BUSN-CD AND PRV_NTWRK_CD = :PRV-NTWRK-CD AND SYS_VOID_STS_IND = 'N' AND PRV_CAPTN_STS_CD = 'A' ORDER BY 1 DESC, 2 DESC [...]
1759 141 38_Re: Checking Consistency Tokens in DB212_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Thu, 1 Feb 2001 08:57:04 -0600416_us-ascii John:
We have no issue with you giving a solution to customers. We do similar things in both our consulting and software efforts. I agree that a commercial solution has a target audience that is different than the 2 AM in the morning question of "did I bind that while I was doing twelve other things?" or showing an application programmer that a bind is required every time the SQL is changed. [...]
1901 60 38_Re: Accessing Stored Procedure Source.13_Michalik, Ken19_kmichalik@KRAFT.COM30_Thu, 1 Feb 2001 08:49:15 -0600319_- I don't see why you would want to change the schema name. As I understand it, since whatever new schema name you use is not in the default search order SYSIBM, SYSFUNC, SYSPROC,.... then the procedure won't be found unless an application is bound with PATH() bind option or issues the SET CURRENT PATH SQL stmt. [...]
1962 53 34_Re: Compact Data for Archive Logs?11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Thu, 1 Feb 2001 09:59:58 -0500602_iso-8859-1 Mark,
This is an old issue. It really depends on your tape hardware. Originally IBM tape drives could not read compacted data backwards. Since DB2 would position to a place in the log and then read backwards through the log for some operations, this was an issue. This operation did not actually "fail" but each backwards read was a failure followed by a forwards retry. Needless to say there was a serious performance penalty in some operations. For this reason IBM recommended against COMPACT and implemented a ZPARM to allow you to control this. (as many shops are compact by [...]
2016 95 16_AW: BIND Problem12_Peter, Georg15_G.Peter@DZBW.DE30_Thu, 1 Feb 2001 16:07:25 +0100872_iso-8859-1 SQLSTATE 42703 (or SQLCODE -206): Column name not in any table referenced in the FROM clause or in the table on which the trigger is defined.
Are the columns RP_NO, PRV_NO, PRV_LCTN_NO, PRV_BUSN_CD, PRV_NTWRK_CD, SYS_VOID_STS_IND and PRV_CAPTN_STS_CD really all in your table GI30BLDB.T_PRV_CAPTN_AGRMTT ?
HTH.
With kind regards - mit freundlichen Grüssen, G e o r g H . P e t e r DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- [...]
2112 20 22_PeopleSoft HR Contacts18_Teegarden, Stephen22_Teegarden@AEGONUSA.COM30_Thu, 1 Feb 2001 09:09:03 -0600503_- We would like to make contact with anyone who is using PeopleSoft on DB2 OS/390 to do HR BENEFITS, OPEN ENROLLMENT via the Internet / Intranet. We are currently at V7.56 PeopleSoft and DB2 v5.1.
Thanks
Steve Teegarden, DBA AEGON USA 319-398-8066
================================================ 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.
2133 77 16_Re: BIND Problem16_Katsnelson, Yury26_Yury.Katsnelson@BCBSFL.COM30_Thu, 1 Feb 2001 10:04:42 -0500561_iso-8859-1 I believe, you would need to change one of the precompiler options (APOST or APOSTSQL,- I do not remember exactly).
> Yury Katsnelson > Electronic Commerce Systems x5-8043
-----Original Message----- From: Toppins, Smike [mailto:smike.toppins@GWL.COM] Sent: Thursday, February 01, 2001 9:54 AM To: DB2-L@RYCI.COM Subject: BIND Problem
We're going crazy here ...
I can run the following SQL in SPUFI, no problem. When we go to compile/bind it however, we keep getting a -206 error. Any ideas??? [...]
2211 125 16_Re: BIND Problem14_Toppins, Smike21_smike.toppins@GWL.COM30_Thu, 1 Feb 2001 08:25:26 -0700683_iso-8859-1 Yes, otherwise the query wouldn't run in SPUFI.
SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094
> ---------- > From: Peter, Georg[SMTP:G.Peter@DZBW.DE] > Reply To: DB2 Data Base Discussion List > Sent: Thursday, February 01, 2001 8:07 AM > To: DB2-L@RYCI.COM > Subject: AW: BIND Problem > > SQLSTATE 42703 (or SQLCODE -206): Column name not in any table referenced > in > the FROM clause or in the table on which the trigger is defined. > > Are the columns RP_NO, PRV_NO, PRV_LCTN_NO, PRV_BUSN_CD, PRV_NTWRK_CD, > SYS_VOID_STS_IND and > PRV_CAPTN_STS_CD really all in your table GI30BLDB.T_PRV_CAPTN_AGRMTT ? > > HTH. [...]
2337 79 16_Re: BIND Problem19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Thu, 1 Feb 2001 09:27:49 -0600629_iso-8859-1 SMike Toppins, Was the SPUFI and the BIND both pointed at the same DB2 subsystem?
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 [...]
2417 118 16_Re: BIND Problem16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 1 Feb 2001 15:36:29 -0000609_iso-8859-1 One stupid question - are you sure that the SQL you execute is identical to the DBRM you are trying to BIND. In the past I have seen people BIND'ing something different than expected ?
BR Steen Rasmussen
> -----Original Message----- > From: Peter, Georg [SMTP:G.Peter@DZBW.DE] > Sent: Thursday, February 01, 2001 4:07 PM > To: DB2-L@RYCI.COM > Subject: AW: BIND Problem > > SQLSTATE 42703 (or SQLCODE -206): Column name not in any table referenced in > the FROM clause or in the table on which the trigger is defined. > > Are the columns RP_NO, PRV_NO, PRV_LCTN_NO, PRV_BUSN_CD, [...]
2536 62 16_Re: BIND Problem9_Rob Crane22_racrane@CONCENTRIC.NET30_Thu, 1 Feb 2001 08:46:31 -0700624_us-ascii When you go through your bind process what parameter do you have for QUALIFIER? Usually you would not have the owner (GI30BLDB) coded in your COBOL program like you do in your SPUFI query below. Make sure you are binding with the correct QUALIFIER(GI30BLDB).
-Rob
"Toppins, Smike" wrote: > > We're going crazy here ... > > I can run the following SQL in SPUFI, no problem. When we go to compile/bind > it however, we keep getting a -206 error. Any ideas??? > > SELECT PRV_CAPTN_REC_BEG > ,DATE(PRV_CAPTN_REC_TRM - 1 DAY) > ,PRV_CAPTN_REC_TRM > ,CAPTN_AGRMT_NO > FROM GI30BLDB.T_PRV_CAPTN_AGRMT > [...]
2599 83 16_Re: BIND Problem23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Thu, 1 Feb 2001 10:49:33 -0500599_- Mike,
The token in error message points to 001 which is the value assigned to RP_NO. Is RP_NO an integer column or character ? If character, make sure, you are using APOSTSQL as a pre-compiler option.
HTH
Sanjay
> -----Original Message----- > From: Toppins, Smike [SMTP:smike.toppins@GWL.COM] > Sent: Thursday, February 01, 2001 9:54 AM > To: DB2-L@RYCI.COM > Subject: BIND Problem > > We're going crazy here ... > > I can run the following SQL in SPUFI, no problem. When we go to > compile/bind > it however, we keep getting a -206 error. Any ideas??? > > SELECT [...]
2683 16 16_Re: BIND Problem14_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US30_Thu, 1 Feb 2001 11:07:52 -0500383_US-ASCII Did this get thru the precompiler ok?
If so, then remove qualifier in SQL statement and bind with QUALIFIER matching TBCREATOR.
===============================================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.
2700 38 38_DSN1COPY ERROR MESSAGE - return code 815_Neff, Stephen R16_NeffSR@STATE.GOV30_Thu, 1 Feb 2001 11:05:22 -0500752_iso-8859-1 Hello,
I run a DSN1COPY in OS/390 DB2V5. I get a return code 8. I look at the step and see the following message
1DSN1999I START OF DSN1COPY FOR JOB CA00SRNP DSN1COPY DSN1989I DSN1COPY IS PROCESSED WITH THE FOLLOWING OPTIONS: CHECK/NO PRINT/ 4K/FULLCOPY /DSN1999I START OF DSN1COPY FOR JOB CA00SRNP DSN1COPY RDSN1989I DSN1COPY IS PROCESSED WITH THE FOLLOWING OPTIONS: D CHECK/NO PRINT/ 4K/FULLCOPY V/NON-SEGMENT/NUMPARTS = 0/ OBIDXLAT/NO VALUE/ DRESET/ /PIECESIZ= / VDSN1998I INPUT DSNAME = DT1C.DSNDBD.CLSDSYS.CLSSDIT.I0001.A0 DVSAM 0DSN1997I OUTPUT DSNAME = DT1C.DSNDBD.CLSDPSY.CLSSDIT.I0001.A0 DVSAM ZEDSN1985I ZERO PAGES ENCOUNTERED. FIRST PAGE = 00000003, LAST 0DSN1993I DSN1COPY TERMINATED, 00000003 PAGES PROCESSED [...]
2739 65 42_Re: DSN1COPY ERROR MESSAGE - return code 815_Murley, Michael22_Michael_Murley@BMC.COM30_Thu, 1 Feb 2001 10:39:46 -0600481_iso-8859-1 Stephen,
Is this a segmented table space ? Segmented spaces can have zero pages embeded, so you have to use the SEGMENT parameter to tell DSN1COPY not to worry when it finds them there. Refer to page 3-59 in the DB2 V5 Utility Guide.
There are other kinds of spaces that can legitimately have embedded zero pages, too. Indexes and MEMBER CLUSTER table spaces are two of the most common. I'm not sure how you're supposed to handle those with DSN1COPY. [...]
2805 99 42_Re: DSN1COPY ERROR MESSAGE - return code 819_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Thu, 1 Feb 2001 10:47:29 -0600592_iso-8859-1 Stephen, The return code 8 happened for some other reason according to the following quote from manual.
DSN1985I: Explanation: One or more pages zero pages were encountered with valid data pages after them. A zero page is a page of all zeros or a page with only the page-synchronization bit on in the first and last byte of the page. The 'first zero page number' and the 'last zero page number' displayed in hexadecimal are the pages where zeros were first encountered and last encountered. In most cases, this is not an error condition. This is a normal condition for [...]
2905 149 20_Re: DSSIZE Parameter10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Thu, 1 Feb 2001 10:53:17 -0600350_us-ascii List,
We are DB2 V6 and have started using DSSIZE parm on some new tablespaces we were installing. An interesting bug we found - after you set the DSSIZE parm - ANY ALTER TABLESPACE (even alter to a new bufferpool, PRIQTY, SECQTY... all we have tried) RESET the large (4g usually) DSSIZE to ZERO, an interesting one to research! [...]
3055 105 16_Re: BIND Problem14_Toppins, Smike21_smike.toppins@GWL.COM30_Thu, 1 Feb 2001 09:55:22 -0700646_- Thanks for all the help. Turns out that the shop standard is to use QUOTESQL and the developer was using "'" (single quote) instead of '"' (double quotes).
SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094
> ---------- > From: Katsnelson, Yury[SMTP:Yury.Katsnelson@BCBSFL.COM] > Reply To: DB2 Data Base Discussion List > Sent: Thursday, February 01, 2001 8:04 AM > To: DB2-L@RYCI.COM > Subject: Re: BIND Problem > > I believe, you would need to change one of the precompiler options (APOST > or > APOSTSQL,- I do not remember exactly). > > > Yury Katsnelson > > Electronic Commerce Systems > [...]
3161 79 37_Re: Retrieving the 30 smallest values14_Mullins, Craig21_Craig_Mullins@BMC.COM30_Thu, 1 Feb 2001 11:13:20 -0600389_iso-8859-1 It is likely that DISTINCT will not help for most of these types of queries. I think most queries of this nature are not just looking for the specific value that is being limited, but also other columns in the row that can differ. For example, in Roger's original query below, adding DISTINCT would only help if columns y & z were the same for every x - which is unlikely. [...]
3241 56 22_Trigrers in DB2 UDB V614_Callur, Balaji33_Balaji.Callur@CENDANTMOBILITY.COM30_Thu, 1 Feb 2001 12:10:06 -0500182_- Hi
How can I determine whether a column has been updated or not? Is there an equivalent of UPDATE(column-name) function of SQL Server in DB2?
Thanks & Regards, Balaji
3298 105 37_Re: Retrieving the 30 smallest values14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 1 Feb 2001 17:39:28 -0000400_iso-8859-1 To expand on Rogers message, the real answer to the top 30 question is:
SELECT many_columns, important_column FROM any_table WHERE important_column IN (SELECT important_column FROM any_table ORDER BY IMPORTANT_COLUMN DESCENDING FETCH FIRST 30 ROWS)
which, of course, is not supported by DB2 (or anything else that I know of)!
Sorry, just rambling!!
Phil G [...]
3404 139 37_Re: Retrieving the 30 smallest values16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM30_Thu, 1 Feb 2001 13:07:24 -0500437_us-ascii Here's the "real" correct answer ( i.e., as far as "real" coders go - Sorry Phil )....
Open cursor as- previously-defined
Do While counter not equal 30 Fetch row into :HVs process fetched row add 1 to counter End Do
"Grainger, Phil" on 02/01/2001 12:39:28 PM
Please respond to DB2 Data Base Discussion List [...]
3544 74 37_Re: Retrieving the 30 smallest values0_19_Tim.Lowe@STPAUL.COM30_Thu, 1 Feb 2001 12:24:11 -0600519_us-ascii Phil, How about:
SELECT T2.many_columns, T2.important_column FROM (SELECT important_column FROM any_table ORDER BY IMPORTANT_COLUMN DESCENDING FETCH FIRST 30 ROWS) T1 INNER JOIN any_table T2 ON T1.important_column = T2.important_column
(FYI, I like your rambling.)
Thanks, Tim
"Grainger, Phil" To: DB2-L@RYCI.COM Subject: Re: Retrieving the 30 smallest Sent by: DB2 values Data Base Discussion List [...]
3619 114 34_Re: Compact Data for Archive Logs?10_Mark Labby16_mlabby@PHEAA.ORG30_Thu, 1 Feb 2001 13:46:57 -0500456_us-ascii Thanks James/Lee/Rick,
I've forwarded the messages to Mike Gibellino who is our DB2 systems administrator. We had been discussing this and will be using the VTS drives for the logs, so reading forward/backward should not be an issue at all. VTS pretty much means that we are actually working off of the DASD cache rather than the physical tape drives. We are going to turn it on in a test region next week and will take it from there. [...]
3734 81 42_Re: DSN1COPY ERROR MESSAGE - return code 815_Neff, Stephen R16_NeffSR@STATE.GOV30_Thu, 1 Feb 2001 13:49:22 -0500566_iso-8859-1 hello and thanks. the table was segmented and by using the segment parameter it worked!!!!!
-----Original Message----- From: Murley, Michael [mailto:Michael_Murley@BMC.COM] Sent: Thursday, February 01, 2001 12:00 PM To: DB2-L@RYCI.COM Subject: Re: DSN1COPY ERROR MESSAGE - return code 8
Stephen,
Is this a segmented table space ? Segmented spaces can have zero pages embeded, so you have to use the SEGMENT parameter to tell DSN1COPY not to worry when it finds them there. Refer to page 3-59 in the DB2 V5 Utility Guide. [...]
3816 177 20_Re: DSSIZE Parameter0_28_charles.t.schopf@VERIZON.COM30_Thu, 1 Feb 2001 14:50:44 -0500748_us-ascii The fix is ptf UQ45515.
Chuck Schopf Verizon 215-466-2137
"Missy Case" @RYCI.COM> on 02/01/2001 11:53:17 AM
Please respond to "DB2 Data Base Discussion List"
Sent by: "DB2 Data Base Discussion List"
To: DB2-L@RYCI.COM cc: Subject: Re: DSSIZE Parameter
List,
We are DB2 V6 and have started using DSSIZE parm on some new tablespaces we were installing. An interesting bug we found - after you set the DSSIZE parm - ANY ALTER TABLESPACE (even alter to a new bufferpool, PRIQTY, SECQTY... all we have tried) RESET the large (4g usually) DSSIZE to ZERO, an interesting one to research! [...]
3994 43 31_Follow-up on the Index question0_20_John_Lendman@FPL.COM30_Thu, 1 Feb 2001 15:01:53 -0500406_us-ascii I would like to follow up on my previous question about using an index I have set up.
First all I want to thank all who responded. I am reconfirmed that because of the cardinally of the key the index was not chosen. But when you add an order by, DB2 is then force in to using that index. Also when I updated the DB2 catalog for the index that I want it to use, it also pick the index. [...]
4038 77 53_Help with a query conversion -Oracle to UDB 7.1 (AIX)12_Roy Reynolds15_royr@INCYTE.COM30_Thu, 1 Feb 2001 12:09:34 -0800588_us-ascii Here is a chance to show your stuff. Please help me untangle this.
I have an Oracle View I want to convert to UDB 7.1 for AIX. Here it is: Create View V1 as Select T1.Col11 as VCol1, T2.Col21 as VCol2, T2.Col22 as VCol3, T2,Col23 as VCol4, T2.Col24 as VCol5, T2.Col25 as VCol6, T3.Col31 as VCol7, T3.Col32 as VCol8, T4.Col41 as VCol9, T3.Col33 as VCol10, T5.Col51 as VCol11. T6.Col61 as VCol12, T2.Col26 as VCol13, T7.Col71 || T8.Col81 || T8.Col82 as VCol14, T2.Col27 as VCol15 From Table5 T5, Table6 T6, Table8 T8, Table3 T3, Table2 T2, Table4 T4, Table7 T7, Table1 [...]
4116 89 23_Re: parallel processing12_Dallas Focht21_dallas.focht@PMIC.COM30_Thu, 1 Feb 2001 14:29:55 -0600438_us-ascii We do not code SET CURRENT DEGREE either. I believe DB2 is doing that to determine what degree of parallelism should be used at run time. And yes, the cost is considerable. Just curious, look at the PLAN_TABLE and see if any statements have ACCESS_DEGREE = 0. These were causing us the problem. And there were other statements in the same package that had ACCESS_DEGREE > 0 which looked like they were processing just fine. [...]
4206 25 56_Re: INTRDR & stored procedure on db2 v6 os/390 problem ?0_24_lightsey@ITS.STATE.MS.US30_Thu, 1 Feb 2001 14:30:17 -0600319_us-ascii Thanks for the heads up guys but, still no joy - same old abend ( display just prior to the GOBACK shows up in the sysout of the SPAS but the calling program gets the errors below and a -dis procedure(*) shows that db2 still thinks that there is an active thread running the sp even though there is not ) [...]
4232 39 46_what's best way to re-size buffer pools daily?11_Jim Coleman23_Jim.Coleman@STATE.MN.US30_Thu, 1 Feb 2001 14:32:14 -0600581_iso-8859-1 Greetings and salutations all!
We are having a problem with resizing buffer pools from production-to-test mode at night, and then back from test-to-production mode before sun-up. The problem has caused us to come down 3 times over the last week. Ouch! We have installed a newer version of PeopleSoft in our production subsystem. When we test the new version - we alter down production buffer pools (BP5 & 6) and alter up test buffer pools (BP21 & 31). We then issue a STOP DB against all of the databases in the subsystem to get the buffer pools to flush or [...]
4272 27 26_Re: Trigrers in DB2 UDB V69_Rob Crane22_racrane@CONCENTRIC.NET30_Thu, 1 Feb 2001 14:01:50 -0700441_us-ascii Depending on what information you want to get you could have several options ranging from the DB2 Log to comparing the data between recovery points to see if a change occurred.
What are you trying to do?
> "Callur, Balaji" wrote: > > Hi > > How can I determine whether a column has been updated or not? Is there > an equivalent of UPDATE(column-name) function of SQL Server in DB2? > > Thanks & Regards, > Balaji [...]
4300 26 17_Easy QMF Question14_Scott Saunders20_ssaunders@SIEBEL.COM30_Thu, 1 Feb 2001 12:59:36 -0800295_iso-8859-1 Good afternoon!
I am returning to OS390 after working exclusively with DB2 on AIX for a few years. In the process of tuning a couple of SQL statements I seem to recall that there was a handy QMF report for formatting explain output. If so, could someone share it with me? [...]
4327 19 6_db2ext10_Hill, Dave14_DHill@FSCO.COM30_Thu, 1 Feb 2001 16:11:37 -0500460_iso-8859-1 We have a problem with the AIV Extenders for DB2 on OS/390. The sample apps work with the sample data in the sample tables in the sample tablespaces. Now I want to put our data in non-default databases and tablespaces. It seems that when the non-default locations are specified, we can't enable the table for any of the extenders. The SQLCODE returned is -604, A DATA TYPE DEFINITION SPECIFIES AN INVALID LENGTH, PRECISION, OR SCALE ATTRIBUTE. [...]
4347 24 23_Re: parallel processing10_Joe Basile17_jbasile@ARROW.COM30_Thu, 1 Feb 2001 16:00:37 -0500260_iso-8859-1 I am DB2 DBA on OS/390 v5. We have an Oracle DBA using UDB v7 on Unix on atrial basis and he is having a problem trying to describe the structure of a DB2 table in a command processor (no GUI interface tools). Would anyone know how to do this? [...]
4372 56 17_Easy QMF Question15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Thu, 1 Feb 2001 15:16:56 -0600297_us-ascii Goto QMF then Press F6 to enter sql , enter sql stmt , then F2 for running , F12 for Report then F4 to print this report which print is dataset userid.qmf.dsqprint.
Transmit this dataset using FTP in order to use in Microsoft word then use CourierNew font with sixe 8 0r 10 . [...]
4429 149 26_Re: Trigrers in DB2 UDB V614_Callur, Balaji33_Balaji.Callur@CENDANTMOBILITY.COM30_Thu, 1 Feb 2001 16:25:52 -0500262_- Thanks for the response. Basically I am looking for a function in DB2 (if there is any) which can tell me whether an update has happened on a column in a trigger. After a trigger is created, I need to check if a particular column has been updated or not. [...]
4579 154 37_Re: Retrieving the 30 smallest values13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Thu, 1 Feb 2001 14:36:48 -0700281_iso-8859-1 A related query which i haven't used looks like this. It returns only the 10 highest cardf tables and no others. The performance can't be that good because it has to ts scan 10 times. Also there is a limit on how deep you can nest subqueries, 30 might be too many. [...]
4734 25 21_Re: Easy QMF Question13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Thu, 1 Feb 2001 16:49:04 -0500367_iso-8859-1 I believe Scott is looking for a query and a form which condenses the Explain info into a readable 132 column report.
I used to have one of those but I fear I may have lost it when we deep-sixed our DB2 2.3 subsys. I'm sure there's one laying around here somewhere. If someone else doesn't find it first, I hope to find you one tomorrow Scott. [...]
4760 180 26_Re: Trigrers in DB2 UDB V612_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Thu, 1 Feb 2001 16:04:19 -0600528_us-ascii
Hello,
I'm probably missing something here, but how 'bout:
CREATE TRIGGER MYTRIGER NO CASCADE BEFORE UPDATE ON MY_TABLE REFERENCING OLD AS OROW NEW AS NROW FOR EACH ROW MODE DB2SQL
WHEN ( OROW.COL1 <> NROW.COL1 ) BEGIN ATOMIC
BLAH BLAH BLAH ;
END :
"Callur, Balaji" on 02/01/2001 03:25:52 PM
Please respond to DB2 Data Base Discussion List [...]
4941 12 19_Test..please ignore14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Thu, 1 Feb 2001 17:10:05 -0500257_iso-8859-1 Test
================================================ 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.
4954 73 18_Re: DB2/UDB ON AIX11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM30_Thu, 1 Feb 2001 17:10:31 -0500398_us-ascii Hello Rajesh,
Your question is sort of like asking a home builder to construct you a home, but you tell them nothing about the property, how large your family is, your spouse's favorite colors, or what you can afford.
How you set up your database depends on a lot of things such as the available hardware, recovery requirements, OLTP/web application, or data warehouse? [...]
5028 57 26_Re: Trigrers in DB2 UDB V611_Jakobs, Jef18_JJakobs@HARTIC.COM30_Thu, 1 Feb 2001 16:14:43 -0600581_iso-8859-1 Balaji,
I'm not sure what you mean, but you might be looking for something like
CREATE TRIGGER schema_name.trigger_name NO CASCADE BEFORE UPDATE ON schema_name.table_name REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL WHEN old.column_name <> new.column_name signal sqlstate 'PR001' ('Some message')
Now this is how it works on AIX/NT, and instead of an error message, you might want to do different things... The syntax on OS/390 is probably slightly different too, but I've never used triggers there, so I really wouldn't know :-) [...]
5086 220 41_Re: Easy QMF Question (format plan_table)14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 2 Feb 2001 10:40:55 +11001125_iso-8859-1 Here's one I used in V4:
SELECT COLLID, PROGNAME, VERSION , QUERYNO, QBLOCKNO , PLANNO, METHOD, TNAME, TABNO , ACCESSTYPE, MATCHCOLS , ACCESSNAME, INDEXONLY , SUBSTR('.......Y',DAY(DATE('000100'||SUBSTR(HEX(SORTN_UNIQ ),2,1)) , SUBSTR('.......Y',DAY(DATE('000100'||SUBSTR(HEX(SORTN_JOIN ),2,1)) , SUBSTR('.......Y',DAY(DATE('000100'||SUBSTR(HEX(SORTN_ORDERBY),2,1)) , SUBSTR('.......Y',DAY(DATE('000100'||SUBSTR(HEX(SORTN_GROUPBY),2,1)) , ' '|| SUBSTR('.......Y',DAY(DATE('000100'||SUBSTR(HEX(SORTC_UNIQ ),2,1)) , SUBSTR('.......Y',DAY(DATE('000100'||SUBSTR(HEX(SORTC_JOIN ),2,1)) , SUBSTR('.......Y',DAY(DATE('000100'||SUBSTR(HEX(SORTC_ORDERBY),2,1)) , SUBSTR('.......Y',DAY(DATE('000100'||SUBSTR(HEX(SORTC_GROUPBY),2,1)) , TSLOCKMODE , PREFETCH, COLUMN_FN_EVAL, MIXOPSEQ, TIMESTAMP, CREATOR , CORRELATION_NAME, JOIN_TYPE , APPLNAME FROM &COLLID.PLAN_TABLE WHERE PROGNAME = &PROG ORDER BY COLLID, APPLNAME, PROGNAME, VERSION , TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO if I ever get around to re-writing it for V5, I'ld use CASE WHEN SORTN_UNIQ='N' THEN '.' ELSE SORTN_UNIQ END instead of [...]
5307 16 10_DB/Artisan17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Thu, 1 Feb 2001 15:40:26 -0800465_us-ascii Does anyone have any info or experience with a product called DB/Artisan from Embarcadero. It is a workstation based administrator tool for Oracle, Sybase, MS SQL etc. etc and also for DB2 on OS/390.
Thanks.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
5324 59 14_Re: DB/Artisan10_Jiang Tong30_jiang@INTREPID.CDG-HARGRAY.COM30_Thu, 1 Feb 2001 18:07:59 -0600580_us-ascii We have DB/Artisan and Change Manager in our shop. It is a decent product. If your administration work is only on DB2 (UDB mainly, I am not sure if they are already supporting DB2 on OS/390), Quest Software's Quest Central for DB2 is a better choice. However if you routinely migrate schema from DB2 to Oracle or vice versa, DB/Artisan and Change Manager will be very useful, though there is a bug - Change Manager doesn't correctly translate DB2's timestamp fields to Oracle's date fields, instead it just treat them as fixed length character field - I had to edit [...]
5384 135 57_Re: Help with a query conversion -Oracle to UDB 7.1 (AIX)14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 2 Feb 2001 11:13:02 +1100504_iso-8859-1 Don't know AIX but try:
select ... from table1 t1, table2 t2 left outer join table3 t2 on T2.Col28 = T3.Col34 left outer join table7 t7 on T3.Col35 = T7.Col72 left outer join table8 t8 on T3.Col36 = T8.Col82 and T3.Col37 = T8.Col83 and T3.Col38 = T8.Col81 left outer join table5 t5 on T3.Col34 = T5.Col52 and T5.Col53 = 'LITERAL1' left outer join table6 t6 on T3.Col34 = T6.Col62 and T6.Col63 = 'LITERAL2' left outer join table4 t4 on T8.Col84 = T4.Col42 Where T1.Col12 = T2.Col21 [...]
5520 13 56_Re: INTRDR & stored procedure on db2 v6 os/390 problem ?0_17_JTonchick@AOL.COM28_Thu, 1 Feb 2001 19:57:12 EST307_US-ASCII Try changing the COBOL program from STOP RUN to END JOB.
================================================ 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.
5534 191 37_Re: Retrieving the 30 smallest values13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Thu, 1 Feb 2001 19:04:43 -0600465_iso-8859-1 Daniel,
You're right, the performance of this query wouldn't be too good since all comparisons to the 11 non-correlated subqueries are stage 2. They can be easily converted to indexable by removing the mismatch of nullability with CARDF (ie. add coalesce).
Strap a DESC (or ASC in V7) index on CARDF and the query will fly. This is easily the best performing of the non "Simple ORDERed Cursor & Fetch" solutions due to the I1 fetches. [...]
5726 78 14_Re: DB/Artisan5_chris22_hhardy1@HOUSTON.RR.COM30_Thu, 1 Feb 2001 18:43:39 -0800405_iso-8859-1 Not 100% sure but those folks @ CA with Enterprise Manager surely would address concerns like this, these days they may refer to it as ManageIT something something, check it out.....
----- Original Message ----- From: "Jiang Tong" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, February 01, 2001 4:07 PM Subject: Re: DB/Artisan [...]
5805 36 14_Re: DB/Artisan5_chris22_hhardy1@HOUSTON.RR.COM30_Thu, 1 Feb 2001 18:46:27 -0800437_iso-8859-1 it's some good stuff.... They have a track record and its power is cross platform migration, schema, etc....
they had a new release about six months back and it was all that one could ask for.... check it out....
----- Original Message ----- From: "Charles Jambrosic" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, February 01, 2001 3:40 PM Subject: DB/Artisan [...]
5842 75 14_Re: DB/Artisan12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Fri, 2 Feb 2001 12:24:20 +1100369_- Any news about whether it supported DB2 for OS/390.
I had a look at ER/Studio about 9 months ago off a IDUG demo disk. I was pretty impressed with it's modelling capabilities, but was dismayed when I couldn't produce OS/390 DB2 DDL. I wouldn't mind looking at it again some time (if I ever get a moment to spare), if it could generate the DB2 OS/390 DDL. [...]
5918 21 14_Re: DB/Artisan0_19_Tim.Lowe@STPAUL.COM30_Thu, 1 Feb 2001 19:59:24 -0600401_us-ascii Nick, We have just started using ER/Studio, and it can now generate what it calls DB2 OS/390 DDL (according to it's pull-down menu). BUT, it does have several syntax problems with what it generates, such as not creating any tablespaces (!!). However, I have been told that ER/Studio will be delivering the ability to generate DDL for database and tablespace objects in a future release. [...]
5940 35 30_OC7 and data does not rollback25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG30_Fri, 2 Feb 2001 12:08:38 +0800348_us-ascii Hi All,
I have a scenario in production and development where the application team claims that they are encountering data exception (OC7) but the data updated to DB2 is not rolled back after the last successful commit. I checked the program in question and also used xpediator in test environment and can simulate the problem. [...]
5976 67 34_Re: OC7 and data does not rollback16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 2 Feb 2001 10:10:56 +0530363_- I think i am sure data is not rolled back if application fails with OC7, it gets rollback only if the error is related to DB2. Please check the Archive, there was some real good discussion on this sometimes 6-7 months back(if i can recall correctly). I do not it in my archive. However, if not find in the archive, u will be getting lots of answers again. [...]
6044 63 41_Re: Easy QMF Question (format plan_table)15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM30_Fri, 2 Feb 2001 16:14:14 +1100613_- > This is a V5 one (although not the prettiest one I've seen, just the > prettiest one I've got) > > SELECT > PROGNAME AS PROGRAM > ,SUBSTR(DIGITS(QUERYNO),7,4) AS QRY# > ,SUBSTR(DIGITS(QBLOCKNO),4,2) AS QB > ,SUBSTR(DIGITS(PLANNO),3,3) AS PLNNO > ,SUBSTR(DIGITS(METHOD),4,2) AS MD > ,SUBSTR(TNAME,1,8) AS TNAME > ,SUBSTR(DIGITS(TABNO),3,3) AS TB# > ,ACCESSTYPE AS AT > ,SUBSTR(DIGITS(MATCHCOLS),4,2) AS MC > ,SUBSTR(ACCESSNAME,1,8) AS ACCNAME > ,INDEXONLY AS IX > ,' ' || > CASE SORTN_JOIN > WHEN 'N' THEN '.' WHEN 'Y' THEN 'Y' ELSE 'X' END || > CASE SORTN_UNIQ > WHEN 'N' THEN '.' WHEN 'Y' THEN 'Y' ELSE [...]
6108 120 34_Re: OC7 and data does not rollback0_19_Tim.Lowe@STPAUL.COM30_Thu, 1 Feb 2001 23:23:27 -0600600_us-ascii This sounds like the old IKJEFT01 vs. IKJEFT1B issue. In either case, the DB2 rollback does occur when the application program abends, but with IKJEFT01, you get a "bad return code" instead of an MVS abend, and therefore you will take the MVS "good" dataset disposition. By switching the IKJEFT1B, you get an abend when your program abends, and therefore you get the correct MVS dataset disposition. The real point is that the MVS dataset disposition and the DB2 rollback are not the same when a program abends if you are using IKJEFT01, therefore you should use IKJEFT1B in background [...]
6229 57 34_Re: OC7 and data does not rollback14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Fri, 2 Feb 2001 16:42:27 +1100354_iso-8859-1 DB2 will rollback if the program abends. The problem in this case is that after the 0C7 abend (or to get pedantic - just like an OS/390 system is: a 0C7 abend is _requested_), LE gets notified and turns the abend into an return code 8 (so, pedantically, the program doesn't actually abend), DB2 never sees the abend, so doesn't rollback. [...]
6287 42 32_Re: Stored Procedure 5.1 and 6.110_teldb2kals22_teldb2kals@TELSTRA.COM30_Fri, 2 Feb 2001 17:57:12 +1000498_ISO-8859-1 Jose,
I think u might be able to use the SPB for v5.1 with the refresh/fixes applied. Refer to the following site for more info:
ftp://ftp.software.ibm.com/software/os390/db2server/fixes/db2apars/sqlprocs.tx t
It talks about all u need for using SPB.
HTH.
Kals
-----Original Message----- From: Jose' Gomez [SMTP:Jose_Gomez@WENDYS.COM] Sent: Tuesday, January 30, 2001 2:41 AM To: DB2-L@RYCI.COM Subject: Stored Procedure 5.1 and 6.1 [...]
6330 149 34_Re: OC7 and data does not rollback16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 2 Feb 2001 14:04:33 +0530637_iso-8859-1 Thanks for the correction Tim. It nothing more than reverse. My opologies.
Regards, Sanjeev
> -----Original Message----- > From: Tim.Lowe@STPAUL.COM [SMTP:Tim.Lowe@STPAUL.COM] > Sent: Friday, February 02, 2001 10:53 AM > To: DB2-L@RYCI.COM > Subject: Re: OC7 and data does not rollback > > This sounds like the old IKJEFT01 vs. IKJEFT1B issue. In either case, the > DB2 rollback does occur when the application program abends, but with > IKJEFT01, you get a "bad return code" instead of an MVS abend, and > therefore you will take the MVS "good" dataset disposition. By switching > the IKJEFT1B, you get an [...]
6480 42 59_Re: Impact of online reorg at the partition level on an NPI10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 2 Feb 2001 10:35:33 +0100432_us-ascii Hi Bill,
I have Craig Friske's article and I agree with him, even if I don't use online reorg too often until now.
It's better to reorg at tablespace level to avoid the build2 fase, the 'reorg' of logical partition of NPIs it's a very slow
process (even in recovery).
It's well-know that sometimes it's better to drop NPIs indexes before reorganizing/loading a tablespace, but the benefits [...]
6523 71 59_Re: Impact of online reorg at the partition level on an NPI13_Johnson, Bill18_bjo23@ALLSTATE.COM30_Fri, 2 Feb 2001 07:04:38 -0600552_- Max, Thanks for responding and yes I'd like to get a copy of that document if you have it? We have some enormous tables and the focus of the company is to provide 24 X 7 availability due to an internet directive.(trying to sell insurance) Even using online reorg on some of these large tablespaces, gives us some problems. When we try and reorg at the partition level to save space, we take an outage during the build2 phase. Plus the unknown(to us) impact on the NPI organization. When we reorg at the tablespace level, we have a space problem [...]
6595 90 14_Re: DB/Artisan15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU30_Fri, 2 Feb 2001 08:11:51 -0500650_iso-8859-1 We are communicating with them now about this product - DBArtisan 5.3 for OS/390
-----Original Message----- From: Cianci, Nick [mailto:Nick.Cianci@TEAM.TELSTRA.COM] Sent: Thursday, February 01, 2001 8:24 PM To: DB2-L@RYCI.COM Subject: Re: DB/Artisan
Any news about whether it supported DB2 for OS/390.
I had a look at ER/Studio about 9 months ago off a IDUG demo disk. I was pretty impressed with it's modelling capabilities, but was dismayed when I couldn't produce OS/390 DB2 DDL. I wouldn't mind looking at it again some time (if I ever get a moment to spare), if it could generate the DB2 OS/390 DDL. [...]
6686 12 14_Re: DB/Artisan15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM30_Fri, 2 Feb 2001 07:30:05 -0600326_us-ascii Yes, it is a great product. I have used it to administer Sybase and Oracle.
================================================ 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.
6699 28 8_DSNJU0030_19_mike.holmans@BT.COM30_Fri, 2 Feb 2001 14:03:09 -0000421_- DB2 for OS/390 V5 (reasonably up-to-date on maintenance), OS/390 2.8.
I want to assign TCP/IP ports to a subsystem so I can contact it from my pc. As far as I can see, all that is *necessary* at the DB2 end of things on the OS/390 box is to change the communication record in the BSDS. (There may well be other ZPARM changes it would be desirable to make, but I'm talking about the minimum necessary here.) [...]
6728 49 26_Re: Trigrers in DB2 UDB V611_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Fri, 2 Feb 2001 09:21:47 -0500330_ISO-8859-1 Balaji
Jef's solution works on DB2 OS/390 V6 also. If you only want the trigger to fire if a specific column is updated, the you can include this in the trigger definition on all DB2 platforms:
CREATE TRIGGER xxx [NO CASCADE BEFORE|AFTER] UPDATE OF colname ON table REFERENCING NEW AS N OLD AS O ... [...]
6778 97 37_Re: Retrieving the 30 smallest values14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 2 Feb 2001 14:27:52 -0000334_iso-8859-1 I need to read the V7 doc again, but I wasn't sure whether you can have FETCH FIRST n ROWS in a SUBQUERY??
I'd hate to mislead before V7 is even GA!!
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...]
6876 173 37_Re: Retrieving the 30 smallest values14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 2 Feb 2001 14:31:19 -0000429_iso-8859-1 I like this thread - we could go on forever!!
My 'correct' answer (and I was trying to avoid host programs - I got into some bother last time I suggested people not write entire applications in SQL!!) is
open cursor (descending on important_column)
count = 0 do while count < 30 OR important_column = last_important_column fetch last_important_columm = important_column count = count + 1 end [...]
7050 51 23_Re: parallel processing16_Christopher Matt14_cjm@US.IBM.COM30_Fri, 2 Feb 2001 09:26:52 -0500771_us-ascii db2 "describe select * from creator.tablename"
----------------------------------------------------------------------------
Christopher Matt DB2 Support IBM , Southbury, Ct. Phone: 203-486-3436 e-mail: cjm@us.ibm.com
Joe Basile @RYCI.COM> on 02/01/2001 04:00:37 PM
Please respond to jbasile@arrow.com
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Subject: Re: parallel processing
I am DB2 DBA on OS/390 v5. We have an Oracle DBA using UDB v7 on Unix on atrial basis and he is having a problem trying to describe the structure of a DB2 table in a command processor (no GUI interface tools). Would anyone know how to do this? [...]
7102 23 51_Display of Application Default Values from DSNHDECP20_Cable, George (CORP)29_George.Cable@CORPORATE.GE.COM30_Fri, 2 Feb 2001 10:09:23 -0500332_iso-8859-1 Have request from application development team to display the application default values from DSNHDECP module. I am aware of the DSNDDECP macro, and that DB2PM can do this. I was just trying not to reinvent the wheel, and I can not get DB2PM. Does anyone know of a freeware version in batch or REXX or CICS? Thanks. [...]
7126 100 23_Re: parallel processing13_Mark Nicholls20_Mark.Nicholls@DVG.DE30_Fri, 2 Feb 2001 16:24:06 +0100264_iso-8859-1 >I am DB2 DBA on OS/390 v5. We have an Oracle DBA using UDB v7 on Unix on >atrial basis and he is having a problem trying to describe the structure of >a DB2 table in a command processor (no GUI interface tools). Would anyone >know how to do this? [...]
7227 35 12_Re: DSNJU00310_Bill Brown25_bill_brown@SPRINGMAIL.COM30_Fri, 2 Feb 2001 10:29:28 -0500561_- Mike, You should be able to go into SDSF, look in the MSTR started task for the desired subsystem and do a find on "LOCATION".
On Fri, 2 Feb 2001 14:03:09 -0000 mike.holmans@BT.COM wrote:
DB2 for OS/390 V5 (reasonably up-to-date on maintenance), OS/390 2.8.
I want to assign TCP/IP ports to a subsystem so I can contact it from my pc. As far as I can see, all that is *necessary* at the DB2 end of things on the OS/390 box is to change the communication record in the BSDS. (There may well be other ZPARM changes it would be [...]
7263 35 32_Invalid HEX characters in column10_Mark Labby16_mlabby@PHEAA.ORG30_Fri, 2 Feb 2001 11:06:58 -0500372_us-ascii We have some user CICS applications where the user is allowed to enter free form text that gets stored in variable length columns in the database. They use this for a scratch pad to keep track of user contacts and such. Apparently, when they have their emulator and/or keyboard setup incorrectly, they end up getting invalid hex characters in the text area. [...]
7299 71 37_SQLJ Stored Procedure - size limit ??14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Fri, 2 Feb 2001 11:28:36 -0500455_iso-8859-1 Hi,
We have a SQLJ stored procedure here (DB2 V7.1 on NT) which has a 62 KB file size. Is there a size limit for SQLJ stored procs ??? This particular stored proc has a number of methods inside it, besides the customary static void method. When I try to build it using the Stored Procedure Builder, it just hangs on the 'Binding...' step. The db2profc.exe comes up on a DOS window, and I have to end the task using NT Task Manager. [...]
7371 92 36_Re: Invalid HEX characters in column13_Terry McCombs20_tmccomb@SEDGWICK.GOV30_Fri, 2 Feb 2001 10:31:15 -0600385_iso-8859-1 I think you would want to push this into the application code on CICS, unless data is being entered into the DB2 tables in another manner, also. It's fairly easy to come up with the displayable characters, and the program then substitutes a space for a non-displayable. Here's something similar that I did when displaying the character version of a hexidecimal string: [...]
7464 53 34_Re: OC7 and data does not rollback13_Pranav Sampat21_psampat@PACIFICEX.COM30_Fri, 2 Feb 2001 08:33:49 -0800641_US-ASCII It can be because of abend termination enclave parameter for LE imigh have been set as RETCODE. You can either modify CEEDOPT or CEEUOPT to have ABTERMENC(ABEND) and the data would be rolled back
Thanks, Pranav
>>> SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG 02/01/01 08:08PM >>> Hi All,
I have a scenario in production and development where the application team claims that they are encountering data exception (OC7) but the data updated to DB2 is not rolled back after the last successful commit. I checked the program in question and also used xpediator in test environment and can simulate the problem. [...]
7518 79 36_Re: Invalid HEX characters in column0_27_jim.leask@RS-COMPONENTS.COM30_Fri, 2 Feb 2001 16:42:33 -0000531_iso-8859-1 Mark,
I think the translate built-in function is what you are looking for, see example below:
SELECT TRANSLATE('ABCDEFGHI ' CONCAT X'08',' ','ABC' CONCAT X'08') FROM SYSIBM.SYSDUMMY1
Basically the above will replace letters A,B,C and the x'08' character with a space.
Hope it helps, Jim.
-----Original Message----- From: Mark Labby [mailto:mlabby@PHEAA.ORG] Sent: 02 February 2001 16:07 To: DB2-L@RYCI.COM Subject: Invalid HEX characters in column [...]
7598 101 41_Re: SQLJ Stored Procedure - size limit ??12_Myron Miller22_myronwmiller@YAHOO.COM30_Fri, 2 Feb 2001 09:00:00 -0800532_us-ascii The REXX script (DSNTPSMP) that processes SPs (BUILD) from the SPB has a limit of 3800 bytes. Any SP larger than this will have problems. --- "Sinha, Abhijit" wrote: > Hi, > > We have a SQLJ stored procedure here (DB2 V7.1 on > NT) which has a 62 KB file > size. Is there a size limit for SQLJ stored procs > ??? This particular stored > proc has a number of methods inside it, besides the > customary static void > method. When I try to build it using the Stored > Procedure Builder, it just > [...]
7700 18 30_DB2/OS390 V7 Utility Packaging11_Lori Bewley21_lori.bewley@SABRE.COM30_Fri, 2 Feb 2001 10:38:52 -0600389_- I've just been reading the Q1 2001 issue of DB2 Magazine, specifically the article by Richard Yevich called Powering Up:DB2 Utilities. There is a reference to the DB2 core utilities being delivered with the core product at no additional charge. Exactly which utilities are included in this "core" set? I can't seem to find the answer to this using the link provided in the article. [...]
7719 40 16_IO-Module issues15_Mohammed Nayeem21_db2udbxpert@YAHOO.COM30_Fri, 2 Feb 2001 10:11:22 -0800518_us-ascii Hello DB2List
Mainline program writes record in the table via IOmodule1.
IOmodule1 compiled against tabl qualifier1 of database1 mainline program compiled against tab2 qualifier2 of database2 where structure of tab1 & tab2 is same in different test databases database1 & database2 respectively.
My questions : 1)Is this is possible , is compilation is possible 2)If so , in which table it writes 3)If not what abend one should get 4)If not why no abend and no record written happens [...]
7760 29 34_Re: DB2/OS390 V7 Utility Packaging13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Fri, 2 Feb 2001 12:42:18 -0500413_iso-8859-1 Hi Lori, I haven't seen an official list, but according to Roger Miller -- at the US DB2 Tech Conf last October --who'll probably jump in here as soon as he sees your question (or maybe he's got a DB2 trigger set up in his Lotus Notes to alert him if anyone uses his name in vain), if there is a product offering from a competitor with substitutable function, the utility will probably be priced. [...]
7790 21 20_Trigger., UDB ON AIX8_bharat r20_bharat_321@YAHOO.COM30_Fri, 2 Feb 2001 10:04:07 -0800586_us-ascii Hi.. I have a requirement, as soon as I insert one record in a table a Java program should be invoked. Can I write a trigger for that? If yes Please let me know..
Bharat
__________________________________________________ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
7812 68 10_SQL Puzzle16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU30_Fri, 2 Feb 2001 13:57:30 -0500456_iso-8859-1 Hey Folks... Question on why a query won't use an index in one situation, but will in another... Here's the table definition:
COLNO NAME LENGTH COLTYPE NULLS KEYSEQ ---------+---------+---------+---------+---------+---------+- 1 CFK_ACCOUNT_NUMBER 12 CHAR N 1 2 CFK_MRNO 11 CHAR N 2 3 CFK_CLINIC_CODE 3 CHAR N 3 4 CFK_VISIT_DATE 10 TIMESTMP N 4 5 CFK_PHYS_CARE_ID 3 CHAR N 5 6 CFK_PHYNO 5 CHAR N 0 7 C_INSERTION_DATE 10 TIMESTMP N 0 [...]
7881 30 12_Re: DSNJU00314_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US30_Fri, 2 Feb 2001 14:11:56 -0500469_US-ASCII Mike, I understand you say you want to JU003 for the purpose of TCPIP access to DBxx:
(DDF LOCATION=DBxx,LUNAME=DBxx,PORT=446,RESPORT=5020 )
If that's correct, then no, -DBxx STOP DDF is not what you need. You can't touch the BSDS that way. Your DBxxDIST (DDF) address space must be down, but so must your DB2 altogether to run DSNJU003. And don't forget to establish the LUNAME thru VTAM as an APPLLID SYSMODENT, prior to all this. [...]
7912 24 46_Possible UDB/AIX position in Hartford, CT Area0_22_BILL_GALLAGHER@PHL.COM30_Fri, 2 Feb 2001 14:25:00 -0500357_us-ascii Hi,
We have an opening for a Database Administrator for UDB on AIX at our shop. We are located in the Hartford CT/Springfield MA metro area. Anybody interested in this position can feel free to contact me via email for more information. Resumes are welcome.
Thanks,
Bill Gallagher, DBA Phoenix Home Life Enfield, CT 06083 [...]
7937 31 20_anyone else see this12_Stan Goodwin29_STANLEY_GOODWIN@MECH.DISA.MIL30_Fri, 2 Feb 2001 14:40:40 -0500547_us-ascii Scenario is: Program has been running forever: The program does a cursor processing where it is reading all the rows on a table. Based on a value in the row certain processing occurs and then the row is deleted. The delete statement was not doing a 'Where current of cursor' to do the delete. Now all of a sudden when the program runs under 6.1 that after the row is fetched and then trying to do the direct delete(where clause is used to set selection criteria) the program gets a not found but when examining the table via QMF the [...]
7969 93 14_Re: SQL Puzzle12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Fri, 2 Feb 2001 13:54:22 -0600727_us-ascii Pray tell, what columns are indexed?
Best regards, John P. Higgins Performance Specialist SAP BASIS Team
Deere & Company Voice: (309) 748-1625 Attn: GTTC East FAX: (309) 748-1605 One John Deere Place email: HigginsJohn@JohnDeere.com Moline, IL 61265-8098
> -----Original Message----- > From: Dempsey, Michael [SMTP:MDempse@UNCH.UNC.EDU] > Sent: Friday, February 02, 2001 12:58 > To: DB2-L@RYCI.COM > Subject: SQL Puzzle > > Hey Folks... > Question on why a query won't use an index in one situation, but will > in another... Here's the table definition: > > COLNO NAME LENGTH COLTYPE NULLS KEYSEQ > ---------+---------+---------+---------+---------+---------+- > 1 CFK_ACCOUNT_NUMBER 12 CHAR [...]
8063 145 14_Re: SQL Puzzle0_24_db46@DAIMLERCHRYSLER.COM30_Fri, 2 Feb 2001 14:56:04 -0500383_us-ascii Well you might want to take the A.CFK_VISIT_DATE out of the 'GROUP BY' clause. This would seem to only get you the same as doing a distinct on those four columns. This may be some of the problem. In other words the MAX and GROUP BY are doing the same thing as DISTINCT. Now this is on a guess that C_LNAME and C_FNAME are part of the attributes dependent on CFK_PHYNO. [...]
8209 86 14_Re: SQL Puzzle13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 2 Feb 2001 13:55:53 -0600546_iso-8859-1 Michael,
The most obvious answer to me is that the second query is able to do a matching index scan on the primary key index (matchcols=2) whereas the first query is not able to match on a leading index column (based on the data you provided).
Even if there is an index available, then a rough guide is that if more than 25% of the data is going to be read via an index, then a tablespace scan is more efficient. The optimizer has probably determined that this is the case for query 1 based on available statistics. [...]
8296 99 14_Re: SQL Puzzle16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU30_Fri, 2 Feb 2001 15:26:27 -0500572_iso-8859-1 Sorry Folks... There is a unique index on the first five columns. The ts scan is happening on the tpatphys tablespace
-----Original Message----- From: Terry Purcell [mailto:Terry_Purcell@YLASSOC.COM] Sent: Friday, February 02, 2001 2:56 PM To: DB2-L@RYCI.COM Subject: Re: SQL Puzzle
Michael,
The most obvious answer to me is that the second query is able to do a matching index scan on the primary key index (matchcols=2) whereas the first query is not able to match on a leading index column (based on the data you provided). [...]
8396 123 41_Re: SQLJ Stored Procedure - size limit ??14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Fri, 2 Feb 2001 15:32:58 -0500488_iso-8859-1 We have a few other SQLJ procedures of 5, 15, 17 & 34 KB each and they do not give any problems during build in SPB. Even the SP I mentioned originally in this e-mail builds fine when the size is around 57 KB.
Any workaround/ solution ???
Thanx, Abhijit
-----Original Message----- From: Myron Miller [mailto:myronwmiller@YAHOO.COM] Sent: Friday, February 02, 2001 12:00 PM To: DB2-L@RYCI.COM Subject: Re: SQLJ Stored Procedure - size limit ?? [...]
8520 33 40_table space partitioning on a Unix (AIX)15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU30_Fri, 2 Feb 2001 15:33:28 -0500335_iso-8859-1 Listserv UDB gurus! We have experience with DB2 on 390, but less than little on other platforms
Does anyone have any rules of thumb on table space partitioning on a Unix (AIX) computer? The kinds of questions we are asking are generally about the relationship between performance and physical placement on disk. [...]
8554 141 14_Re: SQL Puzzle12_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US30_Fri, 2 Feb 2001 14:56:34 -0600489_US-ASCII Terry Purcell's answer still applies. In the first query, you are only supplying values for the 2nd and 5th columns of your unique index. The optimizer will still have to scan the index to return all the possibilites from the index pages. Then since you are referencing a column not in the index (CFK_PHYNO), you still have to go to the data pages to fulfill the request. If the optimizer "thinks" it's faster to not use the index and go directly to the data pages, it will. [...]
8696 17 25_Password column in DB2 ??14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Fri, 2 Feb 2001 16:07:31 -0500366_iso-8859-1 All,
Is there a way for a table column to encrypt and store a password value in DB2 ?
Abhijit Sinha
================================================ 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.
8714 161 14_Re: SQL Puzzle19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Fri, 2 Feb 2001 15:24:03 -0600574_iso-8859-1 Terry, Doug, Help me here -- given, RUNSTATS done on all 5 columns of the index. If, for columns 2 and 5 of the index, a good filter factor was discovered by the optimizer, wouldn't DB2 then use the index?
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 [...]
8876 45 41_CORRECTION: Tridex Education Announcement11_Gerri Lloyd17_glloyd@US.IBM.COM30_Fri, 2 Feb 2001 14:38:19 -0700626_us-ascii PLEASE NOTE CORRECTION TO SHERYL LARSEN SESSION IS FEBRUARY 20TH NOT MARCH 20TH.
Dear Tridex Members,
We are happy to announce two additional one day educational classes. Due to the overwhelming demand for Sheryl Larsen's SQL class, we have scheduled an additional class on February 20, 2001. In addition, we are fortunate to be able to offer Interpreting Key Counters in DB2 V5 /V6 Performance by Horacio Terrizzano of the IBM Silicon Valley Lab on Friday March 2, 2001. The classes will be taught from 8:30 - 4:00, IBM Corporation, 9th Floor, 590 Madison Ave. (between 56th &57th) NY, NY [...]
8922 15 29_Re: Password column in DB2 ??8_bharat r20_bharat_321@YAHOO.COM30_Fri, 2 Feb 2001 13:45:35 -0800402_us-ascii __________________________________________________ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
8938 23 18_Trigger on UDB/AIX8_bharat r20_bharat_321@YAHOO.COM30_Fri, 2 Feb 2001 13:46:26 -0800594_us-ascii Hi.. I have a requirement, as soon as I insert one record in a table a Java program should be invoked. Can I write a trigger for that? If yes Please let me know..
Bharat
__________________________________________________ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
8962 230 14_Re: SQL Puzzle12_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US30_Fri, 2 Feb 2001 16:10:29 -0600356_US-ASCII Here is my non-expert reply. Even if you had a superior filter factor on the 2nd column (let's say it narrowed your result set to 1 row!!) Without the 1st column of the index supplied, the optimizer is still scanning the index. This scanning of the index and the possible number of qualifying rows could be large enough to not use the index. [...]
9193 148 41_Re: SQLJ Stored Procedure - size limit ??14_Richard Yevich17_ryevich@YAHOO.COM30_Fri, 2 Feb 2001 14:57:53 -0800423_us-ascii Abhijit,
Technically, a stored procedure in the SQL Procedure Language, is a single SQL statement, therefore allowed to be 32,767 bytes, which is the current precompiler limit. However, that limit is supposedly being increased significantly in the near term, probably to at least 1 MB, maybe more (IBM's projection, not yet fact). It is currently a precompiler issue as well as an SQL Procedure issue. [...]
9342 73 34_Re: DB2/OS390 V7 Utility Packaging14_Richard Yevich17_ryevich@YAHOO.COM30_Fri, 2 Feb 2001 15:06:36 -0800366_us-ascii All,
Unfortunately the link provided by IBM to DB2 magazine, and hence got into the article is incorrect. It is actually only an error at the end .../db2imstools not .../db2/imstools. HOWEVER, IBM has not updated it yet with the breakout of the utilitites. There will NOT be a final list, until GA I am told, which is actually good and correct. [...]
9416 56 50_Re: what's best way to re-size buffer pools daily?14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Fri, 2 Feb 2001 11:41:30 -0600404_iso-8859-1 The first thing is to check your maintenance levels. If you are way behind, you will be missing some important fixes, especially for HP resizing.
Assuming this is OK, then you should be able to schedule a job in the evening and another in the morning to resize your pools. I do not see a need to stop any databases as part of this process. You definitely do not need to cycle DB2. [...]
9473 94 14_Re: SQL Puzzle12_rl_cotterill24_rl_cotterill@BIGPOND.COM30_Sat, 3 Feb 2001 11:58:33 +1100540_us-ascii Michael, Check the definition of the Host Vatiable to ensure it is not longer than 12 bytes (not an issue under V6 I believe but is for other releases)
Richard
Terry Purcell wrote:
> Michael, > > The most obvious answer to me is that the second query is able to do a > matching index scan on the primary key index (matchcols=2) whereas the first > query is not able to match on a leading index column (based on the data you > provided). > > Even if there is an index available, then a rough guide is that [...]
9568 151 14_Re: SQL Puzzle13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 2 Feb 2001 21:17:55 -0600355_iso-8859-1 I've just caught up with the mail trail, so hope this makes sense.
Richard - What you are referring to is the V6 feature regarding indexability of CHAR & VARCHAR join columns with mismatching lengths. This only applies to join columns (ie. 2 CHAR/VARCHAR columns from different tables in a join), not to host variables or literals. [...]
9720 12 23_LOAD a BLOB in DB2/S39016_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU30_Fri, 2 Feb 2001 20:29:00 -0800427_- I am trying to use DB2 S390 V6 LOAD utility to load a BLOB column into a DB2 table. The BLOB is an FTP'd JPEG file. If you have done this before, I would like to communicate with you.
================================================ 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.
9733 44 29_Re: Password column in DB2 ??14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Sat, 3 Feb 2001 17:39:38 +1100374_iso-8859-1 check the archives. We went through this a month or two ago.
-----Original Message----- From: Sinha, Abhijit [mailto:Abhijit.Sinha@GS.COM] Sent: Saturday, February 03, 2001 8:08 AM To: DB2-L@RYCI.COM Subject: [DB2-L] Password column in DB2 ??
All,
Is there a way for a table column to encrypt and store a password value in DB2 ? [...]
9778 104 20_Re: IO-Module issues14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Sat, 3 Feb 2001 17:50:01 +1100426_iso-8859-1 Mohammed,
Which variety of DB2 is this question related to? I'm assuming it's OS/390.
When you say "compiled against" do you mean the table qualifier was hard coded in the source ("SELECT columns FROM qualifier.table WHERE ...") or do you mean that the table description in a DCLGEN came from a version of the table, but the executable SQL is unqualifier ("SELECT columns FROM table WHERE ..."). [...]
9883 19 17_value for rlferrd12_Rakesh Kumar21_rakesh457@HOTMAIL.COM30_Sat, 3 Feb 2001 07:11:30 -0000553_- ALL, Can anyone specify an optimum value for RLFERRD so that my dynamic sqls also run properly .Or what is the value in ur DB2 environment.
Regards Rakesh _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail 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.
9903 28 27_DB2 UDB EE -> EEE Migration13_Dave Bargeron29_David.Bargeron@BTINTERNET.COM30_Sat, 3 Feb 2001 03:11:07 -0600430_- Hi,
We are running (not production) DB2 UDB EE v7.0 on NT (NetAffinity)- single database. We are considering the option of migrating to EEE, then partitioning the database into across a couple of servers. The primary driver is for reducing backup/recovery window (1tb).
Has anyone any useful tips or gotchas for: - migrating to EEE from EE - backup /recovery - MSCS on EEE in a multiple partition environment [...]
9932 43 27_OFF-TOPIC (but DB2 related)10_Max Scarpa16_mscarpa@CESVE.IT30_Sat, 3 Feb 2001 11:40:54 +0100427_us-ascii We are planning an high level of automation in a production environment and DB2 (of course) will be the in first group of
application.
No problem about how to do this automation, with some intelligent product & powerful hand-made REXXs/Panel it's not
so difficult.
What we want to know, and it's the off-topic argument ( I apologize for this as Achille did many centuries ago) , is IF [...]
9976 46 36_Question for DB2 systems programmers8_ritu zee22_ritu_98_2000@YAHOO.COM30_Sat, 3 Feb 2001 09:51:19 -0800450_us-ascii Hello!
I have been a DB2 'Application DBA' for quite a few years now engaged in Application performance tuning, recovery and other application aspects. I also have some experience in 'System tuning' like buffer pool tuning etc.
However, i am looking for job as a DB2 System Programmer. I am baffled about what additional skills do i require in addition to my current experience for transition to a Systems programmer job. [...]
10023 155 31_Re: DB2 UDB EE -> EEE Migration14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Sat, 3 Feb 2001 17:25:54 -0800593_iso-8859-1 Dave, There are quite a few considerations to be aware of. You will have to identify partitioning keys for all your tables,not such an easy task sometimes, and reload and distribute the data evenly across nodes. Do you have a high speed switch? If not, you could see a degradation in performance due to network time, especially if your data is not partitioned right. You need to ensure that your application can take advantage of parallelism. Backups could run a lot faster if you have the proper number of sessions, buffers, and backup media, etc. I'm not convinced that your [...]
10179 26 11_ART and ORT14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Sat, 3 Feb 2001 21:51:34 -0600623_iso-8859-1 Survey:
Does anyone use or know of any installation that uses the ART and ORT registration tables in DB2 for OS/390?
Regards, Richard
Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums
================================================ 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.
10206 78 37_Re: Retrieving the 30 smallest values13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Sat, 3 Feb 2001 21:49:38 -0600434_iso-8859-1 Phil,
Good idea....I'll keep the thread open to squeeze the last ounce of life out of it (much to some people's annoyance I'm sure).
Forget host programming (Grin)
This example will retrieve you the top 30 using recursive SQL from the UDB (non-OS390) sample EMPLOYEE table. - No limits to subquery nesting - No cartesian products - And each probe is an Index-One Fetch (Create ASC index on EMPNO) [...]
10285 40 51_Display of Application Default Values from DSNHDECP10_Alan Smith25_alancsmith@BTINTERNET.COM30_Sun, 4 Feb 2001 09:29:33 +0000645_US-ASCII George, You can call stored procedure DSNWZP to get DSNZPARM and DSNHDECP info out in printable format. I have a C++ program or can give you some example code if required.
Alan Smith
> ------------------------------ > > Date: Fri, 2 Feb 2001 10:09:23 -0500 > From: "Cable, George (CORP)" > Subject: Display of Application Default Values from DSNHDECP > > Have request from application development team to display the application > default values from DSNHDECP module. I am aware of the DSNDDECP macro, and > that DB2PM can do this. I was just trying not to reinvent the wheel, [...]
10326 122 14_Re: SQL Puzzle25_Robinson, Peter [IBM GSA]33_Peter.J.Robinson@TEAM.TELSTRA.COM30_Mon, 5 Feb 2001 08:59:49 +1100550_iso-8859-1 Hi Michael
I just read the whole thread and have a different slant on your problem: Assuming the scan is causing a problem, and it is caused by the fact that you are not providing the leading column of the index, why not change the index?
First a couple of questions. How is the data structured - cardinality of the columns in the primary key? Was the Primary key designed to have the columns in this order or has it just happened that way? If you can, it might be worth moving them around so that the primary index can [...]
10449 230 50_Re: Help with Partition Table with historical data15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Mon, 5 Feb 2001 10:29:29 +1100659_iso-8859-1 Michael,
I imagine some clever stats tweaking might do the trick. I've got a table here I've artificially partitioned across 9 parts, based on a char(1) column having values '1' to '9'. There's a view on it that omits this column (which is the first column in the table and the primary, clustering, partitioning index) but specifies in the 'where' clause, 'where partioning_column in ('1','2','3','4','5','6','7','8','9')'. All accesses to this table/view are read-only and provide a value for (what is now) the 2nd column. I feared it might become index access but matchcols=0, down from matchcols=1. Instead it became matchcols=2, so [...]
10680 51 30_Re: Adabas to DB2 Conversion !18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Sun, 4 Feb 2001 17:38:23 -0600639_- Howzit Sanjeev?
Have a look on the SAG-L listserver.
Cheers
Bruce
On Thu, 1 Feb 2001 17:24:14 +0530, S, Sanjeev (CTS) wrote:
>Hi All, > > I need to get some idea about Adabas to DB2 conversion. If any >installation have done it or anyone who was involved in these kind of >projects, please share your experience, views and ideas/suggestions. Please >also send me the websites which can give me some information about how to go >about this or any other issues related to this. > >Looking forward to get as many responses as possible. > >Regards, >Sanjeev >This [...]
10732 21 29_Altering 'partitioned' tables15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Mon, 5 Feb 2001 15:53:11 +1100341_- Fellow DB2 for OS/390 colleagues,
It's come to my attention that you can alter a table in a partitioned tablespace to add columns. You can do it in V6, and it appears to also be the case for V5. When was this extremely useful ability introduced? V5?
Just curious; can't seem to find a mention of it in the V5 man-wells. [...]
10754 51 33_Re: Altering 'partitioned' tables16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Mon, 5 Feb 2001 10:55:02 +0530309_- Hi Raymond, I think the facility to add the NOT NULL column by altering the table was available as always. The new feature as known to me is altering the partitioning key values. This is available with V5(with APAR) and in V6 completely.
Please correct me if i am wrong.
Regards, Sanjeev [...]
10806 28 53_Suggestion for "Out of Office AutoReply" Afficionados18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Mon, 5 Feb 2001 17:40:46 +0930426_us-ascii Howzit Folks?
With the advent of the "Out of Office AutoReply" feature in most e-mail software, perhaps subscribers could take the time to change their DB2-L subscription options while out of the office to stop posters being inundated with superfluous "Out of Office" messages.
To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l [...]
10835 56 0_23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Mon, 5 Feb 2001 08:03:09 -0000424_iso-8859-1 ...not that I have heard of .
What Asking Scott Hayes of Database-guys .. he is veyr active in the DB2 AIX world.
..or get youself on the DB2 Email User Group. Mail me if you need details.
kindest regards
Leslie Pendlebury-Bowe etc etc -----Original Message----- From: busayo adebayo [mailto:bayo9@YAHOO.COM] Sent: Wednesday, January 31, 2001 12:48 AM To: DB2-L@RYCI.COM Subject: [...]
10892 98 22_Re: 15-255 Table Joins23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Mon, 5 Feb 2001 08:06:55 -0000339_iso-8859-1 Andy
I know I am not IBM but this sounds a little bit shoddy to me. What did the support guys say when you called in? (Assuming you called in that is).
We had a similar problem (NOT joins) recently and I told the whole world on the PMR I raised exactly what I thought - seemed to get me some action with IBM. [...]
10991 14 15_Re: ART and ORT15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Mon, 5 Feb 2001 02:23:47 -0600424_- Hello Richard
We use these registration tables in our test environment, to make sure that only one plan can create or drop a database. This plan is only granted to the DBAs.
================================================ 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.
11006 60 53_OC7 and data does not rollback - still having problem25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG30_Mon, 5 Feb 2001 16:34:33 +0800440_us-ascii Hi,
I have tested the program using IKJEFT1B and made sure that the ABTERMENC=ABEND and also made sure that TERMTHDACT to UADUMP but the problem still presists. I have receiving user completion code 4039 and the step completion code is 3000. How can I change this completion code to 0C7 so that Db2 will know that it is abnormal EOT. I tested for S222, there is abnormal EOT in DB2 mstr and so the rollback took place. [...]
11067 189 22_Re: 15-255 Table Joins9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Mon, 5 Feb 2001 10:27:50 +0000329_us-ascii Les, Feedback I got from IBM is that they are not looking to go above 15. Obviously, joins with so many tables is not so good from a performance point of view, but we did have some SQL generators that used the new functionality when it became available through UQ29567. This SQL didnt work when we applied UQ39732. [...]
11257 38 22_Re: Trigger on UDB/AIX11_Hamar, Bela22_Bela.Hamar@VARETIS.COM30_Mon, 5 Feb 2001 11:53:04 +0100379_us-ascii Hi Bharat,
you can do it:
1. write your Java program as a UDF and register it 2. create the insert trigger that calls this udf (VALUES is our friend, since the can be a fullselect and VALUES is a fullselect :-) something like:
CREATE TRIGGER ... ... MODE DB2SQL BEGIN ATOMIC VALUES ( () ) ; END [...]
11296 233 22_Re: 15-255 Table Joins23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Mon, 5 Feb 2001 10:48:29 -0000361_iso-8859-1 Andy
..interesting.
Like I said in my post DB2 seems to be a little flaky when you start to stress it in the storage department. This is just my observations and nothing more .. :-)
I must admit that I thought IBM were pushing the increase in "tables in a join" a while back and obviously now have decided to stop the push. [...]
11530 27 13_Exit DSNXVDTX31_=?iso-8859-1?q?Silvia=20Motta?=21_silviamotta@YAHOO.COM30_Mon, 5 Feb 2001 03:15:44 -0800428_iso-8859-1 Hello,
We need to use the DSNXVDTX, In v4 we compiled the exit and worked ok. But now we`re in V6, and i compile the same source, because i don`t find other, and it doesn`t work.
Anybody can help me ? thanks you very much.
Silvia.-
__________________________________________________ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ [...]
11558 40 57_Re: Suggestion for "Out of Office AutoReply" Afficionados16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Mon, 5 Feb 2001 11:29:44 -0000632_- Agree - another way to do it would be to specify that DB2-L will be an exception. At least in OUTLOOK you can specify which addresses NOT to be covered by the Out-of-Office reply.
Steen Rasmussen
> -----Original Message----- > From: Bruce W Williamson [SMTP:Bruce.Williamson@NT.GOV.AU] > Sent: Monday, February 05, 2001 9:11 AM > To: DB2-L@RYCI.COM > Subject: Suggestion for "Out of Office AutoReply" Afficionados > > Howzit Folks? > > With the advent of the "Out of Office AutoReply" feature in most e-mail > software, perhaps subscribers could take the time to change their DB2-L > subscription options while [...]
11599 55 17_Re: Exit DSNXVDTX15_Toine Michielse18_vndobtm@US.IBM.COM30_Mon, 5 Feb 2001 13:14:35 +0100438_us-ascii Hello Silvia,
Can you provide some more details about what does not work? Have you checked availability of the load module at start time of DB2? Have you stop/started DB2 after making the load module abailable to DB2?
Regards,
Toine Michielse
Silvia Motta @RYCI.COM> on 02/05/2001 12:15:44 PM
Please respond to DB2 Data Base Discussion List [...]
11655 49 22_Re: 15-255 Table Joins9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Mon, 5 Feb 2001 12:21:33 +0000334_us-ascii Les, Not sure it is a 'fair call'. I believe that if a software company introduces new functionality then the Customer will use it with the expectation that it will be supported. A fix to a problem (in my view) is resolving the underlying problem rather than just removing the functionality that caused it. Cheers, Andy [...]
11705 80 22_Re: 15-255 Table Joins23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Mon, 5 Feb 2001 12:47:17 -0000276_iso-8859-1 ....I see your point, but then I am seeing IBMs aswell. This is *only* because I have seen so many stoagr problems at my site .. at the end of the day introduction of functionality should be managed .. this seems to have been ripped out rather than taken out. [...]
11786 84 40_Re: Question for DB2 systems programmers11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Mon, 5 Feb 2001 08:25:14 -0500372_iso-8859-1 1. Are SMP/E skills necessary? If yes, can they be acquired just by reading manuals or a course offered by IBM is more appropriate.
Yes. The first part of the DB2 install requires SMP/E as does maintenance. There are some good articles out on www.cbttape.org by Sam Golob as introduction to SMP. Also check out www.naspa.com, you should join Naspa. [...]
11871 76 40_Re: Question for DB2 systems programmers16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 5 Feb 2001 08:26:35 -0500523_iso-8859-1 1) SMP/E skills are a big part of it. Fortunately the SMP/E tasks DB2 folks do are mostly the very basic ones. Some sample JCL from a predecessor plus the manuals should be sufficient. 2) The installation instructions in the manual are pretty good. If you follow the instructions for using the ISPF panels you should have no trouble. &deity help you if you try the PC-based flavor! 3) Please consider IBM's DB2 Systems Administration class. This is a great course for the non-SMP part of a DB2 Sysprog job. [...]
11948 51 12_Re: DSNJU00327_Hilton, Tina, BmS - NMI -PM21_Tina.Hilton@BMSUS.COM30_Mon, 5 Feb 2001 08:26:34 -0500338_iso-8859-1 Harvey,
Why would you set TCPALVER to YES? That doesn't sound like much security to me, or am I misunderstanding something? I thought setting it to yes meant that a password isn't required with the userid. We're just adding the TCP/IP part to our systems, so I'm very interested in this since we have it set to NO. [...]
12000 79 26_Multiple Tables/tablespace14_Scott Trometer22_scott.trometer@RCI.COM30_Mon, 5 Feb 2001 08:47:19 -0500352_iso-8859-1 Listers,
I recall hearing a rule of thumb to limit multiple-table tablespaces to contain tables of similar size...but I don't know the real reason behind it.
I am reviewing an existing tablespace that contains 11 tables with the number of rows ranging from 400 to 7 million. Physical length varies for each table. [...]
12080 90 22_Re: 15-255 Table Joins0_29_paul.packham@POSTOFFICE.CO.UK30_Mon, 5 Feb 2001 13:54:04 +0000399_us-ascii Hi Andy,
Long time no speak ! I have been following this thread with interest & I have to say I agree with you on this one. We are planning to trial a CRM Package on OS/390 (Siebel) which recommends that the value of &SPRMMXT is set to 40.
BTW, I presume you are aware that you can still have up to 255 tables in the join if the query qualifies for Star Join Processing. [...]
12171 126 30_Re: Multiple Tables/tablespace16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 5 Feb 2001 08:56:47 -0500598_iso-8859-1 The places I have worked have avoided multiple tables per tablespace. Having one table per tablespace simplifies utility setup and can reduce the amount of outage time needed for utilities. Where did you hear the 'of similar size' idea? I've not heard that one. The only one I've heard anywhere close to that is 'small tables which are used together'. Of course, I am speaking from an OS/390 background. Maybe for one of the smaller machine OS there are advantages for multiple tables per tablespace. I am very curious why anyone would want a tablespace which contained a 7 million [...]
12298 55 40_Re: Question for DB2 systems programmers12_MARTIN WOLFF31_martin_wolff@GLOBALCROSSING.COM30_Mon, 5 Feb 2001 08:59:21 -0500587_us-ascii Hi Ritu,
Your mileage will vary and every company is different but here goes:-
> > 1. Are SMP/E skills necessary? If yes, can they be > acquired just by reading manuals or a course offered > by IBM is more appropriate.
SMP/E skills are more than likely to be needed. DB2 doesn't have a very difficult SMP/E environment as it isn't source maintained unless the company you work in has extensive customization of the base DB2 code. I personally believe that the manuals and books are probably too advanced for what is required for DB2 SMP/E and SMP/E is [...]
12354 145 22_Re: 15-255 Table Joins9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Mon, 5 Feb 2001 14:52:41 +0000427_us-ascii Hi Paul, I was aware of the 255 limit for the Star Join, but our system is a highly normalised operational system rather than a fact/dimension table design - so the Star Join does not help.
It would be interesting to see how you would resolve a DB2 storage problem with your 3rd party package. They would probably refer you to IBM and then I guess IBM would refer you back to your 3rd party supplier...... [...]
12500 35 12_Re: DSNJU00314_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US30_Mon, 5 Feb 2001 10:20:09 -0500578_US-ASCII Hey Tina ...
It is far from obvious, I confess ... but here's my take / RACF experience on it:
In your LOCATIONS/LINKNAME tables, insure that the LINKNAME/LUNAME is the VTAM APPLLID (not the LOCATION name setup of JU003), and that SECURITY_IN is 'A' . Remembering that inbound txs do not use DSN3@ATH for connection to DB2, but that DSN3@SGN is alive and well for signons. Any thread that hits your DDF address space without a valid UID/PW combination from the "client" will be logged for you to ponder ... and the attempted signon will be rejected. [...]
12536 192 22_Re: 15-255 Table Joins23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Mon, 5 Feb 2001 15:17:06 -0000349_iso-8859-1 The problem with this is that IBM have obviously not communicated the removal of this to the 3rd party people (SAP, Peoplesoft, Siebel etc).
I'll mention it the next time I speak to somebody out in San Jose .. see what they say.
BUT if I were in this position and I wanted it back I would raise it Priority 1 with IBM. [...]
12729 24 0_17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM30_Mon, 5 Feb 2001 09:29:12 -0600489_us-ascii I am having a problem with the new control center for DB2 udb 7.1. I have several saved sql and ddl scripts that I created and saved in notepad. Usually if I have a routine list of commands that I run I would just open up the script, and copy and paste into command center. With 7.1, I can no longer do that. When I go to paste the 'paste' option is greyed out. When I do a 'ctrl+v' the text pastes but I get several errors when I run it. I have to go in and edit the script [...]
12754 198 30_Re: Multiple Tables/tablespace14_Scott Trometer22_scott.trometer@RCI.COM30_Mon, 5 Feb 2001 11:04:14 -0500553_iso-8859-1 Eric,
If I had to guess where I heard it, I'd say a Platinum course...but I don't recall specifically. Mullins eludes to the same in his developer guide by saying to "avoid grouping larger tables (> 32 pages) with other tables".
At any rate, I am looking at an existing scenario, that was likely set up several years ago. You might find it interesting that the tablespace currently contains not only the 7M row table, but also a 3.5M row table, a 1.6 M row table, and several small tables whose range is 40 rows to 50,000. [...]
12953 25 58_Spreadsheet from Richard Yevich & Susan Lawson Book needed13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Mon, 5 Feb 2001 11:38:40 -0500399_- Hi Listers,
Reading through the book "DB2 High Performance Design and Tuning", Chapter 3, pages 88 thru 92 shows various Buffer Pool spreadsheets developed by the noted authors. The book also suggest that you can download these spreadsheets from the ylassoc.com website. I visited the website and couldn't find my way to the spreadsheet. Anybody know how to get to these spreadsheets? [...]
12979 145 75_FW: follow-up question: Re: what's best way to re-size buffer po ols daily?14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Mon, 5 Feb 2001 05:34:45 -0600324_iso-8859-1 I originally sent this only to Jim, but it may be of interest to other people. If anybody can add to my reply, please do!
-----Original Message----- From: Vassie, Edward Sent: 05 February 2001 11:14 To: 'Jim Coleman' Subject: RE: follow-up question: Re: what's best way to re-size buffer pools daily? [...]
13125 93 30_Re: Multiple Tables/tablespace10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Mon, 5 Feb 2001 10:24:24 -0600609_- Segmented Tablespace of course.... Isn't it?
Maybe that's what you heard. You would probably not be able to pick a SEGSIZE which was optimal for both your large and small Tables.
On Mon, 5 Feb 2001 11:04:14 -0500, Scott Trometer wrote:
>Eric, > >If I had to guess where I heard it, I'd say a Platinum course...but I don't >recall specifically. Mullins eludes to the same in his developer guide by >saying to "avoid grouping larger tables (> 32 pages) with other tables". > >At any rate, I am looking at an existing scenario, that was likely set up [...]
13219 50 62_Re: Spreadsheet from Richard Yevich & Susan Lawson Book needed10_Robert Ord21_robertord@HOTMAIL.COM30_Mon, 5 Feb 2001 17:02:46 -0000681_- Carlton,
I tried download the spreadsheets after reading this chapter, I couldn't find them either. I dropped a note to the 'contact us' mail box but I have not had a response yet.
Cheers
Rob
>From: Carlton Enuda >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Spreadsheet from Richard Yevich & Susan Lawson Book needed >Date: Mon, 5 Feb 2001 11:38:40 -0500 > >Hi Listers, > >Reading through the book "DB2 High Performance Design and Tuning", Chapter >3, pages 88 thru 92 shows various Buffer Pool spreadsheets developed by the >noted authors. The book also suggest that [...]
13270 143 30_Re: Multiple Tables/tablespace0_22_Rohn.Solecki@MTS.MB.CA30_Mon, 5 Feb 2001 11:04:00 -0600511_iso-8859-1 In addition, the Administration Guide summarizes the main advantages and disadvantages ... Advantage ... Creating fewer table spaces, by storing several tables in one table space, can help you avoid reaching the maximum number of concurrently open data sets. Each table space requires at least one data set. A maximum number of concurrently open data sets is determined during installation. Using fewer table spaces means less time spent allocating and deallocating data sets. [...]
13414 241 30_Re: Multiple Tables/tablespace16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 5 Feb 2001 12:12:18 -0500504_iso-8859-1 Scott, All the big tables need to be separate tablespaces. What you need even more is that the very active ones be separate to reduce DASD contention. Also consider making the big tables partitioned tablespaces to allow parallelism.
regards,
eric pearson NS ITO Database Support
-----Original Message----- From: Scott Trometer [mailto:scott.trometer@RCI.COM] Sent: Monday, February 05, 2001 11:04 AM To: DB2-L@RYCI.COM Subject: Re: Multiple Tables/tablespace [...]
13656 46 27_Re: LOAD a BLOB in DB2/S39023_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Mon, 5 Feb 2001 12:30:44 -0500586_- John,
I have not yet tried the LOAD utility for loading BLOB data, however I wrote a tiny java servlet for uploading any image/text file on PC to BLOB column on DB2/OS390. If you are interested, I can send you the source code.
Sanjay Jain
> -----Original Message----- > From: JOHN G. MATTHEWS [SMTP:HC#JGM@MVS.MEDCTR.UCLA.EDU] > Sent: Friday, February 02, 2001 11:29 PM > To: DB2-L@RYCI.COM > Subject: LOAD a BLOB in DB2/S390 > > I am trying to use DB2 S390 V6 LOAD utility to load a BLOB column into > a DB2 table. The BLOB is an FTP'd JPEG [...]
13703 74 62_Re: Spreadsheet from Richard Yevich & Susan Lawson Book needed13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 5 Feb 2001 11:33:20 -0600417_iso-8859-1 Carlton/Robert,
I will drop a line to our web developer to get this resolved.
Regards Terry Purcell Yevich Lawson & Assoc www.ylassoc.com
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Robert Ord Sent: Monday, February 05, 2001 11:03 AM To: DB2-L@RYCI.COM Subject: Re: Spreadsheet from Richard Yevich & Susan Lawson Book needed [...]
13778 56 50_Re: UDB 7.1 Control Center copy and paste problems13_Lynne Flatley17_LFlatley@NEFN.COM30_Mon, 5 Feb 2001 12:11:37 -0500624_us-ascii Jeremy,
Have you applied FixPack 1 and/or 2?
Lynne A. Flatley New England Financial (617) 578-4079 (work) (781) 718-0919 (cell) lflatley@nefn.com
> -----Original Message----- > From: Jeremy Schleicher [SMTP:Jeremy_Schleicher@MAY-CO.COM] > Sent: Monday, February 05, 2001 10:29 AM > To: DB2-L@RYCI.COM > Subject: > > I am having a problem with the new control center for DB2 udb 7.1. > I have several saved sql and ddl scripts that I created and saved in > notepad. > Usually if I have a routine list of commands that I run I would just open > up the script, > and copy and [...]
13835 93 62_Re: Spreadsheet from Richard Yevich & Susan Lawson Book needed12_Susan Lawson22_lawson_susan@YAHOO.COM30_Mon, 5 Feb 2001 10:00:22 -0800609_us-ascii Hi All,
These will be externalized shortly on the www.ylassoc.com site.
Regards, Susan Lawson --- Robert Ord wrote: > Carlton, > > I tried download the spreadsheets after reading this > chapter, I couldn't > find them either. I dropped a note to the 'contact > us' mail box but I have > not had a response yet. > > Cheers > > Rob > > > >From: Carlton Enuda > >Reply-To: DB2 Data Base Discussion List > > >To: DB2-L@RYCI.COM > >Subject: Spreadsheet from Richard Yevich & Susan > Lawson Book needed > >Date: Mon, 5 [...]
13929 19 57_Re: Suggestion for "Out of Office AutoReply" Afficionados22_Edward J. Finnell, III23_efinnell@SEEBECK.UA.EDU30_Mon, 5 Feb 2001 12:36:07 -0600357_Windows-1252 Over on IBM-Main, the listowner enabled an LSoft Exit that traps text phrases and does conditional processing. For Out of Office just drops, for other stuff has many choices to include forwarding without attachment, setting sender to NOPOST, holding pending review or the ever popular "thread killer" for when the pilgrims get too chatty! [...]
13949 100 12_FW: DSNJU00325_Nijdam, Fred -Syntegra NL20_F.Nijdam@SYNTEGRA.NL30_Mon, 5 Feb 2001 19:34:00 +0100658_iso-8859-1 Ofcourse TCPALreadyVERified=NO if you want a logon with a (RACF) userid & password. Funny thing is that DDF translates the userid/passsword in upper&lowercase, so if anything is wrong you get two messages.
Fred
-----Original Message----- From: Hilton, Tina, BmS - NMI -PM To: DB2-L@RYCI.COM Sent: 5-2-01 14:26 Subject: Re: DSNJU003
Harvey,
Why would you set TCPALVER to YES? That doesn't sound like much security to me, or am I misunderstanding something? I thought setting it to yes meant that a password isn't required with the userid. We're just adding the TCP/IP part to our systems, so I'm very interested in [...]
14050 17 12_Re: DSNJU00311_Fred Nijdam20_f.nijdam@SYNTEGRA.NL30_Mon, 5 Feb 2001 12:37:41 -0600498_- Hey there,
TCPALreadyVERrified = NO is to force a userid/password verification agains your security-RACF-database. If you specify YES anyone can tell to be anyone (p.e. install-sysadm) and have full-access (if not -temporary- revoked).
Fred
================================================ 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.
14068 178 29_Re: DB2 Connect - TCPIP Error12_tim malamphy20_timalamphy@YAHOO.COM30_Mon, 5 Feb 2001 11:26:35 -0800585_us-ascii I believe DB2 Connect does not run on OS/390. Perhaps you are referring to DDF (xxxxDIST address space). There are new tables names in the CDB (Communications Data Base) in version 6 which must be populated to allow the connectivity with other DB2 regions. Tim --- Viswanathan N wrote: > > > I am facing this problem right from Version 5.1 on > mainframe .. we upgraded to > 6.1 ... nobody from IBM is helping ... > My shop users are really complaining a lot. Again > the probelm occurs > intermittently .. i.e ... sometimes it occurs [...]
14247 192 29_Re: DB2 Connect - TCPIP Error14_Dean Montevago18_DMonteva@VNSNY.ORG30_Mon, 5 Feb 2001 14:38:38 -0500583_iso-8859-1 DB2 Connect doesn't run on OS/390. What type of error are you getting. There are a number of steps that need to be completed inorder to get it going.
> -----Original Message----- > From: tim malamphy [SMTP:timalamphy@YAHOO.COM] > Sent: Monday, February 05, 2001 2:27 PM > To: DB2-L@RYCI.COM > Subject: Re: DB2 Connect - TCPIP Error > > I believe DB2 Connect does not run on OS/390. Perhaps > you are referring to DDF (xxxxDIST address space). > There are new tables names in the CDB (Communications > Data Base) in version 6 which must be populated to > allow [...]
14440 23 12_Re: DSNJU00314_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US30_Mon, 5 Feb 2001 14:48:10 -0500392_US-ASCII "Fred"
Your concern with security is shared by all of us. I am more concerned by your statement "If you specify TCPALVER=YES anyone can tell to be anyone." What you say Fred, is true no matter what you set TCPALVER to ... if you know your install-sysadm RACF signon ... not sure what your point is, or is not. Contact me off-line from the list, if you want to talk more. [...]
14464 213 29_Re: DB2 Connect - TCPIP Error0_17_sjvagnier@AEP.COM30_Mon, 5 Feb 2001 14:48:44 -0500401_us-ascii Vishy, check to see if VTAM APARs OW46128 and OW46885 are applied. I had a similiar problem when I went to DB2 V6. After I applied the two VTAM APAR's, no more problem. Also might want to check II08215.
Hope this helps.....
Steve Vagnier American Electric Power
tim malamphy @RYCI.COM> on 02/05/2001 02:26:35 PM [...]
14678 21 59_Re: Impact of online reorg at the partition level on an NPI18_Anyanso Agwu-Okeke17_pagwu@HOTMAIL.COM30_Mon, 5 Feb 2001 15:22:38 -0500
14700 59 29_Re: DB2 Connect - TCPIP Error12_Troy Coleman19_Colematr@MEIJER.COM30_Mon, 5 Feb 2001 15:53:51 -0500479_US-ASCII I've usually seen this problem when we are having TCP/IP problems on OS/390 or the application server. Without looking up a 1007 or 10044 in TCP/IP Sockets error handling you can do the following. 1) From your client ping the URL used by your node definition. 2) Check your services file to ensure the correct port number is being used. 3) Check your database configuration to ensure that authentication is correct. I usually use "DCS" to authenticate at the host. [...]
14760 31 6_db2set15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU30_Mon, 5 Feb 2001 15:54:55 -0500342_iso-8859-1 I'm trying to set up a DB2 UDB Federated Database "Nickname" that points to an Oracle table. I believe my error is that the db2set command I'm trying to execute is not updating the DB2 Profile Registry. The command I'm using is: db2set DB2_DJ_INI = sqllib/cfg/db2dj.ini (I updated the db2dj.ini file settings for Oracle_home) [...]
14792 34 22_DB2 V5 and DDF startup0_19_csutfin@AMSOUTH.COM30_Mon, 5 Feb 2001 15:25:38 -0600357_us-ascii We have just migrated our test DB2 to V5 this weekend.
The only problem that I can see is this:
In V4, when DDF started I use to get the following message(s) showing that the CONVLIMIT was determined for the individual LU's. 09.28.39 STC27027 DSNL510I - DSNLVCNS CONVLIMIT NEGOTIATED FROM 0 TO 89 FOR LU AD6015SY AND MODE IBMRDB [...]
14827 89 42_FW: DB2 for OS/390 and the support of Java23_Scarcella, Gary V (ING)23_ScarcellaGV@ING-AFS.COM30_Mon, 5 Feb 2001 16:27:11 -0500434_iso-8859-1 Hi. I was coding Java on OS/390 accessing DB2 with DB2 V5, but there were probably some APARs on our system at the time. Gary.
Gary Scarcella Internet address: ScarcellaGV@ING-AFS.com
-----Original Message----- From: Pendlebury-Bowe, Leslie [mailto:Leslie.Pendlebury-Bowe@EU.SONY.COM] Sent: Tuesday, January 23, 2001 3:52 AM To: DB2-L@RYCI.COM Subject: Re: DB2 for OS/390 and the support of Java [...]
14917 63 30_Re: Multiple Tables/tablespace12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Tue, 6 Feb 2001 09:27:34 +1100493_- We have a ROT (Rule of Thumb) here for application tables: 1 Table in 1 TableSpace. It works well FOR US.
For In-house developed applications, I believe this satisfies most requirements, and avoids some of the difficulties. As far as the Number of open datasets are concerned these are likely to be more numerous on the Index side of things which you can't restrict anyway. For reference tables we try to have 1-2 generic reference table rather than have dozens of smaller ones. [...]
14981 95 30_Re: Multiple Tables/tablespace13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Mon, 5 Feb 2001 14:43:14 -0800380_iso-8859-1 I am however surprised that one of the more 'prominent' catalog tablespace DSNDB06.SYSDBASE is a SIMPLE tablespace and contains 14 tables (including big ones like SYSTABLES, SYSTABLESPACE, SYCOLUMNS etc.). Was wondering why IBM chose to have it this way? Is it because catalog is 'different' and has links and hashes and so the tablespace does not matter or what? [...]
15077 56 26_Re: DB2 V5 and DDF startup22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM30_Mon, 5 Feb 2001 18:09:48 -0500476_iso-8859-1 On DB2 V5 for OS/390, we get the CONVLIMIT message when DB2 connects to the LU. No problems.
Mike Piontkowski TP&S Technical Maintenance Voice: +1 302.886.4612 Fax: +1 302.886.4749
-----Original Message----- From: csutfin@AMSOUTH.COM [mailto:csutfin@AMSOUTH.COM] Sent: Monday, February 05, 2001 16:26 To: DB2-L@RYCI.COM Subject: [DB2-L] DB2 V5 and DDF startup
We have just migrated our test DB2 to V5 this weekend. [...]
15134 51 26_Re: DB2 V5 and DDF startup23_Puddy, Andrew (CALBRIS)36_Andrew.PUDDY@COMALCO.RIOTINTO.COM.AU30_Mon, 5 Feb 2001 23:10:23 -0000394_iso-8859-1 on our previous versions (4.1 & 2.1) the message only appeared when a connection was made as is the case with 5.1 .
-----Original Message----- From: csutfin@AMSOUTH.COM [mailto:csutfin@AMSOUTH.COM] Sent: Tuesday, 6 February 2001 7:26 To: DB2-L@RYCI.COM Subject: DB2 V5 and DDF startup
We have just migrated our test DB2 to V5 this weekend. [...]
15186 15 19_UDB on DEC or SGI ?12_Roy Reynolds15_royr@INCYTE.COM30_Mon, 5 Feb 2001 15:36:54 -0800327_us-ascii Does UDB 7.1 run on a DEC Alpha box? Maybe just the runtime client? TIA, Roy
================================================ 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.
15202 48 27_Re: LOAD a BLOB in DB2/S39014_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 6 Feb 2001 11:13:21 +1100406_iso-8859-1 Haven't loaded a BLOB, but have loaded a CLOB. Fortunately, none of them blew 32k, so the entire row fitted into a single record.
If I had more than 32k, I would try CONTINUEIF. I would hope that LOAD can handle this - REORG and DSNTIAUL certainly can't!
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au [...]
15251 126 30_Re: Multiple Tables/tablespace12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Tue, 6 Feb 2001 11:22:43 +1100415_iso-8859-1 Ashish, Unless things have changed in the last couple of releases, part of the reason why IBM have multiple Catalog tables in a "SIMPLE" TableSpace (Although it's not really "SIMPLE"), is because there are internal pointer chains (that supplement the Indices) through the connecting tables in some of the Catalog tables. To make this work the tables need to located with in the same file structure. [...]
15378 184 30_Re: Multiple Tables/tablespace13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Mon, 5 Feb 2001 16:43:25 -0800409_iso-8859-1 Thanks Nick !
I knew catalog tablespaces were 'different' and SYSDBASE, though apparently SIMPLE has something more to it. Even the need of same 'file structure' would logically have led to a segmented tablespace as multi-table simple tablespaces have many problems associated with them; most of them probably arising from the fact that one page can have rows from more than one table. [...]
15563 71 30_Re: Multiple Tables/tablespace14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 6 Feb 2001 11:57:32 +1100470_iso-8859-1 Ashish
SYSDBASE has been around for longer than segmented tablespaces. (The give-away (if I didn't remember when segmented tablespaces came out) is the relative positions of IBMREQD and SEGSIZE. Normally, when a new catalog table is added, IBMREQD is the last column. SEGSIZE comes after IBMREQD, so it's an addition to SYSTABLESPACE.) The question then has to be "why hasn't IBM changed it?" And the answer is probably "it works, why change it?" [...]
15635 102 57_Re: OC7 and data does not rollback - still having problem14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Tue, 6 Feb 2001 13:36:34 +1100447_iso-8859-1 Please rerun with RPTOPTS and let us know what options are reported. Also SYSTSIN input and SYSTSPRT output. (btw, I am assuming that you are using the DSN command processor to invoke the program.)
By "user completion code 4039" do you mean "user _abend_ code 4039"? It seems not, but it's possible. (DB2 does not differentiate between user and system abends. They're both abends - and DB2 should do an automatic rollback.) [...]
15738 240 29_Re: DB2 Connect - TCPIP Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Tue, 6 Feb 2001 08:23:59 +0530418_us-ascii I am not talking about db2 Connect on the mainframe . I am having DB2 Connect on the PC ... on Windows NT .. Thats connecting to DB2 6.1 on OS390 . It gives a communication failure sometimes while running long queries. Sometimes the same long query works fine .. but at certain times .. mostly the second time the user fires the query consecutively ... it hangs .... and gives a communications error ... [...]
15979 16 96_Per-Olof Lindqvist/N239860/Nordbanken =?iso-8859-1?q?=E4r?= inte =?iso-8859-1?q?p=E5?= kontoret.0_24_per-olof.lindqvist@NB.SE28_Tue, 6 Feb 2001 07:22:41 GMT371_iso-8859-1 Jag är borta frċn kontoret frċn 2001-02-05 till 2001-02-07.
Jag kommer att svara pċ meddelandet när jag kommer tillbaka. ===============================================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.
15996 45 45_Result Set Not returned from Stored Procedure18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Tue, 6 Feb 2001 17:55:33 +0930461_us-ascii Howzit Folks? Our application developers are trying to test SPs and are having problems
returning a result set from the stored procedure to CICS or Batch.
They are using VisualAge Generator to generate a CICS Cobol calling program and
a Cobol SP.
Scenario:
CICS Cobol program CALLs SP
SP executes SQL
Debug statements write results to a table immediately prior to returning control [...]
16042 37 8_dsn1copy16_Ian Brodie Smith18_ismith2@UK.IBM.COM30_Tue, 6 Feb 2001 08:58:09 +0000471_us-ascii Hi,
Over the weekend we had a failure witha production job that dsn1copied a full image copy of a partitioned tablespace to a MI version of the same table. It went down with a vsam error. At the start of the output I found an IEC070i 104-204
Looking this up I find that the dataset was unable to expand because the volume was full ( this was true, the vol was 98% full with largest free space of 19 tracks, the seconday of the ds was 16 cyls) [...]
16080 22 59_Re: Impact of online reorg at the partition level on an NPI10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 6 Feb 2001 10:11:14 +0100525_us-ascii Here what you wish....(it's a Power Point presentation)
(See attached file: Intelligent_Reorg.ppt)
and here a URL where youi can find some other infos....
http://www.infotel.com/US/newsletter/newsletter_1_reorg.htm
HTH & regards
Max Scarpa
================================================ 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.
16103 19 27_Re: LOAD a BLOB in DB2/S39010_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 6 Feb 2001 10:18:20 +0100394_us-ascii Hi Sanjay.....
As we are implementing Java in our site, can I see your Java servlet for BLOB ?
Thanks & regards
Max Scarpa
================================================ 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.
16123 17 27_Re: LOAD a BLOB in DB2/S39015_Walter Janissen27_walter.janissen@VICTORIA.DE30_Tue, 6 Feb 2001 03:28:00 -0600483_- James
I found in the Release Guide If the total length of a LOB column and the base table row is less than 32 KB, you can use the LOAD utility to put the data in DB2. Otherwise, you must use INSERT or UPDATE statements
================================================ 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.
16141 54 12_Re: dsn1copy10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Tue, 6 Feb 2001 03:35:19 -0600459_- I think that as DSN1COPY runs outside of DB2, you will need to issue an IDCAMS ALTER ADDVOLUMES to your output d/s before running your DSN1COPY. Note that if you do this, then your first Secondary allocation on the new volume will be the same size as your Primary.
I would guess that the reason your other pagesets have gone multivolume is that they were extended by DB2, and the STOGROUPs (if you use them) are specified with multiple volumes. [...]
16196 21 25_Question Regarding Index.15_Kannan, Perumal22_perumal.kannan@EDS.COM30_Tue, 6 Feb 2001 03:52:10 -0600307_- Hello All, I have question. I am having a composite key index created on a table. on that one particular column is in ascending order, but all my programs are reading that table are sorting based on the same column in descending order. does this make it inefficient? Please let me know your thought. [...]
16218 35 30_Re: Multiple Tables/tablespace14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Tue, 6 Feb 2001 04:50:25 -0600611_iso-8859-1 Just about the biggest subsystem tuning lever you have is I-O reduction. If you can reduce your disk activity your performance is almost certain to increase.
Now consider what is happening inside a multiple-table tablespace. You want row 1, so you read a segment. You want row 20, so you read another segment. Ooops, that segment is not adjacent, but 10,000 segments away, due to the other tables in your tablespace. Segment 3 may jump you back a few thousand segments. None of this looks like decreasing disk activity or helping performance. It is more like having your tablespace split [...]
16254 45 29_Re: Question Regarding Index.10_John Hardy27_john_j_hardy@CONSULTANT.COM30_Tue, 6 Feb 2001 04:54:05 -0600561_- If your queries ORDER BY the columns in the same sequence/ordering as they appear in the index, and if DB2 chooses that index to satisfy the query, then a Sort can be avoided. However, if one of the columns in your ORDER BY is ordered differently (e.g. Descending though Ascending in your Index), then the results set will need to be sorted, which will make your query less efficient. If it is the case that all (or most, or most critical, etc.) of the applications accessing the table are Ordering by the column descending, then it could be argued that [...]
16300 62 13_Exit DSNXVDTX31_=?iso-8859-1?q?Silvia=20Motta?=21_silviamotta@YAHOO.COM30_Tue, 6 Feb 2001 03:35:03 -0800310_iso-8859-1 Toine, The steps i following were : -stop db2 - compile the exit, the module is in sys1.dsn610.sdsnload (that is in linklib) -refresh the lla - start db2
How can i check if the module is available when db2 is up ?
The error is : when i do : SELECT CHAR(COL2,LOCAL) FROM BORRAR99; [...]
16363 40 29_Re: Question Regarding Index.13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Tue, 6 Feb 2001 07:27:02 -0500399_iso-8859-1 Hello Perumal,
You say all your programs are reading that table sorting in descending order. Is there any use of the index in the ascending order it is created? There are several reasons why an index may have been created: data distribution/partitioning, enforcing uniqueness, assisting access performance -- for where clause, group by, order by, distinct support and others. [...]
16404 22 29_Re: Question Regarding Index.19_Rajeev P. Dhanawade22_rdhanawade@STATE.DE.US28_Tue, 6 Feb 2001 07:28:28 EST364_us-ascii Another factor to be considered is whether the rows are inserted in the table in the ascending order of the key in question. If that is the case, then your descending order index will tend to get disorganized more often and need more frequent reorgs.
Regards,
Rajeev P. Dhanawade Phone: 302-577-4659 ext. 221 Fax : 302-577-4454 [...]
16427 56 29_Re: Question Regarding Index.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 6 Feb 2001 18:11:56 +0530383_- Kannan,
There are high chances that all your query is using the RDS (physical) sort. Please look at the Explain and get confirmed. Many times with the appropriate index DB2 can avoid sort but in your case the queries will not be able to take any advantages.If the requirement of the data extraction is to select it in descending order then create your index like that. [...]
16484 184 30_Re: Multiple Tables/tablespace19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Tue, 6 Feb 2001 06:59:14 -0600607_iso-8859-1 Ashish, If you have a copy of DB2 UDB for OS390 "Diagnosis Guide and Reference", see the section DB2 Catalog Diagrams. You will see that there are several Links spread across the tables in strategic spots, e.g. Link DSNDD#AD from SYSDATABASE to SYSDBAUTH.
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 [...]
16669 91 17_Re: Exit DSNXVDTX19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Tue, 6 Feb 2001 07:12:27 -0600626_iso-8859-1 Silvia, My guess is the Exit probably isn't in the SDSNLOAD library used by the DB2 subsystem where the query ran.
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, [...]
16761 15 59_Re: Impact of online reorg at the partition level on an NPI10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 6 Feb 2001 14:38:54 +0100367_us-ascii Did you received the mail with enclosed the article ? I'm afraid that there were some problems....
Max Scarpa
================================================ 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.
16777 21 11_Dynamic DDF15_Daniel Sullivan28_daniel.sullivan@ZURICHUS.COM30_Tue, 6 Feb 2001 07:55:36 -0600311_us-ascii I'm thinking of writing a COBOL program that will read a source sub system and write to a remote sub system. My question: do I need to DCLGEN all the target tables? What will my insert look like:
EXEC SQL INSERT * INTO XXX.OWNER.TABLE-NAME END-EXEC.
Or can I do something like this? [...]
16799 33 6_Doubts8_bharat r20_bharat_321@YAHOO.COM30_Tue, 6 Feb 2001 06:09:41 -0800520_us-ascii I am new to DB2 on AIX. I have some basic doubts..
1.Will there be primary and secondary quantities in the Space allocation for table spaces? 2.Can I have table space and Index space(Both are DMS) in the same container? 3.In DMS what is the criteria of selecting the discs and files? pl. let me know the advantages and disadvantages 4. I have a Table with annual growth of 4GB. Pl. let me know How can I decide the primary quantity, secondary quantity and extent size and no of extents? Pl. help me [...]
16833 76 49_Re: Result Set Not returned from Stored Procedure12_Mark Anzmann24_MAnzmann@DHR.STATE.MD.US30_Tue, 6 Feb 2001 09:34:06 -0500457_US-ASCII Bruce-
If my memory serves me (which according to my wife doesn't happen often), unless you are passing the data back in some sort of an array via the parms, you will need to leave the result set open and fetch the rows via the calling program. If you look in the Redbook "Getting Started with DB2 Stored Procedures: Give Them a Call through the Network, SG24-4693-01", there is a section in there that discusses result set processing. [...]
16910 24 50_What 'KEEP DICTIONARY' option will do for a Table?15_Kannan, Perumal22_perumal.kannan@EDS.COM30_Tue, 6 Feb 2001 08:37:32 -0600415_- Hello All, I have a question. My requirement needs this option 'KEEP DICTIONARY' to be turned on. How should I turn it on and what is the purpose of this option. TIA.
Have a great day!
Regards, Kannan Perumal, EDS - Electronic Data Systems India Ltd., [http://www.eds.com] "Steeple Reach" 25, Cathedral Road, Madras - 86. Call : 91 + 44 + 8113801 to 15 Ext. 2324. mailto:perumal.kannan@eds.com [...]
16935 31 35_Retrieving a ROW ID after an insert11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK30_Tue, 6 Feb 2001 14:51:58 -0000278_- All,
I have an applicaiton programmer who needs to do the following
* INSERT a row into a table (first column defined as ROW ID and using generated value) * retrieve the value of ROW ID * use the ROW ID as a foreign key in a number of 'child' table INSERTs [...]
16967 56 54_Re: What 'KEEP DICTIONARY' option will do for a Table?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Tue, 6 Feb 2001 09:05:10 -0600591_iso-8859-1 Perumal, One place it is used is in DB2 UDB OS390 REORG Utility to reuse the current Dictionary for a compressed tablespace rather than create another. In the future, its always best to indicate which version and platform of DB2, e.g. DB2 UDB AIX V7.1.
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 [...]
17024 79 27_Re: LOAD a BLOB in DB2/S39023_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Tue, 6 Feb 2001 10:19:06 -0500590_- James,
As far as the doc goes, you can not load a BLOB/CLOB bigger than 32K through load utility, thats why we chose to write a program instead.
Sanjay Jain
> -----Original Message----- > From: James Campbell [SMTP:James.Campbell@HANCORP.COM.AU] > Sent: Monday, February 05, 2001 7:13 PM > To: DB2-L@RYCI.COM > Subject: Re: LOAD a BLOB in DB2/S390 > > Haven't loaded a BLOB, but have loaded a CLOB. Fortunately, none of them > blew 32k, so the entire row fitted into a single record. > > If I had more than 32k, I would try CONTINUEIF. I would hope that [...]
17104 71 54_Re: What 'KEEP DICTIONARY' option will do for a Table?15_Blaicher, Chris22_Chris_Blaicher@BMC.COM30_Tue, 6 Feb 2001 09:34:19 -0600564_iso-8859-1 Another reply mentioned REORG, but it probably has a bigger effect in LOAD cases. I am assuming that you are using a 390 version of DB2.
For LOAD, if you do a LOAD REPLACE and do not specify KEEPDICTIONARY, then the dictionary will be built with the data from that load only. If you are only putting in a few rows, or worse, doing a null load, then the dictionary will have nothing in it and you will effectively not have any compression. Compression dictionaries are only built the first time you load a table. Insert activity does not add [...]
17176 24 39_Re: Retrieving a ROW ID after an insert15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Tue, 6 Feb 2001 09:32:26 -0600588_- Carl
I don't know, how you can get the ROWID-value of a row, you have just inserted, if there is no other key.
But I think you shouldn't store those values in child tables, because the value in the parent table can change, if the tablespace will be reorged.
I believe, you really didn't mean ROWID, but you meant a column with the attribute IDENTITY, which was recently introduced in version 6. You can define a column with IDENTITY GENERATED ALWAYS and DB2 always will generate a unique value for that column. The SQL statement SET :H = IDENTITY_VAL_LOCAL () [...]
17201 93 54_Re: What 'KEEP DICTIONARY' option will do for a Table?0_18_mebert@AMADEUS.NET30_Tue, 6 Feb 2001 17:35:46 +0100628_us-ascii Hi Chris,
The last paragraph is not correct. The main difference is that REORG will compress all rows, but LOAD will only compress the rows loaded after the compression dictionary has been built, at which point a couple of thousand rows will already be in the TS. Both REORG and LOAD will report "Compression dictionary built from nn rows". Actually many more rows will have been inspected (see my postings a couple of months ago about initialising a compression dictionary); most rows are probably not counted because they add nothing to the dictionary. Furthermore, REORG does not necessarily provide the [...]
17295 51 39_Re: Retrieving a ROW ID after an insert11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK30_Tue, 6 Feb 2001 16:42:46 -0000522_- That will teach me to type straight from the developers mouth. You are absolutely right in that we meant the column was IDENTITY.
Unfortunately doing the SER :H will not guarantee getting the correct value, what is to stop an other transaction inserting a new row just milliseconds after ours?
TIA
Carl.
-----Original Message----- From: Walter Janissen [mailto:walter.janissen@VICTORIA.DE] Sent: 06 February 2001 15:32 To: DB2-L@RYCI.COM Subject: Re: Retrieving a ROW ID after an insert [...]
17347 206 39_Re: Retrieving a ROW ID after an insert14_Scott Trometer22_scott.trometer@RCI.COM30_Tue, 6 Feb 2001 12:11:18 -0500369_iso-8859-1 Carl,
I think I had the same question a while back. Got the excerpt below from John Hardy:
"I wondered about exactly the same thing. It isn't made clear in the manuals whether you get the latest value generated in your unit of work, or the latest value generated full stop (or 'period', depending which side of the Atlantic you're from!). [...]
17554 44 53_Pipeline Newsletter for DB2 & Oracle - February Issue9_Cam White20_cwhite@REVEALNET.COM30_Tue, 6 Feb 2001 12:18:46 -0500773_iso-8859-1 ------------------------------------------------------------ Don't forget to register for a chance to win a FREE Full-Conference Registration to IDUG (value $1,595) http://www.revealnet.com Winner to be announced on 4/2/2001. ------------------------------------------------------------
The February, 2001 Issue of the Pipeline Newsletter is now available for Oracle and DB2 professionals.
http://www.revealnet.com/newsletter-v2/newsletter_0201.htm
Feature articles include:
- "Managing Oracle 8i Temporary Tables" - by Vijays, Datacons - "General Guidelines for Using DB2 Triggers" - Knowledge Base Topic - "Guidelines for Writing Bullet-Proof Code" - Daniel Clamage - "Script to Transfer Source Code to Files" - Daniel Clamage [...]
17599 24 39_regarding load.... need immediate reply17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Tue, 6 Feb 2001 11:13:19 -0600386_- Hello Folks,
I need an help in one table.... in our shop we are using DB2 V6. one tablespace has two tables in test region. i wanted to load only one table from prod. to test region. but in test region i should not miss the data in other table i downloaded the data from prod. for one table. what is the procedure to load only one table.. can somebody help in this issue. [...]
17624 73 43_Re: regarding load.... need immediate reply10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Tue, 6 Feb 2001 11:31:23 -0600392_us-ascii LOAD RESUME - do * NOT * use REPLACE, as it replaces ALL table data in the tablespace.
LOAD DATA RESUME YES INTO OWNER.TABLENAME
Other options are keepdictionary (for compression) & reuse - which reuses the existing VSAM dataset, doesn't redefine it (or move it)
Check your manual for exact syntax..........
Thanks. Missy Case FDR TMDBA 701-275-6358 [...]
17698 48 43_Re: regarding load.... need immediate reply13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Tue, 6 Feb 2001 09:43:57 -0800466_iso-8859-1 The procedure is .... Load data. Why should you be concerned about other table at all? Unless you want a load replace in which case just delete all the records of that table by a SQL and then run load resume. Or am I missing something here??
Ashish.
-----Original Message----- From: ravi kumar hassan [SMTP:ravibh@HOTMAIL.COM] Sent: Tuesday, February 06, 2001 9:13 AM To: DB2-L@RYCI.COM Subject: regarding load.... need immediate reply [...]
17747 16 29_Re: DB2 Connect - TCPIP Error13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Tue, 6 Feb 2001 12:31:22 -0600319_US-ASCII Vishy, What do you have the querytimeoutinterval set to ? Thanks, Kurt
===============================================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.
17764 77 43_Re: regarding load.... need immediate reply17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Tue, 6 Feb 2001 12:24:47 -0600632_- Hello Folks,
Thank you very much for your help... i deleted all the records from one table which i want to load and then i used a Load utility with Resume Yes to load data from unloaded prod. data.. it works fine.
Thanks Kumar
>From: "Mohan, Ashish" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: regarding load.... need immediate reply >Date: Tue, 6 Feb 2001 09:43:57 -0800 > >The procedure is .... Load data. Why should you be concerned about other >table at all? Unless you want a load replace in which case just delete all [...]
17842 25 30_Extracting execution plan name21_Plourde Renaud (4320)29_renaud.plourde@RRQ.GOUV.QC.CA30_Tue, 6 Feb 2001 13:22:54 -0500527_- Hello listers,
I'd like to know if there's an easy way to extract the execution plan name in an application. I'm currently running DB2 for OS/390 V6.
TIA, Renaud Plourde Regie des rentes du Quebec
Renaud Plourde Regie des rentes du Quebec
================================================ 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.
17868 42 17_DSN1COPY Question9_Colin Fay13_cfay2@CSC.COM30_Tue, 6 Feb 2001 13:37:54 -0500692_us-ascii Hi,
I have an single image copy for a partitioned dataset (10 partitions). I want to use a dsn1copy restore . How do I map the 1 backup tape (SYSUT1) to the 10 vsam datasets (SYSUT1)?
If it had been backed up BY PARTITION it would have looked like this:
//DSN1COP1 EXEC PGM=DSN1COPY, // PARM='FULLCOPY,NUMPARTS(10),OBIDXLAT,RESET' //STEPLIB DD DSN=DBD1.DSNEXIT,DISP=SHR // DD DSN=DBD1.DSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSUT1 DD DSN=DBP1.FICA.DTIME001.STIMEPR4.PART001(-1),DISP=OLD //SYSUT2 DD DSN=DBD1.DSNDBD.DTIMESYS.STIMESR4.I0001.A001,DISP=OLD //* SOURCE/TARGET DBID, PSID(OR ISOBID FOR INDEXES), OBID //SYSXLAT DD * 268,275 102,060 208,146 //* [...]
17911 25 19_huge data loading..15_Rajesh Udandrao17_urajes1@YAHOO.COM30_Tue, 6 Feb 2001 10:59:42 -0800408_us-ascii I have a doubt..There is a huge data insertion in a table. Some one wants to see the data which is already existing(data exisisting before inserts) in the table at the same time. Can he/she see the data ?
Rajesh
__________________________________________________ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices. http://auctions.yahoo.com/ [...]
17937 64 49_Re: Result Set Not returned from Stored Procedure13_Terry McCombs20_tmccomb@SEDGWICK.GOV30_Tue, 6 Feb 2001 12:58:06 -0600487_iso-8859-1 Have you specified the number of result sets returned in the definition of the stored procedure? Have they opened the cursor in the stored procedure?
-----Original Message----- From: Bruce W Williamson [mailto:Bruce.Williamson@NT.GOV.AU] Sent: Tuesday, February 06, 2001 2:26 AM To: DB2-L@RYCI.COM Subject: Result Set Not returned from Stored Procedure
Howzit Folks? Our application developers are trying to test SPs and are having problems [...]
18002 30 59_Re: Impact of online reorg at the partition level on an NPI18_Anyanso Agwu-Okeke17_pagwu@HOTMAIL.COM30_Tue, 6 Feb 2001 14:02:45 -0500
18033 13 16_DB2 V7 NT fixpak13_Jeff Jennings25_Jeff_Jennings@FSAFOOD.COM30_Tue, 6 Feb 2001 11:16:25 -0800385_us-ascii I have searched both the DB2-L archives and the IBM site for "FP2_WR21229.zip" and am unable to locate the file. Any help appreciated.
================================================ 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.
18047 89 17_Re: Exit DSNXVDTX15_Toine Michielse18_vndobtm@US.IBM.COM30_Tue, 6 Feb 2001 20:21:37 +0100629_us-ascii Hello Silvia,
Please make sure that you have set DATELEN/TIMELEN in DSNHDECP to the correct length (not 0).
Regards,
Toine Michielse
Silvia Motta @RYCI.COM> on 02/06/2001 12:35:03 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Subject: Exit DSNXVDTX
Toine, The steps i following were : -stop db2 - compile the exit, the module is in sys1.dsn610.sdsnload (that is in linklib) -refresh the lla - start db2 [...]
18137 81 21_Re: DSN1COPY Question23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Tue, 6 Feb 2001 14:31:38 -0500449_- Colin
If SYSUT1 is an image copy of a whole partitioned table space, SYSUT2 should be the name of the first data set of the table space. DSN1COPY allocates all of the target data sets. However, the target data sets must be previously defined using IDCAMS. Specify the NUMPARTS parameter to identify the number of partitions in the whole table space. The JCL you have should work. You don't have to run the job once for each partition. [...]
18219 138 27_Re: LOAD a BLOB in DB2/S39023_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Tue, 6 Feb 2001 14:35:04 -0500701_iso-8859-1 Max,
Here is the code
import java.io.*; import java.util.*; import javax.servlet.http.*; import javax.servlet.*; import java.sql.*; import sqlj.runtime.*; import sqlj.runtime.ref.*;
public class PutImage extends HttpServlet {
public void doGet(HttpServletRequest req,HttpServletResponse res) throws ServletException, IOException { Connection con = null; String sqlStmt = null; String inTxt = null; String lName = "xyz"; String fileName1 = "out.txt"; String fileName2 = "error.txt"; String url = "jdbc:db2:db2db3d"; String userid = "xxxxxx"; String passwd = "yyyyyyyy"; byte[] b = new byte[200 * 1024]; int bytesRead = 0; String docExtension = null; [...]
18358 24 20_Re: DB2 V7 NT fixpak13_Leo Conchello18_lconche@USWEST.COM30_Tue, 6 Feb 2001 12:49:51 -0700538_us-ascii Try this url,
http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7fphist.d2w/report
Jeff Jennings wrote:
> I have searched both the DB2-L archives and the IBM site for "FP2_WR21229.zip" > and am unable to locate the file. Any help appreciated. > > ================================================ > 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. [...]
18383 19 22_DB2 on OS/390 and SQLP14_Toppins, Smike21_smike.toppins@GWL.COM30_Tue, 6 Feb 2001 12:48:28 -0700477_- Where's the best place(s) to start looking into using Stored Procedures and SQLP? Any gotcha's to look out for? Simple examples that you care to share?
SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094
================================================ 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.
18403 51 39_Re: Retrieving a ROW ID after an insert13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Tue, 6 Feb 2001 15:04:04 -0500442_iso-8859-1 Hello Carl, Walter,
I think there's still some confusion. Maybe I misunderstood you, Walter.
The ROWID datatype does provide a permanent value. See below. The IDENTITY feature provides a permanent value as long as you never need to drop, recreate, & reload the table. Nobody ever does that, unless of course they need to drop a column or change a column (not counting increasing the length of a VarChar column). [...]
18455 35 20_Re: DB2 V7 NT fixpak13_Ellen Richter19_e2richte@HEWITT.COM30_Tue, 6 Feb 2001 14:04:50 -0600731_us-ascii Try this:
ftp://ftp.software.ibm.com/ps/products/db2/fixes/english-us/db2ntv71/
From: Jeff Jennings on 02/06/2001 01:16 PM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Client: Subject: DB2 V7 NT fixpak
I have searched both the DB2-L archives and the IBM site for "FP2_WR21229.zip" and am unable to locate the file. Any help appreciated.
================================================ 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. [...]
18491 30 20_Re: DB2 V7 NT fixpak13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Tue, 6 Feb 2001 14:07:19 -0600572_US-ASCII Jeff , Try ftp://ftp.software.ibm.com/ps/products/db2/fixes/english-us/ then the OS. NT would be db2ntv71 HTH Kurt
>>> Jeff_Jennings@FSAFOOD.COM 02/06/01 01:16PM >>> I have searched both the DB2-L archives and the IBM site for "FP2_WR21229.zip" and am unable to locate the file. Any help appreciated.
================================================ 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. [...]
18522 35 20_Re: DB2 V7 NT fixpak13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Tue, 6 Feb 2001 14:14:37 -0600467_iso-8859-1 Hi, You are not imagining things ... It seems to have been removed. I looked at ftp://ftp.software.ibm.com/ps/products/db2/fixes/english-us/db2ntv71/FP2_WR2 1229 for your file. I downloaded this a few weeks ago from this site.
Perhaps IBM can answer this.
glenn
-----Original Message----- From: Jeff Jennings [mailto:Jeff_Jennings@FSAFOOD.COM] Sent: Tuesday, February 06, 2001 1:16 PM To: DB2-L@RYCI.COM Subject: DB2 V7 NT fixpak [...]
18558 38 31_Buffer Pool Tuning / UDB on AIX0_22_BILL_GALLAGHER@PHL.COM30_Tue, 6 Feb 2001 15:22:59 -0500667_us-ascii Hi,
Over the past couple of years, it's been pretty standard practice/advice to create separate buffer pools for the DB2 system catalog tablespaces, DSNDB07 tablespaces, and application tablespaces/indexes on DB2 for OS/390.
Is there any reason why this rule-of-thumb should not apply for UDB on AIX? It seems that conceptually, it should be a good idea. When you create a database on AIX (or NT, OS/2, etc.), three tablespaces are automatically created for you (SYSCATSPACE, TEMPSPACE1, and USERSPACE1), which all use the IBMDEFAULTBP buffer pool. I'm thinking that a good standard would be to create additional buffer pools (TEMPBP1 and [...]
18597 57 20_Re: DB2 V7 NT fixpak13_Jeff Jennings25_Jeff_Jennings@FSAFOOD.COM30_Tue, 6 Feb 2001 12:22:17 -0800326_us-ascii Thanks to all who responded to my query both on and off the list. It seems to be the consensus that IBM has removed the file for some reason. If I am able to determine why, I will relay the information to the list.
"Mackey, Glenn" on 02/06/2001 12:14:37 PM [...]
18655 85 39_Re: Retrieving a ROW ID after an insert19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Tue, 6 Feb 2001 14:36:42 -0600606_iso-8859-1 Dave, Please consider a row with an Identity column GENERATED BY DEFAULT and a Unique index on it. Could I not unload, drop, recreate, and reload this table?
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 [...]
18741 74 20_Re: DB2 V7 NT fixpak11_Paul Murphy22_Paul_Murphy@MAY-CO.COM30_Tue, 6 Feb 2001 14:37:20 -0600590_us-ascii It looks like it got moved to ftp://ftp.software.ibm.com/ps/products/db2/fixes/english-us/db2winv71/
"Mackey, Glenn" To: DB2-L@RYCI.COM Subject: Re: DB2 V7 NT fixpak Sent by: DB2 Data Base Discussion List
02/06/01 02:14 PM Please respond to DB2 Data Base Discussion List
Hi, You are not imagining things ... It seems to have been removed. I looked at ftp://ftp.software.ibm.com/ps/products/db2/fixes/english-us/db2ntv71/FP2_WR2 [...]
18816 34 39_Re: Retrieving a ROW ID after an insert13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Tue, 6 Feb 2001 16:09:25 -0500537_iso-8859-1 >Please consider a row with an Identity column GENERATED BY DEFAULT >and a Unique index on it. > Could I not unload, drop, recreate, and reload this table?
Hi Rick, Yes, it might. But after you drop, before you recreate your table, before you do the load, you need to modify your DDL to change the START WITH number. Otherwise your start with value is still the original number -- let's say 1. Then your load fails because all your rows are rejected by your unique index. Note, this is not an alterable parameter. [...]
18851 100 39_Re: Retrieving a ROW ID after an insert23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Tue, 6 Feb 2001 16:28:51 -0500555_iso-8859-1 Dave,
6 months ago, I was testing ROWID. I created a table (with a ROWID = column among others) with 0 PCTFREE 0 FREEPAGE and loaded data into it. After = the load, I unloaded the rows to get ROWID data. After the unload, I = changed PCTFREE to 50 and FREEPAGE to 2 and reorged the tablespace. After the reorg, I downloaded the data and surely enough the ROWID values were changed. DB2 version planning guide also suggests the same, though the = SQL reference manual says otherwise (as you quoted). That is why I decided = to check [...]
18952 42 33_Reorg and Multi-Table Tablespaces13_Lockwood Lyon19_Lockwool@MEIJER.COM30_Tue, 6 Feb 2001 16:39:13 -0500318_US-ASCII Okay, I thought I knew this one but I can't find a Clear Answer in the DB2 manuals (DB2 for OS/390 V6).
I have two tables (T1, T2) in a segmented tablespace, and each has an explicit clustering index. The tablespace is defined COMPRESS YES SEGSIZE 16. I then LOAD REPLACE T1, then LOAD RESUME T2. [...]
18995 25 51_DB2 V6 for OS390 - Broken Pages in Very Large Table22_drdeath@ozemail.com.au22_drdeath@OZEMAIL.COM.AU30_Tue, 6 Feb 2001 21:55:35 "GMT"385_us-ascii People, help required urgently. Our site upgraded to V6 3 days ago, and suddenly today we have started getting broken pages throughout one very large table. They all seem to apply to a single partition, which is not the last one. Display shows no indication of the problem. Has anyone any experience with a similar problem, with V6, and have any suggestions as to a fix. [...]
19021 55 39_Re: Retrieving a ROW ID after an insert14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 7 Feb 2001 09:17:52 +1100629_iso-8859-1 -----Original Message----- From: Jain, Sanjay (Exchange) [mailto:sanjayjain@BEAR.COM] Sent: Wednesday, February 07, 2001 8:29 AM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Retrieving a ROW ID after an insert
Dave,
6 months ago, I was testing ROWID. I created a table (with a ROWID = column among others) with 0 PCTFREE 0 FREEPAGE and loaded data into it. After = the load, I unloaded the rows to get ROWID data. After the unload, I = changed PCTFREE to 50 and FREEPAGE to 2 and reorged the tablespace. After the reorg, I downloaded the data and surely enough the ROWID values were changed. DB2 [...]
19077 47 23_Re: huge data loading..8_bharat r20_bharat_321@YAHOO.COM30_Tue, 6 Feb 2001 14:30:11 -0800628_us-ascii rajesh Yes, he can see the data if the locking type is row level..
Bharat
--- Rajesh Udandrao wrote: > I have a doubt..There is a huge data insertion in a > table. Some one wants to see the data which is > already > existing(data exisisting before inserts) in the > table > at the same time. Can he/she see the data ? > > Rajesh > > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Auctions - Buy the things you want at great > prices. > http://auctions.yahoo.com/ > > ================================================ > To change your subscription [...]
19125 78 47_FW: [DB2-L] Retrieving a ROW ID after an insert14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 7 Feb 2001 09:59:24 +1100487_iso-8859-1 Sorry about my less than informative post - accidently pressed SEND before I'ld written anything.
Sanjay
I certainly agree that rowid should not be used for RI - but not for the reason you present. A table can have only one ROWID column, and ROWIDs must be unique (a rowid column must be either GENERATED ALWAYS (which creates unique values) or GENERATED BY DEFAULT and have a unique index). This makes it impossible to have two children of a given parent. [...]
19204 18 55_Re: DB2 V6 for OS390 - Broken Pages in Very Large Table0_26_truman.g.brown@VERIZON.COM30_Tue, 6 Feb 2001 17:02:48 -0500455_us-ascii If all is well - and it probably isn't - you can use the REPAIR utility to set the broken page flag off. I've done this in the past, but it makes me nervous...
Do it at your own risk.
George
================================================ 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.
19223 60 15_Re: Dynamic DDF14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 7 Feb 2001 10:31:54 +1100393_iso-8859-1 I would tend to not hard code the remote subsystem name into your program. While you can do it, what happens when the remote subsytem's name changes? Use CONNECT.
The actual inserts will then be just like if you were using a local subsystem.
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au [...]
19284 52 26_Re: DB2 V5 and DDF startup17_Belfield, Alma G.21_Alma.Belfield@TWA.COM30_Tue, 6 Feb 2001 17:43:43 -0600295_iso-8859-1 In V4, SYSLUMODES had a column AUTO which controlled whether the connection was made at DDF start or on first use..... In V5, table LUMODES no longer has that column (haven't yet seen a statement regarding when the connection will be made). Could you have been using AUTO = 'Y'? [...]
19337 66 34_Re: Extracting execution plan name14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 7 Feb 2001 10:46:09 +1100378_iso-8859-1 Renaud
Well, there is a way - don't know about "easy". Write a UDF which is passed DBINFO - which includes LUWID. Use IFI to issue a "-DIS THREAD luwid()", extract plan name from the response. Return plan name from UDF.
Once you've written it, why don't you post it to DB2-L-DOCUMENTS. I'm sure there are many who would like it. [...]
19404 108 26_Re: DB2 on OS/390 and SQLP14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Tue, 6 Feb 2001 17:07:40 -0800314_iso-8859-1 Getting the subsystem prepared for SPs is covered in the Admin guide. Writing stored procedures is covered in the Application and Programming Guide. There is also a manual on the new Stored Procedure Language. I have a presentation that covers this. If you would like it let me know offline. -Phil [...]
19513 55 29_Re: DB2 Connect - TCPIP Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Wed, 7 Feb 2001 08:33:13 +0530820_us-ascii The Query TimeOut setting in the DB2 Server (OS390) is unlimited .... Vishy
Kurt Sahlberg on 07/02/2001 12:01:22 AM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM
cc: (bcc: Viswanathan N/LTITL)
Subject: Re: DB2 Connect - TCPIP Error
Vishy, What do you have the querytimeoutinterval set to ? Thanks, Kurt
======================== 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. [...]
19569 32 48_An old question : Why DB2 uses only 48K / track?12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Wed, 7 Feb 2001 15:18:36 +1100385_- With respect to a 3390 there are approx. 56K per track that is available, or a bit under (I think somewhere around the 55.5K mark).
Yet the VSAM DataSet only allocate 48K on the track.
Any ideas why we (DB2) didn't go to 13 pages per track (other than superstition). and why not have made DASD with a little bit more capacity per track and get 14 pages per track? [...]
19602 62 23_Re: huge data loading..16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 7 Feb 2001 10:45:13 +0530542_- Hi Rajesh,
I am sure i worked with you, please confirm. Anyway, coming back to your question, it looks bit confusing to me. Is this Loading or Insertion because in the subject line, it is mentioned Loading and in message text it is mentioned Insertion.
If i am not wrong, if insertion is with isolation level RR, no one can see the data till that UOW. If it is other than RR, if the records which someone wants to see is in the same page where insertion is going on at present, then data can't be seen otherwise it looks [...]
19665 157 24_TimeStamp Anomaly in SQL12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Wed, 7 Feb 2001 16:27:39 +1100489_iso-8859-1 Looking at the bounds of Timestamp the non date component runs from 00:00:00.000000 to 24:00:00.000000, but yet the 2 do not equate (Refer 1st query below). <>
Now if you use CURRENT TIMESTAMP to populate the field it's not an issue because 23:59:59.999999 is it's last entry before it clicks over to 0! The other interesting point is that 23:59:59.000000 + 1 second will also clock into the next day not 24:00 (Refer 2nd query below implies this). [...]
19823 79 52_Re: An old question : Why DB2 uses only 48K / track?14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 7 Feb 2001 16:50:02 +1100351_iso-8859-1 Nick
Not our (DB2's) choice. Those that made the disk architected it as they saw fit. Their architecture allows only 12 4k blocks per track. (BTW, the largest blocksize for 12 blocks/track is 4136 - so there's not really much unused space.) There were real reasons why there is so much overhead for each block in a 'real' 3390. [...]
19903 134 28_Re: TimeStamp Anomaly in SQL14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 7 Feb 2001 17:03:46 +1100439_iso-8859-1 Nick
We did this one to death (and then some) a few years ago. Check the archives.
Writing as a "DB2 is correct ( at least in this aspect)" person, 24:00:00.000000 is at the ending moment of a day (and means "24 hours has elapsed since the begining of the day"), 0:00:00.000000 is at the begining moment of a day. They might occur at the same instant, but they are on different days - and hence not the same. [...]
20038 77 29_Re: Question Regarding Index.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 7 Feb 2001 00:10:33 -0600294_iso-8859-1 John,
With regard to V7 and "backward index scanning", my understanding is that V7 provides the functionality to perform an Index-One Fetch for a MAX or MIN, with either a ASC or DESC index. Currently only a MIN function can I1 for an ASC index, and MAX can I1 for DESC. [...]
20116 123 28_Re: TimeStamp Anomaly in SQL13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 7 Feb 2001 00:10:27 -0600455_iso-8859-1 Nick,
Graeme Birchall presents this and many other SQL anomolies in his SQL Cookbook, which is downloadable free from http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM
As for indexability of the predicate, the example you show is stage 2 since it compares an expression to an expression. However had you compared:
WHERE TIMESTAMP_COL = TIMESTAMP('2001-01-01-24.00.00.000000') - 1 SECOND + 1 SECOND [...]
20240 117 52_Re: An old question : Why DB2 uses only 48K / track?12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Wed, 7 Feb 2001 17:38:50 +1100357_iso-8859-1 Cool! Thanks for the reply(s) James.
The point came up because some Storage people were saying that a DataSet had used 4.1G (around the 5110 cyl mark based on 56K/track) But not only can't we use 4.1G on a single DataSet, a Listcat revealed 3.5G as the hi-alloc (which when using a 48K/track used works out around the 5110 cyl mark). [...]
20358 142 52_Re: An old question : Why DB2 uses only 48K / track?14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Wed, 7 Feb 2001 18:31:55 +1100433_iso-8859-1 Like the British and the Americans, we are divided by a common language.
I have taken to using "3390-tracks", "3390-cylinders", or (for big things) "3390-1 equivalents" as my unit of measurement when talking disk space. Learnt the hard way that my bytes weren't the same as theirs.
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Doncaster +61 3 9843 8442 James.Campbell@Hancorp.com.au [...]
20501 17 27_Re: LOAD a BLOB in DB2/S39010_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Feb 2001 08:35:02 +0100342_us-ascii Hi Sanjay,
Thanks a lot for your code. I'm studying it....
Max Scarpa
================================================ 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.
20519 22 55_Re: DB2 V6 for OS390 - Broken Pages in Very Large Table10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Feb 2001 08:43:54 +0100558_us-ascii If you know (and I think you know from error messages) the broken pages you can use RECOVER TABLESPACE PAGE
(but take a look the the error message first. I suppose you've RAID disks so no problem on tracks...) to rebuilt the data
page. It's a slow process, anyway.
HTH
MAx Scarpa
================================================ 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.
20542 17 55_Re: DB2 V6 for OS390 - Broken Pages in Very Large Table10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Feb 2001 08:50:14 +0100401_us-ascii I forgave....When you execute a -DIS DB(...) RES is the tablespace (and index) in some restrict state (LPL or other)?
Regards
Max Scarpa
================================================ 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.
20560 29 37_Re: Reorg and Multi-Table Tablespaces10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Feb 2001 09:12:07 +0100513_us-ascii Hi...
Take a look at Gabrielle Wiorkowsky web site. At the url:
http://www.gabrielledb2.com/rr.pdf
There's a brief description about this topic. Last year there was an entire paragraph (public and free) describing unload
of data during the unload phase of a reorg.
HTH
Max Scarpa
"Long ago, when Protoceratops Andrewsi walked on the earth, DB2 and I were young and with many hopes, and UNIX was only an annoyance, I had a real job. I was a plumber." [...]
20590 82 19_new volumes for dba15_MAHSOUN, KHALID29_kmahsoun@SAUDIAIRLINES.COM.SA30_Wed, 7 Feb 2001 09:13:31 +0300223_- omer the volmes names are: for ALFURSAN DBDF01 MDR DBDM01 DBDM02
> Khalid Mahsoun > Database Support > Saudi Arabian Airlines > Tel: +966-2-686-4769 > Fax: +966-2-686-2962 > Email: kmahsoun@saudiairlines.com.sa > >
20673 123 52_Re: An old question : Why DB2 uses only 48K / track?0_18_mebert@AMADEUS.NET30_Wed, 7 Feb 2001 09:44:16 +0100574_us-ascii Just in case anyone is interested, the discrepancy is because the hardware required gaps between blocks. After reading a block, the CRC (Cyclic Redundancy Check) had to be computed and compared with the value read from disk to check for read errors. Only when this was done, could the next block be read. You can't stop the disk from rotating while the computation is being done... Unfortunately the gaps are not equal in size (no idea why), so there is no easy formula to derive the maximum block size for n blocks/track (for a 3380, it might at first appear [...]
20797 131 28_Re: TimeStamp Anomaly in SQL0_18_mebert@AMADEUS.NET30_Wed, 7 Feb 2001 09:51:52 +0100507_us-ascii I think the archives were not moved when the list did... in that case the story of that war is gone. Just to go on record again, I am opposed to James' view. There is no 24:00:00.000000 except in imprecise everyday language (don't know why, but I just remembered the 12 AM/PM controversy). There used to be computers having two representations for zero, +0 and -0. They died a deserved death. Aren't you glad you don't have to bother with that sort of thing each time you test for equality... [...]
20929 25 52_Re: An old question : Why DB2 uses only 48K / track?10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Feb 2001 10:25:26 +0100360_us-ascii There's (or were was ?) in RAMAC (and in a 'conventional' disk array) a filed called ID field that use approx 5%-15% of
disk capacity (it's variable), needed for data access and error detection.
But I read this some times ago, something may be changed now.
Anyway take a look at:
IBM RAMAC 3 Array Storage - sg244835. [...]
20955 53 15_REASON 00C900A815_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 7 Feb 2001 10:32:06 +0100484_iso-8859-1 Hello list,
Does any1 know what reason 00C900A8 means? We're on DB2v6 /Os390
DSNI002I -D1SO DSNISTFO UNABLE TO START OBJECT-TYPE 00000210 OBJECT-NAME GADB001T.GATS036 .001 REASON 00C900A8 TYPE 00000210 NAME GADB001T.GATS036 .00000001 DSN9023I -D1SO DSNTDDIS 'START DATABASE' ABNORMAL COMPLETION
Kind regards,
Jaap Slot, DB2 DBA, Rabobank ICT
Phone 0031 (0)30 215 2220 Mobile 0031 (0)6 5374 0167 e-Mail mailto:J.P.Slot@rf.rabobank.nl [...]
21009 50 55_Re: DB2 V6 for OS390 - Broken Pages in Very Large Table13_Tony Mitchell22_drdeath@OZEMAIL.COM.AU30_Wed, 7 Feb 2001 20:24:07 +1100597_iso-8859-1 Max,
Thank for offering suggestions.
Our problem has deteriorated.
DIS DB showed no restrictive state, We recovered the page. After our online day started we started to get more pages (all in the one partition) with the same message. DB2 then crashed. After it came up we found corrupted space map page in partition, we recovered the partition, and we started to get a 00c90101 message (on the index) for all inserts attempted on the tablespace. This produces a dump, but the updates commit and log normally. IBM has the dump. We rebuilt the index. No change. [...]
21060 67 51_Unsuccessful update of a varchar column through ADO11_Dimoka Popy24_dimokap@INTERAMERICAN.GR30_Wed, 7 Feb 2001 11:42:44 +0200686_- Hello List, we have a VB6+ADO2.5 application that accesses DB2 on OS/390 V6 tables through DB2 Connect V7.There is a table with a column defined as varchar(4000).We are trying to update this column through this application with a text of about 500 bytes.Then we get the following error:
SQL0102N The string constant beginning with "" is too long. (this is the explanation----------------------------------------------------------------- ------- Explanation: One of the following has occurred: * The comment in the COMMENT ON statement is greater than 254 bytes. * The application server name specified in the SQL CONNECT statement is greater than 18 characters. * [...]
21128 41 19_Re: REASON 00C900A80_18_mebert@AMADEUS.NET30_Wed, 7 Feb 2001 10:43:12 +0100351_us-ascii 00C900A8
Explanation: The table space, index, or partition could not be started because of a failure to acquire the lock. Activity on the table space, index, or partition must quiesce before the START DB ACCESS(FORCE) command can acquire the necessary lock.
The abend reason code is issued by the following CSECT: DSNISTFO [...]
21170 99 19_Re: REASON 00C900A80_29_paul.packham@POSTOFFICE.CO.UK30_Wed, 7 Feb 2001 09:44:32 +0000351_us-ascii Hi Jaap,
Explanation: The table space, index, or partition could not be started because of a failure to acquire the lock. Activity on the table space, index, or partition must quiesce before the START DB ACCESS(FORCE) command can acquire the necessary lock.
The abend reason code is issued by the following CSECT: DSNISTFO [...]
21270 16 55_Re: DB2 V6 for OS390 - Broken Pages in Very Large Table10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Feb 2001 10:44:34 +0100343_us-ascii No NPIs indexes on table ? No REORG/LOAD DISCARD executed ?
Regards
Max Scarpa
================================================ 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.
21287 19 19_Re: REASON 00C900A810_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Feb 2001 10:59:01 +0100421_us-ascii If you're starting the tablespace with FORCE option wait for a while, then the ts will restart (the time for a checkpoint).
Maybe.
Regards
Max Scarpa
================================================ 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.
21307 77 19_Re: REASON 00C900A815_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 7 Feb 2001 13:45:27 +0100662_iso-8859-1 Thanx all,
One of our developers was using a Compuware edit tool accessing the data. After he quit I was able to start the tablespace.
Kind regards,
Jaap Slot.
-----Oorspronkelijk bericht----- Van: mebert@AMADEUS.NET [mailto:mebert@AMADEUS.NET] Verzonden: woensdag 7 februari 2001 10:43 Aan: DB2-L@RYCI.COM Onderwerp: Re: REASON 00C900A8
00C900A8
Explanation: The table space, index, or partition could not be started because of a failure to acquire the lock. Activity on the table space, index, or partition must quiesce before the START DB ACCESS(FORCE) command can acquire the necessary lock. [...]
21385 200 49_Re: Result Set Not returned from Stored Procedure0_24_db46@DAIMLERCHRYSLER.COM30_Wed, 7 Feb 2001 08:00:42 -0500673_iso-8859-1 ---------------------- Forwarded by Dean J Burchill/TCC/DCC/DCX on 02/07/2001 07:59 AM ---------------------------
Dean J Burchill 02/06/2001 03:32 PM
To: DB2 Data Base Discussion List cc:
Subject: Re: Result Set Not returned from Stored Procedure (Document link: Dean J Burchill)
Okay, what is meant by 'successful' return? If you defined one or more result sets in SYSIBM.SYSPROCEDURES, then you should get a +466 SQLCODE back from the stored procedure call. A zero SQLCODE means that everything ran okay, but there is no cursors defined 'WITH RETURN' that were opened by the stored procedure. [...]
21586 77 19_AW: REASON 00C900A816_Reinecke, Thomas22_Thomas.Reinecke@TUI.DE30_Wed, 7 Feb 2001 11:02:47 +0100568_iso-8859-1 Read the manual.
-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]Im Auftrag von Slot, JP (Jaap) Gesendet am: Mittwoch, 7. Februar 2001 10:32 An: DB2-L@RYCI.COM Betreff: REASON 00C900A8
Hello list,
Does any1 know what reason 00C900A8 means? We're on DB2v6 /Os390
DSNI002I -D1SO DSNISTFO UNABLE TO START OBJECT-TYPE 00000210 OBJECT-NAME GADB001T.GATS036 .001 REASON 00C900A8 TYPE 00000210 NAME GADB001T.GATS036 .00000001 DSN9023I -D1SO DSNTDDIS 'START DATABASE' ABNORMAL COMPLETION [...]
21664 72 39_Re: Retrieving a ROW ID after an insert11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK30_Wed, 7 Feb 2001 14:39:12 -0000412_iso-8859-1 As usual many thanks to the responders. I have reviewed the archive postings and will take those ocncerns into consideration when we review the use of IDENTITY.
Carl.
-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: 06 February 2001 20:04 To: DB2-L@RYCI.COM Subject: Re: Retrieving a ROW ID after an insert
Hello Carl, Walter, [...]
21737 89 55_Re: DB2 V6 for OS390 - Broken Pages in Very Large Table23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Wed, 7 Feb 2001 14:39:51 -0000485_iso-8859-1 Tony
I am sure we have had similar here once or twice .. but not post upgrade.
You did a recovery (with index rebuild) but you still get the problem.
My next course of action would be to take a DSN1COPY with xlats etc (did you take a REFERENCE or CHANGE imagecopy in your backups?) to another region and play around with the copied pageset .. starting to sound like the problem has been there for a while .. how far back do you have image copies ? [...]
21827 49 19_Re: REASON 00C900A811_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Wed, 7 Feb 2001 05:20:59 -0500454_ISO-8859-1 Jaap
From V6 messages & codes: 00C900A8 Explanation:The table space,index,or partition could not be started because of a failure to acquire the lock. Activity on the table space,index,or partition must quiesce before the START DB ACCESS(FORCE)command can acquire the necessary lock.
My interpretation is that you are attempting a -START DATABASE ... ACCESS(FORCE) and are being blocked by a lock held by some other process. [...]
21877 85 19_new volumes for dba15_MAHSOUN, KHALID29_kmahsoun@SAUDIAIRLINES.COM.SA30_Wed, 7 Feb 2001 09:13:31 +0300230_us-ascii omer the volmes names are: for ALFURSAN DBDF01 MDR DBDM01 DBDM02
> Khalid Mahsoun > Database Support > Saudi Arabian Airlines > Tel: +966-2-686-4769 > Fax: +966-2-686-2962 > Email: kmahsoun@saudiairlines.com.sa > >
21963 86 35_Re: Buffer Pool Tuning / UDB on AIX15_Jackson Reavill18_damcon2@US.IBM.COM30_Wed, 7 Feb 2001 09:51:13 -0500612_us-ascii Hi Bill,
I don't have an answer, but I too have been thinking the same way. Common sense seems to say... yes, this practice should apply to just about any platform. For a new application we are developing I've setup bufferpools for indexes, read/write data, read only data, and tempspace, letting the system catalog be the only thing residing in the default bufferpool. I didn't concern myself with userspace because all of our tables are defined to their own tablespace which in turn is defined to the appropriate bufferpool. I also have setup multiple file systems to allocate the various [...]
22050 160 52_Re: An old question : Why DB2 uses only 48K / track?12_Kirk Hampton16_khampto1@TXU.COM30_Wed, 7 Feb 2001 08:55:59 -0600581_us-ascii In addition, there is another great discrepancy between DBA's and storage people, with regards to what is a megabyte and what is a gigabyte. DBA's and others who deal with pages in memory (bufferpools), refer to the 4096-byte DB2 VSAM record as 4K, a multiple of 1024. They then translate up to MegaBytes by dividing (n)K by 1024, and translate that to GigaBytes by dividing Megs by 1024. Storage people, on the other hand, are dealing with DASD manufacturers who are quoting sizes in bytes using the real Metric system, meaning 1000 bytes is a K, 1 million bytes is [...]
22211 67 16_Dynamic prefetch0_20_Gerhard.Heiss@RUV.DE30_Wed, 7 Feb 2001 16:02:31 +0100326_iso-8859-1 Dear fellow listers,
on DB2 for OS/390 V5 we have a cobol package with a static sql statement like this:
select c1,...,cn from table1 where c1 like :hostvar order by c1, c2 optimize for 1 rows with ur
clustering index on c1, c2 cluster ratio 99% Accesspath shows no sorts and no prefetch. [...]
22279 24 71_SQL to convert seconds to hrs, minutes, seconds in a TIME format needed13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Wed, 7 Feb 2001 10:13:08 -0500373_- Hi Listers,
I will like to take some time in seconds(e.g. 1691.590902, 1907.228611) and convert it to Hours, minutes, second and also display the result in a TIME format (HH:MM:SS) using SQL select statement. I have tried dividing value in seconds by 3600 (seconds in an hour) to get the hours, but how do I get the minutes and seconds in one sql statements. [...]
22304 19 52_Re: An old question : Why DB2 uses only 48K / track?0_29_paul.packham@POSTOFFICE.CO.UK30_Wed, 7 Feb 2001 15:28:04 +0000270_us-ascii Hi,
I hope the owners of this thread don't mind if I side-track somewhat, but as we are on the topic of DASD, does anyone out there use logical Model 9s rather than Model 3s to map onto their Raid Devices. If so are there any issues with doing so ? [...]
22324 57 14_FW: Data types11_Long, James19_c-jlong@STATE.PA.US30_Wed, 7 Feb 2001 10:27:21 -0500544_iso-8859-1 > I am getting really confused about data typing in DB2. I have some fields > that contain numeric data. Lets call these fields MM (month), DD(day), > YY(year), CCYY(4 digit year), and Dcode(8 digit number). All of these > appear as part of primary keys in various tables and appear as data in > other tables. These fields are used in calculations. When they appear in > the data, and there is no value, they contain spaces. This was loose > convention that was adopted for the IMS data world, where there are no > nulls. > > I [...]
22382 17 39_Re: Retrieving a ROW ID after an insert15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 7 Feb 2001 09:15:20 -0600337_- Well, now I have a question regarding ROWID (and now I really mean ROWID)
If I choose a ROWID-column as the first column of a partitioned index. Does that mean, that a reorg scrambles the rows among all partitions, which means, that a reorg for a single partition isn't possible or will all rows remain in their partition? [...]
22400 88 49_Re: Result Set Not returned from Stored Procedure11_Jeff Brokaw20_jeffbrokaw@YAHOO.COM30_Wed, 7 Feb 2001 09:26:33 -0600399_- Agree with the above, this could cause your problem. I believe the value for RESULT SETS must be >= the number of cursors left open. Definitely get that book, download the pdf, and also check the sample code supplied with UDB on the client.
Also check that your SP doesn't commit/rollback, will close any open cursors, the commit/rollback is to be controlled by the client connection. [...]
22489 135 39_Re: Retrieving a ROW ID after an insert23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Wed, 7 Feb 2001 10:43:22 -0500626_iso-8859-1 James,
"However, the rowid from before the reorg will still fetch the same row. "
Did you test it ? Here is an excerpt from 'DB2 release planning guide for ver 6"
Ensure that your application does not try to remember ROWID columns across reorganizations of the table space. When your application commits, it releases its claim on the table space; it is possible that a REORG can run and move the row, which disables direct row access. Plan your commit processing accordingly--use the returned row ID value before committing, or re-select the row ID value after a commit is [...]
22625 49 12_Schema names10_Mark Savin31_Mark.Savin@SCOOT-TECHNOLOGY.COM30_Wed, 7 Feb 2001 15:58:59 -0000428_iso-8859-1 Hi all,
I've got a question as to what ways there are of changing schema names in DB2. We're running DB2 v6.1 on AIX.
We've made a lot of changes between our UAT and pre-production environments and so, instead of of loading data between them, want to restore a database backup into pre-production. Unfortunately, the schema names differ, so we'd need to find a way of amending this. Any ideas how? [...]
22675 110 20_Re: Dynamic prefetch13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 7 Feb 2001 09:57:16 -0600588_iso-8859-1 Gerhard,
How do you turn off sequential detection? Don't turn off Sequential Prefetch.
You have specified OPTIMIZE FOR 1 ROW, although you have fetched 5. I don't understand. Why tell the optimizer that you want 1 row, and select more? Wouldn't OPTIMIZE FOR 5 ROWS be more correct?
I see this misuse of the OPTIMIZE clause alot, where the intention is to turn off List or Sequential Prefetch, which are actually there to reduce random I/O. Remember, Dynamic prefetch carries a greater overhead than if Sequential Prefetch was chosen in the beginning. [...]
22786 69 29_Re: DB2 Connect - TCPIP Error13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM30_Wed, 7 Feb 2001 10:13:38 -0600558_US-ASCII Vishy, I'm referring to the querytimeoutinterval on the client that is failing intermittently on long running queries. Kurt
>>> Viswanathan.N@POWAIMAIL.LTITL.COM 02/06/01 09:03PM >>> The Query TimeOut setting in the DB2 Server (OS390) is unlimited .... Vishy
Kurt Sahlberg on 07/02/2001 12:01:22 AM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM [...]
22856 35 46_UDB/Win95/AIX JDBC error when starting monitor15_Jackson Reavill18_damcon2@US.IBM.COM30_Wed, 7 Feb 2001 11:15:10 -0500375_us-ascii Hi all,
For some reason what used to work isn't anymore. I'm getting the following error when trying to start any UDB V6 on Win95 monitor. The instance/database that I'm pointing to is a remote UDB V6 database on AIX. I've tried mutiple db's on different AIX boxes and get the same error everytime. Can anyone shed some light on what this is telling me? [...]
22892 14 20_Re: Dynamic prefetch0_26_truman.g.brown@VERIZON.COM30_Wed, 7 Feb 2001 10:18:12 -0500299_us-ascii Have you tried OPTIMIZE FOR 1 ROW?
George
================================================ 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.
22907 49 76_Re: SQL to convert seconds to hrs, minutes, seconds in a TIME for mat needed13_Renjith Davis30_Renjith.Davis@MAIL.STATE.AR.US30_Wed, 7 Feb 2001 09:56:58 -0600732_iso-8859-1 SELECT TIME('00.00.00') + 1691.590902 SECOND FROM SYSIBM.SYSDUMMY1
Result:
COL1 -------- 00.28.11
Renjith
-----Original Message----- From: Carlton Enuda [mailto:carltonenuda@HOTMAIL.COM] Sent: Wednesday, February 07, 2001 9:13 AM To: DB2-L@RYCI.COM Subject: SQL to convert seconds to hrs, minutes, seconds in a TIME format needed
Hi Listers,
I will like to take some time in seconds(e.g. 1691.590902, 1907.228611) and convert it to Hours, minutes, second and also display the result in a TIME format (HH:MM:SS) using SQL select statement. I have tried dividing value in seconds by 3600 (seconds in an hour) to get the hours, but how do I get the minutes and [...]
22957 188 52_Re: An old question : Why DB2 uses only 48K / track?0_22_Rohn.Solecki@MTS.MB.CA30_Wed, 7 Feb 2001 10:23:09 -0600576_iso-8859-1 Good point, a while back we had a similar discussion on the list that included conversion charts (I've including some 'large size' definitions at the bottom). I've also included some 'collect storage facts' (which I hope are correct).
I was going to ask when byte/kilo/mega/gigabyte changed from powers of 2 to powers of 10, but you appear to have answered that question, the names are the same, but the actual value of the definitions depends on who you are talking to. So I guess the point is to make sure every one is 'on the same page' when you are [...]
23146 66 30_Re: Multiple Tables/tablespace10_Bill Welch27_spgwdw@UCLINK4.BERKELEY.EDU30_Wed, 7 Feb 2001 08:22:01 -0800493_us-ascii It's my understanding that, after a REORG, DB2 puts all the segments of one table together in a segmented tablespace.
Assuming you've allowed free space and free pages in your tablespace, DB2 will first insert rows in the page where the row should go until there is no more free space in that page. It will then insert rows in a free page that is as close to the "original" page as possible. It will then get a free page where it can, perhaps as far away as you describe. [...]
23213 74 18_Re: FW: Data types13_Helen Johnson25_helen_johnson@RAC.RAY.COM30_Wed, 7 Feb 2001 10:25:10 -0600320_us-ascii When our programmers converted IMS data to be loaded into DB2 tables, they converted the IMS date fields into a DB2 date format. If the date was blank, a blank was put in the load file in the date column. Then the load file would be coded like this: INV_DT POSITION (79) DATE EXTERNAL NULLIF(79:80) = ' ', [...]
23288 73 75_Re: SQL to convert seconds to hrs, minutes, seconds in a TIME format needed11_Suresh Sane21_data_arch@HOTMAIL.COM30_Wed, 7 Feb 2001 10:28:26 -0600481_- Carlton,
Try this. I am sure some V6 functions will simplify the task but this works for me in V5 style syntax.
SELECT SUBSTR(DIGITS( INTEGER(4567.590902)/3600 ),9,2) CONCAT ':' CONCAT SUBSTR(DIGITS ( (INTEGER(4567.590902) - INTEGER(4567.590902)/3600*3600)/60 ),9,2) CONCAT ':' CONCAT SUBSTR(DIGITS ( INTEGER(4567.590902) - INTEGER(4567.590902)/3600 * 3600 - (INTEGER(4567.590902) - INTEGER(4567.590902)/3600*3600)/60*60 ),9,2) FROM SYSIBM.SYSDUMMY1 WITH UR # [...]
23362 16 28_db2 stored procedure builder13_O'Neill, Mike19_Mike.O'Neill@53.COM30_Wed, 7 Feb 2001 11:45:48 -0500421_iso-8859-1 I am trying to test db2 stored procedure builder but I keep getting a -444 telling me that load module dsntpsmp can not be found. Anybody run into this???? Thanks, Mike
================================================ 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.
23379 208 55_Re: Unsuccessful update of a varchar column through ADO13_Andy Seuffert21_aseuffert@NEONSYS.COM30_Wed, 7 Feb 2001 10:54:29 -0600531_- Popy, as you provided in your explanation, DB2 has a limit of 254 bytes for a literal string in a where clause. I beleive this is being raised to 255 in V7 of DB2 for OS/390 to be more like other DBMSs. In order to get by this limitation you must specify the literal string as a host variable. The company I work for, NEON Systems, have a product called Shadow Direct which provides ODBC access to DB2 and virtually any other data residing on OS/390. In our ODBC driver we have a parameter which converts any literal into a [...]
23588 100 20_Re: Dynamic prefetch19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM30_Wed, 7 Feb 2001 08:57:46 -0800320_- We have a similar situation, where a very heavily used routine needs to return a consistently small set of fetch results. The impact on the bufferpool was to drive the System Hit Ratio to around -375% - i.e. it was fetching just enough rows to turn on pre-fetch, but then ended up throwing most of the data away. [...]
23689 15 14_Re: data types11_Long, James19_c-jlong@STATE.PA.US30_Wed, 7 Feb 2001 12:01:58 -0500456_iso-8859-1 I agree with you about dates (and times). However, the issue is coded numerics and counters. We also have things that are only day (like day of the month) or month (like registration renewal month). JimL
================================================ 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.
23705 105 55_Re: Unsuccessful update of a varchar column through ADO16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 7 Feb 2001 17:26:40 +0530300_iso-8859-1 Is not this the case of insert 254 bytes and update further ? I think so. Try doing so. Look at the statement in the user response below :"assign the long string to a host variable and substitute that variable for the string literal in the SQL statement."
HTH Regards, Sanjeev [...]
23811 90 19_Re: REASON 00C900A816_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 7 Feb 2001 17:36:11 +0530580_- Hi Jaap, The claims aquired by the currently running applications/sqls/other things(commands, utilities) could not be drained after the command is issued. It will be drained only if the running UOW is completed. Drain do not allow new claims to enter but the currently running claims have to be released (by commit). The Stop DB Access(force) is timed out. I think before issueing the stop db the locks should be checked. In this case as i can see you are using ACCESS(FORCE) so it must be some urgent need. Look at the IRLMRWT parameters for Timeout. It should not be too [...]
23902 85 19_Re: REASON 00C900A816_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 7 Feb 2001 17:39:43 +0530373_- Jaap, I am sorry, i did not look at your question correctly. It was not STOP, it was start with Access(force). My opologies !! So few questions for the fresh answer. Is the tablespace already in STOP state ? DO not think so otherwise the it could have been quiesed. Is there some offline stand alone utility running on tablespace.(this would have given -904 to you) [...]
23988 28 23_Recover tablespace.....17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Wed, 7 Feb 2001 11:03:28 -0600487_- Hello Folks,
We are using DB2 V6 on os390.. one of the tablespace has two tables TBL1 and TBL2 in test region. someone deleted the data from TBL2. so i need to recover the TBL2. but i have a image copy as of yesturday. and one more thing this image copy contains the data of two tables TBL1 and TBL2. if i wants to recover the data how do i recover it???? and how do i get the data from image copy since it is copy of two tables.... or is there any way to get the data???? [...]
24017 24 14_Re: Data types13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Wed, 7 Feb 2001 12:12:37 -0500413_iso-8859-1 Hi Jim, I can't agree more strongly with Helen's excellent suggestion.
In my half-vast experience, the only reasons not to put date/time data into date/time fields are bad reasons (fear, politics, not enough time to do it right, ... and a few that I'll politely refrain from mentioning).
You get so much from date arithmetic, not to mention format validation and domain enforcement. [...]
24042 123 29_Re: DB2 Connect - TCPIP Error15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 7 Feb 2001 18:21:18 +0100611_iso-8859-1 Kurt, Vishy,
In my shop we recently had a strange problem with DB2 Connect - TCPIP. Threads that weren't used for 1800 secs were terminated becouse of the zparm settings on DB2. Conclusive testing showed that the tread wasn't terminated from the NT-server side. Thus the Java application on the NT-machine 'thought' there was still a connection. This resulted in a very unsatble NT-machine and finally a crash of the application. We made a workaround, taking care of cancelling all treads by the application on the NT when a thread cancellation is done by DB2. Sofar no-one could tell us [...]
24166 33 32_Re: db2 stored procedure builder13_Kenski, David25_David.Kenski@MARRIOTT.COM30_Wed, 7 Feb 2001 12:29:21 -0500647_- Mike, Check the SPAS to ensure it contains the Stored Procedure load library where the module was moved/linked OR See if the library went into extents...
Dave Kenski
> -----Original Message----- > From: O'Neill, Mike [SMTP:Mike.O'Neill@53.COM] > Sent: Wednesday, February 07, 2001 11:46 AM > To: DB2-L@RYCI.COM > Subject: db2 stored procedure builder > > I am trying to test db2 stored procedure builder but I keep > getting a -444 telling me that load module dsntpsmp can not > be found. Anybody run into this???? > Thanks, > Mike > > ================================================ > To change your subscription options or [...]
24200 34 32_Re: db2 stored procedure builder13_Kenski, David25_David.Kenski@MARRIOTT.COM30_Wed, 7 Feb 2001 12:29:52 -0500606_- > Mike, > Check the SPAS to ensure it contains the Stored Procedure load library > where the module was moved/linked OR See if the library went into > extents... > > Dave Kenski > > -----Original Message----- > From: O'Neill, Mike [SMTP:Mike.O'Neill@53.COM] > Sent: Wednesday, February 07, 2001 11:46 AM > To: DB2-L@RYCI.COM > Subject: db2 stored procedure builder > > I am trying to test db2 stored procedure builder but I keep > getting a -444 telling me that load module dsntpsmp can not > be found. Anybody run into this???? > Thanks, > Mike > > ================================================ [...]
24235 144 14_Re: SQL Puzzle16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU30_Wed, 7 Feb 2001 12:48:00 -0500565_iso-8859-1 Hey Peter... Thanks for your reply... The account number, which is the first column of the table & index is basically an encounter number. The mrno, medical record number is forever. So in essence, you have one mrno and possibly a whole lot of account numbers. You get a unique account number each time you visit the hospital. What I did, was to put an index on the mrno and it cleared up the ts scan. The FIRSTKEYCARD is 255,080, while the FULLKEYCARD is 1,033,788. I'm open to suggestions if you see anything out of the ordinary.. Thanks for your [...]
24380 115 32_Re: db2 stored procedure builder12_Isaac Yassin23_yassin@NETVISION.NET.IL30_Wed, 7 Feb 2001 20:13:29 +0200656_iso-8859-1 Hi, DSNTPSMP is a rexx that should be in *.NEW.SDSNCLST. In order to use the SPB (Stored Procedure Builder) you need a WLM startup procedure and the REXX-DB2 support. Take a look at Section 7 of DB2 Application Programming and SQL Guide.
"7.2.3 Setting up the stored procedures environment
This section discusses the tasks that must be performed before stored procedures can run. Most of this information is for system administrators, but application programmers should read "Defining your stored procedure to DB2" in topic 7.2.3.1. That section explains how to use the CREATE PROCEDURE statement to define a stored procedure [...]
24496 50 32_Re: db2 stored procedure builder13_O'Neill, Mike19_Mike.O'Neill@53.COM30_Wed, 7 Feb 2001 13:11:55 -0500371_iso-8859-1 Thats the problem, I cann't find a load module with the name of dsntpsmp in any load library or runlib. You won't know where this module get linked would you???
-----Original Message----- From: Kenski, David [mailto:David.Kenski@MARRIOTT.COM] Sent: Wednesday, February 07, 2001 12:30 PM To: DB2-L@RYCI.COM Subject: Re: db2 stored procedure builder [...]
24547 26 23_Data Model architecture0_20_John_Lendman@FPL.COM30_Wed, 7 Feb 2001 13:22:47 -0500364_us-ascii I friend of my has ask me to ask this question on the list. I really don't of much information about it but here it goes
They are looking to reenginerring effort to provide a common customer experience regardless of channel, i.e. tell, ATM, Call center, etc. . They have embarked on building a data architecture that will handle this. [...]
24574 84 46_UDB/Win95/AIX JDBC error when starting monitor15_Jackson Reavill18_damcon2@US.IBM.COM30_Wed, 7 Feb 2001 13:32:55 -0500646_us-ascii Looks like the screen paste isn't coming thru so here's the text of the message:
DB2 Message: DBA4090 Connection to the JDBC server has ended abnormally. Close the Control Center and retry the operation. If the problem persists, take a trace at the JDBC server and contact support personnel.
Thanks, Jay
Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 801-7303, Tie Line 8-427-7303 ----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - Bavarian Motor Works ----------------------------------------------------- [...]
24659 45 10_Re: db2set15_McClendon, Rick24_RMcClendon@ADMIN.FSU.EDU30_Wed, 7 Feb 2001 13:35:54 -0500353_iso-8859-1 F.Y.I. It works! We just joined a UDB nicknamed Oracle table to a UDB nicknamed OS/390 table! Our UDB to OS/390 pass-through stored procedure call is working now too.
D B 2 IS COOL!
-----Original Message----- From: McClendon, Rick Sent: Monday, February 05, 2001 3:55 PM To: 'DB2 Data Base Discussion List' Subject: db2set [...]
24705 38 28_Performance Text Suggestions12_GENE TILELLI16_geetee52@USA.NET28_Wed, 7 Feb 2001 13:43:09 EST404_US-ASCII Hi All...
I use Craig Mullins' Developer's Guide quite a bit for reference which is very useful.
However, I'm looking for a book that focuses more on performance aspects. Is there a text that most people here agree on that is best for this purpose?
Any suggestions are appreciated.
Thanks.
Gene Tilelli EDS DBA South Florida Solution Centre (954)575-1627 [...]
24744 52 27_Re: Data Model architecture16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM30_Wed, 7 Feb 2001 13:45:39 -0500572_us-ascii John, you might try Telcordia Technologies at http://www.telcordia.com/ detailed info http://www.telcordia.com/products_services/oss/custcare/index.html Tom F
John_Lendman@FPL.COM on 02/07/2001 01:22:47 PM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: Data Model architecture
I friend of my has ask me to ask this question on the list. I really don't of much information about it but here it goes [...]
24797 76 32_Re: Performance Text Suggestions0_22_BILL_GALLAGHER@PHL.COM30_Wed, 7 Feb 2001 13:56:05 -0500431_us-ascii Gene,
Try "DB2 High Performance Design and Tuning" by Richard Yevich and Susan Lawson. I believe you can order it from Amazon.com or any other online book retailer.
Bill Gallagher, DBA Phoenix Home Life Enfield, CT 06083
GENE TILELLI cc: Sent by: DB2 Subject: Performance Text Suggestions Data Base Discussion List [...]
24874 67 32_Re: db2 stored procedure builder13_Kenski, David25_David.Kenski@MARRIOTT.COM30_Wed, 7 Feb 2001 13:57:29 -0500633_- Member DSNTEJ65 of DSNSAMP is the job to prepare the module. You will need REXX language support for DB2 to use. Dave
> -----Original Message----- > From: O'Neill, Mike [SMTP:Mike.O'Neill@53.COM] > Sent: Wednesday, February 07, 2001 1:12 PM > To: DB2-L@RYCI.COM > Subject: Re: db2 stored procedure builder > > Thats the problem, I cann't find a load module with the name > of dsntpsmp in any load library or runlib. You won't know > where this module get linked would you??? > > -----Original Message----- > From: Kenski, David [mailto:David.Kenski@MARRIOTT.COM] > Sent: Wednesday, February 07, 2001 12:30 PM > [...]
24942 72 32_Re: db2 stored procedure builder13_Kenski, David25_David.Kenski@MARRIOTT.COM30_Wed, 7 Feb 2001 13:58:17 -0500596_- > Mike, > Member DSNTEJ65 of DSNSAMP is the job to prepare the module. You will need > REXX language support for DB2 to use. > Dave > > > -----Original Message----- > From: O'Neill, Mike [SMTP:Mike.O'Neill@53.COM] > Sent: Wednesday, February 07, 2001 1:12 PM > To: DB2-L@RYCI.COM > Subject: Re: db2 stored procedure builder > > Thats the problem, I cann't find a load module with the name > of dsntpsmp in any load library or runlib. You won't know > where this module get linked would you??? > > -----Original Message----- > From: Kenski, David [mailto:David.Kenski@MARRIOTT.COM] > Sent: [...]
25015 74 39_Re: Retrieving a ROW ID after an insert19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Wed, 7 Feb 2001 12:59:47 -0600600_ISO-8859-1 Hi Dave, Your comments/concerns appreciated. Some further thoughts on this issue if you've the time. When using IDENTITY columns, handled in the ways both you and I have described, I see no restriction on using them either as a single column PK or in a multi-column PK. I've a design under way that currently uses IDENTITY columns in the PKs that will be inherited to FKs. Do you see any "gotchas"? Seems to me using IDENTITY columns abates some of the problems when using PK value tables and all the locking, deadlocking, and concurrency problems associated with them. Maybe someone [...]
25090 55 27_Re: Recover tablespace.....15_Di Carlo, Donna23_Donna_Bermender@BMC.COM30_Wed, 7 Feb 2001 13:02:20 -0600449_iso-8859-1 Hi Ravi,
If you don't have a vendor product that allows you to back out the bad deletes, I would suggest the following steps:
1) Create a new table space exactly like the one you want to recover. 2) Use DSN1COPY with XLAT to restore yesterday's image copy to the new table space. Remember to translate both tables. 3) Insert the rows from new TBL2 into the old TBL2 or unload/reload the rows from the new into the old. [...]
25146 69 32_Re: db2 stored procedure builder13_Kenski, David25_David.Kenski@MARRIOTT.COM30_Wed, 7 Feb 2001 14:14:56 -0500599_- Mike, Sorry that was the job to test calling DSNTPSMP. The module is in DSNCLST as Isaac said. Several jobs are in the install library to create the procedure entry for DSNTPSMP, copy the clist, create the database, and test calling the database. Dave
> -----Original Message----- > From: O'Neill, Mike [SMTP:Mike.O'Neill@53.COM] > Sent: Wednesday, February 07, 2001 1:12 PM > To: DB2-L@RYCI.COM > Subject: Re: db2 stored procedure builder > > Thats the problem, I cann't find a load module with the name > of dsntpsmp in any load library or runlib. You won't know > where this module [...]
25216 20 35_DB2 Active logs-greater than 2 gigs22_KOA DAVID S. (dbm1dxk)15_dbm1dxk@UPS.COM30_Wed, 7 Feb 2001 14:33:05 -0500357_iso-8859-1 I am currently in the process of testing DB2 active logs sized between 2-4 gigs archiving to tape(yes it's possible). I have been told that other customers have successfully done this. If you are one of them, can you let me know your experience and any recommendations.
thanks, David Koa DB2 Systems Programming United Parcel Service [...]
25237 17 28_enqueue on SYSZDSN3 DSNYALLI13_Peter Farrell15_pfarrel@TWA.COM30_Wed, 7 Feb 2001 13:40:28 -0600527_- We had an incident last night with DB2MSTR enqueued on SYSZDSN3 DSNYALLI, with several other enqueues waiting, also from DB2MSTR. Can anyone explain what causes this ? It went on for over an hour. For the last 38 minutes, IMS would not schedule any DB2 transactions, even though the message regions were empty. The DB2 messages just piled up on the input queue. The problem was finally resolved by cancelling some long running DB2 jobs. This is a serious problem, as it causes an outage, and no messages to tell us why. [...]
25255 166 39_Re: Retrieving a ROW ID after an insert20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Wed, 7 Feb 2001 15:11:30 -0500316_iso-8859-1 Sanjay
Its been a while but as far as I remember Jay Yother (IBM) telling that ROWID value (not the physical value) across REORG would remain same but would disable direct row access (which is a new access path in V6). DB2 would still find you the right row but at the expense of access path. [...]
25422 74 27_Re: Recover tablespace.....13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Wed, 7 Feb 2001 14:09:40 -0600355_iso-8859-1 A couple other options you might consider (I tend to get a little nervous using dsn1copy)...
- If you have a 3rd party unload utility that allows unloads from an imagecopies, "recover" the table using an unload and load (unload from the imagecopy, delete the existing rows from the table and load-resume the unloaded data back in). [...]
25497 129 32_Re: Performance Text Suggestions12_Susan Lawson22_lawson_susan@YAHOO.COM30_Wed, 7 Feb 2001 13:25:03 -0800332_us-ascii Gene,
You can also order our book via our web site at www.ylassoc.com.
Also, we offer the DB2 Performance Journal which is a quarterly fee based subscription offering 6-8 white papers on DB2 performance topics, hints and tips, etc. You can download a free copy of Vol 1.1 from our site -www.ylassoc.com. [...]
25627 100 43_Re: regarding load.... need immediate reply16_Cockerill, Steve30_SLCockerill@NATIONALGYPSUM.COM30_Wed, 7 Feb 2001 16:49:35 -0500370_iso-8859-1 1. If the tablespace is empty use Load RESUME NO
2. If tablespace contains data in tables to be kept use Load RESUME YES .
3. If all tables in tablespace are being refreshed(loaded) change the first table referenced in the tablespace to Load resume REPLACE (empties all tables in tablespace) and change all other tables to Load RESUME YES. [...]
25728 127 43_Re: regarding load.... need immediate reply14_William Poston21_william.poston@DB.COM30_Wed, 7 Feb 2001 16:11:04 -0600547_us-ascii Also if you loading large amounts of data consider using log no.
---------------------------------------- Message History ----------------------------------------
From: SLCockerill@nationalgypsum.com on 02/07/2001 09:49 PM GMT
Please respond to DB2-L@ryci.com
To: DB2-L@ryci.com cc: Subject: Re: regarding load.... need immediate reply
1. If the tablespace is empty use Load RESUME NO
2. If tablespace contains data in tables to be kept use Load RESUME YES . [...]
25856 12 55_Re: DB2 V6 for OS390 - Broken Pages in Very Large Table11_Fred Nijdam20_f.nijdam@SYNTEGRA.NL30_Wed, 7 Feb 2001 16:06:39 -0600426_- What V6 level is running. We turned on the may-2000 version with some additional ptf's 2 weeks ago on a huge SAP environment, and didn't experience (too) serious problems uptill now.
================================================ 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.
25869 151 52_Re: An old question : Why DB2 uses only 48K / track?15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Thu, 8 Feb 2001 09:31:02 +1100412_iso-8859-1 Hi Nick,
Actually you can hold more than 4Gb in a dataset if a) you're V6, b) you specifiy DSSIZE 8Gb (or larger) and c) you use a dataclass that allows extended addressability. Plus probably a few more things. I've done it here a few days ago. Loaded 13Gb into one partition of a 9-partition tablespace. Went to around 250,000 tracks over 20 volumes and 171 extents. Ugly, but it worked. [...]
26021 88 12_Re: dsn1copy15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Thu, 8 Feb 2001 09:44:43 +1100589_- Hi Ian,
You're not alone in this one. I've got a damned annoying problem with BMC's LoadPlus that they and we are looking into. I'm loading 9 tablespaces with a fair bit of data. The first will be huge (13Gb in the first partition, slightly smaller in the others) while the other 8 will be less impressive (less than 2Gb/partition). To go over the 4Gb dataset limit our Storage Management people here setup a dataclass that allows extended addressability. Worked a treat. Loaded up the first partitions of all these tablespaces - including the 13Gb one - no problem. Spanned [...]
26110 69 55_Re: DB2 V6 for OS390 - Broken Pages in Very Large Table10_Shery Hepp17_schepp@SRPNET.COM30_Wed, 7 Feb 2001 16:26:43 -0700487_ISO-8859-1 Tony- not sure if this applies in your situation- but there was a problem with 00c90101's occurring. Had to do with space map pages being corrupted during incremental image copies- here's the apar- Item PQ41076
Hope this helps- Shery Hepp SRP
-----Original Message----- From: Tony Mitchell [mailto:drdeath@OZEMAIL.COM.AU] Sent: Wednesday, February 07, 2001 2:24 AM To: DB2-L@RYCI.COM Subject: Re: DB2 V6 for OS390 - Broken Pages in Very Large Table [...]
26180 149 39_Re: Retrieving a ROW ID after an insert14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 8 Feb 2001 10:53:08 +1100486_iso-8859-1 This response is specifically directed to use of rowids. I'll have another response to Rick Davis' questions about identity columns as PK/FKs
My testing shows that a (GENERATED ALWAYS) rowid in the VSAM cluster consists of: (1) a unique value, in the first 5 (?-can't remember) bytes (2) an id which identifies when the tablespace was last reorg/loaded prior to the row's _insertion_. This does not appear to be an RBA/LRSN, but is presumably based on it somehow [...]
26330 88 27_Re: Recover tablespace.....14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU30_Wed, 7 Feb 2001 19:56:44 -0500541_iso-8859-1 > Ravi, > > I generally categorise these things as "You broke it, you fix it" :) > > As it is a test region, I'd be inclined to restore the tablespace to > yesterday's image copy, and politely advise the culprit that they can now > recreate any missing data. Maybe not politically correct, but virtually > guarantees they won't do it again. > > Otherwise, Donna's suggestion will work fine as long as you haven't had an > ALTER on the table(s) to add a column, and not subsequently reorged it. > > Regards, > Greg Palgrave > [...]
26419 111 29_Re: DB2 Connect - TCPIP Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Thu, 8 Feb 2001 08:21:57 +0530364_us-ascii The query timeout in the client is also very high ... and again the same query I run using spufi .. I get results in sub seconds ..... so , I dont think its a timeout problem .. again ...the same query I run from the Db2 Connect GUI .. then also it gives sub second results... but when I repeatedly try running the same query ... it suddely hangs... [...]
26531 105 63_Identity column as PK (was Retrieving a ROW ID after an insert)14_James Campbell29_James.Campbell@HANCORP.COM.AU30_Thu, 8 Feb 2001 14:07:11 +1100682_iso-8859-1 Rick, Read the "Identity revisted" thread around 15-17 August 2000 for some of the problems you'll face.
James
-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:rd8246@SBC.COM] Sent: Thursday, February 08, 2001 6:00 AM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Retrieving a ROW ID after an insert
Hi Dave, Your comments/concerns appreciated. Some further thoughts on this issue if you've the time. When using IDENTITY columns, handled in the ways both you and I have described, I see no restriction on using them either as a single column PK or in a multi-column PK. I've a design under way that currently uses IDENTITY [...]
26637 47 52_Re: An old question : Why DB2 uses only 48K / track?18_Sundaresan, Sundar33_Sundar.Sundaresan@SEA.SIEMENS.COM30_Wed, 7 Feb 2001 22:10:47 -0500532_iso-8859-1 Since I have not seen any other postings on this issue here goes
We have run a few tests with Model 9 and had no problems.
This is on an SVA with OS/390 2.8 ( fairly up on maintenance )
Regards Sundar Sundaresan
sundar.sundaresan@sea.siemens.com
-----Original Message----- From: paul.packham@POSTOFFICE.CO.UK [mailto:paul.packham@POSTOFFICE.CO.UK] Sent: Wednesday, February 07, 2001 10:28 AM To: DB2-L@RYCI.COM Subject: Re: An old question : Why DB2 uses only 48K / track? [...]
26685 209 31_DDF Codepage conversion problem13_Guy Smallwood19_GSmallwood@HUON.COM30_Thu, 8 Feb 2001 16:22:41 +1100528_iso-8859-1 We are running UDB 5.2 on NT trying to connect to two OS/390 DB2 V5.1 sub-systems.
DB2A is running in codepage 1027 (Ext Alpha Lowercase) DB2B is running in codepage 290 (IBM EBCIDIC - Japanese Katakana Extended) NT Server is running in codepage 1252 (ANSI Latin 1)
Although the above are double byte character sets, we only want to exchange single-byte data between OS/390 and NT. When I try to connect to DB2B I get the following message (DB2A yields the same message with appropriate codepage): [...]
26895 112 20_Re: Dynamic prefetch16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 8 Feb 2001 10:13:09 +0530379_- Gerhard,
I will go by Terry's response. If you are able to afford(you should not be) the same amount of getpage(if at all it happens) with the synch. I/O then you should not turn the sequential detection off. First idea is to tune the query. It looks to me the non-matching index scan is done and optimizer is scanning the index pages sequentially at the run time. [...]
27008 75 27_Re: Recover tablespace.....16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 8 Feb 2001 10:34:44 +0530460_- I think
UNLOAD TBL1 --> RECOVER TABLESPACE --> LOAD TBL1(RESUME) strategy will work in this case.
or 6 Lines REXX.
SELECT * FROM TBL1 CRETE GLOBAL TEMP TABLE TMP AS TBL1 INSERT INTO TMP SUBMIT JCL FOR RECOVER WAIT FOR SOMETIME(till the JCL completes) INSERT INTO TBL1 SELECT * FROM TMP
I believe most of the installation is having many REXX's coded for each individual functions. Only copy paste is to be done for this EXEC. [...]