1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l April 2001, week 1 2 135 40_Re: JCL to compile a C program on OS/39024_Peter_Schwarcz (Bigpond)26_Peter_Schwarcz@BIGPOND.COM30_Sun, 1 Apr 2001 21:44:08 +1000718_iso-8859-1 John,

There is a sample in your SDSNSAMP library that does pretty much what you are looking for:

Have a look at member DSNTEJ63 which has the following description:

//********************************************************************* 00010000 //* Name = DSNTEJ63 00020000 //* 00030000 //* Descriptive Name = 00040000 //* DB2 Sample Application 00050000 //* Phase 6 00060000 //* Sample SQL Procedure 00070000 //* SQL Procedure Language 00080000 //* 00090000 //* 00100000 //* LICENSED MATERIALS - PROPERTY OF IBM 00110000 //* 5645-DB2 00120000 //* (C) COPYRIGHT 1999 IBM CORP. ALL RIGHTS RESERVED. 00130000 //* 00140000 //* Status = Version 6 00150000 //* 00160000 //* Function = [...] 138 23 27_Stored Procedure 390 DB2 V50_29_WilliamsByron@RUSSELLCORP.COM30_Sun, 1 Apr 2001 11:07:01 -0500531_ISO-8859-1 Many thanks for the help in the past. I am having a problem ASSOCIATEing and ALLOCATEing a cursor left open by a stored procedure running on the HOST. The HOST is an OS/390 running DB2 V5. The SP opens a cursor with the RETURN option. I am using a Visual Basic 6.0 appl., running on my client (Windows NT 4.0), to call the SP. The connection, Call and return of parameters works fine. The communication driver is IBM DB2 ODBC DRIVER. Any help and especially VB code examples on how to do this would be appreciated. [...] 162 16 46_Demetris Kasheris/LT/CPB is out of the office.17_Demetris Kasheris19_DKasheris@LAIKI.COM30_Mon, 2 Apr 2001 01:01:01 +0300440_us-ascii I will be out of the office starting 01/04/2001 and will not return until 07/04/2001.

I will be in London for Disaster Recovery testing. 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. 179 66 21_Re: SQL30020N RC=125410_teldb2kals22_teldb2kals@TELSTRA.COM30_Mon, 2 Apr 2001 10:27:20 +1000594_us-ascii This might be of help.

SQL30020

Symptom SQL30020N Execution failed because of a Distributed Protocol Error that will affect the successful execution of subsequent commands and SQL statements.

Solutions Service should be contacted with this error.

Check the db2dump directory for an ffdc dump (pid.000). Then, format this dump file with db2fdump and look in the result file for "ERROR". An MVS ABEND may be listed here. In this case check the MVS console for further information, and look up the abend code in the DB2 for MVS Messages and Codes manual. [...] 246 55 31_Re: Stored Procedure 390 DB2 V510_teldb2kals22_teldb2kals@TELSTRA.COM30_Mon, 2 Apr 2001 10:42:45 +1000525_us-ascii Hi Byron,

I am not sure if u r using API calls or ADO. I have been able to execute with both. (u dont need associate/allocate). There r some samples at this site.

http://www-4.ibm.com/software/data/db2/os390/cstips/vbsamps.html

Let me know if need further help.

Cheers, Kals



-----Original Message----- From: WilliamsByron@RUSSELLCORP.COM [SMTP:WilliamsByron@RUSSELLCORP.COM] Sent: Monday, April 02, 2001 2:07 AM To: DB2-L@RYCI.COM Subject: Stored Procedure 390 DB2 V5 [...] 302 167 36_Re: V5 to V6 - Host variable problem10_teldb2kals22_teldb2kals@TELSTRA.COM30_Mon, 2 Apr 2001 10:51:53 +1000835_us-ascii Hi Manas,

The following APAR might be of help. (http://techsupport.services.ibm.com/s390/390.uhuic_getrec? args=DVhuron.boulder.ibm.com+DB390+DA53831+STDSNH312Y+USbin)

ABSTRACT: PQ35859: PRECOMPILE ERRORS ON IN(:HV-ARRAY) DSNH312I E DSNHSMUD AND DSNH104I E DSNHSM2V

ORIGINATING DETAILS: Precompile errors on IN(:hv-array)

LOCAL FIX AS REPORTED BY ORIGINATOR: none

RESPONDER SUMMARY: **************************************************************** * USERS AFFECTED: Users of the DB2 UDB for OS/390 Precompiler. * **************************************************************** * PROBLEM DESCRIPTION: The Precompiler was incorrectly issuing * * the DSNH312I error when a host * * structure was used in an IN predicate. * **************************************************************** [...] 470 60 28_00C9008E on resouce type 3047_Sri Sri20_sri_db2l@HOTMAIL.COM30_Sun, 1 Apr 2001 23:45:25 -0500410_- Dear Experts,

Sorry about the long post. We are on OS/390 DB2 UDB V6. A total of about 7 job were running. One program runs as 5 separate jobs to access separate partitions of a tablespace with 10 partitions and rowlevel locking. It has over 100,000 rows. The table is read using cursor with hold and after processing each row the job deletes the row. The other two jobs access different tables. [...] 531 28 52_Re: Tony Provenzola - next 2 zillion reply generetor10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 2 Apr 2001 08:59:09 +0200346_us-ascii I agree with Mr Provenzola but in my opinion in every job/style of life/etc there's an amusing aspect (and we cannot cut

this) we can explore (avoiding 2 zillions of messages, if possible).

And there's a lot of people that take themselves too seriously (sorry if this is not the correct phrase in english, but I think [...] 560 108 40_Re: space realease..... immediate reply.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Mon, 2 Apr 2001 12:45:51 +0530594_- Nice new thing from Chris.

Kumar, In general space of the simple tablespace can't be reused before reorg. For Segmented and Partitioned, rules by Chris can be applied.

Regards, Sanjeev

> -----Original Message----- > From: Blaicher, Chris [SMTP:Chris_Blaicher@BMC.COM] > Sent: Friday, March 30, 2001 11:15 PM > To: DB2-L@RYCI.COM > Subject: Re: space realease..... immediate reply. > > Yes and no. > > DB2 figures out where it would like to put the row (what page) and goes to > the space map page for that page and sees if there is space on the page by > looking at [...] 669 150 40_Re: space realease..... immediate reply.18_Pudukotai, Nagaraj35_nagaraj.pudukotai@BLR.HPSGLOBAL.COM30_Mon, 2 Apr 2001 12:42:25 +0500597_iso-8859-1 Hi Sanjeev I came across this in Gabrielle's DB2 book on performance:

Chapter TS Creating tablespaces

Page no : 111

Reorganization is required to reuse space occupied by a dropped table in a simple tablespace. In contrast, a reorganization is not required to reuse space occupied by dropped tables in a segmented tablespace. This makes segmented tablespaces a particularly good choice for QMF users who frequently create and drop tables explicitly or implicitly. Deleted row space is immediately reusable for all types of tablespaces without a reorganization. [...] 820 53 35_Re: IMS to DB2 Conversion Products?0_22_blyons2@CSCPLOENZKE.DE30_Mon, 2 Apr 2001 10:33:13 +0200507_us-ascii Hi Gary, I used to work for a company who did this kind of thing with the help of home-grown tools. We covered both data and program migration. There are a lot of issues involved here - like what applications/databases you want to migrate and why. There are also pros and cons of a tool-based migration (e.g. ease and speed of migration certainly a plus, but you can suffer badly on maintainability depending on the readability of the generated code). Contact me offline if you need any info. [...] 874 115 35_Re: IMS to DB2 Conversion Products?10_Krish Siva29_csr_krishna@CHENNAI.TCS.CO.IN30_Mon, 2 Apr 2001 14:14:29 +0530359_us-ascii Hi Gary,

Using tools is OK for migration but if you are looking for tools that would intercept IMS calls and convert them to SQL calls maintainability, cost etc. become an issue. We have done IMS to DB2 conversion in the past for many major customers and we would only be too happy to talk to you on this. You could get in touch offline. [...] 990 90 22_Re: Warehouse Question20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT30_Mon, 2 Apr 2001 10:52:23 +0200541_iso-8859-1 Hi Paul,

do you mean that this time you can nstall on Aix platform also the Warehouse Server? I had installed DB2 UDB 7.1 on AIX and it include the warehouse center that should have a subset of the functionality of Warehouse Manager. I didn't find anyway to install a warehouse server on Aix and many people told me the warehouse server was still only avaible on Win NT/2000. The only thing I could do it was to install a warehouse agent on the Aix server. If it's not the truth can you suggest me the way to make the [...] 1081 24 24_Bind Commit / Deallocate11_Mohamed Beg19_mohamedb@ABSA.CO.ZA30_Mon, 2 Apr 2001 12:25:40 +0200557_iso-8859-1 Hi.

We are currently in a 2 way sysplex environment running DB2 and IMS.

I would like to know with regards to binds which would be the better option release commit or deallocate while using IMS Shared Regions.

MOHAMED BEG DATABASE ADMIN AND SUPPORT DEPT DB2 DBA Phone: (011) 350-5225

================================================ 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. 1106 16 32_Re: 00C9008E on resouce type 30415_Walter Janissen27_walter.janissen@VICTORIA.DE30_Mon, 2 Apr 2001 06:19:58 -0500489_- I think, from what you wrote, that the list prefetch was downgraded to a tablespace scan. But I don't no for sure it is really a RID pool problem or if the SQL in question accessed more than (I think) 25% of the entire rows. I never know what this OMEGAMON messages means, because there are two of these: MAX STORAGE and MAX LIMIT, but I would guess MAX STORGAGE has something to do with RID Pool size and the other with to much rows. Maybe somebody else could explain this better. [...] 1123 17 28_Re: Bind Commit / Deallocate15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Mon, 2 Apr 2001 06:25:48 -0500459_- Mohamed

I don't know what you meant with better, but the IBM recommendation was deallocate. The only thing I can say is: We are using release deallocate at our site and we are in a 3-way data sharing with IMS shared queues. Sometimes we have experienced timeouts with our pseudo-WFI transactions and some binds or DDL. Therefore all dependent regions will be bounced at 6 PM with no pseudo-WFI and bounced again at 6 AM with pseudo-WFI enabled. [...] 1141 33 28_Re: Bind Commit / Deallocate11_Mohamed Beg19_mohamedb@ABSA.CO.ZA30_Mon, 2 Apr 2001 14:29:11 +0200621_iso-8859-1 Hi Walter.

I know IBM's recommendation is release deallocate in a data sharing environment. We've experienced the following problem with release Deallocate. We have implemented our binds with release Deallocate. What you have explained in your note is ideal for when you are doing program binds.

Our problem is as follows

In a shared region we have a short running DB2 Plan executing and followed by an IMS long running transaction . The thread retains the DB2 locks while the IMS transactions is running and this causes subsequent DB2 transactions running in other regions to abend. [...] 1175 47 35_Re: IMS to DB2 Conversion Products?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Mon, 2 Apr 2001 07:52:22 -0500643_iso-8859-1 Gary, This may seem a foolish question but, if you don't intend to exploit DB2's relational capabilities, why convert?

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or [...] 1223 16 28_Re: Bind Commit / Deallocate15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Mon, 2 Apr 2001 07:49:16 -0500340_- Mohamed

We didn't experienced that. I must admit, I don't understand, why do you got that. Which locks were held. Do you use LOCK table statements. The only locks held after commits are tablespace and table locks. So the short running plan or the long running transaction must use this statement or did lock escalation occur? [...] 1240 52 28_Re: Bind Commit / Deallocate20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM30_Mon, 2 Apr 2001 09:14:45 -0400387_us-ascii We had this discussion just about 2 weeks ago in the listserv, There are many folks who reported problems with release deallocate while protected threads are reused. We have this problem more severe as we have protected thread, which stay for many days or even month. The makes us impossible to alter tables/tablespaces. We also had discussion with IBM lab regarding this. [...] 1293 63 40_Re: Can you bind while reorg is running?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Mon, 2 Apr 2001 08:28:49 -0500598_iso-8859-1 Andre, I would think the reason for your reBINDs would be to exploit new RUNSTATS info after the REORGs, however, you make no mention of RUNSTATS. If this is true you should wait to reBIND after the REORG/RUNSTATS completes, not during.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, [...] 1357 54 52_Pipeline Newsletter for DB2 and Oracle - April Issue9_Cam White20_cwhite@REVEALNET.COM30_Mon, 2 Apr 2001 09:56:07 -0400508_iso-8859-1 The April, 2001 Issue of the Pipeline Newsletter is now available for Oracle and DB2 professionals.

http://www.revealnet.com/newsletter-v2/newsletter_0401.htm

Feature articles include:

- "Oracle 8i Partitioning Methods" - by Vijayabaskar Srinivasan - "DB2 Existence Checking - Good vs. Bad" - by Isaac Yassin - "Make Code Review Part of your Development Process" - by Steven Feuerstein - "Free CSV File Utility" - by Anthony Whitehead - Formatter Plus v1.0 Now Available [...] 1412 55 22_Re: DB2 v6 experiences14_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Tue, 3 Apr 2001 11:43:29 +1000418_us-ascii Dave,

For high performance we must use the new DB2 features well. Don't use every feature just because it is there.

I have had found far less access path problems and far less difficulties in using the new Outer Join as distinct from the old limited Outer Join.

I have also found the new access path Hints work O.K. for me provided Index to PLAN_TABLE set up exactly as recommended. [...] 1468 35 33_DB2 6.0/7.0 Auto-increment (help)23_Luis Paulo Soares Dutra31_luispaulo@FABRICADIGITAL.COM.BR30_Mon, 2 Apr 2001 10:58:28 -0300437_iso-8859-1 Hi,

I made one script to create one table in a database in DB2 7.0: CREATE TABLE Actor (ActorID int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),Name varchar (50) ,constraint Pk_actor Primary Key (ActorID))

My problem is : this command does not work with Db2 6.0 because there is no one field Identity. I thought about using a trigger but I am not sure if this is the best solution. [...] 1504 35 30_RevealNet IDUG Contest Winner!9_Cam White20_cwhite@REVEALNET.COM30_Mon, 2 Apr 2001 10:13:22 -0400502_iso-8859-1 The winner of RevealNet's IDUG Contest is:

Diane Schultheiss, Norfolk Southern Atlanta, Georgia

Diane is the winner of a full-conference registration to IDUG. Travel, hotels & expenses excluded.

Congratulations, Diane!

Cam White RevealNet, Inc.

…do IT smarter™

RevealNet, Inc. P.O. Box 5560 Rockville, MD 20855 301-947-5706

Subscribe to the Pipeline Newsletter for free technical articles, tips and discussions. http://www.revealnet.com [...] 1540 69 35_Re: IMS to DB2 Conversion Products?9_Bob Smith29_rjs900@EMAIL1.DSS.STATE.VA.US30_Mon, 2 Apr 2001 10:13:24 -0400626_iso-8859-1 If that's all you want to do, follow this link: http://www.circle-group.com/Software/Dl2Intro.htm

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > DAVIS, RICK (SBCSI) > Sent: Monday, April 02, 2001 8:52 AM > To: DB2-L@RYCI.COM > Subject: Re: IMS to DB2 Conversion Products? > > > Gary, > This may seem a foolish question but, if you don't intend > to exploit > DB2's relational capabilities, why convert? > > HTH, > Rick Davis > > "This e-mail and any files transmitted with it are the property of SBC, > are confidential, and are intended solely [...] 1610 110 22_Re: Warehouse Question11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID28_Mon, 2 Apr 2001 14:23:45 GMT319_ISO-8859-1 Fabrizio, I totally agree with you. I am facing the same problem as you are. I think what we need is some kind of confirmation from IBM about the platform in which the Warehouse Server can be installed. Because I couldn't find it anywhere, including all the manuals and redbooks available over the net. [...] 1721 50 35_Re: IMS to DB2 Conversion Products?0_22_Rohn.Solecki@MTS.MB.CA30_Mon, 2 Apr 2001 10:06:18 -0500376_us-ascii Rick, a couple of possible real world answers to that question are:

" *&(_#@ Polictics!" - both office and 'real'. ie when we were owned by the government, the 'government' decided to subsidize a local company by telling us to convert an application to run on IBM/DB2. There was no business or technical (the old system was superior) reason for doing it. [...] 1772 65 30_Comparing two rowvalues in DB213_Keerthi Chegu18_lpbusobj@YAHOO.COM30_Mon, 2 Apr 2001 09:39:18 -0500584_- Hello DB2gurus:

I am new to DB2. I have an Oracle background. Currently, I am trying to create a report in Business objects a reporting tool against DB2 database.

The report is looking for missing no.s in Emp.id and I tried the following SQL against DB2:

select 'Missing numbers are' || to_char(a.emplid +1)||' --'|| to_char (b.emplid-1) from (select rownum rnum, emplid from emp where emplid in (select emplid from emp) ) a, (select rownum rnum,empid from emp where emplid in (select emplid from emp))b where a.rnum = b.rnum-1 and a.emplid != b.emplid -1 [...] 1838 79 35_Re: IMS to DB2 Conversion Products?19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Mon, 2 Apr 2001 10:35:49 -0500635_iso-8859-1 Hi Rohn, You've surfaced one of the comments I'd hoped to, thanks.

Best Regards, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly [...] 1918 36 35_Re: IMS to DB2 Conversion Products?9_Stan Hoey24_stan_hoey@CIRCLE-DL2.COM30_Mon, 2 Apr 2001 16:46:27 +0100510_us-ascii Fellow DB2-L'ers,

I've followed this discussion with interest, but I can no longer resist the opportunity for a commercial.

There are 20 sites world-wide running IMS programs that issue XXXTDLI and EXEC DLI calls that DL/2 processes against DB2 databases. We have converted many complicated logically related databases, including recursive BOML-like structures; we have one site peaking at 20 million CICS transactions per day; the largest database we have migrated so far is 15Gb. [...] 1955 79 34_Re: Comparing two rowvalues in DB216_Victor McDonnell15_Vmcdonn@AOL.COM28_Mon, 2 Apr 2001 12:16:18 EDT457_ISO-8859-1 'to_char' is an oracle (and perhaps others) function. In DB2, 'char' is the name of the equivalent function.

Victor McDonnell



In a message dated Mon, 2 Apr 2001 11:24:40 AM Eastern Daylight Time, Keerthi Chegu writes:

<< Hello DB2gurus:

I am new to DB2. I have an Oracle background. Currently, I am trying to create a report in Business objects a reporting tool against DB2 database. [...] 2035 180 36_Re: V5 to V6 - Host variable problem14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Mon, 2 Apr 2001 12:26:30 -0400882_iso-8859-1 Thanks...Kals







"teldb2kals" wrote in message news:210191f5f3.1f5f321019@bigpond.com... > Hi Manas, > > The following APAR might be of help. > (http://techsupport.services.ibm.com/s390/390.uhuic_getrec? > args=DVhuron.boulder.ibm.com+DB390+DA53831+STDSNH312Y+USbin) > > ABSTRACT: > PQ35859: PRECOMPILE ERRORS ON IN(:HV-ARRAY) > DSNH312I E DSNHSMUD AND DSNH104I E DSNHSM2V > > ORIGINATING DETAILS: > Precompile errors on IN(:hv-array) > > LOCAL FIX AS REPORTED BY ORIGINATOR: > none > > RESPONDER SUMMARY: > **************************************************************** > * USERS AFFECTED: Users of the DB2 UDB for OS/390 Precompiler. * > **************************************************************** > * PROBLEM DESCRIPTION: The Precompiler was incorrectly issuing * > * the DSNH312I error when a host * > [...] 2216 38 32_Re: 00C9008E on resouce type 30410_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 2 Apr 2001 18:28:36 +0200395_us-ascii Hi Sri, a short reply (pardon me). I assume that OMegamon limits are the same for Mainview.

RDS (max) limit = more than 25% of table rows accessed, more than 50% RID pool used, no space for next 4096 RIDs

(I think this is max limit)

As for storage , if it's the same as Mainview, you reached 2 GB (no storage), but I don't think so. I think it's the third RDS [...] 2255 106 20_Re: Trigger question10_Shery Hepp17_schepp@SRPNET.COM30_Mon, 2 Apr 2001 09:43:09 -0700438_iso-8859-1 Hi Steve- Thanks for the response. I'm not sure if what I'm trying to accomplish in this trigger is invalid for triggers or if I have a syntax problem. Basically I want something like this

BEGIN ATOMIC CASE WHEN (N.PO_STATUS = 'D' AND O.PO_STATUS <> 'D') THEN CALL X0DB27.SP11203 ............... else WHEN (N.PO_STATUS IN ('C','X') AND O.PO_STATUS NOT IN ('C','X') THEN CALL X0DB27.SP11204 .................. End - [...] 2362 110 34_Re: Comparing two rowvalues in DB213_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 2 Apr 2001 12:18:20 -0500337_iso-8859-1 Keerthi,

DB2 non/OS390 V7 has the ROW_NUMBER function which I'm assuming is similar to Oracle's. Unfortunately it is not available on OS/390 DB2.

Here is a couple of SQLs I just whipped up to determine gaps in numbering (I did have similar already in use for date ranges). Neither of which are very pretty: [...] 2473 60 66_Re: Application performance problem: What information do yoy need?0_26_mark.walker@CUNAMUTUAL.COM30_Mon, 2 Apr 2001 12:34:42 -0500458_us-ascii I'm coming into this late, but one thing I like to see is hardcopy from a monitor showing that the problem really exists. Too often we chase our tails going after the wrong problem.

Mark Walker







(Embedded image moved Carlton Enuda @RYCI.COM> to file: 03/23/2001 05:09 PM pic32391.pcx)







Please respond to DB2 Data Base Discussion List [...] 2534 135 22_Re: Warehouse Question15_Paul S Rockwood19_rockwood@US.IBM.COM30_Mon, 2 Apr 2001 14:09:20 -0400493_iso-8859-1 Fabrizio -

Please pardon my mistake. Your are correct, the Warehouse Manager server only runs on NT at this time. It will eventually run on the other platforms, but I do not have an update on the time frame now.

Paul

Paul S. Rockwood IBM Software I/T Specialist Certified Solutions Expert - DB2 UDB v7.1 6710B Rockledge Drive Bethesda, MD 20817 301-803-3733 DB2 UDB Version 7 - Find out what all the excitement is about at http://www.ibm.com/software/data [...] 2670 89 67_Re: Application performance problem: What information do yoy need ?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Mon, 2 Apr 2001 13:21:40 -0500362_- No, kidding. We get that too. An example is the parallel jobs thread I started the other day. I just got some information from our performance group that says the jobs in question ran for the same length of time last June. Very often the customer's perception can vary greatly from actual data. I am still pursuing the problem but I am growing skeptical. [...] 2760 17 17_OS/390 DB2 Access17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Mon, 2 Apr 2001 11:40:57 -0700501_us-ascii I am attempting to find alternatives for accessing DB2 on OS/390 from a windows workstation. We currently use a DB2 Connect, TCP/IP and DDF scenario. What other access methods are there? There is no web access so we don't require Net.Data.

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. 2778 37 21_Re: OS/390 DB2 Access13_Walter Davies26_wdavies@CO.EL-DORADO.CA.US30_Mon, 2 Apr 2001 11:57:03 -0700743_iso-8859-1 Merant has a driver that we are using in our company for visual basic to OS390 DB2 V5.



Walter -----Original Message----- From: Charles Jambrosic Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Monday, April 02, 2001 10:41 AM Subject: OS/390 DB2 Access



>I am attempting to find alternatives for accessing DB2 on OS/390 from >a windows workstation. We currently use a DB2 Connect, TCP/IP and >DDF scenario. What other access methods are there? There is no >web access so we don't require Net.Data. > >TIA > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage [...] 2816 33 34_Re: Comparing two rowvalues in DB211_Rolf Loeben17_loeben@DE.IBM.COM30_Mon, 2 Apr 2001 21:24:08 +0200589_us-ascii Terry,

there is something comparable in DB2 for z/OS and OS/390 V7, see SQL Reference under IDENTITY_VAL_LOCAL.

"Examples Example 1: Set the variable IVAR to the value assigned to the identity column in the EMPLOYEE table. The value returned from the function in the VALUES statement should be 1. CREATE TABLE EMPLOYEE (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY, NAME CHAR(30), SALARY DECIMAL(5,2), DEPTNO SMALLINT); INSERT INTO EMPLOYEE (NAME,SALARY,DEPTNO) VALUES ('Rupert',989.99,50); VALUES IDENTITY_VAL_LOCAL()INTO :IVAR; IDENTITY_VAL_LOCAL " Rolf Loeben [...] 2850 13 18_Table Modification12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Mon, 2 Apr 2001 14:36:00 -0400453_- Is there a way to tell if a table has been modified without doing a compare of data? We have a process which unloads a very large table nightly. If the table has not changed, there is no need to do the unload.

================================================ 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. 2864 42 49_Same variable on split screens or different lpars12_Vernon, John27_John.Vernon@TRS.STATE.TX.US30_Mon, 2 Apr 2001 14:53:03 -0500542_iso-8859-1 I have some DB2 screens that use a variable DSNEOV01 that is vput to the profile. The screens also vget DSNEOV01 from the profile. This variable sets the DB2 system id. So if a person goes to the DB2 screen and chooses one system (test for example) and then gets into the DB2 screens from a split screen or even a different LPAR and chooses a different system, he resets the system id for the first screens. If he gets into the production system the 2nd time, he resets the system id to production on the first screens he got [...] 2907 52 22_Re: Table Modification19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Mon, 2 Apr 2001 15:04:03 -0500363_iso-8859-1 Hi Bob, Someone will probably have a faster way to do this and I hope they do. Until then, couldn't you audit the table for update, delete, and insert? Then check before you ran the unload? Of course you'd have to weigh the cost of audit (on all the time, concurrency issues, time for the audit) against the time to unload to see if its worth it. [...] 2960 93 40_Re: Can you bind while reorg is running?45_=?iso-8859-1?Q?=22LeBlanc=2C_Andr=E9-ITB=22?=30_AndreD.Leblanc@CCRA-ADRC.GC.CA30_Mon, 2 Apr 2001 16:06:14 -0400538_iso-8859-1 Thanks for the response Rick.

The reason for the binds is new application code. We're just trying to take advantage of the application outage to run some reorgs which won't fit in our regular maintenance window. The current runstats for this table are still valid.

Thanks, André LeBlanc AndreD.LeBlanc@ccra-adrc.gc.ca

-----Original Message----- From: DAVIS, RICK (SBCSI) [mailto:rd8246@SBC.COM] Sent: Monday, April 02, 2001 9:29 AM To: DB2-L@RYCI.COM Subject: Re: Can you bind while reorg is running? [...] 3054 40 27_Disaster Recovery Checklist11_Clark, Tony26_Tony.Clark@TWC.STATE.TX.US30_Mon, 2 Apr 2001 15:04:27 -0500404_- Hi:

I am preparing for a offsite disaster recovery exercise using DB2 data sharing where I will be recovering to a point of consistency using LSRNs. I was wondering if anyone has actually done this type of recovery, and if they would be willing to share procedures and/or any information concerning the problems they might have encountered in starting up DB2 at the disaster recovery site. [...] 3095 68 34_Re: Comparing two rowvalues in DB213_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 2 Apr 2001 15:02:11 -0500473_us-ascii Rolf,

These are entirely different.

An identity column is used to generate the next sequential number (dependent of course on the increment value) upon insert into the table; with IDENTITY_VAL_LOCAL as the last value generated.

The ROW_NUMBER function assigns a row number to the retrieved rows from a SELECT statement, assigned in the sequence of the ORDER BY within the function (or based on retrieval sequence if no ORDER BY) such as: [...] 3164 107 22_Re: Table Modification16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Mon, 2 Apr 2001 21:22:27 +0100610_- Good Evening Bob -

If you have access to a Log Analysis tool (eg. CA/PLATINUM LogAnalyzer - and I'm sure BMC can as well) you can create a summary report telling the number of inserts, updates and/or deletes within a certain time/RBA/LRSN range.

Steen Rasmussen

> -----Original Message----- > From: BOB JEANDRON [SMTP:BOB.JEANDRON@USDA.GOV] > Sent: Monday, April 02, 2001 8:36 PM > To: DB2-L@RYCI.COM > Subject: Table Modification > Sensitivity: Personal > > Is there a way to tell if a table has been modified without doing a compare of > data? > We have a process which unloads a [...] 3272 17 44_Submitting a job from a DB2 Stored procedure22_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM30_Mon, 2 Apr 2001 14:20:01 -0700462_iso-8859-1 Hello DB2 Gurus, I am exploring the possibility of submitting a JOB to JES from a DB2 stored procedure. Can anybody help me with that ? A sample code and direct email will be highly appreciated ! Thanks Ramesh

================================================ 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. 3290 182 20_Re: Trigger question14_Grainger, Phil20_Phil.Grainger@CA.COM30_Mon, 2 Apr 2001 22:48:28 +0100421_iso-8859-1 Shery,

Lateral answer to your question.

You could get your trigger to call ONE stored procedure and let IT decide what it has to do. Of course, I've now reopened the debate about logic in SQL vs. logic in application code (sorry)

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 3473 107 22_Re: Table Modification24_Koleto, Kenneth M. [SLC]21_KKoleto@CSCUS.JNJ.COM30_Mon, 2 Apr 2001 16:43:11 -0400576_- Bob, I think the REPORT option of IMAGECOPY might be able to help you. You can set a condition code based on the amount of changes you specify. The unload step would depend on the condition code.

Ken

> -----Original Message----- > From: BOB JEANDRON [SMTP:BOB.JEANDRON@USDA.GOV] > Sent: Monday, April 02, 2001 2:36 PM > To: DB2-L@RYCI.COM > Subject: Table Modification > Sensitivity: Personal > > Is there a way to tell if a table has been modified without doing a > compare of > data? > We have a process which unloads a very large table nightly. If the [...] 3581 132 36_Re: V5 to V6 - Host variable problem14_Grainger, Phil20_Phil.Grainger@CA.COM30_Mon, 2 Apr 2001 23:55:21 +0100446_iso-8859-1

I have a horrible feeling that what you have found is something that should NOT have worked in V5 and has now been fixed in V6.

The :HV in your IN LIST, as far as I know, should be the same definition as the column you are comparing it with. I'd be surprised if DB2 processed an IN LIST host variable the way it is defined here. I also suspect that in V5, DB2 may just be looking at the first 8 bytes of the field! [...] 3714 23 13_SQL code -31313_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Mon, 2 Apr 2001 19:00:21 -0400 3738 85 22_Re: Table Modification0_22_Rohn.Solecki@MTS.MB.CA30_Mon, 2 Apr 2001 18:03:43 -0500627_iso-8859-1 Actually in V6 there is another option I just 'found' (while looking up the report option) that will do exactly what he wants.

CHANGELIMIT Specifies the percent limit of changed pages in the table space, partition, or data set when an incremental or full image copy should be taken.



percent_value1 Specifies a value in the CHANGELIMIT range. percent_value1 must be an integer or decimal value from 0 to 100. You do not need to specify leading zeroes, and the decimal point is not required when specifying a whole integer. Specify one decimal place for a decimal value (for example, [...] 3824 22 19_Re: DB2 Conferences12_Roger Miller19_millerrl@US.IBM.COM30_Mon, 2 Apr 2001 17:44:07 -0500401_- There is a list of conferences on www.db2usa.com Then click on conferences

IDUG 13th North American conference: presentations agenda: Orlando (USA) 13-17 May 2001. Share 97 North American: Minneapolis, MN (USA) 22 - 27 July 2001. DB2 and Business Intelligence: IBM technical conferences, Orlando (USA) 1-5 October 2001. IDUG 10th European conference: Florence (Italy) 8-11 October 2001. [...] 3847 54 21_Re: OS/390 DB2 Access18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Tue, 3 Apr 2001 08:41:23 +0930586_us-ascii Howzit Charles?

Have a look at "Possible Client-to-Server Connectivity Scenarios" in the "Installation and Configuration Supplement" manual.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia









Charles Jambrosic @RYCI.COM> on 03/04/2001 04:10:57

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: OS/390 DB2 Access [...] 3902 31 58_Re: CPU Accounting Overhead for DB2 Accounting Class 2 & 312_Roger Miller19_millerrl@US.IBM.COM30_Mon, 2 Apr 2001 18:03:53 -0500612_- There is a fairly careful statement about DB2 traces in the Administration Guide, under the topic, Minimize the Use of DB2 Traces. Here is a Version 7 paste, from chapter 26.

Accounting and statistics traces Enabling accounting class 2 along with accounting classes 1 and 3 provides additional detail relating directly to the accounting record IFCID 0003, as well as recording thread level entry into and exit from DB2. This allows you to separate DB2 times from application times. Running accounting class 2 does add to the cost of processing. How much overhead occurs depends on how much SQL the [...] 3934 227 17_Re: SQL code -31313_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 2 Apr 2001 18:54:10 -0500352_iso-8859-1 Carlton,

Since the error message suggests that the EXECUTE are OPEN have an incorrect number of parameter markers, and you haven't mentioned these, then I'll tell what looks wrong with the SQL you have supplied.

Between the first ON clause and the next LEFT OUTER JOIN is a comma and table name which should not be there. [...] 4162 51 22_Re: Table Modification18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Tue, 3 Apr 2001 11:20:05 +0930729_us-ascii Howzit Bob?

You can check the modified page indicators.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia









BOB JEANDRON @RYCI.COM> on 03/04/2001 04:06:00

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Table Modification





Is there a way to tell if a table has been modified without doing a compare of data? We have a process which unloads a very large table nightly. If the table has not changed, there is no need to do the unload. [...] 4214 18 36_timout setting for command processor25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG30_Tue, 3 Apr 2001 10:11:30 +0800419_us-ascii Hi,

May I know where is the timeout setting for dsn commands such as -sto db(x) space(y). Is it calculated from settings in Zparm. Please let me know.

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. 4233 68 28_Re: Bind Commit / Deallocate14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Mon, 2 Apr 2001 21:35:51 -0500368_iso-8859-1 In one detailed measurement of a production system, protected threads, high volume CICS - close to 2.5% CPU reduction with RELEASE(DEALLOCATE). They did not have the 24x7 problem you have, which is entirely another issue. A 2% reduction can mean x more transactions get through the system.

But it is more than a CPU issue as has been discussed. [...] 4302 70 22_Re: Table Modification12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Tue, 3 Apr 2001 12:25:52 +1000298_- I don't have the Diagnosis manual at hand with the Header page format, but I believe there is an LRSN (RBA) on the header page that indicates I think indicates when then the space was last updated (but it might be last closed) you'll need to check.

It's used in down level detection. [...] 4373 69 17_Re: SQL code -31316_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Tue, 3 Apr 2001 10:06:31 +0530572_- HI Carlton,

Can't exactly say as the SQL given by you shows WHERE predicate "P.C_SEQ_NUM = " is appearing twice. Are you sure this is the concerned SQL ? If at all Yes, why once it is compared with ? and then with host variables.

Regards Sanjeev

> -----Original Message----- > From: Carlton Enuda [SMTP:carltonenuda@HOTMAIL.COM] > Sent: Tuesday, April 03, 2001 4:30 AM > To: DB2-L@RYCI.COM > Subject: SQL code -313 > > Hi everyone, > I'm trying to test a SQL and gets the error shown following this text. > What is it trying to tell me? My SQL [...] 4443 150 20_Re: Trigger question15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM30_Tue, 3 Apr 2001 14:49:55 +1000534_iso-8859-1 The CREATE TRIGGER section SQL manual doesn't specify CASE as an allowable Triggered Action statements.

I've tried and I don't seem to be able to do it, so possibly the END and END CASE tokens needed for the BEGIN ATOMIC and CASE statements respectively are confusing the parser? That's what seems to be occurring on my OS/390 system anyway. Even when I attempt to reduce it to a single SQL CASE statement without the 'BEGIN ATOMIC ... ; END' construct around I still have a problem referring to the END token. [...] 4594 130 36_Re: V5 to V6 - Host variable problem10_teldb2kals22_teldb2kals@TELSTRA.COM30_Tue, 3 Apr 2001 15:43:37 +1000387_us-ascii Phil,

Interestingly, the v6 SQL Reference has the same example, but with the host structure.

01 EMPNO-STRUCTURE. 05 CHAR-ELEMENT-1 PIC X(6) VALUE '000140'. 05 CHAR-ELEMENT-2 PIC X(6) VALUE '000340'. 05 CHAR-ELEMENT-3 PIC X(6) VALUE '000220'. . . . EXEC SQL DECLARE PHCURS CURSOR FOR SELECT PHONENO FROM DSN8610.EMP WHERE EMPNO IN (:EMPNO-STRUCTURE) END-EXEC. [...] 4725 41 22_Re: Table Modification10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 3 Apr 2001 09:53:54 +0200402_us-ascii Hi....

When I need this infos I try to use (if possible) SYSLGRNX table, i.e I do a REPRO on a flat file (you don't need to stop

DB2) and with a trivial REXX exec (currently developing, it's a beta-test REXX exec) based on description of SYSLGRNX

records (see Diag. Guide and Ref.) you can detect the XXIDs of the object (so you can select only the records you need) [...] 4767 84 28_Re: Bind Commit / Deallocate14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Tue, 3 Apr 2001 09:10:09 +0100355_iso-8859-1 In our shop, ALL of our packages (online and batch) are bound RELEASE(DEALLOCATE). Coincidently or not, our EDM pool fills up regularly, with packages and skeleton packages taking up >70% of the thing. It has been suggested that RELEASE(COMMIT) for less-frequently-used online packages would alleviate our problem. (We do not data-share.) [...] 4852 37 43_Re: end of service dates - db2 connect v5.x15_Lucchetti Bruno22_B.Lucchetti@BARILLA.IT30_Tue, 3 Apr 2001 10:23:12 +0200405_iso-8859-1 Hi Guru,

Referring to Db2 Connect Personal Edition,of which are available several versions, I have a question. We are running DB2 UDB for OS/390 V5R1 and using DB2 Connect PE 5.2 on Windows 95 and Windows NT. We are planning to migrate:

a) from DB2 UDB for OS/390 V5R1 to V6R1 b) from Windows 95 & Windows NT to Windows 2000 c) from DB2 Connect PE 5.2 to DB2 Connect PE ?,?. [...] 4890 14 28_Re: Bind Commit / Deallocate15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Tue, 3 Apr 2001 03:42:56 -0500408_- Markus

Are they all in use? I just looked at the EDM pool using OMEGAMON. Our values are: 44% SKPTs and 3% PTs and the system is running since 24th of march.

================================================ 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. 4905 64 28_Re: Bind Commit / Deallocate14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Tue, 3 Apr 2001 10:20:29 +0100413_iso-8859-1 Here's a snapshot of our EDM pool. It's healthy at the moment, but at peak time it's poor. # PAGES % PAGES ------- ------- FREE PAGES 3,291 25.4 SKCT'S 7 0.1 SKPKG'S 5,544 42.8 SQL CACHE 0 0.0 CT'S 17 0.1 DBD'S 260 2.0 PKG'S 3,842 29.6 TOTAL 12,961

Marcus Davage > 'The views expressed are my own and do not necessarily represent the > views or policy of my employer, Lloyds TSB Bank plc.' [...] 4970 38 40_Re: timout setting for command processor11_David Hayes28_Dave.Hayes@MORGANSTANLEY.COM30_Tue, 3 Apr 2001 10:15:00 +0100624_us-ascii Srirengan

The timeout period for DSN commands will be 10 times your 'normal' timeout period. All timeout periods are calculated to include a multiplier. For example online and batch processing, or SQL issued through, say, QMF, have a timeout multiplier of 1 i.e. they will timeout after the specified timeout period has elapsed (this figure is actually calculated using Resource Timeout and Deadlock Time). Stop Database commands use a multiplier of 10, binds have a multiplier of 3 and utilities have a default multiplier of 6. There is a useful section in the DB2 Admin Guide (5.7.5.2.3 Wait time for [...] 5009 13 20_Re: Trigger question15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Tue, 3 Apr 2001 04:10:59 -0500489_- Well the syntax of the CASE-expression is invalid, because in the WHEN- clause there can only be an expression, but no call to a stored procedure. I think, if you use CASE according to the syntax rules, then you can use it in a triggered action.

================================================ 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. 5023 26 28_Re: Bind Commit / Deallocate15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Tue, 3 Apr 2001 04:18:06 -0500544_- And here is a snopshor of one of our production data sharing member:

EDM % of Pages Storage Type Pool Alloc ------------ ------ ------- DBDs 38.9% 14596.0 CTs .1% 33.0 PTs 2.5% 937.0 SKCTs 3.9% 1456.5 CACHE .0% 7.5 SKPTs 44.4% 16655.0 FREE 10.2% 3815.0

Looks a little bit different. Did you translate some entries, especially the PKG'S don't appear in our snapshot. Also I am a little bit surprised about your DBD value or may be our's is somehow way-out, I don't know. It would be very interesting to see other snapshots. [...] 5050 105 34_Re: Comparing two rowvalues in DB212_Kent Collins15_kcollins@WF.NET30_Tue, 3 Apr 2001 06:34:47 -0500459_iso-8859-1 While "char" is close, it does not provide the same functionality as to_char. It is one of those conversion issues, Oracle to DB2.

Robert M. Collins Jr ( Kent ) IBM Certified DB2 UDB DBA Phone: 940.241.2242 Email: kcollins@wf.net ----- Original Message ----- From: "Victor McDonnell" Newsgroups: bit.listserv.db2-l To: Sent: Monday, April 02, 2001 11:16 AM Subject: Re: Comparing two rowvalues in DB2 [...] 5156 94 34_Re: Comparing two rowvalues in DB212_Kent Collins15_kcollins@WF.NET30_Tue, 3 Apr 2001 06:42:32 -0500592_iso-8859-1 DB2 V5? Yes there are stored procedures in 5 but external only and nothing like PL/SQL. However, you could easily write a C program and compare rows. In V5 there is nothing like round that I am aware.

You can write standard SQL to compare rows between 2 tables.

Robert M. Collins JR ( Kent ) IBM Certified DB2 UDB DBA Phone: 940.241.2242 Email: kcollins@wf.net ----- Original Message ----- From: "Keerthi Chegu" Newsgroups: bit.listserv.db2-l To: Sent: Monday, April 02, 2001 9:39 AM Subject: Comparing two rowvalues in DB2 [...] 5251 36 48_Re: Submitting a job from a DB2 Stored procedure15_Lucchetti Bruno22_B.Lucchetti@BARILLA.IT30_Tue, 3 Apr 2001 13:51:19 +0200579_iso-8859-1 In the jcl for the Stored procedures STC we have:

//* to submit job from a stored procedures //D5248U DD SYSOUT=(A,INTRDR)



We have a generalized cobol program (linkedit with PARM='XREF,LET,LIST,AMODE(31),RMODE(ANY),RENT,REUS,NCAL' that is called by the stored procedures that need to submit the job. The caller passes the needed informations to identify the job to be submitted and whatever we need. The generalized program creates in some WORKING STORAGE fields the JCLs and writes them to DD D5248U (in the JCL for stored procedures STC). [...] 5288 35 34_Re: Comparing two rowvalues in DB213_Stefan Jahnke27_stefan.jahnke@D2VODAFONE.DE30_Tue, 3 Apr 2001 14:29:29 +0200553_us-ascii Hi,

I'm actually having the same sort of problems. I have a strong Oracle background, but now I'm involved in a migration project Oracle 8.0.6 -> DB2 5.2. I'm looking for DB2 equivalents to PL/SQL (isn't there a procedural language extension to DB2 that is only available from version 7 on ?). Since I'm on 5.2, what do I have to do ? Write external procs / funcs ? What language do you recommend (C/C++ or Java ?). Is there a class library available (like Oracle Foundation Classes on Windows), or do I have to get down to the CLI [...] 5324 32 69_MQ Series and Stored Procedure Builder - Anyone using them togeth er?15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Tue, 3 Apr 2001 08:02:05 -0500495_iso-8859-1 A colleague of mine has a problem with MQ Series and Stored Procedure Builder. Here is a description of the problem as she sees it.

"The problem as I understand it, is that MQ will initiate this stored procedure. The stored procedure attempts to do an MQ GET to get the parms from MQ. Store Procedure Builder is not recognizing the MQ calls, so the build fails. It almost seems like we need a "pre-compiler" for MQ. Any help you can provide would be greatly appreciated." [...] 5357 31 52_db2 7.1 EEE Redirected restore and Sortheap problems11_Mike Fatula17_mfatula@PHEAA.ORG30_Tue, 3 Apr 2001 10:23:35 -0400482_us-ascii Has anyone experienced this problem when performing a redirected restore. This also causes sortheap problems when the restore fail -----------help---------

set tablespace containers for 129 using (file '/db2/tlgcpaem/tfd03lgc1' 1000) DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

set tablespace containers for 130 using (file '/db2/tlgcpaem/tfd04lgc1' 1000) DB20000I The SET TABLESPACE CONTAINERS command completed successfully. [...] 5389 40 87_Does anyone have a method of turning off the "RETVLCFK=YES" option for specific queries0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM30_Tue, 3 Apr 2001 11:02:25 -0500312_us-ascii I am interested in using the "RETVLCFK=YES" option. I know of several queries that will benefit greatly with this option. However, I also know of some queries in the shop that cannot tolerate this option. I have considered two options that should allow us to us this option. These two options are: [...] 5430 79 53_Re: Same variable on split screens or different lpars11_Suresh Sane21_data_arch@HOTMAIL.COM30_Tue, 3 Apr 2001 11:09:36 -0500659_- John,

Have you considered using FUNCTION pool or the SHARED pool (instead of the PROFILE pool)? I think that will retain a copy of the variable per session/per split screen.

HTH.

Suresh



>From: "Vernon, John" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Same variable on split screens or different lpars >Date: Mon, 2 Apr 2001 14:53:03 -0500 > >I have some DB2 screens that use a variable DSNEOV01 that is vput to the >profile. The screens also vget DSNEOV01 from the profile. This variable >sets >the DB2 system id. So if a person goes to the [...] 5510 165 48_Re: Submitting a job from a DB2 Stored procedure14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Tue, 3 Apr 2001 09:34:53 -0700430_iso-8859-1 I would suggest that you may want to write an assember program that dynamically allocates the internal reader when needed, and frees it when you are done with it. Internal Readers are/can be limited resources, and when they are allocated in JCL you may have problems at startup if none are available. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. [...] 5676 17 13_DB2 Estimator13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Tue, 3 Apr 2001 12:38:49 -0400 5694 30 35_FIFO vs LRU for Sortwork bufferpool10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Tue, 3 Apr 2001 11:53:43 -0500594_us-ascii DB2ers:

I have been wanting to explore setting the PGSTEAL for our isolated DSNDB07 sortwork bufferpool from LRU to FIFO.

A colleague of mine has said he remembers reading (somewhere) that this is a POOR choice for sortwork bufferpools because they actually get re-read/used more than you would think.

Does anyone have a feel for this option - or dare I ask for the myriad opinions available on this listserve? Have any IBM or user benchmarks been run that you would be willing to share with us? Are there shops using or that have tried the FIFO option - did [...] 5725 13 17_Re: DB2 Estimator14_Kurt Wickhorst28_Kurt.Wickhorst@METAVANTE.COM30_Tue, 3 Apr 2001 12:04:25 -0500329_us-ascii yes you can.. The help screens provide good examples of how to accomplish this

================================================ 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. 5739 57 34_Re: Comparing two rowvalues in DB212_Roger Miller19_millerrl@US.IBM.COM30_Tue, 3 Apr 2001 11:48:16 -0500392_- If the table has them, the ROWID is probably the closest we have to a rownum in brand O. There are quite a few hints for conversions in several recent red books - or maybe an older one for older DB2 releases.

Converting from Oracle AIX to DB2 for OS/390, SG24-5478 DB2 UDB V7.1 Porting Guide, SG24-6128 DATABASE 2 for AIX Conversion Guide Oracle 7.1 to DB2 Version 2, SG24-2567 [...] 5797 117 36_Re: V5 to V6 - Host variable problem16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Tue, 3 Apr 2001 10:18:44 -0700482_iso-8859-1 Now that's a funny thing. The v6 manual has the same illustration as Phil mentioned for v7:

01 EMPNO-STRUCTURE. 05 CHAR-ELEMENT-1 PIC X(6) VALUE '000140'. 05 CHAR-ELEMENT-2 PIC X(6) VALUE '000340'. 05 CHAR-ELEMENT-3 PIC X(6) VALUE '000220'. . . . EXEC SQL DECLARE PHCURS CURSOR FOR SELECT PHONENO FROM DSN8610.EMP ---> WHERE EMPNO IN ---> (:EMPNO-STRUCTURE.CHAR-ELEMENT-1, ---> :EMPNO-STRUCTURE.CHAR-ELEMENT-2, ---> :EMPNO-STRUCTURE.CHAR-ELEMENT-3) END-EXEC. [...] 5915 31 32_Integer Function in DB2 V5/OS39013_Keerthi Chegu18_lpbusobj@YAHOO.COM30_Tue, 3 Apr 2001 12:15:36 -0500670_- Hi All:

The following query gives me an error:

select integer(substr(inv_nbr,1,5)) from inv_t

or

select integer(substr(inv_nbr,1,5)) - 1 from inv_t



inv_nbr is type Character

I checked the syntax in the DB2 book and it seems correct.

error: SQL0171N The data,length or type of argument 1 of INTEGER is incorrect.

What is the correct syntax?

Thanks! Keerthi

================================================ 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. 5947 31 21_Re: OS/390 DB2 Access17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Tue, 3 Apr 2001 14:43:42 -030063_iso-8859-1 What´s the problem with DB2 connect?



5979 48 36_Re: Integer Function in DB2 V5/OS39011_David Nance16_DWNance@FHSC.COM30_Tue, 3 Apr 2001 13:50:38 -0400450_US-ASCII Try checking every row of data in your table. Is there any row that does not have a number in your substr?

Dave Nance First Health Services, Corp. (804)527-6841

>>> lpbusobj@YAHOO.COM 04/03/01 01:15PM >>> Hi All:

The following query gives me an error:

select integer(substr(inv_nbr,1,5)) from inv_t

or

select integer(substr(inv_nbr,1,5)) - 1 from inv_t



inv_nbr is type Character [...] 6028 72 39_Re: FIFO vs LRU for Sortwork bufferpool11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM30_Tue, 3 Apr 2001 12:55:11 -0500607_us-ascii Missy this is what Martin Hubel says in a white paper at http://www.responsivesystems.com/

BP Page Stealing Algorithm The buffer pool page stealing algorithm was always least recently used (LRU) before DB2 V6. This means that a highly used page can remain in the pool based on its continued use. There is some overhead associated with maintaining the LRU chain. First in first out (FIFO) means the oldest page will be removed first. FIFO may improve performance if the BP size allows objects to be fixed in the pool, or the objects are sufficiently large that there is little chance of [...] 6101 24 36_Re: Integer Function in DB2 V5/OS39013_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Tue, 3 Apr 2001 13:57:45 -0400607_iso-8859-1 Keerthi, Looks to me like your syntax is ok. I suspect some of your columns may not have valid numeric data.

This works

>>> select int(substr('1234366',1,4)) -1, name from staff

Can you verify that in every row inv_nbr contains numbers?

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. 6126 28 91_Re: Does anyone have a method of turning off the "RETVLCFK=YES" option for specific queries12_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Tue, 3 Apr 2001 13:22:02 -0500343_us-ascii Larry:

This value is set in the zparm gen as the last bit flag in SPRMMIS2. The you could set up two zparms and bring up the system with the bit set and then do your binds and run with the bit unset as the default zparm. In V7 this is documented as an updateable filed, which means you could switch without recycling DB2. [...] 6155 62 28_Re: Bind Commit / Deallocate13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Tue, 3 Apr 2001 11:22:02 -0700326_iso-8859-1 Walter, looks like your DBD's might be large in comparison to mine and Marcus's. BTW, his PKG'S is equivalent to your PTs. Here is a snapshot of mine, overallocated as it is. I recently reduced it by 5157 pages and I still maintain 99.9% hit ratios. All of our online plans/pkgs are bound RELEASE(DEALLOCATE). [...] 6218 75 21_Re: OS/390 DB2 Access17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Tue, 3 Apr 2001 11:32:27 -0700412_iso-8859-1 Absolutely nothing is wrong with DB2 Connect. I was asked if there were any alternatives to connect a workstation to a host DB2 OS/390 and except for some expensive alternatives I really didn't see any others.







Walter Trovijo Jr To: DB2-L@RYCI.COM Subject: Re: OS/390 DB2 Access Sent by: DB2 Data Base Discussion List [...] 6294 38 37_NYC IBM Tools Fair, Monday April 16th11_Gerri Lloyd17_glloyd@US.IBM.COM30_Tue, 3 Apr 2001 12:55:13 -0600337_us-ascii Do you want to know more about IBM's IMS and DB2 Tools recently announced offerings?

Come to IBM's Session on S/390 Data Management Tools for DB2 and IMS Seminar When: Monday, April 16, 2001 Time: 9am-4pm (Continental Breakfast and Lunch to be provided) Where: IBM 590 Madison Avenue Room 609/610, New York NY 10022 [...] 6333 100 21_Re: OS/390 DB2 Access22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM30_Tue, 3 Apr 2001 15:02:05 -0400636_iso-8859-1 StarSQL Pro from StarQuest competes with DB2 Connect.



Mike Piontkowski Technisource for IBM Global Services Service Delivery Center - Northeast Voice: +1 302.886.4612 Fax: +1 302.886.4612



-----Original Message----- From: Charles Jambrosic [mailto:CJambrosic@CSIHOME.COM] Sent: Tuesday, April 03, 2001 14:32 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] OS/390 DB2 Access



Absolutely nothing is wrong with DB2 Connect. I was asked if there were any alternatives to connect a workstation to a host DB2 OS/390 and except for some expensive alternatives I really didn't see any others. [...] 6434 22 20_DB2 Today Newsletter13_Morrill, John12_JohnM@VP.NET30_Tue, 3 Apr 2001 13:58:16 -0600509_iso-8859-1 Note: This is a summary of a DB2 Interested Parties newsletter.

Below is a link to IBM's online DB2 newsletter, "DB2 Today". DB2 Today is published monthly and summarizes key IBM data management related news since the last issue. News items are very short with links back to details on the IBM Data Management Internet site. An edition is produced for each geography and subscribers can define a "My DB2 Today" profile so that the summary is tailored to your individual interest areas. [...] 6457 75 102_Report: Mark your calendar for IDUG 2001 - Technical Symposium, N ovember 8-9, 2001 in Toronto, Canada13_Morrill, John12_JohnM@VP.NET30_Tue, 3 Apr 2001 13:59:21 -0600574_- Dear DB2 Interested Parties,

Below is a brief bulletin about the IDUG 2001 - Technical Symposium, November 8-9, 2001 in Toronto, Canada. This IDUG event offers an alternative to the IDUG conference format, providing 2 days of in-depth DB2 technical education, plus IBM DB2 developer panel and keynote sessions.

Request for Proposal responses are now being accepted for the Education Seminars (half-day and full-day) that will comprise the program. The RFP is available from the IDUG Web site (URL included below). Submissions are due by April 15, 2001. [...] 6533 54 98_Repost: March 30, 2001 Availability Annouced for IBM DB2 UDB Serv er for OS/390 and z/OS Version 713_Morrill, John12_JohnM@VP.NET30_Tue, 3 Apr 2001 14:01:56 -0600491_- Note: This repost has been edited for length.

Dear DB2 Interested Parties,

Last week, March 30, 2001 was announced as the general availability date for Version 7 of DB2 for OS/390 and z/OS. Below is a brief bulletin about the announcement (note the URLs for additional information at the bottom). The full announcement letter (201-054) is available from IBMLink at http://www.ibmlink.ibm.com/ (e.g., for the USA, at http://www.ibmlink.ibm.com/usalets&parms=H_201-054 ). [...] 6588 169 53_Trigger question and UDF question, URGENT PLEASE HELP19_Krishna Vitaldevara19_krishna@BRIENCE.COM30_Tue, 3 Apr 2001 13:54:01 -0700394_iso-8859-1 Hi ALL,

PLEASE HELP

I have two questions.

1) I am not able to do a simple stored procedure call from a trigger. I am using DB2 UDB Version 7 for windows.

I get a message

"[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "CALL TESTPROC" was found following "ACH ROW MODE DB2SQL". Expected tokens may include: "". SQLSTATE=42601 " [...] 6758 22 37_When should a ONLINE SQL run in batch12_Clark, Kevin15_KClark@FDIC.GOV30_Tue, 3 Apr 2001 17:18:13 -0400524_- All,

I'm trying to developed thresholds for online SQL. Any ideals CPU time., Elapsed time, etc...

I thinking that using 100,000 or more SQL statements is a batch candidate

Anyone have an other criteria that provide to applications.



Kevin Clark

================================================ 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. 6781 39 32_Row materialization at OPEN time12_Kazi Hossain30_kazi.x.hossain@MAIL.SPRINT.COM30_Tue, 3 Apr 2001 15:58:59 -0500334_- One of our production batch job is getting about an average 4 rows per OPEN CURSOR. Rows are being materialized at FETCH time, without any Bufferpool hit. Access is 'read only'. Two questions:

Why no Bufferpool hits, while the rows should be on the same page ? What can we do to make the rows materialize at OPEN time ? [...] 6821 150 36_Re: V5 to V6 - Host variable problem15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM30_Wed, 4 Apr 2001 07:30:35 +1000405_iso-8859-1 When we were V5 we tried defining a Group level and got either a Run time error or Bind error (so the following Pre-compiled OK but fell over later).

SELECT PHONENO FROM DSN8510.EMP ---> WHERE EMPNO IN (:EMPNO-STRUCTURE)



regards, Steve

I 2nd Floor 484 St Kilda Rd, Melbourne

' (03) 9865 8557 7 (03) 9804 5368 * [...] 6972 33 13_SQL Date Math10_Chris Kern23_Chris_Kern@VANGUARD.COM30_Tue, 3 Apr 2001 17:42:18 -0400299_us-ascii Listers, I have a Date arithmetic SQL question. I know how to do the following in Oracle, but I don't seem to be able to get the results I need using DB2. I would like to be able to find the difference between dates whereas the results are presented in years and a fraction of a year. [...] 7006 48 17_out of the office16_Deborah L. Brown23_Deborah.Brown@PHILA.GOV30_Tue, 3 Apr 2001 17:30:05 -0400245_us-ascii ----- Forwarded by Deborah Brown/MOIS/Phila on 04/03/01 05:33 PM -----



Deborah Brown 04/03/01 05:26 PM



To: DB2-List@ryci.com cc: Subject: out of the office

I will be out of the office from 4/5 - 4/24 7055 63 20_Re: Trigger question15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM30_Wed, 4 Apr 2001 07:56:59 +1000778_- Walter, so you mean something like this should work?

CREATE TRIGGER B05013SM NO CASCADE BEFORE UPDATE OF PROD_BILG_ID_NAME ON T05013 REFERENCING OLD AS OLDROW NEW AS NEWROW FOR EACH ROW MODE DB2SQL WHEN (SUBSTR(NEWROW.PROD_BILG_ID_NAME,1,1)='#' ) BEGIN ATOMIC CASE WHEN NEWROW.PREV_PBI_NAME = OLDROW.PROD_BILG_ID_NAME SET NEWROW.PREV_PBI_NAME = 'value1' ELSE SET NEWROW.PREV_PBI_NAME = 'value2' END ; SET NEWROW.PROD_BILG_ID_NAME='value3' END ! ---------+---------+---------+---------+---------+---------+---------+------ ---+ DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "END". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ; DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 0 0 0 -1 1084 0 SQL [...] 7119 105 17_Re: DB2 Estimator16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Tue, 3 Apr 2001 23:31:07 +0100372_us-ascii





Carlton,

please please stop sending your e-mails as HTML text since my server does not support this :-( please set the option to "Send as text".

I am posting this on the list because it's not only Carlton that has this unfortunate (for me) option, and every time I have to go through a right dooh-da to read the thread. [...] 7225 74 17_Re: SQL Date Math13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Tue, 3 Apr 2001 17:45:06 -0500578_us-ascii Chris,

I came up with this on UDB non-OS/390 but have not used any functions which are not on V6 OS/390 DB2.

SELECT DECIMAL(INTEGER(YEAR(DERIVED_DATE)) + DECIMAL(DAYOFYEAR(DATE(RTRIM(CHAR(YEAR(CURRENT DATE))) CONCAT '-' CONCAT RTRIM(CHAR(MONTH(DERIVED_DATE))) CONCAT '-' CONCAT CHAR(DAY(DERIVED_DATE)))),9,3) / 365,7,2) FROM (SELECT CURRENT DATE - DATE('1965-04-26') AS DERIVED_DATE FROM SYSIBM.SYSDUMMY1) AS A

The answer I get is 35.85. Thinking about it......27th of November is more likely to be 85% of a year and not 94% as you suggested. [...] 7300 85 78_-440 when invoking DB2 stored procedure thru Powerbuilder and DB2 Connect v7.122_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM30_Tue, 3 Apr 2001 16:06:01 -0700471_iso-8859-1 Hello , We are having problems(-440) invoking a DB2 stored procedure(V6.1) thru DB2 Connect V7.1 (Thru Enterprise edition) , front end is Powerbuilder v6.5. The Stored procedure is unqualified and the current SQL id is set to the schema name of the definition of the SP. It works only if the Database profile(Power Builder) parameter is checked to allow qualified proceduere names and the procedure is invoked with qualification. HELP! ODBC trace follows [...] 7386 86 17_Re: SQL Date Math0_19_Tim.Lowe@STPAUL.COM30_Tue, 3 Apr 2001 18:12:37 -0500579_us-ascii Chris, I cannot figure why you want this, but it seems like an interesting challenge for an evening. And, while this does not look very good , it does appear to get your result: SELECT DECIMAL(DAYS(CURRENT DATE) - DAYS('1965-04-26')) / 365.25 FROM SYSIBM.SYSDUMMY1; I used a DAYS function on each of the dates since you did not want a "date duration" (years/months/days) I used a DECIMAL function and divided by 365 to get fractions of years. I suppose that using 365.25 might be "cheating", but I am not sure how accurate you need this. (I get 35.93 with this) If [...] 7473 21 17_Re: SQL Date Math0_19_Tim.Lowe@STPAUL.COM30_Tue, 3 Apr 2001 18:27:10 -0500457_us-ascii Chris, How about this: SELECT DECIMAL((DAYS(CURRENT DATE) - DAYS('1965-04-26')),8,2) / 365.25 FROM SYSIBM.SYSDUMMY1; (I get 35.9370294 with this)

How about it, is this good enough?

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. 7495 105 22_Re: DB2 v6 experiences15_Bergeson, Loren24_LBergeson@IDAHOPOWER.COM30_Tue, 3 Apr 2001 17:30:47 -0600417_iso-8859-1 When we upgraded from V5 to V6, we saw some batch programs take a nosedive in performance. It turns out that the problem was that we were binding all of our packages with DEGREE(ANY). This worked fine for us under V5. With V6, using DEGREE(ANY) instead of DEGREE(1) often times resulted in far worse performance. Apparently V6 will try to use parallelism much more often than V5 did with DEGREE(ANY). [...] 7601 116 17_Re: SQL Date Math18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Wed, 4 Apr 2001 09:20:45 +0930433_us-ascii Howzit Chris?

How about the following:

SELECT (DAYS(CURRENT DATE) - DAYS('1965-04-26'))/365.25 FROM SYSIBM.SYSDUMMY1 ;

Obviously if you need something more accurate than 365.25 then you will need to determine the number of leap years between your dates to get a more accurate denominator.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia [...] 7718 47 82_Re: -440 when invoking DB2 stored procedure thru Powerbuilder and DB2 Connect v7.110_teldb2kals22_teldb2kals@TELSTRA.COM30_Wed, 4 Apr 2001 09:59:57 +1000445_us-ascii Hi Ramesh,

V6 expects the schema name in the path parameter. Setting the current SQLID wdn't pick up the correct proc. (The default path is "SYSIBM, SYSFUN, SYSPROC"). So if u need DB2 to pick up the correct unqualified proc, set the CURRENT FUNCTION PATH parameter in ur connect string or in the client database configuration as "schemaname, SYSIBM, SYSFUN, SYSPROC". This will then enable DB2 to pick up the correct proc. [...] 7766 71 17_Re: SQL Date Math13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Tue, 3 Apr 2001 19:11:03 -0500418_us-ascii Chris,

I'm intrigued.....You say:

"SELECT CURRENT DATE - DATE('1965-04-26') FROM SYSIBM.SYSDUMMY1 ;

gives me answer of 351127 (YYMMDD)"

But this gives me 351107 on UDB.....Was that a typo, or does OS/390 give you 351127?

Other than that, Tim's reply does give me 35.93 as he suggested.

P.S. I'm not near a mainframe to test currently........

Thanks Terry [...] 7838 27 21_Re: OS/390 DB2 Access9_Eric Kwai18_Eric_Kwai@DTCC.COM30_Tue, 3 Apr 2001 19:11:20 -0500667_- Charles: Shadow Direct from Neon Systems is another alternative. -Eric

On Mon, 2 Apr 2001 11:40:57 -0700, Charles Jambrosic wrote:

>I am attempting to find alternatives for accessing DB2 on OS/390 from >a windows workstation. We currently use a DB2 Connect, TCP/IP and >DDF scenario. What other access methods are there? There is no >web access so we don't require Net.Data. > >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. [...] 7866 105 17_Re: SQL Date Math10_teldb2kals22_teldb2kals@TELSTRA.COM30_Wed, 4 Apr 2001 10:38:26 +1000362_us-ascii Terry,

351107 (rather, 351108, given the time difference) is what I get on OS/390. I think it might be a typo on Chris's part, or he might have altered his date.

Cheers, Kals



----- Original Message ----- From: Terry Purcell Date: Wednesday, April 4, 2001 10:11 am Subject: Re: SQL Date Math [...] 7972 92 17_Re: SQL Date Math23_Puddy, Andrew (CALBRIS)36_Andrew.PUDDY@COMALCO.RIOTINTO.COM.AU30_Wed, 4 Apr 2001 01:00:44 -0000460_iso-8859-1 try using SELECT CURRENT DATE - DATE('1965-04-26') ,current date FROM SYSIBM.SYSDUMMY1 so that we can see what the current date is. remember the answer changes as the date changes.

Andrew Puddy





-----Original Message----- From: Terry Purcell [mailto:Terry_Purcell@YLASSOC.COM] Sent: Wednesday, 4 April 2001 10:11 To: DB2-L@RYCI.COM Subject: Re: SQL Date Math



Chris,

I'm intrigued.....You say: [...] 8065 93 28_Re: Bind Commit / Deallocate12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Wed, 4 Apr 2001 11:43:27 +1000332_- Marcus, Dealloc benefits those transactions that will make use of thread re-use. So if you can cycle out earlier, those SK*** elements out of the EDM pool that aren't going to frequently reused, you may get some relief.

Personally I'm of the opinion that DEALLOCATE is a tuning option to be applied where appropriate. [...] 8159 101 17_Re: SQL Date Math13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM30_Tue, 3 Apr 2001 23:40:51 -0500593_us-ascii OS/390 DB2 V5 returned 351107





Terry Purcell cc: Sent by: DB2 Data Subject: Re: SQL Date Math Base Discussion List



04/03/01 07:11 PM Please respond to DB2 Data Base Discussion List











Chris,

I'm intrigued.....You say:

"SELECT CURRENT DATE - DATE('1965-04-26') FROM SYSIBM.SYSDUMMY1 ;

gives me answer of 351127 (YYMMDD)"

But this gives me 351107 on UDB.....Was that a typo, or does OS/390 give you 351127? [...] 8261 133 17_Re: SQL Date Math13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 4 Apr 2001 00:06:50 -0500315_iso-8859-1 Andrew,

Yes the date will change based on where you are in the world, but the interesting part of this problem is that the date changes by twenty days based on whether you use the SQL I proposed or that provided by Tim Lowe (we had a good but brief discussion of this off line - Thanks Tim). [...] 8395 85 36_Re: Row materialization at OPEN time13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 4 Apr 2001 00:18:05 -0500601_iso-8859-1 Kazi,

You haven't provided the explain output, but I will assume that the access path includes list prefetch since.....for RID list processing without a data sort, then RID processing occurs at first fetch (rather than at cursor open for data sort).

Whether you perform this at open or first fetch is really irrelevant since it is still required to be performed before the first row is retrieved. But to force this at open would require an ORDER BY, DISTINCT or GROUP BY etc to force a sort, thus causing more overhead than the RID list processing experienced currently. [...] 8481 185 14_rebind problem3_joe20_zoujie@MAIL.DCAC.COM30_Wed, 4 Apr 2001 13:49:47 +0800385_gb2312 DEAR LIST: I got a problem and I did not know why it occurs. When we rebind some packages(about 40s),I use IKJEFT01 to run DSN command: rebind package(ZPRFO.PF7C0TPG) IT runs for a long time until I got a system code x'0c4' and reason code '10'. We use DB2 for OS/390 V5R1 and the OS/390 is V1R3. The following is the JCL I used. Can anybody enlight me? Thanks in advance. [...] 8667 85 20_Re: Trigger question13_Robert Wright17_rwright@LIC.CO.NZ30_Wed, 4 Apr 2001 00:50:38 -0500650_- Have you tried using a CASE statement, as opposed to a CASE expression. This would lok like:

case when ... else ... end CASE;

ie, put the END CASE in and see how it goes.

Rob

On Wed, 4 Apr 2001 07:56:59 +1000, Mallett, Steven wrote:

>Walter, so you mean something like this should work? > > CREATE TRIGGER B05013SM > NO CASCADE BEFORE > UPDATE OF PROD_BILG_ID_NAME ON T05013 > REFERENCING OLD AS OLDROW > NEW AS NEWROW > FOR EACH ROW MODE DB2SQL > WHEN (SUBSTR(NEWROW.PROD_BILG_ID_NAME,1,1)='#' ) > BEGIN ATOMIC > CASE > WHEN NEWROW.PREV_PBI_NAME = OLDROW.PROD_BILG_ID_NAME [...] 8753 15 28_Re: Bind Commit / Deallocate15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 4 Apr 2001 02:00:15 -0500491_- Cathy

You're right, we've got some awful big QMF save data DBD's. I think, we must drop a lot of QMF save data tables, REORG and MODIFY to get rid of these big DBD's. May be we can reduce our EDM pool as well and maintain a 99,9% hit ratio.

================================================ 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. 8769 14 28_Re: Bind Commit / Deallocate15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 4 Apr 2001 02:04:45 -0500359_- To come back to Marcus first input. Are there any problems with the EDM pool? I think that the EDM pool is always 100% full like the bufferpools and if DB2 needs space it will steal a page from the LRU page. So the can reduce the size of the EDM pool until you will fall under a certain hit ratio. I think the recommendation is 99,9% for DBDs and PTs. [...] 8784 65 28_Re: Bind Commit / Deallocate14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Wed, 4 Apr 2001 09:21:39 +0100470_iso-8859-1 No, your EDM is not always full. When it's full, you get the following message:

DSNT500I -DB2J DSNGEPLC RESOURCE UNAVAILABLE REASON 00C90089 TYPE 00000600 NAME

which we have, quite frequently, recently.

Marcus Davage

"My opinions, yadda yadda yadda"

-----Original Message----- From: Walter Janissen [mailto:walter.janissen@VICTORIA.DE] Sent: 04 April 2001 08:05 To: DB2-L@RYCI.COM Subject: Re: Bind Commit / Deallocate [...] 8850 142 17_Re: SQL Date Math11_David Hayes28_Dave.Hayes@MORGANSTANLEY.COM30_Wed, 4 Apr 2001 09:46:20 +0100510_us-ascii Terry

I think the discrepancy here is that 351107 is not the 7th of November. The answer is actually 35 years, 11 months and 7 days. As a very rough calculation, assume 30 days per month. (30 * 11) + 7 = 337. Then 337/365 = 0.92, so it's much nearer the result given by using the DAYS calculation. I'm sure if accurate figures were used (i.e. the actual number of days in each of the months spanned), the figures would be the same. However, I'd need another coffee before attempting this! [...] 8993 13 28_Re: Bind Commit / Deallocate15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 4 Apr 2001 04:09:40 -0500289_- But then, I think, all pages are currently in use. Otherwise I can't explain Cathy's and my figures. At our site, because of the big DBDs, we've got a high percentage of the EDM pool for DBDs. Cathy has got a much higher percentage for packages. Therefore I deduced my presumption. [...] 9007 73 36_Re: Row materialization at OPEN time0_27_jim.leask@RS-COMPONENTS.COM30_Wed, 4 Apr 2001 11:03:47 +0100582_iso-8859-1 You could add an order by (where one of the columns does not appear in an index) this would materialise the result set. Not sure it will rectify your problem though.

Jim.

-----Original Message----- From: Kazi Hossain [mailto:kazi.x.hossain@MAIL.SPRINT.COM] Sent: 03 April 2001 21:59 To: DB2-L@RYCI.COM Subject: Row materialization at OPEN time



One of our production batch job is getting about an average 4 rows per OPEN CURSOR. Rows are being materialized at FETCH time, without any Bufferpool hit. Access is 'read only'. Two questions: [...] 9081 45 17_Re: SQL Date Math20_Dr. Matthias Wehrens29_matthias.wehrens@AM-GRUPPE.DE30_Wed, 4 Apr 2001 04:52:19 -0500388_- Dave has found the trap. You must not interpret 351107 as Nov 7th or the 311th day in year. Example: SELECT date('2001-04-03') - DATE('1965-04-26') as YYYYMMDD, date('2001-04-03') - 35 years - 311 days as wrong, date('2001-04-03') - 35 years - 11 months - 7 days as ok FROM dummy

gives

YYYYMMDD WRONG OK ---------- ---------- ---------- 351107, 1965-05-27 1965-04-26 [...] 9127 134 36_Re: V5 to V6 - Host variable problem14_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US30_Wed, 4 Apr 2001 06:27:42 -0400371_US-ASCII ok ... but what's IN predicate's treatment of a host structure (the question) got to do with an INTO treatment of a host structure which has always worked fine ... I'm more stupid this morning than usual ...

>>> Tony.Provenzola@NIKE.COM 04/03/01 01:18PM >>> Now that's a funny thing. The v6 manual has the same illustration as Phil mentioned for v7: [...] 9262 27 14_Duplicate Rows13_Michael Fleck14_m.fleck@LVR.DE30_Wed, 4 Apr 2001 13:55:00 +0100406_ISO-8859-1 Hi list members,

I want to create an unique index on a column but the create gets a -811 because of duplicate values in the table. Is it possible to find out which values are duplicate in this table via a select statement ?

Thanks in advance Michael Fleck

Datenbank- und Ressourcendienste Landschaftsverband Rheinland Infokom Ottoplatz 2, 50679 Köln Tel: 0221/809/2826 [...] 9290 42 90_Parallel jobs running longer then same jobs run one at a time DB2 V6 for OS/390 - SOLUTION15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Wed, 4 Apr 2001 07:10:35 -0500513_iso-8859-1 Here it is, we think. I made a suggestion to the DBA and I don't know if he implemented it yet. The performance group here found that all the jobs were enqueueing (sp?) on the NPI. In addition, they were "thrashing the cache". One job would come in and load the data it needed in the cache controller. Then the next job would come in (after waiting for the first one to complete) and flush out job # 1's data from cache and load it's own in and so on and so forth. We suggested to the DBA that he [...] 9333 50 18_Re: Duplicate Rows11_David Hayes28_Dave.Hayes@MORGANSTANLEY.COM30_Wed, 4 Apr 2001 13:12:30 +0100599_iso-8859-1 Michael

Try:

SELECT COL1, COUNT(*) - where COL1 is the index column you need FROM TABLE1 GROUP BY COL1 HAVING COUNT(*) > 1

HTH

Dave Hayes



Michael Fleck wrote:

> Hi list members, > > I want to create an unique index on a column but the create gets a -811 because of duplicate values in the table. > Is it possible to find out which values are duplicate in this table via a select statement ? > > Thanks in advance > Michael Fleck > > Datenbank- und Ressourcendienste > Landschaftsverband Rheinland > Infokom > Ottoplatz 2, 50679 Köln [...] 9384 51 18_Re: Duplicate Rows15_Lankester, Andy22_Andy_Lankester@BMC.COM30_Wed, 4 Apr 2001 07:15:01 -0500546_iso-8859-1 SELECT keycol1, keycol2 etc, count(*) FROM table GROUP BY keycol1, keycol2 etc HAVING COUNT(*) > 1

Andy Lankester





-----Original Message----- From: Michael Fleck [mailto:m.fleck@LVR.DE] Sent: 04 April 2001 13:55 To: DB2-L@RYCI.COM Subject: Duplicate Rows



Hi list members,

I want to create an unique index on a column but the create gets a -811 because of duplicate values in the table. Is it possible to find out which values are duplicate in this table via a select statement ? [...] 9436 47 18_Re: Duplicate Rows13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Wed, 4 Apr 2001 07:24:00 -0500510_iso-8859-1 select col1, col2 ..., count(*) from table group by col1, col2 ... having count(*) > 1

-----Original Message----- From: Michael Fleck [mailto:m.fleck@LVR.DE] Sent: Wednesday, April 04, 2001 7:55 AM To: DB2-L@RYCI.COM Subject: Duplicate Rows



Hi list members,

I want to create an unique index on a column but the create gets a -811 because of duplicate values in the table. Is it possible to find out which values are duplicate in this table via a select statement ? [...] 9484 64 18_Re: Duplicate Rows0_22_blyons2@CSCPLOENZKE.DE30_Wed, 4 Apr 2001 14:31:58 +0200509_iso-8859-1 Michael,

yes it is. it should be something like: SELECT index-keys, count(*) FROM tablename GROUP BY index-keys HAVING count(*) > 1

Hope this helps, Brian







Michael Fleck @RYCI.COM> on 04.04.2001 13:55:00

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Duplicate Rows



Hi list members, [...] 9549 67 18_Re: Duplicate Rows19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Wed, 4 Apr 2001 07:25:19 -0500392_iso-8859-1 Hi Michael, Probably possible via a SELECT statement but I'm thinking you can reach your ultimate goal faster by doing an unload, followed by a DELETE of all rows, create the unique index on the column, then reload with a SYSDISC DD. Then edit the SYSDISC file and load RESUME(YES) the remaining rows from it. I assume you have resolved how to eliminate the appropriate dups. [...] 9617 21 18_Re: Duplicate Rows9_Colin Fay13_cfay2@CSC.COM30_Wed, 4 Apr 2001 08:26:16 -0400432_us-ascii If

Unique index contains columns dup_col1, dup_col2

then:

select dup_col1, dup_col2 , count(*) from owner.tbname group by dup_col1, dup_col2 having count(*) > 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. 9639 61 18_Re: Duplicate Rows0_19_mike.holmans@BT.COM30_Wed, 4 Apr 2001 13:29:41 +0100409_iso-8859-1 If you are trying to creaqte a unique index on (col1, col2, col3) then the following ought to work:

SELECT col1, col2, col3, COUNT(*) FROM table GROUP BY col1, col2, col3 HAVING COUNT(*) > 1





Mike Holmans BT ISE Database Services mike.holmans@bt.com

This post represents the views of the author and does not necessarily accurately represent the views of BT [...] 9701 53 18_Re: Duplicate Rows16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 4 Apr 2001 18:20:53 +0530583_iso-8859-1 Use this query if it works for you.

SELECT COLNAME,COUNT(*) from TABLE GROUP BY COLNAME HAVING COUNT(*) > 1

Regards Sanjeev

> -----Original Message----- > From: Michael Fleck [SMTP:m.fleck@LVR.DE] > Sent: Wednesday, April 04, 2001 6:25 PM > To: DB2-L@RYCI.COM > Subject: Duplicate Rows > > Hi list members, > > I want to create an unique index on a column but the create gets a -811 > because of duplicate values in the table. > Is it possible to find out which values are duplicate in this table via a > select statement ? > > Thanks in advance [...] 9755 39 29_Distinct on multiple columns?15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 4 Apr 2001 14:47:06 +0200395_iso-8859-1 Hi list members,

In my shop there was a question regarding a distinct on two or more columns to know the number of values in these columns. Is there a way to do a select to find out the number of possible values?

Thanks in advance,

Jaap Slot Rabobank ICT ZL G226 PO Box 17100 3500 HG Utrecht The Netherlands Tel: 0031 (0)30 215 2220 Fax: 0031 (0)30 215 3003 [...] 9795 79 33_Re: Distinct on multiple columns?12_Troy Coleman19_Colematr@MEIJER.COM30_Wed, 4 Apr 2001 08:58:13 -0400305_US-ASCII Hi Jaap, I did not test this SQL however I've done stuff like this in the past. I'm not sure if you want the total number of unique values or how many rows have a given unique value. So I will give you the sql for both.

To get the count of rows for each given unique combination use: [...] 9875 61 33_Re: Distinct on multiple columns?11_David Nance16_DWNance@FHSC.COM30_Wed, 4 Apr 2001 09:05:34 -0400482_US-ASCII Probably not the best way, but you could use:

SELECT COUNT(*) FROM ( SELECT DISTINCT COL_1, COL_2 FROM TABLE1) AS A



Dave Nance First Health Services, Corp. (804)527-6841

>>> J.P.Slot@RF.RABOBANK.NL 04/04/01 08:47AM >>> Hi list members,

In my shop there was a question regarding a distinct on two or more columns to know the number of values in these columns. Is there a way to do a select to find out the number of possible values? [...] 9937 55 18_Re: rebind problem14_Hickman, Mindy29_Mindy.Hickman@DOA.STATE.WI.US30_Wed, 4 Apr 2001 07:58:45 -0500387_gb2312 We've been recommending here to use IKJEFT1A instead of IKJEFT01 since we were told that IKJEFT01 may not process the reason code correctly. Try it again using IKJEF1A and see if you get an error message you can use.

-----Original Message----- From: joe [mailto:zoujie@MAIL.DCAC.COM] Sent: Wednesday, April 04, 2001 12:50 AM To: DB2-L@RYCI.COM Subject: rebind problem [...] 9993 20 19_A puzzling question14_subrata mondal25_subratamondal@HOTMAIL.COM30_Wed, 4 Apr 2001 13:39:22 -0000 10014 39 4_-92213_Shauna Hadden24_shauna.hadden@BCBSKS.COM30_Wed, 4 Apr 2001 08:19:59 -0500334_- We had an interesting scenario happen here a few weeks ago, and I curious if anyone else had come across this and if so, how they coded around it. You can't check for -922 because you want it to error off it is a Plan Authorization Error...unless you are willing to interrogate the DSNTIAR error text for reason code 00F30058. [...] 10054 18 24_SPB setup/usage question18_Sandra Lakenburger34_sandra.lakenburger@MAIL.SPRINT.COM30_Wed, 4 Apr 2001 08:29:32 -0500559_- I've got a question concerning setting up and using SPB. We're trying to get it working. Right now we're trying to get the IVP job, DSNTEJ65, to run and are getting a -552 on the CREATE PROCEDURE statement in step 3. When we do a DISPLAY THREAD command while it is active, we are able to see that the id calling the stored procedure builder program, DSNTPSMP, is an id that has Install SYSADM authority. It does not have CREATEIN authority granted directly to it. Any experiences on getting this to work would be appreciated. Thanks Sandra Lakenburger [...] 10073 75 8_Re: -92215_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 4 Apr 2001 15:57:06 +0200672_iso-8859-1 Well it seems to me.. works as designed...

As Linda said: "Imagine the Creator as a stand-up comedian - and at once the world becomes explicable." - H.L. Mencken

Kind regards,

Jaap Slot

-----Oorspronkelijk bericht----- Van: Shauna Hadden [mailto:shauna.hadden@BCBSKS.COM] Verzonden: woensdag 4 april 2001 15:20 Aan: DB2-L@RYCI.COM Onderwerp: -922



We had an interesting scenario happen here a few weeks ago, and I curious if anyone else had come across this and if so, how they coded around it. You can't check for -922 because you want it to error off it is a Plan Authorization Error...unless you are willing to [...] 10149 61 33_Re: Distinct on multiple columns?12_Bob Lawrence21_rlawrence@BOSCOVS.COM30_Wed, 4 Apr 2001 10:00:33 -0400590_us-ascii here is another one select count(distinct col1||col2||coln) from tbl1 if any of the columns are not char then convert to char using appropriate functions

HTH

Bob Lawrence DBA Boscov's Dept Stores Inc



On Wednesday, April 04, 2001 8:47 AM, Slot, JP (Jaap) [SMTP:J.P.Slot@RF.RABOBANK.NL] wrote: > Hi list members, > > In my shop there was a question regarding a distinct on two or more columns > to know the number of values in these columns. Is there a way to do a select > to find out the number of possible values? > > Thanks in advance, > > Jaap [...] 10211 70 17_Re: SQL Date Math13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 4 Apr 2001 08:56:00 -0500471_iso-8859-1 Damn, I had a few people going that there was a date duration conspiracy.......including myself!!!

Thanks Matthias, David, Tim, Kals etc for setting me straight......Unfortunately I could not see the forest for the trees.

Terry

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Dr. Matthias Wehrens Sent: Wednesday, April 04, 2001 4:52 AM To: DB2-L@RYCI.COM Subject: Re: SQL Date Math [...] 10282 102 33_Re: Distinct on multiple columns?15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 4 Apr 2001 16:03:00 +0200431_iso-8859-1 Troy, David,

Thanks for your help. The last SQL to count the total number of unique values was what we were looking for.

Kind regards,

Jaap Slot.

-----Oorspronkelijk bericht----- Van: David Nance [mailto:DWNance@FHSC.COM] Verzonden: woensdag 4 april 2001 15:06 Aan: DB2-L@RYCI.COM Onderwerp: Re: Distinct on multiple columns?



Probably not the best way, but you could use: [...] 10385 78 18_Re: Duplicate Rows15_Jackson Reavill18_damcon2@US.IBM.COM30_Wed, 4 Apr 2001 08:29:40 -0400528_iso-8859-1 Hi Michael,

Yes, try...

select index cols, count(*) from table group by index cols having count(*) > 1;

This will give you the index values causing the dups.

Cheers, Jay



Jay Reavill damcon2@us.ibm.com IBM Global Services Tampa, Florida Tel: (813) 801-7303, Tie Line 8-427-7303 ----------------------------------------------------- Happiness is not around the corner. Happiness is the corner. - Bavarian Motor Works ----------------------------------------------------- [...] 10464 17 8_Re: -92213_Shauna Hadden24_shauna.hadden@BCBSKS.COM30_Wed, 4 Apr 2001 09:21:10 -0500330_- Jaap,

If you will reread the posting, you will notice that I didn't say it was right and I didn't say it was wrong. I just said it was.

What we are wanting to know is what the "best" way is to code for it. It is causing the transaction to abend and once appeared to have caused the messaging region to abend. [...] 10482 160 17_Re: SQL Date Math15_Blaney, Bill L.21_Bill.L.Blaney@SSA.GOV30_Wed, 4 Apr 2001 10:48:00 -0400601_iso-8859-1 Terry,

You've forgotten that the difference between two dates isn't a date. 351107 is not thirty-five years plus November seventh: it's 35 years, eleven months, and seven days. This means that the fractional year must be computed as 335 (eleven months) plus 7 or 342, which yields .937 (rounded) for a value of 35.94 years. The value I get for 13126/365.25 also rounds to .937, with a slight difference in the unrounded values which can be attributed to the error introduced by using 365.25 for a year instead of doing the modulo arithmetic. This seems to render a pretty close [...] 10643 31 28_DB2 UDB AIX Naming Standards8_Gary Rue25_Gary.Rue@MAIL.STATE.KY.US30_Wed, 4 Apr 2001 11:09:53 -0400343_iso-8859-1 DB2 UDB (V7) on AIX--any words of wisdom, suggestions, etc. on the naming conventions for the group and ID names associated with the instance, admin server and fenced UDF components? How should these names apply across the many environments of development, testing and production? Can they be same or should they be different? [...] 10675 33 42_Database pricing: Are you paying too much?10_Dan Verton28_Dan_Verton@COMPUTERWORLD.COM30_Wed, 4 Apr 2001 10:58:42 -0400492_us-ascii Dear List Members:

I'm new to the DB2 list and wanted to touch base with as many of you as possible for a new survey I am conducting on user satisfaction with database software pricing. I'd like to hear from any of you who have recently switched database vendors (or are planning to) because the pricing or licensing structure was too steep. I am writing a major cover story on this issue, as many users in the database community have expressed concern to me about this. [...] 10709 69 8_Re: -92215_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 4 Apr 2001 17:24:42 +0200577_iso-8859-1 Shauna,

I'm sorry for my poor response. Maybe it's possible to check for the SQLSTATE to get around it. What I understand is that you call this program trough another one. You might want to do a reset for this USERID once you get this certain SQL state. I am not familiar with 'Top Secret' but in RACF it's possible to resume a USERID. However this still leaves your problem with the different users sabotaging eachother. This is application functionality I wouldn't pursue. As I said earlier it works as designed and the design is as poor as my answer, [...] 10779 156 36_Re: V5 to V6 - Host variable problem16_Provenzola, Tony24_Tony.Provenzola@NIKE.COM30_Wed, 4 Apr 2001 08:27:32 -0700455_iso-8859-1 Q: What's IN predicate's treatment of a host structure (the question) got to do with an INTO treatment of a host structure?

A: The v4, v5, and v6 "SQL Reference Manuals", in the section, "Host Structures in PL/I, C, and COBOL" say "A host structure can be referred to in any context where a list of host variables can be referenced." The INTO is just the example from that section of the manual, but the text says "in any context". [...] 10936 11 34_Is there a ARCHIVE for the DB2-L ?12_Clark, Kevin15_KClark@FDIC.GOV30_Wed, 4 Apr 2001 11:32:15 -0400244_-

================================================ 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. 10948 192 23_Re: A puzzling question10_Mike Wiles14_mwiles@UCG.COM30_Wed, 4 Apr 2001 11:42:28 -0400669_iso-8859-1 Hi Subrata (and list),

You are talking about something I do every day. At the risk of oversimplifying, when you are dealing with daily pricing, it's fairly easy, just add a date column. When you start dealing with finer granularity, it starts getting very complicated, i.e This stock price was effective from 2001-04-04-12.13.33.001123 until 2001-04-04-12.19.33.007654. This would involve adding two columns (a start timestamp and an end timestamp.) This causes your dataset to get huge, and your SQL to get ugly and slow (select * from t1 where '2001-04-04-12.16.45.456434' between start_date and end_date). (I've read an article that said that [...] 11141 77 8_Re: -92217_Belfield, Alma G.21_Alma.Belfield@TWA.COM30_Wed, 4 Apr 2001 10:47:26 -0500558_iso-8859-1 I'd suggest you look at your DSN3@SGN exit - if it's calling Top/Secret to get secondary userids for the new IMS transaction then you'd get the -922. You could trace PERFM CLASS(2,3) to see the results of IDENTIFY and SIGN-ON processing, using your monitor's reports. DB2 won't be checking Top/Secret for every SQL executed, just the start of 'new' threads. DB2 V5.1 Admin Guide Chapter 3-4 'Controlling Access to a DB2 Subsystem' describes when security checks are done etc. For IMS transactions you'd look at section 'Processing Sign-Ons'. [...] 11219 68 12_Index Access0_14_mtdage@ATT.NET30_Wed, 4 Apr 2001 15:50:12 +0000704_- Here's our scenario.

We have an SQL below:

SELECT T1.PRTN_ID,T1.CLM_CK_DT,T1.CLM_ID,T1.CLM_SFX, T1.CLM_LOB, T1.CLM_SFX,T1.CLM_LOB, T1.C1_ACCT_ID,T1.C2_ACCT_ID, T1.C2_ACCT_ID,T1.ITS_ACS_FEE,T1.ITS_CLM_TYP FROM "AH00"."VW00720" T1 WHERE (T1.CLM_CK_DT = '2000-06-30') AND (T1.PRTN_ID = '60') AND (T1.ITS_CLM_TYP IN('M','K')) FOR FETCH ONLY



This is actually run as DYNAMIC SQL, we had to make it like static so we can run EXPLAIN on it. Maybe it is coincidental or not but this started happening after DB2 maintenance was applied. In addition they have added more partitions and one month's worth of data. And because it is using IX00500G index it is running a long time. [...] 11288 55 38_AW: Is there a ARCHIVE for the DB2-L ?12_Peter, Georg15_G.Peter@DZBW.DE30_Wed, 4 Apr 2001 17:56:26 +0200991_iso-8859-1 Yes. Visit the archives at http://jupiter.ryci.com/archives/db2-l.html

Mails ausschalten z.B. wg. Urlaub: SET DB2-L NOMAIL and send it to: LISTSERV@RYCI.COM When you come back use: SET DB2-L MAIL

Documents (Word docs, REXX examples, etc.) have been stored/archived at http://jupiter.ryci.com/archives/db2-l-documents.html

With kind regards - mit freundlichen Grüssen, Georg H. Peter DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- Life is 10% what happens to you and 90% how you react to it. [...] 11344 28 14_User Data Type11_Anil Mathur26_anil.mathur@PRUDENTIAL.COM30_Wed, 4 Apr 2001 11:58:34 -0400315_us-ascii Dear List Members:

We are on OS/390 v 6.1

I am creating a table and one of the primary key columns is going to hold the year of retirement. We could define the column as Dec(4,0). I am wondering if I can define a user data type for the year and then use that as the column in the table. [...] 11373 15 36_Re: Integer Function in DB2 V5/OS39012_Roger Miller19_millerrl@US.IBM.COM30_Wed, 4 Apr 2001 10:32:58 -0500303_- Please check that SQL Reference Version 5 again for the INTEGER function. Only DECIMAL works on strings in V5. I suspect that others are remembering Version 6, where INTEGER works on strings. If you use the Version 6 book, note the change bars (|) at the left, which show that these are changes. [...] 11389 14 57_Re: Trigger question and UDF question, URGENT PLEASE HELP12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 4 Apr 2001 10:51:23 -0500470_- I'm sorry. It appears that we gave you an answer that works on DB2 for OS/390 V6 and up. Triggers on our Unix and Windows can't call stored procedures yet, but we are working on it.

Roger Miller, DB2 for OS/390 and 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. 11404 58 20_OS/390 R10 Migration12_Hylton Tom P20_Tom.P.Hylton@IRS.GOV30_Wed, 4 Apr 2001 12:11:13 -0400294_iso-8859-1 We are in the planning stage of migrating OS390 from R8 to R10 (DB2 V6). From veterans of this move, are there any particular areas we should be keeping an eye out for? I haven't seen any discussion on this subject, so either the migration is invisible or few have done it yet? [...] 11463 57 23_DB2 V5 Ignoring index ?13_Chadwick, Cam27_Cam.Chadwick@LONDONLIFE.COM30_Wed, 4 Apr 2001 12:09:36 -0400271_- I have a 5 million row table consisting of 11 columns, 10 of which comprise the unique index. The access to this table is fairly slow due to an excessive number of GETPAGEs required to find the single qualifying row (this is part of a join to a very small table). [...] 11521 22 41_Re: When should a ONLINE SQL run in batch12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 4 Apr 2001 11:00:53 -0500493_- Part of the rubber band is the standard for a commit frequency. To avoid problems with running online utilities and to reduce timeouts and deadlocks, I like to see commits more than once a minute. Managing the commit frequency is crucial for availability, locking and performance. It also suggests a maximum processing time for online transactions. The real question is, "How long will the users wait?" In e-business, many of the surveys are suggesting the time is closer to 8 seconds. [...] 11544 127 27_Re: DB2 V5 Ignoring index ?10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Wed, 4 Apr 2001 11:34:21 -0500535_us-ascii Cam, Is your new index clustering? If your data is clustered and the catalog is updated, DB2 weight the use of the clustering index pretty highly & will in most cases choose the use of a clustering index over other any indexes in most cases. It sounds like your first index is pretty useless at this point...since you now have an index with all eleven columns, Also - the optimizer only uses the index columns to the FIRST < or > so with your SQL below: WHERE A.COL1 = :HV1 AND A.COL2 = :HV2 AND A.COL3 = :HV3 AND A.COL4 [...] 11672 14 11_DB2 Columns17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Wed, 4 Apr 2001 09:41:32 -0700402_us-ascii Would there be a problem if you have to recover or reload a table from a Reorg unload or Image copy that was done prior to adding columns to the table.

================================================ 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. 11687 76 8_Re: -9220_22_Rohn.Solecki@MTS.MB.CA30_Wed, 4 Apr 2001 11:44:03 -0500439_us-ascii Here is what we found (we were able to recreate this). Keep in mind that multiple people share this userid (don't ask me about this..it was not my idea).

Sounds like you are trying to find a technical workaround for something that is working as designed (verifying userid access rights) to fix a what you already know is a bad (or at least less than optimal) design decision, allowing shared userid. [...] 11764 31 36_DB2 Personal Edition and DB2 Connect0_22_BILL_GALLAGHER@PHL.COM30_Wed, 4 Apr 2001 12:54:02 -0400407_us-ascii Hi,

We have some developers who have a need to create local DB2 databases on their NT workstations, as well as access host DB2 tables from the same workstation.

They are trying to install DB2 Personal Edition v6.1 and DB2 Connect v6.1 on the same workstation. For some reason, I thought that there may be a problem with both of these products coexisting on the same workstation. [...] 11796 15 18_Re: rebind problem12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 4 Apr 2001 11:34:40 -0500513_- For details on the progress, there is a DSNTRACE DD statement that can also be helpful. Since this appears to abending in a DB2 module, you should probably check in with IBM service people. How current is your service level?

Roger Miller, DB2 for OS/390 and 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. 11812 10 22_Alternative to listcat13_Daryl Johnson18_djjan@TELEPORT.COM30_Wed, 4 Apr 2001 11:35:09 -0500312_- Does anyone know where I can find documentation on IGGCSI & UCBSCAN?

================================================ 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. 11823 23 40_Re: DB2 Personal Edition and DB2 Connect13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Wed, 4 Apr 2001 14:05:05 -0400326_iso-8859-1 Hi Bill,

I believe we have several folks with exactly that configuration.

I have EE, but I have helped a number of our support folks install DB2 PE & DB2 Connect PE. I wouldn't be suprised if there were problems between DB2 PE and DB2 Connect EE. But I haven't tried it and haven't heard of any. [...] 11847 37 28_Re: Bind Commit / Deallocate13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Wed, 4 Apr 2001 11:34:52 -0700456_iso-8859-1 Well, I think the word "full" has 2 meanings here. At this moment, 84% of my EDM pool is "in use", 16% is free, but 75% of the pages are "stealable", meaning that the objects in them are not currently in use, but waiting for possible re-use. In that way it operates very much like a bufferpool. In actuality, only 9% of my EDM pool was actively being used at the moment I hit enter, so I'm a long way off from an EDM "full" condition. HTH, [...] 11885 20 8_Re: -92213_Shauna Hadden24_shauna.hadden@BCBSKS.COM30_Wed, 4 Apr 2001 14:05:03 -0500365_- So what is the "politically correct" answer to that comment. Hmm.

In my opinion, sharing userids is not a good thing to do. How do you track down which person violated security, changed the data or caused the problem? I feel that it could present a security risk....especially since they have to post the password somewhere so everyone can get to it. [...] 11906 106 27_Re: DB2 V5 Ignoring index ?0_19_Tim.Lowe@STPAUL.COM30_Wed, 4 Apr 2001 14:17:27 -0500590_us-ascii Cam, On occasion, I have seen access path problems when runstats did not gather enough information. In particular, the first thing that I want to do is to make sure that runstats information was gathered on all of the key columns, and that keycard information was gathered. Since keycard gathers information on the key cardinality of "subsets" of the index columns (ie, columns 1 and 2 together , and columns 1 through 3 together, etc.), and your query definitely will need to match on only some of the columns of the index, then this information would appear to be extremely [...] 12013 22 30_Bind vs db2rbind and SQL0818's11_Mike Fatula17_mfatula@PHEAA.ORG30_Wed, 4 Apr 2001 15:44:10 -0400383_us-ascii Questions

1. What timestamps are created and where during a Precompile?

2. What timestamps are created and where during a Bind? Exp. db2 bind LPXFA.bnd blocking all degree 1 isolation cs

3. What timestamps must not match to receive an SQL0818?

4 . What timestamps are created and where during a DB2RBIND Exp. db2rbind dlgsmain -l logerr all [...] 12036 16 31_UDB for Solaris Discussion List14_Nader W Karimi23_Nader.W.Karimi@AEXP.COM30_Wed, 4 Apr 2001 13:53:45 -0600348_us-ascii Does anyone know of another discussion list more geared towards Solaris platforms? Cheers. Nader.

================================================ 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. 12053 33 35_How to skip first n matched rows ??15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Wed, 4 Apr 2001 15:04:00 -0500336_us-ascii Hi,

Is there any method in SQL to skip first n number of rows .

Ex :

select * from X_Tab where col1 = ' value1' and col2 ='value2' order by col2;

Assume this query fetches 100 matched records , and if I wanted to skip first 25 records i.e records from 26th,27th ,,,,,,,so on rows are required. [...] 12087 39 33_Current sqlid.... immediate reply17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Wed, 4 Apr 2001 14:56:41 -0500282_- Hi Folks,

We are using DB2 V6, on os390. i have a question on set Current sqlid. how do i set the current sqlid to some other user. suppose if i am a owner of the table and i want to set a current sqlid to other person to execute a query. other than select privilage. [...] 12127 27 15_net.data v2 ptf12_Isaac Yassin20_yassini@BEZEQINT.NET30_Wed, 4 Apr 2001 22:12:17 +0200498_iso-8859-1 Hi, Upon moving from os/390 2.4 to os/390 v2.8 we put many ptfs to net.data. Performance went down shrply, especially when dealing with sorting data (but not only). This happened both on 2.4 & 2.8. We took out the ptfs and performance became much better (in 2.8 it's better then on 2.4). Has any one encountered this? The list of ptfs is a long one so we're still invastigating. We involved local IBM but they didn't find anything yet. Any help / idea would be appreciated. Thanks, [...] 12155 113 18_Re: Duplicate Rows13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Wed, 4 Apr 2001 14:13:00 -0600327_iso-8859-1 I guess we can agree on a this query.

However I've been here and been defeated by a many row table with a long column that was nearly unique. It blew out the sort work area.

select cola from tab1 t1 where exists (select 1 from tab1 t2 where t1.cola = t2.cola and t1.primary_key <> t2.primary_key) [...] 12269 51 34_Re: Bind vs db2rbind and SQL0818's18_Gert van der Kooij15_geko@WANADOO.NL30_Wed, 4 Apr 2001 22:16:52 +0200458_iso-8859-1 Hi,

Timestamps are only created during precompile. It is used to 'identify' the created package and also to identify the bindfile. This timestamp has to match with the timestamp set in the program during precompile when running this program.

The chapter 'Creating and Preparing the Source Files' in the Application Development Guide shows a clear picture about how it works. (assuming you'r talking about DB2 for Unix, NT etc). [...] 12321 26 40_Re: DB2 Personal Edition and DB2 Connect11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Wed, 4 Apr 2001 16:21:14 -0400282_us-ascii If you have DB2 Connect PE and the UDB Personal Edition with the Developer's kit. You have all you need to create and access LOCAL database and to access REMOTE databases on the host. Licensing is the only thing I am not sure how IBM packages it. I have tested and used the 12348 61 8_Re: -92217_Belfield, Alma G.21_Alma.Belfield@TWA.COM30_Wed, 4 Apr 2001 15:33:35 -0500611_iso-8859-1 I guess my approach is that the user session that was already active is still valid and should continue to work. Of course NEW sessions should be rejected once the userid has been suspended. For example : 1)I sign onto TSO and am accessing DB2, everythings fine. 2)Someone else can revoke/suspend my userid by attempting to signon many times with the wrong password (from another environment e.g IMS). 3)My TSO session should still be able to continue unaffected, even accessing DB2..... and it will if my DSN3@ATH exit uses in-storage control blocks rather than a call to the Security database [...] 12410 86 37_Re: Current sqlid.... immediate reply10_Rob Wright17_rwright@LIC.CO.NZ30_Thu, 5 Apr 2001 08:38:44 +1200378_us-ascii Hi Kumar

Firstly, I am assuming you are using RACF, or equivalent security product Secondly, I am assuming that you have activated the dsn3@sgn/dsn3@ath exits to enable secondary authid checking

If so, then you can set current sqlid = any secondary authid that your userid has.

Secondary authids are the GROUPs that you are connected to. e.g. [...] 12497 42 35_Re: UDB for Solaris Discussion List22_Piontkowski, Michael L35_michael.piontkowski@ASTRAZENECA.COM30_Wed, 4 Apr 2001 16:42:42 -0400591_iso-8859-1 Check out DB2EUG at http://people.mn.mediaone.net/scottrmcleod/

It's focused on non OS/390 DB2.





Mike Piontkowski Technisource for IBM Global Services Service Delivery Center - Northeast Voice: +1 302.886.4612 Fax: +1 302.886.4612



-----Original Message----- From: Nader W Karimi [mailto:Nader.W.Karimi@AEXP.COM] Sent: Wednesday, April 04, 2001 15:54 To: DB2-L@RYCI.COM Subject: [DB2-L] UDB for Solaris Discussion List



Does anyone know of another discussion list more geared towards Solaris platforms? Cheers. Nader. [...] 12540 109 35_Re: UDB for Solaris Discussion List14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Wed, 4 Apr 2001 14:38:05 -0700494_iso-8859-1 Nader, There is a DB2 UDB (Windows/Unix/OS2) list. It's command address is: db2eug-request@lists.best.com For more info, see: http://people.mn.mediaone.net/scottrmcleod Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own.

-----Original Message----- From: Nader W Karimi [mailto:Nader.W.Karimi@AEXP.COM] Sent: Wednesday, April 04, 2001 2:54 PM To: DB2-L@RYCI.COM Subject: [DB2-L] UDB for Solaris Discussion List [...] 12650 40 24_Re: OS/390 R10 Migration13_Zessin, Peter25_Peter.Zessin@ATCOITEK.COM30_Wed, 4 Apr 2001 15:48:15 -0600444_- Hi Tom,

We had JCL (created in the 1990s) for taking DSNDB01/06/BMC/other BMC Imagecopies with multiple refer-backs. That JCL did not work after moving to OS/390 2.10. Our MVS systems programmer had to put on UW77742 to correct the problem.

Regards, Peter Zessin

-----Original Message----- From: Hylton Tom P [SMTP:Tom.P.Hylton@IRS.GOV] Sent: 04 April, 2001 10:11 AM To: DB2-L@RYCI.COM Subject: OS/390 R10 Migration [...] 12691 93 8_Re: -9220_22_Rohn.Solecki@MTS.MB.CA30_Wed, 4 Apr 2001 16:54:21 -0500569_us-ascii I keep putting my 'politically incorrect' feet in my mouth ...

In this case specifically, hmmm . . . A. You could expand on the 'who dun it' point. Is this ID is being used for dial in access, maybe from laptops, or from geographically scattered corporate offices? If the ID is being used for dial in modem or web access, then being able to track down which userid (and presumably user) did specific access could be very important. What happens if the laptop is lost or stolen. If the user has a poor memory, maybe they violated security standards [...] 12785 223 18_Re: EDM pool stats16_Alison Pelletier26_alison_pelletier@YAHOO.COM30_Wed, 4 Apr 2001 15:47:14 -0700536_us-ascii Thanks everyone for your very helpful explanations. Sorry for delay in answering your question, Rick. Been out of office awhile. EDM pool is 15,360K pages, so I suppose I would be releasing 1,536K pages at 10%.

Thanks, alison. --- Charles F Creech wrote: > Alison, > > ----------- ------- ---- > In Use 1535 10% > DBDs 764 5% > CTs 141 1% > PTs 630 4% > > The part of your display that I have pasted above is > telling you what is in > use at that instant in time. 10% of the total EDM > [...] 13009 180 39_Re: How to skip first n matched rows ??14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 5 Apr 2001 00:02:36 +0100600_iso-8859-1 If your SQL supports scrollable cursors than YES, otherwise NO

For YES....

DECLARE CURSOR ....... SCROLLABLE OPEN CURSOR FETCH CURSOR ...... ABSOLUTE 25 (fetches the 25th row)

FETCH FETCH FETCH

etc

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



-----Original Message----- From: Mohammed Nayeem [mailto:Mohammed_Nayeem@MOMED.COM] Sent: 04 April 2001 21:04 To: DB2-L@RYCI.COM Subject: [DB2-L] How to skip first n matched rows ?? [...] 13190 228 39_Re: How to skip first n matched rows ??13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 4 Apr 2001 18:46:19 -0500349_iso-8859-1 RE: [DB2-L] How to skip first n matched rows ??Nayeem,

If you are on UDB non-OS/390 V7.1 then you can use the row number function to allocate a row number and select only those rows greater than that row number, otherwise you can use scrollable cursors with CLI (not embedded SQL). OS/390 V7 also supports scrollable cursors. [...] 13419 107 16_Re: Index Access13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 4 Apr 2001 18:56:29 -0500356_iso-8859-1 Mayflor,

Given that the optimizer utilizes the catalog statistics to determine the access path, then the problem generally lies there as to why a poor index choice was made. The optimizer is the best cost based optimizer available (on OS/390 anyway........starburst on non-OS/390), but can only work with the statistics you provide. [...] 13527 94 27_Re: DB2 V5 Ignoring index ?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 4 Apr 2001 19:10:48 -0500569_iso-8859-1 Cam,

This was particularly a problem in V4 when DB2 would find the primary key index and then stop looking for any others.......But V5 did resolve this....Just not for you for some reason.

You wouldn't think it would make a difference since this is index only, but did you also run runstats on the original index? Runstats may have been run on the first when the table was well organized, and that is no longer the case. But the clusterratio of the new index should not be an issue since it never needs to access the data (unless you have [...] 13622 31 17_Cutover Checklist30_GROUPIT - Jenny Kuan Kong Lang19_jennykkl@MAS.COM.MY30_Thu, 5 Apr 2001 09:07:43 +0800505_iso-8859-1 I have been tasked to come up with a generic checklist for the cutover of distributed/open systems covering::

1) application server (on NT or AIX) 2) database server (could be NT/AIX or OS/390) 3) interface with legacy applications in either batch or realtime mode, running on OS/390 or TPF environments via FTP, IMS/TCP, APPC, or middleware in future 4) Internet access 5) Interface with email systems 6) Security issues 7) Operational issues 8) Software management etc. Any tips? [...] 13654 89 39_Re: How to skip first n matched rows ??18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Thu, 5 Apr 2001 11:18:57 +0930351_us-ascii Howzit Mohammed?

I'll assume you are going to order by a column that is not included in an EQUAL predicate i.e. COL3. IMHO the "ORDER BY COL2" doesn't make sense and wastes DB2 processing when you have "AND COL2 = 'VALUE2'" in your predicate.

If my assumption is invalid then ignore this post, otherwise try the following: [...] 13744 37 26_Re: Alternative to listcat10_teldb2kals22_teldb2kals@TELSTRA.COM30_Thu, 5 Apr 2001 13:34:17 +1000434_us-ascii Hi Daryl,

U should be able to get the info on UCBSCAN from the MVS Assembler Services Reference manual.

The CSI info is there in the DFSMS Managing Catalogs manual. (See Appendix D).

Hope this helps.

Cheers, Kals.



-----Original Message----- From: Daryl Johnson [SMTP:djjan@TELEPORT.COM] Sent: Thursday, April 05, 2001 2:35 AM To: DB2-L@RYCI.COM Subject: Alternative to listcat [...] 13782 115 39_Re: How to skip first n matched rows ??15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM30_Thu, 5 Apr 2001 13:31:45 +1000609_- Wouldn't this SQL perform the subselect once for every row of the outer table? I'm no expert but can't imagine any cardinalities where it would perform better than Fetching and ignore the first 25 rows in a cursor, even if materialisation was necessary?

regards, Steve



> -----Original Message----- > From: Bruce W Williamson [SMTP:Bruce.Williamson@NT.GOV.AU] > Sent: Thursday, April 05, 2001 11:49 AM > To: DB2-L@RYCI.COM > Subject: Re: How to skip first n matched rows ?? > > Howzit Mohammed? > > I'll assume you are going to order by a column that is not included in an > EQUAL [...] 13898 42 34_Re: Bind vs db2rbind and SQL0818's16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 5 Apr 2001 10:03:05 +0530439_- It looks you are asking the question for some other platform, i am writing from DB2 UDB for OS/390 point of view. But it looks that the concept should be same.



> Questions and ANSWERS > > 1. What timestamps are created and where during a Precompile? > A. Timestamp (consistency token) is created during precompile in a working storage put during the precompilation time. The same time stamp is given to DBRM as well. [...] 13941 152 39_Re: How to skip first n matched rows ??18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Thu, 5 Apr 2001 14:16:14 +0930789_us-ascii Howzit Steve?

You've got it in one.

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia









"Mallett, Steven" @RYCI.COM> on 05/04/2001 13:01:45

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: How to skip first n matched rows ??





Wouldn't this SQL perform the subselect once for every row of the outer table? I'm no expert but can't imagine any cardinalities where it would perform better than Fetching and ignore the first 25 rows in a cursor, even if materialisation was necessary? [...] 14094 135 37_Re: Current sqlid.... immediate reply16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 5 Apr 2001 10:22:19 +0530543_iso-8859-1 Kumar,

In continuation of the mail from Rob, There are two kind of authorization ids exists at present. Primary authorization id -> Your TSO logon id Secondary id -> RACF group id from which all the required TSO ids are connected.

Normally instead of assigning the individual previleges to all the users, it is better to connect it to a group for using its previleges. If you do not have SYSADM authority, you can attach your id to the SYSADM id via RACF and then use SET CURRENT SQLID to perform the operations. [...] 14230 31 31_DB2 Load & Unload Functionality17_Demetris Kasheris19_DKasheris@LAIKI.COM30_Thu, 5 Apr 2001 07:52:20 +0300322_us-ascii Hi List. We have DB2 V6.1 on OS/390.

These are some inquiries set by a programmer:

1. We have files that are are needed to be loaded in DB2 and a flag is required as a constant to be added in the Load job. The input dataset does not contain any data for this field. Is this possible with DB2 ? [...] 14262 63 32_Double Byte Character Set (DBCS)11_John Matear32_John.Matear@CBS-SOLUTIONS.COM.AU30_Thu, 5 Apr 2001 13:23:48 +0800586_us-ascii The software company I work for are looking into expanding into the Asia- Pacific market. We are marketing a financial system that is CICS/DB2. Currently the system only supports single byte character set (SBCS).

We are wanting to set up a "Proof of Concept" system for potential customers so that they can get a feel for how the system works and also to see how we can handle DBCS. Initially this would be , for example, simply allowing the clients to dial into our system and for them to perhaps add new customer information etc. We have determined that customer [...] 14326 30 62_Alter Table ADD Colum caused -904 on package - REASON 00C9009018_Siegfried Lindhoff44_Siegfried_Lindhoff@INFORMATIK-KOOPERATION.DE30_Thu, 5 Apr 2001 08:36:14 +0100428_us-ascii Can someone explain the following behavior: For an ALTER TABLE G000.LZ_ROUTING ADD ENTLADE_VORMERK_KZ CHAR(1) NOT NULL WITH DEFAULT 'N';

Column "VALID" on SYSIBM.SYSPACKAGE for MLZ08010 changed from "Y" to "A"

we are getting: SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90090, TYPE OF RESOURCE 00000801, AND RESOURCE NAME G000.MLZ08010.162436DA19FE62A0 [...] 14357 83 39_DB2 PERFORMANCE ON OS/390 vs DB2 ON AIX17_Demetris Kasheris19_DKasheris@LAIKI.COM30_Thu, 5 Apr 2001 09:19:07 +0300518_us-ascii A query performance test has been performed on DB2 V6.1 on our IBM 9672-R14 model with 43 MIPS, 1.2 GB Real storage and 0.2 expanded. The query on the host finishes in 3.53.9 min whereas a test of the same query on DB2 v 7.1 for AIX takes 1 min. On host 3.53.6 min is being spend on sorting. This is a data warehouse query and we are concerned why the same query on the same table structures with the same indexes (no index is used) performs so much better in DB2 for AIX. Why does sorting takes so much [...] 14441 99 19_Replication Problem19_Dimitris Margaritis20_margardi@NOVABANK.GR30_Thu, 5 Apr 2001 09:45:27 +0200491_iso-8859-7 Hi all, I have two DB2 UDB 7.1 servers on Windows NT 4.0 in a warm stand by replication scenario. After applying Fixpack 2 the apply program hangs with a dr Watson error. I uninstall Fixpach 2 from replica server and replication start working. (Primary server with Fixpack 2, replica without Fixpack). It works for a week but know the capture hangs with the error below. Any ideas ? Is there anyone in a replication environment on NT that has successfully install Fixpack 2? [...] 14541 109 39_AW: How to skip first n matched rows ??12_Peter, Georg15_G.Peter@DZBW.DE30_Thu, 5 Apr 2001 09:00:41 +0200638_iso-8859-1 Phil,

to "FETCH CURSOR ...... ABSOLUTE...": AFAIK this is a DB2 for OS/390 and z/OS Version 7 feature. Or is it availabe in Version 6 also ?

With kind regards - mit freundlichen Grüssen, Georg H. Peter DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language): http://www.dzbw.de [...] 14651 154 39_Re: How to skip first n matched rows ??16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 5 Apr 2001 08:46:17 +0100598_iso-8859-1 Hallo Deutschland,

Scrollable cursors are available in UDB V7 for z/OS and OS/390. I am not aware that it is deliverable on a PTF for V6.

Steen Rasmussen Computer Associates Senior Consultant



> -----Original Message----- > From: Peter, Georg [SMTP:G.Peter@DZBW.DE] > Sent: Thursday, April 05, 2001 9:01 AM > To: DB2-L@RYCI.COM > Subject: AW: How to skip first n matched rows ?? > > Phil, > > to "FETCH CURSOR ...... ABSOLUTE...": AFAIK this is a DB2 for OS/390 and > z/OS Version 7 feature. Or is it availabe in Version 6 also ? > > With kind regards - [...] 14806 196 23_a question about rebind3_joe20_zoujie@MAIL.DCAC.COM30_Thu, 5 Apr 2001 15:57:34 +0800563_gb2312 Dear list: I have post a mail for some help about a problem of rebind, it is still a cancer of our DB2 system,and I do want to know why it occurs. The problem is : When we rebind a package,it runs for a long time until the time of limit reached.we got a system code of x'0c4' and reason code '10'.It looks like something gone wrong about the translation of storage. The status of database and tablespace is right,and we can select data from the table. Last time,I dropped the tablespaces and re-created them,migrated data into new tablespaces,then we [...] 15003 50 67_Re: Alter Table ADD Colum caused -904 on package - REASON 00C9009 016_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 5 Apr 2001 13:47:24 +0530653_- Could you identify what is the resource name mentioned below in the error message ? It is surely a locking problem as mentioned in the manual

Regards Sanjeev

> -----Original Message----- > From: Siegfried Lindhoff > [SMTP:Siegfried_Lindhoff@INFORMATIK-KOOPERATION.DE] > Sent: Thursday, April 05, 2001 1:06 PM > To: DB2-L@RYCI.COM > Subject: Alter Table ADD Colum caused -904 on package - REASON > 00C90090 > > Can someone explain the following behavior: > For an > ALTER TABLE G000.LZ_ROUTING > ADD ENTLADE_VORMERK_KZ CHAR(1) NOT NULL WITH DEFAULT 'N'; > > Column "VALID" on SYSIBM.SYSPACKAGE for MLZ08010 changed from "Y" to "A" > [...] 15054 40 27_Migrate to DB2 OS/390 V 7.111_John Curran26_John.Curran@DRESDNERKB.COM30_Thu, 5 Apr 2001 09:42:11 +0100302_iso-8859-1 Hi

We are currently on DB2 OS/390 V 5.1 and expect to upgrade DB2 to either Version 6 or Version 7.

My question is does any body else have plans to migrate from 5 to 7 in the next 3 to 6 months. and is this a good idea?

IBM say you can does the DB2 community agree [...] 15095 89 31_AW: Migrate to DB2 OS/390 V 7.112_Peter, Georg15_G.Peter@DZBW.DE30_Thu, 5 Apr 2001 10:53:37 +0200352_iso-8859-1 John,

DB2 for OS/390 Version 5 is stable and somewhat like rock-solid now. But if you have the need to migrate it is possible to migrate directly to version 7. This is what the IBM told us at the IBM Technical Conference held in Hamburg, Germany, in March 2001.

By the way: What are YOUR reasons to migrate to version 7 ? [...] 15185 26 39_Re: How to skip first n matched rows ??15_Lucchetti Bruno22_B.Lucchetti@BARILLA.IT30_Thu, 5 Apr 2001 10:57:50 +0200541_iso-8859-1 Scrollable cursor, as new star jon and other features, is available in the refresh of may/2000 of DB2 V6 for os/390. I read it on Ibm announcemet of the refresh. Regards



Bruno Lucchetti

Barilla Alimentare S.P.A.

Via Mantova 166

43100 Parma

Italy

================================================ 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. 15212 21 38_inserting and retreiving images in db230_=?iso-8859-1?q?Hari=20Kishen?=20_kish3006@YAHOO.CO.IN30_Thu, 5 Apr 2001 11:19:06 +0100575_iso-8859-1 Hi all, Can anyone explain me how to insert and retreive images into and from db2.I wanted to do this using rexx.Can anyone suggest a website with these information.Thanks in advance.

____________________________________________________________ Do You Yahoo!? For regular News updates go to http://in.news.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. 15234 80 76_Antwort: Re: Alter Table ADD Colum caused -904 on package - REASON 00C9009 018_Siegfried Lindhoff44_Siegfried_Lindhoff@INFORMATIK-KOOPERATION.DE30_Thu, 5 Apr 2001 11:59:31 +0100568_us-ascii The resource name: G000.MLZ08010.162436DA19FE62A0 is Collection Id.Package Name.Contoken SYSIBM.SYSPACKAGE shows for the Package MLZ08010 VALID = 'A': that Package is not invalidated through the alter process. There was also no automatic rebind.

Siegfried Lindhoff







DB2 Data Base Discussion List on 05.04.2001 09:17:24

Bitte antworten an DB2 Data Base Discussion List

An: DB2-L@RYCI.COM Kopie: Thema: Re: Alter Table ADD Colum caused -904 on package - REASON 00C9009 0 [...] 15315 19 66_Re: Alter Table ADD Colum caused -904 on package - REASON 00C9009015_Walter Janissen27_walter.janissen@VICTORIA.DE30_Thu, 5 Apr 2001 05:09:40 -0500465_- Hello Siegfried

I've made similar experiences. If you alter a table, the column VALID is changed to 'A' and therefore I think, a package cannot be executed concurrently. And if you bound your plan with RELEASE(DEALLOCATE) packages could be held over a long time.

A few weeks ago, I opened a thread where I described a similar problem. There I got a -904 creating a new index with a package, which only read the table. Nobody could explain that [...] 15335 40 61_April Events - Central PA - Classes & FREE User Group Meeting10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Thu, 5 Apr 2001 06:34:17 -0400356_iso-8859-1 Hi everyone. These are the events planned for April. You can review abstracts/bios on the website: http://www.db2parug.org. If you are planning to attend the FREE User Group meeting on 4/19 OR any of the Claussen/Larsen classes, please respond asap. Tks!! Seeya

LINDA CLAUSSEN CLASSES: 4/17 - Triggers - $150 4/18 - Cross-DBA - $150 [...] 15376 80 42_AW: inserting and retreiving images in db212_Peter, Georg15_G.Peter@DZBW.DE30_Thu, 5 Apr 2001 12:45:24 +0200658_iso-8859-1 Hari,

take a look at http://www-4.ibm.com/software/data/db2/extenders/. There is some information about import and export objects such as images, audio clips, video clips and their attributes into and out of a database.

And under http://www-4.ibm.com/software/data/db2/extenders/image.htm it may be that you'll find exact the things you are asking for.

If you do not want to work with the extenders here's a simple solution from my company: We had the need to store (and store only) small images in DB2 for OS/390 (without any extenders). And because there was no need to bring these images on a screen (DB2 is used as a [...] 15457 98 53_DB2 Connect PE compatibility versus DB2 UDB & Windows15_Lucchetti Bruno22_B.Lucchetti@BARILLA.IT30_Thu, 5 Apr 2001 12:54:27 +0200390_iso-8859-1 I resend a previous question because of the attached table is not in the Archives, so I believe nobody had seen the table.

Hi Guru,

Referring to Db2 Connect Personal Edition,of which are available several versions, I have a question. We are running DB2 UDB for OS/390 V5R1 and using DB2 Connect PE 5.2 on Windows 95 and Windows NT. We are planning to migrate: [...] 15556 34 67_Re: Alter Table ADD Colum caused -904 on package - REASON 00C9009 013_Hanne Lyssand10_han@VPS.NO30_Thu, 5 Apr 2001 12:38:13 +0200556_us-ascii Check the OPERATIVE in SYSIBM.SYSPACKAGE If it is "N" then DB2 makes a automatic REBIND when you use the package. Try to make a REBIND your self and see if you get any errors.

Best regards from Hanne









********************************************************************** 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 system manager. [...] 15591 31 16_Re: Index Access13_Shauna Hadden24_shauna.hadden@BCBSKS.COM30_Thu, 5 Apr 2001 07:03:22 -0500315_- Actually Terry, there are a few things not in Mayflor's posting. IX00500B is a partitioned, clustered (99%) index. IX00500G is a nonpartitioned index and the cluster ratio is 45%.

In the last month, the number of partitions has increased from 60 to 84 and SEVERAL months worth of data has been added. [...] 15623 88 17_Re: SQL Date Math10_Chris Kern23_Chris_Kern@VANGUARD.COM30_Thu, 5 Apr 2001 08:36:56 -0400508_us-ascii You're correct - my bad typing skills come shining thru once again.







Terry Purcell on 04/03/2001 08:11:03 PM Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Chris Kern/IT/VGI) Subject: Re: SQL Date Math





Chris,

I'm intrigued.....You say:

"SELECT CURRENT DATE - DATE('1965-04-26') FROM SYSIBM.SYSDUMMY1 ;

gives me answer of 351127 (YYMMDD)" [...] 15712 195 39_Re: How to skip first n matched rows ??14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 5 Apr 2001 13:50:57 +0100538_iso-8859-1 Thanks Steen

You are right

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

-----Original Message----- From: Rasmussen, Steen Sent: 05 April 2001 08:46 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] How to skip first n matched rows ??





Hallo Deutschland,

Scrollable cursors are available in UDB V7 for z/OS and OS/390. I am not aware that it is deliverable on a PTF for V6. [...] 15908 63 37_Re: Current sqlid.... immediate reply14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 5 Apr 2001 07:35:03 -0500528_- One alternative that I would consider is: - for each environment have a different plan - each plan points to a different collection - in each collection the package is bound DYNAMICRULES(BIND) QUALIFIER () OWNER()

Now, by running FOCUS under the appropriate plan, dynamic sql will automatically be qualified by the package's QUALIFIER - you don't need to SET CURRENT SQLID, and, in fact, it will be ignored. If you needed to, you could SET CURRENT PACKAGESET to switch between environments. [...] 15972 174 37_Re: Current sqlid.... immediate reply0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM30_Thu, 5 Apr 2001 08:08:47 -0500511_us-ascii Kumar, An addition to what Sanjeev and Rob mentioned, if you do have RACF and you don't know what a persons secondary id and what to find out, I believe you can issue the following commands. At least this is how I check.

TSO LU (tells you about what group you are in and when they were set up) TSO TU xxx (xxx- supply the id of the person you want to find the secondary auth group they are in) for us its our tso id. TSO TG xxxxxxxx ( xxxxxxxx- supply the secondary id and it will show you [...] 16147 29 27_Re: a question about rebind13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Thu, 5 Apr 2001 09:14:05 -0400412_iso-8859-1 Hi Joe,

I think the best advice you received on this problem was from Roger Miller.

Contact IBM service.

It's been my experience with this list that if you describe an abend, if others have encountered it, they are more than happy to respond with their experience and resolution where available.

If you haven't received any responses by now, I'd contact IBM directly. [...] 16177 104 35_Re: AW: Migrate to DB2 OS/390 V 7.114_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 5 Apr 2001 14:18:15 +0100332_iso-8859-1 Paraphrasing IBM.....

"If you are on V5, you are NOT leading edge DB2 users. If you migrate from V5 to V7 you are going to become a leading edge user of DB2 AND an early user of the V5/V7 coexistence code. Unless you have an urgent need for V7 features, you are probably better (aka safer) to go to V6 first" [...] 16282 22 17_DB2PM sort option0_16_ccemilog@YKB.COM30_Thu, 5 Apr 2001 16:30:37 +0300331_us-ascii Hi fellow users, Is there a way to override the DFSORT options that DB2PM uses while trying to get a DB2PM batch report? I can do it if I use an option control statement within SORT JCL, But I need to do it with the db2pm exec. MVS guys donot want to change DFSORT defaults, so I have to do it the other way.. Thanx, [...] 16305 45 38_DSNTEP2 message with insert,delete etc10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 5 Apr 2001 15:25:59 +0200519_us-ascii Hi list, pardon my abysmal ignorance......

Is there a way to avoid the verbose mode in DSNTEP2 ? I need only the end of action:

SUCCESSFUL INSERT OF 1 ROW(S)

and I need to discard all SQL diagnosis informations:

***INPUT STATEMENT: INSERT INTO bla bla bla

RESULT OF SQL STATEMENT: DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION DSNT416I SQLERRD = 0 0 1 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'00000000' X'00000000' X'00000001' X'FFFFFFFF' X'000 INFORMATION [...] 16351 63 31_Re: Migrate to DB2 OS/390 V 7.116_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Thu, 5 Apr 2001 09:31:46 -0400481_iso-8859-1 We are at V5 at a very current maint level including all V6/V7 migration/fallback PTFs. We intend to go directly to V7. We want to do this rather than V5 to V6 to V7 so that we can have one outage rather than two.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: John Curran [mailto:John.Curran@DRESDNERKB.COM] Sent: Thursday, April 05, 2001 4:42 AM To: DB2-L@RYCI.COM Subject: Migrate to DB2 OS/390 V 7.1 [...] 16415 56 40_Re: DB2 Personal Edition and DB2 Connect5_Chris17_isatcjk@YAHOO.COM30_Thu, 5 Apr 2001 06:34:05 -0700604_us-ascii Why would you need both on one workstation?

Either would work for the local machine.

Are these workstations also going to be used as communication servers for other people who do not have any DB2 Connect PE (i.e. DB2 connect runtime (CAE client application enabler))?

Chris

--- BILL_GALLAGHER@PHL.COM wrote: > Hi, > > We have some developers who have a need to create local DB2 databases on > their NT workstations, as well as access host DB2 tables from the same > workstation. > > They are trying to install DB2 Personal Edition v6.1 and DB2 Connect v6.1 > on [...] 16472 199 43_Version 7 Vs Version 6 - Scrollable cursors11_Pavan Borra26_pavan.borra@PRUDENTIAL.COM30_Thu, 5 Apr 2001 09:48:19 -0400385_us-ascii





Speaking of the latest features in DB2 for OS/390 Version 7, Does anyone have a summary document listing the latest features in Version 7 ( when compared to Version 6 ) for OS/390 ?? If so, I appreciate if it can be sent to everyone on this list.

I know I can refer to the manuals, but such a document would be helpful for all the listers. [...] 16672 106 40_Re: DB2 Personal Edition and DB2 Connect0_22_BILL_GALLAGHER@PHL.COM30_Thu, 5 Apr 2001 09:56:32 -0400534_us-ascii Chris,

The developers need to have local databases on their machines for their development environment, hence the need for DB2 Personal Edition. They will also have a need to access host databases on OS/390, since that's where their integrated test environment will be, hence the need for DB2 Connect.

I believe that I've found the answer to my original question. DB2 Personal Developers Edition bundles DB2 PE and DB2 Connect PE in one product. That's what I've advised our developers to acquire and use. [...] 16779 143 42_Re: DSNTEP2 message with insert,delete etc9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK30_Thu, 5 Apr 2001 15:03:51 +0100694_iso-8859-1



Max, Ive had a look at the V5 source code and it looks to me as though you need to bypass the following code in the 'Process SQL Output Message section' 340700 /*******************************************************************/ 340800 /* PROCESS SQL OUTPUT MESSAGE */ 340900 /*******************************************************************/ 341000 341100 PUT SKIP EDIT (' RESULT OF SQL STATEMENT: ') (COL(1), A(29)); 341200 CALL DSNTIAR ( SQLCA, MESSAGE, MAXPAGWD); /* FORMAT ANY MESSAGES */ 341300 IF PLIRETV ¬= ZERO THEN /* IF THE RETURN CODE ISN'T ZERO */ 341400 DO; /* ISSUE AN ERROR MESSAGE */ 341500 PUT EDIT (' RETURN CODE ', PLIRETV, 341600 ' FROM [...] 16923 83 35_Re: DB2 Load & Unload Functionality0_29_sflindsey@HIGHLIGHTS-CORP.COM30_Thu, 5 Apr 2001 10:15:26 -0400572_us-ascii 1. Define the column with "NOT NULL WITH DEFAULT 'default value'". Do not have this column in the load cards and the column will be default to the default value.

2. The load utility would interpret spaces and 0000-00-00 both as invalid dates. You could use the DEFAULTIF option to interpret the value in the columns for the date and default it to the default value of the column. The DEFAULTIF option can only evaluate one of these values. You cannot have multiples. I would write a program that would run before the load utility to change all of the [...] 17007 14 47_Re: Version 7 Vs Version 6 - Scrollable cursors14_Clayton, Colin21_Colin_Clayton@BMC.COM30_Thu, 5 Apr 2001 09:13:39 -0500336_iso-8859-1 Pavan,

try this link http://www-4.ibm.com/software/data/db2/os390/v7annc.htm

================================================ 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. 17022 31 27_Re: DB2 V5 Ignoring index ?13_Chadwick, Cam27_Cam.Chadwick@LONDONLIFE.COM30_Thu, 5 Apr 2001 10:25:17 -0400683_- Thanks for your responses - this working now.

I reran RUNSTATS, specifying .....

RUNSTATS TABLESPACE(name) INDEX(ALL) KEYCARD FREQVAL NUMCOLS 10 COUNT 10

...and the optimizer now chooses the new IX.

I had previously specified....

RUNSTATS TABLESPACE(name) INDEX(ALL)



Cam Chadwick Information Services , Senior Consultant London Life Insurance Co. (519) 435-7524 cam.chadwick@londonlife.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. 17054 121 35_Re: AW: Migrate to DB2 OS/390 V 7.116_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM30_Thu, 5 Apr 2001 10:26:57 -0400500_us-ascii





Phil,

FYI - The reason why we are going to V7 (from V5) is so we can dump both CA and BMC (and associated products) and go true blue.

Rick Molera













"Grainger, Phil" on 04/05/2001 09:18:15 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: RICHARD E MOLERA/SallieMae) Subject: Re: AW: Migrate to DB2 OS/390 V 7.1 [...] 17176 45 23_Re: Replication Problem11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID28_Thu, 5 Apr 2001 14:34:48 GMT401_ISO-8859-1 Dimitris,

Are you using the usual Apply & Capture program supplied by DB2 (asnaccp & asnapply)? With DB2 ver.7.1, you can use Warehouse Center to make the Replication much easier, although still using the same architecture, but with more visual interface and also has the scheduling capability. Perhaps you can re-try making your Replication Scenario with the Warehouse Center. [...] 17222 22 42_Re: DSNTEP2 message with insert,delete etc10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 5 Apr 2001 16:43:18 +0200331_us-ascii Hi Andy, thanks for your reply.

I think I'll remove these rows from program source to avoid the messages.

THX again

Max Scarpa

PS The wish is in US ($$) dollars, the normal salary for a DB2 sysprog outside Italy, as far as I know (1 zillion and 300 furlongs dollars at least & per month). [...] 17245 88 35_Re: AW: Migrate to DB2 OS/390 V 7.116_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Thu, 5 Apr 2001 10:50:25 -0400433_iso-8859-1 Richard, DITTO here. We intend to use V7 to get rid of as many CA products as possible. I only wish DB2 V7 had a scheduler and a tape management system!

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: RICHARD E MOLERA [mailto:RICHARD.E.MOLERA@SLMA.COM] Sent: Thursday, April 05, 2001 10:27 AM To: DB2-L@RYCI.COM Subject: Re: AW: Migrate to DB2 OS/390 V 7.1 [...] 17334 89 27_Re: a question about rebind10_Missy Case28_Missy.Case@FIRSTDATACORP.COM30_Thu, 5 Apr 2001 10:03:38 -0500360_us-ascii Joe,

Does someone have one of your libraries allocated with DISP=OLD in a batch job or started task??? Does the bind package function work in the DB2 online screens? If so, then at least you'd get the bind done and have a starting place to look for anomalies between the online process libraries & the batch process libraries & functions. [...] 17424 26 7_SEGSIZE0_19_mike.holmans@BT.COM30_Thu, 5 Apr 2001 16:08:33 +0100396_- DB2 for OS/390 V5/V6.

What are the main disadvantages of a small SEGSIZE for medium-sized (say 5000 pages) tablespaces which contain only one table?

I just noticed that we have a number of tables with 200K rows in tablespaces with SEGSIZE 4 (nobody thought to change the SEGSIZE when creating the DDL for production), and I wondered what diseases we might catch as a result. [...] 17451 73 20_Re: Trigger question10_Shery Hepp17_schepp@SRPNET.COM30_Thu, 5 Apr 2001 08:37:35 -0700493_iso-8859-1 Steve/Robert/Walter- What I did to resolve this issue was to have the trigger invoke a stored procedure and then the stored proc determines which process to execute. If there is a way to accomplish this type of logic with 1 trigger- knowing that would be great. Seems like even though case is valid in sql it is not in a trigger. Unfortunately the developers are on a tight schedule (as usual) and we just don't have the cushion to spend a lot of time trying different things. [...] 17525 43 15_Re: DB2 Columns10_Linda Ball18_linda_ball@BMC.COM30_Thu, 5 Apr 2001 10:22:34 -0500578_- There shouldn't be a problem recovering to a copy before columns were added. When columns are added, they are NOT instantiated in the rows. Something in the DBD indicates that the columns are variable because, until REORG, newly inserted or updated rows may have the columns but old ones will not. As long as copies are available that could recreate this state, the setting stays. When you MODIFY to remove such copies and the space has been REORGed, it is removed, I believe. One problem you can have is shipping data before a REORG to another system and using DSN1COPY [...] 17569 116 16_Re: Index Access12_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US30_Thu, 5 Apr 2001 10:48:20 -0500551_US-ASCII It appears that the optimizer has chosen the 'G' index over the 'B' index based on cardinality. For your request, the DB2 would have to retrieve all the index rows from the 'G' index that matched the first 2 columns of the index (typically, this would be a good thing), but the cardinality of the first key column is only 68. This means that a possible (106,000,000 / 68 ) index rows could qualify (and have to be scanned). Then the RIDs for each row id would have to read in and checked for the remaining predicate (Would this classify [...] 17686 139 16_Re: Index Access16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Thu, 5 Apr 2001 11:53:37 -0400466_iso-8859-1 Another option is to change predicate: AND (T1.ITS_CLM_TYP IN('M','K')) to AND ((T1.ITS_CLM_TYP IN('M','K')) OR 1 = 0) which usually tricks the optimizer into not selecting the index on that predicate's column.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: DOUG KESTNER [mailto:DHSTS57@DHS.STATE.IL.US] Sent: Thursday, April 05, 2001 11:48 AM To: DB2-L@RYCI.COM Subject: Re: Index Access [...] 17826 84 15_Re: DB2 Columns17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Thu, 5 Apr 2001 09:10:10 -0700425_us-ascii Thanks Linda for the info (no it wasn't more than I needed). I was able to get some time and test the scenario out and even though the load failed using input from the reorg unload the image copy recover as you stated was successful.









Linda Ball cc: Sent by: DB2 Subject: Re: DB2 Columns Data Base Discussion List [...] 17911 63 35_Re: AW: Migrate to DB2 OS/390 V 7.164_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM30_Thu, 5 Apr 2001 11:17:55 -0500579_ISO-8859-1 Eric and Richard,

What BMC and/or CA products do you feel you can get rid of, specifically because you are migrating to DB2 V7? Any why?

Todd.

-----Original Message----- From: Eric.Pearson@NSCORP.COM Sent: Thursday, April 05, 2001 10:06 AM To: DB2-L@RYCI.COM Subject: Re: AW: Migrate to DB2 OS/390 V 7.1

-------------------------------------------------------------------------- -- Richard, DITTO here. We intend to use V7 to get rid of as many CA products as possible. I only wish DB2 V7 had a scheduler and a tape management system! [...] 17975 29 42_OMEGAMon/DB2 DB2V6.1 OS/390 v2r10 question11_Robert Jans26_robert_jans@ALBERTSONS.COM30_Thu, 5 Apr 2001 10:13:28 -0600402_iso-8859-1 Good day, Fellow Listers. I've a situation that just begs for an explanation and I really don't understand why this is happening. We're DB2V6.1 w/ Feb 2001 Maintenance just applied, OS/390 V2R10. This has only been occurring since we applied the maintenance.

OMEGAMon for DB2 shows us the following:

Job name Serv Class CPU % TCB % SRB % D2A1DBM1 (Enclaves) 112.2 n/a n/a [...] 18005 95 35_Re: AW: Migrate to DB2 OS/390 V 7.116_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Thu, 5 Apr 2001 12:32:08 -0400472_iso-8859-1 I would like to get rid of Rapid Reorg, Quick Copy, and Fast Unload ASAP. Long-term we want to dump Log Analyzer, Detector, Fast Load, Database Analyzer, Plan Analyzer, Rc/Update, RC/Migrator, Rc/Query (i.e. all the former Platinum products).

Why? Because they are CA meaning: 1) CA pricing 2) CA support 3) CA salescritters all of which we are better off without. Also because we can get out of the CA blames IBM blames CA blames IBM support loop. [...] 18101 66 47_Re: Version 7 Vs Version 6 - Scrollable cursors16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Thu, 5 Apr 2001 17:23:13 +0100366_us-ascii Sorry if some of you receive this twice, but I got a note saying it had got rejected because of the number of lines...

Pavan,

there is an official announcement page from IBM, you can find it at

http://www-4.ibm.com/software/data/db2/os390/v7annc.htm

and I guess it does not get much more available to everyone than that ;-) [...] 18168 122 35_Re: AW: Migrate to DB2 OS/390 V 7.114_Hickman, Mindy29_Mindy.Hickman@DOA.STATE.WI.US30_Thu, 5 Apr 2001 11:52:00 -0500467_iso-8859-1 We would also like to get off of the Platinum products since they do not support the version 6 features and from our talks with them, they don't appear to be inclined to do so anytime soon. We use the CDB utilities, which we are very happy with, but are looking for a RC/Update, RC/Migrator, etc substitute. We're thinking of bringing in the DB Admin tool from IBM. Has anyone had any experience with this product and what are your impressions of it? [...] 18291 59 46_Re: OMEGAMon/DB2 DB2V6.1 OS/390 v2r10 question0_24_William_O'Black@FFIC.COM30_Thu, 5 Apr 2001 10:37:00 -0700457_us-ascii Robert, we're running PeopleSoft 7.5 with users coming in via 3-tier connection. The explanation we've received is that with 3-tier connections, a very high percentage of the CPU time incurred by these processes (99+%) is charged to enclaves.

Bill









Robert Jans @RYCI.COM> on 04/05/2001 09:13:28 AM

Please respond to DB2 Data Base Discussion List [...] 18351 132 40_Re: DB2 Personal Edition and DB2 Connect5_Chris17_isatcjk@YAHOO.COM30_Thu, 5 Apr 2001 10:57:36 -0700616_us-ascii Ah, I have a nasty habit of inserting 'connect' into db2 personal edition, sorry.

I assumed one was personal connect and the other enterprise edition connect. Thanks Chris

--- BILL_GALLAGHER@PHL.COM wrote: > Chris, > > The developers need to have local databases on their machines for their > development environment, hence the need for DB2 Personal Edition. They > will also have a need to access host databases on OS/390, since that's > where their integrated test environment will be, hence the need for DB2 > Connect. > > I believe that I've found the answer to my original question. [...] 18484 158 40_Re: DB2 Personal Edition and DB2 Connect14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Thu, 5 Apr 2001 14:20:21 -0400414_iso-8859-1 All a developer needs to install is the DB2 Personal edition along with the Fixpaks (2A being the latest for V7.1). After that, the DB2 Client Configuration Assistant (in the installed DB2 Program folder on NT desktop) can be used to configure a connection to any local or remote database by selecting a TCP/IP connection and specifying a host name, port number and the local/remote database name. [...] 18643 14 32_Stored Procedure Debugging Tools0_20_bjnigh@HOUSEHOLD.COM30_Thu, 5 Apr 2001 10:27:24 -0700422_us-ascii Does anyone know of any good DB2 OS/390 Stored Procedure Debugging Tools? Also, has anyone used the Stored Procedure Builder from IBM and if so, how do you like that tool?

================================================ 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. 18658 32 16_DB2 CAF and IDMS17_Bruzdzinski, Mike24_MIKE.BRUZDZINSKI@SSA.GOV30_Thu, 5 Apr 2001 14:51:31 -0400398_iso-8859-1 We have a program that issues an OPEN CURSOR (for SELECT --no update) from an IDMS program.

If we test the program with the IDMS code commented out , it works frine getting the DB2 rows. If we comment out the DB2 CALL, the IMDS stuff works okay.

When we use both together, it fails with a -991 (Call attach was unable to establish an implicit Connect or open to DB2). [...] 18691 76 46_Re: OMEGAMon/DB2 DB2V6.1 OS/390 v2r10 question11_Robert Jans26_robert_jans@ALBERTSONS.COM30_Thu, 5 Apr 2001 13:07:36 -0600644_iso-8859-1 The only catch with this is the _ONLY_! thing running in the whole DB2 subsystem is 1 (one) batch job that Db2 split into 3 parallel tasks.





-----Original Message----- From: William_O'Black@FFIC.COM [mailto:William_O'Black@FFIC.COM] Sent: Thursday, April 05, 2001 11:37 AM To: DB2-L@RYCI.COM Subject: Re: OMEGAMon/DB2 DB2V6.1 OS/390 v2r10 question



Robert, we're running PeopleSoft 7.5 with users coming in via 3-tier connection. The explanation we've received is that with 3-tier connections, a very high percentage of the CPU time incurred by these processes (99+%) is charged to enclaves. [...] 18768 65 20_Re: DB2 CAF and IDMS12_Mark Granger22_the_grange@MBOX.COM.AU30_Thu, 5 Apr 2001 14:18:38 -0500477_us-ascii Mike, I've seen this mainly occur when an application has tried to do a CAF connect twice, and the second program doing the CAF connect doesn't handle the connection already existing. I can only offer DSNTRACE DD (with the -991) to try to track down the CAF connections, and check the DB2 DSNHDECP module connection defaults. If you don't find the problem, I can check with a friend who has some PL1/IDMS/DB2 programs and see if there is anything special he did. [...] 18834 17 28_convert database to all caps12_Vernon, John27_John.Vernon@TRS.STATE.TX.US30_Thu, 5 Apr 2001 14:30:57 -0500435_iso-8859-1 . I was wondering if automatic conversion of data to upper case was something that the database itself would do rather than having to do it programmatically.

Many thanks, john

================================================ 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. 18852 44 20_Re: DB2 CAF and IDMS12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Thu, 5 Apr 2001 14:27:00 -0400458_- We have our IDMS and DB2 calls in separate modules and they work fine in IDMS batch. However, we have not found a way to call DB2 programs from ADSO. According to CA, you can't do cross memory calls, that DB2 requires, from ADSO. If you are using ADSO, you will need some kind of APPC interface such as is available with EDASQL. It was a couple of years ago when we researched this, so there may be more recent developments making this info obsolete. [...] 18897 33 32_Re: convert database to all caps11_Clark, Tony26_Tony.Clark@TWC.STATE.TX.US30_Thu, 5 Apr 2001 15:35:40 -0500644_- In version 6 there is an UCASE or UPPER function that will return a string in upper case characters. This is ued in an SQL statement.

But version 5, probably programmatically......

> -----Original Message----- > From: Vernon, John [SMTP:John.Vernon@TRS.STATE.TX.US] > Sent: Thursday, April 05, 2001 2:31 PM > To: DB2-L@RYCI.COM > Subject: convert database to all caps > > . I was wondering if automatic conversion of data to upper case was > something that the database itself would do rather than having to do it > programmatically. > > Many thanks, > john > > ================================================ > To change [...] 18931 90 43_Order by using the Order by index or sort ?22_Boubakar Saim Haddache16_bsaim@OTOOBE.COM30_Thu, 5 Apr 2001 23:16:11 +0200400_iso-8859-1 Hello,

we are experiencing performance issues in one of our DB2 based applications. We could isolate and reproduce the issue into the following simple case : - a "corporate" table, with two colons, named "zipcode" and "wkfs" (standing for "Work Force Size") - this table contains 13 million rows - the following indexes have been created : "wipcode", "wkfs" and "wipcode+wkfs" [...] 19022 55 32_Re: convert database to all caps12_Clark, Kevin15_KClark@FDIC.GOV30_Thu, 5 Apr 2001 17:25:59 -0400477_- On a related issue.

If I have tables that are used by Network PCs (ASCII) only. Should the data be stored in ASCII ?

Kevin Clark

-----Original Message----- From: Clark, Tony [SMTP:Tony.Clark@TWC.STATE.TX.US] Sent: Thursday, April 05, 2001 4:36 PM To: DB2-L@RYCI.COM Subject: Re: convert database to all caps

In version 6 there is an UCASE or UPPER function that will return a string in upper case characters. This is ued in an SQL statement. [...] 19078 142 24_Question using DB2 alias22_Kennedy, Tom - Cendant23_Tom.Kennedy@CENDANT.COM30_Thu, 5 Apr 2001 17:21:41 -0400498_iso-8859-1 I hope someone can point me in the right direction here. We're having trouble trying to use an alias to communicate between two DB2 v6.1 subsystems via DDF.

Environment: 2 DB2 subsystems, both at v6.1. Problem: Trying to access a table via DDF.

Using DDF, we want to go from one subsystem to another to get access to a table. We created an alias. Here is our scenario: We want to go from subsystem DSNA to subsystem DSNB in order to access the table DB2APPL.ZIPCODES. [...] 19221 105 15_Re: DB2 Columns16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Thu, 5 Apr 2001 22:16:09 +0100426_us-ascii From the DB2 point of you, you might want to remember all those lovely little issues with nullable columns not being fed any data, of course, and such niceties...

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 email: aurora_dellanno@candle.com

1 Archipelago Lyon Way Frimley Camberley Surrey GU16 7ER UK [...] 19327 26 32_Pittsburgh DB2 Users Group - FYI10_Cathy Peck23_cathy.peck@HIGHMARK.COM30_Thu, 5 Apr 2001 17:30:39 -0400332_us-ascii FYI, I have modified the DB2 PA RUG website to display links to all 3 PA RUGs (Pitt, Central, Phila). So check out the chg to the website: http:www.db2parug.org

For Pitt folks, check out the Pitt calendar of events.

On 6/18, we will have a kickoff for the Pitt RUG. I should have the agenda out shortly. [...] 19354 88 36_Re: Stored Procedure Debugging Tools13_Andy Seuffert21_aseuffert@NEONSYS.COM30_Thu, 5 Apr 2001 17:21:27 -0500441_- You can use the IBM Visual Age Remote Debugger for debugging your DB2 stored procedures. The Visual Age debugger comes on the IBM Visual Age For Java (enterprise Edition) 3.5 CD. All you need to install is the debugger nothing else, it is located in the debugger directory off the root. You must also have the IBM CODE/370 Debugger installed on your OS/390 system, most shops should have this installed as it is packaged with LE/370. [...] 19443 214 39_Re: How to skip first n matched rows ??15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Thu, 5 Apr 2001 17:29:30 -0500571_us-ascii





I'm looking for better solution for Version 6.1 but not 7.1 ---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 04/05/2001 05:25 PM ---------------------------



Terry Purcell on 04/04/2001 06:46:19 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: How to skip first n matched rows ??





RE: [DB2-L] How to skip first n matched rows ??Nayeem, [...] 19658 12 39_Re: How to skip first n matched rows ??15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Thu, 5 Apr 2001 17:33:27 -0500316_us-ascii yes it is for ORDER BY Col3 but not col2 . Sorry for mistyped one

================================================ 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. 19671 32 39_Re: How to skip first n matched rows ??11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Thu, 5 Apr 2001 18:55:06 -0500464_ISO-8859-1 Bruno

I am sorry, but that is not correct. Scrollable cursors are not in the V6 May 2000 refresh. They are in V7.

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

Message text written by DB2 Data Base Discussion List >Scrollable cursor, as new star jon and other features, is available in the refresh of may/2000 of DB2 V6 for os/390. I read it on Ibm announcemet of the refresh. Regards [...] 19704 48 11_Re: SEGSIZE11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Thu, 5 Apr 2001 19:01:33 -0500585_ISO-8859-1 Mike

I have seen problems with heavy insert loads when the available freespace is all used up. Each insert had a very high Getpage count due to scanning all space map pages from first to last before inserting the row at the end. If the table is big in relation to the buffer pool, this could also result in additional I/O. Increasing the SEGSIZE from 4 to 64 reduced this Getpage cost significantly. A simple tablespace would have performed even better than a segmented tablespace in this scenario. I know Roger doesn't like simple tablespaces, but I am still a [...] 19753 199 37_Re: Current sqlid.... immediate reply10_teldb2kals22_teldb2kals@TELSTRA.COM30_Fri, 6 Apr 2001 10:10:51 +1000524_us-ascii Hi Shan,

I wd think that TU and TG are locally defined commands on ur system, customised to provide the infm u need. LU and LG are the basic TSO commands.

And yes, usage of LG will probably be restricted.

Cheers, Kals



-----Original Message----- From: Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM [SMTP:Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM] Sent: Thursday, April 05, 2001 11:09 PM To: DB2-L@RYCI.COM Subject: Re: Current sqlid.... immediate reply [...] 19953 26 17_DB2 Connect Logon17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Thu, 5 Apr 2001 17:18:48 -0700555_us-ascii I have a question regarding the login procedure in DB2 Connect via the Control Center. If you right click on the Systems and then Subsystems and then select Connect we receive the Connect window with the areas for userid and password info and underneath an SQL1403N code specifying that an invalid username/password were entered before we even have a chance to enter anything. Once we enter it we login without problems but my question is where can you specify a userid/password to DB2 Connect so as to bypass this initial window and error. [...] 19980 143 35_Re: AW: Migrate to DB2 OS/390 V 7.118_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Fri, 6 Apr 2001 09:54:00 +0930591_us-ascii



What's stopping you dumping them now?









RICHARD E MOLERA @RYCI.COM> on 05/04/2001 23:56:57

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: AW: Migrate to DB2 OS/390 V 7.1











Phil,

FYI - The reason why we are going to V7 (from V5) is so we can dump both CA and BMC (and associated products) and go true blue. [...] 20124 171 35_Re: AW: Migrate to DB2 OS/390 V 7.116_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM30_Thu, 5 Apr 2001 21:09:18 -0400540_us-ascii





Bruce,

If you knew anything about V5 (and below) utility performance, you would have never even asked....

RM











Bruce W Williamson on 04/05/2001 08:24:00 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: RICHARD E MOLERA/SallieMae) Subject: Re: AW: Migrate to DB2 OS/390 V 7.1









What's stopping you dumping them now? [...] 20296 14 12_Index Access0_14_mtdage@ATT.NET30_Fri, 6 Apr 2001 03:11:27 +0000315_- Terry,

Thank you for the response and suggestion.

Mayflor

================================================ 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. 20311 197 35_Re: AW: Migrate to DB2 OS/390 V 7.118_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Fri, 6 Apr 2001 13:19:20 +0930544_us-ascii

Oooooh! A bit touchy or should that be tetchy?

Oh knowledeable One, I no nuthin', Pleez sher with me anythin' 'bout V5 (and below) utility performance.

Standard disclaimer.









RICHARD E MOLERA @RYCI.COM> on 06/04/2001 10:39:18

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Re: AW: Migrate to DB2 OS/390 V 7.1 [...] 20509 53 36_AW: Stored Procedure Debugging Tools12_Peter, Georg15_G.Peter@DZBW.DE30_Fri, 6 Apr 2001 09:08:17 +0200737_iso-8859-1 Ask the Compuware people. Last week a salesman from Compuware Germany told me that his company will offer a possibility to debug SP's....

With kind regards - mit freundlichen Grüssen, Georg H. Peter DB2 Data Base Administrator c/o ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Software Development & Technology Center Knowledge Center Database Systems Krailenshaldenstrasse 44, 70469 Stuttgart, Germany e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- Life is 10% what happens to you and 90% how you react to it. [...] 20563 55 28_Re: Question using DB2 alias12_Simon George21_Simon.George@UBSW.COM30_Fri, 6 Apr 2001 08:22:55 +0100348_US-ASCII Hi Tom,

Simply suggestion I know but have you checked that the user on DSNA has authority to read the table on DSNB? Depends how you have set the DDF up but you may have to GRANT SELECT AT ALL LOCATIONS on the target table. I believe that the registration in communication database determines how authority checking is handled [...] 20619 125 46_Re: OMEGAMon/DB2 DB2V6.1 OS/390 v2r10 question16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Fri, 6 Apr 2001 09:50:59 +0100449_us-ascii Robert,

sound like you might have a problem with maintenance levels, please see the input I received from one of my colleagues in L2 support - do you wish to contact me offline, or your local friendly !Candle support office, for further details?

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 email: aurora_dellanno@candle.com [...] 20745 21 19_Alteration criteria14_Dash, Sushanta21_sushanta.dash@EDS.COM30_Fri, 6 Apr 2001 04:19:46 -0500382_- Hi Fellow Listers, Please clarify my doubts. I have a job to alter the pqty and sqty of tablespace based on percentage active. As far I know we can go for more pqty and sqty once the percntage active is more. But my doubt is what criteria I should follow for this. Like what are the limits for taking a new values for pqty and sqty based on what values of percentage active. [...] 20767 22 38_a basic question on stored procedures.18_Pudukotai, Nagaraj35_nagaraj.pudukotai@BLR.HPSGLOBAL.COM30_Fri, 6 Apr 2001 16:21:56 +0500368_- Dear list

I am new to stored procedures. I get SQLCODE -444 on the call to my stored procedure. SYSROUTINES table is populated appropriately (EXTERNAL_NAME column has the stored procedure name in it). Should the load module of the stored procedure be in a library which is in STEPLIB/JOBLIB of SPAS address space. Pardon me for this, if it sounds silly. [...] 20790 41 42_Re: a basic question on stored procedures.11_David Nance16_DWNance@FHSC.COM30_Fri, 6 Apr 2001 07:33:33 -0400617_US-ASCII Nagaraj, Your external name should be the name of the load module, and it should reside in your steplib dataset.

Dave Nance First Health Services, Corp. (804)527-6841

>>> nagaraj.pudukotai@BLR.HPSGLOBAL.COM 04/06/01 07:21AM >>> Dear list

I am new to stored procedures. I get SQLCODE -444 on the call to my stored procedure. SYSROUTINES table is populated appropriately (EXTERNAL_NAME column has the stored procedure name in it). Should the load module of the stored procedure be in a library which is in STEPLIB/JOBLIB of SPAS address space. Pardon me for this, if it sounds silly. [...] 20832 46 42_Re: a basic question on stored procedures.12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM30_Fri, 6 Apr 2001 07:45:05 -0400768_us-ascii Yes it should be there.







"Pudukotai, Nagaraj" @RYCI.COM> on 04/06/2001 07:21:56 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: a basic question on stored procedures.



Dear list

I am new to stored procedures. I get SQLCODE -444 on the call to my stored procedure. SYSROUTINES table is populated appropriately (EXTERNAL_NAME column has the stored procedure name in it). Should the load module of the stored procedure be in a library which is in STEPLIB/JOBLIB of SPAS address space. Pardon me for this, if it sounds silly. [...] 20879 26 36_Re: Stored Procedure Debugging Tools13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Fri, 6 Apr 2001 07:48:46 -0400627_iso-8859-1 >Georg H. Peter schreib: >Ask the Compuware people.

Latest Version of XPEDITER/TSO Introduces DB2 Stored Procedure Support Streamlining Error Resolution

See http://www.compuware.com/pressroom/news/2001/2001031501.htm or http://www.compuware.com/products/xpediter/

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. 20906 21 35_Partitioning compressed tablespaces10_Steve Lamb25_steve.lamb@POWERGEN.CO.UK30_Fri, 6 Apr 2001 06:28:31 -0500455_- Hi List,

I would appreciate any help/thoughts on the following please: Environment is DB2 OS/390 v6.1 with only the IBM supplied utilities and sample programs (DSNTEP2, DSNTIAUL etc.) I need to partition some (segmented) tablespaces which are compressed using the default IBM-supplied routine. What is the best (i.e. fastest) way of extracting the data from the tables (1 per tablespace) prior to the dropping/recreation of the tablespaces? [...] 20928 28 21_Re: DB2 Connect Logon11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Fri, 6 Apr 2001 08:12:13 -0400443_us-ascii The initial logon is to the Control Center, and you specify that ID and Password at the time you install the DB2 Connect Products (on NT). The connection to the database or subsystem when you right click on it is the RACF or Top Secret password for your primary ID... I do not think there is any connection to the two logons. You may try using the same RACF or Top Secret ID and password for the Control Center and see if bypasses the 20957 94 35_Problems Installing DB2 Fast Unload13_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US30_Fri, 6 Apr 2001 08:29:15 -0400568_US-ASCII Hi all,

We are trying to install DB2 Fast Unload V1R1 and have run into a problem.

We ran through the SMP/E process and unloaded all the data sets required for installation. We then started the customization process by creating a PDS for the INZRSAVE member. Then we followed the instructions for a new installation by modifying INZTDSN member and then tried to run the install procedure INZT01. This is a REXX Exec that is supposed to display a panel with various installation options and it is supposed to create a member called INZTVAR. [...] 21052 72 39_Re: Partitioning compressed tablespaces14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Fri, 6 Apr 2001 13:32:37 +0100395_iso-8859-1 Hi Steve,

Good to hear from you again! We just partitioned some 30M row compressed tables using BMC UNLOAD+. It was a breeze. REORG EXTERNAL is faster than DSNTIAUL, but your 3rd party tools are better.

Best regards, Marcus Davage

> 'The views expressed are my own and do not necessarily represent the > views or policy of my employer, Lloyds TSB Bank plc.' [...] 21125 133 36_Re: Stored Procedure Debugging Tools12_Myron Miller22_myronwmiller@YAHOO.COM30_Fri, 6 Apr 2001 05:34:01 -0700554_us-ascii The problem with this and most debugger tools that I've seen is that when you're working with the Stored Procedure Builder, it translates the SQL language into C code. The debugger works with the C code and provides breaks into that. Unless you know C (And to be honest, how many of us old mainframers know C), you're basically out of luck in debugging this. Sure you can kinda figure out what's going on, but it still presupposes some knowledge of C. And when Java stored procedures are supported, I suspect that you'll need to know JAVA. [...] 21259 139 35_Re: AW: Migrate to DB2 OS/390 V 7.116_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 6 Apr 2001 08:47:16 -0400447_iso-8859-1 Ditto here. We've been waiting a while for the IBM utils to perform. As soon as we are sure the IBM utils are adequate in V7, we will start getting rid of CA products.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: RICHARD E MOLERA [mailto:RICHARD.E.MOLERA@SLMA.COM] Sent: Thursday, April 05, 2001 9:09 PM To: DB2-L@RYCI.COM Subject: Re: AW: Migrate to DB2 OS/390 V 7.1 [...] 21399 110 39_Re: Problems Installing DB2 Fast Unload15_Lankester, Andy22_Andy_Lankester@BMC.COM30_Fri, 6 Apr 2001 07:52:48 -0500411_iso-8859-1 I suspect that the reason you are seeing French comments is that I believe this product originates from Infotel in France! It is not, like several others in the DB2 tools set, written by IBM.

Andy Lankester

-----Original Message----- From: Burgess Evans [mailto:BMEVANS@AUDITOR.STATE.OH.US] Sent: 06 April 2001 13:29 To: DB2-L@RYCI.COM Subject: Problems Installing DB2 Fast Unload [...] 21510 170 25_Re: INFO on DB2 utilities13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM30_Fri, 6 Apr 2001 18:45:09 +0530452_us-ascii



We are going for a YLC for the comming year . we are running UDB 6.1 on OS/390 . Now in the new version UDB7.1 are the basic utilities that come with the normal UDB6.1 also come . bcos I think the packaging of the products have changed and the utilities are shipped seperately . Can any one give details about the packaging of the db2 components for OS/390 and which has a product ID which has to be seperately ordered !! [...] 21681 142 39_Re: Problems Installing DB2 Fast Unload13_Burgess Evans27_BMEVANS@AUDITOR.STATE.OH.US30_Fri, 6 Apr 2001 09:25:38 -0400388_US-ASCII Thank you for the info on the French. At least I know that is probably not the problem.

Thank you, Burge



>>> Andy_Lankester@BMC.COM 04/06/01 08:52AM >>>

I suspect that the reason you are seeing French comments is that I believe this product originates from Infotel in France! It is not, like several others in the DB2 tools set, written by IBM. [...] 21824 196 25_Re: INFO on DB2 utilities16_Robert H. Tilkes22_tilkesr@NATIONWIDE.COM30_Fri, 6 Apr 2001 09:46:08 -0400428_us-ascii

Vishy,

The only utilities that come Free with the product are the CORE utilities. Currently I am getting a list of what the CORE utilities are. I will forward that list on to you. I will also have a list of products that are bundled in there Utilities Suite. You are able to use the Non-CORE utilities to manage the DB2 catalog, but will be restricted from using them against Application Databases. [...] 22021 63 35_Re: AW: Migrate to DB2 OS/390 V 7.112_Gerald Hodge20_ghodge@ATTGLOBAL.NET30_Fri, 6 Apr 2001 09:06:04 -0500410_us-ascii The Fear, Uncertainty, and Doubt (FUD) Factor in this thread is a concern. In the interest of full disclosure, we provide both consulting support and DB2 product. We put considerable effort into insuring our ability to support DB2 V7 on its GA date. We know we were not alone in this effort. It is our belief that V7 is a strategic positioning of DB2 and not a maintenance or "catch up" release. [...] 22085 20 35_Re: AW: Migrate to DB2 OS/390 V 7.116_RICHARD E MOLERA25_RICHARD.E.MOLERA@SLMA.COM30_Fri, 6 Apr 2001 10:29:24 -0400464_us-ascii Bruce,

I apologize to you and the entire list serve. That was a flip remark made at the end of a long day!

It was unwarranted and should never have been sent.

Rick Molera Foot in Mouth Disease

================================================ 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. 22106 22 19_DDF Inefficiencies?0_20_Jones.H@GRAINGER.COM30_Fri, 6 Apr 2001 10:39:00 -0500628_- Under what circumstances does the use of DDF from one lpar/subsystem to another hamper the performance of a query?

We have COBOL programs on one lpar issuing complex queries and numerous updates to a data warehouse on another lpar using DDF and 3-part names. The performance is unacceptable. Some factors which may come into play are inefficient sql, performance group of the batch job. Dispaching priorities of the requesting DB2 address spaces and the target DB2 address spaces, database design, and possible inefficiencies of using DDF. I'd like to know what impact DDF might have in these these circumstances. [...] 22129 54 21_Re: DB2 Connect Logon17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Fri, 6 Apr 2001 09:07:54 -0700334_us-ascii We are on a non NT windows platform and therefore there was no initial id setup. So are you saying that when you first click on the subsystem and it comes up with the connect box to enter your id and password to pass to the host that the invalid id/password in the box is from attempting to logon to the control center? [...] 22184 48 67_Re: WEBCAST: IBM DATABASE TOOLSET - LATEST ADDITIONS TO THE FAMIL Y10_Shery Hepp17_schepp@SRPNET.COM30_Fri, 6 Apr 2001 09:11:32 -0700384_iso-8859-1 Vishy/Robert- I suggest if you have the time to listen to the webcast below. I found it to be very informative concerning IBM tools/utilities.

Shery Hepp

-----Original Message----- From: Morrill, John [mailto:JohnM@VP.NET] Sent: Thursday, March 29, 2001 12:36 PM To: DB2-L@RYCI.COM Subject: WEBCAST: IBM DATABASE TOOLSET - LATEST ADDITIONS TO THE FAMILY [...] 22233 51 23_Re: DDF Inefficiencies?0_22_Rohn.Solecki@MTS.MB.CA30_Fri, 6 Apr 2001 11:14:20 -0500341_us-ascii I think you'll find that they are executing as dynamic SQL. That means the SQL is 'prepared' each time it is executed, equivalent to a bind for each execution. We have similar DDF connection setup for simple SQL to a few remote tables. For complex SQL we create / Compile / bind a program that is local to the DB, and call it. [...] 22285 68 23_Re: DDF Inefficiencies?0_19_Tim.Lowe@STPAUL.COM30_Fri, 6 Apr 2001 11:22:33 -0500312_us-ascii Hayden, Are you on DB2 version 6? If so, are your plans/package bound with DBPROTOCAL(DRDA)? Are they using static or dynamic SQL? How much data are they retrieving? If you are using not using DB2 version 6, or if you are using DBPROTOCAL(PRIVATE), are their cursors defined with "FOR FETCH ONLY"? [...] 22354 55 23_Re: DDF Inefficiencies?16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 6 Apr 2001 12:25:34 -0400382_iso-8859-1 1) The SQL on the remote side is dynamic with the associated costs and other evils. 2) The SQL and results must go through the network with the associated costs and other evils.

Have you considered making the remote SQL into Stored Procedures? Do you have WLM Goal Mode?

If you do this you can reduce the costs and make the priorities more manageable. [...] 22410 87 25_Re: INFO on DB2 utilities14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 6 Apr 2001 17:32:41 +0100453_iso-8859-1 Vishy/Robert et al

In short, the core (i.e. free) utilities are those that have no competition.

This includes all the DSN1xxx stand-alone utilities, CATMAINT, REPAIR, REPORT, TEMPLATE, LISTDEF, QUIESCE and a couple of others.

If it has competition, it is NO LONGER FREE from IBM either.

btw, even the NON core utilities will work on your catalog, directory and sample databases even if you DON'T pay for them [...] 22498 123 23_Re: DDF Inefficiencies?14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Fri, 6 Apr 2001 09:55:18 -0700463_iso-8859-1 Hayden, Going thru the network will always be your slowest path. Implement stored procedures to reduce network traffic. But, to make sure it is not BAD SQL, capture it or recreate it on the target system, do an explain, or if it is a select, run it. If it runs fast, the SQL is not the probem. It if runs just as slow, then you need to tweak the SQL. Always try and eliminate the SQL as the problem first, this will save time and effort. HTH Phil [...] 22622 16 19_DDF Inefficiencies?12_Phil Gunning24_philip.gunning@QUEST.COM30_Fri, 6 Apr 2001 11:46:13 -0500454_- Hayden, Going thru the network will always be your slowest path. Implement stored procedures to reduce network traffic. But, to make sure it is not BAD SQL, capture it or recreate it on the target system, do an explain, or if it is a select, run it. If it runs fast, the SQL is not the probem. It if runs just as slow, then you need to tweak the SQL. Always try and eliminate the SQL as the problem first, this will save time and effort. HTH Phil [...] 22639 24 23_Re: DDF Inefficiencies?0_26_truman.g.brown@VERIZON.COM30_Fri, 6 Apr 2001 13:09:02 -0400382_us-ascii We have some large scale, high activity DDF applications from web servers and other sources and have no problems with DDF itself. Be sure your DB2/DDF parms are correct, especially the pacing value (we use 8).

Our DDF queries which have poor performance are usually SQL related, or take longer due to sheer volume of I/O necessary for DB2 to satisfy the query. [...] 22664 21 12_Index Access0_14_mtdage@ATT.NET30_Fri, 6 Apr 2001 18:00:49 +0000392_- I apologize for not giving you the complete information the first time, I just passed on the information provided to me. I'm glad Shauna(one of our DBAs) provided you with the rest of the information. This has been going on for weeks and since nobody posted this question on the list I decided to do so to get some ideas and help and to get things going in helping us find a solution. [...] 22686 34 35_Re: AW: Migrate to DB2 OS/390 V 7.117_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Fri, 6 Apr 2001 15:23:06 -0300268_us-ascii I've tested BMC utilities against IBM version 5 ones, and found, after a lot of tunning of this-and-that and XBM and all complicated stuff (to run a simple reorg job) that it took longer to run than IBM version 5 utilities....



Walter Trovijo Jr 22721 64 82_Re: -440 when invoking DB2 stored procedure thru Powerbuilder and DB2 Connect v7.122_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM30_Fri, 6 Apr 2001 11:39:07 -0700394_iso-8859-1 Hello Kals Thanks for responding . I tried the CURRENT FUNCTIONPATH parameter, but I am having problems. I understand that parameter is for Function seraching and not for STored procedures. Irrespective of the CURRENTSQLID, it still uses the primary auth ID to check the SYSPARMS table!. I treid creating a schema with that auth ID (and granted execute) but still fails Thanks! [...] 22786 20 23_Re: A puzzling question12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 Apr 2001 14:17:40 -0500350_- Temporal database is a difficult, but interesting topic. DB2 has a very basic understanding of time today, and esswentially no understanding of data which varies over time. Managing data with effectivity dates or historical data puts a large burden upon the application and the developers. An SQL standard for temporal data has been proposed. [...] 22807 15 43_Re: AW: How to skip first n matched rows ??12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 Apr 2001 14:21:33 -0500306_- What we call this function is scrollable cursors, and it's only in Version 7 of DB2 for OS/390. The V7 Presentation Guide red book, SG24-6121 has lots on this topic. We taught a full session on scrollable cursors at the DB2 Technical Conferences in the US last year and Hamburg this year, IDUG, ... [...] 22823 36 36_RC/Comapare, RC/Migration re-visited13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Fri, 6 Apr 2001 15:54:35 -0400 22860 112 43_Re: DB2 PERFORMANCE ON OS/390 vs DB2 ON AIX11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM30_Fri, 6 Apr 2001 09:38:08 -0400300_us-ascii After a careful analysis of the detailed performance data that you provided for DB2 V7/AIX, I've concluded that your DB2 AIX is sub-optimally tuned; that your DB2 AIX query should only be taking 29 seconds, which should make your DB2 AIX about 8 times faster than DB2 on your 9672-R14. [...] 22973 36 43_Re: AW: How to skip first n matched rows ??11_Robert Jans26_robert_jans@ALBERTSONS.COM30_Fri, 6 Apr 2001 13:03:37 -0700500_iso-8859-1 I posted the IDUG presentation to the DB2-L Document list a while back with permission of Kalpana Shyam and IBM. Look around end of October/Early November 2000. At that time, there were a large number of interested people. Sounds like there's still a lot of interest in it.

Robert Jans

-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Friday, April 06, 2001 1:22 PM To: DB2-L@RYCI.COM Subject: Re: AW: How to skip first n matched rows ?? [...] 23010 84 23_Re: Alteration criteria64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM30_Fri, 6 Apr 2001 15:43:40 -0500609_ISO-8859-1 The criteria we use for adjusting priqty (we typically leave secqty alone) is typically not percent active (PCTPAGES), but how desired free space and desired percent free relate to high allocated RBA, high used RBA and number of extents that currently exist.

Percent active, or the number of pages with data rows as that relates to total pages, may not be the best criteria to use if you are wanting to monitor your tablespaces to decide when and if to adjust the priqty and/or secqty. It tells you nothing about how well your initial desired settings (like pctfree, freespace, priqty) [...] 23095 17 25_Re: INFO on DB2 utilities12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 Apr 2001 15:44:35 -0500376_- The What's New? book for Version 7 has a section on the new packaging for DB2 utilities that sounds like it fits what you want. It's on pages 24 and 25 of the print, or page 32 as Acrobat counts. You can download the book in Adobe Acrobat format, .pdf, from ibm.com/software/db2os390 Click on Library, then Version 7, then the HTML or PDF format for the book you want. [...] 23113 25 44_Re: Changing Passwords via DB2 Connect v5.2.10_Lisa Bates19_lbates@JHANCOCK.COM30_Fri, 6 Apr 2001 15:26:16 -0500468_- We tried turning on the extended security as well (DB2 Connect 5.2/DB2 OS390 v5). While the change password function appeared to work, when a userid had a password that was expired, it caused the client machine to loop endlessly trying/failing to connect. The volume of messages generated to the system console was rather extreme. There was no indication on the client workstation what was going on and causing lots of system overhead messages to the logs etc. [...] 23139 23 11_Re: SEGSIZE12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 Apr 2001 15:59:40 -0500470_- It's Friday, and I don't get on a plane until tomorrow, so maybe there is room for one more flippant comment.

In V2R1 1988, we had our first chance to deliver a type 2 table space, resolving ten or twenty issues that are still there for simple table spaces. Don Haderle was the first to provide the guidance, "If it's large, it should be partitioned. If not, it should be segmented." Maybe that's not an exact quote, but close enough for twelve years ago. [...] 23163 15 47_Re: Version 7 Vs Version 6 - Scrollable cursors12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 Apr 2001 16:16:16 -0500269_- If you want the 8 page version, then I'd suggest chapter 1 of the What's New? book. Chapter 2 goes into more on the product, about 20 pages. It includes some of the late V6 items, but they have little V6 flags. I still use the What's New? book as my crib sheet. [...] 23179 22 32_Re: convert database to all caps12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 6 Apr 2001 16:40:01 -0500469_- Most of the time, you save little to nothing by storing in ASCII, unless the applications know that numbers should be before letters rather than after the letters. Back in the Installation Guide there is an appendix about Character Conversion, listing the many PC ASCII code pages, which differ from the ASCII Windows code pages. Translation is generally fast on single byte code pages (built-in TR instruction on S/390 and fairly short path on UNIX & Windows). [...] 23202 136 34_Re: Comparing two rowvalues in DB214_Michael Hannan24_mhannan@C031.AONE.NET.AU30_Sat, 7 Apr 2001 22:55:04 +1000462_us-ascii Terry,

Finding gaps is something that DB2 does not do well without procedural code unless theta joins are very small.

Both your solutions have nasty Theta Join although the second one is on a reduced intermediate set. The 2nd solution's subqueries Equal predicates are stage 2 on OS/390. Are they any better on the other platform? Performance would be expected to be rather poor for both and if tables were large could be unworkable. [...] 23339 108 21_Re: Cutover Checklist9_Eric Kwai18_Eric_Kwai@DTCC.COM30_Fri, 6 Apr 2001 21:54:31 -0500606_- Jenny: This is a big list here. Off the top of my head, I can think of the following which are database related:

1) Application Server (From the view of IBM WebSphere on Sun & OS390) NT and all Unix varaint should be the same, V3.5 and beyond.) . Verify that your development DataSource/DataPool is present and referencing the correct database. . Make sure the AppServer is configured with the correct JDBC driver and URL. /* Should be handled by your WebAdm */ . Verify the CLASSPATH and Servlet.properties. . Verify the application.properties file contains the lastest development changes. [...] 23448 64 17_24x7 Requirements11_Paul Traney22_paultraney@HOTMAIL.COM30_Fri, 6 Apr 2001 22:23:20 -0500634_iso-8859-1 Fellow DB2 Users,

I understand this is a technical forum. I'm new to this list. I am looking for solid EVIDENCE from experienced users (NOT VENDORS) concerning a business requirement from my company to pursue/achieve 24x7 very soon.

Recently, I have been bombarded by vendors claiming to have the complete solution for 24x7. Is anyone really running true 24x7 reorgs and copies? My company is in the process of pursuing a worldwide implementation for several of our existing DB2 applications. In fact, they want us to fully investigate the 24x7 arena in order to present our recommendation end of April, [...] 23513 29 21_Re: 24x7 Requirements8_Bob Abad16_BobRabad@AOL.COM28_Sat, 7 Apr 2001 00:39:22 EDT78_US-ASCII Paul,

Call your BMC rep. They have a true DB2 online reorg.... 23543 178 34_Re: Comparing two rowvalues in DB213_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 6 Apr 2001 23:51:10 -0500467_us-ascii Michael,

True that the performance of these two queries CAN be poor, but this was explained in the solution below.......Just because they can be poor, does not mean that they will be poor. Whether the performance is acceptable is entirely dependent on the number of rows and also the frequency of execution (this may be a one-off requirement!!!)........All we can do is offer the solutions and warn the requester of the performance implications. [...] 23722 149 34_Re: Comparing two rowvalues in DB213_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Fri, 6 Apr 2001 23:59:27 -0500416_us-ascii I forgot to mention......on non-OS/390, these correlated subqueries are still stage 2 (residual predicates). The OS/390 stage 2 predicate AND A.EMPNO < B.EMPNO - 1 is index sargable (index screening).

Logically, the SQL suffers the same performance problems as on OS/390............but non-OS/390 optimizer appears more likely to choose a merge scan join, rather than nested loop on OS/390..... [...] 23872 145 39_Re: How to skip first n matched rows ??13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Sat, 7 Apr 2001 00:11:44 -0500392_us-ascii As previously suggested, the best performing solution on V6.1 is to simply fetch every row (including the first 25). So that's it.....there is no better performing solution.

Any OS/390 DB2 SQL solution which attempts to assign a sequential number to your specific result (which does not have a fixed ordering already implied by the data) will result in poor performance. [...] 24018 76 18_Re: User Data Type13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Sat, 7 Apr 2001 00:42:09 -0500346_us-ascii Anil,

A few random thoughts:

As you say there is no source datatype of year, and therefore creating your own is a perfectly valid usage of UDTs if your requirement is to store the year only. The definition of the UDT will disallow comparison to any other numeric datatype which is unrelated (must be explicitly cast). [...] 24095 145 47_Re: Order by using the Order by index or sort ?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Sat, 7 Apr 2001 01:01:54 -0500427_iso-8859-1 Boubakar,

Let me attempt to answer some of the many questions you pose:

>From the SQL example you provide, the comparison: "zipcode like '750%' " is a range predicate. Thus DB2 must read all rows in the range '75000' to '75099' and then sort on the wkfs column to determine the first 10 rows for that column. The lowest wkfs value could belong to zip '75084', 2nd to '75012', 3rd to '75050' etc.. [...] 24241 22 19_Out Of Office Reply12_Scott McFall22_smcfall@PROTECHPTS.COM30_Sat, 7 Apr 2001 02:27:12 -0400287_- I will be out of the office today, Friday, April 6th, all day having no access to email. If you need to reach me immediately you can call me on my cell phone at 412-445-8070. Otherwise, I will respond to your email on Monday, April 9th.

Thank You and have a great weekend. [...] 24264 46 11_DB2 V5 JDBC15_MAHSOUN, KHALID29_kmahsoun@SAUDIAIRLINES.COM.SA30_Sat, 7 Apr 2001 09:28:56 +030041_- HI ALL Dose DB2 V5 support JDBC

24311 15 44_Pamela Harris/IT/SEARS is out of the office.0_17_pharri1@SEARS.COM30_Sat, 7 Apr 2001 02:18:39 -0500447_us-ascii I will be out of the office from 04/06/2001 until 04/09/2001.

If you need assistance with DB2, use PIN # 7270526, for informix/sqlserver use PIN # 7270525, network issues use PIN # 7270063.

================================================ 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. 24327 61 23_Re: DDF Inefficiencies?18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Sat, 7 Apr 2001 21:20:17 +0930599_us-ascii Howzit Hayden?

There's an interesting article on DDF in the last DB2 Magazine, DDF vs. CICS, by Robert Catterall. http://www.db2mag.com/db_area/archives/2000/q4/db2dba.shtml

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia









Jones.H@GRAINGER.COM@RYCI.COM> on 07/04/2001 01:09:00

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: DDF Inefficiencies? [...] 24389 60 35_Re: AW: Migrate to DB2 OS/390 V 7.118_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Sat, 7 Apr 2001 21:30:28 +0930385_us-ascii Howzit Rick?

Apology accepted.

Personally, I'd be interested to see a performance bencmark of DB2 Utilities vs. the rest (BMC, Platinum, etc.), before making a decision on which way to jump. Do you have access to any such info you'd care to share?

Cheers Bruce Williamson

DB2 Database Administrator DCIS NT Government Darwin NT 0800 Australia [...] 24450 21 15_Re: DB2 V5 JDBC9_Eric Kwai18_Eric_Kwai@DTCC.COM30_Sat, 7 Apr 2001 15:27:33 -0500500_- Yes, please see the following website http://www-4.ibm.com/software/data/db2/os390/jdbc.html Hope this helps. Eric

On Sat, 7 Apr 2001 09:28:56 +0300, MAHSOUN, KHALID wrote:

>HI ALL >Dose DB2 V5 support JDBC > >

================================================ 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.