1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l May 2001, week 1 2 18 73_Heiko =?iso-8859-2?Q?Schm=E4lzle=2FUSR=2FDVGKA=2FDE_ist_au=DFer_Haus=2E?=16_Heiko Schmaelzle25_heiko.schmaelzle@SI-BW.DE30_Tue, 1 May 2001 01:00:50 +0100333_iso-8859-2 Ich werde außer Haus sein von 30.04.2001 Bis 07.05.2001.



Gruß H. Schmälzle ===============================================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. 21 61 52_Re: Should SAP be in a separate subsystem on OS/390?23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Tue, 1 May 2001 06:18:58 +0100312_iso-8859-1 Terry

from where I'm sitting I wouldn't do it unless the other app was small and could live within the bounds laid down by SAP.

We are 4-way datasharing, 1.3TB and rising ... i.e we're a fairly large SAP shop and I would think twice before allowing a non SAP system into my system. [...] 83 89 15_DB2 AIX Problem11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID30_Tue, 1 May 2001 15:36:29 +0700454_us-ascii Dear all,

We are having some problem when we are trying to connect to one of our DB on AIX (SGDB2X). The problem occured after one of our staff restart the AIX server while the DB was in a Rollback process. After the AIX was restarted, we could access to all of our DBs except SGDB2X. In the AIX process monitor, we could see that the AIX (or DB2 Server) is still processing the Harddrive where all of SGDB2X tablespaces are placed. [...] 173 93 15_DB2 AIX Problem11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID30_Tue, 1 May 2001 15:36:29 +0700454_us-ascii Dear all,

We are having some problem when we are trying to connect to one of our DB on AIX (SGDB2X). The problem occured after one of our staff restart the AIX server while the DB was in a Rollback process. After the AIX was restarted, we could access to all of our DBs except SGDB2X. In the AIX process monitor, we could see that the AIX (or DB2 Server) is still processing the Harddrive where all of SGDB2X tablespaces are placed. [...] 267 47 29_Copy DB from OS/390 to UDB/NT14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK30_Tue, 1 May 2001 11:51:42 +0100324_iso-8859-1 Hi.

Without buying anything, is there a neat way of copying a DB2 for OS/390 V5 database's DDL (including tablespaces, tables, indexes, constraints etc.) to DB2 UDN for NT V7.1?

I thought of writing a skeleton UDB DDL and generating it on the m/f and FTPing it down. Has anyone else done this? [...] 315 77 33_Re: Copy DB from OS/390 to UDB/NT12_Kent Collins15_kcollins@WF.NET30_Tue, 1 May 2001 06:37:42 -0500541_iso-8859-1 Yes. Using db2look to copy the ddl. Then using either db2move or export/import to copy the data. If you have RI, using db2move will also require that you put the tables in order by RI before you import them.

Robert M. Collins Jr ( Kent ) IBM Certified DB2 UDB DBA Phone: 940.241.2242 Email: kcollins@wf.net ----- Original Message ----- From: "Davage, Marcus" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, May 01, 2001 5:51 AM Subject: Copy DB from OS/390 to UDB/NT [...] 393 34 40_Re: Transfer data between Oracle and DB214_Hanus, Michael21_Michael_Hanus@BMC.COM30_Tue, 1 May 2001 06:39:56 -0500489_iso-8859-1 You can use BMC Log Master to generate SQL from a DB2 subsystem and then apply it to your Oracle database. There is also another BMC product, Apply Plus which can be used to apply SQL to Oracle databases with restart capabilities.

Mike Hanus BMC Software

-----Original Message----- From: WilliamsByron@RUSSELLCORP.COM [mailto:WilliamsByron@RUSSELLCORP.COM] Sent: Monday, April 30, 2001 4:26 PM To: DB2-L@RYCI.COM Subject: Transfer data between Oracle and DB2 [...] 428 51 43_Re: Disaster Recovery W/O copy of SYSUTILX?11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Tue, 1 May 2001 08:10:43 -0400462_iso-8859-1 I agree that if you could not get a copy of sysutilx you could initialize it. Your only vulnerability here is what you would have done with utilities in flight. In our scenario we recover to a twice daily "syncpoint". Our BMC Recovery software terminates all utilities at the D/R site. We don't intend to restart them anyway. If you need to know what was "inflight" perhaps a display that is recorded as part of your D/R process would be enough. [...] 480 18 36_Transfer data between Oracle and DB214_Hanus, Michael21_Michael_Hanus@BMC.COM30_Tue, 1 May 2001 07:34:55 -0500514_iso-8859-1 You can use BMC Log Master to generate SQL from a DB2 subsystem and then apply it to your Oracle database. There is also another BMC product, Apply Plus which can be used to apply SQL to Oracle databases with restart capabilities.

Mike Hanus BMC Software

================================================ 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. 499 57 25_Re: Update statement help16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Tue, 1 May 2001 08:48:06 -0400527_iso-8859-1 You can do this in DB2 V7 in an SQL statement.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Schaeffer, Dave [mailto:dave.schaeffer@HONEYWELL.COM] Sent: Monday, April 30, 2001 6:04 PM To: DB2-L@RYCI.COM Subject: Update statement help



Is there a way to issue a update statement that refers back to itself? We are getting this data from an outside firm and the "name" keeps changing month to month. The table has the following columns: [...] 557 30 4_test13_George, Alice26_Alice_George@THOMSON.CO.UK30_Tue, 1 May 2001 14:27:49 +0100472_iso-8859-1 test



********************************************************************** This email and any files and/or attachments transmitted with it are confidential and are intended solely for the use of the individual or entity to whom they are addressed.

If you have received this email in error, please notify the Systems Manager at Thomson Holidays Ltd., by telephoning +44 (0)20 7387 9321, by reverse charge or collect call if you wish. [...] 588 37 40_Re: Transfer data between Oracle and DB214_Ruth Gramolini26_rgramolini@TAX.STATE.VT.US30_Tue, 1 May 2001 09:53:38 -0400626_iso-8859-1 You can export and delimited ascii file from DB2 and use Oracle sql*loader to import the data.

HTH, Ruth ----- Original Message ----- From: "Hanus, Michael" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, May 01, 2001 8:34 AM Subject: Transfer data between Oracle and DB2



> You can use BMC Log Master to generate SQL from a DB2 subsystem and then > apply it to your Oracle database. There is also another BMC product, Apply > Plus which can be used to apply SQL to Oracle databases with restart > capabilities. > > Mike Hanus > BMC Software > > [...] 626 49 19_Re: DB2 AIX Problem14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Tue, 1 May 2001 10:02:25 -0400631_iso-8859-1 Endy:

DB2 is probably doing a crash recovery. Look at db2diag.log file in sqllib/db2dump directory. You should be able to see if a crash recovery was started.

Manas.



====================================================== Dear all,

We are having some problem when we are trying to connect to one of our DB on AIX (SGDB2X). The problem occured after one of our staff restart the AIX server while the DB was in a Rollback process. After the AIX was restarted, we could access to all of our DBs except SGDB2X. In the AIX process monitor, we could see that the AIX (or DB2 Server) is [...] 676 20 8_Re: test20_Beighton-Dykes, Hugh27_hugh.beighton-dykes@EDS.COM30_Tue, 1 May 2001 15:16:40 +0100494_iso-8859-1 snip

>If you have received this email in error, please notify the Systems Manager >at Thomson Holidays Ltd., by telephoning +44 (0)20 7387 9321, by >reverse charge or collect call if you wish.

snip

Oh, isn't it tempting!

================================================ 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. 697 29 19_Re: DB2 AIX Problem14_Richard Yevich17_ryevich@YAHOO.COM30_Tue, 1 May 2001 07:46:58 -0700723_us-ascii http://biz.yahoo.com/iw/010501/0101026478.html

Press release on DB2 over Oracle in the business world.

===== Regards, Richard +---+---+---+---+ IBM DB2 and Sysplex Gold Consultant IBM Certified Solutions Expert: DB2 V7 Database Administration OS/390 richard_yevich@ylassoc.com YL&A -- www.ylassoc.com

__________________________________________________ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.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. 727 15 36_Re: Access Path Selection Statistics12_Roger Miller19_millerrl@US.IBM.COM30_Tue, 1 May 2001 09:51:33 -0500334_- So it's probably time to think about getting Craig's Fourth edition, which has the updates for V6. The Administration Guide gets some changes in this area for almost every release as we try to make the optimizer improvements, so using the right level of documentation is important.

Roger Miller, DB2 for z/OS and OS/390 [...] 743 19 11_DB2 Connect14_Penrod, Dale E19_dale.penrod@EDS.COM30_Tue, 1 May 2001 10:29:53 -0500348_iso-8859-1 We are running the Oracle Transparent Gateway for DB2 on various images. Does anyone know if DB2 Connect is an IBM replacement for the Oracle Gateway? As with the Oracle Gateway, there is software to be installed on OS/390 and the server, is this also true for DB2 Connect? Can anyone share any case studies with me on DB2 Connect? [...] 763 131 71_FW: [DB2-L] CA/Platinum No Bashing - reality/fact - CA Response t o NYT14_Grainger, Phil20_Phil.Grainger@CA.COM30_Tue, 1 May 2001 16:32:45 +0100447_iso-8859-1 If anyone is interested, take a look at http://www.ca.com/invest/nytimes_response.pdf

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

-----Original Message----- From: Colin Fay [mailto:cfay2@CSC.COM] Sent: 30 April 2001 13:57 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] CA/Platinum P99D - No Bashing - reality/fact [...] 895 17 40_Re: Transfer data between Oracle and DB212_Roger Miller19_millerrl@US.IBM.COM30_Tue, 1 May 2001 10:08:02 -0500274_- The newest way to get data into DB2 Version 7 from a wide variety of databases is using the LOAD utility and getting the needed data from a cursor. This can use DRDA to get the data from other DBMS, such as Oracle using DB2 Connect and the Relational Connect option. [...] 913 104 38_Re: Upgrade CA DB2 Tools: P97G to P99D9_Rob Crane22_racrane@CONCENTRIC.NET30_Tue, 1 May 2001 09:55:49 -0600380_us-ascii You can change RSPMAIN to pass the subsystem you selected onto RSPDEF. Look in your HLQ.CLIST. Add the following.

SET &SYS = 'your subsystem variable' ISPEXEC VPUT(SYS HIGHLVL) PROFILE

This will pass the subsystem value you came into RSPMAIN with onto RSPDEF and then you will retain the SSID you choose versus the last one saved in your ispf profile. [...] 1018 70 35_Re: DB2 Utilities Evaluation Advice16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Tue, 1 May 2001 17:43:30 +0100438_us-ascii Glenn, it's in the list archives... http://www.ryci.com/db2-l

Ciao!

Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.

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

1 Archipelago Lyon Way Frimley Camberley Surrey GU16 7ER UK



* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER [...] 1089 22 18_Help for Db2 - DBA14_subrata mondal25_subratamondal@HOTMAIL.COM30_Tue, 1 May 2001 16:47:35 -0000 1112 85 8_TRIGGERS15_Conoway, Roslyn28_Roslyn_Conoway@COMPUWARE.COM30_Tue, 1 May 2001 14:07:54 -0400545_iso-8859-1 Assuming you have changed your SPUFI default SQL TERMINATOR to #. I think you need to end your SET CURRENT statement with # not semi-colon.

========================================

Date: Mon, 30 Apr 2001 08:47:39 -0500 From: "Panicker, Lethika" Subject: Triggers

Hi ,

Can someone tell me how I can use my SET CURRENT SQLID while creating a Trigger . I am using SPUFI and the statement delimiter is # . While using my current SQLID I am receiving the following error code: [...] 1198 53 75_Re: FW: [DB2-L] CA/Platinum No Bashing - reality/fact - CA Response t o NYT18_Anyanso Agwu-Okeke17_pagwu@HOTMAIL.COM30_Tue, 1 May 2001 14:34:34 -0400 1252 81 33_FW: [DB2-L] Update statement help13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Tue, 1 May 2001 12:38:36 -0600508_iso-8859-1 It is release dependant.

But what would the SQL be?

update table a set name = (select name from table b where a.shipto = b.shipto and a.incentive_date = (select max(c.incentive_date) from table c where c.shipto = a.shipto)) where a.incentive_date < (select max(d.incentive_date) from table d where e.shipto = a.shipto)) and name <> (select name from table e where a.shipto = e.shipto and a.incentive_date = (select max(f.incentive_date) from table f where f.shipto = a.shipto)) [...] 1334 44 69_We need several UDB DBAs to work on a long term project in Phoeni x!!10_West, Dawn16_dwest@AJILON.COM30_Tue, 1 May 2001 14:42:49 -0400334_- Ajilon is currently looking for UDB DBAs to work in Phoenix on a multi-year contract. We have been awarded a project to provide DBA support to a major Phoenix client which could last for many years. We currently have 6-7 positions still open for DB2 and UDB DBAs with at least 3 years DBA experience in mainframe environments. [...] 1379 37 16_Locking question10_Radha Rani24_radha_rani@ALTAVISTA.COM30_Tue, 1 May 2001 11:53:33 -0700294_- USED TO BE that no dynamic SQLs were allowed to be performed on a database if a DDL is being executed on any dependent object for that database. The reason cited was that dynamic SQLs take a shared lock on DBD while DDLs take an exclusive lock; both of them being mutually incompatible. [...] 1417 39 32_Press release on DB2 over Oracle14_Richard Yevich17_ryevich@YAHOO.COM30_Tue, 1 May 2001 12:23:14 -0700542_us-ascii http://biz.yahoo.com/iw/010501/0101026478.html

Press release on DB2 over Oracle in the business world.

===== Regards, Richard +---+---+---+---+ IBM DB2 and Sysplex Gold Consultant IBM Certified Solutions Expert: DB2 V7 Database Administration OS/390 richard_yevich@ylassoc.com YL&A -- www.ylassoc.com



===== Regards, Richard +---+---+---+---+ IBM DB2 and Sysplex Gold Consultant IBM Certified Solutions Expert: DB2 V7 Database Administration OS/390 richard_yevich@ylassoc.com YL&A -- www.ylassoc.com [...] 1457 132 64_Re: max number of volumes in a DB2 stogroup vs SMS storage group9_Al Heiden31_alheiden@NORTHWESTERNMUTUAL.COM30_Tue, 1 May 2001 15:10:40 -0500289_ISO-8859-1 I have been interested in this thread since we have our DB2 databases in SMS pools, and was concerned when I read the possible 59 volume limit for the SMS pool.

We are running DB2 V5 for OS/390 under OS/390 V2.9, and are in the process of upgrading to OS/390 V2.10. [...] 1590 99 74_Re: FW: [DB2-L] CA/Platinum No Bashing - reality/fact - CAResponse t o NYT15_Donna O'Connell26_donna.oconnell@SAFEWAY.COM30_Tue, 1 May 2001 15:41:12 -0700728_-

"WorldSecure Server " made the following annotations on 05/01/01 16:00:33 ------------------------------------------------------------------------------ Warning: All e-mail sent to this address will be received by the Safeway corporate e-mail system, and is subject to archival and review by someone other than the recipient. This e-mail may contain information proprietary to Safeway and is intended only for the use of the intended recipient(s). If the reader of this message is not the intended recipient(s), you are notified that you have received this message in error and that any review, dissemination, distribution or copying of this message is strictly prohibited. If you have received this [...] 1690 93 75_Re: FW: [DB2-L] CA/Platinum No Bashing - reality/fact - CARespons e t o NYT12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Wed, 2 May 2001 10:02:10 +1000374_- Usually I don't like propagating these kind of threads. But .............



First let me say I have the HIGHEST regard for some of the CA TECHNICAL staff, especially people like Phil over in the UK, and I know some of the TECHNICAL lads in the land of OZ, and have a high respect them too. BUT CA as an organisation I have no respect for. [...] 1784 40 25_Cursor vs Temporary table11_Bikash Paul19_bikash_db@YAHOO.COM30_Tue, 1 May 2001 17:02:59 -0700513_us-ascii Hi everyone,

I am designing a reporting application where data is extracted from lot of OLTP tables and inserted into the final fact table. I am not able to decide whether to use temporary table to keep the intermediate data at different stages and the finally put into final result table. Here data could be inserted using INSERT INTO .. SELECT .. FROM OR to use cursor to fetch from different tables separately and insert into/update the final result table each row at a time. [...] 1825 25 80_2-tier access to DB2 for update without giving users direct update authority ...10_Rob Wright17_rwright@LIC.CO.NZ30_Wed, 2 May 2001 12:24:44 +1200561_us-ascii We have a requirement for providing 2 tier access to our production DB2 envrionment for update purposes. The applications will be written in Delphi. The problem we have is that we do not want to give users update access to the database. From an application perspective, it would be desirable for Delphi to produce a DBRM so that we can BIND the application to DB2, thus removing the need to give users direct access. Is this possible with Delphi? I have had a look at the Delphi doco, but cannot find any reference as such to creating a DB2 DBRM. [...] 1851 14 22_DB2 Apar Database Link0_20_bjnigh@HOUSEHOLD.COM30_Tue, 1 May 2001 16:40:48 -0700387_us-ascii Does anyone know the IBM DB2 for OS/390 Apar Database Link? Our computers were reinstalled and we lost the link in our favorites folder.

================================================ 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. 1866 33 35_Re: JCL for running SPUFI in Batch.14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 1 May 2001 19:01:57 -0500605_- Harry

Search the archives for "spufi and batch". You'll find some JCL that does (or at least did - in a previous version of DB2) run SPUFI in batch. SPUFI, not DSNTIAUL or DSNTEP2, real SPUFI.

James Campbell

>-----Original Message----- >From: Harry William [mailto:NRv893@AOL.COM] >Sent: Saturday, April 28, 2001 11:21 AM >To: DB2-L@RYCI.COM >Subject: JCL for running SPUFI in Batch. > > >Hi All, > I have to run SPUFI to retrieve data over a period of 3 years.If i have >to execute it takes considerable amount of time.Is there any JCL to execute >the SQL in the back ground. [...] 1900 38 43_Re: Usage of IDENTITY columns - pitfalls ??14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 1 May 2001 19:27:50 -0500567_- CON: You can't reset the generated value without dropping the table. PRO: [sorry, but I can't think of one that outweighs that con!]

What I did in a system last year, was have a 'shadow key table' for primary keys, a single column, as PK, with IDENTITY GENERATED ALWAYS and row level locking. The system would insert a row, extract the generated value, delete the row; then use the generated value in the real table. Meant that the real table was not subject to the con. This also pre-positioned the system to convert to SEQUENCEs when they come to DB2 [...] 1939 81 17_Re: Response time9_john king24_john_king@REDIFFMAIL.COM30_Wed, 2 May 2001 02:28:21 -0000414_- Yes john what you guessed is right. The application is accessing from web to databse db2. The response time they need is 2 seconds. All joins and predicates should be unique keys. I m analyzing that way. Also i m making the tables as partioned and making it clustered. Could you tell me some thing about connection pool. I have never worked in connection pool. Could you guide me about the connection pool. [...] 2021 139 25_Re: Update statement help13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 2 May 2001 00:20:48 -0500418_iso-8859-1 Daniel,

Firstly I would only insert into the temporary table the records with the latest shipto date. And if there are a large number of records in this table I would consider using a V6 declared temporary table (which can have an index created on it, but does an incremental bind at execution), or just a standard table with an index (and do a load replace if you are concerned about logging). [...] 2161 76 29_Re: Cursor vs Temporary table13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 2 May 2001 00:28:35 -0500447_us-ascii Bikash,

Temp table usage as you describe significantly reduces the amount of application code required - No cursor and loop containing individual fetch and insert statements. DB2 basically does the same thing for you in one single statement and for all rows.

Not only can this reduce the application effort, but I have seen this technique reduce a programs elapsed time by 90%. As always though, your mileage may vary. [...] 2238 36 26_Re: DB2 Apar Database Link13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 2 May 2001 00:28:37 -0500501_us-ascii Try http://techsupport.services.ibm.com/s390/techKnow

Regards Terry Purcell Yevich Lawson & Assoc www.ylassoc.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of bjnigh@HOUSEHOLD.COM Sent: Tuesday, May 01, 2001 6:41 PM To: DB2-L@RYCI.COM Subject: DB2 Apar Database Link



Does anyone know the IBM DB2 for OS/390 Apar Database Link? Our computers were reinstalled and we lost the link in our favorites folder. [...] 2275 76 29_Re: Cursor vs Temporary table16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 2 May 2001 11:09:37 +0530370_- Hi Bikash,

I hope you are designing one application per table for this kind of requirement. Also the tables used for the reporting purpose will be the denormalized tables(generalized comments !!). The fact that single application per table (or not) will decide whether to use Temp tables or the direct processing and insertion to the mail reporting table. [...] 2352 70 20_Re: Locking question16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 2 May 2001 11:22:10 +0530396_- I am not sure where from you have got the locks status information but it does not look to me the output of -DIS DB(xxx) ...... However, it seems that your DDL is already completed before the dynamic SQL started or caching of dynamic SQL is turned on and for this reason, no lock is taken on the DBD when a statement is prepared for insertion in the cache or for a statement in the cache. [...] 2423 45 34_Re: Help on REORG of tables spaces16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 2 May 2001 11:50:00 +0530614_- When was the RUNSTAT last run ? It seems it was run on the unorganized table and then the reorg is done. So possibly, candidates for sequential prefetch might be using synch. I/Os.....

Let's check the RUNSTAT and then rebind the application(if not done !!)

Regards Sanjeev

> -----Original Message----- > From: Vivekananthan Ettiappan [SMTP:vivdb2@HOTMAIL.COM] > Sent: Monday, April 30, 2001 10:54 PM > To: DB2-L@RYCI.COM > Subject: Help on REORG of tables spaces > > Hello group, > > When you do a re-org of tablespaces, what are all the follow up activities > to be completed?. We did [...] 2469 35 49_Re: MS Access olny moving 510 characters - Urgent17_Michael Hohenauer18_mhohenauer@EPO.ORG30_Wed, 2 May 2001 08:30:00 +0200509_us-ascii as far as i know the problem is msaccess, 'cause it truncates after 512

Carl Newton wrote:

> All, > > We have a problem moving data from SQL Server to DB2 V6 on OS/390 and > are desperately looking for some assistance. We have a column on SQL > Server V7 defined as IMAGE size 16 going to a column in DB2 of > BLOB(1Mb). We import the data into MS Access from SQL Server and then do > a link table to DB2 through DB2 Connect and perform a SELECT * FROM > ..... into the DB2 table. [...] 2505 82 35_Re: JCL for running SPUFI in Batch.15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL30_Wed, 2 May 2001 09:07:14 +0200626_iso-8859-1 Harry,

This JCL works fine with us on DB2 UDB V6 for OS/390. However it is DSNTEP2.

Kind regards,

Jaap Slot DB2 DBA

tel: 0031 (0)30 2152220 fax: 0031 (0)30 2153003 mailto:J.P.Slot@rf.rabobank.nl



//*------------------------------------------- //* SPUFI VIA BATCH //*------------------------------------------- //SPUFI EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSPRINT DD DSN=&&JESIE,SYSOUT=* //SYSTSPRT DD DSN=&&JESIE,SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DSN) RETRY(0) TEST(0) RUN PROGRAM(DSNTEP2) END //* //SYSIN DD * SET CURRENT SQLID = 'XXXX'; SELECT COUNT(*) FROM TABLE; //* [...] 2588 24 43_Re: Disaster Recovery W/O copy of SYSUTILX?10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 2 May 2001 09:09:14 +0200343_us-ascii I used CA tools to (try to) build a DR procedure, but it's not exciting expecially with DataSharing.

If you cannot COPY SYSUTILX why don't you try to make a copy with ADRDSSU (DFSMSdss) with TOL(ENQF) instead

to delete/define it ? So (at least) you have a 'fuzzy copy' of SYSUTILX.

Regards

Max Scarpa [...] 2613 49 39_Re: Image Copy Registration in syscopy.16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 2 May 2001 12:48:44 +0530334_- As said by James, it can be updated using the ZPARM flag(At Own Risks). But my concern is, will this be going to help anyway ?. In this case does creating a record in SYSCOPY, and even keeping all sorts of things required, in consistent state(manually).... will we be able to recover the tablespace ? I am not sure about this. [...] 2663 23 26_Re: DB2 Apar Database Link10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 2 May 2001 09:15:58 +0200482_us-ascii Here the link u need (I think):

http://techsupport.services.ibm.com/s390/support

'Technical documents' is the link to APARs.



HTH & Regards

Max Scarpa

Free DB2 sysprog (until next 13th May)

================================================ 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. 2687 44 49_GSE DB2/IMS Users Conference - Reykjavik, Iceland16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Wed, 2 May 2001 08:35:24 +0100401_us-ascii Hi list(s),

any of you I don't know of yet, going to be attending the above conference?

I am off to the airport to fly to Reykjavikf any of you are there and would like to meet for a "slag Oracle" or "Mframe is the best" drinking session, call me on my mobile (Iceland IS Europe, isn't it :-S ) or look me up at the Radisson SAS Hotel. Or meet you in the conference halls? [...] 2732 97 36_Re: Access Path Selection Statistics16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Wed, 2 May 2001 10:40:22 +0100505_us-ascii Daryl,

re. 2 - below, our DB/Explain has a function called What-If which gives the user the opportunity to capture stats from one subsystem and update into another before an explain is made against the target subsystem. The user can also store the actual SQL executed to update the catalog so he/she can go back at any time and make a replication, as well as the possibility to save different versions of stats which can be used to determine which gives the most optimal access path. [...] 2830 22 34_Re: Help on REORG of tables spaces10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 2 May 2001 12:55:10 +0200542_us-ascii Statistics not updated is the first things to consider.

If tablespace is SMS-managed it may happens that the VSAM file is relocated to a very busy DASD or in a poor

performance pool or (if partitioned) all partitions reside in the same DASD device.

HTH

Max Scarpa

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 2853 37 31_Declaration of arrays in oracle14_Mohan Bezawada29_Mohan_Bezawada@CPR.SATYAM.COM30_Wed, 2 May 2001 16:41:55 +0530286_- Hi List One of my colleague is involved in a project conversion from cobol to oracle. Can someone help me in coding a variable arrays in oracle for the corresponding Cobol table declaration. Ex: 01 table-fiche occurs 58. 05 table-entry occurs 3. 10 f1 PIC X(3). 10 F2 PIC X(20). [...] 2891 37 26_Re: DB2 Apar Database Link0_25_Ed_Vetock@NAVYFEDERAL.ORG30_Wed, 2 May 2001 07:36:45 -0400724_us-ascii ibmlink.ibm.com







bjnigh@HOUSEHOLD.COM@RYCI.COM> on 05/01/2001 07:40:48 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc:

Subject: DB2 Apar Database Link



Does anyone know the IBM DB2 for OS/390 Apar Database Link? Our computers were reinstalled and we lost the link in our favorites folder.

================================================ 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. [...] 2929 67 35_Re: Declaration of arrays in oracle19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Wed, 2 May 2001 07:20:02 -0500639_iso-8859-1 Mohan, First, COBOL "OCCURS" clauses violate relational DB design. Second, it would be much better to use DB2 UDB!

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at 314-235-6854 and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or [...] 2997 62 15_Re: DB2 Connect12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 2 May 2001 05:21:08 -0700317_us-ascii DB2 Connect is not in my opinion a direct replacement for the Oracle gateway, depending upon what you are doing. DB2 Connect and Relational Connect are. With the Oracle gateway you can access Oracle and DB2 tables in the same SQL statement and function. You can't do this in DB2 connect that I know of. [...] 3060 33 43_Re: Disaster Recovery W/O copy of SYSUTILX?11_Jose' Gomez21_Jose_Gomez@WENDYS.COM30_Wed, 2 May 2001 08:27:16 -0400554_us-ascii Sounds to me that if your customer are committed to staying off the system get the applications in RO, do not run any other utilities during the time you are running image copies. After 5 successful DR tests using a "backup window" where DB2 can do its thing and there is no inflight transactions is the way to go. We use FDR to do full vol backups after DB2's IC are done, and that formula has worked for us every time at the hot site. I know there are other products for mirroring or snapshot that provide more flexibility (unfortunately [...] 3094 21 12_db2 function15_Neff, Stephen R16_NeffSR@STATE.GOV30_Wed, 2 May 2001 08:57:27 -0400360_iso-8859-1 Hello,

A table has a column defined as for bit data. I can substring the column to retrieve parts of the data. The substring part I retrieve is hex '79C' or something like that. I would like to translate this hex/binary value to a smallint. I would like to use a DB2 function to do this. Is this possible? We are a db2v6 OS390 platform. [...] 3116 20 11_DB2 Version12_Rakesh Kumar21_rakesh457@HOTMAIL.COM30_Wed, 2 May 2001 13:00:03 -0000537_- All, Can anyone tell me if DB2 V7.0 for OS/390 is available or is it still the beta version for DB2 V7.0 for OS/390 ? Thanx

Regards Rakesh _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 3137 37 15_Re: DB2 Version16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Wed, 2 May 2001 18:45:55 +0530705_- I believe its GA.

Regards Sanjeev

> -----Original Message----- > From: Rakesh Kumar [SMTP:rakesh457@HOTMAIL.COM] > Sent: Wednesday, May 02, 2001 6:30 PM > To: DB2-L@RYCI.COM > Subject: DB2 Version > > All, > Can anyone tell me if DB2 V7.0 for OS/390 is available or is it still > the > beta version for DB2 V7.0 for OS/390 ? > Thanx > > Regards > Rakesh > _________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. [...] 3175 43 15_Re: DB2 Version15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Wed, 2 May 2001 08:33:20 -0500429_ISO-8859-1 It is GA. We have it installed in our sandbox.

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

"We have met the enemy and he is us." - Walt Kelly "Pogo Comic Strip"



-----Original Message----- From: Rakesh Kumar [mailto:rakesh457@HOTMAIL.COM] Sent: Wednesday, May 02, 2001 8:00 AM To: DB2-L@RYCI.COM Subject: DB2 Version [...] 3219 50 15_Re: DB2 Version11_John Curran20_John.Curran@DRKW.COM30_Wed, 2 May 2001 14:34:02 +0100532_iso-8859-1 It has gone GA at the end of March

Have a look at these two sites

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

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



John Curran

-----Original Message----- From: Rakesh Kumar [mailto:rakesh457@HOTMAIL.COM] Sent: 02 May 2001 14:00 To: DB2-L@RYCI.COM Subject: DB2 Version



All, Can anyone tell me if DB2 V7.0 for OS/390 is available or is it still the beta version for DB2 V7.0 for OS/390 ? Thanx [...] 3270 26 43_Re: Usage of IDENTITY columns - pitfalls ??14_Harold Trammel19_hlt@NAPCC.ASPCA.ORG30_Wed, 2 May 2001 08:46:59 -0500686_us-ascii At 07:27 PM 5/1/2001 -0500, you wrote: >CON: You can't reset the generated value without dropping the table. >PRO: [sorry, but I can't think of one that outweighs that con!]

This is now possible with FixPack 2. You use the following:

alter table "tableName" alter column "columnName" restart with "desiredNumber"

___________________________________________________________________________________ Harold L. Trammel, Pharm. D., Senior Director, ASPCA Animal Poison Control Center (http://www.apcc.aspca.org) University of Illinois Allied Agency Hotline: 888.426.4435 Admin: 217/337-5030 Fax: 217/337-0599 "... encourage one another daily ..." Heb 3:13 [...] 3297 57 43_Re: Usage of IDENTITY columns - pitfalls ??15_Philip, Sibimon20_SPhilip@CSXLINES.COM30_Wed, 2 May 2001 09:48:38 -0400483_- Does any new version of BMC load utility support loading a table with identity column. We use load plus 5.1.

Thanks..sibi

-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: Tuesday, May 01, 2001 07:28 PM To: DB2-L@RYCI.COM Subject: Re: Usage of IDENTITY columns - pitfalls ??



CON: You can't reset the generated value without dropping the table. PRO: [sorry, but I can't think of one that outweighs that con!] [...] 3355 30 15_Re: DB2 Version13_Hanne Lyssand10_han@VPS.NO30_Wed, 2 May 2001 15:39:03 +0200457_us-ascii It's GA. We got it from IBM last week. We haven't installed it jet but will start as soon as possible.

Regards Hanne





********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. [...] 3386 97 35_Re: JCL for running SPUFI in Batch.12_rl_cotterill24_rl_cotterill@BIGPOND.COM30_Thu, 3 May 2001 00:00:02 +1000301_us-ascii Hi, It is possible to run the real SPUFI (the one from DB2I) in batch we do it. I wrote a CLIST driver which sits on SPUFI many years ago. To get it to work you need to set up a dummy panel which suppresses the DISPLAY when invoked.

I have seen people doing similar via REXX etc. [...] 3484 16 16_Re: db2 function15_Walter Janissen27_walter.janissen@VICTORIA.DE30_Wed, 2 May 2001 08:49:43 -0500445_- Hello Stephen

We haved faced the same problem. We have looked a long time for a function but didn't find any. So we used a CASE-Expression in the Select-List to do the transformation.

HTH

================================================ 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. 3501 161 29_Re: Cursor vs Temporary table11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 2 May 2001 07:09:09 -0700432_us-ascii Thanks terry and sanjeev for prompt response.

I have requirement to pull data from multiple tables and insert or update the final fact table.

So when whiling pulling the data, we can either a cursor to build the resultset OR a temporary table to do the same.

It is like T1, T2, T3 -> CSR1 T3,T3,T4 -> CSR2 T1,T5,T4 -> CSR3 Then from CSR1 -> FINAL_TABLE (Insert) CSR2, CSR3 -> FINAL_TABLE (Update) [...] 3663 143 35_Re: JCL for running SPUFI in Batch.11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 2 May 2001 07:33:16 -0700569_us-ascii Hi, The JCL looks allright.. But you have to use a plan with the program also. So the SYSTSIN card will looks like //SYSTSIN DD * RUN PROGRAM(DSNTEP2) PLAN(DSNTEP61) /*

The customisation jobs are available to prepare DSNTEP2 and bind the package DSNTEP61

Regards, Bikash --- rl_cotterill wrote: > Hi, > It is possible to run the real SPUFI (the one > from DB2I) in batch we do it. I wrote a CLIST driver > which sits on SPUFI many years ago. To get it to > work you need to set up a dummy panel > which suppresses [...] 3807 67 85_Re: 2-tier access to DB2 for update without giving users direct u pdate authority ...13_Andy Seuffert21_aseuffert@NEONSYS.COM30_Wed, 2 May 2001 09:33:39 -0500413_- Rob, Shadow Direct from NEON Systems, the company I work for, provides the capibility to run static SQL with DB2 OS/390 from any client ODBC application including Delphi. We provide a utility to create a DB2 DBRM from your application and binding that DBRM into a DB2 plan. This provides full plan based security and static SQL support. If you are interested, please contact us or me directly if you wish. [...] 3875 89 34_Re: Help on REORG of tables spaces11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 2 May 2001 07:40:34 -0700462_us-ascii Hi, After reorg, it is important to run RUNSTAT and eventually bind the package. Sometime, that might not be feasible if table changes everyday. In that case, it is better to go for manual catalog statistics update for predicted no of rows and bind the packages. (This could be done once in month depending upon the how rapidly the table data grows) We used to have same kind of problem with the access path. So we have used manual catstat update. [...] 3965 76 39_Re: Image Copy Registration in syscopy.34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM30_Wed, 2 May 2001 10:48:07 -0400612_us-ascii It is possible to recover the tablespace based on this manually added entry. I have done this using few tricks, which I am not going to discuss.

But yes, so long as there is a good entry in SYSCOPY, the recover is not a problem (tested & worked).

-Venkat Pillay





> -----Original Message----- > From: S, Sanjeev (CTS) [SMTP:SSanjeev@CAL.COGNIZANT.COM] > Sent: Wednesday, May 02, 2001 3:19 AM > To: DB2-L@RYCI.COM > Subject: Re: Image Copy Registration in syscopy. > > As said by James, it can be updated using the ZPARM flag(At Own Risks). > But > my concern is, [...] 4042 31 19_Re: db2 aix problem11_Endy Lambey27_endyl@MITRAINFOSARANA.CO.ID30_Wed, 2 May 2001 21:49:25 +0700411_us-ascii Thank you Karen, Manas & Mark.

We have finally found out what our problem was. We only have 2 Harddrive, one was used for AIX system, and the other is for our 3 active transaction databases. Whenever one of our 3 DB was processing big/large data, we could not access our other db's. So we are intending to add another 6 Harddrives and we will span our tablespaces accross those Harddrives. [...] 4074 185 37_FW: [DB2-L] Cursor vs Temporary table13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Wed, 2 May 2001 07:59:18 -0700337_iso-8859-1 Paul:

How bad would the sql be for t1,t2,t3,t4,t5 > CSR1 -> FINAL_TABLE (Insert)?

It would give the optimizer a better idea of what you want. There may be a better access path than the three cursor approach.

You're right about the trip out to the DASD farm (and back). Often that cost is overlooked. [...] 4260 97 39_Re: Image Copy Registration in syscopy.15_Murley, Michael22_Michael_Murley@BMC.COM30_Wed, 2 May 2001 10:16:10 -0500323_iso-8859-1 Venkat,

Even if you are able to get the SYSCOPY entry back in place, you also need to consider any SYSLGRNX entries that may have been deleted (by Modify ?) if you plan to apply any log records. Otherwise, the recovery may seem to run Ok but leave the data wrong because no log records were applied. [...] 4358 91 29_Re: Cursor vs Temporary table12_Gregg Sawyer26_gregg.a.sawyer@VERIZON.COM30_Wed, 2 May 2001 11:25:57 -0400322_us-ascii If the table format is predictable, you might consider using permanent tables for the transient data instead of creating temporary tables; this would allow you to eliminate much of the overhead you are concerned about. You would need simple mechanisms to key the temporary data, and to age it off the table. [...] 4450 94 15_V6 Broken Pages8_Ken Kane24_kkane@UBSPAINEWEBBER.COM30_Wed, 2 May 2001 11:11:26 -0400459_iso-8859-1 Hello, We recently migrated to DB2 V6 OS/390 and have been hit 3 times since with broken pages. Has anyone else experienced or heard of an increase in such unwelcome occurrences? We had gone years without any ! I'd appreciate any information in tracking down the root cause whether it be V6 related or not. The only other dramatic change in our environment would be a recent proliferation of Stored Procedures and migration to WLM. Any clues? [...] 4545 124 39_Re: Image Copy Registration in syscopy.34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM30_Wed, 2 May 2001 11:37:09 -0400631_us-ascii Agreed. What I did was for different purpose and was good for my scenario.

I would definitely discourage anyone doing this for many obvious reasons.

-Venkat Pillay





> -----Original Message----- > From: Murley, Michael [SMTP:Michael_Murley@BMC.COM] > Sent: Wednesday, May 02, 2001 11:16 AM > To: DB2-L@RYCI.COM > Subject: Re: Image Copy Registration in syscopy. > > Venkat, > > Even if you are able to get the SYSCOPY entry back in place, you also need > to consider any SYSLGRNX entries that may have been deleted (by Modify ?) > if > you plan to apply any log records. Otherwise, [...] 4670 54 83_Re: 2-tier access to DB2 for update without giving users directupdate authority ...12_DOUG KESTNER23_DHSTS57@DHS.STATE.IL.US30_Wed, 2 May 2001 10:37:26 -0500440_US-ASCII We made all access to the database thru stored procedures. We gave users access to the stored procedure or possibly a view of the data, but not the tables.

Doug

Doug Kestner - DataBase Administration - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Illinois Department of Human Services Information Systems, Technical Services, System Support 100 South Grand [...] 4725 83 43_Re: Usage of IDENTITY columns - pitfalls ??13_Bright, Randy20_Randy_Bright@BMC.COM30_Wed, 2 May 2001 10:57:38 -0500421_iso-8859-1 BMC LoadPlus for DB2 does not yet support identity columns. If you are loading an identity column defined as "GENERATE BY DEFAULT", contact me directly or call our support line (1-800-537-1813). We may have a limited solution for you.

Full support for identity columns is planned in LoadPlus for DB2 in the next release (beyond V6.1.00). This is currently planned for 4th quarter-ish availability. [...] 4809 27 19_DB2/OS390 v DB2 UDB11_Anne Fanous25_anne.fanous@CONSIGNIA.COM30_Wed, 2 May 2001 16:41:05 +0000440_us-ascii Hi, Our management are currently conducting a 'database review'. One of the things we have been asked is what are the differences between DB2/OS390 and the other flavours of DB2. As we are DB2/OS390 we don't really know. To save us trawling through all the documentation for the various DB2's could anyone summarise very briefly the main differences between DB2 OS/390 and say DB2 for AIX or DB2 for NT? Thanks for your help. [...] 4837 55 19_Re: V6 Broken Pages19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Wed, 2 May 2001 11:28:42 -0500353_iso-8859-1 Hi Ken, Did the broken pages get automatically repaired? If not, and I'm going from memory here, I recall that V6 does better/more checking on space map pages, etc. especially when DSN1COPY was involved. There are many APARs related to broken pages -- might be worth going thru them to see if you can get a hit on your specific problem. [...] 4893 80 17_db2 v5.1 problem:13_Derez D. Lusk20_ddlusk@HOUSEHOLD.COM30_Wed, 2 May 2001 11:47:44 -0500835_us-ascii hello...

we are having a problem with querying a table: this is the dclgen EXEC SQL DECLARE ACCT TABLE ( ACCT_NUM CHAR(19) NOT NULL, BUS_PORTF_CDE CHAR(3) NOT NULL, (IE1.2) ---------------------------------------------------------------------------------------------------



CUST_GLOBL_NUM DECIMAL(11, 0) NOT NULL (FK) (IE1.1) PROD_CDE_GLOBL CHAR(5) NOT NULL, OPEN_DT DATE NOT NULL, REGN_PROCS_CDE CHAR(4) NOT NULL, ORG_CDE SMALLINT NOT NULL, LOGO_CDE SMALLINT NOT NULL, STORE_NUM_ORIG CHAR(9) NOT NULL, CLOSE_DT DATE NOT NULL, CYCLE_DAY SMALLINT NOT NULL, ACCT_SALE_IND CHAR(1) NOT NULL, COSIGNR_NAME CHAR(30) NOT NULL, RSTRUC_DT DATE NOT NULL, AUTO_PYMT_IND CHAR(1) NOT NULL, CRED_INS_CDE CHAR(1) NOT NULL, CRED_LMT_AMT DECIMAL(9, 0) NOT NULL, DLQ_STAT_GLOBL CHAR(3) NOT NULL, ACCT_STAT_CDE CHAR(1) [...] 4974 108 23_Re: DB2/OS390 v DB2 UDB13_Larry Hubbard29_Larry_Hubbard@CMS.STATE.IL.US30_Wed, 2 May 2001 11:51:40 -0500313_us-ascii Hi Anne,

In general, the application oriented stuff is very similar (a SELECT is a SELECT). However "under the covers" where DBA works, things start changing. I'll just list a few of the major things and you can see if that is enough.

Assume UDB = OS/390 and udb = Windows, unix, etc. [...] 5083 113 21_AW: db2 v5.1 problem:16_Roland Schiradin28_SchiradinR@ALTE-LEIPZIGER.DE30_Wed, 2 May 2001 19:12:02 +0200759_iso-8859-1 I believe there was a problem with DSNTEP2 and LE

We add the following parm for DSNTEP2

PARM = "PARMS('STORAGE(NONE,NONE,00,8K)/ALIGN(MID)')"

RUN PROGR("PROG") PLAN("PLAN")" PARM

Roland

> -----Ursprüngliche Nachricht----- > Von: Derez D. Lusk [SMTP:ddlusk@HOUSEHOLD.COM] > Gesendet am: Mittwoch, 2. Mai 2001 18:48 > An: DB2-L@RYCI.COM > Betreff: db2 v5.1 problem: > > hello... > > we are having a problem with querying a table: > this is the dclgen > EXEC SQL DECLARE ACCT TABLE > ( ACCT_NUM CHAR(19) NOT NULL, > BUS_PORTF_CDE CHAR(3) NOT NULL, (IE1.2) > -------------------------------------------------------------------------- > ------------------------- > > > CUST_GLOBL_NUM DECIMAL(11, 0) NOT NULL (FK) [...] 5197 79 43_Re: Usage of IDENTITY columns - pitfalls ??14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM30_Wed, 2 May 2001 13:25:44 -0400424_iso-8859-1 Dear Listers,

Thanks to all of you who responded to my initial query. It was of great help. Many thanks to Susan Lawson for sending me her IDUG article on identity columns.

Regards, Abhijit



-----Original Message----- From: Philip, Sibimon [mailto:SPhilip@CSXLINES.COM] Sent: Wednesday, May 02, 2001 9:49 AM To: DB2-L@RYCI.COM Subject: Re: Usage of IDENTITY columns - pitfalls ?? [...] 5277 133 19_Re: V6 Broken Pages8_Ken Kane24_kkane@UBSPAINEWEBBER.COM30_Wed, 2 May 2001 13:19:27 -0400362_iso-8859-1

In one case, in a batch program, an attempt was made to auto-repair and this failed. In another, the broken page was discovered by a Platinum unload and no attempt was evident. DSN1COPY was not used in any case. Curiously, DSNTIAUL worked against the broken table. We will scan for the APARS. Appreciate any further thoughts. Thanks, Ken [...] 5411 128 29_Re: Cursor vs Temporary table13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM30_Wed, 2 May 2001 10:35:14 -0700601_iso-8859-1 Locks are not acquired on Temporary tables. There is no logging involved and so, if the data involved is large, creating a permanent table might offset this benefit of temporary tables. Most probably you will 'finally' be opening a cursor on the temporary table like SELECT * FROM temp-table. If so, you have nothing to fear. There will be a tablespace scan for temporary table with this statement, with or without any statistics updated in the catalog; so you don't have to worry about optimizer choosing wrong access path. Even if the pages of temporary table have been written back [...] 5540 127 19_Re: V6 Broken Pages11_Brian Lynch19_Brian.Lynch@TRW.COM30_Wed, 2 May 2001 13:37:25 -0400838_US-ASCII Ken, This was the error we were getting on the syslog:

Excerpt from DB2PMSTR address space: STC08574 DSNI014I @DB2P DSNKDPG DATA IN USE DURING ABEND REASON 00C90101 ERQUAL 5003 TYPE 00000302 NAME PSDBCE .PSTSC069.X'00CDE125' CONNECTION-ID=DB2CALL CORRELATION-ID=PSCEJDC6 LUW-ID=*



This was the PTF found:

Item PQ42883



APAR Identifier ...... PQ42883 Last Changed..01/03/05 RC00C90101 DSNKISPL ERQUAL5003



Symptom ...... AB ABEND04E Status ........... CLOSED PER Severity ................... 2 Date Closed ......... 01/01/22 Component .......... 5740XYR00 Duplicate of ........ Reported Release ......... 510 Fixed Release ............ 999 Component Name 5740 IBM DATABA Special Notice Current Target Date ..01/02/19 Flags SCP ................... Platform ............ [...] 5668 163 28_SQL UPDATE Question for v5.114_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM30_Wed, 2 May 2001 10:54:26 -0700292_iso-8859-1 Will the following work with v5.1 or 6.x/7.x only?

UPDATE tbla SET flda = (SELECT fldb FROM tblb WHERE flda1 = fldb1) WHERE flda2 = 'T' AND flda3 = 'xyz' ; I am getting: DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "fldb". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: + ) - [...] 5832 153 29_Re: Cursor vs Temporary table11_David Nance16_DWNance@FHSC.COM30_Wed, 2 May 2001 13:55:34 -0400306_US-ASCII Ashish, With V6 there are a few things with the temporary table that you aren't taking into account. You can have a declared temp table, which does do limited logging and limited locks. Also, you can create an index on this type of temp table, which means not neccessarily a tablespace scan. [...] 5986 26 12_alter column17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Wed, 2 May 2001 12:47:39 -0500432_- Hi Folks,

We are using DB2 V6, on OS390. we already created the tables. one of the column in the table is defined as a 'XXXXXX' data type is DATE and not null with default. now they want to change it is minimum date '0001-01-01' when i tried to alter it is not allowing to do that. or do i need to drop the table and the change into minimum date. or am i missing in alter the column name.... can i alter the column??? [...] 6013 30 22_DB2 Index Health Check13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Wed, 2 May 2001 14:04:04 -0400 6044 125 26_UDB -- open cursor problem12_Randy Wilson22_Randy_Wilson@BCBST.COM30_Wed, 2 May 2001 14:07:36 -0400327_iso-8859-1 Hi Folks, My environment is UDB/EEE v7.1 on AIX, with client workstations on NT fp5 . I'm getting an error when using DBArtisan on 1 database... all others work fine... I've shutdown DBArtisan and started it back up... but that didn't help... something appears to be hung up in the database. this is the error: [...] 6170 42 31_Message errors from db2diag.log17_Marcius D. Moraes22_marcius@GOLDNET.COM.BR30_Wed, 2 May 2001 15:18:19 -0300635_us-ascii Hi,

I've sent this msg recently but I had no response. Frequently I get the following messages in my db2diag.log:

----------------------------------------------------------------------------------- 2001-04-08-05.21.36.697000 Instance:DB2 Node:000 PID:266(db2syscs.exe) TID:282 Appid:none buffer_pool_services sqlbGetFreeSlot Probe:727 DIA9999E An internal error occurred. Report the following error code : "FFFFD12C".

2001-04-08-05.36.25.815000 Instance:DB2 Node:000 PID:266(db2syscs.exe) TID:304 Appid:none buffer_pool_services sqlbFreeUpSlot Probe:121

All pages in buffer pool 1 are in use. [...] 6213 40 39_Re: Image Copy Registration in syscopy.17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Wed, 2 May 2001 15:39:31 -0300318_us-ascii I prefer using a consist name standard for copy output datasets, because we delete everything with more than 45 days from syscopy. If I need anything older than this, i do DSN1COPY. We also save full DDL and DBID, PSID, OBID values in sequential files each time full copies are done, weekly and monthly. [...] 6254 69 35_Re: Message errors from db2diag.log14_Scott Saunders20_ssaunders@SIEBEL.COM30_Wed, 2 May 2001 11:42:15 -0700394_iso-8859-1 Marcius; What platform are you implemented on. In some releases of DB2 (Linux, Unix, NT), the default in syscat.bufferpools is 2000 pages, very small. Also make sure that you have adequate IOCLEANERS specified with an appropriate CHGPG_THRESH (my spelling is suspect here) so that dirty pages are written back to disk before your applications need the pages in the bufferpools. [...] 6324 26 29_Re: Cursor vs Temporary table12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Wed, 2 May 2001 13:52:54 -0500361_us-ascii Hello.

Regarding overhead, I would only add the anecdote that I've incorporated GTT's into several spots of our application and have been impressed by their great performance. They were of particular interest to me because they allowed me to break down complex problems into smaller, simpler steps but still push most of the work into DB2. [...] 6351 22 29_Re: Cursor vs Temporary table12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Wed, 2 May 2001 13:54:12 -0500558_us-ascii P.S.

We do create index(s) on our GTT's. And again, they sing for us.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Steve Grimes Washington University Information Systems OS/390 V2R10 DB2 UDB 6.1 W: 314-935-4376 H: 636-928-5005 Isa.64:6 Steve_Grimes@aismail.wustl.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. 6374 77 26_Re: DB2 Index Health Check19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Wed, 2 May 2001 13:59:58 -0500601_iso-8859-1 Hi Carlton, You really have no control over index levels -- they're determined by the size of key and number of entries. Perfect LEAFDIST is 100 (I seem to recall, might be wrong there). Anything larger indicates a reorg would have some benefit. Obviously, the larger the number the more the benefit from a reorg. The DB2 Admin Guide has recommendations on when to reorg and the SQL needed. Of course, these recommendations depend on recent and accurate RUNSTATS. Freespace and PCTFREE use, and amount, are specific to the nature of access to a table/index. An entire article could be [...] 6452 57 16_Re: alter column19_DAVIS, RICK (SBCSI)14_rd8246@SBC.COM30_Wed, 2 May 2001 14:05:54 -0500587_iso-8859-1 Hi Kumar, In V6 I think you're going to have to first run a SPUFI to change them all to 01-01-0001. Then unload. Then drop/recreate table with that column set to default to 01-01-0001. Then reload.

HTH, Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender at [...] 6510 77 26_Re: DB2 Index Health Check11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 2 May 2001 12:17:37 -0700433_us-ascii Hi, Due to heavy inserts into the tables, the keys are not organised well and that increases the level of the index and also leaf distances. Reorging the indexes will resolve the problem. As index will rebuit again during reorganisation and will bring down the leaf distance and NLEVEL. Generally , it is allright to have 3 levels in index. Howver if key is very big and table has got lot of rows, it might go up to 4. [...] 6588 217 32_Re: SQL UPDATE Question for v5.113_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 2 May 2001 14:13:54 -0500506_iso-8859-1 SQL UPDATE Question for v5.1Daryl,

V6 and above. Scalar subqueries in the Set clause of an update were implemented as part of the V6 Refresh (May 2000). I am not aware of a V5 APAR for this feature.

Regards Terry Purcell Yevich Lawson & Assoc www.ylassoc.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Johnson, Daryl Sent: Wednesday, May 02, 2001 12:54 PM To: DB2-L@RYCI.COM Subject: SQL UPDATE Question for v5.1 [...] 6806 95 30_Re: UDB -- open cursor problem11_Bikash Paul19_bikash_db@YAHOO.COM30_Wed, 2 May 2001 12:22:42 -0700346_us-ascii Hi, The problem seems to be different. I think - it is application problem. The same cursor is opened twice in the application before closing it. OR The application thread is in indoubt state.

You can not really close a cursor from outside of ur application thread. It gets flushed while program ends normally or abnormally. [...] 6902 66 32_Re: SQL UPDATE Question for v5.114_Richard Yevich17_ryevich@YAHOO.COM30_Wed, 2 May 2001 12:28:00 -0700537_us-ascii OS/390, DB2 V6 Refresh, and V7.

--- "Johnson, Daryl" wrote: > Will the following work with v5.1 or 6.x/7.x only? > > UPDATE tbla > SET flda = > (SELECT fldb > FROM tblb > WHERE flda1 = fldb1) > WHERE flda2 = 'T' > AND flda3 = 'xyz' > ; > I am getting: > DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "fldb". SOME > SYMBOLS > THAT > MIGHT BE LEGAL ARE: + ) - > > DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE > > DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR > > DSNT416I [...] 6969 38 26_Re: DB2 Index Health Check34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM30_Wed, 2 May 2001 15:40:29 -0400330_us-ascii There is a little bit of control over NLEVEL, which is by reducing the PCTFREE.

Even index splitting causes NLEVEL to go higher. Higher freepages could increase your leaf distance. choice of clustering index could affect your leaf distance. Delete & insert on the same table can cause high leaf distance too. [...] 7008 67 84_Re: 2-tier access to DB2 for update without giving users direct update authority ...16_Sreedhar Bobbadi24_sbobbadi@DUKE-ENERGY.COM30_Wed, 2 May 2001 16:11:28 -0400407_us-ascii Stored procedures could be an option.

HTH Have a nice day Sreedhar Bobbadi





Rob Wright cc: Sent by: DB2 bcc: Data Base Subject: 2-tier access to DB2 for update Discussion without giving users direct update authority ... List



05/01/01 08:24 PM Please respond to DB2 Data Base Discussion List [...] 7076 18 32_Re: SQL UPDATE Question for v5.115_Schaeffer, Dave28_dave.schaeffer@HONEYWELL.COM30_Wed, 2 May 2001 13:32:15 -0700516_- To everyone that has helped with this, I appricate all of the help.

However is there a solution to my problem, we are a OS/390 DB2 v5.1 shop?

Dave Schaeffer Database Administrator Bendix CVS (a division of Honeywell Inc.) E-Mail: Dave.Schaeffer@Honeywell.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. 7095 162 38_Re: Coalesce replacing NULL processing14_Laredo, Andrew19_Andy_Laredo@BMC.COM30_Wed, 2 May 2001 15:41:33 -0500382_iso-8859-1 I think I have an idea...

The moving back and forth, of the small amount of extra data, in this case probably has a null affect. Your program on the other hand, has a piece of assembled code to perform a very simple function (a move), DB2 has to go through some "clock work" to recognize the COALESCE and apply it. Kind of like interpreted code vs. compiled. [...] 7258 183 29_Re: Cursor vs Temporary table13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Wed, 2 May 2001 15:32:49 -0500610_us-ascii Bikash,

By now you have received a number of testimonials stating how well the global temp tables have performed well for some. I've also noticed some confusion between the two types of GTTs offered in V6.

V5 introduced Global Temp Tables - now referred to as Created GTTs. Which are predefined and instantiated in DSNDB07 when inserted into. There is no locking or logging, and access path selection is based on default stats for static SQL. You can however update CARDF and NPAGES in SYSTABLES to give the optimizer more accurate information. In V6, DB2 keeps track of the number [...] 7442 122 33_Re: Copy DB from OS/390 to UDB/NT12_tim malamphy20_timalamphy@YAHOO.COM30_Wed, 2 May 2001 13:50:18 -0700535_us-ascii I thought db2look was a UDB/NT/AIX utility, and could be used to create a file which could run on OS390 to move data from NT/AIX to OS390, but not from OS390 to NT or AIX. I don't have access to OS390 anymore, but I'd sure like confirmation that it's now available on the mainframe. tim --- Kent Collins wrote: > Yes. > Using db2look to copy the ddl. > Then using either db2move or export/import to copy > the data. > If you have RI, using db2move will also require that > you put the tables in > order [...] 7565 22 38_Who's using the IBM Performance tools?11_Robert Jans26_robert_jans@ALBERTSONS.COM30_Wed, 2 May 2001 16:06:28 -0600364_iso-8859-1 Hi, quick question. How many people are using/trialing/have trialed IBM's "Performance Suite"? Those are DB2PM, Buffer Pool Tuning Tool, DB2 Query Monitor and DB2 SQL Performance Analyzer. We are currently considering a trial of some of these products. I would be very interested in hearing about your experiences, off-line if you prefer.......... [...] 7588 187 38_Re: Coalesce replacing NULL processing13_Olson, Carlos14_COlson@QRS.COM30_Wed, 2 May 2001 15:15:33 -0700470_iso-8859-1 Does anyone know if the IFNULL function is more efficient than the COALESCE function i.e.. SELECT IFNULL(CPI_REF_CD,' ') vs. SELECT COALESCE(CPI_REF_CD,' ')

Carlos Olson Database Administrator QRS Corporation email: colson@qrs.com http://www.qrs.com



-----Original Message----- From: Laredo, Andrew [mailto:Andy_Laredo@BMC.COM] Sent: Wednesday, May 02, 2001 1:42 PM To: DB2-L@RYCI.COM Subject: Re: Coalesce replacing NULL processing [...] 7776 230 38_Re: Coalesce replacing NULL processing14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Wed, 2 May 2001 17:47:09 -0500462_iso-8859-1 They are entirely different in function and are not equivalent. The IFNULL returns a NULL when the 2 values are equal, else it returns the first, whereas the COALESCE returns the first not-NULL parameter.

Regards, Richard

Richard Yevich, YL&A IBM DB2 and Sysplex Gold Consultant IBM Certified Solutions Expert: DB2 V7.1 Database Administration for OS/390 Richard_Yevich@YLAssoc.com http://www.YLAssoc.com -- DB2 Performance Journal [...] 8007 142 33_Re: Copy DB from OS/390 to UDB/NT15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM30_Thu, 3 May 2001 09:12:28 +1000313_iso-8859-1 Haven't done this in awhile, but I think they're talking about connecting to the database that is the OS/390 DB2 subsystem from your NT/AIX box and using db2look on that, pretending it's a local database on your NT/AIX server. The magic of DB2 Connect hides the fact it's on the Big Iron instead. [...] 8150 161 38_Re: Coalesce replacing NULL processing13_Olson, Carlos14_COlson@QRS.COM30_Wed, 2 May 2001 16:55:11 -0700602_iso-8859-1 "IFNULL is identical to the COALESCE..." - see page 222 in SQL Reference Version 6. You have inadvertently described the NULLIF function rather than the IFNULL function.

-----Original Message----- From: Richard Yevich [mailto:Richard_Yevich@YLAssoc.com] Sent: Wednesday, May 02, 2001 3:47 PM To: DB2-L@RYCI.COM Subject: Re: Coalesce replacing NULL processing



They are entirely different in function and are not equivalent. The IFNULL returns a NULL when the 2 values are equal, else it returns the first, whereas the COALESCE returns the first not-NULL parameter. [...] 8312 17 26_Calculating Index key size13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Wed, 2 May 2001 21:02:42 -0400 8330 180 31_Rexx Question - EXECIO error -312_Troy Coleman19_Colematr@MEIJER.COM30_Wed, 2 May 2001 21:51:21 -0400361_US-ASCII Please forgive me for this off topic. I am not sure how to access the REXX list serve. I am getting a return code of -3 on writing to a PDS file. I have a working REXX DB2 program with the following code. I copied this code to another program and it does not seem to like the EXECIO. If you know what a return code of -3 is I would appreciate it. [...] 8511 29 35_Re: Rexx Question - EXECIO error -310_Rob Wright17_rwright@LIC.CO.NZ30_Thu, 3 May 2001 14:05:09 +1200561_us-ascii You need to change it to:

data.1=oline data.0=1

The EXECIO is expecting a STEM variable in which the .0 item is the number of lines of output. You could have:

data.1=oline1 data.2=oline2 data.0=2

"EXECIO 1 DISKW DDOUT (STEM DATA."

To output 2 lines, and so on.

HTH Rob

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 8541 62 43_Re: Disaster Recovery W/O copy of SYSUTILX?11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Wed, 2 May 2001 22:15:05 -0400388_ISO-8859-1 If you run DSN1LOGP with SUMMARY(ONLY) on your last archive log dataset, it will show you which utilities were in flight. The recommended action when you have recovered SYSUTILX depends on the utility:

For CHECK INDEX, MERGECOPY, MODIFY, QUIESCE, RECOVER, REBUILD, RUNSTATS and STOSPACE: just -TERM UTIL.

For CHECK DATA: terminate the utility and re-run it. [...] 8604 77 35_Re: Rexx Question - EXECIO error -310_teldb2kals22_teldb2kals@TELSTRA.COM30_Thu, 3 May 2001 12:24:05 +1000352_us-ascii Troy,

U might have a misplaced ADDRESS statement in ur code. U mentioned that it was a REXX DB2 program. U might be having an ADDRESS DSNREXX or something in ur code. If that is the case, then REXX doesnt recognise the EXECIO as a valid DB2 command. U will then need to reset the ADDRESS environment using the ADDRESS instruction. [...] 8682 43 18_'Commit/Rollback '11_DSI LISTSRV18_dsi.listsrv@DB.COM30_Thu, 3 May 2001 09:13:27 +0530485_us-ascii Hi Guys,

We have an application which is developed in C++ which uses DB2 CLI for the database operations. The application, to achieve its functionality, inserts, deletes and updates the tables in a UDB 6.1 Database. But we have a problem, in the sense that, some of the records in a particular table are accidentally getting deleted(may be unsuccessfuly committed ) between two db2 sessions. In connection with this problem, I wish to pose this following queries. [...] 8726 221 29_Re: Cursor vs Temporary table16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 09:53:36 +0530543_iso-8859-1 Bikash,

For all the reasons you mentioned, i don't think there is any point using temp table for this case. You should go for directly inserting the data in the fact table. We had designed data insertion MIS applications to the denormalized tables like this one and i have never thought of doing this because of all these reasons. For my case i had even compared the elapsed/CPU time once and found the method without Temp table as better than direct insertion. The code also gets lengthy without any great improvement. [...] 8948 101 35_Re: Declaration of arrays in oracle14_Mohan Bezawada29_Mohan_Bezawada@CPR.SATYAM.COM30_Thu, 3 May 2001 10:12:16 +0530561_iso-8859-1 Rick Thanks for the advice. They are developing the application using oracle, so they are forced to use that. However, one of my friend suggested using Pro* C for declaring and handling the table. Any more suggestions please...

Regards

Mohan Bezawada Satyam Computer Services. Chennai Phone: 4983221 ext. 2724 Email: Mohan_Bezawada@cpr.satyam.com

-----Original Message----- From: DAVIS, RICK (SBCSI) [SMTP:rd8246@SBC.COM] Sent: Wednesday, May 02, 2001 5:50 PM To: DB2-L@RYCI.COM Subject: Re: Declaration of arrays in oracle [...] 9050 102 22_Re: 'Commit/Rollback '16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 11:38:19 +0530378_- Siva,

Instead of using the real time monitor for the table, it is better if you choose one application and perform some manual operation along with an application debugger tool to monitor the happenings across the program as well as the database. The manual operation for you is that you have to keep selecting the records from the table for looking at your data. [...] 9153 38 22_Re: performance tuning17_Michael Hohenauer18_mhohenauer@EPO.ORG30_Thu, 3 May 2001 08:01:44 +0200565_us-ascii we made the same expierience ciao

Lucchetti Bruno wrote:

> I don't think to be able to suggest you somethings about DB2. > But i would like to say you that we had, and we are having yet, some > advantages by using the relatevly new compiler > > COBOL FOR OS/390 & VM 2.2.0 (September 2000) because it introduced better > performance in the the area of the TRUNC parameter. > Only recompiling programs (that were not perfectly written respect to > performance) we noticed that in several cases the use of CPU of executions > is less than [...] 9192 41 26_Re: DB2 Apar Database Link17_Michael Hohenauer18_mhohenauer@EPO.ORG30_Thu, 3 May 2001 08:20:36 +0200745_us-ascii have a look at http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/library



Ed_Vetock@NAVYFEDERAL.ORG wrote:

> ibmlink.ibm.com > > bjnigh@HOUSEHOLD.COM@RYCI.COM> on 05/01/2001 07:40:48 PM > > Please respond to DB2 Data Base Discussion List > > Sent by: DB2 Data Base Discussion List > > To: DB2-L@RYCI.COM > cc: > > Subject: DB2 Apar Database Link > > Does anyone know the IBM DB2 for OS/390 Apar Database Link? Our computers > were reinstalled and we > lost the link in our favorites folder. > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage at http://www.ryci.com/db2-l. The [...] 9234 44 35_Re: Declaration of arrays in oracle17_Michael Hohenauer18_mhohenauer@EPO.ORG30_Thu, 3 May 2001 08:26:33 +0200566_us-ascii the only way of doing that is to put tha table into a varchar vc-able-fiche special trick, the length part of vc-table-fiche)/length (table-entry) gives you the ocurrences, but that it is not part of the concept of a RDMS, so you shouldn't use it

Mohan Bezawada wrote:

> Hi List > One of my colleague is involved in a project conversion from cobol to > oracle. > Can someone help me in coding a variable arrays in oracle for the > corresponding > Cobol table declaration. > Ex: > 01 table-fiche occurs 58. > 05 table-entry occurs 3. > 10 [...] 9279 70 23_Re: DB2/OS390 v DB2 UDB16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 12:11:23 +0530444_- Anne,

I believe Larry have given all the major differences(as you asked for) but at the same time for your management who is doing database review, may require some more information for taking the decisions on database review. Its better if you explore through IBM's website and extract some contents from there. As far as the name is concerned, nothing is only DB2 now, everything is DB2 Universal Data Base for "platform name". [...] 9350 67 30_Re: Calculating Index key size16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 12:38:12 +0530525_- Carlton,

If i understood your requirement correctly, you need something like this.

SELECT A.CREATOR,A.NAME, B.CREATOR, B.NAME, SUM(D.LENGTH) FROM SYSIBM.SYSTABLES A, SYSIBM.SYSINDEXES B, SYSIBM.SYSKEYS C, SYSIBM.SYSCOLUMNS D WHERE A.NAME = B.TBNAME AND A.CREATOR = B.TBCREATOR AND B.NAME = C.IXNAME AND B.CREATOR = C.IXCREATOR AND C.COLNAME = D.NAME AND A.NAME = D.TBNAME AND A.CREATOR = D.TBCREATOR GROUP BY A.CREATOR,A.NAME, B.CREATOR, B.NAME ORDER BY A.CREATOR,A.NAME, B.CREATOR, B.NAME (If required) [...] 9418 194 38_Re: Coalesce replacing NULL processing16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 12:54:52 +0530675_iso-8859-1 Lots of similar(not same) things:- But WHY ??

VALUE COALESCE IFNULL

NULLIF

First 3 are identical except for the fact that IFNULL accepts only two arguments. NULLIF also accepts two arguments but it returns nulls when two values are equal otherwise returns the first value.

Regards Sanjeev

> -----Original Message----- > From: Olson, Carlos [SMTP:COlson@QRS.COM] > Sent: Thursday, May 03, 2001 5:25 AM > To: DB2-L@RYCI.COM > Subject: Re: Coalesce replacing NULL processing > > "IFNULL is identical to the COALESCE..." - see page 222 in SQL Reference > Version 6. You have inadvertently described the NULLIF function rather > [...] 9613 49 32_Re: SQL UPDATE Question for v5.116_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 13:00:14 +0530388_- I don't think with V5, this is possible with a single query. According to my knowledge, you need to write a program for this.

The answer applies only if your problem same as the problem for which this thread is opened by Daryl(DJ). The query from Daryl is below

UPDATE tbla SET flda = (SELECT fldb FROM tblb WHERE flda1 = fldb1) WHERE flda2 = 'T' AND flda3 = 'xyz' ; [...] 9663 58 16_Re: alter column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 13:10:46 +0530327_- Kumar,

Let me first know if i understood your question. You have a table with column C1 defined as not null with default. Presently for all nullable entries, it will take the value as SPACES. Now those nullable entries should have minimum date i.e. '0001-01-01'.

If this is your requirement, you need to : [...] 9722 228 38_Re: Coalesce replacing NULL processing12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM30_Thu, 3 May 2001 17:57:19 +1000419_iso-8859-1 The raw figures are below (may need to reset the font to courier), but the way I interpret the results is: - If data is predominantly not null their is a very feint benefit of using IFNULL over COALESCE. - If the data is predominantly null I don't see any benefits NB: I'm basing this on the CPU times, the SQL's are simple as per my prior posting, and the SQL is executed in a tight loop 50,000 times. [...] 9951 18 14_Visual Explain20_Carlo Petrone WZBKD627_carlo.petrone@ISTRUZIONE.IT30_Thu, 3 May 2001 10:10:17 +0200348_iso-8859-1 How I can configure the subsytem for connecet Visual Explain to OS/390?

TIA

Carlo

================================================ 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. 9970 86 16_Re: alter column17_Rajkumar Gurusamy32_Rajkumar.G@CHENNAIMAIL.LTITL.COM30_Thu, 3 May 2001 13:58:26 +0530736_us-ascii Sanjeev,

Since the column is defind as data type DATE, the default value is CURRENT DATE. We can't use C1 = spaces logic.

Regards Rajkumar









"S, Sanjeev (CTS)" on 03/05/2001 01:10:46 PM

Please respond to DB2 Data Base Discussion List

To: DB2-L@RYCI.COM cc: (bcc: Rajkumar G/LTITLCHEN)

Subject: Re: alter column





Kumar,

Let me first know if i understood your question. You have a table with column C1 defined as not null with default. Presently for all nullable entries, it will take the value as SPACES. Now those nullable entries should have minimum date i.e. '0001-01-01'. [...] 10057 87 26_Re: DB2 Index Health Check15_Lankester, Andy22_Andy_Lankester@BMC.COM30_Thu, 3 May 2001 04:23:09 -0500640_iso-8859-1 Carlton,

You should monitor NLEVEL and, assuming the cardinality has not increased significantly, reorganise as soon as it increases.

LEAFDIST. It depends on whether or not you have FREEPAGE>0. If FREEPAGE=0 then LEAFDIST can go up very rapidly for large indexes. I have seen one go to LEAFDIST=1,500,00 in 15 minutes after being reorganised! The problem is that it is not scalable with index size. A bit of maths shows that LEAFDIST/NLEAF is approximately the percentage of split pages (with FREEPAGE=0 every split leaf page goes to the end). So reorganise when LEAFDIST/NLEAF is greater than say 0.1 . Using [...] 10145 124 16_Re: alter column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 15:19:54 +0530399_- Raj,

I think(not sure) column definition mentioned by Ravi is of 'XXXXXX' data type(i.e. CHAR) but it keeps the date as per the business date. Ravi will be the right person to clarify this one.

However, if at all this is defined as DATE then C1=spaces logic can't be used. You are correct in this one but my logic was on the basis of my understanding which i clarified earlier. [...] 10270 148 16_Re: alter column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 15:32:12 +0530601_iso-8859-1 More clarification on default !!

In my last explaination, by the default for date value by DB2, i meant "default for retrieval" not for new data insertion.

However, i still may be wrong in my understanding for the main question.

Regards Sanjeev

> -----Original Message----- > From: S, Sanjeev (CTS) > Sent: Thursday, May 03, 2001 3:16 PM > To: 'DB2 Data Base Discussion List' > Subject: RE: alter column > > Raj, > > I think(not sure) column definition mentioned by Ravi is of 'XXXXXX' data > type(i.e. CHAR) but it keeps the date as per the business date. [...] 10419 80 16_Re: alter column14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU30_Thu, 3 May 2001 04:02:03 -0500396_iso-8859-1 Sanjeev,

I think in this case column C1 is of type DATE, and so NNWD will give a value of CURRENT DATE, not spaces. Your technique would basically still work, but there is no easy way to be sure that a row with C1=Current Date is actually *defaulting* to current date, or whether it has actually been *explicitly set* to the date value (unless you look at the VSAM files). [...] 10500 114 16_Re: alter column16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM30_Thu, 3 May 2001 16:15:27 +0530362_- Greg,

Perfectly agreed with your explaination. If at all this was of data type DATE then its a very difficult task once data is already there in the table. Who knows the date populated at a particular rows is current date or actual date(as per the business data). This can be easier if table already have some insert timestamp column in the table. [...] 10615 34 33_OS/320 DB2 5.1 : Field trancation19_Dimitris Margaritis20_margardi@NOVABANK.GR30_Thu, 3 May 2001 14:10:46 +0200400_iso-8859-7 Hi,

I try to select data from a table on OS/390 using Command Center on WinNT. In a culumn some characters are truncated. This doesn't happen if I select from OS/390 console. When I see the results in hex mode there is 0x0 and then the chatacters that thuncated. This also happen if I use another tool to select from windows. Has this character 0x0 special meaning? Any ideas? [...] 10650 72 23_Re: DB2/OS390 v DB2 UDB5_chris22_hhardy1@HOUSTON.RR.COM30_Thu, 3 May 2001 07:02:39 -0500678_iso-8859-1 DB2 390 Vs UDB(Open?) considerations:

1. From historical data gage expected load factor and performance? 2. Usage, CPU intensive vs I/O or combination of both, mixed? 3. Type of application/s and vendor support per the OS of choice? 4. Time measure of process/task execution? 5. Segmentation of user community? 6. Peripheral media performance per OS? 7. Technical staff knowledge (schema, business model, admin, dba, OS/390, AIX, NT)? 8. Environment justification for central vs distribute database? 9. Business unit delivery expectations? 10. # users 5,000 vs 20,000 vs 50,000 accessing the dB. 11. Utilities & Tools usage, cost, experience, vendor? 12. [...] 10723 88 30_Re: Calculating Index key size18_Bruce W Williamson26_Bruce.Williamson@NT.GOV.AU30_Thu, 3 May 2001 21:45:24 +0930555_iso-8859-1 Howzit Carlton?

Try the following:

SELECT IX.TBCREATOR , IX.TBNAME , IX.CREATOR AS IXCREATOR , IX.NAME AS IXNAME , SUM(CO.LENGTH) AS IXLENGTH FROM SYSIBM.SYSINDEXES IX JOIN SYSIBM.SYSKEYS KY ON KY.IXNAME = IX.NAME AND KY.IXCREATOR = IX.CREATOR JOIN SYSIBM.SYSCOLUMNS CO ON CO.NAME = KY.COLNAME AND CO.TBNAME = IX.TBNAME AND CO.TBCREATOR = IX.TBCREATOR WHERE IX.TBCREATOR = ? -- Optional GROUP BY IX.TBCREATOR , IX.TBNAME , IX.CREATOR , IX.NAME ORDER BY IX.TBCREATOR , IX.TBNAME , IX.CREATOR , IX.NAME FOR FETCH ONLY WITH UR; [...] 10812 43 29_? DSN1COPY and OBIDXLAT Limit0_14_dcreed@CSC.COM30_Thu, 3 May 2001 07:16:09 -0500351_us-ascii Hi all, I have a question I was hoping someone would be able to answer for me. I have a segmented tablespace which contains over 500 tables within it. This is the QMF temporary tablespace, DSQDBDEF. From an Full image copy, I had expected to be able to use DSN1COPY to be able to restore any dropped tables to an intermeditate location. [...] 10856 197 33_Re: ? DSN1COPY and OBIDXLAT Limit14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 3 May 2001 13:41:57 +0100295_iso-8859-1 It's been a while since I did an OBIDXLAT but I have a strange feeling that if DSN1COPY comes across a DBID/OBID that you haven't provided a translation for, it sops with a RC4.

I used to use this feature to find out exactly WHAT DBIDs/OBIDs etc I had in my image copies. [...] 11054 191 35_Re: Rexx Question - EXECIO error -312_Troy Coleman19_Colematr@MEIJER.COM30_Thu, 3 May 2001 08:44:53 -0400569_US-ASCII Thank you Kals, You got it. I needed to add the address TSO because I had changed the address to DB2.

Thanks again!! Troy Coleman Coleman Consulting, Inc.

>>> teldb2kals@TELSTRA.COM 05/02/01 10:24PM >>> Troy,

U might have a misplaced ADDRESS statement in ur code. U mentioned that it was a REXX DB2 program. U might be having an ADDRESS DSNREXX or something in ur code. If that is the case, then REXX doesnt recognise the EXECIO as a valid DB2 command. U will then need to reset the ADDRESS environment using the ADDRESS instruction. [...] 11246 50 35_Effect of changing commit frequency14_Michele Saffer32_Michele_Saffer@CONSULTEC-INC.COM30_Thu, 3 May 2001 09:25:55 -0400584_us-ascii We are running DB2 Version 5 on a Mainframe-OS/390. We recently conducted a test of the effect of changing the commit frequency in a batch process from once every Logical Unit of Work, to once every 100 LUWs. The batch process is basically moving data from one set of "current" tables into a mirror image set of "history" tables. The current tables are small (the biggest is about 250,000 rows at the start of the process), and the history tables are much larger (the largest has 55,000,000 rows). The process moves about 200,000 of the current records into history. It [...] 11297 32 26_UDB Script Center question0_26_Steve.Westfall@EQUIFAX.COM30_Thu, 3 May 2001 08:34:38 -0500486_us-ascii I have the UDB 7.1 Admin client software installed on my Windows 2000 workstation, and I am able to connect with it to DB2 UDB EEE 7.1 on a UNIX box. However, in both the Command Center and in the Script Center, the System Name is not displayed in the drop down list. In Command Center, I can write a script and run it successfully, but when I try to save it I can't fill in the information necessary to do so because of this problem. Any suggestions about what to check? [...] 11330 34 14_Help with join16_Katsnelson, Yury26_Yury.Katsnelson@BCBSFL.COM30_Thu, 3 May 2001 09:44:38 -0400439_iso-8859-1 Hi, Listers!

I have been working with DB2 on mainframe for quite a few years but completely unfamiliar with DB2 UDB. Our UNIX team is trying to move from Informix to DB2 UDB and they have a problem with joining tables across the databases. I know that I could do it with DB2 on OS390. I would very much appreciate if somebody could explain why this task is so different under DB2 UDB and how to resolve the problem. [...] 11365 34 24_Running V6 and V7 on AIX15_Pat DBA Roberts23_patroberts@AHOLDUSA.COM30_Thu, 3 May 2001 10:11:57 -0400497_us-ascii Hello All,

I am currently trying to set up an AIX server to run db2 udb v6 and v7 on the same machine.

The quick beginnings manual states that the v7 can co-exist with other versions of db2.

I installed v7 and created an instance. It will not start completely but gives no messages. The db2set command will not function and dasilist and db2ilist will not show the v6 instances. I did not terminate the v6 instances on the machine when I created the v7 instance. [...] 11400 54 18_Re: Help with join14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Thu, 3 May 2001 10:22:28 -0400395_iso-8859-1 Yury:

A database in DB2/AIX or NT is more like a subsystem in DB2/OS390. A database has its own catalogs and config parameter settings (logs,buffer pools etc.). So, just as you cannot join tables across subsystems, you cannot across databases in DB2 for AIX or NT , atleast not easily. You can use Datajoiner or I believe V7 onwards part of datajoiner is built into DB2 . [...] 11455 109 16_Re: alter column17_ravi kumar hassan18_ravibh@HOTMAIL.COM30_Thu, 3 May 2001 09:15:05 -0500283_- Hi Folks,

Thanks for your help, the column in the table is defined as date not a char, and it is not null with default. so i can not update the date with '0001-01-01'. since they are not caring for data. i drop the table and recreated with date column as '0001-01-01'. [...] 11565 114 30_Re: Calculating Index key size9_Al Heiden31_alheiden@NORTHWESTERNMUTUAL.COM30_Thu, 3 May 2001 10:10:45 -0500553_ISO-8859-1 We have used the following SQL to calculate Index Key Size.

SELECT B.NAME, SUM(A.LENGTH) AS KEYLENGTH FROM SYSIBM.SYSCOLUMNS A, SYSIBM.SYSINDEXES B, SYSIBM.SYSKEYS C WHERE C.IXCREATOR = 'XXXXXXXX' AND B.CREATOR = 'XXXXXXXX' AND A.TBCREATOR = 'XXXXXXXX' AND A.TBNAME = B.TBNAME AND B.NAME = C.IXNAME AND C.COLNAME = A.NAME GROUP BY B.NAME;

Al Heiden Information Systems Northwestern Mutual 720 E. Wisconsin Ave Room N07SE Milwaukee, WI 53202-4797 phone: 414-665-4771 fax: 414-665-5795 email: alheiden@northwesternmutual.com [...] 11680 57 28_Re: Running V6 and V7 on AIX14_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM30_Thu, 3 May 2001 11:19:44 -0400306_iso-8859-1 Pat:

We have one of our lab boxes that have both V6 and V7 in it.

When you say it does not start completely what do you mean? Does db2diag.log have any messages in them? Do a ps -ef | grep and see what processes are running. Compare them with V6's processes. [...] 11738 94 33_Re: ? DSN1COPY and OBIDXLAT Limit15_Murley, Michael22_Michael_Murley@BMC.COM30_Thu, 3 May 2001 10:33:50 -0500392_iso-8859-1 Danny,

I guess you could use DSN1COPY to copy with OBIDXLAT to an intermediate data set to do the first 500 tables and then from the intermediate data set to the final destination to copy the rest. I've never tried it before, but it might work. But it won't work if any of your target OBIDs from the first run are in the second set of source OBIDs you want to process. [...] 11833 136 35_Re: Rexx Question - EXECIO error -364_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM30_Thu, 3 May 2001 10:48:45 -0500328_ISO-8859-1 Address TSO will work, but I typically use Address MVS. However, since you are also using "ALLOC", you are obviously running in TSO, so you're fine.

When I write in REXX, and I write a lot of REXX, I always place the ADDRESS instruction in front of any host commands. That way, [...] 11970 77 33_Re: ? DSN1COPY and OBIDXLAT Limit9_Jim Leask27_jim.leask@RS-COMPONENTS.COM30_Thu, 3 May 2001 16:45:45 +0100666_iso-8859-1 Quick note:

>Second run >0076,00A1 <- But the 1st run created a bunch 0076 rows. You don't want >to translate them again !

I thought all OBID translations are decimal numbers?

Jim.

-----Original Message----- From: Murley, Michael [mailto:Michael_Murley@BMC.COM] Sent: 03 May 2001 16:34 To: DB2-L@RYCI.COM Subject: Re: ? DSN1COPY and OBIDXLAT Limit



Danny,

I guess you could use DSN1COPY to copy with OBIDXLAT to an intermediate data set to do the first 500 tables and then from the intermediate data set to the final destination to copy the rest. I've never tried it before, but it might work. But it [...] 12048 113 30_Re: Calculating Index key size9_Birk, Tim17_BirkT@DIEBOLD.COM30_Thu, 3 May 2001 11:57:13 -0400345_iso-8859-1 Two things to keep in mind - the LENGTH for a decimal column is the precision, not the actual length, and if a column is nullable, you need to add 1 byte. You could use something like the following:

CASE WHEN COLTYPE = 'DECIMAL' THEN (LENGTH + 2)/2 ELSE LENGTH END + CASE WHEN NULLS = 'Y' THEN 1 ELSE 0 END AS TRUE_LENGTH [...] 12162 79 40_Re: Transfer data between Oracle and DB210_Alan Johns26_AlanJ@PRINCETONSOFTECH.COM30_Thu, 3 May 2001 12:06:23 -0400379_ISO-8859-1 You might also want to look at Move for Servers from Princeton Softech. That allows relationally intact sets of data to be moved from Oracle to DB2 (mainframe and UDB) and visa versa.

Alan Visit our product consultant Alan Johns on May 1-3rd at the UK CMG Conference in Edinburgh, he will be presenting 'Archiving Relational Data - a Relational Approach'. [...] 12242 62 35_Re: Rexx Question - EXECIO error -30_19_Tim.Lowe@STPAUL.COM30_Thu, 3 May 2001 11:04:13 -0500373_us-ascii Todd, There was a very long discussion on the REXX discussion list last year about the extra cost of putting the ADDRESS in front of every statement. I will not attempt to summarize what everyone said other than to say that the extra cost is significant. But, I do agree with you that it is much easier to read and maintain if you always specify the address. [...] 12305 99 33_Re: ? DSN1COPY and OBIDXLAT Limit15_Murley, Michael22_Michael_Murley@BMC.COM30_Thu, 3 May 2001 11:12:37 -0500466_iso-8859-1 Jim,

You're right. My mistake.

R+/RESOURCE MAXIMIZER accepts either decimal or hex (hex numbers are in the format x'00A1'). I forgot that you have to use decimal with DSN1COPY (Geez, a guy can't get away with anything on this list !)

Michael

-----Original Message----- From: Jim Leask [mailto:jim.leask@RS-COMPONENTS.COM] Sent: Thursday, May 03, 2001 10:46 AM To: DB2-L@RYCI.COM Subject: Re: ? DSN1COPY and OBIDXLAT Limit [...] 12405 40 40_Re: Transfer data between Oracle and DB213_Tzachi Nissim21_tzachi@ATTUNITY.CO.IL30_Thu, 3 May 2001 19:28:06 +0200501_iso-8859-1 You could use Attunity Connect - it allows you to do something like the following:

INSERT INTO ORACLE:ORATABLE SELECT * FROM DB2:DB2TABLE

Attunity Connect can do this type of cross database cross platform operations...

Cheers, Tzachi Nissim Attunity R&D

----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Monday, April 30, 2001 11:26 PM Subject: Transfer data between Oracle and DB2 [...] 12446 109 28_Re: Running V6 and V7 on AIX15_Pat DBA Roberts23_patroberts@AHOLDUSA.COM30_Thu, 3 May 2001 13:39:18 -0400605_us-ascii The instance does stop and start.

I was assuming the instance is only starting partially because of the following:

Very short list of processes: v7t01 16192 33230 0 08:35:41 - 0:00 db2ipccm v7t01 23970 33230 0 08:35:41 - 0:00 db2gds v7t01 32920 23970 0 08:35:41 - 0:00 db2srvlst v7t01 33230 33844 0 08:35:41 - 0:00 db2sysc root 37880 38616 1 12:29:07 pts/1 0:00 grep v7t01 v7t01 38790 23970 0 08:35:41 - 0:00 db2resyn No response from instance: v7t01 $ db2 get dbm cfg DB21015E The Command Line Processor backend process request queue or input queue was not created within the [...] 12556 76 30_Re: Calculating Index key size17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Thu, 3 May 2001 14:56:40 -0300451_us-ascii Carlton,

This SQL works fine for me:



SELECT DISTINCT(A.NAME), SUM(C.LENGTH), A.UNIQUERULE, A.INDEXTYPE FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSKEYS B, SYSIBM.SYSCOLUMNS C WHERE A.TBCREATOR = '"creator"' AND C.TBCREATOR = '"creator"' AND A.TBCREATOR = C.TBCREATOR AND A.TBNAME = C.TBNAME AND A.NAME = B.IXNAME AND A.TBCREATOR = B.IXCREATOR AND B.COLNAME = C.NAME GROUP BY A.NAME, A.UNIQUERULE, A.INDEXTYPE ORDER BY 1, 2 [...] 12633 150 35_Re: Rexx Question - EXECIO error -317_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Thu, 3 May 2001 14:59:28 -0300491_us-ascii Troy,

RC=-3 means that command was not understood. You need to check what was you last ADDRESS command, or issue ADDRESS TSO just before doing EXECIO command.

HTH,

Walter Trovijo Jr









Troy Coleman Enviado Por: DB2 Data Base Discussion List 02/05/2001 22:51 Responder a DB2 Data Base Discussion List



Para: DB2-L@RYCI.COM cc: Assunto: Rexx Question - EXECIO error -3 [...] 12784 19 30_DB2 PERFORMANCE TOOL SELECTION11_Marc Nelson22_marc.nelson@BCBSKS.COM30_Thu, 3 May 2001 12:29:30 -0500442_- This is a question related to which DB2 performance tool you would spend your money on. We are in the process of making a decision on purchasing a tool to look at the internal parts of DB2. The choice comes down to two vendors. The first vendor would include BMCs' Apptune and SQL Explorer. The second vendor would include Compuwares' DB2 for Strobe. We currently use many of the BMC tools as well as Strobe without the DB2 interface. [...] 12804 21 57_making changes to DB2 tables thru and ODBC link to Access14_Ruth Gramolini26_rgramolini@TAX.STATE.VT.US30_Thu, 3 May 2001 14:16:19 -0400328_iso-8859-1 One of my users needs to make changes to some DB2 tables through and ODBC link to Access. The database user has the necessary permissions on the when connecting directly to DB2 but cannot make the changes in Access via ODBC.

Are there special permissions on DB2 to do this? Any pointers would be helpful. [...] 12826 21 22_DB2 V7 and Platinum/CA16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Thu, 3 May 2001 14:30:45 -0400419_iso-8859-1 Has anyone had experience (good or bad welcome) with DB2/390 V7.1 and Platinum/CA at a P97G level?



regards,

eric pearson NS ITO Database Support

================================================ 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. 12848 69 37_Testing Stored Procedures in MSAccess9_Colin Fay13_cfay2@CSC.COM30_Thu, 3 May 2001 15:22:06 -0400377_us-ascii Hi,

I am trying to test some stored procedures using MSAccess (since utimately they will be invoked from Access). The SPs work when invoked by the Stored Procedure Builder but when run as a pass through query in MSAccess I get different results, or no results:

'Pass through query with ReturnRecords property set to True did not return any records' [...] 12918 41 26_Re: DB2 V7 and Platinum/CA13_Thomas, Janis19_Janis.Thomas@CA.COM30_Thu, 3 May 2001 15:03:17 -0400471_iso-8859-1 For CA's DB2 products, the P97G tape was released back in May 1999, so it does not run under a DB2 V7 environment. You need to be on P99D for V7 toleration.

Janis Thomas Computer Associates DB2 Product Management 630-505-6247 janis.thomas@ca.com







-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: Thursday, May 03, 2001 1:31 PM To: DB2-L@RYCI.COM Subject: DB2 V7 and Platinum/CA [...] 12960 98 41_Re: Testing Stored Procedures in MSAccess10_Rao, Peavy26_Peavy.Rao@TRANSAMERICA.COM30_Thu, 3 May 2001 14:40:16 -0500664_- Colin,

Problem is DIAG [01004] [IBM][CLI Driver] CLI0002W Data truncated. SQLSTATE=01004 (-99999)

This generally happens when receiving fields (client data items) are not big enough to accommodate the values returned by the result set. Or the return values may not be big enough.

Please check your client data items.

peavy



> ---------- > From: Colin Fay[SMTP:cfay2@CSC.COM] > Reply To: DB2 Data Base Discussion List > Sent: Thursday, May 03, 2001 2:22 PM > To: DB2-L@RYCI.COM > Subject: Testing Stored Procedures in MSAccess > > Hi, > > I am trying to test some stored procedures using MSAccess (since > utimately [...] 13059 35 23_DB2 on NT /3gb Boot.ini14_David Bargeron29_David.Bargeron@BTINTERNET.COM30_Thu, 3 May 2001 20:44:38 +0100288_iso-8859-1 Hi,

A DB2 UDB on NT question which I hope someone can shed some light on :

The /3gb option allows an NT server to utilise more than the 2gb memory limit (if available and if on 4.0 SP3 or above - Enterprise version) - see MS note Q171739 for further details [...] 13095 18 46_Re: Restart Logic with Sequential Output Files14_William Poston21_william.poston@DB.COM30_Thu, 3 May 2001 14:58:14 -0600172_us-ascii How are most shops handeling restart logic with sequential output files? Does a rollback also rollbacks records written to sequential output files?



13114 56 46_Re: Restart Logic with Sequential Output Files15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Thu, 3 May 2001 15:25:49 -0500473_ISO-8859-1 Hi, William, No, it doesn't. That is what two-phase commit is for, to coordinate the updates between DB2 and whatever other data sets or systems the unit of work is accessing. If you are running a batch job and it abended you would have to perform a recovery of the tablespace(s) to whatever point in time that your batch job started. You would have to restore the sequential data set from a back-up that you must take just prior to starting the batch job. [...] 13171 41 46_Re: Restart Logic with Sequential Output Files15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Thu, 3 May 2001 15:30:03 -0500450_iso-8859-1 Addendum....of course, if you have some sort of checkpoint package in your shop then your solution may be a different one. I am not familiar with checkpoint procedures so someone else probably has something to add to this.

Linda

-----Original Message----- From: William Poston [mailto:william.poston@DB.COM] Sent: Thursday, May 03, 2001 3:58 PM To: DB2-L@RYCI.COM Subject: Re: Restart Logic with Sequential Output Files [...] 13213 68 46_Re: Restart Logic with Sequential Output Files0_19_Tim.Lowe@STPAUL.COM30_Thu, 3 May 2001 15:37:10 -0500357_us-ascii William, The most common solutions are IMS GSAM, or a third-party vendor restart product (I will leave it to the vendors to advertise at this point.). I have also seen people use DB2 tables that contain the data for the sequential output file, and restart routines that copy the output file on restart, but I do not recommend either of these. [...] 13282 66 33_Problem running DSNTEP2 from REXX0_22_Rohn.Solecki@MTS.MB.CA30_Thu, 3 May 2001 15:50:49 -0500568_us-ascii DB2 V6, OS390 2.10. I'm in the process of teaching myself REXX (in other words, "... a little knowledge is a dangerous thing...")

I'm setting up a REXX to generate and, hopefully, execute stogroup alter statements. (I need it for disaster recovery to change volume ids). I've managed to generate the SQL statements, but when I pass them to DSNTEP2 within REXX I get errors where it appears that DSNTEP2 has problems with the SGNAME and VOLIDS, it tries to treat them as DB2 keywords, not input variables. This is the error message DSNTEP2 outputs [...] 13349 76 37_Re: Problem running DSNTEP2 from REXX12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV30_Thu, 3 May 2001 16:31:00 -0400605_- Any hex chars behind the StoGROUP name? Try passing it to dsntiad.

>>> DB2-L@RYCI.COM@inter2 05/03/01 03:50PM >>> DB2 V6, OS390 2.10. I'm in the process of teaching myself REXX (in other words, "... a little knowledge is a dangerous thing...")

I'm setting up a REXX to generate and, hopefully, execute stogroup alter statements. (I need it for disaster recovery to change volume ids). I've managed to generate the SQL statements, but when I pass them to DSNTEP2 within REXX I get errors where it appears that DSNTEP2 has problems with the SGNAME and VOLIDS, it tries to treat them as [...] 13426 110 37_Re: Problem running DSNTEP2 from REXX0_19_Tim.Lowe@STPAUL.COM30_Thu, 3 May 2001 16:12:57 -0500463_us-ascii Rohn, Your error messages are IKJ* messages, therefore they are TSO error messages, not DSNTEP2 error messages. Why do you have a RISKW (WRITE) to read in the queued statements? Why do you have a DISKR (READ) to "feed it to DSNTEP2"?

I hope this helps.

Thanks, Tim





Rohn.Solecki@ MTS.MB.CA To: DB2-L@RYCI.COM Sent by: DB2 cc: Data Base Subject: Problem running DSNTEP2 from REXX Discussion List [...] 13537 131 37_Re: Problem running DSNTEP2 from REXX0_22_Rohn.Solecki@MTS.MB.CA30_Thu, 3 May 2001 17:09:27 -0500341_us-ascii Tim: Thanks for pointing that out. So, since the error is in TSO, I guess that means the SQL isn't even reaching DSNTEP2. I'll have to do some more careful looking at the DSNTEP2 call command structure.



I patterned the REXX from an example posted on the list a while back. I interpreted that example as follows: [...] 13669 45 37_Re: Problem running DSNTEP2 from REXX64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM30_Thu, 3 May 2001 18:18:58 -0500327_ISO-8859-1 Have you tried writing your SQL statement(s) to the SYSTSIN for DSNTEP2? (I'm suggesting this from memory - can't find a DSNTEP2 batch job handy to verify ddnames... Queueing them to the stack will confuse TSO, and DSNTEP2 will never find them. You might could even get away with allocating SYSTSIN to a VIO... [...] 13715 33 26_Re: DB2 V7 and Platinum/CA9_Rob Crane22_racrane@CONCENTRIC.NET30_Thu, 3 May 2001 17:33:25 -0600351_us-ascii P99D is the V7 toleration code. Also P99D is the level that is verified for V2R10 mvs. P97G was tolerant of V6 but did not exploit it. I assume you are jumping from V5 to V7 of DB2. Once you make that switch I would get the highest version of the platinum code and go with it (currently P99D, P99E will be out shortly from what I hear). [...] 13749 92 39_Re: Effect of changing commit frequency11_Roland Chua18_rolandchua@SGX.COM30_Fri, 4 May 2001 08:03:35 +0800471_us-ascii Hi Michele, Is your records in your input files sorted into the same sequence as your current tables clustering sequence? This will cut down the elapsed time and CPU time. I hope it helps.











Michele Saffer @RYCI.COM> on 03/05/2001 09:25:55 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 13842 69 35_Re: Declaration of arrays in oracle14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 3 May 2001 18:55:28 -0500321_- If what you are asking about matchs my recollections: 1) You can only have a vector, not a multi-dimensional array: eg: 01 table-fiche. 05 f1 pic x(3) occurs 58. 05 f2 pic x(20) occurs 58.

2) You then specify the name of the arrays in a fetch/insert exec sql fetch into :f1, :f2 end-exec [...] 13912 46 21_Re: db2 v5.1 problem:14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Thu, 3 May 2001 19:08:32 -0500364_- Technically, DSNTEP2 is not a "utility" - but a "sample program". The source is available in SDSNSAMP. There's nothing to stop you from using normal program (albeit PLI) debugging to attack this problem.

Do selects work using SPUFI, QMF etc?

James Campbell

On Wed, 2 May 2001 11:47:44 -0500, Derez D. Lusk wrote: [...] 13959 77 16_Re: alter column14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU30_Thu, 3 May 2001 19:34:44 -0500429_iso-8859-1 Sanjeev, Kumar,

Sorry, I was thinking of the case where a table had been altered to add a column NNWD, which would give you variable length rows. The pre-existing data (before the alter) would not have the date column populated, which could be seen from the VSAM file.

In this case, where I assume the table is created initially with C1 as NNWD, it doesn't work. Apologies to all for the confusion. [...] 14037 102 37_Re: Problem running DSNTEP2 from REXX10_teldb2kals22_teldb2kals@TELSTRA.COM30_Fri, 4 May 2001 10:17:35 +1000428_us-ascii Hi Rohn,

It looks like the command is being treated as a TSO ALTER command. Hence the error msg abt Invalid Keyword.

Try coding a TRACE instruction in the beginning of ur REXX code to find out exactly whats happening.

I dont see ur full REXX code, but I assume u r running DSNTEP2 under DSN environment. (We generally use RUN under DSN to execute DSNTEP2). And the SQL should be in the SYSIN. [...] 14140 110 46_Re: Restart Logic with Sequential Output Files25_Srirengan Venkatesh Kumar42_SRIRENGAN_VENKATESH_KUMAR/IRAS@IRAS.GOV.SG30_Fri, 4 May 2001 10:49:30 +0800553_us-ascii Hi,

DB2 tables can be used to store the no of records written at commit points. This count on the number of records can be used as a restart logic. I believe any software meant for restarting would do the same as well (not store the entire copy of output file in table). Upon restart the count is taken from the db2 table and the file output is repositioned todo rewrite and then write (should have restart logic in application - this is were a good apps design will help). Obviously this means that output files cannot be on tapes [...] 14251 20 6_bigint9_john king24_john_king@REDIFFMAIL.COM30_Fri, 4 May 2001 03:11:51 -0000367_- Hi all , thanks in advance. Can anybody tell me the match between BIGINT in db2v7(udb) with db2 v6 0s 390. Is it decimal in db2 os 390. This is the question asked by my friend here working here.

John

_____________________________________________________ Chat with your friends as soon as they come online. Get Rediff Bol at http://bol.rediff.com [...] 14272 21 22_v6 vs v7 in db2 os 3909_john king24_john_king@REDIFFMAIL.COM30_Fri, 4 May 2001 03:13:56 -0000548_- Hi all, could you update me the diffrence between db2 v6 and db2 v7 in os 390. Which one will better considering all aspects.

Thanks

John

_____________________________________________________ Chat with your friends as soon as they come online. Get Rediff Bol at http://bol.rediff.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. 14294 57 35_DB2 PM online monitor abend in ISPF8_Rush Yan14_db2-l@21CN.COM30_Fri, 4 May 2001 12:18:17 +0800658_us-ascii Hello gentle listers,

because I have post this question to ibm-main and db-l, but got zero answer,I'm very sorry for re-post this question here, if you happen to know the clue,Pls shed some light here:

When I use DB2 PM online monitor function, I got the following message,

1st screen: ISPS118L SERVICE NOT INVOKED. A VALID ISPF ENVIRONMENT DOES NOT EXIST. IEA995I SYMPTOM DUMP OUTPUT SYSTEM COMPLETION CODE=0C2 REASON CODE=00000002 TIME=21.45.34 SEQ=00024 CPU=0000 ASID=0032 PSW AT TIME OF ERROR 078D2000 8140AC2C ILC 4 INTC 02 NO ACTIVE MODULE FOUND NAME=UNKNOWN DATA AT PSW 0140AC26 - F1F0ACFC 02795080 08FC0580 GPR 0-3 [...] 14352 199 37_Re: Problem running DSNTEP2 from REXX0_19_Tim.Lowe@STPAUL.COM30_Thu, 3 May 2001 18:04:06 -0500627_us-ascii Rohn, Yes, I think I saw the example that you are referring to. Does this look familiar?: /* REXX */ "ALLOC FI(SYSPRINT) UNIT(VIO) SPACE(5 5) TRACKS NEW DELETE REUSE" "ALLOC FI(SYSIN ) UNIT(VIO) SPACE(5 5) TRACKS NEW DELETE REUSE" QUEUE "SELECT CREATOR, NAME" QUEUE "FROM SYSIBM.SYSDATABASE " QUEUE "WHERE SUBSTR(CREATOR,1,3) <> 'SYS'" QUEUE "" "EXECIO * DISKW SYSIN (FINIS" ADDRESS TSO "CALL 'SYS2.DB2.SDSNEXIT(DSNTEP2)'" "EXECIO * DISKR SYSPRINT (STEM DEBUGER. FINIS" DO I = 1 TO DEBUGER.0 BY 1 SAY DEBUGER.I END But, when I run that example, I get an SQLCODE - 927 because DSNTEP2 must run underneath the DSN. [...] 14552 95 39_Re: DB2 PM online monitor abend in ISPF23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Fri, 4 May 2001 07:32:48 +0100406_iso-8859-1 Hi

are you 100% sure that you have gone through the installation tasks correctly?

Has it ever worked at your site?

what release of DB2 you on? V5 is the pm release ...

regards

Leslie

-----Original Message----- From: Rush Yan [mailto:db2-l@21CN.COM] Sent: Friday, May 04, 2001 5:18 AM To: DB2-L@RYCI.COM Subject: DB2 PM online monitor abend in ISPF [...] 14648 55 0_11_Arvind Heda40_arvind_heda@INTERSOLUTIONS.STPN.SOFT.NET30_Fri, 4 May 2001 12:10:29 -0400265_iso-8859-1 hi list members,

can anyone suggest me how to call a set of stored procedures in db2 just at the time of dbstart automatically.i.e. whenever db instance comes up every time certain SP are called with the initialization.



thanx arvind 14704 87 39_Re: DB2 PM online monitor abend in ISPF10_teldb2kals22_teldb2kals@TELSTRA.COM30_Fri, 4 May 2001 16:27:54 +1000313_us-ascii Hi Rush,

It says ISPF environment doesnt exist. How have u setup ur program to execute ?

I havent gone thru the manual, but in our environment, it is called from an EXEC in which the ISPF libararies are setup.

R u having an active ISPF session ? (We execute DB2 PM under ISPF). [...] 14792 27 29_Db2 vs Oracle Parallel Server17_Richard Cotterill24_rl_cotterill@BIGPOND.COM30_Fri, 4 May 2001 18:20:31 +1000314_us-ascii Hi, Can anyone tell me if DB2 on AIX has is planning to have a function similar to Oracle Parallel Server.

Also does any have info on the the capabilities of this product (ie Oracle Server) good or bad. Does it give high availability for example?

Thanks for any help, Richard



14820 33 56_Re: DB2-L Digest - 30 Mar 2001 to 31 Mar 2001 (#2001-91)10_Steve Bate21_STEVE.BATE@INNOGY.COM30_Fri, 4 May 2001 11:06:56 +0100516_- Unsubscribe







**************************************************************************** The information contained in this email is intended only for the use of the intended recipient at the email address to which it has been addressed. If the reader of this message is not an intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination or copying of the message or associated attachments is strictly prohibited. [...] 14854 61 28_Re: Running V6 and V7 on AIX11_Hamar, Bela22_Bela.Hamar@VARETIS.COM30_Fri, 4 May 2001 13:05:55 +0200532_us-ascii Hi Pat,

we also were having trouble when we installed DB2 V7 on an AIX machine that had V6 too.

1. our experience is that the two versions are totally separated, that is you can work either with the V6 instances or with the V7 instances. It depends on which db2profile/db2cshrc (for ksh/csh) you run (that in the ~v7-instance/sqllib or that in the ~v6-instance/sqllib directory) to set up the environment variables (PATH, LIBPATH, ...). Only one DAS can run on a machine, since its port number is fixed. [...] 14916 178 46_Re: Restart Logic with Sequential Output Files16_Alexander Vilens16_admin@RELARC.COM30_Fri, 4 May 2001 04:14:36 -0700404_- Output files and lost records during abends.

It is possible to loose the output records within the last block during an ABEND.

COMMIT time:

Your application must ensure to complete/finish all I/Os for an output file at the time of each COMMIT and close all open buffers to prevent data loss. Otherwise, you may loose all the records within the last output block during ABEND. [...] 15095 67 26_Re: v6 vs v7 in db2 os 39012_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM30_Fri, 4 May 2001 07:35:15 -0400529_iso-8859-1 John,

You should have sent this yesterday morning as there was a free LightYear teleconference provided yesterday which covered what you're asking for - by the way sign up to receive notices from Lightyear at http://www.lightyr.com/ that way you can include yourself on any futures they may offer, if you click on their Tech Talk, I believe you can replay the conference recording and the number should be listed there and I believe you can print off the foils presented too under the downloads button ... [...] 15163 41 33_Re: Db2 vs Oracle Parallel Server11_Steve Mazer17_smazer@FMTUSA.COM30_Fri, 4 May 2001 08:24:53 -0400551_us-ascii 06:20 PM 5/4/01 +1000, you wrote: >Hi, > Can anyone tell me if DB2 on AIX has is planning to have a function >similar to Oracle Parallel Server. > >Also does any have info on the the capabilities of this product (ie >Oracle Server) good or bad. Does it give high availability for example? > >Thanks for any help, >Richard > > >-- Richard, What 'function' are you speaking of? If its parallelism on AIX, then you must take a look at DB2 UDB EEE. Unlike OPS, it is a shared nothing architecture. Yes it can support High Availability also. [...] 15205 131 37_Re: Problem running DSNTEP2 from REXX11_Hayden, Lee18_Lee_Hayden@CSX.COM30_Fri, 4 May 2001 08:41:10 -0400565_iso-8859-1 Here is an example using DSNTIAUL, the syntax would be the same for DSNTEP2. This is run in batch so the steplib has the executables.

/* REXX */



ARG SUBSYS DBASE TSPACE MODDAYS MAXCARDS

/* CREATE SELECTIVE UNLOAD FOR */ /* TABLE SPACES TO MODIFY */ /* MAXCARDS DEFAULTS TO 500 */

DBASE = "'"||DBASE||"'" TSPACE = "'"||TSPACE||"'" BTYPE = "'"||'B'||"'" OUTPUT.1 = ' SELECT DBNAME, TSNAME, COUNT(*) ' OUTPUT.2 = ' FROM SYSIBM.SYSCOPY ' OUTPUT.3 = ' WHERE DBNAME LIKE '||DBASE OUTPUT.4 = ' AND TSNAME LIKE '||TSPACE [...] 15337 103 37_Re: Problem running DSNTEP2 from REXX15_Robert lawrence21_rlawrence@BOSCOVS.COM30_Fri, 4 May 2001 08:51:56 -0400433_iso-8859-1 Rohn, A couple problems I believe 1. Your first QUEUE "" puts a null line on the Queue when you use EXECIO * to sysin it created an empty dataset since it stops when the QUEUE contains a null line, thus the rest of your statements are executed under TSO which pull from the QUEUE 2. After that I do not see a PLAN in your execute 3. I would use CONCATENTATION(||) on your multi line QUEUE statements to abut the data [...] 15441 117 39_Re: Effect of changing commit frequency14_Michele Saffer32_Michele_Saffer@CONSULTEC-INC.COM30_Fri, 4 May 2001 08:58:47 -0400555_us-ascii The input file is sorted in the clustering sequence of the history tables. The current tables are clustered differently. At this point, I think my problems are first of all, the number of NPIs on my history header table, and also, the clustering sequence of my current tables. The only option I have to change this setup is to cluster the current header table in the same sequence as the history tables. (The column used to cluster the history tables does not exist in the rest of the current tables.) Do you think this would be worthwhile? [...] 15559 32 30_How long does DB2 keep a disk?0_19_mike.holmans@BT.COM30_Fri, 4 May 2001 14:02:07 +0100331_- DB2 for OS/390 V5, OS/390 2.8



We're shovelling things around, converting to use of SMS. We've reorged all the datasets off volume NH6T41, but when the Storage Admin guy tries to vary the disk offline so as to reinitialise it and add it to the SMS pool, he can't because DB2 is still keeping hold of the disk. [...] 15592 55 34_Re: How long does DB2 keep a disk?14_Toppins, Smike21_smike.toppins@GWL.COM30_Fri, 4 May 2001 07:20:36 -0600621_- Yes, DB2 will have to be cycled unless you know all the specific SPACENAM's and can do a -STOP/-START on them.



SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094



> ---------- > From: mike.holmans@BT.COM[SMTP:mike.holmans@BT.COM] > Reply To: DB2 Data Base Discussion List > Sent: Friday, May 04, 2001 7:02 AM > To: DB2-L@RYCI.COM > Subject: How long does DB2 keep a disk? > > DB2 for OS/390 V5, OS/390 2.8 > > > We're shovelling things around, converting to use of SMS. We've > reorged all the datasets off volume NH6T41, but when the Storage Admin guy > tries to vary [...] 15648 35 34_Re: How long does DB2 keep a disk?10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 4 May 2001 15:28:47 +0200427_us-ascii It's a little bit generic as a question..may be a long running query is accessing that disk, or HSM or...???

No, you haven't to cycle DB2 if in that disk there aren't DB2 'system' file as logs, BSDSs, etc. You need to stop some

database/tablespace if they reside on the disk.

I'm currently working on the same problem (+ file placement+ BP tuning) and (until now) I never stopped DB2. Try to [...] 15684 66 34_Re: How long does DB2 keep a disk?15_Robert lawrence21_rlawrence@BOSCOVS.COM30_Fri, 4 May 2001 09:30:02 -0400334_iso-8859-1 Mike, Is volume NH6T41 still in a DB2 STOGROUP or is a candidate volume for any datasets? If it is a candidate volume then do an alter dsname removevol(NH6T41) for those datasets. If it is still in a STOGROUP remove it from the stogroup. If you had to alter any datasets you may have to stop and start those datasets? [...] 15751 166 34_Re: How long does DB2 keep a disk?16_Herman, Philippe22_PHILIPPE.HERMAN@CA.COM30_Fri, 4 May 2001 14:31:38 +0100425_iso-8859-1 A Stop of all the tablespaces and indexes on that volume will flush and externilize all the pages still in the Bufferpools. Keep in mind that DB2 will allways try to keep a certain ammount of open datasets depending on the ZPARM DSMAX parm

Philippe Herman Computer Associates Technical Consultant Tel: +32 (2) 773 28 43 Fax: +32 (2) 762 73 59 Mobile: +32 (496) 59 72 79 [...] 15918 65 34_Re: How long does DB2 keep a disk?12_Travis, John27_John.Travis@CAPGEMINI.CO.UK30_Fri, 4 May 2001 14:34:49 +0100337_- Mike, Check that no other storage group is holding an intent lock on the disk. We had a similar experience that turned was resolved by looking at the volumes assigned to stogroups, when the reorg takes place db2 takes an intent on all the volumes in the stogroup even though it may not place any datasets on the volume concerned. [...] 15984 45 10_Re: bigint0_15_leon@CA.IBM.COM30_Fri, 4 May 2001 10:01:46 -0400619_us-ascii Decimal 19

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



john king @RYCI.COM> on 05/03/2001 11:11:51 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List



To: DB2-L@RYCI.COM cc: Subject: bigint



Hi all , thanks in advance. Can anybody tell me the match between BIGINT in db2v7(udb) with db2 v6 0s 390. Is it decimal in db2 os 390. This is the question asked by my friend here working here. [...] 16030 41 0_0_15_leon@CA.IBM.COM30_Fri, 4 May 2001 10:03:26 -0400454_us-ascii This is not something that would be easy or even possible to do since stored procedures execute within a context of a connection to a particular database.

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



Arvind Heda @RYCI.COM> on 05/04/2001 12:10:29 PM

Please respond to DB2 Data Base Discussion List [...] 16072 47 61_Re: making changes to DB2 tables thru and ODBC link to Access0_15_leon@CA.IBM.COM30_Fri, 4 May 2001 10:06:33 -0400472_us-ascii Ruth, what would be helpfull if you described the sequence of steps that your user took and what error messages she got.

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



Ruth Gramolini @RYCI.COM> on 05/03/2001 02:16:19 PM

Please respond to DB2 Data Base Discussion List

Sent by: DB2 Data Base Discussion List [...] 16120 16 17_DB2 Catalog Chart15_Daniel Sullivan28_daniel.sullivan@ZURICHNA.COM30_Fri, 4 May 2001 09:09:50 -0500457_us-ascii Does anyone know the Site address where I can get a copy of the DB2 Catalog. Platinum use to put it out, I think CA has continued the practice. I went to their web site but could not find it.

Thanks.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 16137 80 15_DB2 V5 Recovery16_Mike Friedlander30_friedlander@FIRSTINVESTORS.COM30_Fri, 4 May 2001 10:20:01 -0400545_iso-8859-1 Hi group. I am having a "problem" with recovery and I could use some information. I have a tablespace in version 5 that I take a full image copy of with no problem. I then insert new rows into a table in that tablespace that did not exist when the image copy was taken. I then perform a recovery using the image copy just taken. I recover the indexes and when I look at the resultant table after the successful recovery I still see the newly inserted rows. Is my assumption incorrect in believing that the table should have been [...] 16218 30 18_Re: DSN1COPY limit16_Lemaire, Nigel B21_nigel.lemaire@EDS.COM30_Fri, 4 May 2001 09:14:54 -0500436_iso-8859-1 I had this problem years ago, when DSN1COPY was limited to around 100 entries. IBM increased it, apparently to 500. Looking at the Util Ref for V6, P.453 that is the limit. Unless you can get IBM to give you a "special" fix, you will have to find an alternative way of moving the tables. You might consider a third party product that can Unload from an Image copy if you just want a "prior image" of a particular table. [...] 16249 46 0_11_Bikash Paul19_bikash_db@YAHOO.COM30_Fri, 4 May 2001 07:38:38 -0700350_us-ascii Hi, I am aware of anything of that sort. But i have done the similar thing on OS/390 and HP-UX writing batch jobs and shell script. on OS/390, I modified the DB2 startup job to include a step which will start another job. The second job will wait till DB2 comes up and then run another program which will excute set of programs and SP. [...] 16296 40 19_Re: DB2 V5 Recovery11_Bikash Paul19_bikash_db@YAHOO.COM30_Fri, 4 May 2001 07:45:17 -0700556_us-ascii Hi, I dont think that you are using TOCOPY option for recovery.

Regards, BIkash --- Mike Friedlander wrote: > Hi group. I am having a "problem" with recovery and I could use some information. I > have a tablespace in version 5 that I take a full image copy of with no problem. I > then insert new rows into a table in that tablespace that did not exist when the image > copy was taken. I then perform a recovery using the image copy just taken. I recover > the indexes and when I look at the resultant [...] 16337 117 19_Re: DB2 V5 Recovery15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Fri, 4 May 2001 09:50:05 -0500296_ISO-8859-1 Hi, Mike, In order to recover to a point prior to the addition of new rows you will have to either recover to the image copy or to a point-in-time prior to the insertion of the new rows. DB2 will automatically recover to the current point in time if you do not specify otherwise. [...] 16455 48 19_Re: DB2 V5 Recovery22_Claudio Meluzzi Mendes18_p000002@BEG.COM.BR30_Fri, 4 May 2001 11:48:09 -0300478_iso-8859-1 I see two possible "side effects" on your recovery:

1) you are issuing a RECOVER TABLESPACE without the TOCOPY parameter. This way the recover utility will recover the tablespace to its most recent status, i.e., with newly inserted rows; or

2) your image copy has rows identical to the new ones inserted. In this case you can use DSN1COPY utility to copy data from the imagecopy to another tablespace and see if your imagecopy contains those rows. [...] 16504 45 19_Re: DB2 V5 Recovery14_Toppins, Smike21_smike.toppins@GWL.COM30_Fri, 4 May 2001 08:48:28 -0600609_- Are you doing a RECOVER ... TOCOPY or just a RECOVER ...? I would expect the results you're seeing if you are doing the latter and not the former.



SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094



> ---------- > From: Mike Friedlander[SMTP:friedlander@FIRSTINVESTORS.COM] > Reply To: DB2 Data Base Discussion List > Sent: Friday, May 04, 2001 8:20 AM > To: DB2-L@ryci.com > Subject: DB2 V5 Recovery > > Hi group. I am having a "problem" with recovery and I could use some > information. I have a tablespace in version 5 that I take a full image > copy of [...] 16550 27 21_Re: DB2 Catalog Chart0_22_Rohn.Solecki@MTS.MB.CA30_Fri, 4 May 2001 10:02:53 -0500449_us-ascii Last time I used (haven't checked it recently) http://www.cai.com/products/posters.htm









Daniel Sullivan @RYCI.COM> on 2001/05/04 09:09:50 AM Subject: DB2 Catalog Chart

Does anyone know the Site address where I can get a copy of the DB2 Catalog. Platinum use to put it out, I think CA has continued the practice. I went to their web site but could not find it. [...] 16578 46 15_Re: DB2 Version11_Carl Newton30_newtonc@INNOVATION-GROUP.CO.UK30_Fri, 4 May 2001 15:53:28 +0100363_- Is V7 the only version IBM will sell you or can you still order V6 if you prefer the stability?

-----Original Message----- From: Hanne Lyssand [mailto:han@VPS.NO] Sent: 02 May 2001 14:39 To: DB2-L@RYCI.COM Subject: Re: DB2 Version



It's GA. We got it from IBM last week. We haven't installed it jet but will start as soon as possible. [...] 16625 115 27_FW: [DB2-L] DB2 V5 Recovery13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Fri, 4 May 2001 09:07:27 -0600315_iso-8859-1 A plain vanilla RECOVER is to the current point in time. It will apply the logs with the inserted rows. Its main use is for a dasd problem.

What you want is a RECOVER TO COPY where you specify the dataset name as registered in the syscopy table. This will mean the loss of the inserted data. [...] 16741 92 21_Re: DB2 Catalog Chart13_O'Neill, Mike19_Mike.O'Neill@53.COM30_Fri, 4 May 2001 11:08:15 -0400439_iso-8859-1 I have a big poster sized one from BMC.

-----Original Message----- From: Daniel Sullivan [mailto:daniel.sullivan@ZURICHNA.COM] Sent: Friday, May 04, 2001 10:10 AM To: DB2-L@RYCI.COM Subject: DB2 Catalog Chart



Does anyone know the Site address where I can get a copy of the DB2 Catalog. Platinum use to put it out, I think CA has continued the practice. I went to their web site but could not find it. [...] 16834 28 24_DB2 Catalog Chart -Reply11_Jeff Schade21_jschade@MAIL2.PCH.COM30_Fri, 4 May 2001 11:10:23 -0400330_US-ASCII Go to WWW.CAI.COM and eneter a search argument of DB2 POSTERS.

>>> daniel.sullivan@ZURICHNA.COM 05/04/01 10:09am >>> Does anyone know the Site address where I can get a copy of the DB2 Catalog. Platinum use to put it out, I think CA has continued the practice. I went to their web site but could not find it. [...] 16863 19 19_DB2/OS390 v DB2 UDB9_Anil Kale18_Usregion10@AOL.COM28_Fri, 4 May 2001 11:19:26 EDT587_ISO-8859-1 Anne,

In addition to all the differences pointed out by others so far, I would like to mention something that I encountered in the UDB land. Most applications for DB2 on OS/390 have evolved over a period of time. And in course of time, many standards and procedures have been developed and followed. However, in the UDB land, I haven't heard of too many projects where they have come up with standards, procedures, and guidelines. One might argue that you could map your existing standards to be used for UDB as well. But in reality, for many reasons (which could [...] 16883 70 19_Re: DB2 V5 Recovery9_Don Alden25_Don.Alden@I-STRUCTURE.COM30_Fri, 4 May 2001 09:25:56 -0600467_iso-8859-1 1. Assuming that you inserted the new rows with => DB2 Logging in effect.

2. You must have performed a Forward Recovery which would apply the Image Copy plus the Log that had the Inserted rows. Viola! The Inserted rows appear in the answer set.

Cheers, Don Alden

-----Original Message----- From: Claudio Meluzzi Mendes [mailto:p000002@BEG.COM.BR] Sent: Friday, May 04, 2001 7:48 AM To: DB2-L@RYCI.COM Subject: Re: DB2 V5 Recovery [...] 16954 66 15_Re: DB2 Version16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Fri, 4 May 2001 11:34:38 -0400533_iso-8859-1 You can still order V6.

regards,

eric pearson NS ITO Database Support



-----Original Message----- From: Carl Newton [mailto:newtonc@INNOVATION-GROUP.CO.UK] Sent: Friday, May 04, 2001 10:53 AM To: DB2-L@RYCI.COM Subject: Re: DB2 Version



Is V7 the only version IBM will sell you or can you still order V6 if you prefer the stability?

-----Original Message----- From: Hanne Lyssand [mailto:han@VPS.NO] Sent: 02 May 2001 14:39 To: DB2-L@RYCI.COM Subject: Re: DB2 Version [...] 17021 141 64_Re: Automation of Stored Procedure Calls at db2start (was blank)14_Wayne Driscoll24_Wayne.Driscoll@QUEST.COM30_Fri, 4 May 2001 08:39:07 -0700551_iso-8859-1 Leon, While this wouldn't be possible at instance startup, is this possible at database activation? Wayne Driscoll Product Developer Quest Software Inc. wdriscoll@quest.com Note: All opinions are strictly my own.

-----Original Message----- From: leon@CA.IBM.COM [mailto:leon@CA.IBM.COM] Sent: Friday, May 04, 2001 9:03 AM To: DB2-L@RYCI.COM Subject: [DB2-L]



This is not something that would be easy or even possible to do since stored procedures execute within a context of a connection to a particular database. [...] 17163 36 21_Re: DB2 Catalog Chart13_Thomas, Janis19_Janis.Thomas@CA.COM30_Fri, 4 May 2001 11:11:29 -0400501_iso-8859-1 DB2 posters are available here: http://www.ca.com/products/posters.htm. We will be giving out DB2 V7 posters at IDUG, and they will be available for ordering on this web page within the next month.



Janis Thomas Computer Associates DB2 Product Management 630-505-6247 janis.thomas@ca.com



-----Original Message----- From: Daniel Sullivan [mailto:daniel.sullivan@ZURICHNA.COM] Sent: Friday, May 04, 2001 9:10 AM To: DB2-L@RYCI.COM Subject: DB2 Catalog Chart [...] 17200 136 21_Re: DB2 Catalog Chart14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Fri, 4 May 2001 10:40:17 -0500605_iso-8859-1 RE: DB2 Catalog ChartThe new BMC/YL&A DB2 V7 for OS/390 poster will be at IDUG, and will also be on the YL&A site for viewing shortly. Regards, Richard

Richard Yevich - Principal Yevich Lawson & Assoc. Inc. (YL&A) Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of O'Neill, Mike Sent: Friday, May 04, 2001 10:08 AM To: DB2-L@RYCI.COM Subject: Re: DB2 Catalog Chart [...] 17337 97 23_DB2 V5 Recovery Success16_Mike Friedlander30_friedlander@FIRSTINVESTORS.COM30_Fri, 4 May 2001 11:55:26 -0400559_iso-8859-1 My thanks to all who replied. The recovery was not being run with the TOCOPY option. By including it the recovery did in fact restore the tablespace to its state prior to the inserts. Many thanks,



Hi group. I am having a "problem" with recovery and I could use some information. I have a tablespace in version 5 that I take a full image copy of with no problem. I then insert new rows into a table in that tablespace that did not exist when the image copy was taken. I then perform a recovery using the image [...] 17435 67 21_Re: db2 v5.1 problem:11_Tina Hilton21_Tina.Hilton@BMSUS.COM30_Fri, 4 May 2001 11:39:19 -0500458_iso-8859-1 I also have a query that produces a S0C7 (DB2 V6). At first, it even took a S0C7 system dump. Now, just the program abends (spufi, dsntep2, dsntiaul). Is testdb.acct by chance a view with a max function already on the column? There's an apar for that -- pq48054.

Tina

-----Original Message----- From: James Campbell [mailto:jacampbell@ACSLINK.NET.AU] Sent: May 03, 2001 7:09 PM To: DB2-L@RYCI.COM Subject: Re: db2 v5.1 problem: [...] 17503 114 64_Re: Automation of Stored Procedure Calls at db2start (was blank)0_15_leon@CA.IBM.COM30_Fri, 4 May 2001 12:40:03 -0400520_us-ascii The answer is that in principal it can be done. In practice I have no clue how you would get this done. We don't have any user exits that would fire off at the completion of database start. You could write a script that would do DB2START, wait a while, then connect to a database and execute your SP. You will need to deal with coding up a poling loop that waits and then checks for status and then waits again if required. You will also need to manage id and password that would be used to connect to the [...] 17618 138 46_Re: Restart Logic with Sequential Output Files14_William Poston21_william.poston@DB.COM30_Fri, 4 May 2001 12:10:07 -0600477_us-ascii Does any one have any documented procedures of restart logic at their shop they could send me? When doing the rewrite in the restart code, shouldn't the sequential output file be opened i-o then closed and opened extend?



---------------------------------------- Message History ----------------------------------------



From: Srirengan Venkatesh Kumar @RYCI.COM> on 05/04/2001 10:49 AM ZE8 [...] 17757 25 16_backups and logs17_John Breidenstine31_JBreidenstine@LEGIS.STATE.PA.US30_Fri, 4 May 2001 14:26:21 -0400345_iso-8859-1 Hello,

I have a question about backups and logs. DB2/UDB V6.1 on AIX.

I have been told that after an online backup is complete, DB2 forces the currently active log to be closed and as a result it will be archived off. This ensures that your online backup has a complete set of archived logs available for recovery. [...] 17783 38 30_Re: UDB Script Center question13_Helen Johnson25_helen_johnson@RAC.RAY.COM30_Fri, 4 May 2001 13:37:57 -0500539_us-ascii Have you used the Client Configuration Assistant to add the database on the UNIX box?

Steve.Westfall@EQUIFAX.COM wrote:

> I have the UDB 7.1 Admin client software installed on my Windows 2000 > workstation, and I am able to connect with it to DB2 UDB EEE 7.1 on a UNIX > box. However, in both the Command Center and in the Script Center, the > System Name is not displayed in the drop down list. In Command Center, I > can write a script and run it successfully, but when I try to save it I > can't fill in the [...] 17822 54 30_Re: UDB Script Center question18_Krawetzky, Peter J21_KrawetzkyPJ@AETNA.COM30_Fri, 4 May 2001 14:48:32 -0400645_iso-8859-1 Is DAS running on the Unix box?

-----Original Message----- From: Helen Johnson [mailto:helen_johnson@RAC.RAY.COM] Sent: Friday, May 04, 2001 2:38 PM To: DB2-L@RYCI.COM Subject: Re: UDB Script Center question



Have you used the Client Configuration Assistant to add the database on the UNIX box?

Steve.Westfall@EQUIFAX.COM wrote:

> I have the UDB 7.1 Admin client software installed on my Windows 2000 > workstation, and I am able to connect with it to DB2 UDB EEE 7.1 on a UNIX > box. However, in both the Command Center and in the Script Center, the > System Name is not displayed in the drop [...] 17877 49 20_Re: backups and logs20_Pav Kumar-Chatterjee17_pavkc@HOTMAIL.COM30_Fri, 4 May 2001 20:06:09 -0000629_- Hi John

What you are decsribing is true for V7 not V6. In V6 the log is not truncated.

HTH

Pav



>From: John Breidenstine >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: [DB2-L] backups and logs >Date: Fri, 4 May 2001 14:26:21 -0400 > >Hello, > >I have a question about backups and logs. DB2/UDB V6.1 on AIX. > >I have been told that after an online backup is complete, DB2 forces the >currently active log to be closed and as a result it will be archived off. >This ensures that your online backup has a complete [...] 17927 94 20_Re: backups and logs11_Judy Kibler21_judith.kibler@TRW.COM30_Fri, 4 May 2001 15:31:11 -0500538_us-ascii Hello everyone,

I am glad that John asked these questions, as I am currently dealing with some of the same issues. We are also V6 on AIX. I have just implemented the userexit to archive off the log files to disk, and that appears to be working fine. I am now trying to figure out how to utilize TSM to copy the archived logs off to tape, without wasting a 100GB tape for each archive log file. Are there other folks out there using TSM to backup the database and/or to help with archive log management? If so, I sure [...] 18022 197 30_Re: UDB Script Center question11_Scott Olson21_Scott.Olson@QUEST.COM30_Fri, 4 May 2001 13:32:27 -0700536_iso-8859-1 TEST

-----Original Message----- From: Krawetzky, Peter J [mailto:KrawetzkyPJ@AETNA.COM] Sent: Friday, May 04, 2001 1:49 PM To: DB2-L@RYCI.COM Subject: Re: UDB Script Center question



Is DAS running on the Unix box?

-----Original Message----- From: Helen Johnson [mailto:helen_johnson@RAC.RAY.COM] Sent: Friday, May 04, 2001 2:38 PM To: DB2-L@RYCI.COM Subject: Re: UDB Script Center question



Have you used the Client Configuration Assistant to add the database on the UNIX box? [...] 18220 36 34_Re: How long does DB2 keep a disk?17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Fri, 4 May 2001 18:46:49 -0300126_iso-8859-1 You don´t need to cycle, just stop all tablespaces residing on referred volume.

HTH

Walter Trovijo Jr 18257 51 19_Re: DB2 V5 Recovery17_Walter Trovijo Jr22_wtrovijo@COELBA.COM.BR30_Fri, 4 May 2001 18:55:51 -0300331_iso-8859-1 Mike,

What´s the syntax of your RECOVER command? To do what you want you should use RECOVER TOCOPY and specify which COPY you want to be used (dataset name from SYSCOPY), or QUIESCE tablespace before update and recover to QUIESCE specifying QUIESCE point from SYSCOPY.



HTH,

Walter Trovijo Jr 18309 44 30_Re: UDB Script Center question0_26_Steve.Westfall@EQUIFAX.COM30_Fri, 4 May 2001 17:43:56 -0500464_us-ascii Looks like we are having some problems with DAS. I'll check that out further. Thanks.

Steve







"Krawetzky, Peter J" To: DB2-L@RYCI.COM Subject: Re: UDB Script Center question Sent by: DB2 Data Base Discussion List



05/04/2001 01:48 PM Please respond to DB2 Data Base Discussion List









Is DAS running on the Unix box? [...] 18354 23 11_db2 monitor13_Murali Kanaga19_murali@EPIPHANY.COM30_Fri, 4 May 2001 15:38:29 -0700542_iso-8859-1 Db2 Guru's

I am new to DB2. We are running / developing on DB2 (Aix, Solaris) environment.

Is there any way to look at the DB performances,table space, process, memory monitoring tools available?

Any pointers, help would be very much appreciated.

Thanks, Murali

================================================ 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. 18378 62 46_Re: Restart Logic with Sequential Output Files11_Mark Harmon17_mhinnyc@YAHOO.COM30_Fri, 4 May 2001 17:57:47 -0500329_- William,

Start at the very beginning...batch jobs can be either re-run from the top or restarted mid-stream from the last checkpoint. The first reply to this question, discussed re-running from the top. I doubt that this would be satisfactory to most people most of the time. It wastes a ton of time and resources. [...] 18441 31 39_Re: Effect of changing commit frequency11_Mark Harmon17_mhinnyc@YAHOO.COM30_Fri, 4 May 2001 18:08:36 -0500617_- Michele,

Let me offer some suggestions. Each has its down side and depending on circumstances, may not be a viable choice.

1. To speed up the single run version of the application in production you can lock the tables exclusively and increase the commit frequency.

2.More interesting and more drastic is to improve the cpu used by your DELETES by asking them to do less. Delete indexes not used by the standalone SELECT on the current table before this moveover application runs. After the app runs, rebuild the indexes. Considering the size of output files, they don't lend themselves to [...] 18473 26 26_Re: v6 vs v7 in db2 os 39012_Roger Miller19_millerrl@US.IBM.COM30_Fri, 4 May 2001 20:06:58 -0500353_- If the book is better than the webinar, then there are several good ones.

What's New? Version 7 about 60 pages which tries to describe the differences between Version 6 and Version 7, plus some of the tools.

Release Guide has lots more detail.

Version 7 Presentation Guide, SG24-6121, is about 600 pages by red book authors. [...] 18500 51 9_SQL Books6_Babu d16_sdevin2@HOME.COM30_Fri, 4 May 2001 18:40:38 -0700216_iso-8859-1 Hello all,

Can you suggest some good books on SQL. I didn't find anything specific to DB2. Are there any books available with case studies or exercises.

Thanks in advance.

Babu

18552 84 13_Re: SQL Books13_Willie Favero21_wfavero@ATTGLOBAL.NET30_Fri, 4 May 2001 21:09:25 -0500393_us-ascii You did not mention a platform so... here is a starting point....

The Complete Guide to DB2 Universal Database by Don Chamberlin. Excellent book and Don was there at the very beginnings of SQL.

You can also go to Amazon.com and just search on DB2. A ton of books will pop.

BTW... any book by Jim Melton, although not specific to DB2, is an great SQL read... [...] 18637 42 38_Re: Coalesce replacing NULL processing11_Mike Turner29_Michael_Turner@COMPUSERVE.COM30_Fri, 4 May 2001 22:46:50 -0400448_ISO-8859-1 Sanjeev

To make DB2 OS/390 compatible with the whole world. One is probably from DB2 UWO, one from Oracle, and so on. The idea is to allow you to port SQL to DB2 OS/390 from all these database servers without recoding it.

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

Message text written by DB2 Data Base Discussion List >Lots of similar(not same) things:- But WHY ?? [...] 18680 21 13_dumb question8_Rush Yan14_db2-l@21CN.COM30_Sat, 5 May 2001 10:56:05 +0800426_us-ascii Hello,

Several dumb question:

1)how to find out all previliges the user USER1 has? 2)how to find out the installation SYSADM ID?

Thank you

Rush Yan

================================================ 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. 18702 140 38_Re: Coalesce replacing NULL processing16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Sat, 5 May 2001 09:51:09 +0100406_us-ascii I remember from a class ages ago, that they ARE very similar in requirement (difference in the number of arguments or such like) but in any case VALUE or COALESCE were preferable for performance reasons.

The instructor clearly said that IFNULL should be avoided in cases of large amounts of data (unfortunately I have my notes on the course at home and cannot look up the reason why). [...] 18843 64 33_Re: Db2 vs Oracle Parallel Server12_Kent Collins15_kcollins@WF.NET30_Sat, 5 May 2001 10:00:49 -0500544_iso-8859-1 Oracle OPS requires Raw containers. It does not support file containers. Some DBA's avoid OPS for that reason. However, OPS is more like OS/390 DB2 Datasharing and IBM would do good to provide a similar capability for UDB.

Robert M. Collins Jr ( Kent ) IBM Certified DB2 UDB DBA Phone: 940.241.2242 Email: kcollins@wf.net ----- Original Message ----- From: "Steve Mazer" Newsgroups: bit.listserv.db2-l To: Sent: Friday, May 04, 2001 7:24 AM Subject: Re: Db2 vs Oracle Parallel Server [...] 18908 62 17_Re: dumb question11_Bikash Paul19_bikash_db@YAHOO.COM30_Sat, 5 May 2001 09:28:11 -0700335_us-ascii Hi, 1) You have to query multiple calalog tables to find out all the previledges held by an user. Sometimes, it becomes bit complex as authorisation might be granted to the user group not to the user directly.

on OS/390, You can do TSO LU The output shows the the RACF group, the userid is connect to to. [...] 18971 91 13_Re: SQL Books23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Sat, 5 May 2001 22:12:09 +0100398_iso-8859-1 do you want os390 or udb (aix/solaris etc etc)?

Les

-----Original Message----- From: Babu d [mailto:sdevin2@HOME.COM] Sent: Saturday, May 05, 2001 2:41 AM To: DB2-L@RYCI.COM Subject: SQL Books



Hello all,

Can you suggest some good books on SQL. I didn't find anything specific to DB2. Are there any books available with case studies or exercises. [...] 19063 155 13_Re: SQL Books6_Babu d16_sdevin2@HOME.COM30_Sat, 5 May 2001 20:51:35 -0700484_iso-8859-1 I am looking for OS/390

Babu



"Willie Favero" wrote in message news: <3AF360D5.EF47096D@attglobal.net>...

> You did not mention a platform so... here is a starting point....

>

> The Complete Guide to DB2 Universal Database by Don Chamberlin.

> Excellent book and Don was there at the very beginnings of SQL.

>

> You can also go to Amazon.com and just search on DB2. A ton of books [...] 19219 207 13_Re: SQL Books23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM30_Sun, 6 May 2001 09:49:25 +0100433_iso-8859-1 if it is 390 then why not go for a more complete book like Craig Mullins, or Gabrielle Wiorkoski's? both are excellent ..

www.gabrielledb2.com i think Babrielle's is the better one for what you want.

Leslie





-----Original Message----- From: Babu d [mailto:sdevin2@HOME.COM] Sent: Sunday, May 06, 2001 4:52 AM To: DB2-L@RYCI.COM Subject: Re: SQL Books [...] 19427 83 13_Re: SQL Books16_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM30_Sun, 6 May 2001 10:01:00 -0400475_iso-8859-1 Joe Celko has two good ones: SQL for Smarties SQL Puzzles and Answers



regards,

eric pearson NS ITO Database Support

-----Original Message----- From: Babu d [mailto:sdevin2@HOME.COM] Sent: Friday, May 04, 2001 9:41 PM To: DB2-L@RYCI.COM Subject: SQL Books



Hello all,

Can you suggest some good books on SQL. I didn't find anything specific to DB2. Are there any books available with case studies or exercises. [...] 19511 65 84_Re: 2-tier access to DB2 for update without giving users direct update authority ...0_15_leon@CA.IBM.COM30_Sun, 6 May 2001 12:01:48 -0400397_us-ascii Borland Delphi uses ADO, or ODBC API (depending on the version) to talk to DB2. Both of these APIs are dynamic SQL and do not produce DBRMs. There are 2 choices that you can use to achieve what you want: 1. Use Static Profiling feature of DB2 Connect to turn your dynamic SQL in to static SQL calls. 2 Develop your update logic as DB2 for OS/390 Stored Procedures using Embedded SQL [...] 19577 32 14_Visual Explain13_Jaydeep Ghosh29_Jaydeep_Ghosh@PROGRESSIVE.COM30_Sun, 6 May 2001 14:20:03 -0400538_us-ascii Hello Carlo ,

You can do the following steps to configure your Visual Explain to connect to a specific DB2 subsystem :

1. Select Subsystem from the menu bar . 2. Select 'ADD' . 3. On the screen that appears , enter the following Subsystem Nickname : The name of your DB2 subsystem ( for eg. DB2D , DB2P , etc. ) Database Alias : Same as above Logon Authorization Id : Your userid Default Explain Table Qualifier : The qualifier ( creator ) for the explain tables . This could be your userid , or the id of the [...] 19610 15 15_Re: DB2 Version12_Roger Miller19_millerrl@US.IBM.COM30_Sun, 6 May 2001 14:01:55 -0500547_- Currently, you can order V5, V6 and/or V7. We keep the status of what can be ordered (not withdrawn from marketing) and what is in service (V4 until December 31 of this year, V5, V6 and V7) on the web. http://www.ibm.com/software/data/db2/os390/availsum.html

Roger Miller, DB2 for z/OS and OS/390

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 19626 35 13_Re: SQL Books12_Roger Miller19_millerrl@US.IBM.COM30_Sun, 6 May 2001 14:21:36 -0500310_- Here are a few. I pulled the www.bn.com list for DB2 in best selling order and pasted in the top 8 of 182 titles. With a specific platform and version, then the list is shorter, and for OS/390, I'd choose Craig Mullin's book if you want Version 6 or the Introduction to DB2 for OS/390 Version 7 for V7. [...] 19662 87 13_Re: SQL Books14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Sun, 6 May 2001 15:07:26 -0500509_iso-8859-1 Add another one soon, June 1st I think is the date IBM just got from Prentice.

The DB2 Universal Database V7.1 Certification Guide for OS/390 with CDROM, Richard Yevich and Susan Lawson

Hey, how are you doing these days? Probably just as nuts as usual out there. I get my won little S/390 (t3) and the ThinkPad S/390 (t3) in couple weeks. Can't wait - my own system to crash and burn as much as I want. Of course, there is the little DB2 Family utility I promised to develop.... [...] 19750 59 13_Re: SQL Books14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Sun, 6 May 2001 15:11:54 -0500552_iso-8859-1 Opps!!!!!!

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Roger Miller > Sent: Sunday, May 06, 2001 2:22 PM > To: DB2-L@RYCI.COM > Subject: Re: SQL Books > > > Here are a few. I pulled the www.bn.com list for DB2 in best > selling order > and pasted in the top 8 of 182 titles. With a specific platform and > version, then the list is shorter, and for OS/390, I'd choose > Craig Mullin's > book if you want Version 6 or the Introduction to DB2 for OS/390 Version 7 > for [...] 19810 90 11_db2 monitor16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Sun, 6 May 2001 22:15:16 +0100329_us-ascii Murali,

if you might be interested in our OMEGAMON and !Candle Command Centre ranges, please write a note back to me, specify which region you're in, and I'll get one of my colleagues near you to send you all the relevant info.

in the meantime, you can look things up on our website, www.candle.com . [...] 19901 120 37_Re: Problem running DSNTEP2 from REXX14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Sun, 6 May 2001 21:07:41 -0500271_- A few observations: - DSNTEP2, in its normal form, runs as a sub-task of the DSN command processor ( not through a TSO CALL command) - when stuff is PUSHed onto the stack, it will be processed - if not by you, then, when the REXX ends, by the TSO monitor program. [...] 20022 42 21_Re: DB2 Catalog Chart18_Subramanyam, Vijay35_vijay.subramanyam@BLR.HPSGLOBAL.COM30_Mon, 7 May 2001 09:21:35 +0500630_- You can find it on BMC site www.bmc.com/db2users



VIJAY



> -----Original Message----- > From: Daniel Sullivan [SMTP:daniel.sullivan@ZURICHNA.COM] > Sent: Friday, May 04, 2001 7:40 PM > To: DB2-L@RYCI.COM > Subject: DB2 Catalog Chart > > Does anyone know the Site address where I can get a copy of the DB2 > Catalog. Platinum use to put it out, I think CA has continued the > practice. > I went to their web site but could not find it. > > Thanks. > > ================================================ > To change your subscription options or to cancel your subscription visit > the DB2-L webpage [...] 20065 25 26_Breaking up the tablespace12_Rakesh Kumar21_rakesh457@HOTMAIL.COM30_Mon, 7 May 2001 04:31:59 -0000302_- ALL, I have a tablespace which has 12 tables and I would like to change this . I would like to have a single table per tablespace so that I do not have any problems with recovery in future and the present system also should not be hampered. Any Ideas will be appreciated. Thanx in anticipation. [...] 20091 24 30_Re: Breaking up the tablespace18_Rajesh Kumar Sinha24_rksinx@LOT.TATASTEEL.COM30_Mon, 7 May 2001 11:05:03 +0530581_us-ascii Rakesh it depends upon type of these 12 tables , whether R/O or R/W. Instead of assigning single table table space you might go for existing table space for all R/O tables or low growth tables and individual table space created for each R/W. Because separating all 12 tables what ever tools you use , all dependent plans would need rebind. Rebind is required again to check the authorization,object validity and access path. Before going for this you must check criticality of your system, no of dependent plans & no of records in these tables. This will help you in [...] 20116 15 43_Paul Park/Toronto/IBM is out of the office.21_Paul Park/Toronto/IBM16_ppark@CA.IBM.COM30_Mon, 7 May 2001 02:46:18 -0400392_us-ascii I will be out of the office starting May 7, 2001 and will not return until May 14, 2001.

I will respond to your message when I return.

================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. 20132 19 13_Re: SQL Books18_Richard Mccutcheon32_Richard_McCutcheon@HCM.HONDA.COM30_Mon, 7 May 2001 08:57:57 -0400423_us-ascii Try the books out at this site:



http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM



Richard McCutcheon, Honda of Canada Mfg.

================================================ 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. 20152 92 46_Re: Restart Logic with Sequential Output Files15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Mon, 7 May 2001 07:59:26 -0500431_ISO-8859-1 "The first response to this question talked about two-phase commits. They are not relevant to sequential files because there is no resource manager for sequential files. "

Right you are, unless the sequential data set is an ESDS or RRDS that is used by CICS(?). Thanks for the correction.

Linda Billings Enterprise Systems Programmer State of Wisconsin Department of Administration Info-Tech Services [...] 20245 81 32_Stored procedures using MSAccess9_Colin Fay13_cfay2@CSC.COM30_Mon, 7 May 2001 09:13:25 -0400433_us-ascii Hi,

I sent out a previous memo on this same problem where I was getting a 'data truncation error' noted in the ODBC trace when using MSAccess to execute a stored procedure. I have elimnated that error by not using an integer in the parms coming from the SP. However I am still getting no data back using MSAccess while I do using the SP builder. The SP has RESULT_SETS = 1 and the result can only contain 1 row. [...] 20327 72 61_Re: making changes to DB2 tables thru and ODBC link to Access13_Mackey, Glenn21_GMackey@GUIDEMAIL.COM30_Mon, 7 May 2001 08:18:12 -0500331_iso-8859-1 Hi,

We just had an incident whereby we get an error message "No Current Record" when modifying a value to a linked table. It seems to be related to columns which have triggers associated with them. When we create a query which does not reference the columns with the triggers, then the update is successful. [...] 20400 43 13_Re: SQL Books14_Campbell White20_cwhite@REVEALNET.COM30_Mon, 7 May 2001 09:53:02 -0400458_us-ascii The Knowledge Base for DB2, just updated for v7, covers SQL coding rules with reusable examples and performance checklists. Download the 30-day trial at http://www.revealnet.com

Best wishes,

Cam White RevealNet, Inc.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM] On Behalf Of 'Richard Mccutcheon' Sent: Monday, May 07, 2001 8:58 AM To: DB2-L@RYCI.COM Subject: Re: SQL Books [...] 20444 52 13_Re: SQL Books11_Jeff Faughn22_Jeff_Faughn@MAY-CO.COM30_Mon, 7 May 2001 09:53:23 -0500572_us-ascii Gabrielle Wiorkowski has a pretty good book. It is easy to read and has exercises... : )







Babu d cc: Sent by: DB2 Subject: SQL Books Data Base Discussion List



05/04/01 08:40 PM Please respond to DB2 Data Base Discussion List

















Hello all,

Can you suggest some good books on SQL. I didn't find anything specific to DB2. Are there any books available with case studies or exercises. [...] 20497 71 20_Re: backups and logs11_Judy Kibler21_judith.kibler@TRW.COM30_Mon, 7 May 2001 10:01:47 -0500538_us-ascii Hello everyone,

I am glad that John asked these questions, as I am currently dealing with some of the same issues. We are also V6 on AIX. I have just implemented the userexit to archive off the log files to disk, and that appears to be working fine. I am now trying to figure out how to utilize TSM to copy the archived logs off to tape, without wasting a 100GB tape for each archive log file. Are there other folks out there using TSM to backup the database and/or to help with archive log management? If so, I sure [...] 20569 63 64_Interesting Paper on Object Oriented Database Management Systems14_Philip Gunning24_Philip.Gunning@QUEST.COM30_Mon, 7 May 2001 08:18:16 -0700227_- FYI. Like usual, it fails to identify DB2 as providing OO support but is a very interesting article with some interesting threads.



http://slashdot.org/features/01/05/03/1434242.shtml





Phil 20633 161 24_Re: DSN1COPY Performance10_Bill Disch23_bill.disch@GENMILLS.COM30_Mon, 7 May 2001 10:18:28 -0500374_- Charles,

I was at a training class last week so I just saw your post today.

Each 2 gig dataset consumes 7-8 tape cartridges, so an entire dataset takes us around 25 minutes also. When using a FULL tablespace copy, the 2nd, 3rd, 4th datasets each took around 60-70 minutes. I have noticed, also that using the VSAM file instead of a tape is much faster. [...] 20795 35 26_Performance of Data Joiner11_Bikash Paul19_bikash_db@YAHOO.COM30_Mon, 7 May 2001 08:24:21 -0700608_us-ascii Hi, We are implementing a ware house solution for a reporting project. The OLTP datbase is in SYBASE and Reporting database will be DB2. We have planned to use Data Joiner as middleware to replicate data from OLTP to reporting database. For non-IBM databases, it use data capture trigger to capture changes and is applied in DB2 asynchronously. With this kind of capture strategy, we are bit worried out OLTP performace as triggers are to run to capture data while online application is running. Has anyone been involved with this kind of architecture and share experience please and any other [...] 20831 103 20_Re: backups and logs19_Philip Nelson (DBA)21_teamdba@ATTGLOBAL.NET30_Mon, 7 May 2001 16:34:04 +0100514_us-ascii John / Judy,

We also are DB2 V6 on AIX and use TSM for our backups. Looking at getting the logs archived using TSM is on my "todo" list.

I notice you've implemented the user exit to archive the logs to disk. There is also a sample user exit for archiving straight to TSM - ends in .cadsm rather than .cdisk - but I've not yet tried to compile this. I did try to compile the cdisk one a while ago and couldn't get it to compile. Can you share your compile flags / commands / make file ? [...] 20935 17 20_Dynamic SQL question15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Mon, 7 May 2001 10:53:32 -0500356_us-ascii Hi List

Is there any way to check access paths for dynamic sql's ?

Thanks in advance Nayeem

================================================ 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. 20953 20 28_Audit information for Tables13_Juan Mautalen22_jmautalen@ANSES.GOV.AR30_Mon, 7 May 2001 13:01:19 -0300517_us-ascii Hi, We would like to know the way in which you get auditing information about inserts, updates and deletes done on DB2 tables. This information includes: user, date, data modified/inserted/deleted.

Thanks in advance for your help,



JUAN MAUTALEN

================================================ 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. 20974 53 32_FW: [DB2-L] Dynamic SQL question13_Daniel Turner28_daniel_turner@ALBERTSONS.COM30_Mon, 7 May 2001 09:17:47 -0700323_iso-8859-1 This is my test template for OS390. All you need is the plan_table. I started using DSN_STATEMNT_TABLE in release 6.1 it gives some relative cost values and some reason codes. I've used it from DSNTEP2 and QMF but not from SPUFI. It works great for what-ifing new ideas or resolving differences of opinion. [...] 21028 41 32_Re: Audit information for Tables9_Don Alden25_Don.Alden@I-STRUCTURE.COM30_Mon, 7 May 2001 10:26:29 -0600396_iso-8859-1 Juan,

You can turn on Table auditing by using AUDIT CHANGES to the table. This can be accomplished by using ALTER TABLE table_name AUDIT CHANGES to the desired table.

Good Luck, Don Alden

-----Original Message----- From: Juan Mautalen [mailto:jmautalen@ANSES.GOV.AR] Sent: Monday, May 07, 2001 9:01 AM To: DB2-L@RYCI.COM Subject: Audit information for Tables [...] 21070 128 15_Re: db2 monitor13_Murali Kanaga19_murali@EPIPHANY.COM30_Mon, 7 May 2001 10:32:10 -0700608_iso-8859-1 Hi,

Thanks for your reply. WE are located in N.California. (Closed to San Francisco). You can reach me via e-mail or phone: 650-578-7283.

thx, Murali

> -----Original Message----- > From: Aurora Dell'Anno [mailto:Aurora_E_DellAnno@CANDLE.COM] > Sent: Sunday, May 06, 2001 2:15 PM > To: DB2-L@RYCI.COM > Subject: db2 monitor > > > Murali, > > if you might be interested in our OMEGAMON and !Candle > Command Centre ranges, > please write a note back to me, specify which region you're > in, and I'll get one > of my colleagues near you to send you all the relevant info. > [...] 21199 29 33_Logical partitions / node (EEE) ?13_Tim Brumfield27_tbrumfield@MMRS.STATE.MS.US30_Mon, 7 May 2001 12:42:26 -0500301_us-ascii Hello everyone, We are running DB2 UDB EEE V7.1 for AIX on an RS/6000 SP with 6 single processor nodes. We are about to purchase a new SP with four SMP nodes (4-way 375mhz Power3 Wide).

My question is; how many logical partitions per node would be the best configuration for DB2? [...] 21229 49 24_Re: Dynamic SQL question15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Mon, 7 May 2001 13:14:16 -0500404_iso-8859-1 Hi, Nayeem, In SPUFI or QMF, EXPLAIN ALL SET QUERYNO = xxxx FOR insert SQL here

Replace xxxx with a number that will identify the query for you in the PLAN_TABLE. The plan table it will insert to is dependent on the SQL ID that you execute it under.

HTH,

Linda Billings Enterprise Systems Programmer State of Wisconsin Department of Administration Info-Tech Services [...] 21279 117 24_Re: DSN1COPY Performance17_Charles Jambrosic22_CJambrosic@CSIHOME.COM30_Mon, 7 May 2001 11:32:08 -0700384_us-ascii Hi Bill,

I split the IC's to do a full to dasd on each dsnum and was getting consistent elapsed times of 13 to 14 minutes. When I dsn1copy them back to their vsam files they more than double their elapsed times to 31-32 minutes each with a considerable increase in excp's. I am assuming there are some internal vsam considerations when copying to a vsam dataset. [...] 21397 81 24_Re: Dynamic SQL question15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Mon, 7 May 2001 14:07:14 -0500577_us-ascii ----- Forwarded by Mohammed Nayeem/MoMedicaid/US on 05/07/01 02:05 PM ----- |--------+--------------------------------> | | "Billings, Linda" | | | | | | | | | 05/07/01 01:14 PM | | | Please respond to DB2 | | | Data Base Discussion | | | List | | | | |--------+--------------------------------> >--------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: (bcc: Mohammed Nayeem/MoMedicaid/US) | | Subject: Re: Dynamic SQL question | >--------------------------------------------------------| [...] 21479 101 24_Re: DSN1COPY Performance10_Bill Disch23_bill.disch@GENMILLS.COM30_Mon, 7 May 2001 14:09:34 -0500662_- Charles,

Sounds like you've got the process tuned very well.





-----Original Message----- From: Charles Jambrosic [mailto:CJambrosic@CSIHOME.COM] Sent: Monday, May 07, 2001 1:32 PM To: DB2-L@RYCI.COM Subject: Re: [DB2-L] DSN1COPY Performance



Hi Bill,

I split the IC's to do a full to dasd on each dsnum and was getting consistent elapsed times of 13 to 14 minutes. When I dsn1copy them back to their vsam files they more than double their elapsed times to 31-32 minutes each with a considerable increase in excp's. I am assuming there are some internal vsam considerations when copying to a vsam dataset. [...] 21581 29 34_LPL Status and Rebuilding an Index13_Moss, William15_MossW@AETNA.COM30_Mon, 7 May 2001 15:22:30 -0400552_iso-8859-1 Our environment is DB2 Version 6 with Data Sharing, OS/390 2.8. We recently had a large NPI with a status of LPL and a rebuild index was submitted to correct the problem. During the unload phase of the rebuild, the utility was canceled because we felt it would be quicker to do a Start Database command to clear up the LPL, rather than wait an hour for the rebuild index to complete. The problem was after the utility job was canceled and the utility was terminated, the status of the index was PSRBD. Does this mean that once you have [...] 21611 126 46_Re: Restart Logic with Sequential Output Files16_Aurora Dell'Anno28_Aurora_E_DellAnno@CANDLE.COM30_Mon, 7 May 2001 20:10:15 +0100508_us-ascii quoting "I don't think IMS checkpoints can include MQSeries changes...." this actually depends on what you're doing. if you use MQSeries API with IMS applications, an IMS commit is also an MQSeries and DB2 commit.

anyway, if you're interested in more about this, look up Stephen Nathan's excellent presentation "Implementation Details for the MQSeries-IMS Interface, which he gave at the DB2/IMS & SQL/DS GSE Conference in Iceland last week (should be on the GSE Website) or contact me. [...] 21738 56 38_Re: LPL Status and Rebuilding an Index34_Pillay, Venkat (USPC.PCT.Hopewell)20_venkat_pillay@ML.COM30_Mon, 7 May 2001 15:44:11 -0400321_us-ascii I think, too late. Once you have started rebuilding index, only option for you is to complete rebuilding it unless you have COPY YES for index and have an imagecopy available. If you have an imagecopy then you could RECOVER.

In essence choice is now left to RECOVER (if imagecopy exists) or REBUILD. [...] 21795 53 38_Re: LPL Status and Rebuilding an Index15_Billings, Linda30_linda.billings@DOA.STATE.WI.US30_Mon, 7 May 2001 15:03:49 -0500355_iso-8859-1 Hi, Bill, I looked at the utility manual for V6 and there is no terminating the utility during unload any more. You must complete the rebuild.

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

"We have met the enemy and he is us." - Walt Kelly "Pogo Comic Strip" [...] 21849 84 38_Re: LPL Status and Rebuilding an Index15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM30_Mon, 7 May 2001 15:21:34 -0500595_us-ascii ----- Forwarded by Mohammed Nayeem/MoMedicaid/US on 05/07/01 03:21 PM ----- |--------+--------------------------------> | | "Billings, Linda" | | | | | | | | | 05/07/01 03:03 PM | | | Please respond to DB2 | | | Data Base Discussion | | | List | | | | |--------+--------------------------------> >--------------------------------------------------------| | | | To: DB2-L@RYCI.COM | | cc: (bcc: Mohammed Nayeem/MoMedicaid/US) | | Subject: Re: LPL Status and Rebuilding an | | Index | >--------------------------------------------------------| [...] 21934 30 24_a question about "scans"13_Dennis Taylor14_ismgr@PCTC.COM30_Mon, 7 May 2001 13:27:29 -0700469_us-ascii Quoting from the book I'm reading (DB2 Universal Database v7.1, by Baklarz/Wong), I've found the following paragraph on indexes (all right, indices).

"Indexes can be defined in ascending or descending order. (Ordering is dependent on the code page.) They can be defined as unique or nonunique, and they can involve a single column's data values or multiple columns' data values. They can also be defined to support both forward and reverse scans." [...] 21965 28 15_Re: db2 monitor13_Jim Wankowski23_jim.wankowski@QUEST.COM30_Mon, 7 May 2001 15:19:29 -0500647_- Hello Murali I am a product manager for Quest Software. Our product Quest Central for DB2 is a comprehensive integrated suite of tools for DB2 UDB database administration. It is comprised of 4 components: Database administration, SQL tuning, space management, and performance/diagnostics all running from a common windows console. Our performance monitor is truly unique in the way it visually displays all the performance metrics for a DB2 instance and allows for alarming and easy drill down to detailed performance reports. You can download a free 30-day trial from our website: WWW.QUEST.COM/DB2. Please feel free to contact me or send [...] 21994 15 39_Quest Software at IDUG 2001 in Orlando!13_Jim Wankowski23_jim.wankowski@QUEST.COM30_Mon, 7 May 2001 15:21:46 -0500373_- Don't Miss Quest Software at IDUG 2001 You won't want to miss Quest Software at IDUG 2001 on May 13-17 in Orlando. Visit us in booth #209 to see how managing DB2 on any platform is as easy as changing the channel. For more information, check out the online invitation at . We look forward to seeing you in Orlando! [...] 22010 16 11_DB2 Schema?13_Carlton Enuda24_carltonenuda@HOTMAIL.COM30_Mon, 7 May 2001 17:20:54 -0400 22027 133 15_Re: DB2 Schema?14_Richard Yevich26_Richard_Yevich@YLASSOC.COM30_Mon, 7 May 2001 16:50:43 -0500579_iso-8859-1 Think of SCHEMA as a high level qualifier for a group of related database objects. It is implemented differently in each of the DBMSs but it means basically the same -- a high level grouping of related objects. 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 Richard_Yevich@YLAssoc.com 1-888-246-5049/1-217-744-0000 http://www.YLAssoc.com -- DB2 Performance Journal http://www.db2-symposium.com -- DB2 Symposiums [...] 22161 42 27_Excellent Job Opportunities12_Trever Neves27_TNeves@ACCLAIMTECHNICAL.COM30_Mon, 7 May 2001 14:56:27 -0700631_- Hello Everyone,

My name is Trever Neves and I am a Technical Recruiter. I apologize in advance for the inconvenience if there is any. The reason why I am sending this message is that I am currently searching for a few very sharp individuals for a premier client of ours. The company is an International Company with operations in the Los Angeles area and offers excellent compensation, including Profit Sharing, along with an excellent benefits package. They offer an incredible working environment with an opportunity for career growth as well as hands-on experience with leading edge technology. They are currently [...] 22204 85 28_Re: a question about "scans"13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 7 May 2001 17:12:50 -0500537_us-ascii Dennis,

Given the context about indexes, I would categorize scans as anytime an index read requires more than one entry to be read (note these are my opinions and therefore open for discussion/disagreement).

Scans can be either non-matching or matching index scans.

A non-matching index scan has no matching index predicates and therefore all index leaf pages are read or scanned (much like a tablespace scan). Index screening (OS/390) or index sargable (non-OS/390) predicates are applied if present. [...] 22290 30 8_Bind Add13_Keerthi Chegu18_lpbusobj@YAHOO.COM30_Mon, 7 May 2001 16:53:33 -0500518_- Hello Everyone:

Currently, I am using Business Objects (reporting tool) to connect to DB2 (Version 5 on OS/390) database via DB2connect. When connecting to the Production DB2 instance, I get the following error using my userid:

The user id does not have the Bind add priveleges to execute (sql code 42501)

The security group is not willing to give Bind add priveleges to each and every user id. Is this a big issue ?I don't have a problem connecting to Oracle production database via Net8. [...] 22321 54 13_Please Ignore15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM30_Tue, 8 May 2001 08:22:56 +1000639_- Hello - I'm back

regards, Steve

I 5/484 St Kilda Rd, Melbourne

' (03) 9865 7647 7 (03) 9866 1753 *





> -----Original Message----- > From: L-Soft list server at RYCI.COM (1.8d) [SMTP:LISTSERV@RYCI.COM] > Sent: Tuesday, May 08, 2001 7:53 AM > To: Mallett, Steven > Subject: Output of your job "Steven.Mallett" > > > SET DB2-L MAIL > Your subscription options have been successfully updated. Here are the > exact > settings now in use for your subscription. Please take a few moments > to > check that this is indeed what you wanted. > > Subscription [...] 22376 108 28_Re: a question about "scans"13_Terry Purcell25_Terry_Purcell@YLASSOC.COM30_Mon, 7 May 2001 17:24:04 -0500414_us-ascii Dennis,

I may have neglected to mention another important piece of information:

Non-OS/390 indexes have the option to be created to allow reverse index scans. This can avoid a DESC sort on an ASC index, or allow I1 fetch for MAX on ASC index and vice versa(s).

OS/390 V7 indexes can by default allow I1 fetch for MAX on ASC index and vice versa. They do not allow reverse scans. [...] 22485 17 28_Performance Class 9 overhead11_Robert Jans26_robert_jans@ALBERTSONS.COM30_Mon, 7 May 2001 11:12:23 -0700422_iso-8859-1 Quick question. We want to start a Performance class 9 trace. Can someone give me a rough guideline as to overhead incurred for this?

Robert Jans Albertson's, Inc

================================================ 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. 22503 19 39_DSNTPSMP stored procedure test program.11_Cliff Boley32_Maurice.C.BOLEY@ODOT.STATE.OR.US30_Mon, 7 May 2001 15:54:54 -0700470_iso-8859-1 Hi all, IBM nicely supplied a sample program to test DSNTPSMP stored proc. (DSN8ED4) Unfortunately its written in C which I don't have, so has anyone written one in REXX?? If so, could I have a copy?? thanks, cliff:-)

================================================ 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. 22523 54 12_Re: Bind Add13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM30_Mon, 7 May 2001 20:07:14 -0400485_iso-8859-1 Hello Keerthi,

You might find the same answer I found when I questioned why a Java application would fail requiring BINDADD. I don't know the Bus. Objects Software. But it would be a very unfriendly design if it required every user to have BINDADD privilege. More likely, especially since you mention DB2 Connect, it may be the case that you just happen to be the first person connecting to that Subsystem (production) using DB2 connect or perhaps the B.O. app. [...] 22578 50 37_Re: Logical partitions / node (EEE) ?11_Steve Mazer17_smazer@FMTUSA.COM30_Tue, 8 May 2001 00:33:18 -0400555_us-ascii At 12:42 PM 5/7/01 -0500, you wrote: >Hello everyone, >We are running DB2 UDB EEE V7.1 for AIX on an RS/6000 SP with 6 single >processor nodes. We are about to purchase a new SP with four SMP nodes >(4-way 375mhz Power3 Wide). > >My question is; how many logical partitions per node would be the best >configuration for DB2? > >From what I've been able to find, it looks like 2 logical partitions per >node is recommended, but I haven't found much to go on and would >appreciate any advice. > >Thanks to all, >-- >Tim Brumfield >DBA >MERLIN [...]