Problem in disabling RCP...

kaps · Post by **kaps** » Mon Aug 27, 2012 10:17 am

I am having trouble disabling RCP in a lookup ODBC Connector stage in a job. I have disabled it in Project level and also in the job level/stage level but I keep getting the following error.

lkp_1: Unable to determine association between statement parameters and table columns. The connector will not be able to obtain external schema and only limited schema reconciliation will be performed

Do I need to bounce the DataStage once I change RCP ? It goes away when I uncheck it but when I open again I see it there. Confused. Please advise.

ArndW · Post by **ArndW** » Mon Aug 27, 2012 10:52 am

Do you have RCP enable in on or more of the input links to the stage?
Turn on the $OSH_PRINT_SCHEMAS in your job parameters and see what the schemas going into the stage are.

kaps · Post by **kaps** » Mon Aug 27, 2012 11:29 am

It's ODBC Connector stage used as a reference (sparse) lookup with Lookup stage, source is a seq file and output is going via copy stage to a funnel stage and then to some other stages downstream.

I turned on the OSH_SCHEMA and did not find anything under OSH_SCRIPT which says that RCP is ON or any extra column.

I am not sure what's wrong...

kwwilliams · Post by **kwwilliams** » Mon Aug 27, 2012 5:28 pm

kaps wrote:lkp_1: Unable to determine association between statement parameters and table columns. The connector will not be able to obtain external schema and only limited schema reconciliation will be performed

Why do you think the problem has anything to do with rcp? I typically see this problem when a user-defined query which includes a function call is executed. The stage is saying that it cannot find a correlation between your sql statement and the columns of a table.

ArndW · Post by **ArndW** » Mon Aug 27, 2012 9:38 pm

You need to compare the columns in the schema shown in the log with those you defined and you will see a discrepancy.

kaps · Post by **kaps** » Tue Aug 28, 2012 5:59 pm

KWWilliams - Reason I thought the problem with RCP is that I have checked multiple times between the columns in the SQL and the columns defined in the columns tab in ODBC Connector and they look the same. Is there anything else I need to check ?
Following is the SQL in the Stage.

Code: Select all

SELECT 
	Table.Field1 as Field1,
	Table.Field2 as Field2, 
	Table.Field3 as Field3, 
	Table.Field4 as Field4, 
	Table.Field5 as Field5, 
	Table.Field6 as Field6,
 'Value' as Field7
FROM
	dbo.Table AS Table
WHERE 
                 Table.Field1 = ORCHESTRATE.Field;

Arnd - I was trying to compare the schemas in the log(main_program: Schemas:) but could not find this lookup stage in there.
I have a job like this :

Code: Select all

 Seqfile -- Transformer -- lookup stage (with DB2 Conn as a ref lookup) -- 	copy stage -- funnel stage ---
 
 Seqfile -- lookup stage (with ODBC Conn as a ref lookup) -- 	copy stage -- funnel stage ---
 
 after funnel stage it goes to Aggregator and then 4 join stages(with 4 db2 connector ref lookups) and then to seq files.

I can see the schema for the lookups after aggregator but not the ones before that.

Another question is I tried to remove RCP from the lookup stage but it simply does not go...

ShaneMuir · Post by **ShaneMuir** » Wed Aug 29, 2012 2:43 am

kaps wrote: Another question is I tried to remove RCP from the lookup stage but it simply does not go...

I have had this issue before - where you turn off RCP but it remains 'selected' on sparse lookup stages - however it never seemed to cause any problems.

As for the query - I assume that if you run the query in the DB it runs successfully?

ArndW · Post by **ArndW** » Wed Aug 29, 2012 3:53 am

It is possible that DataStage has "tuned" the job at runtime. Set $APT_DISABLE_COMBINATION to "true" in this case to test that.

kaps · Post by **kaps** » Wed Aug 29, 2012 8:16 am

Yes. SQL runs fine in SQL Server client tool.

I already have the 'Disable Operator Combination' as True.

Thanks

ShaneMuir · Post by **ShaneMuir** » Wed Aug 29, 2012 8:27 am

Is it possible at all to test your lookup as a normal rather than a sparse?

kwwilliams · Post by **kwwilliams** » Tue Sep 04, 2012 9:03 pm

Your sql statement is where the issue is located:

'Value' as Field7

It cannot associate a column in the database dictionary to your derived field. If you want this to go away you can use a transformer to create field7 with 'Value' as its contents.

kaps · Post by **kaps** » Wed Sep 05, 2012 4:27 pm

I believe the issue is comment statements in the Lookup SQL. When I removed them it worked...