1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l June 2004, week 4 2 139 31_Re: Partitioned tablespace size13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 22 Jun 2004 09:45:33 +0200468_us-ascii More math correction: FREEPAGE n means one empty page after n filled pages. Thus, FREEPAGE 5 means 5 out of 6 pages hold data (83.33% of pages filled), so changing from FREEPAGE 5 to FREEPAGE 0 will result in 20% more pages. With FREEPAGE 5 PCTFREE 25, 37.5% of the space is empty after a REORG or LOAD, so changing this to FREEPAGE 0 PCTFREE 0 will result in 60% more space (0.625*0.6=0.375). With large row lengths, the empty space might be even more. [...] 142 104 31_DB2 Connect & Stored Procedures18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Tue, 22 Jun 2004 07:47:16 -0400574_us-ascii We are currently running DB2 Connect v7.2 with Fixpak 10A (I know we're way behind) against OS/390 DB2 v7. We have some very large SQL select queries that run every day and the SQL stmts/queries never change. I guess you could say they are canned queries & they pull down a lot of data. Some of these large queries run for a considerable amount of time and usually end up hitting one of our time-out thresholds. Would it be a performance improvement if we made these large queries Stored Procedures and executed them this way from the DB2 Connect application? [...] 247 47 24_BIND AUTHORIZATION error12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 22 Jun 2004 15:41:11 +0200368_- Collegues,

because a new plan and a new collection comes up I have granted an owner named ELSTER as follows: CREATE IN COLLECTION GRANT BINDADD.... GRANT PACKADM ON COLLECTION collectionname.*.... GRANT BIND ON PLAN planname .... GRANT BIND ON PACKAGE collectionname.*

But I receive the following error message after a BIND PACKAGE IN COLLECTION: [...] 295 84 28_Re: BIND AUTHORIZATION error12_michael bell21_mbell11a1@VERIZON.NET31_Tue, 22 Jun 2004 09:04:50 -0500418_Windows-1252 In order to use the OWNER(ELSTER), the person who submits the job must have either 1. SYSADM 2. a valid RACF group of ELSTER attached to the userid 3. or a primary id of ELSTER Item number 2 always requires request to the RACF security group and they always want to know what the group name is for. I usually wait until they setup a group id for the project and then use that for the secondary auth. [...] 380 53 28_Re: BIND AUTHORIZATION error14_Seth Bienstock17_seth2@SKARVEN.NET31_Tue, 22 Jun 2004 10:04:08 -0400302_us-ascii It seems that "ELSTER" is not a valid SECONDARY authorization ID for whomever the USER is that you're using to run the BIND. I believe the only user who can use a SECONDARY authID that will not be validated by querying the security subsystem (i.e. RACF) is a user with SYSADM privileges. [...] 434 129 57_Answer: BIND AUTHORIZATION error - reason is clear now...12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 22 Jun 2004 16:14:50 +0200437_iso-8859-1 Thanks Mike and Seth.

The problem was that the userid from the person who submits the job was NOT included in the RACF group "ELSTER"..... ;-((

Best regards, G e o r g

-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von michael bell Gesendet: Dienstag, 22. Juni 2004 16:05 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: Re: BIND AUTHORIZATION error [...] 564 14 28_Re: BIND AUTHORIZATION error33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Tue, 22 Jun 2004 09:50:32 -0500702_- Georg

If the user ELSTER Grants BINDAGENT to PUBLIC or to some users, these are allowed to use ELSTER in the OWNER-keyword. Or someone with SYSADM- authority executes SET CURRENT SQLID = 'ELSTER' and then grants bindagent.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 579 487 26_Re: 2 Options , Which One?12_Brill, Steve22_Steve.Brill@ECOLAB.COM31_Tue, 22 Jun 2004 09:54:44 -0500342_- One other thing. If you do Option A, then there are locks at the database level while this table is accessed that can prevent alteration of other objects in the same database. And keeping the table in DB2 versus outside of DB2 gives you the full benefit of DB2 facilities, including backup and recovery. So, Option B is the way to go. [...] 1067 39 26_Re: 2 Options , Which One?13_Michael Ebert18_mebert@AMADEUS.NET31_Tue, 22 Jun 2004 17:10:28 +0200533_iso-8859-1 This DB locking thing used to be a serious issue if you had long-running dynamic SQL 5 years ago. However, there must have been some changes in DB2 in the meantime. I don't recall any more of these problems in the past years. In SAP, you have one DB per TS because of the mentioned problem (a total of >10.000 DBs) - but in the newest version 4.7 (6.20 kernel), that is being changed, so SAP is learning... On the other side, we have DBs with thousands of TSs, no problem there either. In DB2, a database is mostly a [...] 1107 35 45_Identity column values skipping - DB2 z/OS V714_Andrew Meddick27_andrew.meddick@MARRIOTT.COM31_Tue, 22 Jun 2004 10:23:52 -0500373_ISO-8859-1 DB2 List Servers,

We have a DB2 z/OS V7 table that uses an identity column (the table also has a CLOB – but I don’t think that’s relevant). It is defined as: generated always, no cycle, cache 20, starting with +1, increment by +1, maxvalue is 1 billion and minvalue is +1. The identity column is indexed as unique to ensure uniqueness of the values. [...] 1143 70 49_Re: Identity column values skipping - DB2 z/OS V714_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 22 Jun 2004 16:39:47 +0100414_iso-8859-1 The DB2 Load utility caches these values (in 1,000 chunks??) and the cached values do NOT get reused if you don't load multiples of 1,000 rows.

However, did I recently see an APAR "fixing" this reported by someone on the list????

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 [...] 1214 74 49_Re: Identity column values skipping - DB2 z/OS V717_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM31_Tue, 22 Jun 2004 10:45:59 -0500472_iso-8859-1 Hi Andrew,

I can't remember the exact answer here, but I remember enough to stop you from wondering. There are two things at work here.

(1) If you specify a CACHE value then DB2 reserves identity values based upon that whenever it renews the cache. For instance if you are using CACHE 100 then DB2 reserves 100 at a time. If 3 of them get used and then you STOP DB2 the remaining 97 are lost, and DB2 will start at 101 when it comes back up. [...] 1289 13 41_Re: UDB to DB/2 Connect Problems - Update4_Mike31_michael.brennan@I-STRUCTURE.COM31_Tue, 22 Jun 2004 10:45:56 -0500570_- Lee, Did you perform all the binds (i.e. CLI/ODBC Support) from the Configuration Assistant? Mike

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 1303 204 35_Re: DB2 Connect & Stored Procedures11_Smock, Dale22_dsmock@RANDOMHOUSE.COM31_Tue, 22 Jun 2004 11:01:45 -0500549_us-ascii Unless the Stored Procedure code can issue multiple sql with each call or do additional filtering of the rows before they are returned, you will probably not improve the performance. An individual SQL will perform about the same as remote sql vs. a Stored Procedure sql, unless the WLM definitions for your Stored Procedure queries are running at a higher priority than the DIST address space queries. For large queries, most of the processing is probably done in DBM1 to process the sql and in returning the rows back to the desktop. [...] 1508 68 41_Re: UDB to DB/2 Connect Problems - Update0_29_lherbst@PROPAPPR.MARION.FL.US31_Tue, 22 Jun 2004 11:55:59 -0400470_US-ASCII Yes. BTW, we can do selects, updates, alters, etc through the Command Center and Command Window. It just doesn't make any sense that we can't get this working.

IBM has also suggested we try DJRA. We tried it and we can see the columns with it, but when we try to create the SQL it returns an error say that Object Rexx was not installed properly and to uninstall and reinstall DJRA. We have tried this three times without any luck. Any other ideas? [...] 1577 192 80_Re: DB2 OS/390 V7 and Cognos DecisionStream Product V7.1.646/ DB2 connect V8.1.518_Blake, Betty@HHSDC19_BBlake@HHSDC.CA.GOV31_Tue, 22 Jun 2004 09:16:10 -0700455_- This stored procedure and others used by DB2 connect V8 are added to DB2 OS390 V7 with job DSNTIJMS added by PQ62695/UQ72083 and it's string of PE fixes.





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of John Lendman Sent: Thursday, June 17, 2004 10:10 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 OS/390 V7 and Cognos DecisionStream Product V7.1.646/ DB2 connect V8.1.5 [...] 1770 274 80_Re: DB2 OS/390 V7 and Cognos DecisionStream Product V7.1.646/ DB2 connect V8.1.512_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Tue, 22 Jun 2004 12:54:24 -0400364_iso-8859-1 Thanks, some else has told me that. I am in the process of putting them on now.

Thanks to all.

John C. Lendman IBM Certified Solutions Expert IBM Certified Database Administrator DB2 Universal Database V8.1 for zOS School District of Palm Beach County DBA Room B241 sub room b207 Office 561-357-7523 Email lendman@palmbeach.k12.fl.us [...] 2045 252 35_Re: DB2 Connect & Stored Procedures18_Whittaker, Stephen29_stephen.whittaker@PGNMAIL.COM31_Tue, 22 Jun 2004 13:00:09 -0400699_us-ascii Dale: Thank-you; that makes the picture a little clearer...

--Steve....

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Smock, Dale Sent: Tuesday, June 22, 2004 12:02 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: DB2 Connect & Stored Procedures



Unless the Stored Procedure code can issue multiple sql with each call or do additional filtering of the rows before they are returned, you will probably not improve the performance. An individual SQL will perform about the same as remote sql vs. a Stored Procedure sql, unless the WLM definitions for your Stored Procedure queries are running at a higher [...] 2298 29 52_DB2 Connect Security when accessing DB2 V7.1 on z/OS0_25_Ed_Vetock@NAVYFEDERAL.ORG31_Tue, 22 Jun 2004 16:22:10 -0400346_US-ASCII All,

Perhaps you can provide some options for the following situations:

Situation: We have DB2 Connect V8 accessing DB2 V7.1 on z/OS. We assigned a common id XXXXXXX with a password that does not expire to a JAVA application that connects to z/OS via DB2 Connect. This id XXXXXX as update authority on several tables. [...] 2328 59 56_Re: DB2 Connect Security when accessing DB2 V7.1 on z/OS14_Seth Bienstock17_seth2@SKARVEN.NET31_Tue, 22 Jun 2004 16:33:07 -0400344_us-ascii One option might be to use the Resource Limit Facility (in z/OS) to completely restrict dynamic SQL activity from that user ID. You didn't mention whether the application was using static or dynamic SQL.

Another option might be to store the CONNECT USER PWD in a single routine that is security protected for EXECUTE only. [...] 2388 81 31_Re: Dataset Open/close in DB2PM7_db2sysc17_db2sysc@YAHOO.COM31_Tue, 22 Jun 2004 17:17:14 -0500616_- Hi Martin,

Could you help me with the JCL to get this done? I am very new on the SMF front and any help would be great

Thanks.

On Sun, 20 Jun 2004 11:06:23 +0100, Martin Packer wrote:

>Open and with I/O being done to them could (in the main) be obtained from >SMF 42-6. > >Cheers, Martin > >Martin Packer, MBCS Martin Packer/UK/IBM >020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile >07802-245584) > > > > db2sysc > M> To > Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG > Base Discussion cc > List >
When I try to print out Accounting short report using PM batch jcl and have SMF dataset as input ( our ACCT/STAT are directed to SMF).

I get these messages in the JOBSUMDD output.

FPEA2252I XXAP N/P DBAP N/P 06/17/04 00:00:00.14 ACCOUNTING FIELD ADELCL7L WAS NOT CALCULATED. A BEGIN TIME WAS ZERO OR THE RESULT WOULD HAVE BEEN NEGATIVE [...] 2504 21 49_coversion error when loading accounting file data7_db2sysc17_db2sysc@YAHOO.COM31_Tue, 22 Jun 2004 17:40:41 -0500387_- All:

We created the db2 accounting file tables given in SFPESAMP and tried loading the accounting file data into the table using DGOALFGE member in the sample library. We are hitting DISCARDS with the following errors?

How do we get around this? I even tried doing a decimal external for the CLASS8_SCHED_STPC and still gives the same error. We are in V7 of DB2 PM. [...] 2526 50 32_UDB V8.1 type 2 index conversion12_tim malamphy20_timalamphy@YAHOO.COM31_Tue, 22 Jun 2004 16:04:58 -0700566_us-ascii Unix-type listers-

I think I'm in a Catch-22 situation.

I'm converting from UDB 7.2 to UDB 8.1 on AIX 4.3. Testing has shown that we'll be innundated with 911 deadlocks due to "get next" index processing unless we use type2 indexes. (or db2_rr_to_cs registry variable which is only available on 7.2). Some of the indexes are on tables with 30 - 230 million rows. I calculate from volume tests that it will take 8 hours to build an index (3 columns) on the 30 million row table from scratch, or 6 hours to convert it and a second index (1 [...] 2577 77 36_Re: UDB V8.1 type 2 index conversion12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Tue, 22 Jun 2004 20:35:38 -0400542_us-ascii Tim, It's like the first law of economics, you can't have your cake and eat it too:). Some options are any operation that requires a rebuild of the index will convert it to type-2 (provided that there are no existing type-1). A load with INDEXING MODE REBUILD will rebuild all indexes as type-2. Take a look at lock deferral in 8.1.4...but there are some restrictions (type-2) restrictions for index - data access. I haven't tested this yet. Registry value is DB2_EVALUNCOMMITTED and it is evaluated at runtime for dynamic SQL. [...] 2655 176 26_Re: 2 Options , Which One?8_BigSmile21_msherazi@VIDEOTRON.CA31_Tue, 22 Jun 2004 21:05:55 -0400488_us-ascii

Thank you all for help & support

Mike sherazi This message, including any attachments, is intended solely for the use of the named recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution of this communication(s) is expressly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy any and all copies of the original message. Thank you. [...] 2832 78 53_Re: coversion error when loading accounting file data13_Mertens, Bart18_bart.mertens@CZ.NL31_Wed, 23 Jun 2004 08:34:20 +0200378_- If I recall right you have to split up the different types of accounting records first and use separate load statements for each dataset.

Use member DGOALFPK and DGOALSPK to load the accounting records described below.

met vriendelijke groeten, Bart Mertens Database Administrator DB2 CZ Actief in Gezondheid - Tilburg * (013) 593 8256 * bart.mertens@cz.nl [...] 2911 59 41_DB2 on z/OS accessing data on DB2 for AIX0_18_mflavell@I-TCS.COM31_Wed, 23 Jun 2004 08:24:24 +0100394_US-ASCII Hi all,

Perhaps you can suggest how to resolve this problem.

We currently have DB2 for AIX version 7.2 reading, in realtime, the data on a remote database running under DB2 for AIX version 5 (yes I know its unsupported, but I have been told there are very good reasons why previous attempts to upgrade failed). The connection is established as a federated database. [...] 2971 22 35_Amending the Package RELEASE option12_Pete Woodman20_pete.woodman@EDS.COM31_Wed, 23 Jun 2004 04:20:57 -0500444_- We currently have all Packages bound with RELEASE (DEALLOCATE) (historical reasons!) and are proposing using RELEASE (COMMIT) for CICS and RELEASE (DEALLOCATE) for Batch.

We would then use two different Plans to control which RELEASE option is used.

However, our problem is that all packages have been bound with RELEASE (DEALLOCATE) specifically coded, and I can't find any way of using REBIND to say "No RELEASE" option. [...] 2994 19 39_Re: Amending the Package RELEASE option33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Wed, 23 Jun 2004 04:45:38 -0500433_- Pete

We are facing similar problems and in fact, you cannot rebind a package with no RELEASE-parameter specified. But what you can do is, BIND the package once more and omit this parameter. Then the package's default is the plan's release parameter.

BTW, if you bind your package remotely and you omit the release parameter, the default will always be commit for that package regardless of the plan's parameter. [...] 3014 183 28_XML to SQL Stored Procedures12_kent collins20_kent.collins@ATT.NET31_Wed, 23 Jun 2004 04:54:39 -0500418_us-ascii I am looking for XML processing code that will take a XML convert to SQL and execute for insert, update, delete SQL statements and for Select will return a XML with Data.





I have reviewed XML Extenders(IBM) code. Xcollection being the setup I need, seems to require a OVERRIDE either RDB or SQL to allow for different SQL processing. Also I am not crazy about the Side-Table setup. [...] 3198 16 39_Re: Amending the Package RELEASE option4_Pete20_pete.woodman@EDS.COM31_Wed, 23 Jun 2004 05:08:57 -0500640_- Walter,

I was aware that I could BIND the Packages, but unfortunately we've about 5,500 to do, so I was looking for an "easy" option using REBIND!

Cheers

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 3215 16 33_DB2 UDB AIX backup command tuning12_Totzke, John27_John.Totzke@ARGUSHEALTH.COM31_Wed, 23 Jun 2004 09:19:15 -0500698_us-ascii When running a DB2 BACKUP command on AIX what rule of thumb should be used for PARALLELISM in relation to the number of SESSIONS. Is there a benefit to set PARALLELISM higher than the number of sessions when using TSM?

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 3232 251 39_Re: Amending the Package RELEASE option11_David Nance16_DWNance@FHSC.COM31_Wed, 23 Jun 2004 10:37:45 -0400408_- H QMF 13 F 04 E V W E R 01 03 04/06/23 10:36 T 1110 005 011 1112 007 1113 040 1114 007 1115 006 1116 005 1117 005 1118 003 1119 008 1120 008 1122 006 1121 050 R CHAR COL1 OMIT 2 86 C 1 DEFAULT DEFAULT NO R CHAR COL2 OMIT 2 59 C 2 DEFAULT DEFAULT NO R CHAR COL3 OMIT 2 89 C 3 DEFAULT DEFAULT NO R CHAR COL4 BREAK1 2 80 C 4 DEFAULT DEFAULT NO R CHAR COL5 OMIT 2 48 C 5 DEFAULT DEFAULT NO V 1201 001 0 V [...] 3484 60 39_Re: Amending the Package RELEASE option14_Heiss, Gerhard20_Gerhard.Heiss@RUV.DE31_Wed, 23 Jun 2004 17:10:55 +0200594_us-ascii Pete, I don't have a solution for your original question, but I would like to ask you for your reason to go to RELEASE(COMMIT) in your CICS-plans. Don't know if it's still so, but some years ago it was recommended to use ACQUIRE(USE) and RELEASE(DEALLOCATE) since :" ...it favors the online transaction processing environment (CICS and IMS) where it is more efficient to hold the locks until the thread terminates " and "... it is particularly good for thread reuse in in an OLTP environment"(from the red book Locking in DB2 for MVS/ESA Environment). But since this is almost 10 [...] 3545 180 39_Re: Amending the Package RELEASE option11_David Nance16_DWNance@FHSC.COM31_Wed, 23 Jun 2004 11:26:39 -0400495_US-ASCII Gerhard, We did try using release deallocate on a couple of CICS regions I've supported and on all occasions, it caused us locking headaches like you wouldn't believe. These were systems with heavy volume and multiples of the same transaction running concurrently. These are the transactions that really caused us the headaches, due to trying to lock the same rows/pages. As its said on here YMMV(your mileage may vary), but we stay away from release deallocate in the CICS world. [...] 3726 152 36_Re: UDB V8.1 type 2 index conversion12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 23 Jun 2004 08:35:42 -0700579_us-ascii Good Morning Phil- I thought this might catch your attention. I can understand not being able to use all the new whiz-bang features available with type2 indexes until I convert them, and I am willing to pay the price (outage) to convert an index for the new features. But the lack of having the DB2_RR_TO_RS registry value with V8 poses a large barrier to moving to V8 for any larger unix/UDB customers who used it to eliminate get_next locking, and who also have large tables/indexes. We have to take an outage for the V8 code/catalog upgrade (a couple of hours), [...] 3879 171 36_Re: UDB V8.1 type 2 index conversion12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Wed, 23 Jun 2004 11:44:40 -0400628_us-ascii I understand Tim. Let us know if you come up with a solution. Rgds, Phil

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of tim malamphy Sent: Wednesday, June 23, 2004 10:36 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: UDB V8.1 type 2 index conversion

Good Morning Phil- I thought this might catch your attention. I can understand not being able to use all the new whiz-bang features available with type2 indexes until I convert them, and I am willing to pay the price (outage) to convert an index for the new features. But the lack of having the [...] 4051 327 36_Re: UDB V8.1 type 2 index conversion11_David Nance16_DWNance@FHSC.COM31_Wed, 23 Jun 2004 11:53:27 -0400569_US-ASCII Tim, Not an exact answer, but a possible workaround. A couple of times in the past we have had to make structure changes to tables of the same size that you are talking about here. These changes involved drop/recreate table. We couldn't afford the outage time of unloading/drop/recreate/reload, similar to your rebuild problem. The way we got around it was to create two tables just like the prod version. We set up a trigger on the prod table to insert the same record to one of the tables we created, call that one the shadow. We then started an unload [...] 4379 231 36_Re: UDB V8.1 type 2 index conversion12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 23 Jun 2004 09:07:42 -0700602_us-ascii Thanks David- This is (unfortunatley) the scenario I was stumbling towards to do the larger indexes. I just didn't want this much complexity (or the overhead of triggers) introduced at the same time I'm upgrading software. --- David Nance wrote: > Tim, > Not an exact answer, but a possible workaround. > A couple of times > in the past we have had to make structure changes to > tables of the same > size that you are talking about here. These changes > involved > drop/recreate table. We couldn't afford the outage > time of > unloading/drop/recreate/reload, similar [...] 4611 43 41_Why can't I do this? - creating a trigger15_Edie Richardson23_edie.richardson@SRS.GOV31_Wed, 23 Jun 2004 13:32:25 -0500501_- I'm trying to create a trigger: CREATE TRIGGER T#ADBA.COMPU2 AFTER UPDATE ON T#ADBA.TCOMPANY2 REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL VALUES ( CASE WHEN O.CO_CD <> N.CO_CD THEN INSERT INTO T#ADBA.TMQ_COMPANY2(HIST_TS, TRANS_CD, CO_CD) VALUES(CURRENT TIMESTAMP, 'I', N.CO_CD); INSERT INTO T#ADBA.TMQ_COMPANY2(HIST_TS, TRANS_CD, CO_CD) VALUES(CURRENT TIMESTAMP, 'D', O.CO_CD); ELSE INSERT INTO T#ADBA.TMQ_COMPANY2 (HIST_TS, TRANS_CD, CO_CD VALUES(CURRENT TIMESTAMP, 'U', N.CO_CD); [...] 4655 80 45_Re: Why can't I do this? - creating a trigger12_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Wed, 23 Jun 2004 15:50:48 -0400392_iso-8859-1 Here is my guess. After the first insert you have a ;, since you have a second insert, maybe you only need one ; not two.



John C. Lendman IBM Certified Solutions Expert IBM Certified Database Administrator DB2 Universal Database V8.1 for zOS School District of Palm Beach County DBA Room B241 sub room b207 Office 561-357-7523 Email lendman@palmbeach.k12.fl.us [...] 4736 83 45_Re: Why can't I do this? - creating a trigger22_Killen, Martin W - CNF21_Killen.Martin@CNF.COM31_Wed, 23 Jun 2004 12:56:10 -0700538_us-ascii I don't see a BEGIN ATOMIC with a corresponding END # and you also may want to do a -- Set Terminator #.. I have seen problems arise when there are embedded semi-colons with out the Set terminator statement.. HTH..

Marty Killen Sr. DB2 DBA CNF Inc. 503-450-2681 killen.martin@cnf.com IBM Certified Solutions Expert DB2 V7.1 Database Administration for OS/390 DB2 V7.1 Database Administration for UNIX, Windows and OS/2 6 AM - 3 PM "Kind words can be short and easy, but their echoes are truly endless" Mother Theresa [...] 4820 67 45_Re: Why can't I do this? - creating a trigger16_Graeme St. Clair23_Graeme.St.Clair@HDS.COM31_Wed, 23 Jun 2004 13:04:43 -0700760_iso-8859-1 Isn't there a right paren missing at the end of the line with the 3rd insert?

HTH, G.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Edie Richardson Sent: Wednesday, June 23, 2004 2:32 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Why can't I do this? - creating a trigger



I'm trying to create a trigger: CREATE TRIGGER T#ADBA.COMPU2 AFTER UPDATE ON T#ADBA.TCOMPANY2 REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL VALUES ( CASE WHEN O.CO_CD <> N.CO_CD THEN INSERT INTO T#ADBA.TMQ_COMPANY2(HIST_TS, TRANS_CD, CO_CD) VALUES(CURRENT TIMESTAMP, 'I', N.CO_CD); INSERT INTO T#ADBA.TMQ_COMPANY2(HIST_TS, TRANS_CD, CO_CD) VALUES(CURRENT TIMESTAMP, [...] 4888 42 59_Running Existing CICS/DB2/COBOL Application In Japan Market13_Hannis, James19_James.Hannis@GE.COM31_Wed, 23 Jun 2004 15:12:39 -0500460_- I'm looking for practical tips, guidelines, reference sources, white papers, etc. on the issues that would be encountered by DBA's, DB2 Systems Programmers and/or Development staff when an existing CICS/DB2/COBOL business application, originally developed for the US/Europe market, begins supporting new business opportunities in Japan.

In the short term (whatever that is) - the system would be provided to only internal users in Japan "as is". [...] 4931 95 25_Reorg with discard Option11_David Nance16_DWNance@FHSC.COM31_Wed, 23 Jun 2004 17:29:57 -0400565_US-ASCII Here's one that I could use some help with.

DB2 V7 on Z/OS

Table has character column with the first 6 columns making up a date in the format 040220 = April 20, 2004

Want to run reorg with discard, discarding records where this date is more than 3 weeks old. I've tried a few different scenarios on converting this field into a date, but the reorg tells me that those are invalid keywords(such as substr). I then tried reversing it and changing current date - 21 days to char and formatting it and get invalid keyword on the CHAR [...] 5027 133 29_Re: Reorg with discard Option14_Allen, Susan A24_susan.a.allen@BOEING.COM31_Wed, 23 Jun 2004 14:50:24 -0700314_iso-8859-1 I have a job that drops records daily based on the current date; however I dedicated a column to the date the row is inserted into the table.

are you sure that the format isn't 040420 ?? YYMMDD? if it is you would need to set up some fancy algorithms to convert the text data into date data [...] 5161 109 53_Re: coversion error when loading accounting file data7_db2sysc17_db2sysc@YAHOO.COM31_Wed, 23 Jun 2004 17:29:31 -0500464_- Hi,

From the Accounting file output, I have seggregated the general,package,bufferpool, group buffer pool and ddf records based on 9:9 postion containg (' ','P','B','G','D').

After that when I load the package data using DGOALFPK into DB2PMFACCT_PROGRAM, I get these conversion errors and some records are discarded.

Any help?







On Wed, 23 Jun 2004 08:34:20 +0200, Mertens, Bart wrote: [...] 5271 140 45_Re: DB2 on z/OS accessing data on DB2 for AIX13_David Chapman24_David.Chapman@IAG.COM.AU31_Thu, 24 Jun 2004 09:25:55 +1000561_us-ascii **************************************************************************

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.

************************************************************************** :

Martin,

From your email I assume that you cannot get the connection from DB2 for zOS to DB2 for AIX v5.2 to work. We had some similar issues trying to connect DB2 OS/390 v7 to DB2 for AIX v5.2. We could connect to the remote AIX database, but DSNTIAUL and DSNTEP2 would not run. [...] 5412 16 39_Re: Amending the Package RELEASE option33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 24 Jun 2004 02:19:55 -0500384_- Dave

I do not know, what your locking problems were, but RELEASE(DEALLOCATE) has nothing to do with row or page locking. This parameter only affects intent locks, such as table and tablespace locks and these locks are in general compatible with each other. Problems will occur, if you want to alter objects or rebind plan or packages, which are in use by these threads. [...] 5429 37 45_Re: Why can't I do this? - creating a trigger33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Thu, 24 Jun 2004 02:31:23 -0500464_- Edie

You can't code logic in your trigger body. I am afraid, but you have to create two triggers.

CREATE TRIGGER T#ADBA.COMPU2 AFTER UPDATE ON T#ADBA.TCOMPANY2 REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL WHEN O.CO_CD <> N.CO_CD INSERT INTO T#ADBA.TMQ_COMPANY2(HIST_TS, TRANS_CD, CO_CD) VALUES(CURRENT TIMESTAMP, 'I', N.CO_CD); INSERT INTO T#ADBA.TMQ_COMPANY2(HIST_TS, TRANS_CD, CO_CD) VALUES(CURRENT TIMESTAMP, 'D', O.CO_CD); END # [...] 5467 44 29_Re: Reorg with discard Option15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Thu, 24 Jun 2004 10:41:35 +0300434_us-ascii On Wed, 23 Jun 2004 17:29:57 -0400 David Nance wrote:

:> Here's one that I could use some help with.

:>DB2 V7 on Z/OS

:>Table has character column with the first 6 columns making up a date in :>the format 040220 = April 20, 2004

Cannot figure out the date. I assume you mean YYMMDD. I am surprised that people are still using two digits years, but perhaps that is just me. [...] 5512 33 29_Re: Reorg with discard Option13_Michael Ebert18_mebert@AMADEUS.NET31_Thu, 24 Jun 2004 10:03:45 +0200541_us-ascii REORG...DISCARD allows only a very limited range of expressions in the WHEN clause. Since your comparison date is fixed, you can compute it outside of the REORG step, e.g. by a simple REXX which generates the SYSIN for the REORG. You would then have something like WHEN ( < '040603' ) (assuming the current date is 040624=24th June 2004). I'm using something similar for maintaining the BMC DASD Manager Statistics database, keeping weekly data for 6 months and daily data for 1, 2 or 6 months (depending on TS size). [...] 5546 24 39_Re: Amending the Package RELEASE option12_Pete Woodman20_pete.woodman@EDS.COM31_Thu, 24 Jun 2004 03:05:08 -0500318_- Gerhard,

We are planning to change to RELEASE(COMMIT) as we have a single Plan, which can use all Packages and an EDM Pool which needs to be bigger than all our Bufferpools combined! (This was not set up by me!). It's more difficult for us to change the Plan/Package combination than the RELEASE option. [...] 5571 303 39_Re: Amending the Package RELEASE option14_Heiss, Gerhard20_Gerhard.Heiss@RUV.DE31_Thu, 24 Jun 2004 10:35:23 +0200729_us-ascii David, Your locking problems could have had different reasons since all page and row locks are always released at commit time regardless of the RELEASE option.

regards, Gerhard

-----Original Message----- From: David Nance [mailto:DWNance@FHSC.COM] Sent: Wednesday, June 23, 2004 5:27 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Amending the Package RELEASE option





Gerhard, We did try using release deallocate on a couple of CICS regions I've supported and on all occasions, it caused us locking headaches like you wouldn't believe. These were systems with heavy volume and multiples of the same transaction running concurrently. These are the transactions that really caused us [...] 5875 74 29_Re: Reorg with discard Option13_Bell, Raymond22_raymond.bell@LANDG.COM31_Thu, 24 Jun 2004 09:49:18 +0100558_iso-8859-1 Hi Michael,

Credit where it's due, my friend. I remember you knocking up that BMC Stats cleanup process a few years ago. I think we got fed up with seeing so much statistics detail for 12 months when we only needed it for the most recent few months. Made for some really busy DASD Manager TS/IX graphs! Our fault for running BMCStats daily, I guess. Anyway, you just reminded me of it and how well it worked. If you haven't already, you might want to put it on the DB2-L documents thingey, so other BMCStats users out there can nicely [...] 5950 45 39_Re: Amending the Package RELEASE option14_Heiss, Gerhard20_Gerhard.Heiss@RUV.DE31_Thu, 24 Jun 2004 10:47:08 +0200333_us-ascii Pete, We too have a single plan bound with release deallocate and a single collection for approx. 2.000 packages. I never cared about it, since we have it from the beginning and no problems occured so far. But probably it's worth some design considerations I'm courious what comes out. Please provide your experiences. [...] 5996 32 37_Vague DB2 OS/390 performance question10_Steve Lamb20_steven.lamb@CGEY.COM31_Thu, 24 Jun 2004 07:39:16 -0500590_- Dear List,

I have an extremely vague/nebulous question that a guru somewhere may be able to shed some light on, with a bit of luck.

OS/390 v2.10, DB2 OS/390 v7, TMON DB2

Recently on a production system all executing DB2 batch jobs appeared to stop - they were in a Detected Wait state according to our MVS colleagues, waiting on DB2, although we couldn't see them within DB2. A Display Thread showed no sign of them, nor did TMON. Virtually no batch jobs ran against DB2 for approx. 35 mins. although CICS transactions and DDF queries were being processed. This [...] 6029 71 41_Re: Vague DB2 OS/390 performance question14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 24 Jun 2004 13:48:09 +0100502_iso-8859-1 Hi Steve

You hadn't hit the maximum number of connections to DB2 by any chance?

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: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Steve Lamb Sent: 24 June 2004 13:39 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Vague DB2 OS/390 performance question [...] 6101 46 41_Re: Vague DB2 OS/390 performance question14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Thu, 24 Jun 2004 09:00:09 -0400291_us-ascii One of the best bets for resolving this sort of problem is to take an SVC dump of MSTR, DBM1, IRLM, DIST address spaces while the problem is occuring, before making any attempt to correct it your self. Then the documention can be shipped (FTPed) off to the vendor for analysis [...] 6148 24 41_Re: Vague DB2 OS/390 performance question10_Steve Lamb20_steven.lamb@CGEY.COM31_Thu, 24 Jun 2004 07:59:53 -0500512_- Phil,

We had thought of that but we don't know for sure - wouldn't you get a message out indicating this? The batch jobs sat there waiting for 35 mins or so, rather than abending. When we went from v5 to v7 last year we had a couple of problems with utility jobs failing because of this (multiple subtasks etc.) - we got the message DSNU028I DSNUTILB - UNABLE TO CONNECT TO DB2, REASON=X'00F30055' We increased IDBACK and CTHREAD from 40 to 100 and 250 to 300 respectively and it all worked again. [...] 6173 70 41_Decrease active log size while DB2 is up?13_Michael Ebert18_mebert@AMADEUS.NET31_Thu, 24 Jun 2004 15:05:18 +0200502_us-ascii Hi List,

I want to slightly decrease the size of active logs for harmonising the setup of all our subsystems. Can I do this while DB2 is up? I've thought of something like this:

1. Determine which log is the current one via -DIS LOG 2. Delete & redefine the other ones 3. Do a log switch via -ARCH LOG 4. Delete/redefine the remaining one after the archiver has finished offloading 5. Do some more log switches to prevent DB2 from trying to re-access the now empty datasets [...] 6244 160 41_Re: Vague DB2 OS/390 performance question13_Michael Ebert18_mebert@AMADEUS.NET31_Thu, 24 Jun 2004 15:15:14 +0200520_us-ascii Hi Steve,

here too I've occasionally observed similar problems with jobs in DW (usually mine...). When I asked the MVS guys, they used RMF and some MVS commands to investigate the problem (I don't remember which ones). Normally it turned out that all the waiting jobs were waiting for a disk which had an exclusive enqueue by some other job. However I could never get an explanation why this other job needed this exclusive access... or even why the waiting jobs wanted to access this disk as well. [...] 6405 202 45_Re: Decrease active log size while DB2 is up?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 24 Jun 2004 14:18:19 +0100348_iso-8859-1 Nope

DB2 has all the logs allocated and there's nothing you can do without shutting DB2 down.

On the other hand......

If you have ANY of the CA DB2 tools installed AND you are at the P01 release level, then you have access to a Value Pack item that allows you to delete and add active logs whilst Db2 is active [...] 6608 95 45_Re: Decrease active log size while DB2 is up?0_17_sjvagnier@AEP.COM31_Thu, 24 Jun 2004 09:24:32 -0400447_us-ascii Michael, you can resize active logs while DB2 is up with a product from BMC called "Opertune". I have resized active logs several times with this product with no problems. I ran into the same problem you mentioned of MSTR having allocated all the active logs when trying to resize without the Opertune product. I don't know of any other OEM tool on the market besides BMC's that will allow you to resize active logs while DB2 is up. [...] 6704 73 41_Re: Vague DB2 OS/390 performance question11_Prasad Mani22_Prasad_Mani@SATYAM.COM31_Thu, 24 Jun 2004 19:03:48 +0530380_iso-8859-1 The most probable reason that I could guess is I/O delay. If it had been busy CPU it would hae caused the entire system down.But an High I/O wait state coupled with SRM delay could very easily make the DB2 system down.Please be sure that none of the Central & Expanaded Storage parameters in DB2 (like DB2M1)are over specified than the available CPU memory limit. [...] 6778 27 41_Re: Vague DB2 OS/390 performance question10_Steve lamb20_steven.lamb@CGEY.COM31_Thu, 24 Jun 2004 08:57:43 -0500517_- It didn't seem to be a CPU problem, in that TSO and CICS were running OK and DB2 itself was actually working. The batch jobs normally connect at a rate of 3 or 4 a second during this period. They stopped about 2 seconds after the DDF thread started and started again the same second that the DDF thread was cancelled, which is what makes us think that it is related to the DDF query. As Prasad says, it could have been IO-related. I don't know about the memory side of things - I'll have to check on that. The [...] 6806 92 30_Non-Updatable columns--DB2 V7.13_Michalik, Ken19_kmichalik@KRAFT.COM31_Thu, 24 Jun 2004 09:14:16 -0500314_iso-8859-1 Dear list:

My reference tells me that a column is non-updatable if it is derived somehow, or is or is based on a rowid column. DDL for the table seems to say the column is just a native decimal type. It is a column of a partitioning index, but I don't believe this should cause the problem. [...] 6899 122 29_Re: Reorg with discard Option17_Kornblum, Kenneth24_Kenneth_Kornblum@BMC.COM31_Thu, 24 Jun 2004 09:34:58 -0500410_iso-8859-1 Hello Raymond and Micheal (et al!),

With regards to cleaning out the BMC DASD Manager tables:

It sounds like you may be doing something more complicated based upon whether the stats were collected for Daily, Weekly, etc. purposes.

But, I just wanted to be sure you knew of the 'DELETEAGE' option. The DELETEAGE option cleans up rows older than the age (in days) specified. [...] 7022 196 34_Re: Non-Updatable columns--DB2 V7.14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 24 Jun 2004 15:35:39 +0100381_iso-8859-1 Ken

There is a zparm parameter that controls whether partitioning key columns are updateable or not. I suspect that the default is "not".

Also, if this is an "old" partitioned tablespace (from V4 or earlier) then the partitioning key can never be made updateable without dropping and recreating the table (although V8 may relax this last restriction) [...] 7219 68 70_Problem calling a z/OS Stored Procedure With DB2 Connect V8 (CLI0112E)0_20_simon.george@UBS.COM31_Thu, 24 Jun 2004 15:38:37 +0100395_iso-8859-1 Hello list, if anyone has any words of advice on this problem I'd be most grateful as its driving me even more nuts than I already was.

The basic setup is as follows

XP JAVA JDBC Application Calling a DB2 Stored Procedure on z/OS. On XP we are Running DB2 Connect V8.1 FP5 (on both client and gateway). On z/OS its DB2 V7.1. The JDBC driver is a type-2 connection. [...] 7288 139 45_Re: Decrease active log size while DB2 is up?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 24 Jun 2004 15:42:29 +0100354_iso-8859-1 Hmm

Looks like the CA "stealth marketing" is working just fine (again - see my other post on this subject). I guess we may just have to start shouting louder and LOUDER!

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 [...] 7428 17 41_Re: Vague DB2 OS/390 performance question10_Steve lamb20_steven.lamb@CGEY.COM31_Thu, 24 Jun 2004 09:47:11 -0500291_- Anyway, I reckon it's really either a CICS or an MVS problem. The CICS and MVS guys always say it's nothing to do with them and it has to be a DB2 problem. Thing is, they're in a different building to us and I reckon they gang up on us DBA's as we're not there to defend ourselves :) [...] 7446 55 41_Re: Vague DB2 OS/390 performance question11_Prasad Mani22_Prasad_Mani@SATYAM.COM31_Thu, 24 Jun 2004 20:27:16 +0530514_iso-8859-1 Never to take the dump for later investigation.It really helps to solve the problem atleast to avoid future occurence.

With best Regards, Prasad Mani _______________________________ DB2/Mainframe Performance Analyst, Work : +91 80 2235 3333 Extn : 3726 Mobile: +91 98802 37200





-----Original Message----- From: Steve lamb [mailto:steven.lamb@CGEY.COM] Sent: Thursday, June 24, 2004 8:17 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Vague DB2 OS/390 performance question [...] 7502 91 41_Re: Vague DB2 OS/390 performance question11_Prasad Mani22_Prasad_Mani@SATYAM.COM31_Thu, 24 Jun 2004 20:29:02 +0530443_iso-8859-1 There was a TYPO .....Am rephrasing it ... it is never forget to take dump .

With best Regards, Prasad Mani _______________________________ DB2/Mainframe Performance Analyst, Work : +91 80 2235 3333 Extn : 3726 Mobile: +91 98802 37200





-----Original Message----- From: Prasad_Mani Sent: Thursday, June 24, 2004 8:27 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Vague DB2 OS/390 performance question [...] 7594 183 45_Re: Decrease active log size while DB2 is up?0_17_sjvagnier@AEP.COM31_Thu, 24 Jun 2004 10:57:38 -0400451_us-ascii Phil, no need to SHOUT.

I did not see your response to Michael's question before I replied. Therefore, after reading your reply there are at least two OEM tools that can perform the resizing while DB2 is up.



**************************************** Steve Vagnier American Electric Power Database Administration One Riverside Plaza Columbus, Ohio 43215 Email: sjvagnier@aep.com Phone: 614-716-3677 Audinet: 200-3677 [...] 7778 151 29_Re: Reorg with discard Option13_Bell, Raymond22_raymond.bell@LANDG.COM31_Thu, 24 Jun 2004 16:08:52 +0100530_iso-8859-1 Kenneth,

What the Good Doktor did (and correct me if my memory is fading, Michael) is to delete rows from the BMC history tables that were older than 6 months or so, delete 6/7th of the history rows between 6 months old and a month old (i.e. leave one days' stats each week for between 1 and 6 months ago) and leave the daily stats for the current month. Or something like that. Worked very well. Don't remember if we reorged the TSs with the handy deleteage option - quite possibly, but it was awhile ago. [...] 7930 43 29_Re: Reorg with discard Option13_Michael Ebert18_mebert@AMADEUS.NET31_Thu, 24 Jun 2004 17:33:14 +0200516_us-ascii Hi Mr. B,

your memory is excellent! In our case, the DASD Mgr data collection runs every night, collecting a huge amount of data. So we retain it like this: All data older than half a year is deleted. Data that is older than one month is kept only if it is for a Wednesday, i.e. one day per week is kept. Up to one month, all data is kept. This gives us a detailed overview over the past month and a good trend over the past 6 months but requires only about 30% of the space compared to keeping [...] 7974 25 20_JDBC & Type 4 Driver11_Tamea Smith22_tamea.smith@NCMAIL.NET31_Thu, 24 Jun 2004 11:39:15 -0400564_ISO-8859-1 We are using DB2 v7.1 RSU0307 with z/OS 1.4 RSU0312+ and we have a client that is trying to use Websphere 5.1, Windows 2003 and IBM Type 4 Universal JDBC Driver. He is having problems with the "SQLException" not being translated. I have done some research and it appears that perhaps we, DB2 V7.1, need to have some additional ptf's applied as well as creating new SMP/E target libraries and new HFS directories. I am new to this and was wondering if anyone has done this? If so were there any "gotcha's" and more importantly did it really work? Thanks. 8000 65 24_Re: JDBC & Type 4 Driver15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Thu, 24 Jun 2004 17:52:22 +0200675_iso-8859-1 It just doesn't work properly with DB2 v7. You 'll have to upgrade to v8 is what I understood.

Regards,

Jaap

-----Oorspronkelijk bericht----- Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]namens Tamea Smith Verzonden: donderdag 24 juni 2004 17:39 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: JDBC & Type 4 Driver



We are using DB2 v7.1 RSU0307 with z/OS 1.4 RSU0312+ and we have a client that is trying to use Websphere 5.1, Windows 2003 and IBM Type 4 Universal JDBC Driver. He is having problems with the "SQLException" not being translated. I have done some research and it appears that perhaps we, DB2 V7.1, [...] 8066 146 41_Re: Vague DB2 OS/390 performance question15_Andersen, Cindy18_candersen@FNNI.COM31_Thu, 24 Jun 2004 11:08:22 -0500440_iso-8859-1 I've seen DB2 "stop" for update transactions and keep going for query transactions when there was a problem off-loading the DB2 active logs to the DB2 archive logs. If all DB2 active logs are full and have not been off-loaded yet, DB2 holds all update transactions until it can log again. I've seen it occur when the archives went on tape and there was a problem with the tape drives preventing the off-load from occurring. [...] 8213 38 18_urgent help needed3_lan18_lxw176@HOTMAIL.COM31_Thu, 24 Jun 2004 11:24:15 -0500614_ISO-8859-1 Hi,

We have a database set up already with tables using three different DMS tablespace. However, the size of all the three tablespace seems too small. We did an online backup of the database first. In order to increase the tablespace size, my co-worker tried a wrong way. He just deleted the three DMS tablespaces, then, he recreated them using the same name. The original tables that using the old tablespace in the database seems disappear, and we get an error when we tried to connect to the database: SQL30081N A communication error has been detected. Communication protocol being used: [...] 8252 74 22_Re: urgent help needed14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Thu, 24 Jun 2004 12:46:09 -0400392_- From your 2nd error message, it looks like you have already run a restore of the database. Since your database is enabled for roll-forward recovery (LOGRETAIN parameter = RECOVERY), you'd now need to issue a roll-forward command, BUT upto the point in time BEFORE the tablespaces were dropped. Time can be local time using "USING LOCAL TIME" option, otherwise, time would be GMT time. [...] 8327 18 41_Why can't I do this? - creating a trigger0_27_mamccormack@STATESTREET.COM31_Thu, 24 Jun 2004 12:50:13 -0400656_us-ascii Edie,

It has been a while since I wrestled with this. I think I remember that you can use a CASE expression in a trigger but you cannot use a CASE statement.

Mark

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 8346 248 50_Re: UDB V8.1 type 2 index conversion- the solution12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 24 Jun 2004 10:00:14 -0700353_us-ascii And the answer is:

DB2_RR_TO_RS=YES is no longer valid after V7 FP8 or V8 FP1. The variable must now be set as db2set DB2_RR_TO_RS=YES_OVERRIDE_RI There are some caveats to using this if you have db2 Referential Integrity...you could strand child rows. DB2 will still accept the just "YES" value, it just won't do anything with it. [...] 8595 227 25_DB2 V8 Development Center16_Haverchak, Tanya36_Tanya.Haverchak@ANTARESSOLUTIONS.COM31_Thu, 24 Jun 2004 13:44:37 -0400608_us-ascii Hello list,

Has anyone implemented "DB2 V8 Development Center "(former stored procedure builder) to work with DB2 V7 for z/OS?

If so, can anyone tell me, what DB2 authorities need to be granted to developers to enable them to build stored procedures?

I got it to work with my user ID with no problems. But of course, my user ID has SYSADAM authority. When programmer attempts to do anything in the Development Center, his session just hangs. On the mainframe, TMON DB2 shows that programmer user ID is looking for authorization of SYSADM, which he of course does not have. [...] 8823 30 22_Re: urgent help needed3_lan18_lxw176@HOTMAIL.COM31_Thu, 24 Jun 2004 13:06:33 -0500634_ISO-8859-1 Hi Abhijit, Thanks very much for your quick reply. Yes, my co-worker might already run a restore of the database. I can’t get confirmed at this point. We have few backup files for the database, I’m not sure which one he used to restore. Do you think it will matter? I only know the approximate time when my-coworker changed the tablespaces (around 06-23-17.00). However, I tried the two db2 command, with the second one: db2 "rollforward db javadev to 2004-06-23-15.00.00.000000 and stop", I got the message: “SQL1275N The stoptime passed to roll-forward must be greater than or equal to "2004-06-23-20.44.41.000000", [...] 8854 25 56_Can a trigger retrieve program name of triggering event?8_Jim Hunt27_jhunt@GENERALIUSALIFERE.COM31_Thu, 24 Jun 2004 12:57:27 -0500549_- We're a new DB2 shop running DB2 v.7 for OS/390, so please forgive me if this is a ridiculous question.

I'm developing some "before update" and "before insert" triggers that will automate some transaction audits that we typically perform. The triggers are easily able to retrieve information such as current timestamp and the user-id from the system registers CURRENT_TIMESTAMP and USER. But is there also a way for a trigger to retrieve the name of the program containing the triggering SQL statement that causes the trigger to fire? [...] 8880 15 41_Re: Vague DB2 OS/390 performance question4_Mike31_michael.brennan@I-STRUCTURE.COM31_Thu, 24 Jun 2004 14:58:03 -0500731_- Steve, Sounds to me like your batch jobs have a lower dispatching priority than DDF. This may be a performance tuning opportunity. Since you did a -display thread and none of the batch jobs were connected to DB2, I don't think it's a problem within DB2. Mike

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 8896 20 34_Incorrect Object States at DR site7_Karthik18_vkarthik@YAHOO.COM31_Thu, 24 Jun 2004 15:06:06 -0500410_- Our DR environment consists of mirrored DASD (EMC SRDF)

During DR exercise, we found some objects were showing inconsistent state, Objects showing up in UTUT state, but '-dis util' doesn't show up any utility. We verified this in the primary site, that there was no utility on those objects when the mirror links were broken. And the CF structures were cleared before bringing up DB2 at DR site. [...] 8917 52 22_Re: urgent help needed14_Sinha, Abhijit20_Abhijit.Sinha@GS.COM31_Thu, 24 Jun 2004 16:25:26 -0400380_- First, please make sure you are using the correct backup file(s) taken before the tbsp drops, to restore. If you are not sure as to what has already been restored by your co-worker, it's better to do it again. Next, in your rollforward command, since you are not using "LOCAL TIME", the time would be in GMT, so please calculate appropriately according to your local time. [...] 8970 41 24_Re: JDBC & Type 4 Driver33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Thu, 24 Jun 2004 23:00:53 +0200354_iso-8859-1 PQ80841 introduced a new JDBC driver for V7. I don't know how you handle installation. But we use the same tartget lib at all, we just copy the target libs to a new set of runtime libaries.

Check also PQ90346 for CICS (still open) to reflect the new JDBC driver settings for CICS. Seems a lot of work because of RSU0307. Have fun. [...] 9012 35 22_Re: urgent help needed3_lan18_lxw176@HOTMAIL.COM31_Thu, 24 Jun 2004 16:02:10 -0500677_ISO-8859-1 Hi Abhijit, Sorry to bother you again. The timestamp of tablespace change is 20040623165051, so I issued the command: $ db2 "rollforward db javadev to 2004-06-23-20.00.00.000000 and stop" , but still got the same error:” SQL1275N The stoptime passed to roll-forward must be greater than or equal to "2004-06-23-20.44.41.000000", because database "JAVADEV" on node (s) "0" contains information later than the specified time. It seems it doesn’t allow me to rollforward to a point of time prior to tablespace change. Meanwhile, I try another command: $ restore database javadev from "/db2/backup/" taken at 20040623132656 into newjavadev, here newjavadev was not [...] 9048 34 41_Re: Vague DB2 OS/390 performance question17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Thu, 24 Jun 2004 16:14:39 -0500562_- It is virtually impossible to tell without looking at the performance numbers when the problem happened.

Next time when it happens, look at RMF and find out what the primary reason for the batch delay is. You can use JOBS menu to locate the primary delay reason. You can't find any other better place to look at where the problem is. If it is I/O then you will know which volume the delay is coming from. However I don't think you would have jobs virtually hanging due to a I/O delay unless something went gloriously wrong. You should have noticed [...] 9083 24 22_CICS Version 3 and DB215_myles parkinson33_myles_parkinson@ADMIN.STATE.AK.US31_Thu, 24 Jun 2004 13:17:46 -0800512_us-ascii Hello, I understand CICS Version 3 is coming out 4th Quarter of 2004. Has anyone heard any rumors if the L8 TCB dispatching is going to be improved (cpu consumption lessened). I am aware of the performance PTF's currently available. Any comments on DB2 Version 8 real memory consumption as opposed to Version 7, likewise for running any version of DB2(7 or 8)under OS/390 2.10 versus Z/OS. I have heard that maybe CPU doesn't change a lot but that real memory consumption has gone up in all cases. [...] 9108 41 26_Re: CICS Version 3 and DB233_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Thu, 24 Jun 2004 23:57:44 +0200343_iso-8859-1 There is no announcement about any new version of CICS. I believe an ESP beta will be available end of this year. The nomination for CICS ESP is over and we'll join. Ask your local IBM folks for further info about ESP.

Wait for IBM announcement to schedule your projects or upgrade to CTS (CICS Transaction Server) 2.3. [...] 9150 35 41_Re: Vague DB2 OS/390 performance question7_Karthik18_vkarthik@YAHOO.COM31_Thu, 24 Jun 2004 21:56:43 -0500380_- Steve, Did you see what the 'D GRS' output looked like while this was happening? Enqs on qname=SYSZDSN3 are the ones worth investigating.

you may also want to take a look at info apar dealing with db2 hang/wait.

may be your mvs/cics guys also feel similarly about you... ;-)

On Thu, 24 Jun 2004 09:47:11 -0500, Steve lamb wrote: [...] 9186 62 22_Re: urgent help needed12_Travis, John25_john.travis@CAPGEMINI.COM31_Fri, 25 Jun 2004 08:54:01 +0100426_- Hi Abhijit, Try restoring your database again from a copy taken before the tablespaces were dropped. use the replace existing to get the underlying datasets recreated, and then roll forward to just before the tablespaces were dropped. The reply from db2 is saying that you currently have log records applied up to 20.44.41 so you need to restore to last copy taken before 16.50.51 and then roll forward to 16.45.00ish. [...] 9249 28 41_Re: Vague DB2 OS/390 performance question10_Steve lamb20_steven.lamb@CGEY.COM31_Fri, 25 Jun 2004 04:04:36 -0500372_- Unfortunately RMF3 isn't set up on this LPAR - if I try and look at Job Delays, I get a message saying the Data Gatherer is not active. Can you get the same info easily from TMON/MVS?

The DDF queries are all read-only and the batch jobs do updates, but having looked at the DB2 log and SYSLOG, there weren't any log switches at the time the problem occured. [...] 9278 311 34_Re: Non-Updatable columns--DB2 V7.13_Fung, Chi-Yun23_Chi-Yun.Fung@UNISYS.COM31_Fri, 25 Jun 2004 06:33:47 -0600647_iso-8859-1 Ken,

Do you have PARTKEYU=NO in your Zparm?

From installation guide:

12. UPDATE PART KEY COLS













Acceptable values: YES, NO, or SAME

Default: YES

Update: option 18 on panel DSNTIPB

DSNZPxxx: DSN6SPRM PARTKEYU



















Specify whether values in columns that participate in partitioning keys may be updated. The acceptable values mean the following:



YES

Values in columns that participate in partitioning keys may be updated. This is the default. [...] 9590 93 53_Re: July 2004 meeting of The Seattle DB2 User's Group15_Tillman, Paul A25_paul.a.tillman@BOEING.COM31_Fri, 25 Jun 2004 05:46:46 -0700722_us-ascii Hello Barbara, Please include me on your list of attendees.

Thanks

-----Original Message----- From: Lidstrom, Barbara Sent: Thursday, June 17, 2004 7:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: July 2004 meeting of The Seattle DB2 User's Group



DB2 Enthusiasts Invited!



Join us Tuesday, July 13, 8:30 - 12:00 for technical insight into DB2 application programming.

We are pleased to have Bill Bireley present the recent enhancements to the DB2 Universal Java Driver and DB2 (both z/OS, and Linux, UNIX, Windows) that enable customers to build highly robust and reliable Java applications. Focus will be given to the advanced system management capabilities of [...] 9684 20 22_Re: urgent help needed3_lan18_lxw176@HOTMAIL.COM31_Fri, 25 Jun 2004 08:25:36 -0500456_- Hi John, Thanks for your reply. I tried to restore my database from a copy taken before the tablespaces were dropped with command: db2 restore database javadev from "/db2/backup/" taken at 20040623132656 replace existing, then issued: db2 "rollforward db javadev to 2004-06-23-18.00.00.000000 and stop", I got a message: SQL1004C There is not enough storage on the file system to process the command. Do you know what may be the reason? Thanks! Lan [...] 9705 32 40_Bank One Adding Another DB2 Systems Guru17_Melissa Greenwood31_melissa_j_greenwood@BANKONE.COM31_Fri, 25 Jun 2004 08:45:17 -0500462_- List Members:

Due to the large number of exciting upcoming projects, Bank One Corporation is looking to add yet another senior-level, full-function DB2 s/390 or z/OS systems programmer to our talented Mainframe team.

The ideal candidate has a thorough knowledge of SMP/E and JES2, and has a strong background with SQL, stored procedures, SQLJ, UDB and WebSphere. Experience with DB2 datasharing environments is also important for this role. [...] 9738 13 44_Re: Bank One Adding Another DB2 Systems Guru0_16_mscarpa@CESVE.IT31_Fri, 25 Jun 2004 16:09:33 +0200486_US-ASCII :-**((

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 9752 237 41_Re: Vague DB2 OS/390 performance question19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Fri, 25 Jun 2004 09:19:24 -0500351_us-ascii

I agree Cindy, filling up all the active logs allows all read processes to continue working and all update processes to stop. If CPU wasn't the problem, I bet this could be the problem.

The good thing about this is you can check SYSLOG after the fact to see if all your active logs were filled up during this time period. [...] 9990 60 44_Re: Bank One Adding Another DB2 Systems Guru15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Fri, 25 Jun 2004 10:06:52 -0500447_iso-8859-1 Just out of idle interest, what is happening to the IT departments in the BANKONE / J.P. Morgan Chase merger?

Chris Blaicher BMC Software, Inc. Austin Development Labs Austin, Texas 512-340-6154





-----Original Message----- From: Melissa Greenwood [mailto:melissa_j_greenwood@BANKONE.COM] Sent: Friday, June 25, 2004 8:45 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Bank One Adding Another DB2 Systems Guru [...] 10051 149 34_Re: Non-Updatable columns--DB2 V7.10_Mark Labby21_mlabby@AESSUCCESS.ORG31_Fri, 25 Jun 2004 11:24:40 -0400557_iso-8859-1 You are right Phil, we have some extremely large partitioned tablespaces that were created years ago and they are not updatable. When IBM changed DB2 to allow partitioned key columns to be updated, they never provided a way to change the syscolumns value for existing tables. (Other than the suggestion to drop a half billion row table in production and recreate it, which we politely explained was not practical.) I spoke briefly with Roger Miller about this at last years IDUG and he indicated that in v8, this will no longer be an issue. [...] 10201 213 44_Re: Bank One Adding Another DB2 Systems Guru17_Melissa Greenwood31_melissa_j_greenwood@BANKONE.COM31_Fri, 25 Jun 2004 11:38:10 -0400353_us-ascii Chris, that is a great question, but one that requires a response that is far too lengthy and complex to attempt via the listserv!

If you are genuinely interested, I would be happy to discuss with you off-line the specifics within the context of this particular group.

Please feel free to contact me directly (614-213-6670). [...] 10415 35 39_SQL/PL debugging and error/log handling15_Joseph V Basile17_jbasile@ARROW.COM31_Fri, 25 Jun 2004 11:22:35 -0500531_- We are running DB2 v7 on z/OS. We are trying to find out if any other companies are running SQL stored procedures on z/os and how they have approached the debugging and error/log handling aspects. Below are some of the questions I have.

1) Is anyone currently using a tool or method to debug SQL stored procedures that are running in DB2 v7 z/OS? We contacted IBM and they say that the Stored Procedure Builder (now known as Developement Center in v8) debugging piece will only work when you have migrated to DB2 v8. [...] 10451 190 43_Re: SQL/PL debugging and error/log handling13_Rhea, Matthew29_Matthew.Rhea@INFARMBUREAU.COM31_Fri, 25 Jun 2004 12:43:00 -0500605_iso-8859-1 At a former company I worked for we wanted to use Compuware's Xpediter for Stored Procedures application. Compuware demonstrated the product using one of our own stored procs, and it was great. Unfortunately, our company never approved purchasing it. Instead we had to rely on DISPLAY statements that were written out to the DB2SPAS. We could control whether or not to display the messages by either passing a parm or using a lookup table to retrieve a value that determined if the program should display the error message, so that the DB2SPAS would not fill up with unnecessary messages. [...] 10642 21 47_Attention List Owners - db2-l-documents problem10_Bill Brown21_db2_dba@BELLSOUTH.NET31_Fri, 25 Jun 2004 13:10:44 -0500545_- i have tried on several occasions to view and/or download "stuff" from db2-l-documents and all i get is the message:

The archive files could not be accessed, either because the list does not have Web-accessible archives or because they are being updated. If you know that the list has Web archives, please try again in about 30 seconds, and report the problem if it persists for more than a few minutes. The file that could not be opened is 'c:\Inetpub\WWWROOT\ARCHIVES\DB2-L\upload\2764538342575962.tmp' and the error code was 2. [...] 10664 74 51_Re: Attention List Owners - db2-l-documents problem0_17_sjvagnier@AEP.COM31_Fri, 25 Jun 2004 14:20:31 -0400562_us-ascii Bill, I'm getting the same error message. I'll send an email to one of the listserv administrators.

Regards,

**************************************** Steve Vagnier American Electric Power Database Administration One Riverside Plaza Columbus, Ohio 43215 Email: sjvagnier@aep.com Phone: 614-716-3677 Audinet: 200-3677







Bill Brown cc: Sent by: DB2 Data Subject: Attention List Owners - db2-l-documents problem Base Discussion List [...] 10739 18 31_how to increase tablespace size3_lan18_lxw176@HOTMAIL.COM31_Fri, 25 Jun 2004 14:41:42 -0500722_- Hi,

How to increase the size of table space in an existing database without influencing other objects? Because the tablespaces we have now are not big enough for later development. Detailed instructions will be appreciated!

Thanks! Lan

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 10758 68 29_FW: Timestamp - Timestamp = ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Fri, 25 Jun 2004 16:10:04 -0400733_iso-8859-1 Dear Listers...

We are trying to subtract a timestamp from a timestamp in COBOL (...Db2V7 / OS390). What Cobol variable declaration to we have to declare to receive the results.

Any help would be appreciated.

- Ray Gaston (Con Ed)

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 10827 75 33_Re: FW: Timestamp - Timestamp = ?14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM31_Fri, 25 Jun 2004 16:19:02 -0400698_us-ascii DB2 provides a utility to generate COBOL layouts for tables, views including the lay out of individual columns. It is called DCLGEN. One way to access it is from option 2 of DB2I.

"Gaston, Raymond" wrote:

> Dear Listers...We are trying to subtract a timestamp from a timestamp > in COBOL (...Db2V7 / OS390). What Cobol variable declaration to we > have to declare to receive the results.Any help would be appreciated.- > Ray Gaston (Con > Ed)--------------------------------------------------------------------------------- > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and > home page at http://www.idugdb2-l.org/archives/db2-l.html. From that > [...] 10903 158 33_Re: FW: Timestamp - Timestamp = ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Fri, 25 Jun 2004 16:25:54 -0400455_iso-8859-1 Thanks for your reply. We are aware of dclgns. The problem is that we want to "select a timestamp - timestamp into a host variable". How do you define the host variable? We are getting truncations!



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Avram Friedman Sent: Friday, June 25, 2004 4:19 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: FW: Timestamp - Timestamp = ? [...] 11062 113 33_Re: FW: Timestamp - Timestamp = ?0_22_sally.mir@WACHOVIA.COM31_Fri, 25 Jun 2004 16:27:36 -0400608_US-ASCII From the SQL Reference:



Subtracting timestamps: The result of subtracting one timestamp (TS2) from another (TS1) is a timestamp duration that specifies the number of years, months, days, hours, minutes, seconds, and microseconds between the two timestamps. The data type of the result is DECIMAL(20,6). If TS1 is greater than or equal to TS2, TS2 is subtracted from TS1. If TS1 is less than TS2, however, TS1 is subtracted from TS2 and the sign of the result is made negative. The following procedural description clarifies the steps involved in the operation RESULT = TS1 -TS2. [...] 11176 37 29_Re: Timestamp - Timestamp = ?9_Sam Baugh24_Sam.Baugh@MOTION-IND.COM31_Fri, 25 Jun 2004 15:24:55 -0500540_iso-8859-1 see 'Datetime operations an PIC S9(15)V9(6) for DECIMAL(20,6)

see 'Datetime operations and durations' in the SQL Reference

-----Original Message----- From: Gaston, Raymond [mailto:GastonRay@ORU.COM] Sent: Friday, June 25, 2004 3:10 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: FW: Timestamp - Timestamp = ?





Dear Listers...

We are trying to subtract a timestamp from a timestamp in COBOL (...Db2V7 / OS390). What Cobol variable declaration to we have to declare to receive the results. [...] 11214 334 33_Re: FW: Timestamp - Timestamp = ?10_Price, Ray18_Ray.Price@DRKW.COM31_Fri, 25 Jun 2004 21:30:12 +0100475_- Ray,





According to the SQL manual "The result of subtracting one timestamp (TS2) from another (TS1) is a timestamp duration that specifies the number of years, months, days, hours, minutes, seconds, and microseconds between the two timestamps. The data type of the result is DECIMAL(20,6)."





Have you used decimal(20,6)?





Regards,





Ray Price

Dresdner Kleinwort Wasserstein [...] 11549 446 33_Re: FW: Timestamp - Timestamp = ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Fri, 25 Jun 2004 16:37:21 -0400685_iso-8859-1 The maximum size that you can have in COBOL is 18. This is the problem !!!! We can't use DECIMAL(20,6).

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Price, Ray Sent: Friday, June 25, 2004 4:30 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: FW: Timestamp - Timestamp = ?





Ray,





According to the SQL manual "The result of subtracting one timestamp (TS2) from another (TS1) is a timestamp duration that specifies the number of years, months, days, hours, minutes, seconds, and microseconds between the two timestamps. The data type of the result is DECIMAL(20,6)." [...] 11996 470 33_Re: FW: Timestamp - Timestamp = ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Fri, 25 Jun 2004 16:40:06 -0400411_iso-8859-1 Listers...

Thanks again for all the suggestions! We may end up using one of SQL "functions" on the 2 timestamps to be subtracted.

- Ray





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Price, Ray Sent: Friday, June 25, 2004 4:30 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: FW: Timestamp - Timestamp = ? [...] 12467 386 33_Re: FW: Timestamp - Timestamp = ?10_Price, Ray18_Ray.Price@DRKW.COM31_Fri, 25 Jun 2004 21:46:21 +0100608_- It depends which COBOL. Enterprise COBOL (we have V3.2) supports 31 digits if you use ARITH(EXTEND).









Regards,





Ray Price

Dresdner Kleinwort Wasserstein

London





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gaston, Raymond Sent: 25 June 2004 21:37 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: FW: Timestamp - Timestamp = ?





The maximum size that you can have in COBOL is 18. This is the problem !!!! We can't use DECIMAL(20,6). [...] 12854 434 33_Re: FW: Timestamp - Timestamp = ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Fri, 25 Jun 2004 16:51:58 -0400404_iso-8859-1 Thanks again Ray.

Unfortunately we have COBOL LE at this shop! But it would be nice to have those Enterprise "31 digits" available.

- Ray Gaston

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Price, Ray Sent: Friday, June 25, 2004 4:46 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: FW: Timestamp - Timestamp = ? [...] 13289 147 33_Re: FW: Timestamp - Timestamp = ?0_22_sally.mir@WACHOVIA.COM31_Fri, 25 Jun 2004 17:09:53 -0400422_US-ASCII You COULD convert it to CHAR and parse the result.

SELECT CHAR(TS1 - TS2) into :HV



Sally A. Mir, AVP IBM Certified Database Administrator DB2 Universal Database V8.1 for Z/OS (336) 773-4011





"Gaston, Raymond" To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List Re: FW: Timestamp - Timestamp = ? [...] 13437 35 35_Re: how to increase tablespace size33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Fri, 25 Jun 2004 23:20:36 +0200570_iso-8859-1 Hmhh..

Alter PRIQTY/SECQTY and invoke a REORG utility.

Roland

-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von lan Gesendet: Freitag, 25. Juni 2004 21:42 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: how to increase tablespace size



Hi,

How to increase the size of table space in an existing database without influencing other objects? Because the tablespaces we have now are not big enough for later development. Detailed instructions will be appreciated! [...] 13473 190 33_AW: FW: Timestamp - Timestamp = ?33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Fri, 25 Jun 2004 23:29:15 +0200350_iso-8859-1 Ray, there is no COBOL LE!! Post the header of your Cobol compile listing and I'll tell you the Cobol Version

Like (first LE enabled Cobol compiler) PP 5688-197 IBM SAA AD/Cycle COBOL/370 1.1.1 or PP 5648-A25 IBM COBOL for OS/390 & VM 2.2.1 or (Enterprise Cobol V3.2) PP 5655-G53 IBM Enterprise COBOL for z/OS and OS/390 3.2.0 [...] 13664 69 44_Antwort: Re: how to increase tablespace size0_19_dirk.pohl@SERCON.DE31_Sat, 26 Jun 2004 01:18:23 +0200643_ISO-8859-1 Hi Lan

and if you are on unix , as I suppose , try "alter tablespace extend "

hth dirk

Am 25.06.2004 23:20:36 schrieb DB2 Data Base Discussion List: > Hmhh.. > > Alter PRIQTY/SECQTY and invoke a REORG utility. > > Roland > > -----Ursprüngliche Nachricht----- > Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von > lan > Gesendet: Freitag, 25. Juni 2004 21:42 > An: DB2-L@WWW.IDUGDB2-L.ORG > Betreff: how to increase tablespace size > > > Hi, > > How to increase the size of table space in an existing database without > influencing [...] 13734 50 44_Re: Bank One Adding Another DB2 Systems Guru0_17_JTonchick@AOL.COM29_Fri, 25 Jun 2004 19:19:05 EDT305_US-ASCII Well, Bank One has been in an In-sourcing trend, taking control back from a previous outsource provider. But, those of us in the Chicago area couldn't take advantage of the openings since most of them were in Ohio. It was interesting that this latest posting mentioned both Illinois & Ohio. [...] 13785 30 28_Quiesce Abend Intermittently11_Akay Sharma18_aomprakash@CSC.COM31_Fri, 25 Jun 2004 20:26:28 -0500371_- We are running backup nightly after performing quiesce to the database. The problem is sometimes the quiesce abend, hence backup were not run due to quiesce failure.The error message is as below

-DB2P DSNUGASU - THERE IS A RUNNING EXCLUSIVELY UTILITY NOT WITH COMPATIBLE OTHER ACTIVE UTILITIES. DSNUBAG - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8 [...] 13816 86 74_Re: Problem calling a z/OS Stored Procedure With DB2 Connect V8 (CLI0112E)7_Karthik18_vkarthik@YAHOO.COM31_Fri, 25 Jun 2004 20:41:18 -0500426_- *guessing*, this could be unicode issue, like using utf-16 in place of utf- 8 or vice versa.. do u get the same error when you pass a (diff) parm which is > 1 char?

potential points of investigation would be, classpath, (lib)path reflect v8, pre-req ptfs missing in db2 z/os, bind for v8 packages was not done, NLS conversion registry variable is messed or updated outside of db2, v7 runs with disableunicode=1. [...] 13903 62 43_Re: SQL/PL debugging and error/log handling7_Karthik18_vkarthik@YAHOO.COM31_Fri, 25 Jun 2004 21:02:42 -0500371_- some points that may be of interest to you would be :

jes2 limitation on multiple TCBs trying to update a SYSOUT dataset. to avoid that we tried using msgfile(....enq..) LE runtime option, had atleast 2 outages because a. stored procedure stuck in some db2 code while holding enq, b. there is a timing exposure in LE that caused the enq not being released. [...] 13966 49 35_Re: Errors during Accounting Report7_Karthik18_vkarthik@YAHOO.COM31_Fri, 25 Jun 2004 21:12:24 -0500581_- one possibility apart from a bug in db2 is, the smf records are not time sorted.

-Karthik

On Tue, 22 Jun 2004 17:23:31 -0500, db2sysc wrote:

>All: > >When I try to print out Accounting short report using PM batch jcl and have >SMF dataset as input ( our ACCT/STAT are directed to SMF). > >I get these messages in the JOBSUMDD output. > >FPEA2252I XXAP N/P DBAP N/P 06/17/04 >00:00:00.14 > ACCOUNTING FIELD ADELCL7L WAS NOT CALCULATED. A BEGIN TIME WAS >ZERO OR THE RESULT WOULD HAVE BEEN NEGATIVE > > > >Also, towards the end we see [...] 14016 99 44_Re: Bank One Adding Another DB2 Systems Guru35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Fri, 25 Jun 2004 23:35:58 -0400825_iso-8859-1 I heard that JP is in process of taking some of it back. Don't know what/where.

Regards, Joel ----- Original Message ----- From: To: Sent: Friday, June 25, 2004 7:19 PM Subject: Re: Bank One Adding Another DB2 Systems Guru



> ---------------------- Information from the mail header ----------------------- > Sender: DB2 Data Base Discussion List > Poster: JTonchick@AOL.COM > Subject: Re: Bank One Adding Another DB2 Systems Guru > -------------------------------------------------------------------------- ----- > > -------------------------------1088205545 > Content-Type: text/plain; charset="US-ASCII" > Content-Transfer-Encoding: 7bit > > Well, Bank One has been in an In-sourcing trend, taking control back from a [...] 14116 155 32_Re: Quiesce Abend Intermittently13_Michael Ebert18_mebert@AMADEUS.NET31_Sat, 26 Jun 2004 07:29:21 +0200407_us-ascii The error message should name the other incompatible utility. Usually the "exclusive" utility is an operation on SYSUTILX. If you can't find any info in the failed job, you simply have to check all other DB2 utility jobs running at the same time for a candidate. There is no real point in doing a QUIESCE before a backup in my opinion. There was a discussion about this a couple of years ago. [...] 14272 50 52_Error allocating a DSSIZE 8 G partitioned tablespace13_Horacio Villa17_hvilla@AR.IBM.COM31_Sat, 26 Jun 2004 10:10:17 -0300683_US-ASCII Dear list members,

we have the requirement to define a 15 partition tablespace, DSSIZE 8 G. As we didn't have any dataclass with extended format and extended addressability defined, we asked the storage folks to define one and to assign that dataclass to these tablespaces (we trap that via the volid in the stogroup). Trying to define a tablespace, we get this error: DSNP009I -DS2T THE FOLLOWING ERROR MESSAGES WERE RECEIVED FOR DEFINE CLUSTER ON DS2TUT.DSNDBC.LEGACY.VSTAF001.I0001.A001 IGD17219I UNABLE TO CONTINUE DEFINE OF DATA SET DS2TUT.DSNDBC.LEGACY.VSTAF001.I0001.A001 IDC3014I CATALOG ERROR IDC3009I ** VSAM CATALOG RETURN CODE IS 48 - REASON CODE IS [...] 14323 84 44_DB2 DBA Contract Opportunity - Dallas, Texas0_20_kent.collins@ATT.NET31_Sat, 26 Jun 2004 16:06:28 +0000543_- Shiloh Consulting Inc has a immediate opening for an Application DB2 DBA in Dallas, Texas. If you are interested in this opportunity and have the require skills and certifications please send your resume to kent.collins@att.net for immediate consideration.

Shiloh Consulting Inc www.shilohconsulting.net 940.453.8061

Description: In support of existing DB2 production installations in a HA environment 40% In support of ongoing development efforts 40% Problem solving and consultation with other support organizations 20% [...] 14408 66 35_Re: Errors during Accounting Report7_db2sysc17_db2sysc@YAHOO.COM31_Sat, 26 Jun 2004 11:25:26 -0500597_- How do you timesort the smf dump?. We have TIMEZONE(+05:00) in the GLOBAL area of the accounting report file jcl.

On Fri, 25 Jun 2004 21:12:24 -0500, Karthik wrote:

>one possibility apart from a bug in db2 is, the smf records are not time >sorted. > >-Karthik > >On Tue, 22 Jun 2004 17:23:31 -0500, db2sysc wrote: > >>All: >> >>When I try to print out Accounting short report using PM batch jcl and have >>SMF dataset as input ( our ACCT/STAT are directed to SMF). >> >>I get these messages in the JOBSUMDD output. >> >>FPEA2252I XXAP [...] 14475 18 24_Job timeouts with itself7_db2sysc17_db2sysc@YAHOO.COM31_Sat, 26 Jun 2004 12:02:03 -0500748_- All:

We are exeperiencing timeouts in some of our unload jobs(dsntiaul) and when we see the MSTR log, it says its contending with itself.

The sql stmts have current degree ='any' and has WITH UR or FETCH ONLY at the end if the sql stmts.

Any suggestions?

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 14494 71 56_Re: Error allocating a DSSIZE 8 G partitioned tablespace15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Sat, 26 Jun 2004 22:09:38 +0200608_us-ascii Hi Horacio,

maybe a stupid question, but did your storage people set the REUSE-option for the dataclass itself to YES or did they leave it to NO? Maybe you can check yourself via ISMF?

Marcel.

>Dear list members, > >we have the requirement to define a 15 partition tablespace, DSSIZE 8 G. >As we didn't have any dataclass with extended format and extended >addressability defined, we asked the storage folks to define one and to >assign that dataclass to these tablespaces (we trap that via the volid in >the stogroup). >Trying to define a tablespace, we get this error: > [...] 14566 62 48_Re: DB2 DBA Contract Opportunity - Dallas, Texas13_arlen stovall24_arlenstovall@HOTMAIL.COM31_Sun, 27 Jun 2004 01:51:24 +0000662_- Kent, I recently moved from Dallas to Indiana and would be interested in returning. I am attaching my current resume and would like to talk to you about the opportunity. I will look forward to hearing from you.

>From: kent.collins@ATT.NET >Reply-To: DB2 Database Discussion list at IDUG >To: DB2-L@WWW.IDUGDB2-L.ORG >Subject: DB2 DBA Contract Opportunity - Dallas, Texas >Date: Sat, 26 Jun 2004 16:06:28 +0000 > >Shiloh Consulting Inc has a immediate opening for an Application DB2 DBA in >Dallas, Texas. If you are interested in this opportunity and have the >require skills and certifications please send your resume to [...] 14629 17 60_Re: Can a trigger retrieve program name of triggering event?13_Steve Mallett23_smallett@OZEMAIL.COM.AU31_Sat, 26 Jun 2004 21:46:39 -0500702_- Jim,

I was looking into the same problem a couple of years ago and discovered to my surprise that the called procedure couldn't determine the calling program name. Maybe things have changed since then

regards, Steve

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 14647 87 48_Re: DB2 DBA Contract Opportunity - Dallas, Texas12_kent collins20_kent.collins@ATT.NET31_Sun, 27 Jun 2004 08:44:21 -0500609_US-ASCII Arlen, I will contact you on Monday regarding this opportunity. Interviews will begin on Monday or Tuesday.

Thanks Kent

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of arlen stovall Sent: Saturday, June 26, 2004 8:51 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: DB2 DBA Contract Opportunity - Dallas, Texas

Kent, I recently moved from Dallas to Indiana and would be interested in returning. I am attaching my current resume and would like to talk to you about the opportunity. I will look forward to hearing from you. [...] 14735 341 35_Re: how to increase tablespace size8_BigSmile21_msherazi@VIDEOTRON.CA31_Sun, 27 Jun 2004 09:53:26 -0400634_iso-8859-1 Hi Lan,

1. Calc The PRIQTY/SECQTY round up to nearest trk or cyl 2. Alter PRIQTY/SECQTY 3. Reorg (Load will do too)

Good Luck

-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von lan Gesendet: Freitag, 25. Juni 2004 21:42 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: how to increase tablespace size



Hi,

How to increase the size of table space in an existing database without influencing other objects? Because the tablespaces we have now are not big enough for later development. Detailed instructions will be appreciated! [...] 15077 32 19_Voice from the Past8_Ward Fry12_fryw@USA.NET31_Sun, 27 Jun 2004 16:22:59 -0400533_ISO-8859-1 Hi Steve,

Saw your address out on the DB2 Listserv some time ago, and meant to drop you a line at that time. I often think of you and your company. I'm sure I would have loved the job and the people, including yourself, but, frankly, my wife didn't feel she'd be happy in that area. I think she would have been fine, but who knows. How are you doing? Did the person you hired work out? Is Shep still there? If so, please say hi for me. I'm now working as STROBE Systems Engineer in Cincinnati, Ohio, but still [...] 15110 78 41_Re: Vague DB2 OS/390 performance question12_Troy Coleman25_troy.coleman@SOFTBASE.COM31_Sun, 27 Jun 2004 21:46:20 -0500668_us-ascii Are your DB2 datasets being migrated? I've seen this when jobs are waiting on the recall.



Troy Coleman, Sales Engineer IBM Certified Solutions Expert

SoftBase Systems, Inc. 828-670-9900 ext. 334 troy.coleman@softbase.com

Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/

The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message. [...] 15189 324 36_Re: how to increase table space size12_Troy Coleman25_troy.coleman@SOFTBASE.COM31_Sun, 27 Jun 2004 21:47:44 -0500394_us-ascii If you're not sure how to calculate space you can use a free tool from IBM called DB2 Estimator. The URL: http://www-306.ibm.com/software/data/db2/os390/estimate/ This is a very good tool for doing physical design for performance. You can pull the table space and index space Statistics in from your DB2 catalog. You can then go in and do "What IF?" for size, freepage, pctfree.. [...] 15514 17 41_Re: Vague DB2 OS/390 performance question10_Steve Lamb20_steven.lamb@CGEY.COM31_Mon, 28 Jun 2004 03:19:21 -0500634_- Troy,

Nope, the datasets used by the batch jobs aren't being archived - they're the most heavily-used datasets in the system.

Regards,

Steve

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 15532 166 36_SQL against DB2 Version 7.1 on OS39020_Jenny Kuan Kong Lang19_jennykkl@MAS.COM.MY31_Mon, 28 Jun 2004 17:04:41 +0800316_iso-8859-1 I have two different DBAT threads accessing DB2 Version 7.1 tables on OS/390 V2.10. Both did not encounter any problem when executing their SQL call against DB2 Version 5.0. One was running under WebSphere, the other from Microsfot Excel, both via DB2 Connect gateway Version 8.1 residing on AIX 5L. [...] 15699 64 38_Re: Incorrect Object States at DR site12_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Mon, 28 Jun 2004 05:45:28 -0400476_iso-8859-1 Are you sure that you cleared the Coupling facility before the back up at the primary site. Another DB2 could have had a utility running that you might have missed. Also do you only do a full volume restore at the DR site, and not an forward recovery of the DB2 catalog and directory? If you do a rollforward of the DB2 catalog and Directory, some shops restore SYSUTILX from a image copy. This in effect would lose any utilities that might have been running. [...] 15764 63 40_Re: SQL against DB2 Version 7.1 on OS39012_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 28 Jun 2004 03:37:50 -0700630_us-ascii Are you running UNICODE? Or did you add DISABLEUNICODE=1 to your DB2 Connect CLI.INI file?

--- Jenny Kuan Kong Lang wrote: > I have two different DBAT threads accessing DB2 Version 7.1 tables on OS/390 > V2.10. Both did not encounter any problem when executing their SQL call > against DB2 Version 5.0. One was running under WebSphere, the other from > Microsfot Excel, both via DB2 Connect gateway Version 8.1 residing on AIX 5L. > > > DB Server Connectivity User Interface > ========= ============ =============== > DB2 Ver 7.1 DB2 Connect Gateway Web Application running in WebSphere [...] 15828 44 28_Re: Job timeouts with itself11_Greuter, Al18_Al.Greuter@SSA.GOV31_Mon, 28 Jun 2004 08:12:47 -0400523_- Is it possible you have two jobs with the same UTILID?

Thanks,

Al Greuter Lockheed Martin DB2 Database Administrator 410-496-9547

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of db2sysc Sent: Saturday, June 26, 2004 1:02 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Job timeouts with itself

All:

We are exeperiencing timeouts in some of our unload jobs(dsntiaul) and when we see the MSTR log, it says its contending with itself. [...] 15873 63 35_Re: how to increase tablespace size14_Seth Bienstock17_seth2@SKARVEN.NET31_Mon, 28 Jun 2004 08:13:06 -0400372_us-ascii Lan,

Without being redundant with others' suggestions, I'll stick in an additional 2 cents:

1 cent: The ALTER/REORG approach will work for STOGROUP-defined objects. Otherwise, the approach is a little more complicated. It's probably safe to assume that they are STOGROUP-defined, so no need to explain the other approach unless you ask for it. [...] 15937 75 28_Re: Job timeouts with itself14_Seth Bienstock17_seth2@SKARVEN.NET31_Mon, 28 Jun 2004 08:40:28 -0400606_us-ascii Can you supply the actual MSTR messages?

By the way, the Utility Identifier (UTILID) is only applicable for DB2 online utilities. DSNTIAUL is not even a DB2 utility. It is considered a "sample" unload program that IBM supplies as a freebie.

> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Greuter, Al > Sent: Monday, June 28, 2004 8:13 am > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: Job timeouts with itself > > > Is it possible you have two jobs with the same UTILID? > > Thanks, > > Al Greuter > Lockheed Martin [...] 16013 52 60_Re: Can a trigger retrieve program name of triggering event?8_Jim Hunt27_JHunt@GENERALIUSALIFERE.COM31_Mon, 28 Jun 2004 07:54:42 -0500558_us-ascii Thanks Steve. That's what I was afraid of.



Jim Hunt

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Steve Mallett Sent: Saturday, June 26, 2004 9:47 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Can a trigger retrieve program name of triggering event?

Jim,

I was looking into the same problem a couple of years ago and discovered to my surprise that the called procedure couldn't determine the calling program name. Maybe things have changed since then [...] 16066 95 28_Re: Job timeouts with itself25_Thanikachalam Sundarrajan21_billysundar@YAHOO.COM31_Mon, 28 Jun 2004 06:22:21 -0700587_us-ascii can you check the reason code 00c9008e or 00c90088 it will be helpful if you can cut the message and post it here.

--- Seth Bienstock wrote: > Can you supply the actual MSTR messages? > > By the way, the Utility Identifier (UTILID) is only applicable for DB2 > online utilities. DSNTIAUL is not even a DB2 utility. It is considered a > "sample" unload program that IBM supplies as a freebie. > > > -----Original Message----- > > From: DB2 Data Base Discussion List > > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Greuter, Al > > Sent: Monday, June [...] 16162 55 51_Re: Attention List Owners - db2-l-documents problem15_Thomas, Janis E19_Janis.Thomas@CA.COM31_Mon, 28 Jun 2004 08:52:21 -0500416_iso-8859-1 When the list was moved to IDUG, unfortunately any attachments set to the DB2-L-Documents list could not be moved & will produce the attached error when someone tries to access them. Now the list is set up not to allow any attachments, so anything sent to the list needs to be within the email text. This will avoid the problem in the future if for some reason the list would need to be moved again. [...] 16218 13 28_Quiesce Abend Intermittently0_27_mamccormack@STATESTREET.COM31_Mon, 28 Jun 2004 10:11:21 -0400583_US-ASCII Look in the DB2-L archives for 'QUIESCE question' in May, 2003. There were many postings on this topic.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 16232 246 33_Re: FW: Timestamp - Timestamp = ?15_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 28 Jun 2004 10:16:58 -0400608_iso-8859-1 Roland...Our system programmer says we're on "PP 5648-A25 IBM COBOL for OS/390 & VM 2.2.1" ***************************************************************************************************************************

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Schiradin,Roland HG-Dir itb-db/dc Sent: Friday, June 25, 2004 5:29 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: AW: FW: Timestamp - Timestamp = ?



Ray, there is no COBOL LE!! Post the header of your Cobol compile listing and I'll tell you the Cobol Version [...] 16479 82 32_Re: Quiesce Abend Intermittently0_19_csutfin@AMSOUTH.COM31_Mon, 28 Jun 2004 09:25:26 -0500287_us-ascii This happens to us when I am attempting to image copy the SYSUTILX tablespace in the system catalog and another utility is active.

I have a step at the beginning of my job that performs a -DIS UTILITY(*) so that I can have an idea of what is causing the contention. [...] 16562 63 42_3 part name in view (OS/390 and UDB/linux)10_Derek Lund22_derek.lund@UTORONTO.CA31_Mon, 28 Jun 2004 11:45:10 -0400412_us-ascii Hi, we are in the process of moving a small application (along with the db2 tables) from DB2 OS/390 V7 to DB2 UDB (V8) for Linux (on the Integrated Facility for Linux (OS/390)). To load the Linux tables we are using the Command Centre to 'export' the data from OS/390 to a dataset and then using the LOAD utility on Linux to load them. The IXF format/loading is quick and seems to be working fine. [...] 16626 114 46_Re: 3 part name in view (OS/390 and UDB/linux)14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 28 Jun 2004 17:21:08 +0100521_iso-8859-1 You could always define a UDF to update the PIN. The program that needs to make a pin change does it by calling the UDF (passing acocunt number and new PIN as parameters?). Then the external program that is called by the UDF will do whatever it needs to do, to update the pin in as many places as it needs to, before returning control back to the caller. This has the advantages that if you make changes again to the pin storage tables, you will only have to change the UDF and not the calling program(s) [...] 16741 45 28_Re: Job timeouts with itself12_Jim Harrison20_jimatwork@HOTPOP.COM31_Mon, 28 Jun 2004 12:47:51 -0400323_us-ascii We also ran into this problem last year with a UR query (Z/OS 1.04, DB2 7.1)

Symptom: -904 on secondary index. Reason code 00C200FA

Based on what we saw in the BMC performance monitor, for some reason the query was taking an X lock on a secondary index. Happened with both DSNTEP2 and DSNTIAUL. [...] 16787 16 28_Re: Job timeouts with itself17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Mon, 28 Jun 2004 12:43:00 -0500652_- Where did the timeout happen?. Is it in DBD.

If you were really doing DSNTIAUL, why would a SELECT timeout with another SELECT on the same resource?.

Regards, Venkat

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 16804 86 28_Re: Job timeouts with itself11_Tom Moulder23_tmoulder@HOUSTON.RR.COM31_Mon, 28 Jun 2004 12:50:44 -0500523_us-ascii I had a similar experience years ago on version 4. What we determined was that IRLM storage was exhausted, DB2 could not get the lock and terminated the thread with -904. It was hard to catch because immediately after the error there was a ton of storage available in the IRLM. I would have thought that by now IBM would have made a better error message or fixed this type of problem. But I guess the only fix is to provide lock escalation and then you wouldn't see this. Check your lock escalation settings. [...] 16891 14 46_Re: 3 part name in view (OS/390 and UDB/linux)17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Mon, 28 Jun 2004 12:48:51 -0500633_- You can join with a federated system. But in this case only the distributed apps will be able to join across machines / across databases.

Regards, Venkat

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 16906 26 35_Re: how to increase tablespace size3_lan18_lxw176@HOTMAIL.COM31_Mon, 28 Jun 2004 13:41:00 -0500471_ISO-8859-1 Hi Seth,

Thanks very much for your suggestions. May I know what the “STOGROUP- defined” objects means since I’m very new to DB2. Can I just use “Alter tablespace resize (device ‘’ )” to increase the container size used in the tablespace? What’s the detailed syntax for ALTER/LOAD REPLACE or ALTER/REORG? Are these two commands only used to increase the tablespace size instead of container size? I’m confused by these two sizes. [...] 16933 57 35_Re: how to increase tablespace size14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Mon, 28 Jun 2004 14:02:00 -0500624_us-ascii Lan, For future reference, when asking questions, it is a good idea to state the platform you are running DB2 on. It appears that you are running DB2 UDB for Windows, Unix and Linux. Many of the responses are specific to DB2 UDB for OS/390 and z/OS. For UDB for Windows, Unix and Linux, you would use ALTER TABLESPACE RESIZE Or the ALTER TABLESPACE EXTEND Hope this helps, Wayne Driscoll Sr. Product Architect Quest Software http://www.quest.com/db2 wdriscoll@listserv.quest.com NOTE: All opinions are strictly my own. EMail Address in sig must be modified. [...] 16991 20 46_Re: 3 part name in view (OS/390 and UDB/linux)10_Derek Lund22_derek.lund@UTORONTO.CA31_Mon, 28 Jun 2004 14:29:39 -0500439_- Thank you for the idea. I had not thought of a UDF as we have not taken advantage of them here (yet). We use Natural Code (Software AG) on our host (OS/390) so I am not sure if it (current version) can handle UDF's. I must investigate further.

If we can use UDF's, there must be something done eventually on the Linux side I assume, remote bind, registering UDF, etc so as to indentify what system to go to to do the update. [...] 17012 15 35_Re: how to increase tablespace size3_lan18_lxw176@HOTMAIL.COM31_Mon, 28 Jun 2004 14:44:46 -0500560_- Wayne,

Sorry for my mistake and thank very much for your explanation.

Lan

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 17028 152 51_Re: Attention List Owners - db2-l-documents problem15_Andersen, Cindy18_candersen@FNNI.COM31_Mon, 28 Jun 2004 15:03:30 -0500409_iso-8859-1 Could someone supporting the db2-l-documents listserv copy the old db2-l-documents that didn't make it over into multiple "text" emails and put them back into db2-l-documents?

There were many useful routines located in db2-l-documents that people would like to access again. We can't expect the original contributors to send everything again. Some may not even be working with DB2 now. [...] 17181 214 51_Re: Attention List Owners - db2-l-documents problem15_Thomas, Janis E19_Janis.Thomas@CA.COM31_Mon, 28 Jun 2004 15:20:18 -0500341_iso-8859-1 If there was an easy way to do it at the time of the move, it would have been done. We do not have access to YLA & Assoc's server where the old DB2-L & DB2-L-documents lists were hosted. I would imagine that they have cleaned up their server anyway & removed anything related to DB2-L, since the list was moved last October. [...] 17396 69 32_DB2 Security and CA's Top Secret13_Rhea, Matthew29_Matthew.Rhea@INFARMBUREAU.COM31_Mon, 28 Jun 2004 15:32:42 -0500433_iso-8859-1 Does anyone have opinions about using CA's product Top Secret to support DB2 security? I'm just beginning to explore what the product can do and I don't know of anyone with experience with it. One major point of concern for me is that the DB2 catalog would not contain the privileges/authorizations for objects. That means the DBA can no longer control access to objects, and must rely on a Top Secret administrator. [...] 17466 106 56_Re: Error allocating a DSSIZE 8 G partitioned tablespace13_Horacio Villa17_hvilla@AR.IBM.COM31_Mon, 28 Jun 2004 17:37:14 -0300437_US-ASCII Marcel,

it was NO. They changed it to YES, but I'm getting the same message.

Horacio







Marcel Harleman cc: Sent by: DB2 Data Subject: Re: Error allocating a DSSIZE 8 G partitioned Base Discussion tablespace List



06/26/04 05:09 PM Please respond to DB2 Database Discussion list at IDUG [...] 17573 71 36_Re: DB2 Security and CA's Top Secret0_16_FRUSA@BCBSIL.COM31_Mon, 28 Jun 2004 15:41:16 -0500457_us-ascii We use Top Secret and DB2 security is via secondary authorization ids - a little bit of a headache at times but not a major problem.







"Rhea, Matthew" cc: Sent by: "DB2 Data Subject: DB2 Security and CA's Top Secret Base Discussion List"



06/28/2004 03:32 PM Please respond to "DB2 Database Discussion list at IDUG" [...] 17645 255 36_Re: DB2 Security and CA's Top Secret16_Proctor, William25_William.Proctor@TGSLC.ORG31_Mon, 28 Jun 2004 16:31:41 -0500416_us-ascii Hello,

We use Top Secret with DB2 V7.1. We have very few problems and I have to work with the Security folks to control access to DB2. The security folks still have to rely on us to tell them what needs to be done. We have to work very closely together. Top Secret also has the ability to keep the db2 security updated so that if you decide to switch back to DB2 then your security is in place. [...] 17901 93 61_Antwort: Error allocating a DSSIZE 8 G partitioned tablespace0_19_dirk.pohl@SERCON.DE31_Tue, 29 Jun 2004 00:12:07 +0200576_US-ASCII Hi Horacio

How is

Guaranteed Space Sustained Data Rate

set in your Storage Class .

regards dirk











An: DB2-L@WWW.IDUGDB2-L.ORG Kopie: Thema: Error allocating a DSSIZE 8 G partitioned tablespace Horacio Villa Sicherheit: |-------------------| 26.06.2004 10:10 | [ ] SerCon Intern | Gesendet von: DB2 Data |-------------------| (Embedded image Base Discussion List moved to file: pic18846.jpg) Bitte antworten an DB2 Database Discussion list at IDUG [...] 17995 138 65_Re: Antwort: Error allocating a DSSIZE 8 G partitioned tablespace13_Horacio Villa17_hvilla@AR.IBM.COM31_Mon, 28 Jun 2004 19:45:23 -0300494_US-ASCII Hi Dirk,

sure enough, Guaranteed Space is NO. Can' tell you about Sustained Data Rate, there's no storage people available now. I'll tell you tomorrow.

Regards, Horacio





dirk.pohl@SERCON. DE To: DB2-L@WWW.IDUGDB2-L.ORG Sent by: DB2 Data cc: Base Discussion Subject: Antwort: Error allocating a DSSIZE 8 G List partitioned tablespace



06/28/04 07:12 PM Please respond to DB2 Database Discussion list at IDUG [...] 18134 70 15_Opthint Problem12_John Piccoli18_jpiccoli@COGECO.CA31_Mon, 28 Jun 2004 19:34:29 -0400315_iso-8859-1 I am unable to get DB2 to use my opthint. I have updated an existing access path(shown below after the change) by changing the following plan table columns in the first row of a 4-way nested loop join which originally accessed the outer table via a 1-column matching index scan using list prefetch: [...] 18205 91 35_Re: Errors during Accounting Report7_Karthik18_vkarthik@YAHOO.COM31_Mon, 28 Jun 2004 20:57:05 -0500429_- Hello,

db2pm has a option to sort the input records, I dont remember it now.

The reason I mentioned this possibility is because I experienced a similar sounding (dont remember what) problem which turned to be caused by out-of sequence records in SMF, which was caused by a problem in a job dumping the smf datasets. so check if something has whacked the smf dump, other than that I cant think of any, sorry. [...] 18297 86 38_Re: Incorrect Object States at DR site7_Karthik18_vkarthik@YAHOO.COM31_Mon, 28 Jun 2004 21:18:03 -0500545_- John, thanks for the thoughts.

Our DR environment is not 'queisce-backup-remote restore' mechanism, but based on DASD synchronous mirroring to a remote site. the recovery in this case is very similar to the db2's normal restart recovery after a failure. we didnt do any recover of sysutilx or any catalog objects. nor does db2 complain about any inconsistency. The 'diagnose display sysutil' was consistent with '-dis util' output. The '-dis util' is by default group scope, so we expected to see all utilities in the group there. [...] 18384 18 19_Re: Opthint Problem9_Ford Wong14_fordie@SHAW.CA31_Mon, 28 Jun 2004 23:13:42 -0500651_- John,

This may be silly but did you repeat updating the OPTHINT name in all the explain rows and not just the first one for that query?

Hope this helps,

Ford

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm