Results 1 to 11 of 11

Thread: sql procedure

  1. #1
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    Unanswered: sql procedure

    hI PEOPLE,

    I have a litle problem...i don't know why but just the table(SALE_MD) is loaded with 100 000 rows...waht's the problem with the second cursor?
    Thanks,

    rui


    CREATE PROCEDURE INT_SALE_E_SALE_MD ()
    SPECIFIC INT_SALE_E_SALE_MD
    LANGUAGE SQL
    ---------------------------------------------------------------
    -- SQL Stored Procedure
    ---------------------------------------------------------------
    P1: BEGIN
    -- Declaraçao de Variaveis

    DECLARE v_tpos_md_val DECIMAL;
    DECLARE v_clr_md_val DECIMAL;
    DECLARE v_tpos_md_val_eur DECIMAL;
    DECLARE v_clr_md_val_eur DECIMAL;
    DECLARE v_str_id SMALLINT;
    DECLARE v_yr_wk_id INTEGER;
    DECLARE v_sku_id INTEGER;
    DECLARE v_ts_val DECIMAL;
    DECLARE v_ts_qty INTEGER;
    DECLARE v_ts_val_eur DECIMAL;
    DECLARE v_base_ccy CHARACTER(3);
    DECLARE v_cont INTEGER DEFAULT 0;
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';


    -- declaração dos cursores

    DECLARE c1 CURSOR WITH HOLD FOR
    SELECT
    sl.tpos_md_val,
    sl.clr_md_val,
    sl.tpos_md_val_eur,
    sl.clr_md_val_eur
    FROM basedett.sls sl
    WHERE sl.co_id = 1
    FOR READ ONLY;

    DECLARE c2 CURSOR WITH HOLD FOR
    SELECT
    sl.str_id,
    sl.yr_wk_id,
    sl.sku_id,
    sl.ts_val,
    sl.ts_qty,
    sl.ts_val_eur,
    sl.base_ccy
    FROM basedett.sls sl
    WHERE sl.co_id = 1
    FOR READ ONLY;


    -- Exception Handlers
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;


    -- Criaçao da tabela SALE

    CREATE TABLE WORKTMPT.SALE ("SKU_ID" INTEGER , "STR_ID" SMALLINT ,
    "YR_WK_ID" INTEGER , "TS_QTY" INTEGER , "TS_VAL" DECIMAL (9, 0) ,
    "TS_VAL_EUR" DECIMAL (11, 0) , "FL_PRC_SLS_QTY" INTEGER , "FL_PRC_SLS_VAL" DECIMAL (9, 0) ,
    "FL_PRC_SLS_VAL_EUR" DECIMAL (11, 0) , "CLR_MD_SLS_QTY" INTEGER, "CLR_MD_SLS_VAL" DECIMAL(9,0),
    "CLR_MD_SLS_VAL_EUR" DECIMAL(11,0),"TPOS_SLS_QTY" INTEGER,"TPOS_SLS_VAL" DECIMAL (9,0),
    "TPOS_SLS_VAL_EUR" DECIMAL (11,0),"TS_XTX_VAL" DECIMAL(9,0),"TS_XTX_VAL_EUR" DECIMAL(11,0),
    "BASE_CCY" CHARACTER(3),"WK_FIRST_SOLD" INTEGER, "ROW_STATUS" CHARACTER(1), "RATE_OF_SALE" DECIMAL(9,0),
    "AVAIL_FROM_DT" DATE)
    IN USERSPACE1;

    -- Criação da tabela SALE_MD

    CREATE TABLE WORKTMPT.SALE_MD(
    "SKU_ID" INTEGER ,
    "STR_ID" SMALLINT ,
    "YR_WK_ID" INTEGER,
    "CLR_MD_VAL" DECIMAL(9,2),
    "CLR_MD_VAL_EUR" DECIMAL(11,2),
    "PERM_MU_VAL" DECIMAL(9,2),
    "PERM_MU_VAL_EUR" DECIMAL(11,2),
    "NLP_MD_QTY" SMALLINT,
    "NLP_MD_VAL" DECIMAL(9,2),
    "NLP_MD_VAL_EUR" DECIMAL(11,2),
    "POS_MD_QTY" INTEGER,
    "POS_MD_VAL" DECIMAL(9,2),
    "POS_MD_VAL_EUR" DECIMAL(11,2),
    "NLP_ADJ_VAL" DECIMAL(9,2),
    "NLP_ADJ_VAL_EUR" DECIMAL(11,2),
    "TPOS_MD_QTY" INTEGER,
    "TPOS_MD_VAL" DECIMAL(9,2),
    "TPOS_MD_VAL_EUR" DECIMAL(11,2),
    "TOT_MD_QTY" INTEGER,
    "TOT_MD_VAL" DECIMAL(9,2),
    "TOT_MD_VAL_EUR" DECIMAL(11,2),
    "VCHR_MD_QTY" SMALLINT,
    "VCHR_MD_VAL" DECIMAL(9,2),
    "VCHR_MD_VAL_EUR" DECIMAL(11,2),
    "TOT_MD_XTX_VAL" DECIMAL(9,2),
    "TOT_MD_XTX_VAL_EUR" DECIMAL(11,2),
    "POS_EQV_MD_VAL" DECIMAL(9,2),
    "POS_EQV_MD_VAL_EUR" DECIMAL(11,2),
    "LCL_MD_QTY" INTEGER ,
    "LCL_MD_VAL" DECIMAL(9,2),
    "LCL_MD_VAL_EUR" DECIMAL(11,2),
    "DMG_ALW_QTY" SMALLINT,
    "DMG_ALW_VAL" DECIMAL(9,2) ,
    "DMG_ALW_VAL_EUR" DECIMAL(11,2) ,
    "DMG_WO_QTY" SMALLINT ,
    "DMG_WO_VAL" DECIMAL(9,2) ,
    "DMG_WO_VAL_EUR" DECIMAL(11,2) ,
    "FLTY_ALW_QTY" SMALLINT ,
    "FLTY_ALW_VAL" DECIMAL(9,2) ,
    "FLTY_ALW_VAL_EUR" DECIMAL(11,2) ,
    "FLTY_WO_QTY" SMALLINT ,
    "FLTY_WO_VAL" DECIMAL(9,2) ,
    "FLTY_WO_VAL_EUR" DECIMAL(11,2) ,
    "GDWL_ALW_QTY" SMALLINT ,
    "GDWL_ALW_VAL" DECIMAL(9,2) ,
    "GDWL_ALW_VAL_EUR" DECIMAL(11,2) ,
    "GDWL_WO_QTY" SMALLINT ,
    "GDWL_WO_VAL" DECIMAL(9,2) ,
    "GDWL_WO_VAL_EUR" DECIMAL(11,2) ,
    "SYS_STK_ADJ_VAL" DECIMAL(9,2) ,
    "SYS_STK_ADJ_VAL_EUR" DECIMAL(11,2) ,
    "FLTY_QTY" SMALLINT ,
    "FLTY_VAL" DECIMAL(9,2) ,
    "FLTY_VAL_EUR" DECIMAL(11,2) ,
    "GDWL_QTY" SMALLINT ,
    "GDWL_VAL" DECIMAL(9,2) ,
    "GDWL_VAL_EUR" DECIMAL(11,2) ,
    "DMG_QTY" SMALLINT ,
    "DMG_VAL" DECIMAL(9,2) ,
    "DMG_VAL_EUR" DECIMAL(11,2) ,
    "FDG_QTY" SMALLINT ,
    "FDG_VAL" DECIMAL(9,2) ,
    "FDG_VAL_EUR" DECIMAL(11,2) ,
    "BASE_CCY" CHAR(3) ,
    "ROW_STATUS" CHAR(1) ,
    "AVAIL_FROM_DT" DATE)
    IN USERSPACE1 ;

    -- Program Core
    -- Inserir dados da tabela BASEDETT.SLS para a tabela WORKTMPT.SALE_MD
    OPEN c1;
    FETCH c1 INTO v_sku_id,v_str_id,v_tpos_md_val, v_clr_md_val, v_tpos_md_val_eur,
    v_clr_md_val_eur;
    WHILE at_end = 0 DO
    INSERT INTO worktmpt.sale_md
    VALUES (1,222,222,v_clr_md_val,v_clr_md_val_eur,444,
    444,444,444,444,444,444,444,444,444,444,v_tpos_md_ val,
    v_tpos_md_val_eur,444,444,444,444,444,
    444,444,444,444,444,
    444,444,444,444,444,444,444,
    444,0,0,444,444,444,444,0,0,
    444,444,444,444,0,0,444,
    444,444,444,444,444,444,444,
    444,444,444,444,444,'gbp','p','2003-12-10');
    SET v_cont = v_cont + 1;
    IF v_cont > 5000 THEN
    COMMIT;
    SET v_cont = 0;
    END IF;
    FETCH c1 INTO v_tpos_md_val, v_clr_md_val, v_tpos_md_val_eur, v_clr_md_val_eur;
    END WHILE;
    CLOSE c1;

    -- Inserir dados da tabela BASEDETT.SLS para a tabela WORKTMPT.SALE
    OPEN c2;
    FETCH c2 INTO v_str_id,v_yr_wk_id,v_sku_id,v_ts_val,v_ts_qty,v_t s_val_eur,v_base_ccy;
    WHILE at_end = 0 DO
    INSERT INTO worktmpt.sale
    VALUES(v_sku_id,v_str_id,333,v_ts_qty,v_ts_val,v_t s_val_eur,999,999,999,999,999,999,999,
    999,999,999,999,v_base_ccy,999,'u',999,'2003-12-10');
    SET v_cont = v_cont + 1;
    IF v_cont > 5000 THEN
    COMMIT;
    SET v_cont = 0;
    END IF;
    FETCH c2 INTO v_str_id,v_yr_wk_id,v_sku_id,v_ts_val,v_ts_qty,v_t s_val_eur,v_base_ccy;
    END WHILE;
    CLOSE c2;
    COMMIT;

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to SET AT_END = 0 between the two FETCH loops.

    Andy

  3. #3
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    sql PROCEDURE

    Thanks andy.

    Originally posted by ARWinner
    You need to SET AT_END = 0 between the two FETCH loops.

    Andy

  4. #4
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    sql optimization

    How can i improve the performance and the run time....
    could i use the method of query parallelism to process the inserts statment in parallel?
    If is possible..how can i do that?

    Thanks,
    Rui

    Originally posted by ARWinner
    You need to SET AT_END = 0 between the two FETCH loops.

    Andy

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: sql optimization

    Rui,
    You can try creating the tables with the NOT LOGGED INITIALLY
    clause, remove the commits, and put only one commit at the end.
    With this method you can also rewrite the FETCH loops to do
    INSERT INTO TABLE SELECT ...

    HTH

    Andy


    Originally posted by rmleite
    How can i improve the performance and the run time....
    could i use the method of query parallelism to process the inserts statment in parallel?
    If is possible..how can i do that?

    Thanks,
    Rui

  6. #6
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    sql procedure

    ...How can i create a table with NOT LOGGED INITIALLLY?...
    ...like that?

    CREATE TABLE RMLEITE.TESTE2 WITH NOT LOGGED INITIALLY(
    "ID2" INTEGER,
    "DESC" CHAR(25),
    "STYLE_ID" INTEGER NOT NULL,
    "COLOUR_ID" INTEGER NOT NULL,
    PRIMARY KEY (STYLE_ID,COLOUR_ID))
    IN TESTES;

    thanks,
    Rui

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: sql procedure

    Rui,

    Close...

    CREATE TABLE RMLEITE.TESTE2 (
    "ID2" INTEGER,
    "DESC" CHAR(25),
    "STYLE_ID" INTEGER NOT NULL,
    "COLOUR_ID" INTEGER NOT NULL,
    PRIMARY KEY (STYLE_ID,COLOUR_ID))
    IN TESTES NOT LOGGED INITIALLY;


    Andy

    Originally posted by rmleite
    ...How can i create a table with NOT LOGGED INITIALLLY?...
    ...like that?

    CREATE TABLE RMLEITE.TESTE2 WITH NOT LOGGED INITIALLY(
    "ID2" INTEGER,
    "DESC" CHAR(25),
    "STYLE_ID" INTEGER NOT NULL,
    "COLOUR_ID" INTEGER NOT NULL,
    PRIMARY KEY (STYLE_ID,COLOUR_ID))
    IN TESTES;

    thanks,
    Rui

  8. #8
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    sql procedure

    Thanks Andy.
    Don't you think that it's dangerous for the performance..when you use NOT LOGGED INITIALLY whithout put COMMITs(just one at the end) when you have to load 500 000 rows!

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: sql procedure

    Rui,
    What NOT LOGGED INITIALLY does is that any work done on the table (insert, update, delete) in the same unit of work (UOW) as the creation of the table is not logged. This is a tremendous boost to performance. Think of it this way. On normal inserts, updates, and deletions DB2 has to write the operations twice, once in the log (in case the UOW needs to be backed out) and once in the table. By not writing all those inserts to the log tables (along with the overhead of maintaining the logs themselves) performance will increase. And in my experience it is a dramatic increase. As soon as the UOW of creating the table and all the inserts are completed (the commit at the end) the table will automatically revert to normal mode with all changes now being logged.

    Because you create and then load the data each time you call the SP, NOT LOGGED INITIALLY makes tremendous sense since you do not care about the data until after the inserts are completed. It it failed, for whatever reason, now you no longer have to rollback the changes. Just create it again and load it again.

    Andy


    Originally posted by rmleite
    Thanks Andy.
    Don't you think that it's dangerous for the performance..when you use NOT LOGGED INITIALLY whithout put COMMITs(just one at the end) when you have to load 500 000 rows!

  10. #10
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    Re: sql procedure

    Thanks Andy for your help,
    i think that this way is logic...and i'll try!

    Originally posted by ARWinner
    Rui,
    What NOT LOGGED INITIALLY does is that any work done on the table (insert, update, delete) in the same unit of work (UOW) as the creation of the table is not logged. This is a tremendous boost to performance. Think of it this way. On normal inserts, updates, and deletions DB2 has to write the operations twice, once in the log (in case the UOW needs to be backed out) and once in the table. By not writing all those inserts to the log tables (along with the overhead of maintaining the logs themselves) performance will increase. And in my experience it is a dramatic increase. As soon as the UOW of creating the table and all the inserts are completed (the commit at the end) the table will automatically revert to normal mode with all changes now being logged.

    Because you create and then load the data each time you call the SP, NOT LOGGED INITIALLY makes tremendous sense since you do not care about the data until after the inserts are completed. It it failed, for whatever reason, now you no longer have to rollback the changes. Just create it again and load it again.

    Andy

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: sql procedure

    Remember, ROLLFORWARD may leave your tables in an unusable state , ie, if you take a backup, say 10 pm and at 1030 pm you do a NLI as above, and at 9 am next morning you decide to restore and rollforward the data as it was at 4 am , then, you will be restoring the database from the 10 pm backup and rolling forward the logs ... In this scenario your data will become inaccessible ...

    But if you can recreate the data, it is absolutely safe using NLI ...

    Cheers

    Sathyaram


    Originally posted by rmleite
    Thanks Andy for your help,
    i think that this way is logic...and i'll try!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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