Re: DB2 Problem solution
It's programmer error. The view that I was shown, from the test system, was as I thought it was. The production system, however, had a different view. The production view would, under certain conditions, return a smaller selection set for the view than for the table. Now, we are arguing why this can't be G. Tom Duerbusch THD Consulting
Re: DB2 Problem
Thanks all. Interesting that everyone seems to suggest writing code to get around this problem. I would have thought that there would have been a DB2 method, which I didn't know about, that would, in the same LUW, with a locked DBSPACE, given me the same record selection set for the delete, as was obtained for the insert. Between Repeatable Read and locking the DBSPACE, I thought I should have been given the same selection set within the same LUW. Not a happy camper at this point G. Tom Duerbusch THD Consulting Kevin Corkery kcork...@live.com 6/16/2009 1:03 PM You need to create a delta table. Into an empty delta, select all records of interest from the source table. Insert all records from the delta table to the target table. Use the delta table as a basis for deletion of records from the source table. Not really a DB2 type but I have a similar scenerio with SQLServer and VSAM using ViaSQL; good ol' batch processing mentality at work here :-) -Original Message- From: owner-vs...@lehigh.edu [mailto:owner-vs...@lehigh.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 1:38 PM To: VSE Discussion List Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart
Re: DB2 Problem
Because that listserv is very much into DB2/zOS and DB2/UDB. There is very little there for DB2/VSE (or DB2/VM). And it is not something that I can problem reportyet. Tom Duerbusch THD Consulting MacIntyre, Cory cory.macint...@navistar.com 6/17/2009 2:16 PM Why don't you ask this question on the DB2 listserv. You might get an answer that is more to your liking. -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Wednesday, June 17, 2009 1:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: DB2 Problem Thanks all. Interesting that everyone seems to suggest writing code to get around this problem. I would have thought that there would have been a DB2 method, which I didn't know about, that would, in the same LUW, with a locked DBSPACE, given me the same record selection set for the delete, as was obtained for the insert. Between Repeatable Read and locking the DBSPACE, I thought I should have been given the same selection set within the same LUW. Not a happy camper at this point G. Tom Duerbusch THD Consulting Kevin Corkery kcork...@live.com 6/16/2009 1:03 PM You need to create a delta table. Into an empty delta, select all records of interest from the source table. Insert all records from the delta table to the target table. Use the delta table as a basis for deletion of records from the source table. Not really a DB2 type but I have a similar scenerio with SQLServer and VSAM using ViaSQL; good ol' batch processing mentality at work here :-) -Original Message- From: owner-vs...@lehigh.edu [mailto:owner-vs...@lehigh.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 1:38 PM To: VSE Discussion List Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminating the process that is adding records, not to loose records in the merge/purge process
Re: DB2 Problem
Why don't you ask this question on the DB2 listserv. You might get an answer that is more to your liking. -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Wednesday, June 17, 2009 1:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: DB2 Problem Thanks all. Interesting that everyone seems to suggest writing code to get around this problem. I would have thought that there would have been a DB2 method, which I didn't know about, that would, in the same LUW, with a locked DBSPACE, given me the same record selection set for the delete, as was obtained for the insert. Between Repeatable Read and locking the DBSPACE, I thought I should have been given the same selection set within the same LUW. Not a happy camper at this point G. Tom Duerbusch THD Consulting Kevin Corkery kcork...@live.com 6/16/2009 1:03 PM You need to create a delta table. Into an empty delta, select all records of interest from the source table. Insert all records from the delta table to the target table. Use the delta table as a basis for deletion of records from the source table. Not really a DB2 type but I have a similar scenerio with SQLServer and VSAM using ViaSQL; good ol' batch processing mentality at work here :-) -Original Message- From: owner-vs...@lehigh.edu [mailto:owner-vs...@lehigh.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 1:38 PM To: VSE Discussion List Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminating the process that is adding records, not to loose records in the merge/purge process? Thanks Tom Duerbusch THD Consulting Disclaimer Confidentiality Notice: This e-mail, and any attachments and/or documents linked to this email, are intended for the addressee and may contain information that is privileged, confidential, proprietary, or otherwise
Re: DB2 Problem
On Wed, 17 Jun 2009 13:27:17 -0500, Tom Duerbusch duerbus...@stlouiscity .com wrote: Thanks all. Interesting that everyone seems to suggest writing code to get around th is problem. I would have thought that there would have been a DB2 method, which I di dn't know about, that would, in the same LUW, with a locked DBSPACE, given me the same record s election set for the delete, as was obtained for the insert. Between Repeatable Read and locking the DBSPACE, I thought I should have been given the same selection set within the same LUW. Not a happy camper at this point G. Tom Duerbusch THD Consulting I'm a programmer, not a DB2 expert. I think you may need to contact IBM t o find one of them. You didn't explain how you know this is a single LUW. Alan Ackerman Alan (dot) Ackerman (at) Bank of America (dot) com
DB2 Problem
I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminating the process that is adding records, not to loose records in the merge/purge process? Thanks Tom Duerbusch THD Consulting
Re: DB2 Problem
Well, I'm hoping you were updating the table names for security purposes, and that's the error below. In this example, the table you're using for the SELECT (ASN.ERSHIST_X) is not the same table that you specify in the DELETE (ASN.CDERS_HISTORY). Nora -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 1:38 PM To: IBMVM@LISTSERV.UARK.EDU Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminating the process that is adding records, not to loose records in the merge/purge process? Thanks Tom Duerbusch THD Consulting
Re: DB2 Problem
ERSHIST_X is a view of table CDERS_HISTORY. Tom Duerbusch THD Consulting Graves Nora E nora.e.gra...@irs.gov 6/16/2009 1:08 PM Well, I'm hoping you were updating the table names for security purposes, and that's the error below. In this example, the table you're using for the SELECT (ASN.ERSHIST_X) is not the same table that you specify in the DELETE (ASN.CDERS_HISTORY). Nora -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 1:38 PM To: IBMVM@LISTSERV.UARK.EDU Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminating the process that is adding records, not to loose records in the merge/purge process? Thanks Tom Duerbusch THD Consulting
Re: DB2 Problem
Does the view match the table? I've created views that have WHERE clauses in them to restrict the data that is retrieved, things like WHERE FISCAL_YEAR = 2009. If that's the case, the 2 statements are not looking at the same set of rows. Nora -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 2:14 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: DB2 Problem ERSHIST_X is a view of table CDERS_HISTORY. Tom Duerbusch THD Consulting Graves Nora E nora.e.gra...@irs.gov 6/16/2009 1:08 PM Well, I'm hoping you were updating the table names for security purposes, and that's the error below. In this example, the table you're using for the SELECT (ASN.ERSHIST_X) is not the same table that you specify in the DELETE (ASN.CDERS_HISTORY). Nora -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 1:38 PM To: IBMVM@LISTSERV.UARK.EDU Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I
Re: DB2 Problem
Do you have the REXX SQL option available to you? Bill -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 10:38 AM To: IBMVM@LISTSERV.UARK.EDU Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminating the process that is adding records, not to loose records in the merge/purge process? Thanks Tom Duerbusch THD Consulting
Re: DB2 Problem
The view is a join of two tables. The view always has the same number of records as the base table. I'm joining in descriptions into the table, instead of having just the description codes.Referential integrity makes sure there is a match. Also, if there are no records being added, the merge record count always equals the purge record count. Tom Duerbusch THD Consulting Graves Nora E nora.e.gra...@irs.gov 6/16/2009 1:54 PM Does the view match the table? I've created views that have WHERE clauses in them to restrict the data that is retrieved, things like WHERE FISCAL_YEAR = 2009. If that's the case, the 2 statements are not looking at the same set of rows. Nora -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 2:14 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: DB2 Problem ERSHIST_X is a view of table CDERS_HISTORY. Tom Duerbusch THD Consulting Graves Nora E nora.e.gra...@irs.gov 6/16/2009 1:08 PM Well, I'm hoping you were updating the table names for security purposes, and that's the error below. In this example, the table you're using for the SELECT (ASN.ERSHIST_X) is not the same table that you specify in the DELETE (ASN.CDERS_HISTORY). Nora -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 1:38 PM To: IBMVM@LISTSERV.UARK.EDU Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible
Re: DB2 Problem
Perhaps. This is on VSE which doesn't have REXX SQL from IBM. However, we do have REXXSQL from SPR which does the same thing. BTW, the original program was written in REXXSQL. I tore everything out and put it in the DB2 Batch Utility to eliminate the possibility of a commit being accidently put in by the product developers. Tom Duerbusch THD Consulting Bill Pettit bi...@ormutual.com 6/16/2009 1:59 PM Do you have the REXX SQL option available to you? Bill -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 10:38 AM To: IBMVM@LISTSERV.UARK.EDU Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminating the process that is adding records, not to loose records in the merge/purge process? Thanks Tom Duerbusch THD Consulting
Re: DB2 Problem
Can you add another select statement with the same where clause as your select-insert roll, and write a temporary control file with each record containing enough information to identify the selected row, then after the select-insert roll read back the file and use it to do the deletes using rxsql? Bill -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 12:55 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: DB2 Problem Perhaps. This is on VSE which doesn't have REXX SQL from IBM. However, we do have REXXSQL from SPR which does the same thing. BTW, the original program was written in REXXSQL. I tore everything out and put it in the DB2 Batch Utility to eliminate the possibility of a commit being accidently put in by the product developers. Tom Duerbusch THD Consulting Bill Pettit bi...@ormutual.com 6/16/2009 1:59 PM Do you have the REXX SQL option available to you? Bill -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Tom Duerbusch Sent: Tuesday, June 16, 2009 10:38 AM To: IBMVM@LISTSERV.UARK.EDU Subject: DB2 Problem I don't believe that this is a DB2 Server code problem, just how I'm coding it, is a problem G. I have a table, that a process adds records to it. On an hourly basis, I kick off a job that copies all the records in that table, inserts them into another table, and then deletes all records in the first table, all within the same LUW. However, if the process that adds records to the table, is adding records during this merge/purge process, some records are deleted without being merged. I didn't think that was suppose to happen. After stripping out all the other code, and coding the remaining code in a DB2 Batch Utility step, I see that I do have a problem. I don't know if I'm confusing DB2, as the table I insert from, is a View. The table I delete from, is the real table. ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== lock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 === I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 === I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds records. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK, and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminating the process that is adding records, not to loose records in the merge/purge process? Thanks Tom Duerbusch THD Consulting
Re: DB2 Problem
On Tue, 16 Jun 2009 14:13:03 -0700, Bill Pettit bi...@ormutual.com wrot e: ARI0801I DBS Utility started: 06/16/09 10:29:35. AUTOCOMMIT = OFF ERRORMODE = OFF ISOLATION LEVEL = REPEATABLE READ -- CONNECT SYSA IDENTIFIED BY ; ARI8004I User SYSA connected to server STLDB01. ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- -- COMMENT 'PAYROLL SYSTEM' -- -- LOCK DBSPACE PERSHIST IN EXCLUSIVE MODE;=== l ock the dbspace of ershist_xx ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- INSERT INTO STL01.ERS_HISTORY_A --SELECT * FROM ASN.ERSHIST_X; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 3 == = I've inserted 3 records -- -- DELETE FROM ASN.CDERS_HISTORY -- ; ARI0501I An SQL warning has occurred. Database manager processing is completed. Warning may indicate a problem. ARI0505I SQLCODE = 0 SQLSTATE = 01504 ROWCOUNT = 30705 == = I'ved deleted 30705 records ARI0502I Following SQL warning conditions encountered: NULLWHERE -- COMMIT WORK; ARI0500I SQL processing was successful. ARI0505I SQLCODE = 0 SQLSTATE = 0 ROWCOUNT = 0 -- SET ERRORMODE OFF; ARI0899I ...Command ignored. -- ARI0802I End of command file input. ARI8997I ...Begin COMMIT processing. ARI0811I ...COMMIT of any database changes successful. ARI0809I ...No errors occurred during command processing. ARI0808I DBS processing completed: 06/16/09 10:30:31. I don't really have a good option for stopping the process that adds rec ords. 99.99% of the time, no records are added during the merge/purge process. However, if a batch job add/chg/deleted a lot of records in a signal LUW, as in 100,000 or more, it is possible that the merge/purge runs while records were still being added, which then I might loose some records. I thought locking the DBSPACE that I'm doing the merge/purge from, would do the trick. I thought that the process that was adding records, would be held on a LOCK , and wait (perhaps till -911, in which case it will delay and restart), but the lock didn't seem to do the trick. Between Repeatable Read and Locking the DBSPACE didn't do what I needed. Is there another option, without taking down the database to Single User Mode, or terminat ing the process that is adding records, not to loose records in the merge/purge process? Thanks Tom Duerbusch THD Consulting = == = You could add a FLAG, normally NULL, to mark the records you plan to ins ert and then delete. That would ignore the added records. UPDATE ASN.CDERS_HISTORY SET FLAG=1; INSERT INTO STL01.ERS_HISTORY_A SELECT * FROM ASN.ERSHIST_X WHERE FLAG=1; DELETE FROM ASN.CDERS_HISTORY WHERE FLAG=1; Alan Ackerman Alan (dot) Ackerman (at) Bank of America (dot) com
