Transaction Log Full and Stored Procedure

[harborboy76]

Hi,
I have a stored procedure that does a lot of INSERT/UDATE to 3
tables. And When I call the stored procedure, I get a Transaction Log
Full error. When I want to do is turning off the transaction log on
those 3 tables that the stored procedure is using.
Now, since I call the stored procedure on the command line (CLI),
where do I run ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY statement
? Do I have to put them inside the stored procedure ? Or Do I run the
command before I call the SP ? I am on Linux, DB2 8.2
We don't want to configure the logs to make it bigger because this
is just a one time run to populate the tables. And we have to use the
procedure to migrate those data.

Thanks for any inputs/advices
N.

[shenanwei]

from command line is also simple
db2 -c- alter table
db2 -c- call sp
db2 commit

[harborboy76]

Hi All,
I just want to add some more information on the subject. Basically
my Stored Procedure looks like this :

CREATE PROCEDURE DB.PWC (V_ID CHAR(6), OUT RETURN_VAL INT)
LANGUAGE SQL

BEGIN
DECLARE..
DECLARE..

INSERT INTO TABLE A..
UPDATE TABLE A..

[Ian]

ha*********@gmail.com wrote:

Hi,
I have a stored procedure that does a lot of INSERT/UDATE to 3
tables. And When I call the stored procedure, I get a Transaction Log
Full error. When I want to do is turning off the transaction log on
those 3 tables that the stored procedure is using.
Now, since I call the stored procedure on the command line (CLI),
where do I run ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY statement
? Do I have to put them inside the stored procedure ? Or Do I run the
command before I call the SP ? I am on Linux, DB2 8.2
We don't want to configure the logs to make it bigger because this
is just a one time run to populate the tables. And we have to use the
procedure to migrate those data.

Can you add commits into your stored procedure?
Otherwise,

db2 +c "alter table ... activate not logged initially"
db2 +c "call ..."
db2 commit
The +c option turns disables auto-commit.

[harborboy76]

Hi Ian,
Thank you for the reply. I have been trying to figure out
how to do a COMMIT inside the stored procedure, but I
don't think it can be easily done. Anytime I add a COMMIT
in the procedure, I got the error saying something about
the cursot not opened...
Do you think my problem was because I called the stored
procedure without "+c" in front of my CALL statement ?

Thanks,
N

[hikums]

Can you try if this works?

CREATE PROCEDURE DB.PWC (V_ID CHAR(6), OUT RETURN_VAL INT)
LANGUAGE SQL
P1:BEGIN
BEGIN
DECLARE..
DECLARE..
INSERT INTO TABLE A..
UPDATE TABLE A..
END;
.BEGIN
DECLARE..
DECLARE..

INSERT INTO TABLE B..
UPDATE TABLE B..
END;
BEGIN
. DECLARE..
DECLARE..

INSERT INTO TABLE C..
UPDATE TABLE C..

END;
END P1

[Serge Rielau]

ha*********@gmail.com wrote:

Hi Ian,
Thank you for the reply. I have been trying to figure out
how to do a COMMIT inside the stored procedure, but I
don't think it can be easily done. Anytime I add a COMMIT
in the procedure, I got the error saying something about
the cursot not opened...
Do you think my problem was because I called the stored
procedure without "+c" in front of my CALL statement ?

Thanks,
N

DECLARE your cursor WITH HOLD.
By default cursors get closed on COMMIT. WITH HOLD suspends this behavior.

Also you can place the ALTER into the procedure.
Try: EXECUTE IMMEDIATE ALTER ....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[harborboy76]

Hi Serge,
Thanks for the tips.
I tried to add EXECUTE IMMEDIATE ALTER TABLE DB.A ACTIVATE NOT
LOGGED INITIALLY
in my procedure. But it failed to create. Am I not putting it in the
right place ? Or is my syntax incorrect ?
Can I activate not logged for more than 1 table at a time ?
CREATE PROCEDURE DB.PWC (V_ID CHAR(6), OUT RETURN_VAL INT)
LANGUAGE SQL
BEGIN

EXECUTE IMMEDIATE ALTER TABLE DB.A ACTIVATE NOT LOGGED INITIALLY

DECLARE..
DECLARE..
INSERT INTO TABLE A..
UPDATE TABLE A..

[Serge Rielau]

ha*********@gmail.com wrote:

Hi Serge,
Thanks for the tips.
I tried to add EXECUTE IMMEDIATE ALTER TABLE DB.A ACTIVATE NOT
LOGGED INITIALLY
in my procedure. But it failed to create. Am I not putting it in the
right place ? Or is my syntax incorrect ?
Can I activate not logged for more than 1 table at a time ?
CREATE PROCEDURE DB.PWC (V_ID CHAR(6), OUT RETURN_VAL INT)
LANGUAGE SQL
BEGIN

EXECUTE IMMEDIATE ALTER TABLE DB.A ACTIVATE NOT LOGGED INITIALLY

DECLARE..
DECLARE..
INSERT INTO TABLE A..
UPDATE TABLE A..
.
.
INSERT INTO TABLE B..
UPDATE TABLE B..
.
.
INSERT INTO TABLE C..
UPDATE TABLE C..
END

Try
EXECUTE IMMEDIATE 'ALTER TABLE ...';
If that does not work:
SET txt = 'ALTER TABLE ...';
EXECUTE IMMEDIATE txt;
will work for sure.. I do it rarely myself.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Matt Emmerton]

<ha*********@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Hi Serge,
Thanks for the tips.
I tried to add EXECUTE IMMEDIATE ALTER TABLE DB.A ACTIVATE NOT
LOGGED INITIALLY
in my procedure. But it failed to create. Am I not putting it in the
right place ? Or is my syntax incorrect ?

It needs to come after the DECLARE statements but before your insert/update
queries.

--
Matt Emmerton

[harborboy76]

Hi, Serge
Here is an example of our code. Basically, we manually do the
looping
and set our variables and use those variables to INSERT/UPDATE into the

underlying tables. I'm interested in using CURSOR WITH HOLD. But I
guess
I'm not very experienced. I tried to use EXECUTE IMMEDIATE. It built
and ran, but I got some other errors saying that the table is not
accessible (can't insert/update). It was very strange. I think the
safest way is to
try to commit every n rows, so that the transaction log is not filled
up.
I'm looping through around 1 million rows from TABLE1 into variables,
and
use them to populate 3 tables accordingly. Maybe my code is not very
efficient. Do you have any advice on how to work around this
transaction log ? We do not want to increase our Log Size because this
is a migration script. It will be run only once in a blue moon.

Thanks,

CREATE PROCEDURE db2.pkc (v_in_proc_id CHAR(6),
OUT RETURN_VAL INTEGER )

LANGUAGE SQL

BEGIN

DECLARE ...;
DECLARE ...;
DECLARE ...;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN
INSERT INTO TABLE (..) VALUES (..);
END;
FOR DUMMY_LOOP AS SELECT
COL1,
COL2,
COL3
FROM TABLE1

DO

SET v_1 = COL1;
SET v_2 = COL2;
SET v_3 = COL3;

BEGIN

BEGIN

DECLARE continue HANDLER FOR dup_key
BEGIN

UPDATE TABLE_A
SET COL_A = ..

END;

INSERT INTO TABLE_A VALUES ( .. );

END ;

BEGIN

DECLARE continue HANDLER FOR dup_key
BEGIN

UPDATE TABLE_B
SET COL_B = ..

END;

INSERT INTO TABLE_B VALUES ( .. );

[Serge Rielau]

ha*********@yahoo.com wrote:

Hi, Serge
Here is an example of our code. Basically, we manually do the
looping
and set our variables and use those variables to INSERT/UPDATE into the

underlying tables. I'm interested in using CURSOR WITH HOLD. But I
guess
I'm not very experienced. I tried to use EXECUTE IMMEDIATE. It built
and ran, but I got some other errors saying that the table is not
accessible (can't insert/update). It was very strange. I think the
safest way is to
try to commit every n rows, so that the transaction log is not filled
up.
I'm looping through around 1 million rows from TABLE1 into variables,
and
use them to populate 3 tables accordingly. Maybe my code is not very
efficient. Do you have any advice on how to work around this
transaction log ? We do not want to increase our Log Size because this
is a migration script. It will be run only once in a blue moon.

Thanks,

CREATE PROCEDURE db2.pkc (v_in_proc_id CHAR(6),
OUT RETURN_VAL INTEGER )

LANGUAGE SQL

BEGIN

DECLARE ...;
DECLARE ...;
DECLARE ...;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN
INSERT INTO TABLE (..) VALUES (..);
END;
FOR DUMMY_LOOP AS SELECT
COL1,
COL2,
COL3
FROM TABLE1

DO

SET v_1 = COL1;
SET v_2 = COL2;
SET v_3 = COL3;

BEGIN

BEGIN

DECLARE continue HANDLER FOR dup_key
BEGIN

UPDATE TABLE_A
SET COL_A = ..

END;

INSERT INTO TABLE_A VALUES ( .. );

END ;

BEGIN

DECLARE continue HANDLER FOR dup_key
BEGIN

UPDATE TABLE_B
SET COL_B = ..

END;

INSERT INTO TABLE_B VALUES ( .. );

.
.
.
END

This isn't what I thought you did... I thought you picked teh tabel name
from the FOR loop itself....
First of: Good going with the nested handlers! Nice to see someone use
neste compounds as they are meant to be used.

You can simply the code quite a bit by using MERGE.

BEGIN
FOR THIS AS dummy CURSOR WITH HOLD
FOR SELECT PK1, COL1, PK2, COL2, PK3, COL3 FROM TABLE1
DO
MERGE INTO TABLEA AS T
USING (VALUES(THIS.PK1, THIS.COL1)) AS S(PK, C1)
ON T.PK = S.PK
WHEN MATCHED THEN UPDATE SET T.C1 = S.C1;
WHEN NOT MATCHED THEN INSERT VALUES (S.PK, S.C1);

MERGE .... ;
COMMIT;
END FOR;
END

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab