Teradata Studio products - choosing the right client tool

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

Re: Teradata Studio products - choosing the right client tool

i am using teradata studio to execute a stored procedure, which for right now, generates some create user statements.  when i execute the SP in studio, nothing shows up in the result set viewer.   i am populating the output into an OUT variable within the SP

in the hiostory window i get in the result column: "Executed as Single statement.  Elapsed time = 00:00:00.040 STATEMENT 1: CALL  completed."

I am very new to studio and have no idea where to go from here.  below is the answer set i get when using SQLA.

any help or direction would be greatly appreciated.  thanks so much

output in SQLA:

Creating userid using:

CREATE USER "REGUSER_ID" from "TDUSERS"

as perm = 0

password = "PASSWORD"

spool = 0

temporary = 0

account = ('$L$_NO_PROFILE_&S&D&H')

STARTUP = ''

DEFAULT DATABASE = "REGUSER_ID"

NO FALLBACK

NO BEFORE JOURNAL

NO AFTER JOURNAL

COLLATION = HOST

DEFAULT CHARACTER SET LATIN

DATEFORM=INTEGERDATE

TIME ZONE=NULL

profile="p_tq_td_infra"

default role ="R_SYSTEM_ADMIN"

;

Updating Comment:

comment on user "REGUSER_ID" as "COMMENT - USER NAME - JOB TITLE - DEPARTMENT";

Setting adoc PW to null:

GRANT LOGON ON ALL TO "REGUSER_ID" WITH NULL PASSWORD;

Not applicable

Re: Teradata Studio products - choosing the right client tool

I've encountered a couple of issues that are making it difficult to transition to Teradata Studio from SQL Assistant; would appreciate any clarification from Teradata personnel.

  1. I’d recently started using the .IF construct from BTEQ in my promotion scripts as a work-around for Teradata’s lack of the “IF EXISTS” capability, for example when a table must be replaced but it doesn’t exist in all the applicable promotion environments, for instance:

/* DROP stage table view in Interim environment if it exists */

SELECT 1 FROM DBC.TABLES WHERE DATABASENAME = 'DEV_STAGE_Interim_V' AND TABLENAME = 'VIEW_NAME_V' ;

.IF ACTIVITYCOUNT = 0 THEN GOTO OK1

DROP VIEW  DEV_STAGE_Interim_V.VIEW_NAME_V;

.LABEL OK1

This works well in SQL Assistant starting with the 14 versions, but is flagged as a syntax error in Teradata Studio, and I’ve been told by local support personnel that they’re not aware of any plans to support it in Teradata Studio.

  1.  With respect to parameterized queries – it appears they’ve been made so cumbersome as to be essentially unusable.  Here’s my use case:

This query works well in SQL Assistant (prompts a single time for each parameter, then applies it in multiple places as intended):

sel 'DEV ?Parm1 not DEVNEXT   ' (title 'Discrepancy'), tablename,' '  (title 'GT') from DBC.TABLES

where databasename = 'BUILD_DEV_?Parm1'||'_T'  and tablename like '?Parm2\_%' ESCAPE '\'

and tablename in (sel table_name from BUILD_METADATA_T.TABLE_SOURCE where source_system_name = '?Parm3')

and tablekind  = 'T'

minus

sel 'DEV ?Parm1 not DEVNEXT   ', tablename, ' '  from DBC.TABLES

where databasename = 'BUILD_METADATA_?Parm1'||'_T' and tablename like '?Parm2\_%' ESCAPE '\'

and tablekind = 'T'

UNION ALL

sel 'DEVNEXT ?Parm1 not DEV   ', tablename, ' '  from DBC.TABLES

where databasename = 'BUILD_METADATA_?Parm1'||'_T' and tablename like '?Parm2\_%' ESCAPE '\'

and tablekind = 'T'

minus

sel 'DEVNEXT ?Parm1 not DEV   ', tablename, ' '  from DBC.TABLES

where databasename = 'BUILD_DEV_?Parm1'||'_T' and tablename like '?Parm2\_%' ESCAPE '\'

and tablename in (sel table_name from BUILD_METADATA_T.TABLE_SOURCE where source_system_name = '?Parm3')

and tablekind = 'T'

UNION ALL

sel 'GLOBAL TEMP inconsistent', tablename, CommitOpt from DBC.TABLES

where databasename = 'BUILD_METADATA_?Parm1'||'_T' and trim(tablename) like '?Parm2\_%\_GT' ESCAPE '\'

and tablekind = 'T'

minus

sel 'GLOBAL TEMP inconsistent', tablename, CommitOpt from DBC.TABLES

where databasename = 'BUILD_DEV_?Parm1'||'_T' and trim(tablename) like '?Parm2\_%\_GT' ESCAPE '\'

and tablename in (sel table_name from BUILD_METADATA_T.TABLE_SOURCE where source_system_name = '?Parm3')

and tablekind = 'T'

UNION ALL

sel 'DEVNEXT not MULTISET', tablename, ' '  from DBC.TABLES

where databasename = 'BUILD_METADATA_?Parm1'||'_T' and tablename like '?Parm2\_%' ESCAPE '\'

and tablename in (sel table_name from BUILD_METADATA_T.TABLE_SOURCE where source_system_name = '?Parm3')

and tablekind = 'T'

and ( (version = 1 and index (RequestText, 'CREATE MULTISET' ) = 0) or (version > 1 and checkopt = 'N') )

order by 2,1;

The problems with this in Teradata Studio are multiple.  One is that the entire query has to be rewritten, since apparently a parameter is not recognized inside a quoted string.  So the first line of the select above must be re-coded for concatenation as:

sel 'DEV ' || ?\Parm1 ||' not DEVNEXT   ' (title 'Discrepancy'), tablename,' '  (title 'GT') from DBC.TABLES

This in itself would not be an unworkable solution (the recoding would only need to be done once.)  The problem is with the execution.  We are prompted separately for every instance of the parameter, such that before the query is executed, one must manually type in the values for 11 instances of the ?Parm1 parameter, 4 instances of the ?Parm3 parameter, and 7 instances of the ?Parm2 parameter.

This almost completely destroys the value of the parameterized query for us.  The intent is that the user grab the stored query, enter 3 parameters to customize for the intended environment comparisons, and run.

Are any near-term resolutions in sight?

Teradata Employee

Re: Teradata Studio products - choosing the right client tool

@rkgudde, We are not parsing the SQL Editor for Call statements. Try running the stored procedure from the Data Source Explorer by right clicking on the stored procedure and click the Run... menu option. It then knows that it is a stored procedure and may have OUT values and will display them in the Result set viewer.

Teradata Employee

Re: Teradata Studio products - choosing the right client tool

@tjfoley, 1. There are features of the ODBC driver that are not implemented by the JDBC driver. This sounds like one of them.

2. We will look into this issue. The quoted string issue may be hard to resolve since the parser treats them as identifiers. But the repeated parameter names should be something we can fix.

Enthusiast

Re: Teradata Studio products - choosing the right client tool

Thanks for getting back with me.  I drilled down to where my stored proc is.  left clicked on it and hit the run.  but now i following error:

"Executed as Single statement.  Failed [5531 : HY000] Named-list is not supported for arguments of a procedure.

Elapsed time = 00:00:00.000

STATEMENT 1: Unknown failed."

and the contents of the SQl statement field is "call rgudde.createUserId".  i have several input parms that need to be passed.  the entire call statement is:

CALL rgudde.createUserId ('REGUSER_ID','U','USERNAME','JOBTITLE','DEPARTMENT','COMMENT','PASSWORD','p_tq_td_infra','R_SYSTEM_ADMIN',a,a);

when I right click and hit run, how do i get the input parms passed.

thanks so much

Teradata Employee

Re: Teradata Studio products - choosing the right client tool

@rkgkudde, If you are running from the Data Source Explorer than it will know that you have IN parameters and prompt you for them. You do not need to execute the CALL statement. It will create a JDBC Callable Statement and execute the stored procedure.

Open  the Teradata View (or if already opened then bring in focus) and then select your stored procedure in the DSE. What does the Parameters tab show? It should list all of the IN and OUT parameters. Is your stored procedure a SQL Stored Procedure or external Stored Procedure (C++ or Java)?

Enthusiast

Re: Teradata Studio products - choosing the right client tool

i didnt run a call statement.  i just hit the run when i right clicked.  the call without parms showed up in the history.  when i click on the parm tab it shows my parm definitions (position, name, parm type, mode, comments).  i dont see any place to enter values in need passed in.  it is an SQL SP.  nothing fancy.  Im sorry about this i know its probably a RTFM fix.

Enthusiast

Re: Teradata Studio products - choosing the right client tool

does the SP need to be created in Studio? 

Teradata Employee

Re: Teradata Studio products - choosing the right client tool

@rkgudde, In the Teradata View, what does it show in the Mode column for each of the parameters. For example, I have a stored procedure that takes in a parameter called region and returns the parameter as output (thus INOUT).

Position   Name   Parameter Type    Mode   Comments

1              region  VARCHAR(64)        INOUT

Teradata Employee

Re: Teradata Studio products - choosing the right client tool

@rkgudde, No, it does not.