1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l August 2001, week 5
2 31 27_BMC Change Manager question15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 29 Aug 2001 17:09:35 +1100506_- Just a quickie (pun unintended),
I'm running a BMC Change Manager workid that's basically creating a whole lotta objects. It doesn't have any commit statements, rather I specified it should -sync every 10 statements. I wanted to know how far through it was, so I counted the number of -sync statements in the worklist (760) and compared with the number of commits issued, according to Omegamon (the count currently stands at 3162 and is slowly climbing). Apparently a slight discrepancy, no? [...]
34 22 39_BMC Change Manager question - more info15_Bell, Raymond W31_Raymond.W.Bell@TEAM.TELSTRA.COM31_Wed, 29 Aug 2001 17:40:18 +1100455_- 'Guys',
Looks like the commit frequency and the -sync statements tie up now. There are 3 more commits issued than the number of inserts into a table called BMCALUM_SYNC. I think CM might have done a whole lotta work (for work read inserts and commits) up front, but now it seems to be in sync i.e where the worklist is up to now, it does 10 'create index' statements then a 'commit'. Interesting, but not a problem; I was just very curious. [...]
57 88 36_Re: DB2 for VSE/ESA and VM/ESA Tools16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Wed, 29 Aug 2001 08:44:31 +0100559_us-ascii Sam,
first part to consider must be running VM-hosted VSE, since native VSE does not use extended or expanded memory - making things very complicated.
Secondly, maybe you should check the website of The Fillmore Group http://www.thefillmoregroup.com/ where they offer a whole range of services, including consulting and/or custom training...
I worked both in a VM based and in a VSE based DB2 Server shops, please feel free to contact me offline if you think you might have some questions (no assurances on the answers ;-). [...]
146 31 16_Re: DB2 V7 OS39012_Paul Packham26_paul.packham@CONSIGNIA.COM31_Wed, 29 Aug 2001 09:26:51 +0000650_us-ascii Eric,
This is a refreshing note on DB2 V7 !!!
Are you using the IBM utilities at V7 ? If so have you noticed any significant improvement in performance ?
Regards, Paul Packham
Consignia plc is registered in England and Wales. Registered number 4138203. Registered Office at 148 Old Street, London EC1V 9HQ.
This email and any attachments are confidential and intended for the addressee only. If you are not the named recipient, you must not use, disclose, reproduce, copy or distribute the contents of this communication. If you have received this in error, please contact the sender and then [...]
178 18 21_Re: Query performance15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Wed, 29 Aug 2001 04:13:42 -0500364_- Dimitris
I don't understand your thread. Is your platform DB2 OS390? If it is then I can't do anything with your question about reorg and which indices nor with your runstats-command.
But for the query, I would add the column c2 into the index with the c1- column. So you will get a matching index access with one matching column and no sort. [...]
197 26 27_Additional Catalog Indexes?13_Michael Fleck14_m.fleck@LVR.DE31_Wed, 29 Aug 2001 11:38:50 +0100603_ISO-8859-1 Hi list members,
are there any known restrictions, problems, hints with creating additional indexes on the DB2 catalog? Do we have to delete these indexes before migration to another version?
Thanks in advance, Michael
Datenbank- und Ressourcendienste Landschaftsverband Rheinland Infokom Ottoplatz 2, 50679 Köln Tel: 0221/809/2826
===============================================To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
224 133 31_Re: Additional Catalog Indexes?16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Wed, 29 Aug 2001 12:00:48 +0100536_iso-8859-1 Hi Michael,
for details of SQL statements allowed on the catalog, and restrictions, you can refer to the SQL Reference for the version you're currently on.
There was a discussion on REORGing the catalog, which in a way is connected to what you're asking, since some of our learned colleagues pointed out that the catalog is a strange beast (the thread was "Reorg On SYSDBASE"), although it is common and right to customise the indices on it according to your shop's necessities (you can create up to 100). [...]
358 58 16_Re: DB2 V7 OS39016_Pearson, Eric L,23_Eric.Pearson@NSCORP.COM31_Wed, 29 Aug 2001 08:09:15 -0400296_iso-8859-1 We are using mostly IBM utilites. They are vastly improved over V5. The IBM utils are still not as fast as some other vendor ones we have. However, the IBM utils are much more trouble free, and unlike an OEM Reorg product have never lost data for us.
Regards, eric pearson [...]
417 17 36_Re: DB2 for VSE/ESA and VM/ESA Tools34_Luiz Cesar Vinhaes da Costa Junior27_vinhaes@CAIXASEGUROS.COM.BR31_Wed, 29 Aug 2001 09:43:20 -0300324_us-ascii Hi Sam
Look at BMC software.
Vinhaes Caixa Seguros - Brazil
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
435 71 56_REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) 00C9020513_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG31_Wed, 29 Aug 2001 07:47:12 -0500792_US-ASCII We are at an IBM DR site running a 48 hour test (24 hours into test) recovering DB2 v6 subsystem on OS/390 V 2.8 and we have recovered one of two of our DB2's but one of them is getting this error on the REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) we are getting following error >DB2P DSNIOST2 POTENTIALLY INCONSISTENT DATA REASON 00C90205 ERQUAL 5005 TYPE 00000302 NAME DSNDB06 .SYSDBAUT.X'00000002' CONNECTION-ID=UTILITY CORRELATION-ID=DCEPEL2B LUW-ID=* DSNI013I >DB2P DSNIOST2 POTENTIALLY INCONSISTENT DATA REASON 00C90205 ERQUAL 5005 TYPE 00000303 NAME DSNDB06 .DSNDDH01.X'00000003' CONNECTION-ID=UTILITY CORRELATION-ID=DCEPEL2B LUW-ID=* DSN3201I >DB2P ABNORMAL EOT IN PROGRESS FOR USER=DCEPEL CONNECTION-ID=UTILITY CORRELATION-ID=DCEPEL2B JOBNAME=DCEPEL2B TCB=009FB9E0 [...]
507 36 38_MS Query to DB2 OS/390 via DB2 Connect11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Wed, 29 Aug 2001 08:11:11 -0500374_iso-8859-1 We have a user who received a query from a co-worker that worked for him, but doesn't work for her. The query does a SELECT col1 AS 'new_col_name' ... and it's the AS that seems to be the problem. The error message is:
SQL0104N An unexpected token "'new_col_name'" was found following "". Expected tokens may include: " ". sqlstate=42601. [...]
544 72 36_Re: DB2 for VSE/ESA and VM/ESA Tools16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Wed, 29 Aug 2001 14:13:50 +0100417_us-ascii Sam, Luiz,
the only VSE-related product BMC make is for CICS, but anyway, you'd probably prefer our Omegamon II for CICS/VSE, would you not ;-)))) ?
different story on VM, I admit.
Ciao!
Aurora Emanuela Dell'Anno Systems Engineer Candle Service Ltd.
Office: +44 (0) 1276 41 4752 Mobile: +44 (0) 7881 818 867 Fax: +44 (0) 1276 41 4777 email: aurora_dellanno@candle.com [...]
617 106 60_Re: REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) 00C902059_Andy Hunt33_Andy.Hunt@SCOTTISH-SOUTHERN.CO.UK31_Wed, 29 Aug 2001 14:09:48 +0100531_us-ascii Try rebuilding the indexes on SYSDBAUT individually (i.e. dont use REBUILD INDEX (ALL)). REBUILD INDEX(ALL) does not work if there are user defined indexes on SYSDBAUT. Kind Regards, Andy Hunt - Scottish And Southern Energy
Mike Lawrence on 29/08/2001 13:47:12
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: Andy Hunt/HAV/SSE) Subject: REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) 00C90205 [...]
724 80 56_REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) 00C9020532_Atalay Tanis (Garanti Teknoloji)22_AtalayT@GARANTI.COM.TR31_Wed, 29 Aug 2001 16:29:28 +0300340_- Did you recover SYSUTILX and its indices before this one? Once we got this problem and after investigating it we found that we were not following the steps mentioned in the "RECOVERING CATALOG AND DIRECTORY OBJECTS" topic in the utilities manual. Trying to recover another tablespace or index before sysutilx may yield this problem. [...]
805 33 60_AW: REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) 00C9020512_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 29 Aug 2001 15:28:17 +0200575_iso-8859-1 Mike,
from the Version 5 Utility Reference...
"2.13.1.4.8 Recovering Catalog and Directory Objects ...........
| (3) If there are no user defined indexes on the catalog, execute | RECOVER INDEX (ALL) TABLESPACE DSNDB06.SYSxxxx to recover all | the IBM defined indexes on a catalog table space. If user | defined indexes are created on the catalog, the IBM defined | indexes must be recovered individually and the user defined | indexes recovered in a later step. See Appendix D of SQL | Reference for a list of the IBM defined indexes. " [...]
839 136 86_Re: Insert, delete, select, update access for a id for al l the t ables in a subsystem11_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Wed, 29 Aug 2001 08:53:38 -0500291_iso-8859-1 We also have RACF grp to handle our access. But sometime I see in package rebind warning messages saying the id does not have the authority to do insert. Even when that id is part of the RACF grp which has the authority. Then I grant at the table level and the rebind works. [...]
976 49 31_Re: Additional Catalog Indexes?11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Wed, 29 Aug 2001 08:56:17 -0500414_iso-8859-1 We have several indexes (as recommended by BMC for their products). I did have to drop/create an index on one of the tables (SYSCOLDIST, I think)for the upgrade from v5 to v6, but that was all. The pre-migration job pointed that out.
-----Original Message----- From: Michael Fleck [mailto:m.fleck@LVR.DE] Sent: August 29, 2001 5:39 AM To: DB2-L@RYCI.COM Subject: Additional Catalog Indexes? [...]
1026 78 60_Re: REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) 00C9020513_Mike Lawrence29_mlawrence@ASCENSIONHEALTH.ORG31_Wed, 29 Aug 2001 09:07:52 -0500789_US-ASCII Thanks to everyone that replied... rebuilt the indexes one at time and it worked! on to more fun at Disaster recovery! Thanks again!! ****************************************************************************************************************
We are at an IBM DR site running a 48 hour test (24 hours into test) recovering DB2 v6 subsystem on OS/390 V 2.8 and we have recovered one of two of our DB2's but one of them is getting this error on the REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) we are getting following error >DB2P DSNIOST2 POTENTIALLY INCONSISTENT DATA REASON 00C90205 ERQUAL 5005 TYPE 00000302 NAME DSNDB06 .SYSDBAUT.X'00000002' CONNECTION-ID=UTILITY CORRELATION-ID=DCEPEL2B LUW-ID=* DSNI013I >DB2P DSNIOST2 POTENTIALLY INCONSISTENT DATA REASON [...]
1105 163 85_Re: Insert, delete, select,update access for a id for al l the t ables in a subsystem13_Kurt Sahlberg26_Kurt.Sahlberg@EXPERIAN.COM31_Wed, 29 Aug 2001 09:06:15 -0500559_US-ASCII Hi Amit, I have seen this when a user sets his / her TSO PROFILE PREFIX to something other than a group that has the permissions. A SET CURRENT SQLID= before the rebind will take care of the warnings. HTH Kurt
>>> Sinha.Amit@MBCO.COM 08/29/01 08:53AM >>> We also have RACF grp to handle our access. But sometime I see in package rebind warning messages saying the id does not have the authority to do insert. Even when that id is part of the RACF grp which has the authority. Then I grant at the table level and the rebind works. [...]
1269 74 31_Re: Additional Catalog Indexes?14_Thomas, Steven21_Steven_Thomas@BMC.COM31_Wed, 29 Aug 2001 09:15:35 -0500376_iso-8859-1 One gotcha I did see at a customer once was when they had an index on SYSINDEXES and wanted to remove it. Not sure if this is still the case, but you needed to update this catalog table entry to remove the index but the index on it stopped ths happening. Not sure why, as you shouldn't be able to lockout yourself, and it was a long time ago so no promises... [...]
1344 71 21_"Little DB2" question16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Wed, 29 Aug 2001 16:21:39 +0100499_us-ascii ok it's my turn to ask stoopid questions about the all-pervasive little dreaded DB2... it's on behalf of a colleague who's trying to play with this tool of Satan...
Esteemed list.
hopefully a very easy DB2 question - using DB2 with MQSI 2, and require a database lookup as part of the message processing. I have created a new database in DB2 called BANKDB and created a couple of tables - just not too sure how i populate them, as i need to add a couple of rows of info. [...]
1416 83 60_Re: REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) 00C9020512_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 29 Aug 2001 18:22:19 +0200515_iso-8859-1 Hi Recover the TS again and then try to recover the indexes one by one, not (ALL) - I had a near problem yesterday :-) .
Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Mike Lawrence" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, August 29, 2001 2:47 PM Subject: REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) 00C90205 [...]
1500 52 31_Re: Additional Catalog Indexes?12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 29 Aug 2001 18:29:13 +0200354_iso-8859-1 Hi, I'm using them for a long time now. Changed the recover (& DR) process to recover the indexes one by one and not (ALL) as written in the book. If I remember correctly it is recommended to do the recover for user indexes in a separate step (for me it worked even in the same step, as long as they were in the correct order, but YMMV). [...]
1553 49 26_Re: UDF story - OS/390 DB212_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 29 Aug 2001 18:31:36 +0200425_windows-1255 Hi Venkat,
We'll give it a try next week - thanks for the info.
Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Venkat Pillay" Newsgroups: bit.listserv.db2-l To: Sent: Monday, August 27, 2001 9:24 PM Subject: Re: UDF story - OS/390 DB2 [...]
1603 30 52_gcc for aix? need to compile sql pl store procedures20_Sitaram Vijay kartik21_vijaykartik@YAHOO.COM31_Wed, 29 Aug 2001 08:37:51 -0700425_us-ascii Hi all:
Can anyone tell me if gcc could be used with DB2 EE V7.1 FP2a on AIX 4.3.3
Our compiler expired and we need to compile the SQL PL Store Procedures.
Any kind of help is appreciated.
TIA
-- Vijay
__________________________________________________ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ [...]
1634 131 25_Re: "Little DB2" question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Wed, 29 Aug 2001 16:39:52 +0100372_- Hi
firstly ...db2 or udb as it is called is not a demon .. it is a mature product that does what it says on the can. just a word of warning ...... kicking it when you haven't used it is not a good idea on this list. We all appreciate that any new product when first used can be difficult .. but hey give the thing a chance before calling it a demon :-)))))) [...]
1766 101 60_Re: REBUILD INDEX(ALL) TABLESPACE(DSNDB06.SYSDBAUT) 00C9020512_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Wed, 29 Aug 2001 10:45:00 -0400547_- Don't know if this is you.... We ran into this problem a while back with a user tbsp. We were restoring several tbsps from a stacked tape (multiple ICs on one set of tapes). It was caused by restoring from an IC which spanned 2 tapes and including the REF (referback) in the restore DD names. The recover worked fine, but the rebuild index indicated inconsistencies. At the time, we just recovered the problem tbsp by itself without DDs and it worked fine. We had to take out the Referbacks and make the entries for the DDs exactly like it [...]
1868 158 25_Re: "Little DB2" question16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Wed, 29 Aug 2001 11:52:56 -0400675_us-ascii Very well said!
"Pendlebury-Bowe, Leslie" cc: Sent by: DB2 Data Base bcc: Discussion List Subject: Re: "Little DB2" question
08/29/01 11:39 AM Please respond to DB2 Data Base Discussion List
Hi
firstly ...db2 or udb as it is called is not a demon .. it is a mature product that does what it says on the can. just a word of warning ...... kicking it when you haven't used it is not a good idea on this list. We all appreciate that any new product when first used can be difficult .. but hey give [...]
2027 114 25_Re: "Little DB2" question20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Wed, 29 Aug 2001 17:52:27 +0200343_iso-8859-1 Hopefully a very easy answer ;)
in the first insert command you used no schema name so I think little DB2 tried to insert your records into the table YOUR_USERID.ACCOUNTINFO, but you didn't say what kind of message you get back from this command so it's difficult to say if UDB inserted the record in your table or not. [...]
2142 36 56_Re: gcc for aix? need to compile sql pl store procedures15_Grantham, Karen25_Karen.Grantham@COMPAQ.COM31_Wed, 29 Aug 2001 10:55:25 -0500575_iso-8859-1 We have successfully used gcc in our DB2 EEE V7.1 on AIX 4.3 environment.
Karen Grantham DBA Compaq Direct x8738
-----Original Message----- From: Sitaram Vijay kartik [mailto:vijaykartik@YAHOO.COM] Sent: Wednesday, August 29, 2001 10:38 AM To: DB2-L@RYCI.COM Subject: gcc for aix? need to compile sql pl store procedures
Hi all: Can anyone tell me if gcc could be used with DB2 EE V7.1 FP2a on AIX 4.3.3 Our compiler expired and we need to compile the SQL PL Store Procedures. Any kind of help is appreciated. TIA * Vijay [...]
2179 46 56_Re: gcc for aix? need to compile sql pl store procedures11_Pedron, Leo18_lpedron@AKAMAI.COM31_Wed, 29 Aug 2001 12:07:00 -0400374_iso-8859-1 I haven't tried it with AIX, but I am using gcc on Linux to compile SQL procedures and static SQL programs without any issues.
HTH, Leo
-----Original Message----- From: Sitaram Vijay kartik [mailto:vijaykartik@YAHOO.COM] Sent: Wednesday, August 29, 2001 11:38 AM To: DB2-L@RYCI.COM Subject: gcc for aix? need to compile sql pl store procedures [...]
2226 31 25_Phantom Claimers - URGENT11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Wed, 29 Aug 2001 10:14:36 -0600507_iso-8859-1 Hi, Listers!
Got a bit of a thorny one this morning. We have a situation where we have a tablespace that shows H-X,PP,I with the DISPLAY LOCKS yet when we issue a DISPLAY CLAIMERS on the tablespace, there's no claimers listed. We in a bit of a bind as these locks H-X locks (Held-exclusive?) are keeping out our online users. Since it's only on a few of the 32 partitions, the users can access some of their work but needless to say they're a bit grumpy they can't access everything. [...]
2258 31 19_Re: index lookaside12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 29 Aug 2001 11:11:35 -0500517_- One of the major contributors to the longer time is the commit processing. You are probably taking more cpu time for the commits than for the fetches. While one read IO is enough for many fetches, each commit requires four writes (two logs, phase 1 and phase 2).
It would be useful to have the comparison runs side by side with all of the accounting long traces. Then it's fairly easy to see the various contributions of the time for log write, update commit, synchronous reads, asynchronous reads, ... [...]
2290 13 22_COBOL II/DB2 Questions13_Davis, Ronald17_DavisRo@CONED.COM31_Wed, 29 Aug 2001 12:15:14 -0400345_ISO-8859-1 I need ten interview questions for a DB2/Cobol II programmer? Any help would be appreciated.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
2304 184 86_Re: Insert, delete, select, update access for a id for al l the t ables in a subsystem14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 29 Aug 2001 17:23:07 +0100496_iso-8859-1 Wow,
Someone else is helping me with sales opportunities - Thanks Eric.
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com
-----Original Message----- From: Pearson, Eric L, [mailto:Eric.Pearson@NSCORP.COM] Sent: 28 August 2001 21:52 To: DB2-L@RYCI.COM Subject: Re: [DB2-L] Insert, delete, select, update access for a id for al l the t ables in a subsystem [...]
2489 22 23_INSERT Trigger for LOAD9_Wu, James13_jwu@KRAFT.COM31_Wed, 29 Aug 2001 11:32:52 -0500387_iso-8859-1 IBMers,
An INSERT trigger only works for INSERT operations. Can IBM consider making the INSERT triggers work for LOAD operations as well, or create LOAD triggers that work in the similar way of the INSERT triggers? I know we can get the similar functionality by using EDITPROC or FLDPROC, but using trigger for both INSERT and LOAD will make our life much easier. [...]
2512 193 25_Re: "Little DB2" question16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Wed, 29 Aug 2001 17:35:36 +0100327_us-ascii Leslie et all,
sorry if I offended Satan... joking aside this thing is driving me mental! so I may hopefully be excused for calling it nasty to its face :-(
REALLY joking aside, here's a little bit more info about the environment and what I've done:
DB2 UDB for Windows NT v 6.1, MQSI v. 5.2 [...]
2706 128 26_Re: COBOL II/DB2 Questions13_Keith Gilbert17_kgilbert@CSA1.COM31_Wed, 29 Aug 2001 11:43:27 -0500697_iso-8859-1 Ronald,
Here are some COBOL and DB2 questions we ask in our technical interviews. I got these questions from a book called "Ace the Technical Interview", an excellent source for questions like these.
Here's the Amazon link to that book...
http://www.amazon.com/exec/obidos/ASIN/0072126221/qid=999103067/sr=1-1/ref=s c_b_1/103-6097835-3587034
And here are the questions...
GENERIC COBOL
* Describe a COMP and a COMP-3 field. When would you use a COMP or a COMP-3 field? * How would the number +1234 be stored in a PIC S9(4) COMP-3 field? * Have you ever used an internal sort in a COBOL program? * Explain how the ROUNDED option is [...]
2835 19 22_CALL DSNTIAR from REXX14_Toppins, Smike21_smike.toppins@GWL.COM31_Wed, 29 Aug 2001 10:45:23 -0600410_- Has anyone come up with a way to CALL the DSNTIAR (error message) routine from REXX?
SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
2855 60 27_Re: INSERT Trigger for LOAD12_Susan Lawson22_lawson_susan@YAHOO.COM31_Wed, 29 Aug 2001 09:52:43 -0700570_us-ascii James,
In version 7 - if you use the new online LOAD resume - triggers will be fired. Basically this new feature is executing inserts under the covers.
Susan --- "Wu, James" wrote: > IBMers, > > An INSERT trigger only works for INSERT > operations. Can IBM consider > making the INSERT triggers work for LOAD operations > as well, or create LOAD > triggers that work in the similar way of the INSERT > triggers? I know we can > get the similar functionality by using EDITPROC or > FLDPROC, but using > trigger for both INSERT [...]
2916 23 56_Re: wildcards in Cobol host variables - related question12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 29 Aug 2001 11:54:02 -0500361_- column LIKE :HV is tricky, but it is indexable in V5. It's more likely that the estimate of the number of rows was fairly high, while the clusterratio of the index was low. The default filter factor for LIKE is 1/10 of the rows (V5 and V6 Administration Guide). In those cases, the optimizer can compute that a table space scan is faster than random IO. [...]
2940 56 26_Re: CALL DSNTIAR from REXX12_Isaac Yassin20_yassini@BEZEQINT.NET31_Wed, 29 Aug 2001 20:05:19 +0200753_windows-1255 Hi, Try that
* REXX */ TRACE O "ISPEXEC CONTROL DISPLAY LINE START(1)" PARSE UPPER ARG SQLCODE SQLC = X2C(D2X(SQLCODE,8)) ; SQLCA = 'SQLCA 'X2C(00000088)SQLC||X2C(0000)||COPIES(' ',78)||, COPIES(X2C(00),24)COPIES(' ',16) ; TIAR_MSG = X2C(0190)COPIES(' ',400) ; TEXT_LEN = X2C(00000050) ; ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG TEXT_LEN' SAY SUBSTR(TIAR_MSG,4,400);
Isaac Yassin DBMS & IT Consultant IBM Certified Solution Expert DB2 V7.1 Database Administration for OS/390 yassini@bezeqint.net ----- Original Message ----- From: "Toppins, Smike" Newsgroups: bit.listserv.db2-l To: Sent: Wednesday, August 29, 2001 6:45 PM Subject: CALL DSNTIAR from REXX [...]
2997 28 22_Authorization Question16_Dempsey, Michael20_MDempse@UNCH.UNC.EDU31_Wed, 29 Aug 2001 13:08:43 -0400550_iso-8859-1 Hey Folks... Have a wierd situation. USR1 connects to DB2 OS/390 through command center succesfully. Then issues a set current sqlid = 'USR2' Both USR1 & USR2 have sysadm auth. After the set command, USR2 tries to create a table and gets 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: SQL0551N "USR2" does not have the privilege to perform operation "CREATE TABLE" on object "DSNDB04 ". SQLSTATE=42501 [...]
3026 17 84_Re: Insert, delete, select, update access for a id for all the tables in a subsystem12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 29 Aug 2001 12:03:01 -0500322_- This is easy to do if use are using RACF or Security Server authorization, since the patterns are supported directly. It can probably be implemented using the access control authorization exit. All of the bundles implemented in DB2 include DROP, since there is not much difference between delete all rows and drop. [...]
3044 80 26_Re: CALL DSNTIAR from REXX14_Toppins, Smike21_smike.toppins@GWL.COM31_Wed, 29 Aug 2001 11:20:45 -0600693_- Thanks! That works great!
SMike Toppins Great-West Life smike.toppins@gwl.com (303) 737-5094
> ---------- > From: Isaac Yassin[SMTP:yassini@bezeqint.net] > Reply To: DB2 Data Base Discussion List > Sent: Wednesday, August 29, 2001 12:05 PM > To: DB2-L@ryci.com > Subject: Re: CALL DSNTIAR from REXX > > Hi, > Try that > > * REXX */ > TRACE O > "ISPEXEC CONTROL DISPLAY LINE START(1)" > PARSE UPPER ARG SQLCODE > SQLC = X2C(D2X(SQLCODE,8)) ; > SQLCA = 'SQLCA 'X2C(00000088)SQLC||X2C(0000)||COPIES(' ',78)||, > COPIES(X2C(00),24)COPIES(' ',16) ; > TIAR_MSG = X2C(0190)COPIES(' ',400) ; > TEXT_LEN = X2C(00000050) ; > ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG [...]
3125 82 53_Candle Experience 2001, Candle UK Customer Conference16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Wed, 29 Aug 2001 18:24:35 +0100685_us-ascii Guys and dolls,
just a reminder about the 2-day event open to all Candle customers in the UK, now called the Candle Experience 2001.
This is an annual two-day conference for Candle customers and for organisations seeking to enhance business performance by adopting the principles of IT service excellence.
The theme of this year's conference "Transform your Business with Service Excellence" reflects the conviction that business performance is increasingly dependent upon IT service excellence. This belief clearly impacts on the relationship between IT and the business. The result is a transformation - changing the role of the IT department from [...]
3208 32 31_DB2 for OS/390 and web security19_Natalie W. Faulkner27_nfaulknr@GWMAIL.STATE.WV.US31_Wed, 29 Aug 2001 12:31:51 -0500360_- Our shop is poised to dive into the brave new world of Internet access to our mainframe DB2 data. Connectivity is not an issue, as we currently have several Intranet web apps hitting data. The greatest fear/obstacle is data and machine SECURITY. How can we allow "John Q. Public" access to our mainframe without issuing a each person a mainframe logon? [...]
3241 23 25_Re: "Little DB2" question13_Villa Horacio17_hvilla@TTI.COM.AR31_Wed, 29 Aug 2001 14:50:46 -0300436_iso-8859-1 Aurora, I also had some troubles with CLP. By default, it doesn't like the semicolon and if you want to continue a command on another line, you have to end the previous line with a \ To use ; I put my commands in a file and use the syntax: db2 -tf myfilename I don't think this is your problem, but who knows. Ciao Horacio Villa TTI S.A. Buenos Aires - Argentina 2 free vouchers from IBM to take the certification exams [...]
3265 54 42_Re: MS Query to DB2 OS/390 via DB2 Connect13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Wed, 29 Aug 2001 12:57:27 -0500330_iso-8859-1 Tina,
You might try putting NOSCAN=0 into the datasource(s?) section of the db2cli.ini file for the failing user. This causes DB2 Connect to strip out a lot of non-IBM SQL syntax. We found a similar case for a 3rd party vendor product. It also was sporadic. Not elegant, but fixed the problem for the user. [...]
3320 42 58_DB2 conditional REORG parameters: what do they really mean14_Donald A Smith18_dsmithj@US.IBM.COM31_Wed, 29 Aug 2001 14:18:07 -0400461_us-ascii Hi List,, I am trying to understand the DB2 condtional REORG parameters and would appreciate any feedback on this note. I feel I need a little clearer understanding what they really mean. The parameters I am refering to are OFFPOSLIMIT and INDREFSLIMIT. They are defined as follows:
The conditional execution parameters rely on columns in the SYSIBM.SYSTABLEPART and SYSIBM.SYSINDEXPART DB2 catalog tables. The columns are used as follows: [...]
3363 145 27_Re: INSERT Trigger for LOAD14_Peter Backlund21_pbacklu@ATTGLOBAL.NET31_Wed, 29 Aug 2001 20:49:21 +0200612_us-ascii Susan and James,
I just had a "conversation" with Roger Miller. For the new LOAD SHRLEVEL CHANGE (which is an INSERT under the covers), row after insert triggers are fired - not exactly what the doctor ordered.
Peter
PS Will I see You Susan in Santa Cruz?
Susan Lawson wrote:
>James, > >In version 7 - if you use the new online LOAD resume - >triggers will be fired. Basically this new feature is >executing inserts under the covers. > >Susan >--- "Wu, James" wrote: > >>IBMers, >> >> An INSERT trigger only works for INSERT >>operations. Can IBM [...]
3509 26 34_UPDATE OF PARTITIONING KEY COLUMNS16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Wed, 29 Aug 2001 11:38:44 -0700593_us-ascii Group,
We have a situation where an attempt to bind a package in production resulted in a -151 sqlcode. Apparently, the application had an update statement coded which would update the third key column of the partitioning index on the structure. In viewing the columns of the table participating in the partitioning key, all of them have UPDATE = 'N'. The columns were all defined as NOT NULL WITH DEFAULT. We are currently running in V5 in production and V6 in test. The odd thing is, the same partitioned tablespace in test shows that the partitioning key column CAN be [...]
3536 19 34_Reorg of large SCT02... TONIGHT!!!12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Wed, 29 Aug 2001 13:57:48 -0500509_- We are planning to reorg our catalog and directory this evening. Our SCT02 tablespace in the directory is about 3 Gig. I am trying to figure out what size to make the SYSREC for the unload during the reorg of that object. Looking at the formula - DATASET SIZE IN BYTES = (LONGROW) * NUMROWS. I am unable to find how to query this tablespace to find out how many rows are in it and the longest row. I put an ASKQ with IBM and they said to use the number of rows from SYSPLAN. this does not sound right. [...]
3556 52 39_Unusual behavior with stored procedure.11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Wed, 29 Aug 2001 13:28:10 -0600527_iso-8859-1 We're having a very unusual circumstance with a SQL stored proc that performs an INSERT ... SELECT operation, selecting from our typical database tables and inserting into a global temporary table. The SQL itself is fairly complex, with four correllated queries in a CASE statement, two outside joins and a union. It was running in under 2 hours prior to making some relatively small modifications, which we expected to reduce the complexity. Unfortunately we have found a gigantic decrease in the performance. [...]
3609 20 26_DB2 Connect sqlesact() API13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Wed, 29 Aug 2001 14:28:20 -0500540_iso-8859-1 Hello List,
Can anyone provide an example of using the Accounting string API that DB2 Connect provides for users?
Any real world examples of defining the variable and passing it via the SQLESACT() API, using VB, or JAVA would be greatly appreciated.
TIA Dave
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3630 53 38_Re: UPDATE OF PARTITIONING KEY COLUMNS13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Wed, 29 Aug 2001 12:30:09 -0700718_us-ascii We had this issue sometime back. Basically there is a Zparm PARTKEYU which determines your (in)ability to update a column in partitioning key.
Thanks. Ashish.
-----Original Message----- From: Jeff A L'Italien [SMTP:Jeff.A.L'Italien@AEXP.COM] Sent: Wednesday, August 29, 2001 11:39 AM To: DB2-L@RYCI.COM Subject: UPDATE OF PARTITIONING KEY COLUMNS
Group,
We have a situation where an attempt to bind a package in production resulted in a -151 sqlcode. Apparently, the application had an update statement coded which would update the third key column of the partitioning index on the structure. In viewing the columns of the table participating in the partitioning key, [...]
3684 13 38_Re: Reorg of large SCT02... TONIGHT!!!14_Donald A Smith18_dsmithj@US.IBM.COM31_Wed, 29 Aug 2001 15:31:38 -0400399_us-ascii Go to tape!!! Do not attempt going to DASD with a table of that size. If you have a VTS go to that first. If not use whatever tape of tape you have.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3698 74 42_Re: MS Query to DB2 OS/390 via DB2 Connect27_Hilton, Tina, BmS - NMI -PM21_Tina.Hilton@BMSUS.COM31_Wed, 29 Aug 2001 14:31:36 -0500583_iso-8859-1 That didn't fix the problem. Any other ideas?
-----Original Message----- From: Shapiro, Dave [mailto:Shapiro.Dave@PRINCIPAL.COM] Sent: August 29, 2001 12:57 PM To: DB2-L@RYCI.COM Subject: Re: MS Query to DB2 OS/390 via DB2 Connect
Tina,
You might try putting NOSCAN=0 into the datasource(s?) section of the db2cli.ini file for the failing user. This causes DB2 Connect to strip out a lot of non-IBM SQL syntax. We found a similar case for a 3rd party vendor product. It also was sporadic. Not elegant, but fixed the problem for the user. [...]
3773 18 38_Re: UPDATE OF PARTITIONING KEY COLUMNS16_sylvie Vuittenez24_sylvie.vuittenez@NOOS.FR31_Wed, 29 Aug 2001 14:25:16 -0500399_- The parameter PARTKEYU (new in refresh V5) in zparm has three values : YES : updates authorized on a column of a partitioning index (after the update, the key can be in the same or in another partition) NO : updates NO authorized on a column of a partitioning index SAME : updates authorized on a column of a partitioning index but after the update, the key must be in the SAME partition as before
3792 27 16_Archival Logging10_Mathai Joy22_mathaijoydb2@YAHOO.COM31_Wed, 29 Aug 2001 12:51:59 -0700551_us-ascii I have placed udb database in archival logging mode. Also, I have enabled the user exit option within the log configuration. Primary logfile is set for 5 and secondary log file is set for 20. I have noticed there are over 200 log files created in the log directory and the directory is 94% full. I just issued DEACTIVATE database and ACTIVATE database command. Still the log files remain in the directory and 94% full in the directoy. How can I delete or archive the log file? Please let me know what I should do to recapture the space. [...]
3820 43 26_Stored Procedure Debugging11_Joe Luthman22_jluthma@BGNET.BGSU.EDU31_Wed, 29 Aug 2001 16:00:54 -0400379_us-ascii We use COBOL stored procedures on our OS390 DB2/UDB V6 system. For diagnostics and for audit trail, we're happy enough with the COBOL DISPLAY verb. Occasionally, though, we'll get an abend such as the following.
+CEE0374C CONDITION = CEE3250C TOKEN = 00040CB2 61C3C5C5 00000001 WHILE RUNNING PROGRAM IGZCDSP WHICH STARTS AT 9884A3E8 AT THE TIME OF INTERRUPT [...]
3864 84 38_Re: UPDATE OF PARTITIONING KEY COLUMNS16_Jeff A L'Italien25_Jeff.A.L'Italien@AEXP.COM31_Wed, 29 Aug 2001 13:15:32 -0700419_us-ascii Ashish,
Thanks for this information, as it DOES explain why we are seeing this here. So, I'm assuming that if the PARTKEYU parm was changed to 'Y' during the V5 upgrade, that any partitioned structure which was created prior to V5 would still have UPDATES = 'N' for those partitioned key colums? If so, do you know of any workaround (aside from dropping and recreating) to change the value to 'Y'? [...]
3949 56 20_Re: Archival Logging18_Gert van der Kooij15_geko@WANADOO.NL31_Wed, 29 Aug 2001 22:23:06 +0200364_iso-8859-1 Hi,
You enabled the user exit but did you also create the user exit progam? It's completely described in the Administration Guide how to set things up. What's happening now is that DB2 calls the user exit which most likely can not be found using the systems search PATH so your old log files are never archived and removed from your system. [...]
4006 66 30_Re: Stored Procedure Debugging11_Suresh Sane21_data_arch@HOTMAIL.COM31_Wed, 29 Aug 2001 15:58:42 -0500702_- Joe,
Have you considered using the LE/370 CEEMOUT facility? I understand it is complex to set up but recommended over DISPLAY?
Suresh
>From: Joe Luthman >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Stored Procedure Debugging >Date: Wed, 29 Aug 2001 16:00:54 -0400 > >We use COBOL stored procedures on our OS390 DB2/UDB V6 system. For >diagnostics and for audit trail, we're happy enough with the COBOL DISPLAY >verb. Occasionally, though, we'll get an abend such as the following. > >+CEE0374C CONDITION = CEE3250C TOKEN = 00040CB2 61C3C5C5 00000001 > WHILE RUNNING PROGRAM IGZCDSP WHICH STARTS AT [...]
4073 110 31_Re: Additional Catalog Indexes?0_19_csutfin@AMSOUTH.COM31_Wed, 29 Aug 2001 15:50:45 -0500484_iso-8859-1 Michael
One thing to consider is Disaster Recovery (or any recovery) of the Catalog. It needs to be modified. There was an APAR in V4 that addressed this problem. I don't remember what the APAR number was.
Basically , when you recover the indexes for the catalog tables you cannot issue a RECOVER INDEX(ALL). You need to recover all of the standard indexes by name first. Then as a separate job you can then recover the additional, user indexes by name. [...]
4184 83 38_Re: UPDATE OF PARTITIONING KEY COLUMNS0_18_mebert@AMADEUS.NET31_Wed, 29 Aug 2001 21:46:10 +0200659_us-ascii Jeff,
there is a ZPARM (PARTKEYU I think) that controls this: YES (allow updates of partitioning key), NO (do not allow), and SAME (allow updates if row does not move to another partition).
Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany
Group,
We have a situation where an attempt to bind a package in production resulted in a -151 sqlcode. Apparently, the application had an update statement coded which would update the third key column of the partitioning index on the structure. In viewing the columns of the table participating in the partitioning key, all [...]
4268 89 38_Re: Reorg of large SCT02... TONIGHT!!!0_18_mebert@AMADEUS.NET31_Wed, 29 Aug 2001 21:57:00 +0200490_us-ascii Richard,
simply size SYSREC somewhat larger than SCT02 (with a secondary of maybe 15% of the primary space), and you should be allright. Normally I use NOSYSREC SORTDEVT SYSDA SORTNUM 8 with REORG to let DB2 worry about sizes. For TSs where these parms are not possible (like some Cat/Dir ones, or those without any explicit clustering index, or for REORG INDEX) I have created a cataloged 5GB disk dataset under my userid which I use as a general-purpose data bucket. [...]
4358 14 38_Re: Reorg of large SCT02... TONIGHT!!!12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Wed, 29 Aug 2001 15:43:52 -0500470_- Duhh! I knew that, have done it before when we managed large IEF databases. How age is effecting my memory.. :-( Sorry to clog this very useful resource with such an silly question. Thanks for your fast and obvious resolution.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
4373 121 38_Re: UPDATE OF PARTITIONING KEY COLUMNS24_Leblanc, Francis C - CNF23_Leblanc.Francis@CNF.COM31_Wed, 29 Aug 2001 14:24:41 -0700374_- Hi Jeff!
Unfortunately, while the PARTKEYU will control updates of partitioning keys, it will only do so for tablespaces which were created after the implementation of the ZPARM. For tablespaces created before the maintenance which allowed for the PARTKEYU ZPARM, they will need to be dropped and recreated. As far as I know, there is no work around for this. [...]
4495 15 38_Re: Reorg of large SCT02... TONIGHT!!!12_Richard Pack23_rpack1@MAIL.STATE.MO.US31_Wed, 29 Aug 2001 16:49:47 -0500353_- thanks for the responses, either will work. I have decided to go to tape. IBM's response back is to query SYSSTMT for the calculation. I did that and fiqured I needed only 2000 cylinders, if my formula was correct. I am going to tape anyway to reduce the chance of problems, time is not an issue for us on this development region. thanks to all. [...]
4511 117 25_Re: "Little DB2" question13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Thu, 30 Aug 2001 00:18:04 +0100415_US-ASCII Aurora,
I'll try to answer you questions -
First the insert statement -
Could it be that you have numeric values for all but CUSTOMER_ID ? If so you don't surround the values which are numeric with quotes. So you statement should read -
INSERT INTO ACCOUNTINFO (ACCOUNTNO, CUSTOMER_ID, SORTCODE, PAIDIN, PAIDOUT, BALANCE) VALUES (1234567890, 'CID123', 460516, 0, 0, 20000) [...]
4629 58 21_Re: Query performance15_Mallett, Steven31_Steven.Mallett@TEAM.TELSTRA.COM31_Thu, 30 Aug 2001 11:51:17 +1100317_- Dimtris,
Do you mean to ask which of these indexes should be the clustering index? If so you would usually choose the index that best fits the 'natural' order of the data, which is probably c2. I guess the c1 index must be there to ensure uniqueness of values in that column, to support other SQL or RI [...]
4688 152 26_Re: COBOL II/DB2 Questions16_Thomas E. Faglon21_tfaglon@TELCORDIA.COM31_Wed, 29 Aug 2001 21:31:51 -0400519_us-ascii answers :->
Keith Gilbert on 08/29/2001 12:43:27 PM
Please respond to DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: (bcc: Thomas E. Faglon/Telcordia) Subject: Re: COBOL II/DB2 Questions
Ronald,
Here are some COBOL and DB2 questions we ask in our technical interviews. I got these questions from a book called "Ace the Technical Interview", an excellent source for questions like these. [...]
4841 146 25_Re: "Little DB2" question13_Steve Tennant28_steve.tennant@CUSTOMS.GOV.AU31_Thu, 30 Aug 2001 11:42:14 +1000384_iso-8859-1 I'm sure he won't take offence Aurora, just leave your soul at the door.
Slightly more seriously...I just tried what you did and got exactly the same!! Then I spotted my mistake. I was using the history feature of the CLP to bring back commands and retry them and in my select statements I had spelled the table name correctly and in the inserts misspelled it. [...]
4988 70 27_Re: INSERT Trigger for LOAD11_James Szabo18_jim.szabo@CORE.COM31_Wed, 29 Aug 2001 23:05:27 -0400354_iso-8859-1 James,
Another possibility that we are considering is the design that all triggers have no logic in them other than calling a stored procedure, where the real business logic is.
This would allow you to build a manual process to fire the stored procedure from a stub program, that could use the same input file as your load. [...]
5059 76 39_Re: WebSphere on AIX, WAS DB on OS/390?8_Sean Lee16_leese@US.IBM.COM31_Wed, 29 Aug 2001 21:59:29 -0500624_- Bill,
The configuration you describe, using WebSphere for AIX and DB2 UDB for OS/390 as the repository / session DB, is fully supported by IBM. Unfortunately, the documentation for this architecture which is provided with WebSphere is inadequate. At certain maintenance levels, the provided DB2 UDB for OS/390 DDL may even contain syntax errors. We have opened an APAR to correct this documentation and the supplied DDL which should be incorporated into the WebSphere info center shortly. In the meantime, I have put together a white paper to address these issues. The paper contains a new version of the DDL, [...]
5136 104 25_Re: "Little DB2" question17_Tomy Widhartomo S28_tomyws@MITRAINFOSARANA.CO.ID31_Thu, 30 Aug 2001 10:53:23 +0700395_us-ascii Hi, I am sorry if I don't understand the problem correctly (my english is not very good :) ), but I noted that you used ';' at the end of the SQL sentences you must have not, cause it will make your sentences not valid, if you still want to use this, try to use command windows (I assumed you used windows OS) then you type : db2 -t so the termination character will be set to ';' [...]
5241 238 25_Re: "Little DB2" question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 30 Aug 2001 06:53:36 +0100437_- Aurora
no offence taken ..
I trust all is ok now?
i.e as stated before
1. Use Schema name 2. ' instead of ; 3. NAME instead of TNAME 4. / to wrap lines to the CLP (command line processor)
DB2 UDB is a great product .. once you know it. Imagine what you would have had to work with when it was version 1.0 (whcih I started on back in the early 90's ....... all command line driven, well mostly). [...]
5480 17 28_Question on Auditing Changes13_Kim Comanchee13_KimC@CDTG.NET31_Thu, 30 Aug 2001 01:48:23 -0500364_- I have a client who needs to keep audit information on who modified what column or deleted a row in a particular table. I plan to create a mirror table and lable it as Audit_History, then set up a trigger to insert a row whenver a row is modified or deleted. Does anyone handle there Audits in a different manner? Is there any new audit software avalaible? [...]
5498 27 31_Re: Additional Catalog Indexes?10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 30 Aug 2001 10:11:18 +0200295_us-ascii I appreciated (when it was announced) the fact I could define a user index in a catalog table. It's useful but I know (at least) 2 cases we had problem in DB2 migration (and during DR as I can see). Because no one wrote any document or warning about the creation of these indexes. [...]
5526 87 25_Re: "Little DB2" question20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Thu, 30 Aug 2001 10:28:45 +0200346_iso-8859-1 Ok Aurora, try to "prendere il diavolo per la coda".
>After I sent my note to the list, I realised that TNAME should have been >NAME and corrected the query, but it still gave me an error, apparently >it >does not like the semi-colon - so now I am trying my stuff without >semi-colons but I'm still not getting very far... [...]
5614 102 42_Re: MS Query to DB2 OS/390 via DB2 Connect10_teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 30 Aug 2001 19:13:45 +1000350_us-ascii Hi Tina,
May or maynot be of help. I dont see a "AS newcolname" in my MS-query SQL. (DB2 connect 5.2, DB2 /OS390 v6). It generates it without an AS.
Select col1 'newcol' from tab1
If I code it as COL1 AS NEWCOL, then also it works.
But if I include the quotes around NEWCOL after the AS, then I get a - 104. [...]
5717 51 12_Bind options28_Mayannan, Dharmalingam (CTS)26_MDharmal@CHN.COGNIZANT.COM31_Thu, 30 Aug 2001 14:51:38 +0530356_iso-8859-1 Hello everybody, Greetings. How do the following Bind options function and how do they affect the performance of the SQL? 1. RELEASE(DEALLOCATE) 2. RELEASE(COMMIT) 3. DEGREE(1) 4. REOPTVAR(N)
Response requested immediately.
Kind regards, Dharmalingam.M.
"Many have seen the horizon yet none have touched it".
5769 73 32_AW: Question on Auditing Changes12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Thu, 30 Aug 2001 11:33:44 +0200655_iso-8859-1 Hi Kim,
If you want to keep the audit information in the same table you could do the following.
Let me take table EMP (from the samples) as an example with its cols EMPNO FIRSTNAME ... You add the following auditing columns: INSERT_USER INSERT_DATE INSERT_TIME DELETE_USER DELETE_DATE DELETE_TIME
The valid row for every EMPNO has DELETE_DATE = '31.12.9999' and DELETE_DATE = '24.00.00'. When an UPDATE occurs you update the valid row and make it invalid (DELETE_DATE=CURRENT DATE, DELETE_TIME=CURRENT TIME) and insert a new row. DELETE_DATE and DELETE_TIME should be part of a unique index to make sure that there is [...]
5843 80 16_Re: Bind options16_S, Sanjeev (CTS)26_SSanjeev@CAL.COGNIZANT.COM31_Thu, 30 Aug 2001 15:37:07 +0530491_- Some briefs on all the 4 listed by you.
1. RELEASE(DEALLOCATE) - Release the locks (except row/page, depend on ISOLATION) during plan deallocation. Reset cache used for dynamic prefetch, index lookaside and lot more. 2. RELEASE (COMMIT) - Release the locks during commit Reset cache used for dynamic prefetch, index lookaside and lot more.. 3. DEGREE (1) - No parallelism is in affect. 4. REOPT(VAR) - Reoptimize the SQLs when actual values are obtained during execution time. [...]
5924 18 29_Re: Phantom Claimers - URGENT15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 30 Aug 2001 05:02:25 -0500523_- Hi Robert
I just did the same: DIS DB(...) SPACENAM(...) LOCKS and got the same information:H-X,PP,I, but we can use the tablespace without problems. The manual says: page set or partition P-Lock (i.e. physical lock) hold by the DB2-member in exclusive mode. I means: lock is released when the dataset is closed. So it seems, you have another problem nothing to do with your message, because it seems to be a normal one. Did you try to STOP the partitions in question (with keyword AT(COMMIT)) an start them? [...]
5943 231 25_Re: "Little DB2" question9_Chris Tee23_chris.tee@UK.ZURICH.COM31_Thu, 30 Aug 2001 11:19:02 +0100619_us-ascii Aurora,
The ADELL-DB2-BANKDB that you see is actually systemname - db2instance - dbname
Chris
Aurora Dell'Anno cc: Sent by: DB2 Data Subject: Re: [DB2-L] "Little DB2" question Base Discussion List
29/08/01 17:35 Please respond to DB2 Data Base Discussion List
Leslie et all,
sorry if I offended Satan... joking aside this thing is driving me mental! so I may hopefully be excused for calling it nasty to its face :-( [...]
6175 58 85_Re: Insert, delete, select, update access for a id for all the ta bles in a subsystem13_Thomas Schulz20_thomas.schulz@SVI.DE31_Thu, 30 Aug 2001 05:13:34 -0500911_- I use this job for example to grant select on all tables to a user
Thomas Schulz
//USER11 JOB (T1111),'Grant', // CLASS=C,MSGCLASS=X,MSGLEVEL=(1,1), // NOTIFY=USER,REGION=4M //* //JOBLIB DD DSN=SDSNLOAD,DISP=SHR // DD DSN=SDSNEXIT,DISP=SHR //* //DELETE EXEC PGM=IEFBR14 //DD04 DD DSN=USER.SPUFI.OUTPUT, // DISP=(MOD,DELETE),SPACE=(TRK,0) //* //GRANT1 EXEC PGM=IKJEFT01,DYNAMNBR=100 //* //SYSPRINT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //LISTING DD SYSOUT=* //SYSPUNCH DD SYSOUT=* //SYSREC00 DD DSN=USER.SPUFI.OUTPUT,DISP=(,CATLG,DELETE), // SPACE=(CYL,(5,2),RLSE),UNIT=SYSDA, // DCB=(RECFM=FB,LRECL=80,BLKSIZE=8000) //SYSTSIN DD * DSN SYSTEM(DBT2) RUN PROGRAM(DSNTIAUL) - PLAN(DSNTIB51) LIB('STAT4.IBDB2510.RUNLIB.LOAD') PARM('SQL') //SYSIN DD * SELECT SUBSTR( 'GRANT SELECT ON '||STRIP(CREATOR,T,' ')|| '.'||STRIP(NAME,T,' ')||' TO ANYBODY;'|| ' ' , 1 , 80 ) FROM SYSIBM.SYSTABLES WHERE TYPE [...]
6234 14 26_Re: CALL DSNTIAR from REXX14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 30 Aug 2001 05:23:27 -0500386_- If you want the rest of the sqlca formatted, have a look at
http://jupiter.ryci.com/cgi/wa.exe?A2=ind0008A&L=DB2-L&P=R9404
James
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
6249 69 16_Re: Bind options18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 30 Aug 2001 16:29:39 +0530547_- Hi,
Through RELEASE option you direct DB2 to relinquish locks that have been acquired during table updates. This option can take 2 diff values..
RELEASE(COMMIT) - to release tablespace and table level locks at every Commit. Also erases cache features used for Dynamic Prefetch and Index lookaside(these caches provide improved performance)
RELEASE(DEALLOCATE) - to release tablespace and table locks when application accessing table ends. The caches are retained post commits and performance is not adversely affected. [...]
6319 21 50_DB2 internal security into RACF ext.sec conversion17_Frans van Schaick29_Frans.van.Schaick@MAIL.ING.NL31_Thu, 30 Aug 2001 06:22:07 -0500435_- Collegues, We have converted all our DB2 internal authorization rules converted into RACF-profiles. But we first got a security problem at a LOCK TABLE command. RACF tells that the uid has no SELECT privilege on the table. Strange because the uid should have had the necessary authorizations via 'implicit privileges of ownership'(DB2)? Also with a BIND we got the same. We noticed that this only happened when OWNER<>QUALIFIER [...]
6341 96 52_Re: How to store special character in the data base.10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 30 Aug 2001 17:18:38 +0530591_iso-8859-1 Hi Greg,
Lets say i have an table empdemo (a integer, b character(20)). I want to insert aa ss into the character i.e. b. Now please tell me what would be the appropriate inert and select queries for this data.(inserting carriage return between aa and ss)
Regards Praveen
-----Original Message----- From: Palgrave, Greg [mailto:greg.palgrave@UNISYSWEST.COM.AU] Sent: Thursday, August 23, 2001 2:44 PM To: DB2-L@RYCI.COM Subject: Re: How to store special character in the data base. [...]
6438 18 49_inserting delimiting chars during table download.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Thu, 30 Aug 2001 06:55:00 -0500379_- Hi friends,
I am seeking urgent help in downloading a table such that data of each column is separated by a ';' from the next column in the dataset tha would hold the downloaded data. I guess this can be done by including ';' in the select stmt in the SYSIN but it not an elegant solution when a table contains large number of columns. Does anyone have a solution. [...]
6457 72 25_Re: "Little DB2" question16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Thu, 30 Aug 2001 13:07:48 +0100597_us-ascii well,
first of all thanks very much Leslie, Fabrizio, Horacio, Philip, Steve, Tomy and Chris,
last night at about 1 am I finally managed to get the dratted thing working and this is what had happened:
first of all I re-created the table with schema.tablename through the GUI I then proceeded to issue my INSERT INTO DB2ADMIN.ACCOUNTINFO.... through CLP - this time the processing went well, SQLcode 0 and everybody including me feeling quite satisfied (by the way Philip, since now I was in it just to see the blood and guts, I had created all columns as CHAR - [...]
6530 209 62_Re: DB2 conditional REORG parameters: what do they really mean14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 30 Aug 2001 13:31:47 +0100479_iso-8859-1 We've been through this recently, but VERY quickly:
OFFPOSs refer to rows that have been located away from where they should have been put (according to the clustering index)
INDREFs refer to rows that CANNOT be put back in their original location after an insert as they are longer than they were (variable length rows obviously). DB2 then puts a pointer in the old row location which points to the new row location (rows are INDirectlyREFerenced) [...]
6740 150 32_Re: Question on Auditing Changes14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 30 Aug 2001 13:36:16 +0100390_iso-8859-1 Apologies for regular list(eners) - I am about to repeat myself (and Roger).
DB2 already keeps an audit trail of ALL update activity - it's called the DB2 log. The simplest auditing method uses a tool to read the log and produce whatever report you are needing.
CA, BMC and even IBM produce such tools (see Unicenter Log Analyzer for Db2 from CA for example). [...]
6891 75 27_Re: INSERT Trigger for LOAD14_Grainger, Phil20_Phil.Grainger@CA.COM31_Thu, 30 Aug 2001 13:47:51 +0100357_iso-8859-1 Obvious question, so sorry for asking it!
I'm assuming this means NO before INSERT triggers and NO STATEMENT triggers will be triggered by on-line LOAD RESUME - correct??
Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com
6967 88 32_AW: Question on Auditing Changes12_Thomas Weber23_thomas.weber@LDS.NRW.DE31_Thu, 30 Aug 2001 15:03:45 +0200617_iso-8859-1 Hi Phil,
I agree entirely, but what if the chiefs refuse to purchase such a tool?
Regards
Thomas
-----Ursprüngliche Nachricht----- Von: Grainger, Phil [mailto:Phil.Grainger@CA.COM] Gesendet am: Donnerstag, 30. August 2001 14:36 An: DB2-L@RYCI.COM Betreff: Re: Question on Auditing Changes
Apologies for regular list(eners) - I am about to repeat myself (and Roger).
DB2 already keeps an audit trail of ALL update activity - it's called the DB2 log. The simplest auditing method uses a tool to read the log and produce whatever report you are needing. [...]
7056 52 38_Copy a Plan to a different DB2 SUBSYS?16_Petteno' Massimo27_Petteno.Massimo@GENERALI.IT31_Thu, 30 Aug 2001 15:03:04 +0200138_iso-8859-1 Is it possible to copy or move a DB2 PLAN (member list) from a DB2 (source) to a different DB2 (target)?
TIA Max
7109 178 32_Re: Question on Auditing Changes14_Scott Trometer22_scott.trometer@RCI.COM31_Thu, 30 Aug 2001 08:04:10 -0500510_iso-8859-1 Apologies for not list(ening) to previous posts on this subject...but do the tools you mention allow one to capture before/after data values for update or delete ?
We are looking to do the same types of things Kim mentions, but we are more interested in capturing x versions of the row vs who changed it...its more history than it is an audit trail. I am concerned about insert,update,delete performance if we start adding triggers to update history tables (virtually doubles the work). [...]
7288 106 25_Re: "Little DB2" question23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 30 Aug 2001 14:08:14 +0100491_- Aurora
glad you succeeded.
When I am next at Candle I'll stick my head around the corner and say hello :-).
Leslie Sony Europe Ltd (Surrey - Weybridge)
-----Original Message----- From: Aurora Dell'Anno [mailto:Aurora_DellAnno@CANDLE.COM] Sent: Thursday, August 30, 2001 1:08 PM To: DB2-L@RYCI.COM Subject: Re: "Little DB2" question
well,
first of all thanks very much Leslie, Fabrizio, Horacio, Philip, Steve, Tomy and Chris, [...]
7395 81 34_Re: Recovery Point from Image Copy64_Todd Burch *****************************************************22_tburch@CDBSOFTWARE.COM31_Thu, 30 Aug 2001 08:24:16 -0500583_ISO-8859-1 Gene,
As Michael Murley mentioned, yes, CDB does have a solution to this common need. As a matter of fact, we have two. (Two today, more tomorrow...:-)...)
First, CDB/Auto-Unload can unload from image copies or DSN1COPYs. If the OBIDs are different on TEST than they are on production, with CDB/Auto-Unload you can indicates this, telling CDB/Auto-Unload to look for the PRODUCTION OBID instead of the TEST system OBID. If your image copy is a single-table tablespace, you don't even have to know the OBID, just tell CDB/Auto-Unload to ignore the OBID. [...]
7477 95 16_Re: DB2 V7 OS39023_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 30 Aug 2001 14:19:17 +0100331_iso-8859-1 Eric
I loved your last point ... I can relate to it so well.
>However, the IBM utils are much more trouble free, and unlike an OEM Reorg product have never lost data for us.
We are going to be going to V7 soon and are obviously looking at the tools that come with (did I say that) the product. [...]
7573 86 32_Re: Question on Auditing Changes12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 30 Aug 2001 06:23:08 -0700470_us-ascii That's true. But. We have several web applications where we're using a single userid to connect to DB2 (thousands of individual userids connecting to web server). And we need to know the real individual userid that made the change, not the single signon userid. Without putting the userid in every table, you need a separate table that contains the userid and some auditing information. DB2 writes the before and after changes but not the original userid. [...]
7660 19 42_Re: Copy a Plan to a different DB2 SUBSYS?15_Walter Janissen27_walter.janissen@VICTORIA.DE31_Thu, 30 Aug 2001 08:31:09 -0500340_- What do you mean with copy?
BIND COPY isn't an option, because it's only available for packages (it's an old requirement to do a remote plan bind), so the answer is no.
But you can generate an appropiate BIND PLAN command using Select and execute that command on the target (assuming the DBRMLIBs are available there). [...]
7680 39 34_loads - copy yes or nonrecoverable17_John Breidenstine31_JBreidenstine@LEGIS.STATE.PA.US31_Thu, 30 Aug 2001 09:37:21 -0400600_iso-8859-1 Hello everyone,
We have DB2 on AIX enabled for log retention. We reload allot of our tables from the mainframe using the load command everyday. Currently we use the NONRECOVERABLE parameter in the load command to prevent the tables from going into a backup pending state. The problem with this is - if you restore, then roll forward to a point-in-time beyond the load, the tables that were loaded are now invalid and must be dropped and recreated. This has caused us to look into using the COPY YES parameter instead. This will provide the roll-forward with the information in [...]
7720 55 25_Error in DB2DIAG.log file12_Dauncey, Ian18_idauncey@FNB.CO.ZA31_Thu, 30 Aug 2001 15:20:55 +0200686_iso-8859-1 Hi Listeners,
Can anybody tell me want is causing these errors? We have received similar errors on two different servers in two different locations.
Thu Aug 30 13:38:50 2001 DB2 pid(61) tid(45) process (DB2SYSC.EXE) base_sys_utilities sqleagnt_sigsegvh Probe:10 Database (FNBLDB) Error in agent servicing application with CLIENT PID:3731
Thu Aug 30 13:38:50 2001 DB2 pid(61) tid(45) process (DB2SYSC.EXE) base_sys_utilities sqleagnt_sigsegvh Probe:10 Database (FNBLDB) Error in agent servicing application with INBOUND APPLICATION ID:5a41 464e 4230 3031 2e4f 3031 3134 4536 ZAFNB001.O0114E6 342e 4236 3543 4145 4631 3439 3538 4.B65CAEF14958 [...]
7776 157 38_Re: UPDATE OF PARTITIONING KEY COLUMNS16_Aurora Dell'Anno26_Aurora_DellAnno@CANDLE.COM31_Thu, 30 Aug 2001 15:02:13 +0100353_us-ascii Jeff,
In v5, a new subsystem parameter PARTKEYU, has been added to macro DSN6SPRM - as already stated clearly by lots of our colleagues.
To modify this and other parameters, which were added through APARs, you must edit the macros in job DSNTIJUZ, then assemble and link-edit job DSNTIJUZ, then STOP and START the subsystem. [...]
7934 54 42_Re: Copy a Plan to a different DB2 SUBSYS?16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Thu, 30 Aug 2001 09:58:11 -0400465_us-ascii That's one of the advantages of the third party products: productivity. They can easily generate the bind cards to handle this kind of situation. At least I know that BMC and Ca/Platinum both can, and probably others.
Rick Creech
Walter Janissen cc: Sent by: DB2 Data bcc: Base Discussion Subject: Re: Copy a Plan to a different DB2 List SUBSYS? [...]
7989 54 41_R: Copy a Plan to a different DB2 SUBSYS?16_Petteno' Massimo27_Petteno.Massimo@GENERALI.IT31_Thu, 30 Aug 2001 16:09:00 +0200179_iso-8859-1 I don't have the DBRMs and neither the program sources. Unfortunately (caused by a company split) I should run the application on a different DB2 ...
Regards Max
8044 65 42_Re: Copy a Plan to a different DB2 SUBSYS?0_20_John_Lendman@FPL.COM31_Thu, 30 Aug 2001 10:10:18 -0400497_us-ascii I am getting in on this thread a little late, but if you have Platinum's Plan Analyzer you can you into the source sub system and put a B on the plan you want to copy. You can then do an EDIT command on the command like. This will take you to the simple edit, then you can enter in the command line ISPFEDIT. At this point you can save all the bind control cards to a dataset. Then you can logon on to you TARGET subsystem and go to Platinum Plan Analyzer pull up any plan with a B. [...]
8110 22 34_Re: Query Large denormalized Table12_Alan Gredell28_agredell@KEMPERINSURANCE.COM31_Thu, 30 Aug 2001 08:58:29 -0500568_- Thanks for trying, Sanjeev, but you're right, it was more complex than your query would solve. There are several rows for each address, so the "select distinct *" would have returned an answer several times the number of actual child locations.
We did resolve this, by having the programmer who creates the load file read in the source file and spit out a simple DB2 table of the parent number and count, where the count is incremented each time the row is already found. This table might also provide a solution to the long time it takes to expand (and [...]
8133 37 42_AW: Copy a Plan to a different DB2 SUBSYS?12_Peter, Georg15_G.Peter@DZBW.DE31_Thu, 30 Aug 2001 16:14:54 +0200537_iso-8859-1 No Max,
assuming you are on DB2 for OS/390 you have to take the related DBRM's and then bind a new plan or package set (= collection) on the target subsystem.
HTH.
With kind regards - mit freundlichen Grüssen, Georg H. Peter ------------------------------------------------------------------- -----Ursprüngliche Nachricht----- Von: Petteno' Massimo [mailto:Petteno.Massimo@GENERALI.IT] Gesendet am: Donnerstag, 30. August 2001 15:03 An: DB2-L@RYCI.COM Betreff: Copy a Plan to a different DB2 SUBSYS? [...]
8171 133 42_Re: MS Query to DB2 OS/390 via DB2 Connect11_Tina Hilton21_Tina.Hilton@BMSUS.COM31_Thu, 30 Aug 2001 09:20:21 -0500409_iso-8859-1 Kals,
What release of MS Query are you on? If I double-click on the column header in the query, it brings up a box that let's me change the column heading. When I do that, I get the -104 and looking at the SQL I see it has generated col1 AS 'newcol1'. I've tried editing the SQL to remove the quotes around the column header, but I get the same error so I assume MS Query puts it back. [...]
8305 47 45_Re: R: Copy a Plan to a different DB2 SUBSYS?16_Charles F Creech24_cfcreech@DUKE-ENERGY.COM31_Thu, 30 Aug 2001 10:20:44 -0400485_us-ascii I seem to remember Mr. Pillay stating in a previous discussion of several months back that he had a program for extracting the DBRMs from the catalog. Sounds like this might be what you need. You might find some info on searching the archives.
Rick Creech
"Petteno' Massimo" cc: Sent by: DB2 Data bcc: Base Discussion Subject: R: Copy a Plan to a different DB2 List SUBSYS? [...]
8353 104 38_Re: loads - copy yes or nonrecoverable20_Sitaram Vijay kartik21_vijaykartik@YAHOO.COM31_Thu, 30 Aug 2001 07:40:24 -0700452_us-ascii Hi John:
The NONRECOVERABLE option will avoid backup pending states. The reason for moving into backup pending is because the database is enabled for archival logging.
If you are very particular about archival logging, then you need to use COPY YES which will NOT leave the DB in Backup Pending state.
Else, you could turn off archival logging (thereby disabling all rollforward recovery operations) and do the load. [...]
8458 222 32_Re: Question on Auditing Changes13_McDonald, Ken20_Ken_McDonald@BMC.COM31_Thu, 30 Aug 2001 09:51:35 -0500587_iso-8859-1 Scott,
All of these log tools allow to capture both before/after values for updates and values associated with inserts and deletes. At least the BMC and CA tools have LOAD format outputs which can be used to populate a history table (IBM's may have LOAD output too). BMC's Log Master for DB2 has LOAD format options which make the output portable to off mainframe databases as well. I think I've heard in IBM presentations mention a tool designed for capturing row versions. I'm partial to the log tools because of additional functionality provided over auditing. [...]
8681 45 29_Re: errror regarding trigger.10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 30 Aug 2001 20:26:56 +0530560_iso-8859-1 Hi All,
while firing the following triggger from the command prompt i am getting this error.
CREATE TRIGGER D AFTER UPDATE ON B REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL INSERT INTO D VALUES( SELECT a FROM A,N.b1,N.b2,N.b3);
GETTING AN 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:SQL0104N An unexpected token "A" was found following "". Expected tokens may include: "+ ) - ". SQLSTATE=42601 [...]
8727 48 70_Regarding alternative terminating character+How to fire nested QUERY ?10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 30 Aug 2001 20:33:39 +0530712_iso-8859-1 Hi All,
1. Can somebody tell me regarding alternative terminating character. (apart from ; )on SQL prompt.
2.
Can somebody tell me how to fire the nested query.
For ex:-
insert into empdemo values(6, 'aa',(select dept from empdemo where empid=8)) ;
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: SQL0104N An unexpected token "DEPT" was found following "". Expected tokens may include: "+ ) - ". SQLSTATE=42601 [...]
8776 37 26_How to fire nested QUERY ?10_praveen_kj19_praveen_kj@INFY.COM31_Thu, 30 Aug 2001 20:33:43 +0530572_iso-8859-1 Hi All,
Can somebody tell me how to fire the nested query.
For ex:-
insert into empdemo values(6, 'aa',(select dept from empdemo where empid=8)) ;
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: SQL0104N An unexpected token "DEPT" was found following "". Expected tokens may include: "+ ) - ". SQLSTATE=42601 [...]
8814 99 29_Re: Error in DB2DIAG.log file23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 30 Aug 2001 16:17:01 +0100492_iso-8859-1 not seen this before .. but just a thought - have you raised it with IBM incase they have?
Have you searched the archives?
some realy stabs in the dark :
1. You haven't got a mix of binaries have you .. different release levels or pathing to a mix?
All the best
Les
-----Original Message----- From: Dauncey, Ian [mailto:idauncey@FNB.CO.ZA] Sent: Thursday, August 30, 2001 2:21 PM To: DB2-L@RYCI.COM Subject: Error in DB2DIAG.log file [...]
8914 15 26_Re: Authorization Question12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 30 Aug 2001 10:06:22 -0500525_- For the first look, make sure where the errors are coming from. The SQLCA has an indication of the product, module, ... returning the error. If the error comes from DB2, DSN prefix, then I'd use the audit trace to get everything about the request.
Roger Miller, DB2 for z/OS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
8930 16 35_Re: DB2 for OS/390 and web security12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 30 Aug 2001 10:12:29 -0500355_- There are almost as many techniques for security as there are people implementing it. Your versions of the software matter more than usual, since we put very substantial changes into V7. The option that makes the most sense to me is Kerberos. See the V7 Presentation Guide, SG24-6121, part 4, chapter 6, section 6.2 security enhancements for more. [...]
8947 15 43_Re: Unusual behavior with stored procedure.12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 30 Aug 2001 10:21:27 -0500302_- Timerons are not necessarily comparable. What are the real times for the processes? What are the access paths? If the processing times and access paths differ, then the process is fairly simple. If they are about the same, then I don't think this is a problem.
Roger Miller, DB2 for z/OS [...]
8963 26 8_Quickie.10_Mark Frost23_mark.frost@CITICORP.COM31_Thu, 30 Aug 2001 15:23:21 +0100507_ISO-8859-1 Hi
Can anyone tell me the definition of the ECPU, ECPU-TIME field on SDSF DA panel ? The SDSF manual is somewhat less than detailed on the matter..
many thanks Mark.
Mark Frost. Database Support. Citibank Tel - 0207 500 2632.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
8990 67 24_DB2 Control Center on NT0_18_mebert@AMADEUS.NET31_Thu, 30 Aug 2001 17:31:19 +0200541_us-ascii Hi List,
in response to a question from a developer I have just tried to enter the DB2 Control Center on my NT machine for the first time. It asks for a UserID and Password ("Control Center Sign On localhost").
I have not the slightest idea what UserID and PW it expects. None of my LAN/NT/TSO combinations works. If I use the LAN/NT UID, I get a negative response quickly. If I use a TSO UID, it takes some time. The installation didn't ask for any UID/PW as far as I remember. I can bring up and use the Client [...]
9058 22 17_udb Rexx on WinNt15_Neff, Stephen R16_NeffSR@STATE.GOV31_Thu, 30 Aug 2001 11:31:53 -0400395_iso-8859-1 Hello,
I have installed DB2 UDB personnel and EE DB2 UDB on a win/nt machine. Would anyone be able to tell me if REXX is available with the install of these products or does it need to be purchased separately? If it needs to be purchased would the OBJECT REXX for Windows from IBM be the product to buy and does this product include the necessary functions to access UDB? [...]
9081 22 12_Re: Quickie.10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 30 Aug 2001 17:57:26 +0200468_us-ascii Hi i think they means:
ECPU-TIME Accumulated CPU time for the current job step by and on behalf of the address space, in seconds
ECPU% CPU usage consumed in the address space
HTH
Max Scarpa
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
9104 139 28_Re: DB2 Control Center on NT23_Pendlebury-Bowe, Leslie34_Leslie.Pendlebury-Bowe@EU.SONY.COM31_Thu, 30 Aug 2001 16:59:20 +0100566_- Michael
Hi.
I had to create a userid on my machine in order get things working .. hope that helps. I am win 2000
Les
-----Original Message----- From: mebert@AMADEUS.NET [mailto:mebert@AMADEUS.NET] Sent: Thursday, August 30, 2001 4:31 PM To: DB2-L@RYCI.COM Subject: DB2 Control Center on NT
Hi List,
in response to a question from a developer I have just tried to enter the DB2 Control Center on my NT machine for the first time. It asks for a UserID and Password ("Control Center Sign On localhost"). [...]
9244 50 21_Re: udb Rexx on WinNt13_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Thu, 30 Aug 2001 17:07:42 +0100429_US-ASCII Hello Stephen.
REXX isn't included with DB2.
However I'd say that REXX (at least off the mainframe) is a "yesterday product" and you'd be better looking at alternatives - which are free as well.
May I suggest Perl : you can get the latest version for Windows from ActiveState (http://www.activestate.com), and then you need to install modules DBI and DBD::DB2 using PPM (Perl Package Manager). [...]
9295 126 12_Re: Quickie.11_Mayo Arthur19_Arthur.Mayo@IRS.GOV31_Thu, 30 Aug 2001 11:51:45 -0400605_- ECPU-Time Accumulated CPU time consumed within the address space, for the current job step, in seconds
HTH
> -----Original Message----- > From: Mark Frost [SMTP:mark.frost@CITICORP.COM] > Sent: Thursday, August 30, 2001 10:23 AM > To: DB2-L@RYCI.COM > Subject: Quickie. > > Hi > > Can anyone tell me the definition of the ECPU, ECPU-TIME field on SDSF > DA panel ? The SDSF manual is somewhat less than detailed on the > matter.. > > many thanks > Mark. > > > Mark Frost. > Database Support. > Citibank > Tel - 0207 500 2632. > > ================================================ > To [...]
9422 124 41_Trigger Functionality in Version 7 OS/39014_Bruce Bernhart25_bbernhart@NEWLOGIXINC.COM31_Thu, 30 Aug 2001 10:54:58 -0500399_iso-8859-1 The following trigger does not work in version 6 (OS/390) The Scaler Select is not a valid expression.
Does anyone know if this would work in version7 on OS/390?
CREATE TRIGGER B_ACT_I NO CASCADE BEFORE INSERT ON ACCOUNT REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET NEW.LST_UPDT_EMPL_ID = (SELECT EMPLOYEE_ID FROM EMPLOYEE_ID WHERE HOST_ID = USER); END #
9547 140 28_Re: DB2 Control Center on NT13_Cobbaert Marc19_marc.cobbaert@SD.BE31_Thu, 30 Aug 2001 18:01:04 +0100419_ISO-8859-1 Hi Michael,
It depends on whether you have a direct connection or a connection thru a gateway to your OS/390 DB2 instance. With a direct connection it will ask immediately for your TSO user, with a gateway connection it will first ask for the NT user which you entered when installing the gateway (DB2 Enterprise Edition) and then when drilling down your subsystems it will ask for your TSO uid. [...]
9688 70 30_Re: How to fire nested QUERY ?13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Thu, 30 Aug 2001 11:27:27 -0500387_iso-8859-1 Praveen,
This should work on Non-OS/390 or OS/390 V6 onwards (assuming there are 3 columns in empdemo too):
insert into empdemo select 6, 'aa', dept from empdemo where empid=8;
Regards Terry Purcell Yevich Lawson & Assoc Inc (YL&A) IBM Gold Consultant - DB2 IBM Certified Solutions Expert DB2 V7 Database Administration OS/390 http://www.ylassoc.com [...]
9759 35 7_DB2 UDB11_Moore, Tony15_TMoore@IKON.COM31_Thu, 30 Aug 2001 12:44:13 -0400401_iso-8859-1 Yo Listers, I'm on the hunt for a version of DB2 Connect that will work with both DB2/MVS V4 and DB2 for OS390 V6. So far I have found that DB2 Connect V6 and V7.2 will not work with DB2/MVS V4. I dropped down to DB2 Connect V5.2. This release will connect successfully to DB2/MVS V4, but I'm having troubles getting it to connect to DB2 for OS390 V6, but was thinking that it should. [...]
9795 56 21_Re: udb Rexx on WinNt18_Gert van der Kooij15_geko@WANADOO.NL31_Thu, 30 Aug 2001 19:07:38 +0200540_iso-8859-1 Hi, Rexx isn't installed anymore when installing DB2. Besides that, this note is copied from the V6.1 Application Development Guide:
********* Note: REXX support stabilized in DB2 Version 5, and no enhancements for REXX support are planned for the future. For example, REXX cannot handle SQL object identifiers, such as table names, that are longer than 18 bytes. To use features introduced to DB2 after Version 5, such as table names from 19 to 128 bytes long, you must not write your applications in REXX ********* [...]
9852 48 14_SQL -171 error13_Olson, Carlos14_COlson@QRS.COM31_Thu, 30 Aug 2001 10:04:20 -0700655_iso-8859-1 Hi everyone, A developer is trying to imbed the following SQL statement into a program: SELECT UPC_NBR ,SIZE_DESC ,SIZE_CODE ,SLTN_CD ,PRD_ID FROM AQA1.NMU_NM_MULT_UPC1 WHERE LCASE((REPLACE(STRIP(SIZE_DESC), ' ',''))) = LCASE((REPLACE(STRIP(:A), ' ','')));
It apparently doesn't like the host variable :A and receives a -171 SQL error. The statement executes successfully in DSNTEP2 when the host variable is replaced with a literal as in the following: SELECT UPC_NBR ,SIZE_DESC ,SIZE_CODE ,SLTN_CD ,PRD_ID FROM AQA1.NMU_NM_MULT_UPC1 WHERE LCASE((REPLACE(STRIP(SIZE_DESC), ' ',''))) = LCASE((REPLACE(STRIP('8 FL. OZ.'), ' ',''))); [...]
9901 30 40_testing performance with DB2 compression12_Hend Dwiyono16_hdwi@HOTMAIL.COM31_Thu, 30 Aug 2001 11:49:39 -0600522_- Hi folks...
I need help with expert opinion on this issue. Our system has a 64 partitions table with 200 million rows. Only one partition currently compressed and since we are CPU-constrained, we really want to test the CPU overhead vs. savings from I/O, more space in bufferpool with compressed data. Is there really a good way to test the performance between one compressed partition and the other uncompressed partition ? This way we can have legitimate proof for our client if compression is beneficial. [...]
9932 62 28_Re: DB2 Control Center on NT17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM31_Thu, 30 Aug 2001 12:44:30 -0500372_us-ascii It wants your User ID and Password for your PC. As in if you were to login to your PC and not your LAN domain. If your passwords are insinc you don't have this problem.
Jeremy Schleicher May Company
From: mebert@AMADEUS.NET@RYCI.COM on 08/30/2001 10:31 AM
Please respond to DB2 Data Base Discussion List [...]
9995 50 21_Re: udb Rexx on WinNt17_Jeremy Schleicher28_Jeremy_Schleicher@MAY-CO.COM31_Thu, 30 Aug 2001 12:47:51 -0500399_us-ascii Yes you need to get a copy of REXX it is not native to NT. We are using Object Rexx. Make sure that the REXX directory is in the path of your Server or it will not work.
Jeremy Schleicher May Company
From: "Neff, Stephen R" @RYCI.COM on 08/30/2001 10:31 AM
Please respond to DB2 Data Base Discussion List [...]
10046 22 24_Db2 V5 Column Encryption11_Manoj Veedu18_mv_db2@HOTMAIL.COM31_Thu, 30 Aug 2001 11:54:11 -0600531_- Hi All,
I would really appreciate i tanybody can help me in encrypting a password column in Db2 V5 table.
Thanks
Manoj
_________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
10069 121 73_last archive log record disappeared after restart DB2 with access (maint)10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Thu, 30 Aug 2001 12:17:49 -0600658_iso-8859-1 Hi! I am doing the disaster recovery test now. I added the last archive log into BSDS successfully. When I started DB2 with ACCESS(MAINT), DB2 complained with these error messages: DSNJ113E -DBP1 DSNJR003 RBA '00000B081000' NOT IN ANY ACTIVE OR ARCHIVE LOG DATA SET. CONNECTION-ID=DBP1, CORRELATION-ID=003.RCRSC 02, MEMBER-ID=0 DSNR015I -DBP1 THE CHKPTRBA 00000B08172A SPECIFIED IN THE CRCR IS INVALID IEF450I DBP1MSTR DBP1MSTR - ABEND=S04F U0000 REASON=00000000 The last archive log is from RBA AA96000 to B2271FFF. So I printed BSDS and found the last archive log record disappeared. I added it again and printed BSDS to make sure the last [...]
10191 17 16_edit a PS,OS/39012_Amit Agarwal33_amit_agarwal@STANDARDANDPOORS.COM31_Thu, 30 Aug 2001 14:35:25 -0400416_us-ascii Is there a way to edit a large PS dataset? I have a PS which is 1400 tracks in size . When I try to open it in edit mode, it substitutes a Browse.
Thanks Amit
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
10209 38 20_Re: edit a PS,OS/39022_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Thu, 30 Aug 2001 13:40:55 -0500345_- You should be able to edit it, even if its a full Volume dataset. Check if the LRECL is less than 10 Bytes. If it is, ISPF won't allow edit on that dataset.
-----Original Message----- From: Amit Agarwal [mailto:amit_agarwal@STANDARDANDPOORS.COM] Sent: Thursday, August 30, 2001 1:35 PM To: DB2-L@RYCI.COM Subject: edit a PS,OS/390 [...]
10248 76 30_Re: How to fire nested QUERY ?15_Regan Galbraith28_Regan.Galbraith@SANLAM.CO.ZA31_Thu, 30 Aug 2001 20:45:47 +0200534_us-ascii Rather do :
insert into empdemo values(select 6, 'aa',dept from empdemo where empid=8) ;
Perhaps you have a different example, or does this suffice?
praveen_kj @RYCI.COM> on 08/30/2001 05:03:43 PM
Please respond to DB2 Data Base Discussion List
Sent by: DB2 Data Base Discussion List
To: DB2-L@RYCI.COM cc: Subject: How to fire nested QUERY ? [...]
10325 39 20_Re: edit a PS,OS/39012_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 30 Aug 2001 11:46:06 -0700625_us-ascii Sometimes I've had to break large physical sequential datsets into several pieces ,edit each piece separately, and then put all the pieces back together afterwards. Not very user friendly, admittedly, but usually better than using IEBUPDTE.
--- Amit Agarwal wrote: > Is there a way to edit a large PS dataset? I have a > PS which is 1400 tracks in > size . When I try to open it in > edit mode, it substitutes a Browse. > > Thanks > Amit > > ================================================ > To change your subscription options or to cancel > your subscription visit [...]
10365 58 78_Re: last archive log record disappeared after restart DB2 with ac cess (maint)15_Murley, Michael22_Michael_Murley@BMC.COM31_Thu, 30 Aug 2001 13:47:29 -0500419_iso-8859-1 Grace,
The Conditional Restart Control Record (CRCR) can cause log entries in the BSDS to be deleted at startup. What does your CRCR look like ?
Michael Murley BMC
-----Original Message----- From: Grace Chen [mailto:gchen@CUCENTRAL-AB.COM] Sent: Thursday, August 30, 2001 1:18 PM To: DB2-L@RYCI.COM Subject: last archive log record disappeared after restart DB2 with access (maint) [...]
10424 34 20_Re: edit a PS,OS/3908_K.Balaji19_K.Balaji@TARGET.COM31_Thu, 30 Aug 2001 13:57:58 -0500511_iso-8859-1 Probably Fileaid or Insync utility with include/omit condition will help you. Otherwise you can use syncsort with some include/omit conditions. Balaji
-----Original Message----- From: Amit Agarwal [mailto:amit_agarwal@STANDARDANDPOORS.COM] Sent: Thursday, August 30, 2001 11:35 AM To: DB2-L@RYCI.COM Subject: edit a PS,OS/390
Is there a way to edit a large PS dataset? I have a PS which is 1400 tracks in size . When I try to open it in edit mode, it substitutes a Browse. [...]
10459 18 46_Sending emails from COBOL DB2 Batch jobs (MVS)15_Craig Nicholson27_Marion_C._Nicholson@HUD.GOV31_Thu, 30 Aug 2001 14:50:56 -0400465_us-ascii This is a little out of the realm of DB2 but if anyone can tell me how to go about triggering an email to be sent in the event of a batch job going down, I would appreciate it.
Thanks.
Craig Nicholson
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
10478 51 11_Re: DB2 UDB13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Thu, 30 Aug 2001 14:06:32 -0500619_iso-8859-1 Tony,
We have some 5.2 fp7 clients working with DB2 OS/390 v6.
Dave
-----Original Message----- From: Moore, Tony [mailto:TMoore@IKON.com] Sent: Thursday, August 30, 2001 11:44 AM To: DB2-L@RYCI.COM Subject: DB2 UDB
Yo Listers, I'm on the hunt for a version of DB2 Connect that will work with both DB2/MVS V4 and DB2 for OS390 V6. So far I have found that DB2 Connect V6 and V7.2 will not work with DB2/MVS V4. I dropped down to DB2 Connect V5.2. This release will connect successfully to DB2/MVS V4, but I'm having troubles getting it to connect to DB2 for OS390 V6, [...]
10530 49 26_Re: edit a PS,OS/390 - ...22_Venkatesh Mokshagundam21_Vmokshagun@CSEDGE.COM31_Thu, 30 Aug 2001 14:07:11 -0500364_- Oops, sorry, wrong answer.....
-----Original Message----- From: Venkatesh Mokshagundam Sent: Thursday, August 30, 2001 1:41 PM To: 'DB2-L@RYCI.COM' Subject: RE: edit a PS,OS/390
You should be able to edit it, even if its a full Volume dataset. Check if the LRECL is less than 10 Bytes. If it is, ISPF won't allow edit on that dataset. [...]
10580 41 20_Re: edit a PS,OS/39013_Bright, Randy20_Randy_Bright@BMC.COM31_Thu, 30 Aug 2001 14:28:20 -0500382_iso-8859-1 This is typically caused by not being able to load the entire dataset into virtual storage, which is required for ISPF EDIT. If you can increase the region size on the LOGONID you are using to do the edit, it might help. Otherwise, you can use SYNCSORT or IEBGENER to break the dataset into smaller "pieces", edit them individually, then put them all back together. [...]
10622 45 50_Re: Sending emails from COBOL DB2 Batch jobs (MVS)12_McKown, John22_JMckown@HEALTHAXIS.COM31_Thu, 30 Aug 2001 14:30:05 -0500378_- Try looking at XMITIP at http://www.geocities.com/lbdyck/tcpip.html and the entry for XMITIP. It might do what you want, but it looks like it is really designed to be run as a stand-alone job step. You might be able to integrate it into a COBOL program, but I couldn't.
---------------------------------------------------------------------- John McKown HealthAxis [...]
10668 62 77_Re: last archive log record disappeared after restart DB2 with access (maint)12_BOB JEANDRON21_BOB.JEANDRON@USDA.GOV31_Thu, 30 Aug 2001 14:27:00 -04001072_- We do the following: NEWLOG DSNAME=DB2DB2P.A0007678.DISK, Archive log Copy 1 on disk COPY1VOL=??????,CATALOG=YES, STARTRBA=006844E57000,ENDRBA=00684710AFFF,UNIT=3390 Archive log copy2 on tape and We always add copy2 NEWLOG DSNAME=DB2DB2P.A0007678.TAPE, COPY2VOL=??????,CATALOG=YES, STARTRBA=006844E57000,ENDRBA=00684710AFFF,UNIT=TAPEP DELETE DSNAME=DB2DB2P.LOGCOPY1.DS01 Delete active logs entries DELETE DSNAME=DB2DB2P.LOGCOPY1.DS02 " DELETE DSNAME=DB2DB2P.LOGCOPY1.DS03 " DELETE DSNAME=DB2DB2P.LOGCOPY2.DS01 " DELETE DSNAME=DB2DB2P.LOGCOPY2.DS02 " DELETE DSNAME=DB2DB2P.LOGCOPY2.DS03 " NEWLOG DSNAME=DB2DB2P.LOGCOPY1.DS01,COPY1 Add new active logs entries NEWLOG DSNAME=DB2DB2P.LOGCOPY1.DS02,COPY1 " NEWLOG DSNAME=DB2DB2P.LOGCOPY1.DS03,COPY1 " NEWLOG DSNAME=DB2DB2P.LOGCOPY2.DS01,COPY2 " NEWLOG DSNAME=DB2DB2P.LOGCOPY2.DS02,COPY2 " NEWLOG DSNAME=DB2DB2P.LOGCOPY2.DS03,COPY2 " CRESTART CREATE,ENDRBA=00684710B000 end RBA of archive log +1 /* We also had a similar problem some time ago, when the wrong BSDS was used. >>> DB2-L@RYCI.COM@inter2 08/30/01 01:20PM >>> [...]
10731 60 20_Re: edit a PS,OS/39016_Galambos, Robert29_Robert_Galambos@COMPUWARE.COM31_Thu, 30 Aug 2001 15:39:32 -0400697_iso-8859-1 From one of the vendor lurkers on this list ;-)
Just a small note. File-Aid MVS version 8.8 does NOT need to have any selection criteria to edit a very large file.
Robert Galambos Compuware Corp. Of Canada
1-800-348-8299 1-800-447-1662 (quebec) 1-905-803-8603 (fax)
******************************************************************** This message and any attachments are confidential to the ordinary user of the e-mail address to which it was addressed and may also be privileged. If you are not the addressee you may not copy, forward, disclose or use any part of the message or its attachments and if you have received this message in error, [...]
10792 62 20_Re: edit a PS,OS/39014_Kunjar Bhaduri28_Kunjar.Bhaduri@FIRSTDATA.COM31_Thu, 30 Aug 2001 15:40:00 -0400365_us-ascii I don't think ISPF will allow you to edit the file online. You will probably have to do it in batch. Either File-Aid, SyncSort or even a small Rexx or Easytrieve can do the job for you. Depends on what you want to edit in the file. --- Kunjar
"K.Balaji" @RYCI.COM> on 08/30/2001 02:57:58 PM [...]
10855 238 26_Re: edit a PS,OS/390 - ...17_Petluri, Srinivas36_Srinivas.Petluri@CENDANTMOBILITY.COM31_Thu, 30 Aug 2001 15:42:07 -0400599_- Do you have File-aid?
Thanks & Regards, Sreeni
Srinivasa Rao Petluri, Cendant Mobility, 40, Apple ridge Road, Danbury, CT
Ph : (203)-205-8838 (W) (203)-791-0704 (R) (203)-512-9033 (M)
-----Original Message----- From: Venkatesh Mokshagundam [SMTP:Vmokshagun@CSEDGE.COM] Sent: Thursday, August 30, 2001 3:07 PM To: DB2-L@RYCI.COM Subject: Re: edit a PS,OS/390 - ...
Oops, sorry, wrong answer.....
-----Original Message----- From: Venkatesh Mokshagundam Sent: Thursday, August 30, 2001 1:41 PM To: 'DB2-L@RYCI.COM' Subject: RE: edit a PS,OS/390 [...]
11094 73 18_Re: SQL -171 error13_Olson, Carlos14_COlson@QRS.COM31_Thu, 30 Aug 2001 13:12:36 -0700765_iso-8859-1 The problem was resolved by adding the CHAR function as follows:
SELECT UPC_NBR ,SIZE_DESC ,SIZE_CODE ,SLTN_CD ,PRD_ID FROM AQA1.NMU_NM_MULT_UPC1 WHERE LCASE(REPLACE(CHAR(STRIP(SIZE_DESC)), ' ','')) = LCASE(REPLACE(CHAR(STRIP(:A)), ' ',''));
Carlos Olson Database Administrator QRS Corporation
> -----Original Message----- > From: Olson, Carlos > Sent: Thursday, August 30, 2001 10:04 AM > To: 'DB2-L@RYCI.COM' > Subject: SQL -171 error > > Hi everyone, > A developer is trying to imbed the following SQL statement into a program: > SELECT > UPC_NBR > ,SIZE_DESC > ,SIZE_CODE > ,SLTN_CD > ,PRD_ID > FROM AQA1.NMU_NM_MULT_UPC1 > WHERE > LCASE((REPLACE(STRIP(SIZE_DESC), ' ',''))) > = LCASE((REPLACE(STRIP(:A), ' ',''))); [...]
11168 164 45_Re: Trigger Functionality in Version 7 OS/39012_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Thu, 30 Aug 2001 15:37:52 -0500474_us-ascii
Hello,
Well, I waited as long as I could before jumping in.
I think you may have found the "black hole" in DB2 for OS/390.
The pat answer is: use a UDF. You're only working with one column and that works now, in V6.
In my case, (V6) I want to assign several columns using the returning values from an SP call. The sub-select is my second choice. The UDF is contingent on WLM, which we should be running, but aren't. [...]
11333 77 78_Re: last archive log record disappeared after restart DB2 with ac cess (maint)10_Grace Chen22_gchen@CUCENTRAL-AB.COM31_Thu, 30 Aug 2001 15:01:24 -0600393_iso-8859-1 Thank you Bob and Michael. Finally I found that I gave wrong RBA on the NEWLOG statement. After I fixed it, everything went ok.
Grace
-----Original Message----- From: Murley, Michael [mailto:Michael_Murley@BMC.COM] Sent: Thursday, August 30, 2001 12:47 PM To: DB2-L@RYCI.COM Subject: Re: last archive log record disappeared after restart DB2 with ac cess (maint) [...]
11411 65 50_Re: Sending emails from COBOL DB2 Batch jobs (MVS)13_Renjith Davis30_Renjith.Davis@MAIL.STATE.AR.US31_Thu, 30 Aug 2001 16:40:25 -0500760_iso-8859-1 There was a discussion about this sometime back. Have look at the links below:
http://jupiter.ryci.com/cgi/wa.exe?S2=db2-l&D=0&L=DB2-L&q=Triggering+Emails+ from+JCL&s=&f=&a=&b=
http://jupiter.ryci.com/cgi/wa.exe?A2=ind0106A&L=DB2-L&P=R23573
-----Original Message----- From: McKown, John [mailto:JMckown@HEALTHAXIS.COM] Sent: Thursday, August 30, 2001 2:30 PM To: DB2-L@RYCI.COM Subject: Re: Sending emails from COBOL DB2 Batch jobs (MVS)
Try looking at XMITIP at http://www.geocities.com/lbdyck/tcpip.html and the entry for XMITIP. It might do what you want, but it looks like it is really designed to be run as a stand-alone job step. You might be able to integrate it into a COBOL program, but I couldn't. [...]
11477 17 46_How to find out the package name from Trigger.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Thu, 30 Aug 2001 18:41:46 -0400497_- Is there any way can we find out the db2 package which caused the trigger to get fired? I want to see which program overwriting some field in a table. Let me know if there is any other way. We have lot of program touching this table.
thanks..sibi
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
11495 36 38_Re: UPDATE OF PARTITIONING KEY COLUMNS24_Mahadevan Krishnamoorthy45_mahadevan.krishnamoorthy@FARMERSINSURANCE.COM31_Thu, 30 Aug 2001 15:44:29 -0700355_us-ascii Hi List,
We have a major work coming up for conversion from IMS and DB2 database in DB2 Version 6 in OS/390 to Oracle in Unix.
I have a few questions on the approach. Our clients decided not to use any third party tools.
1) The mainframe data will be in ebcdic and oracle will be in ascii. How can we do this conversion [...]
11532 61 50_Re: How to find out the package name from Trigger.12_Steve Grimes30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Thu, 30 Aug 2001 17:51:31 -0500456_us-ascii I would suggest looking at the Special Registers. I'm using Current SQLID and USER with much success in triggers. There are also ones called CURRENT PACKAGESET, CURRENT SERVER, CURRENT PATH, etc. Maybe on of these would meet your needs?
Stg
"Philip, Sibimon" on 08/30/2001 05:41:46 PM
Please respond to DB2 Data Base Discussion List [...]
11594 42 38_Re: UPDATE OF PARTITIONING KEY COLUMNS11_Jon Nolting33_Jonathan_Nolting@FTSI.FUJITSU.COM31_Thu, 30 Aug 2001 16:01:24 -0700334_us-ascii In looking at your email address, I see the name of my insurance company. That brings to mind the nearly 10% across the board rate increase I just received. When I asked why, I didn't get a good answer. Maybe now I see the true reason why - ORACLE and UNIX. What a combination! Which reminds me to give Geico a call.... [...]
11637 13 38_Re: UPDATE OF PARTITIONING KEY COLUMNS25_Edward(Ed) J. Finnell,III23_efinnell@SEEBECK.UA.EDU31_Thu, 30 Aug 2001 18:27:43 -0500288_iso-8859-1 Or is it the squirrel commercial???
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
11651 23 17_Row Level locking12_Dil Pratheek30_Dil.Pratheek@MORGANSTANLEY.COM31_Thu, 30 Aug 2001 19:26:02 -0400488_us-ascii Hi everyone -
What will happen if for a tablespace LOCKRULE is set to ROW and LOCKMAX as SYSTEM. Do we have to specify a numeric value for LOCKMAX ? Will keeping it SYSTEM give rise to heavy load to the IRLM?
TIA
Dil
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
11675 79 50_Re: How to find out the package name from Trigger.15_Philip, Sibimon20_SPhilip@CSXLINES.COM31_Thu, 30 Aug 2001 19:37:16 -0400384_- The problem is if CICS transaction is causing the trigger then USER and SQLID contains the auth-id of the RCT entry which is RACF group for us.
Thanks..sibi
-----Original Message----- From: Steve Grimes [mailto:Steve_Grimes@AISMAIL.WUSTL.EDU] Sent: Thursday, August 30, 2001 05:52 PM To: DB2-L@RYCI.COM Subject: Re: How to find out the package name from Trigger. [...]
11755 96 20_Re: edit a PS,OS/39012_Amit Agarwal33_amit_agarwal@STANDARDANDPOORS.COM31_Thu, 30 Aug 2001 15:59:42 -0400433_us-ascii I have solved the problem ftping it to UNIX and changing it there.(that was the final destination of the file after the edit). I was trying to use the following change command... c all ''' '' Can someone post the needed REXX.....Thanks
Kunjar Bhaduri on 08/30/2001 03:40:00 PM
Please respond to DB2 Data Base Discussion List [...]
11852 156 52_Re: How to store special character in the data base.14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Fri, 31 Aug 2001 11:18:45 +1000498_iso-8859-1 Hi Praveen,
Using your empdemo table, you can insert the CR/LF character using the concatenate || command:
db2 => describe table empdemo
Column Type Type name schema name Length Scale Nulls ------------------ ----------- ------------------ -------- -------- -------- A SYSIBM INTEGER 4 0 Yes B SYSIBM CHARACTER 20 0 Yes
2 record(s) selected.
db2 => insert into empdemo values (1,'aa'||x'13'||'bb') DB20000I The SQL command completed successfully. [...]
12009 23 18_DB2 V7 Support ???8_dba dbat17_dbathai@YAHOO.COM31_Thu, 30 Aug 2001 18:45:23 -0700585_us-ascii Dear all, Anybody know DB2 V7(OS/390) suppourt what version of Websphere,QMF and Net.data.Where can I find from internet?
Thank you for any kindly help Johnny
__________________________________________________ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
12033 64 30_Re: How to fire nested QUERY ?10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 31 Aug 2001 12:14:20 +1000388_us-ascii Praveen,
Try
Insert into empdemo Select 6, 'aa', dept from empdemo where empid=8 ;
The VALUES clause is not needed when u code a subselect. Just omit it.
Also, I am assuming that table empdemo has got only 3 columns. If not, u have to code the reqd columns in the insert statement. Refer to the help on INSERT in the SQL Reference for more info. [...]
12098 70 29_Re: errror regarding trigger.10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 31 Aug 2001 12:17:15 +1000327_us-ascii Praveen,
The VALUES clause is not needed for a subselect.
Apart from that, how many columns does table D have? And how many rows does table A have? If u want to code it as a subselect, try
INSERT INTO D select a, N.b1, N.b2, N.b3 from A
I assume that 'a' is a columnname in table 'A'. [...]
12169 72 74_Re: Regarding alternative terminating character+How to fire nested QUERY ?18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Fri, 31 Aug 2001 09:42:53 +0530658_- Praveen,
If you are using SPUFI then it has the option of changing the default ';' query delimiter by other char like ','.
regards, Rajendra.
>From: praveen_kj >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Regarding alternative terminating character+How to fire nested > QUERY ? >Date: Thu, 30 Aug 2001 20:33:39 +0530 > >Hi All, > >1. Can somebody tell me regarding alternative terminating character. >(apart from ; )on SQL prompt. > > > >2. > > >Can somebody tell me how to fire the nested query. > >For ex:- > > >insert into empdemo values(6, 'aa',(select [...]
12242 32 47_Re: Regarding alternative terminating character12_Cianci, Nick28_Nick.Cianci@TEAM.TELSTRA.COM31_Fri, 31 Aug 2001 15:22:33 +1100530_- If you are using DSNTEP2 (the next best program since IEFBR14) , you can specify it as a parm for the whole job or, as in my preference use the comment command
--#SET TERMINATOR :
Cheers, Nick Cianci > -----Original Message----- > From: Rajendra Deshpande [SMTP:rajendra_deshpande@HOTMAIL.COM] > Subject: Re: Regarding alternative terminating character+How to fire > nested QUERY ? > > Praveen, If you are using SPUFI then it has the option of changing the default ';' query delimiter by other char like ','. [...]
12275 204 52_Re: How to store special character in the data base.10_praveen_kj19_praveen_kj@INFY.COM31_Fri, 31 Aug 2001 09:59:00 +0530484_iso-8859-1 Hi Greg, I want exactly like this
A B ----------- -------------------- 1 aa bb 1 aa bb
Please tell how to do this. What is CR/LF? How do we split the text at CR/LF?
TIA
Regards Praveen
-----Original Message----- From: Palgrave, Greg [mailto:greg.palgrave@UNISYSWEST.COM.AU] Sent: Friday, August 31, 2001 6:49 AM To: DB2-L@RYCI.COM Subject: Re: How to store special character in the data base. [...]
12480 53 12_Re: Quickie.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Fri, 31 Aug 2001 10:25:23 +0530428_- Mark,
Among other things SDSF-DA displays CPU, CPU%, ECPU, ECPU%. All these are CPU related info fields. CPU% displays the percent of time CPU is busy. I also found that CPU = ECPU and CPU% = ECPU though not sure why they have the 'E' prefixed fields. On a project that I worked earlier we would ask the systems guys to allocate more cpu time to speed up slow running jobs. The CPU value would get bumped up then. [...]
12534 53 12_Re: Quickie.18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Fri, 31 Aug 2001 10:27:04 +0530439_- Mark,
Among other things SDSF-DA displays CPU, CPU%, ECPU, ECPU%. All these are CPU related info fields. CPU% displays the percent of time CPU is busy. I also found that CPU-TIME = ECPU-TIME and CPU% = ECPU% though not sure why they have the 'E' prefixed fields. On a project that I worked earlier we would ask the systems guys to allocate more cpu time to speed up slow running jobs. The CPU value would get bumped up then. [...]
12588 49 50_Re: Sending emails from COBOL DB2 Batch jobs (MVS)18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Fri, 31 Aug 2001 10:46:48 +0530607_- Hi Craig,
See if your installation has a TPX mailing prog. Your systems people need to establish some sort of connection between the mailing prog and your email messaging system(lotus notes, ms-exchange, etc). We were in a similar situation at our shop some time back when we were required to send email messages to important heads in our company if a certain exigency cropped up. We had a prog called 'mailaway' that would send messages to concerned m/f ids and thru a certain bridge/interface (created by systems guys)these messages would landup inside Lotus notes in-boxes of those people. [...]
12638 48 21_Re: udb Rexx on WinNt18_Rajendra Deshpande30_rajendra_deshpande@HOTMAIL.COM31_Fri, 31 Aug 2001 10:56:54 +0530629_- Hi Stephen,
Your mail perked me up. I have already downloaded the zip file for DB2 UDB personal edition and facing a problem while unzipping it. The message says 'cannot open file'. I repeated the download but to no avail.I have a NT workstation. Am i missing something here.
thanks, Rajendra.
>From: "Neff, Stephen R" >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: udb Rexx on WinNt >Date: Thu, 30 Aug 2001 11:31:53 -0400 > >Hello, > > I have installed DB2 UDB personnel and EE DB2 UDB on a win/nt >machine. Would anyone be able to [...]
12687 43 21_Re: Row Level locking18_Pudukotai, Nagaraj35_Nagaraj.Pudukotai@BLR.HPSGLOBAL.COM31_Fri, 31 Aug 2001 11:26:43 +0500593_- It means it would use the value specified in the ZPARM NUMLKTS for LOCKMAX.
> -----Original Message----- > From: Dil Pratheek [SMTP:Dil.Pratheek@MORGANSTANLEY.COM] > Sent: Friday, August 31, 2001 4:56 AM > To: DB2-L@RYCI.COM > Subject: Row Level locking > > Hi everyone - > > What will happen if for a tablespace LOCKRULE is set to > ROW and LOCKMAX as SYSTEM. Do we have to specify > a numeric value for LOCKMAX ? Will keeping it SYSTEM give rise > to heavy load to the IRLM? > > TIA > > Dil > > ================================================ > To change your [...]
12731 16 53_Charles D Wolf/Headquarters/USX is out of the office.14_Charles D Wolf14_CDWolf@USS.COM31_Fri, 31 Aug 2001 02:21:53 -0400464_us-ascii I will be out of the office starting 08/31/2001 and will not return until 09/04/2001.
I will respond to your message when I return. If you are having a question/problem with DB2, please contact Gary Norman.
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
12748 40 38_Re: UPDATE OF PARTITIONING KEY COLUMNS10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 31 Aug 2001 09:43:04 +0200326_us-ascii I think you'd post your questions in an ORACLE newgroup, where they deal with migration from S390 to ORACLE and the last version of QUAKE and TOMB RAIDERS (to find Lara's Croft nude patch).
1) Anyway if you have Open edition is simple to convert from EBCDIC to ASCII on mainframe and then FTPing on UNIX. [...]
12789 79 38_Re: UPDATE OF PARTITIONING KEY COLUMNS14_Palgrave, Greg31_greg.palgrave@UNISYSWEST.COM.AU31_Fri, 31 Aug 2001 18:41:44 +1000318_iso-8859-1 Hi Maha,
For point (2), I hope you don't have any DB2 TIMESTAMP columns that need to be accurate, as Oracle doesn't have a standard equivalent (not in 8i that I am aware of...). The big O(rifice) stores dates or times as date/time combo's - to the awesome accuracy of...
wait for it..... [...]
12869 74 42_Re: MS Query to DB2 OS/390 via DB2 Connect10_teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 31 Aug 2001 19:02:51 +1000438_us-ascii Hi Tina,
I access Query thru Excel97's GetExternalData option, which invokes Query. The version dsplayed is v8.00, if u r interested. I tried it on WinNT and Win2000.
Try running a trace (omegamon).
When I change the colname, I find that the newcolname doesnt get carried across to the mainframe. Could be something to do with the ODBC Driver I use. (IBM's). I think we have fixpack14 for Connect v5.2. [...]
12944 53 15_Galambos Robert14_Zubonyai Tibor30_tibor.zubonyai@ISC-HUNGARIA.HU31_Fri, 31 Aug 2001 11:08:41 +0200194_iso-8859-1 Hi Robert!
I',m Tibor Zubonyai. Are you hungarian? I think youre name looks like typical hungarian name, doesn't it? I work on db2 for OS/390. Please answer to me!
Tibor
12998 60 40_UDB EEE 7.1: TIMESTAMP out of date range20_Napolitano, Fabrizio22_fnapolitano@INSIRIO.IT31_Fri, 31 Aug 2001 12:40:29 +0200378_- Hi there,
Little Devil is striking back (with no offence to anyone or anything).
Joking apart I'm struggling with a strange situation working with TIMESTAMP.
This is my environment: DB2 UDB EEE v7.1 fp3 on AIX 4.3.3 (one DB on a SP 4 nodes, and one on a SP 2 nodes) Two DBs have exactly the same design. Now this time my troubles are with TIMESTAMP. [...]
13059 39 31_-725 on MS-Access to DB2 OS/39013_Philip Nelson21_teamdba@ATTGLOBAL.NET31_Fri, 31 Aug 2001 12:20:34 +0100369_US-ASCII I'm at the end of my tether with this one !!!
We have an MS-Access application accessing DB2 for OS/390 through a DB2 Connect gateway.
We added a few fields to a table, "relinked the tables" in MS-Access (this is what the developer said - I'm no knowledge of Access).
We now get an SQLCODE = -725 with tokens "CURRENT SQLID P3EDBGH". [...]
13099 60 21_Re: Row Level locking0_19_mike.holmans@BT.COM31_Fri, 31 Aug 2001 12:41:45 +0100503_iso-8859-1 LOCKMAX determines when DB2 will escalate the item locks to a tablespace lock. LOCKMAX SYSTEM will use the default for the subsystem as specified by the ZPARM NUMLKTS.
Row level locking will generally result in a higher load on the IRLM than page locking will. A row lock is the same amount of work as a page lock, and you shouldn't be doing it unless you are having concurrency issues with page locks, so you will be taking as many locks per page as rows you are using per page. [...]
13160 237 33_Speculating - Write or wrong ????14_William Gannon33_wgannon@EMAIL.PALMBEACH.K12.FL.US31_Fri, 31 Aug 2001 07:48:23 -0400446_us-ascii Hi All ...
In a thread - Re: DB2 conditional REORG parameters: what do they really mean
"Grainger, Phil" wrote:
> ..... > INDREFs refer to rows that CANNOT be put back in their original > location after an insert as they are longer than they were (variable > length rows obviously). DB2 then puts a pointer in the old row > location which points to the new row location (rows are > INDirectlyREFerenced) > .... [...]
13398 50 21_Re: Row Level locking12_Dil Pratheek30_Dil.Pratheek@MORGANSTANLEY.COM31_Fri, 31 Aug 2001 07:51:15 -0400554_us-ascii Thanks..group..!
"Pudukotai, Nagaraj" wrote:
> It means it would use the value specified in the ZPARM NUMLKTS for LOCKMAX. > > > -----Original Message----- > > From: Dil Pratheek [SMTP:Dil.Pratheek@MORGANSTANLEY.COM] > > Sent: Friday, August 31, 2001 4:56 AM > > To: DB2-L@RYCI.COM > > Subject: Row Level locking > > > > Hi everyone - > > > > What will happen if for a tablespace LOCKRULE is set to > > ROW and LOCKMAX as SYSTEM. Do we have to specify > > a numeric value for LOCKMAX ? Will keeping it SYSTEM give rise > > to [...]
13449 91 20_Re: edit a PS,OS/39014_William Gannon33_wgannon@EMAIL.PALMBEACH.K12.FL.US31_Fri, 31 Aug 2001 08:00:52 -0400578_us-ascii Hi Amit,
Instead of using the character you want to change as the delimiter use another character - say " ex - C all "'" "" = will remove all single quotes from the edited data.
HTH Bill G.
Amit Agarwal wrote:
> I have solved the problem ftping it to UNIX and changing it there.(that was the > final destination of the file after the edit). > I was trying to use the following change command... > c all ''' '' > Can someone post the needed REXX.....Thanks > > Kunjar Bhaduri on 08/30/2001 03:40:00 PM > > [...]
13541 44 10_SQL Tuning20_Subramaniam Baskaran27_subramaniam.baskaran@DB.COM31_Fri, 31 Aug 2001 17:51:36 +0530339_us-ascii Hello Listusers
We have a query which has joins on 4 tables. The joins are similar to the following predicate
B.COl1 = A.COL2 AND C. COL3 = B.COL1 AND D. COL5 = C.COL4
Now, we are encountering severe performance problem on this particular query. On running EXPLAIN function, We got the following results [...]
13586 64 50_Re: Sending emails from COBOL DB2 Batch jobs (MVS)11_Jim Medlock24_jmedlock@ALLIANCESYS.COM31_Fri, 31 Aug 2001 07:39:23 -0500
13651 66 21_Re: udb Rexx on WinNt15_Neff, Stephen R16_NeffSR@STATE.GOV31_Fri, 31 Aug 2001 08:35:58 -0400443_iso-8859-1 Sorry I did not have that problem. The personal edition 7.1 I got was from Amazon.com. I bought the CD's for about $39.00 rather than try and download. Recently I did download the UDB EE version which I unzipped and installed.
Steve
-----Original Message----- From: Rajendra Deshpande [mailto:rajendra_deshpande@HOTMAIL.COM] Sent: Friday, August 31, 2001 1:36 AM To: DB2-L@RYCI.COM Subject: Re: udb Rexx on WinNt [...]
13718 108 30_Re: SYSADM AUTHORITY - HELP!!!0_19_mike.holmans@BT.COM31_Fri, 31 Aug 2001 13:51:18 +0100623_iso-8859-1 I'm surprised.
We're a considerably larger shop than the ones under discussion here. I've been working as a DB2 DBA for 12 years, but I've only had SYSADM (and then only for development susbystems - on production, even the sysprogs have to raise a change request to be connected to the RACF group which has SYSADM privileges when they specifically need them) for the last 18 months while I've been taking on some subsystem level responsibilities as well as those pertaining to application-level functions. As long as I've had CREATEDBA and BINDADD, CREATE IN COLLECTION, SELECT on all catalog tables [...]
13827 16 23_DB2 V6 no rows returned12_Jeff Frazier26_Jeffrey_Frazier@WENDYS.COM31_Fri, 31 Aug 2001 08:54:23 -0400352_us-ascii Hello everyone, We migrated to Version 6 in production over the weekend. We ran into a problem where a select that returned rows in V5 didn't return any rows in V6. what we ended up doing was reorging the tablespace, running the query again and bingo rows returned correctly. Has anyone else hit this problem or had to do this? TIA, Jeff [...]
13844 74 21_Re: udb Rexx on WinNt15_Neff, Stephen R16_NeffSR@STATE.GOV31_Fri, 31 Aug 2001 08:52:03 -0400597_iso-8859-1 Thank You for your responses. Jeremy you mention that you were using OBJECT REXX. What version? There was a new release in March of this year. There is an earlier release still available on AMAZON.COM. The NEW release comes with are two prices for the product $75.00 interpreter and $250.00 development. Would you know what the differences are between the new release, the interpreter and the development and would the functionality need to access udb be different? Also may be this is not the appropriate list to ask this would anyone have any suggestion as to where else I might [...]
13919 36 35_difference in sql select and update10_Tom Abbott22_Thomas_Abbott@HESC.COM31_Fri, 31 Aug 2001 09:10:06 -0400426_us-ascii We have DB2 V5 on OS390. Anyone see a difference in the way subqueries are handled in UPDATE and SELECT statements? Here's an example:
UPDATE Table_A Set column_1 = 123456 Where Tab_id in (Select Tab_id from Table_B Where Column_2 = 55 And Column_3 = 66 And Column_4 = 77 )
SELECT * from Table_A Where Tab_id in (Select Tab_id from Table_B Where Column_2 = 55 And Column_3 = 66 And Column_4 = 77 ) [...]
13956 166 85_Re: Insert, delete, select,update access for a id for al l the t ables in a subsystem11_Sinha, Amit19_Sinha.Amit@MBCO.COM31_Fri, 31 Aug 2001 08:31:33 -0500573_iso-8859-1 Thanks Kurt will try next time it happens. Also thanks to all for all the suggestions.
-----Original Message----- From: Kurt Sahlberg [mailto:Kurt.Sahlberg@EXPERIAN.COM] Sent: Wednesday, August 29, 2001 9:06 AM To: DB2-L@RYCI.COM Subject: Re: Insert, delete, select,update access for a id for al l the t ables in a subsystem
Hi Amit, I have seen this when a user sets his / her TSO PROFILE PREFIX to something other than a group that has the permissions. A SET CURRENT SQLID= before the rebind will take care of the warnings. HTH Kurt [...]
14123 60 37_DDF INACTIVE THDS and RELEASE COMMITS12_Myron Miller22_myronwmiller@YAHOO.COM31_Fri, 31 Aug 2001 06:49:28 -0700457_us-ascii I've run into an interesting issue with DB2 Connect and V6 of OS/390 of DB2. With the packages bound as RELEASE(COMMIT) and Using the inactive thread support, if our Container Managed JAVA Beans don't issue an explicit COMMIT, the locks held by that thread are held by DB2 even after the task completes. I know that commits are being transferred to DB2 by the APP server, but DB2 doesn't seem to release the final locks at thread termination. [...]
14184 25 28_Re: Db2 V5 Column Encryption10_Ian Thorne17_ian.thorne@DB.COM31_Fri, 31 Aug 2001 08:39:55 -0500330_- Manoj
Not 100% sure, but I think what you're looking for is a FIELDPROC which can be added to a table when it's created or through an alter statment. A Fieldproc is a user written exit routine and can encode the data stored in a column. Inorder to retrieve this data it has to be decoded back to the original value. [...]
14210 19 35_Migrating UDB 6.1 data to OS390 DB210_Anne Keefe18_AKeefe@BCBSCNY.ORG31_Fri, 31 Aug 2001 09:40:08 -0400397_us-ascii One of our web development teams has been developing using UDB 6.1 on NT at their remote location. Final testing and deployment will be on the OS390 platform using websphere and DB2 v6. Is there an easy way to import this data to the OS390 environment? We plan to export the table data, transfer to OS390 and use the load utility to populate. Does anyone know of an easier solution? [...]
14230 59 40_Fw: Automatic checks for a sane database12_Bill Littman24_blittman@TOMOTHERAPY.COM31_Fri, 31 Aug 2001 08:56:12 -0500400_iso-8859-1 I sent out the following mail last week and received no responses.
Does this mean: - my questions were nonsensical? - my questions were very difficult? - I should be paying a consultant to answer these questions?
or is it something else?
Any and all responses will be appreciated.
Thank you.
-Bill Littman Senior Software Engineer TomoTherapy, Inc. [...]
14290 104 14_Re: SQL Tuning0_19_mike.holmans@BT.COM31_Fri, 31 Aug 2001 14:59:09 +0100285_iso-8859-1 Hi Subramaniam,
What you need is a predicate which will allow you to use a matching index scan on table A. Have you got a predicate for table A which does not rely on another table - WHERE A.COLx = :hostvar or WHERE A.COly < CURRENT DATE or something like that? [...]
14395 18 24_DB2 SQL Programming Tool9_Ali Akbar25_ali.akbar@CRESSOFT.COM.PK31_Fri, 31 Aug 2001 19:02:43 +0500493_us-ascii Hi, I need some SQL prgramming Tool for DB2 which gives sort of Visual effect just as TOAD is an IDE for Oracle. I have downloaded SQL-Programmer but it doesn,t work. If anyone could tell me of any such Tool for DB2 SQL development
Ali
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
14414 176 81_Reminder: DB2 and Business Intelligence Technical Conference - O ctober 1-5, 200113_Morrill, John12_JohnM@VP.NET31_Fri, 31 Aug 2001 08:36:57 -0600445_- Dear DB2 Interested Parties,
The annual IBM Global Services division's DB2 and Business Intelligence Technical Conference is only weeks away, October 1-5 in Orlando. Below is a bulletin with information about the event and how to register.
Regards, Mike + + + + + + +
Take advantage of what IBM DB2 Universal Database can do for you! Attend the DB2 and Business Intelligence Technical Conference - October 1-5, 2001. [...]
14591 39 28_Re: DB2 SQL Programming Tool15_Grantham, Karen25_Karen.Grantham@COMPAQ.COM31_Fri, 31 Aug 2001 09:42:50 -0500565_iso-8859-1 I've been using UltraEdit32 professional text/hex editor from www.idmcomp.com or www.ultraedit.com.
Karen Grantham DBA
-----Original Message----- From: Ali Akbar [mailto:ali.akbar@CRESSOFT.COM.PK] Sent: Friday, August 31, 2001 9:03 AM To: DB2-L@RYCI.COM Subject: DB2 SQL Programming Tool
Hi, I need some SQL prgramming Tool for DB2 which gives sort of Visual effect just as TOAD is an IDE for Oracle. I have downloaded SQL-Programmer but it doesn,t work. If anyone could tell me of any such Tool for DB2 SQL development [...]
14631 37 39_Re: Migrating UDB 6.1 data to OS390 DB214_Manas Dasgupta25_manas.dasgupta@PFSFHQ.COM31_Fri, 31 Aug 2001 10:46:52 -0400623_us-ascii If the volume of data is not much , I'd suggest using IMPORT over DB2 Connect.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Anne Keefe Sent: Friday, August 31, 2001 9:40 AM To: DB2-L@RYCI.COM Subject: Migrating UDB 6.1 data to OS390 DB2
One of our web development teams has been developing using UDB 6.1 on NT at their remote location. Final testing and deployment will be on the OS390 platform using websphere and DB2 v6. Is there an easy way to import this data to the OS390 environment? We plan to export the table data, transfer [...]
14669 46 28_Re: DB2 SQL Programming Tool12_Cook, Nicole19_Nicole_Cook@BMC.COM31_Fri, 31 Aug 2001 09:48:27 -0500535_iso-8859-1 For DB2 on OS/390? Check out BMC's SQL-Explorer for DB2! http://www.bmc.com/products/proddocview.cfm?id=1805&r=Americas&l=en.
Please let me know if you have any questions I can help answer.
Nicole
Nicole Cook DB2 UDB Platform Specialist Product Management bmcsoftware Austin, Texas 512.340.6817 800.841.2031 ncook@bmc.com
-----Original Message----- From: Ali Akbar [mailto:ali.akbar@CRESSOFT.COM.PK] Sent: Friday, August 31, 2001 9:03 AM To: DB2-L@RYCI.COM Subject: DB2 SQL Programming Tool [...]
14716 78 44_Re: Fw: Automatic checks for a sane database14_Bruce Bernhart25_bbernhart@NEWLOGIXINC.COM31_Fri, 31 Aug 2001 09:51:28 -0500613_iso-8859-1 x ----- Original Message ----- From: "Bill Littman" Newsgroups: bit.listserv.db2-l To: Sent: Friday, August 31, 2001 8:56 AM Subject: Fw: Automatic checks for a sane database
> I sent out the following mail last week and received no responses. > > Does this mean: > - my questions were nonsensical? > - my questions were very difficult? > - I should be paying a consultant to answer these questions? > > or is it something else? > > Any and all responses will be appreciated. > > Thank you. > > -Bill Littman > Senior Software Engineer > [...]
14795 49 28_Re: Db2 V5 Column Encryption11_Manoj Veedu18_mv_db2@HOTMAIL.COM31_Fri, 31 Aug 2001 08:56:02 -0600473_- Ian, Thnks for your reply. The real problem is that here our application programming group is NOT willing to change their code and Auditors want this column encrypted from outside world including DBAs.If we use FIELDPROCs , QMF/SPUFI can still see the data. We tried creating VIEWS, but still DBAs will be able to access the table. Now what we are doing is compressing the tablespace so the data will be encrypted. But I know thats NOT the solution for our problem. [...]
14845 135 30_Re: SYSADM AUTHORITY - HELP!!!11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Fri, 31 Aug 2001 09:08:34 -0600529_iso-8859-1 Okay, I finally have to reply. I've followed this with some interest as I'm always curious about other shops and how they do things. I'm a relative new-timer as a DBA, only going on 7 years and have only worked in 2 shops. First one was so small I WAS the technical group and the DBA, the 2nd is a Fortune 50 company with 20+ DBA's. In each instance, there was/has been no questions about the DBA's having SYSADM so I've had SYSADM ever since I became a DBA. The first 6 months was only for the test and training [...]
14981 71 28_Re: Db2 V5 Column Encryption11_Robert Jans26_robert_jans@ALBERTSONS.COM31_Fri, 31 Aug 2001 09:14:55 -0600467_- Sounds like your auditor's requirements mean the encryption needs to be done in the application and only the result stored in the column regardless of what the application people say..............
Robert Jans Albertson's Inc
-----Original Message----- From: Manoj Veedu [mailto:mv_db2@HOTMAIL.COM] Sent: Friday, August 31, 2001 8:56 AM To: DB2-L@RYCI.COM Subject: Re: Db2 V5 Column Encryption [...]
15053 101 44_Re: Fw: Automatic checks for a sane database13_Villa Horacio17_hvilla@TTI.COM.AR31_Fri, 31 Aug 2001 12:08:31 -0300471_iso-8859-1 Although Bruce's answer is quite clear, I would like to add one or two things. If DB2 has crashed or your tablespaces/indexes/tables are in an inconsistent state (constraints violations) you will get an sqlcode saying so and will not have access to your data. If you mean any other kind of inconsistent state in your data I think there isn't any automatic check for that. So your best way is to check sqlcodes in your applications. Regards, Horacio Villa [...]
15155 39 28_Re: DB2 SQL Programming Tool14_subrata mondal25_subratamondal@HOTMAIL.COM31_Fri, 31 Aug 2001 15:18:37 +0000
15195 188 30_Re: SYSADM AUTHORITY - HELP!!!0_29_sflindsey@HIGHLIGHTS-CORP.COM31_Fri, 31 Aug 2001 11:21:16 -0400515_us-ascii I have also had SYSADM authority since day 1 of becoming a DBA. We are a very small shop and only have one subsystem here. I am pretty much the DBA other than my boss who does small things when I am unavailable and an another person whos is a part time DBA. She's been instructed of the power of SYSADM, what she can do with it and to treat it with kid gloves. SYSADM has come in very useful so many times especially since we outsourced our hardware and system software. I do not want to have to wait [...]
15384 113 44_Re: Fw: Automatic checks for a sane database0_19_mike.holmans@BT.COM31_Fri, 31 Aug 2001 16:24:51 +0100465_iso-8859-1 Hi Bill,
Your questions are to some extent difficult, because it's not exactly obvious what they mean.
The most obvious sort of check to do with DB2 is ask it something. Send it some SQL or a CONNECT request and see what happens.
If DB2 does not respond, then either it has crashed or it has been stopped for some reason. The db2diag.log file will contain the answer to whether or not it closed sensibly or fell over in a heap. [...]
15498 89 11_Re: DB2 UDB11_Moore, Tony15_TMoore@IKON.COM31_Fri, 31 Aug 2001 11:32:50 -0400415_iso-8859-1 Thanks Dave, for the info.
I can only assume at this point that the V5.2 download from the IBM site is missing (at least) the ddcsa.dll file that 5.2 is trying to execute to connect to OS/390. I did a search and could not find the file on my PC.
Does anyone know if this software can be downloaded from any other websites? I'm doing some searches now, but haven't had any luck so far. [...]
15588 118 44_Re: Fw: Automatic checks for a sane database14_Bruce Bernhart25_bbernhart@NEWLOGIXINC.COM31_Fri, 31 Aug 2001 10:30:06 -0500415_iso-8859-1 Bill,
I just joined the list, I can give you some feedback. Since you are a programmer, rather than a DBA, I presume you are looking for some SQL statements you can run.
To see if DB2 has crashed: SELECT COUNT(*) FROM (pick your table) If DB2 has crashed, you will get a negative SQL code, like -905 (unavailable resource). You could use this to check the availability of any table... [...]
15707 123 34_Wepshere on AIX, WAS DB2 on OS/39014_Philip Gunning24_philip.gunning@QUEST.COM31_Fri, 31 Aug 2001 08:38:06 -0700455_- This a post from ADrian.
Mike,
[Please post this to the list - I have subscribed but waiting for conf]
I have not specifically used WebSphere Applicalion Server as a database on AIX, but I do have a lot of experience with DB2 7.1/7.2 EEE running on AIX, with failover using HACMP. We were using RS/6000-SP hardware purchased specifically for the application. High availability was a critical component of this project. [...]
15831 79 39_Re: difference in sql select and update13_Terry Purcell25_Terry_Purcell@YLASSOC.COM31_Fri, 31 Aug 2001 11:07:00 -0500395_us-ascii Tom,
If TAB_ID is the primary key of TABLE_B, then you should see the SELECT transformed into a join by the optimizer.
The UPDATE should appear in the plan table as a non-correlated subquery (as query block 2), and the outer query (block 1) showing a accesstype N with a matchcols 1. This is assuming you have the V5 APAR PQ23243 (indexable non-correlated subquery). [...]
15911 111 38_Re: UPDATE OF PARTITIONING KEY COLUMNS11_James Drewe20_James.Drewe@AEXP.COM31_Fri, 31 Aug 2001 09:39:09 -0700385_iso-8859-1 Mahadevan
My first response is, "you got to be joking". But I'll assume that you are not joking since Dilbert works at quite a few companies. (Those of you not familiar with the Dilbert cartoon, it provides management satire and loads of laughs to those of us in the US.)
There will probably be many suggestions coming from those more experienced than I. [...]
16023 149 29_Re: errror regarding trigger.10_praveen_kj19_praveen_kj@INFY.COM31_Fri, 31 Aug 2001 22:12:19 +0530595_iso-8859-1 Hi Panicker,
I want to fire this trigger from command line centre.
When i fire this -#SET TERMINATOR # CREATE TRIGGER A AFTER UPDATE ON TR8279.EMPDEMO FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC INSERT INTO EMPDEMO(EMPID,EMPNAME,DEPT) VALUES (1,W,1); END#
Again i am getting the same 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: SQL0104N An unexpected token " " was found following "". Expected tokens may include: "QUOTE". SQLSTATE=42601 [...]
16173 80 41_Re: DDF INACTIVE THDS and RELEASE COMMITS13_Shapiro, Dave26_Shapiro.Dave@PRINCIPAL.COM31_Fri, 31 Aug 2001 12:40:08 -0500341_iso-8859-1 Myron,
Looks like a swing from one extreme to the other. In V5 we wrestled with threads staying Active because they were holding locks, and could not go Inactive. We promoted "good programming practices" with Java developers and stressed the importance of Commits at the end of a transaction, even a read transaction. [...]
16254 16 50_Re: Sending emails from COBOL DB2 Batch jobs (MVS)12_Brian Picard26_brian_picard@ALTAVISTA.COM31_Fri, 31 Aug 2001 13:36:48 -0500435_- It is rather very easy if you have Unix system Services installed in full function mode. Turn on SMTP on USS and you can use the sendmail unix command to send emails. There is Batch interface BPXBATCH, which will interface your cobol / db2 Job to the Unix system services. Let the Cobol Bob write to a flat file. Transfer the flat file to a temp USS file. Then invoke sendmail as a shell script from within your JOB STEP. Brian [...]
16271 18 49_George Koegel/Rochester/IBM is out of the office.13_George Koegel18_gkoegel@US.IBM.COM31_Fri, 31 Aug 2001 14:29:35 -0400368_us-ascii I will be out of the office starting August 30, 2001 and will not return until September 1, 2001.
I will be out of the office Friday Morning 8/31/2001 returning Friday afternoon. I will respond to your message when I return. If this is urgent due to DBDC Subsystem Support Problems, contact 1800-446-4722 and request support via Calllist of pager [...]
16290 19 35_Calling UDB EE V6.1 from DB2 OS390.15_Tonmoy Dasgupta32_tonmoy.dasgupta@MAIL.STATE.AR.US31_Fri, 31 Aug 2001 13:48:11 -0500460_- At your site, do you have DB2-OS390 connecting to UDB-Unix? (We have the reverse, ie Unix to OS390, working). I could do with a few pointers.
TIA.
Tonmoy Dasgupta, DIS, State of Arkansas Ph:501-682-5109
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
16310 51 79_FW: Migrating to DB2 for z/OS V7? -- Special Pricing Announced fo r V5 Upgrades13_Morrill, John12_JohnM@VP.NET31_Fri, 31 Aug 2001 13:29:58 -0600710_- Dear DB2 Interested Parties,
To encourage migration over the next year to more current versions of DB2 for OS/390, IBM has announced special transition prices for customers who upgrade directly from DB2 for OS/390 Version 5 to DB2 UDB for z/OS and OS/390 Version 7.
See the Announcement Letter for full details, including terms and conditions: o United States (#201-230): http://www.ibmlink.ibm.com/usalets&parms=H_201-230 o Canada (#A01-1046) http://www.ibmlink.ibm.com/canalets&parms=H_A01-1046 o Europe/Middle East/Africa/Latin America (#ZP010438): http://www.ibmlink.ibm.com/emealets&parms=H_ERIFZP010438 o Asia Pacific (#AP01-1264): http://www.ibmlink.ibm.com/aplets&parms=H_AP011264 [...]
16362 16 21_jcl to start database12_Mathur, Anil29_AMathur@HARLEYSVILLEGROUP.COM31_Fri, 31 Aug 2001 15:49:22 -0400472_- Hi List - I am not sure how to set up IKJEFT01 jcl where I can set my current sqlid to a DB2 racf group and then start a database. Does anyone have any example ? Thanks for your help. We are on DB2 7.1 os/390.
Anil mathur
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
16379 202 23_SQL OUTER JOIN Question14_Johnson, Daryl28_Daryl.Johnson@PACIFICORP.COM31_Fri, 31 Aug 2001 12:53:15 -0700484_iso-8859-1 We are on DB2 5.1. the following SQL is not returning 2 rows that are in file A but not in file B, what am I not seeing?
SELECT A.FILED1 ,A.FILED2 ,B.FILED3 FROM file A FULL OUTER JOIN file B ON A.FILED1 = B.FILED1 AND A.FILED2 = B.FILED2 WHERE A.field4 = 'APPHST' AND B.field5 = 'F' AND B.field6 = '' AND B.field7 = (SELECT MAX(C.field7) FROM fileb C WHERE B.FILED1 = C.FILED1 AND B.FILED2 = C.FILED2 AND C.field5 = 'F') ORDER BY A.FILED1 ,A.FILED2 ,B.FILED3; [...]
16582 62 25_Re: jcl to start database15_Mohammed Nayeem25_Mohammed_Nayeem@MOMED.COM31_Fri, 31 Aug 2001 14:57:59 -0500674_us-ascii //STARTRW EXEC PGM=IKJEFT01, // DYNAMNBR=30,COND=(4,LT) //********************************************************** //STEPLIB DD DSN=&DSNLOAD,DISP=SHR // DD DSN=&SDSNEXT,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DB2E) -START DATABASE(db1) SPACENAM(ts1) ACCESS(FORCE) END
Thanks & Regards M.A.Nayeem IBM Certified Solutions Expert - DB2 UDB Database Administration
"Mathur, Anil" cc: Sent by: DB2 Data Subject: jcl to start database Base Discussion List [...]
16645 74 25_Re: jcl to start database12_Mathur, Anil29_AMathur@HARLEYSVILLEGROUP.COM31_Fri, 31 Aug 2001 16:20:06 -0400704_- > -----Original Message----- > From: Mohammed Nayeem [SMTP:Mohammed_Nayeem@MOMED.COM] > Sent: Friday, August 31, 2001 3:58 PM > To: DB2-L@RYCI.COM > Subject: Re: jcl to start database > > //STARTRW EXEC PGM=IKJEFT01, > // DYNAMNBR=30,COND=(4,LT) > //********************************************************** > //STEPLIB DD DSN=&DSNLOAD,DISP=SHR > // DD DSN=&SDSNEXT,DISP=SHR > //SYSPRINT DD SYSOUT=* > //SYSOUT DD SYSOUT=* > //SYSTSPRT DD SYSOUT=* > //SYSUDUMP DD SYSOUT=* > //SYSTSIN DD * > DSN SYSTEM(DB2E) > -START DATABASE(db1) SPACENAM(ts1) ACCESS(FORCE) > END > > Thanks & Regards > M.A.Nayeem > IBM Certified Solutions Expert - DB2 UDB Database Administration > > > > > > > "Mathur, Anil" [...]
16720 58 27_Re: SQL OUTER JOIN Question13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Fri, 31 Aug 2001 13:29:57 -0700402_iso-8859-1 Do those two rows in file A have field4 = 'APPHST' ?
Thanks. Ashish.
-----Original Message----- From: Johnson, Daryl [SMTP:Daryl.Johnson@PACIFICORP.COM] Sent: Friday, August 31, 2001 12:53 PM To: DB2-L@RYCI.COM Subject: SQL OUTER JOIN Question
We are on DB2 5.1. the following SQL is not returning 2 rows that are in file A but not in file B, what am I not seeing? [...]
16779 97 27_Re: SQL OUTER JOIN Question11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 31 Aug 2001 15:53:49 -0500420_- Mohan,
It's more subtle than that. Even those rows in A that do qualify will be eliminated the way it is written.
Daryl,
The predicate on field7 essentially makes it a right outer join (those with b.field7 = null will be eliminated due to this condition).
If you want the result that I think you want, you will need to use a nested table expression for table b with its subselect i.e. [...]
16877 48 43_Re: Unusual behavior with stored procedure.11_Rick Rogers21_Rick.Rogers@TELUS.COM31_Fri, 31 Aug 2001 15:31:45 -0600560_iso-8859-1 We are talking about a single SQL statement, and we are able to get an appropriate access plan when we use the control center, for example. We get the same timeron value as shown by the access plan when we snapshot this SQL running from the command line. We get a hugely inflated timeron value when we snapshot the exact same SQL running as part of a SQL stored procedure. The procedure runs in excess of 14 hours, while the command line script runs for 1.5 hours. (the command line script has two literals that are variables in the procedure) [...]
16926 151 27_Re: SQL OUTER JOIN Question13_Mohan, Ashish21_Ashish.Mohan@NIKE.COM31_Fri, 31 Aug 2001 15:06:25 -0700296_iso-8859-1 Suresh, thanks for pointing out but would appreciate if you could explain it a bit further. How is the original query a right outer join? I thought the presence of local predicates on both A and B actually make it an Inner join (and not a right outer join as you've pointed out). [...]
17078 37 27_Re: DB2 V6 no rows returned12_Loria, Susan27_Susan_C_Loria@TVRATINGS.COM31_Fri, 31 Aug 2001 20:02:39 -0400302_iso-8859-1 No and we at Nielsen Media Research have been running V6 since 11/18/2000 and all has worked as expected.
Unsure what might have caused this problem for you - can you provide more information - like what type of query and maybe what maintenance level of V6 did you install, etc. [...]
17116 187 27_Re: SQL OUTER JOIN Question11_Suresh Sane21_data_arch@HOTMAIL.COM31_Fri, 31 Aug 2001 22:18:30 -0500529_- Ashish,
You are correct - I had forgotten about the predicate on the A table. This will make it an Inner Join, not a Right Join. I also agree with your SQL that uses the NTEs as I suggested.
The main point I was trying to make was that such joins coded as OJ almost always will need an NTE. If they don't today, an unsuspecting programmer could add a predicate on the right table that changes the entire meaning of the query. Granted - testing should detect this but we all know how well testing is done.... [...]