1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l June 2004, week 5 2 34 20_Index on Foreign Key41_=?iso-8859-1?Q?=22Kurtz=2C_R=FCdiger=22?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Tue, 29 Jun 2004 07:48:59 +0200605_iso-8859-1 -----Ursprüngliche Nachricht----- Von: Kurtz, Rüdiger Gesendet: Dienstag, 29. Juni 2004 07:47 An: 'DB2-L@IDUGDB2-L.ORG' Betreff: Index on Foreign Key



Hi everyone out there,

I have a question on indexes on foreign keys; in our shop we design our indexes so that each foreign key is supported by an index; we do, however, sometimes change the indexes by adding other columns for performance reasons. Now rumour has crept up that DB2 disregards those indexes and will only use those indexes that exactly match the foreign key. DB2 flavour is V7 for z/os. Any comments ? [...] 37 18 24_Re: Index on Foreign Key33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Tue, 29 Jun 2004 03:05:46 -0500476_- Ruediger

A long time ago, I attended a seminar dealing with RI. The teacher told us, that the index can have more columns appended, if a DELETE CASCADE operation comes along. The index will be used. But, if you run a CHECK DATA utility, an index will only be used, if it maps exactly with the foreign key. I am not sure, if this holds for version 7. But I don't believe, that things are getting worse in newer releases. May be only a trace can reveal the truth. [...] 56 24 29_Estimating DB2 Recovery Times9_Gary Hope27_Gary.Hope@MORGANSTANLEY.COM31_Tue, 29 Jun 2004 09:29:39 +0100347_us-ascii Hi.

We have a large number DB2 objects in our OS/390 DB2 V7 environment and I have been asked to settup a repository listing all of these DB2 objects and providing an estimated recovery time for each of the objects. The recovery time will be based on IBM Recover to copy for Tablespaces and IBM rebuild Index for the indexes. [...] 81 90 33_Re: Estimating DB2 Recovery Times23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Tue, 29 Jun 2004 09:59:41 +0100513_- Gary I have thought long and hard about this very subject over the last two years, however, I still cannot see how you will be able to estimate the time given that you do not know :

- bottlenecks on the day of recovery - The complexity of the build of the indexes on that day - How will DB2 handle differing objects being recovered in the same Bufferpool, i.e. object A & B recovered together may give you a different run time than for A & C, i.e. the recovery times of A in each case may differ by [...] 172 100 33_Re: Estimating DB2 Recovery Times9_Gary Hope27_Gary.Hope@MORGANSTANLEY.COM31_Tue, 29 Jun 2004 10:11:05 +0100476_us-ascii Les,

Thanks for your reply, in this case the reason for the request is not really about image copies but is more about Component Failure Impact Analysis and I to have been grappling with the type of questions you have highlighted below. Thats why rather than continuing pulling my hair out I was wondering if anyone has used any quick and easy formula which would perhanps be true in perfect conditions but would carry the ryder of known "uncertainties". [...] 273 33 34_DB2 Connect data provider and .net13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Tue, 29 Jun 2004 07:11:22 -0400549_iso-8859-1 This question is directed to any .net developers on this list.

I'm trying to find someone to confirm the problem I'm having when trying to update a .net dataset back to DB2 on z/os. It appears that the DB2 managed provider does not send primary key information down as part of the fill operation and therefore, no update can take place back to the server. I'm using the .net managed provider that came with DB2 Connect V8.1. I'm also using the data provider command builder so I don't have to code up my own update statement. [...] 307 11 51_Re: Attention List Owners - db2-l-documents problem10_Bill Brown21_db2_dba@BELLSOUTH.NET31_Tue, 29 Jun 2004 07:24:41 -0500572_- If the attachments are gone why not just remove the posts that refer to attachments and start over?

--------------------------------------------------------------------------------- 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 319 93 19_Re: Opthint Problem11_Mark Ediger22_MEDIGER@TRANSUNION.COM31_Tue, 29 Jun 2004 08:02:01 -0500458_US-ASCII Make sure your dsnzparm OPTHINTS for that subsystem is =yes





Ediger

>>> jpiccoli@COGECO.CA 6/28/2004 6:34:29 PM >>> 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: [...] 413 97 19_Re: Opthint Problem14_Seth Bienstock17_seth2@SKARVEN.NET31_Tue, 29 Jun 2004 09:13:05 -0400593_us-ascii Shouldn't ALL of the rows have the OPTHINT value of "TEST1"?

> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of John Piccoli > Sent: Monday, June 28, 2004 7:34 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Opthint Problem > > > 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 [...] 511 26 35_Re: Errors during Accounting Report12_Joyce Lindow30_Joyce.Lindow@FIRSTDATACORP.COM31_Tue, 29 Jun 2004 08:54:04 -0500586_US-ASCII regarding sorting smf records, any sort program will work. here are same control cards for syncsort that will extract the DB2 records (hex 64,65,66) and sort by date,time. INCLUDE COND=((6,1,BI,EQ,X'64'), OR,(6,1,BI,EQ,X'65'), OR,(6,1,BI,EQ,X'66')) SORT FIELDS=(11,4,A, 7,4,A), FORMAT=BI SMF records are VBS so to keep things simple I use a DCB=(*.SORTIN) for the output tape. If this does correct your problem you may want to talk to the team responsible for the smf jobs and find out if they changed anything recently- such as not using the sort exit. Joyce LIndow- FDR [...] 538 81 51_Re: Attention List Owners - db2-l-documents problem14_Flatley, Lynne31_Lynne.Flatley@LIBERTYMUTUAL.COM31_Tue, 29 Jun 2004 10:10:14 -0400277_us-ascii Respectfully, I guess there's no reason to maintain the 2 lists then. The second list was set-up to facilitate the storing of scripts, etc. If they have to be in-stream in the email that one sends to the list, we might as well send the script right to this list. [...] 620 25 35_increase tablespace size in DB2 UDB3_lan18_lxw176@HOTMAIL.COM31_Tue, 29 Jun 2004 09:35:57 -0500618_- Hi,

I'm useing DB2 UDB version 8.0. In order to increase the size of tablespace in an existing database without influencing other object, I used the command "alter tablespace user_small resize (device '/dev/rShortSpace3' 256) overhead 24.1 transferrate 0.9" to increase the tablespace size in my courrent database. But I got the following error: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL20170N There is not enough space in the table space "USER_SMALL" for the specified action. SQLSTATE=57059 [...] 646 31 20_PRECOMPILER Services13_Solecki, Rohn19_Rohn.Solecki@GWL.CA31_Tue, 29 Jun 2004 09:48:51 -0500356_iso-8859-1 DB2 V7 OS/390 z/OS introduced the new feature "PRECOMPILER Services" that allows you to combine the precompile step into the compile step. The admin guide says: If your compiler has an SQL statement coprocessor, you can eliminate the precompile step in your batch program preparation jobs for C, COBOL, and PL/I programs. [...] 678 427 33_Re: Estimating DB2 Recovery Times22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Tue, 29 Jun 2004 10:02:06 -0500335_iso-8859-1

Hi Gary,

If you want something quick and dirty, you can try DB2 Estimator. It may not be perfect, but, its close and it is FREE. I have used it before and has worked well, atleast for initial estimates.

If you have these tables already in your system, you can use DB2 stats to populate Estimator. [...] 1106 40 26_GROUP BY versus ORDER BY ?12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 29 Jun 2004 17:11:16 +0200559_iso-8859-1 Collegues,

Given is the following SQL statement:

SELECT "DAW911_LANGTEXT" ,"DAW911_WERT" FROM "ORDN"."DAW911" WHERE ("DAW911_TAB_NR" = '108' ) GROUP BY "DAW911_LANGTEXT" ,"DAW911_WERT"

Our monitor reports the follwing:

SQL +------- Total IN-SQL Time ------ Calls Elapsed % CPU % ----- ----------------- --------------- 1134K 04:18,98006 67,6% 00:33,64602 71,..

Okay, the statement runs very often.

But in the SELECT there is no aggregation. Would it be better to code a simple ORDER BY in this case ? [...] 1147 60 24_Re: PRECOMPILER Services11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Tue, 29 Jun 2004 16:25:53 +0100815_iso-8859-1 Hi Rohn

I think that IBM Enterprise PL/1 for z/OS and OS/390 V3R1 is the minimum level that supports precompiler services.

Regards Mike Turner Email: michael_turner@compuserve.com Tel: +44 (0)1565 873702 web: www.michael-turner.ltd.uk

-----Original Message----- From: owner-db2-l@WWW.IDUGDB2-L.ORG [mailto:owner-db2-l@WWW.IDUGDB2-L.ORG]On Behalf Of Solecki, Rohn Sent: Tuesday, 29 June 2004 15:49 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: PRECOMPILER Services





DB2 V7 OS/390 z/OS introduced the new feature "PRECOMPILER Services" that allows you to combine the precompile step into the compile step. The admin guide says: If your compiler has an SQL statement coprocessor, you can eliminate the precompile step in your batch program preparation jobs for [...] 1208 235 33_Re: Estimating DB2 Recovery Times11_Smock, Dale22_dsmock@RANDOMHOUSE.COM31_Tue, 29 Jun 2004 10:35:28 -0500446_us-ascii The time to recover tocopy a tablespace is roughly linear based on the size of the tablespace, so you can run a test recover to a "typical" or average size tablespace to get an estimate. You might want to pick several average sizes (small non-partitioned, medium non-partitioned, large partitioned) to get a better estimate, then use these times to estimate each of your objects based on their proportional size to the test tables. [...] 1444 21 40_EXPDL option on a Template for a Dataset13_Robert Tilkes22_tilkesr@NATIONWIDE.COM31_Tue, 29 Jun 2004 10:23:12 -0500434_- Quick question. We have an unload utility that has a template defined as a GDG with a limit set for 7. We would like to set up cycle retention of this dataset. In normal JCL we would use EXPDT=99007. In the template you have to use EXPDL 'YYYYDDD'. We have tried '99007' and DB2 does not like the date format. I hard coded a date to get around the this issue for the time being, but I am looking for a more permanent solution. [...] 1466 172 30_Re: GROUP BY versus ORDER BY ?11_David Nance16_DWNance@FHSC.COM31_Tue, 29 Jun 2004 11:40:00 -0400565_ISO-8859-1 Georg, You have to remember that order by and group by have totally different meanings. I'm assuming that this statement is a singleton select. Perhaps your SQL statement could provide multiple records and the group by was used in order to get a single row back, thus the use of a singleton select rather than a cursor? If that is the case, you might want to consider the use of "FETCH FIRST 1 ROW ONLY". That would eliminate a sort altogether, which I think is what you have currently. Also, remember that a group by clause does not garauntee data [...] 1639 84 44_Re: EXPDL option on a Template for a Dataset0_22_sally.mir@WACHOVIA.COM31_Tue, 29 Jun 2004 11:53:20 -0400387_US-ASCII If your intent is to set the number of generations, you could use the GDGLIMIT parameter.

If your intent is to specify how long to keep each generation, you could use the RETPD parameter, which specifies the retention period in days.

If you have SMS classes set up, you could use the MGMTCLAS (? I think that's the one) that gives you the retention you want. [...] 1724 131 56_Re: Error allocating a DSSIZE 8 G partitioned tablespace15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Tue, 29 Jun 2004 17:51:51 +0200355_us-ascii Hi Horacio,

one of our storage guys suggested you also might take a look at your ACS routines. Perhaps it overrules something that shouldn't be overruled or whatever ...

And as a bonus: the following is the output from a DISPLAY command in ISMF for our definition of the EXTENDED dataclass in production. Maybe this is useful. [...] 1856 22 19_Bufferpool question15_Daniel Cremieux17_c300501@YAHOO.COM31_Tue, 29 Jun 2004 10:58:44 -0500283_- Using Mainview to track I/O on tablespace , i see that

Sync IO = 1016 Async = 316

Pages get by sync i/o = 1016 Pages get by async = 9447



The question i want to ask is : this object must be classified as a Random access object or as a Sequential ? [...] 1879 122 44_Re: EXPDL option on a Template for a Dataset13_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Tue, 29 Jun 2004 11:10:37 -0500382_US-ASCII Yes we could use RETPD. Our situation is the GDG base has already been defined and the job runs weekly. We would like to have Cycle control verses date control. So that it will remain on tape and not be scratched until 7 versions have been generated by that specific job name. I do realize that the ICF catalog will only contain 7 versions that are not job dependent. [...] 2002 40 47_Looking for a C / Assembler Mainframe Developer0_13_TomF@ENET.COM31_Tue, 29 Jun 2004 09:15:34 -0700498_UTF-8







Mainframe Product Development Opportunity

E-Net Corporation provides software solutions for remote recovery and replication of mainframe databases, including DB2 for z/OS. E-Net’s RRDF (Remote Recovery Data Facility) product family is used worldwide to support real-time remote journaling and database shadowing for mission critical applications. Our Log Apply for DB2 solution provides robust high-performance replication of DB2/z/OS databases. [...] 2043 64 21_v8 development center11_Harris, Jay19_jharris@STATE.PA.US31_Tue, 29 Jun 2004 12:20:17 -0400297_iso-8859-1 Using the development center that comes with db2 connect v8 a developer is trying to build a new stored procedure ( on db2 v7 on z/os ) but is getting a sql1097 node not found in node directory, but he can bring up an existing stored procedure and build it on the db2 v7 for z/os. [...] 2108 55 44_Re: EXPDL option on a Template for a Dataset19_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Tue, 29 Jun 2004 12:30:05 -0400408_us-ascii We use EXPDL '1999000' to allow dataset expiration to be controlled by number of generations.

| Larry Jardine | Production DBA | Aetna |



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Robert Tilkes Sent: Tuesday, June 29, 2004 11:23 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: EXPDL option on a Template for a Dataset [...] 2164 57 39_Re: increase tablespace size in DB2 UDB8_P. Evans19_pevansdba@YAHOO.COM31_Tue, 29 Jun 2004 09:33:42 -0700332_us-ascii Lan, issue db2 "? SQL20170N" at the command prompt and it will give you some more detail. Also take a look at your db2diag.log for further explanation.

The explanation for that error code suggests that you are lowering the size of the tablespace, not increasing it. Are you sure you are using the right value ? [...] 2222 12 23_Re: Bufferpool question12_Billy Larsen19_billarsen@YAHOO.COM31_Tue, 29 Jun 2004 11:52:07 -0500579_- chris it's the getpage counter you must consider. in our case , you object has a sequential access profile

--------------------------------------------------------------------------------- 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 2235 58 44_Re: EXPDL option on a Template for a Dataset22_Provenzola, Tony (ETW)24_Tony.Provenzola@NIKE.COM31_Tue, 29 Jun 2004 10:02:14 -0700386_iso-8859-1 We don't use cycles, but my experience (with 98xxx) is that the TMS EXPDT keywords assume/imply '1900' for the century, so EXPDT=99007 should be the same as EXPDT=1999/007 should be the same as EXPDL '1999007'.

Tony Provenzola Nike Database Services Venturi Technology Partners, Consulting Phone * 503-532-0772 Fax * 503-532-3223 Email * Tony.Provenzola@nike.com [...] 2294 68 23_Re: Bufferpool question35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 29 Jun 2004 13:37:14 -0400597_iso-8859-1 Daniel,

The important metric that indicates usage is the getpage counter, and then within this, random and sequential getpages.

The info you provided is either from an object with extremely low usage, or is just a momentary snapshot from Mainview. If it's a snapshot from a minute or less, it's completely without value. You need some meaningful timeframe, say 15 minutes or longer - but not a 1/2 day, or 24 hr period. There is one Synch I/O whenever a prefetch scan starts, and there may also be a possibility of pool thrashing that can cause synch I/Os even after [...] 2363 177 44_Re: EXPDL option on a Template for a Dataset15_Schade, Jeffrey15_JSchade@ISO.COM31_Tue, 29 Jun 2004 13:39:29 -0400608_us-ascii You want to use EXPDL '1999000' which tells your tape management system to let the GDG control when the generations expire.

Jeff _________________________________________ Jeffrey Schade Systems Consultant, Technology Engineering

Insurance Services Office, Inc. 545 Washington Boulevard Jersey City, NJ 07310 Voice: (201) 469-3738 FAX: (201) 748-1500 jschade@iso.com



-----Original Message----- From: Robert Tilkes [mailto:TILKESR@NATIONWIDE.COM] Sent: Tuesday, June 29, 2004 12:11 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: EXPDL option on a Template for a Dataset [...] 2541 29 37_Elementary question on pre-processing13_Charles Mills16_charlesm@MCN.ORG31_Tue, 29 Jun 2004 10:56:51 -0700281_us-ascii I ask your forbearance for what I am sure is an elementary question for most of you. I have over 30 years of MVS-z/OS experience but very little database and/or DB2 knowledge. It's hard to find answers in the manuals when you are not familiar with any of the jargon. [...] 2571 30 39_Re: increase tablespace size in DB2 UDB16_Ian D. Bjorhovde23_ianbjor@MOBILEAUDIO.COM31_Tue, 29 Jun 2004 11:11:55 -0700551_us-ascii lan wrote:

> Hi, > > I'm useing DB2 UDB version 8.0. In order to increase the size of tablespace > in an existing database without influencing other object, > I used the command "alter tablespace user_small resize (device > '/dev/rShortSpace3' 256) overhead 24.1 transferrate 0.9"

Also, make sure you know what you're doing if you're extending DMS raw containers. DB2 does not support offsets within raw devices (i.e. 1 raw device = 1 tablespace), so generally the entire raw device is used when the tablespace is created. [...] 2602 56 41_Re: Elementary question on pre-processing8_Ron Root24_ron.root@CPA.STATE.TX.US31_Tue, 29 Jun 2004 13:16:12 -0500310_US-ASCII Charles, The pre-process can occur on any system. Even a non-MVS platform. The only requirement is that the DBRM be available to the BIND process at bind time. So if the other system does not have shared dasd, there would need to be a process that moves the DBRM to the system that did the BIND. [...] 2659 104 41_Re: Elementary question on pre-processing12_Kirk Hampton16_khampto1@TXU.COM31_Tue, 29 Jun 2004 13:20:31 -0500505_us-ascii Hello Charles, the whole DB2 setup of having the DBRMLIB and doing the separate bind was designed with exactly this in mind, developing and compiling the application on one system, then transmitting the DBRM and the executable module to the target system, where the final BIND is done. Practically all vendor software for DB2 is installed this way, and we do our development on a small LPAR on one machine and promote the executables and DBRM's to production on one of three target systems. [...] 2764 39 41_Re: Elementary question on pre-processing13_Charles Mills16_charlesm@MCN.ORG31_Tue, 29 Jun 2004 11:30:43 -0700500_us-ascii Thank you Ron and Kirk. Exactly the answer I was looking for.

> there would need to be a process that moves the DBRM to the system that did the BIND

I believe the process is called FTP

Thanks again, Charles





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ron Root Sent: Tuesday, June 29, 2004 11:16 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Elementary question on pre-processing [...] 2804 20 20_SYSPACKSTMT and Rexx10_Bill Brown21_db2_dba@BELLSOUTH.NET31_Tue, 29 Jun 2004 13:42:31 -0500546_- I am trying to parse the SQL contained in SYSIBM.SYSPACKSTMT using a rexx procedure. I believe that the first 8 bytes of a set of rows for a statement contain the total length of the statement followed by the length of the entered SQL. I perform a SUBSTR to extract the two lengths from the STMT column and have been unable to convert the length into the decimal equivilant. the command x = x2d(var1) results in the message "Incorrect call to routine". If I execute the command x = x2d(20b) it works fine and I get the value 523 returned. [...] 2825 25 41_FW: Elementary question on pre-processing13_Charles Mills16_charlesm@MCN.ORG31_Tue, 29 Jun 2004 12:14:53 -0700595_us-ascii Someone referred me privately to Bonnie Baker's GREAT article http://www.db2mag.com/showArticle.jhtml?articleID=15300107

THANK YOU. Makes it all about 98% clear.

In it she says "So, DB2 had to allow development on one box with a COBOL compiler but without a DB2 subsystem ..." Do I read that to mean that one does not need a DB2 license on the "development box" -- that the precompiler or coprocesor is NOT a part of the DB2 license but rather a part of -- what? The compilers? DFSMS? MVS? Seems unlikely to me but that's what she said and she seems to be an expert. [...] 2851 61 38_Re: DB2 Connect data provider and .net11_Pan, Andrew19_andrew.pan@WAMU.NET31_Tue, 29 Jun 2004 12:48:57 -0700458_us-ascii I had similar problems before. It seems the DB2 command builder can generate UpdateCommand against DB2 on other platforms, but not DB2 z/OS. You may have to issue explicit update commands to make the changes.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Hodgin, Scott Sent: Tuesday, June 29, 2004 4:11 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 Connect data provider and .net [...] 2913 79 33_Re: Estimating DB2 Recovery Times7_Ed Long19_rdhm99a@PRODIGY.NET31_Tue, 29 Jun 2004 13:35:14 -0700489_us-ascii Hi. Interesting question. There are two cases; one is standalone and the other is DR or mass recovery in general. Estimating #1 can be done this way. Run test recoveries of your biggest tablespaces several times (i.e. around 5x each). Calculate an average ratio of elapsed time per 1000 pages. Now apply this to all your other tables. So if a table has 10000 pages (total pages counting indices) you can then predict that its recovery should be around 10x the standard unit. [...] 2993 60 41_Re: Elementary question on pre-processing6_ghodge26_ghodge@HLSTECHNOLOGIES.COM31_Tue, 29 Jun 2004 15:47:46 -0500533_us-ascii Charles:

You COBOL supplier should provide a pre-processor for DN2. Be sure it is compatible with the DB2 Version you are targeting. The DBRM then needs to be moved from you development system to the target for a Bind.

Gerald Hodge HLS Technologies, Inc.



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Charles Mills Sent: Tuesday, June 29, 2004 2:15 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: FW: Elementary question on pre-processing [...] 3054 101 33_Re: Estimating DB2 Recovery Times14_Seth Bienstock17_seth2@SKARVEN.NET31_Tue, 29 Jun 2004 16:48:07 -0400597_us-ascii Don't forget to account for INDEX RECOVERY !

> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long > Sent: Tuesday, June 29, 2004 4:35 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: Estimating DB2 Recovery Times > > > Hi. > Interesting question. > There are two cases; one is standalone and the other > is DR or mass recovery in general. > Estimating #1 can be done this way. Run test > recoveries of your biggest tablespaces several times > (i.e. around 5x each). > Calculate an average ratio of elapsed time [...] 3156 62 24_Re: SYSPACKSTMT and Rexx6_ghodge26_ghodge@HLSTECHNOLOGIES.COM31_Tue, 29 Jun 2004 15:58:27 -0500519_us-ascii The DB2 Diagnostic Guide shows the format for the table. Be aware that the package statement and the plan statement are different formats. We do this in one of our products and found it to be more difficult than at first expected.

Gerald Hodge HLS Technologies, Inc. 281-265-3004

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bill Brown Sent: Tuesday, June 29, 2004 1:43 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: SYSPACKSTMT and Rexx [...] 3219 157 33_Re: Estimating DB2 Recovery Times7_Ed Long19_rdhm99a@PRODIGY.NET31_Tue, 29 Jun 2004 14:02:41 -0700589_us-ascii Indeed. That is why I included the caveat (total pages counting indices). The elapsed time would be for the entire table (tablespace(s) and indices). --- Seth Bienstock wrote: > Don't forget to account for INDEX RECOVERY ! > > > -----Original Message----- > > From: DB2 Data Base Discussion List > > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long > > Sent: Tuesday, June 29, 2004 4:35 PM > > To: DB2-L@WWW.IDUGDB2-L.ORG > > Subject: Re: Estimating DB2 Recovery Times > > > > > > Hi. > > Interesting question. > > There are two cases; one is standalone [...] 3377 39 24_Re: SYSPACKSTMT and Rexx14_Laredo, Andrew19_Andy_Laredo@BMC.COM31_Tue, 29 Jun 2004 16:00:00 -0500659_iso-8859-1 Try.... X2D(C2X(V1))

-----Original Message----- From: Bill Brown [mailto:db2_dba@BELLSOUTH.NET] Sent: Tue, Jun 29, 2004 2:43 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: SYSPACKSTMT and Rexx



I am trying to parse the SQL contained in SYSIBM.SYSPACKSTMT using a rexx procedure. I believe that the first 8 bytes of a set of rows for a statement contain the total length of the statement followed by the length of the entered SQL. I perform a SUBSTR to extract the two lengths from the STMT column and have been unable to convert the length into the decimal equivilant. the command x = x2d(var1) results in the message "Incorrect [...] 3417 78 41_Re: Elementary question on pre-processing16_Graeme St. Clair23_Graeme.St.Clair@HDS.COM31_Tue, 29 Jun 2004 14:31:09 -0700547_iso-8859-1 FTP is all very well, but...

My sysprog and my customer and I have just been thru a 2 to 3 day exercise, trying to find a way to move these materials around by e-mail. An IEBCOPY UNLOAD results in a RECFM=VS file, whether you like that or not. Our official company PC FTP pgm (WS_FTP) has no idea what to do with the VS RDW's and just uploads them as well. Then when you download back to MVS, it robotically creates files with FB 128 6144 including RDW's and all - and if you pre-allocate the receiving files appropriately, [...] 3496 19 17_BLOB manipulation10_Terry Lake18_terry.lake@EDS.COM31_Tue, 29 Jun 2004 16:24:28 -0500537_- I have a client that is developing an application that will store data in blob columns that are greater than 32K in size. This client is struggling with replicating this blob test data between numerous db2 environments. All my research indicates that the only way to move blob data of this size in and out of db2 is via a user written program. I know of no IBM or 3rd party utility that will do this. Are there other methods to do a load/unload? Also, how are other companies handling blob data? We are on DB2 v7 for z/OS. Thanks [...] 3516 67 41_Re: Elementary question on pre-processing13_Charles Mills16_charlesm@MCN.ORG31_Tue, 29 Jun 2004 14:48:22 -0700340_us-ascii We're getting ever so slightly OT here but (1) we will be FTPing 390 to 390 so hopefully will not be a problem; and (2) the trick for FTPing 390 PDSes is to use the TSO TRANSMIT command to make the PDS into an FB 80/3120 dataset (you must pre-allocate it that way) and then use TSO RECEIVE at the other end to reconstruct it. [...] 3584 171 56_Re: Error allocating a DSSIZE 8 G partitioned tablespace13_Horacio Villa17_hvilla@AR.IBM.COM31_Tue, 29 Jun 2004 18:55:59 -0300492_US-ASCII Marcel,

the problem was in the storage class. They changed Sustained Data Rate, wich had a value of 15 to "nothing" and it worked. I'd like to understand what that value means, because all our DB2 VSAM dataset have that storage class assigned. I didn't get much input from the storage guys, and they want to change all our datasets to the new storage class. Is it possible that without a dataclass, it means nothing? Thanks very much to all who responded to my question. [...] 3756 49 21_Re: BLOB manipulation13_Bright, Randy20_Randy_Bright@BMC.COM31_Tue, 29 Jun 2004 19:38:48 -0500322_- The latest version of the Database Administration for DB2 Solution from BMC Software, Inc. will migrate LOB data up to 32M (that's "M" as in "Meg") in length. If you are copying selected LOB data from production to test databases or systems, this is your solution. If you want more information, contact me offline. [...] 3806 45 19_Re: Opthint Problem12_John Piccoli18_jpiccoli@COGECO.CA31_Tue, 29 Jun 2004 22:24:16 -0400519_iso-8859-1 Ford,

The OPTHINT is accepted only when all rows in the query block are updated with the OPTHINT name.

Thanks John

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Ford Wong Sent: Tuesday, June 29, 2004 12:14 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Opthint Problem



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? [...] 3852 75 30_Re: GROUP BY versus ORDER BY ?14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 30 Jun 2004 08:53:03 +0100358_iso-8859-1 In general ORDER BY should be cheaper than GROUP BY, but don't forget that GROUP BY removes duplicates.

ALSO..... There is NO implied order to a GROUP BY statement, if the result set arrives in the order of the grouped column(s) then that's just coincidence. To be 100% sure you should have BOTH GROUP BY and ORDER BY - just a thought [...] 3928 168 33_Re: Estimating DB2 Recovery Times9_Gary Hope27_Gary.Hope@MORGANSTANLEY.COM31_Wed, 30 Jun 2004 10:59:26 +0100570_us-ascii Many thanks for all the replies on this subject. I have plenty of things to think about so I will run few tests and decide what is the best method for my purposes and report back on the outcome.

regards

Gary Hope

Ed Long wrote:

> Indeed. > That is why I included the caveat > (total pages counting indices). > The elapsed time would be for the entire table > (tablespace(s) and indices). > --- Seth Bienstock wrote: > > Don't forget to account for INDEX RECOVERY ! > > > > > -----Original Message----- > > > [...] 4097 235 44_Re: EXPDL option on a Template for a Dataset13_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Wed, 30 Jun 2004 07:24:14 -0500798_US-ASCII Thank You for all of the responses, I have tried all of the ways that have been suggested '1999000' and '1999007'. I even went as far as trying it without quotes and received the following error:

1DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = MK2W0990.JP010 DSNU050I DSNUGDF3 - LISTDEF TSLIST INCLUDE TABLESPACE MK2D0001.MK2SP001 PAR MK2D0001.MK2SP002 PARTLEVEL ALL INCLUDE TABLESPACE MK2D0001.MK2SP003 PARTLEVEL DSNU050I DSNUGDF3 - TEMPLATE UNLDDS DSN('ATEST.&SS..&DB..&TS..P&PART.(+1)') CATLG,DELETE) GDGLIMIT(5) DSNU049I DSNUGDF3 - INVALID OPERAND '1999007' FOR KEYWORD 'EXPDL' DSNU050I DSNUGDFL - TEMPLATE PUNCHDS DSN(ATEST.&SS..ULP.&DB..&TS..SYSPUNCH) DELETE,DELETE) DSNU050I DSNUGDFL - UNLOAD LIST TSLIST PUNCHDDN PUNCHDS UNLDDN UNLDDS SHRLE DSNU012I DSNUGBAC - [...] 4333 71 32_Lob-reorg & sysibm.syscopy-table14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Wed, 30 Jun 2004 14:49:04 +0200712_iso-8859-1 Hi,

We are investigating a reorg-problem on an z/os 1.4 & db2 v6.1 system.

I've reorged a lob-tablespace. Questions : 1) I can't find a record in SYSIBM.SYSCOPY with ictype REORG ? 2) Is there another indication in the db2-catalog ?

greetings,

Patrick Steurs DBA at National Bank of Belgium Tel : 02/2215384







----------------------------------------- Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do [...] 4405 64 32_DB2 OS/390 & DB2 UDB For Windows16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU31_Wed, 30 Jun 2004 08:47:07 -0400488_iso-8859-1 Hey Folks... I've been tasked to see what the operational differences are between the above. We currently run DB2 OS/390 with much success. Now, management wants to know if we move from the mainframe platform to a LINUX, AIX or Windows arena, if we'd get the same performance, uptime, etc. from this. We are a medium sized shop that does about a million transactions/day with an approximate breakdown of 15% update, insert, delete and the rest query. Thanks in advance... [...] 4470 207 36_Re: Lob-reorg & sysibm.syscopy-table12_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Wed, 30 Jun 2004 08:57:43 -0400482_iso-8859-1 Reorg of a LOB tablespace is quite different than a "normal" reorg.

First the reorg only does an in place reorg, it does not delete the tablespace and reallocate. Therefore you can not regain extents by doing a reorg. Also the output of the reorg does not look like a normal reorg either. I also do not believe that a row is put in to SYSCOPY because there is not a recovery point. I am sure there are other differences but I can not think of them right now. [...] 4678 228 36_AW: Lob-reorg & sysibm.syscopy-table12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 30 Jun 2004 15:24:41 +0200395_iso-8859-1 Patrick,

AFAIK you can find some information on this in SYSLOBSTATS and especially in SYSLOBSTATS_HIST. In the last table mentioned you'll find a column ORGRATIO.

Desciption: ORGRATION is the ratio of organization in a LOB tablespace. The greater this value exceeds 1 the more disorganized is the LOB tablespace.

May be that is what you are looking for..... [...] 4907 191 36_Re: Lob-reorg & sysibm.syscopy-table14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Wed, 30 Jun 2004 15:43:57 +0200703_iso-8859-1 Peter,

I was looking for a db2 catalog table where you could verify that a "reorg lob-tablespace" has been executed.

greetings

Patrick Steurs

-----Original Message----- From: Peter, Georg [mailto:G.Peter@DZBW.DE] Sent: Wednesday, June 30, 2004 15:25 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: AW: Lob-reorg & sysibm.syscopy-table



Patrick,

AFAIK you can find some information on this in SYSLOBSTATS and especially in SYSLOBSTATS_HIST. In the last table mentioned you'll find a column ORGRATIO. Desciption: ORGRATION is the ratio of organization in a LOB tablespace. The greater this value exceeds 1 the more disorganized is the LOB tablespace. [...] 5099 71 21_Re: BLOB manipulation14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 30 Jun 2004 15:10:56 +0100608_iso-8859-1 Terry

If manipulating LOBs is an immediate problem for you, please consider joining the beta program for CAs newest release of DB2 tools. In readiness for our customers running DB2 Version 8 in a 64-bit environment, our Fast Load and Fast Unload utilities have been enhanced to support access to LOB data up to the MAXIMUM SIZES ALLOWED BY DB2. Unfortunately, there will be some limitations when running in a 31-bit environment as it's difficulty to materialize a 2Gb LOB column in an address space that is itself limited to 2Gb of addressable virtual storage, but you should still be [...] 5171 47 32_DB2 UDB for AIX - reorg question0_18_mflavell@I-TCS.COM31_Wed, 30 Jun 2004 15:26:24 +0100335_US-ASCII Hi,

We are running DB2 for AIX v7.2. I am running table reorgs overnight, but last night it seems that one of our applications was prevented from running during the time of the reorgs. I haven't been able to find any reference in the manuals about whether reorgs have to run stand alone or not. Does any one know? [...] 5219 282 44_Re: EXPDL option on a Template for a Dataset22_Provenzola, Tony (ETW)24_Tony.Provenzola@NIKE.COM31_Wed, 30 Jun 2004 07:51:08 -0700365_iso-8859-1 What happened when you tried the 4-digit year with the quotes? I ran EXPDL 1999007 and got the same error you did. I ran EXPDL '1999000' and EXPDL '1999007', and they both ran successfully,

Tony Provenzola Nike Database Services Venturi Technology Partners, Consulting Phone * 503-532-0772 Fax * 503-532-3223 Email * Tony.Provenzola@nike.com [...] 5502 29 36_JDBC - Schema of unqualified tables.3_DBA17_dba_udb@YAHOO.COM31_Wed, 30 Jun 2004 09:50:48 -0500601_- We have an application running on a linux server on intel platform trying to access DB2 on z/OS through JDBC.

Our tables are defined with schema SCH1 and the application connects using user ID "USR1".

My question is how do we qualify the unqualified tables with JDBC connectivity. We could have done with "qualifier" parameter of BIND statement in case of static or dynamic sqls. I came across a JDBC property called "Current SQLID" which can be used to set the schema of the tables. However the behaviour of 'current sqlid' is not same as the setting schema through 'qualifier'. [...] 5532 67 36_Re: DB2 UDB for AIX - reorg question13_Vickers, Mark22_Mark.Vickers@ANICO.COM31_Wed, 30 Jun 2004 10:02:59 -0500441_iso-8859-1 Martin, On z/Os you control this via the SHRLEVEL option - the default is SHRLEVEL NONE, so if you want an online reorg you must use SHRLEVEL CHANGE, and you also need to create a shadow table - detailed in the Utilities manual.

-----Original Message----- From: mflavell@I-TCS.COM [mailto:mflavell@I-TCS.COM] Sent: Wednesday, June 30, 2004 9:26 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 UDB for AIX - reorg question [...] 5600 307 44_Re: EXPDL option on a Template for a Dataset13_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Wed, 30 Jun 2004 10:09:59 -0500407_US-ASCII Thank You for all of your responses. We have discovered that we need to apply an apar PQ60860.

Once again Thank you for all of your help.

Bob





"Provenzola, Tony (ETW)" T cc:

Sent by: DB2 Data Base bcc: Discussion List Subject: Re: EXPDL option on a Template for a Dataset [...] 5908 13 38_Managing LOBs, BLOBs, and CLOBs on zOS12_Doug Breland31_douglas.l.breland@ACCENTURE.COM31_Wed, 30 Jun 2004 09:59:00 -0500725_- Does anyone have any experience with BLOBs (>1M)? With the limitations of zOS dataset record lengths and lack of utilities to support management of BLOB tables, we are looking at user written utilities to replicate the data, read and insert. Any ideas?

--------------------------------------------------------------------------------- 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 5922 55 42_Re: Managing LOBs, BLOBs, and CLOBs on zOS14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 30 Jun 2004 16:16:57 +0100390_iso-8859-1 Doug

It seems LOBs are the flavour of the day today!

Please see my earlier answer on a very similar topic, and if you'd like to join the CA beta program to help assure our support for LOB manipulation in utilities (and at the same time getting Load and Unload for LOBs approaching their maximum allowable length), please drop me a line at phil.grainger@ca.com [...] 5978 42 42_Re: Managing LOBs, BLOBs, and CLOBs on zOS14_Laredo, Andrew19_Andy_Laredo@BMC.COM31_Wed, 30 Jun 2004 10:25:42 -0500380_iso-8859-1 BMC's Database Administration Solution ... as mentioned by Randy Bright the other day...

"The latest version of the Database Administration for DB2 Solution from BMC Software, Inc. will migrate LOB data up to 32M (that's "M" as in "Meg") in length. If you are copying selected LOB data from production to test databases or systems, this is your solution. " [...] 6021 48 35_Re: Errors during Accounting Report7_db2sysc17_db2sysc@YAHOO.COM31_Wed, 30 Jun 2004 11:25:59 -0500706_- Hey Joyce.

Thanks for responding.

I ran the sort card below, and then ran acct file option, Still getting the same Informational messages.

Are you guys using db2pm in yur shop? Could I contact you offline?

Thanks

On Tue, 29 Jun 2004 08:54:04 -0500, Joyce Lindow wrote:

>regarding sorting smf records, any sort program will work. >here are same control cards for syncsort that will extract the DB2 records >(hex 64,65,66) and sort by date,time. >INCLUDE COND=((6,1,BI,EQ,X'64'), > OR,(6,1,BI,EQ,X'65'), > OR,(6,1,BI,EQ,X'66')) >SORT FIELDS=(11,4,A, > 7,4,A), > FORMAT=BI >SMF records are VBS so to keep things simple I use a [...] 6070 62 40_Re: JDBC - Schema of unqualified tables.15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Wed, 30 Jun 2004 18:47:10 +0200457_us-ascii Not sure if I understand what you mean, but we use the following BIND-statement if we want to force the access through JDBC to a particular schema:

BIND PACKAGE(...) MEMBER(DSNJDBCn) QUALIFIER(...) DYNAMICRULES(BIND).

The plan through which access takes is place can be specified in the JDBC properties (correct me if I'm wrong) so I end up with a few plans, each of which points to a different schema through the collection-id. [...] 6133 64 40_Re: JDBC - Schema of unqualified tables.14_Hickman, Mindy29_Mindy.Hickman@DOA.STATE.WI.US31_Wed, 30 Jun 2004 12:19:37 -0500345_- You can switch to SQLJ so that you can do the bind or what we have typically done here is to create a .properties file and store the schema or high level qualifier in the properties file. Then we read that value into a variable that is cached in the application. Here is an example of a SQL statement that I'm using that implements this: [...] 6198 62 30_Re: GROUP BY versus ORDER BY ?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 30 Jun 2004 20:31:52 +0200454_- Hi,

My guess is that originally it was "select distinct" and was changed later to the group by to avoid the sort. If the answer is distinct (only one row) then this is the case.



Isaac Yassin



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter, Georg Sent: Tuesday, June 29, 2004 5:11 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: GROUP BY versus ORDER BY ? [...] 6261 140 35_DB2 Personal Edition - Installation12_McKown, John26_john.mckown@UICIINSCTR.COM31_Wed, 30 Jun 2004 12:57:43 -0500577_us-ascii I just downloaded the DB2 Personal Edition V8.1 from IBM. I'm trying to install it under Windows2000 here at work. I have administrator rights to my local PC. In the "Set user information for the DB2 Administration Server", I used my Windows userid and password. I selected "Prepare the DB2 tools catalog and warehouse control database" on the "Select the metadata you want to prepare" screen. I used my Windows userid & password on the "Specify a local database to store the DB2 metadata" (Instance = DB2 Database=DWCTRLDB). Eventually, I get the error message: [...] 6402 189 39_Re: DB2 Personal Edition - Installation15_Leon Katsnelson15_leon@CA.IBM.COM31_Wed, 30 Jun 2004 14:05:41 -0400373_US-ASCII "JOHN.MCKOWN" really is not a valid id in V8.1 because it contains a period. It will be valid in DB2 "Stinger" release.

(Embedded image Leon Katsnelson moved to file: Manager, DB2 Product Management and Planning. pic07123.gif)Get tel: (905) 413-2119 DB2 Stinger Open Admin Assistant: Cynthia Thomson (905) 413-3632 Beta http://www.ibm.c om/db2/stinger [...] 6592 22 37_Beginning to use real time statistics0_34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Wed, 30 Jun 2004 14:19:19 -0500319_us-ascii It looks to me like you need to do a reorganization on all your tablespace objects soon after the first time you start real-time statistics. If you do not do this, the columns that indicate that a reorganization is required will remain in a null state.

Can anyone confirm or deny this observation? [...] 6615 12 21_Re: BLOB manipulation10_Terry Lake18_terry.lake@EDS.COM31_Wed, 30 Jun 2004 14:16:33 -0500527_- Thanks. I'll check with our respective reps.

TL

--------------------------------------------------------------------------------- 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 6628 57 41_Re: Beginning to use real time statistics12_John Lendman27_lendman@PALMBEACH.K12.FL.US31_Wed, 30 Jun 2004 15:25:03 -0400461_iso-8859-1 That is correct. The RTS will be empty when you start it, so it you want to use it for predicting reorgs,you have to run a reorg on everything at least once. Same goes for Image copy, runstats, etc.

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 [...] 6686 26 36_Re: DB2 UDB for AIX - reorg question16_Ian D. Bjorhovde23_ianbjor@MOBILEAUDIO.COM31_Wed, 30 Jun 2004 12:27:22 -0700368_us-ascii mflavell@I-TCS.COM wrote: > Hi, > > We are running DB2 for AIX v7.2. > I am running table reorgs overnight, but last night it seems that one of > our applications was prevented from running during the time of the reorgs. > I haven't been able to find any reference in the manuals about whether > reorgs have to run stand alone or not. Does any one know? [...] 6713 43 34_Outer Join and null host variables22_KenParis@austin.rr.com22_KenParis@AUSTIN.RR.COM31_Wed, 30 Jun 2004 14:59:45 -0500374_- The following is an SQL statement in a SQL Stored Procedure.

SELECT T.NAME, C.NAME INTO TBLNAMEOUT, COLNAMEOUT FROM SYSIBM.SYSTABLES T LEFT JOIN SYSIBM.SYSCOLUMNS C ON T.NAME = C.TBNAME AND C.NAME = COLNAMEIN WHERE T.NAME = TBLNAMEIN WITH UR;

In this catalog there is only one table named INDV (which is why I've omitted the CREATOR/TBCREATOR columns). [...] 6757 52 39_Re: DB2 Personal Edition - Installation12_McKown, John26_john.mckown@UICIINSCTR.COM31_Wed, 30 Jun 2004 15:21:35 -0500250_us-ascii In other words, I'm dead? I don't want to logon to Windows as "db2admin" because then I don't have access to the LAN or email or ... . Or is there a way for me to logon to the LAN as john.mckown, but then logon to DB2 PE as "db2admin"? [...] 6810 213 39_Re: DB2 Personal Edition - Installation10_Jwkwan200025_jwkwan2000@HOUSTON.RR.COM31_Wed, 30 Jun 2004 16:21:05 -0500694_iso-8859-1 Nor does it like a space in between. "JOHN MCKOWN'

James Kwan ----- Original Message ----- From: "Leon Katsnelson" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, June 30, 2004 1:05 PM Subject: Re: DB2 Personal Edition - Installation



> "JOHN.MCKOWN" really is not a valid id in V8.1 because it contains a > period. It will be valid in DB2 "Stinger" release. > > (Embedded image Leon Katsnelson > moved to file: Manager, DB2 Product Management and Planning. > pic07123.gif)Get tel: (905) 413-2119 > DB2 Stinger Open Admin Assistant: Cynthia Thomson (905) 413-3632 > Beta > http://www.ibm.c > om/db2/stinger > [...] 7024 89 38_Re: Outer Join and null host variables16_Tink@BLTysor.com19_tinktysor@YAHOO.COM31_Wed, 30 Jun 2004 14:49:59 -0700585_us-ascii Hi Ken,

Try taking "AND C.NAME = COLNAMEIN" out of the ON clause and put it in the WHERE clause.

Tink --- "KenParis@austin.rr.com" wrote: > The following is an SQL statement in a SQL Stored > Procedure. > > SELECT T.NAME, C.NAME > INTO TBLNAMEOUT, COLNAMEOUT > FROM SYSIBM.SYSTABLES T > LEFT JOIN SYSIBM.SYSCOLUMNS C > ON T.NAME = C.TBNAME > AND C.NAME = COLNAMEIN > WHERE T.NAME = TBLNAMEIN > WITH UR; > > In this catalog there is only one table named INDV > (which is why I've > omitted the CREATOR/TBCREATOR columns). > > When I [...] 7114 78 35_Re: Errors during Accounting Report12_Joyce Lindow30_Joyce.Lindow@FIRSTDATACORP.COM31_Wed, 30 Jun 2004 17:03:44 -0500294_US-ASCII I'm not working with DB2PM and haven't for a while but if I recall, it does an internal sort for you already unless you tell it not to (check the PM manual- I think you specify to not sort in the Globals section. ) So I don't think your problem is due to records out of sequence. [...] 7193 51 18_Consultant Opening12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Wed, 30 Jun 2004 22:50:56 +0000633_- Dear Subscribers, I have an immediate opening for a senior DB2 UDB DBA with experience tuning SQL on DB2 UDB for Linux, UNIX, and Windows. Consultant would conduct onsite and remote tuning engagements at client sites throughout the US. Other duties would be general DB2 knowledge transfer, Database Administration, index redesign, V8 migrations, onsite performance and tuning support. Travel: Travel is anywhere from 0 to 100%. My target is 50-70% travel but that varies. I am building more remote consulting business. Work out of your home for remote consulting and travel to client sites for onsite consulting from you home [...] 7245 54 42_Re: Managing LOBs, BLOBs, and CLOBs on zOS33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE30_Thu, 1 Jul 2004 00:58:38 +0200382_iso-8859-1 We've been using BLOB tables since two years and I remember a lot of problems in this area. We always use a shadow table/tablespace. Our BLOB can be much larger then 32M well it's a BLOB.

So to replicate data 1. Create a shadow table/tablespace 2. INSERT INTO SELECT FROM (well a where for a rowid is good for corrupted BLOB's) 3. STOP Tablespace 4. DSN1COPY [...] 7300 123 38_Re: Outer Join and null host variables11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 30 Jun 2004 18:01:54 -0500493_- Tink - I think the OJ giving nulls on the right table could be explained by the ON vs WHERE. Here, it is a not found altogether, so I think this is independent of the OJ.

Ken - I am not sure what you mean by "If we execute this statement and > COLNAMEIN is null"...I asume this is a host variable. If this is the case, the only way to do this (I think) is to specifiy the IS NULL clause. I think you will have the same issue even without the outer join. To prove the point, just [...] 7424 155 36_Re: DB2 OS/390 & DB2 UDB For Windows17_CUTHBERTSON, John31_John.CUTHBERTSON@DOIR.WA.GOV.AU30_Thu, 1 Jul 2004 09:15:49 +0800360_iso-8859-1 I have been consulting to an Organisation that has done just this over the last 2 years. They were originally a mainframe site running CICS/COBOL/DB2 with an emerging volume of NT based Websphere and Powerbuilder applications. They also had 18 remote sites that were running DB2 NT servers that were replicated to every 2 minutes (180 tables). [...]