1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2007, week 4
2 95 15_Happy Holidays!7_Kim May28_kim.may@THEFILLMOREGROUP.COM31_Fri, 21 Dec 2007 19:24:25 -0500732_us-ascii If you get the chance, stop by our website (http://www.thefillmoregroup.com) for a humorous take on the upcoming holiday.
And to all a Happy, Healthy, and Prosperous New Year!
Kim May, VP of Business Development The Fillmore Group, Inc. (410) 465-6335 office (443) 956-0288 mobile www.thefillmoregroup.com
The Fillmore Group, Inc., an IBM Business Partner that specializes in DB2, has provided technical services to clients worldwide since 1987. Led by DB2 Gold Consultant Frank Fillmore, The Fillmore Group offers relational database services including analysis, architecture, implementation and tuning. The Fillmore Group delivers IBM authorized DB2 training. [...]
98 29 37_GALLAGHER, BILL is out of the office.0_28_BILL.GALLAGHER@PHOENIXWM.COM31_Fri, 21 Dec 2007 21:59:55 -0500817_US-ASCII I will be out of the office starting 12/21/2007 and will not return until 12/27/2007.
If you need immediate assistance, please contact Jean Williams at x5945.
----------------------------------------- ******************************************************************* CONFIDENTIAL: This communication, including attachments, is intended only for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, you are hereby notified that you have received this document in error, and any use, review, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and [...]
128 56 24_Re: Fast Unload Problem.12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Fri, 21 Dec 2007 21:56:24 -0800576_iso-8859-1 Yes, I see an online reorg run'ed recently on the tablespace that is in question and changed the I0001 to J0001. But , our shop is not controlling VSAM accesses on the J0001 level of the dataset.
But thanks for the info.
Ayalew.
Chris Hoelscher wrote: just a thought - is it possible there was an online reorg done that changed one of the nodes from J0001 to I0001 and for some reason there was an obscure rule that allowed read access to all VSAM datasets with a J0001 in the 5th node??? not likely - but the real [...]
185 57 51_Re: Input on best option for Year End data capture.0_25_jacampbell@ACSLINK.NET.AU31_Sat, 22 Dec 2007 17:35:39 +1100556_ISO-8859-1 What version of z/OS and what disk technology are you using? Can you stop db2d (or at least quiesce write to force all updates to disk) and do a DFSMS COPY CONCURRENT - hopefully utilising a SnapShot - on the tablespaces, then use DSN1COPY on the copies?
Otherwise, I'd suggest a log analysis tool to extract the updates from 29Dec to 31Dec (we write dates as day/month down here in Oz). One advantage of this is that you can run the log analysis after the event, so provided you keep the archive logs you can run this next year. Of [...]
243 137 24_Re: Fast Unload Problem.12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Fri, 21 Dec 2007 22:36:19 -0800321_iso-8859-1 Hello Avram,
we control VSAM access on the first 8 character of the VSAM dataset which is the subsystem name + Facility code + system code ( for example Z1TTZTFF). This means that we have Z1TTZTFF acf2 rule for this application that controls VSAM access. This rule did not change since April 2007. [...]
381 299 51_Re: Input on best option for Year End data capture.13_Michael Ebert18_mebert@AMADEUS.COM31_Sat, 22 Dec 2007 08:31:08 +0100550_ISO-8859-1 Hi Suzanne,
how big and how active are your tablespaces? With luck you don't have to copy all of them at the same time. Unfortunately it is not quite trivial to find out this information. There are several possibilities. A couple of years ago I posted a SAS program to parse the SYSLGRNX VSAM file, extracting the info when each TS is active. I guess it should be on the IDUG Code place (I'm a bit out of touch after doing mostly Oracle for 2 years). Another option is to run REPORT RECOVERY; however I've never used that so I [...]
681 26 59_David Petronella/FlorhamPark/Pershing is out of the office.0_24_dpetronella@PERSHING.COM31_Sat, 22 Dec 2007 12:47:49 -0500727_US-ASCII I will be out of the office starting 12/22/2007 and will not return until 01/01/2008.
I will respond to your message when I return.
For all DBA application-related issues please contact Peter Campbell on x7725, Andy Goodman on x4932, Steve Eng on x4963 or Al DiGiovanni on x4920.
****************************************************** IMPORTANT: Any information contained in this communication is intended for the use of the named individual or entity. All information contained in this communication is not intended or construed as an offer, solicitation, or a recommendation to purchase any security. Advice, suggestions or views presented in this communication are not necessarily [...]
708 153 51_Re: Input on best option for Year End data capture.14_Grainger, Phil20_Phil.Grainger@CA.COM31_Sat, 22 Dec 2007 20:09:41 -0000455_iso-8859-1 Suzanne
Just run another DSN1COPY from your incremental (SYSUT1) to the "temp" on-DASD versions (SYSUT2)
DSN1COPY will overwrite the necessary pages, so far as I remember
Phil G CA
________________________________
From: DB2 Data Base Discussion List on behalf of Nichols, Suzanne Sent: Fri 21/12/2007 21:31 To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Input on best option for Year End data capture. [...]
862 197 51_Re: Input on best option for Year End data capture.15_Kalena, Michael16_MKALENA@BEAR.COM31_Sat, 22 Dec 2007 15:21:29 -0500626_us-ascii Suzanne,
I didn't realize DSN1COPY could do this either but we're going to try it too. I found the doc is very good:
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnugh18/3.7.1.2?SHE LF=&DT=20061201100238
Michael Kalena 973-793-2133 mkalena@bear.com
DB2 Info Page at BSC (http://whsysops1/db2/)
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: Saturday, December 22, 2007 3:10 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Input on best option for Year End data capture. [...]
1060 64 24_Re: Fast Unload Problem.0_17_JTonchick@AOL.COM29_Sat, 22 Dec 2007 15:45:50 EST365_US-ASCII If the error was an ACF2 s913, then either the rule covering the dataset alias (highlevel) must have changed or the dataset name must have changed. Either way you need to talk to the ACF2 administrators or someone with ACF2 AUDIT authority to decompile the rule set and see exactly how it is coded. Only then can you start to figure out what and why. [...]
1125 56 24_Re: Fast Unload Problem.12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Sat, 22 Dec 2007 22:15:34 -0800650_iso-8859-1 Hello Jim, The job did not get s913 ACF2 error. Below is the error that I noticed in th job.
ACF99913 ACF2 VIOLATION-00,16,Z1OT8109,P10277, VSAM dataset name ACF95913 -AMS/CATALOG FUNCTION SUPPRESSED;
Thanks for the info.
Ayalew Kassa.
JTonchick@AOL.COM wrote: If the error was an ACF2 s913, then either the rule covering the dataset alias (highlevel) must have changed or the dataset name must have changed. Either way you need to talk to the ACF2 administrators or someone with ACF2 AUDIT authority to decompile the rule set and see exactly how it is coded. Only then can you start to figure out [...]
1182 81 24_Re: Fast Unload Problem.0_17_JTonchick@AOL.COM29_Sun, 23 Dec 2007 21:48:07 EST402_US-ASCII ACF99913 is a security violation error message. You need to talk to the ACF2 administrators to find out what changed. The "16" says that the rule explicitly prevents access. Since the job worked in the past, either the ACF2 rules changed or the table was moved to a new tablespace or a new index was created over the table resulting in a new dataset name not explicitly listed in a rule. [...]
1264 28 65_CN=Radhakrishnan Gopinath/OU=CHE/O=INeFunds is out of the office.0_36_Radhakrishnan.Gopinath@IN.EFUNDS.COM31_Mon, 24 Dec 2007 08:35:37 +0530454_US-ASCII I will be out of the office starting 12/24/2007 and will not return until 12/31/2007.
My backup details for the period 24th Dec - 31st Dec are as below.
Online Certification will be Chandra - ChandraSekhar V/CHE/INeFunds - 414-341-3737 Extn:8641, Group Email ID - SDC_Certification_Team
Offline Certification will be Ponz, - Ponmozhi T/CHE/INeFunds - 414-341-3737 Extn:8642 - Group Email ID - SDC_Offline_Certification [...]
1293 382 19_Re: BMC Load and RI14_Alexander John28_JAlexander@WOOLWORTHS.COM.AU31_Mon, 24 Dec 2007 17:57:55 +1100339_us-ascii Really appreciate all the feedbacks.
I am not sure if we can run a higher BMC Load version than V83 with DB2 V7, but I will check it out, but even if we can, I do not think our DB2 Sysprog would install it now, he just upgraded all our BMC utilities in preparation for DB2 V8 upgrade, which is long over due for us!!. [...]
1676 12 4_TEST13_Eugene Renico20_Eugene.Renico@53.COM31_Wed, 26 Dec 2007 15:15:26 +0000514_utf-8 TEST - Please do not respond
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
1689 15 46_Mark Maziarczyk/TOR2/SYM is out of the office.0_22_MMaziarczyk@SYMCOR.COM31_Wed, 26 Dec 2007 16:05:19 -0500638_US-ASCII I will be out of the office starting 12/24/2007 and will not return until 12/27/2007.
If you need immediate assistance please contact the Lotus Notes Group Archive Development and Support or contact the CNS Service Desk.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership [...]
1705 422 19_Re: BMC Load and RI13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 27 Dec 2007 08:33:01 -0000477_us-ascii Hey Randy,
Apologies for stealing your thunder. Just got a bit trigger-happy mentioning the feature. Won't happen again (yeah, right).
John,
I think I mentioned before (not sure) but V9.1 of LoadPlus is fine with DB2 V7, V8 and 9. So, other pressures permitting, you certainly could upgrade LoadPlus while at your current DB2 version. Although I suspect getting the DB2 migration complete is more important - six months to go and counting... [...]
2128 185 26_Re: BMCSTATS for Partition11_Nirmal Doss21_nirmal.doss@GMAIL.COM31_Thu, 27 Dec 2007 18:01:47 +0530483_ISO-8859-1 Thanks John
On 12/17/07, Alexander John wrote: > > Hi Nirmal, > > A few weeks ago I was after the same thing, & found that I can add > PARTITION X any where in the command, the syntax I use is; > > > > BMCSTATS TABLESPACE DBNAM.TSNAM PARTITION X > > TABLE ALL > > INDEX Y > > SAVESTATS Y > > REPORT Y > > DELETEAGE 370 > > UPDATEDB2STATS Y > > SPACEONLY N > > RECALL Y > > TASKS 5 > > BUFFERS 500 > > IMAGECOPY N > > GROUPSIZE 0 [...]
2314 152 29_[DB2 V8 z/OS] SYSSTAT (again)14_Lyon, Lockwood20_Lockwood.Lyon@53.COM31_Thu, 27 Dec 2007 13:12:23 -0500332_us-ascii Dear Esteemed List:
I've got the same issue that Lisa did back in '06 ... I'm supporting a Websphere-based vendor package that is heavily dependent on accessing DB2 z/OS data thru Stored Procedures. We see a large percentage of elapsed time and a large # of GetPages associated with the NULLID.SYSSTAT package. [...]
2467 116 33_Re: [DB2 V8 z/OS] SYSSTAT (again)14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Thu, 27 Dec 2007 18:26:29 +0000425_utf-8 Lock Below is a recent (this year) Q&A from IBMlink on this topic. Hope I am not abusing IBM's copyrights, some times its hard to figure out. Happy Holidays
Av
Source..........: CA CA Last updated....: 20070329 Abstract........: What is DB2 Object NULLID.SYSSTAT?
SUMMARY: What is DB2 Object NULLID.SYSSTAT?
Product Name Version/Release Maintenance Level DB2 for z/OS and OS/390 Ver7.1 [...]
2584 182 68_Re: [DB2 V7 z/OS] SQL problem - generated dates to be joined further0_26_MarkVickers@GROCERYBIZ.COM31_Thu, 27 Dec 2007 15:01:18 -0600439_US-ASCII We are still on v7 so I cannot use recursive SQL which seems to be better solution for this.
Problem: for the next 7 days, we need to get a list of any alternate delivery routings and that resultset needs to be joined to more tables, so we either need a massive join or I was thinking along the lines of using a created global temp table and or stored procedure. This is going to be called thousands of times per day. [...]
2767 303 68_Re: [DB2 V7 z/OS] SQL problem - generated dates to be joined further0_24_Mohammad_Khan@BCBSIL.COM31_Thu, 27 Dec 2007 15:34:08 -0600371_us-ascii Why not simplify the SQL as follows:
select dayofweek_iso(DTE), DTE, ALT from WW44G.WCSSTART where DTE between current_date and (current_date + 6 day) and ALT is not null ;
This will return only the rows with alternate routes in the date range. Unless there is any specific need for a temp table, this can very well be joined to other tables. [...]
3071 80 33_Re: [DB2 V8 z/OS] SYSSTAT (again)12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 27 Dec 2007 15:14:41 -0800292_iso-8859-1 From a tuning point of view, there's not much you can do other than ensuring capturing the SQL issued and making sure that the catalog has indexes to support that SQL. IBM is in the process of rewriting some of these procedures, PTF to be released sometime late in Jan. 2008. [...]
3152 247 30_Uncataloged image copy dataset14_Alexander John28_JAlexander@WOOLWORTHS.COM.AU31_Fri, 28 Dec 2007 17:32:10 +1100507_us-ascii Hi DB2 Colleagues,
Does anyone know why DB2 allows reusing a cartridge image copy dataset that has DISP=(NEW,KEEP) more than once, without having to run a MODIFY ? ...this is definitely a good Bug to have in DB2!..
Normally you'll get messages DSNU407I & DSNU408I when reusing a previously cataloged (even if it does not exist) cart image copy dataset, however we have been running Adhoc DBA online reorgs with the following copy dataset more than once, and the job works fine ! [...]
3400 158 34_Re: Uncataloged image copy dataset11_Gerard News24_gerard.le.roy@WANADOO.FR31_Fri, 28 Dec 2007 09:07:09 +0100771_iso-8859-1 Hi Alexander,
That’s not possible.
Have a look on SYSIBM.SYSCOPY.
There is DSNUCX01 a UNIQUE INDEX on SYSIBM.SYSCOPY(DSNAME).
Regards
Gerard
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services [...]
3559 53 34_Re: Uncataloged image copy dataset13_Carol Broyles21_clbroyles55@YAHOO.COM31_Fri, 28 Dec 2007 04:18:59 -0800454_iso-8859-1 Actually it is possible. If your DISP=(NEW,CATLG), then you can't catalog more than one. If your DISP=(NEW,KEEP), then DB2 records the VOLSER and UNIT in SYSCOPY. As long as you don't have the same dataset name on the same VOLSER, DB2 allows it.
Carol
Gerard News wrote: Hi Alexander, That’s not possible. Have a look on SYSIBM.SYSCOPY. There is DSNUCX01 a UNIQUE INDEX on SYSIBM.SYSCOPY(DSNAME). [...]
3613 173 30_Uncataloged image copy dataset17_McCormack, Mark A27_mamccormack@STATESTREET.COM31_Fri, 28 Dec 2007 09:49:20 -0500435_us-ascii Let me expand on Carol's reply.
If you create a cataloged copy file, then DB2 posts to sysibm.syscopy only the dsname, and the volume column in the sysibm.syscopy row is left blank. If you create a copy file that is not cataloged, then DB2 posts both the dsname and volume number(s) in the sysibm.syscopy row. It is the combination of dsname and volume - as posted in the sysibm.syscopy row - that must be unique. [...]
3787 463 68_Re: [DB2 V7 z/OS] SQL problem - generated dates to be joined further0_26_MarkVickers@GROCERYBIZ.COM31_Fri, 28 Dec 2007 10:28:25 -0600466_US-ASCII Thanks Khalid, but the WCSSTART table only contains dates where alternate routing is used, so this returns only those rows: 1 DTE ALT ----------- ---------- ---- 1 2007-12-31 2 2 2008-01-01 3
whereas we need the next 7 days, dates and either the alternate routing route number or a blank to specify normal routing: DOW DTE ALT ------ ---------- ---- 4 2007-12-27 5 2007-12-28 6 2007-12-29 7 2007-12-30 1 2007-12-31 2 2 2008-01-01 3 3 2008-01-02 [...]
4251 251 16_identity columns14_Cheong, Angela24_Angela_Cheong@CTSINC.BIZ31_Fri, 28 Dec 2007 10:20:14 -0600344_US-ASCII We are preparing to move some tables into production using identity column as a primary key (generated always). I'm trying to work out the details of loading/unloading this data from one subsystem to another. I'm trying to use the IDENTITYOVERRIDE parameter, but keep getting an error stating the column itself is not recognized. [...]
4503 103 20_Re: identity columns13_Ohling, Tim R22_Ohling.Tim@CON-WAY.COM31_Fri, 28 Dec 2007 08:45:49 -0800467_us-ascii Since the guy that requested this feature is on vacation this week, here's an example. The identity column is the table_ID (in this case a smallint):
LOAD DATA INDDN SYSREC01 RESUME NO REPLACE STATISTICS TABLE(ALL) INDEX(ALL) LOG NO NOCOPYPEND SORTKEYS 86 IDENTITYOVERRIDE EBCDIC CCSID(0037)
INTO TABLE owner.table_nm ( table_ID POSITION ( 1 ) SMALLINT , next_attribute POSITION ( 3 ) CHAR ( 8 ) , etc_attribute POSITION ( 11 ) CHAR ( 8 ) [...]
4607 173 20_Re: identity columns13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 28 Dec 2007 12:00:08 -0500553_us-ascii Hi Angela,
Which version of DB2 are you using?
If you are not at v8 at least, I suggest you will still have problems. I have not tried the technique you describe. But if it succeeds, you still have the issue of the STARTWITH value. If you have 10,000 rows in your source table and your target table on the other subsystem is newly created, in addition to the steps you describe, you need to create the table with a STARTWITH value of 10001. Otherwise your next insert after the load will attempt to use 1 as the column value [...]
4781 176 68_Re: [DB2 V7 z/OS] SQL problem - generated dates to be joined further0_24_Mohammad_Khan@BCBSIL.COM31_Fri, 28 Dec 2007 11:33:07 -0600316_us-ascii That requirement makes it a little more complicated. If it's a program producing this report, it can supply the missing dates in the final output by sorting it on date. If it has to be a SQL only solution, I'd create a one column table with the seven dates in it and add the following to the main SQL: [...]
4958 421 17_Re: ADMIN: Update16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Fri, 28 Dec 2007 12:34:51 -0500289_iso-8859-1 Its been awhile since the last update, here here it is.
1) The list is STILL up and working correctly. 2) SEARCH. Well right now we are in discussion with the software supporter, as we feel that the search is not running as it should. Have a safe and happy new year [...]
5380 141 20_Re: identity columns14_Cheong, Angela24_Angela_Cheong@CTSINC.BIZ31_Fri, 28 Dec 2007 11:39:33 -0600507_US-ASCII Thank you, that worked.
I was following the utility guide and putting the IDENTITYOVERRIDE parm in the INTO TABLE spec, like this:
INTO TABLE tablename IDENTITYOVERRIDE (chapter 16 page 207 of Utility Guide).
Angela Cheong
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ohling, Tim R Sent: Friday, December 28, 2007 10:46 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] identity columns [...]
5522 388 20_Re: identity columns14_Cheong, Angela24_Angela_Cheong@CTSINC.BIZ31_Fri, 28 Dec 2007 11:58:02 -0600333_US-ASCII Dave,
We are at version 8. This is the information I was looking for. I was able to get the load to work with the IDENTITYOVERRIDE using a GENERATED ALWAYS column definition (thanks to Tim). Now having done that, I should just be able to alter the STARTWITH on my target table, correct? Thank you for your help. [...]
5911 90 20_Re: identity columns12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Fri, 28 Dec 2007 10:21:29 -0800592_iso-8859-1 Angela,
You can issue rest start with command... look the syntax in the Admin Guide. I believe there is a topic in the admin guide on How to load tables with identify column Regards, Ayalew.
"Cheong, Angela" wrote: Dave, We are at version 8. This is the information I was looking for. I was able to get the load to work with the IDENTITYOVERRIDE using a GENERATED ALWAYS column definition (thanks to Tim). Now having done that, I should just be able to alter the STARTWITH on my target table, correct? Thank you for your help. [...]
6002 277 20_Re: identity columns13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 28 Dec 2007 13:42:37 -0500438_us-ascii Yes Angela, You can alter the start with value on your target table.
Here is the actual syntax:
ALTER TABLE STAFFI ALTER COLUMN ID RESTART WITH 10001
The SQL Reference provides the full syntax and description.
Note that you can just specify
ALTER TABLE STAFFI ALTER COLUMN ID RESTART
And the start with value will revert to the original value specified when the column was created. [...]