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*********@gma il.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*********@gma il.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*********@gma il.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*********@gm ail.com> wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.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