1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l June 2003, week 5
2 96 47_Re: Stored Procedures: Maximum # of Parameters?0_16_khampto1@TXU.COM31_Sun, 29 Jun 2003 09:42:53 -0500559_us-ascii Hello Ann, There is no documented hard limit to the number of parameters that may be defined to a stored procedure in DB2 on OS/390 or Z/Os. The practical limit will be the maximum length of an SQL statemenr when you build your CREATE PROCEDURE command. Our developers originally tried doing one with many parameters, then backed off and redid it with the input as one large CHAR parameter that was broken out within the program using a copybook fot the data area definition. Is that what you are trying to do, or can you give us more detail on [...]
99 105 23_Re: PeopleSoft Platform0_16_khampto1@TXU.COM31_Sun, 29 Jun 2003 09:58:05 -0500459_us-ascii Exactly the same scenario has transpired here, except for the last part about dedicating a mainframe, we ran quite nicely with Peoplesoft Financials in its own dedicated DB2 subsystem sharing the same LPAR with two other DB2 subsystems, and that LPAR sharing the physical CPU with two other LPAR's.
Kirk Hampton DB2 OS/390 Sysprog IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390 TXU Business Services Dallas, Texas [...]
205 125 67_Query Rewrite (Between predicate) and REOPT(VARS) - DB2 v.7 OS/39 048_=?iso-8859-1?Q?Jo=E3o_Alberto_de_Oliveira_Lima?=22_JOAOLIMA@SENADO.GOV.BR31_Sun, 29 Jun 2003 18:11:42 -0300462_iso-8859-1 Dear list,
I noticed that query rewriting is not applied to the following predicates when "WS-GRP-MIN = WS-GRP-MAX" and "WS-SUB-MIN = WS-SUB-MAX":
AND GRP_CD BETWEEN :WS-GRP-MIN AND :WS-GRP-MAX AND SUBGRP_CD BETWEEN :WS-SUB-MIN AND :WS-SUB-MAX
I think that, if the package for the statement containing these predicates was bound with REOPT(VARS), the predicates could be rewritten at execution time, by DB2 SQL Compiler, as: [...]
331 80 14_Re: Autorebind12_Paul Redhead35_paul.a.redhead@TRANSPORT.QLD.GOV.AU31_Mon, 30 Jun 2003 09:15:58 +1000462_us-ascii Bob, If these flags don't change then I don't believe that the autorebind can be happening to this package on the mainframe. Could it be possible that the failure is happening on a package of the same name BUT different version?
Paul.
ROBERT NORGANG @LISTSERV.YLASSOC.COM> on 27/06/2003 12:16:55
Please respond to DB2 Data Base Discussion List [...]
412 64 11_unsubscribe23_Senthilkumar Ponnuswamy33_senthilkumar.ponnuswamy@WIPRO.COM31_Mon, 30 Jun 2003 08:40:37 +0530453_us-ascii
**************************Disclaimer************************************
Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. [...]
477 63 11_unsubscribe14_Bhushan Turavi25_bhushan.thuravi@WIPRO.COM31_Mon, 30 Jun 2003 08:57:29 +0530453_us-ascii
**************************Disclaimer************************************
Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. [...]
541 205 17_Re: CLUSTERRATIOF13_Michael Ebert18_mebert@AMADEUS.NET31_Mon, 30 Jun 2003 09:40:14 +0200428_iso-8859-1 Good morning!
The first thing you should check is whether you've got UQ75036 (V6)/UQ75037 (V7) applied which fixes the RUNSTATS bug I've mentioned (closed 20.3.2003). This bug must have been introduced rather recently. I'm also puzzled that CLUSTERED changed because I thought that CLUSTERED is Y if CLUSTERRATIO>=0.95... however this column is NOT used in access path selection (CLUSTERRATIO/F is used). [...]
747 21 11_unsubscribe8_KNVS Rao24_KNVS.Rao@LNTINFOTECH.COM31_Mon, 30 Jun 2003 13:34:43 +05309_us-ascii
769 164 50_Re: CDB vs CA online reorg for busy DB2 SAP tables23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Mon, 30 Jun 2003 10:19:47 +0100310_iso-8859-1 Werv
interesting you (EDS) send this to the list as I was asked the very same question by a friend from the UK.
We have CDB and it works like a dream, not always getting in on the object as Dr Ebert has said ... long running not Committing txns cause us issues from time to time. [...]
934 29 37_Re: Stored Procedures - use of DB2SQL10_Dev DBA SL24_db2_dba@STANDARDLIFE.COM31_Mon, 30 Jun 2003 10:58:55 +0100691_us-ascii James, John
thanks for replies. Thats 2-0 to DB2SQL. Looks like another humiliating climbdown for me.
For more information on Standard Life, visit our website http://www.standardlife.com/ ; The Standard Life Assurance Company, Standard Life House, 30 Lothian Road, Edinburgh EH1 2DH, is registered in Scotland (No. SZ4) and regulated by the Financial Services Authority. Tel: 0131 225 2552 - calls may be recorded or monitored. This confidential e-mail is for the addressee only. If received in error, do not retain/copy/disclose it without our consent and please return it to us. We virus scan and monitor all e-mails but are not [...]
964 36 37_Re: Stored Procedures - use of DB2SQL10_Dev DBA SL24_db2_dba@STANDARDLIFE.COM31_Mon, 30 Jun 2003 11:21:35 +0100583_us-ascii John
Yes I see both UDFs and triggers both require mode Db2SQL, which I hadn't realised as we dont use these features currently and I can see the value of consistency. But how do you deal with limitations on the values that you can assign to P-SQLSTATE? For example if your Storproc receives a referential integrity error code -803 sqlstate 23505, what do you set P-SQLSTATE to ? My understanding it that you must contrive a new error code beginning 38***. Creating a new list of sqlstates that map to existing sqlstates is more than funky, maybe even clunky. I [...]
1001 115 37_Re: using Cobol and Identity columns.14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 30 Jun 2003 11:43:56 +0100537_iso-8859-1 Hi Shery
Consider this
You have a customer table that has the customer number (key) column defined as an identity column GENERATED ALWAYS It's like this in ALL of your DB2 environments
Now try unloading all (or a sample) of your customers from your production subsystem and reloading them into development. You will get a whole new set of customer numbers! Now if you do the same for records from your order table you will have orders for customers where no customer with that customer number exist! [...]
1117 53 47_Re: Table structure changes in 24x7 application14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 30 Jun 2003 11:47:05 +0100365_Windows-1252 Deborah,
What is the timescale for this application going into production (assuming it doesn't exist already)?
Take a look at what Version 8 brings and see if that helps - if so, you might try and explain to your application folks that they can either spend lots of money now or wait a year and solve their problem for (almost) free [...]
1171 46 43_Re: Adding Identity column using RCMigrator14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 30 Jun 2003 12:10:26 +0100565_iso-8859-1 Hi Joe,
What specifically is your question?
RCM V7.1 does support adding Identity columns to existing tables so I am not sure what you need to know
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: Joe Bitetto [mailto:joseph.bitetto@UNIFI.COM] Sent: Friday, June 27, 2003 4:59 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: [DB2-L] Adding Identity column using RCMigrator [...]
1218 97 73_Re: Stored Procedure Build Fails After Applying FixPak 9 to Admin Cli ent15_Steve McAuliffe35_Stephen.McAuliffe@BANKOFAMERICA.COM31_Mon, 30 Jun 2003 12:19:11 +0100566_- John,
Have you a RACF resource define similar to below:
DSNR DT01.WLM_REFRESH.DT01SP01
From what I remember you need one for each subsystem and each work load mangled address space.
Cheers,
Steve McAuliffe DB2 Systems/IMS team Network Computing Group Tel: (020) 8760 6988 Internal: x 66988
-----Original Message----- From: John Turman [mailto:John.Turman@TXDPS.STATE.TX.US] Sent: 27 June 2003 22:52 To: DB2-L@LISTSERV.YLASSOC.COM Subject: Stored Procedure Build Fails After Applying FixPak 9 to Admin Cli ent [...]
1316 100 50_Re: CDB vs CA online reorg for busy DB2 SAP tables16_Aurora Dell'Anno28_aurora.dellanno@UK.QUEST.COM31_Mon, 30 Jun 2003 06:26:14 -0500479_- Hello Werv!
As Michael Ebert and Leslie pointed out, CDB offers you the "extra" utility timeout parm, applicable separately to each of the utilities.
That is, you choose how long each utility should wait before "declaring itself timed-out" and how long it should wait before trying again.
If you don't have need to have R/W access to your data, by the way, you can use the High-Availability mode of the Auto-Online Reorg, too, to save even more time! [...]
1417 65 16_Re: 4gb Log Size16_Aurora Dell'Anno28_aurora.dellanno@UK.QUEST.COM31_Mon, 30 Jun 2003 06:33:26 -0500438_- Hello there,
the usual caution to apply is, your log file size should be no greater than the size of your archival media, for DR purposes.
Otherwise, there are no other "known issues"... as far as I'm aware!
ciao!
Aurora Emanuela Dell'Anno Systems Engineer Quest Software
Office: +44 (0)1628 601000 ext. 1160 Mobile: +44 (0)7881 818867 Fax: +44 (0)1628 606606 email: aurora.dellanno@uk.quest.com [...]
1483 134 45_Re: -991 SQLcode while running online program12_Boone, Henry16_HBoone@GEICO.COM31_Mon, 30 Jun 2003 08:06:13 -0400978_iso-8859-1 Is your USRA.OS390.PGMLIB APF'ed? If not, then I'd suspect #2. You need to add a DFSESL DD statement to your JCL if you put non-APF'ed libraries in your STEPLIB. The following is an example of the JCL we use for DB2 applications using IMS DL/I Batch:
//PS001 EXEC PGM=DFSRRC00,PARM=(DLI,DSNMTV01,psbname,,,,,,,,,,,N) .... //STEPLIB DD DSN=&IMSSYS..RESLIB,DISP=SHR // DD DSN=&IMSSYS..DYNLIB,DISP=SHR // DD DSN=&DB2SYS..DSNEXIT,DISP=SHR // DD DSN=&DB2SYS..DSNLOAD,DISP=SHR // DD DSN=yourloadlib..... //DFSRESLB DD DSN=&IMSSYS..RESLIB,DISP=SHR //IMS DD DSN=&IMSLIB..DBDLIB,DISP=SHR // DD DSN=&IMSLIB..PSBLIB,DISP=SHR //DFSESL DD DSN=&IMSSYS..RESLIB,DISP=SHR // DD DSN=&DB2SYS..DSNEXIT,DISP=SHR // DD DSN=&DB2SYS..DSNLOAD,DISP=SHR //PROCLIB DD DSN=&IMSSYS..PROCLIB,DISP=SHR //IEFRDER DD DSN=.... .... //DDOTV02 DD DSN=your.ddotv02.dsname,DISP=(NEW,CATLG), // SPACE=(CYL,(1,1)),UNIT=SYSDA,VOL=SER=...., // DCB=(RECFM=VB,BLKSIZE=4096,LRECL=4092) //DDITV02 DD * [...]
1618 164 37_Re: using Cobol and Identity columns.14_Julian Stuhler27_julian.stuhler@TRITON.CO.UK31_Mon, 30 Jun 2003 13:28:09 +0100300_US-ASCII Susan,
I'm not sure how much control you have over the SQL statement in a SAS environment, but you could try including the identity column in the column list, and using the DEFAULT clause in the values list. For example, if COL1 is an id column you can use the following syntax: [...]
1783 47 14_IDUG 2003 Nice15_Steve McAuliffe35_Stephen.McAuliffe@BANKOFAMERICA.COM31_Mon, 30 Jun 2003 14:11:47 +0100407_- Does anybody out there have any info on the content of the 2003 European IDUG?
I'm trying to sell it that I should be there, and the IDUG site is a bit blank at the moment as to conference content.
Any information from those in the know would be most appreciated.
Cheers,
Steve McAuliffe DB2 Systems/IMS team Network Computing Group Tel: (020) 8760 6988 Internal: x 66988 [...]
1831 95 18_Re: IDUG 2003 Nice14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 30 Jun 2003 14:41:03 +0100350_iso-8859-1 Hi Steve,
We are just putting the finishing touches to the grid.
Needless to say, in the OS/390 thread(s) there will be a fair amount of Version 8, from both IBM and independent folks. I can't really say much more at this point until the official agenda is published (I will try and find out when that is likely to be). [...]
1927 38 37_Re: Stored Procedures - use of DB2SQL12_John Maenpaa24_john_maenpaa@YLASSOC.COM31_Mon, 30 Jun 2003 09:03:45 -0500337_- I generally use '38999' for catastrophic errors and use the message field to hold details on the error. The calling program usually doesn't care why the stored procedure failed, just that it failed. To clients, the stored procedure should be a black box where they don't have to worry about the internal workings and error codes. [...]
1966 13 14_Re: Autorebind11_Chuck Kosin24_chuck.kosin@EXPERIAN.COM31_Mon, 30 Jun 2003 09:05:59 -0500433_- Is this a data sharing environment with members on different releases? If so, consider assigning the value "coexist" to dsnzparm.abind. The install guide describes this value.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
1980 35 35_accessing ORACLE databases from MVS12_Jim McAlpine22_jim.mcalpine@CEDAR.COM31_Mon, 30 Jun 2003 15:00:59 +0100711_iso-8859-1 Is it possible to access an oracle database on say a unix platform from an MVS client program and if so what options are available.
TIA
Jim McAlpine
About CedAr CedAr Software Ltd. is the newly formed merger between Cedar Software and Arelon. The company provides financial, eProcurement and performance management solutions that deliver increased capabilities and offer a direct and measurable financial return on investment. CedAr has a customer base of more than 500 organisations in both the public and private sector. Particular sector strengths include Airlines, Business Services, Education, Emergency Services, Financial Services, Government, Health, Not for Profit [...]
2016 57 23_Re: SQL Statement Limit32_Poston, William L (COMFIN, ITSS)21_william.poston@GE.COM31_Mon, 30 Jun 2003 10:27:54 -0400632_iso-8859-1 does anyone know if there is a limit on the size of an sql statement?
-----Original Message----- From: John Maenpaa [mailto:john_maenpaa@YLASSOC.COM] Sent: Monday, June 30, 2003 9:04 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Stored Procedures - use of DB2SQL
I generally use '38999' for catastrophic errors and use the message field to hold details on the error. The calling program usually doesn't care why the stored procedure failed, just that it failed. To clients, the stored procedure should be a black box where they don't have to worry about the internal workings and error codes. [...]
2074 15 71_Re: Query Rewrite (Between predicate) and REOPT(VARS) - DB2 v.7 OS/39 015_Patrick Bossman18_bossman@US.IBM.COM31_Mon, 30 Jun 2003 09:25:24 -0500314_- Hello, Yes, that is correct.
Regards, Pat Bossman
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
2090 87 50_Re: Stored Procedure/Trigger Architecture Question13_Fleming, Mark21_MFLEMING@ALLSTATE.COM31_Mon, 30 Jun 2003 09:39:26 -0500575_iso-8859-1 Sharon,
I don't have a solution, but wanted to point out a potential "gotcha". Everything that fires a trigger, including any SPs and UDFs that get invoked, and additional trigger caused by SQL they issue, are part of a single unit of work and are synchronous. The scenario you painted below will all be synchronous. The "gotcha" is in step 4, where you've had the SP issue the commit. While it's allowed in v7, the scope of the commit is everything in the current unit of work. The actual scope can include a lot more than you've described. Let's say [...]
2178 54 23_Re: SQL Statement Limit15_Patrick Hignett33_Patrick.Hignett@MORGANSTANLEY.COM31_Mon, 30 Jun 2003 15:42:08 +0100621_us-ascii 32K springs to mind.
"Poston, William L (COMFIN, ITSS)" wrote:
> does anyone know if there is a limit on the size of an sql statement? > > -----Original Message----- > From: John Maenpaa [mailto:john_maenpaa@YLASSOC.COM] > Sent: Monday, June 30, 2003 9:04 AM > To: DB2-L@LISTSERV.YLASSOC.COM > Subject: Re: Stored Procedures - use of DB2SQL > > I generally use '38999' for catastrophic errors and use the message > field to hold details on the error. The calling program usually doesn't > care why the stored procedure failed, just that it failed. To clients, > the stored procedure should be [...]
2233 19 24_Maximum SQL Statement...15_Gaston, Raymond17_GastonRay@ORU.COM31_Mon, 30 Jun 2003 10:47:27 -0400436_iso-8859-1 Appendix A: Limits in DB2 for OS/390 and z/OS (DB2 Version 7) states:
Longest SQL statement..................32765 bytes
- Ray Gaston
Con Edison / DBA
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
2253 159 23_Re: SQL Statement Limit11_David Nance16_DWNance@FHSC.COM31_Mon, 30 Jun 2003 10:52:38 -0400442_US-ASCII 32K, I think it goes up in V8.
Dave Nance First Health Services, Corp. (804)527-6841
>>> william.poston@GE.COM 6/30/03 10:27:54 AM >>> does anyone know if there is a limit on the size of an sql statement?
-----Original Message----- From: John Maenpaa [mailto:john_maenpaa@YLASSOC.COM] Sent: Monday, June 30, 2003 9:04 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Stored Procedures - use of DB2SQL [...]
2413 10 23_Re: SQL Statement Limit33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Mon, 30 Jun 2003 09:39:29 -0500320_- Yes, see SQL Reference Guide. I think for Version 7 it is 32K.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
2424 82 23_Re: SQL Statement Limit14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 30 Jun 2003 16:00:28 +0100503_iso-8859-1 Yes there is
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: Poston, William L (COMFIN, ITSS) [mailto:william.poston@GE.COM] Sent: Monday, June 30, 2003 3:28 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: [DB2-L] SQL Statement Limit
does anyone know if there is a limit on the size of an sql statement? [...]
2507 91 23_Re: SQL Statement Limit14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 30 Jun 2003 16:02:09 +0100448_iso-8859-1 sorry - couldn't resist that!!
Anyway, it has been increasing slowly from 32K to 64K on Version 7 and goes up to 2M on version 8
Be aware that SQL you enter through SPUFI does NOT have trailing or leading blanks removed from the lines so you can very quickly get to 64K if you space out your SQL (like most of us do). I found this out when I created a 255 partition tablespace and its partitioning index all in one go [...]
2599 45 28_Re: Maximum SQL Statement...14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 30 Jun 2003 16:03:19 +0100493_iso-8859-1 That's irritated me - I wonder where I got the 64K idea from???
Anyway, it is definitely 2Meg in V8!
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: Gaston, Raymond [mailto:GastonRay@ORU.COM] Sent: Monday, June 30, 2003 3:47 PM To: DB2-L@LISTSERV.YLASSOC.COM Subject: [DB2-L] Maximum SQL Statement... [...]
2645 212 23_Re: SQL Statement Limit15_Steve McAuliffe35_Stephen.McAuliffe@BANKOFAMERICA.COM31_Mon, 30 Jun 2003 16:04:22 +0100564_- 2mb in V8 I think
Cheers,
Steve McAuliffe DB2 Systems/IMS team Network Computing Group Tel: (020) 8760 6988 Internal: x 66988
-----Original Message----- From: David Nance [mailto:DWNance@FHSC.COM] Sent: 30 June 2003 15:53 To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: SQL Statement Limit
32K, I think it goes up in V8.
Dave Nance First Health Services, Corp. (804)527-6841
>>> william.poston@GE.COM 6/30/03 10:27:54 AM >>> does anyone know if there is a limit on the size of an sql statement? [...]
2858 97 50_Re: Stored Procedure/Trigger Architecture Question12_Terry Fuller30_Terry.Fuller@ARVATOSYSTEMS.COM31_Mon, 30 Jun 2003 10:11:45 -0500699_iso-8859-1 Another solution would be to use a CICS transient data queue to start a new task to invoke the pieces you want to be asynchronous.
Terry Fuller, DBA arvato systems
-----Original Message----- From: Fleming, Mark [mailto:MFLEMING@ALLSTATE.COM] Sent: Monday, June 30, 2003 9:39 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Stored Procedure/Trigger Architecture Question
Sharon,
I don't have a solution, but wanted to point out a potential "gotcha". Everything that fires a trigger, including any SPs and UDFs that get invoked, and additional trigger caused by SQL they issue, are part of a single unit of work and are synchronous. The scenario you [...]
2956 29 23_Re: SQL Statement Limit13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Mon, 30 Jun 2003 11:23:19 -0400600_utf-8 Hello William.
This and other limits are all documented in Appendix A of the SQL reference. For z/OS, it is 32k until version 8 when it increases to 2Mg.
>David Seibert >Compuware Corporation Database Product Architect >Dave.Seibert@compuware.com > > > > The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...]
2986 28 37_Re: Stored Procedures - use of DB2SQL10_Dev DBA SL24_db2_dba@STANDARDLIFE.COM31_Mon, 30 Jun 2003 16:15:27 +0100688_us-ascii John
Yes, thanks. I was thinking along similar lines, although my developers still reckon they want to interpret non-catastrophic scenarios. You can't please any of the people any of the time in this job.
For more information on Standard Life, visit our website http://www.standardlife.com/ ; The Standard Life Assurance Company, Standard Life House, 30 Lothian Road, Edinburgh EH1 2DH, is registered in Scotland (No. SZ4) and regulated by the Financial Services Authority. Tel: 0131 225 2552 - calls may be recorded or monitored. This confidential e-mail is for the addressee only. If received in error, do not retain/copy/disclose it without [...]
3015 105 39_Re: accessing ORACLE databases from MVS13_Julie Johnson23_Julie.Johnson@UCONN.EDU31_Mon, 30 Jun 2003 11:36:52 -0400339_iso-8859-1 Hi Jim,
Yes, it is; ORACLE has a product called 'Net8' (we call it Oranet, here). I wasn't involved in the install (that would have made life too easy) but inherited maintenance/troubleshooting it. It seems to work pretty well. I heard anectdotally that there were some puzzling configuration issues during install. [...]
3121 62 50_Re: Stored Procedure/Trigger Architecture Question16_Blumenthal, Gary21_GBlument@UNCH.UNC.EDU31_Mon, 30 Jun 2003 11:44:00 -0400572_iso-8859-1 If I am not mistaken, in every RDBMS I have ever seen a trigger firing is part of the same (i.e. original) transaction. So a trigger calls a SP (which performs DML) causing another trigger to fire (which does more DML) is all one LONG transaction.
Your first trigger can insert (via a stored procedure or direct SQL) but you would need a secondary (a daemon that wakes up periodically for example) process that trolls through your 'work to be applied' table and updates your summary table. This will split up the unit of work appropriately for you. [...]
3184 19 21_Hidden explain tables17_Christophe Radier17_c300501@YAHOO.COM31_Mon, 30 Jun 2003 10:51:36 -0500393_- Hi all , I've just read the paper "The DB2 Optimizer : what lies beneath" by Frank Ingrassia , and he talked about 9 hidden tables populated at explain time when they exists. Does any one has the DDL for these 9 tables ? (predicate_table , structure_table,reference_table,cost_table ...) Predicate_table seems to be very interesting as it shows you how the sql is interpreted ..etc ... [...]
3204 125 39_Re: accessing ORACLE databases from MVS12_Jim McAlpine22_jim.mcalpine@CEDAR.COM31_Mon, 30 Jun 2003 16:50:55 +0100633_iso-8859-1 Thanks for that Julie, I'll look into that product.
Regards
Jim McAlpine
-----Original Message----- From: Julie Johnson [mailto:Julie.Johnson@UCONN.EDU] Sent: 30 June 2003 16:37 To: DB2-L@listserv.ylassoc.com Subject: Re: accessing ORACLE databases from MVS
Hi Jim,
Yes, it is; ORACLE has a product called 'Net8' (we call it Oranet, here). I wasn't involved in the install (that would have made life too easy) but inherited maintenance/troubleshooting it. It seems to work pretty well. I heard anectdotally that there were some puzzling configuration issues during install. [...]
3330 104 50_Re: Stored Procedure/Trigger Architecture Question14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 30 Jun 2003 17:05:32 +0100328_iso-8859-1 Not only is it the same unit of work, but anything initiated by a trigger is all part of the same STATEMENT as well - beware performance implications!
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 [...]
3435 44 25_Re: Hidden explain tables16_Farnsworth, Dave31_DFarnsworth@ASHLEYFURNITURE.COM31_Mon, 30 Jun 2003 11:30:53 -0500677_iso-8859-1 Do you have a link for this paper by any chance?
Thanks,
Dave
-----Original Message----- From: Christophe Radier [mailto:c300501@YAHOO.COM] Sent: Monday, June 30, 2003 10:52 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Hidden explain tables
Hi all , I've just read the paper "The DB2 Optimizer : what lies beneath" by Frank Ingrassia , and he talked about 9 hidden tables populated at explain time when they exists. Does any one has the DDL for these 9 tables ? (predicate_table , structure_table,reference_table,cost_table ...) Predicate_table seems to be very interesting as it shows you how the sql is interpreted ..etc ... [...]
3480 53 25_Re: Hidden explain tables13_Mackey, Glenn20_GMackey@GUIDEONE.COM31_Mon, 30 Jun 2003 12:37:22 -0500835_iso-8859-1 http://www.imsi-intl.com/WhatLiesBeneath.ppt
-----Original Message----- From: Farnsworth, Dave [mailto:DFarnsworth@ASHLEYFURNITURE.COM] Sent: Monday, June 30, 2003 11:31 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Re: Hidden explain tables
Do you have a link for this paper by any chance?
Thanks,
Dave
-----Original Message----- From: Christophe Radier [mailto:c300501@YAHOO.COM] Sent: Monday, June 30, 2003 10:52 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: Hidden explain tables
Hi all , I've just read the paper "The DB2 Optimizer : what lies beneath" by Frank Ingrassia , and he talked about 9 hidden tables populated at explain time when they exists. Does any one has the DDL for these 9 tables ? (predicate_table , structure_table,reference_table,cost_table [...]
3534 42 25_Re: Hidden explain tables12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Mon, 30 Jun 2003 13:15:01 -0500283_iso-8859-1 I have the SQL for DB2 Version 5.1, but I that would probably not work on Version 7, might in Version 6. What version do you run of DB2?
By the way, the SQL DDL would have no warranties associated with it at all period. You would be using it at your own risk. [...]
3577 154 25_Re: Hidden explain tables13_Phil Sevetson26_Phil.Sevetson@WAKEFERN.COM31_Mon, 30 Jun 2003 14:49:33 -0400331_us-ascii Tom --
I just did a quick read of Frank's paper, and probably missed the important part, but... how does having the DDL help? Does the information persist beyond the end of the BIND program, if you have the tables predefined in the catalog? Or am I missing something else here which would make the DDL useful? [...]
3732 17 7_OPTHINT18_Patrick BARTHELEMY26_patrick.barthelemy@SNCF.FR31_Mon, 30 Jun 2003 13:28:37 -0500296_- I'm trying to use in DB2 V7 a path which worked fine in DB2 V5 via the OPTHINT option of the bind command. When I bind or rebind, I always get a +395 SQLCODE, an SQLSTATE=01628 and a token = 32. Does someone have an idea about the problem ?. Is it possible to use a DB2 V5 path in DB2 V7? [...]
3750 47 11_Re: OPTHINT12_michael bell24_mbell11@WORLDNET.ATT.NET31_Mon, 30 Jun 2003 14:15:02 -0500340_iso-8859-1 reason code 32 for +395 says 32 CREATOR or TNAME is invalid.
It looks like you are changing environments (different creator, database, etc)
I haven't tested using a V5 access in V7 but I don't know of any reason it wouldn't work if all the information is still valid.
Mike Bell HLS Technologies [...]
3798 93 11_Re: OPTHINT16_Galluzzo, Lori A21_lori.galluzzo@EDS.COM31_Mon, 30 Jun 2003 15:22:51 -0400508_- Be sure to check the matching criteria; same queryno, package, version, collid, etc.
Here is the manual description :
5.8.8.10.6 How DB2 locates the PLAN_TABLE rows for a hint
| DB2 uses the QUERYNO, APPLNAME, PROGNAME, VERSION, COLLID, and OPTHINT | columns of the PLAN_TABLE to determine the rows to use for a hint. For a | PLAN_TABLE row, the QUERYNO, APPLNAME, PROGNAME, VERSION, and COLLID | values must match the corresponding values for an SQL statement before the [...]
3892 61 25_Re: Hidden explain tables12_Troy Coleman29_troycci@COLEMANCONSULTING.COM31_Mon, 30 Jun 2003 14:36:33 -0500477_us-ascii I don't have access to the install libraries but you may want to do a search in your DSNSAMP library for one of the tables like "DSN_STATEMENT". You should find the DDL in the install sample library.
Troy Coleman
IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for OS/390 IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for UNIX, Linux, Windows and OS/2 IBM Certified Advanced Technical Expert DB2 - DRDA [...]
3954 216 25_Re: Hidden explain tables12_Moulder, Tom19_Tom_Moulder@BMC.COM31_Mon, 30 Jun 2003 14:54:33 -0500582_iso-8859-1 Just the presence of the tables will cause the Bind or explain process to populate the tables and the data will persist after the bind just like the plan_table data so it could be saved and interrogated for useful information.
However, there are many warnings about using the information in the tables. They are meant for internal DB2 use only and were never meant to be used by someone tuning an SQL statement. If you find something that is consistent and when you change something DB2 changes as well in a positive manner then you can probably rely on that [...]
4171 130 39_Re: accessing ORACLE databases from MVS12_Hynes, Carol27_Carol.Hynes@DWD.STATE.WI.US31_Mon, 30 Jun 2003 15:06:38 -0500435_iso-8859-1 Jim, We are in the process of testing IBM's Information Integrator to access Oracle data residing on HP/Unix from our Mainframe. So far we've been able to access the data(read only) from SPUFI, COBOL/Batch and COBOL/CICS.
-----Original Message----- From: Jim McAlpine [mailto:jim.mcalpine@CEDAR.COM] Sent: Monday, June 30, 2003 9:01 AM To: DB2-L@LISTSERV.YLASSOC.COM Subject: accessing ORACLE databases from MVS [...]
4302 243 25_Re: Hidden explain tables13_Phil Sevetson26_Phil.Sevetson@WAKEFERN.COM31_Mon, 30 Jun 2003 16:19:25 -0400695_us-ascii Okay, I'm clear on this now. Thanks, Tom.
--Phil Sevetson phil.sevetson@wakefern.com Database Administration Wakefern Food Corporation
"Moulder, Tom" Sent by: DB2 Data Base Discussion List 06/30/2003 03:54 PM Please respond to DB2 Data Base Discussion List
To: DB2-L@LISTSERV.YLASSOC.COM cc: Subject: Re: Hidden explain tables
Just the presence of the tables will cause the Bind or explain process to populate the tables and the data will persist after the bind just like the plan_table data so it could be saved and interrogated for useful information. [...]
4546 48 25_Re: Hidden explain tables14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Mon, 30 Jun 2003 23:10:38 +0200339_- In DB2 for VM/VSE (called SQL/DS some years ago) there are structure_table, reference_table, cost_table and maybe predicate_table. I could send you the DDLs, but I don't know, if the table layout is the same for DB2 for z/OS; the plan_table is different. If someone has the true DDLs for DB2 for z/OS, I would like to get them too. [...]
4595 15 25_Re: Hidden explain tables17_Christophe Radier17_c300501@YAHOO.COM31_Mon, 30 Jun 2003 16:40:32 -0500343_- You can have the full text version of this paper on the site of Idug , section DPJ. Thank you for all the warnings , i just want to see by my own what the paper talked about. About the information given by these tables and ether you can rely on them or not , i think that more Information is always good if you know how to exploit them [...]
4611 49 25_Re: Hidden explain tables14_Wayne Driscoll24_wayne.driscoll@QUEST.COM31_Mon, 30 Jun 2003 17:57:50 -0500441_us-ascii In addition to the warnings Tom has provided, I remember issues where the existance of these "hidden" tables could cause the bind and/or explain process to abend with an S0C4, and I believe could cause DB2 outages in certain situations. I would STRONGLY suggest not using these tables in a production environment. Wayne Driscoll Sr. Software Developer Quest Software wdriscoll@quest.com NOTE: All opinions are strictly my own. [...]
4661 224 19_Re: Synchronous I/O11_Howe, Steve15_SHowe@BCBSM.COM31_Mon, 30 Jun 2003 18:30:56 -0400428_iso-8859-1 The Explains look good. Not unusual to have 50-75 Queries running at any given time.
Steve Howe 313.225.6151 Database Services - Mainframe Group
-----Original Message----- From: Phil Sevetson [mailto:Phil.Sevetson@WAKEFERN.COM] Sent: Friday, June 27, 2003 10:14 AM Subject: Re: Synchronous I/O
Are you seeing the increased SyncIO in the EXPLAINs? Or just in the SMF records? [...]
4886 15 47_Julie A Lundrigan/GIS/CSC is out of the office.17_Julie A Lundrigan16_jlundri2@CSC.COM30_Tue, 1 Jul 2003 01:04:38 +0100403_us-ascii I will be out of the office starting 30/06/2003 and will not return until 07/07/2003.
I will respond to your message when I return.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-REQUEST@listserv.ylassoc.com.
4902 141 19_Re: Synchronous I/O14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Mon, 30 Jun 2003 23:13:55 -0400422_ISO-8859-1 Instead`of bouncing all over with theories, please post the statistics for your buffer pools for a period showing the problem. Maybe stats for an hour, at the Trace Report level, (all the pools) so we can see all the low level pool data. Reards, Joel
Message text written by DB2 Data Base Discussion List >The Explains look good. Not unusual to have 50-75 Queries running at any given time. [...]