Multiload script to load multiple tables - Teradata multiload script with Indexes on Table

Teradata Multiload allows us to UPDATE or INSERT data into non-empty tables. MLOAD internally does the operation using 5 phases.

Phases of Mload are:

1.       Multiload Initial Phase

2.       Multiload DML transaction Phase

3.       Multiload Acquisistion Phase

4.       Multiload Application Phase

5.       Multiload Cleanup Phase

5 tables can be loaded using a single multiload script if they are using the same file.

If you need to restart a failed MLOAD job:

Drop the log tables:

drop table financial.log_staging_cust_present_1;

drop table financial.staging_cust_present_ET_1;

drop table financial.staging_cust_present_UV_1;

drop table ET_staging_cust_present_1;

drop table UV_staging_cust_present_1;

Release the mload lock on the table:

release mload staging_cust_present_1 ;

release mload staging_cust_present ;

Adding  Unique secondary indexes on a table and trying to load data using Multiload, gives an error as shown below:

**** 03:30:30 UTY0805 RDBMS failure, 3547: MLoad target table may not contain

     unique secondary indexes.

This is because USI is a 2-AMP operation.

Index support for Multiload is as below:

1.       NUPI

2.       UPI

3.       PPI

4.       NUSI

Please run the below steps to validate the index support.

CREATE MULTISET TABLE financial.staging_cust_present     (

      NAME VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      DOB DATE FORMAT 'yyyy-mm-dd')

PRIMARY INDEX ( NAME )

INDEX idx_staging_cust ( DOB );

CREATE MULTISET TABLE financial.staging_cust_present_1      (

      NAME VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      DOB DATE FORMAT 'YY/MM/DD')

UNIQUE PRIMARY INDEX ( NAME ,DOB )

PARTITION BY RANGE_N(DOB  BETWEEN '2015-01-01' AND '2015-12-31' EACH INTERVAL '1' MONTH ,

 NO RANGE OR UNKNOWN);

CREATE MULTISET TABLE financial.staging_cust_present_2     (

      NAME VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      DOB DATE FORMAT 'YY/MM/DD')

UNIQUE PRIMARY INDEX ( NAME ,DOB )

PARTITION BY RANGE_N(DOB  BETWEEN '2015-01-01' AND '2015-12-31' EACH INTERVAL '1' MONTH ,

 NO RANGE OR UNKNOWN)

UNIQUE INDEX(DOB);

Execute the attached mload script as: mload < mload_cust_online_present_2.mload

Observation:  If you have USI, the mload will fail. When we remove the Unique secondary index, then the below log file will be generated

The 5 phases of MLOAD are highlighted in yellow:

TDExpress14.10.01_Sles10:~/Teradata/finance # mload < mload_cust_online_present_2.mload

     ========================================================================

     =                                                                      =

     =          MultiLoad Utility    Release MLOD.14.10.00.04               =

     =          Platform LINUX                                              =

     =                                                                      =

     ========================================================================

     =                                                                      =

     =     Copyright 1990-2013 Teradata Corporation. ALL RIGHTS RESERVED.   =

     =                                                                      =

     ========================================================================

**** 04:23:55 UTY2411 Processing start date: WED APR 01, 2015

     ========================================================================

     =                                                                      =

     =          Logon/Connection                                            =

     =                                                                      =

     ========================================================================

0001 .LOGTABLE financial.log_staging_cust_present_1;

0002 .LOGON 127.0.01/dbc,;

**** 04:23:55 UTY8400 Current RDBMS supports Enhanced Statement Status

**** 04:23:55 UTY8400 Teradata Database Release: 14.10.01.02

**** 04:23:55 UTY8400 Teradata Database Version: 14.10.01.01

**** 04:23:55 UTY8400 Default character set: ASCII

**** 04:23:55 UTY8400 Current RDBMS has interval support

**** 04:23:55 UTY8400 Current RDBMS has UDT support

**** 04:23:55 UTY8400 Current RDBMS has Large Decimal support

**** 04:23:55 UTY8400 Current RDBMS has TASM support

**** 04:23:55 UTY8400 Maximum supported buffer size: 1M

**** 04:23:55 UTY8400 Data Encryption supported by RDBMS server

**** 04:23:55 UTY6211 A successful connect was made to the RDBMS.

**** 04:23:55 UTY6210 Logtable 'financial.log_staging_cust_present_1' indicates

     that a restart is in progress.

     ========================================================================

     =                                                                      =

     =          Processing Control Statements                               =

     =                                                                      =

     ========================================================================

0003 sessions 5;

**** 04:23:55 UTY1014 Unsupported RDBMS/SQL statement: "SESSIONS"

0004 tenacity 1;

**** 04:23:55 UTY1014 Unsupported RDBMS/SQL statement: "TENACITY"

0005 database financial;

**** 04:23:55 UTY1012 A restart is in progress.  This request has already been

     executed.  The return code was: 0.

**** 04:23:55 UTY1024 Session modal request, 'DATABASE', re-executed.

0006 drop table financial.staging_cust_present_WK;

**** 04:23:55 UTY1012 A restart is in progress.  This request has already been

     executed.  The return code was: 0.

0007 drop table financial.staging_cust_present_ET_1;

**** 04:23:55 UTY1011 A restart is in progress.  This request has already been

     executed.  The return code was: 3807, accompanied by the following message

     text:

     Object 'financial.staging_cust_present_ET_1' does not exist.

0008 drop table financial.staging_cust_present_UV_1;

**** 04:23:55 UTY1011 A restart is in progress.  This request has already been

     executed.  The return code was: 3807, accompanied by the following message

     text:

     Object 'financial.staging_cust_present_UV_1' does not exist.

0009 .BEGIN IMPORT MLOAD

     TABLES financial.staging_cust_present,financial.staging_cust_present_1

     WORKTABLES

     financial.staging_cust_present_WK,financial.staging_cust_present_1_WK

     ERRORTABLES financial.staging_cust_present_ET_1

     financial.staging_cust_present_UV_1;

     ========================================================================

     =                                                                      =

     =          Processing MultiLoad Statements                             =

     =                                                                      =

     ========================================================================

0010 .LAYOUT DATAIN_LAYOUT;

0011 .FIELD c_name 1 VARCHAR(30);

0012 .FIELD DOB * varchar(10);

0013 .DML LABEL UPSERT_DML

     do insert for missing update rows;

0014 update financial.staging_cust_present

     set

     ---name = :c_name

     dob = :dob

     where name = :c_name;

0015 INSERT INTO financial.staging_cust_present

     (

     name = :c_name

     ,dob = :dob(date, format 'ddmmyyyy')

     );

0016 .dml label insert_dml;

0017 INSERT INTO financial.staging_cust_present_1

     (

     name = :c_name

     ,dob = :dob(date, format 'ddmmyyyy')

     );

0018 .IMPORT INFILE fld1.csv

     FORMAT vartext '|' LAYOUT DATAIN_LAYOUT

     apply upsert_dml

     APPLY INSERT_DML

     ;

0019 .END MLOAD;

     ========================================================================

     =                                                                      =

     =          MultiLoad Initial Phase                                     =

     =                                                                      =

     ========================================================================

**** 04:23:55 UTY0829 Options in effect for this MultiLoad import task:

     .       Sessions:    One session per available amp.

     .       Checkpoint:  15 minute(s).

     .       Tenacity:    4 hour limit to successfully connect load sessions.

     .       Errlimit:    No limit in effect.

     .       AmpCheck:    In effect for apply phase transitions.

**** 04:23:55 UTY0817 MultiLoad submitting the following request:

     Select NULL from financial.log_staging_cust_present_1 where (LogType = 125)

     and (Seq = 1) and (MloadSeq = 0);

**** 04:23:55 UTY0817 MultiLoad submitting the following request:

     Select NULL from financial.log_staging_cust_present_1 where (LogType = 120)

     and (Seq = 1);

**** 04:23:55 UTY0817 MultiLoad submitting the following request:

     SET QUERY_BAND='UTILITYNAME=MULTLOAD;' UPDATE FOR SESSION;

**** 04:23:55 UTY0817 MultiLoad submitting the following request:

     CHECK WORKLOAD FOR BEGIN MLOAD

     financial.staging_cust_present,financial.staging_cust_present_1;

**** 04:23:55 UTY0817 MultiLoad submitting the following request:

     CHECK WORKLOAD END;

**** 04:23:57 UTY0815 MLOAD session(s) connected: 2.

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     BEGIN MLOAD financial.staging_cust_present,financial.staging_cust_present_1

     WITH INTERVAL;

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     Select NULL from financial.log_staging_cust_present_1 where (LogType = 130)

     and (Seq = 1) and (MloadSeq = 10);

     ========================================================================

     =                                                                      =

     =          MultiLoad DML Transaction Phase                             =

     =                                                                      =

     ========================================================================

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     BT;

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     USING C_NAME(VARCHAR(30)), DOB(VARCHAR(10)) update

     financial.staging_cust_present

     set

     ---name = :c_name

     dob = :dob

     where name = :c_name;

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     USING C_NAME(VARCHAR(30)), DOB(VARCHAR(10)) INSERT INTO

     financial.staging_cust_present

     (

     name = :c_name

     ,dob = :dob(date, format 'ddmmyyyy')

     );

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     USING C_NAME(VARCHAR(30)), DOB(VARCHAR(10)) INSERT INTO

     financial.staging_cust_present_1

     (

     name = :c_name

     ,dob = :dob(date, format 'ddmmyyyy')

     );

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     INS financial.log_staging_cust_present_1 (LogType, Seq,

     MLoadSeq)VALUES(130, 1, 20);

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     ET;

     ========================================================================

     =                                                                      =

     =          MultiLoad Acquisition Phase                                 =

     =                                                                      =

     ========================================================================

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 04:23:57 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 04:23:58 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 04:23:59 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INSERT financial.log_staging_cust_present_1

       (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,

     MiscInt1,MiscInt2,MiscInt3,MiscInt4,

     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,byteflag,MLoadCkpt)

      VALUES (110, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, :Ckpt);

**** 04:23:59 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 04:24:00 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INSERT financial.log_staging_cust_present_1

       (Logtype, Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,

     MiscInt1,MiscInt2,MiscInt3,MiscInt4,

     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,byteflag,MLoadCkpt)

      VALUES (110, 1, 1, 1, 0, 4, 4, 8, 8, 0, 0, 0, 0, 0, 1, :Ckpt);

**** 04:24:00 UTY0826 A checkpoint has been taken, recording that end of file

     has been reached for IMPORT 1 of this MultiLoad Import task.

**** 04:24:00 UTY1803 Import processing statistics

     .                                       IMPORT  1     Total thus far

     .                                       =========     ==============

     Candidate records considered:........           4.......           4

     Apply conditions satisfied:..........           8.......           8

     Candidate records not applied:.......           0.......           0

     Candidate records rejected:..........           0.......           0

**** 04:24:00 UTY1821 Aquisition Phase statistics

     Elapsed time:  00:00:03

     CPU time:      0.01 Seconds

     MB/sec:        4.86667e-05

     MB/cpusec:     0.0146

**** 04:24:00 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 04:24:01 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0 END;

**** 04:24:02 UTY0817 MultiLoad submitting the following request:

     INS financial.log_staging_cust_present_1 (LogType, Seq,

     MLoadSeq)VALUES(130, 1, 30);

**** 04:24:02 UTY0817 MultiLoad submitting the following request:

     ET;

     ========================================================================

     =                                                                      =

     =          MultiLoad Application Phase                                 =

     =                                                                      =

     ========================================================================

**** 04:24:02 UTY0817 MultiLoad submitting the following request:

     EXEC MLOAD financial.staging_cust_present;EXEC MLOAD

     financial.staging_cust_present_1;

**** 04:24:02 UTY0818 Statistics for table financial.staging_cust_present:

        Inserts:                    0

        Updates:                    0

        Deletes:                    0

**** 04:24:02 UTY0818 Statistics for table financial.staging_cust_present_1:

        Inserts:                    0

        Updates:                    0

        Deletes:                    0

**** 04:24:02 UTY1822 Application Phase statistics

     Elapsed time:  00:00:00

**** 04:24:02 UTY0817 MultiLoad submitting the following request:

     INS financial.log_staging_cust_present_1 (LogType, Seq) VALUES (115, 1)

**** 04:24:02 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 04:24:02 UTY0817 MultiLoad submitting the following request:

     END MLOAD;

**** 04:24:02 UTY0817 MultiLoad submitting the following request:

     INS financial.log_staging_cust_present_1 (LogType, Seq) VALUES (120, 1)

**** 04:24:02 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INS financial.log_staging_cust_present_1

     (LogType, Seq, MLoadCkpt)VALUES(135, 1, :Ckpt);

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     END TRANSACTION;

     ========================================================================

     =                                                                      =

     =          MultiLoad Task Cleanup                                      =

     =                                                                      =

     ========================================================================

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     SELECT COUNT(*) FROM financial.staging_cust_present_ET_1;

**** 04:24:03 UTY0846 Error table financial.staging_cust_present_ET_1 contains 4

     rows.

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     SELECT COUNT(*) FROM financial.staging_cust_present_UV_1;

**** 04:24:03 UTY0821 Error table financial.staging_cust_present_UV_1 is EMPTY,

     dropping table.

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     INS financial.log_staging_cust_present_1 (LogType, Seq,

     MLoadSeq)VALUES(125, 1, 2)

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     DROP TABLE financial.staging_cust_present_UV_1;

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     END TRANSACTION;

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     INS financial.log_staging_cust_present_1 (LogType, Seq,

     MLoadSeq)VALUES(125, 1, 3)

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     DROP TABLE financial.staging_cust_present_WK;

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     END TRANSACTION;

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     SELECT COUNT(*) FROM financial.ET_staging_cust_present_1;

**** 04:24:03 UTY0846 Error table financial.ET_staging_cust_present_1 contains 2

     rows.

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     SELECT COUNT(*) FROM financial.UV_staging_cust_present_1;

**** 04:24:03 UTY0846 Error table financial.UV_staging_cust_present_1 contains 2

     rows.

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     INS financial.log_staging_cust_present_1 (LogType, Seq,

     MLoadSeq)VALUES(125, 1, 6)

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     DROP TABLE financial.staging_cust_present_1_WK;

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     END TRANSACTION;

**** 04:24:03 UTY0825 Error table statistics for:

     Target table 1: financial.staging_cust_present

     Number of Rows  Error Table Name

     ==============  ========================================================

                  4  financial.staging_cust_present_ET_1

                  0  financial.staging_cust_present_UV_1

     Target table 2: financial.staging_cust_present_1

     Number of Rows  Error Table Name

     ==============  ========================================================

                  2  financial.ET_staging_cust_present_1

                  2  financial.UV_staging_cust_present_1

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INS financial.log_staging_cust_present_1

     (LogType, Seq, MLoadCkpt)VALUES(140, 1, :Ckpt);

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     INS financial.log_staging_cust_present_1 (LogType, Seq) VALUES (125, 1)

**** 04:24:03 UTY0817 MultiLoad submitting the following request:

     END TRANSACTION;

**** 04:24:05 UTY0822 MultiLoad processing complete for this MultiLoad import

     task.

     ========================================================================

     =                                                                      =

     =          MultiLoad Task Complete                                     =

     =                                                                      =

     ========================================================================

**** 04:24:05 UTY1024 Session modal request, 'DATABASE', re-executed.

**** 04:24:05 UTY1024 Session modal request, 'SET

     QUERY_BAND='UTILITYNAME=MULTLOAD;' UPDATE FOR SESSION;', re-executed.

     ========================================================================

     =                                                                      =

     =          Processing Control Statements                               =

     =                                                                      =

     ========================================================================

0020 .LOGOFF;

     ========================================================================

     =                                                                      =

     =          Logoff/Disconnect                                           =

     =                                                                      =

     ========================================================================

**** 04:24:05 UTY6216 The restart log table has been dropped.

**** 04:24:05 UTY6212 A successful disconnect was made from the RDBMS.

**** 04:24:05 UTY2410 Total processor time used = '1.1 Seconds'

     .       Start : 04:23:55 - WED APR 01, 2015

     .       End   : 04:24:05 - WED APR 01, 2015

     .       Highest return code encountered = '0'.

Teradata DBA Queries - Using Roles and manage Privileges in Teradata - Examples and Scripts

/* FOR MAINTENANCE OF ROLES AND PRIVILEGES */

SELECT * FROM DBC.AllRights WHERE TABLENAME = 'ORDERS';

We need to create Roles and assign privileges to the roles, rather than individual roles.

The below query will give us the list of access rights for a particular user (DBC in this case) on a particular database (retail in the example below).

 select DB.databasename

 , tvm1.tvmname as tvmname

 , user1.databasename as usrname

 ,AR.* 

 from dbc.accessrights as AR

 inner join dbc.dbase as DB

 on AR.databaseid = DB.databaseid

 inner join DBC.TVM as tvm1

 on AR.tvmid = tvm1.tvmid

  inner join dbc.dbase as user1

 on AR.userid = user1.databaseid

 where user1.databasename = 'dbc'

 and DB.databasename = 'retail';

If we keep assigning privileges to individual users, then this table will keep growing. In addition, maintenance will be a overhead.

Individual privileges are assigned as seen in the example below:

GRANT SELECT ON RETAIL.ORDERS TO TRAINING_1;

As per Teradata:

Using Roles simplifies the management of user privileges with roles. 

Roles help improve performance by reducing the number of rows added to and deleted from DBC.AccessRights. 

To use roles, assign privileges to a specific role and then grant the role to users. The users can then access all the objects on which their assigned role and nested roles have privileges.

The above can be achieved using the below list of commands:

CREATE ROLE REPORT_USER;

GRANT SELECT ON RETAIL.ORDERS TO REPORT_USER;

SELECT * FROM DBC.USERS;

CREATE USER TRAINING_1

FROM RETAIL

AS PASSWORD = "TRAINING_1"

, PERMANENT = 40000

, DEFAULT ROLE = REPORT_USER;

DROP USER TRAINING_2;

CREATE USER TRAINING_2

FROM TRAINING_1

AS PASSWORD = "TRAINING_2"

, PERMANENT = 40000

, DEFAULT ROLE = REPORT_USER

, PROFILE = HIGH_SPOOL_USER;

CREATE PROFILE HIGH_SPOOL_USER

AS SPOOL = 10000;

SELECT * FROM DBC.ROLES;

SELECT * FROM DBC.RoleInfo;

Roles and Privileges in Teradata

To manage privileges using roles:

• Create different roles for the different job functions and responsibilities. Roles work best when based on job functions or responsibilities. Some users may belong to more than one role.
• Decide which privileges to which objects a role should have.
• Grant specific privileges on database objects to the roles.
• Grant the role to users or other roles. However, note that roles can only be nested one level.
• Assign default roles to users. Assigning a default role to a user allows the user to automatically access all the objects to which the role has been granted privileges. It also gives the user additional privileges to all the objects its nested roles also owns

Join us in our classroom for more discussion on Teradata.