OK, I've gone through all the links - lots of information...
I'm neck deep in begin atomic, isolattion levels, SQLCA, SQLCD, savepoints,
SQLCODE, etc. Unfortunately there are not many code samples out there. The
only one I found on infocenter was a C program with dynamic SQL (EXEC)
statements so control flow logic was in C.
I've got a procedure successfully wrapped in a transaction but I think I
have two problems:
1) I think I am wrapping too much in the transaction. I was able to get the
transaction started by setting the overall procedure isolation level to *CHG
but I have a lot of variable preparation (sub selections, select into. etc.)
that I don't need to have within the transaction scope. Currently I don't
think my savepoint is being used - maybe I'm wrong though.
2) I don't think my rollback is being hit within the procedure. If I
intentionally create a unique key violation in the last loop of the
procedure, I can see all the data that was inserted by prior loops by
connecting with an app (MS Access) that has an isolation level set to *NONE
(allows dirty reads). The rows don't go away until I close my procedure
connection - which seems to imply that my explicity rollback is not being
hit and I am using implicit rollback.
If anyone could provide a few SQL based examples to get me over the hump
quickly, that would be appreciated. Here is what I am trying so far (libs,
tables, etc. removed for privacy):
CREATE PROCEDURE LIB.SPINSERT_WITHTRAN (
IN @JOBNUMBER CHAR(12) ,
IN @USER VARCHAR(8) )
LANGUAGE SQL
SPECIFIC LIB.SPINSERT_WITHTRAN
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *CHG ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
--first, declare variables for control flow, etc.
DECLARE @DATEVAR CHAR ( 6 ) ;
DECLARE @COUNTER INT ;
DECLARE @MAXCOUNTER INT ;
DECLARE @GMAID CHAR ( 8 ) ;
DECLARE @MCSTYL CHAR ( 2 ) ;
DECLARE @CHARTNAME CHAR ( 3 ) ;
--next, variables for table values
DECLARE @GMCO CHAR ( 5 ) ;
DECLARE @GMOBJ CHAR ( 6 ) ;
DECLARE @GMSUB CHAR ( 8 ) ;
DECLARE @GMDL01 CHAR ( 30 ) ;
DECLARE @GMLDA CHAR ( 1 ) ;
DECLARE @GMPEC CHAR ( 1 ) ;
DECLARE @GMUM CHAR ( 2 ) ;
DECLARE @GMWCMP CHAR ( 4 ) ;
DECLARE @GMCCT CHAR ( 1 ) ;
DECLARE @GMR002 CHAR ( 3 ) ;
DECLARE @GMR003 CHAR ( 3 ) ;
DECLARE @GMR004 CHAR ( 3 ) ;
DECLARE @GMR001 CHAR ( 3 ) ;
DECLARE @GMSUBA CHAR ( 8 ) ;
DECLARE @GMERC CHAR ( 2 ) ;
DECLARE @GMBILL CHAR ( 1 ) ;
DECLARE @GMHTC CHAR ( 1 ) ;
DECLARE @GMQLDA CHAR ( 1 ) ;
DECLARE SQLCODE INTEGER DEFAULT 0 ;
DECLARE GLOBAL TEMPORARY TABLE SESSION . ACCOUNTLOOP (
AUTOID INTEGER GENERATED ALWAYS AS IDENTITY ,
GJSUB CHAR ( 8 ) ,
GJOBJ CHAR ( 6 ) ,
GJCTY CHAR ( 3 ) ,
GJDL01 CHAR ( 30 ) ,
GJLDA CHAR ( 1 ) ,
GJPEC CHAR ( 1 ) ,
GJUM CHAR ( 2 ) ,
GJWCMP CHAR ( 4 ) ,
GJRCC CHAR ( 8 ) ,
GJUCC DECIMAL ( 15 , 0 ) ,
GJUCT DECIMAL ( 15 , 0 ) ,
GJCCT CHAR ( 1 ) ,
GJR002 CHAR ( 3 ) ,
GJR003 CHAR ( 3 ) ,
GJR004 CHAR ( 3 ) ,
GJR001 CHAR ( 3 ) ,
GJSUBA CHAR ( 8 ) ,
GJERC CHAR ( 2 ) ,
GJBILL CHAR ( 1 ) ,
GJHTC CHAR ( 1 ) ,
GJQLDA CHAR ( 1 )
)
WITH REPLACE ;
SET @DATEVAR = ( SELECT '1' || RIGHT ( YEAR ( CURRENT_TIMESTAMP ) , 2 )
|| RIGHT ( '000' || DAYOFYEAR ( CURRENT_TIMESTAMP ) , 3 )
FROM SYSIBM . SYSDUMMY1 ) ;
SELECT MCCO , MCSTYL INTO @GMCO , @MCSTYL FROM LIB.TABLE WHERE MCMCU =
@JOBNUMBER ;
SET @CHARTNAME =
CASE
WHEN @MCSTYL IN ( 'BC' , 'BI' , 'BL' ) THEN 'BIA'
WHEN @MCSTYL = 'DE' THEN 'DEA'
WHEN @MCSTYL IN ( 'LR' , 'L2' ) THEN 'LCA'
WHEN @MCSTYL = 'NR' THEN 'NRE'
WHEN @MCSTYL = 'PK' THEN 'PKA'
WHEN @MCSTYL IN ( 'TR' , 'T2' ) THEN 'TIA'
WHEN @MCSTYL = 'T1' THEN 'TI1'
WHEN @MCSTYL = 'T9' THEN 'T99'
END ;
INSERT INTO SESSION . ACCOUNTLOOP ( GJSUB , GJOBJ , GJCTY , GJDL01 , GJLDA ,
GJPEC , GJUM , GJWCMP , GJRCC , GJUCC , GJUCT , GJCCT , GJR002 , GJR003 ,
GJR004 , GJR001 , GJSUBA , GJERC , GJBILL , GJHTC , GJQLDA )
SELECT
GJSUB , GJOBJ , GJCTY , GJDL01 , GJLDA , GJPEC , GJUM , GJWCMP , GJRCC ,
GJUCC , GJUCT , GJCCT , GJR002 , GJR003 , GJR004 , GJR001 , GJSUBA , GJERC ,
GJBILL , GJHTC , GJQLDA
FROM LIB2.TABLE2
WHERE GJCTY = @CHARTNAME ;
SET @COUNTER = ( SELECT MIN ( AUTOID ) FROM SESSION . ACCOUNTLOOP ) ;
SET @MAXCOUNTER = ( SELECT MAX ( AUTOID ) FROM SESSION . ACCOUNTLOOP ) ;
SET @GMAID = ( SELECT MAX ( GMAID ) + 1 FROM LIB.TABLE3 WHERE GMAID >=
'90000000' ) ; --get the next GMAID from within the transaction
/******!!!!!transaction begins below******/
SAVEPOINT BEFORE_UPDATES ON ROLLBACK RETAIN CURSORS; --beginning of
transaction...hopefully not opening transaction prior to here.
WHILE @COUNTER <= @MAXCOUNTER DO
SELECT GJOBJ , GJSUB , GJDL01 , GJLDA , GJPEC , GJUM , GJWCMP , GJCCT ,
GJR002 , GJR003 , GJR004 , GJR001 , GJSUBA , GJERC , GJBILL , GJHTC , GJQLDA
INTO @GMOBJ , @GMSUB , @GMDL01 , @GMLDA , @GMPEC , @GMUM , @GMWCMP , @GMCCT
, @GMR002 , @GMR003 , @GMR004 , @GMR001 , @GMSUBA , @GMERC , @GMBILL ,
@GMHTC , @GMQLDA
FROM SESSION . ACCOUNTLOOP
WHERE AUTOID = @COUNTER ;
INSERT INTO LIB.TABLE3
(
GMCO , GMAID , GMMCU , GMOBJ , GMSUB , GMANS , GMDL01 , GMLDA , GMBPC ,
GMPEC , GMBILL , GMCRCD , GMUM , GMR001 , GMR002 , GMR003 , GMR004 , GMR005
, GMR006 , GMR007 , GMR008 , GMR009 , GMR010 , GMR011 , GMR012 , GMR013 ,
GMR014 , GMR015 , GMR016 , GMR017 , GMR018 , GMR019 , GMR020 , GMR021 ,
GMR022 , GMR023 , GMOBJA ,
GMSUBA , GMWCMP , GMCCT , GMERC , GMHTC , GMQLDA , GMCCC , GMFMOD , GMUSER ,
GMPID , GMJOBN , GMUPMJ , GMUPMT
)
VALUES
(
@GMCO , @GMAID , @JOBNUMBER , @GMOBJ , @GMSUB , '' , @GMDL01 , @GMLDA , '' ,
@GMPEC , @GMBILL , '' , @GMUM , @GMR001 , @GMR002 , @GMR003 , @GMR004 , '' ,
'' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' ,
'' , '' , '' , '' ,
@GMSUBA , @GMWCMP , @GMCCT , @GMERC , @GMHTC , @GMQLDA , '' , '' , @USER ,
'JCC' , 'HIWAPP' , @DATEVAR , 0
) ;
IF SQLCODE <> 0 THEN
BEGIN
ROLLBACK TO SAVEPOINT BEFORE_UPDATES ;
END;
END IF;
--Insert should be done. Setup for the next loop
SET @GMAID = @GMAID + 1 ; --get next account id number
SET @COUNTER = @COUNTER + 1 ; --for next loop
END WHILE ;
--if we get out of the loop without error, let's commit all changes
COMMIT;
END ;
"Luis Rodriguez" <luisro58@xxxxxxxxx> wrote in
message
news:i2k902b36c41004190838r57a7260aj5882f9ceb0dccd56@xxxxxxxxxxxxxxxxx
Ryan,
As a (I suppose) newcomer to DB2 for the IBM i, I would recommend you the
DB2 Database programming Manual, which you can find here:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/dbp/rbafo.pdf
Also, there is a nice book about commiment control here:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzakj/rzakj.pdf
As in almost all the things related the IBM i, Google and the Infocenter are
your friends. The Infocenter link for V5R4 is:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp
HTH,
Luis Rodriguez
IBM Certified Systems Expert ? eServer i5 iSeries
On Mon, Apr 19, 2010 at 8:12 AM, Ryan Hunt
<ryan.hunt@xxxxxxxxxxxxx> wrote:
When I say transaction management, I mean:
Start a unit of work-->perform work-->check for errors (if none
found)-->commit work (else, rollback to start).
I'm hoping the list can provide me with two things.
1) a good reference for reading the details of transaction management on
DB2/400. I started doing some research and found lots on isolation levels
-
but that seems more about isolation of reads (whether reads can be dirty,
etc.) rather than enforcing change control. (please note that I am
primarily an MS SQL DBA/Developer so I may have gleaned something
incorrect
from my reading).
2) a quick primer to get me started, for instance, the real code to
perform
this:
BEGIN TRANSACTION
UPDATE table1
set foo = x, bar = y
WHERE col1 = Z;
IF NO ERROR RETURNED THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
Thanks much. RH
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
midrange.com
