Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Nov 2010
    Posts
    99

    Unanswered: Fetch into cursor slow

    In my SQL stored procedure when fetching data from my cursor into my declared variables I am measuring vastly different elapsed times between my QA and dev environments.

    QA is processing 250+ records per second and dev is processing 2 records per second.

    I captured the elapsed times for each step of the stored procedure on both servers and narrowed it down to the FETCH C1 INTO .... step. I am at a loss b/c I am not sure how to improvement the performance when fetching the values from a cursor into the DECLARED variables.

    Is the temporary space/memory that is allocated to store the values from the cursor during the FETCH INTO somehow not the same between the databases and the affect is this slow performance. If so how would I monitor or adjust?

    I ran the cursor's SQL stmt with db2batch on both servers and the Prepare/Fetch/Execute time is the same for the statement being executed during the OPEN C1 step of the procedure.

    The databases were both built from the same backup image, however my dev env has more memory than QA.

    The procedures are identical and all rebind/statistics are up to date. There is no other activity on the databases.

    I am running DB2 9.7 fp5 on LINUX Redhat 5.3. Both use STMM; BP and sheap sz, etc. are set to automatic. Page sizes are the same for all tables and spaces on each database - again they were built from the same backup image.

    Thanks in advance! james

  2. #2
    Join Date
    Nov 2010
    Posts
    99
    Anyone...Bueller?

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    So what is the difference from the Dev and QA systems?

    Andy

  4. #4
    Join Date
    Nov 2010
    Posts
    99
    Hi thanks for replying.

    Here are the differences -
    Dev has 32GB of Ram 1.5 phy CPU with 1 instance and 1 database. INSTANCE_MEMORY= 7M pages
    QA has 16GB of Ram 2 phy CPU with 2 instances each with 1 database. INSTANCE_MEMORY = 4M pages

    I an running AESE 9.7 fp5 on Linux 5.3.

    They were both rebuilt using the same backup image.

    QA executes at around 250+ records per second and Dev executes around 2 records per second.

    The slowdown is when fetching the cursor into the declared variables. I determined this by printing out the timestamp for each step - open cursor - fietch - insert - loop. I then took compared the elapsed times for each step on both servers and see that the fetch into step is take 1000%+ longer.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How many cores are each CPU? What are the tablespaces on on each server? How big are the affected bufferpools?

    Andy

  6. #6
    Join Date
    Nov 2010
    Posts
    99
    DEV: PCPU=1.5, VCPU=2, SMT=4, LCPU=8
    QA: PCPU=2, VCPU=4, SMT=2, LCPU=4

    Both run on SAN V7000 with 12 enclosures.

    Hope this answers your question.

  7. #7
    Join Date
    Nov 2010
    Posts
    99
    Also the data, index, and tempspace is spread out over 4 volumes.

    Bufferpools are 32K indexes 4K. On dev both are set to automatic. On QA 32K bp automatic but 4K is hardcoded at 200K pages; both no block I/O.

    Tablespace use automatic storage, auto resize, autoprefetch yes, no file system caching

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Are the disks (size, speed, RAID, etc) on the SAN the same for both servers?

    What is the output from both servers:

    SELECT * FROM SYSIBMADM.ENV)_SYS_INFO
    SELECT * FRON SYSIBMADM.ENV_SYS_RESOURCES
    select * from sysibmadm.snapbp_part


    Can you post the Stored Procedure?

    Andy

  9. #9
    Join Date
    Nov 2010
    Posts
    99
    Yeah same for both servers. I am formatting the output of system resources.

  10. #10
    Join Date
    Nov 2010
    Posts
    99
    This is the procedure: It is executed via a trigger on a replicated table. Note this is a Datamart db.

    CREATE PROCEDURE DB2INST1.ITR_ETL_CYCLE (in inID INT)

    DYNAMIC RESULT SETS 1
    SPECIFIC ITR_ETL_CYCLE
    COMMIT ON RETURN NO
    ------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------
    P4: BEGIN

    -- DECLARE VARIABLES FOR PHASE1 TABLE
    DECLARE lsID INTEGER;
    DECLARE lsITR_YEAR_QTR CHAR(6);
    DECLARE lsITR_HFA_MFG_NUM CHAR(6);
    DECLARE lsITR_HFA_MFG_NAME VARCHAR(100);
    DECLARE lsITR_TOP_PUB_NUM VARCHAR(6);
    DECLARE lsITR_TOP_PUB_NAME VARCHAR(100);
    DECLARE lsITR_HFA_PUB_NUM VARCHAR(6);
    DECLARE lsITR_HFA_PUB_NAME VARCHAR(100);
    DECLARE lsITR_MFG_PUB_NUM VARCHAR(9);
    DECLARE lsITR_HFA_SONG_CODE,lsITR_HFA_SONG_CODE_CRTS CHAR(6);
    DECLARE lsITR_HFA_SONG_TITLE VARCHAR(100);
    DECLARE lsITR_MFG_SONG_NUM VARCHAR(100);
    DECLARE lsITR_MFG_CAT_NUM VARCHAR(100) DEFAULT '';
    DECLARE lsITR_MFG_SALES_TYPE CHAR(10);
    DECLARE lsITR_MFG_UNITS DECIMAL(10,0);
    DECLARE lsITR_MFG_RATE DECIMAL(6,4);
    DECLARE lsITR_MFG_AMOUNT DECIMAL(11,2);
    DECLARE lsITR_HFA_LICENSE_NUM_CNT INT;
    DECLARE lsITR_ALBUM_TITLE VARCHAR(100);
    DECLARE lsITR_SONG_WRITER VARCHAR(100);
    DECLARE lsITR_MFG_PERIOD_3 CHAR(8);
    DECLARE lsITR_HFA_PERIOD CHAR(8);
    DECLARE lsITR_HFA_PERIOD_CRTS CHAR(15);
    DECLARE lsITR_HFA_LICENSE_NUM DECIMAL(15,0);
    DECLARE lsITR_PAID_CHK_NUM DECIMAL(9,0);
    DECLARE lsITR_GROUP_CODE CHAR(15);
    DECLARE lsITR_PAY_AMOUNT DECIMAL(11,2);
    DECLARE lsITR_HFA_PUB_SPLIT DECIMAL(3,0);
    DECLARE lsITR_MFG_PUB_SPLIT DECIMAL(3,0);
    DECLARE lsITR_MEDIA_TYPE CHAR(15);
    DECLARE lsITR_T_PTY_INFO VARCHAR(100);
    DECLARE lsITR_FOR_AMT DECIMAL(10,2);
    DECLARE lsITR_FOR_EXC_RATE DECIMAL(9,5);
    DECLARE lsITR_LABEL_NAME VARCHAR(100);
    DECLARE lsITR_CONFIG VARCHAR(100);
    DECLARE lsITR_PUB_SONG_ID CHAR(20);
    DECLARE lsITR_PROC_TYPE CHAR(5);
    DECLARE lsITR_STAT_TYPE CHAR(20);
    DECLARE lsITR_TRAN_TYPE CHAR(20);
    DECLARE lsITR_GRP_METH CHAR(20);
    DECLARE lsITR_ARTIST_NAME VARCHAR(100);
    DECLARE lsITR_UPC VARCHAR(20);
    DECLARE lsITR_RELEASE_DATE DATE;
    DECLARE lsITR_ISRC CHAR(15);
    DECLARE lsITR_DSP_NAME VARCHAR(100);
    DECLARE lsITR_PAID_DATE,lsITR_PAID_DATE_CRTS DATE;
    DECLARE lsITR_HFA_NEW_SONG_CODE CHAR(6);
    DECLARE lsINSRTTIME TIMESTAMP;


    DECLARE lsITR_MFG_PERIOD_3_CRTS CHAR(10);
    DECLARE lsITR_HFA_SONG_CODE_TITLE_CRTS VARCHAR(108);
    DECLARE lsITR_MFG_AMOUNT_CNT INTEGER;
    DECLARE lsGROSS_AMOUNT DECIMAL(31,2);
    DECLARE lsITR_MFG_UNITS_CNT INTEGER;
    DECLARE lsUNITS DECIMAL(31,0);
    DECLARE lsITR_PAY_AMOUNT_CNT INTEGER;
    DECLARE lsPAYABLE_AMOUNT DECIMAL(31,2);
    DECLARE lsITR_LICENSE_COUNT INTEGER;
    DECLARE Use_Alternate_Tbl INT;

    -- DECLARE VARIABLES FOR PROCESSING
    DECLARE lsACTION CHAR(1);
    DECLARE lsChkExists, CHK_CNT INT;

    --================================================== =================
    --LEGACY ETL: PHASE1 - ADD RECORDS INTO ITR_MAIN_REPORT (IMR)
    --================================================== =================

    P3: BEGIN

    --insert the records from pfdtamart that will be loaded to IMR and other summary tables
    DECLARE C1_IMR CURSOR FOR
    SELECT
    ACTION
    , inID
    , case when substr(cast(MRPECD as char(6)),5,2) > '40' then '19' else '20' end || substr(cast(MRPECD as char(6)),5,2) || case when substr(cast(MRPECD as char(6)),3,2) in ('01','02','03') then '01' when substr(cast(MRPECD as char(6)),3,2) in ('04','05','06') then '02' when substr(cast(MRPECD as char(6)),3,2) in ('07','08','09') then '03' when substr(cast(MRPECD as char(8)),3,2) in ('10','11','12') then '04' else '00' end AS ITR_YEAR_QTR
    , I.MRMFG# AS ITR_HFA_MFG_NUM
    , A2.NAME1 || ' '|| A2.NAME2 AS ITR_HFA_MFG_NAME
    , I.MRTPPB AS ITR_TOP_PUB_NUM
    , A3.NAME1 || ' ' || A3.NAME2 ITR_TOP_PUB_NAME
    , I.MRPUB# AS ITR_HFA_PUB_NUM
    , A4.NAME1 || ' ' || A4.NAME2 AS ITR_HFA_PUB_NAME
    , I.MRRFBP AS ITR_MFG_PUB_NUM
    , case when itr2.SONG_CD is not null then itr2.SONG_CD else I.MRHFSC end AS ITR_HFA_SONG_CODE
    , UPPER(I.MRSGTL) AS ITR_HFA_SONG_TITLE
    , I.MRSNG# AS ITR_MFG_SONG_NUM
    , I.MRCGNO AS ITR_MFG_CAT_NUM
    , I.MRSLTP AS ITR_MFG_SALES_TYPE
    , I.MRUNIT AS ITR_MFG_UNITS
    , I.MRRATE AS ITR_MFG_RATE
    , I.MRAMNT AS ITR_MFG_AMOUNT
    , case when alb.ALBUM_TITLE is not null then UPPER(alb.ALBUM_TITLE) else UPPER(I.MRALBM) end AS ITR_ALBUM_TITLE
    , UPPER(I.MRSGWR) AS ITR_SONG_WRITER
    --,NULL AS ITR_MFG_PERIOD_1
    , cast(MRDAT2 as char(8)) AS ITR_MFG_PERIOD_3
    , I.MRPECD AS ITR_HFA_PERIOD
    , I.MRLIC AS ITR_HFA_LICENSE_NUM
    , cast(I.MRCHK as dec(9,0)) AS ITR_PAID_CHK_NUM
    , cast(I.MRGRCD as char(7)) AS ITR_GROUP_CODE
    , I.MRPAY$ AS ITR_PAY_AMOUNT
    , cast(I.MRHSPL as dec(3,0)) as ITR_HFA_PUB_SPLIT
    , cast (0 as dec(3,0)) AS ITR_MFG_PUB_SPLIT
    , substr(I.MRMTYP,1,13) AS ITR_MEDIA_TYPE
    , UPPER(I.MRPINF) AS ITR_T_PTY_INFO
    , I.MRFAMT AS ITR_FOR_AMT
    , I.MRXRAT AS ITR_FOR_EXC_RATE
    , case when itr2.LABEL_NAME is not null then itr2.LABEL_NAME else I.MRLBL end AS ITR_LABEL_NAME
    , I.MRCFG AS ITR_CONFIG
    , I.MRPSNG AS ITR_PUB_SONG_ID
    , I.MRTYPE AS ITR_PROC_TYPE
    , case when substr(upper(I.MRTYPE),1,3) = 'ADP' and upper(substr(mrmisc,8,13)) = 'HFA ADJUSTMNT' then 'HFA Adjustment' when substr(upper(I.MRTYPE),1,3) = 'ADP' and upper(substr(mrmisc,1,7)) = 'HFA ADJ' then 'HFA Adjustment' when substr(upper(I.MRTYPE),1,3) = 'ADP' and upper(substr(mrmisc,8,13)) = ' BALANCE FOR' then 'Balance Forward' when substr(upper(I.MRTYPE),1,3) = 'RSI' and substr(mrmsc2,1,2) > '99' then substr(mrmsc2,1,20) end AS ITR_STAT_TYPE
    , case when substr(d.DTDTSR,1,12) IS NOT NULL THEN substr(d.DTDTSR,1,12) end AS ITR_TRAN_TYPE
    , case when substr(upper(I.MRTYPE),1,3) = 'ADP' and substr(mrmsc2,2,1) = '1' then 'Song Code' when substr(upper(I.MRTYPE),1,3) = 'ADP' and substr(mrmsc2,2,1) = '2' then 'Song Title' when substr(upper(I.MRTYPE),1,3) = 'ADP' and substr(mrmsc2,2,1) = '3' then 'Song Title/Catalog' when substr(upper(I.MRTYPE),1,3) = 'ADP' and substr(mrmsc2,2,1) = '4' then 'Tune Number' end AS ITR_GRP_METH
    , UPPER(art.artist_nm) AS ITR_ARTIST_NAME
    , upc.UPC_NO AS ITR_UPC
    , '01-01-1900' AS ITR_RELEASE_DATE
    , case when I.MRISRC IS NOT NULL THEN I.MRISRC else isrc2.isrc end AS ITR_ISRC
    , case when upper(MRMTYP) = 'DIGITAL' then substr(upper(MRPINF),1,20) end AS ITR_DSP_NAME
    , case when TGCHK.CMDATE IS NOT NULL then CAST(substr((cast(CMDATE as char(8))),1,4)||'-'||substr((cast(CMDATE as char(8))),5,2)||'-'||substr((cast(CMDATE as char(8))),7,2) AS DATE) else current date end AS ITR_PAID_DATE
    , ds.new_song AS ITR_HFA_NEW_SONG_CODE
    , INSRTTIME

    FROM INCR.STG_PFDTAMART I

    INNER JOIN DW_AS400_ACCOUNT A2 ON I.MRMFG# = A2.ACCT_NO
    INNER JOIN DW_AS400_ACCOUNT A3 ON I.MRTPPB = A3.ACCT_NO
    INNER JOIN DW_AS400_ACCOUNT A4 ON I.MRPUB# = A4.ACCT_NO
    LEFT OUTER JOIN DATA.TGPFCHKMST TGCHK ON BIGINT(I.MRCHK) = BIGINT(TGCHK.CMCKNO)
    LEFT OUTER JOIN I_DISTABLF D ON D.DTCODE = SUBSTR(I.MRPECD,1,2)
    LEFT OUTER JOIN DW_AS400_LICENSE_ITR2 ITR2 ON I.MRLIC = ITR2.MECH_LICS_NO
    LEFT OUTER JOIN DW_AS400_LICENSE_ALBUM ALB ON I.MRLIC = ALB.MECH_LICS_NO AND ALB.SEQUENCE_NO = 1
    LEFT OUTER JOIN DW_AS400_LICENSE_ARTIST ART ON I.MRLIC = ART.MECH_LICS_NO AND ART.SEQUENCE_NO = 1
    LEFT OUTER JOIN DS_DUP_SONG DS ON I.MRHFSC = DS.DUP_SONG AND DS.PROCESS_STATUS_AS400 = 'C'
    LEFT OUTER JOIN DW_AS400_LICENSE_UPC UPC ON I.MRLIC = UPC.MECH_LICS_NO AND UPC.SEQUENCE_NO = 1
    LEFT OUTER JOIN DW_AS400_LICENSE_ISRC2 ISRC2 ON I.MRLIC = ISRC2.MECH_LICS_NO
    WHERE
    ID = inID
    FOR FETCH ONLY
    OPTIMIZE FOR 1 ROWS
    ;

    OPEN C1_IMR;

    P2: BEGIN

    --fetch the records from our temp table/cursor to be processed
    FETCH C1_IMR INTO
    lsACTION
    ,lsID
    ,lsITR_YEAR_QTR
    ,lsITR_HFA_MFG_NUM
    ,lsITR_HFA_MFG_NAME
    ,lsITR_TOP_PUB_NUM
    ,lsITR_TOP_PUB_NAME
    ,lsITR_HFA_PUB_NUM
    ,lsITR_HFA_PUB_NAME
    ,lsITR_MFG_PUB_NUM
    ,lsITR_HFA_SONG_CODE
    ,lsITR_HFA_SONG_TITLE
    ,lsITR_MFG_SONG_NUM
    ,lsITR_MFG_CAT_NUM
    ,lsITR_MFG_SALES_TYPE
    ,lsITR_MFG_UNITS
    ,lsITR_MFG_RATE
    ,lsITR_MFG_AMOUNT
    ,lsITR_ALBUM_TITLE
    ,lsITR_SONG_WRITER
    ,lsITR_MFG_PERIOD_3
    ,lsITR_HFA_PERIOD
    ,lsITR_HFA_LICENSE_NUM
    ,lsITR_PAID_CHK_NUM
    ,lsITR_GROUP_CODE
    ,lsITR_PAY_AMOUNT
    ,lsITR_HFA_PUB_SPLIT
    ,lsITR_MFG_PUB_SPLIT
    ,lsITR_MEDIA_TYPE
    ,lsITR_T_PTY_INFO
    ,lsITR_FOR_AMT
    ,lsITR_FOR_EXC_RATE
    ,lsITR_LABEL_NAME
    ,lsITR_CONFIG
    ,lsITR_PUB_SONG_ID
    ,lsITR_PROC_TYPE
    ,lsITR_STAT_TYPE
    ,lsITR_TRAN_TYPE
    ,lsITR_GRP_METH
    ,lsITR_ARTIST_NAME
    ,lsITR_UPC
    ,lsITR_RELEASE_DATE
    ,lsITR_ISRC
    ,lsITR_DSP_NAME
    ,lsITR_PAID_DATE
    ,lsITR_HFA_NEW_SONG_CODE
    ,lsINSRTTIME
    ;

  11. #11
    Join Date
    Nov 2010
    Posts
    99
    sproc continued....


    --insert the records into IMR
    --99.99% of the time ACTION = 'I'
    IF lsACTION='I' THEN
    INSERT INTO DB2INST1.ITR_MAIN_REPORT VALUES
    (
    lsID
    ,lsITR_YEAR_QTR
    ,NULL --lsITR_HFA_TOP_MFG_NUM note:always null
    ,NULL --lsITR_HFA_TOP_MFG_NAME note:always null
    ,lsITR_HFA_MFG_NUM
    ,lsITR_HFA_MFG_NAME
    ,lsITR_TOP_PUB_NUM
    ,lsITR_TOP_PUB_NAME
    ,lsITR_HFA_PUB_NUM
    ,lsITR_HFA_PUB_NAME
    ,lsITR_MFG_PUB_NUM
    ,NULL -- lsITR_MFG_PUB_NAME note:always null
    ,lsITR_HFA_SONG_CODE
    ,lsITR_HFA_SONG_TITLE
    ,lsITR_MFG_SONG_NUM
    ,lsITR_MFG_CAT_NUM
    ,lsITR_MFG_SALES_TYPE
    ,NULL --lsITR_CHANNEL note:always null
    ,lsITR_MFG_UNITS
    ,lsITR_MFG_RATE
    ,lsITR_MFG_AMOUNT
    ,lsITR_ALBUM_TITLE
    ,lsITR_SONG_WRITER
    ,NULL --lsITR_MFG_PERIOD_1 note:unused
    ,lsITR_MFG_PERIOD_3
    ,lsITR_HFA_PERIOD
    ,NULL --lsITR_CALC_HFA_PERIOD note:always null
    ,lsITR_HFA_LICENSE_NUM
    ,lsITR_PAID_CHK_NUM
    ,lsITR_GROUP_CODE
    ,lsITR_PAY_AMOUNT
    ,lsITR_HFA_PUB_SPLIT
    ,lsITR_MFG_PUB_SPLIT --note:always 000.
    ,lsITR_MEDIA_TYPE
    ,lsITR_T_PTY_INFO
    ,lsITR_FOR_AMT
    ,lsITR_FOR_EXC_RATE
    ,lsITR_LABEL_NAME
    ,lsITR_CONFIG
    ,NULL --lsITR_CONFIG_DESC note:always null
    ,lsITR_PUB_SONG_ID
    ,lsITR_PROC_TYPE
    ,lsITR_STAT_TYPE
    ,lsITR_TRAN_TYPE
    ,lsITR_GRP_METH
    ,lsITR_ARTIST_NAME
    ,lsITR_UPC
    ,lsITR_RELEASE_DATE --note: always 01-01-1900
    ,lsITR_ISRC
    ,lsITR_DSP_NAME
    ,lsITR_PAID_DATE
    ,lsITR_HFA_NEW_SONG_CODE
    ,lsITR_TOP_PUB_NUM
    ,lsITR_TOP_PUB_NAME
    ,lsINSRTTIME
    );
    END IF;

    --be very afraid
    IF lsACTION='D' THEN
    DELETE FROM ITR_MAIN_REPORT WHERE ID = lsID;
    END IF;

    --kinda scary but it's okay let go of the fear
    IF lsACTION='U' THEN
    UPDATE DB2INST1.ITR_MAIN_REPORT SET
    ITR_YEAR_QTR = lsITR_YEAR_QTR
    ,ITR_HFA_TOP_MFG_NUM = NULL
    ,ITR_HFA_TOP_MFG_NAME = NULL
    ,ITR_HFA_MFG_NUM = lsITR_HFA_MFG_NUM
    ,ITR_HFA_MFG_NAME = lsITR_HFA_MFG_NAME
    ,ITR_TOP_PUB_NUM = lsITR_TOP_PUB_NUM
    ,ITR_TOP_PUB_NAME = lsITR_TOP_PUB_NAME
    ,ITR_HFA_PUB_NUM = lsITR_HFA_PUB_NUM
    ,ITR_HFA_PUB_NAME = lsITR_HFA_PUB_NAME
    ,ITR_MFG_PUB_NUM = lsITR_MFG_PUB_NUM
    ,ITR_MFG_PUB_NAME = NULL
    ,ITR_HFA_SONG_CODE = lsITR_HFA_SONG_CODE
    ,ITR_HFA_SONG_TITLE = lsITR_HFA_SONG_TITLE
    ,ITR_MFG_SONG_NUM = lsITR_MFG_SONG_NUM
    ,ITR_MFG_CAT_NUM = lsITR_MFG_CAT_NUM
    ,ITR_MFG_SALES_TYPE = lsITR_MFG_SALES_TYPE
    ,ITR_CHANNEL = NULL
    ,ITR_MFG_UNITS = lsITR_MFG_UNITS
    ,ITR_MFG_RATE = lsITR_MFG_RATE
    ,ITR_MFG_AMOUNT = lsITR_MFG_AMOUNT
    ,ITR_ALBUM_TITLE = lsITR_ALBUM_TITLE
    ,ITR_SONG_WRITER = lsITR_SONG_WRITER
    ,ITR_MFG_PERIOD_1 = NULL
    ,ITR_MFG_PERIOD_3 = lsITR_MFG_PERIOD_3
    ,ITR_HFA_PERIOD = lsITR_HFA_PERIOD
    ,ITR_CALC_HFA_PERIOD = NULL
    ,ITR_HFA_LICENSE_NUM = lsITR_HFA_LICENSE_NUM
    ,ITR_PAID_CHK_NUM = lsITR_PAID_CHK_NUM
    ,ITR_GROUP_CODE = lsITR_GROUP_CODE
    ,ITR_PAY_AMOUNT = lsITR_PAY_AMOUNT
    ,ITR_HFA_PUB_SPLIT = lsITR_HFA_PUB_SPLIT
    ,ITR_MFG_PUB_SPLIT = lsITR_MFG_PUB_SPLIT
    ,ITR_MEDIA_TYPE = lsITR_MEDIA_TYPE
    ,ITR_T_PTY_INFO = lsITR_T_PTY_INFO
    ,ITR_FOR_AMT = lsITR_FOR_AMT
    ,ITR_FOR_EXC_RATE = lsITR_FOR_EXC_RATE
    ,ITR_LABEL_NAME = lsITR_LABEL_NAME
    ,ITR_CONFIG = lsITR_CONFIG
    ,ITR_CONFIG_DESC = NULL
    ,ITR_PUB_SONG_ID = lsITR_PUB_SONG_ID
    ,ITR_PROC_TYPE = lsITR_PROC_TYPE
    ,ITR_STAT_TYPE = lsITR_STAT_TYPE
    ,ITR_TRAN_TYPE = lsITR_TRAN_TYPE
    ,ITR_GRP_METH = lsITR_GRP_METH
    ,ITR_ARTIST_NAME = lsITR_ARTIST_NAME
    ,ITR_UPC = lsITR_UPC
    ,ITR_RELEASE_DATE = lsITR_RELEASE_DATE
    ,ITR_ISRC = lsITR_ISRC
    ,ITR_DSP_NAME = lsITR_DSP_NAME
    ,ITR_PAID_DATE = lsITR_PAID_DATE
    ,ITR_HFA_NEW_SONG_CODE = lsITR_HFA_NEW_SONG_CODE
    ,ITR_TOP_PUB_NUM_ORIG = lsITR_TOP_PUB_NUM
    ,ITR_TOP_PUB_NAME_ORIG = lsITR_TOP_PUB_NAME
    ,INSRTTIME = lsINSRTTIME
    WHERE ID = lsID
    ;
    END IF;

    CLOSE C1_IMR;

    --================================================== =================
    --LEGACY ETL: PHASE2 - ADD RECORDS TO THE SUMMARY TABLES
    --================================================== =================

    --replaces ITR_PHASE2 SPROC ETL_ITR_MAIN_REPORT_INCR and calling script: /dbwork/perlscripts/itr_main_report_incr.sh and sproc, sp_etl_itr_main_report_incr.sql
    --instead of adding more logic here,we will create a duplicate key handler in the sproc and let the primary key reject the duplicate but continue processing.
    --At this point we need to load the distinct records into this group of summary tables based on the target table's pk

    IF lsACTION='I' OR lsACTION='U' THEN

    SET CHK_CNT = (SELECT COUNT(1) FROM DB2INST1.ITR_CAT_NUM_SUM_TMP WHERE ITR_TOP_PUB_NUM = lsITR_TOP_PUB_NUM AND ITR_YEAR_QTR = lsITR_YEAR_QTR AND ITR_HFA_MFG_NUM = lsITR_HFA_MFG_NUM AND ITR_MFG_CAT_NUM = lsITR_MFG_CAT_NUM);
    IF CHK_CNT = 0 AND lsITR_TOP_PUB_NUM IS NOT NULL AND lsITR_YEAR_QTR IS NOT NULL AND lsITR_HFA_MFG_NUM IS NOT NULL AND lsITR_MFG_CAT_NUM IS NOT NULL THEN
    INSERT INTO DB2INST1.ITR_CAT_NUM_SUM_TMP VALUES(lsITR_TOP_PUB_NUM ,lsITR_YEAR_QTR, lsITR_HFA_MFG_NUM, lsITR_MFG_CAT_NUM);
    END IF;
    SET CHK_CNT = (SELECT COUNT(1) FROM DB2INST1.ITR_CONFIG_SUM_TMP WHERE ITR_TOP_PUB_NUM = lsITR_TOP_PUB_NUM AND ITR_YEAR_QTR = lsITR_YEAR_QTR AND ITR_MFG_NUM_SUM = lsITR_HFA_MFG_NUM AND ITR_CONFIG_SUM = lsITR_CONFIG);
    IF CHK_CNT = 0 AND lsITR_TOP_PUB_NUM IS NOT NULL AND lsITR_YEAR_QTR IS NOT NULL AND lsITR_HFA_MFG_NUM IS NOT NULL AND lsITR_CONFIG IS NOT NULL THEN
    INSERT INTO DB2INST1.ITR_CONFIG_SUM_TMP VALUES(lsITR_TOP_PUB_NUM ,lsITR_YEAR_QTR, lsITR_HFA_MFG_NUM, lsITR_CONFIG);
    END IF;
    SET CHK_CNT = (SELECT COUNT(1) FROM DB2INST1.ITR_MFG_NUM_SUM_TMP WHERE ITR_TOP_PUB_NUM = lsITR_TOP_PUB_NUM AND ITR_YEAR_QTR = lsITR_YEAR_QTR AND ITR_MFG_NUM_SUM = lsITR_HFA_MFG_NUM AND ITR_MFG_NAME_SUM = lsITR_HFA_MFG_NAME);
    IF CHK_CNT = 0 AND lsITR_TOP_PUB_NUM IS NOT NULL AND lsITR_YEAR_QTR IS NOT NULL AND lsITR_HFA_MFG_NUM IS NOT NULL AND lsITR_HFA_MFG_NAME IS NOT NULL THEN
    INSERT INTO DB2INST1.ITR_MFG_NUM_SUM_TMP VALUES(lsITR_TOP_PUB_NUM ,lsITR_YEAR_QTR, lsITR_HFA_MFG_NUM, lsITR_HFA_MFG_NAME);
    END IF;
    SET CHK_CNT = (SELECT COUNT(1) FROM DB2INST1.ITR_PUB_NUM_SUM_TMP WHERE ITR_TOP_PUB_NUM = lsITR_TOP_PUB_NUM AND ITR_YEAR_QTR = lsITR_YEAR_QTR AND ITR_PUB_NUM_SUM = lsITR_HFA_PUB_NUM AND ITR_PUB_NAME_SUM = lsITR_HFA_PUB_NAME);
    IF CHK_CNT = 0 AND lsITR_TOP_PUB_NUM IS NOT NULL AND lsITR_YEAR_QTR IS NOT NULL AND lsITR_HFA_PUB_NUM IS NOT NULL AND lsITR_HFA_PUB_NAME IS NOT NULL THEN
    INSERT INTO DB2INST1.ITR_PUB_NUM_SUM_TMP VALUES(lsITR_TOP_PUB_NUM ,lsITR_YEAR_QTR, lsITR_HFA_PUB_NUM, lsITR_HFA_PUB_NAME);
    END IF;
    SET CHK_CNT = (SELECT COUNT(1) FROM DB2INST1.ITR_SALES_TYP_SUM_TMP WHERE ITR_TOP_PUB_NUM = lsITR_TOP_PUB_NUM AND ITR_YEAR_QTR = lsITR_YEAR_QTR AND ITR_MFG_NUM_SUM = lsITR_HFA_MFG_NUM AND ITR_SALES_TYPE_SUM = lsITR_MFG_SALES_TYPE);
    IF CHK_CNT = 0 AND lsITR_TOP_PUB_NUM IS NOT NULL AND lsITR_YEAR_QTR IS NOT NULL AND lsITR_HFA_MFG_NUM IS NOT NULL AND lsITR_MFG_SALES_TYPE IS NOT NULL THEN
    INSERT INTO DB2INST1.ITR_SALES_TYP_SUM_TMP VALUES(lsITR_TOP_PUB_NUM ,lsITR_YEAR_QTR, lsITR_HFA_MFG_NUM, lsITR_MFG_SALES_TYPE);
    END IF;
    SET CHK_CNT = (SELECT COUNT(1) FROM DB2INST1.ITR_DATE_TMP WHERE ITR_YEAR_QTR = lsITR_YEAR_QTR);
    IF CHK_CNT = 0 AND lsITR_YEAR_QTR IS NOT NULL THEN
    INSERT INTO DB2INST1.ITR_DATE_TMP VALUES(lsITR_YEAR_QTR);
    END IF;
    SET CHK_CNT = (SELECT COUNT(1) FROM DB2INST1.ITR_MAIN_REPORT_DIST_SONG_TITLE WHERE ITR_HFA_SONG_TITLE = lsITR_HFA_SONG_TITLE);
    IF CHK_CNT = 0 AND lsITR_HFA_SONG_TITLE IS NOT NULL THEN
    INSERT INTO DB2INST1.ITR_MAIN_REPORT_DIST_SONG_TITLE VALUES(lsITR_HFA_SONG_TITLE);
    END IF;
    END IF;

    P1: BEGIN

  12. #12
    Join Date
    Nov 2010
    Posts
    99
    sproc continued:

    DECLARE C2_CRTS CURSOR FOR SELECT
    COUNT(1)
    FROM DB2INST1.ITR_CRTS_MAIN_SUM
    WHERE ITR_MFG_PERIOD_3 = lsITR_MFG_PERIOD_3_CRTS
    AND ITR_PAYMENT_TYPE = lsITR_HFA_PERIOD_CRTS
    AND ITR_PAID_DATE = lsITR_PAID_DATE_CRTS
    AND ITR_TOP_PUB_NUM = lsITR_TOP_PUB_NUM
    AND ITR_TOP_PUB_NAME = lsITR_TOP_PUB_NAME
    AND ITR_HFA_PUB_NUM = lsITR_HFA_PUB_NUM
    AND ITR_HFA_PUB_NAME = lsITR_HFA_PUB_NAME
    AND ITR_HFA_MFG_NUM = lsITR_HFA_MFG_NUM
    AND ITR_HFA_MFG_NAME = lsITR_HFA_MFG_NAME
    AND ITR_HFA_SONG_CODE = lsITR_HFA_SONG_CODE_CRTS
    AND ITR_HFA_SONG_CODE_TITLE = lsITR_HFA_SONG_CODE_TITLE_CRTS;

    OPEN C2_CRTS;

    FETCH C2_CRTS INTO lsChkExists;

    CLOSE C2_CRTS;

    SET lsITR_MFG_PERIOD_3_CRTS = CASE WHEN lsITR_MFG_PERIOD_3 ='00000000' THEN CASE WHEN SUBSTR(CAST(lsITR_HFA_PERIOD as char(6)),5,2) > '40' THEN '19' ELSE '20' END ||SUBSTR(CAST(lsITR_HFA_PERIOD as char(6)),5,2) ||'-'|| CASE WHEN SUBSTR(CAST(lsITR_HFA_PERIOD as char(6)),3,2) IS NULL OR SUBSTR(CAST(lsITR_HFA_PERIOD as char(6)),3,2) = ' ' THEN '01' ELSE SUBSTR(CAST(lsITR_HFA_PERIOD as char(6)),3,2) END ||'-'|| '01' ELSE SUBSTR(lsITR_MFG_PERIOD_3,1,4)||'-'||SUBSTR(lsITR_MFG_PERIOD_3,5,2)||'-'||'01' END;
    SET lsITR_HFA_PERIOD_CRTS = CASE WHEN SUBSTR(lsITR_HFA_PERIOD,1,2) = 'AP' then 'Audit Payment' WHEN SUBSTR(lsITR_HFA_PERIOD,1,2) = 'AV' then 'Advance Payment' ELSE 'Royalty Payment' END;
    SET lsITR_PAID_DATE_CRTS = CAST(YEAR(lsITR_PAID_DATE) AS CHAR(4))||'-'|| CASE WHEN MONTH(lsITR_PAID_DATE) < 10 THEN '0'||CAST(MONTH(lsITR_PAID_DATE) AS CHAR(1)) ELSE CAST(MONTH(lsITR_PAID_DATE) AS CHAR(2))END ||'-'||'01';
    SET lsITR_HFA_SONG_CODE_CRTS = CASE WHEN lsITR_HFA_SONG_CODE IS NULL THEN '000000' ELSE lsITR_HFA_SONG_CODE END;
    SET lsITR_HFA_SONG_CODE_TITLE_CRTS = CASE WHEN lsITR_HFA_SONG_CODE IS NULL THEN '000000' || ' ' || lsITR_HFA_SONG_TITLE ELSE lsITR_HFA_SONG_CODE ||' '|| lsITR_HFA_SONG_TITLE END;

    IF lsChkExists = 0 THEN
    INSERT INTO ITR_CRTS_MAIN_SUM VALUES
    (
    lsITR_MFG_PERIOD_3_CRTS
    ,lsITR_HFA_PERIOD_CRTS
    ,lsITR_PAID_DATE_CRTS
    ,lsITR_TOP_PUB_NUM
    ,lsITR_TOP_PUB_NAME
    ,lsITR_HFA_PUB_NUM
    ,lsITR_HFA_PUB_NAME
    ,lsITR_HFA_MFG_NUM
    ,lsITR_HFA_MFG_NAME
    ,lsITR_HFA_SONG_CODE_CRTS
    ,lsITR_HFA_SONG_CODE_TITLE_CRTS
    ,1
    ,lsITR_MFG_AMOUNT
    ,1
    ,lsITR_MFG_UNITS
    ,1
    ,lsITR_PAY_AMOUNT
    ,1
    )
    ;
    END IF;

    --only update if ACTION = 'I'
    IF lsChkExists = 1 AND lsACTION = 'I' THEN
    UPDATE ITR_CRTS_MAIN_SUM SET
    ITR_MFG_AMOUNT_CNT = ITR_MFG_AMOUNT_CNT + 1
    , GROSS_AMOUNT = GROSS_AMOUNT + lsITR_MFG_AMOUNT
    , ITR_MFG_UNITS_CNT = ITR_MFG_UNITS_CNT + 1
    , UNITS = UNITS + lsITR_MFG_UNITS
    , ITR_PAY_AMOUNT_CNT = ITR_PAY_AMOUNT_CNT + 1
    , PAYABLE_AMOUNT = PAYABLE_AMOUNT + lsITR_PAY_AMOUNT
    , ITR_LICENSE_COUNT = ITR_LICENSE_COUNT + 1
    WHERE
    ITR_MFG_PERIOD_3 = lsITR_MFG_PERIOD_3_CRTS AND
    ITR_PAYMENT_TYPE = lsITR_HFA_PERIOD_CRTS AND
    ITR_PAID_DATE = lsITR_PAID_DATE AND
    ITR_TOP_PUB_NUM = lsITR_TOP_PUB_NUM AND
    ITR_TOP_PUB_NAME = lsITR_TOP_PUB_NAME AND
    ITR_HFA_PUB_NUM = lsITR_HFA_PUB_NUM AND
    ITR_HFA_PUB_NAME = lsITR_HFA_PUB_NAME AND
    ITR_HFA_MFG_NUM = lsITR_HFA_MFG_NUM AND
    ITR_HFA_MFG_NAME = lsITR_HFA_MFG_NAME AND
    lsITR_HFA_SONG_CODE = lsITR_HFA_SONG_CODE_CRTS AND
    ITR_HFA_SONG_CODE_TITLE = lsITR_HFA_SONG_CODE_TITLE_CRTS;
    END IF;

    IF lsChkExists = 1 AND lsACTION = 'D' THEN
    UPDATE ITR_CRTS_MAIN_SUM SET
    ITR_MFG_AMOUNT_CNT = ITR_MFG_AMOUNT_CNT - 1
    , GROSS_AMOUNT = GROSS_AMOUNT - lsITR_MFG_AMOUNT
    , ITR_MFG_UNITS_CNT = ITR_MFG_UNITS_CNT - 1
    , UNITS = UNITS - lsITR_MFG_UNITS
    , ITR_PAY_AMOUNT_CNT = ITR_PAY_AMOUNT_CNT - 1
    , PAYABLE_AMOUNT = PAYABLE_AMOUNT - lsITR_PAY_AMOUNT
    , ITR_LICENSE_COUNT = ITR_LICENSE_COUNT - 1
    WHERE
    ITR_MFG_PERIOD_3 = lsITR_MFG_PERIOD_3_CRTS AND
    ITR_PAYMENT_TYPE = lsITR_HFA_PERIOD_CRTS AND
    ITR_PAID_DATE = lsITR_PAID_DATE AND
    ITR_TOP_PUB_NUM = lsITR_TOP_PUB_NUM AND
    ITR_TOP_PUB_NAME = lsITR_TOP_PUB_NAME AND
    ITR_HFA_PUB_NUM = lsITR_HFA_PUB_NUM AND
    ITR_HFA_PUB_NAME = lsITR_HFA_PUB_NAME AND
    ITR_HFA_MFG_NUM = lsITR_HFA_MFG_NUM AND
    ITR_HFA_MFG_NAME = lsITR_HFA_MFG_NAME AND
    lsITR_HFA_SONG_CODE = lsITR_HFA_SONG_CODE_CRTS AND
    ITR_HFA_SONG_CODE_TITLE = lsITR_HFA_SONG_CODE_TITLE_CRTS;
    END IF;


    END P1;
    END P2;
    END P3;
    END P4@

  13. #13
    Join Date
    Nov 2010
    Posts
    99
    OS_NAME OS_VERSION OS_RELEASE HOST_NAME TOTAL_CPUS CONFIGURED_CPUS TOTAL_MEMORY
    ------- ---------- ---------- --------------------- ----------- --------------- ------------
    Linux 2 6 ddb01.harryfox.com 16 32 31594
    Linux 2 6 TMART01.harryfox.com 16 32 16319

    NAME DEV_VALUE QA_VALUE DATATYPE UNIT DBPARTITIONNUM
    ------------------------ ------------------------------------------- ------------- -------- --------------
    OS_NAME Linux Linux VARCHAR(256) - 0
    HOST_NAME ddb01.harryfox.com TMART01.harryfox.com VARCHAR(256) - 0
    OS_VERSION 2 2 VARCHAR(256) - 0
    OS_RELEASE 6 6 VARCHAR(256) - 0
    MACHINE_IDENTIFICATION ppc64 ppc64 VARCHAR(256) - 0
    OS_LEVEL 32 18 VARCHAR(256) - 0
    CPU_TOTAL 32 32 BIGINT - 0
    CPU_ONLINE 8 8 BIGINT - 0
    CPU_CONFIGURED 32 32 BIGINT - 0
    CPU_SPEED 3000 3000 BIGINT MHz 0
    CPU_TIMEBASE 512000000 512000000 BIGINT Hz 0
    CPU_HMT_DEGREE 2 2 BIGINT - 0
    CPU_CORES_PER_SOCKET 1 1 BIGINT - 0
    MEMORY_TOTAL 31594 16319 BIGINT MB 0
    MEMORY_FREE 42 32 BIGINT MB 0
    MEMORY_SWAP_TOTAL 10240 10144 BIGINT MB 0
    MEMORY_SWAP_FREE 7890 9089 BIGINT MB 0
    VIRTUAL_MEM_TOTAL 41834 26463 BIGINT MB 0
    VIRTUAL_MEM_FREE 7932 9121 BIGINT MB 0
    CPU_LOAD_SHORT 2.860000 1.870000 DECIMAL - 0
    CPU_LOAD_MEDIUM 3.630000 2.050000 DECIMAL - 0
    CPU_LOAD_LONG 3.200000 2.400000 DECIMAL - 0
    CPU_USAGE_TOTAL 8 5 SMALLINT PERCENT 0

    23 record(s) selected.

    ||-----------DEV----------------------------------------------------------------------------------------||------------------------QA-------------------------------------------------------------------------------||
    BP_NAME DB_NAME BP_CUR_BUFFSZ BP_NEW_BUFFSZ BP_PAGES_LEFT_TO_REMOVE BP_TBSP_USE_COUNT DBPARTITIONNUM BP_CUR_BUFFSZ BP_NEW_BUFFSZ BP_PAGES_LEFT_TO_REMOVE BP_TBSP_USE_COUNT DBPARTITIONNUM
    -------------- -------- -------------------- -------------------- ----------------------- -------------------- -------------- -------------------- -------------------- ----------------------- -------------------- --------------
    IBMDEFAULTBP HFA_CONV 5992 5992 0 5 0 10667 10667 0 6 0
    BP4K HFA_CONV 200000 200000 0 8 0 200000 200000 0 7 0
    BP32K HFA_CONV 5718 5718 0 8 0 19293 19293 0 8 0
    BP4KI HFA_CONV 150000 150000 0 7 0 150000 150000 0 7 0
    BP4KS HFA_CONV 6160 6160 0 2 0 12994 12994 0 2 0
    BP32KS HFA_CONV 19304 19304 0 2 0 2027 2027 0 2 0
    BP32K2 HFA_CONV 1000 1000 0 1 0 1806 1806 0 1 0
    BP4K2I HFA_CONV 98403 98403 0 1 0 170000 170000 0 1 0
    BP32K3 HFA_CONV 1000 1000 0 1 0 2750 2750 0 1 0
    BP4K3I HFA_CONV 1000 1000 0 1 0 48775 48775 0 1 0
    IBMSYSTEMBP4K HFA_CONV 16 16 0 0 0 16 16 0 0 0
    IBMSYSTEMBP8K HFA_CONV 16 16 0 0 0 16 16 0 0 0
    IBMSYSTEMBP16K HFA_CONV 16 16 0 0 0 16 16 0 0 0
    IBMSYSTEMBP32K HFA_CONV 16 16 0 0 0 16 16 0 0 0

    14 record(s) selected.

  14. #14
    Join Date
    Nov 2010
    Posts
    99
    OS_NAME OS_VERSION OS_RELEASE HOST_NAME TOTAL_CPUS CONFIGURED_CPUS TOTAL_MEMORY
    ------- ---------- ---------- --------------------- ----------- --------------- ------------
    Linux 2 6 ddb01.harryfox.com 16 32 31594
    Linux 2 6 TMART01.harryfox.com 16 32 16319

  15. #15
    Join Date
    Nov 2010
    Posts
    99
    DEV:

    NAME VALUE DATATYPE UNIT DBPARTITIONNUM
    ------------------------ --------------------- -------------- ------- --------------
    OS_NAME Linux VARCHAR(256) - 0
    HOST_NAME ddb01.harryfox.com VARCHAR(256) - 0
    OS_VERSION 2 VARCHAR(256) - 0
    OS_RELEASE 6 VARCHAR(256) - 0
    MACHINE_IDENTIFICATION ppc64 VARCHAR(256) - 0
    OS_LEVEL 32 VARCHAR(256) - 0
    CPU_TOTAL 32 BIGINT - 0
    CPU_ONLINE 8 BIGINT - 0
    CPU_CONFIGURED 32 BIGINT - 0
    CPU_SPEED 3000 BIGINT MHz 0
    CPU_TIMEBASE 512000000 BIGINT Hz 0
    CPU_HMT_DEGREE 2 BIGINT - 0
    CPU_CORES_PER_SOCKET 1 BIGINT - 0
    MEMORY_TOTAL 31594 BIGINT MB 0
    MEMORY_FREE 42 BIGINT MB 0
    MEMORY_SWAP_TOTAL 10240 BIGINT MB 0
    MEMORY_SWAP_FREE 7890 BIGINT MB 0
    VIRTUAL_MEM_TOTAL 41834 BIGINT MB 0
    VIRTUAL_MEM_FREE 7932 BIGINT MB 0
    CPU_LOAD_SHORT 2.860000 DECIMAL - 0
    CPU_LOAD_MEDIUM 3.630000 DECIMAL - 0
    CPU_LOAD_LONG 3.200000 DECIMAL - 0
    CPU_USAGE_TOTAL 8 SMALLINT PERCENT 0

    23 record(s) selected.

Posting Permissions

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