Adapter is in stopped status in event stream processor cockpit

[Former Member]

I am using design studio 1.6 with SAP ESP 5.1 SP11. when I am loggin in to cockpit I always see adapter is in always stopped status. I am attaching snapshots of the error.

Can someone please help. I have created odbc connection on the server where ESP is installed, I have studio also on the same server. Created odbc connection to conect to database. then in studio in data services I created the connection and have build the whole model.

after I published it on server when I logged, I see that error in cockpit. published project is always in running status.

Thanks

Happy

[RobertWaywell · Accepted solution]

From Studio, are you able to Discover the database schema using that ODBC service?

Is the ESP cluster running on the same machine where you are running ESP Studio?

Can another application connection using the same ODBC DSN?

[Former Member · Accepted solution]

Hello Robert,

I have build the whole model using discover the database schema. that works beautifully. ESP cluster and studio are on the same machine.

adapter I have published on the remote cluster as well. I am using the same when building my project.

Other application like IDT, crystal everything work perfect using that ODBC connection.

[RobertWaywell · Accepted solution]

You mention that you "published on a remote cluster". Has the ODBC DSN been created on the "remote cluster"?

Is the ODBC DSN created as a System DSN or an User DSN?

[Former Member · Accepted solution]

Hi Robert,

By remote cluster what I mean is, rather than default, my ODBC connection is under esp://ESP51.demo.local:19011 as shown in the screenshot in Data services tab. by that I think it is on the remote cluster. I hope I am using the right term here.

I have created system DSN and not the local one.

I am not sure if I should be adding anything in project configuration.

[RobertWaywell · Accepted solution]

OK, if you can discover against the service, then you know that the DSN, User ID and Password are correct. The next thing to do is to reconcile the schema you are outputting from the ESP project with the schema of the database table.

Can you provide the CREATE TABLE statement for the database table and the CCL for the output adapter writing to the database plus the CCL for the stream or window that immediately precedes the output adapter in the ESP project?

Specific things to look for are data type mismatches and column order mismatches.

[Former Member · Accepted solution]

Hi Robert,

I only have input adapter and I am not using any output adapter as I thought using output window would be good enough.

I then will connect design studio to directly final output window.

Do i have to have output adapter to store the final result in table? another question is if I am using 5 different tables from the same database, do i have to create 5 separate connection assigning 5 input adapter?

Thanks

[RobertWaywell · Accepted solution]

Sorry, I should have said "Can you provide the CREATE TABLE statement for the database table and the CCL for the input adapter reading from the database?"

Yes, if you want to output results from the streaming analytics project to a database table you would do that with an output adapter. There is a generic DB output adapter and also optimized adapters for HANA, SAP IQ and SAP ASE databases.

For the database input adapter you are creating an input connection from a specific source table to a specific stream or window in the streaming project. If you have not specified the table that your current input adapter is supposed to be retrieving data from, then that would be a problem.

Yes, if you want to retrieve data from 5 separate tables via database input adapters you will require 5 separate adapter instances as each instance will be configured for a specific table.

[Former Member · Accepted solution]

Hi Robert,

I have created input adapter per tables as you suggested and also used output adapter to store my final results. Here is the code from ccl for the project.

CREATE INPUT STREAM Shop_Input_stream SCHEMA (
	Shop_facts_id integer ,
	Article_id integer ,
	Color_code integer ,
	Week_id integer ,
	Shop_id integer ,
	Margin decimal(19, 4 ) ,
	Amount_sold decimal(19, 4 ) ,
	Quantity_sold integer ) ;


CREATE INPUT WINDOW Outlet_Lookup_window1 SCHEMA (
	Shop_id integer ,
	Shop_name string ,
	Address_1 string ,
	Manager string ,
	Date_open string ,
	Long_opening_hours_flag string ,
	Owned_outright_flag string ,
	Floor_space integer ,
	Zip_code integer ,
	City string ,
	State string ) PRIMARY KEY ( Shop_id ) KEEP ALL ROWS ;


/**@SIMPLEQUERY=JOIN*/
CREATE OUTPUT STREAM Join1
AS
	SELECT
		Outlet_Lookup_window1.Shop_name Shop_name ,
		Outlet_Lookup_window1.City City ,
		Outlet_Lookup_window1.State State ,
		Shop_Input_stream.Shop_id Shop_id ,
		Shop_Input_stream.Margin Margin ,
		Shop_Input_stream.Amount_sold Amount_sold ,
		Shop_Input_stream.Quantity_sold Quantity_sold FROM Outlet_Lookup_window1 INNER JOIN Shop_Input_stream ON Outlet_Lookup_window1.Shop_id =
	Shop_Input_stream.Shop_id ;


CREATE OUTPUT STREAM ESP_output_V2_stream1 SCHEMA (
	Shop_name string ,
	City string ,
	State string ,
	Shop_id integer ,
	Margin decimal(19, 4 ) ,
	Amount_sold decimal(19, 4 ) ,
	Quantity_sold integer )
AS
	SELECT * FROM Join1 ;


ATTACH INPUT ADAPTER Shop_DB_Input TYPE db_in
TO Shop_Input_stream
PROPERTIES
	service = 'efashion' ,
	table = '\"\".\"Shop_facts\"' ;


ATTACH INPUT ADAPTER Generic_DB_Input1 TYPE db_in
TO Outlet_Lookup_window1
PROPERTIES
	service = 'efashion' ,
	table = '\"\".\"Outlet_Lookup\"' ;


ATTACH OUTPUT ADAPTER Generic_DB_Output1 TYPE db_out
TO ESP_output_V2_stream1
PROPERTIES
	service = 'esp_output' ,
	table = '\"\".\"ESP_output_V2\"' ;

Snapshot:

Here is the table where I am saving final result via output.

CREATE TABLE [dbo].[ESP_output_V2](
	[Shop_name] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[State] [nchar](10) NULL,
	[Shop_id] [int] NULL,
	[Margin] [decimal](19, 4) NULL,
	[Amount_sold] [decimal](19, 4) NULL,
	[Quantity_sold] [int] NULL
)

snapshot:

Even after making these changes I still have the same issue all adapters, two input and one output. All of them are always in stopped status. Even If i force it to start it doesnt help.

Error:

Thanks a lot for your help again.

[RobertWaywell · Accepted solution]

Looking at your output adapter, since you have provided all the pieces for that one, a possible problem is that you have not specified the schema/table owner in the ATTACH OUTPUT ADAPTER statement. Since your tables are owned by "dbo", can you try qualifying the table names in the ATTACH ... ADAPTER statements with "dbo"?

CREATE TABLE [dbo].[ESP_output_V2](
	[Shop_name] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[State] [nchar](10) NULL,
	[Shop_id] [int] NULL,
	[Margin] [decimal](19, 4) NULL,
	[Amount_sold] [decimal](19, 4) NULL,
	[Quantity_sold] [int] NULL
)

CREATE OUTPUT STREAM ESP_output_V2_stream1 SCHEMA (
	Shop_name string ,
	City string ,
	State string ,
	Shop_id integer ,
	Margin decimal(19, 4 ) ,
	Amount_sold decimal(19, 4 ) ,
	Quantity_sold integer )
AS
	SELECT * FROM Join1 ;

ATTACH OUTPUT ADAPTER Generic_DB_Output1 TYPE db_out
TO ESP_output_V2_stream1
PROPERTIES
	service = 'esp_output' ,
	table = '\"dbo\".\"ESP_output_V2\"' ;

[Former Member · Accepted solution]

Hi Robert,

Adding dbo solved my issues, I could fetch the data and I had to add the same in input adapters.

Input adapter stayed in running status only till it is fetching data once data is loaded they go back to stopped status. I realized when connected large table.

output adapter always stays in running status.

table = '\"dbo\".\"ESP_output_V2\"' ;

Thanks a lot again for your help.