1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l May 2001, week 5 2 20 38_STORED PROCEDURE BUILDER Documentation11_Dimoka Popy24_dimokap@INTERAMERICAN.GR31_Tue, 29 May 2001 09:45:03 +0300458_- Hello List! Where can i find sufficient documentation concerning the DB2 STORED PROCEDURE BUILDER? I mainly want to create some SPs on our host DB2 system. Regards,

Popy A. Dimoka Interdata S.A Athens,Greece

================================================ 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. 23 32 45_Re: how to recover the BSDS(emergency!!!!!!!)10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 29 May 2001 09:02:12 +0200419_us-ascii If you have some archive logs you can easily recover (after a define cluster of BSDSs) via REPRO . the name of input file (archive) is:

Your_high_level_qualifier.ARCLOGx.Dyyjjj.Thhmmsst.Bxxxxxx.

jjj=julian day yy=year hhmmsst = hour, minutes, seconds xxxxxx=file sequence

Probably you'll need a CRC restart (o cold restart if u desire).

If all it's ok beat your collegue...... [...] 56 16 55_Christopher I Schmidt/Houston/IBM is out of the office.21_Christopher I Schmidt18_cschmid@US.IBM.COM31_Tue, 29 May 2001 01:10:01 -0600461_us-ascii I will be out of the office starting May 29, 2001 and will not return until May 31, 2001.

I will be reachable by cell if there is an emergency. I will respond to all emails when I get back to the office.

================================================ 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. 73 25 23_Re: DSNREXX performance11_Rolf Merkle21_rolf.merkle@DVG-KA.DE31_Tue, 29 May 2001 02:30:38 -0500349_- Hello Isaac, we observed the same poor performance of DSNREXX with DB2 5.1. We opened a problem record. We sent traces and monitor out to IBM. They examined this material for weeks. At the end they said "DSNREXX works as designed" and because it's free of charge we have take it as it is. If we need performance we should take COBOL or PL/I. [...] 99 49 42_AW: STORED PROCEDURE BUILDER Documentation12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Tue, 29 May 2001 10:12:37 +0200803_iso-8859-1 Hi Popy,

try the following URL:

http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg245485.html?Open

Regards

Thomas



> -----Ursprüngliche Nachricht----- > Von: Dimoka Popy [mailto:dimokap@INTERAMERICAN.GR] > Gesendet am: Dienstag, 29. Mai 2001 08:45 > An: DB2-L@RYCI.COM > Betreff: STORED PROCEDURE BUILDER Documentation > > Hello List! > Where can i find sufficient documentation concerning the DB2 STORED > PROCEDURE BUILDER? > I mainly want to create some SPs on our host DB2 system. > Regards, > > Popy A. Dimoka > Interdata S.A > Athens,Greece > > ================================================ > To change your subscription options or to cancel your > subscription visit the DB2-L webpage at > http://www.ryci.com/db2-l. The [...] 149 49 45_Re: how to recover the BSDS(emergency!!!!!!!)16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Tue, 29 May 2001 09:27:38 +0100342_us-ascii Larry,

in order to recover your BSDS, you need to have a good log archive situation. you can recover via a REPRO and then do a START COLD or CRC restart, I am now fishing through the manuals to find the syntax of the input file (archive) you have to use, because it's tricky, using julian day, year and time in the name. [...] 199 57 32_provide UDF cobol sample program19_=?big5?B?tPa90qr4?=21_A000896@MS.SKH.ORG.TW31_Tue, 29 May 2001 16:36:42 +0800312_big5 DEAL ALL : I use OS/390 and DB2 v6 environmet , and try to UDF (user define function) someone can provide sample program (use COBOL language) for client and server , because DB2 application programming and sql guide book have no provide complete sample program

thank S .Y TANG





257 50 34_USER DEFINE FUNCTION DOCUMENTATION19_=?big5?B?tPa90qr4?=21_A000896@MS.SKH.ORG.TW31_Tue, 29 May 2001 16:55:06 +0800227_big5 > Hello List! > Where can i find sufficient documentation concerning the DB2 USER DEFINE FUNCTION , how to use cobol language get UDF > I mainly want to create some UDF on our host DB2 system. > Regards, > S Y TANG

308 61 36_AW: provide UDF cobol sample program12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 29 May 2001 11:02:20 +0200445_big5 The following is a simplified version of the CREATE FUNCTION statement that could be used to register a UDF to DB2:

CREATE FUNCTION DAYSINMONTH(DATE) RETURNS INTEGER EXTERNAL NAME 'DAYMTH' LANAGUAGE COBOL;

This statement creates a UFDF named DAYSINMONTH, with one parameter of DATE data type that returns a single value of INTEGER data type. The external name for the function program is DAYMTH and it is coded in Cobol. [...] 370 28 34_Sample Procedure call from trigger13_Swamy, Ritesh30_Ritesh.Swamy@BLR.HPSGLOBAL.COM31_Tue, 29 May 2001 15:15:35 +0500335_- Dear List Members,

I am trying to write some triggers from which I nned to make some procedure calls. Can anybody provide me with such sample. We are using DB2 7.1 on Solaris.

Also can we directly use SQLSTATE to check errors inside the trigger body. If yes could you please provide some example of these as well. [...] 399 108 23_Re: DSNREXX performance17_Massimo Biancucci30_Massimo.Biancucci@T-SYSTEMS.IT31_Tue, 29 May 2001 13:06:16 +0100373_us-ascii In our shop we use a third part product called RXDB2 to interface DB2 from REXX also we have DSNREXX too. I measured difference between the two products with a simple table of about 1200 records. OK, RXDB2 looks about double faster than DSNREXX but DSNREXX need only 1.5 seconds to perform run !! I don't know where can be the differences with your test .... [...] 508 81 31_DSN1COPY and OBIDXLAT parameter13_Judi Westcott23_judi_westcott@YAHOO.COM31_Tue, 29 May 2001 06:08:16 -0500410_- Hello fellow listers!

We are trying to restore a tablespace from an imagcopy where structural changes involving dropping the table and recreating it were made. The structural changes have been reversed and it is back to its original version.

We are using the DSN1COPY utility to do the restore and are having a few problems (this is the first time we have attempted this type of restore). [...] 590 250 69_Linda Claussen presenting at SIRDUG (Charlotte, NC) Thursday, June 2111_Paul Turpin32_pault@ALUMMAIL.SSB.ROCHESTER.EDU31_Tue, 29 May 2001 06:10:01 -0500631_ISO-8859-1 We are very pleased to announce that the June 21st SIRDUG Educational Seminar will feature Linda Claussen of Claussen & Associates, Inc. Linda is well known for her DB2 seminars. Linda’s seminar is entitled "DB2 Stored Procedures - Developing Stored Procedures in COBOL for OS/390".

Last year, Linda gave 3 short technical sessions, one of which was on Stored Procedures and DRDA. That Stored Procedure technical session last year was a very abbreviated version of the seminar she will be presenting at the June 21th meeting. The full outline of the seminar is listed later in this announcement. The seminar [...] 841 52 21_Re: from os/390 to NT13_Tzachi Nissim21_tzachi@ATTUNITY.CO.IL31_Tue, 29 May 2001 14:38:16 +0200428_iso-8859-1 Hi Silvia,

I encourage you to look at Attunity Connect to accomplish your task. Attunity Connect allows you to connect to or from your os390 with any database. So you can easily write a COBOL program on MVS to access SQL server, MS-Access, DB2, or whatever database you have on your NT. You can also write SQL statements to join your DB2/OS390 data with your NT data, move data between the two and so on. [...] 894 35 38_AW: USER DEFINE FUNCTION DOCUMENTATION12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Tue, 29 May 2001 13:47:10 +0200611_big5 Hi,

did you already read the Application Programming and SQL Guide (chapter 4-3)

Document Number SC26-9004-01

HTH

Thomas



-----Ursprungliche Nachricht----- Von: A000896@MS.SKH.ORG.TW [mailto:A000896@MS.SKH.ORG.TW] Gesendet am: Dienstag, 29. Mai 2001 10:55 An: DB2-L@RYCI.COM Betreff: USER DEFINE FUNCTION DOCUMENTATION Wichtigkeit: Hoch

> Hello List! > Where can i find sufficient documentation concerning the DB2 USER DEFINE FUNCTION , how to use cobol language get UDF > I mainly want to create some UDF on our host DB2 system. > Regards, > S Y TANG [...] 930 73 45_Re: how to recover the BSDS(emergency!!!!!!!)12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Tue, 29 May 2001 07:51:42 -0400722_us-ascii did you loose both copies of bsds?







Aurora Dell'Anno @RYCI.COM> on 05/29/2001 04:27:38 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: how to recover the BSDS(emergency!!!!!!!)



Larry,

in order to recover your BSDS, you need to have a good log archive situation. you can recover via a REPRO and then do a START COLD or CRC restart, I am now fishing through the manuals to find the syntax of the input file (archive) you have to use, because it's tricky, using julian day, year and [...] 1004 115 79_RSVP Reminder - June 18th - Pittsburgh DB2 User Group Meeting - Agenda attached10_Cathy Peck23_cathy.peck@HIGHMARK.COM31_Tue, 29 May 2001 06:42:43 -0400354_iso-8859-1 Below is the agenda for the kickoff meeting for the Pittsburgh DB2 Users Group. Please note, there's been a slight chg to the agenda. During a sponsored lunch, IBM will be giving a presentation on their IBM DB2 for OS/390 Utilities/Tools.

If you have not already registered, please RSVP if you are planning to attend the 6/18 mtg. [...] 1120 105 35_Re: DSN1COPY and OBIDXLAT parameter16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Tue, 29 May 2001 07:59:24 -0400625_us-ascii Hi, I believe 'reset' option in exec statement would help. Tom F







Judi Westcott on 05/29/2001 07:08:16 AM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: DSN1COPY and OBIDXLAT parameter





Hello fellow listers!

We are trying to restore a tablespace from an imagcopy where structural changes involving dropping the table and recreating it were made. The structural changes have been reversed and it is back to its original version. [...] 1226 43 18_Re: Data Migration13_Tzachi Nissim21_tzachi@ATTUNITY.CO.IL31_Tue, 29 May 2001 15:04:27 +0200401_iso-8859-1 Since I've seen replies from Striva and Princeton Softech I might as well jump in too. If it is a one-time migration you want to do, I'm not sure I would bother with any 3rd party tools. If you are planning to have a longer transition in which both databases are live and you want to access both, I would recommend that you look at Attunity's products, in particular Attunity Connect. [...] 1270 102 35_Re: DSN1COPY and OBIDXLAT parameter5_larry17_elibs@NETEASE.COM31_Wed, 30 May 2001 21:28:56 +0800649_gb2312 hi,I think you omit the 'RESET' parameter Larry

----- Original Message ----- From: "Judi Westcott" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, May 29, 2001 7:08 PM Subject: DSN1COPY and OBIDXLAT parameter



> Hello fellow listers! > > We are trying to restore a tablespace from an imagcopy where structural > changes involving dropping the table and recreating it were made. The > structural changes have been reversed and it is back to its original > version. > > We are using the DSN1COPY utility to do the restore and are having a few > problems (this is the first [...] 1373 59 45_Re: how to recover the BSDS(emergency!!!!!!!)0_17_cflink@AWGINC.COM31_Tue, 29 May 2001 08:51:48 -0500387_us-ascii your last archive log arclogx.bxxxxxxx should have a copy of your bsds on it..... you can use ams (idcams) to restore it ... your would then need to print it and verify that everything is ok.





larry cc: Sent by: DB2 Subject: how to recover the Data Base BSDS(emergency!!!!!!!) Discussion List [...] 1433 43 45_Re: how to recover the BSDS(emergency!!!!!!!)11_Bikash Paul19_bikash_db@YAHOO.COM31_Tue, 29 May 2001 06:55:27 -0700418_us-ascii Hi, If you have archive log and it is kept in TAPE. Take the latest archive log and sl 1 will have a copy of BSDS. You can do a repro to BSDS dataset after creating it new. Then do a print of archive logdataset, find out the latest checkpoint and do a cold start with the same RBA.. Doing this, it will loose recoverability of all the activities happened since the highest RBA in the latest archive log. [...] 1477 22 11_Re: segsize12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Tue, 29 May 2001 08:51:51 -0500432_iso-8859-1 Thanks for the responses - I did discover that we have the tables structured poorly. We are not taking advantage of the way most queries are run against the tables. At design time we thought one thing, but in reality they query most often in another manner. In answer to other questions these tables are in an adhoc query environment with the customers using Qmf for Windows. So I will re-structure the tables, etc. [...] 1500 117 45_Fw: how to recover the BSDS(emergency!!!!!!!)5_larry17_elibs@NETEASE.COM31_Wed, 30 May 2001 21:41:56 +0800441_gb2312 Thanks for all kind help .

Today I got straight the cause of why my db2 cannot start,it is not the fault of the destroyed BSDS.

one of my collegues wanted to rollback some data in the log which were commited by one job.the way he made out is to get the RBA of the job by DSN1LOGP , wrote it in the BSDS,and then conditionally restart DB2.therefore Disater came.DB2*MSTR indicated that DB2 directory was unvailable. [...] 1618 29 35_Re: DSN1COPY and OBIDXLAT parameter13_Judi Westcott23_judi_westcott@YAHOO.COM31_Tue, 29 May 2001 08:41:03 -0500372_- Hi

Two people have mentioned the RESET parameter.

My reading of this parameter however indicates that this need only be specified if the DB2 table space you are building is processed on a DB2 subsystem with a different recovery log than the source system. This is not the case. It is being recovered to the same DB2 subsystem and the same tablespace. [...] 1648 18 35_migrate from DB2 32 bits to 64 bits11_Richard Yan23_dhspa3f@DHS.STATE.IL.US31_Tue, 29 May 2001 08:58:48 -0500518_- Hi List,

I'd like to hear from who migrated their production database from DB2 32 bits to 64 bits on AIX platform(4.3.3). Did you get any troubles after the migration? What noticable improvement did you see?

Thanks ahead. Richard Yan IL dept of Human Services.

================================================ 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. 1667 145 35_Re: DSN1COPY and OBIDXLAT parameter11_Bikash Paul19_bikash_db@YAHOO.COM31_Tue, 29 May 2001 07:20:44 -0700339_us-ascii Hi, I have not used DSN1COPY with image copy for quite some time. As far as i remember, the obid will be there in datapages. And if tablespace has multiple tables, all the obids should be used as input to DSN1COPY. But you seem to find out the OBID from header page. Can u please tell us which version of DB2, you are using? [...] 1813 74 49_Re: Fw: how to recover the BSDS(emergency!!!!!!!)11_Bikash Paul19_bikash_db@YAHOO.COM31_Tue, 29 May 2001 07:27:29 -0700557_us-ascii Hi Larry, You are right. DB2 can not rollback the commited data if you do a conditional restart with prior RBA. If a table needs to be recovered to some RBA, he can always RECOVER TO RBA utility rather than making a conditional restart of DB2.

Dont you take backup of DB2 directory everyday?. Generally we do quiesce all the tables together for application, Do force archive of active logs, then take backup of directory and catalogs. Then way, it becoms straight forward. We can build everything from the back and all will be in sync. [...] 1888 67 11_Re: segsize12_Susan Lawson22_lawson_susan@YAHOO.COM31_Tue, 29 May 2001 07:29:08 -0700563_us-ascii Louise,



For choosing a segsize there are some easy guidelines..

If the number of page is 28 or less, use SEGSIZE 4 to 28, if the number of pages is between 28 and 128 use 32, if the number of pages is 128 or more use a segsize of 64.

Regards, Susan Lawson YL&A --- "Reid, Louise" wrote: > Thanks for the responses - I did discover that we > have the tables structured > poorly. We are not taking advantage of the way most > queries are run against > the tables. At design time we thought one [...] 1956 62 35_Re: DSN1COPY and OBIDXLAT parameter11_Bikash Paul19_bikash_db@YAHOO.COM31_Tue, 29 May 2001 07:38:04 -0700310_us-ascii Hi, RESET option will resets the LOGRBA in datapages. If you have not done a cold start of DB2 before the table was dropped, the it is not required as recovery log still remain same.

Can u tell us please which version of DB2, you are using. I am not sure whether you are using right OBID. [...] 2019 34 24_Left Outer Join Question13_Dana Maxfield23_DMaxfie@DOA.STATE.LA.US31_Tue, 29 May 2001 09:42:19 -0500380_iso-8859-1 In DB2 6.1 the following statement will return matched rows for a full outer join and a right outer join. The left outer join will return only nulls for the right column values. The left outer join will not find any matches. I am certain the left outer join is working correctly, I would only like to understand the why left outer join is not finding any matches. [...] 2054 17 35_Re: DSN1COPY and OBIDXLAT parameter13_Judi Westcott23_judi_westcott@YAHOO.COM31_Tue, 29 May 2001 09:18:40 -0500513_- Using DB2 V5.

Best regards, Judi Westcott

p.s. As mentioned in a previous note, the description of RESET in the manual indicates you only need to use it if a different restoring to a different subsystem with a different recovery log. This is not the case.

================================================ 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. 2072 14 25_Re: Update statement help12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 29 May 2001 09:53:25 -0500495_- Update with a subselect was added in V6 with an APAR. This subselect is from the same table that is being updated. We call that a self-referencing subselect, and that is supported in V7. So what version are you using?

Roger Miller, DB2 for z/OS

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2087 40 22_Set Integrity Problems13_Swamy, Ritesh30_Ritesh.Swamy@BLR.HPSGLOBAL.COM31_Tue, 29 May 2001 20:24:10 +0500489_- Hi List Members,

I have done a data load and am then trying to set the integrity for the tables but I am getting the follwoing message

[IBM][CLI Driver][DB2/NT] SQL0290N Table space access is not allowed. SQLSTATE=55039

I have tried to quiesce the tablespace but then I get the following error message

The state of the application or of one or more table spaces for the table specified prohibits the loadapi action or quiescemode "1". Reason code = "1". [...] 2128 22 47_Need email address for ARKANSAS DB2 USERS GROUP12_Jorge Rivera21_bpisols@BELLSOUTH.NET31_Tue, 29 May 2001 11:41:26 -0400513_iso-8859-1 Can someone send me (NOT the LIST) an email address for the ARKANSAS DB2 USERS GROUP or any member. Have some information I need to send them.

Thanks.



Jorge Rivera Yevich, Lawson & Associates, Inc. Jorge_Rivera@YLAssoc.com www.ylassoc.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. 2151 135 25_Re: Update statement help14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Tue, 29 May 2001 08:39:43 -0700468_iso-8859-1 So, the only way to use a subselect in an update statement is with a self-referencing subselect?

Why is it limited this way?

Thanks,

Daryl (DJ) Johnson DBA Work Phone: 503.813.5451 Cell Phone: 503.706.6391 Pager: 503.202.4775 Home Phone: 503.632.4719



-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: Tuesday, May 29, 2001 7:53 AM To: DB2-L@RYCI.COM Subject: Re: Update statement help [...] 2287 39 51_Re: Need email address for ARKANSAS DB2 USERS GROUP13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Tue, 29 May 2001 11:55:00 -0400511_iso-8859-1 >Can someone send me (NOT the LIST) an email address for the ARKANSAS DB2 USERS GROUP or any member.

Hello Jorge,

The contact information is included below. For future reference, you can usually find information like this at www.idug.org

Click on "Regional User groups" on the left.

ARKANSAS DB2 USERS GROUP ARDUG Paul Hartjen Computer Associates, Inc. 850 Warrenville Road 3rd Floor Lisle, IL 60532 Phone: 800/442-6861 x8047 Fax: 708/241-8200 Email: hartjen@cai.com [...] 2327 66 51_Re: Need email address for ARKANSAS DB2 USERS GROUP12_Jorge Rivera21_bpisols@BELLSOUTH.NET31_Tue, 29 May 2001 12:31:29 -0400698_iso-8859-1 Jorge Rivera Yevich, Lawson & Associates, Inc. Latin America Operations (954) 899-6765 Mobile (954) 227-8923 FL Office (954) 255-3890 Fax Jorge_Rivera@YLAssoc.com www.ylassoc.com



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Seibert, Dave > Sent: Tuesday, May 29, 2001 11:55 AM > To: DB2-L@RYCI.COM > Subject: Re: Need email address for ARKANSAS DB2 USERS GROUP > > > >Can someone send me (NOT the LIST) an email address for the ARKANSAS DB2 > USERS GROUP or any member. > > Hello Jorge, > > The contact information is included below. For future > [...] 2394 69 51_Re: Need email address for ARKANSAS DB2 USERS GROUP12_Jorge Rivera21_bpisols@BELLSOUTH.NET31_Tue, 29 May 2001 12:36:09 -0400300_iso-8859-1 The IDUG official web page was my first resource. I discovered that, at least in the case of Arkansas, the email for Paul Hartjen and the IBM Liason were invalid. The phone number for Paul was already disconnected.

Once I get a valid response I will make it available to IDUG. [...] 2464 43 38_Re: DB2 Addressing beyond 2Gigs memory12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 29 May 2001 11:17:14 -0500549_- My memory has a few bits that differ, but has quite a bit the same.

The other memory that is used to balance performance includes the memory in Coupling Facilities for data sharing and the memory in disk cache units.

The zSeries has announced both real and virtual storage at 64 bits, although the real memory is currently at 64 GB or 36 bits. DB2 V6 does a good job of using data spaces and larger real memory on the new machine with OS/390 V2R10 or z/OS. The original guidance for data spaces was tricky, but I looked back at [...] 2508 56 23_Re: DSNREXX performance12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 29 May 2001 19:52:56 +0200534_Windows-1252 Hi, I can say that as per V6 - no improvement. I'll continue to use the "home grown" tool for the time being (at least for select).

For me it's as simple as ..... address sql "select A,B,C into :stemA. , stemB. , :stemC. from ....."

No loops ;-)

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- From: "Rolf Merkle" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, May 29, 2001 9:30 AM Subject: Re: DSNREXX performance [...] 2565 56 25_Re: Update statement help8_K.Balaji19_K.Balaji@TARGET.COM31_Tue, 29 May 2001 12:03:29 -0500442_iso-8859-1 Sajeev, The SQL should have read as

UPDATE SYSIBM.SYSPROCEDURES SET PARMLIST = (SELECT PARMLIST FROM SYSIBM.SYSPROCEDURES WHERE PROCEDURE = 'procedure01') WHERE PROCEDURE = 'procedure02';

While testing one procedures is using the same parmlist as the other. So I wanted to copy the parmlist simply by issuing a sql.

Moreover, I expect this kind of sql to using in many of my application programs. Regards [...] 2622 41 28_db2 v6 perfromance (help!!!)13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM31_Tue, 29 May 2001 12:16:03 -0500422_us-ascii hi... We have a series of tables ( 17 total) with R.I. partitioned indexed. 4 are parents the remaining 9 are children the other 4 are stand-alone 1 row tables. each input record processes on average affects about 16 of the tables inserting 1 row in each table, which may or may not cause an update to another row on the table All sql are using the indexes with only 1 or 2 of the tables accessing datapages [...] 2664 27 57_Steve Westfall/KnowledgeEng/Equifax is out of the office.0_26_Steve.Westfall@EQUIFAX.COM31_Tue, 29 May 2001 13:29:40 -0500597_us-ascii I will be out of the office starting 05/29/2001 and will not return until 06/01/2001.

I will be out of the office May 29 - 31. I will respond to your message when I return.

Steve Westfall





This message contains information from Equifax, Inc. which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail postmaster@equifax.com. [...] 2692 82 25_Re: Update statement help13_Terry Purcell23_purcell_terry@YAHOO.COM31_Tue, 29 May 2001 11:58:33 -0700390_us-ascii Daryl,

What Roger is saying is that the example sent to the list was self-referencing. In V6 you can code anything other than a self-referencing subquery in the set clause (and of course WHERE clause).

Why the restriction on self-referencing? This was not introduced until V7 because there was additional coding required to ensure that the updates did not loop. [...] 2775 66 28_Re: Left Outer Join Question13_Terry Purcell23_purcell_terry@YAHOO.COM31_Tue, 29 May 2001 12:03:19 -0700371_us-ascii Dana,

If the join is working correctly, then the only time a join will not find any matches, is when there are no matches to be found.

You either have a bug or are misreading your data.

If you have an example of the data, then please send this to the list so that we can determine whether it is working as designed, or is indeed a bug. [...] 2842 35 19_Oracle Mass Mailing9_Rob Crane22_racrane@CONCENTRIC.NET31_Tue, 29 May 2001 13:16:58 -0600724_us-ascii Lets hope it is higher than 17%...

------------- From: Oracle [mailto:Oracle] Sent: Tuesday, May 29, 2001 10:44 AM To: rah@ccbroadcast.com Subject: IBM bought Informix. Can they buy you?

Every Informix user faces a major decision... and a major opportunity.

With IBM's purchase of Informix, you will have to change database companies. Why not consider all your options? Switch to Oracle and get leading-edge database technology with unmatched scalability, availability and security. Oracle not only offers a proven migration program, but also the industry's largest pool of third-party applications support and incomparable overall business support. Perhaps that's why research shows that [...] 2878 66 28_Re: Left Outer Join Question17_Linda F. Claussen18_lindafc@NETINS.NET31_Tue, 29 May 2001 14:12:22 -0500342_iso-8859-1 Dana,

The way the query is coded DB2 will give all the rows from T1 (the table on the left) and retrieve the contents of T2 only when all 3 columns match.

To test your results code an inner join to verify if there are matches on all 3 columns, these are the rows that should contain content from both T1 and T2. [...] 2945 100 28_Re: Left Outer Join Question11_Bikash Paul19_bikash_db@YAHOO.COM31_Tue, 29 May 2001 12:32:18 -0700312_us-ascii Hi , I am just thinking whether table and index are in sync. it might so happen that tablespace has been recovered and index is not rebuilt. And join is using the index and not finding anything. But while you run query, it is not using index and data comes from tables. So still, you see the data. [...] 3046 35 35_Cost Category in DSN_STATEMNT_TABLE31_Douwe van Sluis, Infodemic B.V.26_d.b.van.sluis@INFODEMIC.NL31_Tue, 29 May 2001 21:30:18 +0200529_iso-8859-1 Dear list,

Is the optimizer not able to determine the cost of a query below?

Select colx, col3, count(*) as coly from ( Select col1,sum(col2) as colx from table1 where col1 = number group by col2 ) as temp group by colx,col3

The optimizer is able to come up with a COST_CATEGORY of 'A' for the inner query, but gives back COST_CATEGORY 'B', with 'TABLE CARDINALITY' for the total query. If would think that the optimizer should be able to calculate the Table Cardinality of the inner table?? [...] 3082 17 23_Re: Oracle Mass Mailing26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Tue, 29 May 2001 14:50:25 -0500482_- Isn't it sorta like the sex surveys where umpteen percent are this or that and do this or that, when really it's only the extroverts(or braggerts) that answer at all? Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu

================================================ 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. 3100 111 35_Re: DSN1COPY and OBIDXLAT parameter13_Olson, Carlos14_COlson@QRS.COM31_Tue, 29 May 2001 13:24:26 -0700302_iso-8859-1 I believe you should be using the OBID of 194 instead of 214. I'm looking at the top of page 347 in the version 6 Diagnosis Guide and Reference and the chart shows:

HPGROID CHAR(2) Internal identifier of the table if HPGTBLC = 1. (Not applicable if HPGTBLC is not equal to one.) [...] 3212 136 25_Re: Update statement help14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Tue, 29 May 2001 14:32:19 -0700611_iso-8859-1 That makes more sense, thanks for the clarification.

Thanks,

Daryl (DJ) Johnson DBA Work Phone: 503.813.5451 Cell Phone: 503.706.6391 Pager: 503.202.4775 Home Phone: 503.632.4719



-----Original Message----- From: Terry Purcell [mailto:purcell_terry@YAHOO.COM] Sent: Tuesday, May 29, 2001 11:59 AM To: DB2-L@RYCI.COM Subject: Re: Update statement help

Daryl,

What Roger is saying is that the example sent to the list was self-referencing. In V6 you can code anything other than a self-referencing subquery in the set clause (and of course WHERE clause). [...] 3349 64 47_FW: [DB2-L] Cost Category in DSN_STATEMNT_TABLE13_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Tue, 29 May 2001 15:50:12 -0600427_iso-8859-1 REASON = TABLE CARDINALITY means that stats are missing from one or more of the tables involved.

Since col1 is the main filtering predicate, its stats are most important.

For os390, if col1 is the first column of an index, try FREQVAL on the runstats.

If col1 is not in an index, try TABLE(ALL) in the runstats. It gives cardinality for all columns of the all the tables in the tablespace. [...] 3414 35 32_Re: data lost - last update time14_Bistricer, Avi20_BistricerA@CONED.COM31_Tue, 29 May 2001 18:56:20 -0400390_iso-8859-1 Did you get the answer? I also have this problem . thanks

Avi Bistricer, DBA ConEd, IR 212.460.6695

-----Original Message----- From: Cockerill, Steve [mailto:SLCockerill@NATIONALGYPSUM.COM] Sent: Monday, May 21, 2001 3:27 PM To: DB2-L@RYCI.COM Subject: data lost - last update time

Anyone know how I can find the last date and time a table was updated? [...] 3450 54 32_Re: data lost - last update time9_Rob Crane22_racrane@CONCENTRIC.NET31_Tue, 29 May 2001 17:49:19 -0600320_us-ascii 3rd vendor log tools can do this for you (Log Master, Log Analyzer, etc.).

You could write your own process to go after the log and look for updates tied to the DBID/OBID of the object in question. Also need to be aware of changes to the object that may have resulted in it getting a new OBID, etc. [...] 3505 92 49_Re: Fw: how to recover the BSDS(emergency!!!!!!!)5_larry17_elibs@NETEASE.COM31_Thu, 31 May 2001 07:41:51 +0800405_iso-8859-1 Hi,Bikash the problem is on the DB2 for development .we all don't want to waste time backup the systems.So I have no idea how to recovery the DB2 directory. Thanks Larry ----- Original Message ----- From: "Bikash Paul" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, May 29, 2001 10:27 PM Subject: Re: Fw: how to recover the BSDS(emergency!!!!!!!) [...] 3598 84 25_Re: Update statement help15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Tue, 29 May 2001 20:04:16 -0400355_iso-8859-1 Is there a ANSI standard UPDATE SQL with Join? I can update one column with correlated sub-query in the DB2 update SQL, but how do we update multiple columns in a update SQL . SQLserver has something like

Update tab1 set tab1.col1 = tab2.col1 ,tab1.col2 = tab2.col2 ,.................... From Tab1, Tab2 where tab1.col3 = tab2.col3 [...] 3683 31 31_Questions regarding Dynamic SQL11_Roland Chua18_rolandchua@SGX.COM31_Wed, 30 May 2001 09:18:30 +0800687_us-ascii Hi list,

Is In-DB2 time (DB2 Accounting trace class2 elapsed time) includes the dynamic bind time? If In-DB2 time does not include the dynamic bind time then where is it appear in the DB2 accounting report?



Regards Roland

Confidentiality Caution ======================= Priviledged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply email. Opinions, conclusions and other information [...] 3715 79 40_Re: AW: provide UDF cobol sample program19_=?big5?B?tPa90qr4?=21_A000896@MS.SKH.ORG.TW31_Wed, 30 May 2001 11:06:32 +0800465_big5 HI Can you tell me cobol language how to coding use udf,because Craig Mullins DB2 Developers Guide not cobol program sample ----- Original Message ----- From: Peter, Georg Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, May 29, 2001 5:02 PM Subject: AW: provide UDF cobol sample program



The following is a simplified version of the CREATE FUNCTION statement that could be used to register a UDF to DB2: [...] 3795 29 31_idms and db2 - Two phase commit15_Venkatesh Kumar18_akash226@YAHOO.COM31_Tue, 29 May 2001 19:55:29 -0700486_us-ascii Hi,

I am looking for material on two phase commit between IDMS and DB2. Is there any reference, white paper, red books or articles or personal documentation.

I looked at the archive, but the materias is more to do with conversion.

Appreciate your response.

Thanks Akash

__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ [...] 3825 55 42_Re: AW: USER DEFINE FUNCTION DOCUMENTATION19_=?big5?B?tPa90qr4?=21_A000896@MS.SKH.ORG.TW31_Wed, 30 May 2001 11:10:09 +0800396_big5 HI: I know application programming and sql guide (chap 4-3) have not provide total cobol program how to use udf , so can you tell me cobol program coding skill thanks S Y TANG ----- Original Message ----- From: Thomas Weber Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, May 29, 2001 7:47 PM Subject: AW: USER DEFINE FUNCTION DOCUMENTATION [...] 3881 130 25_Re: Update statement help13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Tue, 29 May 2001 23:21:03 -0500398_iso-8859-1 Sibi,

I'm not sure of the exact ANSI version of this SQL, but DB2 can accomplish this in V6 quite easily using subqueries. And although you cannot explicitly code a join in an UPDATE or DELETE statement, V7 optimizer can convert such a subquery into a join.

The V6 syntax to code the equivalent SQL uses a SET clause row expression to update multiple columns such as: [...] 4012 56 44_Re: Migrate from CA-Datacom to DB2 questions10_john clark20_pak02449@PIXIE.CO.ZA31_Wed, 30 May 2001 07:41:34 +0530611_us-ascii Hi Deb,

You should check out www.pov-it.com for conversion s/ware.

I also have a couple of DSF programs to convert DD structures and DB data to DDL and DB2 compatabile data, if you wish to mail me off list.

Best regards



John Clark



Connection 42 - South Africa

Deb Thornton wrote:

> I would like to hear from anyone who has been involved in migrating from > CA-Datacom to DB2 (or vice versa). We are currently using Datacom on > OS-390 and are considering converting to DB2. By the end of this year, we > are expecting to double the [...] 4069 19 23_Re: DSNREXX performance21_Christian Hildebrandt28_Christian.Hildebrandt@DB.COM31_Wed, 30 May 2001 01:57:22 -0500395_- Hello, I observed poor performance with DSNREXX (db2 v6), too. Opened a pmr and got the following response from IBM:

"Found open requirement MR1201003358: DB2-REXX module DSNREXX When using DB2-REXX, module DSNREXX is loaded and deleted for every "EXECSQL...". This leads to significant performance reduction."

The problem is known by IBM and i hope they are working on it. [...] 4089 24 26_Re: Set Integrity Problems11_Hamar, Bela22_Bela.Hamar@VARETIS.COM31_Wed, 30 May 2001 10:13:04 +0200327_us-ascii Hi Ritesh,

some questions so that I can see the your problem better:

1. what is the state of the tablespace (in the output of LIST TABLESPACES) ? 2. What does "LOAD QUERY TABLE " say? 3. Am I right when I guess, that you tried QUIESCE ... INTENT TO UPDATE? If yes, then why not RESET? [...] 4114 130 49_Re: Fw: how to recover the BSDS(emergency!!!!!!!)15_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 29 May 2001 10:15:37 +0200637_us-ascii Larry,

If you have no backups. Not even backups taken by other means than DB2 utilities then you can try to fix the problems with the directory manually if the damage is not too extensive..... But this may not be easy. WHat are the errors you are getting....

If this is not an option.....then you are shit-out-of-luck... There is a reason why people take backups... And even though it's only a development system. Development systems typically are where programmers do there work... So it may have lower availability requirements but I've always considered development systems to have some production status. [...] 4245 25 22_Extend failure probelm8_duam lee16_duam_lee@USA.NET29_Wed, 30 May 2001 03:02:02 MDT438_US-ASCII Hi Lister’s can any body tell me what to do when there is extension failure for a table space. What are the methods I have to follow. The programmers loaded the test table without my knowledge? The table was small. Expecting the comments very soon from master’s.

Duam

____________________________________________________________________ Get free email and a permanent address at http://www.netaddress.com/?N=1 [...] 4271 20 38_32k pages vs 4k pages in same database9_john king24_john_king@REDIFFMAIL.COM31_Wed, 30 May 2001 09:07:30 -0000470_- Hello Listers can any body tell me if I use pages of different size for various tables in same database. The data base is having 10 tables and there is a need for making one table with 32 page size rather than 4 k page sizes. Is it possible under one database ? Shall I have to do some changes to system side like bufferpool and hypersapce pool ? Will there be any performance problems if use pages of different sizes for different tablespaces for same database. [...] 4292 66 26_Re: Set Integrity Problems13_Swamy, Ritesh30_Ritesh.Swamy@BLR.HPSGLOBAL.COM31_Wed, 30 May 2001 14:24:36 +0500607_iso-8859-1 Hi Bela,

Thanks for responding.

Most of the tablespaces were in Backup Pending state for which I took the backup and then used set integrity but there two tablespaces were in 'Delete Pending Backup Pending' state and these are giving problems. I tried restoring one of these using the earlier backup that I had but then I have got another problem. After restoring the tablespace, the status for it has changed to 'Roll Forward Pending'. When I do a roll forward the status changes to 'Restore Pending' and on doing a restore again the status changes to 'Roll Forward Pending'. [...] 4359 60 26_Re: Extend failure probelm15_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 29 May 2001 11:22:41 +0200720_iso-8859-1 Hello,

Alter your PRIQTY/SECQTY and REORG or LOAD REPLACE without REUSE option,

Regards,

Toine Michielse





duam lee @RYCI.COM> on 05/30/2001 11:02:02 AM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: Extend failure probelm





Hi Lister's can any body tell me what to do when there is extension failure for a table space. What are the methods I have to follow. The programmers loaded the test table without my knowledge? The table was small. Expecting the comments very soon from master's. [...] 4420 58 42_Re: 32k pages vs 4k pages in same database15_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 29 May 2001 11:24:36 +0200335_us-ascii Hello,

Yes you can have different pagesizes in the same database. You will have to take a look at the bufferpool that you allocate your 32k pagesize tablespace to. If this is your first table with 32k pages.. you probably have to give it some buffers....

Also if your on v6, consider 6K and 16K pagesizes.. [...] 4479 45 27_Collating Sequence and DB/212_Jenny Bourke23_jenny.bourke@NL.IBM.COM31_Wed, 30 May 2001 11:18:01 +0200336_us-ascii Hi,

I hope someone can help me. I have an application running on AIX that performs a number of queries using embedded sql and sorts the results using an order by clause. I need to compare the results of one query with another and to do this I put the relevant host variables into strings (IString) to compare them. [...] 4525 30 44_Connect V7.1 and Novell Application Launcher13_Thomas Schulz20_thomas.schulz@SVI.DE31_Wed, 30 May 2001 05:57:50 -0500395_ISO-8859-1 Hey friends

I have a strange case with the German umlaute ä,ü,ö when I import a table from OS/390 DB2 by MS Access or any other software.

If I install DB2 Connect V7.1 on my PC (NT Servicepak 5) from the CD, everything went ok.

but if I get it delivered by Zenwork Novell Apllication Launcher, and I insatll it from there, then I get the problems with ö ü ä [...] 4556 24 21_QMF VER 2.0 RELEASE 40_24_ujjwal@LOT.TATASTEEL.COM31_Wed, 30 May 2001 17:19:43 +0530434_us-ascii Hi List , Is QMF Version 2 Release 4 compatible with DB2 V6 ?. We are at present in DB2 V5 R1 and QMF Ver 2 Rel 4. As far as I know QMF Version 2 Release 4 is out of Support and out of Service. The only release being supported is QMF 3.3 and QMF 6.1 which is what was just released.Do We need to get someone from QMF support to find out if we can migrate from 2.4 to 6.1 or 3.3. Any help will be appreciated. Thank You. [...] 4581 27 17_UDB for AIX HELP!11_Judy Kibler21_judith.kibler@TRW.COM31_Wed, 30 May 2001 07:20:40 -0500569_us-ascii Hello listers,

We run Baan IVc on UDB for AIX. Suddenly this morning in the wee hours (isn't that when ALL performance problems start, fellow DBAs?!), we are having performance problems with the Baan sessions that pertain to a specific table. I have run reorgchk on it and it doesn't appear to me to be in trouble. I have also looked to be sure it has plenty of space (it is in a DMS tablespace, per Baan's recommendations). I am new to the UDB environment, as we just migrated this application from DB2/390 over the Memorial Day weekend. All eyes [...] 4609 42 21_Re: UDB for AIX HELP!18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Wed, 30 May 2001 08:47:07 -0400655_iso-8859-1 You ran reorgchk but you didn't metion if you ran runstats/rebind, etc. How recent is the statistic information?

-----Original Message----- From: Judy Kibler [mailto:judith.kibler@TRW.COM] Sent: Wednesday, May 30, 2001 8:21 AM To: DB2-L@RYCI.COM Subject: UDB for AIX HELP!



Hello listers,

We run Baan IVc on UDB for AIX. Suddenly this morning in the wee hours (isn't that when ALL performance problems start, fellow DBAs?!), we are having performance problems with the Baan sessions that pertain to a specific table. I have run reorgchk on it and it doesn't appear to me to be in trouble. I have also looked to [...] 4652 23 56_Shan Leatherman/MO/americancentury is out of the office.0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Wed, 30 May 2001 07:47:40 -0500515_us-ascii I will be out of the office from 05/30/2001 until 06/04/2001.

I will be in a class Wednesday through Friday of this week and on vacation this Sunday(my normal work day) so I'll be returning next Monday.

I will check messages early each morning and at noon, so if your answer can wait I'll respond as soon as I can . If you need DBA support, send a message to *DB or if you need immediate assistance page the primary DBA on call at (816) 292-5449 or contact the help desk (816) 340-4250. [...] 4676 76 21_Re: UDB for AIX HELP!11_Judy Kibler21_judith.kibler@TRW.COM31_Wed, 30 May 2001 08:19:14 -0500582_us-ascii I ran the reorgchk with update statistics, so the statistics are current.







"Krawetzky, Peter J" on 05/30/2001 07:47:07 AM

Please respond to DB2 Data Base Discussion List





To: DB2-L@RYCI.COM

cc: (bcc: Judy Kibler/Nashville)





Subject: Re: UDB for AIX HELP!

















You ran reorgchk but you didn't metion if you ran runstats/rebind, etc. How recent is the statistic information? [...] 4753 16 21_Re: UDB for AIX HELP!0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Wed, 30 May 2001 09:30:54 -0400428_us-ascii Did you rebind? How is your bufferpool activity in relation to the table/indexes being used? Do you have enough concurrent threads allocated in DBM and DB manager?

R/..Ed

================================================ 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. 4770 96 0_15_Daniel Scheibli15_scheibd@GMX.NET31_Wed, 30 May 2001 15:35:26 +0200274_us-ascii Like Peter said, you can try to rebind your packages (db2rbind is the fastest way to do that).

I think the best will be if you try to do an snapshots to see what's going on in your system. I have attached an script for doing this in an unix enviroment. [...] 4867 121 21_Re: UDB for AIX HELP!18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Wed, 30 May 2001 09:50:13 -0400387_iso-8859-1 Did you run an explain on the SQL accessing the table? That would be my next step.

If the explain doesn't show anything, then you probably need to look at the buffer parameters, etc. Turn on your monitoring switches to track sort, bufferpool and lock swithces. You do this with the following command on the server: db2 update monitor switches using switch-name on [...] 4989 50 48_DB2 DBA's, Product Developers &, Product Support13_Ginger Duncan20_gingerd@TGA-JOBS.COM31_Wed, 30 May 2001 09:57:03 -0400709_Windows-1252 DB2 DBA DB2 Database Administrator responsible for support of DB2 for OS/290 database systems. Key responsibilities include: Support for DB2 for OS/390 database systems. Business analysis to develop database designs (logical and physical design). Serves in the design, development, tuning, and problem resolution of database services. Provide training and assistance in the usage of database systems. Provide data migrations, transformation, and backup/recovery to support development and production environments. Develop processes, procedures, and jobs to monitor and/or automate tasks required to maintain databases. Permanent opportunity in NC . Salary $75-80k. Company will not sponsor. [...] 5040 46 25_FW: QMF VER 2.0 RELEASE 413_Gleason, Mike22_MGleason@UILLINOIS.EDU31_Wed, 30 May 2001 09:06:50 -0500490_iso-8859-1 Hi Ujjal, Although we migrated from QMF 3.3 to version 6, the steps necessary to migrate from QMF 2.4 are documented in the INSTALLING AND MANAGING QMF ON OS/390 manual, GC26-9575. I installed QMF 6.1 while the DB2 subsystems were still under DB2 v5, but that requires repeating some of the installation steps when DB2 is upgraded. QMF 2.4 MIGHT work, but one of the JOYS of running on unsupported software is that you(WE!) get to find out for ourselves. Good luck with it. [...] 5087 38 22_Identity cache setting14_Craig McKellar26_craig.mckellar@BIGPOND.COM31_Thu, 31 May 2001 00:18:19 +1000598_us-ascii Has anyone played around with identity columns and cache settings in a high activity environment? I am interested the performance and any contention on sysibm.sysequence. We have a few applications using identity for partitioning and obtaining a key value but none have yet made into production. Our current observations show 1) the first insert forced a retrieval of a set of values based on the cache setting. 2) The MAXASSIGNED in syssequence is set to the limit of the cache not the last row inserted. 3) When the cache is used up, the next insert forces a synchronous request to [...] 5126 23 21_Re: UDB for AIX HELP!11_Judy Kibler21_judith.kibler@TRW.COM31_Wed, 30 May 2001 09:16:49 -0500533_us-ascii Thanks to everyone who replied....several great ideas for me to try! I will look into the rebind (this is a purchased ERP package, so I'm not sure how to go about this, or even if I can!) I will also run some additional snapshots to see what I can tell from that. Performance on this table already appears to have improved, perhaps since I updated the statistics with the reorgchk update statistics command. In any case, it has improved enough that all the people who were camped in my office earlier have left...whew! [...] 5150 57 42_JAVAENV dataset for Java Stored Procedures12_David Booher22_David.Booher@QUEST.COM31_Wed, 30 May 2001 07:20:37 -0700307_iso-8859-1 Does anyone have the correct syntax for the JAVAENV dataset? I keep getting an IEC014I 337-04 error indicating that it is reaching end of file and the WLM address space is dumping. I can't seem to find any documentation on how this dataset is supposed to be terminated.

Thanks, Dave Booher 5208 96 21_Re: UDB for AIX HELP!12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 30 May 2001 07:19:55 -0700347_us-ascii Well of course your managment sent you to proper UDB/AIX training, and provided you with monitoring tools before moving such a mission critical application... And you have two whole days of experience with this in production... Assuming you've followed the previous advice of runstats and rebinding, and that all the indexes are ok, [...] 5305 24 14_Lock avoidance10_Ulsas, Tom23_tulsas@DOIT.STATE.IN.US31_Wed, 30 May 2001 09:33:36 -0500582_iso-8859-1 Our programmers are seeking a way to provide read-only access to the mainframe DB2 database from a voice response server that provides public access to some of the data. They looked into 'dirty reads' earlier and found that they cannot just add the "WITH UR" statement to the end of their SQL statements because that is specific to DB2 and cannot be passed in an Open Database Call (ODBC) successfully. The public has been promised 7X24 access, but periods of heavy batch update to the database is preventing access for hours at at a time. Is there another option ? [...] 5330 41 18_Re: Lock avoidance13_Terry McCombs20_tmccomb@SEDGWICK.GOV31_Wed, 30 May 2001 09:45:48 -0500300_iso-8859-1 Could you bind the package they are using with the UR option and have that work? If they are using a standard package used by many routines (e.g., a DB2 Connect package), can you make a copy of it in a different packageset and have them use the other packageset for this application? [...] 5372 55 26_Re: Extend failure probelm11_Bikash Paul19_bikash_db@YAHOO.COM31_Wed, 30 May 2001 07:54:21 -0700361_us-ascii Hi, it could be one of the followings

1) Even though, the table is small, secondary extent of the tablespace may be big.

2) Table space is simple tablespace and reorg has not been done to reclaim all the storage.

3) May be not big enough extent is available in the volumes , mapped the storage group, the tablespace is using. [...] 5428 40 18_Re: Lock avoidance12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Wed, 30 May 2001 10:01:04 -0500661_iso-8859-1 Could you put the SQL in a stored procedure that was called by the server?

Tom Moulder (BMC Software, Inc.)

-----Original Message----- From: Ulsas, Tom [mailto:tulsas@DOIT.STATE.IN.US] Sent: Wednesday, May 30, 2001 9:34 AM To: DB2-L@RYCI.COM Subject: Lock avoidance



Our programmers are seeking a way to provide read-only access to the mainframe DB2 database from a voice response server that provides public access to some of the data. They looked into 'dirty reads' earlier and found that they cannot just add the "WITH UR" statement to the end of their SQL statements because that is specific to DB2 and cannot be [...] 5469 80 18_Re: Lock avoidance11_Bikash Paul19_bikash_db@YAHOO.COM31_Wed, 30 May 2001 08:01:14 -0700604_us-ascii Hi, I know that there is a parameter in ODBC client setting.. The parameter is called TXNISOLATION and this could be set to UR. That should help.

Or another thing could be using stored procedure and making call to stored procedure from the client.

Thanks and Regards, Bikash







--- Terry McCombs wrote: > Could you bind the package they are using with the UR option and > have that > work? If they are using a standard package used by many routines > (e.g., a > DB2 Connect package), can you make a copy of it in a different [...] 5550 28 25_Re: Update statement help12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 30 May 2001 09:44:50 -0500523_- Back in 1986, Dr. Codd included this rule in his ComputerWorld article.

"Rule 6: All views that are theoretically updatable are also updatable by the system.

"Note that a view is theoretically updatable if there exists a time-independent algorithm for unambiguously determining a single series of changes to the base relations which will have as their effect precisely the requested changes in the view. In this regard, "update" is intended to include insertion and deletion, as well as modification." [...] 5579 73 18_Re: Lock avoidance15_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 29 May 2001 17:19:13 +0200491_us-ascii Hello Tom,

You could use the SQLSetConnectOption (which is in both the ODBC as well as the C/OPEN CLI spec) to specify the options to use for the transaction. Specify SQL_TXN_ISOLATION (option) and SQL_TXN_READ_UNCOMMITTED (value).

If your code needs to run against a DBMS from another vendor (why would you??) then you can use the SQLGetInfo() call with finfoType=SQL_TXN_ISOLATION_OPTIONS) to see if the value for SQL_TXN_ISOLATION is supported by that vendor. [...] 5653 25 34_Timed out problem for Export (UDB)15_Rajesh Udandrao17_urajes1@YAHOO.COM31_Wed, 30 May 2001 08:22:03 -0700622_us-ascii We rebound the Export utility using Isolation level UR, It is failing with Sql Code -911 and Reason code 68.. Pls let me know the probable reasons for this ..

Thanks in advance

Rajesh

__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5679 67 18_Re: Lock avoidance13_Larry Hubbard29_Larry_Hubbard@CMS.STATE.IL.US31_Wed, 30 May 2001 10:16:42 -0500457_us-ascii Tom,

Would you be able to create a Stored Procedure on OS/390 and have your software do the RPC to that?

Larry

Larry Hubbard CMS-BCCS-ISD Tel. 217-785-8848 Fax. 217-782-8126





"Ulsas, Tom" cc: Sent by: DB2 Subject: Lock avoidance Data Base Discussion List



05/30/01 09:33 AM Please respond to DB2 Data Base Discussion List [...] 5747 21 32_Re: db2 v6 perfromance (help!!!)12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 30 May 2001 10:12:55 -0500569_- When you have the DB2 accounting records before and after, with the explain table before and after, the comparison is fairly easy. The less you have, the harder the task becomes. Do you have the accounting records?

After migration from V6, the most common regression situation is a change in an access path, some extra tracing that was turned on, or extra binding. Check that plan and rebind it, saving the explain data. Then get the long accounting report for the run to see where the time is being spent. I don't know Omegamon well, but you may be able [...] 5769 49 26_Initiate RLF DB2V6.1 OS39015_Thomas Karlsson19_thkar97@HOTMAIL.COM31_Wed, 30 May 2001 17:37:11 +0200426_- Hi! ----------- I need some help with a newbie question ?

I want to start using RLF in OS390. I have created DSNRLST01. I have put in the following in the table AUTHID PLANNAME N:ASUTIME LUNAME RLFFUNC RLFBIND USER1 DSNTIB61 N 0

I have then run -START RLIMIT ID=01

DSNT710I + SYSIBM.DSNRLST01 ALREADY ACTIVE. START RLIMIT COMMAND IGNORED. DSN9023I + DSNTCSTR 'START RLIMIT' ABNORMAL COMPLETION *** [...] 5819 66 18_Re: Lock avoidance13_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Wed, 30 May 2001 11:43:11 -0400294_us-ascii I know if you were on the OS/390 environment you could Bind CS with Currentdata NO and code your SQL statement with FOR READ ONLY which is ODBC- compliant. If you code FOR FETCH ONLY it is not ODBC compliant. I am not sure whether this works in the distributed environment (UDB). [...] 5886 17 39_Re: Cost Category in DSN_STATEMNT_TABLE12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 30 May 2001 10:34:59 -0500398_- The optimizer can compute the cost, but the category changed from A to B due to a bit less accuracy in the estimate. There is a REASON column in the table to help explain why something was put into category B. There is a section in the Administration Guide, "Understanding the implications of cost categories" that may be helpful. In pdf dsnag0g2.pdf for V6, the Acrobat page number is 979. [...] 5904 14 32_Re: data lost - last update time12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 30 May 2001 10:38:21 -0500464_- Just to add one more possibility, SYSLGRNX has ranges of time for updates, so you can check on times. There are a few more possibilities in the Audit chapter of the Administration Guide.

Roger Miller, DB2 for z/OS

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 5919 80 30_Re: Initiate RLF DB2V6.1 OS39015_Toine Michielse18_vndobtm@US.IBM.COM31_Tue, 29 May 2001 18:03:01 +0200313_us-ascii Hello Thomas,

Are you sure that the primary authid that executes the query is actually 'USER1'. try updating the RLST table and blank out the AUTHID column. To verify, what is the creator of the table you are querying. For the row you showed to take effect, the table must be USER1.TABLE100. [...] 6000 140 49_Re: Fw: how to recover the BSDS(emergency!!!!!!!)11_Bikash Paul19_bikash_db@YAHOO.COM31_Wed, 30 May 2001 09:02:19 -0700347_us-ascii Hi Larry, I understand now what is the problem for you. But true reality is , you cant recover if you dont have any backup..

Even for test and deveopment system, we keep backup and not everyday. We backup once in a week. And also we keep the archive log until and unless, backup is taken. So we can do point of time recovery. [...] 6141 18 35_Re: Questions regarding Dynamic SQL12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 30 May 2001 10:44:55 -0500466_- Dynamic binds would be included in class 2. There is an explanation with a diagram under How Response Time is Reported. In my V5 hard copy, this is page 5-47. What is not indicated there is that the PREPARE for dynamic BIND is like other statements. Asynchronous work and parallelism makes the picture more complex, but shortens the response times. For BIND, the log and data writing are mostly asynchronous, but almost all of the rest should be synchronous. [...] 6160 22 30_DSNZPARM Recognition Character18_Gaston, Raymond J.17_GastonRay@ORU.COM31_Wed, 30 May 2001 12:14:14 -0400359_ISO-8859-1 DB2-List,

We are on OS390/ DB2 V5. We understand that the default sub-system recognition character is a "-". (for example... -START DB2 PARM(DSNPARM ACCESS(*)).

Where in DSNZPARM is this character set or changed (if need be)? The systems programs need to document this.

Thank you in advance - Ray Gaston (DBA - ConEdison) [...] 6183 55 20_Java Virtual Machine15_Damien Ponthier27_d.ponthier@CASTSOFTWARE.COM31_Wed, 30 May 2001 18:22:32 +0200165_iso-8859-1 Hello,

Does a DB2-UDB (7 and above) always install a JVM when installing the server and is it a standard JVM or modified one by IBM ?

Thanks 6239 43 34_Re: DSNZPARM Recognition Character16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 30 May 2001 12:40:21 -0400494_iso-8859-1 Not in ZPARM. Is in SYS1.PARMLIB(IEFSSN00)

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Gaston, Raymond J. [mailto:GastonRay@ORU.COM] Sent: Wednesday, May 30, 2001 12:14 PM To: DB2-L@RYCI.COM Subject: DSNZPARM Recognition Character



DB2-List,

We are on OS390/ DB2 V5. We understand that the default sub-system recognition character is a "-". (for example... -START DB2 PARM(DSNPARM ACCESS(*)). [...] 6283 59 34_Re: DSNZPARM Recognition Character11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM31_Wed, 30 May 2001 11:47:58 -0500406_us-ascii There is no ZPARM value see page 198 of the DB2 Install Guide. The value is stored in "SYS1.PARMLIB(IEFSSN00)"... : )







"Gaston, Raymond J." To: DB2-L@RYCI.COM Subject: DSNZPARM Recognition Character Sent by: DB2 Data Base Discussion List



05/30/01 11:14 AM Please respond to DB2 Data Base Discussion List [...] 6343 61 0_11_Forbis Mike22_James.M.Forbis@IRS.GOV31_Wed, 30 May 2001 12:39:19 -0400568_iso-8859-1 Our environment consists of DB2 Version 6 running on an IBM OS/390 platform. My question concerns a problem with running the IBM Online Reorg Utility using the shrlevel change mode with a lot of read only CICS transactions. Depending on the volumn of CICS transactions, when the job goes to the switch phase to drain all the readers my job either abends due to unable to get the resource or takes so long to drain the readers that the CICS transactions start backing up and causing problems. I am going to try setting the longlog to term, the delay to [...] 6405 87 24_Re: Java Virtual Machine14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Thu, 31 May 2001 02:51:48 +1000421_iso-8859-1 Damien, IBM supplies a JVM with DB2 UDB on NT/Unix. It is basically from Sun. I have had problems when compiling non-DB2 Java applets and programs but for standard DB2 JDBC programs it should work fine. I believe DB2 supports the 1.1 and 1.2 SDK. DBM cFG needs to be updated to reflect what you are using, along with the classpath environmental variable. Leon can probably offer additional info. HTH Phil [...] 6493 63 20_Online Reorg Problem11_Forbis Mike22_James.M.Forbis@IRS.GOV31_Wed, 30 May 2001 12:44:29 -0400568_iso-8859-1 Our environment consists of DB2 Version 6 running on an IBM OS/390 platform. My question concerns a problem with running the IBM Online Reorg Utility using the shrlevel change mode with a lot of read only CICS transactions. Depending on the volumn of CICS transactions, when the job goes to the switch phase to drain all the readers my job either abends due to unable to get the resource or takes so long to drain the readers that the CICS transactions start backing up and causing problems. I am going to try setting the longlog to term, the delay to [...] 6557 62 34_Re: DSNZPARM Recognition Character18_Gaston, Raymond J.17_GastonRay@ORU.COM31_Wed, 30 May 2001 12:55:43 -0400629_ISO-8859-1 Eric / All,

Thanks loads for your rapid and timely response. Much appreciated and helpful!

- Ray

-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Wednesday, May 30, 2001 12:40 PM To: DB2-L@RYCI.COM Subject: Re: DSNZPARM Recognition Character



Not in ZPARM. Is in SYS1.PARMLIB(IEFSSN00)

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Gaston, Raymond J. [mailto:GastonRay@ORU.COM] Sent: Wednesday, May 30, 2001 12:14 PM To: DB2-L@RYCI.COM Subject: DSNZPARM Recognition Character [...] 6620 104 24_Re: Online Reorg Problem16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 30 May 2001 13:04:16 -0400357_iso-8859-1 We have/had this problem. Best solution is to force all the CICS transactions (even read-only ones) to do EXEC CICS SYNCPOINT which makes a COMMIT happen. They should do this more freqently than the number for IRLMRWT in DSNZPARM. Even better is (time between COMMITs) < (IRLMRWT-(MAXRO+(3secs*# of datasets in the tablespace and indices)). [...] 6725 66 34_Re: DSNZPARM Recognition Character13_Larry Findley17_llfindley@USA.NET29_Wed, 30 May 2001 12:17:11 CDT336_US-ASCII Gaston,

This parameter is set in IEFSSNxx in SYS1.PARMLIB. The xx is specified in IEASYSxx, or IEASYS00 (the SSN=xx parm). The xx for IEASYSxx is specified on SYSPARM in LOADxx, which is selected by the hardware. OR -- to make that simple, just tell your OS/30 system programers that you need the change in IEFSSN. [...] 6792 18 34_Re: DSNZPARM Recognition Character26_Edward(Ed) J. Finnell, III23_efinnell@SEEBECK.UA.EDU31_Wed, 30 May 2001 12:26:45 -0500505_- With more modern version of OS/390 can use SETSSI to add(or change if they were added dynamically). To display ===>d ssi

===>setssi add,S=subname,C=Consname,I=Init_routine,P=initparm Edward(Ed) J. Finnell, III Enterprise Systems/Proj. Mgr. url:www.ua.edu

================================================ 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. 6811 160 21_Re: UDB for AIX HELP!14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Thu, 31 May 2001 03:25:26 +1000539_iso-8859-1 Judy, In the future I would suggest that you do this in a test database first, then do some stress testing or benchmarking before going live in production. Yes, I know I am preaching to the choir but when corners are cut you know what happens. And if you point it out and they (meaning mgt) doesn't heed your advice, then it is on them, although you will still get stuck fixing it. You didn't indicate what kind of SQL was being run against this table. Was it static or dynamic? Since it is a 3rd party vendor package, you [...] 6972 26 20_DB2 DBA 0S/390 HELP?12_Confidential26_confidential@ECFACTORY.NET31_Wed, 30 May 2001 13:35:21 -0400520_iso-8859-1 I am currently looking for a person with IT experience i.e. DB2, DBA and OS/390 expertise for a position in Columbus Ohio. The opportunity offers a strong salary of 80-90k plus a nice sign on bonus plus full relo and a host of outstanding benefits. If you know of anyone who may be a good fit or if you want to know a little more detail yourself please reply or call and I would be happy to help in any way I can. I can reward anyone with a referral bonus of $2000.00 if we secure someone that you refer [...] 6999 28 26_DB2 DBA OS/390 New opening12_Confidential26_confidential@ECFACTORY.NET31_Wed, 30 May 2001 13:39:41 -0400529_iso-8859-1 I am currently looking for a person with IT experience i.e. DB2, DBA and OS/390 expertise for a position in Columbus Ohio. The opportunity offers a strong salary of 80-90k plus a nice sign on bonus plus full relo and a host of outstanding benefits. If you know of anyone who may be a good fit or if you want to know a little more detail yourself please reply or call and I would be happy to help in any way I can. We are not able to sponsor anyone with H1-b visa status. This position is permanent/fulltime I can [...] 7028 42 38_Re: Timed out problem for Export (UDB)11_Jeremy Dodd21_jeremy.dodd@WHICH.NET31_Wed, 30 May 2001 19:01:35 +0100356_us-ascii Rajesh,

My understanding that on Unix, you can not change isolation for an export - it takes a read lock on the table. So if your table isn't read only you will, in all probability, get a lock timeout.

I believe you can (in theory) rebind appropriate packages with UR, but that would mean you are stuck with UR for all exports. [...] 7071 18 26_Lock Avoidance - Thank you10_Ulsas, Tom23_tulsas@DOIT.STATE.IN.US31_Wed, 30 May 2001 13:09:49 -0500463_iso-8859-1 Thanks to everyone who responded to my dilemma. You provided the ammunition to get us moving forward. The LISTSERV is a valued resource for all of us!

Thanks again,

Tom Ulsas State of Indiana, DBA

================================================ 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. 7090 71 24_Re: Online Reorg Problem11_Bill Happel22_WRHappel@US.FORTIS.COM31_Wed, 30 May 2001 13:24:33 -0500339_US-ASCII I agree with Eric. The SYNCPOINT will definately help, especially for long running CICS transactions.

Another thought is to lok into DB2 UDB for OS/390 Version 7. Version 7 is suppose to accelerate the Switch phase by pre-defining the shadow copy from I001 to J001 version. However, you still do have to do the drain. [...] 7162 87 24_Re: Online Reorg Problem16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 30 May 2001 14:45:52 -0400310_iso-8859-1 I have tested the V7 switch. It is a big improvement (and absolutely crucial for large(>64 partitions) tablespaces). But alas, Bill is right. You still must do the DRAIN. The drain is usually the big delay. Now try explaining to Joe Cobol (Mary PL/1?) why a read-only appl needs SYNCPOINT. ;=} [...] 7250 41 34_INDEXED ACCESS AGAINST EMPTY TABLE16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Wed, 30 May 2001 11:30:41 -0700428_us-ascii Esteemed list,

We have a table which usually contains no rows, has 1 column (DATE_EFF), and whose unique index consists of the only column in the table ordered in ascending sequence. It is a table which would ONLY be populated in the event of an emergency such that this table would actually contain one row. The table is heavily hit by online AND batch, and the SQL used to access the table is as follows: [...] 7292 62 26_Re: Identity cache setting12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 30 May 2001 22:00:08 +0200490_iso-8859-1 Hi, Just played with it. Cache refresh can take 2-3 ms. If numbers must be really consecutive - you'll pay in performance. If you can live with "holes" (from time to time) then it depends on your insert rate.

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- From: "Craig McKellar" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, May 30, 2001 4:18 PM Subject: Identity cache setting [...] 7355 39 49_Re: Fw: how to recover the BSDS(emergency!!!!!!!)12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 30 May 2001 22:07:17 +0200519_iso-8859-1 Hi, As a last resort - update the BSDS manually with much higher RBA then was before the problem (before the previous manual update) and do coldstart.

Isaac Yassin DBMS & IT Consultant yassini@bezeqint.net ----- Original Message ----- > > > > --- larry wrote: > > > Thanks for all kind help . > > > > > > Today I got straight the cause of why my db2 cannot start,it is not > > > the fault of the destroyed BSDS. > > > > > > one of my collegues wanted to rollback some data in [...] 7395 26 30_UDB v7.2 vs. UDB 7.1 Fixpack 30_22_BILL_GALLAGHER@PHL.COM31_Wed, 30 May 2001 15:20:56 -0400456_us-ascii Hi,

We're looking into implementing UDB 7.1, fixpack 3 in one of our test environments on AIX. Imagine our surprise when the "FixpakReadme.txt" file included in fixpack 3 contains the following statement:

"This FixPak will upgrade your system to Version 7.2 on all platforms."

If this is the case, then what is the v7.2 that is to be released on June 8? Is v7.2 really just a repackaging of v7.1 with fixpack 3 included? [...] 7422 91 38_Re: INDEXED ACCESS AGAINST EMPTY TABLE11_Bikash Paul19_bikash_db@YAHOO.COM31_Wed, 30 May 2001 12:29:52 -0700512_us-ascii Hi, I am little bit confused with idea of having an index on a table which has just a row and a column. I think, you should not have the index at all. For online application, i would like to read the table and keep the data in a TSQ/TDQ in CICS(If CICS is the transaction server) and let application read from TSQ/TDQ. Because i would not perfer to come to DB2 again and again to get the same data as it is read only. During CICS startup time, table could be read and placed in TSQ/TDQ OR in need. [...] 7514 75 38_Re: INDEXED ACCESS AGAINST EMPTY TABLE22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Wed, 30 May 2001 16:32:20 -0300502_iso-8859-1 Jeff,

I am not sure that I understood your question, but If your table is planned to contain no more than one line, there is no need for an index. An index would be useful for uniqueness or for optmizing access in a populated table.

Claudio



----- Original Message ----- From: "Jeff A L'Italien" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, May 30, 2001 3:30 PM Subject: INDEXED ACCESS AGAINST EMPTY TABLE [...] 7590 60 34_Re: UDB v7.2 vs. UDB 7.1 Fixpack 311_Jeff Gibson29_jeff.w.gibson@MAIL.SPRINT.COM31_Wed, 30 May 2001 14:39:36 -0500501_US-ASCII Bill,

We just came back from a customer briefing at the IBM DB2 Labs in Toronto. UDB 7.1 with Fixpack 3 is the same as UDB 7.2.

It confused us as well, but they said that they are the same!

Jeff Gibson Database Architect ISSD Data Management Sprint Corporation jeff.w.gibson@mail.sprint.com

-----Original Message----- From: BILL.GALLAGHER [mailto:BILL_GALLAGHER@PHL.COM] Sent: Wednesday, May 30, 2001 2:21 PM To: DB2-L Subject: UDB v7.2 vs. UDB 7.1 Fixpack 3 [...] 7651 98 63_DB2 V6 Stored Procedures, Schema names, SQL and Java Procedures0_19_Tim.Lowe@STPAUL.COM31_Wed, 30 May 2001 14:51:11 -0500573_us-ascii FYI, Unfortunately, I only received 2 responses to this question, but I did promise that I would summarize the responses. And, it has been over a week, and there do not appear to be any new replies. So, here goes...

One person said that they did not use the SYSPROC schema, they created their stored procedures in 3-character applications schemas. They migrate their stored procs through Endevor using customized REXX routines. And, they are not using Stored Procedure Builder, SQL procedures, or Java procedures yet. But, they are investigating them. [...] 7750 21 25_Re: QMF VER 2.0 RELEASE 40_17_JTonchick@AOL.COM29_Wed, 30 May 2001 15:58:18 EDT380_US-ASCII Migration from QMF R2.4 to R3.3 is supported. It's a simple process. You will have to check with QMF support on the jump from R2.4 to R6.

You can upgrade your DB2 version independently from QMF. Remember QMF is an application that uses DB2. QMF R2.4 will work under DB2 V6. It just will not be able to exploit features of DB2 that it wasn't coded to support. [...] 7772 63 38_Re: INDEXED ACCESS AGAINST EMPTY TABLE14_Zobjeck, A. J.18_Al_Zobjeck@TTX.COM31_Wed, 30 May 2001 15:10:00 -0500672_iso-8859-1 Yes, I've seen this. I fake the stats on the table/tablespace/indexes (say 2000 rows). This seems to help.

-----Original Message----- From: Jeff A L'Italien [mailto:Jeff.A.L'Italien@AEXP.COM] Sent: Wednesday, May 30, 2001 1:31 PM To: DB2-L@RYCI.COM Subject: INDEXED ACCESS AGAINST EMPTY TABLE



Esteemed list,

We have a table which usually contains no rows, has 1 column (DATE_EFF), and whose unique index consists of the only column in the table ordered in ascending sequence. It is a table which would ONLY be populated in the event of an emergency such that this table would actually contain one row. The table is heavily hit [...] 7836 126 38_Re: INDEXED ACCESS AGAINST EMPTY TABLE16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Wed, 30 May 2001 13:12:38 -0700579_us-ascii Claudio and Bikash,

I'm not the original designer of the structure, but the index was created due to certain standards which have been in place for several years, the standard being that every table needs a unique identifier. I've gone ahead and performed another test of this against a segmented tablespace (SEGSIZE 4) with no index and it DID reduce the getpage count to 1, BUT it did not reduce the CPU correspondingly - as low as .00063 used. I suspect that this could be due to the fact that DB2 is using sequential prefetch as indicated in the explain [...] 7963 73 29_Question about character sets22_Kennedy, Tom - Cendant23_Tom.Kennedy@CENDANT.COM31_Wed, 30 May 2001 17:08:02 -0400457_iso-8859-1 I have a question concerning storing different types of characters in DB2. Suppose you need to store someone's last name that contains the character N with the tilde above it. What is needed on the DB2 side? The DB2 subsystem is at v6.1. The CCSID is currently set to 37. I would imagine the code point for the N with the tilde is a different value than the code point for the N without the tilde (In EBCDIC that would be code point X'D5'). [...] 8037 147 25_Re: Update statement help15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Wed, 30 May 2001 18:10:32 -0400447_iso-8859-1 Thanks Terry. I did not know about this syntax. I would prefer a ANSI syntax which is supported by all the DBMS, so that I do not need to remember different syntax for different DBMS. Now a days DBA's handle multiple DBMS.

Thanks..sibi



-----Original Message----- From: Terry Purcell [mailto:Terry_Purcell@YLASSOC.COM] Sent: Tuesday, May 29, 2001 11:21 PM To: DB2-L@RYCI.COM Subject: Re: Update statement help [...] 8185 111 91_Repost: Technical OS/390 Software Education Coming to USA Cities. ..includes DB2 V7 session13_Morrill, John12_JohnM@VP.NET31_Wed, 30 May 2001 16:27:58 -0600348_- Dear DB2 Interested Parties,

Those of you in the USA and using DB2 for OS/390 may be interested in this half-day "mainframe" technical session coming to Denver, Kansas City, Dallas, Seattle, San Francisco and Long Beach next month (see below).

Regards, Mike + + + + + + +

What's New and Exciting in the Mainframe World? [...] 8297 20 33_RICDUG User Group Meeting June 1213_Willie Favero21_wfavero@ATTGLOBAL.NET31_Wed, 30 May 2001 18:29:43 -0500174_us-ascii The Richmond DB2 Users Group (RICDUG) next scheduled meeting is June 12, 2001. Please visit their web site for all the details.

http://www.ricdug.org/

8318 199 48_Deleting where current of cursor after a Commit.13_Troy R. Hodge27_Troy@VINEYARDENTERPRISE.COM31_Wed, 30 May 2001 21:40:08 -0500389_iso-8859-1



Hi All, I ran into an issue today that has me curious. I was testing some code left behind by a former DBA when I came across a -508. (Cannot update or delete because referenced cursor is not currently positioned on a data row)

Declare CursorA WITH HOLD

Situation.

Procedure Division. Open CursorA Perform Paragraph A Until CursorA-eof [...] 8518 32 52_Check This Out if you are interested in Db2 on Linux14_Philip Gunning23_pgunning@GUNNINGTSI.COM31_Wed, 30 May 2001 21:47:15 -0500536_us-ascii Today's Top Linux News:

IBM INVITES LINUX FANS TO COME PLAY ON ITS MAINFRAME

(Source: IDG.net) As a part of IBM's ongoing embrace of the Linux operating system and the open-source development community, IBM is offering Linux developers free access over the Internet to one of its powerful mainframes. http://www.itworld.com/jump/l0529_nl/www.idg.net/go.cgi?id=481497



Philip K. Gunning Sr. Systems Consultant Quest Software Certified Advanced Technical Expert DB2 UDB V7 Assoc List Owner DB2-l [...] 8551 172 39_Re: UDB v7.2 vs. UDB 7.1 Fixpack 3_____14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Thu, 31 May 2001 12:30:08 +1000338_iso-8859-1 Bill, I also heard the same at IDUG. Phil

-----Original Message----- From: Jeff Gibson To: DB2-L@RYCI.COM Sent: 5/30/01 12:39 PM Subject: Re: UDB v7.2 vs. UDB 7.1 Fixpack 3

Bill,

We just came back from a customer briefing at the IBM DB2 Labs in Toronto. UDB 7.1 with Fixpack 3 is the same as UDB 7.2. [...] 8724 88 29_Re: FW: QMF VER 2.0 RELEASE 40_24_ujjwal@LOT.TATASTEEL.COM31_Thu, 31 May 2001 09:04:26 +0530420_us-ascii Hi Mike & Tonchick , Thanx a lot for ur responses. Now I get an assurance that QMF 2.4 will work with DB2 V6. I was under the impression that it was not supported .Thanx once again .

With Regards Ujjal Bhattacharya IBM





"Gleason, Mike" To: DB2-L@RYCI.COM Subject: FW: QMF VER 2.0 RELEASE 4 Sent by: DB2 Data Base Discussion List [...] 8813 102 52_Re: Deleting where current of cursor after a Commit.12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Thu, 31 May 2001 13:56:17 +1000515_- The commit will release the locks that are held (from the "for update of" sub-clause) which is the cause of your angst. The "with hold" merely maintains position on the tables in the cursor; the rest goes

I'd defer the commit until after delete in para A as follows:-

Procedure Division. Open CursorA Perform Paragraph A Until CursorA-eof Paragraph A Fetch CursorA Perform Paragraph B Delete CursorA Where current of cursor If update count > 500 Perform Commit Paragraph B Delete Children of A [...] 8916 114 39_Re: UDB v7.2 vs. UDB 7.1 Fixpack 3_____15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Thu, 31 May 2001 13:59:13 +1000516_iso-8859-1 In the document found at: http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/newsletter.d2w/report#V72



(June 2001 DB2 Support Newsletter) it states:

"Because of their size and complexity, some features of Version 7.2 are not included in FixPak 3. These features will be available when the full standalone version of Version 7.2 is released. These include: Oracle, Microsoft SQL Server, and Sybase Wrappers i2, SAP and Web Connectors for DB2 Warehouse Manager " [...] 9031 92 52_Re: Deleting where current of cursor after a Commit.15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 31 May 2001 14:15:34 +1000482_- Troy,

To answer your question, yes. To answer it usefully, I had the same problem nearly a year ago with DB2 for OS/390 V5. We were deleting rows from a 2-table RI cascade structure, where the parent row could have a variable number of child rows, and deleteting each individual parent row was sometimes 'triggering' a delete of > 10,000 child rows. We broke it up, committing our delete of child rows every 1,000 (I think) and occasionally got the -508 you describe. [...] 9124 127 52_Re: Deleting where current of cursor after a Commit.13_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU31_Thu, 31 May 2001 14:23:57 +1000524_- Isn't this something that might be affected by the RELCURHL zparm parameter ?

Shaun

-----Original Message----- From: Cianci, Nick [mailto:Nick.Cianci@TEAM.TELSTRA.COM] Sent: Thursday, 31 May 2001 13:56 To: DB2-L@RYCI.COM Subject: Re: Deleting where current of cursor after a Commit.



The commit will release the locks that are held (from the "for update of" sub-clause) which is the cause of your angst. The "with hold" merely maintains position on the tables in the cursor; the rest goes [...] 9252 30 45_Looking for a full-time DB2 UDB Administrator11_John Riddle21_simplyme3@HOTMAIL.COM31_Thu, 31 May 2001 04:14:46 -0000429_- Hi. I'm looking for an experienced DB2 UDB Administrator for a very exciting growth-oriented company in Indianapolis. Candidate should have at leasat 1 year experience working with DB2 UDB (non OS390). This is a very fast growing, fun company with excellent benefits and advancement potential. No H1 Candidates can be considered at this point. Please write or call for details. Resumes should be sent to: j_riddle@llc.net [...] 9283 133 39_Re: UDB v7.2 vs. UDB 7.1 Fixpack 3_____10_Marc Baime14_mbaime@GTE.NET31_Thu, 31 May 2001 00:39:20 -0400375_iso-8859-1 I was at IDUG week before last in Orlando where numerous IBM developers stated that Version 7.1 fixpack 3 is equivalent to Version 7.2. There was no discussion of missing features just the statement that DB2 Version 7.1 fixpack 3 is, in fact, DB2 Version 7.2.

Regards...Marc Baime

On Wednesday, May 30, 2001, at 11:59 PM, Richard Simpson wrote: [...] 9417 34 50_Platinum PDA Object selection for Recovery problem13_Carlton Enuda24_carltonenuda@HOTMAIL.COM31_Thu, 31 May 2001 01:09:39 -0400 9452 155 52_Re: Deleting where current of cursor after a Commit.15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Thu, 31 May 2001 15:32:16 +1000671_iso-8859-1 Yeah, looks like you're right. If the lock is held, the subsequent 'delete... where current of' should work fine. If not, another fetch of the parent row is required to reposition the cursor.



Raymond

> -----Original Message----- > From: LOMBARD,Shaun [SMTP:Shaun.LOMBARD@DEWRSB.GOV.AU] > Sent: Thursday, 31 May 2001 2:24 pm > To: DB2-L@RYCI.COM > Subject: Re: Deleting where current of cursor after a Commit. > > Isn't this something that might be affected by the RELCURHL zparm > parameter > ? > > Shaun > > -----Original Message----- > From: Cianci, Nick [mailto:Nick.Cianci@TEAM.TELSTRA.COM] > Sent: Thursday, 31 May 2001 13:56 > [...] 9608 196 39_Re: UDB v7.2 vs. UDB 7.1 Fixpack 3_____15_Richard Simpson20_rsimpson@AU1.IBM.COM31_Thu, 31 May 2001 15:19:40 +1000368_iso-8859-1 Well I guess the way to find out is to have someone who has installed Fixpack 3 to tell us if any of the following features are there:

Oracle, Microsoft SQL Server, and Sybase Wrappers i2, SAP and Web Connectors for DB2 Warehouse Manager "

Anyone out there with Fixpack 3 installed who can confirm is these features are included or not ? [...] 9805 218 54_Re: Platinum PDA Object selection for Recovery problem16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 31 May 2001 07:35:02 +0100438_- Goodmorning carlton,

I am very sure you have built the EXTRACT procedure with A(utobuild) at the Database level. If you instead use T(ablespaces only) - you should be fine.

If you need further assistance - do not hesitate to contact me - also off the list.

Good luck Steen Rasmussen Computer Associates Senior Consultant tel: +45 45 47 43 63 fax: +45 45 47 41 10 mobile: +45 22 10 22 64 steen.rasmussen@ca.com [...] 10024 89 52_Re: Deleting where current of cursor after a Commit.12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Thu, 31 May 2001 16:52:14 +1000567_- Some times some changes in DB2 just pass you by. Thanks for pointing it out Shaun!

Troy note as per Shaun's comments; there may be a ZPARM that can help, but at the price of holding locks!.

| Option to Release Locks for Cursors Defined WITH HOLD | Effect: A subsystem parameter RELCURHL lets you indicate that you want DB2 | to release a data page or row lock after a COMMIT is issued for cursors defined | WITH HOLD. This lock is not necessary for maintaining cursor position. | Default: NO | Recommendation: The default, [...] 10114 35 34_Re: DSNZPARM Recognition Character13_Schulz Thomas20_thomas.schulz@SVI.DE31_Thu, 31 May 2001 09:24:09 +0200634_us-ascii Hey Ray

It is defines in sys1.parmlib member iefssn.. where you define your Subsystem

Thomas DBA SVI

"Gaston, Raymond J." schrieb: > > DB2-List, > > We are on OS390/ DB2 V5. We understand that the default sub-system > recognition character is a "-". (for example... -START DB2 PARM(DSNPARM > ACCESS(*)). > > Where in DSNZPARM is this character set or changed (if need be)? The systems > programs need to document this. > > Thank you in advance - Ray Gaston > (DBA - ConEdison) > > ================================================ > To change your subscription options or to cancel your subscription [...] 10150 21 49_Re: Fw: how to recover the BSDS(emergency!!!!!!!)10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 31 May 2001 10:20:14 +0200557_us-ascii Did you try to transplant another DB2 (if any) in the broken/destroyed DB2 susbsystem, i.e to build

catalog/directory from another DB2 with the same name ? Sometimes it works, in this way I rebuilt our DB2 in the Y2K

LPAR, but it had the same name of production DB2.

Regards Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10172 35 26_Re: Extend failure probelm10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 31 May 2001 10:36:18 +0200396_us-ascii Just a trivial addition: if you're using SMS take a look what's the quantity you're using when extending on multiple

volumes.

If your tablespace is extending and you change the allocation volume the next extent (in the new volume) will allocate as

many tracks as PRIMARY quantity, not according the SECONDARY quantity, if SMS was customize in the default mode. [...] 10208 21 40_Equivalant for v$session table in ORACLE13_Mathew, Sithu30_sithu.mathew@BLR.HPSGLOBAL.COM31_Thu, 31 May 2001 14:19:33 +0500536_- Hi List,

I am doing a migration project from ORACLE 8.1.6 to DB2 UDB 7.1. In ORACLE there is a system table called V$SESSION. It will give the a lot of system related information. Can anybody suggest the corresponding equivalent in DB2 UDB.



Thanks & Regards Sithu Mathew

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 10230 57 25_Re: Collecting Statistics14_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US31_Thu, 31 May 2001 06:50:43 -0400426_US-ASCII Michelle ... if you have not already figured this one out ... please to tell me what level of collection are you asking for in PDA? Regular, Fast or Scan. You can save much CPU and such by using SCAN (S) on your collection.

I use Scan and PDA to gen the reorg jcl if and only if my ACCESSPATH catalog stats are not up to date. If they are, I use a REXX to assess who needs reorg'ing and to build the jcl. [...] 10288 18 16_Archive Software12_Mark Anzmann24_manzmann@DHR.STATE.MD.US31_Thu, 31 May 2001 06:53:50 -0500544_- Listers-

We are in the process of investigating data archive/restore processes for our DB2 system and I am wondering what software solutions exist. We are looking to either create our own process or to use a packaged solution.

Any input on this topic is appreciated.

Mark Anzmann

================================================ 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. 10307 38 38_Re: INDEXED ACCESS AGAINST EMPTY TABLE22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR31_Thu, 31 May 2001 09:15:27 -0300366_iso-8859-1 Jeff,

> CPU correspondingly - as low as .00063 used. I suspect that this could be due > to the fact that DB2 is using sequential prefetch as indicated in the explain > output.

If the high CPU usage for that access is caused by the sequential prefetch, you might try to disable it. Use OPTIMIZE FOR 1 ROW to disable sequential prefetch. [...] 10346 51 34_Re: DSNZPARM Recognition Character18_Gaston, Raymond J.17_GastonRay@ORU.COM31_Thu, 31 May 2001 08:36:18 -0400686_ISO-8859-1 Thanks loads!

-----Original Message----- From: Schulz Thomas [mailto:thomas.schulz@SVI.DE] Sent: Thursday, May 31, 2001 3:24 AM To: DB2-L@RYCI.COM Subject: Re: DSNZPARM Recognition Character



Hey Ray

It is defines in sys1.parmlib member iefssn.. where you define your Subsystem

Thomas DBA SVI

"Gaston, Raymond J." schrieb: > > DB2-List, > > We are on OS390/ DB2 V5. We understand that the default sub-system > recognition character is a "-". (for example... -START DB2 PARM(DSNPARM > ACCESS(*)). > > Where in DSNZPARM is this character set or changed (if need be)? The systems > programs need to document this. > > Thank you in [...] 10398 186 11_Re: segsize13_Steve Higgins23_Steve_Higgins@BCBST.COM31_Thu, 31 May 2001 08:46:48 -0400334_iso-8859-1 Louise, The DB2 UDB for OS/390 Administration Guide, volume 2, page 927 contains a chart that indicates the recommended SEGSIZE settings base on the number of pages in the tablespace. If you are not on this version or don't have access to the manual, let me know and I will be happy to forward the information to you. [...] 10585 65 20_Re: Archive Software10_Alan Johns26_AlanJ@PRINCETONSOFTECH.COM31_Thu, 31 May 2001 08:47:02 -0400426_ISO-8859-1 Hi Mark. I'm a product specialist for Princeton Softech, and we supply specific tools for the archive arena - specifically Archive for DB2 and Archive for Servers. Both are intelligent archive solutions for Relational databases. Rather than take tour time up here, have a look at our web site at www.princetonsoftech.com , and if you find you have any technical questions, please don't hesitate to contact me. [...] 10651 44 49_Re: Fw: how to recover the BSDS(emergency!!!!!!!)5_larry17_elibs@NETEASE.COM30_Fri, 1 Jun 2001 20:15:59 +0800505_iso-8859-1 Hi,All: I have maked a logical dump from the production system to the development system since our devolepers must need the development system immediately.But I will give you the error message in the DB2*MSTR tomorow when I failed to restart the system. Thanks Larry

----- Original Message ----- From: "Max Scarpa" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, May 31, 2001 4:20 PM Subject: Re: Fw: how to recover the BSDS(emergency!!!!!!!) [...] 10696 148 49_Re: Fw: how to recover the BSDS(emergency!!!!!!!)5_larry17_elibs@NETEASE.COM30_Fri, 1 Jun 2001 21:16:56 +0800623_iso-8859-1 Hi,Toine: I 'll post you the error msg tomorrow. Thanks Larry ----- Original Message ----- From: "Toine Michielse" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, May 29, 2001 4:15 PM Subject: Re: Fw: how to recover the BSDS(emergency!!!!!!!)



> Larry, > > If you have no backups. Not even backups taken by other means than DB2 > utilities then you can try to fix the problems with the directory manually > if the damage is not too extensive..... But this may not be easy. WHat are > the errors you are getting.... > > If this is not an option.....then [...] 10845 36 44_Re: Equivalant for v$session table in ORACLE14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 31 May 2001 09:31:03 -0400540_iso-8859-1 Sithu:

There is not an equivalent of v$session in DB2. However you can get most of the information that you get from v$session by executing 'list applications' and various combinations of 'get snapshot '.

Manas.

========================================== Hi List,

I am doing a migration project from ORACLE 8.1.6 to DB2 UDB 7.1. In ORACLE there is a system table called V$SESSION. It will give the a lot of system related information. Can anybody suggest the corresponding equivalent in DB2 UDB. [...] 10882 113 23_Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 31 May 2001 09:53:05 -0400372_iso-8859-1 I would appreciate any insight into the following problem. I have had an ETR open with IBM on this for about four months and it seems all IBM can do is quote irrelevant passages from Messages and Codes and cannot read the text of the ETR.

The situation is: We have a plan running in a CICS on subsystem DBP1. The plan is bound with multiple PKLIST: [...] 10996 112 20_Re: Archive Software11_Bikash Paul19_bikash_db@YAHOO.COM31_Thu, 31 May 2001 07:23:55 -0700584_us-ascii Hi, Mark We have implemented data archival solution for DB2 in 1998. The software, we have used is called NEAR ARCHIVE from STORAGETEK. This software is not for DB2 but it is general archival software. We wrote programs using API provided by Near Archive to archive, retrieve and delete data from the archive store. Key fatures are 1) Key based archival. Data could be grouped and archived with a key and same group of data alone could be fetched using the key 2) Performance wise, it was quite satisfactory (I dont have figures) 3) Archival could be taken dual copies. [...] 11109 130 27_Re: Unexpected SQLCODE -80514_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Thu, 31 May 2001 10:36:18 -0400578_iso-8859-1 Eric:

Is it possible that under certain circumstances the CURRENT PACKAGESET is being set to DCMITMS somewhere in the code?

Also it may be a good idea to set the CURRENT PACKAGESET to DCTCOAL before you connect to DBP2.

Manas.

=================================================================== I would appreciate any insight into the following problem. I have had an ETR open with IBM on this for about four months and it seems all IBM can do is quote irrelevant passages from Messages and Codes and cannot read the text of the ETR. [...] 11240 49 42_Re: 32k pages vs 4k pages in same database11_Bikash Paul19_bikash_db@YAHOO.COM31_Thu, 31 May 2001 07:50:18 -0700534_us-ascii Hi, It is possible to have many buffer pools of different sizes - 4K, 8K,16K and 32K for V6 onwards. So it is not an issue at all

Regards, Bikash



--- john king wrote: > Hello Listers can any body tell me if I use pages of different size > for various tables in same database. The data base is having 10 > tables and there is a need for making one table with 32 page size > rather than 4 k page sizes. Is it possible under one database ? > Shall I have to do some changes [...] 11290 153 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 31 May 2001 10:50:30 -0400507_iso-8859-1 I have looked at all the programs used by this plan. None have SET CURRENT PACKAGESET. And globally, when we use SET CURRENT PACKAGESET it is never to DCMITMS. (unless some dastardly individual is doing this with (SHUDDER!!) Dynamic SQL).

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Manas Dasgupta [mailto:manas.dasgupta@PFSFHQ.COM] Sent: Thursday, May 31, 2001 10:36 AM To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805 [...] 11444 75 38_Re: Timed out problem for Export (UDB)15_Rajesh Udandrao17_urajes1@YAHOO.COM31_Thu, 31 May 2001 08:07:34 -0700573_us-ascii Jeremy, Thank you, our team is complaining that they are getting -911(reason code 68) within a minute eventhough the time out parameter is 5 min(300 sec). Can there be any other reason

Rajesh



--- Jeremy Dodd wrote: > Rajesh, > > My understanding that on Unix, you can not change > isolation for an export - it takes a read lock on > the table. So if your table isn't read only you > will, in all probability, get a lock > timeout. > > I believe you can (in theory) rebind appropriate > packages with UR, but that [...] 11520 27 47_FW: DB2V6 RID-Pool max limit during hybrid join13_Morrill, John12_JohnM@VP.NET31_Thu, 31 May 2001 09:25:25 -0600298_- Dear experts,

what happens if a hybrid join is in progress and reaches max limit in RID pool? - what happens with the temporary table already build? - how does DB2 go on (fallback to tablespace scan, never seen, nested loop?)? - how can I see (via omegamon e.g.) what really happens? [...] 11548 156 27_Re: Unexpected SQLCODE -80517_Massimo Biancucci30_Massimo.Biancucci@T-SYSTEMS.IT31_Thu, 31 May 2001 17:21:51 +0100488_us-ascii May we see the bind statement of the plan on DBP1 (DBP1 is always the origin of the transaction if I'm not wrong).

Best regards.











"Pearson, Eric L," @RYCI.COM> on 31/05/2001 14.53.05

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Unexpected SQLCODE -805 [...] 11705 51 19_Re: UDB DB Packages11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Thu, 31 May 2001 09:14:47 -0400656_us-ascii Peter,

Check the DB2 UDB SQL Events to find the actual package names being executed by your connections. Packages executed depend on CLP/utilities/clients/Java etc.

Of course, if you had our SQL-GUY tool, you could see the actual and relative ranked costs of each application, then drill down to the PACKAGES executed and see actual resources consumed at package level (totals, %), then drill down to the section numbers within a package for another cost breakdown (totals, %), then drill down into section numbers to see actual aggregated costs of unique statements executed within that section number, again totals and ranked [...] 11757 85 21_Re: UDB for AIX HELP!11_Scott Hayes34_faster.databases@DATABASE-GUYS.COM31_Thu, 31 May 2001 11:00:00 -0400620_us-ascii Hi Judy,

How have you narrowed your focus to one table? GET SNAPSHOT FOR TABLES ON dbname? How many rows read? Written? How many transactions? (Commits Attempted + Rollbacks Attempted) (GET SNAPSHOT FOR DATABASE ON dbname) How many rows read per Transaction (RRTX) for each table? If RRTX < 10, probably good. If RRTX > 10, you have a likely indexing opportunity If RRTX > 100, you've got serious trouble on your hands with substantial indexing opportunity (consider clustering, new indexes, change indexes) If RRTX > 1000, well, you've got what we call a performance crisis on your hands requiring [...] 11843 84 35_Weird CURRENT TIMESTAMP microsecond14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Thu, 31 May 2001 11:41:25 -0400372_iso-8859-1 Hi List,

I am getting the current timestamp returned from sysibm.sysdummy1 with the last 3 digits of the microsecond portion always coming as zeros (except the first time I excute the query when it comes as 001).

Is there any setting in DB2 which tells it to generate only the 1st 3 digits of the microsecond and fill the last 3 with zeros ? [...] 11928 177 25_IDUG Recap, Part I of III14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Fri, 1 Jun 2001 01:53:22 +1000342_iso-8859-1 IDUG® 2001 - North America: Packed with Power

The International DB2 Users Group (IDUG) welcomed an estimated 2,000 DB2 professionals, and more than 40 of the industry's leading vendors to the Orlando World Center Marriott, May 13-17, to exchange a wealth of DB2 information and resources at IDUG 2001 -- North America. [...] 12106 202 26_IDUG Recap, Part II of III14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Fri, 1 Jun 2001 01:54:56 +1000627_-

INSPIRATION This robust technical agenda was complemented by an inspirational keynote presentation by Beck Weathers, MD, survivor of the harrowing 1996 Mount Everest expedition recounted in Jon Krakauer's best-selling book, Into Thin Air. He explained to the audience, who rewarded him with a standing ovation, that his relentless pursuit of success and goals had made him lose sight of what was most precious to him -- his family. Left for dead during a storm that hit the tallest peak in the world, Dr. Weathers relived his experience with a packed room of IDUG attendees and shared his feelings about friends [...] 12309 226 27_IDUG Recap, Part III of III14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Fri, 1 Jun 2001 01:55:43 +1000725_-

ELECTION 2001 -2002 IDUG Board Of Directors Elected During the IDUG General Business Meeting on May 16, the slate of candidates for the 2001-2002 IDUG Board of Directors was approved by members in attendance. Officers include: President, Kathy Komer, Aetna, Inc.; President-Elect, Detlef Felser, UFD AG, and Immediate Past President, Brenda Castiel, EDS. Vice Presidents include: Shawn Bay, Vision Associates, Inc.; David Beulke, Pragmatic Solutions, Inc.; Jacqueline Bloemen, PASS IT-Consulting; Robert Catterall, CheckFree Corporation; Robert Omerza, UPS; Michael Parbs, Global Services Australia; Cathy Peck, Highmark Blue Cross Blue Shield; Carol Runyon, Johnson & Johnson Sales & Logistics Co.; and Sandy [...] 12536 202 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 31 May 2001 11:56:32 -0400573_iso-8859-1 DBP1 bind was: BIND PLAN(KT667) OWNER(DB2DBA ) QUALIFIER(DCTCOAL ) - PKLIST(DBP1.DCMABC1.*, - DBP1.DCMABC2.*, - DBP1.DCMABC3.*, - DBP1.DCMABC4.*, - DBP1.DCMABC5.*, - DBP1.DCMITMS.*, - DBP1.DCTCOAL.*, - DBP2.DCMABC1.*, - DBP2.DCMABC2.*, - DBP2.DCMABC3.*, - DBP2.DCMABC4.*, - DBP2.DCMABC5.*, - DBP2.DCMITMS.*, - DBP2.DCTCOAL.*) - VALIDATE(BIND) EXPLAIN(NO ) - ISOLATION(CS) ACQUIRE(USE ) RELEASE(COMMIT ) - CURRENTDATA(NO ) CACHESIZE(1024) - DEGREE(1 ) SQLRULES(DB2) DISCONNECT(EXPLICIT )- NOREOPT(VARS) - KEEPDYNAMIC(NO ) - ACTION(REPLACE) RETAIN - FLAG(I); [...] 12739 113 39_Re: Weird CURRENT TIMESTAMP microsecond16_Michael McCarthy18_mmccarthy@DTCC.COM31_Thu, 31 May 2001 12:04:34 -0400389_us-ascii Hi Sinha,

The same thing happens for me. I even tried to bind the db2ubind.lst and db2cli.lst file with datetime iso, and that had no effect. It seems to be a limitation of the WinNT environment, as I get the full timestamp back under DB2 for Unix.

-Mike.









"Sinha, Abhijit" on 05/31/2001 11:41:25 AM [...] 12853 99 39_Re: Weird CURRENT TIMESTAMP microsecond18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 31 May 2001 12:15:20 -0400555_iso-8859-1 The NT environment does not support the full microsecond portion of the timestamp. I don't know of anyway around it.

-----Original Message----- From: Sinha, Abhijit [mailto:Abhijit.Sinha@GS.COM] Sent: Thursday, May 31, 2001 11:41 AM To: DB2-L@RYCI.COM Subject: Weird CURRENT TIMESTAMP microsecond



Hi List,

I am getting the current timestamp returned from sysibm.sysdummy1 with the last 3 digits of the microsecond portion always coming as zeros (except the first time I excute the query when it comes as 001). [...] 12953 49 18_six position token0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Thu, 31 May 2001 11:19:15 -0500410_us-ascii Has anyone else encountered a six position token identifier (from a " -DISPLAY THREAD(*)" command). I believe that this began occurring on our production DB2 platform a few months ago and started happening when we began leaving DB2 up for a month (instead of bouncing it weekly).

Because the token is six positions, it is causing some functionality to fail in some of our vendor products. [...] 13003 68 38_Re: INDEXED ACCESS AGAINST EMPTY TABLE16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Thu, 31 May 2001 07:04:48 -0700445_us-ascii Claudio,

Thanks for the suggestion but I don't think that this option is viable since I believe that this can only be used within the context of a DECLARE CURSOR and not a singleton select.

Regards, Jeff L'Italien American Express





From: "Claudio Meluzzi Mendes" @RYCI.COM> on 05/31/2001 09:15 AM NST

Please respond to "DB2 Data Base Discussion List" [...] 13072 27 27_Re: Unexpected SQLCODE -8050_19_csutfin@AMSOUTH.COM31_Thu, 31 May 2001 11:33:36 -0500368_us-ascii Eric

Check your RCT entry for the transaction to see what Plan it uses. If there isn't an RCT for that transaction, check to see the Plan assigned to the POOL RCT entry.

It may be using the pool default Plan.

I sometime forget that one also.

Good Luck Carol Sutfin Corporate DBA AmSouth Bank csutfin@amsouth.com (205)326-5214 [...] 13100 229 27_Re: Unexpected SQLCODE -80514_Harvey Puckett25_HPUCKETT@DOAS.STATE.GA.US31_Thu, 31 May 2001 12:35:33 -0400800_US-ASCII bet it's the CURRENT PACKAGESET Register, given the intermittency ... what's the RCT look like on your CICSs for each DB2 subsystem? Any variance at all?

gotta be the register tho ...

>>> Eric.Pearson@NSCORP.COM 05/31/01 11:56AM >>> DBP1 bind was: BIND PLAN(KT667) OWNER(DB2DBA ) QUALIFIER(DCTCOAL ) - PKLIST(DBP1.DCMABC1.*, - DBP1.DCMABC2.*, - DBP1.DCMABC3.*, - DBP1.DCMABC4.*, - DBP1.DCMABC5.*, - DBP1.DCMITMS.*, - DBP1.DCTCOAL.*, - DBP2.DCMABC1.*, - DBP2.DCMABC2.*, - DBP2.DCMABC3.*, - DBP2.DCMABC4.*, - DBP2.DCMABC5.*, - DBP2.DCMITMS.*, - DBP2.DCTCOAL.*) - VALIDATE(BIND) EXPLAIN(NO ) - ISOLATION(CS) ACQUIRE(USE ) RELEASE(COMMIT ) - CURRENTDATA(NO ) CACHESIZE(1024) - DEGREE(1 ) SQLRULES(DB2) DISCONNECT(EXPLICIT )- NOREOPT(VARS) - KEEPDYNAMIC(NO ) - ACTION(REPLACE) [...] 13330 29 31_CA/Platinum versions and DB2 V60_19_csutfin@AMSOUTH.COM31_Thu, 31 May 2001 11:42:17 -0500317_us-ascii Two Questions for everyone who uses any CA/Platinum for DB2 products.

1. Is there anyone out there on DB2 V6 still on P97F or P97G of the CA/Platinum software. I need to know because we are migrating to DB2 V6 soon and I need some assurance (other than CA's) that P97G and DB2 V6 are compatible. [...] 13360 18 31_Updating tables using utilities8_K.Balaji19_K.Balaji@TARGET.COM31_Thu, 31 May 2001 11:44:47 -0500457_iso-8859-1 Hi all, I am looking for a utility like load where the input will be taken from a input file and update the necessary fields in the table. I think we can do this. Isnt ? Regards Balaji Ph # (510) 727-3259

================================================ 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. 13379 71 35_Re: CA/Platinum versions and DB2 V60_20_John_Lendman@FPL.COM31_Thu, 31 May 2001 12:56:45 -0400324_us-ascii We use Platinum 97G on DB2V6. It works fine. We have by the way tried to use 99D and am having problems with PDA trying to generate image copies where I have 900 IC per step. Also having problems with Modify it is abending with an OC4.

ohn C. Lendman DBA Jlendman@fpl.com (561) 694-5085 Beeper FPL 7413 [...] 13451 33 35_Re: Updating tables using utilities18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM31_Thu, 31 May 2001 12:57:42 -0400549_iso-8859-1 What version of DB2 are you using? OS/390, Unix, WindowsNT? There are import programs available depending on what DB2 you are running...

-----Original Message----- From: K.Balaji [mailto:K.Balaji@TARGET.COM] Sent: Thursday, May 31, 2001 12:45 PM To: DB2-L@RYCI.COM Subject: Updating tables using utilities



Hi all, I am looking for a utility like load where the input will be taken from a input file and update the necessary fields in the table. I think we can do this. Isnt ? Regards Balaji Ph # (510) 727-3259 [...] 13485 47 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 31 May 2001 13:07:18 -0400505_iso-8859-1 Explicit plan assigned to transaction in RCT.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: csutfin@AMSOUTH.COM [mailto:csutfin@AMSOUTH.COM] Sent: Thursday, May 31, 2001 12:34 PM To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805



Eric

Check your RCT entry for the transaction to see what Plan it uses. If there isn't an RCT for that transaction, check to see the Plan assigned to the POOL RCT entry. [...] 13533 79 35_Re: CA/Platinum versions and DB2 V60_22_BILL_GALLAGHER@PHL.COM31_Thu, 31 May 2001 13:14:30 -0400417_us-ascii Carol,

P97G works fine with DB2 v6 as long as you do not use any of the DB2 v6 features (triggers, LOB's, etc.). P97G is tolerant of v6 features in that it won't crash if it comes across something like a LOB column in a table, but it doesn't really know what to do with them. Instead of saying "What the heck is that?", it's more like "I know what that is, I just don't know what to do with it". [...] 13613 102 38_Re: INDEXED ACCESS AGAINST EMPTY TABLE11_Bikash Paul19_bikash_db@YAHOO.COM31_Thu, 31 May 2001 10:27:48 -0700614_us-ascii Jeff, I dont see any option now.. Let us if you find any good solution. But i would suggest to change the ONLINE application as i mentioned earlier. That certainly will help.

Regards, Bikash





--- Jeff A L'Italien wrote: > Claudio, > > Thanks for the suggestion but I don't think that this option is > viable since I > believe that this can only be used within the context of a DECLARE > CURSOR and > not a singleton select. > > Regards, > Jeff L'Italien > American Express > > > > From: "Claudio Meluzzi Mendes" @RYCI.COM> [...] 13716 15 23_Veritas and DB2 for AIX16_Swinski, Kenneth23_KSwinski@MASSMUTUAL.COM31_Thu, 31 May 2001 13:30:16 -0400419_iso-8859-1 Is anybody using the archive exit from Veritas? The exit apparently doesn't handle removing logs that have been archived to tape. How do you handle removing the logs?

================================================ 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. 13732 95 35_Re: CA/Platinum versions and DB2 V612_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Thu, 31 May 2001 13:32:54 -0400637_us-ascii We are on 97F and DB2 V6 as of tuesday and so far so good.







John_Lendman@FPL.COM@RYCI.COM> on 05/31/2001 12:56:45 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: CA/Platinum versions and DB2 V6



We use Platinum 97G on DB2V6. It works fine. We have by the way tried to use 99D and am having problems with PDA trying to generate image copies where I have 900 IC per step. Also having problems with Modify it is abending with an OC4. [...] 13828 15 43_UDB and ITO (Open View) without System View16_Swinski, Kenneth23_KSwinski@MASSMUTUAL.COM31_Thu, 31 May 2001 13:33:21 -0400430_iso-8859-1 Just wondering if anybody out there is monitoring their UDB for AIX systems and db2diag.log's using HP ITO and NOT using IBM's System VIew. If so, what do you use for templates?

================================================ 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. 13844 85 11_Re: segsize12_Reid, Louise25_ReidL@CI.FORT-WORTH.TX.US31_Thu, 31 May 2001 12:49:08 -0500546_iso-8859-1 Thanks for the information!

-----Original Message----- From: Steve Higgins [mailto:Steve_Higgins@BCBST.COM] Sent: Thursday, May 31, 2001 7:47 To: DB2-L@RYCI.COM Subject: Re: segsize





Louise, The DB2 UDB for OS/390 Administration Guide, volume 2, page 927 contains a chart that indicates the recommended SEGSIZE settings base on the number of pages in the tablespace. If you are not on this version or don't have access to the manual, let me know and I will be happy to forward the information to you. [...] 13930 23 27_Re: Unexpected SQLCODE -80513_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Thu, 31 May 2001 14:03:34 -0400546_iso-8859-1 Hello Eric, I notice the DBP2.DCTCOAL. collection is the last in the PK list in the bind. Have you played with moving it up the list? Obviously that shouldn't be necessary and doesn't make any sense. But if it has an effect, it might be something you could use to prod a response from IBM in your ETR. I notice you mention it happens with PKLists of varying sizes, so that makes my suggestion even less likely to lead to a circumvention or resolution. But I've had weirder things lead to resolution of bizarre problems like this. [...] 13954 44 27_Re: Unexpected SQLCODE -80516_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 31 May 2001 14:25:32 -0400668_iso-8859-1 I have shuffled the order several times. Does not seem to change symptoms.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Seibert, Dave [mailto:Dave_Seibert@COMPUWARE.COM] Sent: Thursday, May 31, 2001 2:04 PM To: DB2-L@RYCI.COM Subject: Re: Unexpected SQLCODE -805



Hello Eric, I notice the DBP2.DCTCOAL. collection is the last in the PK list in the bind. Have you played with moving it up the list? Obviously that shouldn't be necessary and doesn't make any sense. But if it has an effect, it might be something you could use to prod a response from IBM in your ETR. I notice [...] 13999 30 26_DB2 OS/390 V6.1 : SQL HELP0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Thu, 31 May 2001 14:01:52 -0400397_us-ascii Hi,

I'd like to update column C in table A by adding 1 second to the time stored in the column on only those rows identified in the query below. Is there an UPDATE statement that can do this?

------------------------------------------------------------------------------------ SELECT A, B, C , COUNT(*) AS NBR_OF_ENTRIES FROM TABLE A GROUP BY A,B,C HAVING COUNT(*) > 1 [...] 14030 24 12_Big IN-Lists11_Pavan Borra26_pavan.borra@PRUDENTIAL.COM31_Thu, 31 May 2001 14:33:49 -0400333_us-ascii We've an SQL that has a WHERE criterion with an IN predicate that contains too big of a list ( over 3000 policy numbers). We usually get a small list of policies in the spreadsheet, then upload it to Mainframe and then add quotes and commas to all the policy numbers, format it and cut & paste inside the IN predicate. [...] 14055 58 26_Change CCSID 65535 to 037?25_Gustavo Hernandez Varguez27_ghernandez@SOFTRENDS.COM.MX31_Thu, 31 May 2001 13:33:05 -0600318_iso-8859-1 Hello,

Is it possible to change the default CCSID 65535 of DB2/400 tables (on a AS400 box) to another (for example, 037)? Would this change corrupt the data? What could imply?

I need to read data using DB2 Connect and this seems not to handle the 65535 format, and I'm seeing hex data... [...] 14114 61 30_Re: DB2 OS/390 V6.1 : SQL HELP11_David Nance16_DWNance@FHSC.COM31_Thu, 31 May 2001 14:56:39 -0400294_US-ASCII As was stated in earlier thread this week. You cannot update a table referencing itself until V7. Since you are on V6 you would either have to come up with a programtic solution or use an interim table(copy of table a). You should be able to use something like in V7(not tested): [...] 14176 70 16_Re: Big IN-Lists0_29_sflindsey@HIGHLIGHTS-CORP.COM31_Thu, 31 May 2001 15:01:02 -0400287_us-ascii How about creating a separate table that contains the policy numbers that you can join to in your where clause? This would allow you to use a load utility to update the table with a RESUME(YES). The column attributes would be the same as the column on your original table. [...] 14247 79 16_Re: Big IN-Lists16_Bhogale, Pushkar24_Bhogale.Pushkar@MBCO.COM31_Thu, 31 May 2001 14:04:06 -0500551_iso-8859-1 Pavan,

I think an easier way might be to define a temporary table containing the 'Policy numbers' and upload it with the data from the Excel spreadsheet. Then in your SQL with the big IN predicate, you can select it from that temporary table to get the list.

This way, your SQL will remain almost static (no editing of commas etc) and all you will have to do is upload that temporary table with the new list of 'policy numbers'. I don't think that temporary table will be too much of a load in terms of space etc, and it [...] 14327 49 35_Re: Updating tables using utilities8_K.Balaji19_K.Balaji@TARGET.COM31_Thu, 31 May 2001 14:05:21 -0500481_iso-8859-1 Hi Peter, I am using OS/390. Acutally I want to update few records in production thro JCL. That is what I am looking for.

-----Original Message----- From: Krawetzky, Peter J [mailto:KrawetzkyPJ@AETNA.COM] Sent: Thursday, May 31, 2001 9:58 AM To: DB2-L@RYCI.COM Subject: Re: Updating tables using utilities



What version of DB2 are you using? OS/390, Unix, WindowsNT? There are import programs available depending on what DB2 you are running... [...] 14377 32 30_Re: Change CCSID 65535 to 037?12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 31 May 2001 12:10:00 -0700532_us-ascii I changed it just fine and it didn't impact any applications. It just made it so that I could then read the data with DB2 Connect. --- Gustavo Hernandez Varguez wrote: > Hello, > > Is it possible to change the default CCSID 65535 of DB2/400 tables > (on a AS400 box) to another (for example, 037)? Would this change > corrupt the data? What could imply? > > I need to read data using DB2 Connect and this seems not to handle > the 65535 format, and I'm seeing hex data... > > Thanks!!! > [...] 14410 47 24_FW: [DB2-L] Big IN-Lists13_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Thu, 31 May 2001 12:14:05 -0700445_iso-8859-1 You can put your IN-list in a table and change the SQL to join or use an exist subquery. Then you can then use utilities to load data from the spreadsheet to the table. Add an index to the table for performance.

DT









-----Original Message----- From: Pavan Borra [mailto:pavan.borra@PRUDENTIAL.COM] Sent: Thursday, May 31, 2001 12:34 PM To: DB2-L@RYCI.COM Subject: [DB2-L] Big IN-Lists [...] 14458 53 25_Re: QMF VER 2.0 RELEASE 412_Vernon, John27_John.Vernon@TRS.STATE.TX.US31_Thu, 31 May 2001 14:32:58 -0500342_iso-8859-1 You wrote: Migration from QMF R2.4 to R3.3 is supported. It's a simple process. You will have to check with QMF support on the jump from R2.4 to R6.

It should be noted that my understanding is that QMF v6 is "part" of DB2 V6, and that you cannot order QMF v6 to try and use it with any other version other than DB2 V6. [...] 14512 29 15_Reference guide16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Thu, 31 May 2001 15:34:41 -0400369_iso-8859-1 Back in the Platinum glory days (B.CA.) there was a wonderful little book 'A Reference Guide for DB2 OS/390 Version 5'. I have looked for an updated (V7) version of this on the CA site and cannot seem to come up with a search argument which gives an answer. Is this manual still being published? If so, how do I get one (actually several for the site). [...] 14542 63 38_FW: [DB2-L] DB2 OS/390 V6.1 : SQL HELP13_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Thu, 31 May 2001 13:00:17 -0700369_iso-8859-1 update table A set C = C + '00:00:01' where A in (select tba.A from (SELECT A,B,C,COUNT(*)FROM TABLE A GROUP BY A,B,C HAVING COUNT(*) > 1) tba) and B in (select tbb.B from (SELECT A,B,C,COUNT(*)FROM TABLE A GROUP BY A,B,C HAVING COUNT(*) > 1) tbb) and C in (select tbc.C from (SELECT A,B,C,COUNT(*)FROM TABLE A GROUP BY A,B,C HAVING COUNT(*) > 1) tbc); [...] 14606 49 30_Re: Change CCSID 65535 to 037?25_Gustavo Hernandez Varguez27_ghernandez@SOFTRENDS.COM.MX31_Thu, 31 May 2001 15:04:57 -0600421_iso-8859-1 Thanks Myron, do you have the command to change it? should I change it for the entire table or for each field?

did you try changing just the CCSID of the profile user you use to logged in?

----- Original Message ----- From: Myron Miller Newsgroups: bit.listserv.db2-l To: Sent: Thursday, May 31, 2001 1:10 PM Subject: Re: Change CCSID 65535 to 037? [...] 14656 91 30_Re: DB2 OS/390 V6.1 : SQL HELP13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 31 May 2001 15:02:23 -0500419_us-ascii Ed,

In OS/390 V7 or non-OS/390 then this is easily coded as a non-correlated subquery with an IN list row expression to the outer update (but you asked for V6).

In OS/390 V6 it is a little trickier but may provide adequate performance. Due to the inability to code a self-referencing subquery in an UPDATE, you will need to first store the result in a temporary table (created or declared). [...] 14748 35 62_=?iso-8859-1?B?Uulm6XJlbmNl?=: CA/Platinum versions and DB2 V613_Marcel Lepage28_marcellepage@MONCOURRIER.COM31_Thu, 31 May 2001 16:09:46 -0400 14784 31 15_JDBC maintenace16_JOHN G. MATTHEWS26_HC#JGM@MVS.MEDCTR.UCLA.EDU31_Thu, 31 May 2001 13:10:00 -0700583_- I have planned the following to handle JDBC maintenance for DB2 for OS390 and am looking to find out if this is the standard way to handle this.

The SMPE will target the /SERVICE/usr/lpp/db2 directory which is backed by a MVS HFS file. After install, this MVS file will be copied to a 'user' MVS file which will back the /usr/lpp/db2 directory which is what the users access. After customization in the /usr/lpp/db2 directory, the JDBC & SQLJ sample tested fine. I will now copy the 'user' MVS file over the 'SERVICE' MVS file, because, when future JDBC maintenance is [...] 14816 77 43_FW: [DB2-L] Updating tables using utilities13_Daniel Turner28_daniel_turner@ALBERTSONS.COM31_Thu, 31 May 2001 14:13:00 -0600471_iso-8859-1 here's some shell jcl.

//DSNTEP2 EXEC PGM=IKJEFT01 //STEPLIB DD DSN=?,DISP=SHR //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(?) RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) /* //SYSIN DD * UPDATE TABLE A SET C = C + 1 WHERE C < 0; /* //



-----Original Message----- From: K.Balaji [mailto:K.Balaji@TARGET.COM] Sent: Thursday, May 31, 2001 1:05 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Updating tables using utilities [...] 14894 46 19_Re: Reference guide5_chris22_hhardy1@HOUSTON.RR.COM31_Thu, 31 May 2001 15:14:05 -0500628_iso-8859-1 The latest would be CA Reference Guide DB2 UDB for OS/390 Version 6.........

----- Original Message ----- From: "Pearson, Eric L," Newsgroups: bit.listserv.db2-l To: Sent: Thursday, May 31, 2001 2:34 PM Subject: Reference guide



> Back in the Platinum glory days (B.CA.) there was a wonderful little book > 'A Reference Guide for DB2 OS/390 Version 5'. I have looked for an updated > (V7) version of this on the > CA site and cannot seem to come up with a search argument which gives an > answer. > Is this manual still being published? If so, how [...] 14941 80 19_Re: Reference guide0_19_csutfin@AMSOUTH.COM31_Thu, 31 May 2001 15:16:56 -0500362_us-ascii Eric

Talk to your CA/Platinum Sales person. Yes, they still publish the DB2 Reference Guides. V7 was to be available at IDUG but might not have made it.

They can be ordered and sent out to you. I just got a box of V6 Guides for our programmers.



Carol Sutfin Corporate DBA AmSouth Bank csutfin@amsouth.com (205)326-5214 [...] 15022 66 35_Re: Updating tables using utilities16_Duane Lee - ATCX22_DLee@MAIL.MARICOPA.GOV31_Thu, 31 May 2001 13:23:15 -0700450_iso-8859-1 Have you looked into using DSNTIAD? You can find information on this program in the Installation Guide.

Duane

-----Original Message----- From: K.Balaji [mailto:K.Balaji@TARGET.COM] Sent: Thursday, May 31, 2001 12:05 PM To: DB2-L@RYCI.COM Subject: Re: Updating tables using utilities



Hi Peter, I am using OS/390. Acutally I want to update few records in production thro JCL. That is what I am looking for. [...] 15089 15 19_Re: Reference guide13_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Thu, 31 May 2001 16:30:32 -0400387_us-ascii FYI,

BMC has one for Ver 7. It is call DB2 UDB Family Reference Guide. It is for all environments OS/390, UNIX, Windows, and OS/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. 15105 27 33_Re: Question about character sets12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 31 May 2001 15:21:38 -0500420_- This is a very long story, and so I'll have to provide just a couple of pointers. The typical answer for most western Europe customers is code page 500, called Latin 1 or a CCSID that is similar, like 1252. Once you use a different code page, you have all of the challenges of making sure that the codes entered match those yoou identify, and then being sure that the characters displayed and printed also match. [...] 15133 25 14_Re: wait times14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Thu, 31 May 2001 16:37:26 -0400297_ISO-8859-1 Message text written by DB2 Data Base Discussion List >If page-ins/(monitor period time) is > 100 /sec it may be because there's a lack of storage.<

I generally consider it a real problem when paging is > 10/sec for the sum of the pools, with a real workload on the system. [...] 15159 27 26_DB2 Connect and ColdFusion10_Rob Wright17_rwright@LIC.CO.NZ30_Fri, 1 Jun 2001 08:55:25 +1200538_us-ascii Hi

We have ColdFusion 4.5 on a Solaris 2.8 UNIX server with DB2 Connect Client 6.1. The DB2 Connect V7 server is on a separate Solaris box - the CF doco does not say it supports V7 clients. We are trying to talk to DB2 V6 for OS/390 from CF. We are having problems getting CF to talk to DB2 using Native drivers, although the CF doco says this is possible. ODBC works fine, although I would rather use Native (the ODBC drivers supplied with CF are from Merant and I want to reduce the number of software suppliers in [...] 15187 47 19_Re: Reference guide13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 31 May 2001 16:02:47 -0500545_us-ascii BMC Software, Inc. and YL&A has a reference guide for DB2 Version 7 available now. They gave it away at IDUG in Orlando.

Willie

"Pearson, Eric L," wrote:

> Back in the Platinum glory days (B.CA.) there was a wonderful little book > 'A Reference Guide for DB2 OS/390 Version 5'. I have looked for an updated > (V7) version of this on the > CA site and cannot seem to come up with a search argument which gives an > answer. > Is this manual still being published? If so, how do I get one (actually > several for [...] 15235 16 16_Migrating to DB215_Cecilia Pernada41_cecilia_pernada.consultant@PEOPLESOFT.COM31_Thu, 31 May 2001 14:17:25 -0700412_us-ascii Hi! We're planning on migrating our databases from SQL Server to DB2. I am wondering if anyone would have references/pointers to share.

Thanks in advance.

================================================ 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. 15252 124 42_Re: DB2 OS/390 V6.1 : SQL HELP...THANK YOU0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Thu, 31 May 2001 17:37:19 -0400536_us-ascii To all

Thanks for responding. Yes, I was trying to modify data corrupted by an application. I used Terry Purcell's solution (temporary table). See below.

Many thanks!!!

Ed







Terry Purcell @RYCI.COM> on 05/31/2001 04:02:23 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: Re: DB2 OS/390 V6.1 : SQL HELP [...] 15377 51 19_Re: Reference guide13_Thomas, Janis19_Janis.Thomas@CA.COM31_Thu, 31 May 2001 16:07:56 -0400424_iso-8859-1 Eric -

Computer Associates has continued to produce the DB2 Reference Guide and it is currently available for DB2 V6. We are working on the version 7 reference guide, but I don't have an estimated time frame right now when this will be available. Normally we give these out at IDUG & various other shows/conferences. Please email me directly if you want a V6 ref guide for now & I can get one to you. [...] 15429 112 20_Re: Migrating to DB210_Larry Wood18_LarryWood@SOPH.COM31_Thu, 31 May 2001 16:53:58 -0500502_iso-8859-1

Cecilia, are you just moving the data to DB2?

If the applications are going too, what languages are they written in and what do you want them to be when they get to the mainframe?

Larry Wood Sophisticated Business Systems 972-664-9005 LarryWood@soph.com http://www.soph.com/

-----Original Message----- From: Cecilia Pernada [mailto:cecilia_pernada.consultant@PEOPLESOFT.COM] Sent: Thursday, May 31, 2001 4:17 PM To: DB2-L@RYCI.COM Subject: Migrating to DB2 [...] 15542 107 38_Re: INDEXED ACCESS AGAINST EMPTY TABLE13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 31 May 2001 17:05:34 -0500353_us-ascii Jeff,

Apologies for arriving late into this discussion but I have some thoughts that I haven't yet seen mentioned.

The first question I would ask is whether the CARDF and NPAGES catalog table columns reflect the fact that this table has only one row. I'm sure you are not using default statistics, but I thought I would ask. [...] 15650 112 24_Re: Online Reorg Problem16_Brookman, Gerald30_gerald.brookman@ATOSORIGIN.COM31_Thu, 31 May 2001 18:14:27 -0400576_iso-8859-1 We have identical environments with long running CICS update and/or read transactions. We use "DRAIN ALL" which was finalized after enormous amounts of grief! Runs every weekend for all large tablespaces in production.

REORG TABLESPACE PDB00001.TSPACE01

COPYDDN(DB01TS01) RECOVERYDDN(DR01TS01)

SHRLEVEL CHANGE MAPPINGTABLE PDB2.MAPPING_TABLE

TIMEOUT TERM DRAIN ALL

OFFPOSLIMIT INDREFLIMIT

STATISTICS TABLE INDEX

sample for indexes:

REORG INDEX PDB2.IMASTCP1

SHRLEVEL CHANGE TIMEOUT TERM DRAIN ALL [...] 15763 35 20_Re: Migrating to DB211_Jakobs, Jef18_JJakobs@HARTIC.COM31_Thu, 31 May 2001 17:15:44 -0500433_iso-8859-1 Cecilia,

I'd check out the IBM website. There is a tool there (think it's free) that does part of the migration for you. Also keep in mind that you normally migrate into one database (with separate schema's).



Jef -----Original Message----- From: Cecilia Pernada [mailto:cecilia_pernada.consultant@PEOPLESOFT.COM] Sent: Thursday, May 31, 2001 4:17 PM To: DB2-L@RYCI.COM Subject: Migrating to DB2 [...] 15799 50 20_Re: Archive Software14_Richard Yevich26_Richard_Yevich@YLASSOC.COM31_Fri, 31 May 2002 18:03:47 -0500675_iso-8859-1 If OS/390, check out DB2 Row Archive Tool. Does exactly what you are asking for.

Regards, Richard

Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) IBM DB2 and Sysplex Gold Consultant IBM Certified Solutions Expert: DB2 V7.1 Database Administration for OS/390 http://www.YLAssoc.com



> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Mark Anzmann > Sent: Thursday, May 31, 2001 6:54 AM > To: DB2-L@RYCI.COM > Subject: Archive Software > > > Listers- > > We are in the process of investigating data archive/restore processes for > our DB2 system and I am wondering [...] 15850 67 30_Re: Change CCSID 65535 to 037?12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 31 May 2001 16:39:28 -0700512_us-ascii Did both, all fields in the table. And did it just for one use and for all users. WHen I did it for all users, I did it for all tables as well. Will try to find the command. ITs been a while since I did this and I don't remember it that well. I'm not an AS/400 person. --- Gustavo Hernandez Varguez wrote: > Thanks Myron, do you have the command to change it? should I change > it for the entire table or for each field? > > did you try changing just the CCSID of the [...] 15918 141 27_Re: Unexpected SQLCODE -80513_LOMBARD,Shaun27_Shaun.LOMBARD@DEWRSB.GOV.AU30_Fri, 1 Jun 2001 09:47:02 +1000634_iso-8859-1 Eric,

It should not make a difference, but I would run the DSN1CHKR and CHECK INDEX utilities just to make sure you're dealing with a consistent catalog and directory.

Shaun

-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Thursday, 31 May 2001 23:53 To: DB2-L@RYCI.COM Subject: Unexpected SQLCODE -805



I would appreciate any insight into the following problem. I have had an ETR open with IBM on this for about four months and it seems all IBM can do is quote irrelevant passages from Messages and Codes and cannot read the text of the ETR. [...] 16060 26 8_DB2 Load17_Charles Jambrosic22_CJambrosic@CSIHOME.COM31_Thu, 31 May 2001 17:36:51 -0700544_us-ascii We have a table defined with 10 columns COL1 through COL10.

We will load data every few months but the input data will not contain COL2 or COL3. These 2 columns will be related to the date they are loaded and we would like to avoid writing a program to load data into those two columns. I was hoping that with the load utility you could somehow set a default value for those two fields even though they are not on the input file. Any recomendations or should we resign ourselves to loading those two columns via a program. [...] 16087 59 20_Re: Archive Software13_Olson, Carlos14_COlson@QRS.COM31_Thu, 31 May 2001 18:00:13 -0700428_iso-8859-1 If you're at least version 5 of DB2 on OS390 and depending on your requirements, you may want to check out using an IBM REORG with DISCARD processing as a free archiving solution. Example:

REORG TABLESPACE CCDBCRSI.CRSARITS LOG NO SORTDATA NOSYSREC SORTKEYS SORTDEVT SYSDA SORTNUM 8 DISCARD FROM TABLE QRSIMAG.ARI_ASN_RJT_ITM WHEN (REC_CRT_TS < CURRENT TIMESTAMP - 6 MONTH) STATISTICS TABLE INDEX KEYCARD [...] 16147 81 44_Re: Equivalant for v$session table in ORACLE14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Thu, 31 May 2001 20:19:36 -0500376_iso-8859-1 Hi Sithu,

DB2 and Oracle are very different (thankfully! I'd not like to work with too many Oracle look-alikes....).

The closest thing I can think of in DB2 UDB (assuming you are NT/UNIx) would be the command:

list applications for database MyUserDB show detail

which will give you this info (it's very wide and wraps in an email): [...] 16229 35 66_DB2 V5.1 - SQL PL Stored Proc. calling another SQL PL Stored Proc.13_Lourdes Kishi14_lkishi@BKB.COM31_Thu, 31 May 2001 22:21:51 -0500652_- Hi All,

We are running under OS/390. We had a problem with a C program calling a SQL PL Stored Procedure that calls another SQL PL Stored Procedure. In the execution we received:

**** LPPC0001: Relacao de Colunas (LPSP0001) Column Name Shearched: TBNAME *** Call LPSP0001 DSNT408I SQLCODE = -751, ERROR: A STORED PROCEDURE HAS BEEN PLACED IN MUST_ROLLBACK STATE DUE TO SQL OPERATION CALL DSNT418I SQLSTATE = 42987 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNX9CAC SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' [...] 16265 46 12_Re: DB2 Load16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 1 Jun 2001 10:14:26 +0530575_- Charles,

I think you can use DEFAULTIF option for LOAD. It seems it will serve your purpose. You can go thru' the utility manual and then look for syntax.

Regards Sanjeev

> -----Original Message----- > From: Charles Jambrosic [SMTP:CJambrosic@CSIHOME.COM] > Sent: Friday, June 01, 2001 6:07 AM > To: DB2-L@RYCI.COM > Subject: DB2 Load > > We have a table defined with 10 columns COL1 through COL10. > > We will load data every few months but the input data will not contain > COL2 > or COL3. > These 2 columns will be related to the date they are [...] 16312 38 35_Re: Updating tables using utilities16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Fri, 1 Jun 2001 10:41:02 +0530373_- What kind of input you are mentioning ? Is it the complete SQL which will be taken from the input file or the value which is to be updated to. DSNTIAD is available but the complete SQL needs to be specified. If you are planning to generate the update statement and then use the data in the input file, i am afraid you need to write your own little program for that. [...]