Parameterize source & target table name and also schema details in TPT script

Tools
N/A

Parameterize source & target table name and also schema details in TPT script

Hi,

I have a requirement to move data from a teradata table (PROD region) to other teradata table(DEV region) using TPT. Say,

Source table Name: Bank_Prod

Columns : ID (interger) , NAME (CHAR)

Target table Name: Bank_Dev

Columns : ID (interger) , NAME (CHAR)

I have 100 such tables to be loaded from PROD to DEV..table structure is same in both region. I want to create a generic TPT script and use that to load any number of tables. For doing this, I need to parameteris the Source table name,target table name,source schema details and target schema details. I have flat file which stores all the table defintions.

Please see below script, I want to parametrize the content highlighted (bold)

Please do the needful

====Script====

/* 1 */ 

/* 2 */ 

/* 3 */  DEFINE JOB Test1

/* 4 */  (

/* 5 */   DEFINE OPERATOR W_1_o_Test1

/* 6 */   TYPE LOAD

/* 7 */   SCHEMA *

/* 8 */   ATTRIBUTES

/* 9 */   (

/* 10 */    VARCHAR UserName,

/* 11 */    VARCHAR UserPassword,

/* 12 */    VARCHAR LogTable,

/* 13 */    VARCHAR TargetTable,

/* 14 */    INTEGER BufferSize,

/* 15 */    INTEGER ErrorLimit,

/* 16 */    INTEGER MaxSessions,

/* 17 */    INTEGER MinSessions,

/* 18 */    INTEGER TenacityHours,

/* 19 */    INTEGER TenacitySleep,

/* 20 */    VARCHAR AccountID,

/* 21 */    VARCHAR DateForm,

/* 22 */    VARCHAR ErrorTable1,

/* 23 */    VARCHAR ErrorTable2,

/* 24 */    VARCHAR NotifyExit,

/* 25 */    VARCHAR NotifyExitIsDLL,

/* 26 */    VARCHAR NotifyLevel,

/* 27 */    VARCHAR NotifyMethod,

/* 28 */    VARCHAR NotifyString,

/* 29 */    VARCHAR PauseAcq,

/* 30 */    VARCHAR PrivateLogName,

/* 31 */    VARCHAR TdpId,

/* 32 */    VARCHAR TraceLevel,

/* 33 */    VARCHAR WorkingDatabase

/* 34 */   );

/* 35 */ 

/* 36 */   DEFINE SCHEMA W_0_s_Test1

/* 37 */   (

/* 38 */    ID INTEGER,

/* 39 */    name CHARACTER(4)

/* 40 */   );


/* 41 */ 

/* 42 */   DEFINE OPERATOR W_0_o_Test1

/* 43 */   TYPE EXPORT

/* 44 */   SCHEMA W_0_s_Test1

/* 45 */   ATTRIBUTES

/* 46 */   (

/* 47 */    VARCHAR UserName,

/* 48 */    VARCHAR UserPassword,

/* 49 */    VARCHAR SelectStmt,

/* 50 */    INTEGER BlockSize,

/* 51 */    INTEGER MaxSessions,

/* 52 */    INTEGER MinSessions,

/* 53 */    INTEGER TenacityHours,

/* 54 */    INTEGER TenacitySleep,

/* 55 */    INTEGER MaxDecimalDigits,

/* 56 */    VARCHAR AccountID,

/* 57 */    VARCHAR DateForm,

/* 58 */    VARCHAR NotifyExit,

/* 59 */    VARCHAR NotifyExitIsDLL,

/* 60 */    VARCHAR NotifyLevel,

/* 61 */    VARCHAR NotifyMethod,

/* 62 */    VARCHAR NotifyString,

/* 63 */    VARCHAR PrivateLogName,

/* 64 */    VARCHAR TdpId,

/* 65 */    VARCHAR TraceLevel,

/* 66 */    VARCHAR WorkingDatabase

/* 67 */   );

/* 68 */ 

/* 69 */   APPLY

/* 70 */    (

/* 71 */     'INSERT INTO DEV.Bank_Dev (ID,name) VALUES (:ID:name);'

/* 72 */    )

/* 73 */   TO OPERATOR

/* 74 */   (

/* 75 */    W_1_o_Test1[1]

/* 76 */ 

/* 77 */    ATTRIBUTES

/* 78 */    (

/* 79 */     UserName = 'xxx',

/* 80 */     UserPassword = 'xxx',

/* 81 */     LogTable = 'DEV.Bank_Dev_log',

/* 82 */     TargetTable = 'DEV.Bank_Dev,

/* 83 */     TdpId = '1234'

/* 84 */    )

/* 85 */   )

/* 86 */   SELECT * FROM OPERATOR

/* 87 */   (

/* 88 */    W_0_o_Test1[1]

/* 89 */ 

/* 90 */    ATTRIBUTES

/* 91 */    (

/* 92 */      UserName = 'xxx',

/* 93 */     UserPassword = 'xxx',

/* 94 */     SelectStmt = 'SELECT * FROM PROD.Bank_Prod;',

/* 95 */     TdpId = '1234'

/* 96 */    )

/* 97 */   );

/* 98 */  );

Tags (1)
13 REPLIES
Teradata Employee

Re: Parameterize source & target table name and also schema details in TPT script

What version of TPT are you using?

We have made great improvements to the simplification of our script language, such that you do not need to be so verbose.

Also, we can obtain the schema automatically now.

We are trying to get TPT users to move to the use of templates. When you install TPT, we will install template files in the install directories. Those templates are text files that contain the operator definitions, with job variables assigned for every supported attribute.

If you want, you can also obtain the schema of a table by using this syntax:

DEFINE SCHEMA FROM TABLE <table name>;

In fact, we have made enhancements to Easy Loader to support the Export operator so that you can do what you are trying to do just from a command line, wiithout the need for a script.

To answer your question, though, pretty much everything in a TPT script can be replaced by either a job variable, or the use of the %INCLUDE statement to bring in text from an outside source.

However, with TPT's "simplicity", you can do this:

DEFINE JOB . . .
(
APPLY $INSERT
TO OPERATOR ($LOAD[1])
SELECT * FROM OPERATOR ($EXPORT[1]);
);

The $LOAD syntax says to use the Load operator.

The $EXPORT syntax says to use the Export operator.

The $INSERT syntax tells TPT to generate the INSERT statement based on the schema of the source table.

TPT will generate the needed syntax for the script under the covers.

You then just need to supply a job variable file on the command line with the needed metadata items (logon information, table name, etc.).

With Easy Loader, we support the Export operator in versions 14.00 and 14.10.

From the command line, you just provide the source table and logon credentials, and the target table and logon credentials, and Easy Loader will generate the script for you, based on the schema of the source table.

The attached Partners presentation may help.

-- SteveF

Re: Parameterize source & target table name and also schema details in TPT script

hi,

I have used the above mentioned code snippet.

DEFINE JOB . . .

(

   APPLY $INSERT

   TO OPERATOR ($LOAD[1])

   SELECT * FROM OPERATOR ($EXPORT[1]);

);


But getting the error message like this.

Teradata Parallel Transporter Version 13.10.00.07

TPT_INFRA: TPT04063: Error: Line 6 of Job Script File 'qsetup1.txt': No operator referenced in the job (step) has an

  explicit schema or any table association from which the job (step) schema can be

  inferred.

Job script preprocessing failed.

Job terminated with status 8.

please help me in resolving this.

Variable file Content(jobvars1.txt):

LoadPrivateLogName = 'loadoper_private_log',

LoadTargetTable = 'emp_test2',

LoadWorkingDatabase = 'EDW_OFFSHORE_STG_INVNT',

LoadErrorTable1 = 'emp_test1_et',

LoadErrorTable2 = 'emp_test1_uv',

LoadLogTable = 'emp_test1_log',

LoadMaxSessions = 5,

LoadMinSessions = 1,

ExportPrivateLogName = 'exportoper_private_log',

ExportMaxSessions = 5,

ExportMinSessions = 1,

ExportSelectStmt = 'select * from dbnAME.emp_test1;',

TargetUserName = 'xxx',

TargetUserPassword = 'yyy',

TargetTdpId = '000.00.00.91',

SourceUserName = 'xxx',

SourceUserPassword = 'yyy',

SourceTdpId =  '000.00.00.92'

Teradata Employee

Re: Parameterize source & target table name and also schema details in TPT script

Please take a look at the templates that came with 13.10.

I think the "Load...." job variables names used to be known by "Target....".

We had to change the names in 14.0 to support the existence of more than one "target" operator.

Since you are using TPT 13.10, you need to use the old names in the job variable file.

-- SteveF

Re: Parameterize source & target table name and also schema details in TPT script

I have changed the job variable names as u have mentioned.

But I am facing the below issue.

Cannot open the file '$SCHEMA_emp_test2.txt'due to error 13

        Permission denied

TPT_INFRA: TPT04032: Error: Schema generation failed for table 'emp_test2' in DBS '138.83.86.91':

  "GetTableSchema" status: 48.

Job script preprocessing failed.

Job terminated with status 12.

Both 'emp_test1' and 'emp_test2' are present in the same server and same db.

so if it is possible to get 'emp_test1' schema,how is the permission denied for 'emp_test2'.Please help me to resolve this.

Thanks for ur response.

Enthusiast

Re: Parameterize source & target table name and also schema details in TPT script

Is it possible to have the File_Loader attributes in a seperate file and just call this file in the attributes session.? currenly in our loader we have only few attributes defined like

TdpId, UserName  , UserPassword  , LogonMech  , QueryBandSessInfo, TargetTable  , LogTable  , ErrorTable1 , ErrorTable2 , WorkTable , MaxSessions

But we need to add few other attributes for job control like TenacityHours,TenacitySleep etc.. so instead of having the attributes in individual tpt script can i have this attributes and its value in a file and call this file in the attribute ?

Teradata Employee

Re: Parameterize source & target table name and also schema details in TPT script

Which version of TPT are you using?

Please refer to the TPT documentation for the use of Job Variables.

Everything in a TPT script is substitutable.

In fact, we do not want you to hardcode anything in scripts. Instead, assign attribute values to job variables and create a job variable file (or use the command line) to provide the runtime values for these job variables.

TPT has also undergone many changes in order to determine the schema for you. The documentation will explain how.

-- SteveF
Enthusiast

Re: Parameterize source & target table name and also schema details in TPT script

we are using ttu 13.10. Can we have the attributes itself in a file and call the those inside the define operator.?

Teradata Employee

Re: Parameterize source & target table name and also schema details in TPT script

Please refer to the TPT documentation for the full explanation of how to use Job Variables. The User Guide should provide the needed information.

-- SteveF
Enthusiast

Re: Parameterize source & target table name and also schema details in TPT script

Thanks feinholz..