How to Collect Statistics within a TPT script

Tools & Utilities
Enthusiast

How to Collect Statistics within a TPT script

Hi All,

I have a TPT script which copies data between different environments(PROD,DEV) and i am using a export and load operator to do this and a DDL operator to delete/insert into target table. I started thinking of collecting stats on my target table and included the below step in script which ran successfully in unix dev env. But in QA and prod env looks like this is showing an issue. We also have some sysdba SPs which can be called in our scripts to collect stats.

I am trying to add something in the script which can call the stored procedure which will collect stats.

Can anyone recommend how to call the SP which collect stats in TPT script.

Thanks in advance.

Tags (1)
8 REPLIES
Teradata Employee

Re: How to Collect Statistics within a TPT script

What version of TPT are you using?

In general, the DDL operator can send any SQL to Teradata, as long as the request does not result in data being returned to the operator.

I believe we added support for calling stored procedures into TPT in 14.0, because stored procedures need special processing in the cases where the stored procedure language needs to be sent from the client box to the Teradata box for storage.

-- SteveF
Enthusiast

Re: How to Collect Statistics within a TPT script

We are using TPT version 13.1.

I created a DDL operator and wrote the below step to collect stats:

STEP COLLECT_STATISTICS

(

APPLY

('CALL SysDBA.COLLECT_STATS('@tgtdb', 'tablename', 'INDEX(col)', RESULT);')

TO OPERATOR (DDL_OPERATOR () );

);

i got the below error:

TPT_INFRA: TPT04046: Error: Line 48 of Job Script File 'test.txt': Adjacent quoted strings must be separated by the

  concatenation operator: '||'

Is there any other approach in tpt i can use to call the SP and this is the standards we have to follow in collecting stats.

Teradata Employee

Re: How to Collect Statistics within a TPT script

You cannot put single quotes within a single quoted string without escaping them. Try doubling each single quote inside the quoted string for the "CALL".

However, if you are using a job variable, you need to concatenate.

Something like this:

('CALL SysDBA.COLLECT_STATS(' || @tgtdb || ', ''tablename'', ''INDEX(col)'', RESULT);')

(or maybe they have to be tripled, you will have to check the documentation)

-- SteveF
Enthusiast

Re: How to Collect Statistics within a TPT script

Can you confirm whether TPT ver 13.1 supports calling a SP using DDL operator.

Also the SP is returning a out variable call result, is this acceptable in TPT DDL operator.

Can you please suggest any other operator or approach in TPT can be used in this case.

and what is the documentation you are talking about, can you please share the link or any.

BTW thanks for quick response !

Highlighted
Enthusiast

Re: How to Collect Statistics within a TPT script

Hi All,

Anybody know what is the best solution for the above questions i have posted?

Please respond. Any help wil be highly appreciated.

Thanks

Enthusiast

Re: How to Collect Statistics within a TPT script

Hi Feinholz,

Can you please let me know your thoughts on the above questions i have posted.

Thank you for ur time.

Teradata Employee

Re: How to Collect Statistics within a TPT script

Stored Procedure support went into TPT in 14.0.

I do not know of any other option for TPT to collect stats.

-- SteveF
Enthusiast

Re: How to Collect Statistics within a TPT script

Ok thanks for your information Feinholz