User defined SQL-required link property

[kcshankar]

Hi,

I searched the forum under the topic user defined sql but i didn't get any solution to my query.

My job looks like

Oracle ------Transformer------Oracle

Source table has 4 columns and Target table has 3 columns.
Iam using user defined sql to Create Target Table and Load Data to that.

Code: Select all

CREATE TABLE TOPNOMINEE1 AS (SELECT RANK, NOMINEE, TARGET FROM DISCOVER.TOPNOMINEE)

The sql is working fine in TOAD.

When I run the job, it aborts with fatal warning. The observations in Director, Designer and Toad are

In director
DbRefIntg...target: User defined SQL is a required link property
Attempting to Cleanup after ABORT raised in stage DbRefIntg...target
Job DbRefIntg aborted.

In Designer
LINK: RED colors
View Data:Required Data

In Toad
Required result.

I can view the Source and target data
My question is
Why the job aborts or the link is red when the data are loaded to the target table?
What is the warning DbRefIntg...target: User defined SQL is a required link property


Thanks in advance
kcs

[chulett]

Confusing.

Assuming your User Defined SQL is in the Target stage, what in the heck is the source stage doing? That method is fine for something like TOAD but not appropriate for a DataStage job.

Put the sourcing query in your first OCI stage. Let the target OCI stage do both the inserts and the create table statement for you. None of this needs User Defined SQL, btw - it's all built in options of the OCI stage. Make use of them and save the User Defined stuff for those rare times when you actually need it, i.e. there's no other way to handle something.

[ray.wurlod]

User-defined SQL must be DML, not DDL. DDL is handled by other means (depending on what stage type you are using).

[Sreenivasulu]

Just to Add:

The Before SQL and AfterSQL tabs as well work with DML not DDL.

Regards
Sreeni

[kcshankar]

Hi Guys,
Thanks for ur replies.

Code: Select all

The Before SQL and AfterSQL tabs as well work with DML not DDL. 

Code: Select all

User-defined SQL must be DML, not DDL. DDL is handled by other means (depending on what stage type you are using).

I replaced ODBC stage for OCI stage,
And sql statements like Create Table xxxx as(Select * from yyyyy) is working fine now.
Design is like that,i have to frequently use some complicated userdefined sql in the project.

In the target ODBC stage,View data option is disabled,anything to do with the settings?


Regards
kcs

[ray.wurlod]

Yes.

You must have DSN, user ID, password, table name and column names (at a minimum) complete before View Data is enabled.

If you are using environment variable job parameters with $PROJDEF or $ENV) it just doesn't happen at all.