Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2012
    Posts
    5

    Unanswered: help with logspace full

    Hello everybody...I am new to this site but heard from my colleague dbas that it is very useful for people who are learning how db2 works. I have been given a task to update 20 million records in a table in my database. I wrote a small script to do this task but it filled up the filesystem and crashed the db . I am trying to commit every 500 records.. the filesystem to the logs is 10 gigs. My database is circular logging.

    here are the db cfgs

    Log file size (4KB) (LOGFILSIZ) = 8196
    Number of primary log files (LOGPRIMARY) = 10
    Number of secondary log files (LOGSECOND) = 245

    and here is the script


    CREATE PROCEDURE db2inst1.Update_test ( )
    SPECIFIC db2inst1.upd
    LANGUAGE SQL
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    EXTERNAL ACTION
    OLD SAVEPOINT LEVEL
    MODIFIES SQL DATA
    INHERIT SPECIAL REGISTERS
    BEGIN


    DECLARE loop INTEGER;
    DECLARE count INTEGER;

    set loop = 500;
    SET count=1;



    while count <= loop

    do

    UPDATE db2inst1.cust_records SET testnumber = '9999999999';

    commit;

    set count = count + 1;

    end while;


    END;
    Can somebody tell me a better way of writing this
    Thanks

    Wannabdba

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    As there is no WHERE clause in your UPDATE statement,
    all rows will be updated (and logged) by a single execution of the update statement.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I am new to this site..
    yes, you are very to db2 also..
    the best would be to read some basics about db2/logging....
    DB2 Database for Linux, UNIX, and Windows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Feb 2012
    Posts
    5
    Yes you are right I need to update the whole table

  5. #5
    Join Date
    Feb 2012
    Posts
    5
    So now I added more space to the filesystem and now I get the below error

    RETCODE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE
    "Log File has reached its saturation point"
    DIA8309C Log file was full.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It might be necessary to devide update rows and to commit after every update operations.

    Here is an example of such partial update with commit.
    (Not tested. Some additional code might be necessary.)
    Code:
    SET loop       =  nnnnn;
    SET updt_count =  0;
    SET saved_key  = -1; /* assumed <primary key> is a number and not negative. */
    
    REPEAT
    
       SELECT MAX(<primary_key>)
            , COUNT(*)
        INTO  saved_key
            , updt_count
        FROM  FINAL TABLE(
              UPDATE (SELECT testnumber
                           , ROW_NUMBER()
                                OVER( ORDER BY <primary_key> ) AS rnum
                       FROM  db2inst1.cust_records
                       WHERE <primary_key> > saved_key
                     ) t
                 SET testnumber = '9999999999'
               WHERE rnum <= loop
              ) u
       ;
    
       COMMIT;
    
       UNTIL updt_count <= 0
    END REPEAT;
    Last edited by tonkuma; 02-16-12 at 23:01. Reason: Reformat sample code(separate lines of updt_count in INTO clause). Change WHILE to REPEAT.

  7. #7
    Join Date
    Feb 2012
    Posts
    5
    I increased the logfilgsz and now running a stored procedure that I wrote , it is painfully slow although

    drop specific procedure update_TEST
    @

    create procedure update_TEST (OUT d_rows bigint)
    LANGUAGE SQL
    SPECIFIC update_TEST

    cd: BEGIN

    DEClARE SQLSTATE char(5) DEFAULT '00000';
    DECLARE rows_updated INT DEFAULT 1;
    DECLARE v_tmp_rows INT DEFAULT 1;
    DECLARE v_pglobal_id varchar(64) DEFAULT '';

    DECLARE c_fetch CURSOR WITH HOLD FOR
    SELECT 1 from db2inst1.cust_records
    FOR UPDATE;

    OPEN c_fetch;

    SET d_rows = 0;

    FETCH FROM c_fetch into v_pglobal_id;

    WHILE ( SQLSTATE = '00000' )
    DO

    UPDATE db2inst1.cust_records SET testnumber = '9999999999' where current of c_fetch;

    GET DIAGNOSTICS rows_updated = ROW_COUNT;

    SET v_tmp_rows = v_tmp_rows + rows_updated;

    IF v_tmp_rows > 500
    THEN
    COMMIT;
    SET v_tmp_rows =0;
    END IF;

    SET d_rows = d_rows + rows_updated;

    FETCH FROM c_fetch into v_pglobal_id;
    END WHILE;

    CLOSE c_fetch WITH RELEASE;

    END cd
    @

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That's a good start, but I would make the following changes:
    1. I think you need one final commit outside the loop at the end of the SP.
    2. Define the cursor as "SELECT ....... WITH UR" (instead of FOR UPDATE) and then when you fetch the row, save the PK of the row in a variable, and then do a seperate UPDATE on the row using the variable containing the PK, instead of UPDATE WHERE CURRENT OF CURSOR.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... it is painfully slow although
    Because, you updated each row one by one, by specifying "... where current of c_fetch".

    So, try to bulk update like my example.

  10. #10
    Join Date
    Feb 2012
    Posts
    5
    Hi guys,

    due to change in requirements I rewrote the stored procedures...but this is updating only some rows. for example

    Value of output parameters
    --------------------------
    Parameter Name : SQLRET
    Parameter Value : 100

    Parameter Name : TOKENSTR
    Parameter Value :

    Parameter Name : MESSTXT
    Parameter Value : SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

    Parameter Name : FINALCOUNT
    Parameter Value : 7220589

    Parameter Name : UPDATEMESSAGE
    Parameter Value : -

    db2 "select count(*) from db2inst1.cust_records where test1 <> 'none'"

    1
    -----------
    7220589

    wheras the rowcount of the table is close to 20 million records

    here is my stored procedure



    CREATE PROCEDURE db2inst1.updatetest ( IN UOW INTEGER
    , out sqlret integer
    , out tokenstr varchar(1000)
    , out messtxt varchar(2000)
    , out finalcount integer
    , out updatemessage VARCHAR(30))
    )

    SPECIFIC db2inst1.updatetest


    P1: BEGIN

    Declare counter integer;
    declare row_counter integer default 0 ;
    Declare loop INTEGER;
    Declare MINID integer;
    Declare maxid integer ;
    Declare updatecounter integer;

    declare sqlcode integer default 0 ;
    declare sqlstate char(05) default '00000' ;


    declare exit handler for sqlexception,sqlwarning
    begin
    get diagnostics exception 1 tokenstr = db2_token_string
    , messtxt = message_text ;
    set sqlret = sqlcode ;
    end ;
    P2: begin

    declare exit handler for not found
    begin
    get diagnostics exception 1 tokenstr = db2_token_string
    , messtxt = message_text ;
    set sqlret = sqlcode ;

    end;

    set sqlret = 0 ;
    set tokenstr = 'Complete';
    set messtxt = 'Success' ;
    set updatecounter = 0;



    set maxid = ( select max(id) from db2inst1.cust_records );
    set minid = ( select min(id) from db2inst1.cust_records );


    set loop = ceiling((decimal(maxid) - decimal(minid - 1) )/UOW );

    set counter = 1;

    if sqlret <> 0 then
    return sqlret ;
    end if ;


    while counter <= loop do

    update db2inst1.cust_records c
    SET test1 = 'none', testnumber='9999999999'
    where exists ( select 1 from db2inst1.cust_records b
    where c.id = b.id
    and c.id between minid + (UOW * (counter - 1))
    and minid + ( UOW * counter )
    and not exists ( select 1 from db2inst1.cust_records b
    where c.id = b.id
    and c.test1 = 'none'
    and c.testnumber='9999999999')
    ) ;

    get diagnostics row_counter = numrows ;
    set updatecounter = updatecounter + row_counter ;
    set counter = counter + 1;
    commit ;
    end while;


    END P2 ;

    set count= (select count(*) from db2inst1.cust_records
    where test1 = 'none');

    if finalcount = 0 then
    SET UPDATEMESSAGE='No more rows to update : ' || finalcount;
    end if;
    commit ;
    return sqlcode ;
    END P1
    @

    Can you tell where am I going wrong please


    thanks

    wannabdba

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •