collect stats programatically without use of dynamic SQL?

Database
Enthusiast

collect stats programatically without use of dynamic SQL?

Is there any way to collect stats programatically when you are restricted from using dynamic SQL? I don't think you can invoke a macro which collects stats from within a stored procedure. Can you shell out to a BTEQ script that collects stats?

N.
8 REPLIES
Enthusiast

Re: collect stats programatically without use of dynamic SQL?

So I take it, the answer is "no"?

I'm still dealing with this issue and haven't come up with a solution.

Am I the only developer whose DBA's won't grant him permsssion to write dynamic SQL?

Nolan
Teradata Employee

Re: collect stats programatically without use of dynamic SQL?

Depends on what "programatically" and "dynamic" mean to you. I'm assuming that it's specifically the use of dbc.SysExecSQL in a stored procedure that you are forbidden from using? What are you trying to accomplish?

A stored procedure or macro can include a COLLECT STATISTICS statement directly as long as the object names are fixed. But you can't pass object names (database, table, index, column) to the database as parameters, those names must be in the actual SQL text of the statement to be parsed.

Enthusiast

Re: collect stats programatically without use of dynamic SQL?

(At least in my environment) a stored procedure cannot collect statistics on a temporary table without permission to run Dynamic SQL.

I have a test SP which inserts rows into a global temporary table. I added the statement:

Collect statistics
On temporary dupcs1.ep_gt_scnro_acct_lst column acct_ky;

The table name and column name are fixed.

When I attempt to call the SP I get a message: "
SPL1078:E(L22), User is not authorized to use the DDL/DCL/Dynamic SQL statement.

"
Enthusiast

Re: collect stats programatically without use of dynamic SQL?

From the messages reference manual:

SPL1078: "%s:E(L%d), User is not authorized to use the DDL/DCL/Dynamic SQL statement."

Explanation: The reason is that the creator and the
immediate owner of the stored procedure is not the
same. Hence the specified DDL, DCL, or dynamic SQL
statement cannot be specified in the SPL source text.
Enthusiast

Re: collect stats programatically without use of dynamic SQL?

I am both the creator and the immediate owner. Even when I try to create a stored procedure with the Collect Stats statement, I get the same error message.
rgs
Enthusiast

Re: collect stats programatically without use of dynamic SQL?

Wow, you must be running on database release 4.1.0. That restriction was eliminated in database release 4.1.1. If you are on 4.1.0 the DBA needs to grant you execute privilege on DBC.sysexecsql. There is no harm in doing that. The sysexecsql is a dummy procedure. It is just a convention to let the SPL compiler know to generate code to process a dynamic SQL statement. You don’t get DBC access rights when executing that call statement which some DBAs assumed was the case. It uses the access rights of the creator/owner of the procedure to execute the dynamic SQL statement. So you don’t have any more privilege than you normally have with none dynamic SQL. You need to talk to your DBA to have them GRANT that or upgrade the database to a newer version.
Enthusiast

Re: collect stats programatically without use of dynamic SQL?

We're running on V26.1.1.

My understanding is that granting access to DBC.sysexecsql allows you to run dynamic SQL. Our DBAs' contention is that a developer can create uncontrolled SQL which consumes all system resources. That's why they won't grant us permission to run dynamic SQL.

I don't see why a Collect Stats statement is considered dynamic SQL.

Nolan
Teradata Employee

Re: collect stats programatically without use of dynamic SQL?

COLLECT STATS is not considered "dynamic SQL", but it is considered DDL so is subject to additional authorization checking - which is what DBC.SysExecSQL does and how it figures into the picture.

But I'm confused on at least a couple points:

1) No specific privilege is required to use DBC.SysExecSQL either to implicitly check rights for DDL/DCL or to explicitly CALL it in a stored procedure for "dynamic SQL". But it validates that the creator of the SP is the immediate owner. In other words, if I log on as MyID then I have to CREATE PROCEDURE MyID.SomeSP to be able to use COLLECT STATS in it. But you said you tried that.

2) Developers can certainly "create uncontrolled SQL which consumes all system resources" - but it doesn't take DBC.SysExecSQL or stored procedures to do that.