1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l June 2001, week 1 2 38 47_Re: DB2V6 RID-Pool max limit during hybrid join16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 1 Jun 2001 10:47:51 +0530431_- - what happens if a hybrid join is in progress and reaches max limit in RID > pool? > Reply - If it is already in progress and the max limit is reached, will not it be backed by disk to sort the RID. I am not sure if hybrid join is already started then it can be converted to other join process due to RID pool failure is possible or not. This kind of situation will arise by giving inappropriate informations to optimizer. [...] 41 12 70_Re: DB2 V5.1 - SQL PL Stored Proc. calling another SQL PL Stored Proc.20_Dr. Matthias Wehrens29_matthias.wehrens@AM-GRUPPE.DE30_Fri, 1 Jun 2001 01:18:47 -0500338_- Nesting stored procedures is available with Version 6 - up to maximum of 16 levels of nesting.

================================================ 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. 54 15 27_Re: Unexpected SQLCODE -80520_Dr. Matthias Wehrens29_matthias.wehrens@AM-GRUPPE.DE30_Fri, 1 Jun 2001 03:23:05 -0500369_- Have you checked privileges of the plan owner DB2DBA in DBP2? He must have at least have "execute on collid.*" for all the DC..- collections. (These privileges for the remote system are evaluated at runtime!) Maybe he has taht privilege only for collection DCMITMS - that would result in this error (-805, RC 04), because DCITMS ist the only "valid" collection. [...] 70 58 47_Re: DB2V6 RID-Pool max limit during hybrid join14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Fri, 1 Jun 2001 04:30:38 -0500335_iso-8859-1 If a RID pool limit is exceeded during the execution of a query, DB2 will revert to using a tablespace scan.

This can (and does!) mean that you can spend a lot of time building a RID list, then hit a limit, then throw all the RID information away, then restart (yes - restart) the query using a tablespace scan. [...] 129 86 12_Re: DB2 Load16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM30_Fri, 1 Jun 2001 10:58:08 +0100450_us-ascii Hi Charles,

I do not remember the sybtax of the DEFAULTIF option that would answer your requirements, this is one of the options in LOAD and you would find its specs in the Utility Guide and reference manual.

HTH.

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com [...] 216 162 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 1 Jun 2001 07:22:54 -0400573_iso-8859-1 Will do. We typically do DSN1CHKR only when upgrading releases but I will run this on our QA subsystems this week.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: LOMBARD,Shaun [mailto:Shaun.LOMBARD@DEWRSB.GOV.AU] Sent: Thursday, May 31, 2001 7:47 PM To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805



Eric,

It should not make a difference, but I would run the DSN1CHKR and CHECK INDEX utilities just to make sure you're dealing with a consistent catalog and directory. [...] 379 37 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 1 Jun 2001 07:25:02 -0400708_iso-8859-1 Plan owners are SYSADM on local and remote subsystems.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Dr. Matthias Wehrens [mailto:matthias.wehrens@AM-GRUPPE.DE] Sent: Friday, June 01, 2001 4:23 AM To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805



Have you checked privileges of the plan owner DB2DBA in DBP2? He must have at least have "execute on collid.*" for all the DC..- collections. (These privileges for the remote system are evaluated at runtime!) Maybe he has taht privilege only for collection DCMITMS - that would result in this error (-805, RC 04), because DCITMS ist the only "valid" collection. [...] 417 64 26_Re: Identity cache setting14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Fri, 1 Jun 2001 06:26:17 -0500400_- Another point for those who are sysplexing, is that each system will obtain its own set of values. This means that when system A does its first insert, it will get, say, values 1, 2 and 3 - and use '1' as the key of the inserted row. If system B does the next insert, it will obtain values 4, 5 and 6 - and use '4' as its key. When system A does its next insert, it will use '2'. [...] 482 195 27_Re: Unexpected SQLCODE -8059_Colin Fay13_cfay2@CSC.COM30_Fri, 1 Jun 2001 07:08:16 -0400642_us-ascii Hi,

A few off-the-wall thoughts:



Are you sure that this is not an authorization problem masking as a 'not found'?

Are you using thread reuse and relese deallocate? Try looking at :

http://www.db2mag.com/db_area/archives/2001/q1/db2dba.shtml

for R Catteral's article on packages.plans and collections , maybe it will stimulate some investigation

"If, for performance reasons, you drive thread reuse and bind your most frequently executed programs with RELEASE(DEALLOCATE), you'd better not think of any collection in a multicollection package list as the default" - R Cattreall. [...] 678 65 19_Re: JDBC maintenace11_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Fri, 1 Jun 2001 06:59:06 -0500426_iso-8859-1 What a coincidence. I've been working on our first V6 maintenance and wondering the same thing. We have maintenance versions of the DB2 libraries & that's where maintenance is applied. We then change the procs for a subsystem to point to the maint libraries when we upgrade that system. When we're ready to upgrade production, we copy the maint libraries to the production libraries and undo the proc changes. [...] 744 74 25_Re: QMF VER 2.0 RELEASE 411_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Fri, 1 Jun 2001 07:11:46 -0500429_iso-8859-1 When I was preparing to upgrade DB2, we were trying to decide whether to upgrade DB2 & QMF at the same time, DB2 and then QMF, or QMF and then DB2. Back in July of last year, a list member stated they upgraded QMF first. I think you do have to order QMF as part of DB2 V6, but I think you can upgrade QMF first and then DB2. We ended up upgrading DB2 first and are now in the process of upgrading QMF (from 3.3). [...] 819 120 57_Visual Basic 5.0 and slow performance to DB2 via odbc 6.114_Enrico Amisano18_eamisano@GUALA.NET30_Fri, 1 Jun 2001 14:12:40 +0200383_iso-8859-1 Hi to all

I am migrating to MS Access 97 to DB2 UDB 6.1 and I have a lot of performance problems with DB2.

I'm connecting to db2 6.1 via odbc but I have bad performance with little tables (5 tables of 5.000 records each and 1 table of 70.000 records)

I use visual basic standard data control to put data in a grid during the open of the program. [...] 940 218 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 1 Jun 2001 09:13:25 -0400408_iso-8859-1 If it is authorization it is subtle! PLANs and PACKAGEs are bound AUTH(BIND). Binder and owner of plans and packages is SYSADM on local and remote subsystems.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Colin Fay [mailto:cfay2@CSC.COM] Sent: Friday, June 01, 2001 7:08 AM To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805 [...] 1159 33 16_JAVA STORED PROC13_Todd Peterson23_toddpeterson2@YAHOO.COM30_Fri, 1 Jun 2001 06:18:38 -0700460_us-ascii We were working toward using JAVA procedures in version 7. We were having difficulty defining a JAR ID and asked IBM for help. We were told that JAVA stored procedures would not work until after GA of version 7. Yet it appears from reading the listserv that people are building JAVA stored procedures. My question is -- are you really building and executing JAVA stored procedures -- or are you building and executing COMPJAVA stored procedures? [...] 1193 46 14_Re: wait times10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 1 Jun 2001 15:21:56 +0200325_us-ascii Hi Joel & thanks for your reply.

I read the value 100 in a Chuck Hoover's article:

'If the sum of these page (read+write page-ins - my note) counters divided by the time period of the data recording cycle is greater than 100 per second, and expanded storage is not over taxed by non- DB2 work...' [...] 1240 71 19_Re: JDBC maintenace9_Mike Deer20_Michael.Deer@UAL.COM30_Fri, 1 Jun 2001 08:36:38 -0500597_- You probably *want* DSNJDBC_JDBCProfile.ser copied over to production if you applied maintenance. All the ptfs I have seen for JDBC are full replacements for the /usr/lpp/db2/db2610 directories and required us to rerun db2genJDBC (which creates the file above). If you have something specific in db2sqljjdbc.properties I suppose you could keep this someplace other than /usr/lpp/db2/db2610. We dont have anything that is subsystem/lpar specific in our .properties file. I let jdbc figure out which subsystem to connect to by letting if find DSNHDECP via the STEPLIB environmental variable. [...] 1312 43 41_regarding unload...... reply immediately,17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Fri, 1 Jun 2001 08:31:41 -0500540_- Hi Folks,

We are using DB2 V6 on OS390, someone requested the data to be unloaded from prod to a dataset and we have to transfer to PC file. they will use this text file from PC for testing in UDB. each data field is seperated by comma(,). and also they want all char datafield by quotes like 'BENGAMENNNNN'. i am just confused how to unlload the data seperated by comma and char data in quotes... is there anyway i can do that... or is there any alternative way option in either notepad or worddoc. becasue data is tooo much [...] 1356 53 20_Re: JAVA STORED PROC14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Fri, 1 Jun 2001 09:55:24 -0400683_iso-8859-1 Hi Todd,

We ARE building Java stored procs (with SQLJ) and calling them from Java application programs. We use the Stored Procedure Builder to build the procs.

Abhijit

-----Original Message----- From: Todd Peterson [mailto:toddpeterson2@YAHOO.COM] Sent: Friday, June 01, 2001 9:19 AM To: DB2-L@RYCI.COM Subject: JAVA STORED PROC



We were working toward using JAVA procedures in version 7. We were having difficulty defining a JAR ID and asked IBM for help. We were told that JAVA stored procedures would not work until after GA of version 7. Yet it appears from reading the listserv that people are building JAVA stored procedures. [...] 1410 59 45_Re: regarding unload...... reply immediately,13_Bright, Randy20_Randy_Bright@BMC.COM30_Fri, 1 Jun 2001 09:01:03 -0500646_iso-8859-1 If you have Unload Plus from BMC Software you can specify "FORMAT CSV" when unloading from OS/390 to format the data exactly as in your example.



-----Original Message----- From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] Sent: Friday, June 01, 2001 8:32 AM To: DB2-L@RYCI.COM Subject: regarding unload...... reply immediately,



Hi Folks,

We are using DB2 V6 on OS390, someone requested the data to be unloaded from prod to a dataset and we have to transfer to PC file. they will use this text file from PC for testing in UDB. each data field is seperated by comma(,). and also they want all [...] 1470 26 36_create new index caused -904 in CICS12_Kirk Hampton16_khampto1@TXU.COM30_Fri, 1 Jun 2001 09:02:40 -0500563_us-ascii We had what we feel like was an unreasonable event this morning. One of our DBA's created a new index in production with DEFER. Then he tried to run a REBUILD INDEX on it, which failed because CICS was running against the tablespace, so the status of the index was RBDP, rebuild pending. At about the same time, CICS transactions started reporting a failure of -904, naming the new index which was rebuild pending. The CICS plan is old-style DBRM, no packages, last rebound in 1999, no dynamic SQL that we are aware of, in fact I scnned the DBRMLIB [...] 1497 77 14_Re: wait times14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Fri, 1 Jun 2001 10:07:41 -0400358_ISO-8859-1 Hi Max, The old 500 pages/sec times the number of engines has been out there for decades.. About a dozen yrs ago I was in an IBM MVS tuning clas, and the instructor added... except when you are running DB2 - then it should be less than 250 pages/sec per engine. These nembers were an absolute max, beyond which the system was really dead.... [...] 1575 111 45_Re: regarding unload...... reply immediately,14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM30_Fri, 1 Jun 2001 07:09:21 -0700392_iso-8859-1 Here is how I do what you are asking. SELECT FK.TBNAME ,'~' ,FK.RELNAME ,'~' ,FK.COLSEQ ,'~' ,FK.COLNAME ||'~'|| RL.REFTBNAME ||'~'|| RL.DELETERULE ||'~'|| RL.IXNAME ||'~' FROM SYSIBM.SYSFOREIGNKEYS FK ,SYSIBM.SYSRELS RL WHERE FK.CREATOR = '??????' AND RL.CREATOR = '???????' AND FK.CREATOR = RL.CREATOR AND FK.RELNAME = RL.RELNAME AND FK.TBNAME = RL.TBNAME ORDER BY 1, 3, 5; [...] 1687 60 40_Re: create new index caused -904 in CICS9_Sally Mir22_Sally.Mir@WACHOVIA.COM30_Fri, 1 Jun 2001 10:21:21 -0400437_us-ascii Are the CICS transactions performing INSERTs against this index's table? If so, DB2 must insert an entry into each index defined on the table for each row inserted. If you're only performing SELECTs, then that is very odd.....

Sally Mir









Kirk Hampton @RYCI.COM> on 06/01/2001 10:02:40 AM

Please respond to DB2 Data Base Discussion List [...] 1748 25 14_Re: wait times10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 1 Jun 2001 16:32:59 +0200454_us-ascii Hi Joel, I absolute agree.

I observed the same, even if I saw problems with a value of paging rate slightly greater than 100 (say 150) but there were other problems in that system. When the rate is > 200 then problems begin....

So I think that 100 pageins/sec as SYSTEM paging rate can be used as a ROT in the evaluation you suggest in your reply. A value of 10 pageins/sec for all BPs is the max you can admit for BP tuning. [...] 1774 51 40_Re: create new index caused -904 in CICS16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 1 Jun 2001 10:38:06 -0400661_iso-8859-1 By any chance might the PLAN BIND have specified: REOPT(VARS) ?

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Kirk Hampton [mailto:khampto1@TXU.COM] Sent: Friday, June 01, 2001 10:03 AM To: DB2-L@RYCI.COM Subject: create new index caused -904 in CICS



We had what we feel like was an unreasonable event this morning. One of our DBA's created a new index in production with DEFER. Then he tried to run a REBUILD INDEX on it, which failed because CICS was running against the tablespace, so the status of the index was RBDP, rebuild pending. At about the same time, CICS [...] 1826 45 40_Re: create new index caused -904 in CICS14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Fri, 1 Jun 2001 10:51:32 -0400610_iso-8859-1 Kirk:

One reason I can think of is if the CICS plan was invalid for some reason and was trying to do an automatic rebind and decided to use the new index. Or are you using REOPT(VARS) ?

Manas.



========================================= We had what we feel like was an unreasonable event this morning. One of our DBA's created a new index in production with DEFER. Then he tried to run a REBUILD INDEX on it, which failed because CICS was running against the tablespace, so the status of the index was RBDP, rebuild pending. At about the same time, CICS transactions [...] 1872 51 63_Repost: IBM enhances DB2 database software - DB2 v7.2 Announced13_Morrill, John12_JohnM@VP.NET30_Fri, 1 Jun 2001 09:09:17 -0600531_- Dear DB2 Interested Parties,

On May 17, IBM announced a new version of DB2 UDB for UNIX, Windows, OS/2...Version 7.2. Below is a brief summary of the new release and a URL where you can find the details.

Regards, Mike + + + + + + +

IBM enhances DB2 database software

IBM has enhanced its DB2 database software, enabling customers to integrate data from the widest variety of sources in the industry--including Web application integration, access to critical data sources and legacy applications. [...] 1924 92 94_Repost: IBM's Janet Perna to be Inducted into Women In Technology International's Hall of Fame13_Morrill, John12_JohnM@VP.NET30_Fri, 1 Jun 2001 09:10:20 -0600421_- Dear DB2 Interested Parties,

As many of you know, Janet Perna is IBM's executive responsible for all development, marketing and sales for IBM data management solutions. Below is a significant recognition of Janet's contribution to our industry.

Congratulations to Janet!

Regards, Mike + + + + + + +

IBM's Janet Perna to be Inducted into Women In Technology International's Hall of Fame [...] 2017 13 20_Re: Archive Software12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 1 Jun 2001 09:58:22 -0500399_- If you want to archive at a data set granularity, DFSMShsm and equivalent function has neem working for a very long time.

Roger Miller, DB2 for z/OS

================================================ 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. 2031 17 48_Connecting to mainframe DB2 from other platforms13_Jim McDonnell23_jmcdonnell@PROMENIX.COM30_Fri, 1 Jun 2001 10:12:20 -0500333_- I am trying to allow access to OS/390 DB2 tables from another platform (i.e. AIX). Access will probably be through MQSeries/Integrator. I have set up ports on the mainframe for DB2/TCP access (446 and 33000). However, I think there are other steps I must take to allow access. Any suggestions on my next step? I am not a DBA. [...] 2049 32 52_Re: Connecting to mainframe DB2 from other platforms14_Philip Gunning23_pgunning@GUNNINGTSI.COM30_Fri, 1 Jun 2001 10:34:21 -0500573_us-ascii Jim, You need to configure DDF on the OS/390 side and TCPIP and Open Edtion (OMVS). Then catalog the database on the AIX side and the client. See the redbook WOW! DRDA Supports TCPIP; Db2 Server for OS/390 and DB2 Universal Database, SG24-2212-00. It is a little dtd but it is still the best reference and "how to" for this. If you are using RACF you need to create the DIST profile and associate a group with it to connect to the SSID on OS/390. Phil > >From: Jim McDonnell >Date: Fri, 1 Jun 2001 10:12:20 -0500 >To: DB2-L@RYCI.COM [...] 2082 46 52_Re: Connecting to mainframe DB2 from other platforms15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Fri, 1 Jun 2001 10:58:20 -0500446_iso-8859-1 Hi, Jim, Do you have Distributed Data Facility turned on for DB2? See DB2 UDB for OS/390 V6 (or Vwhatever) Installation Guide.

Linda Billings Enterprise Systems Programmer State of Wisconsin Department of Administration Info-Tech Services

"When you are courting a nice girl an hour seems like a second. When you sit on a red-hot cinder a second seems like an hour. That's relativity." -Albert Einstein On relativity [...] 2129 27 41_dyn prefetch after many singleton selects5_Chris17_isatcjk@YAHOO.COM30_Fri, 1 Jun 2001 09:10:47 -0700665_us-ascii Hi Everybody, Quick question...if I execute several singleton selects which access pages in a somewhat sequential manner, will a dynamic prefetch kick off?

Thanks Chris



===== http://www.geocities.com/isatcjk/index.html

__________________________________________________ Do You Yahoo!? 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. 2157 21 15_isolation level17_John Breidenstine31_JBreidenstine@LEGIS.STATE.PA.US30_Fri, 1 Jun 2001 12:22:59 -0400314_iso-8859-1 Hello all,

Does anyone know how I can query the current isolation level from the command line. I know that I can use the "CHANGE ISOLATION TO CS" commend to change it. But, is there a way to ask what it currently is?

DB2 V6.1 UDB on AIX

Thanks in advance. John L. Breidenstine [...] 2179 44 30_UDT Structures in Stored Procs13_Swamy, Ritesh30_Ritesh.Swamy@BLR.HPSGLOBAL.COM30_Fri, 1 Jun 2001 21:47:42 +0500344_- Dear Members,

I have defined the following UDT Structure in the database

CREATE TYPE GROUP_TYPE AS ( COMM_ID BININT, REGG_ID BININT, LAST_UPDATE TIMESTAMP ) MODE DB2SQL

I want to use this UDT as an array in my stored procedure and I have declared this as .... SQL TYPE IS GROUP_TYPE AS VARCHAR(16) grp_type[7]; .... [...] 2224 44 45_Re: dyn prefetch after many singleton selects13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Fri, 1 Jun 2001 09:41:39 -0700641_us-ascii YES.

Thanks. Ashish.

-----Original Message----- From: Chris [SMTP:isatcjk@YAHOO.COM] Sent: Friday, June 01, 2001 9:11 AM To: DB2-L@RYCI.COM Subject: dyn prefetch after many singleton selects

Hi Everybody, Quick question...if I execute several singleton selects which access pages in a somewhat sequential manner, will a dynamic prefetch kick off?

Thanks Chris



===== http://www.geocities.com/isatcjk/index.html

__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ [...] 2269 19 17_Backups on UDB7.110_Fred Hanes17_f_hanes@YAHOO.COM30_Fri, 1 Jun 2001 09:54:52 -0700587_us-ascii HAs any one run into problems with backing up on UDB7.1 and having names longer than 18 characters cause problem when trying to restore using the backup files????

__________________________________________________ Do You Yahoo!? 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. 2289 128 12_Re: DB2 Load17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Fri, 1 Jun 2001 10:24:05 -0700407_us-ascii Thanks Aurora and Sanjeev but we have already tried DEFAULTIF and we just couldn't get it to answer our problem. Since the two columns that are not in the input file change whenever we load new data we need a way to load new values into these new rows. We are using DB2 V5.1 and one of the reasons we wanted to use Load instead of a program type solution is so that we could turn logging off. [...] 2418 87 45_Re: regarding unload...... reply immediately,17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Fri, 1 Jun 2001 12:14:12 -0500699_- Hi Randy,

We don't have any tool to unload we are just using IKJEFT01. i wanted to unload the data with a comma delimitor. is there any way i can use delimitor. if yes how???

Thanks kumar







>From: "Bright, Randy" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: regarding unload...... reply immediately, >Date: Fri, 1 Jun 2001 09:01:03 -0500 > >If you have Unload Plus from BMC Software you can specify "FORMAT CSV" when >unloading from OS/390 to format the data exactly as in your example. > > >-----Original Message----- >From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] >Sent: [...] 2506 177 12_Re: DB2 Load0_29_sflindsey@HIGHLIGHTS-CORP.COM30_Fri, 1 Jun 2001 13:39:10 -0400530_us-ascii Will the values of COL2 and COL3 change from row to row or will they be the same for each row that is loaded? If the values are the same, then you could change the column definition to NOT NULL WITH DEFAULT and provide a default value on the column definition. If not, then you could write a program that would run before the load utility to provide the values and then run the load step. This would alleviate the logging from running a program to either load the data or update the data that was loaded by the load [...] 2684 130 45_Re: regarding unload...... reply immediately,16_Richard Muirhead19_dbaguru@HOTMAIL.COM30_Fri, 1 Jun 2001 12:39:48 -0500 2815 149 12_Re: DB2 Load16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV30_Fri, 1 Jun 2001 10:42:34 -0700368_iso-8859-1 I've had to do something similar and I used a two step process. Step one uses the standard LOAD process and step two used DSNTIAD to execute UPDATE SQL in batch to "load" the new fields with the data required. The LOAD command doesn't have, that I could find anyway, a method of inserting fixed values for fields that don't exist in the input dataset. [...] 2965 25 45_Re: regarding unload...... reply immediately,16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV30_Fri, 1 Jun 2001 10:53:23 -0700300_iso-8859-1 Use DSNTIAUL with the SQL parameter and include in your SQL the ',' between each field as a constant and concatenate the single quote to the beginning and end of each of your character defined fields also as a constant. I believe someone already showed you this but here it is again: [...] 2991 42 34_DB2 Connect and connection pooling11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Fri, 1 Jun 2001 13:57:31 -0400357_us-ascii The docs on DB2 Connect v7.1 wrt connection pooling look VERY nice. Now I have two questions.

1) With connection pooling working nicely between DB2 Connect EE to OS390 V6, do I still need my own Java connection pooling code from the Java application to DB2 Connect? We had installed this shareware code before DB2 Connect v7.1 went GA. [...] 3034 140 45_Re: regarding unload...... reply immediately,22_Donnie Woodruff (Work)30_dwoodruf@CI.NEWPORT-NEWS.VA.US30_Fri, 1 Jun 2001 14:00:52 -0400622_iso-8859-1 Kumar,

The following is an example of unloading using a delimiter:

//EXPORT EXEC PGM=IKJEFT01 //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSREC00 DD DSN=your.output.dataset,DISP=SHR //SYSPUNCH DD DSN=your.reload.statement,DISP=SHR //SYSTSPRT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DB2D) RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB51) PARMS('SQL') - LIBRARY('DB2D.RUNLIB.LOAD') END /* //SYSIN DD * SELECT NAME,SUBSTR(X'05',1,1),CREATOR FROM SYSIBM.SYSPLAN; /*

You can use any delimiter, this uses a tab key as a delimiter. You can modify the SQL statement to use a comma as a delimiter also: [...] 3175 106 19_Re: Reference guide8_Tom Sobe13_tsobe@CSC.COM30_Fri, 1 Jun 2001 13:54:23 -0400565_us-ascii Tom Janis

We are currently moving to DB2 version6

Could I get a copy of the DB2 Reference Guide

for V6. We are a major AT&T DB2 application

supporting Card Provisioning and Network



Thanks



Tom Sobe CPP DBA 100 Tower Center Blvd 12B08 East Brunswick NJ 08816 732-509-6159 tsobe@csc.com















"Thomas, Janis" @RYCI.COM> on 05/31/2001 04:07:56 PM

Please respond to DB2 Data Base Discussion List [...] 3282 189 12_Re: DB2 Load13_Larry Findley17_llfindley@USA.NET28_Fri, 1 Jun 2001 13:08:16 CDT603_US-ASCII Since you wish to avoid logging during your process you might consider using a program to process your data and place it in a new flat file, thereby creating a loadable file containing the appropriate data for all columns.

Larry



Duane Lee - ATCX wrote: I've had to do something similar and I used a two step process. Step one uses the standard LOAD process and step two used DSNTIAD to execute UPDATE SQL in batch to "load" the new fields with the data required. The LOAD command doesn't have, that I could find anyway, a method of inserting [...] 3472 67 38_Re: DB2 Connect and connection pooling14_Philip Gunning23_pgunning@GUNNINGTSI.COM30_Fri, 1 Jun 2001 13:21:08 -0500601_us-ascii >Joe, Connection pooling was implemented in Db2 Connect v6.1. Connection concentration was implemented in DB2 Connect V7.1. Connection pooling is avaiable on DB2 for OS/390 v6. So if you connect directly from web to DB2 you could still use pooling in DB2 on OS/390 by setting ZPARMS POOLINAC and IDTHOIN. With DB2 Connect V7.1 Connection concentration, DB2 Connect maps applications to a pool of agents, not a specific agent, and a connection can be reused as long as a commit or cursor is not outstanding. Leon Katsnelson gave a good presenation on this at IDUG and he can probably add [...] 3540 79 22_Re: six position token12_Kirk Hampton16_khampto1@TXU.COM30_Fri, 1 Jun 2001 14:09:27 -0500330_us-ascii Yes Larry, we have seen six-digit token numbers on threads here, and at least two products, including DB2 PM and BMC's Mainview, fail when trying to issue a -CANCEL THREAD because they do not handle the six-digit token number. I believe we had a problem open with IBM on DB2 PM, but I don't know the current status. [...] 3620 18 29_JDBC Type 3 or Type 4 drivers11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Fri, 1 Jun 2001 15:24:27 -0400495_us-ascii Could anyone recommend sites that may have JDBC type 3 or type 4 drivers for connecting to OS390 V6 DB2?

Joe Luthman 260 Hayes Hall DBA Information Technology Services 419-372-7750 Bowling Green State University Bowling Green, Ohio 43403

================================================ 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. 3639 79 12_Re: DB2 Load16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM30_Fri, 1 Jun 2001 20:47:36 +0100463_us-ascii Charles,

I don't fully understand what you mean by "the two columns ... change whenever we load new data" but I guess you'll need an application program to sort this problem out for you.

I did not realise you had no default value for these two columns, however if they are nullable you could use the NULLIF option... and then you'd be back to square one because you'd need to replace the nulls, possibly with an application program... [...] 3719 101 38_Re: DB2 Connect and connection pooling11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Fri, 1 Jun 2001 15:59:33 -0400477_us-ascii Thanks, Philip, But do I NEED the connection pooling code in order for the web app to fully save on connection overhead,,,,, or is it sufficient to have the web app simply invoke DriverManager.getConnection, and let DB2 Connect save the day? Same for the direct connect, but with different flavor. Is the mainframe connection pooling sufficient so that I don't NEED the webserver connection pooling code? I'd really like to avoid maintaining this Java code. -joe [...] 3821 24 30_load job fails with '00E40006'15_Narasim Narayan24_Narasim_Narayan@GLIC.COM30_Fri, 1 Jun 2001 16:48:05 -0400555_us-ascii The load replace job is failing with this return code...No utilities are running... Tablespace is RW status(. I cancelled the utility)

Error msg from qw Explanation: An error was detected while manipulating the utility-in-progress bits of the DBA table. The error might occur if the SYSIBM.SYSUTIL table and the DBA table are out of sync, if the utility-in-progress condition was not properly reset after execution of a prior utility, or if a resource unavailable condition exists. talks about sysutil and dba table being out of sync [...] 3846 85 40_Re: create new index caused -904 in CICS12_Kirk Hampton16_khampto1@TXU.COM30_Fri, 1 Jun 2001 14:27:25 -0500415_us-ascii Thanks Sally, IBM support seems to be saying the same thing, that all insert/update is inhibited on a table when there is a new index with rebuild pending. Too bad there is not a note about this in the manual under Create Index.









Sally Mir on 06/01/2001 09:21:21 AM

Please respond to DB2 Data Base Discussion List [...] 3932 84 23_Java Connection Pooling14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Sat, 2 Jun 2001 07:51:20 +1000704_- Joe, Two things. First, DB2 V7.1 comes with Type II and Type III JDBC drivers. Type II COM.ibm.db2.jdbc.DB2Driver, located in sqllib/java/db2java.zip. Type III driver Net or Applet, is COM.ibm.DB2.jdbc.net.DB2Driver, same location. There is no type IV yet, that is coming in V8 or Vx. Java 1.1 standard API extension supported DriverManager.getConnection(). The JDBC 2.0 Standard Extension API introduces the DataSource interface which provides an alternate means for connecting to databases. The DB2DataSource class implements this interface and a single DataSource object is a factory for connections to a specific DB2 database. To obtain a connection, you call getConnection()method similar to [...] 4017 46 33_Re: JDBC Type 3 or Type 4 drivers13_Tzachi Nissim21_tzachi@ATTUNITY.CO.IL30_Sat, 2 Jun 2001 01:06:45 +0200569_iso-8859-1 Well, I can recommend our product - Attunity Connect has a JDBC type 3 driver to many databases, including DB2 on os390 (also IMS, VSAM and ADABAS on os390). We have a wide matrix of platforms, databases and standard APIs, os390-DB2-JDBC being one square in this 3D matrix.

Having said all that, I'm curious - most people seem content with IBM's JDBC driver (we usually get approached when more than one database is involved because of our heterogeneous ability). So, if I may ask, what is getting you to look around instead of using IBM's JDBC? [...] 4064 180 34_Re: load job fails with '00E40006'14_Crichton, Jean21_jcrichton@FEDDATA.COM30_Fri, 1 Jun 2001 18:23:18 -0400517_iso-8859-1 The system programmer diagnostic info from the "Messages and Codes" manual states that this error usually has an associated DSNT500I message flagging an unavailable resource. If it doesn't, the value in register 2 has secondary information that may help. I am assuming you have checked to see if there are any unavailable resources flagged with a DSNT500I, and you have done the recommended -DISPLAY DATABASE command and found that the tablespace in RW mode. Here's what I would do in your situation: [...] 4245 20 46_How to store Japanese Characters in DB2 OS/39010_King C. Au15_King_Au@GAP.COM30_Fri, 1 Jun 2001 15:45:06 -0700573_us-ascii We have a requirement from our application team to store Japanese characters in a DB2 (OS/390) table. The other columns in the same table are either English characters or integers. According to IBM, currently there is no multiple languages support in DB2 for OS/390 in a single DB2 subsystem. I just want to see if there are any shops who have similar requirement and how they handle it. Our application team indicates that if DB2 cannot support this requirement then they might have to store this Japanese information in the other non IBM DBMS. Thanks. King [...] 4266 19 33_Re: JDBC Type 3 or Type 4 drivers11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Fri, 1 Jun 2001 19:23:25 -0500319_- Thanks for the respose. Our need for type 4 is driven by a desire to avoid using the DB2 run-time client software. We're looking for the absolute easiest way to distribute some application code to many servers. A type 4, or possibly type 3, would avoid some installation and configuration steps, or so I'm told. [...] 4286 116 27_Re: Unexpected SQLCODE -80512_rl_cotterill24_rl_cotterill@BIGPOND.COM30_Sat, 2 Jun 2001 10:37:37 +1000529_us-ascii Eric, By chance DBP1 and DBP2 are not different releases of DB2 (ie V6 and V5) if so there is a bug with the SET command (fix is available).

Let me know if - there is a work around.

Richard

"Pearson, Eric L," wrote:

> I would appreciate any insight into the following problem. I have had an ETR > open with IBM on this > for about four months and it seems all IBM can do is quote irrelevant > passages from Messages and Codes > and cannot read the text of the ETR. > > The situation is: [...] 4403 24 34_Requirements for table with clob ?14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Fri, 1 Jun 2001 20:55:06 -0400375_iso-8859-1 Hi List,

I need to define a table with a CLOB column - size 10 MB. Right now, I have 2 tablespaces for data - one 4K and one 32K, with corresponding index table spaces and buffer pools.

Is it recommended to create a separate TS for the clob table ? What about a separate bufferpool ? Any Do's and Dont's in this matter will be much appreciated. [...] 4428 31 21_Re: Backups on UDB7.111_Endy Lambey20_endyl@CENTRIN.NET.ID30_Sat, 2 Jun 2001 08:05:18 +0700657_- Fred,

Can you specify more about your backup problems ?

>HAs any one run into problems with backing up on >UDB7.1 and having names longer than 18 characters >cause problem when trying to restore using the backup files???? > >__________________________________________________ >Do You Yahoo!? >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. > [...] 4460 54 50_Re: How to store Japanese Characters in DB2 OS/39015_Toine Michielse18_vndobtm@US.IBM.COM30_Sat, 2 Jun 2001 01:34:16 +0200478_us-ascii Hello King,

Take a look at DB2 V7. It supports UNICODE for user databases. This should solve your problem.

Regards,

Toine Michielse



"King C. Au" @RYCI.COM> on 06/02/2001 12:45:06 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: How to store Japanese Characters in DB2 OS/390 [...] 4515 75 50_Re: How to store Japanese Characters in DB2 OS/39014_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sat, 2 Jun 2001 04:07:23 -0500398_- King,

The way I would approach this is to ask - "what is actually stored in a data base?" At its lowest level, it is just a string of bits. That is, you don't store English characters (or rather, as I suspect you mean, Latin characters), or Japanese characters (whether katakana or kanji - or even hiragana) - but a bit string which you _choose_ to interpret in a particular fashion. [...] 4591 55 0_16_Jean-Paul Coenen19_jpc_db2@HOTMAIL.COM30_Sat, 2 Jun 2001 13:53:20 +0200405_- Dear list,

DB2 V6 on OS/390 (recently upgraded from V5).

We don't see why a CICS transaction is doing more than 28,800,000 getpages (elapsed time +4 hours). Ok, I'm amazed, as you are, that this is permitted here :-)

Monitoring reveals following detail, before the trx is cancelled: 10263 opens, 10263 fetches, 10262 closes, 5130 updates, 5130 inserts Two tables are involved. [...] 4647 102 12_Re: DB2 Load12_Isaac Yassin20_yassini@BEZEQINT.NET30_Sat, 2 Jun 2001 19:14:43 +0200389_iso-8859-1 Hi, Can you "fix" the flat file used for loading so that it'll contain the contentes you need for those two columns ?

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- From: "Aurora Dell'Anno" Newsgroups: bit.listserv.db2-l To: Sent: Friday, June 01, 2001 9:47 PM Subject: Re: DB2 Load [...] 4750 44 21_utf-8 problem in java12_Jason Pyeron16_jason@PYERON.COM30_Sat, 2 Jun 2001 12:20:17 -0400449_US-ASCII DB2 v71 on linux sun java 2 v1.3 COM.ibm.db2.jdbc.app.DB2Driver col type vargraphic

i cannot store / retrive high unicode it is garbled as '?'

does any one have suggestions on where to start on this problem?

code fragment:

con = DriverManager.getConnection(url, userid, passwd);

stmt = con.createStatement(); int rowsUpdated = stmt.executeUpdate("UPDATE test set utf8data=N'\0e1a' where id = '1' "); [...] 4795 60 34_Re: UDB v7.2 vs. UDB 7.1 Fixpack 313_Umair Hussain25_umair_hussain@HOTMAIL.COM30_Sat, 2 Jun 2001 11:33:52 -0500356_- we have installed v7.1 with fp3 which in essence makes it 7.2. But there is a little problem.... ie when we do db2level it show 7.1.42 (something like that) but when we connect to the database it show connected to 7.2 database. Hopefully supportability wise it should not make a difference. If you have time, get a GA 7.2 CD and install from there. [...] 4856 34 50_Re: How to store Japanese Characters in DB2 OS/39012_Roger Miller19_millerrl@US.IBM.COM30_Sat, 2 Jun 2001 14:11:42 -0500364_- We have been serving Japanese characters to our Japanese customers since V1R2 in 1986, including various sort sequences using FIELDPROCs. Almost every release has had some changes for variations in handling of the various encodings and techniques. The complexities of mixed graphic or DBCS strings cascade through tens of thousands of lines of code in DB2. [...] 4891 17 47_DSNU361I - PROBLEM WITH FOREIGN KEY DURING LOAD13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Sun, 3 Jun 2001 09:57:43 -0400 4909 117 22_Re: six position token0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM30_Sun, 3 Jun 2001 10:29:00 -0500335_us-ascii When we upgraded to DB2 V6 there was one sentence on pg 232 of the V6 Release guide in Chp 8 for Planning for Migration & Fallback that states:

To support up to 150000 connections, the token for displaying LUWIDs for DISPLAY THREAD is now a 6-digit decimal number.

HTH

Shan Leatherman DBA Specialist [...] 5027 190 51_Re: DSNU361I - PROBLEM WITH FOREIGN KEY DURING LOAD12_Isaac Yassin20_yassini@BEZEQINT.NET30_Sun, 3 Jun 2001 18:54:19 +0200523_Windows-1252 Hi, If you have "enforce constraint" (default) in your input the offending row is deleted. You can do one of the following 1. Use "enforce no" - this will place the TS in "check pending" which can be fixed either by running the check utility or "start acc(force)" or "repair nochekpend". The point as I understand it is that you ant to do some later processing to fix the problem - so it lloks like "repair nochekpend" is preferable. 2. Load the "father" first - this does not fit your way of processing. [...] 5218 190 51_Re: DSNU361I - PROBLEM WITH FOREIGN KEY DURING LOAD12_Isaac Yassin20_yassini@BEZEQINT.NET30_Sun, 3 Jun 2001 18:54:19 +0200523_Windows-1252 Hi, If you have "enforce constraint" (default) in your input the offending row is deleted. You can do one of the following 1. Use "enforce no" - this will place the TS in "check pending" which can be fixed either by running the check utility or "start acc(force)" or "repair nochekpend". The point as I understand it is that you ant to do some later processing to fix the problem - so it lloks like "repair nochekpend" is preferable. 2. Load the "father" first - this does not fit your way of processing. [...] 5409 93 51_Re: DSNU361I - PROBLEM WITH FOREIGN KEY DURING LOAD15_Toine Michielse18_vndobtm@US.IBM.COM30_Sun, 3 Jun 2001 18:26:30 +0200481_iso-8859-1 Hello Carlton,

The answer lies in the explanation of the message.... 'The constraint violation will be rectified..'. I assume you understand what is happening. While loading a dependent table, DB2 finds that a row to be loaded does not have a row in the parent table that will be the parent row based on the values in the foreign key definition. Rectification is making sure that the row is removed from the table (potentially written to a discard dataset). [...] 5503 27 24_Synchronous I/O question10_Radha Rani24_radha_rani@ALTAVISTA.COM30_Sun, 3 Jun 2001 22:25:54 -0700408_- Hi !

I was looking at performance metrices of a bufferpool through Platinum Detector at our shop. For a certain buffer pool, the following two parameters shown by the Detector confused me:

Synchronous I/O requests : 14 Synchronous I/Os performed : 3375

What is the difference between the two and why is the difference in the no. of I/O requests and actual I/Os performed so high? [...] 5531 16 53_Pete Sexton/Rolling Meadows/IBM is out of the office.11_Pete Sexton18_psexton@US.IBM.COM30_Mon, 4 Jun 2001 01:37:31 -0500456_us-ascii I will be out of the office starting June 4, 2001 and will not return until June 5, 2001.

I will respond to your message when I return. ** at customer location with limited access to lotus notes. **

================================================ 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. 5548 14 34_Re: load job fails with '00E40006'10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 4 Jun 2001 09:13:37 +0200322_us-ascii Did you DROP/CREATE the tablespace you were loading ?

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. 5563 23 3_RID22_SERDAR SABRI OZKUBULAY22_sozkubul@AKBANK.COM.TR30_Mon, 4 Jun 2001 11:35:37 +0300426_iso-8859-1 Hello list,

Can you say the format of RID?

RID OF NON-INDEXED ROW = X'000034250D'

Which portion is page number? Thanks.

Serdar S. OZKUBULAY AKNET

================================================ 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. 5587 63 28_Re: Synchronous I/O question14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Mon, 4 Jun 2001 04:12:05 -0500522_iso-8859-1 You can get a Sync I-O in a number of situations...

1) A sync I-O request is issued. e.g. DB2 has determined it needs a specific row to satisfy a query.

2) As a 'primer' for seq prefetch. DB2 issues a sync I-O to get the first row when it invokes seq prefetch. A sync I-O is quick, but a prefetch request is slow but returns more rows. This behaviour allows the application to process something while the prefetch is processing. Bonnie Baker has wonderful examples of the benefits of this... [...] 5651 118 12_Re: DB2 Load11_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Mon, 4 Jun 2001 07:09:06 -0500438_iso-8859-1 How about having a rexx that generates the load control cards and includes the values you want for col2 & col3 as literals?

Tina

-----Original Message----- From: Isaac Yassin [mailto:yassini@BEZEQINT.NET] Sent: June 02, 2001 12:15 PM To: DB2-L@RYCI.COM Subject: Re: DB2 Load



Hi, Can you "fix" the flat file used for loading so that it'll contain the contentes you need for those two columns ? [...] 5770 36 7_Re: RID11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Mon, 4 Jun 2001 09:20:56 -0400412_ISO-8859-1 Serdar

Ignore the last byte and the rest is the page number. In your example the page number is X'00003425'. the last byte, X'0D', is the ID-Map entry number within the data page.

Regards Mike Turner Email: michael_turner@compuserve.com Web: www.michael-turner.ltd.uk

Message text written by INTERNET:sozkubul@akbank.com.tr >Hello list,

Can you say the format of RID? [...] 5807 27 42_Star Join & star schemas in DB2 OS/390 V6.13_Michalik, Ken19_kmichalik@KRAFT.COM30_Mon, 4 Jun 2001 08:28:55 -0500368_- We have recently migrated to release 6 of DB2 OS/390 and the star join feature is disabled.

Should the star join feature be enabled if we design star schemas? What are the performance gains, and is there a downside? If the star join feature is disabled does the star schema still hold a performance advantage over normalized design for specific queries? [...] 5835 148 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 4 Jun 2001 09:23:36 -0400553_iso-8859-1 Richard, Thanks but this is not it. Our prod subsys are all V5 and the problem happens there as well as in our 'mixed' QA subsystems.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: rl_cotterill [mailto:rl_cotterill@BIGPOND.COM] Sent: Friday, June 01, 2001 8:38 PM To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805



Eric, By chance DBP1 and DBP2 are not different releases of DB2 (ie V6 and V5) if so there is a bug with the SET command (fix is available). [...] 5984 36 30_sqlri_tbl_in_mem_alloc problem18_MCCARTHY, DANIEL M27_DANIEL_M_MCCARTHY@FLEET.COM30_Mon, 4 Jun 2001 09:27:44 -0400377_iso-8859-1 I have a developer's environment that supports 4 databases, using a UDB 6.1 instance with Fixpack 7. These 4 databases isolate testing by the application's major functions.

Last week after the developers installed a new code release, the UDB db2diag.log started growing rapidly recording a single message over and over. One example of the message is ... [...] 6021 39 9_deadlocks14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM30_Mon, 4 Jun 2001 08:50:46 -0500621_iso-8859-1 I getting the following deadlock error on two batch applications. I have row locking on the tablespace that I'm getting the deadlock on and I've modified the commit frequecies to 1 on both applications. So, I should not be holding any resources. I'm on DB2 V5. Any suggestions?

DSNT376I ˘ PLAN=FD850 WITH CORRELATION-ID=0006FD850 CONNECTION-ID=IMSP LUW-ID=USTTXA01.DB2PDDF.B5EE5527DF09=2642 THREAD-INFO=@MARC:*:*:* IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=RC130 WITH CORRELATION-ID=0024RC130 CONNECTION-ID=IMSP LUW-ID=USTTXA01.DB2PDDF.B5EE18143028=20 THREAD-INFO=@MARC:*:*:* ON MEMBER DB2P [...] 6061 151 27_Re: Unexpected SQLCODE -80514_Adrian Collett27_adriancollett@TISCALINET.IT30_Mon, 4 Jun 2001 16:12:59 +0200595_iso-8859-1 Eric,

Maybe I'm missing something, or haven't woken up fully yet(it is Monday...), but as far as I can see things are working as they should:

1) Your program is searching for KT659 in collid DCMITMS. 2) Why is it looking in that particular collection ? Because that is the value of the special register CURRENT PACKAGESET. 3) Who sets that value ? Your programs via the SET CURRENT PACKAGESET ... 4) Why does it happen intermittently ? I presume the fact that it works intermittently is that either CURRENT PACKAGESET has been set correctly to DCTCOAL or it hasn't [...] 6213 62 45_Re: regarding unload...... reply immediately,15_Dominic Papania31_dominic.papania@BWC.STATE.OH.US30_Mon, 4 Jun 2001 08:51:48 -0500552_- On Fri, 1 Jun 2001 08:31:41 -0500, ravi kumar hassan wrote:

>Hi Folks, > >We are using DB2 V6 on OS390, someone requested the data to be unloaded from >prod to a dataset and we have to transfer to PC file. they will use this >text file from PC for testing in UDB. each data field is seperated by >comma(,). and also they want all char datafield by quotes like >'BENGAMENNNNN'. i am just confused how to unlload the data seperated by >comma and char data in quotes... is there anyway i can do that... or is >there any [...] 6276 69 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 4 Jun 2001 10:22:47 -0400576_iso-8859-1 The way I read the manuals: 1) DB2 extracts the consistency token for the package from the load module. 2) If CURRENT PACKAGESET is set, DB2 looks only in the collection corresponding to that special register for a match on name and contoken. 3) If CURRENT PACKAGESET is blank, DB2 searches each item in the PKLIST for a match on (load-module-name = package-name) and (load-module-contoken = package-contoken). 4) When match is found for 2) or 3), execute the load module/package. 4) If no match for 2) or 3) (depending upon CURRENT PACKAGESET), SQLCODE -805. [...] 6346 28 16_MAXASSIZE & OMVS11_Emrah GUVEN28_Emrah.GUVEN@PAMUKBANK.COM.TR30_Mon, 4 Jun 2001 17:33:46 +0300360_iso-8859-1 hi colleagues ,

i would like to get your comments about the parameter MAXASSIZE parameter which is used in OpenMVS ,

what's the way of calculating for this parameter , we could not find a suggested way for calculation in the IBM documentation.

Just a default , bottom and highest values are mentioned in the documentation. [...] 6375 28 34_Re: load job fails with '00E40006'15_Narasim Narayan24_Narasim_Narayan@GLIC.COM30_Mon, 4 Jun 2001 10:36:21 -0400452_us-ascii Thank you

To recap what was done... 1. checked for any utilities running against the TS 2. Terminated utilities(if any) 3. TS in RW 4. Started Access(force) after 00E4006 5. Ran the Load job again and encountered the same problem 6.. This is another problem -2 other tablespaces have the same problem in the same database!! 7. The sledge hammer approach- drop tablespace and other objects and recreate them- has not been attempted [...] 6404 113 45_Re: regarding unload...... reply immediately,11_Bikash Paul19_bikash_db@YAHOO.COM30_Mon, 4 Jun 2001 07:46:59 -0700317_us-ascii Hi Dominic, I never tried but i think it will work and becomes simple as well.

1) Use DB2 connect to connect to DB2 oN OS/390 2) Use export to get the data from a table on DB2 on OS/390. 3) Connect to UDB on NT, windowns (On PC) 4) Use import to load the table from the file exported in option 2 [...] 6518 129 13_Re: deadlocks14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM30_Mon, 4 Jun 2001 09:47:26 -0500809_iso-8859-1 Here are the plans. The packages also are the same and have isolation(ur) BIND PLAN(FD850) OWNER(@MAD2 ) QUALIFIER(DB2PROD ) - PKLIST(FD850.*,FD101.*,FD86002.*,FD86003.*,FD885.*, - FD886.*, - FD451.*, - FD222.*, - FD223.*, - FD456.*, - FD870.*, - FD860.*) - VALIDATE(BIND) EXPLAIN(NO ) - ISOLATION(UR) ACQUIRE(USE ) RELEASE(COMMIT ) - CURRENTDATA(NO ) CACHESIZE(1024) - DEGREE(ANY) SQLRULES(DB2) DISCONNECT(EXPLICIT )- NOREOPT(VARS) - KEEPDYNAMIC(NO ) - ACTION(REPLACE) RETAIN - FLAG(I); BIND PLAN(RC130) OWNER(@MAD2 ) QUALIFIER(DB2PROD ) - PKLIST(RC130.*, - RS101.*, - FD101.*) - VALIDATE(BIND) EXPLAIN(NO ) - ISOLATION(CS) ACQUIRE(USE ) RELEASE(COMMIT ) - CURRENTDATA(NO ) CACHESIZE(1024) - DEGREE(ANY) SQLRULES(DB2) DISCONNECT(EXPLICIT )- NOREOPT(VARS) - KEEPDYNAMIC(NO ) - ACTION(REPLACE) [...] 6648 28 34_Re: load job fails with '00E40006'10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 4 Jun 2001 16:55:09 +0200337_us-ascii Hi...

What I mean is not to DROP/CREATE tablespaces now, after you encountered the error '00E40006'.

I asked if they were DROPPed/CREATEd before you tried to execute the load who ended in error.

It happened to me last year, where was a previous utility stopped (and no -TERM command was issued) when I [...] 6677 15 19_EDM Pool DATA SPACE12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Mon, 4 Jun 2001 10:55:52 -0400304_us-ascii We recently migrated 1 of our DB2 subsystems to V6. We have cachedyn=yes and noticed that EDMDSPAC was greater than 0 which turned on the use of dataspaces for the edmpool. Our question is there anyone else using this and what are the pros and cons? We are a non Z-SERIES machine. TIA Jeff [...] 6693 63 27_Re: Unexpected SQLCODE -80514_Adrian Collett27_adriancollett@TISCALINET.IT30_Mon, 4 Jun 2001 17:16:45 +0200517_iso-8859-1 Eric,

Are you using dynamic plan selection in CICS ? Is the program being linked to from another plan who IS setting current packageset ?

What is the -805 message when your programmers issue an explicit set...= '' ? Does that still specify a COLLID ?

Adrian.





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM] On Behalf Of Pearson, Eric L, Sent: 04 June 2001 16:23 To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805 [...] 6757 154 13_Re: deadlocks12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Mon, 4 Jun 2001 10:27:00 -0500365_iso-8859-1 Plan RC130 is bound with isolation CS. This causes locking on read-only SQL. The locks are held until commit.

The programmer must be instructed to commit even if the program has only been reading.

Note: the REASON 00C9008E indicates a timeout, not a deadlock.

Best regards, John P. Higgins Performance Specialist SAP BASIS Team [...] 6912 87 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 4 Jun 2001 11:27:57 -0400500_iso-8859-1 Adrian, We use dynamic plan selection (shudder!!) but not in the plans which are having a problem. The -805 is identical (04) with or without the SET. The set is: SET CURRENT PACKAGESET = ' '; (single blank between single quotes).

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Adrian Collett [mailto:adriancollett@TISCALINET.IT] Sent: Monday, June 04, 2001 11:17 AM To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805 [...] 7000 12 0_12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Mon, 4 Jun 2001 11:32:58 -0400328_us-ascii Hello, Anyone out there using edmpool dataspaces? What are the pros and cons?

================================================ 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. 7013 55 34_Re: load job fails with '00E40006'15_Narasim Narayan24_Narasim_Narayan@GLIC.COM30_Mon, 4 Jun 2001 11:09:06 -0400540_us-ascii Max:

Thank you again.. No the tablespace was not dropped.-no attempt was made to drop it













Max Scarpa on 06/04/2001 10:55:09 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Narasim Narayan/TheGuardian)

Subject: Re: load job fails with '00E40006'





Hi...

What I mean is not to DROP/CREATE tablespaces now, after you encountered the error '00E40006'. [...] 7069 73 17_False Contentions11_Ruth Sutlic24_ruth.sutlic@DILLARDS.COM30_Mon, 4 Jun 2001 10:15:06 -0500383_- We are a DB2 V5 production data sharing environment on OS/390 2.9. We use BMC's MainView product for monitoring. My observations for a week show that see false contentions around 5% during the day and at night we can be as high as 15%. According to the data sharing manual on Performance and Monitoring and tuning, this value needs to be less than 5% preferably less than 1%. [...] 7143 123 27_Re: Unexpected SQLCODE -8059_Colin Fay13_cfay2@CSC.COM30_Mon, 4 Jun 2001 11:51:28 -0400463_us-ascii Eric,

Have you tried GRANTing EXECUTE on the package and collection id to PUBLIC just to be sure that the intermittent failure is not due to a user trying to execute the package not being authorized.

What are the autorizations on this package compared to other plan/packages that work? Any difference?

Colin









"Pearson, Eric L," @RYCI.COM> on 06/04/2001 11:27:57 AM [...] 7267 101 13_Re: deadlocks12_Susan Lawson22_lawson_susan@YAHOO.COM30_Mon, 4 Jun 2001 08:52:35 -0700333_us-ascii Hi Al,

This is actually a timeout situation, not a deadlock. This is noted in the reason code of 00C9008E for the unavailable resource (the RID (type 304) that is noted below also). Plan FD580 is timing out while waiting for the row. You may want to look at how long RC130 is holding the lock before the commit. [...] 7369 146 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Mon, 4 Jun 2001 11:56:18 -0400605_iso-8859-1 Colin, This is a CICS trans. We grant PUBLIC EXECUTE to all CICS plans and packages and control access to the transactions via RACF.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Colin Fay [mailto:cfay2@CSC.COM] Sent: Monday, June 04, 2001 11:51 AM To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805



Eric,

Have you tried GRANTing EXECUTE on the package and collection id to PUBLIC just to be sure that the intermittent failure is not due to a user trying to execute the package not being authorized. [...] 7516 17 27_Re: Unexpected SQLCODE -80513_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Mon, 4 Jun 2001 12:08:48 -0400432_iso-8859-1 I don't believe you'll get -805 for authorization problems. That's generally -922.

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

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 7534 40 35_Problem with sqlri_tbl_in_mem_alloc18_Mccarthy, Daniel M27_DANIEL_M_MCCARTHY@FLEET.COM30_Mon, 4 Jun 2001 12:03:44 -0400479_iso-8859-1 I'm reposting this msg since Listserv notified me of errors with the original posting.



I have a developer's environment that supports 4 databases, using a UDB 6.1 instance with Fixpack 7. These 4 databases isolate testing by the application's major functions.

Last week after the developers installed a new code release, the UDB db2diag.log started growing rapidly recording a single message over and over. One example of the message is ... [...] 7575 147 12_Re: DB2 Load12_Isaac Yassin20_yassini@BEZEQINT.NET30_Mon, 4 Jun 2001 19:07:11 +0200395_iso-8859-1 Hi, If you're using IBM load you can't have literals in the load command. If I renmember correctly it is available only with 3rd party load. Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- From: "Tina Hilton" Newsgroups: bit.listserv.db2-l To: Sent: Monday, June 04, 2001 2:09 PM Subject: Re: DB2 Load [...] 7723 93 52_need help with Business Objects timeout to DB2 OS39020_Bustamente, Gerald G30_Gerald.Bustamente@LANDSEND.COM30_Mon, 4 Jun 2001 11:14:45 -0500498_iso-8859-1 If anyone has experience or ideas about this and the time to respond I'd really appreciate it.



DESCRIPTION:

Customer trying to execute Business Object query from desktop to access DB2 OS390 data.



Error from GUI display:

SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "nn.nnnn.nnn.nnn". Communication function [...] 7817 97 27_Re: Unexpected SQLCODE -80517_Linda F. Claussen18_lindafc@NETINS.NET30_Mon, 4 Jun 2001 11:28:19 -0500445_iso-8859-1 Eric,

A couple of things to look into.

Are the threads reusable? If they are and the CICS application does not issue an explicit SYNCPOINT to force a commit you may be experiencing a hold over from the previous transaction. I noticed that you had bound with RELEASE(COMMIT) so the explicit SYNC should guarantee freeing all resources (including link to package). Not sure if this will help, but may be worth a try. [...] 7915 47 47_Help! Not getting a Stored Procedure Result Set3_Joe29_joey_advincula@MAINFRAMER.NET30_Mon, 4 Jun 2001 11:13:39 -0500425_- Coded a COBOL DB2 Stored Procedure but i'm not getting a Result Set

Defined the Stored Procedure as

CREATE PROCEDURE DB2XXXXX.XXXSP001 ( IN VARCHAR(1024) ,OUT VARCHAR(32704) ,OUT INTEGER ,OUT VARCHAR(1024) ) LANGUAGE COBOL EXTERNAL NAME XXXSP001 COLLID DB2TMED0 NO WLM ENVIRONMENT PARAMETER STYLE GENERAL ASUTIME NO LIMIT STAY RESIDENT NO PROGRAM TYPE MAIN SECURITY DB2 RESULT SET 1 COMMIT ON RETURN NO; [...] 7963 112 27_Re: Unexpected SQLCODE -80514_Adrian Collett27_adriancollett@TISCALINET.IT30_Mon, 4 Jun 2001 18:40:00 +0200387_iso-8859-1 Eric,

If its not DPS then that is weird - I would have expected the COLLID in the message to change.

Let me sleep on it !

Adrian





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM] On Behalf Of Pearson, Eric L, Sent: 04 June 2001 17:28 To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805 [...] 8076 20 12_Re: DB2 Load17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Mon, 4 Jun 2001 09:35:16 -0700412_us-ascii Thanks to all who have replied and given suggestions. One of the application programmers thought he had sometime previously used the Load utility to insert specific values into fields not in the input file and since I'm a sysprog who has only recently added dba responsiblities I had to make certain if this was true since I couldn't see how via the manuals. Once again thanks for the suggestions. [...] 8097 120 13_Re: deadlocks12_Troy Coleman19_Colematr@MEIJER.COM30_Mon, 4 Jun 2001 12:31:52 -0400407_US-ASCII Hi Al, The lock is on a RID. This tells me that both applications are going = after the same row. =20

Does the application open a cursor and read some data then do some = processing before making a decision to update the row? It could be that RC130 read the row and RD850 is trying to update it. If = you have cursor with hold I would think the last row would have a read = lock on it. [...] 8218 36 24_winsock2.dll and db2 7.117_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM30_Mon, 4 Jun 2001 11:47:37 -0500551_us-ascii Hello list,

We are currently trying to rollout the new version of db2 udb runtime client (with the latest fix pack) to all of our users. We plan on doing this via a response file. We have created our response file and the command package to run it. When we tested it in our lab environment 1 out of 3 of the PCs started getting DHCP errors following the install. After reviewing the log we determined that DB2 was upgrading the winsock.dll to winsock2.dll on most of the PCs. 2 of them started getting the DHCP errors. All of the [...] 8255 122 13_Re: deadlocks12_Troy Coleman19_Colematr@MEIJER.COM30_Mon, 4 Jun 2001 12:31:52 -0400407_us-ascii Hi Al, The lock is on a RID. This tells me that both applications are going = after the same row. =20

Does the application open a cursor and read some data then do some = processing before making a decision to update the row? It could be that RC130 read the row and RD850 is trying to update it. If = you have cursor with hold I would think the last row would have a read = lock on it. [...] 8378 39 0_17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM30_Mon, 4 Jun 2001 12:12:25 -0500552_us-ascii I don't know why this did not work the first time, but I will try this again, sorry for the duplicate



Hello list,

We are currently trying to rollout the new version of db2 udb runtime client (with the latest fix pack) to all of our users. We plan on doing this via a response file. We have created our response file and the command package to run it. When we tested it in our lab environment 1 out of 3 of the PCs started getting DHCP errors following the install. After reviewing the log we determined that DB2 was [...] 8418 116 56_Re: need help with Business Objects timeout to DB2 OS3909_Wu, James13_jwu@KRAFT.COM30_Mon, 4 Jun 2001 12:37:49 -0500352_iso-8859-1 Your SQL 'connection' doesn't point to the right DB2 location. If you use 'freehand SQL', check you 'connection' parm in your BO document; if you use BO Universe, check 'parameter--connection' in the universe. If this is your first time talking to DB2 from Business Object, you also have to check the ODBC DB2 driver and DB2 DDF setup. [...] 8535 50 23_Re: EDM Pool DATA SPACE23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Mon, 4 Jun 2001 18:32:15 +0100316_- Jeff we use it and it works really well.

It free's up valuable central storage by placing the Global Dynamic Cache in a Dataspace ..

We use the central storage elsewhere where we get better bang for the buck ...

Are you a dynamic shop? or a mix of both ... sap/peoplesoft/siebel .. etc?? [...] 8586 78 23_Re: EDM Pool DATA SPACE12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Mon, 4 Jun 2001 13:57:30 -0400431_us-ascii We are Peoplesoft and a mix of other applications. I think i should reduce the edmpool size by the amount the edmdspac is equal to. Is that what u did?







"Pendlebury-Bowe, Leslie" @RYCI.COM> on 06/04/2001 01:32:15 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 8665 101 23_Re: EDM Pool DATA SPACE23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Mon, 4 Jun 2001 19:08:56 +0100309_- yes ... however you may be able to give (like we did) the edm dataspace (edmspac in zparm) more space .. which will give you more cached global statements ..

monitor via pm and see what hit ratio you get ... important to know before and after the effect of changing the value of your edmspac .. [...] 8767 16 61_Barbara J. Nigh/Household International is out of the office.0_20_bjnigh@HOUSEHOLD.COM30_Mon, 4 Jun 2001 11:19:01 -0700447_us-ascii I will be out of the office starting 06/04/2001 and will not return until 06/07/2001.

I will respond to your message when I return as I will be unable to access Lotus Notes while I am gone.

================================================ 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. 8784 42 34_Re: load job fails with '00E40006'17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Mon, 4 Jun 2001 15:34:28 -0300240_us-ascii Narasim,

Issue the following command to make sure you cleaned up everything:

-DIS DATABASE(*) SPACE(*) LIMIT(*) RESTRICT

You should have some indexes with utility bits turned on...

HTH, Walter Trovijo Jr 8827 34 14_SQL Procedures19_Donovan, Peggy [IT]22_peggy.donovan@SSMB.COM30_Mon, 4 Jun 2001 14:45:18 -0400586_- I'm trying to teach myself SQL Procedures. As a start I've converted a simple COBOL procedures & then a more complex one. Can't get the second to work. Is there a restriction against passing a RETURN SET from an SQL Procedure back to a calling COBOL program?

Got a return code -482 with state 57011. Did find a box in the latest redbook - Cross-Platform DB2 Stored Procedures:Building and Debugging that ASSOCIATE and ALLOCATE are not working in the SQL language. Does this mean that it won't work passing it back to COBOL which uses these statements? Also following note [...] 8862 46 34_Re: load job fails with '00E40006'17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Mon, 4 Jun 2001 15:34:28 -0300240_us-ascii Narasim,

Issue the following command to make sure you cleaned up everything:

-DIS DATABASE(*) SPACE(*) LIMIT(*) RESTRICT

You should have some indexes with utility bits turned on...

HTH, Walter Trovijo Jr 8909 76 46_Re: Star Join & star schemas in DB2 OS/390 V6.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 4 Jun 2001 14:19:44 -0500655_iso-8859-1 Ken,

Once you've applied APAR PQ43846 (May 2nd 2001), then I would enable star join. The star join access path has been designed specifically for star schemas, and there are a number of optimization improvements that go along with this that are not available for the other access paths.

The initial implementation of Star Join negatively impacted non-star schema designs if the query qualified against the 9 rules listed in the V6 Technical Update Redbook, but there was not sufficient filtering predicates on the dimension tables. The recent APAR has addressed this by introducing heuristic rules for join order permutation [...] 8986 81 18_Re: SQL Procedures0_19_Tim.Lowe@STPAUL.COM30_Mon, 4 Jun 2001 14:19:56 -0500385_us-ascii Peggy, Regarding SQL procedures using associate and allocate, have you seen PQ45804/UQ51506 and PQ45828/UQ51507?

Regarding returning result sets in SQL Procedures, I have built a couple that do this on OS/390 using Stored Procedure Builder.

I am not familiar with the Stored Procedures manual that you refer to.

I hope this helps.

Thanks, Tim [...] 9068 56 56_V6 OS390 static / access path changes during execution ?16_Jean-Paul Coenen19_jpc_db2@HOTMAIL.COM30_Mon, 4 Jun 2001 21:46:56 +0200463_- Forgot a subject, so this is a second try (sorry).

Dear list,

DB2 V6 on OS/390 (recently upgraded from V5).

We don't see why a CICS transaction is doing more than 28,800,000 getpages (elapsed time +4 hours). Ok, I'm amazed, as you are, that this is permitted here :-)

Monitoring reveals following detail, before the trx is cancelled: 10263 opens, 10263 fetches, 10262 closes, 5130 updates, 5130 inserts Two tables are involved. [...] 9125 125 46_Re: Star Join & star schemas in DB2 OS/390 V6.14_Campbell White20_cwhite@REVEALNET.COM30_Mon, 4 Jun 2001 16:12:57 -0400364_us-ascii A paper entitled "Implementing Star Joins" will be the feature DB2 article in the June issue of the Pipeline Newsletter, due for release on Monday, June 11.

You can also learn more about Star Joins by downloading the 30-day trial of the Knowledge Base for DB2 from http://www.revealnet.com

Best wishes,

Cam White RevealNet, Inc. [...] 9251 19 51_Re: Help! Not getting a Stored Procedure Result Set0_19_Tim.Lowe@STPAUL.COM30_Mon, 4 Jun 2001 15:20:31 -0500408_us-ascii Joe, Perhaps you already check this, but could the open cursor have failed? Did you check the sqlcode afterward?

I hope this helps.

Thanks, Tim

================================================ 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. 9271 22 34_Re: load job fails with '00E40006'15_Narasim Narayan24_Narasim_Narayan@GLIC.COM30_Mon, 4 Jun 2001 16:21:35 -0400351_us-ascii Walter, Kiren, Max, Jean thank you for your time: Walter Thank you .. It was an index( the 9th index on the table) that was in UTUT status I checked tablespace status and since I was doing a load replace the status of indexes- we are new to v6- I did'nt check (Having just moved from v5 to v6 that you can recover index also escaped me) [...] 9294 28 23_SAP r3 and DB2/OS390 v60_20_bdato@PSGAUS.JNJ.COM30_Mon, 4 Jun 2001 15:05:24 -0500445_ISO-8859-1 We are a pharmaceutical company and are currently in the process of migrating our current SAP R2 environment (which is VSAM based) to SAP R3 4.6 / OS390 DB2 v6. I would like to ask anyone out there with the same set- up who are willing to share with us their experiences and give us some advice on do’s and don’ts to send me an email of your contact information. Also, is there an SAP/DB2 listserve that we need to subscribe to? [...] 9323 27 34_Requirements for table with clob ?14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Mon, 4 Jun 2001 17:21:00 -0400487_iso-8859-1 > Hi List, > Posting this question again - any feedback will be much appreciated.



> I need to define a table with a CLOB column - size 10 MB. Right now, I > have 2 tablespaces for data - one 4K and one 32K, with corresponding index > table spaces and buffer pools. > > Is it recommended to create a separate TS for the clob table ? What about > a separate bufferpool ? Any Do's and Dont's in this matter will be much > appreciated. > > Thanks, > > Abhijit [...] 9351 80 38_Re: Requirements for table with clob ?0_19_Tim.Lowe@STPAUL.COM30_Mon, 4 Jun 2001 17:15:01 -0500601_us-ascii Abhijit, You will have to create an AUXILIARY table in a LOB tablespace. It is strongly recommended that LOB tablespaces be in separate bufferpools.

There is a LOT of good information available. Read the manuals. See the following: the DB2 UDB for OS/390 Administration Guide, SC26-9003, "Choosing a page size for LOBs". the SQL Reference manual, SC26-9014, under "Create Tablespace" and "Create Auxiliary Table". the DB2 UDB for OS/390 V6 Technical Update, SG24-6108. "LOBs performance considerations". the DB2 UDB for OS/390 V6 Performance Topics, SG24-5351, under "page size". [...] 9432 55 20_SQL: Access vs. UDB?11_James Szabo18_jim.szabo@CORE.COM30_Sun, 3 Jun 2001 18:27:49 -0400270_iso-8859-1 As a DB2 DBA, I am sometimes asked about the SQL differences between MS Access 97 or 2000 and the DB2 family.

For example, can you do a full outer join in Access?

Just wondering if anyone has come across a side-by-side analysis.

Jim Szabo 9488 55 20_SQL: Access vs. UDB?11_James Szabo18_jim.szabo@CORE.COM30_Sun, 3 Jun 2001 18:27:49 -0400270_iso-8859-1 As a DB2 DBA, I am sometimes asked about the SQL differences between MS Access 97 or 2000 and the DB2 family.

For example, can you do a full outer join in Access?

Just wondering if anyone has come across a side-by-side analysis.

Jim Szabo 9544 65 41_Repost: IBM Redbooks Summary for May 200113_Morrill, John12_JohnM@VP.NET30_Mon, 4 Jun 2001 16:57:55 -0600394_- Dear DB2 Interested Parties,

Here is a list of the data management related IBM redbooks published by the IBM ITSO in the last month. Each link points to the book's abstract and ordering information.

SG24-5688-00 e-Business Intelligence Front-End Tool Access to OS/390 Data Warehouse Published: May 31, 2001 184 pages see http://www.redbooks.ibm.com/abstracts/sg245688.html [...] 9610 43 19_Clustering Tradeoff12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Mon, 4 Jun 2001 19:17:25 -0500453_us-ascii Fellow Listers:

I have a large SAP table (RESB) with two indexes.

One index is the Primary Key, and it is the clustering index. The cluster ratio is .99296. The first key (RSNUM) cardinality is 34,816. The full key cardinality is 4,965,219 (which equals the table cardinality).

The second index is not unique. The cluster ratio is .35771 The first key (MATNR) cardinality is 6,208. The full key cardinality is 302,771 [...] 9654 137 38_Re: Requirements for table with clob ?14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Mon, 4 Jun 2001 16:13:53 -0700470_iso-8859-1 Abhijit - I am assuming that this is DB2 UDB for Unix et al. If so, you don't have to define a LOB tablespace, but it is recommended that a LONG tablespace be created (DMS) and the table be created using the LONG IN option for the tablespace. Of course this requires that the table be created in a DMS tablespace, which is also recommended. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 9792 138 38_Re: Requirements for table with clob ?14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Mon, 4 Jun 2001 16:13:53 -0700470_iso-8859-1 Abhijit - I am assuming that this is DB2 UDB for Unix et al. If so, you don't have to define a LOB tablespace, but it is recommended that a LONG tablespace be created (DMS) and the table be created using the LONG IN option for the tablespace. Of course this requires that the table be created in a DMS tablespace, which is also recommended. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 9931 89 23_Re: Clustering Tradeoff15_Richard Simpson20_rsimpson@AU1.IBM.COM30_Tue, 5 Jun 2001 11:15:08 +1000501_us-ascii John wrote: >Is there any way to make BOTH sets of queries fast?

Yes, of course there is. There are likely several.

The simplest (big stick) approach is the make the query on MATNR index only (by including all the columns in the query in the index). That makes the cluster ratio irrelevent. This does have some possibly severe tradoffs on performance of insert / update for the table and space consumption for the index. Whether this solution works for you will depend on: [...] 10021 160 34_Re: sqlri_tbl_in_mem_alloc problem14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Mon, 4 Jun 2001 17:57:46 -0700298_iso-8859-1 Dan, I have not seen this problem. I suggest you change the diaglevel back to 3 since you have captured the problem. Diag level 4 incurs significant overhead. Try searching on the web for this problem. Do you have support from IBM? I would suggest opening a PMR if you do. HTH Phil [...] 10182 161 34_Re: sqlri_tbl_in_mem_alloc problem14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Mon, 4 Jun 2001 17:57:46 -0700298_iso-8859-1 Dan, I have not seen this problem. I suggest you change the diaglevel back to 3 since you have captured the problem. Diag level 4 incurs significant overhead. Try searching on the web for this problem. Do you have support from IBM? I would suggest opening a PMR if you do. HTH Phil [...] 10344 106 27_Re: Unexpected SQLCODE -80514_Adrian Collett27_adriancollett@TISCALINET.IT30_Tue, 5 Jun 2001 08:59:06 +0200557_iso-8859-1 Eric, Is the collection DCMITMS in any plan's package list which is used for DPS ?

Adrian.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM] On Behalf Of Pearson, Eric L, Sent: 04 June 2001 17:28 To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805

Adrian, We use dynamic plan selection (shudder!!) but not in the plans which are having a problem. The -805 is identical (04) with or without the SET. The set is: SET CURRENT PACKAGESET = ' '; (single blank between single quotes). [...] 10451 132 23_Re: Clustering Tradeoff13_Adrian Savory27_Adrian.Savory@WORKTHING.COM30_Tue, 5 Jun 2001 08:52:03 +0100466_iso-8859-1 John,

I would imagine that you're a bit constrained on buffer pool tuning, especially with a large table like this So how about backing the RESB table with a hiperpool? Should help reduce the random I/O overhead on the non-cluster table access.

Adrian Savory ACSIS Limited

-----Original Message----- From: Richard Simpson [mailto:rsimpson@AU1.IBM.COM] Sent: 05 June 2001 02:15 To: DB2-L@RYCI.COM Subject: Re: Clustering Tradeoff [...] 10584 17 61_Barbara J. Nigh/Household International is out of the office.0_20_bjnigh@HOUSEHOLD.COM30_Tue, 5 Jun 2001 01:01:00 -0700495_us-ascii I will be out of the office starting 06/04/2001 and will not return until 06/07/2001.

I will respond to your message when I return as I will be unable to access Lotus Notes while I am gone. For Oracle requests, please contact Mark Jones.

================================================ 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. 10602 66 23_Re: Clustering Tradeoff16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 5 Jun 2001 16:33:10 +0530349_- Is the explain report for non-clustering index showing list prefetch ? List prefetch should be efficient if it is done during run time. Sometimes, explain shows list prefetch because of the stats but may not be done during run time due to many other factors like RID pool shortage. You can check on these lines for improving the performance. [...] 10669 78 60_Re: V6 OS390 static / access path changes during execution ?16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 5 Jun 2001 16:29:48 +0530591_- JP,

Which monitor you are using and is this getpage corresponds to only tables you are using ? It would be better if you look at the distribution on this getpage object wise. If you are with Omegamon, you can look at this distribution.

Regards, Sanjeev

> -----Original Message----- > From: Jean-Paul Coenen [SMTP:jpc_db2@HOTMAIL.COM] > Sent: Tuesday, June 05, 2001 1:17 AM > To: DB2-L@RYCI.COM > Subject: V6 OS390 static / access path changes during execution ? > > Forgot a subject, so this is a second try (sorry). > > Dear list, > > DB2 V6 on OS/390 (recently [...] 10748 98 27_Re: SAP r3 and DB2/OS390 v60_20_John_Lendman@FPL.COM30_Tue, 5 Jun 2001 07:15:52 -0400711_iso-8859-1 Here is the listserve for SAP SAPDB2OS390@LISTSERV.CAI.COM

John C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413





bdato@PSGAUS. JNJ.COM To: DB2-L@RYCI.COM Sent by: "DB2 cc: Data Base Subject: SAP r3 and DB2/OS390 v6 Discussion List"



06/04/01 04:05 PM Please respond to "DB2 Data Base Discussion List"











We are a pharmaceutical company and are currently in the process of migrating our current SAP R2 environment (which is VSAM based) to SAP R3 4.6 / OS390 DB2 v6. I would like to ask anyone out there with the same set- up who are willing to share with us their experiences [...] 10847 25 41_Request for Java coded Connection Pooling11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Tue, 5 Jun 2001 08:10:02 -0400409_us-ascii Please indicate sites where I can find Connection Pooling code written in Java for connecting from DB2 V7.1 on Solaris to DB2V6 OS390. It would also be nice if this code could handle setting up connection pool to Oracle 8.1.6. Our current code is outdated, written to Java 1.2, and I'd like to replace it with code written to Java 2.0 API, and with code that's been tested and more widely used. [...] 10873 146 27_Re: Unexpected SQLCODE -80516_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 5 Jun 2001 17:54:04 +0530320_iso-8859-1 Eric,

I have read this mail now just because i found lots of replies to this mail. Is it possible for you to run this transaction in debugging mode from CEDF and see the first SQL calls after connect to DBP2. That probably will give the idea where the package is searched and tried to be located. [...] 11020 43 33_Error 00E7900C on Building Sql SP11_Dimoka Popy24_dimokap@INTERAMERICAN.GR30_Tue, 5 Jun 2001 15:47:28 +0300416_- Hi list, i am trying to set up my own Sql SPs on DB2 v6.1.1 for OS/390 v2.10 using the IBM DB2 SP Builder and i am not able to get the following error over: ----------------------------------------------------------------------------

Create stored procedure returns -471.

[IBM][CLI Driver][DB2] SQL0471N Invocation of routine "SYSPROC .DSNTPSMP " failed due to reason "00E7900C". SQLSTATE=55023 [...] 11064 70 37_Re: Error 00E7900C on Building Sql SP17_Linda F. Claussen18_lindafc@NETINS.NET30_Tue, 5 Jun 2001 08:11:28 -0500393_iso-8859-1 Poppy,

The problem is with the IBM supplied stored procedure SYSPROC.DSNTPSMP which is the stored procedure that SPB calls to prep your stored procedure. Make sure that the DSNTPSMP stored procedure is defined correctly and points to a valid WLM environment.

Hope this helps, Linda F. Claussen Claussen & Associates, Inc. http://showcase.netins.net/web/lclaussen [...] 11135 95 27_Re: SAP r3 and DB2/OS390 v613_Thomas, Janis19_Janis.Thomas@CA.COM30_Tue, 5 Jun 2001 09:11:37 -0400559_iso-8859-1 Some additional information (from the list owner):

To subscribe to this list, address an email to: listserv@listserv.ca.com Include the following command as the text of the email (blank subject): SUBSCRIBE SAPDB2OS390

Further info can be found here: http://www.ca.com/products/db2/email_forum.htm

Let me know if anyone has any problems subscribing. The list is not very active (there is a PeopleSoft list which is more active - directions can also be found at the link above), but maybe someone can start some discussion. [...] 11231 27 27_TABLE QUALIFIER - WEBSPHERE14_Karthik Ganesh21_karthik_gus@YAHOO.COM30_Tue, 5 Jun 2001 06:00:28 -0700346_us-ascii I would appreciate if someone could me help me out here.We are having problem accessing DB2 on os/390 USING WEBSPEHRE. We are trying to set the table qualifier dynamically for all the enviroments on the client to access the DB2 tables. I was wondering is there a way to attach the qulaifer on the client rather than creating alias. [...] 11259 129 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 5 Jun 2001 09:21:48 -0400309_iso-8859-1 YES, all of the DPS stuff is in collection DCMITMS. However, none of the plans having the problem participate in thread reuse with the DCMITMS plans (those have their own threads defined in the RCT). The -805 problem is also happening with plans which have no reference to collection DCMITMS. [...] 11389 176 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 5 Jun 2001 09:26:23 -0400539_iso-8859-1 I have obtained similar information via Detector SQL application trace and Omegamon application SQL trace. I've not seen anything suspicious like SET CURRENT PACKAGESET. Alas, the CEDF option is not manageable since there seems to be no transaction which gets the -805 consistently enough to re-create the problem predictably. The CEDF option would require me to learn the screens of several applications and execute the (sometimes rather complex) transactions hundreds of times under CEDF in an attempt to find the error. [...] 11566 102 23_Re: Clustering Tradeoff12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Tue, 5 Jun 2001 08:27:54 -0500379_us-ascii Sanjeev, the Explain does show list prefetch.

There is no RID pool shortage.

However, at runtime, I get high wait for other I/O.

I think that this is because the rows are so far apart (I computed an average of 208 pages between selected rows), so each I/O retrieves few rows. As I understand the prefetch, each I/O is confined to one cylinder. [...] 11669 61 42_Unload float in readable format (Urgent !)11_Eric Wilkin18_eric.wilkin@NBB.BE30_Tue, 5 Jun 2001 15:20:48 +0200386_iso-8859-1 Dear list,

We have to load a SQL/Server database with data coming from DB2 OS/390 V5. One of the table contains 1 float column.

Using DSNTIAUL to unload the table we get hex in the output file. We do not find any DB2 function to get something usable by SQL/Server (like "1.2.. E02") Is there a solution with DSNTIAUL ? Should we use another unload tools ? [...] 11731 28 62_Is IMS intrinsically better than DB2 for hosting XML documents14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Tue, 5 Jun 2001 08:37:07 -0500381_iso-8859-1 The purpose of this post is purely to get people's thoughts on this.

There are niche players building XML databases, mainly using a hierarchic model. The key selling point is a perceived need to access an entire XML document quickly for transmission over the web. (Mind you, they often run on NT, so scalability was probably not a critical design factor...) [...] 11760 62 42_Unload float in readable format (Urgent !)11_Eric Wilkin18_eric.wilkin@NBB.BE30_Tue, 5 Jun 2001 15:20:48 +0200386_iso-8859-1 Dear list,

We have to load a SQL/Server database with data coming from DB2 OS/390 V5. One of the table contains 1 float column.

Using DSNTIAUL to unload the table we get hex in the output file. We do not find any DB2 function to get something usable by SQL/Server (like "1.2.. E02") Is there a solution with DSNTIAUL ? Should we use another unload tools ? [...] 11823 47 46_Re: Unload float in readable format (Urgent !)12_Deluca, Fred27_Fred.Deluca@CCRA-ADRC.GC.CA30_Tue, 5 Jun 2001 09:51:28 -0400415_iso-8859-1 Use DSNTIAUL with PARMS("SQL") and code a full select (every column) and use the DIGITS or CHAR function on float column.



Thanks, Fred De Luca DB2 DB Specialist DSD/ADBS Fred.Deluca@ccra-adrc.gc.ca



-----Original Message----- From: Eric Wilkin [mailto:eric.wilkin@NBB.BE] Sent: June 5, 2001 9:21 AM To: DB2-L@RYCI.COM Subject: Unload float in readable format (Urgent !) [...] 11871 100 27_Re: SAP r3 and DB2/OS390 v612_Taylor, John19_John.Taylor@SSA.GOV30_Tue, 5 Jun 2001 10:00:36 -0400479_iso-8859-1 We do not use SAP here but, there is a recent IBM redpaper which has some info this at: http://www.redbooks.ibm.com/redpapers/pdfs/redp0131.pdf

SAP R/3 on DB2 for OS/390: DB2 Features That Benefit SAP



"This IBM Redpaper describes the benefits that DB2 for OS/390 provides for SAP R/3. The paper discusses DB2 for OS/390 V5, V6, and V7 functions which address more specifically SAP R/3 data access, performance and maintenance requirements." [...] 11972 48 31_Re: TABLE QUALIFIER - WEBSPHERE11_Bikash Paul19_bikash_db@YAHOO.COM30_Tue, 5 Jun 2001 07:03:17 -0700572_us-ascii Hi, Is it not possible to do SET CURRENT SCHEMA or SET CURRENT SQLID from the client program. That is how generally it is done.

Regards, Bikash

--- Karthik Ganesh wrote: > I would appreciate if someone could me help me out > here.We are having problem accessing DB2 on os/390 > USING WEBSPEHRE. We are trying to set the table > qualifier dynamically for all the enviroments on the > client to access the DB2 tables. I was wondering is > there a way to attach the qulaifer on the client > rather than creating alias. > > [...] 12021 21 5_DEC310_18_jchimes@PARKER.COM30_Tue, 5 Jun 2001 10:20:56 -0400561_us-ascii Hi all,

Just wondering if anyone has had the need the use the DEC31 option for decimal arithmetic (panel DSNTIPF) for dynamic sql (DB2 V6.1). IBM states that this may lead to 'bind errors, particularly in division operations'. Has anyone found this to be true; any other problems experienced?

TIA

================================================ 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. 12043 136 23_Re: Clustering Tradeoff13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Tue, 5 Jun 2001 09:26:31 -0500391_us-ascii John,

There is always index-only (previously suggested), altering the key generation algorithm so that they exhibit similar clustering (not always possible - especially with existing data), or merely clustering on the column which selects the most rows, since it has the most to benefit from the reduction in random I/O (this is the reverse of your current clustering). [...] 12180 115 27_Re: SAP r3 and DB2/OS390 v614_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM30_Tue, 5 Jun 2001 09:28:47 -0500455_iso-8859-1 I'm an SAP 3.1H user with DB2 V5. There are several other SAP users that frequent this list service too.

-----Original Message----- From: Taylor, John [mailto:John.Taylor@SSA.GOV] Sent: Tuesday, June 05, 2001 9:01 AM To: DB2-L@RYCI.COM Subject: Re: SAP r3 and DB2/OS390 v6



We do not use SAP here but, there is a recent IBM redpaper which has some info this at: http://www.redbooks.ibm.com/redpapers/pdfs/redp0131.pdf [...] 12296 41 77_Check Data Utility / Exception Table Error With Identity Column In Base Table13_DARRELL HONEA14_b06dfh@FDS.COM30_Tue, 5 Jun 2001 10:20:45 -0400691_us-ascii Any help with the following scenario would be appreciated.

Problem: Added an R/I relationship to Table1 that contains an identity column. The Tablespace DB.TS1 is placed in "Check Pending".

Attempted Resolution: Executed Check Data Utility with SCOPE ALL and found 1,791 violations against the restraint added. Created exception Table2, defined with the same attributes as Table1 with the exception of the identity column attributes, GENERATED BY DEFAULT AS IDENTITY, START WITH etc. Exception Table2 was also defined with a column for the RID and a Timestamp. Executed CHECK DATA TABLESPACE DB.TS1 SCOPE ALL FOR EXCEPTION IN OWNER.TABLE1 USE OWNER.TABLE2 DELETE [...] 12338 16 19_BMC change manager.15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Tue, 5 Jun 2001 10:39:49 -0400492_iso-8859-1 Does BMC change manager has a facility to compare and migrate DB2 stored procedure definition between two subsystems. We use CHANGE MANAGER 6.2.01. I could not see this in the compare. How do other companies doing this?

Thanks..Sibi

================================================ 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. 12355 33 11_Re: DSSSIZE16_Lemaire, Nigel B21_nigel.lemaire@EDS.COM30_Tue, 5 Jun 2001 09:54:32 -0500343_iso-8859-1 Looks like IBM has realized a fix for the issue of having mixed EA and non EA defined clusters. They recently released APAR Identifier ...... PQ46367 PROBLEM CONCLUSION: > DB2 has been modified to always open datasets with CI-level > access (regardless of the DSSIZE) if the DFP level allows CI > access (DFSMS 1.5 or higher). [...] 12389 57 42_OS/390 V5 - NEAROFFPOS / FAROFFPOS & REORG10_Joan Green22_Joan.Green@BOIMAIL.COM30_Tue, 5 Jun 2001 15:58:02 +0100569_us-ascii Hi List,

Perhaps you can solve a little riddle.

We have a rogue online transaction which used to take 2/3 seconds and is now taking up to 20 seconds cpu. I've explained the SQL and everything is as I expect it to be - using all the indices I'd expect. My stats are showing NEAROFFPOS (10-15% of rows) & FAROFFPOS(up to 50% of rows) on the indices being used in the queries associated with the transaction. This indicated to me the necessity to reorg & hopefully the resolution to my runaway transaction? This however is not the case. Post - [...] 12447 104 46_Re: Unload float in readable format (Urgent !)11_Eric Wilkin18_eric.wilkin@NBB.BE30_Tue, 5 Jun 2001 16:58:57 +0200446_iso-8859-1 Fred, Thanks for your response. We have tried your solution already. It does not work ! ...

Eric Wilkin National Bank of Belgium SYDS-DB tel +32 (0)2 221 45 99 fax +32 (0)2 221 30 92





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Deluca, Fred Sent: mardi 5 juin 2001 15:51 To: DB2-L@RYCI.COM Subject: Re: Unload float in readable format (Urgent !) [...] 12552 65 74_Repost: Webcast: "DB2 Universal Database Performance Tuning Funda mentals"13_Morrill, John12_JohnM@VP.NET30_Tue, 5 Jun 2001 09:08:48 -0600410_- Dear DB2 Interested Parties,

Below is a bulletin about an upcoming Webcast by Berni Schiefer, IBM Distinguished Engineer and Manager - DB2 Performance and Advanced Technology in the IBM Toronto Lab. The presentation will be helpful to those of you building/supporting high performance DB2 implementations on UNIX and Windows systems. Just click on the URL below to register and view the Webcast. [...] 12618 46 23_Re: BMC change manager.15_Robert lawrence21_rlawrence@BOSCOVS.COM30_Tue, 5 Jun 2001 11:07:03 -0400626_iso-8859-1 Have you called BMC support and asked them this question? They have been very helpful in the past on other questions

HTH Bob Lawrence DBA Boscov's Dept Stores LLc





> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Philip, Sibimon > Sent: Tuesday, June 05, 2001 10:40 AM > To: DB2-L@RYCI.COM > Subject: BMC change manager. > > > Does BMC change manager has a facility to compare and migrate DB2 stored > procedure definition between two subsystems. We use CHANGE > MANAGER 6.2.01. I > could not see this in the compare. How do [...] 12665 105 46_Re: Unload float in readable format (Urgent !)11_Eric Wilkin18_eric.wilkin@NBB.BE30_Tue, 5 Jun 2001 16:58:57 +0200446_iso-8859-1 Fred, Thanks for your response. We have tried your solution already. It does not work ! ...

Eric Wilkin National Bank of Belgium SYDS-DB tel +32 (0)2 221 45 99 fax +32 (0)2 221 30 92





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Deluca, Fred Sent: mardi 5 juin 2001 15:51 To: DB2-L@RYCI.COM Subject: Re: Unload float in readable format (Urgent !) [...] 12771 78 27_Re: SAP r3 and DB2/OS390 v613_Adelino Regio28_adelino.regio@EDINFOR.EDP.PT30_Tue, 5 Jun 2001 16:23:45 +0100566_iso-8859-1 Greetings



In a few words: you will have to cope with bad SQL, bad index design, RUNSTATS and Reorg all the time. DB2 really excels running SAP R/3... If you need some more information please contact me by email: regulus@net.sapo.pt.



(My opinion only...)





Regards

Adelino Regio (Database Administrator)















bdato@PSGAUS.JNJ.COM@RYCI.COM> on 04-06-2001 21:05:24

Please respond to DB2 Data Base Discussion List [...] 12850 33 41_Request for Java coded Connection Pooling11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Tue, 5 Jun 2001 11:51:09 -0400318_us-ascii I'm reposting this message because I can't believe that at least 200 other sites aren't using some form of Java application to enable connection pooling from a web server, to OS390 DB2 V6. Someone...... Please share. OR... If OS390 DB2 V6 DDF support for connection pooling is sufficient, please say so. [...] 12884 94 46_Re: OS/390 V5 - NEAROFFPOS / FAROFFPOS & REORG19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Tue, 5 Jun 2001 10:47:50 -0500569_ISO-8859-1 Hi Joan, The "clustering index" is used during reorg to order the rows in a table. The fact that NEAR and FAROFF didn't change after the reorg probably indicates the indexes you are interested in didn't include the clustering index. Check LEAFDIST on the indexes you are using. Perfect LEAFDIST is 100 (I seem to recall) and anything above 200 or so would indicate the index could benefit from a rebuild/reorg. Other possibilities are a sudden increase in volume, I/O subsystem problem, something really did change in the EXPLAIN and it was overlooked. [...] 12979 97 21_Re: False Contentions11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Tue, 5 Jun 2001 11:49:46 -0400365_ISO-8859-1 Ruth

I do not think you have a problem. The recommendation to keep below 5% relates to Global Contention %. The False Contention % reported by Mainview is a percentage of the Global Contentions that were False, and 15% is acceptable. The figures shown in your output mean that 11% of 2% (i.e. 0.22%) of requests resulted in False Contention. [...] 13077 56 45_Re: Request for Java coded Connection Pooling12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 5 Jun 2001 08:53:47 -0700395_us-ascii Why would you need Connection Pooling code in Java for DB2 V7.1 to DB2V6 OS/390? DB2 V7.1 EE provides Connection Pooling/Connection Concentrator functions for Java on that server. Its a full function Connection Pool interface for the Websphere. See the DB2 Connect Users Guide for details. In addition, OS/390 DB2 V6 provides an additional level of connection pooling at that end. [...] 13134 15 13_Re: deadlocks12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 5 Jun 2001 10:35:25 -0500283_- With CURRENTDATA(NO) there should be very minimal locking on the data, except in a hot spot situation. As noted later, it looks as though there is a very hot row. My guess is that we have FOR UPDATE OF and an UPDATE or just the very hot row.

Roger Miller, DB2 for z/OS [...] 13150 201 46_Re: Unload float in readable format (Urgent !)12_Deluca, Fred27_Fred.Deluca@CCRA-ADRC.GC.CA30_Tue, 5 Jun 2001 12:14:14 -0400724_iso-8859-1 What format are you trying to get ? Here is a solution that we used to migrate data to Excel. This should produce a load format that SQL SERVER can use.

1. We generate a SELECT that provides a comma-delimited output.

SELECT 'SELECT VALUE(DIGITS('||NAME||'),'''') ' , COLNO FROM SYSIBM.SYSCOLUMNS WHERE(COLTYPE='INTEGER' OR COLTYPE='SMALLINT') AND TBNAME = 'tbname' AND TBCREATOR = 'tbcreator' AND COLNO = 1 UNION SELECT 'SELECT VALUE(CHAR('||NAME||'),'''') ' , COLNO FROM SYSIBM.SYSCOLUMNS WHERE (COLTYPE = 'DECIMAL') AND TBNAME = 'tbname' AND TBCREATOR = 'tbcreator' AND COLNO = 1 UNION SELECT 'SELECT ''"''||VALUE(CHAR('||NAME||'),'''')||''"'' ' , COLNO FROM SYSIBM.SYSCOLUMNS WHERE (COLTYPE [...] 13352 122 46_Re: OS/390 V5 - NEAROFFPOS / FAROFFPOS & REORG12_Isaac Yassin20_yassini@BEZEQINT.NET30_Tue, 5 Jun 2001 19:15:38 +0200460_iso-8859-1 Hi, Use DB2 monitor to look for wait time and open a trace on the query. Try to reorg the small table, altering it to add the columns makes its rows a var length.

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- From: "DAVIS, RICK (SBCSI)" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, June 05, 2001 5:47 PM Subject: Re: OS/390 V5 - NEAROFFPOS / FAROFFPOS & REORG [...] 13475 98 17_False Contentions12_Chris Munson18_cmunson@US.IBM.COM30_Tue, 5 Jun 2001 09:18:23 -0700601_us-ascii Ruth, Typically the false contention rate is a percentage of the overall global contention. If BMC reports the data this way this would show from your data that your false contention is 11% of the overall global contention which is 2% which is very acceptable. If you were to actually experience an unacceptable level of false contention the typical response is a larger lock structure. From your report it looks like you have a 16M lock structure, which is pretty small in comparison to other customers. We recommend 16M for 'small' amounts of data sharing or a one member arrangement. [...] 13574 30 41_DB2 utilities and monitor recommendations12_Doug Bulbeck25_dbulbeck@CUCENTRAL-AB.COM30_Tue, 5 Jun 2001 10:45:25 -0600375_iso-8859-1 Hi List,

We are a new DB2 installation. We are installing DB2 V7 on OS/390 V2.10. I would like to get recommendations for DB2 utility packages and DB2 monitor packages. What would be the ideal set up for a new installation? Are there combinations that work better together, or combinations that we should avoid? Any comments, good or bad, are welcome. [...] 13605 66 23_Re: BMC change manager.15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Tue, 5 Jun 2001 13:01:42 -0400385_iso-8859-1 Yes. I did and got a reply but waiting for more detail. It seems BMC is going to support it in next release, but not sure.

How is all the companies doing it now. When a store procedure is called from Trigger and other os/390 programs. We need to check the dependency and may need to rebind and drop the triggers and re-add it before dropping a stored procedure. [...] 13672 45 77_Check Data Utility / Exception Table Error With Identity Column In Base Table13_DARRELL HONEA14_b06dfh@FDS.COM30_Tue, 5 Jun 2001 13:05:33 -0400678_us-ascii If you receive this message more than once, please accept my apologies, as it was rejected twice due to sign up issues.

Any help with the following scenario would be appreciated.

Problem: Added an R/I relationship to Table1 that contains an identity column. The Tablespace DB.TS1 is placed in "Check Pending".

Attempted Resolution: Executed Check Data Utility with SCOPE ALL and found 1,791 violations against the restraint added. Created exception Table2, defined with the same attributes as Table1 with the exception of the identity column attributes, GENERATED BY DEFAULT AS IDENTITY, START WITH etc. Exception Table2 was also defined with [...] 13718 43 45_Re: DB2 utilities and monitor recommendations20_Edward C. Benoit Jr.19_DB2Automate@AOL.COM28_Tue, 5 Jun 2001 13:32:11 EDT159_US-ASCII Doug, For DB2 Disaster Recovery automation you may wish to visit: http://www.recoveryknowledge.com

Regards, Ed.







13762 196 45_Re: DB2 utilities and monitor recommendations12_Troy Coleman19_Colematr@MEIJER.COM30_Tue, 5 Jun 2001 13:43:41 -0400505_US-ASCII I think you could open up a can of worms on this one. I've worked in both systems and application DBA world. The monitor that is perfect for a systems programmer is not always the same tool needed by the application DBA. The tools that are useful for the application DBA will not always help your systems DBA. In either case you will find IBM, CA/Platinum, BMC, CDB, Candle, Compuware, Responsive Systems, and I sure some I'm not thinking of at the moment to have many tools that will help. [...] 13959 66 27_Re: SAP r3 and DB2/OS390 v611_Randy Scott19_Randy_Scott@VFC.COM30_Tue, 5 Jun 2001 14:44:34 -0400427_iso-8859-1 We are SAP 31i and 45b with DB2/OS390 V6.1. We have no third party tools, so we rely on IBM utilities and DB2PM. Our application servers are on AIX.

Randy Scott VF Services









bdato@PSGAUS.JNJ.COM@RYCI.COM> on 06/04/2001 04:05:24 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 14026 23 31_Re: TABLE QUALIFIER - WEBSPHERE10_Rob Wright17_rwright@LIC.CO.NZ30_Wed, 6 Jun 2001 09:40:31 +1200587_us-ascii For DB2 V7 clients (or DB2 Connect V7) you can edit your DB2CLI.INI and add an entry as follows:

[data-source-name] CURRENTSCHEMA=

It is not possible to do this with the Client Configuartion Assistant.

Check your documentation to ensure that the ODBC driver you are using supports this option.

Rob

================================================ 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. 14050 36 27_Re: Unexpected SQLCODE -80510_Rob Wright17_rwright@LIC.CO.NZ30_Wed, 6 Jun 2001 09:26:43 +1200351_us-ascii Eric

Are you using dynamic plan switching? This is different from dynamic plan selection, but you may be doing this inadvertently, especially if your application takes SYNCPOINTs and does SET CURRENT PACKAGESET.

I would review the DB2 Admin Guide, with a copy of your RCT. Take a look at (these are from the DB2 V6 manual) [...] 14087 20 60_Re: V6 OS390 static / access path changes during execution ?12_Isaac Yassin20_yassini@BEZEQINT.NET30_Tue, 5 Jun 2001 22:03:08 +0200511_Windows-1252 Hi, For the MAX, which you say takes most of the time, how's the cursor defined ? Is the tablespace define "locksize row" ? Is it in a small, very active BP ? - Going for desc index can certainly help. Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net

================================================ 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. 14108 89 45_Re: DB2 utilities and monitor recommendations16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM30_Tue, 5 Jun 2001 20:22:02 +0100359_us-ascii Doug,

as you are no doubt aware, !Candle have a range of products for monitoring and tuning your DB2 subsystem.

Please feel free to contact me offline if you'd like an informal chat on what we could offer you, in the way of products and solutions.

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd. [...] 14198 73 82_Re: Check Data Utility / Exception Table Error With Identity Colu mn In Base Table13_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU30_Wed, 6 Jun 2001 09:38:53 +1000442_- Darrell,

I have not tried this myself but try :

CREATE TABLE ... LIKE ... INCLUDING IDENTITY

Hope this helps

Shaun

-----Original Message----- From: DARRELL HONEA [mailto:b06dfh@FDS.COM] Sent: Wednesday, 6 June 2001 0:21 To: DB2-L@RYCI.COM Subject: Check Data Utility / Exception Table Error With Identity Column In Base Table



Any help with the following scenario would be appreciated. [...] 14272 71 45_Re: Request for Java coded Connection Pooling19_CHRISTOPHER STARNES14_b05cxs@FDS.COM30_Tue, 5 Jun 2001 14:27:38 -0400450_us-ascii For applications without a web application server, I have used the Db Connection Broker from Java Exchange (http://www.javaexchange.com).

HTH,

Chris





Joe Luthman cc: Sent by: DB2 Subject: Request for Java coded Connection Data Base Pooling Discussion List



06/05/01 11:51 AM Please respond to DB2 Data Base Discussion List [...] 14344 93 22_DB2 v6.1 and old dbrms22_Kennedy, Tom - Cendant23_Tom.Kennedy@CENDANT.COM30_Tue, 5 Jun 2001 15:26:19 -0400424_iso-8859-1 A question about old dbrms, ones without colons, and migration to DB2 v6.1. I understand that the pre-existing dbrms will work in DB2 v6.1 and the existence of these dbrms will not prevent the migration from the previous release. A question came up about what happens after the migration is complete and an old dbrm, one without the colon, is involved in an autorebind process. Will the rebind process fail? [...] 14438 20 38_SAS V8 & declaring global temp tables?14_Jessen Michael27_JessenMichael@JOHNDEERE.COM30_Tue, 5 Jun 2001 15:15:35 -0500467_iso-8859-1 Has anyone attempted to declare global temp tables using SAS?

If so, I would appreciate an example of how this is coded.

Thanks for your help! Mike

ps - We are running SAS V8, DB2 V6 on OS/390.

================================================ 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. 14459 61 30_Re: DB2 Connect and ColdFusion9_Eric Kwai18_Eric_Kwai@DTCC.COM30_Tue, 5 Jun 2001 18:26:01 -0500377_- Rob: Check the following basic DB2 Connect setup in your environment:

If SNA is used, verify if DDF tables are setup correctly. If TCPIP is used, verify DDF is set up with the right port and tcpip profile on OS/390 is set up correctly.

Verify if DCS directory, node directory and db directory on your client and DB2 Connect server is configured correctly. [...] 14521 181 23_Re: Clustering Tradeoff12_Higgins John25_HigginsJohn@JOHNDEERE.COM30_Tue, 5 Jun 2001 14:26:16 -0500482_iso-8859-1 Hi, Terry. I was in your DB2 Symposium in St. Louis.

Index-only is not possible. The rows have 181 columns (1,421 bytes max). Many of the queries are 'SELECT * ". Many others have a large select list.

How does the self-join help? The queries are selecting an average of 800 rows out of 4,965,219. Given the random nature of this index, that usually means accessing 800 pages out of 1,290,596. This could be as bad as 1,600 pages between selected rows. [...] 14703 75 78_Correction: Webcast: "DB2 Universal Database Performance Tuning F undamentals"13_Morrill, John12_JohnM@VP.NET30_Tue, 5 Jun 2001 21:22:38 -0600612_- The correct time for this Webcast is: 11:00am-12:30pm EDT on June 13

-----Original Message----- Subject: Repost: Webcast: "DB2 Universal Database Performance Tuning Funda mentals"



Dear DB2 Interested Parties,

Below is a bulletin about an upcoming Webcast by Berni Schiefer, IBM Distinguished Engineer and Manager - DB2 Performance and Advanced Technology in the IBM Toronto Lab. The presentation will be helpful to those of you building/supporting high performance DB2 implementations on UNIX and Windows systems. Just click on the URL below to register and view the Webcast. [...] 14779 17 12_Load varchar13_Kim Comanchee13_KimC@CDTG.NET30_Tue, 5 Jun 2001 21:34:54 -0500380_us-ascii What is the best or easiest way to load a varchar column using a flat fixed file for the first time?

Thanks, Kim Comanchee

================================================ 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. 14797 21 29_Problems in stored procedures13_Mathew, Sithu30_sithu.mathew@BLR.HPSGLOBAL.COM30_Wed, 6 Jun 2001 09:37:06 +0500442_- Hi List,

Can we pass an array and a structure to a stored procedure which is written in C Language?????.....

Suggestions are well appreciated....



Thanks & Regards Mathew

================================================ 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. 14819 209 23_Re: Clustering Tradeoff16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 6 Jun 2001 11:14:10 +0530386_iso-8859-1 John,

If i have understood what Terry is trying to mention then you have to add the clustering index column (first column probably) in the non-clustering index and then use self join. This way the access to data may be sequential. I would be interested in seeing the access path of this. This is to be used in case of the queries using non-clustering index only. [...] 15029 78 33_Re: Problems in stored procedures0_24_markus.schaub@SYSTOR.COM30_Wed, 6 Jun 2001 07:49:06 +0200661_us-ascii hi mathew

sure you can. we are passing an array to all of our stored procedures.

we are using the following:

in c:

char inparm[8][20]

which is maped to the create procedures as:

in sql:

create procedure myproc (in char(160), ...) ... language c ... ;

hope its any help for you.



regards markus

ps: standard disclaimer ...





|-------+--------------------------------> | | "Mathew, Sithu"| | | | | | Sent by: DB2 Data Base| | | Discussion List | | | | | | | | | | | | | | | 06.06.2001 06:37| | | [...] 15108 115 33_Re: Problems in stored procedures13_Mathew, Sithu30_sithu.mathew@BLR.HPSGLOBAL.COM30_Wed, 6 Jun 2001 11:22:45 +0500667_- Hi Markus,

Thanks a lot for the suggestions. But we have a typical problem... How we can pass an integer(numeric) array to a stored procedure???.....



Suggestions are well appreciated...

Thanks & Regards Mathew









> -----Original Message----- > From: markus.schaub@SYSTOR.COM [SMTP:markus.schaub@SYSTOR.COM] > Sent: Wednesday, June 06, 2001 11:19 AM > To: DB2-L@RYCI.COM > Subject: Re: Problems in stored procedures > > hi mathew > > sure you can. we are passing an array to all of our stored procedures. > > we are using the following: > > in c: > > char inparm[8][20] > > which is maped to the [...] 15224 18 25_Access of DB2 from Tuxedo17_Kumar, Arun (CTS)23_KArun@CAL.COGNIZANT.COM30_Wed, 6 Jun 2001 12:29:06 +0530358_- Hi All, Any body use Tuxedo to fetch data from DB2 ?.

Please reply soon.

Thanks & Regards Arun Kumar

================================================ 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. 15243 141 46_Re: OS/390 V5 - NEAROFFPOS / FAROFFPOS & REORG20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM30_Wed, 6 Jun 2001 08:05:27 +0100353_iso-8859-1 Please don't think I am being rude in stating the obvious, but if your OFFPOS values really haven't changed at all then there are two basic things that could be the cause i) FAROFFPOS and NEAROFFPOS are not used from V5 onwards - have you looked at FAROFFPOSF and NEAROFFPOSF? ii) You DID run runstats again after the reorg, didn't you? [...] 15385 16 33_James Drewe is out of the office.11_James Drewe20_James.Drewe@AEXP.COM30_Wed, 6 Jun 2001 01:01:38 -0700436_us-ascii I will be out of the office from 06/05/2001 until 06/08/2001.

I will respond to your message when I return. For DBA assistance, please contact Duane Foss at 602-766-5662. 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. 15402 17 41_Performance Considerations for Log files.15_Priyanka Deswal19_prdeswal@IN.IBM.COM30_Wed, 6 Jun 2001 04:22:03 +0000452_us-ascii What are the performance considerations for defining the number of primary and secondary log files and also the logfilesize if I want the first connect to my database to be fast?

Thanks Priyanka

================================================ 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. 15420 49 18_Database holy war.15_Booysen Corneel26_Corneel.Booysen@SITA.CO.ZA30_Wed, 6 Jun 2001 10:53:38 +0200690_iso-8859-1 Hi all,

I am a CICS systems programmer at a Oracle on OS/390 shop.

Some Oracle representitives seem to be campaigning hard against DB2 - as management are looking/investigating/inquiring about DB2.

The following message was sent out by these representitives yesterday : ---------------- >Subject: What's the Fastest Database on IBM's Fastest Computer? > >When IBM wanted to show off their fastest new computer, IBM chose Oracle. >Interesting. > >When IBM wanted to demonstrate the server power of their eServer pSeries >680, they >turned to the TPC-C, an independent online transaction processing benchmark. >To IBM's >delight, the 680 achieved a world [...] 15470 22 24_Warehouse Center Problem11_Endy Lambey20_endyl@CENTRIN.NET.ID30_Wed, 6 Jun 2001 16:11:23 +0700343_- Dear all,

We are facing a very confusing problem with Warehouse Center. We cannot send a notification email after each step completed. We've already set up the proper mail server and address, but still cannot send any notification email.

Anybody knows something about this? Please let me know how to solve this problem... [...] 15493 44 21_AW: Database holy war12_Peter, Georg15_G.Peter@DZBW.DE30_Wed, 6 Jun 2001 11:31:58 +0200376_iso-8859-1 Cornel,

we have both database systems running and I have to deal with both - DB2 for OS/390 subsystems and Oracle instances on NT boxes.

From my point of view Oracle has less functionality than DB2, requires more knowledge to maintain and tune. And: In general Oracle uses more space than DB2 and as far as I know it doesn't support compression. [...] 15538 122 46_AW: Unload float in readable format (Urgent !)0_20_Gerhard.Heiss@RUV.DE30_Wed, 6 Jun 2001 11:50:54 +0200649_iso-8859-1 Hi Eric,

did you try the CHAR function?

DSNTIAUL input: //SYSIN DD * SELECT SUBSTR(NAME, 1, 8), SUBSTR(';', 1, 1), CHAR(CLUSTERRATIOF) FROM SYSIBM.SYSINDEXES WHERE CREATOR ='SYSIBM' AND TBNAME ='SYSINDEXES' WITH UR result: ******************************* DSNDXX03;7.84975330256247E-1 DSNDXX01;7.84338691707783E-1 DSNDXX02;8.10759191469043E-1 *******************************

Best regards

Gerhard Heiss ____________________ R+V Versicherung AG Informationmanagement Taunusstraße 1 65193 Wiesbaden Telefon: +49 611 / 533 - 6488 Telefax: +49 611 / 533 - 776488 Mail: Gerhard.Heiss@ruv.de Internet: www.ruv.de [...] 15661 45 45_Re: Performance Considerations for Log files.15_Daniel Scheibli15_scheibd@GMX.NET30_Wed, 6 Jun 2001 12:10:21 +0200420_us-ascii > What are the performance considerations for defining the > number of primary > and secondary log files and also the logfilesize if I want the first > connect to my database to be fast? > > Thanks > Priyanka



Dear Priyanka,

your first choise should be to activate the database using the ACTIVATE DATABASE command. With this the initializations are done before the first connect. [...] 15707 51 25_Re: AW: Database holy war10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 6 Jun 2001 12:36:18 +0200424_us-ascii Hi all estimeed list

I read some articles about Oracle vs DB2 performance, Oracle vs DB2 costs, Oracle vs DB2 scalability. Every article has some pros & cons for each DBMS

As far as I know from my own experience is:

1) The OS390 is by far the most robust of the operating system with respect to UNIX (I do not consider NT an OS for high tx environment). Expecially to prevent real messes. [...] 15759 212 22_Re: Database holy war.0_20_John_Lendman@FPL.COM30_Wed, 6 Jun 2001 07:25:59 -0400535_us-ascii Here is a recent article from IBM on their market share, hope this helps.

Leading Businesses Continue To Choose IBM Over Oracle DB2's Price And Performance Ratio Is The Key Factor In Purchasing Decisions INTERNET WIRE -- IBM (NYSE: IBM - news) announced today that an increasing number of companies are choosing IBM's DB2 Data Management software over Oracle based on its industry-leading price and performance capabilities. According to industry analysts, DB2 is three to five times less expensive than Oracle8i. [...] 15972 109 25_Re: AW: Database holy war16_Blumenthal, Gary21_GBlument@UNCH.UNC.EDU30_Wed, 6 Jun 2001 07:52:59 -0400525_windows-1252 Max,

I have been doing database development, design, and administration for allot of years and have used Oracle, Sybase, and SQL Server. I have only recently (over the last two months) been exposed to DB2. For what it is worth, here are my observations:

1. Database manufacturers are like automobile manufacturers. When a new slick feature is added by Ford, you can bet that in the next model year GM will have the same thing. The same is true with databases. Same features, different syntax. [...] 16082 22 45_Re: Performance Considerations for Log files.15_Priyanka Deswal19_prdeswal@IN.IBM.COM30_Wed, 6 Jun 2001 10:59:31 +0000510_us-ascii Thanks Daniel

Regards Priyanka Deswal IT Specialist IBM Software Group Ph: 91-124-6359466-69, Fax: 91-124-6359477, e-mail : prdeswal@in.ibm.com "Never let your dreams die,for if they die,life would be a broken wing bird that cannot fly." Erving Berling

================================================ 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. 16105 235 22_Re: Database holy war.26_Johnson, Bill (Pittsburgh)26_bjohnson@RUSSELLMELLON.COM30_Wed, 6 Jun 2001 04:55:43 -0700554_iso-8859-1 A couple of interesting observations regarding this article. IBM states that DB2 is 3 to 5 times less expensive than Oracle8i, yet their real life example only experienced "more than 2 times" the savings? Also, IBM claims that DB2 performs better than Oracle8i but doesn't present any proof to back up that claim? It appears to me that the main selling point is the cost savings. I don't think that press releases from one of the participants involved in this comparison is a fair and impartial piece of information. Bill Johnson DB2 DBA [...] 16341 95 22_Re: Database holy war.12_Curran, John20_John.Curran@DRKW.COM30_Wed, 6 Jun 2001 12:58:24 +0100389_iso-8859-1 Hi Corneel

You need to take a look at the Transaction Processing Council's Website www.tpc.org

Oracle appear second in the non-clustered results,

however they are way down the list a performance when you look at the all results

The top spot goes to SQL server but DB2 has a pretty good second place

at 440,000 tpmC at a price/tmpC $25.03 [...] 16437 63 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Wed, 6 Jun 2001 08:00:10 -0400321_iso-8859-1 No SET CURRENT PACKAGESET other than SET CURRENT PACKAGESET = ' '; which was done in a futile effort to prevent the -805. Re RCT, the transactions in question are mostly POOL but even ones with an explicitly defined entry are having this. Some of the programs having this problem are not even CICS (CAF)! [...] 16501 95 45_Re: Request for Java coded Connection Pooling11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Wed, 6 Jun 2001 08:27:23 -0400336_us-ascii Thanks, Chris. I can't be sure if this application will use a web app server, or if I must provide our own code for connection pooling. My confusion actually rested on that word 'if'. Your answer helps clear up my confusion. So if the web server provides the app code for connection pooling, then I need not. Else I must. [...] 16597 18 32_When was a Table last modified ?12_Rakesh Kumar21_rakesh457@HOTMAIL.COM30_Wed, 6 Jun 2001 12:45:10 -0000501_- All, Is there a way to know when a particular table in DB2 is last updated ?

With Regards Rakesh Kumar _________________________________________________________________________ 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. 16616 146 45_Re: Request for Java coded Connection Pooling12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 6 Jun 2001 05:45:08 -0700336_us-ascii Joe, DB2 Connect does provide code for connection pooling as well. If you have DB2 Connect EE V6 or V7, you don't have to write your own connection pooling code, even if you don't use a web app server. And its really easy to setup. In addition, it provides the JAVA APIs for most web servers for their connection pooling. [...] 16763 40 36_Re: When was a Table last modified ?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Wed, 6 Jun 2001 08:56:26 -0400382_iso-8859-1 1) turn on AUDIT? 2) If you have a took like Log Analyzer from CA, you can read the logs filtering on table name.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Rakesh Kumar [mailto:rakesh457@HOTMAIL.COM] Sent: Wednesday, June 06, 2001 8:45 AM To: DB2-L@RYCI.COM Subject: When was a Table last modified ? [...] 16804 44 36_Re: When was a Table last modified ?11_patty rizzi19_patty_db2@YAHOO.COM30_Wed, 6 Jun 2001 06:01:48 -0700683_us-ascii There is a tool from IBM called 'LOGANALYSIS' that look on the log and tell you, in a very easy way, who did the update on a particular table. Hope this help.

Regards,

Patrizia Rizzi

--- Rakesh Kumar wrote: > All, > Is there a way to know when a particular table in > DB2 is last updated ? > > With Regards > Rakesh Kumar > _________________________________________________________________________ > 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 [...] 16849 35 25_Re: AW: Database holy war10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 6 Jun 2001 15:05:23 +0200422_us-ascii Hi Gary

I agree with you as regard points 1), 3) & 6).

I strongly disagree as regard points 2): You may have the best DBA (even certified & ultra certified) on the earth but if your RDMS & your OS (UNIX in the case I saw) doesn't realize that there are I/O error you have no possibility to recover data.Superman or Zaphod Beebelbrox cannot help you. And I saw data lost 3 times over the years. [...] 16885 40 27_Re: Java Connection Pooling17_Miguel de Andrade18_ANDRADE@DE.IBM.COM30_Wed, 6 Jun 2001 14:06:54 +0100575_us-ascii An example for 'Connection Pooling' :

import java.sql.*; import javax.sql.*; // add DB2 JDBC drivers public ProductPK ejbCreate() { try { // initialize JNDI lookup parameters Context ctx = new InitialContext(parms); ... ConnectionPoolDataSource cpds = (ConnectionPoolDataSource)ctx.lookup(cpsource); ... // Following parms could all come from a JNDI look-up cpds.setDatabaseName("PTDB"); cpds.setUserIF("ANDRADE"); ... PooledConnection pc = cpds.getPooledConnection(); Connection conn = pc.getConnection(); ... // do business logic conn.close(); } ... } [...] 16926 21 36_Re: When was a Table last modified ?10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 6 Jun 2001 15:20:21 +0200522_us-ascii You can REPRO SYSLGRNX to a flat file and with a REXX you can detect the OBID (partition if partitioned) and timestamp of modifications, if present.

SYSLGRNX record description is in ' DB2 diagnosis guide and reference'





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. 16948 23 45_Re: Request for Java coded Connection Pooling13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Wed, 6 Jun 2001 09:21:20 -0400596_iso-8859-1 >if the web server provides the app code for connection pooling, >then I need not. >Else I must.

Joe, I think your syntax above may be the problem.

If you remove the period after "then I need not", I think it should compile cleanly.

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

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16972 105 14_DB2 DBA OS/39012_Confidential26_confidential@ECFACTORY.NET30_Wed, 6 Jun 2001 09:31:04 -0400519_iso-8859-1 I am currently looking for a person with IT experience i.e. DB2, DBA and OS/390 expertise for a position in Columbus Ohio. The opportunity offers a strong salary plus a nice sign on bonus plus full relo and a host of outstanding benefits. If you know of anyone who may be a good fit or if you want to know a little more detail yourself please reply or call and I would be happy to help in any way I can. We are not able to sponsor anyone with H1-b visa status. I can reward anyone with a referral bonus [...] 17078 85 18_Database holy war.15_Booysen Corneel26_Corneel.Booysen@SITA.CO.ZA30_Wed, 6 Jun 2001 15:36:52 +0200327_ISO-8859-1 Hi John,

thank you - I have the full PDF file of Brown and Associates. Makes for interesting reading.

Thank you for the information - this will help a lot.

By the way I got hold of the source of the "IBM choose Oracle over DB2 hype" - it is spread all over their home page www.oracle.com ! [...] 17164 110 46_Re: Unload float in readable format (Urgent !)12_Hall, Robert25_Robert.Hall@OCC.TREAS.GOV30_Wed, 6 Jun 2001 09:43:14 -0400551_iso-8859-1 Eric,

If you have an ODBC driver (like DB2 Connect) installed on your SQL Server, you can use DTS (a part of SQL Server) to create and transfer the table to SQL Server. We use DTS with DB2 Connect and in 98% of the cases DTS actually works very well. (We found a few quirks like SQL server will sometimes make a date column be a smalldate in SQL Server, which usually fails on download...but it is easy to fix in SQL server and re-download). And I know DTS handles the floating point conversion, because every one of our tables [...] 17275 51 45_Re: Performance Considerations for Log files.23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Wed, 6 Jun 2001 14:14:46 +0100299_- Hi At the end of the day the Oracle people are going say as everything and anything to keep you on oracle.

Don't listen to them or benchmarks .. benchmarks are, in my opinion, never a good indication of what product to buy as they do not mimic your companies exact processing profile. [...] 17327 226 23_Re: Clustering Tradeoff15_Richard Simpson20_rsimpson@AU1.IBM.COM30_Wed, 6 Jun 2001 16:11:57 +1000539_us-ascii Terry, Under what circumstances is the approach you have suggested better than list prefetch ? Provided the index provides all the filtering required, how does your suggeston improve performance ? It would appear to be doing effectively the same thing and I would expect it to provide better performance in the same circumstances List Prefetch would. i.e. Where more than 1 row appeared within a given prefetch quantity and the buffer pool was too small to be able to hold a previously referenced page till it was hit again. [...] 17554 38 24_High Log Write Wait Time9_Sally Mir22_Sally.Mir@WACHOVIA.COM30_Wed, 6 Jun 2001 10:08:41 -0400362_us-ascii Anyone have any ideas on what would cause high log write wait times? I have ruled out log buffer full conditions. FYI - the log buffer size is 5,000KB, and the write threshold is 200, and we use dual logging.

Here are some details:

Out of 5 and a half hours of DB2 elapsed time, 1 and a half hours can be attributed to log write I/O. [...] 17593 116 22_Re: Database holy war.23_Jain, Sanjay (Exchange)19_sanjayjain@BEAR.COM30_Wed, 6 Jun 2001 10:17:24 -0400488_iso-8859-1 If one goes to ORACLE web site. It comes across as a tabloid site. Sign of a desperate company that is loosing market.

Sanjay

/* my opinion only */



-----Original Message----- From: Booysen Corneel [mailto:Corneel.Booysen@SITA.CO.ZA] Sent: Wednesday, June 06, 2001 9:37 AM To: DB2-L@RYCI.COM Subject: Database holy war.



Hi John,

thank you - I have the full PDF file of Brown and Associates. Makes for interesting reading. [...] 17710 15 28_Re: High Log Write Wait Time0_26_truman.g.brown@VERIZON.COM30_Wed, 6 Jun 2001 10:23:39 -0400344_us-ascii Apropos of nothing at all, I think your write threshold is a bit high - try 60.

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. 17726 90 45_Re: Performance Considerations for Log files.15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 6 Jun 2001 16:25:51 +0200519_iso-8859-1 Leslie,

If you don't have the proper testing facilities benchmarks can be of some help, giving a direction to you. However I do agree they are not the BEST answer. Asking around, asking other customers helps a lot too.

Kind regards,

Jaap





-----Oorspronkelijk bericht----- Van: Pendlebury-Bowe, Leslie [mailto:Leslie.Pendlebury-Bowe@EU.SONY.COM] Verzonden: woensdag 6 juni 2001 15:15 Aan: DB2-L@RYCI.COM Onderwerp: Re: Performance Considerations for Log files. [...] 17817 63 25_Re: AW: Database holy war23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Wed, 6 Jun 2001 15:22:00 +0100345_- I'm behind Max on this ..... I have a career and experience in all things DB2 ... I sleep easy in my bed at night knowing it is looking after my users data.

Les

-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: Wednesday, June 06, 2001 2:05 PM To: DB2-L@RYCI.COM Subject: Re: AW: Database holy war [...] 17881 184 45_Re: Request for Java coded Connection Pooling11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Wed, 6 Jun 2001 10:51:31 -0400356_us-ascii Thanks, Myron. My problem is that we have only one license for DB2 Connect EE, residing on a Solaris platform. I hesitate to tell the web team to have a web app running on a different platform use a three tiered solution, instead of two tiered. So, my question revolves around either I) Solaris1 web app Solaris2 DB2 Connect IBM OS390 DB2 V6 [...] 18066 16 28_Re: High Log Write Wait Time10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 6 Jun 2001 16:50:44 +0200388_us-ascii Hi Sally is correct 200 as write threshold ? The recommended value is 20....Is there any WAIT FOR LOG BUFFERS ?

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. 18083 63 27_Re: igh Log Write Wait Time14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM30_Wed, 6 Jun 2001 10:50:32 -0400374_ISO-8859-1 Sally, Lower your write threshold to 20 (the old defauylt). This threshold is the +++Number of Buffers+++ where asynch write is triggered..... and your current number is so high that you have dis-abled much of the Asynch write. When increasing the size of the Log buffer, I generally recommend leaving the write threshold at the default of 20. Regards, Joel [...] 18147 21 25_Re: AW: Database holy war10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 6 Jun 2001 16:53:03 +0200527_us-ascii Hi Leslie !! How is it ?

For me it's the same, I sleep easy in my bed, too....I'm not sure that this happens to my ex-collegues working with

ORACLE..Replication every night has problems, never ending backups, problems with tape media...

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. 18169 90 28_Re: High Log Write Wait Time11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 6 Jun 2001 07:53:38 -0700278_us-ascii Hi, The programs has to wait while all the active logs are full and DB2 is waiting for them to be archived. If archival media is TAPE/CART, then it will issue a mount request and operator has to mount the CART and then DB2 will start archiving into the cartridge. [...] 18260 41 23_Re: BMC change manager.11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 6 Jun 2001 07:59:09 -0700649_us-ascii Hi, We dont use BMC change manager in our shop. We had lot of sysbase stored procedures which we have converted into DB2 using IBM's SP builder.



Regards, Bikash

--- "Philip, Sibimon" wrote: > Does BMC change manager has a facility to compare and migrate DB2 > stored > procedure definition between two subsystems. We use CHANGE MANAGER > 6.2.01. I > could not see this in the compare. How do other companies doing > this? > > Thanks..Sibi > > ================================================ > To change your subscription options or to cancel your subscription > visit the DB2-L webpage at [...] 18302 165 22_Re: Database holy war.13_Larry Hubbard29_Larry_Hubbard@CMS.STATE.IL.US30_Wed, 6 Jun 2001 10:00:34 -0500515_us-ascii I would also give heavy consideration to the optimizer technology behind the products being examined. IBM chose to pursue development of a cost based approach to access path selection from the beginning (early 1980's) while others chose a rule based approach. Even though the early years were interesting at times, IBM now has the techniques in place, including the query rewrite. Other vendors, including the big O, now are going in that direction. Like they say, there's no flattery like imitation! [...] 18468 77 22_Re: Database holy war.12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Wed, 6 Jun 2001 09:58:00 -0400572_- We have both. Ask Oracle what tools do they have running on OS390.

Consider: Support. In my experience, It doesn't get any better than IBM. For example in one instance I called Oracle support on the day after Thanksgiving and they were closed because of the Holiday. This is just one example. With DB2, You are dealing with the same vendor when you have a problem. DB2 Product: Hardware, OS/390 and DB2 developers/designers work together to write DB2 code to make a better product taking advantage of both new hardware and new operating system enhancements. [...] 18546 112 82_Re: Check Data Utility / Exception Table Error With Identity Colu mn In Base Table13_DARRELL HONEA14_b06dfh@FDS.COM30_Wed, 6 Jun 2001 11:16:53 -0400486_us-ascii Shaun,

Thank you for the advice. That worked!

Thanks!

Darrell F. Honea IBM Certified Solutions Expert - DB2 UDB Database Administration for OS/390 Architecture/Database/DB2 Federated Systems Group 678-474-2062





"LOMBARD,Shaun" cc: Sent by: DB2 Data Subject: Re: Check Data Utility / Exception Base Discussion Table Error With Identity Colu mn In Base Table List [...] 18659 44 9_Re: DEC3113_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Wed, 6 Jun 2001 10:36:48 -0500355_iso-8859-1 Hi, I changed over to DEC31 when we introduced PeopleSoft about 1 year ago. We experienced no problems, but we only had a few other applications running in DB2. Shops with many applications and more throughput may have more opportunity to hit a problem.

There is a Special Register that you can set as opposed to the global change. [...] 18704 81 27_Re: igh Log Write Wait Time9_Sally Mir22_Sally.Mir@WACHOVIA.COM30_Wed, 6 Jun 2001 11:41:27 -0400493_us-ascii Thank you Joel, you're the best. I'll take this to our sysprogs and let you all know how much difference it makes.

Regards,

Sally Mir







Joel Goldstein @RYCI.COM> on 06/06/2001 10:50:32 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: igh Log Write Wait Time [...] 18786 231 22_Re: Database holy war.15_Zander, Barb J.19_bzander@STATE.ND.US30_Wed, 6 Jun 2001 10:45:24 -0500385_iso-8859-1 Corneel, I agree with Bob Oracle support on OS390 needs improvement. I have had several problems that have either taken forever to fix, I have finally bumped into a solution myself(which support says shouldn't fix the problem), or some just never do get fixed have to live with them. I am told that support on other platforms is better. No experience with this myself. [...] 19018 22 22_Size of a UDB database10_Mathai Joy22_mathaijoydb2@YAHOO.COM30_Wed, 6 Jun 2001 08:37:00 -0700540_us-ascii Can anybody tell me how to find out the total size of a UDB Database 7.1 in NT server.

Thanks,

Mathai

__________________________________________________ Do You Yahoo!? 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. 19041 23 22_Size of a UDB database10_Mathai Joy22_mathaijoydb2@YAHOO.COM30_Wed, 6 Jun 2001 08:49:22 -0700589_us-ascii Can anybody tell, how to find out the size of a UDB database. I have to find out the size of a database which is residing in NT server.

Thanks,

Mathai

__________________________________________________ Do You Yahoo!? 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. 19065 237 31_DB2 Connect Configuration issue12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 6 Jun 2001 08:58:22 -0700295_us-ascii Joe, I suspect from a licensing point of view you're going to be required to either go to the Solaris DB2 Connect box or purchase DB2 connect for the other box. The CAE or runtime component will have to go to a DB2 connect EE box somewhere. It cannot go to the mainframe directly. [...] 19303 110 25_Outer join understanding.15_Smith, Dennis J22_dennis.j.smith@EDS.COM30_Wed, 6 Jun 2001 11:01:15 -0500628_iso-8859-1 Hello group!

I got a question about outer joins, that I am not sure I understand completely. Here is some sample data, all columns are char data:

PNODE SNODE TAG STATUS TIME DSN3 DSN4 DSNTAG2 END 2001-02-20-11.00.00.000000 DSN3 DSNTAG2 NEW 2001-02-20-11.00.00.000000 DSN1 DSNTAG NEW 2001-02-20-11.00.00.000000

This table is like a process table where there will be a line for every handling of the record. The tag will be unique for a group of related records. Status is used to show the beginning and ending of the group. The SQL needs to get the beginning and ending records for the group [...] 19414 50 42_OS/390 V5 - NEAROFFPOS / FAROFFPOS & REORG10_Joan Green22_Joan.Green@BOIMAIL.COM30_Wed, 6 Jun 2001 17:15:50 +0100328_us-ascii Jeff, Isaac, Rick,

To answer the various questions.

1) No data compression 2) The small table was changed using drop/create rather than alter 3) Three of the tables have VARCHAR fields (at the end of the rows!) 4) LEAFDIST is 0 on all but one of the small tables where it is 150 on the primary index [...] 19465 21 22_Re: Database holy war.0_22_Rohn.Solecki@MTS.MB.CA30_Wed, 6 Jun 2001 11:20:44 -0500552_us-ascii Go to the TPC website and read the details of that benchmark, find out what the full context of the test was. Since it was a test of new hardware, it could be that AT THAT POINT IN TIME, DB2 did not have a version that was optimized to take best advantage of the hardware. The TPC benchmarks are supposed to simulate real world conditions. To do that they have defined a lot of very specific conditions for the test to be considered valid. Before the test is accepted as a new benchmark any 'interested parties', ie the competition, look [...] 19487 18 22_Re: Database holy war.13_Mohammad Khan20_mkkhan88@HOTMAIL.COM30_Wed, 6 Jun 2001 11:02:59 -0500382_- On Wed, 6 Jun 2001 10:17:24 -0400, Jain, Sanjay (Exchange) wrote:

>If one goes to ORACLE web site. It comes across as a tabloid site. Sign of >a desperate company that is loosing market. >

I understand all that but I'd still like IBM to either accept or deny the allegation ( with possibly their side of the story ). Anyone ? Mr. Miller ? [...] 19506 17 0_16_Redondo, Luis J.23_LJRedond@SOUTHERNCO.COM30_Wed, 6 Jun 2001 11:47:27 -0500405_- Specific questions relating to triggers in DB2 V6:

Is it possible for a DB2 trigger to pass data and initiate a CICS transaction?



Thank 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. 19524 20 25_Restore a database to new10_Mathai Joy22_mathaijoydb2@YAHOO.COM30_Wed, 6 Jun 2001 09:51:11 -0700399_us-ascii When I restore a UDB database to a new database, restoring process went well. However, the NT server which database resides is having problem to connect to the applications. Any suggestions will be appreciated.

__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ [...] 19545 41 22_Re: Database holy war.13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Wed, 6 Jun 2001 11:55:38 -0500298_iso-8859-1 Even though there appears to be negatives about Oracle etc. posted on this group, I do hope Oracle, SQLServer and other stay around and provide competition. Not too long ago IBM had no or little competition. Now things have changed and we, the consumer, are much better off for it. [...] 19587 146 29_Re: Outer join understanding.11_David Nance16_DWNance@FHSC.COM30_Wed, 6 Jun 2001 12:58:11 -0400400_US-ASCII Dennis, Your ex4 may not always give you just what you want, you may see interim status(other than new and end). I would say try the following, I think it will give you the results you need. SELECT A.PNODE, A.TAG, B.SNODE FROM T1.T1_TABLE A LEFT OUTER JOIN T1.T1_TABLE B ON A.TAG = B.TAG AND B.STATUS = 'END' -- WAS IN WRONG PLACE BEFORE WHERE A.STATUS = 'NEW' AND A.TAG LIKE 'DSNTAG%' [...] 19734 57 31_Re: TABLE QUALIFIER - WEBSPHERE13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Wed, 6 Jun 2001 12:10:44 -0500410_iso-8859-1 Consider using the SET CURRENT SQLID=qualifier. It has some ramifications such that the authority also means that the application/userid can actually drop the table and other owner privilege type things.

Otherwise, you can get your program to add the qualifier to the table names as it "submits" the SQL. You can have that in an INI type file and change it without program modification. [...] 19792 46 29_Re: Restore a database to new17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM30_Wed, 6 Jun 2001 12:12:47 -0500460_us-ascii What kind of problems? Can you give specifics examples? Are your users getting error messages?

Jeremy Schleicher May Company





From: Mathai Joy @RYCI.COM> on 06/06/2001 11:51 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Fax to: Subject: Restore a database to new [...] 19839 118 70_Repost: New Book: "Exploring IBM's e-business Software" by Casey Young13_Morrill, John12_JohnM@VP.NET30_Wed, 6 Jun 2001 11:26:33 -0600393_- Dear DB2 Interested Parties,

A valuable new book just hit the bookselves, "Exploring IBM's e-business Software" by Casey Young (Maximum Press (www.maxpress.com) May 25, 2001, softcover/$49.95, ISBN: 1-885068-58-1v). It is available now from the publisher at http://www.maxpress.com/catalog/ibmebus.html or from your favorite online bookseller.

Regards, Mike + + + + + + + [...] 19958 21 74_Missing Shadow Copy Datasets in Online Index Reorganization - DB2 V6/OS39011_Randy Scott19_Randy_Scott@VFC.COM30_Wed, 6 Jun 2001 13:26:26 -0400451_us-ascii I recently ran an index reorganization with SHRLEVEL CHANGE and during the SWITCH PHASE received an error message saying that three VSAM datasets from the shadow copy of the index did not exist. The exact message was DSNU296I and it identified two DSNDBC and one DSNDBD VSAM datasets as missing during the SWITCH PHASE. The index was left in status 'RW,RBDP' which meant I had to REBUILD the index, which was almost 200 million entries. [...] 19980 49 67_Last chance sign up for Purcell's Advanced SQL Dallas & Little Rock12_Jorge Rivera21_bpisols@BELLSOUTH.NET30_Wed, 6 Jun 2001 13:47:00 -0400397_iso-8859-1 This class is offered next week in Dallas )Jun 11-12 and Little Rock (Jun 13-14)and if you mention that you saw this on the DB2-L list, you will receive $50 off the regularly priced $300 class.

Real World SQL: Advanced Coding and Performance Tuning - By Terry Purcell Platform: DB2 Family Audience: DBAs, Application Programmers, and anyone with a thirst for SQL knowledge. [...] 20030 77 29_Re: Restore a database to new10_Mathai Joy22_mathaijoydb2@YAHOO.COM30_Wed, 6 Jun 2001 10:51:43 -0700638_us-ascii Jeremy,

Thanks for responding. The NT server simply frozen while we acccess tables from both databases. Any suggestion where to procced to fix this problem. Thanks mathai --- Jeremy Schleicher wrote: > What kind of problems? Can you give specifics > examples? Are your users > getting error messages? > > Jeremy Schleicher > May Company > > > > From: Mathai Joy @RYCI.COM> > on 06/06/2001 11:51 AM > > Please respond to DB2 Data Base Discussion List > > > Sent by: DB2 Data Base Discussion List > > > > To: DB2-L@RYCI.COM > cc: [...] 20108 34 54_Forward: Restore DB to new and NT server is going down13_Morrill, John12_JohnM@VP.NET30_Wed, 6 Jun 2001 12:07:39 -0600499_- This message has been forwarded from db2-l-request to db2-l.

-----Original Message----- From: Mathai Joy [mailto:mathaijoydb2@yahoo.com] Sent: Wednesday, June 06, 2001 11:35 AM To: DB2-L-request@RYCI.COM Subject: Restore DB to new and NT server is going down



I have a UDB database in NT server. I restored this database to new. The restore went with out any error. However, NT server is not able to connect the application. Can anybody give me an idea where to proceed. [...] 20143 61 80_Time Correction: DB2 Universal Database Performance Tuning Fundam entals Webcast13_Morrill, John12_JohnM@VP.NET30_Wed, 6 Jun 2001 12:14:27 -0600374_iso-8859-1 The correct time for this Webcast is 11:00am-12:30pm EDT on June 13.

This message is a repost from the DB2 Interested Parties. For more information about the DB2 Interested Parties mail list, contact:

Mike Swift IBM Silicon Valley Lab - 555 Bailey Avenue - San Jose, CA 95141 Phone: 408 463-4105, Fax: 408 463-4633 Internet: dbswift@us.ibm.com [...] 20205 52 58_Re: Forward: Restore DB to new and NT server is going down11_Jakobs, Jef18_JJakobs@HARTIC.COM30_Wed, 6 Jun 2001 13:15:41 -0500424_iso-8859-1 Hard to comment without more detailed info, but my guess would be that your database is in a "rollforward pending" state...

If that's so, then either take it out of that state, or apply logs.

Jef Jakobs -----Original Message----- From: Morrill, John [mailto:JohnM@VP.NET] Sent: Wednesday, June 06, 2001 1:08 PM To: DB2-L@RYCI.COM Subject: Forward: Restore DB to new and NT server is going down [...] 20258 85 35_Re: DB2 Connect Configuration issue11_Joe Luthman22_jluthma@BGNET.BGSU.EDU30_Wed, 6 Jun 2001 14:50:07 -0400446_us-ascii Myron, First time I've heard this. CAE, runtime/appdev client as it's called now, CANNOT GO TO OS390 DB2 V6 directly? Hmmmmm...... This could be a PR problem at our place, as I'm having to withstand constant comparisons to the O product. I'm really confused now. There are developers here who will expect that a web app should be able to directly connect to DB2 V6 for OS390. I had thought the appdev client would enable that. -joe [...] 20344 20 26_DB2 stored procedure again15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Wed, 6 Jun 2001 14:45:17 -0400311_iso-8859-1 Finally we could call DB2 stored procedure on OS/390 using VC++ and ADO. DB2 version is 6.0. DB2 stored procedure has parameter style as GENERAL WITH NULLS. We are unable to pass NULL to a input variable from VC++ and ADO. If anybody has any idea please let me know. An example will a big help. [...] 20365 66 23_Re: BMC change manager.15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Wed, 6 Jun 2001 14:51:41 -0400510_- I was looking for Stored procedure definition migration on OS/390. For example I added a new input parameter to a stored procedure and I want to migrate this changes to production. Now we need to Drop and re-create the stored procedure definition. I think we cannot drop if a trigger is calling that stored procedure, so we have to drop the trigger, then add the definition and grant all the authorizations and then re-create all the triggers and then re-bind all the stored procedure calling programs. [...] 20432 53 30_Re: DB2 stored procedure again12_Gregg Sawyer26_gregg.a.sawyer@VERIZON.COM30_Wed, 6 Jun 2001 15:13:36 -0400428_us-ascii Philip, If I understand you, you are looking for examples of calling a DB2 SP from a VC++/ADO program, passing NULL as the value for some of the SP input parameters. Can't help you there directly, but... Would it be too simple (if clunky) to define the SP parameter style as "GENERAL" only, and code the SP to handle space/zero instead of null? This may not be a long-term solution, but it will get you running... [...] 20486 147 35_Re: DB2 Connect Configuration issue12_tim malamphy20_timalamphy@YAHOO.COM30_Wed, 6 Jun 2001 12:19:29 -0700467_us-ascii With CAE (V6 for sure, and I think with V5 as well), on windows,you can connect directly TO a V5 or above OS390 DB2 system, via DRDA, and AIX UDBs as well. I believe that DB2 Connect was needed in the older versions for the 2 phase commit support, or from connecting FROM OS/390 TO a windows CAE machine. You need to have DDF configured for TCP/IP to do it, and not what was standard, VTAM. Configuring the older versions were a lot more difficult too. [...] 20634 73 30_Re: DB2 stored procedure again15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Wed, 6 Jun 2001 15:35:42 -0400376_- This make more coding in Stored procedure. Also in some case 0 may be a valid value for a integer/smallint input parameter. Checking for space in Alphanumeric variable is O.K. Also We have around 200 input variables. We could solve the problem using some variant native data type. I have no idea what it means in VC++. If anybody need more info on this, I can send it. [...] 20708 42 26_Re: Size of a UDB database18_Gert van der Kooij15_geko@WANADOO.NL30_Wed, 6 Jun 2001 21:37:03 +0200653_iso-8859-1 Hi, You can enter the 'list tablespaces show detail' command. It will show you all tablespaces , their pagesize and the number of pages allocated.

----- Original Message ----- From: "Mathai Joy" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, June 06, 2001 5:49 PM Subject: Size of a UDB database



> Can anybody tell, how to find out the size of a UDB > database. I have to find out the size of a database > which is residing in NT server. > > Thanks, > > Mathai > > __________________________________________________ > Do You Yahoo!? > Get personalized email addresses [...] 20751 157 29_Re: Outer join understanding.15_Smith, Dennis J22_dennis.j.smith@EDS.COM30_Wed, 6 Jun 2001 14:40:52 -0500359_iso-8859-1 David;

Thanks for your answer. Under version 5, this is not allowed. Under version 6 this is allowed. The production system should be there by that time. I guess I was testing under V5, I should have check it under V6. However, I guess I was right, that the Join is done first, then where predicates is done second. Kind of misleading. [...] 20909 66 21_DB2 OS/390 AR problem17_Alessandro Brezzi28_brezzi.isac@INTERBUSINESS.IT30_Wed, 6 Jun 2001 21:41:48 +0200287_us-ascii Hi all, we need to use DB OS/390 V5R2M0 as an ApplReq. in a distr env; the subsystem is already configured as an AS in the same env, and all work just fine. I setup the CDB entry, and tried to bind all the packages and plan for use with a remote WinNT 4sp6 / DB2 UDB V6.2. [...] 20976 67 22_Re: Database holy war.15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Wed, 6 Jun 2001 15:49:30 -0400318_iso-8859-1 I do think Oracle and SQLserver forced IBM to make many changes in DB2. Lot of thing still pending in DB2. May be performance and robustness may be very good in DB2, but in MSSQLserver, coding is very easy. Now a days companies want to do thing is half an hour or one hour not like in one or two days. [...] 21044 233 29_Re: Outer join understanding.0_24_db46@DAIMLERCHRYSLER.COM30_Wed, 6 Jun 2001 16:10:24 -0400334_us-ascii The reason you only got one row in EX3 is because you are putting predicates for the table you are doing an outer join on in the where clause (where else). DB2 likes to do the outer joins first because they will work on the result set after. Which means that there can be values associated with table 'B' that are NULL. [...] 21278 66 67_Re: Is IMS intrinsically better than DB2 for hosting XML document s13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Wed, 6 Jun 2001 15:23:48 -0500599_iso-8859-1 Hi,

Well, i will comment.

Anecdotally, a hierarchical DBMS may seem an option, because XML is hierarchical, but since RDBM's facilitate open systems, then I would stay with RDBMS. Other applications and legacy will want to get at tables directly via SQL and other means. I suspect that no data would be accessed solely for XML processing, you will still have to cater for other uses. End user tools, reporting etc. RDBMS offer lots of flexibility, and who knows, what happens if XML changes or is replaced by a new direction later and you managed to stick your data in [...] 21345 170 29_Re: Outer join understanding.12_Gregg Sawyer26_gregg.a.sawyer@VERIZON.COM30_Wed, 6 Jun 2001 16:35:03 -0400424_us-ascii Questions like this make me ponder whether the root cause of the problem lies elsewhere...

Forgive my presumption, but this smacks of a design developed for an hierarchical database (e.g. IMS) imposed on relational database (i.e. DB2); maybe the solution is to code the application to apply udpates to a single record for each "handling", rather than trying to coalesce handling history after the fact. [...] 21516 17 30_Re: DB2 Connect and ColdFusion10_Rob Wright17_rwright@LIC.CO.NZ30_Thu, 7 Jun 2001 08:36:05 +1200320_us-ascii Thanks to all on their input on this. The bottom line is don't believe everything you read. Although ColdFusion documents that it supports DB2 Native driver communication in the CF 4.5.2 documentation, this is not the case. It is my understanding that CF V5 addresses this ..... I await with bated breath. [...] 21534 17 40_Information on psuedo deleted/punc rows.13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Wed, 6 Jun 2001 15:51:18 -0500660_iso-8859-1 I'm looking for some information on psuedo-deleted rows and possibly-uncommitted data. I've searched the archives and the manuals, and so far the only real detail I've found is in the DB2 MVS locking redbook, which was written for DB2 V4. Does anyone know of some documentation that's a little more more recent? The thing I'm really after is that I'm researching a contention issue in which processes accessing different data are timing out on each other. These are processes accessing a very small table that is setup as locksize=page, lockmax=0, maxrows=1 (running with cursor stability, acquire(use) release(commit)). Everything I've looked [...] 21552 55 76_Re: Callable Services Module - CSRCE - cpu increase under DB2 V6 .1 (REPOST)14_Vivoda, Robert30_Robert.Vivoda@TEAM.TELSTRA.COM30_Thu, 7 Jun 2001 07:37:15 +1000527_- > Dear DB2 List members, > > Could I please have your advice on the following. > > In an IMS MPP Region after migrating to DB2 Version 6.1 we are seeing cpu > time for transactions being attributed to module CSRCE (Callable > Services), where we did not see this prior to V6. We believe that CSRCE is > compression module. > > Is DB2 V6.1 handling compression differently now, or attributing some of > the cost back to the allied address space? > > I have searched IBMLINK and found nothing and now I am asking the list [...] 21608 89 29_Re: Outer join understanding.15_Smith, Dennis J22_dennis.j.smith@EDS.COM30_Wed, 6 Jun 2001 17:33:37 -0500574_iso-8859-1 Well I am not sure how to answer your question. However, this table will track a data file movement through our system (well there system). They need to keep track of each handling of the file as it moves thru the system, and also keep track of history.

I guess one can have several columns that only gets updated by each process, thus creating a fat record. However, several fields would be repeated, like last update, process name and so on (in my samples I eliminated columns that did not pertain to the access issue). Which equates to a repeating [...] 21698 55 27_Re: Unexpected SQLCODE -80514_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 6 Jun 2001 17:21:30 -0500372_- Eric,

I suspect the friendly person handling the ETR thinks that CURRENT PACKAGESET is non-blank - because that's the only reason that I could see for having the collection name in the -805 tokens.

Being rather suspicious of this sort of thing, I would actually obtain the value of CURRENT PACKAGESET after the -805 to verify that it really is blank. [...] 21754 110 22_Re: Database holy war.15_Richard Simpson20_rsimpson@AU1.IBM.COM30_Thu, 7 Jun 2001 09:22:47 +1000468_us-ascii Given who I am working for at the moment, and neither being in Marketing nor DB2 development, I do not really want to comment on the relative merits of Oracle to DB2 on the OS/390 platform.

I will point out a few things however.

1) I can't find these reults on the TCP website. There is one similar (same transaction rate, 680 serier processor for example, Oracle) but the cost per transaction is US$45.30 not the $35.73 Oracle is quoting. [...] 21865 18 78_Re: Missing Shadow Copy Datasets in Online Index Reorganization - DB2 V6/OS39010_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 7 Jun 2001 02:05:14 +0200325_us-ascii Hi Randy....

Did you check the APAR PQ43522 ?

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. 21884 19 26_XML data managemnet system16_Jeffrey I Condon19_jicondon@US.IBM.COM30_Wed, 6 Jun 2001 16:56:44 -0700608_us-ascii Just up on IBM alphaWorks.

XML Registry/Repository ------------------------------------- The IBM XML Registry/Repository (XRR) is a data management system that manages and provides services for XML artifacts including schemes (DTD, XSD), stylesheets (XSL) and instance documents (WSDL). http://www.alphaworks.ibm.com/tech/xrr?open&l=db2ml,t=grx,p=xrr

================================================ 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. 21904 22 14_Re: [CODEPAGE]8_duam lee16_duam_lee@USA.NET28_Wed, 6 Jun 2001 18:15:37 MDT560_US-ASCII Hi All I was away for long. Does any body knows the answer for this question. LIke the question was HOW to convert CODEPAGE of DB2.

Thanks in Advance. DUAM

____________________________________________________________________ Get free email and a permanent address at http://www.netaddress.com/?N=1

===============================================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. 21927 48 0_14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Wed, 6 Jun 2001 19:23:42 -0500405_iso-8859-1 OS/390? Yes - call a Stored Procedure, which uses RRSAF, and then EXCI, which invoked a CICS trans without being CICS enabled, under control of WLM...

Theory but never tried but it is supposed to work.

Regards, Richard +-----+-----+-----+-----+-----+ Richard_Yevich@YLAssoc.com YL&A - http://www.YLAssoc.com DB2 and Sysplex Gold Consultant Certified DB2 DBA for OS/390, z/OS [...] 21976 83 25_Re: AW: Database holy war14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Wed, 6 Jun 2001 19:23:42 -0500467_Windows-1252 Before anyone gets angry, there is both truth and humor in this reply.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Blumenthal, Gary > > 1. Database manufacturers are like automobile manufacturers. When a new > slick feature is added by Ford, you can bet that in the next model year GM > will have the same thing. The same is true with databases. Same > features, different syntax. [...] 22060 66 24_Re: calling CICS from SP12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 6 Jun 2001 19:35:19 -0700613_us-ascii My client has actually done it. Works great. Little bit of effort to get it setup initially. But after that, it works like a charm and is pretty efficient. --- Richard Yevich wrote: > OS/390? Yes - call a Stored Procedure, which uses RRSAF, and then > EXCI, > which invoked a CICS trans without being CICS enabled, under control > of > WLM... > > Theory but never tried but it is supposed to work. > > Regards, > Richard > +-----+-----+-----+-----+-----+ > Richard_Yevich@YLAssoc.com > YL&A - http://www.YLAssoc.com > DB2 and Sysplex Gold Consultant > Certified DB2 DBA [...] 22127 17 29_DB2 DBA for OS/390 job wanted3_J S21_pleniumcorp@YAHOO.COM30_Wed, 6 Jun 2001 21:35:18 -0500310_- Hello List,

I need some information about the best way to search for a DB2 DBA for OS/390 job either contract or permanent. If you know of some useful websites other than dice.com or computerjobs.com that you have recently used to successful find a job, please let me know. Thanks for your help. [...] 22145 37 33_Re: DB2 DBA for OS/390 job wanted13_Murali Kanaga19_murali@EPIPHANY.COM30_Wed, 6 Jun 2001 20:23:09 -0700595_iso-8859-1 try db2jobs.com

> -----Original Message----- > From: J S [mailto:pleniumcorp@YAHOO.COM] > Sent: Wednesday, June 06, 2001 7:35 PM > To: DB2-L@RYCI.COM > Subject: DB2 DBA for OS/390 job wanted > > > Hello List, > > I need some information about the best way to search for a DB2 DBA for > OS/390 job either contract or permanent. If you know of some useful > websites other than dice.com or computerjobs.com that you > have recently > used to successful find a job, please let me know. Thanks for > your help. > > JS > > ================================================ > To [...] 22183 22 26_Triggering Emails from JCL8_K.Balaji19_K.Balaji@TARGET.COM30_Wed, 6 Jun 2001 23:40:03 -0500303_iso-8859-1 Hi all,

How can we trigger a email message from JCL or from front end ASP.

This may not be right platform to ask this question but since I know many of you would have done some thing related to this I am putting this question here.

Thanks Balaji Ph # (510) 727-3259 [...] 22206 179 23_Re: Clustering Tradeoff13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 6 Jun 2001 23:46:29 -0500348_us-ascii John/Richard,

Yes it is as Sanjeev said, the index-only access is only on the local predicate column and the clustering index join column(s). This allows a search on the WHERE clause predicate as index-only, and access to the data via the clustering index. All selected columns must refer to the second reference to the table. [...] 22386 26 21_DB2 Replication Error13_John Glorious26_john@MITRAINFOSARANA.CO.ID30_Thu, 7 Jun 2001 12:19:06 -0700440_us-ascii Hi All,

We have a problem with DB2 Replication (we are using db2 ver. 5.0). When we were running db2 replication there was a message error below :

*** SQL ERROR ***: SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because they would produce duplicate rows for a table with a primary key, unique constraint, or unique index. [...] 22413 109 25_Re: DB2 OS/390 AR problem11_Roland Chua18_rolandchua@SGX.COM30_Thu, 7 Jun 2001 13:40:02 +0800683_us-ascii Try adding "sqlerror(continue)" in your bind package. I hope this will help.

Regards Roland





Alessandro Brezzi cc: Sent by: DB2 Data Subject: DB2 OS/390 AR problem Base Discussion List



06/07/01 03:41 AM Please respond to DB2 Data Base Discussion List











Hi all, we need to use DB OS/390 V5R2M0 as an ApplReq. in a distr env; the subsystem is already configured as an AS in the same env, and all work just fine. I setup the CDB entry, and tried to bind all the packages and plan for use with a remote WinNT 4sp6 / [...] 22523 43 30_Re: Triggering Emails from JCL0_25_john.halloran@SGIO.COM.AU30_Thu, 7 Jun 2001 16:30:25 +1030694_us-ascii I have had success using the jcl below to send emails

//IEBGENER EXEC PGM=IEBGENER //SYSIN DD DUMMY //SYSPRINT DD DUMMY,SYSOUT=* //SYSUT2 DD SYSOUT=A,DEST=SMTP,DCB=(LRECL=80,RECFM=F) //SYSUT1 DD * HELO SGIONJE MAIL FROM: RCPT TO: DATA FROM: JLH'S BATCH JOB SUBJECT: IT WORKS !! SOME MESSAGE TEXT MORE MESSAGE /* //*







********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. [...] 22567 15 41_Don Lee/Boulder/IBM is out of the office.7_Don Lee16_dblee@US.IBM.COM30_Thu, 7 Jun 2001 00:35:37 -0600394_us-ascii I will be out of the office starting June 7, 2001 and will not return until June 18, 2001.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 22583 80 30_Re: Triggering Emails from JCL0_24_ujjwal@LOT.TATASTEEL.COM30_Thu, 7 Jun 2001 12:14:07 +0530426_us-ascii Hi John , Can u send the JCL which sends in the form of attachment to LOTUS NOTES also ?. Thanx in Anticipation .

With Regards Ujjal Bhattacharya







john.halloran@S GIO.COM.AU To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Re: Triggering Emails from JCL Discussion List



06/07/01 11:30 AM Please respond to DB2 Data Base Discussion List [...] 22664 42 30_Re: Triggering Emails from JCL16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 7 Jun 2001 12:26:37 +0530294_- If you have SMTP for OS/390 installed you can do this. We used this to track the auto schedular job for failures and send mails to respective teams and sometimes we escalate the problems by sending mails to higher authorities as well (not a good thing to do :-)).

Regards Sanjeev [...] 22707 26 19_Session Information13_Swamy, Ritesh30_Ritesh.Swamy@BLR.HPSGLOBAL.COM30_Thu, 7 Jun 2001 12:05:06 +0500630_- Helllo list members,

I want to retrieve and use some information regarding the session inside stored procedures and functions. I wanted to how I could obtain this information. I am using DB2 7.1 on NT. Could anyone please provide some information on this issue.

regds, RiteshWARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on [...] 22734 20 11_CA-Infopump15_CVILM-Explo-DBS27_CVILM-Explo-DBS@ARMY.MIL.BE30_Thu, 7 Jun 2001 08:55:51 +0200512_iso-8859-1 Hi List,

Who has experiences in using CA-Infopump ? According to CA-support the product is not capable of transferring null values to DB2 on OS390 Who has had a similar problem ? Can anyone give me a piece of advice for workarounds ?

Johan Oeyen

================================================ 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. 22755 119 22_Re: Database holy war.15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Thu, 7 Jun 2001 09:18:54 +0200437_iso-8859-1 Sibi, For instance; Oracle 9i has a new SQL statement introduced; UPSERT which is a combination of insert and update (updates if the row is already there, inserts if not). I guess DB2 will follow this lead.

Kind regards,

Jaap Slot

-----Original Message----- From: Philip, Sibimon [mailto:SPhilip@CSXLINES.COM] Sent: Wednesday, June 06, 2001 9:50 PM To: DB2-L@RYCI.COM Subject: Re: Database holy war. [...] 22875 39 78_Re: Missing Shadow Copy Datasets in Online Index Reorganization - DB2 V6/OS3900_18_mebert@AMADEUS.NET30_Thu, 7 Jun 2001 09:28:12 +0200574_us-ascii We've recently had lots of problems with abending OLRs as well (S0C7 mostly; recently a "dataset in use during SWITCH" on a partitioning index as well). They're TS OLRs, DB2 V5, but during my APAR research I encountered lots of PTFs having to to with shadow datasets, and some brand-new PTFs for V6 OLR as well (May 01) - you should check whether you've got all of them. They were not for really subtle problems... e.g. V6 OLR inline copies are unusable for logonly recovery because OLR places a wrong RBA in SYSCOPY. We increasingly have the feeling that all [...] 22915 18 8_UPSERT ?12_Peter, Georg15_G.Peter@DZBW.DE30_Thu, 7 Jun 2001 09:43:22 +0200361_iso-8859-1 Jaap,

just kidding or really true ?

With kind regards - mit freundlichen Grüssen, Georg H. Peter

===============================================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. 22934 108 30_Re: Triggering Emails from JCL16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 7 Jun 2001 13:25:55 +0530414_- Ujjwal,

It has nothing to do with whether your mail is in lotus notes or outlook. It specifies simply the e-mail id and sends the e-mails. It is done by SMTP only. As i mentioned in my previous mail, i (with the help of three of my collegue, champion Comp. Science experts of OS390) implemented the same in the shop you are working now. I can let you know the details once i will be there next time. [...] 23043 57 12_Re: UPSERT ?15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Thu, 7 Jun 2001 10:06:10 +0200433_iso-8859-1 Georg,

It is told to me as being true. And why not, it is a very nice statement. (A bit like MOD in JCL).

Kind regards,

Jaap

-----Original Message----- From: Peter, Georg [mailto:G.Peter@DZBW.DE] Sent: Thursday, June 07, 2001 9:43 AM To: DB2-L@RYCI.COM Subject: UPSERT ?

Jaap,

just kidding or really true ?

With kind regards - mit freundlichen Grüssen, Georg H. Peter [...] 23101 15 30_Re: Triggering Emails from JCL0_24_ujjwal@LOT.TATASTEEL.COM30_Thu, 7 Jun 2001 13:37:24 +0530299_us-ascii ThanK You Champ ( Sanjeev).

Regards Ujjal

================================================ 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. 23117 153 22_Re: Database holy war.23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Thu, 7 Jun 2001 09:15:41 +0100535_iso-8859-1 come on now we're starting to through mine functionality is better than yours into the equation.

let's get off that track before we start.

both have merits on functionality, both have good and bad points, however I feel my career is better off with DB2 because "gut feel" it is a better, all-round more stable and enjoyable product to work with. (oh and the last time it crashed and burned at my shop was over 12 months ago and that was no fault of it's own - when you consider our size that's excellent) [...] 23271 49 42_OS/390 V5 - NEAROFFPOS / FAROFFPOS & REORG10_Joan Green22_Joan.Green@BOIMAIL.COM30_Thu, 7 Jun 2001 09:55:45 +0100653_us-ascii Hugh,

I merely shorthand - its the lazy programmer in me - yes I'm looking at FAROFFPOSF & NEAROFFPOSF & yes we ran RUNSTATS & rebound!

Best Regards,

Joan Green ------------------------------

Date: Wed, 6 Jun 2001 08:05:27 +0100 From: "Beighton-Dykes, Hugh" Subject: Re: OS/390 V5 - NEAROFFPOS / FAROFFPOS & REORG

Please don't think I am being rude in stating the obvious, but if your OFFPOS values really haven't changed at all then there are two basic things that could be the cause i) FAROFFPOS and NEAROFFPOS are not used from V5 onwards - have you looked at FAROFFPOSF [...] 23321 47 22_Re: Database holy war.13_Adrian Savory27_Adrian.Savory@WORKTHING.COM30_Thu, 7 Jun 2001 10:26:44 +0100395_iso-8859-1 As everyone cites the administrative overhead of Oracle, perhaps we ought to be grateful to Oracle for providing a job creation scheme for DBAs. I'm doing some Oracle courses next month, not because I want to leave DB2 behind but because there is a greater demand for Oracle DBAs (in the UK). I'm hoping that demand will grow for UDB on Unix/NT but want to keep my options open. [...] 23369 46 28_Re: High Log Write Wait Time14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Thu, 7 Jun 2001 04:42:51 -0500382_iso-8859-1 Just some ideas on what can cause a log wait...

1) A system checkpoint is happening. Check LOGLOAD ZPARM and the checkpoint history in the BSDS. LOGLOAD should be set to give a checkpoint about every 10 - 20 minutes during peak hours. If you get frequent checkpoints only when a certain application is running, then the next item is likely to be the problem. [...] 23416 40 14_Re: [CODEPAGE]14_Vassie, Edward21_Edward_Vassie@BMC.COM30_Thu, 7 Jun 2001 05:00:08 -0500446_iso-8859-1 I think there is something in the Admin Guide about changing code pages. It has a strong health warning.

If you want to change the code page of your DB2 subsystem, it may be best to create a new subsystem and migrate data to it in a controlled manner.

If you want to do the change for some tables, create new ones and migrate the data. Then drop the original tables and rename the new objects to the original names. [...] 23457 91 27_Re: Unexpected SQLCODE -80514_Adrian Collett27_adriancollett@TISCALINET.IT30_Thu, 7 Jun 2001 12:10:22 +0200423_iso-8859-1 Eric,

As you say that you are not using SET CURRENT PACKAGSET then the only other explanation that I can come up with is that some of your programs are "dirtying" some area of working storage.

If a program sets the CURRENT PACKAGESET then the precompiler sets a variable SQL-TEMP to that value. This variable is in the WS and just happens to be the FIRST one after all user defined variables. [...] 23549 23 18_SQL- ERROR message13_Mathew, Sithu30_sithu.mathew@BLR.HPSGLOBAL.COM30_Thu, 7 Jun 2001 15:08:21 +0500353_- Hi List Members,

I am doing a migration project from ORACLE to DB2 UDB 7.1. I am using stored procedure builder(SPB) for creating stored procedures. In my stored procedures I want to display the SQL- ERROR message which may occur during some SQL error occurs. How this is possible???...



Suggestions are well appreciated.... [...] 23573 71 30_Re: Triggering Emails from JCL0_18_mebert@AMADEUS.NET30_Thu, 7 Jun 2001 13:14:04 +0200375_us-ascii Try this for the SYSUT1 input. The "Mime.Part.random.chars" is a string to denote the separation between the email body and the (multiple) attachments; choose any random string not part of your text. Send to multiple recipients by repeating the RCPT TO: line.

Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany [...] 23645 15 59_Derez D. Lusk/Household International is out of the office.13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM30_Thu, 7 Jun 2001 06:17:23 -0500389_us-ascii I will be out of the office starting 06/07/2001 and will not return until 06/08/2001.

any issues from DB2 please contact Marsupport

================================================ 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. 23661 18 30_Re: Database holy war - UPSERT10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 7 Jun 2001 13:31:46 +0200440_us-ascii Pay attention to the messes that this feature will cause, be sure.

Regards and good luck with UPSERT.

Max Scarpa

Sometimes DB2 sysprog, who saw too many dead by 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. 23680 37 33_Re: DB2 DBA for OS/390 job wanted15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Thu, 7 Jun 2001 06:56:02 -0500394_iso-8859-1 I would be willing to wager that this email got the notice of all the headhunters monitoring this site. You probably got contacted by now.

Linda Billings Systems Programmer, DBA, yadda, yadda, yadda

-----Original Message----- From: J S [mailto:pleniumcorp@YAHOO.COM] Sent: Wednesday, June 06, 2001 9:35 PM To: DB2-L@RYCI.COM Subject: DB2 DBA for OS/390 job wanted [...] 23718 35 30_Re: Database holy war - UPSERT16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 7 Jun 2001 17:43:10 +0530629_- It means Oracle have overcome something equivalent to -803. No duplicate records for Oracle. Anyone can do anything it seems !!

Regards Sanjeev

> -----Original Message----- > From: Max Scarpa [SMTP:mscarpa@CESVE.IT] > Sent: Thursday, June 07, 2001 5:02 PM > To: DB2-L@RYCI.COM > Subject: Re: Database holy war - UPSERT > > Pay attention to the messes that this feature will cause, be sure. > > Regards and good luck with UPSERT. > > Max Scarpa > > Sometimes DB2 sysprog, who saw too many dead by ORACLE. > > ================================================ > To change your subscription options or to cancel [...] 23754 54 30_Re: Database holy war - UPSERT15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Thu, 7 Jun 2001 14:28:00 +0200391_iso-8859-1 No more mess than usual I guess..If making an error is human, using computers do really mess things up.

Jaap Slot

No Oracle fan buth none the less almost bald ;-)







-----Original Message----- From: Max Scarpa [mailto:mscarpa@CESVE.IT] Sent: Thursday, June 07, 2001 1:32 PM To: DB2-L@RYCI.COM Subject: Re: Database holy war - UPSERT [...] 23809 53 33_Re: DB2 DBA for OS/390 job wanted0_26_JCameron@MSI-INSURANCE.COM30_Thu, 7 Jun 2001 07:33:42 -0500636_us-ascii monster.com







J S cc: Sent by: DB2 Subject: DB2 DBA for OS/390 job wanted Data Base Discussion List



06/06/01 09:35 PM Please respond to DB2 Data Base Discussion List











Hello List,

I need some information about the best way to search for a DB2 DBA for OS/390 job either contract or permanent. If you know of some useful websites other than dice.com or computerjobs.com that you have recently used to successful find a job, please let me know. Thanks for your help. [...] 23863 24 33_Re: DB2 DBA for OS/390 job wanted0_26_truman.g.brown@VERIZON.COM30_Thu, 7 Jun 2001 09:19:16 -0400668_us-ascii JS, go to a "local" DB2 user's group meeting and announce your REAL requirements, company benefits, salary range, sign-on bonus (if any), relocation package (if any), and opportunities for advancement within the company. Be prepared to pay more than you think for a truly good, expereinced person. Most "DBAs" with 2 years experience are probably not what you're looking for. Find someone who has installed/maintained DB2, knows logical to physical design, SQL, CICS/DB2 interface, and application/subsystem performance tuning (8 to 10 years REAL experience). Probable salary $100,000 to $110,000 a year (in a metropolitan area, less in the boonies) and [...] 23888 34 18_Hanging DDF Thread11_Sinha, Amit19_Sinha.Amit@MBCO.COM30_Thu, 7 Jun 2001 08:31:32 -0500416_iso-8859-1 Hi All,

There is this DDF query which just sits in the mainframe without doing anything.

As our(DBA's) id's have the ASUTIME set to maximum the query executes properly.

The application people concern is that even when the Query is Timed out it should return some error code so that they can trap the error and convey it to the user to check his Query which are build on the fly. [...] 23923 30 43_exec a program that has all DB2 ref removed10_Fred Hanes17_f_hanes@YAHOO.COM30_Thu, 7 Jun 2001 06:47:29 -0700556_us-ascii All, What has to be done to a DB2 program that has had all references to DB2 removed from it to execute..

this is what happens when Executed

QST301E BATCHCAF - DSN2 CONNECT ERROR HAS OCCURED. PLAN= RETURN CODE=0256 REASON CODE=00F30016 EA995I SYMPTOM DUMP OUTPUT

USER COMPLETION CODE=3001

TIME=08.18.07 SEQ=52183 CPU=0000 ASID=017E



__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ [...] 23954 28 48_WebSphere Session Persistence and DB2 for OS/39013_Waldo Tumanut52_Waldo_Tumanut/MO/americancentury@AMERICANCENTURY.COM30_Thu, 7 Jun 2001 08:14:06 -0500523_- Good morning, list.

We have several WebSphere instances on NT. We are thinking of using a common SESSIONS table in DB2 for OS/390 for session persistence. Questions: 1) Is it possible to share a common SESSIONS table amongst the WebSphere instances? 2) Can I pre-create the SESSIONS table ahead of WAS configuration? 3) Any problems with having the table owned by SYSADM and creating an alias for the the WebSphere data source ID? 4) This posting may be more appropriate on a WebSphere list. Do you know one? [...] 23983 19 33_Re: DB2 DBA for OS/390 job wanted3_J S21_pleniumcorp@YAHOO.COM30_Thu, 7 Jun 2001 08:40:26 -0500497_- Hello George,

I think you misunderstood, I was asking about how to look for a job and not how to hire a DBA. Anyway your good suggestion to go to a DB2 user group meeting may still hold good for someone looking for a job.

Thanks,

JS

================================================ 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. 24003 27 53_Stored Procedure returning result set to Visual Basic26_Marco Tulio De Vasconcelos30_marcotulio@CAIXASEGUROS.COM.BR30_Thu, 7 Jun 2001 11:05:23 -0300572_us-ascii Hi all,

Environment - DB2/OS/390 V 5.1, Visual Basic V 6.0, Cobol, TCP/IP

We have a stored procedure in Cobol which has a cursor declared as WITH RETURN that returns a result set from a Join statement. When this SP is called from a Cobol program, the caller program shows the result set correctly. But when this SP is called by a Visual Basic program, this program don't receive the result set. We have another SP also in Cobol, that returns a result set from a simple select (without join) and this one returns to the caller program in Visual [...] 24031 73 30_Re: Database holy war - UPSERT15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Thu, 7 Jun 2001 10:14:40 -0400376_iso-8859-1 I do lot of programming on DB2 and SQLserver as well as administration. Many of our programs do a update first and then not found, do the insert. I could have used UPSERT easily and reduced a bunch of typing and insert or update would have been faster also. I know most of the DBA's do not do the programming, so they do not care about the flexibility of SQL. [...] 24105 144 22_Re: Database holy war.16_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM30_Thu, 7 Jun 2001 10:22:09 -0400465_us-ascii So is Oracle 10i going to introduce a "new" SQL statement DELSERT to automatically delete and insert your row with a totally new key value which will be totally transparent to the application and the business???

No wonder Oracle is losing market share!!!











"Slot, JP (Jaap)" on 06/07/2001 03:18:54 AM

Please respond to DB2 Data Base Discussion List [...] 24250 73 25_Re: DB2 Replication Error15_Jackson Reavill18_damcon2@US.IBM.COM30_Thu, 7 Jun 2001 10:29:29 -0400452_us-ascii John,

Yes, you can delete from the CD table. We have done it without any consequences. I've seen this happen when garbage data gets left in the CD table and propagator tries to reapply it. We never did find the exact cause. I believe that somehow the timestamps used by propagator to control pruning and syncpoints between capture and apply got out of sync. We deleted the garbage records and forced a full refresh to clear it up. [...] 24324 16 44_Re: Information on psuedo deleted/punc rows.10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 7 Jun 2001 16:33:46 +0200375_us-ascii I've a (very) short .ppt file dealing with lock avoidance. Let me know if you're interested offline...

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. 24341 44 33_Re: DB2 DBA for OS/390 job wanted9_Sally Mir22_Sally.Mir@WACHOVIA.COM30_Thu, 7 Jun 2001 09:35:57 -0400698_us-ascii Try www.dbajobs.com. Worked for me!











J S @RYCI.COM> on 06/06/2001 10:35:18 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: DB2 DBA for OS/390 job wanted



Hello List,

I need some information about the best way to search for a DB2 DBA for OS/390 job either contract or permanent. If you know of some useful websites other than dice.com or computerjobs.com that you have recently used to successful find a job, please let me know. Thanks for your help. [...] 24386 92 25_Re: DB2 Replication Error13_Ellen Richter19_e2richte@HEWITT.COM30_Thu, 7 Jun 2001 10:09:41 -0500686_us-ascii I've encountered this error before due to setting up the table for replication table and not identifying the correct primary keys through Control Center.





From: Jackson Reavill on 06/07/2001 09:29 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: Client: Subject: Re: DB2 Replication Error

John,

Yes, you can delete from the CD table. We have done it without any consequences. I've seen this happen when garbage data gets left in the CD table and propagator tries to reapply it. We never did find the exact cause. I believe that somehow the timestamps used by [...] 24479 98 30_Re: Database holy war - UPSERT14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Thu, 7 Jun 2001 08:13:15 -0700504_iso-8859-1 Funny, For years, Oracle has been bashing IBM because of lack of compliance to ANSI SQL standards, yet know they come out with DML statements of their own. I find that interesting. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own.

-----Original Message----- From: Slot, JP (Jaap) [mailto:J.P.Slot@RF.RABOBANK.NL] Sent: Thursday, June 07, 2001 7:28 AM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Database holy war - UPSERT [...] 24578 29 30_Re: Database holy war - UPSERT10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 7 Jun 2001 17:29:43 +0200397_us-ascii Estimeed list,

I've heard that in the next ORACLE 69i there will be a new SQL statement, MAKECOFFEE.

Unfortunately you must have the next release of JAVA_coffemaker 111.111.69 running on SUN set_boulevard_69 with

the new coffee percolator hardware features (by BIALETTI - for italians the latter will be clear...).

For only 69 zillion per license fee.... [...] 24608 16 44_Pamela Harris/IT/SEARS is out of the office.0_17_pharri1@SEARS.COM30_Thu, 7 Jun 2001 10:28:29 -0500500_us-ascii I will be out of the office from 06/07/2001 until 06/14/2001.

For DB2 page PIN # 7270526, for informix/sqlserver page PIN # 7270525, for netfinity infoprint page 7270982, for network infrastructure, page 18007598352, PIN # 7270063 or 7270300.

================================================ 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. 24625 51 57_Re: Stored Procedure returning result set to Visual Basic15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Thu, 7 Jun 2001 11:37:30 -0400426_- We have at least 20 programs written to return result set from a join. Check you commit on return yes/no. If it is yes, then cursor should be with 'with hold with return'

Thanks..Sibi

-----Original Message----- From: Marco Tulio De Vasconcelos [mailto:marcotulio@CAIXASEGUROS.COM.BR] Sent: Thursday, June 07, 2001 09:05 AM To: DB2-L@RYCI.COM Subject: Stored Procedure returning result set to Visual Basic [...] 24677 41 33_Re: DB2 DBA for OS/390 job wanted10_Andy Stein22_jbak2009@AMERITECH.NET30_Wed, 6 Jun 2001 10:46:57 -0500683_us-ascii J S I have some outstanding opportunities available for DB2 OS/390 pros. Please give me a call @ 847-564-2009 or e-mail contact information. I look forward to hearing from you.

Thank You

Andy Stein













At 09:35 PM 6/6/01 -0500, you wrote: >Hello List, > >I need some information about the best way to search for a DB2 DBA for >OS/390 job either contract or permanent. If you know of some useful >websites other than dice.com or computerjobs.com that you have recently >used to successful find a job, please let me know. Thanks for your help. > >JS > >================================================ >To [...] 24719 48 33_Re: DB2 DBA for OS/390 job wanted13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Thu, 7 Jun 2001 08:56:33 -0700743_us-ascii naukari.com :-)

Thanks. Ashish.

-----Original Message----- From: truman.g.brown@VERIZON.COM [SMTP:truman.g.brown@VERIZON.COM] Sent: Thursday, June 07, 2001 6:19 AM To: DB2-L@RYCI.COM Subject: Re: DB2 DBA for OS/390 job wanted

JS, go to a "local" DB2 user's group meeting and announce your REAL requirements, company benefits, salary range, sign-on bonus (if any), relocation package (if any), and opportunities for advancement within the company. Be prepared to pay more than you think for a truly good, expereinced person. Most "DBAs" with 2 years experience are probably not what you're looking for. Find someone who has installed/maintained DB2, knows logical to physical design, SQL, CICS/DB2 interface, [...] 24768 43 23_DB2 Subsystem ID Change11_Moore, Tony15_TMoore@IKON.COM30_Thu, 7 Jun 2001 12:01:19 -0400354_iso-8859-1 Listers, I'm currently working up a DB2 V4 to V6 migration plan. At a mile high view, the plan (so far) is as follows:

1. Install V6 2. Recreate databases in V6 subsystem. 3. DSN1COPY V4 VSAM datasets to V6 VSAM datasets Or UNLOAD FROM V4 / LOAD TO V6 (Haven't decided which way to go on this one yet) 4. Bind packages/plans in V6 [...] 24812 20 30_Re: Triggering Emails from JCL19_Anand H Thippeswamy30_athippes.DHHS@DHHS.STATE.NH.US30_Thu, 7 Jun 2001 12:17:33 -0400469_us-ascii Hello,

Customize SMTP mail generator for OS/390 (XMITIP) obtained "FREE" at http://www.geocities.com/lbdyck . You can send host files as attachment in Lotus notes to group of people.



Regards Anand

================================================ 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. 24833 222 27_Re: DB2 Subsystem ID Change14_Crichton, Jean21_jcrichton@FEDDATA.COM30_Thu, 7 Jun 2001 12:45:17 -0400475_iso-8859-1 Tony,

If you do your migration this way, you have no fallback to V4 without losing everything subsequent to the migration. I am assuming you are doing this to avoid the required migration path to V5 prior to migrating to V6. As someone who has had to fall back to V5 for unavoidable reasons one month after migrating to V6 (and remigrate to V6 two months later), I strongly advise you to rethink this!!! However, if you are willing to take the risk... [...] 25056 28 22_'Data Capture Changes'9_Aristotle21_db2_learner@YAHOO.COM30_Thu, 7 Jun 2001 09:42:42 -0700506_us-ascii Hello DB2 users, I have turned on the 'Data Capture Changes' for one table. If I use this option then full logging will takes place for that particular table. Can any one tell me how it will affect the logging and all costwise? Is it going to be a considerable amout for the company? In what way it will affect the cost? Please let me know your thought. I need this information urgently. Any input regarding this is highly appriciated. Thank you very much indeed. Have a nice day! Aristotle. [...] 25085 161 26_Re: 'Data Capture Changes'16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 7 Jun 2001 18:16:59 +0100559_- Hello Aristotle;

Well - remember that INSERT's and DELETE's already logs the entire row. This leaves the UPDATE's. If your environment has 90% updates compared to 10% DELETE/INSERT - then you might see the log increase. Another thing to have in mind - Compression and VARCHAR may already cause logging beyound the "from byte changed - to byte changed". A third issue - DB2 stores MUCH more than DML in the log. Some years ago when I was a "real DBA" - we enabled Data Capture Changes globally - and we did not see the log increase very much. We [...] 25247 117 27_Re: DB2 Subsystem ID Change11_Moore, Tony15_TMoore@IKON.COM30_Thu, 7 Jun 2001 13:25:41 -0400555_iso-8859-1 Hey Jean, Thanks for the info!! The migrations from V4 to V6 will be on the same LPAR. My system datasets (logs, bsds, DSNDB01, DSNDB06, etc.) currently have a high level qualifier of the subsystem id. I would have liked to keep it that way, but couldn't come up with a simple way of doing so without totally wiping out my V4 system right off the bat. So, I've already figured I'm gonna have to come up with another high level qualifier scheme for those. The user data has a different high level qualifier that I've been wanting to change [...] 25365 50 26_Re: 'Data Capture Changes'11_Moore, Tony15_TMoore@IKON.COM30_Thu, 7 Jun 2001 13:34:16 -0400421_iso-8859-1 Aristotle, Keep in mind this really depends on the type and amounts of updates you're applications are doing, but I turned data capture on for all my production tables for a project to strip daily database changes out of the logs and ship down to Oracle. I saw a slight increase in the amount of archives DB2 was spitting out each day, but nothing drastic. Other than that, we've had no problems with it. [...] 25416 104 28_Re: High Log Write Wait Time9_Sally Mir22_Sally.Mir@WACHOVIA.COM30_Thu, 7 Jun 2001 11:39:40 -0400518_us-ascii Thanks, Edward, for your detailed reply. I have a couple of comments/questions:

1. We have indeed determined that while this application is running we are performing checkpoints extremely frequently. So we will be adjusting LOGLOAD. I am assuming that a checkpoint causes a log write force (I know a COMMIT does, but I haven't found anything in the manuals that explicitly states this about checkpoints-- I would love it if someone could point me to documentation that validates this assumption). [...] 25521 205 29_Re: Outer join understanding.11_David Nance16_DWNance@FHSC.COM30_Thu, 7 Jun 2001 08:32:08 -0400470_US-ASCII Dennis, You can still do this in a round about way in V5, by using a nested table expression. Just make sure you select the columns you want from the second pass at the table, plus any columns you can equate in the ON clause. SELECT A.PNODE, A.TAG, B.SNODE FROM T1.T1_TABLE A LEFT OUTER JOIN ( SELECT C.TAG , C.SNODE FROM T1.T1_TABLE C WHERE B.TAG LIKE 'DSNTAG%' AND B.STATUS = 'END' ) AS B ON A.TAG = B.TAG WHERE A.STATUS = 'NEW' AND A.TAG LIKE 'DSNTAG%' [...] 25727 58 26_Re: Size of a UDB database14_David Bargeron29_David.Bargeron@BTINTERNET.COM30_Thu, 7 Jun 2001 18:50:24 +0100388_iso-8859-1 Mathai,

from the CLP try command (see command reference for further info): 'list tablespaces show detail' then add up all the used pages.

or

if you use TSM for your backups then via the TSM admin client you can interrogate what TSM has backed up via command 'select * from contents where filespace_name = 'your dbname' then look for your last backup. [...] 25786 55 26_Re: 'Data Capture Changes'13_McDonald, Ken20_Ken_McDonald@BMC.COM30_Thu, 7 Jun 2001 12:57:50 -0500455_iso-8859-1 You could write a REXX to examine DB2 Log records to obtain the information you need. The effect of DCC is very specific you each environment. Or, do you have access to a log analysis tool? Log Master for DB2 from BMC Software has a Data Capture Change report which will advise on the bytes increase/decrease logged of turning Data Capture Changes on or off. CA's Log Analyzer or IBM's DB2 Log Analysis tool may have similar capabilities. [...] 25842 139 29_Re: Outer join understanding.12_Gregg Sawyer26_gregg.a.sawyer@VERIZON.COM30_Thu, 7 Jun 2001 14:03:42 -0400628_us-ascii Fair enough - I was off the mark, primarily because of the issues outlined in your second paragraph, though in another life I would like to look very hard at the business requirements, and design alternatives; for instance, a multi-level table design might be appropriate...

With respect to the ON/WHERE division of labour : it seems that DB2 (v6 and up) "promotes" predicates up to the join process if it considers them conducive to better performance; maybe someone else can provide more detailed insight into the reasons why an ON predicate is deemed more efficient than WHERE (generally associated with [...] 25982 19 28_Re: High Log Write Wait Time0_26_truman.g.brown@VERIZON.COM30_Thu, 7 Jun 2001 14:12:23 -0400594_us-ascii Sally, at checkpoint time ALL application processing is suspended, all pending log writes occur, and all updated pages that have not been externalized are written. Then normal processing reumes.

Checkpoints occur during a log switch and/or when LOGLOAD limit is reached. Small log datasets cause more frequent checkpoints.

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. 26002 31 44_FW: Information on psuedo deleted/punc rows.13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Thu, 7 Jun 2001 13:23:31 -0500515_iso-8859-1 You can disregard my previous message. I was able to re-create the situation and confirm that the psuedo-deleted possibly uncommitted index rows apparently can cause the contention we are seeing. I suspect that my earlier tests didn't confirm this since they were passing the CLSN test.

Thanks, Mike. -----Original Message----- From: Vaughan, Mike [mailto:Vaughan.Mike@principal.com] Sent: Wednesday, June 06, 2001 3:51 PM To: DB2-L@RYCI.COM Subject: Information on psuedo deleted/punc rows. [...] 26034 98 57_Re: Stored Procedure returning result set to Visual Basic16_Adriana Teixeira29_ateixeira@CAIXASEGUROS.COM.BR30_Thu, 7 Jun 2001 15:33:49 -0300589_us-ascii Philip, would you mind sending us an example of your caller program written in vb that receives the result set?

TIA, Adriana Teixeira, Data Administrator.













"Philip, Sibimon" em 07/06/2001 12:37:30

Favor responder a DB2 Data Base Discussion List















Para: DB2-L@RYCI.COM

cc: (bcc: Adriana Teixeira/MATRIZ/SASSE)





Assunto: Re: Stored Procedure returning result set to Visual Basic [...] 26133 43 28_Re: High Log Write Wait Time13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Thu, 7 Jun 2001 11:52:44 -0700531_us-ascii Really is that true? I have been under the impression that asynchronous writes are scheduled and only the log buffers are externalized synchronously. Application processing should not wait for all externalization to complete.

Someone confirm or deny this so that I can alter my impression.

Thanks. Ashish.

-----Original Message----- From: truman.g.brown@VERIZON.COM [SMTP:truman.g.brown@VERIZON.COM] Sent: Thursday, June 07, 2001 11:12 AM To: DB2-L@RYCI.COM Subject: Re: High Log Write Wait Time [...] 26177 15 25_Re: AW: Database holy war12_Brian Picard26_brian_picard@ALTAVISTA.COM30_Thu, 7 Jun 2001 13:27:20 -0500488_- And for those that are bald / with dandruff ( just kiddin......) What Richard left out ends with the word 'HOLE' Now Fluorscent Lighted brains can guess 'A__HOLE' What a humour?...

Hey richard, how about sharing some other Ellison jokes.

================================================ 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. 26193 51 22_open cursor hang/loop?11_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Thu, 7 Jun 2001 14:11:30 -0500437_iso-8859-1 We have DB2 6.1 on OS/390. A few weeks after we upgraded production to version 6, some application programming changes went in and programs (batch and online) began looping. Some logic problems were found and the online transacations all work now. One problem that is left is a couple of batch jobs that seem to hang or maybe loop. It's in a package that was also the one that kept showing up for the online problem too. [...] 26245 71 26_Re: open cursor hang/loop?13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Thu, 7 Jun 2001 12:57:00 -0700350_iso-8859-1 We have a simular problem and it appears (at this time) to be a reformulation error related to sysplex. Hard to recreate, goes away when job is canceled and rerun. We are looking at changing a Zparm to not allow sysplex parallelism but to still allow CPU parallelism. It hasn't occur since then, but as you say its hard to reproduce. [...] 26317 37 22_UDB version 7 question29_Sherri Sterling DIGEST NOMIME29_Sherri.Sterling@BPD.TREAS.GOV30_Thu, 7 Jun 2001 16:06:22 -0400435_us-ascii I am new to UDB version 7 and was wondering how UDB uses the LDAP. Either Microsoft's version or IBM's version. I created a database in version 7 and was trying to administer it from a version 6 client, when I got an LDAP error. I read in the version 7 Admin guide that when you drop a database or add a database the services to clean up the LDAP are not available. What does that mean and how does the LDAP get updated. [...] 26355 39 13_BLOB question29_Sherri Sterling DIGEST NOMIME29_Sherri.Sterling@BPD.TREAS.GOV30_Thu, 7 Jun 2001 16:10:14 -0400332_us-ascii I have two images currently stored on tape with a block size > 32meg. I think db2 breaks these images down into 4k chunks. But how does it store them and how does it retrieve all the 4k pieces to build the image again.

Also we are expecting to store 45 Terabytes of these images in db2. Will this be a problem? [...] 26395 42 19_Pessimistic Locking19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Thu, 7 Jun 2001 15:16:25 -0500537_ISO-8859-1 Hi all, I have an end-user that is bent on using pessimistic locking and I haven't yet been able to talk them out of it. Its a fairly typical DB design in that the parent table is the Customer entity and, basically, all other tables are children to it. E.g. the BILL_ID table is child to CUST and each CUST may have several BILL_IDs. The locking structure must allow access by 1 or more users to separate BILL_IDs, but not the same BILL_ID for a given Customer. I've got a design that will work (so far, ha) in that I'll [...] 26438 88 26_Re: open cursor hang/loop?11_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Thu, 7 Jun 2001 15:31:00 -0500417_iso-8859-1 We can't reproduce it on demand, but it happens at least every few days. By sysplex parallelism, I assume you mean data sharing? We don't have that, and it only occurs in this one program. Is yours limited to one program too?

-----Original Message----- From: Daniel Turner [mailto:daniel_turner@ALBERTSONS.COM] Sent: June 07, 2001 2:57 PM To: DB2-L@RYCI.COM Subject: Re: open cursor hang/loop? [...] 26527 100 17_Re: BLOB question24_The Fillmore Group, Inc.18_fillmore@CLARK.NET30_Thu, 7 Jun 2001 16:45:47 -0700471_us-ascii

Sherri, You might want to think about using IBM's Content Management software rather than storing the images natively in DB2. Content Manager is a rebranding and consolidation of IBM products that manage non-traditional, non-textual data such as EDMSuite and Digital Library.

I miss the deli in Parkersburg... :-(

Frank



--Original Message Text--- From: Sherri Sterling DIGEST NOMIME Date: Thu, 7 Jun 2001 16:10:14 -0400 [...] 26628 65 49_Re: [exec a program that has all DB2 ref removed]13_Larry Findley17_llfindley@USA.NET28_Thu, 7 Jun 2001 17:10:46 CDT425_US-ASCII OK, since no one else has weighed in on this one -

I have never seen this happen, and thinking about the possibilities has led me to the following questions:

Was all SQL removed from the program source, and then the program compiled using the compile process for DB2 programs?

Was the program executed in a TSO DSN environment, specifying a plan, even though it is no longer a DB2 program? [...] 26694 182 29_Re: Outer join understanding.15_Smith, Dennis J22_dennis.j.smith@EDS.COM30_Thu, 7 Jun 2001 17:34:05 -0500467_iso-8859-1 Yes..... I have created an SQL that works in both versions, based on David's advice (thanks David). It looks like this.

SELECT TBLA.PNODE, TBLA.TAG, TBLB.SNODE FROM (SELECT PNODE, TAG, SNODE, STATUS FROM T1.T1_TABLE WHERE TAG LIKE 'DSNTAG%' AND STATUS = 'NEW') AS TBLA LEFT OUTER JOIN (SELECT PNODE, TAG, SNODE, STATUS FROM T1.T1_TABLE WHERE TAG LIKE 'DSNTAG%' AND STATUS = 'END') AS TBLB ON TBLA.TAG = TBLB.TAG AND TBLA.STATUS <> TBLB.STATUS; [...] 26877 106 49_Re: [exec a program that has all DB2 ref removed]0_19_Tim.Lowe@STPAUL.COM30_Thu, 7 Jun 2001 17:40:15 -0500437_us-ascii Fred, I am thinking along the same lines as Larry.

I was curious why the PLAN in the message was blank. Is it possible that they are attempting to execute this as a DB2 program, but the reference to the Plan name was taken out, so that the statement now just says RUN PROGRAM(....)? If so, then perhaps you should change your jcl to just execute the program (instead of executing IKJEFT*, then DSN and RUN Program)? [...] 26984 134 57_Re: Stored Procedure returning result set to Visual Basic15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Thu, 7 Jun 2001 18:46:17 -0400758_- My friend created small VB script to call a stored procedure which returns a result set using ADO.

set obj = createobject("adodb.connection") obj.open "dsn=db2t;uid=;pwd="

set cmd = CreateObject("ADODB.Command") cmd.activeconnection = obj cmd.commandtext = "test.mq0002sp " cmd.commandtype = 4

cmd.Parameters.Append cmd.CreateParameter("Download_type",129,1,1,"D")

set rs = cmd.execute()

do while not rs.eof msgbox rs("VEHICLE_K") rs.movenext loop



Thanks..sibi

-----Original Message----- From: Adriana Teixeira [mailto:ateixeira@CAIXASEGUROS.COM.BR] Sent: Thursday, June 07, 2001 01:34 PM To: DB2-L@RYCI.COM Subject: Re: Stored Procedure returning result set to Visual Basic [...] 27119 20 12_DB2 Connect.15_Priyanka Deswal19_prdeswal@IN.IBM.COM30_Thu, 7 Jun 2001 04:41:25 +0000335_us-ascii If I want to install DB2 EEE and DRDA application server on same machine ,what component do I need to install? DB2 connect personal edition,db2 connect enterprise edition,or just db2 eee.

Regards Priyanka "Never let your dreams die,for if they die,life would be a broken wing bird that cannot fly." Erving Berling [...] 27140 29 39_Returning Varchar from Stored Procedure14_Cannamela, Jim26_jcanname@IDCFS.STATE.IL.US30_Thu, 7 Jun 2001 18:15:10 -0500392_iso-8859-1 We have a stored procedure returning a result set containing a column defined as varchar(8000).

The stored procedure returns the result set correctly from CLP and Command Center.

When called from VB the varchar column is returned with a non-printable character after each 1023 characters, possibly a CRLF.

Request any information to correct this problem. [...] 27170 188 29_Re: Outer join understanding.13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Thu, 7 Jun 2001 18:24:42 -0500336_us-ascii Gregg,

There is a huge difference between ON and WHERE clause predicates as you (and others) have discovered.

It's not so much a promotion of a predicate, but more when and where it can be applied. However, you can think of it as promotion when a Nested Table Expression merge is performed by the optimizer. [...] 27359 59 9_OLR issue15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Fri, 8 Jun 2001 10:07:35 +1000513_- Michael (and others),

You say:

'V6 OLR inline copies are unusable for logonly recovery because OLR places a wrong RBA in SYSCOPY.'

Um, I don't understand. I thought log-only recoveries were done after you've used a non-image copy backup to restore a tablespace, like DF/DSS for example. When you restore an object 'offline' from DB2, you then do log-only recovery to where you want to go. If you use the IC from the OLR you're not doing a log-only recovery; you've used an image copy. [...] 27419 57 33_Re: DB2 DBA for OS/390 job wanted15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Fri, 8 Jun 2001 10:17:43 +1000657_- Good point, Linda. I'll have to add DB2-L to Dice, Monster, justdb2jobs, naukari.com (hadn't heard of that one), jobserve.co.uk, computerwork...



Raymond Bell DBA, Certified, and (as an American friend of mine would say) blah, blah-blah, blah. ;o)

> -----Original Message----- > From: Billings, Linda [SMTP:linda.billings@DOA.STATE.WI.US] > Sent: Thursday, 7 June 2001 9:56 pm > To: DB2-L@RYCI.COM > Subject: Re: DB2 DBA for OS/390 job wanted > > I would be willing to wager that this email got the notice of all the > headhunters monitoring this site. You probably got contacted by now. > > Linda Billings > Systems Programmer, [...] 27477 19 28_DB2 Warehouse Center Problem11_Endy Lambey20_endyl@CENTRIN.NET.ID30_Fri, 8 Jun 2001 07:46:16 +0700577_- Dear all...

We are having a problem with DB2 Warehouse Center/Manager. Everytime we want to send a notification for each SQL Steps, it failed. Seems like the Warehouse Center didn't recognize the mail server.

Does anyone had encounter this problem before? How can we fix this problem?

Endy Lambey endy@lambey.net

================================================ 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. 27497 33 30_Re: DB2 Connect and ColdFusion12_Robert Mills30_robert.mills@CENTRELINK.GOV.AU30_Fri, 8 Jun 2001 10:05:59 +1000388_us-ascii Hi Rob,

I have come in late on this thread so I may have missed something but we use Cold Fusion with DB2 native drivers against DB2 OS/390 and UDB. When we first started using Cold Fusion there were no native drivers but they have since become available. We are running Cold Fusion 4.0.1 (soon to move to 4.5), UDB & DB2 Connect V7.1 on Solaris and DB2 OS/390 V6.1. [...] 27531 20 8_Rollback11_Endy Lambey20_endyl@CENTRIN.NET.ID30_Fri, 8 Jun 2001 07:49:34 +0700357_- Dear all...

We have another problem with DB2 Warehouse Manager. When we try to insert 4 millions rows data to a table, after the process had inserted 2 million rows the tablespace is full, so the process will rollback. But the problem was that the termintade inserting process only take 3 hours, but the rollback process took almost 13 hours. [...] 27552 84 25_Re: DB2 Replication Error13_John Glorious26_john@MITRAINFOSARANA.CO.ID30_Fri, 8 Jun 2001 09:26:32 -0700310_us-ascii Hi Jay,

Thanks for you Info. I want to delete record in CD table to solve my problem (about primary key and foreign key), but when I deleted it usually it will effect to other record, and so on, and so on. Do you have any idea ???. We have to avoid full refresh since it very costly...... [...] 27637 123 23_Re: Clustering Tradeoff15_Richard Simpson20_rsimpson@AU1.IBM.COM30_Fri, 8 Jun 2001 10:55:31 +1000366_us-ascii Thanks Terry, I see where you are coming from.

So you are fetching from the clustering index, but in the order the rows are retrieved from the non-clustering index (unless DB2 sorts the keys returned from the 1st 'copy' of the table before joining to the 2nd 'copy'). There are also must be no final materialisation to satisfy an order by, etc. [...] 27761 15 12_Re: UPSERT ?0_17_JTonchick@AOL.COM28_Thu, 7 Jun 2001 23:31:37 EDT474_US-ASCII When did the ANSI SQL committee approve UPSERT as a new addition to SQL standards? Is this an attempt to prevent application conversions from Oracle to other RDBMS servers? Looks like Larry E. is taking lessons from Bill G.

================================================ 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. 27777 55 12_Re: Rollback16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 8 Jun 2001 09:20:13 +0530295_- Endy,

I am not sure how DB2 Warehouse Manager works. I am not sure if you might require yoy 4 millions rows to commit simultaneously because it may have dependency but if possible Can't you have commit in between so that you can commit after say inserting some 'n' number of rows. [...] 27833 69 28_Re: High Log Write Wait Time16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 8 Jun 2001 09:28:43 +0530401_- Hi George,

I think Ashish is correct. Applications processing do not wait. Writes are asynch. Pages which are updated can be externalized but pages on which updation is still taking place once the checkpoint is taken, those pages will not be externalized and this is the reason, pages are written synchronously when 2 successive checkpoints pass without the pages being written to disk. [...] 27903 27 25_Re: DB2 Replication Error13_Henry J. Cobb14_hcobb@SLIP.NET30_Thu, 7 Jun 2001 23:08:52 -0500566_- A massive bug I've found in apply on UDB/NT v6.1 is that apply thinks that IS_KEY requires an exact match for all 'Y' == IS_KEY columns to cause a conflict and so completely overlooks unique fields that are not part of the primary key. (Ouch!)

If a certain large international business machinery company was really committed to open source development, I'd put in a temporary fix to the sources to re-re-try the apply as an update to the true primary key. (Applying the changes in order would then take care of unique fields migrating across records.) [...] 27931 175 23_Re: Clustering Tradeoff13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Thu, 7 Jun 2001 23:29:09 -0500584_us-ascii Richard,

What you are missing is the real problem with List Prefetch. It's not the RID sort, as this is very efficient, and there is no question that the resultant data access using skip sequential process is far better than random I/O.

The problem with List Prefetch is the fact that DB2 must retrieve all of the qualifying index RIDs before the RID sort. Let's take an extreme example that there are 1,000,000 qualifying rows in the index, and there are 100 records per index page. That's 10,000 index leaf pages which must be read. What if we only want [...] 28107 18 17_No of partitions.15_Priyanka Deswal19_prdeswal@IN.IBM.COM30_Fri, 8 Jun 2001 10:03:35 +0530485_us-ascii In DB2 EEE enviroment if my data is doubled on all partitions,what should be the number of partitions that I should add to my System.Should it be double the number of partitions that I have right now ??



Regards Priyanka

================================================ 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. 28126 40 12_Re: Rollback11_Endy Lambey20_endyl@CENTRIN.NET.ID30_Fri, 8 Jun 2001 11:34:45 +0700459_- Sanjeev,

I forgot to mention that we are committing every 5000 rows, and we're using DB2 7.1 AIX. I think the problem is on the Warehouse Manager site, because the problem only occur if the Rollback was initiated by the Warehouse, the Rollback will delete all the rows inserted, even the committed rows. If we force to stop the process directly from AIX (with the kill command) the rollback process will only deleted all the uncommitted rows... [...] 28167 85 12_Re: Rollback17_Sundaresan, Anand34_Anand.Sundaresan@BLR.HPSGLOBAL.COM30_Fri, 8 Jun 2001 09:35:18 +0500586_- Endy

As suggested by Sanjeev Committing at regular intervals is the wisest thing when you are talking of such a large number of rows. You can set a commit counter for x number of rows. I doubt whether the cause is Memory but again its platform dependant.

Regards Anand

> -----Original Message----- > From: S, Sanjeev (CTS) [SMTP:SSanjeev@CAL.COGNIZANT.COM] > Sent: Friday, June 08, 2001 9:20 AM > To: DB2-L@RYCI.COM > Subject: Re: Rollback > > Endy, > > I am not sure how DB2 Warehouse Manager works. I am not sure if you might > require yoy 4 millions rows [...]