Help with Table Partitioning

UDA

Help with Table Partitioning

Can some please help me as I am quite new to teradata.

I've created a fact table as follows:

CREATE MULTISET TABLE FACT ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
ACCOUNT_ID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
PERIOD_DATE DATE,
REGION VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
AMOUNT DECIMAL
)
PRIMARY INDEX ( ACCOUNT_ID )
PARTIONED BY REGION;

1. There are multilpe jobs/scripts performing DELETE and INSERT commands
to this table at the same time in parallel.

For instance Job 1:

DELETE FROM FACT
WHERE PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01'
AND REGION = 'ABC';

/* Start inserting into FACT for REGION = 'ABC'
and for PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01'
*/

INSERT INTO FACT
FROM TMP.TABLE;

For instance Job 2:

DELETE FROM FACT
WHERE PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01'
AND REGION = 'DEF';

/* Start inserting into FACT for REGION = 'DEF'
and for PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01'
*/

INSERT INTO FACT
FROM TMP.TABLE;

For instance Job 3:

DELETE FROM FACT
WHERE PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01'
AND REGION = 'GHI';

/* Start inserting into FACT for REGION = 'GHI'
and for PERIOD_DATE BETWEEN '2009-01-01' and '2009-10-01'
*/

INSERT INTO FACT
FROM TMP.TABLE;

I've created my FACT table as a MULTISET with a NON-UNIQUE primary key, partitioned by REGION
prevent locking when inserting into the table.

1) Since all 3 jobs are deleting and inserting at the same time,
will there be any issues with locking ?
I've read that row hash locking will still occur .

2) Any performance issues ?
It is anticipated that the data for each REGION period will
be in the 10's million's.

tony
3 REPLIES
Enthusiast

Re: Help with Table Partitioning

Hi Tony,

We have designed this type of scenario while buliding the cube in one of our project.Design every instance of the job in different BTEQ script and then call all the BTEQ script in the master shell script one by one.perior calling any BTEQ script just check out exit code and then second BTEQ will check that exit code if zero then it will execute the second if not it will not run and give the master shell script code other then null.

Example:

Master.sh
(
call job1.bteq
check for exit code
if zero then
call job2.bteq
else
return code of master script
)

in this case their will not be any row hash lock on the table. and it will be secure because the second job will not run before the first job completes.

Regards,
Rajeev T

Enthusiast

Re: Help with Table Partitioning

Tony it is recommended that you a code a single mload script for all your 3 jobs. There would be no locking issues in that case
Note: PPI table can use mload only when there is no secondary indexes.
Enthusiast

Re: Help with Table Partitioning

also perfomance will be very good since you are using multiload utility. let me know if you need any more info.