1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l September 2005, week 5
2 70 41_Re: DB2 7.1 - zOS 1.6 - Security Question13_Bell, Raymond22_raymond.bell@LANDG.COM31_Thu, 29 Sep 2005 09:02:01 +0100550_iso-8859-1 Kevin,
Mr. Campbell has already given you an excellent reply. Just to say at a large number of sites I've been at the 2nd Install Sysadm ID was a RACF (or ACF2) group, while the 1st Install Sysadm ID is an actual user ID. I vaguely remember some particularly low-level activities you need to be the 1st Install Sysadm to perform, but whether that was ever the case or not, I think you'd be remiss not to have a real ID as at least one of the Install Sysadms. As James alludes, if you've a problem with your security management [...]
73 462 40_Re: Max number of tables in a tablespace10_Tee, Chris21_Chris.Tee@EU.SONY.COM31_Thu, 29 Sep 2005 09:25:03 +0100295_- If you created 32767 tablespaces, you would only be able to create one table and no indexes. As the original question was what the max no. of tables was, it's probably 65533 assuming the tables are all in a single tablespace. If you wanted an index on each table, the max would be 21844. [...]
536 71 33_Re: [z/OS] Omegamon & Ifcid Trace12_Cuneyt GOKSU19_cuneytgoksu@USA.NET31_Thu, 29 Sep 2005 12:35:11 +0300363_ISO-8859-1 Ramachandran,
The following phrase is from Historical Reporting Guide OMEGAMON II for DB2 Version 520
'The collected trace data is stored in a sequential dataset. You must use your own reporting tools to display the collected data.'
It explains all, Omegamon does not have this feature, I need a 3rd party (maybe 4th) for it. [...]
608 135 40_Re: Max number of tables in a tablespace14_Andy Lankester26_alankester@CDBSOFTWARE.COM31_Thu, 29 Sep 2005 06:14:52 -0500266_us-ascii A thought - can OBIDs be negative? I am pretty sure that DBDids cannot be as there is a max of 32K per DB2 subsystem. OBID, like DBID, is also SMALLINT (aka binary halfword) so it too can probably only be positive hence 32767 would be be the max OBID. [...]
744 111 40_Re: Max number of tables in a tablespace14_Seth Bienstock19_sethdb2@COMCAST.NET31_Thu, 29 Sep 2005 11:40:11 +0000406_- I don't believe DB2 would assign a negative obID value, although I see nothing wrong with doing so.
Although 32,767 is the highest value that can be stored in a halfword binary integer (i.e. SMALLINT), you still have to account for the first two values (1 and 2) which will be used for the tablespace's ps/obIDs. These Object Identifiers are values that are sequences within a database (DB). [...]
856 183 43_Re: DB2 UDB 8.2.2 - Performance of RR vs CS0_25_LL581@DAIMLERCHRYSLER.COM31_Thu, 29 Sep 2005 08:24:18 -0400468_US-ASCII Scott,
Did you run the query with isolation CS a second time (after the RR version) ? And how did you measure the elapsed time?
You may find the your first query (CS #1) has resulted in the physical open of datasets, and population of virtual pools with pages from the tables/indexes your query requires. Further queries, regardless of isolation, may run faster simply because some physical I/Os are not required, data now being in-memory. [...]
1040 66 56_Re: [v7 z/OS] Turning On Tracing for Package Accounting?24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Thu, 29 Sep 2005 07:34:20 -0500394_- Sir,
It depends on what you want to trace. But it is common practice to start ACCOUNTING CLASS 1,2,3,7 and 8 .
If each new package is going to be put in its own plan then ACCOUNTING CLASS 1,2 and 3 are sufficient.
If many packages are going to be bound into a handful of plans then 1,2,3,7 and 8 are needed to have a good idea about what is happening in the system. [...]
1107 136 33_Re: [z/OS] Omegamon & Ifcid Trace12_Martin Hubel17_Martin@MHUBEL.COM31_Thu, 29 Sep 2005 08:56:22 -0400
1244 217 22_AW: [DB2-L] SQL-Puzzle35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Thu, 29 Sep 2005 15:04:20 +0200712_iso-8859-1 Dave,
after literally hours of unsuccessful efforts I gave up, thanks all the same.
Best regards
Ruediger
________________________________
Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von David Nance Gesendet: Mittwoch, 28. September 2005 17:45 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: Re: [DB2-L] SQL-Puzzle
Ruediger, Anything we give you will not perform very well. You can look back in the archives about numbering(ranking) the resultset of a query and see what the issues are there(in fact Terry Purcell had answered a question on this topic for me about 4 or 5 years ago). I think the best solution would be to use [...]
1462 225 32_Re: identity column error hI aLL13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Thu, 29 Sep 2005 09:06:37 -0400433_- Until you get to V8, IF you ever need to drop and recreate the table, I agree, you're hosed.
In v8, you can Alter the Startwith value, you can alter the Generated characteristic. For instance, you could alter it from Generated always to Generated by default, do your re-load, alter it back to Generated Always. V8 fixes all the problems I recall about the Identify feature. But the Sequences object is probably better. [...]
1688 62 23_db2 v 7 error need help11_Fazio, Paul30_Paul.Fazio@MUTUALOFAMERICA.COM31_Thu, 29 Sep 2005 09:43:29 -0400686_iso-8859-1 We are getting the following on a call to a stored procedure and I can not find this error code. Does anyone know what it means?
Thanks, Paul
LINKERR (88) - 609 Connect to IRP failed
--------------------------------------------------------------------------------- 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
1751 68 54_ENC: [DB2-L] Performance Tuning Question - DB2-Z/OS V719_Luiz Roberto Foroni27_lforoni@CAIXASEGUROS.COM.BR31_Thu, 29 Sep 2005 10:47:13 -0300552_iso-8859-1 Hi Barbara,
Is this true only for predicates not indexed and with the same comparator (= , < , between,etc...?)
Any way in the administration guide: item 5.8.3.1 Order of evaluating predicates seems to clarify some doubts
Luiz Roberto Foroni DB2 DBA-ZOS Caixa Seguros lforoni@caixaseguros.com.br
-----Mensagem original----- De: Barbara J Nigh [mailto:barbara.j.nigh@US.HSBC.COM] Enviada em: terça-feira, 27 de setembro de 2005 20:29 Para: DB2-L@WWW.IDUGDB2-L.ORG Assunto: [DB2-L] Performance Tuning Question [...]
1820 301 40_Re: Max number of tables in a tablespace12_Kirk Hampton28_khampto1@CAPGEMINIENERGY.COM31_Thu, 29 Sep 2005 08:47:02 -0500417_us-ascii To recap, the answer to the original question seems to be, "it depends" ! Based on the limit of 32767 objects in one database, where each tablespace takes two entries, and tables and indexes take one entry each, then the theoretical max would seem to be, with one tablespace per database, 32767 - 2 (for the single tablespace) = 32765 / 2 = 16382 tables, assuming you need at least one index per table. [...]
2122 13 33_Re: [z/OS] Omegamon & Ifcid Trace24_Ramachandran Subramanian16_rrnsss@YAHOO.COM31_Thu, 29 Sep 2005 08:44:50 -0500566_- I cant understand why OMEGAMON does not give a RECORD TRACE report
Ram www.db2-dba.net
--------------------------------------------------------------------------------- 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
2136 23 41_Re: DB2 7.1 - zOS 1.6 - Security Question14_Kevin Fletcher26_kevin_fletcher@CONSECO.COM31_Thu, 29 Sep 2005 08:47:47 -0500721_- All,
I would like to thank all of those that replied.
I am going to do the following.
1. 1st ZPARM install sysadm id a TSO user (me, ;-)) 2. 2nd ZPARM install sysadm id a group id
I will repost and let you know the results.
Thanks,
Fletch
--------------------------------------------------------------------------------- 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 [...]
2160 230 32_Re: identity column error hI aLL13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Thu, 29 Sep 2005 09:06:37 -0400433_- Until you get to V8, IF you ever need to drop and recreate the table, I agree, you're hosed.
In v8, you can Alter the Startwith value, you can alter the Generated characteristic. For instance, you could alter it from Generated always to Generated by default, do your re-load, alter it back to Generated Always. V8 fixes all the problems I recall about the Identify feature. But the Sequences object is probably better. [...]
2391 220 40_Re: Max number of tables in a tablespace14_Seth Bienstock19_sethdb2@COMCAST.NET31_Thu, 29 Sep 2005 14:05:35 +0000597_- Well, now you're stretching it a bit. Once you introduce INDEXes, you're now going to use up additional Object Identifiers for them as well. Each index is going to require TWO OIDs.
-------------- Original message --------------
To recap, the answer to the original question seems to be, "it depends" ! Based on the limit of 32767 objects in one database, where each tablespace takes two entries, and tables and indexes take one entry each, then the theoretical max would seem to be, with one tablespace per database, 32767 - 2 (for the single tablespace) = 32765 / 2 = 16382 [...]
2612 106 27_Re: db2 v 7 error need help11_Gugel, Rich26_Rich.Gugel@DOA.STATE.WI.US31_Thu, 29 Sep 2005 09:06:23 -0500505_- Paul;
Looks like a link to CICS is failing, CICS External Interfaces Guide Document Number: SC34-6244-05 has this documented
HTH Rich
_____
From: Fazio, Paul [mailto:Paul.Fazio@MUTUALOFAMERICA.COM] Sent: Thursday, September 29, 2005 8:43 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] db2 v 7 error need help
We are getting the following on a call to a stored procedure and I can not find this error code. Does anyone know what it means? [...]
2719 201 40_Re: Max number of tables in a tablespace16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 29 Sep 2005 09:21:24 -0500323_us-ascii Thank you to everybody for their opinions and suggestions.
To answer Kirks question - I only intend to have one table per tablespace. The reason this question came up was I have heard a lot of different answers and simply wanted to hear the professional voices in DB2 cyber world - so thank you again. [...]
2921 265 35_AW: [DB2-L] db2 v 7 error need help12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 29 Sep 2005 16:22:36 +0200516_iso-8859-1 idx295idx296idx297You are right.
If the external CICS interface receives a RETRYABLE response on an EXCI call that it makes on behalf of an EXEC CICS LINK command, it automatically retries the EXEC CICS LINK command up to five times, providing more serious errors do not occur.
If the RETRYABLE response is still received after the fifth retry, the RESP field is set to LINKERR, and the reason returned on the EXCI CALL request that causes the exception is returned in the RESP2 field. [...]
3187 201 39_Re: AW: [DB2-L] db2 v 7 error need help15_Kumaresh Thimma26_Kumaresh_Thimma@JBHUNT.COM31_Thu, 29 Sep 2005 09:47:11 -0500494_ISO-8859-1 This is what I see in the "CICS External Interfaces Guide: SC34-6449-00"
609 IRC_CONNECT_FAILURE Explanation: During Open_Pipe processing, an attempt to connect to the target CICS system failed.
System Action: The Open_Pipe request fails. DFHIRP returns a R15 value to subreason field-1 and a R0 value (the reason code) to subreason field-2. The first two bytes of subreason field-1 are the return code qualifier and the last two bytes are the return code itself. [...]
3389 15 23_v8 z/os reorg querstion10_Bill Brown21_db2_dba@BELLSOUTH.NET31_Thu, 29 Sep 2005 09:52:15 -0500448_- prior to v8 the sortdata parm of the reorg was available and if omitted from your reorg job the data would be unloaded using the clustering index. the omission of the parm ment nosortdata. this was a good thing if your tablespace is extremly large or the data is very close to clustering order. in v8 the default is sortdata and there is no nosortdata parm. i am looking for recommendations to turn off the sortdata option. any suggestions? [...]
3405 47 44_DB2 on Z as a db server for Portal or Lawson11_Craig Otway18_otwayc@EDNET10.NET31_Thu, 29 Sep 2005 10:12:57 -0500330_iso-8859-1 I would like to know if anyone is running or considering using a Z 8/9 box as a database server to run Portal or Lawson. I am only interested in the DB piece, the webserver and app servers will be on another box for both products.
---------------------------------------------------------------------------------
3453 58 41_Re: DB2 7.1 - zOS 1.6 - Security Question13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 29 Sep 2005 10:10:54 -0500529_ISO-8859-1 Sorry I missed the original question. I knew the answer to that one... LOL
I would like to add an additional comment though. Do you have auditors? If you do, they may not like the idea of you using your personal TSO id as one of the install SYSADM ids. I know I don't think its a good idea. It would mean that your every day DB2 task would be performed as the Install SYSADM, thus bypassing all DB2 controls over what you can do... I would suggest setting up a special ID for use as install SYSADM only to [...]
3512 182 33_Re: [z/OS] Omegamon & Ifcid Trace15_Carol L Broyles30_carol.broyles@MEADWESTVACO.COM31_Thu, 29 Sep 2005 11:28:47 -0400310_US-ASCII All,
You might want to check out Omegamon/DB2's IFCID report writer. I believe it is only available with the latest version(s). We are on V540. I had to open an ETR with IBM to find documentation on it, and it was emailed to me. The document was titled "DB2OM Running the IFCID Reporter." [...]
3695 34 19_simple sql question12_Billy Larsen19_billarsen@YAHOO.COM31_Thu, 29 Sep 2005 10:29:42 -0500483_- ...but i cannot find how to do !!
My sql is : (display all tables that have index but don't have explicit index cluster)
SELECT A.CREATOR, A.NAME FROM SYSIBM.SYSTABLES A WHERE A.CREATOR = 'DERD01Z ' AND A.TYPE = 'T' AND NOT EXISTS (SELECT 1 FROM SYSIBM.SYSINDEXES B WHERE A.CREATOR = B.TBCREATOR AND A.NAME = B.TBNAME AND B.CLUSTERING = 'Y') AND 0 < (SELECT COUNT(*) FROM SYSIBM.SYSINDEXES B WHERE A.CREATOR = B.TBCREATOR AND A.NAME = B.TBNAME ) [...]
3730 168 31_Identity Column Warning Message0_25_LL581@DAIMLERCHRYSLER.COM31_Thu, 29 Sep 2005 11:38:36 -0400439_US-ASCII Esteemed List:
I've implemented a table with an Identity Column, and attempting to compile a Cobol program. I get an odd warning message in the precompile, and was hoping someone on the list can guide me to getting rid of the message.
The table is defined as follows:
CREATE TABLE X.XIDENT ( I_AUTO_NUMBER INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 ,INCREMENT BY 1 ) ) IN DB99.TS99 [...]
3899 476 35_Re: Identity Column Warning Message14_Dash, Sushanta21_Sushanta.Dash@FMR.COM31_Thu, 29 Sep 2005 11:50:42 -0400712_us-ascii Try keeping the DEFAULT within parenthesis. Good luck.
Thanks
Sushant Dash
Lead DBA Fidelity Business Services India Pvt. Ltd., Embassy Golf Links Business Park, Off Intermediate Ring Road, Bangalore - 560 071.
Any comments or statements made in this email are not necessarily those of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity Investments group companies. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you have received this in error, please contact the sender and delete the material from any computer. [...]
4376 58 23_Re: simple sql question10_Doyle Mark21_Mark.Doyle@WIN-NA.COM31_Thu, 29 Sep 2005 10:53:53 -0500472_us-ascii What do you want the count of? (tables, non-clustering indexes, something else?)
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Billy Larsen Sent: Thursday, September 29, 2005 10:30 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] simple sql question
...but i cannot find how to do !!
My sql is : (display all tables that have index but don't have explicit index cluster) [...]
4435 174 41_Re: DB2 7.1 - zOS 1.6 - Security Question15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Thu, 29 Sep 2005 12:05:08 -0400370_ISO-8859-1 I have to second Willie's opinion (not that anyone asked) -- It's WAY too easy to issue a DROP through some of the existing SQL interfaces, and if you're SYSADM.... oops ::BOOM:: too late. Make Install SYSADM something you normally keep tightly locked down. I believe we have a special ID for it, which is normally disabled by the Security Admin. --Phil [...]
4610 124 40_Re: Max number of tables in a tablespace15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Thu, 29 Sep 2005 12:10:31 -0400554_WINDOWS-1252 So this was kind of a "How many angels can dance on the head of a pin?" question, eh? --Phil On 9/29/05, Rasmussen, Steen wrote: > > Thank you to everybody for their opinions and suggestions. > > To answer Kirks question – I only intend to have one table per tablespace. > The reason this question came up was I have heard a lot of different answers > and simply wanted to hear the professional voices in DB2 cyber world – so > thank you again. > > Kind regards > Steen Rasmussen > Computer Associates > Senior [...]
4735 309 35_Re: Identity Column Warning Message0_25_LL581@DAIMLERCHRYSLER.COM31_Thu, 29 Sep 2005 12:10:58 -0400536_US-ASCII Thanks for the suggestion. I tried this:
INSERT INTO X.XIDENT VALUES (DEFAULT)
I get the same warning message:
DSNH408I W DSNHANAL LINE 694 COL 13 STATEMENT INSERTS OR UPDATES COLUMN "I_AUTO_NUMBER" WITH INCOMPATIBLE DATA TYPE
Lock Lyon Compuware Corp
"Dash, Sushanta" Sent by: DB2 Data Base Discussion List 09/29/2005 11:50 AM Please respond to DB2 Database Discussion list at IDUG [...]
5045 95 27_Re: v8 z/os reorg querstion15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Thu, 29 Sep 2005 12:21:45 -0400545_ISO-8859-1 Umm... no idea. Why would you want to? It almost never saves you time to turn of SORTDATA, and it never saves you _much_ time. --Phil On 9/29/05, Bill Brown wrote: > > prior to v8 the sortdata parm of the reorg was available and if omitted > from > your reorg job the data would be unloaded using the clustering index. the > omission of the parm ment nosortdata. this was a good thing if your > tablespace is extremly large or the data is very close to clustering > order. > in v8 the default is sortdata [...]
5141 117 58_Re: ENC: [DB2-L] Performance Tuning Question - DB2-Z/OS V715_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Thu, 29 Sep 2005 12:25:28 -0400391_ISO-8859-1 Luiz has a valuable recommendation. Barbara, As Far As I Know, it's still recommended that you order your most restrictive predicates first, but remember that DB2's order of evaluating predicates overrides the order you specify... so ordering only matters among predicates with _equal_ "precedence" in DB2's analysis. Do it that way, but don't rely on it exclusively. --Phil [...]
5259 240 35_Re: Identity Column Warning Message16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 29 Sep 2005 11:26:30 -0500484_us-ascii Hello Lockwood - you need to specify the column in this case:
INSERT INTO table (column) VALUES ( DEFAULT);
Kind regards Steen Rasmussen Computer Associates Senior Consultant
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of LL581@DAIMLERCHRYSLER.COM Sent: Thursday, September 29, 2005 11:11 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Identity Column Warning Message [...]
5500 102 23_Re: simple sql question15_Robert Lawrence21_rlawrence@BOSCOVS.COM31_Thu, 29 Sep 2005 12:29:12 -0400632_iso-8859-1 Billy, here is a SQL statement that will return all tables that have indices and but no clustering index and the count of the total number of indices for the table. IT only queries SYSIBM.SYSINDEXES
SELECT TBCREATOR , TBNAME , COUNT_INDEXES FROM ( SELECT DISTINCT TBCREATOR , TBNAME , MAX(CLUSTERING) AS CLUSTER_INDEX , SUM(COUNT_INDEX_TYPE) AS COUNT_INDEXES FROM( SELECT DISTINCT TBCREATOR , TBNAME , CLUSTERING , COUNT(*) AS COUNT_INDEX_TYPE FROM SYSIBM.SYSINDEXES GROUP BY TBCREATOR , TBNAME , CLUSTERING ) AS TEMP1 GROUP BY TBCREATOR , TBNAME ) TEMP2 WHERE CLUSTER_INDEX = 'N' ORDER BY TBCREATOR , TBNAME [...]
5603 209 23_Re: simple sql question11_David Nance16_DWNance@FHSC.COM31_Thu, 29 Sep 2005 10:29:17 -0600438_us-ascii I'm guessing you want the number of indexes that were created on your table. Give this a shot. SELECT A.CREATOR, A.NAME, count(*) FROM SYSIBM.SYSTABLES A ,sysibm.sysindexes b WHERE A.CREATOR = 'DERD01Z ' AND A.TYPE = 'T' AND NOT EXISTS (SELECT 1 FROM SYSIBM.SYSINDEXES B WHERE A.CREATOR = B.TBCREATOR AND A.NAME = B.TBNAME AND B.CLUSTERING = 'Y') and A.CREATOR = B.TBCREATOR AND A.NAME = B.TBNAME group by A.CREATOR, A.NAME [...]
5813 214 35_Re: Identity Column Warning Message0_25_LL581@DAIMLERCHRYSLER.COM31_Thu, 29 Sep 2005 12:39:27 -0400522_US-ASCII Thanks for the idea Steen. I tried this:
INSERT INTO X.XIDENT (I_AUTO_NUMBER) VALUES (DEFAULT)
I get the same warning message:
DSNH408I W DSNHANAL LINE 694 COL 13 STATEMENT INSERTS OR UPDATES COLUMN "I_AUTO_NUMBER" WITH INCOMPATIBLE DATA TYPE
Fascinating . . .
Lock Lyon Data Services/DBA/DW ITM Technology & Architecture DaimlerChrysler Services North America LLC Phone: (248) 427-5881 T/L: 870-5881 Email: LL581@dcx.com Page: (586) 898-9080 Text Page: LL581-page@dcx.com [...]
6028 291 35_Re: Identity Column Warning Message16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 29 Sep 2005 11:52:17 -0500332_us-ascii This is funny - executing the statement as dynamic SQL, the warning is not displayed, so it must be the precompiler getting confused when you only have an identity column in the Insert. If more columns existed, the warning might not display if you had at least one regular value or host variable in the VALUES clause. [...]
6320 100 35_Re: Identity Column Warning Message14_Zobjeck, A. J.21_Allen_Zobjeck@TTX.COM31_Thu, 29 Sep 2005 11:54:15 -0500692_us-ascii I created a table like you have and I did an insert via spufi and it worked just fine. This identity column is an integer and maybe the DEFAULT is not compatible with COBOL. Try removing the word default.
--------------------------------------------------------------------------------- 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
6421 130 33_Re: [z/OS] Omegamon & Ifcid Trace10_Bruce, Mae19_Mae.Bruce@OA.MO.GOV31_Thu, 29 Sep 2005 12:20:52 -0500696_us-ascii I think this has the document you are referring to.
http://www-1.ibm.com/support/docview.wss?uid=swg21213624
Mae -----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Carol L Broyles Sent: Thursday, September 29, 2005 10:29 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] [z/OS] Omegamon & Ifcid Trace
All,
You might want to check out Omegamon/DB2's IFCID report writer. I believe it is only available with the latest version(s). We are on V540. I had to open an ETR with IBM to find documentation on it, and it was emailed to me. The document was titled "DB2OM Running the IFCID Reporter." [...]
6552 123 35_Re: Identity Column Warning Message0_25_LL581@DAIMLERCHRYSLER.COM31_Thu, 29 Sep 2005 13:22:43 -0400608_US-ASCII I also (previously) did an Insert via SPUFI, and it worked fine. The question is regarding the Warning message from the PreCompiler.
Lock Lyon Data Services/DBA/DW ITM Technology & Architecture DaimlerChrysler Services North America LLC Phone: (248) 427-5881 T/L: 870-5881 Email: LL581@dcx.com Page: (586) 898-9080 Text Page: LL581-page@dcx.com
"Zobjeck, A. J." Sent by: DB2 Data Base Discussion List 09/29/2005 12:54 PM Please respond to DB2 Database Discussion list at IDUG [...]
6676 276 35_Re: Identity Column Warning Message13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Thu, 29 Sep 2005 13:35:48 -0400488_- Since Lock only has one column in the table, he doesn't need parentheses around the word DEFAULT. See the syntax diagram in the SQL reference.
I'd use the ETR IBMLINK application to ask IBM.
Dave
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of LL581@DAIMLERCHRYSLER.COM Sent: Thursday, September 29, 2005 12:39 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Identity Column Warning Message [...]
6953 221 33_Re: [z/OS] Omegamon & Ifcid Trace15_Carol L Broyles30_carol.broyles@MEADWESTVACO.COM31_Thu, 29 Sep 2005 13:47:39 -0400460_US-ASCII Yes, that's it. I've had limited success with this new IFCID reporter. One report I tried, KO2C0003 - Accounting Detail, abended with a S0C9. Another one I tried, KO2CQLAC, Accounting DDF, worked fine.
"Bruce, Mae" Sent by: DB2 Data Base Discussion List 09/29/2005 01:20 PM Please respond to DB2 Database Discussion list at IDUG [...]
7175 69 62_CCSID changing when converting to Version 8 from Version 7 ???9_Dee Reins24_dee.reins@CENTURYTEL.COM31_Thu, 29 Sep 2005 12:51:58 -0500
7245 204 23_Re: simple sql question25_Biswal, Manas (Cognizant)26_Manas.Biswal@COGNIZANT.COM31_Thu, 29 Sep 2005 23:51:13 +0530502_iso-8859-1
The following should be a simple enough query to solve your purpose -
SELECT TBNAME,TBCREATOR,COUNT(*) AS CNT_NOT_CLUSTERED
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = 'DERD01Z'
AND CLUSTERING = 'N'
GROUP BY TBNAME,TBCREATOR
HAVING COUNT(*) > 0
HTH
Thanks, Manas
"It is not the strongest of the species that survives, nor the most intelligent; it is the one that is most adaptable to change". - Charles Darwin [...]
7450 143 66_Re: CCSID changing when converting to Version 8 from Version 7 ???13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Thu, 29 Sep 2005 13:51:15 -0500303_us-ascii 037 is the CCSID normally used in the US. In fact, most customers with CCSID 500 are trying to figure out how to get to 037. So, if you are a US based company with no need for any of the characters in CCSID 500, you are probably y in great shape, from a CCSID standpoint, to move forward to V8.
7594 63 26_Re: v8 z/os reorg question10_Bill Brown21_db2_dba@BELLSOUTH.NET31_Thu, 29 Sep 2005 13:54:11 -0500486_- it comes down to the sort. we ran a reorg on a peoplesoft table. one tablespace, 450 tables, 1500 indexes. db2 invoked parallel index build, overlayed some code, brought db2 down, and placed approx 75 pages in the lpl including syslgrgx, syscopy, dsndb06, a bunch of indexes and some peoplesoft objects to boot. the util manual still states that if the data is in cluster order or the dataset is very large not to use the option, but if you can't turn it off there is no choice. [...]
7658 87 26_Re: v8 z/os reorg question13_Keemle Joan T25_KeemleJoanT@JOHNDEERE.COM31_Thu, 29 Sep 2005 15:15:20 -0500667_us-ascii PK04076/UK03983 may help you.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bill Brown Sent: Thursday, September 29, 2005 1:54 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] v8 z/os reorg question
it comes down to the sort. we ran a reorg on a peoplesoft table. one tablespace, 450 tables, 1500 indexes. db2 invoked parallel index build, overlayed some code, brought db2 down, and placed approx 75 pages in the lpl including syslgrgx, syscopy, dsndb06, a bunch of indexes and some peoplesoft objects to boot. the util manual still states that if the data is in cluster order [...]
7746 115 26_Re: v8 z/os reorg question15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Thu, 29 Sep 2005 16:16:44 -0400579_ISO-8859-1 Oh, my. Have you asked IBM to investigate the cause? Overlaying code, not to be too delicate about it, sounds like a Bad Thing.
On 9/29/05, Bill Brown wrote: > > it comes down to the sort. we ran a reorg on a peoplesoft table. one > tablespace, 450 tables, 1500 indexes. db2 invoked parallel index build, > overlayed some code, brought db2 down, and placed approx 75 pages in the > lpl > including syslgrgx, syscopy, dsndb06, a bunch of indexes and some > peoplesoft > objects to boot. the util manual still states that if the data [...]
7862 65 49_Resarting a LOAD utility after an abend DB2V8 ZOS14_Mark McCormack27_mamccormack@STATESTREET.COM31_Thu, 29 Sep 2005 16:25:43 -0400496_US-ASCII Mike,
I assume you are asking about Load Shrlevel None and failure after the reload phase. (Shrlevel Change is a different beast). Your situation may or may not be more complicated than you describe.
Do you need the processing in the indexval, enforce, discard, or report phases? In other words, are either of these conditions possible: 1. rows with duplicate keys, where a uniques index disallows this. 2. referential integrity violations (children with no parents). [...]
7928 38 29_DB2 z/OS v7 Recovery question14_Mark McCormack27_mamccormack@STATESTREET.COM31_Thu, 29 Sep 2005 17:00:06 -0400594_US-ASCII < snip> How much elapsed time and CPU time is saved by using COPY and RECOVER of indexes? Any special considerations when using index image copies?
Jim,
Here is my opinion (based on limited experience).
As a table gets bigger and bigger, the time for rebuild index increases at a faster rate, due to the size of the sort. At some point, that time wil be prohibitive. Index copy / recover becomes attractive for such large tables. If you rarely run recover index, and/or if the tables are not giant, then the extra processing to make index copies is [...]
7967 255 40_Re: Max number of tables in a tablespace12_Cianci, Nick19_nick.cianci@EDS.COM31_Fri, 30 Sep 2005 11:34:09 +1000553_us-ascii Steen, I liked your question. The simplest thing to do though (as some one said to me the other day), is to test it!
You would need to make sure you've got a test Sub-System somewhere, where you are not going to flood the EDM-pool!
I've a feeling that unless you've a huge EDM pool, you'll get into EDM pool problems before you hit the OBID limits, if nothing stops you before then. Especially if they were not trivial table definitions & you were to add an application to using it, instead of keeping it purely theoretical. [...]
8223 63 33_Re: DB2 z/OS v7 Recovery question13_Jim Schaeffer29_jim.schaeffer@EPOSTOFFICE.COM31_Thu, 29 Sep 2005 21:52:45 -0400434_ISO-8859-1 Mark,
Thank you.
I have the application with the largest tablespaces that our group of 6 DBAs supports. I think of about six of them as large, but many would laugh at my use of large compared to what they call large. I did a full point-in-time recover of a test instance of my application (sized like the production instance) several months ago. The recover took about 2 hours. I think that would be OK. [...]
8287 46 52_UNCLASSIFIED 6 month contract - Australia - Canberra0_28_steve.tennant@CUSTOMS.GOV.AU31_Fri, 30 Sep 2005 12:29:09 +1000428_iso-8859-1 This is directed to the Oz community.
Anyone out there who wants to a 6 month DBA position in Canberra?
Musts:-
DB2 DBA 3yrs CoolGen/Advantage Gen knowledge
Really nice:
Model management (CoolGen) Platinum/CA tools Security clearance to top secret/highly protected Data migration skills Guardien RACF
Email me in the first instance. Oh yes we want you soooooon! [...]
8334 132 20_sysviews information19_Foundoulakis Petros25_PFoundoulakis@EUROBANK.GR31_Fri, 30 Sep 2005 12:11:57 +0300472_us-ascii Hi listers
I want to unload for all the views of my system the TEXT field from SYSIBM.SYSVIEWS. I want this information in a single row, in a flat file, so I can use it to create these views to another SSID.
The problem is that if I unload with an 'order by SEQNO' parameter, DB2 unloads (DSNTIAUL) the information with huge blanks between words. Also cuts the field names (some of them) that happens to be between one SEQNO and next one...... [...]
8467 157 24_Re: sysviews information13_Richard Fazio21_rfazio@TRANSUNION.COM31_Fri, 30 Sep 2005 05:57:45 -0500309_US-ASCII My guess is that you are going to process this single row via a Rexx or other program.
Why don't you write a Rexx routine to do the data extraction and parsing. With Rexx you can very nicely read the data, re-orient it and generate the DDL. I can send you some samples if you would like. [...]
8625 37 20_COMMAND CENTRE ERROR11_ali hasnain23_me_alihasnain@YAHOO.COM31_Fri, 30 Sep 2005 04:13:54 -0700527_iso-8859-1 Hi;
I am working on DB2 UDB ver 7.2 for WinXp. After installing service pack2 of WinXP I'm getting following error when opening DB2 Command Centre.
"db2cctr.exe Application Error
The application failed to initialize properly (0x000005). Click on Ok to terminate the application."
From Ali Hasnain DBA Bank Al Habib Pakistan
__________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com [...]
8663 106 24_Re: COMMAND CENTRE ERROR4_Peri19_peri.ncst@GMAIL.COM31_Fri, 30 Sep 2005 16:55:03 +0530634_ISO-8859-1 Could you check if system is being hit by a requirement's issue
http://www-1.ibm.com/support/docview.wss?uid=swg21212087
On 9/30/05, ali hasnain wrote: > > Hi; > > I am working on DB2 UDB ver 7.2 for WinXp. > After installing service pack2 of WinXP I'm getting > following error when opening DB2 Command Centre. > > > "db2cctr.exe Application Error > > The application failed to initialize properly > (0x000005). Click on Ok to terminate the application." > > > > > From > Ali Hasnain > DBA Bank Al Habib > Pakistan > > > > __________________________________ > Yahoo! Mail - PC [...]
8770 84 56_Re: UNCLASSIFIED 6 month contract - Australia - Canberra15_Ferguson, Keena21_KFerguson@COMDATA.COM31_Fri, 30 Sep 2005 08:33:23 -0500604_iso-8859-1 Steve,
Always wanted to hang out in Australia ... What are the odds it will become a permanent position ... I don't have all the requirements, but enough I could get by ... Tell me more ...
Keena Ferguson
Sr. Database Administrator Comdata Corporation kferguson@comdata.com 615-371-3483 www.comdata.com
-----Original Message----- From: steve.tennant@CUSTOMS.GOV.AU [mailto:steve.tennant@CUSTOMS.GOV.AU] Sent: Thursday, September 29, 2005 9:29 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] UNCLASSIFIED 6 month contract - Australia - Canberra [...]
8855 29 31_Re: Performance Tuning Question33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Fri, 30 Sep 2005 09:12:22 -0500426_- Hi
As Bonnie told in one of her classes, there is an order in how the optimizer evaluates predicates:
1. matching predicates the order in which they are coded doesn't matter
2. index screening predicates evaluated in the sequence of coding
3. table predicates a) equal predicates b) range predicates c) other stage1-predicates
4. stage2-predicates evaluated in the sequence of coding [...]
8885 32 27_Hello from New Orleans West12_Bob Jeandron21_bobjeandron@YAHOO.COM31_Fri, 30 Sep 2005 09:31:36 -0500518_- Just wanted to say....we performed a REAL disaster recovery spawned by Katrina. Following procedures created during years of testing proved absolutely necessary. DB2 recovery worked great. We were using a combo of Image Copies and FDR. Don't have Flash Copy. Every night we did Image Copies and every 2 weeks we did a cold backup of our production databases. We were able to recover almost everything with the ICs and used FDR to restore DSNs and roll forward for newly added/missed TBSPs not backed up by ICs. [...]
8918 101 56_Re: UNCLASSIFIED 6 month contract - Australia - Canberra15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Fri, 30 Sep 2005 10:42:59 -0400629_ISO-8859-1 Where were you guys three years ago, when I was unemployed? ;-) Oh, but I don't have any security clearances, certainly not an Aussie clearance. Never mind! --Phil, who probably couldn't have gone anyway
On 9/29/05, steve.tennant@customs.gov.au wrote: > > This is directed to the Oz community. > > Anyone out there who wants to a 6 month DBA position in Canberra? > > Musts:- > > DB2 DBA 3yrs > CoolGen/Advantage Gen knowledge > > > Really nice: > > Model management (CoolGen) > Platinum/CA tools > Security clearance to top secret/highly protected > Data migration skills > [...]
9020 87 43_Testing BLOBs with Stored Procedure Builder0_25_LL581@DAIMLERCHRYSLER.COM31_Fri, 30 Sep 2005 10:56:36 -0400411_US-ASCII Esteemed List:
Recently a developer wanted to know how to test a mainframe Cobol stored procedure. This wasn't for debugging purposes, but just to make sure that the SP had the correct number of IN/OUT parameters, and would produce a proper results set when invoked. I've got an (old) copy of Stored Procedure Builder, so I showed the developer how to use it. Things went well for a while. [...]
9108 157 40_Re: Max number of tables in a tablespace19_Database Consultant32_databaseconsultant@SBCGLOBAL.NET31_Fri, 30 Sep 2005 09:17:55 -0700450_iso-8859-1 DBID's can be negative beginning with v7, I believe. Here is a clip from the SQL Ref on the DBID column in sysdatabase:
DBID
SMALLINT NOT NULL
Internal identifier of the database. If there were 32511 databases or more when this database was created, the DBID is a negative number.
Also, the number of databases in a subsystem is 64k: [...]
9266 52 34_[z/OS] DRDA/DDF between v7 and v8?15_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Fri, 30 Sep 2005 13:17:40 -0400194_ISO-8859-1 Is anyone running DB2V7 and DB2V8 on two separate subsystems (NOT two members of a datasharing group) and running distributed UOWs? If so, is it working well/are there issues?
9319 16 53_Re: Resarting a LOAD utility after an abend DB2V8 ZOS9_Jim Ruddy18_jaruddy@US.IBM.COM31_Fri, 30 Sep 2005 12:59:06 -0500309_- If you have any unique indexes, cheek contraints, or RI contraints and you allowed discarding then you should have restarted the LOAD. If you get any uniqueness violations during REBUILD you get to resolve them manually with REPAIR. If you have any check or RI constraints, you need to run CHECK DATA. [...]
9336 84 53_Re: Resarting a LOAD utility after an abend DB2V8 ZOS12_Backes, Mike21_Mike.Backes@OA.MO.GOV31_Fri, 30 Sep 2005 13:18:32 -0500525_us-ascii Mark, yes that's what im talking about load shrlevel none. There are no dup keys or ri in the load so the conditions you mentioned do not come into play. What we are doing is a load resume yes as you mentioned of around 200k rows into a 60mil row table(ie. Replace is not an option). I guess I wasn't asking if term util and rebuild was a viable option, I guess I was saying, that according to my reading of the load restart manual, it's the only option, after a sucessful reload and an abend in the sort phase [...]
9421 27 49_Resarting a LOAD utility after an abend DB2V8 ZOS14_Mark McCormack27_mamccormack@STATESTREET.COM31_Fri, 30 Sep 2005 17:22:01 -0400452_US-ASCII Mike,
I have reread several times the notes to the restart instructions in both the v7 and v8 manuals, and I now notice some differences I didn't see before. I am now as puzzled as you are about restart that would go back to the reload phase.
The points about duplicates and RI are valid, as Jim Ruddy has confirmed. My other suggestions seem to me to be accurate for load replace but not for load resume yes shrlevel none. [...]
9449 226 28_DB2 UDB V8 for Z/os BIT DATA15_Schade, Jeffrey15_JSchade@ISO.COM31_Fri, 30 Sep 2005 19:35:41 -0400436_us-ascii We recently upgraded our TEST sub-systems to DB2 V8 in compatibility mode and began having trouble using DB2 Connect to read some of the catalog tables. It turns out a number of the CHAR and VARCHAR columns in the SYSIBM tables now have an indicator (Column FOREIGNKEY in SYSIBM.SYSCOLUMNS) set to BIT DATA versus Mixed. This is causing the data returned to DB2 Connect to be non-printable hex data. What did we do wrong? [...]