Multi-Source Data Foundation: List of Values based on custom SQL

[Former Member]

I am trying to create a List of Values using custom SQL when using a multi-source data foundation.

Steps to recreate:

In Data Foundation, under List of Values, add new "List of values based on custom SQL"

- Give the lov a name

- click on Edit SQL

- in the SQL Expression Editor window, select SQL Builder. Select 2 columns from the same table and click Preview Data. Data is returned. click OK

- back in SQL Expression Editor window, click Validate and receive Expression is valid message

- back in Lov properties, click Preview and receive a Data Federator driver error

A single-source data foundation works fine. My goal is to have the list of values in the data foundation, not the business layer. Is this possible in a multi-source data foundation?

[Henry_Banks]

This is what i mean:

"normal jar location" i.e for single source = connectionServer\jdbc\drivers\jdbc

for MSU, under here ..\dataAccess\connectionServer\jdbc\drivers\datafederator

Also you should check that the CLASSPATH in datafederator.sbo doesn't contradict the location where the file is found.

Regards,

H

[Henry_Banks]

Hi,

Single-source uses the normal Data Access Connection Servers.

Where as the MultiSource uses the Data Federation Query Service (part of the APS)

so the architecture will be different, as will the location of the required .jar files (for JDBC), or other, that are used at runtime.

Also, You don't specifiy which type of data source you are using, please do .

Regards,

H

Edited by: Henry Banks on Jan 27, 2012 2:49 PM

[Former Member]

I am using SQL Server 2008. My connection is an ODBC connection. Both 32 bit & 64 bit connections are setup on the server.

[Henry_Banks]

You need to determine the cause. Usually: a folder is missing, or connectivity files haven't been unpacked into correct location, or an SQL execution plan setting needs to be changed in Data Federation.

Here's what you need to gather:

1. Enable the client trace for the Information Design Tool, as per Note 1586166

2. Enable the TraceLog service in the Adaptive Processing Server (APS) for the Data Federation Service.

3. Open the Data Federation Administration Tool (DFAT) client. In System Parameters, set "QUERY_HISTORY_SIZE" to 100. Switch to the Query Monitoring view, and select Type "All Queries".

--> Now, Re-run the problematic MSU workflow in IDT

Once complete:

-> In DFAT, Click "Save the monitoring information as XML" to capture the test output

-> For IDT, locate the InformationDesignTool_*_trace.glf file

-> For APS, locate the APS_.AdaptiveProcessingServer_trace..glf file

Now use the trace files to begin analysing the root cause

Let us know your findings. If not, please log a support case with the above collaterals. (it the first thing they/we/I would ask for)

Regards,

H