1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2005, week 4
2 191 54_Re: CALL SQL Stored Procedure: SQL0444N Reason code: 44_Peri19_peri.ncst@GMAIL.COM31_Thu, 22 Dec 2005 12:06:52 +0530666_ISO-8859-1 Rob, As part of the logic of your procedure you are placing an explicit call to the procedure LIVE.GET_SEARCH_CLAUSE. There could be a possibility that db2 is not finding the shared lib for this procedure?
SET SEARCH_CLAUSE = LIVE.GET_SEARCH_CLAUSE(KEYWORDS);
On 12/22/05, Rob Wright wrote: > > Hi > > I have DB2 UDB V8.2, FIXPAK 10, running on a Win2K machine. > I have created a SQL stored procedure. When I try and invoke it, I get the > error > > 42724(-444)[IBM][CLI Driver][DB2/NT] SQL0444N Routine "*CATEGORY" > (specific name "SQL051222152349180") is implemented with code in library > or [...]
194 25 22_LOAD using clause WHEN20_Livia Gomes da Silva31_livia.silva@CAIXASEGUROS.COM.BR31_Thu, 22 Dec 2005 09:41:46 -0300334_iso-8859-1 Hi list, I'm using LOAD utility with clause WHEN, to load records that satisfy the specific condition. During the execution, I'm receiving warning messages DSNU314I. DSNU314I < DSNURWBF - RECORD (1) WILL BE DISCARDED DUE TO WHEN SPECIFICATION(S) I would like to suppress this messages.Does anyone know how to do this? [...]
220 423 39_Re: DB2v7 z/OS: CMTSTAT ACTIVE/INACTIVE12_Fay, Colin M22_cfay@UNUMPROVIDENT.COM31_Thu, 22 Dec 2005 10:59:27 -0500426_us-ascii Hi Kirk and Martin,
Thanks, that was just the re-assurance I needed. It sounds straightforward, I dont know why it wasn't done here before.
Colin
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kirk Hampton Sent: Monday, December 19, 2005 5:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2v7 z/OS: CMTSTAT ACTIVE/INACTIVE [...]
644 59 23_Re: JDBC updates to DB214_Lennart Henang26_lennart.henang@HENFIELD.SE31_Thu, 22 Dec 2005 10:13:25 -0600437_- We did it this way...
First, we have a rule that says that you should have very strong arguments if you should be allowed to execute dynamic SQL against our production system.
The result of the above has been that JDBC applications have been developed to be run against our DW system using tables copied from our production system. Not a good solution, but we don't get any dynamic SQL into our production system... [...]
704 38 31_Classpath Changes for Websphere0_28_BILL.GALLAGHER@PHOENIXWM.COM31_Thu, 22 Dec 2005 15:34:07 -0500394_us-ascii Hi,
I'm posting a question on behalf of a colleague:
Between UDB Version 7 and UDB Version 8, what classpath changes were made, or need to be made in order to support WebSphere?
We've recently upgraded from DB2/UDB v7.2 fixpak 7 to DB2/UDB v8.2 fixpack 10 on AIX 5.2 in a test environment, and one of our WebSphere applications is experiencing some problems. [...]
743 137 54_Re: zOS v7 OBID on Create Table sqlcode -736 - post #215_Philip Sevetson22_db2.dba.guru@GMAIL.COM31_Thu, 22 Dec 2005 16:59:58 -0500593_ISO-8859-1 It looks here like you're trying to create an object with OBID 140, not OBID 142.
--Phil
On 12/21/05, Brian Bear wrote: > > > Hi Listers. > > This was rejected as a dupe, so trying to send again with some minor > changes to the subject and the content. Sorry if you're reading this again. > > > > > In trying to create a table specifying OBID 142. SQLCODE = -736, ERROR: > INVALID OBID 140 SPECIFIED results. > > CREATE TABLE CHARM.TDIST23_PUSH_XREFB > LIKE CHARM.TDIST23_PUSH_XREF > IN DDIST01.SDIST123 > OBID 140 > > > Listing [...]
881 16 26_Re: LOAD using clause WHEN13_K.A.Rodriguez26_grnscrn-db2jan05@YAHOO.COM31_Thu, 22 Dec 2005 16:33:49 -0600700_- You can eliminate the DSNU314I messages by removing the SYSDISC DD from your JCL. However in doing so you run the risk of not knowing if any records were discarded for reasons other than not matching the WHEN specification. Kim
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
898 44 22_Restore to new server.16_Squeak Smalltalk20_wallenberg@GMAIL.COM31_Fri, 23 Dec 2005 09:39:44 +0100487_ISO-8859-1 Hi,
I need to restore a online back to a new server. I have 1- the logfile 2- the online backup. Do I need something more ?
The plan was a- Install db2 on the machine b- create the instance c- create the database with "db2 create database Old_db_name" ( for the moment I do not have the total create statement for the old db. I can probably fix it) d- do the restore. " db2 RESTORE DATABASE Old_db_name FROM . TAKEN AT '20051221080102' INTO Old_db_name"; " [...]
943 96 41_BIND Authorisations for program promotion13_Michael Ebert18_mebert@AMADEUS.COM31_Fri, 23 Dec 2005 11:25:39 +0100495_US-ASCII Hi List,
there's a group here that is trying to replace an existing program promotion process with a new version. I want to use this to optimize the existing authorisations given to the userid doing the promotions. Currently this userid is connected to RACF groups named after schemas, which gives object ownership to the userid - far too many privileges of course. However, I'm confused as to all the different authorisations around the BIND process and their interaction. [...]
1040 192 57_zOS, DB2 V7: BIND Authorisations for program promotion II13_Michael Ebert18_mebert@AMADEUS.COM31_Fri, 23 Dec 2005 12:27:32 +0100384_US-ASCII Some more thoughts: it looks like PACKADM is not good, it doesn't include BINDADD which is required but includes EXECUTE which is not required and in fact a security problem. So I think the binder ID needs these authorisations:
1. BINDAGENT for all possible owners 2. BIND, COPY ON PACKAGE coll.* for all possible collections 3. CREATE IN COLLECTION * 4. BINDADD [...]
1233 105 61_Re: zOS, DB2 V7: BIND Authorisations for program promotion II14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Fri, 23 Dec 2005 23:09:18 +1100355_US-ASCII The bindor needs BINDAGENT for whatever OWNER is used. Usually BINDAGENT is granted to a secondary auth-id held by the bindor.
The other privileges would be held by the BIND OWNER directly (not via a secondary auth-id), including any table access privileges.
James Campbell
On 23 Dec 2005 at 12:27, Michael Ebert wrote: [...]
1339 286 61_Re: zOS, DB2 V7: BIND Authorisations for program promotion II13_Michael Ebert18_mebert@AMADEUS.COM31_Fri, 23 Dec 2005 13:34:02 +0100406_US-ASCII The binder ID (let's call it bbbb) is a RACF group, the actual people running the jobs will be connected to this group.
So for command BIND PACKAGE(pppp) OWNER(xxxx), you propose I issue (as SYSADM):
0 - SET CURRENT SQLID='xxxx'; 1 - GRANT BINDAGENT TO bbbb; 2 - GRANT BIND,COPY ON PACKAGE pppp.* TO xxxx; 3 - GRANT CREATE IN COLLECTION pppp TO xxxx; 4 - GRANT BINDADD TO xxxx; [...]
1626 353 61_Re: zOS, DB2 V7: BIND Authorisations for program promotion II14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 24 Dec 2005 00:21:52 +1100458_US-ASCII You might find
(initially under your SYSADM userid) 1 - GRANT BIND,COPY ON PACKAGE pppp.* TO xxxx; 2 - GRANT CREATE IN COLLECTION pppp TO xxxx; 3 - GRANT BINDADD TO xxxx; 4 - SET CURRENT SQLID='xxxx'; 5 - GRANT BINDAGENT TO bbbb;
a better order of doing things. You might also find PACKADM better than CREATE since it will allow xxxx to overwrite a package owned by another user-id; although this might not be a good thing. YMMV. [...]
1980 41 55_How to get column count and break on new column value??14_Larry Bernacki29_Lawrence.CTR.Bernacki@FAA.GOV31_Fri, 23 Dec 2005 09:28:37 -0500353_US-ASCII Hello all, I have been asked to provide SQL that will provide a count of the number of rows given a specific value. For instance, if it were a employee table, list all employees by department, sorted by department, and given that the output will be grouped by department, provide a count of the number of employees at each new department. [...]
2022 185 59_Re: How to get column count and break on new column value??12_McKown, John26_john.mckown@UICIINSCTR.COM31_Fri, 23 Dec 2005 08:44:57 -0600303_us-ascii Isn't this just:
SELECT DEPTNO, COUNT(EMPNO) FROM EMPLOYEES GROUP BY DEPTNO SORT BY DEPTNO ;
where DEPTNO is the department number and EMPNO is the employee number. I assume that an employee is only in a single department.
Or am I missing something?
2208 117 59_Re: How to get column count and break on new column value??17_Kumar Vaddadi/EIG37_Kumar.Vaddadi@ECONOMICALINSURANCE.COM31_Fri, 23 Dec 2005 10:00:34 -0500559_us-ascii
I think it should be ORDER BY... not SORT BY. I guess like SELECT DEPTNO, COUNT(EMPNO) FROM EMPLOYEES GROUP BY DEPTNO ORDER BY DEPTNO
Happy holidays.. ************* Thanks & Regards ******************* Vaddadi Shyam Kumar IBM Db2 Certified Database Administrator Data Management Group The Economical Insurance Group, Waterloo. Reach me @ 519-570-8500 ext 2242 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [...]
2326 47 59_Re: How to get column count and break on new column value??12_McKown, John26_john.mckown@UICIINSCTR.COM31_Fri, 23 Dec 2005 09:02:42 -0600542_us-ascii > -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kumar Vaddadi/EIG > Sent: Friday, December 23, 2005 9:01 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] How to get column count and break on new > column value?? > > > > I think it should be ORDER BY... not SORT BY. I guess like > SELECT DEPTNO, COUNT(EMPNO) > FROM EMPLOYEES > GROUP BY DEPTNO > ORDER BY DEPTNO > > Happy holidays.. > ************* Thanks & Regards ******************* > Vaddadi Shyam Kumar [...]
2374 280 59_Re: How to get column count and break on new column value??14_Larry Bernacki29_Lawrence.CTR.Bernacki@FAA.GOV31_Fri, 23 Dec 2005 10:21:43 -0500514_US-ASCII John, Thanks for the response. Here is what I am really trying to get done. Here is the real SQL that the user is running.
SELECT AP.DEVICE_ID, AP.DATE_MSMT, COUNT(T.SER_NO), T.OPERATOR, T.SER_NO, T.REGNO, AP.ASE, AP.ASE_SD, AP.ASE_OBS_NUM FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T WHERE AP.MODE_S = T.HEX_MODE_S AND T.OPERATOR = 'XXX' AND AP.DEVICE_ID = 'WWWW' AND T.TYPE = 'B752' AND AP.DATE_MSMT BETWEEN '2005-04-08' AND '2005-10-04' ORDER BY T.SER_NO, T.REGNO, AP.DATE_MSMT ; [...]
2655 95 26_Re: Restore to new server.11_Davies, Ian31_Ian.Davies@CGI.HEALTH.GOV.AB.CA31_Fri, 23 Dec 2005 08:48:01 -0700425_US-ASCII Well that's just so happens to be what we're doing at the moment :-)
The SQL0970N is probably because the instance userid of the new system does not have permission to create the new container or the target container layout has changed. If the target directory structure is the same as the source then you do not need to use the "set tablespace containers", "restore redirect" or the "restore continue". [...]
2751 54 26_Re: Restore to new server.12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Fri, 23 Dec 2005 11:26:20 -0500474_US-ASCII That's normal. Add REPLACE EXISTING to the restore and put the logs in OVER FLOW LOG PATH. Phil
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Squeak Smalltalk Sent: Friday, December 23, 2005 3:40 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Restore to new server.
Hi,
I need to restore a online back to a new server. I have 1- the logfile 2- the online backup. Do I need something more ? [...]
2806 57 59_Re: How to get column count and break on new column value??20_Christopher J Pomasl19_pomasl@STARBAND.NET31_Fri, 23 Dec 2005 09:56:07 -0700618_utf-8 On Fri December 23 2005 8:21 am, Larry Bernacki so notably scribed: > John, > Thanks for the response. Here is what I am really trying to get > done. Here is the real SQL that the user is running. > > SELECT AP.DEVICE_ID, AP.DATE_MSMT, COUNT(T.SER_NO), > T.OPERATOR, T.SER_NO, T.REGNO, AP.ASE, > AP.ASE_SD, AP.ASE_OBS_NUM > FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T > WHERE AP.MODE_S = T.HEX_MODE_S > AND T.OPERATOR = 'XXX' > AND AP.DEVICE_ID = 'WWWW' > AND T.TYPE = 'B752' > AND AP.DATE_MSMT BETWEEN '2005-04-08' AND '2005-10-04' > ORDER BY T.SER_NO, T.REGNO, AP.DATE_MSMT > ; > > They want a [...]
2864 108 59_Re: How to get column count and break on new column value??12_McKown, John26_john.mckown@UICIINSCTR.COM31_Fri, 23 Dec 2005 11:15:17 -0600571_us-ascii > -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Christopher J Pomasl > Sent: Friday, December 23, 2005 10:56 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] How to get column count and break on new > column value?? > > > On Fri December 23 2005 8:21 am, Larry Bernacki so notably scribed: > > John, > > Thanks for the response. Here is what I am really > trying to get > > done. Here is the real SQL that the user is running. > > > > SELECT AP.DEVICE_ID, AP.DATE_MSMT, COUNT(T.SER_NO), [...]
2973 45 59_Re: How to get column count and break on new column value??13_Charles Greer24_CGREER@SONOMA-COUNTY.ORG31_Fri, 23 Dec 2005 09:15:13 -0800702_US-ASCII I've done this kind of thing before by joining to the T table twice, but the SQL gets really ugly. A subselect is rather cleaner.
> SELECT AP.DEVICE_ID, AP.DATE_MSMT,
(select count(t1.ser_no) from usmasps.tblapprovaldata t1 where AP.MODE_S = T1.HEX_MODE_S AND T1.OPERATOR = 'XXX' AND AP.DEVICE_ID = 'WWWW' AND T1.TYPE = 'B752'),
> T.OPERATOR, T.SER_NO, T.REGNO, AP.ASE, > AP.ASE_SD, AP.ASE_OBS_NUM > FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T > WHERE AP.MODE_S = T.HEX_MODE_S > AND T.OPERATOR = 'XXX' > AND AP.DEVICE_ID = 'WWWW' > AND T.TYPE = 'B752' > AND AP.DATE_MSMT BETWEEN '2005-04-08' AND '2005-10-04' > ORDER BY T.SER_NO, T.REGNO, AP.DATE_MSMT > ; [...]
3019 233 59_Re: How to get column count and break on new column value??0_25_LL581@DAIMLERCHRYSLER.COM31_Fri, 23 Dec 2005 12:56:17 -0500254_US-ASCII All,
> It can't be done with SQL alone. > > There is no concept in SQL for breaking the stream of rows to output a count > of the previous set of data. For this you need a program or a report writer > (IBM's QMF or CA's PRF on MVS). [...]
3253 116 59_Re: How to get column count and break on new column value??14_Larry Bernacki29_Lawrence.CTR.Bernacki@FAA.GOV31_Fri, 23 Dec 2005 13:24:16 -0500760_US-ASCII Thank you all who have responded. We came up with the following to get the results we were looking for.. I will take the other suggestions and test each one of them.
SELECT AP.DEVICE_ID, AP.DATE_MSMT, T.OPERATOR, T.SER_NO, T.REGNO, AP.ASE, AP.ASE_SD, AP.ASE_OBS_NUM, MYCOUNT.USECOUNT FROM RVSM.AC_PERFORMANCE AP, USMASPS.TBLAPPROVALDATA T JOIN (SELECT AX.MODE_S, COUNT(*) AS USECOUNT FROM RVSM.AC_PERFORMANCE AX, USMASPS.TBLAPPROVALDATA TX WHERE AX.MODE_S = TX.HEX_MODE_S AND TX.OPERATOR = 'UAL' AND AX.DEVICE_ID = 'AICT' AND TX.TYPE = 'B752' AND AX.DATE_MSMT BETWEEN '2005-04-08' AND '2005-10-04' GROUP BY AX.MODE_S) MYCOUNT ON T.HEX_MODE_S = MYCOUNT.MODE_S WHERE AP.MODE_S = T.HEX_MODE_S AND T.OPERATOR = 'UAL' AND AP.DEVICE_ID = 'AICT' [...]
3370 82 59_Re: How to get column count and break on new column value??20_Christopher J Pomasl19_pomasl@STARBAND.NET31_Fri, 23 Dec 2005 11:37:15 -0700486_utf-8 On Fri December 23 2005 10:56 am, LL581@daimlerchrysler.com so notably scribed: > All, > > > It can't be done with SQL alone. > > > > There is no concept in SQL for breaking the stream of rows to output a > > count > > > of the previous set of data. For this you need a program or a report > > writer > > > (IBM's QMF or CA's PRF on MVS). > > That said, ... how about something like this: > > SELECT AP.DATE_MSMT, > T.SER_NO, 0 AS TOTAL_COUNT , T.REGNO, AP.ASE, > AP.ASE_SD, [...]
3453 53 17_Holiday Greetings24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Fri, 23 Dec 2005 14:02:56 -0500387_iso-8859-1 Merry Christmas! Happy Channukah! Blessed Kwanzaa! Prayerful Ramadan! Delightful Diwali! Joyeux Noel et Bonne Année! YENI YILINIZ KUTLU OLSUN (Happy New Year in Turkish)
And a safe, happy, healthy, and prosperous 2006 to us all.
Frank
P.S. And if I missed a celebration that is important to you, please e-mail it to me and I will honor it next year. [...]
3507 177 47_Discounted DB2 Training from The Fillmore Group24_The Fillmore Group, Inc.18_fillmore@CLARK.NET31_Fri, 23 Dec 2005 14:15:37 -0500588_iso-8859-1
Improve your DB2 skills with IBM authorized education - at a discount!
Register and pay for your 2006 training class before December 31st and save 10% on the course cost.
The classes scheduled are available via Distance Learning (save on travel, too) or may be attended in the classroom, in Baltimore, Maryland. To view the complete course outlines visit our website, www.thefillmoregroup.com. While you're there you can also view a listing of other DB2 courses offered - and take a look at the free, pre-recorded DB2 sessions available "on Demand". [...]
3685 194 36_Bind Error-DSNDB01.SPT01 unavailable9_tspdba00119_tspdba001@GMAIL.COM31_Sat, 24 Dec 2005 08:20:29 -0500920_us-ascii Hi,
I have the following error while doing bind after creating new database.
DB2 Version is 8.1 on zos, can anybody help me with resolving this. Thanks in advance.
Bind Error
DSNT500I -DTI1 DSNXESX RESOURCE UNAVAILABLE
REASON 00D70002
TYPE 00000200
NAME DSNDB01 .SPT01
DSNT233I -DTI1 UNSUCCESSFUL BIND FOR
PACKAGE = DB2T.ntrb.ibacc.()
Thanks,
Tsp
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest [...]
3880 91 40_Re: Bind Error-DSNDB01.SPT01 unavailable13_Richard Fazio21_rfazio@TRANSUNION.COM31_Sat, 24 Dec 2005 09:30:12 -0600333_US-ASCII This really looks ugly. It suggests that part of the directory is missing. I assume that you've done a few basic checks and have seen all the datasets for the directory are in place. If datasets are missing, a recover would be in order (remember the directory is VCAT managed...you have to pre-allocate VSAM datasets). [...]
3972 84 40_Re: Bind Error-DSNDB01.SPT01 unavailable9_Mike Bell21_mbell11a1@VERIZON.NET31_Sat, 24 Dec 2005 10:28:44 -0600358_Windows-1252 Just a Guess - But one way you could get this error is when DB2 is trying to allocate a A002 dataset because the A001 dataset is now 2G. Check the listcat for dsndb01.spt01 and see how big it is. If it is 2000 cylinders or so, run listcat and check actual high used rba, then allocate a dsndb01.spt01.i0001.a002 dataset just like the a001. [...]
4057 106 40_Re: Bind Error-DSNDB01.SPT01 unavailable5_tsp l19_tspdba001@GMAIL.COM31_Sat, 24 Dec 2005 16:05:15 -0500541_ISO-8859-1 Thanks guys. I will try this on Tuesday and hope I will be able to resolve this.
TSP
On 12/24/05, Mike Bell wrote: > Just a Guess - > But one way you could get this error is when DB2 is trying to allocate a > A002 dataset because the A001 dataset is now 2G. Check the listcat for > dsndb01.spt01 and see how big it is. If it is 2000 cylinders or so, run > listcat and check actual high used rba, then allocate a > dsndb01.spt01.i0001.a002 dataset just like the a001. > > Mike Bell > HLS [...]
4164 46 16_DB2 - Plan Error13_Jerry Ragland25_jerry_r_ragland@YAHOO.COM31_Mon, 26 Dec 2005 08:30:27 -0600430_- Hi all,
I am new to CICS-DB2 programming. I get the following error when I try to give grant to a plan.
The JCL and the error message is pasted below -
####JCL#####
//SYSTSIN DD * DSN SYSTEM(DSN1) RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71) PARMS('/ALIGN(LHS) MIXED') - LIB('DSN710.RUNLIB.LOAD') //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSIN DD * GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ; [...]
4211 67 20_Re: DB2 - Plan Error12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 26 Dec 2005 19:39:44 +0200432_us-ascii Hi Jerry,
You need to bind it first.
Isaac Yassin
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jerry Ragland Sent: Monday, December 26, 2005 4:30 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 - Plan Error
Hi all,
I am new to CICS-DB2 programming. I get the following error when I try to give grant to a plan. [...]
4279 21 32_New Year Message from DB2-L Team12_Cuneyt GOKSU19_cuneytgoksu@USA.NET31_Tue, 27 Dec 2005 09:27:46 +0200619_ISO-8859-1 Esteemed DB2-L members,
We wish you a pleasant, peaceful and healty New Year with your loved ones.
Kind Regards, DB2-L Team
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
4301 37 20_REG: updates/Inserts14_bhaskar kalita22_db2questions@YAHOO.COM31_Tue, 27 Dec 2005 00:56:27 -0800352_iso-8859-1 Hi List I have an update statement which is supposed to update 10 rows. If it gets an error while updating the 7th row, what will happen to the previous six rows considering that no ROLLBACK/COMMIT statements have been explicitly used? Will the result differ for batch spufi and COBOL programs ? Also, will it be different for Inserts? [...]
4339 41 36_Re: New Year Message from DB2-L Team10_Max Scarpa16_mscarpa@CESVE.IT31_Tue, 27 Dec 2005 10:28:29 +0100738_US-ASCII Thank you all for wishes, and I wish the same for all DB2-L esteemed administrators and listers.
With best regards
Max Scarpa
> Esteemed DB2-L members, > > We wish you a pleasant, peaceful and healty New Year with your loved ones. > > Kind Regards, > DB2-L Team > > ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives > and home page at http://www.idugdb2-l.org/archives/db2-l.html. From > that page select "Join or Leave the list". The IDUG DB2-L FAQ is at > http://www.idugdb2-l.org. The IDUG List Admins can be reached at > DB2-L-REQUEST@www.idugdb2-l.org. Find [...]
4381 29 15_Error Code -92213_Jerry Ragland25_jerry_r_ragland@YAHOO.COM31_Tue, 27 Dec 2005 04:04:02 -0600477_- Hi all,
I get the -922 error code when I run my cobol program which has a insert stmt. in to a DB2.
The explanation for this error code is given as -
-922 AUTHORIZATION FAILURE: error-type and the error-type I get is user authorization
If error-type is user authorization:, the authorization-ID specified to DB2 through your attachment facility is not valid for DB2. See your system programmer or your CICS, IMS, or TSO system administrator. [...]
4411 94 19_Re: Error Code -92211_Jay Reavill23_Jay.Reavill@CERTEGY.COM31_Tue, 27 Dec 2005 07:50:35 -0500446_us-ascii Typically a -922 on authority is one of the following...
- Execute auth on the plan is missing - Execute auth on the package is missing
Make sure the id you're executing under has these auths.
Hope that helps, Jay
---------------------------------------- Jay Reavill DBA Certegy Card Services 11601 Roosevelt Blvd. St. Petersburg, FL. 33716 Office (727) 227-2144 ---------------------------------------- [...]
4506 27 19_Re: Error Code -92213_Jerry Ragland25_jerry_r_ragland@YAHOO.COM31_Tue, 27 Dec 2005 06:50:55 -0600446_- Hi Jay,
Thanks for your reply... Can u please tell me where to give the execute auth and what should be given.
Thanks, -Jerry.
On Tue, 27 Dec 2005 07:50:35 -0500, Jay Reavill wrote:
>Typically a -922 on authority is one of the following... > >- Execute auth on the plan is missing >- Execute auth on the package is missing > >Make sure the id you're executing under has these auths. > [...]
4534 81 27_AW: [DB2-L] Error Code -92212_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 27 Dec 2005 14:04:07 +0100804_iso-8859-1 Jerry, you may try
GRANT EXECUTE ON PLAN yourplan TO XYZ ;
XYZ can be a userid or a racf group e.g.
If your cobol program is bound as a package (and not as a plan) use
GRANT EXECUTE ON PACKAGE yourcollection.* TO XYZ ;
HTH.
With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r Asst. Chairmain GSE Workinggroups DB2 Nord und Sued ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Development and Product Support (E3) Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- »"Next [...]
4616 57 19_Re: Error Code -92213_Kenney, Marty24_Marty.Kenney@RAILINC.COM31_Tue, 27 Dec 2005 08:09:46 -0500433_us-ascii Jerry, if this is a CICS program then the RCT definition of the transaction determines what you need to grant authority to.
Marty Kenney Sr. DB2 DBA Railinc (919) 651-5211
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jerry Ragland Sent: Tuesday, December 27, 2005 7:51 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Error Code -922 [...]
4674 92 19_Re: Error Code -92214_Avram Friedman21_avramf@IBMSYSPROG.COM31_Tue, 27 Dec 2005 05:18:30 -0800526_iso-8859-1 Hi Jerry Your question yesterday titled "[DB2-L] DB2 - Plan Error" contained the correct statement for GRANT of EXECUTE authorities. It was missing the BIND.
It might pay to read the
Title: DB2 UDB for OS/390 and z/OS V7 Application Programming and SQL Guide
at http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnaph15/CCONTENTS
and
Title: DB2 UDB for OS/390 and z/OS V7 Administration Guide http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnagh15/CCONTENTS [...]
4767 210 20_Re: DB2 - Plan Error25_Biswal, Manas (Cognizant)26_Manas.Biswal@COGNIZANT.COM31_Tue, 27 Dec 2005 20:37:22 +0530346_iso-8859-1
DSNTEP2 is a batch SQL processor. It just runs whatever SQL is present in the SYSIN card. Check that your plan exists on the SYSIBM.SYSPLAN table.
Thanks, Manas
"It is not the strongest of the species that survives, nor the most intelligent; it is the one that is most adaptable to change". - Charles Darwin [...]
4978 158 24_Re: REG: updates/Inserts25_Biswal, Manas (Cognizant)26_Manas.Biswal@COGNIZANT.COM31_Tue, 27 Dec 2005 20:38:10 +0530458_iso-8859-1
Everything will be rolled back in all cases.
Thanks, Manas
"It is not the strongest of the species that survives, nor the most intelligent; it is the one that is most adaptable to change". - Charles Darwin
________________________________
From: DB2 Data Base Discussion List on behalf of bhaskar kalita Sent: Tue 12/27/2005 3:56 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] REG: updates/Inserts [...]
5137 171 24_Re: REG: updates/Inserts13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Tue, 27 Dec 2005 10:50:00 -0500575_- Hello Bhaskar,
What happens depends upon the type of error you encounter in the update of the 7th row.
Some negative SQLcodes will generate rollback on your behalf. But not all. For example, -803, violating a unique index or constraint, does NOT generate a rollback. However, -911 does.
So it is your program's responsibility to do adequate error checking and generate rollback if necessary. It seems to me most people casually refer to batch SPUFI and mean the sample program DSNTEP2. DSNTEP2 behaves as I describe above (for insert and update). [...]
5309 41 16_DB2 - Plan Error14_Mark McCormack27_mamccormack@STATESTREET.COM31_Tue, 27 Dec 2005 12:22:42 -0500344_US-ASCII < //SYSTSIN DD * < DSN SYSTEM(DSN1) < RUN PROGRAM(DSNTEP2) PLAN(DSNTEP71) PARMS('/ALIGN(LHS) MIXED') - < LIB('DSN710.RUNLIB.LOAD') < //SYSPRINT DD SYSOUT=* < //SYSUDUMP DD SYSOUT=* < //SYSIN DD * < GRANT BIND,EXECUTE ON PLAN DB2PLN01 TO PUBLIC ; < < It will be more helpfull if someone explains me what the above JCL tries to do. [...]
5351 101 20_Re: DB2 - Plan Error7_troycci29_troycci@COLEMANCONSULTING.COM31_Tue, 27 Dec 2005 12:23:55 -0600588_US-ASCII Line 1: The SYSTSIN DD is the input for TSO commands. You can issue these same command online but since you are running them in batch the IKJEFT1B TSO processor will process the commands. Line 2: DSN command - connect to DB2 in your case subsystem is DSN1 Line 3: RUN command - Run program DSNTEP2 using PLAN DSNTEP71 out of library DSN710.RUNLIB.LOAD passing some format parms. This plan is usually installed using hlq.SDSNSAMP: DSN710.NEW.SDSNSAMP(DSNTEJ1L) Line 4: Load library you install DSNTEP2 in. Line 5, 6 - output Line 7: SYSIN DD - The DDL you want to execute in [...]
5453 200 24_Re: REG: updates/Inserts7_troycci29_troycci@COLEMANCONSULTING.COM31_Tue, 27 Dec 2005 12:41:14 -0600541_US-ASCII This is one of the reasons you need to design checkpoint/restart (commit) into your program design so that you can have a clean commit or rollback when you are in the middle of a logical unit of work. If you do not issue the ROLLBACK in your program before it ends then you will have saved some of the work but may have not completed a logical unit of work. Usually your error handling section of the program will issue the ROLLBACK for you so that you do not commit incomplete work. DB2 is consistent on how it handles commit [...]
5654 142 32_JAVA SQLException and SQLWarning10_Irwin, Jim19_Jim.Irwin@OA.MO.GOV31_Tue, 27 Dec 2005 13:34:53 -0600536_us-ascii Hey guys........I'm an old mainframe dinosaur trying to adjust to the new millennium and I need some help. Back in the old days when I saw an DB2 SQLCODE (or IDMS Error Status) I could go to my handy reference and eventually figure out what was going on. Now we have a WebSphere/JAVA/JDBC application accessing DB2 for z/OS that "throws" exceptions which apparently the application is not "catching". I'm trying to help diagnose some of the problems but not all of the exceptions show the SQLCODE/SQLSTATE that caused it. [...]
5797 187 36_Re: JAVA SQLException and SQLWarning12_McKown, John26_john.mckown@UICIINSCTR.COM31_Tue, 27 Dec 2005 13:43:26 -0600142_us-ascii Perhaps
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNJVJ12/2.1. 18
would help?
5985 90 36_Re: JAVA SQLException and SQLWarning13_Charles Greer24_CGREER@SONOMA-COUNTY.ORG31_Tue, 27 Dec 2005 11:42:30 -0800390_US-ASCII Hello Jim, the good news is that the SQLException passes all information that you need...
if you can have your Java program print a stack trace then you'll get some more information:
try { ... } catch (SQLException e) { e.printStackTrace(); }
With the Type 4 JDBC driver, the SQLCODE and SQLSTATE is inserted in the message, in its -XXX notation: [...]
6076 60 36_Re: JAVA SQLException and SQLWarning12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 27 Dec 2005 11:45:06 -0800336_iso-8859-1 Jim, Somewhere in the Websphere exception log should be the actual SQLSTATE/SQLCODE being thrown as well. I've never seen a case if the person had a finally section that captured the SQL exceptions that didn't give the proper SQL code or state. Your java programmer needs to capture these just like any other exception. [...]
6137 125 36_Re: JAVA SQLException and SQLWarning10_Irwin, Jim19_Jim.Irwin@OA.MO.GOV31_Tue, 27 Dec 2005 14:07:30 -0600492_us-ascii Hi Charles, My understanding is that the what you say is true in most cases but not all. One example would be where the application does a singleton select but there is more than one row that qualifies. In the mainframe world you would get back a -811 SQLCODE but all that gets returned on the JAVA side is an SQLException with no SQLCODE or SQLSTATE. As I understand it, and I understand very little, the driver does not pass the SQLCODE and/or SQLSTATE back for all requests. [...]
6263 111 36_Re: JAVA SQLException and SQLWarning10_Irwin, Jim19_Jim.Irwin@OA.MO.GOV31_Tue, 27 Dec 2005 15:07:22 -0600446_us-ascii Myron, My dilemma here is that this app was built using WAD by people that didn't necessarily understand what it was doing. There was very little error handling in the beginning. In fact, I was originally told that they had no way of knowing what sql the application was sending to DB2. I know we are doing this backward but if I can find out what the JAVA exceptions map to in DB2 I can at least point them in the right direction. [...]
6375 52 36_Re: JAVA SQLException and SQLWarning12_McKown, John26_john.mckown@UICIINSCTR.COM31_Tue, 27 Dec 2005 15:14:44 -0600580_us-ascii > -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Irwin, Jim > Sent: Tuesday, December 27, 2005 3:07 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] JAVA SQLException and SQLWarning > > > Myron, My dilemma here is that this app was built using WAD by people > that didn't necessarily understand what it was doing. There was very > little error handling in the beginning. In fact, I was > originally told > that they had no way of knowing what sql the application was > sending to > DB2. I know we [...]
6428 86 24_Re: REG: updates/Inserts14_bhaskar kalita22_db2questions@YAHOO.COM31_Tue, 27 Dec 2005 22:34:11 -0800426_iso-8859-1 Thanks dave for the update
Regards Bhaskar
"Seibert, Dave" wrote: Hello Bhaskar,
What happens depends upon the type of error you encounter in the update of the 7th row.
Some negative SQLcodes will generate rollback on your behalf. But not all. For example, -803, violating a unique index or constraint, does NOT generate a rollback. However, -911 does. [...]
6515 138 36_Re: JAVA SQLException and SQLWarning12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 28 Dec 2005 05:16:00 -0800505_iso-8859-1 Jim, The problem is that 99% of the java exceptions don't relate to anything in DB2. So you're facing an impossible task. You really need to modify the application such as John McKown suggested and put in the finally section to trap and write either the exception trace or the SQLCA data. Without one or the other, you will be basically shooting in the dark unless you happen to have a monitor on the mainframe that can trap all SQL and SQL exceptions and relate it back to the requestor. [...]
6654 30 36_Re: JAVA SQLException and SQLWarning10_Irwin, Jim19_Jim.Irwin@OA.MO.GOV31_Wed, 28 Dec 2005 08:28:20 -0600336_us-ascii Thanks to everyone for their input. I know that the application needs to handle these errors but I have no control over the application developers....I'm just trying to do what I can from our side to help the project out.
On the plus side I'm getting a crash course in WebSphere/JAVA/DB2 interaction. Thanks again. [...]
6685 47 36_Re: JAVA SQLException and SQLWarning12_McKown, John26_john.mckown@UICIINSCTR.COM31_Wed, 28 Dec 2005 08:37:43 -0600597_us-ascii > -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Irwin, Jim > Sent: Wednesday, December 28, 2005 8:28 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] JAVA SQLException and SQLWarning > > > Thanks to everyone for their input. I know that the > application needs to > handle these errors but I have no control over the application > developers....I'm just trying to do what I can from our side > to help the > project out. > > On the plus side I'm getting a crash course in WebSphere/JAVA/DB2 > interaction. Thanks [...]
6733 92 44_Fw: [DB2-L] JAVA SQLException and SQLWarning0_22_jfatula@AESSUCCESS.ORG31_Wed, 28 Dec 2005 09:53:31 -0500358_us-ascii Indeed. In this industry, we come up with a new language, and "relearn" many of the same old lessons each time. Error handling--we do not need any stupid error handing. Our code is perfect. Even if it was--the environment it runs in is never perfect.
Joe Fatula
----- Forwarded by Joseph Fatula/PHEAA on 12/28/2005 09:42 AM ----- [...]
6826 15 55_Sherry Simmerl/CSI is on vacation through April 3, 200514_Sherry Simmerl20_SSimmerl@CSIHOME.COM31_Wed, 28 Dec 2005 08:05:27 -0800637_US-ASCII I will be out of the office starting 12/28/2005 and will not return until 12/29/2005.
I will be receiving emails and will respond as soon as possible.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
6842 57 38_Re: DB2/ZOS V7 - Refresh dynamic cache12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 28 Dec 2005 17:00:17 -0600378_- I'd say that we charge for DB2, for utilities and for many other products. I think most other companies do too. It's not cheaper to provide a function in two ways than in one, and I don't find many customers who don't have the utilities. Generally I think there are enough functions that need to be provided that requests to do the same thing again are not as important. [...]
6900 38 24_Re: REG: updates/Inserts12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 28 Dec 2005 17:21:35 -0600625_- This is called atomicity in database terminology, and it works at several levels statement and transaction. The idea is that the statement or the transaction is either completely made or nothing is changed. If an individual insert, update or delete encounters an error, the results of that statement are removed, with few exceptions (example V8 multirow insert NOT ATOMIC option). ROLLBACK is for the entire transaction - all change statements prior to COMMIT. A very few errors cause rollback, but almost all have statement atomicity. For a step further, you can look at SAVEPOINT and ROLLBACK to SAVEPOINT. The more [...]