1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2000, week 4 2 29 39_Merging SQLs - performance implications8_madhavan16_madhavan@INF.COM31_Sat, 22 Jan 2000 15:11:08 +0530349_- Hi,

We have many cases where the user may or may not enter key value. We have used a single sql using the following logic. Will there be performance degradation because of this?

Eg: Emp Table : EmpNo (key), name, address, and other columns

Select EmpDetails from Emp where (empno = :ws-emp-no) or (empno < ws-emp-status) [...] 32 36 69_Performance impilcation between join with distinct and exists cla use8_madhavan16_madhavan@INF.COM31_Sat, 22 Jan 2000 15:20:28 +0530281_- Hi,

We have many sqls where we have 1 to many relationship between 2 tables and we just want to check the existence of values in the second table for selecting the rows. Please let us know if the exist clause or join with distinct - which is performance wise better? [...] 69 30 48_Combining 2 sqls - will performance be affected?8_madhavan16_madhavan@INF.COM31_Sat, 22 Jan 2000 15:27:22 +0530509_- > Hi, > > We have many cases where the user may or may not enter key value. We have > used a single sql using the following logic. Will there be performance > degradation because of this? > > Eg: > Dep Table : DepNo (key), name, and other columns > > Select DepDetails from Emp > where > (depno = :ws-dep-no) or (depno < ws-dep-status) > > When the user enters depno, ws-dep-status will have low values. When the > does not enter any depno, ws-dep-status will be high values. > > regards > Madhavan. > [...] 100 47 19_DSN1COPY problem !!13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Sat, 22 Jan 2000 15:28:11 +0530344_us-ascii Hi !!

I am facing a problem similar to the one posted a few days ago . I am trying out 2 things . first I have a table in subsystem SUB1 and now I create the same table in Subsystem SUB2 . I take an image copy of the tablespace TS1 from SUB1 . Then I use DSN1COPY and copy this imagecopy dataset to TS2 in subsystem SUB2 . [...] 148 49 14_DB2 and Delphi9_Fatih Nar34_fatihnar@ANATOLIA.MSIS.METU.EDU.TR31_Sat, 22 Jan 2000 11:59:20 +0200489_US-ASCII Hi

I am using Delphi 4 (later I will use Delphi 5) and DB2 UDB VB.52 (later Iwill use DB2 UDB V6.x)

I have got some questions to ask:

1. I think this question is the one of the most asked one by beginner of DB2. Coming : AUTOINCREMENT.. how can I define an autoincrement field. I found the trigger foing that. But why DB2 does not support such a thing. I read that DB2 V6.1 does it, but it supports it in UNIX version but not NT version. Is it the fact? [...] 198 65 73_Re: Performance impilcation between join with distinct and exists cla use0_24_ssethi@LOT.TATASTEEL.COM29_Sat, 22 Jan 2000 12:17:32 GMT730_us-ascii Madhavan, I think the 1st sql(with EXISTS) mentioned by u is always very efficient .

Thanks

Sanjeev







madhavan @RYCI.COM> on 01/22/2000 03:20:28 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Performance impilcation between join with distinct and exists cla use



Hi,

We have many sqls where we have 1 to many relationship between 2 tables and we just want to check the existence of values in the second table for selecting the rows. Please let us know if the exist clause or join with distinct - [...] 264 71 43_Re: Merging SQLs - performance implications0_24_ssethi@LOT.TATASTEEL.COM29_Sat, 22 Jan 2000 12:23:46 GMT330_us-ascii Madhavan, u can use between caluse for this , i mean if user enters something then both the variable ws-emp-no and ws-emp-status will be having the same value i.e entered by user otherwise ws-emp-no will be having the lowest possible value for the column and ws-emp-status will be having the highest possible value. [...] 336 80 23_Re: DSN1COPY problem !!17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM31_Sat, 22 Jan 2000 07:46:05 -0500348_- Check to make sure that tablespace TS1 and TS2 are defined the same (e.g. same SEGSIZE and same number of tables and same table definitions, etc.) Also there's no need to give transalations for index PSIDs if your using DSN1COPY to copy tablespaces (I usually just REBUILD the indexes on the target system after the DSN1COPY has completed.) [...] 417 15 52_Re: Combining 2 sqls - will performance be affected?12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 22 Jan 2000 13:32:24 -0600520_- Performance will be affected. Break this into multiple statements. We get one chance to optimize, so performance may be ok if the data entered is for the access path we choose. The OR gives you the opportunity to have the access path be a table space scan.

Roger Miller

================================================ 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. 433 138 23_Re: DSN1COPY problem !!41_=?iso-8859-1?Q?Cl=E1udio_Meluzzi_Mendes?=18_P000002@BEG.COM.BR31_Sat, 22 Jan 2000 17:40:03 -0200510_iso-8859-1 When running DSN1COPY to move data between subsystems one important thing is to create the target tablespace and tables with exactly the same size and structure as in the source subsystem. After that, the DBID, PSID and OBID are informed as the DB2 Utility manual states. We decided, for data integrity and subsystem stability reasons, never run DSN1COPY in our production subsystem. Former bad DSN1COPY executions had caused failures in our target subsystem. A sample of our DSN1COPY follows: [...] 572 145 23_Re: DSN1COPY problem !!41_=?iso-8859-1?Q?Cl=E1udio_Meluzzi_Mendes?=18_P000002@BEG.COM.BR31_Sat, 22 Jan 2000 17:40:03 -0200510_iso-8859-1 When running DSN1COPY to move data between subsystems one important thing is to create the target tablespace and tables with exactly the same size and structure as in the source subsystem. After that, the DBID, PSID and OBID are informed as the DB2 Utility manual states. We decided, for data integrity and subsystem stability reasons, never run DSN1COPY in our production subsystem. Former bad DSN1COPY executions had caused failures in our target subsystem. A sample of our DSN1COPY follows: [...] 718 20 25_Re: FW: Space calculation12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 22 Jan 2000 13:39:16 -0600454_- I don't have time to check on this one and don't know it. Mybe it's useful anyway.

The first time a page is written is the preformatting, where the work is done 2 tracks or 2 cylinders at a time. The other writes are rewrites and won't change HURBA. If you use the preformat option on LOAD or REORG, the HARBA = HURBA. I think that we update the HURBA after preformatting, so the space is reasonably accurate, within the granularity noted. [...] 739 51 19_Re: DB2 V6 on OS39012_Roger Miller19_millerrl@US.IBM.COM31_Sat, 22 Jan 2000 14:24:44 -0600484_- On Fri, 21 Jan 2000 15:07:38 -0700, Duane Lee - ATCX wrote:

>I'm close to ordering OS390 DB2 v6 but heard that a new release, v 6.2, is >due out in March. If that is the case I want to wait for the new release. >Has anyone else heard about this or are we just talking about a bunch of >PTFs that would be applied and a new designation of 6.2?? Any info will be >appreciated. > >Thanks, >Duane > >================================================ [...] 791 74 23_Re: DSN1COPY problem !!0_19_csutfin@AMSOUTH.COM31_Sat, 22 Jan 2000 16:20:41 -0600733_- Vishy:

Did you recover the indexes for the table(s) in TS2?????

What does a -dis database(db2) spacenam(ts2) showreveil

Carol Sutfin AmSouth Bank NA csutfin@amsouth.com





Viswanathan N on 01/22/2000 03:58:11 AM



Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Carol Sutfin/BIRMINGHAM/AL/AMSOUTH BANK) Subject: DSN1COPY problem !!







Hi !!

I am facing a problem similar to the one posted a few days ago . I am trying out 2 things . first I have a table in subsystem SUB1 and now I create the same table in Subsystem SUB2 . I take an image copy of [...] 866 59 43_Re: Merging SQLs - performance implications16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Sat, 22 Jan 2000 17:30:57 -0500646_iso-8859-1 Check the input and use 1 of the 2 specific queries, written to get the best access path in either case. It is never a good idea to be generic, especially with SQL, if performance is a concern.

Richard Yevich +=====+======+ Information Technology Consulting, Data Modeling, Advanced Education RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930 Web: Email: info@ryci.com Offices: USA and Europe DB2® Family and Oracle® Specialists - Parallel Technologies VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®) Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999 [...] 926 107 25_Re: Multiple buffer pools16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Sat, 22 Jan 2000 17:31:05 -0500626_iso-8859-1 Extracted from "DB2 Answers", published by Osborne McGraw-Hill, 1999.

2. How should I use multiple bufferpools?

For the OS/390 platform, it is highly recommended that you have several bufferpools with objects separated according to their type of processing and residency characteristics. This allows for more precise tuning and sizing of each bufferpool. For example, it is ideal to keep objects that have random access in bufferpools separate from objects that have a lot of sequential access. Some general rules of thumb for bufferpool separation on OS/390 are listed in the following example: [...] 1034 66 43_Re: Merging SQLs - performance implications16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Sat, 22 Jan 2000 17:30:57 -0500646_iso-8859-1 Check the input and use 1 of the 2 specific queries, written to get the best access path in either case. It is never a good idea to be generic, especially with SQL, if performance is a concern.

Richard Yevich +=====+======+ Information Technology Consulting, Data Modeling, Advanced Education RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930 Web: Email: info@ryci.com Offices: USA and Europe DB2® Family and Oracle® Specialists - Parallel Technologies VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®) Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999 [...] 1101 30 44_fyi: how to get current qualifier - answered14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Sun, 23 Jan 2000 10:44:13 +1000403_us-ascii For those, like me, who have wondered how to get the current table qualifier in a program: drop a non-existant table. The fully qualified name of the table will be returned as the first token in SQLERRM.

Using sql escape quotes you can create a name that is unlikely to exist eg DROP TABLE "!!!!", 'cause this is one time SQLCODE -204 is normal, and SQLCODE zero means big trouble. [...] 1132 82 23_Re: DSN1COPY problem !!14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Sun, 23 Jan 2000 11:25:24 +1000395_us-ascii In addition to the suggestions of others:

- what is the reason code and unavailable resource? - did you stop TS2 before, and start it after the DSN1COPY? - when you did the copy directly from TS1, did you also stop/start it? Otherwise you'll get what's in the cluster, but not what's still in the bufferpool. (You could also stop/start access(ro)/dsn1copy/start access(rw)) [...] 1215 208 23_Re: DSN1COPY problem !!10_Mark Doyle23_markdoyle@EARTHLINK.NET31_Sat, 22 Jan 2000 21:23:16 -0600522_us-ascii One note about "defined the same". In DB2V1.3 - DB2V4, (and I assume afterward, although I haven't checked) I have noticed that, for DSN1COPY purposes, a table defined with (for example) 6 columns and then ALTERed to add 3 more columns is not the same as the table defined with those same 9 columns initially. In order to work, the two tables must be have used the exactly the same CREATE & ALTER statements in the same order. I'm not sure exactly why it works that way, but I've been burned by this before. [...] 1424 211 23_Re: DSN1COPY problem !!10_Mark Doyle23_markdoyle@EARTHLINK.NET31_Sat, 22 Jan 2000 21:23:16 -0600522_us-ascii One note about "defined the same". In DB2V1.3 - DB2V4, (and I assume afterward, although I haven't checked) I have noticed that, for DSN1COPY purposes, a table defined with (for example) 6 columns and then ALTERed to add 3 more columns is not the same as the table defined with those same 9 columns initially. In order to work, the two tables must be have used the exactly the same CREATE & ALTER statements in the same order. I'm not sure exactly why it works that way, but I've been burned by this before. [...] 1636 44 16_Re: Column names10_Mark Doyle23_markdoyle@EARTHLINK.NET31_Sat, 22 Jan 2000 21:55:07 -0600528_us-ascii From a purely theoretical standpoint, it stands to reason that there is some business/logical difference between the two columns (at very least, as defined, they have different domains). Because of the business/logical difference, they should be named differently, probably with an adjective or qualifier on one or the other column name. Your business analyst or logical data modeler should have be rigorous with the business users to uncover the difference in meaning and then finding or creating that qualifier. [...] 1681 38 33_Batch programs scheduling- OS/39011_Linda Smith22_linda_home@HOTMAIL.COM31_Sat, 22 Jan 2000 23:29:10 -0600454_- Linda Smith, Sanjose - CA

For DB2 List,

In my production environment I have 60 jobs(programs) everyday night runs in the batch cycle. As part of new development, about 40 programs are recently developed and I need to schedule them in the production batch cycle.

I know very little about batch schduling. like crud matrix analysis etc.. I would appreciate if any one explain me the step by step procedure to procede with this. [...] 1720 43 23_Re: DSN1COPY problem !!11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Sun, 23 Jan 2000 06:39:55 -0500494_ISO-8859-1 Mark

The reason for your problem is that using ALTER TABLE to add a column makes the rows variable length and a flag is set in the DBD to signal this. CREATE TABLE, if there are no VARCHAR columns sets the flag to indicate fixed length rows. If you copy variable length rows into a tablespace that has a DBD entry for fixed length rows things go haywire. To avoid the problem, REORG after ALTER. REORG will reset the DBD to fixed length rows including the new column(s). [...] 1764 53 26_Links about DB2 for OS/3906_DB2usa18_db2usa@HOTMAIL.COM31_Sun, 23 Jan 2000 14:56:41 -0600479_- Here is a non-profit website about DB2 for OS/390 (IBM mainframes):

- English : http://www.multimania.com/db2usa/eindex.htm - French : http://www.multimania.com/db2usa/index.htm

Last updated on Wednesday, January 19th 2000

- Links about DB2 for OS/390: http://www.multimania.com/db2usa/eliendb2.htm IBM added an early-release version of REXX V6 (that lets your REXX programs access DB2 data).

Craig Mullins updated his published articles list. [...] 1818 71 23_Re: DSN1COPY problem !!14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Mon, 24 Jan 2000 10:03:36 +1000326_us-ascii Mike + Mark,

A REORG or LOAD REPLACE (while necessary) on its own will not re-set the variable length flag in the DBD. You also need to MODIFY RECOVERY to remove image copies from before the reorg/load. Until this is done, DB2 keeps the variable length flag because you might recover to before the reorg. [...] 1890 100 13_Re: Thank you11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Mon, 24 Jan 2000 08:39:17 +0800426_us-ascii Hi ,

I had tested the two following SQL statements and discovered that :

1. SELECT * FROM DB21SQL.TTRNDIAG WHERE C_DISCHARG_DATE BETWEEN (CURRENT DATE - 7 DAYS) AND CURRENT DATE ORDER BY C_DISCHARG_DATE;

(SQL1 uses tablespace scan)



2. SELECT * FROM DB21SQL.TTRNDIAG WHERE C_DISCHARG_DATE >CURRENT DATE - 8 DAYS AND C_DISCHARG_DATE
(SQL2 uses index scan.) [...] 1991 31 25_Problem connecting to UDB14_Romeo B Titong26_rbtitong@ACISYSTEMS.COM.PH31_Mon, 24 Jan 2000 09:33:28 +0800400_us-ascii I was connected to a database using UDB version 5.2. Everytime I connect to this database the swap space requirement shoot rapidly up to 100% in a span of 1 minute! causing the system to hung. Are there any known bug to UDB version 5.2?

We used to access the database without a problem using version 2.1. The UDB instance is running on our production box with AIX version 4.2.1. [...] 2023 59 29_Re: Problem connecting to UDB11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Mon, 24 Jan 2000 10:28:42 +0800356_us-ascii Hi, I too had encountered similar problem. We are running UDB V5.2 on NT server. For no reason, everytime we connect to the database , the memory allocation shoot up to near 100%. We do not encounter such problem with our other databases under the same db2 instances. We dropped, re-create back the database and the problem just disappeared. [...] 2083 108 29_Re: Problem connecting to UDB14_Romeo B Titong26_rbtitong@ACISYSTEMS.COM.PH31_Mon, 24 Jan 2000 10:52:14 +0800513_us-ascii i tried uninstalling UDB and reinstalling it but the problem still exist.... should i backup the database, drop it, then restore it?

first, i thought it has something to do with the migration...but then, new databases have the same problems....

hope someone could help

















Roland Chua on 01/24/2000 10:28:42 AM

Please respond to DB2 Data Base Discussion List [...] 2192 141 23_Re: DSN1COPY problem !!13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Mon, 24 Jan 2000 08:29:31 +0530458_us-ascii hi !!

Thanks for all the suggestions but I have already done all that .

1. I stopped the database and then started,

2. I tried to recover the index but then also it gave resource unavailable. which gave an error code of downleveling .

3. Until and unless I remove / accept the dodnlevelling I am not able to do anything on that object . I tried repair levelID . that was successful but still it gave the same error. [...] 2334 141 29_Re: Problem connecting to UDB11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Mon, 24 Jan 2000 11:35:44 +0800547_us-ascii I backup the database and also used db2look to generate the ddl. Then I exported data from all the tables. (I am lucky that it is my development database and there is only a few tables involved.) I did not reinstall the UDB. I just dropped the database and create new database using the generated ddl. After I had successfully created the database, I used import to put back all the data. My only problem is to identify all the packages and bind them back to the database using the same bind parameters. I did not used the backup to [...] 2476 80 45_DSN1COPY Problem !! Update on error message!!13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Mon, 24 Jan 2000 13:37:54 +0530703_us-ascii hi !! All this is after the repair levelID execution . !! and stop start of database. Some more info on the error message



---------+---------+---------+---------+---------+---------+---------+----- ----+ SET CURRENT SQLID='SYSADM'; 00001007 ---------+---------+---------+---------+---------+---------+---------+----- ----+ DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

---------+---------+---------+---------+---------+---------+---------+----- ----+ INSERT INTO OOSY.SYGCT2 VALUES('OOSY','OS'); 00001199 ---------+---------+---------+---------+---------+---------+---------+----- ----+ DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN [...] 2557 68 43_Re: how to get current qualifier - answered27_Thakral, Deepak Kumar (CTS)24_TDeepak@CHN.CTS-CORP.COM31_Mon, 24 Jan 2000 03:48:21 -0500494_iso-8859-1 James Campbell,

I don't know if I get the question correct, but if I knew the table in the program is XXXXXXX and I wanted to know its high level qualifier say if the full name was TXXC.XXXXXX and I did'nt know TXXC part, I would go to the loadlib dataset and for that program's member , in the loadlib I would give a find for the timestamp (version) by giving a command " F 1999- " OR "F 2000-" and then on finding the 26 character timestamp , copy it and paste it as : [...] 2626 92 43_Re: how to get current qualifier - answered17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM31_Mon, 24 Jan 2000 06:56:31 -0500632_- I posted the original question, which was how to determine the table qualifier from within a COBOL program while it's executing. James Campbell's solution works great. (I used LOCK TABLE instead of DROP, but the answer is in the same place).

Gary Scarcella Internet address: ScarcellaGV@aetna.com

> ---------- > From: Thakral, Deepak Kumar (CTS)[SMTP:TDeepak@CHN.CTS-CORP.COM] > Reply To: DB2 Data Base Discussion List > Sent: Monday, January 24, 2000 3:48 AM > To: DB2-L@RYCI.COM > Subject: Re: how to get current qualifier - answered > > James Campbell, > > I don't know if I get the question correct, but if [...] 2719 214 21_Re: Image Copy exists0_22_The-Thanh.Luong@AGF.BE31_Mon, 24 Jan 2000 13:03:20 +0100360_- Thank you Tina (and everyone who responded). I also used to MODIFY RECOVERY AGE(n), with n=same number days as the retention. My problem came from the fact that for some unknown reason this job has not been executed in that particular DB2 environment since Nov 99. The situation is clear now. I am also going to apply your suggestion (n=retpd minus 1). [...] 2934 27 43_Re: how to get current qualifier - answered10_Anand Babu19_anandbabu.mp@DB.COM31_Mon, 24 Jan 2000 14:31:12 +0530573_us-ascii Hello

Try selecting from sysibm.systables where name is u'r tablename.

With this you should be able to obtain two qualifiers

1) Database name and tablespace name

2) Creator - Which would be the sqlid or collid used to access/ create the table.

Hope this helps

Regards

Anand

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2962 50 43_Re: Merging SQLs - performance implications14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Tue, 25 Jan 2000 01:04:35 +1000660_us-ascii Madavan,

What you have described is a very good technique to make empno predicate NOT indexable except by Multi-index processing, although remains stage 1.

Anyway performance is bad.

So don't use 1 SQL to do 2 different functions.

From: Michael Hannan

>From: madhavan >Subject: Merging SQLs - performance implications >To: DB2-L@RYCI.COM > >Hi, > >We have many cases where the user may or may not enter key value. We have >used a single sql using the following logic. Will there be performance >degradation because of this? > >Eg: >Emp Table : EmpNo (key), name, address, and other columns > [...] 3013 130 67_Re: Hoover presentations, 1) Buffer pool & 2) batch design for D B219_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Mon, 24 Jan 2000 08:17:06 -0600601_iso-8859-1 Robert, I did some searching for other articles by Chuck Hoover on the Website, couldn't find them. Could you please identify how to find them as well as the "Buffering" and "Batch design" articles?

Thanks, 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 [...] 3144 121 13_Re: Thank you14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Tue, 25 Jan 2000 01:18:29 +1000669_us-ascii However they don't do the same thing!

SQL 1 includes rows where C_DISCHARG_DATE is Current Date incorrectly.

The index version is probably not efficient because doesn't filter out enough rows. How many Getpages does each do? How many pages in table?

What version of DB2 are you on?

From: Michael Hannan

>From: Roland Chua >Subject: Re: Thank you >To: DB2-L@RYCI.COM > >Hi , > >I had tested the two following SQL statements and discovered that : > >1. SELECT * FROM DB21SQL.TTRNDIAG > WHERE C_DISCHARG_DATE > BETWEEN (CURRENT DATE - 7 DAYS) AND CURRENT DATE > ORDER BY C_DISCHARG_DATE; [...] 3266 18 17_identifier column11_Kenneth Lam14_klam@BTMNA.COM31_Mon, 24 Jan 2000 10:10:45 -0500285_us-ascii Can some one tell me if there are any identifier column in DB2? Our environment is DB2 V6 . What we tried to accomplish is to allow system assign a number and it will generated automatic. So that it can generated a unique key. Can someone show me if DB2 able to do that. [...] 3285 41 26_MERGE COPY UTILITY problem9_Raj Ghose24_Raj_Ghose@CANADALIFE.COM31_Mon, 24 Jan 2000 10:24:18 -0500680_us-ascii ---------------------- Forwarded by Raj Ghose/Individual/CANADALIFE/CA on 01/24/2000 10:34 AM ---------------------------



Raj Ghose 01/24/2000 09:10 AM

To: LISTSERV@RYCI.COM cc:

Subject: MERGE COPY UTILITY

Hi. Everyone, I would like to ask if anyone has as idea to force the merger copy utility to make a full IMAGE COPY when a tablespace is not updated/changed, since these are reference (static) tables. The scenario is that we make a FULL IMAGE COPY on Fridays only to save time and during the week we request INCREMENTAL's followed by a MERGE COPY. The MERGE COPY utility is smart enough to recognize that if a tablespace has [...] 3327 63 21_Re: identifier column19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Mon, 24 Jan 2000 09:39:22 -0600647_iso-8859-1 Ken, Provided earlier:

"Start at the DB2 home page. www.ibm.com/software/db2os390

Click on Support on the left.

Click on S/390 Tech Support

Enter the APAR number in the search and click Go.

Then click on the abstract below: PQ30652: ADD NEW FUNCTION - IDENTITY COLUMNS

Roger Miller"

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 [...] 3391 19 21_Re: identifier column11_Kenneth Lam14_klam@BTMNA.COM31_Mon, 24 Jan 2000 11:17:40 -0500303_us-ascii Rick, I did tried to create a table with column rowid generated always. However, the length of the column is very long and when I tried to populate some data it generates very long hex number. It has length 17. Can you give me a sample of DDL to create table using rowid generated always. [...] 3411 56 37_Re: Batch programs scheduling- OS/3900_22_Rohn.Solecki@MTS.MB.CA31_Mon, 24 Jan 2000 10:20:54 -0600356_us-ascii Under OS/390 we use a product called "ESP - Execution Scheduling Processor" by Cybermation Inc.. It allows us to setup "networks" of dependencies, ie "Job 'g' can run only after 'a' 'c' and 'f' have completed with CC<=4". I don't have any contact info for them, but you could try a web search and see if they have any eductational materials. [...] 3468 25 35_Connect to database takes very long14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Mon, 24 Jan 2000 11:53:06 -0500345_iso-8859-1 Hi!

I have this strange problem in one of our AIX boxes running udb 5.2(Fixpack 11). I have 3 local databases under one instance in our development environment. When I try to connect to one of the local databases it takes about 4 to 5 minutes to connect whereas connection to the other two takes only a couple of seconds. [...] 3494 43 23_Using SnapShot for DB2.16_Claude Richbourg17_CRichbou@PCMH.COM31_Mon, 24 Jan 2000 11:47:40 -0500396_US-ASCII Hey everyone!

I have been tasked with using SnapShot (with our RVA) to shorten the length of time it takes(downtime), for our weekly backup process. Currently, we take down our production DB2P subsystem on the weekend, do full volume dumps and then restart. A 3-4 hour process. We are in need of a method to use so we will only have DB2P down for no more than 20-30 minutes. [...] 3538 109 23_Re: DSN1COPY problem !!14_Wayne Driscoll19_wdriscoll@QUEST.COM31_Mon, 24 Jan 2000 09:10:16 -0800587_iso-8859-1 Mark, This will only be a problem when the table (before the alters) had NO variable length columns (and wasn't in a COMPRESS YES tablespace) AND that the tablespace had NEVER been reorged since the ALTER ADD COLUMN. Once a reorg has been done, the problems will no longer occur. Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own. -----Original Message----- From: Mark Doyle [mailto:markdoyle@EARTHLINK.NET] Sent: Saturday, January 22, 2000 9:23 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DSN1COPY problem !! [...] 3648 191 67_Re: Hoover presentations, 1) Buffer pool & 2) batch design for D B216_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM31_Mon, 24 Jan 2000 12:37:28 -0500449_iso-8859-1



As I mentioned in a post 2 weeks ago, Compuware is in the process of making the presentation into a soft copy, so it can be put on the web. As of right now I am still waiting notification from head office that is has been completed. The presentation is quite large and is being put on the web the same time as another presentation about designing batch jobs to run in a DB2 environment and how to optimize those jobs [...] 3840 62 37_Re: Batch programs scheduling- OS/39012_Isaac Yassin23_yassin@NETVISION.NET.IL31_Mon, 24 Jan 2000 19:41:34 +0200166_x-user-defined Hi,

We use CONTROL-M for job scheduling. It is now part of BMC products (they bought the "The New Dimension" that developed it).



3903 72 27_Re: Using SnapShot for DB2.19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Mon, 24 Jan 2000 11:57:00 -0600425_iso-8859-1 Claude, The marriage of SnapShot, RVA, DFSMS, and DB2 local and/or remote/disaster recovery isn't fully consummated yet as noted on db2-l many times and ways. As an alternative, why not consider just going ahead and getting ready for 24x7x365 by using Image Copy SHRLEVEL CHANGE and planning for full forward recovery both locally and remote? This way you won't have to bring down DB2 except for maintenance. [...] 3976 198 79_Hoover presentations, 1) Buffer pool & 2) batch design for DB2 ( available now)16_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM31_Mon, 24 Jan 2000 13:10:19 -0500547_iso-8859-1 Further to the Chuck Hoover Presentation about Buffer pools. We are still in the process of putting the documentation onto a softcopy format. (The original hardcopy version is near 174 pages).

The URL that has a link to Chuck Hoover presentations is

http://www.compuware.com/products/file/index.htm

You will see the link near the bottom of the page under the title 'Chuck Hoover Presentation Series'. (the direct link may change. So that in this way you will always be able to find the documents in question). [...] 4175 82 27_Re: Using SnapShot for DB2.0_20_John_Lendman@FPL.COM31_Mon, 24 Jan 2000 13:29:58 -0500512_us-ascii We are planning on doing something very similar to what you have addressed. I am currently doing this in test. I do a archive log mode(quiesce), I then put all the tables in a read only mode. I then use SNAPSHOT and snap 20 volumes worth of data, and DB2 catalog, and MVS User cat. This takes about 7-10 minutes. I then start all tables back to read/write. And then I run a second job that takes the data from DASD and writes it to tape. This has been tested in my test system and works very well. [...] 4258 30 51_Listing Databases for an Instance (UDB v6.1 on AIX)0_22_BILL_GALLAGHER@PHL.COM31_Mon, 24 Jan 2000 13:55:25 -0500345_us-ascii Hi.

Is there any way to list all the databases created for an instance? We are running four separate UDB v6.1 instances on our AIX box, and I would like to write a shell script that can cycle through each instance, and then through each database within each instance, to perform some housecleaning tasks on a regular basis. [...] 4289 30 11_DB2 and WLM15_Zander, Barb J.19_bzander@STATE.ND.US31_Mon, 24 Jan 2000 13:34:51 -0600321_iso-8859-1 We are running DB2 V5(soon to be V6) with OS/390 2.7 and would really like to use some of the new features in DB2 that require WLM, but we will not have that ready to go anytime in the near future. Is it possible to somehow bypass this requirement? I hate to hold DB2 back just because OS/390 is not WLM. [...] 4320 29 35_OS/390 Stored Procedure Result Sets13_Lockwood Lyon23_Lockwood_Lyon@AMWAY.COM31_Mon, 24 Jan 2000 14:37:29 -0500550_us-ascii Has anyone been able (successfully!) to implement an application that CALLs a DB2 stored procedure which returns results sets?

We're in the throes of trying this for the first time. The invoking applications are either: (a) Batch; (b) CICS; or (c) intranet client (Interdev/VB). There seem to be some growing pains involving how the various client applications return the result set information. Coding the SP seems to be easy: just Open the Cursors and Return. The tough thing seems to be coding the clients to get the results. [...] 4350 21 25_Problem connecting to UDB13_Dubeau, Denis19_DUBEADE@DSHS.WA.GOV31_Mon, 24 Jan 2000 12:32:35 -0800451_iso-8859-1 If you are running UDB V5.2 for NT (fixpack and 11 and below) verify your DB config to make sure that you DISABLED the use of extended storage via the database configuration i.e. num_estore_seg is 0 on performance tab. When we turned this feature on our production box when to the roof and we were not even able to lauch the control center. We opened a problem report with IBM on the subject and they later told us that V6.1 fixed it. [...] 4372 83 20_Re: ORACLE LIST-SERV16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Mon, 24 Jan 2000 15:37:56 -0500750_us-ascii



http://www.oaug.org/ oracle application users group http://www.odtug.com/ oracle development tools users group http://www.oracle-users.com/ oracle users resources hth Tom







Mary Ann Pickett on 01/21/2000 05:10:12 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: ORACLE LIST-SERV







Hi All,

Does anyone know of a good list-serve for Oracle on NT. We have DB2 on the mainframe and ORACLE on NT servers. I have gotten a lot of good information and help about DB2 from this list and would love to find someting comparable for ORACLE. [...] 4456 34 44_OS/390 Stored Procedure Multiple Result Sets13_Lockwood Lyon23_Lockwood_Lyon@AMWAY.COM31_Mon, 24 Jan 2000 15:40:34 -0500599_us-ascii Sorry if this is a duplicate. I got a message that my original post had been rejected . . .

- - - - -

Has anyone been able (successfully!) to implement an application that CALLs a DB2 stored procedure which returns results sets?

We're in the throes of trying this for the first time. The invoking applications are either: (a) Batch; (b) CICS; or (c) intranet client (Interdev/VB). There seem to be some growing pains involving how the various client applications return the result set information. Coding the SP seems to be easy: just Open the Cursors and Return. [...] 4491 53 39_Re: OS/390 Stored Procedure Result Sets15_Johnson, Steven23_X2SJOHNS@SOUTHERNCO.COM31_Mon, 24 Jan 2000 14:44:27 -0600325_- Lyon, I have worked with Stored Procedures and have successfully passed Results Sets using batch COBOL. I am waiting for assistance here to get it working with VB using DB2 Connect. May be able to help with some gotcha's in the Batch world.

Steven Johnson Applications DBA Southern Company Services Atlanta, GA [...] 4545 30 73_Do you know How to get the db2 name from the ODBC login box into access9712_Adam Flinton16_adam@SOFTFAB.COM31_Mon, 24 Jan 2000 17:43:03 -0000418_iso-8859-1 Dear All,

A quick question:

When you login to Db2 using odbc you get the db2 login box which gives you a choice of db's.

How do I get that into access97? I.e when the user logs on he might have a choice of db2 db'es which he can use. I can do this by holding the dsn in either a text file or an access table but I would like access to absorb the choice fom the DB2 odbc login box. [...] 4576 165 55_Re: Listing Databases for an Instance (UDB v6.1 on AIX)11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU31_Tue, 25 Jan 2000 08:51:24 +1100371_- Hi Bill, Our AIX and NT production boxes are still running V5.2 (and V5.0!), but on both platforms we have to change the default instance name, DB2INSTANCE, (we execute the db2profile for that instance) to point to the database directory and node directory for that instance.

When I list db directory, I only get the databases catalogued for that instance. [...] 4742 168 55_Re: Listing Databases for an Instance (UDB v6.1 on AIX)11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU31_Tue, 25 Jan 2000 08:51:24 +1100371_- Hi Bill, Our AIX and NT production boxes are still running V5.2 (and V5.0!), but on both platforms we have to change the default instance name, DB2INSTANCE, (we execute the db2profile for that instance) to point to the database directory and node directory for that instance.

When I list db directory, I only get the databases catalogued for that instance. [...] 4911 110 49_Re: DSN1COPY Problem !! Update on error message!!12_Bing Alabata25_bing.alabata@MCMASTER.COM31_Mon, 24 Jan 2000 16:07:09 -0600444_us-ascii Hi Vishy,

The -904 messages you received with REASON 00C2010D point to detections of down-level IDs. I don't know why the REPAIR LEVELID jobs you ran did not fix the errors.

You might want to disable DOWN-LEVEL CHECKING by specifying a DLDFREQ=0 parameter for the DSN6SYSP macro in your DSNZPARM job, . . .SDSNSAMP(DSNTIJUZ) This will rid you of the need to run REPAIRs when executing inter-subsystem DSN1COPY jobs. [...] 5022 27 25_DB2 referential integrity8_madhavan16_madhavan@INF.COM31_Mon, 24 Jan 2000 17:33:48 +0530560_- Hi,

One of our customer's shop standard is that DB2 referential integrity will not be used in the normal cases. Referential integrity will be maintained through program logic. I was also told that 90% of the DB2 shops do not use DB2 referential integrity due to performance reasons.

I thought referential integrity through DB2 will be performance wise better than checking it through the program. Also unless we do a fetch for update, there is always a chance of loosing the integrity of data when we do referential check in the programs. [...] 5050 30 26_Response to DSN1COPY Error12_Bing Alabata25_bing.alabata@MCMASTER.COM31_Mon, 24 Jan 2000 16:50:20 -0600444_us-ascii Hi Vishy,

The -904 messages you received with REASON 00C2010D point to detections of down-level IDs. I don't know why the REPAIR LEVELID jobs you ran did not fix the errors.

You might want to disable DOWN-LEVEL CHECKING by specifying a DLDFREQ=0 parameter for the DSN6SYSP macro in your DSNZPARM job, . . .SDSNSAMP(DSNTIJUZ) This will rid you of the need to run REPAIRs when executing inter-subsystem DSN1COPY jobs. [...] 5081 53 29_Re: DB2 referential integrity15_Philip, Sibimon19_PhilipS@SEALAND.COM31_Mon, 24 Jan 2000 18:08:26 -0500391_iso-8859-1 Madhavan,

I think you are misinformed. We used DB2 referential integrity in mainframe always. It is very unreliable to maintain the referential integrity through program. What happens if somebody update data through QMF or SPFUI or through any C/S window?

Thanks Sibimon Philip 972-702-2515 - Office 972-417-3597 - Residence E-mail - Sibimon_philip@sealand.com [...] 5135 70 29_Re: DB2 referential integrity14_Paul A Redhead35_Paul.A.Redhead@TRANSPORT.QLD.GOV.AU31_Tue, 25 Jan 2000 09:32:28 +1000515_us-ascii Madhavan, The Business Rules should be built into the database. A lot of the 'performance problem' comes from people using RI for things it shouldn't be used for. Things like checking for 'Y' or 'N' or day of the week etc...should be table check constraints...performing I/O's for these sorts of things do cause problems. Referential Integrity SHOULD be used to enforce parent/child relationships, and in these cases will be faster (I believe) than the same check being performed by SQL in your code. [...] 5206 97 29_Re: DB2 referential integrity14_O'Conner, John26_john.oconner@COURTS.WA.GOV31_Mon, 24 Jan 2000 15:59:57 -0800568_iso-8859-1 In our shop (DB2 5.1, OS/390 2.7), we update Production data with static SQL only. We do not have RI defined in Production, but we do have it defined in the Development database so that our testing can easily catch programs that are not correctly maintaining RI. If (or maybe I should say "when") we allow updates to Production data from sources that do not get developed (and tested) in our development environment, we probably want to rethink RI. But for now it's much less expensive for us to test user input with an "if" statement than with a call [...] 5304 45 43_Batch programs, dependency analysis- OS/39011_Linda Smith22_linda_home@HOTMAIL.COM31_Mon, 24 Jan 2000 20:32:43 -0600342_- Linda Smith, Sanjose -CA

For DB2 List,

In my previous question (Subject: Batch program scheduling - OS/390) , I think i am not clearly expressed, about the problem.

We use CA-7 for batch scheduling.

Actually, I want to know how dependency analysis can be done to schedule a job in the existing batch cycle. [...] 5350 189 13_Re: Thank you11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Tue, 25 Jan 2000 12:24:19 +0800447_us-ascii HI Michael,

We were running on DB2 V4. Table contains : 127,820 pages No of Rows : 4,704,816 Cluster Index column : C_DISCHARG_DATE The following were the result from the testing :

SQL1

Elapse Time : 1:04 IO Waits : 7 Asynch Read Waits : 491 Records Sorted : 5738 Scan Information Data Type Scan count Rows Processed Rows Inserted Pg Scanned Data 9 4,705,983 0 127,871 Index 12 9 0 18 Workfile 2 5,738 5,735 314 [...] 5540 76 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 09:48:38 +0530387_us-ascii Hi Bing !!

Thanks for the suggestion . I tried that too. Still it gives a problem . I still says the dataasets are down-levelled. I am not understanding why the repair LEVELID does not solve the problem . ? Can u suggest some more solutions !! ??

Vishy









Bing Alabata on 01/25/2000 04:20:20 AM [...] 5617 80 43_Re: Merging SQLs - performance implications13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Mon, 24 Jan 2000 22:36:25 -0600592_iso-8859-1 In general I would agree with that statement (don't use 1 SQL to do 2 different functions), but as I look at the query listed, I wonder if it's really doing two different functions, or the same function 2 different ways depending on what the user enters? I would think that changing the sql to use a "between" should be able to provide matching index-access regardless of what the user enters - "where empno between :ws-emp-no and :ws-emp-no2" (and if the user enteres an "empno", then both host variables would have the same value, otherwise they would be low/high. probably [...] 5698 61 29_Re: DB2 referential integrity16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 24 Jan 2000 23:18:10 -0500632_iso-8859-1 Actually it is quite the reverse. DB2 declarative RI should always be the first choice and there are only a couple of exceptions when you need to revert to application program RI.

Richard Yevich +=====+======+ Information Technology Consulting, Data Modeling, Advanced Education RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930 Web: Email: info@ryci.com Offices: USA and Europe DB2® Family and Oracle® Specialists - Parallel Technologies VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®) Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999 [...] 5760 179 44_Re: DB2 UDB v 5 on NT - Performance Problems0_30_Milorad_Cerovac@TAC.VIC.GOV.AU31_Tue, 25 Jan 2000 16:19:36 +1000579_us-ascii Thanks to Bruce and Kent for their responses. We did "fix" our performance problem on PROD by performing an EXPORT from PROD and importing the data into our DEV database. Just to re-cap, both DEV and PROD were on the same server and their config parameters were the same; the tablespace sizes were also the same. Once the import completed we ran a RUNSTATS and made the DEV environment available for testing - and surprise - our applications performed well. This still however leaves us with no real idea of what caused the initial problem, and why are fix worked. [...] 5940 126 43_Re: Merging SQLs - performance implications16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 24 Jan 2000 23:47:04 -0500443_iso-8859-1 As long as we all agree: - generic anything is counter productive to performance - simplicity for the sake of simplicity if counter productive to performance - performance is for the business bottom line

If it takes complex SQL and complex coding to achieve performance, then goodness!

:-)

Hope this helps, Richard Yevich +======+======+======+ The opinions expressed here just that. ryevich@attglobal.net [...] 6067 141 29_Re: DB2 referential integrity16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Mon, 24 Jan 2000 23:47:06 -0500325_iso-8859-1 Could you please explain and justify the following statement you made since it is a little confusing: > for now it's much less expensive for us to test user input with an "if" > statement than with a call to DB2.

Reason is I would like to answer that but I need to be sure I did not misunderstand you. [...] 6209 96 43_Re: how to get current qualifier - answered14_Campbell James30_James.Campbell@ENERGYIS.COM.AU31_Tue, 25 Jan 2000 16:47:29 +1000663_us-ascii Deepak,

The point is about an executing program finding its current qualifier - and hence the environment (production, test etc).

James

-----Original Message----- From: Thakral, Deepak Kumar (CTS) [mailto:TDeepak@CHN.CTS-CORP.COM] Sent: Monday, January 24, 2000 6:48 PM To: DB2-L@RYCI.COM Subject: Re: how to get current qualifier - answered



James Campbell,

I don't know if I get the question correct, but if I knew the table in the program is XXXXXXX and I wanted to know its high level qualifier say if the full name was TXXC.XXXXXX and I did'nt know TXXC part, I would go to the loadlib dataset and for [...] 6306 631 44_Re: DB2 UDB v 5 on NT - Performance Problems11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU31_Tue, 25 Jan 2000 17:04:58 +1100596_iso-8859-1 Hi Milorad, Gald to hear that it is now working well.

Rebuilds are typically done when the database refused to talk to anyone, rather than slowing to a snails pace. Usually we looked in the db2diag.log, but did not see anything (that we recognised as being a problem!). The question was what is different about this database to the other 150. The answer was usually nothing. So, rather than spend time identifying a particular problem, we reimaged the server (NT and DB2), and reloaded the data from the central server. (The 150 NT boxes contain a branch copy; the central [...] 6938 18 26_Selecting Image Properties14_Romeo B Titong26_rbtitong@ACISYSTEMS.COM.PH31_Tue, 25 Jan 2000 18:48:42 +0800420_us-ascii Hi!

Is it possible to select the properties(e.g. in terms of colors) of an image file in a table? if so, how do we construct it's SELECT STATEMENT?

Thanks!

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 6957 43 27_V5 & P97D CA/Platinum tools14_george mclaren31_george_mclaren@STANDARDLIFE.COM31_Tue, 25 Jan 2000 11:23:09 -0000406_iso-8859-1 Is anyone running DB2 V5 with version P97D of platinum which encompasses the following products :-

DATABASE ANALYSER v2.5.2 BATCH PROCESSOR v5.1.2 RC/QUERY v3.6.2 RC/MIGRATOR v5.1.2 RC/UPDATE v5.1.2 RC/SECURE v2.7.3 RAPID REORG v5.1.2 QUICK COPY v4.1.2 FAST LOAD v4.1.2 FAST UNLOAD v2.1.2 RC/COMPARE v5.1.2 FAST RECOVER v3.1.2 PLAN ANALYSER v2.1.2 INFOREFINER v3.2.2 SQL/EASE v2.1.0 [...] 7001 56 29_Re: DB2 referential integrity19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 06:57:01 -0600568_iso-8859-1 Madhaven, Simply said: "If your RI isn't enforced by the DBM, you D O N O T have RI." Every other method is simply something close to RI that will eventually fail. 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 [...] 7058 77 29_Re: DB2 referential integrity12_craig patton21_prgpatton@HOTMAIL.COM29_Tue, 25 Jan 2000 05:10:42 PST294_- I have to agree with Rick. There was concern about DB2 RI performance in the earlier releases, but since V3, I 've felt that if you want REAL RI, then it MUST be defined in the RDBMS. This saves from 2 different programmers coding slightly different rules (not that this EVER happens!). [...] 7136 104 47_Re: Batch programs, dependency analysis- OS/39015_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM31_Tue, 25 Jan 2000 08:12:28 -0500622_iso-8859-1 Linda,

I once was involved in doing some of what you describe you have to do, but we used a different scheduler. It was a major application that when drawn out in flow diagrams looked alot like spaghetti...

SYSIBM.SYSTABAUTH is the catalog table I used alot to start with, you can use the grantee column for the Package/Plan name -- type = 'p'. Also in this table look at INDEXAUTH, INSERTAUTH, SELECTAUTH and UPDATEAUTH columns. To get PLAN versus PACKAGE names, look at SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE, SYSIBM.SYSPACKLIST. You may want to do some joins to get reports produced down to [...] 7241 82 47_Re: Batch programs, dependency analysis- OS/39019_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 07:16:45 -0600562_iso-8859-1 Linda, First, get with your CA7 guru, they can show you how to follow the scheduling of the jobs and list any dependencies online via the CA7 interface. You can also print the schedule/dependencies. Second, get with the group(s) responsible for the jobs and ask them how they should be scheduled. They are the ones that should have done the dependency checking, etc. in the first place. Ask them which jobs can run with each other, those that can't, or any jobs that must be run in a specific order. Third, try to run as many jobs in parallel as [...] 7324 105 30_Re: Response to DSN1COPY Error19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 07:21:12 -0600608_iso-8859-1 Vishy, I can't help but think that you've somehow missed running REPAIR LEVELID on the ones you're having trouble with. Try rerunning DSN1COPY jobs with RESET. 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 [...] 7430 22 31_How to connect to DB2 from java20_Baskaran Subramaniam27_subramaniam.baskaran@DB.COM31_Tue, 25 Jan 2000 18:53:16 +0530330_us-ascii Hi Greetings to all

We are a remote OS/390 v2.04 ,DB2 v 5 site, we get connected to the machine by running IBM's Personal Communication emulator. Now we want to connect to DB2 from java application/applets. How do we do that? Could anybody mail me their experience(JDBC) and suggest some web sites for info.. [...] 7453 30 23_Use of Identity Columns19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 07:36:29 -0600488_iso-8859-1 Hi all and TIA, I'm trying to determine the value or applicability of an identity column in a parent table. Suppose, within the same unit of work, I wanted to add a new Customer, assign the Customer_ID via the identity column GENERATE ALWAYS, and one other child table. Customer_ID is the primary key and only index. I would like to have Customer_ID be the inherited foreign key in the child table. How would you go about handling Customer_ID for the insert in the child? [...] 7484 74 35_Re: How to connect to DB2 from java13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 18:58:01 +0530445_us-ascii hi!! First of all whats ur architecture.

If you want the java servelets to be running on teh mainframe. then the OS390 should have websphere running which will host the servelets. Now u will also have JDK on your OMVS . So now you can connect to DB2 thru the JDBC drivers, We have done at our installation . even we have connected where we have the websphere appl server on an NT and there sevelets connect to os390 db2 v5. [...] 7559 161 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 18:55:21 +0530300_us-ascii thanks Davis. !!

I am trying out all the options. I am just reviewing all that I have done. Its movement of data between subsystems. So the image copy of one subsystem is being used as input to DSN1copy to copy into a table of same structure and attributes in another subsystem. [...] 7721 24 15_indoubt threads13_John Arbogast16_jfarbo@YAHOO.COM31_Tue, 25 Jan 2000 05:48:49 -0800309_us-ascii Has anyone had experience with indoubt threads? We have some server to OS/390 to OS/390 threads that are indoubt. We issued the recover command with action commit. Now we have threads on one side of the OS/390 connection that are in a commited-h status. How would we get them out of the system? [...] 7746 64 47_Re: Batch programs, dependency analysis- OS/39014_Massimo Scarpa16_mscarpa@CESVE.IT31_Tue, 25 Jan 2000 14:58:29 +0100344_us-ascii My 1/2 cent.......

In previous shop were I worked we had a 'main' scheduled flow (via CONTROL-M) which

covered an entire wall of an office.....and the tuning took 1 year....

So I would like to know if your batch programs are developed in your shop or programs are

from a 3rd party vendor. Any utiliy ? [...] 7811 44 46_Transfering data from AS/400 DB2 to OS/390 DB212_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 25 Jan 2000 06:15:29 -0800315_us-ascii My client has come up with the situation that they need to transfer data on a regular basis automatically from an AS/400 DB2 system to an OS/390 DB2 (version 5 soon version 6) system. And they want to do this with the absolute minimum of involvement on the AS/400 side and with no software purchases. [...] 7856 18 50_Re: Transfering data from AS/400 DB2 to OS/390 DB213_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 19:47:15 +0530343_us-ascii Thats a really tuff situation But the option of going to access and all that is not prefferable. Even doing an insert is not advisable. Can u share resources (common) to os390 and as400 at ur installation . is the jes accessible from the as400 or is the cart drive accessible from as400 and os390 then I have solutions for you . [...] 7875 187 30_Re: Response to DSN1COPY Error15_Murley, Michael22_Michael_Murley@BMC.COM31_Tue, 25 Jan 2000 08:30:12 -0600368_ISO-8859-1 Vishy,

It is normal for the subsystem ID and STOGROUP/VCAT name in the header page to remain the same even after OBID translation. In itself, that shouldn't hurt anything.

When you did a dsn1prnt, what was the value of HPGLEVEL on the header page?

Also, are the source and target subsystems on the same release/maint level of DB2? [...] 8063 28 41_DB2 DBA Contract Opportunity - Boston, MA0_18_jtulman@MAAINC.COM31_Tue, 25 Jan 2000 09:28:05 -0500514_us-ascii Ladies and Gentlemen, A client of mine located in the Boston, Massachusetts area is looking for a highly qualified DB2 DBA for a minimum of 6 months temp to perm opportunity. The client is looking for this person to perform the typical Database Administration functions on the MVS/OS390 platform. Experience with UDB as well as IBM Certifications would be a huge plus but not required. Client is looking for this person to start ASAP. We can work with both Corp to Corp as well as with W2 employees. [...] 8092 29 16_OS/390 vs Oracle0_15_KATIKAN@AOL.COM29_Tue, 25 Jan 2000 09:33:07 EST515_us-ascii I have two questions:

1) In our installation, we have applications running on os/390 and PC. we use DB2 on mainframe and Oracle on PC applcations. we are trying to redesign our systems to have only one source of database. we have a maintainence probelms in maintaining the same tables in both database's and replicating huge amount of data now and then. Does anyone is aware of connecting Cobol/db2 applications to PC(oracle/powerbuilder application) effectiively with minimum network traffic. [...] 8122 304 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 20:04:27 +0530895_us-ascii Michael !!

This is what is the output of the dsn1prnt I have made the hpglevel as bold. can u get some clue ??

vishy

DSNAME = OSSY.DSNDBC.SY01D0.SYGCS2.I0001.A001 , VSAM





PGCOMB='10'X PGLOGRBA='00000C8BABC5'X PGNUM='00000000'X PGFLAGS='38'X

HPGOBID='01050143'X HPGHPREF='00000001'X HPGCATRL='00'X HPGREL='C8'X

HPGTORBA='000000000000'X HPGTSTMP='00010101000000000000'X HPGSSNM='ITPD'

HPGFOID='0024'X HPGPGSZ='1000'X HPGSGSZ='0004'X HPGPARTN='0000'X

HPGZ3PNO='000000'X HPGZNUMP='00'X HPGTBLC='0001'X HPGROID='0026'X

HPGZ4PNO='00000000'X HPGMAXL='000A'X HPGNUMCO='0002'X HPGFLAGS='2000'X

HPGCONTM='19991104082628912685'X HPGSGNAM='SY01G1 ' HPGVCATN='OOSY '

HPGRBRBA='000008484707'X HPGCLRSN='00000C8BABC5'X HPGLEVEL='000008484707'X HPGPLEVL='0000066F27DE'X HPGCLRSN='00000C8BABC5'X FOEND='N' [...] 8427 189 30_Re: Response to DSN1COPY Error19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 08:31:53 -0600624_iso-8859-1 Vishy, Your DSN1PRNT would indicate that maybe the DSN1COPY didn't go to the datasets you think they did. HTH, Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying [...] 8617 77 20_Re: OS/390 vs Oracle13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 20:06:24 +0530414_us-ascii hi Ravi !!

There is an excellent product from IBM , the Datajoiner, which will allow you to access the db2 /oracle / any dbms across platforms to connect to each other. have a look at ibm site and go thru the redbooks of datajoiner !!

Vishy







KATIKAN@AOL.COM on 01/25/2000 08:03:07 PM

Please respond to DB2 Data Base Discussion List [...] 8695 260 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 20:15:19 +0530555_us-ascii davis !!

I am just pasting the jcl too used during dsn1copy here !! I am sure the dsns are the same !! any other clue ??

//DSNCOPY JOB (ACCTNUM,EXP),'XXXXX', // NOTIFY=&SYSUID,TIME=1440, // CLASS=S,MSGCLASS=M,MSGLEVEL=(1,1) //EXECUTE EXEC PGM=DSN1COPY,PARM='CHECK,FULLCOPY,PRINT,OBIDXLAT,RESET' //STEPLIB DD DSN=DB2V510.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=A //SYSUT1 DD DSN=OSSY.SYGCT.IMAGE, // DISP=OLD //SYSUT2 DD DSN=OSSY.DSNDBC.SY01D0.SYGCS2.I0001.A001, // DISP=OLD //SYSXLAT DD * 258,261 37,324 40,326 39,325 38,322 /* [...] 8956 28 9_IFI trace14_Massimo Scarpa16_mscarpa@CESVE.IT31_Tue, 25 Jan 2000 15:46:43 +0100320_us-ascii I've to activate some IFCID traces and I have to read the results via IFI but ....unfortunately I don't know ASSEMBLER or C language (I use only REXX...I know, I'm a bad administrator...) so I ask if

there's an Internet site (or a good man/woman who wrote a pgm........) where I can find a complete [...] 8985 76 13_Re: IFI trace13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 20:20:37 +0530428_us-ascii hi Massimo !!

That was realy cool !! but things are not that simple . Here we are developing a online monitor for the db2 which will give online thread / lock / calls (all in detail ) we are using IFI calls and reading from a user started buffer into which the traces are thrown . You have to call the IFI thru DSNWLI !! and then map it to the assemble DSECTS given by IBM and then extract the variables !! [...] 9062 58 20_Re: OS/390 vs Oracle40_Andersen, Cindy (CC-Application Support)26_Cindy.Andersen@CONAGRA.COM31_Tue, 25 Jan 2000 08:48:17 -0600383_iso-8859-1 At a previous company we used Oracle's Transparent Gateway for DB2 and used triggers on the Oracle DML to insert, update, or delete from the DB2 tables on OS/390.

We also used Oracle's APPC Procedural Gateway to have Oracle C programs use Oracle stored procedures to issue CICS/DB2 transactions on the OS/390 side and then provide return to the source system. [...] 9121 285 30_Re: Response to DSN1COPY Error13_Martin Kenney25_martin.kenney@RAILINC.COM31_Tue, 25 Jan 2000 09:58:18 -0500388_us-ascii Try changing your SYSUT2 to the data portion instead of the cluster protion of you linear data set. That is how my DSN1COPY jobs are set up and I have never had the problem you are experiencing.







Viswanathan N on 01/25/2000 09:45:19 AM

Please respond to DB2 Data Base Discussion List [...] 9407 273 30_Re: Response to DSN1COPY Error18_Calvetti, Arthur B30_Arthur.Calvetti@PSS.BOEING.COM31_Tue, 25 Jan 2000 06:55:13 -0800673_iso-8859-1 Have you run the dump function of the repair utility to see if the translation actually worked?

> ---------- > From: Viswanathan N[SMTP:Viswanathan.N@POWAIMAIL.LTITL.COM] > Reply To: DB2 Data Base Discussion List > Sent: Tuesday, January 25, 2000 6:45 AM > To: DB2-L@RYCI.COM > Subject: Re: Response to DSN1COPY Error > > davis !! > > I am just pasting the jcl too used during dsn1copy here !! I am sure > the dsns are the same !! > any other clue ?? > > //DSNCOPY JOB (ACCTNUM,EXP),'XXXXX', > // NOTIFY=&SYSUID,TIME=1440, > // CLASS=S,MSGCLASS=M,MSGLEVEL=(1,1) > //EXECUTE EXEC PGM=DSN1COPY,PARM='CHECK,FULLCOPY,PRINT,OBIDXLAT,RESET' > //STEPLIB DD [...] 9681 61 13_Re: IFI trace20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 25 Jan 2000 09:57:30 -0500396_- Hi

Just in case you have access to DB2 Update from Xephon (or your friend has access to it), I have an article to format traces using rexx in Oct'99 issue. In case you don't have access then here are few tips.

- Use DSN1SDMP to start the trace and output goes to dataset (Use FOR keyword and avoid ACTION keyword) - Use rexx to format the output dataset which contains data. [...] 9743 326 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 20:31:07 +0530471_us-ascii martin !! just a moment !! I will try that !! but I think we have to point to the cluster component , I will reply in a moment !!

vishy







Martin Kenney on 01/25/2000 08:28:18 PM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc: (bcc: Viswanathan N/LTITL)





Subject: Re: Response to DSN1COPY Error [...] 10070 40 50_Re: Transfering data from AS/400 DB2 to OS/390 DB212_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 25 Jan 2000 07:04:22 -0800558_us-ascii As far as I can tell, JES and the OS/390 CART devices are not accessible from the AS/400. We can ping or FTP or communicate via TCPIP between the CPUs and that's about it (other than sneaker net).

--- Viswanathan N wrote: > Thats a really tuff situation But the option of > going to access and all > that is not prefferable. Even doing an insert is not > advisable. Can u share > resources (common) to os390 and as400 at ur > installation . is the jes > accessible from the as400 or is the cart drive [...] 10111 16 21_GQL using DB2/CONNECT10_Lyle Myers25_lyle.myers@TRICON-YUM.COM31_Tue, 25 Jan 2000 08:56:11 -0500427_US-ASCII Hi, Is there anyone that is using GQL with DB2/CONNECT ? We are experiencing hanging conditions after thread timeouts. Our versions are GQL(BI/QUERY) V5.2 and DB2/CONNECT V5.2.

================================================ 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. 10128 243 35_Re: Resend of Instream SQL question14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Wed, 26 Jan 2000 02:04:46 +1000486_us-ascii Roland,

I think you have proven that what Scott recommended (below) is required for DB2 V4 (but not needed for DB2 V5) to use the index effectively. I have simplified slightly:

SELECT * FROM DB21SQL.TTRNDIAG WHERE C_DISCHARG_DATE > (SELECT CURRENT DATE - 8 DAYS FROM DUMMY_1_ROW_TBL) AND C_DISCHARG_DATE < CURRENT DATE

Your SQL1 used BETWEEN and included an expression with CURRENT DATE which was stage 2 in DB2 V4. It therefore used tablespace scan. [...] 10372 201 44_Re: DB2 UDB v 5 on NT - Performance Problems0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 25 Jan 2000 09:13:14 -0600589_us-ascii Milorad, have you tried importing the production data BACK to production? I'm just wondering if the 'solution' was that the physical move from one DB to another did a 'better' reorg or runstat update or access path optimization than doing those functions on the production data in-place. I know, it's a stretch, and doesn't really sound logical, but it might work, since simply moving to development DB worked. If it works, it solves the problem for now, but doesn't give you any clues as to why. And worse, it could lead to the 'voodoo' approach to bug fixing ... everytime [...] 10574 283 30_Re: Response to DSN1COPY Error15_Murley, Michael22_Michael_Murley@BMC.COM31_Tue, 25 Jan 2000 09:22:14 -0600380_ISO-8859-1 Vishy,

I notice that an earlier DSN1PRNT output for the header page showed the DBID/PSID to be 0x105 (261) and 0x143 (323). Notice that this is almost, but not quite, the same as the target ids you have specified below. Was there an earlier run of this job that used 323 as the PSID? If so, have all subsequent runs used DISP=OLD on SYSUT2, as coded below? [...] 10858 333 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 20:53:43 +0530467_us-ascii hi martin !!

I does not work even with the data portion !! Actually I am just trying to see where I am going wrong. how do I find out whether the data is copied correctly or not. It tells it has copied , also it understands that the dataset is downleveled. now I am running a repair. then is my repair getting overridden ??, now I understand that this is a stupid question but there can be a parameter which might be affecting. any idea ?? vishy [...] 11192 329 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 20:58:10 +0530353_us-ascii hi Michael !!

ya the first time I did I had by mistake given 323 as the psid instead os 324 . now after that I changed to 324 and also I used disp = old. I even stopped the spacename. So I know that nobody is using. !!

vishy







"Murley, Michael" on 01/25/2000 08:52:14 PM [...] 11522 123 47_Re: Batch programs, dependency analysis- OS/39013_McDonald, Ian32_Ian.McDonald@ABBEYNATIONAL.CO.UK31_Tue, 25 Jan 2000 14:56:00 -0000311_- Linda I agree entirely with Max.

It isn't your job, however Susan Loria gives a good idea of how to go about it.

There is obviously a business case for the project so it is up to 'the Project Sponsor' to say how and when they 'would like it to be run' with dependencies and deadlines etc.. [...] 11646 287 30_Re: Response to DSN1COPY Error19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 09:34:23 -0600618_iso-8859-1 Vishy, I meant, does SYSUT1 match SYSUT2 in DSN1COPY job?

Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 11934 311 30_Re: Response to DSN1COPY Error19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 09:38:41 -0600580_iso-8859-1 Martin, good catch!

Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 12246 330 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 21:07:06 +0530500_us-ascii davis !!

Sysut1 is an imagecopy dataset and sysut2 is a db2 vsam dataset. I am trying to copy from sysut1 to sysut2 using dsn1copy !! is it fine ??

vishy







"DAVIS, RICK (SBCSI)" on 01/25/2000 09:04:23 PM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc: (bcc: Viswanathan N/LTITL)





Subject: Re: Response to DSN1COPY Error [...] 12577 100 20_Re: OS/390 vs Oracle0_22_Rohn.Solecki@MTS.MB.CA31_Tue, 25 Jan 2000 09:38:16 -0600493_us-ascii We use IBM CICS Client to provide that kind of connectivity. For example, PowerBuilder running on PC invokes a CICS transaction on a RS/6000 via CICS Client. The CICS/6000 transaction runs a COBOL program. This COBOL program can connect to Oracle on RS/6000 or use CICS Client to invoke transaction on OS/390 CICS to access DB2 or IMS DB's. Not as elegant as Datajoiner and the other products mentioned (ie cannot join tables from different DBs) but it gets the job done for us. [...] 12678 361 30_Re: Response to DSN1COPY Error19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 09:47:18 -0600631_iso-8859-1 Vishy, You sure job is running in correct DB2 subsystem? You sure tables are exactly same, i.e. SEGSIZE, etc.?

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 [...] 13040 12 15_Cognos with DB213_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Tue, 25 Jan 2000 09:38:19 -0600426_- Does anyone have experience running Cognos on an NT server using DB2 Connect to access an OS/390 DB2? If so, what flavor of DB2 Connect are you using..Enterprise or Personal Edition?

================================================ 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. 13053 36 19_Re: Cognos with DB212_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 25 Jan 2000 07:57:45 -0800643_us-ascii My current client is running COGNOS on NT server with DB2 Connect PE. Works just fine (mostly - sometimes have a few problems with timing issues on COGNOS - like trying to run when the reorgs are running, etc. and occasionally network problems)

What else would you like to know?

--- Shauna Hadden wrote: > Does anyone have experience running Cognos on an NT > server using DB2 > Connect to access an OS/390 DB2? If so, what flavor > of DB2 Connect are you > using..Enterprise or Personal Edition? > > ================================================ > To change your subscription options [...] 13090 408 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 21:26:09 +0530432_us-ascii davis !!

I think DSN1COPY is an offline utility and has nothing to do with the subsystem and also if I am running the repair in a wrong subsystem where the tablespace is not there it will surely give error , its going fine till that .. .. any other thing . If you want I will again prepare a sequence of operation that I doing. I will start from scratch and then I will note down what I am doing and send it .. [...] 13499 436 30_Re: Response to DSN1COPY Error19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 10:11:38 -0600628_iso-8859-1 Vishy, Please note my second question -- are tables exactly the same, i.e. SEGSIZE, etc.?

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 [...] 13936 73 50_Re: Transfering data from AS/400 DB2 to OS/390 DB20_15_leon@CA.IBM.COM31_Tue, 25 Jan 2000 11:11:01 -0500335_us-ascii While I would very much like for your customer to get a copy of DB2 Connect I really do think it would be a wrong solution. You would be much better off establishing direct connectivity between AS/400 and S/390. There is a book called DRDA Connectivity Guide that provides a lot of information on how to accomplish this. [...] 14010 475 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 21:42:09 +0530589_us-ascii Davis !! The table is not segmented at all !! Vishy







"DAVIS, RICK (SBCSI)" on 01/25/2000 09:41:38 PM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc: (bcc: Viswanathan N/LTITL)





Subject: Re: Response to DSN1COPY Error

















Note: Some recipients have been dropped due to syntax errors. Please refer to the "$AdditionalHeaders" item for the complete headers. [...] 14486 304 30_Re: Response to DSN1COPY Error20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 25 Jan 2000 11:17:52 -0500352_iso-8859-1 Vishy

CHECK and RESET option goes together so you don't have to mention CHECK. You also need only DBID, PSID and OBID of the table in the SYSXLAT dd. Use cluster or data does not really matter but I prefer using cluster.

Why not run the jcl below and show us the output (SYSPRINT message of this JCL). Post it to the list [...] 14791 36 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 21:46:20 +0530307_us-ascii hi !!

I will post on this after I do a through R & D on thursday !! tommorow we are off !! Thanks for the help !! maybe I will get some result. Can anyone at the same time try this out in their shop

1. create a table in one subsystem (simple tablespace in one single tablespace) [...] 14828 499 30_Re: Response to DSN1COPY Error20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 25 Jan 2000 11:19:24 -0500625_iso-8859-1 But your imagecopy is from segmented table. You must have your target table also segmented

> -----Original Message----- > From: Viswanathan N [SMTP:Viswanathan.N@POWAIMAIL.LTITL.COM] > Sent: Tuesday, January 25, 2000 11:12 AM > To: DB2-L@RYCI.COM > Subject: Re: Response to DSN1COPY Error > > Davis !! The table is not segmented at all !! > Vishy > > > > > "DAVIS, RICK (SBCSI)" on 01/25/2000 09:41:38 PM > > Please respond to DB2 Data Base Discussion List > > > > To: DB2-L@RYCI.COM > > cc: (bcc: Viswanathan N/LTITL) > > > > Subject: Re: Response to DSN1COPY [...] 15328 18 30_Re: Response to DSN1COPY Error14_Tom Decabooter27_Tom.Decabooter@HONDA-EU.COM31_Tue, 25 Jan 2000 17:22:14 +0100429_us-ascii We use this technique quite frequently to refresh our test databases from the production environment. It works great.

Tom Decabooter system administrator Honda Europe N.V.

================================================ 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. 15347 232 30_Re: Response to DSN1COPY Error14_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US31_Tue, 25 Jan 2000 11:25:54 -0500481_us-ascii Vishy,

I may have missed something in my cursory following of this thread .. but .. wasn't it your desire to use an image copy from one subsystem to replace the data in another ? - In the JCL you included I can't help but notice the high level qualifier for both the image copy and the receiving DB2 dataset component are the same. What are your VCATs for the two environments and is what you are using really the desired VSAM component. EX DB2p... vs DB2t... [...] 15580 13 21_Re: identifier column12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 25 Jan 2000 10:28:52 -0600385_- If you click on the APAR number, you can get the APAR cover letter and read about 5 pages of description of the function.

Roger Miller

================================================ 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. 15594 293 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 22:05:59 +0530487_us-ascii hi !!

The VCATs for both the subsystems for this database are same . I think I can have that , So that s not a problem . Vishy









William Gannon on 01/25/2000 09:55:54 PM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc: (bcc: Viswanathan N/LTITL)





Subject: Re: Response to DSN1COPY Error [...] 15888 557 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 22:06:40 +0530625_us-ascii hi Venkat !!

how do u say that it is from a segmented tablesspace ?

vishy







"Pillay, Venkat (PCA)" on 01/25/2000 09:49:24 PM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc: (bcc: Viswanathan N/LTITL)





Subject: Re: Response to DSN1COPY Error

















Note: Some recipients have been dropped due to syntax errors. Please refer to the "$AdditionalHeaders" item for the complete headers. [...] 16446 13 27_Re: Use of Identity Columns12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 25 Jan 2000 10:32:44 -0600390_- Right now, the answer is to select back the value. There is some plumbing missing for being able to insert and return a value.

Roger Miller

================================================ 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. 16460 388 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 22:13:37 +0530434_us-ascii hi !! I just tried that !!



Then I tried stopping the database . It threw the error in the log

DSNT500I -ITDD DSNB1OPP RESOURCE UNAVAILABLE 720 REASON 00C20104 TYPE 00000220 NAME OSSY.DSNDBC.SY01D0.SYGCS2.I0001.A001 DSNI003I -ITDD DSNILSTS UNABLE TO STOP 721 OBJECT-TYPE 00000100 OBJECT-NAME SY01D0 REASON 00C20104 TYPE 00000220 NAME OSSY.DSNDBC.SY01D0.SYGCS2.I0001.A001

the sysprint message [...] 16849 16 30_Re: Response to DSN1COPY Error13_Viswanathan N33_Viswanathan.N@POWAIMAIL.LTITL.COM31_Tue, 25 Jan 2000 22:24:11 +0530418_us-ascii see u guys !! I am signing off for today !! Its really very late !! Will apreciate if somebody tries the same and give info !! on the problem !!

Thanks !! Vishy

================================================ 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. 16866 30 25_remote package versioning10_Tom Taylor17_ttaylor@CHUBB.COM31_Tue, 25 Jan 2000 11:57:56 -0500624_us-ascii Hi all

Can anyone help? We use the version(auto) precompile option on static mainframe packages to keep control over how many versions of a package exists at any given time (3 in this case). This enables us to clean-up unused packages to keep the catalog of a manageable size. We have been using DB2 Connect to access mainframe DB2 for several years and have been unable to resolve this quandary: Remote statically bound packages offers no versioning capabilities and thus the application areas change the name of the remote package in order to establish a version-like capability. Without the version, [...] 16897 44 27_Re: Use of Identity Columns19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 10:54:48 -0600591_iso-8859-1 Roger, Thought so . . . Got any idea when the "plumbing" will be available? I have a fairly long development effort yet and can wait awhile.

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 [...] 16942 19 19_Re: Cognos with DB213_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Tue, 25 Jan 2000 10:51:08 -0600331_- I was not sure which flavor of DB2 Connect was required to run when Cognos was set up on a server instead of on a workstation.

What do the client connections look like when they hit the OS/390? Can you trace them back easily to the client userid or do they all use a generic userid when connecting and executing SQL? [...] 16962 586 30_Re: Response to DSN1COPY Error19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 10:58:30 -0600606_iso-8859-1 Vishy, Source and Target tables of DSN1COPY must be same. i.e. segmented and same segment size. You can't copy from a segmented to non-segmented table, at least not yet (V5).

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 [...] 17549 417 30_Re: Response to DSN1COPY Error0_19_mike.holmans@BT.COM31_Tue, 25 Jan 2000 16:53:47 -0000619_iso-8859-1 Does this mean that you are trying to DSN1COPY into a tablespace which is defined as ROSHARE with the ROSHARE OWNER being a different subsystem?

You need to stop the tablespace from the ROSHARE OWNER if that's the case.

Mike Holmans BT ISE Technical Design mike.holmans@bt.com

> -----Original Message----- > From: Viswanathan N [SMTP:Viswanathan.N@POWAIMAIL.LTITL.COM] > Sent: Tuesday, January 25, 2000 4:44 PM > To: DB2-L@RYCI.COM > Subject: Re: Response to DSN1COPY Error > > hi !! I just tried that !! > > > Then I tried stopping the database . It threw the error in the log > > [...] 17967 71 27_Re: Use of Identity Columns7_Jim Lee25_JLee@PRINCETONSOFTECH.COM31_Tue, 25 Jan 2000 12:06:57 -0500546_- Roger

Does this also apply to the ROWID data type. In other words, if I have a table that contains a column of type ROWID which is the unique key to the table, and data is inserted into the table dynamically, is there any way of getting the generated ROWID value returned to identify the row that was just inserted. Since that column is the unique key to the table, it is possible that the table can contain other rows where the contents of all others columns are the same and without the unique ROWID value, it would be impossible [...] 18039 19 13_CDB Utilities18_frank stadankowitz23_fstadankowitz@YAHOO.COM31_Tue, 25 Jan 2000 09:01:01 -0800364_us-ascii I am looking for DB2 utilities and have some interest in CDB. The other players are CA (Platinum) and BMC. I have heard good things about CDB and they have always seemed to be the fastest utilities. Any opinions? __________________________________________________ Do You Yahoo!? Talk to your friends online with Yahoo! Messenger. http://im.yahoo.com [...] 18059 591 30_Re: Response to DSN1COPY Error20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Tue, 25 Jan 2000 12:15:06 -0500626_iso-8859-1 Vishy

The DSN1PRNT you showed from imagecopy tells us that imagecopy belongs to segmented table. If you are saying the target table is not segmented then it is error. You must have same segsize for target as the table from imagecopy.

Regards Venkat

> -----Original Message----- > From: Viswanathan N [SMTP:Viswanathan.N@POWAIMAIL.LTITL.COM] > Sent: Tuesday, January 25, 2000 11:37 AM > To: DB2-L@RYCI.COM > Subject: Re: Response to DSN1COPY Error > > hi Venkat !! > > how do u say that it is from a segmented tablesspace ? > > vishy > > > > > "Pillay, Venkat (PCA)" [...] 18651 90 27_Re: Use of Identity Columns0_19_Tim.Lowe@STPAUL.COM31_Tue, 25 Jan 2000 11:17:20 -0600406_us-ascii Rick, Someone previously suggested to me that perhaps a trigger could be used to insert the assiged value into a global temporary table that you could then retrieve. I have not tried this yet, but I just wondered if this would this work for you?

Thanks, Tim













"DAVIS, RICK (SBCSI)" on 01/25/2000 10:54:48 AM [...] 18742 467 30_Re: Response to DSN1COPY Error12_Tal Lifshitz20_Tal.Lifshitz@WDR.COM31_Tue, 25 Jan 2000 12:16:32 -0500374_iso-8859-1 If you are DSN1COPYing a tablespace from a ROSHARE database to a non-ROSHARE database (no matter if in the same subsystem or in different subsystems) you should turn off the flag in the header page indicating that this tablespace belongs to a ROSHARE database. You do this after the DSN1COPY has successfully completed using the following REPAIR statements: [...] 19210 13 19_Re: indoubt threads11_John Rosser24_jhrosser@DUKE-ENERGY.COM31_Tue, 25 Jan 2000 11:27:53 -0600362_- Have you tried the -RESET INDOUBT command? See Command Ref for more info. Regards, John Rosser Duke Energy Corporation

================================================ 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. 19224 55 30_Re: Selecting Image Properties14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Tue, 25 Jan 2000 11:47:00 -0600547_ISO-8859-1 I am assuming that you are using LOBs in DB2 V6 to store your images. Furthermore, you should be using the DB2 image extender, not just a generic BLOB data type. If this is not true, then disregard the following.

The first thing you should do is download the following manual from the IBM web site: "DB2 Universal Database for OS/390: Image, Audio, and Video Extenders Administration and Programming." There is no document number for this manual. You can find it at http://www-4.ibm.com/software/data/db2/os390/v6books.html [...] 19280 20 22_DSN1COPY Seg to Simple12_Bing Alabata25_bing.alabata@MCMASTER.COM31_Tue, 25 Jan 2000 11:44:57 -0600487_us-ascii Hi again Vishy!

Venkat is right. You are DSN1COPYing from a segmented to a simple tablespace. This is evident from your DSN1PRNT header page output indicating a SEGSIZE of 4, i.e. SEGSIZE='0004'X

Keep in touch,

Bing

================================================ 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. 19301 131 27_Re: Use of Identity Columns19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 11:57:07 -0600453_iso-8859-1 Tim, Thanks for suggestion. Without the afore mentioned "plumbing", my thoughts are running more towards using the special register value for CURRENT TIMESTAMP as my PK. I think it would be the fastest way to generate a non-intelligent ID (NID). Might go something like this:

COMMIT SET :HOST-VAR1 = CURRENT TIMESTAMP INSERT INTO PARENT NID, ... VALUES (:HOST-VAR1, ...) INSERT INTO CHILD NID, ... VALUES (:HOST-VAR1, ...) COMMIT [...] 19433 43 37_FW: DB2 Packages - Transition to.....10_Moe, Sammi24_Sammi.Moe@PACIFICORP.COM31_Tue, 25 Jan 2000 09:56:32 -0800601_iso-8859-1 > -----Original Message----- > From: Frost, Pamela > Sent: Tuesday, January 25, 2000 9:11 AM > To: 'db2-l@ryci.com' > Cc: Moe, Sammi; Greer, Billy > Subject: DB2 Packages - Transition to..... > > I am looking for information on migrating from performing "BIND PLAN" to > "BIND PACKAGE". > > The majority of our current inventory of DB2 applications are generated > using CoolGen and applications are bound and linked using Endevor. > > * What criteria should be considered in determining number of plans? > * What criteria should be used in defining collections? > * Does anyone using [...] 19477 83 29_Re: DB2 referential integrity14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Tue, 25 Jan 2000 12:10:37 -0600600_ISO-8859-1 Although I agree with the general sentiment expressed by others (if the RI is not expressed as declarative constraints in DB2 then the RI is not 100 per cent guaranteed), I have to express a few exceptions to the general rule.

DB2 does a referential integrity check for every row insertion. You can increase efficiency if your application does a single check of a row from the parent table and then makes multiple inserts to the child table. This should not be the sole determining factor for implementing RI or not, though, because program logic can fail (bugs, errors, etc.) [...] 19561 177 27_Re: Use of Identity Columns0_19_Tim.Lowe@STPAUL.COM31_Tue, 25 Jan 2000 12:12:48 -0600305_us-ascii Rick, That sounds like a good idea. It would probably work fine in almost all cases if you don't mind the size of the identifier. However, there might be some high-activity situations where you could wind up with duplicates. But, that might not be a problem in your case.

Thanks, Tim [...] 19739 25 18_online reorg error0_26_mmetcalf@NOTES.STATE.NE.US31_Tue, 25 Jan 2000 12:19:33 -0600278_us-ascii I have run into a problem with the online reorg and this same problem was discussed on the list Oct 22 but I cannot see the actual notes on the archive list. I went to the site below and see the heading but cannot see the actual memos and possible solutions, etc. [...] 19765 308 30_Re: Response to DSN1COPY Error15_Murley, Michael22_Michael_Murley@BMC.COM31_Tue, 25 Jan 2000 12:33:25 -0600482_ISO-8859-1 Vishy,

Others have already pointed out that the DSN1PRT output indicates that you copied a segmented image copy into a non-segmented space. I will also add that the only segment entry that shows up in the DSN1PRT output is for OBID 0x0026 (38), which is one of the source OBIDS in the JCL you sent earlier. Did an earlier run omit OBID 38? If so, and based on the PSID mismatch I mentioned earlier, I believe the VSAM data set still reflects the earlier run. [...] 20074 34 23_MIGRATING FROM V4 TO V517_Luke G. Bauerlein24_lbauerlein@MANDTBANK.COM31_Tue, 25 Jan 2000 13:41:24 -0500614_US-ASCII After migrating from v4 to v5 on a "sysprog" LPAR and then being able to fall back to V4 by steplibing the load libs, I attempted to move needed V4 maintenance (required for migration/fallback (APAR II10128) ) to a "development LPAR using the following methodology: Stop DB2 on devl LPAR Free devl load libs from linklist Copy sdsnexit,sdsnload and sdsnlink from stag LPAR to devl LPAR re-assemble DSNZPARM into sdsnexit refresh linklist Restart DB2 (I even tried steplibs) I then did a few queries, commands through spufi etc to check out maintained version. All seemed OK. Developers arrived and a [...] 20109 297 30_Re: Response to DSN1COPY Error14_William Gannon41_wgannon@INET-SERVICES.PALMBEACH.K12.FL.US31_Tue, 25 Jan 2000 13:43:31 -0500506_us-ascii Vishy, Not to argue the point to death .. but .. How can two DB2 subsystems have the same VCAT unless they are running on different LPARs ??? DB2 dynamically open the underlying VSAM datasets by building the the DSN from the catalog info .. vcat.DSNDBC.dbname.tsname.I0001.A001 - So if your two subsystems are not on separate LPARs - what controls the DB2 dynamic dataset allocation ? - Do you avoid dataset name collisions by having different DB or tablespace names on test and production ? [...] 20407 208 27_Re: Use of Identity Columns19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 12:45:56 -0600604_iso-8859-1 Yes Tim, of course the standard caveat about TIMESTAMP dups applies. I should have mentioned it. We simply check for any -803s after the insert, if so, reset current timestamp, and redrive the insert.

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 [...] 20616 65 27_Re: MIGRATING FROM V4 TO V519_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Tue, 25 Jan 2000 13:00:06 -0600359_iso-8859-1 Luke, This doesn't sound like a locking problem. I don't remember real well how/if the DB2 cat/dir tables changed going from V4 to V5. This could be part of problem. Is it possible the DEVL LPAR is back-leveled from the 'sysprog' LPAR from a DB2 cat/dir table standpoint, and the target library code is looking for stuff that isn't there yet? [...] 20682 48 13_Re: IFI trace12_Jim Herrmann12_b3d@CUOL.NET31_Tue, 25 Jan 2000 13:02:45 -0600371_us-ascii I'm not sure, but I seem to recall that REXXTOOLS from Open Software Technologies ( http://www.open-softech.com/rexxmvs.htm ) had a REXX interface for IFCIDs. It may have been one of the other REXX tools packages we looked at. This would allow you to write exactly what you want in REXX. If it can be done on the mainframe, there is a way to do it in REXX. [...] 20731 53 22_Re: online reorg error16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Tue, 25 Jan 2000 14:03:05 -0500731_us-ascii Michelle try this

http://www.dejanews.com/home_ps.shtml - specify bit.listserv.db2-l in the FORUM field. It will allow you to search both current and archived DB2-L messages. hth Tom







mmetcalf@NOTES.STATE.NE.US on 01/25/2000 01:19:33 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: online reorg error







I have run into a problem with the online reorg and this same problem was discussed on the list Oct 22 but I cannot see the actual notes on the archive list. I went to the site below and see the heading but cannot see the actual memos and possible [...] 20785 14 27_Re: MIGRATING FROM V4 TO V50_26_sandy.kordick@ZURICHUS.COM31_Tue, 25 Jan 2000 13:04:14 -0600398_us-ascii IF you have moved sdsnlink into a linklist library. This is the new early code for db2 and the lpar would have to be ipled for this to take effect.

================================================ 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. 20800 94 20_Re: OS/390 vs Oracle14_Marilyn Stokes28_mstokes@OPPENHEIMERFUNDS.COM31_Tue, 25 Jan 2000 10:07:29 -0700367_us-ascii I looked at Data Joiner a few years ago, had many limitations and bugs. Check out CA?Platinum tool to migrate data from one server to another. You only have to license it on the server that it runs on, and has more flexibility than the IBM tool. I forget the name of their tool, check with your sales rep or maybe someone on this list knows the name. m [...] 20895 18 50_DB2 for OS/390 V 5-6 software migration strategies17_Gerald Bustamente20_ggbusta@LANDSEND.COM31_Tue, 25 Jan 2000 13:37:53 -0600383_koi8-r I just wanted to say thanks to those of you who responded to my posting on this subject.

:-)

Jerry Bustamente Lands' End

================================================ 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. 20914 15 24_DB2 REASON CODE 00C9009028_Dan Courter (1-408-463-3870)27_daniel_courter@VNET.IBM.COM29_Tue, 25 Jan 2000 12:15:37 PST461_- This reason code should not be visible outside DB2, but handled internally.

Could someone that has seen this reason code please refresh my memory about what they were doing at the time it was issued?

Dan

================================================ 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. 20930 42 28_Re: DB2 REASON CODE 00C9009018_Richard Mccutcheon32_Richard_McCutcheon@HCM.HONDA.COM31_Tue, 25 Jan 2000 14:29:15 -0500551_us-ascii Before version 5 of DB2 the reason code meant "An attempt to lock a resource failed". I assume that it still means that.











"Dan Courter (1-408-463-3870)" on 01/25/2000 04:15:37 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Richard McCutcheon/HCM/AM/HONDA) Subject: DB2 REASON CODE 00C90090







This reason code should not be visible outside DB2, but handled internally. [...] 20973 75 25_DB2 referential integrity11_James Drewe20_James.Drewe@AEXP.COM31_Tue, 25 Jan 2000 12:57:56 -0700405_- Madhavan

DB2 RI discussions can become quasi-religious debates. I will share my experiences, but you will have to make up your own mind as to what is best for your physical design and your installation ,s standards. Again, like Richard Yevich pointed out, there is declarative RI, as well as application RI. It is never a question of whether there will be RI, but where is it better suited? [...] 21049 30 14_regarding load17_ravi kumar hassan18_ravibh@HOTMAIL.COM29_Tue, 25 Jan 2000 14:29:47 PST511_- Hello Everybody,

i have a question regarding the load, in our shop normally we load the data from prod. to test database. suppose somebody will come and ask somebody to load the data from prod. to test tables as of today. but how would i know what ever the data is loaded from prod. to test is of todays data or he might have loaded the data from unloaded datasets which was taken long back. is there anyway to check the data is correct as of today. or is there way we can check in catlog tables. [...] 21080 255 27_Re: Use of Identity Columns0_19_Tim.Lowe@STPAUL.COM31_Tue, 25 Jan 2000 16:40:14 -0600291_us-ascii Rick, If you drive a routine that does inserts like this so fast that duplicates occur, then your retry logic may also get duplicates. The result could be a very busy system going in circles. Beware of retry logic. You could be creating a loop that eats CPU cycles. Good luck. [...] 21336 30 36_Oracle Transparent G'Way Question...16_Charles Valentin15_VALENCH@AOL.COM29_Tue, 25 Jan 2000 17:46:19 EST616_us-ascii Hello List Members, We're running Oracle G'Way V8.0.4 with DB2 OS/390 V5.1. The Oracle threads that come in from the Sun/Solaris remain in DB2 (most of them until Oracle is recycled). In TMONDB2, we can see numerous Oracle threads & some of them are holding locks on DB2 Catalog Tablespaces (which lockout any DB2 bind processing). Is there any way to make these threads "go away" once the complete query to DB2? Current bind setting for release is (commit) & Application folks say the they are commiting after queries are executed. Any recommendations will be greatly appreciated. We've called Oracle [...] 21367 46 30_Re: Selecting Image Properties16_Richard A Yevich21_ryevich@ATTGLOBAL.NET31_Tue, 25 Jan 2000 16:57:04 -0500363_iso-8859-1 You need the DB2 Extenders for Image, part of the DB2 V6 on all platforms, including OS/390. The feature you are talking about is called QBIC -- Query By Image COntent. You can even see examples of this at: http://www.hermitagemuseum.org -- well worth the visit.

Hope this helps, Richard Yevich +======+======+======+ ryevich@attglobal.net [...] 21414 22 28_Using CAF for batch programs11_GOSE PAUL M17_pmgose@SRPNET.COM31_Tue, 25 Jan 2000 16:03:58 -0700401_ISO-8859-1 Hello, We are considering a change from running our batch DB2 programs under TSO, IKJEFT01, to using the Call Attach Facility. I'm doing research on third party products to facilitate this and I have found only one so far, "Database Attach" from Computer Associates. Can anyone recommend products that they've used and had good luck with? How about products to stay away from? Thanks. [...] 21437 50 18_Re: regarding load16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Tue, 25 Jan 2000 16:13:36 -0700554_- You can look at SYSCOPY and see the date and time the last LOAD was done (ICTYPE = R or S) but that won't tell you from where or when the data was loaded.

Duane

> -----Original Message----- > From: ravi kumar hassan [SMTP:ravibh@HOTMAIL.COM] > Sent: Tuesday, January 25, 2000 3:30 PM > To: DB2-L@RYCI.COM > Subject: regarding load > > Hello Everybody, > > i have a question regarding the load, in our shop normally we load the > data > from prod. to test database. > suppose somebody will come and ask somebody to load the data from [...] 21488 73 32_Re: Using CAF for batch programs0_19_Tim.Lowe@STPAUL.COM31_Tue, 25 Jan 2000 17:15:59 -0600366_us-ascii Hello Paul, Why do you need a product to switch from TSO attach to CAF attach. By linkediting with DSNALI instead of DSNELI, you will use "Implicit Call Attach". Use of Implicit call attach was made easier in DB2 V5 when they added sqlcode -991.

I hope this helps.

Thanks, Tim

P.S. Why are you considering changing to call attach? [...] 21562 69 18_Re: regarding load24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Tue, 25 Jan 2000 15:22:47 -0800550_- I think you best option is to look at the data itself, possibly looking for a last update date, or something similar. The maximum value should give you a pretty good indication of when the data was copied.

> -----Original Message----- > From: Duane Lee - ATCX [SMTP:DLee@MAIL.MARICOPA.GOV] > Sent: Tuesday, January 25, 2000 3:14 PM > To: DB2-L@RYCI.COM > Subject: Re: regarding load > > You can look at SYSCOPY and see the date and time the last LOAD was done > (ICTYPE = R or S) but that won't tell you from where or when the data was [...] 21632 47 40_Re: Oracle Transparent G'Way Question...15_Mitchell, Sandi29_Sandi.Mitchell@AVISTACORP.COM31_Tue, 25 Jan 2000 14:54:02 -0800323_iso-8859-1 That was the word we got for Oracle also. Our application folks are coding commits after every query that comes through the gateway, even if it is only a select. That does the trick most of the time. We have scheduled recycling the gateway twice daily and are moving away from using the gateway in general. [...] 21680 61 32_Re: Using CAF for batch programs15_Toine Michielse18_vndobtm@US.IBM.COM31_Wed, 26 Jan 2000 00:45:32 +0000398_us-ascii Hello Paul,

Not sure what kind of functionality you are looking for but building your own CAF interface routine that will perform services like connect, create thread etc is dead easy. The application programmng guide is an excellent starting point. From experience I can tell you that it does not require a lot of work, maintenance and the code is pretty straight forward.... [...] 21742 94 32_Re: Using CAF for batch programs11_GOSE PAUL M17_pmgose@SRPNET.COM31_Tue, 25 Jan 2000 16:56:16 -0700339_iso-8859-1 Thanks for the suggestion Tim, although I don't think we could live with the plan name default associated with implicit connections. We are interested in the third party utility to avoid making program changes. The decision to investigate CAF over TSO was based on the potential for CPU savings. Thanks again for the help. [...] 21837 162 32_Re: Using CAF for batch programs0_19_Tim.Lowe@STPAUL.COM31_Tue, 25 Jan 2000 18:31:05 -0600384_us-ascii Paul, What is the problem with the plan name default in implicit call attach?

In order to use an "explicit call attach" routine (written by you or a third-party vendor) you would probably have to make program changes. But, if your objective is to avoid program changes, then it would seem that if you want to use CAF, then implicit attach might be a better idea. [...] 22000 61 29_Re: remote package versioning14_Steven Camitta30_steven.camitta@INGRAMMICRO.COM31_Tue, 25 Jan 2000 16:57:31 -0800445_iso-8859-1 Tom,

It's been awhile but I believe I used the VERSION parameter on the client PREP when I was testing embedded SQL code. It enables you to assign a user controlled version while keeping the package name constant. So you can distribute different versions of your client app while retaining the prior package versions as fallback. You might use V000, V001, etc. Not a true version AUTO but better than nothing. Rgds, Steve [...] 22062 271 35_Re: Resend of Instream SQL question11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Wed, 26 Jan 2000 09:34:15 +0800451_us-ascii Michael, Thanks for the explanation, your simplified sql completed in 3 second which is much much faster than SQL1 and SQL2.

Regard, Roland







Michael Hannan on 26/01/2000 12:04:46 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: ROLAND CHUA CHONG KEE/DATACEN/IT/SGX) Subject: Re: Resend of Instream SQL question [...] 22334 63 40_Re: Oracle Transparent G'Way Question...40_Andersen, Cindy (CC-Application Support)26_Cindy.Andersen@CONAGRA.COM31_Tue, 25 Jan 2000 21:29:03 -0600674_iso-8859-1 At my previous place of employment, we also found we had to code commits after every query through the gateway, including a select.

-----Original Message----- From: Mitchell, Sandi [mailto:Sandi.Mitchell@AVISTACORP.COM] Sent: Tuesday, January 25, 2000 4:54 PM To: DB2-L@RYCI.COM Subject: Re: Oracle Transparent G'Way Question...



That was the word we got for Oracle also. Our application folks are coding commits after every query that comes through the gateway, even if it is only a select. That does the trick most of the time. We have scheduled recycling the gateway twice daily and are moving away from using the gateway in general. [...] 22398 22 19_db2 newbie question5_Jamie14_jamie@DANG.COM31_Tue, 25 Jan 2000 23:32:30 -0500318_iso-8859-1 I want to have a unique field in a table that automatically generates a unique number when I insert a new row. This is automatic in SQL Server and Access, but I don't see an easy way to do this in db2 (UDB V6). Any help is much appreciated.

Thank,

Jamie Jamie Orchard-Hays jamie@dang.com [...] 22421 117 23_Re: db2 newbie question0_24_ssethi@LOT.TATASTEEL.COM29_Wed, 26 Jan 2000 07:18:28 GMT504_us-ascii Hi , I am sending u the SQL Reference contents ---basically cut and paste.U can refer as well in the sql refernece UDB V6 the scalar functions chapter(4).I hope it will solve ur problems...



>>-GENERATE_UNIQUE--(--)---------------------------------------><



The schema is SYSIBM.

The GENERATE_UNIQUE function returns a bit data character string 13 bytes long (CHAR(13) FOR BIT DATA) that is unique compared to any other execution of the same function. 37 [...] 22539 35 23_Re: db2 newbie question14_Tom Decabooter27_Tom.Decabooter@HONDA-EU.COM31_Wed, 26 Jan 2000 09:11:20 +0100404_us-ascii Jamie,

we are using a trigger to create unique sequence numbers (UDB V6 FixPack 1a on WindowsNT). You find an example below :

CREATE TRIGGER triggername AFTER INSERT ON table1 REFERENCING NEW AS NEWITEM FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE table2 SET LASTID = LASTID + 1; UPDATE table1 SET MEMBERID = (SELECT LASTID FROM table2) WHERE MEMBERID = NEWITEM.MEMBERID; END [...] 22575 112 29_Re: DB2 referential integrity19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Wed, 26 Jan 2000 06:51:49 -0600568_iso-8859-1 Jim, Appreciate your comments and could probably safely say that every DBA has struggled with this issue. I agree that this subject can seem to be quasi-religious. However, you'll need a God of some type to protect your job if you rely on any form of RI that isn't enforced at the DB level. You had better spell out those RI forms that can't be enforced at the DB level, or application enforced RI that was chosen for whatever "quasi-religious" reason and the exposure to loss of data integrity it implies. In this manner, you can tell them there were [...] 22688 298 27_Re: Use of Identity Columns19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Wed, 26 Jan 2000 07:30:33 -0600570_iso-8859-1 Tim, Boy, don't you know it! Been there; done that; got kicked there; even got the "I survived . . ." T-Shirt! My experience was that when you start dup'ing on TIMESTAMP (only seen this twice in 15 yrs., the other time during a benchmark test to determine max load available for that machine setup) you are at peak demand which usually drops off very rapidly thereby allowing the retry logic to break thru rapidly. I agree with you in that, on occasion, the retry logic will elongate this peak demand period. It will certainly feed on itself. Thought of [...] 22987 35 21_Re: identifier column14_Grainger, Phil21_Phil.Grainger@CAI.COM31_Wed, 26 Jan 2000 13:39:53 -0000651_- Roger,

I'm looking for the identity column APAR but not finding it - any clues??

Thanks

Phil G

> -----Original Message----- > From: Roger Miller [SMTP:millerrl@US.IBM.COM] > Sent: Tuesday, January 25, 2000 4:29 PM > To: DB2-L@RYCI.COM > Subject: Re: identifier column > > If you click on the APAR number, you can get the APAR cover letter and > read > about 5 pages of description of the function. > > Roger Miller > > ================================================ > 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 [...] 23023 77 21_Re: identifier column9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Wed, 26 Jan 2000 13:44:12 +0000493_us-ascii PQ30652 - Identity column APAR. Kind Regards, Andy Hunt - Scottish And Southern Energy







"Grainger, Phil" on 26/01/2000 13:39:53

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Andy Hunt/HAV/SSE) Subject: Re: identifier column





Roger,

I'm looking for the identity column APAR but not finding it - any clues??

Thanks

Phil G [...] 23101 60 21_Re: identifier column19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Wed, 26 Jan 2000 08:00:45 -0600608_iso-8859-1 Phil, its PQ30652: ADD NEW FUNCTION - IDENTITY COLUMNS Rick Davis "This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited." [...] 23162 77 30_Re: Selecting Image Properties13_Marcus Davage29_Marcus.Davage@LLOYDSTSB.CO.UK31_Wed, 26 Jan 2000 14:19:43 -0000581_iso-8859-1 Richard,

I've just checked out the site, and it's brilliant! Is this run on OS/390 or UDB?

Marcus

-----Original Message----- From: Richard A Yevich [mailto:ryevich@ATTGLOBAL.NET] Sent: 25 January 2000 21:57 To: DB2-L@RYCI.COM Subject: Re: Selecting Image Properties



You need the DB2 Extenders for Image, part of the DB2 V6 on all platforms, including OS/390. The feature you are talking about is called QBIC -- Query By Image COntent. You can even see examples of this at: http://www.hermitagemuseum.org -- well worth the visit. [...] 23240 64 32_Re: Using CAF for batch programs14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU31_Wed, 26 Jan 2000 09:40:22 -0500610_us-ascii I'm curious about what these products do. We use CAF for many of our heavy-duty programs and have found the interface sufficiently straightforward to use directly.

One pitfall we've encountered is that an explicit connection will NOT use the default subsystem identifier -- if you leave the subsys ID blank, you get an error (Failing any logical explanation, we assume that IBM does this to make people crazy.) This means we have to modify and recompile the programs for test mode. We have considered writing an intermediate program that will set the subsys ID based on the SMF system ID. [...] 23305 65 39_Re: Connect to database takes very long11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Wed, 26 Jan 2000 09:51:00 -0500371_iso-8859-1 For the database with the delayed first connect: * Can you activate it so that subsequent users do not experience the delay? * How many megabytes of primary log space? The first connect usually has to format the entire primary allocation. * Hom many megabytes of bufferpool allocations? I've seen first connects delayed when large bufferpools are in use. [...] 23371 68 19_db2 newbie question0_21_RVPham@DSTSYSTEMS.COM31_Wed, 26 Jan 2000 08:51:31 -0600443_us-ascii DB2 UDB has a function called generate_unique(). This is the example from SQL Ref book:

Create a table that includes a column that is unique for each row. Populate this column using the GENERATE_UNIQUE function. Notice that the UNIQUE_ID column has "FOR BIT DATA" specified to identify the column as a bit data character string. CREATE TABLE EMP_UPDATE (UNIQUE_ID CHAR(13) FOR BIT DATA, EMPNO CHAR(6), TEXT VARCHAR(1000)) [...] 23440 132 32_Re: Using CAF for batch programs20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 26 Jan 2000 09:57:24 -0500445_- Paul

I don't think it is good idea to change TSO program to CAF just for the sake of saving CPU because the CPU saving is insignificant. The SQLs don't run faster in CAF instead they just depend on the access path not on the attachment facility (I am talking about CLASS 2 time). Implicit connection does not give you any benefit because you loose lot of advantage TSO provides like attention processing and recovery routines etc. [...] 23573 100 55_Re: Listing Databases for an Instance (UDB v6.1 on AIX)11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Wed, 26 Jan 2000 10:23:25 -0500650_iso-8859-1 Bill, Maybe this will help?

/usr/lpp/db2_06_01/instance/db2ilist | while read iname do print "Processing instance: $iname" db2 -v "attach to $iname" if [[ $? -eq 0 ]] then db2 "list database directory" | grep '=' | while read listout do print ${listout%=*} | read data_label print ${listout##*=} | read data_value case $data_label in 'Database name' ) dbname=$data_value;; 'Directory entry type') dbtype=$data_value;; 'Catalog node number') if [[ $dbtype = "Indirect" ]] then db2 -v "connect to $dbname" db2 -v "list tables" # or other action db2 terminate else print "Skipping remote database: $dbname" fi;; esac done fi done [...] 23674 85 32_Re: Using CAF for batch programs14_Steven Camitta30_steven.camitta@INGRAMMICRO.COM31_Wed, 26 Jan 2000 07:22:52 -0800548_iso-8859-1 Paul,

There's a sample CAF API at WWW.XEPHON.COM, see DOWNLOADS, DB2 Update, follow link to search by article title, find DB2CAF Program.

HTH, Steve

-----Original Message----- From: Harvey Wachtel [mailto:babybaby@UAPC.CUNY.EDU] Sent: Wednesday, January 26, 2000 6:40 AM To: DB2-L@RYCI.COM Subject: Re: Using CAF for batch programs



I'm curious about what these products do. We use CAF for many of our heavy-duty programs and have found the interface sufficiently straightforward to use directly. [...] 23760 84 39_Re: Connect to database takes very long13_Lynne Flatley17_LFlatley@NEFN.COM31_Wed, 26 Jan 2000 10:31:34 -0500627_us-ascii Could someone refresh my memory as to how one would 'schedule' the activate command to run ever time the instance is stopped/started or the server is rebooted?

Thanks!

> -----Original Message----- > From: Scott Hayes [SMTP:faster.databases@DATABASE-GUYS.COM] > Sent: Wednesday, January 26, 2000 9:51 AM > To: DB2-L@RYCI.COM > Subject: Re: Connect to database takes very long > > For the database with the delayed first connect: > * Can you activate it so that subsequent users do not experience > the delay? > * How many megabytes of primary log space? The first connect > usually has to format the [...] 23845 92 23_Re: db2 newbie question18_Jamie Orchard-Hays14_jamie@DANG.COM31_Wed, 26 Jan 2000 10:29:08 -0500652_us-ascii Thanks, I'll try it out!

>DB2 UDB has a function called generate_unique(). >This is the example from SQL Ref book: > >Create a table that includes a column that is unique for each row. Populate this column using the GENERATE_UNIQUE function. Notice that the UNIQUE_ID >column has "FOR BIT DATA" specified to identify the column as a bit data character string. > CREATE TABLE EMP_UPDATE > (UNIQUE_ID CHAR(13) FOR BIT DATA, > EMPNO CHAR(6), > TEXT VARCHAR(1000)) > > INSERT INTO EMP_UPDATE > VALUES (GENERATE_UNIQUE(), '000020', 'Update entry...'), > (GENERATE_UNIQUE(), '000050', 'Update entry...') > >This table will have a unique [...] 23938 127 23_Re: db2 newbie question18_Jamie Orchard-Hays14_jamie@DANG.COM31_Wed, 26 Jan 2000 10:29:41 -0500625_us-ascii Thanks. You and one other came up with similar solutions, so I'll try it out!

>Hi , > I am sending u the SQL Reference contents ---basically cut and paste.U can refer as well in the sql refernece UDB V6 >the scalar functions chapter(4).I hope it will solve ur problems... > > > >>-GENERATE_UNIQUE--(--)--------------------------------------->< > > >The schema is SYSIBM. > >The GENERATE_UNIQUE function returns a bit data character string 13 bytes long (CHAR(13) FOR BIT DATA) that is >unique compared to any other execution of the same function. 37 > >There are no arguments to this function (the empty [...] 24066 38 21_Question on Utilities18_frank stadankowitz23_fstadankowitz@YAHOO.COM31_Wed, 26 Jan 2000 08:03:23 -0800454_us-ascii I take it from the lack of response that no one is using any third party utilities. Is the fact that DB2 utilities are that good? Do people even use any third party software (no names need to be mentioned)? I have sales people calling me and I am trying to justify the expense in my own mind. We have a small installation where I am at and have not felt any pain in the night shift processing so far but things are starting to get tighter. [...] 24105 40 19_SYSCOLDIST question10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Wed, 26 Jan 2000 10:12:10 -0600342_iso-8859-1 All,

Currently we execute CA/Platinum PDA statistics. The main reason for the daily run is to report on any DB2 object abnormalities. The statistics are posted to the DB2 catalog with one exception. SYSCOLDIST for some reason is posted incorrectly. We are testing a product fix and hopefully all will return to normal. [...] 24146 45 25_Re: Question on Utilities14_Massimo Scarpa16_mscarpa@CESVE.IT31_Wed, 26 Jan 2000 17:18:51 +0100384_us-ascii I used 3rd party utility many years ago. They were good tools but they became too expensive

and their cost increased every year. I was sure that with DB2 version 3 and later versions IBM

utilities would have been similar to that of 3.rd party vendors.

And it was right, because I started to use IBM reorg because the 3rd party utility could't reorg [...] 24192 70 25_Re: Question on Utilities20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 26 Jan 2000 11:23:35 -0500567_- Infact once we were evaluating 3rd party tool Vs DB2 utilities. The 3rd party utilities provides so many features (still not available with DB2 utility) that we found difficult to replace just for saving few bucks.

On the negative side of 3rd party utilities - CPU gain is not that much with 3rd part tool as DB2 utilities are becoming faster version by version. Few DB2 secrets like drain & claim etc are not available to 3rd party tool and they have to rely on workaround ways to do the same. This is problematic sometimes when you find utility stops [...] 24263 70 25_Re: Question on Utilities13_Marcus Davage29_Marcus.Davage@LLOYDSTSB.CO.UK31_Wed, 26 Jan 2000 16:25:16 -0000325_iso-8859-1 I used to work somewhere where only IBM utilities were used, and where they lacked, we wrote our own stuff. We could only afford the sales brouchures and getting the salesmen to take us out to lunch. Now, we use everything by everyone, and it's all good. Consequently, we don't get any free lunches any more. [...] 24334 55 23_Re: SYSCOLDIST question20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 26 Jan 2000 11:30:16 -0500367_- >> What process is impacted by having invalid stats posted in >> SYSCOLDIST?

Static and Dynamic SQLs with literals in the WHERE clause are prone to choose wrong access path. Filter factor is calculated based on the coldist statistics (1/frequencyf). If you have static SQL with host variables then only REOPT(VARS) bind option will make use of coldist. [...] 24390 122 25_Re: Question on Utilities18_frank stadankowitz23_fstadankowitz@YAHOO.COM31_Wed, 26 Jan 2000 08:45:44 -0800375_us-ascii Thanks for your response. I'm sorry to hear about your lunching situation but if you pretend to go to the competitor, I'm sure someone will break out the credit cards.

Did you do much comparison shopping when looking at the tools? Does the Platinum / CA situation make you worry at all? I have heard horror stories about the numbers that are left there. [...] 24513 80 23_Re: SYSCOLDIST question14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Thu, 27 Jan 2000 03:35:12 +1000354_us-ascii Multi-column cardinalities (type 'C') are used with host variables.

If the SYSCOLDIST Type 'C' or 'F' are populated with invalid data in COLGROUPCOLNO column, DB2 is known to have experienced very serious problems to the point of almost a DB2 crash.

We have Health Check SQL for SYSCOLDIST now, to ensure not totally corrupt. [...] 24594 200 32_Re: Using CAF for batch programs11_GOSE PAUL M17_pmgose@SRPNET.COM31_Wed, 26 Jan 2000 09:52:05 -0700490_ISO-8859-1 Thanks to all who responded! Based on the replies it's clear to me that we should taker a closer look at the expected benefits before making a change.

From the description of the implicit CAF attach in the Application Programming and SQL Guide, it sounds as though you need to have plans created for each DBRM that would make the initial SQL call in batch runs. We use plans based on applications so this would greatly increase the number of plans we would maintain. [...] 24795 39 21_Runtime -818 response0_24_lightsey@ITS.STATE.MS.US31_Wed, 26 Jan 2000 11:03:08 -0600544_us-ascii My user agency is getting a -818 during an Apply process. We rebound the packages for Apply , tried again, but still get the -818. This started Monday am after putting maintenance on db2 v5.1 and apply/capture on os390 to bring it to 9912. Attempting to bind apply gave a -4930 response and we had to back off the apply fixes. Bind of apply after this backout claims to have worked, and the timestamps in syspackage on os390 and hp box do match up exactly. The apply task runs on os390, connects to udb 5.2 level 8 on HP-UX which [...] 24835 33 20_When is big too big?11_Robert Jans21_Jans.Robert@AMSTR.COM31_Wed, 26 Jan 2000 10:27:00 -0700454_- Hi, DB2ers! We've been discussing this here for a few days now and we just don't know the answers....... Here's the situation: we have a table defined as large with 255 partitions. Number of partitions in active use will be 16 at any time for approximately 60GB. The partitioning key is by week. There are 7 NPI's on this. We're trying to use CA/Platinum's Rapid Reorg on this. There are issues with this which we're working with CA on resolving. [...] 24869 25 34_DB2 V6 to DB2 V5 Fallback - Beware12_Paul Packham28_packhamp@IT.POSTOFFICE.CO.UK31_Wed, 26 Jan 2000 17:26:55 -0000385_iso-8859-1 Hi,

A note of caution !

We have just discovered following a test of our fallback procedures from DB2 v6.1 to v5.1 that any rows loaded into sysstrings as part of the v6 upgrade, must be removed in the fallback to v5 process. Otherwise you could receive either DSNU299I or Sqlcode -872 errors when processing ASCII data on S/390. This is not documentated. [...] 24895 70 24_Re: When is big too big?14_Philip Gunning20_pgunning@BOSCOVS.COM31_Wed, 26 Jan 2000 12:46:43 -0500515_us-ascii Robert, We have a similar situation and just created a VLDB with 53 partitions. You have an object that is manageable. We had a 60GB table and could not do reorgs on it -- not enough time or cycles to reorg it. That's why we went (it's also partitioned by week) VLDB. We have another one that is 255 partitions and find it to be very manageable. As a single large table, we could back it up and that was about it. Recovering the whole TS took a long time so we had to be real careful with changes. We [...] 24966 38 19_Re: Cognos with DB20_15_leon@CA.IBM.COM31_Wed, 26 Jan 2000 10:56:37 -0500439_us-ascii If you are deploying Cognos products on a server then the only DB2 Connect products that will give you the proper license are DB2 Connect Enterprise Edition and DB2 Connect Unlimited Edition.

Leon Katsnelson, DB2 Connect Development Manager mailto:leon@ca.ibm.com



Shauna Hadden on 01/25/2000 10:38:19 AM

Please respond to DB2 Data Base Discussion List [...] 25005 92 24_Re: When is big too big?12_14_paulw@ENET.COM31_Wed, 26 Jan 2000 10:01:42 -0800644_- 255 partitions in a Large TS?

Excuse me, what am I missing?

I thought number of partitions was limited to 254.

Paul Wendt









MIME:pgunning@BOSCOVS.COM 01/26/00 09:44 AM

To: DB2-L@RYCI.COM @ INTERNET cc: (bcc: Paul Wendt/E-Net Corporation) Subject: Re: When is big too big?

Robert, We have a similar situation and just created a VLDB with 53 partitions. You have an object that is manageable. We had a 60GB table and could not do reorgs on it -- not enough time or cycles to reorg it. That's why we went (it's also partitioned by week) VLDB. We have another one that [...] 25098 119 50_Re: Connect to database takes very long / Activate11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Wed, 26 Jan 2000 13:18:39 -0500930_iso-8859-1 Sure. Activate "simulates" a continuous connection to the database so that subsequent users/applications that connect do not experience delay of being first to connect.

Creative DB2 Admins will not only activate a database, but perhaps also run some SQLs to seed the bufferpools.

Hope this helps, Scott

<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><> Scott R. Hayes, President * IBM GOLD Consultant * Database-GUYS Inc. Pager: http://www.database-guys.com/pgform.shtml Consulting and Performance Management Tools for DB2 Universal Database Check out the Database-GUYS(TM) at http://www.database-guys.com/ <><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><> -----Original Message----- From: Lynne Flatley Newsgroups: bit.listserv.db2-l To: DB2-L@RYCI.COM Date: Wednesday, January 26, 2000 10:36 AM Subject: Re: Connect to database [...] 25218 29 22_DB2 V5 , VS COBOL-II ?19_kishore erukulapati21_kishore_e@HOTMAIL.COM29_Wed, 26 Jan 2000 10:22:16 PST513_- Hi , One of the fields of table is amount field which is defined as Decimal(18,3) . We sum all amount rows and store the sum in another which is also defined as decimal(18,3) . DB2 SUM function is used to get the sum . COBOL host variable to store the sum is defined as S9(15)V9(3) COMP-3 . We have received sqlcode = -304 as sum exceeded the limit on one day . We have decided to increase the field definition of SUM to DECIMAL(31,3) ( This is the max. length allowed in DB2 ) We tried to generate DCLGEN [...] 25248 137 50_Re: Connect to database takes very long / Activate13_Lynne Flatley17_LFlatley@NEFN.COM31_Wed, 26 Jan 2000 13:22:51 -0500628_iso-8859-1 Thanks! I'd like to automate the activate command so that it is done whenever the server is rebooted. How would I do that? By the way, this would be on NT if it makes a difference.

> -----Original Message----- > From: Scott Hayes [SMTP:faster.databases@DATABASE-GUYS.COM] > Sent: Wednesday, January 26, 2000 1:19 PM > To: DB2-L@RYCI.COM > Subject: Re: Connect to database takes very long / Activate > > Sure. Activate "simulates" a continuous connection to the > database so that subsequent users/applications that connect > do not experience delay of being first to connect. > > Creative DB2 Admins will [...] 25386 151 23_Re: SYSCOLDIST question10_DANIEL CYR19_DCYR@EMAIL.USPS.GOV31_Wed, 26 Jan 2000 13:24:08 -0600341_iso-8859-1 All,

I should have included the stats from SYSCOLDIST. Please NOTE that the CARDF is set to 1, NUMCOLUMNS is always set to 1, and FREQUENCYF is set to zeros. The table (DLVRY_CNFRMN_T) below has 98 million rows stored in it.

Yes we DO have dynamic WEB queries that access data.

CA/Platinum PDA settings: [...] 25538 38 17_Re: CDB Utilities13_John Arbogast16_jfarbo@YAHOO.COM31_Wed, 26 Jan 2000 10:41:28 -0800669_us-ascii We use BMC and have no complaints. They have an excellent support area.

--- frank stadankowitz wrote: > I am looking for DB2 utilities and have some > interest > in CDB. The other players are CA (Platinum) and > BMC. > I have heard good things about CDB and they have > always seemed to be the fastest utilities. Any > opinions? > __________________________________________________ > Do You Yahoo!? > Talk to your friends online with Yahoo! Messenger. > http://im.yahoo.com > > ================================================ > To change your subscription options or to cancel > your subscription visit the DB2-L webpage [...] 25577 31 19_Re: indoubt threads13_John Arbogast16_jfarbo@YAHOO.COM31_Wed, 26 Jan 2000 10:44:13 -0800693_us-ascii yes, we ended up using reset with ipaddr(addr)force. Any ideas on how to find out what caused the indoubt?

--- John Rosser wrote: > Have you tried the -RESET INDOUBT command? See > Command Ref for more info. > Regards, > John Rosser > Duke Energy Corporation > > ================================================ > 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. > __________________________________________________ Do You Yahoo!? Talk to your friends online with Yahoo! Messenger. http://im.yahoo.com [...] 25609 23 27_Re: Use of Identity Columns12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 26 Jan 2000 12:54:21 -0600533_- To get the same timestamp, applications need to be running at the same microsecond. So my first order of magnitude estimate for duplicates would be the insert rate divided by 1 million. So for example, if we are inserting 10,000 a second, then about 1% of the inserts are likely to be duplicates. Tha typical batching of inserts makes this perhaps another order of magnitude higher, but if inserts take a millisecond, it seems as though the percentage of duplicates should be pretty low. I'd be interested in a reality check. [...] 25633 13 27_Re: Use of Identity Columns12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 26 Jan 2000 12:56:08 -0600349_- You're correct in that the plumbing is needed for both, but some of the work is also unique.

Roger

================================================ 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. 25647 115 24_Re: When is big too big?14_Philip Gunning20_pgunning@BOSCOVS.COM31_Wed, 26 Jan 2000 14:13:19 -0500384_us-ascii ok, 254.

Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert -- DB2 UDB DRDA IBM Cetfified Solutions Expert -- CICS TS Assoc List Owner DB2-L

-----Original Message----- From: [SMTP:paulw@ENET.COM] Sent: Wednesday, January 26, 2000 1:02 PM To: DB2-L@RYCI.COM Subject: Re: When is big too big?

255 partitions in a Large TS? [...] 25763 37 27_VSAM delete of DATA portion13_Joe Abbagnaro25_Joe_Abbagnaro@PRAXAIR.COM31_Wed, 26 Jan 2000 14:08:36 -0500283_us-ascii Hi,

I have just the data portion of a DB2 VSAM file that is stuck on a pack. The cluster is gone. I tried 3.4 with Volser information , I tried Fileaid and I tried IDCAMS delete but can not get rid of the data. The cluster is no where to be found on the system. [...] 25801 31 17_Re: CDB Utilities7_Amy Tam20_atam@PAINEWEBBER.COM31_Wed, 26 Jan 2000 13:43:29 -0500409_iso-8859-1 We are also evaluating the CDB Online Reorg and Romote Image copy products. I can forward to you the feedbacks that other people have on the product. Please keep me posted on how's your testing go. I will do the same.

-----Original Message----- From: frank stadankowitz [mailto:fstadankowitz@YAHOO.COM] Sent: Tuesday, January 25, 2000 12:01 PM To: DB2-L@RYCI.COM Subject: CDB Utilities [...] 25833 58 31_Re: VSAM delete of DATA portion18_Erringer, Keith M.24_Keith.Erringer@ALCOA.COM31_Wed, 26 Jan 2000 14:29:44 -0500564_- Try

> DELETE DB2Q.DSNDBD.DSNDB07.DSN4K01.I0001.A001 - > FILE(DD1) VVR > Keith.

> ---------- > From: Joe Abbagnaro[SMTP:Joe_Abbagnaro@PRAXAIR.COM] > Reply To: DB2 Data Base Discussion List > Sent: Wednesday, January 26, 2000 2:08 PM > To: DB2-L@RYCI.COM > Subject: VSAM delete of DATA portion > > Hi, > > I have just the data portion of a DB2 VSAM file that is stuck on a pack. > The cluster is gone. I tried 3.4 with Volser information , I tried Fileaid > and I tried IDCAMS delete but can not get rid of the data. The cluster is > no > where [...] 25892 55 27_Re: Use of Identity Columns14_O'Conner, John26_john.oconner@COURTS.WA.GOV31_Wed, 26 Jan 2000 11:34:57 -0800571_iso-8859-1 I've mentioned before that our shop (db2 v5.1, os/390 2.7) has an assembler routine that gets a unique value from the system clock. I've been told that this is guarenteed unique. Currently, we call the assembler routine and use the returned value in parent tables and in any children as required. When we move to db2 v6.1, the assembler routine could be implemented as a user defined function. If we wanted to use stored procedures when we insert parent rows, the SP could call the UDF, insert the row, and return the id to the caller. This SP would also [...] 25948 62 31_Re: VSAM delete of DATA portion9_Birk, Tim17_BirkT@DIEBOLD.COM31_Wed, 26 Jan 2000 14:35:44 -0500522_iso-8859-1 Joe,

Try this: //STEP1 EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //DD01 DD UNIT=SYSDA,VOL=SER=LMDB2L,DISP=SHR //SYSIN DD * DELETE (DELETE DB2Q.DSNDBD.DSNDB07.DSN4K01.I0001.A001) FILE(DD01) - CATALOG(cccc) VVR

where cccc is the user catalog that DB2Q is an alias for. Good luck.

Tim Birk Diebold, Inc.

-----Original Message----- From: Joe Abbagnaro [mailto:Joe_Abbagnaro@PRAXAIR.COM] Sent: Wednesday, January 26, 2000 2:09 PM To: DB2-L@RYCI.COM Subject: VSAM delete of DATA portion [...] 26011 68 31_Re: VSAM delete of DATA portion13_Moss, William15_MossW@AETNA.COM31_Wed, 26 Jan 2000 14:38:06 -0500545_- Joe,

Try this.

Bill Moss Aetna/US Healthcare



//******************************************************************** //* NOTE: USE VVR TO DELETE VSAM D OBJECT THAT HAS NO C OBJECT ** //* USE DD1 WITH VOL=SER IF OBJECT IS NOT CATALOGUED ** //******************************************************************** //STEP1 EXEC PGM=IDCAMS //DD1 DD DSN=L198.DSNDBD.TST99.TST99.I0001.A016, // VOL=SER=OSM8CD,UNIT=SYSDA,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSIN DD * DEL L198.DSNDBD.TST99.TST99.I0001.A016 FILE(DD1) VVR [...] 26080 86 31_Re: VSAM delete of DATA portion11_Thomas Bird21_tbird@TJBVENTURES.COM31_Wed, 26 Jan 2000 12:04:13 -0800572_us-ascii Joe,

The age-old problem of VSAM cluster and component mismatches. No VTOC zapping needed anymore. Add VVR to the DELETE. Here's an example I use:

//STPAMS EXEC PGM=IDCAMS,REGION=4M //SYSPRINT DD SYSOUT=* //VDEV001 DD VOL=SER=DEV001,UNIT=9345,DISP=OLD //VDEV002 DD VOL=SER=DEV002,UNIT=9345,DISP=OLD //SYSIN DD * DELETE - MIKE.SYSTEST.T00828B.SAMDBTE9.INDEX - FILE(VDEV002) - VVR 7

Regards,

T. Bird President TJB Ventures, Inc. Voice: (707) 226-1100 Fax: (707) 226-1188 Email: tbird@tjbventures.com Web: http://www.tjbventures.com [...] 26167 69 31_Re: VSAM delete of DATA portion12_HARBRY ARIZA20_harbry_a@HOTMAIL.COM29_Wed, 26 Jan 2000 11:53:44 PST656_- Joe:

First execute a listcat on the vsam file to see in catalog is connected and then execute this.

//DELETE EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=R //* //SYSIN DD * DELETE - CFTCT.FAC.AMSI.CLIENTE - PURGE - ERASE - CATALOG(CATALOG.SHAREMVS.MVSESA) //*



>From: Joe Abbagnaro >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: VSAM delete of DATA portion >Date: Wed, 26 Jan 2000 14:08:36 -0500 > >Hi, > >I have just the data portion of a DB2 VSAM file that is stuck on a pack. >The cluster is gone. I tried 3.4 with Volser information , I tried Fileaid >and [...] 26237 81 27_Re: Use of Identity Columns14_Wayne Driscoll19_wdriscoll@QUEST.COM31_Wed, 26 Jan 2000 12:12:16 -0800627_iso-8859-1 This approach, depending on how it gets implemented, could still, in certain situations, generate duplicates. If the assembler routines gets the time via a STCK (Store Clock) instruction the time returned will be unique in that OS/390 system. However, if this application runs in a Data Sharing environment, in order to ensure uniqueness the assembler routine should be changed to issue the STCKSYNC macro, rather than the STCK instruction. The STCKSYNC macro will obtain the time from an ETR (sysplex timer) if one is present, and it will be unique across all members of the Data Sharing Group. Wayne Driscoll [...] 26319 14 21_delete subscribe list17_GHI - Jerry Liang14_jliang@GHI.COM31_Wed, 26 Jan 2000 14:56:49 -0500289_us-ascii QUIET DELETE DB2-L jliang@pop3.ghi.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. 26334 15 6_delete17_GHI - Jerry Liang14_jliang@GHI.COM31_Wed, 26 Jan 2000 15:00:03 -0500311_us-ascii DELETE DB2-L jliang@ghi.com DELETE DB2-L jliang@pop3.ghi.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. 26350 14 27_Vsam Delete of Data portion13_Joe Abbagnaro25_Joe_Abbagnaro@PRAXAIR.COM31_Wed, 26 Jan 2000 15:24:29 -0500324_us-ascii The delete using the VVR option worked fine ..

Thanks everyone....

================================================ 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. 26365 110 24_Re: When is big too big?0_26_jack.oconnell@MCMASTER.COM31_Wed, 26 Jan 2000 14:59:06 -0600605_us-ascii Paul your message came with the domain name of mcmaster.com

I am concerned that you are using our domain name. Who has given you permission to do that? I am concerned about misrepresentation. please reply as I will be pursuing this.







At 10:01 AM 1/26/00 -0800, you wrote: >255 partitions in a Large TS? > >Excuse me, what am I missing? > >I thought number of partitions was limited >to 254. > >Paul Wendt > > > > > > MIME:pgunning@BOSCOVS.COM > 01/26/00 09:44 AM > > To: DB2-L@RYCI.COM @ INTERNET > cc: (bcc: Paul Wendt/E-Net Corporation) > Subject: Re: [...] 26476 15 13_Oracle Vs DB222_Ramesh Balasubramanyan15_RAMBAL@SAIF.COM31_Wed, 26 Jan 2000 12:55:40 -0800394_- Can a wisebody suggest a book or a website that would be useful for a DB2 DBA wanting a quick grounding on Oracle Administration? Thanks ! Ramesh Bala

================================================ 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. 26492 129 55_Re: Listing Databases for an Instance (UDB v6.1 on AIX)0_22_BILL_GALLAGHER@PHL.COM31_Wed, 26 Jan 2000 16:13:06 -0500375_us-ascii Scott,

This didn't help. The "list database directory" command is showing ALL local databases on the machine, not just the ones defined for the instance I am attached to. We have four separate instances on this box, each with their own database(s). I want to be able to only see the databases that are defined in the instance I am currently attached to. [...] 26622 28 74_Tuning EDM Pool size re. dynamic SQL cache / do we have a problem or not ?11_Kluth, Jeff33_jeff.kluth@EXCHANGE.NORDSTROM.COM31_Wed, 26 Jan 2000 14:05:42 -0700490_- > We currently have our EDM Pool sized at 4,000 pages and we also have > dynamic SQL cache active. Our new monitor is giving us all kinds of > warnings that our EDM pool is full, yet it seems are LOADs from DASD for > DBDs, CTs, and PTs are low. > > In the past (like V4), when are EDM pool space has run low, applications > started getting -904s because they couldn't load stuff into the EDM pool. > Now it seems like our EDM pool is full, possibly with cached SQL ?, but no > apps [...] 26651 21 78_Re: Tuning EDM Pool size re. dynamic SQL cache / do we have a problem or not ?0_23_pjackson@SUMMITBANK.COM31_Wed, 26 Jan 2000 16:25:00 -0500338_us-ascii Jeff,

We also use Dynamic SQL caching - our EDM pool is about 4000 pages and our performance monitor (Insight) tells me that we are using 99% of the pool - but a display (in Insight) of the EDM pool indicates zero failures and that we are using 93% of the pool for Dynamic SQL caching - I don't think it's a problem. [...] 26673 229 32_Re: Using CAF for batch programs14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU31_Wed, 26 Jan 2000 16:36:45 -0500596_us-ascii From what I've heard and read, efficiency is a minor consideration for using CAF. The main advantages are control, flexibility, and elegance. A program that uses CAF to connect to DB2: Will always connect at a predictable point in processing (not somewhere in the middle of the first execution of some loop where it happens to make the first SQL call) under a controllable TCB. Will always connect to and disconnect from DB2 in the same way even in the null case where the main processing loop is not executed. Can dynamically determine the timing and parameters of the connection. [...] 26903 58 79_Re: Tuning EDM Pool size re. dynamic SQL cache / do we have a pro blem or not ?12_Vernon, John27_John.Vernon@TWC.STATE.TX.US31_Wed, 26 Jan 2000 15:51:20 -0600440_- What monitor are you utilizing, and what(if any) edm pool full threshold is set internally in the monitor?

John Vernon DB2 System Administrator Texas WorkForce Commission 101 East 15th Street Austin, Texas 78778 Vice-President, Heart of Texas DB2 Users Group Email: john.vernon@twc.state.tx.us jvernonrr@aol.com Phone: 512-936-3170 Fax: 512-936-3190 "Ability may get you to the top, but it takes character to keep you there." [...] 26962 61 31_Re: V5 & P97D CA/Platinum tools10_Paul Fegan23_Paul.Fegan@CITEC.COM.AU31_Thu, 27 Jan 2000 07:50:05 +1000406_US-ASCII George, We've been using DB2 V5 with the P97D tapes for about 8 months and we haven't come across any major problems that I can remember. We only use Database Analyser and Log Analyser so maybe this isn't a good enough cross section of the products. According to our Platinum/CA reps The P97G tapes are V6 tollerant so if you do upgrade to P97G you should be ok for the V6 migration as well. [...] 27024 274 32_Re: Using CAF for batch programs20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 26 Jan 2000 16:49:35 -0500379_iso-8859-1 Harvey

Everything is not so rosy about CAF. One can't use CAF while running under DSN and vice-versa. If a batch job which does heavy DB2 processing under only one subsystem then CAF does not buy anything because with the use of packages, the need for multiple plans withered. So unless one wants to refer to multiple subsytem, DSN provides all the needs. [...] 27299 27 26_Online Reorg and Map Table0_22_BILL_GALLAGHER@PHL.COM31_Wed, 26 Jan 2000 16:54:34 -0500564_us-ascii Hi. I've search the archives but couldn't find what I was looking for, so I'll ask here.

I seem to remember somebody posting a message to this list some time ago which seemed to indicate that online reorg (shrlevel change) only really used the index for the map table, and not the map table itself, and implied that the tablespace where the map table was defined could be very small. Does this ring a bell with anybody? We've defined a very large tablespace for our map table (over 11k tracks), and if this isn't necessary I'd like to free up [...] 27327 336 32_Re: Using CAF for batch programs14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU31_Wed, 26 Jan 2000 17:09:59 -0500595_us-ascii I'm was intending to agree that if Paul is simply trying to gain efficiency, there's not much point in switching.

We use CAF extensively for the other reason indicated, mostly to insulate applications (usually, but not always, batch applications) from DB2 considerations. Program logic and record formats can be changed, and sometimes entirely new application programs can be written without bothering the DBAs. We also used this technique to convert some legacy programs from BDAM or VSAM to DB2 without even recompiling them! CAF has been a major plus for us in this way. [...] 27664 373 32_Re: Using CAF for batch programs20_Pillay, Venkat (PCA)20_venkat_pillay@ML.COM31_Wed, 26 Jan 2000 18:02:50 -0500424_iso-8859-1 One attachment facility can't start another. Can't use CAF connection under DSN subtask or can't connect to RRSAF under the same address space which has CAF thread.

I guess this must be due to the way attachment facility connects to DB2. The address space is initialized as user of DB2 during the first task trying to connect and then onwards same address space can't use other attachment facilities. [...] 28038 12 20_Re: ORACLE LIST-SERV0_20_bjnigh@HOUSEHOLD.COM31_Wed, 26 Jan 2000 15:03:37 -0800296_us-ascii Do you know if there is a SYBASE list-server?

================================================ 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. 28051 151 25_Re: Question on Utilities11_Ron Johnson25_rjohnson@DATA-TRONICS.COM31_Wed, 26 Jan 2000 17:49:29 -0600464_iso-8859-1 I tend to think the utilities that come with DB2 are adequate for most situations. We have several CA/Platinum utilities. I think we can probably get by without REORG and COPY. I am going to try to do that the next time our contract comes up. We are too dependant on Fastunload and Database Analyzer to consider doing without those. We have never had their LOAD utility. 24 x 7 considerations are also handled well by the IBM utilities these days. [...] 28203 249 27_Re: Use of Identity Columns25_Robinson, Peter [IBM GSA]40_Peter.J.Robinson@CORPMAIL.TELSTRA.COM.AU31_Thu, 27 Jan 2000 11:04:21 +1100463_iso-8859-1 Watch out for any time changes like switching the clock back and forth for daylight savings (sometimes called summer time). Here, when we switch to daylight savings it is ok because at midnight, the clock is set forward to 1am. On the way back again, however, it can be a problem because it goes from 1am back to midnight again and there is a possibility of duplicates for that hour. We have to switch everything off for the hour just to be sure. [...] 28453 499 50_Re: Connect to database takes very long / Activate11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU31_Thu, 27 Jan 2000 11:04:51 +1100538_iso-8859-1 Hi Lynne, One option is to put a DOS script (which executes the activate database command) in the startup folder for a user. Then, enable automatic logon in Windows NT. Unfortunately, this leaves the user logged on at that machine, so not for everybody.

Otherwise, maybe write an application program, which would be set up as an NT service, to start the db2 instance and activate the database. The NT service can be set up to be started at boot time. Unfortunately, requires an NT programmer, so not for everybody. [...] 28953 382 23_Problem: UDB 5.2 hanged11_Roland Chua32_rolandchua@SINGAPOREEXCHANGE.COM31_Thu, 27 Jan 2000 08:24:46 +0800659_gb2312 We are running UDB 5.2 level 9074 on NT. This week our system hanged twice. We checked the Db2diag.log and did not know how to interpret it. Could anyone help. Thanks in advance.



Db2diag.log



2000-01-25-16.15.07.765000 Instance:DB2 Node:000 PID:147(db2syscs.exe) TID:1393 Appid:*LOCAL.DB2.000128045418 base_sys_utilities sqleagnt_sigsegvh Probe:10 Database:DB2NTP

Error in agent servicing application with coor_node:0000 ..



2000-01-25-16.15.07.890000 Instance:DB2 Node:000 PID:147(db2syscs.exe) TID:1393 Appid:*LOCAL.DB2.000128045418 base_sys_utilities sqleagnt_sigsegvh Probe:10 Database:DB2NTP [...] 29336 195 50_Re: Connect to database takes very long / Activate0_29_Greg.Palgrave@BANKWEST.COM.AU31_Thu, 27 Jan 2000 08:19:18 +0800437_us-ascii Hi Lynne,

Just create a script in the Script Centre:

ACTIVATE DB dbname

Then schedule it to run daily/weekly/whatever in the Journal.

Catch is you need to DEACTIVATE the database to do offline backups - FORCE APPLICATION ALL does *not* deactivate. LIST APPLICATION shows *no users*, but you get told "database in use". We did a corresponding deactivate script and scheduled prior to the backup. [...] 29532 539 25_Re: Question on Utilities10_Wang.James24_Wang.James@AAA-CALIF.COM31_Wed, 26 Jan 2000 16:31:06 -0800569_iso-8859-1 I agree with Ron that IBM has improved quite a bit of their DB2 utilities to eliminate the need for most of 3rd parties DB2 utilities.

We used to have Platinum utilities but they are on their way out.

But we purchased a tool called DB2I2 by JRH GoldenState Software, Inc., www.db2i2.com , which improves our DBAs' productivity tremendously in activities such as DB2 catalog query, data base management, data base migration, data base recovery, intelligent reorg, data base space management, performance tuning, etc. [...] 30072 49 31_Re: Reducing extents on an NPI?11_Carl Parisi16_parisi@FLASH.NET31_Wed, 26 Jan 2000 19:00:32 -0800296_us-ascii How bout an online reorg that will reorg all TS's, IX's, and the NPI (instead of just updating it), and w/out locking you (the user) out of your logical partitions? Your performance comes from the NPI's, they need to be reorged, or it's like rearranging deck chairs on the TITANIC. [...] 30122 32 31_regarding partition tablespace.17_ravi kumar hassan18_ravibh@HOTMAIL.COM29_Wed, 26 Jan 2000 18:33:22 PST569_- Hi Everybody,

i have a question on partiotion tablespaces.

Actually in our shop they are using partion tablespace. these partition tablespaces are 32 parts and each partions are 2GB. there are some jobs which will sends the data to partition tablespaces but these jobs will not send certain no. records to each partitions. so my question is do they need to distribute certain no. of records depending upon spaces among the partitions or db2 will take care of distributing no. of records to each partitions. or DBA has to take care of this and how? [...] 30155 65 35_Re: regarding partition tablespace.0_24_ssethi@LOT.TATASTEEL.COM29_Thu, 27 Jan 2000 02:56:47 GMT436_us-ascii I think DB2 will only distribute the rows according to the partitioning key on the partition table.if the job is not sending the uniform distribution of the partition key then u should alter the partitioning key range that too if u are using DB2 V6 . What DBA can do is to look at the SYSIBM.SYSTABLEPART and decide whether uniform distribution of the records are there or not . I hope i understand ur question correctly. [...] 30221 258 25_Re: Question on Utilities0_24_ssethi@LOT.TATASTEEL.COM29_Thu, 27 Jan 2000 03:00:08 GMT394_iso-8859-1 I think DB2 utilities are good enough unless one is facing response problem very frequently .otherwise all DB2 utilities can be automated and run in auto schedular .The output of the all utilities are very user friendly and any errors can be corrected. But for very large amount of data and very less effordable downtime faster tools are good ,however, i have never used those. [...] 30480 44 17_Re: Oracle Vs DB212_Price, David22_David.Price@SCHWAB.COM31_Wed, 26 Jan 2000 19:45:05 -0800449_- There are two good books: 1. ORACLE8 The Complete Reference ($41.99 at Amazon) 2. ORACLE8 DBA Handbook ($31.49 at Amazon)

Both books are published by Oracle Press



Dave Price



All email sent to or from this address will be received or otherwise recorded by the Charles Schwab corporate email system and is subject to archival, monitoring or review by, and or disclosure to, someone other than the recipient [...] 30525 31 40_DB2 active logs and IBM Raid-1 type DASD13_David Chapman24_david_chapman@CGU.COM.AU31_Thu, 27 Jan 2000 14:40:00 +1100420_iso-8859-1 Dear DB2 List,

My sysprog wants to change DB2 to write only single active logs. She argues that since we are using IBM mirrored Raid (Type-1) we don’t need dual active logs. She believes it’s just a waste of DASD.

I’ve tried to explain that IBM and others such as Joel Goldstein recommend dual logging, however, she wants to know what dual logging will give us that mirrored raid will not. [...] 30557 136 44_Re: DB2 active logs and IBM Raid-1 type DASD0_29_Greg.Palgrave@BANKWEST.COM.AU31_Thu, 27 Jan 2000 13:38:33 +0800379_us-ascii David,

Another point to argue is that with single logging, if/when your logs fill up DB2 *stops*. If you have dual logging, it will at least switch to single-log mode which buys you a little time for the archive process to catch up.

Also, if the disk controller fails, you may lose access to your logs anyway. [...] 30694 16 24_Re: When is big too big?13_Dan S Brogdon22_Dan.S.Brogdon@AEXP.COM31_Wed, 26 Jan 2000 17:08:55 -0700442_- I would start looking at summary tables, or tables that contain subsets of the data, or an archival process. Some of these techniques may require join views or code changes but would provide relief.

================================================ 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. 30711 31 25_Re: Question on Utilities14_Massimo Scarpa16_mscarpa@CESVE.IT31_Thu, 27 Jan 2000 08:59:10 +0100396_us-ascii We did some comparisons between IBM & vendors utilities, but it was many years ago. Vendor

utilities were better, but with some costs. Now I think the difference is in some details and features, not in CPU/sort/elapsed of an utility. And I think this is the important thing.

Try to obtain a good (and clear) contract for many years. Some vendors adore to rise utility [...] 30743 33 44_Re: DB2 active logs and IBM Raid-1 type DASD14_Massimo Scarpa16_mscarpa@CESVE.IT31_Thu, 27 Jan 2000 09:14:53 +0100334_us-ascii AAARRRGGGHHHH !!!!

Don't do it !!! In a test environment we had 1 active log mirrored by another device. It was an IBM

3390-3. A day a DASD become FENCED with PINNED DATA in cache and, I don't know how, I

was out and in another office, all was stopped. May be that DASD manager did some errors, but [...] 30777 30 27_Locks on DBD and Tablespace0_24_ssethi@LOT.TATASTEEL.COM29_Thu, 27 Jan 2000 08:44:02 GMT567_us-ascii Hi All, When we execute a DDL (say , to create table in a tablespace) then DBD for corresponding database is locked and when u issue the DB2 Command to -DISPLAY DB(dbname) SPACE(tsname) then DB2 shows that tablespace is also locked in the exclusive mode .My question is why should DB2 exclusively lock the tablespace if DBD is locked.I think this is unneccessary use of the locks and the CPU.As i understand no objects can be accessed via. DB2 without DBD gets loaded into the EDM Pool and if at all somebody is trying to access the VSAM linear dataset [...] 30808 63 78_Re: Tuning EDM Pool size re. dynamic SQL cache / do we have a problem or not ?13_Adrian Savory24_adrian.savory@ZURICH.COM31_Thu, 27 Jan 2000 08:50:16 +0000340_us-ascii Jeff,

I don't believe you have a problem as the dynamic statement caching is using as much space in the EDM pool as possible, but this is stealable for DBDs, etc. If your EDM pool is too small the first indicator would probably be a poor hit ratio on the dynamically cached statements (i.e. low % of prepares avoided). [...] 30872 16 18_How do I sign-off?21_Vijayalakshmi Ganesan30_viji_ganesan@CHENNAI.TCS.CO.IN31_Thu, 27 Jan 2000 14:18:09 +0530342_us-ascii Hi all, Can someone please let me know how to sign-off from the forum.

Regards, Viji

================================================ 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. 30889 134 22_Re: How do I sign-off?0_22_The-Thanh.Luong@AGF.BE31_Thu, 27 Jan 2000 10:09:29 +0100545_- Simply send the command SIGNOFF DB2-L to LISTSERV@ryci.com

See the address at the bottom of your message, everything is explained there. Cheers.

The-Thanh LUONG AGF Belgium Insurance





===== Standard Disclaimer: opinions expressed are strictly my own, etc... =====





-----Original Message----- From: Vijayalakshmi Ganesan [mailto:viji_ganesan@CHENNAI.TCS.CO.IN] Sent: Thursday, January 27, 2000 9:48 AM To: DB2-L@RYCI.COM Subject: How do I sign-off? [...] 31024 72 38_Re: DB2 V6 to DB2 V5 Fallback - Beware9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Thu, 27 Jan 2000 10:40:57 +0000375_us-ascii Pog, Thanks for the warning ! Do you have an example of the job used to delete the rows ? Was this job provided by IBM ? Did IBM provide any other fallback instructions regarding this issue ? Kind Regards, Andy Hunt - Scottish And Southern Energy











Paul Packham on 26/01/2000 17:26:55 [...] 31097 90 38_Re: DB2 V6 to DB2 V5 Fallback - Beware22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 27 Jan 2000 11:20:44 +0000547_ISO-8859-1 oh this is great news .. :-) Leslie



______________________________ Reply Separator _________________________________ Subject: Re: DB2 V6 to DB2 V5 Fallback - Beware Author: Andy Hunt at Internet Date: 1/27/00 10:40 AM



Pog, Thanks for the warning ! Do you have an example of the job used to delete the rows ? Was this job provided by IBM ? Did IBM provide any other fallback instructions regarding this issue ? Kind Regards, Andy Hunt - Scottish And Southern Energy [...] 31188 23 38_Re: DB2 V6 to DB2 V5 Fallback - Beware12_Paul Packham28_packhamp@IT.POSTOFFICE.CO.UK31_Thu, 27 Jan 2000 12:18:17 -0000363_iso-8859-1 Stanley/Andy,

We are re-running our tests & I will let you know if the outcome. But IBM have confirmed through an ETR which we raised with them that the correct action to take is to restore sysstrings back to V5 if it was changed in V6. We used an I/C taken prior to the V6 upgrade. The V6 change was performed as per hold info in UQ31081. [...] 31212 54 44_Re: DB2 active logs and IBM Raid-1 type DASD19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 27 Jan 2000 06:36:29 -0600617_iso-8859-1 David, Whip this on your "?sysprog?". Why do you insist on putting a single point of failure into production DB2? Dual logging on different controllers yields availability. 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. [...] 31267 113 38_Re: DB2 V6 to DB2 V5 Fallback - Beware13_Adrian Savory24_adrian.savory@ZURICH.COM31_Thu, 27 Jan 2000 12:39:07 +0000436_us-ascii I saw that too. The P.O are putting in V6 to trial the new SAP R/3 App Server for OS/390, so the problem is definitely in our area!

What plans have you got for installing V6 now? Doing it in your spare time? :-)









Leslie Pendlebury-Bowe on 27/01/2000 11:20:44

Please respond to DB2 Data Base Discussion List [...] 31381 37 30_3rd party s/w and data sharing0_19_mike.holmans@BT.COM31_Thu, 27 Jan 2000 12:44:56 -0000563_- What are people's experiences with 3rd party tools in a data sharing environment?

Are there products which work fine in non-data sharing but can't cope with data sharing?

In particular, what about performance monitors? Obviously IBM's own DB2PM can do useful things like aggregate traces for the same plan across multiple group members, but can the other monitors support that sort of group-wide function? Are there any better monitors than DB2PM for a data sharing environment? How well do people find that Omegamon copes with data sharing? [...] 31419 22 16_SDSNLOAD Library14_DAVID PETERSEN27_dpetersen@KEYSPANENERGY.COM31_Thu, 27 Jan 2000 07:56:43 -0500532_us-ascii Hi All:

Currently we have the SDSNLOAD dataset as part of our linklist, we are contemplating removing from there and using a steplib statement. I was hoping you could offer some advice about utilizing a linklist or steplib from a performance perspective.

Thanks, Dave

================================================ 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. 31442 136 38_Re: DB2 V6 to DB2 V5 Fallback - Beware22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 27 Jan 2000 13:28:47 +0000450_ISO-8859-1 Ade, doing V6, DS, DR, you name it I'm up to my ears in it .. :-) We are trialing the new Os390 App Server aswell .. I may get in touch with the PO to discuss with them.

Any jobs going with you? Les



______________________________ Reply Separator _________________________________ Subject: Re: DB2 V6 to DB2 V5 Fallback - Beware Author: Adrian Savory at Internet Date: 1/27/00 12:39 PM [...] 31579 24 8_PM tools20_Piedmonte, JoAnnah L31_JoAnnah.Piedmonte@USA.XEROX.COM31_Thu, 27 Jan 2000 08:21:09 -0500350_iso-8859-1 Our DBA group has always used panels written with SAS behind the scenes to track performance of our DB2 tables, etc. The SAS libraries being used were not Y2K compliant and our Software group took them out. There are so many macros to go through that it just seems more hassle to try and change the SAS around to stick to our panels. [...] 31604 199 20_Re: ORACLE LIST-SERV16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Thu, 27 Jan 2000 08:36:24 -0500636_us-ascii



try http://www.pfcguide.com/_newsgroups/group_list.asp or this one from plat db tips







Signing Up for the Sybase Mailing List

Problem: What is the Sybase Internet mailing list and how do I sign up?

Solution: The Sybase Internet mailing list, also known as Sybase-L, is a sort of community bulletin board. You can think of mailing lists as equivalent to a mass mailing. There are around 40,000 mailing lists available on the Internet, and they operate using a list server. A list server is a program that automates mailing list subscription requests and messages. [...] 31804 107 57_Re: Tuning EDM Pool size re. dynamic SQL cache / do we ha22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 27 Jan 2000 13:56:57 +0000418_ISO-8859-1 Jeff

you have 15.6Mb of Edm Pool.

I would be intereste in knowing how effective your global cache is in that EDM pool?

Over maybe a day you can calculate the Global Hit Ratio(effectiveness) by dividing Prepared Statement Match by the sum of Prepared statement Match & Prepared Statement No Match.

This info can be obtained from DB2PM - but can you get it from your monitor? [...] 31912 78 38_Re: DB2 V6 to DB2 V5 Fallback - Beware9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Thu, 27 Jan 2000 14:08:54 +0000397_us-ascii Pul, Ive been looking at UQ31081 (V6) - apar PQ26130. PQ26130 also had a PTF for V5, UQ31080. I presume these PTFs must be loading the same entries into SYSSTRINGS (the HOLDS appear to be identical). If this is true, then I guess you wouldnt have a problem falling back if you had UQ31080 on at V5 (PUT9906 - I think). Maybe you could raise this issue on your ETR and confirm this ? [...] 31991 64 30_Re: Online Reorg and Map Table13_Lockwood Lyon23_Lockwood_Lyon@AMWAY.COM31_Thu, 27 Jan 2000 09:17:00 -0500452_us-ascii You remembered correctly. Our production MAP_TABLE (actually its Tablespace) is allocated with PRIQTY 125, the index with PRIQTY 12500, which suffices for most of the tables I Reorg.

For more details, see Jim Ruddy's presentation at the last IDUG, "DB2 for OS/390 Online Reorg" (presentation E13).

-- Lockwood Lyon Amway Corp









BILL_GALLAGHER@PHL.COM@RYCI.COM> on 01/26/2000 04:54:34 PM [...] 32056 142 24_Re: When is big too big?12_Kirk Hampton16_khampto1@TXU.COM31_Thu, 27 Jan 2000 08:23:35 -0600579_us-ascii Sorry, I just couldn't let this slide... Why should you immediately assume that someone is using your domain name in a malicious manner ? Anyone who has ever worked around computers would know that glitches are possible and look at checking out their own local servers rather than sending out an accusatory and threatening reply that implies legal action. It's people like you who have made our society the litigious one that it has become. What benefit would it be to Paul to offer his comments in a posting which claims to be from some other company than his own [...] 32199 43 12_Re: PM tools14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Thu, 27 Jan 2000 09:26:06 -0500360_ISO-8859-1 JoAnnah, SAS was Y2K compliant. It had to be either your user written code or the files (2 digit yr) taht was not compliant. It shouldn't have taken a good SAS programmer more than 15 minutes to both fix the code AND convert your old files to new ones with a 4 digit year. Sounds like you threw away a working system because somebody was lazy. [...] 32243 91 39_Re: Connect to database takes very long14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 27 Jan 2000 09:34:50 -0500603_iso-8859-1 Scott:

Thank you. You're right - it was the bufferpool size that was causing the connect to take so long.

Thanks, Manas.

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Scott Hayes > Sent: Wednesday, January 26, 2000 9:51 AM > To: DB2-L@RYCI.COM > Subject: Re: Connect to database takes very long > > > For the database with the delayed first connect: > * Can you activate it so that subsequent users do not experience > the delay? > * How many megabytes of primary log space? The first connect > usually has [...] 32335 92 30_Re: Online Reorg and Map Table22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 27 Jan 2000 14:36:52 +0000393_ISO-8859-1 Bill As Lockwood says - you only need a small TS size.

I run 8 parallel reorg streams here and have 8 map tables for them in the same TS .. the index spaces are huge but the TS itself is tinny. I was thinking of taking each table out into its own maptbl tablesapce incase there was any contention when I run so many on-line reorgs .. but I haven't got around to it yet. [...] 32428 21 11_DB2and ZACK12_Mike O'Brien30_michael_obrien_48127@YAHOO.COM31_Thu, 27 Jan 2000 06:45:30 -0800446_us-ascii Is anyone's shop out there using what use to be Platinum's AutoAction automated computer operator software, known as ZACK. If so, do you have any info on trapping archive log messages to generate a job that will create offsite backups? Thanks for any help, have no idea how to use this product. __________________________________________________ Do You Yahoo!? Talk to your friends online with Yahoo! Messenger. http://im.yahoo.com [...] 32450 41 9_REOPTVARS14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Thu, 27 Jan 2000 15:57:48 +0100521_us-ascii Hi fellow DB2'ers,

has someone out there bound packages using reoptvars(yes) and if so what has been the reason to do so and what was the outcome ?

My reason for asking: we have some application designers eagerly waiting to see some packages rebound with reoptvars(yes) and we (the DBAs, that is) are reluctant to comply with their wishes since we have no experience whatsoever in that respect. Has anyone done some benchmark-testing as to what are the pros and cons of using reoptvars(yes) ? [...] 32492 87 9_REOPTVARS14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 28 Jan 2000 02:20:11 +1000366_us-ascii Ruediger,

I have used Report Vars for some OLTP transactions where a long running query (badly performing) has predicates with host variables but a very skewed distribution of data values.

Depending on value of host variables, DB2 needs to choose a different table to visit first in the join. Must go to table doing best filtering first. [...] 32580 38 27_Re: MIGRATING FROM V4 TO V512_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 27 Jan 2000 09:23:00 -0500612_- You didn't list the IRLM loadlib, SDXRRESL. Suggest you check if apply of II10128 changed modules in this lib and copy that lib as well. >>> DB2-L@RYCI.COM@inter2 01/25/00 12:41PM >>> After migrating from v4 to v5 on a "sysprog" LPAR and then being able to fall back to V4 by steplibing the load libs, I attempted to move needed V4 maintenance (required for migration/fallback (APAR II10128) ) to a "development LPAR using the following methodology: Stop DB2 on devl LPAR Free devl load libs from linklist Copy sdsnexit,sdsnload and sdsnlink from stag LPAR to devl LPAR re-assemble DSNZPARM into sdsnexit [...] 32619 58 31_Re: V5 & P97D CA/Platinum tools12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 27 Jan 2000 09:32:00 -0500406_- We have DATABASE ANALYSER BATCH PROCESSOR RC/QUERY RC/MIGRATOR RC/UPDATE PLAN ANALYSER SQL/EASE All on the P97D tape with no reported problems since last summer. The only thing we did was rebind the plans and packages when we went to V5.



>>> DB2-L@RYCI.COM@inter2 01/25/00 05:22AM >>> Is anyone running DB2 V5 with version P97D of platinum which encompasses the following products :- [...] 32678 84 31_Re: V5 & P97D CA/Platinum tools14_george mclaren31_george_mclaren@STANDARDLIFE.COM31_Thu, 27 Jan 2000 15:46:07 -0000696_iso-8859-1 Bob,

Thanks for the reply...didn't you find that you can't display the zparm thru' RC Query ?



-----Original Message----- From: BOB JEANDRON To: db2-l Date: 27 January 2000 15:37 Subject: Re: V5 & P97D CA/Platinum tools



> > >We have >DATABASE ANALYSER >BATCH PROCESSOR >RC/QUERY >RC/MIGRATOR >RC/UPDATE >PLAN ANALYSER >SQL/EASE >All on the P97D tape with no reported problems since last summer. The only >thing we did was rebind the plans and packages when we went to V5. > > >>>> DB2-L@RYCI.COM@inter2 01/25/00 05:22AM >>> >Is anyone running DB2 V5 with version P97D of platinum which encompasses >the [...] 32763 53 30_Locks, concurrency and commits11_Eric Robida22_Eric_Robida@BCBSME.COM31_Thu, 27 Jan 2000 10:42:13 -0500589_us-ascii I have what may be a very basic question. Why did contention occur? Now for the situation. I have a program, with the following bind parameters:

ISOLATION CS ACQUIRE USE RELEASE COMMIT DYNAMICRULES RUN NODEFER PREPARE CACHESIZE 1024 QUALIFIER PRIMARY CURRENTDATA NO DEGREE 1 SQLRULES DB2 DISCONNECT EXPLICIT NOREOPT VARS

All the SQL is static. it opens a cursor with hold for fetch only based on a date in Table A . It then processes every row in the cursor, in this case 333,000 rows. For every row fetched it issues 4 'mass' updates against tables A, B, C, D [...] 32817 24 38_Re: DB2 V6 to DB2 V5 Fallback - Beware0_29_STANLEY_GOODWIN@MECH.DISA.MIL31_Thu, 27 Jan 2000 10:57:16 -0500570_us-ascii Andy, If you review the Program directory it says about having PTF UQ90001 applied and if you review the accompanied APAR and prereqs youwill see that these tolreation ptfs must be applied.





Stan Goodwin DAC MBG DB2 Support

Stanley_goodwin@mech.disa.mil DSN: 430-4335 Outside:- 717-605-4335

================================================ 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. 32842 27 35_sqlcode -180 for invalid date value14_James R. Brown25_brownjr@CI.RICHMOND.VA.US31_Thu, 27 Jan 2000 11:00:55 -0500563_iso-8859-1 Hi all,

Has anyone experienced -180 sqlcode returned when the syntax of the value is correct but the date itself is not valid? One of our developers asked for help identifying an -180 sqlcode during an insert into a table with DATE columns. I saw nothing wrong with the format of the columns but did notice 2 of the date columns had values of 2000-09-31. When he changed the values of these columns to be a valid date, the insert returned a 0 sqlcode. It seems as though a -181 should have been returned for the incorrect values instead of [...] 32870 77 34_Re: Locks, concurrency and commits11_rick creech18_ykcirc@HOTMAIL.COM29_Thu, 27 Jan 2000 08:12:56 PST402_- Eric, We have had a few situations where lock escallation obviously was occurring, but out monitor (TMONDB2) was not indicating it and it had to do with mass deletes. I resolved the issue by changing the tablespaces involved from LOCKSIZE ANY to LOCKSIZE PAGE. I still do not know if the monitor is not picking up the escallation or if IBM is escalating when it should not. Regards, Rick Creech [...] 32948 77 34_Re: Locks, concurrency and commits12_craig patton21_prgpatton@HOTMAIL.COM29_Thu, 27 Jan 2000 08:14:56 PST644_- Eric, The problem (most likely) is with the 'WITH HOLD' parameter on the Cursor. This causes DB2 to HOLD locks ACROSS a cursor COMMIT! It will hold the locks necessary to keep the cursor OPEN and POSITIONED.

HTH, Craig Patton DB2 DBA Contractor



>From: Eric Robida >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Locks, concurrency and commits >Date: Thu, 27 Jan 2000 10:42:13 -0500 > > I have what may be a very basic question. Why did contention occur? >Now for the situation. > I have a program, with the following bind parameters: > >ISOLATION CS [...] 33026 88 31_Re: V5 & P97D CA/Platinum tools12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 27 Jan 2000 10:12:00 -0500398_- Sure can't. This is a function not normally used here.

>>> DB2-L@RYCI.COM@inter2 01/27/00 09:45AM >>> Bob,

Thanks for the reply...didn't you find that you can't display the zparm thru' RC Query ?



-----Original Message----- From: BOB JEANDRON To: db2-l Date: 27 January 2000 15:37 Subject: Re: V5 & P97D CA/Platinum tools [...] 33115 51 35_Re: regarding partition tablespace.16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Thu, 27 Jan 2000 09:26:33 -0700626_- I think your question is will DB2 distribute inserted data among the partitions and the answer is yes. DB2 will insert into the correct partition the data you provide based on the partition index. You do not have any real control over this other than providing the index data.

Duane

> -----Original Message----- > From: ravi kumar hassan [SMTP:ravibh@HOTMAIL.COM] > Sent: Wednesday, January 26, 2000 7:33 PM > To: DB2-L@RYCI.COM > Subject: regarding partition tablespace. > > Hi Everybody, > > i have a question on partiotion tablespaces. > > Actually in our shop they are using partion tablespace. these [...] 33167 71 38_Re: DB2 V6 to DB2 V5 Fallback - Beware9_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Thu, 27 Jan 2000 16:26:47 +0000384_us-ascii Stan, Im just trying to determine from Paul Packham whether he had the stated problem with UQ31080 applied to his V5 code. If he did (and carried out HOLD actions), then the conern is that you need an extra fallback procedure to 'revert' SYSSTRINGS. If he didnt, then it should have been put on as a pre-req to V6. Kind Regards, Andy Hunt - Scottish And Southern Energy [...] 33239 39 45_Mainframe Web Enablement brochure - Forwarded11_John Wynton21_jwynton@THEMISINC.COM31_Thu, 27 Jan 2000 11:12:55 -0500466_iso-8859-1 Folks:

Saw this in the CICS list, thought you might be interested - very good article/presentation:

Together with a couple of colleagues I have made a brochure about our mainframe web enablement experiences. Marketing Dept. now has made a public version with all the for-internal-use-only stuff removed, and anyone interested can pick it up as an Acrobat file (3 MB) at http://mainframeweb.csc.dk/Hostweb/ (which of course is a S/390). [...] 33279 60 27_Re: MIGRATING FROM V4 TO V517_Luke G. Bauerlein24_lbauerlein@MANDTBANK.COM31_Thu, 27 Jan 2000 11:35:43 -0500575_US-ASCII That was part of the problem. The bigger part was the SDSNLINK ERLY code was also modified as part of the maint. IPL cleared the problem. Thanks to all who replied. Good to know some sysprogs are in the loop as well as all the DBAs etc. Luke >>> BOB JEANDRON 01/27/00 09:23AM >>> You didn't list the IRLM loadlib, SDXRRESL. Suggest you check if apply of II10128 changed modules in this lib and copy that lib as well. >>> DB2-L@RYCI.COM@inter2 01/25/00 12:41PM >>> After migrating from v4 to v5 on a "sysprog" LPAR and then being able to [...] 33340 112 38_Re: DB2 V6 to DB2 V5 Fallback - Beware12_Paul Packham28_packhamp@IT.POSTOFFICE.CO.UK31_Thu, 27 Jan 2000 16:37:08 -0000475_iso-8859-1 Hi Andy,

Thanks for this, I will ask the question, in the meantime we are continuing with our testing. We estimate to complete before we go home tonight, although at what hour that will be is anyones quess !! However, we have discovered that our old friend catmaint adds 102 records to sysstrings during the upgrade, and we now think that these are the rows which require backing out rather than those which were loaded as per hold action for UQ31081. [...] 33453 59 13_Re: REOPTVARS22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Thu, 27 Jan 2000 16:42:16 +0000501_ISO-8859-1 Hi, what mix of work do you run through your DB2s? Dynamic/Static that is .. are mostly dynamic? regards Leslie



______________________________ Reply Separator _________________________________ Subject: REOPTVARS Author: Ruediger Kurtz at Internet Date: 1/27/00 3:57 PM



Hi fellow DB2'ers,

has someone out there bound packages using reoptvars(yes) and if so what has been the reason to do so and what was the outcome ? [...] 33513 71 27_Re: MIGRATING FROM V4 TO V517_Snoeyenbos, Craig35_Craig.Snoeyenbos@STARWOODHOTELS.COM31_Thu, 27 Jan 2000 11:53:14 -0500389_iso-8859-1 Luke,

I'm one of the sysprogs on the list, and I'm not sure that you are on the right track with the ERLY code part of the explanation. (SDXRRESL sounds likely) AFAIK, ERLY code has always been downward compatible over a span of several versions. I usually move the ERLY code in advance of the rest of the maintenance and haven't had a problem from V3 through V5.5. [...] 33585 34 44_EDM pool calculations and head scratching...12_Vernon, John27_John.Vernon@TWC.STATE.TX.US31_Thu, 27 Jan 2000 11:17:46 -0600618_- ...walking very gingerly here, I became interested in the discussion related to EDM pool sizing and usage, and looked at the settings we have here within a sysplex environment/data sharing....I reached for my bible DB2 Developers Guide Third Edition and turned to page 678 to work through the calculations provided.....but when I reached page 680, it SEEMS as though there is a problem within the query provided which attempts to provide the average number of columns per table....it attempts to do a count (and divide) from sysibm.syscolumns where type = T ..... there is no type = T available...because there [...] 33620 164 55_Re: Listing Databases for an Instance (UDB v6.1 on AIX)13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Thu, 27 Jan 2000 11:22:52 -0600605_US-ASCII Hello Bill, it sounds like you have all the databases cataloged in the default instance. Some do this for ease of administration though the Control center. You can uncatalog the databases from the default instance and issue a SET DB2INSTANCE=xxxx and catalog the databases that belong to that instance. Now when you issue the List Database Directory you will only see the databases for that instance. However now when you start the Control Center you will only see the the databases in the default instance. To administer the other databases from the Control Center you will have to use the [...] 33785 27 56_Re: caf for batch programs & question about online reorg17_Polley, Mike (M.)16_mpolley@FORD.COM31_Thu, 27 Jan 2000 12:00:02 -0500328_iso-8859-1 Maybe I missed something on this thread, but one place I worked used CAF for mvs enqueing, which reduced unavailable resource contentions with the batch programs that participated by specifying the tablespaces and the type of expected usage i.e. 'E'xclusive or 'S'hared. For this reason CAF seemed to be useful. [...] 33813 60 44_EDM pool calculations and head scratching...14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Thu, 27 Jan 2000 11:37:38 -0600788_ISO-8859-1 John (and list):

For the query on SYSCOLUMNS on page 680 of DB2 Developer's Guide remove the WHERE TYPE = 'T' clause. That is a typo - sorry for the confusion.

Cheers, Craig S. Mullins Director, DB2 Technology Planning BMC Software http://www.bmc.com Craig_Mullins@bmc.com http://www.craigsmullins.com cmullins@compuserve.com



>----------Original Message----- >From: Vernon, John [mailto:John.Vernon@TWC.STATE.TX.US] >Sent: Thursday, January 27, 2000 11:18 AM >To: DB2-L@RYCI.COM >Subject: EDM pool calculations and head scratching... > > >...walking very gingerly here, I became interested in the discussion related >to EDM pool sizing and usage, and looked at the settings we have here within >a sysplex environment/data sharing....I reached [...] 33874 101 27_Re: MIGRATING FROM V4 TO V517_Luke G. Bauerlein24_lbauerlein@MANDTBANK.COM31_Thu, 27 Jan 2000 12:42:24 -0500572_US-ASCII Craig, I have seen maintenance with "hold for action" being an IPL to make the PTF active. My theory was that other maintenance needed the ERLY code to be at a given level. I would like to have the luxury to try the maintained version of V4 with the old SDSNLINK and the maintaned SDXRRESL, but one unscheduled IPL this week is all management can handle. FYI this was not a migration issue but rather getting to a documented maintenance level in order to be able to migrate/fallback to V4 after migration to V5. Versioned downward compatability has not come [...] 33976 40 20_Re: SDSNLOAD Library24_Gendron, Dave (Exchange)28_Dave.Gendron@COASTALCORP.COM31_Thu, 27 Jan 2000 13:07:17 -0500469_- Dave,

We do not linklist. Each subsystem steplibs to its own set of load libs. These are named independent of the DB2 version, such as SYS6.DB2A.SYSTEM.SDSNLOAD. Smp maint hits version named loadlibs such as SYS6.DB2A.DB2V51.SDSNLOAD. Maintenance and upgrades target a version-lib, and are then copied from the version-lib to the generic-lib. Users never change their JCL or other references to the libs. This approach has been reliable and maintainable. [...] 34017 21 13_DB2 for Linux0_23_adamsrob@WELLSFARGO.COM31_Thu, 27 Jan 2000 11:24:56 -0700406_iso-8859-1 Please, anyone, let me know about your experiences with Redhat Linux 6.1 and the DB2 port it ships with, on-list or off. Any resources you can steer me toward would be particularly helpful, along with anecdotal accounts of your own trials and tribs, mishaps, pitfalls, pratfalls and outcomes. Is this platform ready for the real world? Any special difficulties getting to DB2 under Apache? [...] 34039 40 40_Re: Oracle Transparent G'Way Question...12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 27 Jan 2000 12:13:00 -0500301_- It's been a while since we were attempting to use this but my notes say:Alter Session close database link db_link_name; will close the DB2 threads. I think this can be coded in the app. We are moving to the procedural gateway because the transparent gateway did not meet our performance needs. [...] 34080 101 27_Re: MIGRATING FROM V4 TO V517_Snoeyenbos, Craig35_Craig.Snoeyenbos@STARWOODHOTELS.COM31_Thu, 27 Jan 2000 13:41:02 -0500646_iso-8859-1 Luke, You're right, I was confused about the sequence of events. Old SDSNLINK won't work with maintained libs. Maintained SDSNLINK should work with old libs. Test this for your situation, IPL with the maint'd SDSNLINK, old everything else, and you can then migrate/fallback as needed without another IPL. Craig

-----Original Message----- From: Luke G. Bauerlein [mailto:lbauerlein@MANDTBANK.COM] Craig, I have seen maintenance with "hold for action" being an IPL to make the PTF active. My theory was that other maintenance needed the ERLY code to be at a given level. I would like to have the luxury to try the maintained [...] 34182 22 31_Security - DB2 Connect to OS39010_Park, Stan17_SPark@STATE.NM.US31_Thu, 27 Jan 2000 11:43:44 -0700509_iso-8859-1 We are involved in a pilot project here and need to know if there is any more recent information than the Redbook (April 1995) DB2 for MVS DRDA Server: Security Considerations GG24-2500-00 ?

Leon?



Thanks, Stan Park State of New Mexico

================================================ 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. 34205 74 40_Re: Oracle Transparent G'Way Question...22_KOA DAVID S. (DBM1DXK)15_dbm1dxk@UPS.COM31_Thu, 27 Jan 2000 13:50:57 -0500540_iso-8859-1 Can you list the specific perfomance problems you had with the transparent gateway? I'm working in the opposite direction....performance is currently unacceptable with the procedural gateway so I just ordered the transparent gateway for evaluation.

According to the transparent gateway doc V8.0.4.1.0...you have two options for terminating the thread: 1) issue a ROLLBACK in the application followed by the ALTER...statement documented below 2) for Oracle server V8.0.4.3 and higher...you can use the timeout feature [...] 34280 39 42_MODIFY UTILITY FOR PARTITIONED TABLESPACES13_Crowley, Paul21_CrowleP@DIVINVEST.COM31_Thu, 27 Jan 2000 12:39:04 -0600573_iso-8859-1 Hello all, We increased the number of times we quiesce tables during the day which has added rows significantly to SYSCOPY. I had not been running the MODIFY utility and am now trying to catch up. The MODIFY jobs for the non-partitioned tablespace are cp intensive but we have to live with costs. However when I run for the partitioned tablespaces the cost are prohibitive. The SYSCOPY table has 881,160 rows as I write this and was reorged on Sunday 1/23/2000. I ran a MODIFY for 28 non partitioned tables for 92 days which deleted 14,083 rows and the job [...] 34320 87 31_Re: V5 & P97D CA/Platinum tools13_Thomas, Janis20_JANIS.THOMAS@CAI.COM31_Thu, 27 Jan 2000 14:08:06 -0500311_iso-8859-1 Just to clarify, zparms can be displayed in RC/Query using the System DSNZPARM report (DB2 Object => SY, Option => Z). If anyone is having problems displaying the report, please let me know the details (tape/maint level, problem description, any error msgs), and I can get this to tech support. [...] 34408 68 56_Re: caf for batch programs & question about online reorg14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU31_Thu, 27 Jan 2000 14:37:25 -0500513_us-ascii Is this a function of CAF, or do you mean the programs executed some kind of ENQ macro before connecting? If the latter, I think you might be able to do it even with DSN RUN. If I remember correctly, programs that use RUN don't actually connect until the execution of an SQL call, so you could put the ENQ before that. You'd have to DEQ before actually releasing the resources at termination, but the small overlap that this would permit wouldn't necessarily cause much harm for this type of usage. [...] 34477 140 29_Re: DB2 referential integrity11_James Drewe20_James.Drewe@AEXP.COM31_Thu, 27 Jan 2000 10:00:26 -0700573_- Rick

As far betting my career when using application RI, I will have to turn to Dilbert, my chief advisor: "if you had my career, you would bet it as well". (My apologies to those of you in other countries that don ,t receive Dilbert cartoons.)

There really is no 100% guarantee against data integrity risk -- whether you use DBMS or application RI. Recovery or load operations with DBMS RI also can go awry. As Craig Mullins pointed out as well, declarative RI does not cover RI requirements for cascading updates; this has to be done via triggers or [...] 34618 33 24_needing to make contacts18_Dickey, Kathleen F21_kfd862b@MAIL.SMSU.EDU31_Thu, 27 Jan 2000 13:29:12 -0600344_iso-8859-1 I am an applications systems analyst at Southwest Missouri State University. Formerly I was the DBA for our SQL/DS system. We are in the process of formulating a plan for migrating our VSAM files to DB2. Since we are a state funded institution we have little money. Consequently we are a VM/VSE shop with DB2 for this platform. [...] 34652 116 40_Re: Oracle Transparent G'Way Question...40_Andersen, Cindy (CC-Application Support)26_Cindy.Andersen@CONAGRA.COM31_Thu, 27 Jan 2000 13:44:07 -0600543_iso-8859-1 It's been a couple years since I used it, but the big performance problem we had related to the use of Oracle SQL functions, such as DECODE & RTRIM, that were not supported in DB2.

We had Oracle programs that needed to verify that a certain document existed in a DB2 OS/390 table before it could be inserted into an Oracle table with data from the DB2 table. This should have been a simple, quick query because the docid was the unique key of the DB2 table. It should have used the index on the table, gone to the data, [...] 34769 17 12_DB2 Connect?11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Thu, 27 Jan 2000 13:56:22 -0600498_us-ascii Hi everybody!

We are OS/390 DB2 version 6.1 shop trying to connect from Lotus Approach via TCP/IP. My question is do we need DB2 Connect to make the connection from PC to Mainframe? So far we have not been able to connect without it!... : (

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 34787 183 29_Re: DB2 referential integrity19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 27 Jan 2000 14:03:09 -0600603_iso-8859-1 Jim, You've given me a gift beyond all price, inspired me, if you will.

I'm getting out of this dumb business and getting into writing Dilbert cartoons.

You ARE the Man, 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 [...] 34971 69 46_Re: MODIFY UTILITY FOR PARTITIONED TABLESPACES19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 27 Jan 2000 14:10:22 -0600576_iso-8859-1 Paul, This very issue came up a couple(?) weeks ago. Check the DB2-L Archives but I think the consensus opinion was to MODIFY off rows a few days at a time.

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 [...] 35041 78 39_Re: OS/390 Stored Procedure Result Sets10_Rao, Peavy26_Peavy.Rao@TRANSAMERICA.COM31_Thu, 27 Jan 2000 14:14:38 -0600304_- Hi, Our PB application is using 900+ DB2 Stored Procedures. At least 600 of these return result sets. We use StarSQL ODBC driver to call these SPs. On mainframe it's DB2 V5 & COBOL.

I have done most of the work on this migration project. Please let me know, if you have specific questions. [...] 35120 69 58_Consolidating VSAM extents on DSNDB01 and DSNDB06 datasets17_Ben Ollo NONLILLY27_OLLO_BEN_NONLILLY@LILLY.COM31_Thu, 27 Jan 2000 15:51:07 -0500458_us-ascii In the past, I have consolidated DSNDB01 and DSNDB06 VSAM datasets in multiple extents to a single extent with the following procedure. Am I getting myself into trouble? I haven't had any problems. DSN1COPY is supposed to copy SYSUT1 to SYSUT2 page-by-page without any changes. I can't see how this procedure would introduce inconsistencies into the DB2 catalog/directory, but some of my co-workers have expressed concerns over my methodology. [...] 35190 23 24_Tool to evaluate indexes16_Alison Pelletier30_APelletier@MOUNTAINWESTFIN.COM31_Thu, 27 Jan 2000 13:40:50 -0700547_iso-8859-1 Hello everyone,

I have been thrown into a hornet's nest...We are DB2/OS390 V5. We have appox. 200 domain and parameter tables where the index design is lacking to say the least. Several TB scan and non-matching IDX scan access paths. So, I need to evaluate all programs that access these tables to come up with optimal index design. Is there a tool that can help me? I'd like to be able to input all the dbrms and have the tool output a best guess of indexes for each table. Am I dreaming? If you know of such a tool, could [...] 35214 40 28_Re: Tool to evaluate indexes22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Thu, 27 Jan 2000 12:55:56 -0800627_- CA/Platinums Index expert is a pretty decent tool for supplying suggested indexes. Unfortunately it's a pretty spendy product.

> -----Original Message----- > From: Alison Pelletier [SMTP:APelletier@MOUNTAINWESTFIN.COM] > Sent: Thursday, January 27, 2000 12:41 PM > To: DB2-L@RYCI.COM > Subject: Tool to evaluate indexes > > Hello everyone, > > I have been thrown into a hornet's nest...We are DB2/OS390 V5. We have > appox. 200 domain and parameter tables where the index design is lacking > to > say the least. Several TB scan and non-matching IDX scan access paths. > So, > I need to evaluate all programs that [...] 35255 30 38_SQL1058N Error Message on full backups0_18_DB2DBAinTX@AOL.COM29_Thu, 27 Jan 2000 15:59:17 EST551_us-ascii Yesterday we installed DB2 (6.1) on a new NT server. We went in using the "Smart Guide" to set up a job to complete full backups nightly. When we did this, it generated two separate (but identical) jobs. We went back in to delete them and got the attached error message, stating "The handle parameter in the Directory Scan command is not valid". You can't run the two jobs, you can't look at them, reschedule them or ANYTHING - without getting the error. We looked up the error in DB2 Books Online and it gave us the explanation below - [...] 35286 33 38_TIMESTAMP as Non-intelligent ID for PK19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Thu, 27 Jan 2000 14:58:50 -0600564_iso-8859-1 Hi all and thanks in advance, We're looking pretty strongly at using DB2 datatype TIMESTAMP (TS) as a Primary Key (PK). The main reasoning we used is that it is easily assignable. By that, we mean that it can be programmatically set by simply, in COBOL terms, doing a SET :HOSTVAR1 = CURRENT TIMESTAMP. This, I believe, is a register to storage move. An alternative, and the reason for going with TS instead, would be a table with a counter that is incremented each time a PK is needed. This brought up the concurrency issue on a counter table and [...] 35320 34 16_Re: DB2 Connect?10_Park, Stan17_SPark@STATE.NM.US31_Thu, 27 Jan 2000 14:03:54 -0700500_- It is one of several ways. Check the IBM Redbooks out on this.

-----Original Message----- From: Jeff Faughn [mailto:Jeff_Faughn@MAY-CO.COM] Sent: Thursday, January 27, 2000 12:56 PM To: DB2-L@RYCI.COM Subject: DB2 Connect?



Hi everybody!

We are OS/390 DB2 version 6.1 shop trying to connect from Lotus Approach via TCP/IP. My question is do we need DB2 Connect to make the connection from PC to Mainframe? So far we have not been able to connect without it!... : ( [...] 35355 56 42_Re: TIMESTAMP as Non-intelligent ID for PK14_McCombs, Terry20_tmccomb@SEDGWICK.GOV31_Thu, 27 Jan 2000 15:37:58 -0600485_iso-8859-1 Something we've done instead of using a separate counter table is grab the maximum pk in use for the table in question and add 1 to it, then insert. We have retry logic in the program to keep adding 1 up to n times (usually 3) if we get a duplicate. After a failure on the nth time, we send a message so the user can try again. You still have the problem of some contention on the table, but the advantage is that the number is smaller, can be printed on reports, etc. [...] 35412 88 62_Re: Consolidating VSAM extents on DSNDB01 and DSNDB06 datasets10_Paul Fegan23_Paul.Fegan@CITEC.COM.AU31_Fri, 28 Jan 2000 07:49:34 +1000672_US-ASCII Ben, I can't see any problems with this method. An easier method would be to HMIG and HRECALL the offending tablespaces provided the catalog tables aren't too huge it will probably be quicker.

Paul

>>> Ben Ollo NONLILLY 28/01/00 6:51:07 >>> In the past, I have consolidated DSNDB01 and DSNDB06 VSAM datasets in multiple extents to a single extent with the following procedure. Am I getting myself into trouble? I haven't had any problems. DSN1COPY is supposed to copy SYSUT1 to SYSUT2 page-by-page without any changes. I can't see how this procedure would introduce inconsistencies into the DB2 catalog/directory, [...] 35501 126 40_Re: Oracle Transparent G'Way Question...12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 27 Jan 2000 15:49:00 -0500613_- The performance problems we had were related to connect time. The app. needed to connect, disconnect, connect... Most of the processing was done in Oracle and in our testing of the connect/disconnect to DB2 was expensive (10-14 Secs). Once connected, the transparent gateway was quick. The current testing of the procedural gateway, using APPC to CICS, is yeilding subsecond response for the same app. We don't have it in production yet, but test have been good so far. >>> DB2-L@RYCI.COM@inter2 01/27/00 01:46PM >>> It's been a couple years since I used it, but the big performance problem we had related [...] 35628 54 16_Re: DB2 Connect?12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Thu, 27 Jan 2000 16:12:09 -0600349_iso-8859-1 I tried for several days to get cae to work with the os/390 until someone told me the cae would not work. We are currently using cae to connect to datajoiner and then to db2/390. It works fine now but it is somewhat slower than our connection to db2/390 using db2 connect. We are not yet using either method in production just test. [...] 35683 49 21_Question on Utilities15_Jessee, Scott L21_SCOTTL.JESSEE@CAI.COM31_Thu, 27 Jan 2000 17:21:44 -0500574_iso-8859-1 To All:

The DB2 Development & Support organization from the company formerly known as PLATINUM technology is alive and well.

As for the question on third party utilities, most of the large DB2 shops around the world run one of three third party vendor utilities: CA/Pti, BMC, or CDB. They do so for speed, automation, and ease of use, and a host of other features not provided by the base IBM utilities. IBM has done a good job in improving elapsed time in recent releases and have made no small deal about it. Each customer has to look at the [...] 35733 549 55_Re: Listing Databases for an Instance (UDB v6.1 on AIX)11_ALLEN,Bruce37_bruce.allen@EMPLOYMENTNATIONAL.COM.AU31_Fri, 28 Jan 2000 09:18:06 +1100500_iso-8859-1 Hi, this looks real simple on our site. This is what I see for DB2 UDB v6.1 on NT (no fixpacks). 1. list db directory shows all databases in a different machine or instance as 'Directory Entry Type=Remote'. For a remote db entry, the list node directory shows local instances as 'Protocol=Local'. This entry was catalogued by the control centre. 2. If I start control Centre (in the default instance), I can add other local instances, and catalog the databases under those instances. [...] 36283 171 40_Re: Oracle Transparent G'Way Question...15_Mike Jakubowski16_mjakubow@CME.COM31_Thu, 27 Jan 2000 16:48:20 -0600396_- Regarding the original question...A new parameter is introduced with V8.0.4 of the Gateway: GTWY_IDLE_TIME. This allows you to set the amount of time (in seconds) before an idle gateway user is terminated. While we still had 8.0.4 installed, I tested this feature and it appeared to work as advertised. The application may have to handle the following on the next SQL call to the Gateway. [...] 36455 15 10_UDB for NT13_Toppins, Mike19_MToppins@UNIPAC.COM31_Thu, 27 Jan 2000 16:00:26 -0700266_iso-8859-1 I'm a mainframe person that has been asked to get pricing information on what it would take to set up UDB on NT. I've gone out to the web site from IBM and totally confused. What parts/pieces do I need to set up an NT Server and multi client system? [...] 36471 100 44_Re: DB2 active logs and IBM Raid-1 type DASD13_David Chapman24_david_chapman@CGU.COM.AU31_Fri, 28 Jan 2000 10:18:16 +1100359_- Dear DB2 List,

Thanks for the answers to my question so far. However, the sysprogs response to the single point of failure issue is that DB2 will not be effected by failure on IBM Raid-1 type DASD. She tells me that even using single DB2 active logging, she could turn one of the mirrored packs off-line and DB2 would not blink. Is she correct? [...] 36572 37 42_Re: SQL1058N Error Message on full backups12_Jim Harrison12_jimh@QIS.NET31_Thu, 27 Jan 2000 18:24:21 -0500401_us-ascii Are you up to date on NT & DB2 fixpacks etc? Sounds like a bug. I haven't tried 6.1 but a friend who installed it on NT workstation said it totally hosed his machine, but ran ok on his Win95 at home. Unfortunately my experience intalling products on NT has led me to the conclusion that if something doesn't work right the first time - yank it out and start over again at the beginning. [...] 36610 114 22_Re: How do I sign-off?16_Milligan, Andrea25_Andrea_Milligan@BCBST.COM31_Thu, 27 Jan 2000 18:35:08 -0500724_- 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.



Andrea Milligan DataBase Administrator BlueCross BlueShield of Tennessee

> -----Original Message----- > From: Vijayalakshmi Ganesan [SMTP:viji_ganesan@CHENNAI.TCS.CO.IN] > Sent: Thursday, January 27, 2000 3:48 AM > To: DB2-L@RYCI.COM > Subject: How do I sign-off? > > Hi all, > Can someone please let me know how to sign-off from the forum. > > Regards, > Viji > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage [...] 36725 132 25_Re: Question on Utilities0_19_Tim.Lowe@STPAUL.COM31_Thu, 27 Jan 2000 17:40:55 -0600585_us-ascii Frank, IBM has definitely spent big time (and money) improving the performance and functionality of their DB2 utilities. In my opinion, the IBM utilities are as good as, or better than other third-party utilities in most cases.

That is not to say that there is never a need for third-party vendor DB2 utilities, but it is substantially reduced from what it used to be. There are certainly some functions that some products do better than others, but analyze your own needs. Are there other ways that you could do this? How often do you need to do it? For example: [...] 36858 164 48_Re: EDM pool calculations and head scratching...16_Milligan, Andrea25_Andrea_Milligan@BCBST.COM31_Thu, 27 Jan 2000 18:59:47 -0500667_- probably a typo. Join syscolums to systables by creator and table and get type = 't' from systables.

Andrea Milligan DataBase Administrator BlueCross BlueShield of Tennessee

> -----Original Message----- > From: Vernon, John [SMTP:John.Vernon@TWC.STATE.TX.US] > Sent: Thursday, January 27, 2000 12:18 PM > To: DB2-L@RYCI.COM > Subject: EDM pool calculations and head scratching... > > ...walking very gingerly here, I became interested in the discussion > related > to EDM pool sizing and usage, and looked at the settings we have here > within > a sysplex environment/data sharing....I reached for my bible DB2 > Developers > Guide Third Edition [...] 37023 61 24_Tool to evaluate indexes14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Fri, 28 Jan 2000 10:50:56 +1000458_us-ascii Alison,

Optimal Index Design is important for large tables.

For small relatively static tables, if you have more indexes than necessary it doesn't matter so much. You can afford several indexes on such small tables. The Update overhead is low!

Many people believe scans are O.K. on very small tables. I don't agree unless scan happens only once (not repeated and not inner table of joins and not in correlated subqueries). [...] 37085 125 42_Re: TIMESTAMP as Non-intelligent ID for PK25_Darvill, Gary E [IBM GSA]38_Gary.E.Darvill@CORPMAIL.TELSTRA.COM.AU31_Fri, 28 Jan 2000 10:59:51 +1100378_iso-8859-1 A timestamp is ok for generating unique keys but when you have a situation where you need to create a unique integer for audit purposes it is not very useful. In particular, if a reference number has to be quoted to a user then a nine digit integer is preferable to a date/time stamp. And as Terry has pointed out it a bit smaller and can be printed on reports. [...] 37211 230 23_Re: DSN1COPY problem !!12_Chao Terry F23_Terry.F.Chao@M1.IRS.GOV31_Mon, 24 Jan 2000 07:50:55 -0500584_iso-8859-1 Give the following sequence a shot:

1 REORG your source tablespace 2 Take FULL imagecopy of the source tablespace after REORG is complete 3 DSN1COPY using the new imagecopy as input to load the target tablespace (of course, check whether DDL is the same between source/target table AND tablespace).

I suspect you have table ALTERs (such as adding a column) performed on table in your source tablespace not followed by a REORG before the imagecopy was taken and the imagecopy was subsequently used as input to the DSN1COPY to load your target tablespace. [...] 37442 108 30_Re: Selecting Image Properties14_Richard Yevich21_ryevich@ATTGLOBAL.NET31_Thu, 27 Jan 2000 19:26:35 -0500648_iso-8859-1 Marcus,

Not sure but it does not matter. The Extenders are the same. Only difference would be where the actual image was stored, OS/390 or UNIX.

Hope this helps, Richard Yevich +======+======+======+ ryevich@attglobal.net

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Marcus Davage > Sent: Wednesday, January 26, 2000 9:20 AM > To: DB2-L@RYCI.COM > Subject: Re: Selecting Image Properties > > > Richard, > > I've just checked out the site, and it's brilliant! Is this run > on OS/390 or > UDB? > > Marcus > > -----Original Message----- > From: Richard [...] 37551 52 44_Re: DB2 active logs and IBM Raid-1 type DASD14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Thu, 27 Jan 2000 21:45:37 -0500531_ISO-8859-1 Technically she's correct. However, to avoid the single point of failure situation, you don't even want both logs in the same RAID box if you have more than one. Maybe to put things into perspective, ask her if she's willing to bet her job on the premise of never having a problem - because it will be her neck if there ever is a failure. Ask her if she is willing to put it in writing - that DB2 can never be impacted by having single logging. Ask her to quantify the dollar cost saving of single logging. DASD is [...] 37604 60 48_Re: EDM pool calculations and head scratching...22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Fri, 28 Jan 2000 06:14:32 +0000480_ISO-8859-1 John a good point - I think I may improve my storage calculator to tell you how to size the EDM pool aswell as telling how the DBM1 address space is broken down.

How did you get on with it?

regards

Leslie



______________________________ Reply Separator _________________________________ Subject: EDM pool calculations and head scratching... Author: "Vernon; John" at Internet Date: 1/27/00 11:17 AM [...] 37665 40 30_Using high capacity cartridges16_Robert Gillespie21_GillesRP@TELKOM.CO.ZA31_Fri, 28 Jan 2000 08:48:12 +0200429_US-ASCII Hi

We have just install 10 9840 andnow need to utilize these drives.

there are 2 things that we want to look at :

1) Doing our backups to these drives.

The problem the limitation with JCL that only allows 255 exec's in a single job. This is not enough to fill up one of these cartridges. Does anybody know how to get arround this limitation and stack more backups on a single cartridge. [...] 37706 187 24_Re: When is big too big?11_Thomas Bird21_tbird@TJBVENTURES.COM31_Thu, 27 Jan 2000 23:34:22 -0800670_us-ascii I totally agree with Kirk. Thanks for stepping up when some of us were feeling the same way. It's not venting.

Regards,

T. Bird President TJB Ventures, Inc. Voice: (707) 226-1100 Fax: (707) 226-1188 Email: tbird@tjbventures.com Web: http://www.tjbventures.com





|--------+-----------------------> | | Kirk Hampton | | | | | | | | | 01/27/00 | | | 06:23 AM | | | Please | | | respond to | | | DB2 Data Base| | | Discussion | | | List | | | | |--------+-----------------------> >-----------------------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: (bcc: Thomas [...] 37894 139 34_Re: Using high capacity cartridges25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG29_Fri, 28 Jan 2000 07:29:47 GMT514_us-ascii

Hi Robert,

1. When u use 3rd party utility, you do not need to have more than one step in the JCL. For example at my shop I have 170 tablespace gettting backed up in a single step. All these get backedup to the same tape as multiple lables on the same tape. Basically stacking is done. So if you have third party utlity you could. If I am not wrong, even with IBM utility you could mount the tape from some other job and specify a different label. So the limit really does not matter. [...] 38034 37 44_Re: DB2 active logs and IBM Raid-1 type DASD14_Massimo Scarpa16_mscarpa@CESVE.IT31_Fri, 28 Jan 2000 08:57:52 +0100337_us-ascii I didn't see my previous answer so i repost the message.

0) She's correct, but with a RAID-1 mirroring I'd be a little worry....

1) I've never seen a production DB2 in single logging mode. Even in mirror mode.

2) In a test environment, many years ago, we had a single-log DB2, mirrored by a raid-1 IBM [...] 38072 26 25_Re: Question on Utilities14_Massimo Scarpa16_mscarpa@CESVE.IT31_Fri, 28 Jan 2000 09:03:16 +0100400_us-ascii Yes, but at which cost ? You had to charge your budget for a duplicate tool and today , in my

opinion, it's not necessary in most shops. IBM utilities now work well and became easy to use

and with soime very interestng features (REORG UNLOAD EXTERNAL, REORG DISCARD etc..)



Regards

Max Scarpa

My opinion, standard disclaimers apply etc etc.... [...] 38099 80 34_Re: Using high capacity cartridges22_Leslie Pendlebury-Bowe43_Leslie.Pendlebury-Bowe@CCMAIL.EU.SONY.CO.JP31_Fri, 28 Jan 2000 08:08:00 +0000352_ISO-8859-1 Robert

short answers

1. Yes use Platinum (or similar tool) PDA to build multiple jobs with upto 255 steps in them writing to the one tape. You use models which allow you to have the second and third etc jobs mount the same tape as used in the first job ... then write the next file (label) to the tape and away you go ... [...] 38180 163 40_Re: Oracle Transparent G'Way Question...10_Robert Ord21_robertord@HOTMAIL.COM29_Fri, 28 Jan 2000 09:06:05 GMT665_- Surely you could use CASE and STRIP at the DB2 end to replace DECODE and RTRIM which Oracle uses. I kmow its unlikely to be that simple but worth a mention.

Rob



>From: BOB JEANDRON >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Oracle Transparent G'Way Question... >Date: Thu, 27 Jan 2000 15:49:00 -0500 > >The performance problems we had were related to connect time. The app. >needed >to connect, disconnect, connect... Most of the processing was done in >Oracle >and in our testing of the connect/disconnect to DB2 was expensive (10-14 >Secs). Once connected, the [...] 38344 78 13_Re: REOPTVARS14_Ruediger Kurtz28_ruediger.kurtz@HUK-COBURG.DE31_Fri, 28 Jan 2000 10:48:32 +0100669_us-ascii Hi Leslie,

most of our SQL-Statements are static, in fact I think that all of our SQLs are static.

Regards Ruediger







Leslie Pendlebury-Bowe schrieb: > > Hi, > what mix of work do you run through your DB2s? Dynamic/Static that is > .. are mostly dynamic? > regards > Leslie > > ______________________________ Reply Separator _________________________________ > Subject: REOPTVARS > Author: Ruediger Kurtz at Internet > Date: 1/27/00 3:57 PM > > Hi fellow DB2'ers, > > has someone out there bound packages using reoptvars(yes) and if so what > has been the reason to do so and what [...] 38423 22 20_Re: SDSNLOAD Library15_Jeffery A Price17_jprice@IPALCO.COM31_Fri, 28 Jan 2000 06:17:33 -0500280_us-ascii We use LLA to manage our SDSNLOAD library. We are still able to use steplib. Using LLA saved us a great amount of time on I/O to steplib, as well as overhead on unneeded SVC (BUILDL, etc.). One job used to do 1,000,000 I/Os to steplib before LLA, now does about 65. [...] 38446 85 48_Re: EDM pool calculations and head scratching...20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM31_Fri, 28 Jan 2000 11:58:08 -0000397_iso-8859-1 Craig,

Waagh! I'm putting my head over the parapet, arguing with you, but...

If your count of rows in SYSTABLES excludes views (correctly), but your count of rows in SYSCOLUMNS doesn't, surely the resulting ratio will be meaningless? In the case of silly sites like mine where we have a parallel view for every table isn't the result exactly twice what it should be? [...] 38532 13 35_Re: Security - DB2 Connect to OS39013_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Fri, 28 Jan 2000 06:41:57 -0600385_- SNA or TCP/IP?

If TCP/IP, try "WOW! DRDA Supports TCP/IP: DB2 Server for OS/390 and DB2 Universal Database", SG24-2212-00 (July 1997).

================================================ 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. 38546 31 16_Re: DB2 Connect?13_Shauna Hadden24_shauna.hadden@BCBSKS.COM31_Fri, 28 Jan 2000 06:45:19 -0600582_- On Thu, 27 Jan 2000 13:56:22 -0600, Jeff Faughn wrote:

>Hi everybody! > > We are OS/390 DB2 version 6.1 shop trying to connect from Lotus Approach via >TCP/IP. My question is do we need DB2 Connect to make the connection from PC >to Mainframe? >So far we have not been able to connect without it!... : ( > >================================================ >To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. [...] 38578 44 39_A Bit off Topic but ImagePlus is DB2...11_Steck, Dave16_Steckd@ABCBS.COM31_Fri, 28 Jan 2000 06:49:53 -0600577_iso-8859-1 We have had an ImagePlus environment since the beginning of time and have recently had some business reasons why our users were not able to work an Image before it migrated to optical. (30 days) Well, now they're really behind and driving our Jukebox to it's absolute max. We're wanting to pre-fetch objects back onto DASD and have no experience with doing this. I've got a few questions that if anyone could answer, it would be a great help. Our V.P.'s want us to have this solution in place by Monday so we're sweating pretty hard on these questions. Thanks! [...] 38623 90 62_Re: Consolidating VSAM extents on DSNDB01 and DSNDB06 datasets11_Hilton Tina15_HiltonT@TCE.COM31_Fri, 28 Jan 2000 07:55:08 -0500683_iso-8859-1 I use DFDSS (ADRDSSU) to "copy" it to another volume and delete the old dataset. It does its best to get it in one extent.

Tina

-----Original Message----- From: Ben Ollo NONLILLY [mailto:OLLO_BEN_NONLILLY@LILLY.COM] Sent: Thursday, January 27, 2000 3:51 PM To: DB2-L@RYCI.COM Subject: Consolidating VSAM extents on DSNDB01 and DSNDB06 datasets



In the past, I have consolidated DSNDB01 and DSNDB06 VSAM datasets in multiple extents to a single extent with the following procedure. Am I getting myself into trouble? I haven't had any problems. DSN1COPY is supposed to copy SYSUT1 to SYSUT2 page-by-page without any changes. I can't see how [...] 38714 63 20_Re: SDSNLOAD Library11_Hilton Tina15_HiltonT@TCE.COM31_Fri, 28 Jan 2000 08:03:55 -0500739_iso-8859-1 We do the same as Dave. Have done this from the beginning.

Tina

-----Original Message----- From: Gendron, Dave (Exchange) [mailto:Dave.Gendron@COASTALCORP.COM] Sent: Thursday, January 27, 2000 1:07 PM To: DB2-L@RYCI.COM Subject: Re: SDSNLOAD Library



Dave,

We do not linklist. Each subsystem steplibs to its own set of load libs. These are named independent of the DB2 version, such as SYS6.DB2A.SYSTEM.SDSNLOAD. Smp maint hits version named loadlibs such as SYS6.DB2A.DB2V51.SDSNLOAD. Maintenance and upgrades target a version-lib, and are then copied from the version-lib to the generic-lib. Users never change their JCL or other references to the libs. This approach has been reliable [...] 38778 91 34_Re: Using high capacity cartridges11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Fri, 28 Jan 2000 08:06:30 -05001411_iso-8859-1 You can copy many tablespaces in one step. Here is an example.



//* //ICOPY EXEC PGM=DSNUTILB,PARM='DB2P,PR4331IC',REGION=4M //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //* //LOC001 DD UNIT=CART,DISP=(NEW,CATLG,CATLG), // DSN=DB2PTAPE.CSD.D.D433GDDP.TGBUSELM(+1), // LABEL=(001,SL),VOL=(,RETAIN), // DCB=(MODEL.DSCB,TRTCH=COMP) //OFF001 DD UNIT=CART,DISP=(NEW,CATLG,CATLG), // DSN=BKUP.DB2PCSD.D.D433GDDP.TGBUSELM(+1), // LABEL=(001,SL),VOL=(,RETAIN), // DCB=(MODEL.DSCB,TRTCH=COMP) //* //LOC002 DD UNIT=AFF=LOC001,DISP=(NEW,CATLG,CATLG), // DSN=DB2PTAPE.CSD.D.D433GDDP.TGBUSVW(+1), // LABEL=(002,SL),VOL=(,RETAIN,,,REF=*.LOC001), // DCB=(MODEL.DSCB,TRTCH=COMP) //OFF002 DD UNIT=AFF=OFF001,DISP=(NEW,CATLG,CATLG), // DSN=BKUP.DB2PCSD.D.D433GDDP.TGBUSVW(+1), // LABEL=(002,SL),VOL=(,RETAIN,,,REF=*.OFF001), // DCB=(MODEL.DSCB,TRTCH=COMP) //* //LOC003 DD UNIT=AFF=LOC001,DISP=(NEW,CATLG,CATLG), // DSN=DB2PTAPE.CSD.D.D433GDDP.TGBVEDER(+1), // LABEL=(003,SL),VOL=(,RETAIN,,,REF=*.LOC002), // DCB=(MODEL.DSCB,TRTCH=COMP) //OFF003 DD UNIT=AFF=OFF001,DISP=(NEW,CATLG,CATLG), // DSN=BKUP.DB2PCSD.D.D433GDDP.TGBVEDER(+1), // LABEL=(003,SL),VOL=(,RETAIN,,,REF=*.OFF002), // DCB=(MODEL.DSCB,TRTCH=COMP) //* //SYSIN DD * COPY TABLESPACE D433GDDP.TGBUSELM COPYDDN LOC001 RECOVERYDDN OFF001 COPY TABLESPACE D433GDDP.TGBUSVW COPYDDN LOC002 RECOVERYDDN OFF002 COPY TABLESPACE D433GDDP.TGBVEDER [...] 38870 146 20_Re: SDSNLOAD Library11_Hayden, Lee18_Lee_Hayden@CSX.COM31_Fri, 28 Jan 2000 08:30:59 -0500494_iso-8859-1 We also do not linklist and use a similar naming convention as Dave. The only issue I have had is that we would like to not use STEPLIBS in TSO. I have been able to get DB2I to work with ISPLLIB and have attached a sample of the code. A similar technique can be used for other applications. There is an IBM APAR II07935 with more info on the technique. It does NOT work for some products (QMF for one). You can use TSOLIB for those but that must be done prior to starting ISPF. [...] 39017 16 28_Disaster recovery procedures0_21_kramps@NATIONWIDE.COM31_Fri, 28 Jan 2000 08:31:55 -0500308_us-ascii My apologies to all those who requested copies of the jcl we use to automate the updating of our BSDS during a disaster recovery exercise. I was taken ill and was out of the office for over three weeks. I will get that information together and have it sent probably next week sometime. Thanks. [...] 39034 38 45_REORG with DISCARD option: a strange behavior14_Massimo Scarpa16_mscarpa@CESVE.IT31_Fri, 28 Jan 2000 15:02:33 +0100581_us-ascii We used IBM reorg with new DISCARD option with WHERE to eliminate some rows from a table. The tablespace to be reorged was a (large) partitioned tablespace with 2 secondary indexes.

After reorg, ended ok, we had some errors with Buffer Manager. We immediately realize that the partitioned index keys where correct but in the 2 secondary indexes ALL old keys were present. In other words the keys of eliminated rows were reloaded into secondary indexes and NOT discarded, as resulted after a CHECK INDEX. After a recover of 2 secondary indexes all worked well. [...] 39073 58 42_Re: TIMESTAMP as Non-intelligent ID for PK13_Lynne Flatley17_LFlatley@NEFN.COM31_Fri, 28 Jan 2000 09:06:08 -0500360_us-ascii Michael Hannan has written several times on this listserv about why the use of non-intelligent IDs for primary keys is not a good thing...if one of the things you desire is good performance. He convinced me but I already agreed with him :-) We have several apps with tables with non-intelligent PK and they suffer death by random IO, not pretty. [...] 39132 100 38_Re: DB2 V6 to DB2 V5 Fallback - Beware12_Paul Packham28_packhamp@IT.POSTOFFICE.CO.UK31_Fri, 28 Jan 2000 14:12:50 -0000417_iso-8859-1 Hi,

Just a quick update on this saga. Yesterday we carried out a further test of our fallback procedures (without altering the data in sysstrings) & ran our IVPs... Yes, you guessed it, all worked OK this time around. Our IBM support people are in discussion with the code team regarding the correct procedure, (i.e. do you back out updates to sysstrings or not). I'll let you know the result. [...] 39233 171 44_Re: DB2 active logs and IBM Raid-1 type DASD17_Hynes, Kenneth J.17_khynes@TRIGON.COM31_Fri, 28 Jan 2000 09:08:35 -0500564_iso-8859-1 David,

The question is not only the issue of "Single Point of Failure" as regards DB2, but of potential failure during a system recovery. Your SYSPROG is correct as far as she goes relating to mirrored DASD, but she is "missing the point" of the dual backups. The first purpose is to provide "failover" during normal operation -- granted mirrored DASD helps in reducing your potential for error in that case. The second purpose is to provide two copies of your operational log during recovery -- a somewhat prudent approach don't you think? [...] 39405 18 44_Re: DB2 active logs and IBM Raid-1 type DASD15_Lyle Kelly-LH-117_kelly.lh.1@PG.COM31_Fri, 28 Jan 2000 09:29:38 -0500323_us-ascii Previous message said, "However, the sysprogs response to the single point of failure issue is that DB2 will not be effected by failure on IBM Raid-1 type DASD. She tells me that even using single DB2 active logging, she could turn one of the mirrored packs off-line and DB2 would not blink. Is she correct?" [...] 39424 47 14_Re: UDB for NT24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Fri, 28 Jan 2000 09:32:37 -0800596_us-ascii Mike, DB2 UDB V6 for Windows NT - Workgroup or Enterprise - Edition. The latter comes with DB2 Connect for host database integration. It also comes with DB2 Run Time, the Windows 95/98/NT, Unix, OS/2, Linux, Mac client software. That's it. I think Windows NT Workstation V4.0 Fixpack 5 is recommended. I don't know the specs for Windows NT Server. DB2 UDB is priced by processor. The client is free. For pricing or fulfillment, contact John Iatesta of Actium at 610-832-7335 or jiatesta@actium.com. Tell him that Frank Fillmore sent you. I don't think he'll charge you extra... ;-) [...] 39472 19 44_Re: DB2 active logs and IBM Raid-1 type DASD15_Lynette Roberts21_llroberts@KSCABLE.COM31_Fri, 28 Jan 2000 08:40:24 -0600598_- We use single-mode logging to DASD in all of our DB2 environments, including PRODUCTION for over 2 years. Prior to this time, we were using dual-logging and archiving the logs to tape. We decided to change this to single-mode logging and to archive to DASD for performance reasons. ..................... Another question to consider when choosing dual versus single logging --- Why isn't dual logging available on DB2/UDB? DB2/OS390 is the only flavor of DB2 (that I know of anyway) that even allows you to choose between dual and single logging mode. Why is it so critical on OS390 but not [...] 39492 20 40_sql function simil initcap of Oracle sql8_Giuseppe18_dedonno@ARIADNE.IT31_Fri, 28 Jan 2000 15:46:15 +0100337_us-ascii Hi, I need a similar function for DB2 sql. Do it exists?

Thanks

Giuseppe

================================================ 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. 39513 289 25_Re: Question on Utilities0_22_The-Thanh.Luong@AGF.BE31_Fri, 28 Jan 2000 15:50:25 +0100377_- Hi,

I agree with what Tim said: IBM's utilities are good. I would like to mention one more thing (for what it's worth): IBM has announced not long ago that the Infotel's technology has been adopted in its Unload utility (Infotel being a third-party vendor, same as CA/Plat or BMC). Are the other utilities (Copy, Reorg, ...) going to follow the same path? Maybe. [...] 39803 54 28_Re: needing to make contacts20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM31_Fri, 28 Jan 2000 14:53:04 -0000268_iso-8859-1 Kathleen,

Part of my job is SQL/DS DB Admin. I don't recall seeing any questions on this list in the last year or so but I expect there are a few lurkers. I'll try to help if I can. I don't think there is a list specific to this flavour of DB2. [...] 39858 32 34_SQL Performance for Report program8_madhavan16_madhavan@INF.COM31_Fri, 28 Jan 2000 20:28:41 +0530457_- Hi,

We will be coding a number of COBOL/DB2 report programs. We will be accessing tables containing around 1 million rows (normally join of 2 or 3 tables) and are expected to fetch around 1000 rows. We want to evaluate and decide on one of the following strategy.

1. Code the SQL in the COBOL program and fetch the necessary 1000 odd rows. There is no index on the columns used in the where clause and a tablespace scan will be needed. [...] 39891 42 17_stored prodedures5_Jamie14_jamie@DANG.COM31_Fri, 28 Jan 2000 10:10:07 -0500544_iso-8859-1 I'm new to DB2. I've created some simple stored procedures which work fine from the SP builder. However, when I try to call them from Java apps or the Command Center, I invariable get an error. For example, from the Command Center:

------------------------------------- Command Entered ------------------------------------- call jamie.getpages ; ---------------------------------------------------------------------------- ----------------------------- SQL0104N An unexpected token "END-OF-STATEMENT" was found following [...] 39934 40 14_Re: UDB for NT14_C. L. Phillips25_clphillips@MINDSPRING.COM31_Fri, 28 Jan 2000 10:15:13 -0500591_us-ascii Mike, In addition to Frank's suggestions, cut out this address and paste it in your browser and bookmarks:

http://commerce.www.ibm.com?cgrfnbr=1826571&cgmenbr=1&cntry=840&lang=en_US/cgi-bin/ncommerce/CategoryDisplay?cgrfnbr=1824914&cgmenbr=1&cntry=840&lang=en_US

All of the current pricing is there. [I just checked it!]. At the very bottom is the "DB2 Universal Developer's Edition"

It is definitely the one to buy to start because it has EVERYTHING. When you deploy your production server, then buy the specific license for that processor & environment. [...] 39975 13 38_Re: SQL Performance for Report program12_Dan Sullivan28_daniel.sullivan@ZURICHUS.COM31_Fri, 28 Jan 2000 09:09:26 -0600357_us-ascii Put an index on the table, use it and drop it unless your going to be doing this many times in the future.

================================================ 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. 39989 127 48_Re: EDM pool calculations and head scratching...14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Fri, 28 Jan 2000 09:28:46 -0600573_ISO-8859-1 Hugh:

You are, of course, correct. I apologize for not taking the time to review this as fully as I should have the first time. This query is better than the two outlined in the book:

SELECT AVG(COLCOUNT) FROM SYSIBM.SYSTABLES WHERE TYPE = 'T';

The COLCOUNT column contains a count of the number of rows in each table (view, alias, etc). Eliminating all but true tables with the WHERE clause and using AVG to should get the average we are looking for here. I don't recall why I used two different queries in my book. I will fix this in [...] 40117 86 28_Re: Tool to evaluate indexes16_Alison Pelletier30_APelletier@MOUNTAINWESTFIN.COM31_Fri, 28 Jan 2000 08:34:30 -0700370_iso-8859-1 Michael,

Thanks for the input. Actually my problem isn't how to design indexes thru evaluation of SQL. I've been there done that. I'd like to consider using a tool to do it faster than I can being that there are so many programs...so little time. Also I anticipate many other opportunities such as this with other applications in the near future. [...] 40204 100 44_Re: DB2 active logs and IBM Raid-1 type DASD13_Lockwood Lyon23_Lockwood_Lyon@AMWAY.COM31_Fri, 28 Jan 2000 10:46:20 -0500432_us-ascii Okay, my opinion.

"One test is worth a thousand expert opinions."

Don't spend time getting opinions, sifting through them, weighing them, etc., etc. Useful for general guidelines, but a waste of time in this case. Sysprog's opinion is important, so is yours, so are those of other practitioners in the field, so is IBM's, etc., etc. Forget the opinions! This is a relative waste of time. Test the thing! [...] 40305 114 62_Re: Consolidating VSAM extents on DSNDB01 and DSNDB06 datasets11_rick creech18_ykcirc@HOTMAIL.COM29_Fri, 28 Jan 2000 07:48:06 PST559_- FYI, What I have always done in a maintenance window is a delete/define, followed by the install initialize for that part of dsndb06/dsndb01, and then followed by a recover. Of course, I start with a new imagecopy and stop the object before the delete/define. If the object is a tablespace, I also recover the associated indexes. I have done this many times with every part of the catalog and directory in both test and production and have never had any problems. (It's also good practice for certain disaster recovery scenarios.) Regards, Rick Creech [...] 40420 333 25_Re: Question on Utilities0_19_Tim.Lowe@STPAUL.COM31_Fri, 28 Jan 2000 09:47:56 -0600397_us-ascii I heard at the last DB2 technical conference that IBM will be righting a new DB2 unload utility, It sounded as though the unload utility they bought (from Infotel?) may only be a temporary solution, not a long-term strategy. But, that was only my impression.

Regards, Tim













The-Thanh.Luong@AGF.BE on 01/28/2000 08:50:25 AM [...] 40754 211 55_Re: Listing Databases for an Instance (UDB v6.1 on AIX)13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Fri, 28 Jan 2000 09:46:36 -0600652_US-ASCII Sorry I should have said this work for "LOCAL" administration. Kurt

>>> "ALLEN,Bruce" 01/27/00 04:18PM >>> Hi, this looks real simple on our site. This is what I see for DB2 UDB v6.1 on NT (no fixpacks). 1. list db directory shows all databases in a different machine or instance as 'Directory Entry Type=Remote'. For a remote db entry, the list node directory shows local instances as 'Protocol=Local'. This entry was catalogued by the control centre. 2. If I start control Centre (in the default instance), I can add other local instances, and catalog the databases under those instances. [...] 40966 100 20_Re: SDSNLOAD Library14_Harvey Wachtel22_babybaby@UAPC.CUNY.EDU31_Fri, 28 Jan 2000 11:00:34 -0500288_us-ascii We use a similar system, with DB2.CUR. as the prefix for whatever the current version is, DB2.OLD. for the previous and 'DB2.NEW' for the newest version being tested. However, we do linklist to CUR and do most of our testing on a test system where the new version is "CUR". [...] 41067 55 42_Re: TIMESTAMP as Non-intelligent ID for PK11_James Drewe20_James.Drewe@AEXP.COM31_Fri, 28 Jan 2000 08:32:26 -0700549_- Rick

I have a set of logging tables that I have used TIMESTAMP as the high order column in the primary key. It has actually worked very well (in production for about two years). There is a minimum of locking and the rows go in pretty much in clustering sequence (therefore less of a requirement for reorg). On the negative side, subsequent read access has to be done with a NPI ) so I incur the cost of that additional I/O at insert time. I cannot speak for portability issues since this part of the application is strictly DB2/OS390. [...] 41123 40 20_Re: SDSNLOAD Library13_John Arbogast16_jfarbo@YAHOO.COM31_Fri, 28 Jan 2000 08:15:55 -0800541_us-ascii We also use LLA and linklisted libs for our load code. Similar reasons as Jeff.

--- Jeffery A Price wrote: > We use LLA to manage our SDSNLOAD library. We are > still able to use steplib. > Using LLA saved us a great amount of time on I/O to > steplib, as well as overhead > on unneeded SVC (BUILDL, etc.). One job used to do > 1,000,000 I/Os to steplib > before LLA, now does about 65. > > Best wishes, > > Jeffery A. Price > Associate Engineer > Indianapolis Power and Light Co > jprice@ipalco.com [...] 41164 243 25_Re: Question on Utilities18_frank stadankowitz23_fstadankowitz@YAHOO.COM31_Fri, 28 Jan 2000 08:24:04 -0800564_us-ascii Thanks for every one's opinions. If anyone wants to respond further off the list, you can e-mail me directly.

Frank.

--- Tim.Lowe@STPAUL.COM wrote: > I heard at the last DB2 technical conference that > IBM will be righting a new DB2 > unload utility, It sounded as though the unload > utility they bought (from > Infotel?) may only be a temporary solution, not a > long-term strategy. But, that > was only my impression. > > Regards, > Tim > > > > > > > > The-Thanh.Luong@AGF.BE on 01/28/2000 08:50:25 AM > > Please respond to DB2 Data [...] 41408 58 49_Re: REORG with DISCARD option: a strange behavior13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Fri, 28 Jan 2000 10:37:22 -0600631_US-ASCII Max, Take a look at UQ31380. Kurt

>>> Massimo Scarpa 01/28/00 08:02AM >>> We used IBM reorg with new DISCARD option with WHERE to eliminate some rows from a table. The tablespace to be reorged was a (large) partitioned tablespace with 2 secondary indexes.

After reorg, ended ok, we had some errors with Buffer Manager. We immediately realize that the partitioned index keys where correct but in the 2 secondary indexes ALL old keys were present. In other words the keys of eliminated rows were reloaded into secondary indexes and NOT discarded, as resulted after a CHECK INDEX. After [...] 41467 58 49_Re: REORG with DISCARD option: a strange behavior13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Fri, 28 Jan 2000 10:42:11 -0600633_US-ASCII Max also UQ32866. Kurt

>>> Massimo Scarpa 01/28/00 08:02AM >>> We used IBM reorg with new DISCARD option with WHERE to eliminate some rows from a table. The tablespace to be reorged was a (large) partitioned tablespace with 2 secondary indexes.

After reorg, ended ok, we had some errors with Buffer Manager. We immediately realize that the partitioned index keys where correct but in the 2 secondary indexes ALL old keys were present. In other words the keys of eliminated rows were reloaded into secondary indexes and NOT discarded, as resulted after a CHECK INDEX. After a recover of [...] 41526 50 21_Re: stored prodedures12_Daniel Kirby25_daniel.kirby@CITICORP.COM31_Fri, 28 Jan 2000 11:48:50 -0500732_us-ascii This may seems a bit simplistic, but it seems to be objecting to the "." in the stored procedure name. Try to rename it with an underscore, as in "jamie_getpages".

Jamie wrote:

> I'm new to DB2. I've created some simple stored procedures which work fine > from the SP builder. However, when I try to call them from Java apps or the > Command Center, I invariable get an error. For example, from the Command > Center: > > ------------------------------------- Command > Entered ------------------------------------- > call jamie.getpages ; > ---------------------------------------------------------------------------- > ----------------------------- > SQL0104N An unexpected token "END-OF-STATEMENT" was [...] 41577 48 44_Re: DB2 active logs and IBM Raid-1 type DASD0_14_dcreed@CSC.COM31_Fri, 28 Jan 2000 10:51:15 -0600318_us-ascii Just for everyone to understand the arguments, there is a paper entitled "DB2 and RAID" at http://www.citl.co.uk/ done in 1997 by CITL. No opinion expressed (although that this is my first time not to express one). Also some other information for those that like to read up on some things. Regards Danny [...] 41626 161 44_Re: DB2 active logs and IBM Raid-1 type DASD13_John Arbogast16_jfarbo@YAHOO.COM31_Fri, 28 Jan 2000 08:56:34 -0800451_us-ascii We had a recent outage of a RAID device caused by our building maintenance people knocking out power to 1/2 of the device. The whole device went down. We have not determined why the entire device failed yet. Luckily, it was not a DB2 pack.

What would happen if you lost your device in single log mode? Would DB2 just move to the next Dataset? And then skip the down device if DB2 logging came back around before it was recovered? [...] 41788 71 21_Re: stored prodedures14_O'Conner, John26_john.oconner@COURTS.WA.GOV31_Fri, 28 Jan 2000 09:10:31 -0800407_iso-8859-1 Try

call jamie.getpages() ;

-----Original Message----- From: Daniel Kirby [mailto:daniel.kirby@CITICORP.COM] Sent: Friday, January 28, 2000 8:49 AM To: DB2-L@RYCI.COM Subject: Re: stored prodedures



This may seems a bit simplistic, but it seems to be objecting to the "." in the stored procedure name. Try to rename it with an underscore, as in "jamie_getpages". [...] 41860 46 14_Re: UDB for NT0_15_leon@CA.IBM.COM31_Fri, 28 Jan 2000 12:21:27 -0500416_us-ascii I am assuming that you have been asked to set up a database server on Windows NT and not a private database. There are 3 types of database servers that we offer for Windows NT platform: DB2 UDB Workgroup Edition DB2 UDB Enterprise Edition DB2 UDB Extended Enterprise Edition

Which one is most appropriate for your environmnet will depend on what you are planning to do with the database server. [...] 41907 58 21_Re: stored prodedures18_Jamie Orchard-Hays14_jamie@DANG.COM31_Fri, 28 Jan 2000 12:23:05 -0500562_us-ascii thanks, but I get the same result with "call getpages".

>This may seems a bit simplistic, but it seems to be objecting to the "." in the stored procedure name. Try to rename it with an underscore, as in "jamie_getpages". > >Jamie wrote: > > > I'm new to DB2. I've created some simple stored procedures which work fine > > from the SP builder. However, when I try to call them from Java apps or the > > Command Center, I invariable get an error. For example, from the Command > > Center: > > > > ------------------------------------- Command [...] 41966 83 21_Re: stored prodedures18_Jamie Orchard-Hays14_jamie@DANG.COM31_Fri, 28 Jan 2000 12:23:45 -0500620_us-ascii Thanks, I tried that already, but it gives an error as well.

Jamie

>Try > >call jamie.getpages() ; > >-----Original Message----- >From: Daniel Kirby [mailto:daniel.kirby@CITICORP.COM] >Sent: Friday, January 28, 2000 8:49 AM >To: DB2-L@RYCI.COM >Subject: Re: stored prodedures > > >This may seems a bit simplistic, but it seems to be objecting to the "." in >the stored procedure name. Try to rename it with an underscore, as in >"jamie_getpages". > >Jamie wrote: > > > I'm new to DB2. I've created some simple stored procedures which work fine > > from the SP builder. However, when I try to [...] 42050 78 44_Re: DB2 active logs and IBM Raid-1 type DASD19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 28 Jan 2000 11:22:40 -0600617_iso-8859-1 David, I'm talking about a controller failure, not volume within a controller.

Joel, Can an IBM's, or any other vendor's RAID-1 controller mirror a volume to a volume in a different controller?

Confused, 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 [...] 42129 109 21_Re: stored prodedures10_Rao, Peavy26_Peavy.Rao@TRANSAMERICA.COM31_Fri, 28 Jan 2000 11:33:37 -0600615_- Does the documentation say, you can execute Stored Procedure from Command Center ? I use MS Access to execute SPs on main frame....

> ---------- > From: Jamie Orchard-Hays[SMTP:jamie@DANG.COM] > Reply To: DB2 Data Base Discussion List > Sent: Friday, January 28, 2000 11:23 AM > To: DB2-L@RYCI.COM > Subject: Re: stored prodedures > > Thanks, I tried that already, but it gives an error as well. > > Jamie > > >Try > > > >call jamie.getpages() ; > > > >-----Original Message----- > >From: Daniel Kirby [mailto:daniel.kirby@CITICORP.COM] > >Sent: Friday, January 28, 2000 8:49 AM > >To: DB2-L@RYCI.COM [...] 42239 111 42_Re: TIMESTAMP as Non-intelligent ID for PK19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 28 Jan 2000 11:37:02 -0600608_iso-8859-1 Lynn, I appreciate your comments and agree with you and Michael. A term frequently used that describes Michael's position is Natural Key vs. a Surrogate Key. The following definitions make sense to you? Natural Key = one or more attributes that uniquely identifies every occurrence in an entity. Non-intelligent ID (NID) = a value that has no "hidden" or "internal" meaning. A NID helps support the concept of a self-describing database by avoiding "smart columns or keys". "Smart column or key" ex., PIC X(5) field where each byte has a meaning. 01 Key. 05 SmartKey PIC X(5). 10 ByteOne PIC [...] 42351 305 25_Re: Question on Utilities11_Thomas Bird21_tbird@TJBVENTURES.COM31_Fri, 28 Jan 2000 10:22:46 -0800684_us-ascii I agree. The Infotel technology connection with IBM is a strong sign of what may come.

Regards,

T. Bird President TJB Ventures, Inc. Voice: (707) 226-1100 Fax: (707) 226-1188 Email: tbird@tjbventures.com Web: http://www.tjbventures.com





|--------+-----------------------> | | The-Thanh.Luo| | | ng@AGF.BE | | | | | | 01/28/00 | | | 06:50 AM | | | Please | | | respond to | | | DB2 Data Base| | | Discussion | | | List | | | | |--------+-----------------------> >-----------------------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: (bcc: Thomas J Bird/IDI) | | Subject: Re: Question on Utilities | [...] 42657 26 8_Net.Data5_Kiran23_hmkiran@WIPSYS.SOFT.NET31_Fri, 28 Jan 2000 12:06:36 -0600393_- Hi ,

We are trying to configure IBM DB2 Net.Data on Sun solaris with Netscape Enterprise WebServer . The necessary updations have been made to the obj.conf file . The LD_LIBRARY_PATH is also set. We have got the macro to execute , but it fails when its trying to work on the Function DTW_SQL which connects to DB2 . Below is the error msg . Has anybody had similar experiences ? [...] 42684 129 44_Re: DB2 active logs and IBM Raid-1 type DASD19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 28 Jan 2000 12:43:23 -0600647_iso-8859-1 David, Provided no controller problems, your "?sysprog?" is correct. Now ask where all your active logs are. Same controller?

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 [...] 42814 177 42_Re: TIMESTAMP as Non-intelligent ID for PK11_Eric Robida22_Eric_Robida@BCBSME.COM31_Fri, 28 Jan 2000 13:41:25 -0500565_us-ascii Hopefully my ignorance does become too apparent here. I was lucky enough to be able to attend IDUG last May. I attended a session by Michael Lonigro where he discussed using blind surrogate keys in table design, this seems to be the same as the NID surrogate key below. Now, for the disclaimer, I'm an applications programmer and I've never tried it. Anyway he proposed using an integer as a NID surrogate. Mainly due to size, about 4 bytes and it gives you about 2 billion positive values. This key would be the primary key and it's only purpose was [...] 42992 137 21_Re: stored prodedures14_Philip Gunning20_pgunning@BOSCOVS.COM31_Fri, 28 Jan 2000 15:22:36 -0500501_us-ascii The details and sample client programs/calls are in the DB2 UDB Application Development Guide, SC09-2845-00 and Application Building Guide SC09-2842-00. Phil

Philip K. Gunning DB2 DBA IBM Certified Advanced Technical Expert -- DB2 UDB DRDA IBM Cetfified Solutions Expert -- CICS TS Assoc List Owner DB2-L

-----Original Message----- From: Rao, Peavy [SMTP:Peavy.Rao@TRANSAMERICA.COM] Sent: Friday, January 28, 2000 12:34 PM To: DB2-L@RYCI.COM Subject: Re: stored prodedures [...] 43130 49 44_Re: DB2 active logs and IBM Raid-1 type DASD19_DAVIS, RICK (SBCSI)21_RD8246@MOMAIL.SBC.COM31_Fri, 28 Jan 2000 14:31:21 -0600620_iso-8859-1 Lynette, Does either of your UNIX or NT boxes have more than one disk controller?

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 [...] 43180 73 14_Re: UDB for NT24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Fri, 28 Jan 2000 16:04:28 -0800401_us-ascii Catherine, A few things changed in V6. Client Application Enabler (CAE) became DB2 Run Time. I don't *think* there are any charges for concurrent connections to a UDB database from DB2 RT, just for the number of server processors. DB2 Universal Developer's Edition is a great idea, but read the license... I *think it is targeted at ISVs and consultants rather than in-house developers. [...] 43254 118 28_Deadlock and isolation level10_Milos Flek17_milos.flek@FSP.CZ31_Fri, 28 Jan 2000 21:55:17 +0100316_iso-8859-2 Hello, can you help me with following problem (DB2 UDB 5.2 NT)?

Two applications, SQL commands sequence like this

Appl 1/ update Xxx col=0 where id=10

Appl 2/ select * from Xxx where id > 10 /* causes deadlock */ Appl 2/ select * from Xxx where id > 10 and id < 1000000 /* OK */ [...] 43373 21 13_Locking Error12_Bill Johnson17_wjjohnso@KENT.EDU31_Fri, 28 Jan 2000 16:35:14 -0500538_us-ascii List, We are getting the following message in a batch program:

LOCKING ERROR ON SIS_OAR_REJECT_TB SQLCODE: -805 SQLSTATE: 51002 PERFORMING ROLLBACK TO LAST COMMIT POINT ABORTING PROGRAM SBSRI1

Can anybody point us in the right direction. DB2 Version 4. Thanks, Bill Johnson

================================================ 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. 43395 44 17_Re: Locking Error0_20_ddlusk@HOUSEHOLD.COM31_Fri, 28 Jan 2000 15:28:34 -0600594_us-ascii Did you recompile the program recently, if you did you must rebind the program







Bill Johnson 01/28/2000 03:35 PM



Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM cc: (bcc: Derez D. Lusk/Household International) Subject: Locking Error





List, We are getting the following message in a batch program:

LOCKING ERROR ON SIS_OAR_REJECT_TB SQLCODE: -805 SQLSTATE: 51002 PERFORMING ROLLBACK TO LAST COMMIT POINT ABORTING PROGRAM SBSRI1 [...] 43440 48 17_Re: Locking Error12_Bill Johnson17_wjjohnso@KENT.EDU31_Fri, 28 Jan 2000 16:45:34 -0500662_us-ascii It is a new program and was compiled and bound today. Thanks







At 03:28 PM 1/28/00 -0600, you wrote: >Did you recompile the program recently, if you did you must rebind the program > > > > >Bill Johnson >01/28/2000 03:35 PM > > >Please respond to DB2 Data Base Discussion List > > > >To: DB2-L@RYCI.COM >cc: (bcc: Derez D. Lusk/Household International) >Subject: Locking Error > > > >List, We are getting the following message in a batch program: > >LOCKING ERROR ON SIS_OAR_REJECT_TB SQLCODE: -805 SQLSTATE: 51002 >PERFORMING ROLLBACK TO LAST COMMIT POINT >ABORTING PROGRAM SBSRI1 [...] 43489 20 14_Re: UDB for NT11_Thomas Bird21_tbird@TJBVENTURES.COM31_Fri, 28 Jan 2000 14:01:21 -0800407_us-ascii dRegards,

T. Bird President TJB Ventures, Inc. Voice: (707) 226-1100 Fax: (707) 226-1188 Email: tbird@tjbventures.com Web: http://www.tjbventures.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. 43510 21 14_Re: UDB for NT11_Thomas Bird21_tbird@TJBVENTURES.COM31_Fri, 28 Jan 2000 14:41:56 -0800408_us-ascii d Regards,

T. Bird President TJB Ventures, Inc. Voice: (707) 226-1100 Fax: (707) 226-1188 Email: tbird@tjbventures.com Web: http://www.tjbventures.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. 43532 86 34_Trigger calling a Stored Procedure12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Fri, 28 Jan 2000 17:29:40 -0600331_us-ascii Hello. DB2 V6.1 OS/390 V2R6 here!

Any thoughts as to why my "Before" Trigger wouldn't apply the changes made to columns by a Call to a Stored Procedure? My Cobol Stored Procedure is receiving the values and altering them; before and after "Displays" (seen in the SPAS) confirm this.

Here's the Trigger: [...] 43619 27 24_Re: When is big too big?10_Paul Wendt14_paulw@ENET.COM31_Fri, 28 Jan 2000 16:07:48 -0800586_- Earlier this week, I posted to the list. Evidently there was a problem where my "user name" (Paul Wendt) was appended to some local server names, making it appear that my message was coming the local server, rather than an external email address.

I eventually found that the RYCI listserver had me registered as "", rather than "Paul Wendt". The angle brackets denote an actual email address, which seems to have confused some email servers. I have changed the listserver registry to eliminate the angle brackets, which hopefully should eliminate the problem. [...] 43647 50 17_Re: Locking Error0_24_ssethi@LOT.TATASTEEL.COM29_Sat, 29 Jan 2000 03:01:04 GMT404_us-ascii Hi Bill, I have referred the DB2 Message and Code guide after looking at sqlcode -805 and found out that package for this sql code doesn't exists.Please check ur packages and collection and see whether ur plan is pointing to the correct collection of packages.

Thanks

Sanjeev







Bill Johnson @RYCI.COM> on 01/29/2000 03:05:14 AM [...] 43698 76 38_Re: SQL Performance for Report program0_24_ssethi@LOT.TATASTEEL.COM29_Sat, 29 Jan 2000 02:53:54 GMT576_us-ascii Hi Madhavan,

I think it is more of a physical design issue rather than anything else.Try identifying the candidate columns for creating the index. So the only thing which is to be taken care is no online application should do massive update (on the columns of the index which u may create) and insert on that tables because that might cause online application to have poor response time due to index building overhead. If DB2 has given such a good facility of quering the data in an effective manner then there is no point going for flat file technique , [...] 43775 33 39_Resending : Locks on DBD and Tablespace0_24_ssethi@LOT.TATASTEEL.COM29_Sat, 29 Jan 2000 03:05:41 GMT585_us-ascii I am resending this question as i think it is not properly distribute because i couldn't receive any reply.

Hi All, When we execute a DDL (say , to create table in a tablespace) then DBD for corresponding database is locked and when u issue the DB2 Command to -DISPLAY DB(dbname) SPACE(tsname) then DB2 shows that tablespace is also locked in the exclusive mode .My question is why should DB2 exclusively lock the tablespace if DBD is locked.I think this is unneccessary use of the locks and the CPU.As i understand no objects can be accessed via. DB2 without DBD [...] 43809 16 18_Previlege question12_Ranjay Sinha24_rsinha@LOT.TATASTEEL.COM29_Sat, 29 Jan 2000 04:55:55 GMT424_us-ascii Hello What is the minimum previlege required by an primay id or secondary id so that it can grant select, insert,update,delete on any tables or views within a DB2 subsystem.

================================================ 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. 43826 41 22_Re: Previlege question0_24_ssethi@LOT.TATASTEEL.COM29_Sat, 29 Jan 2000 05:14:35 GMT589_us-ascii It will be grant "anything Possible" with grant option

Sanjeev









Ranjay Sinha @RYCI.COM> on 01/29/2000 10:25:55 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Previlege question



Hello What is the minimum previlege required by an primay id or secondary id so that it can grant select, insert,update,delete on any tables or views within a DB2 subsystem. [...] 43868 12 22_Re: Previlege question0_24_ujjwal@LOT.TATASTEEL.COM29_Sat, 29 Jan 2000 05:26:07 GMT256_us-ascii DBADM

================================================ 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. 43881 12 22_Re: Previlege question0_24_ujjwal@LOT.TATASTEEL.COM29_Sat, 29 Jan 2000 05:29:59 GMT290_us-ascii Sorry. Please ignore the previous mail.

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