How different are user defined SQL and the generated SQL

[vcannadevula]

Is there any performance difference if we use a userdefined sql as a generated SQL???

[Sainath.Srinivasan]

Both run in the DBMS and hence there is no performance difference. The User Defined SQL can be enhanced by the developer to perform better.

[chulett]

... and makes maintenance a PITA.

[Sainath.Srinivasan]

Craig,

What is a PITA?

Piece of cake...pity work....?

[ArndW]

Pain in the Appendage

[Sainath.Srinivasan]

Doh!! Unwanted question. Ignorant me.

[kduke]

User defined is usually slower because it is more complicated otherwise why not use the SQL builder in DataStage.

[diamondabhi]

I know its not important but just wanted to add something that I learned recently.
If you are using DB2 plugin as a source or reference and ur using env variables as parameters then to view he data u need to select the user defined option to view the data.

Thanks,
Abhi.

[vinod raj]

user defined Sql query is one of the performance tunning techique in DS. It is always a better option that generated one. you can use all type of oracle or any database inbuilt functions to the max extent thereby reducing the complexity of the job :D

[vmcburney]

I think you should use any type of SQL you are comfortable with from generated to SQL builders to user defined as long as you don't transform your data within the select statement. I always try to use generated first, when it becomes too complex I move to a SQL builder, my last resort is user defined. User defined does not synchronise the SQL select list onto the output column list making it higher maintenance.

Thowing in transform functions into a select list such as renaming columns, trimming and converting data may seem like a good idea at the time but these are primary functions of an ETL tool and should be put into the ETL job. Within a custom SQL statement they are largely invisible to metadata reporting tools such as MetaStage and they place extra load onto the RDBMS engine and they are more difficult to maintain. A new developer has to know both DataStage and the SQL scripting language to debug problems.

[chulett]

user defined Sql query is one of the performance tunning techique in DS. It is always a better option that generated one. you can use all type of oracle or any database inbuilt functions to the max extent thereby reducing the complexity of the job :D

I'm with Vince and of the personal opinion that you're off base with the above advice.

You don't need User Defined SQL to tune a query. It's always my technique of last choice and prefer column generated where possible. Minimize the arcane database functions you use in your SQL for precisely the reasons Vince notes.

But as I said, that's just my opinion. Different strokes...

[kduke]

I agree with Craig. Keep it simple unless some strong need changes it.

[ray.wurlod]

It's really quite amazing what you can do with generated SQL if you put your mind to it. All you have to realize is:

• the Derivation column on the Columns tab becomes the SELECT clause (so you can include set functions in the derivations)
  
  the Table name becomes the FROM clause, so you can use comma-separated lists for inner joins (or Cartesian products!) or you can include full specification of joins such as TableA LEFT OUTER JOIN TableB ON TableA.ProdCode = TableB.ProdCode
  
  you can put whatever you like into the other, optional, clauses on the Selection tab